package com.web.kanban.service.internal;
|
|
import com.app.base.data.ApiResponseResult;
|
import com.web.kanban.service.HyKanbanService;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.jdbc.core.CallableStatementCallback;
|
import org.springframework.jdbc.core.CallableStatementCreator;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Propagation;
|
import org.springframework.transaction.annotation.Transactional;
|
import java.sql.CallableStatement;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.*;
|
|
@Service(value = "HyKanbanService")
|
@Transactional(propagation = Propagation.REQUIRED)
|
public class HyKanbanImpl implements HyKanbanService {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
|
@Override
|
public ApiResponseResult getProductionData() {
|
Map map = new HashMap();
|
map.put("Data1", doProcedure("PRC_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"1"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"}));
|
//map.put("Data2", doProcedure("PRC_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"2"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"}));
|
map.put("Data3", doProcedure("PRC_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"3"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"}));
|
|
List<Object> list_2 = doProcedure("PRC_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"2"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"});
|
|
Integer flag = (Integer)list_2.get(0);
|
Map map_2 = new HashMap();
|
map_2.put("Leng", new ArrayList<String>());
|
map_2.put("BaiData", new ArrayList<String>());
|
map_2.put("YeData", new ArrayList<String>());
|
if(flag == 0){
|
List<Map<String, Object>> lmap = (List<Map<String, Object>>)list_2.get(2);
|
|
//先取所有日期
|
List<String> list_data = new ArrayList<String>();
|
for(Map<String, Object> map1:lmap){
|
if(map1.get("F_DATE") != null){
|
String data = map1.get("F_DATE").toString();
|
boolean flag_data = list_data.contains(data);
|
if(!flag_data){
|
list_data.add(data);
|
}
|
}
|
|
}
|
|
Collections.sort(list_data);//升序
|
|
//取白板和晚班信息
|
String[] str_bai = new String[list_data.size()];
|
String[] str_ye = new String[list_data.size()];
|
for(int i=0;i<list_data.size();i++){
|
String str = list_data.get(i);
|
str_bai[i] = "0";
|
str_ye[i] = "0";
|
for(Map<String, Object> map2:lmap){
|
if(map2.get("F_DATE") != null){
|
if(str.equals(map2.get("F_DATE").toString())){
|
if("白班".equals(map2.get("SHIFT").toString())){
|
str_bai[i] = map2.get("QUANTITY")==null?"0": map2.get("QUANTITY").toString();
|
}else if("夜班".equals(map2.get("SHIFT").toString())){
|
str_ye[i] = map2.get("QUANTITY")==null?"0": map2.get("QUANTITY").toString();
|
}
|
}
|
}
|
}
|
}
|
map_2.put("Leng",list_data);
|
map_2.put("BaiData", str_bai);
|
map_2.put("YeData", str_ye);
|
}
|
map.put("Data2",map_2);
|
return ApiResponseResult.success().data(map);
|
}
|
|
public ApiResponseResult getHeyiData(String code) throws Exception {
|
// TODO Auto-generated method stub
|
Map map = new HashMap();
|
map.put("Data1", doProcedure("PRC_APP_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"1"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"}));
|
map.put("Data2", doProcedure("PRC_APP_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"2"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"}));
|
map.put("Data3", doProcedure("PRC_APP_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"3"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"}));
|
map.put("Data4", doProcedure("PRC_APP_KANBAN_TUBUA_DATA",getQuestion(4),new String[] {"PI_TYPE"}, new String[] {"4"},new String[] {"PO_FLAG","PO_TEXT","PO_CURSOR"}));
|
|
return ApiResponseResult.success().data(map);
|
}
|
|
private List<Object> doProcedure(String pro_name, String ques, String[] in_params, String[] in_values, String[] out_params) {
|
List<Object> resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call "+pro_name+"("+ques+")}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
for(int i=0;i<in_params.length;i++){
|
cs.setString((i+1), in_values[i]);
|
System.out.println("in:"+(i+1));
|
}
|
for(int j=0;j<out_params.length;j++){
|
if(j==0){
|
cs.registerOutParameter((in_params.length+1), java.sql.Types.INTEGER);
|
System.out.println("out:"+(in_params.length+1));
|
}else if(j==1){
|
cs.registerOutParameter((in_params.length+2), java.sql.Types.VARCHAR);
|
System.out.println("out:"+(in_params.length+2));
|
}else{
|
System.out.println("out:"+(in_params.length+1+j));
|
cs.registerOutParameter((in_params.length+1+j), -10);// 输出参数
|
|
}
|
}
|
/*cs.setString(1, in_values[0]);
|
cs.registerOutParameter(2, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(4, -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(3));
|
result.add(cs.getString(4));*/
|
result.add(cs.getInt(in_params.length+1));
|
result.add(cs.getString(in_params.length+2));
|
if (cs.getString(in_params.length+1).toString().equals("0")) {
|
for(int k=3;k<=out_params.length;k++){
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(in_params.length+k);
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
System.out.println(e.toString());
|
}
|
result.add(l);
|
}
|
|
}
|
return result;
|
}
|
});
|
return resultList;
|
}
|
private String getQuestion(int num){
|
String ques = "";
|
for(int i=0;i<num;i++){
|
ques+= "?,";
|
}
|
if(ques.length()>1){
|
ques = ques.substring(0, ques.length()-1);
|
}
|
return ques;
|
}
|
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 LinkedHashMap<String, Object>();
|
for (String columnName : columnNames) {
|
map.put(columnName, rs.getString(columnName));
|
}
|
list.add(map);
|
}
|
}
|
return list;
|
}
|
}
|
