sal_qry_analyzesaleorder1.pas

一个MRPII系统源代码版本

unit Sal_Qry_AnalyzeSaleOrder1;

Interface

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

Type
  TFrm_Sal_Qry_AnalyzeSaleOrder1 = Class(TFrm_Base_Qry)
    Label1: TLabel;
    AdoQry_MainCustomerCode: TStringField;
    AdoQry_MainSa_SaleOrderNumber: TIntegerField;
    AdoQry_MainTotalTaxAmount: TFloatField;
    AdoQry_MainCost: TFloatField;
    AdoQry_MainGrossProfit: TFloatField;
    AdoQry_MainAverageTaxAmount: TFloatField;
    AdoQry_MainAverageCost: TFloatField;
    AdoQry_MainAverageGrossProfit: TFloatField;
    AdoQry_MainCustomerName: TStringField;
    AdoQry_MainCustomerFalg: TStringField;
    AdoQry_MainAreaCode: TStringField;
    AdoQry_MainAreaName: TStringField;
    AdoQry_MainAreaFalg: TStringField;
    AdoQry_MainCustomerSaleEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainSaleEmployeeFalg: TStringField;
    DBText1: TDBText;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_LookExecute(Sender: TObject);
  private
    { Private declarations }
  public
    CreateDateStArt,CreateDateend,CustomerCodeStArt,CustomerCodeend,SloNoStArt,SloNoend,
    SaleTypeCodeStArt,SaleTypeCodeend,SaleDeptCodeStArt,SaleDeptCodeend,
    SaleEmployeeCodeStArt,SaleEmployeeCodeend,PayTermCodeStArt,PayTermCodeend,
    CurrencyCode,ItemCodeStArt,ItemCodeend:String;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Sal_Qry_AnalyzeSaleOrder1: TFrm_Sal_Qry_AnalyzeSaleOrder1;

implementation

uses Sal_Qry_AnalyzeSaleOrder1_C, Sys_Global, Sal_Qry_AnalyzeSaleOrder1_D;

{$R *.DFM}

{ TFrm_Sal_Qry_AnalyzeSaleOrder1 }

procedure TFrm_Sal_Qry_AnalyzeSaleOrder1.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var
  SqlText:String;
  AdoQuery:TAdoQuery;
