package com.winsun.controller;

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.HhrUser;
import com.winsun.bean.SalesOrder;
import com.winsun.bean.School;
import com.winsun.bean.SysUser;
import com.winsun.mapper.HhrUserMapper;
import com.winsun.mapper.SalesOrderMapper;
import com.winsun.mapper.SchoolMapper;
import com.winsun.mapper.SysUserMapper;
import com.winsun.utils.ExcelDealUtils;
import com.winsun.utils.ImportExcel;
import com.winsun.utils.MapUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.text.SimpleDateFormat;
import java.util.*;

/**
 * 放号充值达标数据
 * @Author: pxl
 * @create 2020/5/18 18:04
 */
@Slf4j
@RestController
@RequestMapping("/salesOrder")
public class SalesOrderController extends BaseController{

    /**
     * 导入
     */
    private static final int HEADER_NUM = -1;
    private static final int SHEET_INDEX = 0;

    /**
     * 放号上传模板列名
     */
    private static String FH_SORT = "序号";
    private static String FH_ORDER_PHONE = "放号号码（必填）";
    private static String FH_NAME = "销售员（必填，填合伙人注册姓名）";
    private static String FH_ACCOUNT = "销售员账号（必填，系统注册手机号，重要）";
    private static String FH_SALES_SCHOOL = "销售学校";
    private static String FH_SALES_SUBST = "销售县分";

    /**
     * 充值审核模板列名
     */
    private static String CZ_ORDER_PHONE = "电信激活号码";
    private static String CZ_ACTIVATE_TIME = "激活时间";
    private static String CZ_NETWORK_CODE = "所属网点编码";
    private static String CZ_NETWORK_NAME = "所属网点";
    private static String CZ_AMOUNT = "充值金额";

	private static SalesOrderMapper salesOrderMapper;
	private static SysUserMapper sysUserMapper;
	private static SchoolMapper schoolMapper;
	private static HhrUserMapper hhrUserMapper;

	private List<Map<String, Object>> exportList = new ArrayList<Map<String,Object>>();
	private SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	@Autowired
	public SalesOrderController(SalesOrderMapper salesOrderMapper,SysUserMapper sysUserMapper,
								SchoolMapper schoolMapper,HhrUserMapper hhrUserMapper) {
		SalesOrderController.salesOrderMapper = salesOrderMapper;
		SalesOrderController.sysUserMapper = sysUserMapper;
		SalesOrderController.schoolMapper = schoolMapper;
		SalesOrderController.hhrUserMapper = hhrUserMapper;
	}

	@Permission(menuname = "获取放号充值达标数据列表", value ="/list", method = RequestMethod.POST)
	public ResponseData<Page<Map<String,Object>>> getList(
			@RequestParam("name") String name,@RequestParam("account") String account,
			@RequestParam("salesSubst") String salesSubst,@RequestParam("salesSchool") String salesSchool,
			@RequestParam("activateTime") String activateTime,@RequestParam("position") String position,
			@RequestParam("leader") String leader, @RequestParam("president") String president,
			@RequestParam(name = "pageNo", required = false) int pageNo,
			@RequestParam(name = "pageSize", required = false) int pageSize){
		Page<Map<String,Object>> page = new Page<>(pageNo, pageSize);
		List<Map<String,Object>> dataList = new LinkedList<>();

		List<String> userIds = new ArrayList<>();
		List<String> parentId = new ArrayList<>();
		List<String> parentIds = new ArrayList<>();
		if(StringUtils.isNotBlank(position)) {
			Wrapper<HhrUser> hhrUserWrapper = new EntityWrapper<>();
			hhrUserWrapper.eq("position", position.trim());
			List<HhrUser> hhrUserList = hhrUserMapper.selectList(hhrUserWrapper);
			if (hhrUserList.size() == 0) {
				page.setTotal(0);
				return ResponseData.success(page, "没有数据！");
			}
			for (HhrUser hhrUser : hhrUserList) {
				userIds.add(hhrUser.getId());
				if (StringUtils.isNotBlank(hhrUser.getParentId())) {
					parentId.add(hhrUser.getParentId());
				}
				if (StringUtils.isNotBlank(hhrUser.getParentIds())) {
					String[] parentid = hhrUser.getParentIds().split("\\,");
					if (parentid.length == 2) {
						parentIds.add(parentid[1]);
					}
				}
			}
		}
		if (StringUtils.isNotBlank(leader)) {
			Wrapper<SysUser> sysUserWrapper = new EntityWrapper<>();
			if (parentId.size() > 0) {
				sysUserWrapper.in("id", parentId);
			}
			sysUserWrapper.eq("name", leader.trim());
			List<SysUser> sysUserList = sysUserMapper.selectList(sysUserWrapper);
			if (sysUserList.size() == 0) {
				page.setTotal(0);
				return ResponseData.success(page, "没有数据！");
			}
			Wrapper<HhrUser> hhrUserWrapper = new EntityWrapper<>();
			hhrUserWrapper.eq("parent_id", sysUserList.get(0).getId());
			List<HhrUser> hhrUserList = hhrUserMapper.selectList(hhrUserWrapper);
			if (hhrUserList.size() == 0) {
				page.setTotal(0);
				return ResponseData.success(page, "没有数据！");
			}
			userIds.removeAll(userIds);
			for (HhrUser hhrUser : hhrUserList) {
				userIds.add(hhrUser.getId());
			}
		}
		if (StringUtils.isNotBlank(president)) {
			Wrapper<SysUser> sysUserWrapper = new EntityWrapper<>();
			if (parentIds.size() > 0) {
				sysUserWrapper.in("id", parentIds);
			}
			sysUserWrapper.eq("name", president.trim());
			List<SysUser> sysUserList = sysUserMapper.selectList(sysUserWrapper);
			if (sysUserList.size() == 0) {
				page.setTotal(0);
				return ResponseData.success(page, "没有数据！");
			}
			Wrapper<HhrUser> hhrUserWrapper = new EntityWrapper<>();
			hhrUserWrapper.like("parent_ids", sysUserList.get(0).getId(), SqlLike.LEFT);
			List<HhrUser> hhrUserList = hhrUserMapper.selectList(hhrUserWrapper);
			if (hhrUserList.size() == 0) {
				page.setTotal(0);
				return ResponseData.success(page, "没有数据！");
			}
			userIds.removeAll(userIds);
			for (HhrUser hhrUser : hhrUserList) {
				userIds.add(hhrUser.getId());
			}
		}
		Wrapper<SalesOrder> wrapper = new EntityWrapper<>();
		if(userIds.size()>0){
			wrapper.in("user_id",userIds);
			userIds.removeAll(userIds);
		}
		wrapper.like(StringUtils.isNotBlank(name), "name", name.trim(), SqlLike.DEFAULT);
		wrapper.eq(StringUtils.isNotBlank(account), "account", account.trim());
		wrapper.eq(StringUtils.isNotBlank(salesSubst), "sales_subst", salesSubst.trim());
		wrapper.eq(StringUtils.isNotBlank(salesSchool), "sales_school", salesSchool.trim());
		wrapper.ge("recently_amount", 50);
		wrapper.eq( "del_flag", 0);
		if(StringUtils.isNotBlank(activateTime)){
			String[] dates = activateTime.split("\\,");
			if(!activateTime.equals(",")){
				wrapper.between("activate_time",dates[0],dates[1]);
			}
		}
		wrapper.orderBy("activate_time",false);
		List<SalesOrder> salesOrderList = salesOrderMapper.selectList(wrapper);
		if(salesOrderList.size()==0){
			page.setTotal(0);
			return ResponseData.success(page, "没有数据！");
		}
		page.setTotal(salesOrderList.size());
		if(salesOrderList.size() >= pageSize) {
			Integer endpageSize = (pageNo-1) == salesOrderList.size()/pageSize?(pageNo-1)*pageSize+salesOrderList.size()%pageSize:pageNo*pageSize;
			salesOrderList = salesOrderList.subList((pageNo-1)*pageSize, endpageSize);
		}
		for (SalesOrder salesOrder : salesOrderList) {
			Map<String,Object> objectMap = new HashMap<>();
			objectMap.put("id",salesOrder.getId());
			objectMap.put("orderPhone",salesOrder.getOrderPhone());
			objectMap.put("name",salesOrder.getName());
			objectMap.put("account",salesOrder.getAccount());
			objectMap.put("salesSubst",salesOrder.getSalesSubst());
			objectMap.put("salesSchool",salesOrder.getSalesSchool());
			objectMap.put("createTime",salesOrder.getCreateTime());
			objectMap.put("activateTime",salesOrder.getActivateTime());
			HhrUser hhrUser = hhrUserMapper.selectById(salesOrder.getUserId());
			if(hhrUser != null){
				if(salesOrder.getUserId().equals(hhrUser.getId())){
					objectMap.put("position",hhrUser.getPosition());
					if(StringUtils.isNotBlank(hhrUser.getParentId())){
						SysUser sysUser = sysUserMapper.selectById(hhrUser.getParentId());
						if(sysUser != null){
							objectMap.put("leader",sysUser.getName());
						}
					}
					if(StringUtils.isNotBlank(hhrUser.getParentIds())){
						String[] parentid = hhrUser.getParentIds().split("\\,");
						if(parentid.length >= 2){
							SysUser sysUser = sysUserMapper.selectById(parentid[1]);
							if(sysUser != null){
								objectMap.put("president",sysUser.getName());
							}
						}
					}
				}
			}
			dataList.add(objectMap);
		}
		page.setRecords(dataList);
		return ResponseData.success(page, "查询完毕！");
	}

    /**
     * 导出订单清单
     * @param name
     * @param account
     * @param salesSubst
     * @param salesSchool
     * @param activateTime
     * @param leader
     * @param president
     * @param position
     * @throws IOException
     */
    @Permission(menuname = "导出放号充值达标数据清单", value = "exportList", method = RequestMethod.POST)
    public void downloadHhrOrder(
            @RequestParam(name = "name", required = false) String name,
			@RequestParam(name = "account", required = false) String account,
            @RequestParam(name = "salesSubst", required = false) String salesSubst,
			@RequestParam(name = "salesSchool", required = false) String salesSchool,
            @RequestParam(name = "activateTime", required = false) String activateTime,
			@RequestParam(name = "leader", required = false) String leader,
            @RequestParam(name = "president", required = false) String president,
			@RequestParam(name = "position", required = false) String position) throws IOException {
		List<Map<String,Object>> dataList = new LinkedList<>();

		List<String> userIds = new ArrayList<>();
		List<String> parentId = new ArrayList<>();
		List<String> parentIds = new ArrayList<>();
		if(StringUtils.isNotBlank(position)) {
			Wrapper<HhrUser> hhrUserWrapper = new EntityWrapper<>();
			hhrUserWrapper.eq("position", position.trim());
			List<HhrUser> hhrUserList = hhrUserMapper.selectList(hhrUserWrapper);
			if (hhrUserList.size() == 0) {
				log.info("没有数据");
			}
			for (HhrUser hhrUser : hhrUserList) {
				userIds.add(hhrUser.getId());
				if (StringUtils.isNotBlank(hhrUser.getParentId())) {
					parentId.add(hhrUser.getParentId());
				}
				if (StringUtils.isNotBlank(hhrUser.getParentIds())) {
					String[] parentid = hhrUser.getParentIds().split("\\,");
					if (parentid.length == 2) {
						parentIds.add(parentid[1]);
						parentId.add(parentid[1]);
					}
				}
			}
		}
		if (StringUtils.isNotBlank(leader)) {
			Wrapper<SysUser> sysUserWrapper = new EntityWrapper<>();
			if (parentId.size() > 0) {
				sysUserWrapper.in("id", parentId);
			}
			sysUserWrapper.eq("name", leader.trim());
			List<SysUser> sysUserList = sysUserMapper.selectList(sysUserWrapper);
			if (sysUserList.size() == 0) {
				log.info("没有数据");
			}
			for (SysUser sysUser : sysUserList) {
				parentId.add(sysUser.getId());
			}
			Wrapper<HhrUser> hhrUserWrapper1 = new EntityWrapper<>();
			hhrUserWrapper1.eq("parent_id", sysUserList.get(0).getId());
			List<HhrUser> hhrUserList1 = hhrUserMapper.selectList(hhrUserWrapper1);
			if (hhrUserList1.size() == 0) {
				log.info("没有数据");
			}
			userIds.removeAll(userIds);
			for (HhrUser hhrUser : hhrUserList1) {
				userIds.add(hhrUser.getId());
			}
		}
		if (StringUtils.isNotBlank(president)) {
			Wrapper<SysUser> sysUserWrapper = new EntityWrapper<>();
			if (parentIds.size() > 0) {
				sysUserWrapper.in("id", parentIds);
			}
			sysUserWrapper.eq("name", president.trim());
			List<SysUser> sysUserList = sysUserMapper.selectList(sysUserWrapper);
			if (sysUserList.size() == 0) {
				log.info("没有数据");
			}
			for (SysUser sysUser : sysUserList) {
				parentId.add(sysUser.getId());
			}
			Wrapper<HhrUser> hhrUserWrapper1 = new EntityWrapper<>();
			hhrUserWrapper1.like("parent_ids", sysUserList.get(0).getId(), SqlLike.LEFT);
			List<HhrUser> hhrUserList1 = hhrUserMapper.selectList(hhrUserWrapper1);
			if (hhrUserList1.size() == 0) {
				log.info("没有数据");
			}
			userIds.removeAll(userIds);
			for (HhrUser hhrUser : hhrUserList1) {
				userIds.add(hhrUser.getId());
			}
		}
		Wrapper<SalesOrder> wrapper = new EntityWrapper<>();
		if(userIds.size()>0){
			wrapper.in("user_id",userIds);
			userIds.removeAll(userIds);
		}
		wrapper.like(StringUtils.isNotBlank(name), "name", name.trim(), SqlLike.DEFAULT);
		wrapper.eq(StringUtils.isNotBlank(account), "account", account.trim());
		wrapper.eq(StringUtils.isNotBlank(salesSubst), "sales_subst", salesSubst.trim());
		wrapper.eq(StringUtils.isNotBlank(salesSchool), "sales_school", salesSchool.trim());
		wrapper.ge("recently_amount", 50);
		wrapper.eq( "del_flag", 0);
		if(StringUtils.isNotBlank(activateTime)){
			String[] dates = activateTime.split("\\,");
			if(!activateTime.equals(",")){
				wrapper.between("activate_time",dates[0],dates[1]);
			}
		}
		wrapper.orderBy("activate_time",false);
		List<SalesOrder> salesOrderList = salesOrderMapper.selectList(wrapper);
		if(salesOrderList.size()==0){
			log.info("没有数据");
		}
		for (SalesOrder salesOrder : salesOrderList) {
			userIds.add(salesOrder.getUserId());
		}
		Wrapper<HhrUser> hhrUserWrapper1 = new EntityWrapper<>();
		hhrUserWrapper1.in("id",userIds);
		userIds.removeAll(userIds);
		List<HhrUser> hhrUsers = hhrUserMapper.selectList(hhrUserWrapper1);

		Wrapper<SysUser> sysUserWrapper = new EntityWrapper<>();
		if(parentId.size()>0){
			List<String> list= removeDuplicate(parentId);
			sysUserWrapper.in("id",list);
		}
		List<SysUser> sysUserList = sysUserMapper.selectList(sysUserWrapper);
		for (SalesOrder salesOrder : salesOrderList) {
			Map<String,Object> objectMap = new HashMap<>();
			objectMap.put("orderPhone",salesOrder.getOrderPhone());
			objectMap.put("name",salesOrder.getName());
			objectMap.put("account",salesOrder.getAccount());
			objectMap.put("salesSubst",salesOrder.getSalesSubst());
			objectMap.put("salesSchool",salesOrder.getSalesSchool());
			objectMap.put("createTime",salesOrder.getCreateTime());
			objectMap.put("activateTime",salesOrder.getActivateTime());
			for (HhrUser hhrUser : hhrUsers) {
				if(salesOrder.getUserId().equals(hhrUser.getId())){
					objectMap.put("position",hhrUser.getPosition());
					if(StringUtils.isNotBlank(hhrUser.getParentId())){
						for (SysUser sysUser : sysUserList) {
							if(hhrUser.getParentId().equals(sysUser.getId())){
								objectMap.put("leader",sysUser.getName());
								break;
							}
							objectMap.put("leader","");
						}
					}
					objectMap.put("leader","");
					if(StringUtils.isNotBlank(hhrUser.getParentIds())){
						String[] parentid = hhrUser.getParentIds().split("\\,");
						if(parentid.length == 2){
							for (SysUser sysUser : sysUserList) {
								if(parentid[1].equals(sysUser.getId())){
									objectMap.put("president",sysUser.getName());
									break;
								}
								objectMap.put("president","");
							}
						}
						objectMap.put("president","");
					}
					objectMap.put("president","");
					break;
				}else{
					objectMap.put("position","");
					objectMap.put("president","");
					objectMap.put("leader","");
				}
			}
			dataList.add(objectMap);
		}
        ServletOutputStream os = null;
		List<Integer> columnWidths = new ArrayList<>();
		columnWidths.add(3500);
		columnWidths.add(5000);
		columnWidths.add(3000);
		columnWidths.add(3000);
		columnWidths.add(5000);
		columnWidths.add(2500);
		columnWidths.add(3000);
		columnWidths.add(3000);
		columnWidths.add(5000);
		columnWidths.add(5000);
        XSSFWorkbook workbook = ExcelDealUtils.getWorkBook2(ExcelDealUtils.salesOrderExcel("放号充值达标清单"), dataList,columnWidths);
        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);
        }
    }

	@Permission(menuname = "删除销售订单", value ="/deleteOrder", method = RequestMethod.POST)
	public ResponseData<String> deleteOrder(@RequestParam("id") String id){

		SalesOrder salesOrder = salesOrderMapper.selectById(id);
		String status = salesOrder.getStatus().toString();
		String orderPhone = salesOrder.getOrderPhone();
		String salesSchool = salesOrder.getSalesSchool();
		salesOrder.setDelFlag(1);

		int num = salesOrderMapper.updateById(salesOrder);
		if(num > 0) {
			if("0".equals(status)) {
				// 重复订单删除后，重置对其它重复订单的影响
				List<Map<String, Object>> phoneSchoolList = salesOrderMapper.getPhoneSchoolByPhone(orderPhone);
				Map<String, Object> phoneSchoolMap = phoneSchoolList.get(0);
				String phoneSchool = (String)phoneSchoolMap.get("school");
				String remark = "";

				if(phoneSchool.indexOf(salesSchool+",") > -1) {
					phoneSchool = phoneSchool.replace(salesSchool+",", "");
				}else {
					phoneSchool = phoneSchool.replace(","+salesSchool, "");
				}

				if(phoneSchool.split(",").length > 1) {
					remark = "放号号码"+ phoneSchool +"学校有重复记录，请后期进行核对";
				}else {
					status = "1";
				}

				// 更新号码所属学校
				phoneSchoolMap.put("phoneSchool", phoneSchool);
				phoneSchoolMap.put("orderPhone", orderPhone);
				List<Map<String, Object>> updateSchoolList = new ArrayList<Map<String,Object>>();
				updateSchoolList.add(phoneSchoolMap);
				updatePhoneSchool(updateSchoolList);

				salesOrderMapper.updateRepealOrder(status,remark,orderPhone);
			}else {
				salesOrderMapper.deletePhoneSchool(orderPhone);
			}

			return ResponseData.success("删除成功");
		}


		return ResponseData.error("删除失败");
	}

    /**
     * 受理订单
     *
     * @param file 上传的数据文件
     * @return
     */
    @SuppressWarnings("unchecked")
	@Permission(menuname = "上传订单数据", value = "importOrder", method = RequestMethod.POST)
    public ResponseData<String> importOrder(@RequestParam(name = "file") MultipartFile file) {
        StringBuffer info = 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("b").toString().equals(FH_ORDER_PHONE)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("c").toString().equals(FH_NAME)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("d").toString().equals(FH_ACCOUNT)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("e").toString().equals(FH_SALES_SCHOOL)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("f").toString().equals(FH_SALES_SUBST)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (listMap.get(0).size() != 6) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        //校验数据
        try {
            //校验数据
            for (int i = 1; i < listMap.size(); i++) {
                Map<String, Object> temp = listMap.get(i);

                if (!temp.containsKey("b")) {
                    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("b"));
            }
            if (accountSet.size() != size) {
                return ResponseData.error("上传清单包含重复号码，请删除重复号码再上传!");
            }
        } catch (Exception e) {
            log.error("放号清单导入异常" + e.getMessage(), e);
            return ResponseData.error("导入异常！");
        }

        // 去掉首行标题
        listMap.remove(0);
        Map<String, Object> resultMap = dealUploadData(listMap);
        int num = (int)resultMap.get("num");
        exportList = (List<Map<String, Object>>)resultMap.get("infoList");
        info.append("导入" + num+ "条数据！");
        if(exportList.size() > 0) {
        	info.append("返回导入结果清单！");
        }

        return ResponseData.success(info.toString(), "导入完成");
    }

    public Map<String,Object> dealUploadData(List<Map<String,Object>> list){
    	Map<String, Object> resultMap = new HashMap<String, Object>();

    	// 处理放号清单号码的销售学校清单，用于重复判断
    	Map<String, Object> phoneSchoolMap = MapUtil.listToMap(salesOrderMapper.getPhoneSchool(), "order_phone", "school");
    	// 处理用户清单
    	Wrapper<SysUser> wrapperUser = new EntityWrapper<SysUser>();
    	wrapperUser.setSqlSelect(" account,id ");
    	wrapperUser.eq("status", 1);
    	List<Map<String, Object>> userList = sysUserMapper.selectMaps(wrapperUser);
    	Map<String, Object> userMaps = MapUtil.listToMap(userList, "account", "id");
    	List<Map<String, Object>> insertList = new ArrayList<Map<String,Object>>();
    	List<Map<String, Object>> phoneSchoolList = new ArrayList<Map<String,Object>>();
    	List<Map<String, Object>> updateList = new ArrayList<Map<String,Object>>();
    	List<Map<String, Object>> infoList = new ArrayList<Map<String,Object>>();

    	for(Map<String, Object> map : list) {
    		String sortNo = (String)map.get("a");
    		String orderPhone = (String)map.get("b");
    		String name = (String)map.get("c");
    		String account = (String)map.get("d");
    		String salesSchool = (String)map.get("e");
    		String salesSubst = (String)map.get("f");

			Map<String, Object> theMap = new HashMap<String, Object>();
			theMap.put("sortNo", sortNo);
			theMap.put("orderPhone", orderPhone);
			theMap.put("name", name);
			theMap.put("account", account);
			theMap.put("salesSchool", salesSchool);
			theMap.put("salesSubst", salesSubst);


    		if(null == userMaps.get(account)) {
    			theMap.put("desc", "账号不存在");
    			infoList.add(theMap);
    			continue;
    		}else {
    			theMap.put("userId", userMaps.get(account));
    		}

    		if(null != phoneSchoolMap.get(orderPhone)) {
    			String phoneSchool = (String)phoneSchoolMap.get(orderPhone);

    			if(phoneSchool.indexOf(salesSchool) > -1) {
    				theMap.put("desc", "放号号码该销售学校有重复记录，请去重后重新上传");
    				infoList.add(theMap);
    			}else {
    				theMap.put("desc", "放号号码"+ phoneSchool +","+salesSchool +"学校有重复记录，请后期进行核对;");
    				theMap.put("phoneSchool", phoneSchool +","+salesSchool);

    				updateList.add(theMap);
    				insertList.add(theMap);
    				phoneSchoolList.add(theMap);
    				infoList.add(theMap);
    			}
    		}else {
    			theMap.put("phoneSchool", salesSchool);
    			insertList.add(theMap);
    			phoneSchoolList.add(theMap);
    		}

    	}

    	int num = addSalesOrder(insertList);
    	updatePhoneSchool(phoneSchoolList);
    	updateRepealOrder(updateList);

    	resultMap.put("num", num);
    	resultMap.put("infoList", infoList);

    	return resultMap;
    }

    // 插入销售订单表
    public int addSalesOrder(List<Map<String, Object>> list) {
    	int num = 0;

    	for(Map<String, Object> map : list) {
    		SalesOrder order = new SalesOrder();
    		order.setOrderPhone((String)map.get("orderPhone"));
    		order.setUserId(Integer.toString((int)map.get("userId")));
    		order.setCreateTime(new Date());
    		order.setSalesSubst((String)map.get("salesSubst"));
    		order.setSalesSchool((String)map.get("salesSchool"));
    		order.setOrderType(1);
    		order.setName((String)map.get("name"));
    		order.setAccount((String)map.get("account"));

    		num += salesOrderMapper.insert(order);
    	}

    	return num;
    }

    // 更新号码销售学校表
    public void updatePhoneSchool(List<Map<String, Object>> list) {
    	for(Map<String, Object> map : list) {
    		salesOrderMapper.updatePhoneSchool((String)map.get("orderPhone"), (String)map.get("phoneSchool"));
    	}
    }

    // 更新重复订单的备注说明
    public void updateRepealOrder(List<Map<String, Object>> list) {
    	for(Map<String, Object> map : list) {
    		String orderPhone = (String)map.get("orderPhone");
    		//  0 重复上传
    		String status = "0";
    		String phoneSchool = (String)map.get("phoneSchool");
    		String remark = "放号号码在"+ phoneSchool + "重复上传";

    		salesOrderMapper.updateRepealOrder(status,remark,orderPhone);
    	}
    }

    /**
     * 充值审核
     *
     * @param file 上传的数据文件
     * @return
     */
    @Permission(menuname = "充值审核", value = "importRechargeData", method = RequestMethod.POST)
    public ResponseData<String> importRechargeData(@RequestParam(name = "file") MultipartFile file) {
        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(CZ_ORDER_PHONE)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("b").toString().equals(CZ_ACTIVATE_TIME)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("c").toString().equals(CZ_NETWORK_CODE)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("d").toString().equals(CZ_NETWORK_NAME)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("e").toString().equals(CZ_AMOUNT)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }

        if (listMap.get(0).size() != 5) {
            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("b").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的激活时间不能为空");
                }
                if (StringUtils.isBlank(temp.get("d").toString())) {
                    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) + "行的充值金额不能为空");
                }
            }

        } catch (Exception e) {
            log.error("充值审核导入异常" + e.getMessage(), e);
            return ResponseData.error("导入异常！");
        }

        // 去掉首行标题
        listMap.remove(0);
        int num = dealRechargeData(listMap);

        return ResponseData.success("审核完成，审核"+ num +"条订单！");
    }

    // 处理充值审核数据
    public int dealRechargeData(List<Map<String,Object>> list){

    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.MONTH, -7);
		String date = simpleDateFormat.format(calendar.getTime());
    	// 获取半年上传内的订单
    	Wrapper<SalesOrder> wrapperOrder = new EntityWrapper<SalesOrder>();
    	wrapperOrder.ge("create_time", date);
    	wrapperOrder.eq("order_type", 1);
    	wrapperOrder.eq("del_flag", 0);
    	wrapperOrder.eq("send_bonus", 0);
    	wrapperOrder.ne("status", "0");
    	List<Map<String, Object>> orderList = salesOrderMapper.selectMaps(wrapperOrder);
    	Map<String, Map<String,Object>> orderMaps = MapUtil.listToMap(orderList, "orderPhone");

    	// 获取学校清单比较网点
    	Wrapper<School> wrapperSchool = new EntityWrapper<School>();
    	wrapperSchool.setSqlSelect(" school_name as schoolName,network_name as networkName,network_code as networkCode ");
    	List<Map<String, Object>> schoolList = schoolMapper.selectMaps(wrapperSchool);
    	Map<String, Map<String,Object>> schoolMaps = MapUtil.listToMap(schoolList, "schoolName");

    	List<Map<String, Object>> updateList = new ArrayList<Map<String,Object>>();

    	for(Map<String, Object> map : list) {
    		String orderPhone = (String)map.get("a");
    		String activateTime = (String)map.get("b");
    		String networkCode = (String)map.get("c");
    		String networkName = (String)map.get("d");
    		double amount = Double.parseDouble((String)map.get("e"));
    		boolean isSkip = false;

    		if(null != orderMaps.get(orderPhone)) {
    			Map<String, Object> orderMap = (Map<String, Object>)orderMaps.get(orderPhone);
    			String status = "2";
				String remark = "激活号码所属学校与上报学校不一致";

    			if(null == networkName) {
    				isSkip = true;
    			}

    			Map<String, Object> schoolMap = (Map<String, Object>)schoolMaps.get(orderMap.get("salesSchool"));

    			if(null != schoolMap.get("networkCode")) {
	    			if(networkCode.equals(schoolMap.get("networkCode"))) {
	    				double bonus = 0;
	    				double allBonus = 0;
	    				double nonSendBonus = 0;

	    				if(amount >= 50 && amount < 100) {
	    					bonus = 20;
	    				}else if(amount >= 100) {
	    					bonus = 40;
	    				}

	    				if("广州美术学院（大学城）".equals(orderMap.get("sales_school")) && bonus >= 20){
							bonus = 10;
						}

						allBonus = bonus;
						nonSendBonus = bonus;
						if(bonus == 0) {
							status = "4";
							remark = "充值金额小于50元";
						}else {
							status = "3";
							remark = "";
						}

	    				orderMap.put("allBonus", allBonus);
	    				orderMap.put("nonSendBonus", nonSendBonus);
	    				orderMap.put("recentlyAmount", amount);
	    				orderMap.put("recentlyBonus", bonus);
	    			}
    			}else {
    				remark = "该学校的网点信息未添加";
    			}

   				orderMap.put("status", status);
				orderMap.put("remark", remark);
				orderMap.put("activateTime", activateTime);
				orderMap.put("networkCode", networkCode);
				orderMap.put("networkName", networkName);
				updateList.add(orderMap);
    		}
    	}

    	int num = updateAuditResult(updateList);

    	return num;
    }

    // 更新审核结果到销售订单
    public int updateAuditResult(List<Map<String, Object>> list) {
    	int num = 0;

    	try {
	    	for(Map<String, Object> map : list) {
	    		SalesOrder order = new SalesOrder();
	    		order.setId((int)map.get("id"));
	    		order.setStatus(Integer.valueOf(map.get("status").toString()));
	    		order.setRemark((String)map.get("remark"));
	    		order.setAuditTime(new Date());

	    		if(!"1".equals((String)map.get("status"))) {
	    			order.setAllBonus((double)map.get("allBonus"));
	    			order.setNonSendBonus((double)map.get("nonSendBonus"));
	    			order.setRecentlyAmount((double)map.get("recentlyAmount"));
	    			order.setRecentlyBonus((double)map.get("nonSendBonus"));
	    			order.setActivateTime(sFormat.parse((String)map.get("activateTime")));
	    			order.setNetworkCode((String)map.get("networkCode"));
	    			order.setNetworkName((String)map.get("networkName"));
	    		}

	    		num += salesOrderMapper.updateById(order);
	    	}
    	}catch (Exception e) {
    		log.error("更新审核结果到销售订单异常" + e.getMessage(), e);
		}

    	return num;
    }

    @Permission(menuname = "放号上传模板", method = RequestMethod.POST, value = "downloadTemplateFH")
    public ResponseData<String> downloadTemplateFH() {
        ArrayList<String> columns = new ArrayList<>();
        columns.add(FH_SORT);
        columns.add(FH_ORDER_PHONE);
        columns.add(FH_NAME);
        columns.add(FH_ACCOUNT);
        columns.add(FH_SALES_SCHOOL);
        columns.add(FH_SALES_SUBST);
        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet("放号上传清单");
            sheet.setColumnWidth(0, 3000);
            sheet.setColumnWidth(1, 5000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 4000);
            sheet.setColumnWidth(4, 4000);
            sheet.setColumnWidth(5, 4000);

            //添加表头
            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 = "downloadTemplateCZ")
    public ResponseData<String> downloadTemplateCZ() {
        ArrayList<String> columns = new ArrayList<>();
        columns.add(CZ_ORDER_PHONE);
        columns.add(CZ_ACTIVATE_TIME);
        columns.add(CZ_NETWORK_CODE);
        columns.add(CZ_NETWORK_NAME);
        columns.add(CZ_AMOUNT);
        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);

            //添加表头
            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 = "导出导入结果", value = "exportResult", method = RequestMethod.POST)
    public void exportResult(
    		) throws IOException {

        List<String> tops = new ArrayList<>();
        tops.add(FH_SORT);
        tops.add(FH_ORDER_PHONE);
        tops.add(FH_NAME);
        tops.add(FH_ACCOUNT);
        tops.add(FH_SALES_SCHOOL);
        tops.add(FH_SALES_SUBST);
        tops.add("导入结果");

        List<String> columns = new ArrayList<>();
        columns.add("sortNo");
        columns.add("orderPhone");
        columns.add("name");
        columns.add("account");
        columns.add("salesSchool");
        columns.add("salesSubst");
        columns.add("desc");
        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet("导入结果返回清单");
            //添加表头
            final XSSFRow row = sheet.createRow(0);
            //设置列宽度
            sheet.setColumnWidth(1, 3000);

            //第一行字段名称
            tops.forEach(column -> {
                XSSFCell cell = row.createCell(Math.max(row.getLastCellNum(), 0));
                cell.setCellValue(column);
            });
            //数据
            int rowIndex = row.getRowNum();
            for (Map<String, Object> maps: exportList) {
                final XSSFRow rowdata = sheet.createRow(++rowIndex);
                for (String column : columns) {
                    XSSFCell cell = rowdata.createCell(Math.max(rowdata.getLastCellNum(), 0));
                    if (maps.get(column) != null) {
                        cell.setCellValue(maps.get(column).toString());
                    }
                }
            }

            HttpServletResponse response = getHttpServletResponse();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();

            exportList = null;
        } catch (Exception e) {
            log.error("文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }

    }

    public static String timeStamp2Date(String seconds,String format) {
        if(seconds == null || seconds.isEmpty() || seconds.equals("null")){
            return "";
        }
        if(format == null || format.isEmpty()){
            format = "yyyy-MM-dd HH:mm:ss";
        }
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.format(new Date(Long.valueOf(seconds+"000")));
    }

	/**
	 * List集合去除重复数据
	 * @param list
	 * @return
	 */
	public static List removeDuplicate(List list) {
		HashSet h = new HashSet(list);
		list.clear();
		list.addAll(h);
		return list;
	}
}
