By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,159 Members | 1,941 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,159 IT Pros & Developers. It's quick & easy.

looping to insert event types into schedule

P: 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
Dec 2 '07 #1
Share this Question
Share on Google+
3 Replies


MarkoKlacar
Expert 100+
P: 296
Hi,

not quite sure I understand your problem. The printout seems fine to me if you want to print you every 15 minutes for 7 days.

Does you printout only cover 09:00-12:00 or round the clock?

Cheers
Dec 3 '07 #2

P: 41
Hi

The number of inserted events for each day is 148 there should only be a maximum of 24 inserts per
day. There should not be inserts on Sunday or the following week.



pk date time event type
Monday
1 2007-01-01 09:00:00 201 This is OK
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
Tuesday
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
Wednesday
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
Thursday
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
Friday
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
Saturday
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
Sunday
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
Monday
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
Tuesday
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
Wednesday
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
Dec 5 '07 #3

MarkoKlacar
Expert 100+
P: 296
Hi,

I'm a bit confused but I'm gonna try any way. Seems to me like you need to check the dates and make sure they are not weekend days as well setting a limit for what days you want to loop though.

Make any sense?

GLm8.
Dec 6 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.