package com.web.pda.lyt.lytPda.service.internal;
|
|
import com.app.base.data.ApiResponseResult;
|
import com.app.base.service.FtpClientService;
|
import com.web.pda.lyt.ftp.dao.FileCheckLytDao;
|
import com.web.pda.lyt.lytPda.service.EquipmentInspectionService;
|
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 org.springframework.web.multipart.MultipartFile;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.ByteArrayInputStream;
|
import java.io.OutputStream;
|
import java.net.URLEncoder;
|
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;
|
|
@Service(value = "EquipmentInspectionService")
|
@Transactional(propagation = Propagation.REQUIRED)
|
public class EquipmentInspectionImpl extends AppUtills implements EquipmentInspectionService {
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
@Autowired
|
private FtpClientService ftpClientService;
|
@Autowired
|
private FileCheckLytDao fileCheckLytDao;
|
|
@Override
|
public ApiResponseResult getDateByDianJianCode(String dianJianCode,String type, String user, String billNo) throws Exception{
|
List<Object> list = getDateByDianJianCodePrc(dianJianCode,type, user, billNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
map.put("cursor2", list.get(3));
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult getMachineCode(String machineCode,String keyword,String type,String searchType,String billNo) throws Exception {
|
List<Object> list = getMachineCodePrc(machineCode,keyword,type,searchType,billNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
map.put("cursor2", list.get(3));
|
map.put("cursor3", list.get(4));
|
|
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult updataValue(String factory, String company, String user, String id, String tableName, String fieldName, String fieldVal) throws Exception {
|
List<Object> list = updataValuePrc(factory,company,user,id,tableName,fieldName,fieldVal);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult getRecordList(String type, String id, String keyword, String size, String page) throws Exception {
|
List<Object> list = getRecordPrc(type,id ,keyword, size,page);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));//主表数据
|
map.put("cursor2", list.get(3));//从表数据
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult uploadFileDianjian(String factory, String company, String username, int mid, int type, String note, MultipartFile[] files) throws Exception {
|
List<String> a = getInfo(factory, company, username, mid, type);
|
System.out.println(a);
|
if (a.get(0).equals("0")) {
|
try {
|
for (MultipartFile file : files) {
|
if(file != null){
|
String a6 = a.get(6);// .replace("/",File.separator);
|
ApiResponseResult ar = ftpClientService.uploadFile(a.get(2), Integer.parseInt(a.get(5)), a.get(3),
|
a.get(4), a6, file.getOriginalFilename(), new ByteArrayInputStream(file.getBytes()));
|
if (ar.getStatus().equals("0")) {
|
// ftp上传成功
|
List<String> b = this.getpqcFile(factory, company, username, mid, type, 0,
|
file.getOriginalFilename(), note, 0);
|
System.out.println(b);
|
}
|
}
|
}
|
return ApiResponseResult.success("操作成功!");
|
} catch (Exception e) {
|
System.out.println(e.toString());
|
return ApiResponseResult.failure("上传文件到FTP失败!").data(e.toString());
|
}
|
} else {
|
return ApiResponseResult.failure("获取FTP信息失败!").data(a.get(1));
|
}
|
}
|
|
@Override
|
public ApiResponseResult getFilesList(String mid) throws Exception {
|
List<String> a = this.getRfFileList(mid);
|
System.out.println(a);
|
if (a.get(0).equals("0")) {
|
String[] fn = a.get(2).substring(0, a.get(2).length() - 1).split("#");
|
String[] fq = a.get(3).substring(0, a.get(3).length() - 1).split("#");
|
String[] mi = a.get(4).substring(0, a.get(4).length() - 1).split("#");
|
String[] cd = a.get(5).substring(0, a.get(5).length() - 1).split("#");
|
String[] fu = a.get(6).substring(0, a.get(6).length() - 1).split("#");
|
List l = new ArrayList();
|
for (int i = 0; i < fn.length; i++) {
|
Map m = new HashMap();
|
m.put("FNOTE", fn[i]);
|
m.put("FATTACH", fq[i]);
|
m.put("MID", mi[i]);
|
m.put("CD", cd[i]);
|
m.put("FURL", fu[i]);
|
|
l.add(m);
|
}
|
|
return ApiResponseResult.success().data(l);
|
} else {
|
return ApiResponseResult.failure(a.get(1));
|
}
|
}
|
|
@Override
|
public ApiResponseResult onlineView(String url, String fname, HttpServletResponse response) throws Exception {
|
// 获取ftp地址账号密码以及端口号
|
String ip = fileCheckLytDao.queryFtpServerIP().get(0).get("PV").toString();
|
String num = fileCheckLytDao.queryFtpPortNum().get(0).get("PV").toString();
|
String name = fileCheckLytDao.queryFtpUser().get(0).get("PV").toString();
|
String psw = fileCheckLytDao.queryFtpPsw().get(0).get("PV").toString();
|
ApiResponseResult result = ftpClientService.download(ip, Integer.parseInt(num), name, psw, url, fname);
|
try {
|
String fileName = URLEncoder.encode(fname, "UTF-8"); // 文件名称
|
// String extName = fsFile.getBsFileType(); //文件后缀名
|
response.setContentType("image/png");
|
response.addHeader("Content-Disposition", "inline;filename=" + fileName);
|
OutputStream os = response.getOutputStream();
|
byte[] bytes = (byte[]) result.getData();
|
os.write(bytes);
|
os.flush();
|
os.close();
|
} catch (Exception e) {
|
System.out.println(e.toString());
|
}
|
return null;
|
}
|
|
public List<String> getRfFileList(String mid) {
|
List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_PDA_app_ftpfile_list(?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, mid);
|
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<String> result = new ArrayList<String>();
|
cs.execute();
|
result.add(cs.getString(2));
|
result.add(cs.getString(3));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(4);
|
String ids = "";
|
String n = "";
|
String n2 = "";
|
String n3 = "";
|
String n4 = "";
|
while (rs.next()) {
|
ids += rs.getString("FNOTE") + "#";
|
n += rs.getString("FATTACH") + "#";
|
n2 += rs.getString("MID") + "#";
|
n3 += rs.getString("CD") + "#";
|
n4 += rs.getString("FURL") + "#";
|
}
|
result.add(ids);
|
result.add(n);
|
result.add(n2);
|
result.add(n3);
|
result.add(n4);
|
return result;
|
}
|
});
|
return resultList;
|
|
}
|
|
/**
|
*
|
* 获取ftp的信息
|
*
|
* @param factory
|
* @param company
|
* @param uaername
|
* @param mid
|
* @param type1
|
* @return
|
*/
|
public List<String> getInfo(String factory, String company, String uaername, int mid, int type1) {
|
List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_PDA_app_ftpfile_getpath(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setLong(3, mid);
|
cs.registerOutParameter(4, java.sql.Types.INTEGER);// 注册输出参数
|
cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 注册输出参数
|
cs.registerOutParameter(6, java.sql.Types.VARCHAR);// 注册输出参数
|
cs.registerOutParameter(7, java.sql.Types.VARCHAR);// 注册输出参数
|
cs.registerOutParameter(8, java.sql.Types.VARCHAR);// 注册输出参数
|
cs.registerOutParameter(9, java.sql.Types.INTEGER);// 注册输出参数
|
cs.registerOutParameter(10, java.sql.Types.VARCHAR);// 注册输出参数
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<String> result = new ArrayList<String>();
|
cs.execute();
|
result.add(cs.getString(4));
|
result.add(cs.getString(5));
|
result.add(cs.getString(6));
|
result.add(cs.getString(7));
|
result.add(cs.getString(8));
|
result.add(cs.getString(9));
|
result.add(cs.getString(10));
|
return result;
|
}
|
});
|
return resultList;
|
|
}
|
|
/**
|
* 文件操作
|
*
|
* @param factory
|
* @param company
|
* @param uaername
|
* @param mid
|
* @param type1
|
* @param Dmltype
|
* @param filename
|
* @param note
|
* @param fileid
|
* @return
|
*/
|
public List<String> getpqcFile(String factory, String company, String uaername, int mid, int type1, int Dmltype,
|
String filename, String note, Integer fileid) {
|
List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_PDA_app_ftpfile_insertpath(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, uaername);
|
cs.setLong(4, mid);//
|
cs.setLong(5, Dmltype);// 操作类型，0是新增,1是修改，2是删除
|
cs.setString(6, filename);// 文件名
|
cs.setString(7, note);// 备注说明
|
cs.setLong(8, fileid);// 附件记录ID，Pi_Dmltype<>0的时候不允许为空
|
cs.registerOutParameter(9, java.sql.Types.INTEGER);// 注册输出参数
|
cs.registerOutParameter(10, java.sql.Types.VARCHAR);// 注册输出参数
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<String> result = new ArrayList<String>();
|
cs.execute();
|
result.add(cs.getString(9));
|
result.add(cs.getString(10));
|
return result;
|
}
|
});
|
return resultList;
|
|
}
|
|
|
public List<Object> getRecordPrc(String type, String id, String keyword, String size, String page) {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_PDA_app_checkbill_list(?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, type);
|
cs.setString(2, page);
|
cs.setString(3, size);
|
cs.setString(4, keyword);
|
cs.setString(5, id);
|
cs.registerOutParameter(6, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(7, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(8, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(9, -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<Map<String, Object>> l_2 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(6));
|
result.add(cs.getString(7));
|
if (cs.getString(6).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(8);
|
ResultSet rs_2 = (ResultSet) cs.getObject(9);
|
try {
|
l = fitMap(rs);
|
l_2 = fitMap(rs_2);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
}
|
System.out.println(l);
|
System.out.println(l_2);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
public List<Object> updataValuePrc(String factory, String company, String user, String id, String tableName, String fieldName, String fieldVal) {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_PDA_insert_value_field(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, user);
|
cs.setString(4, id);
|
cs.setString(5, tableName);
|
cs.setString(6, fieldName);
|
cs.setString(7, fieldVal);
|
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();
|
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);
|
|
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;
|
}
|
|
public List<Object> getMachineCodePrc(String machineCode,String keyword,String type,String searchType,String billNo) {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call prc_pda_m_Machine_Code(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, machineCode);
|
cs.setString(2, keyword);
|
cs.setString(3, type);
|
cs.setString(4, searchType);
|
cs.setString(5, billNo);
|
cs.registerOutParameter(6, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(7, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(8, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(9, -10);// 输出参数 追溯数据
|
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<Map<String, Object>> l_2 = new ArrayList();
|
List<Map<String, Object>> l_3 = new ArrayList();
|
|
cs.execute();
|
result.add(cs.getInt(6));
|
result.add(cs.getString(7));
|
if (cs.getString(6).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(8);
|
ResultSet rs2 = (ResultSet) cs.getObject(9);
|
ResultSet rs3 = (ResultSet) cs.getObject(10);
|
|
try {
|
l = fitMap(rs);
|
l_2 = fitMap(rs2);
|
l_3 = fitMap(rs3);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
result.add(l_3);
|
|
}
|
System.out.println(l);
|
System.out.println(l_2);
|
System.out.println(l_3);
|
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
private List<Object> getDateByDianJianCodePrc(String dianJianCode,String type, String user, String billNo) {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call prc_pda_m_generateinspplan(?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, dianJianCode);
|
cs.setString(2, type);
|
cs.setString(3, user);
|
cs.setString(4, billNo);
|
cs.registerOutParameter(5, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(6, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(7, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(8, -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<Map<String, Object>> l_2 = 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);
|
ResultSet rs2 = (ResultSet) cs.getObject(8);
|
try {
|
l = fitMap(rs);
|
l_2 = fitMap(rs2);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
}
|
System.out.println(l);
|
System.out.println(l_2);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
}
|
