Location: PHPKode > projects > ETraxis > etraxis-2.1.1/src/dbo/reminders.php
<?php

/**
 * Reminders
 *
 * This module provides API to work with user reminders.
 * See also {@link http://www.etraxis.org/docs-schema.php#tbl_reminders tbl_reminders} database table.
 *
 * @package DBO
 * @subpackage Reminders
 */

//--------------------------------------------------------------------------------------------------
//
//  eTraxis - Records tracking web-based system.
//  Copyright (C) 2006-2009 by Artem Rodygin
//
//  This program is free software; you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation; either version 2 of the License, or
//  (at your option) any later version.
//
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
//
//  You should have received a copy of the GNU General Public License along
//  with this program; if not, write to the Free Software Foundation, Inc.,
//  51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
//
//--------------------------------------------------------------------------------------------------
//  Author                  Date            Description of modifications
//--------------------------------------------------------------------------------------------------
//  Artem Rodygin           2006-06-25      new-222: Email reminders.
//  Artem Rodygin           2006-06-28      new-271: Maximum execution time should be temporary unlimited during sending mail operations.
//  Artem Rodygin           2006-06-28      bug-273: 'Reminders' button should be disabled if no reminder can be created or send.
//  Artem Rodygin           2006-06-28      bug-275: Empty reminders should not be sent.
//  Artem Rodygin           2006-07-09      bug-289: PHP Warning: odbc_exec(): SQL error: Incorrect syntax near '2'.
//  Artem Rodygin           2006-10-08      bug-336: /src/dbo/reminders.php: Unused function argument: $link.
//  Artem Rodygin           2007-04-03      new-512: Banner about 'no reply on autogenerated message' for notifications.
//  Artem Rodygin           2007-11-27      new-633: The 'dbx' extension should not be used.
//  Denis Makovkin          2008-02-15      bug-674: [SF1893539] Incorrect charset in "Subject" email notifications
//  Artem Rodygin           2008-02-28      new-294: PostgreSQL support.
//  Artem Rodygin           2009-06-12      new-824: PHP 4 is discontinued.
//  Artem Rodygin           2009-06-17      bug-825: Database gets empty strings instead of NULL values.
//  Artem Rodygin           2008-06-21      new-723: Wrap calls of 'mail' function.
//  Artem Rodygin           2008-06-30      bug-727: Notifications are not sent via Lotus Domino SMTP server.
//  Artem Rodygin           2009-09-09      new-826: Native unicode support for Microsoft SQL Server.
//--------------------------------------------------------------------------------------------------

/**#@+
 * Dependency.
 */
require_once('../engine/engine.php');
require_once('../dbo/accounts.php');
require_once('../dbo/records.php');
/**#@-*/

//--------------------------------------------------------------------------------------------------
//  Definitions.
//--------------------------------------------------------------------------------------------------

/**#@+
 * Data restrictions.
 */
define('MAX_REMINDER_NAME',    25);
define('MAX_REMINDER_SUBJECT', 100);
/**#@-*/

/**#@+
 * Reminder group flags.
 */
define('REMINDER_FLAG_GROUP',        0);
define('REMINDER_FLAG_AUTHOR',      -1);
define('REMINDER_FLAG_RESPONSIBLE', -2);
/**#@-*/

//--------------------------------------------------------------------------------------------------
//  Functions.
//--------------------------------------------------------------------------------------------------

/**
 * Finds in database and returns the information about specified reminder.
 *
 * @param int $id {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_id Reminder ID}.
 * @return array Array with data if reminder is found in database, FALSE otherwise.
 */
function reminder_find ($id)
{
    debug_write_log(DEBUG_TRACE, '[reminder_find]');
    debug_write_log(DEBUG_DUMP,  '[reminder_find] $id = ' . $id);

    if (DATABASE_DRIVER == DRIVER_ORACLE9)
    {
        $rs = dal_query('reminders/oracle/fndid.sql', $_SESSION[VAR_USERID], $id);
    }
    else
    {
        $rs = dal_query('reminders/fndid.sql', $_SESSION[VAR_USERID], $id);
    }

    return ($rs->rows == 0 ? FALSE : $rs->fetch());
}

/**
 * Returns {@link CRecordset DAL recordset} which contains all existing reminders of specified account.
 *
 * @param int $id {@link http://www.etraxis.org/docs-schema.php#tbl_accounts_account_id Account ID}.
 * @return CRecordset Recordset with list of reminders.
 */
function reminders_list ($id)
{
    debug_write_log(DEBUG_TRACE, '[reminders_list]');
    debug_write_log(DEBUG_DUMP,  '[reminders_list] $id = ' . $id);

    if (DATABASE_DRIVER == DRIVER_ORACLE9)
    {
        $rs = dal_query('reminders/oracle/list.sql', $id);
    }
    else
    {
        $rs = dal_query('reminders/list.sql', $id);
    }

    return $rs;
}

/**
 * Validates reminder information before creation or modification.
 *
 * @param string $reminder_name {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_name Reminder name}.
 * @param string $subject_text {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_subject_text Subject of reminder}.
 * @return int Error code:
 * <ul>
 * <li>{@link NO_ERROR} - data are valid</li>
 * <li>{@link ERROR_INCOMPLETE_FORM} - at least one of required field is empty</li>
 * </ul>
 */
function reminder_validate ($reminder_name, $subject_text)
{
    debug_write_log(DEBUG_TRACE, '[reminder_validate]');
    debug_write_log(DEBUG_DUMP,  '[reminder_validate] $reminder_name = ' . $reminder_name);
    debug_write_log(DEBUG_DUMP,  '[reminder_validate] $subject_text  = ' . $subject_text);

    if (ustrlen($reminder_name) == 0 ||
        ustrlen($subject_text)  == 0)
    {
        debug_write_log(DEBUG_NOTICE, '[reminder_validate] At least one required field is empty.');
        return ERROR_INCOMPLETE_FORM;
    }

    return NO_ERROR;
}

/**
 * Creates new reminder.
 *
 * @param string $reminder_name {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_name Reminder name}.
 * @param string $subject_text {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_subject_text Subject of reminder}.
 * @param string $state_id {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_state_id State ID}.
 * @param string $group_id {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_group_id Group ID}.
 * @param string $group_flag {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_group_flag Group flag}.
 * @return int Error code:
 * <ul>
 * <li>{@link NO_ERROR} - reminder is successfully created</li>
 * <li>{@link ERROR_ALREADY_EXISTS} - reminder with specified {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_name name} already exists</li>
 * </ul>
 */
function reminder_create ($reminder_name, $subject_text, $state_id, $group_id, $group_flag)
{
    debug_write_log(DEBUG_TRACE, '[reminder_create]');
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $reminder_name = ' . $reminder_name);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $subject_text  = ' . $subject_text);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $state_id      = ' . $state_id);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $group_id      = ' . $group_id);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $group_flag    = ' . $group_flag);

    // Check that user doesn't have another reminder with the same name.
    $rs = dal_query('reminders/fndk.sql', $_SESSION[VAR_USERID], ustrtolower($reminder_name));

    if ($rs->rows != 0)
    {
        debug_write_log(DEBUG_NOTICE, '[reminder_create] Reminder already exists.');
        return ERROR_ALREADY_EXISTS;
    }

    // Create a reminder.
    dal_query('reminders/create.sql',
              $_SESSION[VAR_USERID],
              $reminder_name,
              ustrlen($subject_text) == 0 ? NULL : $subject_text,
              $state_id,
              is_null($group_id) ? NULL : $group_id,
              $group_flag);

    return NO_ERROR;
}

/**
 * Modifies specified reminder.
 *
 * @param int $id {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_id ID} of reminder to be modified.
 * @param string $reminder_name New {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_name Reminder name}.
 * @param string $subject_text New {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_subject_text Subject of reminder}.
 * @param string $state_id New {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_state_id State ID}.
 * @param string $group_id New {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_group_id Group ID}.
 * @param string $group_flag New {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_group_flag Group flag}.
 * @return int Error code:
 * <ul>
 * <li>{@link NO_ERROR} - reminder is successfully modified</li>
 * <li>{@link ERROR_ALREADY_EXISTS} - reminder with specified {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_name name} already exists</li>
 * </ul>
 */
