286 lines
9.5 KiB
Plaintext
286 lines
9.5 KiB
Plaintext
|
|
|
|
ALTER TABLE `supportconcept`
|
|
CHANGE COLUMN `Notice` `Notice` MEDIUMTEXT NULL;
|
|
|
|
|
|
ALTER TABLE `overtime`
|
|
CHANGE COLUMN `Betrag` `Betrag` DECIMAL(18,10) NULL DEFAULT NULL ;
|
|
|
|
|
|
ALTER TABLE `customer2person`
|
|
ADD COLUMN `IstFamilie` TINYINT(4) NULL DEFAULT NULL COMMENT '';
|
|
|
|
|
|
ALTER TABLE `image`
|
|
ADD COLUMN `PersonOid` bigint(19) DEFAULT NULL;
|
|
|
|
update image set personoid = (select personoid from customer c where c.oid = customeroid) where customeroid is not null and personoid is null;
|
|
|
|
update image set personoid = (select personoid from employee e where e.oid = employeeoid) where employeeoid is not null and personoid is null;
|
|
|
|
|
|
|
|
CREATE TABLE `chatmessages` (
|
|
`Oid` bigint(19) NOT NULL AUTO_INCREMENT,
|
|
`SenderPersonOid` bigint(19) DEFAULT NULL,
|
|
`EmpfaengerPersonOid` bigint(19) DEFAULT NULL,
|
|
`Tid` int(10) DEFAULT NULL,
|
|
`Notice` varchar(1024) DEFAULT NULL,
|
|
`InsTs` datetime DEFAULT NULL,
|
|
`InsUser` varchar(1024) DEFAULT NULL,
|
|
`Version` bigint(19) DEFAULT NULL,
|
|
`UdpUser` varchar(256) DEFAULT NULL,
|
|
`isActive` tinyint(4) DEFAULT NULL,
|
|
`SystemEntryID` int(10) DEFAULT NULL,
|
|
`ChatText` longblob,
|
|
`Uhrzeit` datetime DEFAULT NULL,
|
|
`IstZugestellt` tinyint(4) DEFAULT NULL,
|
|
`IstGelesen` tinyint(4) DEFAULT NULL,
|
|
`TeamOid` bigint(19) DEFAULT NULL,
|
|
`MessageId` VARCHAR(1024) DEFAULT NULL,
|
|
`MediaTyp` INT(10) NULL DEFAULT NULL COMMENT '',
|
|
PRIMARY KEY (`Oid`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=327 DEFAULT CHARSET=latin1;
|
|
|
|
|
|
CREATE TABLE `chatmediamessage` (
|
|
`Oid` BIGINT(19) AUTO_INCREMENT COMMENT '',
|
|
`ChatMessageOid` BIGINT(19) NULL COMMENT '',
|
|
`Tid` INT(10) NULL COMMENT '',
|
|
`Notice` VARCHAR(1024) NULL COMMENT '',
|
|
`InsTs` DATETIME NULL COMMENT '',
|
|
`InsUser` VARCHAR(1024) NULL COMMENT '',
|
|
`Version` BIGINT(19) NULL COMMENT '',
|
|
`UdpUser` VARCHAR(256) NULL COMMENT '',
|
|
`isActive` TINYINT(4) NULL COMMENT '',
|
|
`SystemEntryID` INT(10) NULL COMMENT '',
|
|
`Media` LONGBLOB NULL DEFAULT NULL COMMENT '',
|
|
`ImageThumpNail` LONGBLOB NULL DEFAULT NULL COMMENT '',
|
|
|
|
PRIMARY KEY (`Oid`) COMMENT '');
|
|
|
|
ALTER TABLE `chatmediamessage`
|
|
ADD INDEX `ChatMessage_IDX` (`ChatMessageOid` ASC) COMMENT '';
|
|
|
|
|
|
CREATE TABLE `employeeappcode` (
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT,
|
|
`Employeeoid` BIGINT(19) NULL,
|
|
`Tid` INT(10) NULL,
|
|
`Notice` VARCHAR(1024) NULL,
|
|
`InsTs` DATETIME NULL,
|
|
`InsUser` VARCHAR(1024) NULL,
|
|
`Version` BIGINT(19) NULL,
|
|
`UdpUser` VARCHAR(256) NULL,
|
|
`IsActive` TINYINT(4) NULL,
|
|
`SystemEntryID` INT(10) NULL,
|
|
`EmployeeCode` VARCHAR(512) NULL,
|
|
`EmployeeCodegenDate` DATETIME NULL,
|
|
`IsChatActive` INT DEFAULT 0,
|
|
`NotfallStatement` VARCHAR(1024) NULL,
|
|
PRIMARY KEY (`Oid`),
|
|
UNIQUE INDEX `EmployeeCode_UNIQUE` (`EmployeeCode` ASC)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
|
|
CREATE TABLE `customerappcode` (
|
|
`Oid` bigint(19) NOT NULL AUTO_INCREMENT,
|
|
`CustomerOid` bigint(19) DEFAULT NULL,
|
|
`Tid` int(10) DEFAULT NULL,
|
|
`Notice` varchar(1024) DEFAULT NULL,
|
|
`InsTs` datetime DEFAULT NULL,
|
|
`InsUser` varchar(1024) DEFAULT NULL,
|
|
`Version` bigint(19) DEFAULT NULL,
|
|
`UdpUser` varchar(256) DEFAULT NULL,
|
|
`IsActive` tinyint(4) DEFAULT NULL,
|
|
`SystemEntryID` int(10) DEFAULT NULL,
|
|
`CustomerCodeGenDate` datetime DEFAULT NULL,
|
|
`CustomerCode` varchar(512) DEFAULT NULL,
|
|
`IsChatActive` INT DEFAULT 0,
|
|
PRIMARY KEY (`Oid`),
|
|
UNIQUE KEY `CustomerCode_UNIQUE` (`CustomerCode`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
ALTER TABLE `employee2customer`
|
|
ADD COLUMN `Chatpartner` TINYINT(4) NULL DEFAULT 0 COMMENT '' AFTER `SystemEntryID`;
|
|
|
|
|
|
ALTER TABLE `servicerecord`
|
|
ADD COLUMN `DurationInStunden`
|
|
INT(10) NULL DEFAULT NULL COMMENT '' AFTER `Notice5`;
|
|
|
|
ALTER TABLE `servicerecord`
|
|
ADD COLUMN `ServiceRecordFormat`
|
|
INT(10) NULL DEFAULT NULL COMMENT '' AFTER `DurationInStunden`;
|
|
|
|
CREATE TABLE `passwortverlauf` (
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT COMMENT '',
|
|
`ApplicationUserOid` BIGINT(19) NULL COMMENT '',
|
|
`Tid` INT(10) NULL COMMENT '',
|
|
`Notice` VARCHAR(1024) NULL COMMENT '',
|
|
`InsTs` DATETIME NULL COMMENT '',
|
|
`InsUser` VARCHAR(1024) NULL COMMENT '',
|
|
`Version` BIGINT(19) NULL COMMENT '',
|
|
`UdpUser` VARCHAR(256) NULL COMMENT '',
|
|
`isActive` TINYINT(4) NULL COMMENT '',
|
|
`SystemEntryID` INT(10) NULL COMMENT '',
|
|
`ErstellDatum` DATETIME NULL COMMENT '',
|
|
`Passwort` VARCHAR(64) NULL COMMENT '',
|
|
`Salt` VARCHAR(128) NULL COMMENT '',
|
|
PRIMARY KEY (`Oid`) COMMENT '')
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARACTER SET = latin1;
|
|
|
|
|
|
CREATE TABLE `newestchatmessage` (
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT,
|
|
`Tid` INT(10) DEFAULT NULL,
|
|
`IsActive` TINYINT(1) DEFAULT NULL,
|
|
`Notice` VARCHAR(1024) DEFAULT NULL,
|
|
`InsTs` DATETIME DEFAULT NULL,
|
|
`InsUser` VARCHAR(256) DEFAULT NULL,
|
|
`Version` BIGINT(19) DEFAULT NULL,
|
|
`UdpUser` VARCHAR(256) DEFAULT NULL,
|
|
`SystemEntryID` INT(10) DEFAULT NULL,
|
|
`RecipientPersonOid` BIGINT(19) DEFAULT NULL,
|
|
`SenderPersonOid` BIGINT(19) DEFAULT NULL,
|
|
`ChatMessageOid` BIGINT(19) DEFAULT NULL,
|
|
`IsTeam` TINYINT(1) DEFAULT NULL,
|
|
PRIMARY KEY (`Oid`),
|
|
KEY `NEWESTCHATMESSAGE_RECIPIENT_FK` (`RecipientPersonOid`),
|
|
CONSTRAINT `FK_NEWESTCHATMESSAGE_RECIPIENT` FOREIGN KEY (`RecipientPersonOid`) REFERENCES `Person` (`Oid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
|
|
KEY `NEWESTCHATMESSAGE_SENDER_FK` (`SenderPersonOid`),
|
|
CONSTRAINT `FK_NEWESTCHATMESSAGE_SENDER` FOREIGN KEY (`SenderPersonOid`) REFERENCES `Person` (`Oid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
|
|
KEY `NEWESTCHATMESSAGE_CHATMESSAGE_FK` (`ChatMessageOid`),
|
|
CONSTRAINT `FK_NEWESTCHATMESSAGE_CHATMESSAGE` FOREIGN KEY (`ChatMessageOid`) REFERENCES `ChatMessages` (`Oid`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=InnoDB
|
|
AUTO_INCREMENT=1 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
|
|
|
|
|
|
ALTER TABLE `customer2person`
|
|
ADD COLUMN `OrganisationOid` BIGINT(19) NULL DEFAULT NULL COMMENT '' AFTER `SystemEntryID`;
|
|
|
|
|
|
ALTER TABLE `customer2organisation`
|
|
ADD COLUMN `PersonOid` BIGINT(19) NULL DEFAULT NULL COMMENT '' AFTER `SystemEntryID`;
|
|
|
|
|
|
ALTER TABLE `organisation2person`
|
|
ADD COLUMN `RolleInOrganisationValueListOid` BIGINT(19) NULL DEFAULT NULL COMMENT '' AFTER `SystemEntryID`;
|
|
|
|
|
|
|
|
|
|
ALTER TABLE `supportconceptapprovalperiod2employee`
|
|
ADD COLUMN `IsAbsolut` INT(10) NULL DEFAULT 0 COMMENT '' AFTER `Betreuungsschluessel`;
|
|
|
|
ALTER TABLE `organisation`
|
|
ADD COLUMN `FunctionValueListEntryOid` BIGINT(19) NULL DEFAULT NULL COMMENT '' AFTER `AccountNumber`;
|
|
|
|
|
|
|
|
|
|
|
|
ALTER TABLE `contract`
|
|
ADD COLUMN `MonthlyTotalHours` DECIMAL(18,10) NULL DEFAULT NULL COMMENT '' AFTER `WeeklyDays`;
|
|
|
|
|
|
|
|
ALTER TABLE `person`
|
|
ADD COLUMN `IsMigrant` TINYINT(4) NULL DEFAULT NULL COMMENT '' AFTER `FunctionValueListEntryOid`;
|
|
|
|
|
|
|
|
ALTER TABLE `person`
|
|
ADD COLUMN `NationalitaetValueListEntryOid`
|
|
BIGINT(19) NULL DEFAULT NULL COMMENT '' AFTER `IsMigrant`;
|
|
|
|
|
|
ALTER TABLE `person`
|
|
ADD COLUMN `AufenthaltsStatusValueListEntryOid`
|
|
BIGINT(19) NULL DEFAULT NULL COMMENT '' AFTER `NationalitaetValueListEntryOid`;
|
|
|
|
|
|
ALTER TABLE `contract`
|
|
ADD COLUMN
|
|
`MonthlyFLS` DECIMAL(18,10) NULL DEFAULT NULL COMMENT '' AFTER `MonthlyTotalHours`;
|
|
|
|
|
|
CREATE TABLE `vertretung`
|
|
(
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT COMMENT '',
|
|
`MitarbeiterOid` BIGINT(19) NULL COMMENT '',
|
|
`KlientOid` BIGINT(19) NULL COMMENT '',
|
|
`Tid` INT(10) NULL COMMENT '',
|
|
`Notice` VARCHAR(1024) NULL COMMENT '',
|
|
`InsTs` DATETIME NULL COMMENT '',
|
|
`InsUser` VARCHAR(1024) NULL COMMENT '',
|
|
`Version` BIGINT(19) NULL COMMENT '',
|
|
`UdpUser` VARCHAR(256) NULL COMMENT '',
|
|
`IsActive` TINYINT(4) NULL COMMENT '',
|
|
`SystemEntryID` INT(10) NULL COMMENT '',
|
|
`VertretungsZeitraumVon` DATETIME NULL COMMENT '',
|
|
`VertretungsZeitraumBis` DATETIME NULL COMMENT '',
|
|
`VertretenderMitarbeiterOid` BIGINT(19) NULL DEFAULT 0 COMMENT '',
|
|
`AbsenceTimeOid` BIGINT(19) NULL DEFAULT NULL COMMENT '',
|
|
PRIMARY KEY (`Oid`) COMMENT '');
|
|
|
|
ALTER TABLE `absencetime`
|
|
ADD COLUMN `KrankheitsMeldung` DATETIME NULL DEFAULT NULL COMMENT '' AFTER `EmployeeOid`;
|
|
|
|
|
|
|
|
CREATE TABLE `kriterien` (
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT COMMENT '',
|
|
`KlientenOid` BIGINT(19) NULL DEFAULT NULL COMMENT '',
|
|
`Tid` INT(10) NULL DEFAULT NULL COMMENT '',
|
|
`Notice` VARCHAR(1024) NULL DEFAULT NULL COMMENT '',
|
|
`InsTs` DATETIME NULL COMMENT '',
|
|
`InsUser` VARCHAR(1024) NULL COMMENT '',
|
|
`Version` BIGINT(19) NULL COMMENT '',
|
|
`UdpUser` VARCHAR(256) NULL COMMENT '',
|
|
`IsActive` TINYINT(4) NULL COMMENT '',
|
|
`SystemEntryID` INT(10) NULL COMMENT '',
|
|
`AusschlussKriterien` VARCHAR(1024) NULL COMMENT '',
|
|
`WunschKriterien` VARCHAR(1024) NULL COMMENT '',
|
|
PRIMARY KEY (`Oid`) COMMENT '');
|
|
|
|
|
|
CREATE TABLE `arbeitszeit` (
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT COMMENT '',
|
|
`EmployeeOid` BIGINT(19) NULL COMMENT '',
|
|
`Tid` INT(10) NULL COMMENT '',
|
|
`Notice` VARCHAR(1024) NULL COMMENT '',
|
|
`InsTs` DATETIME NULL COMMENT '',
|
|
`InsUser` VARCHAR(1024) NULL COMMENT '',
|
|
`Version` BIGINT(19) NULL COMMENT '',
|
|
`UdpUser` VARCHAR(256) NULL COMMENT '',
|
|
`isActive` TINYINT(4) NULL COMMENT '',
|
|
`SystemEntryID` INT(10) NULL COMMENT '',
|
|
`GueltigVon` DATETIME NULL COMMENT '',
|
|
`GueltigBis` DATETIME NULL COMMENT '',
|
|
PRIMARY KEY (`Oid`) COMMENT '');
|
|
|
|
|
|
|
|
|
|
CREATE TABLE `arbeitszeiteintrag` (
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT COMMENT '',
|
|
`ArbeitszeitOid` BIGINT(19) NULL COMMENT '',
|
|
`Tid` INT(10) NULL COMMENT '',
|
|
`Notice` VARCHAR(1024) NULL COMMENT '',
|
|
`InsTs` DATETIME NULL COMMENT '',
|
|
`InsUser` VARCHAR(1024) NULL COMMENT '',
|
|
`Version` BIGINT(19) NULL COMMENT '',
|
|
`UdpUser` VARCHAR(256) NULL COMMENT '',
|
|
`isActive` TINYINT(4) NULL COMMENT '',
|
|
`SystemEntryID` INT(10) NULL COMMENT '',
|
|
`Tag` INT(10) NULL COMMENT '',
|
|
`UhrzeitVon` VARCHAR(256) NULL COMMENT '',
|
|
`UhrzeitBis` VARCHAR(256) NULL COMMENT '',
|
|
PRIMARY KEY (`Oid`) COMMENT '');
|
|
|