TemplateGroupServiceImpl.java 37 KB
Newer Older
1 2
package pwc.taxtech.atms.service.impl;

eddie.woo's avatar
eddie.woo committed
3 4
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
eddie.woo's avatar
eddie.woo committed
5
import com.google.common.collect.Maps;
eddie.woo's avatar
eddie.woo committed
6 7
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
kevin's avatar
kevin committed
8
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
9
import org.apache.poi.ss.usermodel.*;
eddie.woo's avatar
eddie.woo committed
10
import org.springframework.beans.factory.annotation.Autowired;
11
import org.springframework.stereotype.Service;
12
import org.springframework.transaction.annotation.Propagation;
eddie.woo's avatar
eddie.woo committed
13
import org.springframework.transaction.annotation.Transactional;
14
import org.springframework.util.Assert;
eddie.woo's avatar
eddie.woo committed
15
import org.springframework.web.multipart.MultipartFile;
frank.xa.zhang's avatar
frank.xa.zhang committed
16
import pwc.taxtech.atms.common.CommonUtils;
eddie.woo's avatar
eddie.woo committed
17 18
import pwc.taxtech.atms.common.POIUtil;
import pwc.taxtech.atms.common.message.ErrorMessage;
kevin's avatar
kevin committed
19
import pwc.taxtech.atms.common.message.ErrorMessageCN;
eddie.woo's avatar
eddie.woo committed
20
import pwc.taxtech.atms.common.message.TemplateMessage;
eddie.woo's avatar
eddie.woo committed
21
import pwc.taxtech.atms.constant.Constant;
22
import pwc.taxtech.atms.constant.enums.CellDataSourceType;
23
import pwc.taxtech.atms.constant.enums.FileUploadEnum;
frank.xa.zhang's avatar
frank.xa.zhang committed
24
import pwc.taxtech.atms.constant.enums.TemplateGroupType;
kevin's avatar
kevin committed
25
import pwc.taxtech.atms.dao.*;
26
import pwc.taxtech.atms.dto.OperationResultDto;
frank.xa.zhang's avatar
frank.xa.zhang committed
27
import pwc.taxtech.atms.dto.TemplateGroupDto;
kevin's avatar
kevin committed
28
import pwc.taxtech.atms.entity.*;
29
import pwc.taxtech.atms.exception.ServiceException;
kevin's avatar
#  
kevin committed
30
import pwc.taxtech.atms.vat.dao.EbitCellDataMapper;
31
import pwc.taxtech.atms.vat.dao.PeriodDataSourceMapper;
kevin's avatar
kevin committed
32
import pwc.taxtech.atms.vat.entity.*;
33

kevin's avatar
kevin committed
34
import javax.annotation.Resource;
eddie.woo's avatar
eddie.woo committed
35
import java.io.ByteArrayOutputStream;
kevin's avatar
kevin committed
36
import java.io.IOException;
eddie.woo's avatar
eddie.woo committed
37
import java.io.InputStream;
38 39
import java.math.BigDecimal;
import java.util.*;
40
import java.util.stream.Collectors;
frank.xa.zhang's avatar
frank.xa.zhang committed
41

42
@Service
43
public class TemplateGroupServiceImpl extends AbstractService {
eddie.woo's avatar
eddie.woo committed
44
    @Autowired
eddie.woo's avatar
eddie.woo committed
45
    private HttpFileService httpFileService;
eddie.woo's avatar
eddie.woo committed
46 47 48 49 50 51 52
    @Autowired
    private TemplateGroupDao templateGroupDao;
    @Autowired
    private TemplateGroupMapper templateGroupMapper;
    @Autowired
    private TemplateMapper templateMapper;
    @Autowired
eddie.woo's avatar
eddie.woo committed
53 54
    private TemplateDao templateDao;
    @Autowired
eddie.woo's avatar
eddie.woo committed
55 56
    private CellTemplateMapper cellTemplateMapper;
    @Autowired
eddie.woo's avatar
eddie.woo committed
57 58
    private CellTemplateDao cellTemplateDao;
    @Autowired
eddie.woo's avatar
eddie.woo committed
59
    private CellTemplateConfigMapper cellTemplateConfigMapper;
eddie.woo's avatar
eddie.woo committed
60 61
    @Autowired
    private CellTemplateConfigDao cellTemplateConfigDao;
62 63
    @Autowired
    private PeriodDataSourceMapper periodDataSourceMapper;
64 65
    @Autowired
    private DidiFileUploadService didiFileUploadService;
kevin's avatar
kevin committed
66

kevin's avatar
#  
kevin committed
67 68 69
    @Autowired
    protected DistributedIdService idService;

70
    public List<TemplateGroupDto> get() {
frank.xa.zhang's avatar
frank.xa.zhang committed
71 72 73 74 75 76 77 78 79 80
        List<TemplateGroup> templateGroups = templateGroupMapper.selectByExample(new TemplateGroupExample());
        List<TemplateGroupDto> templateGroupDtos = new ArrayList<>();
        for (TemplateGroup templateGroup : templateGroups) {
            TemplateGroupDto templateGroupDto = new TemplateGroupDto();
            CommonUtils.copyProperties(templateGroup, templateGroupDto);
            templateGroupDtos.add(templateGroupDto);
        }
        return templateGroupDtos;
    }

81
    public List<TemplateGroupDto> get(int serviceTypeId, Integer taxPayType, String industryId) {
frank.xa.zhang's avatar
frank.xa.zhang committed
82 83 84 85
        TemplateGroupExample example = new TemplateGroupExample();

        TemplateGroupExample.Criteria criteria = example.createCriteria();

86
        criteria.andServiceTypeIdEqualTo(String.valueOf(serviceTypeId)).andGroupTypeEqualTo(TemplateGroupType.TaxReturn.getCode());
frank.xa.zhang's avatar
frank.xa.zhang committed
87 88 89 90 91 92 93 94 95 96 97 98 99 100

        if (taxPayType != null) {
            criteria.andPayTaxTypeEqualTo(TemplateGroupType.TaxReturn.getCode());
        }

        List<TemplateGroup> templateGroups = templateGroupMapper.selectByExample(example);
        List<TemplateGroupDto> templateGroupDtos = new ArrayList<>();
        for (TemplateGroup templateGroup : templateGroups) {
            TemplateGroupDto dto = new TemplateGroupDto();
            CommonUtils.copyProperties(templateGroup, dto);
            templateGroupDtos.add(dto);
        }
        return templateGroupDtos;
    }
101

102
    public OperationResultDto<Object> updateTemplateGroupName(TemplateGroupDto templateGroupDto) {
gary's avatar
gary committed
103
        TemplateGroup entity = templateGroupMapper.selectByPrimaryKey(templateGroupDto.getId());
104
        TemplateGroupExample example = new TemplateGroupExample();
gary's avatar
gary committed
105
        example.createCriteria().andNameEqualTo(templateGroupDto.getName()).andIdNotEqualTo(templateGroupDto.getId()).andServiceTypeIdEqualTo(entity.getServiceTypeId()).andIndustryIdsEqualTo(entity.getIndustryIds()).andPayTaxTypeEqualTo(entity.getPayTaxType());
106 107 108 109
        List<TemplateGroup> templateGroups = templateGroupMapper.selectByExample(example);
        if (!templateGroups.isEmpty()) {
            OperationResultDto<Object> result = new OperationResultDto<>();
            result.setResult(false);
110
            result.setResultMsg(TemplateGroupMessage.TEMPLATE_GROUP_NAME_EXIST);
111 112 113 114 115 116 117 118
        }
        entity.setName(templateGroupDto.getName());
        templateGroupMapper.updateByPrimaryKey(entity);
        OperationResultDto<Object> result = new OperationResultDto<>();
        result.setResult(true);
        return result;
    }

119
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
120 121
    public OperationResultDto<Object> deleteTemplateGroup(TemplateGroupDto templateGroupDto) {
        OperationResultDto<Object> result = new OperationResultDto<>();
gary's avatar
gary committed
122
        TemplateGroup templateGroupDb = templateGroupMapper.selectByPrimaryKey(templateGroupDto.getId());
123 124 125 126 127 128 129

        if (templateGroupDb.getIsSystemType()) {
            result.setResult(false);
            result.setResultMsg(TemplateGroupMessage.SYSTEM_TYPE_CANNOT_DELETE);
            return result;
        }

gary's avatar
gary committed
130
        List<String> userOranizationNameList = organizationServiceTemplateGroupMapper.getOrgnizationServiceTemplateGroupOrgNames(templateGroupDto.getId());
131 132 133 134 135 136 137
        if (userOranizationNameList != null && !userOranizationNameList.isEmpty()) {
            result.setResult(false);
            result.setResultMsg(TemplateGroupMessage.ORGANIZATION_USED_TEMPLATE_GROUP);
            return result;
        }

        List<Template> allTemplateDbList = templateMapper.selectByExample(new TemplateExample());
138
        List<Template> templateDbList = allTemplateDbList.stream().filter(a -> a.getTemplateGroupId().equals(templateGroupDb.getId())).collect(Collectors.toList());
139 140 141
        List<String> pathList = new ArrayList<>();

        for (Template templateDb : templateDbList) {
142
            boolean anySameCodeExists = allTemplateDbList.stream().anyMatch(a -> a.getCode() == templateDb.getCode() && a.getName() == templateDb.getCode());
143 144 145 146 147 148

            if (!anySameCodeExists) {
                pathList.add((templateDb.getPath()));
            }
            deleteTemplate(templateDb);
        }
149
        templateGroupMapper.deleteByPrimaryKey(templateGroupDb.getId());
150 151 152 153 154 155 156

        result.setResult(true);
        result.setData(pathList);
        return result;
    }

    private void deleteTemplate(Template templateDb) {
157 158
        cellTemplateConfigMapper.deleteCellTemplateConfigByCellTemplate(templateDb.getId());
        keyValueReferenceMapper.deleteKeyValueReferenceByCellTemplate(templateDb.getId());
159
        CellTemplateExample example = new CellTemplateExample();
160
        example.createCriteria().andReportTemplateIdEqualTo(templateDb.getId());
161 162 163
        cellTemplateMapper.deleteByExample(example);
    }

kevin's avatar
kevin committed
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201
    private boolean isSheetEmpty(Sheet sheet) {
        if (sheet.getLastRowNum() > 0 &&
                (null == sheet.getRow(0).getCell(0) || "Version".equals(getCellStringValue(sheet.getRow(0).getCell(0)))) &&
                null == sheet.getRow(0).getCell(2)) {
            return true;
        }
        return false;
    }


    private String getCellStringValue(Cell cell) {
        if (null == cell) {
            return "";
        }
        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
            return cell.getStringCellValue().trim();
        } else if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
            // 取整
            return String.valueOf((int) cell.getNumericCellValue()).trim();
        }
        logger.warn("获取单元格数据类型未匹配");
        return null;
    }

    @Resource
    private ProjectMapper projectMapper;

    private String getProjId(String orgId, Integer tmsPeriod) {
        ProjectExample pExample = new ProjectExample();
        pExample.createCriteria().andOrganizationIdEqualTo(orgId).andYearEqualTo(tmsPeriod / 100);
        List<Project> pList = projectMapper.selectByExample(pExample);
        if (!pList.isEmpty()) {
            return pList.get(0).getId();
        }
        return "";
    }


kevin's avatar
#  
kevin committed
202 203 204 205
    @Autowired
    private EbitCellDataMapper ebitCellDataMapper;
    public OperationResultDto ebitTemplateImport(MultipartFile file, String orgId, Integer period) {
        OperationResultDto operationResultDto = new OperationResultDto();
kevin's avatar
kevin committed
206 207 208 209 210 211 212 213 214 215 216 217 218
        InputStream inputStream = null;
        Date now = new Date();
        String projectId = getProjId(orgId, period);
        try {
            inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                List<EbitCellData> list = Lists.newArrayList();
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                List<InvoiceData> cfs = Lists.newArrayList();
                String[] sheetName = sheet.getSheetName().split("-");
                String taypayerNum = sheetName[0];
kevin's avatar
#  
kevin committed
219 220
                String titleValue = sheet.getRow(0).getCell(2).getStringCellValue();
                if (titleValue.indexOf("利润表") == -1)
kevin's avatar
kevin committed
221 222 223 224
                    throw new ServiceException(ErrorMessageCN.ErrorFileFormat);
                if (period == null) {
                    throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
                }
kevin's avatar
#  
kevin committed
225 226 227 228 229 230 231
                //判断是否已经上传
                EbitCellDataExample example = new EbitCellDataExample();
                EbitCellDataExample.Criteria criteria = example.createCriteria();
                criteria.andPeriodEqualTo(period).andOrganizationIdEqualTo(orgId);
                if(ebitCellDataMapper.selectByExample(example).size() != 0){
                    ebitCellDataMapper.deleteByExample(example);
                }
kevin's avatar
kevin committed
232
                for (int j = 1; j <= sheet.getRow(0).getLastCellNum(); j++) {
kevin's avatar
#  
kevin committed
233
                    for (int m = 1; m <= sheet.getLastRowNum(); m++) {
kevin's avatar
#  
kevin committed
234
                        if (j == 1 || j == 2) {
kevin's avatar
kevin committed
235
                            EbitCellData ebitCellData = new EbitCellData();
kevin's avatar
#  
kevin committed
236
                            ebitCellData.setId(idService.nextId());
kevin's avatar
kevin committed
237 238 239
                            ebitCellData.setCol(j);
                            ebitCellData.setRow(m);
                            ebitCellData.setCreateTime(now);
kevin's avatar
#  
kevin committed
240
                            try {
kevin's avatar
#  
kevin committed
241 242 243 244 245 246 247 248
                                if(sheet.getRow(m).getCell(j) != null){
                                    if(CellType.STRING == sheet.getRow(m).getCell(j).getCellTypeEnum()){
                                        ebitCellData.setData(sheet.getRow(m).getCell(j).getStringCellValue());
                                    }else if (CellType.NUMERIC  == sheet.getRow(m).getCell(j).getCellTypeEnum()){
                                        ebitCellData.setData(String.valueOf(sheet.getRow(m).getCell(j).getNumericCellValue()));
                                    }
                                }

kevin's avatar
#  
kevin committed
249 250 251
                            }catch (Exception e){
                                //遇到空行,略过
                            }
kevin's avatar
#  
kevin committed
252
                            ebitCellData.setTemplateId(String.valueOf(idService.nextId()));
kevin's avatar
kevin committed
253
                            ebitCellData.setProjectId(projectId);
kevin's avatar
#  
kevin committed
254 255
                            ebitCellData.setOrganizationId(orgId);
                            ebitCellData.setPeriod(period);
kevin's avatar
kevin committed
256 257 258 259
                            list.add(ebitCellData);
                        }
                    }
                }
kevin's avatar
#  
kevin committed
260
                ebitCellDataMapper.insertBatch(list);
kevin's avatar
kevin committed
261
            }
kevin's avatar
#  
kevin committed
262
            return  operationResultDto.success();
kevin's avatar
#  
kevin committed
263
        } catch (Exception e) {
kevin's avatar
kevin committed
264
            e.printStackTrace();
kevin's avatar
#  
kevin committed
265
            return  operationResultDto.error(e.getMessage());
kevin's avatar
kevin committed
266 267 268
        }
    }

269
    public class TemplateGroupMessage {
270 271 272
        static final String TEMPLATE_GROUP_NAME_EXIST = "TemplateGroupNameExist";
        static final String SYSTEM_TYPE_CANNOT_DELETE = "SystemTypeCannotDelete";
        static final String ORGANIZATION_USED_TEMPLATE_GROUP = "OrganizationUsedTemplateGroup";
273 274
    }

eddie.woo's avatar
eddie.woo committed
275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
    public List<String> getSheetNameList(MultipartFile file) {
        try {
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            List<String> nameList = Lists.newArrayList();
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                nameList.add(workbook.getSheetName(i));
            }
            return nameList;
        } catch (Exception e) {
            logger.error("getSheetNameList error.", e);
        }
        return Collections.emptyList();
    }

    @Transactional
290
    public void importTemplateGroupExcelFile(MultipartFile file, boolean allowManual, TemplateGroupDto templateGroupDto) throws ServiceException {
eddie.woo's avatar
eddie.woo committed
291 292 293 294 295 296 297 298 299 300 301 302 303
        List<String> sheetNameList = templateGroupDto.getSheetNameList();
        if (CollectionUtils.isEmpty(sheetNameList)) {
            throw new ServiceException(ErrorMessage.NoSelectSheet);
        }
        try {
            List<TemplateGroup> groupList = templateGroupDao.getByGroupName(templateGroupDto.getName());
            if (CollectionUtils.isNotEmpty(groupList)) {
                throw new ServiceException(TemplateMessage.TemplateGroupNameExist);
            }
            InputStream inputStream = file.getInputStream();
            String fileName = file.getOriginalFilename();
            Workbook workbook = WorkbookFactory.create(inputStream);
            List<Template> filePathList = Lists.newArrayList();
304
            Long templateGroupId = distributedIdService.nextId();
eddie.woo's avatar
eddie.woo committed
305 306 307
            Date now = new Date();
            TemplateGroup templateGroup = new TemplateGroup();
            CommonUtils.copyProperties(templateGroupDto, templateGroup);
308
            templateGroup.setId(templateGroupId);
eddie.woo's avatar
eddie.woo committed
309
            templateGroup.setGroupType(1);//todo 整理枚举
310
            templateGroup.setCopyFrom(0L);
eddie.woo's avatar
eddie.woo committed
311 312 313
            templateGroup.setIsSystemType(false);
            templateGroup.setUpdateTime(now);
            templateGroupMapper.insertSelective(templateGroup);
frank.xa.zhang's avatar
frank.xa.zhang committed
314
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
eddie.woo's avatar
eddie.woo committed
315
                String sheetName = workbook.getSheetName(i);
kevin's avatar
kevin committed
316
                if (!sheetNameList.contains(sheetName)) {
sherlock's avatar
sherlock committed
317 318
                    continue;
                }
eddie.woo's avatar
eddie.woo committed
319 320 321 322 323 324 325 326
                String newName = sheetName + CommonUtils.getUUID() + POIUtil.getFileSuffix(fileName).get();
                Sheet sheet = workbook.getSheetAt(i);
                Optional<Workbook> optional = POIUtil.cloneNewSheet(sheet, fileName);
                if (!optional.isPresent()) {
                    throw new ServiceException(ErrorMessage.SystemError);
                }
                ByteArrayOutputStream bos = new ByteArrayOutputStream();
                optional.get().write(bos);
327
                //上传didi服务器文件
kevin's avatar
kevin committed
328
                FileUpload fileUpload = didiFileUploadService.uploadFile(file, file.getOriginalFilename(), FileUploadEnum.BizSource.REPORT_TEMPLATE_UPLOAD.name());
329
//                String tmpPath = httpFileService.uploadTemplate(newName, file);
eddie.woo's avatar
eddie.woo committed
330 331 332 333
                String[] arr = sheetName.split("_");
                String name = arr.length >= 2 ? arr[1] : arr[0];
                Template template = new Template();
                template.setCode(arr[0]);
334
                template.setReportType(templateGroupDto.getReportType());
eddie.woo's avatar
eddie.woo committed
335
                template.setCreateTime(now);
336
                template.setId(distributedIdService.nextId());
eddie.woo's avatar
eddie.woo committed
337 338 339 340
                template.setIsActiveAssociation(true);
                template.setIsSystemType(false);
                template.setName(name);
                template.setOrderIndex(i + 1);
341
                template.setPath(fileUpload.getUid());
342
                template.setTemplateGroupId(templateGroupId);
eddie.woo's avatar
eddie.woo committed
343 344 345 346
                filePathList.add(template);
                templateMapper.insertSelective(template);
                List<CellTemplate> cellTemplateList = Lists.newArrayList();
                List<CellTemplateConfig> cellTemplateConfigList = Lists.newArrayList();
347
                List<PeriodDataSource> periodDataSourceList = Lists.newArrayList();
frank.xa.zhang's avatar
frank.xa.zhang committed
348
                for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
eddie.woo's avatar
eddie.woo committed
349
                    Row row = sheet.getRow(r);
Memorydoc's avatar
#  
Memorydoc committed
350 351
                    if(row == null)
                        continue;
eddie.woo's avatar
eddie.woo committed
352 353 354 355 356 357 358 359 360 361
                    for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
                        Cell cell = row.getCell(c);
                        if (cell == null) {
                            continue;//todo cell == null 如何处理
                        }
                        CellTemplate cellTemplate = new CellTemplate();
                        cellTemplate.setColumnIndex(c);
                        cellTemplate.setCreateTime(now);
                        cellTemplate.setUpdateTime(now);
                        cellTemplate.setRowIndex(r);
chase's avatar
chase committed
362
                        //税金计算表单独处理
kevin's avatar
kevin committed
363
                        if ("VAT020".equals(template.getCode())) {
chase's avatar
chase committed
364
                            cellTemplate.setRowName(POIUtil.getCellFormulaString(cell));
kevin's avatar
kevin committed
365
                        } else {
chase's avatar
chase committed
366 367
                            cellTemplate.setRowName(StringUtils.EMPTY);
                        }
frank.xa.zhang's avatar
frank.xa.zhang committed
368 369 370 371 372 373
                        cellTemplate.setColumnName(StringUtils.EMPTY);
                        cellTemplate.setComment(StringUtils.EMPTY);
                        cellTemplate.setCopyFromId(0L);
                        cellTemplate.setCreateBy("Admin");
                        cellTemplate.setUpdateBy("Admin");
                        cellTemplate.setDataType(0);
374
                        cellTemplate.setReportTemplateId(template.getId());
375
                        cellTemplate.setId(distributedIdService.nextId());
eddie.woo's avatar
eddie.woo committed
376 377 378
                        if (cell.getCellComment() != null) {
                            cellTemplate.setComment(cell.getCellComment().getString().getString());
                        }
frank.xa.zhang's avatar
frank.xa.zhang committed
379
                        cellTemplate.setIsReadOnly(cell.getCellStyle().getLocked());
eddie.woo's avatar
eddie.woo committed
380
                        cellTemplateList.add(cellTemplate);
eddie.woo's avatar
eddie.woo committed
381
                        //todo: 这里没有Config数据只有在上传模板以后,在界面里面可以配置公式
382
                        if (!cell.getCellStyle().getLocked() && hasKeyIn(cell)) {
eddie.woo's avatar
eddie.woo committed
383
                            cell.setCellValue(StringUtils.EMPTY);
kevin's avatar
kevin committed
384
                            if (allowManual) {
385 386
                                addManualConfig(cellTemplate, template, cell, now, cellTemplateConfigList);
                            }
eddie.woo's avatar
eddie.woo committed
387
                        }
kevin's avatar
kevin committed
388
                        if (!cell.getCellStyle().getLocked() && StringUtils.isNotBlank(POIUtil.getCellFormulaString(cell))) {
389 390 391 392
                            CellTemplateConfig config = new CellTemplateConfig();
                            config.setId(distributedIdService.nextId());
                            config.setCellTemplateId(cellTemplate.getId());
                            config.setReportTemplateId(template.getId());
393
                            config.setDataSourceType(CellDataSourceType.Formula.getCode());//todo 枚举
394 395
                            config.setFormula(POIUtil.getCellFormulaString(cell));
//                        config.setFormula(cell.getCellFormula());
396
                            config.setFormulaDataSource("报表数据"); //todo KV相关
397 398 399
                            config.setUpdateTime(now);
                            config.setUpdateBy(authUserHelper.getCurrentUserId());
                            cellTemplateConfigList.add(config);
400 401 402 403
                            if (allowManual) {
                                addManualConfig(cellTemplate, template, cell, now, cellTemplateConfigList);
                            }

404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419
                            PeriodDataSource pds = new PeriodDataSource();
                            pds.setAmount(BigDecimal.ZERO);
                            pds.setUpdateBy("Admin");
                            pds.setUpdateTime(new Date());
                            pds.setId(distributedIdService.nextId());
                            pds.setCreateTime(new Date());
                            pds.setCellTemplateId(cellTemplate.getId());
                            pds.setCreateBy("Admin");
                            pds.setDescription(" ");
                            pds.setName("ReportDataSource");
                            Calendar cal = Calendar.getInstance();
                            pds.setPeriod(cal.get(Calendar.MONTH) + 1);
                            pds.setType(5);
                            pds.setColumnIndex(cell.getColumnIndex());
                            pds.setRowIndex(cell.getRowIndex());
                            periodDataSourceList.add(pds);
420
                        }
eddie.woo's avatar
eddie.woo committed
421 422 423
                    }
                }
                List<List<CellTemplate>> tmpList = CommonUtils.subListWithLen(cellTemplateList, CommonUtils.BATCH_NUM);
frank.xa.zhang's avatar
frank.xa.zhang committed
424
                tmpList.forEach(list -> cellTemplateMapper.batchInsert2(list));
eddie.woo's avatar
eddie.woo committed
425
                List<List<CellTemplateConfig>> tmpConfigList = CommonUtils.subListWithLen(cellTemplateConfigList, CommonUtils.BATCH_NUM);
eddie.woo's avatar
eddie.woo committed
426
                tmpConfigList.forEach(list -> cellTemplateConfigMapper.batchInsert(list));
427 428
                List<List<PeriodDataSource>> tmpPeriodList = CommonUtils.subListWithLen(periodDataSourceList, CommonUtils.BATCH_NUM);
                tmpPeriodList.forEach(list -> periodDataSourceMapper.batchInsert(list));
eddie.woo's avatar
eddie.woo committed
429 430 431 432 433 434 435 436
            }

        } catch (Exception e) {
            logger.error("importTemplateGroupExcelFile error.", e);
            throw new ServiceException(ErrorMessage.SystemError);
        }
    }

eddie.woo's avatar
eddie.woo committed
437
    @Transactional
438
    public void importTemplateExcelFile(MultipartFile file, boolean allowManual, Long templateGroupId, String reportType, String sheetList) throws ServiceException {
eddie.woo's avatar
eddie.woo committed
439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454
        if (null == file) {
            throw new ServiceException(ErrorMessage.NoFile);
        }
        try {
            if (StringUtils.isBlank(sheetList)) {
                throw new ServiceException(ErrorMessage.NoSelectSheet);
            }
            List<String> sheetNameList = JSON.parseArray(sheetList, String.class);
            if (CollectionUtils.isEmpty(sheetNameList)) {
                throw new ServiceException(ErrorMessage.NoSelectSheet);
            }
            InputStream inputStream = file.getInputStream();
            String fileName = file.getOriginalFilename();
            Workbook workbook = WorkbookFactory.create(inputStream);
            List<Template> filePathList = Lists.newArrayList();
            Date now = new Date();
sherlock's avatar
sherlock committed
455 456 457
            TemplateExample t = new TemplateExample();
            t.createCriteria().andTemplateGroupIdEqualTo(templateGroupId);
            int length = templateMapper.selectByExample(t).size();
458
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
eddie.woo's avatar
eddie.woo committed
459
                String sheetName = workbook.getSheetName(i);
kevin's avatar
kevin committed
460
                if (!sheetNameList.contains(sheetName)) {
461 462
                    continue;
                }
eddie.woo's avatar
eddie.woo committed
463 464 465 466 467 468 469
                String newName = sheetName + CommonUtils.getUUID() + POIUtil.getFileSuffix(fileName).get();
                Sheet sheet = workbook.getSheetAt(i);
                Optional<Workbook> optional = POIUtil.cloneNewSheet(sheet, fileName);
                if (!optional.isPresent()) {
                    throw new ServiceException(ErrorMessage.SystemError);
                }
                ByteArrayOutputStream bos = new ByteArrayOutputStream();
sherlock's avatar
sherlock committed
470 471
                Workbook wtemp = optional.get();
                Sheet temp = wtemp.getSheetAt(0);
kevin's avatar
kevin committed
472
                for (int j = temp.getFirstRowNum(); j < temp.getLastRowNum(); j++) {
sherlock's avatar
sherlock committed
473
                    Row row = temp.getRow(j);
kevin's avatar
kevin committed
474
                    if (row == null) {
zhkwei's avatar
zhkwei committed
475 476
                        continue;
                    }
kevin's avatar
kevin committed
477
                    for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
sherlock's avatar
sherlock committed
478
                        Cell cell = row.getCell(k);
zhkwei's avatar
zhkwei committed
479 480 481
                        if (cell == null) {
                            continue;//todo cell == null 如何处理
                        }
sherlock's avatar
sherlock committed
482
                        cell.setCellType(CellType.STRING);
kevin's avatar
kevin committed
483
                        if (!cell.getCellStyle().getLocked() && "${KeyIn}".equalsIgnoreCase(cell.getStringCellValue())) {
sherlock's avatar
sherlock committed
484 485 486 487 488
                            cell.setCellValue("");
                        }
                    }
                }
                wtemp.write(bos);
489
                //上传didi服务器文件
kevin's avatar
kevin committed
490
                FileUpload fileUpload = didiFileUploadService.uploadFile(file, file.getOriginalFilename(), FileUploadEnum.BizSource.REPORT_TEMPLATE_UPLOAD.name());
491
//                String tmpPath = httpFileService.uploadTemplate(newName, file);
eddie.woo's avatar
eddie.woo committed
492 493 494 495 496
                String[] arr = sheetName.split("_");
                String name = arr.length >= 2 ? arr[1] : arr[0];
                Template template = new Template();
                template.setCode(arr[0]);
                template.setCreateTime(now);
497
                template.setId(distributedIdService.nextId());
eddie.woo's avatar
eddie.woo committed
498 499 500
                template.setIsActiveAssociation(true);
                template.setIsSystemType(false);
                template.setName(name);
sherlock's avatar
sherlock committed
501
                template.setOrderIndex(i + length);
502
                template.setPath(fileUpload.getUid());
eddie.woo's avatar
eddie.woo committed
503
                template.setReportType(StringUtils.isBlank(reportType) ? null : Integer.valueOf(reportType));
504
                template.setTemplateGroupId(templateGroupId);
eddie.woo's avatar
eddie.woo committed
505 506 507 508
                filePathList.add(template);
                templateMapper.insertSelective(template);
                List<CellTemplate> cellTemplateList = Lists.newArrayList();
                List<CellTemplateConfig> cellTemplateConfigList = Lists.newArrayList();
509
                List<PeriodDataSource> periodDataSourceList = Lists.newArrayList();
510
                for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
eddie.woo's avatar
eddie.woo committed
511
                    Row row = sheet.getRow(r);
kevin's avatar
kevin committed
512
                    if (row == null) {
zhkwei's avatar
zhkwei committed
513 514
                        continue;
                    }
eddie.woo's avatar
eddie.woo committed
515
                    for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
kevin's avatar
kevin committed
516
                        if (c < 0) {
zhkwei's avatar
zhkwei committed
517 518
                            continue;
                        }
eddie.woo's avatar
eddie.woo committed
519 520 521 522 523
                        Cell cell = row.getCell(c);
                        if (cell == null) {
                            continue;//todo cell == null 如何处理
                        }
                        CellTemplate cellTemplate = new CellTemplate();
chase's avatar
chase committed
524
                        //税金计算表单独处理
kevin's avatar
kevin committed
525
                        if ("VAT020".equals(template.getCode())) {
chase's avatar
chase committed
526 527
                            cellTemplate.setRowName(POIUtil.getCellFormulaString(cell));
                        }
eddie.woo's avatar
eddie.woo committed
528 529 530 531
                        cellTemplate.setColumnIndex(c);
                        cellTemplate.setCreateTime(now);
                        cellTemplate.setUpdateTime(now);
                        cellTemplate.setRowIndex(r);
532
                        cellTemplate.setReportTemplateId(template.getId());
533
                        cellTemplate.setId(distributedIdService.nextId());
eddie.woo's avatar
eddie.woo committed
534 535 536
                        if (cell.getCellComment() != null) {
                            cellTemplate.setComment(cell.getCellComment().getString().getString());
                        }
537
                        cellTemplate.setIsReadOnly(cell.getCellStyle().getLocked() ? true : false);
eddie.woo's avatar
eddie.woo committed
538
                        cellTemplateList.add(cellTemplate);
eddie.woo's avatar
eddie.woo committed
539
                        //todo: 这里没有Config数据只有在上传模板以后,在界面里面可以配置公式
540
                        if (!cell.getCellStyle().getLocked() && hasKeyIn(cell)) {
eddie.woo's avatar
eddie.woo committed
541
                            cell.setCellValue(StringUtils.EMPTY);
kevin's avatar
kevin committed
542
                            if (allowManual) {
543 544
                                addManualConfig(cellTemplate, template, cell, now, cellTemplateConfigList);
                            }
eddie.woo's avatar
eddie.woo committed
545
                        }
kevin's avatar
kevin committed
546
                        if (!cell.getCellStyle().getLocked() && StringUtils.isNotBlank(POIUtil.getCellFormulaString(cell))) {
sherlock's avatar
sherlock committed
547 548 549 550 551 552 553
                            CellTemplateConfig config = new CellTemplateConfig();
                            config.setId(distributedIdService.nextId());
                            config.setCellTemplateId(cellTemplate.getId());
                            config.setReportTemplateId(template.getId());
                            config.setDataSourceType(1);//todo 枚举
                            config.setFormula(POIUtil.getCellFormulaString(cell));
//                        config.setFormula(cell.getCellFormula());
554
                            config.setFormulaDataSource("报表数据"); //todo KV相关
sherlock's avatar
sherlock committed
555 556 557
                            config.setUpdateTime(now);
                            config.setUpdateBy(authUserHelper.getCurrentUserId());
                            cellTemplateConfigList.add(config);
eddie.woo's avatar
eddie.woo committed
558
                            //noinspection Duplicates
559 560 561
                            if (allowManual) {
                                addManualConfig(cellTemplate, template, cell, now, cellTemplateConfigList);
                            }
562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577
                            PeriodDataSource pds = new PeriodDataSource();
                            pds.setAmount(BigDecimal.ZERO);
                            pds.setUpdateBy("Admin");
                            pds.setUpdateTime(new Date());
                            pds.setId(distributedIdService.nextId());
                            pds.setCreateTime(new Date());
                            pds.setCellTemplateId(cellTemplate.getId());
                            pds.setCreateBy("Admin");
                            pds.setDescription(" ");
                            pds.setName("ReportDataSource");
                            Calendar cal = Calendar.getInstance();
                            pds.setPeriod(cal.get(Calendar.MONTH) + 1);
                            pds.setType(5);
                            pds.setColumnIndex(cell.getColumnIndex());
                            pds.setRowIndex(cell.getRowIndex());
                            periodDataSourceList.add(pds);
sherlock's avatar
sherlock committed
578
                        }
eddie.woo's avatar
eddie.woo committed
579 580 581
                    }
                }
                List<List<CellTemplate>> tmpList = CommonUtils.subListWithLen(cellTemplateList, CommonUtils.BATCH_NUM);
eddie.woo's avatar
eddie.woo committed
582 583
//                tmpList.forEach(list -> cellTemplateMapper.batchInsert2(list));
                tmpList.forEach(list -> cellTemplateDao.batchInsert(list));//todo 批量插入优化
eddie.woo's avatar
eddie.woo committed
584
                List<List<CellTemplateConfig>> tmpConfigList = CommonUtils.subListWithLen(cellTemplateConfigList, CommonUtils.BATCH_NUM);
eddie.woo's avatar
eddie.woo committed
585
                tmpConfigList.forEach(list -> cellTemplateConfigDao.batchInsert(list));
586 587
                List<List<PeriodDataSource>> tmpPeriodList = CommonUtils.subListWithLen(periodDataSourceList, CommonUtils.BATCH_NUM);
                tmpPeriodList.forEach(list -> periodDataSourceMapper.batchInsert(list));
eddie.woo's avatar
eddie.woo committed
588 589 590 591 592 593 594
            }
        } catch (Exception e) {
            logger.error("importTemplateExcelFile error.", e);
            throw new ServiceException(ErrorMessage.SystemError);
        }
    }

eddie.woo's avatar
eddie.woo committed
595 596 597 598 599 600 601
    @Transactional
    public void addTemplateGroup(TemplateGroupDto templateGroupDto) throws ServiceException {
        List<TemplateGroup> groupList = templateGroupDao.getByGroupName(templateGroupDto.getName());
        if (CollectionUtils.isNotEmpty(groupList)) {
            throw new ServiceException(TemplateMessage.TemplateGroupNameExist);
        }
        try {
602
            String uid = authUserHelper.getCurrentUserId();
603
            TemplateGroup templateGroup = templateGroupMapper.selectByPrimaryKey(Long.parseLong(templateGroupDto.getCopyFrom()));
604 605
            Long newGroupId = distributedIdService.nextId();
            Long groupId = templateGroup.getId();
eddie.woo's avatar
eddie.woo committed
606
            Date now = new Date();
607 608
            templateGroup.setCopyFrom(groupId);
            templateGroup.setId(newGroupId);
eddie.woo's avatar
eddie.woo committed
609 610
            templateGroup.setCreateTime(now);
            templateGroup.setUpdateTime(now);
611
            templateGroup.setName(templateGroupDto.getName());
eddie.woo's avatar
eddie.woo committed
612
            templateGroupMapper.insertSelective(templateGroup);
613
            List<Template> templateList = templateDao.getByGroupId(groupId);
eddie.woo's avatar
eddie.woo committed
614
            templateList.stream().forEach(item -> {
615 616 617 618
                Long itemId = item.getId();
                Long newItemId = distributedIdService.nextId();
                item.setId(newItemId);
                item.setTemplateGroupId(newGroupId);
eddie.woo's avatar
eddie.woo committed
619 620 621
                item.setCreateTime(now);
                item.setUpdateTime(now);
                templateMapper.insertSelective(item);
622
                List<CellTemplate> cellTemplateList = cellTemplateDao.getByTemplateId(itemId);
623
                Map<Long, Long> idMapper = Maps.newHashMap();
eddie.woo's avatar
eddie.woo committed
624
                List<List<CellTemplate>> tmpList = CommonUtils.subListWithLen(cellTemplateList.stream().map(cell -> {
625 626 627 628 629
                    Long cellId = cell.getId();
                    Long newCellId = distributedIdService.nextId();
                    cell.setCopyFromId(cellId);
                    cell.setId(newCellId);
                    cell.setReportTemplateId(newItemId);
eddie.woo's avatar
eddie.woo committed
630 631
                    cell.setCreateTime(now);
                    cell.setUpdateTime(now);
632
                    idMapper.put(cellId, newCellId);
eddie.woo's avatar
eddie.woo committed
633 634
                    return cell;
                }).collect(Collectors.toList()), CommonUtils.BATCH_NUM);
635
                tmpList.forEach(list -> cellTemplateMapper.batchInsert2(list));
eddie.woo's avatar
eddie.woo committed
636

637
                List<CellTemplateConfig> configList = getByTemplateId(itemId);
eddie.woo's avatar
eddie.woo committed
638
                List<List<CellTemplateConfig>> tmpConfigList = CommonUtils.subListWithLen(configList.stream().map(cell -> {
639
                    Long newCellId = distributedIdService.nextId();
640 641
                    cell.setCreateBy(uid);
                    cell.setUpdateBy(uid);
642 643
                    cell.setId(newCellId);
                    cell.setReportTemplateId(newItemId);
644
                    cell.setCellTemplateId(idMapper.get(cell.getCellTemplateId()));
eddie.woo's avatar
eddie.woo committed
645 646 647 648 649 650 651 652 653 654 655 656 657
                    cell.setCreateTime(now);
                    cell.setUpdateTime(now);
                    return cell;
                }).collect(Collectors.toList()), CommonUtils.BATCH_NUM);
                tmpConfigList.forEach(list -> cellTemplateConfigMapper.batchInsert(list));
            });
        } catch (Exception e) {
            logger.error("addTemplateGroup error.", e);
            throw new ServiceException(ErrorMessage.SystemError);
        }

    }

