unit QuoteSystemMarval; { // 170825: Copy of QuoteSystem modified a/c replacement database - MarvalQuote. } interface uses Messages, SysUtils, Classes, Graphics, Controls, Dialogs, FileCtrl, db, dbTables; type TServiceDetails = record Custno : string; ServCode : string; ServiceID: string; RentFrom : TDateTime; RentTill : TDateTime; CallFrom : TDateTime; CallTill : TDateTime; end; TCallHeaderType = record SequenceNo : string[7]; ServiceID : string[17]; TxnDate : string[8]; TxnTime : string[5]; Origin : string[20]; Duration : string[9]; CallCode : string[2]; RateDescription : string[32]; DialledNumber : string[17]; // Temporary variable only. ServiceNotes : string[22]; AmountExGST : string[13]; GSTAmount : string[13]; AmountIncGST : string[13]; ToDate : string[8]; end; procedure QuoteSysMarvalPurchasesInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); procedure QuoteSysMarvalJobsInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); procedure QuoteSysMarvalInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); implementation uses Math, Main, DataMod, GenFns, DateFunctions, FileFunctions, CriticalTests, MyMessage, Progress; const // Declare preset values, like default values determined from viewing data. DEFAULT_TRANSACTIONTYPEID = 65; // In case TransactionTypeID not found. var // Declare static variables as frequently reset and reused. nCount: Integer; // Current status for frmProgress. nFactor: Real; // Weighting for nCount in progress. frmProgress: TProgressForm; // frmProgress report to the operator. slAddFieldNames : TStringList; // Calls to AddToTable() only. slAddFieldValues : TStringList; // Calls to AddToTable() only. procedure SetupForNewTransactionTypes; begin with slAddFieldNames do begin Clear; Add('Description'); Add('TransactionGroupID'); Add('SystemID'); Add('Active'); Add('StartDate'); Add('LastUpdatedBy'); end; // slAddFieldNames. end; // SetupForNewTransactionTypes. procedure AddNewTransactionType ( const sDescription : string; const nTransactionGroupID : LongInt; const nSystemID : LongInt ); begin with slAddFieldValues do begin Clear; // Description Add(''''+ sDescription +''''); // TransactionGroupID Add(IntToStr(nTransactionGroupID)); // SystemID Add(IntToStr(nSystemID)); // Active Add('1'); // StartDate Add(DateToStr(Now)); // LastUpdatedBy Add('''TRANS-MIT'''); end; // slAddFieldValues. AddToTable('tTransactionType', slAddFieldNames, slAddFieldValues); end; // InstallNewTransactionType. procedure GetMarvalQuotePurchases ( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const lAnyTime : boolean; var lReturnCode: Boolean ); // 080629: New field ITHELPUSER saved to Var09 for Jobs and Purchases. var dLogDateTime : TDateTime; sBillPeriod : string; sFromDate : string; sToDate : string; sBatch : string; sBatchDescription : string; curAmtExGST : currency; curAmtGST : currency; sBatchTypeID : string; nSystemID : integer; sSystemID : string; nTransactionGroupID : integer; vDescription : variant; lFound : boolean; sTransactionTypeID : string; sCode : string; sDescription : string; sShipTo : string; sServiceID : string; sServiceTypeID : string; slTransactionTypeName : TStringList; slTransactionTypeID : TStringList; nTransactionType : integer; i : integer; nPos : integer; sSubString : string; sListValues : String; sListFields : String; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; qryDC : TQuery; qryQuoteSystem : TQuery; qryLook : TQuery; qryTT : TQuery; nListValues : integer; nTotalCount : integer; nBlock : integer; Factor : Real; lGSTFlag : boolean; fGSTRate : Real; MessageForm : TMessagesForm; begin sBatch := IntToStr(nBatchNumber); sBillPeriod := IntToStr(Year(dStartDate)) +'/'+ Right('0'+IntToStr(Month(dStartDate)),2); sFromDate := FormattedDateString(dStartDate); sToDate := FormattedDateString(dEndDate); sBatchDescription := BatchDescription; fGSTRate := MAINFORM.GST_RATE; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryStringList.Capacity := 200; // Establish the query to be used to save the data read. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryQuoteSystem := TQuery.Create(MainForm); qryQuoteSystem.DatabaseName := 'dbMQuote'; qryLook := TQuery.Create(MainForm); qryLook.DatabaseName := 'dbPPdata'; qryTT := TQuery.Create(MainForm); qryTT.DatabaseName := 'dbPPdata'; lReturnCode := True; nFactor := 100.0; nCount := 0; frmProgress := TProgressForm.Create(MainForm); frmProgress.btnCancel.Visible := False; frmProgress.Show; frmProgress.Caption := 'Marval Quote System Purchases Batch: ' + sBatch; frmProgress.Msg.Caption := 'Reading ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; Factor := 0.1000; // Count to 100 every 1000 records. nTotalCount := 0; nBlock := 0; // Determine the SystemID - used for searching the tTransasactionType table. with qryTT do begin SQL.Add('SELECT BatchTypeID'); SQL.Add('FROM tBatch'); SQL.Add('WHERE ID = '+sBatch); Open; end; // qryTT. if qryTT.Eof then begin sBatchTypeID := '0'; nSystemID := 0; end else begin sBatchTypeID := IntToStr(qryTT.FieldByName('BatchTypeID').AsInteger); with qryTT do begin Close; SQL.Clear; SQL.Add('SELECT ID'); SQL.Add('FROM tSystem'); SQL.Add('WHERE BatchTypeID = '+sBatchTypeID); Open; end; // qryTT. if qryTT.Eof then begin nSystemID := 0; end else begin nSystemID := qryTT.FieldByName('ID').AsInteger; end; end; // qryTT.Eof. sSystemID := IntToStr(nSystemID); with qryTT do begin Close; SQL.Clear; SQL.Add('SELECT ID, Description, TransactionGroupID'); SQL.Add('FROM tTransactionType'); SQL.Add('WHERE SystemID = '+sSystemID); SQL.Add(' AND Active = 1'); Open; if not Eof then begin nTransactionGroupID := qryTT.FieldByName('TransactionGroupID').AsInteger; end else begin nTransactionGroupID := 3; // Default = 'OTHER'. end; Close; // Reset to only the fields required. SQL.Strings[0] := 'SELECT ID, Description'; Open; end; // qryTT. with qryStringList do begin Add('SELECT *'); Add('FROM BillPurchasing'); Add('WHERE BillPurchasing.BillPeriod LIKE '''+sBillPeriod+''''); if lAnyTime then begin Add(' AND (BillPurchasing.ppTaken IS NULL OR '+ 'BillPurchasing.ppTaken = 0)'); end; Add('ORDER BY BillPurchasing.LogDateTime'); end; // qryStringList. // qryQuoteSystem.SQL.Text := qryStringList.Text; qryQuoteSystem.SQL := qryStringList; qryQuoteSystem.Open; qryStringList.Clear; //**************************************************************** //**************************************************************** //********* START READING BILLS INTO 'tDataCollection' *********** //**************************************************************** if qryQuoteSystem.Eof then begin MessageDlg('No entries found.', mtWarning, [mbOk], 0); lReturnCode := False; end else begin with qryLook do begin SQL.Add('SELECT Code, Description'); SQL.Add('FROM tServiceType'); SQL.Add('WHERE ID ='); end; // qryLook. while not qryQuoteSystem.Eof do begin frmProgress.ProcessWinMessages(MainForm); FieldStringList.Clear; ValueStringList.Clear; 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 := 'WPV'; // Default. sDescription := 'PURCHASING'; end else begin sCode := 'WPC'; // Default. sDescription := 'PURCHASING INSTALL EQPT'; 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 // Found - update the sCode value. 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 := 'WPV'; // Default. sDescription := 'PURCHASING'; end else begin sCode := 'WPC'; // Default. sDescription := 'PURCHASING INSTALL EQPT'; 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('PURCHASING '+ 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 - 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 + ','; // Save the values. 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').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['PurchaseOrder']) then begin sListFields := sListFields + 'Var07,'; // PurchaseOrder. sListValues := sListValues +''''+ qryQuoteSystem.FieldByName('PurchaseOrder').AsString +''','; end; if not VarIsNull(qryQuoteSystem['PurchaseOrderDate']) then begin sListFields := sListFields + 'Var08,'; // PurchaseOrderDate. sListValues := sListValues +''''+ StandardDateTimeString( qryQuoteSystem.FieldByName('PurchaseOrderDate').AsDateTime) +''','; 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['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; Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin // and not AbortProcess frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); nBlock := 0; if ((nCount * Factor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * Factor)>100.00) then. frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); frmProgress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. frmProgress.ProcessWinMessages(MainForm); qryQuoteSystem.Next; end; // while not qryQuoteSystem.Eof do begin if lReturnCode then begin frmProgress.Hide; MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := sBatch +': Quote System ...'; MessageForm.Msg.Caption := 'Updating tBatch data ...'; MessageForm.Show; frmProgress.ProcessWinMessages(MainForm); // 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); end; // qryStringList. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); MessageForm.Close; MessageForm.Free; end; // if lReturnCode then begin. end; // qryQuoteSystem.Eof. // Done with the input file; close it. FieldStringList.Free; ValueStringList.Free; frmProgress.Close; frmProgress.Free; qryStringList.Free; qryDC.Close; qryDC.Free; qryQuoteSystem.Close; qryQuoteSystem.Free; end; // GetMarvalQuotePurchases. procedure GetMarvalQuoteJobs ( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const lAnyTime : boolean; var lReturnCode: Boolean ); // 080629: New field ITHELPUSER saved to Var09 for Jobs and Purchases. var dLogDateTime : TDateTime; sBillPeriod : string; sFromDate : string; sToDate : string; sBatch : string; sBatchDescription : string; curAmtExGST : currency; curAmtGST : currency; sBatchTypeID : string; nSystemID : integer; sSystemID : string; nTransactionGroupID : integer; vDescription : variant; lFound : boolean; sTransactionTypeID : string; sCode : string; sDescription : string; sShipTo : string; sServiceID : string; sServiceTypeID : string; slTransactionTypeName : TStringList; slTransactionTypeID : TStringList; nTransactionType : integer; i : integer; nPos : integer; sSubString : string; sListValues : String; sListFields : String; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; qryDC : TQuery; qryQuoteSystem : TQuery; qryLook : TQuery; qryTT : TQuery; nListValues : integer; nTotalCount : integer; nBlock : integer; Factor : Real; lGSTFlag : boolean; fGSTRate : Real; MessageForm : TMessagesForm; begin sBatch := IntToStr(nBatchNumber); sBillPeriod := IntToStr(Year(dStartDate)) +'/'+ Right('0'+IntToStr(Month(dStartDate)),2); sFromDate := FormattedDateString(dStartDate); sToDate := FormattedDateString(dEndDate); sBatchDescription := BatchDescription; fGSTRate := MAINFORM.GST_RATE; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryStringList.Capacity := 200; // Establish the query to be used to save the data read. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryQuoteSystem := TQuery.Create(MainForm); qryQuoteSystem.DatabaseName := 'dbMQuote'; qryLook := TQuery.Create(MainForm); qryLook.DatabaseName := 'dbPPdata'; qryTT := TQuery.Create(MainForm); qryTT.DatabaseName := 'dbPPdata'; lReturnCode := True; nFactor := 100.0; nCount := 0; frmProgress := TProgressForm.Create(MainForm); frmProgress.btnCancel.Visible := False; frmProgress.Show; frmProgress.Caption := 'Marval Quote System Jobs Batch: ' + sBatch; frmProgress.Msg.Caption := 'Reading ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; Factor := 0.1000; // Count to 100 every 1000 records. nTotalCount := 0; nBlock := 0; // Determine the SystemID - used for searching the tTransasactionType table. with qryTT do begin SQL.Add('SELECT BatchTypeID'); SQL.Add('FROM tBatch'); SQL.Add('WHERE ID = '+sBatch); Open; end; // qryTT. if qryTT.Eof then begin sBatchTypeID := '0'; nSystemID := 0; end else begin sBatchTypeID := IntToStr(qryTT.FieldByName('BatchTypeID').AsInteger); with qryTT do begin Close; SQL.Clear; SQL.Add('SELECT ID'); SQL.Add('FROM tSystem'); SQL.Add('WHERE BatchTypeID = '+sBatchTypeID); Open; end; // qryTT. if qryTT.Eof then begin nSystemID := 0; end else begin nSystemID := qryTT.FieldByName('ID').AsInteger; end; end; // qryTT.Eof. sSystemID := IntToStr(nSystemID); with qryTT do begin Close; SQL.Clear; SQL.Add('SELECT ID, Description, TransactionGroupID'); SQL.Add('FROM tTransactionType'); SQL.Add('WHERE SystemID = '+sSystemID); SQL.Add(' AND Active = 1'); Open; if not Eof then begin nTransactionGroupID := qryTT.FieldByName('TransactionGroupID').AsInteger; end else begin nTransactionGroupID := 3; // Default = 'OTHER'. end; Close; // Reset to only the fields required. SQL.Strings[0] := 'SELECT ID, Description'; Open; end; // qryTT. with qryStringList do begin Add('SELECT *'); Add('FROM BillJobs'); Add('WHERE BillJobs.BillPeriod LIKE '''+sBillPeriod+''''); if lAnyTime then begin Add(' AND (BillJobs.ppTaken IS NULL OR '+ 'BillJobs.ppTaken = 0)'); end; Add('ORDER BY BillJobs.LogDateTime'); end; // with qryStringList. qryQuoteSystem.SQL := qryStringList; qryQuoteSystem.Open; qryStringList.Clear; //**************************************************************** //**************************************************************** //********* START READING BILLS INTO 'tDataCollection' *********** //**************************************************************** if qryQuoteSystem.Eof then begin MessageDlg('No entries found.', mtWarning, [mbOk], 0); lReturnCode := False; end else begin with qryLook do begin SQL.Add('SELECT Code, Description'); SQL.Add('FROM tServiceType'); SQL.Add('WHERE ID ='); end; // qryLook. while not qryQuoteSystem.Eof do begin frmProgress.ProcessWinMessages(MainForm); FieldStringList.Clear; ValueStringList.Clear; 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 + ','; 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; Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin // and not AbortProcess frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); nBlock := 0; if ((nCount * Factor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * Factor)>100.00) then. frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); frmProgress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. frmProgress.ProcessWinMessages(MainForm); qryQuoteSystem.Next; end; // while not qryQuoteSystem.Eof do begin if lReturnCode then begin frmProgress.Hide; MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := sBatch +': Quote System ...'; MessageForm.Msg.Caption := 'Updating tBatch data ...'; MessageForm.Show; frmProgress.ProcessWinMessages(MainForm); // 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); end; // qryStringList. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); MessageForm.Close; MessageForm.Free; end; // if lReturnCode then begin. end; // qryQuoteSystem.Eof. // Done with the input file; close it. FieldStringList.Free; ValueStringList.Free; frmProgress.Close; frmProgress.Free; qryStringList.Free; qryDC.Close; qryDC.Free; qryQuoteSystem.Close; qryQuoteSystem.Free; end; // GetMarvalQuoteJobs. procedure QuoteSysMarvalPurchasesInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); var lContinue : Boolean; EmptyList : TStringList; begin EmptyList := TStringList.Create; // Removes all entries for the batch. lContinue := True; // If lAnytime then tDataCollection has been processed first - else, restart. // if not lAnyTime then DeleteFromDC( nBatch, EmptyList, lContinue); DeleteFromDC( nBatch, EmptyList, lContinue); EmptyList.Free; // Initiate values for the addition of any new entries in tTransactionType. slAddFieldValues := TStringList.Create; // Used in calls to AddToTable() only. slAddFieldNames := TStringList.Create; // Used in calls to AddToTable() only. SetupForNewTransactionTypes; GetMarvalQuotePurchases( nBatch, dStartDate, dEndDate, BatchDescription, lAnyTime, lContinue); end; // QuoteSysMarvalPurchasesInvoice. procedure QuoteSysMarvalJobsInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); var lContinue : Boolean; EmptyList : TStringList; begin EmptyList := TStringList.Create; // Removes all entries for the batch. lContinue := True; // If lAnytime then tDataCollection has been processed first - else, restart. // if not lAnyTime then DeleteFromDC( nBatch, EmptyList, lContinue); DeleteFromDC( nBatch, EmptyList, lContinue); EmptyList.Free; // Initiate values for the addition of any new entries in tTransactionType. slAddFieldValues := TStringList.Create; // Used in calls to AddToTable() only. slAddFieldNames := TStringList.Create; // Used in calls to AddToTable() only. SetupForNewTransactionTypes; GetMarvalQuoteJobs( nBatch, dStartDate, dEndDate, BatchDescription, lAnyTime, lContinue); end; // QuoteSysMarvalJobsInvoice. procedure QuoteSysMarvalInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); var lContinue : Boolean; EmptyList : TStringList; begin EmptyList := TStringList.Create; // Removes all entries for the batch. lContinue := True; // If lAnytime then tDataCollection has been processed first - else, restart. // if not lAnyTime then DeleteFromDC( nBatch, EmptyList, lContinue); DeleteFromDC( nBatch, EmptyList, lContinue); EmptyList.Free; // Initiate values for the addition of any new entries in tTransactionType. slAddFieldValues := TStringList.Create; // Used in calls to AddToTable() only. slAddFieldNames := TStringList.Create; // Used in calls to AddToTable() only. SetupForNewTransactionTypes; GetMarvalQuotePurchases( nBatch, dStartDate, dEndDate, BatchDescription, lAnyTime, lContinue); if lContinue then GetMarvalQuoteJobs( nBatch, dStartDate, dEndDate, BatchDescription, lAnyTime, lContinue); end; // QuoteSysMarvalInvoice. end. // QuoteSystemMarval.