<?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.PersonDeviceRelationDAO">
    <sql id="Base_Column_List">
	id_,
	device_id,
	person_id,
	del_flag,
	create_by,
	create_time,
	update_by,
	update_time,
	device_id,
	person_id
</sql>
    <resultMap id="PersonDeviceRelationMap" type="com.jpsoft.shinestar.modules.base.entity.PersonDeviceRelation">
		<id property="id" column="id_" />
			<result property="deviceId" column="device_id" />
			<result property="personId" column="person_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="isBound" column="is_bound" />
			<result property="remoteOpen" column="remote_open" />
			<association property="deviceInfo" column="device_id"
					 select="com.jpsoft.shinestar.modules.base.dao.DeviceInfoDAO.get"></association>
			<association property="personInfo" column="person_id"
					 select="com.jpsoft.shinestar.modules.base.dao.PersonInfoDAO.get"></association>
			</resultMap>
	<insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonDeviceRelation">
	<!--
	<selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
		select sys_guid() from dual
	</selectKey>
	-->
	<![CDATA[
		insert into base_person_device_relation
	    (id_,device_id,person_id,del_flag,create_by,create_time,update_by,update_time,is_bound,
	    remote_open)
		values
		(
#{id,jdbcType=VARCHAR}
,#{deviceId,jdbcType=VARCHAR}
,#{personId,jdbcType=BIGINT}
,#{delFlag,jdbcType= NUMERIC }
,#{createBy,jdbcType=VARCHAR}
,#{createTime,jdbcType= TIMESTAMP }
,#{updateBy,jdbcType=VARCHAR}
,#{updateTime,jdbcType= TIMESTAMP }
,#{isBound,jdbcType=NUMERIC}
,#{remoteOpen,jdbcType=NUMERIC}
		)
	]]>
	</insert>
	<delete id="delete" parameterType="string">
		delete from base_person_device_relation where id_=#{id,jdbcType=VARCHAR}
	</delete>
	<update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonDeviceRelation">
		update base_person_device_relation
		<set>
				<if test="deviceId!=null">
		device_id=#{deviceId,jdbcType=VARCHAR},
		</if>
				<if test="personId!=null">
		person_id=#{personId,jdbcType=BIGINT},
		</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="isBound!=null">
				is_bound=#{isBound,jdbcType= NUMERIC },
			</if>
			<if test="remoteOpen!=null">
				remote_open=#{remoteOpen,jdbcType= NUMERIC },
			</if>
		</set>
	where id_=#{id}
	</update>
	<select id="get" parameterType="string" resultMap="PersonDeviceRelationMap">
		select * from base_person_device_relation where id_=#{0}
	</select>
	<select id="exist" parameterType="string" resultType="int">
		select count(*) from base_person_device_relation where id_=#{0} and del_flag=false
	</select>
	<select id="list" resultMap="PersonDeviceRelationMap">
		select * from base_person_device_relation where del_flag=false
	</select>
	<select id="findByPersonId" parameterType="long" resultMap="PersonDeviceRelationMap">
		select * from base_person_device_relation where person_id=#{personId} and del_flag=false
	</select>
	<select id="findByDeviceId" parameterType="string" resultMap="PersonDeviceRelationMap">
		select * from base_person_device_relation where device_id=#{deviceId} and del_flag=false
	</select>
	<select id="findByDeviceIdAndPersonId"  resultMap="PersonDeviceRelationMap">
		select * from base_person_device_relation where device_id=#{deviceId} and person_id=#{personId} and del_flag=false
	</select>
	<select id="search" parameterType="hashmap" resultMap="PersonDeviceRelationMap">
		<![CDATA[
			select a.* from base_person_device_relation a
			LEFT JOIN base_device_info b ON a.device_id = b.id_
			LEFT JOIN base_person_info c ON a.person_id = c.id_
			LEFT JOIN base_company_info d ON c.company_id = d.id_
		]]>
		<where>
			and a.del_flag = false
			and b.del_flag = false
			and c.del_flag = false
			and d.del_flag = false
			<if test="searchParams.deviceId != null">
				and a.device_id = #{searchParams.deviceId}
			</if>
			<if test="searchParams.personId != null">
				and a.person_id = #{searchParams.personId}
			</if>
			<if test="searchParams.companyId != null">
				and c.company_id = #{searchParams.companyId}
			</if>
			<if test="searchParams.companyCode != null">
				and d.code_ like #{searchParams.companyCode}
			</if>
			<if test="searchParams.name != null">
				and c.name_ like #{searchParams.name}
			</if>
			<if test="searchParams.nameList != null">
				<foreach item="name" collection="searchParams.nameList" open="and (" separator="or" close=")">
					c.name_ like #{name}
				</foreach>
			</if>
			<if test="searchParams.idCard != null">
				and c.id_card like #{searchParams.idCard}
			</if>
			<if test="searchParams.phone != null">
				and c.phone_ like #{searchParams.phone}
			</if>
			<if test="searchParams.position1 != null">
				and c.position1_ like #{searchParams.position1}
			</if>
			<if test="searchParams.position2 != null">
				and c.position2_ like #{searchParams.position2}
			</if>
			<if test="searchParams.position3 != null">
				and c.position3_ like #{searchParams.position3}
			</if>
			<if test="searchParams.position4 != null">
				and c.position4_ like #{searchParams.position4}
			</if>
			<if test="searchParams.position5 != null">
				and c.position5_ like #{searchParams.position5}
			</if>
			<if test="searchParams.faceBound != null">
				and c.face_bound = #{searchParams.faceBound}
			</if>
			<if test="searchParams.isBound != null">
				and a.is_bound = #{searchParams.isBound}
			</if>
			<if test="'1'.toString() == searchParams.isUploadPhoto and null != searchParams.isUploadPhoto">
				and c.face_image_url is not null
			</if>
			<if test="'0'.toString() == searchParams.isUploadPhoto and null != searchParams.isUploadPhoto">
				and c.face_image_url is null
			</if>
			<if test="searchParams.deviceName != null">
				and b.alias_name like #{searchParams.deviceName}
			</if>
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
	        a.${sort.name} ${sort.order}
	 	</foreach>
	</select>
    <select id="findDeviceByPersonId" resultMap="com.jpsoft.shinestar.modules.base.dao.DeviceInfoDAO.DeviceInfoMap">
		select b.* from base_person_device_relation a,base_device_info b
		where a.person_id=#{personId}
		and a.device_id = b.id_
		and a.del_flag=false
		and b.del_flag=false
	</select>

    <select id="findByIsBoundAndUpdateTime" resultMap="PersonDeviceRelationMap">
        <![CDATA[
        select * from base_person_device_relation
                where del_flag = false
                and is_bound is null
                and update_time <= #{updateTime}
                or update_time is null
                and del_flag = false
                and is_bound is null
                limit #{limit}

        ]]>


</select>

	<update id="updateIsBound" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonDeviceRelation">
		update base_person_device_relation
		<set>
			<if test="deviceId!=null">
				device_id=#{deviceId,jdbcType=VARCHAR},
			</if>
			<if test="personId!=null">
				person_id=#{personId,jdbcType=BIGINT},
			</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>
				is_bound=#{isBound,jdbcType= VARCHAR},
		</set>
		where id_=#{id}
	</update>
	<select id="findByDerviceName" resultType="Long">
		select a.person_id from base_person_device_relation a,base_device_info b
		where a.device_id = b.id_
		and a.del_flag=false
		and b.alias_name like #{derviceName}
	</select>

</mapper>