<?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.miaxis.order.mapper.OrderInfoMapper">

    <resultMap type="OrderInfo" id="OrderInfoResult">
        <result property="id"    column="id"    />
        <result property="goodsName"    column="goods_name"    />
        <result property="userId"    column="user_id"    />
        <result property="userName"    column="user_name"    />
        <result property="logincode"    column="logincode"    />
        <result property="outTradeNo"    column="out_trade_no"    />
        <result property="outRefundNo"    column="out_refund_no"    />
        <result property="tradeType"    column="trade_type"    />
        <result property="successTime"    column="success_time"    />
        <result property="payType"    column="pay_type"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="isShare"    column="is_share"    />
        <result property="dqbh"    column="dqbh"    />
        <result property="dqmc"    column="dqmc"    />
        <result property="school"    column="school"    />
        <result property="schoolName"    column="school_name"    />
        <result property="schoolCommission"    column="school_commission"    />
        <result property="phoneType"    column="phone_type"    />
        <result property="refundReason"    column="refund_reason"    />


    </resultMap>

    <sql id="selectOrderInfoVo">
        select * from order_info
    </sql>

    <select id="selectOrderInfoList" parameterType="OrderInfo" resultMap="OrderInfoResult">
        <include refid="selectOrderInfoVo"/>
        <where>
            <if test="isShare != null"> and is_share = #{isShare }</if>
            <if test="goodsName != null  and goodsName != ''"> and goods_name like concat('%', #{goodsName}, '%')</if>
            <if test="schoolName != null  and schoolName != ''"> and school_name like concat('%', #{schoolName}, '%')</if>
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="userName != null  and userName != ''"> and user_name like concat('%', #{userName}, '%')</if>
            <if test="logincode != null  and logincode != ''"> and logincode = #{logincode} </if>
            <if test="outTradeNo != null  and outTradeNo != ''"> and out_trade_no = #{outTradeNo}</if>
            <if test="outRefundNo != null  and outRefundNo != ''"> and out_refund_no = #{outRefundNo}</if>
            <if test="tradeType != null  and tradeType != ''"> and trade_type = #{tradeType}</if>
            <if test="successTime != null "> and success_time = #{successTime}</if>
            <if test="payType != null "> and pay_type = #{payType}</if>
        </where>
        order by success_time desc
    </select>


    <select id="getByOutTradeNo" parameterType="string" resultType="com.miaxis.order.domain.OrderInfo">
        select * from order_info where out_trade_no =#{outTradeNo}
    </select>

    <select id="selectSchoolOrderInfoList"  resultType="com.miaxis.order.vo.QuerySchoolOrderListVo">
        select
        school,
        school_name,
        sum(school_commission) as school_commission,
        sum(total) as orderTotal
        from order_info
        <where>
            <if test="isShare != null">
                and is_share = #{isShare }
            </if>
            <if test="tradeType != null">
                and trade_type = #{tradeType }
            </if>
            <if test="dqbh != null  and dqbh != ''">
                and dqbh = #{dqbh}
            </if>
            <if test="schoolName != null  and schoolName != ''">
                and school_name like concat('%', #{schoolName}, '%')
            </if>
            <if test="startTime != null">
                and DATE_FORMAT(create_time,'%Y%m%d')<![CDATA[ >= ]]> #{startTime}
            </if>
            <if test="endTime != null">
                and DATE_FORMAT(create_time,'%Y%m%d') <![CDATA[ < ]]> #{endTime}
            </if>
        </where>
        group by school,school_name

    </select>


    <select id="selectSchoolOrderDetailed"  resultType="com.miaxis.order.domain.OrderInfo">
        select * from order_info
        <where>
            <if test="isShare != null">
                and is_share = #{isShare }
            </if>
            <if test="tradeType != null">
                and trade_type = #{tradeType }
            </if>
            <if test="schoolName != null  and schoolName != ''">
                and school_name like concat('%', #{schoolName}, '%')
            </if>
            <if test="startTime != null">
                and DATE_FORMAT(create_time,'%Y%m%d')<![CDATA[ >= ]]> #{startTime}
            </if>
            <if test="endTime != null">
                and DATE_FORMAT(create_time,'%Y%m%d') <![CDATA[ < ]]> #{endTime}
            </if>
        </where>
        order by success_time desc
    </select>


</mapper>
