<?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.OutboundMapper">
	<sql id = "selectListByDeptWhere">
		<if test="deptIdList != null and deptIdList.size() > 0">
			AND oi.dept_id in
			<foreach item="item" collection="deptIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="warehouseDeptId != null and warehouseDeptId.size() > 0">
			AND oi.dept_id in
			<foreach item="item" collection="warehouseDeptId" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>

		<if test="dmUpcode0List != null and dmUpcode0List.size() > 0">
			AND std1.code_ in
			<foreach item="item" collection="dmUpcode0List" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="dmUpcode1List != null and dmUpcode1List.size() > 0">
			AND std2.code_ in
			<foreach item="item" collection="dmUpcode1List" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="dmUpname2 != null and dmUpname2.trim() != ''">
			<bind name="likedmUpname2" value="'%' + dmUpname2 + '%'"/>
			AND std3.name_ like #{likedmUpname2}
		</if>
		<if test="provinceSiteIdList != null and provinceSiteIdList.size() >0 ">
			AND  omi.province in
			<foreach item="item" collection="provinceSiteIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="outboundTime != null and outboundTime.size() >0  ">
		    AND  omi.outbound_time between #{outboundTime[0]} and  #{outboundTime[1]}
		</if>
	</sql>
	<select id="selectDistributeListByMaterials"
			resultType="com.emergency.avenue.storage.api.response.OutbountInfoResponse">
		select
		omi.province,
		sd3.dept_id as warehouseDeptId,
		sd3.ABBREVIATION_NAME as warehouseName,
		std1.name_ as materials1,
		std2.name_ as materials2,
		std3.name_ as materials3,
		std4.name_ as materials4,
		omi.quantity as quantity,
		omi.outbound_time as outboundTime,
		omi.unit_price as unitPrice,
		omi.total_price as totalPrice
		from
		outbound_material_info omi
		left join outbound_info oi on oi.outbound_id = omi.outbound_id
		left join SYS_TREE_DICT std1 on std1.id = omi.material_group
		left join SYS_TREE_DICT std2 on std2.id = omi.material_type
		left join SYS_TREE_DICT std3 on std3.id = omi.material_name
		left join SYS_TREE_DICT std4 on std4.id = omi.unit
		left join SYS_DEPT sd3 on sd3.dept_id = oi.dept_id
		where oi.status='7'
		<include refid="selectListByDeptWhere"></include>
		/*group by omi.material_name,omi.province*/
		order by omi.material_name,omi.province
	</select>
	<select id="selectDistributeListByProvince"
			resultType="com.emergency.avenue.storage.api.response.OutbountInfoResponse">
	        select
	        omi.province,
			sd3.dept_id as warehouseDeptId,
			sd3.ABBREVIATION_NAME as warehouseName,
			std1.name_ as materials1,
			std2.name_ as materials2,
			std3.name_ as materials3,
			std4.name_ as materials4,
			omi.quantity as quantity,
			omi.specs as specs,
			omi.outbound_time as outboundTime,
			omi.inbound_time as inboundTime,
			omi.unit_price as unitPrice,
		    1 as sort,
			omi.total_price as totalPrice
		 from
		 outbound_material_info omi
		 left join outbound_info oi on oi.outbound_id = omi.outbound_id
		 left join SYS_TREE_DICT std1 on std1.id = omi.material_group
		 left join SYS_TREE_DICT std2 on std2.id = omi.material_type
		 left join SYS_TREE_DICT std3 on std3.id = omi.material_name
		 left join SYS_TREE_DICT std4 on std4.id = omi.unit
		 left join SYS_DEPT sd3 on sd3.dept_id = oi.dept_id
		where oi.status='7'
		<include refid="selectListByDeptWhere"></include>
		/*group by omi.province,omi.material_name*/
	</select>
	<select id="selectDistributeListByProvincez"
			resultType="com.emergency.avenue.storage.api.response.OutbountInfoResponse">
	        select
	        omi.province,
	        2 as sort,
	        '小计' materials3,
			sum(omi.total_price) as totalPrice
			 from
			 outbound_material_info omi
			left join outbound_info oi on oi.outbound_id = omi.outbound_id
			left join SYS_TREE_DICT std1 on std1.id = omi.material_group
			left join SYS_TREE_DICT std2 on std2.id = omi.material_type
			left join SYS_TREE_DICT std3 on std3.id = omi.material_name
			left join SYS_TREE_DICT std4 on std4.id = omi.unit
			left join SYS_DEPT sd3 on sd3.dept_id = oi.dept_id
			where oi.status='7'
		    <include refid="selectListByDeptWhere"></include>
			group by omi.province
	</select>
	<select id="selectDistributeListByWarehousehz"
			resultType="com.emergency.avenue.storage.api.response.OutbountInfoResponse">
             select
	        sd3.dept_id as warehouseDeptId,
	        2 as sort,
	        '小计' materials3,
			sd3.ABBREVIATION_NAME as warehouseName,
			sum(omi.total_price) as totalPrice
			 from
			 outbound_material_info omi
			left join outbound_info oi on oi.outbound_id = omi.outbound_id
			left join SYS_TREE_DICT std1 on std1.id = omi.material_group
			left join SYS_TREE_DICT std2 on std2.id = omi.material_type
			left join SYS_TREE_DICT std3 on std3.id = omi.material_name
			left join SYS_TREE_DICT std4 on std4.id = omi.unit
			left join SYS_DEPT sd3 on sd3.dept_id = oi.dept_id
			where oi.status='7'
		    <include refid="selectListByDeptWhere"></include>
			group by oi.dept_id
	</select>
	<select id="selectDistributeListByOutboundTimeHz"
			resultType="com.emergency.avenue.storage.api.response.OutbountInfoResponse">
 			select
 			omi.outbound_time,
	        omi.province,
	        2 as sort,
			sum(omi.total_price) as totalPrice
			 from
			 outbound_material_info omi
			left join outbound_info oi on oi.outbound_id = omi.outbound_id
			left join SYS_TREE_DICT std1 on std1.id = omi.material_group
			left join SYS_TREE_DICT std2 on std2.id = omi.material_type
			left join SYS_TREE_DICT std3 on std3.id = omi.material_name
			left join SYS_TREE_DICT std4 on std4.id = omi.unit
			left join SYS_DEPT sd3 on sd3.dept_id = oi.dept_id
			where oi.status='7'
		   <include refid="selectListByDeptWhere"></include>
			group by omi.outbound_time, omi.province
	</select>
</mapper>
