def import_file():
data = read_file()
print("There are {} projects found".format(len(data)))
# Get codelists for lookup
CODELISTS_BY_NAME = codelists.get_codelists_lookups_by_name()
CODELISTS_IDS_BY_NAME = codelists.get_codelists_ids_by_name()
CODELISTS_IDS = codelists.get_codelists_lookups()
CODELIST_IDS_BY_CODE = codelists.get_codelists_lookups_by_code()
for activity in data:
start_date = util.fq_fy_to_date(1, int(activity["earliest_year"][0:4]),
"start")
end_date = util.fq_fy_to_date(4, int(activity["latest_year"][0:4]),
"end")
d = {
"user_id":
1, # FIXME
"domestic_external":
"domestic",
"code":
activity["code"],
"title":
clean_string(activity["name"]),
"description":
"",
# "description": nonempty_from_list([
# activity["Project Description"].decode("utf-8"),
# activity["Objective"].decode("utf-8"),
# ]),
"start_date":
start_date.isoformat(),
"end_date":
end_date.isoformat(),
"reporting_org_id":
qorganisations.get_or_create_organisation("Government of Liberia"),
"organisations": [
make_organisation(4, org[1], org[0])
for org in activity["organisations"]
] + [(make_organisation(1, "Government of Liberia"))],
"recipient_country_code":
"LR",
"classifications":
process_classifications(activity, CODELIST_IDS_BY_CODE),
# "collaboration_type": CODES["collaboration_type"][
# activity["Donor Type"].strip()
# ],
"finance_type":
CODES["finance_type"]["Grant"],
"aid_type":
CODES["aid_type"][""],
"activity_status":
activity["activity_status"],
# "tied_status": "5", # Assume everything is untied
# "flow_type": "10", # Assume everything is ODA
"finances":
process_transactions(activity, CODELIST_IDS_BY_CODE),
}
qactivity.create_activity(d)
def generate_xlsx_export_template(data, mtef=False, currency="USD", _headers=None):
mtef_cols, counterpart_funding_cols, disb_cols, _headers = helpers.get_column_information(
mtef, _headers)
for required_field in ["ID", "Activity Status", 'Activity Dates (Start Date)', 'Activity Dates (End Date)']:
if required_field not in _headers:
flash("Error: the field `{}` is required in this export. Please adjust your selected fields and try again!".format(
required_field))
return False
writer = xlsx_writer.xlsxDictWriter(_headers,
_type={True: "mtef",
False: "disbursements"}[mtef],
template_currency=currency,
instructions_sheet=True)
cl_lookups = get_codelists_lookups()
for org_code, activities in sorted(data.items()):
writer.writesheet(org_code)
writer.ws.add_data_validation(v_status)
writer.ws.add_data_validation(v_date)
writer.ws.add_data_validation(v_number)
writer.ws.add_data_validation(v_id)
#writer.ws.protection.sheet = True
for activity in activities:
existing_activity = activity_to_json(activity, cl_lookups)
for mtef_year in mtef_cols:
fy_start = re.match(r"FY(\S*) \(MTEF\)", mtef_year).groups()[0]
# Every FY has at least one quarter, but may not have more than one quarter.
existing_activity[mtef_year] = sum([float(existing_activity["FY{} Q1 (MTEF)".format(fy_start)]),
float(existing_activity.get(
"FY{} Q2 (MTEF)".format(fy_start), 0)),
float(existing_activity.get(
"FY{} Q3 (MTEF)".format(fy_start), 0)),
float(existing_activity.get("FY{} Q4 (MTEF)".format(fy_start), 0))])
if writer.template_currency != "USD":
# N.B.: we convert at (close to) today's date,
# because these are *projections* and we store in USD
existing_activity[mtef_year] = qexchangerates.convert_to_currency(
currency=writer.template_currency,
_date=datetime.datetime.utcnow().date(),
value=existing_activity[mtef_year])
# Convert disbursement data to writer.template_currency
for disb_year in disb_cols:
if writer.template_currency != "USD":
existing_activity[disb_year] = qexchangerates.convert_to_currency(
currency=writer.template_currency,
_date=util.get_real_date_from_header(disb_year, "end"),
value=existing_activity[disb_year])
# Leave in USD always
for counterpart_year in counterpart_funding_cols:
cfy = re.match(
r"FY(\S*) \(GoL counterpart fund request\)", counterpart_year).groups()[0]
existing_activity[counterpart_year] = activity.FY_counterpart_funding_for_FY(
"FY{}".format(cfy))
writer.writerow(existing_activity)
# Formatting
apply_formatting.formatting_validation(writer, len(
activities), _headers, counterpart_funding_cols, mtef_cols, disb_cols)
writer.delete_first_sheet()
return writer.save()
def generate_xlsx_filtered(arguments={}):
disbFYs = generate_disb_fys()
_headers = headers + disbFYs
writer = xlsx_writer.xlsxDictWriter(_headers)
writer.writesheet("Data")
writer.writeheader()
cl_lookups = get_codelists_lookups()
activities = qactivity.list_activities_by_filters(
arguments)
for activity in activities:
total_commitments = activity.total_commitments
total_disbursements = activity.total_disbursements
for fundsource in activity.fund_sources:
fund_source_code = fundsource.code
fund_source_name = fundsource.name
fund_source_commitments = sum(map(
lambda item: item['value'], activity.FY_commitments_dict_fund_sources[fundsource.id].values()))
fund_source_disbursements = sum(map(
lambda item: item['value'], activity.FY_disbursements_dict_fund_sources[fundsource.id].values()))
# MTEF projections don't have fund sources. So we pro-rate their value out, ideally
# by commitments, alternatively by disbursements.
# This is a bit of an ugly hack and this whole function needs to be rewritten
# in the near future.
if (total_commitments is not None) and (total_commitments > 0):
pct = fund_source_commitments/total_commitments
elif (total_disbursements is not None) and (total_disbursements > 0):
pct = fund_source_disbursements/total_disbursements
else:
pct = 1
activity_data = activity_to_json(activity, cl_lookups)
activity_data.update(
dict(map(lambda d: (d, 0.00), list(generate_disb_fys()))))
activity_data["Fund Source"] = "{} - {}".format(fund_source_code,
fund_source_name) if fund_source_name != fund_source_code else fund_source_name
# Add Disbursements data
if fundsource.id is not None and fundsource.finance_type:
activity_data['Finance Type (Type of Assistance)'] = {'110': 'Grant', '410': 'Loan'}[fundsource.finance_type]
activity_data['Total Commitments'] = activity_data['Total Commitments'] * pct
activity_data['Total Disbursements'] = activity_data['Total Disbursements'] * pct
if fundsource.id in activity.FY_commitments_dict_fund_sources:
activity_data.update(dict(map(lambda d: (
d[0], d[1]["value"]), activity.FY_commitments_dict_fund_sources[fundsource.id].items())))
if fundsource.id in activity.FY_disbursements_dict_fund_sources:
activity_data.update(dict(map(lambda d: (
d[0], d[1]["value"]), activity.FY_disbursements_dict_fund_sources[fundsource.id].items())))
activity_data.update(dict(map(lambda d: (
d[0], d[1]["value"]*pct), activity.FY_forward_spend_dict_fund_sources[None].items())))
writer.writerow(activity_data)
writer.delete_first_sheet()
return writer.save()
def generate_csv():
csv_file = io.StringIO()
cl_lookups = get_codelists_lookups()
disb_fys = generate_disb_fys()
_headers = headers + disb_fys
csv = unicodecsv.DictWriter(csv_file, _headers)
csv.writeheader()
activities = qactivity.list_activities()
for activity in activities:
activity_data = activity_to_json(activity, cl_lookups)
remove_keys = set(activity_data)-set(_headers)
for remove_key in remove_keys:
del activity_data[remove_key]
csv.writerow(activity_data)
return csv_file
def generate_xlsx_transactions(filter_key=None, filter_value=None):
disbFYs = generate_disb_fys()
writer = xlsx_writer.xlsxDictWriter(headers_transactions)
writer.writesheet("Data")
writer.writeheader()
cl_lookups = get_codelists_lookups()
if (filter_key and filter_value):
activities = qactivity.list_activities_by_filters(
{filter_key: filter_value})
else:
activities = qactivity.list_activities()
for activity in activities:
for tr in activity_to_transactions_list(activity, cl_lookups):
writer.writerow(tr)
writer.delete_first_sheet()
return writer.save()
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 build_activity_103(doc, activity):
db_activity = activity
cl_lookups = get_codelists_lookups()
ia = et.Element("iati-activity")
doc.append(ia)
ia.set("last-updated-datetime", activity.updated_date.isoformat())
ia.set("default-currency",
current_app.config["ORGANISATION"]["default_currency"])
ia.set("{http://www.w3.org/XML/1998/namespace}lang",
current_app.config["ORGANISATION"]["default_language"])
o_name = current_app.config["ORGANISATION"]["organisation_name"]
o_ref = current_app.config["ORGANISATION"]["organisation_ref"]
o_type = current_app.config["ORGANISATION"]["organisation_type"]
# IATI Identifier
ia.append(el_iati_identifier(activity, o_ref))
# Reporting org
ia.append(el_org_103("reporting", o_name, o_ref, o_type))
# Title, Description
ia.append(el_with_text("title", activity.title))
ia.append(el_with_text("description", activity.description))
# Participating orgs
for organisation in activity.funding_organisations:
ia.append(
el_org_103("Funding", organisation.name, organisation.code, "10"))
for organisation in activity.implementing_organisations:
ia.append(
el_org_103("Implementing", organisation.name, organisation.code,
"10"))
ia.append(
el_with_code_103(
"activity-status", activity.activity_status,
cl_lookups["ActivityStatus"][activity.activity_status]))
# Activity dates
if activity.start_date:
ia.append(el_date_103("start", activity.start_date.isoformat()))
if activity.end_date:
ia.append(el_date_103("end", activity.end_date.isoformat()))
# Contact info
# ia.append(el_contact_info(current_app.config["ORGANISATION"]))
# Geography
ia.append(
el_with_code_103(
"recipient-country",
activity.recipient_country_code,
cl_lookups["Country"][activity.recipient_country_code],
))
for location in activity.locations:
ia.append(el_location_103(location))
# Classifications
ia.append(
el_with_code_103("sector", activity.dac_sector or "",
cl_lookups["Sector"].get(activity.dac_sector,
""), "DAC"))
ia.append(
el_with_code_103(
"collaboration-type", activity.collaboration_type,
cl_lookups["CollaborationType"].get(activity.collaboration_type)))
ia.append(
el_with_code_103("default-finance-type", activity.finance_type,
cl_lookups["FinanceType"].get(activity.finance_type)))
ia.append(
el_with_code_103("default-flow-type", activity.flow_type,
cl_lookups["FlowType"].get(activity.flow_type)))
ia.append(
el_with_code_103("default-aid-type", activity.aid_type,
cl_lookups["AidType"].get(activity.aid_type)))
ia.append(
el_with_code_103("default-tied-status", activity.tied_status,
cl_lookups["TiedStatus"].get(activity.tied_status)))
# Transactions
activity_commitments = list(filter(valid_transaction,
activity.commitments))
activity_disbursements = list(
filter(valid_transaction, activity.disbursements))
# Output commitments
for transaction in activity_commitments:
ia.append(build_transaction_103(transaction.as_dict()))
if ((len(activity_commitments) == 0) and activity.start_date
and activity.total_commitments):
transaction = {
"id": "%s-C" % activity.id,
"transaction_date": activity.start_date,
"transaction_value": activity.total_commitments,
"transaction_description": "Total commitments",
"transaction_type": "C"
}
ia.append(build_transaction_103(transaction))
# Output disbursements
for transaction in activity_disbursements:
ia.append(build_transaction_103(transaction.as_dict()))
if ((len(activity_disbursements) == 0) and activity.total_disbursements):
transaction = {
"id": "%s-D" % activity.id,
"transaction_date": datetime.datetime.utcnow().date(),
"transaction_value": activity.total_disbursements,
"transaction_description": "Total disbursements",
"transaction_type": "D"
}
ia.append(build_transaction_103(transaction))
return doc
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