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/libraries/vertragsbestandteil/AbstractBestandteil.php

@@ -40,9 +40,7 @@ abstract class AbstractBestandteil implements IValidation
 		
 		if( is_bool($new_value) && ($old_value !== $new_value) ) {
 			$this->modifiedcolumns[$columnname] = $columnname;
-		} else if(is_null($old_value) xor is_null($new_value)) {
-			$this->modifiedcolumns[$columnname] = $columnname;
-		} else if($old_value != $new_value) {
+		} else if($old_value != $new_value) {		
 			$this->modifiedcolumns[$columnname] = $columnname;
 		}
 	}

application/libraries/vertragsbestandteil/VertragsbestandteilLohnguide.php

@@ -137,25 +137,19 @@ EOTXT;
 		return parent::__toString() . $txt;
 	}
 
-	public function validate()
+	/* public function validate()
 	{
-		$value = $this->vordienstzeit;
-
-		if ($value === null || $value === '') {
-			$result = null;   // allow null value 
-		} else {
-			$result = filter_var($value, FILTER_VALIDATE_INT, [
-				'options' => [
-					'min_range' => 0,
-					'max_range' => 100
-				]
-			]);
-
-			if ($result === false) {				
-				$this->validationerrors[] = 'Vordienstjahre muss eine ganze Zahl (0 bis 100) enthalten oder leer sein.';
-			}
+		if( !(filter_var($this->tage, FILTER_VALIDATE_INT, 
+				array(
+					'options' => array(
+						'min_range' => 1,
+						'max_range' => 50
+					)
+				)
+			)) ) {
+			$this->validationerrors[] = 'Urlaubsanspruch muss eine Tagesanzahl im Bereich 1 bis 50 sein.';
 		}
 		
 		return parent::validate();
-	} 
+	} */
 }

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;
+
+	}
 }

composer.json

@@ -505,9 +505,7 @@
 		"vuejs/vuedatepicker_js": "7.2.0",
 		"vuejs/vuedatepicker_css": "7.2.0",
 		"vuejs/vuedatepicker_js11": "11.0.1",
-		"vuejs/vuedatepicker_css11": "11.0.1",
-		"box/spout": "^2.7",
-		"phpoffice/phpspreadsheet": "~1.7.0"
+		"vuejs/vuedatepicker_css11": "11.0.1"
 
 	},
 	"config": {

composer.lock

@@ -4,7 +4,7 @@
         "Read more about it at https://getcomposer.org/doc/01-basic-usage.md#installing-dependencies",
         "This file is @generated automatically"
     ],
-    "content-hash": "c5b0e5eb7392192a92418412ab1f2ed2",
+    "content-hash": "f4f0af4586f46f97d8b6092c1ac0fb3a",
     "packages": [
         {
             "name": "afarkas/html5shiv",
@@ -77,79 +77,6 @@
             },
             "type": "library"
         },
-        {
-            "name": "box/spout",
-            "version": "v2.7.3",
-            "source": {
-                "type": "git",
-                "url": "https://github.com/box/spout.git",
-                "reference": "3681a3421a868ab9a65da156c554f756541f452b"
-            },
-            "dist": {
-                "type": "zip",
-                "url": "https://api.github.com/repos/box/spout/zipball/3681a3421a868ab9a65da156c554f756541f452b",
-                "reference": "3681a3421a868ab9a65da156c554f756541f452b",
-                "shasum": ""
-            },
-            "require": {
-                "ext-xmlreader": "*",
-                "ext-zip": "*",
-                "php": ">=5.4.0"
-            },
-            "require-dev": {
-                "phpunit/phpunit": "^4.8.0"
-            },
-            "suggest": {
-                "ext-iconv": "To handle non UTF-8 CSV files (if \"php-intl\" is not already installed or is too limited)",
-                "ext-intl": "To handle non UTF-8 CSV files (if \"iconv\" is not already installed)"
-            },
-            "type": "library",
-            "extra": {
-                "branch-alias": {
-                    "dev-master": "2.8.x-dev"
-                }
-            },
-            "autoload": {
-                "psr-4": {
-                    "Box\\Spout\\": "src/Spout"
-                }
-            },
-            "notification-url": "https://packagist.org/downloads/",
-            "license": [
-                "Apache-2.0"
-            ],
-            "authors": [
-                {
-                    "name": "Adrien Loison",
-                    "email": "[email protected]"
-                }
-            ],
-            "description": "PHP Library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way",
-            "homepage": "https://www.github.com/box/spout",
-            "keywords": [
-                "OOXML",
-                "csv",
-                "excel",
-                "memory",
-                "odf",
-                "ods",
-                "office",
-                "open",
-                "php",
-                "read",
-                "scale",
-                "spreadsheet",
-                "stream",
-                "write",
-                "xlsx"
-            ],
-            "support": {
-                "issues": "https://github.com/box/spout/issues",
-                "source": "https://github.com/box/spout/tree/v2.7.3"
-            },
-            "abandoned": true,
-            "time": "2017-09-25T19:44:35+00:00"
-        },
         {
             "name": "brutusin/json-forms",
             "version": "1.4.0",

system/dbupdate_3.4/70376_lohnguide.php

@@ -264,8 +264,8 @@ CREATE TABLE IF NOT EXISTS hr.tbl_vertragsbestandteil_lohnguide (
     stellenbezeichnung varchar(255),
     fachrichtung_kurzbz character varying(32) NOT NULL,
     modellstelle_kurzbz character varying(32) NOT NULL,
-    kommentar_person text,
-    kommentar_modellstelle text,
+    kommentar_person varchar(255),
+    kommentar_modellstelle varchar(255),
     CONSTRAINT tbl_vertragsbestandteil_lohnguide_pk PRIMARY KEY (vertragsbestandteil_id),
 	CONSTRAINT tbl_vertragsbestandteil_fk FOREIGN KEY (vertragsbestandteil_id) REFERENCES hr.tbl_vertragsbestandteil (vertragsbestandteil_id) MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
 	CONSTRAINT tbl_vertragsbestandteil_lohnguide_fachrichtung_fk FOREIGN KEY (fachrichtung_kurzbz) REFERENCES hr.tbl_lohnguide_fachrichtung (fachrichtung_kurzbz) MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,