<?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.ruoyi.frequency.wallerecord.mapper.WalletRecordMapper">

    <resultMap type="com.ruoyi.frequency.wallerecord.entity.WalletRecord" id="WalletRecordResult">
        <result property="id" column="id"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="delFlag" column="del_flag"/>
        <result property="userType" column="user_type"/>
        <result property="recordType" column="record_type"/>
        <result property="price" column="price"/>
        <result property="userId" column="user_id"/>
        <result property="objectId" column="object_id"/>
        <result property="status" column="status"/>
        <result property="canWithTime" column="can_with_time"/>
        <result property="noWithTime" column="no_with_time"/>
        <result property="cancelTime" column="cancel_time"/>
        <result property="currentBalance" column="current_balance"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <sql id="selectWalletRecordVo">
        select id,
               create_by,
               create_time,
               update_by,
               update_time,
               del_flag,
               user_type,
               record_type,
               price,
               remark,
               user_id,
               object_id,
               status,
               can_with_time,
               no_with_time,
               cancel_time,
               current_balance
        from t_wallet_record t
    </sql>

    <select id="selectWalletRecordById" parameterType="Long" resultMap="WalletRecordResult">
        <include refid="selectWalletRecordVo"/>
        where id = #{id}
    </select>

    <insert id="insertWalletRecord" parameterType="com.ruoyi.frequency.wallerecord.entity.WalletRecord">
        insert into t_wallet_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="userType != null">user_type,</if>
            <if test="recordType != null">record_type,</if>
            <if test="price != null">price,</if>
            <if test="userId != null">user_id,</if>
            <if test="objectId != null">object_id,</if>
            <if test="status != null">status,</if>
            <if test="canWithTime != null">can_with_time,</if>
            <if test="noWithTime != null">no_with_time,</if>
            <if test="cancelTime != null">cancel_time,</if>
            <if test="currentBalance != null">current_balance,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="userType != null">#{userType},</if>
            <if test="recordType != null">#{recordType},</if>
            <if test="price != null">#{price},</if>
            <if test="userId != null">#{userId},</if>
            <if test="objectId != null">#{objectId},</if>
            <if test="status != null">#{status},</if>
            <if test="canWithTime != null">#{canWithTime},</if>
            <if test="noWithTime != null">#{noWithTime},</if>
            <if test="cancelTime != null">#{cancelTime},</if>
            <if test="currentBalance != null">#{currentBalance},</if>
        </trim>
    </insert>

    <update id="updateWalletRecord" parameterType="com.ruoyi.frequency.wallerecord.entity.WalletRecord">
        update t_wallet_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="userType != null">user_type = #{userType},</if>
            <if test="recordType != null">record_type = #{recordType},</if>
            <if test="price != null">price = #{price},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="objectId != null">object_id = #{objectId},</if>
            <if test="status != null">status = #{status},</if>
            <if test="canWithTime != null">can_with_time = #{canWithTime},</if>
            <if test="noWithTime != null">no_with_time = #{noWithTime},</if>
            <if test="cancelTime != null">cancel_time = #{cancelTime},</if>
            <if test="currentBalance != null">current_balance = #{currentBalance},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteWalletRecordById" parameterType="Long">
        delete
        from t_wallet_record
        where id = #{id}
    </delete>

    <delete id="deleteWalletRecordByIds" parameterType="String">
        delete from t_wallet_record where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="walletPage" resultType="com.ruoyi.controller.resp.WalletPageResp">
        SELECT
        *
        FROM
        (
        SELECT
        t.id,
        t.del_flag,
        t.user_id,
        t.user_type,
        t.price,
        t.create_time,
        t.object_id,
        t.record_type,
        t.current_balance,
        CASE t.record_type
        WHEN 1 THEN
        ''
        ELSE
        ''
        END record,
        1 type,
        o.order_no,
        2 isOnline,
        2 isGivePay
        FROM
        t_wallet_record t
        LEFT JOIN t_order o ON t.object_id = o.id
        where t.record_type != 4
        UNION ALL
        SELECT
        t2.id,
        t2.del_flag,
        t2.user_id,
        t2.user_type,
        t2.price,
        t2.create_time,
        t2.object_id,
        t2.record_type,
        t2.current_balance,
        CASE t2.record_type
        WHEN 1 THEN
        ''
        ELSE
        ''
        END record,
        2 type,
        o.order_no,
        2 isOnline,
        1 isGivePay
        FROM
        t_give_wallet_record t2
        LEFT JOIN t_order o ON t2.object_id = o.id
        UNION ALL
        SELECT
        t3.id,
        t3.del_flag,
        t3.user_id,
        t3.user_type,
        t3.price,
        t3.create_time,
        t3.object_id,
        t3.object_type record_type,
        NULL current_balance,
        CASE t3.object_type
        WHEN 1 THEN
        ''
        ELSE
        ''
        END record,
        3 type,
        o.order_no,
        1 isOnline,
        2 isGivePay
        FROM
        t_online_record t3
        LEFT JOIN t_order o ON t3.object_id = o.id
        ) t
        WHERE
        t.del_flag = 1
        AND t.user_id = #{userVo.userId}
        AND t.user_type = #{userVo.userType}
        <if test="req.type != null and req.type != '' ">
            <if test="req.type == 1 ">
                and (
                (t.type = 1 and t.price > 0 and t.record_type !=4)
                    or
                (t.type = 2 and t.price > 0)
                )
            </if>
            <if test="req.type == 2 ">
                and (
                (t.type = 1 and t.price &lt; 0 )
                    or
                 t.type = 3
                )
            </if>
            <if test="req.type == 3 ">
                and (
                t.type = 3 and t.record_type = 4
                )
            </if>
            <if test="req.type == 4 ">
                and (
                t.type = 2
                )
            </if>
        </if>
        <if test="req.beginTime != null ">
            AND t.create_time >= #{req.beginTime}
        </if>
        <if test="req.endTime != null ">
            AND t.create_time &lt; #{req.endTime}
        </if>
        ORDER BY
        t.create_time DESC,t.id
    </select>

    <select id="walletStorePage" resultType="com.ruoyi.controller.resp.WalletPageResp">
        SELECT
        DATE_FORMAT( t.create_time, '%Y-%m' ) create_time,
        IFNULL( SUM( t.price ), 0 ) price,
        IF(DATE_FORMAT(now(), '%Y-%m' ) = DATE_FORMAT( t.create_time, '%Y-%m' ),1,2) isWithdrawal,
        (
        SELECT
        w.current_balance
        FROM
        t_wallet_record w
        WHERE
        w.del_flag = 1
        AND DATE_FORMAT( w.create_time, '%Y-%m' ) = DATE_FORMAT( t.create_time, '%Y-%m' )
        AND w.user_id = #{userVo.userId}
        AND w.user_type = #{userVo.userType}
        ORDER BY
        w.create_time DESC
        LIMIT 1
        ) current_balance,
        '月收益' record
        FROM
        t_wallet_record t
        WHERE
        t.del_flag = 1
        AND t.user_id = #{userVo.userId}
        AND t.user_type = #{userVo.userType}
        <choose>
            <when test="req.type != null and req.type != ''">
                <if test="req.type == 1 ">
                    and record_type in (1,3,4,8,9,13,15,19,20)
                </if>
                <if test="req.type == 2 ">
                    and record_type in (2,5,7,10,11,12)
                </if>
            </when>
            <otherwise>
                AND t.price > 0
            </otherwise>
        </choose>
        <if test="req.beginTime != null ">AND t.create_time >=
            #{req.beginTime}
        </if>
        <if test="req.endTime != null ">AND t.create_time &lt;
            #{req.endTime}
        </if>
        GROUP BY
        DATE_FORMAT( t.create_time, '%Y-%m' )
        ORDER BY
        t.create_time DESC
    </select>


    <select id="walletSalePage" resultType="com.ruoyi.controller.resp.WalletPageResp">
        SELECT
        DATE_FORMAT( t.create_time, '%Y-%m' ) create_time,
        IFNULL( SUM( t.price ), 0 ) price,
        IF(DATE_FORMAT(now(), '%Y-%m' ) = DATE_FORMAT( t.create_time, '%Y-%m' ),1,2) isWithdrawal,
        (
        SELECT
        w.current_balance
        FROM
        t_wallet_record w
        WHERE
        w.del_flag = 1
        AND DATE_FORMAT( w.create_time, '%Y-%m' ) = DATE_FORMAT( t.create_time, '%Y-%m' )
        AND w.user_id = #{userVo.userId}
        AND w.user_type = #{userVo.userType}
        ORDER BY
        w.create_time DESC
        LIMIT 1
        ) current_balance,
        '月收益' record
        FROM
        t_wallet_record t
        WHERE
        t.del_flag = 1
        AND t.user_id = #{userVo.userId}
        AND t.user_type = #{userVo.userType}
        <choose>
            <when test="req.type != null and req.type != ''">
                <if test="req.type == 1 ">
                    and record_type in (1,3,4,8,9,13,15,19,20)
                </if>
                <if test="req.type == 2 ">
                    and record_type in (2,5,7,10,11,12)
                </if>
            </when>
            <otherwise>
                AND t.price > 0
            </otherwise>
        </choose>
        <if test="req.beginTime != null ">AND t.create_time >=
            #{req.beginTime}
        </if>
        <if test="req.endTime != null ">AND t.create_time &lt;
            #{req.endTime}
        </if>
        GROUP BY
        DATE_FORMAT( t.create_time, '%Y-%m' )
        ORDER BY
        t.create_time DESC
    </select>

    <select id="selectMonthBeforeAmount" resultType="java.math.BigDecimal">
        SELECT IFNULL((SELECT sum(t.price)
                       FROM t_wallet_record t
                       WHERE t.del_flag = 1
                         AND (
                               (t.record_type in (1,6, 8)
                                   AND t.create_time &lt; #{beginOfMonth}
                                   )
                               or t.record_type in (2, 3, 7, 10, 11, 12, 17, 20,21)
                           )
                         AND t.user_type = #{userVo.userType}
                         AND t.user_id = #{userVo.userId}),
                      0)
    </select>
    <select id="walletDetail" resultType="com.ruoyi.frequency.wallerecord.entity.WalletRecord">
        SELECT
            *
        FROM
            (
                SELECT
                    t.id,
                    t.del_flag,
                    t.user_id,
                    t.user_type,
                    t.price,
                    t.create_time,
                    t.object_id,
                    t.record_type,
                    t.current_balance,
                    CASE t.record_type
                        WHEN 1 THEN
                            ''
                        ELSE
                            ''
                        END record,
                    1 type,
                    o.order_no,
                    2 isOnline,
                    2 isGivePay
                FROM
                    t_wallet_record t
                        LEFT JOIN t_order o ON t.object_id = o.id
                UNION ALL
                SELECT
                    t2.id,
                    t2.del_flag,
                    t2.user_id,
                    t2.user_type,
                    t2.price,
                    t2.create_time,
                    t2.object_id,
                    t2.record_type,
                    t2.current_balance,
                    CASE t2.record_type
                        WHEN 1 THEN
                            ''
                        ELSE
                            ''
                        END record,
                    2 type,
                    o.order_no,
                    2 isOnline,
                    1 isGivePay
                FROM
                    t_give_wallet_record t2
                        LEFT JOIN t_order o ON t2.object_id = o.id
                UNION ALL
                SELECT
                    t3.id,
                    t3.del_flag,
                    t3.user_id,
                    t3.user_type,
                    t3.price,
                    t3.create_time,
                    t3.object_id,
                    t3.object_type record_type,
                    NULL current_balance,
                    CASE t3.object_type
                        WHEN 1 THEN
                            ''
                        ELSE
                            ''
                        END record,
                    3 type,
                    o.order_no,
                    1 isOnline,
                    2 isGivePay
                FROM
                    t_online_record t3
                        LEFT JOIN t_order o ON t3.object_id = o.id
            ) t
        WHERE
            t.id = #{id}
    </select>
    <select id="selectWalletRecordList" resultType="com.ruoyi.frequency.wallerecord.entity.WalletRecord">
        SELECT
            *
        FROM
            (
                SELECT
                    t.id,
                    t.create_time,
                    t.record_type,
                    t.price,
                    t.remark,
                    2 isGivePay,
                    2 isOnline,
                    1 type
                FROM
                    t_wallet_record t
                WHERE
                    t.user_id =  #{userId}
                AND t.user_type = #{userType}
                UNION ALL
                SELECT
                    t2.id,
                    t2.create_time,
                    t2.record_type,
                    t2.price,
                    t2.remark,
                    1 isGivePay,
                    2 isOnline,
                    2 type
                FROM
                    t_give_wallet_record t2
                WHERE
                    t2.user_id = #{userId}
                AND t2.user_type = #{userType}
                UNION ALL
                SELECT
                t3.id,
                t3.create_time,
                t3.object_type record_type,
                t3.price,
                null remark,
                2 isGivePay,
                1 isOnline,
                3 type
                FROM
                t_online_record t3
                WHERE
                t3.user_id = #{userId}
                AND t3.user_type = #{userType}
            ) tt
            <where>
                <if test="beginTime != null ">
                    and  tt.create_time >= #{beginTime}
                </if>
                <if test="endTime != null ">
                    and  tt.create_time &lt;= #{endTime}
                </if>
                <if test="recordType!=null and recordType!=''">
                    and tt.record_type = #{recordType}
                </if>
                <if test="recordTypeList!=null and recordTypeList.size()>0">
                    and tt.record_type in
                    <foreach item="recordType" collection="recordTypeList" open="(" separator="," close=")">
                        #{recordType}
                    </foreach>
                </if>
                <if test="type!=null and type!=''">
                    and tt.type = #{type}
                </if>
            </where>
        order by  tt.create_time desc
    </select>
</mapper>
