[Sommaire] [Imprimer]

   1  #!/usr/local/bin/php -q
   2  <?php
   3  /* $Id: send_reminders.php,v 1.21.2.3 2006/06/07 15:10:46 cknudsen Exp $
   4   *
   5   * Description:
   6   * This is a command-line script that will send out any email
   7   * reminders that are due.
   8   *
   9   * Usage:
  10   * php send_reminders.php
  11   *
  12   * Setup:
  13   * This script should be setup to run periodically on your system.
  14   * You could run it once every minute, but every 5-15 minutes should be
  15   * sufficient.
  16   *
  17   * To set this up in cron, add a line like the following in your crontab
  18   * to run it every 10 minutes:
  19   *   1,11,21,31,41,51 * * * * php /some/path/here/send_reminders.php
  20   * Of course, change the path to where this script lives.  If the
  21   * php binary is not in your $PATH, you may also need to provide
  22   * the full path to "php".
  23   * On Linux, just type crontab -e to edit your crontab.
  24   *
  25   * If you're a Windows user, you'll either need to find a cron clone
  26   * for Windows (they're out there) or use the Windows Task Scheduler.
  27   * (See docs/WebCalendar-SysAdmin.html for instructions.)
  28   * 
  29   * Comments:
  30   * You will need access to the PHP binary (command-line) rather than
  31   * the module-based version that is typically installed for use with
  32   * a web server.to build as a CGI (rather than an Apache module) for
  33   *
  34   * If running this script from the command line generates PHP
  35   * warnings, you can disable error_reporting by adding
  36   * "-d error_reporting=0" to the command line:
  37   *   php -d error_reporting=0 /some/path/here/tools/send_reminders.php
  38   *
  39   *********************************************************************/
  40  
  41  // How many days in advance can a reminder be sent (max)
  42  // this will affect performance, but keep in mind that someone may enter
  43  // a reminder to be sent 60 days in advance or they may enter a specific
  44  // date for a reminder to be sent that is more than 30 days before the
  45  // event's date.  If you're only running this once an hour or less often,
  46  // then you could certainly change this to look a whole 365 days ahead.
  47  $DAYS_IN_ADVANCE = 30;
  48  //$DAYS_IN_ADVANCE = 365;
  49  
  50  
  51  // Load include files.
  52  // If you have moved this script out of the WebCalendar directory,
  53  // which you probably should do since it would be better for security
  54  // reasons, you would need to change $includedir to point to the
  55  // webcalendar include directory.
  56  // We set these twice since config.php unsets these.
  57  $includedir = "../includes"; // Set again after config.php
  58  
  59  include "$includedir/config.php";
  60  
  61  $basedir = ".."; // points to the base WebCalendar directory relative to
  62                   // current working directory
  63  $includedir = "../includes";
  64  
  65  include "$includedir/php-dbi.php";
  66  include "$includedir/functions.php";
  67  include "$includedir/$user_inc";
  68  include "$includedir/site_extras.php";
  69  
  70  $debug = false; // set to true to print debug info...
  71  $only_testing = false; // act like we're sending, but don't send -- for debugging
  72  
  73  // Establish a database connection.
  74  $c = dbi_connect ( $db_host, $db_login, $db_password, $db_database );
  75  if ( ! $c ) {
  76    echo "Error connecting to database: " . dbi_error ();
  77    exit;
  78  }
  79  
  80  load_global_settings ();
  81  
  82  include "$includedir/translate.php";
  83  
  84  if ( $debug )
  85    echo "<br />\n";
  86  
  87  // Get a list of people who have asked not to receive email
  88  $res = dbi_query ( "SELECT cal_login FROM webcal_user_pref " .
  89    "WHERE cal_setting = 'EMAIL_REMINDER' " .
  90    "AND cal_value = 'N'" );
  91  $noemail = array ();
  92  if ( $res ) {
  93    while ( $row = dbi_fetch_row ( $res ) ) {
  94      $user = $row[0];
  95      $noemail[$user] = 1;
  96      if ( $debug )
  97        echo "User $user does not want email. <br />\n";
  98    }
  99    dbi_free_result ( $res );
 100  }
 101  
 102  // Get a list of the email users in the system.
 103  // They must also have an email address.  Otherwise, we can't
 104  // send them mail, so what's the point?
 105  $allusers = user_get_users ();
 106  for ( $i = 0; $i < count ( $allusers ); $i++ ) {
 107    $names[$allusers[$i]['cal_login']] = $allusers[$i]['cal_fullname'];
 108    $emails[$allusers[$i]['cal_login']] = $allusers[$i]['cal_email'];
 109  }
 110  
 111  
 112  // Get all users language settings.
 113  $res = dbi_query ( "SELECT cal_login, cal_value FROM webcal_user_pref " .
 114    "WHERE cal_setting = 'LANGUAGE'" );
 115  $languages = array ();
 116  if ( $res ) {
 117    while ( $row = dbi_fetch_row ( $res ) ) {
 118      $user = $row[0];
 119      $user_lang = $row[1];
 120      $languages[$user] = $user_lang;
 121      if ( $debug )
 122        echo "Language for $user is \"$user_lang\" <br />\n";
 123    }
 124    dbi_free_result ( $res );
 125  }
 126  
 127  // Get all users timezone settings.
 128  $res = dbi_query ( "SELECT cal_login, cal_value FROM webcal_user_pref " .
 129    "WHERE cal_setting = 'TZ_OFFSET'" );
 130  $tzoffset = array ();
 131  if ( $res ) {
 132    while ( $row = dbi_fetch_row ( $res ) ) {
 133      $user = $row[0];
 134      $user_tzoffset = $row[1];
 135      $tzoffset[$user] = $user_tzoffset;
 136      if ( $debug )
 137        echo "TZ OFFSET for $user is \"$user_tzoffset\" <br />\n";
 138    }
 139    dbi_free_result ( $res );
 140  }
 141  
 142  $startdate = date ( "Ymd" );
 143  $enddate = date ( "Ymd", time() + ( $DAYS_IN_ADVANCE * 24 * 3600 ) );
 144  
 145  // Now read events all the repeating events (for all users)
 146  $repeated_events = query_events ( "", true, "AND (webcal_entry_repeats.cal_end > $startdate OR webcal_entry_repeats.cal_end IS NULL) " );
 147  
 148  // Read non-repeating events (for all users)
 149  if ( $debug )
 150    echo "Checking for events from date $startdate to date $enddate <br />\n";
 151  $events = read_events ( "", $startdate, $enddate );
 152  if ( $debug )
 153    echo "Found " . count ( $events ) . " events in time range. <br />\n";
 154  
 155  
 156  function indent ( $str ) {
 157    return "  " . str_replace ( "\n", "\n  ", $str );
 158  }
 159  
 160  
 161  // Send a reminder for a single event for a single day to all
 162  // participants in the event.
 163  // Send to participants who have accepted as well as those who have not yet
 164  // approved.  But, don't send to users how rejected (cal_status='R').
 165  function send_reminder ( $id, $event_date ) {
 166    global $names, $emails, $site_extras, $debug, $only_testing,
 167      $server_url, $languages, $tzoffset, $application_name;
 168    global $EXTRA_TEXT, $EXTRA_MULTILINETEXT, $EXTRA_URL, $EXTRA_DATE,
 169      $EXTRA_EMAIL, $EXTRA_USER, $EXTRA_REMINDER, $LANGUAGE, $LOG_REMINDER;
 170    global $allow_external_users, $external_reminders;
 171  
 172    $pri[1] = translate("Low");
 173    $pri[2] = translate("Medium");
 174    $pri[3] = translate("High");
 175  
 176    // get participants first...
 177   
 178    $sql = "SELECT cal_login FROM webcal_entry_user " .
 179      "WHERE cal_id = $id AND cal_status IN ('A','W') " .
 180      "ORDER BY cal_login";
 181    $res = dbi_query ( $sql );
 182    $participants = array ();
 183    $num_participants = 0;
 184    if ( $res ) {
 185      while ( $row = dbi_fetch_row ( $res ) ) {
 186        $participants[$num_participants++] = $row[0];
 187      }
 188    }
 189  
 190    // get external participants
 191    $ext_participants = array ();
 192    $num_ext_participants = 0;
 193    if ( ! empty ( $allow_external_users ) && $allow_external_users == "Y" &&
 194      ! empty ( $external_reminders ) && $external_reminders == "Y" ) {
 195      $sql = "SELECT cal_fullname, cal_email FROM webcal_entry_ext_user " .
 196        "WHERE cal_id = $id AND cal_email IS NOT NULL " .
 197        "ORDER BY cal_fullname";
 198      $res = dbi_query ( $sql );
 199      if ( $res ) {
 200        while ( $row = dbi_fetch_row ( $res ) ) {
 201          $ext_participants[$num_ext_participants] = $row[0];
 202          $ext_participants_email[$num_ext_participants++] = $row[1];
 203        }
 204      }
 205    }
 206  
 207    if ( ! $num_participants && ! $num_ext_participants ) {
 208      if ( $debug )
 209        echo "No participants found for event id: $id <br />\n";
 210      return;
 211    }
 212  
 213  
 214    // get event details
 215    $res = dbi_query (
 216      "SELECT cal_create_by, cal_date, cal_time, cal_mod_date, " .
 217      "cal_mod_time, cal_duration, cal_priority, cal_type, cal_access, " .
 218      "cal_name, cal_description FROM webcal_entry WHERE cal_id = $id" );
 219    if ( ! $res ) {
 220      echo "Db error: could not find event id $id.\n";
 221      return;
 222    }
 223  
 224  
 225    if ( ! ( $row = dbi_fetch_row ( $res ) ) ) {
 226      echo "Error: could not find event id $id in database.\n";
 227      return;
 228    }
 229  
 230    // send mail.  we send one user at a time so that we can switch
 231    // languages between users if needed.
 232    $mailusers = array ();
 233    $recipients = array ();
 234    if ( isset ( $single_user ) && $single_user == "Y" ) {
 235      $mailusers[] = $emails[$single_user_login];
 236      $recipients[] = $single_user_login;
 237    } else {
 238      for ( $i = 0; $i < count ( $participants ); $i++ ) {
 239        if ( strlen ( $emails[$participants[$i]] ) ) {
 240          $mailusers[] = $emails[$participants[$i]];
 241          $recipients[] = $participants[$i];
 242        } else {
 243          if ( $debug )
 244     echo "No email for user $participants[$i] <br />\n";
 245        }
 246      }
 247      for ( $i = 0; $i < count ( $ext_participants ); $i++ ) {
 248        $mailusers[] = $ext_participants_email[$i];
 249        $recipients[] = $ext_participants[$i];
 250      }
 251    }
 252    if ( $debug )
 253      echo "Found " . count ( $mailusers ) . " with email addresses <br />\n";
 254    for ( $j = 0; $j < count ( $mailusers ); $j++ ) {
 255      $recip = $mailusers[$j];
 256      $user = $participants[$j];
 257      if ( ! empty ( $languages[$user] ) )
 258        $userlang = $languages[$user];
 259      else
 260        $userlang = $LANGUAGE; // system default
 261      if ( $userlang == "none" )
 262        $userlang = "English-US"; // gotta pick something
 263      if ( $debug )
 264        echo "Setting language to \"$userlang\" <br />\n";
 265      reset_language ( $userlang );
 266      // reset timezone setting for current user
 267      if ( empty ( $tzoffset[$user] ) )
 268        $GLOBALS["TZ_OFFSET"] = 0;
 269      else
 270        $GLOBALS["TZ_OFFSET"] = $tzoffset[$user];
 271  
 272      $body = translate("This is a reminder for the event detailed below.") .
 273        "\n\n";
 274  
 275      $create_by = $row[0];
 276      $name = $row[9];
 277      $description = $row[10];
 278  
 279      // add trailing '/' if not found in server_url
 280      if ( ! empty ( $server_url ) ) {
 281        if ( substr ( $server_url, -1, 1 ) == "/" ) {
 282          $body .= $server_url . "view_entry.php?id=" . $id . "\n\n";
 283        } else {
 284          $body .= $server_url . "/view_entry.php?id=" . $id . "\n\n";
 285        }
 286      }
 287  
 288      $body .= strtoupper ( $name ) . "\n\n";
 289      $body .= translate("Description") . ":\n";
 290      $body .= indent ( $description ) . "\n";
 291      $body .= translate("Date") . ": " . date_to_str ( $event_date ) . "\n";
 292      if ( $row[2] >= 0 )
 293        $body .= translate ("Time") . ": " . display_time ( $row[2] ) . "\n";
 294      if ( $row[5] > 0 )
 295        $body .= translate ("Duration") . ": " . $row[5] .
 296          " " . translate("minutes") . "\n";
 297      if ( ! empty ( $disable_priority_field ) && ! $disable_priority_field )
 298        $body .= translate("Priority") . ": " . $pri[$row[6]] . "\n";
 299      if ( ! empty ( $disable_access_field ) && ! $disable_access_field )
 300        $body .= translate("Access") . ": " .
 301          ( $row[8] == "P" ? translate("Public") : translate("Confidential") ) .
 302          "\n";
 303      if ( ! empty ( $single_user_login ) && $single_user_login == false )
 304        $body .= translate("Created by") . ": " . $row[0] . "\n";
 305      $body .= translate("Updated") . ": " . date_to_str ( $row[3] ) . " " .
 306        display_time ( $row[4] ) . "\n";
 307  
 308      // site extra fields
 309      $extras = get_site_extra_fields ( $id );
 310      for ( $i = 0; $i < count ( $site_extras ); $i++ ) {
 311        $extra_name = $site_extras[$i][0];
 312        $extra_descr = $site_extras[$i][1];
 313        $extra_type = $site_extras[$i][2];
 314        if ( $extras[$extra_name]['cal_name'] != "" ) {
 315          $body .= translate ( $extra_descr ) . ": ";
 316          if ( $extra_type == $EXTRA_DATE ) {
 317            $body .= date_to_str ( $extras[$extra_name]['cal_date'] ) . "\n";
 318          } else if ( $extra_type == $EXTRA_MULTILINETEXT ) {
 319            $body .= "\n" . indent ( $extras[$extra_name]['cal_data'] ) . "\n";
 320          } else if ( $extra_type == $EXTRA_REMINDER ) {
 321            $body .= ( $extras[$extra_name]['cal_remind'] > 0 ?
 322              translate("Yes") : translate("No") ) . "\n";
 323          } else {
 324            // default method for $EXTRA_URL, $EXTRA_TEXT, etc...
 325            $body .= $extras[$extra_name]['cal_data'] . "\n";
 326          }
 327        }
 328      }
 329      if ( ! empty ( $single_user )  && $single_user != "Y" &&
 330        ! empty ( $disable_participants_field ) &&  ! $disable_participants_field ) {
 331        $body .= translate("Participants") . ":\n";
 332        for ( $i = 0; $i < count ( $participants ); $i++ ) {
 333          $body .= "  " . $names[$participants[$i]] . "\n";
 334        }
 335        for ( $i = 0; $i < count ( $ext_participants ); $i++ ) {
 336          $body .= "  " . $ext_participants[$i] . " (" .
 337            translate("External User") . ")\n";
 338        }
 339      }
 340    
 341      $subject = translate("Reminder") . ": " . $name;
 342  
 343      if ( strlen ( $GLOBALS["email_fallback_from"] ) )
 344        $extra_hdrs = "From: " . $GLOBALS["email_fallback_from"] . "\r\n" .
 345          "X-Mailer: " . translate($application_name);
 346      else
 347        $extra_hdrs = "X-Mailer: " . translate($application_name);
 348    
 349      if ( $debug )
 350        echo "Sending mail to $recip (in $userlang)\n";
 351      if ( $only_testing ) {
 352        if ( $debug )
 353          echo "<hr /><pre>To: $recip\nSubject: $subject\n$extra_hdrs\n\n$body\n\n</pre>\n";
 354      } else {
 355        mail ( $recip, $subject, $body, $extra_hdrs );
 356        activity_log ( $id, "system", $user, $LOG_REMINDER, "" );
 357      }
 358    }
 359  }
 360  
 361  
 362  // keep track of the fact that we send the reminder, so we don't
 363  // do it again.
 364  function log_reminder ( $id, $name, $event_date ) {
 365    global $only_testing;
 366  
 367    if ( ! $only_testing ) {
 368      dbi_query ( "DELETE FROM webcal_reminder_log " .
 369        "WHERE cal_id = $id AND cal_name = '$name' " .
 370        "AND cal_event_date = $event_date" );
 371      dbi_query ( "INSERT INTO webcal_reminder_log " .
 372        "( cal_id, cal_name, cal_event_date, cal_last_sent ) VALUES ( " .
 373        "$id, '" . $name . "', $event_date, " . time() . ")" );
 374    }
 375  }
 376  
 377  
 378  // Process an event for a single day.  Check to see if it has
 379  // a reminder, when it needs to be sent and when the last time it
 380  // was sent.
 381  function process_event ( $id, $name, $event_date, $event_time ) {
 382    global $site_extras, $debug, $only_testing;
 383    global $EXTRA_REMINDER_WITH_OFFSET, $EXTRA_REMINDER_WITH_DATE;
 384  
 385    if ( $debug )
 386      printf ( "Event %d: \"%s\" at %s on %s <br />\n",
 387        $id, $name, $event_time, $event_date );
 388  
 389    // Check to see if this event has any reminders
 390    $extras = get_site_extra_fields ( $id );
 391    for ( $j = 0; $j < count ( $site_extras ); $j++ ) {
 392      $extra_name = $site_extras[$j][0];
 393      $extra_type = $site_extras[$j][2];
 394      $extra_arg1 = $site_extras[$j][3];
 395      $extra_arg2 = $site_extras[$j][4];
 396      //if ( $debug )
 397      //  printf ( "  name: %s\n  type: %d\n  arg1: %s\n  arg2: %s\n",
 398      //  $extra_name, $extra_type, $extra_arg1, $extra_arg2 );
 399      if ( ! empty ( $extras[$extra_name]['cal_remind'] ) ) {
 400        if ( $debug )
 401          echo "  Reminder set for event. <br />\n";
 402        // how many minutes before event should we send the reminder?
 403        $ev_h = (int) ( $event_time / 10000 );
 404        $ev_m = ( $event_time / 100 ) % 100;
 405        $ev_year = substr ( $event_date, 0, 4 );
 406        $ev_month = substr ( $event_date, 4, 2 );
 407        $ev_day = substr ( $event_date, 6, 2 );
 408        $event_time = mktime ( $ev_h, $ev_m, 0, $ev_month, $ev_day, $ev_year );
 409        if ( ( $extra_arg2 & $EXTRA_REMINDER_WITH_OFFSET ) > 0 ) {
 410          $minsbefore = $extras[$extra_name]['cal_data'];
 411          $remind_time = $event_time - ( $minsbefore * 60 );
 412        } else if ( ( $extra_arg2 & $EXTRA_REMINDER_WITH_DATE ) > 0 ) {
 413          $rd = $extras[$extra_name]['cal_date'];
 414          $r_year = substr ( $rd, 0, 4 );
 415          $r_month = substr ( $rd, 4, 2 );
 416          $r_day = substr ( $rd, 6, 2 );
 417          $remind_time = mktime ( 0, 0, 0, $r_month, $r_day, $r_year );
 418        } else {
 419          $minsbefore = $extra_arg1;
 420          $remind_time = $event_time - ( $minsbefore * 60 );
 421        }
 422        if ( $debug )
 423          echo "  Mins Before: $minsbefore <br />\n";
 424        if ( $debug ) {
 425          echo "  Event time is: " . date ( "m/d/Y H:i", $event_time ) . "<br />\n";
 426          echo "  Remind time is: " . date ( "m/d/Y H:i", $remind_time ) . "<br />\n";
 427        }
 428        if ( time() >= $remind_time ) {
 429          // It's remind time or later. See if one has already been sent
 430          $last_sent = 0;
 431          $res = dbi_query ( "SELECT MAX(cal_last_sent) FROM " .
 432            "webcal_reminder_log WHERE cal_id = " . $id .
 433            " AND cal_event_date = $event_date" .
 434            " AND cal_name = '" . $extra_name . "'" );
 435          if ( $res ) {
 436            if ( $row = dbi_fetch_row ( $res ) ) {
 437              $last_sent = $row[0];
 438            }
 439            dbi_free_result ( $res );
 440          }
 441          if ( $debug )
 442            echo "  Last sent on: " . date ( "m/d/Y H:i", $last_sent ) . "<br />\n";
 443          if ( $last_sent < $remind_time ) {
 444            // Send a reminder
 445            if ( $debug )
 446              echo "  SENDING REMINDER! <br />\n";
 447            send_reminder ( $id, $event_date );
 448            // now update the db...
 449            log_reminder ( $id, $extra_name, $event_date );
 450          }
 451        }
 452      }
 453    }
 454  }
 455  
 456  
 457  $startdate = time(); // today
 458  for ( $d = 0; $d < $DAYS_IN_ADVANCE; $d++ ) {
 459    $date = date ( "Ymd", time() + ( $d * 24 * 3600 ) );
 460    //echo "Date: $date\n";
 461    // Get non-repeating events for this date.
 462    // An event will be included one time for each participant.
 463    $ev = get_entries ( "", $date );
 464    // Keep track of duplicates
 465    $completed_ids = array ( );
 466    for ( $i = 0; $i < count ( $ev ); $i++ ) {
 467      $id = $ev[$i]['cal_id'];
 468      if ( ! empty ( $completed_ids[$id] ) )
 469        continue;
 470      $completed_ids[$id] = 1;
 471      process_event ( $id, $ev[$i]['cal_name'], $date, $ev[$i]['cal_time'] );
 472    }
 473    $rep = get_repeating_entries ( "", $date );
 474    for ( $i = 0; $i < count ( $rep ); $i++ ) {
 475      $id = $rep[$i]['cal_id'];
 476      if ( ! empty ( $completed_ids[$id] ) )
 477        continue;
 478      $completed_ids[$id] = 1;
 479      process_event ( $id, $rep[$i]['cal_name'], $date, $rep[$i]['cal_time'] );
 480    }
 481  }
 482  
 483  if ( $debug )
 484    echo "Done.<br />\n";
 485  
 486  ?>