<?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.emergency.avenue.storage.mapper.TransferOrderMapper">
    <select id="getTransferOrderPage" resultType="com.emergency.avenue.storage.api.vo.TransferOrderVo">
    select
	tor.order_id,tor.order_no,tor.order_name,tor.remarks,tor.submit_time,tor.file_part,tor.file_name,tor.status,
	LISTAGG(DISTINCT oi.material_name, ',') WITHIN GROUP (ORDER BY oi.material_name) AS material_name,
	sum(oi.quantity) as quantity,
	oi.outbound_type,
	LISTAGG(DISTINCT oi.warehouse, ',') WITHIN GROUP (ORDER BY oi.warehouse) AS warehouse,
	LISTAGG(DISTINCT oi.called_province, ',') WITHIN GROUP (ORDER BY oi.called_province) AS province
	from
			transfer_order tor
	left join order_outbound oo
	on tor.order_id=oo.order_id
	left join outbound_info oi on oo.outbound_id=oi.outbound_id
	<where>
		<if test="tor.province!=null and tor.province!=''">
          and oi.called_province=#{tor.province}
		</if>
		<if test="tor.outboundType!=null and tor.outboundType!=''">
			and oi.outbound_type=#{tor.outboundType}
		</if>
		<if test="tor.orderName!=null and tor.orderName!=''">
			<bind name="orderName2" value="'%' + tor.orderName + '%'"/>
			and tor.order_name=#{orderName2}
		</if>
		<if test="tor.deptId!=null and tor.deptId!=''">
			and oi.dept_id=#{tor.deptId}
		</if>
		<if test="tor.materialNames != null and tor.materialNames.size() >0 ">
			AND oi.material_name in
			<foreach item="item" collection="tor.materialNames" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="tor.deptIds != null and tor.deptIds.size() >0 ">
			AND oi.dept_id in
			<foreach item="item" collection="tor.deptIds" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
	</where>
	group by tor.order_id
	order by tor.status desc
	</select>
	<select id="getOrderOutboundMaterialInfo"
			resultType="com.emergency.avenue.storage.api.entity.OrderOutboundMaterialInfo">
	select
			omi.material_id   ,
			omi.outbound_id   ,
			 omi.outbound_type ,
			omi.material_group,
			 omi.material_type ,
			omi.material_name ,
			omi.unit          ,
			omi.quantity      ,
			 omi.province      ,
			 omi.specs         ,
			omi.outbound_time ,
			omi.unit_price    ,
			 omi.total_price   ,
			 omi.is_available  ,
			 omi.file_part     ,
			 omi.remarks       ,
			 omi.material_tree  as materialTree,
			 omi.file_name     ,
			omi.inbound_time,
			oi.dept_id,
			oi.warehouse
	from
	outbound_material_info omi left join outbound_info oi on omi.outbound_id=oi.outbound_id
      where
		 oi.outbound_id in
		<foreach item="item" collection="outbound" separator="," open="(" close=")" index="">
			#{item}
		</foreach>


	</select>
</mapper>
