<%@page import="com.northglass.Globel.mes"%> <%@page import="com.northglass.SqlHelper.*"%> <%@page import="org.json.*"%> <%@ page contentType="text/json;charset=UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <% String line = request.getParameter("line"); String gongyi = request.getParameter("gongyi"); String banzu = request.getParameter("banzu"); String sTime = request.getParameter("startTime"); String eTime = request.getParameter("endTime"); DBSession sn = null; boolean ok = false; //System.out.println("工艺：" + gongyi + ";参数：" + line + "," + banzu + "," + sTime + "," + eTime); try { sn = mes.getMesDBHelper().createSession(false); String strSQL = ""; if (gongyi.equals("钢化")) { strSQL = "SELECT NULL,team_name,line,processcard_id," + "(select count(1) from e_pierceds e LEFT JOIN gmms_galss_task_buffer b on e.glass_id = b.id where e.processcard_id = t.processcard_id and e.team_name = t.team_name and e.line = t.line group by e.processcard_id ) as '破损数'," + "(select sum(edg_width*edg_height) from e_pierceds e LEFT JOIN gmms_galss_task_buffer b on e.glass_id = b.id where e.processcard_id = t.processcard_id and e.team_name = t.team_name and e.line = t.line group by e.processcard_id ) as '破损面积'," + "count( 1 ) as '完成数量'," + "sum( width * height ) as '完成面积'," + "concat(thinkess, '', color ) as '膜系'," + "max(create_time) as '完成时间' FROM `e_record_tempering` t "; String strPara = "where create_time >'" + sTime + "'"; if (!eTime.isEmpty()) { strPara += "and create_time <'" + eTime + "'"; } if (!line.equals("0")) { strPara += " and line = " + line; } if (!banzu.equals("0")) { strPara += " and team_name = '" + banzu + "'"; } String strGy = " GROUP BY processcard_id,team_name,line order by team_name,line, max(create_time)"; strSQL = strSQL.concat(strPara).concat(strGy); } if (gongyi.equals("磨边")) { strSQL = "SELECT NULL,team_name,line,processcard_id," + "(select count(1) from e_pierceds e LEFT JOIN gmms_galss_task_buffer b on e.glass_id = b.id where e.processcard_id = t.processcard_id and e.team_name = t.team_name and e.line = t.line group by e.processcard_id ) as '破损数'," + "(select sum(edg_width*edg_height) from e_pierceds e LEFT JOIN gmms_galss_task_buffer b on e.glass_id = b.id where e.processcard_id = t.processcard_id and e.team_name = t.team_name and e.line = t.line group by e.processcard_id ) as '破损面积'," + "count( 1 ) as '完成数量'," + "sum( width * height ) as '完成面积'," + "concat(thinkess, '', color ) as '膜系'," + "max(create_time) as '完成时间' FROM `e_record_stock` t "; String strPara = "where create_time >'" + sTime + "'"; if (!eTime.isEmpty()) { strPara += "and create_time <'" + eTime + "'"; } if (!line.equals("0")) { strPara += " and line = " + line; } if (!banzu.equals("0")) { strPara += " and team_name = '" + banzu + "'"; } String strGy = " GROUP BY processcard_id,team_name,line order by team_name,line, max(create_time)"; strSQL = strSQL.concat(strPara).concat(strGy); } if (gongyi.equals("钢化装载")) { strSQL = "select count(1) from (SELECT count(1),furnace FROM `e_record_tempering` "; String strPara = "where create_time >'" + sTime + "'"; if (!eTime.isEmpty()) { strPara += "and create_time <'" + eTime + "'"; } if (!line.equals("0")) { strPara += " and line = " + line; } if (!banzu.equals("0")) { strPara += " and team_name = '" + banzu + "'"; } strPara += " and furnace is not null "; strSQL += strPara; strSQL += " group by furnace) as s"; } if (gongyi.equals("切割明细")) { strSQL = "select null,width,height,thickness,color,batch_id,sum(pieces),null,groups,dengluname,DATE_FORMAT(BgTime,'%Y-%m-%d') from gmms_cutmanage "; String strPara = "where BgTime >='" + sTime + "'"; if (!eTime.isEmpty()) { strPara += "and BgTime <='" + eTime + " 23:59:59'"; } if (!line.equals("0")) { strPara += " and groups = " + line; } if (!banzu.equals("0")) { strPara += " and dengluname = '" + banzu + "'"; } strSQL += strPara; strSQL += " group by width,height,thickness,color,batch_id,dengluname,DATE_FORMAT(BgTime,'%Y-%m-%d') "; strSQL += " order by DATE_FORMAT(BgTime,'%Y-%m-%d'),color,thickness"; //System.out.println(strSQL); } if (gongyi.equals("切割总览")) { strSQL = "select null,width,height,thickness,color,sum(pieces),null,groups,dengluname,DATE_FORMAT(BgTime,'%Y-%m-%d') from gmms_cutmanage "; String strPara = "where BgTime >='" + sTime + "'"; if (!eTime.isEmpty()) { strPara += "and BgTime <='" + eTime + " 23:59:59'"; } if (!line.equals("0")) { strPara += " and groups = " + line; } if (!banzu.equals("0")) { strPara += " and dengluname = '" + banzu + "'"; } strSQL += strPara; strSQL += " group by groups,dengluname,width,height,thickness,color,DATE_FORMAT(BgTime,'%Y-%m-%d') "; strSQL += " order by DATE_FORMAT(BgTime,'%Y-%m-%d'),color,thickness"; } //2023-12-4 新增 【切割明细，切割总览】按时间/尺寸分组 if (gongyi.equals("切割明细-分组")) { strSQL = "select null,width,height,thickness,color,null,sum(pieces),null,null,null,DATE_FORMAT(BgTime,'%Y-%m-%d') from gmms_cutmanage "; String strPara = "where BgTime >='" + sTime + "'"; if (!eTime.isEmpty()) { strPara += "and BgTime <='" + eTime + " 23:59:59'"; } if (!line.equals("0")) { strPara += " and groups = " + line; } if (!banzu.equals("0")) { strPara += " and dengluname = '" + banzu + "'"; } strSQL += strPara; strSQL += " group by width,height,thickness,color,DATE_FORMAT(BgTime,'%Y-%m-%d') "; strSQL += " order by DATE_FORMAT(BgTime,'%Y-%m-%d'),color,thickness"; //System.out.println(strSQL); } if (gongyi.equals("切割总览-分组")) { strSQL = "select null,width,height,thickness,color,sum(pieces),null,null,null,DATE_FORMAT(BgTime,'%Y-%m-%d') from gmms_cutmanage "; String strPara = "where BgTime >='" + sTime + "'"; if (!eTime.isEmpty()) { strPara += "and BgTime <='" + eTime + " 23:59:59'"; } if (!line.equals("0")) { strPara += " and groups = " + line; } if (!banzu.equals("0")) { strPara += " and dengluname = '" + banzu + "'"; } strSQL += strPara; strSQL += " group by width,height,thickness,color,DATE_FORMAT(BgTime,'%Y-%m-%d') "; strSQL += " order by DATE_FORMAT(BgTime,'%Y-%m-%d'),color,thickness"; } if (gongyi.equals("切割单片")) { } //2023-09-19 新增 开始 //System.out.println("报表SQL参数：" + gongyi+":"+sTime+":"+eTime); if (gongyi.equals("切割率明细")) { String gcNo = request.getParameter("gongchenghao"); //strSQL="select * from v_rf_qiecailv where 完成时间 Between '"+sTime+"' and '"+eTime+"'"; strSQL = "select * from v_rf_qiecailv where "; if (!gcNo.isEmpty()) { strSQL += " 工程号 ='" + gcNo + "'"; } else { if (!sTime.isEmpty() && !eTime.isEmpty()) { strSQL += "(完成时间 >= '" + sTime + "' and 完成时间 <'" + eTime + "')"; } else if (!sTime.isEmpty()) { strSQL += " 完成时间 >= '" + sTime + "' "; } else { } } } if (gongyi.equals("流程卡切裁率")) { String lckNo = request.getParameter("liuchengkahao"); String gcNo = request.getParameter("gongchenghao"); strSQL = ""; strSQL = " select null,f.file_name,b.processcard_id,b.thickness,b.color,count(1),round(sum(b.width*b.height)/1000000,2),f.cutting_rate,f.receive_time " + "from gmms_raw_file f left join gmms_galss_task_buffer b on f.file_name = b.engineering_id " + "where f.file_Name like 'P%' "; if (!lckNo.isEmpty() || !gcNo.isEmpty()) { if (!lckNo.isEmpty()) { strSQL += " and(b.processcard_id like '%" + lckNo + "%')"; } if (!gcNo.isEmpty()) { strSQL += " and(f.file_Name like '%" + gcNo + "%')"; } //strSQL+=" and(f.file_Name like '%"+gcNo+"%' or b.processcard_id like '%"+lckNo+"%')"; } else { if (!sTime.isEmpty()) { strSQL += " and f.receive_time >= '" + sTime + "'"; } if (!eTime.isEmpty()) { strSQL += " and f.receive_time <= '" + eTime + "'"; } } strSQL += " group by f.file_name,b.processcard_id order by f.receive_time desc"; } if (gongyi.equals("总切裁率")) { strSQL = "select operator as 线路, count(1) as 工程数量,ROUND(sum(galssmeasure),2) as 小片面积 ,ROUND(sum(patternmeasure),2) as 原片面积," + " ROUND(sum(galssmeasure)/sum(patternmeasure)*100,2) as 平均切裁率" + " from gmms_raw_file where state like '%完成%' and file_name like 'P%'"; if (!sTime.isEmpty()) { strSQL += " and receive_time >= '" + sTime + "'"; } if (!eTime.isEmpty()) { strSQL += " and receive_time <= '" + eTime + "'"; } strSQL += " group by operator"; } //2023-09-19 新增 结束 //System.out.println("报表SQL：" + strSQL); sn.createSql(strSQL); JSONArray a = sn.query().resultToJson(false); if (a != null) { out.print(a.toString()); } else { out.print("[]"); } } catch (Exception e) { e.printStackTrace(); } finally { if (sn != null) sn.close(); } %>