📄 inv_monthsum.pas
字号:
SQL_Txt:='update InvInBillLine '+
'set InvBillNoTaxPrice=SP.SPPrice,'+
'InvBillNoTaxAmount='+
'Convert(decimal(12,2),InvBillQty*SP.SPPrice),'+
'InvBillPrice=SP.SPPrice,'+
'InvBillAmount='+
'Convert(decimal(12,2),InvBillQty*SP.SPPrice) '+
'from InvInBill B,StandardPrice SP,Warehouse W '+
'where B.InvBillMonth='+
format('''%s''',[lbl_ShowMonth.Caption])+' and '+
'B.InvBillId=InvInBillLine.InvBillId and '+
'B.WHCode=W.WHCode and '+
'W.PriceType=1 and '+
'SP.SPStArtMonth='+
format('''%s''',[lbl_ShowMonth.Caption])+' and '+
'SP.ItemCode=InvInBillLine.ItemCode ';
SQL_Txt1:='update InvOutBillLine '+
'set InvBillNoTaxPrice=SP.SPPrice,'+
'InvBillNoTaxAmount='+
'Convert(decimal(12,2),InvBillQty*SP.SPPrice),'+
'InvBillPrice=SP.SPPrice,'+
'InvBillAmount='+
'Convert(decimal(12,2),InvBillQty*SP.SPPrice) '+
'from InvOutBill B,StandardPrice SP,Warehouse W '+
'where B.InvBillMonth='+
format('''%s''',[lbl_ShowMonth.Caption])+' and '+
'B.InvBillId=InvOutBillLine.InvBillId and '+
'B.WHCode=W.WHCode and '+
'W.PriceType=1 and '+
'SP.SPStArtMonth='+
format('''%s''',[lbl_ShowMonth.Caption])+' and '+
'SP.ItemCode=InvOutBillLine.ItemCode ';
{+' '+
//按标准成本价核算出库的数据( Add by ZKS 2002.10.25)
'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
// 替换InvInBillLine、InvOutBillLine中的计价方式
CommandText:=SQL_Txt;
Prepared;
Execute;
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'' '
+' (I.BillTypeCode not in(''0104'',''0105'') or i.InvBillwhchck=1)) aa '
//+' 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 InvInQty=tmp.qty,'+
'InvInAmount=tmp.notaxAmount '+
'from (select sum(IBL.InvBillQty)as qty,'+
'sum(IBL.InvBillNoTaxAmount) as notaxAmount,'+
'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)) '+
//' IB.InvBillWHChck=1 '+
'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 ';
//添加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'
//+' 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) '+
//' IB.InvBillWHChck=1 '+
'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(''0201'',''0203'',''0204'') or i.InvBillwhchck=1)) aa'
//+' 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 (''0201'',''0203'',''0204'') or '+
'IB.InvBillWHChck=1) '+
//' IB.InvBillWHChck=1 '+
'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(''0201'',''0203'',''0204'') or i.InvBillwhchck=1)) aa '
//+' 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 (''0201'',''0203'',''0204'') or '+
'IB.InvBillWHChck=1) '+
//' IB.InvBillWHChck=1 '+
'group by IBL.ItemCode,'+
'IB.WhCode,'+
//'IBL.ItemCode,'+
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -