<?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.StockCostMapper">


	<select id="listFullData" resultType="com.zfire.mall.manager.bean.stock.OrderFullDateVO">
		SELECT
		a.order_full_data_id,
		a.year_time,
		a.month_time,
		a.order_type,
		a.order_no,
		a.order_time,
		a.examine_time,
		a.customer_number,
		a.customer_id,
		a.customer_name,
		a.ref_use_unit,
		a.material_id,
		a.material_old_number,
		a.material_code,
		a.material_name,
		a.specification,
		a.order_num,
		a.pay_amount,
		a.customer_wallet_id,
		a.customer_wallet_name,
		a.customer_wallet_number,


		DATE_FORMAT(a.full_start_time, '%Y-%m-%d') as full_start_time,
		DATE_FORMAT(a.full_end_time, '%Y-%m-%d') as full_end_time,

		a.full_num,
		a.full_cost,
		a.create_by,
		a.create_time,
		a.update_by,
		a.update_time,

		a.k3_category_id,
		a.k3_category_number,
		a.k3_category_name
		FROM
		order_full_date a
		join customer_product_type_rela cptr on a.customer_wallet_id = cptr.customer_wallet_id
		${ex.query}
		<if test="customerId != null and customerId != ''">
			and a.customer_id = #{customerId}
		</if>

		<if test="serviceIds != null and serviceIds.size > 0">
			and cptr.service_id in
			<foreach item="item" index="index" collection="serviceIds" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>

		<if test="k3CategoryIds != null and k3CategoryIds.size > 0">
			and a.k3_category_id in
			<foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>

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

		${ex.orderBy}
	</select>

    <select id="list" resultType="com.zfire.mall.manager.bean.stock.OrderFullCostVO">
		SELECT
		s.*
		FROM
		(
        SELECT
        	b.material_name,
        	b.material_code,
        	b.retail_order_id AS orderId,
        	b.material_old_number,
        	b.material_id,
        	b.specification,
        	b.full_num,
			DATE_FORMAT(b.full_time, '%Y-%m-%d') as full_time,
        	IF
        	( b.full_num > 5, 'YES', 'NO' ) AS full
        FROM
        	retail_order_item b
        	JOIN retail_order a ON a.id = b.retail_order_id
        WHERE
        	b.full_status = 'YES'
        and	b.refundable_qty > 0
		<if test="materialName != null and materialName != ''">
			and b.material_name = #{materialName}
		</if>
		<if test="orderId != null and orderId != ''">
			and a.id = #{orderId}
		</if>
		<if test="materialNumber != null and materialNumber != ''">
			and b.materialNumber = #{materialNumber}
		</if>
		<if test="specification != null and specification != ''">
			and b.specification = #{specification}
		</if>
		<if test="startTime != null and startTime != ''">
			and b.full_time BETWEEN  #{startTime} and #{endTime}
		</if>
		<if test="materialOldNumber != null and materialOldNumber != ''">
			and b.material_old_number = #{materialOldNumber}
		</if>
		<if test="customerId != null and customerId != ''">
			and a.customer_id = #{customerId}
		</if>
        UNION
        SELECT
        	b.material_name,
        	b.material_number AS material_code,
        	b.engin_order_no AS orderId,
        	b.material_old_number,
        	b.material_id,
        	b.specification,
        	b.full_num,
			DATE_FORMAT(b.full_time, '%Y-%m-%d') as full_time,
		IF
        	( b.full_num > 5, 'YES', 'NO' ) AS FULL
        FROM
        	engin_order_item b
        	JOIN engin_order a ON b.engin_order_no = a.engin_order_no
        WHERE
        	b.full_status = 'YES'
		and b.refundable_qty > 0
		<if test="materialName != null and materialName != ''">
			and b.material_name = #{materialName}
		</if>
		<if test="orderId != null and orderId != ''">
			and a.engin_order_no = #{orderId}
		</if>
		<if test="materialNumber != null and materialNumber != ''">
			and b.materialNumber = #{materialNumber}
		</if>
		<if test="specification != null and specification != ''">
			and b.specification = #{specification}
		</if>
		<if test="startTime != null and startTime != ''">
			and b.full_time BETWEEN  #{startTime} and #{endTime}
		</if>
		<if test="materialOldNumber != null and materialOldNumber != ''">
			and b.material_old_number = #{materialOldNumber}
		</if>
		<if test="customerId != null and customerId != ''">
			and a.customer_id = #{customerId}
		</if>
		) s
		ORDER BY
		s.full_num DESC
    </select>
	<select id="listFullCost" resultType="com.zfire.mall.manager.bean.stock.OrderFullCostDayVO">
		SELECT
		a.order_full_cost_id,
		a.year_time,
		a.month_time,
		a.status,
		a.order_type,
		a.order_no,
		a.order_time,
		a.examine_time,
		a.customer_number,
		a.customer_id,
		a.customer_name,
		a.ref_use_unit,
		a.material_id,
		a.material_old_number,
		a.material_code,
		a.material_name,
		a.specification,
		a.order_num,
		a.pay_amount,
		a.customer_wallet_id,
		a.customer_wallet_name,
		a.customer_wallet_number,
		a.overdue_num,


		DATE_FORMAT(a.full_start_time, '%Y-%m-%d') as full_start_time,
		DATE_FORMAT(a.full_end_time, '%Y-%m-%d') as full_end_time,

		a.full_num,
		a.full_cost,
		a.create_by,
		a.create_time,
		a.update_by,
		a.update_time,
		a.note,
		a.k3_category_id,
		a.k3_category_number,
		a.k3_category_name
		FROM
		order_full_cost a
		join customer_product_type_rela cptr on a.customer_wallet_id = cptr.customer_wallet_id
		${ex.query}
		<if test="customerId != null and customerId != ''">
			and a.customer_id = #{customerId}
		</if>

		<if test="serviceIds != null and serviceIds.size > 0">
			and cptr.service_id in
			<foreach item="item" index="index" collection="serviceIds" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>

		<if test="k3CategoryIds != null and k3CategoryIds.size > 0">
			and a.k3_category_id in
			<foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>


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

		${ex.orderBy}
	</select>
    <select id="listMonthFullCost" resultType="com.zfire.mall.manager.bean.stock.OrderFullCostMonthVO">
		SELECT
			ofc.year_time,
			ofc.month_time,
			ofc.customer_number,
			ofc.customer_id,
			ofc.customer_name
		FROM
			order_full_cost ofc
		join customer_product_type_rela cptr on ofc.customer_wallet_id = cptr.customer_wallet_id
		${ex.query}
		<if test="customerId != null and customerId != ''">
			and ofc.customer_id = #{customerId}
		</if>
		<if test="serviceIds != null and serviceIds.size > 0">
			and cptr.service_id in
			<foreach item="item" index="index" collection="serviceIds" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>

		<if test="k3CategoryIds != null and k3CategoryIds.size > 0">
			and ofc.k3_category_id in
			<foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>

		GROUP BY
			ofc.year_time,
			ofc.month_time,
			ofc.customer_id

		<if test="ex.orderBy == null or ex.orderBy ==''">
			order by ofc.year_time,ofc.month_time desc
		</if>
		${ex.orderBy}
	</select>
</mapper>