sal_qry_analyzeshiporder1_d.pas

一个MRPII系统源代码版本

unit Sal_Qry_AnalyzeShipOrder1_D;

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_AnalyzeShipOrder1_D = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainSa_SaleOrderNumber: TIntegerField;
    AdoQry_MainTotalTaxAmount: TFloatField;
    AdoQry_MainCost: TFloatField;
    AdoQry_MainGrossProfit: TFloatField;
    AdoQry_MainAverageTaxAmount: TFloatField;
    AdoQry_MainAverageCost: TFloatField;
    AdoQry_MainAverageGrossProfit: TFloatField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainItemFalg: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainSloQty: TFloatField;
    DBText1: TDBText;
    Label1: TLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
  procedure InitForm(AdOConnection:TAdOConnection;FormStatus:String;
      AdoQuery:TAdoQuery);

    { Public declarations }
  end;

var
  Frm_Sal_Qry_AnalyzeShipOrder1_D: TFrm_Sal_Qry_AnalyzeShipOrder1_D;

implementation

uses Sal_Qry_AnalyzeShipOrder1, Sys_Global;

{$R *.DFM}

{ TFrm_Sal_Qry_AnalyzeShipOrder1_D }

procedure TFrm_Sal_Qry_AnalyzeShipOrder1_D.InitForm(
  AdOConnection: TAdOConnection; FormStatus: String; AdoQuery: TAdoQuery);
var
  SqlText:String;
begin
  SetDBConnect(AdOConnection);
  TAdoQuery(DBGridEh.DataSource.DataSet).Connection:=AdOConnection;
  SqlText:=' Select Tmp.*,ItemName,Tmp.ItemCode+'' ''+Item.ItemName  As ItemFalg,UomName '
          +' Into #TmpInvOutBill1 '
          +' From             '
          +' (Select InvOutBillLine.ItemCode, '
          +'        Count(Distinct InvOutBill.InvBillNo) As Sa_SaleOrderNumber,  '
          +'        Sum(TotalInvBillAmount)AS TotalTaxAmount, (Sum(InvBillQty)-Sum(IsNull(BackShipQty,0))) AS SloQty, Sum(Cost)As Cost, '
          +'        Sum(TotalInvBillAmount)-Sum(Cost) As GrossProfit, '
          +'        Sum(TotalInvBillAmount)/Count(Distinct InvOutBill.InvBillNo) As AverageTaxAmount ,   '
          +'        Sum(Cost)/Count(Distinct InvOutBill.InvBillNo) As AverageCost,  '
          +'        (Sum(TotalInvBillAmount)-Sum(Cost))/Count(Distinct InvOutBill.InvBillNo) As AverageGrossProfit '
          +' From InvOutBill'
          +'   Join  InvOutBillLine '
          +'   On InvOutBill.InvBillID=InvOutBillLine.InvBillID '
          +'   Where (InvOutBill.InvBillDate Between '''+Frm_Sal_Qry_AnalyzeShipOrder1.CreateDateStArt+''' And '''+Frm_Sal_Qry_AnalyzeShipOrder1.CreateDateend+''')  And '
          +'         (InvOutBill.CustomerCode ='''+AdoQuery.fieldbyname('CustomerCode').AsString+''') And '
          +'         (InvOutBill.InvBillNo Between '''+Frm_Sal_Qry_AnalyzeShipOrder1.SloNoStArt+''' And '''+Frm_Sal_Qry_AnalyzeShipOrder1.SloNoend+''') And '
          +'         (InvOutBill.SaleType Between '''+Frm_Sal_Qry_AnalyzeShipOrder1.SaleTypeCodeStArt+''' And '''+Frm_Sal_Qry_AnalyzeShipOrder1.SaleTypeCodeend+''') And '
          +'         (InvOutBill.DeptCode Between '''+Frm_Sal_Qry_AnalyzeShipOrder1.SaleDeptCodeStArt+''' And '''+Frm_Sal_Qry_AnalyzeShipOrder1.SaleDeptCodeend+''') And '
          +'         (InvOutBill.Sal_EmployeeCode Between '''+Frm_Sal_Qry_AnalyzeShipOrder1.SaleEmployeeCodeStArt+''' And '''+Frm_Sal_Qry_AnalyzeShipOrder1.SaleEmployeeCodeend+''') And '
          +'         (InvOutBill.PayTermCode Between '''+Frm_Sal_Qry_AnalyzeShipOrder1.PayTermCodeStArt+''' And '''+Frm_Sal_Qry_AnalyzeShipOrder1.PayTermCodeend+''') And '
          +'         (InvOutBillLine.ItemCode Between '''+Frm_Sal_Qry_AnalyzeShipOrder1.ItemCodeStArt+''' And '''+Frm_Sal_Qry_AnalyzeShipOrder1.ItemCodeend+''') And '
          +'         InvOutBill.CurrencyCode='''+Frm_Sal_Qry_AnalyzeShipOrder1.CurrencyCode+'''  '
          +' Group By InvOutBillLine.ItemCode) Tmp  '
          +'   Join Item'
          +'     On Tmp.ItemCode=Item.ItemCode '
          +'   Join Uom  '
          +'     On Item.UomCode=Uom.UomCode ' ;
   ExecuteSql(AdoQry_Main,SQLText,1);
   SelectFromSQL:=' Select * from  #TmpInvOutBill1 ';
   ExecuteSql(AdoQry_Main,' Select * from  #TmpInvOutBill1 Order By TotalTaxAmount DESC',0);

end;

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

end.