Appian Usage Insights

Hi Team,

              is it possible to release the DB scripts for MS SQL Server as well.

thanks in advance

Amit

Hi Team,

              is it possible to release the DB scripts for MS SQL Server as well.

thanks in advance

Amit

I have one that seem working for me but not sure that all is fully correct

I'm not sure that all is correct but tha application seem working for me

-- --------------------------------------------------------
-- ********************************************************
-- ********************************************************
-- *** TABLES ***
-- ********************************************************
-- ********************************************************
-- --------------------------------------------------------

--
-- Table structure for table LMA_ANALYSIS_RUN
--

DROP TABLE IF EXISTS LMA_ANALYSIS_RUN
GO
CREATE TABLE LMA_ANALYSIS_RUN (
analysis_id int NOT NULL identity(1,1),
collection_run_id int NOT NULL,
started_by varchar(255) NOT NULL,
started_on datetime NOT NULL,
end_on datetime DEFAULT NULL,
user_count int NOT NULL,
status varchar(255) NOT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_STG_CSV_AUDITED_LOGINS
--

DROP TABLE IF EXISTS LMA_STG_CSV_AUDITED_LOGINS
GO
CREATE TABLE LMA_STG_CSV_AUDITED_LOGINS (
audited_login_id int NOT NULL identity(1,1),
username varchar(255) NOT NULL,
login datetime NOT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_COLLECTION_RUN
--

DROP TABLE IF EXISTS LMA_COLLECTION_RUN
GO
CREATE TABLE LMA_COLLECTION_RUN (
collection_run_id int NOT NULL identity(1,1),
status varchar(50) DEFAULT NULL,
collection_run_date date DEFAULT NULL,
started_on datetime DEFAULT NULL,
ended_on datetime DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_ENTITY
--

DROP TABLE IF EXISTS LMA_ENTITY
GO
CREATE TABLE LMA_ENTITY (
entity_id int NOT NULL identity(1,1),
entity_name varchar(255) DEFAULT NULL,
entity_type varchar(255) DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_ENTITY_LICENCE_POOL
--

DROP TABLE IF EXISTS LMA_ENTITY_LICENCE_POOL
GO
CREATE TABLE LMA_ENTITY_LICENCE_POOL (
entity_licence_pool_id int NOT NULL identity(1,1),
entity_id int DEFAULT NULL,
licence_type_id int DEFAULT NULL,
number_of_licences int DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_ENTITY_LICENCE_POOL_GROUP
--

DROP TABLE IF EXISTS LMA_ENTITY_LICENCE_POOL_GROUP
GO
CREATE TABLE LMA_ENTITY_LICENCE_POOL_GROUP (
entity_licence_pool_group_id int NOT NULL identity(1,1),
entity_licence_pool_id int DEFAULT NULL,
group_id int DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_ENVIRONMENT
--

DROP TABLE IF EXISTS LMA_ENVIRONMENT
GO
CREATE TABLE LMA_ENVIRONMENT (
environment_id int NOT NULL identity(1,1),
environment_name varchar(255) DEFAULT NULL,
environment_description varchar(5000) DEFAULT NULL,
-- LMA-48
is_active tinyint DEFAULT 1
)
GO

-- --------------------------------------------------------

-- --------------------------------------------------------

--
-- Table structure for table LMA_ENVIRONMENT_USER
--

DROP TABLE IF EXISTS LMA_ENVIRONMENT_USER
GO
CREATE TABLE LMA_ENVIRONMENT_USER (
environment_user_id int NOT NULL identity(1,1),
username varchar(255) DEFAULT NULL,
user_uuid varchar(42) DEFAULT NULL,
collection_run_id int DEFAULT NULL,
environment_id int DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_ENVIRONMENT_USER_GROUP
--

DROP TABLE IF EXISTS LMA_ENVIRONMENT_USER_GROUP
GO
CREATE TABLE LMA_ENVIRONMENT_USER_GROUP (
environment_user_group_id int NOT NULL identity(1,1),
username varchar(255) DEFAULT NULL,
user_uuid varchar(42) DEFAULT NULL,
group_name varchar(255) DEFAULT NULL,
group_uuid varchar(255) DEFAULT NULL,
collection_run_id int DEFAULT NULL,
environment_id int DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_ENVIRONMENT_USER_LOGIN
--

DROP TABLE IF EXISTS LMA_ENVIRONMENT_USER_LOGIN
GO
CREATE TABLE LMA_ENVIRONMENT_USER_LOGIN (
Environment_user_login_id int NOT NULL identity(1,1),
username varchar(255) DEFAULT NULL,
login datetime DEFAULT NULL,
collection_run_id int DEFAULT NULL,
environment_id int DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_GROUP
--

DROP TABLE IF EXISTS LMA_GROUP
GO
CREATE TABLE LMA_GROUP (
group_id int NOT NULL identity(1,1),
group_name varchar(255) DEFAULT NULL,
group_uuid varchar(255) DEFAULT NULL,
environment_id int DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_STG_CSV_GROUP_MEMBERSHIP
--

DROP TABLE IF EXISTS LMA_STG_CSV_GROUP_MEMBERSHIP
GO
CREATE TABLE LMA_STG_CSV_GROUP_MEMBERSHIP (
group_membership_id int NOT NULL identity(1,1),
username varchar(255) DEFAULT NULL,
user_uuid varchar(255) DEFAULT NULL,
group_name varchar(255) DEFAULT NULL,
group_uuid varchar(255) DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_LICENCE_POOL_USER_COUNT
--

DROP TABLE IF EXISTS LMA_LICENCE_POOL_USER_COUNT
GO
CREATE TABLE LMA_LICENCE_POOL_USER_COUNT (
licence_pool_user_count_id int NOT NULL identity(1,1),
entity_licence_pool_id int DEFAULT NULL,
user_count int DEFAULT NULL,
collection_run_id int DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_LICENCE_TYPE
--

DROP TABLE IF EXISTS LMA_LICENCE_TYPE
GO
CREATE TABLE LMA_LICENCE_TYPE (
licence_type_id int NOT NULL identity(1,1),
licence_type_name varchar(255) DEFAULT NULL,
licence_type_description varchar(5000) DEFAULT NULL,
licence_type_value float DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_USER_ALLOCATED_LICENCE_POOL
--

DROP TABLE IF EXISTS LMA_USER_ALLOCATED_LICENCE_POOL
GO
CREATE TABLE LMA_USER_ALLOCATED_LICENCE_POOL (
user_allocated_licence_pool_id int NOT NULL identity(1,1),
entity_licence_pool_id int DEFAULT NULL,
username varchar(255) DEFAULT NULL
)
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_USER_POTENTIAL_LICENCE_POOL
--

DROP TABLE IF EXISTS LMA_USER_POTENTIAL_LICENCE_POOL
GO
CREATE TABLE LMA_USER_POTENTIAL_LICENCE_POOL (
user_potential_licence_pool_id int NOT NULL identity(1,1),
entity_licence_pool_id int DEFAULT NULL,
username varchar(255) DEFAULT NULL
)
GO

--
-- Indexes for dumped tables
--

--
-- Indexes for table LMA_ANALYSIS_RUN
--
ALTER TABLE LMA_ANALYSIS_RUN
ADD PRIMARY KEY (analysis_id)
GO

--
-- Indexes for table LMA_STG_CSV_AUDITED_LOGINS
--
ALTER TABLE LMA_STG_CSV_AUDITED_LOGINS
ADD PRIMARY KEY (audited_login_id)
GO

--
-- Indexes for table LMA_COLLECTION_RUN
--
ALTER TABLE LMA_COLLECTION_RUN
ADD PRIMARY KEY (collection_run_id)
GO

--
-- Indexes for table LMA_ENTITY
--
ALTER TABLE LMA_ENTITY
ADD PRIMARY KEY (entity_id)
GO

--
-- Indexes for table LMA_ENTITY_LICENCE_POOL
--
ALTER TABLE LMA_ENTITY_LICENCE_POOL
ADD PRIMARY KEY (entity_licence_pool_id)
GO
CREATE NONCLUSTERED INDEX [fk_entity_license_entity_id] ON LMA_ENTITY_LICENCE_POOL
(
[entity_id] ASC
)
GO
CREATE NONCLUSTERED INDEX fk_entity_license_type_id ON [dbo].LMA_ENTITY_LICENCE_POOL
(
[licence_type_id] ASC
)
GO
--
-- Indexes for table LMA_ENTITY_LICENCE_POOL_GROUP
--
ALTER TABLE LMA_ENTITY_LICENCE_POOL_GROUP
ADD PRIMARY KEY (entity_licence_pool_group_id)
--ADD KEY ix_elpg_licence_pool_id (entity_licence_pool_id),
--ADD KEY ix_elpg_group_id (group_id)
GO

--
-- Indexes for table LMA_ENVIRONMENT
--
ALTER TABLE LMA_ENVIRONMENT
ADD PRIMARY KEY (environment_id)
GO

-- Indexes for table LMA_ENVIRONMENT_USER
--
ALTER TABLE LMA_ENVIRONMENT_USER
ADD PRIMARY KEY (environment_user_id)
--ADD KEY fk_eu_environment_id (environment_id),
--ADD KEY ix_eu_username (environment_user_id),
--ADD KEY ix_eu_collection_run_id (collection_run_id)
GO

--
-- Indexes for table LMA_ENVIRONMENT_USER_GROUP
--
ALTER TABLE LMA_ENVIRONMENT_USER_GROUP
ADD PRIMARY KEY (environment_user_group_id)
--ADD KEY fk_eug_environment_id (environment_id),
--ADD KEY fk_eug_collection_run_id (collection_run_id),
--ADD INDEX ix_group_name (group_name),
--ADD INDEX ix_username (username)
GO

--
-- Indexes for table LMA_ENVIRONMENT_USER_LOGIN
--
ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN
ADD PRIMARY KEY (Environment_user_login_id)
--ADD KEY fk_eul_environment_id (environment_id),
--ADD KEY fk_eul_collection_run_id (collection_run_id),
--ADD INDEX ix_username (username)
GO

--ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN ADD INDEX ix_login (login)
--GO

--
-- Indexes for table LMA_GROUP
--
ALTER TABLE LMA_GROUP
ADD PRIMARY KEY (group_id)
--ADD KEY lma_fk_group_environment_id (environment_id),
--ADD KEY ix_g_group_uuid (group_uuid) USING BTREE
GO

--
-- Indexes for table LMA_STG_CSV_GROUP_MEMBERSHIP
--
ALTER TABLE LMA_STG_CSV_GROUP_MEMBERSHIP
ADD PRIMARY KEY (group_membership_id)
--ADD KEY ix_gm_group_uuid (group_uuid),
--ADD KEY ix_gm_username (username) USING BTREE
GO

--
-- Indexes for table LMA_LICENCE_POOL_USER_COUNT
--
ALTER TABLE LMA_LICENCE_POOL_USER_COUNT
ADD PRIMARY KEY (licence_pool_user_count_id)
--ADD KEY fk_lpuc_collection_run_id (collection_run_id),
--ADD KEY fk_lpuc_entity_licence_pool_id (entity_licence_pool_id)
GO

--
-- Indexes for table LMA_LICENCE_TYPE
--
ALTER TABLE LMA_LICENCE_TYPE
ADD PRIMARY KEY (licence_type_id)
GO

--
-- Indexes for table LMA_USER_ALLOCATED_LICENCE_POOL
--
ALTER TABLE LMA_USER_ALLOCATED_LICENCE_POOL
ADD PRIMARY KEY (user_allocated_licence_pool_id)
--ADD KEY fk_ualp_entity_licence_pool_id (entity_licence_pool_id),
--ADD INDEX ix_username (username)
GO

--
-- Indexes for table LMA_USER_POTENTIAL_LICENCE_POOL
--
ALTER TABLE LMA_USER_POTENTIAL_LICENCE_POOL
ADD PRIMARY KEY (user_potential_licence_pool_id)
--ADD KEY fk_uplp_entity_licence_pool_id (entity_licence_pool_id),
--ADD INDEX ix_username (username)
GO

-- Constraints for dumped tables
--

--
-- Constraints for table LMA_ENTITY_LICENCE_POOL
--
ALTER TABLE LMA_ENTITY_LICENCE_POOL
ADD CONSTRAINT fk_entity_license_entity_id FOREIGN KEY (entity_id) REFERENCES LMA_ENTITY (entity_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE LMA_ENTITY_LICENCE_POOL
ADD CONSTRAINT fk_entity_license_type_id FOREIGN KEY (licence_type_id) REFERENCES LMA_LICENCE_TYPE (licence_type_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_ENTITY_LICENCE_POOL_GROUP
--
ALTER TABLE LMA_ENTITY_LICENCE_POOL_GROUP
ADD CONSTRAINT fk_elpg_group_id FOREIGN KEY (group_id) REFERENCES LMA_GROUP (group_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE LMA_ENTITY_LICENCE_POOL_GROUP
ADD CONSTRAINT fk_elpg_license_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_ENVIRONMENT_USER
--
ALTER TABLE LMA_ENVIRONMENT_USER
ADD CONSTRAINT fk_eu_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE LMA_ENVIRONMENT_USER
ADD CONSTRAINT fk_eu_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_ENVIRONMENT_USER_GROUP
--
ALTER TABLE LMA_ENVIRONMENT_USER_GROUP
ADD CONSTRAINT fk_eug_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE LMA_ENVIRONMENT_USER_GROUP
ADD CONSTRAINT fk_eug_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_ENVIRONMENT_USER_LOGIN
--
ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN
ADD CONSTRAINT fk_eul_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE LMA_ENVIRONMENT_USER_LOGIN
ADD CONSTRAINT fk_eul_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_GROUP
--
ALTER TABLE LMA_GROUP
ADD CONSTRAINT lma_fk_group_environment_id FOREIGN KEY (environment_id) REFERENCES LMA_ENVIRONMENT (environment_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_LICENCE_POOL_USER_COUNT
--
ALTER TABLE LMA_LICENCE_POOL_USER_COUNT
ADD CONSTRAINT fk_lpuc_collection_run_id FOREIGN KEY (collection_run_id) REFERENCES LMA_COLLECTION_RUN (collection_run_id) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE LMA_LICENCE_POOL_USER_COUNT
ADD CONSTRAINT fk_lpuc_entity_licence_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_USER_ALLOCATED_LICENCE_POOL
--
ALTER TABLE LMA_USER_ALLOCATED_LICENCE_POOL
ADD CONSTRAINT fk_ualp_entity_licence_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--
-- Constraints for table LMA_USER_POTENTIAL_LICENCE_POOL
--
ALTER TABLE LMA_USER_POTENTIAL_LICENCE_POOL
ADD CONSTRAINT fk_uplp_entity_licence_pool_id FOREIGN KEY (entity_licence_pool_id) REFERENCES LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- --------------------------------------------------------

--
-- Table structure for table LMA_DEFAULT_LICENCE_POOL_GROUP
--

DROP TABLE IF EXISTS LMA_DEFAULT_LICENCE_POOL_GROUP
GO
CREATE TABLE LMA_DEFAULT_LICENCE_POOL_GROUP (
row_id int NOT NULL identity(1,1),
group_name varchar(255) DEFAULT NULL
)
GO

--
-- Indexes for dumped tables
--

--
-- Indexes for table LMA_DEFAULT_LICENCE_POOL_GROUP
--
ALTER TABLE LMA_DEFAULT_LICENCE_POOL_GROUP
ADD PRIMARY KEY (row_id)
--ADD KEY group_name (group_name)
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_entity_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_entity_license_entity_id' AND so.type in (N'U'))
DROP INDEX [fk_entity_license_entity_id] ON [dbo].[lma_entity_licence_pool]
GO
CREATE NONCLUSTERED INDEX [fk_entity_license_entity_id] ON [dbo].[lma_entity_licence_pool]
(
[entity_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_entity_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_entity_license_type_id' AND so.type in (N'U'))
DROP INDEX [fk_entity_license_type_id] ON [dbo].[lma_entity_licence_pool]
GO
CREATE NONCLUSTERED INDEX [fk_entity_license_type_id] ON [dbo].[lma_entity_licence_pool]
(
[licence_type_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user' AND sc.name = N'dbo' AND si.name = N'fk_eu_environment_id' AND so.type in (N'U'))
DROP INDEX [fk_eu_environment_id] ON [dbo].[lma_environment_user]
GO
CREATE NONCLUSTERED INDEX [fk_eu_environment_id] ON [dbo].[lma_environment_user]
(
[environment_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'fk_eug_collection_run_id' AND so.type in (N'U'))
DROP INDEX [fk_eug_collection_run_id] ON [dbo].[lma_environment_user_group]
GO
CREATE NONCLUSTERED INDEX [fk_eug_collection_run_id] ON [dbo].[lma_environment_user_group]
(
[collection_run_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'fk_eug_environment_id' AND so.type in (N'U'))
DROP INDEX [fk_eug_environment_id] ON [dbo].[lma_environment_user_group]
GO
CREATE NONCLUSTERED INDEX [fk_eug_environment_id] ON [dbo].[lma_environment_user_group]
(
[environment_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'fk_eul_collection_run_id' AND so.type in (N'U'))
DROP INDEX [fk_eul_collection_run_id] ON [dbo].[lma_environment_user_login]
GO
CREATE NONCLUSTERED INDEX [fk_eul_collection_run_id] ON [dbo].[lma_environment_user_login]
(
[collection_run_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'fk_eul_environment_id' AND so.type in (N'U'))
DROP INDEX [fk_eul_environment_id] ON [dbo].[lma_environment_user_login]
GO
CREATE NONCLUSTERED INDEX [fk_eul_environment_id] ON [dbo].[lma_environment_user_login]
(
[environment_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_licence_pool_user_count' AND sc.name = N'dbo' AND si.name = N'fk_lpuc_collection_run_id' AND so.type in (N'U'))
DROP INDEX [fk_lpuc_collection_run_id] ON [dbo].[lma_licence_pool_user_count]
GO
CREATE NONCLUSTERED INDEX [fk_lpuc_collection_run_id] ON [dbo].[lma_licence_pool_user_count]
(
[collection_run_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_licence_pool_user_count' AND sc.name = N'dbo' AND si.name = N'fk_lpuc_entity_licence_pool_id' AND so.type in (N'U'))
DROP INDEX [fk_lpuc_entity_licence_pool_id] ON [dbo].[lma_licence_pool_user_count]
GO
CREATE NONCLUSTERED INDEX [fk_lpuc_entity_licence_pool_id] ON [dbo].[lma_licence_pool_user_count]
(
[entity_licence_pool_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_user_allocated_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_ualp_entity_licence_pool_id' AND so.type in (N'U'))
DROP INDEX [fk_ualp_entity_licence_pool_id] ON [dbo].[lma_user_allocated_licence_pool]
GO
CREATE NONCLUSTERED INDEX [fk_ualp_entity_licence_pool_id] ON [dbo].[lma_user_allocated_licence_pool]
(
[entity_licence_pool_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_user_potential_licence_pool' AND sc.name = N'dbo' AND si.name = N'fk_uplp_entity_licence_pool_id' AND so.type in (N'U'))
DROP INDEX [fk_uplp_entity_licence_pool_id] ON [dbo].[lma_user_potential_licence_pool]
GO
CREATE NONCLUSTERED INDEX [fk_uplp_entity_licence_pool_id] ON [dbo].[lma_user_potential_licence_pool]
(
[entity_licence_pool_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_default_licence_pool_group' AND sc.name = N'dbo' AND si.name = N'group_name' AND so.type in (N'U'))
DROP INDEX [group_name] ON [dbo].[lma_default_licence_pool_group]
GO
CREATE NONCLUSTERED INDEX [group_name] ON [dbo].[lma_default_licence_pool_group]
(
[group_name] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_entity_licence_pool_group' AND sc.name = N'dbo' AND si.name = N'ix_elpg_group_id' AND so.type in (N'U'))
DROP INDEX [ix_elpg_group_id] ON [dbo].[lma_entity_licence_pool_group]
GO
CREATE NONCLUSTERED INDEX [ix_elpg_group_id] ON [dbo].[lma_entity_licence_pool_group]
(
[group_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_entity_licence_pool_group' AND sc.name = N'dbo' AND si.name = N'ix_elpg_licence_pool_id' AND so.type in (N'U'))
DROP INDEX [ix_elpg_licence_pool_id] ON [dbo].[lma_entity_licence_pool_group]
GO
CREATE NONCLUSTERED INDEX [ix_elpg_licence_pool_id] ON [dbo].[lma_entity_licence_pool_group]
(
[entity_licence_pool_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user' AND sc.name = N'dbo' AND si.name = N'ix_eu_collection_run_id' AND so.type in (N'U'))
DROP INDEX [ix_eu_collection_run_id] ON [dbo].[lma_environment_user]
GO
CREATE NONCLUSTERED INDEX [ix_eu_collection_run_id] ON [dbo].[lma_environment_user]
(
[collection_run_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user' AND sc.name = N'dbo' AND si.name = N'ix_eu_username' AND so.type in (N'U'))
DROP INDEX [ix_eu_username] ON [dbo].[lma_environment_user]
GO
CREATE NONCLUSTERED INDEX [ix_eu_username] ON [dbo].[lma_environment_user]
(
[environment_user_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_group' AND sc.name = N'dbo' AND si.name = N'ix_g_group_uuid' AND so.type in (N'U'))
DROP INDEX [ix_g_group_uuid] ON [dbo].[lma_group]
GO
CREATE NONCLUSTERED INDEX [ix_g_group_uuid] ON [dbo].[lma_group]
(
[group_uuid] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_stg_csv_group_membership' AND sc.name = N'dbo' AND si.name = N'ix_gm_group_uuid' AND so.type in (N'U'))
DROP INDEX [ix_gm_group_uuid] ON [dbo].[lma_stg_csv_group_membership]
GO
CREATE NONCLUSTERED INDEX [ix_gm_group_uuid] ON [dbo].[lma_stg_csv_group_membership]
(
[group_uuid] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_stg_csv_group_membership' AND sc.name = N'dbo' AND si.name = N'ix_gm_username' AND so.type in (N'U'))
DROP INDEX [ix_gm_username] ON [dbo].[lma_stg_csv_group_membership]
GO
CREATE NONCLUSTERED INDEX [ix_gm_username] ON [dbo].[lma_stg_csv_group_membership]
(
[username] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'ix_group_name' AND so.type in (N'U'))
DROP INDEX [ix_group_name] ON [dbo].[lma_environment_user_group]
GO
CREATE NONCLUSTERED INDEX [ix_group_name] ON [dbo].[lma_environment_user_group]
(
[group_name] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'ix_login' AND so.type in (N'U'))
DROP INDEX [ix_login] ON [dbo].[lma_environment_user_login]
GO
CREATE NONCLUSTERED INDEX [ix_login] ON [dbo].[lma_environment_user_login]
(
[login] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_login' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
DROP INDEX [ix_username] ON [dbo].[lma_environment_user_login]
GO
CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_environment_user_login]
(
[username] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_environment_user_group' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
DROP INDEX [ix_username] ON [dbo].[lma_environment_user_group]
GO
CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_environment_user_group]
(
[username] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_user_potential_licence_pool' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
DROP INDEX [ix_username] ON [dbo].[lma_user_potential_licence_pool]
GO
CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_user_potential_licence_pool]
(
[username] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_user_allocated_licence_pool' AND sc.name = N'dbo' AND si.name = N'ix_username' AND so.type in (N'U'))
DROP INDEX [ix_username] ON [dbo].[lma_user_allocated_licence_pool]
GO
CREATE NONCLUSTERED INDEX [ix_username] ON [dbo].[lma_user_allocated_licence_pool]
(
[username] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
GO

USE LMA
GO
IF EXISTS (
SELECT * FROM sys.objects so JOIN sys.indexes si
ON so.object_id = si.object_id
JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = N'lma_group' AND sc.name = N'dbo' AND si.name = N'lma_fk_group_environment_id' AND so.type in (N'U'))
DROP INDEX [lma_fk_group_environment_id] ON [dbo].[lma_group]
GO
CREATE NONCLUSTERED INDEX [lma_fk_group_environment_id] ON [dbo].[lma_group]
(
[environment_id] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
-- --------------------------------------------------------
-- ********************************************************
-- ********************************************************
-- *** DATA ***
-- ********************************************************
-- ********************************************************
-- --------------------------------------------------------

INSERT INTO LMA_ENTITY (entity_id, entity_name, entity_type) VALUES
(1, 'Default', 'Default Entity for Unassigned Licences')
GO

--
-- Dumping data for table LMA_LICENCE_TYPE
--

INSERT INTO LMA_LICENCE_TYPE (licence_type_id, licence_type_name, licence_type_description, licence_type_value) VALUES
(1, 'Default', 'Default', 0)
GO

--
-- Dumping data for table LMA_ENTITY_LICENCE_POOL
--

INSERT INTO LMA_ENTITY_LICENCE_POOL (entity_licence_pool_id, entity_id, licence_type_id, number_of_licences) VALUES
(1, 1, 1, 0)
GO

-- --------------------------------------------------------
-- ********************************************************
-- ********************************************************
-- *** FUNCTIONS ***
-- ********************************************************
-- ********************************************************
-- --------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------
-- FUNCTIONS
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE FUNCTION BigToInt(@n BIGINT) RETURNS int as
BEGIN
RETURN @n
END
GO
-- --------------------------------------------------------
-- ********************************************************
-- ********************************************************
-- *** VIEWS ***
-- ********************************************************
-- ********************************************************
-- --------------------------------------------------------

-- ----------------------------------------------------------------------------------------------------------------------------------------------------------
-- VIEWS
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------
-- --------------------------------------------------------

-- --------------------------------------------------------
--
-- Structure for view LMA_V_COMPLIANCE_BY_ENTITY
--

create view LMA_V_COMPLIANCE_BY_ENTITY as

SELECT concat(e.entity_id, '|', t.licence_type_id) as rowId,
e.entity_name,
t.licence_type_name,
cast(ISNULL(lpuc.user_count, 0) as int) as allocated_licences,
cast(ISNULL(p.number_of_licences, 0) as int) as licences_available,
e.entity_id, t.licence_type_id, p.entity_licence_pool_id, lpuc.collection_run_id
FROM LMA_ENTITY e
join LMA_ENTITY_LICENCE_POOL p
on e.entity_id = p.entity_id
join LMA_LICENCE_TYPE t
on t.licence_type_id = p.licence_type_id
left join LMA_LICENCE_POOL_USER_COUNT lpuc
on p.entity_licence_pool_id = lpuc.entity_licence_pool_id
and lpuc.collection_run_id = (select max(collection_run_id) from LMA_COLLECTION_RUN)
GO

-- --------------------------------------------------------
--
-- Structure for view LMA_V_USERS_BY_ALLOCATED_ENTITY
--

create view LMA_V_USERS_BY_ALLOCATED_ENTITY as

SELECT ul.username,
(
SELECT MAX(l.login) AS last_login
FROM LMA_ENVIRONMENT_USER_LOGIN l
WHERE l.username = ul.username
) as last_login,
t.licence_type_name,
e.entity_name,
e.entity_id, t.licence_type_id, p.entity_licence_pool_id
FROM LMA_USER_ALLOCATED_LICENCE_POOL ul
left join LMA_ENTITY_LICENCE_POOL p
on p.entity_licence_pool_id = ul.entity_licence_pool_id
left join LMA_ENTITY e
on e.entity_id = p.entity_id
left join LMA_LICENCE_TYPE t
on t.licence_type_id = p.licence_type_id
GO

-- --------------------------------------------------------
--
-- Structure for view LMA_V_LICENCE_POOL_ENVIRONMENT_USERS
--

CREATE VIEW LMA_V_LICENCE_POOL_ENVIRONMENT_USERS AS

SELECT
DISTINCT
plp.username AS username,
-- plp.user_potential_licence_pool_id AS id,
lt.licence_type_id AS licence_type_id,
lt.licence_type_name AS licence_type_name,
env.environment_id AS environment_id,
env.environment_name AS environment_name,
(SELECT MAX(ll.login) FROM LMA_ENVIRONMENT_USER_LOGIN ll WHERE ll.username = plp.username) AS last_login
FROM LMA_USER_POTENTIAL_LICENCE_POOL plp
join LMA_ENTITY_LICENCE_POOL elp
on elp.entity_licence_pool_id = plp.entity_licence_pool_id
join LMA_LICENCE_TYPE lt
on lt.licence_type_id = elp.licence_type_id
join LMA_ENVIRONMENT_USER eu
on eu.username = plp.username
join LMA_ENVIRONMENT env
on env.environment_id = eu.environment_id
GO

-- --------------------------------------------------------
-- ********************************************************
-- ********************************************************
-- *** STORED PROCEDURES ***
-- ********************************************************
-- ********************************************************
-- --------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORED PROCEDURES
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
GO
CREATE PROCEDURE LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
AS
BEGIN

-- Work out everyone's allocated licence pool
INSERT INTO LMA_USER_ALLOCATED_LICENCE_POOL(username, entity_licence_pool_id)
SELECT alp.username, max(elp.entity_licence_pool_id) as entity_licence_pool_id
FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
JOIN LMA_ENTITY_LICENCE_POOL elp
on elp.entity_licence_pool_id=lp.entity_licence_pool_id
JOIN LMA_LICENCE_TYPE lt
on lt.licence_type_id=elp.licence_type_id
JOIN (
SELECT distinct lp.username,
max(lt.licence_type_value) as licence_type_value
FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
JOIN LMA_ENTITY_LICENCE_POOL elp
on elp.entity_licence_pool_id=lp.entity_licence_pool_id
JOIN LMA_LICENCE_TYPE lt
on lt.licence_type_id=elp.licence_type_id
group by lp.username
) alp on alp.licence_type_value=lt.licence_type_value and alp.username=lp.username
group by alp.username;

-- now update the list of groups where users have fallen into the default licence pool
insert into LMA_DEFAULT_LICENCE_POOL_GROUP (group_name)
select distinct(g.group_name)
from LMA_ENVIRONMENT_USER_GROUP g
where exists (
SELECT 1
FROM LMA_USER_ALLOCATED_LICENCE_POOL dp
WHERE dp.entity_licence_pool_id = 1
and dp.username = g.username
);

END
--
-- LMA_SP_CLEAN_UP_ENVIRONMENT_PACKAGE_DATA
-- clean up the data from the last files we uploaded
--
DROP PROCEDURE IF EXISTS LMA_R_SP_CLEAN_UP_STAGING_CSV_DATA
GO
CREATE PROCEDURE LMA_R_SP_CLEAN_UP_STAGING_CSV_DATA
AS
BEGIN

TRUNCATE TABLE LMA_STG_CSV_AUDITED_LOGINS

TRUNCATE TABLE LMA_STG_CSV_GROUP_MEMBERSHIP

END

GO

--
-- LMA_SP_CLEAN_UP_COLLECTION_RUN_DATA
-- clean up data for tables where we only want the latest collection data for analysis
--
DROP PROCEDURE IF EXISTS LMA_SP_CLEAN_UP_COLLECTION_RUN_DATA
GO

CREATE PROCEDURE LMA_SP_CLEAN_UP_COLLECTION_RUN_DATA
AS
BEGIN

TRUNCATE TABLE LMA_ENVIRONMENT_USER

TRUNCATE TABLE LMA_ENVIRONMENT_USER_GROUP

END

GO

--
-- LMA_SP_CLEAN_UP_STAGING_ANALYSIS_DATA
-- clean up the data for tables where we only want the latest analysis data for reporting
--
DROP PROCEDURE IF EXISTS LMA_SP_CLEAN_UP_STAGING_ANALYSIS_DATA
GO

CREATE PROCEDURE LMA_SP_CLEAN_UP_STAGING_ANALYSIS_DATA
AS
BEGIN

TRUNCATE TABLE LMA_USER_ALLOCATED_LICENCE_POOL
TRUNCATE TABLE LMA_USER_POTENTIAL_LICENCE_POOL

TRUNCATE TABLE LMA_DEFAULT_LICENCE_POOL_GROUP

END

GO
--
-- LMA_SP_CONSOLIDATE_GROUPS
--
DROP PROCEDURE IF EXISTS LMA_SP_CONSOLIDATE_GROUPS
GO

CREATE PROCEDURE LMA_SP_CONSOLIDATE_GROUPS(@environment_id INT)
AS
BEGIN
INSERT INTO LMA_GROUP (group_name, group_uuid, environment_id)
SELECT
DISTINCT
eg.group_name,
eg.group_uuid,
eg.environment_id
FROM LMA_ENVIRONMENT_USER_GROUP eg WHERE
eg.environment_id = environment_id
AND NOT EXISTS(
select 1
from LMA_GROUP g
where g.group_uuid = eg.group_uuid
and g.environment_id = environment_id
)
END

GO

--
-- LMA_SP_POPULATE_ENVIRONMENT_DATA
--
DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_ENVIRONMENT_DATA
GO

CREATE PROCEDURE LMA_SP_POPULATE_ENVIRONMENT_DATA(@collection_run_id INT, @environment_id INT)
AS
BEGIN

INSERT INTO LMA_ENVIRONMENT_USER(username, user_uuid, collection_run_id, environment_id)
SELECT distinct username,user_uuid, @collection_run_id, @environment_id
FROM LMA_STG_CSV_GROUP_MEMBERSHIP

-- Append new logins to existing set
INSERT INTO LMA_ENVIRONMENT_USER_LOGIN(username, login, collection_run_id, environment_id)
SELECT username,login, @collection_run_id, @environment_id
FROM LMA_STG_CSV_AUDITED_LOGINS

INSERT INTO LMA_ENVIRONMENT_USER_GROUP(username, user_uuid, group_name, group_uuid, collection_run_id, environment_id)
SELECT username,user_uuid, group_name,group_uuid, @collection_run_id, @environment_id
FROM LMA_STG_CSV_GROUP_MEMBERSHIP

END

GO

--
-- LMA_SP_POPULATE_LICENCE_POOL_USER_COUNT
--
DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_LICENCE_POOL_USER_COUNT
GO

CREATE PROCEDURE LMA_SP_POPULATE_LICENCE_POOL_USER_COUNT
@collection_id int = 0
AS
BEGIN
DELETE FROM LMA_LICENCE_POOL_USER_COUNT
WHERE collection_run_id=@collection_id

INSERT INTO LMA_LICENCE_POOL_USER_COUNT(entity_licence_pool_id, user_count, collection_run_id)
SELECT entity_licence_pool_id, count(user_allocated_licence_pool_id) as user_count, @collection_id
FROM LMA_USER_ALLOCATED_LICENCE_POOL
group by entity_licence_pool_id

END

GO

--
-- LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
--
DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
GO

CREATE PROCEDURE LMA_SP_POPULATE_USER_ALLOCATED_LICENCE_POOL
AS
BEGIN

-- Work out everyone's allocated licence pool
INSERT INTO LMA_USER_ALLOCATED_LICENCE_POOL(username, entity_licence_pool_id)
SELECT alp.username, max(elp.entity_licence_pool_id) as entity_licence_pool_id
FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
JO@LMA_ENTITY_LICENCE_POOL elp
on elp.entity_licence_pool_id=lp.entity_licence_pool_id
JO@LMA_LICENCE_TYPE lt
on lt.licence_type_id=elp.licence_type_id
JO@(
SELECT distinct lp.username,
max(lt.licence_type_value) as licence_type_value
FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
JO@LMA_ENTITY_LICENCE_POOL elp
on elp.entity_licence_pool_id=lp.entity_licence_pool_id
JO@LMA_LICENCE_TYPE lt
on lt.licence_type_id=elp.licence_type_id
group by lp.username
) alp on alp.licence_type_value=lt.licence_type_value and alp.username=lp.username
group by alp.username

-- now update the list of groups where users have fallen into the default licence pool
insert into LMA_DEFAULT_LICENCE_POOL_GROUP (group_name)
select distinct(g.group_name)
from LMA_ENVIRONMENT_USER_GROUP g
where exists (
SELECT 1
FROM LMA_USER_ALLOCATED_LICENCE_POOL dp
WHERE dp.entity_licence_pool_id = 1
and dp.username = g.username
)

END

GO
--
-- LMA_SP_POPULATE_USER_POTENTIAL_LICENCE_POOL
--
DROP PROCEDURE IF EXISTS LMA_SP_POPULATE_USER_POTENTIAL_LICENCE_POOL
GO
CREATE PROCEDURE LMA_SP_POPULATE_USER_POTENTIAL_LICENCE_POOL
@collection_run_id INT,
-- LMA-69
@LMA_R_BOOL_FLAT_APP_THRESHOLD_ENABLED tinyint,
@LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS varchar(500),
@LMA_R_INT_FLAT_APP_THRESHOLD_1_VALUE int,
@LMA_R_INT_FLAT_APP_THRESHOLD_1_EXCEEDED_LIC_POOL_ID int,
@LMA_R_INT_FLAT_APP_THRESHOLD_2_VALUE int,
@LMA_R_INT_FLAT_APP_THRESHOLD_2_EXCEEDED_LIC_POOL_ID int

AS
BEGIN

INSERT INTO LMA_USER_POTENTIAL_LICENCE_POOL(username, entity_licence_pool_id)
SELECT distinct ug.username, ISNULL(lpg.entity_licence_pool_id, 1)
FROM LMA_ENVIRONMENT_USER_GROUP ug
left join LMA_GROUP g
on g.group_uuid = ug.group_uuid
left join LMA_ENTITY_LICENCE_POOL_GROUP lpg
on lpg.group_id = g.group_id

-- LMA-69 Support for Multiple Application License vs Enterprise License
IF ISNULL(@LMA_R_BOOL_FLAT_APP_THRESHOLD_ENABLED, 0) = 1
BEGIN
-- Add a potential licence pool for users over thresholds flat app,
-- where they don't already have this potential licence pool

-- Flat Apps - Process First Threshold
INSERT INTO LMA_USER_POTENTIAL_LICENCE_POOL (entity_licence_pool_id, username)
SELECT @LMA_R_INT_FLAT_APP_THRESHOLD_1_EXCEEDED_LIC_POOL_ID, lp.username
FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
-- Where this licence pool uses a Flat App licence type
WHERE EXISTS(
SELECT 1
FROM LMA_ENTITY_LICENCE_POOL flatAppPools
-- Where licence type is @scope as a Flat App
WHERE flatAppPools.licence_type_id in (select value from string_split(@LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS,','))
-- And it's used @this potential licence pool
AND flatAppPools.entity_licence_pool_id = lp.entity_licence_pool_id
)
-- And they aren't already @the exceeds licence pool
AND NOT EXISTS(
SELECT 1
FROM LMA_USER_POTENTIAL_LICENCE_POOL plp2
WHERE plp2.username=lp.username AND plp2.entity_licence_pool_id = @LMA_R_INT_FLAT_APP_THRESHOLD_1_EXCEEDED_LIC_POOL_ID
)
GROUP BY lp.username
HAVING count(lp.entity_licence_pool_id) > @LMA_R_INT_FLAT_APP_THRESHOLD_1_VALUE
AND count(lp.entity_licence_pool_id) <= @LMA_R_INT_FLAT_APP_THRESHOLD_2_VALUE

-- Flat Apps - Process Second Threshold
INSERT INTO LMA_USER_POTENTIAL_LICENCE_POOL (entity_licence_pool_id, username)
SELECT @LMA_R_INT_FLAT_APP_THRESHOLD_2_EXCEEDED_LIC_POOL_ID, lp.username
FROM LMA_USER_POTENTIAL_LICENCE_POOL lp
-- Where this licence pool uses a Flat App licence type
WHERE EXISTS(
SELECT 1
FROM LMA_ENTITY_LICENCE_POOL flatAppPools
-- Where licence type is @scope as a Flat App
--WHERE FIND_IN_SET(flatAppPools.licence_type_id, @LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS)
WHERE flatAppPools.licence_type_id in (select value from string_split(@LMA_R_INT_FLAT_APP_LICENCE_TYPE_IDS,','))
-- And it's used @this potential licence pool
AND flatAppPools.entity_licence_pool_id = lp.entity_licence_pool_id
)
-- And they aren't already @the exceeds licence pool
AND NOT EXISTS(
SELECT 1
FROM LMA_USER_POTENTIAL_LICENCE_POOL plp2
WHERE plp2.username=lp.username AND plp2.entity_licence_pool_id = @LMA_R_INT_FLAT_APP_THRESHOLD_2_EXCEEDED_LIC_POOL_ID
)
GROUP BY lp.username
HAVING count(lp.entity_licence_pool_id) > @LMA_R_INT_FLAT_APP_THRESHOLD_2_VALUE
END

END

DROP PROCEDURE IF EXISTS LMA_R_SP_USER_LICENCE_POOL_MEMBERSHIP
GO

CREATE PROCEDURE LMA_R_SP_USER_LICENCE_POOL_MEMBERSHIP(@username varchar(255))
AS
BEGIN

SELECT DISTINCT
ul.user_potential_licence_pool_id AS id,
ul.username AS username,
(SELECT MAX(ll.login) FROM LMA_ENVIRONMENT_USER_LOG@ll WHERE ll.username = ul.username) AS lastLogin,
lt.licence_type_id AS licenceTypeId,
lt.licence_type_name AS licenceTypeName,
lt.licence_type_value AS licenceTypeValue,
en.entity_id AS entityId,
en.entity_name AS entityName,
CASE WHEN alp.entity_licence_pool_id IS NULL then 0 ELSE 1 END AS isAllocated
FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
LEFT join LMA_USER_ALLOCATED_LICENCE_POOL alp
ON alp.entity_licence_pool_id = ul.entity_licence_pool_id
AND alp.username = ul.username
join LMA_ENTITY_LICENCE_POOL lp
ON lp.entity_licence_pool_id = ul.entity_licence_pool_id
LEFT join LMA_LICENCE_TYPE lt
ON lt.licence_type_id = lp.licence_type_id
join LMA_ENTITY en
ON en.entity_id = lp.entity_id
where ul.username = @username

END

GO

END$$
DELIMITER
GO

DROP PROCEDURE IF EXISTS LMA_R_SP_USER_LOGINS_OVER_TIME
GO

CREATE PROCEDURE LMA_R_SP_USER_LOGINS_OVER_TIME(@username varchar(255))
AS
BEGIN

SELECT cast(ul.login as date) as loginDate, count(ul.Environment_user_login_id) as loginCount
FROM LMA_ENVIRONMENT_USER_LOGin ul
WHERE ul.username=username
group by cast(ul.login as date)

END

GO

--
-- LMA_SP_LICENCE_POOL_THRESHOLD_COUNTS
--
DROP PROCEDURE IF EXISTS LMA_SP_LICENCE_POOL_THRESHOLD_COUNTS
GO

CREATE PROCEDURE LMA_SP_LICENCE_POOL_THRESHOLD_COUNTS(
@entityLicencePoolId INT,
@threshold INT,
@periodDays INT
)
AS
BEGIN

SELECT sum(case when tbl.loginCount > @threshold then 1 else 0 end) as aboveUsers,
sum(case when tbl.loginCount <= @threshold then 1 else 0 end) as withinUsers
FROM (
SELECT username, count(login) as loginCount
FROM LMA_ENVIRONMENT_USER_LOGin eul
WHERE EXISTS (
SELECT 1
FROM LMA_USER_ALLOCATED_LICENCE_POOL alp
WHERE alp.entity_licence_pool_id = @entityLicencePoolId
AND alp.username = eul.username
)
AND login > DATEADD(DAY,@periodDays*-1,CAST(GETDATE() AS DATE))
GROUP BY username
) tbl

END

GO

END$$
DELIMITER
GO

--
-- LMA_SP_LICENCE_POOL_THRESHOLD_USERS
--
DROP PROCEDURE IF EXISTS LMA_SP_LICENCE_POOL_THRESHOLD_USERS
GO

CREATE PROCEDURE LMA_SP_LICENCE_POOL_THRESHOLD_USERS(
/* Paging and sorting filters */
@p_limit int,
@p_offset int,
@p_orderBy varchar(255),
@p_orderByDirection varchar(4),
@p_isTotalCount tinyint,
/* Standard filters */
@p_entityLicencePoolId INT,
@p_threshold INT,
@p_periodDays INT,
@p_segment VARCHAR(250)
)
AS
BEGIN

IF @p_isTotalCount = 1

SELECT count(*) as totalCount
FROM (
SELECT username, count(login) as loginCount
FROM LMA_ENVIRONMENT_USER_LOGin eul
WHERE EXISTS (
SELECT 1
FROM LMA_USER_ALLOCATED_LICENCE_POOL alp
WHERE alp.entity_licence_pool_id = @p_entityLicencePoolId
AND alp.username = eul.username
)
AND login> DATEADD(DAY,@p_periodDays*-1,CAST(GETDATE() AS DATE))
GROUP BY username
) tbl
WHERE (
@p_segment = 'ABOVE'
AND
loginCount > @p_threshold
)
OR
(
@p_segment = 'WITHIN'
AND
loginCount <= @p_threshold
)

ELSE
SELECT username,
loginCount
FROM (
SELECT username, count(login) as loginCount
FROM LMA_ENVIRONMENT_USER_LOGin eul
WHERE EXISTS (
SELECT 1
FROM LMA_USER_ALLOCATED_LICENCE_POOL alp
WHERE alp.entity_licence_pool_id = @p_entityLicencePoolId
AND alp.username = eul.username
)
AND login> DATEADD(DAY,@p_periodDays*-1,CAST(GETDATE() AS DATE))
GROUP BY username
) tbl
WHERE (
@p_segment = 'ABOVE'
AND
loginCount > @p_threshold
)
OR
(
@p_segment = 'WITHIN'
AND
loginCount <= @p_threshold
)
/* Custom sorting - ensure that the column names @text below match the field names that are used by the grid for sorting */
ORDER BY

(case when @p_orderBy = 'username' and @p_orderByDirection = 'asc' then username end) asc,
(case when @p_orderBy = 'username' and @p_orderByDirection = 'desc' then username end) desc,

(case when @p_orderBy = 'loginCount' and @p_orderByDirection = 'asc' then loginCount end) asc,
(case when @p_orderBy = 'loginCount' and @p_orderByDirection = 'desc' then loginCount end) desc

/* Custom paging - the wrapper rule ensures the correct values are passed in, including allowing for the fact that OFFSET @MySQL is zero based and startIndex @Appian is 1 based */
OFFSET @p_offset ROWS FETCH NEXT @p_limit ROWS ONLY

END
GO

--
-- LMA_SP_USERS_BY_LICENCE_TYPE
--
DROP PROCEDURE IF EXISTS LMA_SP_USERS_BY_LICENCE_TYPE
GO

CREATE PROCEDURE LMA_SP_USERS_BY_LICENCE_TYPE(
/* Paging and sorting filters */
@p_limit int,
@p_offset int,
@p_orderBy varchar(255),
@p_orderByDirection varchar(4),
@p_isTotalCount tinyint,
/* Standard filters */
@p_licenceTypeId INT,
@p_username VARCHAR(255)
)
AS
BEGIN

IF @p_isTotalCount = 1

SELECT count(distinct username) as totalCount
FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
WHERE (
ISNULL(@p_licenceTypeId,'') = ''
OR
EXISTS (
SELECT 1
FROM LMA_ENTITY_LICENCE_POOL lp
WHERE lp.licence_type_id = @p_licenceTypeId
AND lp.entity_licence_pool_id = ul.entity_licence_pool_id
)
)
AND
(
ISNULL(@p_username,'') = ''
OR
ul.username = @p_username
)

ELSE
SELECT ul.username AS username,
(SELECT MAX(ll.login) FROM LMA_ENVIRONMENT_USER_LOG@ll WHERE ll.username = ul.username) AS lastLogin
FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
WHERE (
ISNULL(@p_licenceTypeId,'') = ''
OR
EXISTS (
SELECT 1
FROM LMA_ENTITY_LICENCE_POOL lp
WHERE lp.licence_type_id = @p_licenceTypeId
AND lp.entity_licence_pool_id = ul.entity_licence_pool_id
)
)
AND
(
ISNULL(@p_username,'') = ''
OR
ul.username = @p_username
)
GROUP BY username

/* Custom sorting - ensure that the column names @text below match the field names that are used by the grid for sorting */
ORDER BY

(case when @p_orderBy = 'username' and @p_orderByDirection = 'asc' then username end) asc,
(case when @p_orderBy = 'username' and @p_orderByDirection = 'desc' then username end) desc

/* Custom paging - the wrapper rule ensures the correct values are passed in, including allowing for the fact that OFFSET @MySQL is zero based and startIndex @Appian is 1 based */
OFFSET @p_offset ROWS FETCH NEXT @p_limit ROWS ONLY

END
GO

--
-- LMA_SP_USER_LICENCE_POOLS
--
DROP PROCEDURE IF EXISTS LMA_SP_USER_LICENCE_POOLS
GO

CREATE PROCEDURE LMA_SP_USER_LICENCE_POOLS(
@p_usernameList VARCHAR(1000)
)
AS
BEGIN

SELECT ul.username AS username,
lt.licence_type_name AS licenceTypeName,
en.entity_name AS entityName,
CASE WHEN alp.entity_licence_pool_id is null then 0 ELSE 1 END AS isAllocated

FROM LMA_USER_POTENTIAL_LICENCE_POOL ul
LEFT join LMA_USER_ALLOCATED_LICENCE_POOL alp
ON alp.entity_licence_pool_id = ul.entity_licence_pool_id
AND alp.username = ul.username
join LMA_ENTITY_LICENCE_POOL lp
ON lp.entity_licence_pool_id = ul.entity_licence_pool_id
join LMA_LICENCE_TYPE lt
ON lt.licence_type_id = lp.licence_type_id
join LMA_ENTITY en
ON en.entity_id = lp.entity_id

--WHERE FIND_IN_SET(ul.username, p_usernameList)
WHERE ul.username in (select value from string_split(@p_usernameList,','))

END
GO

-- LMA-36 / LMA-72 - delete unused licence pools / business entities

--
-- LMA_SP_IS_RECORD_IN_USE
-- Utility to identify if a certain record is in use
--
DROP PROCEDURE IF EXISTS LMA_SP_IS_RECORD_IN_USE
GO
CREATE PROCEDURE LMA_SP_IS_RECORD_IN_USE(
@recordType VARCHAR(255),
@recordId int
)
AS
BEGIN

-- LMA-72
IF @recordType = 'LMA_ENTITY_LICENCE_POOL'
SELECT
CASE
WHEN sum(tbl.rowsCount) > 0 then 1
ELSE 0
END as licPoolInUse
FROM (
SELECT count(1) as rowsCount FROM LMA_LICENCE_POOL_USER_COUNT WHERE entity_licence_pool_id=@recordId
UNION
SELECT count(1) as rowsCount FROM LMA_USER_ALLOCATED_LICENCE_POOL WHERE entity_licence_pool_id=@recordId
UNION
SELECT count(1) as rowsCount FROM LMA_USER_POTENTIAL_LICENCE_POOL WHERE entity_licence_pool_id=@recordId
) tbl

-- LMA-36
ELSE IF @recordType = 'LMA_ENTITY'
SELECT
CASE
WHEN count(1) > 0 then 1
ELSE 0
END as licPoolInUse
FROM LMA_ENTITY_LICENCE_POOL
WHERE entity_id = @recordId

END
GO