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.