pm_qry_analyzerpo2_c.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerPo2_C;

Interface

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

Type
  TFrm_Pm_Qry_AnalyzerPo2_C = Class(TFrm_Base_Condition)
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    ExtEdit1: TExtEdit;
    ExtEdit2: TExtEdit;
    ExtEdit3: TExtEdit;
    ExtEdit4: TExtEdit;
    ExtEdit5: TExtEdit;
    ExtEdit6: TExtEdit;
    ExtEdit7: TExtEdit;
    ExtEdit8: TExtEdit;
    MaskEdit1: TMaskEdit;
    MaskEdit2: TMaskEdit;
    ExtEdit9: TExtEdit;
    ExtEdit10: TExtEdit;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    cmbPoSpecial: TComboBox;
    procedure FormShow(Sender: TObject);
    procedure FormDestroy(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_AnalyzerPo2_C: TFrm_Pm_Qry_AnalyzerPo2_C;

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

procedure TFrm_Pm_Qry_AnalyzerPo2_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_AnalyzerPo2_C.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Pm_Qry_AnalyzerPo2_C:=nil;
end;

procedure TFrm_Pm_Qry_AnalyzerPo2_C.btn_okClick(Sender: TObject);
var StrCondition,StrCondition1:String;
    StrSQLText,StrSQLText1,StrSQLText2,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
     StrCondition:=StrCondition+' and Po.podate between '+Quotedstr(maskedit1.text)+' and '+ Quotedstr(maskedit2.text+' 23:59:59');
   if (Trim(Extedit1.Text)<>'') and (Trim(Extedit2.Text)<>'') then
     StrCondition:=StrCondition+' and  Po.VendorCode between '+Quotedstr(Extedit1.text)+' and '+ Quotedstr(Extedit2.text);
   if (Trim(Extedit3.Text)<>'') and (Trim(Extedit4.Text)<>'') then
     StrCondition:=StrCondition+' and  PoLine.PoNo between '+Quotedstr(Extedit3.text)+' and '+ Quotedstr(Extedit4.text);
   if (Trim(Extedit5.Text)<>'') and (Trim(Extedit6.Text)<>'') then
     StrCondition:=StrCondition+' and  PoLine.ItemCode between '+Quotedstr(Extedit5.text)+' and '+ Quotedstr(Extedit6.text);
   if (Trim(Extedit9.Text)<>'') and (Trim(Extedit10.Text)<>'') then
     StrCondition:=StrCondition+' and poline.poLineStatus between '+Extedit9.Text +' and '+Extedit10.Text;
   if (Trim(Extedit7.Text)<>'') and (Trim(Extedit8.Text)<>'') then
     StrCondition1:=' and case Totalpoqty when 0 then 0 else (TotalFinishPoQty*100/TotalPoQty ) end between '+Extedit7.text+' and '+ Extedit8.text ;

   StrSQLText:='Select Po.VendorCode,Poline.PoQty-Poline.PoNoFinishQty as PoFinishQty,'+
                    ' PoLine.* Into #TmpPoLine'+
               ' From PoLine'+
                   ' Join Po On PoLine.PoNo=Po.PoNo where poline.PoQty>0 '+ StrCondition+
                   ' and ((po.PoSpecial='+inttostr(Special)+') or ('+inttostr(Special)+'=255))'+
                   ' Order by Po.VendorCode ';
   StrSQLText1:='Select VendorCode,ItemCode,PoNo,'+
                      ' poLineStatus= case PoLineStatus when 5 then ''5 准备''  when 6 then ''6 下达''  when 7 then ''7 关闭''  end,'+
                      ' Sum(PoQty) As TotalPoQty,'+
                      ' sum(isnull(poinqty,0))  as poinqty,'+
                      ' Sum(PoFinishQty) As TotalFinishPoQty,'+
                      ' Sum(PoNoFinishQty) As TotalPoNoFinishQty,'+
                      ' Sum(PoTaxAmount) As TotalPoTaxAmount,'+
                      ' Sum(PoNoTaxAmount) As TotalPoNoTaxAmount'+
               ' into #TmpTotalPoline'+
               ' From #TmpPoLine'+
               ' Group by VendorCode,PoNo,ItemCode,poLineStatus ';

   StrSQLText2:='Select #TmpTotalPoLine.*,Item.ItemName,Item.UomCode,Uom.UomName,Vendor.VendorName,Vendorflag=#tmpTotalpoline.VendorCode+'' ''+Vendor.VendorName,Itemflag=#tmpTotalpoline.ItemCode+'' ''+Item.ItemName,'+
                       ' case Totalpoqty when 0 then ''0%'' else Convert(varchAr,round((TotalFinishPoQty*100/TotalPoQty),0))+''%'' end As FinishRate'+
                ' From #TmpTotalPoLine'+
                       ' Join Item On #TmpTotalPoLine.ItemCode=Item.ItemCode'+
                       ' Left Join Vendor On #TmpTotalPoLine.VendorCode=Vendor.VendorCode'+
                       ' Left Outer Join Uom On Item.UomCode=Uom.UomCode'+
                       ' Where TotalPoqty>0 and 1=1 '+ StrCondition1+
                ' Order by #tmpTotalpoline.VendorCode,#tmpTotalpoline.pono,#tmpTotalpoline.ItemCode';

   with Frm_Pm_Qry_AnalyzerPo2.AdoQry_Main do
   begin
     Close;
     SQL.clear;
     SQL.Add(StrSQLText);
  //   showmessage(strsqltext);
     Prepared;
     try
     ExecSQL;
     except
     end;

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

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

   with Frm_Pm_Qry_AnalyzerPo2.AdoQry_Tmp do
   begin
     Connection:=Frm_Pm_Qry_AnalyzerPo2.AdoQry_Main.Connection;
     Close;
     SQL.clear;
     SQL.Add(' drop table #TmpPoLine,#TmpTotalPoLine ');
     Prepared;
     try
     ExecSQL;
     except
     end;
   end;

  if Frm_Pm_Qry_AnalyzerPo2.AdoQry_Main.RecordCount=0 then
  begin
    DispInfo('没有合符条件的数据！',3);
    maskedit1.setfocus;
    exit;
  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+' / 供应商代码从 '+Extedit1.Text+' 到 '+Extedit2.Text+' / 物料代码从 '+Extedit5.Text+' 到 '+Extedit6.Text+#13+'采购单号从 '+Extedit3.Text+' 到 '+Extedit4.Text+' / 行状态从 '+linestatus1+' 到 '+linestatus2+' / 完成率从 '+Extedit7.Text+'%'+' 到 '+Extedit8.Text+'%';
  self.ModalResult:=mrOk;

end;

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

end;

procedure TFrm_Pm_Qry_AnalyzerPo2_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.