def get(self, user_id):
try:
conn = DBConn.conn().connect() # connect to database
sql_request1 = """select message,m.id, m.ts, is_read, chain_num
,concat(coalesce(l.lastname, coalesce(s.lastname, '')), ' ', coalesce(l.firstname , coalesce(s.firstname , '')), ' ', coalesce(l.midname , coalesce(s.midname , '')) ) as from_name
,concat(coalesce(l2.lastname, coalesce(s2.lastname, '')), ' ', coalesce(l2.firstname , coalesce(s2.firstname , '')), ' ', coalesce(l2.midname , coalesce(s2.midname , '')) ) as to_name
from public.message m
left join public.lecturer l on m.from_id = l.id
left join public.student s on m.from_id = s.id
left join public.lecturer l2 on m.to_id = l2.id
left join public.student s2 on m.to_id = s2.id
where to_id = {} or from_id = {}
order by ts desc;
""".format(user_id, user_id)
logging.info('Request to DB: {}'.format(sql_request1))
query = conn.execute(sql_request1)
# sql_request2 = """
# update public.message
# set is_read = true
# where to_id = {};
# """.format( user_id)
# logging.info('Request to DB: {}'.format(sql_request2))
# query2 = conn.execute(sql_request2)
result = [dict(zip(tuple(query.keys()), i)) for i in query.cursor]
grouped = defaultdict(list)
for item in result:
grouped[item['chain_num']].append(item)
res = {}
for chain_num, msg in grouped.items():
res0 = {str(chain_num): msg}
res.update(res0)
return jsonify(res)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def put(self, course_id):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
sql_request = ''
if (json['week_num'] > 15):
raise ValueError("there are only 15 weeks in course")
for x in json.keys():
if (x == "week_num"):
sql_request = sql_request + " where week_num = " + str(json[x])
else:
if(x == "is_hw"):
sql_request = ", " + str(x) + " = " + str(json[x]) + "" + sql_request
else:
sql_request = ", " + str(x) + " = '" + str(json[x]) + "'" + sql_request
#sql_request = ", " + str(x) + " = '" + str(json[x]) + "'" + sql_request
sql_request = sql_request + " and course_id = {};".format(course_id)
sql_request = 'update public.course_week set ' + sql_request[2:]
conn = DBConn.conn().connect()
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
sql_request = 'insert into public.course(' + ', '.join(
(str(x) for x in json.keys())) + ') values(\'' + '\', \''.join(
(str(x) for x in json.values())) + '\');'
sql_request += """insert into public.course_week(course_id, week_num)
select course_id, a from
(select 1 as a union all
select 2 as a union all
select 3 as a union all
select 4 as a union all
select 5 as a union all
select 6 as a union all
select 7 as a union all
select 8 as a union all
select 9 as a union all
select 10 as a union all
select 11 as a union all
select 12 as a union all
select 13 as a union all
select 14 as a union all
select 15 as a) s join(select max(id) as course_id from public.course where "name" = '{}' and lec_id = {} ) d on 1=1;
""".format(json['name'], json['lec_id'])
conn = DBConn.conn().connect()
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self):
try:
sql_request = """select s.lec_id, concat(coalesce(l.lastname, ''), ' ', coalesce(l.firstname , ''), ' ', coalesce(l.midname , '') ) as "lec_name", c."name" as "name",
s.course_id, s.day_cd, s."time", s.place, s.islecture, s.isseminar
from public.schedule0 s join lecturer l on s.lec_id = l.id
join public.course c on s.course_id = c.id
where not is_app and s."comment" is null;"""
logging.info('Request to DB: {}'.format(sql_request))
conn = DBConn.conn().connect()
query = conn.execute(sql_request)
result = [dict(zip(tuple(query.keys()), i)) for i in query.cursor]
grouped = defaultdict(list)
for item in result:
grouped[item['lec_id'], item['lec_name']].append(item)
res = []
for lec, sched in grouped.items():
res0 = {'lec_id': lec[0], 'name': lec[1]}
for k in sched:
k.pop('lec_name')
res0.update({'schedule': sched})
res.append(res0)
return jsonify(res)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
s=''
if(str(json['role']) == 'lecturer'):
s = """insert into public.lecturer(id) select id from public.users where login = '******' and "password" = '{}' and "role" = '{}';""".format(str(json['login']),str(json['password']),str(json['role']))
else:
if(str(json['role']) == 'student'):
s = """insert into public.student(id) select id from public.users where login = '******' and "password" = '{}' and "role" = '{}';""".format(str(json['login']),str(json['password']),str(json['role']))
else:
if(str(json['role']) == 'admin'):
s = ''
else:
raise ValueError("Roles: student, lecturer, admin")
sql_request = """insert into public.users(login, "password", "role") values('{}', '{}', '{}');""".format(str(json['login']),str(json['password']),str(json['role']))
sql_request += s
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
response = jsonify({"result": "OK"})
response.headers.add("Access-Control-Allow-Origin", "*")
return response
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self):
try:
args = request.args
conn = DBConn.conn().connect() # connect to database
sql_request = """select week_num, hw_url, mark, "comment" from public.homeworks h
where course_id = {} and student_id = {};""".format(args['course_id'],
args['student_id'])
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(
sql_request
) # This line performs query and returns json result
result = {
'hw':
[dict(zip(tuple(query.keys()), i)) for i in query.cursor]
}
marks = []
for a in result['hw']:
if (type(a['mark']) == int):
marks.append(a['mark'])
avg_mark = round(mean(marks), 2)
result.update({'avg_mark': avg_mark})
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, course_id):
try:
conn = DBConn.conn().connect()
sql_request = "select course_id, notification, ts from public.course_notification where course_id = {} ".format(course_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request) # This line performs query and returns json result
result = {'data': [dict(zip(tuple (query.keys()) ,i)) for i in query.cursor]}
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, lec_id):
try:
conn = DBConn.conn().connect()
sql_request = "select * from public.lecturer where id = {} ".format(lec_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
result = dict(zip(tuple (query.keys()) ,query.fetchone()))
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self, user_id):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect() # connect to database
query = conn.execute(
"""update public.users set is_auth = false where id = '{}';""".
format(user_id))
return "OK"
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
query = conn.execute(
"""insert into public.course_students(course_id, student_id)
values({}, {});""".format(json['course_id'],
json['student_id']))
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, user_id):
try:
conn = DBConn.conn().connect() # connect to database
sql_request = """select count(*) as not_num from
public.notification s
where to_id = {} and not is_read;""".format(user_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request) # This line performs query and returns json result
result = dict(zip(tuple (query.keys()) ,query.fetchone()))
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
sql_request = 'insert into public.lecturer(' + ', '.join(
(str(x) for x in json.keys())) + ') values(\'' + '\', \''.join(
(str(x) for x in json.values())) + '\')'
conn = DBConn.conn().connect()
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, student_id):
try:
conn = DBConn.conn().connect() # connect to database
sql_request = "select * from public.student where id = {};".format(
student_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(
sql_request
) # This line performs query and returns json result
result = dict(zip(tuple(query.keys()), query.fetchone()))
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect() # connect to database
sql_request = """select id from public.users where login = '******' and "password" = '{}' and "role" = '{}';""".format(str(json['login']),str(json['password']),str(json['role']))
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
result = dict(zip(tuple (query.keys()) ,query.fetchone()))
print('Result: ' + jsonify(result))
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, user_id):
try:
conn = DBConn.conn().connect() # connect to database
sql_request = """select id, case when from_role = 'admin' then 'администратор' when from_role = 'student' then 'студент' when from_role = 'lecturer' then 'преподаватель' when from_role = 'course' then 'курс' end as from_role, coalesce(from_name, '')as from_name, notification, ts, is_read from
public.notification s
where to_id = {}
order by ts desc;
""".format(user_id, user_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request) # This line performs query and returns json result
result = [dict(zip(tuple(query.keys()), i)) for i in query.cursor]
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def put(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
sql_request = """update public.homeworks set mark = {}
where course_id = {} and student_id = {} and week_num = {};""".format(
json['mark'], json['course_id'], json['student_id'],
json['week_num'])
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, course_id):
try:
conn = DBConn.conn().connect() # connect to database
query = conn.execute(
"""select lastname, firstname, midname, student_id from public.v_student_course_names
where course_id = {};""".format(course_id)
) # This line performs query and returns json result
result = {
'data':
[dict(zip(tuple(query.keys()), i)) for i in query.cursor]
}
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
sql_request = """insert into public.message(chain_num, from_id, to_id, message)
select distinct chain_num, from_id, {} as to_id, '{}' as message from message
where chain_num = {} and from_id <> {}
;""".format(json['user_id'], json['message'], json['chain_num'],
json['user_id'])
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self, mes_id):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
sql_request2 = """
update public.notification
set is_read = true
where id = {};
""".format(mes_id)
logging.info('Request to DB: {}'.format(sql_request2))
query2 = conn.execute(sql_request2)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self):
try:
conn = DBConn.conn().connect() # connect to database
sql_request = "select * from public.lecturer;"
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(
sql_request
) # This line performs query and returns json result
result = {
'data':
[dict(zip(tuple(query.keys()), i)) for i in query.cursor]
}
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
sql_request = """insert into public.homeworks(course_id, student_id, week_num, hw_url)
values({}, {}, {}, '{}');""".format(json['course_id'],
json['student_id'],
json['week_num'],
json['hw_url'])
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
sql_request = """insert into public.course_notification(course_id, notification) values({},'{}');
insert into public.student_notifications(student_id, from_who, from_id, notification)
select student_id, 'course', {}, '{}' from public.course_students where course_id = {};""".format(
json['course_id'], json['notification'], json['course_id'],
json['notification'], json['course_id'])
conn = DBConn.conn().connect()
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def put(self, lec_id):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
sql_request = ''
for x in json.keys():
sql_request = ", " + str(x) + " = '" + str(json[x]) + "'" + sql_request
sql_request = sql_request + " where id = {};".format(lec_id)
sql_request = 'update public.lecturer set ' + sql_request[2:]
conn = DBConn.conn().connect()
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self, lec_id):
try:
conn = DBConn.conn().connect()
sql_request = """delete from public.schedule0
where lec_id = {} and is_app = true;
update public.schedule0
set is_app = true
where lec_id = {};
insert into public.notification( from_role, to_id, notification)
select 'admin' as from_role, {} as to_id, 'Ваше расписание одобрено администрацией' as notification;""".format(
lec_id, lec_id, lec_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, lec_id):
try:
conn = DBConn.conn().connect() # connect to database
sql_request = """select c.id, "name", imgurl, info, c.lec_id,
s.islecture, s.isseminar, s.day_cd, s."time" , s.place,
concat(coalesce(lastname, ''), ' ', coalesce(firstname , ''), ' ', coalesce(midname , '') ) as teacher from public.course c
left join schedule0 s on c.id = s.course_id and s.is_app
left join lecturer l on l.id = c.lec_id
where c.lec_id = {}; """.format(lec_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(
sql_request
) # This line performs query and returns json result
result = [dict(zip(tuple(query.keys()), i)) for i in query.cursor]
grouped = defaultdict(list)
for item in result:
grouped[item['id'], item['name'], item['imgurl'], item['info'],
item['lec_id'], item['teacher']].append(item)
res = []
#print(grouped.items())
for course, sched in grouped.items():
#print(course)
res0 = {
'id': course[0],
'name': course[1],
'imgurl': course[2],
'info': course[3],
'lec_id': course[4],
'teacher': course[5]
}
for k in sched:
k.pop('id')
k.pop('name')
k.pop('imgurl')
k.pop('info')
k.pop('lec_id')
k.pop('teacher')
res0.update({'schedule': sched})
res.append(res0)
res = {'data': res}
return jsonify(res)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self, lec_id):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
sql_request = """delete from public.schedule0
where lec_id = {} and is_app = false;
update public.schedule0
set "comment" = '{}'
where lec_id = {};
insert into public.notification( from_role, to_id, notification)
select 'admin' as from_role, {} as to_id, 'Ваше расписание не одобрено администрацией по причине: {}' as notification;""".format(
lec_id, json['comment'], lec_id, lec_id, json['comment'])
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, lec_id):
try:
conn = DBConn.conn().connect() # connect to database
sql_request = """select schedule_id, c."name", s.lec_id, s.course_id, day_cd, "time", place, islecture, isseminar from public.schedule0 s
join public.course c
on c.id = s.course_id
where s.lec_id = {} and is_app;""".format(lec_id)
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(
sql_request
) # This line performs query and returns json result
result = {
'schedule':
[dict(zip(tuple(query.keys()), i)) for i in query.cursor]
}
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def post(self):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
conn = DBConn.conn().connect()
user_name = ''
sql_request = """insert into public.message(from_id, from_role, from_name, to_id, message, chain_num)
select s.id as from_id, 'student' as from_role, concat(coalesce(lastname, ''), ' ', coalesce(firstname , ''), ' ', coalesce(midname , '') ) as from_name,
c.lec_id as to_id, '{}' as message, m.chain_num + 1
from public.student s
join public.course c
on 1 = 1 and s.id = {} and c.id = {}
join (select coalesce(max(chain_num), 0) as chain_num from public.message) m on 1=1;
""".format(json['message'],json['student_id'], json['course_id'])
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def get(self, student_id):
try:
conn = DBConn.conn().connect()
query1 = "select * from public.student_notifications where student_id = {}; ".format(
student_id)
query2 = "update public.student_notifications set is_read = true where student_id = {}; ".format(
student_id)
logging.info('Request to DB: {}'.format(query1))
query = conn.execute(
query1) # This line performs query and returns json result
result = {
'data':
[dict(zip(tuple(query.keys()), i)) for i in query.cursor]
}
logging.info('Request to DB: {}'.format(query2))
query = conn.execute(query2)
return jsonify(result)
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
def put(self, student_id):
try:
json = request.get_json()
logging.info('Request: {}'.format(json))
#sql_request = 'update public.student(' + ', '.join((str(x) for x in json.keys())) + ') values(\'' + '\', \''.join((str(x) for x in json.values())) + '\')'
sql_request = ''
for x in json.keys():
sql_request = ", " + str(x) + " = '" + str(
json[x]) + "'" + sql_request
#if (x == "id"):
# sql_request = sql_request + " where id = " + str(json[x]) + ";"
#else:
# sql_request = ", " + str(x) + " = '" + str(json[x]) + "'" + sql_request
sql_request = sql_request + " where id = {};".format(student_id)
sql_request = 'update public.student set ' + sql_request[2:]
conn = DBConn.conn().connect()
logging.info('Request to DB: {}'.format(sql_request))
query = conn.execute(sql_request)
return 'OK'
except Exception as e:
logging.info('Exception: {}'.format(e))
return 'Exception: {}'.format(e), 500
