📄 inv_monthsum2.pas
字号:
//将材料费加进收入方，并重新计算收入价格
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update InvMonthSum'+
' set InvInAmount=InvInAmount+'+floattostr(TmpAmount)+','+
' InvInPrice=case when InvInQty=0 then 0 else (InvInAmount+'+floattostr(TmpAmount)+')/InvInQty end'+
' where InvMonth='+quotedstr(CalcMonth)+
' and WhCode='+quotedstr(WhCode)+
' and ItemCode='+quotedstr(InputItemCode);
AdoQry_TmpQry.execsql;
//重新计算月平均价
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'select case when InvLMQty+InvInQty=0 then InvOutPrice'+
' else (InvLMAmount+InvInAmount)/(InvLMQty+InvInQty) end Price'+
' from InvMonthSum s'+
' where s.InvMonth='+quotedstr(CalcMonth)+
' and s.WhCode='+quotedstr(WhCode)+
' and s.ItemCode='+quotedstr(InputItemCode);
AdoQry_TmpQry.open;
//新的月平均价
TmpPrice:=AdoQry_TmpQry.fieldbyname('Price').asfloat;
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update InvMonthSum'+
' set InvMonthSum.InvOutAmount=InvMonthSum.InvOutQty*'+floattostr(TmpPrice)+','+
' InvBlncPrice='+floattostr(TmpPrice)+
' from InvMonthSum'+
' where InvMonthSum.InvMonth='+quotedstr(CalcMonth)+
' and InvMonthSum.WhCode='+quotedstr(WhCode)+
' and InvMonthSum.ItemCode='+quotedstr(InputItemCode);
AdoQry_TmpQry.execsql;
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update InvMonthSum'+
' set InvBlncAmount=InvLMAmount+InvInAmount-InvOutAmount,'+
' InvOutPrice=InvBlncPrice'+
' where InvMonth='+quotedstr(CalcMonth)+
' and WhCode='+quotedstr(WhCode)+
' and ItemCode='+quotedstr(InputItemCode);
AdoQry_TmpQry.execsql;
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update InvMonthSum'+
' set InvBlncPrice=InvBlncAmount/InvBlncQty'+
' where InvMonth='+quotedstr(CalcMonth)+
' and WhCode='+quotedstr(WhCode)+
' and ItemCode='+quotedstr(InputItemCode+
' and InvBlncQty<>0 and InvBlncPrice=0');
AdoQry_TmpQry.execsql;
//wxp 加and bl.InvBillqty>0
//记录旧价格时，委外出库的总金额
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'select b.VendorCode,sum(bl.InvBillNoTaxAmount) Amount'+
' into #TmpOpOutBill'+
' from InvOutBill b,InvOutBillLine bl'+
' where b.InvBillID=bl.InvBillID and bl.InvBillqty>0 '+
' and b.InvBillMonth='+quotedstr(CalcMonth)+
' and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OPBill=1))'+
' and bl.ItemCode='+quotedstr(InputItemCode)+
' and b.WhCode='+quotedstr(WhCode)+
' group by b.VendorCode';
AdoQry_TmpQry.execsql;
//替换出库单据的价格
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update InvOutBillLine'+
' set InvBillNoTaxPrice='+floattostr(TmpPrice)+','+
' InvBillNoTaxAmount=InvBillQty*'+floattostr(TmpPrice)+
' from InvOutBill b,Warehouse w'+
' where InvOutBillLine.InvBillID=b.InvBillID '+
' and b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.BillTypeCode<>''1101'''+
' and b.BillTypeCode not in (''0205'',''0206'') '+
' and b.WhCode=w.WhCode'+
' and b.AmountAdjust=0 '+
' and w.PriceType=0'+
' and InvOutBillLine.ItemCode='+quotedstr(InputItemCode)+
' and b.WhCode='+quotedstr(WhCode);
AdoQry_TmpQry.execsql;
//更改OpMonthSum的收入价格、金额，注意要减旧加新
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update OpMonthSum '+
' set InvInAmount=InvInAmount-(o.Amount)+n.Amount,'+
' InvInPrice=case when InvInQty=0 then 0 else (InvInAmount-o.Amount+n.Amount)/InvInQty end '+
' from'+
' (select sum(bl.InvBillNoTaxAmount) as Amount,'+
' b.VendorCode'+
' from InvOutBillLine bl,InvOutBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and bl.InvBillId=b.InvBillId and bl.InvBillqty>0'+
' and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OPBill=1))'+
' and bl.ItemCode='+quotedstr(InputItemCode)+
' and b.WhCode='+quotedstr(WhCode)+
' group by b.VendorCode) n,#TmpOpOutBill o'+
' where n.VendorCode=OpMonthSum.VendorCode'+
' and o.VendorCode=OpMonthSum.VendorCode'+
' and OpMonthSum.InvMonth='+quotedstr(CalcMonth)+
' and OpMonthSum.ItemCode='+quotedstr(InputItemCode);
AdoQry_TmpQry.execsql;
//计算委外材料的月平均价
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'select s.VendorCode,case when InvLMQty+InvInQty=0 then InvOutPrice'+
' else (InvLMAmount+InvInAmount)/(InvLMQty+InvInQty) end Price'+
' into #tmpopPrice from OpMonthSum s,#TmpOpOutBill o '+
' where s.InvMonth='+quotedstr(CalcMonth)+
' and s.VendorCode=o.VendorCode '+
' and s.ItemCode='+quotedstr(InputItemCode);
AdoQry_TmpQry.execsql;
//用供应商的月平均价替换退料单据价格
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update InvOutBillLine '+
' set InvBillNoTaxPrice=t.Price,'+
' InvBillNoTaxAmount=InvBillQty*t.Price '+
' from InvOutBill b,#tmpopPrice t,Warehouse w '+
' where InvOutBillLine.InvBillID=b.InvBillID '+
' and InvOutBillLine.InvBillqty<0'+
' and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OPBill=1 and b.AmountAdjust=0))'+
' and b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.WhCode=w.WhCode'+
' and b.AmountAdjust=0 '+
' and w.PriceType=0'+
' and InvOutBillLine.ItemCode='+quotedstr(InputItemCode)+
' and t.VendorCode=b.VendorCode';
AdoQry_TmpQry.execsql;
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'drop table #TmpOpOutBill,#tmpopPrice';
try
AdoQry_TmpQry.execsql;
except
end;
end;
end;
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'select Count(*) s'+
' from OpMonthSum'+
' where InvMonth='+quotedstr(CalcMonth)+
' and ItemCode='+quotedstr(InputItemCode);
AdoQry_TmpQry.open;
if AdoQry_TmpQry.fieldbyname('s').asinteger>0 then
CalculateOPPrice(InputItemCode,'');
AdoQry_TmpQry.Close;
AdoQry_TmpQry.free;
AdoQry_OpBill.Close;
AdoQry_OpBill.free;
end;
//供应商平均价计算
function TFrm_Inv_MonthSum2.CalculateOpPrice(InputItemCode,
InputVendorCode: string):double;
var
AdoQry_TmpQry:TAdoQuery;
ConditionStr:string;
HasResult:boolean;
begin
HasResult:=False;
AdoQry_TmpQry:=TAdoQuery.Create(nil);
with AdoQry_TmpQry do
begin
Connection:=AdoQry_Tmp.Connection;
EnableBCD:=False;
if InputVendorCode<>'' then
begin
Close;
sql.text:=
'select MArk,InvOutPrice'+
' from OpMonthSum'+
' where InvMonth='+quotedstr(CalcMonth)+
' and VendorCode='+quotedstr(InputVendorCode)+
' and ItemCode='+quotedstr(InputItemCode);
open;
if fieldbyname('MArk').asstring='1' then
begin
Result:=fieldbyname('InvOutPrice').asfloat;
HasResult:=True;
end;
ConditionStr:=' and VendorCode='+quotedstr(InputVendorCode);
end
else
ConditionStr:='';
if not HasResult then
begin
Close;
sql.text:=
'update OpMonthSum'+
' set InvOutPrice=case when InvLMQty+InvInQty=0 then 0 else (InvLMAmount+InvInAmount)/(InvLMQty+InvInQty) end,'+
' mArk=1'+
' where InvMonth='+quotedstr(CalcMonth)+
' and ItemCode='+quotedstr(InputItemCode)+
ConditionStr;
execsql;
Close;
sql.text:=
'update OpMonthSum'+
' set InvBlncPrice=InvOutPrice,'+
' InvOutAmount=InvOutQty*InvOutPrice,'+
' InvBlncAmount=InvLMAmount+InvInAmount-InvOutQty*InvOutPrice'+
' where InvMonth='+quotedstr(CalcMonth)+
' and ItemCode='+quotedstr(InputItemCode)+
ConditionStr;
execsql;
if InputVendorCode<>'' then
begin
Close;
sql.text:=
'select InvOutPrice'+
' from OpMonthSum'+
' where InvMonth='+quotedstr(CalcMonth)+
' and VendorCode='+quotedstr(InputVendorCode)+
' and ItemCode='+quotedstr(InputItemCode);
open;
Result:=fieldbyname('InvOutPrice').asfloat;
end;
end;
end;
AdoQry_TmpQry.free;
end;
procedure TFrm_Inv_MonthSum2.AdoCmdExecSql(DispStr,SqlStr:string);
begin
with AdOCommand1 do
begin
Application.ProcessMessages;
lbl_Status.Caption:=DispStr;
lbl_Status.Refresh;
Application.ProcessMessages;
commandtext:=SqlStr;
if SqlStr<>'' then Execute;
end;
end;
procedure TFrm_Inv_MonthSum2.CaculatePrice(UpdateMonth:String);
var
PoNo,PoLineNo,OldPrice,ParentBillID:String;
begin
DBConnect.beginTrans;
try
lbl_Status.Caption:='正在计算'+UpdateMonth+'月出入库物料的移动加权平均价...';
Application.ProcessMessages;
//建立临时表
try
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Drop Table #CloseBook';
AdoQry_Tmp.ExecSQL;
except
end;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Create Table #CloseBook ('
+' [WHCode] [varchAr] (4) Null'
+',[InvBillCreateTime] [datetime] Null'
+',[InvBillId] [numeric](18, 0) Null'
+',[BillTypeCode] [varchAr] (4) Null'
+',[InvBillNo] [varchAr] (10) Null'
+',[VendorCode] [varchAr] (12) Null'
+',[OpBill] [int] Null'
+' )';
AdoQry_Tmp.ExecSQL;
//插入入库数据
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #CloseBook'
+' Select InvInBill.WHCode'
+',InvInBill.InvBillCreateTime'
+',InvInBill.InvBillId'
+',InvInBill.BillTypeCode'
+',InvInBill.InvBillNo'
+',InvInBill.VendorCode'
+',InvInBill.OpBill'
+' From InvInBill'
+' Join Warehouse On InvInBill.WHCode=Warehouse.WHCode'
+' Where InvInBill.InvBillMonth='''+UpdateMonth+''''
+' And Warehouse.PriceType=0'
+' And InvInBill.BillTypeCode In (''0101'',''0102'',''0199'',''0103'''
+',''1201'',''1202'')';
AdoQry_Tmp.ExecSQL;
//插入出库数据
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #CloseBook'
+' Select InvOutBill.WHCode'
+',InvOutBill.InvBillCreateTime'
+',InvOutBill.InvBillId'
+',InvOutBill.BillTypeCode'
+',InvOutBill.InvBillNo'
+',InvOutBill.VendorCode'
+',InvOutBill.OpBill'
+' From InvOutBill'
+' Join Warehouse On InvOutBill.WHCode=Warehouse.WHCode'
+' Where InvOutBill.InvBillMonth='''+UpdateMonth+''''
+' And Warehouse.PriceType=0'
+' And InvOutBill.BillTypeCode not In (''0205'',''0206'')' // 02.11.19 by zks;
+' And ((InvOutBill.BillTypeCode In (''0202'',''0299''))'
+' Or ((InvOutBill.BillTypeCode In (''0201'',''0203'',''0204''))'
+' And (InvOutBill.InvBillWHchck=1)))';
AdoQry_Tmp.ExecSQL;
{}
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #CloseBook'
+' Select InvOutBill.WHCode'
+',InvOutBill.InvBillCreateTime'
+',InvOutBill.InvBillId'
+',InvOutBill.BillTypeCode'
+',InvOutBill.InvBillNo'
+',InvOutBill.VendorCode'
+',InvOutBill.OpBill'
+' Fr⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -