gl_enter_firstsubjectbalance.pas

一个MRPII系统源代码版本

unit Gl_Enter_FirstSubjectBalance;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Outer, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, jpeg;

Type
  TFrm_Gl_Enter_FirstSubjectBalance = Class(TFrm_Base_Outer)
    AdoQry_MainkmCode: TStringField;
    AdoQry_MainkmName: TStringField;
    AdoQry_MainFirstdebitBalance: TFloatField;
    AdoQry_MainFirstcreditBalance: TFloatField;
    AdoQry_MainFirstBalance: TFloatField;
    AdoQry_MainFirstAmountBalance: TFloatField;
    AdoQry_Mainkmid: TAutoIncField;
    AdoQry_MainFirstfdebitBalance: TFloatField;
    AdoQry_MainFirstfcreditBalance: TFloatField;
    AdoQry_MainAmountcalculate: TIntegerField;
    AdoQry_Mainkmproperty: TIntegerField;
    AdoQry_MainfFirstBalance: TFloatField;
    AdoQry_MainFcurrecy: TStringField;
    AdoQry_Mainendkm: TIntegerField;
    AdoQry_MainBalancedirection: TIntegerField;
    AdoQry_MainFCurrencyRate: TFloatField;
    AdoQry_MainendBalancedirection: TIntegerField;
    AdoQry_MainendBalance: TFloatField;
    AdoQry_MainendFBalance: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_ModifyExecute(Sender: TObject);
    procedure Act_LookExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    function SetDeleteSql:String;Override;
    function gatherlowlevel:boolean;
    function getuseAccount:boolean;
    procedure sumnext(kmid:integer);
    procedure getkmInfo(kmid:integer);
    { Public declarations }
  end;

var
  Frm_Gl_Enter_FirstSubjectBalance: TFrm_Gl_Enter_FirstSubjectBalance;
        kmCode:string;       //科目代码
        kmlevel:integer;     //科目级次
        KmProperty : Integer;//科目性质
implementation
uses Sys_Global,Gl_Enter_FirstSubjectBalance_D,Gl_Enter_FirstSubjectBalance_D1,
     Gl_Enter_FirstSubjectBalance_TryBalance;
{$R *.DFM}

procedure TFrm_Gl_Enter_FirstSubjectBalance.getkmInfo(kmid:integer);
var AdoQry:TAdoQuery;
begin
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
  try
    Executesql(AdoQry,'select kmCode,kmlevel,KmProperty from Gl_AccountSubject where kmid='+inttostr(kmid),0);
    kmCode:=AdoQry.fieldbyname('kmCode').asstring;
    kmlevel:=AdoQry.fieldbyname('kmlevel').asinteger;
    KmProperty := AdoQry.fieldbyname('KmProperty').AsInteger;
  finally
    AdoQry.Free;
  end;
end;
    
procedure TFrm_Gl_Enter_FirstSubjectBalance.InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);
var sqltext:string;
    stArtYear,stArtMonth:integer;
begin
  Application.ProcessMessages;
  extendCaption:=False;
  inherited;
  Application.ProcessMessages;
  self.Caption:='期初余额';
  Executesql(AdoQry_tmp,'select * from Gl_AccountParam ',0);
  stArtYear:=AdoQry_tmp.fieldbyname('AccountuseYear').asinteger;
  stArtMonth:=AdoQry_tmp.fieldbyname('AccountuseMonth').asinteger;
  sqltext:='select  t1.kmid,  '
          +'         t1.kmCode,        '
          +'         case t1.kmlevel when 1 then t1.kmName    '
          +'                         when 2 then ''  ''+t1.kmName  '
          +'                         when 3 then ''    ''+t1.kmName  '
          +'                         when 4 then ''      ''+t1.kmName  '
          +'                         when 5 then ''       ''+t1.kmName  '
          +'                         when 6 then ''        ''+t1.kmName  '
          +'         end as kmName,       '
