<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.FocusAreaNewTaskDao" name="app业务">

    <select id="getAreaList" resultType="map" parameterType="map">
        select distinct(area_id) as area_id from jl_event
        where del_flag='0'
    </select>

	<select id="getEventCategoryList" resultType="map" parameterType="map">
        select label,value from sys_dict where del_flag='0'
        and type='event_category' and parent_id='0'
    </select>

    <select id="getLastMonthCount" parameterType="map" resultType="Integer">
        SELECT COUNT(*) AS count_last_month
        FROM jl_event
        WHERE
        event_category_id=#{value} and del_flag='0'
        and area_id=#{area_id}
        and occurrence_time >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01')
        AND occurrence_time <![CDATA[ < ]]> DATE_FORMAT(CURDATE(), '%Y-%m-01')
    </select>

    <select id="getCurrentMonthCount" parameterType="map" resultType="Integer">
        SELECT COUNT(*) AS count_current_month
        FROM jl_event
        WHERE
        event_category_id=#{value} and del_flag='0'
        and area_id=#{area_id}
        and occurrence_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
        AND occurrence_time <![CDATA[ < ]]> DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01')
    </select>

    <insert id="insertFocusAreaNew" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT count(*) FROM jl_focus_area_new WHERE month = #{month} and event_category_id=#{event_category_id} and data_type=#{data_type} and year=#{year}
            and area_code=#{area_code}
        </selectKey>
        <if test="count > 0">
            UPDATE jl_focus_area_new
            <set>
                update_time = now(),
                <if test="growth_rate != null and growth_rate !='' ">
                    growth_rate = #{growth_rate},
                </if>
                <if test="event_count != null and event_count !='' ">
                    event_count = #{event_count},
                </if>
            </set>
            WHERE month = #{month} AND event_category_id=#{event_category_id} and data_type=#{data_type} and year=#{year}
            and area_code=#{area_code}
        </if>
        <if test="count == 0">
            insert into jl_focus_area_new
            <trim prefix="(" suffix=")" suffixOverrides=",">
                id,
                <if test="event_category_id != null and event_category_id != ''">event_category_id,</if>
                <if test="growth_rate != null and growth_rate != ''">growth_rate,</if>
                <if test="month != null and month != ''">month,</if>
                <if test="year != null and year != ''">year,</if>
                <if test="data_type != null and data_type != ''">data_type,</if>
                <if test="event_count != null and event_count != ''">event_count,</if>
                <if test="area_code != null and area_code != ''">area_code,</if>
                create_time
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                uuid(),
                <if test="event_category_id != null and event_category_id != ''">#{event_category_id},</if>
                <if test="growth_rate != null and growth_rate != ''">#{growth_rate},</if>
                <if test="month != null and month != ''">#{month},</if>
                <if test="year != null and year != ''">#{year},</if>
                <if test="data_type != null and data_type != ''">#{data_type},</if>
                <if test="event_count != null and event_count != ''">#{event_count},</if>
                <if test="area_code != null and area_code != ''">#{area_code},</if>
                now()
            </trim>
        </if>
    </insert>

    <delete id="deleteFocusAreaNew" parameterType="map">
        DELETE FROM
            jl_focus_area_new
        WHERE
            month = #{month}
        and data_type=#{data_type}
        and year=#{year}
    </delete>

    <select id="getLastYearCount" parameterType="map" resultType="Integer">
        SELECT COUNT(*) AS count_last_year
        FROM jl_event
        WHERE
        event_category_id = #{value}
        AND del_flag = '0'
        and area_id=#{area_id}
        AND occurrence_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), '%Y-01-01')
        AND occurrence_time <![CDATA[ < ]]> DATE_FORMAT(CURDATE(), '%Y-01-01')
    </select>

    <select id="getCurrentYearCount" parameterType="map" resultType="Integer">
        SELECT COUNT(*) AS count_current_year
        FROM jl_event
        WHERE
        event_category_id = #{value}
        AND del_flag = '0'
        and area_id=#{area_id}
        AND occurrence_time >= DATE_FORMAT(CURDATE(), '%Y-01-01')
        AND occurrence_time <![CDATA[ < ]]> DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 YEAR), '%Y-01-01')
    </select>

    <select id="getFocusAreaPage"  parameterType="page" resultType="map">
        select
        a.id,
        a.event_category_id,
        a.growth_rate,
        a.month,
        a.create_time,
        a.update_time,
        a.data_type,
        a.year,
        a.event_count,
        b.label as event_category_id_dict,
        a.area_code,
        c.name as area_code_dict
        from jl_focus_area_new a,sys_dict b,sys_area c
        where 1=1 and a.event_category_id=b.value and b.type='event_category'
        and a.area_code=c.code
        <if test="params._user.company_grade=='3'.toString()">
            and a.area_code in (select id from sys_office where parent_id=#{params._user.company_id})
        </if>
        <if test="params._user.company_grade=='4'.toString()">
            and a.area_code=#{params._user.company_id}
        </if>
        <!--and a.event_category_id in (select distinct(event_category_id) from jl_event where del_flag='0'
            <if test="params._user.company_grade=='3'.toString()">&lt;!&ndash;市&ndash;&gt;
                and city_id=#{params._user.company_id}
            </if>
            <if test="params._user.company_grade=='4'.toString()">&lt;!&ndash;区&ndash;&gt;
                and area_id=#{params._user.company_id}
            </if>
        )-->
        <if test="params._user.company_grade == '5'.toString()"><!--街道-->
            AND a.area_code in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
<!--            AND a.area_code in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id} or street_id=#{params._user.company_id})-->
        </if>
        <if test="params._user.company_grade == '6'.toString()"><!--社区-->
            AND a.area_code = #{params._user.company_id}
        </if>
        <if test="params.event_category_id!=null and params.event_category_id!=''">
            and a.event_category_id=#{params.event_category_id}
        </if>
        <if test="params.month_s!=null and params.month_s!=''">
            and CONCAT(a.year,'-',a.month) >= #{params.month_s}
        </if>
        <if test="params.month_e!=null and params.month_e!=''">
            and CONCAT(a.year,'-',a.month) <![CDATA[ <= ]]> #{params.month_e}
        </if>
        <if test="params.data_type!=null and params.data_type!=''">
            and a.data_type=#{params.data_type}
        </if>
        order by a.month desc,a.area_code,a.event_category_id
    </select>

    <select id="getEventaPageList" parameterType="page"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        b.yellow_warn_days AS 'yellowWarnDays',
        b.red_warn_days AS 'redWarnDays',
        if(c.approve_days IS NULL ,0,c.approve_days) AS 'approveDays',
        DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'), DATE_FORMAT(a.createTime,'%Y-%m-%d')) AS 'day',
        a.*
        FROM (
        SELECT
        DISTINCT je.id,
        je.event_type eventType,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
        `value`)eventCategoryId,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` )
        eventTypeId,
        je.is_hidden_danger isHiddenDanger,
        je.risk_name as riskName,
        je.is_special_tasks isSpecialTasks,
        je.is_difficult isDifficult,
        je.is_public_opinion isPublicOpinion,
        je.is_early_warning isEarlyWarning,
        je.is_point_event isPointEvent,
        je.involved_number involvedNumber,
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
        je.city_id cityId,
        (SELECT name FROM sys_area WHERE code = je.city_id) city,
        je.area_id areaId,
        (SELECT name FROM sys_area WHERE code = je.area_id) area,
        je.street_id streetId,
        (SELECT name FROM sys_area WHERE code = je.street_id) street,
        je.community_id communityId,
        (SELECT name FROM sys_area WHERE code = je.community_id) community,
        je.garden_id gardenId,
        je.building_id buildingId,
        je.unit_id unitId,
        je.floor_id floorId,
        je.doorplate_id doorplateId,
        je.event_code eventCode,
        je.event_source eventSource,
        je.address,
        je.complete_time,
        je.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.risk_level riskLevel,
        je.status as eventStatus,
        (SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND office_id =
        #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) status,
        je.is_involve_everyone,
        je.is_involve_everyone_count,
        (select log_type from jl_event_flow_log where event_id=je.id and del_flag='0' order by create_time desc limit 1) as logType
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id
        WHERE
        je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.risk_level IS NOT NULL
        <if test="params.status != null and params.status != ''">
            AND je.status = #{params.status}
        </if>
        <if test="params.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </if>
        <if test="params.isSatisfaction != null and params.isSatisfaction != ''">
            AND jefl.is_satisfaction = #{params.isSatisfaction}
        </if>
        <if test="params.isResolve != null and params.isResolve != ''">
            AND jefl.is_resolve = #{params.isResolve}
        </if>
        <if test="params.eventCardno != null and params.eventCardno != ''">
            AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{params.eventCardno},'*#id#'))
        </if>
        <if test="params.eventTel != null and params.eventTel != ''">
            AND jeu.event_tel = #{params.eventTel}
        </if>
        <if test="params.eventUserName!=null and params.eventUserName!=''">
            and jeu.event_user_name LIKE CONCAT( #{params.eventUserName}, '%')
        </if>
        <if test="params.eventType != null and params.eventType != ''">
            AND je.event_type = #{params.eventType}
        </if>
        <if test="params._user.company_grade=='3'.toString()"><!--市-->
            and je.city_id=#{params._user.company_id}
        </if>
        <if test="params._user.company_grade=='4'.toString()"><!--区-->
            and je.area_id=#{params._user.company_id}
        </if>
        <if test="params.cityId != null and params.cityId != ''">
            AND je.city_id = #{params.cityId}
        </if>
        <if test="params.areaId != null and params.areaId != ''">
            AND je.area_id = #{params.areaId}
        </if>
        <if test="params.streetId != null and params.streetId != ''">
            AND je.street_id = #{params.streetId}
        </if>
        <if test="params.communityId != null and params.communityId != ''">
            AND je.community_id = #{params.communityId}
        </if>
        <!--
        <if test="params.gardenId != null and params.gardenId != ''">
            AND je.garden_id = #{params.gardenId}
        </if>
        <if test="params.buildingId != null and params.buildingId != ''">
            AND je.building_id = #{params.buildingId}
        </if>
        <if test="params.floorId != null and params.floorId != ''">
            AND je.floor_id = #{params.floorId}
        </if>
        <if test="params.doorplateId != null and params.doorplateId != ''">
            AND je.doorplate_id = #{params.doorplateId}
        </if>-->
        <if test="params.eventCategoryId != null and params.eventCategoryId != ''">
            AND je.event_category_id = #{params.eventCategoryId}
        </if>
        <if test="params.eventTypeId != null and params.eventTypeId != ''">
            AND je.event_type_id = #{params.eventTypeId}
        </if>
        <if test="params.isHiddenDanger != null and params.isHiddenDanger != ''">
            AND je.is_hidden_danger = #{params.isHiddenDanger}
        </if>
        <if test="params.isDifficult != null and params.isDifficult != ''">
            AND je.is_difficult = #{params.isDifficult}
        </if>
        <if test="params.isPublicOpinion != null and params.isPublicOpinion != ''">
            AND je.is_public_opinion = #{params.isPublicOpinion}
        </if>
        <if test="params.isPointEvent != null and params.isPointEvent != ''">
            AND je.is_point_event = #{params.isPointEvent}
        </if>
        <if test="params.occurrenceTime != null and params.occurrenceTime != ''">
            AND DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') = #{params.occurrenceTime}
        </if>
        <if test="params.address != null and params.address != ''">
            AND je.address LIKE CONCAT(#{params.address},'%')
        </if>
        <if test="params.occurrenceTimeStart != null and params.occurrenceTimeStart != '' and params.occurrenceTimeEnd != null and params.occurrenceTimeEnd != ''">
            AND #{params.occurrenceTimeEnd} >= je.occurrence_time
            AND je.occurrence_time >= #{params.occurrenceTimeStart}
        </if>
        <if test="params.resolveTimeStart != null and params.resolveTimeStart != '' and params.occurrenceTimeEnd != null and params.occurrenceTimeEnd != ''">
            AND #{params.occurrenceTimeEnd} >= je.complete_time
            AND je.complete_time >= #{params.resolveTimeStart}
        </if>
        <if test="params.is_involve_everyone != null and params.is_involve_everyone != ''">
            AND je.is_involve_everyone =#{params.is_involve_everyone}
        </if>
        <if test="params.is_involve_everyone_count != null and params.is_involve_everyone_count != ''">
            AND je.is_involve_everyone_count =#{params.is_involve_everyone_count}
        </if>
        <if test="params.isEarlyWarning != null and params.isEarlyWarning != ''">
            AND je.is_early_warning =#{params.isEarlyWarning}
        </if>
        <if test="params.area_code != null and params.area_code != ''">
            AND je.area_id =#{params.area_code}
        </if>
        <if test="params.logType!=null and params.logType!=''">
            <if test="params.logType=='0'.toString()">
                and je.status='1'
            </if>
            <if test="params.logType!='0'.toString()">
                having logType=#{params.logType} and je.status!='1'
            </if>
        </if>
        ) a
        INNER JOIN jl_event_overdue_warning_config b ON b.risk_type=a.riskLevel
        LEFT JOIN (SELECT sum(approve_days) AS approve_days ,event_id from jl_event_approve where del_flag='0' and
        approve_type='0' GROUP BY event_id) c ON c.event_id=a.id
        <if test="params.waring!=null and params.waring!=''">
            WHERE DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'), DATE_FORMAT(a.createTime,'%Y-%m-%d'))>  (b.yellow_warn_days+if(c.approve_days IS NULL ,0,c.approve_days))
            OR
            DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'), DATE_FORMAT(a.createTime,'%Y-%m-%d'))>(b.red_warn_days+if(c.approve_days IS NULL ,0,c.approve_days))
        </if>
        ORDER BY createTime DESC
    </select>

</mapper>
