package com.winsun.controller;

import com.alibaba.fastjson.JSON;
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.*;
import com.winsun.mapper.*;
import com.winsun.utils.ExcelDealUtils;
import com.winsun.utils.ImportExcel;
import com.winsun.utils.MyBatisPlusUpdateUtils;
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.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.RedisTemplate;
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.util.*;

/**
 * 楼长薪酬管理
 * @Author: pxl
 * @create 2020/4/29 14:08
 */
@Slf4j
@RestController
@RequestMapping("/lzWage") //
public class LzSalaryController extends BaseController {
    /**
     * 导入
     */
    private static final int HEADER_NUM = -1;
    private static final int SHEET_INDEX = 0;
    /**
     * 楼长薪酬信息模板列名
     */
    private static String SL_month = "KPI月份";
    private static String SL_substName = "县分";
    private static String SL_schoolName = "学校";
    private static String SL_sysuName = "姓名(必填)";
    private static String SL_rechargeNum = "放号充值数";
    private static String SL_rechargeWage = "放号薪酬";
    private static String SL_kpi = "kpi总值";
    private static String SL_kpiWage = "kpi薪酬";
    private static String SL_wage = "总薪酬";

    @Autowired
    private LzWageMpapper lzWageMpapper;
    @Autowired
    private LzKpiMapper lzKpiMapper;
    @Autowired
    private SchoolMapper schoolMapper;
    @Autowired
    private UserSchoolMapper userSchoolMapper;
    @Autowired
    private HhrUserMapper hhrUserMapper;
    @Autowired
    private SalesListMapper salesOrderMapper;
    @Autowired
    private SysUserMapper sysUserMapper;
    @Autowired
    @Qualifier("redisStringTemplate")
    private RedisTemplate redisTemplate;
    /**
     * 楼长薪酬添加
     *
     * @param lzKpi 添加对象json字符串
     * @return
     */
    @Permission(menuname = "添加楼长薪酬信息", value = "insert", method = RequestMethod.POST)
    public ResponseData<String> insertProduct(@RequestParam("lzKpi") String lzKpi) {
        ShiroUser user = getShiroUser();
        if (!user.getRoleNames().stream().anyMatch(roleName -> StringUtils.equalsAny(roleName, "超级管理员"))) {
            return ResponseData.error("无数据权限");
        }
        HashMap hashMap = JSON.parseObject(lzKpi, HashMap.class);
        Wrapper<SysUser> sysWrapper = new EntityWrapper<>();
        String account = hashMap.get("account").toString();
        String substName = hashMap.get("substName").toString();
        sysWrapper.eq(StringUtils.isNotBlank(account),"account",account);
        sysWrapper.eq(StringUtils.isNotBlank(substName),"name",substName);
        List<SysUser> sysUserList = sysUserMapper.selectList(sysWrapper);
        if(sysUserList.size()>0){
            LzWage wage = new LzWage();
            wage.setUserId(Integer.valueOf(sysUserList.get(0).getId()));
            wage.setMonth(hashMap.get("month").toString());
            wage.setRechargeNum(Integer.valueOf(hashMap.get("rechargeNum").toString()));
            wage.setRechargeWage(Double.valueOf(hashMap.get("rechargeWage").toString()));
            wage.setKpi(Double.valueOf(hashMap.get("kpi").toString()));
            wage.setKpiWage(Double.valueOf(hashMap.get("kpiWage").toString()));
            wage.setWage(Double.valueOf(hashMap.get("wage").toString()));
            lzWageMpapper.insert(wage);
            return ResponseData.success("添加成功！");
        }
        return ResponseData.error("账号不存在?");
    }

    /**
     * 楼长薪酬删除
     * @param id 需要删除的id
     * @return
     */
    @Permission(menuname = "删除楼长薪酬信息", value = "delete", method = RequestMethod.POST)
    public ResponseData<String> deleteProduct(@RequestParam(name="id", required = false) String id,@RequestParam(name="isdel", required = false) String isdel) {
        ShiroUser user = getShiroUser();
        if (!user.getRoleNames().stream().anyMatch(roleName -> StringUtils.equalsAny(roleName, "超级管理员"))) {
            return ResponseData.error("无数据权限");
        }
        //Wrapper wrapper = new EntityWrapper<>();
        //wrapper.eq(StringUtils.isNotBlank(id),"id", id);
        //Map<String, Object> dataMapping = new HashMap<>();
        //dataMapping.put("is_del", isdel);
        //lzKpiMapper.updateForSet(MyBatisPlusUpdateUtils.toUpdateSet(dataMapping), wrapper);
        if(StringUtils.isNotBlank(id)){
            lzWageMpapper.deleteById(id);
            return ResponseData.success("操作成功！");
        }
        return ResponseData.error("删除id不能为空!");
    }

    /**
     * 修改楼长薪酬信息
     * @param lzKpi 编辑对象json字符串
     * @return
     */
    @Permission(menuname = "修改楼长薪酬信息", value = "update", method = RequestMethod.POST)
    public ResponseData<String> updateProduct(@RequestParam("lzKpi") String lzKpi) {
        ShiroUser user = getShiroUser();
        if (!user.getRoleNames().stream().anyMatch(roleName -> StringUtils.equalsAny(roleName, "超级管理员"))) {
            return ResponseData.error("无数据权限");
        }
        LzKpi pro = JSON.parseObject(lzKpi, LzKpi.class);
        Wrapper wrapper = new EntityWrapper<>();
        wrapper.eq("id", pro.getId());
        Map<String, Object> dataMapping = new HashMap<>();
        dataMapping.put("report_daily", pro.getReportDaily());
        dataMapping.put("collective_sales", pro.getCollectiveSales());
        dataMapping.put("weekly_meeting", pro.getWeeklyMeeting());
        dataMapping.put("organ_training", pro.getOrganTraining());
        dataMapping.put("design_plan", pro.getDesignPlan());
        dataMapping.put("kpi", pro.getKpi());
        dataMapping.put("user_id", pro.getUserId());
        dataMapping.put("month", pro.getMonth());
        dataMapping.put("is_Del", pro.getIsDel());
        lzWageMpapper.updateForSet(MyBatisPlusUpdateUtils.toUpdateSet(dataMapping), wrapper);
        return ResponseData.success("修改成功！");
    }
    /**
     * 生成楼长上月薪酬
     * @return
     */
    @Permission(menuname = "生成楼长上月薪酬", value = "createSalary", method = RequestMethod.POST)
    public ResponseData<String> createSalary() {
        ShiroUser user = getShiroUser();
        if (!user.getRoleNames().stream().anyMatch(roleName -> StringUtils.equalsAny(roleName, "超级管理员"))) {
            return ResponseData.error("无数据权限");
        }
        List<LzWage> lzWageList = new LinkedList<>();
        List<String> userIds = new LinkedList<>();

        Date date = new Date();
        // 使用String类format()方法格式化日期
        String year = String.format("%tY", date);
        Integer m = Integer.valueOf(String.format("%tm", date));
        String month = "";
        if(m > 10){
            month = year+"-"+(m-1);
        }else if(m == 0){
            month = year+"-12";
        }else{
            month = year+"-0"+(m-1);
        }

        Wrapper<HhrUser> hhrUserWrapper = new EntityWrapper<>();
        hhrUserWrapper.eq("position","楼长");
        List<HhrUser> hhrUserList = hhrUserMapper.selectList(hhrUserWrapper);
        for (HhrUser hhrUser : hhrUserList) {
            LzWage lzWage = new LzWage();
            String userId = hhrUser.getId();
            lzWage.setMonth(month);
            lzWage.setUserId(Integer.valueOf(userId));
            userIds.add(userId);
            lzWageList.add(lzWage);
        }

        Wrapper<SalesList> salesOrderWrapper =  new EntityWrapper<>();
        salesOrderWrapper.in("user_id",userIds);
        salesOrderWrapper.like("activate_time",month,SqlLike.RIGHT);
        List<SalesList> salesOrderList = salesOrderMapper.selectList(salesOrderWrapper);

        Wrapper<LzKpi> lzKpiWrapper =  new EntityWrapper<>();
        lzKpiWrapper.in("user_id",userIds);
        lzKpiWrapper.eq("month",month);
        List<LzKpi> lzKpiList = lzKpiMapper.selectList(lzKpiWrapper);

        Wrapper<LzWage> wrageWrapper =  new EntityWrapper<>();
        wrageWrapper.in("user_id",userIds);
        wrageWrapper.in("month",month);
        List<LzWage> wrageList = lzWageMpapper.selectList(wrageWrapper);
        List<Integer> wrageIds = new LinkedList<>();
        if(wrageList.size()>0){
            for (LzWage lzWage : wrageList) {
                wrageIds.add(lzWage.getId());
            }
        }

        Integer insertCount = 0;
        for (String userId : userIds) {
            Integer count = 0;    //充值达标数
            for (SalesList salesOrder : salesOrderList) {
                if(userId.equals(salesOrder.getUserId())){
                    double recentlyAmount = salesOrder.getRecentlyAmount();
                    //充值金额50以上算充值达标
                    if(recentlyAmount > 50){
                        count++;
                    }
                }
            }
            double kpiTotail = 0;
            for (LzKpi lzKpi : lzKpiList) {
                if(userId.equals(lzKpi.getUserId())){
                    kpiTotail += lzKpi.getKpi();
                }
            }
            for (LzWage lzWage : lzWageList) {
                if(userId.equals(lzWage.getUserId().toString())){
                    lzWage.setRechargeNum(count);      //充值达标数
                    double rechargeWage = count * 20;//充值薪酬=充值达标数*20
                    //充值薪酬和kpi薪酬最高为500
                    if(rechargeWage > 500){
                        rechargeWage = 500;
                    }
                    lzWage.setRechargeWage(rechargeWage); //充值达标薪酬
                    lzWage.setKpi(kpiTotail);
                    double kpiWage = kpiTotail * 5 ;  //kpi薪酬=kpi*5
                    //充值薪酬和kpi薪酬最高为500
                    if(kpiWage > 500){
                        kpiWage = 500;
                    }
                    lzWage.setKpiWage(kpiWage);
                    //总薪酬=充值薪酬+kpi薪酬；总薪酬超过800的部分要收20%的税
                    double wage = kpiWage + rechargeWage;
                    if(wage > 800){
                        wage = wage + (wage-800)*20/100;
                    }
                    lzWage.setWage(wage);
                    if(wrageIds.size()>0){
                        Wrapper<LzWage> delWrapper = new EntityWrapper<>();
                        delWrapper.in("id",wrageIds);
                        lzWageMpapper.delete(delWrapper);
                    }
                    insertCount += lzWageMpapper.insert(lzWage);
                }
            }
        }
        return ResponseData.success(insertCount+"位楼长"+month+"月份薪酬已生成！");
    }

    /**
     *
     * @param userName
     * @param substName
     * @param schoolName
     * @param month
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Permission(menuname = "查询楼长薪酬信息", value = "list", method = RequestMethod.POST)
    public ResponseData<Page<Map<String,Object>>> listProduct(
            @RequestParam(name = "userName", required = false) String userName,@RequestParam(name = "substName", required = false) String substName,
            @RequestParam(name = "schoolName", required = false) String schoolName,@RequestParam(name = "month", required = false) String month,
            @RequestParam(name = "pageNo") int pageNo, @RequestParam(name = "pageSize") int pageSize) {
        ShiroUser user = getShiroUser();

        Page<Map<String,Object>> page = new Page<>(pageNo, pageSize);
        List<Map<String,Object>> dataList = new ArrayList<>();
        List<Map<String,Object>> list = new ArrayList<>();

        List<String> userIds01 = new ArrayList<>();
        Wrapper<HhrUser> hhrUserWrapper = new EntityWrapper<>();
        hhrUserWrapper.eq("position","楼长");
        List<HhrUser> hhrUserList = hhrUserMapper.selectList(hhrUserWrapper);
        for (HhrUser hhrUser : hhrUserList) {
            userIds01.add(hhrUser.getId());
        }

        List<String> userIds1 = new ArrayList<>();
        Wrapper<SysUser> sysWrapper = new EntityWrapper<>();
        sysWrapper.in("id",userIds01);
        sysWrapper.like(StringUtils.isNotBlank(userName), "name", userName, SqlLike.DEFAULT);
        List<SysUser> sysUserList = sysUserMapper.selectList(sysWrapper);
        for (SysUser sysUser : sysUserList) {
            userIds1.add(sysUser.getId());
        }
        if(sysUserList.size()==0){
            page.setRecords(new ArrayList<>());
            page.setTotal(0);
            return ResponseData.success(page);
        }
        List<Integer> schoolIds = new ArrayList<>();
        Wrapper<School> schoolWrapper = new EntityWrapper<>();
        schoolWrapper.eq(StringUtils.isNotBlank(substName), "sub_name", substName);
        schoolWrapper.eq(StringUtils.isNotBlank(schoolName), "school_name", schoolName);
        List<School> schoolList = schoolMapper.selectList(schoolWrapper);
        for (School school : schoolList) {
            schoolIds.add(school.getId());
        }
        if(schoolList.size()==0){
            page.setRecords(new ArrayList<>());
            page.setTotal(0);
            return ResponseData.success(page);
        }
        List<String> userIds2 = new ArrayList<>();
        Wrapper<UserSchool> usWrapper = new EntityWrapper<>();
        usWrapper.in("school_id",schoolIds);
        List<UserSchool> userSchoolList = userSchoolMapper.selectList(usWrapper);
        for (UserSchool userSchool : userSchoolList) {
            userIds2.add(userSchool.getUserId());
        }
        List<String> userIds = new ArrayList<>();
        for (String s : userIds1) {
            for (String s1 : userIds2) {
                if(s.equals(s1)){
                    userIds.add(s1);
                    break;
                }
            }
        }
        Wrapper<LzWage> wrapper = new EntityWrapper();
        if(month.length()>7){
            month = month.substring(0,7);
        }
        wrapper.eq(StringUtils.isNotBlank(month),"month",month);
        wrapper.in("user_id",userIds);
        List<LzWage> lzWages = lzWageMpapper.selectList(wrapper);
        if(lzWages.size()==0){
            page.setRecords(new ArrayList<>());
            page.setTotal(0);
            return ResponseData.success(page);
        }
        for (LzWage lzWage : lzWages) {
            Map<String,Object> map = new HashMap<>();
            map.put("id",lzWage.getId());
            map.put("month",lzWage.getMonth());
            map.put("rechargeNum",lzWage.getRechargeNum());
            map.put("rechargeWage",lzWage.getRechargeWage());
            map.put("kpi",lzWage.getKpi());
            map.put("kpiWage",lzWage.getKpiWage());
            map.put("wage",lzWage.getWage());
            map.put("userId",lzWage.getUserId());
            for (SysUser sysUser : sysUserList) {
                if((lzWage.getUserId().toString()).equals(sysUser.getId())){
                    map.put("sysuName",sysUser.getName());
                    map.put("account",sysUser.getAccount());
                    break;
                }
            }
            for (UserSchool userSchool : userSchoolList) {
                if((lzWage.getUserId().toString()).equals(userSchool.getUserId())){
                    String schoolId = userSchool.getSchoolId();
                    for (School school : schoolList) {
                        if(schoolId.equals(school.getId()+"")){
                            map.put("schoolName",school.getSchoolName());
                            map.put("substName",school.getSubName());
                            break;
                        }
                    }
                }
            }
            list.add(map);
        }

        if(lzWages.size() < pageSize) {
            dataList = list;
        }else {
            Integer endpageSize = (pageNo-1) == lzWages.size()/pageSize?(pageNo-1)*pageSize+lzWages.size()%pageSize:pageNo*pageSize;
            dataList = list.subList((pageNo-1)*pageSize, endpageSize);
        }

        page.setRecords(dataList);
        page.setTotal(lzWages.size());
        return ResponseData.success(page, "查询成功！");
    }

    /**
     *
     * @param userName
     * @param substName
     * @param schoolName
     * @param month
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Permission(menuname = "导出楼长薪酬信息", value = "excelOut", method = RequestMethod.POST)
    public void excelOut(
            @RequestParam(name = "userName", required = false) String userName,@RequestParam(name = "substName", required = false) String substName,
            @RequestParam(name = "schoolName", required = false) String schoolName,@RequestParam(name = "month", required = false) String month,
            @RequestParam(name = "pageNo") int pageNo, @RequestParam(name = "pageSize") int pageSize
    )  throws IOException {
        ShiroUser user = getShiroUser();

        List<String> userIds01 = new ArrayList<>();
        Wrapper<HhrUser> hhrUserWrapper = new EntityWrapper<>();
        hhrUserWrapper.eq("position","楼长");
        List<HhrUser> hhrUserList = hhrUserMapper.selectList(hhrUserWrapper);
        for (HhrUser hhrUser : hhrUserList) {
            userIds01.add(hhrUser.getId());
        }

        List<String> userIds1 = new ArrayList<>();
        Wrapper<SysUser> sysWrapper = new EntityWrapper<>();
        sysWrapper.in("id",userIds01);
        sysWrapper.like(StringUtils.isNotBlank(userName), "name", userName, SqlLike.DEFAULT);
        List<SysUser> sysUserList = sysUserMapper.selectList(sysWrapper);
        for (SysUser sysUser : sysUserList) {
            userIds1.add(sysUser.getId());
        }
        if(sysUserList.size()==0){
            log.error("查询数据为空！");
            return;
        }
        List<Integer> schoolIds = new ArrayList<>();
        Wrapper<School> schoolWrapper = new EntityWrapper<>();
        schoolWrapper.eq(StringUtils.isNotBlank(substName), "substName", substName);
        schoolWrapper.eq(StringUtils.isNotBlank(schoolName), "schoolName", schoolName);
        List<School> schoolList = schoolMapper.selectList(schoolWrapper);
        for (School school : schoolList) {
            schoolIds.add(school.getId());
        }
        if(schoolList.size()==0){
            log.error("查询数据为空！");
            return;
        }
        List<String> userIds = new ArrayList<>();
        Wrapper<UserSchool> usWrapper = new EntityWrapper<>();
        usWrapper.in("user_id",userIds1);
        usWrapper.in("school_id",schoolIds);
        List<UserSchool> userSchoolList = userSchoolMapper.selectList(usWrapper);
        List<UserSchool> userSchoolsTemp = new ArrayList<>();
        for (UserSchool userSchool : userSchoolList) {
            userIds.add(userSchool.getUserId());
        }
        List<Map<String,Object>> list = new ArrayList<>();
        Wrapper<LzWage> wrapper = new EntityWrapper();
        if(month.length()>7){
            month = month.substring(0,7);
        }
        wrapper.eq(StringUtils.isNotBlank(month),"month",month);
        wrapper.in("user_id",userIds);
        List<LzWage> lzWages = lzWageMpapper.selectList(wrapper);
        if(lzWages.size()==0){
            log.error("查询数据为空！");
            return;
        }
        for (LzWage lzWage : lzWages) {
            Map<String,Object> map = new HashMap<>();
            map.put("id",lzWage.getId());
            map.put("month",lzWage.getMonth());
            map.put("rechargeNum",lzWage.getRechargeNum());
            map.put("rechargeWage",lzWage.getRechargeWage());
            map.put("kpi",lzWage.getKpi());
            map.put("kpiWage",lzWage.getKpiWage());
            map.put("wage",lzWage.getWage());
            map.put("userId",lzWage.getUserId());
            for (SysUser sysUser : sysUserList) {
                if((lzWage.getUserId().toString()).equals(sysUser.getId())){
                    map.put("sysuName",sysUser.getName());
                    map.put("account",sysUser.getAccount());
                    break;
                }
            }
            for (UserSchool userSchool : userSchoolList) {
                if((lzWage.getUserId().toString()).equals(userSchool.getUserId())){
                    String schoolId = userSchool.getSchoolId();
                    for (School school : schoolList) {
                        if(schoolId.equals(school.getId()+"")){
                            map.put("schoolName",school.getSchoolName());
                            map.put("substName",school.getSubName());
                            break;
                        }
                    }
                }
            }
            list.add(map);
        }
        ServletOutputStream os = null;
        XSSFWorkbook workbook = ExcelDealUtils.getWorkBook(ExcelDealUtils.wageExcel("楼长楼长薪酬清单"), list);
        try {
            HttpServletResponse response = getHttpServletResponse();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            log.error("文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }
    }

    /**
     * 受理订单模板下载
     *
     * @return
     */
    @Permission(menuname = "楼长楼长薪酬管理信息导入模板", method = RequestMethod.POST, value = "downloadMoBan")
    public ResponseData downloadMoBan() {
        ArrayList<String> columns = new ArrayList<>();
        columns.add(SL_month);
        columns.add(SL_substName);
        columns.add(SL_schoolName);
        columns.add(SL_sysuName);
        columns.add(SL_rechargeNum);
        columns.add(SL_rechargeWage);
        columns.add(SL_kpi);
        columns.add(SL_kpiWage);
        columns.add(SL_wage);
        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet("楼长楼长薪酬管理信息");
            sheet.setColumnWidth(0, 3000);
            sheet.setColumnWidth(1, 3000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 3000);
            sheet.setColumnWidth(4, 3000);
            sheet.setColumnWidth(5, 3000);
            sheet.setColumnWidth(6, 3000);
            sheet.setColumnWidth(7, 3000);
            sheet.setColumnWidth(8, 3000);
            //下拉选项
            /*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;
    }

    /**
     * 受理订单
     *
     * @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文件，其他格式不支持！");
        }
        if ( (double)file.getSize()/1048576 > 100) {
            return ResponseData.error("文件过大，无法上传");
        }
        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_month)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("b").toString().equals(SL_substName)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("c").toString().equals(SL_schoolName)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("d").toString().equals(SL_sysuName)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("f").toString().equals(SL_rechargeNum)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("g").toString().equals(SL_rechargeWage)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("h").toString().equals(SL_kpi)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("i").toString().equals(SL_kpiWage)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("j").toString().equals(SL_wage)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        /*if (listMap.get(0).size() != 11) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }*/

        List<String> accountList = new LinkedList<>();
        List<String> userNameList = new LinkedList<>();
        List<Map<String,Object>> sysUserList = new LinkedList<>();
        //校验数据
        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) + "行的KPI月份不能为空");
                }
                if (StringUtils.isBlank(temp.get("a").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的KPI月份不能为空");
                }
                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 (!temp.containsKey("d")) {
                    return ResponseData.error("第" + (i + 1) + "行的kpi总值不能为空");
                }
                if (StringUtils.isBlank(temp.get("d").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的kpi总值不能为空");
                }
                if (!temp.containsKey("e")) {
                    return ResponseData.error("第" + (i + 1) + "行的kpi薪酬不能为空");
                }
                if (StringUtils.isBlank(temp.get("e").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的kpi薪酬不能为空");
                }
                if (!temp.containsKey("f")) {
                    return ResponseData.error("第" + (i + 1) + "行的总薪酬不能为空");
                }
                if (StringUtils.isBlank(temp.get("f").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的总薪酬不能为空");
                }
                /*if (!temp.containsKey("h")) {
                    return ResponseData.error("第" + (i + 1) + "行的县分不能为空");
                }
                if (StringUtils.isBlank(temp.get("h").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的县分不能为空");
                }
                if (!temp.containsKey("i")) {
                    return ResponseData.error("第" + (i + 1) + "行的学校不能为空");
                }
                if (StringUtils.isBlank(temp.get("i").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的学校不能为空");
                }*/
                if (!temp.containsKey("j")) {
                    return ResponseData.error("第" + (i + 1) + "行的姓名不能为空");
                }
                if (StringUtils.isBlank(temp.get("j").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的姓名不能为空");
                }
                /*if (!temp.containsKey("e")) {
                    return ResponseData.error("第" + (i + 1) + "行的银行账号不能为空");
                }
                if (StringUtils.isBlank(temp.get("e").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的银行账号不能为空");
                }*/
                if (temp.containsKey("j")) {
                    userNameList.add(temp.get("j").toString());
                }
            }
            Wrapper<SysUser> sysWrapper = new EntityWrapper<>();
            sysWrapper.setSqlSelect("id,account");
            if(accountList.size() != 0){
                sysWrapper.in("name",userNameList);
                sysUserList = sysUserMapper.selectMaps(sysWrapper);
            }
            for (Map<String, Object> map : listMap) {
                String name = map.get("j").toString();
                for (Map<String, Object> tmap : sysUserList) {
                    if(name.equals(tmap.get("name").toString())){
                        map.put("k",tmap.get("id").toString());
                        break;
                    }
                }

            }

            //检查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("b"));
            }
            if (accountSet.size() != size) {
                return ResponseData.error("文件中有重复的用户账号，请确认用户账号不能重复!");
            }*/
        } catch (Exception e) {
            log.error("楼长楼长薪酬管理信息导入异常", e.getMessage());
            return ResponseData.error("导入异常！");
        }

        int number = 0;
        for (int i = 1; i < listMap.size(); i++) {
            Map<String, Object> map = listMap.get(i);
            LzWage lzWage = new LzWage();
            String month = map.get("a").toString();
            lzWage.setMonth(month);
            Integer rechargeNum = Integer.valueOf(map.get("b").toString());
            lzWage.setRechargeNum(rechargeNum);
            double rechargeWage = Double.valueOf(map.get("c").toString());
            lzWage.setRechargeWage(rechargeWage);
            double kpi = Double.valueOf(map.get("d").toString());
            lzWage.setKpi(kpi);
            double kpiWage = Double.valueOf(map.get("e").toString());
            lzWage.setKpiWage(kpiWage);
            double wage = Integer.valueOf(map.get("f").toString());
            lzWage.setWage(wage);
            Integer userId = Integer.valueOf(map.get("k").toString());
            lzWage.setUserId(userId);

            Integer integer = lzWageMpapper.insert(lzWage);
            if (integer == 1) {
                number++;
            } else {
                error.append("第" + (i + 1) + "行的更新失败,");
            }
        }
        error.append("成功更新" + number + "条数据！");
        return ResponseData.success(error.toString(), "导入完成");
    }
}
