gl_qry_accountbook4.pas

一个MRPII系统源代码版本

unit Gl_Qry_AccountBook4;

Interface

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

Type
  TFrm_Gl_Qry_AccountBook4 = Class(TFrm_Base_Qry)
    Label1: TLabel;
    edt_kmCode: TLinkEdit;
    edt_kmName: TEdit;
    AdoQry_tmp1: TAdoQuery;
    AdoQry_MainCredenceCodeno: TStringField;
    AdoQry_MainjAmount: TFloatField;
    AdoQry_MaindAmount: TFloatField;
    AdoQry_MainjPrice: TFloatField;
    AdoQry_MaindPrice: TFloatField;
    AdoQry_MainjfPrice: TFloatField;
    AdoQry_MaindfPrice: TFloatField;
    AdoQry_Mainrate: TFloatField;
    AdoQry_MainBalancedirection: TIntegerField;
    AdoQry_MainendAmount: TFloatField;
    AdoQry_MainendfPrice: TFloatField;
    AdoQry_MainendPrice: TFloatField;
    AdoQry_MainendBalance: TFloatField;
    AdoQry_MainendfBalance: TFloatField;
    AdoQry_MaindebitBalance: TFloatField;
    AdoQry_MaincreditBalance: TFloatField;
    AdoQry_MainfdebitBalance: TFloatField;
    AdoQry_MainfcreditBalance: TFloatField;
    AdoQry_Maincurrencyflag: TStringField;
    AdoQry_MainCredencedate: TStringField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure edt_kmCodeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure edt_kmCodeButtonClick(Sender: TObject);
    procedure edt_kmCodeExit(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Gl_Qry_AccountBook4: TFrm_Gl_Qry_AccountBook4;
    kmCode1,kmCode2:string;
    stArtYear,stArtMonth:integer;

implementation
uses Sys_Global,Gl_Qry_AccountBook4_C;
{$R *.DFM}
procedure TFrm_Gl_Qry_AccountBook4.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Month1,Month2:string;
    Month:string;
    include:integer;
    iSc:string;
begin
  Application.ProcessMessages;
  inherited;
    AdoQry_tmp1.Connection:=dbconnect;
    Month1:='1800.01';
    Month2:='1800.01';
    kmCode1:='';
    kmCode2:='';
    include:=3;
  with TFrm_Gl_Qry_AccountBook4_C.Create(self) do
  begin
    try
    AdoQry_tmp.Connection:=dbconnect;
    showmodal;
    if modalResult=mrok then
     begin
        Month1:=medts.Text;
        Month2:=medte.Text;
        kmCode1:=edt_kmCode1.Text;
        kmCode2:=edt_kmCode2.Text;
        include:=cmb_include.Itemindex+1;
        iSc:=cmb_include.Text;
     end;
    finally
     Frm_Gl_Qry_AccountBook4_C.Free;
    end;
  end;   
  if (Month1='1800.01') and (Month2='1800.01') then 
  begin
  edt_kmCode.OnExit:=nil;
  edt_kmCode.Enabled:=False;
  abort;
  end;
  Month:=Month2;
  Executesql(AdoQry_tmp,'select dateAdd(mm,1,'+quotedstr(Month2+'.01')+') as dd',0);
  Month2:=AdoQry_tmp.fieldbyname('dd').asstring;
  sqltext:=' select t1.Credenceid,                               '
          +'       t2.Gl_Credencelineno as Credencelineno,      '
          +'         t1.Credencedate,                           '
          +'         t1.Characterno+''_''+convert(varchAr,t1.Credenceno) as CredenceCodeno, ' 
          +'         t1.tallyflag,                              '
          +'         t2.docketName,                             '
          +'         t2.kmCode,                                 '
          +'         t3.kmName,                                 '
          +'         t2.kmCode+'' ''+t3.kmName as kmflag,         '
          +'         case when t2.debitAmount<>0 then t2.Amount else 0 end as jAmount,      '
          +'         case when t2.creditAmount<>0 then t2.Amount else 0 end as dAmount,     '
          +'         case when t2.debitAmount<>0 then (case when t3.fcurrecy<>'''' then t2.Price*t2.fCurrencyRate   '
          +'          else t2.Price                                                         '
          +'         end)                                                                   '
          +'         else 0 end as jPrice,                                                   '
          +'         case when t2.creditAmount<>0 then (case when t3.fcurrecy<>'''' then t2.Price*t2.fCurrencyRate '
           +'         else t2.Price              '
           +'        end)                        '
           +'        else 0 end as dPrice,        '
           +'        case when t2.debitAmount<>0 then ( case when t3.fcurrecy<>'''' then t2.Price else 0  '
           +'        end )                                                                                     '
           +'        else 0 end as jfPrice,                                                                    '
           +'        case when t2.creditAmount<>0 then ( case when t3.fcurrecy<>'''' then t2.Price else 0 '
           +'        end) else 0 end  as dfPrice,        '
           +'        t2.debitAmount,                     '
           +'        t2.creditAmount,                    '
           +'        t2.fCurrencyRate as rate, '
           +'        t3.fcurrecy as currencyCode,                         '
           +'        currency.currencyName, '
           +'        t3.fcurrecy+'' ''+currency.currencyName as currencyflag, '
           +'        t2.fdebitAmount,                     '
           +'        t2.fcreditAmount,                    '
           +'        0 as Balancedirection,                  '
           +'        convert(float,0) as endAmount,       '
           +'        convert(float,0) as endfPrice,       '
           +'        convert(float,0) as endPrice,        '
           +'        convert(float,0) as endBalance,          '
           +'        convert(float,0) as endfBalance    '
           +' into #tmpAmountCredence                           '
           +' from  Gl_Credenceline t2                     '
           +' join Gl_Credence t1 on t2.Credenceid=t1.Credenceid  ' 
           +'  and t1.Credencedate between '+quotedstr(Month1+'.01')+' and '+quotedstr(Month2)
           +iifstring(include=1,'  and t1.tallyflag in(0,1) ',iifstring(include=2,' and t1.tallyflag=1',' '))
           +' join Gl_AccountSubject t3 on t2.kmCode=t3.kmCode  and t3.fcurrecy<>''''   and t3.AmountCalculate=1       '
           +' left join currency on t3.fcurrecy=currency.currencyCode  '
           +' and t3.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2);
//           +' left join Gl_AccountSubjectBalance t4 on t3.kmid=t4.kmid   ';

  Executesql(AdoQry_tmp,sqltext,1);
  Executesql(AdoQry_tmp,'select * from Gl_AccountSubject where AmountCalculate=1 and fcurrecy<>'''' and kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2),0);
  edt_kmCode.Text:=AdoQry_tmp.fieldbyname('kmCode').asstring;
  activecontrol:=dbgrideh;
  selectfromsql:='select * from #ress';
  edt_kmCode.OnExit(self);
  dbgrideh.Columns[1].Width:=64;

 //selectfromsql:='select * from #CredencesummQry';
// Orderbyfields:=' kmCode';
// Executesql(AdoQry_Main,'select * from #CredencesummQry Order by kmCode',0);
  lbl_Condition.Caption:='会计期间从 '+Month1+' 到 '+Month+' /科目代码从 '+kmCode1+' 到 '+kmCode2+' / 包含未过帐凭证： '+iSc;
end;

procedure TFrm_Gl_Qry_AccountBook4.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_AccountBook4:=nil;
end;

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

procedure TFrm_Gl_Qry_AccountBook4.edt_kmCodeKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  if key=120 then
    CommOnHint(Sender,AdoQry_Tmp,'kmName',
             '科目名称','kmCode','科目代码','Gl_AccountSubject',' Amountcalculate=1 and kmCode between'+quotedstr(kmCode1)+' and '+quotedstr(kmCode2));

end;

procedure TFrm_Gl_Qry_AccountBook4.edt_kmCodeButtonClick(Sender: TObject);
begin
  inherited;
    CommOnHint(Sender,AdoQry_Tmp,'kmName',
             '科目名称','kmCode','科目代码','Gl_AccountSubject',' Amountcalculate=1 and kmCode between'+quotedstr(kmCode1)+' and '+quotedstr(kmCode2));

end;

procedure TFrm_Gl_Qry_AccountBook4.edt_kmCodeExit(Sender: TObject);
var sqltext:string;
    lastBalance:double;
    lastfBalance:double;
    lastAmount:double;
    kmproperty:integer;
begin
  inherited;
  Executesql(AdoQry_tmp,'select * from Gl_AccountSubject where  Amountcalculate=1 and kmCode='+quotedstr(Trim(Tedit(edt_kmCode).text)),0);
  edt_kmName.Text:=AdoQry_tmp.fieldbyname('kmName').asstring;
  try
   Executesql(AdoQry_tmp,'drop table #tmpResult',1);
  except
  end;
  try
   Executesql(AdoQry_tmp,'drop table #ress',1);
  except
  end;
  {sqltext:=' select Credenceid, '
           +'        kmCode,     '
           +'        case when debitAmount<>0 then 1  '
           +'        else 2                           '
           +'        end as kmp,                      '
           +'        Balancedirection                 '
           +' into #tmpkmInfo                        '
           +' from #tmpCredence                       '
           +' where kmCode='+quotedstr(Trim(edt_kmCode.text));
  Executesql(AdoQry_tmp,sqltext,1)         ;   }
  //^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  try