unit AnnualCharges;

interface
                                
uses
  Messages, SysUtils, Classes, Graphics, Controls, Dialogs,
  db, dbTables;

  procedure AnnualChargesInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime;
    const BatchDescription : string; nBatchTypeID : integer);
  procedure GetAnnualCharges(
    const nBatchNumber : LongInt;
    const dStartDate, dEndDate : TDateTime;
    const BatchDescription : string;
    const nBatchTypeID : integer;
    var lReturnCode:Boolean);


implementation

uses
  Math,
  Main, DataMod, GenFns, DateFunctions, FileFunctions,
  CriticalTests, MyMessage, Progress;

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.

procedure GetAnnualCharges(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const nBatchTypeID : integer;
           var lReturnCode: Boolean );
var
  sFromDate : string;
  sToDate : string;
  sBatch : string;
  sProcessDate : string;
  sProcessTime : string;

  sListValues : String;
  sListFields : String;
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  qryDC : TQuery;
  qryAnnual : TQuery;
  nListValues : integer;
  sMonth : string;
  sMonthID : string;
  sQuarterID : string;
  sHalfID : string;
  sYearID : string;
  sSystemID : string;
  sVRTID : string;
  sTransaction : string;
  nTotalCount : integer;
  nBlock : integer;
  Factor : Real;
  sGSTRate : string;
  MessageForm : TMessagesForm;
{
  lNoErrorsFound : boolean;
}
begin
  sBatch := IntToStr(nBatchNumber);
  sFromDate := FormattedDateString(dStartDate);
  sToDate := FormattedDateString(dEndDate);
  sMonth := IntToStr(Month(dStartDate));   // Query selects annual charge month.
  Str(MAINFORM.GST_RATE:6:4, sGSTRate);

  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';
  qryAnnual := TQuery.Create(MainForm);
  qryAnnual.DatabaseName := 'dbPPdata';

  lReturnCode := True;
  nFactor := 100.0;
  nCount := 0;

  frmProgress := TProgressForm.Create(MainForm);
  frmProgress.btnCancel.Visible := False;
  frmProgress.Show;

  frmProgress.Caption := 'Annual Charges Batch: ' + sBatch;
  frmProgress.Msg.Caption := 'Reading ...';
  frmProgress.ProgressBar1.Position := 0;
  frmProgress.Msg.Refresh;
  Factor := 0.1000;        // Count to 100 every 1000 records.
  nTotalCount := 1;
  nBlock := 0;

  sMonthID := '0';                                  // Define transaction types.
  sQuarterID := '0';
  sHalfID := '0';
  sYearID := '0';
  qryDC.SQL.Add('SELECT ID, Description');
  qryDC.SQL.Add('FROM tTransactionType');
  qryDC.SQL.Add('WHERE Description LIKE ''%Annual Charge%''');
  qryDC.Open;
  while not qryDC.Eof do begin
    sTransaction := qryDC.FieldByName('Description').AsString;
    if (Pos('Month',sTransaction) > 0) then
      sMonthID := IntToStr(qryDC['id'])
    else if (Pos('Quarter',sTransaction) > 0) then
      sQuarterID := IntToStr(qryDC['id'])
    else if (Pos('Six',sTransaction) > 0) then
      sHalfID := IntToStr(qryDC['id'])
    else if (Pos('Year',sTransaction) > 0) then
      sYearID := IntToStr(qryDC['id']);
    qryDC.Next
  end;
  qryDC.Close;
  qryDC.SQL.Clear;
  qryDC.SQL.Add('SELECT ID');
  qryDC.SQL.Add('FROM tCustomer');
  qryDC.SQL.Add('WHERE ShipTo LIKE ''VRT''');
  qryDC.Open;
  if qryDC.Eof then sVRTID := '0'
  else              sVRTID := IntToStr(qryDC.FieldByName('ID').AsInteger);
  qryDC.Close;
  qryDC.SQL.Clear;
  qryDC.SQL.Add('SELECT ID');
  qryDC.SQL.Add('FROM tSystem');
  qryDC.SQL.Add('WHERE BatchTypeID = '+IntToStr(nBatchTypeID));
  qryDC.SQL.Add('AND Active = 1');
  qryDC.Open;
  if qryDC.Eof then sSystemID := '0'
  else              sSystemID := IntToStr(qryDC.FieldByName('ID').AsInteger);
  qryDC.Close;
  qryDC.SQL.Clear;

  with qryStringList do begin
    Add('SELECT AC.*');
    Add('FROM (SELECT tServiceID.ServiceID, tServiceID.Description, '+
      'tServiceID.ServiceTypeID, tServiceType.Code, (CASE');
    Add(' WHEN tServiceType.Frequency = 1 THEN '''+sMonthID+'''');
    Add(' WHEN tServiceType.Frequency = 3 THEN '''+sQuarterID+'''');
    Add(' WHEN tServiceType.Frequency = 6 THEN '''+sHalfID+'''');
    Add(' WHEN tServiceType.Frequency = 12 THEN '''+sYearID+'''');
    Add(' ELSE ''0''');
    Add('END) AS "TransactionTypeID", (CASE');
    Add(' WHEN tGroup.CustomerID = '+sVRTID+' THEN ''N'''); // VRT.
    Add(' ELSE ''Y''');
    Add('END) AS "GSTFlag", (CASE');
    Add(' WHEN tServiceType.Frequency IS NULL THEN 1');
    Add(' WHEN tServiceType.Frequency = 0 THEN 1');
    Add(' WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+
      ' AS Real) / tServiceType.Frequency) =');
    Add('  ROUND((Month(tServiceID.InstallationDate)-'+sMonth+
      ') / tServiceType.Frequency,0,0) THEN 0');
    Add(' ELSE 1');
    Add('END) AS "Period"');
    Add('FROM (((tServiceID');
    Add('LEFT JOIN tServiceType '+
      'ON tServiceID.ServiceTypeID = tServiceType.ID)');
    Add('LEFT JOIN tCentre '+
      'ON tServiceID.CustomerCentreID = tCentre.ID)');
    Add('LEFT JOIN tDepartment '+
      'ON tCentre.DepartmentID = tDepartment.ID)');
    Add('LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID');
    Add('WHERE tServiceID.Active = 1');     // Only when current.
    Add('AND tServiceType.SystemID = '+sSystemID+') AS AC');// Annuity.
    Add('WHERE AC.Period = 0');
  end;  // qryStringList.
  qryAnnual.SQL := qryStringList;
  qryAnnual.Open;
  qryStringList.Clear;

  //****************************************************************
  //********* START READING BILLS INTO 'tDataCollection' ***********
  //****************************************************************
  if qryAnnual.Eof then begin
    MessageDlg('No chargeable services found.', mtWarning, [mbOk], 0);
    lReturnCode := False;
  end
  else begin
    sProcessDate := ''''+ FormattedDateString(Now) +''',';
    sProcessTime := ''''+ TimeToStr(Now) +''',';

    // NOTE: Charge amounts added below AFTER ALL entries added to tDataCollection.
    while not qryAnnual.Eof do begin
      frmProgress.ProcessWinMessages(MainForm);

      FieldStringList.Clear;
      ValueStringList.Clear;
      sListFields := '(';
      sListValues := 'VALUES (';
      sListFields := sListFields + 'SequenceNo,';
      sListValues := sListValues + ''''+
        Right('0000'+IntToStr(nTotalCount),5)+''',';
      sListFields := sListFields + 'ServiceID,';
      sListValues := sListValues +''''+
        qryAnnual.FieldByName('ServiceID').AsString +''',';
      sListFields := sListFields + 'ServiceType,';
      sListValues := sListValues +''''+
        qryAnnual.FieldByName('Code').AsString+''',';
      sListFields := sListFields + 'TxnDate,';
      sListValues := sListValues + sProcessDate;
      sListFields := sListFields + 'TxnTime,';
      sListValues := sListValues + sProcessTime;
      sListFields := sListFields + 'TransactionTypeID,';
      sListValues := sListValues +
        qryAnnual.FieldByName('TransactionTypeID').AsString+',';
      FieldStringList.Add(sListFields);
      ValueStringList.Add(sListValues);
      sListFields := '';
      sListValues := '';
      sListFields := sListFields + 'GSTFlag,';
      sListValues := sListValues + ''''+
        qryAnnual.FieldByName('GSTFlag').AsString +''',';
      sListFields := sListFields + 'FromDate,';
      sListValues := sListValues +''''+ sFromDate +''',';
      sListFields := sListFields + 'ToDate,';
      sListValues := sListValues +''''+ sToDate +''',';
      sListFields := sListFields + 'BatchID)';           // BatchID.
      sListValues := sListValues + sBatch +')';

      // Now make the SQL statement to save to the tDataCollection 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);
      qryAnnual.Next;
    end;  // while not qryAnnual.Eof do begin
    if lReturnCode then begin
      frmProgress.Hide;
      MessageForm := TMessagesForm.Create(MainForm);
      MessageForm.Caption := 'Calculating Annual Charges ...';
      MessageForm.Msg.Caption := 'Updating with applied rates ...';
      MessageForm.Show;
      frmProgress.ProcessWinMessages(MainForm);
      qryStringList.Clear;
      qryStringList.Add('UPDATE tDataCollection');
      qryStringList.Add('SET AmountExGST = ChargeRate.AmtExGST,');
      qryStringList.Add(' GSTAmount = ChargeRate.GSTAmt,');
      qryStringList.Add(' AmountIncGST = ChargeRate.AmtExGST + ChargeRate.GSTAmt');
      qryStringList.Add('FROM (SELECT GSTrate.ID, GSTrate.AmtExGST,');
      qryStringList.Add('(GSTrate.AmtExGST * GSTrate.GSTFactor) AS GSTAmt');
      qryStringList.Add('FROM (SELECT tDataCollection.ID, '+
        '(CASE WHEN tDataCollection.GSTFlag LIKE ''Y'' '+
        'THEN '+sGSTRate+' ELSE 0 END) AS "GSTFactor",');
      qryStringList.Add('(tRate.Cost / 12 * tServiceType.Frequency) AS "AmtExGST"');
      qryStringList.Add('FROM ((tDataCollection');
      qryStringList.Add('LEFT JOIN tServiceID '+
        'ON tDataCollection.ServiceID = tServiceID.ServiceID)');
      qryStringList.Add('LEFT JOIN tServiceType '+
        'ON tServiceID.ServiceTypeID = tServiceType.ID)');
