<?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.statistics" name="统计">

    <select id="getCountNumber" parameterType="map" resultType="map" expose="true">
        SELECT
            COUNT(DISTINCT je.id) num
        FROM
            jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
            je.del_flag = '0'
        AND jefl.del_flag = '0'
        <!--<if test="job_duty == '1'.toString()">
            and jefl.grade_user_id = #{_user.id}
        </if>-->
        <if test="_user.company_grade == '3'.toString()">
            <if test="fax!=null and fax!=''">
                and jefl.office_id=#{_user.company_id}
            </if>
            <if test="fax==null or fax==''">
                AND (je.city_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is not null)
                )
            </if>
        </if>
        <if test="_user.company_grade == '4'.toString()">
            <if test="fax!=null and fax!=''">
                and jefl.office_id=#{_user.company_id}
            </if>
            <if test="fax==null or fax==''">
                AND (je.area_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is not null)
                )
            </if>
        </if>
        <if test="_user.company_grade == '5'.toString()">
            <if test="fax!=null and fax!=''">
                and jefl.office_id=#{_user.company_id}
            </if>
            <if test="fax==null or fax==''">
                AND (je.street_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is not null)
                )
            </if>
        </if>
        <if test="_user.company_grade == '6'.toString()">
            AND community_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
        </if>
    </select>

    <select id="selectTitleMap" parameterType="map" resultType="map" name="首页第一行" desc="首页第一行" expose="true">
        SELECT
            COUNT(id) num,
            IFNULL(SUM(IF(create_time= CURDATE(),1,0)),0) newNum,
            IFNULL(SUM(IF(status= 2,1,0)),0) processingNum,
            IFNULL(SUM(IF(is_early_warning= 4,1,0)),0) warningNum,
            IFNULL(SUM(IF(status= 1,1,0)),0) completionNum
        FROM(
            SELECT
            distinct je.id,
            DATE_FORMAT(je.create_time,'%Y-%m-%d') create_time,
            je.status,
            je.is_early_warning
        FROM
            jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        <where>
            je.del_flag = '0'
            AND jefl.del_flag = '0'
            <if test="_user.company_grade == '3'.toString()">
                <if test="fax!=null and fax!=''">
                    and jefl.office_id=#{_user.company_id}
                </if>
                <if test="fax==null or fax==''">
                    AND (je.city_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                    or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is not null)
                    )
                </if>
            </if>
            <if test="_user.company_grade == '4'.toString()">
                <if test="fax!=null and fax!=''">
                    and jefl.office_id=#{_user.company_id}
                </if>
                <if test="fax==null or fax==''">
                    AND (je.area_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                    or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is not null)
                    )
                </if>
            </if>
            <if test="_user.company_grade == '5'.toString()">
                <if test="fax!=null and fax!=''">
                    and jefl.office_id=#{_user.company_id}
                </if>
                <if test="fax==null or fax==''">
                    AND (je.street_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                    or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is not null)
                    )
                </if>
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND je.community_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
            </if>
        </where>
        )a
    </select>

    <select id="selectLabelList" parameterType="map" resultType="map" name="纠纷排名" desc="纠纷排名" expose="true">
        SELECT
        COUNT(DISTINCT je.id) num,
        sd.label
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.value
        WHERE
        je.del_flag = '0'
        AND office_id = #{_user.company_id}
        AND sd.type = 'event_category'
        AND sd.parent_id = '0'
        GROUP BY je.event_category_id
        ORDER BY COUNT(1) DESC, sd.label ASC
    </select>

    <select id="selectEarlyWarningList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" name="预警列表" desc="预警列表" expose="true">
        SELECT
        je.event_type eventType,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
        `value`) label,
        (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.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.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        DATE_FORMAT(je.create_time,'%Y-%m-%d %T') createTime,
        jefl.id,
        jefl.event_id eventId,
        jefl.flow_version flowVersion,
        jefl.flow_id flowId,
        jefl.office_id officeId,
        jefl.status,
        jefl.log_type logType,
        jefl.assignment_type assignmentType,
        jefl.log_result logResult,
        jefl.resolve_name resolveName,
        jefl.resolve_unit resolveUnit,
        jefl.resolve_hierarchy resolveHierarchy,
        jefl.resolve_time resolveTime,
        jefl.is_satisfaction isSatisfaction,
        jefl.resolve_process resolveProcess,
        jefl.is_resolve isResolve,
        jefl.warning_level warningLevel,
        jefl.contacts,
        jefl.attachment,
        jefl.deal_warn_time dealWarnTime,
        jefl.deal_end_time dealEndTime
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.value
        WHERE
        je.del_flag = '0'
        AND sd.type = 'event_category'
        AND sd.parent_id = '0'
        AND office_id = #{_user.company_id}
        AND je.is_early_warning = '3'
        ORDER BY je.create_time DESC
    </select>

    <select id="selectAgencyHandlingList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT * FROM (SELECT
        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.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,
        je.event_code eventCode,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.garden_id gardenId,
        je.building_id buildingId,
        je.unit_id unitId,
        je.floor_id floorId,
        je.doorplate_id doorplateId,
        je.event_source eventSource,
        je.address,
        DATE_FORMAT(je.create_time,'%Y-%m-%d %T') createTime,
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') occurrenceTime,
        sd.label,
        sa.name city,
        sa.id cityId,
        sa1.name area,
        sa1.id areaId,
        je.dispute_description disputeDescription,
        je.id,
        jefl.flow_version flowVersion,
        jefl.flow_id flowId,
        jefl.office_id officeId,
        jefl.status,
        jefl.log_type logType,
        jefl.log_result logResult,
        jefl.resolve_name resolveName,
        jefl.resolve_unit resolveUnit,
        jefl.resolve_hierarchy resolveHierarchy,
        jefl.resolve_time resolveTime,
        jefl.is_satisfaction isSatisfaction,
        jefl.resolve_process resolveProcess,
        jefl.is_resolve isResolve,
        jefl.warning_level warningLevel,
        jefl.contacts,
        jefl.attachment,
        jefl.deal_warn_time dealWarnTime,
        jefl.deal_end_time dealEndTime,
        jefl.risk_level riskLevel,
        (SELECT IFNULL(assignment_type,'9999') FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id = #{params._user.id} ORDER BY create_time DESC LIMIT 1) assignmentType,
        (SELECT IFNULL(fax,'') FROM sys_office WHERE id = jefl.office_id) fax,
        (SELECT IFNULL(IF(grade_user_id != '' AND grade_user_id IS NOT NULL AND log_type = '1', 2, 1), 2) FROM jl_event_flow_log WHERE event_id = je.id ORDER BY create_time DESC LIMIT 1) logStatus
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.`value`
        LEFT JOIN sys_area sa ON je.city_id = sa.`code`
        LEFT JOIN sys_area sa1 ON je.area_id = sa1.`code`
        WHERE
        jefl.grade_user_id = #{params._user.id}
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND sd.type = 'event_category'
        AND jefl.status != 3
        AND jefl.grade_user_id IS NOT NULL
        <if test="params.month != null and params.month != ''">
            <!-- 移动端会通过此参数传递日期， 格式有：[年-月]或[年] 所以通过like查询-->
            AND je.create_time LIKE CONCAT(#{params.month},'%')
        </if>
        GROUP BY je.event_code
        ORDER BY je.create_time) a
    </select>

    <select id="selectAgencyHandlingGridList" parameterType="map" resultType="map">
        SELECT
        jefl.event_id,
        je.event_code,
        je.reporting_name,
        DATE_FORMAT(je.create_time,'%Y-%m-%d %T') create_time,
        sd.label
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.value
        WHERE
        je.status = '2'
        AND jefl.grade_user_id = #{params._user.id}
        AND jefl.log_type = '2'
        AND sd.type = '0'
        AND jefl.`status` = '1'
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
    </select>

    <select id="getFourType" parameterType="map" resultType="map" name="四类统计" desc="四类矛盾统计" expose="true">
        SELECT
        FORMAT(nums.neighbourhoodNum/COUNT(1)* 100,2) neighbourhoodNum,
        FORMAT(nums.labourNum/COUNT(1)* 100,2) labourNum,
        FORMAT(realRightNum/COUNT(1)* 100,2) realRightNum,
        FORMAT(marriageNum/COUNT(1)* 100,2) marriageNum
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        ,( SELECT
        IFNULL(SUM(IF(je.event_category_id = '2',1,0)),0) neighbourhoodNum,
        IFNULL(SUM(IF(je.event_category_id = '8',1,0)),0) labourNum,
        IFNULL(SUM(IF(je.event_category_id = '6',1,0)),0) realRightNum,
        IFNULL(SUM(IF(je.event_category_id = '1',1,0)),0) marriageNum
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE je.del_flag = '0' AND office_id = #{_user.company_id}
        ) nums
        WHERE
        je.del_flag = '0'
        AND office_id = #{_user.company_id}
    </select>


    <select id="findEventOfficeStatusList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT * FROM (
        SELECT
        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.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.create_time,'%Y-%m-%d %T') createTime,
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') 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.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        (SELECT IFNULL(assignment_type,'9999') 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) assignmentType,
        (SELECT IFNULL(fax,'') FROM sys_office WHERE id = jefl.office_id) fax,
        jefl.event_id id,
        jefl.event_id eventId,
        jefl.flow_version flowVersion,
        jefl.flow_id flowId,
        jefl.office_id officeId,
        jefl.status,
        jefl.log_type logType,
        jefl.log_result logResult,
        jefl.resolve_name resolveName,
        jefl.resolve_unit resolveUnit,
        jefl.resolve_hierarchy resolveHierarchy,
        jefl.resolve_time resolveTime,
        jefl.is_satisfaction isSatisfaction,
        jefl.resolve_process resolveProcess,
        jefl.is_resolve isResolve,
        jefl.warning_level warningLevel,
        jefl.contacts,
        jefl.risk_level riskLevel,
        jefl.attachment,
        jefl.deal_warn_time dealWarnTime,
        jefl.deal_end_time dealEndTime,
        (SELECT IFNULL(IF(status = '3', 2, 1), 2) 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) logStatus
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
        jefl.office_id = #{params._user.company_id}
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND jefl.grade_user_id IS NULL
        AND jefl.status != 3
        <if test="params.month != null and params.month != ''">
            <!-- 移动端会通过此参数传递日期， 格式有：[年-月]或[年] 所以通过like查询-->
            AND je.create_time LIKE CONCAT(#{params.month},'%')
        </if>
        ORDER BY je.occurrence_time
        ) a
    </select>
    <select id="selectBuildingNumByGardenId" parameterType="map" resultType="map">
        select count(*) num from jl_grid_building
        <where>
            del_flag = '0'
            <if test="null != communityId and ''!= communityId">
                and community_id = #{communityId}
            </if>
            <if test="null != gardenId and ''!= gardenId">
                and garden_id = #{gardenId}
            </if>
        </where>
    </select>
    <select id="selectDoorplateNumByGardenId" parameterType="map" resultType="map">
        select count(*) num from jl_grid_doorplate
        <where>
            del_flag = '0'
            <if test="null != communityId and ''!= communityId">
                and community_id = #{communityId}
            </if>
            <if test="null != gardenId and ''!= gardenId">
                and garden_id = #{gardenId}
            </if>
            <if test="null != buildingId and ''!= buildingId">
                and building_id = #{buildingId}
            </if>
        </where>
    </select>

    <select id="selectJlGridUserByGardenId" parameterType="map" resultType="map">
        select count(*) num from jl_grid_user
        <where>
            del_flag = '0'
            <if test="null != communityId and ''!= communityId">
                and community_id = #{communityId}
            </if>
            <if test="null != gardenId and ''!= gardenId">
                and garden_id =#{gardenId}
            </if>

            <if test="null != buildingId and ''!= buildingId">
                and building_id = #{buildingId}
            </if>
            <if test="null != sex and ''!= sex">
                and sex =#{sex}
            </if>
            <if test="null != livingCondition and ''!= livingCondition">
                and living_condition =#{livingCondition}
            </if>
            <if test="null != userType and ''!= userType">
                and user_type =#{userType}
            </if>
            <if test="null != hujiType and ''!= hujiType">
                and huji_type =#{hujiType}
            </if>
        </where>
    </select>

    <select id="findSocietyCount" parameterType="map" resultType="map">
        select count(*) num from jl_grid_society where del_flag = '0'
        <if test="null != communityId and ''!= communityId">
            and community_id = #{communityId}
        </if>
    </select>
    <select id="findSubstanceCount" parameterType="map" resultType="map">
        select count(*) num from jl_grid_substance where del_flag = '0'
        <if test="null != communityId and ''!= communityId">
            and community_id = #{communityId}
        </if>
    </select>

    <select id="selectEventList" parameterType="map" resultType="map" name="矛盾化解方式数据情况统计" desc="矛盾化解方式数据情况统计" expose="true">
        SELECT
            so.`name`,
            COUNT(je.id) num,
            IFNULL(SUM(IF(je.`status`= 1,1,0)),0) completedNum,
            IFNULL(SUM(IF(je.`status`= 2,1,0)),0) incompleteNum,
            (SELECT COUNT(je.id) FROM jl_event je INNER JOIN jl_event_flow_log jefl ON je.id = jefl.event_id WHERE je.`status` = '1' AND jefl.grade_user_id IS NOT NULL AND jefl.resolve_way = '1' AND so.id = je.city_id) renminNum,
            (SELECT COUNT(je.id) FROM jl_event je INNER JOIN jl_event_flow_log jefl ON je.id = jefl.event_id WHERE je.`status` = '1' AND jefl.grade_user_id IS NOT NULL AND jefl.resolve_way = '2' AND so.id = je.city_id)xingzhengNum,
            (SELECT COUNT(je.id) FROM jl_event je INNER JOIN jl_event_flow_log jefl ON je.id = jefl.event_id WHERE je.`status` = '1' AND jefl.grade_user_id IS NOT NULL AND jefl.resolve_way = '3' AND so.id = je.city_id)sifanum,
            (SELECT COUNT(je.id) FROM jl_event je INNER JOIN jl_event_flow_log jefl ON je.id = jefl.event_id WHERE je.`status` = '1' AND jefl.grade_user_id IS NOT NULL AND jefl.resolve_way = '4' AND so.id = je.city_id)hangyeNum,
            (SELECT COUNT(je.id) FROM jl_event je INNER JOIN jl_event_flow_log jefl ON je.id = jefl.event_id WHERE je.`status` = '1' AND jefl.grade_user_id IS NOT NULL AND jefl.resolve_way = '5' AND so.id = je.city_id)zhongcaiNum,
            (SELECT COUNT(je.id) FROM jl_event je INNER JOIN jl_event_flow_log jefl ON je.id = jefl.event_id WHERE je.`status` = '1' AND jefl.grade_user_id IS NOT NULL AND jefl.resolve_way = '6' AND so.id = je.city_id)susongNum,
            (SELECT COUNT(je.id) FROM jl_event je INNER JOIN jl_event_flow_log jefl ON je.id = jefl.event_id WHERE je.`status` = '1' AND jefl.grade_user_id IS NOT NULL AND jefl.resolve_way = '7' AND so.id = je.city_id) qitaNum
        FROM
            sys_office so
        LEFT JOIN jl_event je ON so.id = je.city_id
        WHERE
            so.grade = '3' AND fax IS NULL
        GROUP BY so.id
    </select>

    <select id="selectGridUserList" parameterType="map" resultType="map" expose="true">
        SELECT
            jguft.id,
            jguft.name,
            COUNT(jguf.id) AS num
        FROM
            jl_grid_user_focusgroup jguf
        LEFT JOIN jl_grid_user jgu ON jgu.cardno = jguf.cardno
        LEFT JOIN jl_grid_focusgroup_type jguft ON jguf.lable_id = jguft.id
        WHERE
            jgu.community_id = #{_user.company_id}
        GROUP BY jguf.lable_id
    </select>

    <select id="selectGridBuildingList" parameterType="map" resultType="map" expose="true">
        SELECT
            CASE jgu.living_condition
            WHEN '1' THEN '自住'
            WHEN '1' THEN '租住'
            WHEN '1' THEN '借住'
            WHEN '1' THEN '其他'
            ELSE '未知'
            END living_condition,
            COUNT(jgu.id) AS num
        FROM
            jl_grid_user jgu
        WHERE
            jgu.community_id = #{_user.company_id}
        GROUP BY jgu.living_condition
    </select>

    <select id="selectGridSubstanceList" parameterType="map" resultType="map" expose="true">
        SELECT
        (SELECT COUNT(1) FROM jl_grid_substance WHERE community_id = #{_user.company_id}) substanceNumber,
        (SELECT COUNT(1) FROM jl_grid_merchant WHERE community_id = #{_user.company_id}) merchantNumber,
        (SELECT COUNT(1) FROM jl_grid_society WHERE community_id = #{_user.company_id}) societyNumber,
        (SELECT COUNT(1) FROM jl_grid_org WHERE community_id = #{_user.company_id}) orgNumber
    </select>

    <select id="selectGridSocietyList" parameterType="map" resultType="map" expose="true">
        SELECT
            jgg.id,
            jgg.name,
            COUNT(jgs.id) AS num
        FROM
            jl_grid_society jgs
        LEFT JOIN jl_grid_garden jgg ON jgs.garden_id = jgg.id
        WHERE
            jgs.community_id = #{_user.company_id}
        GROUP BY jgs.garden_id
    </select>

    <select id="selectEventCommunityList" parameterType="map" resultType="map" expose="true">
        SELECT
            sd.label name,
            COUNT(je.id) num
        FROM
            jl_event je
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.value
        WHERE
            je.community_id = #{_user.company_id}
        AND sd.type = 'event_category'
        AND sd.parent_id = '0'
        GROUP BY  je.event_category_id
    </select>

    <select id="selectZdryList" resultType="map" expose="true">
        SELECT
            so.name,
            IFNULL(SUM(IF(jgu.is_zdry = '0',1,0)),0) num
        FROM
            sys_office so
        LEFT JOIN jl_grid_user jgu ON so.id = jgu.city_id
        WHERE
            so.grade = '3'
        AND so.fax IS NULL
        GROUP BY so.id
        ORDER BY so.sort ASC
    </select>

    <select id="selectVisitList" resultType="map" expose="true">
        SELECT
            so.name,
            COUNT(jvl.id) num
        FROM
            sys_office so
        LEFT JOIN jl_visit_log jvl ON so.id = jvl.city_id
        WHERE
            so.grade = '3'
        AND so.fax IS NULL
        GROUP BY so.id
        ORDER BY so.sort ASC
    </select>

    <select id="selectFocusGroupList" resultType="map" expose="true">
        SELECT
            jgft.name,
            COUNT(jguf.id) num
        FROM
            jl_grid_focusgroup_type jgft
        LEFT JOIN jl_grid_user_focusgroup jguf ON jgft.id = jguf.lable_id
        GROUP BY jguf.lable_id
        ORDER BY jgft.sort ASC
    </select>
</mapper>
