adm_program/system/classes/CategoryReport.php
<?php
use Admidio\Exception;
/**
* @brief Class manages the data for the report of module CategoryReport
*
* @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
*/
class CategoryReport
{
public array $headerData = array(); ///< Array mit allen Spaltenueberschriften
public array $listData = array(); ///< Array mit den Daten für den Report
public array $headerSelection = array(); ///< Array mit der Auswahlliste für die Spaltenauswahl
protected int $conf; ///< die gewaehlte Konfiguration
protected array $arrConfiguration = array(); ///< Array with the all configurations from the database
/**
* CategoryReport constructor
* @throws Exception
*/
public function __construct()
{
$this->generate_headerSelection();
}
/**
* Method checks whether a configuration with the transferred name already exists.
* If this is the case, "- copy" is appended.
* @param string $name Name that should be checked.
* @return string
* @throws Exception
*/
function createName(string $name): string
{
global $gDb, $gL10n, $gCurrentOrgId;
$sql = ' SELECT crt_name
FROM ' . TBL_CATEGORY_REPORT . '
WHERE ( crt_org_id = ? -- $gCurrentOrgId
OR crt_org_id IS NULL ) ';
$statement = $gDb->queryPrepared($sql, array($gCurrentOrgId));
while ($row = $statement->fetch()) {
if ($row['crt_name'] === $name) {
$name .= ' - ' . $gL10n->get('SYS_CARBON_COPY');
}
}
return $name;
}
/**
* Erzeugt die Arrays listData und headerData fuer den Report
* @return void
* @throws Exception
*/
public function generate_listData()
{
global $gDb, $gProfileFields, $gL10n, $gCurrentOrgId;
$workArray = array();
$number_row_pos = -1;
$number_col = array();
$columns = explode(',', $this->arrConfiguration[$this->conf]['col_fields']);
// run through the saved configurations
foreach ($columns as $key => $data) {
// This is only to check whether this release still exists.
// It could be that a profile field or role has been deleted since the last save
$found = $this->isInHeaderSelection($data);
if ($found == 0) {
continue;
} else {
$workArray[$key + 1] = array();
}
//$data splitten in Typ und ID
$type = substr($data, 0, 1);
$id = (int)substr($data, 1);
$workArray[$key + 1]['type'] = $type;
$workArray[$key + 1]['id'] = $id;
$this->headerData[$key + 1]['id'] = 0;
$this->headerData[$key + 1]['data'] = $this->headerSelection[$found]['data'];
switch ($type) {
case 'p': //p=profileField
// nur bei Profilfeldern wird 'id' mit der 'usf_id' ueberschrieben
$this->headerData[$key + 1]['id'] = $id;
$number_col[$key + 1] = '';
break;
case 'c': //c=categorie
$sql = 'SELECT DISTINCT mem_usr_id
FROM ' . TBL_CATEGORIES . '
INNER JOIN ' . TBL_ROLES . ' ON rol_cat_id = cat_id
INNER JOIN ' . TBL_MEMBERS . ' ON mem_rol_id = rol_id
WHERE cat_id = ? -- $id
AND cat_type = \'ROL\'
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW
AND ( cat_org_id = ? -- $gCurrentOrgId
OR cat_org_id IS NULL )';
$queryParams = array(
$id,
DATE_NOW,
DATE_NOW,
$gCurrentOrgId
);
$statement = $gDb->queryPrepared($sql, $queryParams);
while ($row = $statement->fetch()) {
$workArray[$key + 1]['usr_id'][] = $row['mem_usr_id'];
}
$number_col[$key + 1] = 0;
break;
case 'r': //r=role
$sql = 'SELECT mem_usr_id
FROM ' . TBL_ROLES . '
INNER JOIN ' . TBL_MEMBERS . ' ON mem_rol_id = rol_id
INNER JOIN ' . TBL_CATEGORIES . ' ON cat_id = rol_cat_id
AND cat_type = \'ROL\'
WHERE rol_id = ? -- $id
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW ';
$queryParams = array(
$id,
DATE_NOW,
DATE_NOW
);
$statement = $gDb->queryPrepared($sql, $queryParams);
while ($row = $statement->fetch()) {
$workArray[$key + 1]['usr_id'][] = $row['mem_usr_id'];
}
$number_col[$key + 1] = 0;
break;
case 'w': //w=without (Leader)
$sql = 'SELECT mem_usr_id
FROM ' . TBL_ROLES . '
INNER JOIN ' . TBL_MEMBERS . ' ON mem_rol_id = rol_id
INNER JOIN ' . TBL_CATEGORIES . ' ON cat_id = rol_cat_id
AND cat_type = \'ROL\'
WHERE rol_id = ? -- $id
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW
AND mem_leader = false ';
$queryParams = array(
$id,
DATE_NOW,
DATE_NOW
);
$statement = $gDb->queryPrepared($sql, $queryParams);
while ($row = $statement->fetch()) {
$workArray[$key + 1]['usr_id'][] = $row['mem_usr_id'];
}
$number_col[$key + 1] = 0;
break;
case 'l': //l=leader
$sql = 'SELECT mem_usr_id
FROM ' . TBL_ROLES . '
INNER JOIN ' . TBL_MEMBERS . ' ON mem_rol_id = rol_id
INNER JOIN ' . TBL_CATEGORIES . ' ON cat_id = rol_cat_id
AND cat_type = \'ROL\'
WHERE rol_id = ? -- $id
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW
AND mem_leader = true ';
$queryParams = array(
$id,
DATE_NOW,
DATE_NOW
);
$statement = $gDb->queryPrepared($sql, $queryParams);
while ($row = $statement->fetch()) {
$workArray[$key + 1]['usr_id'][] = $row['mem_usr_id'];
}
$number_col[$key + 1] = 0;
break;
case 'n': //n=number
// eine oder mehrere Zaehlspalten wurden definiert
// die Position der letzten Spalte zwischenspeichern
// Werte werden aber nur in der letzten Zaehlspalte angezeigt
// alles andere ist Unsinn (warum soll derselbe Wert mehrfach angezeigt werden)
$number_row_pos = $key + 1;
$number_col[$key + 1] = '';
break;
case 'a': //a=additional
$number_col[$key + 1] = '';
break;
}
}
$number_col[1] = $gL10n->get('SYS_QUANTITY') . ' (' . $gL10n->get('SYS_COLUMN') . ')';
// Read in all members of the current organisation
$sql = ' SELECT mem_usr_id
FROM ' . TBL_CATEGORIES . '
INNER JOIN ' . TBL_ROLES . ' ON rol_cat_id = cat_id
INNER JOIN ' . TBL_MEMBERS . ' ON mem_rol_id = rol_id
WHERE cat_type = \'ROL\'
AND ( cat_org_id = ? -- $gCurrentOrgId
OR cat_org_id IS NULL )
AND rol_valid = true
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW ';
$queryParams = array(
$gCurrentOrgId,
DATE_NOW,
DATE_NOW
);
$statement = $gDb->queryPrepared($sql, $queryParams);
while ($row = $statement->fetch()) {
$this->listData[$row['mem_usr_id']] = array();
}
$user = new User($gDb, $gProfileFields);
// go through all members
foreach ($this->listData as $member => $dummy) {
$user->readDataById($member);
$memberShips = $user->getRoleMemberships();
$number_row_count = 0;
// Are there role and/or category restrictions?
$roleCategoryMarker = true;
if ((string)$this->arrConfiguration[$this->conf]['selection_role'] !== '') {
$roleCategoryMarker = false;
foreach (explode(',', $this->arrConfiguration[$this->conf]['selection_role']) as $rol) {
if ($user->isMemberOfRole((int)$rol)) {
$roleCategoryMarker = true;
}
}
}
if ((string)$this->arrConfiguration[$this->conf]['selection_cat'] !== '') {
foreach (explode(',', $this->arrConfiguration[$this->conf]['selection_cat']) as $cat) {
if ($this->isMemberOfCategory($cat, $member)) {
$roleCategoryMarker = true;
}
}
}
if (!$roleCategoryMarker) {
unset($this->listData[$member]);
continue;
}
foreach ($workArray as $key => $data) {
if ($data['type'] == 'p') {
$this->listData[$member][$key] = $user->getValue($gProfileFields->getPropertyById($data['id'], 'usf_name_intern'), 'database');
} elseif ($data['type'] == 'a') { //Sonderfall: Rollengesamtuebersicht erstellen
$role = new TableRoles($gDb);
$this->listData[$member][$key] = '';
foreach ($memberShips as $rol_id) {
$role->readDataById($rol_id);
$this->listData[$member][$key] .= $role->getValue('rol_name') . '; ';
}
$this->listData[$member][$key] = trim($this->listData[$member][$key], '; ');
} elseif ($data['type'] == 'n') { //Sonderfall: Anzahlspalte
$this->listData[$member][$key] = '';
} else {
if (isset($data['usr_id']) and in_array($member, $data['usr_id'])) {
$this->listData[$member][$key] = true;
$number_row_count++;
$number_col[$key]++;
} else {
$this->listData[$member][$key] = '';
}
}
}
if ($number_row_pos > -1) {
$this->listData[$member][$number_row_pos] = $number_row_count;
}
}
if ($this->arrConfiguration[$this->conf]['number_col'] == 1) {
$this->listData[] = $number_col;
}
}
/**
* Erzeugt die Auswahlliste fuer die Spaltenauswahl
* @return void
* @throws Exception
*/
private function generate_headerSelection()
{
global $gDb, $gL10n, $gProfileFields, $gCurrentUser, $gCurrentOrgId;
$categories = array();
$i = 1;
foreach ($gProfileFields->getProfileFields() as $field) {
if ($field->getValue('usf_hidden') == 0 || $gCurrentUser->editUsers()) {
$this->headerSelection[$i]['id'] = 'p' . $field->getValue('usf_id');
$this->headerSelection[$i]['cat_name'] = $field->getValue('cat_name');
$this->headerSelection[$i]['data'] = addslashes($field->getValue('usf_name'));
$i++;
}
}
// alle (Rollen-)Kategorien der aktuellen Organisation einlesen
$sql = ' SELECT cat_name, cat_id
FROM ' . TBL_CATEGORIES . '
WHERE cat_type = \'ROL\'
AND ( cat_org_id = ? -- $gCurrentOrgId
OR cat_org_id IS NULL )';
$statement = $gDb->queryPrepared($sql, array($gCurrentOrgId));
$k = 0;
while ($row = $statement->fetch()) {
// check if the category name must be translated
if (Admidio\Language::isTranslationStringId($row['cat_name'])) {
$row['cat_name'] = $gL10n->get($row['cat_name']);
}
$categories[$k]['cat_id'] = $row['cat_id'];
$categories[$k]['cat_name'] = $row['cat_name'];
$categories[$k]['data'] = $gL10n->get('SYS_CATEGORY') . ': ' . $row['cat_name'];
$k++;
}
// alle eingelesenen Kategorien durchlaufen und die Rollen dazu einlesen
foreach ($categories as $data) {
$this->headerSelection[$i]['id'] = 'c' . $data['cat_id'];
$this->headerSelection[$i]['cat_name'] = $data['cat_name'];
$this->headerSelection[$i]['data'] = $data['data'];
$i++;
$sql = 'SELECT DISTINCT rol_name, rol_id, rol_valid
FROM ' . TBL_CATEGORIES . '
INNER JOIN ' . TBL_ROLES . ' ON rol_cat_id = cat_id
WHERE cat_id = ? ';
$statement = $gDb->queryPrepared($sql, array($data['cat_id']));
while ($row = $statement->fetch()) {
$marker = '';
if ($row['rol_valid'] == 0) {
$marker = ' (*)';
}
$this->headerSelection[$i]['id'] = 'r' . $row['rol_id']; //r wie role
$this->headerSelection[$i]['cat_name'] = $data['cat_name'];
$this->headerSelection[$i]['data'] = $gL10n->get('SYS_ROLE') . ': ' . $row['rol_name'] . $marker;
$i++;
$this->headerSelection[$i]['id'] = 'w' . $row['rol_id']; //w wie without (Leader)
$this->headerSelection[$i]['cat_name'] = $data['cat_name'];
$this->headerSelection[$i]['data'] = $gL10n->get('SYS_ROLE_WITHOUT_LEADER') . ': ' . $row['rol_name'] . $marker;
$i++;
$this->headerSelection[$i]['id'] = 'l' . $row['rol_id']; //l wie leader
$this->headerSelection[$i]['cat_name'] = $data['cat_name'];
$this->headerSelection[$i]['data'] = $gL10n->get('SYS_LEADER') . ': ' . $row['rol_name'] . $marker;
$i++;
}
}
//Zusatzspalte fuer die Gesamtrollenuebersicht erzeugen
$this->headerSelection[$i]['id'] = 'adummy'; //a wie additional
$this->headerSelection[$i]['cat_name'] = $gL10n->get('SYS_ADDITIONAL_COLUMNS');
$this->headerSelection[$i]['data'] = $gL10n->get('SYS_ROLE_MEMBERSHIPS');
$i++;
//Zusatzspalte fuer die Anzahl erzeugen
$this->headerSelection[$i]['id'] = 'ndummy'; //n wie number
$this->headerSelection[$i]['cat_name'] = $gL10n->get('SYS_ADDITIONAL_COLUMNS');
$this->headerSelection[$i]['data'] = $gL10n->get('SYS_QUANTITY') . ' (' . $gL10n->get('SYS_ROW') . ')';
}
/**
* Funktion liest das Konfigurationsarray ein
* @return array $config das Konfigurationsarray
* @throws Exception
*/
public function getConfigArray(): array
{
global $gDb, $gSettingsManager, $gCurrentOrgId;
if (count($this->arrConfiguration) === 0) {
$sql = ' SELECT *
FROM ' . TBL_CATEGORY_REPORT . '
WHERE ( crt_org_id = ? -- $gCurrentOrgId
OR crt_org_id IS NULL ) ';
$statement = $gDb->queryPrepared($sql, array($gCurrentOrgId));
while ($row = $statement->fetch()) {
$values = array();
$values['id'] = $row['crt_id'];
$values['name'] = SecurityUtils::encodeHTML($row['crt_name']);
$values['col_fields'] = $row['crt_col_fields'];
$values['selection_role'] = $row['crt_selection_role'];
$values['selection_cat'] = $row['crt_selection_cat'];
$values['number_col'] = $row['crt_number_col'];
$values['default_conf'] = false;
if ($gSettingsManager->getInt('category_report_default_configuration') == $row['crt_id']) {
$values['default_conf'] = true;
}
$this->arrConfiguration[] = $values;
}
}
return $this->arrConfiguration;
}
/**
* get the active configuration
*/
public function getConfiguration(): int
{
return $this->conf;
}
/**
* Checks whether the transferred value exists in the column selection list.
* Note: the column selection list is always up-to-date as it is newly generated,
* but the value to be checked may be out of date as it comes from the configuration table
* @param string $search_value
* @return int
*/
public function isInHeaderSelection(string $search_value): int
{
$ret = 0;
foreach ($this->headerSelection as $key => $data) {
if ($data['id'] == $search_value) {
$ret = $key;
break;
}
}
return $ret;
}
/**
* Funktion prueft, ob ein User Angehoeriger einer bestimmten Kategorie ist
*
* @param int $cat_id ID der zu pruefenden Kategorie
* @param int $user_id ID des Users, fuer den die Mitgliedschaft geprueft werden soll
* @return bool
* @throws Exception
*/
private function isMemberOfCategory(int $cat_id, int $user_id = 0): bool
{
global $gCurrentUserId, $gDb, $gCurrentOrgId;
if ($user_id == 0) {
$user_id = $gCurrentUserId;
} elseif (is_numeric($user_id) === false) {
return false;
}
$sql = 'SELECT mem_id
FROM ' . TBL_MEMBERS . ', ' . TBL_ROLES . ', ' . TBL_CATEGORIES . '
WHERE mem_usr_id = ? -- $user_id
AND mem_begin <= ? -- DATE_NOW
AND mem_end > ? -- DATE_NOW
AND mem_rol_id = rol_id
AND cat_id = ? -- $cat_id
AND rol_valid = true
AND rol_cat_id = cat_id
AND ( cat_org_id = ? -- $gCurrentOrgId
OR cat_org_id IS NULL ) ';
$queryParams = array(
$user_id,
DATE_NOW,
DATE_NOW,
$cat_id,
$gCurrentOrgId
);
$statement = $gDb->queryPrepared($sql, $queryParams);
$user_found = $statement->rowCount();
if ($user_found == 1) {
return true;
} else {
return false;
}
}
/**
* Funktion speichert das Konfigurationsarray
* @param array $arrConfiguration
* @return array das Konfigurationsarray
* @throws Exception
*/
public function saveConfigArray(array $arrConfiguration): array
{
global $gDb, $gCurrentOrgId, $gSettingsManager;
$defaultConfiguration = 0;
$gDb->startTransaction();
foreach ($arrConfiguration as $values) {
if ($values['id'] === '' || $values['id'] > 0) { // id > 0 (=edit a configuration) or '' (=append a configuration)
$categoryReport = new TableAccess($gDb, TBL_CATEGORY_REPORT, 'crt', $values['id']);
$categoryReport->setValue('crt_org_id', $gCurrentOrgId);
$categoryReport->setValue('crt_name', $values['name']);
$categoryReport->setValue('crt_col_fields', $values['col_fields']);
$categoryReport->setValue('crt_selection_role', $values['selection_role']);
$categoryReport->setValue('crt_selection_cat', $values['selection_cat']);
$categoryReport->setValue('crt_number_col', $values['number_col']);
$categoryReport->save();
if ($values['default_conf'] === true || $defaultConfiguration === 0) {
$defaultConfiguration = $categoryReport->getValue('crt_id');
}
// set default configuration
$gSettingsManager->set('category_report_default_configuration', $defaultConfiguration);
} else { // delete
$values['id'] = $values['id'] * (-1);
$categoryReport = new TableAccess($gDb, TBL_CATEGORY_REPORT, 'crt', $values['id']);
$categoryReport->delete();
}
}
$gDb->endTransaction();
$this->arrConfiguration = array();
return $this->getConfigArray();
}
/**
* set the internal active configuration to the crtId of the parameter
*/
public function setConfiguration($crtId)
{
foreach ($this->arrConfiguration as $key => $values) {
if ($values['id'] == $crtId) {
$this->conf = $key;
}
}
}
}