begin
  inherited;
  Frm_Sys_Condition:= TFrm_Sal_Qry_AnalyzeSaleOrder1_C.Create(self);
  act_filter.Execute;
  Frm_Sys_Condition.Release;
  Frm_Sys_Condition:=nil;
  CreatePanel(1,DBGridEh);
  AdoQuery:=TAdoQuery.Create(Self);
  AdoQuery.Connection :=DbConnect;
  SqlText:=' Select Sa_SaleOrder.CustomerCode,'
          +'       Count(Distinct Sa_SaleOrder.SloNo) As Sa_SaleOrderNumber, '
          +'       Sum(TaxAmount)AS TotalTaxAmount,  Sum(Cost)As Cost, '
          +'       Sum(TaxAmount)-Sum(Cost) As GrossProfit, '
          +'       Sum(TaxAmount)/Count(Distinct Sa_SaleOrder.SloNo) As AverageTaxAmount , '
          +'       Sum(Cost)/Count(Distinct Sa_SaleOrder.SloNo) As AverageCost,'
          +'       (Sum(TaxAmount)-Sum(Cost))/Count(Distinct Sa_SaleOrder.SloNo) As AverageGrossProfit '
          +' Into #AnalyzeSaleOrder1                                 '
          +' From Sa_SaleOrder   '
          +'   Join  Sa_SaleOrderLine '
          +'   On Sa_SaleOrder.SloNo=Sa_SaleOrderLine.SloNo '
          +'   Where (Sa_SaleOrder.CreateDate Between '''+CreateDateStArt+''' And '''+CreateDateend+''')  And '
          +'         (isnull(Sa_SaleOrder.CustomerCode,'''') Between '''+CustomerCodeStArt+''' And '''+CustomerCodeend+''') And '
          +'         (Sa_SaleOrder.SloNo Between '''+SloNoStArt+''' And '''+SloNoend+''') And '
          +'         (isnull(Sa_SaleOrder.SaleTypeCode,'''') Between '''+SaleTypeCodeStArt+''' And '''+SaleTypeCodeend+''') And '
          +'         (isnull(Sa_SaleOrder.SaleDeptCode,'''') Between '''+SaleDeptCodeStArt+''' And '''+SaleDeptCodeend+''') And '
          +'         (isnull(Sa_SaleOrder.SaleEmployeeCode,'''') Between '''+SaleEmployeeCodeStArt+''' And '''+SaleEmployeeCodeend+''') And '
          +'         (isnull(Sa_SaleOrder.PayTermCode,'''') Between '''+PayTermCodeStArt+''' And '''+PayTermCodeend+''') And '
          +'         (isnull(Sa_SaleOrderLine.ItemCode,'''') Between '''+ItemCodeStArt+''' And '''+ItemCodeend+''') And '
          +'         CurrencyCode='''+CurrencyCode+''' '
          +' Group By Sa_SaleOrder.CustomerCode ';
  ExecuteSql(AdoQry_Tmp,SqlText,1);
  SelectFromSQL:=' Select #AnalyzeSaleOrder1.*,Customer.CustomerName,'
                +'        #AnalyzeSaleOrder1.CustomerCode+'' ''+Customer.CustomerName As CustomerFalg, '
                +'        Customer.AreaCode,Area.AreaName,Customer.AreaCode+'' ''+Area.AreaName As AreaFalg ,'
                +'        Customer.CustomerSaleEmployeeCode,Employee.EmployeeName,'
                +'        Customer.CustomerSaleEmployeeCode+'' ''+Employee.EmployeeName As SaleEmployeeFalg '
                +' From #AnalyzeSaleOrder1 '
                +'   left Join Customer '
                +'   On #AnalyzeSaleOrder1.CustomerCode=Customer.CustomerCode '
                +'   Left Join Area '
                +'   On Customer.AreaCode=Area.AreaCode '
                +'   left Join Employee '
                +'   On Customer.CustomerSaleEmployeeCode=Employee.EmployeeCode ';
    SqlText:=' Select #AnalyzeSaleOrder1.*,Customer.CustomerName,'
            +'        #AnalyzeSaleOrder1.CustomerCode+'' ''+Customer.CustomerName As CustomerFalg, '
            +'        Customer.AreaCode,Area.AreaName,Customer.AreaCode+'' ''+Area.AreaName As AreaFalg ,'
            +'        Customer.CustomerSaleEmployeeCode,Employee.EmployeeName,'
            +'        Customer.CustomerSaleEmployeeCode+'' ''+Employee.EmployeeName As SaleEmployeeFalg '
            +' From #AnalyzeSaleOrder1 '
            +'   left Join Customer '
            +'   On #AnalyzeSaleOrder1.CustomerCode=Customer.CustomerCode '
            +'   Left Join Area '
            +'   On Customer.AreaCode=Area.AreaCode '
            +'   left Join Employee '
            +'   On Customer.CustomerSaleEmployeeCode=Employee.EmployeeCode '
            +'   Order By TotalTaxAmount DESC';

  ExecuteSql(AdoQry_Main,SqlText,0);
  if ShowAnimate<>nil then ShowAnimate.Free;
    if ShowPanel<>nil then ShowPanel.Free;
  AdoQuery.Free;
end;

procedure TFrm_Sal_Qry_AnalyzeSaleOrder1.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Try
    ExecuteSql(AdoQry_tmp,'Drop Table #AnalyzeSaleOrder1 ',1);
  except
  end;
end;

procedure TFrm_Sal_Qry_AnalyzeSaleOrder1.Act_LookExecute(Sender: TObject);
begin
  inherited;
  if AdoQry_Main.RecordCount=0 then exit;
  Application.CreateForm(TFrm_Sal_Qry_AnalyzeSaleOrder1_D,Frm_Sal_Qry_AnalyzeSaleOrder1_D);
  Frm_Sal_Qry_AnalyzeSaleOrder1_D.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
  Frm_Sal_Qry_AnalyzeSaleOrder1_D.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
  Frm_Sal_Qry_AnalyzeSaleOrder1_D.InitForm(DBConnect,'Query',AdoQry_Main);
  Frm_Sal_Qry_AnalyzeSaleOrder1_D.Show;
end;

end.