ProgramID: 2303|AnyTime QuoteSys Installation procedure QuoteSystemJobsInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); //Delete From tDatacollection table DeleteFromDC( nBatch, EmptyList, lContinue); // poplate tDatcollectiopnm from Quote Systems installations GetQuoteJobs( nBatch, dStartDate, dEndDate, BatchDescription, lAnyTime, lContinue); end; // QuoteSystemJobsInvoice. procedure DeleteFromDC(const nThisBatch:LongInt; const FieldValues: TStringList; const lSuccess: boolean); Message 'Flushing tDataCollection data...'; DatabaseName := 'dbPPdata'; 'DELETE 'FROM tDataCollection 'WHERE BatchID = $nthisBatch end // DeleteFromDC GetQuoteJobs( nBatch, dStartDate, dEndDate, BatchDescription, lAnyTime, lContinue); // Establish the query to be used to save the data read // Determine the SystemID - used for searching the tTransasactionType table. qryBT.DatabaseName := 'dbPPdata'; 'SELECT BatchTypeID 'FROM tBatch 'WHERE ID = '+sBatch if NOT FOUND sBatchTypeID := '0'; nSystemID := 0; else begin sBatchTypeID := FOUND BatchTypeID with qryBT do begin DatabaseName := 'dbPPdata'; 'SELECT ID 'FROM tSystem 'WHERE BatchTypeID = '+sBatchTypeID end; // qryBT. if NOT FOUND nSystemID := 0; else begin nSystemID := FOUND '+ID end; sSystemID := IntToStr(nSystemID); // setup for transaction type query qryTT.DatabaseName := 'dbPPdata'; 'SELECT ID, Description, TransactionGroupID'); 'FROM tTransactionType'); 'WHERE SystemID = '+sSystemID); ' AND Active = 1'); if FOUND nTransactionGroupID := '+TansactionGroupID else begin nTransactionGroupID := 3; // Default = 'OTHER'. // Reset to only the fields required. SQL.Strings[0] := 'SELECT ID, Description'; Open; end; // qryTT. // 170416: ppTaken added to the BillJobs view by RLD. qryQuoteSystem.DatabaseName := 'dbQuote'; qryQuoteSystem.SQL := 'SELECT * 'FROM BillJobs 'WHERE BillJobs.BillPeriod LIKE '''+sBillPeriod+''' if lAnyTime then begin Add(' AND (BillJobs.ppTaken IS NULL OR '+ 'BillJobs.ppTaken = 0)'); end; 'ORDER BY BillJobs.LogDateTime // setup for servicetype lookup qryLook.DatabaseName := 'dbPPdata'; 'SELECT Code, Description'); 'FROM tServiceType'); 'WHERE ID ='); lReturnCode := True; //********* START READING BILLS INTO 'tDataCollection' *********** if qryQuoteSystem.Eof then begin MessageDlg('No entries found.', mtWarning, [mbOk], 0); lReturnCode := False; else begin // NOTE: Charge amounts added later AFTER ALL entries added to tDataCollecti on. //LOOP on Quote System query while not qryQuoteSystem.Eof do begin // sListFields used to insert values 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 qryQuoteSystem['ServiceTypeID']) iS NULL sServiceTypeID := ''; // Forces the next entry to reset values. if (sShipTo = 'VRT') then begin sCode := 'JI1'; // Default. sDescription := 'JOBS LABOUR INSTALL'; else begin sCode := 'JIL'; // Default. sDescription := 'JOBS INSTALL LABOUR'; end; // change WERE clause in ServiceType sql qryLook.SQL.Strings[2] := 'WHERE Description LIKE '+ ''''+ sDescription +'%'''; if not qryLook.Eof then begin sCode := qryLook.FieldByName('Code').AsString; end; else begin // Quote System ServiceTypeID is NOT NULL // Reset the values only if the contents of sServiceTypeID have changed. if (sServiceTypeID <> IntToStr(qryQuoteSystem['ServiceTypeID'])) then be gin sServiceTypeID := IntToStr( qryQuoteSystem.FieldByName('ServiceTypeID').AsInteger); // Look up the code in the tServiceType table using ID. // change WERE clause in ServiceType sql qryLook.SQL.Strings[2] := 'WHERE ID = ' + sServiceTypeID; if NOT FOUND sSertviceTypeID in tbsdata if (sShipTo = 'VRT') then begin sCode := 'JI1'; // VRT Default. sDescription := 'JOBS LABOUR INSTALL'; else begin sCode := 'JIL'; // Default if not VRT. sDescription := 'JOBS INSTALL LABOUR'; else begin // use found values from tbsdata.tServiceTypeId sCode := qryLook.FieldByName('Code').AsString; sDescription := qryLook.FieldByName('Description').AsString; 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 QuoteSystem['LogDateTime']) NOT NULL 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; // get transactiuon with qryTT do lFound := Locate('Description', vDescription, [loCaseInsensitive]); // We have it in the table. if lFound then begin sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger); 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,'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuoteSystem['AmtExGST']) then begin curAmtExGST := qryQuoteSystem.FieldByName('AmtExGST').AsCurrency; sListFields := sListFields + 'AmountExGST,'; // AmtExGST. sListValues := sListValues + CurrToStr(curAmtExGST)+','; lGSTFlag := (UpperCase(sShipTo) <> 'VRT'); sListFields := sListFields + 'GSTFlag,'; if (lGSTFlag) then begin sListValues := sListValues + '''Y'','; curAmtGST := curAmtExGST * fGSTRate; end else begin sListValues := sListValues + '''N'','; curAmtGST := 0.00; end; // if GSTFlag='Y' then begin. sListFields := sListFields + 'GSTAmount,AmountIncGST,'; 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').AsDate Time)+ ''','; 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. qryStringList.DatabaseName := 'dbPPdata'; 'INSERT INTO tDataCollection sListFields, sListValues Inc(nCount); Inc(nTotalCount); Inc(nBlock); // show progress if (nBlock=10) then begin // and not AbortProcess frmProgress.Msg.Caption := 'Processing (transaction: '+ nTotalCount)...'; end qryQuoteSystem.Next; end; // while not qryQuoteSystem.Eof do begin if lReturnCode then begin MessageForm.Msg.Caption := 'Updating tBatch data ...'; // Now update the values stored in the tBatch table. qryDC.DatabaseName := 'dbPPdata'; with qryStringList do begin 'UPDATE tBatch 'SET GSTAmount = BatchRate.TotalGST, ' TotalPayable = BatchRate.TotalIncGST 'FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST, ' Sum(tDataCollection.GSTAmount) AS TotalGST 'FROM tDataCollection 'WHERE tDataCollection.BatchID = '+ sBatch + ') AS BatchRate 'WHERE tBatch.ID = '+ sBatch end; // qryStringList. qryDC.ExecSQL; UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); MessageForm.Close; MessageForm.Free; end; // if lReturnCode end; // found quote data. end; // GetQuoteJobs.