sort.asp

医药进销存管理系统 全代码 很详细 可惜没有数据库 可供参考

<!--#include virtual="/manage/include/top.asp"-->
		 <tr bgcolor="#efefef">
		     <td height="1" colspan="4"></td>
		 </tr>
		 <tr>
		     <td height="34" colspan="2" bgcolor="efefef">		     
	             <span class="style8">　<img src="/manage/images/borrow.gif"><span class="style2"><strong> 销售排行</strong></span></span></td>
	         <td height="19" bgcolor="efefef">&nbsp;</td>
	         <td height="19" bgcolor="efefef">&nbsp;</td>
		 </tr>
		 <tr>
		     <td height="184" colspan="4" valign="top">
<%
If Session("flag")<>1 Then
  Response.Write "<script language=javascript>window.history.back();</script>"
  Response.End()
End If
Set conn=Server.CreateObject("ADODB.Connection")
conn.connectionstring="Driver={Sql Server};Server=(local);UID=sa;database=DB_lsmanage"
conn.open

set rs1=conn.execute("select * from tb_MedicineName")
%>
<script language="javascript" src="/manage/include/calendar.js"></script>
<br>
<form action="<%=request.ServerVariables("PATH_INFO")%>" method="post" name="sForm">
<table width="90%" border="0" align="center" cellpadding="4" cellspacing="1" bgcolor="#0099cc">
    <tr>
        <td bgcolor="#FFFFFF" align="center">
	        条件搜索：&nbsp;
	      <%
		    strStartDate = request("strStartDate")
			strEndDate = request("strEndDate")
			if len(strStartDate) = 0 or len(strEndDate) =0  then
			    strStartDate = year(date) &"-"&right("0"&month(date),2) &"-"& right("0"&day(date),2)
				strEndDate = strStartDate
			end if
		  %>
	       <input type="text" name="strStartDate" value="<%=strStartDate%>" size=10 readonly maxlength=6 onclick="ftn_calendar_compare('sForm','strStartDate','strStartDate','strEndDate')" ID="Text2">
              &nbsp;~&nbsp;
           <input type=text name="strEndDate" value="<%=strEndDate%>" size=10 readonly maxlength=6 onclick="ftn_calendar_compare('sForm','strEndDate','strStartDate','strEndDate')" ID="Text3">
	          &nbsp;&nbsp;
	       <select name="yName">
	       <option value="">选择药品名称</option>
　　　　<%
            do while not rs1.eof
		%>
		   <option value=<%=rs1("yName")%><%if rs1("yName")=request("yName") then response.write " selected"%>><%=rs1("yName")%></option>
		<% 
		    rs1.movenext
		    loop
        %>
		    </select>
		    <select name="type1">
	            <option value="Sl" selected>按销售数量排序</option>
		        <option value="Price">按销售总额排序</option>
	        </select>
	   &nbsp;<input type="submit" value="搜　索" name="b1"style="border:1px solid;border-color:#4c99d4;background:#f2ebc3">	 </td>
     </tr>
</table>
</form><br>
<%
if request.ServerVariables("CONTENT_LENGTH")>0 then
    sqlstr="where itType=2 "
    if len(request("strStartDate"))=0 then
        sqlstr=sqlstr&"and Time1 >= '"&date()&" 00:00:01' "
    else
        sqlstr=sqlstr&"and Time1 >= '"&request("strStartDate")&" 00:00:01' "
    end if
    if len(request("strEndDate"))=0 then
        sqlstr=sqlstr&"and Time1 <= '"&date()&" 23:59:59' "
    else
        sqlstr=sqlstr&"and Time1 <= '"&request("strEndDate")&" 23:59:59' "
    end if
   
    if len(request("yName"))=0 or request("yName")="" then
         sqlstr=sqlstr&" "
    else
	    sqlstr=sqlstr&" and yName='"&request("yName")&"' "
   end if
   if request("type1")="Sl" then
       str=" Sl "
   elseif request("type1")="Price" then
       str=" TotalPrice "
   end if
%>

<table width="79%" border="0" align="center" cellpadding="4" cellspacing="1" bgcolor="#0099cc">
   <tr>
        <td width="7%" bgcolor="#f5f5f5"><div align="center">排名</div></td><td width="27%" bgcolor="#f5f5f5"><div align="center">连锁店名称</div></td><td width="30%" bgcolor="#f5f5f5"><div align="center">药品名称</div></td><td width="17%" bgcolor="#f5f5f5"><div align="center">销售数量</div></td><td width="19%" bgcolor="#f5f5f5"><div align="center">合计</div></td>
   </tr>
<%
set rs=conn.execute("select yName,sum(Sl) AS Sl,sum(TotalPrice) AS TotalPrice,UserId from tb_Sell "&sqlstr&" group by UserId,yName order by "&str&"desc")
i=1
do while not rs.eof
    set rs2=conn.execute("select UserName from tb_User where UserId="&rs("UserId"))
%>
   <tr>
       <td  bgcolor="#ffffff"><div align="center"><%=i%></div></td><td bgcolor="#ffffff"><div align="center"><%=rs2("UserName")%></div></td><td bgcolor="#ffffff"><div align="center"><%=rs("yName")%></div></td><td bgcolor="#ffffff"><div align="center"><%=rs("Sl")%></div></td><td bgcolor="#ffffff"><div align="center"><%=rs("TotalPrice")%>元</div></td>
   </tr>
<%
rs.movenext
i=i+1
loop
%>
</table>
<br><br><center><strong><%=strStartDate%>~<%=strEndDate%> 的销售额总排行</strong></center><br>
<table width="49%" border="0" align="center" cellpadding="4" cellspacing="1" bgcolor="#0099cc">
    <tr>
	    <td width="24%" bgcolor="#F5F5F5"><div align="center" style="font-weight: bold">排名</div></td><td width="49%" bgcolor="#F5F5F5"><div align="center"><b>连锁店名称</b></div></td><td width="27%" bgcolor="#F5F5F5"><div align="center" style="font-weight: bold">销售额</div></td>
	</tr>
<%
set rs3=conn.execute("select sum(TotalPrice) as TotalPrice ,UserId from tb_Sell where Time1>='"&strStartDate&"' and Time1<='"&strEndDate&"' and itType=2 group by UserId order by TotalPrice desc")
i=1
do while not rs3.eof
  set rs2=conn.execute("select UserName from tb_User where UserId="&rs3("UserId"))
%>
    <tr>
	    <td bgcolor="#FFFFFF" align="center"><%=i%></td><td align="center" bgcolor="#FFFFFF"><%=rs2("UserName")%></td><td align="center" bgcolor="#FFFFFF"><%=rs3("TotalPrice")%>元</td>
	</tr>
<%
rs3.movenext
i=i+1
loop
%>
</table><br><br>
<%
end if
conn.close
set conn=nothing
%>
<!--#include virtual="/manage/include/bottom.asp"-->