AnalysisServiceImpl.java 36.5 KB
Newer Older
gary's avatar
gary committed
1 2 3 4 5 6 7 8 9
package pwc.taxtech.atms.service.impl;

import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import pwc.taxtech.atms.analysis.dao.*;
import pwc.taxtech.atms.analysis.entity.*;
gary's avatar
gary committed
10
import pwc.taxtech.atms.common.message.ErrorMessage;
gary's avatar
gary committed
11 12
import pwc.taxtech.atms.common.message.ErrorMessageCN;
import pwc.taxtech.atms.common.util.DateUtils;
gary's avatar
gary committed
13 14
import pwc.taxtech.atms.constant.enums.EnumAnalysisExpTempPath;
import pwc.taxtech.atms.constant.enums.EnumAnalysisImportType;
gary's avatar
gary committed
15 16 17 18 19
import pwc.taxtech.atms.dto.OperationResultDto;
import pwc.taxtech.atms.dto.analysis.*;
import pwc.taxtech.atms.exception.ServiceException;

import javax.annotation.Resource;
20
import javax.servlet.http.HttpServletResponse;
gary's avatar
gary committed
21
import java.io.InputStream;
gary's avatar
gary committed
22
import java.io.OutputStream;
gary's avatar
gary committed
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @Auther: Gary J Li
 * @Date: 14/03/2019 11:31
 * @Description:
 */
@Service
public class AnalysisServiceImpl extends BaseService {

    @Resource
    private AnalysisDriverNumMapper analysisDriverNumMapper;
    @Resource
    private AnalysisEmployeeNumMapper analysisEmployeeNumMapper;
    @Resource
    private AnalysisExpectedTaxReturnMapper analysisExpectedTaxReturnMapper;
    @Resource
    private AnalysisActualTaxReturnMapper analysisActualTaxReturnMapper;
    @Resource
    private AnalysisFeeMapper analysisFeeMapper;
    @Resource
    private AnalysisFileManagementMapper analysisFileManagementMapper;
    @Resource
    private AnalysisGmvSubsidyMapper analysisGmvSubsidyMapper;
    @Resource
    private AnalysisInternationalBusinessDataMapper analysisInternationalBusinessDataMapper;
    @Resource
    private AnalysisInternationalTaxDataMapper analysisInternationalTaxDataMapper;
    @Resource
    private AnalysisMasterMapper analysisMasterMapper;
    @Resource
    private AnalysisSalesMapper analysisSalesMapper;
    @Resource
    private AnalysisTaxMapper analysisTaxMapper;

    public List<Object> displayAnalysisImportData(Integer type, String periodStr) {
        List<Object> objects = new ArrayList<>();
        Integer period = DateUtils.strToPeriod(periodStr);
        switch (type){
            case 0:
                AnalysisTaxExample analysisTaxExample = new AnalysisTaxExample();
                analysisTaxExample.createCriteria().andPeriodEqualTo(period);
                List<AnalysisTax> list = analysisTaxMapper.selectByExample(analysisTaxExample);
                List<AnalysisTaxDto> dtos = Lists.newArrayList();
                Map<String, List<AnalysisTax>> actrs =
                        list.stream().collect(Collectors.groupingBy(AnalysisTax::getCompanyName));
                actrs.forEach((k, v) -> processAts(k, v,dtos));
gary's avatar
gary committed
77
                objects.addAll(dtos);
gary's avatar
gary committed
78 79 80 81 82 83 84 85 86
                break;
            case 1:
                AnalysisActualTaxReturnExample analysisActualTaxReturnExample = new AnalysisActualTaxReturnExample();
                analysisActualTaxReturnExample.createCriteria().andPeriodEqualTo(period);
                List<AnalysisActualTaxReturn> list2 = analysisActualTaxReturnMapper.selectByExample(analysisActualTaxReturnExample);
                List<AnalysisActualTaxReturnDto> dtos2 = Lists.newArrayList();
                Map<String, List<AnalysisActualTaxReturn>> actrs2 =
                        list2.stream().collect(Collectors.groupingBy(AnalysisActualTaxReturn::getCompanyName));
                actrs2.forEach((k, v) -> processActrs(k, v,dtos2));
gary's avatar
gary committed
87
                objects.addAll(dtos2);
gary's avatar
gary committed
88 89 90 91 92
                return objects;
            case 2:
                AnalysisGmvSubsidyExample analysisGmvSubsidyExample = new AnalysisGmvSubsidyExample();
                analysisGmvSubsidyExample.createCriteria().andPeriodEqualTo(period);
                List<AnalysisGMVSubsidyDto> dtos3 = generalDtos(analysisGmvSubsidyMapper.selectByExample(analysisGmvSubsidyExample));
gary's avatar
gary committed
93
                objects.addAll(dtos3);
gary's avatar
gary committed
94 95 96 97 98 99 100 101 102
                break;
            case 3:
                AnalysisEmployeeNumExample analysisEmployeeNumExample = new AnalysisEmployeeNumExample();
                analysisEmployeeNumExample.createCriteria().andPeriodEqualTo(period);
                objects.addAll(analysisEmployeeNumMapper.selectByExample(analysisEmployeeNumExample));
                break;
            case 4:
                AnalysisDriverNumExample analysisDriverNumExample = new AnalysisDriverNumExample();
                analysisDriverNumExample.createCriteria().andPeriodEqualTo(period);
gary's avatar
gary committed
103 104
                List<AnalysisDriverNumDto> dtos4 = processDns(analysisDriverNumMapper.selectByExample(analysisDriverNumExample));
                objects.addAll(dtos4);
gary's avatar
gary committed
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
                break;
            default:
                break;
        }
        return objects;
    }

    private List<AnalysisGMVSubsidyDto> generalDtos(List<AnalysisGmvSubsidy> list) {
        List<AnalysisGMVSubsidyDto> dtos = Lists.newArrayList();
        NumberFormat nt = NumberFormat.getPercentInstance();
        list.forEach(l -> {
            AnalysisGMVSubsidyDto dto = new AnalysisGMVSubsidyDto();
            dto.setBusinessLine(l.getBusinessLine());
            dto.setSegment1(nt.format(l.getOrderChainRatio().doubleValue()));
            dto.setSegment2(nt.format(l.getGmvChainRatio().doubleValue()));
            dto.setSegment3(nt.format(l.getbSubsidyRate().doubleValue()));
            dto.setSegment4(nt.format(l.getbEndLinkRatio().doubleValue()));
            dto.setSegment5(nt.format(l.getcSubsidyRate().doubleValue()));
            dto.setSegment6(nt.format(l.getcEndLinkRatio().doubleValue()));
            dtos.add(dto);
        });
        return dtos;
    }

    private void processAts(String k, List<AnalysisTax> v, List<AnalysisTaxDto> dtos) {
        AnalysisTaxDto dto = new AnalysisTaxDto();
        dto.setCompanyName(k);
        dto.setSegment1(getAmountByGroup(v,"城建税"));
        dto.setSegment2(getAmountByGroup(v,"教育费附加"));
        dto.setSegment3(getAmountByGroup(v,"地方教育费附加"));
        dto.setSegment4(getAmountByGroup(v,"员工个税"));
        dto.setSegment5(getAmountByGroup(v,"司机个税"));
        dto.setSegment6(getAmountByGroup(v,"印花税"));
        dtos.add(dto);
    }

    private void processActrs(String k, List<AnalysisActualTaxReturn> v,List<AnalysisActualTaxReturnDto> dtos) {
        AnalysisActualTaxReturnDto dto = new AnalysisActualTaxReturnDto();
        dto.setCompanyName(k);
        dto.setSegment1(getTaxAmountByGroup(v,"增值税返还"));
        dto.setSegment2(getTaxAmountByGroup(v,"城建税返还"));
        dto.setSegment3(getTaxAmountByGroup(v,"教育费附加返还"));
        dto.setSegment4(getTaxAmountByGroup(v,"地方教育费附加返还"));
        dto.setSegment5(getTaxAmountByGroup(v,"个人所得税返还"));
        dtos.add(dto);
    }

gary's avatar
gary committed
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174
    private List<AnalysisDriverNumDto> processDns(List<AnalysisDriverNum> list) {
        List<AnalysisDriverNumDto> dtos = Lists.newArrayList();
        list.forEach(l->{
            AnalysisDriverNumDto dto1 = new AnalysisDriverNumDto();
            dto1.setDriverType("加盟");
            dto1.setDriverNum(l.getJoinInAmount());
            AnalysisDriverNumDto dto2 = new AnalysisDriverNumDto();
            dto2.setDriverType("自营");
            dto2.setDriverNum(l.getSelfSupportAmount());
            AnalysisDriverNumDto dto3 = new AnalysisDriverNumDto();
            dto3.setDriverType("直营");
            dto3.setDriverNum(l.getDirectSaleAmount());
            AnalysisDriverNumDto dto4 = new AnalysisDriverNumDto();
            dto4.setDriverType("对公");
            dto4.setDriverNum(l.getRightPublicAmount());
            dtos.add(dto1);
            dtos.add(dto2);
            dtos.add(dto3);
            dtos.add(dto4);
        });
        return dtos;
    }

gary's avatar
gary committed
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 202 203
    private BigDecimal getTaxAmountByGroup(List<AnalysisActualTaxReturn> v, String group) {
        List<AnalysisActualTaxReturn> filterRes = v.stream().filter(o-> group.equals(o.getTaxReturnGroup())).collect(Collectors.toList());
        BigDecimal res = filterRes.size()>0?filterRes.get(0).getTaxReturnAmount():BigDecimal.ZERO;
        return res;
    }

    private BigDecimal getAmountByGroup(List<AnalysisTax> v, String group) {
        List<AnalysisTax> filterRes = v.stream().filter(o-> group.equals(o.getTaxGroup())).collect(Collectors.toList());
        BigDecimal res = filterRes.size()>0?filterRes.get(0).getTaxAmount():BigDecimal.ZERO;
        return res;
    }

    public OperationResultDto importDomesitcExcelFile(MultipartFile file, String periodDate, Integer type) {
        switch (type){
            case 0:
                importAnalysisTaxExcelFile(file,periodDate);
                break;
            case 1:
                importAnalysisReturnTaxExcelFile(file,periodDate);
                break;
            case 2:
                importAnalysisGMVSubsidyExcelFile(file,periodDate);
                break;
            case 3:
                importAnalysisEmployeeNumExcelFile(file,periodDate);
                break;
            case 4:
                importAnalysisDriverNumExcelFile(file,periodDate);
                break;
204 205 206 207 208 209 210 211 212
            default:
                break;
        }
        return OperationResultDto.success();
    }

    public OperationResultDto importInterNationalExcelFile(MultipartFile file, String periodDate, Integer type,
                                                           String companyName,String country) {
        switch (type){
gary's avatar
gary committed
213
            case 100:
214
                importAnalysisInterBuDataExcelFile(file,periodDate,companyName,country);
gary's avatar
gary committed
215 216
                break;
            case 101:
217
                importAnalysisInterTaxDataExcelFile(file,periodDate,companyName,country);
gary's avatar
gary committed
218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457
                break;
            default:
                break;
        }
        return OperationResultDto.success();
    }

    private void importAnalysisTaxExcelFile(MultipartFile file, String periodDate) {
        try {
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (StringUtils.isBlank(periodDate) || "null".equals(periodDate)) {
                throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
            }
            // 文件上的期间
            Integer selectedPer = DateUtils.strToPeriod(periodDate);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                List<AnalysisTax> lists = Lists.newArrayList();
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    Cell cell1 = sheet.getRow(j).getCell(0);
                    if (null == cell1 || StringUtils.isEmpty(getCellStringValue(cell1))) {
                        continue;
                    }
                    String companyName = getCellStringValue(cell1);
                    for (int k = 1; k < sheet.getRow(0).getLastCellNum(); k++) {
                        AnalysisTax model = getAnalysisTax(selectedPer, sheet, k, j, companyName);
                        lists.add(model);
                    }
                }
                AnalysisTaxExample example = new AnalysisTaxExample();
                example.createCriteria().andPeriodEqualTo(selectedPer);
                analysisTaxMapper.deleteByExample(example);
                lists.forEach(l -> {
                    analysisTaxMapper.insertSelective(l);
                });
            }
        } catch (ServiceException se) {
            throw se;
        } catch (Exception e) {
            String errMsg = "分析模块-导入各税种税额异常";
            logger.error(errMsg, e);
            throw new ServiceException(errMsg);
        }
    }

    private AnalysisTax getAnalysisTax(Integer selectedPer, Sheet sheet,int k, int j, String companyName) {
        AnalysisTax model = new AnalysisTax();
        model.setId(idService.nextId());
        model.setPeriod(selectedPer);
        model.setCompanyName(companyName);
        model.setTaxGroup(getCellStringValue(sheet.getRow(0).getCell(k)));
        model.setTaxAmount(getCellBigDecimalValue(sheet.getRow(j).getCell(k)));
        return model;
    }

    private void importAnalysisReturnTaxExcelFile(MultipartFile file, String periodDate) {
        try{
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (StringUtils.isBlank(periodDate) || "null".equals(periodDate)) {
                throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
            }
            Integer selectedPer = DateUtils.strToPeriod(periodDate);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                List<AnalysisActualTaxReturn> lists = Lists.newArrayList();
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    Cell cell1 = sheet.getRow(j).getCell(0);
                    if (null == cell1 || StringUtils.isEmpty(getCellStringValue(cell1))) {
                        continue;
                    }
                    String companyName = getCellStringValue(cell1);
                    for(int k = 1; k < sheet.getRow(0).getLastCellNum(); k++){
                        AnalysisActualTaxReturn model = getAnalysisActualTaxReturn(selectedPer, sheet,k, j, companyName);
                        lists.add(model);
                    }
                }
                AnalysisActualTaxReturnExample example = new AnalysisActualTaxReturnExample();
                example.createCriteria().andPeriodEqualTo(selectedPer);
                analysisActualTaxReturnMapper.deleteByExample(example);
                lists.forEach(l->{
                    analysisActualTaxReturnMapper.insertSelective(l);
                });
            }
        }catch (ServiceException se){
            throw se;
        }catch (Exception e){
            String errMsg = "分析模块-导入实际返还税额异常";
            logger.error(errMsg,e);
            throw new ServiceException(errMsg);
        }
    }

    private AnalysisActualTaxReturn getAnalysisActualTaxReturn(Integer selectedPer, Sheet sheet,int k, int j, String companyName) {
        AnalysisActualTaxReturn model = new AnalysisActualTaxReturn();
        model.setId(idService.nextId());
        model.setPeriod(selectedPer);
        model.setCompanyName(companyName);
        model.setTaxReturnGroup(getCellStringValue(sheet.getRow(0).getCell(k)));
        model.setTaxReturnAmount(getCellBigDecimalValue(sheet.getRow(j).getCell(k)));
        return model;
    }

    private void importAnalysisEmployeeNumExcelFile(MultipartFile file, String periodDate) {
        try{
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (StringUtils.isBlank(periodDate) || "null".equals(periodDate)) {
                throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
            }
            Integer selectedPer = DateUtils.strToPeriod(periodDate);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                List<AnalysisEmployeeNum> lists = Lists.newArrayList();
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {

                    AnalysisEmployeeNum model = new AnalysisEmployeeNum();
                    model.setId(idService.nextId());
                    model.setPeriod(selectedPer);

                    Cell cell1 = sheet.getRow(j).getCell(0);
                    if (null == cell1 || StringUtils.isEmpty(getCellStringValue(cell1))) {
                        continue;
                    }
                    model.setCompanyName(getCellStringValue(cell1));
                    model.setFullTimeAmount(getCellBigDecimalValue(sheet.getRow(j).getCell(1)));
                    model.setInternAmount(getCellBigDecimalValue(sheet.getRow(j).getCell(2)));
                    model.setVendorAmount(getCellBigDecimalValue(sheet.getRow(j).getCell(3)));
                    model.setTotalAmount(getCellBigDecimalValue(sheet.getRow(j).getCell(4)));
                    lists.add(model);
                }
                AnalysisEmployeeNumExample example = new AnalysisEmployeeNumExample();
                example.createCriteria().andPeriodEqualTo(selectedPer);
                analysisEmployeeNumMapper.deleteByExample(example);
                lists.forEach(l->{
                    analysisEmployeeNumMapper.insertSelective(l);
                });
            }
        }catch (ServiceException se){
            throw se;
        }catch (Exception e){
            String errMsg = "分析模块-导入雇员人数异常";
            logger.error(errMsg,e);
            throw new ServiceException(errMsg);
        }
    }

    private void importAnalysisGMVSubsidyExcelFile(MultipartFile file, String periodDate) {
        try{
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (StringUtils.isBlank(periodDate) || "null".equals(periodDate)) {
                throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
            }
            Integer selectedPer = DateUtils.strToPeriod(periodDate);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                List<AnalysisGmvSubsidy> lists = Lists.newArrayList();
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    AnalysisGmvSubsidy model = new AnalysisGmvSubsidy();
                    model.setId(idService.nextId());
                    model.setPeriod(selectedPer);
                    Cell cell1 = sheet.getRow(j).getCell(0);
                    if (null == cell1 || StringUtils.isEmpty(getCellStringValue(cell1))) {
                        continue;
                    }
                    model.setBusinessLine(getCellStringValue(cell1));
                    model.setOrderChainRatio(getCellBigDecimalValue(sheet.getRow(j).getCell(1)));
                    model.setGmvChainRatio(getCellBigDecimalValue(sheet.getRow(j).getCell(2)));
                    model.setbSubsidyRate(getCellBigDecimalValue(sheet.getRow(j).getCell(3)));
                    model.setbEndLinkRatio(getCellBigDecimalValue(sheet.getRow(j).getCell(4)));
                    model.setcSubsidyRate(getCellBigDecimalValue(sheet.getRow(j).getCell(5)));
                    model.setcEndLinkRatio(getCellBigDecimalValue(sheet.getRow(j).getCell(6)));
                    lists.add(model);
                }
                AnalysisGmvSubsidyExample example = new AnalysisGmvSubsidyExample();
                example.createCriteria().andPeriodEqualTo(selectedPer);
                analysisGmvSubsidyMapper.deleteByExample(example);
                lists.forEach(l->{
                    analysisGmvSubsidyMapper.insertSelective(l);
                });
            }
        }catch (ServiceException se){
            throw se;
        }catch (Exception e){
            String errMsg = "分析模块-导入GMV及补贴异常";
            logger.error(errMsg,e);
            throw new ServiceException(errMsg);
        }
    }

    private void importAnalysisDriverNumExcelFile(MultipartFile file, String periodDate) {
        try{
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (StringUtils.isBlank(periodDate) || "null".equals(periodDate)) {
                throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
            }
            Integer selectedPer = DateUtils.strToPeriod(periodDate);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                AnalysisDriverNum model = new AnalysisDriverNum();
                model.setId(idService.nextId());
                model.setPeriod(selectedPer);
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    Cell cell1 = sheet.getRow(j).getCell(0);
                    if (null == cell1 || StringUtils.isEmpty(getCellStringValue(cell1))) {
                        continue;
                    }
                    Cell cell2 = sheet.getRow(j).getCell(1);
                    if("加盟".equals(getCellStringValue(cell1))){
                        model.setJoinInAmount(getCellBigDecimalValue(cell2));
                    }else if("自营".equals(getCellStringValue(cell1))){
                        model.setSelfSupportAmount(getCellBigDecimalValue(cell2));
                    }else if("直营".equals(getCellStringValue(cell1))){
                        model.setDirectSaleAmount(getCellBigDecimalValue(cell2));
                    }else if("对公".equals(getCellStringValue(cell1))){
                        model.setRightPublicAmount(getCellBigDecimalValue(cell2));
                    }
                }
                AnalysisDriverNumExample example = new AnalysisDriverNumExample();
                example.createCriteria().andPeriodEqualTo(selectedPer);
                analysisDriverNumMapper.deleteByExample(example);
                analysisDriverNumMapper.insertSelective(model);
            }
        }catch (ServiceException se){
            throw se;
        }catch (Exception e){
            String errMsg = "分析模块-导入司机人数异常";
            logger.error(errMsg,e);
            throw new ServiceException(errMsg);
        }
    }

458 459
    private void importAnalysisInterTaxDataExcelFile(MultipartFile file, String periodDate,
                                                     String companyName,String country) {
gary's avatar
gary committed
460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479
        try{
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (StringUtils.isBlank(periodDate) || "null".equals(periodDate)) {
                throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
            }
            // 文件上的期间
            Integer selectedPer = DateUtils.strToPeriod(periodDate);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                List<AnalysisInternationalTaxData> lists = Lists.newArrayList();
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    Cell cell1 = sheet.getRow(j).getCell(0);
                    if (null == cell1 || StringUtils.isEmpty(getCellStringValue(cell1))) {
                        continue;
                    }
                    AnalysisInternationalTaxData model = new AnalysisInternationalTaxData();
                    model.setId(idService.nextId());
                    model.setPeriod(selectedPer);
480 481
                    model.setCompanyName(companyName);
                    model.setCountry(country);
gary's avatar
gary committed
482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502
                    model.setTaxCategory(getCellStringValue(sheet.getRow(j).getCell(0)));
                    model.setTaxType(getCellStringValue(sheet.getRow(j).getCell(1)));
                    model.setTaxAmount(getCellBigDecimalValue(sheet.getRow(j).getCell(2)));
                    lists.add(model);
                }
                AnalysisInternationalTaxDataExample example = new AnalysisInternationalTaxDataExample();
                example.createCriteria().andPeriodEqualTo(selectedPer);
                analysisInternationalTaxDataMapper.deleteByExample(example);
                lists.forEach(l->{
                    analysisInternationalTaxDataMapper.insertSelective(l);
                });
            }
        }catch (ServiceException se){
            throw se;
        }catch (Exception e){
            String errMsg = "分析模块-导入国际税业务数据异常";
            logger.error(errMsg,e);
            throw new ServiceException(errMsg);
        }
    }

503 504
    private void importAnalysisInterBuDataExcelFile(MultipartFile file, String periodDate,
                                                    String companyName,String country) {
gary's avatar
gary committed
505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520
        try{
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (StringUtils.isBlank(periodDate) || "null".equals(periodDate)) {
                throw new ServiceException(ErrorMessageCN.DoNotSelectPeriod);
            }
            // 文件上的期间
            Integer selectedPer = DateUtils.strToPeriod(periodDate);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (isSheetEmpty(sheet)) continue;
                List<AnalysisInternationalBusinessData> lists = Lists.newArrayList();
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    AnalysisInternationalBusinessData model = new AnalysisInternationalBusinessData();
                    model.setId(idService.nextId());
                    model.setPeriod(selectedPer);
521 522
                    model.setCompanyName(companyName);
                    model.setCountry(country);
gary's avatar
gary committed
523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551
                    Cell cell1 = sheet.getRow(j).getCell(0);
                    if (null == cell1 || StringUtils.isEmpty(getCellStringValue(cell1))) {
                        continue;
                    }
                    model.setGmv(getCellBigDecimalValue(cell1));
                    model.setTrips(getCellBigDecimalValue(sheet.getRow(j).getCell(1)));
                    model.setSubsidyB(getCellBigDecimalValue(sheet.getRow(j).getCell(2)));
                    model.setSubsidyC(getCellBigDecimalValue(sheet.getRow(j).getCell(3)));
                    model.setRevenue(getCellBigDecimalValue(sheet.getRow(j).getCell(4)));
                    model.setProfit(getCellBigDecimalValue(sheet.getRow(j).getCell(5)));
                    model.setExchangeRate(getCellBigDecimalValue(sheet.getRow(j).getCell(6)));
                    lists.add(model);
                }
                AnalysisInternationalBusinessDataExample example = new AnalysisInternationalBusinessDataExample();
                example.createCriteria().andPeriodEqualTo(selectedPer);
                analysisInternationalBusinessDataMapper.deleteByExample(example);
                lists.forEach(l->{
                    analysisInternationalBusinessDataMapper.insertSelective(l);
                });
            }
        }catch (ServiceException se){
            throw se;
        }catch (Exception e){
            String errMsg = "分析模块-导入国际税业务数据异常";
            logger.error(errMsg,e);
            throw new ServiceException(errMsg);
        }
    }

gary's avatar
gary committed
552
    public List<Object> displayAnalysisInternationalImportData(AnalysisInternationlParam param) {
gary's avatar
gary committed
553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612
        List<Object> objects = new ArrayList<>();
        Integer period = DateUtils.strToPeriod(param.getPeriod());
        String country = param.getCountry();
        String companyName = param.getCompanyName();
        switch (param.getType()){
            case 100:
                AnalysisInternationalBusinessDataExample analysisInternationalBusinessDataExample = new AnalysisInternationalBusinessDataExample();
                analysisInternationalBusinessDataExample.createCriteria().andPeriodEqualTo(period).andCompanyNameEqualTo(companyName).andCountryEqualTo(country);
                List<AnalysisInternationalBusinessData> list = analysisInternationalBusinessDataMapper.selectByExample(analysisInternationalBusinessDataExample);
                List<AnalysisInternationalBUDataDto> dtos = Lists.newArrayList();
                list.forEach(l->{
                    AnalysisInternationalBUDataDto dto1 = new AnalysisInternationalBUDataDto();
                    dto1.setCategory("GMV");
                    dto1.setName("GMV");
                    dto1.setAmount(l.getGmv());
                    dtos.add(dto1);
                    AnalysisInternationalBUDataDto dto2 = new AnalysisInternationalBUDataDto();
                    dto2.setCategory("Revenue");
                    dto2.setName("Revenue");
                    dto2.setAmount(l.getRevenue());
                    dtos.add(dto2);
                    AnalysisInternationalBUDataDto dto3 = new AnalysisInternationalBUDataDto();
                    dto3.setCategory("Profit");
                    dto3.setName("Profit");
                    dto3.setAmount(l.getProfit());
                    dtos.add(dto3);
                    AnalysisInternationalBUDataDto dto4 = new AnalysisInternationalBUDataDto();
                    dto4.setCategory("Trips");
                    dto4.setName("Trips");
                    dto4.setAmount(l.getTrips());
                    dtos.add(dto4);
                    AnalysisInternationalBUDataDto dto5 = new AnalysisInternationalBUDataDto();
                    dto5.setCategory("Subsidy");
                    dto5.setName("Subsidy B");
                    dto5.setAmount(l.getSubsidyB());
                    dtos.add(dto5);
                    AnalysisInternationalBUDataDto dto6 = new AnalysisInternationalBUDataDto();
                    dto6.setCategory("Subsidy");
                    dto6.setName("Subsidy C");
                    dto6.setAmount(l.getSubsidyC());
                    dtos.add(dto6);
                });
                objects.addAll(dtos);
                return objects;
            case 101:
                AnalysisInternationalTaxDataExample analysisInternationalTaxDataExample = new AnalysisInternationalTaxDataExample();
                analysisInternationalTaxDataExample.createCriteria().andPeriodEqualTo(period).andCompanyNameEqualTo(companyName).andCountryEqualTo(country);
                List<AnalysisInternationalTaxData> list2 = analysisInternationalTaxDataMapper.selectByExample(analysisInternationalTaxDataExample);
                List<AnalysisInternationalTaxDataDto> dtos2 = Lists.newArrayList();
                list2.forEach(l2->{
                    dtos2.add(beanUtil.copyProperties(l2,new AnalysisInternationalTaxDataDto()));
                });
                objects.addAll(dtos2);
                return objects;
            default:
                break;
        }
        return objects;
    }

gary's avatar
gary committed
613 614 615
    public HttpServletResponse downloadDomesticFile(HttpServletResponse response, AnalysisDomesticlParam param, String fileName) {
        String excelTemplatePathInClassPath = EnumAnalysisExpTempPath.getPath(param.getType());
        List<Object> datas = displayAnalysisImportData(param.getType(),param.getPeriod());
616 617 618
        if(datas.size()<1){
            throw new ServiceException(ErrorMessage.ExportFailed);
        }
gary's avatar
gary committed
619
        OutputStream outputStream = commonDocumentHelper.toXlsxFileUsingJxls(datas, excelTemplatePathInClassPath);
620 621 622
        try {
            return responseMessageBuilder.getDownloadTmpResponseMessage(response, outputStream, fileName);
        } catch (Exception e) {
gary's avatar
gary committed
623
            logger.error(String.format("导出国内税数据异常:%s",e.getMessage()));
624
        }
gary's avatar
gary committed
625 626
        return null;
    }
627

gary's avatar
gary committed
628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651
    public HttpServletResponse downloadInternationalFile(HttpServletResponse response, AnalysisInternationlParam param, String fileName) {
        String excelTemplatePathInClassPath = EnumAnalysisExpTempPath.getPath(param.getType());
        List<Object> datas = Lists.newArrayList();
        if(param.getType().equals(EnumAnalysisImportType.InternationalBuData.getCode())){
            Integer period = DateUtils.strToPeriod(param.getPeriod());
            String country = param.getCountry();
            String companyName = param.getCompanyName();
            AnalysisInternationalBusinessDataExample analysisInternationalBusinessDataExample = new AnalysisInternationalBusinessDataExample();
            analysisInternationalBusinessDataExample.createCriteria().andPeriodEqualTo(period).andCompanyNameEqualTo(companyName).andCountryEqualTo(country);
            List<AnalysisInternationalBusinessData> list = analysisInternationalBusinessDataMapper.selectByExample(analysisInternationalBusinessDataExample);
            datas.addAll(list);
        }else{
            datas = displayAnalysisInternationalImportData(param);
        }
        if(datas.size()<1){
            throw new ServiceException(ErrorMessage.ExportFailed);
        }
        OutputStream outputStream = commonDocumentHelper.toXlsxFileUsingJxls(datas, excelTemplatePathInClassPath);
        try {
            return responseMessageBuilder.getDownloadTmpResponseMessage(response, outputStream, fileName);
        } catch (Exception e) {
            logger.error(String.format("导出国际税数据异常:%s",e.getMessage()));
        }
        return null;
652 653
    }

gary's avatar
gary committed
654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756
    public List<String> getAnalysisInternationalCompanyList(Integer type, String periodStr) {
        List<String> companyList = Lists.newArrayList();
        Integer period = DateUtils.strToPeriod(periodStr);
        switch (type){
            case 100:
                companyList = analysisInternationalBusinessDataMapper.selectCompanyList(period);
                break;
            case 101:
                companyList = analysisInternationalTaxDataMapper.selectCompanyList(period);
                break;
            default:
                break;
        }
        return companyList;
    }

    public List<String> getAnalysisInternationalCountryList(Integer type, String periodStr) {
        List<String> countryList = Lists.newArrayList();
        Integer period = DateUtils.strToPeriod(periodStr);
        switch (type){
            case 100:
                countryList = analysisInternationalBusinessDataMapper.selectCountryList(period);
                break;
            case 101:
                countryList = analysisInternationalTaxDataMapper.selectCountryList(period);
                break;
            default:
                break;
        }
        return countryList;
    }

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

    private Boolean getCellBolleanValue(Cell cell) {
        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
            switch (cell.getStringCellValue()) {
                case "TRUE":
                    return Boolean.TRUE;
                case "FALSE":
                    return Boolean.FALSE;
                case "是":
                    return Boolean.TRUE;
                case "否":
                    return Boolean.FALSE;
                default:
                    return null;
            }
        }else if (cell.getCellTypeEnum().equals(CellType.BOOLEAN)) {
            return cell.getBooleanCellValue();
        }
        logger.warn("获取单元格数据类型未匹配");
        return null;
    }

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

    private BigDecimal getCellBigDecimalValue(Cell cell) {
        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
            return new BigDecimal(cell.getStringCellValue());
        } else if(cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
            return new BigDecimal(cell.getNumericCellValue());
        }
        logger.warn("获取单元格数据类型未匹配");
        return null;
    }

    private Long getCellLongDecimalValue(Cell cell) {
        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
            return Long.valueOf(cell.getStringCellValue());
        } else if(cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
            return new Double(cell.getNumericCellValue()).longValue();
        }
        logger.warn("获取单元格数据类型未匹配");
        return null;
    }

    private boolean isSheetEmpty(Sheet sheet) {
        if (sheet.getLastRowNum() > 0 &&
                (null == sheet.getRow(0).getCell(0) || "Version".equals(sheet.getRow(0).getCell(0).getStringCellValue())) &&
                null == sheet.getRow(0).getCell(2)) {
            return true;
        }
        return false;
    }
}