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