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