存储过程|记录集|临时表|示例
| 以下是ASP代码： <%LANGUAGE="VBSCRIPT" CODEPAGE="936"%> <!--#include file="../conn.asp"--> <!--#include file="adovbs.inc"--> <!--#include file="../inc/check_login.asp"--> <!--#include file="pagecls.asp"--> <%CheckUserPower "100009",Session("EmpPower")%> <% dim deptname,empname,fyear deptname=Trim(Request("deptname")) empname=Trim(Request("empname")) fyear = Trim(Request("fyear")) if fyear = "" then fyear = 2006 end if Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") conn.open connstr ’if deptname = "" and empname ="" then ’sql = "HD_CK_XSHZ "&fyear&"" ’conn.execute(sql) ’end if %> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=gb2312"> <title>销售出库表.</title> <link href="../style.css" rel="stylesheet" type="text/css"> <script language="javascript"> function exportExcel(hd_zlb){ if (typeof(EXPORT_OBJECT)!="object"){ document.body.insertAdjacentHTML("afterBegin","<OBJECT } with (EXPORT_OBJECT){ DataType = "HTMLData"; HTMLData =hd_zlb.outerHTML; try{ ActiveSheet.Export("C:\\华达销售出库一览表.xls", 0); alert(’成功导出EXCEL表格！’); } catch (e){ alert(’导出EXCEL表格失败，请确定已安装Excel2000(或更高版本),并且没打开同名xls文件’); } } } </script> </head> <body> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <form name="form1" method="post" action="list_loading.asp"> <tr> <td><select name="fyear"> <option value="2005" <%if fyear=2005 then response.write"selected"%>>2005年</option> <option value="2006" <%if fyear=2006 then response.write"selected"%>>2006年</option> <option value="2007" <%if fyear=2007 then response.write"selected"%>>2007年</option> </select> <!--#include file="dept_cls.asp"--> <input type="submit" name="Submit" value="查询"></td> <td> <input type="hidden" name="filename" value="list_sale_out.asp"> </td> <td> <div align="right"> <input name="button" type="button" value="导出EXCEL表格..."> </div></td> </tr> </form> </table> <% function formatcur(cur,v) if v=0 then if cur=0 then formatcur="<font color=’#cccccc’>-</font>" else v9=cur&"" va=len(v9) if va<4 then v2=v9 else yi=Fix(va/3) y2=(yi+1)*3 t1=3-(y2-va)+1 if yi<>va/3 then e2="" for i=1 to yi v2=","&Mid(v9,t1+3*(i-1),3) e2=e2+v2 next v2=Mid(v9,1,(va-3*yi))&e2 else e2="" for y=2 to yi v2=","&Mid(v9,(y-1)*3+1,3) e2=e2+v2 next v2=Mid(v9,1,3)&e2 end if end if formatcur=v2 end if else if cur=0 then formatcur="<font color=’#cccccc’>-</font>" else formatcur=FormatCurrency(cur) end if end if end function response.write fyear Set Cmd=server.CreateObject("Adodb.Command") Cmd.ActiveConnection=conn Cmd.CommandText="HD_CK_XSHZ" Cmd.CommandType=4 cmd.prepared=true Cmd.Parameters.Append Cmd.CreateParameter("fyear",adInteger,1,2,fyear) Cmd.Parameters.Append Cmd.CreateParameter("deptname",adVarChar,adParamInput,50,""&deptname&"") Cmd.Parameters.Append Cmd.CreateParameter("empname",adVarChar,adParamInput,50,""&empname&"") set rs=Cmd.execute if deptname<>"" then response.write "<font color=red>你查询的 "&fyear&"年度 "&deptname&" >> "&empname&" 的相关销售出库信息如下:</font>" %> <table width="100%" border="0" cellpadding="2" cellspacing="1" bgcolor="#999999" id="hd_zlb" <tr> <td bgcolor="#666666"><font color="#FFFFFF">ID</font></td> <td bgcolor="#666666"><font color="#FFFFFF" >公司名称</font></td> <%if deptname="" then%> <td bgcolor="#666666"><font color="#FFFFFF">销售部门</font></td> <%end if if empname="" then%> <td bgcolor="#666666"><font color="#FFFFFF">业务员</font></td> <%end if%> <td nowrap bgcolor="#666666"><font color="#FFFFFF">合计</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">1月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">2月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">3月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">4月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">5月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">6月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">7月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">8月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">9月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">10月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">11月</font></td> <td nowrap bgcolor="#666666"><font color="#FFFFFF">12月</font></td> </tr> <% dim s,totals dim m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12 m1=0 m2=0 m3=0 m4=0 m5=0 m6=0 m7=0 m8=0 m9=0 m10=0 m11=0 m12=0 totals=0 s=0 while not rs.eof m1=m1+rs("m1") m2=m2+rs("m2") m3=m3+rs("m3") m4=m4+rs("m4") m5=m5+rs("m5") m6=m6+rs("m6") m7=m7+rs("m7") m8=m8+rs("m8") m9=m9+rs("m9") m10=m10+rs("m10") m11=m11+rs("m11") m12=m12+rs("m12") totals=totals+rs("total") if instr(FormatSQLstr(session("deptpower")),trim(rs("fdeptname")))>0 then %> <tr bgcolor="#FFFFFF"> <td><nobr><%=s%></nobr></td> <td><nobr><%=rs("fcompanyname")%></nobr></td> <%if deptname="" then%> <td nowrap><nobr><%=rs("fdeptname")%></nobr></td> <%end if if empname="" then%> <td nowrap><nobr><%=rs("fempname")%></nobr></td> <%end if%> <td nowrap bgcolor="#efefef"> <div align="right"><font color="#FF0000"><nobr><%=formatcur(rs("total"),0)%></nobr></font></div></td> <td nowrap><div align="right"><nobr><%=formatcur(rs("m1"),0)%></nobr></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m2"),0)%></nobr> </div></td> <td nowrap><div align="right"><nobr><%=formatcur(rs("m3"),0)%></nobr> </div></td> <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m4"),0)%></nobr> </div></td> <td nowrap><div align="right"><nobr><%=formatcur(rs("m5"),0)%></nobr></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m6"),0)%></nobr> </div></td> <td nowrap><div align="right"><nobr><%=formatcur(rs("m7"),0)%></nobr> </div></td> <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m8"),0)%></nobr> </div></td> <td nowrap><div align="right"><nobr><%=formatcur(rs("m9"),0)%></nobr></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m10"),0)%></nobr> </div></td> <td nowrap><div align="right"><nobr><%=formatcur(rs("m11"),0)%></nobr> </div></td> <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m12"),0)%></nobr> </div></td> </tr> <% end if rs.movenext s=s+1 wend rs.close set rs = nothing conn.close set conn=nothing Set Cmd = Nothing %> <tr bgcolor="#FFFFFF"> <td colspan="2"><strong>合计:</strong></td> <%if deptname="" then%> <td></td> <%end if if empname="" then%> <td></td> <%end if%> <td nowrap bgcolor="#efefef"> <div align="right"><font color="#FF0000"><%=formatcur(totals,0)%></font></div></td> <td nowrap> <div align="right"><%=formatcur(m1,0)%></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m2,0)%></div></td> <td nowrap> <div align="right"><%=formatcur(m3,0)%></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m4,0)%></div></td> <td nowrap> <div align="right"><%=formatcur(m5,0)%></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m6,0)%></div></td> <td nowrap> <div align="right"><%=formatcur(m7,0)%></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m8,0)%></div></td> <td nowrap> <div align="right"><%=formatcur(m9,0)%></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m10,0)%></div></td> <td nowrap> <div align="right"><%=formatcur(m11,0)%></div></td> <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m12,0)%></div></td> </tr> </table> </body> </html> |
| 以下是存储过程代码： CREATE PROCEDURE HD_CK_XSHZ ( fyear int, deptname varchar(50), empname varchar(50) ) AS set nocount on begin --按月份、型号汇总，建立销售出库单表 create table #ck1(fyear int,fperiod int,fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),famtref numeric) insert into #ck1 select l.fyear,l.fperiod,l.fcompanyname,l.fdeptname,fempname,sum(l.fsumamtref) from vw_com_saleinout l where l.fyear=fyear and (l.fcompanyname<> ’其它客户’) group by l.fcompanyname,l.fdeptname,l.fempname,l.fyear,l.fperiod order by fcompanyname,fperiod --按月份建立临时表 create table #month1(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m1 int) insert into #month1 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=1 create table #month2(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m2 int) insert into #month2 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=2 create table #month3(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m3 int) insert into #month3 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=3 create table #month4(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m4 int) insert into #month4 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=4 create table #month5(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m5 int) insert into #month5 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=5 create table #month6(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m6 int) insert into #month6 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=6 create table #month7(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m7 int) insert into #month7 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=7 create table #month8(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m8 int) insert into #month8 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=8 create table #month9(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m9 int) insert into #month9 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=9 create table #month10(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m10 int) insert into #month10 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=10 create table #month11(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m11 int) insert into #month11 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=11 create table #month12(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m12 int) insert into #month12 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=12 --建立商品销售明细一览表 create table #hd_ckhzj(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),Total int, m1 int,m2 int,m3 int,m4 int,m5 int,m6 int,m7 int,m8 int,m9 int,m10 int,m11 int,m12 int) insert into #hd_ckhzj select #ck1.fcompanyname,#ck1.fdeptname,#ck1.fempname,sum(#ck1.famtref), isnull((select m1 from #month1 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m2 from #month2 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m3 from #month3 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m4 from #month4 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m5 from #month5 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m6 from #month6 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m7 from #month7 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m8 from #month8 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m9 from #month9 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m10 from #month10 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m11 from #month11 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0), isnull((select m12 from #month12 where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0) from #ck1 group by fcompanyname,fdeptname,fempname order by fcompanyname if (deptname<>’’ and empname=’’) select * from #hd_ckhzj where fdeptname = deptname order by total desc else if (deptname<>’’ and empname<>’’) select * from #hd_ckhzj where fdeptname = deptname and fempname = empname order by total desc else select * from #hd_ckhzj order by total desc end GO |