//          +'         t1.Fcurrecycalculate, '
          +'         t1.Fcurrecy,         '
          +'         T2.FCurrencyRate    , '
          +'         t1.Amountcalculate,  '
          +'         t1.kmproperty,  '
          +'         t1.endkm,       '
          +'         isnull(t2.Balancedirection,t1.kmproperty) as Balancedirection,'
          +'         isnull(t2.endBalancedirection,t1.kmproperty) as endBalancedirection,'
          +'         isnull(t2.debitBalance,0) as FirstdebitBalance,                '
          +'         isnull(t2.creditBalance,0)as FirstcreditBalance,               '
          +'         isnull(t2.fdebitBalance,0) as FirstfdebitBalance,              '
          +'         isnull(t2.fcreditBalance,0) as FirstfcreditBalance,            '
          +'         isnull(t2.FirstBalance,0) as FirstBalance,                                                '
          +'         isnull(t2.FirstfBalance,0) as fFirstBalance,                                                '
          +'         isnull(t2.endBalance,0) as endBalance,                                                '
          +'         isnull(t2.endFBalance,0) as endFBalance,                                                '
          +'         case when t1.Amountcalculate=1 then isnull('+iifstring(stArtMonth>=1,'t2.FirstAmountBalance,','t2.AmountBalance,')+'0) '
          +'         else 0                                               '
          +'         end as FirstAmountBalance                            '
          +'  into #FirstBalance      '
          +'  from Gl_AccountSubject t1                                   '
          +'  left join  Gl_AccountSubjectBalance t2  on t1.kmid=t2.kmid  '
          +'      and t2.AccountPeriodYear='+IntToStr(StArtYear)
          +'      and t2.AccountPeriodMonth='+IntToStr(StArtMonth);
  Executesql(AdoQry_tmp,sqltext,1)       ;
  selectfromsql:='select * from #FirstBalance';
  Executesql(AdoQry_Main,'select * from #FirstBalance Order by kmCode',0);
  // Frm_Sys_Detail:=TFrm_Gl_Enter_FirstSubjectBalance_D.Create(self);
end;

function TFrm_Gl_Enter_FirstSubjectBalance.SetDeleteSql:String;
begin

end;


procedure TFrm_Gl_Enter_FirstSubjectBalance.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Enter_FirstSubjectBalance:=nil;
end;

procedure TFrm_Gl_Enter_FirstSubjectBalance.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  try
    Executesql(AdoQry_tmp,'drop table #FirstBalance',1);
  except
  end;
end;

procedure TFrm_Gl_Enter_FirstSubjectBalance.Act_ModifyExecute(
  Sender: TObject);
begin
  if AdoQry_Main.fieldbyname('endkm').asinteger=0 then exit;
  if getuseAccount then
    begin
      DispInfo('帐套已启用，不可修改期初余额！',3);
      exit;
    end;
//  if AdoQry_Main.fieldbyname('Fcurrecy').asString='' then
 //   Frm_Sys_Detail:=TFrm_Gl_Enter_FirstSubjectBalance_D.Create(self)
//  else  
  Frm_Sys_Detail:=TFrm_Gl_Enter_FirstSubjectBalance_D1.Create(self);
  inherited;
end;

procedure TFrm_Gl_Enter_FirstSubjectBalance.Act_LookExecute(
  Sender: TObject);
   procedure showResult;
    begin

    end;
var sqltext:string;
    yue1,yue2:double;
    Balance10,Balance11:double;
    Balance20,Balance21:double;
    Balance30,Balance31:double;
    Balance40,Balance41:double;
    Balance50,Balance51:double;
    conclusion:string;
