package com.web.lytreport.service.internal;
|
|
import java.io.IOException;
|
import java.net.MalformedURLException;
|
import java.sql.CallableStatement;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.servlet.http.HttpServletResponse;
|
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.data.domain.PageRequest;
|
import org.springframework.jdbc.core.CallableStatementCallback;
|
import org.springframework.jdbc.core.CallableStatementCreator;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.web.bind.annotation.GetMapping;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.utils.EasyExcelUtils;
|
import com.utils.ExcelExport;
|
|
public class ReportPrcUtils {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
/**
|
* 获取工单列表 2021-01-07
|
*/
|
public List getTaskNoPrc(String company, String factory, String keyword) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_APP_GETTASKNO(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, keyword);
|
cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 获取结果报表 2021-01-07
|
*/
|
public List getReportPrc(String prc_name, String company, String factory, String taskNo, String batNo,
|
String begTime, String endTime, Integer size, Integer page) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prc_name + " (?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, taskNo);
|
cs.setString(4, batNo);
|
cs.setString(5, begTime);
|
cs.setString(6, endTime);
|
cs.setInt(7, size);
|
cs.setInt(8, page + 1);
|
cs.registerOutParameter(9, java.sql.Types.INTEGER);// 输出参数 返回标识-总记录数
|
cs.registerOutParameter(10, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(11, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(12, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
List<Object> cols = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(10));
|
result.add(cs.getString(11));
|
if (cs.getString(10).toString().equals("0")) {
|
result.add(cs.getInt(9));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(12);
|
try {
|
l = fitMap(rs);
|
cols = fitMapCols(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(cols);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 质量IQC来料检验台账 2020-01-22
|
* action: 0(报表)/1(导出)
|
**/
|
public List getQualCheckReportPrc(String prc_name, String company, String factory, String year, String month,
|
String target, Integer dataType, String usrName,Integer action) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prc_name + " (?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, year);
|
cs.setString(4, month);
|
cs.setString(5, target);
|
cs.setInt(6, dataType);
|
cs.setString(7, usrName);
|
cs.registerOutParameter(8, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(9, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(10, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
List<Object> cols = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(8));
|
result.add(cs.getString(9));
|
if (cs.getString(8).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(10);
|
if(action==0){
|
try {
|
if (dataType == 1) {
|
l = fitMapIQCX(rs);
|
} else {
|
l = fitMap(rs);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
}else{
|
try {
|
l = fitMap(rs);
|
cols = fitMapCols(rs);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(cols);
|
}
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 2021-01-25 质量IQC来料检验台账 处理图表x轴键值-lst
|
**/
|
private List<Map<String, Object>> fitMapIQCX(ResultSet rs) throws Exception {
|
List<Map<String, Object>> list = new ArrayList<>();// 存所有数据
|
List<Map<String, Object>> list_t = new ArrayList<>();// 存临时数据
|
if (null != rs) {
|
Map<String, Object> map_c = new HashMap<String, Object>();// 存echarts所需数据
|
List<Object> list_c;// 存无键值的数据
|
Map<String, Object> map;// 存有键值的数据
|
int colNum = rs.getMetaData().getColumnCount();
|
List<String> columnNames = new ArrayList<String>();
|
List<String> columnNames1 = new ArrayList<String>();
|
for (int i = 1; i <= colNum; i++) {
|
columnNames.add(rs.getMetaData().getColumnName(i));
|
String aString = rs.getMetaData().getColumnName(i);
|
columnNames1.add(aString.substring(1, aString.length()));
|
}
|
map_c.put("columnNames", columnNames1);// 保存表头-作为横坐标
|
int i = 0;// 循环次数，用于设置键值
|
while (rs.next()) {
|
map = new HashMap<String, Object>();
|
list_c = new ArrayList<>();
|
for (String columnName : columnNames) {
|
map.put(columnName, rs.getString(columnName));
|
String tString = rs.getString(columnName);
|
if (tString.indexOf("%") != -1) {// 含有%字符时
|
list_c.add(tString.substring(0, tString.indexOf("%")));// 去掉%
|
} else {
|
list_c.add(tString);
|
}
|
}
|
map_c.put("index" + i, list_c);
|
i++;
|
list_t.add(map);
|
}
|
map = new HashMap<String, Object>();
|
map.put("ordData", list_t);
|
list.add(map);
|
list.add(map_c);
|
}
|
return list;
|
}
|
|
/**
|
* 2021-01-28 生产日报-按工单
|
**/
|
public List getProdDailyByTaskReportPrc(String prc_name, String proc, String begTime, String endTime,
|
Integer dataType) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prc_name + " (?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, proc);
|
cs.setString(2, begTime);
|
cs.setString(3, endTime);
|
cs.setInt(4, dataType);
|
cs.registerOutParameter(5, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(6, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(7, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
List<Object> cols = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(5));
|
result.add(cs.getString(6));
|
if (cs.getString(5).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(7);
|
try {
|
l = fitMap(rs);
|
cols=fitMapCols(rs);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(cols);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 2021-01-29 lst 只返回列名
|
**/
|
private List<Object> fitMapCols(ResultSet rs) throws Exception {
|
List<Object> list = new ArrayList<>();
|
if (null != rs) {
|
int colNum = rs.getMetaData().getColumnCount();
|
List<Object> columnNames = new ArrayList<Object>();
|
for (int i = 1; i <= colNum; i++) {
|
columnNames.add(rs.getMetaData().getColumnName(i));
|
}
|
list = columnNames;
|
}
|
return list;
|
}
|
|
/**
|
* 2021-01-29 数据导出【导出大量数据会卡死】 param: response, obj-存储过程结果 cols-表头列
|
**/
|
public void Export1(HttpServletResponse response, Object obj, Object cols) throws Exception {
|
Map<String, Object> param = new HashMap<String, Object>();
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
// String filePath = "static/excelFile/导出数据.xlsx";
|
String filePath = "导出数据.xlsx";
|
// List<Object> objList=(List<Object>) obj;//结果集
|
|
String cString = cols.toString();
|
cString = cString.substring(1, cString.length() - 1);// 头尾去掉'['&']'
|
String[] map_arr = cString.split(", ");// 列名数据处理
|
|
List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();// 最终数据
|
listMap = (List<Map<String, Object>>) obj;
|
|
ExcelExport.export(response, listMap, workbook, map_arr, filePath, "导出数据.xlsx");
|
}
|
|
/**
|
* 2021-01-30 数据导出 easyexcel
|
* lst
|
* param: response, obj-存储过程结果 cols-表头列
|
**/
|
@GetMapping("download")
|
public void Export3(HttpServletResponse response, Object obj, Object cols) throws Exception {
|
// 处理表头数组
|
String cString = cols.toString();
|
cString = cString.substring(1, cString.length() - 1);// 头尾去掉'['&']'
|
String[] tit_arr = cString.split(", ");// 列名数据处理
|
// 查询出的数据
|
List<Map<String, Object>> list = (List<Map<String, Object>>) obj;
|
EasyExcelUtils.download(response, "导出数据", tit_arr, tit_arr, list);
|
}
|
|
private List<Map<String, Object>> fitMap(ResultSet rs) throws Exception {
|
List<Map<String, Object>> list = new ArrayList<>();
|
if (null != rs) {
|
Map<String, Object> map;
|
int colNum = rs.getMetaData().getColumnCount();
|
List<String> columnNames = new ArrayList<String>();
|
for (int i = 1; i <= colNum; i++) {
|
columnNames.add(rs.getMetaData().getColumnName(i));
|
}
|
while (rs.next()) {
|
map = new HashMap<String, Object>();
|
for (String columnName : columnNames) {
|
map.put(columnName, rs.getString(columnName));
|
}
|
list.add(map);
|
}
|
}
|
return list;
|
}
|
}
|
