<?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.api.butlerApp.dao.jurisdiction.ButlerBacklogDao">
    <select id="backlogDispatchList" parameterType="com.api.model.butlerApp.ButlerUserIdAndStatus" resultType="com.api.vo.butlerApp.ButlerRepairVo">
        select * from (
        select sdl.id as dispatch_id,srr.id,srr.report_detail,srr.repair_date,sdl.status,(case sdld.create_id is null when true then -1 else sdld.create_id end) as create_id,srr.create_date
        from sys_report_repair as srr,sys_dispatch_list as sdl LEFT JOIN sys_dispatch_list_detail as sdld
        on sdld.dispatch_list_id = sdl.id
        <where>
            srr.dispatch_list_id = sdl.id
            and sdl.sys_delete = 1
            <if test="status == 1">
                and sdl.status = 1
            </if>
            <if test="status == 2">
                and sdl.status in (2,3)
            </if>
            <if test="status == 3">
                and sdl.status = 4
            </if>
        </where>
        ) as a where a.create_id = -1 or a.create_id = #{userId}
        ORDER BY a.create_date DESC
    </select>

    <select id="backlogReceivingList" parameterType="com.api.model.butlerApp.ButlerUserIdAndStatus" resultType="com.api.vo.butlerApp.ButlerRepairVo">
        select sdl.id as dispatch_id,srr.id,srr.report_detail,srr.repair_date,sdl.status
        from sys_report_repair as srr,sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld
        <where>
            srr.dispatch_list_id = sdl.id
            and sdld.dispatch_list_id = sdl.id
            and sdld.operator = #{userId}
            and sdl.sys_delete = 1
            <if test="status == 1">
                and sdl.status = 2
            </if>
            <if test="status == 2">
                and sdl.status = 3
            </if>
            <if test="status == 3">
                and sdl.status = 4
            </if>
        </where>
        ORDER BY sdl.begin_date DESC
    </select>

    <select id="backlogReleasedList" parameterType="com.api.model.butlerApp.ButlerUserIdAndStatus" resultType="com.api.vo.butlerApp.ButlerArticleOutVo">
        select uao.id,uao.status,concat(cb.no,"幢-",cbu.no,"单元",cbue.room_number) as room_name,ur.name as applicant_name,ur.type as identity,uao.name as article_out_name,uao.expected_time,uao.applicant_date
        from user_article_out as uao,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,user_resident as ur
        <where>
            uao.building_unit_estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and ur.id = uao.applicant_id
            <if test="status == 1">
                and uao.status = 1
            </if>
            <if test="status == 2">
                and uao.status = 0
            </if>
            <if test="status == 3">
                and uao.status in (2,3)
            </if>
        </where>
        order by uao.applicant_date desc
    </select>

    <select id="findUnProcessedNum1" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list where status = 1 and sys_delete = 1
    </select>

    <select id="findProcessingNum1" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list where status in (2,3) and sys_delete = 1
    </select>

    <select id="findProcessedNum1" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list where status >= 4 and sys_delete = 1
    </select>

    <select id="findUnProcessedNum2" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld
        where sdl.id = sdld.dispatch_list_id and sdl.status = 2 and sdl.sys_delete = 1 and sdld.operator = #{id}
    </select>

    <select id="findProcessingNum2" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld
        where sdl.id = sdld.dispatch_list_id and sdl.status = 3 and sdl.sys_delete = 1 and sdld.operator = #{id}
    </select>

    <select id="findProcessedNum2" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld
        where sdl.id = sdld.dispatch_list_id and sdl.status >= 4 and sdl.sys_delete = 1 and sdld.operator = #{id}
    </select>

    <select id="findUnProcessedNum3" resultType="java.lang.Integer">
        select count(*) from user_article_out where status = 1
    </select>

    <select id="findProcessedNum3" resultType="java.lang.Integer">
        select count(*) from user_article_out where status >= 2
    </select>
</mapper>