dbo.GetCustomerSummary_25042017.StoredProcedure.sql
15.2 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
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetCustomerSummary_25042017] Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCustomerSummary_25042017]
-- Add the parameters for the stored procedure here
@sAccoutNumber varchar(50)='', @sLicenseeFullName varchar(100)='', @iStartPrice numeric(14,2), @iEndPrice numeric(14,2),
@iLicenseType tinyint, @iAccountType tinyint, @sZip varchar(20) = '', @iState int,
@iCountry int
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON
DECLARE @cGetLicenseDetails CURSOR
DECLARE @iLicenseId INT
DECLARE @sAccountNumber VARCHAR(50)
DECLARE @sLicenseeName VARCHAR(100)
DECLARE @iLicenseTypeId TINYINT
DECLARE @sLicenseType VARCHAR(50)
DECLARE @dtStartDate DATETIME
DECLARE @dtEndDate DATETIME
DECLARE @sAccountType VARCHAR(50)
DECLARE @iAccountTypeId TINYINT
DECLARE @sLicenseStatus VARCHAR(8)
DECLARE @sEdition VARCHAR(200)
DECLARE @bExists bit
DECLARE @sLicenseState VARCHAR(50)
DECLARE @sLicenseZip VARCHAR(20)
DECLARE @sLicenseCountry VARCHAR(50)
DECLARE @sInstitutionName VARCHAR(100)
DECLARE @dtLicenseCreationDate DATETIME
DECLARE @mSubscriptionPrice MONEY
DECLARE @iLicenseSubscriptionId INT
DECLARE @sEmailId VARCHAR(100)
DECLARE @iCardNumber INT
-- create a temporary table to store the desired results of licenses on the basis of parameter
CREATE TABLE #CustomerReport
(
AccountNumber VARCHAR(50),
LicenseeName VARCHAR(100),
LicenseType VARCHAR(50),
Edition VARCHAR(200),
Email VARCHAR(100),
ValidFrom DATETIME,
ValidThrough DATETIME,
AccountType VARCHAR(50),
LicenseStatus VARCHAR(8),
Price MONEY,
LicenseState VARCHAR(50),
LicenseZip VARCHAR(20),
LicenseCountry VARCHAR(50),
InstitutionName VARCHAR(100),
LicenseCreationDate DATETIME,
CardNumber INT
)
SET @sLicenseeFullName = REPLACE(@sLicenseeFullName,' ',' OR ')
-- define the forward only, read-only cursor
SET @cGetLicenseDetails = CURSOR FAST_FORWARD
FOR
SELECT License.Id, License.AccountNumber, (License.LicenseeFirstName+' '+License.LicenseeLastName),
License.LicenseTypeId, License.AccountTypeId, License.EmailId,
(CASE License.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
State.StateName, License.Zip, Country.CountryName,
License.InstitutionName,License.CreationDate,
(CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
FROM License WITH (NOLOCK)
INNER JOIN State WITH (NOLOCK) ON License.StateId = State.Id
INNER JOIN Country WITH (NOLOCK) ON License.CountryId = Country.Id
WHERE
License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber END)
AND License.LicenseTypeId = (CASE WHEN @iLicenseType > 0 THEN @iLicenseType ELSE License.LicenseTypeId END)
AND License.AccountTypeId = (CASE WHEN @iAccountType > 0 THEN @iAccountType ELSE License.AccountTypeId END)
AND State.Id = (CASE WHEN @iState > 0 THEN @iState ELSE State.Id END)
AND Country.Id = (CASE WHEN @iCountry > 0 THEN @iCountry ELSE Country.Id END)
AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
-- open & fetch the cursor variables into the local variables
OPEN @cGetLicenseDetails
FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName,
@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber
-- start of while loop
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sEdition = ''
SET @bExists = 1
-- fetch the latest license start/end date of a license on the basis of Subscription Start & End price if any
SELECT @dtStartDate = MAX(LicenseSubscriptionDetail.SubscriptionValidFrom),
@dtEndDate = MAX(LicenseSubscriptionDetail.SubscriptionValidThrough),
@iLicenseSubscriptionId = MAX(LicenseSubscriptionDetail.Id)
FROM LicenseSubscriptionDetail WITH (NOLOCK)
WHERE LicenseSubscriptionDetail.LicenseId = @iLicenseId
AND (TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END))
AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END))
GROUP BY LicenseSubscriptionDetail.LicenseId
-- check whether the above query returns any row
IF @@Rowcount > 0
BEGIN
-- check whether the name of licensse matches the name entered by user
IF LEN(@sLicenseeFullName) > 0
BEGIN
SELECT @bExists = 1
FROM License WITH (NOLOCK)
WHERE Id = @iLicenseId AND (LicenseeFirstName LIKE '%'+@sLicenseeFullName+'%' OR LicenseeLastName LIKE '%'+@sLicenseeFullName+'%') --CONTAINS((LicenseeFirstName,LicenseeLastName)
IF @@Rowcount = 0
BEGIN
SET @bExists = 0
END
END
-- check whether the above query returns any row
IF @bExists = 1
BEGIN
-- fetch the licensetype of the license
SELECT @sLicenseType = LicenseType.Title FROM LicenseType WITH (NOLOCK)
WHERE LicenseType.Id = @iLicenseTypeId
-- fetch the accounttype of the license
SELECT @sAccountType = AccountType.Title FROM AccountType WITH (NOLOCK)
WHERE AccountType.Id = @iAccountTypeId
-- fetch all the editions mapped as a string with a license
SELECT @sEdition = Edition.Title + '; ' + @sEdition
FROM LicenseToEdition WITH (NOLOCK) INNER JOIN Edition WITH (NOLOCK)
ON LicenseToEdition.EditionId = Edition.Id
WHERE LicenseToEdition.LicenseId = @iLicenseId
IF LEN(@sEdition)> 1
-- remove the trailing comma-separator from the edition-string
SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
ELSE
SET @sEdition = @sEdition
-- fetch the price of the license
SELECT @mSubscriptionPrice = TotalAmount FROM LicenseSubscriptionDetail WITH (NOLOCK)
WHERE Id = @iLicenseSubscriptionId
-- insert into the temporary table
INSERT INTO #CustomerReport
(AccountNumber, LicenseeName, LicenseType, Edition, Email, ValidFrom, ValidThrough, AccountType, LicenseStatus, Price, LicenseState, LicenseZip, LicenseCountry, InstitutionName, LicenseCreationDate, CardNumber)
VALUES(@sAccountNumber, @sLicenseeName, @sLicenseType, @sEdition, @sEmailId, @dtStartDate, @dtEndDate, @sAccountType, @sLicenseStatus, @mSubscriptionPrice, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber)
END
END
-- fetch the next record from cursor
FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName,
@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry, @sInstitutionName, @dtLicenseCreationDate, @iCardNumber
-- end of while loop
END
-- close the cursor to free up resources
CLOSE @cGetLicenseDetails
DEALLOCATE @cGetLicenseDetails
-- Selecting the desired result from temporary table
SELECT AccountNumber, LicenseeName, LicenseType, AccountType, Edition, Email, CONVERT(VARCHAR,ValidFrom,101) as StartDate,
CONVERT(VARCHAR,ValidThrough,101) as EndDate, LicenseStatus, CONVERT(NUMERIC(14,2),Price) as Price,
LicenseZip, LicenseState, LicenseCountry,InstitutionName, CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate , CardNumber
FROM #CustomerReport WITH (NOLOCK) ORDER BY AccountNumber
-- Dropping the temporary table
DROP TABLE #CustomerReport
END
GO