apinvoicesform.pas

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

unit APInvoicesForm;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBCtrls, Grids, DBGrids, DB, DBTables, Menus, ExtCtrls, StdCtrls, Buttons, BDE, SHELLAPI;

type
  TfrmAPInvoices = class(TForm)
    Panel1: TPanel;
    MainMenu1: TMainMenu;
    tblAPInv: TTable;
    dsAPInv: TDataSource;
    DBGrid1: TDBGrid;
    cboFindVendor: TComboBox;
    Label1: TLabel;
    mnuFile: TMenuItem;
    mnuView: TMenuItem;
    mnuHelp: TMenuItem;
    mnuFilter: TMenuItem;
    mnuSortBy: TMenuItem;
    N1: TMenuItem;
    mnuRefresh: TMenuItem;
    mnuAPInvoicesHelp: TMenuItem;
    mnuPrint: TMenuItem;
    N2: TMenuItem;
    mnuExit: TMenuItem;
    Bevel1: TBevel;
    mnuSortAmount: TMenuItem;
    mnuSortDate: TMenuItem;
    mnuSortOwing: TMenuItem;
    mnuSortName: TMenuItem;
    mnuSortDueDate: TMenuItem;
    mnuVendor: TMenuItem;
    mnuCheques: TMenuItem;
    N4: TMenuItem;
    btnNew: TSpeedButton;
    btnEdit: TSpeedButton;
    btnDelete: TSpeedButton;
    editFindVendor: TEdit;
    mnuRecords: TMenuItem;
    mnuNew: TMenuItem;
    mnuEdit: TMenuItem;
    mnuDelete: TMenuItem;
    qryAPInv: TQuery;
    qryAPInvInvoiceNo: TStringField;
    qryAPInvDescription: TStringField;
    qryAPInvInvoiceDate: TDateField;
    qryAPInvDueDate: TDateField;
    qryAPInvInvoiceAmount: TCurrencyField;
    qryAPInvGLYear: TSmallintField;
    qryAPInvGLPeriod: TSmallintField;
    qryAPInvVendorName: TStringField;
    qryAPInvPeriodppyyyy: TStringField;
    tblAPInvDet: TTable;
    tblAPInvChq: TTable;
    mnuSortDateAscending: TMenuItem;
    mnuSortDateDescending: TMenuItem;
    mnuSortDueDateAscending: TMenuItem;
    mnuSortDueDateDescending: TMenuItem;
    qryAPInvOwing: TCurrencyField;
    mnuSortVendorNo: TMenuItem;
    mnuSortInvoiceno: TMenuItem;
    PopupMenu1: TPopupMenu;
    popCheques: TMenuItem;
    popVendor: TMenuItem;
    editFindInvoice: TEdit;
    qryAPInvVendorID: TIntegerField;
    qryAPInvVendorNo: TStringField;
    qryAPInvAPInvoiceID: TIntegerField;
    tblAPInvAPInvoiceID: TIntegerField;
    tblAPInvChqAPInvoiceID: TIntegerField;
    tblAPInvDetAPInvoiceID: TIntegerField;
    N3: TMenuItem;
    mnuPost: TMenuItem;
    qryAPInvPosted: TBooleanField;
    qryAPInvCurrencyID: TIntegerField;
    popNew: TMenuItem;
    popEdit: TMenuItem;
    popDelete: TMenuItem;
    N5: TMenuItem;
    N6: TMenuItem;
    popPhone: TMenuItem;
    qryAPInvPhone: TStringField;
    mnuOutstanding: TMenuItem;
    popEMail: TMenuItem;
    qryAPInvEmail: TStringField;
    btnFilter: TSpeedButton;
    procedure mnuRefreshClick(Sender: TObject);
    procedure tblAPInvBeforeDelete(DataSet: TDataSet);
    procedure FormCreate(Sender: TObject);
    procedure Panel1DblClick(Sender: TObject);
    procedure cboFindVendorDropDown(Sender: TObject);
    procedure cboFindVendorKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure cboFindVendorClick(Sender: TObject);
    procedure cboFindVendorKeyPress(Sender: TObject; var Key: Char);
    procedure DBGrid1KeyPress(Sender: TObject; var Key: Char);
    procedure FormShow(Sender: TObject);
    procedure editFindVendorKeyPress(Sender: TObject; var Key: Char);
    procedure mnuNewClick(Sender: TObject);
    procedure mnuEditClick(Sender: TObject);
    procedure mnuDeleteClick(Sender: TObject);
    procedure mnuExitClick(Sender: TObject);
    procedure qryAPInvCalcFields(DataSet: TDataSet);
    procedure mnuSortDateAscendingClick(Sender: TObject);
    procedure mnuSortDateDescendingClick(Sender: TObject);
    procedure mnuSortDueDateAscendingClick(Sender: TObject);
    procedure mnuSortDueDateDescendingClick(Sender: TObject);
    procedure mnuSortNameClick(Sender: TObject);
    procedure mnuSortAmountClick(Sender: TObject);
    procedure mnuSortOwingClick(Sender: TObject);
    procedure mnuSortVendorNoClick(Sender: TObject);
    procedure mnuSortInvoicenoClick(Sender: TObject);
    procedure mnuVendorClick(Sender: TObject);
    procedure editFindInvoiceKeyPress(Sender: TObject; var Key: Char);
    procedure FormResize(Sender: TObject);
    procedure mnuPostClick(Sender: TObject);
    procedure mnuFilterClick(Sender: TObject);
    procedure qryAPInvFilterRecord(DataSet: TDataSet; var Accept: Boolean);
    procedure mnuPrintClick(Sender: TObject);
    procedure mnuChequesClick(Sender: TObject);
    procedure popPhoneClick(Sender: TObject);
    procedure mnuOutstandingClick(Sender: TObject);
    procedure mnuAPInvoicesHelpClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure popEMailClick(Sender: TObject);
    procedure dsAPInvDataChange(Sender: TObject; Field: TField);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmAPInvoices: TfrmAPInvoices;

implementation

uses BS1Form, VendorForm, APInvoiceForm, APInvoicesPostForm,
  APInvoicesFilterForm, APInvoicesReport, DialerForm, LookUpsData,
  APInvoiceChequesForm, JVsPostForm;

var
  intClientHeight, intClientWidth: Integer;
  cboFindVendor_VendorIDs: TStringList;
  OldWindowState: TWindowState;

{$R *.DFM}

procedure cboFindVendor_Load(const Tbl: TTable; const Cbo: TCombobox);
begin
  Cbo.Items.Clear;   //Populate "find" combobox.
  cboFindVendor_VendorIDs.Clear;
  Tbl.First;
  while not Tbl.EOF do begin
    Cbo.Items.Add(Tbl['VendorName']);
    cboFindVendor_VendorIDs.Add(Tbl['VendorID']);
    Tbl.Next;
  end;
  frmAPInvoices_cboFindVendor_RequeryRequired := false;
end;

procedure TfrmAPInvoices.mnuRefreshClick(Sender: TObject);
var
  Bookmark: TBookmark;
begin
  repaint;
  screen.cursor := crHourglass;
  Bookmark := qryAPInv.GetBookmark;
  with qryAPInv do begin close; open; end;   //Refresh.
  try qryAPInv.GotoBookmark(Bookmark); except; end;
  qryAPInv.FreeBookmark(BookMark);
  screen.cursor := crDefault;
end;

procedure TfrmAPInvoices.tblAPInvBeforeDelete(DataSet: TDataSet);
begin
  tblAPInvChq.Active := true;
  if tblAPInvChq.RecordCount >0 then raise(Exception.Create('Unable to delete invoice ' + '''' + qryAPInvInvoiceNo.AsString + '''' + '...' + #13 + 'Payment(s) exist'));
  if qryAPInvPosted.value = true then raise(Exception.Create('Unable to delete invoice ' + '''' + qryAPInvInvoiceNo.AsString + '''' + '...' + #13 + 'It has already been posted'));
  if MessageDlg('Delete invoice ' + '''' + qryAPInvInvoiceNo.AsString + '''' + '?',mtConfirmation,mbOKCancel,0) <> mrOK then raise(EAbort.create(''));   //Silent Exception: "abort;" replaced by "raise etc" since abort conflicts with BDE (required by DbiSaveChanges).
  repaint;
  
  tblAPInv.edit;   //Put lock on master table.
  with tblAPInvDet do begin   //Delete detail for this invoice.
    Active := true;
    First;
    while not eof do Delete;
  end;
end;

procedure TfrmAPInvoices.FormCreate(Sender: TObject);
var
  x: integer;
begin
  qryAPInv.DatabaseName := strDatabaseName;
  tblAPInv.DatabaseName := strDatabaseName;
  tblAPInvDet.DatabaseName := strDatabaseName;
  tblAPInvChq.DatabaseName := strDatabaseName;
  dmLookUps.tblVendor.DatabaseName := strDatabaseName;
  qryAPInv.Active := true;
  //tblAPInv.Active := true;   ...pospone until required (delete).
  //tblAPInvDet.Active := true;
  //tblAPInvChq.Active := true;
  dmLookUps.tblVendor.Active := true;

  cboFindVendor_VendorIDs := TStringList.create;   //Create a TStringList to store VendorIDs for the "find" combobox.
  cboFindVendor_Load(dmLookUps.tblVendor, cboFindVendor);   //Populate "find" combobox.

  if FontFactor <> 1 then begin   //If using large fonts, resize grid columns.
    //intClientWidth := Trunc(20*FontFactor) + DBGrid1.Columns.Count -1;   //Scrollbar + grid lines.
    intClientWidth := Trunc(20*FontFactor) + 6;   //Scrollbar + grid lines.
    //for x := 0 to DBGrid1.Columns.Count - 1 do begin
    for x := 0 to 6 do begin   //Only 7 columns shown (0 to 6).
      DBGrid1.Columns[x].width := Trunc(DBGrid1.Columns[x].width*FontFactor);
      intClientWidth := intClientWidth + DBGrid1.Columns[x].width;
    end;
    ClientWidth := intClientWidth;
  end;  
  intClientHeight := ClientHeight;   //Store form size.
  intClientWidth := ClientWidth;
end;

procedure TfrmAPInvoices.Panel1DblClick(Sender: TObject);
begin
  ClientHeight := intClientHeight;   //Resize form.
  ClientWidth := intClientWidth;
end;

procedure TfrmAPInvoices.cboFindVendorDropDown(Sender: TObject);
begin
  if frmAPInvoices_cboFindVendor_RequeryRequired = true then cboFindVendor_Load(dmLookUps.tblVendor, cboFindVendor);   //Populate "find" combobox.
end;

procedure TfrmAPInvoices.cboFindVendorKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if frmAPInvoices_cboFindVendor_RequeryRequired = true then cboFindVendor_Load(dmLookUps.tblVendor, cboFindVendor);   //Populate "find" combobox.
end;

procedure TfrmAPInvoices.cboFindVendorClick(Sender: TObject);
begin
  if cboFindVendor.Text = '' then exit;
  editFindVendor.text := '';
  if cboFindVendor.Items.IndexOf(cboFindVendor.Text) < 0 then raise(Exception.Create('Choose an item from the list'));
end;

procedure TfrmAPInvoices.cboFindVendorKeyPress(Sender: TObject; var Key: Char);
begin
  if key = #13 then begin
    Key := #0;   //Cancel Enter key.
    if (cboFindVendor.Text = '') or (cboFindVendor.Text = '<Vendor name>') then begin
      Perform(WM_NEXTDLGCTL, 0, 0);   //Advance to next control.
      exit;
    end;
    cboFindVendorClick(Sender);   //Trigger click event (in case partial text was entered then combobox opened: item would show as selected, but Enter wouldn't process it).
    Perform(WM_NEXTDLGCTL, 0, 0);   //Advance to next control.
  end;
end;

procedure TfrmAPInvoices.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
  if key = #13 then begin
    mnuEditClick(Sender);
    Key := #0;   //Cancel Enter key.
  end;
end;

procedure TfrmAPInvoices.FormShow(Sender: TObject);
begin
  cboFindVendor.ItemIndex := 0;   //Reset selected item to 1st (so down arrow scrolls from start).
  cboFindVendor.text := '<Vendor name>';   //Clear selection.
  editFindVendor.text := '<Vendor no.>';
  editFindInvoice.text := '<Invoice no.>';
  editFindVendor.setfocus;
  editFindVendor.SelectAll;
  qryAPInv.First;   //Reset grid to start at 1st record.
  if mnuOutstanding.checked = true then begin
    if mnuFilter.checked = true then caption := 'AP Invoices (Outstanding, Filtered)'
    else                             caption := 'AP Invoices (Outstanding)';
  end else begin
    if mnuFilter.checked = true then caption := 'AP Invoices (Filtered)'
    else                             caption := 'AP Invoices';
  end;
end;

procedure TfrmAPInvoices.editFindVendorKeyPress(Sender: TObject; var Key: Char);
begin
  if key = #13 then begin
    Key := #0;   //Cancel Enter key.
    if (editFindVendor.Text = '') or (editFindVendor.Text = '<Vendor no.>') then begin
      Perform(WM_NEXTDLGCTL, 0, 0);   //Advance to next control.
      exit;
    end;
    cboFindVendor.text := '';
    editFindInvoice.SetFocus;
  end;
end;

procedure TfrmAPInvoices.mnuNewClick(Sender: TObject);
begin
  screen.cursor := crHourglass;
  application.createform (TfrmAPInvoice,frmAPInvoice);
  frmAPInvoice.tblAPInv.Insert;
  frmAPInvoice.Show;
  screen.cursor := crDefault;
end;

procedure TfrmAPInvoices.mnuEditClick(Sender: TObject);
begin
  if qryAPInvAPInvoiceID.AsVariant = null then mnuNewClick(sender)   //Can happen if no invoices yet.  
  else begin
    screen.cursor := crHourglass;
    application.createform (TfrmAPInvoice,frmAPInvoice);
    frmAPInvoice.tblAPInv.Locate('APInvoiceID', qryAPInvAPInvoiceID.value, []);
    frmAPInvoice.Show;
    screen.cursor := crDefault;
  end;
end;

procedure TfrmAPInvoices.mnuDeleteClick(Sender: TObject);
var
  Bookmark: TBookmark;
begin
  tblAPInv.Active := true;
  tblAPInv.Database.TransIsolation := tiDirtyRead;
  if tblAPInv.Locate('APInvoiceID', qryAPInvAPInvoiceID.value, []) = true then begin
    tblAPInv.delete;   //Delete from table as query is read-only.
    tblAPInv.Database.ApplyUpdates([tblAPInvDet, tblAPInv]);
    DbiSaveChanges(tblAPInvDet.handle);
    DbiSaveChanges(tblAPInv.handle);
    Bookmark := qryAPInv.GetBookmark;
    with qryAPInv do begin close; open; end;   //Refresh.
    try qryAPInv.GotoBookmark(Bookmark); except; end;