package com.goafanti.user.service.impl; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.UUID; import javax.annotation.Resource; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import com.goafanti.common.bo.CityBo; import com.goafanti.common.bo.Error; import com.goafanti.common.bo.ProvinceBo; import com.goafanti.common.constant.AFTConstants; import com.goafanti.common.dao.DistrictGlossoryMapper; import com.goafanti.common.dao.OrganizationContactBookMapper; import com.goafanti.common.dao.OrganizationIdentityMapper; import com.goafanti.common.dao.UserChannelMapper; import com.goafanti.common.dao.UserMapper; import com.goafanti.common.dao.UserNamesMapper; import com.goafanti.common.dao.UserTransferLogMapper; import com.goafanti.common.enums.ChannelStatus; import com.goafanti.common.enums.SocietyTag; import com.goafanti.common.error.BusinessException; import com.goafanti.common.model.User; import com.goafanti.common.model.UserChannel; import com.goafanti.common.utils.ExcelUtils; import com.goafanti.common.utils.LoggerUtils; import com.goafanti.common.utils.PasswordUtil; import com.goafanti.common.utils.StringUtils; import com.goafanti.core.mybatis.BaseMybatisDao; import com.goafanti.core.mybatis.page.Pagination; import com.goafanti.core.shiro.token.TokenManager; import com.goafanti.user.bo.InputUserChannel; import com.goafanti.user.bo.OutUserChannel; import com.goafanti.user.service.UserChannelService; @Service public class UserChannelServiceImpl extends BaseMybatisDao implements UserChannelService{ @Autowired private UserMapper userMapper; @Autowired private OrganizationIdentityMapper organizationIdentityMapper; @Autowired private OrganizationContactBookMapper organizationContactBookMapper; @Autowired private DistrictGlossoryMapper districtGlossoryMapper; @Autowired private UserTransferLogMapper userTransferLogMapper; @Autowired private UserChannelMapper userChannelMapper; @Autowired private UserNamesMapper userNamesMapper; @Resource(name = "passwordUtil") private PasswordUtil passwordUtil; @Override public Object add(InputUserChannel in) { String uid=UUID.randomUUID().toString(); Calendar now = Calendar.getInstance(); now.set(Calendar.MILLISECOND, 0); in.setAid(TokenManager.getAdminId()); in.setStatus(0); in.setUid(uid); User u=new User(); u.setId(uid); u.setNickname(in.getUserName()); u.setCreateTime(now.getTime()); u.setPassword(passwordUtil.getEncryptPwd("123456", now.getTime())); userChannelMapper.insertSelective(in); userMapper.insertSelective(u); return 1; } @Override public Object batchListReceivables(MultipartFile file) { //获得Workbook工作薄对象 Workbook workbook = ExcelUtils.getWorkBook(file); //创建一个对象,用来存储数据 List count=new ArrayList<>(); List list=new ArrayList<>(); List list2=new ArrayList<>(); List list3=new ArrayList<>(); String aid=TokenManager.getAdminId(); Calendar now = Calendar.getInstance(); now.set(Calendar.MILLISECOND, 0); if(workbook != null){ //获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(0); if(sheet == null){ return null; } //获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //处理空白行 for (int i = firstRowNum; i <= sheet.getLastRowNum();i++) { Row r = sheet.getRow(i); if(r == null){ // 如果是空行(即没有任何数据、格式),直接把它以下的数据往上移动 sheet.shiftRows(i+1, sheet.getLastRowNum(),-1); continue; } boolean flag = false; for(Cell c:r){ if(c.getCellTypeEnum() != CellType.BLANK){ flag = true; break; } } if(flag){ continue; } else{//如果是空白行(即可能没有数据,但是有一定格式) if(i == sheet.getLastRowNum())//如果到了最后一行,直接将那一行remove掉 sheet.removeRow(r); else//如果还没到最后一行,则数据往上移一行 sheet.shiftRows(i+1, sheet.getLastRowNum(),-1); } } lastRowNum=sheet.getLastRowNum(); if (lastRowNum<2) { throw new BusinessException(new Error("未找到正确的参数。")); } //循环第2行后的所有行 for(int rowNum = firstRowNum+2;rowNum <= lastRowNum;rowNum++){ //获得当前行 Row row = sheet.getRow(rowNum); if(row == null){ continue; } //获得当前行的开始列 int firstCellNum = row.getFirstCellNum(); //获得当前行的列数 int lastCellNum = row.getLastCellNum(); //循环当前行 cellNum = (firstCellNum+1) 则是除开第一例 InputUserChannel in=new InputUserChannel(); List cList=districtGlossoryMapper.getCity(); ListpList=districtGlossoryMapper.getProvince(); for(int cellNum = (firstCellNum); cellNum < lastCellNum;cellNum++){ Cell cell = row.getCell(cellNum); try { switch (cellNum) { case 0: in.setUserName(getCellValue(cell)); break; case 1: in.setProvince(getProvinceId(pList,getCellValue(cell))); in.setProvinces(getCellValue(cell)); break; case 2: in.setCity(getCityId(cList,getCellValue(cell))); in.setCitys(getCellValue(cell)); break; case 3: in.setSocietyTag(SocietyTag.getCodeByValue(getCellValue(cell)).toString()); in.setSocietyTags(getCellValue(cell)); break; case 4: in.setContacts(getCellValue(cell)); break; case 5: in.setContactMobile(getCellValue(cell)); break; case 6: in.setRemarks(getCellValue(cell)); break; } } catch (Exception e) { throw new BusinessException(new Error("表格第"+(rowNum+1)+"行输入内容不完整。")); } } if (StringUtils.isBlank(in.getUserName())|| StringUtils.isBlank(in.getContacts())|| StringUtils.isBlank(in.getContactMobile())) { throw new BusinessException(new Error("表格第"+(rowNum+1)+"行输入内容不完整。")); } count.add(in); } } for (InputUserChannel in : count) { String uid=UUID.randomUUID().toString(); in.setAid(aid); in.setCreateTime(now.getTime()); in.setShareType(0); in.setPassword(passwordUtil.getEncryptPwd("123456", now.getTime())); User u=userMapper.selectByName(in.getUserName()); if (checkUserName(in.getUserName())) { in.setUid(uid); in.setStatus(ChannelStatus.FFMC.getCode()); in.setUserStatus(1); list3.add(in); }else if (u == null) { in.setUid(uid); in.setUserStatus(0); in.setStatus(ChannelStatus.WFP.getCode()); list.add(in); }else { //判断是自己的客户还是别人的,渠道不用此做判断 Integer i=0; if (u.getAid()!=null&&u.getAid().equals(aid)) { i=1; } // 共享类型 0-私有 1-公共 2 签单 if(u.getShareType()==0) { if(u.getChannel()==0) { if(i==1)in.setStatus(ChannelStatus.YCZWDSY.getCode()); else in.setStatus(ChannelStatus.YCZBRSY.getCode()); }else { UserChannel uc=userChannelMapper.selectByUid(u.getId()); if(uc.getAid().equals(aid)) in.setStatus(ChannelStatus.YCZWDQD.getCode()); else in.setStatus(ChannelStatus.YCZBRQD.getCode()); } } else if(u.getShareType()==1)in.setStatus(ChannelStatus.YCZGG.getCode()); else if(u.getShareType()==2) { if(i==1)in.setStatus(ChannelStatus.YCZWDQIAND.getCode()); else in.setStatus(ChannelStatus.YCZBRQIAND.getCode()); } in.setUid(u.getId()); list2.add(in); } } //检查重复名称 checkListName(list); checkListName(list2); checkListName(list3); //list成功录入 list2是已存在 list3是非法名称 3个处理方式不一样, //已存在则是不用新增直接关联,非法名称则不用关联也不用新增 if (!list.isEmpty()) { userMapper.insertBatch(list); userNamesMapper.insertBatch(list); organizationIdentityMapper.insertBatch(list); organizationContactBookMapper.insertBatch(list); userChannelMapper.insertBatch(list); userTransferLogMapper.insertBatch(list); } if(list2.isEmpty()&&list3.isEmpty()) { return 1; }else { if(!list3.isEmpty()){ userMapper.insertBatch(list3); organizationIdentityMapper.insertBatch(list3); organizationContactBookMapper.insertBatch(list3); list2.addAll(list3); } userChannelMapper.insertBatch(list2); return getErorrList(1,10); } } private void checkListName(List list) { for (InputUserChannel in : list) { int i=0; for (InputUserChannel in2 : list) { if (in.getUserName().equals(in2.getUserName())) { i++; if (i>1) { LoggerUtils.error(getClass(), String.format("客户名称:%s 重复录入", in.getUserName())); throw new BusinessException(String.format("客户名称:%s 重复录入", in.getUserName())); } } } } } /** * 获取Excel单元格的值 * * @param cell * @return */ public static String getCellValue(Cell cell) { String value = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case NUMERIC: //数字 value = cell.getNumericCellValue() + ""; if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd").format(date);//日期格式化 } else { value = ""; } } else { //在解析cell的时候,数字类型默认是double的,但是想要的的整数的类型,需要格式化,很重要 value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case STRING://字符串 value = cell.getStringCellValue(); break; case BOOLEAN://boolean类型 value = cell.getBooleanCellValue() + ""; break; case BLANK://空值 value = ""; break; case ERROR://错误类型 value = "非法字符"; break; default: value = "未知类型"; } } return value.trim(); } private boolean checkUserName(String userName) { String str = AFTConstants.CHANNEL_SENSITIVE; String[] ss=str.split(","); for (String s : ss) { if (userName!=null&&userName.contains(s)) { return true; } } return false; } private String getCityId(List cList, String cellValue) { for (CityBo cityBo : cList) { if (cityBo.getName().contains(cellValue)) { return cityBo.getId().toString(); } } return null; } private String getProvinceId(List pList, String cellValue) { for (ProvinceBo provinceBo : pList) { if (provinceBo.getName().contains(cellValue)) { return provinceBo.getId().toString(); } } return null; } @SuppressWarnings("unchecked") @Override public Pagination getErorrList(Integer pageNo,Integer pageSize) { Map map =new HashMap<>(); map.put("type", 1); map.put("aid", TokenManager.getAdminId()); return (Pagination) findPage("selectByAidAndstatusList", "selectByAidAndstatusCount", map, pageNo, pageSize); } @Override public Object delete(String ids) { String [] str=ids.split(","); List list=Arrays.asList(str); userChannelMapper.deleteUser(list); return userChannelMapper.deleteByIds(list); } }