accountcode.pas

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

unit AccountCode;

interface

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

type
  TAccountCode = class(TObject)
  private
    FAccountClass: string;
    FAccountType: string;
    FAssisCode: string;
    FBalanceDirection: Boolean;
    FClerkAssis: Boolean;
    FClientAssis: Boolean;
    FCode: string;
    FDepAssis: Boolean;
    FGoodsAssis: Boolean;
    FGUnit: string;
    FName: string;
    FParentCode: string;
    FQtySumAssis: Boolean;
  public
    constructor Create; overload;
    constructor Create(vParentCode,vCode,vName:String;SumDirection:Boolean;
            vAccountType,vAssisCode:String); overload;
  published
    property AccountClass: string read FAccountClass write FAccountClass;
    property AccountType: string read FAccountType write FAccountType;
    property AssisCode: string read FAssisCode write FAssisCode;
    property BalanceDirection: Boolean read FBalanceDirection write
            FBalanceDirection;
    property ClerkAssis: Boolean read FClerkAssis write FClerkAssis;
    property ClientAssis: Boolean read FClientAssis write FClientAssis;
    property Code: string read FCode write FCode;
    property DepAssis: Boolean read FDepAssis write FDepAssis;
    property GoodsAssis: Boolean read FGoodsAssis write FGoodsAssis;
    property GUnit: string read FGUnit write FGUnit;
    property Name: string read FName write FName;
    property ParentCode: string read FParentCode write FParentCode;
    property QtySumAssis: Boolean read FQtySumAssis write FQtySumAssis;
  end;
  
  IAccountCode = interface(IInterface)
    function Add(vACCode: TAccountCode): Boolean; stdcall;
    function Delete(vACCode: TAccountCode): Boolean; stdcall;
    function Get(vCode:String): TAccountCode; stdcall;
    function Update(vACCode: TAccountCode): Boolean; stdcall;
  end;
  
  TAccountCodeDBProcess = class(TInterfacedObject)
  private
    FDB: IAccessDB;
    function MakeAdd(vACCode: TAccountCode): string;
    function MakeDelete(vaCCode: TAccountCode): string;
    function MakeGet(vcode:String): string;
    function MakeUpdate(vaCCode: TAccountCode): string;
  public
    constructor Create(vDBAccess:IAccessDB);
    function Add(vACCode: TAccountCode): Boolean; stdcall;
    function Delete(vACCode: TAccountCode): Boolean; stdcall;
    function Get(vCode:String): TAccountCode; stdcall;
    function Update(vACCode: TAccountCode): Boolean;
  published
    property DB: IAccessDB read FDB write FDB;
  end;
  
  TAccountCodeProxy = class(TInterfacedObject, IAccountCode)
  private
    aDBProcess: TAccountCodeDBProcess;
  public
    constructor Create(aDB:IAccessDB);
    function Add(vACCode: TAccountCode): Boolean; stdcall;
    function Delete(vACCode: TAccountCode): Boolean; stdcall;
    function Get(vCode:String): TAccountCode; stdcall;
    function GetBalanceBefore(vYear,vPeriod,vCode:String;vFlag:Boolean): Double;
    function GetCodeAccountID(vYear,vPeriod,vCode:String): string;
    function GetCodeBefore(vYear,vPeriod,vCode:String): Double;
    function GetCodeDebitOrLender(vYear,vPeriod,vCode:String;vFlag:Boolean):
            Double;
    function GetCodeDebitOrLenderBefore(vYear,vPeriod,vCode:String;
            vFlag:Boolean): Double;
    function GetCodeDebitOrLenderYear(vYear,vCode:String;vFlag:Boolean): Double;
    function GetCodeEnd(vYear,vPeriod,vCode:String): Double;
    function GetCodeStart(vCode:String): Double;
    function IsLastCode(vCode:String): Boolean;
    function Update(vACCode: TAccountCode): Boolean; stdcall;
  end;
  

implementation

{
********************************* TAccountCode *********************************
}
constructor TAccountCode.Create;
begin
  FAccountType:='';
  FParentCode:='';
  FCode:='';
  FName:='';
  FBalanceDirection:=True;
  FAssisCode:='';
end;

constructor TAccountCode.Create(vParentCode,vCode,vName:String;
        SumDirection:Boolean; vAccountType,vAssisCode:String);
begin
  FAccountType:=vAccountType;
  FParentCode:=vParentCode;
  FCode:=vCode;
  FName:=vName;
  FBalanceDirection:=SumDirection;
  FAssisCode:=VAssisCode;
end;

{
**************************** TAccountCodeDBProcess *****************************
}
constructor TAccountCodeDBProcess.Create(vDBAccess:IAccessDB);
begin
  FDB:=vDBAccess;
end;

function TAccountCodeDBProcess.Add(vACCode: TAccountCode): Boolean;
begin
  //保存科目代码
  Result:=(FDB.ExecuteSQL(MakeAdd(vACCode))=0);
end;

function TAccountCodeDBProcess.Delete(vACCode: TAccountCode): Boolean;
begin
  //删除科目代码
  Result:=(FDB.ExecuteSQL(MakeDelete(vACCode))=0);
end;

function TAccountCodeDBProcess.Get(vCode:String): TAccountCode;
var
  aDataSet: TDataSet;
begin
  //从数据库中返回一个科目代码
  aDataSet:=FDB.GetDataSet(MakeGet(vCode));
  if (aDataSet<>nil) and (not aDataSet.eof) then
  begin
     Result:=TAccountCode.Create;
     Result.ParentCode:=aDataSet.FieldByName('父项代码').AsString;
     Result.Code:=aDataSet.FieldByName('代码').AsString;
     Result.Name:=aDataSet.FieldByName('名称').AsString;
     Result.BalanceDirection:=aDataSet.FieldByName('余额方向').AsBoolean;
     Result.AccountType:=aDataSet.FieldByName('分类').AsString;
     Result.AccountClass:=aDataSet.FieldByName('科目类别').AsString;
     Result.ClientAssis:=aDataSet.FieldByName('往来单位核算').AsBoolean;
     Result.DepAssis:=aDataSet.FieldByName('部门核算').AsBoolean;
     Result.ClerkAssis:=aDataSet.FieldByName('职员核算').AsBoolean;
     Result.GoodsAssis:=aDataSet.FieldByName('产品核算').AsBoolean;
     Result.QtySumAssis:=aDataSet.FieldByName('数量金额核算').AsBoolean;
     Result.GUnit:=aDataSet.FieldByName('计量单位').AsString;
  end
  else
     Result:=Nil;
end;

