<?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.constructionSelection">
    <select id="selectOfficeGradeParent" parameterType="map" resultType="map">
        SELECT
            so.id area_id,
            so.name area_name,
            so1.name city_name,
            so1.id city_id
        FROM
            sys_office so
        LEFT JOIN sys_office so1 ON so.parent_id = so1.id
        WHERE
            so.id = #{_user.company_id}
    </select>

    <select id="getCountComprehensiveGovernance" parameterType="map" resultType="Integer">
        SELECT
            COUNT(1)
        FROM
            jl_comprehensive_governance
        WHERE
            city_id = #{city_id}
        AND area_id = #{area_id}
        AND del_flag = '0'
    </select>

    <select id="getCountComprehensiveGovernanceCity" parameterType="map" resultType="Integer">
        SELECT
        COUNT(1)
        FROM
        jl_comprehensive_governance
        WHERE
        city_id = #{city_id}
        AND (area_id = null OR area_id = '')
        AND del_flag = '0'
    </select>

   <insert id="insertComprehensiveGovernance" parameterType="map">
       INSERT INTO jl_comprehensive_governance (
           id,
           city_id,
           city_name,
           area_id,
           area_name,
           grade,
           square_measure,
           construction_selection,
           functional_layout,
           institutional_staffing,
           unit_level,
           authorized_strength,
           actual_number,
           public_legal,
           reception_center,
           lawsuit,
           procuratorial,
           multicenter_other,
           multicenter_other_type,
           resident_department_fy,
           resident_department_fy_settle,
           resident_department_jcy,
           resident_department_jcy_settle,
           resident_department_gg,
           resident_department_gg_settle,
           resident_department_sa,
           resident_department_sa_settle,
           resident_department_xf,
           resident_department_xf_settle,
           resident_department_rl,
           resident_department_rl_settle,
           resident_department_rm,
           resident_department_rm_settle,
           resident_department_other,
           resident_department_other_settle,
           rotating_department_mz,
           rotating_department_mz_settle,
           rotating_department_zr,
           rotating_department_zr_settle,
           rotating_department_st,
           rotating_department_st_settle,
           rotating_department_zf,
           rotating_department_zf_settle,
           rotating_department_ws,
           rotating_department_ws_settle,
           rotating_department_ty,
           rotating_department_ty_settle,
           rotating_department_sc,
           rotating_department_sc_settle,
           rotating_department_gh,
           rotating_department_gh_settle,
           rotating_department_gqt,
           rotating_department_gqt_settle,
           rotating_department_fl,
           rotating_department_fl_settle,
           rotating_department_fxh,
           rotating_department_fxh_settle,
           rotating_department_hy,
           rotating_department_hy_settle,
           rotating_department_other,
           rotating_department_other_settle,
           accompanying_department_ms,
           accompanying_department_ms_settle,
           accompanying_department_ld,
           accompanying_department_ld_settle,
           accompanying_department_gz,
           accompanying_department_gz_settle,
           accompanying_department_jd,
           accompanying_department_jd_settle,
           accompanying_department_xl,
           accompanying_department_xl_settle,
           accompanying_department_gy,
           accompanying_department_gy_settle,
           accompanying_department_ls,
           accompanying_department_ls_settle,
           accompanying_department_other,
           accompanying_department_other_settle,
           multicenter_other_number,
           resident_department_other_settle_number,
           rotating_department_other_settle_number,
           accompanying_department_other_settle_number,
           del_flag,
           create_by,
           create_time
       ) VALUES (
           UUID(),
           #{city_id},
           #{city_name},
           #{area_id},
           #{area_name},
           #{grade},
           #{square_measure},
           #{construction_selection},
           #{functional_layout},
           #{institutional_staffing},
           #{unit_level},
           #{authorized_strength},
           #{actual_number},
           #{public_legal},
           #{reception_center},
           #{lawsuit},
           #{procuratorial},
           #{multicenter_other},
           #{multicenter_other_type},
           #{resident_department_fy},
           #{resident_department_fy_settle},
           #{resident_department_jcy},
           #{resident_department_jcy_settle},
           #{resident_department_gg},
           #{resident_department_gg_settle},
           #{resident_department_sa},
           #{resident_department_sa_settle},
           #{resident_department_xf},
           #{resident_department_xf_settle},
           #{resident_department_rl},
           #{resident_department_rl_settle},
           #{resident_department_rm},
           #{resident_department_rm_settle},
           #{resident_department_other},
           #{resident_department_other_settle},
           #{rotating_department_mz},
           #{rotating_department_mz_settle},
           #{rotating_department_zr},
           #{rotating_department_zr_settle},
           #{rotating_department_st},
           #{rotating_department_st_settle},
           #{rotating_department_zf},
           #{rotating_department_zf_settle},
           #{rotating_department_ws},
           #{rotating_department_ws_settle},
           #{rotating_department_ty},
           #{rotating_department_ty_settle},
           #{rotating_department_sc},
           #{rotating_department_sc_settle},
           #{rotating_department_gh},
           #{rotating_department_gh_settle},
           #{rotating_department_gqt},
           #{rotating_department_gqt_settle},
           #{rotating_department_fl},
           #{rotating_department_fl_settle},
           #{rotating_department_fxh},
           #{rotating_department_fxh_settle},
           #{rotating_department_hy},
           #{rotating_department_hy_settle},
           #{rotating_department_other},
           #{rotating_department_other_settle},
           #{accompanying_department_ms},
           #{accompanying_department_ms_settle},
           #{accompanying_department_ld},
           #{accompanying_department_ld_settle},
           #{accompanying_department_gz},
           #{accompanying_department_gz_settle},
           #{accompanying_department_jd},
           #{accompanying_department_jd_settle},
           #{accompanying_department_xl},
           #{accompanying_department_xl_settle},
           #{accompanying_department_gy},
           #{accompanying_department_gy_settle},
           #{accompanying_department_ls},
           #{accompanying_department_ls_settle},
           #{accompanying_department_other},
           #{accompanying_department_other_settle},
           #{multicenter_other_number},
           #{resident_department_other_settle_number},
           #{rotating_department_other_settle_number},
           #{accompanying_department_other_settle_number},
           '0',
           #{_user.id},
           NOW()
       )
   </insert>

    <update id="updateComprehensiveGovernance" parameterType="map">
        UPDATE
            jl_comprehensive_governance
        SET
            construction_selection = #{construction_selection},
            square_measure = #{square_measure},
            functional_layout = #{functional_layout},
            institutional_staffing = #{institutional_staffing},
            unit_level = #{unit_level},
            authorized_strength = #{authorized_strength},
            actual_number = #{actual_number},
            public_legal = #{public_legal},
            reception_center = #{reception_center},
            lawsuit = #{lawsuit},
            procuratorial = #{procuratorial},
            multicenter_other = #{multicenter_other},
            multicenter_other_type = #{multicenter_other_type},
            multicenter_other_number = #{multicenter_other_number},
            resident_department_other_settle_number = #{resident_department_other_settle_number},
            rotating_department_other_settle_number = #{rotating_department_other_settle_number},
            accompanying_department_other_settle_number = #{accompanying_department_other_settle_number},
            resident_department_fy = #{resident_department_fy},
            resident_department_fy_settle = #{resident_department_fy_settle},
            resident_department_jcy = #{resident_department_jcy},
            resident_department_jcy_settle = #{resident_department_jcy_settle},
            resident_department_gg = #{resident_department_gg},
            resident_department_gg_settle = #{resident_department_gg_settle},
            resident_department_sa = #{resident_department_sa},
            resident_department_sa_settle = #{resident_department_sa_settle},
            resident_department_xf = #{resident_department_xf},
            resident_department_xf_settle = #{resident_department_xf_settle},
            resident_department_rl = #{resident_department_rl},
            resident_department_rl_settle = #{resident_department_rl_settle},
            resident_department_rm = #{resident_department_rm},
            resident_department_rm_settle = #{resident_department_rm_settle},
            resident_department_other = #{resident_department_other},
            resident_department_other_settle = #{resident_department_other_settle},
            rotating_department_mz = #{rotating_department_mz},
            rotating_department_mz_settle = #{rotating_department_mz_settle},
            rotating_department_zr = #{rotating_department_zr},
            rotating_department_zr_settle = #{rotating_department_zr_settle},
            rotating_department_st = #{rotating_department_st},
            rotating_department_st_settle = #{rotating_department_st_settle},
            rotating_department_zf = #{rotating_department_zf},
            rotating_department_zf_settle = #{rotating_department_zf_settle},
            rotating_department_ws = #{rotating_department_ws},
            rotating_department_ws_settle = #{rotating_department_ws_settle},
            rotating_department_ty = #{rotating_department_ty},
            rotating_department_ty_settle = #{rotating_department_ty_settle},
            rotating_department_sc = #{rotating_department_sc},
            rotating_department_sc_settle = #{rotating_department_sc_settle},
            rotating_department_gh = #{rotating_department_gh},
            rotating_department_gh_settle = #{rotating_department_gh_settle},
            rotating_department_gqt = #{rotating_department_gqt},
            rotating_department_gqt_settle = #{rotating_department_gqt_settle},
            rotating_department_fl = #{rotating_department_fl},
            rotating_department_fl_settle = #{rotating_department_fl_settle},
            rotating_department_fxh = #{rotating_department_fxh},
            rotating_department_fxh_settle = #{rotating_department_fxh_settle},
            rotating_department_hy = #{rotating_department_hy},
            rotating_department_hy_settle = #{rotating_department_hy_settle},
            rotating_department_other = #{rotating_department_other},
            rotating_department_other_settle = #{rotating_department_other_settle},
            accompanying_department_ms = #{accompanying_department_ms},
            accompanying_department_ms_settle = #{accompanying_department_ms_settle},
            accompanying_department_ld = #{accompanying_department_ld},
            accompanying_department_ld_settle = #{accompanying_department_ld_settle},
            accompanying_department_gz = #{accompanying_department_gz},
            accompanying_department_gz_settle = #{accompanying_department_gz_settle},
            accompanying_department_jd = #{accompanying_department_jd},
            accompanying_department_jd_settle = #{accompanying_department_jd_settle},
            accompanying_department_xl = #{accompanying_department_xl},
            accompanying_department_xl_settle = #{accompanying_department_xl_settle},
            accompanying_department_gy = #{accompanying_department_gy},
            accompanying_department_gy_settle = #{accompanying_department_gy_settle},
            accompanying_department_ls = #{accompanying_department_ls},
            accompanying_department_ls_settle = #{accompanying_department_ls_settle},
            accompanying_department_other = #{accompanying_department_other},
            accompanying_department_other_settle = #{accompanying_department_other_settle},
            <if test="del_flag != null and del_flag != ''">
                del_flag = #{del_flag},
            </if>
            update_by = #{_user.id},
            update_time = NOW()
        WHERE
            id = #{id}
    </update>

    <select id="selectComprehensiveGovernanceList" parameterType="map" resultType="map">
        SELECT
            id,
            city_id,
            city_name,
            area_id,
            area_name,
            square_measure,
            construction_selection,
            functional_layout,
            institutional_staffing,
            unit_level,
            authorized_strength,
            actual_number,
            public_legal,
            reception_center,
            lawsuit,
            procuratorial,
            multicenter_other,
            multicenter_other_type,
            multicenter_other_number,
            resident_department_other_settle_number,
            rotating_department_other_settle_number,
            accompanying_department_other_settle_number,
            resident_department_fy,
            resident_department_fy_settle,
            resident_department_jcy,
            resident_department_jcy_settle,
            resident_department_gg,
            resident_department_gg_settle,
            resident_department_sa,
            resident_department_sa_settle,
            resident_department_xf,
            resident_department_xf_settle,
            resident_department_rl,
            resident_department_rl_settle,
            resident_department_rm,
            resident_department_rm_settle,
            resident_department_other,
            resident_department_other_settle,
            rotating_department_mz,
            rotating_department_mz_settle,
            rotating_department_zr,
            rotating_department_zr_settle,
            rotating_department_st,
            rotating_department_st_settle,
            rotating_department_zf,
            rotating_department_zf_settle,
            rotating_department_ws,
            rotating_department_ws_settle,
            rotating_department_ty,
            rotating_department_ty_settle,
            rotating_department_sc,
            rotating_department_sc_settle,
            rotating_department_gh,
            rotating_department_gh_settle,
            rotating_department_gqt,
            rotating_department_gqt_settle,
            rotating_department_fl,
            rotating_department_fl_settle,
            rotating_department_fxh,
            rotating_department_fxh_settle,
            rotating_department_hy,
            rotating_department_hy_settle,
            rotating_department_other,
            rotating_department_other_settle,
            accompanying_department_ms,
            accompanying_department_ms_settle,
            accompanying_department_ld,
            accompanying_department_ld_settle,
            accompanying_department_gz,
            accompanying_department_gz_settle,
            accompanying_department_jd,
            accompanying_department_jd_settle,
            accompanying_department_xl,
            accompanying_department_xl_settle,
            accompanying_department_gy,
            accompanying_department_gy_settle,
            accompanying_department_ls,
            accompanying_department_ls_settle,
            accompanying_department_other,
            accompanying_department_other_settle
        FROM
            jl_comprehensive_governance
        WHERE
            del_flag = '0'
            <if test="city_id != null and city_id != ''">
                AND city_id = #{city_id}
            </if>
            <if test="area_id != null and area_id != ''">
                AND area_id = #{area_id}
            </if>
        ORDER BY city_id, area_id
    </select>


    <select id="getComprehensiveGovernance" parameterType="map" resultType="map">
        SELECT
            id,
            city_id,
            city_name,
            area_id,
            area_name,
            square_measure,
            construction_selection,
            functional_layout,
            institutional_staffing,
            unit_level,
            authorized_strength,
            actual_number,
            public_legal,
            reception_center,
            lawsuit,
            procuratorial,
            multicenter_other,
            multicenter_other_type,
            multicenter_other_number,
            resident_department_other_settle_number,
            rotating_department_other_settle_number,
            accompanying_department_other_settle_number,
            resident_department_fy,
            resident_department_fy_settle,
            resident_department_jcy,
            resident_department_jcy_settle,
            resident_department_gg,
            resident_department_gg_settle,
            resident_department_sa,
            resident_department_sa_settle,
            resident_department_xf,
            resident_department_xf_settle,
            resident_department_rl,
            resident_department_rl_settle,
            resident_department_rm,
            resident_department_rm_settle,
            resident_department_other,
            resident_department_other_settle,
            rotating_department_mz,
            rotating_department_mz_settle,
            rotating_department_zr,
            rotating_department_zr_settle,
            rotating_department_st,
            rotating_department_st_settle,
            rotating_department_zf,
            rotating_department_zf_settle,
            rotating_department_ws,
            rotating_department_ws_settle,
            rotating_department_ty,
            rotating_department_ty_settle,
            rotating_department_sc,
            rotating_department_sc_settle,
            rotating_department_gh,
            rotating_department_gh_settle,
            rotating_department_gqt,
            rotating_department_gqt_settle,
            rotating_department_fl,
            rotating_department_fl_settle,
            rotating_department_fxh,
            rotating_department_fxh_settle,
            rotating_department_hy,
            rotating_department_hy_settle,
            rotating_department_other,
            rotating_department_other_settle,
            accompanying_department_ms,
            accompanying_department_ms_settle,
            accompanying_department_ld,
            accompanying_department_ld_settle,
            accompanying_department_gz,
            accompanying_department_gz_settle,
            accompanying_department_jd,
            accompanying_department_jd_settle,
            accompanying_department_xl,
            accompanying_department_xl_settle,
            accompanying_department_gy,
            accompanying_department_gy_settle,
            accompanying_department_ls,
            accompanying_department_ls_settle,
            accompanying_department_other,
            accompanying_department_other_settle
        FROM
            jl_comprehensive_governance
        WHERE
            del_flag = '0'
        AND id = #{id}
    </select>

    <select id="selectComprehensiveGovernanceCountList" parameterType="map" resultType="map">
        SELECT
            jcg.city_id,
            jcg.city_name,
            jcg.area_id,
            IF(jcg.grade = '2', '市本级', jcg.area_name) area_name,
            AVG(jcg.square_measure) squareMeasure,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction01', 1, 0)),0) construction01,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction02', 1, 0)),0) construction02,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction03', 1, 0)),0) construction03,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction04', 1, 0)),0) construction04,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction05', 1, 0)),0) construction05,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction06', 1, 0)),0) construction06,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction071', 1, 0)),0) construction071,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction072', 1, 0)),0) construction072,
            IFNULL(SUM(IF( jcg.construction_selection = 'construction073', 1, 0)),0) construction073,
            IFNULL(SUM(IF( jcg.functional_layout LIKE '%functional01%', 1, 0)),0) functional01,
            IFNULL(SUM(IF( jcg.functional_layout LIKE '%functional02%', 1, 0)),0) functional02,
            IFNULL(SUM(IF( jcg.functional_layout LIKE '%functional03%', 1, 0)),0) functional03,
            IFNULL(SUM(IF( jcg.institutional_staffing = '1', 1, 0)),0) institutionalStaffingY,
            IFNULL(SUM(IF( jcg.institutional_staffing = '2', 1, 0)),0) institutionalStaffingN,
            IFNULL(SUM(IF( jcg.unit_level = 'unitLevel01', 1, 0)),0) unitLevel01,
            IFNULL(SUM(IF( jcg.unit_level = 'unitLevel02', 1, 0)),0) unitLevel02,
            IFNULL(SUM(IF( jcg.unit_level = 'unitLevel03', 1, 0)),0) unitLevel03,
            IFNULL(SUM(IF( jcg.unit_level = 'unitLevel04', 1, 0)),0) unitLevel04,
            IFNULL(SUM(IF( jcg.unit_level = 'unitLevel05', 1, 0)),0) unitLevel05,
            IFNULL(SUM(jcg.authorized_strength),0) authorizedStrength,
            IFNULL(SUM(jcg.actual_number),0) actualNumber,
            IFNULL(SUM(IF( jcg.public_legal = '1', 1, 0)),0) publicLegalZ,
            IFNULL(SUM(IF( jcg.public_legal = '2', 1, 0)),0) publicLegalP,
            IFNULL(SUM(IF( jcg.reception_center = '1', 1, 0)),0) receptionCenterZ,
            IFNULL(SUM(IF( jcg.reception_center = '2', 1, 0)),0)receptionCenterP,
            IFNULL(SUM(IF( jcg.lawsuit = '1', 1, 0)),0) lawsuitZ,
            IFNULL(SUM(IF( jcg.lawsuit = '2', 1, 0)),0) lawsuitP,
            IFNULL(SUM(IF( jcg.procuratorial = '1', 1, 0)),0) procuratorialZ,
            IFNULL(SUM(IF( jcg.procuratorial = '2', 1, 0)),0) procuratorialP,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration011' AND jcg.multicenter_other_number = '1', 1, 0)),0) registration011z,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration011' AND jcg.multicenter_other_number = '2', 1, 0)),0) registration011p,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration012' AND jcg.multicenter_other_number = '1', 1, 0)),0) registration012Z,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration012' AND jcg.multicenter_other_number = '2', 1, 0)),0) registration012P,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration013' AND jcg.multicenter_other_number = '1', 1, 0)),0) registration013Z,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration013' AND jcg.multicenter_other_number = '2', 1, 0)),0) registration013P,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration014' AND jcg.multicenter_other_number = '1', 1, 0)),0) registration014Z,
            IFNULL(SUM(IF( jcg.multicenter_other = 'registration014' AND jcg.multicenter_other_number = '2', 1, 0)),0) registration014P,
            IFNULL(SUM( jcg.resident_department_fy_settle),0) resident_department_fy_settle,
            IFNULL(SUM( jcg.resident_department_jcy_settle),0) resident_department_jcy_settle,
            IFNULL(SUM( jcg.resident_department_gg_settle),0) resident_department_gg_settle,
            IFNULL(SUM( jcg.resident_department_sa_settle),0) resident_department_sa_settle,
            IFNULL(SUM( jcg.resident_department_xf_settle),0) resident_department_xf_settle,
            IFNULL(SUM( jcg.resident_department_rl_settle),0) resident_department_rl_settle,
            IFNULL(SUM( jcg.resident_department_rm_settle),0) resident_department_rm_settle,
            IFNULL(SUM(IF( jcg.resident_department_other_settle = 'registration011', jcg.resident_department_other_settle_number, 0)),0) residentDepartmentRegistration011,
            IFNULL(SUM(IF( jcg.resident_department_other_settle = 'registration012', jcg.resident_department_other_settle_number, 0)),0) residentDepartmentRegistration012,
            IFNULL(SUM(IF( jcg.resident_department_other_settle = 'registration013', jcg.resident_department_other_settle_number, 0)),0) residentDepartmentRegistration013,
            IFNULL(SUM(IF( jcg.resident_department_other_settle = 'registration014', jcg.resident_department_other_settle_number, 0)),0) residentDepartmentRegistration014,
            IFNULL(SUM(jcg.rotating_department_mz_settle),0) rotating_department_mz_settle,
            IFNULL(SUM(jcg.rotating_department_zr_settle),0) rotating_department_zr_settle,
            IFNULL(SUM(jcg.rotating_department_st_settle),0) rotating_department_st_settle,
            IFNULL(SUM(jcg.rotating_department_zf_settle),0) rotating_department_zf_settle,
            IFNULL(SUM(jcg.rotating_department_ws_settle),0) rotating_department_ws_settle,
            IFNULL(SUM(jcg.rotating_department_ty_settle),0) rotating_department_ty_settle,
            IFNULL(SUM(jcg.rotating_department_sc_settle),0) rotating_department_sc_settle,
            IFNULL(SUM(jcg.rotating_department_gh_settle),0) rotating_department_gh_settle,
            IFNULL(SUM(jcg.rotating_department_gqt_settle),0) rotating_department_gqt_settle,
            IFNULL(SUM(jcg.rotating_department_fl_settle),0) rotating_department_fl_settle,
            IFNULL(SUM(jcg.rotating_department_fxh_settle),0) rotating_department_fxh_settle,
            IFNULL(SUM(jcg.rotating_department_hy_settle),0) rotating_department_hy_settle,
            IFNULL(SUM(IF( jcg.rotating_department_other_settle = 'registration011', jcg.rotating_department_other_settle_number, 0)),0) rotatingDepartmentRegistration011,
            IFNULL(SUM(IF( jcg.rotating_department_other_settle = 'registration012', jcg.rotating_department_other_settle_number, 0)),0) rotatingDepartmentRegistration012,
            IFNULL(SUM(IF( jcg.rotating_department_other_settle = 'registration013', jcg.rotating_department_other_settle_number, 0)),0) rotatingDepartmentRegistration013,
            IFNULL(SUM(IF( jcg.rotating_department_other_settle = 'registration014', jcg.rotating_department_other_settle_number, 0)),0) rotatingDepartmentRegistration014,
            IFNULL(SUM(jcg.accompanying_department_ms_settle),0) accompanying_department_ms_settle,
            IFNULL(SUM(jcg.accompanying_department_ld_settle),0) accompanying_department_ld_settle,
            IFNULL(SUM(jcg.accompanying_department_gz_settle),0) accompanying_department_gz_settle,
            IFNULL(SUM(jcg.accompanying_department_jd_settle),0) accompanying_department_jd_settle,
            IFNULL(SUM(jcg.accompanying_department_xl_settle),0) accompanying_department_xl_settle,
            IFNULL(SUM(jcg.accompanying_department_gy_settle),0) accompanying_department_gy_settle,
            IFNULL(SUM(jcg.accompanying_department_ls_settle),0) accompanying_department_ls_settle,
            IFNULL(SUM(IF( jcg.accompanying_department_other_settle = 'registration011', jcg.accompanying_department_other_settle_number, 0)),0) accompanyingDepartmentRegistration011,
            IFNULL(SUM(IF( jcg.accompanying_department_other_settle = 'registration012', jcg.accompanying_department_other_settle_number, 0)),0) accompanyingDepartmentRegistration012,
            IFNULL(SUM(IF( jcg.accompanying_department_other_settle = 'registration013', jcg.accompanying_department_other_settle_number, 0)),0) accompanyingDepartmentRegistration013,
            IFNULL(SUM(IF( jcg.accompanying_department_other_settle = 'registration014', jcg.accompanying_department_other_settle_number, 0)),0) accompanyingDepartmentRegistration014
        FROM
            jl_comprehensive_governance jcg
        <if test="area_jurisdiction != null and area_jurisdiction != ''">
            INNER JOIN sys_area_jurisdiction saj ON jcg.area_id = saj.id
        </if>
        WHERE
            jcg.del_flag = '0'
        <if test="city_id == null or city_id == ''">
            <if test="_user.company_grade == '3'.toString()">
                AND jcg.city_id = #{_user.company_id}
            </if>
            GROUP BY jcg.city_id
        </if>
        <if test="city_id != null and city_id != ''">
            AND jcg.city_id = #{city_id}
            GROUP BY jcg.area_id
        </if>
    </select>
</mapper>