<?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.systemDataBigScreen.dao.SystemDataDao">
    <select id="findNowAddNum" parameterType="com.api.model.systemDataBigScreen.DispatchListSearch" resultType="com.api.vo.systemDataBigScreen.SDDispatchNumListVo">
        select date(create_date) as one_day,count(id) as num
        from sys_dispatch_list
        <where>
            <if test="beginDate != null">
                create_date <![CDATA[ >= ]]> date_format(#{beginDate},'%Y%m%d')
            </if>
            <if test="endDate != null">
                and create_date <![CDATA[ <= ]]> date_format(#{endDate},'%Y%m%d')
            </if>
        </where>
        GROUP BY one_day ORDER BY one_day desc
    </select>

    <select id="findNowSolveNum" parameterType="com.api.model.systemDataBigScreen.DispatchListSearch" resultType="com.api.vo.systemDataBigScreen.SDDispatchNumListVo">
        select date(end_date) as one_day,count(id) as num
        from sys_dispatch_list
        <where>
            <if test="beginDate != null">
                end_date <![CDATA[ >= ]]> date_format(#{beginDate},'%Y%m%d')
            </if>
            <if test="endDate != null">
                and end_date <![CDATA[ <= ]]> date_format(#{endDate},'%Y%m%d')
            </if>
            and end_date is not null
        </where>
        GROUP BY one_day ORDER BY one_day desc
    </select>

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

    <select id="findProcessingNum" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list where status = 3
    </select>

    <select id="userVisitorsList" resultType="com.api.vo.systemDataBigScreen.SDUserVisitorsVo">
        select CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,uv.name,uv.tel,uv.visit_date_start as expected_visit_date,
			(case uv.create_id when -1 then '二维码填入' else (select ur.name from user_resident as ur where ur.id = uv.create_id) end) as invite_name,
			(case uv.peers is null when true then 0 else uv.peers end) as peers
        from user_visitors_new as uv,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where uv.estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id
    </select>

    <select id="sysAdviceList" resultType="com.api.vo.systemDataBigScreen.SDSysAdviceVo">
        select type,status,content,create_date from sys_advice where is_delete = 1 and status = 3
    </select>

    <select id="sysAnnouncementList" resultType="com.api.vo.systemDataBigScreen.SDSysAnnouncementVo">
        select T.* from (
            select title,content,status,(case status when 2 then release_date else scheduled_release_time end) as release_date
            from sys_announcement_management
            where status in (2,3)
        ) as T order by T.release_date desc
    </select>
    <select id="findBuildingNum" resultType="java.lang.Integer">
        select count(*) from cpm_building
    </select>

    <select id="findEstateNum" resultType="java.lang.Integer">
        select count(*) from cpm_building_unit_estate where is_delete = 1
    </select>

    <select id="findCheckInEstateNum" resultType="java.lang.Integer">
        select count(*) from cpm_building_unit_estate where is_delete = 1 and status in (1,5)
    </select>

    <select id="findParkingNum" resultType="java.lang.Integer">
        select count(*) from cpm_parking_space
    </select>

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

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

    <select id="findUserCar" resultType="java.lang.Integer">
        select count(distinct code) from user_car
    </select>

    <select id="findResidentNum" resultType="java.lang.Integer">
        select count(*) from user_resident where type = 1
    </select>

    <select id="findTenantNum" resultType="java.lang.Integer">
        select count(*) from user_resident where type = 3
    </select>

    <select id="findThisYearPayableNum" parameterType="java.util.Date" resultType="java.lang.Integer">
        select count(sdp.id)
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and YEAR(sdp.begin_date) = YEAR(#{date}) and sctd.marker in (1,5) and sdp.is_delete = 1
    </select>

    <select id="findThisYearPayablePrice" parameterType="java.util.Date" resultType="java.math.BigDecimal">
        select sum(sdp.total_price)
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and YEAR(sdp.begin_date) = YEAR(#{date}) and sctd.marker in (1,5) and sdp.is_delete = 1
    </select>

    <select id="findPaidNum" parameterType="java.util.Date" resultType="java.lang.Integer">
        select count(sdp.id)
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and YEAR(sdp.begin_date) = YEAR(#{date})  and sctd.marker in (1,5) and sdp.status = 3 and sdp.is_delete = 1
    </select>

    <select id="findPaidPrice" parameterType="java.util.Date" resultType="java.math.BigDecimal">
        select sum(sdp.paid_price)
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and YEAR(sdp.begin_date) = YEAR(#{date})  and sctd.marker in (1,5) and sdp.status in (2,3) and sdp.is_delete = 1
    </select>

    <select id="findUnPaidNum" parameterType="java.util.Date" resultType="java.lang.Integer">
        select count(sdp.id)
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and YEAR(sdp.begin_date) = YEAR(#{date})  and sctd.marker in (1,5) and sdp.status in (1,2) and sdp.is_delete = 1
    </select>

    <select id="findUnPaidPrice" parameterType="java.util.Date" resultType="java.math.BigDecimal">
        select sum(sdp.payment_price)
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and YEAR(sdp.begin_date) = YEAR(#{date})  and sctd.marker in (1,5) and sdp.status in (1,2) and sdp.is_delete = 1
    </select>


    <select id="findSixMonthUnPaidNum" resultType="com.api.vo.systemDataBigScreen.SDCountAndDate">
        select count(sdp.id) as count, year(s.date) as years, MONTH(s.date) as months from
        (select date_add(NOW(),interval @i:=@i-1 month) as date
        from (
        select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1) as tmp,
         (select @i:= 1) t
        ) s left join sys_daily_payment as sdp on YEAR(s.date) = YEAR(sdp.create_date) and MONTH(s.date) = MONTH(sdp.create_date) and sdp.status in (1,2) and sdp.is_delete = 1
        GROUP BY s.date
    </select>

    <select id="findDailyActivity" parameterType="com.api.model.systemDataBigScreen.DailyActivitySearch" resultType="com.api.vo.systemDataBigScreen.SDDailyActivityVo">
        select date(last_date) as createDate,count(DISTINCT resident_id) as daily_activity_num
        from sys_resident_request_log
        <where>
            <if test="beginDate != null">
                and last_date <![CDATA[ >= ]]> date_format(#{beginDate},'%Y%m%d')
            </if>
            <if test="endDate != null">
                and last_date <![CDATA[ <= ]]> date_format(#{endDate},'%Y%m%d')
            </if>
        </where>
        GROUP BY createDate ORDER BY createDate desc
    </select>
    <select id="findAllInspector" resultType="com.api.vo.systemDataBigScreen.SDInspectionSysUserVo">
        select DISTINCT su.id,su.actual_name,su.tel from sys_inspection_execute as sie,sys_inspection_plan as sip,sys_user as su
        where sie.inspection_plan_id = sip.id and sip.inspector = su.id
    </select>
    <select id="findTodayExecute" parameterType="java.util.Date" resultType="com.api.vo.systemDataBigScreen.SDInspectionExecuteListVo">
        select sie.id,sip.name,sie.begin_date,sie.end_date,sie.actual_begin_date,sie.actual_end_date from sys_inspection_execute as sie,sys_inspection_plan as sip
        where sie.inspection_plan_id = sip.id and DATE(sie.begin_date) = DATE(#{date}) order by sie.begin_date
    </select>

    <select id="findNowExecute" parameterType="java.util.Date" resultType="com.api.vo.systemDataBigScreen.SDInspectionExecuteVo">
        select sie.id,sip.name,sie.begin_date,sie.end_date,sie.actual_begin_date,sie.actual_end_date from sys_inspection_execute as sie,sys_inspection_plan as sip
        where sie.inspection_plan_id = sip.id and DATE(sie.begin_date) = DATE(#{date}) order by sie.begin_date
    </select>
    <select id="findExecutePointByExecuteId" parameterType="java.lang.Integer"
            resultType="com.api.vo.systemDataBigScreen.SDInspectionExecutePointVo">
        select id,code,name,complete_date,longitude,latitude from sys_inspection_execute_point where execute_id = #{executeId}
    </select>


    <select id="findExecutePointAll" resultType="com.api.vo.systemDataBigScreen.SDInspectionExecutePointVo">
        select id,code,name,complete_date,longitude,latitude from sys_inspection_execute_point
    </select>

    <select id="findPlanPointByExecuteId" parameterType="java.lang.Integer"
            resultType="com.api.vo.systemDataBigScreen.SDInspectionExecutePointVo">
        select sipo.id,sipo.code,sipo.name,sipo.longitude,sipo.latitude
        from sys_inspection_execute as sie,sys_inspection_plan as sip,sys_inspection_route as sir,sys_inspection_point_route as sipr,sys_inspection_point as sipo
        where sie.inspection_plan_id = sip.id and sip.inspection_route_id = sir.id and sipr.inspection_route_id = sir.id and sipr.inspection_point_id = sipo.id and sie.id = #{executeId}
    </select>

    <select id="findAllLocation" parameterType="java.lang.Integer" resultType="com.api.vo.systemDataBigScreen.SDInspectionExecuteMapVo">
        select longitude,latitude,create_date from sys_inspection_execute_map where execute_id = #{executeId}
    </select>

    <select id="findAllInspectionRecord" resultType="com.api.vo.systemDataBigScreen.SDInspectionRecordVo">
        select sie.id,concat(sip.name,'-',sie.sort) as name ,su.actual_name as inspector,sie.begin_date,sie.end_date,sie.actual_begin_date,sie.actual_end_date
        from sys_inspection_execute as sie,sys_inspection_plan as sip,sys_user as su
        where sie.inspection_plan_id = sip.id and sip.inspector = su.id order by sie.begin_date DESC
    </select>
    <select id="findReportDispatch" resultType="com.api.vo.systemDataBigScreen.SDReportDispatchVo">
        select sdl.id,sdl.code,
        (case sdl.status <![CDATA[ < ]]> 4 when true then 1 else 2 end) as status,
        (case sdl.end_date is null when true then srr.create_date else sdl.end_date end) as date_time,
        srr.type,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,srr.tel,srr.repair_date,
        srr.froms,srr.report_detail,srr.maintenance_labor_cast,srr.maintenance_material_cost,
        sdl.evaluation_level,sdl.evaluation_content,sdl.evaluation_date,sdl.revisit_date,sdl.revisit_detail,
        ur.name as repairman_name,su.actual_name as operator_name,swotl.name as work_order_time_limit
        from sys_report_repair as srr,user_resident as ur,cpm_building_unit_estate as cbue,cpm_building_unit cbu,
        cpm_building as cb,sys_dispatch_list as sdl
        LEFT JOIN sys_dispatch_list_detail as sdld on sdld.dispatch_list_id = sdl.id
        LEFT JOIN sys_user as su on su.id = sdld.operator
        LEFT JOIN sys_work_order_time_limit as swotl on swotl.id = sdld.work_order_time_limit
        where sdl.id = srr.dispatch_list_id and srr.repairman = ur.id and srr.building_unit_estate_id = cbue.id and sdl.sys_delete = 1
        and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id
    </select>
    <select id="findHandledNum" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list
        where status <![CDATA[ >= ]]> 4 and sys_delete = 1
    </select>
    <select id="findPendingNum" resultType="java.lang.Integer">
        select count(*) from sys_dispatch_list
        where status <![CDATA[ < ]]> 4 and sys_delete = 1
    </select>
    <select id="findPublicTypeNum" resultType="java.lang.Integer">
        select count(*) from sys_report_repair
        where type = 1
    </select>
    <select id="findFamilyTypeNum" resultType="java.lang.Integer">
        select count(*) from sys_report_repair
        where type = 2
    </select>

    <select id="findAllArticle" resultType="java.lang.Integer">
        select count(*) from sys_article_detail
    </select>

    <select id="findBorrowArticle" resultType="java.lang.Integer">
        select count(*) from sys_article_borrow as sab,sys_article_detail as sad
        where sab.article_detail_id = sad.id and sab.borrow_status in (1,3)
    </select>
    <select id="findBreakDownArticle" resultType="java.lang.Integer">
        select count(*) from sys_article_detail where status = 2
    </select>
    <select id="findLoseArticle" resultType="java.lang.Integer">
        select count(*) from sys_article_detail where status = 3
    </select>
    <select id="findActivity" resultType="com.api.vo.systemDataBigScreen.SDSysActivityVo">
        select title,content,registration_start_time,registration_end_time
        from sys_activity_management
        where is_delete = 1
        order by create_date DESC
    </select>
    <select id="findVisitorInfo" resultType="com.api.vo.systemDataBigScreen.SDVisitorInfoVo">
        select count(u.id) as visitor_num,s.date as date_time from
        (select date_add(DATE(NOW()),interval @i:=@i-1 day) as date
        from (
        select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1) as tmp,
         (select @i:= 1) t
        ) s left join user_visitors_new as u on s.date = date_format(u.visit_date_start,'%Y-%m-%d')
        GROUP BY s.date
    </select>
    <select id="findAllUnpaidFees" resultType="java.lang.Integer">
        select sum(payment_price) as unpaidFees from sys_daily_payment
    </select>
    <select id="findRegCount" resultType="java.lang.Integer">
        select count(*) from user_resident where create_id = -1
    </select>
    <select id="findPointByExecuteIdAfter" parameterType="java.lang.Integer" resultType="com.api.vo.systemDataBigScreen.SDInspectionExecutePointVo">
        select * from sys_inspection_execute_point where execute_id = #{executeId}
    </select>

    <select id="findPointByExecuteIdBefore" parameterType="java.lang.Integer"
            resultType="com.api.vo.systemDataBigScreen.SDInspectionExecutePointVo">
        select sipo.*
        from sys_inspection_execute as sie,sys_inspection_plan as sip,
        sys_inspection_point_route as sipr,sys_inspection_point as sipo
        where sie.inspection_plan_id = sip.id and sip.inspection_route_id = sipr.inspection_route_id
        and sipr.inspection_point_id = sipo.id and sie.id = #{executeId}
    </select>

    <select id="findExecuteByExecuteId" parameterType="java.lang.Integer" resultType="com.api.vo.systemDataBigScreen.SDInspectionExecuteListVo">
        select * from sys_inspection_execute where id = #{executeId}
    </select>
    <select id="findAllInspectionRoute" resultType="com.api.vo.systemDataBigScreen.SDInspectionRouteVo">
        select * from sys_inspection_route where is_delete = 1
    </select>
    <select id="findExecuteByRoute" parameterType="java.lang.Integer" resultType="com.api.vo.systemDataBigScreen.SDInspectionExecutePlanVo">
        select sie.id as executeId,sip.name,sie.begin_date,sie.end_date
        from sys_inspection_execute as sie,sys_inspection_plan as sip
        where sie.inspection_plan_id = sip.id and sip.inspection_route_id = #{routeId} order by sie.begin_date DESC
    </select>
    <select id="findGreenTaskList" resultType="com.api.vo.operationManagement.VoGreenTask">
        select sgt.id,sga.name as green_area_name,sgt.content,su.actual_name as director_name,sgt.status,sgt.end_date,sgt.create_date
        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
        order by sgt.create_date desc
    </select>
    <select id="findVisitorInfoMonth" resultType="com.api.vo.systemDataBigScreen.SDVisitorInfoMonthVo">
        select count(u.id) as visitor_num,YEAR(s.date) as years,MONTH(s.date) as months from
        (select date_add(DATE(NOW()),interval @i:=@i-1 month) as date
        from (
        select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1
        union all select 1) as tmp,
         (select @i:= 0) t
        ) s left join user_visitors_new as u on  YEAR(s.date)  = YEAR(u.visit_date_start) and MONTH(s.date) = MONTH(u.visit_date_start)
        GROUP BY s.date
    </select>
    <select id="findWaitOutNum" resultType="java.lang.Integer">
        select count(*) from user_article_out where status = 1
    </select>
    <select id="findGoOutNum" resultType="java.lang.Integer">
        select count(*) from user_article_out where status = 2
    </select>
    <select id="findRejectNum" resultType="java.lang.Integer">
        select count(*) from user_article_out where status = 3
    </select>
    <select id="findVoteInfo" resultType="com.api.vo.systemDataBigScreen.SDVoteInfoVo">
        select * from sys_vote
        where is_delete = 1 and is_release = 1
        order by create_date desc
    </select>
    <select id="findShopAppointmentNum" resultType="com.api.vo.systemDataBigScreen.SDShopAppointmentNumVo">
        select sc.name,count(sga.id) as appointment_num
        from
        (
            select * from shop_category where parent_id = 0
        ) as sc
        LEFT JOIN shop_category as sc2 on sc2.id_path like CONCAT('%::',sc.id,'::%')
        LEFT JOIN shop_goods as sg on sg.category_id = sc2.id and sg.is_delete = 1
        LEFT JOIN shop_goods_appointment as sga on sga.goods_id = sg.id
        GROUP BY sc.id
    </select>
    <select id="findOccupancyRate" resultType="com.api.vo.systemDataBigScreen.SDOccupancyRateVo">
        select cb.name,count(cbue.id) as total,count(cbue2.id) as occupancy_num,round((case count(cbue.id) = 0 when true then 0 else count(cbue2.id)/count(cbue.id) end),2)*100 as occupancy_rate
        from cpm_building as cb
        LEFT JOIN cpm_building_unit as cbu on cbu.building_id = cb.id
        LEFT JOIN cpm_building_unit_estate as cbue on cbue.building_unit_id = cbu.id and cbue.is_delete = 1
        LEFT JOIN cpm_building_unit_estate as cbue2 on cbue2.id = cbue.id and cbue2.status in (1,5) and cbue2.is_delete = 1
        group by cb.id
    </select>
    <select id="findExcessiveWorkOrderUserName" parameterType="java.lang.Integer" resultType="java.lang.String">
        select T.actual_name from (
            select su.actual_name,count(su.actual_name) as num
            from sys_report_repair as srr,sys_dispatch_list as sdl,sys_dispatch_list_detail as sdld,sys_user as su
            where srr.dispatch_list_id = sdl.id and sdld.dispatch_list_id = sdl.id and sdl.status = 3 and sdld.operator = su.id
            group by su.actual_name
        ) as T where T.num <![CDATA[ >= ]]> #{threshold}
    </select>
    <select id="findExcessiveGreenTaskUserName" parameterType="java.lang.Integer" resultType="java.lang.String">
        select T.actual_name from (
                select su.actual_name,count(su.actual_name) as num
                from sys_green_task as sgt,sys_user as su
                where sgt.director= su.id and sgt.status = 1 and sgt.end_date >= NOW()
                GROUP BY su.actual_name
        )  as T where T.num <![CDATA[ >= ]]> #{threshold}
    </select>
    <select id="findBorrowExceedWeek" parameterType="java.util.Date" resultType="com.api.vo.systemDataBigScreen.SDBorrowExceedWeek">
        select GROUP_CONCAT(concat(cb.no,'-',cbu.no,'-',cbue.room_number)) as room_name,GROUP_CONCAT(cb.no) as building_no,GROUP_CONCAT(cbu.no) as unit_no,
        GROUP_CONCAT(cbue.room_number) as estate_name,ur.name as user_name,sab.begin_date,sad.name as article_name
        from sys_article_borrow as sab,sys_article_detail as sad,user_resident as ur
        LEFT JOIN cpm_resident_estate as cre on cre.resident_id = ur.id
        LEFT JOIN cpm_building_unit_estate as cbue on cre.building_unit_estate_id = cbue.id
        LEFT JOIN cpm_building_unit as cbu on cbue.building_unit_id = cbu.id
        LEFT JOIN cpm_building as cb on cbu.building_id = cb.id
        where sab.create_id = ur.id and sab.article_detail_id = sad.id and sab.borrow_status = 1
        GROUP BY sab.id
        order by sab.create_date
    </select>
    <select id="findUnpaidUserInfo" resultType="com.api.vo.systemDataBigScreen.SDUnpaidUserInfoVo">
        select concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,sdp.payment_price,sctd.name as charges_template_detail_name
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd,cpm_building_unit_estate as cbue,
        cpm_building_unit as cbu,cpm_building as cb
        where sdp.charges_template_detail_id = sctd.id and sdp.building_unit_estate_id = cbue.id
        and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and sdp.payment_price <![CDATA[ > ]]> 0
    </select>
    <select id="findPointByExecuteIdAll"
            resultType="com.api.vo.systemDataBigScreen.SDInspectionExecutePointAllVo">
        select sie.id as execute_id,sipo.name,sipo.longitude,sipo.latitude,null as complete_date
        from sys_inspection_execute as sie,sys_inspection_plan as sip,
        sys_inspection_point_route as sipr,sys_inspection_point as sipo
        where sie.inspection_plan_id = sip.id and sip.inspection_route_id = sipr.inspection_route_id
        and sipr.inspection_point_id = sipo.id and date_format(sie.begin_date,'%Y%m%d') = date_format(now(),'%Y%m%d')
        and sie.actual_begin_date is null
        UNION
        select siep.execute_id,siep.name,siep.longitude,siep.latitude,siep.complete_date
        from sys_inspection_execute_point as siep,sys_inspection_execute as sie
        where siep.execute_id = sie.id and date_format(sie.begin_date,'%Y%m%d') = date_format(now(),'%Y%m%d')
        and sie.actual_begin_date is not null
    </select>
    <select id="sysInspectionCheckItems"
            resultType="com.api.vo.systemDataBigScreen.SDSysInspectionCheckItemsVo">
        select * from sys_inspection_check_items
    </select>
    <select id="sysInspectionExecute" resultType="com.api.vo.systemDataBigScreen.SDSysInspectionExecuteVo">
        select * from sys_inspection_execute
    </select>
    <select id="sysInspectionExecuteCheckItems"
            resultType="com.api.vo.systemDataBigScreen.SDSysInspectionExecuteCheckItemsVo">
        select * from sys_inspection_execute_check_items
    </select>
    <select id="sysInspectionExecuteMap"
            resultType="com.api.vo.systemDataBigScreen.SDSysInspectionExecuteMapVo">
        select * from sys_inspection_execute_map
    </select>
    <select id="sysInspectionExecutePoint"
            resultType="com.api.vo.systemDataBigScreen.SDSysInspectionExecutePointVo">
        select * from sys_inspection_execute_point
    </select>
    <select id="sysInspectionPlan" resultType="com.api.vo.systemDataBigScreen.SDSysInspectionPlanVo">
        select * from sys_inspection_plan
    </select>
    <select id="sysInspectionPoint" resultType="com.api.vo.systemDataBigScreen.SDSysInspectionPointVo">
        select * from sys_inspection_point
    </select>
    <select id="sysInspectionPointRoute"
            resultType="com.api.vo.systemDataBigScreen.SDSysInspectionPointRouteVo">
        select * from sys_inspection_point_route
    </select>
    <select id="sysInspectionRoute" resultType="com.api.vo.systemDataBigScreen.SDSysInspectionRouteVo">
        select * from sys_inspection_route
    </select>

    <insert id="insertPushAlert" parameterType="com.api.model.systemDataBigScreen.FirePushAlert">
        insert into sys_fire_alarm(
            device_no,
            alarm_no,
            alarm_type,
            device_name,
            alarm_time,
            create_date
        )values (
            #{deviceNo},
            #{alarmNo},
            #{alarmType},
            #{deviceName},
            #{time},
            now()
        )
    </insert>
    <insert id="insertPlanAlarm" parameterType="com.api.model.systemDataBigScreen.PlanPushAlert">
        insert into sys_plan_alarm(
            alarm_occurrence_time,
            push_time,
            device_name,
            plan_content,
            alarm_content,
            create_date,
            status
        )values (
            #{alarmOccurrenceTime},
            #{pushTime},
            #{deviceName},
            #{planContent},
            #{alarmContent},
            now(),
            1
        )
    </insert>

    <select id="userVisitorsNew" resultType="com.api.vo.systemDataBigScreen.SDUserVisitorsNewVo">
        select * from user_visitors_new
    </select>

    <select id="sysMeterReadingRecord" resultType="com.api.vo.systemDataBigScreen.SDSysMeterReadingRecordVo">
        select * from sys_meter_reading_record
    </select>

    <select id="sysMeterReadingShare" resultType="com.api.vo.systemDataBigScreen.SDSysMeterReadingShareVo">
        select * from sys_meter_reading_share
    </select>

    <select id="sysMeterReadingShareDetails"
            resultType="com.api.vo.systemDataBigScreen.SDSysMeterReadingShareDetailsVo">
        select * from sys_meter_reading_share_details
    </select>
    <select id="findActivityTouchScreen" resultType="com.api.vo.systemDataBigScreen.SDTSActivityVo">
        select sam.*,ssm.sponsor_unit as sponsor_name
        from sys_activity_management as sam
        LEFT JOIN sys_sponsor_management as ssm on sam.sponsor_id = ssm.id
        where sam.is_delete = 1
    </select>
    <select id="sysAnnouncementTouchScreen" resultType="com.api.vo.systemDataBigScreen.SDTSAnnouncementVo">
        select T.* from (
            select id,title,content,status,(case status when 2 then release_date else scheduled_release_time end) as release_date
            from sys_announcement_management
            where status in (2,3)
        ) as T
        where now() <![CDATA[ >= ]]> T.release_date
        order by T.release_date desc
    </select>
    <select id="sysNewCategoryTouchScreen" resultType="com.api.vo.systemDataBigScreen.SDTSNewsCategoryVo">
        select id,name from sys_news_category
    </select>

    <select id="sysNewTouchScreen" resultType="com.api.vo.systemDataBigScreen.SDTSNewVo">
        select id,title,content,create_date from sys_news where news_category_id = #{newCategoryId}
    </select>
    <select id="sysNewLatestReleaseTouchScreen" parameterType="java.lang.Integer" resultType="com.api.vo.systemDataBigScreen.SDTSNewVo">
        select id,title,content,create_date from sys_news order by create_date desc limit #{num}
    </select>
    <select id="searchActivity" parameterType="com.api.model.systemDataBigScreen.SearchTouchScreenSearch" resultType="com.api.vo.systemDataBigScreen.SDTSActivityVo">
        select sam.*,ssm.sponsor_unit as sponsor_name
        from sys_activity_management as sam
        LEFT JOIN sys_sponsor_management as ssm on sam.sponsor_id = ssm.id
        <where>
            sam.is_delete = 1
            <if test="content != null and content != ''">
                and (sam.title like concat('%',#{content},'%')
                or sam.content like concat('%',#{content},'%'))
            </if>
        </where>
        limit #{num}
    </select>
    <select id="searchAnnouncement" resultType="com.api.vo.systemDataBigScreen.SDTSAnnouncementVo">
        select T.* from (
            select id,title,content,status,(case status when 2 then release_date else scheduled_release_time end) as release_date
            from sys_announcement_management
            where status in (2,3)
        ) as T
        <where>
            now() <![CDATA[ >= ]]> T.release_date
            <if test="content != null and content != ''">
                and (T.title like concat('%',#{content},'%')
                or T.content like concat('%',#{content},'%'))
            </if>
        </where>
        order by T.release_date desc limit #{num}
    </select>
    <select id="searchNews" resultType="com.api.vo.systemDataBigScreen.SDTSNewVo">
        select id,title,content,create_date from sys_news
        <where>
            <if test="content != null and content != ''">
                title like concat('%',#{content},'%')
                or content like concat('%',#{content},'%')
            </if>
        </where>
        order by create_date desc limit #{num}
    </select>


</mapper>