def get_unapproved_emp():
"""
GET params:
- emp_code: `emp_code`
- access_level: 'manager', 'admin'
"""
emp_code = int(request.args.get('emp_code'))
approved_by = request.args.get('approved_by')
query = "SELECT * FROM visa_form where visa_status = ?"
result = database.query_db(query, (approved_by, ))
return helper.row_jsonify(result)
def get_emp_details():
"""
GET params:
- empCode: `emp_code`
"""
emp_code = int(request.args.get('empCode'))
query = "SELECT * FROM employee where emp_code = ?"
#query="WITH visadetails as (select * from visa_form where emp_code = ?) \
#SELECT * from employee as e, visadetails where e.emp_code = ?"
result = database.query_db(query, (emp_code, ), True)
return helper.row_jsonify(result, True)
def sendDeniedMail(emp_code, access_level):
query = "SELECT email_id FROM employee where emp_code = ?"
result = database.query_db(query, (emp_code, ), True)
email = json.loads(helper.row_jsonify(result, True))['email_id']
subject = "Your application has been DENIED by the {}".format(access_level)
text_body = """\
Hey Employee No {},
We are sorry to inform you that your application was denied.
""".format(emp_code)
text_body = textwrap.dedent(text_body)
sender = "Fractal Analytics"
mailNinja.send_email(subject, sender, email, text_body, 4)
def sendApprovedMail(emp_code):
query = "SELECT email_id FROM employee where emp_code = ?"
result = database.query_db(query, (emp_code, ), True)
email = json.loads(helper.row_jsonify(result, True))['email_id']
subject = "Your application has been approved!"
text_body = """\
Hey Employee No {},
Congratulations! Your application has been APPROVED!
""".format(emp_code)
text_body = textwrap.dedent(text_body)
sender = "Fractal Analytics"
mailNinja.send_email(subject, sender, email, text_body, 4)
def sendAcceptMail(emp_code, access_level):
query = "SELECT email_id FROM employee where emp_code = ?"
result = database.query_db(query, (emp_code, ), True)
email = json.loads(helper.row_jsonify(result, True))['email_id']
subject = "Your application has been accepted by {}".format(access_level)
text_body = """\
Hey Employee No {},
Your application has been accepted by {} and has gone to the next step for approval!
""".format(emp_code, access_level)
text_body = textwrap.dedent(text_body)
sender = "Fractal Analytics"
mailNinja.send_email(subject, sender, email, text_body, 3)
def get_visa_details():
"""
GET params:
- empCode: `emp_code`
"""
emp_code = int(request.args.get('empCode'))
query = "SELECT * FROM visa_form where emp_code = ?"
#query="WITH visadetails as (select * from visa_form where emp_code = ?) \
#SELECT * from employee as e, visadetails where e.emp_code = ?"
result = database.query_db(query, (emp_code, ), True)
if result:
return helper.row_jsonify(result, True)
else:
return json.dumps(dict([("success", 0)]))
def admin_stats():
stat_type = request.args.get('statType')
if stat_type == "visa_type":
visa_dict = {}
visa_types = ('h1', 'l1', 'b1', 'student', 'business')
for visa in visa_types:
query = "SELECT count(lower(visa_type)) from visa_form where visa_type = ?"
result = database.query_db(query, (visa, ), True)
visa_dict[visa] = result[0]
return json.dumps(visa_dict)
elif stat_type == "visa_status":
query = "SELECT count(visa_id) as visa_id from visa_form"
result = database.query_db(query, one=True)
result_dict = dict(result)
query2 = "SELECT count(visa_type) as visa_approved from visa_form \
WHERE visa_status = 'approved'"
result2 = database.query_db(query2, one=True)
result2_dict = dict(result2)
print(result_dict, result2_dict)
result_dict.update(result2_dict)
return json.dumps(result_dict)
else:
return "Error.", 404
def getNotificationMail(source, sender):
if source.lower() == 'employee':
query = "SELECT m.email_id from employee e, manager m where e.emp_id = ? and e.proj_code = m.proj_code LIMIT 1"
result = database.query_db(query, sender, True)
recipients = list(result)
subject = "Employee Number {} has applied for visa registration.".format(
sender)
text_body = """\
Employee ID : {:>20}
Employee Name: {:>20}
Project Code : {:>20}
Please log in to our android app to view the application.
""".format(emp_code, emp_name, proj_code)
text_body = textwrap.dedent(text_body)
return recipients, subject, text_body
def sendNotifyMail(emp_code, country, visa_type):
sender = "Fractal Analytics"
query = "SELECT m.email_id from employee as e, manager as m where \
e.emp_code = ? AND e.proj_code = m.proj_code"
result = database.query_db(query, (emp_code, ), True)
manager_email_id = json.loads(helper.row_jsonify(result, True))['email_id']
subject = "Employee working on your project has applied for VISA!"
text_body = """\
Employee ID : {}
Country : {}
Visa Type : {}
Login to our android app to ACCEPT/REJECT the application!
""".format(emp_code, country, visa_type)
mailNinja.send_email(subject, sender, manager_email_id, text_body, 2)
def login():
"""
POST params:
- id: `emp_code`
- password: `password`
- category: 'employee', 'manager', 'admin', or 'lawyer'
"""
emp_code = request.form['id']
password = request.form['password']
category = request.form['category']
query = "SELECT au.emp_code, password FROM allusers as au, {} as x where au.emp_code = ? and password = ? and au.emp_code = x.emp_code LIMIT 1".format(
category.lower())
result = database.query_db(query, (emp_code, password), True)
if result is None:
success = 0
else:
success = 1
return json.dumps(dict([("success", success)]))
def set_visa_form():
emp_code = request.form['emp_code']
country = request.form['country']
passport_no = request.form['passport_no']
visa_type = request.form['visa_type']
visa_status = "nobody"
if database.query_db("SELECT * from visa_form where emp_code = ?",
(emp_code, ), True):
success = 2
else:
query = "INSERT INTO visa_form \
(emp_code, country, visa_type, passport_no, visa_status) \
VALUES (?, ?, ?, ?, ?)"
database.insert_db(query,
(emp_code, country.lower(), visa_type.lower(),
passport_no.lower(), visa_status.lower()))
database.commit_db()
notify.sendNotifyMail(emp_code, country.lower(), visa_type.lower())
success = 1
return json.dumps(dict([("success", success)]))