begin
  gatherlowlevel;
  conclusion:='';
  //------------------------------------------------------------------------
  //--资产类借贷方
{  sqltext:='select sum( case when BalanceDirection=1 then isnull(FirstBalance,0) else 0 end ) as ff,'
          +'       sum( case when BalanceDirection=2 then isnull(FirstBalance,0) else 0 end ) as gg'
          +' from Gl_AccountSubjectBalance'
          +' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
          +'   and Gl_AccountSubject.kmlevel=1  and kmType=1)';
  Executesql(AdoQry_tmp,sqltext,0);
  Balance10:=AdoQry_tmp.fieldbyname('ff').asfloat;
  Balance11:=AdoQry_tmp.fieldbyname('gg').asfloat;
}
  SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then FirstBalance     '
                               +'       When BalanceDirection=2 And KmProperty=1 Then -FirstBalance   '
                               +'       When BalanceDirection=2 And KmProperty=2 Then -FirstBalance   '
                               +'       When BalanceDirection=1 And KmProperty=2 Then FirstBalance    '
                               +    ' end )                                                        '
              +'   From Gl_AccountSubjectBalance                                                   '
              +'       Join Gl_AccountSubject ON Gl_AccountSubjectBalance.KMID=Gl_AccountSubject.KMId '
              +'   where Gl_AccountSubjectBalance.kmid in (Select KmID       '
              +'                         From Gl_AccountSubject              '
              +'                         Where endkm=1 And kmType in (1))  ';
  ExecuteSql(AdoQry_Tmp,SqlText,0);
  Balance10 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;
  //-----------------------------------------------------------------------
  //------------------------------------------------------------------------
  //--负债类借贷方
{  sqltext:='select sum( case when BalanceDirection=1 then isnull(FirstBalance,0) else 0 end ) as ff,'
          +'       sum( case when BalanceDirection=2 then isnull(FirstBalance,0) else 0 end ) as gg'
          +' from Gl_AccountSubjectBalance'
          +' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
          +'   and Gl_AccountSubject.kmlevel=1  and kmType=2)';
  Executesql(AdoQry_tmp,sqltext,0);
  Balance20:=AdoQry_tmp.fieldbyname('ff').asfloat;
  Balance21:=AdoQry_tmp.fieldbyname('gg').asfloat;
}
  SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then -FirstBalance     '
                               +'       When BalanceDirection=2 And KmProperty=1 Then FirstBalance   '
                               +'       When BalanceDirection=2 And KmProperty=2 Then FirstBalance   '
                               +'       When BalanceDirection=1 And KmProperty=2 Then -FirstBalance    '
                               +    ' end )                                                        '
              +'   From Gl_AccountSubjectBalance                                                   '
              +'       Join Gl_AccountSubject ON Gl_AccountSubjectBalance.KMID=Gl_AccountSubject.KMId '
              +'   where Gl_AccountSubjectBalance.kmid in (Select KmID       '
              +'                         From Gl_AccountSubject              '
              +'                         Where endkm=1 And kmType in (2))  ';
  ExecuteSql(AdoQry_Tmp,SqlText,0);
  Balance20 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;

  //-----------------------------------------------------------------------
  //------------------------------------------------------------------------
  //--权益类借贷方
{  sqltext:='select sum( case when BalanceDirection=1 then isnull(FirstBalance,0) else 0 end ) as ff,'
          +'       sum( case when BalanceDirection=2 then isnull(FirstBalance,0) else 0 end ) as gg'
          +' from Gl_AccountSubjectBalance'
          +' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
          +'   and Gl_AccountSubject.kmlevel=1  and kmType=3)';
  Executesql(AdoQry_tmp,sqltext,0);
  Balance30:=AdoQry_tmp.fieldbyname('ff').asfloat;
  Balance31:=AdoQry_tmp.fieldbyname('gg').asfloat;
}
  SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then -FirstBalance     '
                               +'       When BalanceDirection=2 And KmProperty=1 Then FirstBalance   '
                               +'       When BalanceDirection=2 And KmProperty=2 Then FirstBalance   '
                               +'       When BalanceDirection=1 And KmProperty=2 Then -FirstBalance    '
                               +    ' end )                                                        '
              +'   From Gl_AccountSubjectBalance                                                   '
              +'       Join Gl_AccountSubject ON Gl_AccountSubjectBalance.KMID=Gl_AccountSubject.KMId '
              +'   where Gl_AccountSubjectBalance.kmid in (Select KmID       '
              +'                         From Gl_AccountSubject              '
              +'                         Where endkm=1 And kmType in (3))  ';
  ExecuteSql(AdoQry_Tmp,SqlText,0);
  Balance30 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;

  //-----------------------------------------------------------------------
  //------------------------------------------------------------------------
  //--成本类借贷方
{  sqltext:='select sum( case when BalanceDirection=1 then isnull(FirstBalance,0) else 0 end ) as ff,'
          +'       sum( case when BalanceDirection=2 then isnull(FirstBalance,0) else 0 end ) as gg'
          +' from Gl_AccountSubjectBalance'
          +' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
          +'   and Gl_AccountSubject.kmlevel=1  and kmType=4)';
  Executesql(AdoQry_tmp,sqltext,0);
  Balance40:=AdoQry_tmp.fieldbyname('ff').asfloat;
  Balance41:=AdoQry_tmp.fieldbyname('gg').asfloat;