def import_data(cc_project_code, activity_ids, activities_fields_options):
activity = qactivity.get_activity(activity_ids[0])
activity_id = activity.id
activities = [qactivity.get_activity(
_activity_id) for _activity_id in activity_ids]
activities_lookup = dict((_activity.id, _activity)
for _activity in activities)
activities_finances = [_finance for _activity in activities_lookup.values(
) for _finance in _activity.finances]
transactions_before = len(activities_finances)
activity_retained_finances = list(
filter(lambda transaction: transaction.transaction_type == 'C', activities_finances))
cc_finances = make_cc_finances(activity, cc_project_code)
activity.finances = cc_finances + activity_retained_finances
forwardspends = combined_forwardspends(_forwardspend for _activity in activities_lookup.values(
) for _forwardspend in _activity.forwardspends)
activity.forwardspends = []
db.session.add(activity)
db.session.commit()
# Set data from other activity (if we are merging activities)
for field, field_activity_id in activities_fields_options.items():
if field_activity_id == activity_id:
continue
field_data = getattr(qactivity.get_activity(field_activity_id), field)
setattr(activity, field, field_data)
for delete_id in activity_ids:
# We don't delete the activity that we are merging
if delete_id == activity_id:
continue
delete_activity = qactivity.get_activity(delete_id)
db.session.delete(delete_activity)
activity.forwardspends = forwardspends
transactions_after = len(activity.finances)
db.session.add(activity)
db.session.commit()
return {
'id': activity.id,
'title': activity.title,
'transactions_before': transactions_before,
'transactions_after': transactions_after}
def api_activities_finances_by_id(activity_id):
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
finances = qactivity.get_finances_by_activity_id(
activity_id, request.args.get('by_year'))
return jsonify(finances=OrderedDict(finances))
def check_permissions(permission_name, permission_value=None, activity_id=None):
if "admin" in current_user.roles_list:
return True
if activity_id:
act = qactivity.get_activity(activity_id)
# If the activity is in draft, don't show it to unauthenticated users
if (act.published == False) and (current_user.is_authenticated == False):
return False
if permission_name in ("results-data-entry", "results-data-design"):
if "edit" in current_user.roles_list:
return True
if permission_name in ("view") and "view" in current_user.permissions_list:
return True
if permission_name in ("view", "edit"):
if current_user.permissions_dict.get(permission_name, "none") in (permission_value, "both"):
return True
if permission_name in ("new"):
if current_user.permissions_dict.get("edit", "none") != "none":
return True
if "desk-officer" in current_user.roles_list:
return True
if activity_id:
check = (check_activity_permissions(permission_name, activity_id))
if check:
return True
return False
def api_activity_forwardspends(activity_id):
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
# GET returns a list of all forward spend data for a given activity_id.
if request.method == "GET":
data = qactivity.get_activity(activity_id).forwardspends
forwardspends = [
fs_db.as_dict()
for fs_db in qactivity.get_activity(activity_id).forwardspends
]
# Return fiscal years here
years = sorted(
set(
map(lambda fs: util.date_to_fy_fq_present(fs["value_date"])[0],
forwardspends)))
out = OrderedDict()
for year in years:
out[year] = OrderedDict({
"year":
"FY{}".format(util.fy_to_fyfy_present(str(year))),
"total_value":
0.00
})
for forwardspend in sorted(forwardspends,
key=lambda k: k["value_date"]):
year, fq = util.date_to_fy_fq_present(
forwardspend["period_start_date"])
out[year]["Q{}".format(fq)] = forwardspend
out[year]["total_value"] += forwardspend['value']
out = list(out.values())
quarters = util.make_quarters_text_present()
return jsonify(forwardspends=out, quarters=quarters)
# POST updates value for a given forwardspend_id.
else:
request_data = request.get_json()
if request_data["value"] in (None, " ", ""):
value = 0
else:
value = request_data["value"]
data = {"id": request_data["id"], "value": value}
update_status = qfinances.update_fs_attr(data)
if update_status is True:
return "success"
return "error"
def activity_delete(activity_id):
activity = qactivity.get_activity(activity_id)
if (((activity.domestic_external == 'domestic') and
('piu-desk-officer' in current_user.roles_list))
or (getattr(current_user, "administrator"))
or ('admin' in current_user.roles_list)):
result = qactivity.delete_activity(activity_id)
else:
abort(403)
if result:
return jsonify({'msg': "Successfully deleted that activity"}), 200
else:
return jsonify({'msg': "Sorry, unable to delete that activity"}), 500
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."""
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
if request.method == "POST":
if not quser.check_permissions("edit", None, activity_id):
return abort(403)
request_data = request.get_json()
if request_data["action"] == "add":
result = qlocation.add_location(activity_id,
request_data["location_id"])
elif request_data["action"] == "delete":
result = qlocation.delete_location(activity_id,
request_data["location_id"])
return str(result)
elif request.method == "GET":
if not quser.check_permissions("view"):
return abort(403)
locations = list(
map(lambda x: x.as_dict(),
qactivity.get_activity(activity_id).locations))
return jsonify(locations=locations)
def get_activity_summary(activity_id):
activity = qactivity.get_activity(activity_id)
f = dict([(field, getattr(activity, field)) for field in fields])
flen = dict([(field, "{} {}".format(len(getattr(activity, field)),
field)) for field in fields_len])
f.update(flen)
f['implementing_organisations'] = "; ".join([
organisation.name
for organisation in activity.implementing_organisations
])
f['funding_organisations'] = "; ".join([
organisation.name
for organisation in activity.funding_organisations
])
f['locations'] = "; ".join(
[location.locations.name for location in activity.locations])
#f['classifications'] = dict(filter(lambda clsf: clsf[0]!='mtef-sector', activity.classification_data_dict.items()))
return f
def api_activity_milestones(activity_id):
if request.method == "POST":
request_data = request.get_json()
milestone_id = request_data["milestone_id"]
attribute = request_data["attr"]
value = request_data["value"]
update_status = qmilestones.add_or_update_activity_milestone({
"activity_id":
activity_id,
"milestone_id":
milestone_id,
"attribute":
attribute,
"value":
value
})
if update_status == True:
return "success"
return "error"
else:
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
return jsonify(milestones=activity.milestones_data)
def api_activities_finances_fund_sources_by_id(activity_id):
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
commitments = activity.FY_commitments_dict_fund_sources
allotments = activity.FY_allotments_dict_fund_sources
disbursements = activity.FY_disbursements_dict_fund_sources
forwardspends = activity.FY_forward_spend_dict_fund_sources
finances = list()
if commitments:
finances.append(('commitments', {
"title": {
'external': 'Commitments',
'domestic': 'Appropriations'
}[activity.domestic_external],
"data": commitments
}))
if allotments:
finances.append(('allotment', {
"title": 'Allotments',
"data": allotments
}))
if disbursements:
finances.append(('disbursement', {
"title": 'Disbursements',
"data": disbursements
}))
if forwardspends:
finances.append(('forwardspend', {
"title": 'MTEF Projections',
"data": forwardspends
}))
return jsonify(finances=OrderedDict(finances),
fund_sources=activity.disb_fund_sources)
def check_activity_permissions(permission_name, activity_id):
def edit_rights(activity, permissions_search):
edit_permissions = permissions_search.get("edit", "none")
if edit_permissions == "both":
return True
if edit_permissions == activity.domestic_external:
return True
return False
if "admin" in current_user.roles_list:
return True
act = qactivity.get_activity(activity_id)
# If the activity is in draft, don't show it to unauthenticated users
if (act.published == False) and (current_user.is_authenticated == False):
return False
if permission_name in ('edit', 'results-data-entry', 'results-data-design'):
if edit_rights(act, current_user.permissions_dict):
return True
# For now, we allow all users with results design / data entry roles
# to add results data for all projects
if permission_name in current_user.roles_list:
return True
if act and current_user.permissions_dict.get("organisations"):
if (act.reporting_org_id in current_user.permissions_dict["organisations"]):
# If the user is attached to an organisation, then they should always
# at least have view rights.
if permission_name == "view":
return True
if permission_name == "edit":
if edit_rights(act, current_user.permissions_dict["organisations"][act.reporting_org_id]):
return True
if permission_name in current_user.permissions_list["organisations"][act.reporting_org_id]:
return True
elif permission_name in ("results-data-entry", "results-data-design"):
if "edit" in current_user.permissions_list["organisations"][act.reporting_org_id]:
return True
return False
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."""
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
if request.method == "POST":
request_data = request.get_json()
if request_data["action"] == "add":
print(request_data)
data = {
"transaction_type":
request_data["transaction_type"],
"transaction_date":
request_data["transaction_date"],
"transaction_value_original":
request_data["transaction_value_original"],
"aid_type":
request_data.get("aid_type", activity.aid_type),
"finance_type":
request_data.get("finance_type", activity.finance_type),
"provider_org_id":
request_data.get("provider_org_id",
activity.funding_organisations[0].id),
"receiver_org_id":
request_data.get("receiver_org_id",
activity.implementing_organisations[0].id),
"fund_source_id":
request_data.get("fund_source_id", None),
"currency":
request_data.get("currency", "USD"),
"classifications": {
"mtef-sector":
request_data.get(
"mtef_sector",
activity.classification_data['mtef-sector']['entries']
[0].codelist_code_id)
}
}
result = qfinances.add_finances(activity_id, data).as_simple_dict()
elif request_data["action"] == "delete":
result = qfinances.delete_finances(
activity_id,
request.get_json()["transaction_id"])
if result:
return jsonify(result)
else:
return abort(500)
elif request.method == "GET":
finances = {
'commitments':
sorted([
transaction.as_dict() for transaction in activity.commitments
],
key=lambda transaction: transaction['transaction_date']),
'allotments':
sorted(
[transaction.as_dict() for transaction in activity.allotments],
key=lambda transaction: transaction['transaction_date']),
'disbursements':
sorted([
transaction.as_dict() for transaction in activity.disbursements
],
key=lambda transaction: transaction['transaction_date'])
}
fund_sources = [{
"id": fundsource.id,
"name": fundsource.name
} for fundsource in models.FundSource.query.all()]
return jsonify(finances=finances, fund_sources=fund_sources)
def api_activity_counterpart_funding(activity_id):
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
# 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":
request_data = request.get_json()
if request_data["action"] == "add":
required_date = util.fq_fy_to_date(1,
int(request_data["required_fy"])).date().isoformat()
data = {
"required_value": request_data["required_value"],
"required_date": required_date,
"budgeted": False,
"allotted": False,
"disbursed": False,
}
result = qcounterpart_funding.add_entry(activity_id, data)
counterpart_fund = result.as_dict()
counterpart_fund["required_fy"], _ = util.date_to_fy_fq(
counterpart_fund["required_date"])
return jsonify(counterpart_funding=counterpart_fund)
elif request_data["action"] == "delete":
if 'year' in request_data:
result = qcounterpart_funding.delete_year(
activity_id, request_data["year"])
elif 'type' in request_data:
result = qcounterpart_funding.delete_type(
activity_id, request_data["type"])
if result:
return jsonify(result=True)
return abort(500)
elif request_data["action"] == "update":
year = request_data['year']
value = request_data['value']
data = {
'activity_id': activity_id,
'year': year,
'value': value,
'type': request_data['type'],
}
update_status = qcounterpart_funding.update_entry(data)
if update_status:
return jsonify(result=True)
return abort(500)
return str(result)
elif request.method == "GET":
def to_fy(counterpart_funding):
counterpart_fund = counterpart_funding.as_dict()
if counterpart_funding.required_date == None:
counterpart_fund["year"] = 'total'
else:
counterpart_fund["year"] = counterpart_funding.fiscal_period.fiscal_year.name
if counterpart_funding.required_funding_type == None:
counterpart_fund["type"] = 'total'
else:
counterpart_fund["type"] = counterpart_funding.required_funding_type
return counterpart_fund
counterpart_funding = [to_fy(counterpart_fund) for counterpart_fund in
qactivity.get_activity(activity_id).counterpart_funding]
fys = [str(fy) for fy in util.available_fys(10)]
return jsonify(counterpart_funding=counterpart_funding,
fiscal_years=fys)
def api_activities_by_id(activity_id):
activity = qactivity.get_activity(activity_id)
if activity is None:
return abort(404)
return jsonify(activity=activity.as_jsonable_dict())
def make_doc(activity_id):
project_brief_file = BytesIO()
codelists = get_codelists_lookups()
activity = qactivity.get_activity(activity_id)
document = docx.Document("projectdashboard/lib/docx/template.docx")
document.add_heading(activity.title, 1)
document.add_heading("General Information", 2)
descriptive_data = [
('Project Title', activity.title),
('LPD code', str(activity.id)),
('Donor Project Code', activity.code or ""),
('Project Description', activity.description or ""),
('Project Objectives', activity.objectives or ""),
('Expected Deliverables', activity.deliverables or ""),
('Alignment to PAPD', activity.papd_alignment or ""),
]
docx.rows_to_table(descriptive_data, document)
document.add_heading("Basic data", 2)
basic_data = [
('Start date', activity.start_date.isoformat()),
('End date', activity.end_date.isoformat()),
('Last updated', activity.updated_date.date().isoformat())
]
document = docx.rows_to_table(basic_data, document)
document.add_heading("Sectors", 2)
sectors_data = list(map(lambda sector: (sector["name"], ", ".join(list(map(
lambda entry: entry["codelist_code"]["name"], sector["entries"])))), activity.classification_data_dict.values()))
document = docx.rows_to_table(sectors_data, document)
document.add_heading("Key Financing Information", 2)
financing_info = [
('Project value/cost',
"USD {:,.2f}".format(activity.total_commitments or 0.00)),
('Finance type', ", ".join(list(map(lambda ft: "{}: {}%".format(
ft[0], ft[1]), activity.disb_finance_types.items())))),
('Aid type', codelists["AidType"][activity.aid_type]),
('Commitment Charge', ''),
('Service Charge', ''),
('Interest', ''),
('Maturity', ''),
('Grace Period', ''),
('Financial Contributors', ", ".join(
list(map(lambda org: org.name, activity.funding_organisations)))),
]
document = docx.rows_to_table(financing_info, document)
document.add_heading("Counterpart Funding", 3)
document.add_paragraph(
'Includes the RAP cost, bank service charge etc. - all types of GoL contribution specified in the project agreement.')
# FIXME use data from database once agreement on
# data structure reached.
counterpart_funding = [
('RAP Cost', '', '', '', '', '', ''),
('Bank Charge', '', '', '', '', '', ''),
('Reimbursable', '', '', '', '', '', ''),
('', '', '', '', '', '', ''),
('', '', '', '', '', '', ''),
('Total GoL Contribution', '', '', '', '', '', '')
]
document = docx.rows_to_table(counterpart_funding, document,
['Item', 'Total Amount', 'Amount disbursed to date',
'July 1 2020 - June 30 2021',
'July 1 2021 - December 31 2021',
'January 1 2022 - December 31 2022',
'Note / Comment']
)
document.add_heading("Effectiveness Conditions", 3)
effectiveness_conditions = [
('', '', ''),
('', '', '')
]
document = docx.rows_to_table(effectiveness_conditions, document, [
'Condition', 'Status', 'Note / Comment'])
document.add_heading("MTEF Projections", 2)
# FIXME don't hardcode this - use n+2 once
# new fiscal years data model established.
forwardspends = activity.FY_forward_spend_dict
def sum_if_exists(list_of_quarters):
_sum = 0.0
for quarter in list_of_quarters:
_sum += quarter.get('value')
return _sum
fy2021 = sum_if_exists((
forwardspends.get('2020 Q1 (MTEF)', {'value': 0}),
forwardspends.get('2020 Q2 (MTEF)', {'value': 0}),
forwardspends.get('2020 Q3 (MTEF)', {'value': 0}),
forwardspends.get('2020 Q4 (MTEF)', {'value': 0})
))
fy21 = sum_if_exists((
forwardspends.get('2021 Q1 (MTEF)', {'value': 0}),
forwardspends.get('2021 Q2 (MTEF)', {'value': 0})
))
fy22 = sum_if_exists((
forwardspends.get('2021 Q3 (MTEF)', {'value': 0}),
forwardspends.get('2021 Q4 (MTEF)', {'value': 0}),
forwardspends.get('2022 Q1 (MTEF)', {'value': 0}),
forwardspends.get('2022 Q1 (MTEF)', {'value': 0})
))
mtef_projections = [
('FY2021 (July 1 2020 to June 30 2021)',
"USD {:,.2f}".format(fy2021), ''),
('FY21.5 (July 1 2021 to December 31 2021)',
"USD {:,.2f}".format(fy21), ''),
('FY22 (January 1 2022 to December 31 2022)',
"USD {:,.2f}".format(fy22), '')
]
document = docx.rows_to_table(mtef_projections, document, [
'Fiscal Year(s)', 'Amount', 'Note / Comment'])
document.add_heading("Project Implementation Information", 2)
project_implementation = [
('Project status', codelists["ActivityStatus"]
[activity.activity_status]),
('Project Disbursement', "USD {:,.2f}".format(
activity.total_disbursements or 0.00)),
('Financial Management', ''),
('Implemented by', ", ".join(
list(map(lambda org: org.name, activity.implementing_organisations)))),
('Implementation Issues / Challenges', ''),
]
document = docx.rows_to_table(project_implementation, document)
document.add_heading("Results achieved", 3)
results = [
('', '', ''),
('', '', '')
]
document = docx.rows_to_table(
results, document, ['Result', 'Status', 'Note/Comment'])
document.add_heading("Beneficiaries", 3)
beneficiaries = [
('Direct project beneficiaries', ''),
('Location(s)', ", ".join(
list(map(lambda l: l.locations.name, activity.locations)))),
]
document = docx.rows_to_table(beneficiaries, document)
document.add_heading("Administrative Details", 2)
administrative_details = [
('Contacts', ''),
('Supporting documents links/names', ''),
]
document = docx.rows_to_table(administrative_details, document)
document.save(project_brief_file)
return project_brief_file
def import_xls_new(input_file, _type, disbursement_cols=[]):
num_updated_activities = 0
messages = []
activity_id = None
file_contents = BytesIO(input_file.read())
xl_workbook = openpyxl.load_workbook(file_contents)
num_sheets = len(xl_workbook.sheetnames)
cl_lookups = get_codelists_lookups()
cl_lookups_by_name = get_codelists_lookups_by_name()
def filter_mtef(column):
pattern = r"(\S*) \(MTEF\)$"
return re.match(pattern, column)
def filter_counterpart(column):
pattern = r"(\S*) \(GoL counterpart fund request\)$"
return re.match(pattern, column)
if "Instructions" in xl_workbook.sheetnames:
currency = xl_workbook["Instructions"].cell(6, 3).value
begin_sheet = 1
else:
currency = "USD"
begin_sheet = 0
try:
for sheet_id in range(begin_sheet, num_sheets):
input_file.seek(0)
data = xlsx_to_csv.getDataFromFile(
input_file.filename, input_file.read(), sheet_id, True)
if _type == 'mtef':
mtef_cols = list(filter(filter_mtef, data[0].keys()))
counterpart_funding_cols = list(
filter(filter_counterpart, data[0].keys()))
if len(mtef_cols) == 0:
raise Exception("No columns containing MTEF projections data \
were found in the uploaded spreadsheet!")
elif _type == 'disbursements':
for _column_name in disbursement_cols:
if _column_name not in data[0].keys():
raise Exception("The column {} containing financial data was not \
found in the uploaded spreadsheet!".format(_column_name))
for row in data: # each row is one ID
try:
activity_id = int(row["ID"])
except TypeError:
messages.append("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['ID'], row['Activity Title']))
continue
activity = qactivity.get_activity(activity_id)
activity_iati_preferences = [
pref.field for pref in activity.iati_preferences]
if not activity:
messages.append("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['ID'], row['Activity Title']))
continue
existing_activity = activity_to_json(activity, cl_lookups)
if _type == 'mtef':
# FIXME quick fix for now
if 'forwardspend' in activity_iati_preferences:
continue
updated = {
'activity': update_activity_data(activity, existing_activity,
row, cl_lookups_by_name),
# Parse MTEF projections columns
'mtef_years': parse_mtef_cols(currency, mtef_cols, existing_activity,
row, activity_id),
# Parse counterpart funding columns
'counterpart_years': parse_counterpart_cols(counterpart_funding_cols,
activity, row, activity_id),
}
elif _type == 'disbursements':
# FIXME quick fix for now
if 'disbursement' in activity_iati_preferences:
continue
updated = {
'activity': update_activity_data(activity, existing_activity,
row, cl_lookups_by_name),
'disbursements': parse_disbursement_cols(currency, disbursement_cols,
activity, existing_activity, row)
}
# Mark activity as updated and inform user
update_message, num_updated_activities = make_updated_info(
updated, activity, num_updated_activities)
if update_message is not None:
messages.append(update_message)
except Exception as e:
if activity_id is not None:
messages.append("""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))
else:
messages.append("""There was an error while importing your projects,
the error was: {}""".format(e))
db.session.commit()
return messages, num_updated_activities
