ProjectStatisticsServiceImpl.java 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384
  1. package com.goafanti.order.service.impl;
  2. import java.io.File;
  3. import java.io.FileOutputStream;
  4. import java.io.IOException;
  5. import java.io.OutputStream;
  6. import java.text.SimpleDateFormat;
  7. import java.util.*;
  8. import javax.servlet.http.HttpServletResponse;
  9. import javax.servlet.http.HttpServletResponseWrapper;
  10. import com.goafanti.common.bo.Result;
  11. import com.goafanti.common.utils.excel.NewExcelUtil;
  12. import com.goafanti.order.bo.*;
  13. import com.goafanti.order.bo.outStatistics.*;
  14. import groovy.util.logging.Log4j;
  15. import org.apache.commons.collections4.map.HashedMap;
  16. import org.apache.commons.lang3.StringUtils;
  17. import org.apache.poi.ss.util.CellRangeAddress;
  18. import org.apache.poi.xssf.usermodel.XSSFCell;
  19. import org.apache.poi.xssf.usermodel.XSSFRow;
  20. import org.apache.poi.xssf.usermodel.XSSFSheet;
  21. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  22. import org.springframework.beans.BeanUtils;
  23. import org.springframework.beans.factory.annotation.Value;
  24. import org.springframework.stereotype.Service;
  25. import com.goafanti.common.bo.Error;
  26. import com.goafanti.common.dao.TOrderTaskMapper;
  27. import com.goafanti.common.error.BusinessException;
  28. import com.goafanti.common.utils.ExportExcelUtil;
  29. import com.goafanti.core.mybatis.BaseMybatisDao;
  30. import com.goafanti.core.mybatis.page.Pagination;
  31. import com.goafanti.order.enums.NewProjectStatus;
  32. import com.goafanti.order.service.ProjectStatisticsService;
  33. @Service
  34. @Log4j
  35. public class ProjectStatisticsServiceImpl extends BaseMybatisDao<TOrderTaskMapper> implements ProjectStatisticsService {
  36. @Value("${upload.path}")
  37. private String downloadPath;
  38. @SuppressWarnings("unchecked")
  39. @Override
  40. public Pagination<OutProjectStatistics> selectTaskList(InputProjectStatistics ps) {
  41. Map<String, Object> params= new HashedMap<>();
  42. Integer pageNo=1,pageSize=10;
  43. if(ps.getStartDate()!=null)params.put("startDate", ps.getStartDate());
  44. if(ps.getEndDate()!=null)params.put("endDate", ps.getEndDate()+" 23:59:59");
  45. if(ps.getDepId()!=null)params.put("depId", ps.getDepId());
  46. if(ps.getThchDepId()!=null)params.put("thchDepId", ps.getThchDepId());
  47. if(ps.getProjectStatus()!=null)params.put("projectStatus", ps.getProjectStatus());
  48. if(ps.getProjectType()!=null)params.put("projectType", ps.getProjectType());
  49. if(ps.getOrderNo()!=null)params.put("orderNo", ps.getOrderNo());
  50. if(ps.getContractNo()!=null)params.put("contractNo", ps.getContractNo());
  51. if(ps.getUserName()!=null)params.put("userName", ps.getUserName());
  52. if(ps.getDeclarationBatch()!=null)params.put("declarationBatch", ps.getDeclarationBatch());
  53. if(ps.getProjectSituation()!=null)params.put("projectSituation", ps.getProjectSituation());
  54. if(ps.getProjectAmount()!=null) {
  55. // 0~0.5万、0.5~1万、1~2万、2~5万、5~10万、10~30万、30~50万、50到80万、80万上
  56. double s = 0,e=0;
  57. if (ps.getProjectAmount()==0) { s=0;e=0.5;
  58. }else if (ps.getProjectAmount()==1) {s=0;e=0.5;
  59. }else if (ps.getProjectAmount()==2) {s=0.5;e=1;
  60. }else if (ps.getProjectAmount()==3) {s=1;e=2;
  61. }else if (ps.getProjectAmount()==4) {s=2;e=5;
  62. }else if (ps.getProjectAmount()==5) {s=5;e=10;
  63. }else if (ps.getProjectAmount()==6) {s=10;e=30;
  64. }else if (ps.getProjectAmount()==7) {s=30;e=50;
  65. }else if (ps.getProjectAmount()==8) {s=50;e=80;
  66. }else if (ps.getProjectAmount()==9) {s=80;
  67. }
  68. params.put("projectAmount", ps.getProjectAmount());
  69. params.put("amountStart", s);
  70. params.put("amountEnd", e);
  71. }
  72. params.put("status",ps.getStatus()==null?0:ps.getStatus());
  73. if(ps.getProvince()!=null) params.put("province", ps.getProvince());
  74. if(ps.getProjectId()!=null) params.put("projectId", ps.getProjectId());
  75. if(ps.getScreen()!=null) params.put("screen", ps.getScreen());
  76. if(ps.getPageNo()!=null)pageNo=ps.getPageNo();
  77. if(ps.getPageSize()!=null)pageSize=ps.getPageSize();
  78. Pagination<OutProjectStatistics> p = (Pagination<OutProjectStatistics>)findPage("selectProjectStatisticsListByPage",
  79. "selectProjectStatisticsCount", params, pageNo, pageSize);
  80. return p;
  81. }
  82. @Override
  83. public Result exporTaskList( InputProjectStatistics ib,HttpServletResponse response) {
  84. if(ib.getPageSize()==null)ib.setPageSize(9999999);
  85. if(ib.getStatus()==null)ib.setStatus(0);
  86. @SuppressWarnings("unchecked")
  87. List<OutProjectStatistics> list= (List<OutProjectStatistics>) selectTaskList(ib).getList();
  88. Result res =new Result();
  89. if (ib.getStatus()==null||ib.getStatus()==0){
  90. List<OutProjectStatisticsCommon> tl=new ArrayList<>();
  91. for (OutProjectStatistics o : list) {
  92. OutProjectStatisticsCommon common = new OutProjectStatisticsCommon();
  93. BeanUtils.copyProperties(o,common);
  94. tl.add(common);
  95. }
  96. NewExcelUtil<OutProjectStatisticsCommon> excelUtil=new NewExcelUtil<>(OutProjectStatisticsCommon.class);
  97. res=excelUtil.exportExcel(tl,"项目汇总表(通用)",downloadPath);
  98. }else if (ib.getStatus()==1){//高新、科技
  99. List<OutProjectStatisticsHighTech> tl=new ArrayList<>();
  100. for (OutProjectStatistics o : list) {
  101. OutProjectStatisticsHighTech highTech = new OutProjectStatisticsHighTech();
  102. BeanUtils.copyProperties(o,highTech);
  103. tl.add(highTech);
  104. }
  105. NewExcelUtil<OutProjectStatisticsHighTech> excelUtil=new NewExcelUtil<>(OutProjectStatisticsHighTech.class);
  106. res=excelUtil.exportExcel(tl,"项目汇总表(高新)",downloadPath);
  107. }else if (ib.getStatus()==2){//双软
  108. List<OutProjectStatisticsDoubleSoft> tl=new ArrayList<>();
  109. for (OutProjectStatistics o : list) {
  110. OutProjectStatisticsDoubleSoft doubleSoft = new OutProjectStatisticsDoubleSoft();
  111. BeanUtils.copyProperties(o,doubleSoft);
  112. tl.add(doubleSoft);
  113. }
  114. NewExcelUtil<OutProjectStatisticsDoubleSoft> excelUtil=new NewExcelUtil<>(OutProjectStatisticsDoubleSoft.class);
  115. res=excelUtil.exportExcel(tl,"项目汇总表(双软)",downloadPath);
  116. }else if (ib.getStatus()==3){//软著
  117. List<OutProjectStatisticsSoftwareWorks> tl=new ArrayList<>();
  118. for (OutProjectStatistics o : list) {
  119. OutProjectStatisticsSoftwareWorks softwareWorks = new OutProjectStatisticsSoftwareWorks();
  120. BeanUtils.copyProperties(o,softwareWorks);
  121. softwareWorks.setQuantityAndcount(softwareWorks.getCommodityQuantity()+"/"+softwareWorks.getCertificatesCount());
  122. tl.add(softwareWorks);
  123. }
  124. NewExcelUtil<OutProjectStatisticsSoftwareWorks> excelUtil=new NewExcelUtil<>(OutProjectStatisticsSoftwareWorks.class);
  125. res=excelUtil.exportExcel(tl,"项目汇总表(软著)",downloadPath);
  126. }else if (ib.getStatus()==4){//软著
  127. List<OutProjectStatisticsPatent> tl=new ArrayList<>();
  128. for (OutProjectStatistics o : list) {
  129. OutProjectStatisticsPatent patent = new OutProjectStatisticsPatent();
  130. BeanUtils.copyProperties(o,patent);
  131. patent.setQuantityAndcount(patent.getCommodityQuantity()+"/"+patent.getCertificatesCount());
  132. tl.add(patent);
  133. }
  134. NewExcelUtil<OutProjectStatisticsPatent> excelUtil=new NewExcelUtil<>(OutProjectStatisticsPatent.class);
  135. res=excelUtil.exportExcel(tl,"项目汇总表(专利)",downloadPath);
  136. }
  137. return res;
  138. }
  139. /**
  140. * 处理搜索条件
  141. * @param ib
  142. * @return
  143. */
  144. private String getInputString(InputProjectStatistics ib) {
  145. String str="搜索条件:";
  146. int i=1;
  147. if(ib.getStartDate()!=null) {
  148. str=StringUtils.join(str,i++,"-派单时间:",ib.getStartDate(),"~",ib.getEndDate()," ");
  149. }
  150. if(ib.getScreen()!=null){
  151. //筛选项 0库存 1 派单 2 完成 3立项 4抽查 5未通过 6受理 7完结 8授权 9驳回 10下证 11申请
  152. String screenName="";
  153. if (ib.getScreen()==0)screenName=StringUtils.join("库存");
  154. else if (ib.getScreen()==1)screenName=StringUtils.join("派单");
  155. else if (ib.getScreen()==2)screenName=StringUtils.join("完成");
  156. else if (ib.getScreen()==3)screenName=StringUtils.join("立项");
  157. else if (ib.getScreen()==4)screenName=StringUtils.join("抽查");
  158. else if (ib.getScreen()==5)screenName=StringUtils.join("未通过");
  159. else if (ib.getScreen()==6)screenName=StringUtils.join("受理");
  160. else if (ib.getScreen()==7)screenName=StringUtils.join("完结");
  161. else if (ib.getScreen()==8)screenName=StringUtils.join("授权");
  162. else if (ib.getScreen()==9)screenName=StringUtils.join("驳回");
  163. else if (ib.getScreen()==10)screenName=StringUtils.join("下证");
  164. else if (ib.getScreen()==11)screenName=StringUtils.join("申请");
  165. str=StringUtils.join(str,i++,"-筛选分类:",screenName," ");
  166. }
  167. if(ib.getProvinceName()!=null)str=StringUtils.join(str,i++,"-省份:",ib.getProvinceName()," ");
  168. if(ib.getDepId()!=null)str=StringUtils.join(str,i++,"-订单部门:",ib.getDepName()," ");
  169. if(ib.getThchDepId()!=null)str=StringUtils.join(str,i++,"-责任部门:",ib.getThchDepName()," ");
  170. if(ib.getProjectStatus()!=null)str=StringUtils.join(str,i++,"-项目别类:",ib.getProjectStatusName()," ");
  171. if(ib.getProjectType()!=null)str=StringUtils.join(str,i++,"-项目分类:",ib.getProjectTypeName()," ");
  172. if(ib.getOrderNo()!=null)str=StringUtils.join(str,i++,"-订单编号:",ib.getOrderNo()," ");
  173. if(ib.getContractNo()!=null)str=StringUtils.join(str,i++,"-合同编号:",ib.getContractNo()," ");
  174. if(ib.getUserName()!=null)str=StringUtils.join(str,i++,"-客户名称:",ib.getUserName()," ");
  175. if(ib.getDeclarationBatch()!=null)str=StringUtils.join(str,i++,"-申报批次:",ib.getDeclarationBatch()," ");
  176. if(ib.getProjectSituation()!=null) {
  177. //0 正常 1专利 2软著 3审计 4双软
  178. String str3="";
  179. if (ib.getProjectSituation()==0) { str3="通用";
  180. }else if (ib.getProjectAmount()==1) { str3="专利";
  181. }else if (ib.getProjectAmount()==2) { str3="软著";
  182. }else if (ib.getProjectAmount()==3) { str3="审计";
  183. }else if (ib.getProjectAmount()==4) { str3="双软";
  184. }
  185. str=StringUtils.join(str,i++,"-项目情况:",str3," ");
  186. }
  187. if(ib.getProjectAmount()!=null) {
  188. String str2="";
  189. // 0~0.5万、0.5~1万、1~2万、2~5万、5~10万、10~30万、30~50万、50到80万、80万上
  190. if (ib.getProjectAmount()==0) { str2="0~0.5万";
  191. }else if (ib.getProjectAmount()==1) { str2="0~0.5万";
  192. }else if (ib.getProjectAmount()==2) { str2="0.5~1万";
  193. }else if (ib.getProjectAmount()==3) { str2="1~2万";
  194. }else if (ib.getProjectAmount()==4) { str2="2~5万";
  195. }else if (ib.getProjectAmount()==5) { str2="5~10万";
  196. }else if (ib.getProjectAmount()==6) { str2="10~30万";
  197. }else if (ib.getProjectAmount()==7) { str2="30~50万";
  198. }else if (ib.getProjectAmount()==8) { str2="50到80万";
  199. }else if (ib.getProjectAmount()==9) { str2="80万上";
  200. }
  201. str=StringUtils.join(str,i++,"-项目金额:",str2," ");
  202. }
  203. if(i==1)str=StringUtils.join(str,"无");
  204. return str;
  205. }
  206. @SuppressWarnings("unchecked")
  207. @Override
  208. public Pagination<OutProvincialStatistics> selectProvincialStatistics(InputProvincialStatistics is) {
  209. Map<String, Object> params= new HashedMap<>();
  210. Integer pageNo=1,pageSize=10;
  211. if(is.getStartDate()!=null)params.put("startDate", is.getStartDate());
  212. if(is.getEndDate()!=null)params.put("endDate", is.getEndDate()+" 23:59:59");
  213. if(is.getThchDepId()!=null)params.put("thchDepId", is.getThchDepId());
  214. if(is.getProjectStatus()!=null)params.put("projectStatus", is.getProjectStatus());
  215. if(is.getProjectType()!=null)params.put("projectType", is.getProjectType());
  216. if(is.getProvince()!=null)params.put("province", is.getProvince());
  217. if(is.getProjectId()!=null)params.put("projectId", is.getProjectId());
  218. params.put("status",is.getStatus()==null?0:is.getStatus());
  219. if(is.getPageNo()!=null)pageNo=is.getPageNo();
  220. if(is.getPageSize()!=null)pageSize=is.getPageSize();
  221. Pagination<OutProvincialStatistics> p = (Pagination<OutProvincialStatistics>)findPage("selectProvincialStatisticsListByPage",
  222. "selectProvincialStatisticsCount", params, pageNo, pageSize);
  223. return p;
  224. }
  225. @Override
  226. public Result exporProvincialList(InputProvincialStatistics ib) {
  227. OutputStream out = null;
  228. XSSFWorkbook wb=null;
  229. try {
  230. if(ib.getPageSize()==null)ib.setPageSize(9999999);
  231. if(ib.getStatus()==null)ib.setStatus(0);
  232. @SuppressWarnings("unchecked")
  233. List<OutProvincialStatistics> list= (List<OutProvincialStatistics>) selectProvincialStatistics(ib).getList();
  234. //派单賞份负责部门项目类別陸存数(个)派单数(个)下证数(个)签单额(万元)成本(万元)利润(万元)
  235. String[] comment = {"派单省份","负责部门","项目别类","库存数","派单数"};
  236. List<String> ls = new ArrayList<String>();
  237. Collections.addAll(ls, comment);
  238. if (ib.getStatus()==1) {
  239. //高新
  240. //是否立项 立项时间 是否抽查 联系人电话 法人电话 证书编号 申报批次
  241. String[] ss= {"完成数","立项数","抽查数","未通过"};
  242. Collections.addAll(ls, ss);
  243. }else if (ib.getStatus()==2||ib.getStatus()==3) {
  244. //发明专利、实用新型
  245. ls.add("受理数");
  246. ls.add("完结数");
  247. ls.add("授权数");
  248. ls.add("驳回数");
  249. }else if (ib.getStatus()==4) {
  250. //软著
  251. ls.add("下证数");
  252. }else if (ib.getStatus()==5) {
  253. //商标
  254. String[] ss= {"申请数","驳回数","下证数"};
  255. Collections.addAll(ls, ss);
  256. }else if (ib.getStatus()==0) {
  257. String[] ss= {"完成数","公示数"};
  258. Collections.addAll(ls, ss);
  259. }
  260. String[] ss= {"签单额(万元)","成本(万元)","利润(万元)"};
  261. Collections.addAll(ls, ss);
  262. comment= ls.toArray(new String[ls.size()]);
  263. // String fileName ="项目数据统计" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls";
  264. String filename = UUID.randomUUID().toString() + "_ProvincialList.xlsx";
  265. wb =ExportExcelUtil.exportTemplateInfoS(comment,"项目数据统计");
  266. //根据列表编写文档
  267. XSSFSheet sheet =wb.getSheetAt(0);
  268. XSSFRow xr = sheet.createRow(sheet.getLastRowNum()+1);
  269. //4.创建一个cell
  270. XSSFCell cell = xr.createCell(0);
  271. cell.setCellValue(getProvincialInputString(ib));
  272. sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, comment.length-1));//合并单元格
  273. for (OutProvincialStatistics ob : list) {
  274. //新增一行
  275. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  276. for (int i = 0; i < comment.length; i++) {
  277. //当前列的只
  278. String o = "";
  279. switch (comment[i]) {
  280. // "派单省份","负责部门","项目别类","库存数(个)","派单数(个)","下证数(个)","签单数(个)","成本(万元)","利润(万元)"
  281. case "派单省份": o = ob.getProvinceName(); break;
  282. case "负责部门": o = ob.getThchDepName(); break;
  283. case "项目别类": o = ob.getProjectStatus(); break;
  284. case "库存数": o = ob.getStockCount().toString(); break;
  285. case "派单数": o = ob.getDistributeCount().toString(); break;
  286. //"完成数","立项数","抽查数","未通过"
  287. case "完成数": o = ob.getCompleteCount().toString(); break;
  288. case "立项数": o = ob.getSetUpCount().toString(); break;
  289. case "抽查数": o = ob.getSpotCheckCount().toString(); break;
  290. case "未通过": o = ob.getFailedCount().toString(); break;
  291. //受理数 完结数 授权数 驳回数
  292. case "受理数": o = ob.getAcceptCount().toString(); break;
  293. case "完结数": o = ob.getCompleteCount().toString(); break;
  294. case "授权数": o = ob.getAuthorizeCount().toString(); break;
  295. case "驳回数": o = ob.getRejectCount().toString(); break;
  296. //下证数
  297. case "下证数": o = ob.getCertificatesCount().toString(); break;
  298. //"申请数","驳回数","下证数"
  299. case "申请数": o = ob.getApplyCount().toString(); break;
  300. //"完成数","公示/立项数"
  301. case "公示数": o = ob.getPublicityCount().toString(); break;
  302. case "签单额(万元)": o = ob.getAmountCount().stripTrailingZeros().toPlainString(); break;
  303. case "成本(万元)": o = ob.getCostAmount().stripTrailingZeros().toPlainString(); break;
  304. case "利润(万元)": o = ob.getProfit().stripTrailingZeros().toPlainString(); break;
  305. default: o = ""; break;
  306. }
  307. XSSFCell c = r.createCell(i);
  308. c.setCellValue(o);
  309. }
  310. }
  311. String downloadPath = this.downloadPath+"/" + filename;
  312. File desc = new File(downloadPath);
  313. if (!desc.getParentFile().exists()) {
  314. desc.getParentFile().mkdirs();
  315. }
  316. out=new FileOutputStream(downloadPath);
  317. // 返回数据流
  318. wb.write(out);
  319. return new Result().data(filename);
  320. } catch (Exception e) {
  321. e.printStackTrace();
  322. throw new BusinessException(new Error("表格生成错误。"));
  323. }finally {
  324. if (out != null) {
  325. try {
  326. out.close();
  327. } catch (IOException e1) {
  328. e1.printStackTrace();
  329. }
  330. }
  331. if (wb !=null){
  332. try {
  333. wb.close();
  334. } catch (IOException e1) {
  335. e1.printStackTrace();
  336. }
  337. }
  338. }
  339. }
  340. private String getProvincialInputString(InputProvincialStatistics ib) {
  341. String str="搜索条件:";
  342. int i=1;
  343. if(ib.getStartDate()!=null) str=StringUtils.join(str,i++,"-开始时间:",ib.getStartDate()," ");
  344. if(ib.getEndDate()!=null)str=StringUtils.join(str,i++,"-结束时间:",ib.getEndDate()," ");
  345. if(ib.getProvince()!=null)str=StringUtils.join(str,i++,"-省份:",ib.getProvinceName()," ");
  346. if(ib.getThchDepId()!=null)str=StringUtils.join(str,i++,"-责任部门:",ib.getThchDepName()," ");
  347. if(ib.getProjectStatus()!=null)str=StringUtils.join(str,i++,"-项目别类:",ib.getProjectStatusName()," ");
  348. if(ib.getProjectType()!=null)str=StringUtils.join(str,i++,"-项目分类:",ib.getProjectTypeName()," ");
  349. if(ib.getProjectId()!=null)str=StringUtils.join(str,i++,"-项目名称:",ib.getProjectName()," ");
  350. if(i==1)StringUtils.join(str,"无");
  351. return str;
  352. }
  353. }