📄 inv_opmaterialsum.pas
字号:
unit Inv_OpMaterialSum;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Outer, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBCtrls, Base_Qry, jpeg;
Type
TFrm_Inv_OpMaterialSum = Class(TFrm_Base_Qry)
Label4: TLabel;
lbl_Month: TLabel;
AdoQry_tmp1: TAdoQuery;
ToolButton6: TToolButton;
Button1: TButton;
AdoQry_Vendor: TAdoQuery;
procedure Act_FilterExecute(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
AFont: TFont; var Background: TColor; State: TGridDrawState);
procedure Button1Click(Sender: TObject);
private
Itemtmp:string;
{ Private declarations }
public
acondition:string;
aVendorCode:string;
procedure initform(AdOConnection:TAdOConnection;ReadOnly:boolean);Override;
procedure InitReport;Override;
{ Public declarations }
end;
var
Frm_Inv_OpMaterialSum: TFrm_Inv_OpMaterialSum;
implementation
uses Inv_OpMaterialSum_C,Sys_Global, Inv_OpMaterialSum_D;
{$R *.DFM}
procedure TFrm_Inv_OpMaterialSum.InitReport;
var
userName:string;
begin
inherited;
with AdoQry_tmp do
begin
Close;
sql.Text:='select * from Employee where EmployeeCode='''+userCode+'''';
open;
userName:=fieldbyname('EmployeeName').asstring;
end;
ExtprintReport.subtitle1:='委托加工单位：'+lbl_Order.Caption+' '+'月份：'+lbl_Month.Caption ;
ExtprintReport.Foot1:=' 对方单位签名： 制表：'+userName+' 审核： ';
end;
procedure TFrm_Inv_OpMaterialSum.initform(AdOConnection:TAdOConnection;ReadOnly:boolean);
begin
inherited;
AdoQry_Vendor.Connection:=AdOConnection;
Itemtmp:='#tmpoPmsum';
acondition:='';
with AdoQry_tmp do
begin
Close;
sql.text:='drop table '+Itemtmp;
try
execsql;
except
end;
Close;
sql.clear;
sql.Add('create table '+ItemTmp+' (ItemCode varchAr(16) null,'+
' ItemCode_S varchAr(16) null,'+
' OrderField Integer null,'+
' LMBlncQty float null,'+
' InQty float null,'+
' Ite_ItemCode varchAr(16) null,'+
' ProductQty float null,'+
' BomQty float null,'+
' BomScrAp float null,'+
' Qty float null,'+
' OutQty float null,'+
' UomName1 varchAr(10) null,'+
' flag varchAr(1) null,'+
' BlncQty float null )');
execsql;
end;
Frm_Sys_Condition:=TFrm_Inv_OpMaterialSum_C.Create(Self);
TFrm_Inv_OpMaterialSum_C(Frm_Sys_Condition).InitForm(AdoQry_Main.Connection,UserCode,LoginDate);
//定额、损耗小数位不受限制
AmountFields:='LMBlncQty,OutQty,BlncQty,';
FreeFields:='Bomqty,BomScrAp,';
Act_Filter.Execute;
// AdoQry_Main.FindField('blncqty').displayformat:='#.##';
end;
procedure TFrm_Inv_OpMaterialSum.Act_FilterExecute(Sender: TObject);
begin
if Frm_Sys_Condition.ShowModal=mrOk then
begin
Lbl_Condition.Caption:=Frm_Sys_Condition.ConditionHint;
lbl_Month.Caption:=TFrm_Inv_OpMaterialSum_C(Frm_Sys_Condition).medt_Month.text;
aCondition:=lbl_Month.Caption;
aVendorCode:=TFrm_Inv_OpMaterialSum_C(Frm_Sys_Condition).Extedt_Vendor.text;
lbl_Order.Caption :=aVendorCode+' '+TFrm_Inv_OpMaterialSum_C(Frm_Sys_Condition).Lbl_VendorName.text ;
with AdoQry_tmp do
begin
Close;
sql.clear;
sql.Add('truncate table '+Itemtmp+' '+
' select r.* into #tmpRealOpQty '+
' from RealOpQty r,InvInBill b,InvInBillLine b1 '+
' where b.InvBillMonth='''+acondition+''' '+
' and b.VendorCode='''+aVendorCode+''' '+
' and (b.BillTypeCode=''0103'' or (b.BillTypeCode=''0199'' and b.OpBill=1)) '+
' and b1.InvBillid=b.InvBillid '+
' and b1.InvBillid=r.InvBillid '+
' and b1.InvBilllineno=r.InvBilllineno '+
' insert into #tmpRealOpQty (InvBillid,InvBilllineno,ite_ItemCode,ItemCode,RealOpQtynew,realopScrAp_Percentnew) '+
' select bl.InvBillid,bl.InvBilllineno,p.itm_ItemCode,p.ItemCode,p.RealOpQty,p.realopScrAp_Percent '+
' from poRealOpQty p,InvInBillline bl,InvInBill b '+
' where b.InvBillMonth='''+acondition+''' '+
' and b.VendorCode='''+aVendorCode+''' '+
' and (b.BillTypeCode=''0103'' or (b.BillTypeCode=''0199'' and b.OpBill=1)) '+
' and bl.InvBillid=b.InvBillid '+
' and bl.pono=p.pono '+
' and bl.polineno=p.polineno '+
' update #tmpRealOpQty '+
' set RealOpQtynew=p.RealOpQty,realopScrAp_Percentnew=p.realopScrAp_Percent '+
' from poRealOpQty p,InvInBillline bl '+
' where bl.InvBillid=#tmpRealOpQty.InvBillid '+
' and bl.InvBilllineno=#tmpRealOpQty.InvBilllineno '+
' and bl.pono=p.pono '+
' and bl.polineno=p.polineno '+
' and #tmpRealOpQty.ItemCode=p.ItemCode '+
' insert '+Itemtmp+
' (Orderfield,ItemCode,Ite_ItemCode,ProductQty,BomQty,BomScrAp,qty) '+
' select ''0'',r.ItemCode,'+
' b1.ItemCode Ite_ItemCode,sum(b1.InvBillQty) Pqty,r.RealOpQtynew,r.realopScrAp_Percentnew,'+
' sum(b1.InvBillQty)*(isnull(r.RealOpQtynew,0))*(1+isnull(r.realopScrAp_Percentnew,0)/100.00) '+
' from InvInBill b,InvInBillLine b1,#tmpRealOpQty r '+
' where b.InvBillMonth='''+acondition+''' '+
' and b.VendorCode='''+aVendorCode+''' '+
' and (b.BillTypeCode=''0103'' or (b.BillTypeCode=''0199'' and b.OpBill=1)) '+
' and b1.InvBillid=b.InvBillid '+
' and b1.InvBillid=r.InvBillid '+
' and b1.InvBilllineno=r.InvBilllineno '+
' group by b1.ItemCode,r.ItemCode,r.RealOpQtynew,r.realopScrAp_Percentnew' +
' drop table #tmpRealOpQty ');
execsql;
Close;
sql.clear;
sql.Add('insert '+Itemtmp+'(Orderfield,UomName1,ItemCode,LMBlncQty,InQty,outqty,BlncQty) '+
' select distinct ''1'',c.UomName,a.ItemCode,InvlmQty,InvInqty,Invoutqty,Invblncqty'+
' from Item i,Uom c,OpMonthSum a '+
' where a.InvMonth='''+acondition+''' and a.VendorCode='''+aVendorCode+''''+
' and a.ItemCode=i.ItemCode and i.UomCode=c.UomCode');
execsql;
Close;
sql.clear;
sql.Add('update '+Itemtmp+' set ItemCode_S=ItemCode '+
'update '+Itemtmp+' set ItemCode='''+''+''',UomName1='''+''+''''+
' where Orderfield='+'0'+''+
'delete '+Itemtmp+' where (lmblncqty=0 or lmblncqty is null) and (inqty=0 or inqty is null) and (qty=0 or qty is null) and (outqty=0 or outqty is null) and (blncqty=0 or blncqty is null)');
execsql;
end;
AdoQry_Main.DisableControls;
AdoQry_Main.Close;
AdoQry_Main.SQL.clear;
SelectFromSQL:=
'select a.*,a.ItemCode+'' ''+b.ItemName ItemCode1,'+
' a.ite_ItemCode+'' ''+d.ItemName ite_ItemCode1 ,c.UomName UomName2 '+
' from '+Itemtmp+' a '+
' left join Item b'+
' on a.ItemCode=b.ItemCode '+
' left join Item d'+
' on a.ite_ItemCode=d.ItemCode'+
' left join Uom c '+
' on d.UomCode=c.UomCode '+
' Order by a.ItemCode_S,a.Orderfield,a.ite_ItemCode';
AdoQry_Main.SQL.Text:=SelectFromSQL;
AdoQry_Main.Open;
//AdoQry_Main.Sort:=OrderByFields;
AdoQry_Main.EnableControls;
end;
end;
procedure TFrm_Inv_OpMaterialSum.FormDestroy(Sender: TObject);
begin
inherited;
with AdoQry_tmp do
begin
Close;
sql.clear;
sql.Add('drop table '+Itemtmp+' ');
try
execsql;
except
end;
end;
end;
procedure TFrm_Inv_OpMaterialSum.DBGridEhGetCellParams(Sender: TObject;
Column: TColumnEh; AFont: TFont; var Background: TColor;
State: TGridDrawState);
begin
inherited;
if (TDBGridEh(Sender).DataSource.DataSet.fieldbyname('Orderfield').asstring='1') and
(column.FieldName='ItemCode1') then
afont.Color :=clred;
end;
procedure TFrm_Inv_OpMaterialSum.Button1Click(Sender: TObject);
begin
Frm_Inv_OpMaterialSum_D:=TFrm_Inv_OpMaterialSum_D.Create(self);
Frm_Inv_OpMaterialSum_D.tmPMonth:=acondition;
Frm_Inv_OpMaterialSum_D.initform(AdoQry_Main.Connection,False);
if Frm_Inv_OpMaterialSum_D.Showmodal=mrok then
begin
// AdoQry_Vendor:=Frm_Inv_OpMaterialSum_D.AdoQry_Main;
AdoQry_Vendor.Close;
AdoQry_Vendor.SQL.text:='select * from #aa where flag=1';
AdoQry_Vendor.Open;
AdoQry_Vendor.First;
while not AdoQry_Vendor.Eof do
begin
if AdoQry_Vendor.fieldbyname('flag').AsInteger<>1 then
begin
AdoQry_Vendor.next;
continue;
end;
aVendorCode:=AdoQry_Vendor.fieldbyname('VendorCode').asstring;
lbl_Order.Caption :=aVendorCode+' '+AdoQry_Vendor.fieldbyname('VendorName').asstring;
with AdoQry_tmp do
begin
Close;
sql.clear;
sql.Add('truncate table '+Itemtmp+' '+
' select r.* into #tmpRealOpQty '+
' from RealOpQty r,InvInBill b,InvInBillLine b1 '+
' where b.InvBillMonth='''+acondition+''' '+
' and b.VendorCode='''+aVendorCode+''' '+
' and (b.BillTypeCode=''0103'' or (b.BillTypeCode=''0199'' and b.OpBill=1)) '+
' and b1.InvBillid=b.InvBillid '+
' and b1.InvBillid=r.InvBillid '+
' and b1.InvBilllineno=r.InvBilllineno '+
' insert into #tmpRealOpQty (InvBillid,InvBilllineno,ite_ItemCode,ItemCode,RealOpQtynew,realopScrAp_Percentnew) '+
' select bl.InvBillid,bl.InvBilllineno,p.itm_ItemCode,p.ItemCode,p.RealOpQty,p.realopScrAp_Percent '+
' from poRealOpQty p,InvInBillline bl,InvInBill b '+
' where b.InvBillMonth='''+acondition+''' '+
' and b.VendorCode='''+aVendorCode+''' '+
' and (b.BillTypeCode=''0103'' or (b.BillTypeCode=''0199'' and b.OpBill=1)) '+
' and bl.InvBillid=b.InvBillid '+
' and bl.pono=p.pono '+
' and bl.polineno=p.polineno '+
' update #tmpRealOpQty '+
' set RealOpQtynew=p.RealOpQty,realopScrAp_Percentnew=p.realopScrAp_Percent '+
' from poRealOpQty p,InvInBillline bl '+
' where bl.InvBillid=#tmpRealOpQty.InvBillid '+
' and bl.InvBilllineno=#tmpRealOpQty.InvBilllineno '+
' and bl.pono=p.pono '+
' and bl.polineno=p.polineno '+
' and #tmpRealOpQty.ItemCode=p.ItemCode '+
' insert '+Itemtmp+
' (Orderfield,ItemCode,Ite_ItemCode,ProductQty,BomQty,BomScrAp,qty) '+
' select ''0'',r.ItemCode,'+
' b1.ItemCode Ite_ItemCode,sum(b1.InvBillQty) Pqty,r.RealOpQtynew,r.realopScrAp_Percentnew,'+
' sum(b1.InvBillQty)*(isnull(r.RealOpQtynew,0))*(1+isnull(r.realopScrAp_Percentnew,0)/100.00) '+
' from InvInBill b,InvInBillLine b1,#tmpRealOpQty r '+
' where b.InvBillMonth='''+acondition+''' '+
' and b.VendorCode='''+aVendorCode+''' '+
' and (b.BillTypeCode=''0103'' or (b.BillTypeCode=''0199'' and b.OpBill=1)) '+
' and b1.InvBillid=b.InvBillid '+
' and b1.InvBillid=r.InvBillid '+
' and b1.InvBilllineno=r.InvBilllineno '+
' group by b1.ItemCode,r.ItemCode,r.RealOpQtynew,r.realopScrAp_Percentnew' +
' drop table #tmpRealOpQty ');
execsql;
Close;
sql.clear;
sql.Add('insert '+Itemtmp+'(Orderfield,UomName1,ItemCode,LMBlncQty,InQty,outqty,BlncQty) '+
' select distinct ''1'',c.UomName,a.ItemCode,InvlmQty,InvInqty,Invoutqty,Invblncqty'+
' from Item i,Uom c,OpMonthSum a '+
' where a.InvMonth='''+acondition+''' and a.VendorCode='''+aVendorCode+''''+
' and a.ItemCode=i.ItemCode and i.UomCode=c.UomCode');
execsql;
Close;
sql.clear;
sql.Add('update '+Itemtmp+' set ItemCode_S=ItemCode '+
'update '+Itemtmp+' set ItemCode='''+''+''',UomName1='''+''+''''+
' where Orderfield='+'0'+''+
'delete '+Itemtmp+' where (lmblncqty=0 or lmblncqty is null) and (inqty=0 or inqty is null) and (qty=0 or qty is null) and (outqty=0 or outqty is null) and (blncqty=0 or blncqty is null)');
execsql;
end;
AdoQry_Main.DisableControls;
AdoQry_Main.Close;
AdoQry_Main.SQL.clear;
AdoQry_Main.SQL.text:=
'select a.*,a.ItemCode+'' ''+b.ItemName ItemCode1,'+
' a.ite_ItemCode+'' ''+d.ItemName ite_ItemCode1 ,c.UomName UomName2 '+
' from '+Itemtmp+' a '+
' left join Item b'+
' on a.ItemCode=b.ItemCode '+
' left join Item d'+
' on a.ite_ItemCode=d.ItemCode'+
' left join Uom c '+
' on d.UomCode=c.UomCode '+
' Order by a.ItemCode_S,a.Orderfield,a.ite_ItemCode';
AdoQry_Main.Open;
AdoQry_Main.EnableControls;
if AdoQry_Main.RecordCount>0 then
Act_Print.Execute;
AdoQry_Vendor.next;
end;
DispInfo('连续打印结束!',1);
end;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -