Skip to content
Snippets Groups Projects

Feature/comp 284 excel publisher networks

Merged Remco Tukker requested to merge feature/COMP-284_excel_publisher_networks into develop
6 files
+ 438
6
Compare changes
  • Side-by-side
  • Inline
Files
6
@@ -3,7 +3,8 @@ import logging
import openpyxl
from compendium_v2.conversion import mapping
from compendium_v2.db.presentation_model_enums import CarryMechanism, ConnectivityCoverage, UserCategory, FeeType
from compendium_v2.db.presentation_model_enums import CarryMechanism, ConnectivityCoverage, MonitoringMethod, \
UserCategory, FeeType, YesNoPlanned
from compendium_v2.environment import setup_logging
from compendium_v2.resources import get_resource_file_path
@@ -827,3 +828,239 @@ def fetch_remote_campuses_excel_data():
yield from create_points_for_year(2020, 16)
yield from create_points_for_year(2021, 10)
yield from create_points_for_year(2022, 4)
def fetch_dark_fibre_iru_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "Dark Fibre"
ws = wb[sheet_name]
rows = list(ws.rows)
def parse_int(excel_value):
if excel_value is None or excel_value == "":
return None
return int(str(excel_value).replace("-", "").replace(" ", "").replace(".", ""))
def create_points_for_year(year, start_column):
for i in range(10, 53):
nren_name = rows[i][start_column].value
if not nren_name:
continue
nren_name = nren_name.upper()
s = start_column
iru = ""
if year > 2019:
s += 1
iru = rows[i][s].value
elif parse_int(rows[i][s + 1].value) is not None:
iru = "Yes" if parse_int(rows[i][s + 1].value) else "No"
if iru:
length_in_country = parse_int(rows[i][s + 1].value)
length_out_country = parse_int(rows[i][s + 3].value)
iru = iru == "Yes"
yield nren_name, year, iru, length_in_country, length_out_country
yield from create_points_for_year(2016, 42)
yield from create_points_for_year(2017, 36)
yield from create_points_for_year(2018, 30)
yield from create_points_for_year(2019, 24)
yield from create_points_for_year(2020, 17)
yield from create_points_for_year(2021, 10)
yield from create_points_for_year(2022, 3)
def fetch_dark_fibre_installed_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "Dark Fibre"
ws = wb[sheet_name]
rows = list(ws.rows)
def parse_int(excel_value):
if excel_value is None or excel_value == "":
return None
return int(str(excel_value).replace("-", "").replace(" ", "").replace(".", ""))
def create_points_for_year(year, start_column):
for i in range(10, 53):
nren_name = rows[i][start_column].value
if not nren_name:
continue
nren_name = nren_name.upper()
s = start_column
if year > 2019:
s += 1
installed_length = parse_int(rows[i][s + 2].value)
if installed_length is not None:
installed = bool(installed_length)
yield nren_name, year, installed, installed_length
yield from create_points_for_year(2016, 42)
yield from create_points_for_year(2017, 36)
yield from create_points_for_year(2018, 30)
yield from create_points_for_year(2019, 24)
yield from create_points_for_year(2020, 17)
yield from create_points_for_year(2021, 10)
yield from create_points_for_year(2022, 3)
def fetch_iru_duration_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "IRU duration"
ws = wb[sheet_name]
rows = list(ws.rows)
result = {}
def create_points_for_year(year, start_column):
for i in range(3, 46):
nren_name = rows[i][start_column].value
if not nren_name:
continue
nren_name = nren_name.upper()
years = rows[i][start_column + 1].value
if not years:
continue
years = str(years).split(" ")[0].split("+")[0].split("-")[0]
if not years:
continue
try:
years = int(years)
except ValueError:
logger.warning(f'Invalid iru duration Value :{nren_name} ({year}) with value ({years})')
continue
result[(nren_name, year)] = years
create_points_for_year(2019, 10)
create_points_for_year(2020, 7)
create_points_for_year(2021, 4)
create_points_for_year(2022, 1)
return result
def fetch_passive_monitoring_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "Traffic monitoring"
ws = wb[sheet_name]
rows = list(ws.rows)
def create_points_for_year(year, start_column):
for i in range(6, 48):
nren_name = rows[i][1].value
if not nren_name:
continue
nren_name = nren_name.upper()
monitoring = rows[i][start_column].value
method = rows[i][start_column + 1].value
if monitoring:
monitoring = monitoring == "Yes"
method = {
"SPAN ports": MonitoringMethod.span_ports,
"Passive optical TAPS": MonitoringMethod.taps,
"Both": MonitoringMethod.both,
None: None
}[method]
yield nren_name, year, monitoring, method
yield from create_points_for_year(2021, 4)
yield from create_points_for_year(2022, 2)
def fetch_largest_link_capacity_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "Largest IP Trunk capacity"
ws = wb[sheet_name]
rows = list(ws.rows)
result = {}
def create_points_for_year(year, start_column):
for i in range(5, 47):
nren_name = rows[i][5].value
if not nren_name:
continue
nren_name = nren_name.upper()
largest_capacity = rows[i][start_column].value
if largest_capacity:
result[(nren_name, year)] = int(largest_capacity)
create_points_for_year(2016, 12)
create_points_for_year(2017, 11)
create_points_for_year(2018, 10)
create_points_for_year(2019, 9)
create_points_for_year(2020, 8)
create_points_for_year(2021, 7)
create_points_for_year(2022, 6)
return result
def fetch_typical_backbone_capacity_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "Typical IP Trunk capacity"
ws = wb[sheet_name]
rows = list(ws.rows)
result = {}
def create_points_for_year(year, start_column):
for i in range(5, 47):
nren_name = rows[i][4].value
if not nren_name:
continue
nren_name = nren_name.upper()
typical_capacity = rows[i][start_column].value
if typical_capacity:
result[(nren_name, year)] = int(typical_capacity)
create_points_for_year(2016, 11)
create_points_for_year(2017, 10)
create_points_for_year(2018, 9)
create_points_for_year(2019, 8)
create_points_for_year(2020, 7)
create_points_for_year(2021, 6)
create_points_for_year(2022, 5)
return result
def fetch_non_r_e_peers_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "Peering-Non R& Network"
ws = wb[sheet_name]
rows = list(ws.rows)
def create_points_for_year(year, start_column):
for i in range(5, 48):
nren_name = rows[i][2].value
if not nren_name:
continue
nren_name = nren_name.upper()
nr_peers = rows[i][start_column].value
if nr_peers:
yield nren_name, year, int(nr_peers)
yield from create_points_for_year(2016, 10)
yield from create_points_for_year(2017, 9)
yield from create_points_for_year(2018, 8)
yield from create_points_for_year(2019, 7)
yield from create_points_for_year(2020, 6)
yield from create_points_for_year(2021, 5)
yield from create_points_for_year(2022, 3)
def fetch_ops_automation_excel_data():
wb = openpyxl.load_workbook(EXCEL_FILE_NETWORKS, data_only=True, read_only=True)
sheet_name = "Automation"
ws = wb[sheet_name]
rows = list(ws.rows)
def create_points_for_year(year, start_column):
for i in range(5, 48):
nren_name = rows[i][1].value
if not nren_name:
continue
nren_name = nren_name.upper()
automation = rows[i][start_column].value
specifics = rows[i][start_column + 1].value or ""
if automation:
automation = YesNoPlanned[automation.lower()]
yield nren_name, year, automation, specifics
yield from create_points_for_year(2021, 5)
yield from create_points_for_year(2022, 3)
Loading