updated the queries

1 files changed, 74 insertions, 2 deletions

diff --git a/website/query.py b/website/query.py
index 8ccd7a2..863c497 100644
--- a/website/query.py
+++ b/website/query.py
@@ -2,11 +2,14 @@ GET_ALLMAINCATEGORY_SQL = """
                 SELECT category_id, maincategory
                 FROM list_of_category ORDER BY maincategory ASC
                 """
+
+
 GET_ALLSUBCATEGORY_SQL = """
                 SELECT id, subcategory_id, subcategory, maincategory_id
                 FROM list_of_subcategory ORDER BY subcategory ASC
                 """
 
+
 GET_SUBCATEGORY_SQL = """
                 SELECT id, subcategory_id, subcategory, maincategory_id
                 FROM list_of_subcategory WHERE maincategory_id =%s
@@ -14,8 +17,6 @@ GET_SUBCATEGORY_SQL = """
                 """
 
 
-
-
 GET_TBC_PREFERENCE_SQL = """
                 SELECT DISTINCT (loc.category_id), pe.id,
                 tcbm.sub_category, loc.maincategory, pe.book as book,
@@ -61,6 +62,7 @@ GET_TBC_EXAMPLE_FILE_SQL = """
                 WHERE tcef.filetype = 'S' AND tcef.example_id = %s
                 """
 
+
 GET_TBC_CONTRIBUTOR_DETAILS_SQL = """
                 SELECT preference.id,
                 proposal.full_name as proposal_full_name,
@@ -73,20 +75,24 @@ GET_TBC_CONTRIBUTOR_DETAILS_SQL = """
                 WHERE preference.id = %s
                 """
 
+
 GET_TBC_CHAPTER_ID_SQL = """
                 SELECT chapter_id FROM textbook_companion_example WHERE id = %s
                 """
 
+
 INSERT_TBC_EXAMPLE_VIEW_SQL = """
                 INSERT INTO textbook_companion_example_views
                 (example_id,chapter_id) VALUES(%s, %s)
                 """
 
+
 UPDATE_TBC_EXAMPLE_VIEW_SQL = """
                 UPDATE textbook_companion_example_views
                 SET views_count = views_count + 1 WHERE example_id = %s
                 """
 
+
 GET_TBC_EXAMPLE_VIEW_SQL = """
                 SELECT views_count FROM textbook_companion_example_views
                 WHERE example_id = %s
@@ -96,6 +102,7 @@ GET_TBC_EXAMPLE_R_CLOUD_COMMENT_SQL = """
                 SELECT COUNT(id) FROM r_cloud_comment WHERE example= %s
                 """
 
+
 GET_TBC_EXAMPLE_CHAPTER_ID_SQL = """
                 SELECT DISTINCT(chapter_id)
                 FROM textbook_companion_example
@@ -112,6 +119,7 @@ GET_TBC_CHAPTER_PREFERENCE_ID_SQL = """
                 FROM textbook_companion_chapter WHERE id = %s)
                 """
 
+
 GET_TBC_PREFERENCE_DETAIL_CATEGORY_SQL = """
                 SELECT DISTINCT (loc.category_id),
                 tcbm.sub_category,loc.maincategory, pe.book as book,
@@ -129,6 +137,7 @@ GET_TBC_PREFERENCE_DETAIL_CATEGORY_SQL = """
                 AND pe.id= %s
                 """
 
+
 GET_TBC_PREFERENCE_FROM_CATEGORY_ID_SQL = """
                 SELECT DISTINCT (loc.category_id),pe.id,
                 tcbm.sub_category,loc.maincategory, pe.book as
@@ -146,6 +155,7 @@ GET_TBC_PREFERENCE_FROM_CATEGORY_ID_SQL = """
                 AND tcbm.sub_category = %s
                 """
 
+
 GET_TBC_CHAPTER_DETAIL_SQL = """
                 SELECT id, name, number, preference_id
                 FROM textbook_companion_chapter
@@ -154,3 +164,65 @@ GET_TBC_CHAPTER_DETAIL_SQL = """
                 FROM textbook_companion_chapter WHERE id = %s)
                 ORDER BY number ASC
                 """
+
+
+GET_SEARCH_BOOK_SQL = """
+                SELECT pe.id, pe.book as book, pe.author as author,
+                pe.publisher as publisher,pe.year as year,
+                pe.id as pe_id, po.approval_date as approval_date
+                FROM textbook_companion_preference pe
+                LEFT JOIN textbook_companion_proposal po ON
+                pe.proposal_id = po.id WHERE
+                (pe.book like %s OR pe.author like %s)
+                AND po.proposal_status = 3
+                AND pe.approval_status = 1
+                ORDER BY (pe.book = %s OR pe.author = %s) DESC,
+                length(pe.book)
+                """
+
+
+GET_SEARCH_POPULAR_BOOK_SQL = """
+                SELECT pe.id, pe.book as book, pe.author as author,
+                pe.publisher as publisher,pe.year as year,
+                pe.id as pe_id, po.approval_date as approval_date,
+                tcph.hitcount FROM textbook_companion_preference pe
+                left join textbook_companion_preference_hits tcph on
+                tcph.pref_id = pe.id
+                LEFT JOIN textbook_companion_proposal po ON
+                pe.proposal_id = po.id WHERE po.proposal_status = 3
+                AND pe.approval_status = 1
+                ORDER BY tcph.hitcount DESC LIMIT 10
+                """
+
+
+GET_SEARCH_RECENT_BOOK_SQL = """
+                SELECT pe.id, pe.book as book, pe.author as author,
+                pe.publisher as publisher,pe.year as year,
+                pe.id as pe_id, po.approval_date as approval_date,
+                tcph.hitcount, tcph.last_search
+                FROM textbook_companion_preference pe
+                left join textbook_companion_preference_hits tcph
+                on tcph.pref_id = pe.id
+                LEFT JOIN textbook_companion_proposal po ON
+                pe.proposal_id = po.id WHERE po.proposal_status = 3
+                AND pe.approval_status = 1
+                ORDER BY tcph.last_search DESC LIMIT 10
+                """
+
+
+GET_BOOK_CATEGORY_FROM_ID = """
+                SELECT DISTINCT (loc.category_id),pe.id,
+                tcbm.sub_category,loc.maincategory, pe.book as
+                book,pe.author as author, pe.publisher as publisher,
+                pe.year as year, pe.id as pe_id, pe.edition,
+                po.approval_date as approval_date
+                FROM textbook_companion_preference pe LEFT JOIN
+                textbook_companion_proposal po ON pe.proposal_id = po.id
+                LEFT JOIN textbook_companion_book_main_subcategories
+                tcbm ON pe.id = tcbm.pref_id LEFT JOIN list_of_category
+                loc ON tcbm.main_category = loc.category_id WHERE
+                po.proposal_status = 3 AND pe.approval_status = 1
+                AND pe.id = tcbm.pref_id AND
+                pe.cloud_pref_err_status = 0 AND
+                pe.id=%s
+                """