looping to insert event types into schedule

•

What is DaniWeb IT Discussion Community?

You're currently browsing the PHP section within the Web Development category of DaniWeb, a massive community of 455,985 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,760 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our PHP advertiser: Lunarpages PHP Web Hosting

Thread Tools Display Modes

Views: 737 | Replies: 0 | Solved

•

Join Date: Oct 2006

Posts: 76

Reputation: assgar is an unknown quantity at this point

Rep Power: 3

Solved Threads: 0

assgar

Offline

Junior Poster in Training

looping to insert event types into schedule

Dec 2nd, 2007

I am having problem with my loping.
I don't know if I have chosen the correct approach.

GOAL:
I need to insert into a table event types for a specific date range.
The calendar the event type is displayed on is divided into
15 minutes time intervals

A group consist of days of the week, each day consist of segments of
time blocks for different events:

Note:
The day segments are stored in a table linked to the group_id foreign key

Group
-------------------------------------------------------------------------
| Group Days| Day Segments |Event Type |# of 15 min blocks|
| ---------------------------------------|-----------|------------------|
| Monday |09:00 to 12:00 appointment | 201 | 12 |
|----------------------------------------|-----------|------------------|
| Tuesday |09:00 to 12:00 appointment | 201 | 12 |
| |01:00 to 04:00 appointment | 201 | 12 |
|----------------------------------------|-----------|------------------|
| Wednesday |09:00 to 12:00 appointment | 201 | 12 |
| |01:00 to 04:00 appointment | 201 | 12 |
|----------------------------------------|-----------|------------------|
| Thursday |09:00 to 12:00 appointment | 201 | 12 |
| |01:00 to 04:00 Lunch | 201 | 12 |
|----------------------------------------|-----------|------------------|
| Friday |05:00 to 08:00 appointment | 201 | 12 |
| |09:00 to 10:00 appointment | 201 | 4 |
|----------------------------------------|-----------|------------------|
| Saturday |09:00 to 12:00 appointment | 201 | 12 |
| |01:00 to 04:00 appointment | 201 | 12 |
|----------------------------------------|-----------|------------------|

PROBLEM:
The problem I am having is the looping.
I selected a date range of 7 days that should generate
124 inserts instead I get 1036 inserts.
Also I need to ensure that the segments match up with the day of the week.

Help with Code Tags

(Toggle Plain Text)

<?php

/************************VARIABLES*************************************/

   //repeat
   $repeat_interval;//daily, monthly etc
   $repeat_frequency;//every, every other etc
   $event_date //start date
   $repeat_end_date//end date

 /*****************************selects********************************/

 

	/**----------------------time block/duration------------------------**/
	$time_interval = $d_time_interval

	
	/*Note:$interval must be (case-insensitive): 'day', 'week', 'month', or 'year'*/
	//determine interval used
	if(!empty($repeat_interval))
		{
			 switch ($repeat_interval)
    			{
      				case 'd':
      				$new_interval = "day";
      				break;
      				case 'w':
      				$new_interval = "week";
      				break;
      				case 'm':
       				$new_interval = "month";
      				break;
      				case 'y':
       				$new_interval =  "year";
      				break;
   				}
		}

     /*Note: $frequency must be positive integer (1 = every, 2, = every other,
	        3 = every 3rd, 4 = every 4th. 5 = every 5th, 6 = every 6th)*/
     
     $new_frequency = $repeat_frequency;
		

   /******************************************insert ******************************************/
    //get group module data to apply to schedule
    $query = "SELECT s.event_type_code, s.time_from, s.time_to
	      FROM cal_week w, cal_segment s
	      WHERE s.model_id = w.model_id";
    $result = mysqli_query ($mysqli, $query);
    while($row = mysqli_fetch_array($result))
	{
	    /**
	    $group_seg will contains:
	    $group_seg['event_type_code'], $group_seg['time_from'], $group_seg['time_to'],
	    **/
	    $group_seg[] = $row;
	}

    /*******Note:
        - array repeatEvent(int $startTime, str $interval, int $frequency, int $endTime)
          returns array of UNIX times
        - $startTime and $endTime must be valid UNIX time integer values
        - $interval must be (case-insensitive): 'day', 'week', 'month', or 'year'
        - $frequency must be positive integer (1 = every, 2, = every other,
           3 = every 3rd, 4 = every 4th. 5 = every 5th, 6 = every 6th)
    *********/

    function repeatEvent($startTime, $interval, $frequency, $endTime)
	{
   	   //make sure all paramters are valid
   	   $startTime = (int) $startTime;
   	   $endTime = (int) $endTime;

   	   if($startTime == 0)
   	   	{
      		   user_error("repeatEvent(): invalid start time");
       		   return(FALSE);
   		}

   	  if($endTime < $startTime)
   		{
  		    user_error("repeatEvent(): invalid end time");
    		}

   	  $interval = strtolower(trim($interval));
   	  if(!in_array($interval, array('day','week','month','year')))
   	      {
      		  user_error("repeatEvent(): Invalid interval '$interval'");
           	  return(FALSE);
              }

   	  $frequency = (int)$frequency;
  	  if($frequency < 1)
   	     {
      	  	user_error("repeatEvent(): Invalid frequency '$frequency'");
      	  	return(FALSE);
   	     }

   	  $schedule = array();
   	  for($time = $startTime; $time <= $endTime; $time = strtotime("+$frequency $interval", $time))
   		{
      		   $schedule[] = $time;
   		}
   	  return($schedule);
	}
  


	//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'];
		 $event_type_code = $group_segment['event_type_code'];

		  /**------------------calculate number of blocks for event----------------**/
		  //spilt time
		  list($eh, $em, $es) = split(":",$end_time);
		  list($sh, $sm, $ss) = split(":",$start_time);

		  //convert start and end time to minutes
		  $ehr = $eh * 60;
		  $total_ehr = $ehr + $em;

		  $shr = $sh * 60;
		  $total_shr = $shr + $sm;

		  //get length of event_type minutes
		  $total_min = $total_ehr - $total_shr;

		  //get number of time blocks from event_type minutes
		  $time_block = (round($total_min / $time_interval));

		/**----------------------start and end date ---------------------**/
		  //date of the event
		  $start_date = $event_date." ".$start_time;
		  $end_date = $repeat_end_date." ".$end_time;

		      
		/**-----------------------insert appointment-----------------------**/
	          $sched = repeatEvent(strtotime($start_date), $new_interval, $new_frequency, strtotime($end_date));


		    //outer loop repeated inserts
	            foreach($sched as $date)
   			 {

				//inner loop the number of time blocks
				for($i = 0, $eTime = strtotime($start_time); $i < $time_block;
    				$i++, $eTime = strtotime("+$time_interval minutes", $eTime))
					{
   						$new_event_time = date('H:i', $eTime); //increment time for new single or multi block event
						$new_event_date = date('Y-m-d', $date);//increment date for single or repeat event


					        $cal_query = "INSERT INTO cal_availability(
 								     time_id, group_id, event_date, event_time, event_type_code)
 							       VALUES(null, '$group_id', '$new_event_date', '$new_event_time', 
 							              '$event_type_code')";
      			               }//inner for
         	           }//end inner foreach loop

		   }//outer foreach
 

 //close the connection
$mysqli->close();
?>

<?php

/************************VARIABLES*************************************/

   //repeat
   $repeat_interval;//daily, monthly etc
   $repeat_frequency;//every, every other etc
   $event_date //start date
   $repeat_end_date//end date

 /*****************************selects********************************/

 

	/**----------------------time block/duration------------------------**/
	$time_interval = $d_time_interval

	
	/*Note:$interval must be (case-insensitive): 'day', 'week', 'month', or 'year'*/
	//determine interval used
	if(!empty($repeat_interval))
		{
			 switch ($repeat_interval)
    			{
      				case 'd':
      				$new_interval = "day";
      				break;
      				case 'w':
      				$new_interval = "week";
      				break;
      				case 'm':
       				$new_interval = "month";
      				break;
      				case 'y':
       				$new_interval =  "year";
      				break;
   				}
		}

     /*Note: $frequency must be positive integer (1 = every, 2, = every other,
	        3 = every 3rd, 4 = every 4th. 5 = every 5th, 6 = every 6th)*/
     
     $new_frequency = $repeat_frequency;
		

   /******************************************insert ******************************************/
    //get group module data to apply to schedule
    $query = "SELECT s.event_type_code, s.time_from, s.time_to
	      FROM cal_week w, cal_segment s
	      WHERE s.model_id = w.model_id";
    $result = mysqli_query ($mysqli, $query);
    while($row = mysqli_fetch_array($result))
	{
	    /**
	    $group_seg will contains:
	    $group_seg['event_type_code'], $group_seg['time_from'], $group_seg['time_to'],
	    **/
	    $group_seg[] = $row;
	}

    /*******Note:
        - array repeatEvent(int $startTime, str $interval, int $frequency, int $endTime)
          returns array of UNIX times
        - $startTime and $endTime must be valid UNIX time integer values
        - $interval must be (case-insensitive): 'day', 'week', 'month', or 'year'
        - $frequency must be positive integer (1 = every, 2, = every other,
           3 = every 3rd, 4 = every 4th. 5 = every 5th, 6 = every 6th)
    *********/

    function repeatEvent($startTime, $interval, $frequency, $endTime)
	{
   	   //make sure all paramters are valid
   	   $startTime = (int) $startTime;
   	   $endTime = (int) $endTime;

   	   if($startTime == 0)
   	   	{
      		   user_error("repeatEvent(): invalid start time");
       		   return(FALSE);
   		}

   	  if($endTime < $startTime)
   		{
  		    user_error("repeatEvent(): invalid end time");
    		}

   	  $interval = strtolower(trim($interval));
   	  if(!in_array($interval, array('day','week','month','year')))
   	      {
      		  user_error("repeatEvent(): Invalid interval '$interval'");
           	  return(FALSE);
              }

   	  $frequency = (int)$frequency;
  	  if($frequency < 1)
   	     {
      	  	user_error("repeatEvent(): Invalid frequency '$frequency'");
      	  	return(FALSE);
   	     }

   	  $schedule = array();
   	  for($time = $startTime; $time <= $endTime; $time = strtotime("+$frequency $interval", $time))
   		{
      		   $schedule[] = $time;
   		}
   	  return($schedule);
	}
  


	//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'];
		 $event_type_code = $group_segment['event_type_code'];

		  /**------------------calculate number of blocks for event----------------**/
		  //spilt time
		  list($eh, $em, $es) = split(":",$end_time);
		  list($sh, $sm, $ss) = split(":",$start_time);

		  //convert start and end time to minutes
		  $ehr = $eh * 60;
		  $total_ehr = $ehr + $em;

		  $shr = $sh * 60;
		  $total_shr = $shr + $sm;

		  //get length of event_type minutes
		  $total_min = $total_ehr - $total_shr;

		  //get number of time blocks from event_type minutes
		  $time_block = (round($total_min / $time_interval));

		/**----------------------start and end date ---------------------**/
		  //date of the event
		  $start_date = $event_date." ".$start_time;
		  $end_date = $repeat_end_date." ".$end_time;

		      
		/**-----------------------insert appointment-----------------------**/
	          $sched = repeatEvent(strtotime($start_date), $new_interval, $new_frequency, strtotime($end_date));


		    //outer loop repeated inserts
	            foreach($sched as $date)
   			 {

				//inner loop the number of time blocks
				for($i = 0, $eTime = strtotime($start_time); $i < $time_block;
    				$i++, $eTime = strtotime("+$time_interval minutes", $eTime))
					{
   						$new_event_time = date('H:i', $eTime); //increment time for new single or multi block event
						$new_event_date = date('Y-m-d', $date);//increment date for single or repeat event


					        $cal_query = "INSERT INTO cal_availability(
 								     time_id, group_id, event_date, event_time, event_type_code)
 							       VALUES(null, '$group_id', '$new_event_date', '$new_event_time', 
 							              '$event_type_code')";
      			               }//inner for
         	           }//end inner foreach loop

		   }//outer foreach
 

 //close the connection
$mysqli->close();
?>

RESULTS:

pk date time event type
1 2007-01-01 09:00:00 201
2 2007-01-01 09:15:00 201
3 2007-01-01 09:30:00 201
4 2007-01-01 09:45:00 201
5 2007-01-01 10:00:00 201
6 2007-01-01 10:15:00 201
7 2007-01-01 10:30:00 201
8 2007-01-01 10:45:00 201
9 2007-01-01 11:00:00 201
10 2007-01-01 11:15:00 201
11 2007-01-01 11:30:00 201
12 2007-01-01 11:45:00 201
13 2007-01-02 09:00:00 201
14 2007-01-02 09:15:00 201
15 2007-01-02 09:30:00 201
16 2007-01-02 09:45:00 201
17 2007-01-02 10:00:00 201
18 2007-01-02 10:15:00 201
19 2007-01-02 10:30:00 201
20 2007-01-02 10:45:00 201
21 2007-01-02 11:00:00 201
22 2007-01-02 11:15:00 201
23 2007-01-02 11:30:00 201
24 2007-01-02 11:45:00 201
25 2007-01-03 09:00:00 201
26 2007-01-03 09:15:00 201
27 2007-01-03 09:30:00 201
28 2007-01-03 09:45:00 201
29 2007-01-03 10:00:00 201
30 2007-01-03 10:15:00 201
31 2007-01-03 10:30:00 201
32 2007-01-03 10:45:00 201
33 2007-01-03 11:00:00 201
34 2007-01-03 11:15:00 201
35 2007-01-03 11:30:00 201
36 2007-01-03 11:45:00 201
37 2007-01-04 09:00:00 201
38 2007-01-04 09:15:00 201
39 2007-01-04 09:30:00 201
40 2007-01-04 09:45:00 201
41 2007-01-04 10:00:00 201
42 2007-01-04 10:15:00 201
43 2007-01-04 10:30:00 201
44 2007-01-04 10:45:00 201
45 2007-01-04 11:00:00 201
46 2007-01-04 11:15:00 201
47 2007-01-04 11:30:00 201
48 2007-01-04 11:45:00 201
49 2007-01-05 09:00:00 201
50 2007-01-05 09:15:00 201
51 2007-01-05 09:30:00 201
52 2007-01-05 09:45:00 201
53 2007-01-05 10:00:00 201
54 2007-01-05 10:15:00 201
55 2007-01-05 10:30:00 201
56 2007-01-05 10:45:00 201
57 2007-01-05 11:00:00 201
58 2007-01-05 11:15:00 201
59 2007-01-05 11:30:00 201
60 2007-01-05 11:45:00 201
61 2007-01-06 09:00:00 201
62 2007-01-06 09:15:00 201
63 2007-01-06 09:30:00 201
64 2007-01-06 09:45:00 201
65 2007-01-06 10:00:00 201
66 2007-01-06 10:15:00 201
67 2007-01-06 10:30:00 201
68 2007-01-06 10:45:00 201
69 2007-01-06 11:00:00 201
70 2007-01-06 11:15:00 201
71 2007-01-06 11:30:00 201
72 2007-01-06 11:45:00 201
73 2007-01-07 09:00:00 201
74 2007-01-07 09:15:00 201
75 2007-01-07 09:30:00 201
76 2007-01-07 09:45:00 201
77 2007-01-07 10:00:00 201
78 2007-01-07 10:15:00 201
79 2007-01-07 10:30:00 201
80 2007-01-07 10:45:00 201
81 2007-01-07 11:00:00 201
82 2007-01-07 11:15:00 201
83 2007-01-07 11:30:00 201
84 2007-01-07 11:45:00 201
85 2007-01-01 09:00:00 201
86 2007-01-01 09:15:00 201
87 2007-01-01 09:30:00 201
88 2007-01-01 09:45:00 201
89 2007-01-01 10:00:00 201
90 2007-01-01 10:15:00 201
91 2007-01-01 10:30:00 201
92 2007-01-01 10:45:00 201
93 2007-01-01 11:00:00 201
94 2007-01-01 11:15:00 201
95 2007-01-01 11:30:00 201
96 2007-01-01 11:45:00 201
97 2007-01-02 09:00:00 201
98 2007-01-02 09:15:00 201
99 2007-01-02 09:30:00 201
100 2007-01-02 09:45:00 201
101 2007-01-02 10:00:00 201
102 2007-01-02 10:15:00 201
103 2007-01-02 10:30:00 201
104 2007-01-02 10:45:00 201
105 2007-01-02 11:00:00 201
106 2007-01-02 11:15:00 201
107 2007-01-02 11:30:00 201
108 2007-01-02 11:45:00 201
109 2007-01-03 09:00:00 201
110 2007-01-03 09:15:00 201
111 2007-01-03 09:30:00 201
112 2007-01-03 09:45:00 201
113 2007-01-03 10:00:00 201
114 2007-01-03 10:15:00 201
115 2007-01-03 10:30:00 201
116 2007-01-03 10:45:00 201
117 2007-01-03 11:00:00 201
118 2007-01-03 11:15:00 201
119 2007-01-03 11:30:00 201
120 2007-01-03 11:45:00 201
to....
1036 2007-01-07 14:45:00 201