/*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, `country` varchar(50) DEFAULT NULL, `city` varchar(500) NOT NULL, PRIMARY KEY (`addressid`), UNIQUE KEY `address_unique` (`city`) ) ENGINE=InnoDB AUTO_INCREMENT=7042 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 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`) ) 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, `street` varchar(100) NOT NULL, `postcodeid` int(11) DEFAULT 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, PRIMARY KEY (`businessid`), UNIQUE KEY `customer_unique` (`email`), UNIQUE KEY `business_unique` (`taxno`) ) ENGINE=InnoDB AUTO_INCREMENT=24 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 */; -- -- 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` ( `id` int(11) NOT NULL AUTO_INCREMENT, `contactpersonid` int(11) NOT NULL, `businessid` tinyint(1) NOT NULL DEFAULT 1, UNIQUE KEY `contact_unique` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 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, `priorityid` int(11) DEFAULT NULL, `lastname` varchar(35) NOT NULL, `firstname` varchar(35) NOT NULL, `phone` varchar(50) DEFAULT NULL, `mobilephone` varchar(50) DEFAULT NULL, `position` varchar(50) DEFAULT NULL, `salutation` varchar(10) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `birthday` date DEFAULT NULL, `active` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`contactpersonid`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 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`) ) 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 `PLZ`, 1 AS `Ort`, 1 AS `Anrede`, 1 AS `Ansprechpartner` */; 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, `gender` varchar(30) DEFAULT NULL, `maritalstatus` varchar(30) DEFAULT NULL, `nationality` varchar(50) NOT NULL, `birthday` date NOT NULL, `street` varchar(100) 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, PRIMARY KEY (`employeeid`), UNIQUE KEY `employee_unique` (`identificationid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 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 AUTO_INCREMENT=5 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`) ) 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 */; -- -- 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`) ) 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`) ) 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, `street` varchar(100) NOT NULL, `postcodeid` int(11) NOT NULL, `contactpersonid` int(11) NOT NULL, `businessid` int(11) NOT NULL, PRIMARY KEY (`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`) ) 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 ) 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 AUTO_INCREMENT=5 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 ) 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`) ) ENGINE=InnoDB AUTO_INCREMENT=8888 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 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 AUTO_INCREMENT=3 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 ) 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`) ) 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 ) 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` (`employeeid`), UNIQUE KEY `socialsecurity_unique_1` (`socialsecurityno`) ) 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 ) 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`) ) ENGINE=InnoDB AUTO_INCREMENT=16 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`(new_business JSON) BEGIN DECLARE v_company VARCHAR(100); 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_contactperson VARCHAR(100); DECLARE v_info VARCHAR(500); DECLARE v_taxno VARCHAR(50); DECLARE v_bt VARCHAR(50); SET v_company = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.business')); 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, '$.mobilephone')); 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_contactperson = JSON_UNQUOTE(JSON_EXTRACT(new_business, '$.')); 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')); -- INSERT INTO business(company, street, postcodeid, phone, mobilephone, email, -- homepage, director, contactperson, info, taxno, btid) -- VALUES (v_company, v_street, v_postcodeid, v_phone,v_cell, -- v_email,v_homepage, v_director, v_contactpersonid, v_info, v_taxno, v_bt); 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 IF NOT EXISTS (SELECT 1 FROM permissions WHERE permission = newpermission) THEN INSERT INTO permissions(permission) VALUES(newpermission); 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 IF NOT EXISTS (SELECT 1 FROM `role` WHERE description = newdescription) THEN INSERT INTO `role`(description) VALUES(newdescription); 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(500)) 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(); ELSE SELECT addressid INTO zip_town FROM address WHERE city = town; END IF; IF NOT EXISTS (SELECT 1 FROM postcode WHERE postcode = zipcode) THEN INSERT INTO postcode(postcode, addressid) VALUES(zipcode, zip_town); 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; 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; IF NOT EXISTS (SELECT 1 FROM users WHERE username = uname) THEN INSERT INTO users(username, password, gecos, roleid) VALUES(uname, password, gecos, userrole); 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 `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; 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`() BEGIN SELECT * FROM customerinfo ORDER BY Kundenname ASC; 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; 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 `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(`bt`.`street`,'') AS `Straße`,coalesce(`pt`.`postcode`,'') AS `PLZ`,coalesce(`at`.`city`,'') AS `Ort`,coalesce(`ct`.`salutation`,'') AS `Anrede`,coalesce(concat(`ct`.`firstname`,' ',`ct`.`lastname`),'') AS `Ansprechpartner` from ((((`business` `bt` left join `postcode` `pt` on(`bt`.`postcodeid` = `pt`.`postcodeid`)) left join `address` `at` on(`pt`.`addressid` = `at`.`addressid`)) left join `contact` `cct` on(`cct`.`businessid` = `bt`.`businessid`)) left join `contactperson` `ct` on(`ct`.`contactpersonid` = `cct`.`contactpersonid`)) */; /*!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 2024-12-05 15:36:34