📄 mrp_qry_informaltotalmrp.pas
字号:
+ ' and InvOutBill.polineno=#tmpOrder.Orderlineno '
+' ) '
+ ' group by ItemCode,InvOutBill.pono,InvOutBill.polineno ';
sqltext33:='select ItemCode, '
+#13+' mono as Orderno, '
+#13+' MoLineno as Orderlineno, '
+#13+' sum(isnull(moCtrlqty,0)) as Ctrlqty '
+#13+' into #tmpItemList '
+#13+' from mnItemList '
+#13+' where exists(select * from #tmpOrder '
+#13+' where mnItemList.mono=#tmpOrder.Orderno '
+#13+' and mnItemList.MoLineno=#tmpOrder.Orderlineno) '
+#13+' group by ItemCode,mono,MoLineno '
+#13+' union '
+#13+' select ItemCode, '
+#13+' pono as Orderno, '
+#13+' polineno as Orderlineno, '
+#13+' sum(isnull(poCtrlqty,0)) as Ctrlqty '
+#13+' from opItemList '
+#13+' where exists(select * from #tmpOrder '
+#13+' where opItemList.pono=#tmpOrder.Orderno '
+#13+' and opItemList.polineno=#tmpOrder.Orderlineno) '
+#13+' group by ItemCode,pono,polineno '
+' select #tmpItemList.*, '
+ ' #tmpOrder.Orderqty, '
+ ' #tmpOrder.OrdernoFinishqty, '
+ ' #tmpInvBill.Billqty, '
+ ' case when #tmpItemList.Ctrlqty>=#tmpInvBill.Billqty '
+ ' then case when #tmpInvBill.Billqty-(#tmpOrder.Orderqty-#tmpOrder.OrdernoFinishqty)*#tmpItemList.Ctrlqty/#tmpOrder.Orderqty<0 then 0 '
+ ' else #tmpInvBill.Billqty-(#tmpOrder.Orderqty-#tmpOrder.OrdernoFinishqty)*#tmpItemList.Ctrlqty/#tmpOrder.Orderqty end '
+ ' when #tmpItemList.Ctrlqty<#tmpInvBill.Billqty '
+ ' then (#tmpOrder.Orderqty-#tmpOrder.OrdernoFinishqty)*#tmpInvBill.Billqty/#tmpOrder.Orderqty '
+ ' end as Assignedqty '
+ ' into #tmPAssignedqty '
+ ' from #tmpItemList,#tmpOrder,#tmpInvBill '
+ ' where #tmpItemList.Orderno=#tmpOrder.Orderno '
+ ' and #tmpItemList.Orderlineno=#tmpOrder.Orderlineno '
+ ' and #tmpItemList.Orderno=#tmpInvBill.Orderno '
+ ' and #tmpItemList.Orderlineno=#tmpInvBill.Orderlineno '
+ ' and #tmpItemList.ItemCode=#tmpInvBill.ItemCode '
+ ' Order by #tmpItemList.Orderno,#tmpItemList.Orderlineno '
+ ' select ItemCode,sum(Assignedqty) as Assignedqty into #Assignedqty from #tmPAssignedqty '
+ ' group by ItemCode ';
//得出临时结果
sqltext8:='Select #TmPMrp1.ItemCode, '
+' #TmPMrp1.ItemName, '
+' #tmPMrp1.Qclt,'
+' #TmPMrp1.UomName, '
+' #tmPMrp1.PmCode, '
+' #tmPMrp1.PmBatch, '
+' #tmPMrp1.Batchstrat, '
+' #TmPMrp1.CurrentOnCheckInv, '
+' #TmPMrp1.GrossQty, '
+' #TmPMrp1.MinQty, '
+' isnull(#Assignedqty.Assignedqty,0) as Assignedqty,'
//+' #Assignedqty.Assignedqty, '
+' isnull(#TmPMrp1.CurrentOnHandInv,0) As CurrentOnHandInv,'
+' isnull(#TmPMoLine1.Qty1,0)+isnull(#TmpPoLine1.Qty1,0) As Orderpreqty,'
+' isnull(#TmPMoLine2.Qty2,0)+isnull(#TmpPoLine2.Qty2,0) As Orderxdqty,'
+' isnull(#TmPMoLine2.Qty3,0)+isnull(#TmpPoLine2.Qty3,0) As OrderFinishqty,'
+' isnull(#TmPMoLine2.Qty4,0)+isnull(#TmpPoLine2.Qty4,0) As OrdernoFinishqty '
+' into #tmpresul '
+' From #TmPMrp1 '
+' Left Join #TmPMoLine1 '
+' On #TmPMrp1.ItemCode=#TmPMoLine1.ItemCode '
+' Left Join #TmPMoLine2 '
+' On #TmPMrp1.ItemCode=#TmPMoLine2.ItemCode '
+' Left Join #TmpPoLine1 '
+' On #TmPMrp1.ItemCode=#TmpPOLine1.ItemCode '
+' Left Join #TmpPoLine2 '
+' On #TmPMrp1.ItemCode=#TmpPOLine2.ItemCode '
+' Left Join #TmpInvOutBillLine1 '
+' On #TmPMrp1.ItemCode=#TmpInvOutBillLine1.ItemCode '
+' Left Join #TmpInvOutBillLine2 '
+' On #TmPMrp1.ItemCode=#TmpInvOutBillLine2.ItemCode'
+#13+' left join #Assignedqty on #tmPMrp1.ItemCode=#Assignedqty.ItemCode ';
//得出最后结果
sqltext9:='select *, '
+' MrpInv=CurrentonCheckInv+CurrentonhandInv+Assignedqty, '
+' lessqty=CurrentonhandInv+CurrentonCheckInv-grossqty, '
+' Sysqty=case when Batchstrat=0 '
+' then -(CurrentonhandInv+Assignedqty-grossqty-minqty+Orderpreqty+OrdernoFinishqty) '
+' when Batchstrat=1 and PmBatch=0 '
+' then -(CurrentonhandInv+Assignedqty-grossqty-minqty+Orderpreqty+OrdernoFinishqty) '
+' when Batchstrat=1 and PmBatch>0 and (CurrentonhandInv+Assignedqty-grossqty-minqty+Orderpreqty+OrdernoFinishqty)-floor((CurrentonhandInv+Assignedqty-grossqty-minqty+Orderpreqty+OrdernoFinishqty)/PmBatch)*PmBatch=0 '
+' then -(CurrentonhandInv+Assignedqty-grossqty-minqty+Orderpreqty+OrdernoFinishqty) '
+' when Batchstrat=1 and PmBatch>0 and (CurrentonhandInv+Assignedqty-grossqty-minqty+Orderpreqty+OrdernoFinishqty)-floor((CurrentonhandInv+Assignedqty-grossqty-minqty+Orderpreqty+OrdernoFinishqty)/PmBatch)*PmBatch<>0 '
+' then (floor((grossqty-CurrentonhandInv-Assignedqty+minqty-Orderpreqty-OrdernoFinishqty)/PmBatch)+1)*PmBatch '
+' end '
+' into #Result '
+' from #tmpresul'
+' Order by #tmpresul.PmCode,#tmpresul.ItemCode'
+' drop table #tmpOrder,#tmpItemList,#tmpInvBill,#tmPAssignedqty,#Assignedqty ';
selectfromsql:='select *,Itemflag=ItemCode+'' ''+ItemName from #Result ';
Orderbyfields:='PmCode,ItemCode,';
condition:='PmCode<>0';
Application.ProcessMessages;
Executesql(AdoQry_Main,sqltext0,1);
Executesql(AdoQry_Main,sqltext1,1);
Executesql(AdoQry_Main,sqltext2,1);
Executesql(AdoQry_Main,sqltext3,1);
Executesql(AdoQry_Main,sqltext4,1);
Executesql(AdoQry_Main,sqltext5,1);
Executesql(AdoQry_Main,sqltext6,1);
Executesql(AdoQry_Main,sqltext7,1);
Executesql(AdoQry_Main,sqltext22,1);
Executesql(AdoQry_Main,sqltext33,1);
Executesql(AdoQry_Main,sqltext8,1);
Executesql(AdoQry_Main,sqltext9,1);
Executesql(AdoQry_Main,'select *,Itemflag=ItemCode+'' ''+ItemName from #Result where PmCode<>0 Order by PmCode,ItemCode',0);
end;
procedure TFrm_Mrp_Qry_InformalTotalMrp.FormDestroy(Sender: TObject);
begin
inherited;
Frm_Mrp_Qry_InformalTotalMrp:=nil;
end;
procedure TFrm_Mrp_Qry_InformalTotalMrp.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
inherited;
with AdoQry_tmp do
begin
Close;
sql.clear;
sql.Add('drop table #tmPMrp,#tmPMrp1,#tmPMoLine1,#tmPMoLine2,#tmppoline1,#tmppoline2,#tmpInvOutBillline1,#tmpInvOutBillline2,#tmpresul,#Result');
Prepared;
try
execsql;
except
end;
end;
end;
procedure TFrm_Mrp_Qry_InformalTotalMrp.Act_LookExecute(Sender: TObject);
begin
//inherited;
try
Frm_Mrp_Qry_InformalTotalMrp_D:=TFrm_Mrp_Qry_InformalTotalMrp_D.Create(self);
with Frm_Mrp_Qry_InformalTotalMrp_D do
begin
Extedt_ItemCode.Text:=AdoQry_Main.fieldbyname('ItemCode').asstring;
lbl_ItemName.Caption:=AdoQry_Main.fieldbyname('ItemName').asstring;
lbl_UomName.Caption:=AdoQry_Main.fieldbyname('UomName').asstring;
lbl_PmCode.Caption:=AdoQry_Main.fieldbyname('PmCode').asstring;
lbl_minqty.Caption:=floattostr(AdoQry_Main.fieldbyname('minqty').asfloat);
edt_CurrentonCheckInv.Text:=floattostr(AdoQry_Main.fieldbyname('CurrentonCheckInv').asfloat);
edt_CurrentonhandInv.Text:=floattostr(AdoQry_Main.fieldbyname('CurrentonhandInv').asfloat);
edt_MrpInv.Text:=floattostr(AdoQry_Main.fieldbyname('MrpInv').asfloat);
edt_grossqty.Text:=floattostr(AdoQry_Main.fieldbyname('grossqty').asfloat);
edt_lessqty.Text:=floattostr(AdoQry_Main.fieldbyname('lessqty').asfloat);
edt_Sysqty.Text:=floattostr(AdoQry_Main.fieldbyname('Sysqty').asfloat);
edt_Orderpreqty.Text:=floattostr(AdoQry_Main.fieldbyname('Orderpreqty').asfloat);
edt_Orderxdqty.Text:=floattostr(AdoQry_Main.fieldbyname('Orderxdqty').asfloat);
edt_OrderFinishqty.Text:=floattostr(AdoQry_Main.fieldbyname('OrderFinishqty').asfloat);
edt_OrdernoFinishqty.Text:=floattostr(AdoQry_Main.fieldbyname('OrdernoFinishqty').asfloat);
showmodal;
end;
finally
Frm_Mrp_Qry_InformalTotalMrp_D.Free;
end;
end;
procedure TFrm_Mrp_Qry_InformalTotalMrp.btn_ItemClick(Sender: TObject);
begin
inherited;
Qry_Item(dbconnect,AdoQry_Main.fieldbyname('ItemCode').asstring);
activecontrol:=dbgrideh;
{ try
Frm_Mrp_Qry_UpRightMrp_D_Item:=TFrm_Mrp_Qry_UpRightMrp_D_Item.Create(Application);
with Frm_Mrp_Qry_UpRightMrp_D_Item do
begin
with AdoQry_tmp do
begin
Connection:=dbconnect;
Close;
sql.clear;
sql.Add('select Item.*,t1.EmployeeName as buyerName,'
+' t2.EmployeeName as PlannerName,'
+' t3.EmployeeName as whkeeperName,'
+' t4.UomName '
+' from Item,Employee t1,Employee t2,Employee t3,Uom t4'
+' where Item.EmployeeCode*=t1.EmployeeCode '
+' and Item.Pla_EmployeeCode*=t2.EmployeeCode'
+' and Item.wh_EmployeeCode*=t3.EmployeeCode'
+' and Item.UomCode*=t4.UomCode'
+' and ItemCode='+quotedstr(AdoQry_Main.fieldbyname('ItemCode').asstring));
open;
edit1.text:=fieldbyname('ItemCode').asstring;
edit2.text:=fieldbyname('ItemName').asstring;
edit3.text:=fieldbyname('UomCode').asstring;
label4.Caption:=fieldbyname('UomName').asstring;
edit4.text:=fieldbyname('designno').asstring;
edit3.text:=fieldbyname('UomCode').asstring;
combobox1.ItemIndex:=fieldbyname('ItemUsable').asinteger;
combobox2.ItemIndex:=fieldbyname('ItemType').asinteger;
edit5.text:=fieldbyname('EmployeeCode').asstring;
label9.Caption:=fieldbyname('buyerName').asstring;
edit6.text:=fieldbyname('Pla_EmployeeCode').asstring;
label11.Caption:=fieldbyname('PlannerName').asstring;
edit7.text:=fieldbyname('whCode').asstring;
edit8.text:=fieldbyname('wh_EmployeeCode').asstring;
label14.Caption:=fieldbyname('whkeeperName').asstring;
combobox3.ItemIndex:=fieldbyname('onlydefaultwh').asinteger;
edit9.text:=floattostr(fieldbyname('maxqty').asfloat);
edit10.text:=floattostr(fieldbyname('minqty').asfloat);
combobox4.ItemIndex:=fieldbyname('limitout').asinteger;
combobox5.ItemIndex:=fieldbyname('longpurchlt').asinteger;
edit11.text:=floattostr(fieldbyname('purchldtime').asfloat);
edit12.text:=floattostr(fieldbyname('mnldtime').asfloat);
combobox6.ItemIndex:=fieldbyname('PmCode').asinteger;
combobox7.ItemIndex:=fieldbyname('Batchstrat').asinteger;
edit13.text:=floattostr(fieldbyname('PmBatch').asfloat);
edit14.text:=fieldbyname('DeptCode').asstring;
edit15.text:=fieldbyname('BarCode').asstring;
combobox8.ItemIndex:=fieldbyname('BatchCtrl').asinteger;
edit16.text:=floattostr(fieldbyname('Capacityhours').asfloat);
combobox9.ItemIndex:=fieldbyname('canSale').asinteger;
edit17.text:=fieldbyname('ItemCode2').asstring;
edit18.text:=fieldbyname('ItemshortName').asstring;
edit19.text:=floattostr(fieldbyname('CurrentonhandInv').asfloat);
edit20.text:=floattostr(fieldbyname('CurrentonCheckInv').asfloat);
end;
showmodal;
end;
finally
Frm_Mrp_Qry_UpRightMrp_D_Item.Free;
activecontrol:=dbgrideh;
end; }
end;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -