
drop procedure ActivityAlertDetails



CREATE PROCEDURE ActivityAlertDetails(
	IN `p_emp_code` VARCHAR(255),
	IN `p_page` INT,
	IN `p_page_size` INT,
	IN `p_ignore_paging` BOOLEAN,
	IN `p_filter` VARCHAR(255),
	IN `p_login_time` DATETIME,
	IN `p_logout_time` DATETIME,
	IN `p_sort_column` VARCHAR(50),
	IN `p_sort_type` VARCHAR(4)
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);
    
    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'login_time';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'LoginTime' THEN
        SET p_sort_column = 'login_time';  -- Map "LoginTime" to the correct column name "login_time"
    ELSEIF p_sort_column = 'LogoutTime' THEN
        SET p_sort_column = 'logout_time';  -- Map "LogoutTime" to the correct column name "logout_time"
    ELSEIF p_sort_column = 'SystemStatus' THEN
        SET p_sort_column = 'system_status';  -- Map "SystemStatus" to the correct column name "system_status"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;
   
    DROP TABLE IF EXISTS temp_filtered_results;
   
    CREATE TABLE temp_filtered_results (
        `id` int,
        `emp_code` varchar(255) NOT NULL,
        `login_time` datetime,
        `logout_time` datetime,
        `system_status` varchar(255),
        `ip_address` varchar(15),
        `computer_name` varchar(255),
        `app_version` varchar(255),
        `created_on` timestamp NULL,
        `updated_on` timestamp NULL
    );
	   
    -- Insert filtered results into the temporary table
    INSERT INTO temp_filtered_results (id, emp_code, login_time, logout_time, system_status, ip_address, created_on, updated_on)
    SELECT 
        id AS Id,
        emp_code AS EmpCode,
        login_time AS LoginTime,
        logout_time AS LogoutTime,
        system_status AS SystemStatus,
        ip_address AS IpAddress,
        created_on AS CreatedOn,
        updated_on AS UpdatedOn        
    FROM 
        system_login_details
    WHERE     
        (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'));

    -- Additional filtering for Login/Logout status
    INSERT INTO temp_filtered_results (id, emp_code, login_time, logout_time, system_status, ip_address, created_on, updated_on)
    SELECT 
        id AS Id,
        emp_code AS EmpCode,
        login_time AS LoginTime,
        logout_time AS LogoutTime,
        CASE 
            WHEN login_time IS NOT NULL THEN 'Logged In'
            ELSE ''
        END AS SystemStatus,
        ip_address AS IpAddress,
        created_on AS CreatedOn,
        updated_on AS UpdatedOn        
    FROM 
        system_login_details
    WHERE 
        (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'));

    INSERT INTO temp_filtered_results (id, emp_code, login_time, logout_time, system_status, ip_address, created_on, updated_on)
    SELECT 
        id AS Id,
        emp_code AS EmpCode,
        login_time AS LoginTime,
        logout_time AS LogoutTime,
        CASE 
            WHEN logout_time IS NOT NULL THEN 'Logged Out'
            ELSE ''
        END AS SystemStatus,
        ip_address AS IpAddress,
        created_on AS CreatedOn,
        updated_on AS UpdatedOn        
    FROM 
        system_login_details
    WHERE 
        (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'));   

    -- Filter based on p_filter, p_login_time, and p_logout_time
    CREATE TEMPORARY TABLE temp_filtered2_results AS
    SELECT      
        id,
        emp_code,
        login_time,
        logout_time,
        system_status,
        ip_address,        
        created_on,
        updated_on,
        v_total_records AS Total_Records,
        v_total_pages AS Total_Pages
    FROM
        temp_filtered_results
    WHERE 
        (p_filter IS NULL OR system_status = p_filter)
        AND (p_login_time IS NULL OR DATE(login_time) >= DATE(p_login_time))
        AND (p_logout_time IS NULL OR DATE(login_time) <= DATE(p_logout_time));
                         
    -- Calculate the total number of records from the filtered table
    SELECT COUNT(*) INTO v_total_records FROM temp_filtered2_results;

    -- Calculate the total number of pages
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the filtered and paginated results
    IF NOT p_ignore_paging THEN
        SET @query = CONCAT('SELECT id AS Id, emp_code AS EmpCode, login_time AS LoginTime, logout_time AS LogoutTime, system_status AS SystemStatus, ip_address AS IpAddress, created_on AS CreatedOn, updated_on AS UpdatedOn, ', 
                            v_total_records, ' AS Total_Records, ', 
                            v_total_pages, ' AS Total_Pages FROM temp_filtered2_results ', 
                            v_sort_query, v_limit_query);
    ELSE
        SET @query = CONCAT('SELECT id AS Id, emp_code AS EmpCode, login_time AS LoginTime, logout_time AS LogoutTime, system_status AS SystemStatus, ip_address AS IpAddress, created_on AS CreatedOn, updated_on AS UpdatedOn, ', 
                            v_total_records, ' AS Total_Records, ', 
                            v_total_pages, ' AS Total_Pages FROM temp_filtered2_results ', 
                            v_sort_query);
    END IF;

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Drop the temporary tables
    DROP TEMPORARY TABLE IF EXISTS temp_filtered_results;
    DROP TEMPORARY TABLE IF EXISTS temp_filtered2_results;
END




**********************


drop procedure GetActivityDetails


CREATE PROCEDURE GetActivityDetails(
IN p_Type INT
)
BEGIN
   select * from activity_details where Type = p_Type;
END



*************************

drop procedure GetApplicationChart
 
 
CREATE PROCEDURE GetApplicationChart(
    IN p_emp_code VARCHAR(100),
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP,
    IN p_show VARCHAR(100),
    IN p_device VARCHAR(100),
    IN p_chartType VARCHAR(100),
    IN p_type VARCHAR(100)
)
BEGIN
	-- Check if p_started_on and p_ended_on are the same
    IF (p_started_on IS NOT NULL AND p_ended_on IS NOT NULL AND p_started_on = p_ended_on) THEN
        -- Add one day to p_ended_on
        SET p_ended_on = DATE_ADD(p_ended_on, INTERVAL 1 DAY);
    END IF;
	
    IF p_type = 'Applications' THEN
        -- Calculate the total number of records grouped by date
        SELECT 
            COUNT(*) AS ApplicationCount,
            CAST(started_on AS DATE) AS StartedOn, 
            '' AS ApplicationName
        FROM 
            running_application_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.started_on >= p_started_on) 
            AND (p_ended_on IS NULL OR rad.started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'))
        GROUP BY 
            CAST(started_on AS DATE);
    ELSEIF p_type = 'Application(Top 10)' THEN
        -- Calculate the total number of records grouped by application name
        SELECT 
            COUNT(application_name) AS ApplicationCount,
            application_name AS ApplicationName,
            null AS StartedOn
        FROM 
            running_application_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.started_on >= p_started_on)
            AND (p_ended_on IS NULL OR rad.started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'))
        GROUP BY 
            application_name
        ORDER BY 
            ApplicationCount DESC 
        LIMIT 10;
    END IF;
END



****************

drop procedure GetCallDetails


CREATE PROCEDURE GetCallDetails(
    IN p_emp_code VARCHAR(100),
    IN p_call_started_on TIMESTAMP,
    IN p_call_ended_on TIMESTAMP,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Added parameter for sort column
    IN p_sort_type VARCHAR(4)      -- Added parameter for sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);
    
    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'call_started_on';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'CallWith' THEN
        SET p_sort_column = 'call_with';  -- Map "CallWith" to the correct column name "call_with"
    ELSEIF p_sort_column = 'CallStartedOn' THEN
        SET p_sort_column = 'call_started_on';  -- Map "CallStartedOn" to the correct column name "call_started_on"
    ELSEIF p_sort_column = 'CallEndedOn' THEN
        SET p_sort_column = 'call_ended_on';  -- Map "CallEndedOn" to the correct column name "call_ended_on"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT('SELECT COUNT(*) INTO @v_total_records FROM call_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND emp_code LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_call_started_on IS NOT NULL, CONCAT('AND DATE(call_started_on) >= DATE("', p_call_started_on, '") '), ''),
                        IF(p_call_ended_on IS NOT NULL, CONCAT('AND DATE(call_started_on) <= DATE("', p_call_ended_on, '") '), ''));
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the records with pagination if paging is not ignored
    SET @query = CONCAT('SELECT id, emp_code, call_with, call_started_on, call_ended_on, created_on, updated_on, ',
                        v_total_records, ' AS total_records, ', 
                        v_total_pages, ' AS total_pages FROM call_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND emp_code LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_call_started_on IS NOT NULL, CONCAT('AND DATE(call_started_on) >= DATE("', p_call_started_on, '") '), ''),
                        IF(p_call_ended_on IS NOT NULL, CONCAT('AND DATE(call_started_on) <= DATE("', p_call_ended_on, '") '), ''),
                        v_sort_query, 
                        v_limit_query);

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END



*********************
drop procedure GetCallsChart


CREATE PROCEDURE GetCallsChart(
    IN p_emp_code VARCHAR(100),
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP,
    IN p_show VARCHAR(100),
    IN p_device VARCHAR(100),
    IN p_chartType VARCHAR(100),
    IN p_type VARCHAR(100)
)
BEGIN
	
		-- Check if p_started_on and p_ended_on are the same
    IF (p_started_on IS NOT NULL AND p_ended_on IS NOT NULL AND p_started_on = p_ended_on) THEN
        -- Add one day to p_ended_on
        SET p_ended_on = DATE_ADD(p_ended_on, INTERVAL 1 DAY);
    END IF;
	
    IF p_type = 'Calls' THEN
        -- Calculate the total number of records grouped by date
        SELECT 
            COUNT(*) AS CallsCount,
            CAST(call_started_on AS DATE) AS StartedOn, 
            NULL AS callwith,
            rad.emp_code AS EmpCode
        FROM 
            call_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.call_started_on >= p_started_on)
            AND (p_ended_on IS NULL OR rad.call_started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(rad.emp_code) LIKE CONCAT('%', lower(p_emp_code), '%'))
        GROUP BY 
            CAST(call_started_on AS DATE), rad.emp_code;

    ELSEIF p_type = 'Calls(Top 10)' THEN
        -- Calculate the total number of records grouped by employee code
        SELECT 
            COUNT(rad.emp_code) AS CallsCount,
            rad.emp_code AS EmpCode,
            NULL AS StartedOn, NULL AS callwith
        FROM 
            call_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.call_started_on >= p_started_on)
            AND (p_ended_on IS NULL OR rad.call_started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(rad.emp_code) LIKE CONCAT('%', lower(p_emp_code), '%'))
        GROUP BY 
            rad.emp_code
        ORDER BY 
            CallsCount DESC 
        LIMIT 10;
    END IF;
END



*****************


drop procedure GetDashBoardDetail

CREATE  PROCEDURE GetDashBoardDetail(
    IN Req_time DATETIME
)
BEGIN
    DECLARE SleepCount INT;
    DECLARE IdleCount INT;
    DECLARE ActiveCount INT;
    DECLARE TotalUsageCount INT;
   	DECLARE EmpCode varchar(255);
    DECLARE LoginTime DATETIME;
    DECLARE TotalActiveUsers INT;
    DECLARE TotalUsers INT;

    -- Calculate LastActiveUser
    SELECT 
    	SUM(total_sleep) AS SleepCount,
        SUM(total_idle) AS IdleCount,
        SUM(total_active) AS ActiveCount,
        SUM(total_active) + SUM(total_sleep) + SUM(total_idle) AS TotalUsageCount
    INTO
         SleepCount, IdleCount, ActiveCount, TotalUsageCount
    FROM employee_working_time
    WHERE work_logged_on >= Req_time - INTERVAL 1 DAY
      AND work_logged_on <= Req_time;

    -- Calculate system status counts
    SELECT
        sld.emp_code as EmpCode,
        sld.login_time as LoginTime
     INTO
        EmpCode, LoginTime
    FROM	
    	system_login_details sld 
    WHERE
        sld.login_time >= Req_time - INTERVAL 1 DAY
      AND sld.login_time <= Req_time
    ORDER BY sld.login_time DESC
    LIMIT 1;
   
    -- Calculate Total Users counts
   
   SELECT
         COUNT(DISTINCT sl.emp_code) as TotalUsers
        INTO TotalUsers
         
    FROM	
    	system_login_details sl;
    
   
   SELECT
         COUNT(DISTINCT sl.emp_code) as TotalActiveUsers
        INTO TotalActiveUsers
         
    FROM	
    	system_login_details sl 
    WHERE
        sl.login_time >= (Req_time - INTERVAL -60 SECOND);
      
   
       -- Return the results
    SELECT 'Window-PC' as Device,'1.0.0.1' as 'Version',
    	   COALESCE(EmpCode, 0) AS EmpCode, COALESCE(LoginTime, Req_time - INTERVAL 1 DAY) AS LoginTime,
    	   COALESCE(TotalActiveUsers, 0) AS TotalActiveUsers,
    	   COALESCE(TotalUsers, 0) AS TotalUsers,
           COALESCE(SleepCount, 0) AS SleepCount, 
           COALESCE(IdleCount, 0) AS IdleCount, 
           COALESCE(ActiveCount, 0) AS ActiveCount,
           COALESCE(TotalUsageCount, 0) AS TotalUsageCount;
END



****************

drop procedure GetEmployeeWorkingTime

CREATE  PROCEDURE GetEmployeeWorkingTime(
    IN p_emp_code VARCHAR(255),
    IN p_work_logged_on TIMESTAMP,
    IN p_work_ended_on TIMESTAMP,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Sort column
    IN p_sort_type VARCHAR(4)      -- Sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);

    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'work_logged_on';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'TotalActive' THEN
        SET p_sort_column = 'total_active';  -- Map "TotalActive" to the correct column name "total_active"
    ELSEIF p_sort_column = 'TotalIdle' THEN
        SET p_sort_column = 'total_idle';  -- Map "TotalIdle" to the correct column name "total_idle"
    ELSEIF p_sort_column = 'WorkLoggedOn' THEN
        SET p_sort_column = 'work_logged_on';  -- Map "WorkLoggedOn" to the correct column name "work_logged_on"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    ELSEIF p_sort_column = 'TotalSleep' THEN
        SET p_sort_column = 'total_sleep';  -- Map "TotalSleep" to the correct column name "total_sleep"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT('SELECT COUNT(*) INTO @v_total_records FROM employee_working_time WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_work_logged_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) >= DATE("', p_work_logged_on, '") '), ''),
                        IF(p_work_ended_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) <= DATE("', p_work_ended_on, '") '), ''));
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Retrieve the count result
    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the records with pagination if paging is not ignored
    SET @query = CONCAT('SELECT id, emp_code, total_active, total_idle, work_logged_on, created_on, updated_on, total_sleep, ',
                        v_total_records, ' AS total_records, ', 
                        v_total_pages, ' AS total_pages FROM employee_working_time WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_work_logged_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) >= DATE("', p_work_logged_on, '") '), ''),
                        IF(p_work_ended_on IS NOT NULL, CONCAT('AND DATE(work_logged_on) <= DATE("', p_work_ended_on, '") '), ''),
                        v_sort_query, 
                        v_limit_query);

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END


********************


drop procedure GetGraphDetail

CREATE  PROCEDURE GetGraphDetail(
    IN Req_time DATETIME,
    IN Live_data_flag BOOL
)
BEGIN
    DECLARE ApplicationCount INT;
    DECLARE CallCount INT;
    DECLARE LoginCount INT;
    DECLARE StartTime DATETIME;
    DECLARE EndTime DATETIME;
    DECLARE HourCounter INT DEFAULT 0;

    IF Live_data_flag THEN
        -- 15-second interval queries
        SET StartTime = Req_time;
        SET EndTime = DATE_ADD(Req_time, INTERVAL -60 SECOND);

        SELECT count(*) INTO ApplicationCount
        FROM running_application_details 
        WHERE started_on >= StartTime
        AND started_on < EndTime;

        SELECT count(*) INTO CallCount
        FROM call_details 
        WHERE call_started_on >= StartTime
        AND call_started_on < EndTime;

        SELECT count(*) INTO LoginCount
        FROM system_login_details
        WHERE login_time >= StartTime
        AND login_time < EndTime;

        SELECT ApplicationCount, CallCount, LoginCount, EndTime;

    ELSE
        -- 24-hour interval queries (hourly aggregation)
        -- Create a temporary table to store results
        CREATE TEMPORARY TABLE HourlyData (
            Hour INT,
            ApplicationCount INT,
            CallCount INT,
            LoginCount INT,
            EndTime DATETIME
            
        );

        -- Loop through each hour in the 24-hour period
        WHILE HourCounter < 24 DO
            SET StartTime = (Req_time) + INTERVAL HourCounter HOUR;
            SET EndTime = StartTime + INTERVAL 1 HOUR - INTERVAL 1 SECOND;

            INSERT INTO HourlyData (Hour, ApplicationCount, CallCount, LoginCount, EndTime)
            SELECT 
                HourCounter,
                (SELECT count(*) FROM running_application_details WHERE started_on >= StartTime AND started_on <= EndTime),
                (SELECT count(*) FROM call_details WHERE call_started_on >= StartTime AND call_started_on <= EndTime),
                (SELECT count(*) FROM system_login_details WHERE login_time >= StartTime AND login_time <= EndTime),
                StartTime AS EndTime;

            SET HourCounter = HourCounter + 1;
        END WHILE;

        -- Select results from the temporary table
        SELECT * FROM HourlyData;

        -- Drop the temporary table
        DROP TEMPORARY TABLE HourlyData;
    END IF;
