package com.winsun.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.enums.SqlLike;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.winsun.auth.core.annotion.Permission;
import com.winsun.auth.core.base.controller.BaseController;
import com.winsun.auth.core.common.model.ResponseData;
import com.winsun.auth.core.shiro.ShiroUser;
import com.winsun.auth.core.util.IOUtils;
import com.winsun.bean.Order;
import com.winsun.bean.School;
import com.winsun.bean.SysUser;
import com.winsun.bean.YrymReport;
import com.winsun.mapper.*;
import com.winsun.rabbitmq.RabbitProducer;
import com.winsun.utils.ImportExcel;
import com.winsun.utils.MyBatisPlusUpdateUtils;
import com.winsun.utils.YrymReportUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.*;

/**
 * @Author: dongyp
 * @Date: 2020/2/14 17:08
 */
@Slf4j
@RestController
@RequestMapping("/order")
public class OrderController extends BaseController {

    /**
     * 导入
     */
    private static final int HEADER_NUM = -1;
    private static final int SHEET_INDEX = 0;
    /**
     * 受理单模板列名
     */
    private static String SL_ORDERNUMBER = "订单编号(必填)";
    private static String SL_ACCEPTRESULT = "受理结果(必填)";
    private static String SL_ACCEPTNAME = "受理人(必填)";
    private static String SL_ACCERPCOMMENT = "备注（受理不成功需填写不成功原因）";
    /**
     * 快递清单模板列名
     */
    private static String KD_ORDERNUMBER = "订单编号(必填)";
    private static String SL_SENDTYPE = "快递方式(必填)";
    private static String SL_COMPANY = "快递公司(快递方式为快递必须填写)";
    private static String SL_KUAIDORDER = "快递单号(快递方式为快递必须填写)";

    private static OrderMapper orderMapper;

    private static SchoolMapper schoolMapper;

    private static SysUserMapper sysUserMapper;

    private static AppMapper appMapper;

    private ExportExcelMapper exportExcelMapper;

    @Autowired
    private RabbitProducer taskImport;

    @Autowired
    public OrderController(OrderMapper orderMapper, SchoolMapper schoolMapper, SysUserMapper sysUserMapper,
                           AppMapper appMapper, ExportExcelMapper exportExcelMapper) {
        OrderController.orderMapper = orderMapper;
        OrderController.schoolMapper = schoolMapper;
        OrderController.sysUserMapper = sysUserMapper;
        OrderController.appMapper = appMapper;
        this.exportExcelMapper = exportExcelMapper;
    }

    /**
     * 查询订单历史状态
     */
    @Permission(menuname = "查询订单历史状态", value = "selectOrderHis", method = RequestMethod.POST)
    public ResponseData<List<Map<String, Object>>> selectOrderHis(@RequestParam("orderId") String orderId) {
        List<Map<String, Object>> list = orderMapper.selectOrderHis(orderId);
        return ResponseData.success(list);
    }

    /**
     * 审核学生证
     */
    @Permission(menuname = "审核学生证", value = "reviewStudentIdCard", method = RequestMethod.POST)
    public ResponseData<String> reviewStudentIdCard(@RequestParam("studentCardCheckStatus") String studentCardCheckStatus, @RequestParam(name = "checkFail", required = false) String checkFail, @RequestParam("id") String id) {
        ShiroUser user = getShiroUser();
        /*if (!user.getRoleNames().stream().anyMatch(roleName -> StringUtils.equalsAny(roleName, "超级管理员"))) {
            return ResponseData.error("无数据权限");
        }*/
        Wrapper wrapper = new EntityWrapper<>();
        wrapper.eq("id", id);
        Map<String, Object> dataMapping = new HashMap<>();
        dataMapping.put("student_card_check_status", studentCardCheckStatus);
        if (StringUtils.isNotBlank(checkFail)) {
            dataMapping.put("check_fail", checkFail);
        }
        Integer integer = orderMapper.updateForSet(MyBatisPlusUpdateUtils.toUpdateSet(dataMapping), wrapper);
        if (integer == 1) {
            if ("1".equals(studentCardCheckStatus)) {
                appMapper.inserOrderHis(id, "审核通过!", new Date(), user.getName());
            } else {
                appMapper.inserOrderHis(id, "审核不通过:" + checkFail, new Date(), user.getName());
            }

        } else {
            return ResponseData.error("审核失败！");
        }
        return ResponseData.success("审核成功！");
    }

    /**
     * 修改为异常单
     */
    @Permission(menuname = "修改为异常单", value = "abnormal", method = RequestMethod.POST)
    public ResponseData<String> abnormal(@RequestParam("orderId") String orderId) {
        ShiroUser user = getShiroUser();
        /*if (!user.getRoleNames().stream().anyMatch(roleName -> StringUtils.equalsAny(roleName, "超级管理员"))) {
            return ResponseData.error("无数据权限");
        }*/
        Wrapper wrapper = new EntityWrapper<>();
        wrapper.eq("id", orderId);
        Map<String, Object> dataMapping = new HashMap<>();
        dataMapping.put("order_status", "异常单");
        dataMapping.put("success_time", new Date());
        Integer integer = orderMapper.updateForSet(MyBatisPlusUpdateUtils.toUpdateSet(dataMapping), wrapper);
        if (integer == 1) {
            appMapper.inserOrderHis(orderId, "异常单!", new Date(), user.getName());
            return ResponseData.success("设为异常单成功！");
        }
        return ResponseData.error("设为异常单失败！");
    }

    /**
     * @param createTimeStart  订单创建时间开始
     * @param createTimeEnd    订单创建时间结束
     * @param successTimeStart 订单完成时间开始
     * @param successTimeEnd   订单完成时间结束
     * @param userType         客户类型
     * @param orderNumber      订单编号
     * @param kapin            卡品
     * @param businessNumber   办理号码
     * @param contactNumber    联系号码
     * @param hehuorenSchool   合伙人学校
     * @param hehuorenName     合伙人姓名
     * @param orderStatus      订单状态
     * @param customerName     客户姓名
     * @param pageSize
     * @return
     */
    @Permission(menuname = "查询订单信息", value = "list", method = RequestMethod.POST)
    //@RequestMapping(value="list", method = RequestMethod.POST)
    public ResponseData<Page<Order>> listOrder(
            @RequestParam(name = "createTimeStart", required = false) String createTimeStart,
            @RequestParam(name = "createTimeEnd", required = false) String createTimeEnd, @RequestParam(name = "successTimeStart", required = false) String successTimeStart,
            @RequestParam(name = "successTimeEnd", required = false) String successTimeEnd, @RequestParam("userType") String userType,
            @RequestParam(name = "orderNumber", required = false) String orderNumber, @RequestParam(name = "kapin", required = false) String kapin,
            @RequestParam(name = "businessNumber", required = false) String businessNumber, @RequestParam(name = "contactNumber", required = false) String contactNumber,
            @RequestParam(name = "hehuorenSchool", required = false) String hehuorenSchool, @RequestParam(name = "hehuorenName", required = false) String hehuorenName,
            @RequestParam(name = "orderStatus", required = false) String orderStatus,
            @RequestParam(name = "customerName", required = false) String customerName, @RequestParam(name = "pageNo", required = false) int pageNo,
            @RequestParam(name = "pageSize", required = false) int pageSize) {
        ShiroUser user = getShiroUser();
        if (!user.getRoleNames().stream().anyMatch(roleName -> StringUtils.equalsAny(roleName, "超级管理员"))) {
            return ResponseData.error("无数据权限");
        }
        Wrapper<Order> wrapper = new EntityWrapper();
        wrapper.ge(StringUtils.isNotBlank(createTimeStart), "create_time", createTimeStart);
        wrapper.le(StringUtils.isNotBlank(createTimeEnd), "create_time", createTimeEnd);
        wrapper.ge(StringUtils.isNotBlank(successTimeStart), "success_time", successTimeStart);
        wrapper.le(StringUtils.isNotBlank(successTimeEnd), "success_time", successTimeEnd);
        wrapper.eq(StringUtils.isNotBlank(userType), "user_type", userType);
        wrapper.eq(StringUtils.isNotBlank(orderNumber), "order_number", orderNumber);
        //wrapper.eq(StringUtils.isNotBlank(kapin), "kapin", kapin);
        wrapper.eq(StringUtils.isNotBlank(customerName), "customer_name", customerName);
        wrapper.eq(StringUtils.isNotBlank(businessNumber), "business_number", businessNumber);
        wrapper.eq(StringUtils.isNotBlank(contactNumber), "contact_number", contactNumber);
        wrapper.eq(StringUtils.isNotBlank(hehuorenSchool), "hehuoren_school", hehuorenSchool);
        wrapper.like(StringUtils.isNotBlank(hehuorenName), "hehuoren_name", hehuorenName, SqlLike.DEFAULT);
        wrapper.eq(StringUtils.isNotBlank(orderStatus), "order_status", orderStatus).orderBy("create_time",false);

        Page<Order> page = new Page<>(pageNo, pageSize);
        List<Order> aPackage = orderMapper.selectPage(page, wrapper);
        page.setRecords(aPackage);
        return ResponseData.success(page, "查询完毕！");
    }

    @Permission(menuname = "导出订单", value = "download", method = RequestMethod.POST)
    public ResponseData<String> downloadOrderView(
            @RequestParam(name = "createTimeStart", required = false) String createTimeStart,
            @RequestParam(name = "createTimeEnd", required = false) String createTimeEnd, @RequestParam(name = "successTimeStart", required = false) String successTimeStart,
            @RequestParam(name = "successTimeEnd", required = false) String successTimeEnd, @RequestParam("userType") String userType,
            @RequestParam(name = "orderNumber", required = false) String orderNumber, @RequestParam(name = "kapin", required = false) String kapin,
            @RequestParam(name = "businessNumber", required = false) String businessNumber, @RequestParam(name = "contactNumber", required = false) String contactNumber,
            @RequestParam(name = "hehuorenSchool", required = false) String hehuorenSchool, @RequestParam(name = "hehuorenName", required = false) String hehuorenName,
            @RequestParam(name = "orderStatus", required = false) String orderStatus,
            @RequestParam(name = "customerName", required = false) String customerName) {

        String dateFormat = "YYYY-MM-DD";
        if (StringUtils.isNotBlank(createTimeStart) && StringUtils.isNotBlank(createTimeEnd)) {
            createTimeStart = createTimeStart.substring(0, dateFormat.length()) + " 00:00:00";
            createTimeEnd = createTimeEnd.substring(0, dateFormat.length()) + " 23:59:59";
        } else {
            createTimeStart = null;
            createTimeEnd = null;
        }
        Map<String, Object> map = new HashMap<>();
        ShiroUser user = getShiroUser();
        map.put("userId", user.getId());
        map.put("userName", user.getName());
        if (StringUtils.isNotBlank(createTimeStart)) {
            map.put("createTimeStart", createTimeStart);
        }
        if (StringUtils.isNotBlank(createTimeEnd)) {
            map.put("createTimeEnd", createTimeEnd);
        }
        if (StringUtils.isNotBlank(successTimeStart)) {
            map.put("successTimeStart", successTimeStart);
        }
        if (StringUtils.isNotBlank(successTimeEnd)) {
            map.put("successTimeEnd", successTimeEnd);
        }
        if (StringUtils.isNotBlank(userType)) {
            map.put("userType", userType);
        }
        if (StringUtils.isNotBlank(orderNumber)) {
            map.put("orderNumber", orderNumber);
        }
        if (StringUtils.isNotBlank(customerName)) {
            map.put("customerName", customerName);
        }
        if (StringUtils.isNotBlank(businessNumber)) {
            map.put("businessNumber", businessNumber);
        }
        if (StringUtils.isNotBlank(contactNumber)) {
            map.put("contactNumber", contactNumber);
        }
        if (StringUtils.isNotBlank(hehuorenSchool)) {
            map.put("hehuorenSchool", hehuorenSchool);
        }
        if (StringUtils.isNotBlank(hehuorenName)) {
            map.put("hehuorenName", hehuorenName);
        }
        if (StringUtils.isNotBlank(orderStatus)) {
            map.put("orderStatus", orderStatus);
        }
        try {
            taskImport.taskImport(map);
        } catch (Exception e) {
            log.error("订单导出队列异常", e.getMessage());
            return ResponseData.error("订单导出队列异常!");
        }
        return ResponseData.success("订单导出正在队列排队执行中，稍后请在下载列表下载！");
    }

    @Permission(menuname = "一人一码统计数据", value = "getHhrOrderInfo", method = RequestMethod.POST)
    public ResponseData<List<YrymReport>> getHhrOrderInfo(String startTime, String endTime) {
        List<YrymReport> subNameList = getReportNum(startTime, endTime);
        return ResponseData.success(subNameList, "查询完毕！");
    }

    @Permission(menuname = "导出一人一码统计报表", value = "outputHhrOrder", method = RequestMethod.POST)
    public void downloadHhrOrder(HttpServletResponse response, String startTime, String endTime) throws IOException {

        //获取数据
        List<YrymReport> reportNum = getReportNum(startTime, endTime);

        InputStream in = null;
        ExcelWriter excelWriter = null;
        ServletOutputStream outputStream = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //防止中文乱码
            String fileName = URLEncoder.encode("一人一码统计报表", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            in = this.getClass().getResourceAsStream("/static/一人一码统计报表模板.xlsx");
            outputStream = response.getOutputStream();
            excelWriter = EasyExcel.write(outputStream).withTemplate(in).excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(reportNum, writeSheet);
            Map<String, Object> map = new HashMap<String, Object>();
            LocalDate nowTime = LocalDate.now();
            map.put("month", nowTime.getMonthValue());
            map.put("date", nowTime.getDayOfMonth());
            excelWriter.fill(map, writeSheet);
        } catch (Exception e) {
            log.error("一人一码下载文件失败", e.getMessage());
            throw e;
        } finally {
            //关闭资源
            if (excelWriter != null) {
                try {
                    excelWriter.finish();
                }catch(Exception e) {
                }
            }
            if (outputStream != null) {
                try{
                    outputStream.flush();
                }catch(Exception e) {
                }
            }
            if (in != null) {
                try{
                    in.close();
                }catch(Exception e) {
                }
            }
        }
    }

    /**
     * 根据县分名称获取一人一码发展统计表数据
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return
     */
    private List<YrymReport> getReportNum(String startTime, String endTime) {

        List<YrymReport> resultList = new ArrayList<>();
        List<String> subNameList = YrymReport.subNameList;
        for (String tempSubName : subNameList) {
            YrymReport tempYrym = new YrymReport();
            tempYrym.setSubName(tempSubName);
            resultList.add(tempYrym);
        }

        //学校数
        Wrapper<School> schoolNumWrapper = new EntityWrapper<>();
        schoolNumWrapper.setSqlSelect("sub_name AS subName,COUNT(1) AS schoolNum");
        schoolNumWrapper.isNotNull("sub_name");
        schoolNumWrapper.groupBy("sub_name");
        List<Map<String, Object>> schoolNumList = schoolMapper.selectMaps(schoolNumWrapper);

        //学生数
        Wrapper<SysUser> studentNumWrapper = new EntityWrapper<>();
        studentNumWrapper.setSqlSelect("substName AS subName,COUNT(1) AS studentNum");
        studentNumWrapper.isNotNull("substName");
        studentNumWrapper.groupBy("substName");
        List<Map<String, Object>> studentNumList = sysUserMapper.selectMaps(studentNumWrapper);

        //通用查询模板
        Wrapper<Order> commonWrapper = new EntityWrapper();
        String selectSql = "hehuoren_area AS subName," +
                "COUNT( DISTINCT `id_card` AND CASE WHEN user_type = 0 THEN 1 END ) AS newUserNum," +
                "COUNT( DISTINCT `id_card` AND CASE WHEN user_type = 5 THEN 1 END ) AS newAloneNum," +
                "COUNT( DISTINCT `id_card` AND CASE WHEN user_type = 3 THEN 1 END ) AS newMixNum," +
                "COUNT( DISTINCT `id_card`,`now_package`,`business_package`AND CASE WHEN user_type = 1 THEN 1 END ) AS oldUserNum," +
                "COUNT( DISTINCT `id_card`,`now_package`,`business_package`AND CASE WHEN user_type = 2 THEN 1 END ) AS oldContinueNum," +
                "COUNT( DISTINCT CASE WHEN user_type IN (0, 3, 5) AND order_status IN ('已发货', '已完成') THEN 1 END ) AS finishNewNum," +
                "COUNT( CASE WHEN user_type IN (1,2) AND order_status IN ('已发货', '已完成') THEN 1 END ) AS finishOldNum," +
                "COUNT( DISTINCT `id_card` AND CASE WHEN user_type = 0 THEN 1 END ) +" +
                " COUNT( DISTINCT `id_card` AND CASE WHEN user_type = 5 THEN 1 END ) +" +
                " COUNT( DISTINCT `id_card` AND CASE WHEN user_type = 3 THEN 1 END ) +" +
                " COUNT( DISTINCT `id_card`,`now_package`,`business_package`AND CASE WHEN user_type = 1 THEN 1 END ) +" +
                " COUNT( DISTINCT `id_card`,`now_package`,`business_package`AND CASE WHEN user_type = 2 THEN 1 END ) AS sum";
        commonWrapper.setSqlSelect(selectSql);
        commonWrapper.ne("order_status", "异常单");
        commonWrapper.isNotNull("hehuoren_area");
        commonWrapper.groupBy("hehuoren_area");

        //周
        Wrapper<Order> weekWrapper = commonWrapper.clone();
        if (StringUtils.isNotBlank(endTime)) {
            weekWrapper.addFilter("DATE(success_time) >= DATE_SUB(DATE('" + endTime + "'), INTERVAL 7 DAY)");
        } else {
            weekWrapper.addFilter("DATE(success_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)");
        }
        List<Map<String, Object>> weekNumList = orderMapper.selectMaps(weekWrapper);
        //月
        Wrapper<Order> monthWrapper = commonWrapper.clone();
        if (StringUtils.isNotBlank(endTime)) {
            monthWrapper.addFilter("DATE(success_time) >= DATE_SUB(DATE('" + endTime + "'), INTERVAL 30 DAY)");
        } else {
            monthWrapper.addFilter("DATE(success_time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)");
        }
        List<Map<String, Object>> monthNumList = orderMapper.selectMaps(monthWrapper);
        //累计
        Wrapper<Order> allWrapper = commonWrapper.clone();
        if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
            allWrapper.between("DATE(success_time)", startTime, endTime);
        }
        List<Map<String, Object>> allNumList = orderMapper.selectMaps(allWrapper);

        YrymReportUtils.joinData(resultList, schoolNumList, 1, null);
        YrymReportUtils.joinData(resultList, studentNumList, 2, null);
        YrymReportUtils.joinData(resultList, weekNumList, 3, "week");
        YrymReportUtils.joinData(resultList, monthNumList, 3, "month");
        YrymReportUtils.joinData(resultList, allNumList, 3, "all");
        YrymReportUtils.setAllSum(resultList);
        return resultList;
    }

    /**
     * 受理订单
     *
     * @param file 上传的数据文件
     * @return
     */
    @Permission(menuname = "上传数据", value = "importDate", method = RequestMethod.POST)
    public ResponseData<String> updateDatas(@RequestParam(name = "file") MultipartFile file) {
        StringBuffer error = new StringBuffer();
        if (!StringUtils.endsWithAny(file.getOriginalFilename(), "xlsx", "xls")) {
            return ResponseData.error("手工上传数据仅支持Excel文件，其他格式不支持！");
        }
        ShiroUser user = getShiroUser();
        // 当前是否有数据权限
        boolean hasDataPermission = user.getRoleNames().stream().anyMatch(data -> StringUtils.equalsAny(data, "活动上单员", "数据管理员", "超级管理员"));
        if (!hasDataPermission) {
            return ResponseData.error("无数据权限！");
        }
        ImportExcel importExcel = null;
        try {
            importExcel = new ImportExcel(file, HEADER_NUM, SHEET_INDEX);
        } catch (Exception e) {
            log.error("受理单导入异常", e.getMessage());
            return ResponseData.error("导入异常！");
        }
        List<Map<String, Object>> listMap = importExcel.getDataListMap();
        if (listMap.size() == 1) {
            return ResponseData.error("文件不能为空");
        }
        if (!listMap.get(0).get("a").toString().equals(SL_ORDERNUMBER)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("b").toString().equals(SL_ACCEPTRESULT)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("c").toString().equals(SL_ACCEPTNAME)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("d").toString().equals(SL_ACCERPCOMMENT)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (listMap.get(0).size() != 4) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        //校验数据
        try {
            //校验数据
            for (int i = 1; i < listMap.size(); i++) {
                Map<String, Object> temp = listMap.get(i);
                if (!temp.containsKey("a")) {
                    return ResponseData.error("第" + (i + 1) + "行的订单编号不能为空");
                }
                if (StringUtils.isBlank(temp.get("a").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的订单编号不能为空");
                }
                if (!temp.containsKey("b")) {
                    return ResponseData.error("第" + (i + 1) + "行的受理结果不能为空");
                }
                if (StringUtils.isBlank(temp.get("b").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的受理结果不能为空");
                }
                if (!temp.containsKey("c")) {
                    return ResponseData.error("第" + (i + 1) + "行的受理人不能为空");
                }
                if (StringUtils.isBlank(temp.get("c").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的受理人不能为空");
                }
                if ("受理不成功".equals(temp.get("b").toString())) {
                    if (!temp.containsKey("d")) {
                        return ResponseData.error("第" + (i + 1) + "行的备注不能为空");
                    }
                    if (StringUtils.isBlank(temp.get("d").toString())) {
                        return ResponseData.error("第" + (i + 1) + "行的备注不能为空");
                    }
                }
            }
            //检查excel表中是否有重复人员编号
            int size = listMap.size();
            Set<String> accountSet = new HashSet<>(size);
            for (int i = 0; i < size; i++) {
                Map<String, Object> temp = listMap.get(i);
                accountSet.add((String) temp.get("a"));
            }
            if (accountSet.size() != size) {
                return ResponseData.error("文件中有重复的订单编号，请确认订单编号不能重复!");
            }
        } catch (Exception e) {
            log.error("受理单导入异常", e.getMessage());
            return ResponseData.error("导入异常！");
        }
        int number = 0;
        Date acceptDate = new Date();
        Date successTime = acceptDate;
        for (int i = 1; i < listMap.size(); i++) {
            Map<String, Object> map = listMap.get(i);
            String orderNumber = map.get("a").toString();
            String acceptResult = map.get("b").toString();
            String acceptName = map.get("c").toString();
            String acceptComment = null;
            String orderStatus = null;
            String status = null;
            if (StringUtils.isNotBlank(map.get("d").toString())) {
                acceptComment = map.get("d").toString();
            }
            if ("受理成功".equals(map.get("b").toString())) {
                orderStatus = "已完成";
                status = "受理成功";
            } else {
                orderStatus = "异常单";
                status = "受理失败，" + acceptComment;
            }
            Integer integer = null;
            if (StringUtils.isNotBlank(acceptComment)) {
                integer = orderMapper.updateOrderSUByOrderNumberIsNotNull(orderNumber, orderStatus, acceptName, acceptResult, acceptComment, acceptDate,successTime);
            } else {
                integer = orderMapper.updateOrderSUByOrderNumberIsNull(orderNumber, orderStatus, acceptName, acceptResult, acceptDate,successTime);
            }

            if (integer == 1) {
                number++;
                String id = orderMapper.selectIdByOrderNumber(orderNumber);
                appMapper.inserOrderHis(id, status, acceptDate, user.getName());
            } else {
                error.append("第" + (i + 1) + "行的更新失败,");
            }
        }
        error.append("成功更新" + number + "条数据！");
        return ResponseData.success(error.toString(), "导入完成");
    }

    /**
     * 上传快递清单
     *
     * @param file 上传的数据文件
     * @return
     */
    @Permission(menuname = "上传快递清单", value = "importExpressDelivery", method = RequestMethod.POST)
    public ResponseData<String> importExpressDelivery(@RequestParam(name = "file") MultipartFile file) {
        StringBuffer error = new StringBuffer();
        if (!StringUtils.endsWithAny(file.getOriginalFilename(), "xlsx", "xls")) {
            return ResponseData.error("手工上传数据仅支持Excel文件，其他格式不支持！");
        }
        ShiroUser user = getShiroUser();
        // 当前是否有数据权限
        boolean hasDataPermission = user.getRoleNames().stream().anyMatch(data -> StringUtils.equalsAny(data, "活动上单员", "数据管理员", "超级管理员"));
        if (!hasDataPermission) {
            return ResponseData.error("无数据权限！");
        }
        ImportExcel importExcel = null;
        try {
            importExcel = new ImportExcel(file, HEADER_NUM, SHEET_INDEX);
        } catch (Exception e) {
            log.error("快递单导入异常", e.getMessage());
            return ResponseData.error("导入异常！");
        }
        List<Map<String, Object>> listMap = importExcel.getDataListMap();
        if (listMap.size() == 0) {
            return ResponseData.error("文件不能为空");
        }
        if (listMap.get(0).size() != 4) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("a").toString().equals(KD_ORDERNUMBER)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("b").toString().equals(SL_SENDTYPE)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("c").toString().equals(SL_COMPANY)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("d").toString().equals(SL_KUAIDORDER)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }

        //校验数据
        try {
            //校验数据
            for (int i = 1; i < listMap.size(); i++) {
                Map<String, Object> temp = listMap.get(i);
                if (!temp.containsKey("a")) {
                    return ResponseData.error("第" + (i + 1) + "行的订单编号不能为空");
                }
                if (StringUtils.isBlank(temp.get("a").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的订单编号不能为空");
                }
                if (!temp.containsKey("b")) {
                    return ResponseData.error("第" + (i + 1) + "行的快递方式不能为空");
                }
                if (StringUtils.isBlank(temp.get("b").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的快递方式不能为空");
                }
                if ("快递".equals(temp.get("b").toString())) {
                    if (!temp.containsKey("c")) {
                        return ResponseData.error("第" + (i + 1) + "行的快递公司不能为空");
                    }
                    if (!temp.containsKey("d")) {
                        return ResponseData.error("第" + (i + 1) + "行的快递单号不能为空");
                    }
                    if (StringUtils.isBlank(temp.get("c").toString())) {
                        return ResponseData.error("第" + (i + 1) + "行的快递公司不能为空");
                    }
                    if (StringUtils.isBlank(temp.get("d").toString())) {
                        return ResponseData.error("第" + (i + 1) + "行的快递单号不能为空");
                    }
                }
            }
            //检查excel表中是否有重复人员编号
            int size = listMap.size();
            Set<String> accountSet = new HashSet<>(size);
            for (int i = 0; i < size; i++) {
                Map<String, Object> temp = listMap.get(i);
                accountSet.add((String) temp.get("a"));
            }
            if (accountSet.size() != size) {
                return ResponseData.error("文件中有重复的订单编号，请确认订单编号不能重复!");
            }
        } catch (Exception e) {
            log.error("快递单导入异常", e.getMessage());
            return ResponseData.error("导入异常！");
        }
        int number = 0;
        Date successTime = new Date();
        for (int i = 1; i < listMap.size(); i++) {
            Map<String, Object> map = listMap.get(i);
            String orderNumber = map.get("a").toString();
            String sendType = map.get("b").toString();
            String company = null;
            String kuaidiOrder = null;
            String orderStatus = null;
            String xbOrderId = orderMapper.selectxbOrderIdByOrderNumber(orderNumber);
            if (StringUtils.isNotBlank(xbOrderId)) {
                orderStatus = "待活体";
                successTime = null;
            } else {
                orderStatus = "已完成";
            }
            if ("快递".equals(map.get("b").toString())) {
                company = map.get("c").toString();
                kuaidiOrder = map.get("d").toString();
            }
            Integer integer = null;
            if (StringUtils.isBlank(company) || StringUtils.isBlank(kuaidiOrder)) {
                if(successTime == null){
                    integer = orderMapper.updateOrderKDIsNull(orderNumber, sendType, orderStatus);
                }else {
                    integer = orderMapper.updateOrderKDIsNull(orderNumber, sendType, orderStatus,successTime);
                }
            } else {
                if(successTime == null){
                    integer = orderMapper.updateOrderKDIsNotNull(orderNumber, company, kuaidiOrder, sendType, orderStatus);
                }else {
                    integer = orderMapper.updateOrderKDIsNotNull(orderNumber, company, kuaidiOrder, sendType, orderStatus,successTime);
                }

            }
            if (integer == 1) {
                number++;
                String id = orderMapper.selectIdByOrderNumber(orderNumber);
                appMapper.inserOrderHis(id, "发货中，快递方式：" + sendType, new Date(), user.getName());
            } else {
                error.append("第" + (i + 1) + "行的更新失败,");
            }
        }
        error.append("成功更新" + number + "条数据！");
        return ResponseData.success(error.toString(), "导入完成");
    }

    /**
     * 受理订单模板下载
     *
     * @return
     */
    @Permission(menuname = "受理单导入模板", method = RequestMethod.POST, value = "downloadMoBan")
    public ResponseData downloadMoBan() {
        ArrayList<String> columns = new ArrayList<>();
        columns.add(SL_ORDERNUMBER);
        columns.add(SL_ACCEPTRESULT);
        columns.add(SL_ACCEPTNAME);
        columns.add(SL_ACCERPCOMMENT);
        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet("一人一码受理单");
            sheet.setColumnWidth(0, 5000);
            sheet.setColumnWidth(2, 4000);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(3, 9000);
            //下拉选项
            List<String> fengju = new ArrayList<>();
            fengju.add("受理不成功");
            fengju.add("受理成功");
            sheet = this.getXSSFSheet(sheet, fengju, 1, 1);
            //添加表头
            final XSSFRow row = sheet.createRow(0);
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            columns.forEach(val -> {
                XSSFCell cell = row.createCell(Math.max(row.getLastCellNum(), 0));
                cell.setCellValue(val);
            });
            HttpServletResponse response = getHttpServletResponse();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;fileName=data.xlsx");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (IOException e) {
            log.error("受理单模板文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }
        return ResponseData.error("受理单模板出错");
    }

    @Permission(menuname = "快递导入模板", method = RequestMethod.POST, value = "downloadMoBanKD")
    public ResponseData downloadMoBanKD() {
        ArrayList<String> columns = new ArrayList<>();
        columns.add(KD_ORDERNUMBER);
        columns.add(SL_SENDTYPE);
        columns.add(SL_COMPANY);
        columns.add(SL_KUAIDORDER);
        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet("快递清单");
            sheet.setColumnWidth(0, 5000);
            sheet.setColumnWidth(2, 9000);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(3, 9000);
            //下拉选项
            List<String> fengju = new ArrayList<>();
            fengju.add("快递");
            fengju.add("送货上门");
            sheet = this.getXSSFSheet(sheet, fengju, 1, 1);
            //添加表头
            final XSSFRow row = sheet.createRow(0);
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            columns.forEach(val -> {
                XSSFCell cell = row.createCell(Math.max(row.getLastCellNum(), 0));
                cell.setCellValue(val);
            });
            HttpServletResponse response = getHttpServletResponse();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;fileName=data.xlsx");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (IOException e) {
            log.error("快递模板文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }
        return ResponseData.error("快递模板出错");
    }


    XSSFSheet getXSSFSheet(XSSFSheet sheet, List<String> datas, int firstcol, int lastcol) {
        if (datas.size() == 0) {
            return sheet;
        }
        String[] strdata = new String[datas.size()];
        for (int i = 0; i < datas.size(); i++) {
            strdata[i] = datas.get(i);
        }
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strdata);
        CellRangeAddressList addressList = new CellRangeAddressList(1, 20000, firstcol, lastcol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint1, addressList);
        sheet.addValidationData(validation);
        return sheet;
    }
}
