refactor

application/config/treemenu/stv_menu.php

@@ -0,0 +1,47 @@
+<?php
+if (! defined('BASEPATH')) exit('No direct script access allowed');
+
+$config['stv_menu'] = array(
+	'library' => 'treemenu/TreeMenuLib',
+	'children' => array(
+		'stg' => array(
+			'library' => 'treemenu/StgLib',
+			'children' => array(
+/*
+				'pre' => array(
+					'library' => 'treemenu/PrestudentLib'
+				),
+				'sem' => array(
+					'library' => 'treemenu/AusbSemesterLib',
+					'children' => array(
+						'vbd' => array(
+							'library' => 'treemenu/VerbandLib'
+						)
+					)
+				),
+*/
+				'orgform' => array(
+					'library' => 'treemenu/OrgFormLib',
+					'children' => array(
+/*
+						'pre' => array(
+							'library' => 'treemenu/PrestudentLib'
+						),
+						'sem' => array(
+							'library' => 'treemenu/AusbSemesterLib',
+							'children' => array(
+								'vbd' => array(
+									'library' => 'treemenu/VerbandLib'
+								)
+							)
+						)
+*/
+					)
+				)
+			)
+		),
+		'inout' => array(
+			'library' => 'treemenu/InOutLib'
+		),
+	)
+);

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

@@ -36,9 +36,7 @@ class Ort extends FHCAPI_Controller
 			'ContentID' => self::PERM_LOGGED,
 			'getOrtKurzbzContent' => self::PERM_LOGGED,
 			'getRooms' => self::PERM_LOGGED,
-			'getTypes' => self::PERM_LOGGED,
-			'getRoomsWithEmployeesAssigned' => 'basis/ort:r',
-			'getEmployeesWithRoomAssigned' => 'basis/ort:r'
+			'getTypes' => self::PERM_LOGGED
 		]);
 
 		$this->load->model('ressource/Ort_model', 'OrtModel');
@@ -176,42 +174,5 @@ 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/TreeMenu.php

@@ -0,0 +1,103 @@
+<?php
+/**
+ * Copyright (C) 2024 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');
+
+/**
+ * This controller operates between (interface) the JS (GUI) and the back-end
+ * Provides data to the ajax get calls about verbände
+ * This controller works with JSON calls on the HTTP GET or POST and the output is always JSON
+ */
+class TreeMenu extends FHCAPI_Controller
+{
+	protected $treemenuconfig;
+
+	public function __construct()
+	{
+		parent::__construct([
+			'fullMenu' => ['admin:r', 'assistenz:r'],
+			'partMenu' => ['admin:r', 'assistenz:r'],
+		]);
+	}
+
+	public function fullMenu($treemenu=null)
+	{
+		$includeSubMenu = true;
+		$this->getMenu($treemenu, $includeSubMenu);
+	}
+
+	public function partMenu($treemenu=null)
+	{
+		$includeSubMenu = false;
+		$this->getMenu($treemenu, $includeSubMenu);
+	}
+
+	protected function getMenu($treemenu=null, $includeSubMenu=false)
+	{
+		if(is_null($treemenu))
+		{
+			$this->terminateWithError('missing parameter treemenu.');
+		}
+
+		$this->loadMenuConfig($treemenu);
+
+		$uri = $this->uri->uri_to_assoc(7);
+		$startconfig = $this->findStartLib($this->treemenuconfig, array_keys($uri));
+
+		$libpath = $startconfig['library'];
+		$children = isset($startconfig['children']) ? $startconfig['children'] : array();
+		$libname = basename($startconfig['library']);
+
+		$this->load->library(
+			$libpath,
+			$children,
+			$libname
+		);
+
+		$bhdebug = (object) array(
+			'treemenu' => $treemenu,
+			'treemenuconfig' => $this->treemenuconfig,
+			'uri' => $uri,
+			'libpath' => $libpath,
+			'libname' => $libname,
+			'children' => $children,
+			'startconfig' => $startconfig
+		);
+		$this->addMeta('bhdebug', $bhdebug);
+		//$this->addMeta('bhci', $this);
+
+		$this->$libname->init($uri, $includeSubMenu);
+		$data = $this->$libname->getSubMenu();
+		$this->terminateWithSuccess($data);
+	}
+
+	protected function findStartLib($config, $uri)
+	{
+		$level = array_shift($uri);
+		if(is_null($level)) {
+			return $config;
+		}
+		return $this->findStartLib($config['children'][$level], $uri);
+	}
+
+	protected function loadMenuConfig($treemenu)
+	{
+		$this->config->load('treemenu/' . $treemenu . '.php');
+		$this->treemenuconfig = $this->config->item($treemenu);
+	}
+}

application/libraries/treemenu/InOutLib.php

@@ -0,0 +1,37 @@
+<?php
+require_once APPPATH . 'libraries/treemenu/TreeMenuLib.php';
+
+/**
+ * Description of InOutLib
+ *
+ * @author bambi
+ */
+class InOutLib extends TreeMenuLib
+{
+	public function getNodes($params=array(), $includeSubMenu=false)
+	{
+		return [
+			[
+				'name' => 'International',
+				'link' => 'inout',
+				'children' => [
+					[
+						'name' => 'Incoming',
+						'link' => 'inout/incoming',
+						'leaf' => true
+					],
+					[
+						'name' => 'Outgoing',
+						'link' => 'inout/outgoing',
+						'leaf' => true
+					],
+					[
+						'name' => 'Gemeinsame Studien',
+						'link' => 'inout/gemeinsamestudien',
+						'leaf' => true
+					]
+				]
+			]
+		];
+	}
+}

application/libraries/treemenu/OrgFormLib.php

@@ -0,0 +1,64 @@
+<?php
+require_once APPPATH . 'libraries/treemenu/TreeMenuLib.php';
+
+/**
+ * Description of OrgFormLib
+ *
+ * @author bambi
+ */
+class OrgFormLib extends TreeMenuLib
+{
+	public function getData()
+	{
+		if(!isset($this->params['stg']))
+		{
+			throw new Exception(self::class . ' Missing Parameter stg.');
+		}
+
+		$this->ci->load->model('codex/Orgform_model', 'OrgFormModel');
+
+		$sql = <<<EOSQL
+		select 
+			o.*
+		from
+			bis.tbl_orgform o
+		join (
+			SELECT 
+				tl.orgform_kurzbz 
+			FROM 
+				public.tbl_lehrverband AS tl
+			WHERE 
+				studiengang_kz = {$this->ci->OrgFormModel->escape($this->params['stg'])}
+				and 
+				orgform_kurzbz IS NOT null 
+			group by 
+				orgform_kurzbz
+		) v on o.orgform_kurzbz = v.orgform_kurzbz
+		order by
+			o.orgform_kurzbz
+EOSQL;
+
+		$res = $this->ci->OrgFormModel->execReadonlyQuery($sql);
+		$orgforms = hasData($res) ? getData($res) : array();
+
+		$this->ci->addMeta('bhorgform_' . $this->params['stg'] , $orgforms);
+
+		return $orgforms;
+	}
+
+	protected function getParamsForNextLevel($element=null)
+	{
+		if($element)
+		{
+			$paramsstg = array_merge($this->params, array('orgform' => $element->orgform_kurzbz));
+			return $paramsstg;
+		}
+		return $this->params;
+	}
+
+	protected function getName($element)
+	{
+		$name = $element->orgform_kurzbz . ' ' . $element->bezeichnung;
+		return $name;
+	}
+}

application/libraries/treemenu/PreStudentLib.php

@@ -0,0 +1,37 @@
+<?php
+require_once APPPATH . 'libraries/treemenu/TreeMenuLib.php';
+
+/**
+ * Description of PreStudentLib
+ *
+ * @author bambi
+ */
+class PreStudentLib extends TreeMenuLib
+{
+	public function getNodes($params=array())
+	{
+		return [
+			[
+				'name' => 'International',
+				'link' => 'inout',
+				'children' => [
+					[
+						'name' => 'Incoming',
+						'link' => 'inout/incoming',
+						'leaf' => true
+					],
+					[
+						'name' => 'Outgoing',
+						'link' => 'inout/outgoing',
+						'leaf' => true
+					],
+					[
+						'name' => 'Gemeinsame Studien',
+						'link' => 'inout/gemeinsamestudien',
+						'leaf' => true
+					]
+				]
+			]
+		];
+	}
+}

application/libraries/treemenu/StgLib.php

@@ -0,0 +1,39 @@
+<?php
+require_once APPPATH . 'libraries/treemenu/TreeMenuLib.php';
+
+/**
+ * Description of InOutLib
+ *
+ * @author bambi
+ */
+class StgLib extends TreeMenuLib
+{
+	protected function getData()
+	{
+		$this->ci->load->model('organisation/Studiengang_model', 'StudiengangModel');
+		$this->ci->StudiengangModel->addOrder('typ');
+		$this->ci->StudiengangModel->addOrder('kurzbz');
+		$res = $this->ci->StudiengangModel->loadWhere(array('aktiv' => true));
+		$stgs = hasData($res) ? getData($res) : array();
+
+		$this->ci->addMeta('bhstg', $stgs);
+
+		return $stgs;
+	}
+
+	protected function getParamsForNextLevel($element=null)
+	{
+		if($element)
+		{
+			$paramsstg = array_merge($this->params, array('stg' => $element->studiengang_kz));
+			return $paramsstg;
+		}
+		return $this->params;
+	}
+
+	protected function getName($element)
+	{
+		$name = strtoupper($element->typ . $element->kurzbz) . ' ' . $element->bezeichnung;
+		return $name;
+	}
+}

application/libraries/treemenu/StvMenuLib.php

@@ -0,0 +1,14 @@
+<?php
+require_once APPPATH . 'libraries/treemenu/TreeMenuLib.php';
+/**
+ * Description of StvMenuLib
+ *
+ * @author bambi
+ */
+class StvMenuLib extends TreeMenuLib
+{
+	public function getNodes($params=array(), $includeSubMenu=false)
+	{
+		return [];
+	}
+}

application/libraries/treemenu/TreeMenuLib.php

@@ -0,0 +1,108 @@
+<?php
+
+/*
+ * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
+ * Click nbfs://nbhost/SystemFileSystem/Templates/Scripting/PHPClass.php to edit this template
+ */
+
+/**
+ * Description of InOutLib
+ *
+ * @author bambi
+ */
+class TreeMenuLib
+{
+	protected $ci;
+	protected $children_config;
+	protected $params;
+	protected $includeSubMenu;
+
+	public function __construct($children_config)
+	{
+		$this->ci =& get_instance();
+		$this->children_config = $children_config;
+		$this->params = array();
+		$this->includeSubMenu = false;
+
+		foreach($this->children_config as $child_config)
+		{
+			$grandchildren_config = isset($child_config['children']) ? $child_config['children'] : [];
+			$this->ci->load->library($child_config['library'], $grandchildren_config, basename($child_config['library']));
+		}
+	}
+
+	public function init($params=array(), $includeSubMenu=false)
+	{
+		$this->params = $params;
+		$this->includeSubMenu = $includeSubMenu;
+	}
+
+	public function getNodes()
+	{
+		$data = $this->getData();
+
+		$nodes = array();
+		foreach($data as $element)
+		{
+			$node = array(
+					'name' => $this->getName($element),
+					'link' => $this->getLink($element),
+					'leaf' => $this->isLeaf()
+			);
+			if($this->includeSubMenu)
+			{
+				$node['children'] = $this->getSubMenu($element);
+			}
+			$nodes[] = $node;
+		};
+
+		return $nodes;
+	}
+
+	public function getSubMenu($element=null)
+	{
+		$nodes = array();
+
+		foreach($this->children_config as $childconfig)
+		{
+			$childlib = basename($childconfig['library']);
+			$this->ci->$childlib->init(
+				$this->getParamsForNextLevel($element),
+				$this->includeSubMenu
+			);
+			$childnodes = $this->ci->$childlib->getNodes();
+			$nodes = array_merge($nodes, $childnodes);
+		}
+
+		return $nodes;
+	}
+
+	protected function getData()
+	{
+		return array();
+	}
+
+	protected function getParamsForNextLevel($element=null)
+	{
+		return $this->params;
+	}
+
+	protected function getName($element)
+	{
+		return __METHOD__ . 'NOT IMPLEMENTED';
+	}
+
+	protected function getLink($element)
+	{
+		$link = $this->ci->uri->assoc_to_uri($this->getParamsForNextLevel($element));
+		return $link;
+	}
+
+	protected function isLeaf()
+	{
+		if(count($this->children_config) === 0) {
+			return true;
+		}
+		return false;
+	}
+}

application/libraries/vertragsbestandteil/AbstractBestandteil.php

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

application/libraries/vertragsbestandteil/VertragsbestandteilLohnguide.php

@@ -137,19 +137,25 @@ EOTXT;
 		return parent::__toString() . $txt;
 	}
 
-	/* public function validate()
+	public function validate()
 	{
-		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.';
+		$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.';
+			}
 		}
 		
 		return parent::validate();
-	} */
+	} 
 }

application/models/ressource/Ort_model.php

@@ -32,124 +32,4 @@ 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;
-
-	}
 }

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 varchar(255),
-    kommentar_modellstelle varchar(255),
+    kommentar_person text,
+    kommentar_modellstelle text,
     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,