Untitled

CREATE PROC [dbo].[proc_rpt_GlobalStatusReport] 
(
    @FyId INT = NULL, 
    @MonthId INT = NULL, 
    @Conformity INT = NULL, 
    @roleID INT = NULL, 
    @Win_User VARCHAR(50)
)
AS
BEGIN
    -- Cleanup temp tables
    IF OBJECT_ID(N'tempdb..#tblResult', N'U') IS NOT NULL DROP TABLE #tblResult
    IF OBJECT_ID(N'tempdb..#tblResult2', N'U') IS NOT NULL DROP TABLE #tblResult2

    -- Variables
    DECLARE @month VARCHAR(50), @monthNo INT

    -- Fetch Month and MonthNo
    SELECT @month = Month, @monthNo = MonthNo
    FROM mst_fy_calendar
    WHERE ID = @MonthId

    -- Assign RoleID
    SELECT TOP 1 @roleID = RoleID 
    FROM mst_user_mapping 
    WHERE WinUser = @Win_User
    ORDER BY RoleID ASC

    -- Temporary table #tblResult2
    CREATE TABLE #tblResult2 
    (
        Pid INT IDENTITY PRIMARY KEY, 
        ScheduleID INT, 
        PlanID INT, 
        ScheduleWeekId INT, 
        ScheduleDate INT, 
        ScheduleMonthNo INT
    )

    INSERT INTO #tblResult2 (ScheduleID, PlanID, ScheduleWeekId, ScheduleDate, ScheduleMonthNo)
    SELECT ScheduleID, PlanID, ScheduleWeekID, 0, 0
    FROM tran_auditPlan_schedule

    -- Update #tblResult2 with ScheduleDate and ScheduleMonthNo
    UPDATE #tblResult2
    SET 
        ScheduleDate = DATEPART(DAY, s.ScheduleWeekDate),
        ScheduleMonthNo = DATEPART(MONTH, s.ScheduleWeekDate)
    FROM #tblResult2 t
    INNER JOIN tran_auditPlan_schedule s ON t.ScheduleID = s.ScheduleID

    -- Temporary table #tblResult
    CREATE TABLE #tblResult 
    (
        Pid INT IDENTITY PRIMARY KEY, 
        SerialNo INT, 
        Month VARCHAR(255), 
        KPI VARCHAR(255), 
        PlantId INT, 
        PlantName VARCHAR(255), 
        Value VARCHAR(255)
    )

    -- Temporary table #tblResult3
    CREATE TABLE #tblResult3 
    (
        Pid INT IDENTITY PRIMARY KEY, 
        PlantIdAuth INT, 
        PlantNameAuth VARCHAR(255)
    )

    -- Populate #tblResult3 based on role
    IF (@roleID = 1)
    BEGIN
        INSERT INTO #tblResult3 (PlantIdAuth, PlantNameAuth)
        SELECT DISTINCT PlantID, PlantName
        FROM mst_plant
    END
    ELSE
    BEGIN
        INSERT INTO #tblResult3 (PlantIdAuth, PlantNameAuth)
        SELECT DISTINCT mup.PlantID, mp.PlantName
        FROM mst_user_mapping mup
        INNER JOIN mst_plant mp ON mup.PlantID = mp.PlantID
        WHERE mup.WinUser = @Win_User
    END

    -- Insert KPIs
    INSERT INTO #tblResult (SerialNo, Month, KPI, PlantId, PlantName, Value)
    SELECT 
        SerialNo,
        @month AS Month,
        KPI,
        PlantIdAuth AS PlantId,
        PlantNameAuth AS PlantName,
        '0 (0)' AS Value
    FROM (
        SELECT 1 AS SerialNo, 'Planned Audits-Current Month' AS KPI
        UNION ALL
        SELECT 2, 'Planned YTM'
        UNION ALL
        SELECT 3, 'Overdue Audits-Current Month'
        UNION ALL
        SELECT 4, 'Overdue rate'
        UNION ALL
        SELECT 5, 'Overdue Audits-YTM'
        UNION ALL
        SELECT 6, 'Overdue rate-YTM'
    ) AS KPIs
    CROSS JOIN #tblResult3

    -- Update KPI values using set-based operations
    UPDATE r
    SET Value = CAST(c.Value AS VARCHAR) + ' (' + CAST(c.CumulativeValue AS VARCHAR) + ')'
    FROM #tblResult r
    CROSS APPLY (
        SELECT 
            COUNT(*) AS Value,
            COUNT(*) OVER () AS CumulativeValue
        FROM #tblResult2 ts
        INNER JOIN tran_auditPlan tp ON ts.PlanID = tp.PlanID
        INNER JOIN tran_auditProgram tap ON tp.AuditProgramID = tap.AuditProgramID
        WHERE 
            tp.PlantID = r.PlantId
            AND ts.ScheduleMonthNo = @monthNo
            AND tp.FyID = ISNULL(@FyId, tp.FyID)
            AND tap.ConformityID = ISNULL(@Conformity, tap.ConformityID)
    ) c

    -- Return final result
    SELECT SerialNo, Month, KPI, PlantId, PlantName, Value
    FROM #tblResult
    ORDER BY SerialNo, PlantId

    -- Cleanup
    DROP TABLE #tblResult
    DROP TABLE #tblResult2
    DROP TABLE #tblResult3
END