Hi all,
I have a client that wants a daily payment report. I built the following mass update to create an output file, which works great -- unless there is no retrieve result.
How can I change the code below to just create a file with a header and footer line if no results are retrieved?
stat vClient as string
stat vDataPath as String
stat vFileHandle as string
stat vDataLine as string
stat vHeader as string
stat vHeaderDate as string
stat vStartDate as date
stat vEndDate as date
stat vState as string
stat vStatus as string
stat vTrans as string
stat vPmtOrg as money
stat vPmtHST as money
stat vCode as string
stat vPmt as string
stat vNeg as string = " "
stat vDesc as string
stat vCltRetrieve as int
stat vButtonOn as int
stat vfilelinecount as int
stat vstartingrec as int
stat q as int
stat vtrailer as string
stat vtotal as string = 0
stat vbatch as int
stat vref as string
stat vBadDebt as string
stat p as int = 0
// ==========================
// USER PROMPT
// ==========================
vCltRetrieve = @PromptForUserInput("Client?","2011")
vStartDate = @PromptForUserInput("Start Date?",@serverdate())
vEndDate = @PromptForUserInput("End Date?",@serverdate())
// ==========================
// RECORD RETRIEVE
// ==========================
vButtonOn = @LoadRetrieveSpec("Blank Retrieve")
PmtCltNo = vCltRetrieve
FormFieldValue("Pmt Admin Screen", "PmtDate", 1, (@Str(vStartDate)) & ".." & (@Str(vEndDate)))
FormFieldValue("Pmt Admin Screen", "PmtHere", 1, ">0;<0")
vButtonOn = @SelectTreeItem("Search Menu!Search Commands!Retrieve New Results (F10)")
// ==========================
// FILE CREATION
// ==========================
vBatch = @str(@tonumber(@xlookup(@FN, PmtCltNo, "Sales Screen!CltNo", "CltDRNRemitBatch")))
vHeaderDate = @right(("0" + @dom(@date)),2) + "/" + @left(@month$(@date),3) + "/" + @YEAR(@date)
vDataPath = "c:\sesame2\data\padexport\KNG_" + @right(("0" + @dom(vHeaderDate)),2) + @left(@month$(vHeaderdate),3) + @year(vHeaderdate) + ".txt"
FileDelete(vDatapath)
// ==========================
// HEADER CREATION
// ==========================
vHeader = "HDR " + @right(("0" + @dom(@date)),2) + "/" + @right(("0" + @month(@date)),2) + "/" + @year(@Date)
// ==========================
// DATALINE CREATION
// ==========================
vFileHandle = FileOpen(vDataPath)
FileSeek(vDataPath, 0)
FileWrite(vFileHandle,vHeader)
vFileLineCount = vFileLineCount +1
FileWrite(vFileHandle,@newline())
// ============================
// MOVE THROUGH EACH RECORD
// ============================
vStartingRec = @ResultSetCurrentPosition()
if PmtHere >1 then vCode = "0305"
if PmtHere <1 then vCode = "9305"
for q = 1 to @ResultSetTotal()
vDesc = @replace(PmtDesc,"(","")
vDesc = @replace(vDesc,")","")
vDesc = @replace(vDesc," ","..")
vDesc = vDesc + ".."
// vCode = @xlookup(@FN, vDesc, "PaymentCodes!PmtDesc", "PmtCode")
vRef = PmtRef + @xlookup(@FN, PmtDBNo, "DR Screen!DBID", "DBComment1")
vBadDebt = @xlookup(@FN, PmtDBNo, "DR Screen!DBID", "CltInfo5")
vPmt = @Decimals(@sum(@abs(PmtHere), @abs(PmtDir), @abs(PmtIntPd)),2)
if vPmt <0 then vNeg = "-"
vDataLine =
"DTL " +
" " +
@right(("0" + @dom(PmtDate)),2) + "/" + @right(("0" + @month(PmtDate)),2) + "/" + @year(PmtDate) +
" " +
@left(vCode,4) & @text(4-@len(vCode)," ") +
" 2 " +
(@text(9-@len(vPmt)," ") + vPmt) +
vNeg +
@left(vRef,14) & @text(14-@len(vRef)," ") +
@left(vBadDebt,10) & @text(10-@len(vBadDebt)," ")
FileWrite(vFileHandle,vDataLine)
vFileLineCount = vFileLineCount +1
FileWrite(vFileHandle,@newline())
ResultSetCurrentPosition (q + 1)
vTotal = @sum(vTotal, vPmt)
NEXT
p = @resultsettotal()
// ==========================
// TRAILER CREATION
// ==========================
vFileLineCount = vFileLineCount +1
vTotal = @decimals(vTotal,2)
vTrailer = "TRL" +
" " +
(@text(10-@len(vTotal)," ") + vTotal) +
" " +
(@text(13-@len(vBatch)," ") + p)
FileWrite(vFileHandle,vTrailer)
FileClose(vFileHandle)
vBatch = @sum(vBatch, 1)
XPost(@Fn, PmtCltNo, "Sales Screen!CltNo", vBatch, "CltDRNRemitBatch","")