📄 inv_monthsum1.pas
字号:
'from OpMonthSum'+
' where InvMonth='+
format('''%s''',[LC_Counted_Month])+
' and (InvBlncQty<>0 or InvBlncAmount<>0)';
CommandText:=SQL_Txt2;
Prepared;
Execute;
end;
end;
procedure TFrm_Inv_MonthSum1.Convert;
var
SQL_Txt,SQL_Txt1:string;
begin
Application.ProcessMessages;
lbl_Status.Caption:='正在处理按标准成本价核算仓库的数据......';
lbl_Status.Refresh;
Application.ProcessMessages;
{//按标准成本价核算出库的数据( Add by ZKS 2002.10.25)
SQL_Txt1:='update InvOutBillLine '+
'set InvBillNoTaxPrice=SP.Price,'+
'InvBillNoTaxAmount='+
'Convert(decimal(12,2),InvBillQty*SP.Price),'+
'InvBillPrice=SP.Price,'+
'InvBillAmount='+
'Convert(decimal(12,2),InvBillQty*SP.Price) '+
'from InvOutBill B,Sc_PriceMaster SP,Warehouse W '+
'where B.InvBillMonth='+
format('''%s''',[lbl_ShowMonth.Caption])+' and '+
'B.InvBillId=InvOutBillLine.InvBillId and '+
'B.WHCode=W.WHCode and '+
'W.PriceType=3 and '+
'SP.ItemCode=InvOutBillLine.ItemCode ';}
with AdOCommand1 do
begin
{ CommandText:=SQL_Txt1;
Prepared;
Execute;}
Application.ProcessMessages;
lbl_Status.Caption:='正在计算本厂物料收入汇总数据......';
lbl_Status.Refresh;
Application.ProcessMessages;
sql_txt:='Insert into InvMonthSum (InvMonth'
+',whCode'
+',ItemCode)'
+' select distinct aa.InvBillMonth,'
+' aa.whCode,'
+' aa.ItemCode'
+' from (select I.InvBillMonth,I.whCode,IB.ItemCode'
+' from InvInBill I,InvInBillline IB'
+' where I.InvBillMonth='''+lbl_ShowMonth.Caption+''' and '
+' I.InvBillid=Ib.InvBillid and'
//+' i.BillTypeCode <> ''1101'' and '
+' (I.BillTypeCode not in(''0104'',''0105'') or i.InvBillwhchck=1)) aa '
+' Left Join InvMonthSum IM'
+' on IM.InvMonth=aa.InvBillMonth and'
+' IM.whCode=aa.whCode and'
+' IM.ItemCode=aa.ItemCode and'
+' IM.InvMonth='''+lbl_ShowMonth.Caption+''' '
+' where IM.InvMonth is Null and'
+' IM.whCode is Null and'
+' IM.ItemCode is Null';
//更新InvMonthSum中本月进入数据，从InvInBill，InvInBillLINE中按仓库，货位，物料；
// 汇总处理月份收入数量，不含税金额（=数量*标准价）
sql_txt1:='update InvMonthSum '+
'set InvInQty=tmp.qty,'+
'InvInAmount=tmp.notaxAmount '+
'from (select sum(tmp1.InvBillQty) as qty, '+
' sum(tmp1.InvBillQty*isnull(sp.Price,0)) as notaxAmount,'+
' tmp1.WHCode,'+
' tmp1.ItemCode,'+
' tmp1.InvBillMonth '+
'from '+
'(select IBL.InvBillQty , '+
' IB.WHCode,'+
' IBL.ItemCode,'+
' IB.InvBillMonth '+
'from InvInBillLine IBL,InvInBill IB '+
'where IB.InvBillMonth='+
' '''+lbl_ShowMonth.Caption+''' and '+
'IBL.InvBillId=IB.InvBillId and '+
//'ib.BillTypeCode <> ''1101'' and '+
'((IB.BillTypeCode not in (''0104'',''0105'') or '+
'IB.InvBillWHChck<>0)) '+
' ) tmp1'+
' left join Sc_PriceMaster sp on tmp1.ItemCode=sp.ItemCode'+
' group by tmp1.ItemCode,tmp1.WhCode,tmp1.InvBillMonth) tmp '+
'where tmp.whCode=InvMonthSum.whCode and '+
'tmp.InvBillMonth=InvMonthSum.InvMonth and '+
'tmp.ItemCode=InvMonthSum.ItemCode ';
//添加InvInBill的数据
CommandText:=SQL_Txt;
Prepared;
Execute;
CommandText:=SQL_Txt1;
Prepared;
Execute;
sql_txt:='Insert into whPMonthSum (InvMonth'
+',whCode'
+',ItemCode'
+',WhPositionCode)'
+' select distinct aa.InvBillMonth,'
+' aa.whCode,'
+' aa.ItemCode,'
+' aa.WhPositionCode'
+' from (select I.InvBillMonth,I.whCode,IB.ItemCode,I.WhPositionCode'
+' from InvInBill I,InvInBillline IB'
+' where I.InvBillMonth='''+lbl_ShowMonth.Caption+''' and '
+' I.InvBillid=Ib.InvBillid and'
+' (I.BillTypeCode not in(''0104'',''0105'') or i.InvBillwhchck=1)) aa'
+' Left Join whPMonthSum IM'
+' on IM.InvMonth=aa.InvBillMonth and'
+' IM.whCode=aa.whCode and'
+' IM.ItemCode=aa.ItemCode and'
+' IM.WhPositionCode=aa.WhPositionCode and'
+' IM.InvMonth='''+lbl_ShowMonth.Caption+''' '
+' where IM.InvMonth is Null and'
+' IM.whCode is Null and'
+' IM.ItemCode is Null and '
+' IM.WhPositionCode is Null';
sql_txt1:='update WhPMonthSum '+
'set InvInQty=tmp.qty '+
'from (select distinct sum(IBL.InvBillQty)as qty,'+
'IB.WHCode,'+
'IBL.ItemCode,'+
'IB.WhPositionCode,'+
'IB.InvBillMonth '+
'from InvInBillLine IBL,InvInBill IB '+
'where IB.InvBillMonth='+
' '''+lbl_ShowMonth.Caption+''' and '+
'IBL.InvBillId=IB.InvBillId and '+
'(IB.BillTypeCode not in (''0104'',''0105'') or '+
'IB.InvBillWHChck<>0) '+
'group by IBL.ItemCode,'+
'IB.WhCode,'+
'IBL.ItemCode,'+
'IB.WhPositionCode,'+
'IB.InvBillMonth) tmp '+
'where tmp.whCode=whPMonthSum.whCode and '+
'tmp.InvBillMonth=whPMonthSum.InvMonth and '+
'tmp.ItemCode=whPMonthSum.ItemCode and '+
'tmp.WhPositionCode=whPMonthSum.WhPositionCode';
//更新whPMonthSum中的InvInBillline值
CommandText:=SQL_Txt;
Prepared;
Execute;
CommandText:=SQL_Txt1;
Prepared;
Execute;
Application.ProcessMessages;
lbl_Status.Caption:='正在计算本厂物料发出汇总数据......';
lbl_Status.Refresh;
Application.ProcessMessages;
//添加InvOutBill的数据
sql_txt:='Insert into InvMonthSum '
+'(InvMonth,'
+'whCode,'
+'ItemCode) '
+' select distinct aa.InvBillMonth,'
+' aa.whCode,'
+' aa.ItemCode'
+' from (select I.InvBillMonth,I.whCode,IB.ItemCode'
+' from InvOutBill I,InvOutBillline IB'
+' where I.InvBillMonth='''+lbl_ShowMonth.Caption+''' and '
+' I.InvBillid=Ib.InvBillid and'
//+' i.BillTypeCode<>''1101'' and'
+' i.BillTypeCode not in (''0205'',''0206'') ' // 02.11.19 by zks;
+' and (I.BillTypeCode not in(''0201'',''0203'',''0204'') or i.InvBillwhchck=1)) aa'
+' Left Join InvMonthSum IM'
+' on IM.InvMonth=aa.InvBillMonth and'
+' IM.whCode=aa.whCode and'
+' IM.ItemCode=aa.ItemCode and'
+' IM.InvMonth='''+lbl_ShowMonth.Caption+''' '
+' where IM.InvMonth is Null and'
+' IM.whCode is Null and'
+' IM.ItemCode is Null';
sql_txt1:='update InvMonthSum '+
'set InvOutQty=tmp.qty,'+
'InvOutAmount=tmp.notaxAmount '+
'from (select sum(IBL.InvBillQty) as qty,'+
'sum(IBL.InvBillNoTaxAmount) as notaxAmount,'+
'IB.WHCode,'+
'IBL.ItemCode,'+
'IB.InvBillMonth '+
'from InvOutBillLine IBL,InvOutBill IB '+
'where IB.InvBillMonth='+
' '''+lbl_ShowMonth.Caption+''' and '+
'IBL.InvBillId=IB.InvBillId and '+
//'ib.BillTypeCode<>''1101'' and '+
' Ib.BillTypeCode not in (''0205'',''0206'') '+ // 02.11.19 by zks;
' and (IB.BillTypeCode not in (''0201'',''0203'',''0204'') or '+
' IB.InvBillWHChck<>0) '+
'group by IBL.ItemCode,'+
'IB.WhCode,'+
'IBL.ItemCode,'+
'IB.InvBillMonth) tmp '+
'where tmp.whCode=InvMonthSum.whCode and '+
'tmp.InvBillMonth=InvMonthSum.InvMonth and '+
'tmp.ItemCode=InvMonthSum.ItemCode ';
CommandText:=SQL_Txt;
Prepared;
Execute;
CommandText:=SQL_Txt1;
Prepared;
Execute;
//更新whPMonthSum中的InvOutBillline中的值
sql_txt:='Insert into whPMonthSum '
+'(InvMonth,'
+ 'whCode,'
+'WhPositionCode,'
+'ItemCode) '
+' select distinct aa.InvBillMonth,'
+' aa.whCode,'
+' aa.WhPositionCode,'
+' aa.ItemCode'
+' from (select I.InvBillMonth,I.whCode,IB.ItemCode,I.WhPositionCode'
+' from InvOutBill I,InvOutBillline IB'
+' where I.InvBillMonth='''+lbl_ShowMonth.Caption+''' and '
+' I.InvBillid=Ib.InvBillid and'
+' i.BillTypeCode not in (''0205'',''0206'') ' // 02.11.19 by zks;
+' and (I.BillTypeCode not in(''0201'',''0203'',''0204'') or i.InvBillwhchck=1)) aa '
+' Left Join whPMonthSum IM'
+' on IM.InvMonth=aa.InvBillMonth and'
+' IM.whCode=aa.whCode and'
+' IM.ItemCode=aa.ItemCode and'
+' IM.WhPositionCode=aa.WhPositionCode and'
+' IM.InvMonth='''+lbl_ShowMonth.Caption+''' '
+' where IM.InvMonth is Null and'
+' IM.whCode is Null and'
+' IM.ItemCode is Null and'
+' IM.WhPositionCode is Null';
sql_txt1:='update whPMonthSum '+
'set InvOutQty=tmp.qty '+
'from (select sum(IBL.InvBillQty) as qty,'+
'IB.WHCode,'+
'IBL.ItemCode,'+
'IB.WhPositionCode,'+
'IB.InvBillMonth '+
'from InvOutBillLine IBL,InvOutBill IB '+
'where IB.InvBillMonth='+
' '''+lbl_ShowMonth.Caption+''' and '+
'IBL.InvBillId=IB.InvBillId and '+
' IB.BillTypeCode not in (''0205'',''0206'') and ' +// 02.11.19 by zks;
'(IB.BillTypeCode not in (''0201'',''0203'',''0204'') or '+
'IB.InvBillWHChck<>0) '+
'group by IBL.ItemCode,'+
'IB.WhCode,'+
'IBL.ItemCode,'+
'IB.WhPositionCode,'+
'IB.InvBillMonth) tmp '+
'where tmp.whCode=whPMonthSum.whCode and '+
'tmp.InvBillMonth=whPMonthSum.InvMonth and '+
'tmp.ItemCode=whPMonthSum.ItemCode and '+
'tmp.WhPositionCode=whPMonthSum.WhPositionCode';
CommandText:=SQL_Txt;
Execute;
CommandText:=SQL_Txt1;
Execute;
end;
end;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -