<?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.DeviceInfoDAO">
	<resultMap id="DeviceInfoMap" type="com.jpsoft.shinestar.modules.base.entity.DeviceInfo">
		<id property="id" column="id_"/>
		<result property="header" column="header_"/>
		<result property="deviceNo" column="device_no"/>
		<result property="aliasName" column="alias_name"/>
		<result property="address" column="address_"/>
		<result property="isOnline" column="is_online"/>
		<result property="companyId" column="company_id"/>
		<result property="companyName" column="company_name"/>
		<result property="unlockPassword" column="unlock_password"/>
		<result property="codeAddress" column="code_address"/>
		<result property="ipAddress" column="ip_address"/>
		<result property="port" column="port_"/>
		<result property="type" column="type_"/>
		<result property="isAutoUpdate" column="is_auto_update"/>
		<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="defaultFaceLibraryId" column="default_face_library_id"/>
		<result property="heartbeatUpdateTime" column="heartbeat_update_time"/>
		<result property="abnormalReason" column="abnormal_reason"/>
		<result property="isBind" column="is_bind"/>
		<result property="longtitude" column="longtitude_"/>
		<result property="latitude" column="latitude_"/>
		<result property="code" column="code_"/>
		<result property="columnColor" column="column_color"/>
		<result property="columnHeight" column="column_height"/>
		<result property="deviceType" column="device_type"/>
		<result property="deviceAddress" column="device_address"/>
		<result property="manufacturer" column="manufacturer_"/>
		<result property="remoteOpenStartTime" column="remote_open_start_time"/>
		<result property="remoteOpenEndTime" column="remote_open_end_time"/>
		<result property="scene" column="scene_"/>
	</resultMap>
	<insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.DeviceInfo">
		<!--
        <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
            select sys_guid() from dual
        </selectKey>
        -->
		<![CDATA[
		insert into base_device_info
	    (id_,header_,device_no,alias_name,address_,is_online,company_id,
	    unlock_password,code_address,ip_address,port_,type_,is_auto_update,
	    del_flag,create_by,create_time,update_by,update_time,
	    default_face_library_id,heartbeat_update_time,abnormal_reason,
	    longtitude_,latitude_,device_address,
	    code_,column_color,column_height,device_type,manufacturer_,remote_open_start_time,remote_open_end_time,
	    scene_)
		values
		(
			#{id,jdbcType=VARCHAR}
			,#{header,jdbcType=VARCHAR}
			,#{deviceNo,jdbcType=VARCHAR}
			,#{aliasName,jdbcType=VARCHAR}
			,#{address,jdbcType=VARCHAR}
			,#{isOnline,jdbcType= NUMERIC }
			,#{companyId,jdbcType=VARCHAR}
			,#{unlockPassword,jdbcType=VARCHAR}
			,#{codeAddress,jdbcType=VARCHAR}
			,#{ipAddress,jdbcType=VARCHAR}
			,#{port,jdbcType=VARCHAR}
			,#{type,jdbcType=VARCHAR}
			,#{isAutoUpdate,jdbcType=NUMERIC}
			,#{delFlag,jdbcType= NUMERIC }
			,#{createBy,jdbcType=VARCHAR}
			,#{createTime,jdbcType= TIMESTAMP }
			,#{updateBy,jdbcType=VARCHAR}
			,#{updateTime,jdbcType= TIMESTAMP }
			,#{defaultFaceLibraryId,jdbcType=VARCHAR}
            ,#{heartbeatUpdateTime,jdbcType=TIMESTAMP}
            ,#{abnormalReason,jdbcType=VARCHAR}
            ,#{longtitude,jdbcType=VARCHAR}
            ,#{latitude,jdbcType=VARCHAR}
            ,#{deviceAddress,jdbcType=VARCHAR}
            ,#{code,jdbcType=VARCHAR}
            ,#{columnColor,jdbcType=VARCHAR}
            ,#{columnHeight,jdbcType=VARCHAR}
            ,#{deviceType,jdbcType=VARCHAR}
            ,#{manufacturer,jdbcType=VARCHAR}
            ,#{remoteOpenStartTime,jdbcType=VARCHAR}
            ,#{remoteOpenEndTime,jdbcType=VARCHAR}
            ,#{scene,jdbcType=VARCHAR}
		)
	]]>
	</insert>
	<delete id="delete" parameterType="string">
		delete from base_device_info where id_=#{id,jdbcType=VARCHAR}
	</delete>
	<update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.DeviceInfo">
		update base_device_info
		<set>
			<if test="header!=null">
				header_=#{header,jdbcType=VARCHAR},
			</if>
			<if test="deviceNo!=null">
				device_no=#{deviceNo,jdbcType=VARCHAR},
			</if>
			<if test="aliasName!=null">
				alias_name=#{aliasName,jdbcType=VARCHAR},
			</if>
			<if test="address!=null">
				address_=#{address,jdbcType=VARCHAR},
			</if>
			<if test="isOnline!=null">
				is_online=#{isOnline,jdbcType= NUMERIC },
			</if>
			<if test="companyId!=null">
				company_id=#{companyId,jdbcType=VARCHAR},
			</if>
			<if test="unlockPassword!=null">
				unlock_password=#{unlockPassword,jdbcType=VARCHAR},
			</if>
			<if test="codeAddress!=null">
				code_address=#{codeAddress,jdbcType=VARCHAR},
			</if>
			<if test="ipAddress!=null">
				ip_address=#{ipAddress,jdbcType=VARCHAR},
			</if>
			<if test="port!=null">
				port_=#{port,jdbcType=VARCHAR},
			</if>
			<if test="type!=null">
				type_=#{type,jdbcType=VARCHAR},
			</if>
			<if test="isAutoUpdate!=null">
				is_auto_update=#{isAutoUpdate,jdbcType= NUMERIC },
			</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="defaultFaceLibraryId!=null">
				default_face_library_id=#{defaultFaceLibraryId,jdbcType= VARCHAR },
			</if>
			<if test="heartbeatUpdateTime!=null">
				heartbeat_update_time=#{heartbeatUpdateTime,jdbcType= TIMESTAMP },
			</if>
			<if test="abnormalReason!=null">
				abnormal_reason=#{abnormalReason,jdbcType= VARCHAR },
			</if>
			<if test="longtitude!=null">
				longtitude_=#{longtitude,jdbcType= VARCHAR },
			</if>
			<if test="latitude!=null">
				latitude_=#{latitude,jdbcType= VARCHAR },
			</if>
			<if test="deviceAddress!=null">
				device_address=#{deviceAddress,jdbcType= VARCHAR },
			</if>
			<if test="code!=null">
				code_=#{code,jdbcType= VARCHAR },
			</if>
			<if test="columnColor!=null">
				column_color=#{columnColor,jdbcType= VARCHAR },
			</if>
			<if test="columnHeight!=null">
				column_height=#{columnHeight,jdbcType= VARCHAR },
			</if>
			<if test="deviceType!=null">
				device_type=#{deviceType,jdbcType= VARCHAR },
			</if>
			<if test="manufacturer!=null">
				manufacturer_=#{manufacturer,jdbcType= VARCHAR },
			</if>
			<if test="remoteOpenStartTime!=null">
				remote_open_start_time=#{remoteOpenStartTime,jdbcType= VARCHAR },
			</if>
			<if test="remoteOpenEndTime!=null">
				remote_open_end_time=#{remoteOpenEndTime,jdbcType= VARCHAR },
			</if>
			<if test="scene!=null">
				scene_=#{scene,jdbcType= VARCHAR },
			</if>
		</set>
		where id_=#{id}
	</update>
	<select id="get" parameterType="string" resultMap="DeviceInfoMap">
		select * from base_device_info where id_=#{0}
	</select>
	<select id="getByDeviceNo" parameterType="string" resultMap="DeviceInfoMap">
		select a.*,b.name_ AS company_name from base_device_info a
		left join base_company_info b on a.company_id = b.id_
		where a.device_no=#{0} and a.del_flag=0
	</select>
	<select id="exist" parameterType="string" resultType="int">
		select count(*) from base_device_info where id_=#{0} and del_flag=false
	</select>
	<select id="list" resultMap="DeviceInfoMap">
		select * from base_device_info where del_flag=false
	</select>
	<select id="listOnline" resultMap="DeviceInfoMap">
		select * from base_device_info where is_online=true and del_flag=false
	</select>
	<select id="findByDeviceIdList" resultMap="DeviceInfoMap">
		select * from base_device_info where del_flag=false and id_ in
		<foreach item="item" collection="deviceNoList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
	<select id="findByScene" resultMap="DeviceInfoMap">
		SELECT * FROM base_device_info WHERE scene_ like #{0}
	</select>


	<select id="search" parameterType="hashmap" resultMap="DeviceInfoMap">
		select
		a.*,
		b.name_ AS company_name
		<if test="searchParams.isBoundCompanyId != null">
			,( SELECT count(c.id_) FROM base_company_device_relation c WHERE c.device_id = a.id_ AND c.company_id =
			#{searchParams.isBoundCompanyId}) AS is_bind
		</if>
		<if test="searchParams.isBoundPersonId != null">
			,( SELECT count(c.id_) FROM base_person_device_relation c WHERE c.device_id = a.id_ AND c.person_id =
			#{searchParams.isBoundPersonId} and c.del_flag = 0) AS is_bind
		</if>
		<if test="searchParams.isBoundCanteenId != null" >
			,( SELECT count(c.id_) FROM base_canteen_device c WHERE c.device_id = a.id_ AND c.canteen_id = #{searchParams.isBoundCanteenId} and c.del_flag = 0) AS is_bind
		</if>
		<if test="searchParams.isBoundAddressId != null" >
			,( SELECT count(c.id_) FROM base_address_device c WHERE c.device_id = a.id_ AND c.address_id = #{searchParams.isBoundAddressId} and c.del_flag = 0) AS is_bind
		</if>
		from base_device_info a
		LEFT JOIN base_company_info b ON a.company_id = b.id_
		<where>
			and a.del_flag=false
			<if test="searchParams.deviceIdList != null">
				and a.id_ in
				<foreach item="item" collection="searchParams.deviceIdList" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="searchParams.companyCode != null">
				and b.code_ like #{searchParams.companyCode}
			</if>
			<if test="searchParams.aliasName != null">
				and a.alias_name like #{searchParams.aliasName}
			</if>
			<if test="searchParams.deviceNo != null">
				and a.device_no like #{searchParams.deviceNo}
			</if>
			<if test="searchParams.address != null">
				and a.address_ like #{searchParams.address}
			</if>
			<if test="searchParams.type != null">
				and a.type_ = #{searchParams.type}
			</if>
			<if test="searchParams.companyId != null">
				and a.company_id = #{searchParams.companyId}
			</if>
			<if test="searchParams.companyName != null">
				and b.name_ like #{searchParams.companyName}
			</if>
			<if test="searchParams.bindCompanyId != null">
				and (
				a.company_id = #{searchParams.bindCompanyId}
				or a.id_ in(
				select c.device_id from base_company_device_relation c
				where c.device_id=a.id_ and c.company_id=#{searchParams.bindCompanyId}
				)
				)
			</if>
			<if test="searchParams.isOnline != null">
				and a.is_online = #{searchParams.isOnline}
			</if>
			<if test="searchParams.code != null">
				and a.code_ like #{searchParams.code}
			</if>
			<if test="searchParams.columnColor != null">
				and a.column_color = #{searchParams.columnColor}
			</if>
			<if test="searchParams.columnHeight != null">
				and a.column_height = #{searchParams.columnHeight}
			</if>
			<if test="searchParams.deviceType != null">
				and a.device_type = #{searchParams.deviceType}
			</if>
			<if test="searchParams.manufacturer != null">
				and a.manufacturer_ = #{searchParams.manufacturer}
			</if>
			<if test="searchParams.scene != null">
				and a.scene_ like #{searchParams.scene}
			</if>

		</where>
		<foreach item="sort" collection="sortList" open="order by" separator=",">
			${sort.name} ${sort.order}
		</foreach>
	</select>
	<select id="findByDeviceNo" parameterType="string" resultMap="DeviceInfoMap">
		select * from base_device_info where
		device_no = #{0} and del_flag=false
		order by create_time asc
		limit 1
	</select>
	<select id="findByCompanyId" parameterType="string" resultMap="DeviceInfoMap">
		<![CDATA[
			select * from base_device_info a
			where
			(
				a.company_id = #{0}
				or
				a.id_ in(
					select b.device_id from base_company_device_relation b
					where b.device_id=a.id_ and b.company_id=#{0}
				)
			)
			and a.del_flag=0
			order by is_online desc,create_time asc
		]]>
	</select>
	<select id="findByCompanyIdRelation" parameterType="string" resultMap="DeviceInfoMap">
		<![CDATA[
			select * from base_device_info a , base_company_info info
			where (info.id_ = #{0} or info.relation_company_id =#{0})
			and a.del_flag=0 	and info.del_flag=0  and info.id_=a.company_id
			order by is_online desc,a.create_time asc
		]]>
	</select>
	<select id="findByDeviceAddressAndSchoolId" resultMap="DeviceInfoMap">
		select * from base_device_info where del_flag=0
		and
		<foreach item="item" collection="deviceAddressList" open="(" separator="or" close=")">
			device_address = #{item}
		</foreach>
		and company_id=#{schoolId}
	</select>
	<select id="countOnlineByDeviceNoList" resultType="int">
		select count(*) from base_device_info
		where del_flag=0 and is_online=1
		<if test="deviceNoList != null">
			and device_no in
			<foreach item="item" collection="deviceNoList" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
	</select>
</mapper>