def Customers_project_test(current_user):
if not current_user["public_id"]:
return jsonify({"status": "Error", "message": "Missing PublicId"}), 404
try:
with connection.cursor() as cursor:
search = False
per_page = 10
q = request.args.get('q')
if q:
search = True
if search == False:
q = ''
Search_q = "%" + q + "%"
page = request.args.get(get_page_parameter(), type=int, default=1)
offset = (page - 1) * per_page
sql_count = (
" SELECT count(*) as count FROM `job` WHERE `job_name` LIKE %s")
cursor.execute(sql_count, (Search_q))
total = cursor.fetchone()
sql = ("SELECT * from job "
" WHERE job_name LIKE %s "
" LIMIT %s OFFSET %s"
)
cursor.execute(sql, (Search_q, per_page, offset))
rv = cursor.fetchall()
connection.commit()
cursor.close()
if not rv:
return jsonify({"Status": "error", "message": "empty "}), 404
return jsonify({"Status": "Success", "details": {'count': total["count"], 'page': page, 'offset': offset, 'per_page': per_page}, "project": rv}), 200
except Exception as e:
return jsonify({"Status": "error", "message": e}), 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()
sql_assets = (
" 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"
" WHERE job.job_public_id = %s "
)
cursor.execute(sql_assets, (public_job))
rv_assets = cursor.fetchall()
connection.commit()
cursor.close()
return jsonify({"Status": "success", "job": rv, "assets": rv_assets}), 200
except Exception as e:
return jsonify({"Status": "Error", "projectList": e}), 500
def Customers_project_id_group(current_user, public_project):
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 job.job_name , job.job_public_id , status.status_name , job.status_id,priority.priority_name,teamproject.teamproject_public_id,teamproject.teamproject_name ,job.job_created , jobdetails.jobdetails_enddate ,job.job_operator_id 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 GROUP BY job.job_public_id ORDER BY status.status_id"
)
cursor.execute(sql, (public_project))
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
cursor.execute(sql, (publicProject,))
result = cursor.fetchall()
cursor.close()
return jsonify({"Status": "success", "projectList": result}), 200
def Customers_project_id(current_user, publicProject):
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, (publicProject,))
result = cursor.fetchall()
cursor.close()
return jsonify({"Status": "success", "projectList": result}), 200
def Customers_stats(current_user):
print(current_user)
if not current_user["public_id"]:
return jsonify({"status": "Error", "message": "Missing PublicId"}), 404
try:
with connection.cursor() as cursor:
sql_count_project = (
" SELECT count(*) as TotalProject FROM `customershasproject` WHERE `customers_public_id` = %s ")
cursor.execute(sql_count_project,
(current_user["customers_public_id"],))
rv_1 = cursor.fetchone()
sql_groupProject = (
" SELECT COUNT(project_has_job.job_public_id ) as job_count ,project.project_name,customershasproject.project_public_id , status.status_name FROM customershasproject "
" LEFT JOIN project on customershasproject.project_public_id = project.project_public_id "
" LEFT JOIN project_has_job on project_has_job.project_public_id = customershasproject.project_public_id "
" LEFT JOIN status on project.status_id = status.status_id"
" WHERE customershasproject.customers_public_id = %s GROUP BY project_has_job.project_public_id "
)
cursor.execute(sql_groupProject,
(current_user["customers_public_id"],))
rv_2 = cursor.fetchall()
sql_count_jobAll = (
" SELECT COUNT(job.job_public_id) as job_status_count ,status.status_name FROM customershasproject "
" LEFT JOIN project on project.project_public_id = customershasproject.project_public_id"
" LEFT JOIN project_has_job on project_has_job.project_public_id = customershasproject.project_public_id "
" LEFT JOIN job on job.job_public_id = project_has_job.job_public_id"
" LEFT JOIN status on status.status_id = job.status_id"
" WHERE customershasproject.customers_public_id = %s GROUP BY status.status_name"
)
cursor.execute(sql_count_jobAll,
(current_user["customers_public_id"],))
rv_3 = cursor.fetchall()
sql_count_assets_not_register = (
" SELECT COUNT(customershasassets.assets_id) as countassets ,status.status_name from customershasassets "
" LEFT JOIN customers on customers.customers_public_id = customershasassets.customers_public_id"
" LEFT JOIN status on status.status_id = customershasassets.status_id "
" WHERE customershasassets.customers_public_id = %s GROUP BY status.status_name"
)
cursor.execute(sql_count_assets_not_register,
(current_user["customers_public_id"],))
rv_4 = cursor.fetchall()
connection.commit()
cursor.close()
if not rv_1:
return jsonify({"status": "error", "message": "Failed "}), 404
return jsonify({"Status": "Success", "totalStatusProject": rv_2, "totalStatusJob": rv_3, "totalproject": rv_1, "totalAssets": rv_4}), 200
except Exception as e:
return jsonify({"Status": "error", "message": e}), 500
def Customers_project(current_user):
if not current_user["public_id"]:
return jsonify({"status": "Error", "message": "Missing PublicId"}), 404
try:
with connection.cursor() as cursor:
sql = ("SELECT project.project_public_id , project.project_name , status.status_name from customershasproject "
" LEFT JOIN customers on customershasproject.customers_public_id = customers.customers_public_id "
" LEFT JOIN project on project.project_public_id = customershasproject.project_public_id "
" LEFT JOIN status on status.status_id = project.status_id "
" WHERE customers.customers_public_id = %s "
)
cursor.execute(sql, (current_user["customers_public_id"],))
rv = cursor.fetchall()
connection.commit()
cursor.close()
if not rv:
return jsonify({"status": "error", "message": "Failed "}), 404
return jsonify({"Status": "Success", "project": rv}), 200
except Exception as e:
return jsonify({"Status": "error", "message": e}), 500
def Customers_ListProject_job(current_user, ProjectID):
if not current_user["public_id"]:
return jsonify({"status": "Error", "message": "Missing PublicId"}), 404
try:
# jobdetails.job_public_id ,jobdetails.jobdetails_enddate , jobdetails.jobdetails_location ,jobdetails.jobdetails_note,teamproject.teamproject_public_id,teamproject.teamproject_name,users_creator_id
with connection.cursor() as cursor:
sql = (" SELECT job.job_public_id , job.job_name , jobdetails.jobdetails_location , jobdetails.jobdetails_startdate, jobdetails.jobdetails_enddate , job.job_operator_id , status.status_name , teamproject.teamproject_public_id , teamproject.teamproject_name, users_creator_id from project_has_job "
" LEFT JOIN job on job.job_public_id = project_has_job.job_public_id "
" LEFT JOIN teamproject on teamproject.teamproject_public_id = project_has_job.teamproject_public_id "
" LEFT JOIN status on job.status_id = status.status_id "
" LEFT JOIN jobdetails on jobdetails.job_public_id = job.job_public_id "
" WHERE project_has_job.project_public_id = %s "
)
cursor.execute(sql, (ProjectID,))
rv = cursor.fetchall()
connection.commit()
cursor.close()
if not rv:
return jsonify({"status": "error", "message": "empty"}), 404
return jsonify({"Status": "Success", "job": rv}), 200
except Exception as e:
return jsonify({"Status": "error", "message": e}), 500