예제 #1
0
    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()
예제 #2
0
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
예제 #3
0
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
예제 #4
0
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
예제 #5
0
    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()
예제 #6
0
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
예제 #7
0
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
예제 #8
0
    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()
예제 #9
0
    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()
예제 #10
0
    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()