clear
//20091010 - GMR!
//Tagaytay Highlands
// this program is a utility to check/compare values of AgingBase.dbf with AgingBaseWKS.dbf
/*
Example:
AgingBase.dbf
MemAcctID TranDate Amount
50 08/31/2009 1000.00
50 06/30/2009 1500.00
50 05/31/2009 100.00
75 08/31/2009 2000.00
75 07/31/2009 300.00
75 06/30/2009 100.00
75 05/31/2009 2300.00
75 04/30/2009 3000.00
75 03/31/2009 1000.00
AgingBaseWKS.dbf
MemAcctID Current 30Days 60Days 90Days 120Days Over120
50 1000.00 0.00 1500.00 100.00 0.00 0.00
75 2000.00 300.00 100.00 2300.00 3000.00 1000.00
Assumptions/Remarks:
if trandate not found in AgingBase, Fields in AgingBaseWKS should be equal to 0.00
re: AGINGBase MemAcctID Trandate AgingWKS
50 07/31/2009 30Days = 0.00
50 04/30/2009 120Days = 0.00
50 03/31/2009 Over120 = 0.00
//Dependecies:
dateex.cc
UtilCompareAging.dbf
*/
//SET Additional Tools:
set proc to dateex.cc additive // this proc is used to easily manipulate
// dates based on DUFLP
dateex = new dateex() // init var
? dateex.ldom(date()) // get last date of the month - testing only
//SET Variables:
dYear = 2009// Year(date()) or date set by user //ex. 2009
dCurrent = "08/31/2009" //current date or date set by user //ex. 08/31/2009
dMonth = month(ctod(dCurrent))
do case
case (dMonth - 1) <= 0
d30 = dateex.ldom (ctod((dMonth - 1) + 12 + "/01/" + dYear)) //ex. 07/31/2009
case (dMonth - 1) > 0
d30 = dateex.ldom (ctod(dMonth - 1 + "/01/" + dYear)) //ex. 07/31/2009
endcase
do case
case (dMonth - 2) <= 0
d60 = dateex.ldom (ctod((dMonth - 2) + 12 + "/01/" + dYear)) //ex. 06/30/2009
case (dMonth - 2) > 0
d60 = dateex.ldom (ctod(dMonth - 2 + "/01/" + dYear)) //ex. 06/30/2009
endcase
do case
case (dMonth - 3) <= 0
d90 = dateex.ldom (ctod((dMonth - 3) + 12 + "/01/" + dYear)) //ex. 05/31/2009
case (dMonth - 3) > 0
d90 = dateex.ldom (ctod(dMonth - 3 + "/01/" + dYear)) //ex. 05/31/2009
endcase
do case
case (dMonth - 4) <= 0
d120 = dateex.ldom (ctod((dMonth - 4) + 12 + "/01/" + dYear)) //ex. 04/30/2009
case (dMonth - 4) > 0
d120 = dateex.ldom (ctod(dMonth - 4 + "/01/" + dYear)) //ex. 04/30/2009
endcase
do case
case (dMonth - 5) <= 0
dOver120 = dateex.ldom (ctod((dMonth - 5) + 12 + "/01/" + dYear)) //ex. 03/31/2009
case (dMonth - 5) > 0
dOver120 = dateex.ldom (ctod(dMonth - 5 + "/01/" + dYear)) //ex. 03/31/2009
endcase
// /01/ because every first day of the month starts with 1 - LOL!
dTHIGCI = new database()
dTHIGCI.databaseName := DATABASE_ALIAS // from Settings.h
dTHIGCI.active := true
//set AgingBase
qAgingBase = new query()
with (qAgingBase)
database := dTHIGCI
sql := [SELECT * FROM AgingBase]
active := true
endwith
rAgingBase = qAgingBase.rowset
fAgingBase = rAgingBase.fields
with (rAgingBase)
indexName = "MemAcctID"
endwith
//set AgingBaseWKS
qAgingBaseWKS = new query()
with (qAgingBaseWKS)
database := dTHIGCI
sql := [SELECT * ;
FROM AgingBaseWKS ]
active := true
endwith
rAgingBaseWKS = qAgingBaseWKS.rowset
fAgingBaseWKS = rAgingBaseWKS.fields
with (rAgingBaseWKS)
masterRowset = rAgingBase
indexName = "MemAcctID"
endwith
//reset utilcompareaging table
dTHIGCI.emptyTable("UtilCompareAging")
dTHIGCI.packTable("UtilCompareAging")
//set UtilCompareAging
qUCA = new query()
with (qUCA)
database := dTHIGCI
sql := [SELECT * ;
FROM UtilCompareAging ]
active := true
endwith
rUCA = qUCA.rowset
fUCA = rUCA.fields
rAgingBase.first()
rAgingBaseWKS.first()
for i = 1 to rAgingBase.count()
if rAgingBaseWKS.findkey(fAgingBase["MemAcctID"].value)
//check here by date
do case
//current
case fAgingBase["Trandate"].value = dCurrent
if fAgingBase["Amount"].value <> fAgingBaseWKS["current"].value
? "Current: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["current"].value + chr(9) + chr(9) + fAgingBase["amount"].value //test only
rUCA.beginAppend()
fUCA["ABMemAcctID"].value = fAgingBase["MemAcctID"].value
fUCA["ABTranDate"].value = fAgingBase["Trandate"].value
fUCA["ABAmount"].value = fAgingBase["Amount"].value
fUCA["ABWMemAcctID"].value = fAgingBaseWKS["MemAcctID"].value
fUCA["ABWAmount"].value = fAgingBaseWKS["Current"].value
fUCA["Aging"].value = "Current"
rUCA.save()
endif
//30 days
case fAgingBase["Trandate"].value = d30
if fAgingBase["Amount"].value <> fAgingBaseWKS["30Days"].value
? "30Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["30days"].value + chr(9) + chr(9) + fAgingBase["amount"].value//test only
rUCA.beginAppend()
fUCA["ABMemAcctID"].value = fAgingBase["MemAcctID"].value
fUCA["ABTranDate"].value = fAgingBase["Trandate"].value
fUCA["ABAmount"].value = fAgingBase["Amount"].value
fUCA["ABWMemAcctID"].value = fAgingBaseWKS["MemAcctID"].value
fUCA["ABWAmount"].value = fAgingBaseWKS["30Days"].value
fUCA["Aging"].value = "30Days"
rUCA.save()
endif
//60 days
case fAgingBase["Trandate"].value = d60
if fAgingBase["Amount"].value <> fAgingBaseWKS["60Days"].value
? "60Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["60days"].value + chr(9) + chr(9) + fAgingBase["amount"].value//test only
rUCA.beginAppend()
fUCA["ABMemAcctID"].value = fAgingBase["MemAcctID"].value
fUCA["ABTranDate"].value = fAgingBase["Trandate"].value
fUCA["ABAmount"].value = fAgingBase["Amount"].value
fUCA["ABWMemAcctID"].value = fAgingBaseWKS["MemAcctID"].value
fUCA["ABWAmount"].value = fAgingBaseWKS["60Days"].value
fUCA["Aging"].value = "60Days"
rUCA.save()
endif
//90 days
case fAgingBase["Trandate"].value = d90
if fAgingBase["Amount"].value <> fAgingBaseWKS["90Days"].value
? "90Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["90days"].value + chr(9) + chr(9) + fAgingBase["amount"].value//test only
rUCA.beginAppend()
fUCA["ABMemAcctID"].value = fAgingBase["MemAcctID"].value
fUCA["ABTranDate"].value = fAgingBase["Trandate"].value
fUCA["ABAmount"].value = fAgingBase["Amount"].value
fUCA["ABWMemAcctID"].value = fAgingBaseWKS["MemAcctID"].value
fUCA["ABWAmount"].value = fAgingBaseWKS["90Days"].value
fUCA["Aging"].value = "90Days"
rUCA.save()
endif
//120 days
case fAgingBase["Trandate"].value = d120
if fAgingBase["Amount"].value <> fAgingBaseWKS["120Days"].value
? "120Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["120days"].value + chr(9) + chr(9) + fAgingBase["amount"].value//test only
rUCA.beginAppend()
fUCA["ABMemAcctID"].value = fAgingBase["MemAcctID"].value
fUCA["ABTranDate"].value = fAgingBase["Trandate"].value
fUCA["ABAmount"].value = fAgingBase["Amount"].value
fUCA["ABWMemAcctID"].value = fAgingBaseWKS["MemAcctID"].value
fUCA["ABWAmount"].value = fAgingBaseWKS["120Days"].value
fUCA["Aging"].value = "120Days"
rUCA.save()
endif
//over120 days
case fAgingBase["Trandate"].value = dOver120
if fAgingBase["Amount"].value <> fAgingBaseWKS["Over120"].value
? "Over120: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["over120"].value + chr(9) + chr(9) + fAgingBase["amount"].value//test only
rUCA.beginAppend()
fUCA["ABMemAcctID"].value = fAgingBase["MemAcctID"].value
fUCA["ABTranDate"].value = fAgingBase["Trandate"].value
fUCA["ABAmount"].value = fAgingBase["Amount"].value
fUCA["ABWMemAcctID"].value = fAgingBaseWKS["MemAcctID"].value
fUCA["ABWAmount"].value = fAgingBaseWKS["Over120"].value
fUCA["Aging"].value = "Over120"
rUCA.save()
endif
endcase
//check here content of AgingBaseWKS if it has 0.00 value
//upon which no record should have been appended to
//Aging base in relation to aging which has 0.00 value
do case
case fAgingBaseWKS["Current"].value = 0.00 AND fAgingBase["Trandate"].value = dcurrent
? "Current: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["current"].value + chr(9) + chr(9) + fAgingBase["amount"].value //test only
case fAgingBaseWKS["30Days"].value = 0.00 AND fAgingBase["Trandate"].value = d30
? "30Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["30Days"].value + chr(9) + chr(9) + fAgingBase["amount"].value //test only
case fAgingBaseWKS["60Days"].value = 0.00 AND fAgingBase["Trandate"].value = d60
? "60Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["60Days"].value + chr(9) + chr(9) + fAgingBase["amount"].value //test only
case fAgingBaseWKS["90Days"].value = 0.00 AND fAgingBase["Trandate"].value = d90
? "90Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["90Days"].value + chr(9) + chr(9) + fAgingBase["amount"].value //test only
case fAgingBaseWKS["120Days"].value = 0.00 AND fAgingBase["Trandate"].value = d120
? "120Days: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["120Days"].value + chr(9) + chr(9) + fAgingBase["amount"].value //test only
case fAgingBaseWKS["Over120"].value = 0.00 AND fAgingBase["Trandate"].value = dOver120
? "Over120: " + fAgingBase["MemAcctID"].value + chr(9) + fAgingBase["Trandate"].value + chr(9) + fAgingBaseWKS["Over120"].value + chr(9) + chr(9) + fAgingBase["amount"].value //test only
endcase
else
? fAgingBaseWKS["MemAcctID"].value // check what memacctID has been saved in AgingBaseWKS
//that is not present in AgingBASE
endif
rAgingBase.next()
endfor
? "AgingBase RecordCount: " + chr(9) + chr(9) +rAgingBase.count()
? "AgingBaseWKS RecordCount:" + chr(9) +rAgingBaseWKS.count()
qAgingBase.active = false
qAgingBaseWKS.active = false
qUCA.active = false
dTHIGCI.active = false
No comments:
Post a Comment