New Database Dump clean and with data
This commit is contained in:
@@ -1,953 +0,0 @@
|
||||
/*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 `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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `Entities`
|
||||
--
|
||||
|
||||
LOCK TABLES `Entities` WRITE;
|
||||
/*!40000 ALTER TABLE `Entities` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `Entities` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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,
|
||||
`address` varchar(50) NOT NULL,
|
||||
PRIMARY KEY (`addressid`),
|
||||
UNIQUE KEY `address_unique` (`address`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `address`
|
||||
--
|
||||
|
||||
LOCK TABLES `address` WRITE;
|
||||
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `assignment`
|
||||
--
|
||||
|
||||
LOCK TABLES `assignment` WRITE;
|
||||
/*!40000 ALTER TABLE `assignment` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `assignment` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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) NOT NULL DEFAULT 1,
|
||||
PRIMARY KEY (`businessid`),
|
||||
UNIQUE KEY `customer_unique` (`email`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `business`
|
||||
--
|
||||
|
||||
LOCK TABLES `business` WRITE;
|
||||
/*!40000 ALTER TABLE `business` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `business` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `businesstype`
|
||||
--
|
||||
|
||||
LOCK TABLES `businesstype` WRITE;
|
||||
/*!40000 ALTER TABLE `businesstype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `businesstype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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,
|
||||
`contactpersonid` int(11) NOT NULL,
|
||||
`businessid` tinyint(1) NOT NULL DEFAULT 1,
|
||||
UNIQUE KEY `contact_unique` (`id`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contact`
|
||||
--
|
||||
|
||||
LOCK TABLES `contact` WRITE;
|
||||
/*!40000 ALTER TABLE `contact` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contact` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contactperson`
|
||||
--
|
||||
|
||||
LOCK TABLES `contactperson` WRITE;
|
||||
/*!40000 ALTER TABLE `contactperson` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contactperson` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contract`
|
||||
--
|
||||
|
||||
LOCK TABLES `contract` WRITE;
|
||||
/*!40000 ALTER TABLE `contract` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contract` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `employee`
|
||||
--
|
||||
|
||||
LOCK TABLES `employee` WRITE;
|
||||
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `identification`
|
||||
--
|
||||
|
||||
LOCK TABLES `identification` WRITE;
|
||||
/*!40000 ALTER TABLE `identification` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `identification` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `identificationtype`
|
||||
--
|
||||
|
||||
LOCK TABLES `identificationtype` WRITE;
|
||||
/*!40000 ALTER TABLE `identificationtype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `identificationtype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `invoice`
|
||||
--
|
||||
|
||||
LOCK TABLES `invoice` WRITE;
|
||||
/*!40000 ALTER TABLE `invoice` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `invoice` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `jobdetail`
|
||||
--
|
||||
|
||||
LOCK TABLES `jobdetail` WRITE;
|
||||
/*!40000 ALTER TABLE `jobdetail` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `jobdetail` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `jobstatus`
|
||||
--
|
||||
|
||||
LOCK TABLES `jobstatus` WRITE;
|
||||
/*!40000 ALTER TABLE `jobstatus` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `jobstatus` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `logs`
|
||||
--
|
||||
|
||||
LOCK TABLES `logs` WRITE;
|
||||
/*!40000 ALTER TABLE `logs` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `logs` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `medicalcare`
|
||||
--
|
||||
|
||||
LOCK TABLES `medicalcare` WRITE;
|
||||
/*!40000 ALTER TABLE `medicalcare` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `medicalcare` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `object`
|
||||
--
|
||||
|
||||
LOCK TABLES `object` WRITE;
|
||||
/*!40000 ALTER TABLE `object` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `object` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `offer`
|
||||
--
|
||||
|
||||
LOCK TABLES `offer` WRITE;
|
||||
/*!40000 ALTER TABLE `offer` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `offer` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `offers`
|
||||
--
|
||||
|
||||
LOCK TABLES `offers` WRITE;
|
||||
/*!40000 ALTER TABLE `offers` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `offers` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `office`
|
||||
--
|
||||
|
||||
LOCK TABLES `office` WRITE;
|
||||
/*!40000 ALTER TABLE `office` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `office` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `permissions`
|
||||
--
|
||||
|
||||
LOCK TABLES `permissions` WRITE;
|
||||
/*!40000 ALTER TABLE `permissions` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `permissions` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `personalrole`
|
||||
--
|
||||
|
||||
LOCK TABLES `personalrole` WRITE;
|
||||
/*!40000 ALTER TABLE `personalrole` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `personalrole` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `postcode`
|
||||
--
|
||||
|
||||
LOCK TABLES `postcode` WRITE;
|
||||
/*!40000 ALTER TABLE `postcode` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `postcode` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `role`
|
||||
--
|
||||
|
||||
LOCK TABLES `role` WRITE;
|
||||
/*!40000 ALTER TABLE `role` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `role` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `roles`
|
||||
--
|
||||
|
||||
LOCK TABLES `roles` WRITE;
|
||||
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `salestax`
|
||||
--
|
||||
|
||||
LOCK TABLES `salestax` WRITE;
|
||||
/*!40000 ALTER TABLE `salestax` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `salestax` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `service`
|
||||
--
|
||||
|
||||
LOCK TABLES `service` WRITE;
|
||||
/*!40000 ALTER TABLE `service` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `service` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `servicedesc`
|
||||
--
|
||||
|
||||
LOCK TABLES `servicedesc` WRITE;
|
||||
/*!40000 ALTER TABLE `servicedesc` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `servicedesc` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `servicetype`
|
||||
--
|
||||
|
||||
LOCK TABLES `servicetype` WRITE;
|
||||
/*!40000 ALTER TABLE `servicetype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `servicetype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `socialsecurity`
|
||||
--
|
||||
|
||||
LOCK TABLES `socialsecurity` WRITE;
|
||||
/*!40000 ALTER TABLE `socialsecurity` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `socialsecurity` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `tariff`
|
||||
--
|
||||
|
||||
LOCK TABLES `tariff` WRITE;
|
||||
/*!40000 ALTER TABLE `tariff` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `tariff` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `timetrack`
|
||||
--
|
||||
|
||||
LOCK TABLES `timetrack` WRITE;
|
||||
/*!40000 ALTER TABLE `timetrack` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `timetrack` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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(150) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `users`
|
||||
--
|
||||
|
||||
LOCK TABLES `users` WRITE;
|
||||
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 `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 `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(150), 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);
|
||||
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 */ ;
|
||||
/*!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-11-21 15:31:16
|
||||
@@ -1,995 +0,0 @@
|
||||
/*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,
|
||||
`address` varchar(50) NOT NULL,
|
||||
PRIMARY KEY (`addressid`),
|
||||
UNIQUE KEY `address_unique` (`address`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `address`
|
||||
--
|
||||
|
||||
LOCK TABLES `address` WRITE;
|
||||
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `assignment`
|
||||
--
|
||||
|
||||
LOCK TABLES `assignment` WRITE;
|
||||
/*!40000 ALTER TABLE `assignment` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `assignment` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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) NOT NULL DEFAULT 1,
|
||||
PRIMARY KEY (`businessid`),
|
||||
UNIQUE KEY `customer_unique` (`email`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `business`
|
||||
--
|
||||
|
||||
LOCK TABLES `business` WRITE;
|
||||
/*!40000 ALTER TABLE `business` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `business` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `businesstype`
|
||||
--
|
||||
|
||||
LOCK TABLES `businesstype` WRITE;
|
||||
/*!40000 ALTER TABLE `businesstype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `businesstype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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,
|
||||
`contactpersonid` int(11) NOT NULL,
|
||||
`businessid` tinyint(1) NOT NULL DEFAULT 1,
|
||||
UNIQUE KEY `contact_unique` (`id`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contact`
|
||||
--
|
||||
|
||||
LOCK TABLES `contact` WRITE;
|
||||
/*!40000 ALTER TABLE `contact` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contact` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contactperson`
|
||||
--
|
||||
|
||||
LOCK TABLES `contactperson` WRITE;
|
||||
/*!40000 ALTER TABLE `contactperson` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contactperson` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contract`
|
||||
--
|
||||
|
||||
LOCK TABLES `contract` WRITE;
|
||||
/*!40000 ALTER TABLE `contract` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contract` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `employee`
|
||||
--
|
||||
|
||||
LOCK TABLES `employee` WRITE;
|
||||
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `entities`
|
||||
--
|
||||
|
||||
LOCK TABLES `entities` WRITE;
|
||||
/*!40000 ALTER TABLE `entities` DISABLE KEYS */;
|
||||
INSERT INTO `entities` VALUES
|
||||
(1,'business'),
|
||||
(3,'contactperson'),
|
||||
(2,'employee'),
|
||||
(4,'object');
|
||||
/*!40000 ALTER TABLE `entities` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `identification`
|
||||
--
|
||||
|
||||
LOCK TABLES `identification` WRITE;
|
||||
/*!40000 ALTER TABLE `identification` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `identification` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `identificationtype`
|
||||
--
|
||||
|
||||
LOCK TABLES `identificationtype` WRITE;
|
||||
/*!40000 ALTER TABLE `identificationtype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `identificationtype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `invoice`
|
||||
--
|
||||
|
||||
LOCK TABLES `invoice` WRITE;
|
||||
/*!40000 ALTER TABLE `invoice` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `invoice` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `jobdetail`
|
||||
--
|
||||
|
||||
LOCK TABLES `jobdetail` WRITE;
|
||||
/*!40000 ALTER TABLE `jobdetail` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `jobdetail` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `jobstatus`
|
||||
--
|
||||
|
||||
LOCK TABLES `jobstatus` WRITE;
|
||||
/*!40000 ALTER TABLE `jobstatus` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `jobstatus` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `logs`
|
||||
--
|
||||
|
||||
LOCK TABLES `logs` WRITE;
|
||||
/*!40000 ALTER TABLE `logs` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `logs` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `medicalcare`
|
||||
--
|
||||
|
||||
LOCK TABLES `medicalcare` WRITE;
|
||||
/*!40000 ALTER TABLE `medicalcare` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `medicalcare` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `object`
|
||||
--
|
||||
|
||||
LOCK TABLES `object` WRITE;
|
||||
/*!40000 ALTER TABLE `object` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `object` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `offer`
|
||||
--
|
||||
|
||||
LOCK TABLES `offer` WRITE;
|
||||
/*!40000 ALTER TABLE `offer` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `offer` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `offers`
|
||||
--
|
||||
|
||||
LOCK TABLES `offers` WRITE;
|
||||
/*!40000 ALTER TABLE `offers` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `offers` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `office`
|
||||
--
|
||||
|
||||
LOCK TABLES `office` WRITE;
|
||||
/*!40000 ALTER TABLE `office` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `office` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `permissions`
|
||||
--
|
||||
|
||||
LOCK TABLES `permissions` WRITE;
|
||||
/*!40000 ALTER TABLE `permissions` DISABLE KEYS */;
|
||||
INSERT INTO `permissions` VALUES
|
||||
(4,'delete'),
|
||||
(1,'read'),
|
||||
(3,'update'),
|
||||
(2,'write');
|
||||
/*!40000 ALTER TABLE `permissions` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `personalrole`
|
||||
--
|
||||
|
||||
LOCK TABLES `personalrole` WRITE;
|
||||
/*!40000 ALTER TABLE `personalrole` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `personalrole` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `postcode`
|
||||
--
|
||||
|
||||
LOCK TABLES `postcode` WRITE;
|
||||
/*!40000 ALTER TABLE `postcode` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `postcode` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `role`
|
||||
--
|
||||
|
||||
LOCK TABLES `role` WRITE;
|
||||
/*!40000 ALTER TABLE `role` DISABLE KEYS */;
|
||||
INSERT INTO `role` VALUES
|
||||
(1,'adminrole'),
|
||||
(2,'userrole');
|
||||
/*!40000 ALTER TABLE `role` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `roles`
|
||||
--
|
||||
|
||||
LOCK TABLES `roles` WRITE;
|
||||
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
|
||||
INSERT INTO `roles` VALUES
|
||||
(1,1,1),
|
||||
(1,2,1),
|
||||
(1,3,1),
|
||||
(1,4,1),
|
||||
(1,1,2),
|
||||
(1,2,2),
|
||||
(1,3,2),
|
||||
(1,4,2),
|
||||
(1,1,3),
|
||||
(1,2,3),
|
||||
(1,3,3),
|
||||
(1,4,3),
|
||||
(1,1,4),
|
||||
(1,2,4),
|
||||
(1,3,4),
|
||||
(1,4,4),
|
||||
(2,1,1),
|
||||
(2,2,1),
|
||||
(2,3,1),
|
||||
(2,1,2),
|
||||
(2,2,2),
|
||||
(2,3,2),
|
||||
(2,1,3),
|
||||
(2,2,3),
|
||||
(2,3,3),
|
||||
(2,1,4),
|
||||
(2,2,4),
|
||||
(2,3,4);
|
||||
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `salestax`
|
||||
--
|
||||
|
||||
LOCK TABLES `salestax` WRITE;
|
||||
/*!40000 ALTER TABLE `salestax` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `salestax` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `service`
|
||||
--
|
||||
|
||||
LOCK TABLES `service` WRITE;
|
||||
/*!40000 ALTER TABLE `service` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `service` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `servicedesc`
|
||||
--
|
||||
|
||||
LOCK TABLES `servicedesc` WRITE;
|
||||
/*!40000 ALTER TABLE `servicedesc` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `servicedesc` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `servicetype`
|
||||
--
|
||||
|
||||
LOCK TABLES `servicetype` WRITE;
|
||||
/*!40000 ALTER TABLE `servicetype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `servicetype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `socialsecurity`
|
||||
--
|
||||
|
||||
LOCK TABLES `socialsecurity` WRITE;
|
||||
/*!40000 ALTER TABLE `socialsecurity` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `socialsecurity` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `tariff`
|
||||
--
|
||||
|
||||
LOCK TABLES `tariff` WRITE;
|
||||
/*!40000 ALTER TABLE `tariff` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `tariff` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `timetrack`
|
||||
--
|
||||
|
||||
LOCK TABLES `timetrack` WRITE;
|
||||
/*!40000 ALTER TABLE `timetrack` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `timetrack` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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(150) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `users`
|
||||
--
|
||||
|
||||
LOCK TABLES `users` WRITE;
|
||||
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 `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 `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(150), 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);
|
||||
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 */ ;
|
||||
/*!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-11-26 13:49:32
|
||||
@@ -1,833 +0,0 @@
|
||||
/*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 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 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 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 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 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 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 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 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
|
||||
@@ -1,832 +0,0 @@
|
||||
/*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(50) NOT NULL,
|
||||
PRIMARY KEY (`addressid`)
|
||||
) 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'));
|
||||
|
||||
|
||||
|
||||
|
||||
|
||||
|
||||
|
||||
|
||||
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 16:40:40
|
||||
File diff suppressed because it is too large
Load Diff
File diff suppressed because it is too large
Load Diff
File diff suppressed because it is too large
Load Diff
File diff suppressed because it is too large
Load Diff
@@ -1,761 +0,0 @@
|
||||
/*M!999999\- enable the sandbox mode */
|
||||
-- MariaDB dump 10.19 Distrib 10.11.9-MariaDB, for Linux (x86_64)
|
||||
--
|
||||
-- Host: localhost Database: pyqcrm
|
||||
-- ------------------------------------------------------
|
||||
-- Server version 10.11.9-MariaDB
|
||||
|
||||
/*!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,
|
||||
`address` varchar(50) NOT NULL,
|
||||
PRIMARY KEY (`addressid`),
|
||||
UNIQUE KEY `address_unique` (`address`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `address`
|
||||
--
|
||||
|
||||
LOCK TABLES `address` WRITE;
|
||||
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `assignment`
|
||||
--
|
||||
|
||||
LOCK TABLES `assignment` WRITE;
|
||||
/*!40000 ALTER TABLE `assignment` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `assignment` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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,
|
||||
`contactpersonid` int(11) NOT NULL,
|
||||
`customer` tinyint(1) NOT NULL DEFAULT 1,
|
||||
UNIQUE KEY `contact_unique` (`id`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contact`
|
||||
--
|
||||
|
||||
LOCK TABLES `contact` WRITE;
|
||||
/*!40000 ALTER TABLE `contact` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contact` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contactperson`
|
||||
--
|
||||
|
||||
LOCK TABLES `contactperson` WRITE;
|
||||
/*!40000 ALTER TABLE `contactperson` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contactperson` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `contract`
|
||||
--
|
||||
|
||||
LOCK TABLES `contract` WRITE;
|
||||
/*!40000 ALTER TABLE `contract` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `contract` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- Table structure for table `customer`
|
||||
--
|
||||
|
||||
DROP TABLE IF EXISTS `customer`;
|
||||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||||
/*!40101 SET character_set_client = utf8 */;
|
||||
CREATE TABLE `customer` (
|
||||
`customerid` 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) NOT NULL,
|
||||
`homepage` varchar(255) DEFAULT NULL,
|
||||
`director` varchar(100) DEFAULT NULL,
|
||||
`contactpersonid` int(11) NOT NULL,
|
||||
PRIMARY KEY (`customerid`),
|
||||
UNIQUE KEY `customer_unique` (`email`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `customer`
|
||||
--
|
||||
|
||||
LOCK TABLES `customer` WRITE;
|
||||
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `employee`
|
||||
--
|
||||
|
||||
LOCK TABLES `employee` WRITE;
|
||||
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `identification`
|
||||
--
|
||||
|
||||
LOCK TABLES `identification` WRITE;
|
||||
/*!40000 ALTER TABLE `identification` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `identification` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `identificationtype`
|
||||
--
|
||||
|
||||
LOCK TABLES `identificationtype` WRITE;
|
||||
/*!40000 ALTER TABLE `identificationtype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `identificationtype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `invoice`
|
||||
--
|
||||
|
||||
LOCK TABLES `invoice` WRITE;
|
||||
/*!40000 ALTER TABLE `invoice` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `invoice` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `jobdetail`
|
||||
--
|
||||
|
||||
LOCK TABLES `jobdetail` WRITE;
|
||||
/*!40000 ALTER TABLE `jobdetail` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `jobdetail` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `jobstatus`
|
||||
--
|
||||
|
||||
LOCK TABLES `jobstatus` WRITE;
|
||||
/*!40000 ALTER TABLE `jobstatus` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `jobstatus` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `medicalcare`
|
||||
--
|
||||
|
||||
LOCK TABLES `medicalcare` WRITE;
|
||||
/*!40000 ALTER TABLE `medicalcare` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `medicalcare` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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,
|
||||
`customerid` int(11) NOT NULL,
|
||||
PRIMARY KEY (`objectid`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `object`
|
||||
--
|
||||
|
||||
LOCK TABLES `object` WRITE;
|
||||
/*!40000 ALTER TABLE `object` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `object` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `offer`
|
||||
--
|
||||
|
||||
LOCK TABLES `offer` WRITE;
|
||||
/*!40000 ALTER TABLE `offer` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `offer` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `offers`
|
||||
--
|
||||
|
||||
LOCK TABLES `offers` WRITE;
|
||||
/*!40000 ALTER TABLE `offers` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `offers` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `office`
|
||||
--
|
||||
|
||||
LOCK TABLES `office` WRITE;
|
||||
/*!40000 ALTER TABLE `office` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `office` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `permissions`
|
||||
--
|
||||
|
||||
LOCK TABLES `permissions` WRITE;
|
||||
/*!40000 ALTER TABLE `permissions` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `permissions` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `personalrole`
|
||||
--
|
||||
|
||||
LOCK TABLES `personalrole` WRITE;
|
||||
/*!40000 ALTER TABLE `personalrole` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `personalrole` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `postcode`
|
||||
--
|
||||
|
||||
LOCK TABLES `postcode` WRITE;
|
||||
/*!40000 ALTER TABLE `postcode` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `postcode` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `role`
|
||||
--
|
||||
|
||||
LOCK TABLES `role` WRITE;
|
||||
/*!40000 ALTER TABLE `role` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `role` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `roles`
|
||||
--
|
||||
|
||||
LOCK TABLES `roles` WRITE;
|
||||
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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,
|
||||
`customerid` int(11) NOT NULL,
|
||||
PRIMARY KEY (`salestaxid`),
|
||||
UNIQUE KEY `salestax_unique` (`customerid`)
|
||||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `salestax`
|
||||
--
|
||||
|
||||
LOCK TABLES `salestax` WRITE;
|
||||
/*!40000 ALTER TABLE `salestax` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `salestax` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `service`
|
||||
--
|
||||
|
||||
LOCK TABLES `service` WRITE;
|
||||
/*!40000 ALTER TABLE `service` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `service` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `servicedesc`
|
||||
--
|
||||
|
||||
LOCK TABLES `servicedesc` WRITE;
|
||||
/*!40000 ALTER TABLE `servicedesc` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `servicedesc` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `servicetype`
|
||||
--
|
||||
|
||||
LOCK TABLES `servicetype` WRITE;
|
||||
/*!40000 ALTER TABLE `servicetype` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `servicetype` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `socialsecurity`
|
||||
--
|
||||
|
||||
LOCK TABLES `socialsecurity` WRITE;
|
||||
/*!40000 ALTER TABLE `socialsecurity` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `socialsecurity` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `tariff`
|
||||
--
|
||||
|
||||
LOCK TABLES `tariff` WRITE;
|
||||
/*!40000 ALTER TABLE `tariff` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `tariff` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- 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 */;
|
||||
|
||||
--
|
||||
-- Dumping data for table `timetrack`
|
||||
--
|
||||
|
||||
LOCK TABLES `timetrack` WRITE;
|
||||
/*!40000 ALTER TABLE `timetrack` DISABLE KEYS */;
|
||||
/*!40000 ALTER TABLE `timetrack` ENABLE KEYS */;
|
||||
UNLOCK TABLES;
|
||||
|
||||
--
|
||||
-- Dumping routines for database 'pyqcrm'
|
||||
--
|
||||
/*!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-10-29 13:20:21
|
||||
Reference in New Issue
Block a user