def api_activity_finances(activity_id):
"""GET returns a list of all financial data for a given activity_id.
POST also accepts financial data to be added or deleted."""
if request.method == "POST":
if request.form["action"] == "add":
data = {
"transaction_type": request.form["transaction_type"],
"transaction_date": request.form["transaction_date"],
"transaction_value": request.form["transaction_value"],
"aid_type": request.form["aid_type"],
"finance_type": request.form["finance_type"],
"provider_org_id": request.form["provider_org_id"],
"receiver_org_id": request.form["receiver_org_id"],
"classifications": {
"mtef-sector": request.form["mtef_sector"]
}
}
result = qfinances.add_finances(activity_id, data)
elif request.form["action"] == "delete":
result = qfinances.delete_finances(activity_id,
request.form["transaction_id"])
return str(result)
elif request.method == "GET":
finances = list(
map(lambda x: x.as_dict(),
qactivity.get_activity(activity_id).finances))
return jsonify(finances=finances)
def api_activity_counterpart_funding(activity_id):
"""GET returns a list of all counterpart funding for a given activity_id.
POST also accepts counterpart funding data to be added, deleted, updated."""
if request.method == "POST":
if request.form["action"] == "add":
required_date = util.fq_fy_to_date(
1, int(request.form["required_fy"])).date().isoformat()
data = {
"required_value": request.form["required_value"],
"required_date": required_date,
"budgeted": False,
"allotted": False,
"disbursed": False,
}
result = qcounterpart_funding.add_entry(activity_id, data)
elif request.form["action"] == "delete":
result = qcounterpart_funding.delete_entry(activity_id,
request.form["id"])
elif request.form["action"] == "update":
attr = request.form['attr']
value = request.form['value']
if value == "true":
value = True
elif value == "false":
value = False
if attr == "required_fy":
attr = "required_date"
value = util.fq_fy_to_date(1, int(value)).date().isoformat()
data = {
'activity_id': activity_id,
'attr': attr,
'value': value,
'id': request.form['id'],
}
update_status = qcounterpart_funding.update_entry(data)
if update_status == True:
return "success"
return "error"
return str(result)
elif request.method == "GET":
def to_fy(counterpart_funding):
cf = counterpart_funding.as_dict()
cf["required_fy"], fq = util.date_to_fy_fq(
counterpart_funding.required_date)
return cf
counterpart_funding = sorted(list(
map(lambda cf: to_fy(cf),
qactivity.get_activity(activity_id).counterpart_funding)),
key=lambda x: x["required_date"])
return jsonify(counterpart_funding=counterpart_funding,
fiscal_years=range(2013, 2025))
def api_activity_locations(activity_id):
"""GET returns a list of all locations for a given activity_id.
POST also accepts locations to be added or deleted."""
if request.method == "POST":
if request.form["action"] == "add":
result = qlocation.add_location(activity_id, request.form["location_id"])
elif request.form["action"] == "delete":
result = qlocation.delete_location(activity_id, request.form["location_id"])
return str(result)
elif request.method == "GET":
locations = list(map(lambda x: x.as_dict(),
qactivity.get_activity(activity_id).locations))
return jsonify(locations = locations)
def activity_edit(activity_id):
activity = qactivity.get_activity(activity_id)
locations = qlocation.get_locations_country(
activity.recipient_country_code)
return render_template("activity_edit.html",
activity = activity,
loggedinuser=current_user,
codelists = codelists.get_codelists(),
locations = locations,
api_locations_url ="/api/locations/%s/" % activity.recipient_country_code,
api_activity_locations_url = "/api/activity_locations/%s/" % activity_id,
api_activity_finances_url = "/api/activity_finances/%s/" % activity_id,
api_update_activity_finances_url = "/api/activity_finances/%s/update_finances/" % activity_id,
users = quser.user()
)
def api_activity_locations(activity_id):
"""GET returns a list of all locations for a given activity_id.
POST also accepts locations to be added or deleted."""
if request.method == "POST":
if request.form["action"] == "add":
result = qlocation.add_location(activity_id,
request.form["location_id"])
elif request.form["action"] == "delete":
result = qlocation.delete_location(activity_id,
request.form["location_id"])
return str(result)
elif request.method == "GET":
locations = list(
map(lambda x: x.as_dict(),
qactivity.get_activity(activity_id).locations))
return jsonify(locations=locations)
def api_activity_finances(activity_id):
"""GET returns a list of all financial data for a given activity_id.
POST also accepts financial data to be added or deleted."""
if request.method == "POST":
if request.form["action"] == "add":
data = {"transaction_type": request.form["transaction_type"]}
result = qfinances.add_finances(activity_id, data)
elif request.form["action"] == "delete":
result = qfinances.delete_finances(activity_id,
request.form["transaction_id"])
return str(result)
elif request.method == "GET":
finances = list(
map(lambda x: x.as_dict(),
qactivity.get_activity(activity_id).finances))
return jsonify(finances=finances)
def api_activity_finances(activity_id):
"""GET returns a list of all financial data for a given activity_id.
POST also accepts financial data to be added or deleted."""
if request.method == "POST":
if request.form["action"] == "add":
data = {
"transaction_type": request.form["transaction_type"]
}
result = qfinances.add_finances(activity_id, data)
elif request.form["action"] == "delete":
result = qfinances.delete_finances(activity_id, request.form["transaction_id"])
return str(result)
elif request.method == "GET":
finances = list(map(lambda x: x.as_dict(),
qactivity.get_activity(activity_id).finances))
return jsonify(finances = finances)
def activity_edit(activity_id):
activity = qactivity.get_activity(activity_id)
locations = qlocation.get_locations_country(
activity.recipient_country_code)
return render_template(
"activity_edit.html",
activity=activity,
loggedinuser=current_user,
codelists=codelists.get_codelists(),
organisations=qorganisations.get_organisations(),
locations=locations,
api_locations_url=url_for("api.api_locations", country_code=activity.recipient_country_code),
api_activity_locations_url=url_for("api.api_activity_locations", activity_id=activity_id),
api_activity_finances_url=url_for("api.api_activity_finances", activity_id=activity_id),
api_activity_milestones_url=url_for("api.api_activity_milestones", activity_id=activity_id),
api_update_activity_finances_url=url_for("api.finances_edit_attr", activity_id=activity_id),
api_iati_search_url=url_for("api.api_iati_search"),
api_activity_forwardspends_url=url_for("api.api_activity_forwardspends", activity_id=activity_id),
api_activity_counterpart_funding_url = url_for("api.api_activity_counterpart_funding", activity_id=activity_id),
users=quser.user()
)
def activity(activity_id):
activity = qactivity.get_activity(activity_id)
if not activity:
return(abort(404))
locations = qlocation.get_locations_country(
activity.recipient_country_code)
return render_template(
"activity.html",
activity=activity,
loggedinuser=current_user,
codelists=codelists.get_codelists(),
codelist_lookups=codelists.get_codelists_lookups(),
locations=locations,
api_locations_url=url_for("api.api_locations", country_code=activity.recipient_country_code),
api_activity_locations_url=url_for("api.api_activity_locations", activity_id=activity_id),
api_activity_finances_url=url_for("api.api_activity_finances", activity_id=activity_id),
api_update_activity_finances_url=url_for("api.finances_edit_attr", activity_id=activity_id),
api_iati_search_url=url_for("api.api_iati_search"),
api_activity_forwardspends_url=url_for("api.api_activity_forwardspends", activity_id=activity_id),
users=quser.user()
)
def api_activity_forwardspends(activity_id, fiscal_year=True):
"""GET returns a list of all forward spend data for a given activity_id.
POST updates value for a given forwardspend_id."""
if request.method == "GET":
if not fiscal_year == False:
data = qactivity.get_activity(activity_id).forwardspends
forwardspends = list(
map(lambda fs_db: fs_db.as_dict(),
qactivity.get_activity(activity_id).forwardspends))
# Return fiscal years here
years = sorted(
set(
map(lambda fs: util.date_to_fy_fq(fs["value_date"])[0],
forwardspends)))
out = OrderedDict()
for year in years:
out[year] = OrderedDict({
"year":
"FY{}".format(util.fy_to_fyfy(str(year))),
"total_value":
0.00
})
for forwardspend in sorted(forwardspends,
key=lambda k: k["value_date"]):
if util.date_to_fy_fq(
forwardspend["period_start_date"])[0] == year:
fq = util.date_to_fy_fq(
forwardspend["period_start_date"])[1]
out[year]["Q{}".format(fq)] = forwardspend
out[year]["total_value"] += float(
forwardspend["value"])
out = list(out.values())
quarters = util.make_quarters_text(util.LR_QUARTERS_MONTH_DAY)
return jsonify(forwardspends=out, quarters=quarters)
else:
data = qactivity.get_activity(activity_id).forwardspends
forwardspends = list(
map(lambda fs_db: fs_db.as_dict(),
qactivity.get_activity(activity_id).forwardspends))
years = sorted(
set(map(lambda fs: fs["value_date"].year, forwardspends)))
out = OrderedDict()
for year in years:
out[year] = {"year": year, "total_value": 0.00}
for forwardspend in forwardspends:
if forwardspend["period_start_date"].year == year:
fq = MONTHS_QUARTERS[
forwardspend["period_start_date"].month]
out[year]["Q{}".format(fq)] = forwardspend
out[year]["total_value"] += float(
forwardspend["value"])
out = list(out.values())
quarters = util.make_quarters_text(util.QUARTERS_MONTH_DAY)
return jsonify(forwardspends=out, quarters=quarters)
elif request.method == "POST":
data = {"id": request.form["id"], "value": request.form["value"]}
update_status = qfinances.update_fs_attr(data)
if update_status == True:
return "success"
return "error"
def api_activities_by_id_complete(activity_id):
cl_lookups = get_codelists_lookups()
activity = qactivity.get_activity(activity_id).as_jsonable_dict()
return jsonify(activity)
def api_activities_by_id(activity_id):
cl_lookups = get_codelists_lookups()
activity = qactivity.get_activity(activity_id)
data = qgenerate_csv.activity_to_json(activity, cl_lookups)
return jsonify(data)
def import_xls(input_file, column_name=u"2018 Q1 (D)"):
xl_workbook = xlrd.open_workbook(filename=input_file.filename,
file_contents=input_file.read())
num_sheets = len(xl_workbook.sheet_names())
num_updated_activities = 0
activity_id = None
cl_lookups = get_codelists_lookups()
cl_lookups_by_name = get_codelists_lookups_by_name()
try:
for sheet_id in range(0, num_sheets):
input_file.seek(0)
data = xlsx_to_csv.getDataFromFile(input_file.filename,
input_file.read(), sheet_id,
True)
for row in data: # each row is one ID
if column_name not in row:
flash(
u"The column {} containing financial data was not \
found in the uploaded spreadsheet!".format(column_name),
"danger")
raise Exception
if ((row[column_name] == "") or (float(row[column_name]) == 0)
or (float(row[column_name]) == "-")):
continue
activity_id = row[u"ID"]
activity = qactivity.get_activity(activity_id)
if not activity:
flash(
u"Warning, activity ID \"{}\" with title \"{}\" was not found in the system \
and was not imported! Please create this activity in the \
system before trying to import.".format(
row[u'ID'], row[u'Activity Title']), "warning")
continue
existing_activity = activity_to_json(activity, cl_lookups)
row_value, row_currency = tidy_amount(row[column_name])
updated_activity_data = update_activity_data(
activity, existing_activity, row, cl_lookups_by_name)
#FIXME need to handle multiple currencies later... also handle this in process_transaction()
difference = row_value - float(
existing_activity.get(column_name, 0))
if (difference == 0) and (updated_activity_data == False):
continue
if difference != 0:
activity.finances.append(
process_transaction(activity, difference, row_currency,
column_name))
db.session.add(activity)
num_updated_activities += 1
qactivity.activity_updated(activity.id)
if difference == 0:
# Financial values not updated, only other activity data
flash(
u"Updated {} (Project ID: {})".format(
activity.title, activity.id), "success")
elif existing_activity.get(column_name, 0) != 0:
# Non-zero financial values were previously provided and should be adjusted upwards/downwards
flash(
u"Updated {} for {} (Project ID: {}); previous value was {}; \
new value is {}. New entry for {} added.".format(
util.column_data_to_string(column_name),
activity.title, activity.id,
existing_activity.get(column_name),
row.get(column_name), difference), "success")
else:
# Financial values were not previously provided, and are now entered
flash(
u"Updated {} for {} (Project ID: {})".format(
util.column_data_to_string(column_name),
activity.title, activity.id), "success")
except Exception, e:
if activity_id:
flash(
u"""There was an unexpected error when importing your
projects, there appears to be an error around activity ID {}.
The error was: {}""".format(activity_id, e), "danger")
else:
flash(
u"""There was an unexpected error when importing your projects,
the error was: {}""".format(e), "danger")
def import_xls_mtef(input_file):
xl_workbook = xlrd.open_workbook(filename=input_file.filename,
file_contents=input_file.read())
num_sheets = len(xl_workbook.sheet_names())
num_updated_activities = 0
activity_id = None
cl_lookups = get_codelists_lookups()
cl_lookups_by_name = get_codelists_lookups_by_name()
def filter_mtef(column):
pattern = "(.*) \(MTEF\)$"
return re.match(pattern, column)
try:
for sheet_id in range(0, num_sheets):
input_file.seek(0)
data = xlsx_to_csv.getDataFromFile(input_file.filename,
input_file.read(), sheet_id,
True)
mtef_cols = filter(filter_mtef, data[0].keys())
if len(mtef_cols) == 0:
flash(
"No columns containing MTEF projections data \
were found in the uploaded spreadsheet!", "danger")
raise Exception
for row in data: # each row is one ID
activity_id = row[u"ID"]
activity = qactivity.get_activity(activity_id)
if not activity:
flash(
"Warning, activity ID \"{}\" with title \"{}\" was not found in the system \
and was not imported! Please create this activity in the \
system before trying to import.".format(
row[u'ID'], row[u'Activity Title']), "warning")
continue
existing_activity = activity_to_json(activity, cl_lookups)
updated_activity_data = update_activity_data(
activity, existing_activity, row, cl_lookups_by_name)
updated_years = []
for mtef_year in mtef_cols:
new_fy_value, row_currency = tidy_amount(row[mtef_year])
fy_start, fy_end = re.match("FY(\d*)/(\d*) \(MTEF\)",
mtef_year).groups()
existing_fy_value = sum([
float(existing_activity["20{} Q1 (MTEF)".format(
fy_start)]),
float(existing_activity["20{} Q2 (MTEF)".format(
fy_start)]),
float(existing_activity["20{} Q3 (MTEF)".format(
fy_start)]),
float(existing_activity["20{} Q4 (MTEF)".format(
fy_start)])
])
#FIXME need to handle multiple currencies later... also handle this in process_transaction()
difference = new_fy_value - existing_fy_value
# We ignore differences < 1 USD, because this can be due to rounding errors
# when we divided input date by 4.
if round(difference) == 0:
continue
# Create 1/4 of new_fy_value for each quarter in this FY
value = round(new_fy_value / 4.0, 2)
for _fq in [1, 2, 3, 4]:
year, quarter = util.lr_quarter_to_cal_quarter(
int("20{}".format(fy_start)), _fq)
inserted = qfinances.create_or_update_forwardspend(
activity_id, quarter, year, value, u"USD")
updated_years.append(u"FY{}/{}".format(fy_start, fy_end))
if updated_years or updated_activity_data:
num_updated_activities += 1
qactivity.activity_updated(activity.id)
if updated_years:
#FIXME there is an issue with a maximum number of flash messages
# so this sometimes breaks -- this is the reason we only display
# for MTEF updates as for these it is more important.
flash(
u"Updated MTEF projections for {} for {} (Project ID: {})"
.format(", ".join(updated_years), activity.title,
activity.id), "success")
except Exception, e:
if activity_id:
flash(
"""There was an unexpected error when importing your
projects, there appears to be an error around activity ID {}.
The error was: {}""".format(activity_id, e), "danger")
else:
flash(
"""There was an unexpected error when importing your projects,
the error was: {}""".format(e), "danger")
