<%@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();
|
}
|
%>
|
