<?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.zcloud.mapper.dsno1.corp.MaintenanceFlowMapper">

	<!--表名 -->
	<sql id="tableName">
		BUS_MAINTENANCE_FLOW
	</sql>

	<!--数据字典表名 -->
	<sql id="dicTableName">
		SYS_DICTIONARIES
	</sql>

	<!-- 字段 -->
	<sql id="Field">
		mf.ID,
		mf.CORPINFO_ID,
		mf.ISDELETE,
		mf.CREATOR,
		mf.CREATTIME,
		mf.NOTES,
		mf.STATUS,
		mf.STEP,
		mf.`ACTION`
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		ID,
		CORPINFO_ID,
		ISDELETE,
		CREATOR,
		CREATTIME,
		NOTES,
		STATUS,
		STEP,
		`ACTION`
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{ID},
		#{CORPINFO_ID},
		#{ISDELETE},
		#{CREATOR},
		#{CREATTIME},
		#{NOTES},
		#{STATUS},
		#{STEP},
		#{ACTION}
	</sql>

	<!-- 新增-->
	<insert id="save" parameterType="pd">
		insert into
	<include refid="tableName"></include>
		(
	<include refid="Field2"></include>
		) values (
	<include refid="FieldValue"></include>
		)
	</insert>

	<!--更新实施提醒次数-->
	<update id="updateReminderCount" parameterType="pd">
		update <include refid="tableName"></include> bmf
		set bmf.WARN_COUNT = bmf.WARN_COUNT + 1
		where bmf.CORPINFO_ID in
			<foreach item="item" index="index" collection="corpIds" open="(" separator="," close=")">
				#{item}
			</foreach> and bmf.STEP = '3' and bmf.ISDELETE = '0'
	</update>

	<!-- 存储验收报告照片 -->
	<update id="updateAccReport" parameterType="pd">
		update <include refid="tableName"></include> bmf
		set bmf.ACCREP_FILE = #{ACCREP_FILE}
		where bmf.CORPINFO_ID = #{CORPINFO_ID} and bmf.ISDELETE = '0' and bmf.STEP = '4'
	</update>

	<!-- 存储交付信息 -->
    <update id="deliverConfirmation" parameterType="pd">
		update <include refid="tableName"></include> bmf,
		    	BUS_MAINTENANCEUSER_INFO bmi,
		    	bus_corp_info bmc
		<set>
			<if test="DELIVERY_INFO != null">
				bmf.DELIVERY_INFO = #{DELIVERY_INFO},
			</if>
			<if test="OPERATIONPERSON_ID != null and OPERATIONPERSON_ID != ''">
				bmi.TEM_MAINTENANCE_PERSON = #{OPERATIONPERSON_ID},
			</if>
			<if test="DELIVERDATE != null and DELIVERDATE != ''">
				bmc.DELIVERDATE = #{DELIVERDATE}
			</if>
		</set>
		where bmf.CORPINFO_ID = #{CORPINFO_ID} and bmf.ISDELETE = '0' and bmf.STEP = '4' and bmi.CORPINFO_ID = #{CORPINFO_ID} and bmi.ISDELETE = '0'
		and bmc.CORPINFO_ID = #{CORPINFO_ID}  and bmc.ISDELETE = '0'
	</update>

	<!-- 项目负责人管理-确认交付 -->
	<update id="confirmedDelivery" parameterType="pd">
		update <include refid="tableName"></include> bmf,
		    	bus_maintenanceuser_info bmi
		set bmf.DELIVERY_INFO = #{DELIVERY_INFO},
		    bmi.MAINTENANCE_PERSON = #{OPERATIONPERSON_ID}
		where bmf.CORPINFO_ID = #{CORPINFO_ID} and bmi.CORPINFO_ID = #{CORPINFO_ID} and bmf.STEP = '5' and bmf.ISDELETE = '0' and bmi.ISDELETE = '0'
	</update>

	<!-- 实施人员管理-延期申请表单提交 -->
	<update id="editApplyExtension" parameterType="pd">
		update <include refid="tableName"></include> bmf
		set bmf.APPROVAL_INFO = #{APPROVAL_INFO},
		    bmf.APPROVAL_TIME = #{APPROVAL_TIME},
		    bmf.STATUS = '1'
		where bmf.ID = (select bm.ID from
		    (SELECT bmff.ID ID
				FROM bus_maintenance_flow bmff
				where bmff.CORPINFO_ID = #{CORPINFO_ID} and bmff.STEP = '2' and bmff.ISDELETE = '0'
				ORDER BY bmff.CREATTIME DESC
				LIMIT 1) bm )
	</update>

	<!-- 项目负责人管理-延期审批 -->
	<update id="updateApprovalInfo">
		update <include refid="tableName"></include> bmf
		set bmf.APPROVAL_INFO = #{APPROVAL_INFO},
		    bmf.APPROVAL_TIME = #{APPROVAL_TIME},
		    bmf.NOTES = #{NOTES},
		    bmf.STATUS = #{ISPASSED}
		where bmf.CORPINFO_ID = #{CORPINFO_ID} and bmf.ISDELETE = '0'
			<if test='ISPASSED == "1"'>
				and bmf.STEP = '-1'
			</if>
			<if test='ISPASSED == "2"'>
				and bmf.STEP = '2'
			</if>
	</update>

	<!-- 实施提醒 -->
	<select id="implementationReminder" parameterType="pd" resultType="pd">
		select
		    f.CORPINFO_ID CORPINFO_ID,
			f.CORP_NAME CORP_NAME
		from
			(select b.CORPINFO_ID CORPINFO_ID
			from (
				select MAX(bmf.CREATTIME) CREATTIME, bmf.CORPINFO_ID CORPINFO_ID, bmf.WARN_COUNT WARN_COUNT
			    from <include refid="tableName"></include> bmf,
			    	(select bmi.CORPINFO_ID CORPINFO_ID
			    	from BUS_MAINTENANCEUSER_INFO bmi
			    	where bmi.ISDELETE = '0'
					<choose>
						<when test='ROLE_NAME == "市场人员"'>
							and bmi.MARKETING_PERSON = #{USER_ID}
						</when>
						<when test='ROLE_NAME == "项目负责人"'>
							and bmi.MARKETING_PERSON = #{USER_ID}
						</when>
					</choose>
			    	) a
			    where a.CORPINFO_ID = bmf.CORPINFO_ID and bmf.STEP = '3' and bmf.ISDELETE = '0'
			    group by bmf.CORPINFO_ID
				) b
			where b.WARN_COUNT <![CDATA[ < ]]> '3' and TIMESTAMPDIFF(DAY,b.CREATTIME,NOW()) <![CDATA[ > ]]> '90'
			) c, BUS_CORP_INFO f
		where c.CORPINFO_ID = f.CORPINFO_ID and f.ISDELETE = '0'
	</select>

	<!-- 获取延期审批申请信息 -->
	<select id="getApprovalInfo" resultType="pd" parameterType="pd">
		select
		    bmf.CORPINFO_ID CORPINFO_ID,
			bmf.APPROVAL_INFO APPROVAL_INFO,
		    bmf.APPROVAL_TIME APPROVAL_TIME,
		    bmf.NOTES NOTES
		from
			<include refid="tableName"></include> bmf
		where
		    bmf.CORPINFO_ID = #{CORPINFO_ID}
			and bmf.STEP = '2'
			and bmf.ISDELETE = '0'
		order by bmf.CREATTIME desc
	</select>

	<!-- 获取验收报告信息 -->
	<select id="getAccReportInfo" resultType="pd" parameterType="pd">
		select
			bmfff.CORPINFO_ID CORPINFO_ID,
		    bmfff.ACCREP_FILE ACCREP_FILE
		from
			(
			select bmff.CREATTIME CREATTIME,bmff.CORPINFO_ID CORPINFO_ID,bmff.STEP STEP,
			       bmff.APPROVAL_INFO APPROVAL_INFO, bmff.APPROVAL_TIME APPROVAL_TIME, bmff.ACCREP_FILE ACCREP_FILE
			from BUS_MAINTENANCE_FLOW bmff
				LEFT JOIN ( SELECT MAX(bmff1.CREATTIME) CREATTIME,CORPINFO_ID FROM bus_maintenance_flow bmff1 WHERE bmff1.ISDELETE = '0' GROUP BY bmff1.CORPINFO_ID ) bmff2
				ON bmff2.CORPINFO_ID = bmff.CORPINFO_ID
			WHERE
				bmff.ISDELETE = '0'
			  AND bmff.CREATTIME = bmff2.CREATTIME
			GROUP BY bmff.CORPINFO_ID
			) bmfff
		where
		    bmfff.CORPINFO_ID = #{CORPINFO_ID}
			and bmfff.STEP = '4';
	</select>

	<select id="getDeliveryInfo" resultType="pd" parameterType="pd">
		select
		    bmf.CORPINFO_ID CORPINFO_ID,
			bmf.DELIVERY_INFO DELIVERY_INFO,
		    bmi.TEM_MAINTENANCE_PERSON OPERATIONPERSON_ID,
		    bmc.DELIVERDATE,
		    m.TERM TERM,
		    m.CONTRACT_FILE CONTRACT_FILE
		from <include refid="tableName"></include> bmf
			LEFT JOIN BUS_MAINTENANCEUSER_INFO bmi on bmi.CORPINFO_ID = bmf.CORPINFO_ID and bmi.ISDELETE = '0'
			LEFT JOIN bus_corp_time m on bmi.CORPINFO_ID = m.CORPINFO_ID and m.ISDELETE = '0'
			left join bus_corp_info bmc on bmc.CORPINFO_ID = bmf.CORPINFO_ID and bmc.ISDELETE = '0'
		where bmf.CORPINFO_ID = #{CORPINFO_ID} and bmf.ISDELETE = '0' and bmf.STEP = '4'
	</select>
</mapper>
