<?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.message.ButlerAppMessageDao">
    <select id="findSysNoReadNumById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from butler_sys_message where receiver_account = #{id} and send_status = 1
    </select>

    <select id="findFirstTypeById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select type from butler_sys_message where receiver_account = #{id} order by send_date DESC limit 1
    </select>

    <select id="findCommentNoReadNumById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from butler_comment_message where receiver_account = #{id} and send_status = 1
    </select>

    <select id="sysMessageList" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerSysMessageVo">
        select id,type,relation_id,send_date from butler_sys_message where receiver_account = #{id}
    </select>

    <select id="sysCommentMessageList" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerCommentMessageVo">
        select id,type,relation_id,send_date from butler_comment_message where receiver_account = #{id}
    </select>
    <insert id="insert" parameterType="com.api.model.butlerApp.ButlerAppCommentMessage">
        insert into butler_comment_message(
            type,
            relation_id,
            receiver_account,
            send_status,
            send_date
        )values (
            #{type},
            #{relationId},
            #{receiverAccount},
            #{sendStatus},
            #{sendDate}
        )
    </insert>

    <insert id="insertSysMessage" parameterType="com.api.model.butlerApp.ButlerAppSysMessage">
        insert into butler_sys_message(
            type,
            relation_id,
            receiver_account,
            send_status,
            send_date
        )values (
            #{type},
            #{relationId},
            #{receiverAccount},
            #{sendStatus},
            #{sendDate}
        )
    </insert>

    <select id="findRepairByRepairId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerRepairMessageVo">
        select srr.id,srr.tel,srr.type,ur.name from sys_report_repair as srr,user_resident as ur
        where srr.repairman = ur.id and srr.id = #{repairId}
    </select>

    <select id="findCommentByDispatchId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerRepairCommentMesVo">
        select sdl.id,ur.name,sdl.evaluation_level as level from sys_report_repair as srr,user_resident as ur,sys_dispatch_list as sdl
        where srr.repairman = ur.id and srr.dispatch_list_id = sdl.id and sdl.id = #{dispatchId}
    </select>
    <select id="findFirstSysDateById" parameterType="java.lang.Integer" resultType="java.util.Date">
        select send_date from butler_sys_message where receiver_account = #{id} order by send_date DESC limit 1
    </select>
    <select id="findFirstCommentDateById" parameterType="java.lang.Integer" resultType="java.util.Date">
        select send_date from butler_comment_message where receiver_account = #{id} order by send_date DESC limit 1
    </select>
    <select id="findGreenTaskByGreenId" parameterType="java.lang.Integer" resultType="com.api.model.butlerApp.ButlerGreenTaskMesVo">
        select sgt.id,su.actual_name as name,su.tel as tel,sga.name as area_name
        from sys_green_task as sgt,sys_green_area as sga,sys_user as su
        where sgt.green_area_id = sga.id and sgt.director = su.id and sgt.id = #{greenTaskId}
    </select>
    <select id="findHygieneTaskByHygieneId" parameterType="java.lang.Integer" resultType="com.api.model.butlerApp.ButlerHygieneTaskMesVo">
        select sht.id,su.actual_name as name,su.tel as tel,sha.name as area_name
        from sys_hygiene_task as sht,sys_hygiene_area as sha,sys_user as su
        where sht.hygiene_area_id = sha.id and sht.director = su.id and sht.id = #{hygieneTaskId}
    </select>

    <update id="allReadSys" parameterType="java.lang.Integer">
        update butler_sys_message
        set send_status = 3
        where receiver_account = #{id}
    </update>
    <update id="allReadComment" parameterType="java.lang.Integer">
        update butler_comment_message
        set send_status = 3
        where receiver_account = #{id}
    </update>

    <select id="findHousekeepingByHousekeepingId" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppHousekeepingServiceVo">
        select shs.*,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,ur.name as proposer_name,ur.tel as proposer_tel,
        su.actual_name as handler_name,su.tel as handler_tel
        from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,user_resident as ur,sys_housekeeping_service as shs
        left join sys_user as su on shs.handler = su.id
        where shs.estate_id = cbue.id
        and cbue.building_unit_id = cbu.id
        and cbu.building_id = cb.id
        and shs.proposer = ur.id
		and shs.id = #{housekeepingServiceId}
    </select>

</mapper>