<?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.manage.dao.dataStatistics.DataStatisticsDao">

    <select id="findLastMonthPayPrice" resultType="java.math.BigDecimal">
        select(case sum(sdp.paid_price) is null when true then 0 else sum(sdp.paid_price) end) as last_month_pay_price
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and date_format(sdp.begin_date,'%Y-%m')=date_format(DATE_SUB(now(), INTERVAL 1 MONTH),'%Y-%m')
		and sdp.status in (2,3) and sctd.marker = 1
    </select>
    <select id="findLastMonthUnpaidPrice" resultType="java.math.BigDecimal">
        select(case sum(sdp.payment_price) is null when true then 0 else sum(sdp.payment_price) end) as last_month_unpaid_price
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and date_format(sdp.begin_date,'%Y-%m')=date_format(DATE_SUB(now(), INTERVAL 1 MONTH),'%Y-%m')
		and sdp.status in (1,2) and sctd.marker = 1
    </select>
    <select id="findLastMonthShouldPayPrice" resultType="java.math.BigDecimal">
        select(case sum(sdp.total_price) is null when true then 0 else sum(sdp.total_price) end) as last_month_should_pay_price
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and date_format(sdp.begin_date,'%Y-%m')=date_format(DATE_SUB(now(), INTERVAL 1 MONTH),'%Y-%m')
		and sctd.marker = 1
    </select>
    <select id="findLastMonthUnpaidHouseholds" resultType="java.math.BigDecimal">
        select count(*) as last_month_unpaid_households
        from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and date_format(sdp.begin_date,'%Y-%m')=date_format(DATE_SUB(now(), INTERVAL 1 MONTH),'%Y-%m')
        and sdp.status in (1,2) and sctd.marker = 1
    </select>
    <select id="findPaymentStatistics" resultType="com.api.vo.dataStatistics.DSPaymentStatisticsVo">
        select (case sum(s2.total_price) is null when true then 0 else sum(s2.total_price) end) as should_pay_price,
        (case sum(s2.paid_price) is null when true then 0 else sum(s2.paid_price) end) as pay_price,
        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) as tmp,
             (select @i:= 0) t
        ) s
        LEFT JOIN
        (
            select sdp.*
            from sys_charges_template_detail as sctd,sys_daily_payment as sdp
            where sdp.charges_template_detail_id = sctd.id and sctd.marker = 1 and sdp.is_delete = 1
        ) as s2 on YEAR(s.date) = YEAR(s2.begin_date) and MONTH(s.date) = MONTH(s2.begin_date)
        GROUP BY s.date
    </select>
    <select id="findGreenTaskTotal" resultType="java.lang.Integer">
        select count(*)
        from sys_green_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
    </select>
    <select id="findGreenTaskPending" resultType="java.lang.Integer">
        select count(*)
        from sys_green_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
        and status = 1 and NOW() <![CDATA[ < ]]> end_date
    </select>
    <select id="findGreenTaskCompleted" resultType="java.lang.Integer">
        select count(*)
        from sys_green_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
        and status = 2
    </select>
    <select id="findGreenTaskUnFinished" resultType="java.lang.Integer">
        select count(*)
        from sys_green_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
        and status = 1 and NOW() <![CDATA[ > ]]> end_date
    </select>
    <select id="findHygieneTaskTotal" resultType="java.lang.Integer">
        select count(*)
        from sys_hygiene_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
    </select>
    <select id="findHygieneTaskPending" resultType="java.lang.Integer">
        select count(*)
        from sys_hygiene_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
        and status = 1 and NOW() <![CDATA[ < ]]> end_date
    </select>
    <select id="findHygieneTaskCompleted" resultType="java.lang.Integer">
        select count(*)
        from sys_hygiene_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
        and status = 2
    </select>
    <select id="findHygieneTaskUnFinished" resultType="java.lang.Integer">
        select count(*)
        from sys_hygiene_task
        where date_format(end_date,'%Y-%m-%d') = date_format(NOW(),'%Y-%m-%d')
        and status = 1 and NOW() <![CDATA[ > ]]> end_date
    </select>
    <select id="findTodayInspectionRecord" resultType="com.api.vo.dataStatistics.DSInspectionRecord">
        select sie.id,sie.begin_date,sie.end_date,sie.actual_begin_date,sie.actual_end_date,sir.space_time
        from sys_inspection_execute as sie,sys_inspection_plan as sip,sys_inspection_route as sir
        where sie.inspection_plan_id = sip.id and sip.inspection_route_id = sir.id
        and date_format(sie.begin_date,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')
        order by sie.begin_date
    </select>
    <select id="findExecutePointTotalNum" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*)
        from sys_inspection_execute_point
        where execute_id = #{id}
    </select>
    <select id="findUnfinishedExecutePointNum" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*)
        from sys_inspection_execute_point
        where execute_id = #{id} and complete_date is null
    </select>
</mapper>