usp_GetLicenses.sql
9.11 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
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetLicenses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetLicenses]
GO
-- ====================================================
-- Author: Magic Software
-- Create date: 22-Jan-2018
-- Description: To get all the licenses based on criteria
-- ====================================================
create PROCEDURE [dbo].[usp_GetLicenses]
-- 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
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;
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)
order by CreationDate
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO