Page 1 of 1

Inserting recurring event/appointment type

Posted: Sat Dec 29, 2007 12:24 am
by assgar
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi 
    
    I need help.
    
    I know what I want to accomplish, but I do not know how to do it.
    
    The events are recurring every month, but not recurring at the same time or on the same day of the week.
    
    
    WHAT I NEED HELP ACCOMPLISHING:
    How to do I insert recurring events into a table for a date range.  
    Where the months are the same but the event/appointment types occur at different 
    times and or on different days of the week.
    
    
    POSSIBLE APPROACH:
    I would like to choose a 7 day cycle/template, or to allow making the weeks 
    different with a 14 day or 21 day or 28 day cycle/template.
    
    For example the 14 days cycle represents two weeks where every second week can be different.
      
    Day#   Day
    1        
    2	Monday    (meetings 1:00pm to 3:00pm)
    3	Tuesday
    4	Wednesday
    5	Thursday  (breakfast meeting 8:00AM to 9:00AM)
    6	Friday
    7	
    8
    9	Monday   (breakfast meeting 8:00AM to 9:00AM)
    10	Tuesday
    11	Wednesday (meetings 1:00pm to 3:00pm)
    12	Thursday
    13	Friday
    14
   
   
   The current code works well for a week or if every week is the same in the date range.
   See below.
   
   The availablity table store different event/appointment types using date and time range.
   This event/appointment type information is then displayed to the user using a daily 
   schedule format.
    
   HOW THE 7 DAY CYCLE DATA IS STORED:
   Example: Meetings(event_type_code) between 2:30 PM and 4:30 PM for Monday to Friday
             this is stored in the "availablity" table as seen below.
    
    Note: A template group holds the different appointment types for the days of the 
           week as selected. 
    
   |group_id|start_time|end_time| start_date|end_date  | week_day|type_code 
   |26      |14:30:00  |16:30:00| 2007-12-03|2007-12-07| 550     |   201         
   |26      |14:30:00  |16:30:00| 2007-12-03|2007-12-07| 551     |   201         
   |26      |14:30:00  |16:30:00| 2007-12-03|2007-12-07| 552     |   201         
   |26      |14:30:00  |16:30:00| 2007-12-03|2007-12-07| 553     |   201           
   |26      |14:30:00  |16:30:00| 2007-12-03|2007-12-07| 554     |   201        
  
   
   CODE FOR ONE WEEK CYCLE

Code: Select all

<?
   	$group_seg = array();
   	
         /*get group templates data to apply to schedule. This data contains 
            event/appointment types*/
   	$query = "SELECT distinct(s.seg_id), s.model_id, w.group_id, s.event_type_code,
   	                 s.time_from, s.time_to, w.weekday
   		  FROM cal_group_week w, cal_day_segment s
   		  WHERE s.model_id = w.model_id
   		  AND w.group_id = '$group_id'
   		  AND s.deleted = 'N'
   		  AND w.deleted = 'N'";
   	$result = mysqli_query ($mysqli, $query);
   	while($row = mysqli_fetch_array($result))
   		{
   		   $group_seg[] = $row;
  		}
   
   
         //loop through segment start and end time
         foreach($group_seg as $group_segment)
   	  {
   		  //database stored time from daily model segments
   		  $start_time = $group_segment['time_from'];
   		  $end_time = $group_segment['time_to'];
   		  $group_id = $group_segment['group_id'];
   		  $event_type_code = $group_segment['event_type_code'];
   		  $day = $group_segment['weekday'];
   		
   
   		
   	 /**-----------------------insert event type/appointment---------------------**/
   		    
   	 $cal_query = "INSERT INTO availablity(
    			 time_id, group_id, start_time, end_time, 
    			 start_date, end_date, week_day,  
                           type_code) 
    		      VALUES(
   		                 null, '$group_id', '$start_time', '$end_time', '$start_date', 
   			 '$end_date', '$day', '$event_type_code')";
   
   	 mysqli_query($mysqli, $cal_query)or die(mysqli_error($mysqli));		
   
     	 }//apply group
  
   
   ?>

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Dec 31, 2007 7:42 pm
by Christopher
There was a lengthly discussion of this here

Re: Inserting recurring event/appointment type

Posted: Mon Jan 28, 2008 8:44 pm
by assgar
Hi

After some time I think I have done it.
Let nme know if you have any suggestions how to refine the code.

The result below is for a three week (7 day) cycle for the month of January.
Note: week days Monday (550) to Friday (554)

Result Columns:
date range(start date, end date), day of week, time range(start time and end time)

