usp_GetLicenseUserGroupUsers.sql
4.56 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetLicenseUserGroupUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetLicenseUserGroupUsers]
GO
-- ====================================================
-- Author: Magic Software
-- Create date: 20-Mar-2018
-- Description: To get all user group users with all users of a license
-- ====================================================
create PROCEDURE [dbo].[usp_GetLicenseUserGroupUsers]
-- Add the parameters for the stored procedure here
@licenseId int, @groupId int, @allUsers bit, @pageNo int, @pageLength int, @recordCount int out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if (@AllUsers = 1)
begin
--Get the records on the basis of parameters page length and page number rows
select * from
(SELECT row_number() OVER (order by AIAUser.Id) AS RowNo, AIAUser.Id, AIAUser.FirstName, AIAUser.LastName, AIAUser.LoginId,
AIAUser.EmailId, Edition.Title,
(CASE WHEN UserGroupToAIAUser.UserId IS NULL THEN 0 ELSE 1 END) AS InGroup
FROM
AIAUser
INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
INNER JOIN License ON LicenseToEdition.LicenseId = License.Id AND License.IsActive = 1
LEFT JOIN UserGroupToAIAUser ON UserGroupToAIAUser.UserId = AIAUser.Id
AND UserGroupToAIAUser.UserGroupId = @groupId
WHERE License.Id = @licenseId AND AIAUser.UserTypeId = 6
) TB
WHERE RowNo > @pageLength * (@pageNo - 1) and RowNo <= @pageLength * @pageNo;
--Calculate total number of records
select @recordCount = count(ResultTable.Id) from (
SELECT AIAUser.Id, AIAUser.FirstName, AIAUser.LastName, AIAUser.LoginId,
AIAUser.EmailId, Edition.Title,
(CASE WHEN UserGroupToAIAUser.UserId IS NULL THEN 0 ELSE 1 END) AS InGroup
FROM
AIAUser
INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
INNER JOIN License ON LicenseToEdition.LicenseId = License.Id AND License.IsActive = 1
LEFT JOIN UserGroupToAIAUser ON UserGroupToAIAUser.UserId = AIAUser.Id
AND UserGroupToAIAUser.UserGroupId = @groupId
WHERE License.Id = @licenseId AND AIAUser.UserTypeId = 6) as ResultTable;
end
else
begin
--Get the records on the basis of parameters page length and page number rows
select * from
(SELECT row_number() OVER (order by AIAUser.Id) AS RowNo, AIAUser.Id, AIAUser.FirstName, AIAUser.LastName, AIAUser.LoginId,
AIAUser.EmailId, Edition.Title,
(CASE WHEN UserGroupToAIAUser.UserId IS NULL THEN 0 ELSE 1 END) AS InGroup
FROM
AIAUser
INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
INNER JOIN License ON LicenseToEdition.LicenseId = License.Id AND License.IsActive = 1
LEFT JOIN UserGroupToAIAUser ON UserGroupToAIAUser.UserId = AIAUser.Id
AND UserGroupToAIAUser.UserGroupId = @groupId
WHERE License.Id = @licenseId AND AIAUser.UserTypeId = 6 and UserGroupToAIAUser.UserId > 0
) TB
WHERE RowNo > @pageLength * (@pageNo - 1) and RowNo <= @pageLength * @pageNo;
--Calculate total number of records
select @recordCount = count(ResultTable.Id) from (
SELECT AIAUser.Id, AIAUser.FirstName, AIAUser.LastName, AIAUser.LoginId,
AIAUser.EmailId, Edition.Title,
(CASE WHEN UserGroupToAIAUser.UserId IS NULL THEN 0 ELSE 1 END) AS InGroup
FROM
AIAUser
INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
INNER JOIN License ON LicenseToEdition.LicenseId = License.Id AND License.IsActive = 1
LEFT JOIN UserGroupToAIAUser ON UserGroupToAIAUser.UserId = AIAUser.Id
AND UserGroupToAIAUser.UserGroupId = @groupId
WHERE License.Id = @licenseId AND AIAUser.UserTypeId = 6 and UserGroupToAIAUser.UserId > 0) as ResultTable;
end
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO