kmall-haikong/kmall-admin/src/main/java/com/kmall/admin/controller/ExportExcelController.java

package com.kmall.admin.controller;

import com.kmall.admin.dto.ExportExcelDto;
import com.kmall.admin.dto.TaxDetailDto;
import com.kmall.admin.entity.OrderEntity;
import com.kmall.admin.service.OrderService;
import com.kmall.admin.service.SalesDataUploadService;
import com.kmall.admin.utils.ParamUtils;
import com.kmall.common.constant.Dict;
import com.kmall.common.utils.DateUtils;
import com.kmall.common.utils.R;
import com.kmall.common.utils.excel.ExcelExport;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author zhangchuangbiao
 * @version 1.0
 * 2020-12-22 13:59
 */
@RestController
@RequestMapping("exportExcel")
public class ExportExcelController {
    private static String  ISO = "iso-8859-1";
    private static String  UTF = "utf-8";
    private static final Logger LOGGER = LoggerFactory.getLogger(ExportExcelController.class);

    @Autowired
    private OrderService orderService;
    @Autowired
    private SalesDataUploadService salesDataUploadService;



    @RequestMapping(value = "/exportSalesExcel")
    public R exportOffilineOrder(@RequestParam Map<String, Object> params, HttpServletResponse response, HttpServletRequest request) throws ParseException {
        ParamUtils.setQueryPowerByRoleType(params, "storeId", "merchSn", "thirdPartyMerchCode");

        // 账单日期
        String billDate = (String) params.get("billDate");
        params = ParamUtils.setTimeMap(params);

        try {
            billDate = new String(billDate.getBytes(ISO), UTF);
        } catch (Exception e) {
            e.printStackTrace();
        }
        billDate = DateUtils.getDate(billDate);

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date sDate = sdf.parse(billDate);

        Calendar c = Calendar.getInstance();
        c.setTime(sDate);
        c.add(Calendar.DAY_OF_MONTH, 1);//利用Calendar 实现 Date日期+1天
        sDate = c.getTime();
        // 汇总日期
        String summaryDate = sdf.format(sDate);



        params.put("isOnfiilineOrder", Dict.isOnfflineOrder.item_1.getItem());

        SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
        ExcelExport ee = new ExcelExport("CW保税 新零售销售额统计_"+format.format(new Date()));

        // ==================================第一个sheet-销售总额===============================================
        ExportExcelDto exportExcelDto = orderService.querySumAmount(billDate,"");


        Map<String,String> firstSheetFirstRowValue = new HashMap<>();
//        "汇总日期","账单日期","销售含税总额","当期退T-1日前金额","销售含税净额","销售税前总额","税款总额","当期退T-1日前税款","当期税款金额","成功订单数"
        firstSheetFirstRowValue.put("汇总日期",summaryDate); // 汇总日期
        firstSheetFirstRowValue.put("账单日期",billDate); // 账单日期
        firstSheetFirstRowValue.put("销售含税总额",exportExcelDto.getTotalAmount()); // 销售含税总额
        firstSheetFirstRowValue.put("当期退T-1日前金额",exportExcelDto.getRefundPreviousAmount()); // 当期退T-1日前金额
        firstSheetFirstRowValue.put("销售含税净额",exportExcelDto.getNetAmount()); // 销售含税净额
        firstSheetFirstRowValue.put("销售税前总额",exportExcelDto.getPreTaxTotalAmount()); // 销售税前总额
        firstSheetFirstRowValue.put("税款总额",exportExcelDto.getTotalTax()); // 税款总额
        firstSheetFirstRowValue.put("当期退T-1日前税款",exportExcelDto.getPreviousTax()); // 当前退T-1日前税款
        firstSheetFirstRowValue.put("当期税款金额",exportExcelDto.getCurrentTax()); // 当期税款金额
        firstSheetFirstRowValue.put("成功订单数",exportExcelDto.getSuccessNumber()); // 成功订单数

        Map<String,String> firstSheetSecondRowValue = new HashMap<>();
//        "汇总日期","账单日期","当期退当天金额（销售总额已减扣）","当期退当天订单数","退T-1日前订单总额（销售总额已减扣）","退T-1日前订单总数","当期退单总额（销售总额已减扣）","当日退款总单数"
        firstSheetSecondRowValue.put("汇总日期",summaryDate); // 汇总日期
        firstSheetSecondRowValue.put("账单日期",billDate); // 账单日期
        firstSheetSecondRowValue.put("当期退当天金额（销售总额已减扣）",exportExcelDto.getRefundCurrentAmount()); // 当期退当天金额（销售总额已减扣）
        firstSheetSecondRowValue.put("当期退当天订单数",exportExcelDto.getRefundCurrentCount()); // 当期退当天订单数
        firstSheetSecondRowValue.put("退T-1日前订单总额（销售总额已减扣）",exportExcelDto.getRefundPreviousAmount()); // 退T-1日前订单总额（销售总额已减扣）
        firstSheetSecondRowValue.put("退T-1日前订单总数",exportExcelDto.getRefundPreviousCount()); // 退T-1日前订单总数
        firstSheetSecondRowValue.put("当期退单总额（销售总额已减扣）",exportExcelDto.getRefundAmount()); // 当期退单总额（销售总额已减扣）
        firstSheetSecondRowValue.put("当日退款总单数",exportExcelDto.getRefundCount()); // 当日退款总单数



        // ==================================第二个sheet-销售订单金额===========================================
        String[] saleHeader = new String[]{"订单编号", "操作店员", "购买类型", "订单业务类型", "订单状态",
                "付款状态",  "实际支付", "订单总价","下单时间", "付款时间", "退T-1日前订单金额", "T-1日前订单生成时间"};
        Map<String,List<OrderEntity>> orderEntityListMap = orderService.querySaleOrderList(billDate,"");
        // 当期的订单
        List<OrderEntity> orderEntityList = orderEntityListMap.get("current");
        List<Map<String, Object>> list = new LinkedList<>();
        if (orderEntityList != null && orderEntityList.size() > 0) {
            for (OrderEntity orderEntity :  orderEntityList) {

                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                // 订单业务类型
                String orderBizType = orderEntity.getOrderBizType();
                // 订单状态
                Integer orderStatus = orderEntity.getOrderStatus();
                // 付款状态
                Integer payStatus = orderEntity.getPayStatus();

                map.put("订单编号", orderEntity.getOrderSn());
                map.put("操作店员", orderEntity.getUserName());
                map.put("购买类型", Dict.isOnfflineOrder.item_1.getItem().equals(orderEntity.getIsOnfflineOrder()) ? "线下购买" : "线上购买");
                map.put("订单业务类型", StringUtils.isEmpty(orderBizType)?"":Dict.orderBizType.valueOf("item_"+orderBizType).getItemName());
                map.put("订单状态", Dict.orderStatus.valueOf("item_"+orderStatus).getItemName());
                map.put("付款状态", Dict.payStatus.valueOf("item_"+payStatus).getItemName());
                map.put("实际支付", orderEntity.getActualPrice());
                map.put("订单总价", orderEntity.getOrderPrice());
                map.put("下单时间", orderEntity.getAddTime());
                map.put("付款时间", orderEntity.getPayTime());
                map.put("退T-1日前订单金额", orderEntity.getPayTime());
                map.put("T-1日前订单生成时间", orderEntity.getPayTime());

                list.add(map);
            }
        }
        // 前期的订单
        List<OrderEntity> previousList = orderEntityListMap.get("previous");
        if (previousList != null && previousList.size() > 0) {
            for (OrderEntity orderEntity :  previousList) {

                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                // 订单业务类型
                String orderBizType = orderEntity.getOrderBizType();
                // 订单状态
                Integer orderStatus = orderEntity.getOrderStatus();
                // 付款状态
                Integer payStatus = orderEntity.getPayStatus();

                map.put("订单编号", orderEntity.getOrderSn());
                map.put("操作店员", orderEntity.getUserName());
                map.put("购买类型", Dict.isOnfflineOrder.item_1.getItem().equals(orderEntity.getIsOnfflineOrder()) ? "线下购买" : "线上购买");
                map.put("订单业务类型", StringUtils.isEmpty(orderBizType)?"":Dict.orderBizType.valueOf("item_"+orderBizType).getItemName());
                map.put("订单状态", Dict.orderStatus.valueOf("item_"+orderStatus).getItemName());
                map.put("付款状态", Dict.payStatus.valueOf("item_"+payStatus).getItemName());
                map.put("实际支付", orderEntity.getActualPrice());
                map.put("订单总价", orderEntity.getOrderPrice());
                map.put("下单时间", "");
                map.put("付款时间", "");
                map.put("退T-1日前订单金额", orderEntity.getActualPrice());
                map.put("T-1日前订单生成时间", orderEntity.getPayTime());

                list.add(map);
            }
        }


        // ==================================第三个sheet-订单税款明细===========================================
        String[] taxDetailHeader = new String[]{"订单编号", "门店编号", "门店名称", "商品编码", "完税总价格",
                "进口增值税率(%)", "进口从价消费税率(%)", "应征增值税", "应征消费税", "总税额",
                "税单接收时间","退T-1日前应征增值税","退T-1日前应征消费税","退T-1日前总税额","T-1日前生成时间"};
        Map<String,List<TaxDetailDto>> TaxDetailEntityListMap = orderService.queryTaxDetailList(billDate,"");
        List<Map<String,Object>> taxList = new LinkedList<>();
        // 获取当前税率
        List<TaxDetailDto> current = TaxDetailEntityListMap.get("current");
        if (current != null && current.size() > 0) {

            for (TaxDetailDto taxDetailDto : current) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();

                map.put("订单编号", taxDetailDto.getOrderSn());
                map.put("门店编号", taxDetailDto.getStoreSn());
                map.put("门店名称", taxDetailDto.getStoreName());
                map.put("商品编码", taxDetailDto.getSku());
                map.put("完税总价格", taxDetailDto.getPretaxPrice());
                map.put("进口增值税率(%)", taxDetailDto.getVatRate());
                map.put("进口从价消费税率(%)", taxDetailDto.getSaleTaxRate());
                map.put("应征增值税", taxDetailDto.getVat());
                map.put("应征消费税", taxDetailDto.getSaleTax());
                map.put("总税额", taxDetailDto.getTax());
                map.put("税单接收时间", taxDetailDto.getReceiveTaxTime());
                taxList.add(map);
            }
        }

        List<TaxDetailDto> previous = TaxDetailEntityListMap.get("previous");
        if (previous != null && previous.size() > 0) {

            for (TaxDetailDto taxDetailDto : previous) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();

                map.put("订单编号", taxDetailDto.getOrderSn());
                map.put("门店编号", taxDetailDto.getStoreSn());
                map.put("门店名称", taxDetailDto.getStoreName());
                map.put("商品编码", taxDetailDto.getSku());
                map.put("完税总价格", taxDetailDto.getPretaxPrice());
                map.put("进口增值税率(%)", taxDetailDto.getVatRate());
                map.put("进口从价消费税率(%)", taxDetailDto.getSaleTaxRate());
                map.put("退T-1日前应征增值税", taxDetailDto.getPreVat());
                map.put("退T-1日前应征消费税", taxDetailDto.getPreSaleTax());
                map.put("退T-1日前总税额", taxDetailDto.getPreTax());
                map.put("T-1日前生成时间", taxDetailDto.getPreTaxTime());
                taxList.add(map);
            }
        }



        ee.addSalesFirstSheet("销售总额", firstSheetFirstRowValue, firstSheetSecondRowValue);
        ee.addSheetByMap("销售订单金额", list, saleHeader);
        ee.addSheetByMap("订单税款明细", taxList, taxDetailHeader);
        ee.export(response);


        return R.ok();
    }


}