adm_plugins/birthday/birthday.php
<?php
use Admidio\Plugins\Overview;
/**
***********************************************************************************************
* Birthday
*
* The plugin lists all users who have birthday on the current day. In the configuration
* file you can set that also past or future birthdays are displayed.
*
* @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
***********************************************************************************************
*/
try {
$rootPath = dirname(__DIR__, 2);
$pluginFolder = basename(__DIR__);
require_once($rootPath . '/adm_program/system/common.php');
// only include config file if it exists
if (is_file(__DIR__ . '/config.php')) {
require_once(__DIR__ . '/config.php');
}
$birthdayPlugin = new Overview($pluginFolder);
// global variable to show names of the members who have birthday
$plgBirthdayShowNames = false;
// set default values if there is no value has been stored in the config.php
if (!isset($plg_show_names_extern) || !is_numeric($plg_show_names_extern) || $plg_show_names_extern !== 1) {
$plg_show_names_extern = 0;
}
if (!isset($plg_show_email_extern) || !is_numeric($plg_show_email_extern)) {
$plg_show_email_extern = 0;
}
if (!isset($plg_show_names) || !is_numeric($plg_show_names)) {
$plg_show_names = 1;
}
if (!isset($plg_show_age) || !is_numeric($plg_show_age)) {
$plg_show_age = 0;
}
if (!isset($plg_show_hinweis_keiner) || !is_numeric($plg_show_hinweis_keiner)) {
$plg_show_hinweis_keiner = 0;
}
if (!isset($plg_show_alter_anrede) || !is_numeric($plg_show_alter_anrede)) {
$plg_show_alter_anrede = 18;
}
if (!isset($plg_show_zeitraum) || !is_numeric($plg_show_zeitraum)) {
$plg_show_zeitraum = 1;
}
if (!isset($plg_show_future) || !is_numeric($plg_show_future)) {
$plg_show_future = 2;
}
if (!isset($plg_show_display_limit) || !is_numeric($plg_show_display_limit)) {
$plg_show_display_limit = 200;
}
// check if only members of configured roles could view birthday
if ($gValidLogin) {
if (isset($plg_birthday_roles_view_plugin) && count($plg_birthday_roles_view_plugin) > 0) {
// current user must be member of at least one listed role
if (count(array_intersect($plg_birthday_roles_view_plugin, $gCurrentUser->getRoleMemberships())) > 0) {
$plgBirthdayShowNames = true;
}
} else {
// every member could view birthdays
$plgBirthdayShowNames = true;
}
} else {
if ($plg_show_names_extern === 1) {
// every visitor is allowed to view birthdays
$plgBirthdayShowNames = true;
}
}
// Check if the role condition has been set
if (isset($plg_rolle_sql) && is_array($plg_rolle_sql) && count($plg_rolle_sql) > 0) {
$sqlRol = 'IN (' . implode(',', $plg_rolle_sql) . ')';
} else {
$sqlRol = 'IS NOT NULL';
}
// Check if the sort condition has been set
if (!isset($plg_sort_sql) || $plg_sort_sql === '') {
$sqlSort = 'DESC';
} else {
$sqlSort = $plg_sort_sql;
}
// if no birthdays should be shown than disable future and former birthday periods
if (!$plgBirthdayShowNames) {
$plg_show_zeitraum = 0;
$plg_show_future = 0;
}
$fieldBirthday = $gProfileFields->getProperty('BIRTHDAY', 'usf_id');
if ($gDbType === 'pgsql') {
$sql = 'SELECT DISTINCT usr_id, usr_uuid, usr_login_name,
last_name.usd_value AS last_name, first_name.usd_value AS first_name,
birthday.bday AS birthday, birthday.bdate,
EXTRACT(DAY FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\') - birthday.bdate) * (-1) AS days_to_bdate, -- DATE_NOW
EXTRACT(YEAR FROM bdate) - EXTRACT(YEAR FROM TO_TIMESTAMP(bday, \'YYYY-MM-DD\')) AS age,
email.usd_value AS email, gender.usd_value AS gender
FROM ' . TBL_USERS . ' AS users
INNER JOIN ( (SELECT usd_usr_id, usd_value AS bday,
TO_DATE(EXTRACT(YEAR FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\')) || TO_CHAR(TO_TIMESTAMP(bd1.usd_value, \'YYYY-MM-DD\'), \'-MM-DD\'), \'YYYY-MM-DD\') AS bdate -- DATE_NOW
FROM ' . TBL_USER_DATA . ' AS bd1
WHERE EXTRACT(DAY FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\') - TO_TIMESTAMP(EXTRACT(YEAR FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\')) || TO_CHAR(TO_TIMESTAMP(bd1.usd_value, \'YYYY-MM-DD\'), \'-MM-DD\'), \'YYYY-MM-DD\')) -- DATE_NOW,DATE_NOW
BETWEEN ? AND ? -- -$plg_show_zeitraum AND $plg_show_future
AND usd_usf_id = ?) -- $fieldBirthday
UNION
(SELECT usd_usr_id, usd_value AS bday,
TO_DATE(EXTRACT(YEAR FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\'))-1 || TO_CHAR(TO_TIMESTAMP(bd2.usd_value, \'YYYY-MM-DD\'), \'-MM-DD\'), \'YYYY-MM-DD\') AS bdate -- DATE_NOW
FROM ' . TBL_USER_DATA . ' AS bd2
WHERE EXTRACT(DAY FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\') - TO_TIMESTAMP(EXTRACT(YEAR FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\')- INTERVAL \'1 year\') || TO_CHAR(TO_TIMESTAMP(bd2.usd_value, \'YYYY-MM-DD\'), \'-MM-DD\'), \'YYYY-MM-DD\')) -- DATE_NOW,DATE_NOW
BETWEEN ? AND ? -- -$plg_show_zeitraum AND $plg_show_future
AND usd_usf_id = ?) -- $fieldBirthday
UNION
(SELECT usd_usr_id, usd_value AS bday,
TO_DATE(EXTRACT(YEAR FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\'))+1 || TO_CHAR(TO_TIMESTAMP(bd3.usd_value, \'YYYY-MM-DD\'), \'-MM-DD\'), \'YYYY-MM-DD\') AS bdate -- DATE_NOW
FROM ' . TBL_USER_DATA . ' AS bd3
WHERE EXTRACT(DAY FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\') - TO_TIMESTAMP(EXTRACT(YEAR FROM TO_TIMESTAMP(?, \'YYYY-MM-DD\')+ INTERVAL \'1 year\') || TO_CHAR(TO_TIMESTAMP(bd3.usd_value, \'YYYY-MM-DD\'), \'-MM-DD\'), \'YYYY-MM-DD\')) -- DATE_NOW,DATE_NOW
BETWEEN ? AND ? -- -$plg_show_zeitraum AND $plg_show_future
AND usd_usf_id = ?) -- $fieldBirthday
) AS birthday
ON birthday.usd_usr_id = usr_id
LEFT JOIN ' . TBL_USER_DATA . ' AS last_name
ON last_name.usd_usr_id = usr_id
AND last_name.usd_usf_id = ? -- $gProfileFields->getProperty(\'LAST_NAME\', \'usf_id\')
LEFT JOIN ' . TBL_USER_DATA . ' AS first_name
ON first_name.usd_usr_id = usr_id
AND first_name.usd_usf_id = ? -- $gProfileFields->getProperty(\'FIRST_NAME\', \'usf_id\')
LEFT JOIN ' . TBL_USER_DATA . ' AS email
ON email.usd_usr_id = usr_id
AND email.usd_usf_id = ? -- $gProfileFields->getProperty(\'EMAIL\', \'usf_id\')
LEFT JOIN ' . TBL_USER_DATA . ' AS gender
ON gender.usd_usr_id = usr_id
AND gender.usd_usf_id = ? -- $gProfileFields->getProperty(\'GENDER\', \'usf_id\')
LEFT JOIN ' . TBL_MEMBERS . '
ON mem_usr_id = usr_id
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW
INNER JOIN ' . TBL_ROLES . '
ON mem_rol_id = rol_id
AND rol_valid = true
INNER JOIN ' . TBL_CATEGORIES . '
ON rol_cat_id = cat_id
AND cat_org_id = ? -- $gCurrentOrgId
WHERE usr_valid = true
AND mem_rol_id ' . $sqlRol . '
ORDER BY days_to_bdate ' . $sqlSort . ', last_name, first_name';
} else {
$sql = 'SELECT DISTINCT usr_id, usr_uuid, usr_login_name,
last_name.usd_value AS last_name, first_name.usd_value AS first_name,
birthday.bday AS birthday, birthday.bdate,
DATEDIFF(birthday.bdate, ?) AS days_to_bdate, -- DATE_NOW
YEAR(bdate) - YEAR(bday) AS age,
email.usd_value AS email, gender.usd_value AS gender
FROM ' . TBL_USERS . ' AS users
INNER JOIN ( (SELECT usd_usr_id, usd_value AS bday,
CONCAT(YEAR(?), DATE_FORMAT(bd1.usd_value, \'-%m-%d\')) AS bdate -- DATE_NOW
FROM ' . TBL_USER_DATA . ' AS bd1
WHERE DATEDIFF(CONCAT(YEAR(?), DATE_FORMAT(bd1.usd_value, \'-%m-%d\')), ?) -- DATE_NOW,DATE_NOW
BETWEEN ? AND ? -- -$plg_show_zeitraum AND $plg_show_future
AND usd_usf_id = ?) -- $fieldBirthday
UNION
(SELECT usd_usr_id, usd_value AS bday,
CONCAT(YEAR(?)-1, DATE_FORMAT(bd2.usd_value, \'-%m-%d\')) AS bdate -- DATE_NOW
FROM ' . TBL_USER_DATA . ' AS bd2
WHERE DATEDIFF(CONCAT(YEAR(?)-1, DATE_FORMAT(bd2.usd_value, \'-%m-%d\')), ?) -- DATE_NOW,DATE_NOW
BETWEEN ? AND ? -- -$plg_show_zeitraum AND $plg_show_future
AND usd_usf_id = ?) -- $fieldBirthday
UNION
(SELECT usd_usr_id, usd_value AS bday,
CONCAT(YEAR(?)+1, DATE_FORMAT(bd3.usd_value, \'-%m-%d\')) AS bdate -- DATE_NOW
FROM ' . TBL_USER_DATA . ' AS bd3
WHERE DATEDIFF(CONCAT(YEAR(?)+1, DATE_FORMAT(bd3.usd_value, \'-%m-%d\')), ?) -- DATE_NOW,DATE_NOW
BETWEEN ? AND ? -- -$plg_show_zeitraum AND $plg_show_future
AND usd_usf_id = ?) -- $fieldBirthday
) AS birthday
ON birthday.usd_usr_id = usr_id
LEFT JOIN ' . TBL_USER_DATA . ' AS last_name
ON last_name.usd_usr_id = usr_id
AND last_name.usd_usf_id = ? -- $gProfileFields->getProperty(\'LAST_NAME\', \'usf_id\')
LEFT JOIN ' . TBL_USER_DATA . ' AS first_name
ON first_name.usd_usr_id = usr_id
AND first_name.usd_usf_id = ? -- $gProfileFields->getProperty(\'FIRST_NAME\', \'usf_id\')
LEFT JOIN ' . TBL_USER_DATA . ' AS email
ON email.usd_usr_id = usr_id
AND email.usd_usf_id = ? -- $gProfileFields->getProperty(\'EMAIL\', \'usf_id\')
LEFT JOIN ' . TBL_USER_DATA . ' AS gender
ON gender.usd_usr_id = usr_id
AND gender.usd_usf_id = ? -- $gProfileFields->getProperty(\'GENDER\', \'usf_id\')
LEFT JOIN ' . TBL_MEMBERS . '
ON mem_usr_id = usr_id
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW
INNER JOIN ' . TBL_ROLES . '
ON mem_rol_id = rol_id
AND rol_valid = true
INNER JOIN ' . TBL_CATEGORIES . '
ON rol_cat_id = cat_id
AND cat_org_id = ? -- $gCurrentOrgId
WHERE usr_valid = true
AND mem_rol_id ' . $sqlRol . '
ORDER BY days_to_bdate ' . $sqlSort . ', last_name, first_name';
}
$queryParams = array(
DATE_NOW,
DATE_NOW, DATE_NOW, DATE_NOW, -$plg_show_zeitraum, $plg_show_future, $fieldBirthday,
DATE_NOW, DATE_NOW, DATE_NOW, -$plg_show_zeitraum, $plg_show_future, $fieldBirthday,
DATE_NOW, DATE_NOW, DATE_NOW, -$plg_show_zeitraum, $plg_show_future, $fieldBirthday,
$gProfileFields->getProperty('LAST_NAME', 'usf_id'),
$gProfileFields->getProperty('FIRST_NAME', 'usf_id'),
$gProfileFields->getProperty('EMAIL', 'usf_id'),
$gProfileFields->getProperty('GENDER', 'usf_id'),
DATE_NOW,
DATE_NOW,
$gCurrentOrgId
// TODO add more params
);
$birthdayStatement = $gDb->queryPrepared($sql, $queryParams);
$numberBirthdays = $birthdayStatement->rowCount();
if ($numberBirthdays > 0) {
if ($plgBirthdayShowNames) {
// how many birthdays should be displayed (as a maximum)
$birthdayCount = null;
$birthdayArray = array();
while (($row = $birthdayStatement->fetch()) && $birthdayCount < $plg_show_display_limit) {
// the display type of the name
switch ($plg_show_names) {
case 1: // first name, last name
$plgShowName = $row['first_name'] . ' ' . $row['last_name'];
break;
case 2: // last name, first name
$plgShowName = $row['last_name'] . ', ' . $row['first_name'];
break;
case 3: // first name
$plgShowName = $row['first_name'];
break;
case 4: // Loginname
$plgShowName = $row['usr_login_name'];
break;
default: // first name, last name
$plgShowName = $row['first_name'] . ' ' . $row['last_name'];
}
// from a specified age, only the last name with salutation is displayed for logged out visitors
if (!$gValidLogin && $plg_show_alter_anrede <= $row['age']) {
if ($row['gender'] > 1) {
$plgShowName = $gL10n->get('PLG_BIRTHDAY_WOMAN_VAR', array($row['last_name']));
} else {
$plgShowName = $gL10n->get('PLG_BIRTHDAY_MAN_VAR', array($row['last_name']));
}
}
// show name and e-mail link for registered users
if ($gValidLogin) {
$plgShowName = '<a href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/profile/profile.php', array('user_uuid' => $row['usr_uuid'])) . '"
title="' . $gL10n->get('SYS_SHOW_PROFILE') . '">' . $plgShowName . '</a>';
// E-Mail-Adresse ist hinterlegt und soll auch bei eingeloggten Benutzern verlinkt werden
if ((string)$row['email'] !== '' && $plg_show_email_extern < 2) {
$plgShowName .= '
<a class="admidio-icon-link" href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/messages/messages_write.php', array('user_uuid' => $row['usr_uuid'])) . '">' .
'<i class="bi bi-envelope-fill" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_WRITE_EMAIL') . '"></i></a>';
}
} elseif ($plg_show_email_extern === 1 && strlen($row['email']) > 0) {
$plgShowName .= '
<a class="admidio-icon-link" href="mailto:' . $row['email'] . '">' .
'<i class="bi bi-envelope-fill" data-bs-toggle="tooltip" title="' . $gL10n->get('SYS_WRITE_EMAIL') . '"></i></a>';
}
// set css class and string for birthday today, in the future or in the past
$birthdayDate = DateTime::createFromFormat('Y-m-d', $row['birthday']);
if ($row['days_to_bdate'] < 0) {
$plgCssClass = 'plgBirthdayNameHighlightAgo';
if ($row['days_to_bdate'] == -1) {
$birthdayText = 'PLG_BIRTHDAY_YESTERDAY';
$plgDays = ' ';
} else {
$birthdayText = 'PLG_BIRTHDAY_PAST';
$plgDays = -$row['days_to_bdate'];
}
} elseif ($row['days_to_bdate'] > 0) {
$plgCssClass = 'plgBirthdayNameHighlightFuture';
if ($row['days_to_bdate'] == 1) {
$birthdayText = 'PLG_BIRTHDAY_TOMORROW';
$plgDays = ' ';
} else {
$birthdayText = 'PLG_BIRTHDAY_FUTURE';
$plgDays = $row['days_to_bdate'];
}
} else {
$plgCssClass = 'plgBirthdayNameHighlight';
$birthdayText = 'PLG_BIRTHDAY_TODAY';
$plgDays = $row['age'];
}
// don't show age of birthday person if preference is set
if ($plg_show_age === 0 || !$gValidLogin) {
$birthdayText .= '_NO_AGE';
}
$birthdayArray[] = array(
'userText' => $gL10n->get($birthdayText, array($plgShowName, $plgDays, $row['age'], $birthdayDate->format($gSettingsManager->getString('system_date'))))
);
// counting displayed birthdays
$birthdayCount++;
}
$birthdayPlugin->assignTemplateVariable('birthdays', $birthdayArray);
} else {
if ($numberBirthdays === 1) {
$birthdayPlugin->assignTemplateVariable('message',$gL10n->get('PLG_BIRTHDAY_ONE_MEMBER'));
} else {
$birthdayPlugin->assignTemplateVariable('message',$gL10n->get('PLG_BIRTHDAY_MORE_MEMBERS', array($numberBirthdays)));
}
}
} else {
// If the configuration is set accordingly, a message is output if no member has a birthday today
if (!$plg_show_hinweis_keiner) {
$birthdayPlugin->assignTemplateVariable('message',$gL10n->get('PLG_BIRTHDAY_NO_MEMBERS'));
}
}
echo $birthdayPlugin->html('plugin.birthday.tpl');
} catch (Throwable $e) {
echo $e->getMessage();
}