ALTER PROCEDURE [dbo].[CRPTBKTWUL]
(
	@Period VARCHAR(20)
	,@Organization VARCHAR(20)
)
AS

--DECLARE @Period VARCHAR(20) = '2025'
--DECLARE @Organization VARCHAR(20) = '00000245'

DECLARE @now VARCHAR(20) = FORMAT(getdate(), 'yyyyMMdd')
DECLARE @PeiodeEnDt VARCHAR(20) = @Period + '1231'

IF(@Organization = '')
BEGIN SET @Organization = '00000001' END

DECLARE @t_org TABLE(
	org_id VARCHAR(20)
	,org_name VARCHAR(500)
	,div_id VARCHAR(20)
	,div_name VARCHAR(500)
)
INSERT INTO @t_org
select org_id, org_name, org_id_04, org_name_04 from dbo.GetOrgInOrgWide(@Organization, @now)

DECLARE @t_emp TABLE
(
	EmployeeID VARCHAR(20)
	,EmployeeName VARCHAR(500)
	,div_id VARCHAR(20)
	,div_name VARCHAR(500)
	,Organization VARCHAR(20)
	,OrgDesc VARCHAR(500)
	,Position VARCHAR(20)
	,PosDesc VARCHAR(500)
	,Job VARCHAR(20)
	,JobDesc VARCHAR(500)
)

INSERT INTO @t_emp
SELECT pa2.EmployeeID, pa1.FullName, torg.div_id, torg.div_name, pa2.Organization, torg.org_name, pa2.Position, pos.ObjectDescription, pa2.Job, job.ObjectDescription
FROM PHRPA0002 as pa2
INNER JOIN @t_org AS torg
	ON pa2.Organization = torg.org_id
LEFT JOIN PHRPA0001 AS pa1
	ON pa2.EmployeeID = pa1.EmployeeID
	AND pa1.StartDate <= @now AND pa1.EndDate >= @now
LEFT JOIN PHROM0001 AS pos
	ON pa2.Position = pos.ObjectID
	AND pos.ObjectClass = 'P'
	AND pos.StartDate <= @now AND pos.EndDate >= @now
LEFT JOIN PHROM0001 AS job
	ON pa2.Job = job.ObjectID
	AND job.ObjectClass = 'J'
	AND job.StartDate <= @now AND job.EndDate >= @now
WHERE pa2.StartDate <= @now AND pa2.EndDate >= @now 
	--AND pa2.EmployeeID IN ( '00000043','00000110','00000855')

DECLARE @t_result TABLE
(
	EmployeeID VARCHAR(20)
	,EmployeeName VARCHAR(500)
	,DivID VARCHAR(20)
	,DivDesc VARCHAR(500)
	,Organization VARCHAR(20)
	,OrgDesc VARCHAR(500)
	,Position VARCHAR(20)
	,PosDesc VARCHAR(500)
	,Job VARCHAR(20)
	,JobDesc VARCHAR(500)
	,RegID DECIMAL(18,0)
	,BizFlowID VARCHAR(100)
	,[Sequence] DECIMAL(18,0)
	,TaskIDRun VARCHAR(100)
	,TaskStatus VARCHAR(50)
	,ExecuteEmployeeID VARCHAR(50)
	,ExecuteEmpName VARCHAR(50)
)

DECLARE @t_pa47 TABLE
(
	EmployeeID VARCHAR(20)
	,EmployeeName VARCHAR(500)
	,DivID VARCHAR(20)
	,DivDesc VARCHAR(500)
	,Organization VARCHAR(20)
	,OrgDesc VARCHAR(500)
	,Position VARCHAR(20)
	,PosDesc VARCHAR(500)
	,Job VARCHAR(20)
	,JobDesc VARCHAR(500)
	,StartDate VARCHAR(20)
	,RegID DECIMAL(18,0)
	,BizFlowID VARCHAR(100)
	,[Sequence] DECIMAL(18,0)
	,TaskIDRun VARCHAR(100)
	,TaskStatus VARCHAR(50)
	,ExecuteEmployeeID VARCHAR(50)
	,ExecuteEmpName VARCHAR(50)
)

