Page 1 of 1

Distributing assignments equally and setting up for CRON job

Posted: Fri Jan 29, 2010 2:06 pm
by tymlls05
The code I placed at the bottom of this message is something I wrote to automatically assign individual agents (as events) to next month's days.

Currently it randomly chooses an agent (again, as an event) from the Floor Schedule category and if it is Sunday, schedules one event from 1-4. If it is Saturday it schedules 2 events from 9 am to 12:30 pm and 12:30 pm to 4 pm. Then, M-F it is the same setup as saturday, only with slightly different times.

The issue I am having is I am not sure how to tell it to NOT use the event again until all of the other ones have been assigned and to not use one event twice in one day. I used $maxuse to count the max number of times to assign the event per month to offer a more fair and balanced schedule. I am just unsure how to implement it.

I am sure the code below could be more efficient in many ways, but I just used what I know best.

Code: Select all

 
<?      
    $con=mysql_connect('localhost','USER',PASSWORD'); 
    if (!$con){
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("TABLE", $con);
    //TODAY'S DATE
    $start = date("Y-m-d");
    $startY =  date("Y");
    $startm =date("m");
    $startd =date("d");
    echo 'Current Month: '.$start.'<p>';
    list($year, $month, $day) = explode('-', $start);
    $month=$month+1;
    //Max days in next month
    $maxdays =strftime("%d");
    echo 'Number of days in next month: '.$maxdays.'<p>';
    $daycount=1;
    //Max number of times each agent can be used for month
    $maxuse=(($maxdays*2)-4)/22;
    $maxuse=ceil($maxuse);
    echo 'Maxuse Per Agent Next Month: '.$maxuse.'<p>';
    
    echo 'We are now trying to start generating a schedule';
    for ($daycount; $daycount <= $maxdays; $daycount++) {
        $next = date('Y-m-d', mktime(0, 0, 0, $month, $daycount, $year));
        $daytext=date('l', strtotime($next));
        If ($daytext=="Sunday"){
            $preexist=mysql_query('SELECT `start_date` FROM `event_schedules` where `start_date`=\''.$next.'\'');
            while($checkexistance = mysql_fetch_array($preexist)){
                $exist=true;
            }
            if ($exist){
                unset($exist);
                echo 'Date already exist: '.$next;
            } else {
                echo '<div style="background-color:lightgreen"><hr><h3>'.$daytext.'</h3>';
                echo '<p>Day '.$daycount.'</div>';
                echo 'Looking to insert into date: '.$next.'<p>';
                //Select RANDOM EVENT (AGENT) TO ASSIGN TO DAY
                $offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `event_categories_events` WHERE event_category_id = '2'  ") or die(mysql_error());
                $offset_row = mysql_fetch_object( $offset_result );
                $offset = $offset_row->offset;
                $floor_events = mysql_query( " SELECT * FROM `event_categories_events` WHERE event_category_id = '2' LIMIT $offset, 1 " ); 
                
                //Grab Random Event from All Events  (Agents) Assigned To The Floor Schedule Category
                while($grabFloorEvent = mysql_fetch_array($floor_events)){
                    //For every event id we grab, grab the event (agent) details.
                    $agent=$grabFloorEvent['event_id'];
                    $agent_details = mysql_query('SELECT * FROM `events` WHERE `id`=\''.$agent.'\'');
                    while($grabAgent_details = mysql_fetch_array($agent_details)){
                        $AgentName=$grabAgent_details['title'];
                        $AgentLink=$grabAgent_details['link'];
                        $AgentDescription=$grabAgent_details['description'];
                        $IsAgentPublished=$grabAgent_details['publish'];
                        $AgentNoticeEmail=$grabAgent_details['notice_email'];
                        mysql_query("INSERT INTO event_schedules 
                            (event_id,  start_date, start_time, end_date,end_time, frequency, daily_interval, weekly_interval)
                            VALUES ('$agent', '$next', '13:00:00', '$next', '16:00:00', '0', '1', '1')") or die(mysql_error());
                        echo "Name: $agent $AgentName <br>
                            Link: $AgentLink <br>
                            Description: $AgentDescription <br>
                            Published: $IsAgentPublished <br>
                            Notify: $AgentNoticeEmail  <br> <br>";
                        break;
                    }
                }
            }
        } elseif ($daytext=="Saturday"){
            for ($am=0; $am < 2; $am++) {
                echo $am;
                echo '<div style="background-color:lightgreen"><hr><h3>'.$daytext.'</h3>';
                echo '<p>Day '.$daycount.'</div>';
                echo 'Looking to insert into date: '.$next.'<p>';
                //Select RANDOM EVENT (AGENT) TO ASSIGN TO DAY
                $offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `event_categories_events` WHERE event_category_id = '2'  ") or die(mysql_error());
                $offset_row = mysql_fetch_object( $offset_result );
                $offset = $offset_row->offset;
                $floor_events = mysql_query( " SELECT * FROM `event_categories_events` WHERE event_category_id = '2' LIMIT $offset, 1 " ); 
                        
                //Grab Random Event from All Events  (Agents) Assigned To The Floor Schedule Category
                while($grabFloorEvent = mysql_fetch_array($floor_events)){
                    //For every event id we grab, grab the event (agent) details.
                    $agent=$grabFloorEvent['event_id'];
                    $agent_details = mysql_query('SELECT * FROM `events` WHERE `id`=\''.$agent.'\'');
                    while($grabAgent_details = mysql_fetch_array($agent_details)){
                        $AgentName=$grabAgent_details['title'];
                        $AgentLink=$grabAgent_details['link'];
                        $AgentDescription=$grabAgent_details['description'];
                        $IsAgentPublished=$grabAgent_details['publish'];
                        $AgentNoticeEmail=$grabAgent_details['notice_email'];
                        if ($am==0){
                            mysql_query("INSERT INTO event_schedules 
                                (event_id,  start_date, start_time, end_date,end_time, frequency, daily_interval, weekly_interval)
                                VALUES ('$agent', '$next', '09:00:00', '$next', '12:30:00', '0', '1', '1')") or die(mysql_error());
                            echo'morning';
                        } else {
                            echo'noon';
                            mysql_query("INSERT INTO event_schedules 
                                (event_id,  start_date, start_time, end_date,end_time, frequency, daily_interval, weekly_interval)
                                VALUES ('$agent', '$next', '12:30:00', '$next', '16:00:00', '0', '1', '1')") or die(mysql_error()); 
                        }
                        echo "Name: $agent $AgentName <br>
                            Link: $AgentLink <br>
                            Description: $AgentDescription <br>
                            Published: $IsAgentPublished <br>
                            Notify: $AgentNoticeEmail  <br> <br>";
                        break;
                    }
                }
            }
        } else {
            for ($am=0; $am < 2; $am++) {
                echo $am;
                echo '<div style="background-color:lightgreen"><hr><h3>'.$daytext.'</h3>';
                echo '<p>Day '.$daycount.'</div>';
                echo 'Looking to insert into date: '.$next.'<p>';
                //Select RANDOM EVENT (AGENT) TO ASSIGN TO DAY
                $offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `event_categories_events` WHERE event_category_id = '2'  ") or die(mysql_error());
                $offset_row = mysql_fetch_object( $offset_result );
                $offset = $offset_row->offset;
                $floor_events = mysql_query( " SELECT * FROM `event_categories_events` WHERE event_category_id = '2' LIMIT $offset, 1 " ); 
                //Grab Random Event from All Events  (Agents) Assigned To The Floor Schedule Category
                while($grabFloorEvent = mysql_fetch_array($floor_events)){
                    //For every event id we grab, grab the event (agent) details.
                    $agent=$grabFloorEvent['event_id'];
                    $agent_details = mysql_query('SELECT * FROM `events` WHERE `id`=\''.$agent.'\'');
                    while($grabAgent_details = mysql_fetch_array($agent_details)){
                        $AgentName=$grabAgent_details['title'];
                        $AgentLink=$grabAgent_details['link'];
                        $AgentDescription=$grabAgent_details['description'];
                        $IsAgentPublished=$grabAgent_details['publish'];
                        $AgentNoticeEmail=$grabAgent_details['notice_email'];
                        if ($am==0){
                            mysql_query("INSERT INTO event_schedules 
                                (event_id,  start_date, start_time, end_date,end_time, frequency, daily_interval, weekly_interval)
                                VALUES ('$agent', '$next', '08:30:00', '$next', '13:00:00', '0', '1', '1')") or die(mysql_error());
                            echo'morning';
                        } else {
                            echo'noon';
                            mysql_query("INSERT INTO event_schedules 
                                (event_id,  start_date, start_time, end_date,end_time, frequency, daily_interval, weekly_interval)
                                VALUES ('$agent', '$next', '13:00:00', '$next', '17:00:00', '0', '1', '1')") or die(mysql_error()); 
                        }
                        echo "Name: $agent $AgentName <br>
                            Link: $AgentLink <br>
                            Description: $AgentDescription <br>
                            Published: $IsAgentPublished <br>
                            Notify: $AgentNoticeEmail  <br> <br>";
                        break;
                    }
                }
            }
        }
    }
    
?>
 

Re: Distributing assignments equally and setting up for CRON job

Posted: Fri Jan 29, 2010 5:21 pm
by JakeJ
Create a boolean field in your database for each agent and set it when an agent is assigned. At the appropriate time (the end of a shift I assume) and clear it when the agent is no longer assigned.

You could also create a time stamp when the event it assigned and round it off to that day or whatever and then do a calculation to see if it's the next day *see strtotime().

I don't have time to write any example code, but I hope this helps.