Fix: code quality checks

- Page for the Infocenter details messages block should use CL/Message_model logic
- Page to read personal messages wrongly displays the sender
- Page to read message with token wrongly displays the sender
- All the pages should display the sender using the following logic:
 	- If the sender id is set and it is not the system sender and the organization unit is not set then display the sender name + surname
 	- If the sender id is set and it is not the system sender and the organization unit is set then display the sender name + surname
 	- If the sender id is set and it is the system sender and the organization unit is set then display the organization unit
 	- If the sender id is set and it is the system sender and the organization unit is not set then display "System sender"
 	- The organization unit should firstly retrieved from the degree programs, if not available then from the organization units, if not available then display "System sender"
- Centralized this logic in the CL/Message_model as much as possible

application/config/constants.php

@@ -10,26 +10,26 @@ if (!defined('BASEPATH')) exit('No direct script access allowed');
 | Used to indicate the conditions under which the script is exit()ing.
 |
 */
-define('EXIT_SUCCESS',						0); // no errors
-define('EXIT_ERROR',						1); // generic error
-define('EXIT_MODEL',						2); // model error
-define('EXIT_CONFIG',						3); // configuration error
-define('EXIT_UNKNOWN_FILE',					4); // file not found
-define('EXIT_UNKNOWN_CLASS',				5); // unknown class
-define('EXIT_UNKNOWN_METHOD',				6); // unknown class method
-define('EXIT_USER_INPUT',					7); // invalid user input
-define('EXIT_DATABASE',						8); // database error
-define('EXIT_VALIDATION_UDF',				10); // UDF validation has been failed
+define('EXIT_SUCCESS',				0); // no errors
+define('EXIT_ERROR',				1); // generic error
+define('EXIT_MODEL',				2); // model error
+define('EXIT_CONFIG',				3); // configuration error
+define('EXIT_UNKNOWN_FILE',			4); // file not found
+define('EXIT_UNKNOWN_CLASS',			5); // unknown class
+define('EXIT_UNKNOWN_METHOD',			6); // unknown class method
+define('EXIT_USER_INPUT',			7); // invalid user input
+define('EXIT_DATABASE',				8); // database error
+define('EXIT_VALIDATION_UDF',			10); // UDF validation has been failed
 define('EXIT_VALIDATION_UDF_MIN_VALUE',		11); // UDF validation has been failed -> MIN VALUE
 define('EXIT_VALIDATION_UDF_MAX_VALUE',		12); // UDF validation has been failed -> MAX VALUE
 define('EXIT_VALIDATION_UDF_MIN_LENGTH',	13); // UDF validation has been failed -> MIN LENGTH
 define('EXIT_VALIDATION_UDF_MAX_LENGTH',	14); // UDF validation has been failed -> MAX LENGTH
-define('EXIT_VALIDATION_UDF_REGEX',			15); // UDF validation has been failed -> REGEX
+define('EXIT_VALIDATION_UDF_REGEX',		15); // UDF validation has been failed -> REGEX
 define('EXIT_VALIDATION_UDF_REQUIRED',		16); // UDF validation has been failed -> REQUIRED
 define('EXIT_VALIDATION_UDF_NOT_VALID_VAL',	17); // UDF validation has been failed -> Not valid value, object or array
 
-define('EXIT_AUTO_MIN',						1000); // lowest automatically-assigned error code
-define('EXIT_AUTO_MAX',						2000); // highest automatically-assigned error code
+define('EXIT_AUTO_MIN',				1000); // lowest automatically-assigned error code
+define('EXIT_AUTO_MAX',				2000); // highest automatically-assigned error code
 
 /*
 |--------------------------------------------------------------------------
@@ -45,7 +45,7 @@ define('BEGINNING_OF_TIME',					'1970-01-01');
 */
 // Foreign authentication methods
 define('AUTH_HBALDAP',	'httpBasicAuthLDAP');
-define('AUTH_BT',		'bewerbung');
+define('AUTH_BT',	'bewerbung');
 
 // Login methods
 define('AUTH_LDAP',	'ldap');
@@ -53,7 +53,7 @@ define('AUTH_DB',	'database');
 define('AUTH_SSO',	'sso');
 
 // Authentication return codes
-define('AUTH_SUCCESS',				0);
+define('AUTH_SUCCESS',			0);
 define('AUTH_NOT_AUTHENTICATED',	1);
 define('AUTH_INVALID_CREDENTIALS',	2);
 
@@ -112,7 +112,7 @@ define('FOPEN_READ_WRITE_CREATE_STRICT', 'x+b');
 | of this setting
 |
 */
-define('SHOW_DEBUG_BACKTRACE', TRUE);
+define('SHOW_DEBUG_BACKTRACE', true);
 
 /*
 |--------------------------------------------------------------------------
@@ -127,24 +127,25 @@ define('EMAIL_CONFIG_INDEX', 'mail');
 |--------------------------------------------------------------------------
 */
 // Message statuses
-define('MSG_STATUS_UNREAD',		0);
-define('MSG_STATUS_READ',		1);
+define('MSG_STATUS_UNREAD',	0);
+define('MSG_STATUS_READ',	1);
 define('MSG_STATUS_ARCHIVED',	2);
 define('MSG_STATUS_DELETED',	3);
 
 // Message priorities
-define('MSG_PRIORITY_LOW',		1);
+define('MSG_PRIORITY_LOW',	1);
 define('MSG_PRIORITY_NORMAL',	2);
-define('MSG_PRIORITY_HIGH',		3);
+define('MSG_PRIORITY_HIGH',	3);
 define('MSG_PRIORITY_URGENT',	4);
 
 // Message error status
-define('MSG_ERR_INVALID_SUBJECT',		40);
-define('MSG_ERR_INVALID_BODY',			41);
-define('MSG_ERR_INVALID_TEMPLATE',		42);
-define('MSG_ERR_INVALID_MSG_ID',		43);
-define('MSG_ERR_INVALID_STATUS_ID',		44);
-define('MSG_ERR_INVALID_SENDER',		45);
+define('MSG_ERR_INVALID_SUBJECT',	40);
+define('MSG_ERR_INVALID_BODY',		41);
+define('MSG_ERR_INVALID_TEMPLATE',	42);
+define('MSG_ERR_INVALID_MSG_ID',	43);
+define('MSG_ERR_INVALID_STATUS_ID',	44);
+define('MSG_ERR_INVALID_SENDER',	45);
 define('MSG_ERR_INVALID_RECIPIENTS',	46);
-define('MSG_ERR_INVALID_OU',			47);
-define('MSG_ERR_INVALID_TOKEN',			48);
+define('MSG_ERR_INVALID_OU',		47);
+define('MSG_ERR_INVALID_TOKEN',		48);
+

application/controllers/jobs/ReihungstestJob.php

@@ -1,8 +1,32 @@
 <?php
+/**
+ * Copyright (C) 2023 fhcomplete.org
+ *
+ * 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 3 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, see <https://www.gnu.org/licenses/>.
+ */
+
 if (!defined('BASEPATH')) exit('No direct script access allowed');
 
+use \stdClass as stdClass;
+use \DateTime as DateTime;
+use \DB_Model as DB_Model;
+
 class ReihungstestJob extends JOB_Controller
 {
+	// OE used to as sender for the test deadline reminder
+	const OU_SENDER_TEST_REMINDER = 'infocenter';
+
 	/**
 	 * Constructor
 	 */
@@ -21,6 +45,9 @@ class ReihungstestJob extends JOB_Controller
 
 		// Load helpers
 		$this->load->helper('hlp_sancho_helper');
+
+		// Loads CLMessagesModel
+		$this->load->model('CL/Messages_model', 'CLMessagesModel');
 	}
 
 	/**
@@ -64,7 +91,8 @@ class ReihungstestJob extends JOB_Controller
 				'ReihungstestJob',
 				$content_data_arr,
 				MAIL_INFOCENTER,
-				'Support für die Reihungstest-Verwaltung');
+				'Support für die Reihungstest-Verwaltung'
+			);
 		}
 	}
 
@@ -113,6 +141,8 @@ class ReihungstestJob extends JOB_Controller
 
 		foreach ($reachedRegistration_rt_arr as $reihungstest)
 		{
+			$reihungstestDatum = date_format(date_create($reihungstest->datum), 'd.m.Y');
+
 			$applicants = $this->ReihungstestModel->getApplicantsOfPlacementTestForCronjob($reihungstest->reihungstest_id);
 
 			if (hasData($applicants))
@@ -153,14 +183,22 @@ class ReihungstestJob extends JOB_Controller
 				}
 				if ($applicantCounter == 0)
 				{
-					$mailcontent = '<p style="font-family: verdana, sans-serif;">Der Anmeldeschluss für den zentralen Reihungstest am ' . date_format(date_create($reihungstest->datum), 'd.m.Y') . ' um ' . $reihungstest->uhrzeit . ' Uhr wurde gestern erreicht.</p>';
-					$mailcontent .= '<p style="font-family: verdana, sans-serif;"><b>Für den Studiengang '.$studiengang_kuerzel.' nehmen keine InteressentInnen an diesem Reihungstest teil</b></p>';
+					$mailcontent = '<p style="font-family: verdana, sans-serif;">Der Anmeldeschluss für den zentralen Reihungstest am ' .
+						$reihungstestDatum . ' um ' . $reihungstest->uhrzeit . ' Uhr wurde gestern erreicht.</p>';
+
+					$mailcontent .= '<p style="font-family: verdana, sans-serif;"><b>Für den Studiengang '.
+						$studiengang_kuerzel.
+						' nehmen keine InteressentInnen an diesem Reihungstest teil</b></p>';
 				}
 				else
 				{
-					$mailcontent = '<p style="font-family: verdana, sans-serif;">Der Anmeldeschluss für den zentralen Reihungstest am ' . date_format(date_create($reihungstest->datum), 'd.m.Y') . ' um ' . $reihungstest->uhrzeit . ' Uhr wurde gestern erreicht.</p>';
+					$mailcontent = '<p style="font-family: verdana, sans-serif;">Der Anmeldeschluss für den zentralen Reihungstest am ' .
+						$reihungstestDatum .
+						' um ' . $reihungstest->uhrzeit . ' Uhr wurde gestern erreicht.</p>';
+
 					$mailcontent .= '
-					<p style="font-family: verdana, sans-serif;"><b>' . $applicantCounter . '</b> InteressentIn(nen) des Studiengangs ' . $studiengang_kuerzel . ' nehmen daran teil:</p>
+					<p style="font-family: verdana, sans-serif;"><b>' . $applicantCounter . '</b> InteressentIn(nen) des Studiengangs ' .
+					$studiengang_kuerzel . ' nehmen daran teil:</p>
 					<p style="font-family: verdana, sans-serif;">
 						<a href="'.APP_ROOT.'vilesci/stammdaten/auswertung_fhtw.php?reihungstest='.$reihungstest->reihungstest_id.'&studiengang='.$bachelorStudiengang->studiengang_kz.'" target="_blank">
 							Liste der Anmeldungen
@@ -176,12 +214,13 @@ class ReihungstestJob extends JOB_Controller
 						'Sancho_ReihungstestteilnehmerJob',
 						$mailcontent_data_arr,
 						$bachelorStudiengang->email,
-						'Anmeldeschluss Reihungstest ' . date_format(date_create($reihungstest->datum), 'd.m.Y') . ' ' . $reihungstest->uhrzeit . ' Uhr',
+						'Anmeldeschluss Reihungstest ' . $reihungstestDatum . ' ' . $reihungstest->uhrzeit . ' Uhr',
 						'sancho_header_min_bw.jpg',
 						'sancho_footer_min_bw.jpg',
 						$from,
 						'',
-						$bcc);
+						$bcc
+					);
 				}
 			}
 		}
@@ -229,7 +268,6 @@ class ReihungstestJob extends JOB_Controller
 		}
 
 		$studiengang = '';
-		$mailReceipients = ''; // String with all mailadresses
 		$mailcontent_data_arr = array();
 		$headerstyle = 'style="background: #DCE4EF; border: 1px solid #FFF; padding: 4px; text-align: left;"';
 		$rowstyle = 'style="background-color: #EEEEEE; padding: 4px;"';
@@ -248,10 +286,10 @@ class ReihungstestJob extends JOB_Controller
 						$mailcontent .= $applicants_list;
 						$mailcontent .= '</tbody></table>';
 						$mailcontent .= '<p style="font-family: verdana, sans-serif;">
-											<a href="'.APP_ROOT.'vilesci/stammdaten/auswertung_fhtw.php?reihungstest='.$applicant->reihungstest_id.'&studiengang='.$studiengang.'" target="_blank">
-												Liste der Anmeldungen
-											</a>
-										</p>';
+							<a href="'.APP_ROOT.'vilesci/stammdaten/auswertung_fhtw.php?reihungstest='.$applicant->reihungstest_id.'&studiengang='.$studiengang.'" target="_blank">
+								Liste der Anmeldungen
+							</a>
+						</p>';
 						$mailcontent_data_arr['table'] = $mailcontent;
 						sendSanchoMail(
 							'Sancho_ReihungstestteilnehmerJob',
@@ -262,12 +300,15 @@ class ReihungstestJob extends JOB_Controller
 							'sancho_footer_min_bw.jpg',
 							$from,
 							'',
-							$bcc);
+							$bcc
+						);
 						$applicants_list = '';
 						$mailcontent_data_arr = array();
 					}
 
-					$mailcontent = '<p style="font-family: verdana, sans-serif;">Folgende InteressentInnen wurden <b>nach</b> der Anmeldefrist zu einem Reihungstest hinzugefügt.<br>Details siehe Link</p>';
+					$mailcontent = '<p style="font-family: verdana, sans-serif;">
+						Folgende InteressentInnen wurden <b>nach</b> der Anmeldefrist zu einem Reihungstest hinzugefügt.<br>Details siehe Link
+					</p>';
 					$mailcontent .= '
 					<table width="100%" style="cellpadding: 3px; font-family: verdana, sans-serif; border: 1px solid #000000;">
 						<thead>
@@ -283,7 +324,6 @@ class ReihungstestJob extends JOB_Controller
 				}
 
 				$studiengang = $applicant->studiengang_kz;
-				$mailReceipients .= $applicant->email . ';';
 				$applicants_list .= '
 						<tr ' . $rowstyle . '>
 						<td>' . date_format(date_create($applicant->datum), 'd.m.Y') . '</td>
@@ -299,10 +339,10 @@ class ReihungstestJob extends JOB_Controller
 			$mailcontent .= $applicants_list;
 			$mailcontent .= '</tbody></table>';
 			$mailcontent .= '<p style="font-family: verdana, sans-serif;">
-								<a href="'.APP_ROOT.'vilesci/stammdaten/auswertung_fhtw.php?reihungstest='.$applicant->reihungstest_id.'&studiengang='.$studiengang.'" target="_blank">
-									Liste der Anmeldungen
-								</a>
-							</p>';
+				<a href="'.APP_ROOT.'vilesci/stammdaten/auswertung_fhtw.php?reihungstest='.$applicant->reihungstest_id.'&studiengang='.$studiengang.'" target="_blank">
+					Liste der Anmeldungen
+				</a>
+			</p>';
 			$mailcontent_data_arr['table'] = $mailcontent;
 			sendSanchoMail(
 				'Sancho_ReihungstestteilnehmerJob',
@@ -313,7 +353,8 @@ class ReihungstestJob extends JOB_Controller
 				'sancho_footer_min_bw.jpg',
 				$from,
 				'',
-				$bcc);
+				$bcc
+			);
 		}
 	}
 
@@ -346,6 +387,7 @@ class ReihungstestJob extends JOB_Controller
 
 		// Get placement tests with testdate within the next 2 weeks
 		$resultNextTestDates = $this->ReihungstestModel->getNextPlacementtests($degreeProgram, 14);
+
 		if (hasData($resultNextTestDates))
 		{
 			$nextTestDates = $resultNextTestDates->retval;
@@ -357,7 +399,8 @@ class ReihungstestJob extends JOB_Controller
 				$testsOndate = array();
 
 				// Deduct days till 2 working days are reached
-				for ($i = 1; ; $i++)
+				// NOTE: the condition is to avoid a never ending loop
+				for ($i = 1; $workingdays < 10; $i++)
 				{
 					if (isDateWorkingDay($testDates->datum, $i) === true)
 					{
@@ -393,7 +436,8 @@ class ReihungstestJob extends JOB_Controller
 				{
 					foreach ($testsOndate as $reihungstest)
 					{
-						// Loads applicants of a test
+						// Loads applicants of a test with prestudent ID
+						$applicants_arr = '';
 						$applicants = $this->ReihungstestModel->getApplicantsOfPlacementTest($reihungstest->reihungstest_id);
 
 						if (hasData($applicants))
@@ -405,34 +449,35 @@ class ReihungstestJob extends JOB_Controller
 							show_error(getError($applicants));
 						}
 
-						foreach ($applicants_arr as $applicant)
+						if (!empty($applicants_arr))
 						{
-							$mailcontent_data_arr = array();
-							$mailcontent_data_arr['anrede'] = $applicant->anrede;
-							$mailcontent_data_arr['nachname'] = $applicant->nachname;
-							$mailcontent_data_arr['vorname'] = $applicant->vorname;
-							$mailcontent_data_arr['rt_datum'] = date_format(date_create($reihungstest->datum), 'd.m.Y');
-							$mailcontent_data_arr['rt_uhrzeit'] = date_format(date_create($reihungstest->uhrzeit), 'H:i');
-							$mailcontent_data_arr['rt_raum'] = $applicant->planbezeichnung;
-							if ($applicant->lageplan == '')
+							foreach ($applicants_arr as $applicant)
 							{
-								$mailcontent_data_arr['wegbeschreibung'] = 'Für diesen Raum liegt noch keine Wegbeschreibung vor.<br><br>No directions were found for this room';
-							}
-							else
-							{
-								$mailcontent_data_arr['wegbeschreibung'] = $applicant->lageplan;
-							}
+								$mailcontent_data_arr = array();
+								$mailcontent_data_arr['anrede'] = $applicant->anrede;
+								$mailcontent_data_arr['nachname'] = $applicant->nachname;
+								$mailcontent_data_arr['vorname'] = $applicant->vorname;
+								$mailcontent_data_arr['rt_datum'] = date_format(date_create($reihungstest->datum), 'd.m.Y');
+								$mailcontent_data_arr['rt_uhrzeit'] = date_format(date_create($reihungstest->uhrzeit), 'H:i');
+								$mailcontent_data_arr['rt_raum'] = $applicant->planbezeichnung;
+								if ($applicant->lageplan == '')
+								{
+									$mailcontent_data_arr['wegbeschreibung'] = 'Für diesen Raum liegt noch keine Wegbeschreibung vor.
+										<br><br>No directions were found for this room';
+								}
+								else
+								{
+									$mailcontent_data_arr['wegbeschreibung'] = $applicant->lageplan;
+								}
 
-							sendSanchoMail(
-								'Sancho_RemindApplicantsOfTest',
-								$mailcontent_data_arr,
-								$applicant->email,
-								'Ihre Anmeldung zum Reihungstest - Reminder / Your registration for the placement test - Reminder',
-								DEFAULT_SANCHO_HEADER_IMG,
-								DEFAULT_SANCHO_FOOTER_IMG,
-								$from,
-								'',
-								$bcc);
+								$this->CLMessagesModel->sendExplicitTemplateSenderId(
+									$this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID),
+									$applicant->prestudent_id,
+									self::OU_SENDER_TEST_REMINDER,
+									'Sancho_RemindApplicantsOfTest',
+									$mailcontent_data_arr
+								);
+							}
 						}
 					}
 				}
@@ -494,7 +539,7 @@ class ReihungstestJob extends JOB_Controller
 				person_id,
 				tbl_reihungstest.studiensemester_kurzbz,
 				tbl_reihungstest.reihungstest_id,
-				(SELECT(tbl_reihungstest.datum::text || \' \' || tbl_reihungstest.uhrzeit::text)::timestamp) AS reihungstest_timestamp
+				(SELECT(tbl_reihungstest.datum::text || \' \' || tbl_reihungstest.uhrzeit::text)::timestamp) as reihungstest_timestamp
 			');
 			$this->PrestudentModel->addJoin('public.tbl_studiengang', 'studiengang_kz');
 			$this->PrestudentModel->addJoin('public.tbl_studiengangstyp', 'typ');
@@ -540,11 +585,9 @@ class ReihungstestJob extends JOB_Controller
 		}
 
 		// Sort by STG. This is important to send the mails clustered by STG to the different STG assistances.
-		usort($result_arr, function ($a, $b)
-		{
-			if ($a->studiengang_kz == $b->studiengang_kz) {
-				return 0;
-			}
+		usort($result_arr, function ($a, $b) {
+			if ($a->studiengang_kz == $b->studiengang_kz) return 0;
+
 			return ($a->studiengang_kz < $b->studiengang_kz) ? -1 : 1;
 		});
 
@@ -687,6 +730,7 @@ class ReihungstestJob extends JOB_Controller
 		}
 
 		// Set associative array with the prepared HTML tables and URL be used by the template's variables
+		$content_data_arr = array();
 		$content_data_arr['studienplan_list'] = $studienplan_list;
 		$content_data_arr['freie_plaetze_list'] = $freie_plaetze_list;
 		$content_data_arr['link'] = site_url('/organisation/Reihungstest');
@@ -709,7 +753,9 @@ class ReihungstestJob extends JOB_Controller
 			$counter++;
 		}
 
+		$content_data_arr = array();
 		$content_data_arr['link'] = $content;
+
 		return $content_data_arr;
 	}
 
@@ -808,13 +854,13 @@ class ReihungstestJob extends JOB_Controller
 			return;
 		}
 
-		$qry = "WITH prst AS (
+		$qry = "WITH prst as (
 					SELECT DISTINCT
-						get_rolle_prestudent (tbl_prestudent.prestudent_id, ?) AS laststatus,
+						get_rolle_prestudent (tbl_prestudent.prestudent_id, ?) as laststatus,
 						tbl_prestudentstatus.studiensemester_kurzbz,
-						tbl_prestudentstatus.datum AS prestudenstatus_datum,
+						tbl_prestudentstatus.datum as prestudenstatus_datum,
 						tbl_prestudent.*,
-						tbl_studiengang.typ AS studiengang_typ
+						tbl_studiengang.typ as studiengang_typ
 					FROM PUBLIC.tbl_person
 						JOIN PUBLIC.tbl_prestudent USING (person_id)
 						JOIN PUBLIC.tbl_prestudentstatus USING (prestudent_id)
@@ -822,16 +868,26 @@ class ReihungstestJob extends JOB_Controller
 						JOIN lehre.tbl_studienordnung USING (studienordnung_id)
 						JOIN PUBLIC.tbl_studiengang ON (tbl_studienordnung.studiengang_kz = tbl_studiengang.studiengang_kz)
 					WHERE get_rolle_prestudent (tbl_prestudent.prestudent_id, ?) IN ('Aufgenommener','Bewerber','Wartender','Abgewiesener')
-						AND studiensemester_kurzbz = ? 
+						AND studiensemester_kurzbz = ?
 						AND tbl_studiengang.typ IN ('b', 'm')
 				)
 				SELECT * FROM prst
 				WHERE prestudenstatus_datum >= (SELECT CURRENT_DATE - 1)
-				AND (studiengang_typ = 'b' OR (studiengang_typ = 'm' AND EXISTS (SELECT 1 /* Master Studiengänge berücksichtigen wenn auch Bachelor im gleichen Semester */
-																					FROM prst prstb
-																					WHERE studiengang_typ = 'b'
-																					AND laststatus != 'Abgewiesener'
-																					AND prstb.person_id = prst.person_id )))
+				AND (
+					studiengang_typ = 'b'
+					OR
+					(
+						studiengang_typ = 'm'
+						AND
+						EXISTS (
+							SELECT 1 /* Master Studiengänge berücksichtigen wenn auch Bachelor im gleichen Semester */
+							FROM prst prstb
+							WHERE studiengang_typ = 'b'
+							AND laststatus != 'Abgewiesener'
+							AND prstb.person_id = prst.person_id
+						)
+					)
+				)
 				ORDER BY studiengang_kz, laststatus";
 
 		// Encode Params
@@ -863,12 +919,12 @@ class ReihungstestJob extends JOB_Controller
 				// Alle niedrigeren Prios laden
 				$qryNiedrPrios = "
 						SELECT DISTINCT
-							get_rolle_prestudent (tbl_prestudent.prestudent_id, '".$row_ps->studiensemester_kurzbz."') AS laststatus,
+							get_rolle_prestudent (tbl_prestudent.prestudent_id, '".$row_ps->studiensemester_kurzbz."') as laststatus,
 							tbl_studienplan.orgform_kurzbz,
 							tbl_person.nachname,
 							tbl_person.vorname,
 							tbl_prestudent.*,
-							tbl_studiengang.typ AS studiengang_typ
+							tbl_studiengang.typ as studiengang_typ
 						FROM PUBLIC.tbl_person
 							JOIN PUBLIC.tbl_prestudent USING (person_id)
 							JOIN PUBLIC.tbl_prestudentstatus USING (prestudent_id)
@@ -876,7 +932,10 @@ class ReihungstestJob extends JOB_Controller
 							JOIN PUBLIC.tbl_studiengang ON (tbl_prestudent.studiengang_kz = tbl_studiengang.studiengang_kz)
 						WHERE tbl_prestudent.person_id = ".$row_ps->person_id."
 							AND tbl_prestudent.prestudent_id != ".$row_ps->prestudent_id."
-							AND get_rolle_prestudent (tbl_prestudent.prestudent_id, '".$row_ps->studiensemester_kurzbz."') IN ('Aufgenommener','Bewerber','Wartender')
+							AND get_rolle_prestudent(
+								tbl_prestudent.prestudent_id,
+								'".$row_ps->studiensemester_kurzbz."'
+							) IN ('Aufgenommener','Bewerber','Wartender')
 							AND studiensemester_kurzbz = '".$row_ps->studiensemester_kurzbz."'
 							AND tbl_studiengang.typ IN ('b', 'm')
 							AND priorisierung > ".$row_ps->priorisierung."
@@ -1019,12 +1078,12 @@ class ReihungstestJob extends JOB_Controller
 		// Mails senden
 		if (!isEmptyArray($mailArray))
 		{
-			foreach ($mailArray AS $stg=>$orgform)
+			foreach ($mailArray as $stg => $orgform)
 			{
 				$studiengang = $this->StudiengangModel->load($stg);
 				$mailcontent = '';
 
-				foreach ($orgform AS $art=>$value)
+				foreach ($orgform as $art => $value)
 				{
 					// Orgform nur dazu schreiben, wenn es mehr als Eine gibt
 					if (count($orgform) > 1)
@@ -1034,12 +1093,12 @@ class ReihungstestJob extends JOB_Controller
 					if (isset($value['AbgewiesenGesetztWartender']) && !isEmptyArray($value['AbgewiesenGesetztWartender']))
 					{
 						$mailcontent .= '<p style="font-family: verdana, sans-serif;">
-									Folgende Personen auf der Warteliste wurden in einem höher priorisierten Studiengang aufgenommen und haben deshalb einen Status "Abgewiesen" erhalten:</p>';
+							Folgende Personen auf der Warteliste wurden in einem höher priorisierten Studiengang aufgenommen und haben deshalb einen Status "Abgewiesen" erhalten:
+						</p>';
 						$mailcontent .= '<table style="border-collapse: collapse; border: 1px solid grey;">';
-						//$mailcontent .= '<thead><th style="font-family: verdana, sans-serif; border: 1px solid grey; padding: 3px; text-align: left">Zuvor Warteliste</th></thead>';
 						$mailcontent .= '					<tbody>';
 						sort($value['AbgewiesenGesetztWartender']);
-						foreach ($value['AbgewiesenGesetztWartender'] AS $key=>$bewerber)
+						foreach ($value['AbgewiesenGesetztWartender'] as $key => $bewerber)
 						{
 							$mailcontent .= '<tr><td style="font-family: verdana, sans-serif; border: 1px solid grey; padding: 3px">'.$bewerber.'</td></tr>';
 						}
@@ -1050,10 +1109,9 @@ class ReihungstestJob extends JOB_Controller
 						$mailcontent .= '<p style="font-family: verdana, sans-serif;">
 									Folgende Aufgenommene wurden in einem höher priorisierten Studiengang aufgenommen:</p>';
 						$mailcontent .= '<table style="border-collapse: collapse; border: 1px solid grey;">';
-						//$mailcontent .= '<thead><th style="font-family: verdana, sans-serif; border: 1px solid grey; padding: 3px; text-align: left">Aufgenommene</th></thead>';
 						$mailcontent .= '					<tbody>';
 						sort($value['AufnahmeHoeherePrio']);
-						foreach ($value['AufnahmeHoeherePrio'] AS $key=>$bewerber)
+						foreach ($value['AufnahmeHoeherePrio'] as $key => $bewerber)
 						{
 							$mailcontent .= '<tr><td style="font-family: verdana, sans-serif; border: 1px solid grey; padding: 3px">'.$bewerber.'</td></tr>';
 						}
@@ -1066,7 +1124,7 @@ class ReihungstestJob extends JOB_Controller
 						$mailcontent .= '<table style="border-collapse: collapse; border: 1px solid grey;">';
 						$mailcontent .= '					<tbody>';
 						sort($value['AbgewiesenHoeherePrio']);
-						foreach ($value['AbgewiesenHoeherePrio'] AS $key=>$bewerber)
+						foreach ($value['AbgewiesenHoeherePrio'] as $key => $bewerber)
 						{
 							$mailcontent .= '<tr><td style="font-family: verdana, sans-serif; border: 1px solid grey; padding: 3px">'.$bewerber.'</td></tr>';
 						}
@@ -1077,10 +1135,9 @@ class ReihungstestJob extends JOB_Controller
 						$mailcontent .= '<p style="font-family: verdana, sans-serif;">
 										Folgende BewerberInnen wurden zu Abgewiesenen gemacht:</p>';
 						$mailcontent .= '<table style="border-collapse: collapse; border: 1px solid grey;">';
-						//$mailcontent .= '<thead><th style="font-family: verdana, sans-serif; border: 1px solid grey; padding: 3px; text-align: left">Aufgenommene</th></thead>';
 						$mailcontent .= '					<tbody>';
 						sort($value['AbgewiesenWeilBewerber']);
-						foreach ($value['AbgewiesenWeilBewerber'] AS $key => $bewerber)
+						foreach ($value['AbgewiesenWeilBewerber'] as $key => $bewerber)
 						{
 							$mailcontent .= '<tr><td style="font-family: verdana, sans-serif; border: 1px solid grey; padding: 3px">'.$bewerber.'</td></tr>';
 						}
@@ -1088,6 +1145,7 @@ class ReihungstestJob extends JOB_Controller
 					}
 				}
 
+				$mailcontent_data_arr = array();
 				$mailcontent_data_arr['table'] = $mailcontent;
 
 				// Send email in Sancho design
@@ -1102,7 +1160,8 @@ class ReihungstestJob extends JOB_Controller
 						'sancho_footer_min_bw.jpg',
 						$from,
 						'',
-						$bcc);
+						$bcc
+					);
 				}
 			}
 		}

