added query file for raw sql queries

1 files changed, 67 insertions, 0 deletions

diff --git a/website/query.py b/website/query.py
new file mode 100644
index 0000000..43b172b
--- /dev/null
+++ b/website/query.py
@@ -0,0 +1,67 @@
+GET_SUBCATEGORY_SQL = """
+                SELECT id, subcategory_id, subcategory, maincategory_id
+                FROM list_of_subcategory WHERE maincategory_id =%s
+                """
+
+
+GET_TBC_PREFERENCE_SQL = """
+                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.edition, po.approval_date as approval_date
+                FROM textbook_companion_preference pe INNER JOIN
+                textbook_companion_proposal po ON pe.proposal_id = po.id
+                INNER JOIN textbook_companion_book_main_subcategories tcbm
+                ON pe.id = tcbm.pref_id
+                INNER 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 loc.category_id = %s AND tcbm.sub_category = %s
+                """
+
+
+GET_TBC_CHAPTER_SQL = """
+                SELECT id, number, name FROM textbook_companion_chapter
+                WHERE preference_id = %s AND cloud_chapter_err_status = 0
+                ORDER BY number
+                """
+
+
+GET_TBC_EXAMPLE_SQL = """
+                SELECT id, number, caption FROM textbook_companion_example
+                WHERE chapter_id = %s AND cloud_err_status = 0
+                ORDER BY number
+                """
+
+
+GET_TBC_EXAMPLE_FILE_SQL = """
+                SELECT tcp.id AS id,tcc.id AS chapter_id, tce.id AS example_id,
+                tcef.id AS file_id, tcp.directory_name, tcef.filepath
+                FROM textbook_companion_preference tcp
+                INNER JOIN textbook_companion_chapter tcc
+                ON tcp.id = tcc.preference_id
+                INNER JOIN textbook_companion_example tce
+                ON tcc.id = tce.chapter_id
+                INNER JOIN textbook_companion_example_files tcef
+                ON tce.id=tcef.example_id
+                WHERE tcef.filetype = 'S' AND tcef.example_id = %s
+                """
+
+
+GET_TBC_EXAMPLE_FILE_VIEW_SQL = """
+                SELECT id, views_count FROM textbook_companion_example_views
+                WHERE example_id = %s
+                """
+
+
+GET_TBC_CONTRIBUTOR_DETAILS_SQL = """
+                SELECT preference.id,
+                proposal.full_name as proposal_full_name,
+                proposal.faculty as proposal_faculty,
+                proposal.reviewer as proposal_reviewer,
+                proposal.university as proposal_university
+                FROM textbook_companion_proposal proposal
+                INNER JOIN textbook_companion_preference preference
+                ON proposal.id = preference.proposal_id
+                WHERE preference.id = %s