ar_qry_gatheringar4.pas

一个MRPII系统源代码版本

unit Ar_Qry_GatheringAr4;

Interface

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

Type
  TFrm_Ar_Qry_GatheringAr4 = Class(TFrm_Base_Qry)
    AdoQry_MainInvoiceno: TStringField;
    AdoQry_MainTotaltaxAmount: TFloatField;
    AdoQry_MainBalance: TFloatField;
    AdoQry_MaincurrencyCode: TStringField;
    AdoQry_MaincurrencyName: TStringField;
    AdoQry_Maincurrencyflag: TStringField;
    AdoQry_Maincreatedate: TStringField;
    AdoQry_Mainflag: TIntegerField;
    AdoQry_MainTotalAmount: TFloatField;
    AdoQry_MainSaleEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainSaleEmployeeflag: TStringField;
    AdoQry_MainTotalTaxAmountC: TFloatField;
    AdoQry_MainTotalAmountC: TFloatField;
    AdoQry_MainBalanceC: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Ar_Qry_GatheringAr4: TFrm_Ar_Qry_GatheringAr4;

implementation
uses Ar_Qry_GatheringAr2_C,Sys_Global;
{$R *.DFM}
procedure TFrm_Ar_Qry_GatheringAr4.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    date1,date2:string;
    customerCode1,customerCode2:string;
    SaleTypeCode1,SaleTypeCode2:string;
    DeptCode1,DeptCode2:string;
    EmployeeCode1,EmployeeCode2:string;
    Invoiceno1,Invoiceno2:string;
    currencyCode:string;
begin
Application.ProcessMessages;
 inherited;
 date1:='';
 date2:='';
 customerCode1:='';
 customerCode2:='';
 DeptCode1:='';
 DeptCode2:='';
 SaleTypeCode1:='';
 SaleTypeCode2:='';
 EmployeeCode1:='';
 EmployeeCode2:='';
 Invoiceno1:='';
 Invoiceno2:='';
 currencyCode:='';
 try
  with TFrm_Ar_Qry_GatheringAr2_C.Create(self) do
   begin
     AdoQry_tmp.Connection:=dbconnect;
     medt_Date1.Text:=formatdatetime('yyyy.mm.dd',strtodatetime(GetServerDateTime(dbconnect))-365);
     medt_Date2.Text:=formatdatetime('yyyy.mm.dd',strtodatetime(GetServerDateTime(dbconnect)));
     showmodal;     
     if modalResult=mrok then 
       begin
         date1:=medt_Date1.Text;
         date2:=medt_Date2.Text;
         customerCode1:=edt_CustomerCode1.Text;
         customerCode2:=edt_CustomerCode2.Text;
         SaleTypeCode1:=edt_SaleTypeCode1.text;
         SaleTypeCode2:=edt_SaleTypeCode2.text;
         DeptCode1:=edt_DeptCode1.Text;
         DeptCode2:=edt_DeptCode2.Text;
         EmployeeCode1:=edt_EmployeeCode1.Text;
         EmployeeCode2:=edt_EmployeeCode2.Text;
         Invoiceno1:=edt_Invoiceno1.Text;
         Invoiceno2:=edt_Invoiceno2.Text;
         currencyCode:=edt_CurrencyCode1.Text;
       end;     
   end;
 finally
  Frm_Ar_Qry_GatheringAr2_C.Free;
 end;

 sqltext:='select flag=0,sa_SaleInvoice.SaleEmployeeCode,'
           +      ' Employee.EmployeeName,'
           +      ' sa_SaleInvoice.SaleEmployeeCode+'' ''+Employee.EmployeeName as SaleEmployeeflag,'
           +      ' dateName(yy,sa_SaleInvoice.Billdate)+''.''+dateName(mm,sa_SaleInvoice.Billdate)+''.''+dateName(dd,sa_SaleInvoice.Billdate) as createdate,                                 '
           +      ' sa_SaleInvoice.Invoiceno,                                   '
           +      ' sa_SaleInvoice.TotaltaxAmount,                               '
           +      ' tmp.TotalAmount,'
           +      ' sa_SaleInvoice.TotaltaxAmount-sa_SaleInvoice.CancelAmount as Balance,'
           +      ' Sa_SaleInvoice.TotalTaxAmountC, '
           +'       Tmp.TotalAmountC, '
           +'       Sa_SaleInvoice.TotalTaxAmountC-Sa_SaleInvoice.CancelAmount as BalanceC , '
           +      ' sa_SaleInvoice.currencyCode,                                '
           +      ' currency.currencyName,                                      '
           +      ' sa_SaleInvoice.currencyCode+'' ''+currency.currencyName as currencyflag'
           +' into #ArQry_gather4 '
          +' from  sa_SaleInvoice                                                         '
          +' left join Employee on sa_SaleInvoice.SaleEmployeeCode=Employee.EmployeeCode                   '
          +' left join currency on sa_SaleInvoice.currencyCode=currency.currencyCode        '
          +' left join (select Ar_Gatheringline.Billno,  '
          +'                   sum(isnull(Ar_Gatheringline.TotalAmount,0)) as TotalAmount , '
          +'                   Sum(IsNull(Ar_Gatheringline.TotalAmount,0)*Ar_Gathering.ExchangeRate) as TotalAmountC   '    
          +'              from Ar_Gatheringline  '
          +'              join Ar_Gathering on Ar_Gatheringline.GatheringNo=Ar_Gathering.GatheringNo '
          +' group by Ar_Gatheringline.Billno) tmp '
          +'       on sa_SaleInvoice.Invoiceno=tmp.Billno  '
          +' where sa_SaleInvoice.Billdate between '+quotedstr(date1)+' and '+quotedstr(date2)
          +'   and isnull(sa_SaleInvoice.SaleDeptCode,''0'') between '+quotedstr(DeptCode1)+' and '+quotedstr(DeptCode2)
          +'   and sa_SaleInvoice.Invoiceno  between '+quotedstr(Invoiceno1)+' and '+quotedstr(Invoiceno2)
          +'   and sa_SaleInvoice.customerCode between '+quotedstr(customerCode1)+' and '+quotedstr(customerCode2)
          +'   and isnull(sa_SaleInvoice.SaleEmployeeCode,''0'') between '+quotedstr(EmployeeCode1)+' and '+quotedstr(EmployeeCode2)
          +'   and sa_SaleInvoice.currencyCode='+quotedstr(currencyCode)
          +'   and sa_SaleInvoice.SaleTypeCode between '+quotedstr(SaleTypeCode1)+' and '+quotedstr(SaleTypeCode2);
