212 lines
8.9 KiB
Transact-SQL
212 lines
8.9 KiB
Transact-SQL
|
|
UPDATE TBIDB_BASE SET CONFIG_VALUE = '1.0.9' where CONFIG_NAME = 'DB_VERSION'
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[TBIDB_OBJECT_METADATA_ADD](
|
|
[GUID] BIGINT IDENTITY(1,1) NOT NULL,
|
|
[IDB_OBJ_ID] BIGINT NOT NULL,
|
|
[ATTR_ID] INT NOT NULL,
|
|
[TEXT_TERM_LANG_ID] BIGINT,
|
|
[INT_TERM_ID] BIGINT,
|
|
[DEC_TERM_ID] BIGINT,
|
|
[FLOAT_TERM_ID] BIGINT,
|
|
[DATE_TERM_ID] BIGINT,
|
|
[ADDED_WHO] VARCHAR(100),
|
|
[ADDED_WHEN] DATETIME NOT NULL,
|
|
CONSTRAINT PK_TBDD_GROUPS_USER PRIMARY KEY (GUID),
|
|
CONSTRAINT FK_TBIDB_OBJECTMETADATAADD_ATTRID FOREIGN KEY(ATTR_ID) REFERENCES TBIDB_ATTRIBUTE(GUID),
|
|
CONSTRAINT FK_TBIDB_OBJECTMETADATAADD_IDBOBJID FOREIGN KEY(IDB_OBJ_ID) REFERENCES TBIDB_OBJECT(IDB_OBJ_ID),
|
|
CONSTRAINT FK_TBIDB_OBJECTMETADATAADD_DATETERMID FOREIGN KEY(DATE_TERM_ID) REFERENCES TBIDB_TERM_VALUE_DATE(GUID),
|
|
CONSTRAINT FK_TBIDB_OBJECTMETADATAADD_DECTERMID FOREIGN KEY(DEC_TERM_ID) REFERENCES TBIDB_TERM_VALUE_DECIMAL(GUID),
|
|
CONSTRAINT FK_TBIDB_OBJECTMETADATAADD_FLOATTERMID FOREIGN KEY(FLOAT_TERM_ID) REFERENCES TBIDB_TERM_VALUE_FLOAT(GUID),
|
|
CONSTRAINT FK_TBIDB_OBJECTMETADATAADD_INTTERMID FOREIGN KEY(INT_TERM_ID) REFERENCES TBIDB_TERM_VALUE_INTEGER(GUID),
|
|
CONSTRAINT FK_TBIDB_OBJECTMETADATAADD_TEXTTERMLANGID FOREIGN KEY(TEXT_TERM_LANG_ID) REFERENCES TBIDB_TERM_LANGUAGE(GUID),
|
|
)
|
|
|
|
GO
|
|
ALTER TABLE TBIDB_OBJECT_METADATA_DELETE
|
|
ADD ADDED_WHEN DATETIME;
|
|
|
|
USE [IDB]
|
|
GO
|
|
--#######################
|
|
--Change 06.10.2021 ADDED_WHEN METADATA_DELETE
|
|
--#######################
|
|
|
|
ALTER PROCEDURE [dbo].[PRIDB_DELETE_TERM_OBJECT_METADATA] @IDB_OBJ_ID BIGINT, @ATTRIBUTE VARCHAR(100), @TERM VARCHAR(900), @WHO VARCHAR(100),@LANG_CODE VARCHAR(5) = 'de-DE',@ID_ISFOREIGN as BIT = False
|
|
AS
|
|
BEGIN
|
|
DECLARE
|
|
@ATTR_ID INTEGER,
|
|
@TYP_ID TINYINT,
|
|
@TYP_DESCR VARCHAR(100),
|
|
@MULTI BIT,
|
|
@LANG_ID INT
|
|
--IF @ID_ISFOREIGN = 1
|
|
-- BEGIN
|
|
-- SELECT @IDB_OBJ_ID = IDB_OBJ_ID FROM TBIDB_OBJECT WHERE REFERENCE_KEY = @IDB_OBJ_ID
|
|
-- END
|
|
|
|
SELECT @ATTR_ID = T.GUID, @TYP_ID = T.TYP_ID, @MULTI = T.MULTI_CONTEXT,@TYP_DESCR = T1.NAME_TYPE FROM TBIDB_ATTRIBUTE T, TBIDB_ATTRIBUTE_TYPE T1
|
|
WHERE T.TYP_ID = T1.GUID AND UPPER(T.TITLE) = UPPER(@ATTRIBUTE)
|
|
|
|
SELECT @LANG_ID = GUID FROM TBIDB_LANGUAGE WHERE LANG_CODE = @LANG_CODE
|
|
|
|
DECLARE @MY_TERM_ID BIGINT
|
|
EXEC PRIDB_NEW_TERM @TYP_DESCR,@TERM,'',@TERM_ID = @MY_TERM_ID OUTPUT;
|
|
|
|
PRINT '@MY_TERM_ID: ' + CONVERT(VARCHAR(10),@MY_TERM_ID)
|
|
|
|
IF @TYP_ID IN (1,8) --VARCHAR
|
|
BEGIN
|
|
PRINT 'Attribute is varchar...'
|
|
SELECT @MY_TERM_ID = MAX(GUID) FROM TBIDB_TERM_LANGUAGE WHERE LANGUAGE_ID = @LANG_ID AND TEXT_TERM_ID = @MY_TERM_ID
|
|
IF EXISTS(SELECT GUID FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND TEXT_TERM_LANG_ID = @MY_TERM_ID)
|
|
BEGIN
|
|
DELETE FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND TEXT_TERM_LANG_ID = @MY_TERM_ID
|
|
PRINT 'PRIDB_DEL_TERM_OBJECT_METADATA: DELETED TEXT ROW!'
|
|
INSERT INTO TBIDB_OBJECT_METADATA_DELETE (IDB_OBJ_ID,ATTR_ID,TEXT_TERM_LANG_ID,DELETED_WHO, ADDED_WHO,ADDED_WHEN)
|
|
SELECT @IDB_OBJ_ID,@ATTR_ID, @MY_TERM_ID,@WHO,ADDED_WHO,ADDED_WHEN FROM TBIDB_OBJECT_METADATA
|
|
WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND TEXT_TERM_LANG_ID = @MY_TERM_ID
|
|
END
|
|
ELSE
|
|
PRINT 'TERM SEEMS NOT TO EXIST'
|
|
END
|
|
ELSE IF @TYP_ID IN (2,7,9) --BIGINT
|
|
BEGIN
|
|
IF EXISTS(SELECT GUID FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND INT_TERM_ID = @MY_TERM_ID)
|
|
BEGIN
|
|
DELETE FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND INT_TERM_ID = @MY_TERM_ID
|
|
PRINT 'PRIDB_DEL_TERM_OBJECT_METADATA: DELETED INTEGER ROW!'
|
|
INSERT INTO TBIDB_OBJECT_METADATA_DELETE (IDB_OBJ_ID,ATTR_ID,INT_TERM_ID,DELETED_WHO, ADDED_WHO,ADDED_WHEN)
|
|
SELECT @IDB_OBJ_ID,@ATTR_ID, @MY_TERM_ID,@WHO,ADDED_WHO,ADDED_WHEN FROM TBIDB_OBJECT_METADATA
|
|
WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND INT_TERM_ID = @MY_TERM_ID
|
|
END
|
|
END
|
|
ELSE IF @TYP_ID = 3 --FLOAT
|
|
BEGIN
|
|
IF EXISTS(SELECT GUID FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND FLOAT_TERM_ID = @MY_TERM_ID)
|
|
BEGIN
|
|
DELETE FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND FLOAT_TERM_ID = @MY_TERM_ID
|
|
PRINT 'PRIDB_DEL_TERM_OBJECT_METADATA: DELETED FLOAT ROW!'
|
|
INSERT INTO TBIDB_OBJECT_METADATA_DELETE (IDB_OBJ_ID,ATTR_ID,FLOAT_TERM_ID,DELETED_WHO, ADDED_WHO,ADDED_WHEN)
|
|
SELECT @IDB_OBJ_ID,@ATTR_ID, @MY_TERM_ID,@WHO,ADDED_WHO,ADDED_WHEN FROM TBIDB_OBJECT_METADATA
|
|
WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND FLOAT_TERM_ID = @MY_TERM_ID
|
|
END
|
|
END
|
|
ELSE IF @TYP_ID = 4 --DECIMAL
|
|
BEGIN
|
|
IF EXISTS(SELECT GUID FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND DEC_TERM_ID = @MY_TERM_ID)
|
|
BEGIN
|
|
DELETE FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND DEC_TERM_ID = @MY_TERM_ID
|
|
PRINT 'PRIDB_DEL_TERM_OBJECT_METADATA: DELETED DECIMAL ROW!'
|
|
INSERT INTO TBIDB_OBJECT_METADATA_DELETE (IDB_OBJ_ID,ATTR_ID,DEC_TERM_ID,DELETED_WHO, ADDED_WHO,ADDED_WHEN)
|
|
SELECT @IDB_OBJ_ID,@ATTR_ID, @MY_TERM_ID,@WHO,ADDED_WHO,ADDED_WHEN FROM TBIDB_OBJECT_METADATA
|
|
WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND DEC_TERM_ID = @MY_TERM_ID
|
|
END
|
|
END
|
|
ELSE IF @TYP_ID = 5 --DATE
|
|
BEGIN
|
|
IF EXISTS(SELECT GUID FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND DATE_TERM_ID = @MY_TERM_ID)
|
|
BEGIN
|
|
DELETE FROM TBIDB_OBJECT_METADATA WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND DATE_TERM_ID = @MY_TERM_ID
|
|
PRINT 'PRIDB_DEL_TERM_OBJECT_METADATA: DELETED DATE ROW!'
|
|
INSERT INTO TBIDB_OBJECT_METADATA_DELETE (IDB_OBJ_ID,ATTR_ID,DATE_TERM_ID,DELETED_WHO, ADDED_WHO,ADDED_WHEN)
|
|
SELECT @IDB_OBJ_ID,@ATTR_ID, @MY_TERM_ID,@WHO,ADDED_WHO,ADDED_WHEN FROM TBIDB_OBJECT_METADATA
|
|
WHERE IDB_OBJ_ID = @IDB_OBJ_ID AND ATTR_ID = @ATTR_ID AND DATE_TERM_ID = @MY_TERM_ID
|
|
END
|
|
END
|
|
|
|
|
|
END
|
|
GO
|
|
-- =============================================
|
|
-- Author: Digital Data, MS
|
|
-- Create date: 12.10.2021
|
|
-- Description: Löscht User-Zuordnungen wo der USer nicht mehr Teil der Posteingangsgruppe ist
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[PRIDB_GET_ATTRIBUTE_VALUE] @IDB_OBJ_ID BIGINT,@ATTRIBUTE VARCHAR(100), @LANG_CODE VARCHAR(5), @VALUE VARCHAR(MAX) OUTPUT
|
|
AS
|
|
BEGIN
|
|
DECLARE
|
|
@ATTR_ID INTEGER,
|
|
@TYP_ID TINYINT,
|
|
@TYP_DESCR VARCHAR(100),
|
|
@MULTI BIT,
|
|
@LANG_ID INT
|
|
|
|
IF NOT EXISTS(SELECT GUID FROM TBIDB_ATTRIBUTE WHERE UPPER(TITLE) = UPPER(@ATTRIBUTE))
|
|
RAISERROR ('NO VALID ATTRIBUTE',16,1)
|
|
|
|
SELECT @ATTR_ID = T.GUID, @TYP_ID = T.TYP_ID, @MULTI = T.MULTI_CONTEXT,@TYP_DESCR = T1.NAME_TYPE FROM TBIDB_ATTRIBUTE T, TBIDB_ATTRIBUTE_TYPE T1
|
|
WHERE T.TYP_ID = T1.GUID AND UPPER(T.TITLE) = UPPER(@ATTRIBUTE)
|
|
|
|
IF @LANG_CODE = 'de-DE'
|
|
SET @LANG_ID = 1
|
|
ELSE
|
|
SELECT @LANG_ID = GUID FROM TBIDB_LANGUAGE WHERE LANG_CODE = @LANG_CODE
|
|
|
|
|
|
IF @TYP_ID IN (1,8) --VARCHAR
|
|
BEGIN
|
|
select
|
|
@VALUE = TERM.TERM_VALUE
|
|
FROM
|
|
IDB.dbo.TBIDB_TERM_VALUE_VARCHAR TERM INNER JOIN IDB.dbo.TBIDB_TERM_LANGUAGE TLANG ON TERM.GUID = TLANG.TEXT_TERM_ID
|
|
INNER JOIN IDB.dbo.TBIDB_OBJECT_METADATA MD ON TLANG.GUID = MD.TEXT_TERM_LANG_ID
|
|
INNER JOIN IDB.dbo.TBIDB_OBJECT OBJ ON MD.IDB_OBJ_ID = OBJ.IDB_OBJ_ID
|
|
WHERE
|
|
OBJ.DELETED = 0 AND
|
|
MD.IDB_OBJ_ID = @IDB_OBJ_ID ANd
|
|
MD.ATTR_ID = @ATTR_ID AND TLANG.LANGUAGE_ID = @LANG_ID
|
|
END
|
|
ELSE IF @TYP_ID IN (2,7,9) --BIGINT
|
|
BEGIN
|
|
select
|
|
@VALUE = CONVERT(VARCHAR(20),TERM.TERM_VALUE)
|
|
FROM
|
|
IDB.dbo.TBIDB_TERM_VALUE_INTEGER TERM INNER JOIN TBIDB_OBJECT_METADATA MD ON TERM.GUID = MD.INT_TERM_ID
|
|
INNER JOIN IDB.dbo.TBIDB_OBJECT OBJ ON MD.IDB_OBJ_ID = OBJ.IDB_OBJ_ID
|
|
WHERE
|
|
OBJ.DELETED = 0 AND
|
|
MD.IDB_OBJ_ID = @IDB_OBJ_ID ANd
|
|
MD.ATTR_ID = @ATTR_ID
|
|
END
|
|
ELSE IF @TYP_ID = 3 --FLOAT
|
|
BEGIN
|
|
select
|
|
@VALUE = CONVERT(VARCHAR(20),TERM.TERM_VALUE)
|
|
FROM
|
|
IDB.dbo.TBIDB_TERM_VALUE_FLOAT TERM INNER JOIN TBIDB_OBJECT_METADATA MD ON TERM.GUID = MD.INT_TERM_ID
|
|
INNER JOIN IDB.dbo.TBIDB_OBJECT OBJ ON MD.IDB_OBJ_ID = OBJ.IDB_OBJ_ID
|
|
WHERE
|
|
OBJ.DELETED = 0 AND
|
|
MD.IDB_OBJ_ID = @IDB_OBJ_ID ANd
|
|
MD.ATTR_ID = @ATTR_ID
|
|
END
|
|
ELSE IF @TYP_ID = 4 --DECIMAL
|
|
BEGIN
|
|
select
|
|
@VALUE = CONVERT(VARCHAR(20),TERM.TERM_VALUE)
|
|
FROM
|
|
IDB.dbo.TBIDB_TERM_VALUE_DECIMAL TERM INNER JOIN TBIDB_OBJECT_METADATA MD ON TERM.GUID = MD.INT_TERM_ID
|
|
INNER JOIN IDB.dbo.TBIDB_OBJECT OBJ ON MD.IDB_OBJ_ID = OBJ.IDB_OBJ_ID
|
|
WHERE
|
|
OBJ.DELETED = 0 AND
|
|
MD.IDB_OBJ_ID = @IDB_OBJ_ID ANd
|
|
MD.ATTR_ID = @ATTR_ID
|
|
END
|
|
ELSE IF @TYP_ID = 5 --DATE
|
|
BEGIN
|
|
select
|
|
@VALUE = CONVERT(VARCHAR(20),TERM.TERM_VALUE)
|
|
FROM
|
|
IDB.dbo.TBIDB_TERM_VALUE_DATE TERM INNER JOIN TBIDB_OBJECT_METADATA MD ON TERM.GUID = MD.INT_TERM_ID
|
|
INNER JOIN IDB.dbo.TBIDB_OBJECT OBJ ON MD.IDB_OBJ_ID = OBJ.IDB_OBJ_ID
|
|
WHERE
|
|
OBJ.DELETED = 0 AND
|
|
MD.IDB_OBJ_ID = @IDB_OBJ_ID ANd
|
|
MD.ATTR_ID = @ATTR_ID
|
|
END
|
|
END
|
|
GO |