package com.cftech.workshop.workshop.web;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cftech.base.codingrule.utils.CodingruleUtils;

import com.cftech.core.schafflerentity.VisitEntity;
import com.cftech.core.util.*;
import com.cftech.workshop.details.model.Details;
import com.cftech.workshop.details.service.DetailsService;
import com.cftech.workshop.meeting.model.Meeting;
import com.cftech.workshop.meeting.service.MeetingService;
import com.cftech.workshop.workshop.model.Workshop;
import com.cftech.workshop.workshop.model.WorkshopDto;
import com.cftech.workshop.workshop.model.WorkshopRSVP;
import com.cftech.workshop.workshop.service.WorkshopService;
import com.cftech.core.poi.ExcelKit;
import com.cftech.core.scope.OrderType;
import com.cftech.core.sql.Conds;
import com.cftech.core.sql.Sort;
import com.cftech.sys.security.UserUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.util.HtmlUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.*;


/**
 * Controller
 * <p>
 * 权限字符串说明:
 * 查看:public static final String WORKSHOP_VIEW = "qy:workshop:view"
 * 查看:public static final String WORKSHOP_EDIT = "qy:workshop:edit"
 *
 * @author byj
 * @date: 2018-06-22 17:30
 */
@Slf4j
@Controller
@RequestMapping("/a/workshop")
public class WorkshopController {

    public static final String WORKSHOP_VIEW = "qy:workshop:view";
    public static final String WORKSHOP_EDIT = "qy:workshop:edit";

    @Autowired
    private WorkshopService workshopService;
    @Autowired
    private CodingruleUtils codingruleUtils;

    @Autowired
    private MeetingService meetingService;

    @Autowired
    private DetailsService detailsService;

    //列表页面
    @RequiresPermissions(value = WORKSHOP_VIEW)
    @RequestMapping(value = "/list" , method = {RequestMethod.POST,RequestMethod.GET})
    public String list(HttpServletRequest request, Model model,Long meetingId) {
        Long accountId = UserUtils.getmpaccounts(request);
        model.addAttribute("accountId", accountId);

        model.addAttribute("meetingId", meetingId);
        return "workshop/workshoplist";
    }

    //编辑页面（新增、修改）
    @RequiresPermissions(value = WORKSHOP_VIEW)
    @RequestMapping(value = "/form" , method = RequestMethod.POST)
    public String form(HttpServletRequest request, String id, Model model) {
        if (!StringUtils.isEmpty(id)) {
            Workshop workshop = workshopService.fetchById(id);
            model.addAttribute("data", workshop);
        }
        return "workshop/workshopform";
    }

    //提交数据（新增、修改）
    @RequiresPermissions(value = WORKSHOP_EDIT)
    @RequestMapping(value = "/formData" , method = RequestMethod.POST)
    @ResponseBody
    public JSONObject formData(Workshop workshop, Model model, HttpServletRequest request) {
        Long accountsId = UserUtils.getmpaccounts(request);
        JSONObject rtnJson = new JSONObject();
        try {
            if (workshop != null && workshop.getId() != null) {
                workshop.setUpdateBy(UserUtils.getUser().getId());
                workshopService.update(workshop);
                rtnJson.put("errorNo", 0);
            } else {
                workshop.setNumber(codingruleUtils.getNumber(accountsId, Workshop.class.getName()));
                workshop.setAccountsId(accountsId);
                workshop.setDelFlag(false);
                workshop.setAccountsId(UserUtils.getmpaccounts(request));
                workshop.setCreateBy(UserUtils.getUser().getId());
                workshop.setUpdateBy(UserUtils.getUser().getId());
                workshopService.save(workshop);
                rtnJson.put("errorNo", 2);
            }
        } catch (Exception e) {
            log.error("保存WORKSHOP异常",e);
            rtnJson.put("errorNo", 1);
        }
        return rtnJson;
    }

    //获取列表数据
    @RequiresPermissions(value = WORKSHOP_VIEW)
    @RequestMapping(value = "/listData" , method = {RequestMethod.GET,RequestMethod.POST})
    @ResponseBody
    public JSONObject listData(int iDisplayStart, int iDisplayLength, WorkshopDto workshopDto, HttpServletRequest request) {
        Long accountsId = UserUtils.getmpaccounts(request);
        Conds conds = new Conds();
        //conds.equal("w.del_flag", Constants.DEL_FLAG_0);
        conds.equal("s.accounts_id", accountsId);
        if(!StringUtils.isEmpty(workshopDto.getSurname())){
            conds.like("s.surname",workshopDto.getSurname());
        }
        if (!StringUtils.isEmpty(workshopDto.getFirst_name())) {
            conds.like("s.first_name", workshopDto.getFirst_name());
        }
        if(workshopDto.getWsid()!=null){
            conds.equal("s.wsid",workshopDto.getWsid());
        }
        if (!StringUtils.isEmpty(workshopDto.getPhone())) {
            conds.like("s.phone", workshopDto.getPhone());
        }
        if(!StringUtils.isEmpty(workshopDto.getSort())){
            conds.equal("s.sort",workshopDto.getSort());
        }
        if(!StringUtils.isEmpty(workshopDto.getMname())){
            conds.like("s.msurname",workshopDto.getMname());
        }
        if(!StringUtils.isEmpty(workshopDto.getMsurname())){
            conds.like("s.mname",workshopDto.getMsurname());
        }
        //Sort sort = new Sort("w.del_flag", OrderType.ASC);
        Integer counts = workshopService.delnum(conds);
        List<WorkshopDto> list = workshopService.delworkshop(conds, null, iDisplayStart, iDisplayLength);


        JSONObject rtnJson = new JSONObject();
        rtnJson.put("iTotalRecords", counts);
        rtnJson.put("iTotalDisplayRecords", counts);
        rtnJson.put("aaData", list);
        return rtnJson;
    }

    //删除数据
    @RequiresPermissions(value = WORKSHOP_EDIT)
    @RequestMapping(value = "/delete",method = RequestMethod.POST)
    @ResponseBody
    public JSONObject delete(String id) {
        JSONObject rtnJosn = new JSONObject();
        try {
            workshopService.delete(id);
            rtnJosn.put("errorNo", 0);
        } catch (Exception e) {
            log.error("删除WORKSHOP异常",e);
            rtnJosn.put("errorNo", 1);
        }
        return rtnJosn;
    }

    //下载模板
    @RequestMapping(value = "/templateExcel" , method = {RequestMethod.GET,RequestMethod.POST})
    @RequiresPermissions(value = WORKSHOP_EDIT)
    @ResponseBody
    public void templateExcel(HttpServletRequest request, HttpServletResponse response , Workshop workshop) {
        // 生成Excel并使用浏览器下载

        ExcelKit.$Export(WorkshopDto.class, response).toExcel(null, "预导入名单信息");
    }


