📄 mrp_enter_newmrpinfo3.pas
字号:
unit Mrp_Enter_NewMrpInfo3;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Outer, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBCtrls, jpeg;
Type
TFrm_Mrp_Enter_NewMrpInfo3 = Class(TFrm_Base_Outer)
AdoQry_MainItemCode: TStringField;
AdoQry_MainSSSysInfoFlag: TIntegerField;
AdoQry_MaInOrderNo: TStringField;
AdoQry_MaInOrderLineNo: TIntegerField;
AdoQry_MainSSDate: TDateTimeField;
AdoQry_MainItemName: TStringField;
AdoQry_MainDeptName: TStringField;
AdoQry_MainUomName: TStringField;
AdoQry_MainSScheck: TIntegerField;
AdoQry_MaInOrderLineStatus: TIntegerField;
AdoQry_MainDeptFlag: TStringField;
AdoQry_MainItemFlag: TStringField;
AdoQry_MainDeptVendorCode: TStringField;
AdoQry_MainDeptCode: TStringField;
Label1: TLabel;
DBText1: TDBText;
AdoQry_MainSSName: TStringField;
AdoQry_MainEmployeeName: TStringField;
AdoQry_MainEmployeeFlag: TStringField;
Label4: TLabel;
DBText4: TDBText;
AdoQry_MaindueDate: TDateTimeField;
AdoQry_MainMnldTime: TFloatField;
AdoQry_MainEmployeeCode: TStringField;
AdoQry_MainReMainQty: TFloatField;
AdoQry_Mainssflag: TStringField;
lbl_RunMrpdate: TLabel;
Label5: TLabel;
AdoQry_MaintMpssreMainqty: TFloatField;
AdOCommand: TAdOCommand;
AdoQry_MainPmCode: TIntegerField;
AdoQry_MainSysqty: TFloatField;
Label2: TLabel;
DBText2: TDBText;
btn_Item: TButton;
btn_ite_Item: TButton;
btn_Order: TButton;
AdoQry_Mainmoqty: TFloatField;
btn_uPMrp: TButton;
btn_OblongMrp: TButton;
Button1: TButton;
Button2: TButton;
AdoQry_MainRealReleaseDate: TDateTimeField;
Label3: TLabel;
Button3: TButton;
AdoQry_MainSSId: TAutoIncField;
AdoQry_MainSSQty: TFloatField;
AdoQry_MainMrpResultid: TIntegerField;
AdoQry_Maincanuseonhand: TFloatField;
Button4: TButton;
AdoQry_Mainendssid: TIntegerField;
procedure FormDestroy(Sender: TObject);
procedure Act_NewExecute(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure Act_CheckExecute(Sender: TObject);
procedure AdoQry_MainBeforeInsert(DataSet: TDataSet);
procedure DBGridEhTitleClick(Column: TColumnEh);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure AdoQry_MainBeforeEdit(DataSet: TDataSet);
procedure btn_ItemClick(Sender: TObject);
procedure btn_ite_ItemClick(Sender: TObject);
procedure btn_OrderClick(Sender: TObject);
procedure btn_uPMrpClick(Sender: TObject);
procedure btn_OblongMrpClick(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure DataSourceDataChange(Sender: TObject; Field: TField);
procedure Button3Click(Sender: TObject);
procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
AFont: TFont; var Background: TColor; State: TGridDrawState);
procedure Button4Click(Sender: TObject);
procedure FormKeyDown(Sender: TObject; var Key: Word;
Shift: TShiftState);
private
Checked:Boolean;
function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
{ Private declarations }
public
lddate:integer;
procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
function getMrpdate:string;
function getQclt(ItemCode:string):string;{ Public declarations }
{ Public declarations }
end;
var
Frm_Mrp_Enter_NewMrpInfo3: TFrm_Mrp_Enter_NewMrpInfo3;
implementation
uses Sys_Global, Mrp_Enter_NewAutoMo, Public_Show, Mrp_Enter_NewMoSsParent,
Mrp_Qry_UpRightMrp_D_Item, Mrp_Qry_InformalTotalMrp_Item,
Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_InformalTotalMrp_Mo,
Mrp_Qry_NewUpRightMrp_D, Mrp_Enter_CloseMo1,Bas_Qry_MultiBomSViewMaster,
Mrp_Qry_NewOblongMrp_D,Mrp_Enter_NewEditPassMo1, Mrp_Enter_NewAutoMo2;
{$R *.DFM}
procedure TFrm_Mrp_Enter_NewMrpInfo3.InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);
var SqlText,ItemCode,StArtSsdate,endSsdate :string;
AdoQuery,AdoQuery1:TAdoQuery;
begin
Application.ProcessMessages;
createpanel(1,dbgrideh);
inherited;
If ReadOnly=True Then
begin
TlBtn_Copy.Visible:=False;
end;
lbl_RunMrpdate.Caption:=getMrpdate;
Adocommand.Connection:=AdoQry_Main.Connection;
//**********************************************************8
// --选出每种产品中Min(SSdate)放入#C_Ss3
SqlText:=' CREATE TABLE [#C_Ss3] ('
+' [SsId] [int] ,'
+' [ItemCode] [varchAr] (50) ,'
+' [ssdate] [datetime] , '
+' [duedate] [datetime] , '
+' [SSQty] [float] , '
+' [ReMainQty] [float] ,'
+' [ssreMainqty] [float] ) '
+' CREATE TABLE [#C_Ss4] ('
+' [StArtSsId] [int] ,'
+' [endSsId] [int] ,'
+' [ItemCode] [varchAr] (50) ,'
+' [ssdate] [datetime] ,'
+' [duedate] [datetime] ,'
+' [SSQty] [float] ,'
+' [ReMainQty] [float] ,'
+' [ssreMainqty] [float] )'
+' insert into #C_Ss3 (ItemCode,ssdate)'
+' select ItemCode,min(ssdate) ssdate '
+' from #C_Ss2 group by ItemCode ';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.clear;
AdoQry_Tmp.SQL.Text := SqlText;
AdoQry_Tmp.ExecSQL;
//--循环从#C_Ss3中起出日期 并合成日期段 汇总后放入#C_Ss4并栓去#C_Ss3中的记录，#C_Ss2中的记录段
repeat
AdoQuery:=TAdoQuery.Create(self);
AdoQuery.Connection := DbConnect;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.clear;
AdoQry_Tmp.SQL.Text := ' Select * from #C_Ss3 ';
AdoQry_Tmp.open;
while not AdoQry_Tmp.Eof do
begin
ItemCode:= AdoQry_Tmp.fieldbyname('ItemCode').asstring;
StArtSsDate:= AdoQry_Tmp.fieldbyname('SsDate').asstring;
endSsDate:=SlCalendar(DbConnect,StArtSsDate,LDdate);
SqlText:= ' insert into #C_Ss4 (StArtSsId,endSsId,ItemCode,ssdate,'
+' duedate,SSQty,ReMainQty,ssreMainqty)'
+' select min(SsId) as StArtSsId,max(SsId) as endSsId,ItemCode,'
+' min(ssdate) ssdate, min(duedate) duedate,sum(SSQty) as SSQty ,'
+' sum(ReMainQty) as ReMainQty ,sum(ssReMainQty) as ssReMainQty'
+' from #C_Ss2 where ssdate >='''+StArtSsDate+''' and ssdate <='''+endSsDate+''''
+' and ItemCode= '''+AdoQry_Tmp.fieldbyname('ItemCode').asstring+''''
+' group by ItemCode'
+' Delete #C_Ss2 where ssdate >='''+StArtSsDate+''' and ssdate <='''+endSsDate+''' '
+' and ItemCode= '''+AdoQry_Tmp.fieldbyname('ItemCode').asstring+'''';
AdoQuery.Close;
AdoQuery.SQL.Text :=SqlText;
AdoQuery.ExecSQL ;
AdoQry_Tmp.Next;
end;
if AdoQry_Tmp.Eof then
begin
AdoQuery.Close;
AdoQuery.SQL.Text :=' Delete #C_Ss3'
+' insert into #C_Ss3 (ItemCode,ssdate)'
+' select ItemCode,min(ssdate) ssdate '
+' from #C_Ss2 group by ItemCode ';
AdoQuery.ExecSQL;
end;
SqlText:= 'Select * from #C_Ss2 ';
AdoQuery1:=TAdoQuery.Create(self);
AdoQuery1.Connection := DbConnect;
AdoQuery1.Close;
AdoQuery1.SQL.clear;
AdoQuery1.SQL.Text := SqlText;
AdoQuery1.Open;
until (AdoQuery1.RecordCount = 0) ;
try
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.clear;
AdoQry_Tmp.SQL.Text := 'Drop table #ss12 ';
AdoQry_Tmp.ExecSQL;
except
end;
{ SqlText:=' select #C_Ss4.StArtSsId as SsId,#C_Ss4.endSsId as endSsId,'
+' #C_Ss4.ItemCode as ItemCode,#C_Ss4.ssdate as ssdate,#C_Ss4.duedate as duedate,'
+' #C_Ss4.SSQty,#c_Ss4.ssqty as Sysqty,#C_Ss4.ReMainQty,#C_Ss4.ssreMainqty ,'
+' #tMpssInfo.ssSysInfoflag,#tMpssInfo.MrpResultid,#tMpssInfo.canuseonhand, #tMpssInfo.Orderno,#tMpssInfo.Orderlineno, #tMpssInfo.PmCode,'
+' #tMpssInfo.sScheck,#tMpssInfo.realreleasedate,#tMpssInfo.moqty,#tMpssInfo.OrderLineStatus,#tMpssInfo.DeptVendorCode as DeptVendorCode,'
+' #tMpssInfo.GrossQty as GrossQty '//,#ss.PlanInvQty'
+' into #ss12 from #C_Ss4 join #tMpsSInfo'
+' on #C_Ss4.StArtSsId=#tMpsSInfo.SsId';
} SqlText:=' select #C_Ss4.StArtSsId as SsId,#C_Ss4.endSsId as endSsId,'
+' #C_Ss4.ItemCode as ItemCode,#C_Ss4.ssdate as ssdate,#C_Ss4.duedate as duedate,'
+' #C_Ss4.SSQty,#c_Ss4.ssqty as Sysqty,#C_Ss4.ReMainQty,#C_Ss4.ssreMainqty ,'
+' #tMpssInfoResult.ssSysInfoflag,#tMpssInfoResult.MrpResultid,#tMpssInfoResult.canuseonhand, #tMpssInfoResult.Orderno,#tMpssInfoResult.Orderlineno, #tMpssInfoResult.PmCode,'
+' #tMpssInfoResult.sScheck,#tMpssInfoResult.realreleasedate,#tMpssInfoResult.moqty,#tMpssInfoResult.OrderLineStatus,#tMpssInfoResult.DeptVendorCode as DeptVendorCode,'
+' #tMpssInfoResult.GrossQty as GrossQty '//,#ss.PlanInvQty'
+' into #ss12 from #C_Ss4 join #tMpssInfoResult'
+' on #C_Ss4.StArtSsId=#tMpssInfoResult.SsId';
Executesql(AdoQry_tmp,sqltext,1);
//*********************************************************8
{ sqltext:='select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=1,MoLine.moqty,ssqty=MrpResult.onhand,Sysqty=MrpResult.onhand,ssreMainqty=reMainqty '
+' into #tMpssInfo from MrpResult '
+' left outer join MoLine on MrpResult.Orderno=MoLine.mono '
+' and MrpResult.Orderlineno=MoLine.MoLineno '
+' where (ordinal=3 ) '
+' and PmCode=0 '
+' union '
+' select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=3,MoLine.moqty,ssqty=MrpResult.Orderqty,Sysqty=MrpResult.Orderqty,ssreMainqty=0 from MrpResult '
+' join MoLine on MrpResult.Orderno=MoLine.mono '
+' and MrpResult.Orderlineno=MoLine.MoLineno '
+' where ordinal=2 '
+' and Orderlinestatus=6 '
+' and realOrderqty<=0 '
+' and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno and MoLine.MoLinestatus<>7) '
+' union '
+' select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=4,MoLine.moqty,ssqty=MrpResult.Orderqty,Sysqty=MrpResult.Orderqty,ssreMainqty=0 from MrpResult '
+' join MoLine on MrpResult.Orderno=MoLine.mono '
+' and MrpResult.Orderlineno=MoLine.MoLineno '
+' where ordinal=2 '
+' and Orderlinestatus=5 '
+' and onhand>0 '
+' and (releasedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()) or duedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate())) '
// +' and Orderlinestatus<>7 '
+' and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno and MoLine.MoLinestatus<>7) '
+' union '
+' select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=4,MoLine.moqty,ssqty=MrpResult.Orderqty,Sysqty=MrpResult.Orderqty,ssreMainqty=0 from MrpResult '
+' join MoLine on MrpResult.Orderno=MoLine.mono '
+' and MrpResult.Orderlineno=MoLine.MoLineno '
+' where ordinal=2 '
+' and Orderlinestatus<>7 '
+' and canuseonhand=1 '
// +' and Orderlinestatus<>7 '
+' and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno and MoLine.MoLinestatus<>7) '
+' union '
+' select MrpResult.*, '
+' ssid=MrpResultid,'
+' ssdate=releasedate, '
+' ssSysInfoflag=2, '
+' MoLine.moqty, '
// +' Sysqty=case when ss.reMainqty<MoLine.moqty then MoLine.moqty-ss.reMainqty '
// +' else 0 '
// +' end, '
+' ssqty=Orderqty-onhand, '
+' Sysqty=Orderqty-onhand, '
+' ssreMainqty=0 '
+' from MrpResult,MoLine '
+' where (ordinal=2 ) '
+' and Orderlinestatus<>7 '
+' and Orderqty>onhand '
+' and MrpResult.ItemCode not in (select ItemCode from MrpResult with(index(ordinal)) where ordinal=4 )'
// +' and exists(select mono from MoLine where Orderno=MoLine.mono) '
+' and MrpResult.Orderno=MoLine.mono '
+' and MrpResult.Orderlineno=MoLine.MoLineno '
+' union '
+' select MrpResult.*, '
+' ssid=MrpResultid,'
+' ssdate=releasedate, '
+' ssSysInfoflag=5, '
+' MoLine.moqty, '
// +' Sysqty=case when ss.reMainqty<MoLine.moqty then MoLine.moqty-ss.reMainqty '
// +' else 0 '
// +' end, '
+' ssqty=Orderqty-onhand, '
+' Sysqty=Orderqty-onhand, '
+' ssreMainqty=0 '
+' from MrpResult,MoLine '
+' where (ordinal=2 ) '
+' and Orderlinestatus<>7 '
+' and Orderqty>onhand '
+' and MrpResult.ItemCode in (select ItemCode from MrpResult with(index(ordinal)) where ordinal=4)'
// +' and exists(select mono from MoLine where Orderno=MoLine.mono) '
+' and MrpResult.Orderno=MoLine.mono '
+' and MrpResult.Orderlineno=MoLine.MoLineno ';
with Adocommand do
begin
commandtext:=sqltext;
Execute;
end; }
//Executesql(AdoQry_Main,sqltext,1);
sqltext:='select #ss12.*,tMpssreMainqty=case #ss12.ssreMainqty when 0 then null else #ss12.ssreMainqty end,Item.mnldtime,Item.ItemName,Uom.UomName,Dept.DeptCode,Dept.DeptName,'
+' Employee.EmployeeCode,Employee.EmployeeName,'
+' ssflag=convert(varchAr,#ss12.ssSysInfoflag)+'' ''+SysssInfo.ssName,'
+' Employee.EmployeeCode+'''+' '+'''+Employee.EmployeeName As EmployeeFlag,'
+' Dept.DeptCode+'''+' '+'''+Dept.DeptName As DeptFlag, '
+' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag,'
//+' Item.PmCode,'
+' ConVert(varchAr,SysSsInfo.SSCode)+'''+' '+'''+SysSsInfo.SsName As SsName'
+' into #ssInfoResult '
+' From #ss12 '
+' Join Item On #ss12.ItemCode=Item.ItemCode '
+' And #ss12.PmCode=0 '
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -