pm_qry_analyzerpo6_c.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerPo6_C;

Interface

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

Type
  TFrm_Pm_Qry_AnalyzerPo6_C = Class(TFrm_Base_Condition)
    ExtEdit1: TExtEdit;
    ExtEdit2: TExtEdit;
    ExtEdit3: TExtEdit;
    ExtEdit4: TExtEdit;
    ExtEdit5: TExtEdit;
    ExtEdit6: TExtEdit;
    ExtEdit7: TExtEdit;
    ExtEdit8: TExtEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    MaskEdit1: TMaskEdit;
    MaskEdit2: TMaskEdit;
    ExtEdit9: TExtEdit;
    Label11: TLabel;
    Label12: TLabel;
    ExtEdit10: TExtEdit;
    Label13: TLabel;
    cmbPoSpecial: TComboBox;
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ExtEdit5KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Pm_Qry_AnalyzerPo6_C: TFrm_Pm_Qry_AnalyzerPo6_C;

implementation
uses Pm_Qry_AnalyzerPo6,Sys_Global;
{$R *.DFM}

procedure TFrm_Pm_Qry_AnalyzerPo6_C.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Pm_Qry_AnalyzerPo6_C:=nil;
end;

procedure TFrm_Pm_Qry_AnalyzerPo6_C.FormShow(Sender: TObject);
begin
  inherited;
  Maskedit1.Text:=Formatdatetime('yyyy.mm.dd',date-15);
  Maskedit2.Text:=Formatdatetime('yyyy.mm.dd',date);
end;

procedure TFrm_Pm_Qry_AnalyzerPo6_C.btn_okClick(Sender: TObject);
var
  StrCondition1,StrCondition2:string;
  StrSQLText1,StrSQLText2,StrSQLText3,linestatus1,linestatus2:string;
     Special:integer;
