· 9 years ago · Nov 17, 2016, 09:42 AM
1USE [CSAEpubReader]
2GO
3/****** Object: StoredProcedure [dbo].[selectProductForType] Script Date: 11/17/2016 10:41:15 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER proc [dbo].[selectProductForType]
9@userId int = 0,
10@search nvarchar(500) = NULL,
11@pageSize int,
12@pageIndex int,
13@sortBy nvarchar(100),
14@sortOrder nvarchar(100),
15@categoryId int = 0,
16@productId int = 0,
17@mybooks bit = null,
18@getType int = 1,
19@isFeatured bit = null,
20--@syncTimestamp datetime = null,
21@accountApplicationId INT = 0
22as
23
24if (@userId <> 0)
25begin
26
27 IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tempTable'))
28 BEGIN
29 drop table tempTable
30 END
31
32
33 CREATE TABLE tempTable (BookId INT, [Timestamp] DATETIME)
34 INSERT INTO tempTable (BookId, [Timestamp])
35 select book_id, MAX([timestamp])
36 from ePubActivityHistories
37 where ePubActivityHistories.account_Id = @userId
38 group by book_id
39
40 DECLARE @retVal int
41
42 SELECT @retVal = COUNT(*)
43 FROM tempTable
44
45
46 if(@categoryId <> 0)
47 begin
48
49 if(@retVal > 0)
50 begin
51 WITH AllResults AS (SELECT ROW_NUMBER() OVER
52 (ORDER BY
53 CASE WHEN @sortBy = 'name' and @sortOrder = 'ascending' THEN Product.name END ASC,
54 CASE WHEN @sortBy = 'name' and @sortOrder = 'descending' THEN Product.name END DESC,
55
56 CASE WHEN @getType != 3 and @sortBy = 'date' and @sortOrder = 'ascending' THEN account_code.date END ASC,
57 CASE WHEN @getType != 3 and @sortBy = 'date' and @sortOrder = 'descending' THEN account_code.date END DESC,
58
59 CASE WHEN @getType = 3 and @sortBy = 'date' and @sortOrder = 'ascending' THEN tempTable.[Timestamp] END ASC,
60 CASE WHEN @getType = 3 and @sortBy = 'date' and @sortOrder = 'descending' THEN tempTable.[Timestamp] END DESC
61 ) AS rowNumber,
62 Count(*) over () AS TotalCount,
63 Product.*,
64 books.Id as bookId,
65 books.Publisher as publisher,
66 books.TableOfContentHTML as tableOfContentHTML,
67 books.TableOfContentEpub30File as tableOfContentEpub30File,
68 books.ContentOpfPath as contentOpfPath,
69 books.SpineContent as spineContent,
70 books.ManifestElementContent as manifestElementContent,
71 books.BookIdentifier as bookIdentifier,
72 books.Parent as parent,
73 books.Version as bookVersion,
74 books.FileSize as fileSize,
75 account_code.id as account_code_id,
76 account_code.date as account_code_date,
77 tempTable.Timestamp as ePubActivityHistories_timestamp
78 FROM Product
79 left join books on books.CsaBookId = Product.Id
80 left join account_code on Product.id = account_code.product_ID and account_code.account_ID = @userId
81 left join productCategory on Product.id = productCategory.product_ID
82 left join tempTable on tempTable.BookId = books.Id
83 WHERE Product.[type] = 1
84 and hidden <> 1
85 AND (@isFeatured IS NULL OR Product.featured = @isFeatured)
86 and ((@search is null) OR (name like '%' + @search + '%' or Product.description like '%' + @search + '%'))
87 and productCategory.category_ID = @categoryId
88 and (@mybooks = 0 or account_code.id is not null)
89 --AND (@syncTimestamp is NULL OR account_code.date > @syncTimestamp)
90 AND (@productId = 0 OR Product.Id = @productId)
91 and (@getType = 1 OR --all books
92 (@getType = 2 and tempTable.BookId is null) OR --not read
93 (@getType = 3 and tempTable.BookId is not null and tempTable.[Timestamp] > DATEADD(day,-7,GETUTCDATE())) OR --recently read
94 (@getType = 4 and account_code.date > DATEADD(day,-7,GETDATE()))) -- recently added
95 and (books.Id is null OR books.Id in (SELECT p.Id from Books p
96 WHERE NOT EXISTS (
97 SELECT 1 FROM Books b
98 WHERE b.Parent = p.Id
99 )))
100 )
101
102
103 SELECT *
104 FROM AllResults
105 WHERE (@pageSize = 0 OR (rowNumber > (@pageSize * (@pageIndex - 1)) AND rowNumber <= @pageSize * @pageIndex))
106
107 end
108
109 else
110 begin
111
112 WITH AllResults AS
113 (
114 SELECT ROW_NUMBER() OVER
115 (ORDER BY
116 CASE WHEN @sortBy = 'name' and @sortOrder = 'ascending' THEN Product.name END ASC,
117 CASE WHEN @sortBy = 'name' and @sortOrder = 'descending' THEN Product.name END DESC,
118
119 CASE WHEN @sortBy = 'date' and @sortOrder = 'ascending' THEN Product.date END ASC,
120 CASE WHEN @sortBy = 'date' and @sortOrder = 'descending' THEN Product.date END DESC
121 ) AS rowNumber,
122 Count(*) over () AS TotalCount,
123 Product.*, books.Id as bookId,
124 books.Publisher as publisher,
125 books.TableOfContentHTML as tableOfContentHTML,
126 books.TableOfContentEpub30File as tableOfContentEpub30File,
127 books.ContentOpfPath as contentOpfPath,
128 books.SpineContent as spineContent,
129 books.ManifestElementContent as manifestElementContent,
130 books.BookIdentifier as bookIdentifier,
131 books.Parent as parent,
132 books.Version as bookVersion,
133 books.FileSize as fileSize,
134 account_code.id as account_code_id,
135 account_code.date as account_code_date,
136 null as ePubActivityHistories_timestamp
137 FROM Product
138 left join books on books.CsaBookId = Product.Id
139 left join productCategory on Product.id = productCategory.product_ID
140 left join account_code on Product.id = account_code.product_ID and account_code.account_ID = @userId
141 WHERE [type] = 1
142 and hidden <> 1
143 AND (@isFeatured IS NULL OR Product.featured = @isFeatured)
144 and ((@search is null) OR (name like '%' + @search + '%' or Product.description like '%' + @search + '%'))
145 and productCategory.category_ID = @categoryId AND (@productId = 0 OR Product.Id = @productId)
146 and (@mybooks = 0 or account_code.id is not null)
147 and (books.Id is null OR books.Id in (SELECT p.Id from Books p
148 WHERE NOT EXISTS (
149 SELECT 1 FROM Books b
150 WHERE b.Parent = p.Id
151 )))
152
153 )
154
155 SELECT *
156 FROM AllResults
157 WHERE (@pageSize = 0 OR (rowNumber > (@pageSize * (@pageIndex - 1)) AND rowNumber <= @pageSize * @pageIndex ))
158 end
159 end
160
161 else
162 begin
163
164 if(@retVal > 0)
165 begin
166 WITH AllResults AS (
167 SELECT ROW_NUMBER() OVER
168 (ORDER BY
169 CASE WHEN @sortBy = 'name' and @sortOrder = 'ascending' THEN Product.name END ASC,
170 CASE WHEN @sortBy = 'name' and @sortOrder = 'descending' THEN Product.name END DESC,
171
172 CASE WHEN @getType != 3 and @sortBy = 'date' and @sortOrder = 'ascending' THEN account_code.date END ASC,
173 CASE WHEN @getType != 3 and @sortBy = 'date' and @sortOrder = 'descending' THEN account_code.date END DESC,
174
175 CASE WHEN @sortBy = 'date' and @sortOrder = 'ascending' THEN tempTable.Timestamp END ASC,
176 CASE WHEN @sortBy = 'date' and @sortOrder = 'descending' THEN tempTable.Timestamp END DESC
177 ) AS rowNumber,
178 Count(*) over () AS TotalCount,
179 Product.*,
180 books.Id as bookId,
181 books.Publisher as publisher,
182 books.TableOfContentHTML as tableOfContentHTML,
183 books.TableOfContentEpub30File as tableOfContentEpub30File,
184 books.ContentOpfPath as contentOpfPath,
185 books.SpineContent as spineContent,
186 books.ManifestElementContent as manifestElementContent,
187 books.BookIdentifier as bookIdentifier,
188 books.Parent as parent,
189 books.Version as bookVersion,
190 books.FileSize as fileSize,
191 account_code.id as account_code_id,
192 account_code.date as account_code_date,
193 tempTable.Timestamp as ePubActivityHistories_timestamp,
194 MyBooksSyncStatuses.id as MyBooksSyncStatusesId
195 FROM Product
196 left join books on books.CsaBookId = Product.Id
197 left join account_code on Product.id = account_code.product_ID and account_code.account_ID = @userId
198 left join tempTable on tempTable.BookId = books.Id
199 LEFT OUTER JOIN MyBooksSyncStatuses ON Books.CsaBookId = MyBooksSyncStatuses.productId AND MyBooksSyncStatuses.accountApplicationId = @accountApplicationId
200 WHERE
201 Product.[type] = 1
202 and hidden <> 1
203 AND (@isFeatured IS NULL OR Product.featured = @isFeatured)
204 and ((@search is null) OR (name like '%' + @search + '%' or Product.description like '%' + @search + '%'))
205 and (@mybooks = 0 or account_code.id is not null)
206 AND (@accountApplicationId = 0 OR MyBooksSyncStatuses.id IS NULL)
207 --AND (@syncTimestamp is NULL OR account_code.date > @syncTimestamp)
208 AND (@productId = 0 OR Product.Id = @productId)
209 and
210 (@getType = 1 OR --all books
211 (@getType = 2 and tempTable.BookId is null) OR --not read
212 (@getType = 3 and tempTable.BookId is not null and tempTable.Timestamp > DATEADD(day,-7,GETUTCDATE())) OR --recently read
213 (@getType = 4 and account_code.date > DATEADD(day,-7,GETDATE()))) -- recently added
214 and (books.Id is null OR books.Id in (SELECT p.Id from Books p
215 WHERE NOT EXISTS (
216 SELECT 1 FROM Books b
217 WHERE b.Parent = p.Id
218 )))
219 )
220
221 SELECT *
222 FROM AllResults
223 WHERE (@pageSize = 0 OR (rowNumber > (@pageSize * (@pageIndex - 1)) AND rowNumber <= @pageSize * @pageIndex))
224 end
225
226 else
227 begin
228 WITH AllResults AS (
229 SELECT ROW_NUMBER() OVER
230 (ORDER BY
231 CASE WHEN @sortBy = 'name' and @sortOrder = 'ascending' THEN Product.name END ASC,
232 CASE WHEN @sortBy = 'name' and @sortOrder = 'descending' THEN Product.name END DESC,
233
234 CASE WHEN @getType != 3 and @sortBy = 'date' and @sortOrder = 'ascending' THEN account_code.date END ASC,
235 CASE WHEN @getType != 3 and @sortBy = 'date' and @sortOrder = 'descending' THEN account_code.date END DESC,
236
237 CASE WHEN @sortBy = 'date' and @sortOrder = 'ascending' THEN Product.date END ASC,
238 CASE WHEN @sortBy = 'date' and @sortOrder = 'descending' THEN Product.date END DESC
239 ) AS rowNumber,
240 Count(*) over () AS TotalCount,
241 Product.*,
242 books.Id as bookId,
243 books.Publisher as publisher,
244 books.TableOfContentHTML as tableOfContentHTML,
245 books.TableOfContentEpub30File as tableOfContentEpub30File,
246 books.ContentOpfPath as contentOpfPath,
247 books.SpineContent as spineContent,
248 books.ManifestElementContent as manifestElementContent,
249 books.BookIdentifier as bookIdentifier,
250 books.Parent as parent,
251 books.Version as bookVersion,
252 books.FileSize as fileSize,
253 account_code.id as account_code_id,
254 account_code.date as account_code_date,
255 tempTable.Timestamp as ePubActivityHistories_timestamp,
256 MyBooksSyncStatuses.id as MyBooksSyncStatusesId
257 FROM Product
258 left join books on books.CsaBookId = Product.Id
259 left join account_code on Product.id = account_code.product_ID and account_code.account_ID = @userId
260 left join tempTable on tempTable.BookId = books.Id
261 LEFT OUTER JOIN MyBooksSyncStatuses ON Books.CsaBookId = MyBooksSyncStatuses.productId AND MyBooksSyncStatuses.accountApplicationId = @accountApplicationId
262 WHERE
263 Product.[type] = 1
264 and hidden <> 1
265 AND (@isFeatured IS NULL OR Product.featured = @isFeatured)
266 and ((@search is null) OR (name like '%' + @search + '%' or Product.description like '%' + @search + '%'))
267 and (@mybooks = 0 or account_code.id is not null)
268 AND (@accountApplicationId = 0 OR MyBooksSyncStatuses.id IS NULL)
269 --AND (@syncTimestamp is NULL OR account_code.date > @syncTimestamp)
270 AND (@productId = 0 OR Product.Id = @productId)
271 and
272 (@getType = 1 OR --all books
273 (@getType = 2 and tempTable.BookId is null) OR --not read
274 (@getType = 3 and tempTable.BookId is not null and tempTable.Timestamp > DATEADD(day,-7,GETUTCDATE())) OR --recently read
275 (@getType = 4 and account_code.date > DATEADD(day,-7,GETDATE()))) -- recently added
276 and (books.Id is null OR books.Id in (SELECT p.Id from Books p
277 WHERE NOT EXISTS (
278 SELECT 1 FROM Books b
279 WHERE b.Parent = p.Id
280 )))
281 )
282
283 SELECT *
284 FROM AllResults
285 WHERE (@pageSize = 0 OR (rowNumber > (@pageSize * (@pageIndex - 1)) AND rowNumber <= @pageSize * @pageIndex))
286
287 end
288 end
289
290end
291
292else
293begin
294
295if(@categoryId <> 0)
296begin
297 WITH AllResults AS
298 (
299 SELECT ROW_NUMBER() OVER
300 (ORDER BY
301 CASE WHEN @sortBy = 'name' and @sortOrder = 'ascending' THEN Product.name END ASC,
302 CASE WHEN @sortBy = 'name' and @sortOrder = 'descending' THEN Product.name END DESC,
303
304 CASE WHEN @sortBy = 'date' and @sortOrder = 'ascending' THEN Product.date END ASC,
305 CASE WHEN @sortBy = 'date' and @sortOrder = 'descending' THEN Product.date END DESC
306 ) AS rowNumber,
307 Count(*) over () AS TotalCount,
308 Product.*, books.Id as bookId,
309 books.Publisher as publisher,
310 books.TableOfContentHTML as tableOfContentHTML,
311 books.TableOfContentEpub30File as tableOfContentEpub30File,
312 books.ContentOpfPath as contentOpfPath,
313 books.SpineContent as spineContent,
314 books.ManifestElementContent as manifestElementContent,
315 books.BookIdentifier as bookIdentifier,
316 books.Parent as parent,
317 books.Version as bookVersion,
318 books.FileSize as fileSize
319 FROM Product
320 left join books on books.CsaBookId = Product.Id
321 left join productCategory on Product.id = productCategory.product_ID
322 WHERE [type] = 1
323 and hidden <> 1
324 AND (@isFeatured IS NULL OR Product.featured = @isFeatured)
325 and ((@search is null) OR (name like '%' + @search + '%' or Product.description like '%' + @search + '%'))
326 and productCategory.category_ID = @categoryId AND (@productId = 0 OR Product.Id = @productId)
327 and (books.Id is null OR books.Id in (SELECT p.Id from Books p
328 WHERE NOT EXISTS (
329 SELECT 1 FROM Books b
330 WHERE b.Parent = p.Id
331 )))
332
333 )
334
335 SELECT *
336 FROM AllResults
337 WHERE (@pageSize = 0 OR (rowNumber > (@pageSize * (@pageIndex - 1)) AND rowNumber <= @pageSize * @pageIndex ))
338end
339
340else
341begin
342 WITH AllResults AS
343 (
344 SELECT ROW_NUMBER() OVER
345 (ORDER BY
346 CASE WHEN @sortBy = 'name' and @sortOrder = 'ascending' THEN Product.name END ASC,
347 CASE WHEN @sortBy = 'name' and @sortOrder = 'descending' THEN Product.name END DESC,
348
349 CASE WHEN @sortBy = 'date' and @sortOrder = 'ascending' THEN Product.date END ASC,
350 CASE WHEN @sortBy = 'date' and @sortOrder = 'descending' THEN Product.date END DESC
351 ) AS rowNumber,
352 Count(*) over () AS TotalCount,
353 Product.* , books.Id as bookId,
354 books.Publisher as publisher,
355 books.TableOfContentHTML as tableOfContentHTML,
356 books.TableOfContentEpub30File as tableOfContentEpub30File,
357 books.ContentOpfPath as contentOpfPath,
358 books.SpineContent as spineContent,
359 books.ManifestElementContent as manifestElementContent,
360 books.BookIdentifier as bookIdentifier,
361 books.Parent as parent,
362 books.Version as bookVersion,
363 books.FileSize as fileSize
364 FROM Product
365 left join books on books.CsaBookId = Product.Id
366 WHERE [type] = 1
367 and hidden <> 1
368 AND (@isFeatured IS NULL OR Product.featured = @isFeatured)
369 and ((@search is null) OR (name like '%' + @search + '%' or Product.description like '%' + @search + '%'))
370 AND (@productId = 0 OR Product.Id = @productId)
371 and (books.Id is null OR books.Id in (SELECT p.Id from Books p
372 WHERE NOT EXISTS (
373 SELECT 1 FROM Books b
374 WHERE b.Parent = p.Id
375 )))
376
377 )
378
379 SELECT *
380 FROM AllResults
381 WHERE (@pageSize = 0 OR (rowNumber > (@pageSize * (@pageIndex - 1)) AND rowNumber <= @pageSize * @pageIndex ))
382end
383end
384
385
386--end