28,391
社区成员
发帖
与我相关
我的任务
分享
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!--#include file="../../common/public_function.asp" -->
<!--#include file="../../common/menuinc.asp"-->
<%title=selectLanguage_Fun(user_default_language,"stock-account","账面库存明细表")
dim a(20)%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link rel="stylesheet" href="../css/style1.css" type="text/css">
<title><%=title%></title>
<style type="text/css">
<!--
body {
background-color: #dcdbdb;
margin-top: 0px;
}
.style3 {color: #ffffff; font-weight: bold; }
.style4 {
color: #0099ff;
font-weight: bold;
font-size: 18px;
}
-->
</style></head>
<body>
<table width="90%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#cccccc">
<tr bgcolor="#ffffff">
<td colspan="8"><div align="center"><img src="../imagers/xtreme_logo.gif" width="70" height="71"> <span class="style4">账面库存明细表</span></div></td>
</tr>
</table>
<table width="90%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#cccccc">
<tr bgcolor="000">
<td width="10%"><span class="style3">序号</span></td>
<td width="16%"><span class="style3">单据号</span></td>
<td width="10%"><span class="style3">业务类型</span></td>
<td width="11%"><span class="style3">开单日期</span></td>
<td width="13%"><span class="style3">单位</span></td>
<td width="13%"><span class="style3">收入</span></td>
<td width="12%"><span class="style3">发出</span></td>
<td width="6%"><span class="style3">结存</span></td>
<td width="9%"> </td>
</tr>
<%i=1
sql="select a.period,a.inventory_item_id,sum(a.banlance)as total,b.prod_name_cn,b.prod_name_en,b.id_talk_code from stocks a inner join [020_product_main] b on a.inventory_item_id=b.id and a.period=convert(char(6),dateadd(m,-1,getdate()),112) group by a.period,a.inventory_item_id,b.prod_name_cn,b.prod_name_en,b.id_talk_code"
set rs=server.CreateObject("adodb.recordset")
rs.open sql,conn,1,1
y=0
do while not rs.eof%>
<tr <%if y mod 2=0 then
response.Write("bgcolor='#efefef'")
else
response.Write("bgcolor='#ffffff'")
end if%>>
<td><%=i%></td>
<td>料号:<%=rs("id_talk_code")%></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr <%if y mod 2=0 then
response.Write("bgcolor='#ffffff'")
else
response.Write("bgcolor='#efefef'")
end if%>>
<td> </td>
<td>期初结存</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td><%=rs("total")%></td>
<td></td>
</tr>
<%m=0
set rs1=server.CreateObject("adodb.recordset")
'sql="select a.inventory_item_id,a.transaction_action_id,a.transaction_quantity,a.create_date,(case transaction_action_id when 3 then '入库单'when 1 then '出库单' when 6 then '报损单'end )as receipt_type ,b.prod_name_cn,b.prod_name_en,b.id_talk_code from mtl_material_transactions a inner join [020_product_main] b on a.inventory_item_id=b.id and convert(char(6),create_date,112)=convert(char(6),getdate(),112) and a.inventory_item_id="&rs("inventory_item_id")
sql="select a.inventory_item_id,a.transaction_action_id,a.create_date,a.transaction_quantity,b.rec_no as receipt_type,b.buy_count from mtl_material_transactions a inner join to_depot b on a.inventory_item_id=b.inventory_item_id and a.transaction_action_id=3 and convert(char(6),a.create_date,112)=convert(char(6),getdate(),112) and convert(char(6),b.create_date,112)=convert(char(6),getdate(),112) and a.inventory_item_id="&rs("inventory_item_id")&""
sql=sql &"union all"
sql=sql &" select c.inventory_item_id,c.transaction_action_id,c.create_date,c.transaction_quantity, d.outlist as receipt_type,d.total from mtl_material_transactions c inner join (select m.inventory_item_id,m.total,m.out_id,n.outlist,n.out_date from out_depot_lines_all m inner join out_depot n on m.out_id=n.out_id and convert(char(6),n.create_date,112)=convert(char(6),getdate(),112)) d on c.inventory_item_id=d.inventory_item_id and c.transaction_action_id=1 and convert(char(6),c.create_date,112)=convert(char(6),getdate(),112) and convert(char(6),d.out_date,112)=convert(char(6),getdate(),112) and c.inventory_item_id="&rs("inventory_item_id")&""
sql=sql &"union all"
sql=sql&" select e.inventory_item_id,e.transaction_action_id,e.create_date,e.transaction_quantity,f.damage_no as receipt_type,f.damage_qty from mtl_material_transactions e inner join product_damage f on e.inventory_item_id=f.inventory_item_id and e.transaction_action_id=6 and convert(char(6),e.create_date,112)=convert(char(6),getdate(),112) and convert(char(6),f.create_date,112)=convert(char(6),getdate(),112) and e.inventory_item_id="&rs("inventory_item_id")&""
rs1.open sql,conn,1,1
do while not rs1.eof%>
<tr <%if m mod 2=0 then
response.Write("bgcolor='#efefff'")
else
response.Write("bgcolor='#ffffff'")
end if%>>
<td></td>
<td><%=rs1("receipt_type")%></td>
<td><%if rs1("transaction_action_id")=3 then%>入库单<%elseif rs1("transaction_action_id")=1 then%>出库单<%elseif rs1("transaction_action_id")=6 then%>报损单<%end if%></td>
<td><%=rs1("create_date")%></td>
<td> </td>
<td><%if rs1("transaction_action_id")=3 then%><%=rs1("transaction_quantity")%><%end if%></td>
<td><%if rs1("transaction_action_id")=1 or rs1("transaction_action_id")=6 then%><%=rs1("transaction_quantity")%><%end if%></td>
<td><%if rs1("transaction_action_id")=3 then%><%=rs1("transaction_quantity")+rs("total")%><%else%><%a(1)=rs("total")-rs1("transaction_quantity")%><%=a(1)%><%end if%></td>
<td> </td>
</tr>
<%m=m+1
rs1.movenext
loop
rs1.close
set rs1=nothing
i=i+1
y=y+1
rs.movenext
loop%>
</table>
</body>
</html>
'以下新增,用于计算每一个明细行的结存数量
1、 sqltemp="select top "&m+1&replace(sql,"select a.inventory_item_id,a.transaction_action_id,a.create_date,a.transaction_quantity,b.rec_no as receipt_type,b.buy_count","")
2、 sqltemp="select case transaction_action_id when '报损单' then -1*sum(transaction_quantity) when '领退料单' then -1*sum(transaction_quantity) when '采购入库' then sum(transaction_quantity) end as Expr1 from "&sqltemp&" group by transaction_action_id)"
3、 sqltemp="select sum(Expr1) from "&sqltemp
rstemp.open sqltemp,conn,1,1
4、 rowTotal=rs("total")+rstemp(0)
'新增完毕
......
<%m=0
set rs1=server.CreateObject("adodb.recordset")
'sql="select a.inventory_item_id,a.transaction_action_id,a.transaction_quantity,a.create_date,(case transaction_action_id when 3 then '入库单'when 1 then '出库单' when 6 then '报损单'end )as receipt_type ,b.prod_name_cn,b.prod_name_en,b.id_talk_code from mtl_material_transactions a inner join [020_product_main] b on a.inventory_item_id=b.id and convert(char(6),create_date,112)=convert(char(6),getdate(),112) and a.inventory_item_id="&rs("inventory_item_id")
sql="select a.inventory_item_id,a.transaction_action_id,a.create_date,a.transaction_quantity,b.rec_no as receipt_type,b.buy_count from mtl_material_transactions a inner join to_depot b on a.inventory_item_id=b.inventory_item_id and a.transaction_action_id=3 and convert(char(6),a.create_date,112)=convert(char(6),getdate(),112) and convert(char(6),b.create_date,112)=convert(char(6),getdate(),112) and a.inventory_item_id="&rs("inventory_item_id")&""
sql=sql &"union all"
sql=sql &" select c.inventory_item_id,c.transaction_action_id,c.create_date,c.transaction_quantity, d.outlist as receipt_type,d.total from mtl_material_transactions c inner join (select m.inventory_item_id,m.total,m.out_id,n.outlist,n.out_date from out_depot_lines_all m inner join out_depot n on m.out_id=n.out_id and convert(char(6),n.create_date,112)=convert(char(6),getdate(),112)) d on c.inventory_item_id=d.inventory_item_id and c.transaction_action_id=1 and convert(char(6),c.create_date,112)=convert(char(6),getdate(),112) and convert(char(6),d.out_date,112)=convert(char(6),getdate(),112) and c.inventory_item_id="&rs("inventory_item_id")&""
sql=sql &"union all"
sql=sql&" select e.inventory_item_id,e.transaction_action_id,e.create_date,e.transaction_quantity,f.damage_no as receipt_type,f.damage_qty from mtl_material_transactions e inner join product_damage f on e.inventory_item_id=f.inventory_item_id and e.transaction_action_id=6 and convert(char(6),e.create_date,112)=convert(char(6),getdate(),112) and convert(char(6),f.create_date,112)=convert(char(6),getdate(),112) and e.inventory_item_id="&rs("inventory_item_id")&""
'以下新增,用于计算每一个明细行的结存数量
sqltemp="select top "&m+1&replace(sql,"select a.inventory_item_id,a.transaction_action_id,a.create_date,a.transaction_quantity,b.rec_no as receipt_type,b.buy_count","")
sqltemp="select case transaction_action_id when '报损单' then -1*sum(transaction_quantity) when '领退料单' then -1*sum(transaction_quantity) when '采购入库' then sum(transaction_quantity) end as Expr1 from "&sqltemp&" group by transaction_action_id)"
sqltemp="select sum(Expr1) from "&sqltemp
rstemp.open sqltemp,conn,1,1
rowTotal=rs("total")+rstemp(0)
'新增完毕
rs1.open sql,conn,1,1
do while not rs1.eof%>
......