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.