import com.alibaba.druid.support.json.JSONUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.foreveross.security.service.extension.ApplicationException;
import com.foreveross.springboot.dubbo.utils.Payload;
import com.XXX.XXX.domain.dto.ProjectImportDto;
import com.xxx.xxx.service.rest.api.ProjectImportRestService;
import com.google.common.collect.Lists;
import com.sargeraswang.util.ExcelUtil.ExcelLogs;
import com.sargeraswang.util.ExcelUtil.ExcelUtil;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceUnit;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.Consumes;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.UUID;
/**
* 导入xxxx数据
*/
@Service("projectImportRestService")
@Path("/api/v1/project")
public class ProjectImportRestServiceImpl implements ProjectImportRestService {
private static Logger logger = LoggerFactory.getLogger(ProjectImportRestServiceImpl.class);
@PersistenceUnit
private EntityManagerFactory emf;
private List<String> questionIds; // 存储问题数据
@POST
@Path("/import")
@Consumes({MediaType.MULTIPART_FORM_DATA})
@Produces({MediaType.APPLICATION_JSON})
@Override
public Payload importProject(@Context HttpServletRequest request, @Context HttpServletResponse response) {
response.setCharacterEncoding("UTF-8");
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (isMultipart) {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
// 支持中文文件名
upload.setHeaderEncoding("utf-8");
List<FileItem> fileItems = Lists.newArrayList(); // guava 不用处理泛型,左边是什么类型,右边可以自动匹配。如果list里面放map这种,就可以自动转换。
try {
fileItems = upload.parseRequest(request);
FileItem fileItem = fileItems.get(0); // 可以多文件上传,只有一个文件,所以get(0),取第一个。
//获得上传的文件名
String name = UUID.randomUUID().toString() + "-" + fileItem.getName();
String filePath = "/home/logs";
this.upload4Stream(name, filePath, fileItem.getInputStream());
} catch (ApplicationException ex) {
ex.printStackTrace();
// return new Payload(ex.getMessage());
throw new ApplicationException(Response.Status.NOT_IMPLEMENTED, ex.getMessage());
} catch (Exception e) {
e.printStackTrace();
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "导入发生异常:", e);
}
} else {
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "请上传文件");
}
return new Payload("Success "+questionIds);
}
/**
* 上传文件具体操作 *
* @param fileName 文件名
* @param filePath 文件上传路径
* @param inStream 文件流
* @return 上传是否成功
*/
private boolean upload4Stream(String fileName, String filePath,
InputStream inStream) throws Exception {
boolean result = false;
if ((filePath == null) || (filePath.trim().length() == 0)) {
return result;
}
File outputFile = null;
try {
String wholeFilePath = filePath + File.separator + fileName;
File dir = new File(filePath);
if (!dir.exists()) {
dir.mkdirs();
}
outputFile = new File(wholeFilePath);
boolean isFileExist = outputFile.exists();
boolean canUpload = true;
if (isFileExist) {
canUpload = outputFile.delete();
}
if (canUpload) {
FileUtils.copyInputStreamToFile(inStream, outputFile);
//导入数据库
importData(outputFile);
}
} finally {
FileUtils.deleteQuietly(outputFile);
}
return result;
}
/**
* 导入Excel 数据到数据库
* @param file
* @throws Exception
*/
private void importData(File file) throws Exception {
InputStream inputStream = new FileInputStream(file);
ExcelLogs logs = new ExcelLogs();
Collection<ProjectImportDto> list = ExcelUtil.importExcel(ProjectImportDto.class, inputStream,
"yyyy-MM-dd HH:mm:ss", logs);
EntityManager em = getEm();
try {
int i = 0;
for (ProjectImportDto dto : list) {
if (StringUtils.isEmpty(dto.getProjectCode())) {
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "xxx编号不能为空,请输入xxx编号,并检查是否为文本类型");
}
String querySql = "select * from GAEI_PROJECT where CODE = '" + dto.getProjectCode() + "'";
List<Object> objectArrayList = em.createNativeQuery(querySql).getResultList();
int result;
try {
em.getTransaction().begin();
if (!CollectionUtils.isEmpty(objectArrayList)) {
Object[] data = (Object[]) objectArrayList.get(0);
JSONArray jsonArray = JSON.parseArray(data[9].toString());
// -----------------------------------------
checkManagerId(dto,em);
// -----------------------------------------
String professionJson = toProfessionJson(jsonArray, dto);
String updateSql = "update GAEI_PROJECT set PROFESSIONAL_GROUP='" +
professionJson + "' where CODE = '" + dto.getProjectCode() + "'";
result = em.createNativeQuery(updateSql).executeUpdate();
} else {
// -----------------------------------------
checkManagerId(dto,em);
// -----------------------------------------
JSONArray jsonArray = new JSONArray();
String insertSql = "insert into GAEI_PROJECT (SOURCE, PROJECT_DIRECTOR_NO, CODE, STATUS,CREATED_AT, PROFESSIONAL_GROUP) " +
" values ('本系统新增', " +
"'" + gedProjectDirectorId(dto.getProjectManagerCode(),em) + "'," +
"'" + dto.getProjectCode() + "'," +
"'正常'," +
"'" + DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss") + "'," +
"'" + toProfessionJson(jsonArray, dto) + "')";
result = em.createNativeQuery(insertSql).executeUpdate();
}
em.getTransaction().commit();
} catch (ApplicationException ex) {
ex.printStackTrace();
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, ex.getMessage());
} catch (Exception ex) {
ex.printStackTrace();
em.getTransaction().rollback();
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "导入xxxx数据失败", ex);
}
if (result <= 0) {
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "插入xxx数据失败");
}
}
} catch (ApplicationException ex) {
ex.printStackTrace();
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, ex.getMessage());
}
catch (Exception ex) {
ex.printStackTrace();
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, ex.getMessage());
} finally {
if (null != em) {
em.close();
}
}
}
/**
* 得xxxxid
* @param projectManagerCode
* @param em
* @return
*/
private String gedProjectDirectorId(String projectManagerCode,EntityManager em){
if(projectManagerCode.length() <3){ // 没有工号
return "00000";
}
String getIdSql = "select id from appbricks_user where usernumber = '" + projectManagerCode + "'";
String projectDirectorId = (String)em.createNativeQuery(getIdSql).getSingleResult();
if(null == projectDirectorId){
questionIds.add("xxx工号:"+projectManagerCode);
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "xxx工号: "+projectManagerCode+" 有误,请核查");
}
return projectDirectorId;
}
/**
* 设置xxxxid
* @param dto
* @param em
*/
private ProjectImportDto checkManagerId(ProjectImportDto dto,EntityManager em){
String getManagerIdSql = "select id from appbricks_user where usernumber = '" + dto.getProfessionGroupManagerCode() + "'";
if(null != dto && null != dto.getProfessionGroupManagerCode()){
if(dto.getProfessionGroupManagerCode().length()>4 ){ // 长度大于4
String managerId = null;
try{
managerId = (String)em.createNativeQuery(getManagerIdSql).getSingleResult();
}catch ( Exception e){
questionIds.add("xxxx工号:"+dto.getProfessionGroupManagerCode());
logger.info("------------------------------------------------------------------\nxxx导入,xxxid不正确"+ e.getMessage());
throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "xxxx工号: "+dto.getProfessionGroupManagerCode()+" 有误,请核查");
}
dto.setProfessionGroupManagerCode(String.valueOf(managerId));
}else {
dto.setProfessionGroupManagerCode("无");
}
}else {
dto.setProfessionGroupManagerCode("无");
}
return dto;
}
/**
* 得EntityManager
*
* @return
*/
private EntityManager getEm() {
return emf.createEntityManager();
}
/**
* xxx转json
* @param jsonArray
* @param dto
* @return
*/
private String toProfessionJson(JSONArray jsonArray, ProjectImportDto dto) {
JSONObject jsonObject = new JSONObject();
jsonObject.put("name", dto.getProfessionGroup());
jsonObject.put("departmentId", "");
jsonObject.put("managerId", dto.getProfessionGroupManagerCode());
jsonObject.put("managerName", dto.getProfessionGroupManagerName());
if(null == jsonArray){
jsonArray = new JSONArray();
}
jsonArray.add(jsonObject);
return JSONUtils.toJSONString(jsonArray);
}
}