diff options
Diffstat (limited to 'website/query.py')
| -rw-r--r-- | website/query.py | 76 |
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 + """ |