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})