def get(self):
try:
#get idnetification details
req_params = (request.args.get('fname'), request.args.get('lname'),
request.args.get('dob'), 'False')
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM identification WHERE first_name=%s AND last_name=%s AND dob=%s AND del_flag=%s",
req_params)
identification_row = cursor.fetchone()
if identification_row == None:
return not_found()
else:
json_i = {
"FirstName": identification_row[1],
"LastName": identification_row[2],
"DOB": identification_row[3].strftime('%m/%d/%Y'),
"Gender": identification_row[4],
"Title": identification_row[5]
}
identification_id = identification_row[0]
sel_data = ('False', identification_id)
#get contact details
cursor.execute(
"SELECT type, preferred, value FROM communication WHERE del_flag=%s AND identification_id=%s",
sel_data)
row_headers = [y[0] for y in cursor.description]
contact_rows = cursor.fetchall()
json_c = []
for row in contact_rows:
json_c.append(dict(zip(row_headers, row)))
#get address details
cursor.execute(
"SELECT a.type, a.num, a.street, a.unit, a.city, a.state, a.zipcode FROM address a, identification_address ad WHERE a.id = ad.address_id AND del_flag=%s AND ad.identification_id=%s",
sel_data)
row_headers = [y[0] for y in cursor.description]
address_rows = cursor.fetchall()
json_d = []
for row in address_rows:
json_d.append(dict(zip(row_headers, row)))
get_dict = {
" Identification": json_i,
"Address": json_d,
"Communication": json_c
}
resp = jsonify(get_dict)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def get_response():
conn = mysql.connect()
print(current_identity[2])
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT type,flag FROM user where id = %s",
str(current_identity[0]))
data = cursor.fetchone()
if data:
return json.dumps({"information": data}), 200
else:
return json.dumps({"information": "No information"}), 200
def get_players():
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT id,username,name,photo FROM info WHERE extra != 0")
data = cursor.fetchall()
response = list()
for row in data:
print(row)
response.append(row)
return json.dumps({"players": response}), 200
def get_player(username):
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT id FROM user WHERE username=%s", username)
data = cursor.fetchone()
print(data)
cursor.execute(
"SELECT id,name,description,moreinfo,photo FROM info WHERE extra != 0 and id = %s",
data['id'])
data = cursor.fetchone()
if data:
return json.dumps({"information": data}), 200
else:
cursor.execute("SELECT count(*) AS count FROM info WHERE id = %s", id)
data = cursor.fetchone()
print(data)
if data["count"] > 0:
return json.dumps({"information": "You dont have Permission"}), 401
else:
return json.dumps({"information": "No User Found"}), 404
def delete(self):
try:
#get identification details
req_params = (request.args.get('fname'), request.args.get('lname'),
request.args.get('dob'), 'False')
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"SELECT id FROM identification WHERE first_name=%s AND last_name=%s AND dob=%s AND del_flag = %s",
req_params)
row = cursor.fetchone()
if row == None:
return not_found()
else:
identification_id = row[0]
del_data = ('True', identification_id)
#soft delete identification table(sets del_flag t0 true)
cursor.execute(
"UPDATE identification SET del_flag = %s WHERE id=%s",
del_data)
conn.commit()
# soft delete all communication data for Contact (sets del_flag to true)
cursor.execute(
"UPDATE communication SET del_flag = %s WHERE identification_id=%s",
del_data)
conn.commit()
#soft delete all adress data for Contact (sets del_flag to true)
cursor.execute(
"UPDATE identification_address SET del_flag = %s WHERE identification_id=%s",
del_data)
conn.commit()
resp = jsonify("Contact Deleted Succesfully")
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def identity(payload):
if payload['identity']:
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT id,username,type FROM user WHERE id=%s",
payload['identity'])
row = cursor.fetchone()
if row:
return (row['id'], row['username'], row['type'])
else:
return None
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
else:
return None
def authenticate(username, password):
if username and password:
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"SELECT id, username, password, type FROM user WHERE username=%s",
username)
row = cursor.fetchone()
if row:
if check_password_hash(row['password'], password):
return User(row['id'], row['username'])
else:
return None
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
return None
def patch(self):
conn = mysql.connect()
cursor = conn.cursor()
try:
resp = jsonify("Contact Updated Succesfully")
resp.status_code = 200
#get identification details
req_params = (request.args.get('fname'), request.args.get('lname'),
request.args.get('dob'), 'False')
cursor.execute(
"SELECT id FROM identification WHERE first_name=%s AND last_name=%s AND dob=%s AND del_flag=%s",
req_params)
row = cursor.fetchone()
if row == None:
return not_found()
else:
identification_id = row[0]
json_arr = request.json
for item in json_arr:
operation = item['op']
path = (item['path'].split("/"))
if operation == "add" and path[1] == "Address":
#in patch we will not update the existing address data for contact/ Put method available for that
#insert addresses
address_data = (item['value']['type'],
item['value']['number'],
item['value']['street'],
item['value']['Unit'],
item['value']['City'],
item['value']['State'],
item['value']['zipcode'])
cursor.execute(
"SELECT id FROM address WHERE type = %s AND num = %s AND street = %s AND unit = %s AND city = %s AND state = %s AND zipcode = %s",
address_data)
add_result = cursor.fetchall()
if len(add_result) > 0:
address_id = add_result[0]
else:
cursor.execute(
"INSERT INTO address(type, num, street, unit, city, state, zipcode) VALUES (%s, %s, %s, %s, %s, %s, %s)",
address_data)
conn.commit()
#get address_id
address_id = cursor.lastrowid
#insert identity_address
identity_address_data = (identification_id,
address_id)
cursor.execute(
"INSERT INTO identification_address(identification_id, address_id) VALUES (%s, %s)",
identity_address_data)
conn.commit()
elif operation == "add" and path[1] == "Communication":
communication_data = (identification_id,
item['value']['type'],
item['value']['preferred'],
item['value']['value'])
cursor.execute(
"INSERT INTO communication(identification_id, type, preferred, value)VALUES(%s, %s, %s, %s)",
communication_data)
conn.commit()
elif operation == "remove" and path[1] == "Communication":
# soft del old communication data for contact
del_data = ('True', identification_id)
cursor.execute(
"UPDATE communication SET del_flag = %s WHERE identification_id=%s",
del_data)
conn.commit()
elif operation == "remove" and path[1] == "Address":
#soft delete all adress data for contact
del_data = ('True', identification_id)
cursor.execute(
"UPDATE identification_address SET del_flag = %s WHERE identification_id=%s",
del_data)
conn.commit()
elif operation == "replace" and path[1] == "Title":
identification_data = (item['value'],
identification_id)
#soft delete identification table
cursor.execute(
"UPDATE identification SET title = %s WHERE id=%s",
identification_data)
conn.commit()
elif operation == "remove" and path[1] == "Title":
identification_data = ("", identification_id)
#soft delete identification table
cursor.execute(
"UPDATE identification SET title = %s WHERE id=%s",
identification_data)
conn.commit()
else:
resp = jsonify("Operation not supported")
resp.status_code = 400
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def post(self):
try:
sql1 = "INSERT INTO identification (first_name, last_name, dob, gender, title) VALUES (%s, %s, %s, %s, %s)"
sql2 = "SELECT id FROM address WHERE type = %s AND num = %s AND street = %s AND unit = %s AND city = %s AND state = %s AND zipcode = %s"
sql3 = "INSERT INTO address(type, num, street, unit, city, state, zipcode) VALUES (%s, %s, %s, %s, %s, %s, %s)"
sql4 = "INSERT INTO identification_address(identification_id, address_id) VALUES (%s, %s)"
sql5 = "INSERT INTO communication(identification_id, type, preferred, value)VALUES(%s, %s, %s, %s)"
json_arr = request.json
for _json in json_arr:
identification_data = (_json['Identification']['FirstName'],
_json['Identification']['LastName'],
_json['Identification']['DOB'],
_json['Identification']['Gender'],
_json['Identification']['Title'])
conn = mysql.connect()
cursor = conn.cursor()
#insert indentification
cursor.execute(sql1, identification_data)
conn.commit()
#get identification id
identification_id = cursor.lastrowid
#insert addresses
for a in _json['Address']:
address_data = (a['type'], a['number'], a['street'],
a['Unit'], a['City'], a['State'],
a['zipcode'])
cursor.execute(sql2, address_data)
add_result = cursor.fetchall()
if len(add_result) > 0:
address_id = add_result[0]
else:
cursor.execute(sql3, address_data)
conn.commit()
#get address_id
address_id = cursor.lastrowid
#insert identity_address
identity_address_data = (identification_id, address_id)
cursor.execute(sql4, identity_address_data)
conn.commit()
#insert communication
for c in _json['Communication']:
if 'preferred' in c:
preference = c['preferred']
else:
preference = 'false'
communication_data = (identification_id, c['type'],
preference, c['value'])
cursor.execute(sql5, communication_data)
conn.commit()
try:
resp = jsonify("Contact Added successfully")
resp.status_code = 200
return resp
except Exception as e:
print(e)
return internal_error(e)
except Exception as e:
print(e)
return internal_error(e)
finally:
cursor.close()
conn.close()
def put(self):
conn = mysql.connect()
cursor = conn.cursor()
try:
#get identification details
req_params = (request.args.get('fname'), request.args.get('lname'),
request.args.get('dob'), "False")
_json = request.json
#queries
sql1 = "INSERT INTO identification (first_name, last_name, dob, gender, title) VALUES (%s, %s, %s, %s, %s)"
sql2 = "SELECT id FROM address WHERE type = %s AND num = %s AND street = %s AND unit = %s AND city = %s AND state = %s AND zipcode = %s"
sql3 = "INSERT INTO address(type, num, street, unit, city, state, zipcode) VALUES (%s, %s, %s, %s, %s, %s, %s)"
sql4 = "INSERT INTO identification_address(identification_id, address_id) VALUES (%s, %s)"
sql5 = "INSERT INTO communication(identification_id, type, preferred, value)VALUES(%s, %s, %s, %s)"
#get identification_id
cursor.execute(
"SELECT id FROM identification WHERE first_name=%s AND last_name=%s AND dob=%s AND del_flag=%s",
req_params)
row = cursor.fetchone()
if row == None: #Add the contact if does not exist already
identification_data = (_json['Identification']['FirstName'],
_json['Identification']['LastName'],
_json['Identification']['DOB'],
_json['Identification']['Gender'],
_json['Identification']['Title'])
conn = mysql.connect()
cursor = conn.cursor()
#insert indentification
cursor.execute(sql1, identification_data)
conn.commit()
#get identification id
identification_id = cursor.lastrowid
#insert addresses
for a in _json['Address']:
address_data = (a['type'], a['number'], a['street'],
a['Unit'], a['City'], a['State'],
a['zipcode'])
cursor.execute(sql2, address_data)
add_result = cursor.fetchall()
if len(add_result) > 0:
address_id = add_result[0]
else:
cursor.execute(sql3, address_data)
conn.commit()
#get address_id
address_id = cursor.lastrowid
#insert identity_address
identity_address_data = (identification_id, address_id)
cursor.execute(sql4, identity_address_data)
conn.commit()
#insert communication
for c in _json['Communication']:
communication_data = (identification_id, c['type'],
c['preferred'], c['value'])
cursor.execute(sql5, communication_data)
conn.commit()
resp = jsonify("Contact Added Succesfully")
resp.status_code = 200
else: #update the contact if exists already
identification_id = row[0]
identification_data = (_json['Identification']['Title'],
identification_id)
#soft delete identification table
cursor.execute(
"UPDATE identification SET title = %s WHERE id=%s",
identification_data)
conn.commit()
# soft del old communication data for contact
del_data = ('True', identification_id)
cursor.execute(
"UPDATE communication SET del_flag = %s WHERE identification_id=%s",
del_data)
conn.commit()
#add new communication data
for c in _json['Communication']:
communication_data = (identification_id, c['type'],
c['preferred'], c['value'])
cursor.execute(sql5, communication_data)
conn.commit()
#soft delete all adress data for contact
cursor.execute(
"UPDATE identification_address SET del_flag = %s WHERE identification_id=%s",
del_data)
conn.commit()
#insert addresses
for a in _json['Address']:
address_data = (a['type'], a['number'], a['street'],
a['Unit'], a['City'], a['State'],
a['zipcode'])
cursor.execute(sql2, address_data)
add_result = cursor.fetchall()
if len(add_result) > 0:
address_id = add_result[0]
else:
cursor.execute(sql3, address_data)
conn.commit()
#get address_id
address_id = cursor.lastrowid
#insert identity_address
identity_address_data = (identification_id, address_id)
cursor.execute(sql4, identity_address_data)
conn.commit()
resp = jsonify("Contact Updated Succesfully")
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()