function reminder_modify ($id, $reminder_name, $subject_text, $state_id, $group_id, $group_flag)
{
    debug_write_log(DEBUG_TRACE, '[reminder_modify]');
    debug_write_log(DEBUG_DUMP,  '[reminder_modify] $id            = ' . $id);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $reminder_name = ' . $reminder_name);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $subject_text  = ' . $subject_text);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $state_id      = ' . $state_id);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $group_id      = ' . $group_id);
    debug_write_log(DEBUG_DUMP,  '[reminder_create] $group_flag    = ' . $group_flag);

    // Check that user doesn't have another reminder with the same name, besides this one.
    $rs = dal_query('reminders/fndku.sql', $id, $_SESSION[VAR_USERID], ustrtolower($reminder_name));

    if ($rs->rows != 0)
    {
        debug_write_log(DEBUG_NOTICE, '[reminder_modify] Reminder already exists.');
        return ERROR_ALREADY_EXISTS;
    }

    // Modify the reminder.
    dal_query('reminders/modify.sql',
              $id,
              $reminder_name,
              ustrlen($subject_text) == 0 ? NULL : $subject_text,
              $state_id,
              is_null($group_id) ? NULL : $group_id,
              $group_flag);

    return NO_ERROR;
}

/**
 * Deletes specified reminder.
 *
 * @param int $id {@link http://www.etraxis.org/docs-schema.php#tbl_reminders_reminder_id ID} of reminder to be deleted.
 * @return int Always {@link NO_ERROR}.
 */
function reminder_delete ($id)
{
    debug_write_log(DEBUG_TRACE, '[reminder_delete]');
    debug_write_log(DEBUG_DUMP,  '[reminder_delete] $id = ' . $id);

    dal_query('reminders/delete.sql', $id);

    return NO_ERROR;
}

/**
 * Checks whether reminder can be created.
 *
 * @return bool TRUE if reminder can be created, FALSE otherwise.
 */
function can_reminder_be_created ()
{
    debug_write_log(DEBUG_TRACE, '[can_reminder_be_created]');

    if (DATABASE_DRIVER == DRIVER_ORACLE9)
    {
        $rs = dal_query('reminders/oracle/plist.sql', $_SESSION[VAR_USERID]);
    }
    else
    {
        $rs = dal_query('reminders/plist.sql', $_SESSION[VAR_USERID]);
    }

    return ($rs->rows != 0);
}

/**
 * Generates and returns message body for reminder about specified records.
 *
 * @param array $records Array of records data.
 * @param int $locale ID of language. If omitted, then language of current user, or (when user is not logged in) default language will be used (see {@link LANG_DEFAULT}).
 * @return string Generated message body.
 */
function reminder_message ($records, $locale = NULL)
{
    debug_write_log(DEBUG_TRACE, '[reminder_message]');
    debug_write_log(DEBUG_DUMP,  '[reminder_message] $locale = ' . $locale);

    $message =
        '<html>' .
        '<body>' .
        '<b><font color="red">' . get_html_resource(RES_ALERT_DO_NOT_REPLY_ID) . '</font></b><br/>' .
        '<table border="1" cellspacing="0" cellpadding="5">' .
        '<tr valign="top">' .
        '<td nowrap><b>' . get_html_resource(RES_ID_ID,      $locale) . '</b></td>' .
        '<td nowrap><b>' . get_html_resource(RES_STATE_ID,   $locale) . '</b></td>' .
        '<td nowrap><b>' . get_html_resource(RES_PROJECT_ID, $locale) . '</b></td>' .
        '<td><b>'        . get_html_resource(RES_SUBJECT_ID, $locale) . '</b></td>' .
        '<td nowrap><b>' . get_html_resource(RES_AUTHOR_ID,  $locale) . '</b></td>' .
        '</tr>';

    while (($row = $records->fetch()))
    {
        $message .=
            '<tr valign="top">' .
            '<td align="left" nowrap><a href="' . WEBROOT . 'records/view.php?id=' . $row['record_id'] . '">' . record_id($row['record_id'], $row['template_prefix']) . '</a></td>' .
            '<td align="center" nowrap>' . ustr2html($row['state_abbr'])      . '</td>' .
            '<td align="left" nowrap>'   . ustr2html($row['project_name'])    . '</td>' .
            '<td align="left">'          . ustr2html($row['subject'])         . '</td>' .
            '<td align="left" nowrap>'   . ustr2html($row['fullname']) . '</td>' .
            '</tr>';
    }

    $message .=
        '</table>' .
        '</body>' .
        '</html>';

    return $message;
}

/**
 * Sends specified reminder to all interested parties.
 *
 * @param array $reminder Array with data of reminder (e.g. how it's returned by {@link reminder_find}).
 * @return int Always {@link NO_ERROR}.
 */
