CrmController.java

package cn.timer.api.controller.crm;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.timer.api.bean.clazz.ClientSourceClass;
import cn.timer.api.bean.clazz.ClientTypeClass;
import cn.timer.api.bean.clazz.IndustryClass;
import cn.timer.api.bean.clazz.SysRegion;
import cn.timer.api.bean.crm.CrmBusinessGroup;
import cn.timer.api.bean.crm.CrmBusinessGroupMember;
import cn.timer.api.bean.crm.CrmClientAssociate;
import cn.timer.api.bean.crm.CrmClientContacts;
import cn.timer.api.bean.crm.CrmClientData;
import cn.timer.api.bean.crm.CrmClientFollow;
import cn.timer.api.bean.crm.CrmSeaRule;
import cn.timer.api.bean.qyzx.QyzxEmpEntAsso;
import cn.timer.api.bean.yggl.YgglMainEmp;
import cn.timer.api.config.annotation.CurrentUser;
import cn.timer.api.config.annotation.UserBean;
import cn.timer.api.config.enums.SysRoleType;
import cn.timer.api.dao.crm.CrmClientContactsMapper;
import cn.timer.api.dao.crm.CrmClientDataMapper;
import cn.timer.api.dto.crm.CrmCartogramDto;
import cn.timer.api.dto.crm.CrmClientDataImportVo;
import cn.timer.api.utils.CheckUtil;
import cn.timer.api.utils.Result;
import cn.timer.api.utils.ResultUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

/**
 * Title: CrmController.java
 * 
 * @Description:
 * @author dsc
 * @date 2020年6月5日
 * @version 1.0
 */

@RestController
@Api(tags = "6.1客户管理")
@Transactional
@RequestMapping(value = "/crm", produces = { "application/json" })
public class CrmController {

//	@Value(value = "${config-8timer.crm-excel.realPath}")
//	private String realPath;

	@Autowired
	private CrmClientDataMapper crmClientDataMapper;

	@Autowired
	private CrmClientContactsMapper crmClientContactsMapper;

	private Integer getEmpNum(UserBean userBean) {
		return userBean.getEmpNum();
	}

	private Integer getOrgCode(UserBean userBean) {
		return userBean.getOrgCode();
	}

	private YgglMainEmp getYg(Integer orgCode, Integer empNum) {
		return YgglMainEmp.builder().build()
				.selectOne(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getEmpNum, empNum)
						.eq(YgglMainEmp::getOrgCode, orgCode).select(YgglMainEmp::getName, YgglMainEmp::getHeadUrl));
	}

	private String getEmpName(Integer orgCode, Integer empNum) {
		return getYg(orgCode, empNum).getName();
	}

//	private Integer getQyzxManager(Integer orgCode, Integer empNum) {
//		return YgglMainEmp.builder().build()
//				.selectOne(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getOrgCode, orgCode)
//						.eq(YgglMainEmp::getEmpNum, empNum).select(YgglMainEmp::getIsManager))
//				.getIsManager();
//	}

	@GetMapping("getIndustry")
	@ApiOperation(value = "获取行业列表", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<IndustryClass>> getIndustry(@CurrentUser UserBean userBean) {
		return ResultUtil.data(IndustryClass.builder().build().selectAll());

	}

	@GetMapping("getClientType")
	@ApiOperation(value = "获取客户类型", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<ClientTypeClass>> getClientType(@CurrentUser UserBean userBean) {
		return ResultUtil.data(ClientTypeClass.builder().build().selectAll());

	}

	@GetMapping("getClientSource")
	@ApiOperation(value = "获取客户来源", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<ClientSourceClass>> getClientSource(@CurrentUser UserBean userBean) {
		return ResultUtil.data(ClientSourceClass.builder().build().selectAll());
	}

	@PostMapping("addBusinessGroup")
	@Transactional
	@ApiOperation(value = "新增/编辑业务组", httpMethod = "POST", notes = "接口发布说明")
	public Result<Object> addBusinessGroup(@CurrentUser UserBean userBean,
			@RequestBody CrmBusinessGroup crmBusinessGroup) {
		Integer orgCode = getOrgCode(userBean);
		Integer empNum = getEmpNum(userBean);
		CrmSeaRule crmSeaRule = crmBusinessGroup.getCrmSeaRules();
		Integer one = crmSeaRule.getOne();
		Integer two = crmSeaRule.getTwo();
		Integer three = crmSeaRule.getThree();
		Integer four = crmSeaRule.getFour();
		if ((one != null && one < 1) || (two != null && two < 1) || (three != null && three < 1)
				|| (four != null && four < 1))
			return ResultUtil.error("请输入正确的规则天数");
		if (three <= one)
			return ResultUtil.error("该规则天数必须大于规则1的天数");

		Integer gid = crmBusinessGroup.getId();
		crmBusinessGroup.setModifyUser(empNum); // 修改人
		if (gid == null) {
			CrmBusinessGroup crmBusinessGroupOld = CrmBusinessGroup.builder().build()
					.selectOne(new QueryWrapper<CrmBusinessGroup>().lambda().eq(CrmBusinessGroup::getOrgCode, orgCode)
							.select(CrmBusinessGroup::getSort).last("limit 1").orderByDesc(CrmBusinessGroup::getId));
			if (crmBusinessGroupOld == null)
				crmBusinessGroup.setSort(1);
			else
				crmBusinessGroup.setSort(crmBusinessGroupOld.getSort() + 1);// 排序
			crmBusinessGroup.setCreateUser(empNum); // 创建人
			crmBusinessGroup.setOrgCode(orgCode);
			boolean result = crmBusinessGroup.insert();
			if (!result)
				return ResultUtil.success("新增失败");
			gid = crmBusinessGroup.getId();
			List<Integer> memberIds = crmBusinessGroup.getMemberIds();
			for (Integer empId : memberIds) {
				insertMembers(gid, empId, orgCode, 0);
			}
			List<Integer> managerIds = crmBusinessGroup.getManagerIds();
			for (Integer empId : managerIds) {
				insertMembers(gid, empId, orgCode, 1);
			}
			crmSeaRule.setGid(gid);
			crmSeaRule.insert();
			return ResultUtil.success("新增业务组成功");
		} else {
			crmBusinessGroup.updateById();
			List<Integer> memberIds = crmBusinessGroup.getMemberIds();
			List<Integer> managerIds = crmBusinessGroup.getManagerIds();
			// 更新组成员
			updateMembers(orgCode, gid, memberIds, 0);
			updateMembers(orgCode, gid, managerIds, 1);
			// 更新公海规则
			crmSeaRule.update(new LambdaQueryWrapper<CrmSeaRule>().eq(CrmSeaRule::getGid, gid));
			return ResultUtil.success("编辑业务组成功");
		}

	}

	private void insertMembers(Integer gid, Integer empId, Integer orgCode, Integer type) {
		CrmBusinessGroupMember.builder().gid(gid).empNum(empId).orgCode(orgCode).type(type).build().insert();
	}

	private void updateMembers(Integer orgCode, Integer gid, List<Integer> memberIds, Integer type) {
		List<CrmBusinessGroupMember> oldMembers = CrmBusinessGroupMember.builder().build()
				.selectList(new LambdaQueryWrapper<CrmBusinessGroupMember>().eq(CrmBusinessGroupMember::getGid, gid)
						.eq(CrmBusinessGroupMember::getType, type).select(CrmBusinessGroupMember::getEmpNum));
		// 旧组成员idlist
		List<Integer> oldIdsList = oldMembers.stream().map(CrmBusinessGroupMember::getEmpNum)
				.collect(Collectors.toList());

		List<Integer> deList = CheckUtil.reduce(oldIdsList, memberIds); // 删
		for (Integer integer : deList) {
			CrmBusinessGroupMember.builder().build()
					.delete(new LambdaQueryWrapper<CrmBusinessGroupMember>()
							.eq(CrmBusinessGroupMember::getEmpNum, integer).eq(CrmBusinessGroupMember::getGid, gid)
							.eq(CrmBusinessGroupMember::getOrgCode, orgCode).eq(CrmBusinessGroupMember::getType, type));
		}

		List<Integer> addList = CheckUtil.reduce(memberIds, oldIdsList); // 增
		for (Integer integer : addList) {
			CrmBusinessGroupMember.builder().empNum(integer).gid(gid).orgCode(orgCode).type(type).build().insert();
		}
	}

	@GetMapping("getBusinessGroup")
	@ApiOperation(value = "获取业务组列表", httpMethod = "GET", notes = "接口发布说明")
	public Result<Object> getBusinessGroup(@CurrentUser UserBean userBean) {
		Integer nowEmpNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);

//		YgglMainEmp yg = YgglMainEmp.builder().build()
//				.selectOne(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getOrgCode, orgCode)
//						.eq(YgglMainEmp::getEmpNum, nowEmpNum).select(YgglMainEmp::getIsManager));
		QyzxEmpEntAsso qyzxEmpEntAsso = QyzxEmpEntAsso.builder().build()
				.selectOne(new LambdaQueryWrapper<QyzxEmpEntAsso>().eq(QyzxEmpEntAsso::getOrgCode, orgCode)
						.eq(QyzxEmpEntAsso::getEmpNum, nowEmpNum));
		if (qyzxEmpEntAsso != null && qyzxEmpEntAsso.getUserType() != null
				&& (qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_ADMIN.getType()
						|| qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_C_ADMIN.getType())) {

			List<CrmBusinessGroup> list = CrmBusinessGroup.builder().build()
					.selectList(new QueryWrapper<CrmBusinessGroup>().lambda()
							.eq(CrmBusinessGroup::getOrgCode, getOrgCode(userBean)).select(CrmBusinessGroup::getId,
									CrmBusinessGroup::getSort, CrmBusinessGroup::getName,
									CrmBusinessGroup::getModifyTime));
			for (CrmBusinessGroup crmBusinessGroup : list) {
				Integer gid = crmBusinessGroup.getId();
				List<CrmBusinessGroupMember> crmBusinessGroupManagers = CrmBusinessGroupMember.builder().build()
						.selectList(new QueryWrapper<CrmBusinessGroupMember>().lambda()
								.eq(CrmBusinessGroupMember::getGid, gid).eq(CrmBusinessGroupMember::getType, 1)
								.eq(CrmBusinessGroupMember::getOrgCode, getOrgCode(userBean))
								.select(CrmBusinessGroupMember::getEmpNum));
				List<CrmBusinessGroupMember> crmBusinessGroupMembers = CrmBusinessGroupMember.builder().build()

						.selectList(new QueryWrapper<CrmBusinessGroupMember>().lambda()
								.eq(CrmBusinessGroupMember::getGid, gid).eq(CrmBusinessGroupMember::getType, 0)
								.eq(CrmBusinessGroupMember::getOrgCode, getOrgCode(userBean))
								.select(CrmBusinessGroupMember::getEmpNum));
				List<Map<String, Object>> managers = new ArrayList<Map<String, Object>>();
				for (CrmBusinessGroupMember crmBusinessGroupManager : crmBusinessGroupManagers) {
					Integer empNum = crmBusinessGroupManager.getEmpNum();
					String name = getEmpName(orgCode, empNum);
					Map<String, Object> map = new HashMap<String, Object>();
					map.put("name", name);
					map.put("empNum", empNum);
					managers.add(map);
				}
				crmBusinessGroup.setManagers(managers);

				List<Map<String, Object>> members = new ArrayList<Map<String, Object>>();
				for (CrmBusinessGroupMember crmBusinessGroupManager : crmBusinessGroupMembers) {
					Integer empNum = crmBusinessGroupManager.getEmpNum();
					String name = getEmpName(orgCode, empNum);
					Map<String, Object> map = new HashMap<String, Object>();
					map.put("name", name);
					map.put("empNum", empNum);
					members.add(map);
				}
				crmBusinessGroup.setMembers(members);

				CrmSeaRule crmSeaRules = CrmSeaRule.builder().build()
						.selectOne(new QueryWrapper<CrmSeaRule>().lambda().eq(CrmSeaRule::getGid, gid));
				crmBusinessGroup.setCrmSeaRules(crmSeaRules);
			}
			return ResultUtil.data(list, "获取成功");

		} else
			return ResultUtil.error("暂无查看权限");

	}

	@GetMapping("getBusinessGroupId")
	@ApiOperation(value = "获取业务组列表(id,name)", httpMethod = "GET", notes = "接口发布说明")
	public Result<Object> getBusinessGroupId(@CurrentUser UserBean userBean) {

		Integer nowEmpNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);

//		YgglMainEmp yg = YgglMainEmp.builder().build()
//				.selectOne(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getOrgCode, orgCode)
//						.eq(YgglMainEmp::getEmpNum, nowEmpNum).select(YgglMainEmp::getIsManager));
//		if (yg != null && yg.getIsManager() != null && (yg.getIsManager() == 1 || yg.getIsManager() == 2)) {
		QyzxEmpEntAsso qyzxEmpEntAsso = QyzxEmpEntAsso.builder().build()
				.selectOne(new LambdaQueryWrapper<QyzxEmpEntAsso>().eq(QyzxEmpEntAsso::getOrgCode, orgCode)
						.eq(QyzxEmpEntAsso::getEmpNum, nowEmpNum));
		if (qyzxEmpEntAsso != null && qyzxEmpEntAsso.getUserType() != null
				&& (qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_ADMIN.getType()
						|| qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_C_ADMIN.getType())) {
			List<CrmBusinessGroup> list = CrmBusinessGroup.builder().build()
					.selectList(new LambdaQueryWrapper<CrmBusinessGroup>().eq(CrmBusinessGroup::getOrgCode, orgCode)
							.select(CrmBusinessGroup::getName, CrmBusinessGroup::getId));
			return ResultUtil.data(list, "获取成功");
		} else {
			List<CrmBusinessGroupMember> list = CrmBusinessGroupMember.builder().build()
					.selectList(new LambdaQueryWrapper<CrmBusinessGroupMember>()
							.eq(CrmBusinessGroupMember::getEmpNum, getEmpNum(userBean))
							.eq(CrmBusinessGroupMember::getOrgCode, getOrgCode(userBean))
							.select(CrmBusinessGroupMember::getGid));
			List<Integer> gids = list.stream().map(CrmBusinessGroupMember::getGid).collect(Collectors.toList()).stream()
					.distinct().collect(Collectors.toList());
			List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
			for (Integer gid : gids) {
				String name = CrmBusinessGroup.builder().build().selectOne(new LambdaQueryWrapper<CrmBusinessGroup>()
						.eq(CrmBusinessGroup::getId, gid).select(CrmBusinessGroup::getName)).getName();
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("id", gid);
				map.put("name", name);
				result.add(map);
			}
			return ResultUtil.data(result, "获取成功");
		}
	}

//	@GetMapping("getBusinessGroupMembers")
//	@ApiOperation(value = "获取业务组列表成员", httpMethod = "GET", notes = "接口发布说明")
//	public Result<Object> getBusinessGroupMembers(@CurrentUser UserBean userBean, @RequestParam Integer gid) {
//		List<CrmBusinessGroupMember> crmBusinessGroupMembers = CrmBusinessGroupMember.builder().build()
//				.selectList(new QueryWrapper<CrmBusinessGroupMember>().lambda().eq(CrmBusinessGroupMember::getGid, gid)
//						.eq(CrmBusinessGroupMember::getType, 0));
//		List<YgglMainEmp> ygglMainEmps = new ArrayList<YgglMainEmp>();
//		for (CrmBusinessGroupMember crmBusinessGroupMember : crmBusinessGroupMembers) {
//			ygglMainEmps.add(getYg(getOrgCode(userBean), crmBusinessGroupMember.getEmpNum()));
//		}
//		List<CrmBusinessGroupMember> crmBusinessGroupManagers = CrmBusinessGroupMember.builder().build()
//				.selectList(new QueryWrapper<CrmBusinessGroupMember>().lambda().eq(CrmBusinessGroupMember::getGid, gid)
//						.eq(CrmBusinessGroupMember::getType, 1));
//		List<YgglMainEmp> ygglMainEmps2 = new ArrayList<YgglMainEmp>();
//		for (CrmBusinessGroupMember crmBusinessGroupManager : crmBusinessGroupManagers) {
//			ygglMainEmps2.add(getYg(getOrgCode(userBean), crmBusinessGroupManager.getEmpNum()));
//		}
//		return ResultUtil.datas(ygglMainEmps2,ygglMainEmps, "获取成功");
//	}

//	@GetMapping("getBusinessGroupRules") 
//	@ApiOperation(value = "获取业务组公海规则", httpMethod = "GET", notes = "接口发布说明")
//	public Result<Object> getBusinessGroupRules(@RequestParam Integer gid) {
//		List<CrmSeaRule> crmSeaRules = CrmSeaRule.builder().build()
//				.selectList(new QueryWrapper<CrmSeaRule>().lambda().eq(CrmSeaRule::getGid, gid));
//		return ResultUtil.data(crmSeaRules, "获取成功");
//	}

	private SysRegion getSysRegion(Integer keyId) {
		return SysRegion.builder().build()
				.selectOne(new QueryWrapper<SysRegion>().lambda().eq(SysRegion::getKeyId, keyId).select(
						SysRegion::getRegionName, SysRegion::getKeyId, SysRegion::getZipCode,
						SysRegion::getRegionCode));
	}

	@PostMapping("addClient")
	@Transactional
	@ApiOperation(value = "新增客户", httpMethod = "POST", notes = "接口发布说明")
	public Result<Object> addClient(@CurrentUser UserBean userBean, @RequestBody CrmClientData crmClientData) {
		Integer empNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);
		crmClientData.setClientStatus(1);
		crmClientData.setCreateUser(empNum);
		SysRegion district = getSysRegion(crmClientData.getDistrictId());
//		crmClientData.setZipCode(district.getZipCode());
		if (district != null) {
			crmClientData.setDistrict(district.getRegionName());
			SysRegion city = getSysRegion(district.getRegionCode());
			crmClientData.setCity(city.getRegionName());
			crmClientData.setCityId(city.getKeyId());
			SysRegion province = getSysRegion(city.getRegionCode());
			crmClientData.setProvinceId(province.getKeyId());
			crmClientData.setProvince(province.getRegionName());
		}

		crmClientData.setBelongUserName(getEmpName(orgCode, crmClientData.getBelongUser()));
		crmClientData.setOrgCode(getOrgCode(userBean));
		Integer belongGroup = crmClientData.getBelongGroup();
		if (belongGroup == null)
			return ResultUtil.error("未选择业务组");

		String belongGroupName = CrmBusinessGroup.builder().id(belongGroup).build().selectById().getName();
		crmClientData.setBelongGroupName(belongGroupName);
		Integer clientSource = crmClientData.getClientSource();
		Integer clientType = crmClientData.getClientType();
		Integer industry = crmClientData.getIndustry();
		if (clientSource != null) {
			String clientSourceName = ClientSourceClass.builder().id(clientSource).build().selectById().getName(); // 客户来源
			crmClientData.setClientSourceName(clientSourceName);
		}
		if (clientType != null) {
			String clientTypeName = ClientTypeClass.builder().id(clientType).build().selectById().getName(); // 客户类型
			crmClientData.setClientTypeName(clientTypeName);
		}
		if (industry != null) {
			String industryName = IndustryClass.builder().id(industry).build().selectById().getName();// 行业名
			crmClientData.setIndustryName(industryName);
		}
		if (!crmClientData.insert())
			return ResultUtil.error("新增失败");
		Integer crmDataId = crmClientData.getId();
		Integer[] ids = crmClientData.getIds();
		for (Integer id : ids) {
			CrmClientAssociate.builder().cid(crmDataId).associateId(id).orgCode(getOrgCode(userBean)).build().insert();
		}
		List<CrmClientContacts> crmClientContacts = crmClientData.getCrmClientContacts();
		for (CrmClientContacts crmClientContact : crmClientContacts) {
			crmClientContact.setCid(crmDataId);
			crmClientContact.insert();
		}
		crmClientData.setContactsNum(crmClientContacts.size());
		crmClientData.updateById();
		return ResultUtil.success("新增成功");
	}

	@PostMapping("editClient")
	@Transactional
	@ApiOperation(value = "编辑客户", httpMethod = "POST", notes = "接口发布说明")
	public Result<Object> editClient(@CurrentUser UserBean userBean, @RequestBody CrmClientData crmClientData) {
		Integer cid = crmClientData.getId();
		if (cid == null)
			return ResultUtil.error("请传入id再试");
		CrmClientData oldData = CrmClientData.builder().id(cid).build().selectById();
		if (oldData == null)
			return ResultUtil.error("请确认该客户数据是否存在");
		if (oldData.getClientStatus() == 5)
			return ResultUtil.error("请解封后继续编辑操作");
		List<CrmClientContacts> crmClientContacts = crmClientData.getCrmClientContacts();
		if (crmClientContacts != null) {
			for (CrmClientContacts crmClientContact : crmClientContacts) {
				crmClientContact.updateById();
			}
		}
		List<CrmClientAssociate> oldAssociates = CrmClientAssociate.builder().build()
				.selectList(new QueryWrapper<CrmClientAssociate>().lambda().eq(CrmClientAssociate::getCid, cid));
		List<Integer> oldIdsList = oldAssociates.stream().map(CrmClientAssociate::getAssociateId)
				.collect(Collectors.toList()); // 旧协助人list
		Integer[] ids = crmClientData.getIds();
		List<Integer> newIdsList = CollUtil.newArrayList(ids); // 新协助人list
		List<Integer> delList = CheckUtil.reduce(oldIdsList, newIdsList); // 要删的协助人
		for (Integer id : delList) {
			CrmClientAssociate.builder().build().delete(new QueryWrapper<CrmClientAssociate>().lambda()
					.eq(CrmClientAssociate::getCid, cid).eq(CrmClientAssociate::getAssociateId, id));
		}
		List<Integer> addList = CheckUtil.reduce(newIdsList, oldIdsList); // 要加的协助人
		for (Integer id : addList) {
			CrmClientAssociate.builder().cid(cid).associateId(id).orgCode(getOrgCode(userBean)).build().insert();
		}
		crmClientData.setModifyUser(getEmpNum(userBean));
		if (crmClientData.updateById())
			return ResultUtil.success("编辑成功");
		return ResultUtil.error("编辑失败");
	}

	@GetMapping("getClientAssociate")
	@ApiOperation(value = "获取协作人列表", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<Map<String, Object>>> getClientAssociate(@CurrentUser UserBean userBean,
			@RequestParam Integer cid) {
		List<CrmClientAssociate> crmClientAssociates = CrmClientAssociate.builder().build()
				.selectList(new QueryWrapper<CrmClientAssociate>().lambda().eq(CrmClientAssociate::getCid, cid));
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		for (CrmClientAssociate crmClientAssociate : crmClientAssociates) {
			Integer orgCode = crmClientAssociate.getOrgCode();
			Integer empNum = crmClientAssociate.getAssociateId();
			YgglMainEmp ygglMainEmp = getYg(orgCode, empNum);
			String name = ygglMainEmp.getName();
			String headUrl = ygglMainEmp.getHeadUrl();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("name", name);
			map.put("empNum", empNum);
			map.put("headUrl", headUrl);
			list.add(map);
		}
		return ResultUtil.data(list, "获取成功");
	}

	// 归属我的客户
	private List<CrmClientData> getMyCilent(Integer empNum, Integer orgCode, Integer groupId, Integer status,
			String like) {
		return CrmClientData.builder().build()
				.selectList(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
						.eq(CrmClientData::getBelongUser, empNum)
						.eq(groupId != null && groupId >= 0, CrmClientData::getBelongGroup, groupId)
						.eq(status != null && status >= 0, CrmClientData::getClientStatus, status)
						.like(!StrUtil.isBlank(like), CrmClientData::getClientName, like).or()
						.like(!StrUtil.isBlank(like), CrmClientData::getClientCellphone, like)
						.orderByDesc(CrmClientData::getCreateTime));
	}

	// 我协作的客户
	private List<CrmClientData> getMyAssociateCilent(Integer empNum, Integer orgCode, Integer groupId, Integer status,
			String like) {
		List<CrmClientAssociate> crmClientAssociates = CrmClientAssociate.builder().build().selectList(
				new QueryWrapper<CrmClientAssociate>().lambda().eq(CrmClientAssociate::getAssociateId, empNum)
						.eq(CrmClientAssociate::getOrgCode, orgCode).select(CrmClientAssociate::getCid));
		List<CrmClientData> myCrmClientAssociates = new ArrayList<CrmClientData>();
		for (CrmClientAssociate crmClientAssociate : crmClientAssociates) {
			Integer cid = crmClientAssociate.getCid();
			CrmClientData crmClientData = CrmClientData.builder().build()
					.selectOne(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getId, cid)
							.eq(groupId != null && groupId >= 0, CrmClientData::getBelongGroup, groupId)
							.eq(status != null && status >= 0, CrmClientData::getClientStatus, status)
							.like(!StrUtil.isBlank(like), CrmClientData::getClientName, like).or()
							.like(!StrUtil.isBlank(like), CrmClientData::getClientCellphone, like));
			if (crmClientData != null)
				myCrmClientAssociates.add(crmClientData);
		}
		return myCrmClientAssociates;
	}

	// 我的全部客户
	private List<CrmClientData> getMyTotalCilent(Integer empNum, Integer orgCode, Integer groupId, Integer status,
			String like) {
		return CheckUtil.distinct(getMyCilent(empNum, orgCode, groupId, status, like),
				getMyAssociateCilent(empNum, orgCode, groupId, status, like), true);
	}

	// 我关注的客户
	private List<CrmClientData> getMyStarCilent(Integer empNum, Integer orgCode, Integer groupId, Integer status) {
		return getMyTotalCilent(empNum, orgCode, groupId, status, null).stream()
				.filter(item -> item.getStar().equals(1)).collect(Collectors.toList());
	}

	// 七天未跟进的客户
	private List<CrmClientData> getCilentFollowOvertime(Integer empNum, Integer orgCode, Integer groupId,
			Integer status) {
		return getMyTotalCilent(empNum, orgCode, groupId, status, null).stream()
				.filter(item -> item.getLastFollowTime() != null
						&& item.getLastFollowTime().before(DateUtil.offsetDay(new Date(), -7)))
				.collect(Collectors.toList());
	}

	private <T> List<T> getPageList(List<T> list, int pageNumber, int pageSize, Comparator<T> comparator) {
		return list.stream().sorted(comparator).skip((pageNumber - 1) * pageSize).limit(pageSize)
				.collect(Collectors.toList());

	}

	@GetMapping("getMyCilentTotal")
	@ApiOperation(value = "获取我的客户列表(全)", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<CrmClientData>> getMyCilentTotal(@CurrentUser UserBean userBean,
			@RequestParam(required = false, defaultValue = "0") Integer type,
			@RequestParam(required = false) Integer groupId, @RequestParam(required = false) Integer status,
			@RequestParam(required = false) String like,
			@RequestParam(required = false, defaultValue = "1") int pageNumber,
			@RequestParam(required = false, defaultValue = "10") int pageSize) {
		Integer empNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);
		List<CrmClientData> crmClientDatas = null;
		if (type == null || type == 0) { // 全部客户(去重)
			List<CrmClientData> myClient = getMyCilent(empNum, orgCode, groupId, status, like);
			List<CrmClientData> myAsso = getMyAssociateCilent(empNum, orgCode, groupId, status, like);
			crmClientDatas = CheckUtil.distinct(myClient, myAsso, true).stream()
					.filter(CheckUtil.distinctByKey(CrmClientData::getId)).collect(Collectors.toList());
		} else if (type == 1) // 我负责的客户
			crmClientDatas = getMyCilent(empNum, orgCode, groupId, status, like);
		else if (type == 2) // 我协作的客户
			crmClientDatas = getMyAssociateCilent(empNum, orgCode, groupId, status, like);
		else if (type == 3) // 我关注的客户
			crmClientDatas = getMyStarCilent(empNum, orgCode, groupId, status);
		else if (type == 4) // 七天未跟进的客户
			crmClientDatas = getCilentFollowOvertime(empNum, orgCode, groupId, status);
		else
			return ResultUtil.error("查询失败");
//		Collections.sort(crmClientDatas, Comparator.comparing(CrmClientData::getCreateTime).reversed()); // 按时间降序排序

//		int pageNumber = 1;
//		int pageSize = 10;

		List<CrmClientData> result = getPageList(crmClientDatas, pageNumber, pageSize,
				Comparator.comparing(CrmClientData::getCreateTime).reversed());

		return ResultUtil.pageData(result, (long) crmClientDatas.size(), "查询成功");
//		return ResultUtil.data(crmClientDatas, "查询成功");
	}

	private List<Integer> MyJoinGroup(Integer empNum, Integer orgCode) {
		List<CrmBusinessGroupMember> members = CrmBusinessGroupMember.builder().build()
				.selectList(new QueryWrapper<CrmBusinessGroupMember>().lambda()
						.eq(CrmBusinessGroupMember::getEmpNum, empNum).eq(CrmBusinessGroupMember::getOrgCode, orgCode));
		return members.stream().map(CrmBusinessGroupMember::getGid).collect(Collectors.toList());
	}

	private List<Integer> MyManageGroup(Integer empNum, Integer orgCode) {
		List<CrmBusinessGroupMember> managers = CrmBusinessGroupMember.builder().build()
				.selectList(new QueryWrapper<CrmBusinessGroupMember>().lambda()
						.eq(CrmBusinessGroupMember::getEmpNum, empNum).eq(CrmBusinessGroupMember::getOrgCode, orgCode)
						.eq(CrmBusinessGroupMember::getType, 1));
		return managers.stream().map(CrmBusinessGroupMember::getGid).collect(Collectors.toList());
	}

	@GetMapping("getSeaCilentTotal")
	@ApiOperation(value = "获取公海客户列表(全)", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<CrmClientData>> getSeaCilentTotal(@CurrentUser UserBean userBean,
			@RequestParam(required = false) Integer type, @RequestParam(required = false) Integer groupId,
			@RequestParam(required = false) Integer status, @RequestParam(required = false) String like,
			@RequestParam(required = false, defaultValue = "1") int pageNumber,
			@RequestParam(required = false, defaultValue = "10") int pageSize) {
		Integer empNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);

//		YgglMainEmp yg = YgglMainEmp.builder().build()
//				.selectOne(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getOrgCode, orgCode)
//						.eq(YgglMainEmp::getEmpNum, empNum).select(YgglMainEmp::getIsManager));
//		if (yg != null && yg.getIsManager() != null && (yg.getIsManager() == 1 || yg.getIsManager() == 2)) {

		QyzxEmpEntAsso qyzxEmpEntAsso = QyzxEmpEntAsso.builder().build()
				.selectOne(new LambdaQueryWrapper<QyzxEmpEntAsso>().eq(QyzxEmpEntAsso::getOrgCode, orgCode)
						.eq(QyzxEmpEntAsso::getEmpNum, empNum));
		if (qyzxEmpEntAsso != null && qyzxEmpEntAsso.getUserType() != null
				&& (qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_ADMIN.getType()
						|| qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_C_ADMIN.getType())) {
			List<CrmClientData> list = CrmClientData.builder().build()
					.selectList(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
							.eq(CrmClientData::getBelongUser, 0)
							.eq(type != null && type >= 0, CrmClientData::getClientType, type)
							.eq(groupId != null && groupId >= 0, CrmClientData::getBelongGroup, groupId)
							.eq(status != null && status >= 0, CrmClientData::getClientStatus, status)
							.like(!StrUtil.isBlank(like), CrmClientData::getClientName, like).or()
							.like(!StrUtil.isBlank(like), CrmClientData::getClientCellphone, like));

//			int pageNumber = 1;
//			int pageSize = 5;
			List<CrmClientData> result = getPageList(list, pageNumber, pageSize,
					Comparator.comparing(CrmClientData::getCreateTime).reversed());

			return ResultUtil.pageData(result, (long) list.size(), "查询成功");

//			return ResultUtil.data(list, "查询成功");

		} else {

			// 当前用户加入的组
			List<Integer> gids1 = MyJoinGroup(empNum, orgCode);
			// 当前用户管理的组
			List<Integer> gids2 = MyManageGroup(empNum, orgCode);
			List<Integer> myGids = CheckUtil.distinct(gids1, gids2, true); // 当前用户所有业务组id
			List<CrmClientData> crmClientDatas = new ArrayList<CrmClientData>();
			for (Integer gid : myGids) {
				List<CrmClientData> datas = CrmClientData.builder().build()
						.selectList(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getBelongGroup, gid)
								.eq(CrmClientData::getBelongUser, 0)
								.eq(type != null && type >= 0, CrmClientData::getClientType, type)
								.eq(groupId != null && groupId >= 0, CrmClientData::getBelongGroup, groupId)
								.eq(status != null && status >= 0, CrmClientData::getClientStatus, status)
								.like(!StrUtil.isBlank(like), CrmClientData::getClientName, like).or()
								.like(!StrUtil.isBlank(like), CrmClientData::getClientCellphone, like));
				for (CrmClientData data : datas)
					crmClientDatas.add(data);
			}
			List<CrmClientData> result = getPageList(crmClientDatas, pageNumber, pageSize,
					Comparator.comparing(CrmClientData::getCreateTime).reversed());

			return ResultUtil.pageData(result, (long) crmClientDatas.size(), "查询成功");
//			return ResultUtil.data(crmClientDatas, "查询成功");
		}
	}

	@GetMapping("getAllCilentTotal")
	@ApiOperation(value = "获取所有客户列表(全)", httpMethod = "GET", notes = "接口发布说明")
	public Result<Object> getAllCilentTotal(@CurrentUser UserBean userBean,
			@RequestParam(required = false) Integer type, @RequestParam(required = false) Integer groupId,
			@RequestParam(required = false) Integer status, @RequestParam(required = false) String like,
			@RequestParam(required = false) String startCreateTime,
			@RequestParam(required = false) String endCreateTime,
			@RequestParam(required = false) String startFollowTime,
			@RequestParam(required = false) String endFollowTime,
			@RequestParam(required = false, defaultValue = "1") int pageNumber,
			@RequestParam(required = false, defaultValue = "10") int pageSize) {

		Integer empNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);

//		YgglMainEmp yg = YgglMainEmp.builder().build()
//				.selectOne(new QueryWrapper<YgglMainEmp>().lambda().eq(YgglMainEmp::getOrgCode, orgCode)
//						.eq(YgglMainEmp::getEmpNum, empNum).select(YgglMainEmp::getIsManager));
//		if (yg != null && yg.getIsManager() != null && (yg.getIsManager() == 1 || yg.getIsManager() == 2)) {
		QyzxEmpEntAsso qyzxEmpEntAsso = QyzxEmpEntAsso.builder().build()
				.selectOne(new LambdaQueryWrapper<QyzxEmpEntAsso>().eq(QyzxEmpEntAsso::getOrgCode, orgCode)
						.eq(QyzxEmpEntAsso::getEmpNum, empNum));
		if (qyzxEmpEntAsso != null && qyzxEmpEntAsso.getUserType() != null
				&& (qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_ADMIN.getType()
						|| qyzxEmpEntAsso.getUserType() == SysRoleType.U_TYPE_C_ADMIN.getType())) {

			IPage<CrmClientData> page = new Page<CrmClientData>(pageNumber, pageSize);

			IPage<CrmClientData> datas = CrmClientData.builder().build().selectPage(page,
					new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
							.eq(groupId != null && groupId >= 0, CrmClientData::getBelongGroup, groupId)
							.eq(type != null && type >= 0, CrmClientData::getClientType, type)
							.eq(status != null && status >= 0, CrmClientData::getClientStatus, status)
							.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime, startCreateTime)
							.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime, endCreateTime)
							.ge(StringUtils.isNotBlank(startFollowTime), CrmClientData::getCreateTime, startFollowTime)
							.le(StringUtils.isNotBlank(endFollowTime), CrmClientData::getCreateTime, endFollowTime)
							.like(!StrUtil.isBlank(like), CrmClientData::getClientName, like).or()
							.like(!StrUtil.isBlank(like), CrmClientData::getClientCellphone, like)
							.orderByDesc(CrmClientData::getCreateTime));

			return ResultUtil.pageData(datas.getRecords(), datas.getTotal(), "查询成功");
		} else {

			// 当前用户管理的组
			List<Integer> gids = MyManageGroup(empNum, orgCode);
			List<CrmClientData> crmClientDatas = new ArrayList<CrmClientData>();
			for (Integer gid : gids) {
				List<CrmClientData> datas = CrmClientData.builder().build().selectList(new QueryWrapper<CrmClientData>()
						.lambda().eq(CrmClientData::getBelongGroup, gid)
						.eq(groupId != null && groupId >= 0, CrmClientData::getBelongGroup, groupId)
						.eq(type != null && type >= 0, CrmClientData::getClientType, type)
						.eq(status != null && status >= 0, CrmClientData::getClientStatus, status)
						.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime, startCreateTime)
						.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime, endCreateTime)
						.ge(StringUtils.isNotBlank(startFollowTime), CrmClientData::getCreateTime, startFollowTime)
						.le(StringUtils.isNotBlank(endFollowTime), CrmClientData::getCreateTime, endFollowTime)
						.like(!StrUtil.isBlank(like), CrmClientData::getClientName, like).or()
						.like(!StrUtil.isBlank(like), CrmClientData::getClientCellphone, like));
				for (CrmClientData data : datas)
					crmClientDatas.add(data);
			}

			List<CrmClientData> result = getPageList(crmClientDatas, pageNumber, pageSize,
					Comparator.comparing(CrmClientData::getCreateTime).reversed());

			return ResultUtil.pageData(result, (long) crmClientDatas.size(), "查询成功");
//			return ResultUtil.data(crmClientDatas, "查询成功");
		}
	}

	@PostMapping("addCilentLinkMan")
	@Transactional
	@ApiOperation(value = "新增/编辑客户联系人", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> addCilentLinkMan(@CurrentUser UserBean userBean,
			@RequestBody CrmClientContacts crmClientContacts) {
		Integer cid = crmClientContacts.getCid();

		if (crmClientContacts.getId() == null) {
			if (cid == null)
				return ResultUtil.error("编辑失败,请传入客户id");

			Integer num = CrmClientData.builder().id(crmClientContacts.getCid()).build().selectById().getContactsNum();
			CrmClientData.builder().id(cid).contactsNum(++num).build().updateById();
		}
		if (crmClientContacts.insertOrUpdate())
			return ResultUtil.success("新增/编辑成功");
		return ResultUtil.error("新增/编辑失败");

	}

	@DeleteMapping("delCilentLinkMan")
	@Transactional
	@ApiOperation(value = "删除客户联系人", httpMethod = "DELETE", notes = "接口发布说明")
	public Result<Void> delCilentLinkMan(@CurrentUser UserBean userBean, @RequestParam Integer id,
			@RequestParam Integer cid) {
		if (cid == null)
			return ResultUtil.error("删除失败,请传入客户id");
		Integer num = CrmClientData.builder().id(cid).build().selectById().getContactsNum();
		if (--num < 0) {
			return ResultUtil.error("删除失败,已经没有可以删的联系人了");
		}
		CrmClientData.builder().id(cid).contactsNum(num).build().updateById();
		if (crmClientContactsMapper.deleteById(id) > 0)
			return ResultUtil.success("删除成功");
		return ResultUtil.error("删除失败");

	}

	@GetMapping("getCilentLinkMan")
	@ApiOperation(value = "获取客户联系人列表", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<CrmClientContacts>> getCilentLinkMan(@CurrentUser UserBean userBean, Integer cid) {
		return ResultUtil.data(CrmClientContacts.builder().build()
				.selectList(new QueryWrapper<CrmClientContacts>().lambda().eq(CrmClientContacts::getCid, cid)), "获取成功");

	}

	@PostMapping("addOrCancelClientStar")
	@ApiOperation(value = "新增客户收藏/取消收藏(仅归属人)", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> addOrCancelClientStar(@CurrentUser UserBean userBean, @RequestParam Integer cid) {
		Integer empNum = getEmpNum(userBean);
		CrmClientData crmClientData = CrmClientData.builder().id(cid).build().selectById();
		if (crmClientData == null)
			return ResultUtil.error("请确认该客户是否存在");
		if (!crmClientData.getBelongUser().equals(empNum))
			return ResultUtil.error("不是当前客户归属人");
		Integer star = crmClientData.getStar();
		if (star == null || star == 0) {
			crmClientData.setStar(1);
			crmClientData.updateById();
			return ResultUtil.success("收藏成功");
		} else if (star == 1) {
			crmClientData.setStar(0);
			crmClientData.updateById();
			return ResultUtil.success("取消收藏成功");
		} else
			return ResultUtil.error("失败");
	}

	@PostMapping("addClientFollow")
	@Transactional
	@ApiOperation(value = "新增跟进记录", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> addClientFollow(@CurrentUser UserBean userBean, @RequestBody CrmClientFollow crmClientFollow) {

		Integer cid = crmClientFollow.getCid();
		if (cid == null)
			return ResultUtil.error("请传入id再试");
		CrmClientData oldData = CrmClientData.builder().id(cid).build().selectById();
		if (oldData == null)
			return ResultUtil.error("请确认该客户数据是否存在");
		if (oldData.getClientStatus() == 5)
			return ResultUtil.error("请解封后继续编辑操作");

		Integer orgCode = getOrgCode(userBean);
		Integer empNum = getEmpNum(userBean);

		CrmClientData.builder().id(crmClientFollow.getCid()).clientStatus(crmClientFollow.getClientStatus())
				.lastFollowTime(new Date()).build().updateById(); // 更新客户最近跟进时间

		crmClientFollow.setOrgCode(orgCode);
		crmClientFollow.setCreateUserName(getEmpName(orgCode, empNum));

		if (crmClientFollow.insert())
			return ResultUtil.success("新增成功");
		return ResultUtil.error("新增失败");
	}

	@GetMapping("getCilentFollow")
	@ApiOperation(value = "获取跟进记录", httpMethod = "GET", notes = "接口发布说明")
	public Result<List<CrmClientFollow>> getCilentFollow(@CurrentUser UserBean userBean, @RequestParam Integer cid) {
		Integer orgCode = getOrgCode(userBean);
		return ResultUtil.data(CrmClientFollow.builder().build().selectList(new QueryWrapper<CrmClientFollow>().lambda()
				.eq(CrmClientFollow::getOrgCode, orgCode).eq(CrmClientFollow::getCid, cid)), "获取成功");
	}

	// 根据组id和员工号查询组管理员
	private CrmBusinessGroupMember getGroupManager(Integer groupId, Integer empNum) {
		return CrmBusinessGroupMember.builder().build().selectOne(
				new QueryWrapper<CrmBusinessGroupMember>().lambda().eq(CrmBusinessGroupMember::getGid, groupId)
						.eq(CrmBusinessGroupMember::getEmpNum, empNum).eq(CrmBusinessGroupMember::getType, 1));
	}

	@PostMapping("addClientArchive")
	@ApiOperation(value = "客户封档/解封", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> addClientArchive(@CurrentUser UserBean userBean, @RequestParam Integer cid) {
		Integer empNum = getEmpNum(userBean);
		CrmClientData crmClientData = CrmClientData.builder().id(cid).build().selectById();
		if (crmClientData == null)
			return ResultUtil.error("请确认该客户是否存在");
		if (!crmClientData.getBelongUser().equals(empNum)
				&& getGroupManager(crmClientData.getBelongGroup(), empNum) == null)
			return ResultUtil.error("不是当前客户归属人或组管理员");
		Integer status = crmClientData.getClientStatus();
		if (status != null && status != 5) {
			crmClientData.setClientStatus(5);
			crmClientData.updateById();
			return ResultUtil.success("封档成功");
		} else if (status == 5) {
			crmClientData.setClientStatus(1); // 解封后默认潜在状态
			crmClientData.updateById();
			return ResultUtil.success("解封成功");
		} else
			return ResultUtil.error("失败");
	}

	@PostMapping("designateClientBelonger")
	@ApiOperation(value = "指派客户归属人", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> designateClientBelonger(@CurrentUser UserBean userBean, @RequestParam Integer cid,
			@RequestParam Integer belongUser) {
		Integer empNum = getEmpNum(userBean);
		if (cid == null)
			return ResultUtil.error("请传入id再试");
		CrmClientData oldData = CrmClientData.builder().id(cid).build().selectById();
		if (oldData == null)
			return ResultUtil.error("请确认该客户数据是否存在");
		if (!oldData.getBelongUser().equals(empNum) && getGroupManager(oldData.getBelongGroup(), empNum) == null)
			return ResultUtil.error("不是当前客户归属人或组管理员");
		if (oldData.getClientStatus() == 5)
			return ResultUtil.error("请解封后继续编辑操作");

		Integer belongGroup = oldData.getBelongGroup();
		CrmBusinessGroupMember crmBusinessGroupMember = CrmBusinessGroupMember.builder().build()
				.selectOne(new QueryWrapper<CrmBusinessGroupMember>().lambda()
						.eq(CrmBusinessGroupMember::getGid, belongGroup)
						.eq(CrmBusinessGroupMember::getEmpNum, belongUser));
		if (crmBusinessGroupMember == null)
			return ResultUtil.error("指派失败,该用户不是本组成员");

		if (CrmClientData.builder().id(cid).belongUser(belongUser)
				.belongUserName(getEmpName(getOrgCode(userBean), belongUser)).build().updateById())
			return ResultUtil.success("指派成功");
		return ResultUtil.error("指派失败");

	}

	@PostMapping("freeClientToSea")
	@Transactional
	@ApiOperation(value = "释放客户到公海", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> freeClientToSea(@CurrentUser UserBean userBean, @RequestParam Integer cid) {
		Integer empNum = getEmpNum(userBean);
		if (cid == null)
			return ResultUtil.error("请传入id再试");
		CrmClientData oldData = CrmClientData.builder().id(cid).build().selectById();
		if (oldData == null)
			return ResultUtil.error("请确认该客户数据是否存在");
		if (oldData.getBelongUser() == 0)
			return ResultUtil.error("该客户已在公海列表");
		if ((!oldData.getOrgCode().equals(getOrgCode(userBean)) || !oldData.getBelongUser().equals(empNum))
				&& getGroupManager(oldData.getBelongGroup(), empNum) == null)
			return ResultUtil.error("不是当前客户归属人或组管理员");
		if (oldData.getClientStatus() == 5)
			return ResultUtil.error("请解封后继续编辑操作");
		oldData.setBelongUser(0); // 0-进入客户公海
		oldData.setBelongUserName(null);
		// 删除协作人列表
		CrmClientAssociate.builder().build()
				.delete(new QueryWrapper<CrmClientAssociate>().lambda().eq(CrmClientAssociate::getCid, cid));
		if (oldData.updateById())
			return ResultUtil.success("释放成功");
		return ResultUtil.error("释放失败");
	}

	@PostMapping("receiveSeaClient")
	@ApiOperation(value = "接收公海客户", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> receiveSeaClient(@CurrentUser UserBean userBean, @RequestParam Integer cid) {
		Integer empNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);
		if (cid == null)
			return ResultUtil.error("请传入id再试");
		CrmClientData oldData = CrmClientData.builder().id(cid).build().selectById();
		if (oldData == null)
			return ResultUtil.error("请确认该客户数据是否存在");
		// 当前用户所有组id
		List<Integer> myGids = CheckUtil.distinct(MyJoinGroup(empNum, orgCode), MyManageGroup(empNum, orgCode), true);
		if (!myGids.contains(oldData.getBelongGroup()))
			return ResultUtil.error("不是当前业务组成员");
		oldData.setBelongUser(empNum);
		oldData.setBelongUserName(getEmpName(orgCode, empNum));
		if (oldData.updateById())
			return ResultUtil.success("接收成功");
		return ResultUtil.error("接收失败");
	}

	@Transactional
	@PostMapping("designateClientBelongers")
	@ApiOperation(value = "转移客户归属人(批量客户)", httpMethod = "POST", notes = "接口发布说明")
	public Result<Void> designateClientBelongers(@CurrentUser UserBean userBean, @RequestParam List<Integer> cids,
			@RequestParam Integer belonger) {
		// 所选的客户列表
		List<CrmClientData> crmClientDatas = crmClientDataMapper.selectBatchIds(cids);
		// 业务组id列表
		List<Integer> gids = crmClientDatas.stream().map(CrmClientData::getBelongGroup).collect(Collectors.toList());
		Integer gid = gids.get(0);
		// 当前操作人是否管理员
		CrmBusinessGroupMember nowManager = CrmBusinessGroupMember.builder().build()
				.selectOne(new QueryWrapper<CrmBusinessGroupMember>().lambda().eq(CrmBusinessGroupMember::getGid, gid)
						.eq(CrmBusinessGroupMember::getEmpNum, getEmpNum(userBean))
						.eq(CrmBusinessGroupMember::getType, 1));
		if (nowManager == null)
			return ResultUtil.error("没有操作权限，请联系组管理员进行操作");
		// 是否有不同组的客户
		if (gids.stream().distinct().count() > 1)
			return ResultUtil.error("请确认是否同一业务组客户");
		// 转移人业务组信息
		CrmBusinessGroupMember crmBusinessGroupMember = CrmBusinessGroupMember.builder().build()
				.selectOne(new QueryWrapper<CrmBusinessGroupMember>().lambda().eq(CrmBusinessGroupMember::getGid, gid)
						.eq(CrmBusinessGroupMember::getEmpNum, belonger));
		if (crmBusinessGroupMember == null)
			return ResultUtil.error("请确认转移目标是当前业务组成员");
		for (CrmClientData crmClientData : crmClientDatas) {
			crmClientData.setBelongUser(belonger);
			crmClientData.setBelongUserName(getEmpName(getOrgCode(userBean), belonger));
			crmClientData.updateById();
		}
		return ResultUtil.success("转移成功");

	}

	/**
	 * 
	 * @param userBean
	 * @param type            统计图类型(暂时不要)
	 * @param groupId         业务组id
	 * @param memberId        业务员id
	 * @param startCreateTime 创建时间-开始时间
	 * @param endCreateTime   创建时间-结束时间
	 * @return 对应客户数据count
	 */
	@GetMapping("getAllCartogram")
	@ApiOperation(value = "获取统计图(全)", httpMethod = "GET", notes = "接口发布说明")
	public Result<Object> getAllCartogram(@CurrentUser UserBean userBean,
			@RequestParam(required = false) Integer groupId, @RequestParam(required = false) Integer belonger,
			@RequestParam(required = false) String startCreateTime,
			@RequestParam(required = false) String endCreateTime) {
		Integer orgCode = getOrgCode(userBean);
		List<Object> listAll = new ArrayList<Object>();
		listAll.add(cartogram(orgCode, groupId, 1, belonger, startCreateTime, endCreateTime));// 图1
		listAll.add(cartogram(orgCode, groupId, 2, belonger, startCreateTime, endCreateTime));// 图2
		listAll.add(cartogram(orgCode, groupId, 3, belonger, startCreateTime, endCreateTime));// 图3
		listAll.add(cartogram(orgCode, groupId, 4, belonger, startCreateTime, endCreateTime));// 图4

		List<Map<String, Object>> cartogram5 = new ArrayList<Map<String, Object>>();// 图5
		List<ClientTypeClass> typeClasses = ClientTypeClass.builder().build().selectAll();
		for (ClientTypeClass clientTypeClass : typeClasses) {
			Integer type = clientTypeClass.getId();
			Map<String, Object> map = cartogram2(orgCode, groupId, type, belonger, startCreateTime, endCreateTime);
			if ((Integer) map.get("num") > 0) {
				cartogram5.add(map);
			}
		}
		listAll.add(cartogram5);

//		List<Map<String, Object>> cartogram6 = new ArrayList<Map<String, Object>>(); // 图6
//		for (Integer keyId : getKeyIdList()) {
//			Map<String, Object> map = cartogram3(orgCode, groupId, keyId, belonger, startCreateTime, endCreateTime);
//			if ((Integer) map.get("num") > 0) {
//				cartogram6.add(map);
//			}
//		}

		List<CrmCartogramDto> cartogram6 = getCartogram6(orgCode, belonger, groupId, startCreateTime, endCreateTime);
		listAll.add(cartogram6);
		return ResultUtil.data(listAll, "获取成功");
	}

	// 获取省份id
//	private List<Integer> getKeyIdList() {
//		List<SysRegion> list = SysRegion.builder().build()
//				.selectList(new LambdaQueryWrapper<SysRegion>().eq(SysRegion::getLevel, 1).select(SysRegion::getKeyId));
//		return list.stream().map(SysRegion::getKeyId).collect(Collectors.toList());
//	}

	@GetMapping("getAllBusinessMan")
	@ApiOperation(value = "获取全部业务员", httpMethod = "GET", notes = "接口发布说明")
	public Result<Object> getAllBusinessMan(@CurrentUser UserBean userBean) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		List<Integer> empNums = getAllBusinessGroupMember(getOrgCode(userBean), null);
		for (Integer integer : empNums) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("empNum", integer);
			map.put("name", getEmpName(getOrgCode(userBean), integer));
			list.add(map);
		}
		return ResultUtil.data(list, "获取成功");
	}

	@GetMapping("getBusinessManByGroupId")
	@ApiOperation(value = "获取业务员ByGroupId", httpMethod = "GET", notes = "接口发布说明")
	public Result<Object> getBusinessManByGroupId(@CurrentUser UserBean userBean, Integer gid) {
		List<CrmBusinessGroupMember> list = CrmBusinessGroupMember.builder().build()
				.selectList(new LambdaQueryWrapper<CrmBusinessGroupMember>().eq(CrmBusinessGroupMember::getGid, gid)
						.select(CrmBusinessGroupMember::getEmpNum));
		List<Integer> empNums = list.stream().map(CrmBusinessGroupMember::getEmpNum).collect(Collectors.toList());
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		for (Integer empNum : empNums) {
			String name = getEmpName(getOrgCode(userBean), empNum);
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("name", name);
			map.put("empNum", empNum);
			result.add(map);
		}
		return ResultUtil.data(result, "获取成功");
	}

	// 获取公司全部业务员
	private List<Integer> getAllBusinessGroupMember(Integer orgCode, Integer groupId) {
		List<CrmBusinessGroupMember> list = CrmBusinessGroupMember.builder().build()
				.selectList(new QueryWrapper<CrmBusinessGroupMember>().lambda()
						.eq(CrmBusinessGroupMember::getOrgCode, orgCode)
						.eq(groupId != null && groupId >= 0, CrmBusinessGroupMember::getGid, groupId));
		return list.stream().map(CrmBusinessGroupMember::getEmpNum).collect(Collectors.toList()).stream().distinct()
				.collect(Collectors.toList());
//		list.stream().filter(CheckUtil.distinctByKey(CrmBusinessGroupMember::getEmpNum)).collect(Collectors.toList());
	}

	// 封装条形图数据Map
	private Map<String, Object> selectMap(Integer orgCode, Integer groupId, Integer belonger, Integer status,
			String startCreateTime, String endCreateTime) {
		Map<String, Object> map = new HashMap<String, Object>();
		Integer num = CrmClientData.builder().build()
				.selectCount(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
						.eq(CrmClientData::getClientStatus, status)
						.eq(belonger != null, CrmClientData::getBelongUser, belonger)
						.eq(groupId != null, CrmClientData::getBelongGroup, groupId)
						.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime, startCreateTime)
						.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime, endCreateTime));
		String name = "未知";
		if (belonger == 0)
			name = "公海客户";
		else if (belonger > 0)
			name = getEmpName(orgCode, belonger);

		map.put("name", name);
		map.put("num", num);
		return map;
	}

	/**
	 * 统计图1,2,3,4
	 * 
	 * @param orgCode  企业id(必填)
	 * @param groupId  组id (可不填)
	 * @param status   分类 1 2 3 4 (必填)
	 * @param belonger 业务员id (可不填)
	 * @return
	 */
	private List<Map<String, Object>> cartogram(Integer orgCode, Integer groupId, Integer status, Integer belonger,
			String startCreateTime, String endCreateTime) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if (belonger == null) {
			List<Integer> members = getAllBusinessGroupMember(orgCode, groupId);
			for (Integer memberId : members) {
				Map<String, Object> map = selectMap(orgCode, groupId, memberId, status, startCreateTime, endCreateTime);
				if ((Integer) map.get("num") > 0) {
					list.add(map);
				}
			}
			Map<String, Object> map = selectMap(orgCode, groupId, 0, status, startCreateTime, endCreateTime);
			if ((Integer) map.get("num") > 0)
				list.add(map);

		} else {
			Map<String, Object> map = selectMap(orgCode, groupId, belonger, status, startCreateTime, endCreateTime);
			if ((Integer) map.get("num") > 0) {
				list.add(map);
			}
		}
		return list;
	}

	/**
	 * 统计图5
	 * 
	 * @param orgCode  企业id(必填)
	 * @param groupId  组id (可不填)
	 * @param level    客户等级分类 (必填)
	 * @param belonger 业务员id (可不填)
	 * @return
	 */
	private Map<String, Object> cartogram2(Integer orgCode, Integer groupId, Integer level, Integer belonger,
			String startCreateTime, String endCreateTime) {
		Map<String, Object> map = new HashMap<String, Object>();
		Integer num = 0;
		if (belonger == null) {
			List<Integer> members = getAllBusinessGroupMember(orgCode, groupId);
			for (Integer memberId : members) {
				num += CrmClientData.builder().build()
						.selectCount(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
								.eq(CrmClientData::getClientType, level).eq(CrmClientData::getBelongUser, memberId)
								.eq(groupId != null, CrmClientData::getBelongGroup, groupId)
								.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime,
										startCreateTime)
								.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime,
										endCreateTime));

			}

			num += CrmClientData.builder().build()
					.selectCount(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
							.eq(CrmClientData::getClientType, level).eq(CrmClientData::getBelongUser, 0)
							.eq(groupId != null, CrmClientData::getBelongGroup, groupId)
							.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime, startCreateTime)
							.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime, endCreateTime));

			map.put("level", level);
			String name = ClientTypeClass.builder().id(level).build().selectById().getName();
			map.put("name", name);
			map.put("num", num);

		} else {
			num = CrmClientData.builder().build()
					.selectCount(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
							.eq(CrmClientData::getClientType, level).eq(CrmClientData::getBelongUser, belonger)
							.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime, startCreateTime)
							.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime, endCreateTime));
			map.put("level", level);
			String name = ClientTypeClass.builder().id(level).build().selectById().getName();
			map.put("name", name);
			map.put("num", num);
		}
		return map;
	}

	/**
	 * 统计图6
	 * 
	 * @param orgCode  企业id(必填)
	 * @param groupId  组id (可不填)
	 * @param level    客户等级分类 (必填)
	 * @param belonger 业务员id (可不填)
	 * @return
	 */
//	private Map<String, Object> cartogram3(Integer orgCode, Integer groupId, Integer provinceId, Integer belonger,
//			String startCreateTime, String endCreateTime) {
//		Map<String, Object> map = new HashMap<String, Object>();
//		Integer num = 0;
//		if (belonger == null) {
//
//			List<Integer> members = getAllBusinessGroupMember(orgCode, groupId);
//			for (Integer memberId : members) {
//				num += CrmClientData.builder().build()
//						.selectCount(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
//								.eq(CrmClientData::getProvinceId, provinceId).eq(CrmClientData::getBelongUser, memberId)
//								.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime,
//										startCreateTime)
//								.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime,
//										endCreateTime));
//			}
//			map.put("provinceId", provinceId);
//			String name = SysRegion.builder().build().selectOne(new QueryWrapper<SysRegion>().lambda()
//					.eq(SysRegion::getLevel, 1).eq(SysRegion::getKeyId, provinceId).select(SysRegion::getRegionName))
//					.getRegionName();
//			map.put("provinceName", name);
//			map.put("num", num);
//
//		} else {
//			num = CrmClientData.builder().build()
//					.selectCount(new QueryWrapper<CrmClientData>().lambda().eq(CrmClientData::getOrgCode, orgCode)
//							.eq(CrmClientData::getProvinceId, provinceId).eq(CrmClientData::getBelongUser, belonger)
//							.ge(StringUtils.isNotBlank(startCreateTime), CrmClientData::getCreateTime, startCreateTime)
//							.le(StringUtils.isNotBlank(endCreateTime), CrmClientData::getCreateTime, endCreateTime));
//			map.put("provinceId", provinceId);
//			String name = SysRegion.builder().build().selectOne(new QueryWrapper<SysRegion>().lambda()
//					.eq(SysRegion::getLevel, 1).eq(SysRegion::getKeyId, provinceId).select(SysRegion::getRegionName))
//					.getRegionName();
//			map.put("provinceName", name);
//			map.put("num", num);
//		}
//		return map;
//	}

	/**
	 * 统计图6-籍贯
	 * 
	 * @param orgCode
	 * @param belongGroup
	 * @param createTime
	 * @return
	 */
	private List<CrmCartogramDto> getCartogram6(Integer orgCode, Integer belongUser, Integer belongGroup,
			String startCreateTime, String endCreateTime) {
		return crmClientDataMapper.crmCartogram(orgCode, belongUser, belongGroup, startCreateTime, endCreateTime);
	}

	/**
	 * 导入
	 * 
	 * @param url 上传路径
	 * @return
	 */
	@PostMapping("importClient")
	@Transactional
	@ApiOperation(value = "导入客户", httpMethod = "POST", notes = "接口发布说明")
	public Result<Object> importClient(@CurrentUser UserBean userBean, @RequestParam String url) {
		Integer empNum = getEmpNum(userBean);
		Integer orgCode = getOrgCode(userBean);

//		String filePath = null;
//		String time = null;
//		try {
//			time = DateUtil.format(new Date(), "yyyyMMddHHmmssSSS");
//			filePath = FileHelper.downLoadFromUrl(url, time + ".xls", realPath);
//		} catch (IOException e) {
//			e.printStackTrace();
//		}
		// 从文件中读取Excel为ExcelReader
		ExcelReader reader;
		// 从流中读取Excel为ExcelReader（比如从ClassPath中读取Excel文件）
//		reader = ExcelUtil.getReader(ResourceUtil.getStream(filePath));
		// 从网络流OSS中读取excel资源
		reader = getReaderByUrl(url);
		reader.addHeaderAlias("*客户名称(必填)", "clientName");
		reader.addHeaderAlias("*所属分组(必填)", "belongGroupName");
		reader.addHeaderAlias("行业", "industryName");
		reader.addHeaderAlias("客户类型", "clientTypeName");
		reader.addHeaderAlias("手机号码(客户)", "clientCellphone");
		reader.addHeaderAlias("座机号码", "clientTelephone");
		reader.addHeaderAlias("官网", "clientUrl");
		reader.addHeaderAlias("邮箱", "clientEmail");
		reader.addHeaderAlias("地址", "address");
		reader.addHeaderAlias("联系人姓名", "linkName");
		reader.addHeaderAlias("手机号码(联系人)", "cellphone");
		reader.addHeaderAlias("所属公司", "company");
		reader.addHeaderAlias("性别", "gender");
		reader.addHeaderAlias("职位", "position");
		reader.addHeaderAlias("办公电话", "telephone");
		reader.addHeaderAlias("个人邮箱", "email");
		reader.addHeaderAlias("个人地址", "linkAddress");
		List<CrmClientDataImportVo> list = reader.read(0, 1, CrmClientDataImportVo.class);
		for (CrmClientDataImportVo crmClientDataImportVo : list) {

			String clientName = crmClientDataImportVo.getClientName(); // 客户名称(必填)
			String belongGroupName = crmClientDataImportVo.getBelongGroupName(); // 分组名(必填)
			String industryName = crmClientDataImportVo.getIndustryName(); // 行业名
			String clientTypeName = crmClientDataImportVo.getClientTypeName(); // 客户类型名
			String clientCellphone = crmClientDataImportVo.getClientCellphone(); // 客户手机号
			String clientTelephone = crmClientDataImportVo.getClientTelephone(); // 客户座机号
			String clientUrl = crmClientDataImportVo.getClientUrl(); // 官网
			String clientEmail = crmClientDataImportVo.getClientEmail(); // 邮箱
			String clientAddress = crmClientDataImportVo.getAddress(); // 地址

			String linkname = crmClientDataImportVo.getLinkName(); // 联系人姓名
			String linkcellphone = crmClientDataImportVo.getCellphone(); // 手机号码
			String linkcompany = crmClientDataImportVo.getCompany(); // 所属公司
			String linkgender = crmClientDataImportVo.getGender(); // 性别
			String linkposition = crmClientDataImportVo.getPosition(); // 职位
			String linktelephone = crmClientDataImportVo.getTelephone(); // 办公电话
			String linkemail = crmClientDataImportVo.getEmail(); // 个人邮箱
			String linkaddress = crmClientDataImportVo.getLinkAddress(); // 个人地址

			CrmClientData crmClientData = new CrmClientData();

			// 业务组
			CrmBusinessGroup crmBusinessGroup = CrmBusinessGroup.builder().build()
					.selectOne(new LambdaQueryWrapper<CrmBusinessGroup>().eq(CrmBusinessGroup::getOrgCode, orgCode)
							.eq(CrmBusinessGroup::getName, belongGroupName).select(CrmBusinessGroup::getId));
			if (crmBusinessGroup == null)
				return ResultUtil.error("没有匹配的业务组名称");

			if (StrUtil.isNotBlank(industryName)) {
				// 行业字典
				IndustryClass industryClass = IndustryClass.builder().build()
						.selectOne(new LambdaQueryWrapper<IndustryClass>().eq(IndustryClass::getName, industryName)
								.select(IndustryClass::getId));
				if (industryClass == null)
					return ResultUtil.error("没有匹配的行业名称");
				crmClientData.setIndustry(industryClass.getId());
			}

			if (StrUtil.isNotBlank(clientTypeName)) {
				// 客户类型
				ClientTypeClass clientTypeClass = ClientTypeClass.builder().build()
						.selectOne(new LambdaQueryWrapper<ClientTypeClass>()
								.eq(ClientTypeClass::getName, clientTypeName).select(ClientTypeClass::getId));
				if (clientTypeClass == null)
					return ResultUtil.error("没有匹配的客户类型名称");
				crmClientData.setClientType(clientTypeClass.getId());
			}

			crmClientData.setClientStatus(1);
			crmClientData.setContactsNum(1);
			crmClientData.setBelongUser(empNum);
			crmClientData.setBelongUserName(getEmpName(orgCode, empNum));
			crmClientData.setClientName(clientName);
			crmClientData.setBelongGroupName(belongGroupName);
			crmClientData.setBelongGroup(crmBusinessGroup.getId());
			crmClientData.setIndustryName(industryName);

			crmClientData.setClientTypeName(clientTypeName);

			crmClientData.setClientCellphone(clientCellphone);
			crmClientData.setClientTelephone(clientTelephone);
			crmClientData.setClientUrl(clientUrl);
			crmClientData.setClientEmail(clientEmail);
			crmClientData.setAddress(clientAddress);
			crmClientData.setOrgCode(orgCode);
			crmClientData.insert();

			Integer cid = crmClientData.getId();

			CrmClientContacts contacts = new CrmClientContacts();
			contacts.setName(linkname);
			contacts.setCellphone(linkcellphone);
			contacts.setCompany(linkcompany);
			if (linkgender == "女")
				contacts.setGender(0);
			else if (linkgender == "男")
				contacts.setGender(1);
			else
				contacts.setGender(null);
			contacts.setPosition(linkposition);
			contacts.setTelephone(linktelephone);
			contacts.setEmail(linkemail);
			contacts.setAddress(linkaddress);
			if (!(linkname == null && linkcellphone == null && linkcompany == null && linkgender == null
					&& linkposition == null && linktelephone == null && linkemail == null && linkaddress == null)) {
				contacts.setCid(cid);
				contacts.insert();
			}
		}
		return ResultUtil.success("导入成功");
	}

	private ExcelReader getReaderByUrl(String url) {
		InputStream is = null;
		try {
			is = new URL(url).openConnection().getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return ExcelUtil.getReader(is);
	}
}