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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 register():
"""Register user"""
if request.method == "POST":
password = request.form.get("password")
username = request.form.get("username")
# Ensure username was submitted
if not username:
return apology("must provide username", 400)
# Ensure password was submitted
elif not password:
return apology("must provide password", 400)
# Ensure confirm password was submitted
elif not request.form.get("confirmation"):
return apology("must confirm password", 400)
# Ensures passwords match
if password != request.form.get("confirmation"):
return apology("passwords do not match", 400)
# Ensure password is 8 charachters or more
#if len(password) < 8:
#return apology("Password must be at least 8 charachters", 400)
#number = False
# Ensure password includes a number
#for letter in password:
#if letter.isnumeric():
#number = True
#if number == False:
#return apology("Password must include a number", 400)
# Ensures User does not already have an account
# Query the databse
cur = connection.cursor()
cur.execute("SELECT * FROM users WHERE username = %s", [username])
# Check if an existing password exists
if cur.fetchone():
return apology("Username already exists", 400)
# Insert user into database
phash = generate_password_hash(password)
cur.execute("INSERT into users (username, password) VALUES(%s, %s)",
(username, phash))
# save changes to database
connection.commit()
# close cursor
cur.close()
# Opens login cursor
# Gets user's ID
login_cursor = connection.cursor()
login_cursor.execute("SELECT id FROM users WHERE username = %s",
[username])
user_id = login_cursor.fetchone()
# Remember which user has logged in, and log them in
session["user_id"] = user_id[0]
# Redirect user to home page
return redirect("/")
else:
return render_template("register.html")
