package com.bcxin.ins.coninsweb.order.controller;

import com.alibaba.fastjson.JSONObject;
import com.bcxin.ins.service.order.PolicyService;
import com.bcxin.ins.service.product.InsProductRuleXWCKAPIService;
import com.bcxin.ins.service.product.ProductService;
import com.bcxin.ins.rest.UserSupportUtil;
import com.bcxin.ins.core.base.web.BaseController;
import com.bcxin.ins.util.DateUtil;
import com.bcxin.ins.util.MyConverUtil;
import com.bcxin.ins.util.enums.IdType;
import com.bcxin.ins.util.enums.NatureLinkage;
import com.bcxin.ins.vo.*;
import com.bcxin.mybatisplus.toolkit.StringUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 订单投保过程其它业务处理控制器
 * 如：个别产品投保页面需要文件上传，或者需要联动查询等
 * @author zxf
 *
 */
@Controller
@RequestMapping("/order-handle-util")
public class OrderHandleUtilController extends BaseController {

	@Autowired
	private ProductService productService;
	@Autowired
	private PolicyService policyService;
	@Autowired
	private InsProductRuleXWCKAPIService insProductRuleXWCKAPIService;

	/**
	 * <b> 诉讼在用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 11:21
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping("/delete-ln-accused-by-oid")
	@ResponseBody
	public ResultDto deleteLnAccusedByOid(HttpServletRequest request) {
		String lna_id = request.getParameter("lna_id");
		if(StringUtils.isEmpty(lna_id)){
			return new ResultDto("数据丢失，删除失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
		if(policyService.deleteLnAccusedByOid(lna_id)){
			return new ResultDto("删除成功！", ConstProp.CODE_SUCCESS,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}else{
			return new ResultDto("运行时异常，删除失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	/**
	 * <b> 完成每笔支付跳转支付完成页面,小微出口在用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 11:19
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping("/get-rule-xwck-vo/{product_id}/{city_code}")
	@ResponseBody
	public String getRuleXWCKVo(@PathVariable String product_id,@PathVariable String city_code){
		RuleXWCKVo vo = insProductRuleXWCKAPIService.getRuleXWCKVoByProductIDAndCity(product_id, city_code);
		if(vo != null){
			//转String类型的map
			try {
				return JSONObject.toJSONString(vo);
			} catch (Exception e) {
				return ConstProp.BLANK_CHAR;
			}
		}
		return ConstProp.BLANK_CHAR;
	}

	/**
	 * <b> 计算生效日期失效日期，目前投保保证险在用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 11:09
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping("/request-plan-end-time")
	@ResponseBody
	public ResultDto requestPlanEndTime(HttpServletRequest request) throws Exception {
		String bidtime = request.getParameter("bidtime");
		String bidPeriod = request.getParameter("bidPeriod");
		String productCode = request.getParameter("productCode");
		if (StringUtils.isEmpty(productCode)||StringUtils.isEmpty(bidtime)||StringUtils.isEmpty(bidPeriod)) {
			return new ResultDto(ConstProp.BLANK_CHAR, "301", ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
		//2.计算投标日期+投标有效期+28天后的日期
		int bpd = Integer.parseInt(bidPeriod) + 28;
		Date planTimeLast = DateUtil.convertStringToDate(DateUtil.dateAdd(3,bidtime,Integer.parseInt(bidPeriod)));//不加28天
		Date planTimeNow = DateUtil.convertStringToDate(DateUtil.dateAdd(3,bidtime,bpd));//加28天
		if(productCode.contains("BZX-TB")){//太保规则
			//1.计算一年后的时间
			Date planTime = DateUtil.convertStringToDate(DateUtil.dateAdd(1,bidtime,1));
			//3.比较planTimeLast的日期是否大于planTime，是就超出一年期限
			if (DateUtil.calculateTwoDateDvalue(planTime,planTimeLast)<0) {
				return new ResultDto("保险期间大于一年，请重新设置投标有效期！", ConstProp.CODE_FAILURE, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
			}
			if (DateUtil.calculateTwoDateDvalue(planTime,planTimeNow)<0) {//如果按+28天的日期来比较超出1年的限制就直接按一年算
				planTimeNow = DateUtil.convertStringToDate(DateUtil.dateAdd(3,DateUtil.dateAdd(1,bidtime,1),-1));
			}
		}else if(productCode.contains("BZX-YG")){//阳光规则
			//1.计算一年后的时间
//                Date planTime = DateUtil.convertStringToDate(DateUtil.dateAdd(3,DateUtil.dateAdd(1,bidtime,1),-1));
			Date planTime = DateUtil.convertStringToDate(DateUtil.dateAdd(1,bidtime,1));
			//3.比较planTimeLast的日期是否大于planTime，是就超出一年期限
			if (DateUtil.calculateTwoDateDvalue(planTime,planTimeLast)<0) {
				return new ResultDto("保险期间大于一年，请重新设置投标有效期！", ConstProp.CODE_FAILURE, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
			}
			if (DateUtil.calculateTwoDateDvalue(planTime,planTimeNow)<0) {//如果按+28天的日期来比较超出1年的限制就直接按一年算
				planTimeNow = DateUtil.convertStringToDate(DateUtil.dateAdd(3,DateUtil.dateAdd(1,bidtime,1),-1));
			}
		}else if(productCode.contains("BZX-DB")){//都邦规则
			//1.计算三个月后的时间
			Date planTime = DateUtil.convertStringToDate(DateUtil.dateAdd(2,bidtime,3));
			//3.比较planTimeLast的日期是否大于planTime，是就超出三个月期限
			if (DateUtil.calculateTwoDateDvalue(planTime,planTimeLast)<0) {
				return new ResultDto("保险期间不能大于三个月，请重新设置投标有效期！", ConstProp.CODE_FAILURE, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
			}
			if (DateUtil.calculateTwoDateDvalue(planTime,planTimeNow)<0) {//如果按+28天的日期来比较超出1年的限制就直接按一年算
				planTimeNow = planTime;
			}
		}
		if (planTimeNow != null) {
			String plannedEnd = DateUtil.convertDateToString(planTimeNow, DateUtil.FORMAT2);
			return new ResultDto(plannedEnd, ConstProp.CODE_SUCCESS, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		} else {
			return new ResultDto("获取失效日期失败", ConstProp.CODE_FAILURE, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	/**
	 * 通过pss的订单id去调用pss提供的查询活动详细信息接口获取活动相关及发票相关BLBCommunicationDto,目前公责险在用
	 * @param thr_order_id pss的订单id
	 * @return BLBCommunicationDto的jsonToString
	 */
	@RequestMapping("/get-activity-by-pss/{thr_order_id}")
	@ResponseBody
	public String getActivityByPss(@PathVariable String thr_order_id) {
		String result = ConstProp.BLANK_CHAR;
		ClientUserVo userdetail = UserSupportUtil.getSessionUser();
		if(userdetail == null){
			return result;
		}
		try {
			BLBCommunicationDto dto = policyService.getBLBCommunicationDtoByOrderID_PSSAPI(thr_order_id, userdetail.getWeb_type());
			if(dto != null){
				result = JSONObject.toJSONString(dto);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}

	/**
	 * <b> 联动查询营业性质，目前团意险、雇主责任险共用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 10:59
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping(value = "/natureChange")
	@ResponseBody
	public Map<String, String> natureChange(HttpServletRequest request) {
		String nID = request.getParameter("nID");
		if(StringUtils.isNotEmpty(nID)){
			Map<String, String> map = NatureLinkage.getMapByProvince(nID);
			return map;
		}
		return null;
	}

	@RequestMapping("/session-info")
	@ResponseBody
	public ResultDto sessionInfo(HttpServletRequest reqeust, ModelMap model) {
		String name = reqeust.getParameter("name");
		String base64 = reqeust.getParameter("base64");
		model.addAttribute(name, base64);
		return new ResultDto("", ConstProp.CODE_SUCCESS, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
	}

	/**
	 * <b> 删除列表中的某个人，目前由团意险投保页调用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 10:53
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping("/delete-role-in-policy-by-oid")
	@ResponseBody
	public ResultDto deleteRoleInPolicyByOid(HttpServletRequest request) {
		String role_id = request.getParameter("role_id");
		if(StringUtils.isEmpty(role_id)){
			return new ResultDto("数据丢失，删除失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
		if(policyService.deleteRoleInPolicyByOid(role_id)){
			return new ResultDto("删除成功！", ConstProp.CODE_SUCCESS,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}else{
			return new ResultDto("运行时异常，删除失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	/**
	 * <b> 删除已存在的人员列表信息，目前由团意险投保页调用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 10:54
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping("/delect-all-ins-role")
	@ResponseBody
	public ResultDto delectAllInsRole(HttpServletRequest request) {
		String order_id = request.getParameter("order_id");
		if(StringUtils.isEmpty(order_id)){
			return new ResultDto("数据丢失，请刷新后重试！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
		if(policyService.delectAllInsRole(order_id)){
			return new ResultDto("删除成功！", ConstProp.CODE_SUCCESS,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}else{
			return new ResultDto("运行时异常，请刷新后重试！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	/**
	 * excel文件上传并解析,批量导入人员，增员的导入功能，目前由团意险投保页调用
	 * @param request
	 * @return
	 * @throws IOException
	 */
	@RequestMapping("/gzx-upload-role-file")
	@ResponseBody
	public ResultDto uploadRoleFile(MultipartHttpServletRequest request) throws Exception {
		MultipartFile roleFile = request.getFile("role_emp");
		if(roleFile != null){
			return uploadRoleFile(roleFile);
		}else{
			return new ResultDto("文件上传失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}

	}

	/**
	 * 人员导入加载到投保页面
	 * @return
	 */
	@RequestMapping("/role_upload_list")
	public ModelAndView role_upload_list(){
		ModelAndView view = new ModelAndView("/coninsweb/api/gmr/policy/role_upload_list");
		view.addObject("rsVoList",getRoleSubjectValueSession());
		view.addObject("personTypeList", IdType.personList());
		return view;
	}

	/**
	 * <b> 删除列表中的某个人，目前由雇主责任险投保页调用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 10:53
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping("/delete-hireling-by-oid")
	@ResponseBody
	public ResultDto deleteHirelingByOid(HttpServletRequest request) {
		String hire_id = request.getParameter("hire_id");
		if(StringUtils.isEmpty(hire_id)){
			return new ResultDto("数据丢失，删除失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
		if(policyService.deleteHirelingByOid(hire_id)){
			return new ResultDto("删除成功！", ConstProp.CODE_SUCCESS,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}else{
			return new ResultDto("运行时异常，删除失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	/**
	 * <b> 删除已存在的人员列表信息，目前由雇主责任险投保页调用 </b>
	 * @author ZXF
	 * @create 2018/10/08 0008 10:54
	 * @version
	 * @注意事项 </b>
	 */
	@RequestMapping("/delect-all-hireling")
	@ResponseBody
	public ResultDto delectAllHireling(HttpServletRequest request) {
		String order_id = request.getParameter("order_id");
		if(StringUtils.isEmpty(order_id)){
			return new ResultDto("数据丢失，请刷新后重试！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
		if(policyService.delectAllHireling(order_id)){
			return new ResultDto("删除成功！", ConstProp.CODE_SUCCESS,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}else{
			return new ResultDto("运行时异常，请刷新后重试！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	/**
	 * excel文件上传并解析,批量导入人员，增员的导入功能
	 * @param request
	 * @return
	 * @throws IOException
	 */
	@RequestMapping("/gzx-upload-hireling-file")
	@ResponseBody
	public ResultDto gzxUploadHirelingFile(MultipartHttpServletRequest request) throws Exception {
		MultipartFile hireFile = request.getFile("hire_emp");
		if(hireFile != null){
			return uploadHirelingFile(hireFile);
		}else{
			return new ResultDto("文件上传失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}

	}

	/**
	 * 人员导入加载到投保页面
	 * @return
	 */
	@RequestMapping("/hire_upload_list")
	public ModelAndView hire_upload_list(){
		ModelAndView view = new ModelAndView("/coninsweb/api/gzzrx/policy/hire_upload_list");
		view.addObject("rsVoList",getHirelingValueSession());
		view.addObject("personTypeList", IdType.personList());
		return view;
	}

	private boolean setRoleSubjectValueSession(List<RoleSubjectVo> list) {
		HttpServletRequest request = null;
		if (RequestContextHolder.getRequestAttributes() != null) {
			request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
			HttpSession session = request.getSession();
			session.setMaxInactiveInterval(60*30);
			session.setAttribute("rsVoList",list);
			return true;
		}else{
			return false;
		}
	}

	private List<RoleSubjectVo> getRoleSubjectValueSession() {
		HttpServletRequest request = null;
		List<RoleSubjectVo> list = null;
		if (RequestContextHolder.getRequestAttributes() != null) {
			request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
			HttpSession session = request.getSession();
			list = (List<RoleSubjectVo>)session.getAttribute("rsVoList");
			session.removeAttribute("rsVoList");
		}
		return list;
	}

	/**
	 * excel人员导入处理
	 * @param mFile MultipartFile上传文件
	 * @return
	 * @throws Exception
	 */
	private ResultDto uploadRoleFile(MultipartFile mFile) throws Exception {
		List<RoleSubjectVo> list = null;
		String[] arrStr = {"name_cn","id_type","organization_code","birth_date","sex","mobile","major_group"};
		// Read the Sheet
		list = Lists.newArrayList();
		String msg = "";
		int cum = 0;
		String cds = "";
		try {
			InputStream is = mFile.getInputStream();
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
				RoleSubjectVo rsVo = null;
				// Read the Row
				for (int rowNum = 6; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);

					if (hssfRow == null) {
						continue;
					}
					HSSFCell cell = null;
					rsVo = new RoleSubjectVo();
					Map<String,String> map = Maps.newHashMap();
					String cd = "";
					for(int i=1;i<8;i++){
						cell = hssfRow.getCell(i);
						String _value = getValue(cell).trim();
						if(StringUtils.isEmpty(_value)){
							continue;
						}
						if("name_cn".equals(arrStr[i-1])){
							if(StringUtils.isNotEmpty(_value)){
								_value = _value.replace(" ","");
							}
						}else if("id_type".equals(arrStr[i-1])){
							if("居民身份证".equals(_value)){
								_value = "0";
							}else if("护照".equals(_value)){
								_value = "3";
							}else if("台胞证".equals(_value)){
								_value = "4";
							}else if("回乡证".equals(_value)){
								_value = "5";
							}
						}else if("sex".equals(arrStr[i-1])){
							if("男".equals(_value)){
								_value = "1";
							}else if("女".equals(_value)){
								_value = "2";
							}
						}else if("birth_date".equals(arrStr[i-1])){
							try {
								if(_value.contains(ConstProp.MINUS)){
									DateUtil.parseDate(_value);
								}else{
									if(HSSFDateUtil.isCellDateFormatted(cell)){
										Date d = cell.getDateCellValue();
										DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
										_value = formater.format(d);
									}
								}
							}catch (Exception e){
								msg = msg+ "证件号：#CD#,出生日期有误;";
								cum = 1;
								break;
							}
						}else if("organization_code".equals(arrStr[i-1])){
							_value = _value.toUpperCase();
							cd = _value;
						}else if("mobile".equals(arrStr[i-1])){
							if(_value.contains("E")){
								try {
									DecimalFormat df = new DecimalFormat(ConstProp.POUND_SIGN);
									_value = df.format(cell.getNumericCellValue());
								}catch (Exception e){
									msg = msg+ "证件号：#CD#,手机号码有误;";
									cum = 1;
									break;
								}
							}
						}
						map.put(arrStr[i-1],_value);
					}
					msg = msg.replace("#CD#",cd);
					if(StringUtils.isNotEmpty(cd)&&cds.contains(cd)){
						msg = msg+ "证件号："+cd+",该证件号已在本清单中，请勿重复录入;";
						cum = 1;
					}
					cds = cds + cd + ConstProp.SEMICOLON;
					if(rsVo != null&&map.size()>0){
						MyConverUtil.map2PO(map, rsVo);
						list.add(rsVo);
						rsVo = null;
					}
				}
			}
			if(cum == 1){
				return new ResultDto(msg, ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
			}
			if(list!=null && !list.isEmpty()){
				if(list.size()>2001||list.size()<3){
					return new ResultDto("请控制导入人数在3~2001人以内！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
				}
				if(setRoleSubjectValueSession(list)){
					return new ResultDto("文件解析成功！", ConstProp.CODE_SUCCESS,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
				}else{
					return new ResultDto("数据缓存失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
				}
			}else{
				return new ResultDto("未解析到可用数据！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
			}
		} catch (Exception e){
			return new ResultDto("Excel清单模板文件数据解析失败，请使用标准的Excel清单模板上传！\n" +
					"可能原因：\n1.模板文件头部内容被删除或整体样式被改变；\n2.直接它处复制的人员信息附带格式。", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	private boolean setHirelingValueSession(List<HirelingVo> list) {
		HttpServletRequest request = null;
		if (RequestContextHolder.getRequestAttributes() != null) {
			request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
			HttpSession session = request.getSession();
			session.setMaxInactiveInterval(60*30);
			session.setAttribute("rsVoList",list);
			return true;
		}else{
			return false;
		}
	}

	private List<HirelingVo> getHirelingValueSession() {
		HttpServletRequest request = null;
		List<HirelingVo> list = null;
		if (RequestContextHolder.getRequestAttributes() != null) {
			request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
			HttpSession session = request.getSession();
			list = (List<HirelingVo>)session.getAttribute("rsVoList");
			session.removeAttribute("rsVoList");
		}
		return list;
	}

	/**
	 * excel人员导入处理
	 * @param mFile MultipartFile上传文件
	 * @return
	 * @throws Exception
	 */
	private ResultDto uploadHirelingFile(MultipartFile mFile) throws Exception {
		List<HirelingVo> list = null;
		String[] arrStr = {"name","id_type","id_card","birth_date","sex","mobile","career"};
		// Read the Sheet
		list = Lists.newArrayList();
		String msg = "";
		int cum = 0;
		String cds = "";
		try {
			InputStream is = mFile.getInputStream();
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
				HirelingVo rsVo = null;
				// Read the Row
				for (int rowNum = 6; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);

					if (hssfRow == null) {
						continue;
					}
					HSSFCell cell = null;
					rsVo = new HirelingVo();
					Map<String,String> map = Maps.newHashMap();
					String cd = "";
					for(int i=1;i<8;i++){
						cell = hssfRow.getCell(i);
						String _value = getValue(cell).trim();
						if(StringUtils.isEmpty(_value)){
							continue;
						}
						if("id_type".equals(arrStr[i-1])){
							if("居民身份证".equals(_value)){
								_value = "0";
							}else if("护照".equals(_value)){
								_value = "3";
							}else if("台胞证".equals(_value)){
								_value = "4";
							}else if("回乡证".equals(_value)){
								_value = "5";
							}
						}else if("sex".equals(arrStr[i-1])){
							if("男".equals(_value)){
								_value = "1";
							}else if("女".equals(_value)){
								_value = "2";
							}
						}else if("birth_date".equals(arrStr[i-1])){
							try {
								if(_value.contains(ConstProp.MINUS)){
									DateUtil.parseDate(_value);
								}else{
									if(HSSFDateUtil.isCellDateFormatted(cell)){
										Date d = cell.getDateCellValue();
										DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
										_value = formater.format(d);
									}
								}
							}catch (Exception e){
								msg = msg+ "证件号：#CD#,出生日期有误;";
								cum = 1;
								break;
							}
						}else if("id_card".equals(arrStr[i-1])){
							_value = _value.toUpperCase();
							cd = _value;
						}else if("mobile".equals(arrStr[i-1])){
							if(_value.contains("E")){
								try {
									DecimalFormat df = new DecimalFormat(ConstProp.POUND_SIGN);
									_value = df.format(cell.getNumericCellValue());
								}catch (Exception e){
									msg = msg+ "证件号：#CD#,手机号码有误;";
									cum = 1;
									break;
								}
							}
							if(_value.length()!=11){
								msg = msg+ "证件号：#CD#,手机号码有误;";
								cum = 1;
								break;
							}
						}
						map.put(arrStr[i-1],_value);
					}
					msg = msg.replace("#CD#",cd);
					if(StringUtils.isNotEmpty(cd)&&cds.contains(cd)){
						msg = msg+ "证件号："+cd+",该证件号已在本清单中，请勿重复录入;";
						cum = 1;
					}
					cds = cds + cd + ConstProp.SEMICOLON;
					if(rsVo != null&&map.size()>0){
						MyConverUtil.map2PO(map, rsVo);
						list.add(rsVo);
						rsVo = null;
					}
				}
			}
			if(cum == 1){
				return new ResultDto(msg, ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
			}
			if(list!=null && !list.isEmpty()){
				if(list.size()>2001||list.size()<3){
					return new ResultDto("请控制导入人数在3~2001人以内！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
				}
				if(setHirelingValueSession(list)){
					return new ResultDto("文件解析成功！", ConstProp.CODE_SUCCESS,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
				}else{
					return new ResultDto("数据缓存失败！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
				}
			}else{
				return new ResultDto("未解析到可用数据！", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
			}
		} catch (Exception e){
			return new ResultDto("Excel清单模板文件数据解析失败，请使用标准的Excel清单模板上传！\n" +
					"可能原因：\n1.模板文件头部内容被删除或整体样式被改变；\n2.直接它处复制的人员信息附带格式。", ConstProp.CODE_FAILURE,ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR, ConstProp.BLANK_CHAR);
		}
	}

	private static String getValue(HSSFCell hssfRow) {
		if (hssfRow.getCellType() == hssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfRow.getBooleanCellValue());
		} else if (hssfRow.getCellType() == hssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfRow.getNumericCellValue());
		} else {
			return String.valueOf(hssfRow.getStringCellValue());
		}
	}
}
