pm_enter_purchaseapply_c.pas

一个MRPII系统源代码版本

unit Pm_Enter_PurchaseApply_C;

Interface

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

Type
  TFrm_Pm_Enter_PurchaseApply_C = Class(TFrm_Base_Condition)
    Label1: TLabel;
    Label2: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    Label3: TLabel;
    Label4: TLabel;
    Lbl_ItemName1: TLabel;
    Lbl_ItemName2: TLabel;
    procedure FormCreate(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure Edit1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Edit2KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Pm_Enter_PurchaseApply_C: TFrm_Pm_Enter_PurchaseApply_C;

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

procedure TFrm_Pm_Enter_PurchaseApply_C.FormCreate(Sender: TObject);
begin
  inherited;
  //SetDBConnect(DBConnect);
  //AdoQry_Tmp.Connection:=DBConnect;
  Edit3.Text:=Datetostr(date);
  Edit4.Text:=Datetostr(Date+5);
end;

procedure TFrm_Pm_Enter_PurchaseApply_C.btn_okClick(Sender: TObject);
var
  ItemStr,DateStr:string;
begin
  inherited;
  Frm_Pm_Enter_PurchaseApply.Lbl_Status.Caption:='正在查询数据！';
  Frm_Pm_Enter_PurchaseApply.Lbl_Status.Refresh;
  ItemStr:='';
  DateStr:='';
  if Edit1.Text<>'' then
    Itemstr:=' and ItemCode>='''+Edit1.Text+''' ';
  if Edit2.Text<>'ZZZZZ' then
    ItemStr:=ItemStr+' and ItemCode<='''+Edit2.Text+''' ';
  if Edit3.Text<>'' then
    DateStr:=' and releasedate>='''+Edit3.Text+''' ';
  if Edit4.text<>'' then
    DateStr:=Datestr+' and releasedate<='''+Edit4.Text+''' ';
  with AdoQry_Tmp do
  begin
    Close;
    sql.clear;
                // --取出下达建议
    sql.Text:='select ItemCode,SSReleaseDate=releasedate,ssqty=sum(onhand) into #TmpPurchase'+
              ' from MrpResult'+
              ' where (PmCode=1 or PmCode=2 or PmCode=9) and ordinal=3 ';
    if ItemStr<>'' then
      Sql.Text:=Sql.Text+ItemStr;
    if DateStr<>'' then
      Sql.Text:=Sql.Text+DateStr;
    sql.Text:=Sql.Text+
              ' group by ItemCode,releasedate'+
              ' Order by ItemCode,SSReleaseDate'+#13+
                 //--统计价格表中物料对应供应商数
              ' select #TmpPurchase.ItemCode,#TmpPurchase.SSReleaseDate,MVendor=Count(PcTemp.VendorCode)'+
              ' into #PcMVendor                                                                         '+
              ' from #TmpPurchase                                                                       '+
              ' left join (select Pcline.ItemCode,PcLine.PcStArtDate,PcLine.PcendDate,Pc.VendorCode     '+
              ' from pcline left join Pc on pc.pcno=PcLine.PcNo) PcTemp                                 '+
              ' on #TmpPurchase.ItemCode=PcTemp.ItemCode                                                '+
              ' where #TmpPurchase.SSReleaseDate between PcTemp.PcStArtDate and PcTemp.PcendDate        '+
              ' group by #TmpPurchase.ItemCode,SSReleaseDate                                            '+
              //--供应商物料分配表
              ' select #PcMVendor.ItemCode,#PcMVendor.SSReleaseDate,MVendor=case Count(VendorItemAssign.VendorCode) when 0 then 0'+
              ' when 1 then 0 else 1 end                                                                                         '+
              ' into #MVendor                                                                                                    '+
              ' from #PcMVendor left join VendorItemAssign on #PcMVendor.ItemCode=VendorItemAssign.ItemCode                      '+
              ' where #PcMVendor.MVendor>1 and VendorItemAssign.ItemCode not in(select distinct ItemCode from VendorItemAssign where AssignRate=100 or AssignRate=0)'+
              ' group by #PcMVendor.ItemCode,#PcMVendor.SSReleaseDate                                                            '+
              ' union                                                                                                            '+
              ' select #PcMVendor.ItemCode,#PcMVendor.SSReleaseDate,MVendor=1                                                    '+
              ' from #PcMVendor where ItemCode not in(Select ItemCode from VendorItemAssign) and MVendor>1                       '+
              //--物料供应商组合
              ' select #TmpPurchase.ItemCode,#TmpPurchase.SSReleaseDate,PcTemp0.VendorCode,MVendor=0                             '+
              ' into #MMVendor                                                                                                   '+
              ' from  #TmpPurchase                                                                                               '+
              ' left join (select PcLine.ItemCode,Pcline.PcStArtDate,PcLine.PcendDate,Pc.VendorCode                              '+
              '           From PcLine left join Pc on Pcline.PcNo=Pc.PcNo) PcTemp0                                               '+
              '         on #TmpPurchase.ItemCode=PcTemp0.ItemCode                                                                '+
              ' left join VendorItemAssign on VendorItemAssign.ItemCode=#TmpPurchase.ItemCode and PcTemp0.VendorCode=VendorItemAssign.VendorCode'+
              ' where PcTemp0.ItemCode not in(select ItemCode from #MVendor where MVendor=1)                                     '+
              ' and #TmpPurchase.SSReleaseDate between PcTemp0.PcStArtDate and PcTemp0.PcendDate                                 '+
              ' and (VendorItemAssign.Assignrate=100 or #TmpPurchase.ItemCode not in(select ItemCode from #PcMVendor where MVendor>1))'+
              ' union select ItemCode,SSReleasedate,null,MVendor from #MVendor where MVendor=1'+#13+
                 // --数据结果
              ' select #TmpPurchase.ItemCode,#TmpPurchase.ssqty,PlanQty=#TmpPurchase.ssqty,'+
              'SSReleaseDate=Convert(varchAr,#TmpPurchase.SSReleaseDate,102),PlanReleaseDate=Convert(varchAr,#TmpPurchase.SSReleaseDate,102),IsMultiVendor=isnull(#MMVendor.MVendor,0),'+
              'ISRefill=case Item.ItemType when 7 then 1'+
              ' else 0 end,'+
              'IScreatePo=0,TmpField=0,CreateDate=Convert(varchAr,GetDate(),102),LTDay=Item.PrepareLT+Item.RunLT+Item.QcLT,#MMVendor.VendorCode'+
              ' from #TmpPurchase left join #MMVendor on #TmpPurchase.ItemCode=#MMVendor.ItemCode and #TmpPurchase.SSReleaseDate=#MMVendor.SSReleaseDate'+
              ' left join Item on #TmpPurchase.ItemCode=Item.ItemCode'+
              ' Order by #TmpPurchase.ItemCode,#TmpPurchase.SSReleaseDate'+#13+
              //--删除临时表
              ' drop table #TmpPurchase'+#13+
              ' drop table #MVendor'+#13+
              ' drop table #PcMVendor'+#13+
              ' drop table #MMVendor';
    Prepared;
    open;
  end;
end;

procedure TFrm_Pm_Enter_PurchaseApply_C.Edit1KeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  if(Key=120)then
    CommonHint(Sender,AdoQry_Tmp,'ItemName','物料名称','ItemCode','物料代码','Item',' PmCode=1');
end;

procedure TFrm_Pm_Enter_PurchaseApply_C.Edit2KeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  if(Key=120)then
    CommonHint(Sender,AdoQry_Tmp,'ItemName','物料名称','ItemCode','物料代码','Item',' PmCode=1');
end;

end.