/*M!999999\- enable the sandbox mode */ -- MariaDB dump 10.19 Distrib 10.11.11-MariaDB, for Linux (x86_64) -- -- Host: bearybot.selfhost.co Database: pyqcrm -- ------------------------------------------------------ -- Server version 10.11.11-MariaDB-0ubuntu0.24.04.2-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' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `address` -- DROP TABLE IF EXISTS `address`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `address` ( `addressid` int(11) NOT NULL AUTO_INCREMENT, `countryid` int(11) NOT NULL, `cityid` int(11) NOT NULL, `postcodeid` int(11) NOT NULL, PRIMARY KEY (`addressid`), KEY `address_country_FK` (`countryid`) ) ENGINE=InnoDB AUTO_INCREMENT=6956 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `addressesview` -- DROP TABLE IF EXISTS `addressesview`; /*!50001 DROP VIEW IF EXISTS `addressesview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `addressesview` AS SELECT 1 AS `1` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `applicants` -- DROP TABLE IF EXISTS `applicants`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `applicants` ( `id` varchar(40) NOT NULL, `title` smallint(6) DEFAULT 0, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `street` varchar(255) DEFAULT NULL, `house_number` varchar(255) DEFAULT NULL, `zip_code` int(11) DEFAULT NULL, `phone_number` varchar(255) DEFAULT NULL, `mobile_number` varchar(255) DEFAULT NULL, `email_address` varchar(255) DEFAULT NULL, `salutation` text NOT NULL, PRIMARY KEY (`id`), KEY `zip_code` (`zip_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `applicantview` -- DROP TABLE IF EXISTS `applicantview`; /*!50001 DROP VIEW IF EXISTS `applicantview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `applicantview` AS SELECT 1 AS `EmployeeId`, 1 AS `Aktenzeichen`, 1 AS `Vorname`, 1 AS `Nachname`, 1 AS `Anrede`, 1 AS `Mobil`, 1 AS `Telefon`, 1 AS `Email`, 1 AS `Beschäftigungsverhältnis`, 1 AS `Erledigt` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `assignment` -- DROP TABLE IF EXISTS `assignment`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `assignment` ( `contractid` int(11) NOT NULL AUTO_INCREMENT, `employeeid` int(11) NOT NULL, UNIQUE KEY `assignment_unique` (`contractid`), KEY `assignment_employee_FK` (`employeeid`), CONSTRAINT `assignment_contract_FK` FOREIGN KEY (`contractid`) REFERENCES `contract` (`contractid`), CONSTRAINT `assignment_employee_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_estonian_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `bank` -- DROP TABLE IF EXISTS `bank`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `bank` ( `bankid` int(11) NOT NULL AUTO_INCREMENT, `bankname` varchar(75) NOT NULL, PRIMARY KEY (`bankid`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER bank_logger_insert AFTER INSERT ON bank FOR EACH ROW CALL logger (new.bankid, 'INSERT', 'bankAccounts() - add a new bank') */;; 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 */ ; -- -- Table structure for table `banking` -- DROP TABLE IF EXISTS `banking`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `banking` ( `bankingid` int(11) NOT NULL AUTO_INCREMENT, `iban` varchar(150) NOT NULL, `bankid` int(11) NOT NULL, `employeeid` int(11) NOT NULL, PRIMARY KEY (`bankingid`), UNIQUE KEY `banking_unique` (`iban`), UNIQUE KEY `banking_unique_1` (`employeeid`), KEY `banking_bank_FK` (`bankid`), CONSTRAINT `banking_bank_FK` FOREIGN KEY (`bankid`) REFERENCES `bank` (`bankid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `banking_employee_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER banking_logger_insert AFTER INSERT ON banking FOR EACH ROW CALL logger (new.bankingid, 'INSERT', 'bankAccounts() - add a new bank account') */;; 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 */ ; -- -- Table structure for table `business` -- DROP TABLE IF EXISTS `business`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `business` ( `businessid` int(11) NOT NULL AUTO_INCREMENT, `company` varchar(100) NOT NULL, `phone` varchar(50) DEFAULT NULL, `mobilephone` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `homepage` varchar(255) DEFAULT NULL, `director` varchar(100) DEFAULT NULL, `contactpersonid` int(11) DEFAULT NULL, `info` varchar(500) DEFAULT NULL, `btid` int(11) DEFAULT 1, `taxno` varchar(50) DEFAULT NULL, `enabled` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`businessid`), UNIQUE KEY `business_unique` (`contactpersonid`), KEY `business_businesstype_FK` (`btid`), CONSTRAINT `business_businesstype_FK` FOREIGN KEY (`btid`) REFERENCES `businesstype` (`btypeID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `business_contactperson_FK` FOREIGN KEY (`contactpersonid`) REFERENCES `contactperson` (`contactpersonid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `businesstype` -- DROP TABLE IF EXISTS `businesstype`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `businesstype` ( `btypeID` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(35) NOT NULL, PRIMARY KEY (`btypeID`), UNIQUE KEY `businesstype_unique` (`description`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `city` -- DROP TABLE IF EXISTS `city`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `city` ( `cityid` int(11) NOT NULL AUTO_INCREMENT, `city` varchar(50) DEFAULT NULL, PRIMARY KEY (`cityid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `clienteleinfo` -- DROP TABLE IF EXISTS `clienteleinfo`; /*!50001 DROP VIEW IF EXISTS `clienteleinfo`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `clienteleinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `contact` -- DROP TABLE IF EXISTS `contact`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `contact` ( `contactpersonid` int(11) NOT NULL, `isbusiness` tinyint(1) NOT NULL DEFAULT 1, UNIQUE KEY `contact_unique` (`contactpersonid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `contactperson` -- DROP TABLE IF EXISTS `contactperson`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `contactperson` ( `contactpersonid` int(11) NOT NULL AUTO_INCREMENT, `priority` tinyint(1) NOT NULL DEFAULT 0, `lastname` varchar(75) NOT NULL, `firstname` varchar(75) NOT NULL, `phone` varchar(75) DEFAULT NULL, `mobilephone` varchar(75) DEFAULT NULL, `pos` varchar(50) DEFAULT NULL, `salutation` varchar(15) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `birthday` varchar(35) DEFAULT NULL, `active` tinyint(1) NOT NULL DEFAULT 1, `invoice` tinyint(1) NOT NULL DEFAULT 0, `reminder` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`contactpersonid`) ) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `contactpersonview` -- DROP TABLE IF EXISTS `contactpersonview`; /*!50001 DROP VIEW IF EXISTS `contactpersonview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `contactpersonview` AS SELECT 1 AS `contactid`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname`, 1 AS `Telefon`, 1 AS `Mobil`, 1 AS `E-Mail`, 1 AS `Position`, 1 AS `Entscheider` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `contract` -- DROP TABLE IF EXISTS `contract`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `contract` ( `contractid` int(11) NOT NULL AUTO_INCREMENT, `tariffid` int(11) NOT NULL, `officeid` int(11) NOT NULL COMMENT 'Kostenstelle', `startdate` date NOT NULL, `enddate` date DEFAULT NULL, PRIMARY KEY (`contractid`), KEY `contract_office_FK` (`officeid`), CONSTRAINT `contract_office_FK` FOREIGN KEY (`officeid`) REFERENCES `office` (`officeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `country` -- DROP TABLE IF EXISTS `country`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `country` ( `countryid` int(11) NOT NULL AUTO_INCREMENT, `country` varchar(200) NOT NULL, `countryshort` varchar(100) NOT NULL, `nationality` varchar(100) NOT NULL, `iso2` varchar(2) NOT NULL, `iso3` varchar(3) NOT NULL, PRIMARY KEY (`countryid`), UNIQUE KEY `country_unique` (`country`), UNIQUE KEY `country_unique_1` (`iso2`), UNIQUE KEY `country_unique_2` (`iso3`) ) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `customerinfo` -- DROP TABLE IF EXISTS `customerinfo`; /*!50001 DROP VIEW IF EXISTS `customerinfo`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `customerinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `employee` -- DROP TABLE IF EXISTS `employee`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `employee` ( `employeeid` int(11) NOT NULL AUTO_INCREMENT, `lastname` varchar(75) NOT NULL, `firstname` varchar(75) NOT NULL, `maritalstatusid` int(11) NOT NULL DEFAULT 1, `countryid` int(11) NOT NULL DEFAULT 37 COMMENT 'used for nationality', `birthday` varchar(75) NOT NULL, `phone` varchar(75) DEFAULT NULL, `mobilephone` varchar(75) DEFAULT NULL, `email` varchar(75) DEFAULT NULL, `employeecontracttypeid` int(11) NOT NULL DEFAULT 5, `formofaddress` varchar(35) DEFAULT NULL COMMENT 'Briefanrede', `salutation` varchar(35) NOT NULL, `applicant` tinyint(1) NOT NULL DEFAULT 1, `enabled` tinyint(1) NOT NULL DEFAULT 1, `processed` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'erledigt', `disability` varchar(75) DEFAULT '0', `reference` varchar(75) NOT NULL, `empreference` varchar(75) NOT NULL DEFAULT '0', `officeid` int(11) DEFAULT NULL, `taxno` varchar(75) NOT NULL, `userid` int(11) DEFAULT NULL COMMENT 'supervisor/dispatcher', `birthname` varchar(75) DEFAULT NULL, `placeofbirth` varchar(75) DEFAULT NULL, `workpermit` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`employeeid`), UNIQUE KEY `employee_unique_2` (`reference`), UNIQUE KEY `employee_unique_3` (`taxno`), KEY `employee_jobstatus_FK` (`employeecontracttypeid`), KEY `employee_maritalstatus_FK` (`maritalstatusid`), KEY `employee_office_FK` (`officeid`), KEY `employee_country_FK` (`countryid`), KEY `employee_users_FK` (`userid`), CONSTRAINT `employee_country_FK` FOREIGN KEY (`countryid`) REFERENCES `country` (`countryid`) ON UPDATE CASCADE, CONSTRAINT `employee_jobstatus_FK` FOREIGN KEY (`employeecontracttypeid`) REFERENCES `employeecontracttype` (`employeecontracttypeid`), CONSTRAINT `employee_maritalstatus_FK` FOREIGN KEY (`maritalstatusid`) REFERENCES `maritalstatus` (`maritalstatusid`) ON UPDATE CASCADE, CONSTRAINT `employee_office_FK` FOREIGN KEY (`officeid`) REFERENCES `office` (`officeid`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `employee_users_FK` FOREIGN KEY (`userid`) REFERENCES `users` (`usersid`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_unicode_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER employee_logger_insert AFTER INSERT ON employee FOR EACH ROW CALL logger (new.employeeid, 'INSERT', 'addApplicant() - new Applicant inserted') */;; 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 */ ; -- -- Temporary table structure for view `employeeapplicantview` -- DROP TABLE IF EXISTS `employeeapplicantview`; /*!50001 DROP VIEW IF EXISTS `employeeapplicantview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `employeeapplicantview` AS SELECT 1 AS `EmployeeId`, 1 AS `Aktenzeichen`, 1 AS `Vorname`, 1 AS `Nachname`, 1 AS `Anrede`, 1 AS `Mobil`, 1 AS `Telefon`, 1 AS `Email`, 1 AS `Beschäftigungsverhältnis`, 1 AS `Erledigt`, 1 AS `Bewerber` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `employeecontract` -- DROP TABLE IF EXISTS `employeecontract`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `employeecontract` ( `empcontractid` int(11) NOT NULL AUTO_INCREMENT, `employeeid` int(11) NOT NULL, `contractstart` date NOT NULL DEFAULT current_timestamp(), `contractend` date DEFAULT NULL, `salary` decimal(5,2) unsigned NOT NULL DEFAULT 12.82, `jobdescription` varchar(100) DEFAULT NULL, PRIMARY KEY (`empcontractid`), KEY `employeecontract_employee_FK` (`employeeid`), CONSTRAINT `employeecontract_employee_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER pyqcrm.employeecontract_logger_insert AFTER INSERT ON employeecontract FOR EACH ROW CALL logger (new.empcontractid, 'INSERT', 'getEmployeeContract() - add a Contract') */;; 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 */ ; -- -- Table structure for table `employeecontracttype` -- DROP TABLE IF EXISTS `employeecontracttype`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `employeecontracttype` ( `employeecontracttypeid` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(30) NOT NULL, PRIMARY KEY (`employeecontracttypeid`), UNIQUE KEY `jobstatus_unique` (`description`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `employeeview` -- DROP TABLE IF EXISTS `employeeview`; /*!50001 DROP VIEW IF EXISTS `employeeview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `employeeview` AS SELECT 1 AS `EmployeeId`, 1 AS `Aktenzeichen`, 1 AS `Vorname`, 1 AS `Nachname`, 1 AS `Anrede`, 1 AS `Mobil`, 1 AS `Telefon`, 1 AS `Email`, 1 AS `Beschäftigungsverhältnis`, 1 AS `Erledigt`, 1 AS `Bewerber` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `employeeweeklyhours` -- DROP TABLE IF EXISTS `employeeweeklyhours`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `employeeweeklyhours` ( `empcontractid` int(11) NOT NULL, `workhours` tinyint(1) unsigned NOT NULL, `workdays` tinyint(1) unsigned NOT NULL, KEY `employeeweeklyhours_employeecontract_FK` (`empcontractid`), CONSTRAINT `employeeweeklyhours_employeecontract_FK` FOREIGN KEY (`empcontractid`) REFERENCES `employeecontract` (`empcontractid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER pyqcrm.employeeweeklyhours_logger_insert AFTER INSERT ON employeeweeklyhours FOR EACH ROW CALL logger (new.empcontractid, 'INSERT', 'getEmployeeContract() - add a Contract') */;; 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 */ ; -- -- Temporary table structure for view `empprocessedview` -- DROP TABLE IF EXISTS `empprocessedview`; /*!50001 DROP VIEW IF EXISTS `empprocessedview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `empprocessedview` AS SELECT 1 AS `1` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `entities` -- DROP TABLE IF EXISTS `entities`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `entities` ( `entityid` int(11) NOT NULL AUTO_INCREMENT, `entity` varchar(35) NOT NULL, PRIMARY KEY (`entityid`), UNIQUE KEY `entities_unique` (`entity`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `finishedinfo` -- DROP TABLE IF EXISTS `finishedinfo`; /*!50001 DROP VIEW IF EXISTS `finishedinfo`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `finishedinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `fulladress` -- DROP TABLE IF EXISTS `fulladress`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `fulladress` ( `fulladressid` int(11) NOT NULL AUTO_INCREMENT, `addressid` int(11) NOT NULL, PRIMARY KEY (`fulladressid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `house` -- DROP TABLE IF EXISTS `house`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `house` ( `houseid` int(11) NOT NULL AUTO_INCREMENT, `strid` int(11) NOT NULL, `houseno` varchar(6) NOT NULL, PRIMARY KEY (`houseid`), KEY `house_street_FK` (`strid`) ) ENGINE=InnoDB AUTO_INCREMENT=134 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `identification` -- DROP TABLE IF EXISTS `identification`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `identification` ( `identificationid` int(11) NOT NULL AUTO_INCREMENT, `idnumber` varchar(75) NOT NULL, `expiry` date NOT NULL, `issued` date NOT NULL, `authority` varchar(75) NOT NULL, `typeid` int(11) NOT NULL, `employeeid` int(11) NOT NULL, PRIMARY KEY (`identificationid`), UNIQUE KEY `identification_unique` (`idnumber`), KEY `identification_identificationtype_FK` (`typeid`), KEY `identification_employee_FK` (`employeeid`), CONSTRAINT `identification_employee_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `identification_identificationtype_FK` FOREIGN KEY (`typeid`) REFERENCES `identificationtype` (`typeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER pyqcrm.identification_logger_insert AFTER INSERT ON identification FOR EACH ROW CALL logger (new.identificationid, 'INSERT', 'getIdentification() - add an identification') */;; 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 */ ; -- -- Table structure for table `identificationtype` -- DROP TABLE IF EXISTS `identificationtype`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `identificationtype` ( `typeid` int(11) NOT NULL AUTO_INCREMENT, `idtype` varchar(50) NOT NULL, PRIMARY KEY (`typeid`), UNIQUE KEY `identificationtype_unique` (`idtype`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `interestedinfo` -- DROP TABLE IF EXISTS `interestedinfo`; /*!50001 DROP VIEW IF EXISTS `interestedinfo`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `interestedinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `invoice` -- DROP TABLE IF EXISTS `invoice`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `invoice` ( `invoiceid` int(11) NOT NULL AUTO_INCREMENT, `issued` date NOT NULL COMMENT 'Rechnungsdatum', `entry` date NOT NULL COMMENT 'Buchungsdatum', `contractid` int(11) NOT NULL, PRIMARY KEY (`invoiceid`), KEY `invoice_contract_FK` (`contractid`), CONSTRAINT `invoice_contract_FK` FOREIGN KEY (`contractid`) REFERENCES `contract` (`contractid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `jobdetail` -- DROP TABLE IF EXISTS `jobdetail`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `jobdetail` ( `jobdetailid` int(11) NOT NULL AUTO_INCREMENT, `duration` decimal(10,0) NOT NULL COMMENT 'Zeitaufwand für Leistungen', `objectid` int(11) NOT NULL, `period` varchar(15) NOT NULL COMMENT 'Zeitraum des Reinigungsvertrag', `personnel` int(11) NOT NULL DEFAULT 1 COMMENT 'number of employees', `price` decimal(10,0) NOT NULL, PRIMARY KEY (`jobdetailid`), UNIQUE KEY `jobdetail_unique` (`objectid`), CONSTRAINT `jobdetail_object_FK` FOREIGN KEY (`objectid`) REFERENCES `objects` (`objectid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `logs` -- DROP TABLE IF EXISTS `logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `logs` ( `logid` int(11) NOT NULL AUTO_INCREMENT, `recordid` int(11) DEFAULT NULL, `operationtype` varchar(100) NOT NULL, `message` varchar(100) DEFAULT NULL, `logdate` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`logid`) ) ENGINE=InnoDB AUTO_INCREMENT=21914 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `maritalstatus` -- DROP TABLE IF EXISTS `maritalstatus`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `maritalstatus` ( `maritalstatusid` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(25) NOT NULL, PRIMARY KEY (`maritalstatusid`), UNIQUE KEY `maritalstatus_unique` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `medicalcare` -- DROP TABLE IF EXISTS `medicalcare`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `medicalcare` ( `medicalcareid` int(11) NOT NULL AUTO_INCREMENT, `company` varchar(50) NOT NULL, PRIMARY KEY (`medicalcareid`), UNIQUE KEY `medicalcare_unique` (`company`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER pyqcrm.medicalcare_logger_insert AFTER INSERT ON medicalcare FOR EACH ROW CALL logger (new.medicalcareid, 'INSERT', 'getSocialInformation() - add a Company') */;; 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 */ ; -- -- Table structure for table `object_mitarbeiter` -- DROP TABLE IF EXISTS `object_mitarbeiter`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `object_mitarbeiter` ( `employeeid` int(11) NOT NULL, `hour_wage` int(3) NOT NULL, `duration` varchar(20) NOT NULL, `workdays` varchar(25) NOT NULL, `tasks` varchar(50) NOT NULL COMMENT 'Tätigkeiten', `profit` int(4) NOT NULL COMMENT 'Ertrag', `objectid` int(11) NOT NULL, KEY `fk_objectid` (`objectid`), KEY `fk_employeeid` (`employeeid`), CONSTRAINT `fk_employeeid` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`), CONSTRAINT `fk_objectid` FOREIGN KEY (`objectid`) REFERENCES `objects` (`objectid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `objectmanagement` -- DROP TABLE IF EXISTS `objectmanagement`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `objectmanagement` ( `objectid` int(11) NOT NULL, `businessid` int(11) DEFAULT NULL, `contact1id` int(11) DEFAULT NULL, `contact2id` int(11) DEFAULT NULL, `contact3id` int(11) DEFAULT NULL, KEY `objectmanagement_objects_FK` (`objectid`), CONSTRAINT `objectmanagement_objects_FK` FOREIGN KEY (`objectid`) REFERENCES `objects` (`objectid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Relationtiops between objects, businesses and contact persons'; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER logObjectContacts AFTER INSERT ON objectmanagement FOR EACH ROW CALL logger (new.objectid, 'INSERT', 'addObject() - add an Object') */;; 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 */ ; -- -- Table structure for table `objects` -- DROP TABLE IF EXISTS `objects`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `objects` ( `objectid` int(11) NOT NULL AUTO_INCREMENT, `info` varchar(500) DEFAULT NULL, `units` smallint(6) NOT NULL DEFAULT 1 COMMENT 'Parteien', `floors` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Etage', `mezzanine` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Zwischenetage', `elevator` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Aufzug vorhanden?', `remarks` varchar(100) DEFAULT NULL COMMENT 'Besonderheiten', `cleaningproducts` varchar(100) DEFAULT NULL COMMENT 'Reinigungsmittel Ort', `active` tinyint(1) NOT NULL DEFAULT 0, `objectno` varchar(100) NOT NULL COMMENT 'Objektnummer', PRIMARY KEY (`objectid`), UNIQUE KEY `objects_unique` (`objectno`) ) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER pyqcrm.objects_logger_insert AFTER INSERT ON objects FOR EACH ROW CALL logger (new.objectid, 'INSERT', 'addObject() - add an Object') */;; 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 */ ; -- -- Temporary table structure for view `objectview` -- DROP TABLE IF EXISTS `objectview`; /*!50001 DROP VIEW IF EXISTS `objectview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `objectview` AS SELECT 1 AS `ObjektId`, 1 AS `Aktiv`, 1 AS `Objekt-Nr.`, 1 AS `Parteien`, 1 AS `Besonderheiten`, 1 AS `Kunde`, 1 AS `Hi1`, 1 AS `Fkon1`, 1 AS `Lkon1`, 1 AS `Hi2`, 1 AS `Fkon2`, 1 AS `Lkon2`, 1 AS `Hi3`, 1 AS `Fkon3`, 1 AS `Lkon3` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `offer` -- DROP TABLE IF EXISTS `offer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `offer` ( `offerid` int(11) NOT NULL AUTO_INCREMENT, `date` date NOT NULL, PRIMARY KEY (`offerid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `offers` -- DROP TABLE IF EXISTS `offers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `offers` ( `offerid` int(11) DEFAULT NULL, `contractid` int(11) DEFAULT NULL, `jobdetailid` int(11) NOT NULL, UNIQUE KEY `offers_unique` (`jobdetailid`), KEY `offers_offer_FK` (`offerid`), KEY `offers_contract_FK` (`contractid`), CONSTRAINT `offers_jobdetail_FK` FOREIGN KEY (`jobdetailid`) REFERENCES `jobdetail` (`jobdetailid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Kreuztabelle Angebot - Auftrag'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `office` -- DROP TABLE IF EXISTS `office`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `office` ( `officeid` int(11) NOT NULL AUTO_INCREMENT, `office` varchar(50) NOT NULL COMMENT 'Kostenstelle', PRIMARY KEY (`officeid`), UNIQUE KEY `office_unique` (`office`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Kostenstelle'; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER office_logger_insert AFTER INSERT ON office FOR EACH ROW CALL logger (new.officeid, 'INSERT', 'getCostOffice() - add an officeid') */;; 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 */ ; -- -- Table structure for table `permissions` -- DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `permissions` ( `permissionid` int(11) NOT NULL AUTO_INCREMENT, `permission` varchar(50) NOT NULL, PRIMARY KEY (`permissionid`), UNIQUE KEY `berechtigungen_unique` (`permission`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `personalrole` -- DROP TABLE IF EXISTS `personalrole`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `personalrole` ( `employeeid` int(11) NOT NULL, `roleid` int(11) NOT NULL, KEY `personalrole_employee_FK` (`employeeid`), KEY `personalrole_role_FK` (`roleid`), CONSTRAINT `personalrole_employee_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`), CONSTRAINT `personalrole_role_FK` FOREIGN KEY (`roleid`) REFERENCES `userrole` (`roleid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Kreuztabelle'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `postcode` -- DROP TABLE IF EXISTS `postcode`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `postcode` ( `postcodeid` int(11) NOT NULL AUTO_INCREMENT, `postcode` varchar(15) NOT NULL, PRIMARY KEY (`postcodeid`) ) ENGINE=InnoDB AUTO_INCREMENT=8178 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary table structure for view `providerinfo` -- DROP TABLE IF EXISTS `providerinfo`; /*!50001 DROP VIEW IF EXISTS `providerinfo`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `providerinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `roles` -- DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `roles` ( `roleid` int(11) NOT NULL, `permissionid` int(11) NOT NULL, `entityid` int(11) NOT NULL, KEY `roles_role_FK` (`roleid`), KEY `roles_permissions_FK` (`permissionid`), KEY `roles_entities_FK` (`entityid`), CONSTRAINT `roles_entities_FK` FOREIGN KEY (`entityid`) REFERENCES `entities` (`entityid`), CONSTRAINT `roles_permissions_FK` FOREIGN KEY (`permissionid`) REFERENCES `permissions` (`permissionid`), CONSTRAINT `roles_role_FK` FOREIGN KEY (`roleid`) REFERENCES `userrole` (`roleid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `salestax` -- DROP TABLE IF EXISTS `salestax`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `salestax` ( `salestaxid` varchar(20) NOT NULL, `businessid` int(11) NOT NULL, PRIMARY KEY (`salestaxid`), UNIQUE KEY `salestax_unique` (`businessid`), CONSTRAINT `salestax_business_FK` FOREIGN KEY (`businessid`) REFERENCES `business` (`businessid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `service` -- DROP TABLE IF EXISTS `service`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `service` ( `servicetypeid` int(11) NOT NULL, `servicedescid` int(11) NOT NULL, `objectid` int(11) NOT NULL, KEY `service_servicedesc_FK` (`servicedescid`), KEY `service_object_FK` (`objectid`), KEY `service_servicetype_FK` (`servicetypeid`), CONSTRAINT `service_object_FK` FOREIGN KEY (`objectid`) REFERENCES `objects` (`objectid`), CONSTRAINT `service_servicedesc_FK` FOREIGN KEY (`servicedescid`) REFERENCES `servicedesc` (`servicedescid`), CONSTRAINT `service_servicetype_FK` FOREIGN KEY (`servicetypeid`) REFERENCES `servicetype` (`servicetypeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `servicedesc` -- DROP TABLE IF EXISTS `servicedesc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `servicedesc` ( `servicedescid` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(1000) NOT NULL, PRIMARY KEY (`servicedescid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `servicetype` -- DROP TABLE IF EXISTS `servicetype`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `servicetype` ( `servicetypeid` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(50) NOT NULL, PRIMARY KEY (`servicetypeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `socialinformation` -- DROP TABLE IF EXISTS `socialinformation`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `socialinformation` ( `employeeid` int(11) NOT NULL, `socialsecurityno` varchar(75) NOT NULL, `medicalcareid` int(11) NOT NULL, `knappschaft` varchar(50) DEFAULT NULL, UNIQUE KEY `socialsecurity_unique_1` (`socialsecurityno`), UNIQUE KEY `socialsecurity_unique` (`employeeid`), KEY `socialsecurity_medicalcare_FK` (`medicalcareid`), CONSTRAINT `socialsecurity_employee_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`), CONSTRAINT `socialsecurity_medicalcare_FK` FOREIGN KEY (`medicalcareid`) REFERENCES `medicalcare` (`medicalcareid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER pyqcrm.socialinformation_logger_insert AFTER INSERT ON socialinformation FOR EACH ROW CALL logger (new.employeeid, 'INSERT', 'getSocialInformation() - add a socialsecuritynumber') */;; 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 */ ; -- -- Table structure for table `street` -- DROP TABLE IF EXISTS `street`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `street` ( `strid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`strid`) ) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `streethouseno` -- DROP TABLE IF EXISTS `streethouseno`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `streethouseno` ( `streethouseno` int(11) NOT NULL AUTO_INCREMENT, `houseid` int(11) NOT NULL, `strid` int(11) NOT NULL, PRIMARY KEY (`streethouseno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Kreuztabelle Street-Houseno'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tariff` -- DROP TABLE IF EXISTS `tariff`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `tariff` ( `tariffid` int(11) NOT NULL AUTO_INCREMENT, `tariff` decimal(10,0) NOT NULL COMMENT 'Festgelegter Stundensatz', `description` varchar(35) NOT NULL, PRIMARY KEY (`tariffid`), UNIQUE KEY `tariff_unique` (`description`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Stundensatz Tabelle'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `timetrack` -- DROP TABLE IF EXISTS `timetrack`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `timetrack` ( `employeeid` int(11) NOT NULL, `start` datetime NOT NULL, `stop` datetime NOT NULL, `vacation` tinyint(1) NOT NULL DEFAULT 0, KEY `timetrack_employee_FK` (`employeeid`), CONSTRAINT `timetrack_employee_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `userrole` -- DROP TABLE IF EXISTS `userrole`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `userrole` ( `roleid` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(50) NOT NULL, PRIMARY KEY (`roleid`), UNIQUE KEY `role_unique` (`description`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `users` ( `usersid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `password` varchar(161) NOT NULL, `enabled` tinyint(1) NOT NULL DEFAULT 1, `roleid` int(11) NOT NULL, `gecos` varchar(75) DEFAULT NULL, PRIMARY KEY (`usersid`), UNIQUE KEY `users_unique` (`username`), KEY `users_role_FK` (`roleid`), CONSTRAINT `users_role_FK` FOREIGN KEY (`roleid`) REFERENCES `userrole` (`roleid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`pyqcrm`@`%`*/ /*!50003 TRIGGER newUserLog AFTER INSERT ON users FOR EACH ROW CALL logger (new.usersid, "INSERT", "createUser: New user created") */;; 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 */ ; -- -- Dumping routines for database 'pyqcrm' -- /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP FUNCTION IF EXISTS `getNewReference` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` FUNCTION `getNewReference`(IN ref_type CHAR(2), IN dec_key VARCHAR(45)) RETURNS varchar(15) CHARSET utf8mb4 COLLATE utf8mb4_general_ci DETERMINISTIC BEGIN DECLARE next_ref VARCHAR(15); DECLARE new_ref VARCHAR(15); DECLARE ref_prefix VARCHAR(8); DECLARE search_expr VARCHAR(9); SET ref_prefix = CONCAT('TP', DATE_FORMAT(CURRENT_DATE, '%y%U'), ref_type); SET new_ref = CONCAT(ref_prefix, '000'); SET search_expr = CONCAT(ref_prefix, '%'); IF ref_type = 'MB' THEN SELECT CONCAT(ref_prefix, LPAD(RIGHT(AES_DECRYPT(UNHEX(reference), dec_key), 3) + 1, 3, '0')) INTO next_ref FROM `pyqcrm`.`employee` `em` WHERE AES_DECRYPT(UNHEX(reference), dec_key) LIKE search_expr ORDER BY AES_DECRYPT(UNHEX(reference), dec_key) DESC LIMIT 1; ELSE -- SELECT CONCAT(ref_prefix, LPAD(RIGHT (objectno, 3) + 1, 3, '0')) INTO next_ref SELECT objectno + 1 INTO next_ref FROM `pyqcrm`.`objects` `ob` WHERE objectno > 999 ORDER BY objectno DESC LIMIT 1; END IF; SELECT IFNULL(next_ref, new_ref) INTO next_ref; RETURN next_ref; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addAddress` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addAddress`(IN new_address JSON, OUT zipcode_id INT(11)) BEGIN DECLARE a_streetid INT(11); DECLARE a_street VARCHAR(100); DECLARE a_houseno VARCHAR(6); DECLARE a_postcode INT(11); DECLARE a_city VARCHAR(50); SET a_street = JSON_UNQUOTE(JSON_EXTRACT(new_address, '$.street')); -- street Tabelle : postcodeid? -- ADDED SET a_houseno = JSON_UNQUOTE(JSON_EXTRACT(new_address, '$.houseno')); -- house Tabelle : strid? -- ADDED SET a_postcode = JSON_UNQUOTE(JSON_EXTRACT(new_address, '$.postcode')); -- postcode Tabelle : addressid? -- ADDED SET a_city = JSON_UNQUOTE(JSON_EXTRACT(new_address, '$.city')); -- address Tabelle : countryid? -- ADDED CALL checkZipCode (a_postcode, a_city, zipcode_id); -- <-- zipcodeid, @addressid IF a_street IS NOT NULL THEN CALL checkStreet (zipcode_id, a_street, a_houseno, a_streetid); -- <-- streetid, @houseid END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addApplicant` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addApplicant`(IN new_applicant JSON, IN is_applicant BOOL, IN encrypt_key VARCHAR(45)) BEGIN DECLARE add_app_query_open VARCHAR(4096); DECLARE add_app_query_close VARCHAR(4096); DECLARE a_zipcodeid INT(11); DECLARE a_employeeid INT(11); DECLARE app_contract_type_id INT(11); DECLARE app_country_id INT(11); DECLARE reference VARCHAR(75); DECLARE a_jobdesc VARCHAR(30); DECLARE a_taxno VARCHAR(75); DECLARE fake_birthday VARCHAR(75); DECLARE a_country VARCHAR(50); DECLARE app_work_permit BOOL; DECLARE a_residence BOOL; CALL addApplicantAddress (new_applicant, a_zipcodeid); -- <-- zipcodeid, @addressid, @houseid IF a_zipcodeid IS NOT NULL THEN CALL addApplicantPerso (new_applicant); -- : @app_salute, @app_fname, @app_lname, @app_dear_sir SET @app_fname = CASE WHEN @app_fname != "" THEN HEX(AES_ENCRYPT(@app_fname, encrypt_key)) END; SET @app_lname = CASE WHEN @app_lname != "" THEN HEX(AES_ENCRYPT(@app_lname, encrypt_key)) END; CALL addEmployeePerso (new_applicant); -- : @app_marital_id, @app_birthname, @app_birthday, @app_pob SET @app_birthname = CASE WHEN @app_birthname != "" THEN HEX(AES_ENCRYPT(@app_birthname, encrypt_key)) END; CASE @app_birthday WHEN "" THEN SET @app_birthday = HEX(AES_ENCRYPT("0000-00-00", encrypt_key)); ELSE SET @app_birthday = HEX(AES_ENCRYPT(@app_birthday, encrypt_key)); END CASE; SET @app_pob = CASE WHEN @app_pob != "" THEN HEX(AES_ENCRYPT(@app_pob, encrypt_key)) END; CALL addApplicantContactInfo (new_applicant); -- : @app_phone, @app_mobile, @app_email SET @app_phone = CASE WHEN @app_phone != "" THEN HEX(AES_ENCRYPT(@app_phone, encrypt_key)) END; SET @app_mobile = CASE WHEN @app_mobile != "" THEN HEX(AES_ENCRYPT(@app_mobile, encrypt_key)) END; SET @app_email = CASE WHEN @app_email != "" THEN HEX(AES_ENCRYPT(@app_email, encrypt_key)) END; SELECT pyqcrm.getNewReference('MB', encrypt_key) INTO reference; SET reference = HEX(AES_ENCRYPT(reference, encrypt_key)); IF is_applicant = 0 THEN SET a_jobdesc = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.jobstatus')); -- employeecontracttype : employeecontracttype ADDED SET a_taxno = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.taxno')); -- employee Tabelle : taxid ADDED -- SET a_taxno = CASE WHEN a_taxno != "" THEN HEX(AES_ENCRYPT(a_taxno, encrypt_key)) END; CASE a_taxno WHEN "" THEN SELECT UNIX_TIMESTAMP() INTO a_taxno; ELSE BEGIN END; END CASE; SET a_taxno = HEX(AES_ENCRYPT(a_taxno, encrypt_key)); SET a_country = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.country')); -- country Tabelle : SET app_work_permit = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.worklicense')); -- employee Tabelle ADDED SET a_residence = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.residencetype')); CALL getEmployeeContractType (a_jobdesc, app_contract_type_id); -- : app_contract_type_id CALL getNationality(a_country, app_country_id); -- : app_country_id CALL addEmployeeExtraInfo(new_applicant, encrypt_key); -- : @app_supervisor_id, @app_office_id, @app_ref_ext, @app_disability INSERT INTO employee (salutation, firstname, lastname, formofaddress, phone, mobilephone, email, birthname, birthday, placeofbirth, taxno, reference, empreference, disability, applicant, workpermit, countryid, userid, officeid, maritalstatusid, employeecontracttypeid) VALUES (@app_salute, @app_fname, @app_lname, @app_dear_sir, @app_phone, @app_mobile, @app_email, @app_birthname, @app_birthday, @app_pob, a_taxno, reference, @app_ref_ext, @app_disability, is_applicant, app_work_permit, app_country_id, @app_supervisor_id, @app_office_id, @a_marital_id, app_contract_type_id); COMMIT; SET a_employeeid = LAST_INSERT_ID(); CALL addEmployeeBank(new_applicant, a_employeeid, encrypt_key); -- : @app_banking_id CALL addEmployeeSocialSec(new_applicant, a_employeeid, encrypt_key); CALL addEmployeeJob(new_applicant, a_employeeid); CALL addEmployeeId(new_applicant, a_employeeid, encrypt_key); IF a_residence = TRUE THEN CALL addEmployeeExId(new_applicant, a_employeeid, encrypt_key); END IF; ELSE SELECT UNIX_TIMESTAMP() INTO a_taxno; SET a_taxno = HEX(AES_ENCRYPT(a_taxno, encrypt_key)); SET fake_birthday = HEX(AES_ENCRYPT("0000-00-00", encrypt_key)); INSERT INTO employee (salutation, firstname, lastname, formofaddress, phone, mobilephone, email, taxno, reference, birthday) VALUES (@app_salute, @app_fname, @app_lname, @app_dear_sir, @app_phone, @app_mobile, @app_email, a_taxno, reference, fake_birthday); COMMIT; SET a_employeeid = LAST_INSERT_ID(); END IF; -- END is_applicant FALSE INSERT INTO location_emp VALUES (a_employeeid, @addressid, @houseid); COMMIT; END IF; -- END IF a_zipcodeid NOT NULL 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addApplicantAddress` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addApplicantAddress`(IN new_applicant JSON, OUT zipcode_id INT(11)) BEGIN DECLARE a_streetid INT(11); DECLARE a_street VARCHAR(100); DECLARE a_houseno VARCHAR(6); DECLARE a_postcode INT(11); DECLARE a_city VARCHAR(50); SET a_street = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.street')); -- street Tabelle : postcodeid? -- ADDED SET a_houseno = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.houseno')); -- house Tabelle : strid? -- ADDED SET a_postcode = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.postcode')); -- postcode Tabelle : addressid? -- ADDED SET a_city = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.city')); -- address Tabelle : countryid? -- ADDED CALL checkZipCode (a_postcode, a_city, zipcode_id); -- <-- zipcodeid, @addressid IF a_street IS NOT NULL THEN CALL checkStreet (zipcode_id, a_street, a_houseno, a_streetid); -- <-- streetid, @houseid END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addApplicantContactInfo` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addApplicantContactInfo`(IN new_applicant JSON) BEGIN DECLARE a_phone VARCHAR(50); DECLARE a_mobilephone VARCHAR(50); DECLARE a_email VARCHAR(255); SET @app_phone = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.phone')); -- employee Tabelle -- ADDED SET @app_mobile = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.mobile')); -- employee Tabelle -- ADDED SET @app_email = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.email')); -- employee Tabelle -- ADDED 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addApplicantPerso` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addApplicantPerso`(IN new_applicant JSON) BEGIN SET @app_salute = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.title')); -- employee Tabelle : maritalstatusid?, nationalityid?, identificationid?, jobstatusid? -- ADDED SET @app_fname = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.firstname')); -- employee Tabelle -- ADDED SET @app_lname = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.lastname')); -- employee Tabelle -- ADDED SET @app_dear_sir = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.formofaddress')); -- employee Tabelle -- ADDED 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addBankAccounts` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addBankAccounts`(IN gbankname VARCHAR(75), IN giban VARCHAR(150), IN emp_id INT(11)) BEGIN DECLARE gbankid INT(11); IF (SELECT 1 FROM banking WHERE iban = giban) IS NULL THEN SELECT bankid INTO gbankid FROM bank WHERE bankname = gbankname; IF gbankid IS NULL THEN INSERT INTO bank (bankname) VALUES (gbankname); SET gbankid = LAST_INSERT_ID(); END IF; INSERT INTO banking (iban, bankid, employeeid) VALUES (giban, gbankid, emp_id); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addBusiness` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addBusiness`(IN new_business JSON, IN v_contactpersonid INT (11)) BEGIN DECLARE v_company VARCHAR(100); DECLARE v_streetid INT(11); DECLARE v_street VARCHAR(100); DECLARE v_postcode INT(11); DECLARE v_phone VARCHAR(50); DECLARE v_cell VARCHAR(50); DECLARE v_email VARCHAR(255); DECLARE v_homepage VARCHAR(255); DECLARE v_director VARCHAR(100); DECLARE v_info VARCHAR(500); DECLARE v_taxno VARCHAR(50); DECLARE v_bt VARCHAR(50); DECLARE v_zipcodeid INT(11); DECLARE v_city VARCHAR (50); DECLARE v_btid INT(11); DECLARE bid INT(11); DECLARE v_houseno VARCHAR(6); SET v_company = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.business')); SET v_city = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.city')); SET v_street = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.street')); SET v_postcode = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.postcode')); SET v_phone = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.telephone')); SET v_cell = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.cellphone')); SET v_email = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.email')); SET v_homepage = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.homepage')); SET v_director = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.ceo')); SET v_info = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.customerinfo')); SET v_taxno = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.taxno')); SET v_bt = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.typeid')); SET v_houseno = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.houseno')); CALL getBtype (v_bt, v_btid); CALL checkZipCode (v_postcode, v_city, v_zipcodeid); IF v_zipcodeid IS NOT NULL THEN IF v_street IS NOT NULL THEN CALL checkStreet (v_zipcodeid, v_street, v_houseno, v_streetid); END IF; IF v_contactpersonid = 0 THEN INSERT INTO business (company, phone, mobilephone, email, homepage, director, info, btid, taxno) VALUES (v_company, v_phone, v_cell, v_email, v_homepage, v_director, v_info, v_btid, v_taxno); ELSE INSERT INTO business (company, phone, mobilephone, email, homepage, director, contactpersonid, info, btid, taxno) VALUES (v_company, v_phone, v_cell, v_email, v_homepage, v_director, v_contactpersonid, v_info, v_btid, v_taxno); END IF; SET bid = LAST_INSERT_ID(); CALL logger (bid, "INSERT", "addBusiness: New Customer added"); INSERT INTO location_ct VALUES (bid, @addressid, @houseid, v_zipcodeid); CALL logger ((SELECT LAST_INSERT_ID()) , "INSERT", "addBusiness: New Customer Location added"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addContactPerson` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addContactPerson`(IN encrypt_key VARCHAR(45), IN new_contactperson JSON) BEGIN DECLARE x_salutation VARCHAR(15); DECLARE x_fname VARCHAR (75); DECLARE x_lname VARCHAR (75); DECLARE x_phone VARCHAR (75); DECLARE x_cell VARCHAR (75); DECLARE x_position VARCHAR (50); DECLARE x_email VARCHAR (255); DECLARE x_birthday VARCHAR (35); DECLARE x_priority BOOL; DECLARE x_invoice BOOL; DECLARE x_reminder BOOL; SET x_salutation = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.title')); SET x_fname = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.firstname')); SET x_fname = CASE WHEN x_fname != "" THEN HEX(AES_ENCRYPT(x_fname, encrypt_key)) END; SET x_lname = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.lastname')); SET x_lname = CASE WHEN x_lname != "" THEN HEX(AES_ENCRYPT(x_lname, encrypt_key)) END; SET x_phone = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.telephonecontact')); SET x_phone = CASE WHEN x_phone != "" THEN HEX(AES_ENCRYPT(x_phone, encrypt_key)) END; SET x_cell = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.cellphone')); SET x_cell = CASE WHEN x_cell != "" THEN HEX(AES_ENCRYPT(x_cell, encrypt_key)) END; SET x_position = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.jobdescription')); SET x_email = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.email')); SET x_email = CASE WHEN x_email != "" THEN HEX(AES_ENCRYPT(x_email, encrypt_key)) END; SET x_birthday = JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.birthday')); SET x_birthday = CASE WHEN x_birthday != "" THEN HEX(AES_ENCRYPT(x_birthday, encrypt_key)) END; SET x_priority = if(JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.rank')) = 'Ja', true, false); SET x_invoice = if(JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.invoice')) = 'Ja', true, false); SET x_reminder = if(JSON_UNQUOTE(JSON_EXTRACT(new_contactperson, '$.due')) = 'Ja', true, false); INSERT INTO contactperson (lastname, firstname, phone, mobilephone, pos, salutation, email, birthday, priority, invoice, reminder) VALUES (x_lname, x_fname, x_phone, x_cell, x_position, x_salutation, x_email, x_birthday, x_priority, x_invoice, x_reminder); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addEmployeeBank` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addEmployeeBank`(IN new_applicant JSON, IN emp_id INT(11), IN encrypt_key VARCHAR(45)) BEGIN DECLARE a_iban VARCHAR(150); DECLARE a_bank VARCHAR(75); SET a_iban = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.iban')); -- banking Tabelle : bankid SET a_bank = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.bank')); -- bank Tabelle : banking id SET a_iban = CASE WHEN a_iban != "" THEN HEX(AES_ENCRYPT(a_iban, encrypt_key)) END; CALL addBankAccounts(a_bank, a_iban, emp_id); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addEmployeeExId` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addEmployeeExId`(IN new_applicant JSON, IN employee_id INT(11), IN encrypt_key VARCHAR(45)) BEGIN DECLARE a_residenceno VARCHAR(50); DECLARE a_residenceauthority VARCHAR(50); DECLARE a_residenceissued DATE; DECLARE a_residenceexpiry DATE; DECLARE a_residencetype BOOL; SET a_residenceno = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.residenceno')); SET a_residenceno = CASE WHEN a_residenceno != "" THEN HEX(AES_ENCRYPT(a_residenceno, encrypt_key)) END; SET a_residenceissued = STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.residenceissued')), "%d.%m.%Y"); SET a_residenceexpiry = STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.residenceexpiry')), "%d.%m.%Y"); SET a_residenceauthority = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.residenceauthority')); SET a_residencetype = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.residencetype')); -- identificationtype CALL getIdentification(a_residenceno, a_residenceissued, a_residenceexpiry, a_residenceauthority, a_residencetype, employee_id); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addEmployeeExtraInfo` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addEmployeeExtraInfo`(IN new_applicant JSON, IN encrypt_key VARCHAR(45)) BEGIN DECLARE a_office VARCHAR(50); DECLARE a_disponent VARCHAR(50); SET @app_disability = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.disability'));-- employee Tabelle : disablity ADDED SET @app_disability = CASE WHEN @app_disability != "" THEN HEX(AES_ENCRYPT(@app_disability, encrypt_key)) END; SET @app_ref_ext = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.empreference')); -- employee Tabelle : empreference ADDED SET @app_ref_ext = CASE WHEN @app_ref_ext != "" THEN HEX(AES_ENCRYPT(@app_ref_ext, encrypt_key)) END; SET a_office = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.office')); -- office Tabelle : ADDED SET a_disponent = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.disponent')); -- user Tabelle : userid CALL getCostOffice(a_office, @app_office_id); CALL getSupervisor(a_disponent, @app_supervisor_id); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addEmployeeId` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addEmployeeId`(IN new_applicant JSON, IN employee_id INT(11), IN encrypt_key VARCHAR(45)) BEGIN DECLARE a_doc_id INT(11); DECLARE a_idexpiry DATE; DECLARE a_idissued DATE; DECLARE a_idauthority VARCHAR(50); DECLARE a_idtype VARCHAR(50); DECLARE a_idnumber VARCHAR(50); SET a_idnumber = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.idnumber')); SET a_idnumber = CASE WHEN a_idnumber != "" THEN HEX(AES_ENCRYPT(a_idnumber, encrypt_key)) END; SET a_idexpiry = STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.idexpiry')), "%d.%m.%Y"); -- identification Tabelle : identificationid SET a_idissued = STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.idissued')), "%d.%m.%Y"); -- identificationtype Tabelle : identificationid SET a_idauthority = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.idauthority')); -- identification Tabelle : identificationid SET a_idtype = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.idtype')); -- identificationtype Tabelle : identificationid CALL getIdentification(a_idnumber, a_idissued, a_idexpiry, a_idauthority, a_idtype, employee_id); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addEmployeeJob` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addEmployeeJob`(IN new_applicant JSON, IN employee_id INT(11)) BEGIN DECLARE a_salary DECIMAL(5,2) UNSIGNED; DECLARE a_contractstart DATE; DECLARE a_contractend DATE; DECLARE a_workdays TINYINT(1) UNSIGNED; DECLARE a_workhours TINYINT(1) UNSIGNED; DECLARE a_jobdesc VARCHAR(100); SET a_contractstart = STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.contractstart')), "%d.%m.%Y"); -- employeecontract Tabelle : SET a_contractend = STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.contractend')), "%d.%m.%Y"); -- employeecontract Tabelle : SET a_workhours = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.workhours')); -- employeeweeklyhours : empcontractid SET a_workdays = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.workdays')); -- employeeweeklyhours : empcontractid SET a_salary = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.salary')); -- SET a_jobdesc = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.jobdesc')); CALL getEmployeeContract (a_contractstart, a_contractend, a_workhours, a_workdays, a_salary, employee_id); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addEmployeePerso` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addEmployeePerso`(IN new_applicant JSON) BEGIN DECLARE a_birthday DATE; DECLARE a_placeofbirth VARCHAR(40); DECLARE a_birthname VARCHAR(40); DECLARE a_maritalstatus VARCHAR(15); DECLARE a_marital_status_id INT(11); SET @app_birthname = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.birthname')); -- ADDED SET @app_pob = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.placeofbirth')); -- ADDED SET @app_birthday = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.birthday')); -- ADDED SET a_maritalstatus = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.maritalstatus')); -- maritalstatus Tabelle : employeeid CALL getMaritalStatus (a_maritalstatus, @a_marital_id); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addEmployeeSocialSec` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addEmployeeSocialSec`(IN new_applicant JSON, IN employee_id INT(11), IN encrypt_key VARCHAR(45)) BEGIN DECLARE a_statusid INT(11); DECLARE a_socialno VARCHAR(75); DECLARE a_medicalinsurance VARCHAR(50); DECLARE a_knappschaft VARCHAR(50); SET a_socialno = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.socialno')); -- socialsecurity Tabelle : employeeid SET a_socialno = CASE WHEN a_socialno != "" THEN HEX(AES_ENCRYPT(a_socialno, encrypt_key)) END; SET a_medicalinsurance = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.medicalinsurance')); -- medicalcare Tabelle : in socialsecurity id SET a_knappschaft = JSON_UNQUOTE(JSON_EXTRACT(new_applicant, '$.knappschaft')); -- medicalcare Tabelle : in socialsecurity id CALL getSocialInformation(a_socialno, a_knappschaft, a_medicalinsurance, employee_id); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addHouseno` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addHouseno`(IN v_houseno VARCHAR(6), IN scid INT(11)) BEGIN INSERT INTO house (houseno, strid) VALUES (v_houseno, scid); SET @houseid = LAST_INSERT_ID(); CALL logger ((SELECT LAST_INSERT_ID()), "INSERT", "addHouseno: New Housenumber added from oschkarischtverhaftetwegensexy"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addObject` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addObject`(IN new_object JSON) BEGIN -- Object-related variables DECLARE g_city VARCHAR(50); DECLARE g_postcode VARCHAR(15); DECLARE g_houseno VARCHAR(6); DECLARE g_street VARCHAR(100); DECLARE g_units SMALLINT(6); DECLARE g_floors SMALLINT(6); DECLARE g_mezzanine TINYINT(1); DECLARE g_elevator TINYINT(1); DECLARE g_remarks VARCHAR(100); DECLARE g_cleaningproducts VARCHAR(100); DECLARE g_objectno VARCHAR(100); DECLARE g_zipcodeid INT(11); DECLARE g_objectid INT(11); -- Object-related variables SET g_units = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.units')); SET g_street = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.street')); SET g_floors = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.floors')); SET g_city = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.city')); SET g_postcode = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.postcode')); SET g_houseno = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.houseno')); SET g_mezzanine = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.mezzanin')); SET g_elevator = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.lift')); SET g_remarks = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.remarks')); SET g_cleaningproducts = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.cleaningproducts')); SET g_objectno = JSON_UNQUOTE(JSON_EXTRACT(new_object, '$.objectno')); IF g_objectno = "" OR g_objectno = 0 THEN SELECT getNewReference ('OB', NULL) INTO g_objectno; END IF; CALL addAddress (new_object, g_zipcodeid); INSERT INTO objects (mezzanine, elevator, cleaningproducts, objectno, floors, units, remarks) VALUES (g_mezzanine, g_elevator, g_cleaningproducts, g_objectno, g_floors, g_units, g_remarks); -- COMMIT; SET @g_objectid = LAST_INSERT_ID(); INSERT INTO location_obj VALUES (@g_objectid, @addressid, @houseid); -- COMMIT; SET @g_objectid = 0; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addObjectContact` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addObjectContact`(IN enc_key VARCHAR(45), IN new_objcontact JSON, IN obj_id INT(11)) BEGIN DECLARE counter INT(1) DEFAULT 0; DECLARE contact_num INT(1) DEFAULT JSON_LENGTH(new_objcontact); -- SET contact_num = JSON_LENGTH(new_objcontact); DECLARE c_fname VARCHAR(75); -- Encrypted DECLARE c_lname VARCHAR(75); -- Encrypted DECLARE c_phone VARCHAR(75); -- Encrypted DECLARE c_mphone VARCHAR(75); -- Encrypted DECLARE c_pos VARCHAR(50); DECLARE c_type VARCHAR(35); DECLARE c_salute VARCHAR(15); DECLARE j_path CHAR(20); -- DECLARE c_1id INT(11); -- DECLARE c_2id INT(11); -- DECLARE c_3id INT(11); SET @c_1id = 0; SET @c_2id = 0; SET @c_3id = 0; IF contact_num > 0 THEN WHILE counter < contact_num DO SET j_path = CONCAT('$.', counter, '.title'); SET c_salute = JSON_UNQUOTE(JSON_EXTRACT(new_objcontact, j_path)); SET j_path = CONCAT('$.', counter, '.position'); SET c_pos = JSON_UNQUOTE(JSON_EXTRACT(new_objcontact, j_path)); SET j_path = CONCAT('$.', counter, '.phone'); SET c_phone = HEX(AES_ENCRYPT(JSON_UNQUOTE(JSON_EXTRACT(new_objcontact, j_path)), enc_key)); SET j_path = CONCAT('$.', counter, '.mobile'); SET c_mphone = HEX(AES_ENCRYPT(JSON_UNQUOTE(JSON_EXTRACT(new_objcontact, j_path)), enc_key)); SET j_path = CONCAT('$.', counter, '.fname'); SET c_fname = HEX(AES_ENCRYPT(JSON_UNQUOTE(JSON_EXTRACT(new_objcontact, j_path)), enc_key)); SET j_path = CONCAT('$.', counter, '.lname'); SET c_lname = HEX(AES_ENCRYPT(JSON_UNQUOTE(JSON_EXTRACT(new_objcontact, j_path)), enc_key)); INSERT INTO contactperson(salutation, firstname, lastname, phone, mobilephone, pos) VALUES(c_salute, c_fname, c_lname, c_phone, c_mphone, c_pos); -- COMMIT; CASE counter WHEN 0 THEN SET @c_1id = LAST_INSERT_ID(); WHEN 1 THEN SET @c_2id = LAST_INSERT_ID(); ELSE SET @c_3id = LAST_INSERT_ID(); END CASE; SET counter = counter + 1; END WHILE; INSERT INTO objectmanagement(objectid, contact1id, contact2id, contact3id) VALUES (obj_id, @c_1id, @c_2id, @c_3id); -- COMMIT; -- CALL logObjectContact(); -- COMMIT; END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addPermission` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addPermission`(IN newpermission VARCHAR(50)) BEGIN CALL logger ((SELECT LAST_INSERT_ID()), "SELECT", "addPermission: strid selected from oschkarischtverhaftetwegensexy"); IF NOT EXISTS (SELECT 1 FROM permissions WHERE permission = newpermission) THEN INSERT INTO permissions(permission) VALUES(newpermission); CALL logger ((SELECT LAST_INSERT_ID()), "INSERT", "addPermission: New Permission added from oschkarischtverhaftetwegensexy"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addRole` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addRole`(IN newdescription VARCHAR(50)) BEGIN CALL logger ((SELECT LAST_INSERT_ID()), "SELECT", "addRole: strid selected from oschkarischtverhaftetwegensexy"); IF NOT EXISTS (SELECT 1 FROM userrole WHERE description = newdescription) THEN INSERT INTO userrole(description) VALUES(newdescription); CALL logger ((SELECT LAST_INSERT_ID()), "INSERT", "addRole: New Role added from oschkarischtverhaftetwegensexy"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addStreet` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addStreet`(IN zipcodeid INT(11), IN streetname VARCHAR(100), OUT scid INT(11)) BEGIN CALL logger ((SELECT LAST_INSERT_ID()), "SELECT", "addStreet: 1 selected from oschkarischtverhaftetwegensexy"); IF NOT EXISTS (SELECT 1 FROM street WHERE postcodeid = zipcodeid AND name = streetname) THEN INSERT INTO street(postcodeid, name) VALUES(zipcodeid, streetname); SET scid = LAST_INSERT_ID(); CALL logger ((SELECT LAST_INSERT_ID()), "INSERT", "addStreet: New Customer added from oschkarischtverhaftetwegensexy"); ELSE SELECT strid INTO scid FROM street WHERE name = streetname; CALL logger (scid, "SELECT", "addStreet: strid selected from oschkarischtverhaftetwegensexy"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `addZipCodes` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `addZipCodes`(IN zipcode VARCHAR(15), IN town VARCHAR(50), OUT nzcid VARCHAR (15)) BEGIN DECLARE zip_town INT; IF NOT EXISTS (SELECT 1 FROM address WHERE city = town) THEN INSERT INTO address(countryid, city) VALUES(37, town); SET zip_town = LAST_INSERT_ID(); -- CALL logger (zip_town, "INSERT", "addZipCodes: New Address added from oschkarischtverhaftetwegensexy"); ELSE SELECT addressid INTO zip_town FROM address WHERE city = town; CALL logger (zip_town, "SELECT", "addZipCodes: Addressid selected from oschkarischtverhaftetwegensexy"); END IF; SET nzcid = (SELECT postcodeid FROM postcode WHERE postcode = zipcode); CALL logger (zip_town, "SELECT", "addZipCodes: Postcodeid selected from oschkarischtverhaftetwegensexy"); IF nzcid IS NULL THEN INSERT INTO postcode(postcode, addressid) VALUES(zipcode, zip_town); SET nzcid = LAST_INSERT_ID(); -- CALL logger (nzcid, "INSERT", "addZipCodes: New Postcode added from oschkarischtverhaftetwegensexy"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `checkAdmin` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `checkAdmin`() BEGIN SELECT 1 FROM users WHERE roleid = 1 AND enabled = TRUE LIMIT 1; CALL logger (NULL, "SELECT", "checkAdmin: Check Admin User VALID written by OSCHKAR"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `checkStreet` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `checkStreet`(IN zipcodeid INT(11), IN streetname VARCHAR (100), IN v_houseno VARCHAR(6), OUT scid INT(11)) BEGIN SET scid = ( SELECT st.strid FROM street AS st JOIN postcode AS pc ON st.postcodeid = pc.postcodeid WHERE pc.postcodeid = zipcodeid AND st.name = streetname); CALL logger (NULL, "SELECT", "checkStreet: Check if street exists written by OSCHKAR"); IF scid IS NULL THEN CALL addStreet (zipcodeid, streetname, scid); CALL addHouseno (v_houseno, scid); ELSE CALL logger (NULL, "SELECT", "checkStreet: Check if Housenumber exists written by OSCHKAR"); IF (SELECT 1 FROM house WHERE strid = scid AND v_houseno = houseno) IS NULL THEN CALL addHouseno (v_houseno, scid); END IF; END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `checkZipCode` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `checkZipCode`(IN zipcode VARCHAR(15), IN town VARCHAR(50), OUT zcid INT) BEGIN SET zcid = ( SELECT pc.postcodeid FROM address AS ad JOIN postcode AS pc ON ad.addressid = pc.addressid WHERE pc.postcode = zipcode); CALL logger (zcid, "SELECT", "checkZipCode: Check if postcode exists written by OSCHKAR"); IF zcid IS NULL THEN CALL addZipCodes (zipcode, town, zcid); END IF; SELECT ad.addressid INTO @addressid FROM address AS ad JOIN postcode AS pc ON ad.addressid = pc.addressid WHERE pc.postcode = zipcode; CALL logger (@addressid, "SELECT", "checkZipCode: Check addressid written by OSCHKAR"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `createUser` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `createUser`(IN uname VARCHAR(35), IN password VARCHAR(161), IN gecos VARCHAR(75), IN admin BOOL) BEGIN DECLARE userole INT; SET userole = 2; IF admin = 1 THEN SET userole = 1; END IF; CALL logger (NULL, "SELECT", "createUser: Check if user exists written by OSCHKAR"); IF NOT EXISTS (SELECT 1 FROM users WHERE username = uname) THEN INSERT INTO users(username, password, gecos, roleid) VALUES(uname, password, gecos, userole); -- CALL logger ((SELECT LAST_INSERT_ID()) , "INSERT", "createUser: User created written by OSCHKAR"); ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username already in use'; END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getAddress` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getAddress`(IN all_address BOOL, IN zipcode VARCHAR(15)) BEGIN IF all_address = 1 THEN SELECT * FROM addressesview; CALL logger (NULL, "SELECT", "getAddress: All from addressview written by OSCHKAR"); ELSE SELECT pc.postcodeid, pc.addressid, pc.postcode, ad.city, ad.country FROM address AS ad JOIN postcode AS pc ON ad.addressid = pc.addressid WHERE pc.postcode = zipcode ORDER BY pc.postcode ASC; CALL logger (NULL, "SELECT", "getAddress: Select one adress written by OSCHKAR"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getApplicant` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_unicode_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getApplicant`(IN employee_id INT(11), IN encrypt_key VARCHAR(45)) BEGIN SELECT employee. employeeId AS employeeId, employee.salutation AS salutation, AES_DECRYPT(employee.lastname, encrypt_key) AS lastname, AES_DECRYPT(employee.firstname, encrypt_key) AS firstname, street.name AS street, house.houseno AS houseno, postcode.postcode AS postcode, address.city AS city, AES_DECRYPT(employee.phone, encrypt_key) AS phone, AES_DECRYPT(employee.mobilephone, encrypt_key) AS mobile, AES_DECRYPT(employee.email, encrypt_key) AS email, employee.formofaddress AS formofaddress FROM employee LEFT JOIN location_emp ON employee.employeeid = location_emp.employeeid LEFT JOIN house ON location_emp.houseid = house.houseid LEFT JOIN street ON house.strid = street.strid LEFT JOIN postcode ON street.postcodeid = postcode.postcodeid LEFT JOIN address ON postcode.addressid = address.addressid WHERE employee.employeeId = employee_id; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getBtype` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getBtype`(IN businessdesc VARCHAR (35), OUT typeid INT (11)) BEGIN IF businessdesc IS NULL THEN SELECT * FROM businesstype ORDER BY btypeID; CALL logger (NULL, "SELECT", "getBtype: All from businesstype written by OSCHKAR"); ELSE SET typeid = (SELECT btypeID FROM businesstype WHERE description = businessdesc); CALL logger (typeid, "SELECT", "getBtype: Select one businesstype written by OSCHKAR"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getBusiness` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getBusiness`(IN businesstype VARCHAR(35)) BEGIN SELECT * FROM business; CALL logger (NULL, "SELECT", "getBusiness: All from business written by OSCHKAR"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getContactPersonView` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getContactPersonView`(IN decrypt_key VARCHAR(45)) BEGIN SELECT contactid, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS Kontakt, CASE WHEN Telefon != "" THEN AES_DECRYPT(UNHEX(Telefon), decrypt_key) END AS Telefon, CASE WHEN Mobil != "" THEN AES_DECRYPT(UNHEX(Mobil), decrypt_key) END AS Handy, CASE WHEN `E-Mail` != "" THEN AES_DECRYPT(UNHEX(`E-Mail`), decrypt_key) END AS `E-Mail`, `Position`, Entscheider AS Entscheider FROM contactpersonview; CALL logger (NULL, "SELECT", "getContactPersonView: All from getContactPersonView written by OSCHKAR"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getCostOffice` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getCostOffice`(IN costoffice VARCHAR(50), OUT costofficeid INT(11)) BEGIN SELECT officeid INTO costofficeid FROM office WHERE office = costoffice; IF costofficeid IS NULL THEN INSERT INTO office (office) VALUES (costoffice); SET costofficeid = LAST_INSERT_ID(); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getCustomer` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getCustomer`(IN bus_id INT(11), IN decrypt_key VARCHAR(45)) BEGIN select `bt`.`businessid` AS `BusinessId`, `bt`.`contactpersonid` AS `KontaktId`, coalesce(`bt`.`company`, '') AS `Kundenname`, coalesce(`bt`.`phone`, '') AS `Telefon`, coalesce(`bt`.`mobilephone`, '') AS `Handy`, coalesce(`bt`.`email`, '') AS `E-Mail`, coalesce(`bt`.`homepage`, '') AS `Webseite`, coalesce(`bt`.`director`, '') AS `CEO`, coalesce(`bt`.`info`, '') AS `Info`, coalesce(`bt`.`taxno`, '') AS `Steuer-ID`, coalesce(`st`.`name`, '') AS `Straße`, coalesce(`hn`.`houseno`, '') AS `Hausnummer`, coalesce(lpad(`pt`.`postcode`, 5, '0'), '') AS `PLZ`, coalesce(`ad`.`city`, '') AS `Ort` from (((((`pyqcrm`.`business` `bt` join `pyqcrm`.`location_ct` `lc` on (`bt`.`businessid` = `lc`.`businessid`)) join `pyqcrm`.`address` `ad` on (`lc`.`addressid` = `ad`.`addressid`)) join `pyqcrm`.`house` `hn` on (`lc`.`houseid` = `hn`.`houseid`)) join `pyqcrm`.`street` `st` on (`hn`.`strid` = `st`.`strid`)) join `pyqcrm`.`postcode` `pt` on (`st`.`postcodeid` = `pt`.`postcodeid`)) where `bt`.`businessid` = bus_id; CALL logger (bus_id, "SELECT", "getCustomer: Get a client"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getCustomerContact` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getCustomerContact`(IN cont_id INT(11), IN decrypt_key VARCHAR(45)) BEGIN select `ct`.`contactpersonid` AS `KontaktId`, coalesce(`ct`.`salutation`, '') AS `Anrede`, coalesce(AES_DECRYPT(UNHEX(`ct`.`firstname`), decrypt_key), '') AS `Vorname`, coalesce(AES_DECRYPT(UNHEX(`ct`.`lastname`), decrypt_key), '') AS `Nachname`, coalesce(AES_DECRYPT(UNHEX(`ct`.`phone`), decrypt_key), '') AS `K-Telefon`, coalesce(AES_DECRYPT(UNHEX(`ct`.`mobilephone`), decrypt_key), '') AS `K-Handy`, coalesce(`ct`.`pos`, '') AS `Stelle`, coalesce(AES_DECRYPT(UNHEX(`ct`.`email`), decrypt_key), '') AS `K-Email`, coalesce(AES_DECRYPT(UNHEX(`ct`.`birthday`), decrypt_key), '') AS `Geburtsdatum`, `ct`.`priority` AS `Prio`, `ct`.`invoice` AS `Abrechnung`, `ct`.`reminder` AS `Mahnung` from `pyqcrm`.`contactperson` `ct` where `ct`.`contactpersonid` = cont_id; CALL logger (cont_id, "SELECT", "getCustomerContact: Get a client's contact person"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getCustomerView` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getCustomerView`(IN decrypt_key VARCHAR(45), IN criterion VARCHAR(35)) BEGIN IF criterion = "Interessent" THEN SELECT BusinessId AS customer, `Kundenname`, CONCAT(Straße, " ", Hausnummer) AS Straße, PLZ, Ort, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS Ansprechpartner FROM interestedinfo ORDER BY Kundenname ASC; CALL logger (NULL, "SELECT", "getCustomerView: Get all Interessent written by OSCHKAR"); ELSEIF criterion = "Kunden" THEN SELECT BusinessId AS customer, Kundenname, CONCAT(Straße, " ", Hausnummer) AS Straße, PLZ, Ort, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS Ansprechpartner FROM clienteleinfo ORDER BY Kundenname ASC; CALL logger (NULL, "SELECT", "getCustomerView: Get all Kunden written by OSCHKAR"); ELSEIF criterion = "Lieferant" THEN SELECT BusinessId AS customer, Kundenname, CONCAT(Straße, " ", Hausnummer) AS Straße, PLZ, Ort, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS Ansprechpartner FROM providerinfo ORDER BY Kundenname ASC; CALL logger (NULL, "SELECT", "getCustomerView: Get all Lieferant written by OSCHKAR"); ELSEIF criterion = "Erledigt" THEN SELECT BusinessId AS customer, Kundenname, CONCAT(Straße, " ", Hausnummer) AS Straße, PLZ, Ort, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS Ansprechpartner FROM finishedinfo ORDER BY Kundenname ASC; CALL logger (NULL, "SELECT", "getCustomerView: Get all Erledigt written by OSCHKAR"); ELSE SELECT BusinessId AS customer, `Kundenname`, CONCAT(Straße, " ", Hausnummer) AS Straße, PLZ, Ort, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS Ansprechpartner FROM customerinfo ORDER BY Kundenname ASC; CALL logger (NULL, "SELECT", "getCustomerView: Get all Customer written by OSCHKAR"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getEmployeeContract` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getEmployeeContract`(IN g_contractstart DATE, IN g_contractend DATE, IN g_workhours TINYINT(1)UNSIGNED, IN g_workdays TINYINT(1)UNSIGNED, IN g_salary DECIMAL(5,2) UNSIGNED, IN g_employeeid INT(11), IN job_desc VARCHAR(100)) BEGIN DECLARE db_empcontractid INT (11); SELECT empcontractid INTO db_empcontractid FROM employeecontract WHERE employeeid = g_employeeid; CALL logger (db_empcontractid, 'SELECT', 'getEmployeeContract() - empcontractid from employeecontract'); IF db_empcontractid IS NULL THEN INSERT INTO employeecontract (contractstart, contractend, salary, employeeid, jobdescription) VALUES (g_contractstart, g_contractend, g_salary, g_employeeid, job_desc); SET db_empcontractid = LAST_INSERT_ID(); END IF; INSERT INTO employeeweeklyhours (workhours, workdays, empcontractid) VALUES (g_workhours, g_workdays, db_empcontractid); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getEmployeeContractType` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getEmployeeContractType`(IN descr VARCHAR(30), OUT empcontractid INT (11)) BEGIN SELECT employeecontracttypeid INTO empcontractid FROM employeecontracttype WHERE description = descr; CALL logger (empcontractid, 'SELECT', CONCAT('getEmployeeContractType() - ', descr)); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getEmployeeTable` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getEmployeeTable`(IN criterion VARCHAR(30), IN procesado BOOL, IN fired BOOL, IN proc_and_nonproc BOOL, IN decrypt_key VARCHAR(45)) BEGIN DECLARE is_processed VARCHAR(13); SET is_processed = CASE WHEN procesado = TRUE THEN 'processed' ELSE 'non-processed' END; IF fired = TRUE THEN CALL getFiredAppliEmpView(criterion, decrypt_key); ELSEIF criterion = "Bewerber" THEN CALL getOnlyApplicants(procesado, proc_and_nonproc, decrypt_key); ELSEIF criterion = "Mitarbeiter" THEN CALL getOnlyEmployees(procesado, proc_and_nonproc, decrypt_key); ELSE IF proc_and_nonproc = TRUE THEN SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, `Bewerber`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Mitarbeiter/Bewerber`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt`, `Beschäftigungsverhältnis` FROM employeeapplicantview ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", "getEmployeeTable: Get all employees and applicants"); ELSE SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, `Bewerber`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Mitarbeiter/Bewerber`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt`, `Beschäftigungsverhältnis` FROM employeeapplicantview WHERE `Erledigt` = procesado ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", CONCAT("getEmployeeTable: Get ", is_processed, " employees and applicants")); END IF; END IF; COMMIT; -- In order to get the logs written!! 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getFiredAppliEmpView` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getFiredAppliEmpView`(IN criterion VARCHAR(30), IN decrypt_key VARCHAR(45)) BEGIN IF criterion = "Bewerber" THEN SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Bewerber`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt` FROM applicantview WHERE `Beschäftigungsverhältnis` = 'Ausgeschieden' ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", "getFiredAppliEmpView: Get fired applicants "); ELSEIF criterion = "Mitarbeiter" THEN SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Mitarbeiter`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt` FROM employeeview WHERE `Beschäftigungsverhältnis` = 'Ausgeschieden' ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", "getFiredAppliEmpView: Get fired employees"); ELSE SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, `Bewerber`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Mitarbeiter/Bewerber`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt` FROM employeeapplicantview WHERE `Beschäftigungsverhältnis` = 'Ausgeschieden' ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", "getFiredAppliEmpView: Get fired employees and applicants"); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getIdentification` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getIdentification`(IN g_idnumber INT(11), IN g_idissued DATE, IN g_idexpiry DATE, IN g_idauthority VARCHAR(50), IN g_idtype VARCHAR(50), IN g_employeeid INT(11)) BEGIN DECLARE db_typeid INT(11); SELECT typeid INTO db_typeid FROM identificationtype WHERE idtype = g_idtype; CALL logger (db_typeid, 'SELECT', 'getIdentification - get a typeid'); IF db_typeid IS NULL THEN INSERT INTO identificationtype (idtype) VALUES (g_idtype); SET db_typeid = LAST_INSERT_ID(); END IF; INSERT INTO identification(idnumber, issued, expiry, authority, typeid, employeeid) VALUES (g_idnumber, g_idissued, g_idexpiry, g_idauthority, db_typeid, g_employeeid); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getLastInsertId` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getLastInsertId`() BEGIN SELECT LAST_INSERT_ID(); CALL logger ((SELECT LAST_INSERT_ID()), "SELECT", "getLastInsertId: Select LastInsertID written by OSCHKAR"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getMaritalStatus` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getMaritalStatus`(IN mstatus VARCHAR(25), OUT statusid INT(11)) BEGIN SELECT maritalstatusid INTO statusid FROM maritalstatus WHERE status = mstatus; CALL logger (statusid, 'SELECT', CONCAT('getMaritalstatus() - ', mstatus)); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getNationality` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getNationality`(IN ncountry VARCHAR(200), OUT ncountryid INT(11)) BEGIN SELECT countryid INTO ncountryid FROM country WHERE country LIKE CONCAT('%', ncountry, '%'); CALL logger(ncountryid, 'SELECT', 'getNationality - get countryid'); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getObjects` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getObjects`(IN enabled CHAR(7), IN enc_key VARCHAR(45)) BEGIN CASE enabled WHEN 'Aktiv' THEN SELECT `ObjektId`, `Objekt-Nr.`, `Parteien`, `Besonderheiten`, `Kunde`, CONCAT(Hi1, ' ', AES_DECRYPT(UNHEX(Fkon1), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon1), enc_key)) AS `Kontakt I`, CONCAT(Hi2, ' ', AES_DECRYPT(UNHEX(Fkon2), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon2), enc_key)) AS `Kontakt II`, CONCAT(Hi3, ' ', AES_DECRYPT(UNHEX(Fkon3), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon3), enc_key)) AS `Kontakt III` FROM objectview WHERE Aktiv = TRUE; WHEN 'Inaktiv' THEN SELECT `ObjektId`, `Objekt-Nr.`, `Parteien`, `Besonderheiten`, `Kunde`, CONCAT(Hi1, ' ', AES_DECRYPT(UNHEX(Fkon1), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon1), enc_key)) AS `Kontakt I`, CONCAT(Hi2, ' ', AES_DECRYPT(UNHEX(Fkon2), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon2), enc_key)) AS `Kontakt II`, CONCAT(Hi3, ' ', AES_DECRYPT(UNHEX(Fkon3), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon3), enc_key)) AS `Kontakt III` FROM objectview WHERE Aktiv = FALSE; ELSE SELECT `ObjektId`, `Objekt-Nr.`, `Parteien`, `Besonderheiten`, `Kunde`, CONCAT(Hi1, ' ', AES_DECRYPT(UNHEX(Fkon1), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon1), enc_key)) AS `Kontakt I`, CONCAT(Hi2, ' ', AES_DECRYPT(UNHEX(Fkon2), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon2), enc_key)) AS `Kontakt II`, CONCAT(Hi3, ' ', AES_DECRYPT(UNHEX(Fkon3), enc_key), ' ', AES_DECRYPT(UNHEX(Lkon3), enc_key)) AS `Kontakt III` FROM objectview; END CASE; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getOnlyApplicants` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getOnlyApplicants`(IN procesado BOOL, IN everyone BOOL, IN decrypt_key VARCHAR(45)) BEGIN DECLARE is_processed VARCHAR(13); SET is_processed = CASE WHEN procesado = TRUE THEN 'processed' ELSE 'non-processed' END; IF everyone = TRUE THEN SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Bewerber`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt`, `Beschäftigungsverhältnis` FROM applicantview ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", "getOnlyApplicants: Get all applicants"); ELSE SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Bewerber`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt`, `Beschäftigungsverhältnis` FROM applicantview WHERE Erledigt = procesado ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", CONCAT("getOnlyApplicants: Get all ", is_processed, " applicants")); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getOnlyEmployees` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getOnlyEmployees`(IN procesado BOOL, IN everyone BOOL, IN decrypt_key VARCHAR(45)) BEGIN DECLARE is_processed VARCHAR(13); SET is_processed = CASE WHEN procesado = TRUE THEN 'processed' ELSE 'non-processed' END; IF everyone = TRUE THEN SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Mitarbeiter`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt`, `Beschäftigungsverhältnis` FROM employeeview ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", "getOnlyEmployees: Get all employees"); ELSE SELECT `EmployeeId` AS employee, CONVERT(AES_DECRYPT(UNHEX(Aktenzeichen), decrypt_key), CHAR) AS `Aktenzeichen`, CONCAT(Anrede, " ", AES_DECRYPT(UNHEX(Vorname), decrypt_key), " ", AES_DECRYPT(UNHEX(Nachname), decrypt_key)) AS `Mitarbeiter`, CONVERT(AES_DECRYPT(UNHEX(Mobil), decrypt_key), CHAR) AS `Handy`, CONVERT(AES_DECRYPT(UNHEX(Telefon), decrypt_key), CHAR) AS `Telefon`, CONVERT(AES_DECRYPT(UNHEX(Email), decrypt_key), CHAR) AS `E-Mail`, `Stadt`, `Beschäftigungsverhältnis` FROM employeeview WHERE Erledigt = procesado ORDER BY Vorname ASC; CALL logger (NULL, "SELECT", CONCAT("getOnlyEmployees: Get all ", is_processed, " employees")); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getSocialInformation` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getSocialInformation`(IN g_socialno VARCHAR(75), IN g_knappschaft VARCHAR(50), IN g_medicalinsurance VARCHAR(50), IN g_employeeid INT(11)) BEGIN DECLARE db_medicalcareid INT(11); SELECT medicalcareid INTO db_medicalcareid FROM medicalcare WHERE g_medicalinsurance = company; CALL logger(db_medicalcareid, 'SELECT', 'getSocialInformation - add a company'); IF db_medicalcareid IS NULL THEN -- No insurance company, let's add it INSERT INTO medicalcare (company) VALUES (g_medicalinsurance); SET db_medicalcareid = LAST_INSERT_ID(); END IF; INSERT INTO socialinformation (socialsecurityno, employeeid, medicalcareid, knappschaft) VALUES (g_socialno, g_employeeid, db_medicalcareid, g_knappschaft); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getSupervisor` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getSupervisor`(IN g_supervisor VARCHAR(50), OUT g_userid INT(11)) BEGIN DECLARE db_roleid INT(11); SELECT us.usersid, ro.roleid INTO g_userid, db_roleid FROM users AS us, userrole AS ro WHERE ro.description = 'Disponent' AND us.gecos = g_supervisor AND us.roleid = ro.roleid; IF g_userid IS NULL THEN SELECT ro.roleid INTO db_roleid FROM userrole AS ro WHERE ro.description = 'Disponent'; INSERT INTO users(username, password, enabled, roleid, gecos) VALUES (g_supervisor, g_supervisor, 0, db_roleid, g_supervisor); SET g_userid = LAST_INSERT_ID(); END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `getUser` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `getUser`( IN uname VARCHAR(50)) BEGIN SELECT * FROM users WHERE username = uname AND enabled = 1; CALL logger (NULL, "SELECT", "getUser: Select User written by OSCHKAR"); 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `logger` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `logger`(recid INT(11), operation VARCHAR(100), msg VARCHAR(500)) BEGIN INSERT INTO logs (recordid, operationtype, message) VALUES (recid, operation, msg); -- COMMIT; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `logObjectContact` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `logObjectContact`() BEGIN IF @c_1id != 0 THEN CALL logger(@c_1id, "INSERT", "addObjectContact: Object contact 1 added"); SET @c_1id = 0; END IF; IF @c_2id != 0 THEN CALL logger(@c_2id, "INSERT", "addObjectContact: Object contact 2 added"); SET @c_2id = 0; END IF; IF @c_3id != 0 THEN CALL logger(@c_3id, "INSERT", "addObjectContact: Object contact 3 added"); SET @c_3id = 0; END IF; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `updateBusiness` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `updateBusiness`(IN p_businessid INT(11), IN update_business JSON) BEGIN DECLARE v_company VARCHAR(100); DECLARE v_phone VARCHAR(50); DECLARE v_cell VARCHAR(50); DECLARE v_email VARCHAR(255); DECLARE v_homepage VARCHAR(255); DECLARE v_director VARCHAR(100); DECLARE v_info VARCHAR(500); DECLARE v_taxno VARCHAR(50); DECLARE v_city VARCHAR(50); DECLARE v_houseno VARCHAR(6); DECLARE v_postcode INT(11); DECLARE v_street VARCHAR(100); DECLARE v_houseid INT(11); DECLARE v_addressid INT(11); DECLARE v_postcodeid INT(11); DECLARE v_streetid INT(11); DECLARE x_postcodeid INT(11); DECLARE x_addressid INT(11); SET v_company = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.company')); SET v_phone = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.phone')); SET v_cell = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.cell')); SET v_email = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.email')); SET v_homepage = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.homepage')); SET v_director = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.ceo')); SET v_info = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.info')); SET v_taxno = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.tax')); SET v_city = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.city')); SET v_houseno = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.house')); SET v_postcode = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.zip')); SET v_street = JSON_UNQUOTE(JSON_EXTRACT(update_business, '$.street')); SELECT lct.postcodeid, lct.houseid, lct.addressid INTO v_postcodeid, v_houseid, v_addressid FROM location_ct as lct WHERE lct.businessid = p_businessid; SELECT h.strid INTO v_streetid FROM house as h WHERE h.houseid = v_houseid; SELECT p.postcodeid, p.addressid INTO x_postcodeid, x_addressid FROM postcode as p WHERE p.postcode = v_postcode; UPDATE business SET company = COALESCE(v_company, company), phone = COALESCE(v_phone, phone), mobilephone = COALESCE(v_cell, mobilephone), email = COALESCE(v_email, email), homepage = COALESCE(v_homepage, homepage), director = COALESCE(v_director, director), info = COALESCE(v_info, info), taxno = COALESCE(v_taxno, taxno) WHERE businessid = p_businessid; UPDATE address SET city = v_city WHERE addressid = v_addressid; -- UPDATE postcode SET postcode = v_postcode WHERE addressid = v_addressid AND postcodeid = v_postcodeid; UPDATE street SET name = v_street, postcodeid = x_postcodeid WHERE postcodeid = v_postcodeid AND strid = v_streetid; UPDATE house SET houseno = v_houseno WHERE houseid = v_houseid AND strid = v_streetid; UPDATE location_ct SET addressid = v_addressid, postcodeid = x_postcodeid WHERE businessid = p_businessid; 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 = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `updateContactPerson` */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`pyqcrm`@`%` PROCEDURE `updateContactPerson`(IN p_contactpersonid INT(11), IN update_contact JSON) BEGIN DECLARE x_salute VARCHAR(15); DECLARE x_fname VARCHAR (75); DECLARE x_lname VARCHAR (75); DECLARE x_phone VARCHAR (75); DECLARE x_cell VARCHAR (75); DECLARE x_pos VARCHAR (50); DECLARE x_email VARCHAR (255); DECLARE x_birthday VARCHAR (35); DECLARE x_priority BOOL; DECLARE x_invoice BOOL; DECLARE x_reminder BOOL; SET x_salute = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.salute')); SET x_fname = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.fname')); SET x_lname = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.lname')); SET x_phone = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.phone')); SET x_cell = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.cell')); SET x_email = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.email')); SET x_birthday = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.birthday')); SET x_pos = JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.position')); SET x_priority = if(JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.priority')) = 'Ja', true, false); SET x_invoice = if(JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.invoice')) = 'Ja', true, false); SET x_reminder = if(JSON_UNQUOTE(JSON_EXTRACT(update_contact, '$.reminder')) = 'Ja', true, false); UPDATE contactperson SET salutation = COALESCE(x_salute, salutation), firstname = COALESCE(x_fname, firstname), lastname = COALESCE(x_lname, lastname), phone = COALESCE(x_phone, phone), mobilephone = COALESCE(x_cell, mobilephone), email = COALESCE(x_email, email), birthday = COALESCE(x_birthday, birthday), pos = COALESCE(x_pos, pos), priority = COALESCE(x_priority, priority), invoice = COALESCE(x_invoice, invoice), reminder = COALESCE(x_reminder, reminder) WHERE contactpersonid = p_contactpersonid; 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 `addressesview` -- /*!50001 DROP VIEW IF EXISTS `addressesview`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `addressesview` AS select 1 AS `1` */; /*!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 `applicantview` -- /*!50001 DROP VIEW IF EXISTS `applicantview`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `applicantview` AS select `emp`.`employeeid` AS `EmployeeId`,`emp`.`reference` AS `Aktenzeichen`,coalesce(`emp`.`firstname`,'') AS `Vorname`,coalesce(`emp`.`lastname`,'') AS `Nachname`,coalesce(`emp`.`salutation`,'') AS `Anrede`,coalesce(`emp`.`mobilephone`,'') AS `Mobil`,coalesce(`emp`.`phone`,'') AS `Telefon`,coalesce(`emp`.`email`,'') AS `Email`,coalesce(`ect`.`description`,'') AS `Beschäftigungsverhältnis`,`emp`.`processed` AS `Erledigt` from (`employee` `emp` left join `employeecontracttype` `ect` on(`emp`.`employeecontracttypeid` = `ect`.`employeecontracttypeid`)) where `emp`.`applicant` = 1 */; /*!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 `clienteleinfo` -- /*!50001 DROP VIEW IF EXISTS `clienteleinfo`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `clienteleinfo` AS select `bt`.`businessid` AS `BusinessId`,coalesce(`bt`.`company`,'') AS `Kundenname`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((`business` `bt` left join `contactperson` `ct` on(`bt`.`contactpersonid` = `ct`.`contactpersonid`)) join `businesstype` `btt` on(`bt`.`btid` = `btt`.`btypeID`)) where `btt`.`description` = 'Kunde' */; /*!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 `contactpersonview` -- /*!50001 DROP VIEW IF EXISTS `contactpersonview`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `contactpersonview` AS select `contactperson`.`contactpersonid` AS `contactid`,`contactperson`.`salutation` AS `Anrede`,`contactperson`.`firstname` AS `Vorname`,`contactperson`.`lastname` AS `Nachname`,coalesce(`contactperson`.`phone`,'') AS `Telefon`,coalesce(`contactperson`.`mobilephone`,'') AS `Mobil`,coalesce(`contactperson`.`email`,'') AS `E-Mail`,coalesce(`contactperson`.`pos`,'') AS `Position`,case when `contactperson`.`priority` = 1 then 'JA' else 'NEIN' end AS `Entscheider` from `contactperson` order by `contactperson`.`firstname` */; /*!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 `customerinfo` -- /*!50001 DROP VIEW IF EXISTS `customerinfo`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `customerinfo` AS select `bt`.`businessid` AS `BusinessId`,coalesce(`bt`.`company`,'') AS `Kundenname`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from (`business` `bt` left join `contactperson` `ct` on(`bt`.`contactpersonid` = `ct`.`contactpersonid`)) */; /*!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 `employeeapplicantview` -- /*!50001 DROP VIEW IF EXISTS `employeeapplicantview`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `employeeapplicantview` AS select `emp`.`employeeid` AS `EmployeeId`,`emp`.`reference` AS `Aktenzeichen`,coalesce(`emp`.`firstname`,'') AS `Vorname`,coalesce(`emp`.`lastname`,'') AS `Nachname`,coalesce(`emp`.`salutation`,'') AS `Anrede`,coalesce(`emp`.`mobilephone`,'') AS `Mobil`,coalesce(`emp`.`phone`,'') AS `Telefon`,coalesce(`emp`.`email`,'') AS `Email`,coalesce(`ect`.`description`,'') AS `Beschäftigungsverhältnis`,`emp`.`processed` AS `Erledigt`,`emp`.`applicant` AS `Bewerber` from (`employee` `emp` left join `employeecontracttype` `ect` on(`emp`.`employeecontracttypeid` = `ect`.`employeecontracttypeid`)) */; /*!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 `employeeview` -- /*!50001 DROP VIEW IF EXISTS `employeeview`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `employeeview` AS select `emp`.`employeeid` AS `EmployeeId`,`emp`.`reference` AS `Aktenzeichen`,coalesce(`emp`.`firstname`,'') AS `Vorname`,coalesce(`emp`.`lastname`,'') AS `Nachname`,coalesce(`emp`.`salutation`,'') AS `Anrede`,coalesce(`emp`.`mobilephone`,'') AS `Mobil`,coalesce(`emp`.`phone`,'') AS `Telefon`,coalesce(`emp`.`email`,'') AS `Email`,coalesce(`ect`.`description`,'') AS `Beschäftigungsverhältnis`,`emp`.`processed` AS `Erledigt`,`emp`.`applicant` AS `Bewerber` from (`employee` `emp` left join `employeecontracttype` `ect` on(`emp`.`employeecontracttypeid` = `ect`.`employeecontracttypeid`)) where `emp`.`applicant` = 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 `empprocessedview` -- /*!50001 DROP VIEW IF EXISTS `empprocessedview`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `empprocessedview` AS select 1 AS `1` */; /*!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 `finishedinfo` -- /*!50001 DROP VIEW IF EXISTS `finishedinfo`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `finishedinfo` AS select `bt`.`businessid` AS `BusinessId`,coalesce(`bt`.`company`,'') AS `Kundenname`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((`business` `bt` left join `contactperson` `ct` on(`bt`.`contactpersonid` = `ct`.`contactpersonid`)) join `businesstype` `btt` on(`bt`.`btid` = `btt`.`btypeID`)) where `btt`.`description` = 'Erledigt' */; /*!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 `interestedinfo` -- /*!50001 DROP VIEW IF EXISTS `interestedinfo`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `interestedinfo` AS select `bt`.`businessid` AS `BusinessId`,coalesce(`bt`.`company`,'') AS `Kundenname`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((`business` `bt` left join `contactperson` `ct` on(`bt`.`contactpersonid` = `ct`.`contactpersonid`)) left join `businesstype` `btt` on(`bt`.`btid` = `btt`.`btypeID`)) where `btt`.`description` = 'Interessent' */; /*!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 `objectview` -- /*!50001 DROP VIEW IF EXISTS `objectview`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `objectview` AS select `ob`.`objectid` AS `ObjektId`,`ob`.`active` AS `Aktiv`,`ob`.`objectno` AS `Objekt-Nr.`,`ob`.`units` AS `Parteien`,`ob`.`remarks` AS `Besonderheiten`,`bsn`.`company` AS `Kunde`,`ct1`.`salutation` AS `Hi1`,`ct1`.`firstname` AS `Fkon1`,`ct1`.`lastname` AS `Lkon1`,`ct2`.`salutation` AS `Hi2`,`ct2`.`firstname` AS `Fkon2`,`ct2`.`lastname` AS `Lkon2`,`ct3`.`salutation` AS `Hi3`,`ct3`.`firstname` AS `Fkon3`,`ct3`.`lastname` AS `Lkon3` from (((((`objects` `ob` left join `objectmanagement` `omt` on(`ob`.`objectid` = `omt`.`objectid`)) left join `business` `bsn` on(`omt`.`businessid` = `bsn`.`businessid`)) left join `contactperson` `ct1` on(`omt`.`contact1id` = `ct1`.`contactpersonid`)) left join `contactperson` `ct2` on(`omt`.`contact2id` = `ct2`.`contactpersonid`)) left join `contactperson` `ct3` on(`omt`.`contact3id` = `ct3`.`contactpersonid`)) */; /*!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 `providerinfo` -- /*!50001 DROP VIEW IF EXISTS `providerinfo`*/; /*!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_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`pyqcrm`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `providerinfo` AS select `bt`.`businessid` AS `BusinessId`,coalesce(`bt`.`company`,'') AS `Kundenname`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((`business` `bt` left join `contactperson` `ct` on(`bt`.`contactpersonid` = `ct`.`contactpersonid`)) join `businesstype` `btt` on(`bt`.`btid` = `btt`.`btypeID`)) where `btt`.`description` = 'Lieferant' */; /*!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 */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2025-05-09 15:37:22