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);