-- Stand 2015-02-16 (TC)
-- "QDAS_USER" -> "USERGUID" "GUSERNO"

CREATE TABLE QDAS_AREA
( 
Q_KEY 	int,
Q_NO  	VARCHAR(40),
Q_NAME 	VARCHAR(80)
)
GO
CREATE UNIQUE INDEX QDAS_AREA ON QDAS_AREA(Q_KEY)
GO


CREATE TABLE QDAS_COST
( 
Q_KEY 	int,
Q_NO  	VARCHAR(40),
Q_NAME 	VARCHAR(80)
)
GO
CREATE UNIQUE INDEX QDAS_COST ON QDAS_COST(Q_KEY)
GO


CREATE TABLE QDAS_DEPT
( 
Q_KEY 	int,
Q_NO  	VARCHAR(40),
Q_NAME 	VARCHAR(80)
)
GO
CREATE UNIQUE INDEX QDAS_DEPT ON QDAS_DEPT(Q_KEY)
GO


CREATE TABLE QDAS_FILTERS 
( 
Q_USERKEY 	int,
Q_GROUPKEY  	int,
Q_MODUL 	smallint,
Q_KEY 		smallint,
Q_SUBKEY 	smallint,
Q_CONTEXT 	int,
Q_SUBCONTEXT 	smallint,
Q_HISTORYKEY  	smallint,
Q_ENTRY 	VARCHAR(50),
Q_VALUE 	TEXT
)
GO
CREATE UNIQUE INDEX QDAS_FILTERS ON QDAS_FILTERS(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY)
GO


CREATE TABLE QDAS_GRAPHICS 
( 
Q_USERKEY 	int,
Q_GROUPKEY  	int,
Q_MODUL 	smallint,
Q_KEY 		smallint,
Q_SUBKEY 	smallint,
Q_CONTEXT 	int,
Q_SUBCONTEXT 	smallint,
Q_HISTORYKEY  	smallint,
Q_ENTRY 	VARCHAR(50),
Q_VALUE 	TEXT
)
GO
CREATE UNIQUE INDEX QDAS_GRAPHICS ON QDAS_GRAPHICS(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY)
GO


CREATE TABLE QDAS_QUERIES 
( 
Q_USERKEY 	int,
Q_GROUPKEY  	int,
Q_MODUL 	smallint,
Q_KEY 		smallint,
Q_SUBKEY 	smallint,
Q_CONTEXT 	int,
Q_SUBCONTEXT 	smallint,
Q_HISTORYKEY  	smallint,
Q_ENTRY 	VARCHAR(50),
Q_VALUE 	TEXT
)
GO
CREATE UNIQUE INDEX QDAS_QUERIES ON QDAS_QUERIES(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY)
GO


CREATE TABLE QDAS_QUICKFILTERS
( 
Q_USERKEY 	int,
Q_GROUPKEY  	int,
Q_MODUL 	smallint,
Q_KEY 		smallint,
Q_SUBKEY 	smallint,
Q_CONTEXT 	int,
Q_SUBCONTEXT 	smallint,
Q_HISTORYKEY  	smallint,
Q_ENTRY 	VARCHAR(50),
Q_VALUE 	TEXT
)
GO
CREATE UNIQUE INDEX QDAS_QUICKFILTERS ON QDAS_QUICKFILTERS(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY)
GO


CREATE TABLE QDAS_STRATEGY
(
COMPANYKEY 		smallint,
MODUL 			smallint, 
SUBTYPE 		smallint,
ACTNO 			smallint,
COMPANYNAME 		VARCHAR(100), 
STANDARDSTRATEGY 	smallint,
STRATEGYVISIBLE 	smallint,
STRATEGYSTATUS 		int,
STRATEGYCHANGED 	smallint,
CHANGEDATETIME 		DATETIME,
CHANGEUSER 		VARCHAR(50)
)
GO
CREATE UNIQUE INDEX QDAS_STRATEGY ON QDAS_STRATEGY(COMPANYKEY,MODUL,SUBTYPE,ACTNO)
GO

  
CREATE TABLE QDAS_STRATEGYKEYS
(
USERKEY 	smallint,
COMPANYKEY 	smallint,
MODUL 		smallint, 
SUBTYPE 	smallint,
ACTNO 		smallint,
ENTRY 		VARCHAR(50),
VALUE 		TEXT
)
GO
CREATE UNIQUE INDEX QDAS_STRATEGYKEYS ON QDAS_STRATEGYKEYS(USERKEY,COMPANYKEY,MODUL,SUBTYPE,ACTNO,ENTRY)
GO


CREATE TABLE QDAS_STRATEGYKEYS_NEW
(
USERKEY 	smallint,
COMPANYKEY 	smallint,
MODUL 		smallint, 
SUBTYPE 	smallint,
ACTNO 		smallint,
ENTRY 		VARCHAR(50),
VALUE 		TEXT
)
GO
CREATE UNIQUE INDEX QDAS_STRATEGYKEYS_NEW ON QDAS_STRATEGYKEYS_NEW(USERKEY,COMPANYKEY,MODUL,SUBTYPE,ACTNO,ENTRY)
GO


CREATE TABLE QDAS_SYSTEM 
( 
Q_USERKEY 	int,
Q_GROUPKEY  	int,
Q_MODUL 	smallint,
Q_KEY 		smallint,
Q_SUBKEY 	smallint,
Q_CONTEXT 	int,
Q_SUBCONTEXT 	smallint,
Q_HISTORYKEY  	smallint,
Q_ENTRY 	VARCHAR(50),
Q_VALUE 	TEXT
)
GO
CREATE UNIQUE INDEX QDAS_SYSTEM ON QDAS_SYSTEM(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY)
GO


CREATE TABLE QDAS_SYSTEM_OPTIONS 
( 
Q_KEY 		int,
Q_OPTION 	VARCHAR(255)
)
GO
CREATE UNIQUE INDEX QDAS_SYSTEM_OPTIONS ON QDAS_SYSTEM_OPTIONS(Q_KEY)
GO

CREATE TABLE QDAS_UPLOAD 
( 
Q_USERKEY 	int,
Q_GROUPKEY  	int,
Q_MODUL 	smallint,
Q_KEY 		smallint,
Q_SUBKEY 	smallint,
Q_CONTEXT 	int,
Q_SUBCONTEXT 	smallint,
Q_HISTORYKEY  	smallint,
Q_ENTRY 	VARCHAR(50),
Q_VALUE 	TEXT
)
GO
CREATE UNIQUE INDEX QDAS_UPLOAD ON QDAS_UPLOAD(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY)
GO


CREATE TABLE QDAS_USER
(
USERNO		smallint,
USERGROUP	smallint,
USERNAME	VARCHAR(50),
PASSWORD	VARCHAR(255),
USINIDAT	VARCHAR(50),
USHIERA		smallint,
USBEREICH	int,
USABT		int,		
USWERKSTATT	int,
USNAME		VARCHAR(50),
USFIRMA		VARCHAR(50),
USABTNAME	VARCHAR(50),
USFON		VARCHAR(50),
USFAX		VARCHAR(50),
USMAIL		VARCHAR(50),
USZUS		int
)
GO
alter table QDAS_USER add USLOGIN	DATETIME
GO
alter table QDAS_USER add USLOGINPC	VARCHAR(50)
GO
alter table QDAS_USER add USLOGINIP	VARCHAR(15)
GO
alter table QDAS_USER add USLOGOUT	DATETIME
GO
alter table QDAS_USER add USLOGOUTPC	VARCHAR(50)
GO
alter table QDAS_USER add USLOGOUTIP	VARCHAR(15)
GO
alter table QDAS_USER add USERGUID	VARCHAR(50)
GO
alter table QDAS_USER add GUSERNO	smallint
GO

ALTER TABLE QDAS_USER ALTER COLUMN PASSWORD VARCHAR(255)
GO

CREATE UNIQUE INDEX QDAS_USER ON QDAS_USER(USERNO)
GO

CREATE TABLE QDAS_USERGROUP
(
GROUPNO	smallint,
GROUPNAME	VARCHAR(50)
)
GO
CREATE UNIQUE INDEX QDAS_USERGROUP ON QDAS_USERGROUP(GROUPNO)
GO


