<?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">
<!-- namespace必须指向DAO接口 -->
<mapper namespace="com.jpsoft.shinestar.modules.base.dao.PersonPopedomDAO">
    <resultMap id="PersonPopedomMap" type="com.jpsoft.shinestar.modules.base.entity.PersonPopedom">
        <id property="id" column="id_"/>
        <result property="companyId" column="company_id"/>
        <result property="companyStructureId" column="company_structure_id"/>
        <result property="personRoleId" column="person_role_id"/>
        <result property="personId" column="person_id"/>
        <result property="showIconId" column="show_icon_id"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark_"/>
        <result property="companyName" column="company_name"/>
        <result property="companyStructureName" column="company_structure_name"/>
        <result property="roleName" column="role_name"/>
        <result property="address" column="address_"/>
        <result property="relationPopedom" column="relation_popedom"/>
        <result property="relationName" column="relation_name"/>
        <result property="serviceCompanyId" column="service_company_id"/>
        <result property="sceneId" column="scene_id"/>
        <result property="popularizePersonId" column="popularize_person_id"/>
        <result property="enableRegisterExamine" column="enable_register_examine"/>
        <result property="enableAttendance" column="enable_attendance"/>
        <result property="personName" column="person_name"/>
        <result property="faceImageUrl" column="face_image_url"/>
        <result property="workOverTime" column="work_over_time"/>
    </resultMap>
    <insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonPopedom">
        <!--
        <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
            select sys_guid() from dual
        </selectKey>
        -->
        <![CDATA[
		insert into base_person_popedom
	    (id_,company_id,person_role_id,person_id,show_icon_id,del_flag,create_by,create_time,update_by,update_time,remark_,
	    company_structure_id,address_,relation_popedom,relation_name,service_company_id,scene_id,
	    popularize_person_id,enable_register_examine,enable_attendance,work_over_time)
		values
		(
            #{id,jdbcType=VARCHAR}
            ,#{companyId,jdbcType=VARCHAR}
            ,#{personRoleId,jdbcType=VARCHAR}
            ,#{personId,jdbcType= NUMERIC }
            ,#{showIconId,jdbcType=VARCHAR}
            ,#{delFlag,jdbcType= NUMERIC }
            ,#{createBy,jdbcType=VARCHAR}
            ,#{createTime,jdbcType= TIMESTAMP }
            ,#{updateBy,jdbcType=VARCHAR}
            ,#{updateTime,jdbcType= TIMESTAMP }
            ,#{remark,jdbcType=VARCHAR}
            ,#{companyStructureId,jdbcType=VARCHAR}
            ,#{address,jdbcType=VARCHAR}
            ,#{relationPopedom,jdbcType=VARCHAR}
            ,#{relationName,jdbcType=VARCHAR}
            ,#{serviceCompanyId,jdbcType=VARCHAR}
            ,#{sceneId,jdbcType=VARCHAR}
            ,#{popularizePersonId,jdbcType=BIGINT}
            ,#{enableRegisterExamine,jdbcType=BOOLEAN}
            ,#{enableAttendance,jdbcType=BOOLEAN}
            ,#{workOverTime,jdbcType=INTEGER}
		)
	]]>
    </insert>
    <delete id="delete" parameterType="string">
        delete from base_person_popedom where id_=#{id,jdbcType=VARCHAR}
    </delete>
    <update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonPopedom">
        update base_person_popedom
        <set>
            <if test="companyId!=null">
                company_id=#{companyId,jdbcType=VARCHAR},
            </if>
            <if test="personRoleId!=null">
                person_role_id=#{personRoleId,jdbcType=VARCHAR},
            </if>
            <if test="personId!=null">
                person_id=#{personId,jdbcType= NUMERIC },
            </if>
            <if test="showIconId!=null">
                show_icon_id=#{showIconId,jdbcType=VARCHAR},
            </if>
            <if test="delFlag!=null">
                del_flag=#{delFlag,jdbcType= NUMERIC },
            </if>
            <if test="createBy!=null">
                create_by=#{createBy,jdbcType=VARCHAR},
            </if>
            <if test="createTime!=null">
                create_time=#{createTime,jdbcType= TIMESTAMP },
            </if>
            <if test="updateBy!=null">
                update_by=#{updateBy,jdbcType=VARCHAR},
            </if>
            <if test="updateTime!=null">
                update_time=#{updateTime,jdbcType= TIMESTAMP },
            </if>
            <if test="remark!=null">
                remark_=#{remark,jdbcType=VARCHAR},
            </if>
            <if test="companyStructureId!=null">
                company_structure_id=#{companyStructureId,jdbcType=VARCHAR},
            </if>
            <if test="address!=null">
                address_=#{address,jdbcType=VARCHAR},
            </if>
            <if test="relationPopedom!=null">
                relation_popedom=#{relationPopedom,jdbcType=VARCHAR},
            </if>
            <if test="relationName!=null">
                relation_name=#{relationName,jdbcType=VARCHAR},
            </if>
            <if test="serviceCompanyId!=null">
                service_company_id=#{serviceCompanyId,jdbcType=VARCHAR},
            </if>
            <if test="sceneId!=null">
                scene_id=#{sceneId,jdbcType=VARCHAR},
            </if>
            <if test="popularizePersonId!=null">
                popularize_person_id=#{popularizePersonId,jdbcType=BIGINT},
            </if>
            <if test="enableRegisterExamine!=null">
                enable_register_examine=#{enableRegisterExamine,jdbcType=BOOLEAN},
            </if>
            <if test="enableAttendance!=null">
                enable_attendance=#{enableAttendance,jdbcType=BOOLEAN},
            </if>
            <if test="workOverTime!=null">
                work_over_time=#{workOverTime,jdbcType=INTEGER},
            </if>
        </set>
        where id_=#{id}
    </update>
    <select id="get" parameterType="string" resultMap="PersonPopedomMap">
        select * from base_person_popedom where id_=#{0} and del_flag = 0
    </select>
    <select id="exist" parameterType="string" resultType="int">
        select count(*) from base_person_popedom where id_=#{0}
    </select>
    <select id="list" resultMap="PersonPopedomMap">
        select * from base_person_popedom
    </select>
    <select id="search" parameterType="hashmap" resultMap="PersonPopedomMap">
        <![CDATA[
			SELECT
				a.*,
				b.name_ AS company_name,
				c.person_role_name AS role_name,
				d.structure_name AS company_structure_name,
				e.name_ AS person_name,
				e.face_image_url AS face_image_url
			FROM
				base_person_popedom a
				LEFT JOIN base_company_info b ON a.company_id = b.id_
				LEFT JOIN base_person_role_info c ON a.person_role_id = c.id_
				LEFT JOIN base_company_structure_info d ON a.company_structure_id = d.id_ and d.del_flag = 0
				LEFT JOIN base_person_info e on a.person_id = e.id_
		]]>
        <where>
            a.del_flag=0
            and e.del_flag = 0
            <if test="searchParams.id != null">
                and a.ID_ like #{searchParams.id}
            </if>
            <if test="searchParams.personId != null">
                and a.person_id = #{searchParams.personId}
            </if>
            <if test="searchParams.sceneId != null">
                and c.scene_id = #{searchParams.sceneId}
            </if>
            <if test="searchParams.companyId != null">
                and a.company_id = #{searchParams.companyId}
            </if>
            <if test="searchParams.enableAttendance != null">
                and a.enable_attendance = #{searchParams.enableAttendance}
            </if>

            <if test="searchParams.personRoleId != null">
                and a.person_role_id = #{searchParams.personRoleId}
            </if>
            <if test="searchParams.nullOpenId != null">
                and e.open_id is null
            </if>
            <if test="searchParams.notNullOpenId != null">
                and e.open_id is not null
            </if>
            <if test="searchParams.name != null">
                and e.name_ like #{searchParams.name}
            </if>


            <if test="searchParams.inCompanyId != null">
                <foreach collection="searchParams.inCompanyId" item="companyId"  open="and a.company_id in (" separator="," close=")">
                    #{companyId}
                </foreach>
            </if>

            <if test="searchParams.companyList != null">
                <foreach collection="searchParams.companyList" item="companyInfo"  open="and a.company_id in (" separator="," close=")">
                    #{companyInfo.id}
                </foreach>
            </if>
            <if test="searchParams.enableRegisterExamine != null">
                and a.enable_register_examine = #{searchParams.enableRegisterExamine}
            </if>
        </where>
        <foreach item="sort" collection="sortList" open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>

    <select id="search2" parameterType="hashmap" resultMap="PersonPopedomMap">
        <![CDATA[
        select distinct(a.person_id)
        from base_person_popedom a
        left join base_person_info b ON a.person_id = b.id_
        ]]>
        <where>
            a.del_flag=0
            and b.del_flag = 0
            <if test="searchParams.name != null">
                and b.name_ like #{searchParams.name}
            </if>
            <if test="searchParams.healthyCode != null">
                and b.healthy_code = #{searchParams.healthyCode}
            </if>
            <if test="searchParams.nullHealthyCode != null">
                and (b.healthy_code is null or b.healthy_code = '11')
            </if>
            <if test="searchParams.companyList != null">
                <foreach collection="searchParams.companyList" item="companyInfo"  open="and a.company_id in (" separator="," close=")">
                    #{companyInfo.id}
                </foreach>
            </if>
        </where>
        <foreach item="sort" collection="sortList" open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>


    <select id="findByPersonId" resultMap="PersonPopedomMap">
        <![CDATA[
        SELECT
            a.*,
            b.name_ AS company_name,
            c.person_role_name AS role_name
        FROM
            base_person_popedom a
            LEFT JOIN base_company_info b ON a.company_id = b.id_
            LEFT JOIN base_person_role_info c ON a.person_role_id = c.id_
        WHERE a.person_id = #{id}
		and a.del_flag = 0
		]]>

    </select>
    <select id="findByCompanyIdAndRoleIds" resultMap="PersonPopedomMap">
        SELECT a.* FROM base_person_popedom a
        inner join base_person_info b on a.person_id = b.id_ and b.del_flag=0
        <where>
            a.del_flag = 0
            and a.company_id = #{companyId}
            and a.person_role_id = #{personRoleId}
            order by a.create_time asc
        </where>
    </select>
    <select id="countByPerson" resultType="int">
        select count(*) from base_person_popedom where del_flag=0 and person_id=#{personId}
    </select>
    <select id="findOneByPersonId" resultMap="PersonPopedomMap">
        select * from base_person_popedom where del_flag=0 and person_id=#{personId} limit 1
    </select>
    <select id="findByVerify" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonPopedom"
            resultMap="PersonPopedomMap">
        select
        *
        from base_person_popedom
        where del_flag=0
        and company_id=#{companyId}
        <if test="companyStructureId != null">
        and company_structure_id=#{companyStructureId}
        </if>
        and scene_id=#{sceneId}
        and person_role_id=#{personRoleId}
        and person_id=#{personId}
        and enable_register_examine=#{enableRegisterExamine}
        limit 1
    </select>

    <select id="findByCompanyIdAndRoleIdList" resultMap="PersonPopedomMap">
        <![CDATA[

        select * from base_person_popedom
        where del_flag = 0
        and company_id = #{serviceCompanyId}
        ]]>
        <foreach collection="personRoloIds" index="index" item="item" open="and person_role_id in(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="findByRelationPopedomId" resultMap="PersonPopedomMap">
        <![CDATA[
            select * from base_person_popedom
            where del_flag = 0
            and relation_popedom = #{relationPopedomId}
        ]]>
    </select>
    <select id="findNumByCompanyId" resultType="Integer">
        <![CDATA[
        select count(DISTINCT person_id) from base_person_popedom a left join base_person_info b
        on a.person_id = b.id_
        where a.del_flag = 0
        and b.del_flag =0
        and a.company_id = #{companyId}
        ]]>
    </select>

    <select id="findByCompanyId" resultMap="PersonPopedomMap">
        <![CDATA[
        select a.* from base_person_popedom a left join base_person_info b
        on a.person_id = b.id_
        where a.del_flag = 0
        and b.del_flag =0
        and a.company_id = #{companyId}
        ]]>
    </select>

    <select id="findNumByCompanyList" resultType="Integer">

        <![CDATA[
        select count(DISTINCT person_id) from base_person_popedom a left join base_person_info b
        on a.person_id = b.id_
        where a.del_flag = 0
        and b.del_flag =0
        ]]>
        <foreach collection="list" item="companyInfo" open="and a.company_id in(" separator="," close=")">
            #{companyInfo.id}
        </foreach>
    </select>

    <select id="findByCompanyList" resultMap="PersonPopedomMap">
        <![CDATA[
        select a.* from base_person_popedom a left join base_person_info b
        on a.person_id = b.id_
        where a.del_flag = 0
        and b.del_flag =0
        ]]>
        <foreach collection="list" item="companyInfo" open="and a.company_id in(" separator="," close=")">
            #{companyInfo.id}
        </foreach>
    </select>

    <select id="findByCompanyListAndName" resultMap="PersonPopedomMap">
        <![CDATA[
        select a.* from base_person_popedom a left join base_person_info b
        on a.person_id = b.id_
        where a.del_flag = 0
        and b.del_flag =0
        and b.name_ like #{personName}
        ]]>
        <foreach collection="companyInfoList" item="companyInfo" open="and a.company_id in(" separator="," close=")">
            #{companyInfo.id}
        </foreach>
    </select>
    <select id="findByPersonIdAndCompanyCode" resultMap="PersonPopedomMap">
        SELECT
        a.*
        FROM
        base_person_popedom a
        INNER JOIN base_company_info b ON a.company_id = b.id_
        WHERE a.person_id = #{personId}
        and b.code_ like #{companyCode}
		and a.del_flag = 0
    </select>
    <select id="hasEnableAttendance" resultMap="PersonPopedomMap">
        SELECT
        a.*
        FROM
        base_person_popedom a
        INNER JOIN base_company_info b ON a.company_id = b.id_
        WHERE a.person_id = #{personId}
        and b.code_ like #{companyCode}
        and a.del_flag = 0
        and a.enable_attendance = 1
        limit 1
    </select>

    <select id="findByCompanyIdAndEnableAttendance" resultMap="PersonPopedomMap">
        <![CDATA[
        select a.* from base_person_popedom a left join base_person_info b
        on a.person_id = b.id_
        where a.del_flag = 0
        and b.del_flag =0
        and a.enable_attendance = #{enableAttendance}
        ]]>
        <foreach collection="companyInfoList" item="companyInfo" open="and a.company_id in(" separator="," close=")">
            #{companyInfo.id}
        </foreach>
    </select>

    <select id="findByPersonIdAndRoleIdList" resultMap="PersonPopedomMap">
        <![CDATA[
        select * from base_person_popedom
        where del_flag = 0
        and person_id = #{personId}
        ]]>
        <foreach collection="personRoleIconList" item="item" open="and person_role_id in(" separator="," close=")">
            #{item.personRoleId}
        </foreach>
    </select>

    <select id="hasEnableAttendanceByCompanyId" resultMap="PersonPopedomMap">
        SELECT a.* FROM base_person_popedom a
        WHERE a.person_id = #{personId}
        and a.del_flag = 0
        and a.company_id = #{companyId}
        and a.enable_attendance = 1
        limit 1
    </select>

    <select id="findByPersonIdCompanyId" resultMap="PersonPopedomMap">
        <![CDATA[

        select * from base_person_popedom
        where del_flag = 0
        and person_id = #{personId}
        and company_id = #{companyId}
        ]]>
    </select>

    <select id="findByPersonIdAndRoleId" resultMap="PersonPopedomMap">
        <![CDATA[
        select  * from base_person_popedom
        where del_flag = 0
        and person_id = #{personId}
        and person_role_id = #{personRoleId}
        ]]>
    </select>
    <select id="findByCompanyIdAndEnableRegisterExamine" resultMap="PersonPopedomMap">
        <![CDATA[
         select  * from base_person_popedom
        where del_flag = 0
        and company_id = #{companyId}
        and enable_register_examine = #{enableRegisterExamine}
        ]]>
    </select>
    <select id="findByIdIgnoreDel" resultMap="PersonPopedomMap">
        <![CDATA[
        select * from base_person_popedom
        where id_ = #{personPopedomId}
        ]]>
    </select>
</mapper>