def get_latest_teachers_date(_groups, date_original, date_plus20):
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = 'SELECT teachers_id, date, latitude, longitude, semester, subject, groups FROM ' \
'public.teachers_coordinates ' \
"ORDER BY date DESC LIMIT 1 "
cursor.execute(postgre_sql_select_query)
mobile_records = cursor.fetchall()
for row in mobile_records:
date_original = datetime.strptime(row[1].split('.')[0],
'%Y-%m-%d %H:%M:%S')
date_plus20 = datetime.strptime(row[1].split('.')[0],
'%Y-%m-%d %H:%M:%S')
semester = row[4]
subject = row[5]
_groups = row[6]
date_plus20 = date_plus20 + timedelta(minutes=20)
except (Exception, psycopg2.DatabaseError) as error:
print("Error TEACHERS COORDINATES while doing smth in PostgreSQL",
error)
finally:
# closing database connection.
cursor.close()
connection.close()
return _groups, date_original, date_plus20
def all_dates_t(teacher_id, subject, semester, groups):
global connection, cursor
dates = []
#print(teacher_id, subject)
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = 'SELECT teachers_id, latitude, longitude, date, groups, subject, semester ' \
'FROM public.teachers_coordinates ' \
'WHERE teachers_id = %s AND subject = %s '
# 'AND groups LIKE ' + '\'%%\' ' + '|| %s || ' + '\'%%\'; '
record_tuple = (teacher_id, subject)
cursor.execute(postgre_sql_select_query, record_tuple)
mobile_records = cursor.fetchall()
for row in mobile_records:
dates.append([row[3], row[4]])
except (Exception, psycopg2.DatabaseError) as error:
print("Error all_dates_t while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
#print('-'*40)
#print(dates)
#print('-' * 40)
return dates
def all_students_s(groups):
global connection, cursor
'''
@:param students
- array of students data set
[student_id,first_name, second_name, group]
'''
#print(groups)
students = []
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = 'SELECT st.student_id,st.first_name, st.second_name, st.username, st.email, ' \
'st."group", st.github ' \
'FROM public.students as st ' \
'WHERE ' \
"%s LIKE " + '\'%%\' ' + ' || st."group" || ' + '\'%%\';'
record_tuple = (groups, )
cursor.execute(postgre_sql_select_query, record_tuple)
mobile_records = cursor.fetchall()
for row in mobile_records:
students.append([row[0], row[1], row[2], row[5]])
except (Exception, psycopg2.DatabaseError) as error:
print("Error all_students while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
#print('-' * 40)
#print(students)
#print('-' * 40)
return students
def get_teachers_location(request):
# get teachers Id
teacher_id = 0
#print(request.POST['email'])
try:
connection = get_sql_connection()
cursor = connection.cursor()
email = request.POST['email']
postgre_sql_select_query = "SELECT * FROM public.teachers WHERE public.teachers.email=%s"
cursor.execute(postgre_sql_select_query, (email, ))
mobile_records = cursor.fetchall()
for row in mobile_records:
teacher_id = row[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
try:
connection = get_sql_connection()
cursor = connection.cursor()
create_table_query = ''' INSERT INTO public.teachers_coordinates(
teachers_id, "date", latitude, longitude, groups, subject, semester)
VALUES (%s, %s, %s, %s, %s, %s, %s);
'''
#print(request.POST.getlist('group[]'))
groups = ','.join(request.POST.getlist('groups[]'))
#print('-' * 50)
#print(groups)
#print('-' * 50)
record_tuple = (teacher_id, datetime.now(), request.POST['latitude'],
request.POST['longitude'], groups,
request.POST.get('subject'),
int(str(request.POST.get('semester')).split()[1]))
cursor.execute(create_table_query, record_tuple)
connection.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
return render(request, 'home_teacher.html')
def add_teacher(first_name, second_name, groups, email, faculty, username,
password):
try:
connection = get_sql_connection()
cursor = connection.cursor()
create_table_query = ''' INSERT INTO public.teachers(
first_name, second_name, email, faculty, password, username)
VALUES (%s, %s, %s, %s, %s, %s);
'''
record_tuple = (first_name, second_name, email, faculty, password,
username)
cursor.execute(create_table_query, record_tuple)
connection.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
teacher_id = 0
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = "SELECT teacher_id FROM public.teachers ORDER BY teacher_id DESC LIMIT 1"
cursor.execute(postgre_sql_select_query)
mobile_records = cursor.fetchall()
for row in mobile_records:
teacher_id = row[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
add_schedule(teacher_id, groups, "SQL")
def students_on_lecture(date, st_groups):
# dates = last_teachers_date()
global cursor, connection
date = datetime.strptime(date.split('.')[0], '%Y-%m-%d %H:%M:%S')
dates = [date, date + timedelta(minutes=20)]
#print(dates)
#print(st_groups)
students = []
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgres_sql_select_query = 'SELECT att.student_id, att.date, att.latitude, att.longitude, st.first_name, ' \
'st.second_name, st."group" FROM public.attendance as att INNER JOIN ' \
'public.students as st ON ' \
'st.student_id=att.student_id AND att.date::timestamp BETWEEN %s::timestamp ' \
'AND ' \
'%s::timestamp AND %s LIKE ' + '\'%%\' ' + '|| st."group" || ' + '\'%%\'; '
#print(dates[1])
record_tuple = (dates[0], dates[1], st_groups)
cursor.execute(postgres_sql_select_query, record_tuple)
mobile_records = cursor.fetchall()
number_of_students = 0
#print('^' * 40)
#print(mobile_records)
#print('^' * 40)
#print(postgres_sql_select_query)
for row in mobile_records:
st = Student()
st.number = number_of_students + 1
st.date = row[1]
st.latitude = row[2]
st.longitude = row[3]
st.first_name = row[4]
st.second_name = row[5]
st.group = row[6]
number_of_students += 1
students.append(st)
except (Exception, psycopg2.DatabaseError) as error:
print("Error students_on_lecture while doing smth in PostgreSQL", error)
student_or_teacher = 1
finally:
# closing database connection.
cursor.close()
connection.close()
#print("PostgreSQL count_number_os_students connection is closed")
return students
def add_subject(request):
"""
Функция отображения для домашней страницы сайта.
"""
global cursor, connection
#print(request.user)
semester = str(request.POST.get('semester')).split()
subject = request.POST.get('subject')
teacher_id = get_teachers_id(str(request.user))
lst_groups = request.POST.getlist('groups')
for group in lst_groups:
from Attendance.controllers.add.group import get_schedule_id
schedule_id = get_schedule_id(teacher_id=teacher_id,
group=group,
semester=semester[1])
try:
connection = get_sql_connection()
cursor = connection.cursor()
if schedule_id == -1:
create_table_query = ''' INSERT INTO public.schedule(
teacher_id, "group", semester)
VALUES (%s, %s, %s);
'''
record_tuple = (teacher_id, group, semester[1])
cursor.execute(create_table_query, record_tuple)
connection.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
if len(subject) > 0:
schedule_id = get_schedule_id(teacher_id=teacher_id,
group=group,
semester=semester[1])
if schedule_id == -1:
pass
else:
insert_into_subjects(schedule_id, subject, semester[1])
from Attendance.views import home_teacher
return home_teacher(request)
def get_teachers_id(username):
teachers_id = 0
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = "SELECT * FROM public.teachers WHERE username=%s"
cursor.execute(postgre_sql_select_query, (username, ))
mobile_records = cursor.fetchall()
for row in mobile_records:
teachers_id = row[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error TEACHERS while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
#print("PostgreSQL TEACHERS connection is closed")
return teachers_id
def get_student_groups():
global connection, cursor
all_groups = []
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = 'SELECT "group" FROM public.students;'
cursor.execute(postgre_sql_select_query, )
mobile_records = cursor.fetchall()
for row in mobile_records:
all_groups.append(row[0])
except (Exception, psycopg2.DatabaseError) as error:
print("Error get_student_groups while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
#print("PostgreSQL get_student_groups connection is closed")
return all_groups
def add_student(first_name, second_name, group, email, github, username,
password):
try:
connection = get_sql_connection()
cursor = connection.cursor()
create_table_query = ''' INSERT INTO public.students(
first_name, second_name, email, "group", github, password, username)
VALUES (%s, %s, %s, %s, %s, %s, %s);
'''
record_tuple = (first_name, second_name, email, group, github,
password, username)
cursor.execute(create_table_query, record_tuple)
connection.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
def get_students_id(user):
student_id = 0
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = "SELECT student_id, username FROM public.students WHERE username=%s"
cursor.execute(postgre_sql_select_query, (user, ))
mobile_records = cursor.fetchall()
#print(mobile_records)
for row in mobile_records:
student_id = row[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error STUDENTS while doing smth in PostgreSQL", error)
student_or_teacher = 1
finally:
# closing database connection.
cursor.close()
connection.close()
return student_id
def subjects_one(schedule_id):
subject = []
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = 'SELECT id, subject FROM public.subjects WHERE schedule_id=%s'
#print(postgre_sql_select_query)
cursor.execute(postgre_sql_select_query, (schedule_id, ))
mobile_records = cursor.fetchall()
for row in mobile_records:
subject.append(row[1])
except (Exception, psycopg2.DatabaseError) as error:
print("Error subjects while doing smth in PostgreSQL", error)
student_or_teacher = 1
finally:
# closing database connection.
cursor.close()
connection.close()
return subject
def group_ids(teacher_id):
all_group_ids = []
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = 'SELECT id, "group" FROM public.schedule WHERE teacher_id=%s'
#print(postgre_sql_select_query)
cursor.execute(postgre_sql_select_query, (teacher_id, ))
mobile_records = cursor.fetchall()
for row in mobile_records:
all_group_ids.append(row[0])
except (Exception, psycopg2.DatabaseError) as error:
print("Error STUDENTS while doing smth in PostgreSQL", error)
student_or_teacher = 1
finally:
# closing database connection.
cursor.close()
connection.close()
return all_group_ids
def insert_into_subjects(schedule_id, subject, semester):
global cursor, connection
#print('INSERTING SUBJECTS')
try:
connection = get_sql_connection()
cursor = connection.cursor()
create_table_query = ''' INSERT INTO public.subjects(
schedule_id, subject, semester)
VALUES (%s, %s, %s);
'''
record_tuple = (schedule_id, subject, semester)
cursor.execute(create_table_query, record_tuple)
connection.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
def get_schedule_id(teacher_id, group, semester):
global connection, cursor
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = 'SELECT id from public.schedule WHERE teacher_id=%s AND "group"=%s AND semester=%s;'
cursor.execute(
postgre_sql_select_query,
(teacher_id, group, semester),
)
mobile_records = cursor.fetchall()
for row in mobile_records:
return row[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error get_schedule_id while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
#print("PostgreSQL ifStudentOnTheLecture connection is closed")
return -1
def add_schedule(teacher_id, groups, name):
try:
connection = get_sql_connection()
cursor = connection.cursor()
arr_groups = groups.split(',')
#print(arr_groups)
for group in arr_groups:
#print(group)
create_table_query = ''' INSERT INTO public.schedule(
teacher_id, "group", name)
VALUES (%s, %s, %s);
'''
record_tuple = (teacher_id, str(group), str(name))
cursor.execute(create_table_query, record_tuple)
connection.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
def if_student_on_the_lecture(student_id, date_original, date_plus20):
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = "SELECT * FROM public.attendance WHERE student_id=%s"
cursor.execute(postgre_sql_select_query, (student_id, ))
#print("Selecting ifStudentOnTheLecture rows from mobile table using cursor.fetchall")
mobile_records = cursor.fetchall()
for row in mobile_records:
student_id = row[0]
date = datetime.strptime(row[1].split('.')[0], '%Y-%m-%d %H:%M:%S')
if date_original <= date <= date_plus20:
return True
except (Exception, psycopg2.DatabaseError) as error:
print("Error ifStudentOnTheLecture while doing smth in PostgreSQL",
error)
student_or_teacher = 1
finally:
# closing database connection.
cursor.close()
connection.close()
#print("PostgreSQL ifStudentOnTheLecture connection is closed")
return False
def get_students_location(request):
# get teachers Id
#print(request.user)
student_id = get_students_id(str(request.user))
student_arr = []
date_original = datetime.now()
date_plus20 = datetime.now()
# get last teachers visit
latitude = 0
longitude = 0
try:
connection = get_sql_connection()
cursor = connection.cursor()
postgre_sql_select_query = "SELECT teachers_id, date, latitude, longitude FROM public.teachers_coordinates " \
"ORDER BY date DESC LIMIT 1 "
cursor.execute(postgre_sql_select_query)
mobile_records = cursor.fetchall()
for row in mobile_records:
teacher_id = row[0]
latitude = row[2]
longitude = row[3]
date_original = datetime.strptime(row[1].split('.')[0],
'%Y-%m-%d %H:%M:%S')
date_plus20 = datetime.strptime(row[1].split('.')[0],
'%Y-%m-%d %H:%M:%S')
date_plus20 = date_plus20 + timedelta(minutes=20)
except (Exception, psycopg2.DatabaseError) as error:
print("Error getStudentsLocation while doing smth in PostgreSQL",
error)
finally:
# closing database connection.
cursor.close()
connection.close()
#print("PostgreSQL getStudentsLocation connection is closed")
# Check with date now
# Check location difference between
present = datetime.now()
if present > date_plus20 or calculate_location_distance(
request.POST['latitude'], request.POST['longitude'], latitude,
longitude) == False or if_student_on_the_lecture(
student_id, date_original, date_plus20):
return render(request, 'home.html')
try:
connection = get_sql_connection()
cursor = connection.cursor()
create_table_query = ''' INSERT INTO public.attendance(
student_id, date, latitude, longitude)
VALUES (%s, %s, %s, %s);
'''
record_tuple = (
student_id,
datetime.now(),
request.POST['latitude'],
request.POST['longitude'],
)
cursor.execute(create_table_query, record_tuple)
connection.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("Error attendance while doing smth in PostgreSQL", error)
finally:
# closing database connection.
cursor.close()
connection.close()
return render(request, 'home.html')