<?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="com.dgtis.dorm.manager.db.dao.AlumniOrganizeCustomMapper">

	<!-- <result column="cover_image" jdbcType="INTEGER" property="coverImage" /> -->
    <resultMap id="BaseResultMap" type="com.dgtis.dorm.manager.db.domain.AlumniOrganizeCustom">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="organize_name" jdbcType="VARCHAR" property="organizeName" />
        <result column="organize_type" jdbcType="VARCHAR" property="organizeType" />
        <result column="organize_content" jdbcType="VARCHAR" property="organizeContent" />
        <result column="organize_user_id" jdbcType="INTEGER" property="organizeUserId" />
        <result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
        <result column="is_delete" jdbcType="BIT" property="isDelete" />
        <result column="organize_memo" jdbcType="VARCHAR" property="organizeMemo" />
        <result column="auth_user_id" jdbcType="INTEGER" property="authUserId" />
        <result column="auth_status" jdbcType="INTEGER" property="authStatus" />
        <result column="auth_date" jdbcType="TIMESTAMP" property="authDate" />
        <result column="modify_user_id" jdbcType="INTEGER" property="modifyUserId" />
        <result column="modify_date" jdbcType="TIMESTAMP" property="modifyDate" />
        <result column="url" jdbcType="VARCHAR" property="url" />
        <result column="weight" jdbcType="INTEGER" property="weight" />
    </resultMap>

    <resultMap id="CustomResultMap" type="com.dgtis.dorm.manager.db.domain.AlumniOrganizeCustom" extends="BaseResultMap">
        <result column="user_name" jdbcType="VARCHAR" property="userName" />
        <result column="auth_user_name" jdbcType="VARCHAR" property="authUserName" />
        <result column="organize_user_count" jdbcType="VARCHAR" property="organizeUserCount" />
        <result column="pri_type" jdbcType="VARCHAR" property="priType" />
        <result column="owner_user_name" jdbcType="VARCHAR" property="ownerUserName" />
        <result column="bg_auth_user_name" jdbcType="VARCHAR" property="bgAuthUserName" />
        <result column="is_forbidden" jdbcType="BIT" property="isForbidden" />
        <result column="is_normal" jdbcType="VARCHAR" property="isNormal" />
    </resultMap>

    <sql id="Base_Column_List">
        id, organize_name, organize_type,organize_content, organize_user_id, create_date, is_delete, organize_memo,
    auth_user_id, auth_date, modify_user_id, modify_date, url,weight,auth_status
    </sql>

    <sql id="Custom_Date_Format_List">
        id,
        organize_name,
        organize_type,
        organize_content,
        organize_user_id,
        date_format(create_date,'%Y-%m-%d %H:%i:%S') AS create_date,
        is_delete,
        organize_memo,
        auth_user_id,
        date_format(auth_date,'%Y-%m-%d %H:%i:%S') AS auth_date,
        modify_user_id,
        date_format(modify_date,'%Y-%m-%d %H:%i:%S') AS modify_date,
        url,
        weight,
        auth_status
    </sql>

    <!-- 根据type查询组织 -->
    <select id="selectOrganizeByType" parameterType="java.lang.String" resultMap="CustomResultMap">
        SELECT
          o.id,
          o.organize_name,
          o.organize_type,
          o.organize_content,
          o.organize_user_id,
          o.create_date,
          o.is_delete,
          o.organize_memo,
          o.auth_user_id,
          o.auth_date,
          o.modify_user_id,
          o.modify_date,
          o.url,
          o.auth_status,
          u.user_name
        FROM zhll_organize o
          LEFT JOIN zhll_user u ON o.organize_user_id = u.id
        WHERE 1 = 1 and o.is_remove = 0 AND organize_type =  #{type,jdbcType=VARCHAR}
    </select>

    <select id="selectOrganizeBySelective" parameterType="com.dgtis.dorm.manager.db.domain.AlumniOrganizeCustom" resultMap="CustomResultMap">
        SELECT
            o.id,
            o.organize_name,
            o.organize_type,
            o.organize_content,
            o.organize_user_id,
            o.create_date,
            o.is_delete,
            o.organize_memo,
            o.auth_user_id,
            o.auth_date,
            o.modify_user_id,
            o.modify_date,
            o.url,
            o.weight,
            o.auth_status,
            ad.nickname user_name, /* 创建人*/
            au.user_name AS auth_user_name,/* 前台审核人*/
            ad.username AS bg_auth_user_name, /* 后台审核人 */
            ou.fk_user_id, /* 群主 user id*/
            ouu.user_name AS owner_user_name, /* 群主 */
        (SELECT count(1) from zhll_organize_user WHERE is_normal = '1' AND is_delete = '1' AND fk_organize_id = o.id) AS organize_user_count
        FROM zhll_organize o
          LEFT JOIN zhll_user u ON o.organize_user_id = u.id
          LEFT JOIN zhll_user au ON o.auth_user_id = au.id
          LEFT JOIN (SELECT * FROM zhll_organize_user WHERE pri_type = '1') ou ON ou.fk_organize_id = o.id
          LEFT JOIN zhll_user ouu ON ouu.id = ou.`fk_user_id`
          LEFT JOIN zhll_sys_admin ad ON ad.id = o.auth_user_id
        WHERE 1 = 1
        and o.is_remove = 0
              <if test="id != null">
                  AND o.id = #{id,jdbcType=INTEGER}
              </if>
              <if test="organizeName !=  '' and organizeName != null">
                  AND o.organize_name like concat('%',#{organizeName,jdbcType=VARCHAR}, '%')
              </if>
              <if test="organizeType != '' and organizeType != null">
                  AND o.organize_type = #{organizeType,jdbcType=VARCHAR}
              </if>
              <if test="organizeUserId != null">
                  AND o.organize_user_id = #{organizeUserId,jdbcType=INTEGER}
              </if>
              <if test="isDelete != null">
                  AND o.is_delete = #{isDelete,jdbcType=BIT}
              </if>
        order by o.weight DESC,o.create_date DESC
    </select>

    <select id="selectOrganizeByUserId" parameterType="com.dgtis.dorm.manager.db.domain.AlumniOrganizeCustom" resultMap="CustomResultMap">
        SELECT
        o.id,
        o.organize_name,
        o.organize_type,
        o.organize_content,
        o.organize_user_id,
        o.create_date,
        o.is_delete,
        o.organize_memo,
        o.auth_user_id,
        o.auth_date,
        o.modify_user_id,
        o.modify_date,
        o.url,
        o.weight,
        o.auth_status,
        u.user_name, /* 创建人*/
        au.user_name AS auth_user_name,/* 前台审核人*/
        ad.username AS bg_auth_user_name, /* 后台审核人 */
        ou.fk_user_id, /* 群主 user id*/
        ouu.user_name AS owner_user_name, /* 群主 */
        (SELECT count(1) from zhll_organize_user WHERE is_normal = '1' AND is_delete = '1' AND fk_organize_id = o.id) AS organize_user_count
        <if test="userId != null and userId != ''">
            ,
        (SELECT is_normal from zhll_organize_user WHERE is_delete = '1' AND fk_organize_id = o.id AND fk_user_id = #{userId}) AS is_normal
        </if>
        FROM zhll_organize o
        LEFT JOIN zhll_user u ON o.organize_user_id = u.id
        LEFT JOIN zhll_user au ON o.auth_user_id = au.id
        LEFT JOIN (SELECT * FROM zhll_organize_user WHERE pri_type = '1') ou ON ou.fk_organize_id = o.id
        LEFT JOIN zhll_user ouu ON ouu.id = ou.`fk_user_id`
        LEFT JOIN zhll_sys_admin ad ON ad.id = o.auth_user_id
        WHERE 1 = 1
        and o.is_remove = 0
        <if test="id != null">
            AND o.id = #{id,jdbcType=INTEGER}
        </if>
        <if test="organizeName !=  '' and organizeName != null">
            AND o.organize_name like concat('%',#{organizeName,jdbcType=VARCHAR}, '%')
        </if>
        <if test="organizeType != '' and organizeType != null">
            AND o.organize_type = #{organizeType,jdbcType=VARCHAR}
        </if>
        <if test="organizeUserId != null">
            AND o.organize_user_id = #{organizeUserId,jdbcType=INTEGER}
        </if>
        <if test="isDelete != null">
            AND o.is_delete = #{isDelete,jdbcType=BIT}
        </if>
            AND o.auth_status != 2
        order by o.weight DESC,o.create_date DESC
    </select>

    <!-- 根据userid和orgtype查询我的组织 -->
    <select id="selectMyOrganizeByUserIdAndOrgType" parameterType="java.lang.Object" resultMap="CustomResultMap">
        SELECT
          o.id,
          o.organize_name,
          o.organize_type,
          o.organize_content,
          o.organize_user_id,
          o.create_date,
          o.is_delete,
          o.organize_memo,
          o.auth_user_id,
          o.auth_date,
          o.modify_user_id,
          o.modify_date,
          o.url,
          o.auth_status,
          ou.pri_type,
          ou.is_forbidden,
          (SELECT count(1) from zhll_organize_user WHERE is_normal = '1' AND is_delete = '1' AND fk_organize_id = o.id) AS organize_user_count
        FROM zhll_organize_user ou
          LEFT JOIN (SELECT *
                     FROM zhll_organize
                     WHERE 1 = 1) o ON ou.fk_organize_id = o.id
        WHERE 1 = 1
              AND ou.is_normal = 1
              AND ou.is_delete = 1
              AND o.is_delete = 1
              AND o.is_remove = 0
              AND ou.fk_user_id = #{userId,jdbcType=INTEGER}
              AND o.organize_type = #{organizeType,jdbcType=VARCHAR}
    </select>
    
    <!-- 根据userid查询我的组织 -->
    <select id="selectMyOrganizeByUserId" parameterType="java.lang.Object" resultMap="CustomResultMap">
        SELECT
          o.id,
          o.organize_name,
          o.organize_type,
          o.organize_content,
          o.organize_user_id,
          o.create_date,
          o.is_delete,
          o.organize_memo,
          o.auth_user_id,
          o.auth_date,
          o.modify_user_id,
          o.modify_date,
          o.url,
          o.auth_status,
          ou.pri_type,
          ou.is_forbidden,
          (SELECT count(1) from zhll_organize_user WHERE is_normal = '1' AND is_delete = '1' AND fk_organize_id = o.id) AS organize_user_count
        FROM zhll_organize_user ou
          LEFT JOIN (SELECT *
                     FROM zhll_organize
                     WHERE 1 = 1) o ON ou.fk_organize_id = o.id
        WHERE 1 = 1
              AND ou.is_normal = 1
              AND ou.is_delete = 1
              AND o.is_delete = 1
              AND o.is_remove = 0
              AND ou.fk_user_id = #{userId,jdbcType=INTEGER}
    </select>
    <!--删除圈子-->
    <select id="deleteOrgById" parameterType="java.lang.Integer">
        delete from zhll_organize  where id = #{orgId,jdbcType=INTEGER}
    </select>

    <select id="selectById" resultMap="CustomResultMap">

      select
      <include refid="Base_Column_List"/>
      from zhll_organize
      where  id=#{id}

    </select>
</mapper>