def update_useravatar(public_id):
if not public_id :
return jsonify ({"status" : "error" , "message":"Missing PublicId"}) , 404
try :
with connection.cursor() as cursor:
sql = "SELECT * FROM user where user.public_id = %s"
cursor.execute(sql, (public_id,))
rv = cursor.fetchall()
if not rv:
return jsonify ({"status" : "error" , "message":"Failed to find users"}) , 404
connection.commit()
cursor.close()
except :
return jsonify ({"status" : "error" , "message":"Connection Error "}) , 500
try:
if request.method == 'POST':
if 'file' not in request.files:
return jsonify ({"Code" : "002" , "Message":"NO files to Upload"})
file = request.files['file']
if file.filename == '':
return jsonify ({"Code" : "002" , "Message":"No selected file"})
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
extension = filename.split('.')[1]
newfilename = 'publicid-' + public_id+('-') + 'avatar.' + extension
file.save(os.path.join(UPLOAD_FOLDER, newfilename))
with connection.cursor() as cursor:
sql = "UPDATE usersdetails SET usersdetails_avatar = %s WHERE user_public_id = %s"
cursor.execute(sql, (newfilename,public_id,))
connection.commit()
cursor.close()
return jsonify(filename=newfilename),200
except:
return jsonify ({"Code" : "002" , "Message":"Failed to UpdateData Avatar"}),500
def changejobStatus(current_user,public_job,parameter):
try:
public_id = current_user["public_id"]
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500
if parameter is None:
return jsonify({"Status": "Failed", "message": "Error Parameter???"}), 500
if public_job is None:
return jsonify({"Status": "Failed", "message": "Error public_job???"}), 500
if parameter == 3 :
try:
with connection.cursor() as cursor:
sql = (" UPDATE job SET job.job_operator_id = %s WHERE job.job_public_id = %s " )
cursor.execute(sql, (public_id,public_job,))
connection.commit()
cursor.close()
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
try:
with connection.cursor() as cursor:
sql = (" UPDATE job SET job.status_id = %s WHERE job.job_public_id = %s " )
cursor.execute(sql, (parameter,public_job,))
connection.commit()
cursor.close()
return jsonify({"Status": "success", "jobList": "Update Status success"}), 200
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
def upload_image():
if request.files:
image_file = request.files.get("imageToUpload", "")
all_followers = int(request.form.get("allFollowers") != None)
caption = request.form.get("caption")
image_name = image_file.filename
filepath = os.path.join(IMAGES_DIR, image_name)
image_file.save(filepath)
photo_id = None
query = "INSERT INTO photo (photoOwner, timestamp, filePath, allFollowers, caption) VALUES (%s, %s, %s, %s, %s)"
with connection.cursor() as cursor:
cursor.execute(query, (session["username"], time.strftime('%Y-%m-%d %H:%M:%S'), image_name, all_followers, caption))
photo_id = cursor.lastrowid
if not all_followers:
query = "INSERT INTO Share (groupName, groupOwner, photoID) VALUES (%s, %s, %s)"
groups = request.form.getlist('group')
for group in groups:
group_divided_to_list = group.split(",")
groupName = group_divided_to_list[0]
groupOwner = group_divided_to_list[1]
with connection.cursor() as cursor:
cursor.execute(query, (groupName, groupOwner, photo_id))
message = "Image has been successfully uploaded."
return render_template("upload.html", message=message, user_groups=tools._get_current_user_groups())
else:
message = "Failed to upload image."
return render_template("upload.html", message=message, user_groups=tools._get_current_user_groups())
def submit_tag_action():
request_data = request.form
response = request_data["response"]
photoid = request_data["photoid"]
if response == "accept":
query = "update tag set acceptedTag = 1 where photoid = %s and username = %s"
with connection.cursor() as cursor:
cursor.execute(query, (photoid, session["username"]))
elif response == "decline":
query = "delete from tag where photoid = %s and username = %s"
with connection.cursor() as cursor:
cursor.execute(query, (photoid, session["username"]))
def show_user(id):
if not id:
return jsonify({"Code": "001", "Message": "id is empty"})
try:
user = User.query.filter_by(id=id).first()
userId = user.id
publicId = user.public_id
companyId = user.company_id
roleId = user.role_id
username = user.username
company = Company.query.filter_by(company_id=companyId).first()
companyName = company.company_name
role = Role.query.filter_by(role_id=roleId).first()
rolename = role.role_name
with connection.cursor() as cursor:
sql = "SELECT usersdetails_email ,usersdetails_firstname , usersdetails_lastname ,usersdetails_phone , usersdetails_position ,usersdetails_avatar , usersdetails_employeenumber FROM usersdetails LEFT JOIN user on user.public_id = usersdetails.user_public_id "\
" WHERE user.public_id = %s"
cursor.execute(sql, (publicId, ))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify(
{
"userId": userId,
"username": username,
"publicId": publicId,
"companyId": companyId,
"companyName": companyName,
"roleId": roleId,
"rolename": rolename
}, {"usersdetails": rv})
except Exception as e:
print(e)
return jsonify({"Code": "002", "Message": "User Not found"})
def CountStatusUserJob(current_user,projectid):
try:
public_id = current_user["public_id"]
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200
try:
with connection.cursor() as cursor:
# Read a single record
sql = (
" SELECT COUNT(job.job_public_id) as count , status.status_name , job.status_id from job"
" LEFT JOIN project_has_job on project_has_job.job_public_id = job.job_public_id"
" LEFT JOIN status on status.status_id = job.status_id"
" LEFT JOIN teamproject on project_has_job.teamproject_public_id = teamproject.teamproject_public_id"
" Left join teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id"
" LEFT JOIN user on teamproject_has_user.user_public_id = user.public_id"
" WHERE teamproject_has_user.user_public_id = %s and project_has_job.project_public_id = %s "
" GROUP BY job.status_id"
)
cursor.execute(sql, (public_id,projectid,))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify({"Status": "success", "projectCount": rv}), 200
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
def ListAllProjectByGroup(current_user, public_project):
try:
public_id = current_user["public_id"]
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500
try:
with connection.cursor() as cursor:
# Read a single record
sql = (
" SELECT job.job_name , job.job_public_id , status.status_name , job.status_id,priority.priority_name,teamproject.teamproject_public_id ,job.job_created , jobdetails.jobdetails_enddate from job LEFT JOIN project_has_job ON job.job_public_id = project_has_job.job_public_id "
"LEFT JOIN priority on priority.priority_id = job.priority_id"
" LEFT JOIN jobdetails on jobdetails.job_public_id = job.job_public_id "
" LEFT JOIN status on status.status_id = job.status_id "
" LEFT JOIN teamproject on teamproject.teamproject_public_id = project_has_job.teamproject_public_id"
" LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id"
" WHERE project_has_job.project_public_id = %s and teamproject_has_user.user_public_id = %s ORDER BY status.status_id"
)
cursor.execute(sql, (public_project, public_id))
rv = cursor.fetchall()
connection.commit()
cursor.close()
arr = dict()
for r in rv:
val = []
status = r["status_name"]
arr[status] = {}
for a in rv:
if status == a["status_name"]:
val.append(a)
arr[status] = val
return jsonify({"Status": "Success", "projectList": arr}), 200
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500
def ListAssetsJob(current_user, job_public):
try:
public_id = current_user["public_id"]
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200
try:
with connection.cursor() as cursor:
sql = (
" SELECT assets_public_id , assets_sn , users_creator_id ,assets_brand_name , assets_categories_name , assets_series_name , assets_insurance_name "
" FROM assets LEFT JOIN jobhasassets on jobhasassets.assets_id = assets.assets_id "
" LEFT JOIN job on job.job_id = jobhasassets.job_id "
" LEFT JOIN assetsbrand on assetsbrand.assets_brand_id = assets.assets_brand_id"
" LEFT JOIN assetscategories on assetscategories.assets_categories_id = assets.assets_categories_id"
" LEFT JOIN assetsinsurance on assetsinsurance.assets_insurance_id = assets.assets_insurance_id"
" LEFT JOIN assetsseries on assetsseries.assets_series_id = assets.assets_series_id"
" LEFT JOIN assetsbrand on assetsbrand.assets_brand_id = assets.assets_brand_id"
" LEFT JOIN assetscategories on assetscategories.assets_categories_id = assets.assets_categories_id"
" LEFT JOIN assetsinsurance on assetsinsurance.assets_insurance_id = assets.assets_insurance_id"
" LEFT JOIN assetsseries on assetsseries.assets_series_id = assets.assets_series_id"
" WHERE job.job_public_id = %s ")
cursor.execute(sql, (job_public))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify({"Status": "success", "assets": rv}), 200
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
def ListProjectByStatus(current_user, status_id):
try:
public_id = current_user["public_id"]
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200
if status_id is None:
return jsonify({"Status": " Failed ", "message": "Error Status not"}), 500
try:
with connection.cursor() as cursor:
# Read a single record
sql = (
" SELECT project.project_public_id , project_name , status_name , teamproject.teamproject_public_id , teamproject_name from project "
" LEFT JOIN teamproject_has_project on teamproject_has_project.project_public_id = project.project_public_id "
" LEFT JOIN teamproject on teamproject_has_project.teamproject_public_id = teamproject.teamproject_public_id"
" LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id "
" LEFT JOIN status on status.status_id = teamproject_has_project.status_id"
" WHERE teamproject_has_user.user_public_id = %s AND teamproject_has_project.status_id = %s"
)
cursor.execute(sql, (public_id, status_id,))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify({"Status": "success", "projectList": rv}), 200
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
def get_user_tag_requests():
result = None
query = "select photoid, filepath from photo natural join tag where username = %s and acceptedTag != True"
with connection.cursor() as cursor:
cursor.execute(query, session["username"])
result = cursor.fetchall()
return result
def index():
with connection.cursor() as cursor:
sql = "SELECT MovieID, Name FROM Movie WHERE `Now Playing` = 1"
cursor.execute(sql,args=None)
result = cursor.fetchall()
return render_template('index.html',title = 'Home', movieData = result)
def submit_like():
request_data = request.form # get the proper form
photoid = request_data["photoid"] # get the photoid of the liked photo
query = "insert into liked (username, photoID) VALUES (%s, %s)" # prepare the query
with connection.cursor() as cursor:
cursor.execute(query,
(session["username"], photoid)) # submit the photo
def database_connection_list_cold(connection, user_data):
cursor = connection.cursor()
array = tuple(user_data.keys())
sql_query = "update list_juces set checkbox_yes_no = ' '"
sql_query_yes = "update list_juces set checkbox_yes_no = 'yes' WHERE available_juices IN %s"
print(cursor.mogrify(sql_query, (array, )))
cursor.execute(sql_query)
cursor.execute(sql_query_yes, (array, ))
connection.commit()
cursor.close()
def database_connect_function():
try:
connection = psycopg2.connect(user="******",
host="127.0.0.1",
port="5432",
database="officecafe")
cursor = connection.cursor()
cursor.execute("select available_juices from list_juces")
record = cursor.fetchall()
return record
except (Exception, psycopg2.Error) as error:
print("Error in connecting to PSQL", error)
def submit_comment():
request_data = request.form
comment = request_data["comment"] # get comment from form data
photoid = request_data["photoid"] # get photoid from form data
query = "insert into comment (username, photoID, commentText) VALUES (%s, %s, %s)" #timestamp is generated by db
try:
with connection.cursor() as cursor:
cursor.execute(query, (session["username"], photoid, comment))
except:
# decided to fail silently rather than change the db schema bc we like that each user is limited in the number of
# comments. it reduces spam for a user
print("fail silently for duplicate entries or other db failures")
def new_tag():
if request.form:
requestData = request.form
tagged_name = requestData["username"]
photoID = requestData["photoID"]
user = session["username"]
print(user + " tagged " + tagged_name + " in photoID: " + photoID)
query = "INSERT into Tag (username, photoID, acceptedTag) VALUES (%s, %s, %s)"
#if the user is tagging themselves
if(user == tagged_name):
with connection.cursor() as cursor:
cursor.execute(query, (tagged_name, photoID, 1))
#if they are tagging someone else
else:
#if query is visiable to the tagged_name then the result (tagged_name, photoID, 0)
query2 = 'SELECT Photo.photoID, timestamp, filePath, photoOwner, caption FROM Photo, Belong, Share Where belong.username = %s and belong.groupOwner = share.groupOwner AND Belong.groupName = share.groupName AND photo.photoID = share.photoID UNION (SELECT Photo.photoID, timestamp, filePath, photoOwner, caption FROM Photo, Follow WHERE (photoOwner = %s ) or (followerUsername = %s AND photoOwner = followeeUsername AND acceptedfollow = TRUE)) ORDER BY Timestamp DESC'
with connection.cursor() as cursor:
cursor.execute(query2, (tagged_name, tagged_name, tagged_name))
vPhoto = cursor.fetchall()
isVisible= 0; #true/false to determine if the person can view the photo
print(vPhoto)
for photo in vPhoto:
#print(photo)
#print(photo['photoID'])
currentPhoto = int(photo['photoID'])
#print("Does the local number above match tag number below")
#print(photoID)
intphoto = int(photoID)
if(currentPhoto == intphoto):
#print("here")
isVisible= 1
with connection.cursor() as cursor:
cursor.execute(query, (tagged_name, photoID, 0))
if(isVisible == 0):
return render_template('/home.html')
return render_template('/images.html')
def show_user_by_publicId(current_user,public_id):
try :
with connection.cursor() as cursor:
sql =( "SELECT company_name , username,usersdetails_firstname ,usersdetails_lastname,usersdetails_avatar,usersdetails_email,usersdetails_phone,usersdetails_position FROM usersdetails "
"LEFT JOIN user on user.public_id = usersdetails.user_public_id "
"LEFT JOIN company on company.company_id = user.company_id"
" WHERE usersdetails.user_public_id = %s")
cursor.execute(sql, (public_id,))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify( {"usersdetails" :rv} )
except Exception as e:
print (e)
return jsonify ({"Code" : "002" , "Message":"User Not found"})
def Listproject(current_user,project_id):
try:
user_public_id = current_user["public_id"]
except Exception as e:
return jsonify({"Status": "Failed", "message": "Error DecodeId" + str(e)}), 500
with connection.cursor() as cursor:
# Read a single record
sql = (" SELECT * from project LEFT JOIN status on status.status_id = project.status_id "
" LEFT JOIN projectdetails on projectdetails.project_public_id = project.project_public_id"
" WHERE project.project_public_id = %s " )
cursor.execute(sql,(project_id,))
result = cursor.fetchall()
cursor.close()
return jsonify({"Status" : "success" ,"projectList": result } ) , 200
def show_user_customers(current_user,):
try :
with connection.cursor() as cursor:
public_id = current_user["public_id"]
sql =( " SELECT customers_address,customers_city,customers_creator_id,customers_enddate,customers_name,customers_postcode,status_name FROM customers "
" LEFT JOIN usercustomers on usercustomers.customers_public_id = customers.customers_public_id "
" LEFT JOIN status on status.status_id = customers.status_id"
" WHERE usercustomers.usercustomers_public_id = %s")
cursor.execute(sql, (public_id,))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify( {"usersdetails" :rv} )
except Exception as e:
print (e)
return jsonify ({"Code" : "002" , "Message":"User Not found"})
def get_useravatar(public_id):
if not public_id :
return jsonify ({"status" : "error" , "message":"Missing PublicId"}) , 404
try :
with connection.cursor() as cursor:
sql = "SELECT usersdetails_avatar FROM usersdetails where usersdetails.user_public_id = %s"
cursor.execute(sql, (public_id,))
rv = cursor.fetchone()
avatar = rv['usersdetails_avatar']
if not rv:
return jsonify ({"status" : "error" , "message":"Failed to find users"}) , 404
connection.commit()
cursor.close()
return redirect(url_for('static', filename='img/' + avatar), code=301)
except Exception as e :
return jsonify ({"Status" : "error" , "message":"Can't GET usersAvatar "}) , 500
def descjobdetails(current_user,public_job):
try:
public_id = current_user["public_id"]
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500
try:
with connection.cursor() as cursor:
sql = ("SELECT * from job LEFT JOIN status on status.status_id = job.status_id "
" LEFT JOIN jobdetails on jobdetails.job_public_id = job.job_public_id WHERE job.job_public_id = %s" )
cursor.execute(sql, (public_job))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify({"Status": "success", "jobList": rv}), 200
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
def ListTeamproject(current_user, public_project):
try:
public_id = current_user.public_id
username = current_user.username
user_id = current_user.id
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200
with connection.cursor() as cursor:
# Read a single record
sql = " SELECT job.job_name , job.job_public_id , status.status_name , teamproject.teamproject_public_id ,job.job_created from job LEFT JOIN project_has_job ON job.job_public_id = project_has_job.job_public_id "\
" LEFT JOIN status on status.status_id = job.status_id "\
" LEFT JOIN teamproject on teamproject.teamproject_public_id = project_has_job.teamproject_public_id"\
" LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id" \
" WHERE project_has_job.project_public_id = %s and teamproject_has_user.user_public_id = %s"
cursor.execute(sql, (public_project, public_id))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify({"Status": "success", "projectList": rv}), 200
def UpdateAssetsisValid(current_user, assets_public, isvalid):
try:
public_id = current_user["public_id"]
except:
return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200
try:
with connection.cursor() as cursor:
sql = (
" update jobhasassets LEFT JOIN assets on assets.assets_id = jobhasassets.assets_id "
" SET jobhasassets.assets_is_valid = %s WHERE assets.assets_public_id = %s "
)
cursor.execute(sql, (isvalid, assets_public))
connection.commit()
cursor.close()
return jsonify({
"Status": "success",
"updateAssets": "success"
}), 200
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
def ListAssetsJob(assets_sn):
try:
with connection.cursor() as cursor:
sql = (
" SELECT assets.assets_public_id , assets.assets_sn ,assetsbrand.assets_brand_name,assetscategories.assets_categories_name,assetsinsurance.assets_insurance_name,assetsseries.assets_series_name, jobhasassets.job_id ,jobhasassets.project_public_id,status.status_name "
" , job.job_name , job.status_id , job_operator_id "
" from assets LEFT JOIN customershasassets on assets.assets_id = customershasassets.assets_id "
" LEFT JOIN status on customershasassets.status_id = status.status_id "
" LEFT JOIN jobhasassets on jobhasassets.assets_id = assets.assets_id "
" LEFT JOIN job on jobhasassets.job_id = job.job_id "
" LEFT JOIN assetsbrand on assetsbrand.assets_brand_id = assets.assets_brand_id"
" LEFT JOIN assetscategories on assetscategories.assets_categories_id = assets.assets_categories_id"
" LEFT JOIN assetsinsurance on assetsinsurance.assets_insurance_id = assets.assets_insurance_id"
" LEFT JOIN assetsseries on assetsseries.assets_series_id = assets.assets_series_id"
" WHERE assets.assets_sn = %s ")
cursor.execute(sql, (assets_sn, ))
rv = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify({"Status": "success", "assets": rv}), 200
except Exception as e:
return jsonify({"Status": "Error", "assets": e}), 500
def match():
if request.method == "POST":
# gets user's code
user = request.form.get("code")
# connect to database
cursor = connection.cursor()
# Select all movies both users have liked
cursor.execute(
"SELECT * FROM top_250 WHERE id IN (SELECT movie_id FROM likes where preference = true AND user_id = %s) AND id IN (SELECT movie_id FROM likes where preference = true AND user_id = %s)",
(session["user_id"], user))
rows = cursor.fetchall()
row_count = cursor.rowcount
if row_count == 0:
return apology("no matches", 400)
# Copy those movies over into a dictionary for display purposes
matches = []
for i in range(row_count):
match = {}
match["title"] = rows[i][0]
match["year"] = rows[i][1]
match["image"] = rows[i][2]
match["rating"] = rows[i][3]
matches.append(match)
return render_template("matched.html", matches=matches)
else:
friend_code = session["user_id"]
return render_template("match.html", friend_code=friend_code)
def login_customers():
data = request.get_json()
if not data or not data['username'] or not data['password']:
return jsonify({ "Code" : "401_1" , 'Error' : "Username or password is empty " } ) , 401
with connection.cursor() as cursor:
sql = "SELECT * FROM usercustomers where usercustomers.usercustomers_userkey = %s"
cursor.execute(sql, (data['username'],))
rv = cursor.fetchone()
if not rv:
return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401
connection.commit()
cursor.close()
if not rv["usercustomers_userkey"] :
return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401
try:
if check_password_hash(rv["usercustomers_passkey"], data['password']):
token = jwt.encode({'public_id' : rv["usercustomers_public_id"],'usercustomers_id' :rv["usercustomers_id"] ,'exp' : rv["usercustomers_exp"] , 'customers_public_id' : rv["customers_public_id"]}, Secret_key)
return jsonify({'token':token.decode('UTF-8')}), 200
else:
return jsonify({"Code": "401_2", 'Error': "Password is incorrect "}), 401
except Exception as e:
return jsonify({"Code": "401_2", 'Error': "Error " +str(e)}), 500
def login():
data = request.get_json()
if not data or not data['username'] or not data['password']:
return jsonify({ "Code" : "401_1" , 'Error' : "Username or password is empty " } ) , 401
with connection.cursor() as cursor:
sql = "SELECT * FROM user where user.username = %s"
cursor.execute(sql, (data['username'],))
rv = cursor.fetchone()
if not rv:
return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401
connection.commit()
cursor.close()
if not rv["username"] :
return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401
try:
if check_password_hash(rv["password"], data['password']):
token = jwt.encode({'public_id' : rv["public_id"],'user_id' :rv["id"] ,'exp' : datetime.datetime.utcnow() + datetime.timedelta(minutes=2440)}, Secret_key)
return jsonify({'token':token.decode('UTF-8')}), 200
else:
return jsonify({"Code": "401_2", 'Error': "Password is incorrect "}), 401
except Exception as e:
return jsonify({"Code": "401_2", 'Error': "Error " +str(e)}), 500
def login():
"""Log user in"""
if request.method == "POST":
# Ensure username was submitted
if not request.form.get("username"):
return apology("must provide username", 403)
# Ensure password was submitted
elif not request.form.get("password"):
return apology("must provide password", 403)
# Query database for username
cursor = connection.cursor()
cursor.execute("SELECT * FROM users WHERE username=%s",
[request.form.get("username")])
# Ensure username exists and password is correct
try:
row = cursor.fetchone()
if not check_password_hash(row[2], request.form.get("password")):
return apology("invalid password", 403)
except:
return apology("invalid username", 403)
# Stop acessing database
cursor.close()
# Remember which user has logged in
session["user_id"] = row[0]
# Redirect user to home page
return redirect("/")
# User reached route via GET (as by clicking a link or via redirect)
else:
return render_template("login.html")
def CountProjectByGroup(current_user):
try:
public_id = current_user["public_id"]
except Exception as e:
return jsonify({"Status": "Failed", "message": "Error DecodeId" + str(e)}), 500
try:
with connection.cursor() as cursor:
# Read a single record
sql = (
" SELECT project.project_public_id , project_name , teamproject.teamproject_public_id , teamproject_name , teamproject_has_project.status_id ,status.status_name from project "
" LEFT JOIN teamproject_has_project on teamproject_has_project.project_public_id = project.project_public_id "
" LEFT JOIN teamproject on teamproject_has_project.teamproject_public_id = teamproject.teamproject_public_id"
" LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id "
" LEFT JOIN project_has_job on project_has_job.project_public_id = project.project_public_id"
" LEFT JOIN job on project_has_job.job_public_id = job.job_public_id"
" LEFT JOIN status on status.status_id = teamproject_has_project.status_id"
" WHERE teamproject_has_user.user_public_id = %s GROUP BY project_public_id"
)
cursor.execute(sql, (public_id))
rv = cursor.fetchall()
connection.commit()
cursor.close()
arr = dict()
for r in rv:
count = 0
val = []
project = r["status_name"]
arr[project] = {}
for a in rv:
if project == a["status_name"]:
count = count + 1
val.append(a)
arr[project] = val
# return jsonify(rv)
return jsonify({"Status": "Success", "projectList:": arr}), 200
except Exception as e :
return jsonify({"Status": "Failed", "message": "Error " +str( e)}), 500
def tearDown(self):
connection.cursor()
