jvspostform.pas

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

unit JVsPostForm;

interface

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

type
  TfrmJVsPost = class(TForm)
    btnOK: TButton;
    btnCancel: TButton;
    qryJV: TQuery;
    lblRecordCount: TLabel;
    ProgressBar1: TProgressBar;
    tblJVDet: TTable;
    dsJV: TDataSource;
    tblJVDetJVID: TIntegerField;
    tblJVDetSeq: TIntegerField;
    tblJVDetGLAccount: TStringField;
    tblJVDetGLAmount: TCurrencyField;
    qryJVJVID: TIntegerField;
    qryJVGLYear: TSmallintField;
    qryJVGLPeriod: TSmallintField;
    qryJVJVNumber: TIntegerField;
    qryJVSource: TStringField;
    qryJVTransType: TStringField;
    qryJVTransDate: TDateField;
    qryJVTransDescription: TStringField;
    qryJVVendorID: TIntegerField;
    qryJVAPInvoiceID: TIntegerField;
    qryJVJVAmount: TCurrencyField;
    qryJVPosted: TBooleanField;
    qryJVAutoReverse: TBooleanField;
    tblGLHist: TTable;
    tblGLHistGLHistID: TAutoIncField;
    tblGLHistGLAccount: TStringField;
    tblGLHistGLYear: TSmallintField;
    tblGLHistGLPeriod: TSmallintField;
    tblGLHistSource: TStringField;
    tblGLHistJVNumber: TIntegerField;
    tblGLHistTransDate: TDateField;
    tblGLHistAmount: TCurrencyField;
    tblGLHistTransDescription: TStringField;
    tblGLHistVendorID: TIntegerField;
    tblGLHistAPInvoiceID: TIntegerField;
    tblJV: TTable;
    tblJVJVID: TIntegerField;
    tblJVPosted: TBooleanField;
    tblGLHistTransType: TStringField;
    qryJVCustomerID: TIntegerField;
    qryJVARInvoiceID: TIntegerField;
    tblGLHistCustomerID: TIntegerField;
    tblGLHistARInvoiceID: TIntegerField;
    tblGLActual: TTable;
    tblGLActualGLAccount: TStringField;
    tblGLActualGLYear: TSmallintField;
    tblGLActualActualBudget: TStringField;
    tblGLActualAmount1: TCurrencyField;
    tblGLActualAmount2: TCurrencyField;
    tblGLActualAmount3: TCurrencyField;
    tblGLActualAmount4: TCurrencyField;
    tblGLActualAmount5: TCurrencyField;
    tblGLActualAmount6: TCurrencyField;
    tblGLActualAmount7: TCurrencyField;
    tblGLActualAmount8: TCurrencyField;
    tblGLActualAmount9: TCurrencyField;
    tblGLActualAmount10: TCurrencyField;
    tblGLActualAmount11: TCurrencyField;
    tblGLActualAmount12: TCurrencyField;
    qryJVChequeID: TIntegerField;
    qryJVPaymentID: TIntegerField;
    tblGLHistChequeID: TIntegerField;
    tblGLHistPaymentID: TIntegerField;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure btnOKClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmJVsPost: TfrmJVsPost;

implementation

uses BS1Form;

var
  RecordCount: integer;
  
{$R *.DFM}

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

procedure TfrmJVsPost.FormCreate(Sender: TObject);
begin
  qryJV.DatabaseName := strDatabaseName;
  tblJV.DatabaseName := strDatabaseName;
  tblJVDet.DatabaseName := strDatabaseName;
  tblGLHist.DatabaseName := strDatabaseName;
  tblGLActual.DatabaseName := strDatabaseName;
  qryJV.Active := true;

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

procedure TfrmJVsPost.btnOKClick(Sender: TObject);
var
  aComponent: TComponent;
  ReverseGLYear, ReverseGLPeriod: integer;
begin
  if license = '' then begin
    qryJV.close;
    qryJV.SQL[1] := 'WHERE Posted = true';
    qryJV.open;
    {if (qryJV.RecordCount >30) and (frmBS1.tblCompanyCompanyName.value <> 'Sample Data') then begin
      frmBS1.DemoMessage;
      ModalResult := mrCancel;
      exit;
    end;}
    if frmBS1.DemoLimitExceeded(qryJV.RecordCount, 'JV') then begin
      ModalResult := mrCancel;
      exit;
    end;
    repaint;
    qryJV.close;
    qryJV.SQL[1] := 'WHERE Posted = false';
    qryJV.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('frmJV');
  if Assigned (aComponent) then raise(Exception.Create('A conflicting program is running...' + #13 + 'Please close ' + '''' + 'Journal Voucher' + '''' + ' form'));

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

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

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

      tblGLHist.Active := true;   //Create GLHist.
      tblJVDet.Active := true;
      tblJVDet.First;
      While not tblJVDet.eof do begin
        try
          tblGLHist.Insert;
          tblGLHistGLAccount.Value := tblJVDetGLAccount.Value;
          tblGLHistGLYear.Value := qryJVGLYear.Value;
          tblGLHistGLPeriod.Value := qryJVGLPeriod.Value;
          tblGLHistSource.Value := qryJVSource.Value;
          tblGLHistTransType.Value := qryJVTransType.Value;
          tblGLHistJVNumber.Value := qryJVJVNumber.Value;
          tblGLHistTransDate.Value := qryJVTransDate.Value;
          tblGLHistAmount.Value := tblJVDetGLAmount.Value;
          tblGLHistTransDescription.Value := qryJVTransDescription.Value;
          tblGLHistVendorID.Value := qryJVVendorID.Value;
          tblGLHistAPInvoiceID.Value := qryJVAPInvoiceID.Value;
          tblGLHistChequeID.Value := qryJVChequeID.Value;
          tblGLHistCustomerID.Value := qryJVCustomerID.Value;
          tblGLHistARInvoiceID.Value := qryJVARInvoiceID.Value;
          tblGLHistPaymentID.Value := qryJVPaymentID.Value;
          tblGLHist.Post;
          if qryJVAutoReverse.value = true then begin   //AutoReverse entry.
            tblGLHist.Insert;
            tblGLHistGLAccount.Value := tblJVDetGLAccount.Value;
            if qryJVGLPeriod.value < 12 then begin
              tblGLHistGLYear.Value := qryJVGLYear.Value;
              tblGLHistGLPeriod.Value := qryJVGLPeriod.Value + 1;
            end else begin
              tblGLHistGLYear.Value := qryJVGLYear.Value + 1;
              tblGLHistGLPeriod.Value := 1;
            end;
            tblGLHistSource.Value := qryJVSource.Value;
            tblGLHistTransType.Value := 'JVR';
            tblGLHistJVNumber.Value := qryJVJVNumber.Value;
            tblGLHistTransDate.Value := qryJVTransDate.Value;
            tblGLHistAmount.Value := -tblJVDetGLAmount.Value;
            tblGLHistTransDescription.Value := qryJVTransDescription.Value;
            tblGLHistVendorID.Value := qryJVVendorID.Value;
            tblGLHistAPInvoiceID.Value := qryJVAPInvoiceID.Value;
            tblGLHistChequeID.Value := qryJVChequeID.Value;
            tblGLHistCustomerID.Value := qryJVCustomerID.Value;
            tblGLHistARInvoiceID.Value := qryJVARInvoiceID.Value;
            tblGLHistPaymentID.Value := qryJVPaymentID.Value;
            tblGLHist.Post;
          end;
        except raise; end;
        tblJVDet.next;
      end;

      tblJVDet.Active := true;   //Update GLActual.
      tblJVDet.First;
      While not tblJVDet.eof do begin
        try
          if tblGLActual.Locate('GLAccount;GLYear', VarArrayOf([tblJVDetGLAccount.Value,qryJVGLYear.Value]), []) = true then begin
            tblGLActual.Edit;
            case qryJVGLPeriod.Value of
              1:  tblGLActualAmount1.value := tblGLActualAmount1.value + tblJVDetGLAmount.Value;
              2:  tblGLActualAmount2.value := tblGLActualAmount2.value + tblJVDetGLAmount.Value;
              3:  tblGLActualAmount3.value := tblGLActualAmount3.value + tblJVDetGLAmount.Value;
              4:  tblGLActualAmount4.value := tblGLActualAmount4.value + tblJVDetGLAmount.Value;
              5:  tblGLActualAmount5.value := tblGLActualAmount5.value + tblJVDetGLAmount.Value;
              6:  tblGLActualAmount6.value := tblGLActualAmount6.value + tblJVDetGLAmount.Value;
              7:  tblGLActualAmount7.value := tblGLActualAmount7.value + tblJVDetGLAmount.Value;
              8:  tblGLActualAmount8.value := tblGLActualAmount8.value + tblJVDetGLAmount.Value;
              9:  tblGLActualAmount9.value := tblGLActualAmount9.value + tblJVDetGLAmount.Value;
             10:  tblGLActualAmount10.value := tblGLActualAmount10.value + tblJVDetGLAmount.Value;
             11:  tblGLActualAmount11.value := tblGLActualAmount11.value + tblJVDetGLAmount.Value;
             12:  tblGLActualAmount12.value := tblGLActualAmount12.value + tblJVDetGLAmount.Value;
            end;
            tblGLActual.Post;
          end else begin
            tblGLActual.Insert;
            tblGLActualGLAccount.Value := tblJVDetGLAccount.Value;
            tblGLActualGLYear.Value := qryJVGLYear.Value;
            tblGLActualActualBudget.Value := 'A';
            case qryJVGLPeriod.Value of
              1:  tblGLActualAmount1.value := tblJVDetGLAmount.Value;
              2:  tblGLActualAmount2.value := tblJVDetGLAmount.Value;
              3:  tblGLActualAmount3.value := tblJVDetGLAmount.Value;
              4:  tblGLActualAmount4.value := tblJVDetGLAmount.Value;
              5:  tblGLActualAmount5.value := tblJVDetGLAmount.Value;
              6:  tblGLActualAmount6.value := tblJVDetGLAmount.Value;
              7:  tblGLActualAmount7.value := tblJVDetGLAmount.Value;
              8:  tblGLActualAmount8.value := tblJVDetGLAmount.Value;
              9:  tblGLActualAmount9.value := tblJVDetGLAmount.Value;
             10:  tblGLActualAmount10.value := tblJVDetGLAmount.Value;
             11:  tblGLActualAmount11.value := tblJVDetGLAmount.Value;
             12:  tblGLActualAmount12.value := tblJVDetGLAmount.Value;
            end;
            tblGLActual.Post;
          end;
          if qryJVAutoReverse.value = true then begin   //AutoReverse entry.
            if qryJVGLPeriod.value < 12 then begin
              ReverseGLYear := qryJVGLYear.Value;
              ReverseGLPeriod := qryJVGLPeriod.Value + 1;
            end else begin
              ReverseGLYear := qryJVGLYear.Value + 1;
              ReverseGLPeriod := 1;
            end;
            if tblGLActual.Locate('GLAccount;GLYear', VarArrayOf([tblJVDetGLAccount.Value,ReverseGLYear]), []) = true then begin
              tblGLActual.Edit;
              case ReverseGLPeriod of
                1:  tblGLActualAmount1.value := tblGLActualAmount1.value - tblJVDetGLAmount.Value;
                2:  tblGLActualAmount2.value := tblGLActualAmount2.value - tblJVDetGLAmount.Value;
                3:  tblGLActualAmount3.value := tblGLActualAmount3.value - tblJVDetGLAmount.Value;
                4:  tblGLActualAmount4.value := tblGLActualAmount4.value - tblJVDetGLAmount.Value;
                5:  tblGLActualAmount5.value := tblGLActualAmount5.value - tblJVDetGLAmount.Value;
                6:  tblGLActualAmount6.value := tblGLActualAmount6.value - tblJVDetGLAmount.Value;
                7:  tblGLActualAmount7.value := tblGLActualAmount7.value - tblJVDetGLAmount.Value;
                8:  tblGLActualAmount8.value := tblGLActualAmount8.value - tblJVDetGLAmount.Value;
                9:  tblGLActualAmount9.value := tblGLActualAmount9.value - tblJVDetGLAmount.Value;
               10:  tblGLActualAmount10.value := tblGLActualAmount10.value - tblJVDetGLAmount.Value;
               11:  tblGLActualAmount11.value := tblGLActualAmount11.value - tblJVDetGLAmount.Value;
               12:  tblGLActualAmount12.value := tblGLActualAmount12.value - tblJVDetGLAmount.Value;
              end;
              tblGLActual.Post;
            end else begin
              tblGLActual.Insert;
              tblGLActualGLAccount.Value := tblJVDetGLAccount.Value;
              tblGLActualGLYear.Value := ReverseGLYear;
              tblGLActualActualBudget.Value := 'A';
              case ReverseGLPeriod of
                1:  tblGLActualAmount1.value := - tblJVDetGLAmount.Value;
                2:  tblGLActualAmount2.value := - tblJVDetGLAmount.Value;
                3:  tblGLActualAmount3.value := - tblJVDetGLAmount.Value;
                4:  tblGLActualAmount4.value := - tblJVDetGLAmount.Value;
                5:  tblGLActualAmount5.value := - tblJVDetGLAmount.Value;
                6:  tblGLActualAmount6.value := - tblJVDetGLAmount.Value;
                7:  tblGLActualAmount7.value := - tblJVDetGLAmount.Value;
                8:  tblGLActualAmount8.value := - tblJVDetGLAmount.Value;
                9:  tblGLActualAmount9.value := - tblJVDetGLAmount.Value;
               10:  tblGLActualAmount10.value := - tblJVDetGLAmount.Value;
               11:  tblGLActualAmount11.value := - tblJVDetGLAmount.Value;
               12:  tblGLActualAmount12.value := - tblJVDetGLAmount.Value;
              end;
              tblGLActual.Post;
            end;
          end;
        except raise; end;
        tblJVDet.next;
      end;

      ProgressBar1.StepIt;
      qryJV.next;
    end;
  finally
    DbiSaveChanges(tblGLHist.handle);
    DbiSaveChanges(tblGLActual.handle);
    DbiSaveChanges(tblJV.handle);
    tblJV.close;
    tblGLActual.close;
    screen.cursor := crDefault;
  end;

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

end.