funding.py

import csv
from collections import defaultdict

from flask import Blueprint, jsonify, current_app
from compendium_v2 import db
from compendium_v2.background_task import xlsx_to_csv_sheet_parsing_task
from compendium_v2.routes import common
from compendium_v2.db import model
import logging
from typing import Any

routes = Blueprint('funding', __name__)


@routes.before_request
def before_request():
    config = current_app.config['CONFIG_PARAMS']
    dsn_prn = config['SQLALCHEMY_DATABASE_URI']
    db.init_db_model(dsn_prn)


logger = logging.getLogger(__name__)

FUNDING_RESPONSE_SCHEMA = {
    '$schema': 'http://json-schema.org/draft-07/schema#',

    'definitions': {
        'funding': {
            'type': 'object',
            'properties': {
                'id': {'type': 'number'},
                'NREN': {'type': 'string'},
                'YEAR': {'type': 'string'},
                'CLIENT_INSTITUTIONS': {'type': 'string'},
                'EUROPEAN_FUNDING': {'type': 'string'},
                'GOV_PUBLIC_BODIES': {'type': 'string'},
                'COMMERCIAL': {'type': 'string'},
                'OTHER': {'type': 'string'}
            },
            'required': ['id'],
            'additionalProperties': False
        }
    },

    'type': 'array',
    'items': {'$ref': '#/definitions/funding'}
}


@routes.route('/', methods=['GET'])
@common.require_accepts_json
def budget_view() -> Any:
    """
    handler for /api/funding/ requests

    response will be formatted as:

    .. asjson::
        compendium_v2.routes.data_entry.BUDGET_RESPONSE_SCHEMA

    :return:
    """

    def _extract_data(entry: model.FundingSource):
        return {
            'id': entry.id,
            'NREN': entry.nren,
            'YEAR': entry.year,
            'CLIENT_INSTITUTIONS': entry.client_institutions,
            'EUROPEAN_FUNDING': entry.european_funding,
            'GOV_PUBLIC_BODIES': entry.gov_public_bodies,
            'COMMERCIAL': entry.commercial,
            'OTHER': entry.other
        }

    with db.session_scope() as session:
        entries = sorted([_extract_data(entry)
                          for entry in session.query(model.FundingSource)],
                         key=lambda d: (d['NREN'], d['YEAR']))
    dict_obj = {"data": entries}
    return jsonify(dict_obj)


@routes.route('/migration', methods=['GET'])
@common.require_accepts_json
def db_funding_migration():
    with  db.session_scope() as session:

        # Import the data to database
        xlsx_to_csv_sheet_parsing_task.parse_income_source_xlsx_file()
        with open('compendium_v2/background_task/csv/FundingSourceCsvFile.csv',
                  newline='') as csvfile:
            reader = csv.reader(csvfile)

            for row in reader:
                if row is not None:
                    entry = session.query(
                        model.FundingSource).filter_by(nren=row[0],
                                                       year=row[1])
                    dup_entry: model.FundingSource = entry.first()
                    if dup_entry:
                        entry.update({"client_institutions": row[2],
                                      "european_funding": row[3],
                                      "gov_public_bodies": row[4],
                                      "commercial": row[5],
                                      "other": row[6]})

                    else:
                        print("add new")
                        print(row)
                        budget_entry = model.FundingSource(
                            nren=row[0], year=row[1],
                            client_institutions=row[2],
                            european_funding=row[3],
                            gov_public_bodies=row[4],
                            commercial=row[5],
                            other=row[6])
                        session.add(budget_entry)
        session.commit()

    return "Success"