📄 inv_monthsum2.pas
字号:
' set InvBlncPrice= case when Invblncqty=0 then 0'+
' else InvBlncAmount/Invblncqty end,'+
' InvInPrice=case when InvInqty=0 then 0'+
' else InvInAmount/InvInqty end '+
' where InvMonth='+quotedstr(CalcMonth)+
' and ItemCode='+quotedstr(tmpItemCode)+
' and InvInqty<>0 and Invblncqty<>0');
AdoQry_tmp.next;
end;
AdoCmdExecSql
('正在计算出库的金额......',
'update InvMonthSum'+
' set InvoutAmount=a.Amount'+
' from (select b.whCode,bl.ItemCode,sum(InvBillNoTaxAmount) Amount'+
' from InvOutBillline bl,InvOutBill b '+
' where bl.InvBillID=b.InvBillID '+
' and b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.BillTypeCode<>''1101'' '+
' and b.BillTypeCode not in (''0205'',''0206'') '+ // 02.11.19 by zks;
' and (b.BillTypeCode not in(''0201'',''0203'',''0204'') or b.InvBillWhChck=1) '+
' group by whCode,ItemCode) a '+
' where InvMonthSum.InvMonth='+quotedstr(CalcMonth)+
' and InvMonthSum.ItemCode=a.ItemCode and InvMonthSum.whCode=a.whCode');
AdoCmdExecSql
('正在计算出库的单价......',
'update InvMonthSum'+
' set InvoutPrice=InvoutAmount/Invoutqty where Invoutqty<>0 and InvMonth='+quotedstr(CalcMonth));
AdoCmdExecSql
('正在计算结存金额......',
'update InvMonthSum'+
' set InvBlncAmount=InvLMAmount+InvInAmount-InvOutAmount'+
' where InvMonth='+quotedstr(CalcMonth));
AdoCmdExecSql
('正在计算结存价格......',
'update InvMonthSum'+
' set InvBlncPrice=InvblncAmount/Invblncqty'+
' where Invblncqty<>0 and InvMonth='+quotedstr(CalcMonth));
//结存数量为零，金额小于0.1元的物料，其金额加到出库上
AdoCmdExecSql
('正在计算结存金额......',
'update InvMonthSum'+
' set InvblncAmount=0,InvoutAmount=InvoutAmount+InvblncAmount'+
' where Invblncqty=0 and abs(InvblncAmount)<0.1 and InvMonth='+quotedstr(CalcMonth));
//重新计算委外结存数据，将本厂的委外发出（包括负数）都算作供应商的收入
AdoCmdExecSql
('正在进行委外加工月结初始化.......',
'update OpMonthSum '+
' set InvInQty=0, '+
' InvInAmount=0,'+
' Invoutqty=0,'+
' InvoutAmount=0'+
' where InvMonth='+quotedstr(CalcMonth));
AdoCmdExecSql
('正在进行委外加工材料收入方数量、金额汇总......',
'update OpMonthSum '+
' set InvInQty=a.Qty, '+
' InvInAmount=a.Amount '+
' from '+
' (select sum(bl.InvBillQty) as qty,sum(bl.InvBillnotaxAmount) as Amount,'+
' b.VendorCode,'+
' bl.ItemCode,'+
' b.InvBillMonth '+
' from InvOutBillLine bl,InvOutBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and bl.InvBillId=b.InvBillId '+
' and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OPBill=1))'+
' group by b.InvBillMonth,b.VendorCode,bl.ItemCode) a'+
' where a.VendorCode=OpMonthSum.VendorCode'+
' and a.InvBillMonth=OpMonthSum.InvMonth'+
' and a.ItemCode=OpMonthSum.ItemCode');
//将委外加工材料结存调整单的数据加在暂时先加在收入方 wxp
//是否应该给OpMonthSum加一列（调整数据）？？以后再说
//加了2列Invtzqty,InvtzAmount
AdoCmdExecSql
('正在进行委外加工材料结存调整的数量、金额汇总......',
'Insert OpMonthSum '+
' (InvMonth,VendorCode,ItemCode) '+
' Select distinct b.opBillMonth,b.VendorCode,bl.ItemCode '+
' from opAdjustBillLine bl,opAdjustBill b '+
' where b.opBillMonth='+quotedstr(CalcMonth)+
' and bl.opBillId=b.opBillId '+
' and b.VendorCode+bl.ItemCode not in ('+
' select VendorCode+ItemCode from OpMonthSum where InvMonth='+
quotedstr(CalcMonth)+')');
AdoCmdExecSql
('正在进行委外加工材料结存调整的数量、金额汇总......',
'update OpMonthSum '+
' set InvtzQty=a.Qty, '+
' InvtzAmount=a.Amount '+
' from '+
' (select sum(bl.opBillQty) as qty,sum(bl.opBillnotaxAmount) as Amount,'+
' b.VendorCode,'+
' bl.ItemCode,'+
' b.opBillMonth '+
' from opAdjustBillLine bl,opAdjustBill b '+
' where b.opBillMonth='+quotedstr(CalcMonth)+
' and bl.opBillId=b.opBillId '+
' group by b.opBillMonth,b.VendorCode,bl.ItemCode) a'+
' where a.VendorCode=OpMonthSum.VendorCode'+
' and a.opBillMonth=OpMonthSum.InvMonth'+
' and a.ItemCode=OpMonthSum.ItemCode');
AdoCmdExecSql
('正在进行委外加工材料发出方数量、金额汇总......',
'update OpMonthSum '+
' set InvOutQty=a.Qty,'+
' InvoutAmount=a.Amount '+
' from '+
' (select sum(op.OpBillQty) as Qty,sum(op.OpBillnotaxAmount) as Amount,'+
' b.VendorCode,op.ItemCode,b.InvBillMonth '+
' from InvInBillLine bl,InvInBill b,OPBill OP '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and OP.InvBillId=b.InvBillId'+
' and op.InvBillId=bl.InvBillId'+
' and OP.InvBillLineNo=bl.InvBillLineNo '+
' group by op.ItemCode,b.VendorCode,b.InvBillMonth) a '+
' where a.VendorCode=OpMonthSum.VendorCode'+
' and a.InvBillMonth=OpMonthSum.InvMonth'+
' and a.ItemCode=OpMonthSum.ItemCode');
AdoCmdExecSql
('正在进行委外加工材料结存数量、金额的处理......',
'Update OpMonthSum '+
' set InvBlncQty=InvLMQty+InvInQty+Invtzqty-(InvOutQty), '+
' InvBlncAmount=InvLMAmount+InvInAmount+InvtzAmount-(InvOutAmount) '+
' where InvMonth='+quotedstr(CalcMonth));
//2001.12.30修改部分 MD
AdoCmdExecSql
('正在进行将委外加工材料结存数量绝对值小于1的数量增加到出方......',
'Update OpMonthSum '+
' set Invoutqty=Invoutqty+Invblncqty,Invblncqty=0 '+
' where abs(Invblncqty)<1 and InvMonth='+quotedstr(CalcMonth));
//MD EDIT 2001.12.30
AdoCmdExecSql
('正在进行委外加工材料价格的处理......',
'Update OpMonthSum '+
' set InvBlncPrice= case when Invblncqty<>0 then InvblncAmount/Invblncqty else 0 end, '+
' InvInPrice= case when InvInqty<>0 then InvInAmount/InvInqty else 0 end, '+
' InvoutPrice= case when Invoutqty<>0 then InvoutAmount/Invoutqty else 0 end '+
' where InvMonth='+quotedstr(CalcMonth));
//出库单上的含税价=未税价
AdoCmdExecSql
('正在计算出库的价格......',
'update InvOutBillline'+
' set InvBillPrice=InvBillnotaxPrice '+
' ,InvBillAmount=InvBillnotaxAmount '+
' from InvOutBill '+
' where InvOutBill.InvBillMonth='+quotedstr(CalcMonth)+
' and InvOutBill.BillTypeCode not in (''0205'',''0206'') '+
' and InvOutBill.InvBillid=InvOutBillline.InvBillid ');
end;
//重新计算下月移动加权平均价
procedure TFrm_Inv_MonthSum2.CalcNextMonthPrice;
begin
with AdoQry_Tmp do
begin
//先用处理月份的月末价初始化移动加权平均价表的数据
Close;
SQL.Text:=
'Update AveragePrice Set ApQty=0'
+' ,ApAmount=0'
+' ,AveragePrice=0';
ExecSQL;
Close;
SQL.Text:=
'Update OpAveragePrice Set OpApQty=0'
+' ,OpApAmount=0'
+' ,OpAveragePrice=0';
ExecSQL;
Close;
SQL.Text:=
'Update AveragePrice Set ApQty=InvBlncQty'
+' ,ApAmount=InvBlncAmount'
+' ,AveragePrice=InvBlncPrice'
+' From InvMonthSum'
+' Where AveragePrice.ItemCode=InvMonthSum.ItemCode'
+' And AveragePrice.WHCode=InvMonthSum.WHCode'
+' And InvMonthSum.InvMonth='''+CalcMonth+'''';
ExecSQL;
Close;
SQL.Text:=
'Update OpAveragePrice Set OpApQty=InvBlncQty'
+' ,OpApAmount=InvBlncAmount'
+' ,OpAveragePrice=InvBlncPrice'
+' From OpMonthSum'
+' Where OpAveragePrice.ItemCode=OpMonthSum.ItemCode'
+' And OpAveragePrice.VendorCode=OpMonthSum.VendorCode'
+' And OpMonthSum.InvMonth='''+CalcMonth+'''';
ExecSQL;
end;
CaculatePrice(NextMonth);
end;
//仓库数据发生改动标志复位
procedure TFrm_Inv_MonthSum2.ResetInvStatus;
begin
with AdoQry_tmp do
begin
Close;
SQL.text:='Update InvStatus '+
' set InvStatus=0 '+
' where InvStatusName='+quotedstr(copy(CalcMonth,3,2)+copy(CalcMonth,6,2));
ExecSql;
end;
end;
//委外材料费计算
procedure TFrm_Inv_MonthSum2.OPCalculate(InputItemCode: string);
var
InvBillID,InvBillLineNo:integer;
VendorCode,WhCode,SonItemCode:string;
//TmpPrice临时记录某子项价格，TmpAmount记录InputItemCode总的材料费
//BillAmount记录InputItemCode在某仓库的材料费
TmpPrice,TmpAmount,BillAmount:double;
AdoQry_OpBill,AdoQry_TmpQry:TAdoQuery;
begin
AdoQry_OpBill:=TAdoQuery.Create(nil);
AdoQry_OpBill.Connection:=AdoQry_Tmp.Connection;
AdoQry_OpBill.EnableBCD:=False;
AdoQry_TmpQry:=TAdoQuery.Create(nil);
AdoQry_TmpQry.Connection:=AdoQry_Tmp.Connection;
AdoQry_TmpQry.EnableBCD:=False;
with AdoDS_OpBill do
begin
locate('ItemCode',InputItemCode,[]);
while (uppercase(fieldbyname('ItemCode').asstring)=uppercase(InputItemCode)) and (not eof) do
begin
WhCode:=fieldbyname('WhCode').asstring;
TmpAmount:=0;
while (uppercase(fieldbyname('ItemCode').asstring)=uppercase(InputItemCode)) and
(not eof) and (uppercase(fieldbyname('WhCode').asstring)=uppercase(WhCode)) do
begin
InvBillID:=fieldbyname('InvBillID').asinteger;
InvBillLineNo:=fieldbyname('InvBillLineNo').asinteger;
Label2.Caption:='[单 据 号]'+fieldbyname('InvBillNo').AsString
+#10+#13+'[仓 库]'+fieldbyname('WhCode').AsString
+#10+#13+'[供 应 商]'+fieldbyname('VendorCode').AsString
+#10+#13+'[物料代码]'+fieldbyname('ItemCode').AsString;
Application.ProcessMessages;
VendorCode:=fieldbyname('VendorCode').asstring;
BillAmount:=0;
AdoQry_OpBill.Close;
AdoQry_OpBill.sql.text:=
'select * from OPBill o'+
' where o.InvBillId='+fieldbyname('InvBillID').asstring+
' and o.InvBillLineNo='+fieldbyname('InvBillLineNo').asstring;
AdoQry_OpBill.open;
//遍历该单据该产品的所有子项
while not AdoQry_OpBill.eof do
begin
SonItemCode:=AdoQry_OpBill.fieldbyname('ItemCode').asstring;
//如果子项是委外产品
if locate('ItemCode',SonItemCode,[]) then
begin
AdoQry_TmpQry.Close;
//是否已经计算过
AdoQry_TmpQry.sql.text:=
'select MArk from OpMonthSum'+
' where VendorCode='+quotedstr(VendorCode)+
' and ItemCode='+quotedstr(SonItemCode)+
' and InvMonth='+quotedstr(CalcMonth);
AdoQry_TmpQry.open;
//还没有计算过
if AdoQry_TmpQry.fieldbyname('MArk').asstring<>'1' then
begin
//递归调用
OPCalculate(SonItemCode);
end;
end;
locate('InvBillId;InvBillLineNo',varArrayOf([InvBillId,InvBillLineNo]),[]);
TmpPrice:=CalculateOPPrice(SonItemCode,VendorCode);
AdoQry_TmpQry.Close;
AdoQry_TmpQry.sql.text:=
'update OpBill'+
' set OpBillNoTaxPrice='+floattostr(TmpPrice)+','+
' OPBillNoTaxAmount=OPBillQty*'+floattostr(TmpPrice)+
' where InvBillID='+fieldbyname('InvBillID').asstring+
' and InvBillLineNo='+fieldbyname('InvBillLineNo').asstring+
' and ItemCode='+quotedstr(SonItemCode);
AdoQry_TmpQry.execsql;
TmpAmount:=TmpAmount+strtofloat(formatfloat('#0.00',AdoQry_OpBill.fieldbyname('OpBillQty').asfloat*TmpPrice));
BillAmount:=BillAmount+strtofloat(formatfloat('#0.00',AdoQry_OpBill.fieldbyname('OpBillQty').asfloat*TmpPrice));
AdoQry_OpBill.next;
end;
//子项全部计算完毕
//替换入库单据的材料费
AdoQry_TmpQry.Close;
if fieldbyname('BillTypeCode').asstring='1202' then
AdoQry_TmpQry.sql.text:=
'update InvInBillLine'+
' set InvBillNoTaxAmount='+floattostr(BillAmount)+
' where InvBillId='+fieldbyname('InvBillID').asstring+
' and InvBillLineNo='+fieldbyname('InvBillLineNo').asstring
else
AdoQry_TmpQry.sql.text:=
'update InvInBillLine'+
' set InvBillNoTaxAmount='+floattostr(BillAmount)+
' where InvBillId='+inttostr(fieldbyname('InvBillID').asinteger+1)+
' and InvBillLineNo='+fieldbyname('InvBillLineNo').asstring;
AdoQry_TmpQry.execsql;
edit;
fieldbyname('flag').asinteger:=1;
post;
//下一行单据
next;
end;
//当前仓库已经计算完毕
//重新计算InvMonthSum中该物料该仓库的记录
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -