account.pas

财务软件:功能尚可,基于UML设计开发的delphi系统,文档齐全

unit Account;

interface

uses
  SysUtils, Windows, Messages, Classes, Graphics, Controls, Forms, Dialogs,Variants,AccessDB,db,adodb;

type
  TAccountItem = class(TObject)
  private
    FBalanceDate: TDateTime;
    FBalanceNO: string;
    FBalanceType: string;
    FBillDate: TDateTime;
    FClerk: string;
    FClient: string;
    FCode: string;
    FDebit: Double;
    FDep: string;
    FDetailID: Integer;
    FDigest: string;
    FGoods: string;
    FLender: Double;
    FPrice: Double;
    FQty: Double;
    FTransID: string;
    FUnitID: Integer;
  public
    constructor Create; overload;
    constructor Create(Precis,Code:String;Debit,Lender:Double); overload;
  published
    property BalanceDate: TDateTime read FBalanceDate write FBalanceDate;
    property BalanceNO: string read FBalanceNO write FBalanceNO;
    property BalanceType: string read FBalanceType write FBalanceType;
    property BillDate: TDateTime read FBillDate write FBillDate;
    property Clerk: string read FClerk write FClerk;
    property Client: string read FClient write FClient;
    property Code: string read FCode write FCode;
    property Debit: Double read FDebit write FDebit;
    property Dep: string read FDep write FDep;
    property DetailID: Integer read FDetailID write FDetailID;
    property Digest: string read FDigest write FDigest;
    property Goods: string read FGoods write FGoods;
    property Lender: Double read FLender write FLender;
    property Price: Double read FPrice write FPrice;
    property Qty: Double read FQty write FQty;
    property TransID: string read FTransID write FTransID;
    property UnitID: Integer read FUnitID write FUnitID;
  end;
  
  TAccount = class(TObject)
  private
    FAccountID: string;
    FAccountLists: TList;
    FAccountStatus: string;
    FAType: string;
    FBiller: string;
    FChecker: string;
    FDate: TDateTime;
    FIDoc: Integer;
    FPeriod: Integer;
    FRemark: string;
    FTransDate: TDateTime;
    FYear: Integer;
    function GetAccountItem(index: Integer): TAccountItem;
  public
    constructor Create; overload;
    constructor Create(vPeriod:Integer;vAccountID,vType,vBiller,vChecker:String;
            vDate:TDatetime;vDoc:Integer); overload;
    function AddAccountItem: TAccountItem; overload;
    function AddAccountItem(Precis,ItemID:String;Debit,Lender:Double): Boolean;
            overload;
    function AddAccountItem(vAccountItem:TAccountItem): Boolean; overload;
    function CheckBlance: Boolean;
    function GetItemCount: Integer;
    function RemoveAll: Boolean;
    function SetAccount(vIperiod:Integer;AccountID,AType,Biller,Checker:String;
            vDate:TDatetime;IDoc:Integer): Boolean;
    property Item[index: Integer]: TAccountItem read GetAccountItem;
  published
    property AccountID: string read FAccountID write FAccountID;
    property AccountStatus: string read FAccountStatus write FAccountStatus;
    property AType: string read FAType write FAType;
    property Biller: string read FBiller write FBiller;
    property Checker: string read FChecker write FChecker;
    property Date: TDateTime read FDate write FDate;
    property IDoc: Integer read FIDoc write FIDoc;
    property Period: Integer read FPeriod write FPeriod;
    property Remark: string read FRemark write FRemark;
    property TransDate: TDateTime read FTransDate write FTransDate;
    property Year: Integer read FYear write FYear;
  end;
  
  IAccount = interface(IInterface)
    function Add(vAccount:TAccount): Boolean; stdcall;
    function Delete(aAccount:TAccount): Boolean; stdcall;
    function Get(vYear,vPeriod:Integer;vType,vNO:String): TAccount; stdcall;
  end;
  
  TAccountDBProcess = class(TInterfacedObject)
  private
    FDB: IAccessDB;
    function GetAccountID(vYear,vPeriod:Integer;vType,vNO:String): Integer;
    function MakeGet(vYear,vPeriod:Integer;vType,vNO:String): string;
  public
    constructor Create(adB:IAccessDB);
    function Add(aAccount:TAccount): Boolean;
    function Delete(aAccount:TAccount): Boolean;
    function Get(vYear,vPeriod:Integer;vType,vNO:String): TAccount;
  published
    property DB: IAccessDB read FDB write FDB;
  end;
  
  TAccountProxy = class(TInterfacedObject, IAccount)
  private
    aDBProcess: TAccountDBProcess;
  public
    constructor Create(adB:IAccessDB);
    function Add(vAccount:TAccount): Boolean; stdcall;
    function Delete(aAccount:TAccount): Boolean; stdcall;
    function Get(vYear,vPeriod:Integer;vType,vNO:String): TAccount; stdcall;
    function GetNextAccountNO(vYear,vPeriod:Integer;vType:String): Integer;
    function GetOtherAccountName(vAccountID:Integer;vCode:String): string;
    function SetAccountStatus(vYear,vPeriod:Integer;vType,vNO,vStatus:String):
            Boolean;
  end;
  

implementation

{
********************************* TAccountItem *********************************
}
constructor TAccountItem.Create;
begin
  inherited create;
end;

constructor TAccountItem.Create(Precis,Code:String;Debit,Lender:Double);
begin
  inherited create;
  FCode:=Code;
  FDigest:=Precis;
  FBillDate:=0;
  FDebit:=Debit;
  FLender:=Lender;
end;

{
*********************************** TAccount ***********************************
}
constructor TAccount.Create;
begin
  inherited create;
  FPeriod:=0;
  FAccountID:='';
  FAType:='';
  FBiller:='';
  FChecker:='';
  FDate:=Date;
  FIDoc:=0;
  FAccountLists:=TList.create;
end;

constructor TAccount.Create(vPeriod:Integer;vAccountID,vType,vBiller,
        vChecker:String; vDate:TDatetime;vDoc:Integer);
begin
  inherited create;
  FPeriod:=vPeriod;
  FAccountID:=vAccountID;
  FAType:=vType;
  FBiller:=vBiller;
  FChecker:=vChecker;
  FDate:=vDate;
  FIDoc:=vDOC;
  FAccountLists:=TList.create;
end;

function TAccount.AddAccountItem: TAccountItem;
var
  vAccountItem: TAccountItem;
begin
  vAccountItem:=TAccountItem.Create;
  FAccountLists.Add(vAccountItem);
  Result:=vAccountItem;
end;

function TAccount.AddAccountItem(Precis,ItemID:String;Debit,Lender:Double):
        Boolean;
var
  Acc: TAccountItem;
begin
  AddAccountItem(TAccountItem.create(Precis,ItemID,Debit,Lender));
end;

function TAccount.AddAccountItem(vAccountItem:TAccountItem): Boolean;
begin
  FAccountLists.Add(vAccountItem);
end;

function TAccount.CheckBlance: Boolean;
var
  I: Integer;
  SumDebit, SumLender: Double;
begin
  {
  SumIn:=0;
  SumOut:=0;
  }
  For i:=0 to FAccountLists.count-1 do
  begin
      SumDebit:=SumDebit+Item[i].Debit;
      SumLender:=SumLender+Item[i].Lender;
  end;
  //浮点数比较大小
  //Result:=not ((SumIn-SumOut)>0);
  Result:=(SumDebit=SumLender);
end;

function TAccount.GetAccountItem(index: Integer): TAccountItem;
begin
  Result := nil;
  if (index < GetItemCount) then
    Result := TAccountItem(FAccountLists[index]);
end;

function TAccount.GetItemCount: Integer;
begin
  Result:=FAccountLists.Count;
end;

function TAccount.RemoveAll: Boolean;
begin
  FAccountLists.Clear;
end;

function TAccount.SetAccount(vIperiod:Integer;AccountID,AType,Biller,
        Checker:String; vDate:TDatetime;IDoc:Integer): Boolean;
begin
  FPeriod:=vIperiod;
  FAccountID:=AccountID;
  FAType:=AType;
  FBiller:=Biller;
  FChecker:=Checker;
  FDate:=vDate;
  FIDoc:=IDOC;
end;

{
****************************** TAccountDBProcess *******************************
}
constructor TAccountDBProcess.Create(adB:IAccessDB);
begin
  FDB:=aDB;
end;

function TAccountDBProcess.Add(aAccount:TAccount): Boolean;
var
  vSQL: string;
  I,ID: Integer;
begin
  //保存凭证
  //开始事务
  try
    FDB.BeginTrans;
    //删除原凭证
  
    Delete(aAccount);
    //****************************************************************
    //保存凭证头
    //****************************************************************
    vSQL:='insert into AccountM(凭证字,凭证号,年度,会计区间,日期,制单,凭证状态) values('''
           +aAccount.AType
           +''','''+aAccount.AccountID
           +''','''+IntToStr(aAccount.Year)
           +''','''+IntToStr(aAccount.Period)
           +''','''+FormatDateTime('yyyy-mm-dd',aAccount.Date)
           +''','''+aAccount.Biller+''',''未审核'')';
    Result:=(FDB.ExecuteSQL(vSQL)=0);
  
    //****************************************************************
    //保存凭证明细
    //****************************************************************
    vSQL:='';
    //取凭证ID
    ID:=GetAccountID(aAccount.Year,aAccount.Period,aAccount.AType,aAccount.AccountID);
    for i:=0 to aACcount.GetItemCount-1 do
    begin
        vSQL:='insert into AccountD(凭证ID,摘要,代码,借方金额,贷方金额,往来单位,部门,职员,产品,数量,单价,结算方式,结算号,结算日期) values('
              + IntToStr(ID)
              +','''+aACcount.Item[i].Digest
              +''','''+aACcount.Item[i].Code
              +''','+floattostr(aACcount.Item[i].Debit)
              +','+floattostr(aACcount.Item[i].Lender)
              +','''+aACcount.Item[i].Client
              +''','''+aACcount.Item[i].Dep
              +''','''+aACcount.Item[i].Clerk
              +''','''+aACcount.Item[i].Goods
              +''','+floattostr(aACcount.Item[i].Qty)
              +','+floattostr(aACcount.Item[i].Price)
              +','''+aACcount.Item[i].BalanceType
              +''','''+aACcount.Item[i].BalanceNO
              +''','''+FormatDateTime('yyyy-mm-dd',aACcount.Item[i].BalanceDate)+''')';
        Result:=(FDB.ExecuteSQL(vSQL)=0);
    end;
    FDB.CommitTrans;
    Result:=True;
  except
     FDB.RollbackTrans;
     Result:=False;
  end;
end;

function TAccountDBProcess.Delete(aAccount:TAccount): Boolean;
var
  ID: Integer;
  vSQL: string;
begin
  //删除凭证
  ID:=GetAccountID(aAccount.Year,aAccount.Period,aAccount.AType,aAccount.AccountID);
  vSQL:='delete AccountD where  凭证ID='+inttostr(ID);
  Result:=(FDB.ExecuteSQL(vSQL)=0);
  vSQL:='delete AccountM where  凭证ID='+inttostr(ID);
  Result:=(FDB.ExecuteSQL(vSQL)=0);
end;

function TAccountDBProcess.Get(vYear,vPeriod:Integer;vType,vNO:String):
        TAccount;
var
  aDataSet: TDataSet;
  aAccount: TAccount;
begin
  //从数据库中返回一个凭证
  aDataSet:=FDB.GetDataSet(MakeGet(vYear,vPeriod,vType,vNO));
  
  aAccount:=TAccount.Create;
  
  if (aDataSet<>nil) and (not aDataSet.eof) then
  begin
     //TODO:详细写返回对象
     aAccount.AType:=aDataSet.FieldByName('凭证字').AsString;
     aAccount.AccountID:=aDataSet.FieldByName('凭证号').AsString;
     aAccount.Year:=aDataSet.FieldByName('年度').AsInteger;
     aAccount.Period:=aDataSet.FieldByName('会计区间').AsInteger;
     aAccount.Date:=aDataSet.FieldByName('日期').AsDateTime;
     aAccount.Biller:=aDataSet.FieldByName('制单').AsString;
     aAccount.AccountStatus:=aDataSet.FieldByName('凭证状态').AsString;
  end
  else
     Result:=Nil;
  
  aAccount.RemoveAll;
  
  with aDataSet do
  while not eof do
  begin
       with aAccount.AddAccountItem do
       begin
           Digest:=FieldValues['摘要'];
           Code:=FieldValues['代码'];
           Debit:=FieldValues['借方金额'];
           Lender:=FieldValues['贷方金额'];
           Client:=FieldByName('往来单位').AsString;
           Dep:=FieldByName('部门').AsString;
           Clerk:=FieldByName('职员').AsString;
           Goods:=FieldByName('产品').AsString;
           Qty:=FieldByName('数量').AsFloat;
           Price:=FieldByName('单价').AsFloat;
           BalanceType:=FieldByName('结算方式').AsString;
           BalanceNO:=FieldByName('结算号').AsString;
           BalanceDate:=FieldByName('结算日期').AsDateTime;
       end;
       Next;
  end;
  
  Result:=aAccount;
end;

function TAccountDBProcess.GetAccountID(vYear,vPeriod:Integer;vType,
        vNO:String): Integer;
begin
  with FDB.GetDataSet('select isnull(凭证ID,0) as 凭证ID from AccountM where 年度='
       +inttostr(vYear)+' and 会计区间='+inttostr(vPeriod)
       +' and 凭证字='''+vType
       +''' and 凭证号='''+vNO+'''') do
       Result:=FieldByName('凭证ID').AsInteger;
end;

function TAccountDBProcess.MakeGet(vYear,vPeriod:Integer;vType,vNO:String):
        string;
var
  vSQL: string;
begin
  vSQL:='select * from  V_Account  where 年度='
         +IntToStr(vYear)+' and 会计区间='+IntToStr(vPeriod)
         +' and 凭证字='''+vType+''' and 凭证号='''+vNO+'''';
  
  Result:=vSQL;
end;

{
******************************** TAccountProxy *********************************
}
constructor TAccountProxy.Create(adB:IAccessDB);
begin
  aDBProcess:=TAccountDBProcess.Create(aDB);
end;

function TAccountProxy.Add(vAccount:TAccount): Boolean;
begin
  //保存凭证到数据库
  Result:=aDBProcess.Add(vAccount);
end;

function TAccountProxy.Delete(aAccount:TAccount): Boolean;
begin
  //删除凭证
  Result:=aDBProcess.Delete(aAccount);
end;

function TAccountProxy.Get(vYear,vPeriod:Integer;vType,vNO:String): TAccount;
begin
  //从数据库中取得一张凭证
  Result:=aDBProcess.Get(vYear,vPeriod,vType,vNO);
end;

function TAccountProxy.GetNextAccountNO(vYear,vPeriod:Integer;vType:String):
        Integer;
begin
  with aDBProcess.DB.GetDataSet('select isnull(Max(凭证号),0)+1 as 凭证号 from AccountM where 年度='
       +IntToStr(vYear)+' and 会计区间='+inttostr(vPeriod)+' and 凭证字='''+vType+'''') do
       Result:=FieldByName('凭证号').AsInteger;
end;

function TAccountProxy.GetOtherAccountName(vAccountID:Integer;vCode:String):
        string;
var
  vSQL: string;
  vDir: Boolean;
  aDataSet: TDataSet;
begin
  vSQL:='select * from AccountD where 凭证ID='+inttostr(vAccountID)+' and 代码='''+vCode+'''';
  with aDBProcess.DB.GetDataSet(vSQL) do
       if not eof then
          if FieldByName('借方金额').asFloat>0 then vDir:=True else vDir:=False;
  
  if vDir then
     vSQL:='select * from AccountD where 凭证ID='+inttostr(vAccountID)+' and 贷方金额>0'
  else
      vSQL:='select * from AccountD where 凭证ID='+inttostr(vAccountID)+' and 借方金额>0';
  
  
  aDataSet:=aDBProcess.DB.GetNewDataSet(vSQL);
  
  Result:='';
  
  while not aDataSet.eof do
  begin
      with aDBProcess.DB.GetDataSet('select 名称 from AccountCode where 代码='''+aDataSet.FieldByName('代码').asstring+'''') do
           Result:=Result+FieldByName('名称').asstring;
      aDataSet.next;
  end;
  
  aDataSet.Free;
end;

function TAccountProxy.SetAccountStatus(vYear,vPeriod:Integer;vType,vNO,
        vStatus:String): Boolean;
var
  vSQL: string;
begin
  vSQL:='update AccountM set 凭证状态='''+vStatus+''' where 年度='
         +IntToStr(vYear)+' and 会计区间='+IntToStr(vPeriod)
         +' and 凭证字='''+vType+''' and 凭证号='''+vNO+'''';
  Result:=(aDBProcess.DB.ExecuteSQL(vSQL)=0);
end;



end.