END



*********************


drop procedure GetLogInChart

CREATE PROCEDURE GetLogInChart(
    IN p_started_on DATETIME,
    IN p_ended_on DATETIME, 
    IN ChartType VARCHAR(100)
)
BEGIN
    IF ChartType = 'Log In' THEN
        SELECT 
                       
            DATE(login_time) AS LogInDate,
            count(*) AS LogInCount,
            0 AS LogOutCount
        FROM 
            system_login_details
        WHERE 
            (p_started_on IS NULL OR login_time >= p_started_on)
            AND (p_ended_on IS NULL OR login_time <= p_ended_on)
            AND system_status IN ('Active', 'Idle')
        GROUP BY 
             DATE(login_time);
    ELSEIF ChartType = 'Log Out' THEN
        SELECT 
           
            DATE(login_time) AS LogInDate,
            count(*) AS LogOutCount,
            0 AS LogInCount
        FROM 
            system_login_details
        WHERE 
            (p_started_on IS NULL OR login_time >= p_started_on)
            AND (p_ended_on IS NULL OR login_time <= p_ended_on)
            AND system_status = 'Sleep'
        GROUP BY 
            DATE(login_time);
    END IF;
END



*******************

drop procedure GetReportActivityDetails

CREATE PROCEDURE GetReportActivityDetails()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE parentId INT;
    DECLARE parentName VARCHAR(255);

    -- Declare cursor to select parent activities
    DECLARE cur CURSOR FOR 
    SELECT ad.Id, ad.Name
    FROM activity_details ad
    WHERE ad.`Type` = 2;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Temporary table to store final results
    DROP TEMPORARY TABLE IF EXISTS finalResult;
    CREATE TEMPORARY TABLE finalResult (
        CombinedData JSON
    );

    -- Open cursor to fetch parent activities
    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO parentId, parentName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Insert parent and its associated child activities into finalResult
        INSERT INTO finalResult (CombinedData)
        SELECT 
            JSON_OBJECT(
                'ParentId', parentId,
                'ParentName', parentName,
                'ActivtyType', JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'Id', ad1.Id,
                        'Name', ad1.Name,
                        'Type', ad1.`Type`,
                        'UniqueId', ad1.UniqueId
                    )
                )
            ) AS CombinedData
        FROM activity_details ad1
        WHERE ad1.MappedType = parentId;

    END LOOP;

    CLOSE cur;

    -- Select the final results
    SELECT * FROM finalResult;

    /*DROP TEMPORARY TABLE IF EXISTS finalResult;*/
