Here's the main query that retrieves the last invoice for customers
(...inside a TEXT/ENDTEXT block):
SELECT h1.iid, h1.iacctid, h1.dcreated, h1.ddue,
h1.nprevbal, h1.ncurrcredits, h1.ncurrdebits,
h1.nprevbal + h1.ncurrdebits - h1.ncurrcredits
as nbalance,
c1.cname, a1.cacctnum, a1.iactive, towns.ctown
FROM invoice_header h1
INNER JOIN accounts a1 on a1.iid = h1.iacctid
INNER JOIN customers c1 on c1.iid = a1.icustid
INNER JOIN towns on towns.iid = a1.itownid
WHERE h1.ddue < '<<DTOC(DATE(),1)>>' and h1.ivoided = 0
and a1.iactive = 1
and h1.dcreated <> '00000000'
and h1.iid in (select MAX(iid) from
invoice_header h2 where h2.iacctid = h1.iacctid)
and h1.iacctid not in
(select t1.iacctid
from transactions t1
where t1.iacctid = h1.iacctid
and t1.ctranstype = 'LATE'
and t1.ivoided = 0
and t1.ddate > h1.dcreated)
Afterwards, I run this code to get the sum of payments and see if the
account had it's invoiced amount fully paid on/prior to the due date,
and if not, a late fee is assessed:
* get the current late fee percentage
TEXT TO lcSQL NOSHOW TEXTMERGE PRETEXT 2
SELECT iid, ndays, npct, dactive
FROM latefees f1
WHERE dactive <= '<<DTOC(DATE(),1)>>'
ORDER BY dactive DESC
LIMIT 1
ENDTEXT
=SQLEXEC(liHandle,lcSQL,"curRate")
SELECT curRate
IF RECCOUNT("curRate") > 0 THEN && should be 1...go through
and set each of the late fees in the dataset
* now start to make the final cursor
CREATE CURSOR (lcCursor) ;
(lSelected l DEFAULT .T., iacctid i, cacctnum c(16),
nBalance n(13,2), nLateFee n(8,2), ;
nNewBalance n(13,2), cname c(64), ctown c(32),
ncurrbal n(13,2), nSumCredits n(13,2), ;
dLast d, dDue d)
* now scan thru dataset and see if balance has been paid
by due date
SELECT curTemp
liCtr = 0
lcRecCnt = ALLTRIM(STR(RECCOUNT("curTemp")))
SCAN
liCtr = liCtr + 1
WAIT WINDOW NOWAIT "Analyzing invoice " +
ALLTRIM(STR(liCtr)) + " of " + lcRecCnt
* I know I could speed it up by not creating 4 vars
and just referencing the curTemp fields below....and I'm gonna do that
soon, so other suggestions, please!
liAcctID = curTemp.iacctid
ldDue = curTemp.dDue
ldCreated = curTemp.dCreated
lnBalance = curTemp.nBalance
* see what credits/payments applied since invoice
was created and on/before due date
TEXT TO lcSQL NOSHOW TEXTMERGE PRETEXT 2
SELECT SUM(t1.namount) as nSum
FROM transactions t1
INNER JOIN transaction_types tt
ON tt.ctranstype = t1.ctranstype
WHERE t1.iacctid = <<liAcctID>> and tt.crectype
= 'C'
and t1.ddate <= '<<DTOC(ldDue,1)>>'
and t1.ddate >= '<<DTOC(ldCreated,1)>>'
and t1.ivoided = 0 and t1.icount = 1
ENDTEXT
IF SQLEXEC(liHandle,lcSQL,"curSum") = 1 THEN && see
if amt credited is >= nbalance
lnSumCredits = NVL(curSum.nSum,0)
IF lnSumCredits < lnBalance THEN && add late fee
to temp cursor
lnCurrBal = this.GetBalance(liAcctID)
* mjb 02-23-08 want to get last payment date
too for this account
TEXT TO lcSQL NOSHOW TEXTMERGE PRETEXT 2
SELECT MAX(dDate) as dLast
FROM transactions t1
WHERE t1.iacctid = <<liAcctID>> and
t1.ivoided = 0
ENDTEXT
=SQLEXEC(liHandle,lcSQL,"curLastDate")
ldLastDate = NVL(curLastDate.dLast,{/})
INSERT INTO (lcCursor)
(iacctid,cacctnum,nbalance,nlatefee,cname,ctown,ncurrbal,;
nnewbalance,ddue,nsumcredits,dlast) ;
VALUES
(liAcctID,curTemp.cacctnum,lnBalance,curRate.nPct/100 * lnBalance,;
curtemp.cname,curtemp.ctown,lnCurrBal,lnCurrBal + (curRate.nPct/100 *
lnBalance),;
ldDue,lnSumCredits,ldLastDate)
ENDIF && curSum.nSum <= lnBalance
USE IN SELECT("curSum") && done with temp cursor
until the next time
ELSE && problems with SQL
AERROR(laError)
MESSAGEBOX("Error on acct " +
ALLTRIM(STR(liAcctID)) + CHR(13) + laError(2),16,"Error retrieving sum
of payments before due date. Contact MBSS and note account.")
ENDIF && SQLEXEC(..) = 1
ENDSCAN
This code works, and now I'm looking for code optimizations. It's VFP9
against a MySQL5 backend.
Ideas/suggestions for improvement (other than using DotNet, Stephen <g>)???
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
