ar_qry_ar5.pas

一个MRPII系统源代码版本

unit Ar_Qry_Ar5;

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_Ar5 = 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_MainSaleTypeCode: TStringField;
    AdoQry_MainSaleTypeName: TStringField;
    AdoQry_MainSaleTypeflag: TStringField;
    AdoQry_MainTotalTaxAmountC: 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_Ar5: TFrm_Ar_Qry_Ar5;

implementation
uses Ar_Qry_Ar2_C,Sys_Global;
{$R *.DFM}
procedure TFrm_Ar_Qry_Ar5.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    date1,date2:string;
    customerCode1,customerCode2:string;
    DeptCode1,DeptCode2:string;
    EmployeeCode1,EmployeeCode2:string;
    SaleTypeCode1,SaleTypeCode2:string;
    Invoiceno1,Invoiceno2:string;
    currencyCode:string;
begin
  Application.ProcessMessages;
  inherited;
  date1:='';
  date2:='';
  customerCode1:='';
  customerCode2:='';
  DeptCode1:='';
  DeptCode2:='';
  EmployeeCode1:='';
  EmployeeCode2:='';
  SaleTypeCode1:='';
  SaleTypeCode2:='';
  Invoiceno1:='';
  Invoiceno2:='';
  currencyCode:='';
  try
    with TFrm_Ar_Qry_Ar2_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;
            DeptCode1:=edt_DeptCode1.Text;
            DeptCode2:=edt_DeptCode2.Text;
            EmployeeCode1:=edt_EmployeeCode1.Text;
            EmployeeCode2:=edt_EmployeeCode2.Text;
            SaleTypeCode1:=edt_SaleTypeCode1.Text;
            SaleTypeCode2:=edt_SaleTypeCode2.Text;
            Invoiceno1:=edt_Invoiceno1.Text;
            Invoiceno2:=edt_Invoiceno2.Text;
            currencyCode:=edt_CurrencyCode1.Text;
          end;     
        end;
  finally
    Frm_Ar_Qry_Ar2_C.Free;
  end;

  sqltext:='select flag=0,sa_SaleInvoice.SaleTypeCode,'
           +      ' SaleType.SaleTypeName,'
           +      ' sa_SaleInvoice.SaleTypeCode+'' ''+SaleType.SaleTypeName as SaleTypeflag,'
           +      ' dateName(yy,sa_SaleInvoice.Billdate)+''.''+dateName(mm,sa_SaleInvoice.Billdate)+''.''+dateName(dd,sa_SaleInvoice.Billdate) as createdate,                                 '
           +      ' sa_SaleInvoice.Invoiceno,                                   '
           +      ' sa_SaleInvoice.TotaltaxAmount,                               '
           +      ' sa_SaleInvoice.TotaltaxAmount-sa_SaleInvoice.CancelAmount as Balance,'
           +'       Sa_SaleInvoice.TotalTaxAmountC, '
           +'       Sa_SaleInvoice.TotalTaxAmountC-Sa_SaleInvoice.CancelAmountC as BalanceC, '
           +      ' sa_SaleInvoice.currencyCode,                                '
           +      ' currency.currencyName,                                      '
           +      ' sa_SaleInvoice.currencyCode+'' ''+currency.currencyName as currencyflag'
           +' into #Ar5Qry '
          +' from  sa_SaleInvoice                                                         '
          +' left join SaleType on sa_SaleInvoice.SaleTypeCode=SaleType.SaleTypeCode                   '
          +' left join currency on sa_SaleInvoice.currencyCode=currency.currencyCode        '
          +' 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 sa_SaleInvoice.SaleTypeCode between '+quotedstr(SaleTypeCode1)+' and '+quotedstr(SaleTypeCode2)
          +'   and isnull(sa_SaleInvoice.SaleEmployeeCode,''0'') between '+quotedstr(EmployeeCode1)+' and '+quotedstr(EmployeeCode2)
          +'   and sa_SaleInvoice.currencyCode='+quotedstr(currencyCode);
  Executesql(AdoQry_Main,sqltext,1)          ;
  Executesql(AdoQry_tmp,'select * from #Ar5Qry',0);
  if AdoQry_tmp.RecordCount<> 0 then 
    begin
      sqltext:='insert into #Ar5Qry'
        +' select 1,''zz'',''  '',''合计'','
        +'        ''  '','
        +'        ''  '','
        +'        isnull(sum(isnull(TotaltaxAmount,0)),0),'
        +'        isnull(sum(isnull(Balance,0)),0),   '
        +'        isnull(sum(isnull(TotaltaxAmountC,0)),0),'
        +'        isnull(sum(isnull(BalanceC,0)),0),   '
        +'        ''  '','
        +'        ''  '','
        +'        ''  '''
        +' from #Ar5Qry  ';
      Executesql(AdoQry_Main,sqltext,1);

    sqltext:='insert into #Ar5Qry '
        +' select 1,SaleTypeCode,SaleTypeName,SaleTypeCode+'' ''+SaleTypeName+'' 小计'' as SaleTypeflag,'
        +'        ''  '','
        +'        ''  '','
        +'        sum(TotaltaxAmount),'
        +'        sum(Balance),   '
        +'        isnull(sum(isnull(TotaltaxAmountC,0)),0),'
        +'        isnull(sum(isnull(BalanceC,0)),0),   '
        +'        ''  '','
        +'        ''  '','
        +'        ''  '''
        +' from #Ar5Qry  '
        +' where SaleTypeCode<>''zz'' '
        +' group by SaleTypeCode,SaleTypeName ';
    Executesql(AdoQry_Main,sqltext,1);
  end;
        
  selectfromsql:='select * from #Ar5Qry';
  Executesql(AdoQry_Main,'select * from #Ar5Qry Order by SaleTypeCode,SaleTypeflag,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_Ar5.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ar_Qry_Ar5:=nil;
end;

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

procedure TFrm_Ar_Qry_Ar5.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.