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")