<?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.bonus.canteen.core.order.mapper.OrderInfoMapper">
    
    <resultMap type="com.bonus.canteen.core.order.domain.OrderInfo" id="OrderInfoResult">
        <result property="orderId"    column="order_id"    />
        <result property="deviceOrderId"    column="device_order_id"    />
        <result property="deviceSn"    column="device_sn"    />
        <result property="deviceNum"    column="device_num"    />
        <result property="userId"    column="user_id"    />
        <result property="identityVerification"    column="identity_verification"    />
        <result property="sourceType"    column="source_type"    />
        <result property="isOnline"    column="is_online"    />
        <result property="canteenId"    column="canteen_id"    />
        <result property="canteenName"    column="canteen_name"    />
        <result property="stallId"    column="stall_id"    />
        <result property="stallName"    column="stall_name"    />
        <result property="mealtimeType"    column="mealtime_type"    />
        <result property="mealtimeName"    column="mealtime_name"    />
        <result property="orderDate"    column="order_date"    />
        <result property="payableAmount"    column="payable_amount"    />
        <result property="discountsAmount"    column="discounts_amount"    />
        <result property="realAmount"    column="real_amount"    />
        <result property="accountPayAmount"    column="account_pay_amount"    />
        <result property="externalPayAmount"    column="external_pay_amount"    />
        <result property="refundAmount"    column="refund_amount"    />
        <result property="orderTime"    column="order_time"    />
        <result property="orderType"    column="order_type"    />
        <result property="orderState"    column="order_state"    />
        <result property="orderRefundState"    column="order_refund_state"    />
        <result property="deductionType"    column="deduction_type"    />
        <result property="payTime"    column="pay_time"    />
        <result property="payType"    column="pay_type"    />
        <result property="payChannel"    column="pay_channel"    />
        <result property="payState"    column="pay_state"    />
        <result property="payParam"    column="pay_param"    />
        <result property="deliveryAmount"    column="delivery_amount"    />
        <result property="packingAmount"    column="packing_amount"    />
        <result property="deliveryType"    column="delivery_type"    />
        <result property="commentState"    column="comment_state"    />
        <result property="deviceName"    column="device_name"    />
        <result property="nickName"    column="nick_name"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectOrderInfoVo">
        select oi.order_id, oi.device_order_id, oi.device_sn, oi.device_num, oi.user_id,
               oi.identity_verification, oi.source_type, oi.is_online, oi.canteen_id, bc.canteen_name,
               oi.stall_id, bs.stall_name, oi.mealtime_type, oi.mealtime_name, oi.order_date, oi.payable_amount,
               oi.discounts_amount, oi.real_amount, oi.account_pay_amount, oi.external_pay_amount,
               oi.refund_amount, oi.order_time, oi.order_type, oi.order_state, oi.order_refund_state,
               oi.deduction_type, oi.pay_time, oi.pay_type, oi.pay_channel, oi.pay_state, oi.pay_param,
               oi.delivery_amount, oi.packing_amount, oi.delivery_type, oi.comment_state,
               di.device_name, su.nick_name, oi.create_by, oi.create_time, oi.update_by, oi.update_time,
               su.phonenumber as phoneNumber, sd.dept_full_name, ba.area_name
        from order_info oi
        left join basic_canteen bc on bc.canteen_id = oi.canteen_id
        left join basic_stall bs on bs.stall_id = oi.stall_id
        left join basic_area ba on ba.area_id = bc.area_id
        left join sys_user su on oi.user_id = su.user_id
        left join sys_dept sd on sd.dept_id = su.dept_id
        left join device_info di on oi.device_sn = di.device_sn
    </sql>

    <select id="selectOrderInfoList" parameterType="com.bonus.canteen.core.order.domain.param.OrderQueryParam" resultMap="OrderInfoResult">
        <include refid="selectOrderInfoVo"/>
        <where>
            <if test="orderIdList != null and orderIdList.size >0">
                oi.order_id in
                <foreach collection="orderIdList" item="orderId" open="(" separator="," close=")">
                    #{orderId}
                </foreach>
            </if>
            <if test="orderId != null"> and oi.order_id = #{orderId}</if>
            <if test="deviceOrderId != null  and deviceOrderId != ''"> and oi.device_order_id = #{deviceOrderId}</if>
            <if test="deviceSn != null  and deviceSn != ''"> and oi.device_sn = #{deviceSn}</if>
            <if test="deviceNum != null  and deviceNum != ''"> and oi.device_num = #{deviceNum}</if>
            <if test="userId != null "> and oi.user_id = #{userId}</if>
            <if test="identityVerification != null "> and oi.identity_verification = #{identityVerification}</if>
            <if test="sourceType != null "> and oi.source_type = #{sourceType}</if>
            <if test="isOnline != null "> and oi.is_online = #{isOnline}</if>
            <if test="canteenId != null "> and oi.canteen_id = #{canteenId}</if>
            <if test="areaId != null "> and bc.area_id = #{areaId}</if>
            <if test="canteenIdList != null and canteenIdList.size >0">
                and oi.canteen_id in
                <foreach collection="canteenIdList" item="canteenId" open="(" separator="," close=")">
                    #{canteenId}
                </foreach>
            </if>
            <if test="stallId != null "> and oi.stall_id = #{stallId}</if>
            <if test="stallIdList != null and stallIdList.size >0">
                and oi.stall_id in
                <foreach collection="stallIdList" item="stallId" open="(" separator="," close=")">
                    #{stallId}
                </foreach>
            </if>
            <if test="mealtimeType != null "> and oi.mealtime_type = #{mealtimeType}</if>
            <if test="mealtimeTypeList != null and mealtimeTypeList.size >0">
                and oi.mealtime_type in
                <foreach collection="mealtimeTypeList" item="mealtimeType" open="(" separator="," close=")">
                    #{mealtimeType}
                </foreach>
            </if>
            <if test="orderDate != null "> and oi.order_date = #{orderDate}</if>
            <if test="orderTime != null "> and oi.order_time = #{orderTime}</if>
            <if test="orderType != null "> and oi.order_type = #{orderType}</if>
            <if test="orderTypeList != null and orderTypeList.size >0">
                and oi.order_type in
                <foreach collection="orderTypeList" item="orderType" open="(" separator="," close=")">
                    #{orderType}
                </foreach>
            </if>
            <if test="orderState != null "> and oi.order_state = #{orderState}</if>
            <if test="orderStateList != null and orderStateList.size >0">
                and oi.order_state in
                <foreach collection="orderStateList" item="orderState" open="(" separator="," close=")">
                    #{orderState}
                </foreach>
            </if>
            <if test="orderRefundState != null "> and oi.order_refund_state = #{orderRefundState}</if>
            <if test="deductionType != null "> and oi.deduction_type = #{deductionType}</if>
            <if test="payTime != null "> and oi.pay_time = #{payTime}</if>
            <if test="payType != null "> and oi.pay_type = #{payType}</if>
            <if test="payChannel != null "> and oi.pay_channel = #{payChannel}</if>
            <if test="payState != null "> and oi.pay_state = #{payState}</if>
            <if test="payStateList != null and payStateList.size >0">
                and oi.pay_state in
                <foreach collection="payStateList" item="payState" open="(" separator="," close=")">
                    #{payState}
                </foreach>
            </if>
            <if test="deptIdList != null and deptIdList.size >0">
                and su.dept_id in
                <foreach collection="deptIdList" item="deptId" open="(" separator="," close=")">
                    #{deptId}
                </foreach>
            </if>
            <if test="commentState != null "> and oi.comment_state = #{commentState}</if>
            <if test="startDateTime != null "> and oi.create_time <![CDATA[ >= ]]> #{startDateTime}</if>
            <if test="endDateTime != null "> and oi.create_time <![CDATA[ <= ]]> #{endDateTime}</if>
            <if test="searchValue != null and searchValue != ''">
                and (bc.canteen_name like concat('%', #{searchValue}, '%')
                    or bs.stall_name like concat('%', #{searchValue}, '%')
                    or EXISTS (select 1 from order_detail od
                                where oi.order_id  = od.order_id
                                and od.goods_name like concat('%', #{searchValue}, '%'))
                    )
            </if>
            <if test="custSearchInfo != null and custSearchInfo != ''">
                and (
                su.nick_name like CONCAT('%',#{custSearchInfo},'%')
                or su.phonenumber = #{encryptedCustSearchValue}
                or su.user_id like concat('%', #{custSearchInfo}, '%')
                )
            </if>
            <if test="goodsSearchInfo != null and goodsSearchInfo != ''">
                and (
                EXISTS (select 1 from order_detail od
                where oi.order_id  = od.order_id
                and od.goods_name like concat('%', #{goodsSearchInfo}, '%'))
                )
            </if>
        </where>
        order by oi.create_time desc
    </select>
    
    <select id="selectOrderInfoByOrderId" parameterType="Long" resultMap="OrderInfoResult">
        <include refid="selectOrderInfoVo"/>
        where oi.order_id = #{orderId}
    </select>

    <select id="selectOrderInfoByDeviceOrderId" parameterType="String" resultMap="OrderInfoResult">
        <include refid="selectOrderInfoVo"/>
        where oi.device_order_id = #{orderId}
    </select>

    <insert id="insertOrderInfo" parameterType="com.bonus.canteen.core.order.domain.OrderInfo">
        insert into order_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="orderId != null">order_id,</if>
            <if test="deviceOrderId != null and deviceOrderId != ''">device_order_id,</if>
            <if test="deviceSn != null and deviceSn != ''">device_sn,</if>
            <if test="deviceNum != null and deviceNum != ''">device_num,</if>
            <if test="userId != null">user_id,</if>
            <if test="identityVerification != null">identity_verification,</if>
            <if test="sourceType != null">source_type,</if>
            <if test="isOnline != null">is_online,</if>
            <if test="canteenId != null">canteen_id,</if>
            <if test="stallId != null">stall_id,</if>
            <if test="mealtimeType != null">mealtime_type,</if>
            <if test="mealtimeName != null">mealtime_name,</if>
            <if test="orderDate != null">order_date,</if>
            <if test="payableAmount != null">payable_amount,</if>
            <if test="discountsAmount != null">discounts_amount,</if>
            <if test="realAmount != null">real_amount,</if>
            <if test="accountPayAmount != null">account_pay_amount,</if>
            <if test="externalPayAmount != null">external_pay_amount,</if>
            <if test="refundAmount != null">refund_amount,</if>
            <if test="orderTime != null">order_time,</if>
            <if test="orderType != null">order_type,</if>
            <if test="orderState != null">order_state,</if>
            <if test="orderRefundState != null">order_refund_state,</if>
            <if test="deductionType != null">deduction_type,</if>
            <if test="payTime != null">pay_time,</if>
            <if test="payType != null">pay_type,</if>
            <if test="payChannel != null">pay_channel,</if>
            <if test="payState != null">pay_state,</if>
            <if test="payParam != null">pay_param,</if>
            <if test="deliveryAmount != null">delivery_amount,</if>
            <if test="packingAmount != null">packing_amount,</if>
            <if test="deliveryType != null">delivery_type,</if>
            <if test="commentState != null">comment_state,</if>
            <if test="createBy != null and createBy != ''">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null and updateBy != ''">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="orderId != null">#{orderId},</if>
            <if test="deviceOrderId != null and deviceOrderId != ''">#{deviceOrderId},</if>
            <if test="deviceSn != null and deviceSn != ''">#{deviceSn},</if>
            <if test="deviceNum != null and deviceNum != ''">#{deviceNum},</if>
            <if test="userId != null">#{userId},</if>
            <if test="identityVerification != null">#{identityVerification},</if>
            <if test="sourceType != null">#{sourceType},</if>
            <if test="isOnline != null">#{isOnline},</if>
            <if test="canteenId != null">#{canteenId},</if>
            <if test="stallId != null">#{stallId},</if>
            <if test="mealtimeType != null">#{mealtimeType},</if>
            <if test="mealtimeName != null">#{mealtimeName},</if>
            <if test="orderDate != null">#{orderDate},</if>
            <if test="payableAmount != null">#{payableAmount},</if>
            <if test="discountsAmount != null">#{discountsAmount},</if>
            <if test="realAmount != null">#{realAmount},</if>
            <if test="accountPayAmount != null">#{accountPayAmount},</if>
            <if test="externalPayAmount != null">#{externalPayAmount},</if>
            <if test="refundAmount != null">#{refundAmount},</if>
            <if test="orderTime != null">#{orderTime},</if>
            <if test="orderType != null">#{orderType},</if>
            <if test="orderState != null">#{orderState},</if>
            <if test="orderRefundState != null">#{orderRefundState},</if>
            <if test="deductionType != null">#{deductionType},</if>
            <if test="payTime != null">#{payTime},</if>
            <if test="payType != null">#{payType},</if>
            <if test="payChannel != null">#{payChannel},</if>
            <if test="payState != null">#{payState},</if>
            <if test="payParam != null">#{payParam},</if>
            <if test="deliveryAmount != null">#{deliveryAmount},</if>
            <if test="packingAmount != null">#{packingAmount},</if>
            <if test="deliveryType != null">#{deliveryType},</if>
            <if test="commentState != null">#{commentState},</if>
            <if test="createBy != null and createBy != ''">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null and updateBy != ''">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <insert id="batchInsertOrderInfo" useGeneratedKeys="true" keyProperty="orderId" parameterType="java.util.List">
        insert into order_info (
        order_id,
        device_order_id,
        device_sn,
        device_num,
        user_id,
        identity_verification,
        source_type,
        is_online,
        canteen_id,
        stall_id,
        mealtime_type,
        mealtime_name,
        order_date,
        payable_amount,
        discounts_amount,
        real_amount,
        account_pay_amount,
        external_pay_amount,
        refund_amount,
        order_time,
        order_type,
        order_state,
        order_refund_state,
        deduction_type,
        pay_time,
        pay_type,
        pay_channel,
        pay_state,
        pay_param,
        delivery_amount,
        packing_amount,
        delivery_type,
        comment_state,
        create_by,
        update_by
        )
        values
        <foreach collection="list" item="orderInfo" separator=",">
            (
            #{orderInfo.orderId},
            #{orderInfo.deviceOrderId},
            #{orderInfo.deviceSn},
            #{orderInfo.deviceNum},
            #{orderInfo.userId},
            #{orderInfo.identityVerification},
            #{orderInfo.sourceType},
            #{orderInfo.isOnline},
            #{orderInfo.canteenId},
            #{orderInfo.stallId},
            #{orderInfo.mealtimeType},
            #{orderInfo.mealtimeName},
            #{orderInfo.orderDate},
            #{orderInfo.payableAmount},
            #{orderInfo.discountsAmount},
            #{orderInfo.realAmount},
            #{orderInfo.accountPayAmount},
            #{orderInfo.externalPayAmount},
            #{orderInfo.refundAmount},
            #{orderInfo.orderTime},
            #{orderInfo.orderType},
            #{orderInfo.orderState},
            #{orderInfo.orderRefundState},
            #{orderInfo.deductionType},
            #{orderInfo.payTime},
            #{orderInfo.payType},
            #{orderInfo.payChannel},
            #{orderInfo.payState},
            #{orderInfo.payParam},
            #{orderInfo.deliveryAmount},
            #{orderInfo.packingAmount},
            #{orderInfo.deliveryType},
            #{orderInfo.commentState},
            #{orderInfo.createBy},
            #{orderInfo.updateBy}
            )
        </foreach>
    </insert>

    <update id="updateOrderInfo" parameterType="com.bonus.canteen.core.order.domain.OrderInfo">
        update order_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceOrderId != null and deviceOrderId != ''">device_order_id = #{deviceOrderId},</if>
            <if test="deviceSn != null and deviceSn != ''">device_sn = #{deviceSn},</if>
            <if test="deviceNum != null and deviceNum != ''">device_num = #{deviceNum},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="identityVerification != null">identity_verification = #{identityVerification},</if>
            <if test="sourceType != null">source_type = #{sourceType},</if>
            <if test="isOnline != null">is_online = #{isOnline},</if>
            <if test="canteenId != null">canteen_id = #{canteenId},</if>
            <if test="stallId != null">stall_id = #{stallId},</if>
            <if test="mealtimeType != null">mealtime_type = #{mealtimeType},</if>
            <if test="mealtimeName != null">mealtime_name = #{mealtimeName},</if>
            <if test="orderDate != null">order_date = #{orderDate},</if>
            <if test="payableAmount != null">payable_amount = #{payableAmount},</if>
            <if test="discountsAmount != null">discounts_amount = #{discountsAmount},</if>
            <if test="realAmount != null">real_amount = #{realAmount},</if>
            <if test="accountPayAmount != null">account_pay_amount = #{accountPayAmount},</if>
            <if test="externalPayAmount != null">external_pay_amount = #{externalPayAmount},</if>
            <if test="refundAmount != null">refund_amount = #{refundAmount},</if>
            <if test="orderTime != null">order_time = #{orderTime},</if>
            <if test="orderType != null">order_type = #{orderType},</if>
            <if test="orderState != null">order_state = #{orderState},</if>
            <if test="orderRefundState != null">order_refund_state = #{orderRefundState},</if>
            <if test="deductionType != null">deduction_type = #{deductionType},</if>
            <if test="payTime != null">pay_time = #{payTime},</if>
            <if test="payType != null">pay_type = #{payType},</if>
            <if test="payChannel != null">pay_channel = #{payChannel},</if>
            <if test="payState != null">pay_state = #{payState},</if>
            <if test="payParam != null">pay_param = #{payParam},</if>
            <if test="deliveryAmount != null">delivery_amount = #{deliveryAmount},</if>
            <if test="packingAmount != null">packing_amount = #{packingAmount},</if>
            <if test="deliveryType != null">delivery_type = #{deliveryType},</if>
            <if test="commentState != null">comment_state = #{commentState},</if>
            <if test="createBy != null and createBy != ''">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where order_id = #{orderId}
    </update>

    <update id="updateOrderPayResult">
        update order_info set
            <if test="param.payAmount != null">account_pay_amount = #{param.payAmount},</if>
            <if test="param.payState != null">pay_state = #{param.payState},</if>
            <if test="param.updateBy != null and param.updateBy != ''">update_by = #{param.updateBy},</if>
            <if test="param.orderState != null">order_state = #{param.orderState},</if>
            <if test="param.payTime != null">pay_time = #{param.payTime},</if>
            <if test="param.updateTime != null">update_time = #{param.updateTime}</if>
        where order_id in
        <foreach item="orderId" collection="param.orderIdList" open="(" separator="," close=")">
            #{orderId}
        </foreach>
    </update>

    <delete id="deleteOrderInfoByOrderId" parameterType="Long">
        delete from order_info where order_id = #{orderId}
    </delete>

    <delete id="deleteOrderInfoByOrderIds" parameterType="String">
        delete from order_info where order_id in 
        <foreach item="orderId" collection="orderIds" open="(" separator="," close=")">
            #{orderId}
        </foreach>
    </delete>

    <select id="orderRefundHistory" resultType="com.bonus.canteen.core.order.domain.OrderRefundHistoryVO">
        select su.user_id,
               su.nick_name,
               oi.order_id,
               oi.real_amount,
               oi.pay_time,
               oi.pay_type,
<!--               at2.trade_id,-->
               at2.actual_amount,
               at2.trade_type,
               at2.trade_time,
               at2.update_by
        from account_trade at2
                 left join order_info oi on oi.order_id = at2.order_no
                 left join sys_user su on at2.user_id = su.user_id
        where at2.trade_type = 130 and at2.pay_state = 3
        <if test="param.searchValue != null and param.searchValue != ''">
            and (su.nick_name like CONCAT('%',#{param.searchValue},'%')
            or su.user_id like CONCAT('%',#{param.searchValue},'%')
            )
        </if>
        <if test="param.deptIdList != null and param.deptIdList.size >0">
            and su.dept_id in
            <foreach collection="param.deptIdList" item="deptId" open="(" separator="," close=")">
                #{deptId}
            </foreach>
        </if>
        <if test="param.startDateTime != null ">
         and at2.trade_time <![CDATA[ >= ]]> #{param.startDateTime}
        </if>
        <if test="param.endDateTime != null ">
         and at2.trade_time <![CDATA[ <= ]]> #{param.endDateTime}
        </if>
        <if test="param.createBy != null ">
            and at2.update_by like CONCAT('%',#{param.createBy},'%')
        </if>
        <if test="param.orderId != null">
            and oi.order_id =#{param.orderId}
        </if>
        order by at2.trade_time desc
    </select>

    <update id="writeOffOrderByOrderIds">
        update order_info set
        order_state = 2
        where order_id in
        <foreach item="orderId" collection="param.orderIdList" open="(" separator="," close=")">
            #{orderId}
        </foreach>
    </update>

</mapper>