<?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="io.renren.dao.ConsumeRecordMapper">

    <resultMap type="ConsumeRecord" id="ConsumeRecordResult">
        <result property="id"    column="id"    />
        <result property="ver"    column="ver"    />
        <result property="empId"    column="emp_id"    />
        <result property="accountId"    column="account_id"    />
        <result property="base64"    column="base64"    />
        <result property="consumeMode"    column="consume_mode"    />
        <result property="consumeMoney"    column="consume_money"    />
        <result property="discountMoney"    column="discount_money"    />
        <result property="outTradeId"    column="out_trade_id"    />
        <result property="startTime"    column="start_time"    />
        <result property="endTime"    column="end_time"    />
        <result property="resultCode"    column="result_code"    />
        <result property="payMode"    column="pay_mode"    />
        <result property="orderType"    column="order_type"    />
        <result property="walletConsumeMode"    column="wallet_consume_mode"    />
        <result property="likeId"    column="like_id"    />
        <result property="cardSn"    column="card_sn"    />
        <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="startDate"    column="start_date"    />
        <result property="endDate"    column="end_date"    />
        <result property="consumeResult"    column="consume_result"    />
        <result property="accountName"    column="account_name"    />
        <result property="buyerId"    column="buyer_id"    />
        <result property="remark"    column="remark"    />

    </resultMap>

    <sql id="selectConsumeRecordVo">
        select cr.id, cr.ver, cr.emp_id, cr.account_id, cr.base64, cr.consume_mode, cr.consume_money, cr.discount_money, cr.out_trade_id,
               cr.start_time, cr.end_time, cr.result_code, cr.pay_mode, cr.order_type, cr.wallet_consume_mode, cr.like_id, cr.card_sn,
               cr.create_by, cr.create_time, cr.update_by, cr.update_time,cr.start_date,cr.end_date,cr.consume_result,sp.name account_name,cr.buyer_id,cr.remark
        from consume_record cr
        left join sys_people sp on sp.id=cr.account_id
    </sql>

    <select id="selectConsumeRecordList" parameterType="ConsumeRecord" resultMap="ConsumeRecordResult">
        <include refid="selectConsumeRecordVo"/>
        <where>
            <if test="empId != null  and empId != ''"> and cr.emp_id = #{empId}</if>
            <if test="consumeMode != null  and consumeMode != ''"> and cr.consume_mode = #{consumeMode}</if>
            <if test="consumeMoney != null  and consumeMoney != ''"> and cr.consume_money = #{consumeMoney}</if>
            <if test="outTradeId != null  and outTradeId != ''"> and cr.out_trade_id = #{outTradeId}</if>
            <if test="startTime != null  and startTime != ''"> and cr.start_time = #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and cr.end_time = #{endTime}</if>
            <if test="resultCode != null  and resultCode != ''"> and cr.result_code = #{resultCode}</if>
            <if test="payMode != null  and payMode != ''"> and cr.pay_mode = #{payMode}</if>
            <if test="devId != null  and devId != ''"> and cr.dev_id = #{devId}</if>
            <if test="consumeResult != null">and cr.consume_result = #{consumeResult}</if>
        </where>
            order by cr.start_time desc
            LIMIT 10
    </select>

    <select id="selectConsumeRecordAllList" parameterType="ConsumeRecord" resultMap="ConsumeRecordResult">
        <include refid="selectConsumeRecordVo"/>
        <where>
            1=1 and   curdate()=date_format(cr.start_date,'%Y-%m-%d')
            <if test="devId != null  and devId != ''"> and cr.dev_id = #{devId}</if>
            <if test="consumeResult != null">and cr.consume_result = #{consumeResult}</if>
            <if test="accountId != null  and accountId != ''"> and cr.account_id = #{accountId}</if>
            <if test="timeId != null  and timeId != ''"> and cr.time_id = #{timeId}</if>
        </where>
        order by cr.start_time desc
    </select>


    <select id="selectConsumeRecordById" parameterType="Long" resultMap="ConsumeRecordResult">
        <include refid="selectConsumeRecordVo"/>
        where id = #{id}
    </select>

    <insert id="insertConsumeRecord" parameterType="ConsumeRecord" useGeneratedKeys="true" keyProperty="id">
        insert into consume_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="ver != null">ver,</if>
            <if test="empId != null">emp_id,</if>
            <if test="accountId != null">account_id,</if>
            <if test="base64 != null">base64,</if>
            <if test="consumeMode != null">consume_mode,</if>
            <if test="consumeMoney != null">consume_money,</if>
            <if test="discountMoney != null">discount_money,</if>
            <if test="outTradeId != null">out_trade_id,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="resultCode != null">result_code,</if>
            <if test="payMode != null">pay_mode,</if>
            <if test="orderType != null">order_type,</if>
            <if test="walletConsumeMode != null">wallet_consume_mode,</if>
            <if test="likeId != null">like_id,</if>
            <if test="cardSn != null">card_sn,</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="startDate != null">start_date,</if>
            <if test="endDate != null">end_date,</if>
            <if test="consumeResult != null">consume_result,</if>
            <if test="remark != null">remark,</if>
            <if test="devId != null">dev_id,</if>
            <if test="timeId != null">time_id,</if>
            <if test="buyerId != null">buyer_id,</if>
            <if test="openid != null">openid,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="ver != null">#{ver},</if>
            <if test="empId != null">#{empId},</if>
            <if test="accountId != null">#{accountId},</if>
            <if test="base64 != null">#{base64},</if>
            <if test="consumeMode != null">#{consumeMode},</if>
            <if test="consumeMoney != null">#{consumeMoney},</if>
            <if test="discountMoney != null">#{discountMoney},</if>
            <if test="outTradeId != null">#{outTradeId},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="resultCode != null">#{resultCode},</if>
            <if test="payMode != null">#{payMode},</if>
            <if test="orderType != null">#{orderType},</if>
            <if test="walletConsumeMode != null">#{walletConsumeMode},</if>
            <if test="likeId != null">#{likeId},</if>
            <if test="cardSn != null">#{cardSn},</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="startDate != null">#{startDate},</if>
            <if test="endDate != null">#{endDate},</if>
            <if test="consumeResult != null">#{consumeResult},</if>
            <if test="remark != null">#{remark},</if>
            <if test="devId != null">#{devId},</if>
            <if test="timeId != null">#{timeId},</if>
            <if test="buyerId != null">#{buyerId},</if>
            <if test="openid != null">#{openid},</if>

        </trim>
    </insert>

    <update id="updateConsumeRecord" parameterType="ConsumeRecord">
        update consume_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="ver != null">ver = #{ver},</if>
            <if test="empId != null">emp_id = #{empId},</if>
            <if test="accountId != null">account_id = #{accountId},</if>
            <if test="base64 != null">base64 = #{base64},</if>
            <if test="consumeMode != null">consume_mode = #{consumeMode},</if>
            <if test="consumeMoney != null">consume_money = #{consumeMoney},</if>
            <if test="discountMoney != null">discount_money = #{discountMoney},</if>
            <if test="outTradeId != null">out_trade_id = #{outTradeId},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="resultCode != null">result_code = #{resultCode},</if>
            <if test="payMode != null">pay_mode = #{payMode},</if>
            <if test="orderType != null">order_type = #{orderType},</if>
            <if test="walletConsumeMode != null">wallet_consume_mode = #{walletConsumeMode},</if>
            <if test="likeId != null">like_id = #{likeId},</if>
            <if test="cardSn != null">card_sn = #{cardSn},</if>
            <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="startDate != null">start_date = #{startDate},</if>
            <if test="endDate != null">end_date = #{endDate},</if>
            <if test="consumeResult != null">consume_result = #{consumeResult},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="devId != null">dev_id = #{devId},</if>
            <if test="timeId != null">time_id = #{timeId},</if>
            <if test="buyerId != null">buyer_id = #{buyerId},</if>
            <if test="openid != null">openid = #{openid},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteConsumeRecordById" parameterType="Long">
        delete from consume_record where id = #{id}
    </delete>

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

    <select id="selectConsumeRecordByOutTradeId" parameterType="String" resultMap="ConsumeRecordResult">
        <include refid="selectConsumeRecordVo"/>
        where out_trade_id = #{outTradeId}
    </select>

    <select id="selectConsumeRecordSumonline" resultType="java.util.Map" parameterType="String">
        select "消费总额" name , count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0   and c.dev_id=#{devId}
          and  curdate()=date_format(c.create_time,'%Y-%m-%d')
        UNION all
        select "在线消费" name, count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0  and c.result_code=0 and c.dev_id=#{devId}
          and  curdate()=date_format(c.create_time,'%Y-%m-%d')
        UNION all
        select "离线消费" name , count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0  and c.result_code=1 and c.dev_id=#{devId}
          and  curdate()=date_format(c.create_time,'%Y-%m-%d')
        UNION all
        select "超时消费" name,  count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0  and c.result_code=2 and c.dev_id=#{devId}
          and  curdate()=date_format(c.create_time,'%Y-%m-%d')
    </select>

    <select id="selectConsumeRecordSumonlineMonth" resultType="java.util.Map" parameterType="String">
        select "消费总额" name , count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0   and c.dev_id=#{devId}
          and  date_format(curdate(),'%Y%m')=date_format(c.create_time,'%Y%m')
        UNION all
        select "在线消费" name, count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0  and c.result_code=0 and dev_id=#{devId}
          and  date_format(curdate(),'%Y%m')=date_format(c.create_time,'%Y%m')
        UNION all
        select "离线消费" name , count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0  and c.result_code=1 and c.dev_id=#{devId}
          and  date_format(curdate(),'%Y%m')=date_format(c.create_time,'%Y%m')
        UNION all
        select "超时消费" name,  count(1) num, ROUND(sum(consume_money),2) money   from consume_record c
        where c.consume_result=0  and c.result_code=2 and c.dev_id=#{devId}
          and  date_format(curdate(),'%Y%m')=date_format(c.create_time,'%Y%m')
    </select>

</mapper>
