adm_program/modules/messages/messages_data.php
<?php
/**
***********************************************************************************************
* @copyright The Admidio Team
* @see https://www.admidio.org/
* @license https://www.gnu.org/licenses/gpl-2.0.html GNU General Public License v2.0 only
***********************************************************************************************
* Server side script for Datatables to return the requested messages list
*
* This script will read all necessary messages and their data from the database. It is optimized to
* work with the javascript DataTables and will return the data in json format.
*
* **Code example**
* ```
* // the returned json data string
* {
* "draw":1,
* "recordsTotal":"147",
* "data": [ [ 1,
* "Link to profile",
* "Smith, Heinz",
* "Admin",
* "Gender",
* "16.06.1991",
* "14.02.2009 15:24",
* "Functions"],
* [ ... ],
* ],
* "recordsFiltered":"147"
* }
* ```
*
* Parameters:
*
* draw - Number to validate the right inquiry from DataTables.
* start - Paging first record indicator. This is the start point in the current data set
* (0 index based - i.e. 0 is the first record).
* length - Number of records that the table can display in the current draw. It is expected that
* the number of records returned will be equal to this number, unless the server has
* fewer records to return. Note that this can be -1 to indicate that all records should
* be returned (although that negates any benefits of server-side processing!)
* search[value] - Global search value.
*****************************************************************************/
use Admidio\Exception;
require_once(__DIR__ . '/../../system/common.php');
require(__DIR__ . '/../../system/login_valid.php');
try {
// Initialize and check the parameters
$getDraw = admFuncVariableIsValid($_GET, 'draw', 'int', array('requireValue' => true));
$getStart = admFuncVariableIsValid($_GET, 'start', 'int', array('requireValue' => true));
$getLength = admFuncVariableIsValid($_GET, 'length', 'int', array('requireValue' => true));
$getSearch = admFuncVariableIsValid($_GET['search'], 'value', 'string');
$jsonArray = array('draw' => $getDraw);
header('Content-Type: application/json');
// create order statement
$orderCondition = '';
$orderColumns = array('msg_type', 'msg_subject', 'recipients', 'attachments', 'msg_timestamp');
if (array_key_exists('order', $_GET)) {
foreach ($_GET['order'] as $order) {
if (is_numeric($order['column'])) {
if ($orderCondition === '') {
$orderCondition = ' ORDER BY ';
} else {
$orderCondition .= ', ';
}
if (strtoupper($order['dir']) === 'ASC') {
$orderCondition .= $orderColumns[$order['column']] . ' ASC ';
} else {
$orderCondition .= $orderColumns[$order['column']] . ' DESC ';
}
}
}
} else {
$orderCondition = ' ORDER BY msg_timestamp DESC ';
}
// create search conditions
$searchCondition = '';
$queryParamsSearch = array();
$searchColumns = array(
'COALESCE(msg_subject, \' \')'
);
if ($getSearch !== '' && count($searchColumns) > 0) {
$searchString = explode(' ', $getSearch);
if (DB_ENGINE === Database::PDO_ENGINE_PGSQL) {
$searchValue = ' ?::text ';
} else {
// mysql
$searchValue = ' ? ';
}
foreach ($searchString as $searchWord) {
$searchCondition .= ' AND concat(' . implode(', ', $searchColumns) . ') LIKE CONCAT(\'%\', ' . $searchValue . ', \'%\') ';
$queryParamsSearch[] = htmlspecialchars_decode($searchWord, ENT_QUOTES | ENT_HTML5);
}
$searchCondition = ' WHERE ' . substr($searchCondition, 4);
}
// get count of all found users
$sql = 'SELECT COUNT(*)
FROM ' . TBL_MESSAGES . '
WHERE ( msg_usr_id_sender = ? -- $gCurrentUserId
OR EXISTS (
SELECT 1
FROM ' . TBL_MESSAGES_RECIPIENTS . '
WHERE msr_msg_id = msg_id
AND msg_type = \'PM\'
AND msr_usr_id = ? -- $gCurrentUserId
)
)';
$queryParams = array(
$gCurrentUserId,
$gCurrentUserId
);
$countTotalStatement = $gDb->queryPrepared($sql, $queryParams); // TODO add more params
$jsonArray['recordsTotal'] = (int)$countTotalStatement->fetchColumn();
// SQL-Statement zusammensetzen
$mainSql = 'SELECT msg_id, msg_uuid, msg_type, msg_subject, msg_usr_id_sender, msg_timestamp, msg_read,
(SELECT count(1) FROM ' . TBL_MESSAGES_ATTACHMENTS . ' WHERE msa_msg_id = msg_id) AS attachments
FROM ' . TBL_MESSAGES . '
WHERE ( msg_usr_id_sender = ? -- $gCurrentUserId
OR EXISTS (
SELECT 1
FROM ' . TBL_MESSAGES_RECIPIENTS . '
WHERE msr_msg_id = msg_id
AND msg_type = \'PM\'
AND msr_usr_id = ? -- $gCurrentUserId
)
)';
$queryParamsMain = array(
$gCurrentUserId,
$gCurrentUserId
);
$limitCondition = '';
if ($getLength > 0) {
$limitCondition = ' LIMIT ' . $getLength . ' OFFSET ' . $getStart;
}
if ($getSearch === '') {
// no search condition entered then return all records in dependence of order, limit and offset
$sql = $mainSql . $orderCondition . $limitCondition;
} else {
$sql = 'SELECT msg_id, msg_uuid, msg_type, msg_subject, attachments, msg_timestamp
FROM (' . $mainSql . ') AS members
' . $searchCondition
. $orderCondition
. $limitCondition;
}
$messageStatement = $gDb->queryPrepared($sql, array_merge($queryParamsMain, $queryParamsSearch)); // TODO add more params
$rowNumber = $getStart; // count for every row
// show rows with all organization users
while ($message = $messageStatement->fetch()) {
++$rowNumber;
$arrContent = array();
$cssClass = 'fw-normal';
$iconAttachments = '';
$messageObject = new TableMessage($gDb);
$messageObject->setArray($message);
// Icon fuer Orgamitglied und Nichtmitglied auswaehlen
if ($message['msg_type'] === TableMessage::MESSAGE_TYPE_EMAIL) {
$icon = 'bi-envelope-fill';
$iconText = $gL10n->get('SYS_EMAIL');
$links = '<a class="admidio-icon-link" href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/messages/messages_write.php', array('msg_uuid' => $message['msg_uuid'], 'forward' => '1')) . '">
<i class="bi bi-forward" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_FORWARD') . '"></i></a>';
} else {
$icon = 'bi-chat-left-fill';
$iconText = $gL10n->get('SYS_PRIVATE_MESSAGES');
$links = '';
}
if ($messageObject->isUnread()) {
$cssClass = 'fw-bold';
}
if ((int)$message['attachments'] === 1) {
$iconAttachments = '<i class="bi bi-paperclip" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_ATTACHMENT_ONE') . '"></i>';
} elseif ($message['attachments'] > 1) {
$iconAttachments = '<i class="bi bi-paperclip" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_ATTACHMENTS_VAR', array($message['attachments'])) . '"></i>';
}
$arrContent['DT_RowId'] = 'row_message_' . $message['msg_uuid'];
$arrContent['DT_RowClass'] = $cssClass;
$arrContent['0'] = '<i class="bi ' . $icon . '" data-bs-toggle="tooltip" title="' . $iconText . '"></i>';
$arrContent['1'] = '<a href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/messages/messages_write.php', array('msg_uuid' => $message['msg_uuid'])) . '">' . $messageObject->getValue('msg_subject') . '</a>';
$arrContent['2'] = $messageObject->getRecipientsNamesString();
$arrContent['3'] = $iconAttachments;
$arrContent['4'] = $messageObject->getValue('msg_timestamp');
$arrContent['5'] = $links . '
<a class="admidio-icon-link openPopup" href="javascript:void(0);"
data-href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . '/adm_program/system/popup_message.php', array('type' => 'msg', 'element_id' => 'row_message_' . $message['msg_uuid'], 'name' => $messageObject->getValue('msg_subject', 'database'), 'database_id' => $message['msg_uuid'])) . '">
<i class="bi bi-trash" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_REMOVE_MESSAGE') . '"></i>
</a>';
// create array with all column values and add it to the json array
$jsonArray['data'][] = $arrContent;
}
// set count of filtered records
if ($getSearch !== '') {
if ($rowNumber < $getStart + $getLength) {
$jsonArray['recordsFiltered'] = $rowNumber;
} else {
// read count of all filtered records without limit and offset
$sql = 'SELECT COUNT(*) AS count
FROM (' . $mainSql . ') AS members
' . $searchCondition;
$countFilteredStatement = $gDb->queryPrepared($sql, array_merge($queryParamsMain, $queryParamsSearch));
$jsonArray['recordsFiltered'] = (int)$countFilteredStatement->fetchColumn();
}
} else {
$jsonArray['recordsFiltered'] = $jsonArray['recordsTotal'];
}
// add empty data element if no rows where found
if (!array_key_exists('data', $jsonArray)) {
$jsonArray['data'] = array();
}
echo json_encode($jsonArray);
} catch (Exception $e) {
$jsonArray['error'] = $e->getMessage();
echo json_encode($jsonArray);
exit();
}