<?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.qmth.sop.business.mapper.DeviceMonitorMapper">
    <select id="count" resultType="java.util.Map">
        SELECT count(d.id) TOTAL,
        sum(case when d.bound='OUT' then 1 else 0 end) ISOUT,
        sum(case when d.bound='IN' and d.`status`!='BREAK_DOWN' then 1 else 0 end) ISIN,
        sum(case when d.`status`='BREAK_DOWN' then 1 else 0 end) BREAK_DOWN
        from sys_device d
        <where>
            <if test="supplierId != null and supplierId != ''">
                and d.supplier_id = #{supplierId}
            </if>
            <if test="model != null and model != ''">
                and d.model = #{model}
            </if>
        </where>
    </select>


    <select id="countByModel" resultType="java.util.Map">
        SELECT
        d.model MODEL,
        count( d.id ) TOTAL,
        sum( CASE WHEN d.bound = 'OUT' THEN 1 ELSE 0 END ) ISOUT,
        sum( CASE WHEN d.bound='IN' and d.`status`!='BREAK_DOWN' THEN 1 ELSE 0 END ) ISIN,
        sum( CASE WHEN d.`status` = 'BREAK_DOWN' THEN 1 ELSE 0 END ) BREAK_DOWN
        FROM
        sys_device d
        <where>
            <if test="supplierId != null and supplierId != ''">
                and d.supplier_id = #{supplierId}
            </if>
        </where>
        group by d.model
    </select>
    <select id="countByServiceUnit" resultType="java.util.Map">
        SELECT
        sum(  l.devices ) DEVICES
<!--        sum( CASE WHEN d.bound = 'OUT' THEN 1 ELSE 0 END ) OUTS-->
        FROM
        t_b_service s
        LEFT JOIN t_b_crm c ON c.service_id = s.id
        LEFT JOIN sys_custom cu ON cu.id = c.custom_id
        LEFT JOIN sys_level l ON l.id = cu.level_id

<!--        left join t_b_device_in_out io on io.service_id=s.id-->
<!--        left join sys_device d on d.device_code=io.device_no-->
        <where>
            and s.status !='FINISH'
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND s.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>

        </where>
    </select>
    <select id="countByServiceUnitDetail" resultType="java.util.Map">


        SELECT a.*,b.OUTS from (

        SELECT s.id ,
        s.`name` NAME,
        ifNull(sum(l.devices),0) devices

        FROM
        t_b_service s
        LEFT JOIN t_b_crm c ON c.service_id = s.id
        LEFT JOIN sys_custom cu ON cu.id = c.custom_id
        LEFT JOIN sys_level l ON l.id = cu.level_id
        <where>
            and s.status !='FINISH'
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND s.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>

        </where>
        GROUP BY s.id ,s.`name`
        ) a
        left join

        (SELECT s.id ,

        sum(case when io.type='OUT' then 1 else 0 end )-sum(case when io.type='IN' then 1 else 0 end ) OUTS

        FROM
        t_b_service s

        left join t_b_device_in_out io on io.service_id=s.id
        left join sys_device d on d.serial_no=io.device_serial_no
        <where>
            and s.status !='FINISH'
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND s.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>

        </where>
        GROUP BY s.id )
        b on a.id =b.id
    </select>
</mapper>