Executesql(AdoQry_Main,sqltext,1)          ;
Executesql(AdoQry_tmp,'select * from #ArQry_gather4',0);
if AdoQry_tmp.RecordCount<> 0 then 
begin
      sqltext:='insert into #ArQry_gather4'
        +' select 1,''zz'',''  '',''合计'','
        +'        ''  '','
        +'        ''  '','
        +'        isnull(sum(isnull(TotaltaxAmount,0)),0),'
        +'        isnull(sum(isnull(TotalAmount,0)),0),'
        +'        isnull(sum(isnull(Balance,0)),0),   '
        +'        isnull(sum(isnull(TotaltaxAmountc,0)),0),'
        +'        isnull(sum(isnull(TotalAmountc,0)),0),'
        +'        isnull(sum(isnull(Balancec,0)),0),   '
        +'        ''  '','
        +'        ''  '','
        +'        ''  '''
        +' from #ArQry_gather4  ';
      Executesql(AdoQry_Main,sqltext,1);

sqltext:='insert into #ArQry_gather4 '
        +' select 1,SaleEmployeeCode,EmployeeName,SaleEmployeeCode+'' ''+EmployeeName+'' 小计'' as SaleEmployeeflag,'
        +'        ''  '','
        +'        ''  '','
        +'        sum(TotaltaxAmount),'
        +'        sum(TotalAmount),'
        +'        sum(Balance),   '
        +'        isnull(sum(isnull(TotaltaxAmountc,0)),0),'
        +'        isnull(sum(isnull(TotalAmountc,0)),0),'
        +'        isnull(sum(isnull(Balancec,0)),0),   '
        +'        ''  '','
        +'        ''  '','
        +'        ''  '''
        +' from #ArQry_gather4 '
        +' where SaleEmployeeCode<>''zz'' '
        +' group by SaleEmployeeCode,EmployeeName ';
Executesql(AdoQry_Main,sqltext,1);
end;
        
selectfromsql:='select * from #ArQry_gather4';
Executesql(AdoQry_Main,'select * from #ArQry_gather4 Order by SaleEmployeeCode,SaleEmployeeflag,createdate,Invoiceno',0);
lbl_Condition.Caption:='发票日期从     '+date1+' 到 '+date2+' / 客户代码从 '+customerCode1+' 到 '+customerCode2+' / 销售类型从 '+SaleTypeCode1+' 到 '+SaleTypeCode2
                      +#13+'销售部门代码从 '+DeptCode1+' 到 '+DeptCode2+' / 营销业务员代码从 '+EmployeeCode1+' 到 '+EmployeeCode2
                      +#13+'发票单号从     '+Invoiceno1+' 到 '+Invoiceno2+' /货币代码 '+currencyCode;
end;

procedure TFrm_Ar_Qry_GatheringAr4.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ar_Qry_GatheringAr4:=nil;
end;

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

procedure TFrm_Ar_Qry_GatheringAr4.DBGridEhGetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
if AdoQry_Main.fieldbyname('flag').asinteger=1 then 
 begin
     Background:=$00f9d1c6;
   afont.Color:=clblack;
 end;
end;

end.