ASP 动态汇总车间检验数据详细代码_asp月度数据汇总-优快云博客

本文链接：https://blog.youkuaiyun.com/klxtx1234/article/details/129284672

这是一个使用ASP编写的车间检验数据报表系统，具备按年、月、部门筛选，产品、系列、制单号分组汇总，以及FPY计算等功能。系统通过SQL语句进行数据汇总，支持自定义日期和排除特定拉线。用户可以选择查看或导出为Excel格式，具有灵活的筛选和汇总选项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前年给车间写了一个ASP版的车间检验数据报表，分享给大家最终的汇总页代码。

汇总功能如下：

1. 可选年，月，部门：实时刷新进入，默认按月汇总

2.可进行产品名称，系列，制单号进行分组汇总

3.汇总方向：分为CASE-MOD-FG, FG-MOD-CASE

4.在进行月汇总时可增加每月每周汇总

5，可自定义日期时间段汇总

6，可单独进行某个产品，系列，制单号进行汇总

7，可排除某条拉线不参与汇总

以下为ASP代码，类似的便略了，因为不良项目每个类型每个车间不一样，采用的是动态获取进行汇总，表头也是动态获取，每条记录均进行FPY计算，整页进行FPY平均计算。

此项目就用到一个不良项目表，一个原始数据表（前端手机录入），其他所有的均用SQL汇总语句，不整多的表，方便以后维护。

现在还有多少人在玩ASP？

<%
if yhbgdept<>"" then dept=yhbgdept
a2="xxxxx"
if request.form("ss") <>"" then    a2= request.form("ss") 
a6=request.form("xz")
a7=request.form("xz2")
a8=request.form("xz3")
a9=request.form("xz4")
if a9="" then a9="月"
if a8="" or a8="a" then 
a8="a"
c1="CASE"
c2="FG"
END IF
IF a8="b" then 
c2="CASE"
c1="FG"
end if
if a7="a" or a7="" then a7="产品名称"		
if a7="b"  then a7="供应商"	
if a7="c"  then a7="制单号"
a71=a7
if dept<>"2B" and a7="供应商" THEN a71= "系列" 
year =cint(Request.QueryString("year"))
month =cint(Request.QueryString("month"))
 if month=0 then month=month(date()) end if 
 if year=0 then year=year(date()) end if 
dr=year&"年"&month&"月"
date1=request.form("date1") 
date2=request.form("date2") 
if date1<>"" and date2<>"" then
 DR=date1&"至"&date2&"汇总"

 end if
xzla9=chr(9)&"周" 
 if a9="月" then xzla9=""
'CASE判断开始
set rrs=server.createobject("adodb.recordset")
sql="SELECT * FROM 不良项目  where 工序段='case'  and  部门='"&dept&"' order by IDD  "
rrs.open sql,conn,1,1
CASEQTY=rrs.recordcount
for i=1 to rrs.recordcount
			if rrs.bof or rrs.eof then exit for 
		blxmbC=blxmbC&"<td style='width:3px;'>"&rrs("不良项目")&"</td> "
		 rscase=rscase&",sum(h"&i&") as c"&i
		 xzl=xzl&rrs("不良项目")& chr(9) 
		rrs.movenext	
next 
rrs.close
set rrs=nothing	
'MOD判断开始
此处省略参考同上
set rrs=nothing	
'FG判断开始
此处省略参考同上

%>
<BODY >

<div style="font-size: 25px;color: #0073AE;"><b>
<select name="year" style="width:auto;border:0px solid white;color: #0073AE;font-size: 25px;" onChange="document.location.href=this.value;" >
<%for y=2020 to year(date())%>
<option  value="?year=<%=y%>&month=<%=month%>&q=<%=dept%>" <%if y=year then %>selected="selected"<%end if %>><%=y%></option>
<% next%>
</select>年
<select name="month" style="width:auto;border:0px solid white;color: #0073AE;font-size: 25px;" onChange="document.location.href=this.value;" >
<%for z=1 to 12%>
<option  value="?year=<%=year%>&month=<%=z%>&q=<%=dept%>" <%if z=month then %>selected="selected"<%end if %>><%=z%></option>
<% next%>
</select>月
<select name="q" tyle="width:auto;border:0px solid white;color: #0073AE;font-size: 25px;" onChange="document.location.href=this.value;"  style="width:auto;border:0px solid white;color: #0073AE;font-size: 25px;">
<option value="?q=XXX&month=<%=month%>&year=<%=year%> "   <%if dept="XXX" then %>selected="selected"<%end if %>>XXX</option>

</select>
</b></div><br>
<form  action="" method="post" >
导出:<span><input type="radio" checked  name="xz" value="1" >浏览</span>
<span><input type="radio" <%if a6=2 then %>checked <%end if %> name="xz" value="2" >导出EXCEL</span><br>
筛选:<span><input type="radio" <%if a7="产品名称" then %>checked <%end if %>  name="xz2" value="a" >产品名称</span>
<span><input type="radio" <%if a7="供应商"  then %>checked <%end if %> name="xz2" value="b" >系列</span>
<span><input type="radio" <%if a7="制单号"  then %>checked <%end if %> name="xz2" value="c" >制单号</span>
<br>
方向:<span><input type="radio" <%if a8="a" then %>checked <%end if %>  name="xz3" value="a" >CASE-FG</span>
<span><input type="radio" <%if a8="b"  then %>checked <%end if %> name="xz3" value="b" >FG-CASE</span>
<br>
周月:<span><input type="radio" <%if a9="月" then %>checked <%end if %>  name="xz4" value="月" >月</span>
<span><input type="radio" <%if a9="周"  then %>checked <%end if %> name="xz4" value="周" >周</span>

<br>

日期:<input type="date" name="date1" value="<%=date1%>"  >至<input type="date" name="date2" value="<%=date2%>"  ><br>
产品名称/系列/制单号：<input type="text" name="cpmc" value=<%=request.form("cpmc")%>>

<input type="submit" value="提交"></form>
注意：选择了两个日期后结果仅汇总该日期区间，年月选择项目自动失效。
<!--startprint-->
<div align="center" style="position:relative;z-index:-1;" id="prnnn">
<div class="pr" align="left"><img  width="20%"  src='logo.jpg'></div>
<div align="center" style="font-size: 25px;color: #0073AE;">
<b><%=dept&" "&DR%> FPY报表</b>
</div>
<form  action="" name="myform" method="post" >	
<table style="font-size: 16px;width:100%">
	<thead>
	<tr style="font-weight:bold;font-align:center;">
<td rowspan="2"  align="center" size="5" >序号</td><td rowspan="2"  align="center" >月</td><% if a9="周" then %>
<td rowspan="2"  align="center" >周</td>
<% end if%>
<td rowspan="2"  align="center" ><%=a71%></td>
<td colspan="3" align="center" ><%=c1%></td><td colspan="3" align="center" >MOD</td><td colspan="3" align="center" ><%=c2%></td><td rowspan="2" align="center" >FPY</td>
<td colspan="<%=caseqty%>" align="center" >CASE不良明细</td><td colspan="<%=modqty%>" align="center" >MOD不良明细</td><td colspan="<%=fgqty%>" align="center" >FG不良明细</td>

</TR>
<tr style="font-weight:bold;align:center;">
<td align="center" >检查数</td><td align="center" >合格数</td><td align="center" >合格率</td><td align="center" >检查数</td><td align="center" >合格数</td><td align="center" >合格率</td><td align="center" >检查数</td><td align="center" >合格数</td><td align="center" >合格率</td><%=blxmbC&blxmbM&blxmbF%>
	</tr>
	</thead>
	<tbody id="table_content">
	<%shijian=" and 月='"&month&"' and 年='"&year&"'"	
if date1<>"" and date2 <>""  then 	shijian=" and 日期 >='"&date1&"' and 日期 <='"&date2&"'"			
	if 	request.form("cpmc") <>"" then shijian=shijian & "  and "&a7&"='"&request.form("cpmc")&"'"	
		set rs=server.createobject("adodb.recordset") 
SQL="SELECT A.*,B.*,C.*,round(CF*(case  WHEN MF IS null then 100 ELSE mf END)*(case  WHEN ff IS null then 100 ELSE ff END )/10000,2) as fpy,D.*,E.*,F.* FROM (SELECT 年,月,周,"&a7&",sum(检查数) as CASET,sum(合格数) as CASEH, (case when  sum(检查数)=0 then 100 else  Convert(decimal(18,2),cast(100*sum(合格数) AS FLOAT)/sum(检查数)  ) end ) as CF  FROM [RF].[dbo].[验货报告每日记录明细] where 部门='"&dept&"' and 类别='case' "&shijian&"  GROUP BY 年,月,周,"&a7&" ) AS A LEFT OUTER JOIN (SELECT 年,月,周,"&a7&",sum(检查数) as MODT,sum(合格数) as MODH, (case when  sum(检查数)=0 then 100 else  Convert(decimal(18,2),cast(100*sum(合格数) AS FLOAT)/sum(检查数)  ) end ) as MF   FROM [RF].[dbo].[验货报告每日记录明细] where 部门='"&dept&"' and 类别='MOD'  "&shijian&"  GROUP BY 年,月,周,"&a7&") AS B ON A.年=B.年 and A.月=B.月 and A.周=B.周 and A."&a7&"=B."&a7&"  LEFT OUTER JOIN (SELECT 年,月,周,"&a7&",sum(检查数) as FGT,sum(合格数) as FGH, (case when  sum(检查数)=0 then 100 else  Convert(decimal(18,2),cast(100*sum(合格数) AS FLOAT)/sum(检查数)  ) end ) as FF  FROM [RF].[dbo].[验货报告每日记录明细] where 部门='"&dept&"' and 类别='FG'  "&shijian&"  GROUP BY 年,月,周,"&a7&") AS C ON A.年=C.年 and A.月=C.月 and A.周=C.周 and A."&a7&"=C."&a7&" LEFT OUTER JOIN (SELECT 年,月,周,"&a7&rscase&"  FROM [RF].[dbo].[验货报告每日记录明细] where 部门='"&dept&"' and 类别='case'  "&shijian&"  GROUP BY 年,月,周,"&a7&") AS D ON A.年=D.年 and A.月=D.月 and A.周=D.周 and A."&a7&"=D."&a7&" 	LEFT OUTER JOIN (SELECT 年,月,周,"&a7&rsmod&"  FROM [RF].[dbo].[验货报告每日记录明细] where 部门='"&dept&"' and 类别='mod'  "&shijian&"  GROUP BY 年,月,周,"&a7&") AS E ON A.年=E.年 and A.月=E.月 and A.周=E.周 and A."&a7&"=E."&a7&" LEFT OUTER JOIN (SELECT 年,月,周,"&a7&rsfg&"  FROM [RF].[dbo].[验货报告每日记录明细] where 部门='"&dept&"' and 类别='fg'  "&shijian&"  GROUP BY 年,月,周,"&a7&") AS F ON A.年=F.年 and A.月=F.月 and A.周=F.周 and A."&a7&"=F."&a7&""
if a9="月" then 
此处省略参考同上，默认有月和周，选月后代码去掉周

end if

IF a8="b" then 
此处省略参考同上，这里是方向，将方向反过来即可
if a9="月" then 
此处省略参考同上，这里是方向，将方向反过来即可
end if
end if	
	rs.open SQL,conn,1,1
	if rs.eof or rs.pagecount = 0 then 
		response.write("无记录")
	else
		rs.PageSize=50 
pagecount=rs.PageCount '获取总页码 
page=int(request("page")) '接收页码 
if page<=0 then page=1 '判断 
if request("page")="" then page=1 
rs.AbsolutePage=page '设置本页页码 	
		avgfpy=0
		iend=rs.PageSize
		if rs.recordcount<iend then iend=rs.recordcount
		for i=1 to iend
			if rs.bof or rs.eof then exit for		
				
		if a9="月" then 
		response.write "<tr class='plist'><td>"&i&"</td><td>"&rs(1)&"</td><td>"&rs(2)&"</td><td>"&rs(3)&"</td><td>"&rs(4)&"</td><td>"&rs(5)&"</td><td>"&rs(9)&"</td><td>"&rs(10)&"</td><td>"&rs(11)&"</td><td>"&rs(15)&"</td><td>"&rs(16)&"</td><td>"&rs(17)&"</td><td>"&rs(18)&"</td>"
		avgfpy=avgfpy+round(rs(18),2)
		end if
		if a9="周" then
		 response.write  "<tr class='plist'><td>"&i&"</td><td>"&rs(1)&"</td><td>"&rs(2)&"</td><td>"&rs(3)&"</td><td>"&rs(4)&"</td><td>"&rs(5)&"</td><td>"&rs(6)&"</td><td>"&rs(11)&"</td><td>"&rs(12)&"</td><td>"&rs(13)&"</td><td>"&rs(18)&"</td><td>"&rs(19)&"</td><td>"&rs(20)&"</td><td>"&rs(21)&"</td>"
		avgfpy=avgfpy+round(rs(21),2)
		end if
			for casei = 1 to caseqty
			response.write "<td>"&rs("c"&casei)&"</td>"
			next
			for casei = 1 to modqty
			response.write "<td>"&rs("m"&casei)&"</td>"
			next
			for casei = 1 to fgqty
			response.write "<td>"&rs("f"&casei)&"</td>"
			next
			response.write "</tr>"
			rs.movenext	
		next 
		avgfpy=round(avgfpy/iend,2)
		response.write  "<div align='left'>当前页平均 FPY："&avgfpy&"%</div>"
		end if
	rs.close
	set rs=nothing	
	%>

	</tbody></table>
<div class="pr" align="right"> </div>	
</div>	
	<!--endprint-->
<%
Response.Write "<p align=""center""><input type=""button"" name=""print"" value=""打印"""
Response.Write " onclick=""preview()"" id=""pt"">&nbsp;&nbsp;<input type=""button"" "
Response.Write "name=""print"" value=""返 回"" onclick=""location.href='';"" id=""pt""></p>"
%>
<script language="javascript">
function preview() 
{ 
bdhtml=window.document.body.innerHTML; 
sprnstr="<!--startprint-->"; 
eprnstr="<!--endprint-->"; 
prnhtml=bdhtml.substr(bdhtml.indexOf(sprnstr)+17); 
prnhtml=prnhtml.substring(0,prnhtml.indexOf(eprnstr)); 
window.document.body.innerHTML=document.getElementById("prnnn").innerHTML; 

window.print(); 
} 
</script>

分页代码略
</form>	
<br>
<%
if a6=2 then
导出EXCEL代码略
end if
%>
</body>