pm_qry_analyzerpo5_c.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerPo5_C;

Interface                

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

Type
  TFrm_Pm_Qry_AnalyzerPo5_C = Class(TFrm_Base_Condition)
    Label3: TLabel;
    Label1: TLabel;
    medts: TMaskEdit;
    Label4: TLabel;
    medte: TMaskEdit;
    edtvCodee: TEdit;
    Label5: TLabel;
    edtvCodes: TEdit;
    Label2: TLabel;
    edtItemCodes: TEdit;
    Label6: TLabel;
    edtItemCodee: TEdit;
    edtFinishratee: TEdit;
    Label8: TLabel;
    edtFinishrates: TEdit;
    Label7: TLabel;
    Label9: TLabel;
    edtlinestatuss: TEdit;
    Label10: TLabel;
    edtlinestatuse: TEdit;
    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_AnalyzerPo5_C: TFrm_Pm_Qry_AnalyzerPo5_C;

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

procedure TFrm_Pm_Qry_AnalyzerPo5_C.FormCreate(Sender: TObject);
begin
  inherited;
     medts.Text:=formatdatetime('yyyy.mm.dd',now-15);
     medte.Text:=formatdatetime('yyyy.mm.dd',now);
     edtvCodes.Text:='0';
     edtItemCodes.Text:='0';
     edtvCodee.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_AnalyzerPo5_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.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.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 ItemCode,'+
                    'VendorCode,'+
                    '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 ItemCode,VendorCode'+
            ' Order By ItemCode,VendorCode';

  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 '+
                    'round((#tmpResult.TotalPoQty-#tmpResult.TotalPONoFinishQty)*100/#tmpResult.TotalPoQty,0) end )+''%'''+
                    'As FinishRate'+
             ' From #TmpResult'+
             ' Join Item On #TmpResult.ItemCode=Item.ItemCode'+
             ' Left Join Vendor On #TmpResult.VendorCode=Vendor.VendorCode'+
             ' 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
                    +' Order by #tmpResult.ItemCode,#tmpResult.VendorCode';
            // ' Where  #TmpResult. TotalFinishQty*100/#TmpResult.TotalPoQty  between '+edtFinishrates.text+' and '+edtFinishratee.text;
     with Frm_Pm_Qry_AnalyzerPo5.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_AnalyzerPo5.AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('drop table #tmppoline,#tmpResult');
      Prepared;
      execsql;
    end;
    
    if  Frm_Pm_Qry_AnalyzerPo5.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 关闭';


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

end;

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