<?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.ButlerRepairDao">
    <select id="findJIdsByRoleId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select jurisdiction_id from sys_role_jurisdiction where role_id = #{roleId}
    </select>

    <select id="findJIdsByRoleIds" parameterType="java.util.List" resultType="java.lang.Integer">
        select jurisdiction_id from sys_role_jurisdiction where role_id in
        <foreach collection="split" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </select>


    <select id="list1" parameterType="com.api.model.butlerApp.ButlerRepairSearch" 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,sdl.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="repairStatus == 1">
                    and sdl.status = #{repairStatus}
                </if>
                <if test="repairStatus == 2">
                    and sdl.status = #{repairStatus}
                </if>
                <if test="repairStatus == 3">
                    and sdl.status = #{repairStatus}
                </if>
                <if test="repairStatus == 4">
                    and sdl.status between 4 and 7
                </if>
                <if test="repairStatus == 5">
                    and sdl.status = 7
                </if>
                <if test="repairStatus == 6">
                    and sdl.status = 8
                </if>
            </where>
        ) as a where a.create_id = -1 or a.create_id = #{id} order by a.create_date DESC
    </select>

    <select id="list2" parameterType="com.api.model.butlerApp.ButlerRepairSearch" 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 = #{id}
            and sdl.sys_delete = 1
            <if test="repairStatus == 1">
                and sdl.status = 2
            </if>
            <if test="repairStatus == 2">
                and sdl.status = 3
            </if>
            <if test="repairStatus == 3">
                and sdl.status between 4 and 7
            </if>
            <if test="repairStatus == 4">
                and sdl.status = 7
            </if>
            <if test="repairStatus == 5">
                and sdl.status = 8
            </if>
        </where>
        order by sdl.create_date DESC
    </select>

    <select id="list3" parameterType="com.api.model.butlerApp.ButlerRepairSearch" 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
        <where>
            srr.dispatch_list_id = sdl.id
            and sdl.sys_delete = 1
            <if test="repairStatus == 1">
                and sdl.status in (1,2)
            </if>
            <if test="repairStatus == 2">
                and sdl.status = 3
            </if>
            <if test="repairStatus == 3">
                and sdl.status between 4 and 7
            </if>
            <if test="repairStatus == 4">
                and sdl.status = 7
            </if>
            <if test="repairStatus == 5">
                and sdl.status = 8
            </if>
        </where>
        order by sdl.create_date DESC
    </select>

    <select id="findById" parameterType="com.api.model.butlerApp.ButlerUserIdAndRepairId" resultType="com.api.vo.butlerApp.ButlerRepairFindByIdVo">
        select a.*,ur.name,ur.tel from (
            select sdl.id as dispatch_id,srr.type,srr.repairman,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
            from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,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.building_unit_estate_id = cbue.id
                and cbue.building_unit_id = cbu.id
                and cbu.building_id = cb.id
                and srr.dispatch_list_id = sdl.id
                and sdl.sys_delete = 1
                and srr.id = #{repairId}
            </where>
		) as a
        LEFT JOIN user_resident as ur
        on ur.id = a.repairman
		where a.create_id = -1 or a.create_id = #{id}
    </select>

    <select id="findDispatchTypeById" parameterType="com.api.model.butlerApp.ButlerUserIdAndRepairId" resultType="com.api.vo.butlerApp.ButlerDispatchTypeVo">
        select a.*,swotd.name as work_order_subclass_name
        from (
                select (case sdld.create_id is null when true then -1 else sdld.create_id end) as create_id,sdld.work_order_type_detail,sdld.type as dispatch_type,swotl.name as work_order_limit_name
                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
                LEFT JOIN sys_work_order_time_limit  as swotl
                on swotl.id = sdld.work_order_time_limit
                where
                        srr.dispatch_list_id = sdl.id
                        and sdl.sys_delete = 1 and srr.id = #{repairId}
            ) as a
        LEFT JOIN sys_work_order_type_detail as swotd
        on swotd.id = a.work_order_type_detail
        where a.create_id = -1 or a.create_id = #{id}
    </select>

    <select id="findProcessRecord" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerProcessRecordVo">
        select spr.id,spr.operation_type,spr.operation_date
        from sys_report_repair as srr,sys_dispatch_list as sdl,sys_process_record as spr
        where srr.dispatch_list_id = sdl.id and spr.dispatch_list_id = sdl.id and sdl.sys_delete =1 and srr.id = #{repairId}
    </select>

    <select id="findById2" parameterType="com.api.model.butlerApp.ButlerUserIdAndRepairId" resultType="com.api.vo.butlerApp.ButlerRepairFindByIdVo">
        select srr.id,sdl.id as dispatch_id,ur.name,ur.tel,srr.type,sdl.status,CONCAT(cb.no,'栋',cbu.no,'单元',cbue.room_number) as room_name
        from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld,sys_report_repair as srr
        LEFT JOIN user_resident as ur
        on ur.id = srr.repairman
        where srr.building_unit_estate_id = cbue.id
		and cbue.building_unit_id = cbu.id
		and cbu.building_id = cb.id
        and srr.dispatch_list_id = sdl.id and sdld.dispatch_list_id = sdl.id and srr.id = #{repairId}
    </select>

    <select id="findDispatchTypeById2" parameterType="com.api.model.butlerApp.ButlerUserIdAndRepairId" resultType="com.api.vo.butlerApp.ButlerDispatchTypeVo">
        select sdld.type as dispatch_type,swotl.name as work_orderLimit_name,swotd.name as work_order_subclass_name
        from sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld,sys_report_repair as srr,sys_work_order_time_limit as swotl,sys_work_order_type_detail as swotd
        where srr.dispatch_list_id = sdl.id and sdld.dispatch_list_id = sdl.id and swotl.id = sdld.work_order_time_limit and swotd.id = sdld.work_order_type_detail and sdl.sys_delete =1 and srr.id = #{repairId}
    </select>

    <select id="findById3" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerRepairFindByIdVo">
        select T.*,ur.name,ur.tel from (
            select srr.id,srr.repairman,sdl.id as dispatch_id,srr.type,sdl.status,CONCAT(cb.no,'栋',cbu.no,'单元',cbue.room_number) as room_name
            from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,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.building_unit_estate_id = cbue.id
              and cbue.building_unit_id = cbu.id
              and cbu.building_id = cb.id
              and srr.dispatch_list_id = sdl.id  and srr.id = #{repairId}
        ) as T
        LEFT JOIN user_resident as ur
        on ur.id = T.repairman
    </select>

    <select id="findDispatchTypeById3" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerDispatchTypeVo">
        select sdld.type as dispatch_type,swotl.name as work_orderLimit_name,swotd.name as work_order_subclass_name
        from sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld,sys_report_repair as srr,sys_work_order_time_limit as swotl,sys_work_order_type_detail as swotd
        where srr.dispatch_list_id = sdl.id and sdld.dispatch_list_id = sdl.id and swotl.id = sdld.work_order_time_limit and swotd.id = sdld.work_order_type_detail and sdl.sys_delete = 1 and srr.id = #{repairId}
    </select>

    <select id="findWorkOrderTimeLimit" resultType="com.api.vo.app.IdAndName">
        select id,name from sys_work_order_time_limit
    </select>

    <select id="findWorkOrderTypeDetail" parameterType="java.lang.Integer" resultType="com.api.vo.app.IdAndName">
        select id,name from sys_work_order_type_detail where work_order_type_id =#{workOrderTypeId}
    </select>

    <select id="findRepairOrganization" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerRepairOrganizationVo">
        select id,name from sys_organization where parent_id = #{repairOrganizationId}
    </select>

    <select id="findRepairman" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerRepairmanVo">
        select id,actual_name as name,tel from sys_user where organization_id = #{id}
    </select>

    <insert id="dispatch" parameterType="com.api.model.butlerService.SysDispatchListDetail">
        insert into sys_dispatch_list_detail(
            code,
            dispatch_list_id,
            work_order_type,
            work_order_type_detail,
            work_order_time_limit,
            type,
            dispatch_date,
            operator,
            remake,
            create_id,
            create_date
        ) values (
            #{code},
            #{dispatchListId},
            #{workOrderType},
            #{workOrderTypeDetail},
            #{workOrderTimeLimit},
            #{type},
            #{dispatchDate},
            #{operator},
            #{remake},
            #{createId},
            #{createDate}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <update id="updateStatus" parameterType="com.api.model.butlerService.UpdateDispatchStatus">
        update sys_dispatch_list set status = #{status} where id =#{id}
    </update>

    <select id="findSysUserById" parameterType="java.lang.Integer" resultType="com.api.model.businessManagement.SysUser">
        select * from sys_user
        where id = #{id}
    </select>

    <insert id="insertProcessRecord" parameterType="com.api.model.butlerService.ProcessRecord">
        insert into sys_process_record(
            dispatch_list_id,
            operation_date,
            operation_type,
            operator,
            operator_type,
            operator_content
        ) values (
            #{dispatchListId},
            #{operationDate},
            #{operationType},
            #{operator},
            #{operatorType},
            #{operatorContent}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <select id="findStatusByDispatchId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select status from sys_dispatch_list where id = #{dispatchListId}
    </select>

    <update id="updateDispatchListDetail" parameterType="com.api.model.butlerService.SysDispatchListDetail">
        update sys_dispatch_list_detail
        set operator = #{operator},
        modify_id = #{modifyId},
        modify_date = #{modifyDate}
        where dispatch_list_id = #{dispatchListId}
    </update>

    <select id="findHSByRepairId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerHandlingSituationVo">
        select shcd.* from sys_report_repair as srr,sys_dispatch_list as sdl,sys_handle_complete_detail as shcd
        where srr.dispatch_list_id = sdl.id and shcd.dispatch_list_id = sdl.id and sdl.sys_delete =1 and srr.id = #{repairId}
    </select>

    <select id="findRCDByRepairId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerRepairCostDetailVo">
        select shcd.labor_cost,shcd.material_cost,shcd.total_cost
        from sys_report_repair as srr,sys_dispatch_list as sdl,sys_handle_complete_detail as shcd
        where srr.dispatch_list_id = sdl.id and shcd.dispatch_list_id = sdl.id and sdl.sys_delete =1 and srr.id = #{repairId}
    </select>

    <select id="findEIByRepairId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerEvaluateInfoVo">
        select sdl.evaluation_level,sdl.evaluation_content,sdl.evaluation_date
        from sys_report_repair as srr,sys_dispatch_list as sdl
        where srr.dispatch_list_id = sdl.id and sdl.sys_delete =1 and srr.id = #{repairId}
    </select>

    <update id="receivingOrders" parameterType="com.api.model.butlerApp.ButlerUpdateStatusAndDate">
        update sys_dispatch_list
        set status = #{status},
        begin_date = #{updateDate}
        where id = #{dispatchListId}
    </update>

    <select id="findOperatorByDispatchId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select operator from sys_dispatch_list_detail where dispatch_list_id = #{dispatchId}
    </select>

    <select id="findDelayedByShowValue" parameterType="java.lang.Integer" resultType="java.lang.String">
        select show_name from sys_data_dictionary
        where type_name = 'sys_dispatch_list_delayed_delayed' and show_value = #{delayed}
    </select>

    <insert id="insertDispatchListDelayed" parameterType="com.api.model.butlerApp.ButlerApplyDelayed">
        insert into sys_dispatch_list_delayed(
            dispatch_list_id,
            process_record_id,
            delayed_time,
            remake,
            create_id,
            create_date
        )values (
            #{dispatchListId},
            #{processRecordId},
            #{delayedTime},
            #{remake},
            #{createId},
            #{createDate}
        )
    </insert>

    <insert id="insertHandleCompleteDetail" parameterType="com.api.model.butlerApp.ButlerHandleCompleteDetail">
        insert into sys_handle_complete_detail(
            dispatch_list_id,
            detail,
            material_list,
            <if test="laborCost != null">
                labor_cost,
            </if>
            <if test="materialCost != null">
                material_cost,
            </if>
            <if test="totalCost != null">
                total_cost,
            </if>
            repair_result,
            complete_date,
            create_id,
            create_date
        )values (
            #{dispatchListId},
            #{detail},
            #{materialList},
            <if test="laborCost != null">
                #{laborCost},
            </if>
            <if test="materialCost != null">
                #{materialCost},
            </if>
            <if test="totalCost != null">
                #{totalCost},
            </if>
            #{repairResult},
            #{completeDate},
            #{createId},
            #{createDate}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <select id="findTypeByDispatchListId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select type from sys_dispatch_list_detail where dispatch_list_id = #{dispatchListId}
    </select>

    <update id="updateStatusAndDate" parameterType="com.api.model.butlerApp.ButlerUpdateStatusAndDate">
        update sys_dispatch_list
        set status = #{status},
        end_date = #{updateDate}
        where id =#{dispatchListId}
    </update>

    <select id="findRepairIdByDispatchListId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select id from sys_report_repair where dispatch_list_id = #{dispatchListId}
    </select>
</mapper>