apinvoicespostform.pas

功能全面的商业财会系统源码,清晰,很有参考价值.扩展性强.

unit APInvoicesPostForm;

interface

uses Windows, SysUtils, Classes, Graphics, Forms, Controls, StdCtrls,
  Buttons, ExtCtrls, ComCtrls, DB, DBTables, Mask, DBCtrls, Dialogs, BDE;

type
  TfrmAPInvoicesPost = class(TForm)
    btnOK: TButton;
    btnCancel: TButton;
    qryAPInv: TQuery;
    lblRecordCount: TLabel;
    ProgressBar1: TProgressBar;
    tblVBalance: TTable;
    tblVBalanceVendorID: TIntegerField;
    tblVBalanceBalance: TCurrencyField;
    tblJV: TTable;
    tblJVJVID: TIntegerField;
    tblJVGLYear: TSmallintField;
    tblJVGLPeriod: TSmallintField;
    tblJVJVNumber: TIntegerField;
    tblJVSource: TStringField;
    tblJVTransType: TStringField;
    tblJVTransDate: TDateField;
    tblJVTransDescription: TStringField;
    tblJVVendorID: TIntegerField;
    tblJVAPInvoiceID: TIntegerField;
    tblJVJVAmount: TCurrencyField;
    tblJVPosted: TBooleanField;
    tblJVAutoReverse: TBooleanField;
    qryAPInvAPInvoiceID: TIntegerField;
    qryAPInvVendorID: TIntegerField;
    qryAPInvInvoiceNo: TStringField;
    qryAPInvInvoiceAmount: TCurrencyField;
    qryAPInvGLPeriod: TSmallintField;
    qryAPInvGLYear: TSmallintField;
    qryAPInvPosted: TBooleanField;
    tblJVCtl: TTable;
    tblJVCtlNextJVID: TIntegerField;
    tblJV2: TTable;
    tblJVCtlNextJVNumber: TIntegerField;
    tblJV2GLYear: TSmallintField;
    tblJV2GLPeriod: TSmallintField;
    tblJV2JVNumber: TIntegerField;
    tblAPInvDet: TTable;
    tblAPInvDetAPInvoiceID: TIntegerField;
    tblAPInvDetGLAccount: TStringField;
    tblAPInvDetGLAmount: TCurrencyField;
    tblJVDet: TTable;
    tblJVDetJVID: TIntegerField;
    tblJVDetSeq: TIntegerField;
    tblJVDetGLAccount: TStringField;
    tblJVDetGLAmount: TCurrencyField;
    dsAPInv: TDataSource;
    tblAPInvDetSeq: TIntegerField;
    qryAPInvCurrencyID: TIntegerField;
    tblAPInv: TTable;
    tblAPInvAPInvoiceID: TIntegerField;
    tblAPInvPosted: TBooleanField;
    tblCurrency: TTable;
    tblCurrencyCurrencyID: TAutoIncField;
    tblCurrencyExchangeRate: TFloatField;
    tblCurrencyAPGLAccount: TStringField;
    tblCurrencyGainLossExchangeGLAccount: TStringField;
    qryAPInvAPGLAccount: TStringField;
    qryAPInvExchangeRate: TFloatField;
    qryAPInvGainLossExchangeGLAccount: TStringField;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure btnOKClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmAPInvoicesPost: TfrmAPInvoicesPost;

implementation

uses BS1Form;

var
  RecordCount: integer;

{$R *.DFM}

procedure TfrmAPInvoicesPost.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TfrmAPInvoicesPost.FormCreate(Sender: TObject);
begin
  qryAPInv.DatabaseName := strDatabaseName;
  tblAPInv.DatabaseName := strDatabaseName;
  tblVBalance.DatabaseName := strDatabaseName;
  tblCurrency.DatabaseName := strDatabaseName;
  tblAPInvDet.DatabaseName := strDatabaseName;
  tblJVCtl.DatabaseName := strDatabaseName;
  tblJV.DatabaseName := strDatabaseName;
  tblJV2.DatabaseName := strDatabaseName;
  tblJVDet.DatabaseName := strDatabaseName;
  qryAPInv.Active := true;

  RecordCount := qryAPInv.RecordCount;
  lblRecordCount.caption := IntToStr(RecordCount) + ' invoice(s) to be posted';
end;

procedure TfrmAPInvoicesPost.btnOKClick(Sender: TObject);
var
  aComponent: TComponent;
  TotalExchange, TotalDebits: currency;
begin
  if license = '' then begin
    qryAPInv.close;
    qryAPInv.SQL[1] := 'WHERE I.Posted = true';
    qryAPInv.open;
    {if (qryAPInv.RecordCount >30) and (frmBS1.tblCompanyCompanyName.value <> 'Sample Data') then begin
      frmBS1.DemoMessage;
      ModalResult := mrCancel;
      exit;
    end;}
    if frmBS1.DemoLimitExceeded(qryAPInv.RecordCount, 'invoice') then begin
      ModalResult := mrCancel;
      exit;
    end;
    repaint;
    qryAPInv.close;
    qryAPInv.SQL[1] := 'WHERE I.Posted = false';
    qryAPInv.open;
  end;

  ModalResult := mrCancel;   //Set to mrCancel to prevent subsequent requery in case job can't be started.
  if RecordCount = 0 then exit;
  aComponent := Application.FindComponent('frmAPInvoice');
  if Assigned (aComponent) then raise(Exception.Create('A conflicting program is running...' + #13 + 'Please close ' + '''' + 'AP Invoice' + '''' + ' form'));

  repeat   //Exclusive open to prevent locking Delphi 2 locking bug.
  try
    tblJVCtl.Active := True;
    Break;   //If no error, exit the loop.
  except
    on EDatabaseError do if MessageDlg('Unable to open JV Control table exclusively.', mtError, [mbRetry, mbCancel], 0) <> mrRetry then raise;
  end;
  until False;

  repeat   //Exclusive open to prevent locking Delphi 2 locking bug.
  try
    tblAPInv.Active := True;
    Break;   //If no error, exit the loop.
  except
    on EDatabaseError do if MessageDlg('Unable to open Invoices table exclusively.', mtError, [mbRetry, mbCancel], 0) <> mrRetry then raise;
  end;
  until False;

  repeat   //Exclusive open to prevent locking Delphi 2 locking bug.
  try
    tblVBalance.Active := True;
    Break;   //If no error, exit the loop.
  except
    on EDatabaseError do if MessageDlg('Unable to open Vendor Balance table exclusively.', mtError, [mbRetry, mbCancel], 0) <> mrRetry then raise;
  end;
  until False;

  ModalResult := mrOK;
  screen.cursor := crHourglass;
  ProgressBar1.max := qryAPInv.RecordCount;
  ProgressBar1.step := 1;
  tblCurrency.Active := true;
  tblJV.Active := true;
  tblJV2.Active := true;
  tblJVDet.Active := true;
  tblAPInvDet.Active := true;
  try
    qryAPInv.First;
    while not qryAPInv.EOF do begin
      with tblAPInv do begin   //Update tblAPInv.
        try
          Edit;
          FieldValues['Posted'] := true;
          Post;
        except raise; end;
      end;

      if tblVBalance.Locate('VendorID', qryAPInvVendorID.value, []) = true then begin   //Update tblVBalance.
        try
          tblVBalance.Edit;
          tblVBalanceBalance.AsCurrency := tblVBalanceBalance.value + qryAPInvInvoiceAmount.value;
          tblVBalance.Post;
        except raise; end;
      end else begin
        try
          tblVBalance.Insert;
          tblVBalanceVendorID.value := qryAPInvVendorID.value;
          tblVBalanceBalance.AsCurrency := qryAPInvInvoiceAmount.value;
          tblVBalance.Post;
        except raise; end;
      end;

      try   //Create JV.
        tblJV.Insert;
          tblJVCtl.Edit;
          tblJVJVID.Value := tblJVCtlNextJVID.Value;
          tblJVCtlNextJVID.Value := tblJVCtlNextJVID.Value + 1;
          if (tblJVCtlNextJVNumber.Value < 1001) or (tblJVCtlNextJVNumber.Value > 10000000) then tblJVCtlNextJVNumber.Value := 1001;   //Start auto-assigned numbers here.
          while true do begin
            if tblJV2.Locate('GLYear;GLPeriod;JVNumber', VarArrayOf([qryAPInvGLYear.value,qryAPInvGLPeriod.value,tblJVCtlNextJVNumber.Value]), []) = true then tblJVCtlNextJVNumber.Value := tblJVCtlNextJVNumber.Value + 1
            else break;   //Exit loop.
          end;
          tblJVJVNumber.Value := tblJVCtlNextJVNumber.Value;
          tblJVCtlNextJVNumber.Value := tblJVCtlNextJVNumber.Value + 1;
          tblJVCtl.Post;
        tblJVGLYear.value := qryAPInvGLYear.value;
        tblJVGLPeriod.value := qryAPInvGLPeriod.value;
        tblJVSource.value := 'AP';
        tblJVTransType.value := 'Inv';
        tblJVTransDate.value := Date;
        tblJVTransDescription.value := 'AP Invoice ' + qryAPInvInvoiceNo.value;
        tblJVVendorID.value := qryAPInvVendorID.value;
        tblJVAPInvoiceID.value := qryAPInvAPInvoiceID.value;
        if qryAPInvInvoiceAmount.AsCurrency > 0 then tblJVJVAmount.AsCurrency := qryAPInvInvoiceAmount.value
        else                                         tblJVJVAmount.AsCurrency := -qryAPInvInvoiceAmount.value;
        TotalDebits := 0;   //Accumulate total debits to adjust JV amount later if neccessary.
        tblJVPosted.value := false;
        tblJVAutoReverse.value := false;
        tblJV.Post;
      except raise; end;

      tblAPInvDet.First;   //Create JV detail.
      TotalExchange := 0;
      While not tblAPInvDet.eof do begin
        try   //Expense accounts.
          tblJVDet.Insert;
          tblJVDetJVID.Value := tblJVJVID.Value;
          tblJVDetSeq.Value := tblAPInvDetSeq.Value;
          if (qryAPInvExchangeRate.value = 0) or (qryAPInvExchangeRate.value = 1) then tblJVDetGLAmount.AsCurrency := tblAPInvDetGLAmount.Value
          //else tblJVDetGLAmount.AsCurrency := tblAPInvDetGLAmount.Value * qryAPInvExchangeRate.value;   //Prevent Delphi 2 bug: rounds .5 down.
          else begin
            tblJVDetGLAmount.AsCurrency := frmBS1.RoundIt(tblAPInvDetGLAmount.Value * qryAPInvExchangeRate.value * 100)/100;
            TotalExchange := TotalExchange + (tblJVDetGLAmount.Value - tblAPInvDetGLAmount.Value);
          end;
          if tblJVDetGLAmount.AsCurrency >0 then TotalDebits := TotalDebits + tblJVDetGLAmount.value;
          tblJVDetGLAccount.Value := tblAPInvDetGLAccount.Value;
          tblJVDet.Post;
        except raise; end;
        tblAPInvDet.next;
      end;
      try   //AP GLAccount.
        tblJVDet.Insert;
        tblJVDetJVID.Value := tblJVJVID.Value;
        tblJVDetSeq.Value := tblAPInvDetSeq.Value + 1;
        tblJVDetGLAmount.AsCurrency := -qryAPInvInvoiceAmount.Value;
        if tblJVDetGLAmount.AsCurrency >0 then TotalDebits := TotalDebits + tblJVDetGLAmount.value;
        tblJVDetGLAccount.Value := qryAPInvAPGLAccount.value;
        tblJVDet.Post;
      except raise; end;
      if TotalExchange <> 0 then begin   //AP Exchange.
        try
          tblJVDet.Insert;
          tblJVDetJVID.Value := tblJVJVID.Value;
          tblJVDetSeq.Value := tblAPInvDetSeq.Value + 2;
          tblJVDetGLAmount.AsCurrency := -TotalExchange;
          if tblJVDetGLAmount.AsCurrency >0 then TotalDebits := TotalDebits + tblJVDetGLAmount.value;
          tblJVDetGLAccount.Value := qryAPInvGainLossExchangeGLAccount.value;
          tblJVDet.Post;
        except raise; end;
        //try
        //  tblJV.Edit;
        //  if TotalExchange >0 then tblJVJVAmount.AsCurrency := tblJVJVAmount.value + TotalExchange
        //  else tblJVJVAmount.AsCurrency := tblJVJVAmount.value - TotalExchange;
        //  tblJV.Post;
        //except raise; end;
      end;
      if tblJVJVAmount.AsCurrency <> TotalDebits then begin   //Update JV amount if necessary.
        try
          tblJV.Edit;
          tblJVJVAmount.AsCurrency := TotalDebits;
          tblJV.Post;
        except raise; end;
      end;

      ProgressBar1.StepIt;
      qryAPInv.next;
    end;
  finally
    DbiSaveChanges(tblJVCtl.handle);
    DbiSaveChanges(tblJV.handle);
    DbiSaveChanges(tblJVDet.handle);
    DbiSaveChanges(tblAPInv.handle);
    DbiSaveChanges(tblVBalance.handle);
    tblJVCtl.Close;
    tblAPInv.Close;
    tblVBalance.Close;
    screen.cursor := crDefault;
  end;

  if (license = '') and (frmBS1.tblCompanyCompanyName.value <> 'Sample Data') then begin
    qryAPInv.close;
    qryAPInv.SQL[1] := 'WHERE I.Posted = true';
    qryAPInv.open;
    frmBS1.DemoLimitWarning(qryAPInv.RecordCount, 'invoice');
  end;
end;

end.