Ejemplo n.º 1
0
    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
Ejemplo n.º 2
0
 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
Ejemplo n.º 3
0
    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
Ejemplo n.º 4
0
    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
Ejemplo n.º 5
0
 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
Ejemplo n.º 6
0
    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
Ejemplo n.º 7
0
 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
Ejemplo n.º 8
0
 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
Ejemplo n.º 9
0
 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
Ejemplo n.º 10
0
 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
Ejemplo n.º 11
0
    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
Ejemplo n.º 12
0
 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
Ejemplo n.º 13
0
 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
Ejemplo n.º 14
0
 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
Ejemplo n.º 15
0
    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
Ejemplo n.º 16
0
 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
Ejemplo n.º 17
0
 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
Ejemplo n.º 18
0
    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
Ejemplo n.º 19
0
    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
Ejemplo n.º 20
0
 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
Ejemplo n.º 21
0
 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
Ejemplo n.º 23
0
 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
Ejemplo n.º 24
0
    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
Ejemplo n.º 25
0
    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
Ejemplo n.º 26
0
    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
Ejemplo n.º 27
0
 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
Ejemplo n.º 28
0
    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
Ejemplo n.º 29
0
 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
Ejemplo n.º 30
0
 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