END


******************

drop procedure GetRunningApplications

CREATE PROCEDURE GetRunningApplications(
    IN p_emp_code VARCHAR(100),
    IN p_Description TEXT,
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Sort column
    IN p_sort_type VARCHAR(4)      -- Sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);

    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'started_on';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'ApplicationName' THEN
        SET p_sort_column = 'application_name';  -- Map "ApplicationName" to the correct column name "application_name"
    ELSEIF p_sort_column = 'StartedOn' THEN
        SET p_sort_column = 'started_on';  -- Map "StartedOn" to the correct column name "started_on"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    ELSEIF p_sort_column = 'Description' THEN
        SET p_sort_column = 'description';  -- Map "Description" to the correct column name "description"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT('SELECT COUNT(*) INTO @v_total_records FROM running_application_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_Description IS NOT NULL, CONCAT('OR lower(Description) LIKE "%', p_Description, '%" '), ''),  
                        IF(p_started_on IS NOT NULL, CONCAT('AND DATE(started_on) >= DATE("', p_started_on, '") '), ''),
                        IF(p_ended_on IS NOT NULL, CONCAT('AND DATE(started_on) <= DATE("', p_ended_on, '") '), ''));
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Retrieve the count result
    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the records with pagination if paging is not ignored
    SET @query = CONCAT('SELECT id, emp_code, application_name, started_on, created_on, updated_on, description, ',
                        'ended_on AS EndedOn, ',  -- Correctly fetch the actual 'ended_on' column from the table
                        v_total_records, ' AS total_records, ', 
                        v_total_pages, ' AS total_pages FROM running_application_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_Description IS NOT NULL, CONCAT('OR lower(Description) LIKE "%', p_Description, '%" '), ''),  
                        IF(p_started_on IS NOT NULL, CONCAT('AND DATE(started_on) >= DATE("', p_started_on, '") '), ''),
                        IF(p_ended_on IS NOT NULL, CONCAT('AND DATE(started_on) <= DATE("', p_ended_on, '") '), ''),
                        v_sort_query, 
                        v_limit_query);

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END