begin
 inherited;
  case cmbPoSpecial.Itemindex of
    0: Special:=255;
    1: Special:=1;
    2: Special:=0;
  end;
  If (Maskedit1.Text<>'    .  .  ') and (Maskedit2.Text<>'    .  .  ')Then
    StrCondition1:=' And Po.Podate between '+Quotedstr(Maskedit1.Text)+' and '+Quotedstr(Maskedit2.Text+' 23:59:59') ;
  if (Trim(Extedit1.Text)<>'') and (Trim(Extedit2.Text)<>'') then
    StrCondition1:=StrCondition1+' and Po.EmployeeCode between '+ Quotedstr(Extedit1.text)+' and '+ Quotedstr(Extedit2.text);
  if (Trim(Extedit3.Text)<>'') and (Trim(Extedit4.Text)<>'') then
    StrCondition1:=StrCondition1+' and Po.VendorCode between '+ Quotedstr(Extedit3.text)+' and '+ Quotedstr(Extedit4.text);
  if (Trim(Extedit5.Text)<>'') and (Trim(Extedit6.Text)<>'') then
    StrCondition1:=StrCondition1+' and Poline.ItemCode between '+ Quotedstr(Extedit5.text)+' and '+ Quotedstr(Extedit6.text);
  if (Trim(Extedit7.Text)<>'') and (Trim(Extedit8.Text)<>'') then
    StrCondition2:=' and case Totalpoqty when 0 then 0 else  (TotalFinishPoQty*100/TotalPoQty ) end between ' + Extedit7.text +' and '+ Extedit8.text;

  StrSQLText1:='Select Po.EmployeeCode,Po.VendorCode,PoLine.*,'+
               ' (PoLine.Poqty-PoLine.PoNoFinishQty) As PoFinishQty'+
              ' Into #TmpPoLine'+
              ' From PoLine'+
              ' Join Po On PoLine.PoNo=Po.PoNo where Poline.poQty>0 and Poline.PolineStatus between '+Extedit9.Text+' and '+Extedit10.Text+StrCondition1+
              ' and ((po.PoSpecial='+inttostr(Special)+') or ('+inttostr(Special)+'=''255''))'+
              ' Order by Po.EmployeeCode';

  StrSQLText2:='Select EmployeeCode,VendorCode,ItemCode,'+
               ' Sum(PoQty) As TotalPoQty,'+
               ' sum(poinqty) as poinqty, '+
               'Sum(PoFinishQty)   As TotalFinishPoQty,'+
               ' Sum(PoNoFinishQty) As TotalPoNoFinishQty,'+
               ' Sum(PoTaxAmount) As TotalPoTaxAmount,'+
               ' Sum(PoNoTaxAmount) As TotalPoNoTaxAmount'+
               ' Into #TmpTotalPoLine'+
            ' From #TmpPoLine'+
            ' Group By EmployeeCode,VendorCode,ItemCode';

  StrSQLText3:='Select #TmpTotalPoLine.*,Employee.EmployeeName,Vendor.VendorName,Employeeflag=#TmpTotalPoLine.EmployeeCode+'' ''+Employee.EmployeeName,'+
                      ' Vendorflag=#TmpTotalPoLine.VendorCode+'' ''+Vendor.VendorName,'+
                      ' Itemflag=#TmpTotalPoLine.ItemCode+'' ''+Item.ItemName,'+
                      ' Item.ItemName,Item.UomCode,'+
                      'case #tmpTotalPoline.Totalpoqty when 0 then ''0%'' else  Convert(varchAr,round(#TmpTotalPoLine.TotalFinishPoQty*100/#TmpTotalPoLine.TotalPoQty,0) )+''%'' end As FinishRate'+
              ' From #TmpTotalPoLine'+
              ' Join Item On #TmpTotalPoLine.ItemCode=Item.ItemCode'+
              ' Left Outer Join Uom On Item.UomCode=Uom.UomCode'+
              ' Left Outer Join Employee On #TmpTotalPoLine.EmployeeCode='+
              ' Employee.EmployeeCode'+
              ' Left Outer Join Vendor On'+
              ' #TmpTotalPoLine.VendorCode=Vendor.VendorCode where TotalPoQty >0 '+ StrCondition2;

  with  Frm_Pm_Qry_AnalyzerPo6.AdoQry_Main do
  begin
    Close;
    SQL.clear;
    SQL.Add(StrSQLText1);
    Prepared;
    try
    ExecSQL;
    except
    end;

    Close;
    SQL.clear;
    SQL.Add(StrSQLText2);
    Prepared;
    try
    ExecSQL;
    except
    end;

    Close;
    SQL.clear;
    SQL.Add(StrSQLText3);
    Prepared;
    try
    Open;
    except
    end;
  end;

  with Frm_Pm_Qry_AnalyzerPo6.AdoQry_Tmp do
  begin
    Connection:=Frm_Pm_Qry_AnalyzerPo6.AdoQry_Main.Connection;
    Close;
    SQL.clear;
    SQL.Add('drop table #TmpPoLine,#TmpTotalPoLine');
    Prepared;
    try
    ExecSQL;
    except
    end;
  end;
  if Extedit9.Text='5' then
      linestatus1:='5 准备'
   else if  Extedit9.Text='6'  then
      linestatus1:='6 下达'
      else if  Extedit9.Text='7'  then
       linestatus1:='7 关闭';
   if Extedit10.Text='5' then
      linestatus2:='5 准备'
   else if  Extedit10.Text='6'  then
      linestatus2:='6 下达'
      else if  Extedit10.Text='7'  then
       linestatus2:='7 关闭';

 ConditionHint:='下单日期从 '+maskedit1.Text+' 到 '+maskedit2.Text+' / 订单性质： '+cmbPoSpecial.Text+' / 物料代码从 '+Extedit5.Text+' 到 '+Extedit6.Text+' / 业务员代码从 '+Extedit1.Text+' 到 '+Extedit2.Text+#13+'供应商代码从 '+Extedit3.Text+' 到 '+Extedit4.Text+' / 行状态从 '+linestatus1+' 到 '+linestatus2+' / 完成率从 '+Extedit7.Text+'%'+' 到 '+Extedit8.Text+'%';
  self.ModalResult:=mrok;

end;

procedure TFrm_Pm_Qry_AnalyzerPo6_C.FormCreate(Sender: TObject);
begin
  inherited;
     cmbPoSpecial.ItemIndex:=0;

end;

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

end.