<?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.template.mapper.AlumniClubApplyMapper">
    <resultMap type="com.template.model.vo.ApplyClubVo" id="ClubDataApplyMap">
        <result property="id" column="id"/>
        <result property="applyName" column="apply_name"/>
        <result property="collegeName" column="college_name"/>
        <result property="periodName" column="period_name"/>
        <result property="majorName" column="major_name"/>
        <result property="className" column="class_name"/>
        <result property="name" column="name"/>
        <result property="applyUserName" column="apply_user_name"/>
        <result property="isPass" column="is_pass"/>
        <result property="passTime" column="pass_time"/>
        <result property="createTime" column="create_time"/>
        <result property="passValue" column="pass_value"/>
    </resultMap>
    <select id="queryClubApplys" resultType="com.template.model.vo.ApplyClubVo" resultMap="ClubDataApplyMap">
        select aca.id,au.name as apply_name,au.college_name,aca.apply_user_name,au.period_name,au.major_name,au.class_name,aca.name,aca.is_pass,aca.update_time as pass_time,aca.create_time,aca.pass_value
        from alumni_club_apply aca
        inner join alumni_user au on au.deleted = 0 and au.id = aca.create_user
        <if test="userName != null and userName != ''">
            and au.name like '%' #{userName} '%'
        </if>
        <if test="collegeId != null and collegeId != 0">
            and au.college_id = #{collegeId}
        </if>
        <if test="periodId != null and periodId != 0">
            and au.period_id = #{periodId}
        </if>
        <if test="majorId != null and majorId != 0">
            and au.major_id = #{majorId}
        </if>
        <if test="classId != null and classId != 0">
            and au.class_id = #{classId}
        </if>
        where aca.deleted= 0
        <if test="name != null and name != ''">
            and aca.name like '%' #{name} '%'
        </if>
        <if test="isPass != null and isPass != 0">
            and aca.is_pass = #{isPass}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and aca.create_time BETWEEN #{startTime} AND #{endTime}
        </if>
        <if test="userId != null and userId != 0">
            and FIND_IN_SET(${userId},aca.apply_user)
        </if>
    </select>

    <select id="queryClubExcelApplys" resultType="com.template.model.vo.ApplyClubVo" resultMap="ClubDataApplyMap">
        select aca.id,au.name as apply_name,au.college_name,aca.apply_user_name,au.period_name,au.major_name,au.class_name,aca.name,aca.is_pass,aca.update_time as pass_time,aca.create_time,aca.pass_value
        from alumni_club_apply aca
        inner join alumni_user au on au.deleted = 0 and au.id = aca.create_user
        <if test="userName != null and userName != ''">
            and au.name like '%' #{userName} '%'
        </if>
        <if test="collegeId != null and collegeId != 0">
            and au.college_id = #{collegeId}
        </if>
        <if test="periodId != null and periodId != 0">
            and au.period_id = #{periodId}
        </if>
        <if test="majorId != null and majorId != 0">
            and au.major_id = #{majorId}
        </if>
        <if test="classId != null and classId != 0">
            and au.class_id = #{classId}
        </if>
        where aca.deleted= 0
        <if test="name != null and name != ''">
            and aca.name like '%' #{name} '%'
        </if>
        <if test="isPass != null and isPass != 0">
            and aca.is_pass = #{isPass}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and aca.create_time BETWEEN #{startTime} AND #{endTime}
        </if>
        <if test="userId != null and userId != 0">
            and FIND_IN_SET(${userId},aca.apply_user)
        </if>
    </select>


    <select id="queryMyClubApplys" resultType="com.template.model.vo.ApplyClubVo" resultMap="ClubDataApplyMap">
        select aca.club_id as id,au.name as apply_name,au.college_name,au.period_name,au.major_name,au.class_name,aca.name,aca.is_pass,aca.update_time as pass_time,aca.create_time,aca.pass_value
        from alumni_club_apply aca
        inner join alumni_user au on au.deleted = 0 and au.id = aca.create_user
        where aca.deleted= 0
        <if test="name != null and name != ''">
            and aca.name like '%' #{name} '%'
        </if>
        <if test="userId != null and userId != 0">
            and aca.create_user = #{userId}
        </if>
    </select>


    <select id="queryApproveClubApplys" resultType="com.template.model.vo.ApplyClubVo" resultMap="ClubDataApplyMap">
        select aca.id,au.name as apply_name,au.college_name,au.period_name,au.major_name,au.class_name,aca.name,aca.is_pass,aca.update_time as pass_time,aca.create_time,aca.pass_value
        from alumni_club_apply aca
        inner join alumni_user au on au.deleted = 0 and au.id = aca.create_user
        where aca.deleted= 0 and aca.is_pass = 1
        <if test="name != null and name != ''">
            and aca.name like '%' #{name} '%'
        </if>
        <if test="userId != null and userId != 0">
            and FIND_IN_SET(${userId},aca.apply_user)
        </if>
        order by aca.create_time desc
    </select>
</mapper>
