Untitled

CREATE PROCEDURE [dbo].[proc_rpt_GlobalStatusReport]
    @FyId INT = 4, 
    @MonthId INT = 157, 
    @Conformity INT = NULL, 
    @roleID INT = NULL, 
    @Win_User VARCHAR(50) = 'muskan.jain'
AS
BEGIN
    -- Drop temp tables if they exist
    IF OBJECT_ID(N'tempdb..#tblResult', N'U') IS NOT NULL 
    BEGIN 
        DROP TABLE #tblResult 
    END 

    IF OBJECT_ID(N'tempdb..#tblResult2', N'U') IS NOT NULL 
    BEGIN 
        DROP TABLE #tblResult2 
    END

    -- Declare variables
    DECLARE @month VARCHAR(50),
            @monthNo INT,
            @roleID INT,
            @ScheduleCount INT,
            @sepratedDate INT,
            @sepratedMonth INT,
            @ScheduledId INT,
            @FullDate NVARCHAR(50),
            @tblCOuntForFirst INT,
            @valueForFirst INT,
            @Plant_Id_For_First INT,
            @tblCOuntForSecond INT,
            @valueForSecond INT,
            @updatedtblCOuntForSecond INT,
            @pidcountForSecond INT,
            @tblCOuntForThird INT,
            @valueForThird FLOAT,
            @updatedtblCOuntForThird INT,
            @pidcountForThird INT,
            @tblCOuntForFourth INT,
            @valueForFourth FLOAT,
            @cummValueForFourth FLOAT,
            @finalCummValueFourth INT,
            @updatedtblCOuntForFourth INT,
            @pidcountForFourth INT,
            @tblCOuntForFifth INT,
            @valueForFifth FLOAT,
            @sumvalueforfifth FLOAT,
            @updatedtblCOuntForFifth INT,
            @pidcountForFifth INT,
            @tblCOuntForSixth INT,
            @valueForSixth FLOAT,
            @cummValueForSixth FLOAT,
            @finalCummValueforYTMSixth INT,
            @updatedtblCOuntForSixth INT,
            @pidcountForSixth INT;

    -- Get month and month number
    SET @month = (SELECT Month FROM mst_fy_calendar WHERE ID = @MonthId);
    SET @monthNo = (SELECT MonthNo FROM mst_fy_calendar WHERE ID = @MonthId);

    -- Set roleID
    SET @roleID = (SELECT TOP 1 RoleID 
                   FROM mst_user_mapping 
                   WHERE WinUser = @Win_User 
                   ORDER BY RoleID ASC);

    -- Create temp tables
    CREATE TABLE #tblResult2 (
        Pid INT IDENTITY PRIMARY KEY,
        ScheduleID INT,
        PlanID INT,
        ScheduleWeekId INT,
        ScheduleDate INT,
        ScheduleMonthNo INT
    );

    -- Insert initial values into #tblResult2
    INSERT INTO #tblResult2(ScheduleID, PlanID, ScheduleWeekId, ScheduleDate, ScheduleMonthNo)
    SELECT S1.ScheduleID, S1.PlanID, S1.ScheduleWeekId, 0, 0
    FROM (SELECT ScheduleID, PlanID, ScheduleWeekID 
          FROM tran_auditPlan_schedule) S1;

    -- Calculate schedule count
    SET @ScheduleCount = (SELECT COUNT(1) FROM #tblResult2);

    -- Process each schedule
    WHILE (@ScheduleCount > 0)
    BEGIN
        SET @ScheduledId = (SELECT TOP 1 ScheduleID 
                            FROM #tblResult2 
                            WHERE Pid = @ScheduleCount);
        SET @FullDate = (SELECT ScheduleWeekDate 
                         FROM tran_auditPlan_schedule 
                         WHERE ScheduleID = @ScheduledId);
        SET @sepratedMonth = (SELECT MONTH(@FullDate));
        SET @sepratedDate = SUBSTRING(@FullDate, CHARINDEX('-', @FullDate) + 4, 2);
        
        -- Update #tblResult2 with separated date and month
        UPDATE #tblResult2 
        SET ScheduleDate = @sepratedDate, ScheduleMonthNo = @sepratedMonth 
        WHERE ScheduleID = @ScheduledId;

        SET @ScheduleCount = @ScheduleCount - 1;
    END

    -- Create #tblResult and #tblResult3 temp tables
    CREATE TABLE #tblResult (
        Pid INT IDENTITY PRIMARY KEY,
        SerialNo INT,
        Month VARCHAR(255),
        KPI VARCHAR(255),
        PlantId INT,
        PlantName VARCHAR(255),
        Value VARCHAR(255)
    );

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

    -- Insert data based on roleID into #tblResult3
    IF (@roleID = 1)
    BEGIN
        INSERT INTO #tblResult3 (PlantIdAuth, PlantNameAuth)
        SELECT DISTINCT mpc.PlantID AS PlantIdAuth, mpc.PlantName AS PlantNameAuth
        FROM mst_plant AS mpc;
    END
    ELSE
    BEGIN
        INSERT INTO #tblResult3 (PlantIdAuth, PlantNameAuth)
        SELECT DISTINCT MUP.PlantID AS PlantIdAuth, MP.PlantName AS PlantNameAuth
        FROM mst_user_mapping AS MUP
        INNER JOIN mst_plant AS MP ON MUP.PlantID = MP.PlantID
        WHERE MUP.WinUser = @Win_User;
    END

    -- Process and insert first KPI: Planned Audits-Current Month
    INSERT INTO #tblResult(SerialNo, Month, KPI, PlantId, PlantName, Value)
    SELECT T1.SerialNo, T1.Month, T1.KPI, T1.PlantID, T1.PlantName, CAST(0 AS VARCHAR) + '(' + CAST(0 AS VARCHAR) + ')'
    FROM (SELECT DISTINCT 1 AS SerialNo, @month AS Month, 'Planned Audits-Current Month' AS KPI, 
                 mpc.PlantIDAuth AS PlantID, mpc.PlantNameAuth AS PlantName 
          FROM #tblResult3 AS mpc) T1;

    -- Process first KPI values
    SET @tblCOuntForFirst = (SELECT COUNT(1) FROM #tblResult);
    WHILE (@tblCOuntForFirst > 0)
    BEGIN
        SET @Plant_Id_For_First = (SELECT TOP 1 PlantId FROM #tblResult WHERE Pid = @tblCOuntForFirst);
        SET @valueForFirst = (SELECT COUNT(1)
                             FROM #tblResult2 AS TS
                             INNER JOIN tran_auditPlan AS TP ON TP.PlanID = TS.PlanID
                             INNER JOIN tran_auditProgram AS TAP ON TAP.AuditProgramID = TP.AuditProgramID
                             WHERE TP.PlantID = @Plant_Id_For_First
                             AND TS.ScheduleMonthNo = @monthNo
                             AND TP.FyID = @FyId
                             AND TP.IsActive = 1
                             AND TAP.IsActive = 1);
        
        -- Update first KPI value
        UPDATE #tblResult
        SET Value = ISNULL(CAST(@valueForFirst AS VARCHAR), CAST(0 AS VARCHAR))
        WHERE PlantId = @Plant_Id_For_First;

        SET @tblCOuntForFirst = @tblCOuntForFirst - 1;
    END

    -- Process second KPI: Scheduled Audit Value
    SET @tblCOuntForSecond = (SELECT COUNT(1) FROM #tblResult);
    WHILE (@tblCOuntForSecond > 0)
    BEGIN
        SET @Plant_Id_For_Second = (SELECT TOP 1 PlantId FROM #tblResult WHERE Pid = @tblCOuntForSecond);
        SET @valueForSecond = (SELECT COUNT(1)
                              FROM #tblResult2 AS TS
                              INNER JOIN tran_auditPlan AS TP ON TP.PlanID = TS.PlanID
                              WHERE TP.PlantID = @Plant_Id_For_Second
                              AND TS.ScheduleMonthNo = @monthNo
                              AND TP.FyID = @FyId
                              AND TP.IsActive = 1);
        
        -- Update second KPI value
        UPDATE #tblResult
        SET Value = ISNULL(CAST(@valueForSecond AS VARCHAR), CAST(0 AS VARCHAR))
        WHERE PlantId = @Plant_Id_For_Second;

        SET @tblCOuntForSecond = @tblCOuntForSecond - 1;
    END

    -- Repeat similar logic for the third, fourth, fifth, and sixth KPIs
    -- This includes their value calculations and updates based on the logic for each KPI

    -- Example for third KPI (update as necessary)
    SET @tblCOuntForThird = (SELECT COUNT(1) FROM #tblResult);
    WHILE (@tblCOuntForThird > 0)
    BEGIN
        SET @Plant_Id_For_Third = (SELECT TOP 1 PlantId FROM #tblResult WHERE Pid = @tblCOuntForThird);
        SET @valueForThird = (SELECT COUNT(1)
                             FROM #tblResult2 AS TS
                             INNER JOIN tran_auditPlan AS TP ON TP.PlanID = TS.PlanID
                             WHERE TP.PlantID = @Plant_Id_For_Third
                             AND TS.ScheduleMonthNo = @monthNo
                             AND TP.FyID = @FyId
                             AND TP.IsActive = 1);
        
        -- Update third KPI value
        UPDATE #tblResult
        SET Value = ISNULL(CAST(@valueForThird AS VARCHAR), CAST(0 AS VARCHAR))
  -- Third KPI: Audit Planned YTM
    SET @tblCOuntForThird = (SELECT COUNT(1) FROM #tblResult);
    WHILE (@tblCOuntForThird > 0)
    BEGIN
        SET @Plant_Id_For_Third = (SELECT TOP 1 PlantId FROM #tblResult WHERE Pid = @tblCOuntForThird);
        SET @valueForThird = (SELECT COUNT(1)
                             FROM #tblResult2 AS TS
                             INNER JOIN tran_auditPlan AS TP ON TP.PlanID = TS.PlanID
                             WHERE TP.PlantID = @Plant_Id_For_Third
                             AND TS.ScheduleMonthNo <= @monthNo
                             AND TP.FyID = @FyId
                             AND TP.IsActive = 1);
        
        -- Update third KPI value
        UPDATE #tblResult
        SET Value = ISNULL(CAST(@valueForThird AS VARCHAR), CAST(0 AS VARCHAR))
        WHERE PlantId = @Plant_Id_For_Third;

        SET @tblCOuntForThird = @tblCOuntForThird - 1;
    END

    -- Fourth KPI: Audit Completed YTM
    SET @tblCOuntForFourth = (SELECT COUNT(1) FROM #tblResult);
    WHILE (@tblCOuntForFourth > 0)
    BEGIN
        SET @Plant_Id_For_Fourth = (SELECT TOP 1 PlantId FROM #tblResult WHERE Pid = @tblCOuntForFourth);
        SET @valueForFourth = (SELECT COUNT(1)
                              FROM #tblResult2 AS TS
                              INNER JOIN tran_auditPlan AS TP ON TP.PlanID = TS.PlanID
                              WHERE TP.PlantID = @Plant_Id_For_Fourth
                              AND TS.ScheduleMonthNo <= @monthNo
                              AND TP.FyID = @FyId
                              AND TP.IsActive = 1
                              AND TP.AuditStatus = 'Completed');
        
        -- Update fourth KPI value
        UPDATE #tblResult
        SET Value = ISNULL(CAST(@valueForFourth AS VARCHAR), CAST(0 AS VARCHAR))
        WHERE PlantId = @Plant_Id_For_Fourth;

        -- Cumulative value for fourth KPI
        SET @cummValueForFourth = (SELECT SUM(CAST(Value AS INT)) 
                                   FROM #tblResult
                                   WHERE PlantId = @Plant_Id_For_Fourth 
                                   AND KPI = 'Audit Completed YTM');
        
        -- Final cumulative value for fourth KPI
        SET @finalCummValueFourth = @cummValueForFourth;
        
        -- Update final cumulative value for fourth KPI
        UPDATE #tblResult
        SET Value = CAST(@finalCummValueFourth AS VARCHAR)
        WHERE PlantId = @Plant_Id_For_Fourth 
        AND KPI = 'Audit Completed YTM';

        SET @tblCOuntForFourth = @tblCOuntForFourth - 1;
    END

    -- Fifth KPI: Audit Results Achieved
    SET @tblCOuntForFifth = (SELECT COUNT(1) FROM #tblResult);
    WHILE (@tblCOuntForFifth > 0)
    BEGIN
        SET @Plant_Id_For_Fifth = (SELECT TOP 1 PlantId FROM #tblResult WHERE Pid = @tblCOuntForFifth);
        SET @valueForFifth = (SELECT COUNT(1)
                             FROM #tblResult2 AS TS
                             INNER JOIN tran_auditPlan AS TP ON TP.PlanID = TS.PlanID
                             WHERE TP.PlantID = @Plant_Id_For_Fifth
                             AND TS.ScheduleMonthNo <= @monthNo
                             AND TP.FyID = @FyId
                             AND TP.IsActive = 1
                             AND TP.AuditStatus = 'Achieved');
        
        -- Update fifth KPI value
        UPDATE #tblResult
        SET Value = ISNULL(CAST(@valueForFifth AS VARCHAR), CAST(0 AS VARCHAR))
        WHERE PlantId = @Plant_Id_For_Fifth;

        -- Cumulative value for fifth KPI
        SET @sumvalueforfifth = (SELECT SUM(CAST(Value AS FLOAT)) 
                                FROM #tblResult
                                WHERE PlantId = @Plant_Id_For_Fifth 
                                AND KPI = 'Audit Results Achieved');
        
        -- Update cumulative value for fifth KPI
        UPDATE #tblResult
        SET Value = CAST(@sumvalueforfifth AS VARCHAR)
        WHERE PlantId = @Plant_Id_For_Fifth 
        AND KPI = 'Audit Results Achieved';

        SET @tblCOuntForFifth = @tblCOuntForFifth - 1;
    END

    -- Sixth KPI: Audit Completion Rate
    SET @tblCOuntForSixth = (SELECT COUNT(1) FROM #tblResult);
    WHILE (@tblCOuntForSixth > 0)
    BEGIN
        SET @Plant_Id_For_Sixth = (SELECT TOP 1 PlantId FROM #tblResult WHERE Pid = @tblCOuntForSixth);
        SET @valueForSixth = (SELECT COUNT(1)
                             FROM #tblResult2 AS TS
                             INNER JOIN tran_auditPlan AS TP ON TP.PlanID = TS.PlanID
                             WHERE TP.PlantID = @Plant_Id_For_Sixth
                             AND TS.ScheduleMonthNo <= @monthNo
                             AND TP.FyID = @FyId
                             AND TP.IsActive = 1
                             AND TP.AuditStatus = 'Completed');
        
        -- Update sixth KPI value
        UPDATE #tblResult
        SET Value = ISNULL(CAST(@valueForSixth AS VARCHAR), CAST(0 AS VARCHAR))
        WHERE PlantId = @Plant_Id_For_Sixth;

        -- Cumulative value for sixth KPI
        SET @cummValueForSixth = (SELECT SUM(CAST(Value AS FLOAT)) 
                                  FROM #tblResult
                                  WHERE PlantId = @Plant_Id_For_Sixth 
                                  AND KPI = 'Audit Completion Rate');
        
        -- Final cumulative value for sixth KPI
        SET @finalCummValueforYTMSixth = @cummValueForSixth;
        
        -- Update final cumulative value for sixth KPI
        UPDATE #tblResult
        SET Value = CAST(@finalCummValueforYTMSixth AS VARCHAR)
        WHERE PlantId = @Plant_Id_For_Sixth 
        AND KPI = 'Audit Completion Rate';

        SET @tblCOuntForSixth = @tblCOuntForSixth - 1;
    END

    -- Final results from #tblResult can be selected or further processed
    SELECT * FROM #tblResult;

END;