How to create a batch to count the worked days of the users based on LoginTime

Question

I need to create a batch to count the worked days of the users based on LoginTime, excluding only the weekends (Saturday & Sunday), but I'm quite new in the programming area and I have been struggling for the past 4 days to figure this out and I still quite far from the resolution.

Criteria of the batch:

• Create a set or map of users (and if possible the LogintTime already), that can be used later on in a loop;
• The loop is supposed to check every userID, and then check the first login of the day (it doesn't matter if a person has more than one login in the same day, or if stayed logged less than X hours. The only days that will not count if a person login, are the weekends);
• The map or set is supposed to be uploaded;
• Difficulties: I cannot make a list using the data from LoginHistory, and for that, I need to adapt the query so it can collect data from the user and then mix it with the login input. From what I saw, it seems like I need to create separated lists to create a map afterward.

I really appreciate any help. I know the below code seems quite messy, but I just changed my career at the beginning of the year, and my internship is remote, which can be quite difficult sometimes in this learning process. Happy holidays!

What I have done so far is a class (I'm aware that the query is not correct)

    public class WorkingDaysCalculation{  
    public static Integer calculateWorkingDays(Date startDate, Date endDate){          
    Integer workingDays = 0;  
    // create a set or map with usersID

            for(LoginHistory usersLogin : [SELECT UserId, MIN(LoginTime) First_Login_Day FROM LoginHistory  //only take the first login of the day 
            WHERE LoginTime in : currYear AND todayDayStr != 'Saturday' 
            AND todayDayStr != 'Sunday'])
            {
                for(integer i=0; i <= startDate.daysBetween(endDate); i++)  
                {  
                    Date dt = startDate + I;  
                    DateTime currDate = DateTime.newInstance(dt.year(), dt.month(), dt.day());  
                    String todayDay = currDate.format('EEEE');  
                    if(todayDay != 'Saturday' && todayDay != 'Sunday')
                        { workingDays = workingDays + 1; }  
                } 
            // count how many logins each users have                 
            }
        System.debug('--Working days'+workingDays);  
        return workingDays; 
        }
    }

The attempt of a batch is this one, which don\t even get me started, I know it's bad and not even a draft.

    global class WorkingDaysCalculationBatch implements Database.Batchable<sObject>, Database.Stateful{

            //List of the IDs to check 
            List<User> usersList;
            DateTime todayDay = System.today();
            DateTime currDate = DateTime.newInstance(todayDay.year(), todayDay.month(), todayDay.day()); 
            String todayDayStr = currDate.format('EEEE');
            System.debug(todayDayStr);        
            Integer workingDays = 0;      

        global WorkingDaysCalculationBatch(List<User> users2) {
            usersList = users2; 
            currYear = String.valueOf(System.Today().year()); 
        }

        global Database.QueryLocator start(Database.BatchableContext bc) {
            
            List<Id> usersIdsLst = new List<Id>();
            usersIdsLst = UsersMethods.GetUsersIds(this.usersList);
            System.debug('Batch Process - Start Querying');        
            return Database.getQueryLocator([SELECT UserId, MIN(LoginTime) First_Login_Day FROM LoginHistory  //only take the first login of the day 
                                            WHERE LoginTime in : usersIdsLst 
                                            ]);
            
        }    

        global void execute(Database.BatchableContext bc, List<User> usersList){
            
            List<Contract> contractsList = new List<Contract>();
            for( acc : accountsList){
                //Check if Account already has a Contract for the specified 'contractYear'
                if(todayDayStr != 'Saturday' && todayDayStr != 'Sunday' && todayDay >= startDate && todayDay <= endDate)
                { workingDays = workingDays + 1; } 
                }
            }
            
            insert contractsList;
        
        }      

Answer 1

Depending on the number of results, you can just write your query like this:

SELECT DAY_ONLY(LoginTime), UserId 
FROM LoginHistory 
WHERE DAY_IN_WEEK(LoginTime) NOT IN (1, 7) 
GROUP BY UserId, DAY_ONLY(LoginTime)

This will give you every day a user has logged in, not including weekends. The data type for these results will be an AggregateResult. Note that "queryMore" is not supported for these queries, so you will need to keep the limit under 2,000 unique permutations of users and login days. You can do that with additional filters, such as entries from the last week:

SELECT DAY_ONLY(LoginTime), UserId 
FROM LoginHistory 
WHERE DAY_IN_WEEK(LoginTime) NOT IN (1, 7) AND LoginTime = LAST_N_DAYS:7 
GROUP BY UserId, DAY_ONLY(LoginTime)

Even if you decide to just iterate over the LoginHistory records directly in your batchable, as opposed to using the above query, you can use the DAY_IN_WEEK filter to avoid all that messy logic with checking the day in Apex code.