<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jeeplus.assess.workClientInfo.mapper.WorkClientInfoMapper">

	<sql id="Base_Column_List">
        a.id,
        a.create_by_id,
        a.create_time,
        a.update_by_id,
        a.update_time,
        a.del_flag,
        a.name,
        a.lawer_presint,
        a.or_unicode,
        a.company_type,
        a.company_industry,
        a.client_type,
        a.credit_rank,
        a.area_id,
        a.zip_code,
        a.fax,
        a.tax_id,
        a.email,
        a.address,
        a.register_address,
        a.company_url,
        a.remarks,
        a.company_id,
        a.office_id,
        a.area_parent_ids,
        a.telephone,
        a.has_uscc,
        a.usc_code,
        a.number
    </sql>
	<resultMap id="BaseResultMap" type="com.jeeplus.assess.workClientInfo.domain.WorkClientInfo">
		<id property="id" column="id" jdbcType="VARCHAR"/>
		<result property="createById" column="create_by_id" jdbcType="VARCHAR"/>
		<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
		<result property="updateById" column="update_by_id" jdbcType="VARCHAR"/>
		<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
		<result property="delFlag" column="del_flag" jdbcType="CHAR"/>
		<result property="name" column="name" jdbcType="VARCHAR"/>
		<result property="lawerPresint" column="lawer_presint" jdbcType="VARCHAR"/>
		<result property="orUnicode" column="or_unicode" jdbcType="VARCHAR"/>
		<result property="companyType" column="company_type" jdbcType="VARCHAR"/>
		<result property="companyIndustry" column="company_industry" jdbcType="VARCHAR"/>
		<result property="clientType" column="client_type" jdbcType="VARCHAR"/>
		<result property="creditRank" column="credit_rank" jdbcType="VARCHAR"/>
		<result property="areaId" column="area_id" jdbcType="VARCHAR"/>
		<result property="zipCode" column="zip_code" jdbcType="VARCHAR"/>
		<result property="fax" column="fax" jdbcType="VARCHAR"/>
		<result property="taxId" column="tax_id" jdbcType="VARCHAR"/>
		<result property="email" column="email" jdbcType="VARCHAR"/>
		<result property="address" column="address" jdbcType="VARCHAR"/>
		<result property="registerAddress" column="register_address" jdbcType="VARCHAR"/>
		<result property="companyUrl" column="company_url" jdbcType="VARCHAR"/>
		<result property="remarks" column="remarks" jdbcType="VARCHAR"/>
		<result property="companyId" column="company_id" jdbcType="VARCHAR"/>
		<result property="officeId" column="office_id" jdbcType="VARCHAR"/>
		<result property="areaParentIds" column="area_parent_ids" jdbcType="VARCHAR"/>
		<result property="telephone" column="telephone" jdbcType="VARCHAR"/>
		<result property="hasUscc" column="has_uscc" jdbcType="TINYINT"/>
		<result property="uscCode" column="usc_code" jdbcType="VARCHAR"/>
		<result property="number" column="number" jdbcType="VARCHAR"/>
		<result property="areaName" column="sa_name" jdbcType="VARCHAR"/>

	</resultMap>
	<select id="componentList" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"></include>,
		sa.name sa_name
		from work_client_info a
		left join sys_area sa on sa.id = a.area_id and sa.del_flag = '0'
		${ew.customSqlSegment}
		order by a.update_time desc
	</select>
	<select id="findPageList" resultType="com.jeeplus.assess.workClientInfo.domain.WorkClientInfo">
		SELECT
			a.id,
			su.name create_by_id,
			a.create_time,
			a.update_by_id,
			a.update_time,
			a.del_flag,
			a.name,
			a.lawer_presint,
			a.or_unicode,
			a.company_type,
			a.company_industry,
			a.client_type,
			a.credit_rank,
			b.`name` AS area_id,
			b.`name` AS areaName,
			a.zip_code,
			a.fax,
			a.tax_id,
			a.email,
			a.address,
			a.register_address,
			a.company_url,
			a.remarks,
			a.company_id,
			a.office_id,
			a.area_parent_ids,
			a.telephone,
			a.has_uscc,
			a.usc_code,
			a.number,
			(SELECT GROUP_CONCAT(d.label SEPARATOR ',' )
				FROM
					work_client_job_type_info c,
					sys_dict_value d,
					sys_dict_type e
				WHERE
					c.job_type_id = d.VALUE
					AND e.id = d.dict_type_id
					AND e.type = 'representative'
					AND c.work_client_id = a.id) AS deputy
		FROM
			work_client_info a
		LEFT JOIN sys_area b ON a.area_id = b.`code`
		LEFT JOIN sys_user su ON su.id = a.create_by_id
		LEFT JOIN work_client_job_type_info f ON a.id = f.work_client_id
		${ew.customSqlSegment}
		ORDER BY
			a.update_time DESC
	</select>

	<select id="isExist" resultType="java.lang.Integer">
		SELECT
			COUNT( 0 )
		FROM
			work_client_info
		WHERE
			del_flag = 0
			AND (
			`name` = #{value}
			OR usc_code = #{value})
	</select>
</mapper>
