package com.winsun.controller;

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.SalesList;
import com.winsun.bean.School;
import com.winsun.bean.SysUser;
import com.winsun.constant.OrderType;
import com.winsun.mapper.SalesListMapper;
import com.winsun.mapper.SchoolMapper;
import com.winsun.mapper.SysUserMapper;
import com.winsun.utils.ImportExcel;
import com.winsun.utils.MapUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
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.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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * 销售订单
 * @author Warden
 *
 */
@Slf4j
@RestController
@RequestMapping("/salesList")
public class SalesListController extends BaseController{
	
    /**
     * 导入
     */
    private static final int HEADER_NUM = -1;
    private static final int SHEET_INDEX = 0;

    @Autowired
    private RedisTemplate redisTemplate;

    /**
     * 放号上传模板列名
     */
    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 FH_ORDER_TYPE = "订单类型";
    
    /**
     * 充值审核模板列名
     */
    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 String PL_SALES_SUBST = "销售县分";
    private static String PL_SALES_SCHOOL = "销售学校";
    private static String PL_ORDER_PHONE = "电信激活号码";
    private static String PL_NAME = "销售员";
    private static String PL_ACCOUNT = "销售账号";
    private static String PL_CREATE_TIME = "上传时间";
    private static String PL_ORDER_TYPE = "订单类型";
    private static String PL_LOCK_STATE = "是否锁定";
    private static String PL_AUDIT_TIME = "系统匹配时间";
    private static String PL_NETWORK_CODE = "系统所属网点";
    private static String PL_NETWORK_NAME = "网点名称";
    private static String PL_ACTIVATE_TIME = "系统激活时间";
    private static String PL_RECENTLY_AMOUNT = "最近充值金额";
    private static String PL_RECENTLY_BONUS = "最近充值激励";
    private static String PL_ALL_BONUS = "应发总激励";
	private static String PL_SEND_BONUS = "已发激励";
	private static String PL_NONSEND_BONUS = "未发激励";

	private static String SD_ORDER_NUMBER = "电信激活号码";
	private static String SD_LOCK_STATE = "是否锁定";

	private static SalesListMapper salesListMapper;
	private static SysUserMapper sysUserMapper;
	private static SchoolMapper schoolMapper;

	private static ThreadLocal<List<Map<String, Object>>> exportList = new ThreadLocal<>();

	@Autowired
	public SalesListController(SalesListMapper salesListMapper,SysUserMapper sysUserMapper,SchoolMapper schoolMapper) {
		SalesListController.salesListMapper = salesListMapper;
		SalesListController.sysUserMapper = sysUserMapper;
		SalesListController.schoolMapper = schoolMapper;
		exportList.set(new ArrayList<>());
	}
	
	@Permission(menuname = "获取销售订单列表", value ="/list", method = RequestMethod.POST)
	public ResponseData<Page<SalesList>> getList(@RequestParam("orderPhone") String orderPhone,
			@RequestParam("name") String name,@RequestParam("account") String account,
			@RequestParam("status") String status,@RequestParam("orderType") String orderType,
			@RequestParam("createTimeStart") String createTimeStart,@RequestParam("createTimeEnd") String createTimeEnd,
			@RequestParam("activateTimeStart") String activateTimeStart,@RequestParam("activateTimeEnd") String activateTimeEnd,
			@RequestParam("salesSubst") String salesSubst,@RequestParam("salesSchool") String salesSchool,
		 	@RequestParam("preType") String preType,@RequestParam("lockState") String lockState,
			@RequestParam(name = "pageNo", required = false) int pageIndex,
			@RequestParam(name = "remark", required = false) String remark,
			@RequestParam(name = "pageSize", required = false) int pageSize){
		Page<SalesList> page = new Page<>(pageIndex, pageSize);
		Wrapper<SalesList> wrapperOrder = new EntityWrapper<SalesList>();
		wrapperOrder.eq(StringUtils.isNotBlank(orderPhone), "order_phone", orderPhone);
		wrapperOrder.eq(StringUtils.isNotBlank(orderType), "order_type", orderType);
		wrapperOrder.eq(StringUtils.isNotBlank(status), "status", status);
		wrapperOrder.eq(StringUtils.isNotBlank(name), "name", name);
		wrapperOrder.eq(StringUtils.isNotBlank(account), "account", account);
		wrapperOrder.eq(StringUtils.isNotBlank(preType), "pre_type", preType);
		wrapperOrder.eq(StringUtils.isNotBlank(salesSubst), "sales_subst", salesSubst);
		wrapperOrder.eq(StringUtils.isNotBlank(salesSchool), "sales_school", salesSchool);
		wrapperOrder.gt(StringUtils.isNotBlank(createTimeStart), "create_time", createTimeStart);
		wrapperOrder.lt(StringUtils.isNotBlank(createTimeEnd), "create_time", createTimeEnd);
		wrapperOrder.gt(StringUtils.isNotBlank(activateTimeStart), "activate_time", activateTimeStart);
		wrapperOrder.lt(StringUtils.isNotBlank(activateTimeEnd), "activate_time", activateTimeEnd);
		wrapperOrder.eq(StringUtils.isNotBlank(lockState), "lock_state", lockState);
		wrapperOrder.eq("del_flag", 0);
		wrapperOrder.like(StringUtils.isNotBlank(remark), "remark", remark);
		wrapperOrder.orderBy("create_time", false);
		List<SalesList> orderList = salesListMapper.selectPage(page, wrapperOrder);
		
		page.setRecords(orderList);
		return ResponseData.success(page, "查询完毕！");
	}

	@Permission(menuname = "自定义备注", value = "/customRemark", method = RequestMethod.POST)
	public ResponseData<String> customRemark(@RequestParam("id") Integer id, @RequestParam("customRemark") String customRemark ) {
		SalesList salesList = new SalesList();
		salesList.setId(id);
		salesList.setCustomRemark(customRemark);
		Integer integer = salesListMapper.updateById(salesList);

		if (integer > -1) {
			return ResponseData.success("更新成功");
		}else{
			return ResponseData.error("更新失败！");
		}
	}
	
	@Permission(menuname = "删除销售订单", value ="/deleteOrder", method = RequestMethod.POST)
	public ResponseData<String> deleteOrder(@RequestParam("id") String id){

		SalesList salesOrder = salesListMapper.selectById(id);
		String status = salesOrder.getStatus();
		String orderPhone = salesOrder.getOrderPhone();
		String salesSchool = salesOrder.getSalesSchool();
		salesOrder.setDelFlag("1");
		
		int num = salesListMapper.updateById(salesOrder);
		if(num > 0) {
			if("0".equals(status)) {
				// 重复订单删除后，重置对其它重复订单的影响
				List<Map<String, Object>> phoneSchoolList = salesListMapper.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);
				
				salesListMapper.updateRepealOrder(status,remark,orderPhone);
			}else {
				salesListMapper.deletePhoneSchool(orderPhone);
			}
			
			return ResponseData.success("删除成功");
		}
		
		
		return ResponseData.error("删除失败");
	}
	
	@Permission(menuname = "导出订单清单", value = "exportList", method = RequestMethod.POST)
    public void downloadHhrOrder(HttpServletResponse response, @RequestParam("orderPhone") String orderPhone,
			@RequestParam("name") String name,@RequestParam("account") String account,
			@RequestParam("status") String status,@RequestParam("orderType") String orderType,
			@RequestParam("createTimeStart") String createTimeStart,@RequestParam("createTimeEnd") String createTimeEnd,
			@RequestParam("activateTimeStart") String activateTimeStart,@RequestParam("activateTimeEnd") String activateTimeEnd
			) 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) { // 重置response response.reset();
		 * response.setContentType("application/json");
		 * response.setCharacterEncoding("utf-8"); Map<String, String> map = new
		 * HashMap<String, String>(); map.put("status", "failure"); map.put("message",
		 * "下载文件失败" + e.getMessage());
		 * response.getWriter().println(JSON.toJSONString(map)); log.error("销售清单下载文件失败",
		 * e.getMessage()); } finally { //关闭资源 if (excelWriter != null) {
		 * excelWriter.finish(); } if (outputStream != null) { outputStream.flush(); }
		 * if (in != null) { in.close(); } }
		 */
    }
    
    /**
     * 受理订单
     *
     * @param file 上传的数据文件
     * @return
     */
    @SuppressWarnings("unchecked")
	@Permission(menuname = "上传订单数据", value = "importOrder", method = RequestMethod.POST)
    public ResponseData<Map<String, Object>> importOrder(@RequestParam(name = "file") MultipartFile file) {
        StringBuffer info = new StringBuffer();
    	Map<String, Object> resultMap = new HashMap<>();

        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("b").toString().equals(FH_ORDER_PHONE)) {
            return ResponseData.error("b列不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("c").toString().equals(FH_NAME)) {
            return ResponseData.error("c列不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("d").toString().equals(FH_ACCOUNT)) {
            return ResponseData.error("d列不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("e").toString().equals(FH_SALES_SCHOOL)) {
            return ResponseData.error("e列不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("f").toString().equals(FH_SALES_SUBST)) {
            return ResponseData.error("f列不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("g").toString().equals(FH_ORDER_TYPE)) {
			return ResponseData.error("g列不对，请选择正确的模板！");
		}
        //校验数据
        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> dataResultMap = dealUploadData(listMap);
        int num = (int)dataResultMap.get("num");
        info.append("导入" + num+ "条数据！");

		// 将导出清单结果数据保存到redis中
		List<Map<String, Object>> errorList = (List<Map<String, Object>>)dataResultMap.get("infoList");
		if (errorList.size() > 0) {
			info.append("返回导入结果清单！");
			String key = UUID.randomUUID().toString().replace("-","");
			redisTemplate.opsForValue().set(key, JSONArray.fromObject(errorList).toString(), 30, TimeUnit.SECONDS);
			resultMap.put("key", key);
		}

		resultMap.put("msg", info);

        return ResponseData.success(resultMap, "导入完成");
    }
    
    public Map<String,Object> dealUploadData(List<Map<String,Object>> list){
    	Map<String, Object> resultMap = new HashMap<String, Object>();
    	
    	// 处理放号清单号码的销售学校清单，用于重复判断
    	Map<String, Object> phoneSchoolMap = MapUtil.listToMap(salesListMapper.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");
    		String orderType = getOrderType(map.get("g") != null ? map.get("g").toString() : "");
    		
			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);
			theMap.put("orderType", orderType);
    		
    		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 String getOrderType(String orderTypeName) {
    	if ("放号登记".equals(orderTypeName)) {
    		return "1";
		}else if ("新用户办理".equals(orderTypeName)) {
			return "0";
		}else if ("羊城通".equals(orderTypeName)) {
			return "4";
		}else if ("存量升级".equals(orderTypeName)) {
			return "2";
		}else if ("小白卡".equals(orderTypeName)) {
			return "3";
		}else if ("线上预制卡".equals(orderTypeName)) {
			return "5";
		}else if ("线上小白卡".equals(orderTypeName)) {
			return "6";
		}else if ("自助终端".equals(orderTypeName)) {
			return "7";
		}else{
    		return "无";
		}

	}
    
    // 插入销售订单表
    public int addSalesOrder(List<Map<String, Object>> list) {
    	int num = 0;
    	
    	for(Map<String, Object> map : list) {
    		SalesList order = new SalesList();
    		order.setOrderPhone((String)map.get("orderPhone"));
    		order.setUserId(Integer.toString((int)map.get("userId")));
    		order.setCreateTime(new Date());
			order.setLockState("否");
    		order.setSalesSubst((String)map.get("salesSubst"));
    		order.setSalesSchool((String)map.get("salesSchool"));
    		order.setOrderType((String)map.get("orderType"));
    		order.setName((String)map.get("name"));
    		order.setAccount((String)map.get("account"));
    		
    		num += salesListMapper.insert(order);
    	}
    	
    	return num;
    }
    
    // 更新号码销售学校表
    public void updatePhoneSchool(List<Map<String, Object>> list) {
    	for(Map<String, Object> map : list) {
    		salesListMapper.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 + "重复上传";
    		
    		salesListMapper.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文件，其他格式不支持！");
        }
		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(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<SalesList> wrapperOrder = new EntityWrapper<SalesList>();
    	wrapperOrder.ge("create_time", date);
    	wrapperOrder.eq("del_flag", 0);
    	wrapperOrder.eq("send_bonus", 0);
    	List<Map<String, Object>> orderList = salesListMapper.selectMaps(wrapperOrder);
    	Map<String, Map<String,Object>> orderMaps = MapUtil.listToMap(orderList, "orderPhone");

    	Date nowDate = new Date();

    	// 获取学校清单比较网点
    	Wrapper<School> wrapperSchool = new EntityWrapper<School>();
    	wrapperSchool.setSqlSelect(" school_name as schoolName,network_name as networkName,network_code as networkCode," +
				"start_date as startDate, end_date as endDate, greater_fifty as greaterFifty, greater_hundred as greaterHundred");
    	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<>();
    	
    	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;
			String status = "2";
			// 默认false为开学激励，true为前置激励
			boolean isNormalSchoolOpens = false;

    		if(null != orderMaps.get(orderPhone)) {
    			Map<String, Object> orderMap = (Map<String, Object>)orderMaps.get(orderPhone);
    			// 锁定跳过
    			if (orderMap.get("lock_state") != null && "是".equals(orderMap.get("lock_state"))) {
					continue;
				}
				String remark = "激活号码所属学校与上报学校不一致";
    			
    			if(null == networkName) {
    				isSkip = true;
    			}

    			Map<String, Object> schoolMap = (Map<String, Object>)schoolMaps.get(orderMap.get("salesSchool"));
    			
    			if(null != schoolMap.get("networkCode") && !"".equals(schoolMap.get("networkCode"))) {
	    			if(networkCode.equals(schoolMap.get("networkCode"))) {
	    				double bonus = 0;
	    				double allBonus = 0;
	    				double nonSendBonus = 0;
	    				double defaultGreaterFifty = 20;
						double defaultGreaterHundred = 40;

	    				// 获取数据
						Date startDate = schoolMap.get("startDate") == null ? null : (Date)schoolMap.get("startDate");
						Date endDate = schoolMap.get("endDate") == null ? null : (Date)schoolMap.get("endDate");
						Double greaterFifty = schoolMap.get("greaterFifty") == null ? null :  (Double)schoolMap.get("greaterFifty");
						Double greaterHundred = schoolMap.get("greaterHundred") == null ? null :  (Double)schoolMap.get("greaterHundred");

						if (startDate != null && endDate != null &&
								startDate.getTime() <= nowDate.getTime() && endDate.getTime() >= nowDate.getTime()) {
							isNormalSchoolOpens = true;
						}

						// 根据网点配置的时段进行判定
						if (isNormalSchoolOpens) {
							if (greaterFifty != null) {
								defaultGreaterFifty = greaterFifty;
							}
							if (greaterHundred != null) {
								defaultGreaterHundred = greaterHundred;
							}
						}

	    				if(amount >= 50 && amount < 100) {
	    					bonus = defaultGreaterFifty;
	    				}else if(amount >= 100) {
	    					bonus = defaultGreaterHundred;
	    				}
	    				
	    				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("nonSendBonus", nonSendBonus);
						orderMap.put("allBonus", allBonus);
	    				orderMap.put("recentlyAmount", amount);
	    				orderMap.put("recentlyBonus", bonus);
	    			}
    			}else {
    				remark = "该学校的网点信息未添加";
    			}

				// 不达标，充值激励，未发送激励，应发总激励为0
				if (!"3".equals(status)) {
					orderMap.put("nonSendBonus", 0d);
					orderMap.put("allBonus", 0d);
				}
    			
   				orderMap.put("status", status);
				orderMap.put("remark", remark);
				orderMap.put("activateTime", activateTime);
				orderMap.put("networkCode", networkCode);
				orderMap.put("networkName", networkName);
				orderMap.put("preType", isNormalSchoolOpens ? "前置" : "开学");
				updateList.add(orderMap);
    		}
    	}
    	
    	int num = updateAuditResult(updateList);
    	
    	return num;
    }
    
    // 更新审核结果到销售订单
    public int updateAuditResult(List<Map<String, Object>> list) {
    	int num = 0;
    	
    	try {
			SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    	for(Map<String, Object> map : list) {
	    		SalesList order = new SalesList();
	    		order.setId((int)map.get("id"));
	    		order.setStatus((String)map.get("status"));
	    		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("recentlyBonus"));
	    			order.setActivateTime(sFormat.parse((String)map.get("activateTime")));
	    			order.setNetworkCode((String)map.get("networkCode"));
	    			order.setNetworkName((String)map.get("networkName"));
					order.setPreType((String)map.get("preType"));
	    		}
	    		
	    		num += salesListMapper.updateById(order);
	    	}
    	}catch (Exception e) {
    		log.error("更新审核结果到销售订单异常" + e.getMessage(), e);
		}
    	
    	return num;
    }

	/**
	 * 审核锁定
	 *
	 * @param file 上传的数据文件
	 * @return
	 */
	@Permission(menuname = "审核锁定", value = "importAuditLock", method = RequestMethod.POST)
	public ResponseData<String> importAuditLock(@RequestParam(name = "file") MultipartFile file) {
		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(SD_ORDER_NUMBER)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("b").toString().equals(SD_LOCK_STATE)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		//校验数据
		try {
			//校验数据
			for (int i = 1; i < listMap.size(); i++) {
				Map<String, Object> temp = listMap.get(i);
				if (StringUtils.isBlank(temp.get("a").toString())) {
					return ResponseData.error("第" + (i + 1) + "行的电信激活号码不能为空");
				}
				if (StringUtils.isBlank(temp.get("b").toString())) {
					return ResponseData.error("第" + (i + 1) + "行的是否锁定不能为空");
				}
			}

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

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

		return ResponseData.success("修改完成，修改"+ count +"条订单！");
	}

	// 更新锁定状态
	public Integer updateLockState(List<Map<String, Object>> listMap) {

		Wrapper<SalesList> wrapper = null;
		Integer count = 0;
		List<String> lock = new ArrayList<>();
		List<String> unlock = new ArrayList<>();
		for(Map<String, Object> map : listMap) {
			if ("是".equals(map.get("b"))) {
				lock.add(map.get("a").toString());
			}else if ("否".equals(map.get("b"))){
				unlock.add(map.get("a").toString());
			}
		}

		if (lock.size() != 0) {
			wrapper = new EntityWrapper<>();
			wrapper.in("order_phone", lock.toArray());
			SalesList salesList = new SalesList();
			salesList.setLockState("是");
			count += salesListMapper.update(salesList, wrapper);
		}
		if (unlock.size() != 0) {
			wrapper = new EntityWrapper<>();
			wrapper.in("order_phone", unlock.toArray());
			SalesList salesList = new SalesList();
			salesList.setLockState("否");
			count += salesListMapper.update(salesList, wrapper);
		}

		return count;
	}

	/**
	 * 批量更新
	 *
	 * @param file 上传的数据文件
	 * @return
	 */
	@Permission(menuname = "批量更新", value = "importBatchUpdate", method = RequestMethod.POST)
	public ResponseData<String> importBatchUpdate(@RequestParam(name = "file") MultipartFile file) {
		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(PL_SALES_SUBST)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("b").toString().equals(PL_SALES_SCHOOL)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("c").toString().equals(PL_ORDER_PHONE)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("d").toString().equals(PL_NAME)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("e").toString().equals(PL_ACCOUNT)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("f").toString().equals(PL_CREATE_TIME)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("g").toString().equals(PL_ORDER_TYPE)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("h").toString().equals(PL_LOCK_STATE)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("i").toString().equals(PL_AUDIT_TIME)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("j").toString().equals(PL_NETWORK_CODE)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("k").toString().equals(PL_NETWORK_NAME)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("l").toString().equals(PL_ACTIVATE_TIME)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("m").toString().equals(PL_RECENTLY_AMOUNT)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("n").toString().equals(PL_RECENTLY_BONUS)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("o").toString().equals(PL_ALL_BONUS)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("p").toString().equals(PL_SEND_BONUS)) {
			return ResponseData.error("模板不对，请选择正确的模板！");
		}
		if (!listMap.get(0).get("q").toString().equals(PL_NONSEND_BONUS)) {
			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("c").toString())) {
					return ResponseData.error("第" + (i + 1) + "行的电信激活号码不能为空");
				}
				if (StringUtils.isBlank(temp.get("d").toString())) {
					return ResponseData.error("第" + (i + 1) + "行的销售员不能为空");
				}
				if (StringUtils.isBlank(temp.get("e").toString())) {
					return ResponseData.error("第" + (i + 1) + "行的销售账号不能为空");
				}
				if (StringUtils.isBlank(temp.get("g").toString())) {
					return ResponseData.error("第" + (i + 1) + "行的订单类型不能为空");
				}
			}

		} catch (Exception e) {
			log.error("批量修改导入异常" + e.getMessage(), e);
			return ResponseData.error("导入异常！");
		}

		// 去掉首行标题
		listMap.remove(0);
		int count = 0;
		try {
			count = batchUpdateData(listMap);
		}catch (ParseException p) {
			return ResponseData.error("修改失败，日期格式转换失败，请确认正确的日期格式！");
		}

		return ResponseData.success("修改完成，修改"+ count +"条订单！");
	}

	/**
	 * 处理批量导入数据(每条要更新的数据都不一样，只能一条条修改)
	 * @param list
	 * @return
	 */
	public Integer batchUpdateData(List<Map<String, Object>> list) throws ParseException {

		// 转换对象
		List<SalesList> salesLists = mapToObejct(list);
		int updateCount = 0;
		Wrapper<SalesList> wrapper = null;
		for(SalesList salesList : salesLists) {
			wrapper = new EntityWrapper<>();
			wrapper.eq("order_phone", salesList.getOrderPhone());
			wrapper.eq("del_flag", 0);
			Integer update = salesListMapper.update(salesList, wrapper);
			updateCount += update;
		}
		return updateCount;
	}

	// 订单数据对象转换
	public List<SalesList> mapToObejct(List<Map<String, Object>> listMap) throws ParseException {
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<SalesList> list = new ArrayList<>();
		SalesList salesList = null;
		for(Map<String, Object> map : listMap) {
			salesList = new SalesList();
			salesList.setSalesSubst(map.get("a").toString());
			salesList.setSalesSchool(map.get("b").toString());
			salesList.setOrderPhone(map.get("c").toString());
			salesList.setName(map.get("d").toString());
			salesList.setAccount(map.get("e").toString());
			if (map.get("f") != null && StringUtils.isNotBlank(map.get("f").toString())) {
				salesList.setCreateTime(simpleDateFormat.parse(map.get("f").toString()));
			}
			if (map.get("g") != null && StringUtils.isNotBlank(map.get("g").toString())) {
				salesList.setOrderType(OrderType.getCodeByName(map.get("g").toString()));
			}
			if (map.get("h") != null && StringUtils.isNotBlank(map.get("h").toString())) {
				salesList.setLockState(map.get("h").toString());
			}
			if (map.get("i") != null && StringUtils.isNotBlank(map.get("i").toString())) {
				salesList.setAuditTime(simpleDateFormat.parse(map.get("i").toString()));
			}
			if (map.get("j") != null && StringUtils.isNotBlank(map.get("j").toString())) {
				salesList.setNetworkCode(map.get("j").toString());
			}
			if (map.get("k") != null && StringUtils.isNotBlank(map.get("k").toString())) {
				salesList.setNetworkName(map.get("k").toString());
			}
			if (map.get("l") != null && StringUtils.isNotBlank(map.get("l").toString())) {
				salesList.setActivateTime(simpleDateFormat.parse(map.get("l").toString()));
			}
			if (map.get("m") != null && StringUtils.isNotBlank(map.get("m").toString())) {
				salesList.setRecentlyAmount(Double.parseDouble(map.get("m").toString()));
			}
			if (map.get("n") != null && StringUtils.isNotBlank(map.get("n").toString())) {
				salesList.setRecentlyBonus(Double.parseDouble(map.get("n").toString()));
			}
			if (map.get("o") != null && StringUtils.isNotBlank(map.get("o").toString())) {
				salesList.setAllBonus(Double.parseDouble(map.get("o").toString()));
			}
			if (map.get("p") != null && StringUtils.isNotBlank(map.get("p").toString())) {
				salesList.setSendBonus(Double.parseDouble(map.get("p").toString()));
			}
			if (map.get("q") != null && StringUtils.isNotBlank(map.get("q").toString())) {
				salesList.setNonSendBonus(Double.parseDouble(map.get("q").toString()));
			}
			list.add(salesList);
		}
		return list;
	}
    
    @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);
        columns.add(FH_ORDER_TYPE);
        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);
			sheet.setColumnWidth(6, 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 = "批量修改模板下载", method = RequestMethod.POST, value = "downloadTemplatePL")
	public ResponseData<String> downloadTemplatePL() {
		ArrayList<String> columns = new ArrayList<>();
		columns.add("销售县分");
		columns.add("销售学校");
		columns.add("电信激活号码");
		columns.add("销售员");
		columns.add("销售账号");
		columns.add("上传时间");
		columns.add("订单类型");
		columns.add("是否锁定");
		columns.add("系统匹配时间");
		columns.add("系统所属网点");
		columns.add("网点名称");
		columns.add("系统激活时间");
		columns.add("最近充值金额");
		columns.add("最近充值激励");
		columns.add("应发总激励");
		columns.add("已发激励");
		columns.add("未发激励");
		ServletOutputStream os = null;
		XSSFWorkbook workbook = new XSSFWorkbook();
		try {
			//创建工作表
			XSSFSheet sheet = workbook.createSheet("批量修改清单");
			sheet.setColumnWidth(1, 6500);
			sheet.setColumnWidth(2, 6500);
			sheet.setColumnWidth(3, 4500);
			sheet.setColumnWidth(4, 3500);
			sheet.setColumnWidth(5, 7000);
			sheet.setColumnWidth(6, 4000);
			sheet.setColumnWidth(7, 4500);
			sheet.setColumnWidth(8, 6500);
			sheet.setColumnWidth(9, 6500);
			sheet.setColumnWidth(10, 7000);
			sheet.setColumnWidth(11, 4000);
			sheet.setColumnWidth(12, 4500);
			sheet.setColumnWidth(13, 4500);
			sheet.setColumnWidth(14, 2000);
			sheet.setColumnWidth(15, 2000);
			sheet.setColumnWidth(16, 2000);
			sheet.setColumnWidth(17, 2000);

			//添加表头
			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 = "downloadTemplateSD")
	public ResponseData<String> downloadTemplateSD() {
		ArrayList<String> columns = new ArrayList<>();
		columns.add(SD_ORDER_NUMBER);
		columns.add(SD_LOCK_STATE);
		ServletOutputStream os = null;
		XSSFWorkbook workbook = new XSSFWorkbook();
		try {
			//创建工作表
			XSSFSheet sheet = workbook.createSheet("审核锁定清单");
			sheet.setColumnWidth(0, 5000);
			sheet.setColumnWidth(2, 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 = "导出导入结果", value = "exportResult", method = RequestMethod.POST)
    public void exportResult(String key) {
		
        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(FH_ORDER_TYPE);
        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("orderType");
        columns.add("desc");
		JSONArray jsonArray = new JSONArray();
        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
			Object o = redisTemplate.opsForValue().get(key);
			if (o != null) {
				jsonArray = JSONArray.fromObject(o);
			}
			//创建工作表
            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();
			JSONObject jsonObject = null;
            for(int i = 0; i < jsonArray.size(); ++i) {
				jsonObject = jsonArray.getJSONObject(i);
				final XSSFRow rowdata = sheet.createRow(++rowIndex);
				for (String column : columns) {
					XSSFCell cell = rowdata.createCell(Math.max(rowdata.getLastCellNum(), 0));
					if (jsonObject.get(column) != null) {
						cell.setCellValue(jsonObject.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();
        } 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")));  
    } 
    
    @Permission(menuname = "导出销售清单", value = "exportOrderList", method = RequestMethod.POST)
    public void exportOrderList(@RequestParam("orderPhone") String orderPhone,
			@RequestParam("name") String name,@RequestParam("account") String account,
			@RequestParam("status") String status,@RequestParam("orderType") String orderType,
			@RequestParam("createTimeStart") String createTimeStart,@RequestParam("createTimeEnd") String createTimeEnd,
			@RequestParam("activateTimeStart") String activateTimeStart,@RequestParam("activateTimeEnd") String activateTimeEnd,
			@RequestParam("salesSubst") String salesSubst,@RequestParam("salesSchool") String salesSchool,
			@RequestParam("preType") String preType
    		) throws IOException {

		Wrapper<SalesList> wrapperOrder = new EntityWrapper<>();
		wrapperOrder.eq(StringUtils.isNotBlank(orderPhone), "order_phone", orderPhone);
		wrapperOrder.eq(StringUtils.isNotBlank(orderType), "order_type", orderType);
		wrapperOrder.eq(StringUtils.isNotBlank(status), "status", status);
		wrapperOrder.eq(StringUtils.isNotBlank(name), "name", name);
		wrapperOrder.eq(StringUtils.isNotBlank(account), "account", account);
		wrapperOrder.eq(StringUtils.isNotBlank(preType), "pre_type", preType);
		wrapperOrder.eq(StringUtils.isNotBlank(salesSubst), "sales_subst", salesSubst);
		wrapperOrder.eq(StringUtils.isNotBlank(salesSchool), "sales_school", salesSchool);
		wrapperOrder.gt(StringUtils.isNotBlank(createTimeStart), "create_time", createTimeStart);
		wrapperOrder.lt(StringUtils.isNotBlank(createTimeEnd), "create_time", createTimeEnd);
		wrapperOrder.gt(StringUtils.isNotBlank(activateTimeStart), "activate_time", activateTimeStart);
		wrapperOrder.lt(StringUtils.isNotBlank(activateTimeEnd), "activate_time", activateTimeEnd);
		wrapperOrder.eq("del_flag", 0);
		wrapperOrder.orderBy("create_time", false);
		List<Map<String, Object>> orderList = salesListMapper.selectMaps(wrapperOrder);

        List<String> tops = new ArrayList<>();
		tops.add("销售县分");
        tops.add("销售学校");
        tops.add("电信激活号码");
        tops.add("销售员");
        tops.add("销售账号");
        tops.add("上传时间");
		tops.add("订单类型");
		tops.add("是否锁定");
		tops.add("系统匹配时间");
		tops.add("系统所属网点");
		tops.add("网点名称");
		tops.add("系统激活时间");
		tops.add("最近充值金额");
		tops.add("最近充值激励");
		tops.add("应发总激励");
		tops.add("已发激励");
		tops.add("未发激励");

        List<String> columns = new ArrayList<>();
        columns.add("salesSubst");
        columns.add("salesSchool");
        columns.add("orderPhone");
        columns.add("name");
        columns.add("account");
        columns.add("createTime");
        columns.add("orderType");
		columns.add("lockState");
		columns.add("auditTime");
		columns.add("networkCode");
		columns.add("networkName");
		columns.add("activateTime");
		columns.add("recentlyAmount");
		columns.add("recentlyBonus");
		columns.add("allBonus");
		columns.add("sendBonus");
		columns.add("nonSendBonus");

        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet("积分记录清单");
            //添加表头
            final XSSFRow row = sheet.createRow(0);
            //设置列宽度
            sheet.setColumnWidth(1, 6500);
            sheet.setColumnWidth(2, 6500);
            sheet.setColumnWidth(3, 4500);
            sheet.setColumnWidth(4, 3500);
            sheet.setColumnWidth(5, 7000);
            sheet.setColumnWidth(6, 4000);
			sheet.setColumnWidth(7, 4500);
			sheet.setColumnWidth(8, 6500);
			sheet.setColumnWidth(9, 6500);
			sheet.setColumnWidth(10, 7000);
			sheet.setColumnWidth(11, 4000);
			sheet.setColumnWidth(12, 4500);
			sheet.setColumnWidth(13, 4500);
			sheet.setColumnWidth(14, 2000);
			sheet.setColumnWidth(15, 2000);
			sheet.setColumnWidth(16, 2000);
			sheet.setColumnWidth(17, 2000);
            //第一行字段名称
            tops.forEach(column -> {
                XSSFCell cell = row.createCell(Math.max(row.getLastCellNum(), 0));
                cell.setCellValue(column);
            });
            //数据
            int rowIndex = row.getRowNum();
            for (Map<String, Object> maps: orderList) {
                final XSSFRow rowdata = sheet.createRow(++rowIndex);
                for (String column : columns) {
                    XSSFCell cell = rowdata.createCell(Math.max(rowdata.getLastCellNum(), 0));
                    if (maps.get(column) != null) {
                    	if ("orderType".equals(column)) {
							cell.setCellValue(OrderType.getNameByCode(maps.get(column).toString()));
						}else{
							// 订单类型数据替换
							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();
        } catch (Exception e) {
            log.error("文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }
        
    }

}
