mrp_enter_mossparent.pas

一个MRPII系统源代码版本

unit Mrp_Enter_MoSsParent;

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_Enter_MoSsParent = 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_MainMrpqty: TFloatField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainQclt: TIntegerField;
    AdoQry_MainPreparelt: TIntegerField;
    procedure FormDestroy(Sender: TObject);
    procedure Action1Execute(Sender: TObject);
    procedure Action2Execute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    function ifexistsiteItemCode(ItemCode:string;date,Qclt:string):boolean;{ Private declarations }
    function existsfArItemCode(ItemCode:string):boolean;
    function getlt(ItemCode:string;ltType:integer):integer;
  public
    Qclt:string;
    ItemCode:String;
    duedate:string;
    strpath:string;
    { Public declarations }
  end;
// procedure MoSsInitForm(AdOConnection:TAdOConnection;ItemCode,SsDate:String);
var
  Frm_Mrp_Enter_MoSsParent: TFrm_Mrp_Enter_MoSsParent;
   tmplowPmCode:integer; lowBomqty:real; lowScrAp: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_Enter_MoSsParent.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
begin
  inherited;
 if  ifexistsiteItemCode(ItemCode,duedate,Qclt)=False then
   begin
    DispInfo('无当前物料的父项需求相关数据！',3);
    Close;
   end
  else
   Executesql(AdoQry_Main,'select * from #tmpuPMrpResult where Mrpqty>0',0);
 end;

procedure TFrm_Mrp_Enter_MoSsParent.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Enter_MoSsParent:=Nil;
end;

procedure TFrm_Mrp_Enter_MoSsParent.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 MrpResult.*,Bom.Bomqty,Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
               +'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
               +'  Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'  Item.Qclt, '
               +'  Item.Preparelt,'
               +'   Uom.UomName                           '
               +'  into #tmpuPMrpResult '
               +'   from MrpResult               '
               +' join Item on MrpResult.ItemCode=Item.ItemCode  '
               +' left outer join Uom on Item.UomCode=Uom.UomCode'
               +' join Bom on MrpResult.ItemCode=Bom.ite_ItemCode';
 Executesql(AdoQry_tmp,tMpsqltext,1);
 tMpsqltext:=' select ItemCode,Item.Qclt from Item where ItemCode='+quotedstr(copy(strpath,1,pos(',',strpath)-1));
 Executesql(AdoQry_tmp,tMpsqltext,0);
 with AdoQry_tmp do
       begin
        if tmplowPmCode=0 then
         tMpsqltext:='insert into #tmpuPMrpResult'
                    +' select distinct MrpResult.*,'
                    +floattostr(lowBomqty)+','
                    +' Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
                    +quotedstr(lowScrAp)+','
                    //+'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
                    +'  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 Bom on MrpResult.ItemCode=Bom.ItemCode'
                    +' where (ordinal=2 or ordinal=5) '
                    +' and MrpResult.ItemCode= '+quotedstr(copy(strpath,1,pos(',',strpath)-1))
                    +' and MrpResult.releasedate='+quotedstr(slCalendar(dbconnect,AdoQry_Main.fieldbyname('releasedate').asstring,iifinteger(AdoQry_Main.fieldbyname('PmCode').asinteger=0,fieldbyname('Qclt').asinteger+AdoQry_Main.fieldbyname('Preparelt').asinteger+getlt(AdoQry_Main.fieldbyname('ItemCode').asstring,3),fieldbyname('Qclt').asinteger+getlt(AdoQry_Main.fieldbyname('ItemCode').asstring,4))))
         else
                  tMpsqltext:='insert into #tmpuPMrpResult'
                    +' select distinct MrpResult.*,'
                    +floattostr(lowBomqty)+','
                    +' Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
                    +quotedstr(lowScrAp)+','
                    //+'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
                    +'  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 Bom on MrpResult.ItemCode=Bom.ItemCode'
                    +' where (ordinal=2 or ordinal=5)  '
                    +' and MrpResult.ItemCode= '+quotedstr(copy(strpath,1,pos(',',strpath)-1))