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

<?
   	$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
  
   
   ?>

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

<?

$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
	    	
    ?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.