· 8 years ago · Dec 04, 2017, 03:02 PM
1IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PV_IRWebGetNewUsersList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
2DROP PROCEDURE [dbo].PV_IRWebGetNewUsersList
3GO
4PRINT 'Creating procedure PV_IRWebGetNewUsersList';
5GO
6CREATE PROCEDURE [dbo].PV_IRWebGetNewUsersList
7 @ClientID BIGINT = NULL,
8 @SearchValue NVARCHAR(100) = NULL,
9 @Mode TINYINT,
10 @Company NVARCHAR(200) = NULL,
11 @ZipCode NVARCHAR(200) = NULL,
12 @City NVARCHAR(200) = NULL,
13 @StateID INT = NULL,
14 @WithoutCompany BIT = NULL,
15 @NotVerified BIT = NULL,
16 @SortField VARCHAR(100) = 'Name',
17 @SortDirection VARCHAR(5) = 'ASC',
18 @PageSize INT = 10,
19 @PageIndex BIGINT = 1,
20 @RecordCount BIGINT OUTPUT
21AS
22BEGIN
23 CREATE TABLE #Results
24 (
25 ID BIGINT
26 , Name NVARCHAR(200)
27 , CompanyName NVARCHAR(100)
28 , IntellirentCompany NVARCHAR(100)
29 , UserIntellirentCompanies NVARCHAR(MAX)
30 , Email NVARCHAR(100)
31 , EmailingAddress NVARCHAR(100)
32 , [Password] NVARCHAR(50)
33 , Phone NVARCHAR(50)
34 , OfficePhone NVARCHAR(50)
35 , RegisterDate DATETIME
36 , [Address] NVARCHAR(400)
37 , IsMyIrUser BIT
38 , IsAssistUser BIT
39 , IsAdmin BIT
40 , IsReceiveEmails BIT
41 , IsReceiveCatalog BIT
42 , IsTestModeEnabled BIT
43 , IsTestUser bit
44 , RegistrationStatus TINYINT
45 , LastSeenMyIR DATETIME
46 , LastSeenAssist DATETIME
47 , RowId INT
48 )
49
50 IF @SortField = ''
51 BEGIN
52 SET @SortField = 'Name'
53 END
54 IF @SortDirection = ''
55 BEGIN
56 SET @SortDirection = 'ASC'
57 END
58
59 INSERT INTO #Results
60 SELECT ID, Name, CompanyName, IntellirentCompany, UserIntellirentCompanies, Email, EmailingAddress, [Password], Phone, OfficePhone, RegisterDate, [Address], IsMyIrUser, IsAssistUser, IsAdmin, IsReceiveEmails, IsReceiveCatalog, IsTestModeEnabled,IsTestUser, RegistrationStatus, LastSeenMyIR, LastSeenAssist,
61 (ROW_NUMBER() OVER (ORDER BY
62 CASE WHEN @SortField = 'Name' AND @SortDirection = 'ASC' THEN Name END ASC,
63 CASE WHEN @SortField = 'Name' AND @SortDirection = 'DESC' THEN Name END DESC,
64 CASE WHEN @SortField = 'CompanyName' AND @SortDirection = 'ASC' THEN CompanyName END ASC,
65 CASE WHEN @SortField = 'CompanyName' AND @SortDirection = 'DESC' THEN CompanyName END DESC,
66 CASE WHEN @SortField = 'Phone' AND @SortDirection = 'ASC' THEN Phone END ASC,
67 CASE WHEN @SortField = 'Phone' AND @SortDirection = 'DESC' THEN Phone END DESC,
68 CASE WHEN @SortField = 'Email' AND @SortDirection = 'ASC' THEN Email END ASC,
69 CASE WHEN @SortField = 'Email' AND @SortDirection = 'DESC' THEN Email END DESC,
70 CASE WHEN @SortField = 'RegisterDate' AND @SortDirection = 'ASC' THEN RegisterDate END ASC,
71 CASE WHEN @SortField = 'RegisterDate' AND @SortDirection = 'DESC' THEN RegisterDate END DESC,
72 CASE WHEN @SortField = 'Address' AND @SortDirection = 'ASC' THEN [Address] END ASC,
73 CASE WHEN @SortField = 'Address' AND @SortDirection = 'DESC' THEN [Address] END DESC,
74 CASE WHEN @SortField = 'IsMyIrUser' AND @SortDirection = 'ASC' THEN IsMyIrUser END ASC,
75 CASE WHEN @SortField = 'IsMyIrUser' AND @SortDirection = 'DESC' THEN IsMyIrUser END DESC,
76 CASE WHEN @SortField = 'IsAssistUser' AND @SortDirection = 'ASC' THEN IsAssistUser END ASC,
77 CASE WHEN @SortField = 'IsAssistUser' AND @SortDirection = 'DESC' THEN IsAssistUser END DESC,
78 CASE WHEN @SortField = 'IsReceiveEmails' AND @SortDirection = 'ASC' THEN IsReceiveEmails END ASC,
79 CASE WHEN @SortField = 'IsReceiveEmails' AND @SortDirection = 'DESC' THEN IsReceiveEmails END DESC,
80 CASE WHEN @SortField = 'IsReceiveCatalog' AND @SortDirection = 'ASC' THEN IsReceiveCatalog END ASC,
81 CASE WHEN @SortField = 'IsReceiveCatalog' AND @SortDirection = 'DESC' THEN IsReceiveCatalog END DESC,
82 CASE WHEN @SortField = 'User' AND @SortDirection = 'ASC' THEN Mode END ASC,
83 CASE WHEN @SortField = 'User' AND @SortDirection = 'DESC' THEN Mode END DESC,
84 CASE WHEN @SortField = 'Receive' AND @SortDirection = 'ASC' THEN receiveSortField END ASC,
85 CASE WHEN @SortField = 'Receive' AND @SortDirection = 'DESC' THEN receiveSortField END DESC
86 )) as RowId
87 FROM
88 (
89 SELECT u.ID
90 , u.FirstName + N' ' + u.LastName as Name
91 , ISNULL(u.CompanyName, '') as CompanyName
92 , ISNULL(u.IntellirentCompany, '') as IntellirentCompany
93 --, CASE WHEN u.RegistrationStatus = 2 /* RequiresConfirmation */ THEN dbo.fn_GetUserIntellirentCompanies(u.ID) ELSE '' END as UserIntellirentCompanies
94 , dbo.fn_GetUserIntellirentCompanies(u.ID) as UserIntellirentCompanies
95 , u.Email
96 , ISNULL(u.MaillingAddress, '') as EmailingAddress
97 , u.[Password]
98 , PhoneNumber as Phone
99 , u.OfficePhone
100 , u.RegisterDateUTC as RegisterDate
101 , STUFF(
102 (
103 SELECT ', ' + v
104 FROM (VALUES (CASE WHEN u.Street = '' THEN NULL ELSE u.Street END), (CASE WHEN u.City = '' THEN NULL ELSE u.City END), (CASE WHEN s.Name = '' THEN NULL ELSE s.Name END), (CASE WHEN u.ZipCode = '' THEN NULL ELSE u.ZipCode END)) AS v (v)
105 FOR XML PATH (''), TYPE
106 ).value('.[1]', 'varchar(max)'),
107 1, 2, ''
108 ) as [Address]
109 , CASE WHEN u.Mode & 2 /* MyIR */ = 2 /* MyIR */ THEN 1 ELSE 0 END as IsMyIrUser
110 , CASE WHEN u.Mode & 1 /* Assist */ = 1 /* Assist */ THEN 1 ELSE 0 END as IsAssistUser
111 , CASE WHEN u.[Type] = 1 /* Admin */ THEN 1 ELSE 0 END as IsAdmin
112 , u.ReceiveEmail as IsReceiveEmails
113 , u.ReceiveCatalog as IsReceiveCatalog
114 , u.IsTestModeEnabled
115 , u.IsTestUser
116 , u.Mode
117 , CASE WHEN u.ReceiveEmail = 0 AND u.ReceiveCatalog = 0 THEN 1
118 WHEN u.ReceiveEmail = 0 THEN 2
119 WHEN u.ReceiveCatalog = 0 THEN 3
120 ELSE 4
121 END receiveSortField
122 , u.RegistrationStatus
123 , (SELECT TOP 1 LastContactDateUTC
124 FROM IRUserMobile
125 WHERE AppType = 1/*Assist*/
126 AND u.ID = IRUserID
127 ORDER BY LastContactDateUTC DESC) as LastSeenMyIR
128 , (SELECT TOP 1 LastContactDateUTC
129 FROM IRUserMobile
130 WHERE AppType = 2/*MyIR*/
131 AND u.ID = IRUserID
132 ORDER BY LastContactDateUTC DESC) as LastSeenAssist
133 FROM IRUser as u LEFT OUTER JOIN
134 IRState as s ON u.IRStateID = s.ID
135 WHERE (@ClientID IS NULL
136 OR u.IRClientID = @ClientID)
137 AND ((@Mode = 0 AND u.Mode = 1 /* MyIR */ + 2 /* Assist */)
138 OR (@Mode = 100 /* Admin */ AND u.[Type] = 1)
139 OR (u.Mode | @Mode = @Mode)
140 )
141 AND (@Company IS NULL -- select all - ignore @Company parameter
142 OR (@Company = '' AND u.CompanyName IS NULL ) -- if (@Company = '') THEN select also ('CompanyName = NULL') - we want select all 'empty' CompanyName
143 OR u.CompanyName = @Company -- in all other cases company must be equal to @Company
144 )
145 AND ((@ZipCode IS NULL OR @ZipCode = '')
146 OR u.ZipCode = @ZipCode
147 )
148 AND ((@City IS NULL OR @City = '')
149 OR u.City = @City
150 )
151 AND ((@StateID IS NULL OR @StateID = '')
152 OR s.ID = @StateID
153 )
154 AND u.IsDeleted = 0 AND u.IsTemporary = 0
155 --AND (u.[Password] IS NOT NULL AND u.[Password] <> '')
156 AND (u.FirstName IS NOT NULL AND u.FirstName <> '')-- u.LastName
157 AND (@WithoutCompany IS NULL OR @WithoutCompany = 0 OR (u.IntellirentCompany IS NULL OR u.IntellirentCompany = ''))
158 AND (@NotVerified IS NULL OR @NotVerified = 0 OR u.RegistrationStatus <> 3 /* Verified */)
159 ) r
160 WHERE ( @SearchValue IS NULL OR
161 (
162 (r.Name LIKE '%' + @SearchValue + '%')
163 OR (r.CompanyName LIKE '%' + @SearchValue + '%')
164 OR (r.IntellirentCompany LIKE '%' + @SearchValue + '%')
165 OR (r.Email LIKE '%' + @SearchValue + '%')
166 OR (r.EmailingAddress LIKE '%' + @SearchValue + '%')
167 OR (r.Phone LIKE '%' + @SearchValue + '%')
168 OR (r.OfficePhone LIKE '%' + @SearchValue + '%')
169 OR (r.[Address] LIKE '%' + @SearchValue + '%')
170 ))
171
172 SELECT @RecordCount = COUNT(1)
173 FROM #Results
174
175 IF ((((@PageIndex - 1) * @PageSize) + 1) > @RecordCount)
176 BEGIN
177 SET @PageIndex = 1
178 END
179
180 SELECT * FROM #Results
181 WHERE RowId BETWEEN (((@PageIndex - 1) * @PageSize) + 1) AND (@PageIndex * @PageSize)
182
183 DROP TABLE #Results
184
185 IF @@ERROR<>0 GOTO ERROR_PROC;
186
187 --------------------------------------------------------------------------
188 -- exit point of the store proc
189 RETURN(0);
190
191 ERROR_PROC:
192 RETURN(99);
193END;
194GO
195-- end PV_IRWebGetNewUsersList