<?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.jeeplus.pubmodules.wareHouse.mapper.WareHouseBasicMapper">
    <select id="findList" resultType="com.jeeplus.pubmodules.wareHouse.service.dto.WareHouseDto">
		SELECT
			b.id as purchaseId,
			a.id,
			a.create_by_id as createBy,
			a.create_time as createDate,
			a.ware_house_number,
			a.ware_house_name,
			a.ware_house_date,
			a.handled_by,
			a.handled_by_office,
			so.name as handledByOfficeName,
			sy.name as handleByName,
			a.proc_ins_id,
			a.process_definition_id,
			ifnull(a.status,'0') as status,
			art.ID_ as task_id
		FROM
			material_management_warehouse_basics a
			left join material_management_pruchase_request_basics b on a.purchase_no = b.purchase_no and b.del_flag = 0
			left join sys_office so on a.handled_by_office = so.id
			left join sys_user sy on sy.id = a.handled_by
			left join act_ru_task art ON a.proc_ins_id = art.PROC_INST_ID_
			${ew.customSqlSegment}
		ORDER BY a.update_time DESC
    </select>
	<select id="getUserNameByUserId" resultType="java.lang.String">
		select name from sys_user where id = #{id}
	</select>
	<select id="summaryList2"
			resultType="com.jeeplus.pubmodules.wareHouse.service.dto.WareHouseDto">
			SELECT
				a.id,
				a.create_by_id as createBy,
				a.create_time as createDate,
				a.ware_house_type,
				a.trade_name,
				a.company,
				b.NAME AS wareHouseTypeName,
				sum( trade_number ) AS allNumber,
				b.collect_number AS borrowNumber,
				IFNULL((
						sum( trade_number ) - collect_number
						),
				sum( trade_number )) AS tradeNumber,
				b.mmcId as mmcId
			FROM
				material_management_warehouse_detailed a
				LEFT JOIN (
				SELECT
					sum( g.collect_number ) AS collect_number,
					e.goods_name,
					c.NAME as name,
					e.basic_id as mmcId,
				STATUS
				FROM
					material_management_collect_detailed e
					LEFT JOIN material_management_collect_basics f ON e.basic_id = f.id
					AND f.del_flag = 0
					LEFT JOIN material_management_type c ON e.collect_type = c.id
					left join material_management_collect_detailed g on e.goods_name = g.goods_name and g.del_flag = 0
				WHERE
					f.`status` = 5 AND e.del_flag = 0
				GROUP BY
					e.id
				) b ON a.trade_name = b.goods_name
				LEFT JOIN material_management_type c ON a.ware_house_type = c.id
				${ew.customSqlSegment}
				GROUP BY a.trade_name,a.ware_house_type
				HAVING sum( trade_number ) - IFNULL(collect_number,0) &lt;= 5
				ORDER BY a.update_time DESC
	</select>
    <select id="summaryList"
            resultType="com.jeeplus.pubmodules.wareHouse.service.dto.WareHouseDto">
		SELECT
			a.id,
			a.create_by_id as createBy,
			a.create_time as createDate,
			a.ware_house_type,
			a.trade_name,
			a.company,
			c.name as wareHouseTypeName,
			sum(trade_number) as allNumber,
			b.collect_number as borrowNumber,
			IFNULL((sum(trade_number) - collect_number),sum(trade_number)) as tradeNumber,
			a.warn_num,
			a.warn_flag
		FROM
			material_management_warehouse_detailed a
			left join
			(select sum(collect_number) as collect_number,goods_name,status from material_management_collect_detailed e
			left join material_management_collect_basics f on e.basic_id = f.id and f.del_flag = 0
			WHERE f.`status` = 5 and e.del_flag = 0
			GROUP BY e.goods_name) b
			on a.trade_name = b.goods_name

			LEFT JOIN material_management_warehouse_basics bas on a.basic_id = bas.id
			left join material_management_type c on a.ware_house_type = c.id
			${ew.customSqlSegment}
			GROUP BY a.trade_name,a.ware_house_type
			ORDER BY a.ware_house_type DESC,a.trade_name
	</select>
    <select id="findRequestId" resultType="java.lang.String">
		select id from material_management_pruchase_request_basics where purchase_no = #{purchaseNo} and del_flag = 0
	</select>
	<select id="wareHouseHistoryList"
			resultType="com.jeeplus.pubmodules.wareHouse.service.dto.WareHouseDto">
		SELECT
			b.create_time as dateTest,
			a.id as wareHouseId,
			a.ware_house_number,
			a.ware_house_name,
			a.ware_house_date,
			sy.name as wareHouseHandledBy,
			so.name as wareHouseHandledByOff,
			b.trade_name,
			b.trade_number,
			b.current_inventory,
			b.ware_house_type
		FROM
			material_management_warehouse_basics a
			LEFT JOIN
			material_management_warehouse_detailed b on a.id = b.basic_id and b.del_flag = 0

			left join sys_user sy on sy.id = a.handled_by
			left join sys_office so on sy.office_id = so.id

			${ew.customSqlSegment}
		ORDER BY a.create_time DESC
	</select>
	<select id="collectHistoryList"
			resultType="com.jeeplus.pubmodules.wareHouse.service.dto.WareHouseDto">
		SELECT
			b.create_time as dateTest2,
			a.id as collectId,
			a.collect_no,
			sy.name as collectHandleBy,
			so.name as collectHandleByOff,
			a.collect_date,
			b.goods_name,
			b.collect_number,
			b.current_inventory,
			b.collect_type as collectType
		FROM
			material_management_collect_basics a
			LEFT JOIN
			material_management_collect_detailed b on a.id = b.basic_id and b.del_flag = 0

			left join sys_user sy on sy.id = a.handled_by
			left join sys_office so on sy.office_id = so.id

			${ew.customSqlSegment}
		ORDER BY a.update_time DESC
	</select>
	<select id="getByTypeAndGoodsName"
			resultType="com.jeeplus.pubmodules.wareHouse.service.dto.WareHouseDto">
		SELECT
			a.id,
			a.create_by_id as createBy,
			a.create_time as createDate,
			a.ware_house_type,
			a.trade_name,
			a.company,
			c.name as wareHouseTypeName,
			sum(trade_number) as allNumber,
			b.collect_number as borrowNumber,
			IFNULL((sum(trade_number) - collect_number),sum(trade_number)) as tradeNumber
		FROM
			material_management_warehouse_detailed a
			left join
			(select sum(collect_number) as collect_number,goods_name,status from material_management_collect_detailed e
			left join material_management_collect_basics f on e.basic_id = f.id and f.del_flag = 0
			WHERE f.`status` = 5 and e.collect_type = #{collectTypeId}
			GROUP BY e.goods_name) b
			on a.trade_name = b.goods_name

			LEFT JOIN material_management_warehouse_basics bas on a.basic_id = bas.id
			left join material_management_type c on a.ware_house_type = c.id
			where a.trade_name = #{goodsName} and a.ware_house_type = #{collectTypeId} and a.del_flag = 0
			GROUP BY a.trade_name
			ORDER BY a.update_time DESC
	</select>
	<update id="saveUpdateUser">
		update material_management_warehouse_basics set update_user = #{userId} where id = #{basicId}
	</update>
	<select id="getUpdateUser" resultType="string">
		select update_user from material_management_warehouse_basics where id = #{basicId}
	</select>
	<update id="deleteUpdateUser">
		update material_management_warehouse_basics set update_user = null where id = #{basicId}
	</update>
	<select id="getByTypeAndTradeName" resultType="com.jeeplus.pubmodules.wareHouse.service.dto.WareHouseDto">
		select
			warn_num,
			warn_flag
		from material_management_warehouse_detailed
		where trade_name = #{tradeName} and ware_house_type = #{typeId} and del_flag = '0'
	</select>
	<select id="getWareHouseInfoByTime" resultType="java.lang.String">
		SELECT
		IFNULL((sum(trade_number) - collect_number),sum(trade_number)) as tradeNumber
		FROM
		material_management_warehouse_detailed a
		left join material_management_type c on a.ware_house_type = c.id

		left join
		(select sum(collect_number) as collect_number,goods_name,status from material_management_collect_detailed e
		left join material_management_collect_basics f on e.basic_id = f.id and f.del_flag = 0
		WHERE f.`status` = 5 and e.del_flag = 0 and e.create_time &lt;= #{createDate} and e.collect_type = #{wareHouseType} and e.goods_name = #{tradeName}) b
		on a.trade_name = b.goods_name

		WHERE a.ware_house_type = #{wareHouseType}
		and a.del_flag = 0
		and a.trade_name = #{tradeName}
		and a.create_time &lt;= #{createDate}
		GROUP BY a.trade_name,a.ware_house_type
		ORDER BY a.ware_house_type DESC
	</select>
	<select id="getCollectInfoByTime" resultType="java.lang.String">
		SELECT
		IFNULL((sum(trade_number) - collect_number),sum(trade_number)) as tradeNumber
		FROM
		material_management_warehouse_detailed a
		left join material_management_type c on a.ware_house_type = c.id

		left join
		(select sum(collect_number) as collect_number,goods_name,status from material_management_collect_detailed e
		left join material_management_collect_basics f on e.basic_id = f.id and f.del_flag = 0
		WHERE f.`status` = 5 and e.del_flag = 0 and e.create_time &lt;= #{createDate} and e.collect_type = #{wareHouseType} and e.goods_name = #{tradeName}) b
		on a.trade_name = b.goods_name

		WHERE a.ware_house_type = #{wareHouseType}
		and a.del_flag = 0
		and a.trade_name = #{tradeName}
		and a.create_time &lt;= #{createDate}
		GROUP BY a.trade_name,a.ware_house_type
		ORDER BY a.ware_house_type DESC
	</select>
	<select id="getCollectNumByType" resultType="java.lang.String">
		SELECT
		sum( collect_number ) AS collect_number
	FROM
		material_management_collect_detailed e
		LEFT JOIN material_management_collect_basics f ON e.basic_id = f.id
		AND f.del_flag = 0
	WHERE
		f.`status` = 5
		AND e.del_flag = 0
		and e.collect_type = #{wareHouseType} and e.goods_name = #{tradeName}
	</select>
	<update id="updateWarnFlagByTradeNameAndType">
		update material_management_warehouse_detailed
		set warn_flag = #{warnFlag}
		where trade_name = #{tradeName} and ware_house_type = #{typeId}
	</update>
	<update id="updateWarnNumByTradeNameAndType">
		update material_management_warehouse_detailed
		set warn_num = #{warnNum}
		where trade_name = #{tradeName} and ware_house_type = #{typeId}
	</update>
	<update id="clearUpdateCause">
		update material_management_warehouse_basics set update_cause = null where id = #{id}
	</update>
</mapper>
