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

	<update id="updateByIdAndIsInvalid">
		update inbound_status set is_invalid=1 where inbound_id=#{id}
	</update>

	<sql id = "selectListByDeptWhere">
		<if test="infoRequest.deptIdList != null and infoRequest.deptIdList.size() > 0">
			AND ii.dept_id in
			<foreach item="item" collection="infoRequest.deptIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.gameDeptId != null and infoRequest.gameDeptId.size() > 0">
			AND sd2.dept_id in
			<foreach item="item" collection="infoRequest.gameDeptId" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.warehouseDeptId != null and infoRequest.warehouseDeptId.size() > 0">
			AND ii.dept_id in
			<foreach item="item" collection="infoRequest.warehouseDeptId" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>

		<if test="infoRequest.dmUpcode0List != null and infoRequest.dmUpcode0List.size() > 0">
			AND std1.code_ in
			<foreach item="item" collection="infoRequest.dmUpcode0List" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>

		<if test="infoRequest.dmUpcode1List != null and infoRequest.dmUpcode1List.size() > 0">
			AND std2.code_ in
			<foreach item="item" collection="infoRequest.dmUpcode1List" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.dmUpname2 != null and infoRequest.dmUpname2.trim() != ''">
			<bind name="likedmUpname2" value="'%' + infoRequest.dmUpname2 + '%'"/>
			AND std3.name_ like #{likedmUpname2}
		</if>
		<if test="infoRequest.provinceSiteIdList != null and infoRequest.provinceSiteIdList.size() >0 ">
			AND json_value(sd3.DEPT_MERGE_ADDRESS,'$[0]') in
			<foreach item="item" collection="infoRequest.provinceSiteIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.storageState !=null and infoRequest.storageState.trim() !='' ">
			and REGEXP_REPLACE(std3.org_years,'[^0-9]+')
			  <if test="infoRequest.storageState=='1'.toString()">
				  and CONCAT(year(now())-2,'-',12,'-',31) &gt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
			  </if>
			<if test="infoRequest.storageState=='2'.toString() ">
				and REGEXP_REPLACE(std3.org_years,'[^0-9]+')/ (CASE
				WHEN TIMESTAMPDIFF(YEAR,TO_DATE(imi.inbound_time, 'YYYY-MM-DD'),now())=0 THEN 1
				ELSE TIMESTAMPDIFF(YEAR,TO_DATE(imi.inbound_time, 'YYYY-MM-DD'),now()) end)
				&lt;= 2
			</if>
			<if test="infoRequest.storageState=='3'.toString() ">
				and CONCAT(year(now())-2,'-',12,'-',31) &lt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
				and CONCAT(year(now())-1,'-',12,'-',31) &gt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
			</if>
			<if test="infoRequest.storageState=='4'.toString() ">
				and CONCAT(year(now())-1,'-',12,'-',31) &lt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
				and CONCAT(year(now()),'-',12,'-',31) &gt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
			</if>
			<if test="infoRequest.storageState=='5'.toString() ">
				and CONCAT(year(now()),'-',12,'-',31) &lt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
				and CONCAT(year(now())+1,'-',12,'-',31) &gt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
			</if>
			<if test="infoRequest.storageState=='6'.toString()">
				and CONCAT(year(now())+1,'-',12,'-',31) &lt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
				and CONCAT(year(now())+2,'-',12,'-',31) &gt;= ADD_MONTHS(imi.inbound_time,REGEXP_REPLACE(std3.org_years, '[^0-9]+')*12 )
			</if>
		</if>
	</sql>

	<!--orderDict简单分页查询-->
	<select id="selectListByDept" resultType="com.emergency.avenue.storage.api.response.InboundInfoResponse">
		select
		DISTINCT
		std5.name_ as province,
		sd2.dept_id as gameDeptId,
		sd2.ABBREVIATION_NAME as gameName,
		sd3.dept_id as warehouseDeptId,
		sd3.ABBREVIATION_NAME as warehouseName,
		std1.name_ as materials1,
		std2.name_ as materials2,
		std3.code_ as dmUpcode2,
		std3.name_ as materials3,
		std4.name_ as materials4,
		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)
		and omi.inbound_time=imi.inbound_time and omi.unit_price=imi.unit_price and  omi.is_available=imi.is_available
		) as quantity,
		imi.specs as specs,
		imi.inbound_time as inboundTime,
		imi.unit_price as unitPrice,
		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)
		and omi.inbound_time=imi.inbound_time and omi.unit_price=imi.unit_price and omi.is_available=imi.is_available )
		as totalPrice,
		imi.is_available as isAvailable,
		imi.remarks as remarks,
		0 as warehouseTotalPrice,
		2 as sort,
		std3.org_years as years,
		std3.is_parts as isParts
		from inbound_material_info imi
		left join inbound_info ii on ii.inbound_id = imi.inbound_id
		left join SYS_TREE_DICT std1 on std1.id = imi.material_group
		left join SYS_TREE_DICT std2 on std2.id = imi.material_type
		left join SYS_TREE_DICT std3 on std3.id = imi.material_name
		left join SYS_TREE_DICT std4 on std4.id = imi.unit
		left join SYS_DEPT sd3 on sd3.dept_id = ii.dept_id
		left join SYS_DEPT sd2 on sd2.dept_id = sd3.parent_id
		left join SYS_TREE_DICT std5 on sd3.DEPT_PROVINCE=std5.id
		where ii.status = 5
		<include refid="selectListByDeptWhere"></include>
	</select>

	<select id="selectListByDeptAndMaintenance" resultType="com.emergency.avenue.storage.api.response.InboundInfoResponse">
		select
		DISTINCT
		std5.name_ as province,
		sd2.dept_id as gameDeptId,
		sd2.ABBREVIATION_NAME as gameName,
		sd3.dept_id as warehouseDeptId,
		sd3.ABBREVIATION_NAME as warehouseName,
		std1.name_ as materials1,
		std2.name_ as materials2,
		std3.code_ as dmUpcode2,
		std3.name_ as materials3,
		std4.name_ as materials4,
		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)
		and omi.inbound_time=imi.inbound_time and omi.unit_price=imi.unit_price and  omi.is_available=imi.is_available
		) as quantity,
		imi.specs as specs,
		imi.inbound_time as inboundTime,
		imi.unit_price as unitPrice,
		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)
		and omi.inbound_time=imi.inbound_time and omi.unit_price=imi.unit_price and omi.is_available=imi.is_available )
		as totalPrice,
		imi.is_available as isAvailable,
		imi.remarks as remarks,
		0 as warehouseTotalPrice,
		2 as sort,
		std3.org_years as years,
		mm.counts as maintenanceCount,
		mm.id as maintenanceId,
		std3.is_parts as isParts
		from inbound_material_info imi
		left join inbound_info ii on ii.inbound_id = imi.inbound_id
		left join SYS_TREE_DICT std1 on std1.id = imi.material_group
		left join SYS_TREE_DICT std2 on std2.id = imi.material_type
		left join SYS_TREE_DICT std3 on std3.id = imi.material_name
		left join SYS_TREE_DICT std4 on std4.id = imi.unit
		left join SYS_DEPT sd3 on sd3.dept_id = ii.dept_id
		left join SYS_DEPT sd2 on sd2.dept_id = sd3.parent_id
		left join SYS_TREE_DICT std5 on sd3.DEPT_PROVINCE=std5.id
		left join (select mm.*,count(*) as counts from
		material_maintenance mm
		left join material_maintenance_detail mmd on mm.id=mmd.maintenance_id
		where mm.status=5 group by mmd.maintenance_id) mm on
		mm.dept_id      =ii.dept_id
		and mm.material_name=imi.material_name
		and imi.inbound_time=mm.entry_date
		and mm.unit_price=imi.unit_price
		where ii.status = 5
		<include refid="selectListByDeptWhere"></include>
		<if test="infoRequest.maintenanceCount !=null and infoRequest.maintenanceCount!=''">
			and mm.counts=#{infoRequest.maintenanceCount}
		</if>
	</select>
	<!--orderDict简单分页查询-->
	<select id="selectListByDeptMoney" resultType="com.emergency.avenue.storage.api.response.InboundInfoResponse">
		select
		std5.name_ as province,
		sd2.dept_id as gameDeptId,
		sd2.ABBREVIATION_NAME as gameName,
		sd3.dept_id as warehouseDeptId,
		sd3.ABBREVIATION_NAME as warehouseName,
		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)
		  ) as warehouseTotalPrice,
		1 as sort

		from inbound_material_info imi
		left join inbound_info ii on ii.inbound_id = imi.inbound_id
		left join SYS_TREE_DICT std1 on std1.id = imi.material_group
		left join SYS_TREE_DICT std2 on std2.id = imi.material_type
		left join SYS_TREE_DICT std3 on std3.id = imi.material_name
		left join SYS_TREE_DICT std4 on std4.id = imi.unit
		left join SYS_DEPT sd3 on sd3.dept_id = ii.dept_id
		left join SYS_DEPT sd2 on sd2.dept_id = sd3.parent_id
		left join SYS_TREE_DICT std5 on sd3.DEPT_PROVINCE=std5.id
		where ii.status = 5
			<include refid="selectListByDeptWhere"></include>
		group by sd2.dept_id,
		sd2.ABBREVIATION_NAME,
		sd3.dept_id,
		sd3.ABBREVIATION_NAME
	</select>
	<select id="selectListByStorageMaterials"
			resultType="com.emergency.avenue.storage.api.response.StorageMaterialsResponse">
		select dept_id,province,warehouse,
		sum(quantity22) as quantity22,
		sum(totalPrice22) as totalPrice22,
		sum(quantity23) as quantity23,
		sum(totalPrice23) as totalPrice23,
		sum(quantity24) as quantity24,
		sum(totalPrice24) as totalPrice24,
		sum(quantity25) as quantity25,
		sum(totalPrice25) as totalPrice25,
		sum(quantity26) as quantity26,
		sum(totalPrice26) as totalPrice26,
		sum(quantitygb) as quantitygb,
		sum(totalPricegb) as totalPricegb
		from (select
		inbound.dept_id,
		inbound.province,
		inbound.warehouse,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity22,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice22,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now())-1,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity23,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31)&lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now())-1,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice23,
		CASE WHEN
		CONCAT(year(now())-1,'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity24,
		CASE WHEN
		CONCAT(year(now())-1,'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice24,
		CASE WHEN
		CONCAT(year(now()),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+1),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity25,
		CASE WHEN
		CONCAT(year(now()),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+1),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice25,
		CASE WHEN
		CONCAT(year(now()+1),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+2),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity26,
		CASE WHEN
		CONCAT(year(now()+1),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+2),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice26,
		CASE WHEN  REGEXP_REPLACE(inbound.org_years,'[^0-9]+')/ (CASE
		WHEN TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now())=0 THEN 1
		ELSE TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now()) end)
		&lt;= 2  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantitygb,
		CASE WHEN
		REGEXP_REPLACE(inbound.org_years,'[^0-9]+')/ (CASE
		WHEN TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now())=0 THEN 1
		ELSE TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now()) end)
		&lt;= 2 THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPricegb
		from (select
		sd.DEPT_PROVINCE as province,
		sd.ABBREVIATION_NAME as warehouse,
		std.name_ as materialsName,
		std1.code_,
		imi.quantity as quantity,
		imi.specs as specs,
		imi.total_price,
		imi.is_available as isAvailable,
		imi.remarks as remarks,
		std.org_years,
		std.is_parts as isParts,
		ii.dept_id,
		imi.material_name,
		imi.unit_price,
		imi.is_available,
		imi.inbound_time
		from inbound_material_info imi
		left join inbound_info ii on ii.inbound_id = imi.inbound_id
		left join SYS_TREE_DICT std1 on std1.id = imi.material_group
		left join SYS_DEPT sd on sd.dept_id = ii.dept_id
		left join SYS_TREE_DICT std on std.id = imi.material_name where ii.status = 5
		and REGEXP_REPLACE(std.org_years,'[^0-9]+')
		) inbound
		left join
		(select
		oi.dept_id,
		omi.material_name,
		omi.unit_price,
		omi.is_available,
		omi.inbound_time,
		omi.quantity,
		omi.unit_price as unit_price1,
		omi.total_price
		from outbound_info oi
		left join outbound_material_info omi
		on omi.outbound_id = oi.outbound_id where oi.status in(5,7))outbound on
		outbound.dept_id = inbound.dept_id
		and outbound.material_name = inbound.material_name
		and outbound.inbound_time=inbound.inbound_time
		and outbound.unit_price1 = inbound.unit_price
		and outbound.is_available=inbound.is_available
		<where>
			<include refid="selectStorageMaterialsWhere"></include>
		</where>
		) as datas group by datas.dept_id order by datas.province
	</select>
	<select id="selectListByStorageMaterialsDeptId"
			resultType="com.emergency.avenue.storage.api.response.StorageMaterialsResponse">
		select dept_id,province,warehouse, materialsName,
		sum(quantity22) as quantity22,
		sum(totalPrice22) as totalPrice22,
		sum(quantity23) as quantity23,
		sum(totalPrice23) as totalPrice23,
		sum(quantity24) as quantity24,
		sum(totalPrice24) as totalPrice24,
		sum(quantity25) as quantity25,
		sum(totalPrice25) as totalPrice25,
		sum(quantity26) as quantity26,
		sum(totalPrice26) as totalPrice26,
		sum(quantitygb) as quantitygb,
		sum(totalPricegb) as totalPricegb
		from (select
		inbound.dept_id,
		inbound.province,
		inbound.warehouse,
		inbound.materialsName,
		inbound.material_name,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity22,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice22,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now())-1,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity23,
		CASE WHEN
		CONCAT(year(now())-2,'-',12,'-',31)&lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now())-1,'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice23,
		CASE WHEN
		CONCAT(year(now())-1,'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity24,
		CASE WHEN
		CONCAT(year(now())-1,'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice24,
		CASE WHEN
		CONCAT(year(now()),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+1),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity25,
		CASE WHEN
		CONCAT(year(now()),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+1),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice25,
		CASE WHEN
		CONCAT(year(now()+1),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+2),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantity26,
		CASE WHEN
		CONCAT(year(now()+1),'-',12,'-',31) &lt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 )
		and CONCAT(year(now()+2),'-',12,'-',31) &gt;= ADD_MONTHS(inbound.inbound_time,REGEXP_REPLACE(inbound.org_years, '[^0-9]+')*12 ) THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPrice26,
		CASE WHEN  REGEXP_REPLACE(inbound.org_years,'[^0-9]+')/ (CASE
		WHEN TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now())=0 THEN 1
		ELSE TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now()) end)
		&lt;= 2  THEN inbound.quantity-COALESCE(outbound.quantity,0)
		ELSE 0 END as quantitygb,
		CASE WHEN
		REGEXP_REPLACE(inbound.org_years,'[^0-9]+')/ (CASE
		WHEN TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now())=0 THEN 1
		ELSE TIMESTAMPDIFF(YEAR,TO_DATE(inbound.inbound_time, 'YYYY-MM-DD'),now()) end)
		&lt;= 2 THEN inbound.total_price-COALESCE(outbound.total_price,0)
		ELSE 0 END as totalPricegb
		from (select
		sd.DEPT_PROVINCE as province,
		sd.ABBREVIATION_NAME as warehouse,
		std.name_ as materialsName,
		std1.code_,
		imi.quantity as quantity,
		imi.specs as specs,
		imi.total_price,
		imi.is_available as isAvailable,
		imi.remarks as remarks,
		std.org_years,
		std.is_parts as isParts,
		ii.dept_id,
		imi.material_name,
		imi.unit_price,
		imi.is_available,
		imi.inbound_time
		from inbound_material_info imi
		left join inbound_info ii on ii.inbound_id = imi.inbound_id
		left join SYS_TREE_DICT std1 on std1.id = imi.material_group
		left join SYS_DEPT sd on sd.dept_id = ii.dept_id
		left join SYS_TREE_DICT std on std.id = imi.material_name where ii.status = 5
		and REGEXP_REPLACE(std.org_years,'[^0-9]+')
		) inbound
		left join
		(select
		oi.dept_id,
		omi.material_name,
		omi.unit_price,
		omi.is_available,
		omi.inbound_time,
		omi.quantity,
		omi.unit_price as unit_price1,
		omi.total_price
		from outbound_info oi
		left join outbound_material_info omi
		on omi.outbound_id = oi.outbound_id where oi.status in(5,7))outbound on
		outbound.dept_id = inbound.dept_id
		and outbound.material_name = inbound.material_name
		and outbound.inbound_time=inbound.inbound_time
		and outbound.unit_price1 = inbound.unit_price
		and outbound.is_available=inbound.is_available
		<where>
			<include refid="selectStorageMaterialsWhere"></include>
		</where>
		) as datas group by datas.dept_id,datas.material_name order by datas.province
	</select>

	<sql id="selectStorageMaterialsWhere">
		<if test="infoRequest.deptIdList != null and infoRequest.deptIdList.size() > 0">
			AND inbound.dept_id in
			<foreach item="item" collection="infoRequest.deptIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.warehouseDeptId != null and infoRequest.warehouseDeptId.size() > 0">
			AND inbound.dept_id in
			<foreach item="item" collection="infoRequest.warehouseDeptId" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.provinceSiteIdList != null and infoRequest.provinceSiteIdList.size() >0 ">
			AND  inbound.province in
			<foreach item="item" collection="infoRequest.provinceSiteIdList" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.dmUpcode0List != null and infoRequest.dmUpcode0List.size() > 0">
			AND inbound.code_ in
			<foreach item="item" collection="infoRequest.dmUpcode0List" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="infoRequest.dmUpname2 != null and infoRequest.dmUpname2.trim() != ''">
			<bind name="likedmUpname2" value="'%' + infoRequest.dmUpname2 + '%'"/>
			AND inbound.materialsName like #{likedmUpname2}
		</if>

		<if test="infoRequest.deptId != null and infoRequest.deptId.trim() != ''">
			AND inbound.dept_id = #{infoRequest.deptId}
		</if>
	</sql>
</mapper>
