pm_qry_analyzerpo7_c.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerPo7_C;

Interface

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

Type
  TFrm_Pm_Qry_AnalyzerPo7_C = Class(TFrm_Base_Condition)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    edteCodes: TEdit;
    medts: TMaskEdit;
    medte: TMaskEdit;
    edteCodee: TEdit;
    edtItemCodes: TEdit;
    edtItemCodee: TEdit;
    edtFinishrates: TEdit;
    edtFinishratee: TEdit;
    edtlinestatuss: TEdit;
    Label9: TLabel;
    edtlinestatuse: TEdit;
    Label10: TLabel;
    Label13: TLabel;
    cmbPoSpecial: TComboBox;
    procedure FormCreate(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure edtItemCodesKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Pm_Qry_AnalyzerPo7_C: TFrm_Pm_Qry_AnalyzerPo7_C;

implementation

uses Pm_Qry_AnalyzerPo7,Sys_Global;

{$R *.DFM}

procedure TFrm_Pm_Qry_AnalyzerPo7_C.FormCreate(Sender: TObject);
begin
  inherited;
     medts.Text:=formatdatetime('yyyy.mm.dd',now-15);
     medte.Text:=formatdatetime('yyyy.mm.dd',now);
     edteCodes.Text:='0';
     edtItemCodes.Text:='0';
     edteCodee.Text:='ZZZZZ';
     edtItemCodee.Text:='ZZZZZ';
     edtFinishrates.Text:='0';
     edtFinishratee.Text:='100';
     edtlinestatuss.Text:='6';
     edtlinestatuse.Text:='6';
     cmbPoSpecial.ItemIndex:=0;

end;

procedure TFrm_Pm_Qry_AnalyzerPo7_C.btn_okClick(Sender: TObject);
 var SqlText1,SqlText2,SqlText3,linestatus1,linestatus2:string;
     Special:integer;
begin
 inherited;
  case cmbPoSpecial.Itemindex of
    0: Special:=255;
    1: Special:=1;
    2: Special:=0;
  end;
  Sqltext1:='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.ItemCode  between  '''+edtItemCodes.text+''''+'  and  '''+edtItemCodee.Text+''''+
            ' and  po.podate  between '''+medts.text+''''+'  and  '''+medte.Text+' 23:59:59'+''''+
            ' and ((po.PoSpecial='+inttostr(Special)+') or ('+inttostr(Special)+'=''255''))'+
            ' and  poline.polinestatus between '+edtlinestatuss.Text+ ' and '+edtlinestatuse.Text+
            ' and  po.EmployeeCode  between '''+edteCodes.text+''''+'  and  '''+edteCodee.Text+'''';

  SqlText2:='Select EmployeeCode,'+
                    'ItemCode,'+
                    'Sum(PoQty)  As TotalPoQty,'+
                    'sum(poinqty) as poinqty, '+
                    'Sum(PoFinishQty)   As TotalFinishQty,'+
                    'Sum(PoNoFinishQty) As TotalPoNoFinishQty,'+
                    'Sum(PoTaxAmount) As TotalPoTaxACount,'+
                    'Sum(PoNoTaxAmount) As TotalPoNoTaxACount '+
             'into  #tmpResult'+
            ' From #TmpPoLine '+
            ' Group By EmployeeCode,ItemCode'+
            ' Order By EmployeeCode,ItemCode';

  SqlText3:='Select #TmpResult.*,'+
                    'Item.ItemName,'+
                    'Item.UomCode,'+
                    'Uom.UomName,'+
                    'Employee.EmployeeName,'+
                    'Employeeflag=#tmpResult.EmployeeCode+'' ''+Employee.EmployeeName,'+
                    'Itemflag=#tmpResult.ItemCode+'' ''+Item.ItemName,'+
                    'Convert(varChAr,Case #tmpResult.TotalPoQty when 0 then 0 '+
                    'else '+
                    'round((#tmpResult.TotalPoQty-#tmpResult.TotalPONoFinishQty)*100/#tmpResult.TotalPoQty,0) end )+''%'''+
                    'As FinishRate'+
             ' From #TmpResult'+
             ' Join Item On #TmpResult.ItemCode=Item.ItemCode'+
             ' Left Join Employee On #TmpResult.EmployeeCode=Employee.EmployeeCode'+
             ' Left Outer Join Uom On Item.UomCode=Uom.UomCode '+
             'where Convert(float,Case #tmpResult.TotalPoQty when 0 then 0 '+
                    'else '+
                    'round((#tmpResult.TotalPoQty-#tmpResult.TotalPONoFinishQty)*100/#tmpResult.TotalPoQty,0) end )  between '+edtFinishrates.text+' and '+edtFinishratee.text;
            // ' Where  #TmpResult. TotalFinishQty*100/#TmpResult.TotalPoQty  between '+edtFinishrates.text+' and '+edtFinishratee.text;
     with Frm_Pm_Qry_AnalyzerPo7.AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(SqlText1);
      Prepared;
      execsql;

      Close;
      sql.clear;
      sql.Add(Sqltext2);
      Prepared;
      execsql;

      Close;
      sql.clear;
      sql.Add(SqlText3);
      Prepared;
      open;

    end;
  with Frm_Pm_Qry_AnalyzerPo7.AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('drop table #tmppoline,#tmpResult');
      Prepared;
      try
      execsql;
      except
      end;
    end;

    if  Frm_Pm_Qry_AnalyzerPo7.AdoQry_Main.RecordCount=0 then
        begin
          DispInfo('无符合条件的数据！',3);
          edteCodes.setfocus;
          exit;
        end;
   if edtlinestatuss.Text='5' then
      linestatus1:='5 准备'
   else if  edtlinestatuss.Text='6'  then
      linestatus1:='6 下达'
      else if  edtlinestatuss.Text='7'  then
       linestatus1:='7 关闭';
   if edtlinestatuse.Text='5' then
      linestatus2:='5 准备'
   else if  edtlinestatuse.Text='6'  then
      linestatus2:='6 下达'
      else if  edtlinestatuse.Text='7'  then
       linestatus2:='7 关闭';


   ConditionHint:='下单日期从 '+medts.Text+' 到 '+medte.Text+' / 订单性质： '+cmbPoSpecial.Text+' / 采购员代码从 '+EdteCodes.Text+' 到 '+EdteCodee.Text+#13+'行状态从 '+linestatus1+' 到 '+linestatus2+' / 物料代码从 '+EdtItemCodes.Text+' 到 '+EdtItemCodee.Text+' / 完成率从 '+EdtFinishrates.Text+'%'+' 到 '+EdtFinishratee.Text+'%';
   self.ModalResult:=mrOk;

end;

procedure TFrm_Pm_Qry_AnalyzerPo7_C.edtItemCodesKeyDown(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.