application/controllers/system/infocenter/InfoCenter.php

@@ -109,8 +109,8 @@ class InfoCenter extends Auth_Controller
 	 * Constructor
 	 */
 	public function __construct()
-    {
-        parent::__construct(
+	{
+		parent::__construct(
 			array(
 				'index' => 'infocenter:r',
 				'freigegeben' => 'infocenter:r',
@@ -166,7 +166,6 @@ class InfoCenter extends Auth_Controller
 		$this->load->model('crm/ZGVPruefungStatus_model', 'ZGVPruefungStatusModel');
 		$this->load->model('person/Notiz_model', 'NotizModel');
 		$this->load->model('person/Person_model', 'PersonModel');
-		$this->load->model('system/Message_model', 'MessageModel');
 		$this->load->model('system/Filters_model', 'FiltersModel');
 		$this->load->model('system/PersonLock_model', 'PersonLockModel');
 		$this->load->model('organisation/Studiengang_model', 'StudiengangModel');
@@ -176,6 +175,7 @@ class InfoCenter extends Auth_Controller
 		$this->load->model('person/Kontakt_model', 'KontaktModel');
 		$this->load->model('person/Geschlecht_model', 'GeschlechtModel');
 		$this->load->model('person/adresse_model', 'AdresseModel');
+		$this->load->model('CL/Messages_model', 'CLMessagesModel');
 
 		// Loads libraries
 		$this->load->library('PersonLogLib');
@@ -199,7 +199,7 @@ class InfoCenter extends Auth_Controller
 		$this->load->library('VariableLib', array('uid' => $this->_uid));
 
 		$this->setControllerId(); // sets the controller id
-    }
+	}
 
 	// -----------------------------------------------------------------------------------------------------------------
 	// Public methods
@@ -407,7 +407,7 @@ class InfoCenter extends Auth_Controller
 						'saveformalgep',
 						array(
 							isEmptyString($akte->retval[0]->titel) ? $akte->retval[0]->bezeichnung : $akte->retval[0]->titel,
-							is_null($timestamp) ? 'NULL' : $timestamp
+							is_null($timestamp) ? 'null' : $timestamp
 						)
 					);
 				}
@@ -608,7 +608,8 @@ class InfoCenter extends Auth_Controller
 	/**
 	 * Sendet bei einer neuen ZGV Prüfung die Mail raus an den Studiengang
 	 */
-	private function sendZgvMail($mail, $typ, $person){
+	private function sendZgvMail($mail, $typ, $person)
+	{
 		$data = array(
 			'vorname' => $person->vorname,
 			'nachname' => $person->nachname,
@@ -692,7 +693,6 @@ class InfoCenter extends Auth_Controller
 				'openZgv' => $openZgv
 			)
 		);
-
 	}
 
 	/**
@@ -866,7 +866,7 @@ class InfoCenter extends Auth_Controller
 
 		$person_id = $logdata['person_id'];
 
-		$akteresult = $this->AkteModel->loadWhere(array('person_id' => $person_id, 'formal_geprueft_amum !=' => NULL));
+		$akteresult = $this->AkteModel->loadWhere(array('person_id' => $person_id, 'formal_geprueft_amum !=' => null));
 
 		if (hasData($lastStatus) && isSuccess($akteresult))
 		{
@@ -1085,8 +1085,23 @@ class InfoCenter extends Auth_Controller
 	 */
 	public function reloadMessages($person_id)
 	{
-		$messages = $this->MessageModel->getMessagesOfPerson($person_id, 1);
-		$this->load->view('system/infocenter/messageList.php', array('messages' => $messages->retval));
+		$messages = $this->CLMessagesModel->getReceivedAndSentMessages($person_id);
+
+		// If there are messages
+		if (hasData($messages))
+		{
+			$personexists = $this->PersonModel->load($person_id);
+
+			if (isError($personexists)) show_error(getError($personexists));
+
+			if (!hasData($personexists)) show_error('Person does not exist!');
+
+			$this->load->view('system/infocenter/messageList.php', getData($messages));
+		}
+		elseif (isError($messages)) // Otherwise if an error occurred
+		{
+			show_error(getError($messages));
+		}
 	}
 
 	/**
@@ -1228,7 +1243,9 @@ class InfoCenter extends Auth_Controller
 		$person_id = $this->input->post('person_id');
 		$date = $this->input->post('onholddate');
 
-		$result = $this->personloglib->setOnHold($person_id, date_format(date_create($date), 'Y-m-d'), self::TAETIGKEIT, self::APP, null, $this->_uid);
+		$result = $this->personloglib->setOnHold(
+			$person_id, date_format(date_create($date), 'Y-m-d'), self::TAETIGKEIT, self::APP, null, $this->_uid
+		);
 
 		$this->outputJson($result);
 	}
@@ -1439,7 +1456,6 @@ class InfoCenter extends Auth_Controller
 					if (isError($update))
 						$this->terminateWithJsonError($this->p->t('ui', 'fehlerBeimSpeichern'));
 				}
-
 			}
 		}
 
@@ -1449,7 +1465,7 @@ class InfoCenter extends Auth_Controller
 	public function saveNachreichung($person_id)
 	{
 		$nachreichungAm = $this->input->post('nachreichungAm');
-		$nachreichungAnmerkung = empty($this->input->post('nachreichungAnmerkung')) ? NULL : $this->input->post('nachreichungAnmerkung');
+		$nachreichungAnmerkung = empty($this->input->post('nachreichungAnmerkung')) ? null : $this->input->post('nachreichungAnmerkung');
 		$typ = $this->input->post('typ');
 
 		$allowedTypes = [
@@ -1506,14 +1522,14 @@ class InfoCenter extends Auth_Controller
 				array(
 					'dokument_kurzbz' => $allowedTypes[$typ],
 					'person_id' => $person_id,
-					'erstelltam' => NULL,
+					'erstelltam' => null,
 					'gedruckt' => false,
 					'anmerkung' => $nachreichungAnmerkung,
 					'updateamum' => $today,
 					'updatevon' => get_uid(),
 					'insertamum' => $today,
 					'insertvon' => get_uid(),
-					'uid' => NULL,
+					'uid' => null,
 					'nachgereicht' => true,
 					'nachgereicht_am' => $nachreichungAm
 				)
@@ -1522,6 +1538,78 @@ class InfoCenter extends Auth_Controller
 
 		$this->outputJsonSuccess("Done!");
 	}
+
+	/**
+	 * One day I'll be given some nice comment
+	 */
+	public function getAbsageData()
+	{
+		$stg_typ = $this->getStudienArtBerechtigung(['b', 'm']);
+
+		if (!is_null($stg_typ))
+		{
+			$statusgruende = $this->StatusgrundModel->getStatus(self::ABGEWIESENERSTATUS, true)->retval;
+			$studienSemester = $this->variablelib->getVar('infocenter_studiensemester');
+			$studiengaenge = $this->StudiengangModel->getStudiengaengeWithOrgForm(array_column($stg_typ, 'typ'), $studienSemester);
+
+			$data = array (
+				'statusgruende' => $statusgruende,
+				'studiengaenge' => $studiengaenge->retval
+			);
+
+			$this->outputJsonSuccess($data);
+		}
+		else
+			$this->outputJsonSuccess(null);
+	}
+
+	/**
+	 * One day I'll be given some nice comment
+	 */
+	public function getStudienArtBerechtigung($typ = null)
+	{
+		$studiengang_kz_all = $this->permissionlib->getSTG_isEntitledFor('infocenter');
+		$stg_typ = $this->StudiengangModel->getStudiengangTyp($studiengang_kz_all, $typ);
+		return getData($stg_typ);
+	}
+
+	/**
+	 * One day I'll be given some nice comment
+	 */
+	public function getStudienartData()
+	{
+		$this->outputJsonSuccess($this->getStudienArtBerechtigung(['b', 'm', 'l']));
+	}
+
+	/**
+	 * One day I'll be given some nice comment
+	 */
+	public function saveAbsageForAll()
+	{
+		$statusgrund = $this->input->post('statusgrund');
+		$studiengang = $this->input->post('studiengang');
+		$abgeschickt = $this->input->post('abgeschickt');
+		$personen = $this->input->post('personen');
+		$studienSemester = $this->variablelib->getVar('infocenter_studiensemester');
+
+		if ($statusgrund === 'null' || $studiengang === 'null' || $abgeschickt === 'null' || empty($personen))
+			$this->terminateWithJsonError("Bitte füllen Sie alle Felder aus");
+
+		foreach($personen as $person)
+		{
+			$prestudent = $this->PrestudentModel->getPrestudentByStudiengangAndPerson($studiengang, $person, $studienSemester, $abgeschickt);
+
+			if (!hasData($prestudent))
+				continue;
+
+			$prestudentData = getData($prestudent);
+
+			$this->saveAbsage($prestudentData[0]->prestudent_id, $statusgrund);
+		}
+
+		$this->outputJsonSuccess("Success");
+	}
+
 	// -----------------------------------------------------------------------------------------------------------------
 	// Private methods
 
@@ -1934,7 +2022,7 @@ class InfoCenter extends Auth_Controller
 			show_error(getError($dokumente_nachgereicht));
 		}
 
-		$messages = $this->MessageModel->getMessagesOfPerson($person_id, 1);
+		$messages = $this->CLMessagesModel->getReceivedAndSentMessages($person_id);
 
 		if (isError($messages))
 		{
@@ -1967,13 +2055,13 @@ class InfoCenter extends Auth_Controller
 		$data = array (
 			'lockedby' => $lockedby,
 			'lockedbyother' => $lockedbyother,
-			'stammdaten' => $stammdaten->retval,
-			'dokumente' => $dokumente->retval,
-			'dokumente_nachgereicht' => $dokumente_nachgereicht->retval,
-			'messages' => $messages->retval,
+			'stammdaten' => getData($stammdaten),
+			'dokumente' => getData($dokumente),
+			'dokumente_nachgereicht' => getData($dokumente_nachgereicht),
+			'messages' => getData($messages) == null ? array() : getData($messages),
 			'logs' => $logs,
-			'notizen' => $notizen->retval,
-			'notizenbewerbung' => $notizen_bewerbung->retval
+			'notizen' => getData($notizen),
+			'notizenbewerbung' => getData($notizen_bewerbung)
 		);
 
 		return $data;
@@ -2011,15 +2099,21 @@ class InfoCenter extends Auth_Controller
 				//get orgform for german and english
 				if (isset($zgvpruefung->prestudentstatus->bezeichnung_orgform) && is_array($zgvpruefung->prestudentstatus->bezeichnung_orgform))
 				{
-					$zgvpruefung->prestudentstatus->bezeichnung_orgform_german = getPhraseByLanguage($zgvpruefung->prestudentstatus->bezeichnung_orgform, 'German');
-					$zgvpruefung->prestudentstatus->bezeichnung_orgform_english = getPhraseByLanguage($zgvpruefung->prestudentstatus->bezeichnung_orgform, 'English');
+					$zgvpruefung->prestudentstatus->bezeichnung_orgform_german = getPhraseByLanguage(
+						$zgvpruefung->prestudentstatus->bezeichnung_orgform, 'German'
+					);
+					$zgvpruefung->prestudentstatus->bezeichnung_orgform_english = getPhraseByLanguage(
+						$zgvpruefung->prestudentstatus->bezeichnung_orgform, 'English'
+					);
 				}
 
 				$position = strpos($zgvpruefung->prestudentstatus->anmerkung, 'Alt:');
 
 				//parse Anmerkung for Alternative (Prio is given in orgform and sprache anyway)
-				$zgvpruefung->prestudentstatus->alternative = is_numeric($position) ? substr($zgvpruefung->prestudentstatus->anmerkung, $position) : null;
+				$zgvpruefung->prestudentstatus->alternative = is_numeric($position) ?
+					substr($zgvpruefung->prestudentstatus->anmerkung, $position) : null;
 			}
+
 			//if prestudent is not interessent or is already bestaetigt, then show only as information, non-editable
 			$zgvpruefung->infoonly = !isset($zgvpruefung->prestudentstatus)
 				|| isset($zgvpruefung->prestudentstatus->bestaetigtam)
@@ -2030,15 +2124,21 @@ class InfoCenter extends Auth_Controller
 			//wether prestudent was freigegeben for RT/Stg
 			$zgvpruefung->isRtFreigegeben = false;
 			$zgvpruefung->isStgFreigegeben = false;
-			$zgvpruefung->sendStgFreigabeMsg = true;//wether Stgudiengangfreigabemessage can be sent (for "exceptions", Studiengänge with no message sending)
+			// Wether Stgudiengangfreigabemessage can be sent (for "exceptions", Studiengänge with no message sending)
+			$zgvpruefung->sendStgFreigabeMsg = true;
 
 			$isFreigegeben = null;
 			if (isset($zgvpruefung->prestudentstatus->studiensemester_kurzbz))
 			{
-				$this->PrestudentstatusModel->addSelect('bestaetigtam, statusgrund_id, tbl_status_grund.bezeichnung_mehrsprachig AS bezeichnung_statusgrund');
+				$this->PrestudentstatusModel->addSelect(
+					'bestaetigtam, statusgrund_id, tbl_status_grund.bezeichnung_mehrsprachig AS bezeichnung_statusgrund'
+				);
 				$this->PrestudentstatusModel->addJoin('public.tbl_status_grund', 'statusgrund_id', 'LEFT');
-				$isFreigegeben = $this->PrestudentstatusModel->loadWhere(array('studiensemester_kurzbz' => $zgvpruefung->prestudentstatus->studiensemester_kurzbz,
-															  'tbl_prestudentstatus.status_kurzbz' => self::INTERESSENTSTATUS, 'prestudent_id' => $prestudent->prestudent_id));
+				$isFreigegeben = $this->PrestudentstatusModel->loadWhere(array(
+					'studiensemester_kurzbz' => $zgvpruefung->prestudentstatus->studiensemester_kurzbz,
+					'tbl_prestudentstatus.status_kurzbz' => self::INTERESSENTSTATUS,
+					'prestudent_id' => $prestudent->prestudent_id)
+				);
 			}
 
 			if (hasData($isFreigegeben))
@@ -2047,11 +2147,14 @@ class InfoCenter extends Auth_Controller
 				{
 					if (isset($prestudentstatus->bestaetigtam))
 					{
-						//if statusgrund set - freigegeben for Studiengang, otherwise freigegeben for RT
+						// If statusgrund set - freigegeben for Studiengang, otherwise freigegeben for RT
 						if (isset($prestudentstatus->statusgrund_id))
 						{
 							if (isset($prestudentstatus->bezeichnung_statusgrund[0])
-								&& in_array($prestudentstatus->bezeichnung_statusgrund[0], $this->_statusgruendeNoStgFreigabeMessage))
+								&& in_array(
+									$prestudentstatus->bezeichnung_statusgrund[0],
+									$this->_statusgruendeNoStgFreigabeMessage)
+							)
 								$zgvpruefung->sendStgFreigabeMsg = false;
 							else
 								$zgvpruefung->isStgFreigegeben = true;
@@ -2061,43 +2164,52 @@ class InfoCenter extends Auth_Controller
 							$zgvpruefung->isRtFreigegeben = true;
 					}
 				}
-            }
+			}
 
-            //application priority change possible?
-            $zgvpruefung->changeup = false;
-            $zgvpruefung->changedown = false;
-            $zgvpruefung->hasBewerber = false;
-
-            if (isset($zgvpruefung->prestudentstatus->status_kurzbz) && $zgvpruefung->prestudentstatus->status_kurzbz == self::INTERESSENTSTATUS)
-            {
-                if (isset($zgvpruefung->prestudentstatus->studiensemester_kurzbz))
-                {
-                    $studiensemester = $zgvpruefung->prestudentstatus->studiensemester_kurzbz;
-                    //show warning if there is already another bewerber (RT result already exists)
-                    $bewerber = $this->PersonModel->hasBewerber($person_id, $studiensemester, 'b');
-
-                    if (hasData($bewerber))
-                    {
-                        $bewerbercnt = getData($bewerber);
-
-                        if (is_numeric($bewerbercnt[0]->anzahl_bewerber) && $bewerbercnt[0]->anzahl_bewerber > 0)
-                        {
-                            $zgvpruefung->hasBewerber = true;
-                        }
-                    }
-
-                    $zgvpruefung->changeup = $this->PrestudentModel->checkPrioChange($zgvpruefung->prestudent_id, $studiensemester, -1);
-                    $zgvpruefung->changedown = $this->PrestudentModel->checkPrioChange($zgvpruefung->prestudent_id, $studiensemester, 1);
-                }
-            }
+			// Application priority change possible?
+			$zgvpruefung->changeup = false;
+			$zgvpruefung->changedown = false;
+			$zgvpruefung->hasBewerber = false;
+			
+			if (isset($zgvpruefung->prestudentstatus->status_kurzbz)
+				&& $zgvpruefung->prestudentstatus->status_kurzbz == self::INTERESSENTSTATUS)
+			{
+				if (isset($zgvpruefung->prestudentstatus->studiensemester_kurzbz))
+				{
+					$studiensemester = $zgvpruefung->prestudentstatus->studiensemester_kurzbz;
+					//show warning if there is already another bewerber (RT result already exists)
+					$bewerber = $this->PersonModel->hasBewerber($person_id, $studiensemester, 'b');
+					
+					if (hasData($bewerber))
+					{
+						$bewerbercnt = getData($bewerber);
+						
+						if (is_numeric($bewerbercnt[0]->anzahl_bewerber) && $bewerbercnt[0]->anzahl_bewerber > 0)
+						{
+							$zgvpruefung->hasBewerber = true;
+						}
+					}
+					
+					$zgvpruefung->changeup = $this->PrestudentModel->checkPrioChange(
+						$zgvpruefung->prestudent_id, $studiensemester, -1
+					);
+					$zgvpruefung->changedown = $this->PrestudentModel->checkPrioChange(
+						$zgvpruefung->prestudent_id, $studiensemester, 1
+					);
+				}
+			}
 			$zgvExist = $this->ZGVPruefungModel->loadWhere(array('prestudent_id' => $zgvpruefung->prestudent_id));
 
-            if (isSuccess($zgvExist) && hasData($zgvExist))
+			if (isSuccess($zgvExist) && hasData($zgvExist))
 			{
 				$this->ZGVPruefungStatusModel->addOrder('datum', 'DESC');
 				$this->ZGVPruefungStatusModel->addLimit(1);
 
-				$statusZGV = $this->ZGVPruefungStatusModel->loadWhere(array('zgvpruefung_id' => $zgvExist->retval[0]->zgvpruefung_id));
+				$statusZGV = $this->ZGVPruefungStatusModel->loadWhere(
+					array(
+						'zgvpruefung_id' => $zgvExist->retval[0]->zgvpruefung_id
+					)
+				);
 
 				if (isSuccess($statusZGV) && hasData($statusZGV))
 					$zgvpruefung->statusZGV = $statusZGV->retval[0]->status;
@@ -2128,7 +2240,7 @@ class InfoCenter extends Auth_Controller
 		$this->GeschlechtModel->addOrder('sort');
 		$allGenders = getData($this->GeschlechtModel->load());
 
-		$data = array (
+		$data = array(
 			'zgvpruefungen' => $zgvpruefungen,
 			'abwstatusgruende' => $abwstatusgruende,
 			'intstatusgruende' => $intstatusgruende,
@@ -2151,7 +2263,7 @@ class InfoCenter extends Auth_Controller
 	{
 		$this->load->model('organisation/studiensemester_model', 'StudiensemesterModel');
 
-		@usort($zgvpruefungen, function ($a, $b) {
+		@usort($zgvpruefungen, function($a, $b) {
 			//sort:
 			// 1: Studiensemester
 			if (isset($a->prestudentstatus->studiensemester_kurzbz) || isset($b->prestudentstatus->studiensemester_kurzbz))
@@ -2252,7 +2364,13 @@ class InfoCenter extends Auth_Controller
 		$studiengang_mail = $prestudentdata->studiengangmail;
 		$studiengang_typ = $prestudentdata->studiengangtyp;
 
-		return array('person_id' => $person_id, 'studiengang_kurzbz' => $studiengang_kurzbz, 'studiengang_bezeichnung' => $studiengang_bezeichnung, 'studiengang_mail' => $studiengang_mail, 'studiengang_typ' => $studiengang_typ);
+		return array(
+			'person_id' => $person_id,
+			'studiengang_kurzbz' => $studiengang_kurzbz,
+			'studiengang_bezeichnung' => $studiengang_bezeichnung,
+			'studiengang_mail' => $studiengang_mail,
+			'studiengang_typ' => $studiengang_typ
+		);
 	}
 
 	/**
@@ -2328,8 +2446,13 @@ class InfoCenter extends Auth_Controller
 		foreach ($dokumenteNachzureichen as $dokument)
 		{
 			$anmerkung = !isEmptyString($dokument->anmerkung) ? ' | Anmerkung: '.$dokument->anmerkung : '';
-			$nachgereichtam = !isEmptyString($dokument->nachgereicht_am) ? ' | wird nachgereicht bis '.date_format(date_create($dokument->nachgereicht_am), 'd.m.Y') : '';
-			$dokumenteNachzureichenMail[] = array('dokument_bezeichnung' => $dokument->dokument_bezeichnung, 'anmerkung' => $anmerkung, 'nachgereicht_am' => $nachgereichtam);
+			$nachgereichtam = !isEmptyString($dokument->nachgereicht_am) ?
+				' | wird nachgereicht bis '.date_format(date_create($dokument->nachgereicht_am), 'd.m.Y') : '';
+			$dokumenteNachzureichenMail[] = array(
+				'dokument_bezeichnung' => $dokument->dokument_bezeichnung,
+				'anmerkung' => $anmerkung,
+				'nachgereicht_am' => $nachgereichtam
+			);
 		}
 
 		$notizenBewerbung = $this->NotizModel->getNotizByTitel($person_id, 'Anmerkung zur Bewerbung')->retval;
@@ -2358,11 +2481,6 @@ class InfoCenter extends Auth_Controller
 			'nachname' => $person->nachname,
 			'prestudentid' => $prestudent_id,
 			'statusgrund' => $statusgrund,
-			/*'zgvbez' => $prestudent->zgv_bez,
-			'zgvort' => $zgvort,
-			'zgvdatum' => $zgvdatum,
-			'zgvnation' => $zgvnation,
-			*/
 			'notizentext' => $notizentext,
 			'dokumente' => $dokumenteMail,
 			'dokumente_nachgereicht' => $dokumenteNachzureichenMail,
@@ -2372,7 +2490,11 @@ class InfoCenter extends Auth_Controller
 		$this->load->library('LogLib');
 		$this->load->helper('hlp_sancho');
 
-		$subject = ($person->geschlecht == 'm' ? 'Interessent ' : 'Interessentin ').$person->vorname.' '.$person->nachname.' für '.$prestudentstatus->studiengangbezeichnung.$orgform.' freigegeben';
+		$subject = ($person->geschlecht == 'm' ? 'Interessent ' : 'Interessentin ').
+			$person->vorname.' '.
+			$person->nachname.' für '.
+			$prestudentstatus->studiengangbezeichnung.
+			$orgform.' freigegeben';
 
 		$receiver = $prestudent->studiengangmail;
 
@@ -2393,63 +2515,5 @@ class InfoCenter extends Auth_Controller
 			$this->loglib->logError('Studiengang has no mail for sending Freigabe mail');
 		}
 	}
-
-	public function getAbsageData()
-	{
-		$stg_typ = $this->getStudienArtBerechtigung(['b', 'm']);
-
-		if (!is_null($stg_typ))
-		{
-			$statusgruende = $this->StatusgrundModel->getStatus(self::ABGEWIESENERSTATUS, true)->retval;
-			$studienSemester = $this->variablelib->getVar('infocenter_studiensemester');
-			$studiengaenge = $this->StudiengangModel->getStudiengaengeWithOrgForm(array_column($stg_typ, 'typ'), $studienSemester);
-
-			$data = array (
-				'statusgruende' => $statusgruende,
-				'studiengaenge' => $studiengaenge->retval
-			);
-
-			$this->outputJsonSuccess($data);
-		}
-		else
-			$this->outputJsonSuccess(null);
-	}
-
-	public function getStudienArtBerechtigung($typ = null)
-	{
-		$studiengang_kz_all = $this->permissionlib->getSTG_isEntitledFor('infocenter');
-		$stg_typ = $this->StudiengangModel->getStudiengangTyp($studiengang_kz_all, $typ);
-		return getData($stg_typ);
-	}
-
-	public function getStudienartData()
-	{
-		$this->outputJsonSuccess($this->getStudienArtBerechtigung(['b', 'm', 'l']));
-	}
-
-	public function saveAbsageForAll()
-	{
-		$statusgrund = $this->input->post('statusgrund');
-		$studiengang = $this->input->post('studiengang');
-		$abgeschickt = $this->input->post('abgeschickt');
-		$personen = $this->input->post('personen');
-		$studienSemester = $this->variablelib->getVar('infocenter_studiensemester');
-
-		if ($statusgrund === 'null' || $studiengang === 'null' || $abgeschickt === 'null' || empty($personen))
-			$this->terminateWithJsonError("Bitte füllen Sie alle Felder aus");
-
-		foreach($personen as $person)
-		{
-			$prestudent = $this->PrestudentModel->getPrestudentByStudiengangAndPerson($studiengang, $person, $studienSemester, $abgeschickt);
-
-			if (!hasData($prestudent))
-				continue;
-
-			$prestudentData = getData($prestudent);
-
-			$this->saveAbsage($prestudentData[0]->prestudent_id, $statusgrund);
-		}
-
-		$this->outputJsonSuccess("Success");
-	}
 }
+

application/models/CL/Messages_model.php

@@ -1,4 +1,23 @@
 <?php
+/**
+ * Copyright (C) 2023 fhcomplete.org
+ *
+ * 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 3 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, see <https://www.gnu.org/licenses/>.
+ */
+
+use \stdClass as stdClass;
+use \DateTime as DateTime;
 
 /**
  * Messages GUI logic
@@ -27,6 +46,7 @@ class Messages_model extends CI_Model
 	const TYPE_PRESTUDENTS = 'prestudents';
 
 	const ALT_OE = 'infocenter'; // alternative organisation unit when no one is found for a presetudent
+	const SYSTEM_SENDER_NAME = 'System sender';
 
 	/**
 	 * Constructor
@@ -50,7 +70,11 @@ class Messages_model extends CI_Model
 		$this->load->model('crm/Prestudent_model', 'PrestudentModel');
 		// Loads model Benutzer_model
 		$this->load->model('person/Benutzer_model', 'BenutzerModel');
-		
+		// Loads model Studiengang_model
+		$this->load->model('organisation/Studiengang_model', 'StudiengangModel');
+
+		// Loads model Organisationseinheitsmodel
+		$this->load->model('organisation/Organisationseinheit_model', 'OrganisationseinheitModel');
 	}
 
 	//------------------------------------------------------------------------------------------------------------------
@@ -201,12 +225,23 @@ class Messages_model extends CI_Model
 				$jsonRecord->message_id = $receivedMessage->message_id;
 				$jsonRecord->subject = $receivedMessage->subject;
 				$jsonRecord->body = $receivedMessage->body;
-				$jsonRecord->from = $receivedMessage->vorname.' '.$receivedMessage->nachname;
 				$sentDate = new DateTime($receivedMessage->sent);
 				$jsonRecord->sent = $sentDate->format('d/m/Y H:i:s');
-				$jsonRecord->status = $receivedMessage->status;
-				$jsonRecord->statusPersonId = $receivedMessage->statuspersonid;
+				$jsonRecord->status = $receivedMessage->lastStatus;
+				$jsonRecord->statusPersonId = $receivedMessage->senderPersonId;
 				$jsonRecord->token = $receivedMessage->token;
+				$jsonRecord->from = self::SYSTEM_SENDER_NAME; // default fallback
+
+				// If the sender id is the system sender then use the organisation unit
+				if ($receivedMessage->senderPersonId == $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID))
+				{
+					// If the oe exists
+					if (!isEmptyString($receivedMessage->oe)) $jsonRecord->from = $receivedMessage->oe;
+				}
+				else // otherwise use the name and surname of the person sender
+				{
+					$jsonRecord->from = $receivedMessage->senderName.' '.$receivedMessage->senderSurname;
+				}
 
 				$jsonArray[] = $jsonRecord;
 			}
@@ -240,17 +275,20 @@ class Messages_model extends CI_Model
 				$jsonRecord->body = $sentMessage->body;
 				$sentDate = new DateTime($sentMessage->sent);
 				$jsonRecord->sent = $sentDate->format('d/m/Y H:i:s');
-				$jsonRecord->status = $sentMessage->status;
-				$jsonRecord->statusPersonId = $sentMessage->statuspersonid;
+				$jsonRecord->status = $sentMessage->lastStatus;
+				$jsonRecord->statusPersonId = $sentMessage->senderPersonId;
 				$jsonRecord->token = $sentMessage->token;
+				$jsonRecord->to = self::SYSTEM_SENDER_NAME; // default fallback
 
-				if ($sentMessage->person_id == $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID))
+				// If the recipient id is the system sender then use the organisation unit
+				if ($sentMessage->recipientPersonId == $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID))
 				{
-					$jsonRecord->to = $sentMessage->oe;
+					// If the oe exists
+					if (!isEmptyString($sentMessage->oe)) $jsonRecord->to = $sentMessage->oe;
 				}
-				else
+				else // otherwise use the name and surname of the person recipient
 				{
-					$jsonRecord->to = $sentMessage->vorname.' '.$sentMessage->nachname;
+					$jsonRecord->to = $sentMessage->recipientName.' '.$sentMessage->recipientSurname;
 				}
 
 				$jsonArray[] = $jsonRecord;
@@ -280,18 +318,42 @@ class Messages_model extends CI_Model
 		$srmsbtResult = $this->MessageTokenModel->setReadMessageStatusByToken($token);
 		if (isError($srmsbtResult)) show_error(getError($srmsbtResult));
 
-		// Retrieves message sender information
-		$senderResult = $this->MessageTokenModel->getSenderData($message->sender_id);
-		if (isError($senderResult)) show_error(getError($senderResult));
-		if (!hasData($senderResult)) show_error('No sender information found');
+		// Sender name
+		$sender = self::SYSTEM_SENDER_NAME; // default fallback
 
-		$sender = getData($senderResult)[0]; // Found sender data
+		// If the sender is a person
+		if ($message->sender_id != $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID))
+		{
+			// Retrieves message sender information
+			$senderResult = $this->MessageTokenModel->getSenderData($message->sender_id);
+			if (isError($senderResult)) show_error(getError($senderResult));
+			if (!hasData($senderResult)) show_error('No sender information found');
 
-		// If the sender is not the system sender and the receiver is not the system sender
-		// and are present configurations to reply
+			$sender = getData($senderResult)[0]->vorname.' '.getData($senderResult)[0]->nachname;
+		}
+		else // otherwise if the sender is an organization unit (degree program)
+		{
+			// Looks into the degree programs
+			$ouResult = $this->StudiengangModel->loadWhere(array('oe_kurzbz' => $message->oe_kurzbz));
+
+			if (isError($ouResult)) show_error(getError($ouResult));
+
+			// If not found here
+			if (!hasData($ouResult))
+			{
+				// Then looks into the organisation units
+				$ouResult = $this->OrganisationseinheitModel->loadWhere(array('oe_kurzbz' => $message->oe_kurzbz));
+
+				if (isError($ouResult)) show_error(getError($ouResult));
+			}
+
+			// If found then set it, otherwise the fallback is used
+			if (hasData($ouResult)) $sender = getData($ouResult)[0]->bezeichnung;
+		}
+
+		// If the sender is not the system sender and configurations to reply exist
 		$hrefReply = '';
 		if ($message->sender_id != $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID)
-			&& $message->receiver_id != $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID)
 			&& !isEmptyString($this->config->item(MessageLib::CFG_REDIRECT_VIEW_MESSAGE_URL)))
 		{
 			$hrefReply = $this->config->item(MessageLib::CFG_MESSAGE_SERVER).
@@ -407,7 +469,7 @@ class Messages_model extends CI_Model
 		{
 			// Merge receivers data with logged in user data
 			$msgVarsDataArray = $this->_addMsgVarsDataOfLoggedInUser($receiver);
-	
+
 			$msgVarsDataArray = $this->_lowerReplaceSpaceArrayKeys((array)getData($msgVarsDataArray)[0]); // replaces array keys
 			$parsedSubject = parseText($subject, $msgVarsDataArray);
 			$parsedBody = parseText($body, $msgVarsDataArray);
@@ -446,7 +508,7 @@ class Messages_model extends CI_Model
 
 		return success('Messages sent successfully');
 	}
-	
+
 	/**
 	 * Wrapper method for sendExplicitTemplateSenderId
 	 * The sender id is retrieved from the authentication session, if not present an error would be raised
@@ -459,7 +521,7 @@ class Messages_model extends CI_Model
 
 		return $this->sendExplicitTemplateSenderId($sender_id, $prestudents, $oe_kurzbz, $vorlage_kurzbz, $msgVars);
 	}
-	
+
 	/**
 	 * Sends a new message using the given template and information present in parameter prestudents
 	 * Extra variables can be added using parameter $msgVars
@@ -472,28 +534,28 @@ class Messages_model extends CI_Model
 		if (!hasData($msgVarsData)) show_error('No recipients were given');
 
 		$prestudentsData = $this->PrestudentModel->getOrganisationunits($prestudents);
-		
+
 		// Get the senders uid (if user is an active employee)
 		$this->BenutzerModel->addSelect('uid');
 		$this->BenutzerModel->addJoin('public.tbl_mitarbeiter ma', 'ma.mitarbeiter_uid = uid');
-		if (!$result = getData($this->BenutzerModel->getFromPersonId($sender_id)))
-		{
-			show_error('No sender_uid found');
-		}
-		$sender_uid = $result[0]->uid;
+		$benutzerResult = $this->BenutzerModel->getFromPersonId($sender_id);
+		if (isError($benutzerResult)) show_error(getError($benutzerResult));
+		if (!hasData($benutzerResult)) show_error('No sender uid found');
+
+		$sender_uid = getData($benutzerResult)[0]->uid;
 
 		// Adds the organisation unit to each prestudent
 		if (isEmptyString($oe_kurzbz) && hasData($msgVarsData) && hasData($prestudentsData))
 		{
-			$this->CLMessagesModel->_addOeToPrestudents($msgVarsData, $prestudentsData);
+			$this->_addOeToPrestudents($msgVarsData, $prestudentsData);
 		}
 
 		foreach (getData($msgVarsData) as $receiver)
 		{
 			/**
 			 * Merge receivers data with senders data
-			 * NOTE: _addMsgVarsDataOfLoggedInUser usually retrieves data of the logged in user that is set in the 
-			 * templates user fields. As sendExplicitTemplateSenderId is run by a job, a sender uid is passed to be used 
+			 * NOTE: _addMsgVarsDataOfLoggedInUser usually retrieves data of the logged in user that is set in the
+			 * templates user fields. As sendExplicitTemplateSenderId is run by a job, a sender uid is passed to be used
 			 * instead the logged in user.
 			 */
 			$msgVarsDataArray = $this->_addMsgVarsDataOfLoggedInUser($receiver, $sender_uid);
@@ -540,8 +602,6 @@ class Messages_model extends CI_Model
 		if (isError($senderResult)) show_error(getError($senderResult));
 		if (!hasData($senderResult)) show_error('No sender information found');
 
-		$sender = getData($senderResult)[0]; // Found sender data
-
 		$messageResult = $this->MessageTokenModel->getMessageByToken($token);
 		if (isError($messageResult)) show_error(getError($messageResult));
 		// Security check! It is possible to reply only to a received message!!
@@ -636,7 +696,7 @@ class Messages_model extends CI_Model
 		$parseMessageText = error('The given person_id is not a valid number');
 
 		if (is_numeric($person_id)) $parseMessageText = $this->MessageModel->getMsgVarsDataByPersonId($person_id);
-		
+
 		// Add message vars data of the logged in user
 		$parseMessageText = $this->_addMsgVarsDataOfLoggedInUser($parseMessageText);
 
@@ -662,10 +722,10 @@ class Messages_model extends CI_Model
 		$parseMessageText = error('The given prestudent_id is not a valid number');
 
 		if (is_numeric($prestudent_id)) $parseMessageText = $this->MessageModel->getMsgVarsDataByPrestudentId($prestudent_id);
-		
+
 		// Add message vars data of the logged in user
 		$parseMessageText = $this->_addMsgVarsDataOfLoggedInUser($parseMessageText);
-		
+
 		if (hasData($parseMessageText))
 		{
 			$parseMessageText = success(
@@ -701,6 +761,17 @@ class Messages_model extends CI_Model
 		}
 	}
 
+	/**
+	 * Returns all the received and sent messages for the given person
+	 */
+	public function getReceivedAndSentMessages($person_id)
+	{
+		return $this->RecipientModel->getReceivedAndSentMessages(
+			$person_id,
+			$this->config->item(MessageLib::CFG_OU_RECEIVERS)
+		);
+	}
+
 	//------------------------------------------------------------------------------------------------------------------
 	// Private methods
 
@@ -771,7 +842,10 @@ class Messages_model extends CI_Model
 	{
 		return sprintf(
 			self::REPLY_BODY_FORMAT,
-			date_format(date_create($sentDate), 'd.m.Y H:i'), $receiverName, $receiverSurname, $body
+			date_format(date_create($sentDate), 'd.m.Y H:i'),
+			$receiverName,
+			$receiverSurname,
+			$body
 		);
 	}
 
@@ -875,14 +949,14 @@ class Messages_model extends CI_Model
 
 			$variables[] = $tmpVar;
 		}
-		
+
 		// ---------------------------------------------------------------------------------------
 		// Retrieves message vars of logged in user from database view vw_msg_vars_person
 		$result = null;
-		
+
 		// If data contains a prestudent id
 		$result = $this->messagelib->getMessageVarsLoggedInUser();
-		
+
 		if (isError($result)) show_error(getError($result));
 
 		// Then builds an array that contains objects with field name and field description of logged in user data
@@ -892,7 +966,7 @@ class Messages_model extends CI_Model
 			$obj = new stdClass();
 			$obj->id = $id;
 			$obj->description = $description;
-			
+
 			$user_fields[] = $obj;
 		}
 
@@ -924,30 +998,32 @@ class Messages_model extends CI_Model
 			'type' => $type
 		);
 	}
-	
+
 	/**
 	 * Adds message vars data of the logged in user to the given object (that should also have message vars data)
 	 * @param object $otherMsgVarsDataObj Can be success object or simple object.
 	 * @return object Returns success object.
 	 */
-	public function _addMsgVarsDataOfLoggedInUser($otherMsgVarsDataObj, $uid = null)
+	private function _addMsgVarsDataOfLoggedInUser($otherMsgVarsDataObj, $uid = null)
 	{
 		// First check if param type is object
 		if (!is_object($otherMsgVarsDataObj)) show_error('Must pass an object to merge with data of logged in user');
-		
+
 		// If it is a return object, extract the simple data object
-		if (isSuccess($otherMsgVarsDataObj))
-		{
-			$otherMsgVarsDataObj = getData($otherMsgVarsDataObj)[0];
-		}
-		
+		if (isSuccess($otherMsgVarsDataObj)) $otherMsgVarsDataObj = getData($otherMsgVarsDataObj)[0];
+
 		// Retrieve message vars data of the logged in user
-		if (!$msgVarsDataLoggedInUser = getData($this->MessageModel->getMsgVarsDataByLoggedInUser($uid))[0])
-		{
-			return success($otherMsgVarsDataObj);   // If failed, return at least given object as expected success object
-		}
-		
-		return success(array((object)(array_merge((array) $otherMsgVarsDataObj, (array) $msgVarsDataLoggedInUser))));
-		
+		$msgVarsDataLoggedInUserResult = $this->MessageModel->getMsgVarsDataByLoggedInUser($uid);
+		if (isError($msgVarsDataLoggedInUserResult)) show_error(getError($msgVarsDataLoggedInUserResult));
+
+		// If no data have been found return only the given otherMsgVarsDataObj
+		if (!hasData($msgVarsDataLoggedInUserResult)) return success(array($otherMsgVarsDataObj));
+
+		// Otherwise get them...
+		$msgVarsDataLoggedInUser = getData($this->MessageModel->getMsgVarsDataByLoggedInUser($uid))[0];
+
+		// ...and return them merged with the otherMsgVarsDataObj
+		return success(array((object)(array_merge((array)$otherMsgVarsDataObj, (array)$msgVarsDataLoggedInUser))));
 	}
 }
+

application/models/crm/Reihungstest_model.php

@@ -1,4 +1,20 @@
 <?php
+/**
+ * Copyright (C) 2022 fhcomplete.org
+ *
+ * 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 3 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, see <https://www.gnu.org/licenses/>.
+ */
 
 class Reihungstest_model extends DB_Model
 {
@@ -10,8 +26,8 @@ class Reihungstest_model extends DB_Model
 		parent::__construct();
 		$this->dbTable = 'public.tbl_reihungstest';
 		$this->pk = 'reihungstest_id';
-	}	
-	
+	}
+
 	/**
 	 * Gets a test from a test id only if it is available
 	 */
@@ -35,15 +51,15 @@ class Reihungstest_model extends DB_Model
 								 WHERE rt_id = tbl_reihungstest.reihungstest_id
 							) > 0
 					  AND reihungstest_id = ?';
-		
+
 		return $this->execQuery($query, array($reihungstest_id));
 	}
