CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`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