dbo.usp_GetlicensesList.sql
16.6 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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
-- ====================================================
-- Author: Magic Software
-- Create date: 22-Jan-2018
-- Description: To get all the licenses based on criteria
-- ====================================================
alter PROCEDURE [dbo].[usp_GetlicensesList]
-- Add the parameters for the stored procedure here
@sStartDate varchar(20) = '', @sEndDate varchar(20) = '', @sAccoutNumber varchar(50)='',
@sLicenseeFirstName varchar(50)='', @sLicenseeLastName varchar(50)='', @iLicenseTypeId tinyint,
@sInstituteName varchar(100) = '', @sEmail varchar(50) = '', @iStateId int, @iCountryId int,
@bisActive bit = 1, @pageNo int, @pageLength int, @recordCount int out
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Get the records on the basis of parameters page length and page number rows
select
LD.id as LicenseId,
LD.AccountNumber,
LD.LicenseType,
LD.AccountType,
LD.InstitutionName,
LD.StateName as LicenseState,
LD.CountryName as LicenseCountry,
LD.EmailId,
LD.CardNumber,
LD.ProductId as ProductKey,
(case
when len(LD.AdminName)>=990
then left(LD.AdminName, 990) + '...'
else LD.AdminName end)
as ClientAdmin,
(LD.LicenseeFirstName + ' ' + LD.LicenseeLastName) as LicenseeName,
(ISNULL(LD.Address1,'')+' '+ ISNULL(LD.Address2,'')+' ' +ISNULL(LD.City,'')) as ContactAddress,
CONVERT(VARCHAR,LD.CreationDate,101) as EntryDate,
(CASE LD.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
ISNULL(CONVERT(VARCHAR,LD.ModifiedDate,101),'') as ModifyDate,
CONVERT(VARCHAR,LD.SubscriptionStartDate,101) as StartDate,
ISNULL(CONVERT(VARCHAR,LD.SubscriptionRenewalDate,101),'') as RenewDate,
CONVERT(VARCHAR,LD.SubscriptionEndDate,101) as EndDate,
LD.NoofImages, LD.RowNo
from
(Select ROW_NUMBER() OVER (ORDER BY L.id) AS RowNo,
L.id,
L.AccountNumber,
L.LicenseeFirstName,
L.LicenseeLastName,
L.LicenseTypeId,
LT.Title as LicenseType,
A.Title as AccountType,
L.InstitutionName,
L.Address1,
L.Address2,
L.City,
L.StateId,
S.StateName,
L.CountryId,
C.CountryName,
L.EmailId,
L.CreationDate,
L.IsActive,
L.ModifiedDate,
L.CardNumber,
L.ProductId,
STUFF((Select ',' + AIAUser.FirstName + ' ' + AIAUser.LastName
FROM LicenseToEdition
INNER JOIN AIAUserToLicenseEdition ON LicenseToEdition.Id = AIAUserToLicenseEdition.LicenseEditionId
INNER JOIN AIAUser ON AIAUserToLicenseEdition.UserId = AIAUser.Id
INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
WHERE LicenseToEdition.LicenseId = L.Id AND AIAUser.IsActive = 1
AND UserType.Title in ('District Admin','Client Admin','Single User','Reseller')
FOR XML PATH ('')), 1, 1, '') AS AdminName,
(SELECT MAX(lsd.SubscriptionValidFrom) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionStartDate,
(SELECT MAX(lsd.RenewalDate) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionRenewalDate,
(SELECT MAX(lsd.SubscriptionValidThrough) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionEndDate,
(SELECT TOP(1) lsd.NoofImages FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id order by lsd.SubscriptionValidFrom desc) AS NoofImages
from License L
inner join LicenseType LT on L.LicenseTypeId = LT.Id
inner join AccountType A on L.AccountTypeId=A.Id
inner join State S on L.StateId =S.Id
inner join Country C on L.CountryId = C.Id
where
((LEN(@sStartDate)=0) OR ((SELECT MAX(lsd.SubscriptionValidFrom) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) >= (CONVERT(DATETIME,@sStartDate)))) AND
((LEN(@sEndDate)=0) OR ((SELECT MAX(lsd.SubscriptionValidThrough) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) <= (DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))))) AND
((LEN(@sAccoutNumber)=0) OR (L.AccountNumber LIKE '%'+@sAccoutNumber+'%')) AND
((LEN(@sLicenseeFirstName)=0) OR (L.LicenseeFirstName LIKE '%'+@sLicenseeFirstName+'%')) AND
((LEN(@sLicenseeLastName)=0) OR (L.LicenseeLastName LIKE '%'+@sLicenseeLastName+'%')) AND
((LEN(@sInstituteName)=0) OR (L.InstitutionName LIKE '%'+@sInstituteName+ '%')) AND
((@iLicenseTypeId = 0) OR (L.LicenseTypeId = @iLicenseTypeId)) AND
((LEN(@sEmail)=0) OR (L.EmailId = @sEmail)) AND
((@iStateId =0) OR (L.StateId = @iStateId)) AND
((@iCountryId =0) OR (L.CountryId = @iCountryId)) AND
(L.isActive = @bisActive)
) as LD
where
RowNo > @pageLength * (@pageNo - 1) AND
RowNo <= @pageLength * @pageNo
order by creationdate
--Calculate total number of records
select @recordCount = count(ResultTable.LicenseId) from (
select
LD.id as LicenseId,
LD.AccountNumber,
LD.LicenseType,
LD.AccountType,
LD.InstitutionName,
LD.StateName as LicenseState,
LD.CountryName as LicenseCountry,
LD.EmailId,
LD.CardNumber,
LD.ProductId as ProductKey,
(case
when len(LD.AdminName)>=990
then left(LD.AdminName, 990) + '...'
else LD.AdminName end)
as ClientAdmin,
(LD.LicenseeFirstName + ' ' + LD.LicenseeLastName) as LicenseeName,
(ISNULL(LD.Address1,'')+' '+ ISNULL(LD.Address2,'')+' ' +ISNULL(LD.City,'')) as ContactAddress,
CONVERT(VARCHAR,LD.CreationDate,101) as EntryDate,
(CASE LD.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
ISNULL(CONVERT(VARCHAR,LD.ModifiedDate,101),'') as ModifyDate,
CONVERT(VARCHAR,LD.SubscriptionStartDate,101) as StartDate,
ISNULL(CONVERT(VARCHAR,LD.SubscriptionRenewalDate,101),'') as RenewDate,
CONVERT(VARCHAR,LD.SubscriptionEndDate,101) as EndDate,
LD.NoofImages
from
(Select
L.id,
L.AccountNumber,
L.LicenseeFirstName,
L.LicenseeLastName,
L.LicenseTypeId,
LT.Title as LicenseType,
A.Title as AccountType,
L.InstitutionName,
L.Address1,
L.Address2,
L.City,
L.StateId,
S.StateName,
L.CountryId,
C.CountryName,
L.EmailId,
L.CreationDate,
L.IsActive,
L.ModifiedDate,
L.CardNumber,
L.ProductId,
STUFF((Select ',' + AIAUser.FirstName + ' ' + AIAUser.LastName
FROM LicenseToEdition
INNER JOIN AIAUserToLicenseEdition ON LicenseToEdition.Id = AIAUserToLicenseEdition.LicenseEditionId
INNER JOIN AIAUser ON AIAUserToLicenseEdition.UserId = AIAUser.Id
INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
WHERE LicenseToEdition.LicenseId = L.Id AND AIAUser.IsActive = 1
AND UserType.Title in ('District Admin','Client Admin','Single User','Reseller')
FOR XML PATH ('')), 1, 1, '') AS AdminName,
(SELECT MAX(lsd.SubscriptionValidFrom) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionStartDate,
(SELECT MAX(lsd.RenewalDate) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionRenewalDate,
(SELECT MAX(lsd.SubscriptionValidThrough) FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionEndDate,
(SELECT TOP(1) lsd.NoofImages FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id order by lsd.SubscriptionValidFrom desc) AS NoofImages
from License L
inner join LicenseType LT on L.LicenseTypeId = LT.Id
inner join AccountType A on L.AccountTypeId=A.Id
inner join State S on L.StateId =S.Id
inner join Country C on L.CountryId = C.Id)
as LD
where
((LEN(@sStartDate)=0) OR (SubscriptionStartDate >= (CONVERT(DATETIME,@sStartDate)))) AND
((LEN(@sEndDate)=0) OR (SubscriptionEndDate <= (DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))))) AND
((LEN(@sAccoutNumber)=0) OR (AccountNumber LIKE '%'+@sAccoutNumber+'%')) AND
((LEN(@sLicenseeFirstName)=0) OR (LicenseeFirstName LIKE '%'+@sLicenseeFirstName+'%')) AND
((LEN(@sLicenseeLastName)=0) OR (LicenseeLastName LIKE '%'+@sLicenseeLastName+'%')) AND
((LEN(@sInstituteName)=0) OR (InstitutionName LIKE '%'+@sInstituteName+ '%')) AND
((@iLicenseTypeId = 0) OR (LicenseTypeId = @iLicenseTypeId)) AND
((LEN(@sEmail)=0) OR (EmailId = @sEmail)) AND
((@iStateId =0) OR (StateId = @iStateId)) AND
((@iCountryId =0) OR (CountryId = @iCountryId)) AND
(isActive = @bisActive)) as ResultTable;
END