def update_user_info(firstname,lastname,othernames,username,tel_no,email,password,user_avatar,actual_username):
'''
This function helps updates a users information provided from the front-end,
First of all, the username is verified before updating. After updating, we
fetch the updated information and return it
'''
try:
cursor = get_db().cursor()
query = '''
UPDATE User_info SET firstname=%s,lastname=%s,othernames=%s,username=%s,
telephone_number=%s,email=%s,user_password=%s,user_avatar=%s WHERE username=%s
'''
cursor.execute(query,(firstname,lastname,othernames,username,tel_no,email,password,user_avatar,actual_username))
get_db().commit()
except:
return jsonify({
"message":"You can't update user info"
})
cur = get_db().cursor()
query_two = '''
SELECT firstname,lastname,othernames,username,telephone_number
,email,user_avatar FROM User_info WHERE username=%s
'''
cur.execute(query_two,(actual_username,))
results = cur.fetchall()[0]
user_updated_info = jsonify({
"firstname":results[0],
"lastname":results[1],
"othernames":results[2],
"username":results[3],
"telephone_number":results[4],
"email":results[5],
"user_avatar":results[6]
})
return user_updated_info
def delete_user_from_db(username):
cursor = get_db().cursor()
delete_user_query = '''
DELETE FROM User_info WHERE username=%s
'''
cursor.execute(delete_user_query,(username,))
get_db().commit()
return jsonify({
"message":"User deleted by admin"
})
def update_bus(bus_url,capacity,car_no):
cursor = get_db().cursor()
query = '''
UPDATE Buses SET bus_photo=%s,capacity=%s WHERE car_no=%s
'''
cursor.execute(query,(bus_url,int(capacity),car_no))
get_db().commit()
return jsonify({
"message":"Bus info updated"
})
def delete_otherVehicles(car_no):
cursor = get_db().cursor()
query = '''
DELETE FROM other_vehicles WHERE car_no=%s
'''
cursor.execute(query,(car_no,))
get_db().commit()
return jsonify({
"message":"Car deleted successfully"
})
def delete_busOwner(user_id):
cursor = get_db().cursor()
query='''
DELETE FROM Bus_owners WHERE user_id=%s
'''
cursor.execute(query,(int(user_id),))
get_db().commit()
return jsonify({
"message":"Bus owner account deleted successfully"
})
def update_busOwner_info(company_name,location,owner_avatar,user_id):
cursor = get_db().cursor()
query = '''
UPDATE Bus_owners SET company_name=%s,location=%s,owner_avatar=%s WHERE user_id=%s
'''
cursor.execute(query,(company_name,location,owner_avatar,int(user_id),))
get_db().commit()
return jsonify({
"message":"You bus owner info is updated"
})
def delete_driver(user_id):
cursor= get_db().cursor()
query = '''
DELETE FROM driver_info WHERE user_id=%s
'''
cursor.execute(query,(int(user_id),))
get_db().commit()
return jsonify({
"message":"Driver deleted from database"
})
def update_privateCarOwner_info(location,user_id):
cursor = get_db().cursor()
query = '''
UPDATE private_car_users SET location=%s WHERE user_id=%s
'''
cursor.execute(query,(location,int(user_id),))
get_db().commit()
return jsonify({
"message":"Private car owner info updated"
})
def register_privateCarOwner(location,user_id):
cursor = get_db().cursor()
query = '''
INSERT INTO private_car_users (location,user_id) VALUES (%s,%s)
'''
cursor.execute(query,(location,int(user_id),))
get_db().commit()
return jsonify({
"message":"Private car owner registered"
})
def register_other_owners(photo,location,user_id):
cursor = get_db().cursor()
query = '''
INSERT INTO other_vehicle_users (photo,location,user_id) VALUES (%s,%s,%s)
'''
cursor.execute(query,(photo,location,int(user_id),))
get_db().commit()
return jsonify({
"message":"Vehicle registered successfully"
})
def update_OtherVehicleOwnersInfo(photo_url,location,user_id):
cursor = get_db().cursor()
query = '''
UPDATE other_vehicle_users SET photo=%s,location=%s WHERE user_id=%s
'''
cursor.execute(query,(photo_url,location,int(user_id),))
get_db().commit()
return jsonify({
"message":"Other vehicle info updated"
})
def register_driver(date_of_birth,residential_address,license_no,sex,user_id):
cursor = get_db().cursor()
query = '''
INSERT INTO driver_info (date_of_birth,residential_address,license_no,
sex,user_id)VALUES(%s,%s,%s,%s,%s)
'''
cursor.execute(query,(date_of_birth,residential_address,license_no,sex,int(user_id),))
get_db().commit()
return jsonify({
"message":"Driver registered successfully"
})
def delete_troski_owner(user_id):
cursor = get_db().cursor()
query = '''
DELETE FROM Troski_owner WHERE user_id=%s
'''
cursor.execute(query,(int(user_id),))
get_db().commit()
return jsonify({
"message":"Troski owner info deleted"
})
def update_trokis_owner_info(owner_photo,location,user_id):
cursor = get_db().cursor()
query = '''
UPDATE Troski_owner SET owner_photo=%s,location=%s WHERE user_id=%s
'''
cursor.execute(query,(owner_photo,location,int(user_id),))
get_db().commit()
return jsonify({
"message":"Trokis owner info updated"
})
def register_trokis_owner(owner_photo,location,user_id):
cursor = get_db().cursor()
query = '''
INSERT INTO Troski_owner (owner_photo,location,user_id) VALUES (%s,%s,%s)
'''
cursor.execute(query,(owner_photo,location,int(user_id),))
get_db().commit()
return jsonify({
"message":"Troski owner register"
})
def delete_bus(car_no):
cursor = get_db().cursor()
query = '''
DELETE FROM Buses WHERE car_no=%s
'''
cursor.execute(query,(car_no,))
get_db().commit()
return jsonify({
"message":"Bus deleted successfully"
})
def delete_privateCarOwner_info(user_id):
cursor = get_db().cursor()
query = '''
DELETE FROM private_car_users WHERE user_id=%s
'''
cursor.execute(query,(int(user_id),))
get_db().commit()
return jsonify({
"message":"Pricate car owner deleted"
})
def register_bus_owner(company_name,location,owner_avatar,user_id):
cursor = get_db().cursor()
query = '''
INSERT INTO Bus_owners (company_name,location,owner_avatar,user_id)
VALUES(%s,%s,%s,%s)
'''
cursor.execute(query,(company_name,location,owner_avatar,int(user_id),))
get_db().commit()
return jsonify({
"message":"Bus owner registered successfully"
})
def delete_OtherVehicleOwnersInfo(user_id):
cursor = get_db().cursor()
query = '''
DELETE FROM other_vehicle_users WHERE user_id=%s
'''
cursor.execute(query,(int(user_id),))
get_db().commit()
return jsonify({
"message":"Other vehicle owner info deleted"
})
def update_driver_info(dateOfBirth,Residential,license,sex,user_id):
cursor = get_db().cursor()
query = '''
UPDATE driver_info SET date_of_birth=%s,residential_address=%s,
license_no=%s,sex=%s WHERE user_id = %s
'''
cursor.execute(query,(dateOfBirth,Residential,license,sex,user_id,))
get_db().commit()
gc.collect()
return jsonify({
"message":"Driver information updated sucessfully"
})
def update_otherVehiclesInfo(photo,car_type,car_no,):
cursor = get_db().cursor()
query = '''
UPDATE other_vehicles SET photo=%s,car_type=%s WHERE car_no=%s
'''
try:
cursor.execute(query,(photo,car_type,car_no,))
get_db().commit()
return jsonify({
"message":"Information updated"
})
except:
return jsonify({
"message":"Car number doesnt exist"
}),201
def register_OtherVehicles(photo,car_no,car_type,owner_id):
cursor = get_db().cursor()
query = '''
INSERT INTO other_vehicles (photo,car_no,car_type,owner_id) VALUES (%s,%s,%s,%s)
'''
try:
cursor.execute(query,(photo,car_no,car_type,int(owner_id),))
get_db().commit()
return jsonify({
"message":"Car registered successfully"
})
except:
return jsonify({
"message":"car number already exists"
})
def register_troski(photo,car_no,capacity,owner_id):
cursor = get_db().cursor()
query = '''
INSERT INTO Troskis (photo,car_no,capacity,owner_id) VALUES (%s,%s,%s,%s)
'''
try:
cursor.execute(query,(photo,car_no,int(capacity),int(owner_id),))
get_db().commit()
except:
return jsonify({
"message":"Vehicle with that number already registered"
})
return jsonify({
"message":"Trokis registered"
})
def get_driver_info(username):
cursor = get_db().cursor()
query = '''
SELECT User_info.firstname,User_info.lastname,User_info.othernames,
User_info.user_avatar,User_info.email,User_info.telephone_number,
User_info.username, driver_info.date_of_birth,driver_info.residential_address,
driver_info.license_no,driver_info.sex
FROM
User_info
JOIN driver_info ON User_info.user_id=driver_info.user_id WHERE
username=%s
'''
cursor.execute(query,(username,))
query_results = cursor.fetchall()[0]
query_results = jsonify({
"firstname":query_results[0],
"lastname":query_results[1],
"othernames":query_results[2],
"user_avatar":query_results[3],
"email":query_results[4],
"telephone number":query_results[5],
"username":query_results[6],
"date of birth":query_results[7],
"residential address":query_results[8],
"license number":query_results[9],
"sex":query_results[10]
})
return query_results
def update_troski(photo_url,capacity,car_no):
cursor = get_db().cursor()
query = '''
UPDATE Troskis SET photo=%s,capacity=%s WHERE car_no=%s
'''
try:
cursor.execute(query,(photo_url,int(capacity),car_no,))
get_db().commit()
except:
return jsonify({
"message":"Invalid car number"
})
return jsonify({
"message":"Bus info updated"
})
def delete_troski(car_no):
cursor = get_db().cursor()
query = '''
DELETE FROM Troskis WHERE car_no=%s
'''
try:
cursor.execute(query,(car_no,))
get_db().commit()
except:
return jsonify({
"message":"This car number doesnt exist"
})
return jsonify({
"message":"Bus deleted successfully"
})
def delete_car(car_no):
cursor = get_db().cursor()
query = '''
DELETE FROM private_cars WHERE car_number=%s
'''
try:
cursor.execute(query,(car_no,))
get_db().commit()
except:
return jsonify({
"message":"Car number doesnt exist and therefore cant delete"
})
return jsonify({
"message":"Private car deleted"
})
def sign_up(firstname,lastname,othernames,username,tel_no,email,password,user_avatar):
try:
cur = get_db().cursor()
query = '''
INSERT INTO User_info
(firstname,lastname,othernames,username,telephone_number,email,user_password,user_avatar)
values(%s,%s,%s,%s,%s,%s, %s,%s)
'''
args = (firstname,lastname,othernames,username,tel_no,email,password,user_avatar)
cur.execute(query,args)
get_db().commit()
gc.collect()#Gabbage collection
except:
return jsonify({
"database":"connection problems"
})
def register_privateCar(car_type,car_photo,car_no,owner_id):
cursor = get_db().cursor()
query = '''
INSERT INTO private_cars (car_type,car_photo,car_number,owner_id) VALUES (%s,%s,%s,%s)
'''
try:
cursor.execute(query,(car_type,car_photo,car_no,int(owner_id),))
get_db().commit()
except:
return jsonify({
"message":"Vehicle with that number already registered"
})
return jsonify({
"message":"Private car registered successfully"
})
def get_otherVehicleOwnerId(user_id):
cursor = get_db().cursor()
query = '''
SELECT owner_id FROM other_vehicle_users WHERE user_id=%s
'''
cursor.execute(query,(int(user_id),))
result = cursor.fetchall()[0]
return result[0]