INSERT INTO @t_pa47
SELECT DISTINCT temp.*, pa47.StartDate, pa47.RegID, '' AS BizFlowID, 1 AS Sequence, '' AS TaskIDRun, 'F' AS TaskStatus, '' AS ExecuteEmployeeID, '' AS ExecuteEmpName
FROM PHRPA0047 AS pa47
INNER JOIN @t_emp AS temp
	ON pa47.EmployeeID = temp.EmployeeID
WHERE LEFT(StartDate,4) = @Period

DECLARE @t_wf TABLE
(
	RegID VARCHAR(20)
	,BizFlowID VARCHAR(250)
	,TaskRunID VARCHAR(250)
	,EmployeeID VARCHAR(20)
	,WorkFlowStatus VARCHAR(20)
)
INSERT INTO @t_wf
SELECT runtask.RegID, runtask.BizFlowID, TaskIDRun, ExecuteEmployeeID, WorkFlowStatus
FROM PCMWFRUNTASK AS runtask
INNER JOIN @t_emp AS temp
	ON runtask.ExecuteEmployeeID = temp.EmployeeID
INNER JOIN PCMWFRUNBIZDATA AS bizdata
	ON runtask.RegID = bizdata.RegID
WHERE (TaskIDRun = 'create') 
	AND runtask.BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
	AND LEFT(InboundDate,4) = @Period
	AND bizdata.FieldID = 'StartDate_47' AND LEFT(bizdata.Value,4) = @Period
	AND runtask.RegID NOT IN ( SELECT DISTINCT RegID FROM @t_pa47)
--order by EmployeeID, RegID, Sequence ASC

DECLARE @t_wf_det TABLE
(
	RegID VARCHAR(20)
	,BizFlowID VARCHAR(250)
	,Sequence DECIMAL(18,2)
	,TaskRunID VARCHAR(250)
	,TaskStatus VARCHAR(20)
	,ExecuteEmployeeID VARCHAR(20)
	,WorkFlowStatus VARCHAR(20)
	,EmployeeID VARCHAR(20)
)

DECLARE @t_wf_detail TABLE
(
	RegID VARCHAR(20)
	,BizFlowID VARCHAR(250)
	,Sequence DECIMAL(18,2)
	,TaskRunID VARCHAR(250)
	,TaskStatus VARCHAR(20)
	,ExecuteEmployeeID VARCHAR(20)
	,WorkFlowStatus VARCHAR(20)
	,EmployeeID VARCHAR(20)
)

INSERT INTO @t_wf_det
SELECT DISTINCT runtask.RegID, runtask.BizFlowID, runtask.Sequence, runtask.TaskIDRun, runtask.TaskStatus, runtask.ExecuteEmployeeID, runtask.WorkFlowStatus, twf.EmployeeID
FROM PCMWFRUNTASK AS runtask
INNER JOIN @t_wf AS twf
	ON runtask.RegID = twf.RegID
WHERE (runtask.TaskIDRun IN ('create ','apv_ds', 'apv_nha') OR runtask.WorkFlowStatus = 'F')
	AND runtask.BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
ORDER BY RegID ASC

--INSERT INTO @t_wf_detail
--SELECT * FROM @t_wf_det

INSERT INTO @t_wf_detail
SELECT tdet.RegID, tdet.BizFlowID, tdet.Sequence, tdet.TaskRunID, tdet.TaskStatus, tdet.ExecuteEmployeeID, tdet.WorkFlowStatus, tdet.EmployeeID
FROM @t_wf_det AS tdet
INNER JOIN ( SELECT RegID, BizFlowID, MAX(Sequence) AS MaxSeq FROM @t_wf_det GROUP BY RegID, BizFlowID ) AS tmax
	ON tdet.BizFlowID = tmax.BizFlowID
WHERE tdet.RegID = tmax.RegID AND tdet.Sequence = tmax.MaxSeq

INSERT INTO @t_wf_detail
SELECT DISTINCT pa47.RegID, '' AS BizFlowID, 1 AS Sequence, 'Finish' AS TaskIDRun, 'F' AS TaskStatus, '' AS ExecuteEmployeeID, 'F' AS WorkFlowStatus, pa47.EmployeeID
FROM @t_pa47 AS pa47
WHERE LEFT(StartDate,4) = @Period

DECLARE @t_emp_detail TABLE
(
	EmployeeID VARCHAR(20)
	,EmployeeName VARCHAR(500)
	,div_id VARCHAR(20)
	,div_name VARCHAR(500)
	,Organization VARCHAR(20)
	,OrgDesc VARCHAR(500)
	,Position VARCHAR(20)
	,PosDesc VARCHAR(500)
	,Job VARCHAR(20)
	,JobDesc VARCHAR(500)
	,TotCreate DECIMAL(22,0)
	,TotFinish DECIMAL(22,0)
	,EmpWFStatus VARCHAR(20)
	,TotApvDs DECIMAL(22,0)
	,TotApvNha DECIMAL(22,0)
)
INSERT INTO @t_emp_detail
SELECT temp.*, tcreate.TotCreate, tfinish.TotFinish, 
		CASE WHEN tcreate.TotCreate = tfinish.TotFinish THEN 'F' ELSE 'N' END AS EmpWFStatus, ISNULL(TotApvDs,0) AS TotApvDs, ISNULL(TotApvNha,0) AS TotApvNha
FROM @t_emp AS temp
LEFT JOIN ( SELECT EmployeeID, COUNT(DISTINCT RegID) AS TotCreate FROM @t_wf_detail group by EmployeeID ) AS tcreate
	ON temp.EmployeeID = tcreate.EmployeeID
LEFT JOIN ( SELECT EmployeeID, count(DISTINCT RegID) AS TotFinish FROM @t_wf_detail WHERE WorkFlowStatus = 'F' group by EmployeeID ) AS tfinish
	ON temp.EmployeeID = tfinish.EmployeeID
LEFT JOIN ( SELECT EmployeeID, count(DISTINCT RegID) AS TotApvDs FROM @t_wf_detail WHERE TaskRunID = 'apv_ds' group by EmployeeID ) AS tapv_ds
	ON temp.EmployeeID = tapv_ds.EmployeeID
LEFT JOIN ( SELECT EmployeeID, count(DISTINCT RegID) AS TotApvNha FROM @t_wf_detail WHERE TaskRunID = 'apv_nha' group by EmployeeID ) AS tapv_nha
	ON temp.EmployeeID = tapv_nha.EmployeeID
WHERE div_id <> ''


SELECT tdiv.*, ISNULL((CONVERT(DECIMAL(22,2),(TotalEmpFinish/TotalEmp)) * 100),0) AS Persentase, 
		ISNULL(tfinish.TotalEmpFinish,0) AS TotalEmpFinish, ISNULL(tcreate.TotCreate,0) AS TotCreate, ISNULL(tapvds.TotApvDs,0) AS TotApvDs, ISNULL(tapvnha.TotApvNha,0) AS TotApvNha
FROM (SELECT div_id, div_name, CONVERT(DECIMAL(22,2),COUNT(EmployeeID)) AS TotalEmp FROM @t_emp_detail GROUP BY div_id, div_name) AS tdiv
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotalEmpFinish FROM @t_emp_detail WHERE EmpWFStatus = 'F' GROUP BY div_id, div_name) AS tfinish
	ON tdiv.div_id = tfinish.div_id
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotCreate FROM @t_emp_detail WHERE TotCreate > 0 GROUP BY div_id, div_name) AS tcreate
	ON tdiv.div_id = tcreate.div_id
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotApvDs FROM @t_emp_detail WHERE TotApvDs > 0 GROUP BY div_id, div_name) AS tapvds
	ON tdiv.div_id = tapvds.div_id
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotApvNha FROM @t_emp_detail WHERE TotApvNha > 0 GROUP BY div_id, div_name) AS tapvnha
	ON tdiv.div_id = tapvnha.div_id