function reminder_send ($reminder)
{
    debug_write_log(DEBUG_TRACE, '[reminder_send]');
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["project_id"]    = ' . $reminder['project_id']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["project_name"]  = ' . $reminder['project_name']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["template_id"]   = ' . $reminder['template_id']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["template_name"] = ' . $reminder['template_name']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["reminder_name"] = ' . $reminder['reminder_name']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["subject_text"]  = ' . $reminder['subject_text']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["state_id"]      = ' . $reminder['state_id']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["group_id"]      = ' . $reminder['group_id']);
    debug_write_log(DEBUG_DUMP,  '[reminder_send] $reminder["group_flag"]    = ' . $reminder['group_flag']);

    global $locale_info;

    $account = account_find($_SESSION[VAR_USERID]);

    // Since sending email can takes a time, disable PHP execution timeout.
    set_time_limit(0);

    switch ($reminder['group_flag'])
    {
        // Reminder is dedicated to specified group.
        case REMINDER_FLAG_GROUP:

            $records = dal_query('reminders/rlist.sql', $reminder['state_id']);

            if ($records->rows == 0)
            {
                debug_write_log(DEBUG_NOTICE, '[reminder_send] Reminder is empty and will not be sent.');
            }
            else
            {
                $supported_locales = array_keys($locale_info);

                foreach ($supported_locales as $locale)
                {
                    $to = array();
                    $rs = dal_query('reminders/members.sql', $reminder['group_id'], $locale);

                    while (($row = $rs->fetch()))
                    {
                        array_push($to, $row['email']);
                    }

                    if (count($to) != 0)
                    {
                        $recipients = implode(', ', array_unique($to));
                        $message    = reminder_message($records, $locale);

                        if (EMAIL_NOTIFICATIONS_ENABLED)
                        {
                            debug_write_log(DEBUG_NOTICE, '[reminder_send] Sending email.');
                            sendmail($account['fullname'], $account['email'], $recipients, $reminder['subject_text'], $message);
                        }
                        else
                        {
                            debug_write_log(DEBUG_NOTICE, '[reminder_send] Email notifications are disabled.');
                        }
                    }
                }
            }

            break;

        // Reminder is dedicated to records submitters.
        case REMINDER_FLAG_AUTHOR:

            $rs = dal_query('reminders/alista.sql', $reminder['state_id']);

            while (($row = $rs->fetch()))
            {
                $records = dal_query('reminders/rlista.sql', $reminder['state_id'], $row['account_id']);

                if ($records->rows == 0)
                {
                    debug_write_log(DEBUG_NOTICE, '[reminder_send] Reminder is empty and will not be sent.');
                }
                else
                {
                    $message = reminder_message($records, $row['locale']);

                    if (EMAIL_NOTIFICATIONS_ENABLED)
                    {
                        debug_write_log(DEBUG_NOTICE, '[reminder_send] Sending email.');
                        sendmail($account['fullname'], $account['email'], $row['email'], $reminder['subject_text'], $message);
                    }
                    else
                    {
                        debug_write_log(DEBUG_NOTICE, '[reminder_send] Email notifications are disabled.');
                    }
                }
            }

            break;

        // Reminder is dedicated to current records assignees.
        case REMINDER_FLAG_RESPONSIBLE:

            $rs = dal_query('reminders/alistr.sql', $reminder['state_id']);

            while (($row = $rs->fetch()))
            {
                $records = dal_query('reminders/rlistr.sql', $reminder['state_id'], $row['account_id']);

                if ($records->rows == 0)
                {
                    debug_write_log(DEBUG_NOTICE, '[reminder_send] Reminder is empty and will not be sent.');
                }
                else
                {
                    $message = reminder_message($records, $row['locale']);

                    if (EMAIL_NOTIFICATIONS_ENABLED)
                    {
                        debug_write_log(DEBUG_NOTICE, '[reminder_send] Sending email.');
                        sendmail($account['fullname'], $account['email'], $row['email'], $reminder['subject_text'], $message);
                    }
                    else
                    {
                        debug_write_log(DEBUG_NOTICE, '[reminder_send] Email notifications are disabled.');
                    }
                }
            }

            break;

        default:

            debug_write_log(DEBUG_WARNING, '[reminder_send] Unknown reminder group flags = ' . $reminder['group_flag']);
    }

    // Restore PHP execution timeout, disabled above.
    ini_restore('max_execution_time');

    return NO_ERROR;
}

?>
Return current item: ETraxis