[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   * $Id: export_handler.php,v 1.29.2.5 2006/01/28 22:40:51 umcesrjones Exp $
   4   *
   5   * Description:
   6   *    Handler for exporting webcalendar events to various formats.
   7   *
   8   * Comments:
   9   *    All-day events and untimed events are treated differently.  An
  10   *    all-day event is a 12am event with duration 24 hours.  We store
  11   *    untimed events with a start time of -1 in the webcalendar database.
  12   *
  13   *********************************************************************/
  14  include_once  'includes/init.php';
  15  
  16  if ( ! empty ( $PROGRAM_VERSION ) ) {
  17    $prodid = "PRODID:-//WebCalendar-$PROGRAM_VERSION";
  18  } else if ( preg_match ( "/v(\d\S+) /", $GLOBALS['PROGRAM_NAME'], $matches ) ) {
  19    $prodid = "PRODID:-//WebCalendar-$matches[1]";
  20  } else {
  21    $prodid = "PRODID:-//WebCalendar-UnknownVersion";
  22  }
  23  
  24  if ( empty ( $user ) || $user == $login )
  25    load_user_layers ();
  26  
  27  function export_get_event_entry($id) {
  28    global $use_all_dates, $include_layers, $fromyear,$frommonth,$fromday,
  29      $endyear,$endmonth,$endday,$modyear,$modmonth,$modday,$login;
  30    global $DISPLAY_UNAPPROVED, $layers;
  31  
  32    // We export repeating events only with the pilot-datebook CSV format
  33    $sql = "SELECT webcal_entry.cal_id, webcal_entry.cal_name " .
  34      ", webcal_entry.cal_priority, webcal_entry.cal_date " .
  35      ", webcal_entry.cal_time " .
  36      ", webcal_entry_user.cal_status, webcal_entry.cal_create_by " .
  37      ", webcal_entry.cal_access, webcal_entry.cal_duration " .
  38      ", webcal_entry.cal_description " .
  39      ", webcal_entry_user.cal_category " .
  40      "FROM webcal_entry, webcal_entry_user ";
  41  
  42    if ($id == "all") {
  43        $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
  44      " ( webcal_entry_user.cal_login = '" . $login . "'";
  45        if ( ! empty ( $user ) && $user != $login ) {
  46          $sql .= " OR webcal_entry_user.cal_login = '$user'";
  47        } else if ( $include_layers && $layers ) {
  48          foreach ( $layers as $layer ) {
  49            $sql .= " OR webcal_entry_user.cal_login = '" .
  50              $layer['cal_layeruser'] . "'";
  51          }
  52        }
  53        $sql .= " ) ";
  54  
  55      if (!$use_all_dates) {
  56        $startdate = sprintf ( "%04d%02d%02d", $fromyear, $frommonth, $fromday );
  57        $enddate = sprintf ( "%04d%02d%02d", $endyear, $endmonth, $endday );
  58        $sql .= " AND webcal_entry.cal_date >= $startdate " .
  59          "AND webcal_entry.cal_date <= $enddate";
  60        $moddate = sprintf ( "%04d%02d%02d", $modyear, $modmonth, $modday );
  61        $sql .= " AND webcal_entry.cal_mod_date >= $moddate";
  62      }
  63    } else {
  64        $sql .= "WHERE webcal_entry.cal_id = '$id' AND " .
  65      "webcal_entry_user.cal_id = '$id' AND " .
  66      "( webcal_entry_user.cal_login = '" . $login . "'";
  67          // TODO: add support for user in URL so we can export from other
  68          // calendars, particularly non-user calendars.
  69      //"webcal_entry_user.cal_id = '$id'";
  70        if ( ! empty ( $user )  && $user != $login ) {
  71          $sql .= " OR webcal_entry_user.cal_login = '$user'";
  72        } else if ( $layers ) {
  73          foreach ( $layers as $layer ) {
  74            $sql .= " OR webcal_entry_user.cal_login = '" .
  75               $layer['cal_layeruser'] . "'";
  76          }
  77        }
  78        $sql .= " ) ";
  79    } //end if $id=all
  80  
  81    if ( $DISPLAY_UNAPPROVED == "N"  || $login == "__public__" )
  82      $sql .= " AND webcal_entry_user.cal_status = 'A'";
  83    else
  84      $sql .= " AND webcal_entry_user.cal_status IN ('W','A')";
  85  
  86    $sql .= " ORDER BY webcal_entry.cal_date";
  87  
  88    //echo "SQL: $sql <p>";
  89    $res = dbi_query ( $sql );
  90  
  91    return $res;
  92  } //end function export_get_event_entry($id)
  93  
  94  function export_quoted_printable_encode($car) {
  95    $res = "";
  96  
  97    if ((ord($car) >= 33 && ord($car) <= 60) || (ord($car) >= 62 && ord($car) <= 126) || 
  98        ord($car) == 9 || ord($car) == 32) {
  99        $res = $car;
 100    } else {
 101        $res = sprintf("=%02X", ord($car));
 102    } //end if
 103  
 104    return $res;
 105  } //end function export_quoted_printable_encode
 106  
 107  function export_fold_lines($string, $encoding="none", $limit=76) {
 108    $len = strlen($string);
 109    $fold = $limit; 
 110    $res = array();
 111    $row = "";
 112    $enc = "";
 113    $lwsp = 0; // position of the last linear whitespace (where to fold)
 114    $res_ind = 0; // row index
 115    $start_encode = 0; // we start encoding only after the ":" caracter is encountered
 116  
 117    if (strcmp($encoding,"quotedprintable") == 0)
 118      $fold--; // must take into account the soft line break
 119  
 120    for ($i = 0; $i < $len; $i++) {
 121        $enc = $string[$i];
 122  
 123      if ($start_encode) {
 124        if (strcmp($encoding,"quotedprintable") == 0)
 125          $enc = export_quoted_printable_encode($string[$i]);
 126        else if (strcmp($encoding,"utf8") == 0)
 127          $enc = utf8_encode($string[$i]);
 128      }
 129  
 130      if ($string[$i] == ":")
 131        $start_encode = 1;
 132  
 133      if ((strlen($row) + strlen($enc)) > $fold) {
 134        $delta = 0;
 135  
 136        if ($lwsp == 0)
 137          $lwsp = $fold - 1; // the folding will occur in the middle of a word
 138  
 139        if ($row[$lwsp] == " " || $row[$lwsp] == "\t")
 140          $delta = -1;
 141  
 142        $res[$res_ind] = substr($row, 0, $lwsp+1+$delta);
 143  
 144        if (strcmp($encoding,"quotedprintable") == 0)
 145          $res[$res_ind] .= "="; // soft line break;
 146  
 147        $row = substr($row, $lwsp+1);
 148  
 149        $row = " " . $row;
 150  
 151        if ($delta == -1 && strcmp($encoding,"utf8") == 0)
 152          $row = " " . $row;
 153  
 154        if ($res_ind == 0)
 155          $fold--; //reduce row length of 1 to take into account the whitespace at the beginning of lines
 156  
 157        $res_ind++; // next line
 158  
 159        $lwsp = 0;
 160      } //end if ((strlen($row) + strlen($enc)) > $fold)
 161  
 162        $row .= $enc;
 163  
 164        if ($string[$i] == " " || $string[$i] == "\t" || $string[$i] == ";" || $string[$i] == ",")
 165      $lwsp = strlen($row) - 1;
 166  
 167        if ($string[$i] == ":" && (strcmp($encoding,"quotedprintable") == 0))
 168      $lwsp = strlen($row) - 1; // we cut at ':' only for quoted printable
 169      } //end for ($i = 0; $i < $len; $i++)
 170  
 171    $res[$res_ind] = $row; // Add last row (or first if no folding is necessary)
 172  
 173    return $res;
 174  } //end function export_fold_lines($string, $encoding="none", $limit=76)
 175  
 176  function export_get_attendee($id, $export) {
 177    global $login;
 178  
 179    $request = "SELECT webcal_entry_user.cal_login, webcal_entry_user.cal_status, " .
 180      " webcal_entry.cal_create_by " . 
 181      "FROM webcal_entry_user LEFT JOIN  webcal_entry " .
 182      " ON webcal_entry_user.cal_id = webcal_entry.cal_id " .
 183      " WHERE webcal_entry_user.cal_id = '$id' AND webcal_entry_user.cal_status <> 'D'";
 184  
 185    $att_res =  dbi_query($request);
 186  
 187    $count = 0;
 188  
 189    $attendee = array();
 190    $entry_array = array();
 191  
 192    while ($entry = dbi_fetch_row($att_res)) {
 193        $entry_array[$count++] = $entry;
 194    }
 195  
 196    dbi_free_result($att_res);
 197  
 198    $count = 0;
 199  
 200    while (list($key,$row) = each($entry_array)) {
 201        $request = "SELECT cal_firstname, cal_lastname, cal_email, cal_login " .
 202          " FROM webcal_user where cal_login = '". $row[0] . "'";
 203  
 204        $user_res = dbi_query($request);
 205  
 206        $user = dbi_fetch_row($user_res);
 207  
 208        dbi_free_result($user_res);
 209  
 210      if (count($user) > 0) {
 211        $attendee[$count] = "ATTENDEE;ROLE=";
 212        $attendee[$count] .=    ($row[2] == $user[3]) ? "OWNER;" : "ATTENDEE;";
 213        $attendee[$count] .= "STATUS=";
 214  
 215        switch ($row[1]) {
 216          case 'A':
 217            $attendee[$count] .= "CONFIRMED";
 218            break;
 219          case 'R':
 220            $attendee[$count] .= "DECLINED";
 221            break;
 222          case 'W':
 223            $attendee[$count] .= "SENT";
 224            break;
 225          default:
 226            continue;
 227        } //end switch
 228  
 229        if (strcmp($export,"vcal") == 0)
 230          $attendee[$count] .= ";ENCODING=QUOTED-PRINTABLE";
 231  
 232        $attendee[$count] .= ":$user[0] $user[1] <$user[2]>";
 233  
 234        $count++;
 235      } //end if (count($user) > 0)
 236    } //end while
 237  
 238    return $attendee;
 239  } //end function export_get_attendee($id, $export)
 240  
 241  function export_time($date, $duration, $time, $texport) {
 242    $year = (int) substr($date,0,-4);
 243    $month = (int) substr($date,-4,2);
 244    $day = (int) substr($date,-2,2);
 245  
 246    if ( $time == -1 ) {
 247        // untimed event
 248        $hour = 0;
 249        $min = 0;
 250        $sec = 0;
 251        $duration = 24 * 60 * 60;
 252        $str_duration = "1D";
 253  
 254        $start_tmstamp = mktime($hour, $min, $sec, $month, $day, $year);
 255  
 256        $start_date = date("Ymd", $start_tmstamp);
 257        echo "DTSTART;VALUE=DATE:$start_date\r\n";
 258    } else {
 259        // timed event or all-day event
 260        $hour = (int) substr($time,0,-4);
 261        $min = (int) substr($time,-4,2);
 262        $sec = (int) substr($time,-2,2);
 263        $str_duration = "T" . $duration . "M";
 264        $duration = $duration * 60;
 265  
 266        $start_tmstamp = mktime($hour, $min, $sec, $month, $day, $year);
 267  
 268        $utc_start = export_get_utc_date($date, $time);
 269        echo "DTSTART:$utc_start\r\n";
 270    }
 271  
 272    if (strcmp($texport,"ical") == 0) {
 273        $utc_dtstamp = export_get_utc_date(date("Ymd", mktime()),
 274          date("His", mktime()));
 275        echo "DTSTAMP:$utc_dtstamp\r\n";
 276  
 277      if ($time == -1) {
 278            // untimed event
 279        $end_tmstamp = $start_tmstamp + 24*60*60;
 280        $utc_end = date("Ymd", $end_tmstamp);
 281        echo "DTEND;VALUE=DATE:$utc_end\r\n";
 282      } else {
 283        $end_tmstamp = $start_tmstamp + $duration;
 284        $utc_end = export_get_utc_date(date("Ymd", $end_tmstamp), date("His", $end_tmstamp));
 285        echo "DTEND:$utc_end\r\n";
 286      }
 287    } elseif (strcmp($texport,"vcal") == 0) {
 288      if ($time == -1) {
 289        $end_tmstamp = $start_tmstamp + 24*60*60;
 290        $utc_end = date("Ymd", $end_tmstamp);
 291        echo "DTEND:$utc_end\r\n";
 292      } else {
 293        $end_tmstamp = $start_tmstamp + $duration;
 294        $utc_end = export_get_utc_date(date("Ymd", $end_tmstamp), date("His", $end_tmstamp));
 295        echo "DTEND:$utc_end\r\n";
 296      }
 297    } else {
 298        echo "DURATION:P$str_duration\r\n";
 299    }
 300  }
 301  
 302  function export_recurrence_ical($id, $date) {
 303    $sql = "SELECT cal_date FROM webcal_entry_repeats_not WHERE cal_id = '$id'";
 304  
 305    $res = dbi_query($sql);
 306  
 307    if ($res) {
 308        $exdate = array();
 309        $i = 0;
 310      while ($row = dbi_fetch_row($res)) {
 311        $exdate[$i] = $row[0];
 312        $i++;
 313      }
 314    }
 315  
 316    dbi_free_result($res);
 317  
 318    $sql = "SELECT webcal_entry_repeats.cal_type, webcal_entry_repeats.cal_end, "
 319      . "webcal_entry_repeats.cal_frequency, webcal_entry_repeats.cal_days, webcal_entry.cal_time"
 320      . " FROM webcal_entry, webcal_entry_repeats WHERE webcal_entry_repeats.cal_id = '$id'"
 321      . "AND webcal_entry.cal_id = '$id'";
 322  
 323    $res = dbi_query($sql);
 324  
 325    if ($res)
 326      $row = dbi_fetch_row($res);
 327  
 328    if ($row) {
 329        $type = $row[0];
 330        $end = $row[1];
 331        $freq = $row[2];
 332        $day = $row[3];
 333        $time = $row[4];
 334        $str_day = array( 'SU','MO','TU','WE','TH','FR','SA' );
 335        $byday = "";
 336  
 337        echo "RRULE:";
 338  
 339        /* recurrence frequency */
 340        switch ($type) {
 341      case 'daily' :
 342        echo "FREQ=DAILY";
 343        break;
 344      case 'weekly' :
 345        echo "FREQ=WEEKLY";
 346        break;
 347      case 'monthlyByDay':
 348      case 'monthlyByDate' :
 349        echo "FREQ=MONTHLY";
 350        break;
 351      case 'yearly' :
 352        echo "FREQ=YEARLY";
 353        break;
 354        }
 355  
 356        echo ";INTERVAL=$freq";
 357  
 358        if ($type == "weekly") {
 359        if ($day != "nnnnnnn") {
 360            echo ";BYDAY=";
 361          for ($i = 0; $i < strlen($day); $i++) {
 362            if ($day[$i] == 'y') {
 363                $byday .= $str_day[$i] .",";
 364            }
 365          }
 366            $byday = substr($byday, 0, strlen($byday)-1); // suppress last ','
 367            echo $byday;
 368        }
 369        } elseif ($type == "monthlyByDate") {
 370        $day = (int) substr($date,-2,2);
 371  
 372        echo ";BYMONTHDAY=$day";
 373        } elseif ($type == "monthlyByDay") {
 374        echo ";BYDAY=";
 375  
 376        $year = (int) substr($date,0,-4);
 377        $month = (int) substr($date,-4,2);
 378        $day = (int) substr($date,-2,2);
 379  
 380        $stamp = mktime(0, 0, 0, $month, $day, $year);
 381  
 382        $date_array = getdate($stamp);
 383  
 384        echo $str_day[$date_array['wday']];
 385  
 386        $next_stamp = $stamp + 7 * 24 * 60 * 60;
 387  
 388        $next_date_array = getdate($next_stamp);
 389  
 390        if ($date_array['mon'] != $next_date_array['mon'])
 391          $pos = -1;
 392        else {
 393            $pos = (int) ($day / 7);
 394  
 395            if (($day % 7) > 0) {
 396            $pos++;
 397            }
 398         }
 399        echo ";BYSETPOS=$pos";
 400        }
 401  
 402        if (!empty($end))    {
 403        echo ";UNTIL=";
 404  
 405        $utc = export_get_utc_date($end, $time);
 406  
 407        echo $utc;
 408        }
 409  
 410        echo "\r\n";
 411  
 412        if (count($exdate) > 0) {
 413        $string = "EXDATE:";
 414        $i = 0;
 415        while ($i < count($exdate)) {
 416            $date = export_get_utc_date($exdate[$i],$time);
 417            $string .= "$date,";
 418            $i++;
 419        }
 420  
 421        $string = substr($string, 0, strlen($string)-1); // suppress last ','
 422  
 423        $string = export_fold_lines($string);
 424  
 425        while (list($key,$value) = each($string))
 426          echo "$value\r\n";
 427        }
 428      }
 429  }
 430  
 431  function export_recurrence_vcal($id, $date) {
 432    $sql = "SELECT cal_date FROM webcal_entry_repeats_not WHERE cal_id = '$id'";
 433    $res = dbi_query($sql);
 434  
 435    if ($res) {
 436      $exdate = array();
 437      while ($row = dbi_fetch_row($res)) {
 438        $exdate[] = $row[0];
 439      }
 440    }
 441  
 442    dbi_free_result($res);
 443  
 444    $sql = "SELECT webcal_entry_repeats.cal_type, webcal_entry_repeats.cal_end, "
 445      . "webcal_entry_repeats.cal_frequency, webcal_entry_repeats.cal_days, webcal_entry.cal_time"
 446      . " FROM webcal_entry, webcal_entry_repeats WHERE webcal_entry_repeats.cal_id = '$id'"
 447      . "AND webcal_entry.cal_id = '$id'";
 448  
 449    $res = dbi_query($sql);
 450    $row = dbi_fetch_row($res);
 451  
 452    //echo $sql;exit;
 453  
 454    if ($row) {
 455        $type = $row[0];
 456        $end = $row[1];
 457        $freq = $row[2];
 458        $day = $row[3];
 459        $time = $row[4];
 460        $str_day = array('SU','MO','TU','WE','TH','FR','SA');
 461        $byday = "";
 462  
 463        echo "RRULE:";
 464  
 465        /* recurrence frequency */
 466        switch ($type) {
 467      case 'daily' :
 468        echo "D";
 469        break;
 470      case 'weekly' :
 471        echo "W";
 472        break;
 473      case 'monthlyByDay':
 474        echo "MP";
 475        break;
 476      case 'monthlyByDayR':
 477        echo "MP";
 478        break;
 479      case 'monthlyByDate' :
 480        echo "MD";
 481        break;
 482      case 'yearly' :
 483        echo "YM";
 484        break;
 485        }
 486  
 487        echo $freq." ";
 488  
 489      if ($type == "weekly") {
 490        if ($day != "nnnnnnn") {
 491          for ($i=0; $i < strlen($day); $i++) {
 492            if ($day[$i] == 'y') {
 493          $byday .= $str_day[$i] ." ";
 494                }
 495          }
 496          echo $byday;
 497            }
 498      } elseif ($type == "monthlyByDate") {
 499        $day = (int) substr($date,-2,2);
 500        echo "$day ";
 501      } elseif (($type == "monthlyByDay") || ($type == "monthlyByDayR")) {
 502        $year = (int) substr($date,0,-4);
 503        $month = (int) substr($date,-4,2);
 504        $day = (int) substr($date,-2,2);
 505  
 506        $stamp = mktime(0, 0, 0, $month, $day, $year);
 507        $date_array = getdate($stamp);
 508            $day_no = $str_day[$date_array['wday']];
 509  
 510        $next_stamp = $stamp + 7 * 24 * 60 * 60;
 511        $next_date_array = getdate($next_stamp);
 512  
 513        if ($date_array['mon'] != $next_date_array['mon']) {
 514          $pos = 5;
 515        } else {
 516          $pos = (int) ($day / 7);
 517              if (($day % 7) > 0) {
 518                $pos++;
 519              }
 520         }
 521        echo $pos."+ $day_no ";
 522      } elseif ($type == "yearly") {
 523            $month = (int) substr($date,-4,2);
 524            echo "$month ";
 525      }
 526  
 527      // End Date - For all types
 528      if (!empty($end)) {
 529        //echo export_get_utc_date($end, $time);
 530        echo $end;
 531      } else {
 532        echo "20311231";
 533      }
 534  
 535        echo "\r\n";
 536  
 537      // Repeating Exceptions
 538      $num = count($exdate);
 539      if ($num > 0) {
 540        $string = "EXDATE:";
 541  
 542        for ($i=0;$i<$num;$i++) {
 543          $string .= $exdate[$i]."T000000,";
 544        }
 545        echo rtrim($string,",")."\r\n";
 546      }
 547  
 548    }
 549  }
 550  
 551  
 552  /*
 553   * Create a date-time format (e.g. "20041130T123000Z") that is
 554   * converted from local timezone to GMT.
 555   */
 556  function export_get_utc_date($date, $time=0) {
 557    $year = (int) substr($date,0,-4);
 558    $month = (int) substr($date,-4,2);
 559    $day = (int) substr($date,-2,2);
 560  
 561    if ($time <= 0) {
 562        $hour = 0;
 563        $min = 0;
 564        $sec = 0;
 565    } else {
 566        $hour = (int) substr($time,0,-4);
 567        $min = (int) substr($time,-4,2);
 568        $sec = (int) substr($time,-2,2);
 569    }
 570  
 571    $tmstamp = mktime($hour, $min, $sec, $month, $day, $year);
 572  
 573    $utc_date = gmdate("Ymd", $tmstamp);
 574    $utc_hour = gmdate("His", $tmstamp);
 575  
 576    $utc = sprintf ("%sT%sZ", $utc_date, $utc_hour);
 577  
 578    return $utc;
 579  }
 580  
 581  function export_alarm_vcal($id,$date,$time=0) {
 582    $sql = "SELECT cal_data FROM webcal_site_extras " .
 583           "WHERE cal_id = $id AND cal_type = 7 AND cal_remind = 1";
 584    $res = dbi_query ( $sql );
 585    $row = dbi_fetch_row ( $res );
 586    dbi_free_result ( $res );
 587  
 588    if ($row) {
 589      echo "DALARM:";
 590      $offset = $row[0] * 60; // How many seconds
 591      $year = (int) substr($date,0,-4);
 592      $month = (int) substr($date,-4,2);
 593      $day = (int) substr($date,-2,2);
 594      $hour = ($time > 0) ? (int) substr($time,0,-4) : 0;
 595      $min  = ($time > 0) ? (int) substr($time,-4,2) : 0;
 596      $sec  = ($time > 0) ? (int) substr($time,-2,2) : 0;
 597      $stamp = mktime($hour, $min, $sec, $month, $day, $year);
 598      $atime = $stamp - $offset;
 599      echo gmdate("Ymd\THis\Z", $atime)."\r\n";
 600    }
 601  }
 602  
 603  
 604  function export_alarm_ical($id, $description) {
 605    $sql = "SELECT cal_data FROM webcal_site_extras " .
 606           "WHERE cal_id = $id AND cal_type = 7 AND cal_remind = 1";
 607    $res = dbi_query ( $sql );
 608    $row = dbi_fetch_row ( $res );
 609    dbi_free_result ( $res );
 610  
 611    if ($row) {
 612      echo "BEGIN:VALARM\r\n";
 613      echo "TRIGGER:-PT".$row[0]."M\r\n";
 614      echo "ACTION:DISPLAY\r\n";
 615  
 616      $array = export_fold_lines($description,"utf8");
 617      while (list($key,$value) = each($array)) {
 618        echo "$value\r\n";
 619      }
 620  
 621      echo "END:VALARM\r\n";
 622    }
 623  }
 624  
 625  
 626  function generate_uid() {
 627    $rand = mt_rand(1000000,9999999);
 628  
 629    $utc_id_date = gmdate("Ymd", mktime());
 630    $utc_id_hour = gmdate("His", mktime());
 631    $pid = getmypid() or die ("System error");
 632    $host = getenv("SERVER_NAME");
 633  
 634    return sprintf ("%sT%sZ-%d-%d@%s", $utc_id_date, $utc_id_hour, $rand, $pid, $host);
 635  }
 636  
 637  
 638  function export_vcal ($id) {
 639    global $prodid;
 640    header ( "Content-Type: text/x-vcalendar" );
 641    //header ( "Content-Type: text/plain" );
 642  
 643    $res = export_get_event_entry($id);
 644  
 645    $entry_array = array();
 646    $count = 0;
 647  
 648    while ( $entry = dbi_fetch_row($res) ) {
 649        $entry_array[$count++] = $entry;
 650    }
 651  
 652    dbi_free_result($res);
 653  
 654    if (count($entry_array) > 0) {
 655      echo "BEGIN:VCALENDAR\r\n";
 656      echo "$prodid\r\n";
 657      echo "VERSION:1.0\r\n";
 658  
 659      /* Time Zone
 660      $tzdate = mktime();
 661      $gmdate = gmmktime();
 662      $tzdiff = ($gmdate - $tzdate) / 60 / 60; //FIXME only hours are represented
 663  
 664      $tz = sprintf("%02d", $tzdiff);
 665  
 666      echo "TZ:";
 667      echo ($tzdiff >= 0) ? "+" : "-";
 668      echo "$tz\r\n";
 669  
 670      */
 671    }
 672  
 673    while (list($key,$row) = each($entry_array)) {
 674        $uid = $row[0];
 675        $export_uid = generate_uid();
 676        $name = $row[1];
 677        $priority = $row[2];
 678        $date = $row[3];
 679        $time = $row[4];
 680        $status = $row[5];
 681        $create_by = $row[6];
 682        $access = $row[7];
 683        $duration = $row[8];
 684        $description = $row[9];
 685  
 686        /* Start of event */
 687        echo "BEGIN:VEVENT\r\n";
 688  
 689        /* UID of the event (folded to 76 char) */
 690        $export_uid = "UID:$export_uid";
 691        $array = export_fold_lines($export_uid);
 692        while (list($key,$value) = each($array))
 693      echo "$value\r\n";
 694  
 695        /* SUMMARY of the event (folded to 76 char) */
 696        $name = preg_replace("/\\\\/", "\\\\\\", $name); // ??
 697        $name = "SUMMARY;ENCODING=QUOTED-PRINTABLE:" . $name;
 698        $array = export_fold_lines($name,"quotedprintable");
 699  
 700        while (list($key,$value) = each($array))
 701      echo "$value\r\n";
 702  
 703        /* DESCRIPTION if any (folded to 76 char) */
 704        if ($description != "") {
 705        $description = preg_replace("/\\\\/", "\\\\\\", $description); // ??
 706        $description = "DESCRIPTION;ENCODING=QUOTED-PRINTABLE:" . $description;
 707        $array = export_fold_lines($description,"quotedprintable");
 708        while (list($key,$value) = each($array))
 709          echo "$value\r\n";
 710        } //end if ($description != "")
 711  
 712        /* CLASS either "PRIVATE" or "PUBLIC" (the default) */
 713        if ($access == "R") {
 714        echo "CLASS:PRIVATE\r\n";
 715        } else {
 716        echo "CLASS:PUBLIC\r\n";
 717        }
 718  
 719        // ATTENDEE of the event
 720        $attendee = export_get_attendee($row[0], "vcal");
 721  
 722        for ($i = 0; $i < count($attendee); $i++) {
 723        $attendee[$i] = export_fold_lines($attendee[$i],"quotedprintable");
 724        while (list($key,$value) = each($attendee[$i]))
 725          echo "$value\r\n";
 726        }
 727  
 728        /* Time - all times are utc */
 729        export_time($date, $duration, $time, "vcal");
 730  
 731        export_recurrence_vcal($uid, $date);
 732  
 733        // FIXME: handle alarms
 734        export_alarm_vcal($uid,$date,$time);
 735  
 736        /* Goodbye event */
 737        echo "END:VEVENT\n";
 738      } //end while (list($key,$row) = each($entry_array))
 739  
 740    if (count($entry_array) > 0)
 741      echo "END:VCALENDAR\r\n";
 742  } //end function
 743  
 744  function export_ical ($id) {
 745    global $prodid;
 746  
 747    header ( "Content-Type: text/calendar" );
 748    //header ( "Content-Type: text/plain" );
 749  
 750    $res = export_get_event_entry($id);
 751  
 752    $entry_array = array();
 753    $count = 0;
 754  
 755    while ( $entry = dbi_fetch_row($res) ) {
 756        $entry_array[$count++] = $entry;
 757    }
 758  
 759    if ($count > 0) {
 760      echo "BEGIN:VCALENDAR\r\n";
 761      echo "$prodid\r\n";
 762      echo "VERSION:2.0\r\n";
 763      echo "METHOD:PUBLISH\r\n";
 764    }
 765  
 766    while (list($key,$row) = each($entry_array)) {
 767        $uid = $row[0];
 768        $export_uid = generate_uid();
 769        $name = $row[1];
 770        $priority = $row[2];
 771        $date = $row[3];
 772        $time = $row[4];
 773        $status = $row[5];
 774        $create_by = $row[6];
 775        $access = $row[7];
 776        $duration = $row[8];
 777        $description = $row[9];
 778  
 779        /* Start of event */
 780        echo "BEGIN:VEVENT\r\n";
 781  
 782        /* UID of the event (folded to 76 char) */
 783        $array = export_fold_lines("UID:$export_uid");
 784        while (list($key,$value) = each($array))
 785      echo "$value\r\n";
 786  
 787        $name = preg_replace("/\r/", "", $name);
 788        //$name = preg_replace("/\n/", "\\n", $name); //PER RFC2445
 789        $name = preg_replace("/\\\\/", "\\\\\\", $name); // ??
 790        $description = preg_replace("/\r/", "", $description);
 791        //$description = preg_replace("/\n/", "\\n", $description); //PER RFC2445
 792        $description = preg_replace("/\\\\/", "\\\\\\", $description); // ??
 793  
 794        /* SUMMARY of the event (folded to 76 char) */
 795        $name = "SUMMARY:" . $name;
 796        $array = export_fold_lines($name,"utf8");
 797  
 798        while (list($key,$value) = each($array))
 799      echo "$value\r\n";
 800  
 801        /* DESCRIPTION if any (folded to 76 char) */
 802        if ($description != "") {
 803        $description = "DESCRIPTION:" . $description;
 804        $array = export_fold_lines($description,"utf8");
 805        while (list($key,$value) = each($array))
 806          echo "$value\r\n";
 807        }
 808  
 809        /* CLASS either "PRIVATE" or "PUBLIC" (the default) */
 810        if ($access == "R") {
 811        echo "CLASS:PRIVATE\r\n";
 812        } else {
 813        echo "CLASS:PUBLIC\r\n";
 814        }
 815  
 816        // ATTENDEE of the event
 817        $attendee = export_get_attendee($row[0], "ical");
 818  
 819        for ($i = 0; $i < count($attendee); $i++) {
 820        $attendee[$i] = export_fold_lines($attendee[$i],"utf8");
 821        while (list($key,$value) = each($attendee[$i]))
 822          echo "$value\r\n";
 823        }
 824  
 825        /* Time - all times are utc */
 826        export_time($date, $duration, $time, "ical");
 827  
 828        /* Recurrence */
 829        export_recurrence_ical($uid, $date);
 830  
 831        // FIXME: handle alarms
 832        export_alarm_ical($uid, $description);
 833  
 834        /* Goodbye event */
 835        echo "END:VEVENT\r\n";
 836      } //end while (list($key,$row) = each($entry_array))
 837  
 838    if ($count > 0)
 839      echo "END:VCALENDAR\r\n";
 840  } //end function
 841  
 842  
 843  // convert time in ("hhmmss") format, plus duration (as a number of
 844  // minutes), to end time ($hour = number of hours, $min = number of
 845  // minutes).
 846  // FIXME: doesn't handle wrap to next day correctly.
 847  function get_end_time ( $time, $duration, &$hour, &$min) {
 848    $hour = (int) ( $time / 10000 );
 849    $min = ( $time / 100 ) % 100;
 850    $minutes = $hour * 60 + $min + $duration;
 851    $hour = $minutes / 60;
 852    $min = $minutes % 60;
 853  }
 854  
 855  // convert calendar date to a format suitable for the install-datebook
 856  // utility (part of pilot-link)
 857  function pilot_date_time ( $date, $time, $duration, $csv=false ) {
 858    $year = (int) ( $date / 10000 );
 859    $month = (int) ( $date / 100 ) % 100;
 860    $mday = $date % 100;
 861    get_end_time ( $time, $duration, $hour, $min );
 862  
 863    // Assume that the user is in the same timezone as server
 864    $tz_offset = date ( "Z" ); // in seconds
 865    $tzh = (int) ( $tz_offset / 3600 );
 866    $tzm = (int) ( $tz_offset / 60 ) % 60;
 867    if ( $tzh < 0 ) {
 868      $tzsign = "-";
 869      $tzh = abs ( $tzh );
 870    } else
 871      $tzsign = "+";
 872  
 873    if ( $csv )
 874      return sprintf ( "%04d-%02d-%02d%s%02d:%02d:00",
 875               $year, $month, $mday, $csv, $hour, $min );
 876    else
 877      return sprintf ( "%04d/%02d/%02d %02d%02d  GMT%s%d%02d",
 878               $year, $month, $mday, $hour, $min, $tzsign, $tzh, $tzm );
 879  } //end function
 880  
 881  function export_install_datebook ($id) {
 882    $res = export_get_event_entry($id);
 883  
 884    while ( $row = dbi_fetch_row ( $res ) ) {
 885      $start_time = pilot_date_time ( $row[3], $row[4], 0 );
 886      $end_time = pilot_date_time ( $row[3], $row[4], $row[8] );
 887      printf ( "%s\t%s\t\t%s\n",
 888           $start_time, $end_time, $row[1] );
 889      echo "Start time: $start_time\n";
 890      echo "End time: $end_time\n";
 891      echo "Duration: $row[8]\n";
 892      echo "Name: $row[1]\n";
 893    }
 894  } //end function
 895  
 896  function get_cal_ent_extras($id, $from, $where = false) {
 897    $res = dbi_query( "SELECT * FROM $from WHERE cal_id='$id'".    ( $where?"AND ( $where );":';') );
 898    if ( $res )
 899      return ( dbi_fetch_row($res) );
 900    else
 901      return ( false );
 902  } //end function
 903  
 904  function export_pilot_csv ($id) {
 905    /* to be imported to a Palm with:
 906     *        pilot-datebook -r csv -f webcalendar-export.txt -w hotsync
 907     */
 908  
 909    $res = export_get_event_entry($id);
 910  
 911    echo "uid,attributes,category,untimed,beginDate,beginTime,endDate,endTime,description,note,alarm,advance,advanceUnit,repeatType,repeatForever,repeatEnd,repeatFrequency,repeatDay,repeatWeekdays,repeatWeekstart\n";
 912    while ( $row = dbi_fetch_row ( $res ) ) {
 913      // uid (long)
 914      echo $row[0], ',';
 915      // attributes (int)
 916      //  128 = 0x80 : Deleted
 917      //   64 = 0x40 : Dirty
 918      //   32 = 0x20 : Busy
 919      //   16 = 0x10 : Secret/Private
 920      echo ($row[7] == 'R')?'16,':'0,';
 921      // category (int: 0=Unfiled)
 922      echo '0,';
 923      // untimed (int: 0=Appointment, 1=Untimed)
 924      // note: Palm "Untimed" is WebCalendar "AllDay"
 925      if ( $row[4] < 0 ) {
 926        echo
 927      '1,',                // untimed
 928      substr($row[3],0,4), '-',    // beginDate (str: YYYY-MM-DD) + beginTime
 929      substr($row[3],4,2), '-',
 930      substr($row[3],6,2), ',00:00:00,',
 931      substr($row[3],0,4), '-',    // endDate + endTime
 932      substr($row[3],4,2), '-',
 933      substr($row[3],6,2), ',00:00:00,';
 934      } else {
 935        echo '0,', // untimed
 936      pilot_date_time($row[3], $row[4], 0, ','), ',',    // beginDate,beginTime
 937      pilot_date_time($row[3], $row[4], $row[8], ','), ',';    //endDate,endTime
 938      } //end if ( $row[4] < 0 )
 939      // description (str)
 940      echo '"', preg_replace("/\x0D?\n/", "\\n", $row[1]), '",';
 941      // note (str)
 942      echo '"', preg_replace("/\x0D?\n/", "\\n", $row[9]), '",';
 943      // alarm, advance, advanceUnit
 944      // alarm (int: 0=no alarm, 1=alarm)
 945      // FIXME: verify if WebCal. DB interpreted correctly
 946      // advance (int), advanceUnit (int: 0=minutes, 1=hours, 2=days)
 947      // FIXME: better adjust unit
 948      $ext = get_cal_ent_extras($row[0], 'webcal_site_extras', "cal_name = 'Reminder' AND cal_remind = 1");
 949      if ( $ext )
 950        echo '1,', $ext[5], ',0,';
 951      else
 952        echo '0,0,0,';
 953      // repeat:
 954      // repeatType (int: 0=none, 1=daily, 2=weekly, 3=monthly, 4=monthly/weekday,
 955      // repeatForever (int: 0=not forever, 1=forever)                   5=yearly)
 956      // repeatEnd (time)
 957      // repeatFrequency (int)
 958      // repeatDay (int: day# or 0..6=Sun..Sat 1st, 7..13 2nd, 14..20 3rd,
 959      //                    21..27 4th,  28-34 last week)
 960      // repeatWeekdays (int: add - 1=Sun,2=Mon,4=Tue,8=Wed,16=Thu,32=Fri,64=Sat)
 961      // repeatWeekstart (int)
 962      $ext = get_cal_ent_extras($row[0], 'webcal_entry_repeats');
 963      if ( $ext ) {
 964        switch ( $ext[1] ) {
 965      case 'daily':        $repType = 1; break;
 966      case 'weekly':        $repType = 2; break;
 967      case 'monthlyByDate':    $repType = 3; break;
 968      case 'monthlyByDay':    $repType = 4; break;
 969      case 'yearly':        $repType = 5; break;
 970      default:            $repType = 0;
 971        }
 972      } else $repType = 0;
 973      if ( $repType ) {
 974        echo $repType, ',';        // repeatType
 975        if ( $ext[2] ) {
 976      echo '0,',             // repeatForever
 977        substr($ext[2],0,4), '-',    // repeatEnd
 978        substr($ext[2],4,2), '-',
 979        substr($ext[2],6,2), ' 00:00:00,';
 980        } else
 981      echo '1,,';    // repeatForever,repeatEnd
 982        echo $ext[3], ',';// repeatFrequency
 983        switch ( $repType ) {
 984      case 2:    // weekly
 985        echo '0,', bindec(strtr(strrev($ext[4]),'yn','10')) ,",1\n";
 986        break;
 987      case 3:    // monthly/weekday
 988          // repeatDay (0..6=Sun..Sat 1st, 7..13 2nd, 14..20 3rd,
 989          // 21..27 4th,  28-34 last week)
 990          echo floor( substr($row[3], 6, 2) / 7) *7
 991            + date( 'w', date_to_epoch($row[3]) ), ",0,0\n";
 992          break;
 993      case 1:    // daily
 994      case 4:    // monthly
 995      case 5:    // yearly
 996          echo "0,0,0\n";
 997        } //end switch
 998      } else
 999        echo "0,0,,0,0,0,0\n";
1000      } //end if ( $repType )
1001  } //end function
1002  
1003  function transmit_header ( $mime, $file ) {
1004    header ( "Content-Type: application/octet-stream" );
1005    //header ( "Content-Type: $mime" );
1006    header ( 'Content-Disposition: attachment; filename="' . $file .  '"');
1007    header ( 'Pragma: no-cache');
1008    header ( 'Cache-Control: no-cache' );
1009  } //end function
1010  
1011  /*******************************************/
1012  /*** Let's go ***/
1013  /*******************************************/
1014  
1015  $id = getIntValue  ( 'id', true );
1016  $format = getValue  ( 'format' );
1017  if ( $format != 'ical' && $format != 'vcal' && $format != 'pilot-csv' &&
1018    $format != 'pilot-text' )
1019    die_miserable_death ( "Invalid format '" . $format . "'" );
1020  
1021  $use_all_dates = getPostValue  ( 'use_all_dates' );
1022  if ( $use_all_dates != 'y' )
1023    $use_all_dates = '';
1024  
1025  $include_layers = getPostValue  ( 'include_layers' );
1026  if ( $include_layers != 'y' )
1027   $include_layers = '';
1028  
1029  $fromyear = getIntValue  ( 'fromyear', true );
1030  $frommonth = getIntValue  ( 'frommonth', true );
1031  $fromday = getIntValue  ( 'fromday', true );
1032  $endyear = getIntValue  ( 'endyear', true );
1033  $endmonth = getIntValue  ( 'endmonth', true );
1034  $endday = getIntValue  ( 'endday', true );
1035  $modyear = getIntValue  ( 'modyear', true );
1036  $modmonth = getIntValue  ( 'modmonth', true );
1037  $modday = getIntValue  ( 'modday', true );
1038  
1039  mt_srand((float) microtime()*1000000);
1040  
1041  if (empty($id)) {
1042    $id = "all";
1043  }
1044  
1045  if ($format == "ical") {
1046    transmit_header ( 'text/ical', "webcalendar-$id.ics" );
1047    export_ical($id);
1048  } elseif ($format == "vcal") {
1049    transmit_header ( 'text/vcal', "webcalendar-$id.vcs" );
1050    export_vcal($id);
1051  } elseif ($format == "pilot-csv") {
1052    transmit_header ( 'text/csv', "webcalendar-$id.csv" );
1053    export_pilot_csv ( $id );
1054  } elseif ($format == "pilot-text") {
1055    transmit_header('text/plain', "webcalendar-$id.txt" );
1056    export_install_datebook($id);
1057  } else {
1058    //exit;
1059  
1060    print_header();
1061  
1062    echo "<h2>";
1063    etranslate("Export");
1064    echo " ";
1065    etranslate("Error");
1066    echo "</h2>\n";
1067    echo "<span style=\"font-weight:bold;\">";
1068    etranslate("Error");
1069    echo ":</span> ";
1070    echo translate("export format not defined or incorrect") . ".";
1071    echo "<br />\n";
1072  
1073    print_trailer ();
1074  
1075    echo " </body>\n";
1076    echo "</html>";
1077  } //end if ($format == "ical")