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 process_forward_spends(activity, start_date, end_date):
forwardspends = []
"""Take earliest of start_date or first forward spend date.
From then until latest of end_date or last forward spend date, add
either a value or 0.
Return list of forward spends, start_date and end_date."""
mtef_cols = [
'MTEF 2013/2014', 'MTEF 2014/2015', 'MTEF 2015/2016', 'MTEF 2016/2017',
'MTEF 2017/2018', 'MTEF 2018/2019', 'MTEF 2019/2020'
]
def get_activity_mtefs(activity):
activity_mtefs = {}
for col in mtef_cols:
if activity[col].strip() in ("", "-", "0"):
continue
mtef_year = int(re.match(r"MTEF (\d{4})/\d{4}", col).groups()[0])
activity_mtefs[mtef_year] = tidy_amount(activity[col])[0]
return activity_mtefs
activity_mtefs = get_activity_mtefs(activity)
# If there is no MTEF data, set everything to 0
if len(activity_mtefs) == 0:
forwardspends = (qfinances.create_forward_spends(start_date, end_date))
return start_date, end_date, forwardspends
first_mtef = min(activity_mtefs)
last_mtef = max(activity_mtefs)
start_date_fy, start_date_fq = util.date_to_fy_fq(start_date)
end_date_fy, end_date_fq = util.date_to_fy_fq(end_date)
if start_date_fy < first_mtef:
forwardspends += (qfinances.create_forward_spends(
start_date,
util.fq_fy_to_date(1, first_mtef, 'start') - dt.timedelta(days=1)))
for mtef_year, mtef_value in activity_mtefs.items():
forwardspends += (qfinances.create_forward_spends(
util.fq_fy_to_date(1, mtef_year, 'start'),
util.fq_fy_to_date(4, mtef_year, 'end'), mtef_value))
# create 0-value quarters up to first mtef
if end_date_fy > last_mtef:
forwardspends += (qfinances.create_forward_spends(
dt.datetime(last_mtef + 1, 1, 1), end_date))
# FIXME decide whether to do this…
# Make adjustments to start/end dates if there are MTEFs found
# outside of these dates
# if first_mtef < start_date.fy:
# first = util.fq_fy_to_date(1, first_mtef, "start")
# if last_mtef > end_date_fy:
# end_date = util.fq_fy_to_date(4, last_mtef, "end")
return start_date, end_date, forwardspends
def import_transactions(data, fiscal_year):
if fiscal_year:
fy_start_date = util.fq_fy_to_date(
1, util.fy_fy_to_fy(fiscal_year), "start").date()
fy_end_date = util.fq_fy_to_date(
4, util.fy_fy_to_fy(fiscal_year), "end").date()
ifmis_fiscal_year = util.fy_fy_to_fyfy_ifmis(fiscal_year)
else:
fy_start_date, fy_end_date, ifmis_fiscal_year = None, None, None
fund_sources = models.FundSource.query.all()
fund_source_codes = dict(map(lambda fs: (fs.code, fs), fund_sources))
grouped_by_code = defaultdict(list)
relevant_activities = list(map(lambda a: (a.code, a), models.Activity.query.filter(
models.Activity.code == u"44000-P125574").all()))
for project_code, activity in relevant_activities:
grouped_by_code[project_code].append(activity)
data_by_project = defaultdict(list)
for row in data:
row["project_key"] = "44000-{}".format(row["Project"].split(":")[0])
data_by_project[row["project_key"]].append(row)
updated_projects = 0
for project_code, project_data in data_by_project.items():
if project_code in grouped_by_code:
print("Importing project code {}".format(project_code))
if len(grouped_by_code[project_code]) > 1:
flash("Project {} was not imported, as more than one project is mapped to it.".format(
project_code), "danger")
else:
existing_activity = grouped_by_code[project_code][0]
existing_transactions = existing_activity.finances
fund_sources_cc = set(map(lambda f: f["Loan"], project_data))
if not set([fs in fund_source_codes.keys() for fs in fund_sources_cc]) == set([True]):
flash("Missing fund sources (required: {}), could not import project data.".format(
fund_sources_cc), "danger")
continue
for transaction in existing_transactions:
if not fiscal_year:
db.session.delete(transaction)
elif (fiscal_year and
(transaction.transaction_date >= fy_start_date) and
(transaction.transaction_date <= fy_end_date)):
db.session.delete(transaction)
db.session.commit()
make_transactions(existing_activity, project_data,
ifmis_fiscal_year, fund_source_codes)
updated_projects += 1
return updated_projects
def parse_disbursement_cols(currency, disbursement_cols, activity, existing_activity, row):
updated_disbursements = []
for column_name in disbursement_cols:
row_value = clean_value(row[column_name])
fq, fy = util.get_data_from_header(column_name)
column_date = util.fq_fy_to_date(fq, fy, "end")
existing_value = float(existing_activity.get(column_name, 0))
existing_value_same_currency = qexchangerates.convert_to_currency(
currency=currency,
_date=column_date,
value=existing_value)
difference = round(row_value - existing_value_same_currency, 4)
if (round(difference) == 0):
continue
activity.finances.append(
process_transaction(activity, difference, currency, column_name)
)
db.session.add(activity)
if existing_activity.get(column_name, 0) != 0:
# Non-zero financial values were previously provided
# and should be adjusted upwards/downwards
updated_disbursements.append("{}; previous value was {}; \
new value is {}; new entry for {} added".format(
util.column_data_to_string(column_name),
existing_value_same_currency,
row_value,
difference))
else:
# Financial values were not previously provided, and are now entered
updated_disbursements.append("{}".format(
util.column_data_to_string(column_name)))
return updated_disbursements
def process_transaction(activity, amount, currency, column_name):
provider = activity.funding_organisations[0].id
receiver = activity.implementing_organisations[0].id
fq, fy = util.get_data_from_header(column_name)
end_fq_date = util.fq_fy_to_date(fq, fy, "end")
disbursement = models.ActivityFinances()
disbursement.transaction_date = end_fq_date
disbursement.transaction_type = "D"
disbursement.transaction_description = "Disbursement for Q{} FY{}, imported from AMCU template".format(
fq, fy
)
disbursement.currency = currency
disbursement.currency_automatic = True
disbursement.currency_source, disbursement.currency_rate, disbursement.currency_value_date = qexchangerates.get_exchange_rate(
disbursement.transaction_date, disbursement.currency)
disbursement.transaction_value_original = amount
disbursement.provider_org_id = provider
disbursement.receiver_org_id = receiver
disbursement.finance_type = activity.finance_type
disbursement.aid_type = activity.aid_type
disbursement.classifications = process_transaction_classifications(
activity)
return disbursement
def update_entry(data):
if data['year'] == 'total':
required_date = None
else:
required_date = util.fq_fy_to_date(fq=1, fy=data['year'][2:])
cf = models.ActivityCounterpartFunding.query.filter_by(
required_date=required_date,
activity_id=data['activity_id'],
required_funding_type=data['type']).first()
if cf is None:
cf = models.ActivityCounterpartFunding()
cf.required_date = required_date
cf.activity_id = data['activity_id']
cf.required_funding_type = data['type']
old_value = cf.required_value
if data['value'] == "":
data['value'] = 0.0
cf.required_value = data['value']
db.session.add(cf)
db.session.commit()
qactivity.activity_updated(
cf.activity_id, {
"user_id": current_user.id,
"mode": "update",
"target": "ActivityCounterpartFunding",
"target_id": cf.id,
"old_value": {
data['year']: old_value
},
"value": {
data['year']: data['value']
}
})
return cf
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)