def get_all_probe():
try:
gender = request.json['gender']
department = request.json['department']
# common in all case
base_query = '''
SELECT COUNT(*) AS `total_rec` FROM employee e LEFT JOIN department d on e.dept_id = d.id
'''
if gender == 'all' and department == 'all':
query = base_query
result = select_all(query, [])
elif gender != 'all' and department == 'all':
query = base_query + " where e.gender = %s"
result = select_all(query, [gender])
elif gender == 'all' and department != 'all':
query = base_query + " where e.dept_id = %s"
result = select_all(query, [department])
else:
query = base_query + " where e.dept_id = %s and e.gender = %s"
result = select_all(query, [department, gender])
return jsonify(result)
except Exception:
return jsonify({'result': 'failure'})
def get_all_blogs():
if request.method == 'GET':
# query = 'SELECT * FROM `blog`'
query = '''SELECT blog.`blog_id`, blog.`heading`, blog.`body`, user.`name` FROM `blog`
LEFT JOIN `user` ON blog.`user_id` = user.`user_id`
'''
result = select_all(query, [])
return jsonify(result)
elif request.method == 'POST':
n = int(request.json['n'])
query = "SELECT * FROM `blog` ORDER BY `created_on` LIMIT %s"
result = select_all(query, [n])
return jsonify(result)
def get_comments_by_blog_id(blog_id):
# query = 'SELECT `comment` FROM `comment` WHERE `blog_id` = %s'
query = """SELECT comment.comment, user.name FROM comment
LEFT JOIN user ON comment.user_id = user.user_id WHERE comment.`blog_id` = %s
"""
result = select_all(query, [blog_id])
return jsonify(result)
def fetch_subject():
try:
query = "SELECT `subject_name`, `subject_uuid` FROM `subject`"
return select_all(query, [])
except Exception as ex:
print(ex)
return jsonify({'result': 'failure'})
def author_select():
try:
query = '''SELECT * FROM `authors`'''
result = select_all(query, [])
return jsonify(result)
except Exception:
return jsonify({'result': 'failure'})
def fetch_classes():
try:
query = "SELECT `class_name`, `class_uuid` FROM `classes`"
return select_all(query, [])
except Exception as ex:
print(ex)
return jsonify({'result': 'failure'})
def book_fetch_searched():
try:
searchParam = request.json['searchParam']
query = '''
SELECT * FROM (SELECT b.book_id, b.book_title,p.name as publisher,
GROUP_CONCAT(DISTINCT a.name) AS authors,
GROUP_CONCAT(DISTINCT c.name) AS categories
FROM ((SELECT ba.book_id, ba.author_id, bc.category_id FROM book_author ba
LEFT JOIN book_category bc ON ba.book_id = bc.book_id) as bac
RIGHT JOIN books b ON b.book_id = bac.book_id
LEFT JOIN authors a on a.id = bac.author_id
LEFT JOIN category c on c.id = bac.category_id
LEFT JOIN publishers p ON p.id = b.publisher_id)
GROUP BY b.book_id) AS temp
WHERE book_title LIKE '%{0}%'
OR authors LIKE '%{0}%'
OR categories LIKE '%{0}%'
OR publisher LIKE '%{0}%'
'''.format(searchParam)
result = select_all(query)
return jsonify(result)
except Exception as ex:
print(ex)
return jsonify({'result': 'failure'})
def category_select():
try:
query = '''SELECT * FROM `category`'''
result = select_all(query, [])
return jsonify(result)
except Exception:
return jsonify({'result': 'failure'})
def view():
db = sqlite3.connect("contacts.db")
t = PrettyTable(['Name', 'Contact Number'])
allContacts = select_all(db, "contact")
for i in allContacts:
t.add_row([i[0],i[1]])
print(t)
t.clear()
db.close()
def fetch_teacher():
try:
query = """
SELECT t.teacher_name, c.class_name, s.section_name, sub.subject_name
FROM teachers t LEFT JOIN classes c on t.class_uuid = c.class_uuid
LEFT JOIN section s ON t.section_uuid = s.section_uuid
LEFT JOIN subject sub on t.subject_uuid = sub.subject_uuid;
"""
return jsonify(select_all(query, []))
except Exception as ex:
return jsonify({'result': 'failure'})
def summary():
try:
user_id = request.json['user_id']
query = '''select tl.tasklist_id, tl.name, count(t.task_id) as count from tasklists tl
left join task t on tl.tasklist_id = t.tasklist_id
where tl.user_id = %s group by tl.tasklist_id, tl.name
'''
arguments = [user_id]
result = select_all(query, arguments)
return jsonify(result)
except Exception:
return jsonify({'result': 'failure'})
def get_all_records():
try:
gender = request.json['gender']
department = request.json['department']
sal_order = request.json['sal_order']
limit_beg = request.json['limit_beg']
limit_end = request.json['limit_end']
# common in all case
base_query = '''
SELECT LPAD(e.id, 4, "0") as id, e.emp_uuid, e.emp_name, e.email, e.gender, e.salary,
d.dept_name FROM employee e LEFT JOIN department d on e.dept_id = d.id
'''
if sal_order == 'asc':
temp_qry = 'ORDER BY salary ASC LIMIT {}, {}'.format(
limit_beg, limit_end)
else:
temp_qry = 'ORDER BY salary DESC LIMIT {}, {}'.format(
limit_beg, limit_end)
if gender == 'all' and department == 'all':
query = base_query + temp_qry
result = select_all(query, [])
elif gender != 'all' and department == 'all':
query = base_query + " where e.gender = %s " + temp_qry
result = select_all(query, [gender])
elif gender == 'all' and department != 'all':
query = base_query + " where e.dept_id = %s " + temp_qry
result = select_all(query, [department])
else:
query = base_query + " where e.dept_id = %s and e.gender = %s " + temp_qry
result = select_all(query, [department, gender])
return jsonify(result)
except Exception:
return jsonify({'result': 'failure'})
def select_tasklist():
try:
user_id = request.json['user_id']
query = '''select tl.tasklist_id, tl.uuid as tl_uuid, tl.name as tl_name, tl.desc,
group_concat(t.name separator ';;;') as t_name, group_concat(t.uuid separator ';;;') as uuid from tasklists
tl left join task t on tl.tasklist_id = t.tasklist_id
where tl.user_id = %s group by tl.tasklist_id;'''
arguments = [user_id]
result = select_all(query, arguments)
return jsonify(result)
except Exception:
return jsonify({'result':'failure' })
def dir_list(uuid):
try:
if uuid == "all":
query = "SELECT * FROM `dir` WHERE `parent_id` is NULL"
else:
query = "SELECT * FROM `dir` WHERE `parent_id` = '{0}'".format(
uuid)
result = select_all(query, [])
return jsonify(result)
except Exception as ex:
print(ex)
return jsonify({'result': 'failure'})
def search_tasklist():
try:
user_id = request.json['user_id']
search = request.json['search']
query = '''select tl.tasklist_id, tl.uuid as tl_uuid, tl.name as tl_name, tl.desc,
group_concat(t.name separator ';;;') as t_name, group_concat(t.uuid separator ';;;') as uuid from tasklists
tl left join task t on tl.tasklist_id = t.tasklist_id
where tl.user_id = {0} and tl.name like '%{1}%'
group by tl.tasklist_id;'''.format(user_id, search)
# return jsonify({'query': query})
result = select_all(query)
return jsonify(result)
except Exception:
return jsonify({'result':'failure' })
def book_fetchall():
try:
query = '''
SELECT b.book_id, b.book_title,p.name as publisher,
GROUP_CONCAT(DISTINCT a.name) AS authors,
GROUP_CONCAT(DISTINCT c.name) AS categories
FROM ((SELECT ba.book_id, ba.author_id, bc.category_id
FROM book_author ba LEFT JOIN book_category bc ON ba.book_id = bc.book_id) as bac
RIGHT JOIN books b ON b.book_id = bac.book_id LEFT JOIN authors a on a.id = bac.author_id
LEFT JOIN category c on c.id = bac.category_id
LEFT JOIN publishers p ON p.id = b.publisher_id) GROUP BY b.book_id;
'''
result = select_all(query, [])
return jsonify(result)
except Exception:
return jsonify({'result': 'failure'})
def get_all_department():
query = 'SELECT id, dept_name FROM department'
result = select_all(query, [])
return jsonify(result)
def get_blog_by_user_id(user_id):
query = 'SELECT * FROM `blog` WHERE `user_id` = %s'
result = select_all(query, [user_id])
return jsonify(result)
def get_all_category():
query = 'SELECT * FROM `category` ORDER BY `category` ASC'
result = select_all(query, [])
return jsonify(result)