-	
+
 	/**
 	 * Checks if there are active studyplans which have no public placement tests assigned yet.
 	 * Only check assignment to studyplans that are
-	 *	- Bachelor, 
-	 *	- active, 
+	 *	- Bachelor,
+	 *	- active,
 	 *	- set as online application
 	 *  - valid for 1st terms
 	 * @return array Returns object array with studyplans that have no public placement tests assigned yet.
@@ -51,20 +67,20 @@ class Reihungstest_model extends DB_Model
 	public function checkMissingReihungstest()
 	{
 		$query = '
-			SELECT 
+			SELECT
 				bezeichnung
 			FROM
 				lehre.tbl_studienplan
 			WHERE
 				studienplan_id
-			IN 
+			IN
 			(
-				SELECT DISTINCT 
+				SELECT DISTINCT
 					studienplan_id
-				FROM 
+				FROM
 					public.tbl_studiensemester
 				JOIN
-					lehre.tbl_studienplan_semester 
+					lehre.tbl_studienplan_semester
 					USING (studiensemester_kurzbz)
 				JOIN
 					lehre.tbl_studienplan
@@ -81,31 +97,31 @@ class Reihungstest_model extends DB_Model
 					tbl_studiensemester.onlinebewerbung = \'t\'
 				AND
 					tbl_studienplan.onlinebewerbung_studienplan = \'t\'
-				AND 
+				AND
 					semester = 1
 				AND
 					typ = \'b\'
 
 				EXCEPT
 
-				SELECT DISTINCT 
+				SELECT DISTINCT
 					studienplan_id
-				FROM 
-					public.tbl_reihungstest 
+				FROM
+					public.tbl_reihungstest
 				JOIN
 					public.tbl_rt_studienplan
 					USING (reihungstest_id)
-				WHERE 
-					datum >= now() 
-				AND 
+				WHERE
+					datum >= now()
+				AND
 					oeffentlich = \'t\'
 				)
 			';
-		
+
 		return $this->execQuery($query);
 	}
-	
-	/**	
+
+	/**
 	 *  Gets amount of free places.
 	 * @return array Returns object array with faculty and amount of free places
 	 * for each public actual placement test date.
@@ -132,9 +148,9 @@ class Reihungstest_model extends DB_Model
 							SELECT
 								sum(arbeitsplaetze) - ceil(sum(arbeitsplaetze)/100.0*'. REIHUNGSTEST_ARBEITSPLAETZE_SCHWUND. ')
 							FROM
-								public.tbl_rt_ort  
+								public.tbl_rt_ort
 							JOIN
-								public.tbl_ort 
+								public.tbl_ort
 							ON (tbl_rt_ort.ort_kurzbz = tbl_ort.ort_kurzbz)
 							WHERE
 								tbl_rt_ort.rt_id = tbl_reihungstest.reihungstest_id
@@ -170,27 +186,27 @@ class Reihungstest_model extends DB_Model
 										oe_kurzbz
 									FROM
 										public.tbl_rt_studienplan
-									JOIN 	
+									JOIN
 										lehre.tbl_studienplan sp USING (studienplan_id)
-									JOIN 
+									JOIN
 										lehre.tbl_studienordnung USING (studienordnung_id)
 									JOIN
 										public.tbl_studiengang sg USING (studiengang_kz)
 										WHERE
 										tbl_rt_studienplan.reihungstest_id = tbl_reihungstest.reihungstest_id
 								)
-							AND 
+							AND
 								aktiv = true
-								
+
 							UNION ALL
-							
+
 							SELECT
 								o.oe_kurzbz, o.oe_parent_kurzbz, o.organisationseinheittyp_kurzbz
 							FROM
 								public.tbl_organisationseinheit o, meine_oes
 							WHERE
 								o.oe_kurzbz = meine_oes.oe_parent_kurzbz
-							AND 
+							AND
 								aktiv = true
 						)
 					SELECT
@@ -207,7 +223,7 @@ class Reihungstest_model extends DB_Model
 			JOIN
 				public.tbl_studiengang
 				USING (studiengang_kz)
-			WHERE 
+			WHERE
 				tbl_reihungstest.datum >= now()
 			AND
 				tbl_reihungstest.oeffentlich = \'t\'
@@ -221,10 +237,10 @@ class Reihungstest_model extends DB_Model
 			fakultaet,
 			freie_plaetze
 		';
-		
+
 		return $this->execQuery($query);
 	}
-	
+
 	/**
 	 * Checks if a registration date (Anmeldefrist) of a placement test has been reached yesterday.
 	 * @param integer $studiengang_kz Optional. Kennzahl of degree program whose registration date should be checked.
@@ -239,9 +255,9 @@ class Reihungstest_model extends DB_Model
 					SELECT CURRENT_DATE - 1
 					)
 			';
-		
+
 		$parametersArray = array();
-		
+
 		if (!isEmptyString($studiengang_kz))
 		{
 			$query .= ' AND studiengang_kz = ?';
@@ -250,7 +266,7 @@ class Reihungstest_model extends DB_Model
 
 		return $this->execQuery($query, $parametersArray);
 	}
-	
+
 	/**
 	 * Loads all applicants of a placement test for runZentraleReihungstestAnmeldefristAssistenzJob
 	 * @param integer $reihungstest_id ID of placement test
@@ -326,11 +342,11 @@ class Reihungstest_model extends DB_Model
 				AND tbl_prestudentstatus.studiensemester_kurzbz = rt.studiensemester_kurzbz
 				AND bewerbung_abgeschicktamum IS NOT NULL
 				AND bestaetigtam IS NOT NULL
-			ORDER BY studiengang_kz, 
-				orgform_kurzbz, 
-				prioritaet, 
-				nachname, 
-				vorname, 
+			ORDER BY studiengang_kz,
+				orgform_kurzbz,
+				prioritaet,
+				nachname,
+				vorname,
 				person_id
 			';
 
@@ -432,43 +448,46 @@ class Reihungstest_model extends DB_Model
 	}
 
 	/**
- * Loads all applicants of a placement test
- * @param integer $reihungstest_id ID of placement test
- * @return array Returns object array with data of applicants.
- */
+	 * Loads all applicants of a placement test
+	 * @param integer $reihungstest_id ID of placement test
+	 * @return array Returns object array with data of applicants.
+	 */
 	public function getApplicantsOfPlacementTest($reihungstest_id)
 	{
 		$query = '
-			SELECT DISTINCT tbl_rt_person.person_id,
-				anrede,
-				nachname,
-				vorname,
-				(
-					SELECT kontakt
-					FROM PUBLIC.tbl_kontakt
-					WHERE kontakttyp = \'email\'
-						AND zustellung = true
-						AND person_id = tbl_rt_person.person_id
-					ORDER BY insertamum DESC,
-						updateamum DESC LIMIT 1
-					) AS "email",
-				tbl_ort.planbezeichnung,
-				tbl_ort.lageplan
-			FROM PUBLIC.tbl_rt_person
-			JOIN PUBLIC.tbl_person ON (tbl_rt_person.person_id = tbl_person.person_id)
-			JOIN PUBLIC.tbl_reihungstest rt ON (rt_id = reihungstest_id)
-			JOIN PUBLIC.tbl_prestudent ps ON (ps.person_id = tbl_rt_person.person_id)
-			JOIN PUBLIC.tbl_prestudentstatus USING (prestudent_id)
-			LEFT JOIN bis.tbl_zgv ON (ps.zgv_code = tbl_zgv.zgv_code)
-			LEFT JOIN PUBLIC.tbl_ort ON (tbl_rt_person.ort_kurzbz = tbl_ort.ort_kurzbz)
-			WHERE rt_id = ?
-				AND get_rolle_prestudent(prestudent_id, rt.studiensemester_kurzbz) = \'Interessent\'
-				AND tbl_prestudentstatus.studiensemester_kurzbz = rt.studiensemester_kurzbz
-				AND bewerbung_abgeschicktamum IS NOT NULL
-				AND bestaetigtam IS NOT NULL
-			ORDER BY nachname,
-				vorname,
-				person_id
+		SELECT *
+		FROM
+		(SELECT DISTINCT tbl_rt_person.person_id, ps.prestudent_id, anrede, nachname, vorname,
+		 (
+		  SELECT kontakt
+		  FROM PUBLIC.tbl_kontakt
+		  WHERE kontakttyp = \'email\'
+		   AND zustellung = true
+		   AND person_id = tbl_rt_person.person_id
+		  ORDER BY insertamum DESC,
+		   updateamum DESC LIMIT 1
+		  ) AS "email",
+		 tbl_ort.planbezeichnung,
+		 tbl_ort.lageplan,
+		 sg.oe_kurzbz,
+		row_number () over (partition by tbl_rt_person.person_id order by ps.priorisierung) as row_number
+		FROM PUBLIC.tbl_rt_person
+		JOIN PUBLIC.tbl_person ON (tbl_rt_person.person_id = tbl_person.person_id)
+		JOIN PUBLIC.tbl_reihungstest rt ON (rt_id = reihungstest_id)
+		JOIN PUBLIC.tbl_prestudent ps ON (ps.person_id = tbl_rt_person.person_id)
+		JOIN PUBLIC.tbl_prestudentstatus USING (prestudent_id)
+		JOIN PUBLIC.tbl_studiengang sg ON(sg.studiengang_kz = rt.studiengang_kz)
+		LEFT JOIN bis.tbl_zgv ON (ps.zgv_code = tbl_zgv.zgv_code)
+		LEFT JOIN PUBLIC.tbl_ort ON (tbl_rt_person.ort_kurzbz = tbl_ort.ort_kurzbz)
+		WHERE rt_id = ?
+		 AND get_rolle_prestudent(prestudent_id, rt.studiensemester_kurzbz) = \'Interessent\'
+		 AND tbl_prestudentstatus.studiensemester_kurzbz = rt.studiensemester_kurzbz
+		 AND bewerbung_abgeschicktamum IS NOT NULL
+		 AND bestaetigtam IS NOT NULL
+		ORDER BY nachname,
+		 vorname,
+		 person_id)
+		temp where row_number = 1
 			';
 
 		return $this->execQuery($query, array($reihungstest_id));
@@ -511,4 +530,4 @@ class Reihungstest_model extends DB_Model
 
 		return $this->execQuery($query, array($date, $studiengang_kz));
 	}
-}
+}

application/models/system/Message_model.php

@@ -1,6 +1,6 @@
 <?php
 
-if ( ! defined('BASEPATH')) exit('No direct script access allowed');
+if (!defined('BASEPATH')) exit('No direct script access allowed');
 
 class Message_model extends DB_Model
 {
@@ -77,67 +77,6 @@ class Message_model extends DB_Model
 		return $this->execQuery($sql, $parametersArray);
 	}
 
-	/**
-	 * Gets massages with a person being sender OR receiver.
-	 * @param $person_id
-	 * @param null $status message status. by default, latest status is returned
-	 * @return array|null
-	 */
-	public function getMessagesOfPerson($person_id, $status = null)
-	{
-		$sql = 'SELECT m.message_id,
-						m.person_id,
-						m.subject,
-						m.body,
-						m.priority,
-						m.insertamum,
-						m.relationmessage_id,
-						m.oe_kurzbz,
-						se.person_id AS sepersonid,
-						se.anrede AS seanrede,
-						se.titelpost AS setitelpost,
-						se.titelpre AS setitelpre,
-						se.nachname AS senachname,
-						se.vorname AS sevorname,
-						se.vornamen AS sevornamen,
-						re.person_id AS repersonid,
-						re.anrede AS reanrede,
-						re.titelpost AS retitelpost,
-						re.titelpre AS retitelpre,
-						re.nachname AS renachname,
-						re.vorname AS revorname,
-						re.vornamen AS revornamen,
-						s.status,
-						s.statusinfo,
-						s.insertamum AS statusamum
-				  FROM public.tbl_msg_message m
-						JOIN public.tbl_msg_recipient r ON m.message_id = r.message_id
-						JOIN public.tbl_person se ON (m.person_id = se.person_id)
-						JOIN public.tbl_person re ON (r.person_id = re.person_id)
-						LEFT JOIN (
-							SELECT message_id, person_id, status, statusinfo, insertamum
-							  FROM public.tbl_msg_status
-							  %s
-						  ORDER BY insertamum DESC
-						  ) s ON (m.message_id = s.message_id AND re.person_id = s.person_id)
-				 WHERE se.person_id = ?
-				 OR re.person_id = ?
-				 ';
-
-		if (is_numeric($status))
-		{
-			$sql = sprintf($sql, 'WHERE status = '.$status);
-		}
-		else
-		{
-			$sql = sprintf($sql, '');
-		}
-
-		$parametersArray = array($person_id, $person_id);
-
-		return $this->execQuery($sql, $parametersArray);
-	}
-
 	/**
 	 * getMessageVars
 	 */
@@ -171,14 +110,14 @@ class Message_model extends DB_Model
 			return error($this->db->error(), FHC_DB_ERROR);
 		}
 	}
