def company_register():
data = ""
status = ""
request_data = request.get_json()
try:
company_name = request_data['name']
company_email = request_data['email']
company_password = request_data['password']
query = """
insert into company(name,email,password)
values(%s,%s,%s)
"""
conn.execute(query, (company_name, company_email, company_password))
data = "Company registered"
status = "true"
except KeyError:
data = "bad request"
status = "false"
except:
data = "Company Email already used"
status = "false"
return jsonify({
'data': data,
'status': status
})
def select_student():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 2:
# logged in user is not a company
status = "false"
data = "User is not a company"
else:
company_id = session['username']
try:
request_data = request.get_json()
jaf_no = int(request_data['jaf_no'])
rollno = request_data['rollno']
# check if student signed jaf exists
query = """
select count(*)
from signedjafs
where company_id = %s
and jaf_no = %s
and rollno = %s
"""
res = list(conn.execute(query, (company_id, jaf_no, rollno)).first())
if (res[0] == 1):
query = """
update signedjafs
set is_selected = true
where company_id = %s
and jaf_no = %s
and rollno = %s
"""
conn.execute(query, (company_id, jaf_no, rollno))
query1 = """
delete from signedjafs
where rollno = %s
and is_selected = %s
"""
conn.execute(query1, (rollno,False))
status = "true"
data = "successfully selected student"
else:
status = "false"
data = "student hasn't signed up JAF"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def ic_verify_jaf():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 1:
# logged in user is not an IC
status = "false"
data = "User is not an IC"
else:
ic_id = int(session['username'])
try:
request_data = request.get_json()
company_id = int(request_data['company_id'])
jaf_no = int(request_data['jaf_no'])
query = """
select alloted_ic_id, is_verified
from jaf
where company_id = %s
and jaf_no = %s
"""
res = list(conn.execute(query, (company_id, jaf_no)).first())
if (res[0] == ic_id):
if not res[1]:
# verify, update jaf table
query = """
update jaf
set is_verified = true
where company_id = %s and jaf_no = %s
"""
conn.execute(query, (company_id, jaf_no))
status = "true"
data = "verified successfully"
else:
status = "false"
data = "Already verified"
else:
status = "false"
data = "alloted IC is different."
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def pending_resume_verification():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 1:
# logged in user is not an IC
status = "false"
data = "User is not an IC"
else:
try:
query = """
select rollno
from resume
where verified_ic is null;
"""
students = conn.execute(query)
data = []
for row in students:
rollno = row[0]
sub_query = """
select
rollno,
S.name,
S.cpi,
D.name
from student as S, department as D
where S.dept_id = D.id
and S.rollno = %s
"""
res = list(conn.execute(sub_query, (rollno)).first())
data.append({
'rollno': res[0],
'name': res[1],
'cpi': float(res[2]),
'dept_name': res[3]
})
status = "true"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def ic():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 1:
# logged in user is not an IC
status = "false"
data = "User is not an IC"
else:
ic_id = int(session['username'])
query = """
select S.name, I.rollno
from student as S, ic as I
where S.rollno = I.rollno and
I.id = %s
"""
res = list(conn.execute(query, (ic_id, )).first())
data = {
'name': res[0],
'rollno': res[1],
'id': ic_id,
}
status = "true"
return jsonify({
'data': data,
'status': status
})
def company():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 2:
# logged in user is not a company
status = "false"
data = "User is not a company"
else:
company_id = session['username']
try:
query = """
select name,email
from company
where id = %s
"""
res = list(conn.execute(query, (company_id, )).first())
data = {
'name': res[0],
'email': res[1]
}
status = "true"
except:
data = "Database error"
status = "false"
return jsonify({
'data': data,
'status': status
})
def get_departments():
query = """
select *
from department
"""
res = conn.execute(query)
data = []
for row in res:
data.append({
'dept_id': row[0],
'name': row[1]
})
return res
def ic_view_all_jafs():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 1:
# logged in user is not an IC
status = "false"
data = "User is not an IC"
else:
# ic_id = session['username']
try:
query = """
select
company.id,
jaf_no,
jaf.name,
description,
stipend,
cpi_cutoff,
company.name,
is_verified
from jaf, company
where jaf.company_id = company.id
"""
res = conn.execute(query)
data = []
for row in res:
eligible_departments = []
sub_query = """
with ed as
(
select id, name, true as is_eligible
from eligibility join department on dept_id = id
where company_id = %s
and jaf_no = %s
)
select id, name,
case
when is_eligible IS null then false
else is_eligible
end
from department natural left join ed
"""
sub_res = conn.execute(sub_query, (int(row[0]), int(row[1])))
for dept in sub_res:
eligible_departments.append({
'dept_id': int(dept[0]),
'name': dept[1],
'is_eligible': dept[2]
})
data.append({
'company_id': row[0],
'company_name': row[6],
'jaf_no': row[1],
'jaf_name': row[2],
'description': row[3],
'stipend': row[4],
'cpi_cutoff': float(row[5]),
'is_verified': row[7],
'eligible_departments': eligible_departments
})
status = "true"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def ic_view_jaf():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 1:
# logged in user is not an IC
status = "false"
data = "User is not an IC"
else:
ic_id = int(session['username'])
try:
request_data = request.get_json()
company_id = int(request_data['company_id'])
jaf_no = int(request_data['jaf_no'])
query = """
with jafs as
(select *
from jaf
where alloted_ic_id = %s)
select
company.id,
jaf_no,
jafs.name,
description,
stipend,
cpi_cutoff,
company.name,
is_verified
from jafs, company
where jafs.company_id = company.id
and jaf_no = %s
and company_id = %s
"""
res = list(conn.execute(query, (ic_id, jaf_no, company_id)).first())
eligible_departments = []
sub_query = """
with ed as
(
select id, name, true as is_eligible
from eligibility join department on dept_id = id
where company_id = %s
and jaf_no = %s
)
select id, name,
case
when is_eligible IS null then false
else is_eligible
end
from department natural left join ed
"""
sub_res = conn.execute(sub_query, (int(res[0]), int(res[1])))
for dept in sub_res:
eligible_departments.append({
'dept_id': int(dept[0]),
'name': dept[1],
'is_eligible': dept[2]
})
data = {
'company_id': res[0],
'company_name': res[6],
'jaf_no': res[1],
'jaf_name': res[2],
'description': res[3],
'stipend': res[4],
'cpi_cutoff': float(res[5]),
'is_verified': res[7],
'eligible_departments': eligible_departments
}
status = "true"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def add_jaf():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 2:
# logged in user is not a company
status = "false"
data = "User is not a company"
else:
company_id = session['username']
request_data = request.get_json()
try:
jaf_no = int(request_data['jaf_no'])
name = request_data['jaf_name']
description = request_data['description']
stipend = request_data['stipend']
cpi_cutoff = request_data['cpi_cutoff']
eligible_depts = request_data['eligible_departments']
# check if jaf_no already exists
query = """
select count(*)
from jaf
where company_id = %s
and jaf_no = %s
"""
res = list(conn.execute(query, (company_id, jaf_no, )).first())
if (res[0] == 1):
status = "false"
data = "JAF already exists"
else:
# get IC with lowest no of jafs alloted to him/her
query = """
with ic_jaf_count as
(select alloted_ic_id, count(*) as jaf_count
from jaf
group by alloted_ic_id
order by jaf_count asc)
select ic.id, rollno,
case
when jaf_count IS null then 0
else jaf_count
end
from ic left outer join ic_jaf_count
on ic.id = ic_jaf_count.alloted_ic_id
order by jaf_count asc
limit 1
"""
res = list(conn.execute(query).first())
query = """
insert into
jaf(company_id, jaf_no, name, description, stipend, cpi_cutoff, alloted_ic_id)
values(%s, %s, %s, %s, %s, %s, %s)
"""
conn.execute(query, (company_id, jaf_no, name, description, stipend, cpi_cutoff, int(res[0])))
query = """
insert into
eligibility
values (%s, %s, %s)
"""
for dept in eligible_depts:
if (dept['is_eligible'] == True):
conn.execute(query, (company_id, jaf_no, int(dept['dept_id'])))
status = "true"
data = "successfully added new JAF"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def comapany_view_signed_students():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 2:
# logged in user is not a company
status = "false"
data = "User is not a company"
else:
company_id = session['username']
try:
request_data = request.get_json()
jaf_no = int(request_data['jaf_no'])
# check if jaf exists
query = """
select count(*)
from jaf
where company_id = %s and jaf_no = %s
"""
res = list(conn.execute(query, (company_id, jaf_no)).first())
if (res[0] == 1):
query = """
select
student.name,
cpi,
department.name,
is_shortlisted,
date_signed,
student.rollno,
is_selected
from signedjafs natural join student, department
where company_id = %s
and jaf_no = %s
and department.id = dept_id
"""
res = conn.execute(query, (company_id, jaf_no))
data = []
for row in res:
data.append({
'name': row[0],
'cpi': float(row[1]),
'dept': row[2],
'is_shortlisted': bool(row[3]),
'date_signed': row[4],
'rollno': row[5],
'is_selected': row[6]
})
status = "true"
else:
status = "false"
data = "JAF does not exists"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def comapany_view_jaf_details():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 2:
# logged in user is not a company
status = "false"
data = "User is not a company"
else:
company_id = session['username']
try:
request_data = request.get_json()
jaf_no = int(request_data['jaf_no'])
query = """
select count(*)
from jaf
where company_id = %s and jaf_no = %s
"""
res = list(conn.execute(query, (company_id, jaf_no)))
if (res[0][0] == 1):
query = """
select
jaf_no,
name,
description,
stipend,
cpi_cutoff,
is_verified
from jaf
where company_id = %s and jaf_no = %s
"""
res = list(conn.execute(query, (company_id, jaf_no)).first())
eligible_departments = []
sub_query = """
with ed as
(
select id, name, true as is_eligible
from eligibility join department on dept_id = id
where company_id = %s
and jaf_no = %s
)
select id, name,
case
when is_eligible IS null then false
else is_eligible
end
from department natural left join ed
"""
sub_res = conn.execute(sub_query, (company_id, jaf_no))
for dept in sub_res:
eligible_departments.append({
'dept_id': int(dept[0]),
'name': dept[1],
'is_eligible': dept[2]
})
data = {
'jaf_no': jaf_no,
'jaf_name': res[1],
'description': res[2],
'stipend': res[3],
'cpi_cutoff': float(res[4]),
'is_verified': res[5],
'eligible_departments': eligible_departments
}
status = "true"
else:
status = "false"
data = "JAF does not exists"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def edit_jaf():
data = ""
status = ""
if 'username' not in session:
# no user has logged in
status = "false"
data = "Invalid Session"
elif session['user_type'] != 2:
# logged in user is not a company
status = "false"
data = "User is not a company"
else:
company_id = session['username']
request_data = request.get_json()
try:
jaf_no = int(request_data['jaf_no'])
name = request_data['jaf_name']
description = request_data['description']
stipend = request_data['stipend']
cpi_cutoff = request_data['cpi_cutoff']
eligible_depts = request_data['eligible_departments']
# check if jaf_no already exist
# cannot edit once verified by IC
query = """
select count(*)
from jaf
where company_id = %s
and jaf_no = %s
and is_verified = false
"""
res = list(conn.execute(query, (company_id, jaf_no, )).first())
if (res[0] == 0):
status = "false"
data = "JAF does not exist / already verified"
else:
query = """
update jaf
set name = %s,
description = %s,
stipend = %s ,
cpi_cutoff = %s
where company_id = %s
and jaf_no = %s
"""
conn.execute(query, (name, description, stipend, cpi_cutoff, company_id, jaf_no))
# delete related entries in eligibility
query = """
delete
from eligibility
where company_id = %s and jaf_no = %s
"""
conn.execute(query, (company_id, jaf_no))
query = """
insert into
eligibility
values (%s, %s, %s)
"""
for dept in eligible_depts:
if (dept['is_eligible'] == True):
conn.execute(query, (company_id, jaf_no, int(dept['dept_id'])))
status = "true"
data = "successfully updated JAF"
except Exception as e:
status = "false"
data = str(e)
return jsonify({
'data': data,
'status': status
})
def login():
data = ""
status = ""
request_data = request.get_json()
if 'username' not in request_data:
data = "no username"
elif 'password' not in request_data:
data = "no password"
elif 'type' not in request_data:
data = "no type"
else:
username = request_data['username']
password = request_data['password']
user_type = int(request_data['type'])
table = ""
username_col = ""
if user_type == 0:
# student
table = "student"
username_col = "rollno"
elif user_type == 1:
table = "ic"
username_col = "id"
elif user_type == 2:
table = "company"
username_col = "email"
query = "select count(*) from {} where {} = %s and password = %s"\
.format(table, username_col)
res = list(conn.execute(query, (
username,
password,
)).first())
if (res[0] == 1):
status = "true"
data = ""
if (user_type == 2):
query = """
select id
from company
where email = %s
"""
res = list(conn.execute(query, (username, )).first())
session['username'] = res[0]
session['user_type'] = user_type
else:
session['username'] = username
session['user_type'] = user_type
else:
status = "false"
data = "Error: Invalid credentials"
return jsonify({'data': data, 'status': status})