658 659 660 661 662 663 664
    @Transactional
    public void addTemplateGroupWithoutTemplate(TemplateGroupDto templateGroupDto) throws ServiceException {
        List<TemplateGroup> groupList = templateGroupDao.getByGroupName(templateGroupDto.getName());
        if (CollectionUtils.isNotEmpty(groupList)) {
            throw new ServiceException(TemplateMessage.TemplateGroupNameExist);
        }
        try {
kevin's avatar
kevin committed
665 666
            TemplateGroup templateGroup = new TemplateGroup();
            CommonUtils.copyProperties(templateGroupDto, templateGroup);
667 668 669 670 671 672 673 674 675 676 677 678 679
            Long newGroupId = distributedIdService.nextId();
            Date now = new Date();
            templateGroup.setId(newGroupId);
            templateGroup.setCreateTime(now);
            templateGroup.setUpdateTime(now);
            templateGroup.setName(templateGroupDto.getName());
            templateGroupMapper.insertSelective(templateGroup);
        } catch (Exception e) {
            logger.error("addTemplateGroupWithoutTemplate error.", e);
            throw new ServiceException(ErrorMessage.SystemError);
        }
    }

680
    private List<CellTemplateConfig> getByTemplateId(Long id) {
681 682 683 684 685 686
        CellTemplateConfigExample example = new CellTemplateConfigExample();
        CellTemplateConfigExample.Criteria criteria = example.createCriteria();
        criteria.andReportTemplateIdEqualTo(id);
        return cellTemplateConfigMapper.selectByExample(example);
    }

