-- ===================================================================== -- db/schema.sql — Struktur der LBM-Datenbank (Tabellen, Views, Prozeduren) -- ===================================================================== -- Zweck: die DB-Objekte in git sichtbar und versioniert halten — das -- frameworklose Pendant zu Laravel-Migrations. OHNE Daten (keine -- Personendaten), DEFINER-Klauseln entfernt (portabel re-importierbar). -- -- Neu erzeugen (aus einer vertrauenswuerdigen DB): -- ddev exec 'mysqldump --no-data --routines --skip-triggers \ -- --no-tablespaces --skip-dump-date -udb -pdb db' \ -- | sed -E 's/ AUTO_INCREMENT=[0-9]+//g; s/DEFINER=`[^`]*`@`[^`]*` ?//g' \ -- > db/schema.sql -- -- ACHTUNG Prozeduren: Dies ist der aktuelle DEV-Stand. procDeleteFolders ist -- die nicht-rekursive Fallback-Version aus .ddev/scripts/ensure-procs.php -- (loescht Unterordner NICHT mit); die echte Prod-Prozedur ist noch nicht -- bestaetigt (SHOW CREATE PROCEDURE auf prod ausfuehren). Siehe CHANGELOG. -- ===================================================================== /*M!999999\- enable the sandbox mode */ -- MariaDB dump 10.19-11.8.8-MariaDB, for debian-linux-gnu (aarch64) -- -- Host: db Database: db -- ------------------------------------------------------ -- Server version 11.8.8-MariaDB-ubu2404-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*M!100616 SET @OLD_NOTE_VERBOSITY=@@NOTE_VERBOSITY, NOTE_VERBOSITY=0 */; -- -- Table structure for table `pages` -- DROP TABLE IF EXISTS `pages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `pages` ( `uid` int(11) NOT NULL, `pid` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabAktion` -- DROP TABLE IF EXISTS `tabAktion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabAktion` ( `aktID` int(11) NOT NULL AUTO_INCREMENT, `aktAktion` varchar(50) DEFAULT NULL, `aktDeleted` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`aktID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabDokument` -- DROP TABLE IF EXISTS `tabDokument`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabDokument` ( `dokID` int(11) NOT NULL AUTO_INCREMENT, `dokFkOrdner` int(11) DEFAULT 0, `dokFkUser` int(11) NOT NULL, `dokDatum` date DEFAULT NULL, `dokName` varchar(100) NOT NULL, `dokExtension` varchar(16) NOT NULL, `dokSize` varchar(10) DEFAULT NULL, `dokDeleted` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`dokID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabFavorit` -- DROP TABLE IF EXISTS `tabFavorit`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabFavorit` ( `favFkUser` int(11) NOT NULL, `favFkProjekt` int(11) NOT NULL, UNIQUE KEY `index_tabFavorit` (`favFkUser`,`favFkProjekt`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabGemeinde` -- DROP TABLE IF EXISTS `tabGemeinde`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabGemeinde` ( `gemID` int(11) NOT NULL AUTO_INCREMENT, `gemName` varchar(50) DEFAULT NULL, `gemKanton` int(11) DEFAULT NULL, `gemDeleted` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`gemID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabKanton` -- DROP TABLE IF EXISTS `tabKanton`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabKanton` ( `kntID` int(11) NOT NULL AUTO_INCREMENT, `kntKanton` varchar(20) DEFAULT NULL, PRIMARY KEY (`kntID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabKategorie` -- DROP TABLE IF EXISTS `tabKategorie`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabKategorie` ( `katID` int(11) NOT NULL AUTO_INCREMENT, `katKategorie` varchar(100) NOT NULL, `katDeleted` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`katID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabLastVisited` -- DROP TABLE IF EXISTS `tabLastVisited`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabLastVisited` ( `lsvFkUser` int(11) NOT NULL, `lsvFkProjekt` int(11) NOT NULL, `lsvDate` datetime NOT NULL, PRIMARY KEY (`lsvFkUser`,`lsvFkProjekt`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabNotiz` -- DROP TABLE IF EXISTS `tabNotiz`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabNotiz` ( `notID` int(11) NOT NULL AUTO_INCREMENT, `notFkProjekt` int(11) NOT NULL, `notDatum` date NOT NULL, `notNotiz` text NOT NULL, `notFkUser` int(11) NOT NULL, `notDeleted` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`notID`), KEY `notFkProjekt` (`notFkProjekt`), CONSTRAINT `tabNotiz_ibfk_1` FOREIGN KEY (`notFkProjekt`) REFERENCES `tabProjekt` (`proID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabOrdner` -- DROP TABLE IF EXISTS `tabOrdner`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabOrdner` ( `ordID` int(11) NOT NULL AUTO_INCREMENT, `ordFkProjekt` int(11) NOT NULL, `ordParent` int(11) NOT NULL, `ordName` varchar(200) DEFAULT NULL, `ordFkUser` int(11) DEFAULT NULL, `ordDeleted` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`ordID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabOrgExtern` -- DROP TABLE IF EXISTS `tabOrgExtern`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabOrgExtern` ( `orgID` int(11) NOT NULL AUTO_INCREMENT, `orgName` varchar(50) NOT NULL, `orgDeleted` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`orgID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabPasswordReset` -- DROP TABLE IF EXISTS `tabPasswordReset`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `tabPasswordReset` ( `prsID` int(11) NOT NULL AUTO_INCREMENT, `prsFkUser` int(11) NOT NULL, `prsTokenHash` char(64) NOT NULL COMMENT 'sha256(token) hex', `prsExpires` datetime NOT NULL, `prsUsed` tinyint(1) NOT NULL DEFAULT 0, `prsCreated` datetime NOT NULL, `prsRequestIp` varchar(45) DEFAULT NULL, PRIMARY KEY (`prsID`), KEY `idxTokenHash` (`prsTokenHash`), KEY `idxUser` (`prsFkUser`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabProjekt` -- DROP TABLE IF EXISTS `tabProjekt`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabProjekt` ( `proID` int(11) NOT NULL AUTO_INCREMENT, `proName` varchar(100) DEFAULT NULL, `proGegenstand` varchar(255) DEFAULT NULL, `proDatum` date DEFAULT NULL, `proKanton` int(11) DEFAULT NULL, `proGemeinde` int(11) DEFAULT NULL, `proKategorie` int(11) DEFAULT NULL, `proStatus` tinyint(4) DEFAULT NULL, `proAktion` tinyint(4) DEFAULT NULL, `proLeiter` int(11) DEFAULT NULL, `proNummer` varchar(20) DEFAULT '', `proFkOrdner` int(11) DEFAULT NULL, `proErfasser` int(11) DEFAULT 0, `proBemerkung` varchar(255) DEFAULT NULL, `proDeleted` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`proID`), KEY `index_proKanton` (`proKanton`), KEY `index_proGemeinde` (`proGemeinde`), KEY `index_proLeiter` (`proLeiter`), KEY `index_proErfasser` (`proErfasser`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabProjektOrganisation` -- DROP TABLE IF EXISTS `tabProjektOrganisation`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabProjektOrganisation` ( `fkProjekt` int(11) NOT NULL, `fkExterne` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabProjektUser` -- DROP TABLE IF EXISTS `tabProjektUser`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabProjektUser` ( `pruID` int(11) NOT NULL AUTO_INCREMENT, `pruFkProjekt` int(11) NOT NULL, `pruFkUser` int(11) NOT NULL, `pruDatum` date DEFAULT curdate(), PRIMARY KEY (`pruID`), UNIQUE KEY `unique_projekt_user` (`pruFkProjekt`,`pruFkUser`), KEY `pruFkUser` (`pruFkUser`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabStatus` -- DROP TABLE IF EXISTS `tabStatus`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabStatus` ( `staID` int(11) NOT NULL AUTO_INCREMENT, `staStatus` varchar(20) DEFAULT NULL, `staDeleted` tinyint(4) NOT NULL DEFAULT 0, `staIstAbschluss` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'markiert den Status, den der Button Projekt abschliessen setzt', PRIMARY KEY (`staID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabTermin` -- DROP TABLE IF EXISTS `tabTermin`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabTermin` ( `terID` int(11) NOT NULL AUTO_INCREMENT, `terFkProjekt` int(11) NOT NULL, `terDatum` datetime NOT NULL, `terKategorie` enum('Frist','Sitzung') NOT NULL, `terTitel` varchar(255) NOT NULL, `terOrt` varchar(255) DEFAULT NULL, `terEmailGesendet` tinyint(1) DEFAULT 0, `terDeleted` tinyint(1) DEFAULT 0, `terCreated` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`terID`), KEY `terFkProjekt` (`terFkProjekt`), CONSTRAINT `tabTermin_ibfk_1` FOREIGN KEY (`terFkProjekt`) REFERENCES `tabProjekt` (`proID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabTerminUser` -- DROP TABLE IF EXISTS `tabTerminUser`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabTerminUser` ( `teuID` int(11) NOT NULL AUTO_INCREMENT, `teuFkTermin` int(11) NOT NULL, `teuFkUser` int(11) NOT NULL, PRIMARY KEY (`teuID`), KEY `teuFkTermin` (`teuFkTermin`), KEY `teuFkUser` (`teuFkUser`), CONSTRAINT `tabTerminUser_ibfk_1` FOREIGN KEY (`teuFkTermin`) REFERENCES `tabTermin` (`terID`) ON DELETE CASCADE, CONSTRAINT `tabTerminUser_ibfk_2` FOREIGN KEY (`teuFkUser`) REFERENCES `tabUser` (`usrID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabUserPref` -- Benutzerbezogene UI-Präferenzen als Key/Value (z.B. Klappzustand der -- Startseiten-Übersicht). Bewusst utf8mb4 wie tabTermin; Schlüssel sind ASCII. -- DROP TABLE IF EXISTS `tabUserPref`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabUserPref` ( `uprFkUser` int(11) NOT NULL, `uprKey` varchar(64) NOT NULL, `uprValue` text NOT NULL, PRIMARY KEY (`uprFkUser`,`uprKey`), CONSTRAINT `tabUserPref_ibfk_1` FOREIGN KEY (`uprFkUser`) REFERENCES `tabUser` (`usrID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabLoginAttempt` -- Fehlgeschlagene Login-Versuche je Schlüssel (IP) mit Zeitstempel, zur -- Ratenbegrenzung/Brute-Force-Bremse. Eine Zeile pro Fehlversuch; alte Zeilen -- werden beim Prüfen aufgeräumt. ASCII-Daten, utf8mb4 wie tabUserPref. -- DROP TABLE IF EXISTS `tabLoginAttempt`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabLoginAttempt` ( `latID` int(11) NOT NULL AUTO_INCREMENT, `latKey` varchar(64) NOT NULL, `latTime` datetime NOT NULL, PRIMARY KEY (`latID`), KEY `latKey` (`latKey`,`latTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tabUser` -- DROP TABLE IF EXISTS `tabUser`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tabUser` ( `usrID` int(11) NOT NULL AUTO_INCREMENT, `usrLogin` varchar(255) DEFAULT NULL, `usrPass` varchar(255) DEFAULT NULL, `usrFirstName` varchar(60) DEFAULT NULL, `usrLastName` varchar(60) DEFAULT NULL, `usrEmail` varchar(255) DEFAULT NULL, `usrNickName` varchar(3) DEFAULT NULL, `usrDeleted` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`usrID`), UNIQUE KEY `usrLogin` (`usrLogin`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `viewBpMain` -- DROP TABLE IF EXISTS `viewBpMain`; /*!50001 DROP VIEW IF EXISTS `viewBpMain`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewBpMain` AS SELECT NULL AS `proID`, NULL AS `proName`, NULL AS `proGegenstand`, NULL AS `proDatum`, NULL AS `proNummer`, NULL AS `kntKanton`, NULL AS `gemName`, NULL AS `kntID`, NULL AS `gemID`, NULL AS `proZuletztBearbeitet`, NULL AS `proZuletztBearbeitetFormatiert` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewComment` -- DROP TABLE IF EXISTS `viewComment`; /*!50001 DROP VIEW IF EXISTS `viewComment`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewComment` AS SELECT NULL AS `notID`, NULL AS `notDatum`, NULL AS `notNotiz`, NULL AS `notFkProjekt`, NULL AS `notDeleted`, NULL AS `usrName` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewDokument` -- DROP TABLE IF EXISTS `viewDokument`; /*!50001 DROP VIEW IF EXISTS `viewDokument`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewDokument` AS SELECT NULL AS `dokID`, NULL AS `dokName`, NULL AS `dokDatum`, NULL AS `dokExtension`, NULL AS `FusrName`, NULL AS `dokFkOrdner`, NULL AS `dokSize` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewExtOrg` -- DROP TABLE IF EXISTS `viewExtOrg`; /*!50001 DROP VIEW IF EXISTS `viewExtOrg`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewExtOrg` AS SELECT NULL AS `fkProjekt`, NULL AS `orgName` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewFavorit` -- DROP TABLE IF EXISTS `viewFavorit`; /*!50001 DROP VIEW IF EXISTS `viewFavorit`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewFavorit` AS SELECT NULL AS `favFkUser`, NULL AS `proID`, NULL AS `proName`, NULL AS `proGegenstand`, NULL AS `proNummer`, NULL AS `proDatum`, NULL AS `proZuletztBearbeitet`, NULL AS `proZuletztBearbeitetFormatiert` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewLastNumbers` -- DROP TABLE IF EXISTS `viewLastNumbers`; /*!50001 DROP VIEW IF EXISTS `viewLastNumbers`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewLastNumbers` AS SELECT NULL AS `proNummer` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewLastVisited` -- DROP TABLE IF EXISTS `viewLastVisited`; /*!50001 DROP VIEW IF EXISTS `viewLastVisited`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewLastVisited` AS SELECT NULL AS `lsvFkUser`, NULL AS `proID`, NULL AS `proName`, NULL AS `proGegenstand`, NULL AS `proNummer`, NULL AS `proDatum`, NULL AS `proZuletztBearbeitet`, NULL AS `proZuletztBearbeitetFormatiert` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewNewsDokument` -- DROP TABLE IF EXISTS `viewNewsDokument`; /*!50001 DROP VIEW IF EXISTS `viewNewsDokument`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewNewsDokument` AS SELECT NULL AS `dokName`, NULL AS `dokFDatum`, NULL AS `proErfasser`, NULL AS `dokFkOrdner`, NULL AS `ordFkProjekt` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewNewsNotiz` -- DROP TABLE IF EXISTS `viewNewsNotiz`; /*!50001 DROP VIEW IF EXISTS `viewNewsNotiz`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewNewsNotiz` AS SELECT NULL AS `notFkProjekt`, NULL AS `notFDatum`, NULL AS `notNotiz`, NULL AS `proErfasser` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewNewsProjekt` -- DROP TABLE IF EXISTS `viewNewsProjekt`; /*!50001 DROP VIEW IF EXISTS `viewNewsProjekt`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewNewsProjekt` AS SELECT NULL AS `proID`, NULL AS `proFDatum`, NULL AS `proName`, NULL AS `proErfasser` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewOrdnerInfo` -- DROP TABLE IF EXISTS `viewOrdnerInfo`; /*!50001 DROP VIEW IF EXISTS `viewOrdnerInfo`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewOrdnerInfo` AS SELECT NULL AS `ordID`, NULL AS `ordName`, NULL AS `ordParent`, NULL AS `FAnzOrd`, NULL AS `FAnzDoks` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewProjektFull` -- DROP TABLE IF EXISTS `viewProjektFull`; /*!50001 DROP VIEW IF EXISTS `viewProjektFull`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewProjektFull` AS SELECT NULL AS `proID`, NULL AS `proName`, NULL AS `proGegenstand`, NULL AS `proDatum`, NULL AS `proNummer`, NULL AS `proFkOrdner`, NULL AS `proBemerkung`, NULL AS `proErfasser`, NULL AS `kntKanton`, NULL AS `gemName`, NULL AS `kntID`, NULL AS `gemID`, NULL AS `usrID`, NULL AS `statusID`, NULL AS `aktionID`, NULL AS `proStatus`, NULL AS `proAktion`, NULL AS `katID`, NULL AS `proKategorie`, NULL AS `usrName`, NULL AS `proZuletztBearbeitet`, NULL AS `proZuletztBearbeitetFormatiert`, NULL AS `proZugewieseneUser` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSearch` -- DROP TABLE IF EXISTS `viewSearch`; /*!50001 DROP VIEW IF EXISTS `viewSearch`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSearch` AS SELECT NULL AS `proID`, NULL AS `proName`, NULL AS `proGegenstand`, NULL AS `proDatum`, NULL AS `proFDatum`, NULL AS `proNummer`, NULL AS `proFkOrdner`, NULL AS `proBemerkung`, NULL AS `proErfasser`, NULL AS `kntKanton`, NULL AS `gemName`, NULL AS `kntID`, NULL AS `gemID`, NULL AS `usrID`, NULL AS `statusID`, NULL AS `aktionID`, NULL AS `proStatus`, NULL AS `proAktion`, NULL AS `katID`, NULL AS `katKategorie`, NULL AS `usrName`, NULL AS `proZuletztBearbeitet`, NULL AS `proZuletztBearbeitetFormatiert` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSettingsExterne` -- DROP TABLE IF EXISTS `viewSettingsExterne`; /*!50001 DROP VIEW IF EXISTS `viewSettingsExterne`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSettingsExterne` AS SELECT NULL AS `itemID`, NULL AS `itemName`, NULL AS `itemDeleted`, NULL AS `itemCount` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSettingsGemeinde` -- DROP TABLE IF EXISTS `viewSettingsGemeinde`; /*!50001 DROP VIEW IF EXISTS `viewSettingsGemeinde`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSettingsGemeinde` AS SELECT NULL AS `itemID`, NULL AS `itemName`, NULL AS `itemDeleted`, NULL AS `itemCount`, NULL AS `gemKanton` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSettingsKat` -- DROP TABLE IF EXISTS `viewSettingsKat`; /*!50001 DROP VIEW IF EXISTS `viewSettingsKat`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSettingsKat` AS SELECT NULL AS `itemID`, NULL AS `itemName`, NULL AS `itemDeleted`, NULL AS `itemCount` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSettingsMassnahme` -- DROP TABLE IF EXISTS `viewSettingsMassnahme`; /*!50001 DROP VIEW IF EXISTS `viewSettingsMassnahme`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSettingsMassnahme` AS SELECT NULL AS `itemID`, NULL AS `itemName`, NULL AS `itemDeleted`, NULL AS `itemCount` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSettingsStatus` -- DROP TABLE IF EXISTS `viewSettingsStatus`; /*!50001 DROP VIEW IF EXISTS `viewSettingsStatus`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSettingsStatus` AS SELECT NULL AS `itemID`, NULL AS `itemName`, NULL AS `itemDeleted`, NULL AS `itemCount` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSubAnzahlDoks` -- DROP TABLE IF EXISTS `viewSubAnzahlDoks`; /*!50001 DROP VIEW IF EXISTS `viewSubAnzahlDoks`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSubAnzahlDoks` AS SELECT NULL AS `ordID`, NULL AS `AnzDoks` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewSubAnzahlOrdner` -- DROP TABLE IF EXISTS `viewSubAnzahlOrdner`; /*!50001 DROP VIEW IF EXISTS `viewSubAnzahlOrdner`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewSubAnzahlOrdner` AS SELECT NULL AS `ordParent`, NULL AS `AnzOrd` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `viewTermin` -- DROP TABLE IF EXISTS `viewTermin`; /*!50001 DROP VIEW IF EXISTS `viewTermin`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `viewTermin` AS SELECT NULL AS `terID`, NULL AS `terFkProjekt`, NULL AS `terDatum`, NULL AS `terDatumFormatiert`, NULL AS `terKategorie`, NULL AS `terTitel`, NULL AS `terOrt`, NULL AS `terEmailGesendet`, NULL AS `proName`, NULL AS `terMitarbeitende`, NULL AS `terMitarbeitendeIDs` */; SET character_set_client = @saved_cs_client; -- -- Dumping routines for database 'db' -- /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `procDeleteFolders` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE PROCEDURE `procDeleteFolders`(IN pOrd INT) BEGIN UPDATE tabOrdner SET ordDeleted = 1 WHERE ordID = pOrd; UPDATE tabDokument SET dokDeleted = 1 WHERE dokFkOrdner = pOrd; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `procDeleteProjekt` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE PROCEDURE `procDeleteProjekt`(IN pPro INT) BEGIN UPDATE tabProjekt SET proDeleted = 1 WHERE proID = pPro; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `procDeleteSingleFile` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE PROCEDURE `procDeleteSingleFile`(IN pDok INT) BEGIN UPDATE tabDokument SET dokDeleted = 1 WHERE dokID = pDok; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `procLastVisited` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE PROCEDURE `procLastVisited`(IN pUser INT, IN pProjekt INT) BEGIN DELETE FROM tabLastVisited WHERE lsvFkUser = pUser AND lsvFkProjekt = pProjekt; INSERT INTO tabLastVisited (lsvFkUser, lsvFkProjekt, lsvDate) VALUES (pUser, pProjekt, NOW()); END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Final view structure for view `viewBpMain` -- /*!50001 DROP VIEW IF EXISTS `viewBpMain`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewBpMain` AS select `tabProjekt`.`proID` AS `proID`,`tabProjekt`.`proName` AS `proName`,`tabProjekt`.`proGegenstand` AS `proGegenstand`,if(`tabProjekt`.`proDatum` = '0000-00-00','',date_format(`tabProjekt`.`proDatum`,'%d.%m.%Y')) AS `proDatum`,`tabProjekt`.`proNummer` AS `proNummer`,`tabKanton`.`kntKanton` AS `kntKanton`,`tabGemeinde`.`gemName` AS `gemName`,`tabKanton`.`kntID` AS `kntID`,`tabGemeinde`.`gemID` AS `gemID`,greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) AS `proZuletztBearbeitet`,if(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) = '0000-00-00','',date_format(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')),'%d.%m.%Y')) AS `proZuletztBearbeitetFormatiert` from ((`tabProjekt` left join `tabKanton` on(`tabProjekt`.`proKanton` = `tabKanton`.`kntID`)) left join `tabGemeinde` on(`tabProjekt`.`proGemeinde` = `tabGemeinde`.`gemID`)) where `tabProjekt`.`proDeleted` = 0 order by `tabKanton`.`kntKanton`,`tabGemeinde`.`gemName`,`tabProjekt`.`proID` desc */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewComment` -- /*!50001 DROP VIEW IF EXISTS `viewComment`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewComment` AS select `tabNotiz`.`notID` AS `notID`,date_format(`tabNotiz`.`notDatum`,'%d.%m.%Y') AS `notDatum`,`tabNotiz`.`notNotiz` AS `notNotiz`,`tabNotiz`.`notFkProjekt` AS `notFkProjekt`,`tabNotiz`.`notDeleted` AS `notDeleted`,concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) AS `usrName` from (`tabNotiz` left join `tabUser` on(`tabNotiz`.`notFkUser` = `tabUser`.`usrID`)) where `tabNotiz`.`notDeleted` = 0 order by `tabNotiz`.`notDatum` desc,`tabNotiz`.`notID` desc */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewDokument` -- /*!50001 DROP VIEW IF EXISTS `viewDokument`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewDokument` AS select `tabDokument`.`dokID` AS `dokID`,`tabDokument`.`dokName` AS `dokName`,if(`tabDokument`.`dokDatum` = '0000-00-00','',date_format(`tabDokument`.`dokDatum`,'%d.%m.%Y')) AS `dokDatum`,`tabDokument`.`dokExtension` AS `dokExtension`,concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) AS `FusrName`,`tabDokument`.`dokFkOrdner` AS `dokFkOrdner`,`tabDokument`.`dokSize` AS `dokSize` from (`tabDokument` join `tabUser` on(`tabDokument`.`dokFkUser` = `tabUser`.`usrID`)) where `tabDokument`.`dokDeleted` = 0 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewExtOrg` -- /*!50001 DROP VIEW IF EXISTS `viewExtOrg`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewExtOrg` AS select `tabProjektOrganisation`.`fkProjekt` AS `fkProjekt`,`tabOrgExtern`.`orgName` AS `orgName` from (`tabProjektOrganisation` left join `tabOrgExtern` on(`tabProjektOrganisation`.`fkExterne` = `tabOrgExtern`.`orgID`)) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewFavorit` -- /*!50001 DROP VIEW IF EXISTS `viewFavorit`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewFavorit` AS select `tabFavorit`.`favFkUser` AS `favFkUser`,`tabProjekt`.`proID` AS `proID`,`tabProjekt`.`proName` AS `proName`,`tabProjekt`.`proGegenstand` AS `proGegenstand`,`tabProjekt`.`proNummer` AS `proNummer`,if(`tabProjekt`.`proDatum` = '0000-00-00','',date_format(`tabProjekt`.`proDatum`,'%d.%m.%Y')) AS `proDatum`,greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) AS `proZuletztBearbeitet`,if(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) = '0000-00-00','',date_format(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')),'%d.%m.%Y')) AS `proZuletztBearbeitetFormatiert` from (`tabFavorit` join `tabProjekt` on(`tabFavorit`.`favFkProjekt` = `tabProjekt`.`proID`)) order by `tabProjekt`.`proName` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewLastNumbers` -- /*!50001 DROP VIEW IF EXISTS `viewLastNumbers`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewLastNumbers` AS select `tabProjekt`.`proNummer` AS `proNummer` from `tabProjekt` where `tabProjekt`.`proNummer` <> '' order by `tabProjekt`.`proID` desc limit 0,5 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewLastVisited` -- /*!50001 DROP VIEW IF EXISTS `viewLastVisited`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewLastVisited` AS select `tabLastVisited`.`lsvFkUser` AS `lsvFkUser`,`tabProjekt`.`proID` AS `proID`,`tabProjekt`.`proName` AS `proName`,`tabProjekt`.`proGegenstand` AS `proGegenstand`,`tabProjekt`.`proNummer` AS `proNummer`,if(`tabProjekt`.`proDatum` = '0000-00-00','',date_format(`tabProjekt`.`proDatum`,'%d.%m.%Y')) AS `proDatum`,greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) AS `proZuletztBearbeitet`,if(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) = '0000-00-00','',date_format(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')),'%d.%m.%Y')) AS `proZuletztBearbeitetFormatiert` from (`tabLastVisited` join `tabProjekt` on(`tabLastVisited`.`lsvFkProjekt` = `tabProjekt`.`proID`)) where `tabProjekt`.`proDeleted` = 0 order by `tabLastVisited`.`lsvDate` desc */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewNewsDokument` -- /*!50001 DROP VIEW IF EXISTS `viewNewsDokument`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewNewsDokument` AS select `tabDokument`.`dokName` AS `dokName`,if(`tabDokument`.`dokDatum` = '0000-00-00','',date_format(`tabDokument`.`dokDatum`,'%d.%m.%Y')) AS `dokFDatum`,concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) AS `proErfasser`,`tabDokument`.`dokFkOrdner` AS `dokFkOrdner`,`tabOrdner`.`ordFkProjekt` AS `ordFkProjekt` from ((`tabOrdner` join `tabDokument` on(`tabOrdner`.`ordID` = `tabDokument`.`dokFkOrdner`)) join `tabUser` on(`tabDokument`.`dokFkUser` = `tabUser`.`usrID`)) where `tabDokument`.`dokDeleted` = 0 order by `tabDokument`.`dokDatum` desc limit 0,10 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewNewsNotiz` -- /*!50001 DROP VIEW IF EXISTS `viewNewsNotiz`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewNewsNotiz` AS select `tabNotiz`.`notFkProjekt` AS `notFkProjekt`,if(`tabNotiz`.`notDatum` = '0000-00-00','',date_format(`tabNotiz`.`notDatum`,'%d.%m.%Y')) AS `notFDatum`,`tabNotiz`.`notNotiz` AS `notNotiz`,concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) AS `proErfasser` from (`tabNotiz` join `tabUser` on(`tabNotiz`.`notFkUser` = `tabUser`.`usrID`)) where `tabNotiz`.`notDeleted` = 0 order by `tabNotiz`.`notDatum` desc limit 0,5 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewNewsProjekt` -- /*!50001 DROP VIEW IF EXISTS `viewNewsProjekt`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewNewsProjekt` AS select `tabProjekt`.`proID` AS `proID`,if(`tabProjekt`.`proDatum` = '0000-00-00','',date_format(`tabProjekt`.`proDatum`,'%d.%m.%Y')) AS `proFDatum`,`tabProjekt`.`proName` AS `proName`,concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) AS `proErfasser` from (`tabProjekt` join `tabUser` on(`tabProjekt`.`proErfasser` = `tabUser`.`usrID`)) where `tabProjekt`.`proDeleted` = 0 order by `tabProjekt`.`proDatum` desc limit 0,5 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewOrdnerInfo` -- /*!50001 DROP VIEW IF EXISTS `viewOrdnerInfo`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewOrdnerInfo` AS select `tabOrdner`.`ordID` AS `ordID`,`tabOrdner`.`ordName` AS `ordName`,`tabOrdner`.`ordParent` AS `ordParent`,if(`viewSubAnzahlOrdner`.`AnzOrd` is null,0,`viewSubAnzahlOrdner`.`AnzOrd`) AS `FAnzOrd`,if(`viewSubAnzahlDoks`.`AnzDoks` is null,0,`viewSubAnzahlDoks`.`AnzDoks`) AS `FAnzDoks` from ((`tabOrdner` left join `viewSubAnzahlDoks` on(`tabOrdner`.`ordID` = `viewSubAnzahlDoks`.`ordID`)) left join `viewSubAnzahlOrdner` on(`tabOrdner`.`ordID` = `viewSubAnzahlOrdner`.`ordParent`)) where `tabOrdner`.`ordDeleted` = 0 group by `tabOrdner`.`ordID`,`tabOrdner`.`ordName`,`tabOrdner`.`ordParent`,if(`viewSubAnzahlOrdner`.`AnzOrd` is null,0,`viewSubAnzahlOrdner`.`AnzOrd`),if(`viewSubAnzahlDoks`.`AnzDoks` is null,0,`viewSubAnzahlDoks`.`AnzDoks`) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewProjektFull` -- /*!50001 DROP VIEW IF EXISTS `viewProjektFull`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewProjektFull` AS select `tabProjekt`.`proID` AS `proID`,`tabProjekt`.`proName` AS `proName`,`tabProjekt`.`proGegenstand` AS `proGegenstand`,if(`tabProjekt`.`proDatum` = '0000-00-00','',date_format(`tabProjekt`.`proDatum`,'%d.%m.%Y')) AS `proDatum`,`tabProjekt`.`proNummer` AS `proNummer`,`tabProjekt`.`proFkOrdner` AS `proFkOrdner`,`tabProjekt`.`proBemerkung` AS `proBemerkung`,(select concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) from `tabUser` where `tabUser`.`usrID` = `tabProjekt`.`proErfasser`) AS `proErfasser`,`tabKanton`.`kntKanton` AS `kntKanton`,`tabGemeinde`.`gemName` AS `gemName`,`tabKanton`.`kntID` AS `kntID`,`tabGemeinde`.`gemID` AS `gemID`,`tabUser`.`usrID` AS `usrID`,`tabStatus`.`staID` AS `statusID`,`tabAktion`.`aktID` AS `aktionID`,`tabStatus`.`staStatus` AS `proStatus`,`tabAktion`.`aktAktion` AS `proAktion`,`tabKategorie`.`katID` AS `katID`,`tabKategorie`.`katKategorie` AS `proKategorie`,concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) AS `usrName`,greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) AS `proZuletztBearbeitet`,if(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) = '0000-00-00','',date_format(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')),'%d.%m.%Y')) AS `proZuletztBearbeitetFormatiert`,(select group_concat(concat(`u`.`usrFirstName`,' ',`u`.`usrLastName`) separator ', ') from (`tabProjektUser` `pu` join `tabUser` `u` on(`pu`.`pruFkUser` = `u`.`usrID`)) where `pu`.`pruFkProjekt` = `tabProjekt`.`proID` and `u`.`usrDeleted` = 0) AS `proZugewieseneUser` from ((((((`tabProjekt` left join `tabKanton` on(`tabKanton`.`kntID` = `tabProjekt`.`proKanton`)) left join `tabGemeinde` on(`tabProjekt`.`proGemeinde` = `tabGemeinde`.`gemID`)) left join `tabAktion` on(`tabProjekt`.`proAktion` = `tabAktion`.`aktID`)) left join `tabStatus` on(`tabProjekt`.`proStatus` = `tabStatus`.`staID`)) left join `tabUser` on(`tabProjekt`.`proLeiter` = `tabUser`.`usrID`)) left join `tabKategorie` on(`tabProjekt`.`proKategorie` = `tabKategorie`.`katID`)) where `tabProjekt`.`proDeleted` = 0 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSearch` -- /*!50001 DROP VIEW IF EXISTS `viewSearch`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSearch` AS select `tabProjekt`.`proID` AS `proID`,`tabProjekt`.`proName` AS `proName`,`tabProjekt`.`proGegenstand` AS `proGegenstand`,`tabProjekt`.`proDatum` AS `proDatum`,if(`tabProjekt`.`proDatum` = '0000-00-00','',date_format(`tabProjekt`.`proDatum`,'%d.%m.%Y')) AS `proFDatum`,`tabProjekt`.`proNummer` AS `proNummer`,`tabProjekt`.`proFkOrdner` AS `proFkOrdner`,`tabProjekt`.`proBemerkung` AS `proBemerkung`,(select concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) from `tabUser` where `tabUser`.`usrID` = `tabProjekt`.`proErfasser`) AS `proErfasser`,`tabKanton`.`kntKanton` AS `kntKanton`,`tabGemeinde`.`gemName` AS `gemName`,`tabKanton`.`kntID` AS `kntID`,`tabGemeinde`.`gemID` AS `gemID`,`tabUser`.`usrID` AS `usrID`,`tabStatus`.`staID` AS `statusID`,`tabAktion`.`aktID` AS `aktionID`,`tabStatus`.`staStatus` AS `proStatus`,`tabAktion`.`aktAktion` AS `proAktion`,`tabKategorie`.`katID` AS `katID`,`tabKategorie`.`katKategorie` AS `katKategorie`,concat(`tabUser`.`usrFirstName`,' ',`tabUser`.`usrLastName`) AS `usrName`,greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) AS `proZuletztBearbeitet`,if(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')) = '0000-00-00','',date_format(greatest(coalesce((select max(`n`.`notDatum`) from `tabNotiz` `n` where `n`.`notFkProjekt` = `tabProjekt`.`proID` and `n`.`notDeleted` = 0),'0000-00-00'),coalesce((select max(`d`.`dokDatum`) from `tabDokument` `d` where `d`.`dokFkOrdner` = `tabProjekt`.`proFkOrdner` and `d`.`dokDeleted` = 0),'0000-00-00')),'%d.%m.%Y')) AS `proZuletztBearbeitetFormatiert` from ((((((`tabProjekt` left join `tabKanton` on(`tabKanton`.`kntID` = `tabProjekt`.`proKanton`)) left join `tabGemeinde` on(`tabProjekt`.`proGemeinde` = `tabGemeinde`.`gemID`)) left join `tabAktion` on(`tabProjekt`.`proAktion` = `tabAktion`.`aktID`)) left join `tabStatus` on(`tabProjekt`.`proStatus` = `tabStatus`.`staID`)) left join `tabKategorie` on(`tabProjekt`.`proKategorie` = `tabKategorie`.`katID`)) left join `tabUser` on(`tabProjekt`.`proLeiter` = `tabUser`.`usrID`)) where `tabProjekt`.`proDeleted` = 0 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSettingsExterne` -- /*!50001 DROP VIEW IF EXISTS `viewSettingsExterne`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSettingsExterne` AS select `tabOrgExtern`.`orgID` AS `itemID`,`tabOrgExtern`.`orgName` AS `itemName`,`tabOrgExtern`.`orgDeleted` AS `itemDeleted`,count(`tabProjektOrganisation`.`fkExterne`) AS `itemCount` from (`tabOrgExtern` left join `tabProjektOrganisation` on(`tabProjektOrganisation`.`fkExterne` = `tabOrgExtern`.`orgID`)) group by `tabOrgExtern`.`orgID`,`tabOrgExtern`.`orgName` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSettingsGemeinde` -- /*!50001 DROP VIEW IF EXISTS `viewSettingsGemeinde`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSettingsGemeinde` AS select `tabGemeinde`.`gemID` AS `itemID`,`tabGemeinde`.`gemName` AS `itemName`,`tabGemeinde`.`gemDeleted` AS `itemDeleted`,sum(if(`tabProjekt`.`proDeleted` = 0,1,0)) AS `itemCount`,`tabGemeinde`.`gemKanton` AS `gemKanton` from (`tabGemeinde` left join `tabProjekt` on(`tabProjekt`.`proGemeinde` = `tabGemeinde`.`gemID`)) group by `tabGemeinde`.`gemID`,`tabGemeinde`.`gemName`,`tabGemeinde`.`gemDeleted`,`tabGemeinde`.`gemKanton` order by `tabGemeinde`.`gemName` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSettingsKat` -- /*!50001 DROP VIEW IF EXISTS `viewSettingsKat`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSettingsKat` AS select `tabKategorie`.`katID` AS `itemID`,`tabKategorie`.`katKategorie` AS `itemName`,`tabKategorie`.`katDeleted` AS `itemDeleted`,count(`tabProjekt`.`proID`) AS `itemCount` from (`tabKategorie` left join `tabProjekt` on(`tabKategorie`.`katID` = `tabProjekt`.`proKategorie`)) where `tabProjekt`.`proDeleted` = 0 or `tabProjekt`.`proDeleted` is null group by `tabKategorie`.`katID`,`tabKategorie`.`katKategorie`,`tabKategorie`.`katDeleted` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSettingsMassnahme` -- /*!50001 DROP VIEW IF EXISTS `viewSettingsMassnahme`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSettingsMassnahme` AS select `tabAktion`.`aktID` AS `itemID`,`tabAktion`.`aktAktion` AS `itemName`,`tabAktion`.`aktDeleted` AS `itemDeleted`,count(`tabProjekt`.`proID`) AS `itemCount` from (`tabAktion` left join `tabProjekt` on(`tabAktion`.`aktID` = `tabProjekt`.`proAktion`)) where `tabProjekt`.`proDeleted` = 0 or `tabProjekt`.`proDeleted` is null group by `tabAktion`.`aktID`,`tabAktion`.`aktAktion`,`tabAktion`.`aktDeleted` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSettingsStatus` -- /*!50001 DROP VIEW IF EXISTS `viewSettingsStatus`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSettingsStatus` AS select `tabStatus`.`staID` AS `itemID`,`tabStatus`.`staStatus` AS `itemName`,`tabStatus`.`staDeleted` AS `itemDeleted`,count(`tabProjekt`.`proID`) AS `itemCount` from (`tabStatus` left join `tabProjekt` on(`tabProjekt`.`proStatus` = `tabStatus`.`staID`)) where `tabProjekt`.`proDeleted` = 0 or `tabProjekt`.`proDeleted` is null group by `tabStatus`.`staID`,`tabStatus`.`staStatus`,`tabStatus`.`staDeleted` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSubAnzahlDoks` -- /*!50001 DROP VIEW IF EXISTS `viewSubAnzahlDoks`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSubAnzahlDoks` AS select `tabOrdner`.`ordID` AS `ordID`,if(count(`tabDokument`.`dokID`) = 0,0,count(`tabDokument`.`dokID`)) AS `AnzDoks` from (`tabOrdner` left join `tabDokument` on(`tabDokument`.`dokFkOrdner` = `tabOrdner`.`ordID`)) where `tabDokument`.`dokDeleted` = 0 group by `tabOrdner`.`ordID` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewSubAnzahlOrdner` -- /*!50001 DROP VIEW IF EXISTS `viewSubAnzahlOrdner`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewSubAnzahlOrdner` AS select `tabOrdner`.`ordParent` AS `ordParent`,count(`tabOrdner`.`ordParent`) AS `AnzOrd` from `tabOrdner` where `tabOrdner`.`ordDeleted` = 0 group by `tabOrdner`.`ordParent` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `viewTermin` -- /*!50001 DROP VIEW IF EXISTS `viewTermin`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_520_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 SQL SECURITY DEFINER */ /*!50001 VIEW `viewTermin` AS select `t`.`terID` AS `terID`,`t`.`terFkProjekt` AS `terFkProjekt`,`t`.`terDatum` AS `terDatum`,date_format(`t`.`terDatum`,'%d.%m.%Y %H:%i') AS `terDatumFormatiert`,`t`.`terKategorie` AS `terKategorie`,`t`.`terTitel` AS `terTitel`,`t`.`terOrt` AS `terOrt`,`t`.`terEmailGesendet` AS `terEmailGesendet`,`p`.`proName` AS `proName`,group_concat(concat(`u`.`usrFirstName`,' ',`u`.`usrLastName`) order by `u`.`usrLastName` ASC separator ', ') AS `terMitarbeitende`,group_concat(`u`.`usrID` order by `u`.`usrLastName` ASC separator ',') AS `terMitarbeitendeIDs` from (((`tabTermin` `t` left join `tabProjekt` `p` on(`t`.`terFkProjekt` = `p`.`proID`)) left join `tabTerminUser` `tu` on(`t`.`terID` = `tu`.`teuFkTermin`)) left join `tabUser` `u` on(`tu`.`teuFkUser` = `u`.`usrID` and `u`.`usrDeleted` = 0)) where `t`.`terDeleted` = 0 group by `t`.`terID` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*M!100616 SET NOTE_VERBOSITY=@OLD_NOTE_VERBOSITY */; -- Dump completed