def rehabilitation():
form = DateForm()
rehab_list = Rehabilitation.query.all()
posts = Post.query.order_by(Post.id.desc()).all()
if request.method == "POST":
start_date = datetime.strptime(request.form['start_date'],
"%Y-%m-%d").strftime("%Y-%m-%d")
end_date = datetime.strptime(request.form['end_date'],
"%Y-%m-%d").strftime("%Y-%m-%d")
q = db.engine.execute("SELECT FORMAT((t1.col_total), 2) As col_total \
FROM (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM rehabilitation \
WHERE date_commenced >= %s and date_completed<= %s) t1" , \
(start_date, end_date)).first()
return jsonify({
'data':
render_template('projects/rehab_json.html', q=q, form=form)
})
return render_template('projects/rehabilitation.html',
title='Rehabilitation',
rehab_list=rehab_list,
posts=posts,
form=form)
def upgrading():
form = DateForm()
upgrade_list = Upgrading.query.all()
posts = Post.query.order_by(Post.id.desc()).all()
if request.method == "POST":
start_date = request.form['start_date']
end_date = request.form['end_date']
q = db.engine.execute("SELECT FORMAT((t1.col_total), 2) As col_total \
FROM (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM upgrading \
WHERE date_commenced >= %s and date_completed<= %s) t1" , \
(start_date, end_date)).first()
return jsonify({
'data':
render_template('projects/upgrade_json.html', q=q, form=form)
})
return render_template('projects/upgrading.html',
title='Upgrading',
upgrade_list=upgrade_list,
posts=posts,
form=form)
def completed_periodic():
form = DateForm()
posts = Post.query.order_by(Post.id.desc()).all()
if request.method == "POST":
start_date = request.form['start_date']
end_date = request.form['end_date']
results = db.engine.execute("SELECT FORMAT((t1.col_total + t2.col_total + t3.col_total + \
t4.col_total + t5.col_total + t6.col_total + t7.col_total + \
t8.col_total + t9.col_total), 2) As col_total \
FROM (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM rehabilitation \
WHERE date_commenced >= %s and date_completed<= %s) t1 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM regravelling \
WHERE date_commenced >= %s and date_completed<= %s) t2 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM repairs \
WHERE date_commenced >= %s and date_completed<= %s) t3 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM resealing \
WHERE date_commenced >= %s and date_completed<= %s) t4 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM resurfacing \
WHERE date_commenced >= %s and date_completed<= %s) t5 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM upgrading \
WHERE date_commenced >= %s and date_completed<= %s) t6 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM asphalticoverlay \
WHERE date_commenced >= %s and date_completed<= %s) t7 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM preconstruction \
WHERE date_commenced >= %s and date_completed<= %s) t8 \
CROSS JOIN (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM construction \
WHERE date_commenced >= %s and date_completed<= %s) t9", \
(start_date, end_date, start_date, end_date, start_date, end_date,\
start_date, end_date, start_date, end_date, start_date, end_date, \
start_date, end_date, start_date, end_date, start_date, end_date)).first()
return jsonify({'data': render_template('main/periodic_json.html', results=results, form=form)})
return render_template('main/completed_periodic.html', title='Completed Periodic Projects', posts=posts, form=form)
def table():
form = DateForm()
contract = Contract.query.all()
posts = Post.query.order_by(Post.id.desc()).all()
if request.method == "POST":
start_date = datetime.strptime(request.form['start_date'],
"%Y-%m-%d").strftime("%Y-%m-%d")
end_date = datetime.strptime(request.form['end_date'],
"%Y-%m-%d").strftime("%Y-%m-%d")
q = db.session.query(db.func.sum(Contract.contract_sum)).filter(
Contract.date_commenced >= start_date).filter(
Contract.date_completed <= end_date).first()
return jsonify({
'data':
render_template('projects/tables_json.html', q=q, form=form)
})
return render_template('projects/tables.html',
title='Basic',
posts=posts,
contract=contract,
form=form)
def asphalticoverlay():
form = DateForm()
overlay_list = Asphalticoverlay.query.all()
posts = Post.query.order_by(Post.id.desc()).all()
if request.method == "POST":
start_date = request.form['start_date']
end_date = request.form['end_date']
q = db.engine.execute("SELECT FORMAT((t1.col_total), 2) As col_total \
FROM (SELECT IFNULL(SUM(contract_sum),0) As col_total FROM asphalticoverlay \
WHERE date_commenced >= %s and date_completed<= %s) t1" , \
(start_date, end_date)).first()
return jsonify({
'data':
render_template('projects/asphaltic_json.html', q=q, form=form)
})
return render_template('projects/asphaltic_overlay.html',
title='Asphaltic Overlay',
overlay_list=overlay_list,
posts=posts,
form=form)
def planning_periodic():
form=DateForm()
posts = Post.query.order_by(Post.id.desc()).all()
return render_template('main/planning_periodic.html', title='Periodic Projects Under Planning', posts=posts, form=form)
def ongoing_periodic():
form=DateForm()
posts = Post.query.order_by(Post.id.desc()).all()
return render_template('main/ongoing_periodic.html', title='Ongoing Periodic Projects', posts=posts, form=form)