snd-escloud/src/main/java/platform/modules/company/service/CompanyInfoManageService.java

package platform.modules.company.service;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
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.transaction.annotation.Transactional;

import com.aliyuncs.exceptions.ClientException;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.xiaoleilu.hutool.crypto.SecureUtil;

import org.springframework.util.CollectionUtils;
import platform.common.Constant;
import platform.common.base.service.BaseService;
import platform.common.base.service.DictionaryItemService;
import platform.common.base.service.DictionaryTypeService;
import platform.common.util.AlibabaSMSUtil;
import platform.common.util.DateUtil;
import platform.common.util.ExportExcel;
import platform.common.util.ShiroUtils;
import platform.common.util.WebUtil;
import platform.modules.build.DTO.CompanyDto;
import platform.modules.build.dao.CompanyContactDao;
import platform.modules.build.dao.CompanyDao;
import platform.modules.build.entity.BuildInfo;
import platform.modules.build.entity.Company;
import platform.modules.build.entity.CompanyContact;
import platform.modules.build.service.BuildInfoService;
import platform.modules.build.service.CompanyContactService;
import platform.modules.build.service.CompanyService;
import platform.modules.company.dto.CompanyInfoExportDto;
import platform.modules.company.dto.CompanyInfoManageDto;
import platform.modules.company.entity.CompanyInfoData;
import platform.modules.government.entity.BuildType;
import platform.modules.government.entity.Street;
import platform.modules.government.entity.User;
import platform.modules.government.service.BuildTypeService;
import platform.modules.government.service.StreetService;
import platform.modules.government.service.UserService;
import platform.modules.sys.entity.Approval;
import platform.modules.sys.entity.Role;
import platform.modules.sys.entity.UserRole;
import platform.modules.sys.service.ApprovalService;
import platform.modules.sys.service.RoleService;
import platform.modules.sys.service.UserRoleService;
import platform.modules.sys.web.ResponseMessage;
import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.entity.Example.Criteria;

@Service
@Transactional
public class CompanyInfoManageService extends BaseService<Company> {


    @Autowired
    private CompanyService companyService;

    @Autowired
    private CompanyContactService companyContactService;
    @Autowired
    private CompanyContactDao companyContactdao;

    @Autowired
    private BuildTypeService buildTypeService;

    @Autowired
    private CompanyDao companyDao;
    @Autowired
    private BuildInfoService buildInfoService;
    @Autowired
    private StreetService streetService;
    @Autowired
    private DictionaryTypeService dictionaryTypeService;
    @Autowired
    private DictionaryItemService dictionaryItemService;
    @Autowired
    private RoleService roleService;
    @Autowired
    private UserService userService;

    public PageInfo<Company> findPage(Integer pageNum, Integer pageSize,
                                      CompanyDto companyDto, CompanyInfoManageDto companyInfoManageDto) throws Exception {
        if (StringUtils.isNotBlank(companyInfoManageDto.getKcompany_name())) {
            companyInfoManageDto.setKcompany_name(companyInfoManageDto.getKcompany_name().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKuscc())) {
            companyInfoManageDto.setKuscc(companyInfoManageDto.getKuscc().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKorganization_code())) {
            companyInfoManageDto.setKorganization_code(companyInfoManageDto.getKorganization_code().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKindustry_code())) {
            companyInfoManageDto.setKindustry_code(companyInfoManageDto.getKindustry_code().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKlegal_representative())) {
            companyInfoManageDto.setKlegal_representative(companyInfoManageDto.getKlegal_representative().trim());
        }
        PageHelper.startPage(pageNum, pageSize);
        List<Company> contents = companyDao.findByCondition(companyInfoManageDto);
        for (Company company : contents) {
            if (null != company.getType_id()) {
                BuildType buildType = buildTypeService.findById(company.getType_id());
                if (null != buildType) {
                    company.setType_name(buildType.getType());
                }
            }
            company.setCompanyContacts(companyContactService.findByCompanyId(company.getId()));
            Integer build_id = company.getBuild_id();
            if (build_id != null) {
                BuildInfo info = buildInfoService.findById(build_id);
                if (null != info) {
                    company.setBuild_name(info.getName());
                }
            }
            Integer street_id = company.getStreet_id();
            if (street_id != null) {
                company.setStreet_name(streetService.findById(street_id).getName());
            }
        }
        return new PageInfo<Company>(contents);
    }

    public PageInfo<Company> findPages(Integer pageNum, Integer pageSize,
                                       CompanyDto companyDto, CompanyInfoManageDto companyInfoManageDto) throws Exception {
        if (StringUtils.isNotBlank(companyInfoManageDto.getKcompany_name())) {
            companyInfoManageDto.setKcompany_name(companyInfoManageDto.getKcompany_name().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKuscc())) {
            companyInfoManageDto.setKuscc(companyInfoManageDto.getKuscc().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKorganization_code())) {
            companyInfoManageDto.setKorganization_code(companyInfoManageDto.getKorganization_code().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKindustry_code())) {
            companyInfoManageDto.setKindustry_code(companyInfoManageDto.getKindustry_code().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKlegal_representative())) {
            companyInfoManageDto.setKlegal_representative(companyInfoManageDto.getKlegal_representative().trim());
        }
        PageHelper.startPage(pageNum, pageSize);
        List<Company> contents = companyDao.findByConditions(companyInfoManageDto);
        for (Company company : contents) {
            if (null != company.getType_id()) {
                BuildType buildType = buildTypeService.findById(company.getType_id());
                if (null != buildType) {
                    company.setType_name(buildType.getType());
                }
            }
            company.setCompanyContacts(companyContactService.findByCompanyId(company.getId()));
            Integer build_id = company.getBuild_id();
            if (build_id != null) {
                BuildInfo info = buildInfoService.findById(build_id);
                if (null != info) {
                    company.setBuild_name(info.getName());
                }
            }
            Integer street_id = company.getStreet_id();
            if (street_id != null) {
                company.setStreet_name(streetService.findById(street_id).getName());
            }
        }
        return new PageInfo<Company>(contents);
    }

    public PageInfo<Company> findPage2(Integer pageNum, Integer pageSize,
                                       CompanyDto companyDto, CompanyInfoManageDto companyInfoManageDto) throws Exception {
        if (StringUtils.isNotBlank(companyInfoManageDto.getKcompany_name())) {
            companyInfoManageDto.setKcompany_name(companyInfoManageDto.getKcompany_name().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKuscc())) {
            companyInfoManageDto.setKuscc(companyInfoManageDto.getKuscc().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKorganization_code())) {
            companyInfoManageDto.setKorganization_code(companyInfoManageDto.getKorganization_code().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKindustry_code())) {
            companyInfoManageDto.setKindustry_code(companyInfoManageDto.getKindustry_code().trim());
        }

        if (StringUtils.isNotBlank(companyInfoManageDto.getKlegal_representative())) {
            companyInfoManageDto.setKlegal_representative(companyInfoManageDto.getKlegal_representative().trim());
        }
        PageHelper.startPage(pageNum, pageSize);
        List<Company> contents = companyDao.findByCondition(companyInfoManageDto);
        for (Company company : contents) {
            if (null != company.getType_id()) {
                BuildType buildType = buildTypeService.findById(company.getType_id());
                if (null != buildType) {
                    company.setType_name(buildType.getType());
                }
            }
            company.setCompanyContacts(companyContactService.findByCompanyId(company.getId()));
            Integer build_id = company.getBuild_id();
            if (build_id != null) {
                company.setBuild_name(buildInfoService.findById(build_id).getName());
            }
            Integer street_id = company.getStreet_id();
            if (street_id != null) {
                company.setStreet_name(streetService.findById(street_id).getName());
            }
        }
        return new PageInfo<Company>(contents);
    }

    /**
     * 新增企业信息
     */
    @Transactional(readOnly = false)
    public Boolean saveCompanyInfo(Company information) throws Exception {
        //information.setBuild_id(ShiroUtils.getUserEntity().getBuild_id());
        //information.setStreet_id(ShiroUtils.getUserEntity().getStreet_id());
        //新增企业信息
        information.setIs_start(true);
        information.setNature(1);
        information.setIs_register(1);
        boolean flag = companyService.saveSelective(information) > 0 ? true : false;
        if (flag) {
            List<CompanyContact> companyContactList = information.getCompanyContacts();
            Calendar cal = Calendar.getInstance();
            int y = cal.get(Calendar.YEAR);
            int m = cal.get(Calendar.MONTH);
            int d = cal.get(Calendar.DATE);
            int h = cal.get(Calendar.HOUR_OF_DAY);
            int mi = cal.get(Calendar.MINUTE);
            int s = cal.get(Calendar.SECOND);
            if (companyContactList != null && companyContactList.size() > 0) {
                for (int i = 0; i < companyContactList.size(); i++) {
                    //2018-04-17 19:02:33
                    String create_time = "";
                    if (mi < 10) {
                        mi += 1;
                        create_time = y + "-" + m + "-" + d + " " + h + ":0" + mi + ":" + s;
                    } else {
                        mi += 1;
                        create_time = y + "-" + m + "-" + d + " " + h + ":" + mi + ":" + s;
                    }

                    Integer company_id = information.getId();
                    String contact = companyContactList.get(i).getContact();
                    String phone = companyContactList.get(i).getPhone();
                    String position = companyContactList.get(i).getPosition();
                    Integer create_by = ShiroUtils.getUserId();
                    String create_name = ShiroUtils.getUserName();
                    companyContactdao.insertInfo(company_id, contact, phone, position, create_time, create_by, create_name);
                }
            }
            //return true;
        }
        //为企业新增企业管理员，用户名：组织机构代码、密码：123456、角色：企业管理员
        String init_password = "";
        String organization_code = information.getOrganization_code();
        User user = new User();
        if (StringUtils.isNotBlank(organization_code)) {
            user.setNick_name(organization_code);
            user.setUser_name(organization_code);
            String passWord = organization_code.substring(3, organization_code.length());
            init_password = passWord;
            user.setPassword(SecureUtil.md5().digestHex(passWord));
        } else {
            init_password = "123456";
            String phone = information.getCompanyContacts().get(0).getPhone();
            user.setNick_name(phone);
            user.setUser_name(phone);
            user.setPassword(SecureUtil.md5().digestHex("123456"));
        }
        user.setIs_start(true);
        if (null != information.getBuild_id()) {
            user.setBuild_id(information.getBuild_id());
            user.setBuild_name(buildInfoService.findById(information.getBuild_id()).getName());
        }
        user.setStreet_id(information.getStreet_id());
        user.setStreet_name(streetService.findById(information.getStreet_id()).getName());
        //user.setUser_type(Constant.UserType.COMPANY);
        user.setIs_admin(true);
        user.setUser_type(Constant.UserType.COMPANY);
        user.setCompany_id(companyService.findByCompanyName(information.getCompany_name()).getId());
        user.setCompany_name(information.getCompany_name());
        user.setCreate_by(ShiroUtils.getUserEntity().getId());
        user.setCreate_name(ShiroUtils.getUserEntity().getNick_name());
        userService.saveSelective(user);

        sendSMSToNewCompanyUser(init_password, user, information);

        return true;
    }

    //发送短信给企业用户，账号新增成功
    private void sendSMSToNewCompanyUser(String init_password, User user, Company company) throws ClientException, InterruptedException {
        //发送短信给企业用户，账号新增成功
        //您的企业  ${company_name} 平台账号名为${nick_name},初始密码为${initPassword},首次登录后，请修改密码。感谢您的支持。
        String company_name = user.getCompany_name();
        String phone = company.getCompanyContacts().get(0).getPhone();
        String nick_name = user.getNick_name();
        String templateParam = "{\"nick_name\":\"" + nick_name + "\",\"initPassword\":\"" + init_password + "\",\"company_name\":\"" + company_name + "\"}";
        AlibabaSMSUtil.sendSMS(phone, Constant.SMS_TemplateCode.NEW_COMPANY_USER, Constant.SINGNAMW, templateParam);
    }

    /**
     * 删除企业信息
     */
    public ResponseMessage delInfo(Integer id) {
        boolean flag = false;
        if (id != null) {
            Company findById = companyService.findById(id);
            findById.setDel_flag(true);
            flag = companyService.updateSelective(findById) > 0 ? true : false;
            companyContactService.deleteByCompanyId(id);
        }
        if (flag) {
            return ResponseMessage.success("删除成功");
        } else {
            return ResponseMessage.error("删除失败");
        }
    }

    /**
     * 启用企业信息
     */
    public ResponseMessage startBatch(String[] ids) {
        try {
            for (String id : ids) {
                Company findById = companyService.findById(Integer.parseInt(id));
                findById.setIs_start(true);
                companyService.updateSelective(findById);
            }
            return ResponseMessage.success("启用成功");
        } catch (Exception e) {
            e.printStackTrace();
            return ResponseMessage.error("启用失败");
        }
    }

    /**
     * 禁用企业信息
     */
    public ResponseMessage stopBatch(String[] ids) {
        try {
            for (String id : ids) {
                Company findById = companyService.findById(Integer.parseInt(id));
                findById.setIs_start(false);
                companyService.updateSelective(findById);
            }
            return ResponseMessage.success("禁用成功");
        } catch (Exception e) {
            e.printStackTrace();
            return ResponseMessage.error("禁用失败");
        }
    }

    /**
     * 修改企业信息
     */
    public ResponseMessage editInfo(Integer id, Company information) {
        boolean flag = false;
        companyContactService.deleteByCompanyId(id);
        flag = companyService.updateSelective(information) > 0 ? true : false;
        if (!CollectionUtils.isEmpty(information.getCompanyContacts())) {
            for (CompanyContact companyContact : information.getCompanyContacts()) {
                companyContact.setCompany_id(information.getId());
                companyContactService.saveSelective(companyContact);
            }
        }
        User user = new User();
        user.setStreet_name(information.getStreet_name());
        user.setStreet_id(information.getStreet_id());
        userService.updateByCompanyId(id.toString(), user);
        if (flag) {
            return ResponseMessage.success("编辑成功");
        } else {
            return ResponseMessage.error("编辑失败");
        }

    }

    /**
     * 多条件模糊查询企业信息
     */
    public PageInfo<Company> finByCondition(Integer pageNum, Integer pagesize,
                                            CompanyInfoManageDto companyInfoManageDto) {
        PageHelper.startPage(pageNum, pagesize);
        List<Company> companies = companyDao.findByCondition(companyInfoManageDto);
        return new PageInfo<Company>(companies);
    }

    //导出所有企业信息数据
    public List<Company> findExportCompany(CompanyInfoManageDto companyInfoManageDto) {
        List<Company> cList = null;
        if (ShiroUtils.getUserType().equals(Constant.UserType.STREET)) {
            companyInfoManageDto.setStreet_id(ShiroUtils.getStreetId());
            cList = companyDao.findByCondition(companyInfoManageDto);
        } else if (ShiroUtils.getUserType().equals(Constant.UserType.GOVERNMENT)) {
            cList = companyDao.findByCondition(companyInfoManageDto);
        }
        for (Company company : cList) {
            if (company.getBuild_id() != null) {
                String buildName = "";
                BuildInfo buildInfo = buildInfoService.findById(company.getBuild_id());
                if (buildInfo != null){
                    buildName = buildInfo.getName();
                }
                company.setBuild_name(buildName);
            }
            if (company.getStreet_id() != null) {
                String streetName = "";
                Street street = streetService.findById(company.getStreet_id());
                if (street != null){
                    streetName = street.getName();
                }
                company.setStreet_name(streetName);
            }
            if (company.getIs_start() == true) {
                company.setIs_start_name("启用");
            } else {
                company.setIs_start_name("禁用");
            }
        }
        return cList;
    }

    //导出所有企业信息数据
    public List<Company> findExportCompanyByStreet(CompanyInfoManageDto companyInfoManageDto) {
        List<Company> cList = companyDao.findByCondition(companyInfoManageDto);
        for (Company company : cList) {
            if (company.getBuild_id() != null) {
                company.setBuild_name(buildInfoService.findById(company.getBuild_id()).getName());
            }
            if (company.getStreet_id() != null) {
                company.setStreet_name(streetService.findById(company.getStreet_id()).getName());
            }
            if (company.getIs_start() == true) {
                company.setIs_start_name("启用");
            } else {
                company.setIs_start_name("禁用");
            }
        }

        return cList;
    }

    /**
     * 获取单元格值
     */
    private static String getCellValue(Cell cell, int index, String thead) throws Exception {
        String value = null;
        if (cell != null) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            value = cell.getStringCellValue();
            if (StringUtils.isNotBlank(value)) {
                return value;
            }
        }
        return value;
        //throw new Exception("第"+(index+1)+"行"+thead+"不能为空");
    }

    /**
     * 批量导入企业信息
     *
     * @throws Exception
     */
    public void importCompanyInfoData(InputStream inputStream, Workbook wb) throws Exception {
        List<CompanyInfoData> dataList = null;
        //街道用户导入
        if (ShiroUtils.getUserType().equals(Constant.UserType.GOVERNMENT)) {
            dataList = getExcelData(wb);
        } else if (ShiroUtils.getUserType().equals(Constant.UserType.STREET)) {
            Street street = streetService.selectById(ShiroUtils.getStreetId());
            dataList = getExcelDataOnStreet(wb, street.getName());
        }

        validData(dataList);
        saveData(dataList);
    }

    /**
     * isValidDate(判断一个字符串是否符合yyyy/MM/dd的时间格式)
     *
     * @param s
     * @return boolean
     * @throws
     */
    public static boolean isValidDate(String s) {
        boolean flag = false;
        if (StringUtils.isNotBlank(s)) {
            if (s.length() > 10) {
                return flag;
            }
        } else {
            flag = true;
            return flag;
        }
        try {
            // 指定日期格式为四位年/两位月份/两位日期，注意yyyy-MM-dd其中MM为大写
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
            // 设置lenient为false. 否则SimpleDateFormat会比较宽松地验证日期，比如2004/02/29会被接受，并转换成2004/03/01
            dateFormat.setLenient(false);
            Date parse = dateFormat.parse(s);
            flag = true;
            return flag;
        } catch (Exception e) {
            // 如果throw java.text.ParseException或者NullPointerException，就说明格式不对
            e.printStackTrace();
            flag = false;
            return flag;
        }
    }

    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 读取数据(政府用户)
     *
     * @throws Exception
     */
    private List<CompanyInfoData> getExcelData(Workbook wb) throws Exception {
        List<CompanyInfoData> dataList = new ArrayList<>();
        List<String> nameList = new ArrayList<>();
        nameList.clear();
        Row row = null;
        Cell cell = null;
        Sheet sheet = (Sheet) wb.getSheetAt(0);
        int tatalRows = sheet.getPhysicalNumberOfRows();
        //sheet.getr
        if (tatalRows == 2) {
            throw new Exception("请添加企业信息数据");
        }

        //从一行读取数据
        for (int r = 2; r < tatalRows; r++) {
            //获取行数据
            row = sheet.getRow(r);
            cell = row.getCell(0);
            String cellValue = getCellValue(cell, r, "序号");
            if (StringUtils.isBlank(cellValue)) {
                break;
            }
            CompanyInfoData com = new CompanyInfoData();
            cell = row.getCell(1);
            com.setUscc(getCellValue(cell, r, "统一社会信用代码"));
            cell = row.getCell(2);
            //String organization_code = getCellValue(cell,r,"组织机构代码");
            com.setOrganization_code(getCellValue(cell, r, "组织机构代码"));
            cell = row.getCell(3);
            String company_name = getCellValue(cell, r, "企业名称");
            if (nameList.size() > 0 && StringUtils.isNotBlank(company_name) && nameList.contains(company_name)) {
                //System.out.println(cellValue + "--------------->" + organization_code + "=================================================================================================");
                throw new Exception("企业名称为：" + company_name + "，企业已经存在,请修改企业名称！");
            }
            com.setCompany_name(getCellValue(cell, r, "企业名称"));
            cell = row.getCell(4);
            com.setLegal_Representative(getCellValue(cell, r, "法人代表人"));
            cell = row.getCell(5);
            com.setIndustry_Code(getCellValue(cell, r, "行业代码"));
            cell = row.getCell(6);
            com.setType_name(getCellValue(cell, r, "企业性质"));
            cell = row.getCell(7);
            com.setStreet_name(getCellValue(cell, r, "所属区域"));
            cell = row.getCell(8);
            com.setBuild_name(getCellValue(cell, r, "所属工业区"));
            cell = row.getCell(9);
            com.setRelation_Name(getCellValue(cell, r, "管理部门"));
            cell = row.getCell(10);
            com.setRegistration_Capital(getCellValue(cell, r, "注册资本"));
            cell = row.getCell(11);
//			String date = cell.getStringCellValue();
            boolean flag = isValidDate(getCellValue(cell, r, "注册日期"));
            if (flag) {

                com.setEstablishment_date(getCellValue(cell, r, "注册日期"));
            } else {
                throw new Exception("时间格式非法");
            }
            cell = row.getCell(12);
            com.setLocation(getCellValue(cell, r, "地址"));
            cell = row.getCell(13);
            com.setBusiness(getCellValue(cell, r, "经营范围"));
            cell = row.getCell(14);
            com.setType_id_name(getCellValue(cell, r, "是否工业企业"));
            cell = row.getCell(15);
            com.setIs_product_name(getCellValue(cell, r, "是否经营"));
            nameList.add(company_name);
            dataList.add(com);
        }
        return dataList;
    }

    /**
     * 读取数据(街道用户)
     *
     * @throws Exception
     */
    private List<CompanyInfoData> getExcelDataOnStreet(Workbook wb, String Sname) throws Exception {
        List<CompanyInfoData> dataList = new ArrayList<>();
        List<String> nameList = new ArrayList<>();
        nameList.clear();
        Row row = null;
        Cell cell = null;
        Sheet sheet = (Sheet) wb.getSheetAt(0);
        int tatalRows = sheet.getPhysicalNumberOfRows();
        if (tatalRows == 2) {
            throw new Exception("请添加企业信息数据");
        }
        //从一行读取数据
        for (int r = 2; r < tatalRows; r++) {
            //获取行数据
            row = sheet.getRow(r);
            cell = row.getCell(0);
            String cellValue = getCellValue(cell, r, "序号");
            if (StringUtils.isBlank(cellValue)) {
                break;
            }
            CompanyInfoData com = new CompanyInfoData();
            cell = row.getCell(1);
            com.setUscc(getCellValue(cell, r, "统一社会信用代码"));
            cell = row.getCell(2);
            com.setOrganization_code(getCellValue(cell, r, "组织机构代码"));
            cell = row.getCell(3);
            String company_name = getCellValue(cell, r, "企业名称");
            if (nameList.size() > 0 && StringUtils.isNotBlank(company_name) && nameList.contains(company_name)) {
                throw new Exception("企业名称为：" + company_name + "，企业已经存在,请修改企业名称！");
            }
            com.setCompany_name(getCellValue(cell, r, "企业名称"));
            cell = row.getCell(4);
            com.setLegal_Representative(getCellValue(cell, r, "法人代表人"));
            cell = row.getCell(5);
            com.setIndustry_Code(getCellValue(cell, r, "行业代码"));
            cell = row.getCell(6);
            com.setType_name(getCellValue(cell, r, "企业性质"));
            cell = row.getCell(7);
            //街道名称
            String street_name = getCellValue(cell, r, "所属区域");
            if (StringUtils.isBlank(Sname)) {
                com.setStreet_name(Sname);
            }
            if (!street_name.equals(Sname)) {
                throw new Exception("企业名称为：" + company_name + ", 所属区域有误，请修改为： " + Sname);
            }
            com.setStreet_name(getCellValue(cell, r, "所属区域"));
            cell = row.getCell(8);
            com.setBuild_name(getCellValue(cell, r, "所属工业区"));
            cell = row.getCell(9);
            com.setRelation_Name(getCellValue(cell, r, "管理部门"));
            cell = row.getCell(10);
            com.setRegistration_Capital(getCellValue(cell, r, "注册资本"));
            cell = row.getCell(11);
//			String date = cell.getStringCellValue();
            boolean flag = isValidDate(getCellValue(cell, r, "注册日期"));
            if (flag) {

                com.setEstablishment_date(getCellValue(cell, r, "注册日期"));
            } else {
                throw new Exception("时间格式非法");
            }
            cell = row.getCell(12);
            com.setLocation(getCellValue(cell, r, "地址"));
            cell = row.getCell(13);
            com.setBusiness(getCellValue(cell, r, "经营范围"));
            cell = row.getCell(14);
            com.setType_id_name(getCellValue(cell, r, "是否工业企业"));
            cell = row.getCell(15);
            com.setIs_product_name(getCellValue(cell, r, "是否经营"));
            nameList.add(company_name);
            dataList.add(com);
        }
        return dataList;
    }

    /**
     * isNumAndLetter(判断一个字符串是否由数字和字母组成)
     *
     * @param str
     * @return boolean
     * @throws
     */
    public boolean isNumAndLetter(String str) {
        boolean flag = false;
        Pattern pattern = Pattern.compile("^[A-Za-z0-9]+$*");
        Matcher matcher = pattern.matcher(str);
        flag = matcher.matches();
        return flag;
    }

    /**
     * isNum(判断一个字符串是否由数字组成)
     *
     * @param str
     * @return boolean
     * @throws
     */
    public boolean isNum(String str) {
        boolean flag = false;
        Pattern pattern = Pattern.compile("^[0-9]+$*");
        Matcher matcher = pattern.matcher(str);
        flag = matcher.matches();
        return flag;
    }

    /**
     * isNumAndLetter(判断一个实数是否包含两位小数组成)
     *
     * @param str
     * @return boolean
     * @throws
     */
    public boolean containNumAndPoint(String str) {
        boolean flag = false;
        Pattern pattern = Pattern.compile("^[0-9]+(.[0-9]{1,5})?$");
        Matcher matcher = pattern.matcher(str);
        flag = matcher.matches();
        return flag;
    }


    /**
     * 校验企业信息数据是否合法
     */
    private void validData(List<CompanyInfoData> dataList) throws Exception {
        //查询数据库表中所有街道、园区
        //List<String> streetNameList =  streetService.findAllStreeName();
        for (CompanyInfoData companyInfoData : dataList) {
            //组织机构代码为空时，此条数据不校验
            String organization_code = companyInfoData.getOrganization_code();
            //判断数据库中是否存在被导入的企业
            String company_name = companyInfoData.getCompany_name();
            if (StringUtils.isBlank(organization_code)) {
                throw new Exception("企业名称为：" + company_name + " 的" + " “组织机构代码” " + "为空");
                //continue;
            }

//			if (companyService.isCompanyExist(company_name, organization_code)) {
//				continue;
//			}

            //项目名称限制为50个字符
            if (company_name.length() >= 50) {
                throw new Exception("企业名称限制为50个字符长度");
            }

            //判断项目名称是否为空
			/*if (StringUtils.isBlank(company_name)) {
				throw new Exception("企业名称为：" + company_name + "，企业已经存在");
			}*/
            //判断项目名称是否存在
            if (companyService.isCompanyNameExist(company_name)) {
                throw new Exception("企业名称为：" + company_name + "，企业已经存在");
            }

            String uscc = companyInfoData.getUscc();
            //判断社会统一社会代码是否存在
            if (StringUtils.isNotBlank(uscc)) {
                if (companyService.isCompanyUsccExist(uscc)) {
                    throw new Exception("企业名称为：" + company_name + " 的" + " “社会统一信用代码” " + "已经存在");
                }
                //判断社会统一代码的长度，是否由数字和字母走成（18位长度）
                if ((uscc.length() != 18) || (!isNumAndLetter(uscc))) {
                    throw new Exception("企业名称为：" + company_name + " 的" + " “社会统一信用代码” " + "非法");
                }
            }

            //判断组织机构代码是否存在
            if (StringUtils.isNotBlank(organization_code)) {
                if (companyService.isCompanyOrganizationCodeExist(organization_code, null)) {
                    throw new Exception("企业名称为：" + company_name + " 的" + " “组织机构代码” " + "已经存在");
                }
                //判断组织机构代码的长度，是否由数字和字母组成（大于6位长度）
                if (((organization_code.length() != 9)) || (!isNumAndLetter(organization_code))) {
                    throw new Exception("企业名称为：" + company_name + " 的" + " “组织机构代码” " + "非法");
                }
            }
            //判断行业代码是否存在
            String industry_code = companyInfoData.getIndustry_Code();
            if (StringUtils.isNotBlank(industry_code)) {
				/*if (companyService.isCompanyIndustryCodeExist(industry_code)) {
					throw new Exception("企业名称为：" + company_name + " 的行业代码已经存在");
				}*/
                //判断行业代码的长度，是否只包含数字（4长度）
                if ((industry_code.length() != 4) || (!isNum(industry_code))) {
                    throw new Exception("企业名称为：" + company_name + " 的" + " “行业代码” " + "非法");
                }
            }
            //判断注册资金是否合法
            if (StringUtils.isNotBlank(companyInfoData.getRegistration_Capital())) {
                double registration_Capital = 0.0;
                try {
                    //if (StringUtils.isNotBlank(companyInfoData.getRegistration_Capital())) {
                    registration_Capital = Double.parseDouble(companyInfoData.getRegistration_Capital());
                    //}
                } catch (Exception e) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + companyInfoData.getRegistration_Capital() + " 的" + " “注册资金” " + "非法");
                }
                if (companyInfoData.getRegistration_Capital().length() > 14 || registration_Capital < 0 ||
                        !containNumAndPoint(companyInfoData.getRegistration_Capital())) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + companyInfoData.getRegistration_Capital() + " 的" + " “注册资金” " + "小数部分保留超过5位");
                }
            }
            //校验街道是否存在
            if (StringUtils.isNotBlank(companyInfoData.getStreet_name())) {
                String street_name = companyInfoData.getStreet_name();
                Street street = streetService.getStreet(street_name);
                if (street == null) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + companyInfoData.getStreet_name() + " 的" + " “所属区域” " + "不存在");
                }
            }
			/*if (StringUtils.isNotBlank(companyInfoData.getStreet_name())) {
				if (!streetNameList.contains(companyInfoData.getStreet_name())) {
					Street street = new Street();
					street.setDel_flag(false);
					street.setIs_start(true);
					street.setName(companyInfoData.getStreet_name());
					street.setCreate_by(ShiroUtils.getUserEntity().getId());
					street.setCreate_name(ShiroUtils.getUserEntity().getUser_name());
				}
			}*/
            //校验园区是否存在
            if (StringUtils.isNotBlank(companyInfoData.getBuild_name())) {
                String build_name = companyInfoData.getBuild_name();
                BuildInfo build = buildInfoService.getBuildByName(build_name);
                if (build == null) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + companyInfoData.getBuild_name() + " 的" + " “所属工业园区” " + "不存在");
                }
            }
        }

    }

    /**
     * 保存数据
     *
     * @throws Exception
     */
    private void saveData(List<CompanyInfoData> dataList) throws Exception {
        for (CompanyInfoData data : dataList) {
            //判断数据库中是否存在被导入的企业,如果存在不保存，否则保存
            String company_name = data.getCompany_name();
            String organization_code = data.getOrganization_code();
            if (companyService.isCompanyExist(company_name, organization_code)) {
                continue;
            }
            Company com = new Company();
            //社会统一信用代码
            //if (companyService.isCompanyUsccExist(data.getUscc())) {
            //throw new Exception("企业名称为：" + company_name + " 的"+" “社会统一信用代码” "+"已经存在");
            //}else {
            com.setUscc(data.getUscc());
            //}
            //组织机构代码
            //if (companyService.isCompanyOrganizationCodeExist(data.getOrganization_code())) {
            //	throw new Exception("企业名称为：" + company_name + " 的"+" “组织机构代码” "+"已经存在");
            //}else {
            com.setOrganization_code(data.getOrganization_code());
            //}
            //项目名称
            //if (companyService.isCompanyNameExist(company_name)) {
            //	throw new Exception("企业名称为：" + company_name + " 的"+" “企业名称” "+"已经存在");
            //}else {
            com.setCompany_name(data.getCompany_name());
            //}
            //法定代表人
            com.setLegal_representative(data.getLegal_Representative());
            //行业代码
			/*if (companyService.isCompanyIndustryCodeExist(data.getIndustry_Code())) {
				throw new Exception("企业名称为：" + company_name + " 的"+" “行业代码” "+"已经存在");
			}else {*/
//			}
            //行业代码可重复，不必校验
            com.setIndustry_code(data.getIndustry_Code());


            //企业性质
			/*Integer tempId = dictionaryItemService.findIdByTypeAndName(Constant.DictionaryType.COMPANY_TYPE, data.getType_name());
			if (tempId == null) {
				throw new Exception("企业名称为："+company_name+"，且值为："+data.getType_name()+" 的"+" “企业性质” "+"不存在");
			}else {
				com.setType(tempId);
			}*/

            //******************** 街道，园区校验 **********************//
            if (StringUtils.isBlank(data.getStreet_name())) {
                com.setStreet_id(null);
            } else {
                try {
                    com.setStreet_id(streetService.getStreet(data.getStreet_name()).getId());
                } catch (Exception e) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + data.getStreet_name() + " 的" + " “所属区域” " + "不存在");
                }
            }


            if (StringUtils.isBlank(data.getBuild_name())) {
                com.setBuild_id(null);
            } else {
                try {
                    BuildInfo buildInfo = buildInfoService.getBuildByName(data.getBuild_name());
                    com.setBuild_id(buildInfo.getId());
                } catch (Exception e) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + data.getBuild_name() + " 的" + " “所属工业区” " + "不存在");
                }

            }
            //******************** 街道，园区比校验 **********************//

            //管理部门
            com.setRelation_name(data.getRelation_Name());
            //注册资本
            if (StringUtils.isNotBlank(data.getRegistration_Capital())) {
                com.setRegistration_capital(data.getRegistration_Capital());
            } else {
                com.setRegistration_capital(null);
            }
            //注册日期
            com.setEstablishment_date(data.getEstablishment_date());
            //地址
            com.setLocation(data.getLocation());
            //经营范围
            com.setBusiness(data.getBusiness());
            //是否是工业
			/*if (data.getType_id_name().equals("是")) {
				com.setType_id(1);
			}else {
				com.setType_id(0);
			}*/
            //是否生产
			/*if (data.getIs_product_name().equals("是")) {
				com.setIs_product(true);
			}else {
				com.setIs_product(false);
			}*/
            //企业编码
            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMddHHmmssSSS");
            String format = sdf1.format(new Date());
            Random r = new Random();
            String no = "";
            for (int i = 0; i < 3; i++) {
                no += r.nextInt(10);
            }

            com.setNo("SNDE" + format + no);
            //客户性质（默认企业客户）
            com.setNature(1);
            //货币单位，默认（人民币）
            com.setCurrency_unit(1);
            com.setIs_start(true);
            com.setDel_flag(false);
            com.setIs_register(1);
            companyService.saveSelective(com);

            //为企业新增管理员，用户名：组织机构代码、密码：123456、角色：企业管理员
            User user = new User();
            user.setNick_name(organization_code);
            user.setUser_name(organization_code);
            String passWord = organization_code.substring(3, organization_code.length());
            user.setPassword(SecureUtil.md5().digestHex(passWord));
            user.setIs_start(true);
            user.setIs_admin(true);
            user.setUser_type(Constant.UserType.COMPANY);
            if (StringUtils.isNotBlank(data.getBuild_name())) {
                try {
                    BuildInfo buildInfo = buildInfoService.getBuildByName(data.getBuild_name());
                    user.setBuild_id(buildInfo.getId());
                    user.setBuild_name(buildInfo.getName());
                } catch (Exception e) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + data.getBuild_name() + " 的" + " “所属工业区” " + "不存在");
                }
            } else {
                user.setBuild_id(null);
                user.setBuild_name(null);
            }
            if (StringUtils.isNotBlank(data.getStreet_name())) {
                try {
                    Street street = streetService.getStreet(data.getStreet_name());
                    user.setStreet_id(street.getId());
                    user.setStreet_name(street.getName());
                } catch (Exception e) {
                    throw new Exception("企业名称为：" + company_name + "，且值为：" + data.getStreet_name() + " 的" + " “所属区域” " + "不存在");
                }

            } else {
                user.setStreet_id(null);
                user.setStreet_name(null);
            }
            if (StringUtils.isNotBlank(data.getCompany_name())) {
                try {
                    Company companyName = companyService.findByCompanyName(data.getCompany_name());
                    user.setCompany_id(companyName.getId());
                    user.setCompany_name(companyName.getCompany_name());
                } catch (Exception e) {
                    throw new Exception("系统错误！");
                }

            } else {
                user.setCompany_id(null);
                user.setCompany_name(null);
            }
            user.setCreate_by(ShiroUtils.getUserEntity().getId());
            user.setCreate_name(ShiroUtils.getUserEntity().getNick_name());
            userService.saveSelective(user);
            //发送短信给企业用户，账号新增成功
            //sendSMSToNewCompanyUser(passWord,user,com);
        }
    }

    public String createCompanyNo(){
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMddHHmmssSSS");
        String format = sdf1.format(new Date());
        Random r = new Random();
        StringBuilder no = new StringBuilder();
        for (int i = 0; i < 3; i++) {
            no.append(r.nextInt(10));
        }
        return "SNDE" + format + no;
    }


    public PageInfo<Company> findRegisterPage(Integer pageNum, Integer pageSize, String keyword, Company searchCondition) {

        //根据当前用户userId查询和当前用户同一部门，审核层级小于当前用户的UserId
        //List<Integer> userIdList = userService.findUserIdsByUserId(ShiroUtils.getUserId());

        Integer currentStreet_id = ShiroUtils.getUserEntity().getStreet_id();
        PageHelper.startPage(pageNum, pageSize);
		/*Company company1 = new Company();
		company1.setIs_register(0);
		company1.setStreet_id(currentStreet_id);*/
        //List<Company> list = companyService.findListByContidion(ShiroUtils.getUserId(),currentStreet_id);
        /* 20180418-yl- 不分审批人 街道可看见所有数据*/
        List<Company> list = companyService.findListByContidion(null, currentStreet_id, keyword, searchCondition);
        for (Company company : list) {
			/*Approval approval = approvalService.getLatestApproval(company.getId());
			if(null != approval && StringUtils.isNotBlank(approval.getNextchecker())){
				String nextChecker [] = null == approval.getNextchecker() ?null:approval.getNextchecker().split(",");
				for(Integer userId:userIdList){
					if(Arrays.asList(nextChecker).contains(String.valueOf(userId))){
						company.setIs_approval(0);
						break;
					}
				}
			}*/
            if (null != company.getType_id()) {
                BuildType buildType = buildTypeService.findById(company.getType_id());
                if (null != buildType) {
                    company.setType_name(buildType.getType());
                }
            }
            company.setCompanyContacts(companyContactService.findByCompanyId(company.getId()));
            Integer build_id = company.getBuild_id();
            if (build_id != null) {
                BuildInfo info = buildInfoService.findById(build_id);
                if (null != info) {
                    company.setBuild_name(info.getName());
                }
            }
            Integer street_id = company.getStreet_id();
            if (street_id != null) {
                company.setStreet_name(streetService.findById(street_id).getName());
            }
        }
        return new PageInfo<Company>(list);
    }

    public PageInfo<Company> findAlready_RegisterPage(Integer pageNum, Integer pagesize, String keyword, Company searchCondition) {
        Integer currentStreet_id = ShiroUtils.getUserEntity().getStreet_id();
        PageHelper.startPage(pageNum, pagesize);
		/*Company company1 = new Company();
		company1.setStreet_id(currentStreet_id);
		List<Company> list = companyService.findListByWhere(company1);*/
        //Integer currentUserId = ShiroUtils.getUserId();
        Integer currentUserId = null;
        List<Company> list = companyService.findCompanyByCondition(currentStreet_id, currentUserId, keyword, searchCondition);

        for (Company company : list) {
            if (null != company.getType_id()) {
                BuildType buildType = buildTypeService.findById(company.getType_id());
                if (null != buildType) {
                    company.setType_name(buildType.getType());
                }
            }
            company.setCompanyContacts(companyContactService.findByCompanyId(company.getId()));
            Integer build_id = company.getBuild_id();
            if (build_id != null) {
                BuildInfo info = buildInfoService.findById(build_id);
                if (null != info) {
                    company.setBuild_name(info.getName());
                }
            }
            Integer street_id = company.getStreet_id();
            if (street_id != null) {
                company.setStreet_name(streetService.findById(street_id).getName());
            }
        }
        return new PageInfo<Company>(list);
    }
}