ProgramID: 2304|AnyTime QuoteSys Purchasing procedure QuoteSystemPurchasesInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string; const lAnyTime : boolean); //Delete From tDatacollection table DeleteFromDC( nBatch, EmptyList, lContinue); // init field names for slAddFieldName // (I missied including this for 2303, but it called the same, (see below)) SetupForNewTransactionTypes; GetQuotePurchases( nBatch, dStartDate, dEndDate, BatchDescription, lAnyTime, lContinue); end; // QuoteSystemPurchasesInvoice. 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 procedure GetQuotePurchases ( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const lAnyTime : boolean; var lReturnCode: Boolean ); // Establish the query to be used to save the data read. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryQuoteSystem := TQuery.Create(MainForm); qryQuoteSystem.DatabaseName := 'dbQuote'; qryLook := TQuery.Create(MainForm); qryLook.DatabaseName := 'dbPPdata'; qryTT := TQuery.Create(MainForm); qryTT.DatabaseName := 'dbPPdata'; frmProgress.Caption := 'Quote System Purchases Batch: ' + sBatch; frmProgress.Msg.Caption := 'Reading ...'; // Determine the SystemID - used for searching the tTransasactionType table. qryTT.DatabaseName := 'dbPPdata'; 'SELECT BatchTypeID 'FROM tBatch 'WHERE ID = '+sBatch if NOT FOUND sBatchTypeID := '0'; nSystemID := 0; else begin sBatchTypeID := FOUND BatchTypeID qryBT.DatabaseName := 'dbPPdata'; with qryBT do begin 'SELECT ID 'FROM tSystem 'WHERE BatchTypeID = '+sBatchTypeID end; // qryBT. if NOT FOIUND nSystemID := 0; else begin nSystemID := FOUND 'ID' end; end; // qryTT.Eof. sSystemID := IntToStr(nSystemID); // setup for transactiontype query qryTT.DatabaseName := 'dbPPdata'; with qryTT do begin 'SELECT ID, Description, TransactionGroupID 'FROM tTransactionType 'WHERE SystemID = '+sSystemID ' AND Active = 1 if FOUND nTransactionGroupID := FOUND 'TransactionGroupID else begin nTransactionGroupID := 3; // Default = 'OTHER'. end; // Reset to only the fields required. SQL.Strings[0] := 'SELECT ID, Description'; Open; end; // qryTT. // 170416: ppTaken added to the BillPurchasing view by RLD. qryQuoteSystem.DatabaseName := 'dbQuote'; qryQuoteSystem.SQL := with qryStringList do begin 'SELECT * 'FROM BillPurchasing 'WHERE BillPurchasing.BillPeriod LIKE '''+sBillPeriod+''' if lAnyTime then begin ' AND (BillPurchasing.ppTaken IS NULL OR '+ 'BillPurchasing.ppTaken = 0)'); end; 'ORDER BY BillPurchasing.LogDateTime' end; // qryStringList. // 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 // (NO quote data) MessageDlg('No entries found.', mtWarning, [mbOk], 0); lReturnCode := False; else begin // NOTE: Charge amounts added below AFTER ALL entries added to tDataCollection. // LOOP on Qute 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 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; 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; 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. qryStringList.Clear; qryStringList.Add('UPDATE tBatch'); qryStringList.Add('SET GSTAmount = BatchRate.TotalGST,'); qryStringList.Add(' TotalPayable = BatchRate.TotalIncGST'); qryStringList.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); qryStringList.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch + ') AS BatchRate'); qryStringList.Add('WHERE tBatch.ID = '+ sBatch); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); MessageForm.Close; MessageForm.Free; end; // if lReturnCode then begin. end; // Quetzal faults found. // 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; // GetQuotePurchases. ZZZZZZZ // Additional routenes not supplied with 2303 info, but called by 2303 import // (slAddFieldNames is a list of strings for field names) 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; Add(''''+ sDescription +''''); Add(IntToStr(nTransactionGroupID)); Add(IntToStr(nSystemID)); // Active Add('1'); // StartDate Add(DateToStr(Now)); // LastUpdatedBy Add('''TRANS-MIT'''); end; // slAddFieldValues. AddToTable('tTransactionType', slAddFieldNames, slAddFieldValues); end; // InstallNewTransactionType. procedure UpdateBatchStatus(const nThisBatch, nStatus: LongInt); var qryBA.DatabaseName := 'dbPPdata'; 'SELECT BatchStatusID FROM tBatch 'WHERE (ID = +nThisBatch if FOUND // (Test avoids a possible error - but can't get here unless it exists). if (qryBA['BatchStatusID'] <> nStatus) then begin // Update the tBatch table qryBA.DatabaseName := 'dbPPdata'; 'UPDATE tBatch 'SET BatchStatusID = +nStatus 'FROM tBatch 'WHERE ID = '+nThisBatch end; // if (qryDC['BatchStatusID'] <> nOutcome) then. // Update the tBatchStatusHistory table. qryBA.DatabaseName := 'dbPPdata'; 'INSERT INTO tBatchStatusHistory ' (BatchID, BatchStatusID, StatusChangeDate,UserID) ' VALUES ('+IntToStr(nBatch)+','+IntToStr(nOutcome)+','''+StandardDateString(Now)+''','+IntToStr(MainForm.OPERATORNUMBER)+')') ; end; // if qryDC.Eof and qryDC.Bof then. // Now update the list of viewable Batch numbers in tDataCollection // (in TabTransactionSummary). MainForm.SetBillingPeriodBatch; end; // UpdateBatchStatus(). procedure TMainForm.SetBillingPeriodBatch; var sTransactionBatchIDWas : string; nTransactionBatchIDWas : integer; qrySetBatch.DatabaseName := 'dbPPdata'; 'SELECT tBatch.id, tBatch.BatchStatusID'); 'FROM (tBatch'); 'LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.I D)'LEFT JOIN tDataSourceType '+ 'ON tBatchType.DataSourceTypeID = tDataSourceType.ID'); 'WHERE tBatch.BillingPeriod LIKE '''+cbBillingPeriod.Text+ '''); 'AND tBatch.BatchStatusID > '+IntToStr(BATCHSTAT_INIT)); 'AND tBatch.BatchStatusID < '+IntToStr(BATCHSTAT_TRANSNS)) ; if rbBatchTransactionsAuto.Checked then 'AND tDataSourceType.DataSource LIKE ''%Automatic%'''); 'ORDER BY tBatch.ID DESC'); ZZZZZZ // create list of found tBatrch ID's while qrySetBatch.Eof do begin cbBatchTransactions.Items.Add(Trim(qrySetBatch['id'])); qrySetBatch.Next; end; //cbBatchTransaction is a TComboBox input on ht Main Form sTransactionBatchIDWas := cbBatchTransactions.Text; // search list for sTransactionBatchIDWas nTransactionBatchIDWas := 0; for iCount := 0 to cbBatchTransactions.Items.Count-1 do if (cbBatchTransactions.Items[iCount]=sTransactionBatchIDWas) then nTransactionBatchIDWas := iCount; cbBatchTransactions.ItemIndex := nTransactionBatchIDWas; { // The following 2 lines are for switching between All and Automatic results. RxDBGridTransactions.Visible := rbBatchTransactions.Checked; RxDBGridTransactionsAuto.Visible := rbBatchTransactionsAuto.Checked; } end; // SetBillingPeriodBatch.