mrp_qry_classifystatmo_f.pas

一个MRPII系统源代码版本

unit Mrp_Qry_ClassifyStatMo_F;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Condition, StdCtrls, Mask, Db, AdODB;

Type
  TFrm_Mrp_Qry_ClassifyStatMo_F = Class(TFrm_Base_Condition)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    MEdt_StArt_Date: TMaskEdit;
    MEdt_End_Date: TMaskEdit;
    Edt_StArt_ItemCode: TEdit;
    Edt_End_ItemCode: TEdit;
    Edt_StArt_MoNo: TEdit;
    Edt_End_MoNo: TEdit;
    Edt_StArt_Linestatus: TEdit;
    Edt_End_Linestatus: TEdit;
    Edt_StArt_FinishRate: TEdit;
    Edt_End_FinishRate: TEdit;
    Cmbx_Total: TComboBox;
    Cmbx_ProdurtType: TComboBox;
    Cmbx_SaleType: TComboBox;
    Label14: TLabel;
    Edt_PValue: TEdit;
    procedure FormCreate(Sender: TObject);
    procedure Edt_StArt_ItemCodeExit(Sender: TObject);
    procedure Edt_End_ItemCodeExit(Sender: TObject);
    procedure Edt_StArt_MoNoExit(Sender: TObject);
    procedure Edt_End_MoNoExit(Sender: TObject);
    procedure Edt_StArt_LinestatusExit(Sender: TObject);
    procedure Edt_End_LinestatusExit(Sender: TObject);
    procedure Edt_StArt_FinishRateExit(Sender: TObject);
    procedure Edt_End_FinishRateExit(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure Edt_StArt_ItemCodeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Edt_End_ItemCodeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    procedure SetDBConnect(AdOConnection:TAdOConnection);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_ClassifyStatMo_F: TFrm_Mrp_Qry_ClassifyStatMo_F;

implementation

uses Sys_Global, Mrp_Qry_ClassifyStatMo;

{$R *.DFM}
procedure TFrm_Mrp_Qry_ClassifyStatMo_F.SetDBConnect(AdOConnection:TAdOConnection);
var
  SqlText:String;
begin
  Inherited;
  SqlText:=' Select * '
          +' From SysParam '
          +' Where SubString(SysParamCode,1,8)='''+'SaleType'+'''';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
  Cmbx_SaleType.clear;
  While Not AdoQry_Tmp.Eof Do
  begin
    Cmbx_SaleType.Items.Add(AdoQry_Tmp.fieldbyname('SysParamValueC').AsString);
    AdoQry_Tmp.Next;
  end;
  Cmbx_SaleType.Items.Add('  ');
  Cmbx_SaleType.ItemIndex:=Cmbx_SaleType.Items.Count-1;
  SqlText:=' select S.SysParamValueC '
          +' from SysParam s '
          +' where upper(SubString(SysParamCode,1,9))=Upper('''+'ItemClass'+''') ';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
  Cmbx_ProdurtType.Items.clear;
  While Not AdoQry_Tmp.Eof Do
  begin
    Cmbx_ProdurtType.Items.Add(AdoQry_Tmp.fieldbyname('SysParamValueC').AsString);
    AdoQry_Tmp.Next;
  end;
  Cmbx_ProdurtType.Items.Add('  ');
  Cmbx_ProdurtType.ItemIndex:=Cmbx_SaleType.Items.Count-1;
  
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.FormCreate(Sender: TObject);
begin
  inherited;
  MEdt_StArt_Date.Text:=FormatDateTime('yyyy.mm.dd',Date-30);
  MEdt_End_Date.Text:=FormatDateTime('yyyy.mm.dd',Date);
  Edt_StArt_ItemCode.Text:='0';
  Edt_End_ItemCode.Text:='ZZZZZ';
  Edt_StArt_MoNo.Text:='0';
  Edt_End_MoNo.Text:='ZZZZZ';
  Edt_StArt_LineStatus.Text:='5';
  Edt_End_LineStatus.Text:='6';
  Edt_StArt_FinishRate.Text:='0';
  Edt_End_FinishRate.Text:='100';
  Cmbx_Total.Items.clear;
  Cmbx_Total.Items.Add('日');
  Cmbx_Total.Items.Add('周');
  Cmbx_Total.Items.Add('月');
  Cmbx_Total.ItemIndex:=0;
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_StArt_ItemCodeExit(
  Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='0';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_End_ItemCodeExit(
  Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='ZZZZZ';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_StArt_MoNoExit(
  Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='0';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_End_MoNoExit(Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='ZZZZZ';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_StArt_LinestatusExit(
  Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='5';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_End_LinestatusExit(
  Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='6';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_StArt_FinishRateExit(
  Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='0';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_End_FinishRateExit(
  Sender: TObject);
begin
  inherited;
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  If Length(Trim(TEdit(Sender).Text))=0 Then
     TEdit(Sender).Text:='100';
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.btn_okClick(Sender: TObject);
var
  SqlText,SysParamCode,SalesValue:String;
begin
  inherited;
  If (Trim(Edt_PValue.Text)<>'') And (Trim(Cmbx_ProdurtType.Text)='') Then
  begin
    DispInfo('请选择一种产品类型!',3);
    Cmbx_ProdurtType.SetFocus;
    Abort;
  end;
  SqlText:=' Select Mo.MoNo,Mo.MoDate,Mo.SaleType,Item.ItemCode,Item.ItemName,Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag,Uom.UomName,Sum(MoLine.MoQty) As MoQty,Sum(MoLine.MoQty-MoLine.MoNoFinishQty) As FinishQty,'
          +' Sum(MoLine.MoNoFinishQty) As MoNoFinishQty, '
          +' Case When Sum(MoLine.MoQty)=0 Then 0 '
          +' Else (Sum(MoLine.MoQty-MoNoFinishQty)/Sum(MoLine.MoQty)*100) '
          +' end FinishRate1 '
          +' Into #TmpClassMo '
          +' From Mo,MoLine,Item,Uom '
          +' Where (Mo.MoNo=MoLine.MoNo)  '
          +' And MoLine.ItemCode=Item.ItemCode '
          +' And Item.UomCode=Uom.UomCode '
          +' And  (Mo.MODATE >='''+MEdt_StArt_Date.Text+''''
          +' And  Mo.MODATE<='''+MEdt_End_Date.Text+''''
          +' ) And  (MoLine.ItemCode >='''+Trim(Edt_StArt_ItemCode.Text)+''''
          +' And MoLine.ItemCode<='''+Trim(Edt_End_ItemCode.Text)+''''
          +' ) And (Mo.MoNo >= '''+Trim(Edt_StArt_MoNo.Text)+''''
          +' And Mo.MoNo<=''' +Trim(Edt_End_MoNo.Text)+''''
          +' ) And (MoLine.MoLineStatus >='''+Trim(Edt_StArt_LineStatus.Text)+''''
          +' And MoLine.MoLineStatus<='''+Trim(Edt_End_LineStatus.Text)+''')'
          +' Group By Mo.MoNo,Mo.MoDate,Item.ItemCode,Item.ItemName,Item.ItemCode+'''+' '+'''+Item.ItemName,Mo.SaleType,Uom.UomName'
          +' Order By Mo.MoDate,ItemFlag';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.Sql.Text:=SqlText;
  AdoQry_Tmp.ExecSQL;
  If Length(Trim(TComBoBox(Cmbx_ProdurtType).Text))<>0 Then
  begin
    SqlText:=' Select SysParamCode '
             +' From SysParam '
             +' Where SysParamValueC='''+Trim(TComBoBox(Cmbx_ProdurtType).Text)+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    If AdoQry_Tmp.Eof Then
    begin
      DispInfo('数据已被其它用户改变，请退出后重新进入!',1);
      Abort;
    end
    Else
      SysParamCode:=AdoQry_Tmp.fieldbyname('SysParamCode').AsString;
  end;
    If Length(Trim(TComBoBox(Cmbx_SaleType).Text))<>0 Then
  begin
    SqlText:=' Select SysParamCode '
             +' From SysParam '
             +' Where SysParamValueC='''+Trim(TComBoBox(Cmbx_SaleType).Text)+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    If AdoQry_Tmp.Eof Then
    begin
      DispInfo('数据已被其它用户改变，请退出后重新进入!',1);
      Abort;
    end
    Else
      SalesValue:=AdoQry_Tmp.fieldbyname('SysParamCode').AsString;
  end;
  Case Cmbx_Total.ItemIndex Of
    0:SqlText:=' Select M.MoDate As Date1,';
    1:SqlText:=' Select Convert(varchAr,DatepArt(Month,M.MoDate))+'''+'-'+'''+ConVert(varchAr,(datepArt(wk,M.MODate)-datepArt(wk,convert(chAr(4),datepArt(yy,M.MODate))+'''+'.'+'''+convert(chAr(2),datepArt(mm,M.MODate))+'''+'.01'+''')+1)) As Date1,';
    2:SqlText:=' Select Convert(varchAr,DatepArt(Year,M.MoDate))+'''+'.'+'''+Convert(varChAr,DatepArt(Month,M.MoDate)) As Date1,';
  end;
  SqlText:=SqlText+'M.ItemFlag,M.UomName,M.ItemCode,M.ItemName,'
          +' Sum(M.MoQty) as MoTotalQty,Sum(M.FinishQty) As FinishedQty,Sum(M.MoNoFinishQty) As MoNoFinishedQty, '
          +' Case When Sum(M.MoQty)=0 Then 0 '
          +' Else Sum(M.FinishQty)/Sum(M.MoQty)*100 '
          +' end FinishedRate '
          +' From #TmpClassMo M,Item'
          +' where M.ItemCode=Item.ItemCode '
          +' And (M.FinishRate1>='''+Trim(Edt_StArt_FinishRate.Text)+''''
          +' And M.FinishRate1<='''+Trim(Edt_End_FinishRate.Text)+''''+')';
 If Length(Trim(TComBoBox(Cmbx_SaleType).Text))<>0 Then
   SqlText:=SqlText+' And M.SaleType='''+Trim(SalesValue)+'''';     //销售类型值
 If (Length(Trim(TComBoBox(Cmbx_ProdurtType).Text))<>0) And
    (Trim(Edt_PValue.Text)<>'') Then
   SqlText:=SqlText+' And  Item.'+Copy(SysParamCode,1,10)+'='''+Trim(Edt_PValue.Text)+''''; //产品类型值
 SqlText:=SqlText+' Group By M.ItemFlag,M.UomName,M.ItemCode,M.ItemName,';
 Case Cmbx_Total.ItemIndex Of
    0: SqlText:=SqlText+'  M.MoDate ';
    1: SqlText:=SqlText+'  Convert(varchAr,DatepArt(Month,M.MoDate))+'''+'-'+'''+ ConVert(varchAr,(datepArt(wk,M.MODate)-datepArt(wk,convert(chAr(4),datepArt(yy,M.MODate))+'''+'.'+'''+convert(chAr(2),datepArt(mm,M.MODate))+'''+'.01'+''')+1)) ';
    2: SqlText:=SqlText+'  Convert(varchAr,DatepArt(Year,M.MoDate))+'''+'.'+'''+Convert(varChAr,DatepArt(Month,M.MoDate))';
  end;
  If Length(Copy(SysParamCode,1,10))<>0 Then
    SqlText:=SqlText+',Item.'+Copy(SysParamCode,1,10);
  If Length(Trim(TComBoBox(Cmbx_SaleType).Text))<>0 Then
    SqlText:=SqlText+','+'M.SaleType';
  SqlText:=SqlText+' Order By Date1,M.ItemFlag';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
  If AdoQry_Tmp.Eof Then
  begin
    SqlText:=' Drop Table #TmpClassMo';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Sql.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
    DispInfo('没找到适合条件的记录，请重新输入条件!',1);
    MEdt_StArt_Date.SetFocus;
    Abort;
  end;
  Frm_Mrp_Qry_ClassifyStatMo.AdoQry_Main.Clone(AdoQry_Tmp);
  Frm_Mrp_Qry_ClassifyStatMo.Lbl_Condition.Caption:=' 下单日期从: '+MEdt_StArt_Date.Text+' 到 '+Medt_End_Date.Text
        +' / 物料代码从: '+Trim(Edt_StArt_ItemCode.Text)+' 到 '+Trim(Edt_End_ItemCode.Text)
        +' / 生产订单号从: '+Trim(Edt_StArt_MoNo.Text)+' 到 '+Trim(Edt_End_MoNo.Text)
        +#13+#10
        +' / 行状态从：'+Trim(Edt_StArt_LineStatus.Text)+' 到 '+Trim(Edt_End_LineStatus.Text)
        +' / 完成率从：'+Trim(Edt_StArt_FinishRate.Text)+'% 到 '+Trim(Edt_End_FinishRate.Text)+'%'
        +' / 汇总阶段:'+Trim(Cmbx_Total.Text)
        +' / 产品类型：'+Trim(Cmbx_ProdurtType.Text)
        +' / 销售类型：'+Trim(Cmbx_SaleType.Text);
  Frm_Mrp_Qry_ClassifyStatMo.DBGridEh.Columns[0].Title.Caption:='统计'+Trim(Cmbx_Total.Text);
  Frm_Mrp_Qry_ClassifyStatMo.Lbl_Type.Caption:='统计'+Trim(Cmbx_Total.Text)+':';
  If (Trim(Cmbx_Total.Text)='月') Or (Trim(Cmbx_Total.Text)='周') Then
  Frm_Mrp_Qry_ClassifyStatMo.DBGridEh.Columns[0].Width:=50
  Else
  Frm_Mrp_Qry_ClassifyStatMo.DBGridEh.Columns[0].Width:=70;
  Frm_Mrp_Qry_ClassifyStatMo.Lbl_Order.Caption:='统计'+Trim(Cmbx_Total.Text)+'/物料标识';
  SqlText:=' Drop Table #TmpClassMo';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.Sql.Text:=SqlText;
  AdoQry_Tmp.ExecSQL;
  ModalResult:=Mrok;
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.FormActivate(Sender: TObject);
begin
  inherited;
  MEdt_StArt_Date.AutoSelect:=False;
  MEdt_StArt_Date.SetFocus;
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_StArt_ItemCodeKeyDown(
  Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  inherited;
 If key=120 Then
  CommOnHint(Sender,AdoQry_Tmp,'ItemName','物料描述','ItemCode',
             '物料代码',' Item ',' PmCode In (0,3) and ItemUsable=1 ');
end;

procedure TFrm_Mrp_Qry_ClassifyStatMo_F.Edt_End_ItemCodeKeyDown(
  Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  inherited;
 If key=120 Then
  CommOnHint(Sender,AdoQry_Tmp,'ItemName','物料描述','ItemCode',
             '物料代码',' Item ',' PmCode In (0,3) and ItemUsable=1 ');
end;

end.