<?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.ym.mec.biz.dal.dao.StudentApplyRefundsDao">

    <resultMap type="com.ym.mec.biz.dal.entity.StudentApplyRefunds" id="StudentApplyRefunds">
        <result column="id_" property="id"/>
        <result column="user_id_" property="userId"/>
        <result column="order_no_" property="orderNo"/>
        <result column="status_" property="status" typeHandler="com.ym.mec.common.dal.CustomEnumTypeHandler"/>
        <result column="expect_amount_" property="expectAmount"/>
        <result column="actual_amount_" property="actualAmount"/>
        <result column="create_time_" property="createTime"/>
        <result column="update_time_" property="updateTime"/>
        <result column="orig_payment_order_id_" property="origPaymentOrderId"/>
        <result column="remark_" property="remark"/>
    </resultMap>
    
    <resultMap type="com.ym.mec.biz.dal.entity.StudentApplyRefunds" extends="StudentApplyRefunds" id="StudentApplyRefundsListDTO">
    	<result column="spo.expect_amount_" property="studentPaymentOrder.expectAmount"/>
    	<result column="spo.actual_amount_" property="studentPaymentOrder.actualAmount"/>
    	<result column="username_" property="user.username"/>
    </resultMap>

    <!-- 根据主键查询一条记录 -->
    <select id="get" resultMap="StudentApplyRefunds">
		SELECT * FROM student_apply_refunds WHERE id_ = #{id} 
	</select>

    <!-- 全查询 -->
    <select id="findAll" resultMap="StudentApplyRefunds">
		SELECT * FROM student_apply_refunds ORDER BY id_
	</select>

    <!-- 向数据库增加一条记录 -->
    <insert id="insert" parameterType="com.ym.mec.biz.dal.entity.StudentApplyRefunds" useGeneratedKeys="true"
            keyColumn="id" keyProperty="id">
        <!--
        <selectKey resultClass="int" keyProperty="id" >
        SELECT SEQ_WSDEFINITION_ID.nextval AS ID FROM DUAL
        </selectKey>
        -->
        INSERT INTO student_apply_refunds
        (id_,user_id_,order_no_,status_,expect_amount_,actual_amount_,create_time_,update_time_,orig_payment_order_id_,remark_)
        VALUES(#{id},#{userId},#{orderNo},#{status,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler},#{expectAmount},#{actualAmount},now(),now(),#{origPaymentOrderId},#{remark})
    </insert>

    <!-- 根据主键查询一条记录 -->
    <update id="update" parameterType="com.ym.mec.biz.dal.entity.StudentApplyRefunds">
        UPDATE student_apply_refunds
        <set>
            <if test="status != null">
                status_ = #{status,typeHandler=com.ym.mec.common.dal.CustomEnumTypeHandler},
            </if>
            <if test="userId != null">
                user_id_ = #{userId},
            </if>
            <if test="orderNo != null">
                order_no_ = #{orderNo},
            </if>
            <if test="expectAmount != null">
                expect_amount_ = #{expectAmount},
            </if>
            <if test="updateTime != null">
                update_time_ = NOW(),
            </if>
            <if test="origPaymentOrderId != null">
                orig_payment_order_id_ = #{origPaymentOrderId},
            </if>
            <if test="actualAmount != null">
                actual_amount_ = #{actualAmount},
            </if>
            <if test="remark != null">
                remark_ = #{remark},
            </if>
        </set>
        WHERE id_ = #{id}
    </update>

    <!-- 根据主键删除一条记录 -->
    <delete id="delete">
		DELETE FROM student_apply_refunds WHERE id_ = #{id} 
	</delete>

    <!-- 分页查询 -->
    <select id="queryPage" resultMap="StudentApplyRefundsListDTO" parameterType="map">
        SELECT sar.*,spo.expect_amount_,spo.actual_amount_,u.username_ FROM student_apply_refunds sar left join student_payment_order spo on sar.orig_payment_order_id_ = spo.id_ left join sys_user u on sar.user_id_ = u.id_
        where 1=1 
        <if test="startTime != null">
            and sar.create_time_ &gt;= #{startTime}
        </if>
        <if test="endTime != null">
            and sar.create_time_ &lt;= #{endTime}
        </if>
        <if test="status!=null and status!=''">
            and sar.status_=#{status}
        </if>
        <if test="organId!=null and organId!=''">
            and u.organ_id_=#{organId}
        </if>
        ORDER BY sar.id_ <include refid="global.limit"/>
    </select>

    <!-- 查询当前表的总记录数 -->
    <select id="queryCount" resultType="int" parameterType="map">
		SELECT count(1) FROM student_apply_refunds sar left join student_payment_order spo on sar.orig_payment_order_id_ = spo.id_ left join sys_user u on sar.user_id_ = u.id_
		where 1=1 
        <if test="startTime != null">
            and sar.create_time_ &gt;= #{startTime}
        </if>
        <if test="endTime != null">
            and sar.create_time_ &lt;= #{endTime}
        </if>
        <if test="status!=null and status!=''">
            and sar.status_=#{status}
        </if>
        <if test="organId!=null and organId!=''">
            and u.organ_id_=#{organId}
        </if>
	</select>

    <select id="checkIsApplyRefund" resultType="java.util.Map">
        SELECT
            DISTINCT spo.music_group_id_ AS 'key',
            sar.id_ IS NOT NULL AS 'value'
        FROM
            student_payment_order spo
            LEFT JOIN student_apply_refunds sar ON sar.orig_payment_order_id_ = spo.id_
        WHERE
            spo.status_ = 'SUCCESS'
            AND spo.user_id_=#{userId}
            AND spo.music_group_id_ IN
            <foreach collection="musicGroupIds" item="musicGroupId" open="(" close=")" separator=",">
                #{musicGroupId}
            </foreach>
    </select>
    <select id="findByGroupAndType" resultMap="StudentApplyRefunds">
        SELECT
            sar.user_id_,
            sar.status_,
            sar.update_time_
        FROM
            student_payment_order spo
            LEFT JOIN student_apply_refunds sar ON spo.user_id_=sar.user_id_
            AND spo.id_ = sar.orig_payment_order_id_
        WHERE
            spo.music_group_id_ = #{groupId}
            AND spo.group_type_ = #{groupType}
            AND spo.status_ = 'SUCCESS'
            AND sar.user_id_ IS NOT NULL
        ORDER BY
            spo.update_time_ DESC

    </select>
    <select id="findByGroupAndUser" resultMap="StudentApplyRefunds">
        SELECT
            sar.*
        FROM
            student_apply_refunds sar
            LEFT JOIN student_payment_order spo ON spo.user_id_=sar.user_id_
            AND spo.id_ = sar.orig_payment_order_id_
        WHERE
            spo.music_group_id_ = #{groupId}
            AND spo.group_type_ = #{groupType}
            AND spo.user_id_ = #{userId}
            AND spo.status_ = 'SUCCESS'
            AND sar.status_ = 'ING'
        ORDER BY
            spo.update_time_ DESC
    </select>
</mapper>
