<?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="cn.com.countrygarden.bi.main.dao.ck.CkCleanerFieldConfigMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.dto.ck.CkCleanerFieldConfigVo">
        <id column="id" property="id"/>
        <result column="field_code" property="fieldCode"/>
        <result column="field_name" property="fieldName"/>
        <result column="parent_id" property="parentId"/>
        <result column="path" property="path"/>
        <result column="type" property="type"/>
        <result column="field_length" property="fieldLength"/>
        <result column="show_width" property="showWidth"/>
        <result column="query_flag" property="queryFlag"/>
        <result column="required_flag" property="requiredFlag"/>
        <result column="inherit_flag" property="inheritFlag"/>
        <result column="value_type" property="valueType"/>
        <result column="soft" property="soft"/>
        <result column="status" property="status"/>
        <result column="is_write" property="isWrite"/>
        <result column="max" property="max"/>
        <result column="min" property="min"/>
        <result column="create_time" property="createTime"/>

    </resultMap>
    <select id="selectListData" resultMap="BaseResultMap">
        SELECT distinct
        cfc.id,
        cfc.field_code,
        cfc.field_name,
        cfc.parent_id,
        cfc.path,
        cfc.type,
        cfc.field_length,
        cfc.query_flag,
        cfc.required_flag,
        cfc.inherit_flag,
        cfc.value_type,
        cfc.soft,
        cfc. STATUS,
        cfc.show_width,
        cfc.max,
        cfc.min,
        (
        SELECT
        MAX(r.is_write)
        FROM
        ck_cleaner_fileid_role r
        WHERE r.status=1 and
        r.field_code = cfr.field_code
        <choose>
            <when test="query.rolesList!=null and query.rolesList.size()>0">
                <foreach collection="query.rolesList" item="roleCode" open="and  r.role_code in (" close=")" separator=",">
                    #{roleCode}
                </foreach>
            </when>
            <otherwise>
               and 1=2
            </otherwise>
        </choose>

        ) is_write
        FROM
        ck_cleaner_field_config cfc
        LEFT JOIN ck_cleaner_fileid_role cfr ON cfr.field_code = cfc.field_code
        <where>
        1=1
            <choose>
                <when test="query!=null and query.status!=null">
                  and  cfc.status  = #{query.status}
                </when>
                <otherwise>
                    and  cfc.status <![CDATA[<>]]> 2
                </otherwise>
            </choose>
            <if test="query!=null">

                <if test="query.id!=null">
                    and cfc.id  <![CDATA[<>]]> #{query.id}
                </if>
                <if test="query.type!=null">
                    and cfc.type=#{query.type}
                </if>
                <if test="query.parentId!=null">
                    and cfc.parent_id=#{query.parentId}
                </if>
                <if test="query.fieldCode!=null and query.fieldCode!=''">
                    and cfc.field_code  LIKE CONCAT("%",#{query.fieldCode},"%")
                </if>
                <if test="query.fieldName!=null and query.fieldName!=''">
                    and cfc.field_name  LIKE CONCAT("%",#{query.fieldName},"%")
                </if>
                <if test="query.rolesList!=null and query.rolesList.size()>0">
                    AND (
                    cfc.type = 0 or (cfc.type=1 and cfr.status=1
                    <foreach collection="query.rolesList" item="roleCode" open="and  cfr.role_code in (" close=")))" separator=",">
                        #{roleCode}
                    </foreach>
                </if>
            </if>
        </where>
        ORDER BY soft,id desc
    </select>

    <select id="selectListaddData" resultMap="BaseResultMap">
        SELECT distinct
        cfc.id,
        cfc.field_code,
        cfc.field_name,
        cfc.parent_id,
        cfc.path,
        cfc.type,
        cfc.field_length,
        cfc.query_flag,
        cfc.required_flag,
        cfc.inherit_flag,
        cfc.value_type,
        cfc.soft,
        cfc. STATUS,
        cfc.show_width,
        cfc.max,
        cfc.min,
        cfc.create_time,
        (
        SELECT
        MAX(r.is_write)
        FROM
        ck_cleaner_fileid_role r
        WHERE r.status=1 and
        r.field_code = cfr.field_code
        <choose>
            <when test="query.rolesList!=null and query.rolesList.size()>0">
                <foreach collection="query.rolesList" item="roleCode" open="and  r.role_code in (" close=")" separator=",">
                    #{roleCode}
                </foreach>
            </when>
            <otherwise>
                and 1=2
            </otherwise>
        </choose>

        ) is_write
        FROM
        ck_cleaner_field_config cfc
        LEFT JOIN ck_cleaner_fileid_role cfr ON cfr.field_code = cfc.field_code
        <where>
            1=1
            <choose>
                <when test="query!=null and query.status!=null">
                    and  cfc.status  = #{query.status}
                </when>
                <otherwise>
                    and  cfc.status <![CDATA[<>]]> 2
                </otherwise>
            </choose>
            <if test="query!=null">

                <if test="query.id!=null">
                    and cfc.id  <![CDATA[<>]]> #{query.id}
                </if>
                <if test="query.type!=null">
                    and cfc.type=#{query.type}
                </if>
                <if test="query.parentId!=null">
                    and cfc.parent_id=#{query.parentId}
                </if>
                <if test="query.fieldCode!=null and query.fieldCode!=''">
                    and cfc.field_code  LIKE CONCAT("%",#{query.fieldCode},"%")
                </if>
                <if test="query.fieldName!=null and query.fieldName!=''">
                    and cfc.field_name  LIKE CONCAT("%",#{query.fieldName},"%")
                </if>
                <if test="query.rolesList!=null and query.rolesList.size()>0">
                    AND (
                    cfc.type = 0 or (cfc.type=1 and cfr.status=1
                    <foreach collection="query.rolesList" item="roleCode" open="and  cfr.role_code in (" close=")))" separator=",">
                        #{roleCode}
                    </foreach>
                </if>
            </if>
            AND cfc.field_name NOT IN ('月度运营数据','便民服务', '收费管理面积（单位：㎡） （不含地库）','外拓','实测作业面积 （单位：㎡）',
            '水景水系作业面积','垃圾清运','保洁设备配置（品牌与型号类型）', '绿化机械设备','人员配置（本月在职人数）（单位：人）',
            '服务类','服务亮点/成果（微创新平台整合）','社团类','停车收费开展情况','监控基础数据','保安员工单位备案情况','安防在职信息','资质证书',
            '岗位基础数据','好人好事人员奖励情况','门岗基础数据','消防监控中心基础数据')
        </where>
        ORDER BY  cfc.create_time asc
    </select>

    <select id="selectFieldConfigTree" resultType="cn.com.countrygarden.bi.main.dto.ck.CkFileidRoleListVo">
        SELECT distinct
        cfc.id id,
        cfc.field_code fieldCode,
        cfc.field_name fieldName,
        cfc.parent_id parentId,
        cfc.type type,
        cfc.soft soft
        FROM
        ck_cleaner_field_config cfc
        <where>
              cfc.status = 1
            <if test="query!=null">

                <if test="query.id!=null">
                    and cfc.id  <![CDATA[<>]]> #{query.id}
                </if>
                <if test="query.type!=null">
                    and cfc.type=#{query.type}
                </if>
                <if test="query.parentId!=null">
                    and cfc.parent_id=#{query.parentId}
                </if>
            </if>
        </where>
        ORDER BY cfc.soft,cfc.id desc
    </select>

    <select id="selectFieldConfigTreedropDown" resultType="cn.com.countrygarden.bi.main.dto.ck.CkFileidRoleListVo">
        SELECT distinct
        cfc.id id,
        cfc.field_code fieldCode,
        cfc.field_name fieldName,
        cfc.parent_id parentId,
        cfc.type type,
        cfc.soft
        FROM
        ck_cleaner_field_config cfc
        LEFT JOIN ck_cleaner_fileid_role cr ON cfc.field_code=cr.field_code
        <where>
            cfc.status = 1 AND cr.status = 1
            <if test="query!=null">

                <if test="query.id!=null">
                    and cfc.id  <![CDATA[<>]]> #{query.id}
                </if>
                <if test="query.type!=null">
                    and cfc.type=#{query.type}
                </if>
                <if test="query.parentId!=null">
                    and cfc.parent_id=#{query.parentId}
                </if>
                <!--<if test="query.fieldCode!=null">-->
                    <!--and cr.field_code=#{query.fieldCode}-->
                <!--</if>-->
            </if>
            <if test="role!=null and role!=''">
                AND cr.role_code=#{role}
            </if>
        </where>
        ORDER BY cfc.soft,cfc.id desc
    </select>

    <select id="selectPageList" resultType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerFieldConfigListVo">
       SELECT
	cfc.id,
	cfc.field_code fieldCode,
	cfc.field_name fieldName,
	cfc.parent_id parentId,
	cfc.path,
	cfc.type,
	cfc.field_length fieldLength,
	cfc.show_width showWidth,
	cfc.query_flag queryFlag,
	cfc.required_flag requiredFlag,
	cfc.inherit_flag inheritFlag,
	cfc.value_type valueType,
	cfc.soft,
	cfc.create_time createTime,
  (SELECT COUNT(DISTINCT c.id) FROM ck_cleaner_field_config c WHERE  c.status=1 and  c.parent_id=cfc.id) childrenCount
FROM
	ck_cleaner_field_config cfc
	<where>
        cfc.status <![CDATA[<>]]>2
        <if test="null!=query.fieldCode and ''!=query.fieldCode">
            and cfc.field_code LIKE CONCAT("%",query.fieldCode,"%")
        </if>
        <if test="null!=query.fieldName and ''!=query.fieldName">
            and cfc.field_name LIKE CONCAT("%",query.fieldName,"%")
        </if>
    </where>
        ORDER BY cfc.id desc
    </select>

</mapper>