<?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.AlumniActivityMapper">
    <resultMap type="com.template.model.vo.AlumniActivityPageVo" id="AlumniActivityPageMap">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="poster" column="poster"/>
        <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="orgName" column="org_name"/>
        <result property="theme" column="theme"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="signsTime" column="signs_time"/>
        <result property="signeTime" column="signe_time"/>
        <result property="createTime" column="create_time"/>
        <result property="reportNumber" column="report_number"/>
        <result property="signinNumber" column="signin_number"/>
    </resultMap>
    <select id="queryActivityPages" resultType="com.template.model.vo.AlumniActivityPageVo" resultMap="AlumniActivityPageMap">
        select aa.id,aa.poster,au.name,au.college_name,au.period_name,au.major_name,au.class_name,aa.org_name,aa.theme,aa.start_time,aa.end_time,aa.signs_time,aa.signe_time,aa.create_time,
        (select count(*) from activity_report where deleted = 0 and activity_id = aa.id) as report_number,
        (select count(*) from activity_signin where deleted = 0 and activity_id = aa.id) as signin_number
        from alumni_activity aa
        inner join alumni_user au on au.deleted = 0 and au.id = aa.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 aa.deleted = 0 and aa.is_pass = 2
        <if test="orgName != null and orgName != ''">
            and aa.org_name like '%' #{orgName} '%'
        </if>
        <if test="theme != null and theme != ''">
            and aa.theme like '%' #{theme} '%'
        </if>
        <if test="stateStr != null and stateStr != ''">
            ${stateStr}
        </if>
        <if test="createStartTime != null and createStartTime != '' and createEndTime != null and createEndTime != ''">
            and aa.create_time BETWEEN #{createStartTime} AND #{createEndTime}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and (aa.start_time BETWEEN #{startTime} AND #{endTime} or aa.end_time BETWEEN #{startTime} AND #{endTime})
        </if>
        <if test="signsTime != null and signsTime != '' and signeTime != null and signeTime != ''">
            and (aa.signs_time BETWEEN #{signsTime} and #{signeTime} or aa.signe_time BETWEEN #{signsTime} and #{signeTime})
        </if>
        order by aa.create_time desc
    </select>


    <resultMap type="com.template.model.vo.AlumniActivityApplyPageVo" id="AlumniActivityApplyPageMap">
        <result property="id" column="id"/>
        <result property="name" column="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="orgName" column="org_name"/>
        <result property="theme" column="theme"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="signsTime" column="signs_time"/>
        <result property="signeTime" column="signe_time"/>
        <result property="createTime" column="create_time"/>
        <result property="reportNumber" column="report_number"/>
        <result property="signinNumber" column="signin_number"/>
        <result property="passTime" column="pass_time"/>
        <result property="isPass" column="is_pass"/>
        <result property="passValue" column="pass_value"/>
        <result property="applyUserAdmin" column="apply_user_admin"/>
    </resultMap>
    <select id="queryActivityApplyPages" resultType="com.template.model.vo.AlumniActivityApplyPageVo" resultMap="AlumniActivityApplyPageMap">
        select aa.id,au.name,au.college_name,au.period_name,au.major_name,au.class_name,aa.org_name,aa.theme,aa.start_time,aa.end_time,aa.signs_time,aa.signe_time,aa.create_time,
        aa.pass_time,aa.apply_user_admin,aa.pass_value,aa.is_pass,
        (select count(*) from activity_report where deleted = 0 and activity_id = aa.id) as report_number,
        (select count(*) from activity_signin where deleted = 0 and activity_id = aa.id) as signin_number
        from alumni_activity aa
        inner join alumni_user au on au.deleted = 0 and au.id = aa.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 aa.deleted = 0
        <if test="orgName != null and orgName != ''">
            and aa.org_name like '%' #{orgName} '%'
        </if>
        <if test="theme != null and theme != ''">
            and aa.theme like '%' #{theme} '%'
        </if>
        <if test="isPass != null and isPass != 0">
            and aa.is_pass = #{isPass}
        </if>
        <if test="createStartTime != null and createStartTime != '' and createEndTime != null and createEndTime != ''">
            and aa.create_time BETWEEN #{createStartTime} AND #{createEndTime}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and (aa.start_time BETWEEN #{startTime} AND #{endTime} or aa.end_time BETWEEN #{startTime} AND #{endTime})
        </if>
        <if test="signsTime != null and signsTime != '' and signeTime != null and signeTime != ''">
            and (aa.signs_time BETWEEN #{signsTime} and #{signeTime} or aa.signe_time BETWEEN #{signsTime} and #{signeTime})
        </if>
        <if test="userId != null and userId != 0">
            and FIND_IN_SET(${userId},aa.apply_user)
        </if>
        order by aa.create_time desc
    </select>


    <resultMap type="com.template.model.vo.AlumniMyActivityPageVo" id="AlumniMyActivityPageMap">
        <result property="id" column="id"/>
        <result property="orgName" column="org_name"/>
        <result property="theme" column="theme"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="signsTime" column="signs_time"/>
        <result property="signeTime" column="signe_time"/>
        <result property="createTime" column="create_time"/>
        <result property="passTime" column="pass_time"/>
        <result property="isPass" column="is_pass"/>
        <result property="passValue" column="pass_value"/>
        <result property="poster" column="poster"/>
    </resultMap>
    <select id="queryMyActivityPages" resultType="com.template.model.vo.AlumniMyActivityPageVo" resultMap="AlumniMyActivityPageMap">
        select aa.id,aa.org_name,aa.theme,aa.start_time,aa.end_time,aa.signs_time,aa.signe_time,aa.create_time,aa.pass_time,aa.is_pass,aa.pass_value,aa.poster
        from alumni_activity aa
        left join alumni_user au on au.deleted = 0 and au.id = aa.create_user
        where aa.deleted = 0
        <if test="userId != null and userId != 0">
            and aa.create_user = #{userId}
        </if>
        <if test="isPass != null and isPass != 0">
            and aa.is_pass = #{isPass}
        </if>
        order by aa.create_time desc
    </select>


    <select id="queryMyActivityApplyPages" resultType="com.template.model.vo.AlumniMyActivityPageVo" resultMap="AlumniMyActivityPageMap">
        select aa.id,aa.org_name,aa.theme,aa.start_time,aa.end_time,aa.signs_time,aa.signe_time,aa.create_time,aa.pass_time,aa.is_pass,aa.pass_value,aa.poster
        from alumni_activity aa
        inner join alumni_user au on au.deleted = 0 and au.id = aa.create_user
        <if test="userName != null and userName != ''">
            and au.name like '%' #{userName} '%'
        </if>
        where aa.deleted = 0
        <if test="userId != null and userId != 0">
            and aa.create_user = #{userId}
        </if>
        order by aa.create_time desc
    </select>


    <select id="queryApproveActivityApplyPages" resultType="com.template.model.vo.AlumniMyActivityPageVo" resultMap="AlumniMyActivityPageMap">
        select aa.id,aa.org_name,aa.theme,aa.start_time,aa.end_time,aa.signs_time,aa.signe_time,aa.create_time,aa.pass_time,aa.is_pass,aa.pass_value,aa.poster
        from alumni_activity aa
        inner join alumni_user au on au.deleted = 0 and au.id = aa.create_user
        <if test="userName != null and userName != ''">
            and au.name like '%' #{userName} '%'
        </if>
        where aa.deleted = 0 and aa.is_pass = 1
        <if test="userId != null and userId != 0">
            and FIND_IN_SET(${userId},aa.apply_user)
        </if>
        order by aa.create_time desc
    </select>
</mapper>
