data_download.py

import logging
from typing import List, Optional, Dict, Any, Sequence
from flask import Blueprint
from itertools import product
from collections import defaultdict
from sqlalchemy.sql import select
from sqlalchemy.orm import selectinload

from compendium_v2.db import db
from compendium_v2.auth.session_management import admin_required
from compendium_v2.routes import common
from compendium_v2.db.presentation_models import NREN, AlienWave, BudgetEntry, Capacity, CentralProcurement, \
    CertificateProviders, \
    CommercialChargingLevel, CommercialConnectivity, \
    ConnectedProportion, ConnectionCarrier, ConnectivityGrowth, ConnectivityLevel, ConnectivityLoad, CrisisExercises, \
    DarkFibreInstalled, \
    DarkFibreLease, ECProject, EOSCListings, ExternalConnections, FibreLight, \
    FundingSource, ChargingStructure, InstitutionURLs, MonitoringTools, NRENService, NetworkAutomation, \
    NetworkFunctionVirtualisation, NetworkMapUrls, NonREPeers, NrenStaff, OpsAutomation, ParentOrganization, \
    PassiveMonitoring, PertTeam, RemoteCampuses, SecurityControls, ServiceManagement, ServiceUserTypes, SiemVendors, \
    Standards, SubOrganization, TrafficRatio, TrafficStatistics, \
    TrafficVolume, Policy, WeatherMap
from compendium_v2.db.presentation_model_enums import UserCategory
from compendium_v2.db.survey_models import SurveyResponse
from compendium_v2.helpers import extract_model_data


routes = Blueprint('data_download', __name__)
logger = logging.getLogger(__name__)


def _fetch(table_model, nrens):
    all_data = db.session.query(table_model).options(selectinload(table_model.nren)).all()
    by_nren = defaultdict(dict)
    for entry in all_data:
        by_nren[entry.nren_id][entry.year] = entry

    distinct_years = sorted(set(entry.year for entry in all_data), reverse=True)

    result = []
    for nren, year in product(nrens, distinct_years):
        entry = by_nren.get(nren.id, {}).get(year)
        if entry is None:
            result.append({
                **nren.to_dict(),
                'year': year,
            })
        else:
            data = list(extract_model_data(entry, download=True))
            for e in data:
                for key, val in list(e.items()):
                    if isinstance(val, list):
                        if val and isinstance(val[0], dict):
                            for i, v in enumerate(val):
                                for k, v in v.items():
                                    e[f'{key}_{i}_{k}'] = v
                        else:
                            e[key] = ', '.join(val)
            result.extend(data)

    return result


def _fetch_with_user_category(table_model, nrens):
    all_data = db.session.query(table_model).options(selectinload(table_model.nren)).all()
    by_nren = defaultdict(lambda: defaultdict(dict))
    for entry in all_data:
        by_nren[entry.nren_id][entry.year][entry.user_category] = entry

    distinct_years = sorted(set(entry.year for entry in all_data), reverse=True)
    result = []

    for nren, year, user_category in product(nrens, distinct_years, UserCategory):
        entry = by_nren.get(nren.id, {}).get(year, {}).get(user_category)

        if entry is None:
            result.append({
                **nren.to_dict(),
                'year': year,
                'user_category': user_category.value,
            })
        else:
            data = list(extract_model_data(entry, download=True))
            for e in data:
                for key, val in list(e.items()):
                    if isinstance(val, list):
                        e[key] = ', '.join(val)
            result.extend(data)

    return result


def fetch_survey_comments():

    survey_data = db.session.scalars(select(SurveyResponse)).unique().all()
    for response in survey_data:
        data = {
            # we don't control the order of the columns in the generated xlsx, so put A in front of the important ones
            'A NREN': response.nren.name,
            'A Year': response.survey_year,
        }
        for key, val in response.answers.get('data', {}).items():
            if key.endswith('_comments'):
                data[key] = val
        if len(data) > 2:  # only return responses with comments
            yield data


