add endpoints getRoomsWithEmployeesAssigned and getEmployeesWithRoomAssigned

application/controllers/api/frontend/v1/Ort.php

@@ -36,7 +36,9 @@ class Ort extends FHCAPI_Controller
 			'ContentID' => self::PERM_LOGGED,
 			'getOrtKurzbzContent' => self::PERM_LOGGED,
 			'getRooms' => self::PERM_LOGGED,
-			'getTypes' => self::PERM_LOGGED
+			'getTypes' => self::PERM_LOGGED,
+			'getRoomsWithEmployeesAssigned' => 'basis/ort:r',
+			'getEmployeesWithRoomAssigned' => 'basis/ort:r'
 		]);
 
 		$this->load->model('ressource/Ort_model', 'OrtModel');
@@ -174,5 +176,42 @@ class Ort extends FHCAPI_Controller
 
 		$this->terminateWithSuccess($content);
 	}
+
+	public function getRoomsWithEmployeesAssigned($ort_kurzbz=null)
+	{
+		$res = $this->OrtModel->getRoomsWithEmployeesAssigned($ort_kurzbz);
+
+		if (isError($res))
+		{
+			$this->terminateWithError(getError($res));
+		}
+
+		$data = hasData($res) ? getData($res) : [];
+		$this->json_decode_db_res($data);
+		$this->terminateWithSuccess($data);
+	}
+
+	public function getEmployeesWithRoomAssigned($mitarbeiter_uid=null)
+	{
+		$res = $this->OrtModel->getEmployeesWithRoomAssigned($mitarbeiter_uid);
+
+		if (isError($res))
+		{
+			$this->terminateWithError(getError($res));
+		}
+
+		$data = hasData($res) ? getData($res) : [];
+		$this->json_decode_db_res($data);
+		$this->terminateWithSuccess($data);
+	}
+
+	protected function json_decode_db_res(&$data)
+	{
+		array_walk($data, function($item, $key) {
+			isset($item->employees) && $item->employees = json_decode($item->employees);
+			isset($item->employee) && $item->employee = json_decode($item->employee);
+			isset($item->room) && $item->room = json_decode($item->room);
+		});
+	}
 }
 

application/controllers/api/frontend/v1/stv/Dokumente.php

@@ -78,52 +78,32 @@ class Dokumente extends FHCAPI_Controller
 			$this->terminateWithError($this->p->t('ui', 'errorMissingValue', ['value' => 'Studiengang_kz']), self::ERROR_TYPE_GENERAL);
 
 		$resultPreDoc = $this->_getPrestudentDokumente($prestudent_id);
-
-		$arrayAccepted = [];
 		$person_id = $this->_getPersonId($prestudent_id);
 
-		$docNames = array_map(function ($item) {
-			return $item->dokument_kurzbz;
-		}, $resultPreDoc);
+		$mergedArray = [];
 
-		foreach($docNames as $doc)
+		foreach ($resultPreDoc as $pre)
 		{
-			$result = $this->AkteModel->getAktenFAS($person_id, $doc, $studiengang_kz, $prestudent_id, true);
+			$result = $this->AkteModel->getAktenFAS($person_id, $pre->dokument_kurzbz, $studiengang_kz, $prestudent_id, true);
 
 			if (isError($result))
-			{
 				return $this->terminateWithError($result, self::ERROR_TYPE_GENERAL);
-			}
+
 			if (hasData($result))
 			{
-				$data = getData($result);
-				foreach ($data as $value)
+				foreach (getData($result) as $doc)
 				{
-					array_push($arrayAccepted, $value);
+					$merged = clone $doc;
+					$merged->docdatum = $pre->docdatum;
+					$merged->insertvonma = $pre->insertvonma;
+					$merged->bezeichnung = $pre->bezeichnung;
+					$mergedArray[] = $merged;
 				}
 			}
-		}
-
-		//Mapping with document_kurzbz
-		$preDocMap = [];
-		foreach ($resultPreDoc as $pre) {
-			$preDocMap[$pre->dokument_kurzbz] = $pre;
-		}
-
-		$mergedArray = [];
-		foreach ($arrayAccepted as $doc) {
-			$merged = clone $doc;
-
-			if (isset($preDocMap[$doc->dokument_kurzbz])) {
-				$merged->docdatum = $preDocMap[$doc->dokument_kurzbz]->docdatum;
-				$merged->insertvonma = $preDocMap[$doc->dokument_kurzbz]->insertvonma;
-				$merged->bezeichnung = $preDocMap[$doc->dokument_kurzbz]->bezeichnung;
-			} else {
-				$merged->akzeptiertdatum = null;
-				$merged->akzeptiertvon = null;
+			else
+			{
+				$mergedArray[] = $pre;
 			}
-
-			$mergedArray[] = $merged;
 		}
 
 		$this->terminateWithSuccess($mergedArray);

application/models/ressource/Ort_model.php

@@ -32,4 +32,124 @@ class Ort_model extends DB_Model
 		
 	}
 
+	public function getRoomsWithEmployeesAssigned($ort_kurzbz=null)
+	{
+		$ort_kurzbz_clause = is_null($ort_kurzbz)
+			? ''
+			: 'and r.ort_kurzbz = ' . $this->escape($ort_kurzbz);
+		$sql = <<<EOSQL
+			{$this->roomEmployeesCTEs()}
+
+			select
+				r.rauminfo as room,
+				mir.ma_count as employee_count,
+				mir.mas_in_room as employees
+			from
+				roominfo r
+			join
+				mas_in_room mir on r.ort_kurzbz = mir.ort_kurzbz
+			where
+				1=1
+				{$ort_kurzbz_clause}
+			order by
+				mir.ma_count DESC
+EOSQL;
+
+		return $this->execReadOnlyQuery($sql);
+	}
+
+	public function getEmployeesWithRoomAssigned($mitarbeiter_uid=null)
+	{
+		$mtarbeiter_uid_clause = is_null($mitarbeiter_uid)
+			? ''
+			: 'and aer.mitarbeiter_uid = ' . $this->escape($mitarbeiter_uid);
+		$sql = <<<EOSQL
+			{$this->roomEmployeesCTEs()}
+
+			select
+				m.mainfo as employee,
+				r.rauminfo as room
+			from
+				active_employee_room aer
+			join
+				roominfo r on aer.ort_kurzbz = r.ort_kurzbz
+			join
+				mainfo m on aer.mitarbeiter_uid = m.mitarbeiter_uid
+			where
+				1=1
+				{$mtarbeiter_uid_clause}
+EOSQL;
+
+		return $this->execReadOnlyQuery($sql);
+	}
+
+	protected function roomEmployeesCTEs()
+	{
+		return <<<EOCTES
+			with active_employee_room as (
+				select
+					tm.mitarbeiter_uid,
+					tm.ort_kurzbz,
+					td.vertragsart_kurzbz
+				from
+					public.tbl_mitarbeiter tm
+				join
+					hr.tbl_dienstverhaeltnis td
+					on
+					td.mitarbeiter_uid = tm.mitarbeiter_uid
+					and NOW() between COALESCE(td.von, '1970-01-01') and coalesce(td.bis, '2170-12-31')
+					and td.mitarbeiter_uid not like '_Dummy%'
+			),
+			roominfo as (
+				select
+					o.ort_kurzbz,
+					json_build_object(
+						'ort_kurzbz', o.ort_kurzbz,
+						'bezeichnung', o.bezeichnung,
+						'planbezeichnung', o.planbezeichnung,
+						'max_person', o.max_person,
+						'aktiv', o.aktiv
+					) as rauminfo
+				from
+					public.tbl_ort o
+			),
+			mainfo as (
+				select
+					tm.mitarbeiter_uid,
+					tm.ort_kurzbz,
+					json_build_object(
+						'mitarbeiter_uid', tm.mitarbeiter_uid,
+						'vorname', tp.vorname,
+						'nachname', tp.nachname,
+						'vertragsart_kurzbz', td.vertragsart_kurzbz
+					) as mainfo
+				from
+					public.tbl_mitarbeiter tm
+				join
+					public.tbl_benutzer b on b.uid = tm.mitarbeiter_uid and b.aktiv = true
+				join
+					public.tbl_person tp on tp.person_id = b.person_id
+				join
+					hr.tbl_dienstverhaeltnis td
+					on
+					td.mitarbeiter_uid = tm.mitarbeiter_uid
+					and NOW() between COALESCE(td.von, '1970-01-01') and coalesce(td.bis, '2170-12-31')
+					and td.mitarbeiter_uid not like '_Dummy%'
+			),
+			mas_in_room as (
+				select
+					m.ort_kurzbz,
+					count(m.mitarbeiter_uid) as ma_count,
+					json_agg(m.mainfo) as mas_in_room
+				from
+					mainfo m
+				group by
+					m.ort_kurzbz
+				order by
+					ma_count desc
+			)
+
+EOCTES;
+
+	}
 }