// Amend to select only active trate values - RJC 170116:        
      qryStringList.Add('LEFT JOIN tRate '+
        'ON ((tServiceID.ServiceTypeID = tRate.ServiceTypeID) AND '+
             (tRate.Active = 1))');
      qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch);
      qryStringList.Add('AND tRate.Cost IS NOT NULL) AS GSTrate) AS ChargeRate');
      qryStringList.Add('WHERE tDataCollection.ID = ChargeRate.ID');
      qryDC.Close;
      qryDC.SQL := qryStringList;
      qryDC.ExecSQL;

      // 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);
{
      lNoErrorsFound := TRUE;
      CriticalErrorTest(nBatchNumber, lNoErrorsFound);
      if lNoErrorsFound then begin
        MessageDlg('Batch '+ sBatch +
          ': Passed all tests for Critical Errors.', mtInformation,
          [mbOk], 0);
      end
      else begin
        MessageDlg('Batch '+ sBatch +
          ': Critical errors found.'#13#10#13#10'These must be resolved ' +
          'before this invoice can be progressed.',
          mtWarning, [mbOk], 0);
      end;  // if lNoErrorsFound.
}
      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;
  qryAnnual.Close;
  qryAnnual.Free;
end;  // GetAnnualCharges.


procedure AnnualChargesInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime;
  const BatchDescription : string; nBatchTypeID : integer);
var
  lContinue : Boolean;
  EmptyList : TStringList;
begin
  EmptyList := TStringList.Create;         // Removes all entries for the batch.
  lContinue := True;
  DeleteFromDC( nBatch, EmptyList, lContinue);
  EmptyList.Free;
  GetAnnualCharges( nBatch, dStartDate, dEndDate, BatchDescription,
    nBatchTypeID, lContinue);
end;  // AnnualChargesInvoice.

end.  // AnnualCharges.