<?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.AlumniEndorseMapper">

    <resultMap type="com.template.model.vo.AlumniEndorsePageVo" id="AlumniEndorsePageMap">
        <result property="id" column="id"/>
        <result property="dataNo" column="data_no"/>
        <result property="name" column="name"/>
        <result property="cardNumber" column="card_number"/>
        <result property="phone" column="phone"/>
        <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="preferredMethod" column="preferred_method"/>
        <result property="image" column="image"/>
        <result property="descript" column="descript"/>
        <result property="createTime" column="create_time"/>
    </resultMap>
    <select id="alumniEndorsePageDatas" resultType="com.template.model.vo.AlumniEndorsePageVo"
            resultMap="AlumniEndorsePageMap">
        select
        ae.id,ae.data_no,ae.name,ae.card_number,au.phone,au.college_name,au.period_name,au.major_name,au.class_name,ae.preferred_method,ae.image,ae.descript,ae.create_time
        from alumni_endorse ae
        inner join alumni_user au on au.deleted = 0 and au.id = ae.user_id
        <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 ae.deleted = 0 and ae.is_pass = 2
        <if test="keyword != null and keyword != ''">
            and (ae.data_no like '%' #{keyword} '%' or ae.name like '%' #{keyword} '%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and ae.create_time BETWEEN #{startTime} AND #{endTime}
        </if>
        order by ae.create_time desc
    </select>


    <resultMap type="com.template.model.vo.AlumniEndorseApplyPageVo" id="AlumniEndorseApplyPageMap">
        <result property="id" column="id"/>
        <result property="dataNo" column="data_no"/>
        <result property="name" column="name"/>
        <result property="cardNumber" column="card_number"/>
        <result property="phone" column="phone"/>
        <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="preferredMethod" column="preferred_method"/>
        <result property="image" column="image"/>
        <result property="isPass" column="is_pass"/>
        <result property="descript" column="descript"/>
        <result property="passTime" column="pass_time"/>
        <result property="createTime" column="create_time"/>
    </resultMap>
    <select id="alumniEndorsePageApplyDatas" resultType="com.template.model.vo.AlumniEndorseApplyPageVo"
            resultMap="AlumniEndorseApplyPageMap">
        select
        ae.id,ae.data_no,ae.name,ae.card_number,au.phone,au.college_name,au.period_name,au.major_name,au.class_name,ae.preferred_method,ae.image,ae.descript,ae.pass_time,ae.create_time,ae.is_pass
        from alumni_endorse ae
        inner join alumni_user au on au.deleted = 0 and au.id = ae.user_id
        <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 ae.deleted = 0
        <if test="isPass != null and isPass != 0">
            and ae.is_pass = #{isPass}
        </if>
        <if test="keyword != null and keyword != ''">
            and (ae.data_no like '%' #{keyword} '%' or ae.name like '%' #{keyword} '%')
        </if>
        <if test="name != null and name != ''">
            and ae.name like '%' #{name} '%'
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and ae.create_time BETWEEN #{startTime} AND #{endTime}
        </if>
        <if test="userId != null and userId != 0">
            and FIND_IN_SET(${userId},ae.apply_user)
        </if>
        order by ae.create_time desc
    </select>


    <resultMap type="com.template.model.vo.MoblieEndorsePageVo" id="MobileEndorsePageMap">
        <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="image" column="image"/>
    </resultMap>
    <select id="mobileEndorsePageDatas" resultType="com.template.model.vo.MoblieEndorsePageVo"
            resultMap="MobileEndorsePageMap">
        select
        ae.id,ae.name,au.college_name,au.period_name,au.major_name,au.class_name,ae.image
        from alumni_endorse ae
        inner join alumni_user au on au.deleted = 0 and au.id = ae.user_id
        where ae.deleted = 0 and ae.is_pass = 2
        <if test="keyword != null and keyword != ''">
            and ae.name like '%' #{keyword} '%'
        </if>
        order by ae.create_time desc
    </select>


    <resultMap type="com.template.model.vo.ApplicationRecordPageVo" id="ApplicationRecordDatasMap">
        <result property="id" column="id"/>
        <result property="dataNo" column="data_no"/>
        <result property="name" column="name"/>
        <result property="cardNumber" column="card_number"/>
        <result property="phone" column="phone"/>
        <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="preferredMethod" column="preferred_method"/>
        <result property="image" column="image"/>
        <result property="isPass" column="is_pass"/>
        <result property="descript" column="descript"/>
        <result property="passTime" column="pass_time"/>
        <result property="createTime" column="create_time"/>
        <result property="passValue" column="pass_value"/>
    </resultMap>
    <select id="applicationRecordDatas" resultType="com.template.model.vo.ApplicationRecordPageVo"
            resultMap="ApplicationRecordDatasMap">
        select
        ae.id,ae.data_no,ae.name,ae.card_number,au.phone,au.college_name,au.period_name,au.major_name,au.class_name,ae.preferred_method,ae.image,ae.descript,ae.pass_time,ae.create_time,ae.is_pass
        ,IFNULL( ae.pass_value,'') as pass_value
        from alumni_endorse ae
        left join alumni_user au on au.deleted = 0 and au.id = ae.user_id
        where ae.deleted = 0
        <if test="userId != null and userId != 0">
            and ae.user_id = #{userId}
        </if>
        <if test="weekType != null and weekType != ''">
            ${weekType}
        </if>
        order by ae.create_time desc
    </select>

    <select id="applicationMyRecordDatas" resultType="com.template.model.vo.ApplicationRecordPageVo"
            resultMap="ApplicationRecordDatasMap">
        select
        ae.id,ae.data_no,ae.name,ae.card_number,au.phone,au.college_name,au.period_name,au.major_name,au.class_name,ae.preferred_method,ae.image,ae.descript,ae.pass_time,ae.create_time,ae.is_pass
        ,IFNULL( ae.pass_value,'') as pass_value
        from alumni_endorse ae
        left join alumni_user au on au.deleted = 0 and au.id = ae.user_id
        where ae.deleted = 0
        <if test="userId != null and userId != 0">
            and ae.user_id = #{userId}
        </if>
        <if test="name != null and name != ''">
            and ae.name like '%' #{name} '%'
        </if>
        order by ae.create_time desc
    </select>


    <select id="applicationApproveRecordDatas" resultType="com.template.model.vo.ApplicationRecordPageVo" resultMap="ApplicationRecordDatasMap">
        select
        ae.id,ae.data_no,ae.name,ae.card_number,au.phone,au.college_name,au.period_name,au.major_name,au.class_name,ae.preferred_method,ae.image,ae.descript,ae.pass_time,ae.create_time,ae.is_pass
        ,IFNULL( ae.pass_value,'') as pass_value
        from alumni_endorse ae
        left join alumni_user au on au.deleted = 0 and au.id = ae.user_id
        where ae.deleted = 0 and ae.is_pass = 1
        <if test="userId != null and userId != 0">
            and FIND_IN_SET(${userId},ae.apply_user)
        </if>
        <if test="name != null and name != ''">
            and ae.name like '%' #{name} '%'
        </if>
        order by ae.create_time desc
    </select>
</mapper>
