1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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
|