CREATE TABLE QDAS_USERRIGHTS
(
ZRUSERNO			int,
ZRUSERGROUP			smallint,
ZRMESSWEIN			smallint,
ZRMESSWAEND			smallint,
ZRPRPLEIN			smallint,
ZRPRPLAEND			smallint,
ZRUSERQUERIES			smallint,
ZRKATEDIT			smallint,
ZRDBSAVE			smallint,
ZREVALCHANGE			smallint,
ZRSYSKONF			smallint,
ZROPTIONAL			VARCHAR(50),
ZRPROTECT_AUSWAHLSTRATEGY	smallint,
ZRPROTECT_DELETE_TEILAUSDB	smallint,
ZRPROTECT_SELEKTION_INGRAFIK	smallint,
ZRPROTECT_AUSREISSER_ENTFERNEN	smallint,
ZRPROTECT_VERTEILUNG_AENDERN	smallint,
ZRPROTECT_ANALYSE_QRK_AENDERN	smallint,
ZRPROTECT_SPC_QRK_AENDERN	smallint,
ZRPROTECT_AUSGABEPUNKT_AENDERN	smallint
)
GO
alter table QDAS_USERRIGHTS add ZRDBADMIN			smallint
GO
alter table QDAS_USERRIGHTS add ZRPROTECT_SIGNATUR_VIEW		smallint
GO
alter table QDAS_USERRIGHTS add ZRPROTECT_SIGNATUR_CREATE	smallint
GO
alter table QDAS_USERRIGHTS add ZRPROTECT_PRUEFERNAME_EINGABE 	smallint
GO
alter table QDAS_USERRIGHTS add ZRUSERADMIN			smallint
GO

alter table QDAS_USERRIGHTS add ZRPROTECT_SEEALLPARTS 		smallint
GO
alter table QDAS_USERRIGHTS add ZRPROTECT_SEE_CHANGE_HISTORY 	smallint
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZREXTENDED_MMSELECT		smallint
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZR_EDIT_SUBCAT			smallint
GO

CREATE UNIQUE INDEX QDAS_USERRIGHTS ON QDAS_USERRIGHTS(ZRUSERNO,ZRUSERGROUP)
GO


CREATE TABLE QDAS_WORKSHOP
( 
Q_KEY 	int,
Q_NO  	VARCHAR(40),
Q_NAME 	VARCHAR(80)
)
GO
CREATE UNIQUE INDEX QDAS_WORKSHOP ON QDAS_WORKSHOP(Q_KEY)
GO


CREATE TABLE SAVE_RESULTS
( 
CONF_NAME 	VARCHAR(50),
MODUL  		int,
Q_VALUE 	TEXT
)
GO
CREATE UNIQUE INDEX SAVE_RESULTS ON SAVE_RESULTS(CONF_NAME,MODUL)
GO


CREATE TABLE SR_MERKMAL_REPORT
( 
METEIL		int,
MEMERKMAL	smallint,
MEREPORT	VARCHAR(255),
MODUL		smallint,
EMAIL		VARCHAR(255),
PRINTER		VARCHAR(255),
CONFNAME	VARCHAR(50),
GRUPPE		int,
TEMPLATE	VARCHAR(255),
DBID		VARCHAR(50)
)
GO
CREATE INDEX CONFNAME ON SR_MERKMAL_REPORT(METEIL,MEMERKMAL,CONFNAME,MEREPORT,MODUL)
GO

CREATE INDEX DBID ON SR_MERKMAL_REPORT(DBID)
GO


CREATE TABLE SR_REPORTS
( 
REPORTNO	smallint,
REPORTNAME	VARCHAR(255),
REPORTTYPE	smallint,
MODUL		smallint,
CONFNAME	VARCHAR(50),
REPORTTYPETM	smallint,
DBID		VARCHAR(50)
)
GO
CREATE INDEX SRR_DBID ON SR_REPORTS(DBID)
GO


CREATE TABLE QDAS_LIC
( 
Q_USERKEY 	int,
Q_MODUL 	smallint,
Q_KEY 		smallint,
Q_ENTRY 	int
)
GO
ALTER TABLE QDAS_LIC ADD Q_CONTEXT		smallint
GO

CREATE TABLE QDAS_FDA_PROT
( 
USER_GROUP_NO 	int,
TYPE 		smallint,
Q_KEY 		VARCHAR(50),
CHANGEDVALUE 	VARCHAR(255),
CHANGEDATETIME	DATETIME,
CHANGEUSERNO	int
)
GO

CREATE TABLE TMPNODE
( 
NODE smallint,
INDEXNO int,
TMG VARCHAR(40),
INDEXNEW int
)
GO
ALTER TABLE TMPNODE ADD INDEXNEW int
GO

CREATE TABLE QDAS_OFFLINE
( 
PC_GUID 	VARCHAR(50),
PC_NAME  	VARCHAR(50),
PROD_KEY 	smallint,
LANG		VARCHAR(50),
Q_CONTEXT	smallint,
ANZAHL		smallint,
ABLAUF		DATETIME
)
GO

CREATE UNIQUE INDEX QDAS_OFFLINE ON QDAS_OFFLINE(PC_GUID,PC_Name,Prod_Key,Lang,Q_CONTEXT)
GO

alter table QDAS_USERRIGHTS add ZRPROTECT_VERTEILUNG_BERECHNEN 	smallint
GO

CREATE TABLE AENDERUNGSSTAND
(
TABELLE			VARCHAR(50),
FELD			VARCHAR(50),
TYP			VARCHAR(50),
BEMERKUNG		VARCHAR(100),
STATUS			VARCHAR(50),
DATUM			DATETIME,
VERSION			VARCHAR(50),
BEMERKUNG_AENDERUNG	VARCHAR(100),
AENDERUNGSGRUND		TEXT
)
GO

alter table QDAS_USERRIGHTS add ZRPROTECT_CHANGE_DBCONN 	smallint
GO

ALTER TABLE QDAS_USERRIGHTS ADD ZR_EDIT_AUXDATA			SMALLINT 
GO

ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_IMPORT		SMALLINT 
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_EXPORT		SMALLINT 
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_CONFIG_WS		SMALLINT 
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_MANAGE_REP		SMALLINT 
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_EXEMPTCHARS		SMALLINT 
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_MODIFYDISTNO		SMALLINT 
GO
ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_CREATEMAINNODE	SMALLINT 
GO

ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_AUSWAHLGCSUBSTRATEGY	SMALLINT 
GO

ALTER TABLE QDAS_USERRIGHTS ADD ZR_CLOSE_PDCA			SMALLINT 
GO

CREATE TABLE QDAS_USER_ACCESS
( 
Q_USERNO 	INT,
Q_LAST_ACCESS	DATETIME
)
GO

CREATE UNIQUE INDEX QDAS_USER_ACCESS ON QDAS_USER_ACCESS(Q_USERNO)
GO

create table MERKMAL_REPORT
(
	METEIL		int,
	MEMERKMAL	smallint,
	MEREPORT	VARCHAR(255),
	MODUL		smallint,
	EMAIL		VARCHAR(255),
	PRINTER		VARCHAR(255),
	CONFNAME	VARCHAR(50),
	GRUPPE		int,
	TEMPLATE	VARCHAR(255),
	MEREPORTALARM	VARCHAR(255),
 	PDFPATH		VARCHAR(255),
	WITHOUT_AL_TXT	VARCHAR(255),
	ALTPDFDNERZ VARCHAR(255),
 	PDF_URL		VARCHAR(255),
	DBID		int
)
GO
create index MERKMAL_REPORT_TEIL on MERKMAL_REPORT(METEIL)
GO

CREATE TABLE MERKMAL_REPORT_NO_SELECT
(
	METEIL                        	INT ,
	MEMERKMAL                     	SMALLINT ,
	MEREPORT                      	VARCHAR(255),
	MODUL                         	SMALLINT ,
	CONFNAME                      	VARCHAR(50),
	DBID				int
)
GO

create index MERKMAL_REPORT_NST on MERKMAL_REPORT_NO_SELECT(METEIL)
GO


create table Reports
(
	ReportNo	smallint,
	ReportName	VARCHAR(255),
	ReportType	smallint,
	Modul		smallint,
	CONFNAME	VARCHAR(50),
	DBID		int
)
GO

create table QDAS_RSJOBS
(
	RSSTATION	VARCHAR(38),
	RSSECTION	VARCHAR(255),
	Q_ENTRY		VARCHAR(255),
	Q_VALUE		TEXT
)
GO
create index QDAS_RSJOBS on QDAS_RSJOBS(RSSTATION,RSSECTION,Q_ENTRY)
GO

ALTER TABLE QDAS_USERRIGHTS ADD ZR_DENIED_LOADSAVE_USER_CFG SMALLINT 
GO

ALTER TABLE QDAS_USERRIGHTS ADD ZRPROTECT_LOCK_TESTPLAN	SMALLINT
GO

ALTER TABLE QDAS_USER ADD USSIGNATURE IMAGE
GO

ALTER TABLE QDAS_USER ADD USSIGNATUREFILEXT VARCHAR(10)
GO

create table QDAS_TEMPLATE
(
	TEMPLATE_ID		VARCHAR(255),
	TEMPLATE_BESCHREIBUNG	VARCHAR(255),
	TEMPLATE_BILD		VARBINARY(MAX),
	TEMPLATE_KONFIG		VARCHAR(1000)
)
GO