<?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="cn.cecep.talroad.mapper.AmVideoDevFiMapper">

    <resultMap type="cn.cecep.talroad.domain.AmVideoDevFi" id="AmVideoDevFiResult">
        <result property="id"    column="id"    />
        <result property="factoryId"    column="factory_id"    />
        <result property="devId"    column="dev_id"    />
        <result property="devName"    column="dev_name"    />
        <result property="devInsideCode"    column="dev_inside_code"    />
        <result property="devCode"    column="dev_code"    />
        <result property="model"    column="model"    />
        <result property="modelRoad"    column="model_road"    />
        <result property="location"    column="location"    />
        <result property="lonMap"    column="lon_map"    />
        <result property="latMap"    column="lat_map"    />
        <result property="descrAny"    column="descr_any"    />
    </resultMap>

    <resultMap type="cn.cecep.talroad.vo.export.AmVideoDevFiExport" id="AmVideoDevFiExportResult">

        <result property="devName"    column="dev_name"    />
        <result property="channelId"    column="channel_id"    />
        <result property="outletName"    column="outlet_name"    />
        <result property="outletCode"    column="outlet_code"    />
    </resultMap>


    <sql id="selectAmVideoDevFiVo">
        select id, factory_id, dev_id, dev_name, dev_inside_code, dev_code, model, model_road, location, lon_map, lat_map, descr_any from m_am_video_dev_fi
    </sql>
    <update id="updateStatusBatch">
        <foreach collection="updateList" separator=";" item="item">
            UPDATE m_am_video_dev_fi SET status = #{item.status} WHERE id = #{item.id}
        </foreach>
    </update>

    <select id="selectAmVideoDevFiList" parameterType="cn.cecep.talroad.domain.AmVideoDevFi" resultMap="AmVideoDevFiResult">
        <include refid="selectAmVideoDevFiVo"/>
        <where>
            <if test="factoryId != null  and factoryId != ''"> and factory_id = #{factoryId}</if>
            <if test="devId != null  and devId != ''"> and dev_id = #{devId}</if>
            <if test="devName != null  and devName != ''"> and dev_name like concat('%', #{devName}, '%')</if>
            <if test="devInsideCode != null  and devInsideCode != ''"> and dev_inside_code = #{devInsideCode}</if>
            <if test="devCode != null  and devCode != ''"> and dev_code = #{devCode}</if>
            <if test="model != null  and model != ''"> and model = #{model}</if>
            <if test="modelRoad != null  and modelRoad != ''"> and model_road = #{modelRoad}</if>
            <if test="location != null  and location != ''"> and location = #{location}</if>
            <if test="lonMap != null  and lonMap != ''"> and lon_map = #{lonMap}</if>
            <if test="latMap != null  and latMap != ''"> and lat_map = #{latMap}</if>
            <if test="descrAny != null  and descrAny != ''"> and descr_any = #{descrAny}</if>
        </where>
    </select>

    <select id="selectAmVideoDevFiExportList" parameterType="cn.cecep.talroad.vo.export.AmVideoDevFiExport" resultMap="AmVideoDevFiExportResult">
        select  dev_name as dev_name,channel_id as channel_id, concat(coalesce(poc.pollutant_name,''),coalesce(poc.pollutant_code,'')) as outlet_name,coalesce(poc.pollutant_code,'') as outlet_code
        from  m_am_video_dev_fi mam
        left join bas_device poc on   poc.device_id = mam.device_id
        <where>
            <if test="factoryId != null  and factoryId != ''"> and mam.factory_id = #{factoryId}</if>
            <if test="devId != null  and devId != ''"> and mam.dev_id = #{devId}</if>
            <if test="devName != null  and devName != ''"> and mam.dev_name like concat('%', #{devName}, '%')</if>
            <if test="devInsideCode != null  and devInsideCode != ''"> and mam.dev_inside_code = #{devInsideCode}</if>
            <if test="devCode != null  and devCode != ''"> and mam.dev_code = #{devCode}</if>
            <if test="model != null  and model != ''"> and mam.model = #{model}</if>
            <if test="modelRoad != null  and modelRoad != ''"> and mam.model_road = #{modelRoad}</if>
            <if test="location != null  and location != ''"> and mam.location = #{location}</if>
            <if test="lonMap != null  and lonMap != ''"> and mam.lon_map = #{lonMap}</if>
            <if test="latMap != null  and latMap != ''"> and mam.lat_map = #{latMap}</if>
            <if test="descrAny != null  and descrAny != ''"> and mam.descr_any = #{descrAny}</if>
            <if test="deviceName != null  and deviceName != ''"> and poc.pollutant_name like concat('%', #{deviceName}, '%')</if>
            <if test="deviceCode != null  and deviceCode != ''"> and poc.pollutant_code like concat('%', #{deviceCode}, '%')</if>
        </where>
    </select>

    <select id="selectAmVideoDevFiSelectList" parameterType="cn.cecep.talroad.vo.export.AmVideoDevFiExport" resultMap="AmVideoDevFiExportResult">
        select   mam.id as id, mam.factory_id as factory_id,mam.device_id as device_id,dev_name as dev_name,channel_id as channel_id, concat(coalesce(poc.pollutant_name,''),coalesce(poc.pollutant_code,'')) as outlet_name,coalesce(poc.pollutant_code,'') as outlet_code
        from  m_am_video_dev_fi mam
                  left join bas_device poc on   poc.device_id = mam.device_id
            ${ew.customSqlSegment}
    </select>
    <select id="selectAmVideoDevFiSelectXkList" parameterType="cn.cecep.talroad.vo.export.AmVideoDevFiExport" resultMap="AmVideoDevFiExportResult">
        select    poc.factory_id as factory_id,poc.device_id as device_id,pollutant_name as outlet_name ,pollutant_code as outlet_code
        from   bas_device poc
            ${ew.customSqlSegment}
    </select>

    <select id="selectDeviceNameById" parameterType="cn.cecep.talroad.vo.export.AmVideoDevFiExport" resultMap="AmVideoDevFiExportResult">
        select   mam.id as id, mam.factory_id as factory_id,mam.device_id as device_id,dev_name as dev_name,channel_id as channel_id, concat(coalesce(poc.pollutant_name,''),coalesce(poc.pollutant_code,'')) as outlet_name,coalesce(poc.pollutant_code,'') as outlet_code
        from  m_am_video_dev_fi mam
        left join bas_device poc on   poc.device_id = mam.device_id
            ${ew.customSqlSegment}
    </select>
    <select id="getByExport" parameterType="cn.cecep.talroad.vo.export.AmVideoDevFiExport" resultMap="AmVideoDevFiExportResult">
        select   mam.id as id, mam.factory_id as factory_id,mam.device_id as device_id,dev_name as dev_name,channel_id as channel_id, concat(coalesce(poc.pollutant_name,''),coalesce(poc.pollutant_code,'')) as outlet_name,coalesce(poc.pollutant_code,'') as outlet_code
        from  m_am_video_dev_fi mam
        left join bas_device poc on   poc.device_id = mam.device_id
            where mam.id = ${id}
    </select>

    <select id="overviewVideoMonitors" resultType="java.lang.String">
        <if test="type==1">
            SELECT COUNT( pf.id )
            FROM
            pc_factory AS pf
            INNER JOIN ( SELECT DISTINCT factory_id FROM "m_am_video_dev_fi" WHERE is_status = '1' ) AS ma ON pf.ID = ma.factory_id
        </if>
        <if test="type==2">
            SELECT COUNT( pf.id )
            FROM
            pc_factory AS pf
            INNER JOIN ( SELECT factory_id FROM "m_am_video_dev_fi" WHERE is_status = '1' ) AS ma ON pf.ID = ma.factory_id
        </if>
        <if test="type==3">
            SELECT COUNT( pf.id )
            FROM
            pc_factory AS pf
            INNER JOIN ( SELECT DISTINCT factory_id FROM "m_am_video_dev_fi" WHERE is_status = '1' and dev_name LIKE '%门%' ) AS ma ON pf.ID = ma.factory_id
        </if>
        <if test="type==4">
            SELECT COUNT( pf.id )
            FROM
            pc_factory AS pf
            INNER JOIN ( SELECT factory_id FROM "m_am_video_dev_fi" WHERE is_status = '1' and dev_name LIKE '%门%' ) AS ma ON pf.ID = ma.factory_id
        </if>
        <if test="regionCodes != null and regionCodes.size() != 0">
            and pf.country_code IN
            <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
    </select>

    <select id="proportionExitTypes" resultType="cn.cecep.talroad.vo.homepage.ProportionVideoMonitorsVo">
        SELECT
            COALESCE( SUM ( CASE WHEN discharge_stage = 'NE' THEN 1 ELSE 0 END ), 0 ) videoEnterprise,
            COALESCE( SUM ( CASE WHEN discharge_stage = 'VI' THEN 1 ELSE 0 END ), 0 ) videoNumber,
            COALESCE( SUM ( CASE WHEN discharge_stage = 'V' THEN 1 ELSE 0 END ), 0 ) accessControlEnterprise,
            COALESCE( SUM ( CASE WHEN discharge_stage in ('I','II','III','IV') THEN 1 ELSE 0 END ), 0 ) accessControlNumber
        FROM
            m_am_door_monitor_account_fi as da
        INNER JOIN pc_factory  AS pf ON pf.ID = da.factory_id
        <where>
            <if test="monitoringStartTime != null and monitoringStartTime!=''">
                and "entry_time"  >= #{monitoringStartTime}
            </if>
            <if test="monitoringEndTime != null and monitoringEndTime!=''">
                and "entry_time"  &lt;= #{monitoringEndTime}
            </if>
            <if test="regionCodes != null and regionCodes.size() != 0">
                and pf.country_code IN
                <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                    #{item,jdbcType=VARCHAR}
                </foreach>
            </if>
        </where>
    </select>

    <select id="trendsVehicleEmissionTypes" resultType="cn.cecep.talroad.vo.homepage.OverviewVideoMonitorsVo">
        SELECT
        COALESCE( SUM ( CASE WHEN discharge_stage = 'NE' THEN 1 ELSE 0 END ), 0 ) videoEnterprise,
        COALESCE( SUM ( CASE WHEN discharge_stage = 'VI' THEN 1 ELSE 0 END ), 0 ) videoNumber,
        COALESCE( SUM ( CASE WHEN discharge_stage = 'V' THEN 1 ELSE 0 END ), 0 ) accessControlEnterprise,
        COALESCE( SUM ( CASE WHEN discharge_stage in ('I','II','III','IV') THEN 1 ELSE 0 END ), 0 ) accessControlNumber,
        TO_DATE(entry_time,'YYYY-MM-DD') as eaTime
        FROM
        m_am_door_monitor_account_fi as da
        INNER JOIN pc_factory  AS pf ON pf.ID = da.factory_id
        <where>
            <if test="monitoringStartTime != null and monitoringStartTime!=''">
                and "entry_time"  >= #{monitoringStartTime}
            </if>
            <if test="monitoringEndTime != null and monitoringEndTime!=''">
                and "entry_time"   &lt;= #{monitoringEndTime}
            </if>
            <if test="regionCodes != null and regionCodes.size() != 0">
                and pf.country_code IN
                <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                    #{item,jdbcType=VARCHAR}
                </foreach>
            </if>
        </where>
        GROUP BY TO_DATE(entry_time,'YYYY-MM-DD')
        ORDER BY eaTime
    </select>

    <select id="videoMonitoringIssues" resultType="cn.cecep.talroad.vo.homepage.VideoMonitoringIssuesVo">
        SELECT
        distinct on(fivideo.problem_id)
        fivideo.problem_id,
            fivideo.video_path,
            fivideo.video_type,
            fivideo.start_time,
            fivideo.end_time,
            pf.factory_name,
            amvideo.dev_name,
            amvideo.dev_id,
            amvideo.channel_id,
            amvideo.status,
            amvideo.gbs_device_id,
            amvideo.dev_source,
            amvideo.is_status
        FROM
            m_am_problem_fi_video AS fivideo
                LEFT JOIN m_am_problem_fi AS fi ON fivideo.problem_id = fi.ID
                LEFT JOIN m_am_video_dev_fi AS amvideo ON amvideo.ID = fivideo.video_dev_fi_id
                LEFT JOIN pc_factory AS pf ON amvideo.factory_id = pf.ID
        WHERE
            fi.feedback_date IS NULL and judge_result != '6'
        <if test="regionCodes != null and regionCodes.size() != 0">
            and pf.country_code IN
            <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
        ORDER BY
        fivideo.problem_id desc
    </select>

    <select id="otherEnterpriseMonitoringVideos" resultType="cn.cecep.talroad.vo.homepage.VideoMonitoringIssuesVo">
        SELECT
            pf.factory_name,
            amvideo.dev_name,
            amvideo.dev_id,
            amvideo.channel_id,
            amvideo.status,
            amvideo.gbs_device_id,
            amvideo.is_status,
            amvideo.dev_source
        FROM
            m_am_video_dev_fi AS amvideo
                inner JOIN pc_factory AS pf ON amvideo.factory_id = pf.ID
        <if test="regionCodes != null and regionCodes.size() != 0">
            and pf.country_code IN
            <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
    </select>
    <select id="selectByOutletAndDeviceIds" resultType="cn.cecep.talroad.vo.AmVideoDevFiVo">
        SELECT
            id,
            factory_id,
            dev_id,
            dev_name,
            dev_type,
            location,
            lon_map,
            lat_map,
            status,
            dev_source
        FROM m_am_video_dev_fi
        <where>
            AND dev_source IN ('1', '2')
            AND (
                <trim prefixOverrides="OR">
                    <if test="outletId != null and outletId != ''">
                        OR outlet_id = #{outletId}
                    </if>
                    <if test="deviceIds != null and deviceIds.size() > 0">
                        OR device_id IN
                        <foreach collection="deviceIds" open="(" separator="," close=")" item="item">
                            #{item}
                        </foreach>
                    </if>
                </trim>
            )
        </where>
        ORDER BY status, id
    </select>
</mapper>