function TAccountCodeDBProcess.MakeAdd(vACCode: TAccountCode): string;
begin
  //生成保存科目代码的SQL语句
  Result:='insert into AccountCode(分类,父项代码,代码,名称,助记码,余额方向,科目类别,往来单位核算,部门核算,职员核算,产品核算,数量金额核算,计量单位)'
           +' values('''+vACCode.AccountType
           +''','''+vACCode.ParentCode
           +''','''+vACCode.Code
           +''','''+vACCode.Name
           +''','''+vACCode.AssisCode
           +''','''+inttostr(ord(vACCode.BalanceDirection))
           +''','''+vACCode.AccountClass
           +''','''+inttostr(ord(vACCode.ClientAssis))
           +''','''+inttostr(ord(vACCode.DepAssis))
           +''','''+inttostr(ord(vACCode.ClerkAssis))
           +''','''+inttostr(ord(vACCode.GoodsAssis))
           +''','''+inttostr(ord(vACCode.QtySumAssis))
           +''','''+vACCode.GUnit+''')';
end;

function TAccountCodeDBProcess.MakeDelete(vaCCode: TAccountCode): string;
begin
  //生成保存科目代码的SQL语句
  Result:='Delete  AccountCode where 代码='''+vACCode.Code+'''';
end;

function TAccountCodeDBProcess.MakeGet(vcode:String): string;
begin
  Result:='select * from AccountCode where 代码='''+vCode+'''';
end;

function TAccountCodeDBProcess.MakeUpdate(vaCCode: TAccountCode): string;
begin
  //生成更新科目代码的SQL语句
  Result:='update AccountCode set 分类='''+vACCode.AccountType+''''
           +',父项代码='''+vACCode.ParentCode+''''
           +',名称='''+vACCode.Name+''''
           +',助记码='''+vACCode.AssisCode+''''
           +',科目类别='''+vACCode.AccountClass+''''
           +',余额方向='''+inttostr(ord(vACCode.BalanceDirection))+''''
           +',往来单位核算='''+inttostr(ord(vACCode.ClientAssis))+''''
           +',部门核算='''+inttostr(ord(vACCode.DepAssis))+''''
           +',职员核算='''+inttostr(ord(vACCode.ClerkAssis))+''''
           +',产品核算='''+inttostr(ord(vACCode.GoodsAssis))+''''
           +',数量金额核算='''+inttostr(ord(vACCode.QtySumAssis))+''''
           +',计量单位='''+vACCode.GUnit+''''
           +' where 代码='''+ vACCode.Code+'''';
end;

function TAccountCodeDBProcess.Update(vACCode: TAccountCode): Boolean;
begin
  //更新科目代码
  Result:=(FDB.ExecuteSQL(MakeUpdate(vACCode))=0);
end;

{
****************************** TAccountCodeProxy *******************************
}
constructor TAccountCodeProxy.Create(aDB:IAccessDB);
begin
  aDBProcess:=TAccountCodeDBProcess.Create(aDB);
end;

function TAccountCodeProxy.Add(vACCode: TAccountCode): Boolean;
begin
  //保存科目代码
  Result:=aDBProcess.Add(vACCode);
end;

function TAccountCodeProxy.Delete(vACCode: TAccountCode): Boolean;
begin
  //删除科目代码
  Result:=aDBProcess.Delete(vACCode);
end;

function TAccountCodeProxy.Get(vCode:String): TAccountCode;
begin
  //获得科目代码
  Result:=aDBProcess.Get(vCode);
end;

function TAccountCodeProxy.GetBalanceBefore(vYear,vPeriod,vCode:String;
        vFlag:Boolean): Double;
var
  Sum: Double;
  vC,vSQL: string;
begin
  //期初
  //Sum:=GetCodeStart(vCode);
  //借方金额或贷方金额
  //vC:='b.代码='''+vCode+'''';
  vC:=' left(a.代码,len('''+vCode+'''))='''+vCode+'''';
  
  if vFlag then vSQL:='借方金额' else vSQL:='贷方金额';
  {
  vSQL:='select isnull(sum(b.'+vSQL+'),0) as 金额 from AccountM as a  '
         +'left join AccountD as b on a.凭证ID=b.凭证ID  where '
         +vC+' and a.年度='+vYear+' and a.会计区间<'+vPeriod;
  }
  vSQL:='select isnull(sum(a.'+vSQL+'),0) as 金额 from AccountTotal as a  '
         +'  where '
         +vC+' and a.年度='+vYear+' and a.会计区间<'+vPeriod;
  
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=FieldByName('金额').AsFloat;
end;

function TAccountCodeProxy.GetCodeAccountID(vYear,vPeriod,vCode:String): string;
begin
  //TODO:返回凭证类别和凭证字
  Result:='凭证字';
end;

function TAccountCodeProxy.GetCodeBefore(vYear,vPeriod,vCode:String): Double;
var
  vC,vSQL: string;
begin
  //借方金额或贷方金额
  vC:=' left(a.代码,len('''+vCode+'''))='''+vCode+'''';
  {
  vSQL:='select isnull(sum(b.'+vSQL+'),0) as 金额 from AccountM as a '
         +'left join AccountD as b on a.凭证ID=b.凭证ID where '
         +vC+' and a.年度='+vYear+' and a.会计区间='+vPeriod;
  }
  vSQL:='select isnull(期初余额,0) as 金额 from AccountTotal as a '
         +' where '+vC+' and a.年度='+vYear+' and a.会计区间='+vPeriod;
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=FieldByName('金额').AsFloat;
end;

function TAccountCodeProxy.GetCodeDebitOrLender(vYear,vPeriod,vCode:String;
        vFlag:Boolean): Double;
var
  vC,vSQL: string;
begin
  //借方金额或贷方金额
  //vC:='b.代码='''+vCode+'''';
  vC:=' left(a.代码,len('''+vCode+'''))='''+vCode+'''';
  
  if vFlag then vSQL:='借方金额' else vSQL:='贷方金额';
  {
  vSQL:='select isnull(sum(b.'+vSQL+'),0) as 金额 from AccountM as a '
         +'left join AccountD as b on a.凭证ID=b.凭证ID where '
         +vC+' and a.年度='+vYear+' and a.会计区间='+vPeriod;
  }
  vSQL:='select isnull(sum(a.'+vSQL+'),0) as 金额 from AccountTotal as a '
         +' where '
         +vC+' and a.年度='+vYear+' and a.会计区间='+vPeriod;
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=FieldByName('金额').AsFloat;
end;

function TAccountCodeProxy.GetCodeDebitOrLenderBefore(vYear,vPeriod,
        vCode:String;vFlag:Boolean): Double;
var
  vC,vSQL: string;
begin
  //借方金额或贷方金额
  //vC:='b.代码='''+vCode+'''';
  vC:=' left(a.代码,len('''+vCode+'''))='''+vCode+'''';
  
  if vFlag then vSQL:='借方金额' else vSQL:='贷方金额';
  {
  vSQL:='select isnull(sum(b.'+vSQL+'),0) as 金额 from AccountM as a  '
         +'left join AccountD as b on a.凭证ID=b.凭证ID  where '
         +vC+' and a.年度='+vYear+' and a.会计区间<'+vPeriod;
  }
  vSQL:='select isnull(sum(a.'+vSQL+'),0) as 金额 from AccountTotal as a  '
         +'  where '
         +vC+' and a.年度='+vYear+' and a.会计区间<'+vPeriod;
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=FieldByName('金额').AsFloat;
end;

function TAccountCodeProxy.GetCodeDebitOrLenderYear(vYear,vCode:String;
        vFlag:Boolean): Double;
var
  vC,vSQL: string;
begin
  //借方金额或贷方金额
  //vC:='b.代码='''+vCode+'''';
  vC:=' left(a.代码,len('''+vCode+'''))='''+vCode+'''';
  
  if vFlag then vSQL:='借方金额' else vSQL:='贷方金额';
  {
  vSQL:='select isnull(sum(b.'+vSQL+'),0) as 金额 from AccountM as a  '
         +'left join AccountD as b on a.凭证ID=b.凭证ID  where '
         +vC+' and a.年度='+vYear+' and a.会计区间<='+vPeriod;
  }
  //使用汇总表
  vSQL:='select isnull(sum(a.'+vSQL+'),0) as 金额 from AccountTotalYear as a  '
         +'  where '
         +vC+' and a.年度='+vYear;
  
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=FieldByName('金额').AsFloat;
end;

function TAccountCodeProxy.GetCodeEnd(vYear,vPeriod,vCode:String): Double;
var
  vC,vSQL: string;
begin
  vC:=' left(a.代码,len('''+vCode+'''))='''+vCode+'''';
  vSQL:='select isnull(余额,0) as 金额 from AccountTotal as a '
         +' where '+vC+' and  a.年度='+vYear+' and a.会计区间='+vPeriod;
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=FieldByName('金额').AsFloat;
end;

function TAccountCodeProxy.GetCodeStart(vCode:String): Double;
var
  vC,vSQL: string;
begin
  //期初数据
  //TODO:期初没有年度设置
  //vC:='b.代码='''+vCode+'''';
  vC:=' left(代码,len('''+vCode+'''))='''+vCode+'''';
  
  vSQL:='select isnull(sum(期初余额),0) as 金额 from StartData  where '
         +vC;
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=FieldByName('金额').AsFloat;
end;

function TAccountCodeProxy.IsLastCode(vCode:String): Boolean;
var
  vSQL: string;
begin
  //判断是否是末级代码
  vSQL:='select Count(*) as 数量 from AccountCode  where 父项代码='''+vCode+'''';
  with aDBProcess.DB.GetDataSet(vSQL) do
    Result:=(FieldByName('数量').AsInteger=0);
end;

function TAccountCodeProxy.Update(vACCode: TAccountCode): Boolean;
begin
  //更新科目代码
  Result:=aDBProcess.Update(vACCode);
end;


end.