<?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.fdkankan.ucenter.mapper.ISceneMapper">

    <select id="getSceneList" resultType="com.fdkankan.ucenter.vo.response.SceneVo">
        SELECT * FROM (
        SELECT  p.id ,null as name ,0 as isFolder,null as type,null as parentId ,p.create_time,num,scene_name,scene_dec,
        p.status,pay_status,thumb,web_site,0 as is_upgrade,sn_code,view_count,p.build_type,p.scene_type
        FROM t_scene_pro p
        <include refid="sceneJoinCamera"></include>
        WHERE is_upgrade = 0 and  p.rec_status = 'A' and p.status in(1,-2)
        <if test="param.sceneName !=null and param.sceneName != '' ">
            and p.scene_name like CONCAT('%',#{param.sceneName},'%')
        </if>
        <include refid="commonWhere"></include>
        <if test="param.searchKey !=null and param.searchKey !=''">
            and ( p.scene_name like CONCAT('%',#{param.searchKey},'%')
            or c.sn_code like CONCAT('%',#{param.searchKey},'%')
            <if test="param.numList !=null and param.numList.size >0">
                or num in
                <foreach item="num" collection="param.numList" open="(" separator="," close=")">
                    #{num}
                </foreach>
            </if>
            )
        </if>
        UNION ALL
        SELECT  p.id as id ,null as name ,0 as isFolder,null as type,null as parentId,p.create_time ,num,title as scene_name,description as scene_dec,
        scene_status as status,pay_status,thumb,web_site,1 as is_upgrade,sn_code,view_count,e.build_type,p.scene_type
        FROM t_scene_plus p
        LEFT JOIN t_scene_plus_ext e on p.id = e.plus_id
        <include refid="sceneJoinCamera"></include>
        WHERE p.rec_status = 'A' and p.scene_status in(1,-2)
        <if test="param.sceneName !=null and param.sceneName != '' ">
            and p.title like CONCAT('%',#{param.sceneName},'%')
        </if>
        <include refid="commonWhere"></include>

        <if test="param.searchKey !=null and param.searchKey !=''">
            and ( p.title like CONCAT('%',#{param.searchKey},'%')
            or c.sn_code like CONCAT('%',#{param.searchKey},'%')
            <if test="param.numList !=null and param.numList.size >0">
                or num in
                <foreach item="num" collection="param.numList" open="(" separator="," close=")">
                    #{num}
                </foreach>
            </if>
            )
        </if>
        ORDER BY create_time desc
        ) as scene_tb
    </select>


    <sql id="commonWhere">
        and p.user_id =#{param.userId} and pay_status = 1
        <if test="param.snCode !=null and param.snCode != '' ">
            and c.sn_code like CONCAT('%',#{param.snCode},'%')
        </if>
        <if test="param.startTime !=null and param.startTime !=''">
            and p.create_time &gt;= #{param.startTime}
        </if>
        <if test="param.endTime !=null and param.endTime !=''">
            and p.create_time &lt;= #{param.endTime}
        </if>
    </sql>

    <sql id="sceneJoinCamera">
        LEFT JOIN t_camera c on p.camera_id = c.id
    </sql>

    <select id="getDecorateScenes" resultType="com.fdkankan.ucenter.vo.response.SceneVo">
    select a.* from (

        select id,scene_name, scene_dec, web_site, thumb, scene_type,
        create_time, status, user_id, pay_status, camera_id, num, scene_scheme, shoot_count,
        rec_status, space, recommend, view_count, 'V1' AS build_type,1 as  scene_source from `t_scene` s
        WHERE rec_status = 'A'  and user_id = #{param.userId}
        and (status = -2 or status = 1)
         <if test= 'param.searchKey != null'>
          and s.scene_name like CONCAT('%',#{param.searchKey},'%') or s.num = #{param.searchKey}
         </if>
        UNION ALL
        SELECT s.id,s.scene_name, s.scene_dec, s.web_site, s.thumb, s.scene_type,
        s.create_time, s.status, s.user_id, s.pay_status, s.camera_id, s.num, s.scene_scheme, s.shoot_count,
        s.rec_status, s.space, s.recommend, s.view_count, s.build_type,s.scene_source FROM `t_scene_pro` s
        WHERE rec_status = 'A' and scene_type != 99 and scene_source !=4 and user_id = #{param.userId}
        and (status = -2 or status = 1)
        <if test= 'param.searchKey != null'>
            and s.scene_name like CONCAT('%',#{param.searchKey},'%') or s.num = #{param.searchKey}
        </if>
  ) a
     order by create_time desc
    </select>
</mapper>
