OrderReceivablesServiceImpl.java 31 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736
  1. package com.goafanti.order.service.impl;
  2. import java.io.IOException;
  3. import java.io.InputStream;
  4. import java.io.OutputStream;
  5. import java.math.BigDecimal;
  6. import java.text.SimpleDateFormat;
  7. import java.util.ArrayList;
  8. import java.util.Date;
  9. import java.util.HashMap;
  10. import java.util.List;
  11. import java.util.Map;
  12. import java.util.UUID;
  13. import javax.annotation.Resource;
  14. import javax.servlet.http.HttpServletResponse;
  15. import org.apache.commons.lang3.StringUtils;
  16. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  17. import org.apache.poi.ss.usermodel.Cell;
  18. import org.apache.poi.ss.usermodel.Row;
  19. import org.apache.poi.ss.usermodel.Sheet;
  20. import org.apache.poi.ss.usermodel.Workbook;
  21. import org.apache.poi.ss.util.CellRangeAddress;
  22. import org.apache.poi.xssf.usermodel.XSSFCell;
  23. import org.apache.poi.xssf.usermodel.XSSFCellStyle;
  24. import org.apache.poi.xssf.usermodel.XSSFRow;
  25. import org.apache.poi.xssf.usermodel.XSSFSheet;
  26. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  27. import org.springframework.beans.factory.annotation.Autowired;
  28. import org.springframework.stereotype.Service;
  29. import org.springframework.web.multipart.MultipartFile;
  30. import com.goafanti.common.bo.Error;
  31. import com.goafanti.common.constant.AFTConstants;
  32. import com.goafanti.common.dao.TOrderBillNewMapper;
  33. import com.goafanti.common.dao.TOrderBonusMapper;
  34. import com.goafanti.common.dao.TOrderLogMapper;
  35. import com.goafanti.common.dao.TOrderMidMapper;
  36. import com.goafanti.common.dao.TOrderNewMapper;
  37. import com.goafanti.common.dao.TOrderTaskMapper;
  38. import com.goafanti.common.dao.TemporaryReceivablesMapper;
  39. import com.goafanti.common.enums.OrderLogProcess;
  40. import com.goafanti.common.error.BusinessException;
  41. import com.goafanti.common.model.TOrderBillNew;
  42. import com.goafanti.common.model.TOrderBonus;
  43. import com.goafanti.common.model.TOrderLog;
  44. import com.goafanti.common.model.TOrderNew;
  45. import com.goafanti.common.model.TOrderTask;
  46. import com.goafanti.common.model.TemporaryReceivables;
  47. import com.goafanti.common.utils.DateUtils;
  48. import com.goafanti.common.utils.ExportExcelUtil;
  49. import com.goafanti.common.utils.LoggerUtils;
  50. import com.goafanti.core.mybatis.BaseMybatisDao;
  51. import com.goafanti.core.mybatis.page.Pagination;
  52. import com.goafanti.core.shiro.token.TokenManager;
  53. import com.goafanti.order.bo.OrderListBo;
  54. import com.goafanti.order.bo.ReceivablesListBo;
  55. import com.goafanti.order.bo.TOrderInvoiceBo;
  56. import com.goafanti.order.bo.TOrderNewBo;
  57. import com.goafanti.order.bo.TemporaryReceivablesBo;
  58. import com.goafanti.order.enums.ApprovalNewState;
  59. import com.goafanti.order.enums.BonusSubject;
  60. import com.goafanti.order.enums.LiquidationNewState;
  61. import com.goafanti.order.enums.ProcessStatus;
  62. import com.goafanti.order.enums.ReceivablesStatus;
  63. import com.goafanti.order.enums.TransactionSubject;
  64. import com.goafanti.order.service.FundManageOrderService;
  65. import com.goafanti.order.service.OrderBillService;
  66. import com.goafanti.order.service.OrderInvoiceService;
  67. import com.goafanti.order.service.OrderReceivablesService;
  68. @Service
  69. public class OrderReceivablesServiceImpl extends BaseMybatisDao<TemporaryReceivablesMapper> implements OrderReceivablesService{
  70. @Autowired
  71. private TemporaryReceivablesMapper temporaryReceivablesMapper;
  72. @Autowired
  73. private TOrderBillNewMapper tOrderBillNewMapper;
  74. @Autowired
  75. private TOrderNewMapper tOrderNewMapper;
  76. @Autowired
  77. private TOrderBonusMapper tOrderBonusMapper;
  78. @Autowired
  79. private TOrderLogMapper tOrderLogMapper;
  80. @Autowired
  81. private TOrderTaskMapper tOrderTaskMapper;
  82. @Autowired
  83. private TOrderMidMapper tOrderMidMapper;
  84. @Resource
  85. private FundManageOrderService fundManageOrderService;
  86. @Resource
  87. private OrderInvoiceService orderInvoiceService;
  88. @Resource
  89. private OrderBillService orderBillService;
  90. @Override
  91. public int addReceivables(TemporaryReceivablesBo t) {
  92. t.setAid(TokenManager.getAdminId());
  93. if(t.getCorporateName()==null)t.setCorporateName("");
  94. t.setCreateTime(new Date());
  95. t.setStatus(0);
  96. if(t.getReceivablesTimes()!=null) t.setReceivablesTime(DateUtils.StringToDate(t.getReceivablesTimes(), AFTConstants.YYYYMMDD));
  97. return temporaryReceivablesMapper.insertSelective(t);
  98. }
  99. @Override
  100. public int updateReceivables(TemporaryReceivablesBo t) {
  101. t.setOrderNo(t.getOrderNo().trim());
  102. t.setStatus(ReceivablesStatus.ZC.getCode());
  103. if(t.getReceivablesTimes()!=null) {
  104. t.setReceivablesTime(DateUtils.StringToDate(t.getReceivablesTimes(), AFTConstants.YYYYMMDD));
  105. }
  106. return temporaryReceivablesMapper.updateByPrimaryKeySelective(t);
  107. }
  108. @Override
  109. public int deleteReceivables(String ids) {
  110. List<String> delList = new ArrayList<String>();
  111. String[] strs = ids.split(",");
  112. for (String str : strs) {
  113. delList.add(str);
  114. }
  115. return temporaryReceivablesMapper.deleteByids(delList);
  116. }
  117. @Override
  118. public int deleteAllReceivables() {
  119. return temporaryReceivablesMapper.deleteByaid(TokenManager.getAdminId());
  120. }
  121. @Override
  122. public int batchImport() {
  123. List<TemporaryReceivables> list=temporaryReceivablesMapper.selectByaid(TokenManager.getAdminId());
  124. if(list.size()>99)throw new BusinessException(new Error("数量过大,建议不超过99。"));
  125. List<TOrderBillNew> listBull=new ArrayList<>();
  126. for (TemporaryReceivables tr : list) {
  127. TOrderBillNew tbn=new TOrderBillNew();
  128. //获取已收款
  129. BigDecimal bill = tOrderBillNewMapper.getAmountByOid(tr.getOrderNo());
  130. //获取总金额
  131. TOrderNewBo t=tOrderNewMapper.getOrderNewDetail(tr.getOrderNo());
  132. //订单匹配不到
  133. if(t==null||t.getOrderNo()==null) {
  134. tr.setStatus(ReceivablesStatus.DDYC.getCode());
  135. temporaryReceivablesMapper.updateByPrimaryKeySelective(tr);
  136. continue;
  137. }else if(t.getDeleteSign()==2||t.getDeleteSign()==3) {
  138. tr.setStatus(ReceivablesStatus.BGSD.getCode());
  139. temporaryReceivablesMapper.updateByPrimaryKeySelective(tr);
  140. continue;
  141. }else if(!tr.getCorporateName().equals(t.getUserName())) {
  142. tr.setStatus(ReceivablesStatus.MCYC.getCode());
  143. temporaryReceivablesMapper.updateByPrimaryKeySelective(tr);
  144. continue;
  145. }else if(temporaryReceivablesMapper.selectReceivablesNumberRepeats(tr)>0) {
  146. tr.setStatus(ReceivablesStatus.YSK.getCode());
  147. temporaryReceivablesMapper.updateByPrimaryKeySelective(tr);
  148. continue;
  149. }
  150. //计算收款结果
  151. BigDecimal bd = bill.add(tr.getAmount());
  152. //判断表中数据
  153. BigDecimal count = new BigDecimal(0).add(bd);
  154. for (TOrderBillNew tBill : listBull) {
  155. if (tBill.getOrderNo().equals(tr.getOrderNo())) {
  156. count=count.add(tBill.getTransactionAmount());
  157. continue;
  158. }
  159. }
  160. TOrderBonus bonus = new TOrderBonus();
  161. bonus.setId(UUID.randomUUID().toString());
  162. bonus.setOrderNo(tr.getOrderNo());
  163. bonus.setGrantBy(TokenManager.getAdminId());
  164. int lstaus = 0;//如果订单流水小于首付 lstaus 0
  165. if(count.compareTo(t.getTotalAmount()) == 1){//如果订单流水总额大于总金额 return -1
  166. tr.setStatus(ReceivablesStatus.JEYC.getCode());
  167. temporaryReceivablesMapper.updateByPrimaryKeySelective(tr);
  168. continue;
  169. }else if (count.compareTo(t.getTotalAmount()) == 0) {//等于总额
  170. //付完款//如果订单流水等于于总金额 lstaus 2
  171. lstaus = 2;
  172. bonus.setBonusSubject(BonusSubject.WK.getCode());//12
  173. //发放给技术员如果付完款所有参与这个项目任务的人都会有结算奖
  174. //获得所有的任务负责人
  175. List<TOrderTask> l = tOrderTaskMapper.getReceiverByOid(tr.getOrderNo());
  176. for (TOrderTask tOrderTask : l) {
  177. if(null != tOrderTask &&null != tOrderTask.getTaskReceiver()){
  178. bonus.setTaskId(tOrderTask.getId());//任务id
  179. bonus.setGrantTarget(tOrderTask.getTaskReceiver());
  180. bonus.setGrantType(1);
  181. int c = tOrderBonusMapper.checkByOnoAndSub(bonus);
  182. //不存在就设置
  183. if(c==0){
  184. bonus.setId(UUID.randomUUID().toString());
  185. tOrderBonusMapper.insertSelective(bonus);
  186. }
  187. }
  188. }
  189. if(tr.getStatus()==2) {
  190. tr.setStatus(0);
  191. temporaryReceivablesMapper.updateByPrimaryKeySelective(tr);
  192. }
  193. addOrderLog(tr.getOrderNo(), OrderLogProcess.CWQK.getCode());
  194. }else if (count.compareTo(t.getTotalAmount()) == -1) {//小于总额
  195. //如果订单流水小于总金额
  196. if(bd.compareTo(t.getFirstAmount()) == 0 || bd.compareTo(t.getFirstAmount()) == 1){//首付完成
  197. //查看首付奖是否存在
  198. TOrderBonus b = new TOrderBonus();
  199. b.setOrderNo(tr.getOrderNo());
  200. b.setBonusSubject(11);
  201. b.setGrantType(0);
  202. b.setGrantTarget(t.getSalesmanId());
  203. int c = tOrderBonusMapper.checkByOnoAndSub(b);
  204. //不存在就设置
  205. if(c==0){
  206. bonus.setBonusSubject(BonusSubject.SF.getCode());//11
  207. }
  208. lstaus = 1;
  209. addOrderLog(tr.getOrderNo(), OrderLogProcess.CWSF.getCode());
  210. }
  211. }
  212. if(tr.getStatus()==2) {
  213. tr.setStatus(0);
  214. temporaryReceivablesMapper.updateByPrimaryKeySelective(tr);
  215. }
  216. //否则新增流水信息
  217. Date date=new Date();
  218. tbn.setBillNo(UUID.randomUUID().toString().replaceAll("-", ""));
  219. tbn.setCreater(TokenManager.getAdminId());
  220. tbn.setType(1);//批量
  221. tbn.setOrderNo(t.getOrderNo());
  222. tbn.setCreateTime(date);
  223. tbn.setPayeeId("0");//默认设置0
  224. tbn.setPayerId(t.getBuyerId());
  225. tbn.setTransactionSubject(0);
  226. tbn.setTransactionChannel(2);
  227. tbn.setConfirmSign(0);
  228. tbn.setTransactionAmount(tr.getAmount());
  229. tbn.setDeleteSign((new Byte("0")));
  230. tbn.setFinancialPayTime(tr.getReceivablesTime());
  231. tbn.setFinancialPayNo(tr.getFinancialPayNo());
  232. tbn.setRemarks(tr.getRemarks());
  233. tOrderMidMapper.updatefinalReceivables(t.getOrderNo(),tr.getAmount(),date);
  234. //修改订单结算金额和结算状态
  235. //如果已经首付并且流程状态是没有派给咨询师的修改流程状态为4
  236. TOrderNew orderNew = new TOrderNew();
  237. orderNew.setOrderNo(tr.getOrderNo());
  238. //这里的已收金额是以前收的所有金额加上本次收的金额
  239. orderNew.setSettlementAmount(count);
  240. orderNew.setLiquidationStatus(lstaus);
  241. tOrderNewMapper.updateByPrimaryKeySelective(orderNew);
  242. //如果达到奖金发放要求新增奖金信息
  243. if(null != bonus.getBonusSubject()){
  244. bonus.setId(UUID.randomUUID().toString());
  245. bonus.setGrantTarget(t.getSalesmanId());
  246. //设置发放人类型
  247. bonus.setGrantType(0);
  248. tOrderBonusMapper.insertSelective(bonus);
  249. }
  250. listBull.add(tbn);
  251. }
  252. if(!listBull.isEmpty()) {
  253. tOrderBillNewMapper.insertBatch(listBull);
  254. temporaryReceivablesMapper.deleteNormalByaid(TokenManager.getAdminId());
  255. }else {
  256. return -1;
  257. }
  258. LoggerUtils.debug(getClass(), "=========================批量导入结束===================");
  259. return 1;
  260. }
  261. private void addOrderLog(String orderNo, Integer code) {
  262. TOrderLog tl=new TOrderLog();
  263. tl.setOrderNo(orderNo);
  264. tl.setProcess(code);
  265. tl.setAid(TokenManager.getAdminId());
  266. tOrderLogMapper.insertSelective(tl);
  267. }
  268. @SuppressWarnings("unchecked")
  269. @Override
  270. public Pagination<ReceivablesListBo> listReceivables(ReceivablesListBo rl,Integer status,String adminName, String adminDepId, String orderDepId, Integer pageSize,
  271. Integer pageNo) {
  272. Map<String,Object> parameter=new HashMap<String, Object>();
  273. if(pageSize==null||pageSize<1)pageSize=10;
  274. if(pageNo==null||pageNo<1)pageNo=1;
  275. if(StringUtils.isNotBlank(rl.getContractNo()))parameter.put("contractNo", rl.getContractNo());
  276. if(StringUtils.isNotBlank(rl.getOrderNo()))parameter.put("orderNo", rl.getOrderNo());
  277. if(StringUtils.isNotBlank(rl.getNickname()))parameter.put("nickname", rl.getNickname());
  278. if(StringUtils.isNotBlank(rl.getCorporateName()))parameter.put("corporateName", rl.getCorporateName());
  279. if(StringUtils.isNotBlank(adminName))parameter.put("adminName", adminName);
  280. if(StringUtils.isNotBlank(adminDepId))parameter.put("adminDepId", adminDepId);
  281. if(StringUtils.isNotBlank(orderDepId))parameter.put("orderDepId", orderDepId);
  282. if(null!=status)parameter.put("status", status);
  283. if(StringUtils.isNotBlank(TokenManager.getAdminId()))parameter.put("aid", TokenManager.getAdminId());
  284. Pagination<ReceivablesListBo> p=(Pagination<ReceivablesListBo>) findPage("selectReceivablesList", "selectReceivablesCount", parameter, pageNo, pageSize);
  285. List<ReceivablesListBo> list=(List<ReceivablesListBo>) p.getList();
  286. for (ReceivablesListBo r : list) {
  287. //设置导入信息数据是否有问题判断
  288. // InptStatus 0 正常 1内容不完整 2订单未能匹配 3名称不匹配
  289. if (StringUtils.isBlank(r.getAmount())||StringUtils.isBlank(r.getOrderNo())
  290. ||StringUtils.isBlank(r.getCorporateName())||r.getReceivablesTimes()==null) {
  291. r.setInptStatus(1);
  292. }else if(r.getTotalAmount()==null) {
  293. r.setInptStatus(2);
  294. }else if(!r.getCorporateName().equals(r.getNickname())) {
  295. r.setInptStatus(3);
  296. }else {
  297. r.setInptStatus(0);
  298. }
  299. }
  300. return p;
  301. }
  302. @Override
  303. public Map<String, Object> getCountlistReceivables(ReceivablesListBo rl, Integer status, String adminName,
  304. String adminDepId, String orderDepId) {
  305. Map<String,Object> parameter=new HashMap<String, Object>();
  306. if(StringUtils.isNotBlank(rl.getContractNo()))parameter.put("contractNo", rl.getContractNo());
  307. if(StringUtils.isNotBlank(rl.getOrderNo()))parameter.put("orderNo", rl.getOrderNo());
  308. if(StringUtils.isNotBlank(rl.getNickname()))parameter.put("nickname", rl.getNickname());
  309. if(StringUtils.isNotBlank(rl.getCorporateName()))parameter.put("corporateName", rl.getCorporateName());
  310. if(StringUtils.isNotBlank(adminName))parameter.put("adminName", adminName);
  311. if(StringUtils.isNotBlank(adminDepId))parameter.put("adminDepId", adminDepId);
  312. if(StringUtils.isNotBlank(orderDepId))parameter.put("orderDepId", orderDepId);
  313. if(null!=status)parameter.put("status", status);
  314. if(StringUtils.isNotBlank(TokenManager.getAdminId()))parameter.put("aid", TokenManager.getAdminId());
  315. //计算回收表的订单总金额与当前总收款额。
  316. Map<String,Object> map=new HashMap<String, Object>();
  317. map.put("totalAmount", new BigDecimal(temporaryReceivablesMapper.getCountlistReceivablesTotalAmount(parameter)).stripTrailingZeros() );
  318. map.put("amount",new BigDecimal(temporaryReceivablesMapper.getCountlistReceivablesAmount(parameter)).stripTrailingZeros() );
  319. return map;
  320. }
  321. @SuppressWarnings("deprecation")
  322. @Override
  323. public void batchListReceivables(MultipartFile file) {
  324. //获得Workbook工作薄对象
  325. Workbook workbook = getWorkBook(file);
  326. //创建一个对象,用来存储数据
  327. List<TemporaryReceivablesBo> list=new ArrayList<>();
  328. if(workbook != null){
  329. //获得当前sheet工作表
  330. Sheet sheet = workbook.getSheetAt(0);
  331. if(sheet == null){
  332. return;
  333. }
  334. //获得当前sheet的开始行
  335. int firstRowNum = sheet.getFirstRowNum();
  336. //获得当前sheet的结束行
  337. int lastRowNum = sheet.getLastRowNum();
  338. //处理空白行
  339. for (int i = firstRowNum; i <= sheet.getLastRowNum();i++) {
  340. Row r = sheet.getRow(i);
  341. if(r == null){
  342. // 如果是空行(即没有任何数据、格式),直接把它以下的数据往上移动
  343. sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
  344. continue;
  345. }
  346. boolean flag = false;
  347. for(Cell c:r){
  348. if(c.getCellType() != Cell.CELL_TYPE_BLANK){
  349. flag = true;
  350. break;
  351. }
  352. }
  353. if(flag){
  354. i++;
  355. continue;
  356. } else{//如果是空白行(即可能没有数据,但是有一定格式)
  357. if(i == sheet.getLastRowNum())//如果到了最后一行,直接将那一行remove掉
  358. sheet.removeRow(r);
  359. else//如果还没到最后一行,则数据往上移一行
  360. sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
  361. }
  362. }
  363. lastRowNum=sheet.getLastRowNum();
  364. if (lastRowNum<2) {
  365. throw new BusinessException(new Error("未找到正确的参数。"));
  366. }
  367. //循环除了第2行的所有行
  368. for(int rowNum = firstRowNum+2;rowNum <= lastRowNum;rowNum++){
  369. //获得当前行
  370. Row row = sheet.getRow(rowNum);
  371. if(row == null){
  372. continue;
  373. }
  374. //获得当前行的开始列
  375. int firstCellNum = row.getFirstCellNum();
  376. //获得当前行的列数
  377. int lastCellNum = row.getLastCellNum();
  378. //循环当前行 cellNum = (firstCellNum+1) 则是除开第一例
  379. TemporaryReceivablesBo trb=new TemporaryReceivablesBo();
  380. for(int cellNum = (firstCellNum); cellNum < lastCellNum;cellNum++){
  381. Cell cell = row.getCell(cellNum);
  382. try {
  383. switch (cellNum) {
  384. case 0:
  385. trb.setFinancialPayNo(getCellValue(cell));
  386. break;
  387. case 1:
  388. trb.setOrderNo(getCellValue(cell).trim());
  389. break;
  390. case 2:
  391. trb.setCorporateName(getCellValue(cell));
  392. break;
  393. case 3:
  394. trb.setAmount(new BigDecimal((getCellValue(cell).equals("")?"0":getCellValue(cell))));
  395. break;
  396. case 4:
  397. trb.setReceivablesTime(DateUtils.StringToDate(getCellValue(cell), AFTConstants.YYYYMMDD));
  398. break;
  399. case 5:
  400. trb.setRemarks(getCellValue(cell));
  401. break;
  402. }
  403. } catch (Exception e) {
  404. throw new BusinessException(new Error("表格第"+(rowNum+1)+"行输入内容不正确。"));
  405. }
  406. }
  407. trb.setAid(TokenManager.getAdminId());
  408. trb.setCreateTime(new Date());
  409. trb.setStatus(0);
  410. list.add(trb);
  411. }
  412. }
  413. temporaryReceivablesMapper.insertBatch(list);
  414. //计算重复的单
  415. temporaryReceivablesMapper.updateRepeatOrderNo(TokenManager.getAdminId());
  416. }
  417. public static Workbook getWorkBook(MultipartFile file) {
  418. //创建Workbook工作薄对象,表示整个excel
  419. Workbook workbook = null;
  420. try {
  421. //获取excel文件的io流
  422. InputStream is = file.getInputStream();
  423. //如果是xls,使用HSSFWorkbook;如果是xlsx,使用XSSFWorkbook
  424. if(file.getOriginalFilename().matches("^.+\\.(?i)(xls)$"))workbook = new HSSFWorkbook(is);
  425. if(file.getOriginalFilename().matches("^.+\\.(?i)(xlsx)$"))workbook = new XSSFWorkbook(is);
  426. } catch (IOException e) {
  427. }
  428. return workbook;
  429. }
  430. @SuppressWarnings("deprecation")
  431. public static String getCellValue(Cell cell){
  432. String cellValue = "";
  433. if(cell == null){
  434. return cellValue;
  435. }
  436. //把数字当成String来读,避免出现1读成1.0的情况
  437. if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
  438. cell.setCellType(Cell.CELL_TYPE_STRING);
  439. }
  440. //判断数据的类型
  441. switch (cell.getCellType()){
  442. case Cell.CELL_TYPE_NUMERIC: //数字
  443. cellValue = String.valueOf(cell.getNumericCellValue());
  444. break;
  445. case Cell.CELL_TYPE_STRING: //字符串
  446. cellValue = String.valueOf(cell.getStringCellValue());
  447. break;
  448. case Cell.CELL_TYPE_BOOLEAN: //Boolean
  449. cellValue = String.valueOf(cell.getBooleanCellValue());
  450. break;
  451. case Cell.CELL_TYPE_FORMULA: //公式
  452. cellValue = String.valueOf(cell.getCellFormula());
  453. break;
  454. case Cell.CELL_TYPE_BLANK: //空值
  455. cellValue = "";
  456. break;
  457. case Cell.CELL_TYPE_ERROR: //故障
  458. cellValue = "非法字符";
  459. break;
  460. default:
  461. cellValue = "未知类型";
  462. break;
  463. }
  464. return cellValue;
  465. }
  466. @SuppressWarnings("unchecked")
  467. @Override
  468. public void exportReceivables(HttpServletResponse response,OrderListBo order, Integer pageSize, Integer pageNo) throws Exception {
  469. OutputStream out = response.getOutputStream();
  470. // 财务专员自己的id
  471. order.setFinanceId(TokenManager.getAdminId());
  472. //order.setProcessStatus(3);//分配给自己 改为前端 传3未分配 4已分配
  473. order.setOrderStatus(2);//营销管理员审核通过
  474. if(pageSize==null||pageSize<0)pageSize=9999;
  475. if(pageNo==null||pageNo<0)pageNo=1;
  476. Pagination<OrderListBo> p=(Pagination<OrderListBo>) fundManageOrderService.financeList(order, pageNo, pageSize).get("pagination");
  477. List<OrderListBo> list=(List<OrderListBo>)p.getList() ;
  478. if(list.isEmpty())throw new BusinessException(new Error("数据是空的。","数据是空的。"));
  479. try {
  480. //设计表头
  481. String[] comment = {"订单编号", "合同编号","客户名称","订单部门","订单负责人","签单日期","下单日期",
  482. "签单金额(万元)", "已开票金额(万元)", "已收金额(万元)","最近收款(万元)","最近收款时间","结算状态","订单流程","是否特批"};
  483. String fileName = "收款结算列表" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls";
  484. XSSFWorkbook wb =exportTemplateInfoS(comment,"收款结算",list,0);
  485. //根据列表编写文档
  486. //返回名称及参数
  487. response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),"iso-8859-1"));
  488. response.setContentType("application/octet-stream;charset=utf-8");
  489. // 返回数据流
  490. wb.write(out);
  491. out.flush();
  492. out.close();
  493. } catch (Exception e) {
  494. throw new BusinessException(new Error("表格生成错误。"));
  495. }finally {
  496. out.flush();
  497. out.close();
  498. }
  499. }
  500. @SuppressWarnings("unchecked")
  501. @Override
  502. public void exportInvoice(HttpServletResponse response, String orderNo, Integer pageSize, Integer pageNo)throws Exception {
  503. OutputStream out = response.getOutputStream();
  504. if(pageSize==null||pageSize<0)pageSize=9999;
  505. if(pageNo==null||pageNo<0)pageNo=1;
  506. List<TOrderInvoiceBo>list=(List<TOrderInvoiceBo>) orderInvoiceService.salesmanOrderInvoiceList( orderNo, pageNo,pageSize).getList();
  507. // if(list.isEmpty())throw new BusinessException(new Error("数据是空的。"));
  508. try {
  509. //设计表头
  510. String[] comment = {"编号","订单编号","开票金额(万元)","申请时间","开票状态"};
  511. String fileName = orderNo+"开票记录列表" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls";
  512. //type 0 收款 1开票 2流水
  513. XSSFWorkbook wb =exportTemplateInfoS(comment,"开票记录",list,1);
  514. //根据列表编写文档
  515. //返回名称及参数
  516. response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),"iso-8859-1"));
  517. response.setContentType("application/octet-stream;charset=utf-8");
  518. // 返回数据流
  519. wb.write(out);
  520. out.flush();
  521. out.close();
  522. } catch (Exception e) {
  523. throw new BusinessException(new Error("表格生成错误。"));
  524. }finally {
  525. out.flush();
  526. out.close();
  527. }
  528. }
  529. @SuppressWarnings("unchecked")
  530. @Override
  531. public void exportMyBill(HttpServletResponse response, TOrderBillNew billNew, Integer pageSize, Integer pageNo)throws Exception {
  532. OutputStream out = response.getOutputStream();
  533. if(pageSize==null||pageSize<0)pageSize=9999;
  534. if(pageNo==null||pageNo<0)pageNo=1;
  535. List<TOrderBillNew>list=(List<TOrderBillNew>) orderBillService.myBillList(billNew,pageNo, pageSize).getList();
  536. try {
  537. //设计表头
  538. String[] comment = {"订单编号","合同编号","收款时间","财务流水时间","财务流水号","金额(万元)","收款方","付款方","流水科目","平台流水号","收款类型","备注"};
  539. String fileName = (billNew.getOrderNo()==null?"":billNew.getOrderNo())+"流水记录列表" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls";
  540. //type 0 收款 1开票 2流水
  541. XSSFWorkbook wb =exportTemplateInfoS(comment,"流水记录",list,2);
  542. //根据列表编写文档
  543. //返回名称及参数
  544. response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),"iso-8859-1"));
  545. response.setContentType("application/octet-stream;charset=utf-8");
  546. // 返回数据流
  547. wb.write(out);
  548. out.flush();
  549. out.close();
  550. } catch (Exception e) {
  551. throw new BusinessException(new Error("表格生成错误。"));
  552. }finally {
  553. out.flush();
  554. out.close();
  555. }
  556. }
  557. /**
  558. *
  559. * @param comment
  560. * @param fileName
  561. * @param list
  562. * @param type 0收款 1开票 2流水
  563. * @return
  564. */
  565. public static XSSFWorkbook exportTemplateInfoS(String[] comment,String fileName,List<?> list,Integer type){
  566. //map 数据 ,comment表头 (列名称)
  567. //1,创建一个workbook,对应的是一个excel文件
  568. XSSFWorkbook wb = new XSSFWorkbook();
  569. //遍历map
  570. XSSFSheet sheet = wb.createSheet(fileName);
  571. //3。创建一个row
  572. XSSFRow row = sheet.createRow(0);
  573. //4.创建一个cell
  574. XSSFCell cell = row.createCell(0);
  575. cell.setCellValue(fileName+"列表");
  576. XSSFCellStyle style1 = ExportExcelUtil.setCellStyle1(wb);//标题
  577. XSSFCellStyle style2 = ExportExcelUtil.setCellStyle2(wb);//正文
  578. cell.setCellStyle(style1);
  579. //合并列
  580. sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格
  581. //设置其他单位格式
  582. //创建表头
  583. XSSFRow row2 = sheet.createRow(1);
  584. for(int i = 0; i < comment.length; i++){
  585. sheet.setColumnWidth(i, 3000);//设置列宽
  586. XSSFCell c = row2.createCell(i);
  587. c.setCellValue(comment[i]);
  588. c.setCellStyle(style2);
  589. }
  590. switch (type) {
  591. case 0:
  592. addSettlementReceivablesList(comment, list, sheet);
  593. break;
  594. case 1:
  595. addSettlementInvoiceList(comment, list, sheet);
  596. break;
  597. case 2:
  598. addSettlementMyBillList(comment, list, sheet);
  599. break;
  600. }
  601. return wb;
  602. }
  603. @SuppressWarnings("unchecked")
  604. private static void addSettlementMyBillList(String[] comment, List<?> list, XSSFSheet sheet) {
  605. for (TOrderBillNew ob : (List<TOrderBillNew>)list) {
  606. //新增一行
  607. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  608. for (int i = 0; i < comment.length; i++) {
  609. //当前列的只
  610. String o = "";
  611. switch (comment[i]) {
  612. // String[] comment = {"订单编号","合同编号","收款时间","财务流水时间","财务流水号","金额(万元)","收款方","付款方","流水科目","平台流水号","收款类型","备注"};
  613. case "订单编号": o = ob.getOrderNo(); break;
  614. case "合同编号": o = ob.getContractNo(); break;
  615. case "收款时间": o = ob.getCreateTimes(); break;
  616. case "财务流水时间": o = ob.getFinancialPayTimes(); break;
  617. case "财务流水号": o = ob.getFinancialPayNo(); break;
  618. case "金额(万元)": o = ob.getTransactionAmount().toString(); break;
  619. case "收款方": o = ob.getPayeeName(); break;
  620. case "付款方": o = ob.getPayerName(); break;
  621. case "流水科目": o = TransactionSubject.getValueByCode(ob.getTransactionSubject()); break;
  622. case "平台流水号": o = ob.getBillNo(); break;
  623. case "收款类型":
  624. if(ob.getType()==0) {
  625. o = "手工收款"; break;
  626. }else if(ob.getType()==1) {
  627. o = "批量收款"; break;
  628. }
  629. case "备注": o = ob.getRemarks(); break;
  630. default: o = ""; break;
  631. }
  632. XSSFCell c = r.createCell(i);
  633. c.setCellValue(o);
  634. }
  635. }
  636. }
  637. @SuppressWarnings("unchecked")
  638. private static void addSettlementInvoiceList(String[] comment, List<?> list, XSSFSheet sheet) {
  639. for (TOrderInvoiceBo ob : (List<TOrderInvoiceBo>)list) {
  640. //新增一行
  641. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  642. for (int i = 0; i < comment.length; i++) {
  643. //当前列的只
  644. String o = "";
  645. switch (comment[i]) {
  646. // String[] comment = {"编号","订单编号","开票金额(万元)","申请时间","开票状态"};
  647. case "编号": o = ob.getId().toString(); break;
  648. case "订单编号": o = ob.getOrderno(); break;
  649. case "开票金额(万元)": o = ob.getAmount().toString(); break;
  650. case "申请时间": o = ob.getCreateTime(); break;
  651. case "开票状态":
  652. // 状态 0未提交 1处理中 2通过 3拒绝 4 撤销
  653. switch(ob.getStatus()) {
  654. case 1 :o = "处理中"; break;
  655. case 2 :o = "通过"; break;
  656. }
  657. break;
  658. default: o = ""; break;
  659. }
  660. XSSFCell c = r.createCell(i);
  661. c.setCellValue(o);
  662. }
  663. }
  664. }
  665. @SuppressWarnings("unchecked")
  666. private static void addSettlementReceivablesList(String[] comment, List<?> list, XSSFSheet sheet) {
  667. for (OrderListBo ob : (List<OrderListBo>)list) {
  668. //新增一行
  669. XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
  670. for (int i = 0; i < comment.length; i++) {
  671. //当前列的只
  672. String o = "";
  673. switch (comment[i]) {
  674. // "订单编号", "合同编号","客户名称","订单部门","订单负责人","签单日期","下单日期",
  675. // "签单金额(万元)", "已开票金额(万元)", "已收金额(万元)","最近收款(万元)","结算状态","订单流程","是否特批"}
  676. case "订单编号": o = ob.getOrderNo().toString(); break;
  677. case "合同编号": o = ob.getContractNo(); break;
  678. case "客户名称": o = ob.getBuyerName(); break;
  679. case "订单部门": o = ob.getDepartmentName(); break;
  680. case "订单负责人": o = ob.getSellerName(); break;
  681. case "签单日期": o = ob.getSignTime(); break;
  682. case "下单日期": o = ob.getCreateTime(); break;
  683. case "签单金额(万元)":o=ob.getSignTotalAmount();break;
  684. case "已开票金额(万元)":o=ob.getInvoiceAmount();break;
  685. case "已收金额(万元)":o=ob.getActuallyTotalAmount();break;
  686. case "最近收款(万元)":o=ob.getFinalReceivables();break;
  687. case "最近收款时间":o=ob.getFinalReceivablesTime();break;
  688. case "结算状态":o=LiquidationNewState.getValueByCode(ob.getLiquidationStatus());break;
  689. case "订单流程":o=ProcessStatus.getValueByCode(ob.getProcessStatus());break;
  690. case "是否特批":o=ApprovalNewState.getValueByCode(ob.getApproval());break;
  691. default: o = ""; break;
  692. }
  693. XSSFCell c = r.createCell(i);
  694. c.setCellValue(o);
  695. }
  696. }
  697. }
  698. }