📄 inv_monthsum1.pas
字号:
procedure TFrm_Inv_MonthSum1.Caculate;
var
SQL_Txt,SQL_Txt1:string;
begin
Application.ProcessMessages;
lbl_Status.Caption:='正在计算本厂物料结存数据......';
lbl_Status.Refresh;
Application.ProcessMessages;
SQL_Txt:='Update InvMonthSum '+
'set InvBlncQty=TempInv.qty,'+
'InvBlncAmount=TempInv.Amount,'+
//'InvLMPrice=TempInv.SPPrice,'+
'InvInPrice=TempInv.SPPrice,'+
'InvOutPrice=TempInv.SPPrice,'+
'InvBlncPrice=TempInv.SPPrice ,'+
'InvInAmount= TempInv.InvInAmount,'+
'InvOutAmount=TempInv. InvOutAmount '+
'From '+
'(Select InvLMQty+InvInQty-InvOutQty as qty,'+
'Convert(decimal(12,2),(InvLMQty+InvInQty-InvOutQty)*isnull(sp.Price,0)) as Amount,'+
'Convert(decimal(12,2),InvInQty*isnull(sp.Price,0)) as InvInAmount,'+
'Convert(decimal(12,2),InvOutQty*isnull(sp.Price,0)) as InvOutAmount,'+
'isnull(sp.Price,0) as SPPrice,'+
'InvMonth,'+
'WHCode,'+
'InvMonthSum.ItemCode '+
'from InvMonthSum left join Sc_PriceMaster sp on InvMonthSum.ItemCode=sp.ItemCode '+
'where InvMonth='+
' '''+lbl_ShowMonth.Caption+''') TempInv '+
'where TempInv.InvMonth=InvMonthSum.InvMonth and '+
'TempInv.WHCode=InvMonthSum.WHCode and '+
'TempInv.ItemCode=InvMonthSum.ItemCode ';
with AdoCommand1 do
begin
//产生本厂仓库本月结存发出数量、金额,并且计算平均价格
CommandText:=SQL_Txt;
Prepared;
Execute;
{//-------------------------------------------------------------------zks 2002.10.25----------
//按标准成本价
SQL_Txt:='Update InvMonthSum '+
'set InvBlncQty=TempInv.qty,'+
'InvBlncAmount=TempInv.Amount,'+
'InvLMPrice=TempInv.LMPrice,'+
'InvInPrice=TempInv.InPrice,'+
'InvOutPrice=TempInv.OutPrice,'+
'InvBlncPrice=case TempInv.qty when 0 then 0 else TempInv.Amount/TempInv.qty end '+
'From '+
'(Select InvLMQty+InvInQty-InvOutQty as qty,'+
'Convert(decimal(12,2),InvLMAmount+InvInAmount-InvOutAmount) as Amount,'+
'case Invlmqty when 0 then 0 else InvLMAmount/InvLMqty end as LMPrice,'+
'case InvInqty when 0 then 0 else InvInAmount/InvInqty end as InPrice,'+
'case Invoutqty when 0 then 0 else InvOutAmount/InvOutqty end as OutPrice,'+
'InvMonth,'+
'WHCode,'+
'ItemCode '+
'from InvMonthSum '+
'where InvMonth='+
' '''+lbl_ShowMonth.Caption+''') TempInv '+
'where TempInv.InvMonth=InvMonthSum.InvMonth and '+
'TempInv.WHCode=InvMonthSum.WHCode and '+
'TempInv.ItemCode=InvMonthSum.ItemCode ';
CommandText:=SQL_Txt;
Prepared;
Execute;
//*------------------------------------------------------------------------------------*}
{//产生委外材料本月收入数量、金额
Application.ProcessMessages;
lbl_Status.Caption:='正在计算委外加工材料收入方汇总数据......';
lbl_Status.Refresh;
Application.ProcessMessages;
sql_txt:='Insert into OpMonthSum '+
'(InvMonth,'+
' VendorCode,'+
' ItemCode) '+
'Select distinct aa.InvBillMonth,'+
'aa.VendorCode,'+
'aa.ItemCode '
+' from (select I.InvBillMonth,I.VendorCode,IB.ItemCode'
+' from InvOutBill I,InvOutBillline IB'
+' where I.InvBillMonth='''+lbl_ShowMonth.Caption+''' and '
+' I.InvBillid=Ib.InvBillid and'
+' (I.BillTypeCode=''0202'' or (i.BillTypeCode=''0299'' and i.opBill=1))) aa '
+' Left Join OpMonthSum OPm'
+' on OPm.InvMonth=aa.InvBillMonth and'
+' OPm.VendorCode=aa.VendorCode and'
+' OPm.ItemCode=aa.ItemCode and'
+' OPm.InvMonth='''+lbl_ShowMonth.Caption+''' '
+' where OPm.InvMonth is Null and'
+' OPm.VendorCode is Null and'
+' OPm.ItemCode is Null';
SQL_Txt1:='update OpMonthSum '+
'set InvInQty=tmp.qty,'+
'InvInAmount=tmp.notaxAmount '+
'from (select sum(IBL.InvBillQty)as qty,'+
'sum(IBL.InvBillNoTaxAmount) as notaxAmount,'+
'IB.VendorCode,'+
'IBL.ItemCode,'+
'IB.InvBillMonth '+
'from InvOutBillLine IBL,InvOutBill IB '+
'where IB.InvBillMonth='+
' '''+lbl_ShowMonth.Caption+''' and '+
'IBL.InvBillId=IB.InvBillId and '+
'(IB.BillTypeCode=''0202'' or (ib.BillTypeCode=''0299'' and ib.opBill=1)) '+
'group by IBL.ItemCode,'+
'IB.VendorCode,'+
'IB.InvBillMonth) tmp '+
'where tmp.VendorCode=OpMonthSum.VendorCode and '+
'tmp.InvBillMonth=OpMonthSum.InvMonth and '+
'tmp.ItemCode=OpMonthSum.ItemCode';
CommandText:=SQL_Txt;
Prepared;
Execute;
CommandText:=SQL_Txt1;
Prepared;
Execute;
Application.ProcessMessages;
lbl_Status.Caption:='正在计算委外加工材料发出方汇总数据......';
lbl_Status.Refresh;
Application.ProcessMessages;
//产生委外加工材料本月发出数量、金额
SQL_Txt:=' insert into OpMonthSum '+
' (InvMonth,'+
' VendorCode,'+
' ItemCode)'+
' select distinct '+
' aa.InvBillMonth,'+
' aa.VendorCode,'+
' aa.ItemCode'+
' from'+
' (select distinct o.ItemCode,'+
' I.whCode,'+
' I.WhPositionCode,'+
' I.InvBillMonth,'+
' IB.InvBillLineNO,'+
' I.VendorCode'+
' from opBill o'+
' Join InvInBill I'+
' on I.InvBillid=o.InvBillid and'+
' I.InvBillMonth='''+lbl_ShowMonth.Caption+''' and'+
' (I.BillTypeCode=''0103'' or (i.BillTypeCode=''0199'' and i.opBill=1)) '+
' Join InvInBillLine IB'+
' on IB.InvBillid=I.InvBillid and'+
' IB.InvBillLineno=o.InvBillLineno) aa'+
' Left Join OpMonthSum OM'+
' on OM.InvMonth=aa.InvBillMonth and'+
' OM.VendorCode=aa.VendorCode and'+
' OM.ItemCode=aa.ItemCode and'+
' OM.InvMonth='''+lbl_ShowMonth.Caption+''' '+
' where OM.InvMonth is Null and'+
' OM.VendorCode is Null and'+
' OM.ItemCode is Null';
SQL_Txt1:='update OpMonthSum '+
'set InvOutQty=tmp.qty,'+
'InvOutAmount=tmp.notaxAmount '+
'from ('+
'select sum(op.opBillQty)as qty,'+
'sum(op.opBillNoTaxAmount) as notaxAmount,'+
'IB.VendorCode,'+
'op.ItemCode,'+
'IB.InvBillMonth '+
'from InvInBillLine IBL,InvInBill IB,OPBill OP '+
'where IB.InvBillMonth='+
' '''+lbl_ShowMonth.Caption+''' and '+
'OP.InvBillId=IB.InvBillId and '+
'op.InvBillId=IBL.InvBillId and '+
'OP.InvBillLineNo=IBL.InvBillLineNo '+
'group by op.ItemCode,'+
'IB.VendorCode,'+
'IB.InvBillMonth) tmp '+
'where tmp.VendorCode=OpMonthSum.VendorCode and '+
'tmp.InvBillMonth=OpMonthSum.InvMonth and '+
'tmp.ItemCode=OpMonthSum.ItemCode';
CommandText:=SQL_Txt;
Prepared;
Execute;
CommandText:=SQL_Txt1;
Prepared;
Execute;
//计算产生委外加工材料收入、发出、结存价格
Application.ProcessMessages;
lbl_Status.Caption:='正在计算委外加工材料结存数据......';
lbl_Status.Refresh;
Application.ProcessMessages;
SQL_Txt:='Update OpMonthSum '+
'set InvBlncQty=TempInv.qty,'+
'InvBlncAmount=TempInv.Amount,'+
//'InvLMPrice=TempInv.SPPrice,'+
'InvInPrice=TempInv.SPPrice,'+
'InvOutPrice=TempInv.SPPrice,'+
'InvBlncPrice=TempInv.SPPrice ,'+
'InvInAmount= TempInv.InvInAmount,'+
'InvOutAmount=TempInv. InvOutAmount '+
'From '+
'(Select InvLMQty+InvInQty-InvOutQty as qty,'+
'Convert(decimal(12,2),(InvLMQty+InvInQty-InvOutQty)*isnull(sp.Price,0)) as Amount,'+
'Convert(decimal(12,2),InvInQty*isnull(sp.Price,0)) as InvInAmount,'+
'Convert(decimal(12,2),InvOutQty*isnull(sp.Price,0)) as InvOutAmount,'+
'isnull(sp.Price,0) as SPPrice,'+
'InvMonth,'+
'OpMonthSum.ItemCode '+
'from OpMonthSum left join Sc_PriceMaster sp on OpMonthSum.ItemCode=sp.ItemCode '+
'where InvMonth='+
' '''+lbl_ShowMonth.Caption+''') TempInv '+
'where TempInv.InvMonth=OpMonthSum.InvMonth and '+
'TempInv.ItemCode=OpMonthSum.ItemCode ';
CommandText:=SQL_Txt;
Prepared;
Execute;}
//计算汇总WhPMonthSum中的数据InvBlncQty
SQL_Txt:='Update WhPMonthSum'+
' set InvBlncQty=InvLMQty+InvInQty-InvOutQty '+
' where InvMonth='''+lbl_ShowMonth.Caption+''' ';
CommandText:=SQL_Txt;
Prepared;
Execute;
//更改InvStatus中记录数据发生改变的标志
SQL_Txt1:=formatdatetime('yymm',strtodate(lbl_ShowMonth.Caption+'.15'));
SQL_Txt:='Update InvStatus '+
'set InvStatus=0 '+
'where InvStatusName='+
format('''%s''',[SQL_Txt1]);
CommandText:=SQL_Txt;
Prepared;
Execute;
end;
end;
procedure TFrm_Inv_MonthSum1.FormDestroy(Sender: TObject);
begin
inherited;
Frm_Inv_MonthSum1:=nil;
end;
procedure TFrm_Inv_MonthSum1.SetDBConnect(AdOConnection: TAdOConnection);
begin
inherited;
AdoQry_Temp.Connection:=AdOConnection;
AdoQuery.Connection:=AdOConnection;
AdOCommand1.Connection:=AdOConnection;
AdoQry_Tmp.Connection:=AdOConnection;
lbl_ShowMonth.Caption:='';
lbl_Status.Caption:='';
with AdoQry_Tmp do
begin
Close;
sql.text:='select left(convert(varchAr,Invstatus),7) Invstatus from Invstatus where InvstatusName=''clsperiod''';
open;
LC_Counted_Month:=fieldbyname('Invstatus').asstring;
lbl_ShowMonth.Caption:=formatdatetime('yyyy.mm',
strtodatetime(fieldbyname('Invstatus').asstring+'.28')+20);
btn_ok.SetFocus;
end;
end;
procedure TFrm_Inv_MonthSum1.CaculatePrice(UpdateMonth:String);
var
PoNo,PoLineNo,OldPrice,ParentBillID:String;
begin
DBConnect.beginTrans;
try
lbl_Status.Caption:='正在计算'+UpdateMonth+'月出入库物料的移动加权平均价...';
Application.ProcessMessages;
//建立临时表
try
AdoQry_Tmp.Close;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -