<?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.zfire.mall.manager.commonmapper.front.CustomerFrontOrderMapper">

    <select id="list" resultType="com.zfire.mall.manager.bean.front.CustomerOrderBean">
		SELECT
		co.id,
		co.`status`,
		co.order_time,
		co.pick_time,
		co.customer_id,
		co.customer_name,
		co.customer_number,
		co.user_name,
		co.phone,
		co.province,
		co.city,
		co.area,
		co.street,
		co.rece_address,
		co.jiaxian_order_id,
		co.sync_status,
		co.stock_type,
		IF(co.stock_type = 1,'前置仓','商家仓') as stock_type_name,
		coi.material_id,
		coi.material_old_number,
		coi.material_code,
		coi.material_name,
		coi.specification,
		coi.qty,
		coi.refund_qty,
		coi.qty- coi.real_qty as refund_order_qty,
		co.notes,
		co.create_by,
		co.create_time,
		cs.front_stock_num as stock_qty,
		cs.customer_stock_num as customer_stock_qty,
		co.gong_dan_id,
		co.pgid,
		co.sales_number,
		co.sales_wname,
		co.install_number,
		co.install_name,
		co.order_state,
		co.appoint_state,
		co.worker_name,
		co.worker_number,
		co.worker_phone,
		co.admin_officer_id,
		co.admin_officer_name,
		co.admin_officer_number
		FROM
		customer_order co
		JOIN customer_order_item coi ON co.id = coi.order_id
		join customer_stock cs on coi.material_id = cs.material_id and co.customer_id = cs.customer_id
		${ex.query}
		<if test="customers != null and customers.size > 0">
		and co.customer_number in
		<foreach item="item" index="index" collection="customers" open="(" separator=","
				 close=")">
			#{item}
		</foreach>
	</if>
		group by coi.id
		${ex.orderBy}
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by co.create_time desc
		</if>

	</select>

	<select id="listRefund" resultType="com.zfire.mall.manager.bean.front.CustomerRefundOrderBean">
		SELECT
		co.id,
		co.`status`,
		CASE
		WHEN co.status = '1' THEN
		'已提交'
		WHEN co.status = '2' THEN
		'已退货'
		WHEN co.status = '3' THEN
		'已发货'
		WHEN co.status = '4' THEN
		'已签收' ELSE '已退货'
		END AS status_name,
		co.order_time,
		co.pick_time,
		co.customer_id,
		co.customer_name,
		co.customer_number,
		co.user_name,
		co.phone,
		co.jiaxian_order_id,
		co.sync_status,
		co.province,
		co.city,
		co.area,
		co.street,
		co.rece_address,
		co.stock_type,
		IF(co.stock_type = 1,'前置仓','商家仓') as stock_type_name,
		coi.material_id,
		coi.material_old_number,
		coi.material_code,
		coi.material_name,
		coi.specification,
		coi.qty,
		co.notes,
		co.create_by,
		co.create_time,
		co.customer_order_no,
		co.refund_remark

		FROM
		customer_order_refund co
		JOIN customer_order_refund_item coi ON co.id = coi.order_id
		${ex.query}
		<if test="customers != null and customers.size > 0">
			and co.customer_number in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by co.create_time desc
		</if>
		${ex.orderBy}
	</select>


	<resultMap id="BaseResultListPushMap" type="com.zfire.mall.manager.bean.front.CustomerOrderPush">
		<id column="id" jdbcType="VARCHAR" property="id"/>
		<result column="customer_number" jdbcType="VARCHAR" property="customerNumber"/>
		<result column="customer_name" jdbcType="VARCHAR" property="customerName"/>
		<result column="pick_time" jdbcType="VARCHAR" property="pickTime"/>
		<result column="create_time" jdbcType="VARCHAR" property="createTime"/>
		<result column="update_time" jdbcType="VARCHAR" property="updateTime"/>
		<result column="user_name" jdbcType="VARCHAR" property="userName"/>
		<result column="phone" jdbcType="VARCHAR" property="phone"/>
		<result column="province" jdbcType="VARCHAR" property="province"/>
		<result column="city" jdbcType="VARCHAR" property="city"/>
		<result column="area" jdbcType="VARCHAR" property="area"/>
		<result column="street" jdbcType="VARCHAR" property="street"/>
		<result column="rece_address" jdbcType="VARCHAR" property="receAddress"/>
		<result column="orderNotes" jdbcType="VARCHAR" property="orderTime"/>
		<result column="notes" jdbcType="VARCHAR" property="notes"/>
		<collection property="customerOrderItemSyncs" ofType="com.zfire.mall.manager.bean.front.CustomerOrderItemSync" select="goods" column="id">
			<result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber"/>
			<result column="specification" jdbcType="VARCHAR" property="specification"/>
			<result column="material_code" jdbcType="VARCHAR" property="materialCode"/>
			<result column="material_name" jdbcType="VARCHAR" property="materialName"/>
			<result column="qty" jdbcType="INTEGER" property="qty"/>
			<result column="volume" jdbcType="VARCHAR" property="volume"/>
			<result column="total_volume" jdbcType="VARCHAR" property="totalVolume"/>
			<result column="notes" jdbcType="VARCHAR" property="notes"/>
		</collection>
	</resultMap>


	<select id="listPush" resultMap="BaseResultListPushMap">
		SELECT
		co.*
		FROM
		customer_order co
		where 1=1
		and co.stock_type = 1
		and co.status = 2
		and co.sync_status in ('WAIT','FAIL')
		<if test="format != null and format != ''">
			and co.update_time > #{format}
		</if>

	</select>

	<select id="goods" resultType="com.zfire.mall.manager.bean.front.CustomerOrderItemSync">
		SELECT
		coi.*
		FROM
		customer_order_item coi
				where coi.order_id = #{id}

	</select>


	<resultMap id="BaseResultListRefundPushMap" type="com.zfire.mall.manager.bean.front.CustomerOrderRefundPush">
		<id column="id" jdbcType="VARCHAR" property="id"/>
		<result column="customer_number" jdbcType="VARCHAR" property="customerNumber"/>
		<result column="customer_name" jdbcType="VARCHAR" property="customerName"/>
		<result column="pick_time" jdbcType="VARCHAR" property="pickTime"/>
		<result column="order_time" jdbcType="VARCHAR" property="orderTime"/>
		<result column="create_time" jdbcType="VARCHAR" property="createTime"/>
		<result column="update_time" jdbcType="VARCHAR" property="updateTime"/>
		<result column="user_name" jdbcType="VARCHAR" property="userName"/>
		<result column="phone" jdbcType="VARCHAR" property="phone"/>
		<result column="province" jdbcType="VARCHAR" property="province"/>
		<result column="city" jdbcType="VARCHAR" property="city"/>
		<result column="area" jdbcType="VARCHAR" property="area"/>
		<result column="street" jdbcType="VARCHAR" property="street"/>
		<result column="rece_address" jdbcType="VARCHAR" property="receAddress"/>
		<result column="orderNotes" jdbcType="VARCHAR" property="orderTime"/>
		<result column="notes" jdbcType="VARCHAR" property="notes"/>
		<result column="customer_order_no" jdbcType="VARCHAR" property="customerOrderNo"/>
		<collection property="customerOrderRefundItemSyncs" ofType="com.zfire.mall.manager.bean.front.CustomerOrderItemSync" select="goodsRefund" column="id">
			<result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber"/>
			<result column="specification" jdbcType="VARCHAR" property="specification"/>
			<result column="material_code" jdbcType="VARCHAR" property="materialCode"/>
			<result column="material_name" jdbcType="VARCHAR" property="materialName"/>
			<result column="qty" jdbcType="INTEGER" property="qty"/>
			<result column="volume" jdbcType="VARCHAR" property="volume"/>
			<result column="total_volume" jdbcType="VARCHAR" property="totalVolume"/>
			<result column="notes" jdbcType="VARCHAR" property="notes"/>
		</collection>
	</resultMap>


	<select id="orderRefundPush" resultMap="BaseResultListRefundPushMap">
		SELECT
		co.*
		FROM
		customer_order_refund co
		where 1=1
		and co.stock_type = 1
		and co.status = 2
		and co.sync_status in ('WAIT','FAIL')
		<if test="format != null and format != ''">
			and co.update_time > #{format}
		</if>
	</select>

	<select id="goodsRefund" resultType="com.zfire.mall.manager.bean.front.CustomerOrderItemSync">
		SELECT
		coi.*
		FROM
		customer_order_refund_item coi
				where coi.order_id = #{id}

	</select>


	<resultMap id="BaseResultListStockPushMap" type="com.zfire.mall.manager.bean.front.CustomerStockOrderPush">
		<id column="id" jdbcType="VARCHAR" property="id"/>
		<result column="customer_number" jdbcType="VARCHAR" property="customerNumber"/>
		<result column="customer_name" jdbcType="VARCHAR" property="customerName"/>
		<result column="change_time" jdbcType="VARCHAR" property="changeTime"/>
		<result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
		<result column="create_time" jdbcType="VARCHAR" property="createTime"/>
		<result column="update_time" jdbcType="VARCHAR" property="updateTime"/>
		<result column="create_by" jdbcType="VARCHAR" property="createBy"/>
		<result column="remark" jdbcType="VARCHAR" property="remark"/>
		<result column="examine_status" jdbcType="VARCHAR" property="examineStatus"/>
		<result column="approval_name" jdbcType="VARCHAR" property="approvalName"/>
		<result column="approval_time" jdbcType="VARCHAR" property="approvalTime"/>
		<result column="approval_remark" jdbcType="VARCHAR" property="approvalRemark"/>
		<collection property="customerStockOrderItems" ofType="com.zfire.mall.manager.bean.front.CustomerStockOrderItemPush" select="goodsStockAcc" column="id">
			<result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber"/>
			<result column="specification" jdbcType="VARCHAR" property="specification"/>
			<result column="material_code" jdbcType="VARCHAR" property="materialCode"/>
			<result column="material_name" jdbcType="VARCHAR" property="materialName"/>
			<result column="directFlag" jdbcType="BOOLEAN" property="directFlag"/>
			<result column="stockChangeQty" jdbcType="VARCHAR" property="stockChangeQty"/>
			<result column="stockCurQty" jdbcType="VARCHAR" property="stockCurQty"/>
			<result column="notes" jdbcType="VARCHAR" property="notes"/>
			<result column="stockCloseQty" jdbcType="VARCHAR" property="stockCloseQty"/>
			<result column="categoryNumber" jdbcType="VARCHAR" property="categoryNumber"/>
			<result column="categoryName" jdbcType="VARCHAR" property="categoryName"/>
		</collection>
	</resultMap>

	<select id="customerStockAcc" resultMap="BaseResultListStockPushMap">
		SELECT
		co.*
		FROM
		customer_stock_order co
		join customer_stock_order_item coi on co.id = coi.order_id

		where 1=1
		and coi.stock_type = 1
		and co.examine_status = 'OK'
		and co.sync_status in ('WAIT','FAIL')
		<if test="format != null and format != ''">
			and co.update_time > #{format}
		</if>
	</select>

	<select id="goodsStockAcc" resultType="com.zfire.mall.manager.bean.front.CustomerStockOrderItemPush">
		SELECT
		coi.*
		FROM
		customer_stock_order_item coi
				where coi.order_id = #{id}

	</select>
	<select id="listOrder" resultType="com.zfire.mall.manager.bean.front.CustomerOrderRefundOrder">
		SELECT
		co.id,
		co.`status`,
		co.order_time,
		co.pick_time,
		co.customer_id,
		co.customer_name,
		co.customer_number,
		co.user_name,
		co.phone,
		co.province,
		co.city,
		co.area,
		co.street,
		co.rece_address,
		co.jiaxian_order_id,
		co.sync_status,
		co.stock_type,
		coi.material_id,
		coi.material_old_number,
		coi.material_code,
		coi.material_name,
		coi.specification,
		coi.qty,
		coi.refund_qty,
		co.notes,
		co.create_by,
		co.create_time,
		coi.id as item_id,
		cs.front_stock_num,
		cs.customer_stock_num

		FROM
		customer_order co
		JOIN customer_order_item coi ON co.id = coi.order_id
		join customer_stock cs on coi.material_id = cs.material_id and co.customer_id = cs.customer_id

		${ex.query}
		and co.status in (3,4)
		and coi.refund_qty > 0
		<if test="customerId != null and customerId !=''">
			and co.customer_id = #{customerId}
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by co.create_time desc
		</if>
		${ex.orderBy}
	</select>
    <select id="orderTrackList" resultType="com.zfire.mall.manager.bean.front.OrderTrackTocBean">
		SELECT
		*
		FROM
		order_track_toc a
		${ex.query}

		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>

	<update id="updateToc" >
	UPDATE order_track_toc ott
	JOIN customer_order co ON co.jiaxian_order_id = ott.sale_code
	SET ott.order_id = co.id
	</update>

	<update id="updateReturnToc" >
	UPDATE order_track_toc ott
	JOIN customer_order_refund co ON co.jiaxian_order_id = ott.sale_code
	SET ott.order_id = co.id
	</update>
</mapper>

