gl_qry_accountbook5.pas

一个MRPII系统源代码版本

 begin
   sqltext:='update #tmPMoreAccountQry '
           +' set  Balance='+floattostr(lastBalance)+'+case when '+quotedstr(Firstdirection)+' =''1 借方'' then debitTotal else (-1)*creditTotal end '
           +' where Credencedate='+quotedstr(AdoQry_tmp.fieldbyname('Credencedate').asstring)
           +'  and  idflag=0 and lineflag=0 '
           +'  and CredencenoandCode='+quotedstr(AdoQry_tmp.fieldbyname('CredencenoandCode').asstring);
  //showmessage(sqltext);
   Executesql(AdoQry_Main,sqltext,1);
   sqltext:='update #tmPMoreAccountQry '
           +' set  Balancedirection=case when '+quotedstr(Firstdirection)+' =''1 借方'' then (case when debitTotal>0 then  ''1 借方'' else ''2 贷方'' end) '
           +'                            when  '+quotedstr(Firstdirection)+' =''2 贷方'' then (case when creditTotal>0 then  ''2 贷方'' else ''1 借方'' end) '
           +'                       end  '
           +' where Credencedate='+quotedstr(AdoQry_tmp.fieldbyname('Credencedate').asstring)
           +'  and CredencenoandCode='+quotedstr(AdoQry_tmp.fieldbyname('CredencenoandCode').asstring)
           +'  and  idflag=0 and lineflag=0 ';
//  showmessage(sqltext);
   Executesql(AdoQry_Main,sqltext,1);

   sqltext:= 'select Balance from #tmPMoreAccountQry '
           +' where Credencedate='+quotedstr(AdoQry_tmp.fieldbyname('Credencedate').asstring)
           +'  and  idflag=0 and lineflag=0 '
           +'  and CredencenoandCode='+quotedstr(AdoQry_tmp.fieldbyname('CredencenoandCode').asstring);
   Executesql(AdoQry_tmp1,sqltext,0);
   lastBalance:=AdoQry_tmp1.fieldbyname('Balance').asfloat;
   AdoQry_tmp.Next;
 end; 
 //**********************************************************************************************************************
 Executesql(AdoQry_tmp,'select * from #tmPMoreAccountQry where not (idflag=0 and lineflag=2) Order by Credencedate ',0);
lastBalance:=0;
while not AdoQry_tmp.Eof do
 begin
  if (AdoQry_tmp.fieldbyname('idflag').asinteger=0) and (AdoQry_tmp.fieldbyname('lineflag').asinteger=1) then
   begin
   sqltext:='update #tmPMoreAccountQry '
           +' set  Balance='+floattostr(lastBalance)
           +' where   Credencedate='+quotedstr(AdoQry_tmp.fieldbyname('Credencedate').asstring);
//  showmessage(sqltext);
   Executesql(AdoQry_Main,sqltext,1);
   sqltext:='update #tmPMoreAccountQry '
           +' set  Balancedirection=case when '+quotedstr(Firstdirection)+' =''1 借方'' then (case when debitTotal>0 then  ''1 借方'' else ''2 贷方'' end) '
           +'                            when  '+quotedstr(Firstdirection)+' =''2 贷方'' then (case when creditTotal>0 then  ''2 贷方'' else ''1 借方'' end) '
           +'                       end  '
           +' where Credencedate='+quotedstr(AdoQry_tmp.fieldbyname('Credencedate').asstring)
           +'  and CredencenoandCode='+quotedstr(AdoQry_tmp.fieldbyname('CredencenoandCode').asstring)
           +'  and  idflag=0 and lineflag=1 ';
//  showmessage(sqltext);
   Executesql(AdoQry_Main,sqltext,1);
   end
   else lastBalance:=AdoQry_tmp.fieldbyname('Balance').asfloat;
   AdoQry_tmp.Next;
 end;
