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

	<!--实施人员管理列表-->
	<select id="listPageForImplManagementList" parameterType="page" resultType="pd">
		select
			bci.CORPINFO_ID,
			bci.CORP_NAME,
			bci.CODE,
			bci.COMPANY_AREA,
			bci.CONTACTS,
			bci.CONTACTS_PHONE,
			bci.VIPLEVEL,
			bci.CREATTIME,
			bci.DELIVERDATE,
			bci.USE_DATE_END,
			sysrole.ROLE_NAME as vipLevelName,
			bmi.MARKETING_PERSON as corpMarketingPerson,
			su.NAME as corpMarketingPersonName,
			bam.AGENCY_MANAGEMENT_ID as agencyId,
			if (bam.AGENCY_NAME is null,'平台',bam.AGENCY_NAME) as agencyName,
			bam.MARKETING_PERSON as agencyMarketingPerson,
			sysu.NAME as agencyMarketingPersonName,
			sus.NAME as projectLeaderName,
			us.NAME as implPersonName,
			suser.NAME as maintenancePersonName,
			(case
				when TIMESTAMPDIFF(DAY, NOW(), max(endtime.USE_DATE_END)) <![CDATA[ <= ]]> '90' and
				TIMESTAMPDIFF(DAY, NOW(), max(endtime.USE_DATE_END)) <![CDATA[ >= ]]> '0'
				then '临期'
				when bmfff.STEP = '3' then '待验收'
				when bmfff.STEP = '4' and bmfff.STATUS = '2' then '验收打回'
				when bmfff.STEP = '4' then '验收'
			    when bmfff.STEP = '0' then '待指派项目负责人'
			    when bmfff.STEP = '1' then '待指定实施人员'
				when bmfff.STEP = '2' and bmfff.APPROVAL_TIME is not null and bmfff.STATUS = '1' then '待审核'
				when bmfff.STEP = '2' then '实施'
				when bmfff.STEP = '-1' and bmfff.APPROVAL_TIME <![CDATA[ > ]]> NOW() then '延期实施'
			    when bmfff.STEP = '-1' and bmfff.APPROVAL_TIME <![CDATA[ <= ]]> NOW() then '实施'
				else '正常'
				end
			) as ENTERPRISE_STATUS,
		    (case
		        when bmfff.STEP = '-1' and bmfff.APPROVAL_TIME <![CDATA[ > ]]> NOW() then false
		        when bmfff.STEP = '-1' and bmfff.APPROVAL_TIME <![CDATA[ <= ]]> NOW() then true
		        when bmfff.STEP = '2' and bmfff.APPROVAL_INFO is null and bmfff.APPROVAL_TIME is null then true
				when bmfff.STEP = '2' and bmfff.STATUS = '2' then true
				when bmfff.STEP = '2'
					and bmfff.APPROVAL_INFO is not null
					and bmfff.APPROVAL_TIME is not null
					and bmfff.STATUS = '1' then false
				else true
		        end
			) as IS_APPLYEXTENSION,
		    bmfff.NOTES NOTES
		from
			bus_corp_info bci
		left join bus_maintenanceuser_info bmi on bci.CORPINFO_ID = bmi.CORPINFO_ID and bmi.ISDELETE = '0'
		left join bus_agency_management bam on bmi.AGENCY_MANAGEMENT_ID = bam.AGENCY_MANAGEMENT_ID and bam.ISDELETE = '0'
		left join sys_role sysrole on sysrole.ROLE_ID = bci.VIPLEVEL
		left join sys_user su on su.USER_ID = bmi.MARKETING_PERSON
		left join sys_user us on us.USER_ID = bmi.IMPLEMENTATION_PERRON
		left join sys_user sus on sus.USER_ID = bmi.PROJECT_LEADER
		left join sys_user suser on suser.USER_ID = bmi.MAINTENANCE_PERSON
		left join sys_user sysu on sysu.USER_ID = bam.MARKETING_PERSON
		LEFT JOIN BUS_CORP_TIME endtime ON endtime.CORPINFO_ID = bci.CORPINFO_ID and endtime.ISDELETE = '0'
		LEFT JOIN (
					select bmff.CORPINFO_ID CORPINFO_ID,
					bmff.STEP STEP, bmff.APPROVAL_INFO APPROVAL_INFO, bmff.APPROVAL_TIME APPROVAL_TIME,
					bmff.ACCREP_FILE ACCREP_FILE,bmff.STATUS STATUS, bmff.NOTES NOTES
					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 on bmfff.CORPINFO_ID = bci.CORPINFO_ID
		where bci.ISDELETE = '0' and (bmfff.STEP in ('2','-1') or (bmfff.STEP = '4' and bmfff.STATUS = '2'))
		<if test="pd.PROXY_SUB_ACCOUNT != null and pd.PROXY_SUB_ACCOUNT != ''"><!-- 代理分账号角色 -->
			and bmi.IMPLEMENTATION_PERRON = #{pd.PROXY_SUB_ACCOUNT}
		</if>
		<if test="pd.PROXY_ACCOUNT != null and pd.PROXY_ACCOUNT != ''"><!-- 代理机构主账号 -->
			and bmi.AGENCY_MANAGEMENT_ID = #{pd.PROXY_ACCOUNT}
		</if>
		<if test="pd.PLATFORM_SUB_ACCOUNT != null and pd.PLATFORM_SUB_ACCOUNT != ''"><!-- 平台分账号角色 -->
			and bmi.IMPLEMENTATION_PERRON = #{pd.PLATFORM_SUB_ACCOUNT}
		</if>
		<if test="pd.CORP_NAME != null and pd.CORP_NAME != ''">
			and bci.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.CORP_NAME}),'%')
		</if>
		<if test="pd.VIPLEVEL != null and pd.VIPLEVEL != ''">
			and bci.VIPLEVEL = #{pd.VIPLEVEL}
		</if>
		<if test="pd.MARKETING_PERSON != null and pd.MARKETING_PERSON != ''">
			and su.NAME LIKE CONCAT(CONCAT('%', #{pd.MARKETING_PERSON}),'%')
		</if>
		<if test="pd.AGENCY_MANAGEMENT_NAME != null and pd.AGENCY_MANAGEMENT_NAME != '' and pd.AGENCY_MANAGEMENT_NAME != '平台'">
			and bam.AGENCY_NAME LIKE CONCAT(CONCAT('%', #{pd.AGENCY_MANAGEMENT_NAME}),'%')
		</if>
		<if test="pd.AGENCY_MANAGEMENT_NAME != null and pd.AGENCY_MANAGEMENT_NAME == '平台'">
			and bam.AGENCY_NAME is null
		</if>
		<if test="pd.PROJECT_LEADER != null and pd.PROJECT_LEADER != ''">
			and sus.NAME LIKE CONCAT(CONCAT('%', #{pd.PROJECT_LEADER}),'%')
		</if>
		<if test="pd.MAINTENANCE_PERSON != null and pd.MAINTENANCE_PERSON != ''">
			and suser.NAME LIKE CONCAT(CONCAT('%', #{pd.MAINTENANCE_PERSON}),'%')
		</if>
		group by bci.CORPINFO_ID
		order by bci.CREATTIME desc
	</select>

	<!--实施人员管理查询机构列表-->
	<select id="getAgencyListForImplManagement" parameterType="pd" resultType="pd">
		select
			bam.AGENCY_MANAGEMENT_ID,
			bam.AGENCY_NAME
		from
			bus_agency_management bam
		where bam.ISDELETE = '0'
		order by bam.CREATE_TIME desc
	</select>

	<!--实施人员管理查询项目负责人列表-->
	<select id="getProjectLeaderListForImplManagement" parameterType="pd" resultType="pd">
		select
			su.USER_ID,
			su.USERNAME,
			su.NAME
		from
		    sys_user su
		where su.ISDELETE = '0' and su.ROLE_ID = '2c78a1b8435c41fbbf73a8acf0273109'
	</select>

	<!--实施人员管理查询运维人员列表-->
	<select id="getMaintenancePersonListForImplManagement" parameterType="pd" resultType="pd">
		select
			su.USER_ID,
			su.USERNAME,
			su.NAME
		from
		    sys_user su
		where su.ISDELETE = '0' and su.ROLE_ID = '714937449cc041f6a258df64f6db054b'
	</select>

	<!--根据企业id查询-->
	<select id="getCorpInfoByCorpInfoId" parameterType="pd" resultType="pd">
		select
			bci.CORPINFO_ID,
			bci.CORP_NAME,
			bci.CODE,
			bci.COMPANY_AREA,
			bci.CONTACTS,
			bci.CONTACTS_PHONE,
			bci.VIPLEVEL,
			bci.CREATTIME,
			bci.DELIVERDATE,
			bci.USE_DATE_END,
			sysrole.ROLE_NAME as vipLevelName,
			bmi.MARKETING_PERSON as corpMarketingPerson,
			su.NAME as corpMarketingPersonName,
			bam.AGENCY_MANAGEMENT_ID as agencyId,
			bam.AGENCY_NAME as agencyName,
			bam.MARKETING_PERSON as agencyMarketingPerson,
			sysu.NAME as agencyMarketingPersonName,
			sus.NAME as projectLeaderName,
			us.NAME as implPersonName,
			suser.NAME as maintenancePersonName,
		    bmf.APPROVAL_INFO as APPROVAL_INFO,
		    bmf.APPROVAL_TIME as APPROVAL_TIME,
		    (case
		        when bmf.STEP = '-1' and bmf.STATUS = '1' then '通过'
		        when bmf.STEP = '2' and bmf.STATUS = '2' then '未通过'
		        else ''
		        end
		    ) as STATUS,
		    bmf.NOTES as NOTES
		from
			bus_corp_info bci
		left join bus_maintenanceuser_info bmi on bci.CORPINFO_ID = bmi.CORPINFO_ID and bmi.ISDELETE = '0'
		left join bus_agency_management bam on bmi.AGENCY_MANAGEMENT_ID = bam.AGENCY_MANAGEMENT_ID and bam.ISDELETE = '0'
		left join sys_role sysrole on sysrole.ROLE_ID = bci.VIPLEVEL
		left join sys_user su on su.USER_ID = bmi.MARKETING_PERSON
		left join sys_user us on us.USER_ID = bmi.IMPLEMENTATION_PERRON
		left join sys_user sus on sus.USER_ID = bmi.PROJECT_LEADER
		left join sys_user suser on suser.USER_ID = bmi.MAINTENANCE_PERSON
		left join sys_user sysu on sysu.USER_ID = bam.MARKETING_PERSON
		left join bus_maintenance_flow bmf on bmf.CORPINFO_ID = bci.CORPINFO_ID
		                                and bmf.APPROVAL_INFO is not null and bmf.APPROVAL_TIME is not null
		                                and bci.ISDELETE = '0' and (bmf.STEP = '2' or bmf.STEP = '-1')
		where bci.ISDELETE = '0' and bci.CORPINFO_ID = #{CORPINFO_ID}
		order by bmf.CREATTIME DESC
		limit 0,1
	</select>

</mapper>
