4 * Loads appropriate import file parser and processes the data returned
6 * Palmdesktop (dba file)
12 * User defined inport routines may be used, see example
13 * in the SWITCH statement below
16 * FileName: File name specified by user on import.php
17 * calUser: user's calendar to import data into, unless
18 * single user = Y or Admin, caluser will equal logged
20 * exc_private: exclude private records from Palmdesktop import
21 * overwrite: Overwrite previous import
26 include_once $gfplugins.'webcalendar/www/includes/init.php';
27 include_once $gfplugins.'webcalendar/www/includes/site_extras.php';
31 $overwrite = getValue("overwrite");
32 $doOverwrite = ( empty ( $overwrite ) || $overwrite != 'Y' ) ? false : true;
37 if ( ! empty ( $_FILES['FileName'] ) ) {
38 $file = $_FILES['FileName'];
39 } else if ( ! empty ( $HTTP_POST_FILES['FileName'] ) ) {
40 $file = $HTTP_POST_FILES['FileName'];
43 if ( empty ( $file ) ) {
44 echo "No file!<br />";
48 $calUser = getValue ( "calUser" );
49 if ( ! empty ( $calUser ) ) {
50 if ( $single_user == "N" && ! $is_admin ) $calUser = $login;
55 if ($file['size'] > 0) {
56 switch ($ImportType) {
58 // ADD New modules here:
61 // include "import_module.php";
62 // $data = parse_module($HTTP_POST_FILES['FileName']['tmp_name']);
66 include "import_palmdesktop.php";
67 if (delete_palm_events($login) != 1) $errormsg = "Error deleting palm events from webcalendar.";
68 $data = parse_palmdesktop($file['tmp_name'], $exc_private);
73 include "import_vcal.php";
74 $data = parse_vcal($file['tmp_name']);
79 include "import_ical.php";
80 $data = parse_ical($file['tmp_name']);
85 $count_con = $count_suc = $error_num = 0;
86 if (! empty ($data) && empty ($errormsg) ) {
87 import_data ( $data, $doOverwrite, $type );
88 echo "<p>" . translate("Import Results") . "</p>\n<br /><br />\n" .
89 translate("Events successfully imported") . ": $count_suc<br />\n";
90 echo translate("Events from prior import marked as deleted") . ": $numDeleted<br />\n";
91 if ( empty ( $allow_conflicts ) ) {
92 echo translate("Conflicting events") . ": " . $count_con . "<br />\n";
94 echo translate ( "Errors" ) . ": $error_num<br><br>\n";
95 } elseif (! empty ( $errormsg ) ) {
96 echo "<br /><br />\n<b>" . translate("Error") . ":</b> $errormsg<br />\n";
98 echo "<br /><br />\n<b>" . translate("Error") . ":</b> " .
99 translate("There was an error parsing the import file or no events were returned") .
103 echo "<br /><br />\n<b>" . translate("Error") . ":</b> " .
104 translate("The import file contained no data") . ".<br />\n";
108 //echo "<hr />$sqlLog\n";
111 echo "</body>\n</html>";
113 /* Import the data structure
114 $Entry[RecordID] = Record ID (in the Palm) ** only required for palm desktop
115 $Entry[StartTime] = In seconds since 1970 (Unix Epoch)
116 $Entry[EndTime] = In seconds since 1970 (Unix Epoch)
117 $Entry[Summary] = Summary of event (string)
118 $Entry[Duration] = How long the event lasts (in minutes)
119 $Entry[Description] = Full Description (string)
120 $Entry[Untimed] = 1 = true 0 = false
121 $Entry[Private] = 1 = true 0 = false
122 $Entry[Category] = useless for Palm (not supported yet)
123 $Entry[AlarmSet] = 1 = true 0 = false
124 $Entry[AlarmAdvanceAmount] = How many units in AlarmAdvanceType (-1 means not set)
125 $Entry[AlarmAdvanceType] = Units: (0=minutes, 1=hours, 2=days)
126 $Entry[Repeat] = Array containing repeat information (if repeat)
127 $Entry[Repeat][Interval] = 1=daily,2=weekly,3=MonthlyByDay,4=MonthlyByDate,5=Yearly,6=monthlyByDayR
128 $Entry[Repeat][Frequency] = How often event occurs. (1=every, 2=every other,etc.)
129 $Entry[Repeat][EndTime] = When the repeat ends (In seconds since 1970 (Unix Epoch))
130 $Entry[Repeat][Exceptions] = Exceptions to the repeat (In seconds since 1970 (Unix Epoch))
131 $Entry[Repeat][RepeatDays] = For Weekly: What days to repeat on (7 characters...y or n for each day)
134 // TODO: Figure out category from $Entry[Category] or have a drop-down asking which
135 // category to import into.
137 function import_data ( $data, $overwrite, $type ) {
138 global $login, $count_con, $count_suc, $error_num, $ImportType, $LOG_CREATE;
139 global $single_user, $single_user_login, $allow_conflicts;
140 global $numDeleted, $errormsg;
141 global $calUser, $H2COLOR, $sqlLog;
147 // Generate a unique import id
148 $res = dbi_query ( "SELECT MAX(cal_import_id) FROM webcal_import" );
150 if ( $row = dbi_fetch_row ( $res ) ) {
151 $importId = $row[0] + 1;
153 dbi_free_result ( $res );
155 $sql = "INSERT INTO webcal_import ( cal_import_id, cal_name, " .
156 "cal_date, cal_type, cal_login ) VALUES ( $importId, NULL, " .
157 date("Ymd") . ", '$type', '$login' )";
158 if ( ! dbi_query ( $sql ) ) {
159 $errormsg = translate("Database error") . ": " . dbi_error ();
163 foreach ( $data as $Entry ){
166 $participants[0] = $calUser;
168 // Some additional date/time info
169 $START = $Entry['StartTime'] > 0 ? localtime($Entry['StartTime']) : 0;
170 $END = $Entry['EndTime'] > 0 ? localtime($Entry['EndTime']) : 0;
171 $Entry['StartMinute'] = sprintf ("%02d",$START[1]);
172 $Entry['StartHour'] = sprintf ("%02d",$START[2]);
173 $Entry['StartDay'] = sprintf ("%02d",$START[3]);
174 $Entry['StartMonth'] = sprintf ("%02d",$START[4] + 1);
175 $Entry['StartYear'] = sprintf ("%04d",$START[5] + 1900);
176 $Entry['EndMinute'] = sprintf ("%02d",$END[1]);
177 $Entry['EndHour'] = sprintf ("%02d",$END[2]);
178 $Entry['EndDay'] = sprintf ("%02d",$END[3]);
179 $Entry['EndMonth'] = sprintf ("%02d",$END[4] + 1);
180 $Entry['EndYear'] = sprintf ("%04d",$END[5] + 1900);
181 if ( $overwrite && ! empty ( $Entry['UID'] ) ) {
182 if ( empty ( $oldUIDs[$Entry['UID']] ) ) {
183 $oldUIDs[$Entry['UID']] = 1;
185 $oldUIDs[$Entry['UID']]++;
190 if ( ! empty ( $Entry['Untimed'] ) && $Entry['Untimed'] == 1) {
191 $Entry['StartMinute'] = '';
192 $Entry['StartHour'] = '';
193 $Entry['EndMinute'] = '';
194 $Entry['EndHour'] = '';
197 // first check for any schedule conflicts
198 if ( ( empty ( $allow_conflicts ) || $allow_conflicts == "N" ) &&
199 ( $Entry['Duration'] != 0 )) {
200 $date = mktime (0,0,0,$Entry['StartMonth'],
201 $Entry['StartDay'],$Entry['StartYear']);
202 $endt = (! empty ( $Entry['Repeat']['EndTime'] ) ) ?
203 $Entry['Repeat']['EndTime'] : 'NULL';
204 $dayst = (! empty ( $Entry['Repeat']['RepeatDays'] ) ) ?
205 $Entry['Repeat']['RepeatDays'] : "nnnnnnn";
208 if ( ! empty ( $Entry['Repeat']['Exceptions'] ) ) {
209 foreach ($Entry['Repeat']['Exceptions'] as $ex_date) {
210 $ex_days[] = date("Ymd",$ex_date);
214 $dates = get_all_dates($date, RepeatType($Entry['Repeat']['Interval']),
215 $endt, $dayst, $ex_days, $Entry['Repeat']['Frequency']);
216 $overlap = check_for_conflicts ( $dates, $Entry['Duration'],
217 $Entry['StartHour'], $Entry['StartMinute'], $participants, $login, 0 );
220 if ( empty ( $error ) && ! empty ( $overlap ) ) {
221 $error = translate("The following conflicts with the suggested time").
222 ":<ul>$overlap</ul>\n";
225 if ( empty ( $error ) ) {
229 // See if event already is there from prior import.
230 // The same UID is used for all events imported at once with iCal.
231 // So, we still don't have enough info to find the exact
232 // event we want to replace. We could just delete all
233 // existing events that correspond to the UID.
234 /************************************************************************
235 Not sure what to do with this code since I don't know how Palm and vCal
236 use the UID stuff yet...
238 if ( ! empty ( $Entry['UID'] ) ) {
239 $res = dbi_query ( "SELECT webcal_import_data.cal_id " .
240 "FROM webcal_import_data, webcal_entry_user " .
241 "WHERE cal_import_type = 'ical' AND " .
242 "webcal_import_data.cal_id = webcal_entry_user.cal_id AND " .
243 "webcal_entry_user.cal_login = '$login' AND " .
244 "cal_external_id = '$Entry[UID]'" );
246 if ( $row = dbi_fetch_row ( $res ) ) {
247 if ( ! empty ( $row[0] ) ) {
250 // update rather than add a new event
255 ************************************************************************/
258 $res = dbi_query ( "SELECT MAX(cal_id) FROM webcal_entry" );
260 $row = dbi_fetch_row ( $res );
262 dbi_free_result ( $res );
265 //$error = "Unable to select MAX cal_id: " . dbi_error () .
266 // "<br /><br />\n<b>SQL:</b> $sql";
269 if ( $firstEventId == 0 )
276 if ( ! $updateMode ) {
277 $names[] = 'cal_create_by';
278 $values[] = "'$login'";
280 $names[] = 'cal_date';
281 $values[] = sprintf ( "%04d%02d%02d",
282 $Entry['StartYear'],$Entry['StartMonth'],$Entry['StartDay']);
283 $names[] = 'cal_time';
284 $values[] = ( ! empty ( $Entry['Untimed'] ) &&
285 $Entry['Untimed'] == 1) ? "-1" :
286 sprintf ( "%02d%02d00", $Entry['StartHour'],$Entry['StartMinute']);
287 $names[] = 'cal_mod_date';
288 $values[] = date("Ymd");
289 $names[] = 'cal_mod_time';
290 $values[] = date("Gis");
291 $names[] = 'cal_duration';
292 $values[] = sprintf ( "%d", $Entry['Duration'] );
293 $names[] = 'cal_priority';
294 $values[] = $priority;
295 $names[] = 'cal_access';
296 $values[] = ( ! empty ( $Entry['Private'] ) &&
297 $Entry['Private'] == 1) ? "'R'" : "'P'";
298 $names[] = 'cal_type';
299 $values[] = ( ! empty ( $Entry['Repeat'] ) ) ? "'M'" : "'E'";
301 if ( strlen ( $Entry['Summary'] ) == 0 )
302 $Entry['Summary'] = translate("Unnamed Event");
303 if ( empty ( $Entry['Description'] ) )
304 $Entry['Description'] = $Entry['Summary'];
305 $Entry['Summary'] = str_replace ( "\\n", "\n", $Entry['Summary'] );
306 $Entry['Summary'] = str_replace ( "\\'", "'", $Entry['Summary'] );
307 $Entry['Summary'] = str_replace ( "\\\"", "\"", $Entry['Summary'] );
308 $Entry['Summary'] = str_replace ( "'", "\\'", $Entry['Summary'] );
309 $names[] = 'cal_name';
310 $values[] = "'" . $Entry['Summary'] . "'";
311 $Entry['Description'] = str_replace ( "\\n", "\n", $Entry['Description'] );
312 $Entry['Description'] = str_replace ( "\\'", "'", $Entry['Description'] );
313 $Entry['Description'] = str_replace ( "\\\"", "\"", $Entry['Description'] );
314 $Entry['Description'] = str_replace ( "'", "\\'", $Entry['Description'] );
315 // Mozilla will send this goofy string, so replace it with real html
316 $Entry['Description'] = str_replace ( "=0D=0A=", "<br />",
317 $Entry['Description'] );
318 $Entry['Description'] = str_replace ( "=0D=0A", "",
319 $Entry['Description'] );
320 // Allow option to not limit description size
321 // This will only be practical for mysql and MSSQL/Postgres as
322 //these do not have limits on the table definition
323 //TODO Add this option to preferences
324 if ( empty ( $LIMIT_DESCRIPTION_SIZE ) ||
325 $LIMIT_DESCRIPTION_SIZE == "Y" ) {
326 // limit length to 1024 chars since we setup tables that way
327 if ( strlen ( $Entry['Description'] ) >= 1024 ) {
328 $Entry['Description'] = substr ( $Entry['Description'], 0, 1019 ) . "...";
331 $names[] = 'cal_description';
332 $values[] = "'" . $Entry['Description'] . "'";
334 $sql = "UPDATE webcal_entry SET ";
335 for ( $f = 0; $f < count ( $names ); $f++ ) {
338 $sql .= $names[$f] . " = " . $values[$f];
340 $sql .= " WHERE cal_id = $id";
342 $sql = "INSERT INTO webcal_entry ( " . implode ( ", ", $names ) .
343 " ) VALUES ( " . implode ( ", ", $values ) . " )";
346 if ( empty ( $error ) ) {
347 $sqlLog .= $sql . "<br />\n";
348 //echo "SQL: $sql <br />\n";
349 if ( ! dbi_query ( $sql ) ) {
350 $error .= "<p>" . translate("Database error") . ": " . dbi_error () .
357 activity_log ( $id, $login, $login,
358 $updateMode ? $LOG_UPDATE : $LOG_CREATE, "Import from $ImportType" );
360 if ( $single_user == "Y" ) {
361 $participants[0] = $single_user_login;
364 // Now add to webcal_import_data
365 if ( ! $updateMode ) {
366 if ($ImportType == "PALMDESKTOP") {
367 $sql = "INSERT INTO webcal_import_data ( cal_import_id, cal_id, " .
368 "cal_login, cal_import_type, cal_external_id ) VALUES ( " .
369 "$importId, $id, '$calUser', 'palm', '$Entry[RecordID]' )";
370 $sqlLog .= $sql . "<br />\n";
371 if ( ! dbi_query ( $sql ) ) {
372 $error = translate("Database error") . ": " . dbi_error ();
376 else if ($ImportType == "VCAL") {
377 $uid = empty ( $Entry['UID'] ) ? "null" : "'$Entry[UID]'";
378 if ( strlen ( $uid ) > 200 )
380 $sql = "INSERT INTO webcal_import_data ( cal_import_id, cal_id, " .
381 "cal_login, cal_import_type, cal_external_id ) VALUES ( " .
382 "$importId, $id, '$calUser', 'vcal', $uid )";
383 $sqlLog .= $sql . "<br />\n";
384 if ( ! dbi_query ( $sql ) ) {
385 $error = translate("Database error") . ": " . dbi_error ();
389 else if ($ImportType == "ICAL") {
390 $uid = empty ( $Entry['UID'] ) ? "null" : "'$Entry[UID]'";
391 // This may cause problems
392 if ( strlen ( $uid ) > 200 )
394 $sql = "INSERT INTO webcal_import_data ( cal_import_id, cal_id, " .
395 "cal_login, cal_import_type, cal_external_id ) VALUES ( " .
396 "$importId, $id, '$calUser', 'ical', $uid )";
397 $sqlLog .= $sql . "<br />\n";
398 if ( ! dbi_query ( $sql ) ) {
399 $error = translate("Database error") . ": " . dbi_error ();
405 // Now add participants
406 if ( ! $updateMode ) {
407 $status = ( $login == "__public__" ) ? 'W' : 'A';
408 if ( empty ( $cat_id ) ) $cat_id = 'NULL';
409 $sql = "INSERT INTO webcal_entry_user " .
410 "( cal_id, cal_login, cal_status, cal_category ) VALUES ( $id, '" .
411 $participants[0] . "', '$status', $cat_id )";
412 $sqlLog .= $sql . "<br />\n";
413 if ( ! dbi_query ( $sql ) ) {
414 $error = translate("Database error") . ": " . dbi_error ();
419 // Add repeating info
421 // remove old repeating info
422 dbi_query ( "DELETE FROM webcal_entry_repeats WHERE cal_id = $id" );
423 dbi_query ( "DELETE FROM webcal_entry_repeats_not WHERE cal_id = $id" );
425 if (! empty ($Entry['Repeat']['Interval'])) {
426 //while ( list($k,$v) = each ( $Entry['Repeat'] ) ) {
427 // echo "$k: $v <br />\n";
429 $rpt_type = RepeatType($Entry['Repeat']['Interval']);
430 $freq = ( ! empty ( $Entry['Repeat']['Frequency'] ) ?
431 $Entry['Repeat']['Frequency'] : 1 );
432 if ( ! empty ( $Entry['Repeat']['EndTime'] ) ) {
433 $REND = localtime($Entry['Repeat']['EndTime']);
434 $end = sprintf ( "%04d%02d%02d",$REND[5] + 1900,$REND[4] + 1,$REND[3]);
438 $days = (! empty ($Entry['Repeat']['RepeatDays'])) ?
439 "'".$Entry['Repeat']['RepeatDays']."'" : 'NULL';
440 $sql = "INSERT INTO webcal_entry_repeats ( cal_id, " .
441 "cal_type, cal_end, cal_days, cal_frequency ) VALUES " .
442 "( $id, '$rpt_type', $end, $days, $freq )";
443 $sqlLog .= $sql . "<br />\n";
444 if ( ! dbi_query ( $sql ) ) {
445 $error = "Unable to add to webcal_entry_repeats: ".
446 dbi_error ()."<br /><br />\n<b>SQL:</b> $sql";
450 // Repeating Exceptions...
451 if ( ! empty ( $Entry['Repeat']['Exceptions'] ) ) {
452 foreach ($Entry['Repeat']['Exceptions'] as $ex_date) {
453 $ex_date = date("Ymd",$ex_date);
454 $sql = "INSERT INTO webcal_entry_repeats_not ( cal_id, cal_date ) VALUES ( $id, $ex_date )";
455 $sqlLog .= $sql . "<br />\n";
456 if ( ! dbi_query ( $sql ) ) {
457 $error = "Unable to add to webcal_entry_repeats_not: ".
458 dbi_error ()."<br /><br />\n<b>SQL:</b> $sql";
465 // Add Alarm info -> site_extras
467 dbi_query ( "DELETE FROM webcal_site_extras WHERE cal_id = $id" );
469 if ( ! empty ( $Entry['AlarmSet'] ) && $Entry['AlarmSet'] == 1 ) {
470 $RM = $Entry['AlarmAdvanceAmount'];
471 if ($Entry['AlarmAdvanceType'] == 1){ $RM = $RM * 60; }
472 if ($Entry['AlarmAdvanceType'] == 2){ $RM = $RM * 60 * 24; }
473 $sql = "INSERT INTO webcal_site_extras ( cal_id, " .
474 "cal_name, cal_type, cal_remind, cal_data ) VALUES " .
475 "( $id, 'Reminder', 7, 1, $RM )";
476 $sqlLog .= $sql . "<br />\n";
477 if ( ! dbi_query ( $sql ) ) {
478 $error = translate("Database error") . ": " . dbi_error ();
483 if ( ! empty ($error) && empty ($overlap)) {
485 echo "<h2>". translate("Error") .
486 "</h2>\n<blockquote>\n";
487 echo $error . "</blockquote>\n<br />\n";
491 if ( ! empty ( $overlap ) ) {
493 translate("Scheduling Conflict") . ": ";
497 if ( $Entry['Duration'] > 0 ) {
498 $time = display_time ( $Entry['StartHour'].$Entry['StartMinute']."00" ) .
499 " - " . display_time ( $Entry['EndHour'].$Entry['EndMinute']."00" );
501 $dd = $Entry['StartMonth'] . "-" . $Entry['StartDay'] . "-" . $Entry['StartYear'];
502 $Entry['Summary'] = str_replace ( "''", "'", $Entry['Summary'] );
503 $Entry['Summary'] = str_replace ( "'", "\\'", $Entry['Summary'] );
504 echo htmlspecialchars ( $Entry['Summary'] );
506 $time = trim ( $time );
507 if ( ! empty ( $time ) )
508 echo " " . $time;
510 etranslate("conflicts with the following existing calendar entries");
511 echo ":<ul>\n" . $overlap . "</ul>\n";
516 translate("Event Imported") . ":</h2></b>\n";
518 if ( $Entry['Duration'] > 0 ) {
519 $time = display_time ( $Entry['StartHour'].$Entry['StartMinute']."00" ) .
520 " - " . display_time ( $Entry['EndHour'].$Entry['EndMinute']."00" );
522 $dateYmd = sprintf ( "%04d%02d%02d", $Entry['StartYear'],
523 $Entry['StartMonth'], $Entry['StartDay'] );
524 $dd = date_to_str ( $dateYmd );
525 echo "<a class=\"entry\" href=\"view_entry.php?id=$id";
526 echo "\" onmouseover=\"window.status='" . translate("View this entry") .
527 "'; return true;\" onmouseout=\"window.status=''; return true;\">";
528 $Entry['Summary'] = str_replace( "''", "'", $Entry['Summary']);
529 $Entry['Summary'] = str_replace( "\\", "", $Entry['Summary']);
530 echo htmlspecialchars ( $Entry['Summary'] );
532 if ( ! empty ( $time ) )
533 echo " " . $time;
538 $overlap = $error = $dd = $time = '';
541 // Mark old events from prior import as deleted.
542 if ( $overwrite && count ( $oldUIDs ) > 0 ) {
543 // We could do this with a single SQL using sub-select, but
544 // I'm pretty sure MySQL does not support it.
545 $old = array_keys ( $oldUIDs );
546 for ( $i = 0; $i < count ( $old ); $i++ ) {
547 $sql = "SELECT cal_id FROM webcal_import_data WHERE " .
548 "cal_import_type = '$type' AND " .
549 "cal_external_id = '$old[$i]' AND " .
550 "cal_login = '$calUser' AND " .
551 "cal_id < $firstEventId";
552 $res = dbi_query ( $sql );
554 while ( $row = dbi_fetch_row ( $res ) ) {
557 dbi_free_result ( $res );
559 echo translate("Database error") . ": " . dbi_error () . "<br />\n";
562 for ( $i = 0; $i < count ( $oldIds ); $i++ ) {
563 $sql = "UPDATE webcal_entry_user SET cal_status = 'D' " .
564 "WHERE cal_id = $oldIds[$i]";
565 $sqlLog .= $sql . "<br />\n";
571 //echo "<b>SQL:</b><br />\n$sqlLog\n";
574 // Convert interval to webcal repeat type
575 function RepeatType ($type) {
576 $Repeat = array (0,'daily','weekly','monthlyByDay','monthlyByDate','yearly','monthlyByDayR');
577 return $Repeat[$type];