pm_qry_analyzerpo3_c.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerPo3_C;

Interface

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

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

var
  Frm_Pm_Qry_AnalyzerPo3_C: TFrm_Pm_Qry_AnalyzerPo3_C;

implementation

uses Pm_Qry_AnalyzerPo3,Sys_Global;

{$R *.DFM}

procedure TFrm_Pm_Qry_AnalyzerPo3_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;
// formatdatetime('yyyy.mm.dd',strtodate(medte.text+' 00:00:00'));

  Sqltext1:='Select Po.VendorCode,PoLine.*,'+
            '(PoLine.PoQty-PoLine.PONoFinishQty) As PoFinishQty'+
            ' Into #TmpPoLine '+
            ' From PoLine '+
            ' Join Po On PoLine.PoNo=Po.PoNo '+
            ' Where PoLine.PoNo between '''+edtponos.text+''''+' and '''+edtponoe.Text+''''+
            ' and poline.ItemCode  between  '''+edtItemCodes.text+''''+'  and  '''+edtItemCodee.Text+''''+
            ' and ((po.PoSpecial='+inttostr(Special)+') or ('+inttostr(Special)+'=''255''))'+
            ' and  po.podate  between '''+medts.text+''''+'  and  '''+medte.Text+' 23:59:59'+''''+
            ' and  poline.polinestatus between '+edtlinestatuss.Text+ ' and '+edtlinestatuse.Text+
            ' and  po.VendorCode  between '''+edtvCodes.text+''''+'  and  '''+edtvCodee.Text+'''';

  SqlText2:='Select VendorCode,'+
                    '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 VendorCode,ItemCode'+
            ' Order By VendorCode,ItemCode';

  SqlText3:='Select #TmpResult.*,'+
                    'Item.ItemName,'+
                    'Item.UomCode,'+
                    'Uom.UomName,'+
                    'Vendor.VendorName,'+
                    'Vendorflag=#tmpResult.VendorCode+'' ''+Vendor.VendorName,'+
                    'Itemflag=#tmpResult.ItemCode+'' ''+Item.ItemName,'+
                    'Convert(varChAr,Case #tmpResult.TotalPoQty when 0 then 0 '+
                    'else '+
                    'convert(int,(#tmpResult.TotalPoQty-#tmpResult.TotalPONoFinishQty)*100/#tmpResult.TotalPoQty) end )+''%'''+
                    'As FinishRate'+
             ' From #TmpResult'+
             ' left outer Join Item On #TmpResult.ItemCode=Item.ItemCode'+
             ' Left outer Join Vendor On #TmpResult.VendorCode=Vendor.VendorCode'+
             ' Left Outer Join Uom On Item.UomCode=Uom.UomCode '+
             'where Convert(int,Case #tmpResult.TotalPoQty when 0 then 0 '+
                    'else '+
                    '(#tmpResult.TotalPoQty-#tmpResult.TotalPONoFinishQty)*100/#tmpResult.TotalPoQty end )  between '+edtFinishrates.text+' and '+edtFinishratee.text
             +' Order by #tmpResult.VendorCode,#tmpResult.ItemCode';
            // ' Where  #TmpResult. TotalFinishQty*100/#TmpResult.TotalPoQty  between '+edtFinishrates.text+' and '+edtFinishratee.text;
 try
     with Frm_Pm_Qry_AnalyzerPo3.AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(SqlText1);
      Prepared;
      try
      execsql;
      except
      end;

      Close;
      sql.clear;
      sql.Add(Sqltext2);
      Prepared;
      try
      execsql;
      except
      end;
      
      Close;
      sql.clear;
      sql.Add(SqlText3);
      Prepared;
      try
      open;
      except
      end;

    end
  finally
  with Frm_Pm_Qry_AnalyzerPo3.AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('drop table #tmppoline,#tmpResult');
      Prepared;
      try
      execsql;
      except
      end;
    end;
  end;
    if  Frm_Pm_Qry_AnalyzerPo3.AdoQry_Main.RecordCount=0 then
        begin
          DispInfo('无符合条件的数据！',3);
          edtvCodes.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 关闭';

  // end;
   ConditionHint:='下单日期从 '+medts.Text+' 到 '+medte.Text+' / 供应商代码从 '+EdtvCodes.Text+' 到 '+EdtvCodee.Text+' / 物料代码从 '+EdtItemCodes.Text+' 到 '+EdtItemCodee.Text+#13+'订单性质： '+cmbPoSpecial.Text+' / 采购单号从 '+Edtponos.Text+' 到 '+Edtponoe.Text+' / 行状态从 '+linestatus1+' 到 '+linestatus2+' / 完成率从 '+EdtFinishrates.Text+'%'+' 到 '+EdtFinishratee.Text+'%';
   self.ModalResult:=mrOk;
end;



procedure TFrm_Pm_Qry_AnalyzerPo3_C.FormCreate(Sender: TObject);
begin
  inherited;
     medts.Text:=formatdatetime('yyyy.mm.dd',now-15);
     medte.Text:=formatdatetime('yyyy.mm.dd',now);
     edtvCodes.Text:='0';
     edtponos.Text:='0';
     edtItemCodes.Text:='0';
     edtvCodee.Text:='ZZZZZ';
     edtponoe.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_AnalyzerPo3_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.