📄 mrp_qry_informaloblongmrp_d.pas
字号:
unit Mrp_Qry_InformalOblongMrp_D;
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_InformalOblongMrp_D = Class(TFrm_Base_Qry)
lbl_ItemCode: TLabel;
Label1: TLabel;
lbl_ItemName: TLabel;
Label3: TLabel;
lbl_PmBatch: TLabel;
lbl_CurrentInv: TLabel;
Label2: TLabel;
lbl_ldtime: TLabel;
Label4: TLabel;
Label5: TLabel;
cmbselectType: TComboBox;
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;
lbl_runlt: TLabel;
Label12: TLabel;
lbl_Preparelt: TLabel;
Label14: TLabel;
Label15: TLabel;
lbl_Qclt: TLabel;
Label10: TLabel;
lbl_Assignedqty: TLabel;
procedure AdoQry_MainAfterScroll(DataSet: TDataSet);
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 FormCreate(Sender: TObject);
procedure cmbselectTypeChange(Sender: TObject);
procedure Act_LookExecute(Sender: TObject);
procedure Act_ShowGridExecute(Sender: TObject);
procedure FormShow(Sender: TObject);
private
function getValue(Code:string;tableName:string):variant;
{ Private declarations }
public
ItemCode:string;
procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
{ Public declarations }
end;
var
Frm_Mrp_Qry_InformalOblongMrp_D: TFrm_Mrp_Qry_InformalOblongMrp_D;
implementation
uses Mrp_Qry_OblongMrp_m, Mrp_Qry_InformalUpRightMrp_D_ParentMo,Sys_Global,
Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_InformalTotalMrp_Mo,Mrp_Global;
{$R *.DFM}
{ TFrm_Mrp_Qry_InformalOblongMrp }
procedure TFrm_Mrp_Qry_InformalOblongMrp_D.InitForm(
AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5,sqltext6,sqltext7,sqltext8:string;
i:integer;
sqltext0,sqltext20:string;
success:integer;
tMpsql:string;
begin
inherited;
lbl_Mrprundate.Caption:=getValue('InformalRunMrpdate','MrpParam');
Frm_Mrp_Qry_OblongMrp_m:=TFrm_Mrp_Qry_OblongMrp_m.Create(self);
Adocommand.Connection:=AdoQry_Main.Connection;
success:=TemporArilyRunMrp(dbconnect,'select ItemCode from InformalMrpResult where ordinal=1 and ItemCode='+quotedstr(ItemCode),0);
tMpsql:='select * into #ttttMrpResult from InformalMrpResult'
+' where ItemCode='+quotedstr(ItemCode);
Executesql(AdoQry_tmp,tMpsql,1);
Application.ProcessMessages;
sqltext0:='select ItemCode, min(releasedate) as releasedate '
+' into #tmPm '
+' from #ttttMrpResult '
+' where ordinal=3 '
+' and ItemCode='+quotedstr(ItemCode)
+' group by ItemCode '
+' select t1.ItemCode,t1.onhand,Assignedqty=isnull(t1.Assignedqty,0),t1.oncheck,t1.releasedate,t1.PmCode,t1.ordinal '
+' into #tmponhand '
+' from #ttttMrpResult t1, #tmPm t2 '
+' where t1.ItemCode=t2.ItemCode '
+' and t1.releasedate=t2.releasedate '
+' and t1.ordinal=3 ';
with AdoQry_tmp do
begin
Close;
sql.clear;
sql.Add(sqltext0);
Prepared;
try
execsql;
except
end;
end;
Application.ProcessMessages;
sqltext1:='Select distinct #ttttMrpResult.ItemCode,#ttttMrpResult.GrossQty As SSQty,#ttttMrpResult.releasedate as SSDate,'+
'Item.ItemName,Itemflag=#ttttMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#ttttMrpResult.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
'(Case #ttttMrpResult.PmCode'+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime '+
' into #TmPMrp1 '+
' From #ttttMrpResult(nolock) '+
'left outer Join Item On #ttttMrpResult.ItemCode=Item.ItemCode '+
' Where grossqty>0 and ordinal=1 '
+' and #ttttMrpResult.ItemCode='+quotedstr(ItemCode)+
' Order by #ttttMrpResult.ItemCode,#ttttMrpResult.releasedate ';
Application.ProcessMessages;
sqltext20:='declAre @PmCode tinyint, '
+' @tmpPmCode tinyint, '
+' @ordinal tinyint, '
+' @Count int '
+' select top 0 *,Pmflag=0 into #tmPmdMrpResult1 from #ttttMrpResult '
+' select * into #tmPMrpResult1 from #ttttMrpResult where ItemCode='+quotedstr(ItemCode)
+' select @Count=(select Count(*) from #tmPMrpResult1) '
+' set rowCount 1 '
+' insert into #tmPmdMrpResult1 '
+' select *,Pmflag=PmCode from #tmPMrpResult1 '
+' delete from #tmPMrpResult1 '
+' select @PmCode=PmCode from #tmPmdMrpResult1 '
+' while @Count>=1 '
+' begin '
+' select @ordinal=ordinal from #tmPMrpResult1 '
+' insert into #tmPmdMrpResult1 '
+' select *,Pmflag=case when @ordinal=1 then PmCode else @PmCode end from #tmPMrpResult1 '
+' if @ordinal=1 '
+' select @PmCode=PmCode from #tmPMrpResult1 '
+' delete from #tmPMrpResult1 '
+' select @Count=@Count-1 '
+' end '
+ ' set rowCount 999999999';
sqltext2:='Select distinct #tmPmdMrpResult1.ItemCode,ssqty=case #tmPmdMrpResult1.ordinal when 2 then sum(isnull(#tmPmdMrpResult1.OrderQty,0)) else sum(isnull(#tmPmdMrpResult1.netqty,0)) end ,#tmPmdMrpResult1.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#tmPmdMrpResult1.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#tmPmdMrpResult1.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt, '+
' (Case #tmPmdMrpResult1.PmCode '+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime, '+
' flag=case isnull(#tmPmdMrpResult1.ordinal,''/\\'') '+
' when 5 then ''s'' '+
' else ''o'' '+
' end, '+
' Pmflag=#tmPmdMrpResult1.Pmflag '+
' into #TmPMrp2 '+
' From #tmPmdMrpResult1(nolock) '+
' left outer Join Item On #tmPmdMrpResult1.ItemCode=Item.ItemCode '+
' Where ordinal in(2,5) '+
' group by #tmPmdMrpResult1.ItemCode,#tmPmdMrpResult1.Pmflag,#tmPmdMrpResult1.releasedate,#tmPmdMrpResult1.ordinal,Item.purchldtime,Item.mnldtime,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,Item.minqty,#tmPmdMrpResult1.PmCode,Item.Batchstrat,'
+'Item.Preparelt,Item.runlt,Item.Qclt'+
' Order by #tmPmdMrpResult1.ItemCode,#tmPmdMrpResult1.releasedate ';
Application.ProcessMessages;
sqltext3:=' Select distinct #ttttMrpResult.ItemCode,#ttttMrpResult.onhand AS SSQty,#ttttMrpResult.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#ttttMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,#tmponhand.onhand-#tmponhand.Assignedqty as CurrentonhandInv,#tmponhand.Assignedqty,#tmponhand.oncheck as CurrentonCheckInv,Item.minqty,#ttttMrpResult.PmCode,'
+'Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
' (Case #ttttMrpResult.PmCode '+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime '+
' into #TmPMrp3 '+
' From #ttttMrpResult(nolock) '+
' Join Item On #ttttMrpResult.ItemCode=Item.ItemCode '+
' join #tmponhand on #ttttMrpResult.ItemCode=#tmponhand.ItemCode'+
' Order by #ttttMrpResult.ItemCode,#ttttMrpResult.releasedate ';
sqltext4:='Select Top 0 物料标识=Itemflag,物料代码=ItemCode,物料描述=ItemName,供需项目=''0'',期初=null,批量=PmBatch,提前期=LdTime,当前可用库存=CurrentonhandInv,订单已领量=Assignedqty,当前待检库存=CurrentonCheckInv,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt '+
' Into #TmPMrp4 '+
' From #TmPMrp3 '+
' alter table #tmPMrp4 Add stArtqty float '+
' alter table #tmPMrp4 Add SupplyType varchAr(50) ';
sqltext5:= 'select 物料标识,物料代码,物料描述,供需项目=SupplyType,期初=stArtqty,批量,提前期,当前可用库存,订单已领量,当前待检库存,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt'+
' into #tmPMrp '+
' from #tmPMrp4 ';
Application.ProcessMessages;
sqltext6:='insert into #tmPMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''0'',null,PmBatch,ldtime, CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrp1 '+
' insert into #tmPMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''1'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrp2 '+
' insert into #tmPMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''12'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrp1 '+
' insert into #tmPMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''2'',null,PmBatch,ldtime,CurrentonhandInv,Assignedqty,CurrentonCheckInv,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrp3';
sqltext7:='declAre @maxdate datetime, '+
' @mindate datetime, '+
' @Monthbegin datetime,'+
' @outputweek tinyint,'+
' @s varchAr(80), '+
' @update varchAr(300), '+
' @update1 varchAr(900), '+
' @update2 varchAr(900), '+
' @update22 varchAr(900), '+
' @update3 varchAr(900), '+
' @update4 varchAr(200), '+
' @mm varchAr(2), '+
' @day varchAr(2), '+
' @selectType tinyint, '+
' @sameweek varchAr(18), '+
' @sameMonth varchAr(18) '+
' select @sameweek=''ddf'''+
' select @sameMonth=''dfd'''+
' select @selectType='+inttostr(cmbselectType.Itemindex)+
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -