<?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.gridModule" name="网格模块">
    <select id="findGridModuleList" parameterType="map" resultType="map" name="查询模块" desc="查询模块" expose="true">
        SELECT
        id,
        `name`,
        identification,
        create_by,
        create_time,
        del_flag,
        lable_id,
        two_lable_id,
        img1,
        img2,
        img3,
        module_bg,
        pid,
        type_level,
        sort,
        is_click
        FROM
        jl_grid_module
        WHERE
        del_flag = '0'
    </select>
    <select id="findModulePeopleList" parameterType="map" resultType="map">
        SELECT
        a.doorplate_id
        FROM
        jl_grid_user a
        WHERE
        a.city_id=#{city_id} and
        a.unit_id = #{id}
        AND a.del_flag = '0'
        AND EXISTS (
        SELECT cardno
        FROM jl_grid_user_focusgroup
        WHERE cardno = a.cardno
        AND two_lable_id = #{second_id})
    </select>
    <select id="findModulePeopleForYQList" parameterType="map" resultType="map">
        SELECT
        a.doorplate_id
        FROM
        jl_grid_user a
        WHERE
        a.unit_id = #{id}
        AND a.del_flag = '0'
        AND EXISTS (
        SELECT cardno
        FROM jl_grid_user_focusgroup_nucleic_acid
        WHERE cardno = a.cardno)
    </select>
    <select id="findModuleUserList" parameterType="map" resultType="map" name="查询列表" desc="查询列表">
        SELECT
        a.id,
        a.user_type,
        a.huji_type,
        a.is_kgh,
        a.user_name,
        a.juzhu_type,
        a.cardno,
        a.community_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.doorplate_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.relation,
        a.tel mobile,
        case a.sex when '1' then '男' when '2' then '女' else '' end sex,
        unit.name unit_name,
        floor.name floor_name,
        doorplate.name doorplate_name
        FROM
        jl_grid_user a
        left join jl_grid_unit unit on unit.id = a.unit_id
        left join jl_grid_floor floor on floor.id = a.floor_id
        left join jl_grid_doorplate doorplate on doorplate.id = a.doorplate_id
        <where>
        	a.city_id=#{city_id}
        	<choose>
	            <when test="unit_id != null and unit_id != ''">
	                AND a.unit_id = #{unit_id}
	            </when>
	            <otherwise>
	                AND a.building_id = #{building_id}
	            </otherwise>
	        </choose>
        </where>
        ORDER BY
        CAST( floor_name AS UNSIGNED INTEGER ),
        CAST( doorplate_name AS UNSIGNED INTEGER )
    </select>
    <select id="findDoorplatePeopleList" parameterType="map" resultType="map">
        SELECT a.id,
        a.user_type,
        a.huji_type,
        a.is_kgh,
        a.relation,
        a.juzhu_type,
        a.living_condition,
        a.user_name,
        a.cardno,
        a.card_front_url,
        a.card_back_url,
        a.tel,
        a.folk,
        a.sex,
        a.political_outlook,
        a.avatar_url,
        a.city_id,
        a.area_id,
        a.street_id,
        a.community_id,
        a.community_code,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.doorplate_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.is_zdry,
        a.hj_address,
        a.now_address,
        a.remarks
        FROM
        jl_grid_user a
        WHERE
        a.del_flag = '0'
        and city_id=#{city_id}
        AND doorplate_id = #{doorplate_id}
    </select>
    <select id="findModuleDoorplatePeopleList" parameterType="map" resultType="map">
        SELECT
        a.id,
        a.user_type,
        a.huji_type,
        a.is_kgh,
        a.user_name,
        a.juzhu_type,
        cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
        a.community_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.doorplate_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.relation
        FROM
        jl_grid_user a
        WHERE
        a.del_flag = '0'
        AND doorplate_id = #{doorplate_id}
        AND EXISTS (
        SELECT b.cardno
        FROM jl_grid_user_focusgroup b
        WHERE a.cardno = b.cardno
        AND two_lable_id = #{two_lable_id} )
    </select>
    <select id="findModuleDoorplatePeopleYQList" parameterType="map" resultType="map">
        SELECT
        a.id,
        a.user_type,
        a.huji_type,
        a.is_kgh,
        a.user_name,
        a.juzhu_type,
        cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
        a.community_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.doorplate_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.relation
        FROM
        jl_grid_user a
        WHERE
        a.del_flag = '0'
        AND doorplate_id = #{doorplate_id}
        AND EXISTS (
        SELECT b.cardno
        FROM jl_grid_user_focusgroup_nucleic_acid b
        WHERE a.cardno = b.cardno )
    </select>
    <select id="findModuleList" parameterType="map" resultType="map">
        SELECT
        id,
        name as title,
        name,
        identification,
        create_by,
        create_time,
        del_flag,
        lable_id,
        two_lable_id,
        img1,
        img2,
        img3,
        module_bg,
        pid,
        type_level,
        sort,
        is_click
        FROM
        jl_grid_module
        WHERE
        del_flag = '0'
        ORDER BY
        type_level,
        sort
    </select>
    <insert id="saveModule" parameterType="map" expose="true" name="保存模块" desc="保存模块">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            select count(1) from jl_grid_module where id = #{id}
        </selectKey>
        <if test="count > 0">
            update jl_grid_module
            set name = #{name},
            identification = #{identification},
            lable_id = #{lable_id},
            two_lable_id = #{two_lable_id},
            img1 = #{img1},
            sort = #{sort},
            is_click = #{is_click}
            where id = #{id}
        </if>
        <if test="count == 0">
            insert into jl_grid_module(
            id,
            name,
            identification,
            create_by,
            create_time,
            lable_id,
            two_lable_id,
            img1,
            pid,
            type_level,
            sort,
            is_click
            )values(
            uuid(),
            #{name},
            #{identification},
            'sys',
            now(),
            #{lable_id},
            #{two_lable_id},
            #{img1},
            #{pid},
            #{type_level},
            #{sort},
            #{is_click}
            )
        </if>
    </insert>
    <select id="getFocusGroupUserNum" parameterType="map" resultType="map">
        SELECT
        count( DISTINCT a.cardno ) as num
        FROM
        jl_grid_user a
        WHERE
        a.del_flag = '0'
        AND a.building_id = #{building_id}
        AND EXISTS (
        SELECT cardno
        FROM jl_grid_user_focusgroup
        WHERE a.cardno = cardno
        AND lable_id = #{lable_id}
        AND two_lable_id = #{two_lable_id} )
    </select>

    <select id="findGridTypeList" parameterType="map" resultType="map">
        SELECT
        id,
        type,
        name,
        icon,
        create_by,
        create_time,
        del_flag,
        pid
        FROM
        jl_grid_place
        WHERE
        del_flag = '0'
    </select>

    <select id="findGridTypeInfo" parameterType="map" resultType="map">
        SELECT
        COUNT(1) + 1 as count
        FROM
        jl_grid_place
        WHERE
        pid = #{pid}
    </select>

    <insert id="addGridType" parameterType="map">
        INSERT INTO jl_grid_place (
        id,
        type,
        name,
        icon,
        create_by,
        create_time,
        pid )
        values (
        #{id},
        #{type},
        #{name},
        #{icon},
        #{_user.loginName},
        now(),
        #{pid}
        )
    </insert>

    <update id="updateGridType" parameterType="map">
        UPDATE jl_grid_place
        SET
        name = #{name},
        icon = #{icon}
        WHERE
        id = #{id}
    </update>
    <select id="getCityIdFromUnit" parameterType="string" resultType="map">
    	select city_id from jl_grid_Unit where id=#{id}
    </select>
    <select id="getCityIdFromUnitOrBuildingOrDoorplate" parameterType="map" resultType="map">
    	<choose>
    		<when test="doorplate_id != null and doorplate_id != ''">
                select city_id from jl_grid_doorplate where id = #{doorplate_id}
            </when>
            <when test="unit_id != null and unit_id != ''">
                select city_id from jl_grid_Unit where id = #{unit_id}
            </when>
            <otherwise>
                select city_id from jl_grid_building where id = #{building_id}
            </otherwise>
            
        </choose>
    </select>
    <select id="findDoorplatePeopleNumList" parameterType="map" resultType="map">
        SELECT
        doorplate_id,
        COUNT( 1 ) as num
        FROM
        jl_grid_user
        WHERE
        city_id= #{city_id} and
        unit_id = #{id}
        GROUP BY
        doorplate_id
    </select>
    <select id="findDoorplatePeopleLabelList" parameterType="map" resultType="map">
        SELECT
        a.id,
        a.user_type,
        a.huji_type,
        a.is_kgh,
        a.user_name,
        a.juzhu_type,
        a.cardno,
        a.community_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.doorplate_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.relation,
        b.lable_id,
        b.two_lable_id,
        c.label_img
        FROM
        jl_grid_user a
        LEFT JOIN jl_grid_user_focusgroup b ON a.cardno = b.cardno
        LEFT JOIN jl_grid_focusgroup_type c ON b.two_lable_id = c.id
        WHERE
        a.city_id=#{city_id} and
        a.doorplate_id = #{doorplate_id}
    </select>
    <select id="findMapInterviewLogList" parameterType="map" resultType="map">
        SELECT
        doorplate_id
        FROM
        jl_grid_interview a
        LEFT JOIN jl_grid_doorplate b ON a.doorplate_id = b.id
        WHERE
        DATE ( a.zf_time ) BETWEEN DATE_SUB( CURDATE(), INTERVAL 3 MONTH ) AND CURDATE()
        AND a.community_id = #{_user.company_id}
        AND b.unit_id = #{id}
        GROUP BY
        a.doorplate_id
    </select>
</mapper>