mrp_qry_informaluprightmrp_d_newparentmo.pas

一个MRPII系统源代码版本

unit Mrp_Qry_InformalUpRightMrp_D_NewParentMo;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin;

Type
  TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainDueDate: TDateTimeField;
    AdoQry_MainReleaseDate: TDateTimeField;
    AdoQry_MaInOrderNo: TStringField;
    AdoQry_MainGrossQty: TFloatField;
    AdoQry_MaInOrderQty: TFloatField;
    AdoQry_MainOnHand: TFloatField;
    AdoQry_MainCanUseOnHand: TFloatField;
    AdoQry_MainNetQty: TFloatField;
    AdoQry_MainOrdinal: TIntegerField;
    AdoQry_MainPmCode: TIntegerField;
    AdoQry_MaInOrderLineStatus: TIntegerField;
    AdoQry_MaInOrderLineno: TIntegerField;
    AdoQry_Mainpegging: TStringField;
    AdoQry_Mainoncheck: TFloatField;
    AdoQry_MainBomqty: TFloatField;
    AdoQry_MainScrAprate: TStringField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainQclt: TIntegerField;
    AdoQry_MainPreparelt: TIntegerField;
    AdoQry_MainInformalMrpqty: TFloatField;
    Panel1: TPanel;
    DBGridEh1: TDBGridEh;
    AdoQuery1: TAdoQuery;
    StringField1: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainItemType: TIntegerField;
    IntegerField1: TIntegerField;
    AdoQry_MainBatchStrat: TIntegerField;
    AdoQry_MainMaxQty: TFloatField;
    AdoQry_MainMinQty: TFloatField;
    AdoQry_MainPurchLDTime: TFloatField;
    StringField2: TStringField;
    AdoQry_MainMNLDTime: TFloatField;
    StringField3: TStringField;
    AdoQry_MainPmBatch: TIntegerField;
    AdoQuery1PrepareLT: TIntegerField;
    AdoQuery1RunLT: TIntegerField;
    AdoQuery1QcLT: TIntegerField;
    DataSource1: TDataSource;
    Label1: TLabel;
    Label3: TLabel;
    Label2: TLabel;
    Label4: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label31: TLabel;
    Label32: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label29: TLabel;
    Label30: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label27: TLabel;
    Label28: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label17: TLabel;
    Label18: TLabel;
    Bevel1: TBevel;
    AdoQry_MainnoFinishqty: TFloatField;
    Label19: TLabel;
    procedure FormDestroy(Sender: TObject);
    procedure Action1Execute(Sender: TObject);
    procedure Action2Execute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    function ifexistsiteItemCode(ItemCode:string;date,Qclt:string):boolean;{ Private declarations }
    function existsfArItemCode(ItemCode:string):boolean;
    function getlt(ItemCode:string;ltType:integer):integer;
  public
    initType:integer;
    TotalType:string;
    Qclt:string;
    ItemCode:String;
    duedate:string;
    strpath:string;
    peggingType:integer;
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    procedure setItemInfo(ItemCode:string);
    { Public declarations }
  end;
// procedure MoSsInitForm(AdOConnection:TAdOConnection;ItemCode,SsDate:String);
var
  Frm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo: TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo;
   tmplowPmCode1:integer; tmplowBomqty:real;tmplowScrAp:string;
implementation

uses Sys_Global;

{$R *.DFM}

