Pulling apart the QuoteSuetm PP code SQL's..... Get BatchTypeID Running... --# Determine the SystemID - used for searching the tTransasactionType table. SELECT BatchTypeID FROM tBatch WHERE ID = 62615; Result: 200| Get SystemID Running... SELECT ID FROM tSystem WHERE BatchTypeID = 200 Result: 200| Get TransactionGroupID # PP GetQuoteJObs procedure uses first returned to get TransactionGroupID # sTransactionTypeID=(1st from returned List) # sTransactionGroupID=(1st from returned List ) Running... SELECT ID, Description, TransactionGroupID FROM tTransactionType WHERE SystemID = 200 AND Active = 1 Result: 71|60025 JOBS DATA&VOICE PABX LABOUR|3| # the PP Quote Installations code then runs this SQL on VRTQuote syetm # SELECT * # FROM BillJobs # WHERE BillJobs.BillPeriod LIKE '2024/02' # --# if lAnyTime then begin # AND (BillJobs.ppTaken IS NULL OR BillJobs.ppTaken = 0) # --# endif # ORDER BY BillJobs.LogDateTime # Running the below SQL in SQLVER Management Studio on Citrix # USE VRTQuote; # SELECT * # FROM BillJobs # WHERE BillJobs.BillPeriod LIKE '2024/02' # --# if lAnyTime then begin # AND (BillJobs.ppTaken IS NULL OR BillJobs.ppTaken = 0) # --# endif # ORDER BY BillJobs.LogDateTime # # Returns these (NO S000003377)... #LogDateTime BillPeriod ServiceTypeID CallNumber Supplier BilledDate CustRef Description ShipTo Location ProgOnly ProgLocation Lastname Bill AmtExGST Seqno IthelpUser PPTaken #21/02/2024 2024/02 988 SO00003604 NULL 2024-02-21 10:01:13.187 30917 NULL HOL NULL 1.00 Functional Testing Certification and HMI upgrade x Jess Mackie 1 18502.34 SO00003604 NULL NULL #21/02/2024 2024/02 988 SO00003510 NULL 2024-02-21 17:06:43.480 MTM157382 NULL ZPF NULL 1.00 MTM - Planned Power Outage Flinders Street - 4th F Richard Ma 1 10545.61 SO00003510 NULL NULL # # removing # AND (BillJobs.ppTaken IS NULL OR BillJobs.ppTaken = 0) # from the sql # Returns 23 rows one of them beeing SO00003377 (Showing PPTaken set to 1) #02/02/2024 2024/02 988 SO00003377 NULL 2024-02-02 15:54:26.833 76583 NULL NNA NULL 1.00 Termination of (D)4831 from Deer Pk to william st Olga Votrubec 1 1384.00 SO00003377 NULL 1 # for each qryQuoteSystem BillJobs row # # The PP Quote Installations code then creats # and executes Insert and update statments # based on the Value found in BillJobs.* and addition sql to tbsdata # # here is the code... # # qryLook.SQL.Add('SELECT Code, Description'); # qryLook.SQL.Add('FROM tServiceType'); # qryLook.SQL.Add('WHERE ID ='); # # //********* START READING BILLS INTO 'tDataCollection' *********** # # sListFields := '('; # sListValues := 'VALUES ('; # sListFields := sListFields + 'SequenceNo,'; # sListValues := sListValues + '''' + # qryQuoteSystem.FieldByName('SeqNo').AsString +''','; # // NB ShipTo (following) needed NOW for the default values. # sShipTo := Trim(UpperCase(qryQuoteSystem.FieldByName('ShipTo').AsString)); # if VarIsNull(qryQuoteSystem['ServiceTypeID']) then begin # sServiceTypeID := ''; // Forces the next entry to reset values. # if (sShipTo = 'VRT') then begin # sCode := 'JI1'; // Default. # sDescription := 'JOBS LABOUR INSTALL'; # end # else begin # sCode := 'JIL'; // Default. # sDescription := 'JOBS INSTALL LABOUR'; # end; # // Look up the code in the tServiceType table using description. # qryLook.Close; # qryLook.SQL.Strings[2] := 'WHERE Description LIKE '+ # ''''+ sDescription +'%'''; # qryLook.Open; # if not qryLook.Eof then begin # sCode := qryLook.FieldByName('Code').AsString; # end; # end # else begin # // Reset the values only if the contents of sServiceTypeID have changed. # if (sServiceTypeID <> IntToStr(qryQuoteSystem['ServiceTypeID'])) then begin # sServiceTypeID := IntToStr( # qryQuoteSystem.FieldByName('ServiceTypeID').AsInteger); # // Look up the code in the tServiceType table using ID. # qryLook.Close; # qryLook.SQL.Strings[2] := 'WHERE ID = ' + sServiceTypeID; # qryLook.Open; # if qryLook.Eof then begin # if (sShipTo = 'VRT') then begin # sCode := 'JI1'; // VRT Default. # sDescription := 'JOBS LABOUR INSTALL'; # end # else begin # sCode := 'JIL'; // Default if not VRT. # sDescription := 'JOBS INSTALL LABOUR'; # end; # end # else begin # sCode := qryLook.FieldByName('Code').AsString; # sDescription := qryLook.FieldByName('Description').AsString; # end; # end; # end; # sListFields := sListFields + 'ServiceType,'; # sListValues := sListValues + '''' + sCode +''','; # sListFields := sListFields + 'ServiceID,'; # // RJC 060621: sCode added to the ServiceID entry. # sServiceID := Trim('JOBS '+ sShipTo +' '+ sCode); # sListValues := sListValues + '''' + sServiceID +''','; # if not VarIsNull(qryQuoteSystem['LogDateTime']) then begin # sListFields := sListFields + 'TxnDate,'; # dLogDateTime := qryQuoteSystem.FieldByName('LogDateTime').AsDateTime; # sListValues := sListValues +''''+FormattedDateString(dLogDateTime)+''','; # sListFields := sListFields + 'TxnTime,'; # sListValues := sListValues +''''+ TimeToStr(dLogDateTime) +''','; # end; # # // Now set the transaction type values. # if (sShipTo = 'VRT') then sServiceID := Trim(sShipTo +' '+ sDescription) # else sServiceID := Trim(sDescription); # // Now set the transaction type values - first the description. # if (sShipTo = 'VRT') then sServiceID := Trim(sShipTo +' '+ sDescription) # else sServiceID := Trim(sDescription); # // Now set the transaction type values - search for it. # vDescription := sServiceID; # with qryTT do # lFound := Locate('Description', vDescription, [loCaseInsensitive]); # // We have it in the table. # if lFound then begin # sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger); # end # else begin # // New - create a new entry in tTransactionType. # AddNewTransactionType( sServiceID, nTransactionGroupID, nSystemID); # qryTT.Close; # qryTT.Open; # // Now retrieve the index number - ID. # with qryTT do # lFound := Locate('Description', vDescription, [loCaseInsensitive]); # if lFound then begin # // We have it - of course. # sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger); # end # else begin # // Something wrong - use a generic value. # sTransactionTypeID := IntToStr(DEFAULT_TRANSACTIONTYPEID); # end; // lFound. # end; // lFound. # sListFields := sListFields + 'TransactionTypeID,'; # sListValues := sListValues + sTransactionTypeID + ','; # { # nTransactionType := -1; # for i := 0 to slTransactionTypeName.Count-1 do begin # sSubString := slTransactionTypeName.Strings[i]; # nPos := Pos(' ',sSubString)+1; # if (Copy(sSubString,nPos,50) = sServiceID) then begin # nTransactionType := i; # break; # end; # end; // for. # if (nTransactionType > -1) and # (nTransactionType < slTransactionTypeName.Count) then begin # sListFields := sListFields + 'TransactionTypeID,'; # sListValues := sListValues + slTransactionTypeID[nTransactionType]+','; # end; # } # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # sListFields := ''; # sListValues := ''; # # if not VarIsNull(qryQuoteSystem['AmtExGST']) then begin # // curAmtExGST := curFormat(qryQuoteSystem['AmtExGST']); # curAmtExGST := qryQuoteSystem.FieldByName('AmtExGST').AsCurrency; # sListFields := sListFields + 'AmountExGST,'; // AmtExGST. # // sListValues := sListValues + Curr2Str(curAmtExGST)+','; # sListValues := sListValues + CurrToStr(curAmtExGST)+','; # lGSTFlag := (UpperCase(sShipTo) <> 'VRT'); # sListFields := sListFields + 'GSTFlag,'; # if (lGSTFlag) then begin # sListValues := sListValues + '''Y'','; # // curAmtGST := curFormat(curAmtExGST * fGSTRate); # curAmtGST := curAmtExGST * fGSTRate; # end # else begin # sListValues := sListValues + '''N'','; # curAmtGST := 0.00; # end; // if GSTFlag='Y' then begin. # sListFields := sListFields + 'GSTAmount,AmountIncGST,'; # // sListValues := sListValues + Curr2Str(curAmtGST)+','+ # // Curr2Str(curSum(curAmtExGST,curAmtGST))+','; # sListValues := sListValues + CurrToStr(curAmtGST)+','+ # CurrToStr(curAmtExGST + curAmtGST)+','; # end; // AmtExGST. # sListFields := sListFields + 'FromDate,'; # sListValues := sListValues +''''+ sFromDate +''','; # sListFields := sListFields + 'ToDate,'; # sListValues := sListValues +''''+ sToDate +''','; # if not VarIsNull(qryQuoteSystem['CallNumber']) then begin # sListFields := sListFields + 'Var01,'; // CallNumber. # sListValues := sListValues +''''+ # qryQuoteSystem.FieldByName('CallNumber').AsString +''','; # end; # if not VarIsNull(qryQuoteSystem['Supplier']) then begin # sListFields := sListFields + 'Var02,'; // Supplier. # sListValues := sListValues +''''+ # qryQuoteSystem.FieldByName('Supplier').AsString+''','; # end; # if not VarIsNull(qryQuoteSystem['BilledDate']) then begin # sListFields := sListFields + 'Var03,'; // BilledDate. # sListValues := sListValues +''''+ # StandardDateTimeString(qryQuoteSystem.FieldByName('BilledDate').AsDateTime)+ # ''','; # end; # if not VarIsNull(qryQuoteSystem['CustRef']) then begin # sListFields := sListFields + 'Var04,'; // CustRef. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('CustRef').AsString) +''','; # end; # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # sListFields := ''; # sListValues := ''; # if not VarIsNull(qryQuoteSystem['Description']) then begin # sListFields := sListFields + 'Var05,'; // Description. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('Description').AsString) +''','; # end; # if (Length(sShipTo) > 0) then begin # sListFields := sListFields + 'Var06,'; // ShipTo. # sListValues := sListValues +''''+ sShipTo +''','; # end; # if not VarIsNull(qryQuoteSystem['CustRef']) then begin # // Repeat the CustRef saved in Var04 into Var07 too - RLD 170120. # sListFields := sListFields + 'Var07,'; // CustRef. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('CustRef').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['ITHelpUser']) then begin # sListFields := sListFields + 'Var09,'; // ITHelpUser. # sListValues := sListValues +''''+ # qryQuoteSystem.FieldByName('ITHelpUser').AsString +''','; # end; # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # sListFields := ''; # sListValues := ''; # if not VarIsNull(qryQuoteSystem['Location']) then begin # sListFields := sListFields + 'Var10,'; // Location. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('Location').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['ProgOnly']) then begin # sListFields := sListFields + 'Var12,'; // ProgOnly. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('ProgOnly').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['ProgLocation']) then begin # sListFields := sListFields + 'Var13,'; // ProgLocation. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('ProgLocation').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['LastName']) then begin # sListFields := sListFields + 'Var14,'; // LastName. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('LastName').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['Bill']) then begin # sListFields := sListFields + 'Var15,'; // Bill. # sListValues := sListValues + IntToStr(qryQuoteSystem['Bill']) +','; # end; # sListFields := sListFields + 'BatchID)'; // BatchID. # sListValues := sListValues + sBatch +')'; # # // Now make the SQL statement to save to the database table. # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # qryStringList.Clear; # qryStringList.Add('INSERT INTO tDataCollection'); # for nListValues := 0 to FieldStringList.Count-1 do begin # qryStringList.Add(FieldStringList[nListValues]); # end; # for nListValues := 0 to ValueStringList.Count-1 do begin # qryStringList.Add(ValueStringList[nListValues]); # end; # // Update and execute the query. # qryDC.Close; # qryDC.SQL := qryStringList; # qryDC.ExecSQL; # # // Now update the values stored in the tBatch table. # with qryStringList do begin # Clear; # Add('UPDATE tBatch'); # Add('SET GSTAmount = BatchRate.TotalGST,'); # Add(' TotalPayable = BatchRate.TotalIncGST'); # Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ # 'AS TotalIncGST,'); # Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); # Add('FROM tDataCollection'); # Add('WHERE tDataCollection.BatchID = '+ sBatch + ') AS BatchRate'); # Add('WHERE tBatch.ID = '+ sBatch); # qryDC.ExecSQL; # UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT);