package com.goafanti.order.service.impl; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.*; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpServletResponseWrapper; import com.goafanti.common.bo.Result; import com.goafanti.common.utils.excel.NewExcelUtil; import com.goafanti.order.bo.*; import com.goafanti.order.bo.outStatistics.*; import groovy.util.logging.Log4j; import org.apache.commons.collections4.map.HashedMap; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import com.goafanti.common.bo.Error; import com.goafanti.common.dao.TOrderTaskMapper; import com.goafanti.common.error.BusinessException; import com.goafanti.common.utils.ExportExcelUtil; import com.goafanti.core.mybatis.BaseMybatisDao; import com.goafanti.core.mybatis.page.Pagination; import com.goafanti.order.enums.NewProjectStatus; import com.goafanti.order.service.ProjectStatisticsService; @Service @Log4j public class ProjectStatisticsServiceImpl extends BaseMybatisDao implements ProjectStatisticsService { @Value("${upload.path}") private String downloadPath; @SuppressWarnings("unchecked") @Override public Pagination selectTaskList(InputProjectStatistics ps) { Map params= new HashedMap<>(); Integer pageNo=1,pageSize=10; if(ps.getStartDate()!=null)params.put("startDate", ps.getStartDate()); if(ps.getEndDate()!=null)params.put("endDate", ps.getEndDate()+" 23:59:59"); if(ps.getDepId()!=null)params.put("depId", ps.getDepId()); if(ps.getThchDepId()!=null)params.put("thchDepId", ps.getThchDepId()); if(ps.getProjectStatus()!=null)params.put("projectStatus", ps.getProjectStatus()); if(ps.getProjectType()!=null)params.put("projectType", ps.getProjectType()); if(ps.getOrderNo()!=null)params.put("orderNo", ps.getOrderNo()); if(ps.getContractNo()!=null)params.put("contractNo", ps.getContractNo()); if(ps.getUserName()!=null)params.put("userName", ps.getUserName()); if(ps.getDeclarationBatch()!=null)params.put("declarationBatch", ps.getDeclarationBatch()); if(ps.getProjectSituation()!=null)params.put("projectSituation", ps.getProjectSituation()); if(ps.getProjectAmount()!=null) { // 0~0.5万、0.5~1万、1~2万、2~5万、5~10万、10~30万、30~50万、50到80万、80万上 double s = 0,e=0; if (ps.getProjectAmount()==0) { s=0;e=0.5; }else if (ps.getProjectAmount()==1) {s=0;e=0.5; }else if (ps.getProjectAmount()==2) {s=0.5;e=1; }else if (ps.getProjectAmount()==3) {s=1;e=2; }else if (ps.getProjectAmount()==4) {s=2;e=5; }else if (ps.getProjectAmount()==5) {s=5;e=10; }else if (ps.getProjectAmount()==6) {s=10;e=30; }else if (ps.getProjectAmount()==7) {s=30;e=50; }else if (ps.getProjectAmount()==8) {s=50;e=80; }else if (ps.getProjectAmount()==9) {s=80; } params.put("projectAmount", ps.getProjectAmount()); params.put("amountStart", s); params.put("amountEnd", e); } params.put("status",ps.getStatus()==null?0:ps.getStatus()); if(ps.getProvince()!=null) params.put("province", ps.getProvince()); if(ps.getProjectId()!=null) params.put("projectId", ps.getProjectId()); if(ps.getScreen()!=null) params.put("screen", ps.getScreen()); if(ps.getPageNo()!=null)pageNo=ps.getPageNo(); if(ps.getPageSize()!=null)pageSize=ps.getPageSize(); Pagination p = (Pagination)findPage("selectProjectStatisticsListByPage", "selectProjectStatisticsCount", params, pageNo, pageSize); return p; } @Override public Result exporTaskList( InputProjectStatistics ib,HttpServletResponse response) { if(ib.getPageSize()==null)ib.setPageSize(9999999); if(ib.getStatus()==null)ib.setStatus(0); @SuppressWarnings("unchecked") List list= (List) selectTaskList(ib).getList(); Result res =new Result(); if (ib.getStatus()==null||ib.getStatus()==0){ List tl=new ArrayList<>(); for (OutProjectStatistics o : list) { OutProjectStatisticsCommon common = new OutProjectStatisticsCommon(); BeanUtils.copyProperties(o,common); tl.add(common); } NewExcelUtil excelUtil=new NewExcelUtil<>(OutProjectStatisticsCommon.class); res=excelUtil.exportExcel(tl,"项目汇总表(通用)",downloadPath); }else if (ib.getStatus()==1){//高新、科技 List tl=new ArrayList<>(); for (OutProjectStatistics o : list) { OutProjectStatisticsHighTech highTech = new OutProjectStatisticsHighTech(); BeanUtils.copyProperties(o,highTech); tl.add(highTech); } NewExcelUtil excelUtil=new NewExcelUtil<>(OutProjectStatisticsHighTech.class); res=excelUtil.exportExcel(tl,"项目汇总表(高新)",downloadPath); }else if (ib.getStatus()==2){//双软 List tl=new ArrayList<>(); for (OutProjectStatistics o : list) { OutProjectStatisticsDoubleSoft doubleSoft = new OutProjectStatisticsDoubleSoft(); BeanUtils.copyProperties(o,doubleSoft); tl.add(doubleSoft); } NewExcelUtil excelUtil=new NewExcelUtil<>(OutProjectStatisticsDoubleSoft.class); res=excelUtil.exportExcel(tl,"项目汇总表(双软)",downloadPath); }else if (ib.getStatus()==3){//软著 List tl=new ArrayList<>(); for (OutProjectStatistics o : list) { OutProjectStatisticsSoftwareWorks softwareWorks = new OutProjectStatisticsSoftwareWorks(); BeanUtils.copyProperties(o,softwareWorks); softwareWorks.setQuantityAndcount(softwareWorks.getCommodityQuantity()+"/"+softwareWorks.getCertificatesCount()); tl.add(softwareWorks); } NewExcelUtil excelUtil=new NewExcelUtil<>(OutProjectStatisticsSoftwareWorks.class); res=excelUtil.exportExcel(tl,"项目汇总表(软著)",downloadPath); }else if (ib.getStatus()==4){//软著 List tl=new ArrayList<>(); for (OutProjectStatistics o : list) { OutProjectStatisticsPatent patent = new OutProjectStatisticsPatent(); BeanUtils.copyProperties(o,patent); patent.setQuantityAndcount(patent.getCommodityQuantity()+"/"+patent.getCertificatesCount()); tl.add(patent); } NewExcelUtil excelUtil=new NewExcelUtil<>(OutProjectStatisticsPatent.class); res=excelUtil.exportExcel(tl,"项目汇总表(专利)",downloadPath); } return res; } /** * 处理搜索条件 * @param ib * @return */ private String getInputString(InputProjectStatistics ib) { String str="搜索条件:"; int i=1; if(ib.getStartDate()!=null) { str=StringUtils.join(str,i++,"-派单时间:",ib.getStartDate(),"~",ib.getEndDate()," "); } if(ib.getScreen()!=null){ //筛选项 0库存 1 派单 2 完成 3立项 4抽查 5未通过 6受理 7完结 8授权 9驳回 10下证 11申请 String screenName=""; if (ib.getScreen()==0)screenName=StringUtils.join("库存"); else if (ib.getScreen()==1)screenName=StringUtils.join("派单"); else if (ib.getScreen()==2)screenName=StringUtils.join("完成"); else if (ib.getScreen()==3)screenName=StringUtils.join("立项"); else if (ib.getScreen()==4)screenName=StringUtils.join("抽查"); else if (ib.getScreen()==5)screenName=StringUtils.join("未通过"); else if (ib.getScreen()==6)screenName=StringUtils.join("受理"); else if (ib.getScreen()==7)screenName=StringUtils.join("完结"); else if (ib.getScreen()==8)screenName=StringUtils.join("授权"); else if (ib.getScreen()==9)screenName=StringUtils.join("驳回"); else if (ib.getScreen()==10)screenName=StringUtils.join("下证"); else if (ib.getScreen()==11)screenName=StringUtils.join("申请"); str=StringUtils.join(str,i++,"-筛选分类:",screenName," "); } if(ib.getProvinceName()!=null)str=StringUtils.join(str,i++,"-省份:",ib.getProvinceName()," "); if(ib.getDepId()!=null)str=StringUtils.join(str,i++,"-订单部门:",ib.getDepName()," "); if(ib.getThchDepId()!=null)str=StringUtils.join(str,i++,"-责任部门:",ib.getThchDepName()," "); if(ib.getProjectStatus()!=null)str=StringUtils.join(str,i++,"-项目别类:",ib.getProjectStatusName()," "); if(ib.getProjectType()!=null)str=StringUtils.join(str,i++,"-项目分类:",ib.getProjectTypeName()," "); if(ib.getOrderNo()!=null)str=StringUtils.join(str,i++,"-订单编号:",ib.getOrderNo()," "); if(ib.getContractNo()!=null)str=StringUtils.join(str,i++,"-合同编号:",ib.getContractNo()," "); if(ib.getUserName()!=null)str=StringUtils.join(str,i++,"-客户名称:",ib.getUserName()," "); if(ib.getDeclarationBatch()!=null)str=StringUtils.join(str,i++,"-申报批次:",ib.getDeclarationBatch()," "); if(ib.getProjectSituation()!=null) { //0 正常 1专利 2软著 3审计 4双软 String str3=""; if (ib.getProjectSituation()==0) { str3="通用"; }else if (ib.getProjectAmount()==1) { str3="专利"; }else if (ib.getProjectAmount()==2) { str3="软著"; }else if (ib.getProjectAmount()==3) { str3="审计"; }else if (ib.getProjectAmount()==4) { str3="双软"; } str=StringUtils.join(str,i++,"-项目情况:",str3," "); } if(ib.getProjectAmount()!=null) { String str2=""; // 0~0.5万、0.5~1万、1~2万、2~5万、5~10万、10~30万、30~50万、50到80万、80万上 if (ib.getProjectAmount()==0) { str2="0~0.5万"; }else if (ib.getProjectAmount()==1) { str2="0~0.5万"; }else if (ib.getProjectAmount()==2) { str2="0.5~1万"; }else if (ib.getProjectAmount()==3) { str2="1~2万"; }else if (ib.getProjectAmount()==4) { str2="2~5万"; }else if (ib.getProjectAmount()==5) { str2="5~10万"; }else if (ib.getProjectAmount()==6) { str2="10~30万"; }else if (ib.getProjectAmount()==7) { str2="30~50万"; }else if (ib.getProjectAmount()==8) { str2="50到80万"; }else if (ib.getProjectAmount()==9) { str2="80万上"; } str=StringUtils.join(str,i++,"-项目金额:",str2," "); } if(i==1)str=StringUtils.join(str,"无"); return str; } @SuppressWarnings("unchecked") @Override public Pagination selectProvincialStatistics(InputProvincialStatistics is) { Map params= new HashedMap<>(); Integer pageNo=1,pageSize=10; if(is.getStartDate()!=null)params.put("startDate", is.getStartDate()); if(is.getEndDate()!=null)params.put("endDate", is.getEndDate()+" 23:59:59"); if(is.getThchDepId()!=null)params.put("thchDepId", is.getThchDepId()); if(is.getProjectStatus()!=null)params.put("projectStatus", is.getProjectStatus()); if(is.getProjectType()!=null)params.put("projectType", is.getProjectType()); if(is.getProvince()!=null)params.put("province", is.getProvince()); if(is.getProjectId()!=null)params.put("projectId", is.getProjectId()); params.put("status",is.getStatus()==null?0:is.getStatus()); if(is.getPageNo()!=null)pageNo=is.getPageNo(); if(is.getPageSize()!=null)pageSize=is.getPageSize(); Pagination p = (Pagination)findPage("selectProvincialStatisticsListByPage", "selectProvincialStatisticsCount", params, pageNo, pageSize); return p; } @Override public Result exporProvincialList(InputProvincialStatistics ib) { OutputStream out = null; XSSFWorkbook wb=null; try { if(ib.getPageSize()==null)ib.setPageSize(9999999); if(ib.getStatus()==null)ib.setStatus(0); @SuppressWarnings("unchecked") List list= (List) selectProvincialStatistics(ib).getList(); //派单賞份负责部门项目类別陸存数(个)派单数(个)下证数(个)签单额(万元)成本(万元)利润(万元) String[] comment = {"派单省份","负责部门","项目别类","库存数","派单数"}; List ls = new ArrayList(); Collections.addAll(ls, comment); if (ib.getStatus()==1) { //高新 //是否立项 立项时间 是否抽查 联系人电话 法人电话 证书编号 申报批次 String[] ss= {"完成数","立项数","抽查数","未通过"}; Collections.addAll(ls, ss); }else if (ib.getStatus()==2||ib.getStatus()==3) { //发明专利、实用新型 ls.add("受理数"); ls.add("完结数"); ls.add("授权数"); ls.add("驳回数"); }else if (ib.getStatus()==4) { //软著 ls.add("下证数"); }else if (ib.getStatus()==5) { //商标 String[] ss= {"申请数","驳回数","下证数"}; Collections.addAll(ls, ss); }else if (ib.getStatus()==0) { String[] ss= {"完成数","公示数"}; Collections.addAll(ls, ss); } String[] ss= {"签单额(万元)","成本(万元)","利润(万元)"}; Collections.addAll(ls, ss); comment= ls.toArray(new String[ls.size()]); // String fileName ="项目数据统计" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls"; String filename = UUID.randomUUID().toString() + "_ProvincialList.xlsx"; wb =ExportExcelUtil.exportTemplateInfoS(comment,"项目数据统计"); //根据列表编写文档 XSSFSheet sheet =wb.getSheetAt(0); XSSFRow xr = sheet.createRow(sheet.getLastRowNum()+1); //4.创建一个cell XSSFCell cell = xr.createCell(0); cell.setCellValue(getProvincialInputString(ib)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, comment.length-1));//合并单元格 for (OutProvincialStatistics ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o = ""; switch (comment[i]) { // "派单省份","负责部门","项目别类","库存数(个)","派单数(个)","下证数(个)","签单数(个)","成本(万元)","利润(万元)" case "派单省份": o = ob.getProvinceName(); break; case "负责部门": o = ob.getThchDepName(); break; case "项目别类": o = ob.getProjectStatus(); break; case "库存数": o = ob.getStockCount().toString(); break; case "派单数": o = ob.getDistributeCount().toString(); break; //"完成数","立项数","抽查数","未通过" case "完成数": o = ob.getCompleteCount().toString(); break; case "立项数": o = ob.getSetUpCount().toString(); break; case "抽查数": o = ob.getSpotCheckCount().toString(); break; case "未通过": o = ob.getFailedCount().toString(); break; //受理数 完结数 授权数 驳回数 case "受理数": o = ob.getAcceptCount().toString(); break; case "完结数": o = ob.getCompleteCount().toString(); break; case "授权数": o = ob.getAuthorizeCount().toString(); break; case "驳回数": o = ob.getRejectCount().toString(); break; //下证数 case "下证数": o = ob.getCertificatesCount().toString(); break; //"申请数","驳回数","下证数" case "申请数": o = ob.getApplyCount().toString(); break; //"完成数","公示/立项数" case "公示数": o = ob.getPublicityCount().toString(); break; case "签单额(万元)": o = ob.getAmountCount().stripTrailingZeros().toPlainString(); break; case "成本(万元)": o = ob.getCostAmount().stripTrailingZeros().toPlainString(); break; case "利润(万元)": o = ob.getProfit().stripTrailingZeros().toPlainString(); break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } String downloadPath = this.downloadPath+"/" + filename; File desc = new File(downloadPath); if (!desc.getParentFile().exists()) { desc.getParentFile().mkdirs(); } out=new FileOutputStream(downloadPath); // 返回数据流 wb.write(out); return new Result().data(filename); } catch (Exception e) { e.printStackTrace(); throw new BusinessException(new Error("表格生成错误。")); }finally { if (out != null) { try { out.close(); } catch (IOException e1) { e1.printStackTrace(); } } if (wb !=null){ try { wb.close(); } catch (IOException e1) { e1.printStackTrace(); } } } } private String getProvincialInputString(InputProvincialStatistics ib) { String str="搜索条件:"; int i=1; if(ib.getStartDate()!=null) str=StringUtils.join(str,i++,"-开始时间:",ib.getStartDate()," "); if(ib.getEndDate()!=null)str=StringUtils.join(str,i++,"-结束时间:",ib.getEndDate()," "); if(ib.getProvince()!=null)str=StringUtils.join(str,i++,"-省份:",ib.getProvinceName()," "); if(ib.getThchDepId()!=null)str=StringUtils.join(str,i++,"-责任部门:",ib.getThchDepName()," "); if(ib.getProjectStatus()!=null)str=StringUtils.join(str,i++,"-项目别类:",ib.getProjectStatusName()," "); if(ib.getProjectType()!=null)str=StringUtils.join(str,i++,"-项目分类:",ib.getProjectTypeName()," "); if(ib.getProjectId()!=null)str=StringUtils.join(str,i++,"-项目名称:",ib.getProjectName()," "); if(i==1)StringUtils.join(str,"无"); return str; } }