inner loop)1
2008-01-01 2008-01-07 550 09:00:00 12:00:00
2008-01-22 2008-01-28 550 09:00:00 12:00:00
2008-01-01 2008-01-07 551 09:00:00 12:00:00
2008-01-22 2008-01-28 551 09:00:00 12:00:00
2008-01-01 2008-01-07 552 09:00:00 12:00:00
2008-01-22 2008-01-28 552 09:00:00 12:00:00
2008-01-01 2008-01-07 553 09:00:00 12:00:00
2008-01-22 2008-01-28 553 09:00:00 12:00:00
2008-01-01 2008-01-07 554 09:00:00 12:00:00
2008-01-22 2008-01-28 554 09:00:00 12:00:00

(inner loop)2
2008-01-08 2008-01-14 550 10:00:00 13:00:00
2008-01-29 2008-01-31 550 10:00:00 13:00:00
2008-01-08 2008-01-14 551 10:00:00 13:00:00
2008-01-29 2008-01-31 551 10:00:00 13:00:00
2008-01-08 2008-01-14 552 10:00:00 13:00:00
2008-01-29 2008-01-31 552 10:00:00 13:00:00
2008-01-08 2008-01-14 553 10:00:00 13:00:00
2008-01-29 2008-01-31 553 10:00:00 13:00:00
2008-01-08 2008-01-14 554 10:00:00 13:00:00
2008-01-29 2008-01-31 554 10:00:00 13:00:00

(inner loop)3
2008-01-15 2008-01-21 550 16:00:00 20:00:00
2008-01-15 2008-01-21 551 16:00:00 20:00:00
2008-01-15 2008-01-21 552 16:00:00 20:00:00
2008-01-15 2008-01-21 553 16:00:00 20:00:00
2008-01-15 2008-01-21 554 16:00:00 20:00:00

Code: Select all

 
 
<?
 
$max_week = '3';
$start_date = '2008-01-01';
$end_date = '2008-01-31';
 
/**-------------loop through number of weeks------------**/
for($i = 1; $i <= $max_week; $i++)
   {
        //format to two characters
    $week_num = "0$i";
   
        //detemine start date interval 
     if($i == 1)
           {
            $start_date = $start_date;//week 1
       }
      else
         {
            //week 2, 3 and 4   
        $wk_start = strtotime($start_date);
        $start_date = date("Y-m-d", strtotime("+7 days", $wk_start));
         }
         
         
       //flush previous array contents
       unset ($group_seg);
    
       /**------------get group module data to apply to schedule------------**/
       $query = "SELECT distinct(s.seg_id) w.group_id,
                        s.time_from, s.time_to, w.weekday
             FROM group_week w, day_segment s
             WHERE s.model_id = w.model_id
             AND w.group_id = '$group_id'";
  
       $result = mysqli_query ($mysqli, $query);
       while($row = mysqli_fetch_array($result))
        {
           $group_seg[] = $row;
        }
 
 
           /**------------------- event type info to insert-----------------------**/ 
       //loop through segment start and end time
       foreach($group_seg as $group_segment)
        {
           //database stored time from daily model segments
           $start_time = $group_segment['time_from'];
           $end_time = $group_segment['time_to'];
           $group_id = $group_segment['group_id'];
           $day = $group_segment['weekday'];
 
           //more than one week cycle used interval date
           if($max_week > 1)
            {
 
                 //determine date start incrementation using max_week
                switch($max_week)
                        {
                           case '1': //1 week
                              $cycle_days = 7;
                           break;
                           case '2': //2 weeks
                              $cycle_days = 14;
                           break;
                       case '3': //3 weeks
                           $cycle_days = 21;
                           break;
                       case '4': //4 weeks
                           $cycle_days = 28;
                           break;
                        }
 
                             /**----------increment using $cycle_days from above--------**/      
                             for($f = $start_date; $f <= $end_date; $f = date("Y-m-d", strtotime($f . "+ $cycle_days day")))
                        {
                   //set start date
                   $startdate = strtotime($f);
                       $type_start_date = date("Y-m-d", $startdate);
 
                       //set end date with addtional 6 days
                      $wk_start = strtotime($type_start_date);
                          $wk_end_date = date("Y-m-d", strtotime("+6 days", $wk_start));
                                            
                      //check incremented end date does not exceed selected end date
                      if($wk_end_date <= $end_date) 
                     {
                        $type_end_date = $wk_end_date;//incremented end date
                     }
                    else
                       {
                       $type_end_date = $end_date;//selected end date
                       }
                                        
    
                                   /**INSERT STATEMENT GOES HERE**/                                 }
             }
             else
                {
                // single week cycle insert selected start and end dates no manipulation needed
                $type_start_date = $start_date;
                $type_end_date = $end_date;
                                
                /**INSERT STATEMENT GOES HERE**/
                }
             }//foreach 
            }//for
            
    ?>