USE [PLM]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetWorkItemOfWfTaskBox]    Script Date: 2024/12/26 19:18:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


-- =============================================
-- Author:		wxy
-- Create date: 2015-09-14
-- Description:	任务项获取任务
-- =============================================
ALTER PROCEDURE [dbo].[usp_GetWorkItemOfWfTaskBox]
	-- Add the parameters for the stored procedure here
	@param_qualification_id int,       --当前视图ID
	@param_person_id	      int,        --当前人员ID
	@param_task_type          smallint,				--1尚未接收任务2已接收未处理任务 3已完成任务 4删除的任务
	--后续参数只对param_task_type为3和4有效 
	@param_start_date   DATETIME,				--开始时间(任务开始时间 start_time)
	@param_end_date     DATETIME,				--结束时间(任务开始时间 start_time)
	@param_template_id  int,					--模板ID 如果<=0，表示没有模板条件
	@param_process_name nvarchar(256),			--流程名字，如果为空表示不查询流程名字
	@param_task_name    nvarchar(256)			--任务名字，如果为空表示不查询任务名字
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF @param_task_type = 3    --已完成任务
	BEGIN
		select e.name as wfp_name,e.description as wfp_description,g.name as wft_name,i.Name as wfp_creator_name
			,a.sender as task_sender,e.SFXF
			,a.* 
		from T_WfWorkItem a 
			 INNER JOIN T_WFWorkitemPerform b  ON a.obj_id = b.roleA_id and a.class_id = b.RoleA_Class_ID
				 AND ((b.RoleB_id = @param_person_id and a.agent_id = 0) OR (a.agent_id = @param_person_id))
				 AND a.state >= 3 AND a.delete_flag = 0
				 AND (a.start_time >@param_start_date and a.start_time <@param_end_date)
		  	     AND (len(@param_task_name) = 0 OR a.name like N'%'+@param_task_name+ N'%')
		    	 AND (@param_template_id <=0 OR a.template_id = @param_template_id)
			 INNER JOIN T_WfActivityWorkItem c ON a.obj_id = c.roleB_id and a.class_id = c.roleB_class_ID
			 INNER JOIN T_WfProcessActivity d  ON c.ROLEA_id = d.roleB_id and c.ROLEA_class_id = d.roleB_class_ID
			 INNER JOIN T_WfProcessIns e       ON e.obj_id = d.roleA_id and e.class_id = d.roleA_class_ID
				 AND (len(@param_process_name)= 0 OR e.name like N'%'+@param_process_name + N'%')
			 INNER JOIN T_WfTemplateProcess f  ON e.obj_id = f.roleB_id and e.class_id = f.roleB_class_ID
			 INNER JOIN T_WfProcessTemplate g  ON g.obj_id = f.roleA_id and g.class_id = f.roleA_class_ID
			 INNER JOIN T_WfProcessCreator h   ON a.root_process_id = h.roleA_id
			 INNER JOIN T_PERSON i             ON i.obj_id = h.roleB_id and i.class_id = h.roleB_class_ID
		     Order by a.obj_id;
	END
	IF @param_task_type = 4  --已删除任务
	BEGIN
		select e.name as wfp_name,e.description as wfp_description,g.name as wft_name,i.Name as wfp_creator_name
			,a.sender as task_sender
			,a.* 
		from T_WfWorkItem a 
			 INNER JOIN T_WFWorkitemPerform b  ON a.obj_id = b.roleA_id and a.class_id = b.RoleA_Class_ID
			 AND ((b.RoleB_id = @param_person_id and a.agent_id = 0) OR (a.agent_id = @param_person_id))
			 AND a.delete_flag <> 0
			 AND ((a.start_time >@param_start_date and a.start_time <@param_end_date)
			      OR (a.receive_state = 0 and a.creationdate > @param_start_date and a.creationdate <@param_end_date))
		     AND (len(@param_task_name) = 0 OR a.name like N'%'+@param_task_name+ N'%')
		     AND (@param_template_id <=0 OR a.template_id = @param_template_id)
			 INNER JOIN T_WfActivityWorkItem c ON a.obj_id = c.roleB_id and a.class_id = c.roleB_class_ID
			 INNER JOIN T_WfProcessActivity d  ON c.ROLEA_id = d.roleB_id and c.ROLEA_class_id = d.roleB_class_ID
			 INNER JOIN T_WfProcessIns e       ON e.obj_id = d.roleA_id and e.class_id = d.roleA_class_ID
			 AND (len(@param_process_name)= 0 OR e.name like N'%'+@param_process_name + N'%')
			 INNER JOIN T_WfTemplateProcess f  ON e.obj_id = f.roleB_id and e.class_id = f.roleB_class_ID
			 INNER JOIN T_WfProcessTemplate g  ON g.obj_id = f.roleA_id and g.class_id = f.roleA_class_ID
			 INNER JOIN T_WfProcessCreator h   ON a.root_process_id = h.roleA_id
			 INNER JOIN T_PERSON i             ON i.obj_id = h.roleB_id and i.class_id = h.roleB_class_ID
		Order by a.obj_id;
	END;
	IF (@param_task_type = 2)--已接收任务未完成任务
	BEGIN
		select e.name as wfp_name,e.description as wfp_description,g.name as wft_name,i.Name as wfp_creator_name
			,a.sender as task_sender,e.SFXF
			,a.* 
		from T_WfWorkItem a 
			 INNER JOIN T_WFWorkitemPerform b  ON a.obj_id = b.roleA_id and a.class_id = b.RoleA_Class_ID
			 	AND ((b.RoleB_id = @param_person_id and a.agent_id = 0) OR (a.agent_id = @param_person_id))
				AND a.state = 2 AND a.delete_flag = 0 AND a.valid_Flag = 0 AND a.receive_state = 1 AND a.submit_state = 0
			 INNER JOIN T_WfActivityWorkItem c ON a.obj_id = c.roleB_id and a.class_id = c.roleB_class_ID
			 INNER JOIN T_WfProcessActivity d  ON c.ROLEA_id = d.roleB_id and c.ROLEA_class_id = d.roleB_class_ID
			 INNER JOIN T_WfProcessIns e       ON e.obj_id = d.roleA_id and e.class_id = d.roleA_class_ID
			 INNER JOIN T_WfTemplateProcess f  ON e.obj_id = f.roleB_id and e.class_id = f.roleB_class_ID
			 INNER JOIN T_WfProcessTemplate g  ON g.obj_id = f.roleA_id and g.class_id = f.roleA_class_ID
			 INNER JOIN T_WfProcessCreator h   ON a.root_process_id = h.roleA_id
			 INNER JOIN T_PERSON i             ON i.obj_id = h.roleB_id and i.class_id = h.roleB_class_ID
		 Order by a.obj_id;
	END;
	IF (@param_task_type = 1)--未接收任务
	BEGIN
		--未接收任务的代理处理起来比较麻烦，需要判断哪些是当前代理的任务。
		--判断是否要获取未接收代理任务，如果需要则处理。
		--人员，模板列表
		select * from 
		(
		select e.name as wfp_name,e.description as wfp_description,g.name as wft_name,i.Name as wfp_creator_name
			,a.sender as task_sender,e.SFXF
			,a.* 
		from T_WfWorkItem a 
			 INNER JOIN T_WFWorkitemPerform b  ON a.obj_id = b.roleA_id and a.class_id = b.RoleA_Class_ID
				 AND b.RoleB_id = @param_person_id
				 AND a.state = 2 AND a.delete_flag = 0 AND a.valid_Flag = 0 AND a.receive_state = 0
			 INNER JOIN T_WfActivityWorkItem c ON a.obj_id = c.roleB_id and a.class_id = c.roleB_class_ID
			 INNER JOIN T_WfProcessActivity d  ON c.ROLEA_id = d.roleB_id and c.ROLEA_class_id = d.roleB_class_ID
			 INNER JOIN T_WfProcessIns e       ON e.obj_id = d.roleA_id and e.class_id = d.roleA_class_ID
			 INNER JOIN T_WfTemplateProcess f  ON e.obj_id = f.roleB_id and e.class_id = f.roleB_class_ID
			 INNER JOIN T_WfProcessTemplate g  ON g.obj_id = f.roleA_id and g.class_id = f.roleA_class_ID
			 INNER JOIN T_WfProcessCreator h   ON a.root_process_id = h.roleA_id
			 INNER JOIN T_PERSON i             ON i.obj_id = h.roleB_id and i.class_id = h.roleB_class_ID
		union
		select e.name as wfp_name,e.description as wfp_description,g.name as wft_name,i.Name as wfp_creator_name
			,a.sender as task_sender,e.SFXF
			,a.* 
		from T_WfWorkItem a 
			 INNER JOIN T_WFWorkitemPerform b   ON a.obj_id = b.roleA_id and a.class_id = b.RoleA_Class_ID
			  AND a.state = 2 AND a.delete_flag = 0 AND a.valid_Flag = 0 AND a.allow_agent = 1 AND a.receive_state = 0
			 INNER JOIN T_WfActivityWorkItem c  ON a.obj_id = c.roleB_id and a.class_id = c.roleB_class_ID
			 INNER JOIN T_WfProcessActivity d   ON c.ROLEA_id = d.roleB_id and c.ROLEA_class_id = d.roleB_class_ID
			 INNER JOIN T_WfProcessIns e        ON e.obj_id = d.roleA_id and e.class_id = d.roleA_class_ID
			 INNER JOIN T_WfTemplateProcess f   ON e.obj_id = f.roleB_id and e.class_id = f.roleB_class_ID
			 INNER JOIN T_WfProcessTemplate g   ON g.obj_id = f.roleA_id and g.class_id = f.roleA_class_ID
			 INNER JOIN T_WfProcessCreator h    ON a.root_process_id = h.roleA_id
			 INNER JOIN T_PERSON i              ON i.obj_id = h.roleB_id and i.class_id = h.roleB_class_ID
			 INNER JOIN T_DelegateData l        ON l.delegatedperson_id = b.roleB_id
			 AND l.start_date <= getdate() 
			 AND l.end_date >= getdate()
			 AND l.delegatetype = 0
			 INNER JOIN T_PersonDelegatorData k ON l.obj_id = k.roleB_id and l.class_id = k.roleB_class_id			 
			 AND k.roleA_id = @param_person_id
		union	 
		select e.name as wfp_name,e.description as wfp_description,g.name as wft_name,i.Name as wfp_creator_name
			,a.sender as task_sender,e.SFXF
			,a.* 
		from T_WfWorkItem a 
			 INNER JOIN T_WFWorkitemPerform b   ON a.obj_id = b.roleA_id and a.class_id = b.RoleA_Class_ID
				AND a.state = 2 AND a.delete_flag = 0 AND a.valid_Flag = 0 AND a.allow_agent = 1  AND a.receive_state = 0
			 INNER JOIN T_WfActivityWorkItem c  ON a.obj_id = c.roleB_id and a.class_id = c.roleB_class_ID
			 INNER JOIN T_WfProcessActivity d   ON c.ROLEA_id = d.roleB_id and c.ROLEA_class_id = d.roleB_class_ID
			 INNER JOIN T_WfProcessIns e        ON e.obj_id = d.roleA_id and e.class_id = d.roleA_class_ID
			 INNER JOIN T_WfTemplateProcess f   ON e.obj_id = f.roleB_id and e.class_id = f.roleB_class_ID
			 INNER JOIN T_WfProcessTemplate g   ON g.obj_id = f.roleA_id and g.class_id = f.roleA_class_ID
			 INNER JOIN T_WfProcessCreator h    ON a.root_process_id = h.roleA_id
			 INNER JOIN T_PERSON i              ON i.obj_id = h.roleB_id and i.class_id = h.roleB_class_ID
			 INNER JOIN T_DelegateData l        ON l.delegatedperson_id = b.roleB_id		 
			 AND l.start_date <= getdate() 
			 AND l.end_date >= getdate()
			 AND l.delegatetype <> 0
			 INNER JOIN T_PersonDelegatorData k ON l.obj_id = k.roleB_id and l.class_id = k.roleB_class_id
			 			 AND k.roleA_id = @param_person_id
			 INNER JOIN T_DelegateDataWFTemplate m on l.obj_id = m.roleA_id and l.class_id = m.roleA_class_id
			 INNER JOIN T_WfTemplate n on n.obj_id = m.roleB_id and n.class_id = m.roleB_class_id and a.template_id = n.obj_id
		
		) as w
		Order by obj_id;
	END;
END

