<?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="org.jeecg.modules.stm.project.exec.mapper.StProjectExecMapper">
	<select id="queryStProjectExecVoByPage" resultType="org.jeecg.modules.stm.project.exec.vo.StProjectExecVo">
	        select
		a.id as
		projectCreId,
		max(a.project_name) as projectName ,
		max(a.project_type)
		as projectType ,
		max(a.exec_dep_code) as execDepCode ,
		max(a.head_code)
		as headCode ,
		max(a.plan_start_date) as planStartDate ,
		max(a.plan_end_date) as planEndDate ,
		max((select depart_name from sys_depart c
		where c.id=a.exec_dep_code))
		as
		execDepName,
		max((select realname from sys_user c where
		c.username=a.head_code)) as
		headName,
		max(
		case
		when c.exec_level is null  or c.exec_level='0000'
		then '信息采集'
		when c.exec_level='0001' then '事实发现'
		when
		c.exec_level='0002' then '风险计量'
		when c.exec_level='0003' then '事实确认'
		when c.exec_level='0004' then '整改建议'
		else '实施完成'
		end
		) as execLevel,
		max(ifnull(c.exec_level,'0000')) as execLevelCode,
		sum(
		case
		when c.exec_level='0004'
		then (select count(1) from st_project_exec_pro s where c.id=s.project_exec_Id)
		else 1
		end
		) as sumCount,
		sum(
		case
		when c.exec_level='0004' then (select count(1)  from st_project_exec_pro s where c.id=s.project_exec_Id and s.severity is not null)
		when c.confirm_Status ='1' and (c.exec_level!='0003' or c.exec_level is null) then 1
		when c.true_Status ='1' and c.exec_level='0003' then 1
		else 0
		end
		)as overCount
		from
		st_project_cre a ,st_project_check b
		left join st_project_exec c on b.id=c.project_Check_Id
		where
		a.id=b.PROJECT_CRE_ID
		<if test="stProjectExecVo.projectName!=null and stProjectExecVo.projectName!='' ">
			and a.project_name like concat('%',#{stProjectExecVo.projectName},'%')
		</if>
		<if test="stProjectExecVo.execDepCode!=null   and  stProjectExecVo.execDepCode!=''  ">
			and a.exec_dep_code =#{stProjectExecVo.execDepCode}
		</if>
		<if test="stProjectExecVo.projectType!=null and stProjectExecVo.projectType!='' ">
			and a.project_type =#{stProjectExecVo.projectType}
		</if>
		<if test="stProjectExecVo.execLevel!=null and stProjectExecVo.execLevel!='' ">
			and c.exec_level =#{stProjectExecVo.execLevel}
		</if>
		group by a.id
		order by a.updated_time desc
	</select>
	<select id="queryStProjectExecExVoByPage" resultType="org.jeecg.modules.stm.project.exec.vo.StProjectExecExVo">
select
		a.id as projectCreId,
		b.id as projectCheckId,		
		a.project_name as projectName ,
		a.project_type as projectType ,
		a.exec_dep_code as execDepCode ,
		a.head_code as headCode ,
		a.plan_start_date as planStartDate ,
		a.plan_end_date as planEndDate ,
		(select depart_name from sys_depart c
		where c.id=a.exec_dep_code)
		as
		execDepName,
		(select realname from sys_user c where
		c.username=a.head_code) as
		headName,

		case
		when c.exec_level is null or c.exec_level='0000' then '信息采集'
		when c.exec_level='0001' then
		'事实发现'
		when c.exec_level='0002' then '风险计量'
		when c.exec_level='0003'
		then '事实确认'
		when c.exec_level='0004' then '整改建议'
		else '实施完成'
		end
		as execLevel,	
		d.CA_NAME as caName,
		b.CHECK_CONTENT as checkCountent,
		c.file_Upload_Path as fileUploadPath,
		c.id as id,
		(select i.domain_Name from ST_DOMAIN i,ST_PROJECT_GATHER f where i.DOMAIN_CODE=f.DOMAIN_CODE and  b.id=f.PROJECT_CHECK_ID) as domainName,
		'会议纪要' as checkDoc,
		c.exec_level as execLevelCode,	
		(select i.des from st_project_gather i where i.PROJECT_CHECK_ID=b.id) as des,
		case
		when confirm_Status is null then 0
		else confirm_Status
		end
		as confirmStatus,
		
		(select max(problem_Title) from st_project_exec_pro i where i.project_Exec_Id=c.id) as problemTitle,
				(select max(problem_Content) from st_project_exec_pro i where i.project_Exec_Id=c.id) as problemContent	,
				(select max(risk_Title) from st_project_exec_risk i where i.project_Exec_Id=c.id) as riskTitle,
		(select max(risk_Content) from st_project_exec_risk i where i.project_Exec_Id=c.id) as riskContent,	
		(select i.name from sys_position i where i.code=b.CHECK_ASK) as checkAsk,
		(select i.name from sys_position i where i.code=b.CHECK_CROSS) as checkCross,
		c.exec_Content as execContent,
c.true_Status as trueStatus,
c.exec_Content_Des as execContentDes,
c.severity as severity,
c.advice_Level as adviceLevel,
c.advice_Advice as adviceAdvice,
c.damage_level as damageLevel,
c.damage_Score as damageScore,
c.confirm_Dep_Code as confirmDepCode,
		(select depart_name from sys_depart d
		where d.id=c.confirm_Dep_Code)
		as
		confirmDepName,
c.confirm_User as confirmUser,
		(select realname from sys_user d where
		d.username=c.confirm_User) as
		confirmName,
		(select c.file_Upload_Path from st_project_gather c where b.id=c. project_Check_Id) as fileUploadPathModel,
		c.file_Upload_Path_Des as fileUploadPathDes,
		(select f.name from sys_category f where  b.check_Review=f.code) 		
		as checkReview,
		b.ca_no as caNo,
		case 
		when c.file_Upload_Path is not null then c.UPDATED_TIME
		else null
		end 
		as fileUploadDate,
				case 
		when c.file_Upload_Path is not null then 		(select realname from sys_user c where
		c.username=c.UPDATED_USER) 
		else null
		end
		as 
		fileUploadUser,
		case 
		when c.file_Upload_Path is not null then (select max(depart_name) from sys_user_depart  n,sys_user m,sys_depart p 
where n.user_id=m.id and n.dep_id=p.id and m.username=c.UPDATED_USER) 
		else null
		end
		as
		fileUploadDep
		from
		st_project_cre a ,
		ST_CA_MAIN d,
		st_project_check b
		left join st_project_exec c on
		b.id=c.project_Check_Id
		where a.id=b.PROJECT_CRE_ID
		and d.ca_no=b.ca_no
		<if test="stProjectExecExVo.projectCreId!=null">
			and a.id = #{stProjectExecExVo.projectCreId}
		</if>
		<if test="stProjectExecExVo.id!=null">
			and c.id = #{stProjectExecExVo.id}
		</if>
	</select>
		<select id="queryStProjectExecPro" resultType="org.jeecg.modules.stm.project.exec.vo.StProjectExecExVo">
select
		a.id as projectCreId,
		b.id as projectCheckId,		
		a.project_name as projectName ,
		a.project_type as projectType ,
		a.exec_dep_code as execDepCode ,
		a.head_code as headCode ,
		a.plan_start_date as planStartDate ,
		a.plan_end_date as planEndDate ,
		(select depart_name from sys_depart c
		where c.id=a.exec_dep_code)
		as
		execDepName,
		(select realname from sys_user c where
		c.username=a.head_code) as
		headName,

		case
		when c.exec_level is null or c.exec_level='0000' then '信息采集'
		when c.exec_level='0001' then
		'事实发现'
		when c.exec_level='0002' then '风险计量'
		when c.exec_level='0003'
		then '事实确认'
		when c.exec_level='0004' then '整改建议'
		else '实施完成'
		end
		as execLevel,
		d.CA_NAME as caName,
		b.CHECK_CONTENT as checkCountent,
		c.file_Upload_Path as fileUploadPath,
		e.id as id,
		(select i.domain_Name from ST_DOMAIN i,ST_PROJECT_GATHER f where i.DOMAIN_CODE=f.DOMAIN_CODE and  b.id=f.PROJECT_CHECK_ID) as domainName,
		'会议纪要' as checkDoc,
		c.exec_level as execLevelCode,	
		(select i.des from st_project_gather i where i.PROJECT_CHECK_ID=b.id) as des,
		case
		when e.severity is null then 0
		else 1
		end
		as confirmStatus,
		
		(select max(problem_Title) from st_project_exec_pro i where i.project_Exec_Id=c.id) as problemTitle,
				(select max(problem_Content) from st_project_exec_pro i where i.project_Exec_Id=c.id) as problemContent	,
				(select max(risk_Title) from st_project_exec_risk i where i.project_Exec_Id=c.id) as riskTitle,
		(select max(risk_Content) from st_project_exec_risk i where i.project_Exec_Id=c.id) as riskContent,	
		(select i.name from sys_position i where i.code=b.CHECK_ASK) as checkAsk,
		(select i.name from sys_position i where i.code=b.CHECK_CROSS) as checkCross,
		c.exec_Content as execContent,
c.true_Status as trueStatus,
c.exec_Content_Des as execContentDes,
e.severity as severity,
e.advice_Level as adviceLevel,
e.advice_Advice as adviceAdvice,
c.damage_level as damageLevel,
c.damage_Score as damageScore,
c.confirm_Dep_Code as confirmDepCode,
		(select depart_name from sys_depart d
		where d.id=c.confirm_Dep_Code)
		as
		confirmDepName,
c.confirm_User as confirmUser,
		(select realname from sys_user d where
		d.username=c.confirm_User) as
		confirmName,
		(select c.file_Upload_Path from st_project_gather c where b.id=c. project_Check_Id) as fileUploadPathModel,
		c.file_Upload_Path_Des as fileUploadPathDes,
		(select f.name from sys_category f where  b.check_Review=f.code) 		
		as checkReview,
		(select realname from sys_user f where
f.username=e.created_User) as proUserName,
e.problem_Title as problemItem,
e.problem_Content as problemExample,
		b.ca_no as caNo
		from
		st_project_cre a ,
		ST_CA_MAIN d,
		st_project_check b,
		st_project_exec c,
                st_project_exec_pro e
		where a.id=b.PROJECT_CRE_ID
		and d.ca_no=b.ca_no
		and b.id=c.project_Check_Id
		and c.id=e.project_Exec_Id
		and b.PROJECT_CRE_ID = #{stProjectExecExVo.projectCreId}
	</select>
			<select id="queryStProjectExecProList" resultType="org.jeecg.modules.stm.project.exec.vo.StProjectExecExVo">
		select  
a.ca_Name as caName,
c.damage_Level as damageLevel,
c.damage_Score as damageScore,
d.problem_Title as problemItem,
d.problem_Content as problemExample,
(select realname from sys_user e where
e.username=d.created_User) as proUserName,
b.PROJECT_CRE_ID  as projectCreId
from 
ST_CA_MAIN a,
st_project_check b,
st_project_exec c ,
st_project_exec_pro d
where 
b.id=c.project_Check_Id and
a.ca_no=b.ca_no and
c.id=d.project_Exec_Id
		and b.PROJECT_CRE_ID = #{stProjectExecExVo.projectCreId}
	</select>
		<!-- 修改为已读消息 -->
	<update id="submitExec"  >
		update st_project_exec a set a.exec_level = #{stProjectExecVo.execLevel},a.confirm_Status='0'
		 where a.project_check_id in
		 (select b.id from st_project_check b where b.PROJECT_CRE_ID=#{stProjectExecVo.projectCreId})
		 	</update>
</mapper>