41
Hi
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.
[php]
<?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]
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