{procedure MoSsInitForm(AdOConnection: TAdOConnection;
  ItemCode: String;SsDate:String);
var
  AdoQry_Tmp:TAdoQuery;
  SqlText,Tmp_ItemCode:String;
  I:Integer;
  Frm_Mrp_Enter_MoSsParent: TFrm_Mrp_Enter_MoSsParent;
begin
  AdoQry_Tmp:=TAdoQuery.Create(Nil);
  AdoQry_Tmp.Connection:=AdOConnection;
  SqlText:=' Create Table #TmPmdlOpenBom(ItemCode varChAr(16),MNLDTime Float) '
          +' Create Table #TmpOpenBom(ItemCode varChAr(16),MNLDTime Float) '
          +' Create Table #TmpComponent(ItemCode varchAr(16),MNLDTime Float) '
          +' Insert #TmpComponent(ItemCode,MNLDTime)Values('''+ItemCode+''',0) '
          +' While Exists (Select * From #TmpComponent) '
          +' begin '
          +' Delete From #TmPmdlOpenBom '
          +' Insert #TmPmdlOpenBom(ItemCode,MNLDTime) '
          +'                    Select Ite_ItemCode As ItemCode,Case Item.PmCode '
          +'                                            When 1 then #TmpComponent.MNLDTime+Item.PurchLDTime '
          +'                                            When 2 then #TmpComponent.MNLDTime+Item.PurchLDTime  '
          +'                                            Else #TmpComponent.MNLDTime+Item.MNLDTime '
          +'                                          end As MNLDTime '
          +'                           From Bom '
          +'                               Join Item On Bom.Ite_ItemCode=Item.ItemCode '
          +'                               Join #TmpComponent On Bom.ItemCode=#TmpComponent.ItemCode '
          +' Delete From #TmpComponent '
          +' Insert #TmpComponent '
          +'        Select * From #TmPmdlOpenBom '
          +' Insert #TmpOpenBom '
          +'        Select * From #TmPmdlOpenBom '
          +' end '

          +' Drop Table #TmpComponent '
          +' Drop Table #TmPmdlOpenBom  '
          +' select * From #TmpOpenBom  '
          +' Drop Table #TmpOpenBom ';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.Sql.Text:=SqlText;
  AdoQry_Tmp.Open;
  Tmp_ItemCode:='';
  I:=0;
  While NOT AdoQry_Tmp.Eof Do
  begin
    If I=0 Then
    begin
      Tmp_ItemCode:='(Mps.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' Mps.MpsDate='''+SlCalendar(AdoConnection,SsDate,-AdoQry_Tmp.fieldbyname('MNLDTime').AsInteger)+''')';
    end
    Else
      Tmp_ItemCode:=Tmp_ItemCode+' Or (Mps.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' Mps.MpsDate='''+SlCalendar(AdoConnection,SsDate,-AdoQry_Tmp.fieldbyname('MNLDTime').AsInteger)+''')';
    I:=I+1;
    AdoQry_Tmp.Next;
  end;
  Frm_Mrp_Enter_MoSsParent:=TFrm_Mrp_Enter_MoSsParent.Create(Application);
  Frm_Mrp_Enter_MoSsParent.ItemCode:=Tmp_ItemCode;
  Frm_Mrp_Enter_MoSsParent.InitForm(AdoConnection,False);
end; }

{ TFrm_Mrp_Enter_MoSsParent }

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
var sqltext:string;
begin
Application.ProcessMessages;
  inherited;
AdoQuery1.Connection:=dbconnect;
  if initType=1 then
 begin
  if TotalType='MrpTotal' then
   begin
    Executesql(AdoQry_tmp,'select * into #tInformalMrpResult from MrpResult',1);
    Bomopen(dbconnect,ItemCode);
    SelectFromSql:=' select distinct MrpResult.*,#tmpopenBom.Bomqty,InformalMrpqty=case ordinal when 2 then realOrderqty when 3 then onhand end,  '
               +'  noFinishqty= case ordinal when 2 then Orderqty when 3 then null end,  '
               +'  ScrAprate=convert(varchAr,#tmpopenBom.BomScrAprate)+''%'','
               +'  Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'  Item.Qclt,'
               +'  Item.Preparelt,'
               +'   Uom.UomName                           '
               +'   from MrpResult               '
               +' join Item on MrpResult.ItemCode=Item.ItemCode  '
               +' left outer join Uom on Item.UomCode=Uom.UomCode'
               +' join #tmpopenBom on MrpResult.ItemCode=#tmpopenBom.ItemCode ';
              // +' join Bom on MrpResult.ItemCode=Bom.ite_ItemCode';
   Condition:=iifstring(peggingType=1,' ordinal=3 ',' (ordinal=2 or ordinal=3) ');
   OrderByFields:='ItemCode,dueDate ';
   GetData;
      sqltext:='select Item.*,Uom.UomName,Itemflag=ItemCode+'' ''+ItemName'+
               '   from Item,Uom '+
               ' where Item.UomCode*=Uom.UomCode '+
               '   and Item.ItemCode in (select ItemCode  '+
               '                           from #tmpopenBom '+
               '                         where  exists(select * from MrpResult where #tmpopenBom.ItemCode=MrpResult.ItemCode))';
   Executesql(AdoQuery1,sqltext,0);
   Executesql(AdoQry_tmp,'drop table #tmpopenBom',1);
   setItemInfo(ItemCode);
   end;
  if TotalType='InformalMrpTotal' then
   begin
    Executesql(AdoQry_tmp,'select * into #tInformalMrpResult from InformalMrpResult',1);
    Bomopen(dbconnect,ItemCode);
    SelectFromSql:=' select distinct InformalMrpResult.*,#tmpopenBom.Bomqty,InformalMrpqty=case ordinal when 2 then realOrderqty when 3 then onhand end,  '
               +'  noFinishqty= case ordinal when 2 then Orderqty when 3 then null end,  '
               +'  ScrAprate=convert(varchAr,#tmpopenBom.BomScrAprate)+''%'','
               +'  Itemflag=InformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'  Item.Qclt,'
               +'  Item.Preparelt,'
               +'   Uom.UomName                           '
               +'   from InformalMrpResult               '
               +' join Item on InformalMrpResult.ItemCode=Item.ItemCode  '
               +' left outer join Uom on Item.UomCode=Uom.UomCode'
               +' join #tmpopenBom on InformalMrpResult.ItemCode=#tmpopenBom.ItemCode ';
              // +' join Bom on InformalMrpResult.ItemCode=Bom.ite_ItemCode';
   Condition:=iifstring(peggingType=1,' ordinal=3 ',' (ordinal=2 or ordinal=3) ');
   OrderByFields:='ItemCode,dueDate ';
   GetData;
      sqltext:='select Item.*,Uom.UomName,Itemflag=ItemCode+'' ''+ItemName'+
               '   from Item,Uom '+
               ' where Item.UomCode*=Uom.UomCode '+
               '   and Item.ItemCode in (select ItemCode  '+
               '                           from #tmpopenBom '+
               '                         where  exists(select * from InformalMrpResult where #tmpopenBom.ItemCode=InformalMrpResult.ItemCode))';
   Executesql(AdoQuery1,sqltext,0);
   Executesql(AdoQry_tmp,'drop table #tmpopenBom',1);
   setItemInfo(ItemCode);
   end;
  if  AdoQry_Main.RecordCount=0 then
   begin
    DispInfo('无当前物料的父项需求相关数据！',3);
    Close;
  end

 end
else
 begin
     Executesql(AdoQry_tmp,'select * into #tInformalMrpResult from InformalMrpResult',1);
 if  ifexistsiteItemCode(ItemCode,duedate,Qclt)=False then
   begin
    DispInfo('无当前物料的父项需求相关数据！',3);
    Close;
  end
  else
   begin
   Executesql(AdoQry_Main,'select * from #tmpuPMrpResult where InformalMrpqty>0',0);
      sqltext:='select Item.*,Uom.UomName,Itemflag=ItemCode+'' ''+ItemName'+
               '   from Item,Uom '+
               ' where Item.UomCode*=Uom.UomCode '+
               '   and Item.ItemCode in (select ItemCode  '+
               '                           from #tmpuPMrpResult '+
               '                         where  InformalMrpqty>0)';
   Executesql(AdoQuery1,sqltext,0);
   setItemInfo(ItemCode);
   end;
  end;
end;

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo:=Nil;
end;

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.Action1Execute(Sender: TObject);
var tMpsqltext:string;
    AdoQry:TAdoQuery;
begin
  inherited;
  if strpath=ItemCode+',' then
   begin
    DispInfo('不能再展开下级！',3);
    exit;
   end;
   AdoQry:=TAdoQuery.Create(self);
   AdoQry.EnableBCD:=False;
   AdoQry.Connection:=dbconnect;
  try
    try
     Executesql(AdoQry_tmp,'drop table #tmpuPMrpResult',1);
    except

    end;

 tMpsqltext:=' select top 0 #tInformalMrpResult.*,Bom.Bomqty,InformalMrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
               +'  noFinishqty=convert(float,0),          '
               +'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
               +'  Itemflag=#tInformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'  Item.Qclt, '
               +'  Item.Preparelt,'
               +'   Uom.UomName                           '
               +'  into #tmpuPMrpResult '
               +'   from #tInformalMrpResult               '
               +' join Item on #tInformalMrpResult.ItemCode=Item.ItemCode  '
               +' left outer join Uom on Item.UomCode=Uom.UomCode'