mrp_qry_oblongmrp1.pas
来自「一个MRPII系统源代码版本」· PAS 代码 · 共 778 行 · 第 1/3 页
PAS
778 行
unit Mrp_Qry_OblongMrp1;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBTables;
Type
TFrm_Mrp_Qry_OblongMrp1 = Class(TFrm_Base_Qry)
lbl_ItemCode: TLabel;
Label1: TLabel;
lbl_ItemName: TLabel;
Label2: TLabel;
lbl_ldtime: 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_OblongMrp1: TFrm_Mrp_Qry_OblongMrp1;
implementation
uses Mrp_Qry_OblongMrp_m,Sys_Global, Mrp_Qry_InformalUpRightMrp_D_ParentMo,
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_OblongMrp1.InitForm(AdOConnection: TAdOConnection;
ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5,sqltext20,sqltext6,sqltext7,sqltext8:string;
i:integer;
sqltext0: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;
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 ';
Application.ProcessMessages;
with AdoQry_tmp do
begin
Close;
sql.clear;
sql.Add(sqltext0);
Prepared;
try
execsql;
except
end;
end;
//----------------------------------------------------------------------------------------------------------------------------\\
sqltext1:='Select distinct MrpResult.ItemCode,sum(isnull(MrpResult.GrossQty,0)) As SSQty,MrpResult.releasedate as SSDate,'+
'Item.ItemName,Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,MrpResult.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
'(Case MrpResult.PmCode'+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime '+
' into #TtmpOblongMrp1 '+
' From MrpResult(nolock) '+
'left outer Join Item On MrpResult.ItemCode=Item.ItemCode '+
' Where grossqty>0 and ordinal=1 '+
' and MrpResult.ItemCode in (select ItemCode from #TtmpItems) '+
' group by MrpResult.ItemCode,MrpResult.releasedate,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,Item.minqty,MrpResult.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,Item.purchldtime,Item.mnldtime'+
' Order by MrpResult.ItemCode,MrpResult.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';
sqltext2:='Select distinct #TtmpOblongmdMrpResult1.ItemCode,ssqty=case #TtmpOblongmdMrpResult1.ordinal when 2 then sum(isnull(#TtmpOblongmdMrpResult1.OrderQty,0)) else sum(isnull(#TtmpOblongmdMrpResult1.netqty,0)) end ,'
+' #TtmpOblongmdMrpResult1.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=#TtmpOblongmdMrpResult1.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#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 5 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,5) '+
' group by #TtmpOblongmdMrpResult1.ItemCode,#TtmpOblongmdMrpResult1.Pmflag,#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 MrpResult.ItemCode,MrpResult.onhand AS SSQty,MrpResult.releaseDate as ssdate,'+
' Item.ItemName,Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,#Ttmponhand.onhand-#Ttmponhand.Assignedqty as CurrentonhandInv,#Ttmponhand.Assignedqty,#Ttmponhand.oncheck as CurrentonCheckInv,Item.minqty,MrpResult.PmCode,'+
' Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
' (Case MrpResult.PmCode '+
' When 1 Then '+
' Item. PurchLDTime '+
' Else '+
' Item.MNLDTime '+
' end) As LdTime '+
' into #TtmpOblongMrp3 '+
' From MrpResult(nolock) '+
' Join Item On MrpResult.ItemCode=Item.ItemCode '+
' join #Ttmponhand on MrpResult.ItemCode=#Ttmponhand.ItemCode and MrpResult.PmCode=#Ttmponhand.PmCode'+
' Order by MrpResult.ItemCode,MrpResult.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
sqltext4:='Select Top 0 物料标识=Itemflag,物料代码=ItemCode,物料描述=ItemName,供需项目=''0'',期初=null,批量=PmBatch,提前期=LdTime,当前可用库存=CurrentonhandInv,订单已领量=Assignedqty,当前待检库存=CurrentonCheckInv,minqty,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,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,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TtmpOblongMrp1 '+
' insert into #TtmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''1'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TtmpOblongMrp2 '+
' insert into #TtmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''12'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TtmpOblongMrp1 '+
' insert into #TtmpOblongMrp '+
' select distinct Itemflag,ItemCode,ItemName,SupplyType=''2'',null,PmBatch,ldtime,CurrentonhandInv,Assignedqty,CurrentonCheckInv,minqty,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), '+
' @update4 varchAr(200), '+
' @mm varchAr(2), '+
' @day varchAr(2), '+
' @selectType tinyint, '+
' @sameweek varchAr(18), '+
' @sameMonth varchAr(18) '+
' select @sameweek=''ddf'''+
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?