mrp_qry_newinformaloblongmrp1.pas
来自「一个MRPII系统源代码版本」· PAS 代码 · 共 827 行 · 第 1/3 页
PAS
827 行
unit Mrp_Qry_NewInformalOblongMrp1;
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_NewInformalOblongMrp1 = 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;
Label12: TLabel;
lbl_Assignedqty: 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
ItemCode:string;
iSChanged:boolean;
procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
function isbuyerorPlanner(user:string):integer;
{ Public declarations }
end;
var
Frm_Mrp_Qry_NewInformalOblongMrp1: TFrm_Mrp_Qry_NewInformalOblongMrp1;
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_Qry_OblongMrp1_C;
{$R *.DFM}
{ TFrm_Mrp_Qry_OblongMrp }
procedure TFrm_Mrp_Qry_NewInformalOblongMrp1.InitForm(AdOConnection: TAdOConnection;
ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5,sqltext20,sqltext6,sqltext7,sqltext8:string;
i:integer;
sqltext0,tMpsql:string;
begin
Application.ProcessMessages;
inherited;
if not iSChanged then
try
Frm_Mrp_Qry_OblongMrp1_C:=TFrm_Mrp_Qry_OblongMrp1_C.Create(self);
Frm_Mrp_Qry_OblongMrp1_C.AdoQry_Tmp.Connection:=dbconnect;
case isbuyerorPlanner(userCode) of
1: begin
Frm_Mrp_Qry_OblongMrp1_C.Edit1.Text:=userCode;
Frm_Mrp_Qry_OblongMrp1_C.Label4.Caption:=Frm_Mrp_Qry_OblongMrp1_C.geTEmployeeName(userCode);
end;
2: begin
Frm_Mrp_Qry_OblongMrp1_C.Edit2.Text:=userCode;
Frm_Mrp_Qry_OblongMrp1_C.Label6.Caption:=Frm_Mrp_Qry_OblongMrp1_C.geTEmployeeName(userCode);
end;
3: begin
Frm_Mrp_Qry_OblongMrp1_C.Edit2.Text:=userCode;
Frm_Mrp_Qry_OblongMrp1_C.Label6.Caption:=Frm_Mrp_Qry_OblongMrp1_C.geTEmployeeName(userCode);
end;
end;
Frm_Mrp_Qry_OblongMrp1_C.Showmodal;
if Frm_Mrp_Qry_OblongMrp1_C.modalResult<>mrok then abort;
finally
Frm_Mrp_Qry_OblongMrp1_C.Free;
end;
lbl_Mrprundate.Caption:=getValue('RunMrpdate','MrpParam');
Frm_Mrp_Qry_OblongMrp_m:=TFrm_Mrp_Qry_OblongMrp_m.Create(self);
Adocommand.Connection:=AdoQry_Main.Connection;
//--------------------------------------------------------------------\\
Application.ProcessMessages;
tMpsql:='select * into #nnnnMrpResult from InformalMrpResult where ItemCode in(select ItemCode from #TtmpItems)';
Executesql(AdoQry_tmp,tMpsql,1);
{sqltext0:='select ItemCode,PmCode, min(releasedate) as releasedate '
+' into #TtmpOblongm1 '
+' from MrpResult '
+' where ordinal=1 '
+' and ItemCode in (select ItemCode from #TtmpItems)'
+' group by ItemCode,PmCode '
+' select #TtmpOblongm1.ItemCode,#TtmpOblongm1.PmCode,min(MrpResult.releasedate) as releasedate'
+' into #TtmpOblongm '
+' from MrpResult,#TtmpOblongm1 '
+' where MrpResult.ItemCode=#TtmpOblongm1.ItemCode and MrpResult.releasedate>=#TtmpOblongm1.releasedate'
+' and MrpResult.ordinal=3 '
+' group by #TtmpOblongm1.ItemCode,#TtmpOblongm1.PmCode'
+' select distinct t1.ItemCode,t1.onhand,Assignedqty=isnull(t1.Assignedqty,0),t1.oncheck,t1.releasedate,t1.PmCode,t1.ordinal '
+' into #Ttmponhand '
+' from MrpResult t1, #TtmpOblongm t2 '
+' where t1.ItemCode=t2.ItemCode '
+' and t1.releasedate=t2.releasedate '
+' and t1.ordinal=3 '; }
sqltext0:=' declAre @VisualManuObject Integer '
+' Select @VisualManuObject=MrpParamValueN '
+' From MrpParam '
+' Where MrpParamCode=''VisualManuObject'''
+' select #nnnnMrpResult.ItemCode, '
+' case when @VisualManuObject=1 and IsNUll(Mps.MpsStatus,100)=3 then 0.0 '
+' else Item.CurrentonhandInv '
+' end as onhand, '
+' releasedate, '
+' #nnnnMrpResult.PmCode, '
+' ordinal '
+' into #Ttmponhand '
+' from #nnnnMrpResult '
+' Join Item On #nnnnMrpResult.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 #nnnnMrpResult.ItemCode, '
+' case when @VisualManuObject=1 and IsNull(Mps.MpsStatus,100)=3 then 0.0 '
+' else Item.CurrentonCheckInv '
+' end as onhand, '
+' releasedate, '
+' #nnnnMrpResult.PmCode, '
+' ordinal '
+' into #Ttmponcheck '
+' from #nnnnMrpResult '
+' Join Item On #nnnnMrpResult.ItemCode=Item.ItemCode '
+' left join Mps On Item.ItemCode=Mps.ItemCode '
+' where ordinal=1';
Executesql(AdoQry_tmp,sqltext0,1);
//----------------------------------------------------------------------------------------------------------------------------\\
sqltext1:='Select distinct #nnnnMrpResult.ItemCode,sum(isnull(#nnnnMrpResult.GrossQty,0)) As SSQty,#nnnnMrpResult.releasedate as SSDate,'+
'Item.ItemName,Itemflag=#nnnnMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#nnnnMrpResult.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 #TtmpOblongMrp1 '+
' From #nnnnMrpResult(nolock) '+
'left outer Join Item On #nnnnMrpResult.ItemCode=Item.ItemCode '+
' Where ordinal=4 '+
// ' and #nnnnMrpResult.ItemCode in (select ItemCode from #TtmpItems) '+
' group by #nnnnMrpResult.ItemCode,#nnnnMrpResult.groupordinal,#nnnnMrpResult.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 #nnnnMrpResult.ItemCode,#nnnnMrpResult.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
{ sqltext20:='declAre @PmCode tinyint, '
+' @tmpPmCode tinyint, '
+' @ordinal tinyint, '
+' @Count int '
+' select top 0 *,Pmflag=0 into #TtmpOblongmdMrpResult1 from MrpResult '
+' select * into #TtmpOblongMrpResult1 from MrpResult where ItemCode in (select ItemCode from #TtmpItems) '
+' select @Count=(select Count(*) from #TtmpOblongMrpResult1) '
+' set rowCount 1 '
+' insert into #TtmpOblongmdMrpResult1 '
+' select *,Pmflag=PmCode from #TtmpOblongMrpResult1 '
+' delete from #TtmpOblongMrpResult1 '
+' select @PmCode=PmCode from #TtmpOblongmdMrpResult1 '
+' while @Count>=1 '
+' begin '
+' select @ordinal=ordinal from #TtmpOblongMrpResult1 '
+' insert into #TtmpOblongmdMrpResult1 '
+' select *,Pmflag=case when @ordinal=1 then PmCode else @PmCode end from #TtmpOblongMrpResult1 '
+' if @ordinal=1 '
+' select @PmCode=PmCode from #TtmpOblongMrpResult1 '
+' delete from #TtmpOblongMrpResult1 '
+' select @Count=@Count-1 '
+' end '
+ ' set rowCount 999999999'; }
sqltext20:=' select * into #TtmpOblongmdMrpResult1 from #nnnnMrpResult';
sqltext2:='Select distinct #TtmpOblongmdMrpResult1.ItemCode,ssqty=case #TtmpOblongmdMrpResult1.ordinal when 2 then sum(isnull(#TtmpOblongmdMrpResult1.OrderQty,0)) else sum(isnull(#TtmpOblongmdMrpResult1.onhand,0)) end ,'
+' #TtmpOblongmdMrpResult1.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#TtmpOblongmdMrpResult1.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#TtmpOblongmdMrpResult1.groupordinal,#TtmpOblongmdMrpResult1.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt, '+
' (Case #TtmpOblongmdMrpResult1.PmCode '+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime, '+
' flag=case isnull(#TtmpOblongmdMrpResult1.ordinal,''/\\'') '+
' when 3 then ''s'' '+
' else ''o'' '+
' end '+
// ' Pmflag=#TtmpOblongmdMrpResult1.Pmflag '+
' into #TtmpOblongMrp2 '+
' From #TtmpOblongmdMrpResult1(nolock) '+
' left outer Join Item On #TtmpOblongmdMrpResult1.ItemCode=Item.ItemCode '+
' Where ordinal in(2,3) '+
' group by #TtmpOblongmdMrpResult1.ItemCode,#TtmpOblongmdMrpResult1.groupordinal,#TtmpOblongmdMrpResult1.releasedate,#TtmpOblongmdMrpResult1.ordinal,Item.purchldtime,Item.mnldtime,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,'
+' Item.minqty,#TtmpOblongmdMrpResult1.PmCode,Item.Batchstrat,'
+' Item.Preparelt,Item.runlt,Item.Qclt'+
' Order by #TtmpOblongmdMrpResult1.ItemCode,#TtmpOblongmdMrpResult1.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
sqltext3:=' Select distinct #nnnnMrpResult.ItemCode,#nnnnMrpResult.groupordinal,#nnnnMrpResult.onhand AS SSQty,#nnnnMrpResult.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#nnnnMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,#Ttmponhand.onhand as CurrentonhandInv,#Ttmponcheck.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 #TtmpOblongMrp3 '+
' From #nnnnMrpResult(nolock) '+
' Join Item On #nnnnMrpResult.ItemCode=Item.ItemCode '+
' join #Ttmponhand on #nnnnMrpResult.ItemCode=#Ttmponhand.ItemCode '+
' join #Ttmponcheck on #nnnnMrpResult.ItemCode=#Ttmponcheck.ItemCode '+
' Order by #nnnnMrpResult.ItemCode,#nnnnMrpResult.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
sqltext4:='Select Top 0 物料标识=Itemflag,物料代码=ItemCode,物料描述=ItemName,供需项目=''0'',期初=null,批量=PmBatch,提前期=LdTime,当前可用库存=CurrentonhandInv,当前待检库存=CurrentonCheckInv,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt '+
' Into #TtmpOblongMrp4 '+
' From #TtmpOblongMrp3 '+
' alter table #TtmpOblongMrp4 Add stArtqty float '+
' alter table #TtmpOblongMrp4 Add SupplyType varchAr(50) ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
sqltext5:= 'select 物料标识,物料代码,物料描述,供需项目=SupplyType,期初=stArtqty,批量,提前期,当前可用库存,当前待检库存,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt'+
' into #TtmpOblongMrp '+
' from #TtmpOblongMrp4 ';
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
sqltext6:='insert into #TtmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''0'',null,PmBatch,ldtime, CurrentonhandInv=0,0,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TtmpOblongMrp1 '+
' insert into #TtmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''1'',null,PmBatch,ldtime,CurrentonhandInv=0,0,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TtmpOblongMrp1 '+
' insert into #TtmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''12'',null,PmBatch,ldtime,CurrentonhandInv=0,0,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TtmpOblongMrp1 '+
' insert into #TtmpOblongMrp '+
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?