unit SY990;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, BaseDialog, StdCtrls, DB, DBTables, Buttons, ComCtrls;

type
  TfmSY990 = class(TfmBaseDialog)
    Memo1: TMemo;
    qyUpdateInventoryBeginning: TQuery;
    qyInventoryBeginning: TQuery;
    qyInsertInventoryBeginning: TQuery;
    ProgressBar: TProgressBar;
    qyInsertAccountReceivableBeginning: TQuery;
    qyUpdateAccountReceivableBeginning: TQuery;
    qyAccountReceivableBeginning: TQuery;
    qyAccountPayableBeginning: TQuery;
    qyUpdateAccountPayableBeginning: TQuery;
    qyInsertAccountPayableBeginning: TQuery;
    procedure bnOKClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
    procedure ProgressPostiotn(iPosition: Integer);
  public
    { Public declarations }
  end;

var
  fmSY990: TfmSY990;

implementation

uses DataModule, PublicFunction, Main;

{$R *.dfm}

procedure TfmSY990.bnOKClick(Sender: TObject);
var
  fBeginningQuantity, fBeginningBalance, fUnitPrice : Extended;
begin
  inherited;
  if not MyConfirmation('確定執行月結操作嗎？') then
    Exit;
  ProgressBar.Visible := True;
  ProgressPostiotn(4);
  with qyTemp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT COUNT(PaymentID) AS PaymentID ');
    SQL.Add('FROM AccountPayableMaster ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND PayDate >= :BeginPayDate AND PayDate <= :EndPayDate ');
    SQL.Add('AND PayAmount <> TotalBalance ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginPayDate').AsString := sStartPeriodDate;
    ParamByName('EndPayDate').AsString := sEndPeriodDate;
    Open;
  end;
  if qyTemp.FieldByName('PaymentID').AsInteger <> 0 then
  begin
    MyWarning('尚有' + IntToStr(qyTemp.FieldByName('PaymentID').AsInteger) +
              '張付款單的付款金額與實沖金額不等，月結操作停止！');
    ProgressBar.Visible := False;
    Abort;
  end;
  ProgressPostiotn(8);

  with qyTemp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT COUNT(ReceiveID) AS ReceiveID ');
    SQL.Add('FROM AccountReceivableMaster ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND ReceiveDate >= :BeginReceiveDate AND ReceiveDate <= :EndReceiveDate ');
    SQL.Add('AND ReceiveAmount <> TotalBalance ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginReceiveDate').AsString := sStartPeriodDate;
    ParamByName('EndReceiveDate').AsString := sEndPeriodDate;
    Open;
  end;
  if qyTemp.FieldByName('ReceiveID').AsInteger <> 0 then
  begin
    MyWarning('尚有' + IntToStr(qyTemp.FieldByName('ReceiveID').AsInteger) +
              '張收款沖賬單的收款金額與實沖金額不等，月結操作停止！');
    ProgressBar.Visible := False;
    Abort;
  end;
  ProgressPostiotn(12);

  with qyInsertInventoryBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('INSERT INTO InventoryBeginning ');
    SQL.Add(' (CompanyID, InventoryYear, InventoryMonth, WarehouseID, ');
    SQL.Add('  ProductID, BeginningQuantity, BeginningBalance) ');
    SQL.Add('VALUES ');
    SQL.Add(' (:CompanyID, :InventoryYear, :InventoryMonth, :WarehouseID, ');
    SQL.Add('  :ProductID, :BeginningQuantity, :BeginningBalance) ');
  end;
  with qyUpdateInventoryBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('UPDATE InventoryBeginning ');
    SQL.Add('SET ');
    SQL.Add('BeginningQuantity = BeginningQuantity + :BeginningQuantity, ');
    SQL.Add('BeginningBalance = :BeginningBalance ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND InventoryYear = :InventoryYear AND InventoryMonth = :InventoryMonth ');
    SQL.Add('AND WarehouseID = :WarehouseID AND ProductID = :ProductID ');
  end;

  with qyInventoryBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT ProductID, BeginningQuantity ');
    SQL.Add('FROM InventoryBeginning ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND InventoryYear = :InventoryYear ');
    SQL.Add('AND InventoryMonth = :InventoryMonth ');
    SQL.Add('AND WarehouseID = :WarehouseID ');
    SQL.Add('AND ProductID = :ProductID ');
  end;
  with qyInsertAccountReceivableBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('INSERT INTO AccountReceivableBeginning ');
    SQL.Add(' (CompanyID, BeginningYear, BeginningMonth, ');
    SQL.Add('  CustomerID, BeginningBalance) ');
    SQL.Add('VALUES ');
    SQL.Add(' (:CompanyID, :BeginningYear, :BeginningMonth, ');
    SQL.Add('  :CustomerID, :BeginningBalance) ');
  end;
  with qyUpdateAccountReceivableBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('UPDATE AccountReceivableBeginning ');
    SQL.Add('SET BeginningBalance = BeginningBalance + :BeginningBalance ');
    SQL.Add('WHERE :CompanyID = :CompanyID ');
    SQL.Add('AND BeginningYear = :BeginningYear ');
    SQL.Add('AND BeginningMonth = :BeginningMonth ');
    SQL.Add('AND CustomerID = :CustomerID ');
  end;
  with qyAccountReceivableBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT CustomerID ');
    SQL.Add('FROM AccountReceivableBeginning ');
    SQL.Add('WHERE :CompanyID = :CompanyID ');
    SQL.Add('AND BeginningYear = :BeginningYear ');
    SQL.Add('AND BeginningMonth = :BeginningMonth ');
    SQL.Add('AND CustomerID = :CustomerID ');
  end;
  with qyInsertAccountPayableBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('INSERT INTO AccountPayableBeginning ');
    SQL.Add(' (CompanyID, BeginningYear, BeginningMonth, ');
    SQL.Add('  SupplierID, BeginningBalance) ');
    SQL.Add('VALUES ');
    SQL.Add(' (:CompanyID, :BeginningYear, :BeginningMonth, ');
    SQL.Add('  :SupplierID, :BeginningBalance) ');
  end;
  with qyUpdateAccountPayableBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('UPDATE AccountPayableBeginning ');
    SQL.Add('SET BeginningBalance = BeginningBalance + :BeginningBalance ');
    SQL.Add('WHERE :CompanyID = :CompanyID ');
    SQL.Add('AND BeginningYear = :BeginningYear ');
    SQL.Add('AND BeginningMonth = :BeginningMonth ');
    SQL.Add('AND SupplierID = :SupplierID ');
  end;
  with qyAccountPayableBeginning do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT SupplierID ');
    SQL.Add('FROM AccountPayableBeginning ');
    SQL.Add('WHERE :CompanyID = :CompanyID ');
    SQL.Add('AND BeginningYear = :BeginningYear ');
    SQL.Add('AND BeginningMonth = :BeginningMonth ');
    SQL.Add('AND SupplierID = :SupplierID ');
  end;
  ProgressPostiotn(16);

  DM.DB.StartTransaction;
  try

    qyInventoryBeginning.Prepare;
    qyUpdateInventoryBeginning.Prepare;
    qyInsertAccountReceivableBeginning.Prepare;
    qyUpdateAccountReceivableBeginning.Prepare;
    qyInsertAccountPayableBeginning.Prepare;
    qyUpdateAccountPayableBeginning.Prepare;
    ProgressPostiotn(20);

    with qyTemp do
    begin
      Close;
      SQL.Clear;
      SQL.Add('DELETE FROM InventoryBeginning ');
      SQL.Add('WHERE CompanyID = :CompanyID ');
      SQL.Add('AND InventoryYear = :InventoryYear ');
      SQL.Add('AND InventoryMonth = :InventoryMonth ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('InventoryYear').AsString := LeftStr(sEndPeriodDate, 2);
      ParamByName('InventoryMonth').AsString := SubStr(sEndPeriodDate, 4, 2);
      ExecSQL;
    end;
    ProgressPostiotn(24);

    with qyTemp do
    begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT ProductID, WarehouseID, BeginningQuantity, BeginningBalance ');
      SQL.Add('FROM InventoryBeginning ');
      SQL.Add('WHERE CompanyID = :CompanyID ');
      SQL.Add('AND InventoryYear = :InventoryYear ');
      SQL.Add('AND InventoryMonth = :InventoryMonth ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('InventoryYear').AsString := LeftStr(sStartPeriodDate, 2);
      ParamByName('InventoryMonth').AsString := SubStr(sStartPeriodDate, 4, 2);
      Open;
    end;
    ProgressPostiotn(28);
    qyTemp.First;
    while not qyTemp.Eof do
    begin
      with qyInsertInventoryBeginning do
      begin
        ParamByName('CompanyID').AsString := sCompanyID;
        ParamByName('InventoryYear').AsString := LeftStr(sEndPeriodDate, 2);
        ParamByName('InventoryMonth').AsString := SubStr(sEndPeriodDate, 4, 2);
        ParamByName('WarehouseID').AsString := qyTemp.FieldByName('WarehouseID').AsString;
        ParamByName('ProductID').AsString := qyTemp.FieldByName('ProductID').AsString;
        ParamByName('BeginningQuantity').AsFloat := qyTemp.FieldByName('BeginningQuantity').AsFloat;
        ParamByName('BeginningBalance').AsFloat := qyTemp.FieldByName('BeginningBalance').AsFloat;
        ExecSQL;
      end;
      qyTemp.Next;
    end;
    ProgressPostiotn(32);

    with qyTemp do
    begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT A.InventoryYear, A.InventoryMonth, ');
      SQL.Add('A.WarehouseID, A.ProductID, C.AddOrSubtract, ');
      SQL.Add('SUM(A.TotalQuantity) AS TotalQuantity, ');
      SQL.Add('SUM(A.TotalAmount) AS TotalAmount ');
      SQL.Add('FROM InventoryAnalyst A, InventoryChange C ');
      SQL.Add('WHERE A.CompanyID = :CompanyID ');
      SQL.Add('AND A.InventoryYear = :InventoryYear ');
      SQL.Add('AND A.InventoryMonth = :InventoryMonth ');
      SQL.Add('AND A.CompanyID = C.CompanyID AND A.ChangeCode = C.ChangeCode ');
      SQL.Add('GROUP BY A.InventoryYear, A.InventoryMonth, ');
      SQL.Add('A.WarehouseID, A.ProductID, C.AddOrSubtract ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('InventoryYear').AsString := LeftStr(sStartPeriodDate, 2);
      ParamByName('InventoryMonth').AsString := SubStr(sStartPeriodDate, 4, 2);
      Open;
    end;
    ProgressPostiotn(36);
    qyTemp.First;
    while not qyTemp.Eof do
    begin
      if qyTemp.FieldByName('AddOrSubtract').AsString = '+' then

        fBeginningQuantity := qyTemp.FieldByName('TotalQuantity').AsFloat
      else

        fBeginningQuantity := qyTemp.FieldByName('TotalQuantity').AsFloat * -1;

      with qyInventoryBeginning do
      begin
        Close;
        ParamByName('CompanyID').AsString := sCompanyID;
        ParamByName('InventoryYear').AsString := LeftStr(sEndPeriodDate, 2);
        ParamByName('InventoryMonth').AsString := SubStr(sEndPeriodDate, 4, 2);
        ParamByName('WarehouseID').AsString := qyTemp.FieldByName('WarehouseID').AsString;
        ParamByName('ProductID').AsString := qyTemp.FieldByName('ProductID').AsString;
        Open;
      end;
      if qyInventoryBeginning.FieldByName('ProductID').AsString = '' then
      begin

        with qyInsertInventoryBeginning do
        begin
          ParamByName('CompanyID').AsString := sCompanyID;
          ParamByName('InventoryYear').AsString := LeftStr(sEndPeriodDate, 2);
          ParamByName('InventoryMonth').AsString := SubStr(sEndPeriodDate, 4, 2);
          ParamByName('WarehouseID').AsString := qyTemp.FieldByName('WarehouseID').AsString;
          ParamByName('ProductID').AsString := qyTemp.FieldByName('ProductID').AsString;
          ParamByName('BeginningQuantity').AsFloat := fBeginningQuantity;
          ParamByName('BeginningBalance').AsFloat := 0;
          ExecSQL;
        end;
      end
      else
      begin

        with qyUpdateInventoryBeginning do
        begin
          ParamByName('CompanyID').AsString := sCompanyID;
          ParamByName('InventoryYear').AsString := LeftStr(sEndPeriodDate, 2);
          ParamByName('InventoryMonth').AsString := SubStr(sEndPeriodDate, 4, 2);
          ParamByName('WarehouseID').AsString := qyTemp.FieldByName('WarehouseID').AsString;
          ParamByName('ProductID').AsString := qyTemp.FieldByName('ProductID').AsString;
          ParamByName('BeginningQuantity').AsFloat := fBeginningQuantity;
          ParamByName('BeginningBalance').AsFloat := 0;
          ExecSQL;
        end;
      end;
      qyTemp.Next;
    end;
    ProgressPostiotn(38);

    with qyTemp do
    begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT A.ProductID, A.WarehouseID, ');
      SQL.Add('SUM(A.TotalQuantity) AS TotalQuantity, ');
      SQL.Add('SUM(A.TotalAmount) AS TotalAmount ');
      SQL.Add('FROM InventoryAnalyst A, InventoryChange C ');
      SQL.Add('WHERE A.CompanyID = :CompanyID ');
      SQL.Add('AND A.InventoryYear = :InventoryYear ');
      SQL.Add('AND A.InventoryMonth = :InventoryMonth ');
      SQL.Add('AND C.AddOrSubtract = ''+'' ');
      SQL.Add('AND A.CompanyID = C.CompanyID AND A.ChangeCode = C.ChangeCode ');
      SQL.Add('GROUP BY A.ProductID, A.WarehouseID ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('InventoryYear').AsString := LeftStr(sStartPeriodDate, 2);
      ParamByName('InventoryMonth').AsString := SubStr(sStartPeriodDate, 4, 2);
      Open;
    end;
    qyTemp.First;
    while not qyTemp.Eof do
    begin

      with qyInventoryBeginning do
      begin
        Close;
        ParamByName('CompanyID').AsString := sCompanyID;
        ParamByName('InventoryYear').AsString := LeftStr(sEndPeriodDate, 2);
        ParamByName('InventoryMonth').AsString := SubStr(sEndPeriodDate, 4, 2);
        ParamByName('WarehouseID').AsString := qyTemp.FieldByName('WarehouseID').AsString;
        ParamByName('ProductID').AsString := qyTemp.FieldByName('ProductID').AsString;
        Open;
      end;
      fBeginningQuantity := qyInventoryBeginning.FieldByName('BeginningQuantity').AsFloat;

      if (qyTemp.FieldByName('TotalQuantity').AsFloat =  0) then
      begin
        with qyInventoryBeginning do
        begin
          Close;
          ParamByName('CompanyID').AsString := sCompanyID;
          ParamByName('InventoryYear').AsString := LeftStr(sStartPeriodDate, 2);
          ParamByName('InventoryMonth').AsString := SubStr(sStartPeriodDate, 4, 2);
          ParamByName('WarehouseID').AsString := qyTemp.FieldByName('WarehouseID').AsString;
          ParamByName('ProductID').AsString := qyTemp.FieldByName('ProductID').AsString;
          Open;
        end;
        if qyInventoryBeginning.FieldByName('BeginningQuantity').AsFloat <> 0 then
          fUnitPrice := RealToInt(qyInventoryBeginning.FieldByName('BeginningAmount').AsFloat /
                                  qyInventoryBeginning.FieldByName('BeginningQuantity').AsFloat)
        else

          fUnitPrice := 0;
      end
      else