def reporting_orgs_summary():
reporting_orgs = qorganisations.get_reporting_orgs()
response_statuses = [
resp.as_dict() for resp in qmonitoring.response_statuses()
]
orgs = generate_reporting_organisation_checklist(reporting_orgs,
response_statuses)
def generate_summary(list_of_quarters, orgs):
out = dict(
map(lambda q: (q, {
'True': 0,
'False': 0,
"Total": 0
}), list_of_quarters.keys()))
for ro in orgs:
for disb_q, disb_v in ro["disbursements"].items():
if disb_v["status"]["name"] == "Donor responded with data":
out[disb_q]['True'] += 1
else:
out[disb_q]['False'] += 1
out[disb_q]["Total"] += 1
return out
list_of_quarters = util.Last4Quarters().list_of_quarters()
summary = generate_summary(list_of_quarters, orgs)
return jsonify(summary=summary,
current_year=util.FY("current").fy_fy(),
previous_year=util.FY("previous").fy_fy(),
list_of_quarters=list_of_quarters)
def counterpart_funding():
next_fy = util.FY("current").fiscal_year.name
fiscal_year = request.args.get("fiscal_year", next_fy)
start_date, end_date = qactivity.get_earliest_latest_dates_filter(
{'key': 'domestic_external', 'val': 'external'})
next_fy_end_date = util.FY("next").date("end")
fys = [str(fy) for fy in util.fys_in_date_range(
start_date, max(end_date, next_fy_end_date))]
def annotate_activity(activity):
return {
'id': activity.id,
'title': activity.title,
'reporting_org_name': activity.reporting_org.name,
'sector_name': ", ".join([sector.codelist_code.name for sector in activity.classification_data.get('mtef-sector', {}).get('entries', [])]),
'ministry_name': ", ".join([ministry.codelist_code.name for ministry in activity.classification_data.get('aligned-ministry-agency', {}).get('entries', [])]),
'gol_requested': activity._fy_counterpart_funding,
'donor_planned': activity._fy_forwardspends
}
activities = [annotate_activity(activity) for activity in
qcounterpart_funding.annotate_activities_with_aggregates(fiscal_year)]
return jsonify(
fy=util.FY("next").fy_fy(),
activities=activities,
fiscalYears=fys,
fiscalYear=str(fiscal_year)
)
def fwddata_query(current_previous="current"):
since = util.FY(current_previous).date("start")
until = util.FY(current_previous).date("end")
return db.session.query(
func.sum(models.ActivityForwardSpend.value).label("value"),
models.Activity.reporting_org_id).join(models.Activity).filter(
models.ActivityForwardSpend.value != 0).filter(
models.ActivityForwardSpend.period_start_date >= since).filter(
models.ActivityForwardSpend.period_end_date <= until
).group_by(models.Activity.reporting_org_id).order_by(
models.Activity.reporting_org_id.desc()).all()
def reporting_orgs():
reporting_orgs = qorganisations.get_reporting_orgs()
response_statuses = [
resp.as_dict() for resp in qmonitoring.response_statuses()
]
orgs = generate_reporting_organisation_checklist(reporting_orgs,
response_statuses)
return jsonify(orgs=orgs,
previous_year=util.FY("previous").fy_fy(),
current_year=util.FY("current").fy_fy(),
next_year=util.FY("next").fy_fy(),
list_of_quarters=util.Last4Quarters().list_of_quarters())
def reporting_orgs_user():
if request.method == "POST":
if ("organisation_id" in request.json) and ("response_id"
in request.json):
qmonitoring.update_organisation_response(request.json)
return jsonify(result=True)
#FIXME change to roles
if ("user_id" in request.args) or ("admin" not in current_user.roles_list):
reporting_orgs = qorganisations.get_reporting_orgs(
user_id=request.args.get('user_id', current_user.id))
user = models.User.query.get(
request.args.get('user_id', current_user.id))
user_name = user.name
user_id = user.id
else:
reporting_orgs = qorganisations.get_reporting_orgs()
user_name = None
user_id = None
if "admin" in current_user.roles_list:
users = [{
'value': user.id,
'text': user.name
} for user in quser.users_with_role('desk-officer')]
else:
users = []
response_statuses = [
resp.as_dict() for resp in qmonitoring.response_statuses()
]
orgs = generate_reporting_organisation_checklist(reporting_orgs,
response_statuses)
data_collection_calendar = qmonitoring.generate_data_collection_calendar()
return jsonify(orgs=orgs,
previous_year=util.FY("previous").fy_fy(),
current_year=util.FY("current").fy_fy(),
next_year=util.FY("next").fy_fy(),
list_of_quarters=util.Last4Quarters().list_of_quarters(),
users=users,
user_name=user_name,
user_id=user_id,
statuses=response_statuses,
data_collection_calendar=data_collection_calendar)
def available_fys_fqs():
def mtef_or_disbursements():
"""Set reporting functionality to highlight MTEF or disbursement
data import by default, depending on where we are in the year"""
budget_preparation_month = 2
if datetime.datetime.utcnow().date().month == budget_preparation_month:
return "mtef"
else:
return "disbursements"
return jsonify(fys=util.available_fys(),
fys_fqs=util.available_fy_fqs_as_dict(),
current_fy=util.FY("current").fy_fy(),
previous_fy_fq=util.previous_fy_fq(),
mtef_or_disbursements=mtef_or_disbursements())
def project_development_tracking():
current_fy = util.FY("previous").fiscal_year.name
fiscal_year = request.args.get("fiscal_year", current_fy)
activities = models.Activity.query.filter_by(
domestic_external="domestic"
).all()
milestones = models.Milestone.query.filter_by(
domestic_external="domestic"
).order_by(
models.Milestone.milestone_order
).all()
sum_appropriations = qreports.sum_transactions(
fiscal_year, 0, 'domestic', 'sum_appropriations', 'C')
sum_allotments = qreports.sum_transactions(
fiscal_year, 0, 'domestic', 'sum_allotments', '99-A')
sum_disbursements = qreports.sum_transactions(
fiscal_year, 0, 'domestic', 'sum_disbursements', 'D')
def annotate_activity(activity):
out = OrderedDict({
'id': activity.id,
'title': activity.title,
'implementer': ", ".join(
[implementer.name for implementer in activity.implementing_organisations]
),
'sum_appropriations': sum_appropriations.get(activity.id, 0.00),
'sum_allotments': sum_allotments.get(activity.id, 0.00),
'sum_disbursements': sum_disbursements.get(activity.id, 0.00)
})
[out.update({ms['name']: ms['achieved']})
for ms in activity.milestones_data]
return out
milestone_data = [annotate_activity(activity) for activity in activities]
start_date, end_date = qactivity.get_earliest_latest_dates_filter(
{'key': 'domestic_external', 'val': 'domestic'})
if start_date is not None:
fys = [str(fy) for fy in util.fys_in_date_range(start_date, end_date)]
else:
fys = []
return jsonify(activities=milestone_data,
milestones=[milestone.name for milestone in milestones],
fiscalYears=fys,
fiscalYear=str(fiscal_year))
def aid_disbursements_api():
_current_fy = util.FY("previous")
current_fy = _current_fy.fiscal_year.name
fiscal_year = request.args.get("fiscal_year", current_fy)
start_of_fy = _current_fy.fiscal_year.start
days_since_fy_began = (
(datetime.datetime.utcnow().date()-start_of_fy).days)
progress_time = min(round(days_since_fy_began/365.0*100.0, 2), 100.0)
start_date, end_date = qactivity.get_earliest_latest_dates_filter(
{'key': 'domestic_external', 'val': 'external'})
start_date = max(start_date, current_app.config['EARLIEST_DATE'])
fys = [str(fy) for fy in util.fys_in_date_range(start_date, end_date)]
return jsonify(activities=qreports.make_forwardspends_disbursements_data(fiscal_year),
progress_time=progress_time,
days_since_fy_began=days_since_fy_began,
fy_start_day=datetime.datetime.strftime(
start_of_fy, "%d.%m.%Y"),
fiscalYears=fys,
fiscalYear=str(fiscal_year))
def make_sector_doc(sector_code):
sector_brief_file = BytesIO()
sector = qcodelists.get_code_by_id('mtef-sector', sector_code)
sector_totals = qaggregates.aggregate('reporting-org',
req_filters=[
models.CodelistCode.codelist_code == 'mtef-sector',
models.CodelistCode.code == sector_code
],
req_finances_joins=[
models.ActivityFinancesCodelistCode,
models.CodelistCode
],
req_forwardspends_joins=[
models.ActivityCodelistCode,
models.CodelistCode
]
)
current_fy = util.FY('current')
filtered_current_fy = list(
filter(lambda entry: entry.fiscal_year == current_fy.fy_fy(), sector_totals))
donors = {}
for item in filtered_current_fy:
if item.code not in donors:
donors[item.code] = {
'code': item.code,
'name': item.name,
'disbursements': 0.00,
'forwardspends': 0.00
}
if item.transaction_type == 'D':
donors[item.code]['disbursements'] = round(
item.total_value/1000000)
if item.transaction_type == 'FS':
donors[item.code]['forwardspends'] = round(
item.total_value/1000000)
ongoing_query = db.session.query(
func.sum(models.ActivityFinances.transaction_value).label("total_value")
).join(models.Activity
).join(models.ActivityFinancesCodelistCode
).join(models.CodelistCode
).filter(
models.ActivityFinances.transaction_type == 'D',
models.Activity.activity_status == 2,
models.CodelistCode.codelist_code == 'mtef-sector',
models.CodelistCode.code == sector_code
).scalar()
ongoing = round(ongoing_query/1000000)
filtered_current_fy_disb = list(
filter(lambda item: item.transaction_type == 'D', filtered_current_fy))
current_fy_disb = round(
sum(map(lambda item: item.total_value, filtered_current_fy_disb))/1000000)
filtered_current_fy_fs = list(
filter(lambda item: item.transaction_type == 'FS', filtered_current_fy))
current_fy_fs = round(
sum(map(lambda item: item.total_value, filtered_current_fy_fs))/1000000)
filtered_cumulative_d = list(
filter(lambda item: item.transaction_type == 'D', sector_totals))
cumulative_d = round(
sum(map(lambda item: item.total_value, filtered_cumulative_d))/1000000)
document = docx.Document("projectdashboard/lib/docx/template.docx")
document.add_heading('{} Sector Brief'.format(sector.name), 1)
document.add_heading("General Information", 2)
# Sector donors
paragraph = document.add_paragraph()
run = paragraph.add_run("Sector donors: ")
font = run.font
font.bold = True
run = paragraph.add_run(
f"Includes current fiscal year projected and actual disbursement information for donors operating in the sector for { current_fy.fy_fy() } ({ current_fy.date_format('start') } to { current_fy.date_format('end') })")
font = run.font
font.italic = True
sector_donors = [(
donor['name'],
"USD {:,}m".format(donor['forwardspends']),
"USD {:,}m".format(donor['disbursements'])
) for donor in donors.values()]
if len(sector_donors) > 0:
docx.rows_to_table(sector_donors, document, [
'Donor', 'Amount Projected', 'Amount Disbursed'])
# Sector portfolio value
paragraph = document.add_paragraph()
paragraph = document.add_paragraph()
run = paragraph.add_run("Sector portfolio value: ")
font = run.font
font.bold = True
run = paragraph.add_run(
"Includes cumulative financial information about the sector")
font = run.font
font.italic = True
sector_portfolio = [(
"",
"USD {:,}m".format(ongoing),
"",
"",
"USD {:,}m".format(current_fy_disb),
"USD {:,}m".format(current_fy_fs),
"USD {:,}m".format(cumulative_d))
]
docx.rows_to_table(sector_portfolio, document, ['Total PAPD Financing Cost', 'Total value of all ongoing projects', 'GOL budget allocation',
'PAPD financing Gap', 'Current Fiscal year projection', 'Current fiscal year disbursement', 'Cumulative Disbursement from FY12/13'])
# Sector thematic
paragraph = document.add_paragraph()
paragraph = document.add_paragraph()
run = paragraph.add_run("Key Sector Thematic Areas (PAPD): ")
font = run.font
font.bold = True
run = paragraph.add_run("Includes area of focus in the PAPD")
font = run.font
font.italic = True
sector_thematic = [
("", ""),
("", ""),
("", ""),
]
docx.rows_to_table(sector_thematic, document, ['Thematic area', 'Comment'])
# Sector deliverables
paragraph = document.add_paragraph()
paragraph = document.add_paragraph()
run = paragraph.add_run("Key Expected deliverables: ")
font = run.font
font.bold = True
run = paragraph.add_run("Includes a summary of key expected deliverables ")
font = run.font
font.italic = True
sector_deliverables = [
("", ""),
("", ""),
("", ""),
]
docx.rows_to_table(sector_deliverables, document, [
'Key expected deliverable', 'Comment'])
# Sector deliverables
paragraph = document.add_paragraph()
paragraph = document.add_paragraph()
run = paragraph.add_run("Key Results Achieved: ")
font = run.font
font.bold = True
run = paragraph.add_run("Includes a summary of key results achieved")
font = run.font
font.italic = True
results_achieved = [
("", ""),
("", ""),
("", ""),
]
docx.rows_to_table(results_achieved, document, [
'Results achieved', 'Comment'])
# Sector thematic gap
paragraph = document.add_paragraph()
paragraph = document.add_paragraph()
run = paragraph.add_run("Sector thematic gap: ")
font = run.font
font.bold = True
run = paragraph.add_run(
"Includes PAPD thematic areas still requiring funding")
font = run.font
font.italic = True
thematic_gap = [
("", ""),
("", ""),
("", ""),
]
docx.rows_to_table(thematic_gap, document, ['Thematic area', 'Comment'])
# Issues and Challenges:
paragraph = document.add_paragraph()
paragraph = document.add_paragraph()
run = paragraph.add_run("Issues and Challenges: ")
font = run.font
font.bold = True
run = paragraph.add_run(
"Includes a summary of key issues and challenges requiring action")
font = run.font
font.italic = True
issues_challenges = [
("", ""),
("", ""),
("", ""),
]
docx.rows_to_table(issues_challenges, document, [
'Issues/challenges', 'Status'])
# Save
document.save(sector_brief_file)
return sector_brief_file
def available_fys():
fy = util.FY("previous").fiscal_year.name
return jsonify(fys=util.available_fys_as_dict(), current_fy=fy)