Below is the code, thank you.
var vQuit as Int var vErr as Int var vLine as String var vArray as String var vLen as Int var n as Int var n2 as Int var e as Int var vCount as Int
//Posting vars var vInvoice as String var vInvoices as String var vAcct as String var vAccts as String var vMisc as String var vMiscValues as String
var vCheckNo as String var vTransDate as Date var vManTransDate as String var vDay as String var vDatePosted as Date var vDOW as String var vTime as Time var vType as Int var vTransNo as String var vName as String var vTotalPayment as Double var vMonitor as Double var vService as Double var vInstall as Double var vAcctNo as String
var vAmount as Double var vAmt as String var vAmts as String
var vRSID as Int var vRSID2 as Int var vRSID3 as Int var vLocked as Int
var vRevisedBalance as Double var vCopyWriteLn as String var vCheckOrCredit as String var vResult as Int var vSubject as String var vMsg as String var vReturn as Int var vTotalItemsPosted as Int
If (Check# = "") and (Use Credit Card <> 1) { @Msgbox("Check# was not supplied and 'Use Credit Card' isn't checked.","","Please specify one or the other.") vQuit = 1 ThrowFocus(Check#) }
If Use Credit Card = 1 { If (CC Num = "") Or (CC Exp = "") Or (CtrlNo = "") { @Msgbox("If using credit card, Card Number, Exp Date and Ctrl# are required.","","(Ctrl# is the Authorization Code)") vQuit = 1 } }
If vQuit = 0 { For n = 1 to 20
If (@("Acct" + n) <> "") Or (@("Name" + n) <> "") Or (@("Invoice" + n) <> "") Or (@("Amount" + n) <> "") { vLine = @("Acct" + n) + ";" + @("Name" + n) + ";" + @("Invoice" + n) + ";" + @("Amount" + n)
If (@Instr(vLine, ";;") > 0) Or (@Left(vLine, 1) = ";") Or (@Right(vLine, 1) = ";") { @Msgbox("There's missing data in the grid. Please correct.","","") vQuit = 1 } } Next }
If vQuit = 0 { If Payor = "" { @Msgbox("'Payor Name' hasn't been supplied.","","") vQuit = 1 ThrowFocus(Payor) } If AcctNo = "" { @Msgbox("Payor 'AcctNo' field is empty.","","") vQuit = 1 ThrowFocus(AcctNo) } }
//Find duplicate items in grid If vQuit = 0 { For n = 1 to 20
If @("Acct" + n) <> "" { vLine = @("Acct" + n) + "^" + @("Name" + n) + "^" + @("Invoice" + n) + "^" + @("Amount" + n) vArray = @AppendStringArray(vArray, vLine) }
Next
vLen = @Len(vArray) vArray = @UniqueStringArray(vArray)
If @Len(vArray) <> vLen { @Msgbox("You have a duplicate entry in the grid.","","Please clear the duplicate entry and try again.") vQuit = 1 } }
If vQuit = 0 { If @Askuser("Post this check/payment to DR and pay down the specified invoice(s)?",@NL() + "(Note: If the posted record is in use and therefore locked, you will"," have to manually apply the payment to it.)") { // Service invoices have a '-I' or '-S' suffix which must be stripped off
//Add new record to DR first
//Get list of Invoice Nos For n = 1 to 20
If @("Invoice" + n) <> "" { vInvoice = @("Invoice" + n) vInvoice = @Replace(vInvoice, "-S", "") vInvoice = @Replace(vInvoice, "-I", "") vInvoices = @AppendStringArray(vInvoices, vInvoice) vAcct = @("Acct" + n) vAccts = @AppendStringArray(vAccts, vAcct) vAmt = @("Amount" + n) vAmts = @AppendStringArray(vAmts, vAmt) if vAcct <> "" and vAmt <> "" { if vInvoice <> "" { vMiscValues += vAcct + "-" + vInvoice + " [" + @AsFormattedByLE(TotalBalanceDue, 0, vAmt) + "]; " } else { vMiscValues += vAcct + " [" + @AsFormattedByLE(TotalBalanceDue, 0, vAmt) + "]; " } } }
Next
vMiscValues = @Replace(vMiscValues, "Monitor", "CS") vMisc = "Paying: " + @TrimStringRight(vMiscValues,"; ")
// Create new DR record ###########################################
vAcctNo = AcctNo vCheckNo = Check#
If @Lt(vCheckNo, 3) <> "ACH" { vTransDate = "2099/01/01" } else if @Lt(vCheckNo, 3) = "ACH" {
if @askuser("Do you wish to manually enter the Transaction Date?","","") { vManTransDate = @Calendar("", "Choose Transaction Date [Esc to Cancel]") If Not @Error { vTransDate = vManTransDate vCheckNo = vCheckNo + "-P" } } // end if @askuser("Do you wish to manually enter the Transaction Date?","","") else if vTransDate = "" { If @Int(@Dow(@ServerDate())) = 5 //Friday { vTransDate = @TD(@ServerDate()) + 3 } Else If @Int(@Dow(@ServerDate())) = 6 //Saturday { vTransDate = @TD(@ServerDate()) + 2 } Else if (@Int(@Dow(@ServerDate())) <> 5 and @Int(@Dow(@ServerDate())) <> 6) { vTransDate = @TD(@ServerDate()) + 1 } } // end else if vTransDate = "" } // end if @Lt(vCheckNo, 3) = "ACH"
vDay = @DOW$(vTransDate) vDatePosted = @ServerDate() vDOW = @Dow$(vDatePosted) vTime = @TT(@ServerTime()) vType = "1" vName = Payor vTotalPayment = Total Paid vMonitor = Monitor vService = Service vInstall = Install vTransNo = @Int(@GlobalValue("gvDR@Num")); GlobalValue("gvDR@Num", @Int(vTransNo) + 1)
vRSID = @XResultSetSearch(@FN, "DR", 0, 2, "!Transaction#==")
If vRSID > -1 { XResultSetCreateNewRecord(vRSID) XResultSetValue(vRSID, "Transaction Date", vTransDate) XResultSetValue(vRSID, "Day", vDay) XResultSetValue(vRSID, "Date Posted", vDatePosted) XResultSetValue(vRSID, "DOW", vDOW) XResultSetValue(vRSID, "Time1", vTime) XResultSetValue(vRSID, "Type of Transaction", vType) XResultSetValue(vRSID, "Transaction#", vTransNo) XResultSetValue(vRSID, "CheckNo", vCheckNo) XResultSetValue(vRSID, "Name", vName) XResultSetValue(vRSID, "Total Payment Received", vTotalPayment)
if @Lt(Check#, 3) = "ACH" { XResultSetValue(vRSID, "BankNo", "CREDIT-ACH") XResultSetValue(vRSID, "Bank Name","Electronic Funds Transfer") }
If Use Credit Card = 1 { XResultSetValue(vRSID, "BankNo", "CREDIT-ACH") XResultSetValue(vRSID, "Bank Name", CC Num + " " + CC Exp) XResultSetValue(vRSID, "CheckNo", CtrlNo)
If @Int(@Dow(@ServerDate())) = 5 //Friday { XResultSetValue(vRSID, "Transaction Date", @TD(@ServerDate()) + 3 ) XResultSetValue(vRSID, "Day", @Dow$(@TD(@ServerDate()) + 3 )) } Else If @Int(@Dow(@ServerDate())) = 6 //Saturday { XResultSetValue(vRSID, "Transaction Date", @TD(@ServerDate()) + 2 ) XResultSetValue(vRSID, "Day", @Dow$(@TD(@ServerDate()) + 2 )) } Else { XResultSetValue(vRSID, "Transaction Date", @TD(@ServerDate()) + 1 ) XResultSetValue(vRSID, "Day", @Dow$(@TD(@ServerDate()) + 1 )) }
}
If vMonitor > 0 Then XResultSetValue(vRSID, "Monitor", vMonitor) If vService > 0 Then XResultSetValue(vRSID, "Service", vService) If vInstall > 0 Then XResultSetValue(vRSID, "Install", vInstall)
// 07/25/13: this line modified to below due to vervbiage 'Monitor' post in the Misc field XResultSetValue(vRSID, "Misc_Inv No", vMisc) If vMisc <> "Monitor" { XResultSetValue(vRSID, "Misc_Inv No", vMisc) }
XResultSetValue(vRSID, "AcctNo", vAcctNo)
XResultSetClose(vRSID) } Else { @Msgbox("Unable to create DR payment receipt record.","","") } //#################################################################
//Update Service/Install & Monitor invoices & their parents #######
For n = 1 to 20
If @("Acct" + n) <> "" { vAcctNo = @("Acct" + n) vInvoice = @("Invoice" + n); vInvoice = @Replace(vInvoice, "-S", ""); vInvoice = @Replace(vInvoice, "-I", "") vAmount = @TM(@("Amount" + n))
vRSID = @XResultSetSearch(@FN, "Customers", 0, 2, "!NUM=" + @TN(vAcctNo))
If vRSID > -1 {
If @XResultSetLocked(vRSID) = 1 { WriteLn("Customer Acct# " + @Int(vAcct) + " record was locked and could not be updated." + @NL() + "You will need to manually update the customer's record.") RESIZE() vLocked += 1 } Else {
If vInvoice = "Monitor" { vRSID2 = @XResultSetSubset(vRSID, "CS Invoice") vCount = @XResultSetTotal(vRSID2) XResultSetSort(vRSID2, "Inv Date:-1")
//Find oldest Monitor invoice with a balance due For e = 1 to vCount
XResultSetCurrentPosition(vRSID2, e)
If @TN(@XResultSetValue(vRSID2, "Balance")) > 0 { XResultSetValue(vRSID2, "Pmt Amt", vAmount) XResultSetValue(vRSID2, "Pmt Date", @TD(@ServerDate())) XResultSetValue(vRSID2, "Balance", @TM(@XResultSetValue(vRSID2, "Balance")) - vAmount) //Kill loop cuz we're done e = vCount + 1
//Update Parent XResultSetValue(vRSID, "CS Balance", @TM(@XResultSetValue(vRSID, "CS Balance")) - vAmount) XResultSetValue(vRSID, "Balance Due", @TM(@XResultSetValue(vRSID, "Balance Due")) - vAmount) }
Next
XResultSetClose(vRSID2) }
Else // it's a Service invoice {
vRSID2 = @XResultSetSearch(@FN, "Customers!Service", 0, 2, "!Invoice #=" + vInvoice)
// Add new Payments record and make it a Service record child vRSID3 = @XResultSetSubset(vRSID2, "Payments") XResultSetCreateNewRecord(vRSID3) XResultSetValue(vRSID3, "Payment", vAmount) XResultSetValue(vRSID3, "Date1", @TD(@ServerDate())) XResultSetValue(vRSID3, "Invoice", vInvoice) XResultSetValue(vRSID3, "Acct", vAcctNo) XResultSetReparent(vRSID2, "Payments", vRSID3)
XResultSetClose(vRSID3)
XResultSetValue(vRSID2, "Amt Paid", vAmount) XResultSetValue(vRSID2, "Service Due", @TM(@XResultSetValue(vRSID2, "Service Due")) - vAmount) XResultSetValue(vRSID2, "Invoice #", vInvoice)
XResultSetClose(vRSID2)
//Update Parent XResultSetValue(vRSID, "Service Balance", @TM(@XResultSetValue(vRSID, "Service Balance")) - vAmount) XResultSetValue(vRSID, "Balance Due", @TM(@XResultSetValue(vRSID, "Balance Due")) - vAmount) }
} // End If record wasn't locked
XResultSetClose(vRSID)
} // End If VRSID > -1
} // End If @("Acct" + n) <> ""
Next
//#################################################################
If vLocked = 0 {
For n2 = 1 to 20 // this routine to count the number of line items If @("Acct" + n2) <> "" then vTotalItemsPosted += 1 Next
If @lt(Check#,3) = "ACH" { FileDelete("c:\sesame2\ACHPosting.html") vResult = @PrintAFormToHTML("c:\sesame2\ACHPosting.html")
If vResult = 0 { Writeln("Error: Failed to create document to email.") RESIZE() } if vResult = 1 { vSubject = "ACH Transfer posted" vMsg = "c:\blat\blat.exe c:\Sesame2\Docs\ACHTransfer.txt -f admin@aronsonalarm.com -t andy@aronsonalarm.com -subject " + @chr(34) + vSubject + @chr(34) + " -server mail.aronsonalarm.com:587 -u mark@aronsonalarm.com -pw aron15330 -hostname AronsonEmailer -log c:\blat\blat.log -timestamp -attach c:\sesame2\ACHPosting.html" FileOverWrite("C:\Sesame2\Email.bat", vMsg) vReturn = @RedirectProcess("cmd /C C:\Sesame2\Email.vbs", "") if vReturn = 0 { @MsgBox("ACH Email sent successfully.", "", "") } else { @msgbox("Email send failed","","") }
FileDelete("c:\sesame2\ACHPosting.html") }
} // end If @lt(Check#,3) = "ACH"
vRevisedBalance = TotalBalanceDue - Total Paid
if Check# <> "" { vCheckOrCredit = "Check no: " + Check# } else { vCheckOrCredit = "Credit card ctrl no: " + CtrlNo }
vCopyWriteLn = vCheckOrCredit + " successfully posted. Account record(s) updated." + @NL() + @NL() + vTotalItemsPosted + " line item(s) posted totaling " + @AsFormattedByLE(TotalBalanceDue, 0, Total Paid) + @NL() + @NL() + "New starting balance: " + @AsFormattedByLE(TotalBalanceDue, 0, vRevisedBalance) + @NL() + @NL()
SetCopyBuffer(vCopyWriteLn) RESIZE() vCopyWriteLn += "This information has been copied to clipboard. This Window will self close."
WriteLn(vCopyWriteLn) ForceRedraw()
CLEAR_ALL() REFRESH_LIST()
Loiter(3000) CLS() CloseSlate() ReadOnly(Check#, 0) vTotalItemsPosted = "" } // end If vLocked = 0
// Next 3 lines remmed on 10/05/14. Moved same to allow screen refresh while the slate loiters for 3 seconds. // vTotalItemsPosted = "" // CLEAR_ALL() // REFRESH_LIST()
} // End If @Askuser
} // End If VQuit = 0
|