📄 mrp_qry_informaloblongmrp1.pas
字号:
' select @selectType='+inttostr(cmbselectType.Itemindex)+
' select @maxdate=(select max(releasedate) from InformalMrpResult where ItemCode in(select ItemCode from #ttmpItems)) '+
' select @mindate=(select min(releasedate) from InformalMrpResult where ItemCode in(select ItemCode from #ttmpItems)) '+
' while @mindate<=@maxdate '+
' begin '+
' if datepArt(mm,@mindate)<10 '+
' select @mm=''0''+convert(varchAr,datepArt(mm,@mindate)) '+
' else select @mm=convert(varchAr,datepArt(mm,@mindate)) '+
' if datepArt(dd,@mindate)<10 '+
' select @day=''0''+convert(varchAr,datepArt(dd,@mindate)) '+
' else select @day=convert(varchAr,datepArt(dd,@mindate)) '+
' select @Monthbegin=convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01'' '+
' select @outputweek=datepArt(wk,@mindate)-datepArt(wk,@Monthbegin)+1 '+
' if @selectType=0 '+
' begin '+
' select @s=''[''+substring(convert(varchAr,datepArt(yy,@mindate)),3,2)+@mm+@day+'']'' '+
' select @update=@s '+
' select @s=''alter table #tmPMrp Add ''+@s+'' float'' '+
' exec(@s) '+
' end '+
' else '+
' if @selectType=1 '+
' begin '+
' select @s=''[''+convert(varchAr,datepArt(yy,@mindate))+''年''+@mm+''月第''+convert(varchAr,@outputweek)+''周]'''+
' if (convert(varchAr,datepArt(yy,@mindate))+@mm+convert(varchAr,@outputweek))<>@sameweek '+
' begin '+
' select @update=@s '+
' select @s=''alter table #tmPMrp Add ''+@s+'' float'' '+
' exec(@s) '+
' end '+
' select @sameweek=convert(varchAr,datepArt(yy,@mindate))+@mm+convert(varchAr,@outputweek)'+
' end '+
' else if @selectType=2 '+
' begin '+
' select @s=''[''+convert(varchAr,datepArt(yy,@mindate))+''年''+@mm+''月]'''+
' if (convert(varchAr,datepArt(yy,@mindate))+convert(varchAr,datepArt(mm,@mindate)))<>@sameMonth '+
' begin '+
' select @update=@s '+
' select @s=''alter table #tmPMrp Add ''+@s+'' float'' '+
' exec(@s) '+
' end '+
' select @sameMonth=convert(varchAr,datepArt(yy,@mindate))+convert(varchAr,datepArt(mm,@mindate))'+
' end '+
' if @selectType=0 '+
' begin '+
' select @update1=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp1 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+''''''''+''and #tmPMrp.物料代码=#tmPMrp1.ItemCode and #tmPMrp.PmCode=#tmPMrp1.PmCode''+'')'' '+
' select @update1=@update1+'' where 供需项目=0'' '+
' select @update2=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp2 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+'''''''' '+
' select @update2=@update2+''and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag and #tmPMrp2.flag=''''s'''''' '+
' select @update2=@update2+'')'''+
' select @update2=@update2+'' where 供需项目=1'' '+
' select @update22=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp2 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+'''''''' '+
' select @update22=@update22+''and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag and #tmPMrp2.flag=''''o''''''+'')'' '+
' select @update22=@update22+'' where 供需项目=12'' '+
' select @update3=''update #tmPMrp set ''+@update+''=(select sum(isnull(#tmPMrp2.ssqty,0)) from #tmPMrp2 where #tmPMrp2.ssdate<='' '+
' select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
' select @update3=@update3+'' and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag)'' '+
' select @update3=@update3+''-(select sum(isnull(#tmPMrp1.ssqty,0)) from #tmPMrp1 where #tmPMrp1.ssdate<='' '+
' select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
' select @update3=@update3+'' and #tmPMrp.物料代码=#tmPMrp1.ItemCode and #tmPMrp.PmCode=#tmPMrp1.PmCode)+#tmPMrp.当前可用库存+#tmPMrp.订单已领量 where #tmPMrp.供需项目=2'' '+
' select @update4=''update #tmPMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''+
' end else '+
' if @selectType=1 '+
' begin '+
#13+' select @update1=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp1 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
#13+' select @update1=@update1+'' and ''+convert(varchAr,datepArt(wk,@mindate)-datepArt(wk,convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01'')) '+
#13+' select @update1=@update1+''=datepArt(wk,ssdate)-datepArt(wk,convert(chAr(4),datepArt(yy,ssdate))+''''.''''+convert(chAr(2),datepArt(mm,ssdate))+''''.01'''')'' '+
#13+' select @update1=@update1+''and #tmPMrp.物料代码=#tmPMrp1.ItemCode and #tmPMrp.PmCode=#tmPMrp1.PmCode''+'')'''+
#13+' select @update1=@update1+'' where 供需项目=0'' '+
#13+' select @update2=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
#13+' select @update2=@update2+'' and ''+convert(varchAr,datepArt(wk,@mindate)-datepArt(wk,convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01'')) '+
#13+' select @update2=@update2+''=datepArt(wk,ssdate)-datepArt(wk,convert(chAr(4),datepArt(yy,ssdate))+''''.''''+convert(chAr(2),datepArt(mm,ssdate))+''''.01'''')'' '+
#13+' select @update2=@update2+''and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag and #tmPMrp2.flag=''''s''''''+'')''+'' where 供需项目=1'' '+
#13+' select @update22=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
#13+' select @update22=@update22+'' and ''+convert(varchAr,datepArt(wk,@mindate)-datepArt(wk,convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01'')) '+
#13+' select @update22=@update22+''=datepArt(wk,ssdate)-datepArt(wk,convert(chAr(4),datepArt(yy,ssdate))+''''.''''+convert(chAr(2),datepArt(mm,ssdate))+''''.01'''')'' '+
#13+' select @update22=@update22+''and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag and #tmPMrp2.flag=''''o''''''+'')''+'' where 供需项目=12'' '+
' select @update3=''update #tmPMrp set ''+@update+''=(select sum(isnull(#tmPMrp2.ssqty,0)) from #tmPMrp2 where datepArt(mm,ssdate)<=''+convert(varchAr,datepArt(mm,@mindate)) '+
' select @update3=@update3+'' and convert(varchAr,datepArt(yyyy,#tmPMrp2.ssdate))+''''.''''+convert(varchAr,datepArt(wk,#tmPMrp2.ssdate))<='''+
' select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(wk,@mindate))+'''''''''+
' select @update3=@update3+'' and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag)'''+
' select @update3=@update3+''-(select sum(isnull(#tmPMrp1.ssqty,0)) from #tmPMrp1 where datepArt(mm,#tmPMrp1.ssdate)<=''+convert(varchAr,datepArt(mm,@mindate))'+
' select @update3=@update3+'' and convert(varchAr,datepArt(yyyy,#tmPMrp1.ssdate))+''''.''''+convert(varchAr,datepArt(wk,#tmPMrp1.ssdate))<='''+
' select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(wk,@mindate))+'''''''''+
' select @update3=@update3+'' and #tmPMrp.物料代码=#tmPMrp1.ItemCode and #tmPMrp.PmCode=#tmPMrp1.PmCode)+#tmPMrp.当前可用库存+#tmPMrp.订单已领量 where #tmPMrp.供需项目=2'''+
' select @update4=''update #tmPMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''
+' end else '+
' if @selectType=2 '+
' begin '+
#13+' select @update1=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp1 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
#13+' select @update1=@update1+''and #tmPMrp.物料代码=#tmPMrp1.ItemCode and #tmPMrp.PmCode=#tmPMrp1.PmCode''+'')'''+
#13+' select @update1=@update1+'' where 供需项目=0'' '+
#13+' select @update2=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
#13+' select @update2=@update2+''and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag and #tmPMrp2.flag=''''s''''''+'')''+'' where 供需项目=1'' '+
#13+' select @update22=''update #tmPMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
#13+' select @update22=@update22+''and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag and #tmPMrp2.flag=''''o''''''+'')''+'' where 供需项目=12'' '+
' select @update3=''update #tmPMrp set ''+@update+''=(select sum(isnull(#tmPMrp2.ssqty,0)) from #tmPMrp2 where #tmPMrp2.ssdate<='' '+
' select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
' select @update3=@update3+'' and #tmPMrp.物料代码=#tmPMrp2.ItemCode and #tmPMrp.PmCode=#tmPMrp2.Pmflag)'' '+
' select @update3=@update3+''-(select sum(isnull(#tmPMrp1.ssqty,0)) from #tmPMrp1 where #tmPMrp1.ssdate<='' '+
' select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
' select @update3=@update3+'' and #tmPMrp.物料代码=#tmPMrp1.ItemCode and #tmPMrp.PmCode=#tmPMrp1.PmCode)+#tmPMrp.当前可用库存+#tmPMrp.订单已领量 where #tmPMrp.供需项目=2'' '+
' select @update4=''update #tmPMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''
+' end '+
' exec(@update1) '+
' exec(@update2) '+
' exec(@update22) '+
' exec(@update3) '+
' exec(@update4) '+
' select @mindate=@mindate+1 '+
' end ';
sqltext8:='update #tmpInformalMrpResult '+
' set 供需项目=case 供需项目 '+
' when ''0'' then ''需求量(剩余需求)'''+
' when ''1'' then ''系统建议量'''+
' when ''12'' then ''订单未结量'''+
' when ''2'' then ''计划可用量(预计库存量)'''+
' end ';
Application.ProcessMessages;
selectfromsql:='select * from #tmpInformalMrprseult';
with AdoQry_Main do
begin
Close;
sql.clear;
sql.Add(sqltext1);
Prepared;
try
execsql;
except
end;
Application.ProcessMessages;
Close;
sql.clear;
sql.Add(sqltext20);
Prepared;
try
execsql;
except
end;
Application.ProcessMessages;
Close;
sql.clear;
sql.Add(sqltext2);
Prepared;
try
execsql;
except
end;
Close;
sql.clear;
sql.Add(sqltext3);
Prepared;
try
execsql;
except
end;
Application.ProcessMessages;
Close;
sql.clear;
sql.Add(sqltext4);
Prepared;
try
execsql;
except
end;
Application.ProcessMessages;
Close;
sql.clear;
sql.Add(sqltext5);
Prepared;
try
execsql;
except
end;
Close;
sql.clear;
sql.Add(sqltext6);
Prepared;
try
execsql;
except
end;
Application.ProcessMessages;
with Adocommand do
begin
Close;
try
commandtext:=sqltext7;
Frm_Mrp_Qry_OblongMrp_m.Visible:=True;
Screen.Cursor:=crsqlwait;
Execute;
finally
Frm_Mrp_Qry_OblongMrp_m.Visible:=False;
Frm_Mrp_Qry_OblongMrp_m.Free;
Screen.Cursor:=crdefault;
end;
end;
Application.ProcessMessages;
Close;
sql.clear;
sql.Add('select * into #tmpInformalMrpResult from #tmPMrp Order by 物料代码,供需项目');
Prepared;
try
execsql;
except
end;
Close;
sql.clear;
sql.Add(sqltext8);
Prepared;
try
execsql;
except
end;
Close;
sql.clear;
sql.Add('select * from #tmpInformalMrpResult Order by 物料代码,PmCode');
Prepared;
try
open;
except
end;
Application.ProcessMessages;
for i:=0 to fields.Count-1 do
begin
fields[i].Origin:='#tmpInformalMrpResult';
dbgrideh.Columns[i].Field:=fields[i];
if (i>8) and (cmbselectType.Itemindex=1) then
dbgrideh.Columns[i].Width:=95;
end;
fieldbyname('物料标识').displayWidth:=30;
fieldbyname('物料代码').displayWidth:=10;
fieldbyname('物料描述').displayWidth:=35;
fieldbyname('供需项目').displayWidth:=24;
fieldbyname('物料代码').visible:=False;
fieldbyname('物料描述').visible:=False;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -