ok, I have reworked the programming and lowered the 2minute calc down to 1min17sec (yea!). However, to use this program where I want to in my application, I need to get that 1min17sec down to about 30sec. Otherwise I can just add a master loop that will calc not just a single client, but all clients within the date parameter, and run the calc once/week.
Whether the further speed increase is possible or not, thank you for schooling me on some better programming techniques!
If (@Mode() = 1) and (vFlag = 0) { // below is asking for a clientID and getting a ResultSet
If ClientMultiLoc = "" { vClientEmail = "" vClientFax = "" vClientName = "" vClientID = ClientID vClientEmail = ClientEmail1 vClientFax = ClientFax vClientName = ClientName writeln(vClientID) } else If ClientMultiLoc <> ""
{ vClientID = ClientID vShipToEmail1 = "" vShipToFax = "" vClientName = "" vClientEmail = "" vRS20 = @XResultSetSearch(@FN, "Client!ShipTo", SEARCH_MODE_OR, SEARCH_SYNTAX_QA, "!ClientID=" + vClientID) XResultSetSort(vRS20, "ShipToName") If vRS20 > -1 { vCount = @XResultSetTotal(vRS20) If vCount > 0 { vList = "" For vLoop20 = 1 To vCount XResultSetCurrentPosition(vRS20, vLoop20) vLine = @XResultSetValue(vRS20, "ShipToName") + " ~ " + @XResultSetValue(vRS20, "ShipToId") vList = vList + vLine + ";" Next vList = @ReplLas(vList, ";", "") vList = @SortStringArray(vList, 0) PopupSelectPosition(4, @Xpos(ClientID)+10, @Ypos(ClientID)+10) vChoice = @PopupChoiceList(vList, "SELECT A ShipTo______________________________________________") If vChoice <> "" { vChoice = @Mid(vChoice, @Instr(vChoice, "~") + 2, 500) //ShipToID = vChoice vClientID = vClientID + "+" + vChoice writeln(vClientID) } } // this next section will // get the ShipToEmail and ShiptoFax // and ShipToName
vRS21 = @XResultSetSearch(@FN, "Client!ShipTo", SEARCH_MODE_OR, SEARCH_SYNTAX_QA, "!ShipToClientID=" + vClientID) If vRS21 > -1 { vCount = 0 vCount = @XResultSetTotal(vRS21) If vCount = 1 { vClientEmail = "" vShipToName = @XResultSetValue(vRS21, "ShipToName") vShipToEmail1 = @XResultSetValue(vRS21, "ShipToEmail1") vShipToFax = @XResultSetValue(vRS21, "ShipToFax") vClientEmail = vShiptoEmail1 vClientFax = vShipToFax vClientName = vShipToName } } XResultSetClose(vRS21)
}
XResultSetClose(vRS20)
} // ends If ClientMultiLoc <> ""
// the above has determined our vCliendID.
// the next calc will determine the date parameter for this program vDate = @Date vStartDate = (vDate - 180) vEndDate = vDate vDateRange = (vStartDate + ".." + vEndDate) If vClientID <> "" { // the next calc will find all records // for this client within the date parameter // that do not include backorders or // description line items or labor items
vUniqueItemNum = "Begin here" vRS6 = @XResultSetSearch(@FN, "Orders!SOLines", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ShipToClientID=" + vClientID, "!SODate=" + vDateRange, "!SONum=/" + "..a;..b;..c;..d;..e;..x") writeln(vRS6) If vRS6 > -1 { vTot = 0 vTot = @XResultSetTotal(vRS6) writeln("vTot - " + vTot)
XResultSetSort(vRS6, "ItemNum:-1")
// this next section I want to take the first ItemNum, find out, within the date parameter // 1) how many times this item number has been ordered by this customer, // 2) how many total units have been ordered // 3) then I can determine the average # of days between orders // 4) and the average # of units per order // 5) then take the most recent date this item has been ordered, add the average span of days between orders // to arrive at a predicted date of needed reorder // 6) divide the total qty of units ordered by the number of orders placed during the date parameter // to arrive at an average qty ordered per order
// this below section will find skip duplicate ItemNum's, calcing only on one of each ItemNum instance // ordered in the date parameter. vTot6 = @XResultSetTotal(vRS6)
vLoop6 = 0 For vLoop6 = 1 to vTot6 XResultSetCurrentPosition(vRS6, vLoop6) vItemNum = @XResultSetValue(vRS6, "ItemNum") If vUniqueItemNum <> vItemNum
// begin vUniqueItemNum <> vItemNum { // begin vUniqueItemNum <> vItemNum
vUniqueItemNum = vItemNum vItemNumList = vItemNumList + @NewLine() + vItemNum
vRS7 = @XResultSetSearch(@FN, "Orders!SOLines", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ShipToClientID=" + vClientID, "!SODate=" + vDateRange, "!ItemNum=" + vItemNum, "!ItemCat=/" + "DESC;LBR;RC", "!SONum=/" + "..a;..b;..c;..d;..e;..x") If vRS7 > -1 { vItemDesc = @XResultSetValue(vRS6, "ItemDesc") vQtyTotal = 0 vAvgInterval = 0 vPredictedDate = 0 vAvgQty = 0 vTot7 = 0 vTot7 = @XResultSetTotal(vRS7) // only continue if there is more than one // instance of this ItemNum within the date // parameter If vTot7 > 1 {
// this calc will determine the total qty // of this ItemNum vLoop8 = 0 vQtyTotal = 0 For vLoop8 = 1 to vTot7 vQty = 0 XResultSetCurrentPosition(vRS7, vLoop8) vQty = @XResultSetValue(vRS7, "ItemQtyShadow") vQtyTotal = vQtyTotal + vQty Next
// only continue is Total quantity of this // ItemNum is more than 1 unit If vQtyTotal > 1 {
vSONum = @XResultSetValue(vRS7, "SONum") vItemCat = @XResultSetValue(vRS7, "ItemCat") vItemUoM = @XResultSetValue(vRS7, "ItemUoM") // now I need to determine the earliest SODate // and the latest SODate
// This calc will sort in ascend order and then // take only the first record (minimum date) XResultSetSort(vRS7, "SODate:-1") vLoop8 = 0 For vLoop8 = 1 to 1 XResultSetCurrentPosition(vRS7, vLoop8) vMinSODate = @XResultSetValue(vRS7, "SODate") Next
// This calc will sort in descend order and // then take only the first record (maximum date) XResultSetSort(vRS7, "SODate:1") vLoop8 = 0 For vLoop8 = 1 to 1 XResultSetCurrentPosition(vRS7, vLoop8) vMaxSODate = @XResultSetValue(vRS7, "SODate") Next
// this calc will determine the average # of days // btwn orders, and the average qty per order vAvgInterval = (@TD(vMaxSODate) - @TD(vMinSODate))/vTot7 vPredictedDate = @TD(vMaxSODate) + vAvgInterval vAvgQty = vQtyTotal/vTot7 If vAvgQty < 1 { vAvgQty = 1 } If (vPredictedDate > (vDate - 7)) and (vPredictedDate < (vDate + 7)) { //writeln("Reorder Date for " + vAvgQty + " " + vItemUoM + " of ItemNum #" + vItemNum + " / " + vItemDesc + @NewLine() + " should be " + vPredictedDate + @NewLine() + @NewLine()) vNeedSoonAddTo = "" vNeedSoonAddTo = "Reorder Date for " + vAvgQty + " " + vItemUoM + " of ItemNum #" + vItemNum + " / " + vItemDesc + @NewLine() + " should be " + vPredictedDate + @NewLine() + @NewLine() vNeedSoonFYI = vNeedSoonFYI + vNeedSoonAddTo + @NewLine() }
} // ends if vQtyTotal > 1
} // ends if vTot7 > 1
} // ends if vRS7 > -1 XResultSetClose(vRS7)
} // ends If vUniqueItemNum <> vItemNum
Next // vLoop6 to vTot6
writeln(vItemNumList)
If vNeedSoonFYI <> "" { vNeedSoonHeader = "Need Soon Report from MyCompany " + @NewLine() + "Hi, " + @NewLine() + vClientID + " / " + vClientName + @NewLine() + @NewLine() + "MyCompany's wise, all-seeing White Owl Software suggests that " + @NewLine() + "you are due to order the following products - please contact us " + @NewLine() + "via fax xxx-xxx-xxxx, phone xxx-xxx-xxxx, or email MySalesDept@mycompany.com." + @NewLine() + "We appreciate you !!!" + @NewLine() + @NewLine() + @NewLine() vNeedSoonFYI = vNeedSoonHeader + vNeedSoonFYI Writeln(vNeedSoonFYI) vQuestion1 = @PromptforUserInput("If you wish to FAX this NeedSoon FYI to this client, Enter 'Y' and press <OK>; otherwise, just leave blank and press <OK> or <cancel> ", "") If vQuestion1 = "Y" { Cls() CloseSlate() OpenSlate() Writeln("Fax: " + vClientFax + @NewLine() + @NewLine() + vNeedSoonFYI) } If vClientEmail = "" { @MsgBox("Please get correct email address for this client,","and enter it in either the main ClientEmail1 field, or","in the correct ShipToEmail1 field.") }
Else If vClientEmail <> "" { vQuestion1 = "" vQuestion1 = @PromptforUserInput("If you wish to EMAIL this NeedSoon FYI to this client, Enter 'Y' and press <OK>; otherwise, just leave blank and press <OK> or <cancel> ", "") If vQuestion1 = "Y" { vEmail = @sendmail("smtp2go.com","You are due for the following products from MyCompany","myserver@mycompany.com", vClientEmail,"","",vNeedSoonFYI,"","") writeln(vEmail) } } } // ends If vNeedSoonFYI <> ""
//vRpt = @XResultSetPrintReport("Client-ItemNumQtySum2-Ascend", vRS5, REPORT_MODE_HTML_PREVIEW)
} // ends If vRS6 > -1
XResultSetClose(vRS6) vFlag = 1
} // ends If vClientID <> ""
} // ends program
|