-	
+
 	/**
 	 * Get message variables for logged in user
 	 */
 	public function getMsgVarsLoggedInUser()
 	{
 		$result = $this->db->query('SELECT * FROM public.vw_msg_vars_user WHERE 0 = 1');
-		
+
 		if ($result)
 		{
 			return success($result->list_fields());
@@ -208,7 +147,7 @@ class Message_model extends DB_Model
 
 		return $this->execQuery(sprintf($query, is_array($person_id) ? 'IN' : '='), array($person_id));
 	}
-	
+
 	/**
 	 * Get message vars data for logged in user
 	 * @param string uid The UID should ONLY be passed if this method is called by a cronjob.
@@ -225,9 +164,9 @@ class Message_model extends DB_Model
 		{
 			$params = array(getAuthUID());
 		}
-		
+
 		$query = 'SELECT * FROM public.vw_msg_vars_user WHERE my_uid = ?';
-		
+
 		return $this->execQuery($query, $params);
 	}
 }

application/models/system/Recipient_model.php

@@ -1,4 +1,20 @@
 <?php
+/**
+ * Copyright (C) 2023 fhcomplete.org
+ *
+ * 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 3 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, see <https://www.gnu.org/licenses/>.
+ */
 
 class Recipient_model extends DB_Model
 {
@@ -111,7 +127,11 @@ class Recipient_model extends DB_Model
 		else
 		{
 			array_push($parametersArray, $person_id, $person_id);
-			$sql = sprintf($sql, 'WHERE person_id = ? AND message_id NOT IN (SELECT message_id FROM public.tbl_msg_status WHERE status >= 3 AND person_id = ?)');
+			$sql = sprintf(
+				$sql,
+				'WHERE person_id = ?
+				   AND message_id NOT IN (SELECT message_id FROM public.tbl_msg_status WHERE status >= 3 AND person_id = ?)'
+			);
 		}
 
 		if ($oe_kurzbz != null)
@@ -267,69 +287,14 @@ class Recipient_model extends DB_Model
 	 */
 	public function getReceivedMessages($person_id, $functions)
 	{
-		$sql = '-- Messages sent directly to the person
-				SELECT mr.message_id,
-						mm.relationmessage_id,
-						mm.subject,
-						mm.body,
-						mm.insertamum AS sent,
-						p.vorname,
-						p.nachname,
-						MAX(ms.status) AS status,
-						ms.person_id AS statusPersonId,
-						mr.token
-				  FROM public.tbl_msg_recipient mr
-				  JOIN public.tbl_msg_message mm ON (mm.message_id = mr.message_id)
-				  JOIN public.tbl_msg_status ms ON (ms.message_id = mr.message_id AND ms.person_id = mr.person_id)
-				  JOIN public.tbl_person p ON (p.person_id = mm.person_id)
-				 WHERE mr.person_id = ?
-			  GROUP BY mr.message_id,
-			  			mm.relationmessage_id,
-						mm.subject,
-						mm.body,
-						mm.insertamum,
-						p.vorname,
-						p.nachname,
-						ms.person_id,
-						mr.token
-				 UNION
-				-- Messages sent to a person that belongs to the recipient organisation unit
-				SELECT mrou.message_id,
-						mm.relationmessage_id,
-						mm.subject,
-						mm.body,
-						mm.insertamum AS sent,
-						pr.vorname,
-						pr.nachname,
-						MAX(ms.status) AS status,
-						ms.person_id AS statusPersonId,
-						mrou.token
-				  FROM public.tbl_person p
-				  JOIN public.tbl_benutzer b ON (b.person_id = p.person_id)
-				  JOIN (
-					  	SELECT uid, oe_kurzbz
-						  FROM public.tbl_benutzerfunktion
-						 WHERE (datum_von IS NULL OR datum_von <= NOW())
-					  	   AND (datum_bis IS NULL OR datum_bis >= NOW())
-						   AND funktion_kurzbz IN ?
-						) bf ON (bf.uid = b.uid)
-				  JOIN public.tbl_msg_recipient mrou ON (mrou.oe_kurzbz = bf.oe_kurzbz)
-				  JOIN public.tbl_msg_message mm ON (mm.message_id = mrou.message_id)
-				  JOIN public.tbl_msg_status ms ON (ms.message_id = mrou.message_id AND ms.person_id = mrou.person_id)
-				  JOIN public.tbl_person pr ON (pr.person_id = mm.person_id)
-				 WHERE p.person_id = ?
-			  GROUP BY mrou.message_id,
-			  			mm.relationmessage_id,
-						mm.subject,
-						mm.body,
-						mm.insertamum,
-						pr.vorname,
-						pr.nachname,
-						ms.person_id,
-						mrou.token
-			  ORDER BY sent DESC';
-
-		return $this->execQuery($sql, array($person_id, $functions, $person_id));
+		return $this->execQuery(
+			$this->_getReceivedMessagesQuery().' ORDER BY sent DESC',
+			array(
+				$person_id,
+				$functions,
+				$person_id
+			)
+		);
 	}
 
 	/**
@@ -337,38 +302,7 @@ class Recipient_model extends DB_Model
 	 */
 	public function getSentMessages($person_id)
 	{
-		$sql = 'SELECT mm.message_id,
-						mm.relationmessage_id,
-						mm.subject,
-						mm.body,
-						mm.insertamum AS sent,
-						p.person_id,
-						p.vorname,
-						p.nachname,
-						MAX(ms.status) AS status,
-						ms.person_id AS statusPersonId,
-						oe.bezeichnung AS oe,
-						mr.token
-				  FROM public.tbl_msg_message mm
-				  JOIN public.tbl_msg_recipient mr ON (mr.message_id = mm.message_id)
-				  JOIN public.tbl_msg_status ms ON (ms.message_id = mm.message_id AND ms.person_id = mr.person_id)
-				  JOIN public.tbl_person p ON (p.person_id = mr.person_id)
-			 LEFT JOIN public.tbl_organisationseinheit oe ON (oe.oe_kurzbz = mr.oe_kurzbz)
-				 WHERE mm.person_id = ?
-			  GROUP BY mm.message_id,
-			  			mm.relationmessage_id,
-						mm.subject,
-						mm.body,
-						mm.insertamum,
-						p.person_id,
-						p.vorname,
-						p.nachname,
-						ms.person_id,
-						oe.bezeichnung,
-						mr.token
-			  ORDER BY sent DESC';
-
-		return $this->execQuery($sql, array($person_id));
+		return $this->execQuery($this->_getSentMessagesQuery().' ORDER BY sent DESC', array($person_id));
 	}
 
 	/**
@@ -393,4 +327,191 @@ class Recipient_model extends DB_Model
 
 		return $this->execQuery($sql, array($messageIds));
 	}
+
+	/**
+	 *
+	 */
+	public function getReceivedAndSentMessages($person_id, $functions)
+	{
+		return $this->execQuery(
+			$this->_getReceivedMessagesQuery().
+			' UNION '.
+			$this->_getSentMessagesQuery().
+			' ORDER BY sent DESC',
+			array(
+				$person_id,
+				$functions,
+				$person_id,
+				$person_id
+			)
+		);
+	}
+
+	// -------------------------------------------------------------------------------------------------------------------------
+	// Private methods
+
+	/**
+	 * Returns the query used to get the sent messages for a given person
+	 */
+	private function _getSentMessagesQuery()
+	{
+		return '-- Messages sent from the given person
+			SELECT mm.message_id,
+				mm.relationmessage_id,
+				mm.subject,
+				mm.body,
+				mm.insertamum AS sent,
+				pr.person_id AS "recipientPersonId",
+				pr.vorname AS "recipientName",
+				pr.nachname AS "recipientSurname",
+				ps.person_id AS "senderPersonId",
+				ps.vorname AS "senderName",
+				ps.nachname AS "senderSurname",
+				(SELECT MAX(status) FROM public.tbl_msg_status WHERE message_id = mm.message_id AND person_id = mr.person_id) AS "lastStatus",
+				(SELECT MAX(insertamum) FROM public.tbl_msg_status WHERE message_id = mm.message_id AND person_id = mr.person_id) AS "lastStatusDate",
+				oe.oe_kurzbz AS "oeId",
+				COALESCE(sg.bezeichnung, oe.bezeichnung) AS oe,
+				mr.token
+			  FROM public.tbl_msg_message mm
+			  JOIN public.tbl_msg_recipient mr ON (mr.message_id = mm.message_id)
+			  JOIN public.tbl_person pr ON (pr.person_id = mr.person_id)
+			  JOIN public.tbl_person ps ON (ps.person_id = mm.person_id)
+		     LEFT JOIN public.tbl_organisationseinheit oe ON (oe.oe_kurzbz = mr.oe_kurzbz)
+		     LEFT JOIN public.tbl_studiengang sg ON (sg.oe_kurzbz = mr.oe_kurzbz)
+			 WHERE mm.person_id = ?
+		      GROUP BY mm.message_id,
+		  		mm.relationmessage_id,
+				mm.subject,
+				mm.body,
+				mm.insertamum,
+				pr.person_id,
+				pr.vorname,
+				pr.nachname,
+				ps.person_id,
+				ps.vorname,
+				ps.nachname,
+				"lastStatus",
+				"lastStatusDate",
+				oe.oe_kurzbz,
+				oe,
+				mr.token';
+	}
+
+	/**
+	 * Returns the query used to get the received messages for a given person
+	 */
+	private function _getReceivedMessagesQuery()
+	{
+		return '-- Messages sent directly to the person
+			SELECT mr.message_id,
+				mm.relationmessage_id,
+				mm.subject,
+				mm.body,
+				mm.insertamum AS sent,
+				pr.person_id AS "recipientPersonId",
+				pr.vorname AS "recipientName",
+				pr.nachname AS "recipientSurname",
+				ps.person_id AS "senderPersonId",
+				ps.vorname AS "senderName",
+				ps.nachname AS "senderSurname",
+				(
+					SELECT MAX(status)
+					  FROM public.tbl_msg_status
+					 WHERE message_id = mm.message_id
+					   AND person_id = mr.person_id
+				) AS "lastStatus",
+				(
+					SELECT MAX(insertamum)
+					  FROM public.tbl_msg_status
+					 WHERE message_id = mm.message_id
+					   AND person_id = mr.person_id
+				) AS "lastStatusDate",
+				oe.oe_kurzbz AS "oeId",
+				COALESCE(sg.bezeichnung, oe.bezeichnung) AS oe,
+				mr.token
+			  FROM public.tbl_msg_recipient mr
+			  JOIN public.tbl_msg_message mm ON (mm.message_id = mr.message_id)
+			  JOIN public.tbl_person ps ON (ps.person_id = mm.person_id)
+			  JOIN public.tbl_person pr ON (pr.person_id = mr.person_id)
+		     LEFT JOIN public.tbl_organisationseinheit oe ON (oe.oe_kurzbz = mm.oe_kurzbz)
+		     LEFT JOIN public.tbl_studiengang sg ON (sg.oe_kurzbz = mm.oe_kurzbz)
+			 WHERE mr.person_id = ?
+		      GROUP BY mr.message_id,
+		  		mm.relationmessage_id,
+				mm.subject,
+				mm.body,
+				mm.insertamum,
+				pr.person_id,
+				pr.vorname,
+				pr.nachname,
+				ps.person_id,
+				ps.vorname,
+				ps.nachname,
+				"lastStatus",
+				"lastStatusDate",
+				oe.oe_kurzbz,
+				oe,
+				mr.token
+			 UNION
+			-- Messages sent to a person that belongs to the recipient organisation unit
+			SELECT mrou.message_id,
+				mm.relationmessage_id,
+				mm.subject,
+				mm.body,
+				mm.insertamum AS sent,
+				pr.person_id AS "recipientPersonId",
+				pr.vorname AS "recipientName",
+				pr.nachname AS "recipientSurname",
+				ps.person_id AS "senderPersonId",
+				ps.vorname AS "senderName",
+				ps.nachname AS "senderSurname",
+				(
+					SELECT MAX(status)
+					  FROM public.tbl_msg_status
+					 WHERE message_id = mrou.message_id
+					   AND person_id = mrou.person_id
+				) AS "lastStatus",
+				(
+					SELECT MAX(insertamum)
+					  FROM public.tbl_msg_status
+					 WHERE message_id = mrou.message_id
+					  AND person_id = mrou.person_id
+				) AS "lastStatusDate",
+				oe.oe_kurzbz AS "oeId",
+				COALESCE(sg.bezeichnung, oe.bezeichnung) AS oe,
+				mrou.token
+			  FROM public.tbl_person p
+			  JOIN public.tbl_benutzer b ON (b.person_id = p.person_id)
+			  JOIN (
+			  	SELECT uid, oe_kurzbz
+				  FROM public.tbl_benutzerfunktion
+				 WHERE (datum_von IS NULL OR datum_von <= NOW())
+			  	   AND (datum_bis IS NULL OR datum_bis >= NOW())
+				   AND funktion_kurzbz IN ?
+			  ) bf ON (bf.uid = b.uid)
+			  JOIN public.tbl_msg_recipient mrou ON (mrou.oe_kurzbz = bf.oe_kurzbz)
+			  JOIN public.tbl_msg_message mm ON (mm.message_id = mrou.message_id)
+			  JOIN public.tbl_person ps ON (ps.person_id = mm.person_id)
+			  JOIN public.tbl_person pr ON (pr.person_id = mrou.person_id)
+		     LEFT JOIN public.tbl_organisationseinheit oe ON (oe.oe_kurzbz = mrou.oe_kurzbz)
+		     LEFT JOIN public.tbl_studiengang sg ON (sg.oe_kurzbz = mrou.oe_kurzbz)
+			 WHERE p.person_id = ?
+		      GROUP BY mrou.message_id,
+		  		mm.relationmessage_id,
+				mm.subject,
+				mm.body,
+				mm.insertamum,
+				pr.person_id,
+				pr.vorname,
+				pr.nachname,
+				ps.person_id,
+				ps.vorname,
+				ps.nachname,
+				"lastStatus",
+				"lastStatusDate",
+				oe.oe_kurzbz,
+				oe,
+				mrou.token';
+	}
 }
+

application/views/system/infocenter/infocenterDetails.php

@@ -182,7 +182,10 @@
 								<div class="panel-body">
 									<div class="row" id="messagelist">
 										<?php
-										$this->load->view('system/infocenter/messageList.php', $messages);
+											$this->load->view(
+												'system/infocenter/messageList.php',
+												$messages
+											);
 										?>
 									</div>
 								</div>

application/views/system/infocenter/messageList.php

@@ -6,21 +6,27 @@ $widthColumn = $msgExists === true ? 8 : 12;
 	<table id="msgtable" class="table table-bordered table-condensed tablesort-hover tablesort-active">
 		<thead>
 		<tr>
-			<th><?php echo  ucfirst($this->p->t('global','gesendetAm')) ?></th>
-			<th><?php echo  ucfirst($this->p->t('global','sender')) ?></th>
-			<th><?php echo  ucfirst($this->p->t('global','empfaenger')) ?></th>
-			<th><?php echo  ucfirst($this->p->t('global','betreff')) ?></th>
-			<th><?php echo  ucfirst($this->p->t('global','gelesenAm')) ?></th>
+			<th><?php echo  ucfirst($this->p->t('global', 'gesendetAm')) ?></th>
+			<th><?php echo  ucfirst($this->p->t('global', 'sender')) ?></th>
+			<th><?php echo  ucfirst($this->p->t('global', 'empfaenger')) ?></th>
+			<th><?php echo  ucfirst($this->p->t('global', 'betreff')) ?></th>
+			<th><?php echo  ucfirst($this->p->t('global', 'gelesenAm')) ?></th>
 		</tr>
 		</thead>
 		<tbody>
 		<?php foreach ($messages as $message): ?>
-			<tr id="<?php echo $message->message_id.'_'.$message->repersonid ?>" style="cursor: pointer">
-				<td><?php echo isset($message->insertamum) ? date_format(date_create($message->insertamum), 'd.m.Y H:i:s') : '' ?></td>
-				<td><?php echo $message->sevorname.' '.$message->senachname ?></td>
-				<td><?php echo $message->revorname.' '.$message->renachname ?></td>
+			<tr id="<?php echo $message->message_id.'_'.$message->recipientPersonId ?>" style="cursor: pointer">
+				<td><?php echo isset($message->sent) ? date_format(date_create($message->sent), 'd.m.Y H:i:s') : '' ?></td>
+				<td><?php
+					echo $message->senderPersonId == $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID) && isset($message->oeId) ?
+						$message->oe : $message->senderName.' '.$message->senderSurname;
+				?></td>
+				<td><?php
+					echo $message->recipientPersonId == $this->config->item(MessageLib::CFG_SYSTEM_PERSON_ID) && isset($message->oeId) ?
+						$message->oe : $message->recipientName.' '.$message->recipientSurname;
+				?></td>
 				<td><?php echo $message->subject ?></td>
-				<td><?php echo isset($message->statusamum) ? date_format(date_create($message->statusamum), 'd.m.Y H:i:s') : '' ?></td>
+				<td><?php echo isset($message->lastStatusDate) ? date_format(date_create($message->lastStatusDate), 'd.m.Y H:i:s') : '' ?></td>
 			</tr>
 		<?php endforeach; ?>
 		</tbody>

application/views/system/messages/htmlRead.php

@@ -44,7 +44,7 @@
 											</td>
 											<td>
 
-												<?php echo $sender->vorname.' '.$sender->nachname; ?>
+												<?php echo $sender; ?>
 
 											</td>
 										</tr>

config/vilesci.config-default.inc.php

@@ -279,4 +279,7 @@ define ('DEFAULT_LEHRMODUS','regulaer');
 //Echter Dienstvertrag
 define ('DEFAULT_ECHTER_DIENSTVERTRAG',[103,110]);
 
+// Person id of the system sender for the messaging system
+define('MESSAGING_SYSTEM_PERSON_ID', 92);
+
 ?>

rdf/messages.rdf.php

@@ -41,43 +41,142 @@ else
 	die('Parameter person_id is missing');
 
 $db = new basis_db();
-$qry = "
-SELECT
-	m.message_id AS message_id,
-	m.subject AS subject,
-	m.body AS body,
-	m.insertamum AS insertamum,
-	m.relationmessage_id AS relationmessage_id,
-	(SELECT COALESCE(titelpre,'') || ' ' || COALESCE(vorname,'') || ' ' || COALESCE(nachname,'') || ' ' || COALESCE(titelpost,'') FROM public.tbl_person WHERE person_id = m.person_id) as sender,
-	(SELECT COALESCE(titelpre,'') || ' ' || COALESCE(vorname,'') || ' ' || COALESCE(nachname,'') || ' ' || COALESCE(titelpost,'') FROM public.tbl_person WHERE person_id = r.person_id) as recipient,
-	m.person_id as sender_id,
-	r.person_id as recipient_id,
-	MAX(ss.status) as status,
-	MAX(ss.insertamum) as statusdatum
-FROM public.tbl_msg_message m
-     JOIN public.tbl_msg_recipient r USING(message_id)
-     JOIN public.tbl_msg_status ss ON(r.message_id = ss.message_id AND ss.person_id = r.person_id)
-WHERE m.person_id = ".$db->db_add_param($person_id, FHC_INTEGER)."
-GROUP BY m.message_id, m.subject, m.body, m.insertamum, m.relationmessage_id, sender, recipient, sender_id, recipient_id
-UNION ALL
-SELECT
-	m.message_id AS message_id,
-	m.subject AS subject,
-	m.body AS body,
-	m.insertamum AS insertamum,
-	m.relationmessage_id AS relationmessage_id,
-	(SELECT COALESCE(titelpre,'') || ' ' || COALESCE(vorname,'') || ' ' || COALESCE(nachname,'') || ' ' || COALESCE(titelpost,'') FROM public.tbl_person WHERE person_id = m.person_id) as sender,
-	(SELECT COALESCE(titelpre,'') || ' ' || COALESCE(vorname,'') || ' ' || COALESCE(nachname,'') || ' ' || COALESCE(titelpost,'') FROM public.tbl_person WHERE person_id = r.person_id) as recipient,
-	m.person_id as sender_id,
-	r.person_id as recipient_id,
-	MAX(ss.status) as status,
-	MAX(ss.insertamum) as statusdatum
-FROM public.tbl_msg_recipient r
-     JOIN public.tbl_msg_status ss USING(message_id, person_id)
-     JOIN public.tbl_msg_message m USING(message_id)
-WHERE r.person_id = ".$db->db_add_param($person_id, FHC_INTEGER)."
-GROUP BY m.message_id, m.subject, m.body, m.insertamum, m.relationmessage_id, sender, recipient, sender_id, recipient_id
-ORDER BY insertamum";
+
+$qry = '-- Messages sent from the given person
+       SELECT mm.message_id,
+               mm.relationmessage_id,
+               mm.subject,
+               mm.body,
+               mm.insertamum AS sent,
+               pr.person_id AS "recipientPersonId",
+               pr.vorname AS "recipientName",
+               pr.nachname AS "recipientSurname",
+               ps.person_id AS "senderPersonId",
+               ps.vorname AS "senderName",
+               ps.nachname AS "senderSurname",
+               (SELECT MAX(status) FROM public.tbl_msg_status WHERE message_id = mm.message_id AND person_id = mr.person_id) AS "lastStatus",
+               (SELECT MAX(insertamum) FROM public.tbl_msg_status WHERE message_id = mm.message_id AND person_id = mr.person_id) AS "lastStatusDate",
+               oe.oe_kurzbz AS "oeId",
+               COALESCE(sg.bezeichnung, oe.bezeichnung) AS oe,
+               mr.token
+         FROM public.tbl_msg_message mm
+         JOIN public.tbl_msg_recipient mr ON (mr.message_id = mm.message_id)
+         JOIN public.tbl_person pr ON (pr.person_id = mr.person_id)
+         JOIN public.tbl_person ps ON (ps.person_id = mm.person_id)
+    LEFT JOIN public.tbl_organisationseinheit oe ON (oe.oe_kurzbz = mr.oe_kurzbz)
+    LEFT JOIN public.tbl_studiengang sg ON (sg.oe_kurzbz = mr.oe_kurzbz)
+        WHERE mm.person_id = '.$db->db_add_param($person_id, FHC_INTEGER).'
+     GROUP BY mm.message_id,
+               mm.relationmessage_id,
+               mm.subject,
+               mm.body,
+               mm.insertamum,
+               pr.person_id,
+               pr.vorname,
+               pr.nachname,
+               ps.person_id,
+               ps.vorname,
+               ps.nachname,
+               "lastStatus",
+               "lastStatusDate",
+               oe.oe_kurzbz,
+               oe,
+       	mr.token
+	UNION
+	-- Messages sent directly to the person
+                        SELECT mr.message_id,
+                                mm.relationmessage_id,
+                                mm.subject,
+                                mm.body,
+                                mm.insertamum AS sent,
+                                pr.person_id AS "recipientPersonId",
+                                pr.vorname AS "recipientName",
+                                pr.nachname AS "recipientSurname",
+                                ps.person_id AS "senderPersonId",
+                                ps.vorname AS "senderName",
+                                ps.nachname AS "senderSurname",
+                                (SELECT MAX(status) FROM public.tbl_msg_status WHERE message_id = mm.message_id AND person_id = mr.person_id) AS "lastStatus",
+                                (SELECT MAX(insertamum) FROM public.tbl_msg_status WHERE message_id = mm.message_id AND person_id = mr.person_id) AS "lastStatusDate",
+                                oe.oe_kurzbz AS "oeId",
+                                COALESCE(sg.bezeichnung, oe.bezeichnung) AS oe,
+                                mr.token
+                          FROM public.tbl_msg_recipient mr
+                          JOIN public.tbl_msg_message mm ON (mm.message_id = mr.message_id)
+                          JOIN public.tbl_person ps ON (ps.person_id = mm.person_id)
+                          JOIN public.tbl_person pr ON (pr.person_id = mr.person_id)
+                     LEFT JOIN public.tbl_organisationseinheit oe ON (oe.oe_kurzbz = mm.oe_kurzbz)
+                     LEFT JOIN public.tbl_studiengang sg ON (sg.oe_kurzbz = mm.oe_kurzbz)
+                         WHERE mr.person_id = '.$db->db_add_param($person_id, FHC_INTEGER).'
+                      GROUP BY mr.message_id,
+                                mm.relationmessage_id,
+                                mm.subject,
+                                mm.body,
+                                mm.insertamum,
+                                pr.person_id,
+                                pr.vorname,
+                                pr.nachname,
+                                ps.person_id,
+                                ps.vorname,
+                                ps.nachname,
+                                "lastStatus",
+                                "lastStatusDate",
+                                oe.oe_kurzbz,
+                                oe,
+                                mr.token
+                         UNION
+-- Messages sent to a person that belongs to the recipient organisation unit
+                        SELECT mrou.message_id,
+                                mm.relationmessage_id,
+                                mm.subject,
+                                mm.body,
+                                mm.insertamum AS sent,
+                                pr.person_id AS "recipientPersonId",
+                                pr.vorname AS "recipientName",
+                                pr.nachname AS "recipientSurname",
+                                ps.person_id AS "senderPersonId",
+                                ps.vorname AS "senderName",
+                                ps.nachname AS "senderSurname",
+                                (SELECT MAX(status) FROM public.tbl_msg_status WHERE message_id = mrou.message_id AND person_id = mrou.person_id) AS "lastStatus",
+                                (SELECT MAX(insertamum) FROM public.tbl_msg_status WHERE message_id = mrou.message_id AND person_id = mrou.person_id) AS "lastStatusDate",
+                                oe.oe_kurzbz AS "oeId",
+                                COALESCE(sg.bezeichnung, oe.bezeichnung) AS oe,
+                                mrou.token
+                          FROM public.tbl_person p
+                          JOIN public.tbl_benutzer b ON (b.person_id = p.person_id)
+                          JOIN (
+                                SELECT uid, oe_kurzbz
+                                  FROM public.tbl_benutzerfunktion
+                                 WHERE (datum_von IS NULL OR datum_von <= NOW())
+                                   AND (datum_bis IS NULL OR datum_bis >= NOW())
+                                   AND funktion_kurzbz IN (\'ass\')
+                          ) bf ON (bf.uid = b.uid)
+                          JOIN public.tbl_msg_recipient mrou ON (mrou.oe_kurzbz = bf.oe_kurzbz)
+                          JOIN public.tbl_msg_message mm ON (mm.message_id = mrou.message_id)
+                          JOIN public.tbl_person ps ON (ps.person_id = mm.person_id)
+                          JOIN public.tbl_person pr ON (pr.person_id = mrou.person_id)
+                     LEFT JOIN public.tbl_organisationseinheit oe ON (oe.oe_kurzbz = mrou.oe_kurzbz)
+                     LEFT JOIN public.tbl_studiengang sg ON (sg.oe_kurzbz = mrou.oe_kurzbz)
+                         WHERE p.person_id = '.$db->db_add_param($person_id, FHC_INTEGER).'
+                      GROUP BY mrou.message_id,
+                                mm.relationmessage_id,
+                                mm.subject,
+                                mm.body,
+                                mm.insertamum,
+                                pr.person_id,
+                                pr.vorname,
+                                pr.nachname,
+                                ps.person_id,
+                                ps.vorname,
+                                ps.nachname,
+                                "lastStatus",
+                                "lastStatusDate",
+                                oe.oe_kurzbz,
+                                oe,
+                                mrou.token
+';
+
+
+// $db->db_add_param($person_id, FHC_INTEGER)
 
 if($db->db_query($qry))
 {
@@ -85,34 +184,56 @@ if($db->db_query($qry))
 	while($row = $db->db_fetch_object())
 	{
 		$status = '';
-		if ($row->status == 0)
+		if ($row->lastStatus == 0)
 		{
 			$status = 'Unread';
 		}
-		else if ($row->status == 1)
+		else if ($row->lastStatus == 1)
 		{
 			$status = 'Read';
 		}
-		else if ($row->status == 2)
+		else if ($row->lastStatus == 2)
 		{
 			$status = 'Archived';
 		}
-		else if ($row->status == 3)
+		else if ($row->lastStatus == 3)
 		{
 			$status = 'Deleted';
 		}
 
-		$i=$oRdf->newObjekt($row->message_id);
-		$oRdf->obj[$i]->setAttribut('subject',$row->subject,true);
-		$oRdf->obj[$i]->setAttribut('body',$row->body,true);
-		$oRdf->obj[$i]->setAttribut('message_id',$row->message_id,true);
-		$oRdf->obj[$i]->setAttribut('insertamum',$row->insertamum,true);
-		$oRdf->obj[$i]->setAttribut('status',$status,true);
-		$oRdf->obj[$i]->setAttribut('statusdatum',$datum_obj->formatDatum($row->statusdatum,'d.m.Y H:i'),true);
-		$oRdf->obj[$i]->setAttribut('sender',$row->sender,true);
-		$oRdf->obj[$i]->setAttribut('recipient',$row->recipient,true);
-		$oRdf->obj[$i]->setAttribut('sender_id',$row->sender_id,true);
-		$oRdf->obj[$i]->setAttribut('recipient_id',$row->recipient_id,true);
+		$sender = $recipient = 'System sender'; // default fallback
+
+		// If the sender is not the system sender
+		if ($row->senderPersonId != MESSAGING_SYSTEM_PERSON_ID)
+		{
+			$sender = $row->senderName.' '.$row->senderSurname;
+		}
+		elseif ($row->oeId != null) // otherwise take the oe
+		{
+			$sender = $row->oe;
+		}
+
+		// If the recipient is not the system sender
+		if ($row->recipientPersonId != MESSAGING_SYSTEM_PERSON_ID)
+		{
+			$recipient = $row->recipientName.' '.$row->recipientSurname;
+		}
+		elseif ($row->oeId != null) // otherwise take the oe
+		{
+			$recipient = $row->oe;
+		}
+
+		$i = $oRdf->newObjekt($row->message_id);
+		$oRdf->obj[$i]->setAttribut('subject', $row->subject, true);
+		$oRdf->obj[$i]->setAttribut('body', $row->body, true);
+		$oRdf->obj[$i]->setAttribut('message_id', $row->message_id, true);
+		$oRdf->obj[$i]->setAttribut('insertamum', $row->sent, true);
+		$oRdf->obj[$i]->setAttribut('status', $status, true);
+		$oRdf->obj[$i]->setAttribut('statusdatum', $datum_obj->formatDatum($row->lastStatusDate, 'd.m.Y H:i'), true);
+		$oRdf->obj[$i]->setAttribut('sender', $sender, true);
+		$oRdf->obj[$i]->setAttribut('recipient', $recipient, true);
+		$oRdf->obj[$i]->setAttribut('sender_id', $row->senderPersonId, true);
+		$oRdf->obj[$i]->setAttribut('recipient_id', $row->recipientPersonId, true);
 
 		if($row->relationmessage_id!='')
 			$oRdf->addSequence($row->message_id, $row->relationmessage_id);