ar_qry_ar4.pas

一个MRPII系统源代码版本

unit Ar_Qry_Ar4;

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_Ar4 = 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_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_Ar4: TFrm_Ar_Qry_Ar4;

implementation
uses Ar_Qry_Ar4_C,Sys_Global;
{$R *.DFM}
procedure TFrm_Ar_Qry_Ar4.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;
    currencyCode1,currencyCode2:string;
begin
  Application.ProcessMessages;
  inherited;
  date1:='';
  date2:='';
  customerCode1:='';
  customerCode2:='';
  DeptCode1:='';
  DeptCode2:='';
  EmployeeCode1:='';
  EmployeeCode2:='';
  SaleTypeCode1:='';
  SaleTypeCode2:='';
  Invoiceno1:='';
  Invoiceno2:='';
  currencyCode1:='';
  currencyCode2:='';
  try
    with TFrm_Ar_Qry_Ar4_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;
            currencyCode1:=edt_CurrencyCode1.Text;
            currencyCode2:=edt_CurrencyCode2.text;
          end;     
      end;
  finally
    Frm_Ar_Qry_Ar4_C.Free;
  end;

  sqltext:='select  flag=0,sa_SaleInvoice.currencyCode,                                '
           +      ' currency.currencyName,                                      '
           +      ' sa_SaleInvoice.currencyCode+'' ''+currency.currencyName as currencyflag,'
           +'       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 '
           +' into #Ar4Qry '
           +' from  sa_SaleInvoice                                                         '
           +' left join currency on sa_SaleInvoice.currencyCode=currency.currencyCode        '
           +' where sa_SaleInvoice.Billdate between '+quotedstr(date1)+' and '+quotedstr(date2)
           +'   and sa_SaleInvoice.SaleTypeCode between '+quotedstr(SaleTypeCode1)+' and '+quotedstr(SaleTypeCode2)
           +'   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 between '+quotedstr(currencyCode1)+' and '+quotedstr(currencyCode2);
  Executesql(AdoQry_Main,sqltext,1)          ;
  Executesql(AdoQry_tmp,'select * from #Ar4Qry',0);
  if AdoQry_tmp.RecordCount<> 0 then 
  begin
    sqltext:='insert into #Ar4Qry '
          +' select 1,currencyCode,currencyName,currencyCode+'' ''+currencyName+'' 小计'' as currencyflag,'
          +'        ''  '','
          +'        ''  '','
          +'        sum(TotaltaxAmount),'
          +'        sum(Balance) ,  '
          +'        sum(TotaltaxAmountC),'
          +'        sum(BalanceC)   '
          +' from #Ar4Qry  '
          +' group by currencyCode,currencyName ';
    Executesql(AdoQry_Main,sqltext,1);
  end;
        
  selectfromsql:='select * from #Ar4Qry';
  Executesql(AdoQry_Main,'select * from #Ar4Qry Order by currencyCode,currencyflag,createdate,Invoiceno',0);
  lbl_Condition.Caption:='发票日期从     '+date1+' 到 '+date2+' / 销售客户代码从 '+customerCode1+' 到 '+customerCode2+' / 销售类型代码从 '+SaleTypeCode1+' 到 '+SaleTypeCode2
                        +#13+'销售部门代码从 '+DeptCode1+' 到 '+DeptCode2+' / 营销业务员代码从 '+EmployeeCode1+' 到 '+EmployeeCode2
                        +#13+'发票单号从     '+Invoiceno1+' 到 '+Invoiceno2+' /货币代码从 '+currencyCode1+' 到 '+currencyCode2;
end;

procedure TFrm_Ar_Qry_Ar4.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ar_Qry_Ar4:=nil;
end;

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

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