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

	<!--orderDict简单分页查询-->
	<select id="dimWzdw" resultType="com.emergency.avenue.storage.api.response.DmmatypeResponse">
		SELECT
		id,
		parent_id,
		name_ as matypename3,
		sort_,
		del_flag,
		content_ as specs,
		type_,
		create_by,
		update_by,
		create_time,
		update_time,
		code_ as matypecode3,
		path_ as matypeapth,
	    difference_,
		ORG_YEARS,
		YEARS
		FROM SYS_TREE_DICT
		where del_flag = 0 and type_ = 1 and difference_ = 3 and is_parts=0
		<if test="dmmatypeRequest.matype1 != null and dmmatypeRequest.matype1.size() > 0">
			AND (
				<foreach item="item" collection="dmmatypeRequest.matype1" separator=" or " open="(" close=")" index="">
					path_ like #{item}
				</foreach>
			)
		</if>

		<if test="dmmatypeRequest.matype2 != null and dmmatypeRequest.matype2.size() > 0">
			AND (
			<foreach item="item" collection="dmmatypeRequest.matype2" separator=" or " open="(" close=")" index="">
				path_ like #{item}
			</foreach>
			)
		</if>

		<if test="dmmatypeRequest.matype3 != null and dmmatypeRequest.matype3.trim() != ''">
			<bind name="matype3like" value="'%' + dmmatypeRequest.matype3 + '%'"/>
			AND name_ like #{matype3like}
		</if>
		order by sort_
	</select>

	<!--orderDict简单分页查询-->
	<select id="getAllList" resultType="com.emergency.avenue.storage.api.response.DmmatypeResponse">
		SELECT
		id,
		parent_id,
		name_ as matypename3,
		sort_,
		del_flag,
		content_ as specs,
		type_,
		create_by,
		update_by,
		create_time,
		update_time,
		code_ as matypecode3,
		path_ as matypeapth,
		difference_
		FROM SYS_TREE_DICT
		where del_flag = 0 and type_ = 1 and difference_ = 3
		<if test="dmmatypeRequest.matype1 != null and dmmatypeRequest.matype1.size() > 0">
			AND (
			<foreach item="item" collection="dmmatypeRequest.matype1" separator=" or " open="(" close=")" index="">
				path_ like #{item}
			</foreach>
			)
		</if>

		<if test="dmmatypeRequest.matype2 != null and dmmatypeRequest.matype2.size() > 0">
			AND (
			<foreach item="item" collection="dmmatypeRequest.matype2" separator=" or " open="(" close=")" index="">
				path_ like #{item}
			</foreach>
			)
		</if>

		<if test="dmmatypeRequest.matype3 != null and dmmatypeRequest.matype3.trim() != ''">
			<bind name="matype3like" value="'%' + dmmatypeRequest.matype3 + '%'"/>
			AND name_ like #{matype3like}
		</if>
		order by sort_
	</select>

	<resultMap id="TeacherStudent" type="com.emergency.avenue.storage.api.response.IrkzqXxb1Response">
		<result property="upidName" column="upidName"/>
		<result property="userid" column="userid"/>
		<result property="username" column="username"/>
		<result property="province" column="province"/>
		<collection property="list3" ofType="com.emergency.avenue.storage.api.response.IrkzqXxb3Response">
			<result property="dmUpcode2" column="dmUpcode2"/>
			<result property="dmUpname2" column="dmUpname2"/>
			<result property="tolNum" column="tolNum"/>
			<result property="tolMoney" column="tolMoney"/>
		</collection>
	</resultMap>

	<select id="selectIrkzqXxb" resultMap="TeacherStudent">
		select
		std1.name_ as province,
		sd1.name as upidName,
		ii.dept_id as userid,sd.ABBREVIATION_NAME as username,
		std.code_ as dmUpcode2,std.name_ as dmUpname2,
		sum(imi.total_price) -
		(select  case when sum(omi.total_price) is not null then sum(omi.total_price) else 0 end
		from outbound_info oi
		left join outbound_material_info omi on  omi.outbound_id = oi.outbound_id
		where oi.dept_id = ii.dept_id and omi.material_name = imi.material_name and oi.status in(5,7)
		<if test="irpt.isAvailable !=null and irpt.isAvailable.trim() != ''">
			AND omi.is_available = #{irpt.isAvailable}
		</if>
		 ) as tolMoney,
		sum(imi.quantity)-(select  case when sum(omi.quantity) is not null then sum(omi.quantity) else 0 end
		from outbound_info oi
		left join outbound_material_info omi on  omi.outbound_id = oi.outbound_id
		where oi.dept_id = ii.dept_id and omi.material_name = imi.material_name and oi.status in(5,7)
		<if test="irpt.isAvailable !=null and irpt.isAvailable.trim() != ''">
			AND omi.is_available = #{irpt.isAvailable}
		</if>
		)   as tolNum
		from inbound_info ii
		left join inbound_material_info imi on imi.inbound_id = ii.inbound_id
		left join SYS_DEPT sd on sd.dept_id = ii.dept_id
		left join SYS_TREE_DICT std on std.id = imi.material_name
		left join SYS_DEPT sd1 on sd1.dept_id = sd.PARENT_ID
		left join SYS_TREE_DICT std1 on json_value(sd.DEPT_MERGE_ADDRESS,'$[0]')=std1.id
		where ii.status = 5 and std.is_parts=0
		<if test="irpt.provinceDeptIdList != null and irpt.provinceDeptIdList.size() > 0">
			AND sd1.dept_id in
			<foreach item="item" collection="irpt.provinceDeptIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="irpt.deptIdList != null and irpt.deptIdList.size() > 0">
			AND sd.dept_id in
			<foreach item="item" collection="irpt.deptIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="irpt.dmUpcode0List != null and irpt.dmUpcode0List.size() > 0">
			AND (
			<foreach item="item" collection="irpt.dmUpcode0List" separator=" or " open="(" close=")" index="">
				std.path_ like #{item}
			</foreach>
			)
		</if>

		<if test="irpt.dmUpcode1List != null and irpt.dmUpcode1List.size() > 0">
			AND (
			<foreach item="item" collection="irpt.dmUpcode1List" separator=" or " open="(" close=")" index="">
				std.path_ like #{item}
			</foreach>
			)
		</if>
		<if test="irpt.dmUpname2 != null and irpt.dmUpname2.trim() != ''">
			<bind name="dmUpname2like" value="'%' + irpt.dmUpname2 + '%'"/>
			AND std.name_ like #{dmUpname2like}
		</if>
		<if test="irpt.deptId != null and irpt.deptId.trim() != ''">
			AND ii.dept_id = #{irpt.deptId}
		</if>
		<if test="irpt.provinceSiteIdList != null and irpt.provinceSiteIdList.size() > 0 ">
			AND json_value(sd.DEPT_MERGE_ADDRESS,'$[0]') in
			<foreach item="item" collection="irpt.provinceSiteIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="irpt.deptPermissionsList != null and irpt.deptPermissionsList.size() > 0">
			AND sd.dept_id in
			<foreach item="item" collection="irpt.deptPermissionsList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="irpt.isAvailable !=null and irpt.isAvailable.trim() != ''">
			AND imi.is_available = #{irpt.isAvailable}
		</if>
		group by ii.dept_id,sd.name,std.code_,std.name_
		order by sd1.dept_id,std1.id,ii.dept_id,sd.name,std.code_,std.name_
	</select>
</mapper>
