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

	<update id="updatebyIdAndisInvalid">
		update material_maintenance_status set is_invalid=1 where maintenance_id=#{id}
	</update>
	<sql id = "getStatisticsWhere">
       <if test="deptId !=null and deptId!=''">
		   AND mm.dept_id=#{deptId}
	   </if>
		<if test="materialType !=null and materialType!=''">
			AND mm.material_type=#{materialType}
		</if>
		<if test="materialName !=null and materialName!=''">
			AND mm.material_Name=#{materialName}
		</if>
		<if test="startEntryDate !=null and endEntryDate!=null">
			and  mm.entry_date BETWEEN  #{startEntryDate} and #{endEntryDate}
		</if>
		<if test="maintenanceUnits != null and maintenanceUnits.size() >0 ">
			AND mm.id in
			<foreach item="item" collection="maintenanceUnits" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
		<if test="deptIds != null and deptIds.size() >0 ">
			AND mm.dept_id in
			<foreach item="item" collection="deptIds" separator="," open="(" close=")" index="">
				#{item}
			</foreach>
		</if>
	</sql>

	<select id="getStatistics"
			resultType="com.emergency.avenue.storage.api.response.MaterialMaintenanceResponse">
		select
        mm.id            ,
        mm.dept_id       ,
        mm.material_group,
        mm.material_type ,
        mm.material_name ,
        mm.entry_date    ,
        mm.unit_price,
        mv.quantity
	from
			material_maintenance mm
	left join material_view mv
	on
        mm.dept_id      =mv.dept_id
    and mm.material_name=mv.material_name
    and mv.inbound_time=mm.entry_date
    and mm.unit_price=mv.unit_price where mv.quantity>0 and mm.status='5'
		<include refid="getStatisticsWhere"></include>
		order by mm.dept_id,mm.material_group, mm.material_type ,
		mm.material_name ,mm.entry_date
	</select>
	<select id="getStatisticsbyId"
			resultType="com.emergency.avenue.storage.api.response.MaterialMaintenanceResponse">
       select
        mm.id            ,
        mm.dept_id       ,
        mm.material_group,
        mm.material_type ,
        mm.material_name ,
        mm.entry_date    ,
        mm.unit_price,
        mv.quantity,
        mmd.maintenance_unit,
        mmd.maintenance_person,
        mmd.maintenance_time,
        mmd.file_part,
        mmd.file_name,
        mmd.maintenance_unit,
        mmd.maintenance_id
	from
	 material_maintenance_detail mmd
	left join material_maintenance mm on mm.id=mmd.maintenance_id
	left join material_view mv
	on
        mm.dept_id      =mv.dept_id
    and mm.material_name=mv.material_name
    and mv.inbound_time=mm.entry_date
    and mm.unit_price=mv.unit_price where mv.quantity>0 and mm.status='5'
    and mmd.maintenance_id=#{id}
	</select>
</mapper>