****************************

drop procedure GetSessionsChart


CREATE PROCEDURE GetSessionsChart(
    IN p_emp_code VARCHAR(100),
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP, 
    IN p_device VARCHAR(100),
    IN ChartType VARCHAR(100)
)
BEGIN
    -- Check if p_started_on and p_ended_on are the same
    IF (p_started_on IS NOT NULL AND p_ended_on IS NOT NULL AND p_started_on = p_ended_on) THEN
        -- Add one day to p_ended_on
        SET p_ended_on = DATE_ADD(p_ended_on, INTERVAL 1 DAY);
    END IF;

    SELECT 
        CAST(login_time AS DATE) AS LoginDate,
        SUM(CASE WHEN lower(system_status) = 'active' THEN 1 ELSE 0 END) AS ActiveCount,
        SUM(CASE WHEN lower(system_status) = 'sleep' THEN 1 ELSE 0 END) AS SleepCount,
        SUM(CASE WHEN lower(system_status) = 'idle' THEN 1 ELSE 0 END) AS IdleCount,
        COUNT(*) AS TotalCount
    FROM 
        system_login_details  
    WHERE 
        (p_started_on IS NULL OR login_time >= p_started_on)
        AND (p_ended_on IS NULL OR login_time <= p_ended_on)
        AND (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', lower(p_emp_code), '%'))
        AND (lower(system_status) = lower(ChartType) OR ChartType IS NULL)
    GROUP BY 
        CAST(login_time AS DATE);

END



***************

drop procedure GetSystemLoginDetails

CREATE PROCEDURE GetSystemLoginDetails(
    IN p_emp_code VARCHAR(255),
    IN p_login_time DATETIME,
    IN p_logout_time DATETIME,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Added parameter for sort column
    IN p_sort_type VARCHAR(4)      -- Added parameter for sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);

    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'login_time';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'LoginTime' THEN
        SET p_sort_column = 'login_time';  -- Map "LoginTime" to the correct column name "login_time"
    ELSEIF p_sort_column = 'LogoutTime' THEN
        SET p_sort_column = 'logout_time';  -- Map "LogoutTime" to the correct column name "logout_time"
    ELSEIF p_sort_column = 'SystemStatus' THEN
        SET p_sort_column = 'system_status';  -- Map "SystemStatus" to the correct column name "system_status"
    ELSEIF p_sort_column = 'IpAddress' THEN
        SET p_sort_column = 'ip_address';  -- Map "IpAddress" to the correct column name "ip_address"
    ELSEIF p_sort_column = 'ComputerName' THEN
        SET p_sort_column = 'computer_name';  -- Map "ComputerName" to the correct column name "computer_name"
    ELSEIF p_sort_column = 'AppVersion' THEN
        SET p_sort_column = 'app_version';  -- Map "AppVersion" to the correct column name "app_version"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT(
        'SELECT COUNT(*) INTO @v_total_records FROM system_login_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_login_time IS NOT NULL, CONCAT('AND DATE(login_time) >= DATE("', p_login_time, '") '), ''),
        IF(p_logout_time IS NOT NULL, CONCAT('AND DATE(login_time) <= DATE("', p_logout_time, '") '), '')
    );
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Retrieve the count result
    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the filtered and paginated results along with total records and total pages
    SET @query = CONCAT(
        'SELECT id, emp_code, login_time, logout_time, system_status, ip_address, computer_name, app_version, created_on, updated_on, ', 
        v_total_records, ' AS total_records, ', 
        v_total_pages, ' AS total_pages ',
        'FROM system_login_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_login_time IS NOT NULL, CONCAT('AND DATE(login_time) >= DATE("', p_login_time, '") '), ''),
        IF(p_logout_time IS NOT NULL, CONCAT('AND DATE(login_time) <= DATE("', p_logout_time, '") '), ''),
        v_sort_query,
        v_limit_query
    );

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END


***************

drop procedure GetSystemSleepDetails


CREATE PROCEDURE GetSystemSleepDetails(
    IN p_emp_code VARCHAR(255),
    IN p_triggered_on TIMESTAMP,
    IN p_triggered_off TIMESTAMP,
    IN p_description VARCHAR(50),
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Added parameter for sort column
    IN p_sort_type VARCHAR(4)      -- Added parameter for sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);
    DECLARE v_actual_sort_column VARCHAR(50);

    -- Map the model class property names to the actual table column names
    SET v_actual_sort_column = CASE p_sort_column
        WHEN 'Id' THEN 'id'
        WHEN 'EmpCode' THEN 'emp_code'
        WHEN 'Description' THEN 'description'
        WHEN 'TriggeredOn' THEN 'triggered_on'
        WHEN 'CreatedOn' THEN 'created_on'
        WHEN 'UpdatedOn' THEN 'updated_on'
        ELSE 'triggered_on'  -- Default column if no match is found
    END;

    -- Set default sort type if not provided
    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', v_actual_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT(
        'SELECT COUNT(*) INTO @v_total_records FROM system_sleep_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_triggered_on IS NOT NULL, CONCAT('AND DATE(triggered_on) >= DATE("', p_triggered_on, '") '), ''),
        IF(p_triggered_off IS NOT NULL, CONCAT('AND DATE(triggered_on) <= DATE("', p_triggered_off, '") '), ''),
        IF(p_description IS NOT NULL AND p_description != '', CONCAT('AND lower(description) LIKE "%', p_description, '%" '), '')
    );
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the filtered and paginated results along with total records and total pages
    SET @query = CONCAT(
        'SELECT id, emp_code, description, triggered_on, created_on, updated_on, ', 
        v_total_records, ' AS total_records, ', 
        v_total_pages, ' AS total_pages ',
        'FROM system_sleep_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_triggered_on IS NOT NULL, CONCAT('AND DATE(triggered_on) >= DATE("', p_triggered_on, '") '), ''),
        IF(p_triggered_off IS NOT NULL, CONCAT('AND DATE(triggered_on) <= DATE("', p_triggered_off, '") '), ''),
        IF(p_description IS NOT NULL AND p_description != '', CONCAT('AND lower(description) LIKE "%', p_description, '%" '), ''),
        v_sort_query,
        v_limit_query
    );

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

