📄 mrp_qry_newoblongmrp1.pas
字号:
unit Mrp_Qry_NewOblongMrp1;
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_NewOblongMrp1 = 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_NewOblongMrp1: TFrm_Mrp_Qry_NewOblongMrp1;
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_NewOblongMrp1.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 #nnnMrpResult from MrpResult where ItemCode in(select ItemCode from #TtmpItems)';
Executesql(AdoQry_tmp,tMpsql,1);
sqltext0:=' declAre @VisualManuObject Integer '
+' Select @VisualManuObject=MrpParamValueN '
+' From MrpParam '
+' Where MrpParamCode=''VisualManuObject'''
+' select #nnnMrpResult.ItemCode, '
+' case when @VisualManuObject=1 and IsNull(Mps.MpsStatus,100)=3 then 0.0 '
+' else Item.CurrentonhandInv '
+' end as onhand, '
+' releasedate, '
+' #nnnMrpResult.PmCode, '
+' ordinal '
+' into #Ttmponhand '
+' from #nnnMrpResult '
+' join Item On #nnnMrpResult.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 #nnnMrpResult.ItemCode, '
+' case when @VisualManuObject=1 and IsNull(Mps.MpsStatus,100)=3 then 0.0 '
+' else Item.CurrentonCheckInv '
+' end as onhand, '
+' releasedate, '
+' #nnnMrpResult.PmCode, '
+' ordinal '
+' into #Ttmponcheck '
+' from #nnnMrpResult '
+' join Item on #nnnMrpResult.ItemCode=Item.ItemCode '
+' left join Mps on Item.ItemCode=Mps.ItemCode '
+' where ordinal=1';
Executesql(AdoQry_tmp,sqltext0,1);
//----------------------------------------------------------------------------------------------------------------------------\\
sqltext1:='Select distinct #nnnMrpResult.ItemCode,sum(isnull(#nnnMrpResult.GrossQty,0)) As SSQty,#nnnMrpResult.releasedate as SSDate,'+
'Item.ItemName,Itemflag=#nnnMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#nnnMrpResult.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 #nnnMrpResult(nolock) '+
'left outer Join Item On #nnnMrpResult.ItemCode=Item.ItemCode '+
' Where ordinal=4 '+
// ' and #nnnMrpResult.ItemCode in (select ItemCode from #TtmpItems) '+
' group by #nnnMrpResult.ItemCode,#nnnMrpResult.groupordinal,#nnnMrpResult.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 #nnnMrpResult.ItemCode,#nnnMrpResult.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 #nnnMrpResult';
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 #nnnMrpResult.ItemCode,#nnnMrpResult.groupordinal,#nnnMrpResult.onhand AS SSQty,#nnnMrpResult.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#nnnMrpResult.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 #nnnMrpResult(nolock) '+
' Join Item On #nnnMrpResult.ItemCode=Item.ItemCode '+
' join #Ttmponhand on #nnnMrpResult.ItemCode=#Ttmponhand.ItemCode '+
' join #Ttmponcheck on #nnnMrpResult.ItemCode=#Ttmponcheck.ItemCode '+
' Order by #nnnMrpResult.ItemCode,#nnnMrpResult.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 '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''2'',null,PmBatch,ldtime,CurrentonhandInv,CurrentonCheckInv,minqty,groupordinal,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TtmpOblongMrp3';
//----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
Application.ProcessMessages;
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), '+
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -