<?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.yiqi.order.mapper.OrderGoodsMapper">

    <resultMap type="OrderGoods" id="OrderGoodsResult">
        <result property="id" column="id"/>
        <result property="orderNo" column="order_no"/>
        <result property="sourceType" column="source_type"/>
        <result property="orgId" column="org_id"/>
        <result property="appUserId" column="app_user_id"/>
        <result property="goodsType" column="goods_type"/>
        <result property="goodsOrderType" column="goods_order_type"/>
        <result property="totalPrice" column="total_price"/>
        <result property="deductAmount" column="deduct_amount"/>
        <result property="deductPoint" column="deduct_point"/>
        <result property="payAmount" column="pay_amount"/>
        <result property="deliveryWay" column="delivery_way"/>
        <result property="deliveryCompany" column="delivery_company"/>
        <result property="deliveryNo" column="delivery_no"/>
        <result property="orderStatus" column="order_status"/>
        <result property="payTime" column="pay_time"/>
        <result property="sendTime" column="send_time"/>
        <result property="finishTime" column="finish_time"/>
        <result property="appointmentTime" column="appointment_time"/>
        <result property="refundStatus" column="refund_status"/>
        <result property="refundReason" column="refund_reason"/>
        <result property="refundTime" column="refund_time"/>
        <result property="refundAmount" column="refund_amount"/>
        <result property="payType" column="pay_type"/>
        <result property="outTradeOrderNo" column="out_trade_order_no"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createById" column="create_by_id"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateById" column="update_by_id"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <sql id="selectOrderGoodsVo">
        select id, order_no, source_type, org_id, app_user_id, goods_type, total_price, deduct_amount, deduct_point, pay_amount, delivery_way, delivery_company, delivery_no, order_status, pay_time, send_time, finish_time,appointment_time, refund_status, refund_reason, refund_time, refund_amount, pay_type, out_trade_order_no, del_flag, create_by_id, create_by, create_time, update_by_id, update_by, update_time, remark
        from order_goods
    </sql>

    <sql id="orderGoodsFinancialVo">
        select a.order_no as orderNo, a.total_price as totalPrice, a.pay_amount as payAmount, a.coupon_deduct_amount + a.cash_deduct_amount  as couponAmount,
            a.coupon_amount as couponAmount, a.create_time as createTime, b.real_name as userName, b.phoneNumber as phoneNumber,
            a.create_by as employeeName, a.create_by_id as employeeId, a.order_status as orderStatus, a.pay_type as payType
        from order_goods a
        left join app_user b on a.app_user_id = b.id
    </sql>

    <select id="selectOrderGoodsList"  resultType="com.yiqi.order.domain.vo.GoodsOrderQueryVO">
        select a.id, a.order_no as orderNo, a.source_type as sourceType, a.org_id as orgId, a.app_user_id as appUserId,
               a.goods_type as goodsType, a.total_price as totalPrice, a.deduct_amount as deductAmount, a.deduct_point as deductPoint,
               a.pay_amount as payAmount, a.delivery_way as deliveryWay, a.delivery_company as deliveryCompany,
               a.delivery_no as deliveryNo, a.order_status as orderStatus, a.pay_time as payTime, a.send_time as sendTime,
               a.finish_time as finishTime, a.appointment_time as appointmentTime, a.refund_status as refundStatus, a.refund_reason as refundReason,
               a.refund_time as refund, a.refund_amount as refundAmount, a.pay_type as payType, a.out_trade_order_no as outTradeOrderNo,
               a.del_flag as delFlag, a.create_by_id as createById, a.create_by as createBy, a.create_time as createTime,
               a.update_by_id as updateById, a.update_by as updateBy, a.update_time as updateTime, a.remark
        from order_goods a
        left join app_user b on a.app_user_id = b.id
        <where>
            and a.del_flag='0'
            <if test="orderNo != null  and orderNo != ''">
                and a.order_no = #{orderNo}
            </if>
            <if test="sourceType != null  and sourceType != ''">
                and a.source_type = #{sourceType}
            </if>
            <if test="orgId != null ">
                and a.org_id = #{orgId}
            </if>
            <if test="goodsType != null  and goodsType != ''">
                and a.goods_type = #{goodsType}
            </if>
            <if test="goodsOrderType != null  and goodsOrderType != ''">
                and a.goods_order_type = #{goodsOrderType}
            </if>
            <if test="orderStatus != null  and orderStatus != ''">
                and a.order_status = #{orderStatus}
            </if>
            <if test="phoneNumber != null ">
                and b.phone_number = #{phoneNumber}
            </if>
            <if test="realName != null ">
                and b.real_name = #{realName}
            </if>
            <if test="createTimeFrom != null and createTimeFrom !=''">
                <![CDATA[and a.create_time >= concat(#{createTimeFrom}, ' 00:00:00')]]>
            </if>
            <if test="createTimeTo != null and createTimeTo !=''">
                <![CDATA[and a.create_time <= concat(#{createTimeTo}, ' 23:59:59')]]>
            </if>
            <if test="beginPayTime != null and beginPayTime !=''">
                <![CDATA[and a.pay_time >= concat(#{beginPayTime}, ' 00:00:00')]]>
            </if>
            <if test="endPayTime != null and endPayTime !=''">
                <![CDATA[and a.pay_time <= concat(#{endPayTime}, ' 23:59:59')]]>
            </if>
            order by a.create_time DESC

            <if test="pageSizeExport != 0">
                limit #{pageSizeExport}
            </if>
        </where>
    </select>

    <select id="selectOrderGoodsById" parameterType="Long"
            resultType="com.yiqi.order.domain.vo.OrderRetailGoodsSkuVO">
        select a.order_no as orderNo, c.goods_category_name as goodsCategoryName, c.goods_img as goodsImg,
               c.goods_name as goodsName, a.sku_name as skuName, a.buy_num as buyNum,
               a.market_price as marketPrice, b.total_price as totalPrice
        from order_goods_sku a
                 left join order_goods b on a.order_id = b.id
                 left join order_goods_item c on a.order_id = a.order_id and a.order_goods_item_id = c.id
        where a.order_id = #{id}
    </select>
    <select id="getGoodsOrderListByApp" resultType="com.yiqi.order.domain.vo.weapp.AppGoodsOrderInfoQueryVO">
        select a.*
        from  order_goods a
        where a.source_type = '00' and  a.del_flag='0' and a.app_user_id = #{appUserId}
        <if test="orderStatus != null  and orderStatus != ''">
            and a.order_status = #{orderStatus}
        </if>
        <if test="goodsType != null  and goodsType != ''">
            and a.goods_type = #{goodsType}
        </if>
        GROUP BY a.order_no
        ORDER BY a.create_time DESC
        limit #{startRow},#{pageSize}
    </select>
    <select id="selectOrderGoodsByOrderNos" resultType="com.yiqi.order.domain.vo.OrderRetailGoodsSkuVO">
        select a.order_no as orderNo, c.goods_category_name as goodsCategoryName, c.goods_img as goodsImg, a.goods_id as goodsId,
        c.goods_name as goodsName, a.sku_name as skuName, a.buy_num as buyNum,
        a.market_price as marketPrice, a.buy_num * a.market_price as totalPrice
        from order_goods_sku a
        left join order_goods b on a.order_id = b.id
        left join order_goods_item c on a.order_id = a.order_id and a.order_goods_item_id = c.id
        where a.order_no   in
        <foreach item="orderNo" collection="orderNoList" open="(" separator="," close=")">
            #{orderNo}
        </foreach>
    </select>
    <select id="getRefundOrderGoodsList" resultType="com.yiqi.order.domain.vo.GoodsOrderRefundQueryVO">
        select a.*
        from order_goods a
        left join app_user b on a.app_user_id = b.id
        <where>
            and a.del_flag='0'  and a.refund_status in ('1','2')
            <if test="orderNo != null  and orderNo != ''">
                and a.order_no = #{orderNo}
            </if>
            <if test="sourceType != null  and sourceType != ''">
                and a.source_type = #{sourceType}
            </if>
            <if test="orgId != null ">
                and a.org_id = #{orgId}
            </if>
            <if test="goodsType != null  and goodsType != ''">
                and a.goods_type = #{goodsType}
            </if>
            <if test="goodsOrderType != null  and goodsOrderType != ''">
                and a.goods_order_type = #{goodsOrderType}
            </if>
            <if test="orderStatus != null  and orderStatus != ''">
                and a.order_status = #{orderStatus}
            </if>
            <if test="refundStatus != null  and refundStatus != ''">
                and a.refund_status = #{refundStatus}
            </if>
            <if test="phoneNumber != null ">
                and b.phone_number = #{phoneNumber}
            </if>
            <if test="realName != null ">
                and b.real_name = #{realName}
            </if>
            ORDER BY a.create_time DESC
            <if test="pageSizeExport != 0">
                limit #{pageSizeExport}
            </if>
        </where>

    </select>
    <select id="listFinancialStatisticsByGoodsOrderToday"
            resultType="com.yiqi.order.domain.vo.OrderGoodsFinancialVO">
        select a.order_no as orderNo , b.buy_num  as orderGoodsCount
        from order_goods a
        left join order_goods_sku b on a.order_no = b.order_no
        <where>
            a.order_status IN ('2','3','5') and a.refund_status = '0' and a.del_flag='0'
            and a.org_id = #{orgId} and a.source_type = '02'
            <if test="orderFinancialDayQueryDTO.beginCreateTime != null and orderFinancialDayQueryDTO.beginCreateTime != ''"><!-- 开始时间检索 -->
                and date_format(a.create_time,'%y%m%d') &gt;= date_format(#{orderFinancialDayQueryDTO.beginCreateTime},'%y%m%d')
            </if>
            <if test="orderFinancialDayQueryDTO.endCreateTime != null and orderFinancialDayQueryDTO.endCreateTime != ''"><!-- 结束时间检索 -->
                and date_format(a.create_time,'%y%m%d') &lt;= date_format(#{orderFinancialDayQueryDTO.endCreateTime},'%y%m%d')
            </if>
        </where>
        order by a.create_time desc
    </select>
    <select id="listFinancialStatisticsByGoodsOrderDay"
            resultType="com.yiqi.order.domain.vo.OrderGoodsFinancialVO">
        select
        a.order_no as orderNo, a.total_price as totalPrice, a.pay_amount as payAmount, a.coupon_deduct_amount + a.cash_deduct_amount  as couponAmount,
        a.create_time as createTime, b.real_name as userName, b.phone_number as phoneNumber,
        a.create_by as employeeName, a.create_by_id as employeeId, a.order_status as orderStatus, a.pay_type as payType
        from order_goods a
        left join app_user b on a.app_user_id = b.id
        <where>
        a.order_status IN ('0','1','2','3','5') and a.refund_status = '0' and a.del_flag='0'
            and a.org_id = #{orgId} and a.source_type = '02'
            <if test="orderFinancialDayQueryDTO.beginCreateTime != null and orderFinancialDayQueryDTO.beginCreateTime != ''"><!-- 开始时间检索 -->
                and date_format(a.create_time,'%y%m%d') &gt;= date_format(#{orderFinancialDayQueryDTO.beginCreateTime},'%y%m%d')
            </if>
            <if test="orderFinancialDayQueryDTO.endCreateTime != null and orderFinancialDayQueryDTO.endCreateTime != ''"><!-- 结束时间检索 -->
                and date_format(a.create_time,'%y%m%d') &lt;= date_format(#{orderFinancialDayQueryDTO.endCreateTime},'%y%m%d')
            </if>
        </where>
        order by a.create_time desc
        limit #{orderFinancialDayQueryDTO.startRow},#{orderFinancialDayQueryDTO.pageSize}
    </select>
    <select id="countFinancialStatisticsByGoodsOrderDay" resultType="java.lang.Integer">
        select
            count(*)
        from order_goods a
        <where>
            a.order_status IN ('0','1','2','3','5') and a.refund_status = '0' and a.del_flag='0'
            and a.org_id = #{orgId} and a.source_type = '02'
            <if test="orderFinancialDayQueryDTO.beginCreateTime != null and orderFinancialDayQueryDTO.beginCreateTime != ''"><!-- 开始时间检索 -->
                and date_format(a.create_time,'%y%m%d') &gt;= date_format(#{orderFinancialDayQueryDTO.beginCreateTime},'%y%m%d')
            </if>
            <if test="orderFinancialDayQueryDTO.endCreateTime != null and orderFinancialDayQueryDTO.endCreateTime != ''"><!-- 结束时间检索 -->
                and date_format(a.create_time,'%y%m%d') &lt;= date_format(#{orderFinancialDayQueryDTO.endCreateTime},'%y%m%d')
            </if>
        </where>
    </select>
    <select id="listFinancialStatisticsByGoodsOrderMTD"
            resultType="com.yiqi.order.domain.vo.OrderGoodsFinancialVO">
        select a.order_no as orderNo , b.buy_num  as orderGoodsCount
        from order_goods a
        left join order_goods_sku b on a.order_no = b.order_no
        <where>
            a.order_status IN ('2','3','5') and a.refund_status = '0' and a.del_flag='0'
            and a.org_id = #{orgId} and a.source_type = '02'
            and a.create_time BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND LAST_DAY(NOW())
        </where>
        order by a.create_time desc
    </select>
    <select id="listFinancialStatisticsByGoodsOrderMonth"
            resultType="com.yiqi.order.domain.vo.OrderGoodsFinancialVO">
        select
        a.order_no as orderNo, a.total_price as totalPrice, a.pay_amount as payAmount, a.coupon_deduct_amount + a.cash_deduct_amount  as couponAmount,
        a.create_time as createTime, b.real_name as userName, b.phone_number as phoneNumber,
        a.create_by as employeeName, a.create_by_id as employeeId, a.order_status as orderStatus, a.pay_type as payType
        from order_goods a
        left join app_user b on a.app_user_id = b.id
        <where>
            a.order_status IN ('0','1','2','3','5') and a.refund_status = '0' and a.del_flag='0'
            and a.org_id = #{orgId} and a.source_type = '02'
            <if test="orderFinancialMonthQueryDTO.monthTime != null and orderFinancialMonthQueryDTO.monthTime != ''"><!-- 开始时间检索 -->
                and DATE_FORMAT(a.create_time,'%Y-%m') = #{orderFinancialMonthQueryDTO.monthTime}
            </if>
        </where>
        order by a.create_time desc
        limit #{orderFinancialMonthQueryDTO.startRow},#{orderFinancialMonthQueryDTO.pageSize}
    </select>
    <select id="countFinancialStatisticsByGoodsOrderMonth" resultType="java.lang.Integer">
        select count(*)
        from order_goods a
        left join app_user b on a.app_user_id = b.id
        <where>
            a.order_status IN ('0','1','2','3','5') and a.refund_status = '0' and a.del_flag='0'
            and a.org_id = #{orgId} and a.source_type = '02'
            <if test="orderFinancialMonthQueryDTO.monthTime != null and orderFinancialMonthQueryDTO.monthTime != ''">
                and DATE_FORMAT(a.create_time,'%Y-%m') = #{orderFinancialMonthQueryDTO.monthTime}
            </if>
        </where>
        order by a.create_time desc
    </select>


</mapper>