sal_qry_totalsaleorder1.pas

一个MRPII系统源代码版本

unit Sal_Qry_TotalSaleOrder1;

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_TotalSaleOrder1 = Class(TFrm_Base_Qry)
    AdoQry_MainCustomerFalg: TStringField;
    AdoQry_MainCreateDate: TStringField;
    AdoQry_MainSloNo: TStringField;
    AdoQry_MainSloQty: TFloatField;
    AdoQry_MainShipQty: TFloatField;
    AdoQry_MainNoTaxAmount: TFloatField;
    AdoQry_MainTaxAmount: TFloatField;
    AdoQry_MainSaleTypeFalg: TStringField;
    AdoQry_MainSaleDeptFalg: TStringField;
    AdoQry_MainSaleEmployeeFalg: TStringField;
    AdoQry_MainTaxRate_Percent: TFloatField;
    AdoQry_MainPayTermFalg: TStringField;
    AdoQry_MainShipModeFalg: TStringField;
    AdoQry_MainCustomerShipAddressFalg: TStringField;
    AdoQry_Mainfalg: TIntegerField;
    DBText1: TDBText;
    Label1: TLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_LookExecute(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
    { Private declarations }
  public
    CreateDateStArt,CreateDateend,CustomerCodeStArt,CustomerCodeend,SloNoStArt,SloNoend,
    SaleTypeCodeStArt,SaleTypeCodeend,SaleDeptCodeStArt,SaleDeptCodeend,
    SaleEmployeeCodeStArt,SaleEmployeeCodeend,PayTermCodeStArt,PayTermCodeend,CurrencyCode:String;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Sal_Qry_TotalSaleOrder1: TFrm_Sal_Qry_TotalSaleOrder1;

implementation

uses Sal_Qry_TotalSaleOrder1_C, Sys_Global, Sal_Enter_SaleOrder;

{$R *.DFM}

{ TFrm_Sal_Qry_TotalSaleOrder1 }

procedure TFrm_Sal_Qry_TotalSaleOrder1.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var
  SqlText:String;
  AdoQuery:TAdoQuery;
begin
  inherited;
  Frm_Sys_Condition:= TFrm_Sal_Qry_TotalSaleOrder1_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+'' ''+ Customer.CustomerName as CustomerFalg,Convert(varchAr(12),Sa_SaleOrder.CreateDate,102) As CreateDate,'
          +'        convert(varchAr(500),Sa_SaleOrder.SloNo) as slono,Sa_SaleOrderLine.SloQty,Sa_SaleOrderLine.ShipQty,'
          +'        Sa_SaleOrderLine.NoTaxAmount,Sa_SaleOrderLine.TaxAmount,  '
          +'        Sa_SaleOrder.SaleTypeCode+'' ''+SaleType.SaleTypeName As SaleTypeFalg,   '
          +'        Sa_SaleOrder.SaleDeptCode+'' ''+Dept.DeptName As SaleDeptFalg, '
          +'        Sa_SaleOrder.SaleEmployeeCode+'' ''+Employee.EmployeeName As SaleEmployeeFalg,  '
          +'        Sa_SaleOrder.TaxRate_Percent, '
          +'        Sa_SaleOrder.PayTermCode+'' ''+PayTerm.PayTermName As PayTermFalg, '
          +'        Sa_SaleOrder.ShipModeCode+'' ''+ShipMode.ShipModeName As ShipModeFalg,   '
          +'        Sa_SaleOrder.ShipAddresSCode+'' ''+CustomerShipAddress.ShipAddressName As CustomerShipAddressFalg,falg=1 '
          +' Into #TotalSaleOrder1                                 '
          +' From Sa_SaleOrder Join Sa_SaleOrderLine  '
          +'      On Sa_SaleOrder.SloNo=Sa_SaleOrderLine.SloNo '
          +'      left Join Customer  '
          +'      On Sa_SaleOrder.CustomerCode=Customer.CustomerCode  '
          +'      left Join SaleType  '
          +'      On Sa_SaleOrder.SaleTypeCode=SaleType.SaleTypeCode '
          +'      left Join Dept '
          +'      On Sa_SaleOrder.SaleDeptCode=Dept.DeptCode   '
          +'      left Join Employee '
          +'      On Sa_SaleOrder.SaleEmployeeCode=Employee.EmployeeCode '
          +'      left Join PayTerm '
          +'      On Sa_SaleOrder.PayTermCode=PayTerm.PayTermCode '
          +'      left Join ShipMode '
          +'      On Sa_SaleOrder.ShipModeCode=ShipMode.ShipModeCode  '
          +'      left Join CustomerShipAddress   '
          +'      On (Sa_SaleOrder.ShipAddresSCode= CustomerShipAddress.ShipAddresSCode And '
          +'           Sa_SaleOrder.CustomerCode= CustomerShipAddress.CustomerCode) '
          +'   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 '
          +'         CurrencyCode='''+CurrencyCode+''' '
          +' Order By Sa_SaleOrder.CustomerCode, Sa_SaleOrder.CreateDate,Sa_SaleOrder.SloNo ';
  ExecuteSql(AdoQry_Tmp,SqlText,1);
  SqlText:=' Select  CustomerFalg,Sum(SloQty) As SloQty,Sum(ShipQty) As ShipQty,'
          +'        Sum(NoTaxAmount) As NoTaxAmount,Sum(TaxAmount) As TaxAmount '
          +' From #TotalSaleOrder1 '
          +' Group By CustomerFalg ';
  ExecuteSql(AdoQry_Tmp,SqlText,0);
  AdoQry_Tmp.First;
  while not AdoQry_Tmp.Eof do
  begin
    SqlText:=' insert #TotalSaleOrder1 (CustomerFalg,CreateDate,SloQty,ShipQty,'
            +'                          NoTaxAmount,TaxAmount,falg,slono,SaleTypeFalg)'
            +'   Values( '
            +''''+AdoQry_Tmp.fieldbyname('CustomerFalg').Asstring+'''+'' 小计'','
            +''' 小计: '','    //'''+AdoQry_Tmp.fieldbyname('CustomerFalg').Asstring+'''+
            +''''+AdoQry_Tmp.fieldbyname('SloQty').Asstring+''','
            +''''+AdoQry_Tmp.fieldbyname('ShipQty').Asstring+''','
            +''''+AdoQry_Tmp.fieldbyname('NoTaxAmount').Asstring+''','
            +''''+AdoQry_Tmp.fieldbyname('TaxAmount').Asstring+''','
            +'2,'
            +''''+AdoQry_Tmp.fieldbyname('CustomerFalg').Asstring+'''+'' 小计'','
            +''''')';
    ExecuteSql(AdoQuery,SqlText,1);
    AdoQry_Tmp.Next;
  end;
  SelectFromSQL:=' Select * From #TotalSaleOrder1 ';
  OrderByFields:=' CustomerFalg,falg ';
  Getdata;
  if ShowAnimate<>nil then ShowAnimate.Free;
    if ShowPanel<>nil then ShowPanel.Free;  
  AdoQuery.Free;
end;

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

procedure TFrm_Sal_Qry_TotalSaleOrder1.Act_LookExecute(Sender: TObject);
begin
  inherited;
  if AdoQry_Main.RecordCount=0 then exit;
  if AdoQry_Main.fieldbyname('falg').asinteger=2 then exit;
  Application.CreateForm(TFrm_Sal_Enter_SaleOrder,Frm_Sal_Enter_SaleOrder);
  Frm_Sal_Enter_SaleOrder.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
  Frm_Sal_Enter_SaleOrder.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
  Frm_Sal_Enter_SaleOrder.InitForm(DBConnect,'Query',AdoQry_Main);
  Frm_Sal_Enter_SaleOrder.ShowModal;
end;

procedure TFrm_Sal_Qry_TotalSaleOrder1.DBGridEhGetCellParams(
  Sender: TObject; Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
  if AdoQry_Mainfalg.Value = 2 then
  begin
    Background:=$00F9D1C6;
    afont.Color:=clblack;
  end;
end;

end.