@routes.route('/', methods=['GET'])
@common.require_accepts_json
@admin_required
def fetch_and_combine_data() -> Sequence[Optional[Dict[str, Any]]]:
    result_set: List[Optional[Dict[str, Any]]] = []

    all_nrens = list(db.session.query(NREN).order_by(NREN.name.asc()).all())

    def fetch_data_from_table(table):
        return _fetch(table, all_nrens)

    def fetch_data_from_table_with_user_category(table):
        return _fetch_with_user_category(table, all_nrens)

    entries = fetch_data_from_table(AlienWave)
    result_set.append({'name': 'Alien Wave', 'data': entries})

    entries = fetch_data_from_table(BudgetEntry)
    metaInfo = {'columnName': 'budget', 'format': '€#,##0.00,,"M"'}
    result_set.append({'name': 'Budget', 'data': entries, 'meta': metaInfo})

    entries = fetch_data_from_table(Capacity)
    result_set.append({'name': 'Backbone Capacity', 'data': entries})

    entries = fetch_data_from_table(ChargingStructure)
    result_set.append({'name': 'Charging Structure', 'data': entries})

    entries = fetch_data_from_table(CentralProcurement)
    result_set.append({'name': 'Central Procurement', 'data': entries})

    entries = fetch_data_from_table(CertificateProviders)
    result_set.append({'name': 'Certificate Providers', 'data': entries})

    entries = fetch_data_from_table(CommercialChargingLevel)
    result_set.append({'name': 'Commercial Charging Level', 'data': entries})

    entries = fetch_data_from_table_with_user_category(ConnectedProportion)
    result_set.append({'name': 'Market Share', 'data': entries})

    entries = fetch_data_from_table_with_user_category(ConnectivityLevel)
    result_set.append({'name': 'Level of IP Connectivity ', 'data': entries})

    entries = fetch_data_from_table_with_user_category(ConnectionCarrier)
    result_set.append({'name': 'Carrying IP Traffic to Users', 'data': entries})

    entries = fetch_data_from_table_with_user_category(ConnectivityLoad)
    result_set.append({'name': 'Traffic Load', 'data': entries})

    entries = fetch_data_from_table_with_user_category(ConnectivityGrowth)
    result_set.append({'name': 'Est Traffic Growth 3 years', 'data': entries})

    entries = fetch_data_from_table(CommercialConnectivity)
    result_set.append({'name': 'Commercial Connectivity', 'data': entries})

    entries = fetch_data_from_table(CrisisExercises)
    result_set.append({'name': 'Crisis Exercises', 'data': entries})

    entries = fetch_data_from_table(DarkFibreLease)
    result_set.append({'name': 'Dark Fibre Lease', 'data': entries})

    entries = fetch_data_from_table(DarkFibreInstalled)
    result_set.append({'name': 'Dark Fibre Installed', 'data': entries})

    entries = fetch_data_from_table(ECProject)
    result_set.append({'name': 'EC Project', 'data': entries})

    entries = fetch_data_from_table(EOSCListings)
    result_set.append({'name': 'EOSC Listings', 'data': entries})

    entries = fetch_data_from_table(ExternalConnections)
    result_set.append({'name': 'External IP links', 'data': entries})

    entries = fetch_data_from_table(FibreLight)
    result_set.append({'name': 'Fibre Operation Model', 'data': entries})

    entries = fetch_data_from_table(FundingSource)
    result_set.append({'name': 'Funding Source', 'data': entries})

    entries = fetch_data_from_table(InstitutionURLs)
    result_set.append({'name': 'Institutions', 'data': entries})

    entries = fetch_data_from_table(MonitoringTools)
    result_set.append({'name': 'Monitoring Tools', 'data': entries})

    entries = fetch_data_from_table(NetworkAutomation)
    result_set.append({'name': 'Network Automation', 'data': entries})

    entries = fetch_data_from_table(NetworkFunctionVirtualisation)
    result_set.append({'name': 'Network Function Virtualisation', 'data': entries})

    entries = fetch_data_from_table(NetworkMapUrls)
    result_set.append({'name': 'Network Map Urls', 'data': entries})

    entries = fetch_data_from_table(NonREPeers)
    result_set.append({'name': 'Non R&E Peers', 'data': entries})

    entries = fetch_data_from_table(NRENService)
    result_set.append({'name': 'Services', 'data': entries})

    entries = fetch_data_from_table(NrenStaff)
    result_set.append({'name': 'Staff', 'data': entries})

    entries = fetch_data_from_table(OpsAutomation)
    result_set.append({'name': 'Ops Automation', 'data': entries})

    entries = fetch_data_from_table(ParentOrganization)
    result_set.append({'name': 'Parent Organization', 'data': entries})

    entries = fetch_data_from_table(PassiveMonitoring)
    result_set.append({'name': 'Passive Monitoring', 'data': entries})

    entries = fetch_data_from_table(PertTeam)
    result_set.append({'name': 'Pert Team', 'data': entries})

    entries = fetch_data_from_table(Policy)
    result_set.append({'name': 'Policies', 'data': entries})

    entries = fetch_data_from_table(SubOrganization)
    result_set.append({'name': 'Sub-Organization', 'data': entries})

    entries = fetch_data_from_table(RemoteCampuses)
    result_set.append({'name': 'Remote Campuses', 'data': entries})

    entries = fetch_data_from_table(TrafficVolume)
    result_set.append({'name': 'Traffic Volume', 'data': entries})

    entries = fetch_data_from_table(SecurityControls)
    result_set.append({'name': 'Security Controls', 'data': entries})

    entries = fetch_data_from_table(ServiceManagement)
    result_set.append({'name': 'Service Management', 'data': entries})

    entries = fetch_data_from_table(ServiceUserTypes)
    result_set.append({'name': 'Service User Types', 'data': entries})

    entries = fetch_data_from_table(SiemVendors)
    result_set.append({'name': 'Siem Vendors', 'data': entries})

    entries = fetch_data_from_table(Standards)
    result_set.append({'name': 'Standards', 'data': entries})

    entries = fetch_data_from_table(TrafficStatistics)
    result_set.append({'name': 'Traffic Statistics', 'data': entries})

    entries = fetch_data_from_table(TrafficRatio)
    result_set.append({'name': 'Traffic Ratio', 'data': entries})

    entries = fetch_data_from_table(WeatherMap)
    result_set.append({'name': 'Weather Map Url', 'data': entries})

    survey_comments = list(fetch_survey_comments())
    result_set.append({'name': 'Survey Comments', 'data': survey_comments})

    return result_set