    @RequestMapping(value = "/exportInviteCodeExcel" , method = {RequestMethod.GET,RequestMethod.POST})
    @RequiresPermissions(value = WORKSHOP_EDIT)
    @ResponseBody
    public void exportInviteCodeExcel(HttpServletRequest request, HttpServletResponse response , Workshop workshop ) {
        // 生成Excel并使用浏览器下载
        Long accountsId = UserUtils.getmpaccounts(request);
        Conds conds = new Conds();
        //conds.equal("w.del_flag", Constants.DEL_FLAG_0);
        conds.equal("s.accounts_id", accountsId);
        if(!UserUtils.isAdmin()){
            conds.equal("s.create_by", UserUtils.getUser().getId());
        }
        if (!StringUtils.isEmpty(workshop.getFirst_name())) {
            conds.like("s.first_name", workshop.getFirst_name());
        }
        if(workshop.getWsid()!=null){
            conds.equal("s.wsid",workshop.getWsid());
        }
        if (!StringUtils.isEmpty(workshop.getPhone())) {
            conds.like("s.phone", workshop.getPhone());
        }
        Sort sort = new Sort("s.sort", OrderType.ASC);

        List<WorkshopDto> list = workshopService.delworkshop(conds, sort, 0, 0);
        ExcelKit.$Export(WorkshopDto.class, response).toExcel(list, "预导入名单详情");
    }

    //导出
    @RequestMapping(value = "/exportExcel" , method = {RequestMethod.GET,RequestMethod.POST})
    @RequiresPermissions(value = WORKSHOP_EDIT)
    @ResponseBody
    public void exportExcel(HttpServletRequest request, HttpServletResponse response , Workshop workshop , String meetingId) {
        // 生成Excel并使用浏览器下载
        try{
            Conds conds = new Conds();
            if (!StringUtils.isEmpty(meetingId)) {
                conds.equal("s.wsid", meetingId);
                //conds.equal("w.del_flag",0);
            }
            Sort sort = new Sort("s.sort", OrderType.ASC);
            List<WorkshopRSVP> list1 = workshopService.exportRSVP(conds,sort,0,0);


            Meeting meeting = meetingService.fetchById(meetingId);
                //研讨会开始和结束时间
                /*String meetingStart = DateUtils.formatDate(meeting.getStartTime());
                String meetingEnd = DateUtils.formatDate(meeting.getEndTime());
                List<String> strList = new ArrayList<String>();
                strList.add(meetingStart);
                for(int k =1;k>=1;k++){
                    Calendar c = Calendar.getInstance();
                    c.setTime(DateUtils.parseDate(meetingStart));
                    c.add(Calendar.DAY_OF_MONTH, k);// 今天+1天
                    Date tomorrow = c.getTime();
                    strList.add(DateUtils.formatDate(tomorrow,"yyyy-MM-dd"));
                    if(DateUtils.formatDate(tomorrow,"yyyy-MM-dd").equals(meetingEnd)){
                        break;
                    }
                }*/
                //需求服务
                String service = HtmlUtils.htmlUnescape(meeting.getService());
            List<String> gongchangList = new ArrayList<String>();
            //试乘试驾
            List<String> shicheng = new ArrayList<String>();
            //酒店服务
            List<String> strList = new ArrayList<String>();

            //餐饮服务
            List<String> catering = new ArrayList<String>();

                if(!StringUtils.isEmpty(service)){
                    JSONObject object = JSONObject.parseObject(service);
                    //工厂参观
                    if(object.get("isfactory").equals("0")){
                        JSONArray array = JSON.parseArray(object.getString("factory"));
                        for(int i =0;i<array.size();i++){
                            JSONObject jsonObject = array.getJSONObject(i);
                            gongchangList.add(jsonObject.getString("visittime")+" "+jsonObject.getString("des"));
                        }
                    }
                    if(object.get("isTake").equals("0")){
                        JSONArray array = JSON.parseArray(object.getString("take"));
                        for(int i=0;i<array.size();i++){
                            JSONObject jsonObject = array.getJSONObject(i);
                            shicheng.add(jsonObject.getString("taketime"));
                        }
                    }
                    if(object.get("isHotel").equals("0")){
                        JSONArray array = JSON.parseArray(object.getString("hotel"));
                        for(int i = 0 ; i<array.size(); i++){
                            JSONObject jsonObject = array.getJSONObject(i);
                            String meetingStart = jsonObject.getString("startDate");
                            String meetingEnd = jsonObject.getString("endDate");
                            strList.add(meetingStart);
                            for(int k =1;k>=1;k++){
                                Calendar c = Calendar.getInstance();
                                c.setTime(DateUtils.parseDate(meetingStart));
                                c.add(Calendar.DAY_OF_MONTH, k);// 今天+1天
                                Date tomorrow = c.getTime();
                                strList.add(DateUtils.formatDate(tomorrow,"yyyy-MM-dd"));
                                if(DateUtils.formatDate(tomorrow,"yyyy-MM-dd").equals(meetingEnd)){
                                    break;
                                }
                            }
                        }
                    }


                    if(object.containsKey("isEat") && "0".equals(object.get("isEat"))){
                        JSONArray array = JSON.parseArray(object.getString("eat"));
                        for(int i=0;i<array.size();i++){
                            JSONObject jsonObject = array.getJSONObject(i);
                            catering.add(jsonObject.getString("eatname"));
                        }
                    }


                }
                WorkshopController workshopController = new WorkshopController();
                List<VisitEntity> list = new ArrayList<VisitEntity>();
                list=mockData(catering,strList,gongchangList,shicheng,list1);
                try {
                    workshopController.generateExcel(catering,strList,gongchangList,shicheng,list,response,meeting.getTitle()+"RSVP");
                } catch (IOException e) {
//                    e.printStackTrace();
                    log.error("导出WORKSHOP，文件异常",e);
                }
        }catch (Exception e){
//            e.printStackTrace();
            log.error("导出WORKSHOP异常",e);
        }

    }
    public   void generateExcel(List<String> catering,List<String> dynamicCols,List<String> plantCols,List<String> testDriveCols,List<VisitEntity> mockDataList,HttpServletResponse rs,String title) throws IOException
    {
        int dySize = dynamicCols.size();
        if(dySize==0){
            dynamicCols.add("无");
            dySize = dynamicCols.size();
        }
        int plantSize = plantCols.size();
        if(plantSize == 0){
            plantCols.add("无");
            plantSize = plantCols.size();
        }
        int testDriveSize = testDriveCols.size();

        if(testDriveSize == 0){
            testDriveCols.add("无");
            testDriveSize = testDriveCols.size();
        }

        int cateringSize = catering.size();

        if(cateringSize == 0){
            catering.add("无");
            cateringSize = catering.size();
        }

        //创建Excel
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet
        Sheet createSheet = wb.createSheet("RSVP");
        // 设置标题字体
        Font fontTitle = wb.createFont();
        fontTitle.setFontHeightInPoints((short) 10); // 字体大小
        fontTitle.setColor(HSSFColor.WHITE.index); // 字体颜色
        fontTitle.setFontName("Arial"); // 字体
        //fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体显示
        fontTitle.setBold(true);
        // 设置标题单元格类型  //蓝色区域
        CellStyle greenRegion = generateHeaderStyle(IndexedColors.GREEN.index,wb,fontTitle,(byte) 0, (byte) 137, (byte) 61);
        CellStyle blueRegion = generateHeaderStyle(IndexedColors.BLUE.index,wb,fontTitle,(byte) 0, (byte) 176, (byte) 240);
        CellStyle darkRegion =  generateHeaderStyle(IndexedColors.DARK_BLUE.index,wb,fontTitle,(byte) 31, (byte) 73, (byte) 125);
        CellStyle greyRegion =  generateHeaderStyle(IndexedColors.GREY_25_PERCENT.index,wb,fontTitle,(byte) 217, (byte) 217, (byte) 217);


        CellStyle cellStyle = highlightedCell(wb);

        // 创建合并单元格
        Map<String,CellRangeAddress> regions = initRange(cateringSize,dySize,plantSize,testDriveSize);
        //合并单元格
        addRange(createSheet, regions);
        // -----------填充第一行数据-------------
        Row rowTitle = createSheet.createRow(0);
        generateCell(rowTitle,0,greenRegion,"Item No.");
        generateCell(rowTitle,1,blueRegion,"个人信息 \n Personal Information");
        generateCell(rowTitle,8+3,greenRegion,"Hospitality Information \n 入住信息");
        generateCell(rowTitle,10+3+dySize,darkRegion,"Transportation \n 交通");
        generateCell(rowTitle,19+3+1+dySize,greenRegion,"Plant Tour \n 工厂参观");
        generateCell(rowTitle,19+3+1+plantSize+dySize,greenRegion,"Test Drive \n 试乘试驾");
        generateCell(rowTitle,19+3+1+plantSize+dySize+testDriveSize,greenRegion,"Catering\n 餐饮");
        generateCell(rowTitle,19+3+1+plantSize+dySize+testDriveSize+cateringSize,greenRegion,"Remark \n 备注");
        rowTitle.setHeightInPoints(80);
        // -----------填充第二行数据-------------
        Row seoncRow = createSheet.createRow(1);
        generateCell(seoncRow,8+3,greenRegion,"需要住宿");//living
        generateCell(seoncRow,8+3+dySize,greenRegion,"不需要住宿");//no living
        generateCell(seoncRow,9+3+dySize,greenRegion,"state \n 状态"); //noSmoking
        generateCell(seoncRow,10+3+dySize,darkRegion,"Pick-up Service \n 接机/接站");//pickUp
        generateCell(seoncRow,13+3+1+dySize,darkRegion,"Self-driving \n 自驾");//selfDriving
        generateCell(seoncRow,14+3+1+dySize,darkRegion,"Drop-off Service \n 送机/送站");//dropOff
        generateCell(seoncRow,18+3+1+dySize,darkRegion,"Self-driving \n 自驾");//selfDriving
        //设置动态列内容
        for(int i=0;i<plantSize;i++)
        {
            String value = plantCols.get(i);
            generateCell(seoncRow,i+19+3+1+dySize,greenRegion,value);
        }
        for(int i=0;i<testDriveSize;i++)
        {
            String value = testDriveCols.get(i);
            generateCell(seoncRow,19+3+1+plantSize+dySize+i,greenRegion,value);
        }
        for(int i=0;i<cateringSize;i++)
        {
            String value = catering.get(i);
            generateCell(seoncRow,19+3+1+plantSize+dySize+testDriveSize+i,greenRegion,value);
        }
        // -----------填充第三行数据-------------
        Row lastHeaderRow = createSheet.createRow(2);
        generateCell(lastHeaderRow,1,blueRegion,"Last Name \n 姓");// 设置 姓
        generateCell(lastHeaderRow,2,blueRegion,"First Name \n 名");// 设置 名
        generateCell(lastHeaderRow,3,blueRegion,"Gender \n 性别");//设置性别
        generateCell(lastHeaderRow,4,blueRegion,"Company/Organization \n 公司/组织");//公司/组织
        generateCell(lastHeaderRow,5,blueRegion,"Register Company/Organization \n 注册公司/组织");//职务
        generateCell(lastHeaderRow,6,blueRegion,"Email Address \n 邮箱/Bussiness card  \n 名片输入");// Email Address 名片输入
        generateCell(lastHeaderRow,7,blueRegion,"Mobile Phone \n 手机");// 手机
        generateCell(lastHeaderRow,8,blueRegion,"Register Last Name \n 注册姓");// 注册姓
        generateCell(lastHeaderRow,9,blueRegion,"Register First Name \n 注册名");// 注册名
        generateCell(lastHeaderRow,10,blueRegion,"Register Mobile Phone \n 注册手机号");// 注册手机号


        //设置动态列内容
        for(int i=0;i<dynamicCols.size();i++)
        {
            String value = dynamicCols.get(i);
            generateCell(lastHeaderRow,i+3+8,greenRegion,value);// 手机
        }
        //设置最后两行为空
        for(int i=0;i<2;i++)
        {
            generateCell(lastHeaderRow,i+3+8+dySize,greenRegion,"");// 手机
        }
        generateCell(lastHeaderRow,10+3+dySize,darkRegion,"Flight No/Train No \n 航班号/车次");// 接机的航班号
        generateCell(lastHeaderRow,11+3+dySize,darkRegion,"Date of Arrival \n 到达日期");// 接机的 到达日期
        generateCell(lastHeaderRow,12+3+dySize,darkRegion,"TIme of Arrival \n 到达时间");// 接机的 到达时间
        generateCell(lastHeaderRow,13+3+dySize,darkRegion,"Arrival Airport/Train Station \n 到达站点");// 接机的 站点
        generateCell(lastHeaderRow,14+3+1+dySize,darkRegion,"Flight No/Train No \n 航班号/车次");// 出发的航班号
        generateCell(lastHeaderRow,15+3+1+dySize,darkRegion,"Departure Date \n 出发日期");// 出发日期
        generateCell(lastHeaderRow,16+3+1+dySize,darkRegion,"TIme of Depature \n 出发时间");// 出发时间
        generateCell(lastHeaderRow,17+3+1+dySize,darkRegion,"Departure Airport/Train Station \n 出发站点");// 出发站点
        //设置动态列表头最后一行的空格，否则单元格没有边框
        for(int i=0;i<plantSize;i++)
        {
            generateCell(lastHeaderRow,i+3+19+1+dySize,greenRegion,"");
        }
        for(int i=0;i<testDriveSize;i++)
        {
            generateCell(lastHeaderRow,19+3+1+plantSize+dySize+i,greenRegion,"");
        }

        for(int i = 0;i<cateringSize;i++){
            generateCell(lastHeaderRow,19+3+1+plantSize+dySize+testDriveSize+i,greenRegion,"");
        }

        generateCell(lastHeaderRow,19+3+1+testDriveSize+plantSize+dySize+cateringSize,greyRegion,"灰色提示文字：如随行人员餐饮招待、停车位等需求。");
        //设置为空单元格
        //-----结束表头设置-------
        //解决合并单元格造成的边框样式丢失问题
        handleMergeRegionCss(createSheet,regions,greenRegion,blueRegion,darkRegion);
        createSheet.setColumnWidth(0, 1200);
//		createSheet.autoSizeColumn(0, true);

        String prefix = "xls";
        generateData(createSheet,mockDataList,dynamicCols,plantCols,testDriveCols,catering,cellStyle);
        rs.reset();
        rs.setContentType("multipart/form-data"); //自动识别
        rs.setHeader("Content-Type","application/octet-stream");
        rs.setHeader("Content-Disposition","attachment;filename="+new String(title.getBytes("gb2312"), "ISO8859-1" )+"."+prefix);
        //文件流输出到rs里
        wb.write(rs.getOutputStream());
        rs.getOutputStream().flush();
        rs.getOutputStream().close();
    }


    //生成单元格
    private void generateCell(Row row,int cellNumber,CellStyle css,String value)
    {
        Cell cellTitle = row.createCell(cellNumber);
        if(css!=null)
            cellTitle.setCellStyle(css);
        cellTitle.setCellValue(value);// 设置内容
    }


    private void setRegionStyle(Sheet sheet, CellRangeAddress region,
                                CellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {

            Row row = sheet.getRow(i);
            if (row == null)
                row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);

            }
        }
    }

    //初始化region合并内容
    private  Map<String,CellRangeAddress> initRange(int cateringSize,int dySize,int plantTourSize,int testDriveSize)
    {
        Map<String,CellRangeAddress> rangeMap = new HashMap<String,CellRangeAddress>();
        CellRangeAddress personInfo = new CellRangeAddress(0, 1,1, 7+3);// 下标从0开始 个人信息合并不包括个人信息详情合并
        CellRangeAddress itemNo = new CellRangeAddress(0, 2, 0, 0);// 起始行号，终止行号， 起始列号，终止列号   ITEMNO 合并
        CellRangeAddress liveRegion = new CellRangeAddress(0, 0,8+3, 9+dySize+3);
        CellRangeAddress needLiveRegion = new CellRangeAddress(1,1,8+3, 7+dySize+3);
        CellRangeAddress transportHeaderRegion = new CellRangeAddress(0,0,10+dySize+3, 18+dySize+3+1);
        CellRangeAddress pickUpRegion = new CellRangeAddress(1,1,10+dySize+3, 13+dySize+3);
        CellRangeAddress selfDrivingPickRegion = new CellRangeAddress(1,2,13+dySize+3+1, 13+dySize+3+1);
        CellRangeAddress dropOffRegion = new CellRangeAddress(1,1,14+dySize+3+1, 17+dySize+3+1);
        CellRangeAddress selfDrivingRegion = new CellRangeAddress(1,2,18+dySize+3+1, 18+dySize+3+1);
        CellRangeAddress plantTourRegion = new CellRangeAddress(0,0,19+dySize+3+1, 18+plantTourSize+dySize+3+1);
        CellRangeAddress testDriveRegion = new CellRangeAddress(0,0,19+plantTourSize+dySize+3+1, 18+testDriveSize+plantTourSize+dySize+3+1);
        CellRangeAddress cateringRegion = new CellRangeAddress(0,0,19+plantTourSize+dySize+testDriveSize+3+1, 18+testDriveSize+plantTourSize+dySize+cateringSize+3+1);
        CellRangeAddress remarkRegion = new CellRangeAddress(0,1,19+testDriveSize+plantTourSize+dySize+cateringSize+3+1,19+testDriveSize+plantTourSize+dySize+cateringSize+3+1);
        rangeMap.put("personInfo",personInfo);
        rangeMap.put("itemNo",itemNo);
        rangeMap.put("liveRegion",liveRegion);
        rangeMap.put("needLiveRegion",needLiveRegion);
        rangeMap.put("transportHeaderRegion",transportHeaderRegion);
        rangeMap.put("pickUpRegion",pickUpRegion);
        rangeMap.put("selfDrivingPickRegion",selfDrivingPickRegion);
        rangeMap.put("dropOffRegion",dropOffRegion);
        rangeMap.put("selfDrivingRegion",selfDrivingRegion);
        rangeMap.put("plantTourRegion",plantTourRegion);
        rangeMap.put("testDriveRegion",testDriveRegion);
        rangeMap.put("cateringRegion",cateringRegion);
        rangeMap.put("remarkRegion",remarkRegion);
        return rangeMap;
    }


    //跟进合并单元列表 向表格中加入合并单元
    private void addRange(Sheet createSheet,Map<String,CellRangeAddress> regions)
    {
        Iterator<CellRangeAddress> coll =	regions.values().iterator();
        while(coll.hasNext())
        {
            createSheet.addMergedRegion(coll.next());
        }
    }

    //解决单元格合并问题 无边框问题。
    private void handleMergeRegionCss(Sheet createSheet,Map<String,CellRangeAddress> regions,
                                      CellStyle greenRegion,CellStyle	blueRegion,CellStyle darkRegion)
    {
        for (Map.Entry<String, CellRangeAddress> entry : regions.entrySet()) {
            String key = entry.getKey();
            CellRangeAddress region = entry.getValue();
            if("personInfo".equals(key))
            {
                setRegionStyle(createSheet,region,blueRegion);
            }else if("itemNo".equals(key)||"liveRegion".equals(key)||"needLiveRegion".equals(key)||"plantTourRegion".equals(key)||"testDriveRegion".equals(key)||"remarkRegion".equals(key)||"cateringRegion".equals(key))
            {
                setRegionStyle(createSheet,region,greenRegion);
            }else
            {
                setRegionStyle(createSheet,region,darkRegion);
            }
        }
    }

    //生成标题颜色区域
    private  CellStyle generateHeaderStyle(short index,HSSFWorkbook wb,Font fontTitle,byte r,byte g,byte b)
    {
        HSSFPalette palette = wb.getCustomPalette();
        CellStyle cellStyleTitle = wb.createCellStyle();
        cellStyleTitle.setFont(fontTitle);
        cellStyleTitle.setFillForegroundColor(index);
        palette.setColorAtIndex(index, r, g, b); //蓝色区域
        cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER); // 水平布局：居中
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleTitle.setWrapText(true);// 设置自动换行
        cellStyleTitle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyleTitle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyleTitle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyleTitle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyleTitle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleTitle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleTitle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleTitle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return cellStyleTitle;
    }

    private static List<VisitEntity> mockData(List<String> caterList,List<String> hotelList,List<String> gongchangList,List<String> shicheng,List<WorkshopRSVP> list)
    {
        List<VisitEntity> entities = new ArrayList<VisitEntity>();

        for(int i=0;i<list.size();i++)
        {
            WorkshopRSVP workshopRSVP = list.get(i);
            VisitEntity entity = new VisitEntity();
            entity.setNoneedHosp("-1");
            Map<String,String> valuejiudian = new HashMap<String,String>();
            for(String str : hotelList){
                valuejiudian.put(str,"0");
            }
            entity.setNeedHosp(valuejiudian);

            Map<String,String> valueshicheng = new HashMap<String,String>();
            for(String str : shicheng){
                valueshicheng.put(str,"0");
            }
            entity.setTestDrive(valueshicheng);

            Map<String,String> valuegongchang = new HashMap<String,String>();
            for(String str : gongchangList){
                valuegongchang.put(str,"0");
            }

            Map<String,String> valueCater = new HashMap<String,String>();
            for(String str : caterList){
                valueCater.put(str,"0");
            }

            entity.setCaterIng(valueCater);//餐饮
            entity.setPlanTour(valuegongchang);
            entity.setSelfDriving("0");//是否自驾//送站
            entity.setSelfPickDriving("0");//是否自驾//接站
            entity.setItemNo(String.valueOf(workshopRSVP.getSort()));
            entity.setLastName(workshopRSVP.getSurname()); //预导入姓
            entity.setFirstName(workshopRSVP.getFirst_name()); //预导入名
            entity.setGender(workshopRSVP.getGender()); //预导入性别
            entity.setCompany(workshopRSVP.getOrganization());//预导入公司
            entity.setJob(workshopRSVP.getMorganization()); //预导入公司
            entity.setEmail(workshopRSVP.getEmail()); //预导入邮箱
            entity.setMobile(workshopRSVP.getPhone()); //预导入手机
            entity.setRegiLasetName(workshopRSVP.getMsurname()); //会员姓
            entity.setRegiFirstName(workshopRSVP.getMname()); //会员名
            entity.setRegiMobile(workshopRSVP.getMphone()); //会员手机
            if("0".equals(workshopRSVP.getDel_flag())){
                if("0".equals(workshopRSVP.getStatus())){
                    entity.setNosmoking("待报名");
                }else if("1".equals(workshopRSVP.getStatus())){
                    entity.setNosmoking("已报名");
                }else if("2".equals(workshopRSVP.getStatus())){
                    entity.setNosmoking("已签到");
                }else if("-1".equals(workshopRSVP.getStatus())){
                    entity.setNosmoking("已拒绝");
                }else{
                    entity.setNosmoking("");
                }
            }else if("1".equals(workshopRSVP.getDel_flag())){
                entity.setNosmoking("已删除");
            }


            if(!StringUtils.isEmpty(workshopRSVP.getDetailsinfo())){
                String details = HtmlUtils.htmlUnescape(workshopRSVP.getDetailsinfo());  //会员详情信息
                JSONObject object = JSONObject.parseObject(details);
                if(object.get("isRelay").toString().equals("0") ){
                    JSONObject relayAray = JSON.parseObject(object.getString("relay")); //接
                    if(!StringUtils.isEmpty(relayAray)){
                        if(relayAray.containsKey("isPlane") && relayAray.getString("isPlane").toString().equals("0")){  //飞机
                            JSONObject jsonObject = JSON.parseObject(relayAray.getString("plane"));
                            entity.setArriveStation(jsonObject.getString("planesattion"));//接站站点planesattion
                            entity.setPickUpFlightNo(relayAray.getString("shift"));//接站班次arrivetime
                            String time = relayAray.getString("arrivetime");
                            String[] time1 = time.split(" ");
                            entity.setArriveDate(time1[0]);//到站日期
                            entity.setArriveTime(time1[1]);//接站时间
                        }else if(relayAray.containsKey("isHigh") && relayAray.getString("isHigh").toString().equals("0")){  //高铁
                            JSONObject jsonObject = JSON.parseObject(relayAray.getString("high"));
                            entity.setArriveStation(jsonObject.getString("highsattion"));//接站站点
                            entity.setPickUpFlightNo(relayAray.getString("shift"));//接站班次
                            String time = relayAray.getString("arrivetime");
                            String[] time1 = time.split(" ");
                            entity.setArriveDate(time1[0]);//到站日期
                            entity.setArriveTime(time1[1]);//接站时间
                        }else if(relayAray.containsKey("isOnedrive") && relayAray.getString("isOnedrive").toString().equals("0")){
                            entity.setSelfPickDriving("1");//是否自驾
                        }
                    }
                    JSONObject relayAray1 = JSON.parseObject(object.getString("send"));  //送
                    if(!StringUtils.isEmpty(relayAray1)){
                        if( relayAray1.containsKey("isPlane") && relayAray1.getString("isPlane").toString().equals("0")){  //飞机
                            JSONObject jsonObject = JSON.parseObject(relayAray1.getString("plane"));
                            entity.setDepartStation(jsonObject.getString("planesattion"));//送站站点
                            entity.setDepartFlightNo(relayAray1.getString("shift1"));//送站班次
                            String time = relayAray1.getString("arrivetime1");
                            String[] time1 = time.split(" ");

                            entity.setDepartDate(time1[0]);//到站日期
                            entity.setDepartTime(time1[1]);//接站时间
                        }else if(relayAray1.containsKey("isHigh") && relayAray1.getString("isHigh").toString().equals("0")){
                            JSONObject jsonObject = JSON.parseObject(relayAray1.getString("high"));
                            entity.setDepartStation(jsonObject.getString("highsattion"));//送站站点
                            entity.setDepartFlightNo(relayAray1.getString("shift1"));//送站班次
                            String time = relayAray1.getString("arrivetime1");
                            String[] time1 = time.split(" ");
                            entity.setDepartDate(time1[0]);//到站日期
                            entity.setDepartTime(time1[1]);//接站时间
                        }else if(relayAray1.containsKey("isOnedrive") && relayAray1.getString("isOnedrive").toString().equals("0")){
                            entity.setSelfDriving("1");//是否自驾
                        }
                    }
                }
                //是否住宿  0：是  1：不是
                Map<String,String> value = entity.getNeedHosp();
                if(object.getString("isHotel").toString().equals("0")){
                    JSONObject hotelObject =JSON.parseObject(object.getString("hotel"));
                    String startDate = hotelObject.getString("startDate");
                    String endDate = hotelObject.getString("endDate");
                    List<String> strList = new ArrayList<String>();
                    value.put(startDate,"1");
                    entity.setNoneedHosp("0");//不需要住宿单元格设置为1（否定）
                    //strList.add(startDate);
                    for(int t =1;t>=1;t++){
                        Calendar c = Calendar.getInstance();
                        c.setTime(DateUtils.parseDate(startDate));
                        c.add(Calendar.DAY_OF_MONTH, t);// 今天+1天
                        Date tomorrow = c.getTime();
//                        if(DateUtils.formatDate(tomorrow,"yyyy-MM-dd").equals(endDate)){
//                            break;
//                        }

                        if(tomorrow.compareTo(DateUtils.parseDate(endDate))>=0){
                            break;
                        }
                        String strDate = DateUtils.formatDate(tomorrow,"yyyy-MM-dd");
                        value.put(strDate,"1"); //需要住宿
                    }
                    entity.setNeedHosp(value);
                }else{
                    entity.setNoneedHosp("1");
                }
                Map<String,String> plantTour = entity.getPlanTour();
                if(object.getString("isfactory").toString().equals("0")){
                    JSONArray array = JSON.parseArray(object.getString("factory"));
                    for(int m=0;m<array.size();m++){
                        JSONObject jsonObject = array.getJSONObject(m);
                        plantTour.put(String.valueOf(jsonObject.getString("visittime"))+" "+jsonObject.getString("visitdesc"),"1"); //需要参观
                    }
                }
                entity.setPlanTour(plantTour);

                Map<String,String> caterIng = entity.getCaterIng();
                if("0".equals(object.getString("isEat").toString())){
                    JSONArray array = JSON.parseArray(object.getString("eat"));
                    for(int m=0;m<array.size();m++){
                        JSONObject jsonObject = array.getJSONObject(m);
                        caterIng.put(jsonObject.getString("eatname"),"1"); //餐饮服务
                    }
                }

                entity.setCaterIng(caterIng);

                //是否试乘车驾
                Map<String,String> testDrives = entity.getTestDrive();
                if(object.getString("isTake").toString().equals("0")){
                    JSONArray array = JSON.parseArray(object.getString("take"));
                    for(int d=0;d<array.size();d++){
                        JSONObject jsonObject = array.getJSONObject(d);
                        testDrives.put(String.valueOf(jsonObject.getString("taketime")),"1"); //需要试驾
                    }
                }
                entity.setTestDrive(testDrives);
                entity.setRemark(object.getString("desc")); //备注
//                entity.setInviteCode("CODE0001");
            }
            highlightCell(entity);
            entities.add(entity);
        }
        return entities;
    }


    //判断是否需要高亮单元格
    public static void highlightCell(VisitEntity visitEntity)
    {
        List<String> i = new ArrayList<String>();
        //判断名字
        boolean lastNameFlag = false;
        boolean firstNameFlag = false;
        boolean regoMobileFlag = false;
        String lastName = StringUtils.isEmpty(visitEntity.getLastName())?"":visitEntity.getLastName();
        String regiLastName =StringUtils.isEmpty(visitEntity.getRegiLasetName())?"":visitEntity.getRegiLasetName();
        if(!lastName.equals(regiLastName))
        {
            lastNameFlag = true;
        }
        String firstName = StringUtils.isEmpty( visitEntity.getFirstName())?"": visitEntity.getFirstName();
        String regiFirstName =StringUtils.isEmpty(  visitEntity.getRegiFirstName())?"":  visitEntity.getRegiFirstName();
        if(!firstName.equals(regiFirstName))
        {
            firstNameFlag = true;
        }
        String mobile = visitEntity.getMobile();
        if(StringUtils.isEmpty(mobile)){
            mobile="";
        }

        String regoMobile = visitEntity.getRegiMobile();
        if(StringUtils.isEmpty(regoMobile)){
            regoMobile="";
        }
        if(!mobile.equals(regoMobile))
        {
            regoMobileFlag = true;
        }
        // 1 8 2 9 7 10
        if(lastNameFlag)
        {
            i.add("1") ;
            i.add("8");
        }
        if(firstNameFlag)
        {
            i.add("2") ;
            i.add("9");
        }

        if(regoMobileFlag)
        {
            i.add("7") ;
            i.add("10");
        }
        visitEntity.setHightLightedCols(i);
    }

    //生成数据
    private void generateData(Sheet createSheet, List<VisitEntity> entities,List<String> dyCols,List<String> plantCols,List<String> testDriveCols,List<String> caterIngsCols,CellStyle cellStyle)
    {
        int dataSize = entities.size();
        for(int i=0;i<dataSize;i++)
        {	Row dataRow = createSheet.createRow(3+i);
            int dyColsSize = dyCols.size();
            int plantSize = plantCols.size();
            int testDriveSize = testDriveCols.size();
            int caterIngSize = caterIngsCols.size();
            VisitEntity visitEntity = entities.get(i);
            List<String> highLightedCells = visitEntity.getHightLightedCols();
            Map<String,String> value = visitEntity.getNeedHosp();
            Map<String,String> plant = visitEntity.getPlanTour();
            Map<String,String> testDrive = visitEntity.getTestDrive();
            Map<String,String> catering = visitEntity.getCaterIng();
            generateCell(dataRow,0,null,visitEntity.getItemNo());
            if(highLightedCells.contains("1"))
            {
                generateCell(dataRow,1,cellStyle,visitEntity.getLastName());
            }else
            {
                generateCell(dataRow,1,null,visitEntity.getLastName());
            }
            if(highLightedCells.contains("2"))
            {
                generateCell(dataRow,2,cellStyle,visitEntity.getFirstName());
            }else
            {
                generateCell(dataRow,2,null,visitEntity.getFirstName());
            }

            generateCell(dataRow,3,null,visitEntity.getGender());
            generateCell(dataRow,4,null,visitEntity.getCompany());
            generateCell(dataRow,5,null,visitEntity.getJob());
            generateCell(dataRow,6,null,visitEntity.getEmail());

            if(highLightedCells.contains("7"))
            {
                generateCell(dataRow,7,cellStyle,visitEntity.getMobile());
            }else
            {
                generateCell(dataRow,7,null,visitEntity.getMobile());
            }



            if(highLightedCells.contains("8"))
            {
                generateCell(dataRow,8,cellStyle,visitEntity.getRegiLasetName());
            }else
            {
                generateCell(dataRow,8,null,visitEntity.getRegiLasetName());
            }


            if(highLightedCells.contains("9"))
            {
                generateCell(dataRow,9,cellStyle,visitEntity.getRegiFirstName());
            }else
            {
                generateCell(dataRow,9,null,visitEntity.getRegiFirstName());
            }


            if(highLightedCells.contains("10"))
            {
                generateCell(dataRow,10,cellStyle,visitEntity.getRegiMobile());
            }else
            {
                generateCell(dataRow,10,null,visitEntity.getRegiMobile());
            }


            if(value!=null){
                //生成动态列
                for(int q=0;q<dyColsSize;q++)
                {
                    String key = dyCols.get(q);
                    String content = value.get(key);
                    generateCell(dataRow,8+q+3,null,content);
                }
            }


            generateCell(dataRow,8+3+dyColsSize,null,visitEntity.getNoneedHosp());
            generateCell(dataRow,9+3+dyColsSize,null,visitEntity.getNosmoking());

            generateCell(dataRow,10+3+dyColsSize,null,visitEntity.getPickUpFlightNo());//接机航班号
            generateCell(dataRow,11+3+dyColsSize,null,visitEntity.getArriveDate());//接机到达日期
            generateCell(dataRow,12+3+dyColsSize,null,visitEntity.getArriveTime());//接机到达时间
            generateCell(dataRow,13+3+dyColsSize,null,visitEntity.getArriveStation());//接机站点
            generateCell(dataRow,13+3+1+dyColsSize,null,visitEntity.getSelfPickDriving());//接机站点
            generateCell(dataRow,14+3+1+dyColsSize,null,visitEntity.getDepartFlightNo());//出发航班号
            generateCell(dataRow,15+3+1+dyColsSize,null,visitEntity.getDepartDate());//出发日期
            generateCell(dataRow,16+3+1+dyColsSize,null,visitEntity.getDepartTime());//出发时间
            generateCell(dataRow,17+3+1+dyColsSize,null,visitEntity.getDepartStation());//出发站点
            generateCell(dataRow,18+3+1+dyColsSize,null,visitEntity.getSelfDriving());//自驾

            if(plant!=null){
                //工厂参观
                for(int q=0;q<plantCols.size();q++)
                {
                    String key = plantCols.get(q);
                    String content = plant.get(key);
                    generateCell(dataRow,19+3+1+dyColsSize+q,null,content);
                }
            }

            if(testDrive!=null){
                for(int q=0;q<testDriveCols.size();q++)
                {
                    String key = testDriveCols.get(q);
                    String content = testDrive.get(key);
                    generateCell(dataRow,19+3+1+q+plantSize+dyColsSize,null,content);
                }
            }

            if(catering!=null){
                for(int q=0;q<caterIngsCols.size();q++)
                {
                    String key = caterIngsCols.get(q);
                    String content = catering.get(key);
                    generateCell(dataRow,19+3+1+q+plantSize+dyColsSize+testDriveSize,null,content);
                }
            }

            // 试乘试驾

            generateCell(dataRow,19+1+3+testDriveSize+plantSize+dyColsSize+caterIngSize,null,visitEntity.getRemark());


        }
    }

    public HSSFCellStyle highlightedCell(HSSFWorkbook wb)
    {
        HSSFCellStyle style = wb.createCellStyle();
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.RED.index);
        return style;
    }


    //导入
    @RequestMapping(value = "/importExcel" , method = {RequestMethod.POST,RequestMethod.GET})
    @ResponseBody
    @RequiresPermissions(value = WORKSHOP_EDIT)
    public JSONObject importExcel(HttpServletRequest request, MultipartFile file, Model model, Long id) {
        JSONObject retJson = new JSONObject();
        Long accountId = UserUtils.getmpaccounts(request);
        final String[] sort1 = {""};
        StringBuffer stringBuffer = new StringBuffer();
        if (file == null) {
            retJson.put("errorNo", "1");
            retJson.put("errorMsg", "未选择导入文件");
            return retJson;
        }
        // 构造临时路径来存储上传的文件
        String uploadPath = System.getProperty("java.io.tmpdir");
        File uploadDir = new File(uploadPath);
        if (!uploadDir.exists()) {
            uploadDir.mkdir();
        }
        String fileName = file.getOriginalFilename();
        String filePath = uploadPath + File.separator + fileName;
        File storeFile = new File(filePath);
        try {
            file.transferTo(storeFile);


            ExcelKit.$Import().setEmptyCellValue(null).readExcel(storeFile, rowData -> {

                Workshop workshop = new Workshop();
                Conds conds = new Conds();
                if(com.cftech.core.util.StringUtils.isBlank(rowData.get(7))){
                    return;
                }
                conds.equal("w.phone",rowData.get(7));
                conds.equal("w.wsid",id);
                conds.equal("w.del_flag",0);
                Workshop work = workshopService.fetchSearchByConds(conds);
                if(work==null){  //如果没导入过
                    Conds condssort = new Conds();
                    if(com.cftech.core.util.StringUtils.isBlank(rowData.get(0))){
                        return;
                    }
                    condssort.equal("sort",Long.valueOf(rowData.get(0)));
                    condssort.equal("wsid",id);
                    condssort.equal("del_flag",0);
                    Workshop workshopsort = workshopService.fetchSearchByConds(condssort);
                    if(workshopsort!=null){  //序号重复
                        if(StringUtils.isEmpty(rowData.get(4))){
                            workshopsort.setOrganization(null);
                        }else{
                            workshopsort.setOrganization(rowData.get(4));
                        }
                        if(StringUtils.isEmpty(rowData.get(8))){
                            workshopsort.setOrganization_en(null);
                        }else{
                            workshopsort.setOrganization_en(rowData.get(8));
                        }
                        if(StringUtils.isEmpty(rowData.get(10))){
                            workshopsort.setName_en(null);
                        }else{
                            workshopsort.setName_en(rowData.get(10));
                        }
                        if(StringUtils.isEmpty(rowData.get(11))){
                            workshopsort.setTable_number(null);
                        }else{
                            workshopsort.setTable_number(rowData.get(11));
                        }
                        if(StringUtils.isEmpty(rowData.get(12))){
                            workshopsort.setFactory_number(null);
                        }else{
                            workshopsort.setFactory_number(rowData.get(12));
                        }
                       if(StringUtils.isEmpty(rowData.get(13))){
                           workshopsort.setDrive_number(null);
                       }else{
                           workshopsort.setDrive_number(rowData.get(13));
                       }
                        workshopService.update(workshopsort);
                        return;
                    }
                    workshop.setAccountsId(accountId);
                    workshop.setStatus("0");
                    workshop.setDelFlag(false);
                    workshop.setCreateBy(UserUtils.getUser().getId());
                    workshop.setUpdateBy(UserUtils.getUser().getId());
                    workshop.setSort(Long.valueOf(rowData.get(0)));
                    workshop.setSurname(rowData.get(1)); //姓
                    workshop.setFirst_name(rowData.get(2));//研讨会名
                    workshop.setGender(rowData.get(3)); //性别
                    workshop.setOrganization(rowData.get(4)); //公司
                    workshop.setPosition(rowData.get(5)); //职位
                    workshop.setEmail(rowData.get(6)); //邮箱
                    workshop.setPhone(rowData.get(7));//手机号码
                    workshop.setWsid(Long.valueOf(id)); // 研讨会ID

                    String val = "";
                    // 用字符数组的方式随机
                    String model1 = "23456789ABCDEFGHJKMNPQRSTUVWXYZ";
                    char[] m = model1.toCharArray();
                    for (int j = 0; j < 6; j++) {
                        char c = m[(int) (Math.random() * 31)];
                        // 保证六位随机数之间没有重复的
                        if (val.contains(String.valueOf(c))) {
                            j--;
                            continue;
                        }
                        val = val + c;
                    }

                    //判断随机数是否重复
                    Conds condws = new Conds();
                    String s = val.toLowerCase();
                    condws.equal("invitcode",s);
                    condws.equal("del_flag",0);
                    Sort sortws = new Sort("create_time",OrderType.DESC);
                    List<Workshop> listws = workshopService.selectworkshop(condws,sortws,0,0);
                    if(listws!=null&&listws.size()>0){ //已重复
                        return;
                    }else{
                        workshop.setInvitcode(s);
                    }
                    workshop.setOrganization_en(rowData.get(8));
                    workshop.setPeopletype(rowData.get(9));
                    workshop.setName_en(rowData.get(10));
                    workshop.setTable_number(rowData.get(11));
                    workshop.setFactory_number(rowData.get(12));
                    workshop.setDrive_number(rowData.get(13));
                    workshopService.save(workshop);
                }else{
                    return;
                }
            });
        } catch (IOException e) {
//            e.printStackTrace();
            log.error(e.getMessage());
        }
        retJson.put("errorNo", "0");
        if(sort1[0] == ""){
            retJson.put("errorMsg","导入成功");
        }else{
            retJson.put("errorMsg","导入成功,"+sort1[0]+"已过滤以上序号重复的人员");
        }

        return retJson;
    }

    //维护英文名字
    @ResponseBody
    @RequestMapping(value = "/updateconpany",method = RequestMethod.POST)
    public JSONObject updateconpany(String conpany,String id){
        JSONObject jsonObject = new JSONObject();
       try {
           if(!StringUtils.isEmpty(conpany)&&!StringUtils.isEmpty(id)){
               Workshop workshop = workshopService.fetchById(id);
               if(workshop!=null){
                   workshop.setName_en(conpany);
                   workshopService.update(workshop);
               }
           }
       }catch (Exception e){
           e.printStackTrace();
       }
        return  jsonObject;
    }
    @ResponseBody
    @RequestMapping(value = "/updateconpanys",method = RequestMethod.POST)
    public JSONObject updateconpanys(String id,Model model){
        JSONObject jsonObject = new JSONObject();
        try {
            if(!StringUtils.isEmpty(id)){
                Workshop workshop = workshopService.fetchById(id);
               if(workshop!=null){
                   jsonObject.put("name",workshop.getName_en());
               }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return  jsonObject;
    }

    //维护桌号
    @ResponseBody
    @RequestMapping(value = "/updatetablenumber",method = RequestMethod.POST)
    public JSONObject updatetablenumber(String table_number,String id){
        JSONObject jsonObject = new JSONObject();
        try {
            if(!StringUtils.isEmpty(table_number)&&!StringUtils.isEmpty(id)){
                Workshop workshop = workshopService.fetchById(id);
                if(workshop!=null){
                    workshop.setTable_number(table_number);
                    workshopService.update(workshop);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return  jsonObject;
    }
    @ResponseBody
    @RequestMapping(value = "/updatetablenumbers",method = RequestMethod.POST)
    public JSONObject updatetablenumbers(String id){
        JSONObject jsonObject = new JSONObject();
        try {
            if(!StringUtils.isEmpty(id)){
                Workshop workshop = workshopService.fetchById(id);
                if(workshop!=null){
                    jsonObject.put("table",workshop.getTable_number());
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return  jsonObject;
    }
    //维护工厂参观分组
    @ResponseBody
    @RequestMapping(value = "/updatefactorynumber",method = RequestMethod.POST)
    public JSONObject updatefactorynumber(String factory_number,String id){
        JSONObject jsonObject = new JSONObject();
        try {
            if(!StringUtils.isEmpty(factory_number)&&!StringUtils.isEmpty(id)){
                Workshop workshop = workshopService.fetchById(id);
                if(workshop!=null){
                    workshop.setFactory_number(factory_number);
                    workshopService.update(workshop);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return  jsonObject;
    }
    @ResponseBody
    @RequestMapping(value = "/updatefactorynumbers",method = RequestMethod.POST)
    public JSONObject updatefactorynumbers(String id){
        JSONObject jsonObject = new JSONObject();
        try {
            if(!StringUtils.isEmpty(id)){
                Workshop workshop = workshopService.fetchById(id);
                if(workshop!=null){
                   jsonObject.put("factory",workshop.getFactory_number());
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return  jsonObject;
    }
    //维护试乘试驾体验
    @ResponseBody
    @RequestMapping(value = "/updatedrivenumber",method = RequestMethod.POST)
    public JSONObject updatedrivenumber(String drive_number,String id){
        JSONObject jsonObject = new JSONObject();
        try {
            if(!StringUtils.isEmpty(drive_number)&&!StringUtils.isEmpty(id)){
                Workshop workshop = workshopService.fetchById(id);
                if(workshop!=null){
                    workshop.setDrive_number(drive_number);
                    workshopService.update(workshop);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return  jsonObject;
    }
    @ResponseBody
    @RequestMapping(value = "/updatedrivenumbers",method = RequestMethod.POST)
    public JSONObject updatedrivenumbers(String id){
        JSONObject jsonObject = new JSONObject();
        try {
            if(!StringUtils.isEmpty(id)){
                Workshop workshop = workshopService.fetchById(id);
                if(workshop!=null){
                   jsonObject.put("drive",workshop.getDrive_number());
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return  jsonObject;
    }

    //内部员工
    @ResponseBody
    @RequestMapping(value = "/sendMessage",method = RequestMethod.POST)
    public JSONObject sendMessage(Long meetingId){
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("errorNo",0);
        Conds conds = new Conds();
        conds.equal("w.wsid",meetingId);
        conds.equal("w.del_flag",0);
        conds.equal("w.peopletype",SystemConfig.p.getProperty("empname"));
        Sort sort = new Sort("w.id",OrderType.ASC);
        Meeting meeting  = meetingService.fetchById(meetingId);
        StringBuffer msg = new StringBuffer();
        try {
            List<Workshop> workshops = workshopService.fetchSearchByPage(conds,sort,0,0);
            for(Workshop workshop : workshops){
                if(com.cftech.core.util.StringUtils.isNotBlank(workshop.getDescription()) && Long.valueOf(workshop.getDescription())>0){
                    continue;
                }
                String content = SystemConfig.p.getProperty("empSendMessage");
                content=content.replace("{{USERNAME}}",workshop.getSurname()+workshop.getFirst_name()).replace("{{ACTIVITYNAME}}",meeting.getTitle()).replace("{{CODE}}",workshop.getInvitcode());
                String result = SMSSender.readContentFromPost(workshop.getPhone(),content);
                workshop.setDescription(result);
                workshopService.update(workshop);
            }
        }catch (Exception e){
            jsonObject.put("errorNo",1);
            e.printStackTrace();
        }
        return  jsonObject;
    }

    @ResponseBody
    @RequestMapping(value = "/detailInfoSyn",method = RequestMethod.GET)
    public JSONObject detailInfoSyn(String olddesc,String newdesc){
        JSONObject rtnJson = new JSONObject();
        Conds conds = new Conds();
        conds.notNull("detailsinfo");
        List<Details> details = detailsService.fetchSearchByPage(conds,null,0,0);
        int count = 0;
        for(Details d:details){
            String detailss = HtmlUtils.htmlUnescape(d.getDetailsinfo());  //会员详情信息
            JSONObject object = JSONObject.parseObject(detailss);
            if(object.getString("isfactory").toString().equals("0")){
                JSONArray array = JSON.parseArray(object.getString("factory"));
                for(int m=0;m<array.size();m++){
                    JSONObject jsonObject = array.getJSONObject(m);
                    String temp = jsonObject.getString("visitdesc");
                    if(temp.equals(olddesc)){
                        count++;
                        log.info(count+"=========="+object.toJSONString());
                        jsonObject.put("visitdesc",newdesc);
                        object.put("factory",array.toArray());
                        d.setDetailsinfo(object.toString());

                        detailsService.update(d);
                    }
                }


            }
        }
        rtnJson.put("errorNo",0);
        return rtnJson;
    }





}
