/*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