//******************************************************************************************************************8           
 selectfromsql:='select * from #tmPMoreAccountQry';
 Orderbyfields:=' Credencedate';
 Executesql(AdoQry_Main,'select * from #tmPMoreAccountQry Order by Credencedate',0);
 AdoQry_Main.Fields[0].Visible:=False;
 AdoQry_Main.Fields[1].visible:=False;
 AdoQry_Main.Fields[2].DisplayLabel:='日期';
 AdoQry_Main.Fields[2].DisplayWidth:=12;
 AdoQry_Main.Fields[3].DisplayLabel:='凭证字号';
 AdoQry_Main.Fields[3].DisplayWidth:=12;
 for i:=0 to AdoQry_Main.FieldCount-1 do
  begin
     if i>1 then 
       AdoQry_Main.Fields[i].Origin:='#tmPMoreAccountQry';
 // showmessage(AdoQry_Main.Fields[i].displayName);
     if AdoQry_Main.Fields[i].displayName='debitTotal' then
       AdoQry_Main.Fields[i].DisplayLabel:='借方|合计'
     else 
     if AdoQry_Main.Fields[i].displayName='creditTotal' then 
       AdoQry_Main.Fields[i].DisplayLabel:='贷方|合计'
     else 
     if copy(AdoQry_Main.fields[i].displayName,1,7)='jkmCode' then
      begin
        tmpkmCode:=copy(AdoQry_Main.fields[i].displayName,pos('e',AdoQry_Main.Fields[i].displayName)+1,length(AdoQry_Main.fields[i].displayName)-length('jkmCode'));
    //    showmessage(tmpkmCode);
        sqltext:='select * from Gl_AccountSubject '
                +' where kmCode='+quotedstr(tmpkmCode);
        Executesql(AdoQry_tmp,sqltext,0);
        AdoQry_Main.Fields[i].DisplayLabel:='借方|'+tmpkmCode+' '+AdoQry_tmp.fieldbyname('kmName').asstring;
        AdoQry_Main.Fields[i].DisplayWidth:=15;        
        if kmproperty=2 then AdoQry_Main.Fields[i].Visible:=False;        
      end
     else 
     if copy(AdoQry_Main.fields[i].displayName,1,7)='dkmCode' then
      begin
        tmpkmCode:=copy(AdoQry_Main.fields[i].displayName,pos('e',AdoQry_Main.Fields[i].displayName)+1,length(AdoQry_Main.fields[i].displayName)-length('jkmCode'));
        sqltext:='select * from Gl_AccountSubject '
                +' where kmCode='+quotedstr(tmpkmCode);
        Executesql(AdoQry_tmp,sqltext,0);
        AdoQry_Main.Fields[i].DisplayLabel:='贷方|'+tmpkmCode+' '+AdoQry_tmp.fieldbyname('kmName').asstring;
        AdoQry_Main.Fields[i].DisplayWidth:=15;        
        if kmproperty=1 then AdoQry_Main.Fields[i].Visible:=False;        
      end
     else 
     if AdoQry_Main.Fields[i].displayName='Balancedirection' then
       AdoQry_Main.Fields[i].DisplayLabel:='余额方向'
     else 
     if AdoQry_Main.Fields[i].displayName='Balance' then
       AdoQry_Main.Fields[i].DisplayLabel:='余额';
  end;
{  for i:=0 to dbgrideh.Columns.Count-1 do
   begin
     if dbgrideh.Columns[i].FieldName='Balancedirection' then
        begin
        dbgrideh.Columns[i].KeyList.clear;
        dbgrideh.Columns[i].KeyList.Add('1');
        dbgrideh.Columns[i].KeyList.Add('2');
        dbgrideh.Columns[i].pickList.clear;
        dbgrideh.Columns[i].PickList.Add('1 借方');
        dbgrideh.Columns[i].PickList.Add('2 贷方');
        end
   end; }
 lbl_Condition.Caption:='会计期间从 '+Month1+' 到 '+Month+' /多栏账： '+cre;
 dbgrideh.FrozenCols:=2;
end;

procedure TFrm_Gl_Qry_AccountBook5.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_AccountBook5:=nil;
end;

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

