SELECT
OLevel,
WorkOrgID,
WorkOrgName,
PlanNum,
PlanFinishNum,
PlanUnFinishNum,
PlanCanceledNum,
PlanChangedNum,
OrtherNum,
PlanTime,
ActualTime,
RIGHT(PlanIDs,LEN(PlanIDs)-1) AS PlanIDs
FROM (SELECT
( CASE MIN(Parent)
WHEN 'E13744A1-7E2C-42E8-8E7B-4D52F9E04A08' THEN MIN(ROLevel)
ELSE MIN(OLevel)
END ) AS OLevel ,
( CASE MIN(Parent)
WHEN 'E13744A1-7E2C-42E8-8E7B-4D52F9E04A08'
THEN MIN(RegulatoryOrgID)
ELSE WorkOrgID
END ) AS WorkOrgID ,
( CASE MIN(Parent)
WHEN 'E13744A1-7E2C-42E8-8E7B-4D52F9E04A08' THEN MIN(RoName)
ELSE WorkOrgName
END ) AS WorkOrgName ,
MIN(RegulatoryOrgID) AS RegulatoryOrgID ,
COUNT(1) AS PlanNum ,
SUM(CASE FormStatusCode
WHEN 'Finish' THEN 1
ELSE 0
END) AS PlanFinishNum ,
COUNT(CASE WHEN FormStatusCode != 'Finish' THEN 1
ELSE 0
END) AS PlanUnFinishNum ,
SUM(CASE WHEN FormStatusCode = 'Canceled' THEN 1
ELSE 0
END) AS PlanCanceledNum ,
SUM(CASE WHEN FormStatusCode = 'Changed' THEN 1
ELSE 0
END) AS PlanChangedNum ,
SUM(CASE WHEN FormStatusCode IN ( 'Approved', 'Begining', 'Begin' )
THEN 1
ELSE 0
END) AS OrtherNum ,
SUM(CASE WHEN DATEDIFF(mi, PlanBeginTime, PlanEndTime) < 0 THEN 0
WHEN DATEDIFF(mi, PlanBeginTime, PlanEndTime) IS NULL THEN 0
ELSE DATEDIFF(mi, PlanBeginTime, PlanEndTime)
END) AS PlanTime ,
SUM(CASE WHEN DATEDIFF(mi, CheckInApproveTime, CheckOutApproveTime) < 0
THEN 0
WHEN DATEDIFF(mi, CheckInApproveTime, CheckOutApproveTime) IS NULL
THEN 0
ELSE DATEDIFF(mi, CheckInApproveTime, CheckOutApproveTime)
END) AS ActualTime ,
( SELECT ',' +''''+ CAST(wp.PlanID AS VARCHAR(40)) +''''
FROM dbo.PL_WorkPlan wp
LEFT JOIN dbo.PL_WorkDetail wd ON wd.PlanID = wp.PlanID
LEFT JOIN dbo.PT_WorkPoint pp ON wp.PlanID = pp.PlanID
INNER JOIN dbo.SEC_Organization o ON o.Id = wd.WorkOrgID
LEFT JOIN dbo.SEC_Organization RO ON ro.Id = wd.RegulatoryOrgID
WHERE wp.FormStatusCode NOT IN ( 'Draft', 'Approving' )
AND wd.WorkOrgName IS NOT NULL
AND ( WorkOrgID = V.WorkOrgID )
FOR
XML PATH('')
) PlanIDs
FROM ( SELECT wd.WorkTypeCode ,
wp.planid ,
wp.PlanBeginTime ,
wp.PlanEndTime ,
WP.FormStatusCode ,
PlanTypeCode ,
wd.WorkOrgID ,
wd.RegulatoryOrgID ,
o.Parent ,
wd.WorkOrgName ,
o.[Level] AS OLevel ,
RO.[Level] AS ROLevel,
Ro.NAME AS RoName,
CheckInApproveTime ,
CheckOutApproveTime
FROM dbo.PL_WorkPlan wp
LEFT JOIN dbo.PL_WorkDetail wd ON wd.PlanID = wp.PlanID
LEFT JOIN dbo.PT_WorkPoint pp ON wp.PlanID = pp.PlanID
INNER JOIN dbo.SEC_Organization o ON o.Id = wd.WorkOrgID
LEFT JOIN dbo.SEC_Organization RO ON ro.Id = wd.RegulatoryOrgID
WHERE wp.FormStatusCode NOT IN ( 'Draft', 'Approving' )
AND wd.WorkOrgName IS NOT NULL
) V
WHERE 1 = 1
and WorkTypeCode in ($WorkTypeCode$)
and V.PlanBeginTime >= @BeginTime
and V.PlanBeginTime < @EndTime
{? and V.LineID = @LineID }
{? and V.PlanTypeCode = @PlanTypeCode }
{? and V.WorkOrgID = @WorkOrgID }
GROUP BY
WorkOrgID ,
WorkOrgName)
as V2
WHERE V2.WorkOrgID !='00000000-0000-0000-0000-000000000000'