📄 mrp_qry_newoblongmrp_d.pas
字号:
unit Mrp_Qry_NewOblongMrp_D;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBTables, jpeg;
Type
TFrm_Mrp_Qry_NewOblongMrp_D = Class(TFrm_Base_Qry)
lbl_ItemCode: TLabel;
Label1: TLabel;
lbl_ItemName: TLabel;
lbl_PmBatch: TLabel;
Label3: TLabel;
lbl_CurrentInv: TLabel;
Label4: TLabel;
Label5: TLabel;
cmbselectType: TComboBox;
Adodataset_Main: TAdODataSet;
AdOCommand: TAdOCommand;
Label6: TLabel;
lbl_CurrentonCheckInv: TLabel;
Label7: TLabel;
lbl_PmCode: TLabel;
Label9: TLabel;
lbl_minqty: TLabel;
Label11: TLabel;
lbl_Batchstrat: TLabel;
Label8: TLabel;
lbl_Mrprundate: TLabel;
Label10: TLabel;
lbl_Preparelt: TLabel;
Label13: TLabel;
lbl_runlt: TLabel;
lbl_Qclt: TLabel;
Label16: TLabel;
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure FormDestroy(Sender: TObject);
procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
AFont: TFont; var Background: TColor; State: TGridDrawState);
procedure cmbselectTypeChange(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure AdoQry_MainAfterScroll(DataSet: TDataSet);
procedure Act_LookExecute(Sender: TObject);
procedure Act_ShowGridExecute(Sender: TObject);
procedure FormShow(Sender: TObject);
private
function getValue(Code:string;tableName:string):variant;
public
mRunMrp:integer;
ItemCode:string;
procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
{ Public declarations }
end;
var
Frm_Mrp_Qry_NewOblongMrp_D: TFrm_Mrp_Qry_NewOblongMrp_D;
implementation
uses Mrp_Qry_OblongMrp_m,Sys_Global, Mrp_Qry_InformalUpRightMrp_D_NewParentMo,
Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_InformalTotalMrp_Mo,
Mrp_Qry_InformalTotalMrp_vd,Mrp_Global;
{$R *.DFM}
{ TFrm_Mrp_Qry_OblongMrp }
procedure TFrm_Mrp_Qry_NewOblongMrp_D.InitForm(AdOConnection: TAdOConnection;
ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5,sqltext20,sqltext6,sqltext7,sqltext8:string;
i:integer;
sqltext0:string;
success:integer;
tMpsql:string;
begin
inherited;
lbl_Mrprundate.Caption:=getValue('RunMrpdate','MrpParam');
Frm_Mrp_Qry_OblongMrp_m:=TFrm_Mrp_Qry_OblongMrp_m.Create(self);
Adocommand.Connection:=AdoQry_Main.Connection;
// if mRunMrp=1 then
// TemporArilyRunMrp(dbconnect,'select ItemCode from MrpResult where ordinal=1 and ItemCode='+quotedstr(ItemCode),1);
tMpsql:='select * into #tttMrpResult from MrpResult'
+' where ItemCode='+quotedstr(ItemCode);
Executesql(AdoQry_tmp,tMpsql,1);
//--------------------------------------------------------------------\\
Application.ProcessMessages;
{sqltext0:='select ItemCode,PmCode, min(releasedate) as releasedate '
+' into #TmpOblongm1 '
+' from #tttMrpResult '
+' where ordinal=0 '
+' and ItemCode='+quotedstr(ItemCode)
+' group by ItemCode,PmCode '
+' select #TmpOblongm1.ItemCode,#TmpOblongm1.PmCode,min(#tttMrpResult.releasedate) as releasedate'
+' into #TmpOblongm '
+' from #tttMrpResult,#TmpOblongm1 '
+' where #tttMrpResult.ItemCode=#TmpOblongm1.ItemCode and #tttMrpResult.releasedate>=#TmpOblongm1.releasedate'
+' and #tttMrpResult.ordinal=0 '
+' group by #TmpOblongm1.ItemCode,#TmpOblongm1.PmCode'
+' select distinct t1.ItemCode,t1.onhand,Assignedqty=isnull(t1.Assignedqty,0),t1.oncheck,t1.releasedate,t1.PmCode,t1.ordinal '
+' into #tmponhand '
+' from #tttMrpResult t1, #TmpOblongm t2 '
+' where t1.ItemCode=t2.ItemCode '
+' and t1.releasedate=t2.releasedate '
+' and t1.ordinal=0 '; }
sqltext0:=' declAre @VisualManuObject Integer '
+' Select @VisualManuObject=MrpParamValueN '
+' From MrpParam '
+' Where MrpParamCode=''VisualManuObject'''
+' select #tttMrpResult.ItemCode, '
+' case When @VisualManuObject=1 and IsNull(Mps.MpsStatus,100)=3 then 0.0 '
+' else Item.CurrentonhandInv '
+' end as onhand, '
+' releasedate, '
+' #tttMrpResult.PmCode,'
+' ordinal '
+' into #tmponhand '
+' from #tttMrpResult '
+' join Item on #TttMrpResult.ItemCode=Item.ItemCode '
+' left join Mps on Item.ItemCode=Mps.ItemCode '
+' where ordinal=0';
Application.ProcessMessages;
with AdoQry_tmp do
begin
Close;
sql.clear;
sql.Add(sqltext0);
Prepared;
try
execsql;
except
end;
end;
sqltext0:=' declAre @VisualManuObject Integer '
+' Select @VisualManuObject=MrpParamValueN '
+' From MrpParam '
+' Where MrpParamCode=''VisualManuObject'''
+' select #tttMrpResult.ItemCode, '
+' case when @VisualManuObject=1 and IsNUll(Mps.MpsStatus,100)=3 then 0.0 '
+' else Item.CurrentonCheckInv '
+' end as onhand,'
+' releasedate, '
+' #tttMrpResult.PmCode, '
+' ordinal '
+' into #tmponcheck '
+' from #tttMrpResult'
+' join Item on #tttMrpResult.ItemCode=Item.ItemCode '
+' left join Mps on Item.ItemCode=Mps.ItemCode '
+' where ordinal=1';
Executesql(AdoQry_tmp,sqltext0,1);
//----------------------------------------------------------------------------------------------------------------------------\\
sqltext1:='Select distinct #tttMrpResult.ItemCode,sum(isnull(#tttMrpResult.GrossQty,0)) As SSQty,#tttMrpResult.releasedate as SSDate,'+
'Item.ItemName,Itemflag=#tttMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#tttMrpResult.groupordinal,Item.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
'(Case Item.PmCode'+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime '+
' into #TmpOblongMrp1 '+
' From #tttMrpResult(nolock) '+
'left outer Join Item On #tttMrpResult.ItemCode=Item.ItemCode '+
' Where ordinal=4 '+
' and #tttMrpResult.ItemCode='+quotedstr(ItemCode)+
' group by #tttMrpResult.ItemCode,#tttMrpResult.groupordinal,#tttMrpResult.releasedate,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,Item.minqty,Item.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,Item.purchldtime,Item.mnldtime'+
' Order by #tttMrpResult.ItemCode,#tttMrpResult.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
{ sqltext20:='declAre @PmCode tinyint, '
+' @tmpPmCode tinyint, '
+' @ordinal tinyint, '
+' @Count int '
+' select top 0 *,Pmflag=0 into #TmpOblongmdMrpResult1 from #tttMrpResult '}
sqltext20:=' select * into #TmpOblongmdMrpResult1 from #tttMrpResult where ItemCode='+quotedstr(ItemCode);
{ +' select @Count=(select Count(*) from #TmpOblongMrpResult1) '
+' set rowCount 1 '
+' insert into #TmpOblongmdMrpResult1 '
+' select *,Pmflag=PmCode from #TmpOblongMrpResult1 '
+' delete from #TmpOblongMrpResult1 '
+' select @PmCode=PmCode from #TmpOblongmdMrpResult1 '
+' while @Count>=1 '
+' begin '
+' select @ordinal=ordinal from #TmpOblongMrpResult1 '
+' insert into #TmpOblongmdMrpResult1 '
+' select *,Pmflag=case when @ordinal=4 then PmCode else @PmCode end from #TmpOblongMrpResult1 '
+' if @ordinal=4 '
+' select @PmCode=PmCode from #TmpOblongMrpResult1 '
+' delete from #TmpOblongMrpResult1 '
+' select @Count=@Count-1 '
+' end '
+ ' set rowCount 999999999'; }
sqltext2:='Select distinct #TmpOblongmdMrpResult1.ItemCode,#tmpOblongmdMrpResult1.groupordinal,ssqty=case #TmpOblongmdMrpResult1.ordinal when 2 then sum(isnull(#TmpOblongmdMrpResult1.OrderQty,0)) else sum(isnull(#TmpOblongmdMrpResult1.onhand,0)) end ,'
+' #TmpOblongmdMrpResult1.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#TmpOblongmdMrpResult1.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#TmpOblongmdMrpResult1.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt, '+
' (Case #TmpOblongmdMrpResult1.PmCode '+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime, '+
' flag=case isnull(#TmpOblongmdMrpResult1.ordinal,''/\\'') '+
' when 3 then ''s'' '+
' else ''o'' '+
' end '+
// ' PmCode=#TmpOblongmdMrpResult1.PmCode '+
' into #TmpOblongMrp2 '+
' From #TmpOblongmdMrpResult1(nolock) '+
' left outer Join Item On #TmpOblongmdMrpResult1.ItemCode=Item.ItemCode '+
' Where ordinal in(2,3) '+
' group by #TmpOblongmdMrpResult1.ItemCode,#tmpOblongmdMrpResult1.groupordinal,#TmpOblongmdMrpResult1.PmCode,#TmpOblongmdMrpResult1.releasedate,#TmpOblongmdMrpResult1.ordinal,Item.purchldtime,Item.mnldtime,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,'
+' Item.minqty,#TmpOblongmdMrpResult1.PmCode,Item.Batchstrat,'
+' Item.Preparelt,Item.runlt,Item.Qclt'+
' Order by #TmpOblongmdMrpResult1.ItemCode,#TmpOblongmdMrpResult1.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
sqltext3:=' Select distinct #tttMrpResult.ItemCode,#tttMrpResult.groupordinal,#tttMrpResult.onhand AS SSQty,#tttMrpResult.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#tttMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,#tmponhand.onhand as CurrentonhandInv,#tmponcheck.onhand as CurrentonCheckInv,Item.minqty,Item.PmCode,'+
' Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
' (Case Item.PmCode '+
' When 1 Then '+
' Item.PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime '+
' into #TmpOblongMrp3 '+
' From Item(nolock) '+
' Join #tttMrpResult On #tttMrpResult.ItemCode=Item.ItemCode '+
' join #tmponhand on #tttMrpResult.ItemCode=#tmponhand.ItemCode '+
' join #tmponcheck on #tttMrpResult.ItemCode=#tmponcheck.ItemCode '+
' Order by #tttMrpResult.ItemCode,#tttMrpResult.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
sqltext4:='Select Top 0 物料标识=Itemflag,物料代码=ItemCode,物料描述=ItemName,供需项目=''0'',期初=null,批量=PmBatch,提前期=LdTime,当前可用库存=CurrentonhandInv,当前待检库存=CurrentonCheckInv,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt '+
' Into #TmpOblongMrp4 '+
' From #TmpOblongMrp3 '+
' alter table #TmpOblongMrp4 Add stArtqty float '+
' alter table #TmpOblongMrp4 Add SupplyType varchAr(50) ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
sqltext5:= 'select 物料标识,物料代码,物料描述,供需项目=SupplyType,期初=stArtqty,批量,提前期,当前可用库存,当前待检库存,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt'+
' into #TmpOblongMrp '+
' from #TmpOblongMrp4 ';
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
sqltext6:='insert into #TmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''0'',null,PmBatch,ldtime, CurrentonhandInv=0,0,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp1 '+
' insert into #TmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''1'',null,PmBatch,ldtime,CurrentonhandInv=0,0,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp1 '+
' insert into #TmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''12'',null,PmBatch,ldtime,CurrentonhandInv=0,0,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp1 '+
' insert into #TmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''2'',null,PmBatch,ldtime,CurrentonhandInv,CurrentonCheckInv,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp3';
//----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
sqltext7:='declAre @maxdate datetime, '+
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -