USE [BPMOC]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MEMBERS_GROUPS]') AND parent_object_id = OBJECT_ID(N'[dbo].[MEMBERS]'))
ALTER TABLE [dbo].[MEMBERS] DROP CONSTRAINT [FK_MEMBERS_GROUPS]
GO
/****** Object:  View [dbo].[vwGROUPMEMBERS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwGROUPMEMBERS]'))
DROP VIEW [dbo].[vwGROUPMEMBERS]
GO
/****** Object:  View [dbo].[vwDEPARTMENTMEMBERS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwDEPARTMENTMEMBERS]'))
DROP VIEW [dbo].[vwDEPARTMENTMEMBERS]
GO
/****** Object:  Table [dbo].[ORGANIZATIONS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ORGANIZATIONS]') AND type in (N'U'))
DROP TABLE [dbo].[ORGANIZATIONS]
GO
/****** Object:  Table [dbo].[MEMBERS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MEMBERS]') AND type in (N'U'))
DROP TABLE [dbo].[MEMBERS]
GO
/****** Object:  Table [dbo].[LABELS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LABELS]') AND type in (N'U'))
DROP TABLE [dbo].[LABELS]
GO
/****** Object:  Table [dbo].[JOBS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JOBS]') AND type in (N'U'))
DROP TABLE [dbo].[JOBS]
GO
/****** Object:  Table [dbo].[GROUPS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GROUPS]') AND type in (N'U'))
DROP TABLE [dbo].[GROUPS]
GO
/****** Object:  Table [dbo].[DEPARTMENTS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPARTMENTS]') AND type in (N'U'))
DROP TABLE [dbo].[DEPARTMENTS]
GO
/****** Object:  Table [dbo].[DEPARTMENTS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPARTMENTS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DEPARTMENTS](
	[ID] [int] NULL,
	[NAME] [nchar](48) NULL,
	[PARENTDEPT] [int] NULL,
	[ISINUSE] [int] NULL,
	[PROFILE] [nchar](256) NULL,
	[NEEDSSORTING] [int] NULL,
	[MISCDATA] [image] NULL,
	[ORGID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[GROUPS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GROUPS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GROUPS](
	[GID] [int] NOT NULL,
	[NAME] [nchar](48) NOT NULL,
	[INUSE] [int] NULL,
	[ACCESSGRP] [nchar](128) NULL,
	[ORGID] [int] NULL,
	[ISWEIGHTED] [int] NULL,
 CONSTRAINT [GROUPS_GID] PRIMARY KEY CLUSTERED 
(
	[GID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[JOBS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JOBS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JOBS](
	[ID] [int] NULL,
	[JOBFUNCTION] [nchar](75) NULL,
	[NAME] [nchar](256) NULL,
	[DEPTID] [int] NULL,
	[SUPERVISORID] [int] NULL,
	[JOBTYPE] [int] NULL,
	[ISPRIMARY] [int] NULL,
	[MISCDATA] [image] NULL,
	[RIGHTS] [nchar](256) NULL,
	[FULLNAME] [nchar](256) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[LABELS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LABELS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LABELS](
	[ID] [int] NULL,
	[DEPTID] [int] NULL,
	[BOXTEXT] [nchar](256) NULL,
	[MISCDATA] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[MEMBERS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MEMBERS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MEMBERS](
	[MEMBERID] [int] NOT NULL,
	[MEMBERTYPE] [int] NOT NULL,
	[NAME] [nchar](256) NOT NULL,
	[FULLNAME] [nchar](256) NULL,
	[GID] [int] NOT NULL,
	[BUSINESSID] [int] NULL,
	[WEIGHT] [int] NULL,
	[SEQ] [int] NULL,
	[MISCDATA] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[ORGANIZATIONS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ORGANIZATIONS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ORGANIZATIONS](
	[ID] [int] NULL,
	[NAME] [nchar](64) NULL,
	[DESCRIPTION] [nchar](1024) NULL
) ON [PRIMARY]
END
GO
/****** Object:  View [dbo].[vwDEPARTMENTMEMBERS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwDEPARTMENTMEMBERS]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwDEPARTMENTMEMBERS]	AS	SELECT     TOP 100 PERCENT D.ID AS DEPTID, D.NAME AS DEPTNAME, J.ID AS MEMBERID, J.NAME AS MEMBERNAME, J.JOBTYPE AS MEMBERTYPE, 						  J.FULLNAME, J.JOBFUNCTION, J.ISPRIMARY, J.SUPERVISORID, S.NAME AS SUPERVISORNAME, S.JOBFUNCTION AS SUPERVISORJF	FROM         JOBS AS J WITH (READPAST) INNER JOIN						  DEPARTMENTS AS D WITH (READPAST) ON J.DEPTID = D.ID INNER JOIN						  JOBS AS S WITH (READPAST) ON J.SUPERVISORID = S.ID	ORDER BY DEPTNAME' 
GO
/****** Object:  View [dbo].[vwGROUPMEMBERS]    Script Date: 16/02/2018 02:45:34 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwGROUPMEMBERS]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwGROUPMEMBERS]  AS SELECT	G.NAME ''GROUPNAME'', J.ID ''MEMBERID'', M.NAME ''MEMBERNAME'', M.MEMBERTYPE, J.FULLNAME, M.WEIGHT, M.SEQ FROM    GROUPS G(READPAST), JOBS J(READPAST), MEMBERS M WHERE   M.MEMBERID <> 0 AND J.NAME = M.NAME AND J.ISPRIMARY = 1 AND M.GID = G.GID UNION SELECT  G.NAME, 0, M.NAME, M.MEMBERTYPE, '''', M.WEIGHT, M.SEQ FROM    GROUPS G(READPAST), MEMBERS M WHERE     (M.MEMBERID = 0 OR M.MEMBERTYPE < 4) AND M.MEMBERTYPE <> 3 AND (M.MEMBERTYPE <> 2 OR CHARINDEX(''*'', M.NAME) = 0) AND M.GID = G.GID UNION SELECT  G.NAME, M.MEMBERID, M.NAME, M.MEMBERTYPE, M.FULLNAME, M.WEIGHT, M.SEQ FROM    GROUPS G(READPAST), MEMBERS M WHERE   (M.MEMBERID <> 0 AND (M.MEMBERTYPE = 4 OR M.MEMBERTYPE = 5) AND (SELECT COUNT(*) FROM JOBS J WHERE J.NAME = M.NAME) = 0)  AND M.GID = G.GID UNION SELECT  G.NAME, D .MEMBERID, D .MEMBERNAME, 3, D .FULLNAME, M.WEIGHT, M.SEQ FROM    GROUPS G(READPAST), vwDEPARTMENTMEMBERS D (READPAST), MEMBERS M WHERE   M.MEMBERTYPE = 2 AND CHARINDEX(''*'', M.NAME) <> 0 AND D .SUPERVISORJF = SUBSTRING(M.NAME, CHARINDEX(''*'', M.NAME) + 1,LEN(M.NAME) - CHARINDEX(''*'', M.NAME))  AND M.GID = G.GID UNION SELECT  TOP 100 PERCENT  G.NAME, D.MEMBERID, D.MEMBERNAME, M.MEMBERTYPE, D.FULLNAME, M.WEIGHT, M.SEQ FROM    GROUPS G(READPAST), vwDEPARTMENTMEMBERS D(READPAST), MEMBERS M(READPAST) WHERE   M.MEMBERTYPE = 3 AND D.JOBFUNCTION = SUBSTRING(M.NAME, CHARINDEX(''*'', M.NAME) + 1,LEN(M.NAME) - CHARINDEX(''*'', M.NAME)) AND D.DEPTNAME = SUBSTRING(M.NAME, 0, CHARINDEX(''*'', M.NAME)) AND M.GID = G.GID ORDER BY G.NAME, M.SEQ' 
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MEMBERS_GROUPS]') AND parent_object_id = OBJECT_ID(N'[dbo].[MEMBERS]'))
ALTER TABLE [dbo].[MEMBERS]  WITH CHECK ADD  CONSTRAINT [FK_MEMBERS_GROUPS] FOREIGN KEY([GID])
REFERENCES [dbo].[GROUPS] ([GID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MEMBERS_GROUPS]') AND parent_object_id = OBJECT_ID(N'[dbo].[MEMBERS]'))
ALTER TABLE [dbo].[MEMBERS] CHECK CONSTRAINT [FK_MEMBERS_GROUPS]
GO