procedure TFrm_Gl_Qry_AccountBook5.Act_FilterExecute(Sender: TObject);
var i:integer;
    tmpkmCode:string;
    sqltext:string;
begin
 if not AdoQry_Main.Active then
 exit;
  inherited;

 AdoQry_Main.Fields[0].Visible:=False;
 AdoQry_Main.Fields[1].visible:=False;
 AdoQry_Main.Fields[2].DisplayLabel:='日期';
 AdoQry_Main.Fields[2].DisplayWidth:=12;
 AdoQry_Main.Fields[3].DisplayLabel:='凭证字号';
 AdoQry_Main.Fields[3].DisplayWidth:=12;
 for i:=0 to AdoQry_Main.FieldCount-1 do
  begin
     if i>1 then 
       AdoQry_Main.Fields[i].Origin:='#tmPMoreAccountQry';
 // showmessage(AdoQry_Main.Fields[i].displayName);
     if AdoQry_Main.Fields[i].displayName='debitTotal' then
       AdoQry_Main.Fields[i].DisplayLabel:='借方|合计'
     else 
     if AdoQry_Main.Fields[i].displayName='creditTotal' then 
       AdoQry_Main.Fields[i].DisplayLabel:='贷方|合计'
     else 
     if copy(AdoQry_Main.fields[i].displayName,1,7)='jkmCode' then
      begin
        tmpkmCode:=copy(AdoQry_Main.fields[i].displayName,pos('e',AdoQry_Main.Fields[i].displayName)+1,length(AdoQry_Main.fields[i].displayName)-length('jkmCode'));
    //    showmessage(tmpkmCode);
        sqltext:='select * from Gl_AccountSubject '
                +' where kmCode='+quotedstr(tmpkmCode);
        Executesql(AdoQry_tmp,sqltext,0);
        AdoQry_Main.Fields[i].DisplayWidth:=15;        
        AdoQry_Main.Fields[i].DisplayLabel:='借方|'+tmpkmCode+' '+AdoQry_tmp.fieldbyname('kmName').asstring;
        if kmproperty=2 then AdoQry_Main.Fields[i].Visible:=False;        
      end
     else 
     if copy(AdoQry_Main.fields[i].displayName,1,7)='dkmCode' then
      begin
        tmpkmCode:=copy(AdoQry_Main.fields[i].displayName,pos('e',AdoQry_Main.Fields[i].displayName)+1,length(AdoQry_Main.fields[i].displayName)-length('jkmCode'));
        sqltext:='select * from Gl_AccountSubject '
                +' where kmCode='+quotedstr(tmpkmCode);
        Executesql(AdoQry_tmp,sqltext,0);
        AdoQry_Main.Fields[i].DisplayWidth:=15;        
        AdoQry_Main.Fields[i].DisplayLabel:='贷方|'+tmpkmCode+' '+AdoQry_tmp.fieldbyname('kmName').asstring;
        if kmproperty=1 then AdoQry_Main.Fields[i].Visible:=False;        
      end
     else 
     if AdoQry_Main.Fields[i].displayName='Balancedirection' then
       AdoQry_Main.Fields[i].DisplayLabel:='余额方向'
     else 
     if AdoQry_Main.Fields[i].displayName='Balance' then
       AdoQry_Main.Fields[i].DisplayLabel:='余额';
  end;
{  for i:=0 to dbgrideh.Columns.Count-1 do
   begin
     if dbgrideh.Columns[i].fieldName='Balancedirection' then
        begin
        dbgrideh.Columns[i].KeyList.clear;
        dbgrideh.Columns[i].KeyList.Add('1');
        dbgrideh.Columns[i].KeyList.Add('2');
        dbgrideh.Columns[i].pickList.clear;
        dbgrideh.Columns[i].PickList.Add('1 借方');
        dbgrideh.Columns[i].PickList.Add('2 贷方');
        end
   end;}

end;

procedure TFrm_Gl_Qry_AccountBook5.Act_ExcelExecute(Sender: TObject);
begin
  if not dbgrideh.DataSource.DataSet.Active then exit;
  inherited;

end;

end.