def post(self):
parser = reqparse.RequestParser()
parser.add_argument('request_no',
type=int,
required=True,
help="request_no cannot be left blank!")
parser.add_argument('image',
type=str,
required=True,
help="image cannot be left blank!")
parser.add_argument('uname',
type=str,
required=True,
help="uname cannot be left blank!")
data = parser.parse_args()
# a transaction is made, so not using query function from db module
# we use connectToHost function from db module and commit explicitly
# the query function from db module commits for each query which is not desirable in
# a transaction sequence as follows.
# here we execute several queries then commit.
try:
connection = connectToHost()
#start connection, create cursor and execute query from cursor
connection.begin()
cursor = connection.cursor()
#check if any paper got approved before allowing to perform action
qstr = f"""
select count(request_no) from requests
where request_no = { data['request_no'] } and select_status = 1;
"""
cursor.execute(qstr)
result = cursor.fetchall()
approved_count = list(result[0].values())[0]
if approved_count > 0:
return {
"message":
"""Cannot perform action. Admin accepted some paper already.
The paper you uploaded is either approved or got deleted."""
}, 400
#perform update if any paper did not get approved
qstr = f"""
SELECT r_id FROM User.submissions WHERE
request_no = "{ data['request_no'] }" AND
uname = "{ data['uname'] }" LIMIT 1;
"""
cursor.execute(qstr)
result = cursor.fetchall()
insert_rid = list(result[0].values())[0]
#print(data)
print(qstr)
print(insert_rid)
qstr = f""" UPDATE requests
SET image = (%s)
WHERE request_no = '{ data['request_no'] }' AND
r_id = '{ insert_rid }';"""
print(qstr)
#creating a tuple of values to be inserted because a formatted string is used
#here its useful to avoid SQL syntax errors while inserting BLOB value into table
vals_tuple = (convertToBlob(data['image']))
#convertToBlob is used to convert base64 string to BLOB data
cursor.execute(qstr, vals_tuple)
connection.commit() #commit the changes made
#close the cursor and connection
cursor.close()
connection.close()
except IndexError:
"""
this is to handle tuple index error
which is raised if no data could be retrieved and stored
where data is retrieved in this way
result = cursor.fetchall()
req_no = list(result[0].values())[0]
"""
return {"message": "Required data not present."}, 400
except (pymysql.err.InternalError, pymysql.err.ProgrammingError,
pymysql.err.IntegrityError) as e:
return {"message": "MySQL error: " + str(e)}, 500
except Exception as e:
return {
"message":
"There was an error connecting to the requests table while inserting."
+ str(e)
}, 500
return {"message": "Succesfully inserted"}, 200
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('request_no',
type=int,
required=True,
help="request_no cannot be left blank!")
data = parser.parse_args()
# a transaction is made, so not using query function from db module
# we use connectToHost function from db module and commit explicitly
# the query function from db module commits for each query which is not desirable in
# a transaction sequence as follows.
# here we execute several queries then commit.
try:
connection = connectToHost()
#start connection, create cursor and execute query from cursor
connection.begin()
cursor = connection.cursor()
# obtain d_id for the provided request_no, so that we can delete
# an entry in details if no other rows in timetable have have the same d_id
qstr = f"""SELECT d_id from timetable
where request_no = "{ data['request_no'] }";"""
cursor.execute(qstr)
result = cursor.fetchall()
del_did = list(result[0].values())[0]
# delete requests entries with the request_no
qstr = f"""delete from requests
where request_no = "{ data['request_no'] }";"""
cursor.execute(qstr)
# delete timetable entry with the request_no
qstr = f"""delete from timetable
where request_no = "{ data['request_no'] }";"""
cursor.execute(qstr)
# delete an entry in details if no other row in timetable table has d_id
qstr = f"""delete from details
where d_id = "{ del_did }"
AND NOT EXISTS (select d_id from timetable where d_id = "{ del_did }");"""
cursor.execute(qstr)
# deleting the request_no from active exams is taken care by on delete cascade
# the bottom commented code is if the deletion by on delete cascade is not working.
# qstr = f"""
# DELETE FROM active_exams
# where request_no = '{ data['request_no'] }'
# """
# cursor.execute(qstr)
connection.commit() #commit the changes made
#close the cursor and connection
cursor.close()
connection.close()
except IndexError:
"""
this is to handle tuple index error
which is raised if no data could be retrieved and stored
where data is retrieved in this way
result = cursor.fetchall()
req_no = list(result[0].values())[0]
"""
return {"message": "Required data not present."}, 400
except (pymysql.err.InternalError, pymysql.err.ProgrammingError,
pymysql.err.IntegrityError) as e:
return {"message": "MySQL error: " + str(e)}, 500
except Exception as e:
return {
"message":
"There was an error connecting to the requests table while inserting."
+ str(e)
}, 500
return {"message": "Succesfully deleted."}, 200
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('b_id',
type=int,
required=True,
help="b_id cannot be left blank!")
parser.add_argument('sem_no',
type=int,
required=True,
help="sem_no cannot be left blank!")
parser.add_argument('exam_type',
type=str,
required=True,
help="exam_type cannot be left blank!")
parser.add_argument('subtype',
type=str,
required=True,
help="request_no cannot be left blank!")
parser.add_argument('s_code',
type=str,
required=True,
help="s_code cannot be left blank!")
parser.add_argument('year',
type=int,
required=True,
help="year cannot be left blank!")
parser.add_argument('image',
type=str,
required=True,
help="image cannot be left blank!")
#parser.add_argument('select_status', type=int, required=False, default = 0)
data = parser.parse_args()
# a transaction is made, so not using query function from db module
# we use connectToHost function from db module and commit explicitly
# the query function from db module commits for each query which is not desirable in
# a transaction sequence as follows.
# here we execute several queries then commit.
try:
connection = connectToHost()
#start connection, create cursor and execute query from cursor
connection.begin()
cursor = connection.cursor()
#obtain request_no from the details provided, store in req_no
qstr = f"""
select DISTINCT request_no
from timetable t
inner join details d on (t.d_id = d.d_id)
WHERE b_id = '{data['b_id']}' AND
sem_no = '{data['sem_no']}' AND
exam_type = '{data['exam_type']}' AND
subtype = '{data['subtype']}' AND
year = '{data['year']}' AND
s_code = '{data['s_code']}'
LIMIT 1;
"""
cursor.execute(qstr)
cursor.execute(qstr)
result = cursor.fetchall()
req_no = list(result[0].values())[0]
# delete all the other entries in requests table
# with the same request_no, whether selected or not
qstr = f"""
delete from requests
where request_no = {req_no};
"""
cursor.execute(qstr)
#creating a tuple of values to be inserted because a formatted string is used
#here its useful to avoid SQL syntax errors while inserting BLOB value into table
vals_tuple = (req_no, convertToBlob(data['image']), 1
) #set select status to 1
#convertToBlob is used to convert base64 string to BLOB data
qstr = f""" INSERT INTO requests (request_no, image, select_status)
values (%s, %s, %s); """
cursor.execute(qstr, vals_tuple)
# delete the corresponding entry from active_exams if paper is uploaded.
qstr = f""" DELETE FROM active_exams
WHERE request_no = { req_no }; """
cursor.execute(qstr)
# for deleting user info from submissions table
qstr = f""" DELETE FROM User.submissions
WHERE request_no = { req_no };
"""
cursor.execute(qstr)
connection.commit() #commit the changes made
#close the cursor and connection
cursor.close()
connection.close()
except IndexError:
"""
this is to handle tuple index error
which is raised if no data could be retrieved and stored
where data is retrieved in this way
result = cursor.fetchall()
req_no = list(result[0].values())[0]
"""
return {"message": "Required data not present."}, 400
except (pymysql.err.InternalError, pymysql.err.ProgrammingError,
pymysql.err.IntegrityError) as e:
return {"message": "MySQL error: " + str(e)}, 500
except Exception as e:
return {
"message":
"There was an error connecting to the requests table while inserting."
+ str(e)
}, 500
return {"message": "Succesfully inserted"}, 200
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('request_no', type=int, required=True, help="request_no cannot be left blank!")
parser.add_argument('b_id', type=int, required=True, help="b_id cannot be left blank!")
parser.add_argument('s_code', type=str, required=True, help="s_code cannot be left blank!")
parser.add_argument('exam_type', type=str, required=True, help="exam_type cannot be left blank!")
parser.add_argument('subtype', type=str, required=True, help="subtype cannot be left blank!")
parser.add_argument('start_at', type=str, required=True, help="start_at cannot be left blank!")
parser.add_argument('end_at', type=str, required=True, help="end_at cannot be left blank!")
parser.add_argument('date', type=str, required=True, help="date cannot be left blank!")
parser.add_argument('year', type=int, required=True, help="year cannot be left blank!")
parser.add_argument('sem_no', type=int, required=True, help="sem_no cannot be left blank!")
data = parser.parse_args()
# a transaction is made, so not using query function from db module
# we use connectToHost function from db module and commit explicitly
# the query function from db module commits for each query which is not desirable in
# a transaction sequence as follows.
# here we execute several queries then commit.
try:
connection = connectToHost()
#start connection, create cursor and execute query from cursor
connection.begin()
cursor = connection.cursor()
# obtain the d_id so that if the current timings change, d_id for the row which is to
# be updated should have d_id changed.
qstr = f"""SELECT d_id from timetable where request_no = "{ data['request_no'] }";"""
cursor.execute(qstr)
result = cursor.fetchall()
old_did = list(result[0].values())[0]
# the timings sent in the request are added if not already there in the details table
qstr = f"""INSERT INTO details (start_at, end_at, date, year, sem_no)
SELECT * FROM
(SELECT "{ data['start_at'] }" as s,
"{ data['end_at'] }" as e,
"{ data['date'] }" as d,
"{ data['year'] }" as y,
"{ data['sem_no'] }" as se) AS TEMP
WHERE NOT EXISTS(
SELECT d_id from details
where start_at = " {data['start_at']} " AND
end_at = "{ data['end_at'] }" AND
date = "{ data['date'] }" AND
sem_no = "{ data['sem_no'] }"
) LIMIT 1 ;"""
cursor.execute(qstr)
# the d_id with the timings is obtained from the table comparing with the timings provided
# in the json
qstr = f"""
SELECT d_id from details
where start_at = " {data['start_at']} " AND
end_at = "{ data['end_at'] }" AND
date = "{ data['date'] }" AND
sem_no = "{ data['sem_no'] }" ;
"""
#lets call the d_id as new_did, it could be the same d_id as before if timings aren't updated.
cursor.execute(qstr)
result = cursor.fetchall()
new_did = list(result[0].values())[0]
#update timetable with the fields provided
qstr = f"""
update timetable
set d_id = "{ new_did }",
exam_type = "{ data['exam_type'] }",
subtype = "{ data['subtype'] }" ,
s_code = "{ data['s_code'] }" ,
b_id = { data['b_id'] }
where request_no = "{ data['request_no'] }";
"""
cursor.execute(qstr)
# if timings are changed, then old_did and new_did will differ.
# so delete the old_did if no other row has old_did value in d_id column
# of timetable table.
if old_did != new_did:
qstr = f"""
delete from details
where d_id = "{ old_did }"
AND NOT EXISTS (select d_id from timetable where d_id = "{ old_did }");
"""
cursor.execute(qstr)
# for active_exams table
qstr = f"""
update active_exams
set branch_name = (SELECT branch_name from branch where b_id = '{ data['b_id'] }') ,
subject_name = (SELECT subject_name from subject where s_code = '{ data['s_code'] }') ,
exam_type = '{ data['exam_type'] }' ,
subtype = '{ data['subtype'] }' ,
end_at = '{ data['end_at'] }' ,
date = '{ data['date'] }' ,
sem_no = '{ data['sem_no'] }'
where request_no = "{ data['request_no'] }"
"""
cursor.execute(qstr)
connection.commit() #commit the changes made
#close the cursor and connection
cursor.close()
connection.close()
except IndexError:
"""
this is to handle tuple index error
which is raised if no data could be retrieved and stored
where data is retrieved in this way
result = cursor.fetchall()
req_no = list(result[0].values())[0]
"""
return {
"message" : "Required data not present."
}, 400
except (pymysql.err.InternalError, pymysql.err.ProgrammingError, pymysql.err.IntegrityError) as e:
return {
"message" : "MySQL error: " + str(e)
}, 500
except Exception as e:
return {
"message" : "There was an error connecting to the requests table while inserting." + str(e)
}, 500
return {
"message" : "Succesfully updated."
}, 200
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('b_id', type=int, required=True, help="b_id cannot be left blank!")
#parser.add_argument('d_id', type=int, required=True, help="d_id cannot be left blank!")
parser.add_argument('s_code', type=str, required=True, help="s_code cannot be left blank!")
parser.add_argument('exam_type', type=str, required=True, help="exam_type cannot be left blank!")
parser.add_argument('subtype', type=str, required=True, help="subtype cannot be left blank!")
parser.add_argument('start_at', type=str, required=True, help="start_at cannot be left blank!")
parser.add_argument('end_at', type=str, required=True, help="end_at cannot be left blank!")
parser.add_argument('date', type=str, required=True, help="date cannot be left blank!")
parser.add_argument('year', type=int, required=True, help="year cannot be left blank!")
parser.add_argument('sem_no', type=int, required=True, help="sem_no cannot be left blank!")
# parser.add_argument('subject_name', type=str, required=True, help="subject_name cannot be left blank!")
data = parser.parse_args()
# a transaction is made, so not using query function from db module
# we use connectToHost function from db module and commit explicitly
# the query function from db module commits for each query which is not desirable in
# a transaction sequence as follows.
# here we execute several queries then commit.
try:
connection = connectToHost()
#start connection, create cursor and execute query from cursor
connection.begin()
cursor = connection.cursor()
#insert into details if not present already
qstr = f"""
INSERT INTO details (start_at, end_at, date, year, sem_no)
SELECT * FROM (SELECT '{data['start_at']}' as st,
'{data['end_at']}' as en,
'{data['date']}' as da,
'{data['year']}' as ye ,
'{data['sem_no']}' as se) as temp
WHERE NOT EXISTS (
SELECT d_id FROM details WHERE
start_at = '{data['start_at']}' AND
end_at = '{data['end_at']}' AND
date = '{data['date']}' AND
year = '{data['year']}' AND
sem_no = '{data['sem_no']}'
) LIMIT 1;
"""
cursor.execute(qstr)
# obtain d_id of the timings given, the query returns the old value of d_id if timings and sem no
# didnt change and returns new value of d_id if timings are updated
qstr = f"""
SELECT d_id FROM details WHERE
start_at = '{data['start_at']}' AND
end_at = '{data['end_at']}' AND
date = '{data['date']}' AND
year = '{data['year']}' AND
sem_no = '{data['sem_no']}';
"""
cursor.execute(qstr)
result = cursor.fetchall()
did = list(result[0].values())[0]
# insert the timetable into the timetable table if its not already present.
qstr = f"""
INSERT INTO timetable (b_id, d_id, s_code, exam_type, subtype)
SELECT * FROM (
SELECT '{data['b_id']}' as b,
'{did}' as d,
'{data['s_code']}' as s,
'{data['exam_type']}' as ex,
'{data['subtype']}' as su) AS TEMP
WHERE NOT EXISTS (
SELECT request_no FROM timetable WHERE
b_id = '{data['b_id']}' AND
d_id = '{did}' AND
s_code = '{data['s_code']}' AND
exam_type = '{data['exam_type']}' AND
subtype = '{data['subtype']}'
) LIMIT 1;
"""
cursor.execute(qstr)
#for active_exams table
qstr = f"""SELECT subject_name FROM subject
WHERE s_code = '{ data['s_code'] }';"""
cursor.execute(qstr)
result = cursor.fetchall()
sname = list(result[0].values())[0]
qstr = f"""SELECT branch_name FROM branch
WHERE b_id = '{ data['b_id'] }';"""
cursor.execute(qstr)
result = cursor.fetchall()
bname = list(result[0].values())[0]
qstr = f"""SELECT request_no FROM timetable WHERE
b_id = '{ data['b_id'] }' AND
s_code = '{ data['s_code'] }' AND
exam_type = '{ data['exam_type'] }' AND
subtype = '{ data['subtype'] }' AND
d_id = '{ did }' ;"""
cursor.execute(qstr)
result = cursor.fetchall()
reqno = list(result[0].values())[0]
qstr = f"""
INSERT INTO active_exams
SELECT * FROM (
SELECT '{ reqno }' as r,
'{ bname }' as b,
'{ sname }' as s,
'{ data['exam_type'] }' as e,
'{ data['subtype'] }' as su,
'{ data['end_at'] }' as en,
'{ data['date'] }' as d,
'{ data['sem_no'] }' as se) AS TEMP
WHERE NOT EXISTS (
SELECT request_no FROM active_exams WHERE
branch_name = '{ bname }' AND
subject_name = '{ sname }' AND
exam_type = '{ data['exam_type'] }' AND
subtype = '{ data['subtype'] }' AND
end_at = '{ data['end_at'] }' AND
date = '{ data['date'] }' AND
sem_no = '{ data['sem_no'] }'
) LIMIT 1;
"""
cursor.execute(qstr)
connection.commit() #commit the changes made
#close the cursor and connection
cursor.close()
connection.close()
except IndexError:
"""
this is to handle tuple index error
which is raised if no data could be retrieved and stored
where data is retrieved in this way
result = cursor.fetchall()
req_no = list(result[0].values())[0]
"""
return {
"message" : "Required data not present."
}, 400
except (pymysql.err.InternalError, pymysql.err.ProgrammingError, pymysql.err.IntegrityError) as e:
return {
"message" : "MySQL error: " + str(e)
}, 500
except Exception as e:
return {
"message" : "There was an error connecting to the requests table while inserting." + str(e)
}, 500
return {
"message" : "Succesfully inserted"
}, 200
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('request_no',
type=int,
required=True,
help="request_no cannot be left blank!")
parser.add_argument('r_id',
type=int,
required=True,
help="r_id cannot be left blank!")
data = parser.parse_args()
# a transaction is made, so not using query function from db module
# we use connectToHost function from db module and commit explicitly
# the query function from db module commits for each query which is not desirable in
# a transaction sequence as follows.
# here we execute several queries then commit.
try:
connection = connectToHost()
#start connection, create cursor and execute query from cursor
connection.begin()
cursor = connection.cursor()
qstr = f""" DELETE FROM requests
WHERE request_no = { data['request_no'] } AND
r_id <> { data['r_id'] } AND
select_status = 0; """
cursor.execute(qstr)
qstr = f""" UPDATE requests
SET select_status = 1
WHERE r_id = { data['r_id'] };
"""
cursor.execute(qstr)
# delete the corresponding entry from active_exams if paper is accepted.
qstr = f""" DELETE FROM active_exams
WHERE request_no = { data['request_no'] }; """
cursor.execute(qstr)
# for deleting user info from submissions table
qstr = f""" DELETE FROM User.submissions
WHERE request_no = { data['request_no'] };
"""
cursor.execute(qstr)
connection.commit() #commit the changes made
#close the cursor and connection
cursor.close()
connection.close()
except (pymysql.err.InternalError, pymysql.err.ProgrammingError,
pymysql.err.IntegrityError) as e:
return {"message": "MySQL error: " + str(e)}, 500
except Exception as e:
return {
"message":
"There was an error connecting to the requests table while inserting."
+ str(e)
}, 500
return {"message": "Succesfully deleted."}, 200