/*M!999999\- enable the sandbox mode */ -- MariaDB dump 10.19 Distrib 10.11.10-MariaDB, for Linux (x86_64) -- -- Host: bearybot.selfhost.co Database: pyqcrm -- ------------------------------------------------------ -- Server version 10.11.8-MariaDB-0ubuntu0.24.04.1 /*!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 = utf8 */; CREATE TABLE `address` ( `addressid` int(11) NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `countryid` int(11) NOT NULL, PRIMARY KEY (`addressid`), KEY `address_country_FK` (`countryid`), CONSTRAINT `address_country_FK` FOREIGN KEY (`countryid`) REFERENCES `country` (`countryid`) ) ENGINE=InnoDB 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 = utf8; /*!50001 CREATE VIEW `addressesview` AS SELECT 1 AS `postcodeid`, 1 AS `addressid`, 1 AS `postcode`, 1 AS `city`, 1 AS `country` */; 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 = utf8 */; 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 `business` -- DROP TABLE IF EXISTS `business`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `business` ( `businessid` int(11) NOT NULL AUTO_INCREMENT, `company` varchar(100) NOT NULL, `postcodeid` int(11) 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_postcode_FK` (`postcodeid`), 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, CONSTRAINT `business_postcode_FK` FOREIGN KEY (`postcodeid`) REFERENCES `postcode` (`postcodeid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB 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 = utf8 */; 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 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 = utf8; /*!50001 CREATE VIEW `clienteleinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Straße`, 1 AS `Hausnummer`, 1 AS `PLZ`, 1 AS `Ort`, 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 = utf8 */; 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 = utf8 */; 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(10) 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 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 = utf8; /*!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 = utf8 */; CREATE TABLE `contract` ( `contractid` int(11) NOT NULL AUTO_INCREMENT, `jobdetailid` int(11) NOT NULL, `tariffid` int(11) NOT NULL, `officeid` int(11) NOT NULL COMMENT 'Kostenstelle', `date` date NOT NULL, PRIMARY KEY (`contractid`), UNIQUE KEY `contract_unique` (`jobdetailid`), KEY `contract_office_FK` (`officeid`), CONSTRAINT `contract_jobdetail_FK` FOREIGN KEY (`jobdetailid`) REFERENCES `jobdetail` (`jobdetailid`), 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 = utf8 */; CREATE TABLE `country` ( `countryid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`countryid`), UNIQUE KEY `country_unique` (`name`) ) ENGINE=InnoDB 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 = utf8; /*!50001 CREATE VIEW `customerinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Straße`, 1 AS `Hausnummer`, 1 AS `PLZ`, 1 AS `Ort`, 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 = utf8 */; CREATE TABLE `employee` ( `employeeid` int(11) NOT NULL AUTO_INCREMENT, `lastname` varchar(35) NOT NULL, `firstname` varchar(35) NOT NULL, `maritalstatus` varchar(30) DEFAULT NULL, `nationality` varchar(50) NOT NULL, `birthday` date NOT NULL, `postcodeid` int(11) NOT NULL, `phone` varchar(50) DEFAULT NULL, `mobilephone` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `identificationid` int(11) NOT NULL, `jobstatusid` int(11) NOT NULL, `formofadress` varchar(30) DEFAULT NULL COMMENT 'Briefanrede', `salutation` varchar(10) NOT NULL, `minijob` tinyint(1) NOT NULL DEFAULT 0, `enabled` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`employeeid`), UNIQUE KEY `employee_unique` (`identificationid`), KEY `employee_jobstatus_FK` (`jobstatusid`), KEY `employee_postcode_FK` (`postcodeid`), CONSTRAINT `employee_identification_FK` FOREIGN KEY (`identificationid`) REFERENCES `identification` (`identificationid`), CONSTRAINT `employee_jobstatus_FK` FOREIGN KEY (`jobstatusid`) REFERENCES `jobstatus` (`jobstatusid`), CONSTRAINT `employee_postcode_FK` FOREIGN KEY (`postcodeid`) REFERENCES `postcode` (`postcodeid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 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 = utf8 */; CREATE TABLE `employeecontract` ( `empcontractid` int(11) NOT NULL AUTO_INCREMENT, `employeeid` int(11) NOT NULL, `start` date NOT NULL DEFAULT current_timestamp(), `end` date DEFAULT NULL, `limited` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Befristet/Unbefristet\r\nDefault ist unbefristet, 1 ist befristet', `hours` varchar(100) DEFAULT NULL COMMENT 'Arbeitszeiten', 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 */; -- -- Temporary table structure for view `employeesview` -- DROP TABLE IF EXISTS `employeesview`; /*!50001 DROP VIEW IF EXISTS `employeesview`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE VIEW `employeesview` AS SELECT 1 AS `firstname`, 1 AS `lastname`, 1 AS `salutation`, 1 AS `mobilephone`, 1 AS `phone`, 1 AS `email`, 1 AS `city`, 1 AS `description` */; 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 = utf8 */; 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 = utf8; /*!50001 CREATE VIEW `finishedinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Straße`, 1 AS `Hausnummer`, 1 AS `PLZ`, 1 AS `Ort`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname` */; 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 = utf8 */; 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`), CONSTRAINT `house_street_FK` FOREIGN KEY (`strid`) REFERENCES `street` (`strid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB 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 = utf8 */; CREATE TABLE `identification` ( `identificationid` int(11) NOT NULL AUTO_INCREMENT, `number` varchar(50) NOT NULL, `expiry` date NOT NULL, `issued` date NOT NULL, `authority` varchar(50) NOT NULL, `typeid` int(11) NOT NULL, PRIMARY KEY (`identificationid`), UNIQUE KEY `identification_unique` (`number`), KEY `identification_identificationtype_FK` (`typeid`), 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 */; -- -- Table structure for table `identificationtype` -- DROP TABLE IF EXISTS `identificationtype`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `identificationtype` ( `typeid` int(11) NOT NULL AUTO_INCREMENT, `type` varchar(50) NOT NULL, PRIMARY KEY (`typeid`), UNIQUE KEY `identificationtype_unique` (`type`) ) 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 = utf8; /*!50001 CREATE VIEW `interestedinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Straße`, 1 AS `Hausnummer`, 1 AS `PLZ`, 1 AS `Ort`, 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 = utf8 */; 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 = utf8 */; CREATE TABLE `jobdetail` ( `jobdetailid` int(11) NOT NULL AUTO_INCREMENT, `duration` decimal(10,0) NOT NULL, `objectid` int(11) NOT NULL, `period` varchar(15) NOT NULL, `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 `object` (`objectid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `jobstatus` -- DROP TABLE IF EXISTS `jobstatus`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `jobstatus` ( `jobstatusid` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(30) NOT NULL, PRIMARY KEY (`jobstatusid`), UNIQUE KEY `jobstatus_unique` (`description`) ) 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 = utf8 */; CREATE TABLE `logs` ( `logid` int(11) NOT NULL AUTO_INCREMENT, `recordid` int(11) DEFAULT NULL, `operationtype` varchar(100) NOT NULL, `message` varchar(100) NOT NULL, `logdate` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`logid`) ) ENGINE=InnoDB 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 = utf8 */; 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `object` -- DROP TABLE IF EXISTS `object`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `object` ( `objectid` int(11) NOT NULL AUTO_INCREMENT, `postcodeid` int(11) NOT NULL, `contactpersonid` int(11) NOT NULL, `businessid` int(11) NOT NULL, `leistungsort` varchar(50) NOT NULL, `lohnanteil inkl. fahrtkosten` int(11) DEFAULT NULL, `materialanteil` int(11) DEFAULT NULL, `zusatz1` varchar(50) DEFAULT NULL, `zusatz2` varchar(50) DEFAULT NULL, `gesamtnetto` int(11) DEFAULT NULL, `mwst` int(11) DEFAULT NULL, `gesamt` int(11) DEFAULT NULL, `info` varchar(500) DEFAULT NULL, PRIMARY KEY (`objectid`), KEY `object_contactperson_FK` (`contactpersonid`), KEY `object_business_FK` (`businessid`), KEY `object_postcode_FK` (`postcodeid`), CONSTRAINT `object_business_FK` FOREIGN KEY (`businessid`) REFERENCES `business` (`businessid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `object_contactperson_FK` FOREIGN KEY (`contactpersonid`) REFERENCES `contactperson` (`contactpersonid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `object_postcode_FK` FOREIGN KEY (`postcodeid`) REFERENCES `postcode` (`postcodeid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 = utf8 */; CREATE TABLE `object_mitarbeiter` ( `employeeid` int(11) NOT NULL, `lohnmitarbeiterprostunde` int(2) NOT NULL, `einsatzdauer` varchar(20) NOT NULL, `reinigungstage` varchar(25) NOT NULL, `tätigkeiten` varchar(50) NOT NULL, `ertrag` int(4) NOT NULL, `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 `object` (`objectid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 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 = utf8 */; CREATE TABLE `offer` ( `offerid` int(11) NOT NULL AUTO_INCREMENT, `jobdetailid` int(11) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`offerid`), KEY `offer_jobdetail_FK` (`jobdetailid`), CONSTRAINT `offer_jobdetail_FK` FOREIGN KEY (`jobdetailid`) REFERENCES `jobdetail` (`jobdetailid`) ) 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 = utf8 */; CREATE TABLE `offers` ( `offerid` int(11) NOT NULL, `contractid` int(11) NOT NULL, KEY `offers_offer_FK` (`offerid`), KEY `offers_contract_FK` (`contractid`), CONSTRAINT `offers_contract_FK` FOREIGN KEY (`contractid`) REFERENCES `contract` (`contractid`), CONSTRAINT `offers_offer_FK` FOREIGN KEY (`offerid`) REFERENCES `offer` (`offerid`) ) 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 = utf8 */; 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Kostenstelle'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `permissions` -- DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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 = utf8 */; 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 `role` (`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 = utf8 */; CREATE TABLE `postcode` ( `postcodeid` int(11) NOT NULL AUTO_INCREMENT, `postcode` varchar(15) NOT NULL, `addressid` int(11) NOT NULL, PRIMARY KEY (`postcodeid`), KEY `postcode_address_FK` (`addressid`), CONSTRAINT `postcode_address_FK` FOREIGN KEY (`addressid`) REFERENCES `address` (`addressid`) ) ENGINE=InnoDB 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 = utf8; /*!50001 CREATE VIEW `providerinfo` AS SELECT 1 AS `BusinessId`, 1 AS `Kundenname`, 1 AS `Straße`, 1 AS `Hausnummer`, 1 AS `PLZ`, 1 AS `Ort`, 1 AS `Anrede`, 1 AS `Vorname`, 1 AS `Nachname` */; SET character_set_client = @saved_cs_client; -- -- Table structure for table `role` -- DROP TABLE IF EXISTS `role`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `role` ( `roleid` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(50) NOT NULL, PRIMARY KEY (`roleid`), UNIQUE KEY `role_unique` (`description`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 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 = utf8 */; 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 `role` (`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 = utf8 */; 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 = utf8 */; 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 `object` (`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 = utf8 */; 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 = utf8 */; 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 `socialsecurity` -- DROP TABLE IF EXISTS `socialsecurity`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `socialsecurity` ( `employeeid` int(11) NOT NULL, `socialsecurityno` varchar(30) NOT NULL, `medicalcareid` int(11) NOT 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 */; -- -- Table structure for table `street` -- DROP TABLE IF EXISTS `street`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `street` ( `strid` int(11) NOT NULL AUTO_INCREMENT, `postcodeid` int(11) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`strid`), KEY `street_postcode_FK` (`postcodeid`), CONSTRAINT `street_postcode_FK` FOREIGN KEY (`postcodeid`) REFERENCES `postcode` (`postcodeid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!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 = utf8 */; CREATE TABLE `tariff` ( `tariffid` int(11) NOT NULL AUTO_INCREMENT, `tariff` decimal(10,0) NOT NULL COMMENT 'Festgelegter Stundensatz', PRIMARY KEY (`tariffid`) ) 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 = utf8 */; 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 `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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 `role` (`roleid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 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, postcodeid, phone, mobilephone, email, homepage, director, info, btid, taxno) VALUES (v_company, v_zipcodeid, v_phone, v_cell, v_email, v_homepage, v_director, v_info, v_btid, v_taxno); -- COMMIT; CALL logger ((SELECT LAST_INSERT_ID()), "INSERT", "addBusiness: New Customer added"); ELSE INSERT INTO business (company, postcodeid, phone, mobilephone, email, homepage, director, contactpersonid, info, btid, taxno) VALUES (v_company, v_zipcodeid, v_phone, v_cell, v_email, v_homepage, v_director, v_contactpersonid, v_info, v_btid, v_taxno); -- COMMIT; CALL logger ((SELECT LAST_INSERT_ID()), "INSERT", "addBusiness: New Customer added"); -- INSERT INTO contact(contactpersonid, isbusiness) VALUES(v_contactpersonid, true); 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 `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(10); 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); -- CALL logger ((SELECT LAST_INSERT_ID()), "INSERT", "addContactPerson: New Contactperson 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 `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), scid INT(11)) BEGIN INSERT INTO house (houseno, strid) VALUES (v_houseno, scid); -- COMMIT; 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 `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 `role` WHERE description = newdescription) THEN INSERT INTO `role`(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 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(country, city) VALUES("Deutschland", 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); 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; 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 userrole INT; SET userrole = 2; IF admin = 1 THEN SET userrole = 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, userrole); 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 `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 `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 `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 `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 */ ; -- -- 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 `pc`.`postcodeid` AS `postcodeid`,`pc`.`addressid` AS `addressid`,coalesce(`pc`.`postcode`,'') AS `postcode`,coalesce(`ad`.`city`,'') AS `city`,coalesce(`ct`.`name`,'') AS `country` from ((`postcode` `pc` left join `address` `ad` on(`ad`.`addressid` = `pc`.`addressid`)) left join `country` `ct` on(`ad`.`countryid` = `ct`.`countryid`)) order by `pc`.`postcode` */; /*!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(`st`.`name`,'') AS `Straße`,coalesce(`hn`.`houseno`,'') AS `Hausnummer`,coalesce(lpad(`pt`.`postcode`,5,'0'),'') AS `PLZ`,coalesce(`at`.`city`,'') AS `Ort`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((((((`business` `bt` left join `postcode` `pt` on(`bt`.`postcodeid` = `pt`.`postcodeid`)) left join `street` `st` on(`st`.`postcodeid` = `pt`.`postcodeid`)) left join `house` `hn` on(`hn`.`strid` = `st`.`strid`)) left join `address` `at` on(`pt`.`addressid` = `at`.`addressid`)) left join `contactperson` `ct` on(`ct`.`contactpersonid` = `bt`.`contactpersonid`)) left 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(`st`.`name`,'') AS `Straße`,coalesce(`hn`.`houseno`,'') AS `Hausnummer`,coalesce(lpad(`pt`.`postcode`,5,'0'),'') AS `PLZ`,coalesce(`at`.`city`,'') AS `Ort`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from (((((`business` `bt` left join `postcode` `pt` on(`bt`.`postcodeid` = `pt`.`postcodeid`)) left join `street` `st` on(`pt`.`postcodeid` = `st`.`postcodeid`)) left join `house` `hn` on(`st`.`strid` = `hn`.`strid`)) left join `address` `at` on(`pt`.`addressid` = `at`.`addressid`)) 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 `employeesview` -- /*!50001 DROP VIEW IF EXISTS `employeesview`*/; /*!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 `employeesview` AS select `emp`.`firstname` AS `firstname`,`emp`.`lastname` AS `lastname`,`emp`.`salutation` AS `salutation`,`emp`.`mobilephone` AS `mobilephone`,`emp`.`phone` AS `phone`,`emp`.`email` AS `email`,`ad`.`city` AS `city`,`jo`.`description` AS `description` from (((`employee` `emp` left join `jobstatus` `jo` on(`emp`.`jobstatusid` = `jo`.`jobstatusid`)) left join `postcode` `po` on(`emp`.`postcodeid` = `po`.`postcodeid`)) left join `address` `ad` on(`po`.`addressid` = `ad`.`addressid`)) */; /*!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(`st`.`name`,'') AS `Straße`,coalesce(`hn`.`houseno`,'') AS `Hausnummer`,coalesce(lpad(`pt`.`postcode`,5,'0'),'') AS `PLZ`,coalesce(`at`.`city`,'') AS `Ort`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((((((`business` `bt` left join `postcode` `pt` on(`bt`.`postcodeid` = `pt`.`postcodeid`)) left join `street` `st` on(`st`.`postcodeid` = `pt`.`postcodeid`)) left join `house` `hn` on(`hn`.`strid` = `st`.`strid`)) left join `address` `at` on(`pt`.`addressid` = `at`.`addressid`)) left join `contactperson` `ct` on(`ct`.`contactpersonid` = `bt`.`contactpersonid`)) left 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(`st`.`name`,'') AS `Straße`,coalesce(`hn`.`houseno`,'') AS `Hausnummer`,coalesce(lpad(`pt`.`postcode`,5,'0'),'') AS `PLZ`,coalesce(`at`.`city`,'') AS `Ort`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((((((`business` `bt` left join `postcode` `pt` on(`bt`.`postcodeid` = `pt`.`postcodeid`)) left join `street` `st` on(`st`.`postcodeid` = `pt`.`postcodeid`)) left join `house` `hn` on(`hn`.`strid` = `st`.`strid`)) left join `address` `at` on(`pt`.`addressid` = `at`.`addressid`)) left join `contactperson` `ct` on(`ct`.`contactpersonid` = `bt`.`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 `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(`st`.`name`,'') AS `Straße`,coalesce(`hn`.`houseno`,'') AS `Hausnummer`,coalesce(lpad(`pt`.`postcode`,5,'0'),'') AS `PLZ`,coalesce(`at`.`city`,'') AS `Ort`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(`ct`.`firstname`,'') AS `Vorname`,coalesce(`ct`.`lastname`,'') AS `Nachname` from ((((((`business` `bt` left join `postcode` `pt` on(`bt`.`postcodeid` = `pt`.`postcodeid`)) left join `street` `st` on(`st`.`postcodeid` = `pt`.`postcodeid`)) left join `house` `hn` on(`hn`.`strid` = `st`.`strid`)) left join `address` `at` on(`pt`.`addressid` = `at`.`addressid`)) left join `contactperson` `ct` on(`ct`.`contactpersonid` = `bt`.`contactpersonid`)) left 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-01-21 9:06:59