eddie.woo's avatar
eddie.woo committed
687 688
    /**
     * 替换占位符
kevin's avatar
kevin committed
689
     *
eddie.woo's avatar
eddie.woo committed
690 691 692 693 694 695 696
     * @param cell cell
     * @return Boolean
     */
    private Boolean hasKeyIn(Cell cell) {
        if (null == cell) {
            return false;
        }
eddie.woo's avatar
eddie.woo committed
697 698 699 700
        CellType cellType = cell.getCellTypeEnum();
        if (!CellType.STRING.equals(cellType)) {
            return false;
        }
eddie.woo's avatar
eddie.woo committed
701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724
        String v = cell.getStringCellValue();
        if (StringUtils.isBlank(v)) {
            return false;
        }
        return StringUtils.equals(v, Constant.ReplaceKeyword.KEY_IN);
    }

    /**
     * 添加手工数据源
     */
    private void addManualConfig(CellTemplate cellTemplate, Template template, Cell cell, Date now, List<CellTemplateConfig> list) {
        CellTemplateConfig configManual = new CellTemplateConfig();
        configManual.setId(distributedIdService.nextId());
        configManual.setCellTemplateId(cellTemplate.getId());
        configManual.setReportTemplateId(template.getId());
        configManual.setDataSourceType(CellDataSourceType.KeyIn.getCode());
        configManual.setFormula(POIUtil.getCellFormulaString(cell));
//        config.setFormula(cell.getCellFormula());
//        configManual.setFormulaDataSource("报表数据"); //todo KV相关
        configManual.setUpdateTime(now);
        configManual.setUpdateBy(authUserHelper.getCurrentUserId());
        list.add(configManual);
    }

725
}