Skip to content
GitLab
Explore
Sign in
Primary navigation
Search or go to…
Project
C
compendium-v2
Manage
Activity
Members
Labels
Plan
Issues
Issue boards
Milestones
Wiki
Code
Merge requests
Repository
Branches
Commits
Tags
Repository graph
Compare revisions
Snippets
Build
Pipelines
Jobs
Pipeline schedules
Artifacts
Deploy
Releases
Package registry
Container registry
Model registry
Operate
Environments
Terraform modules
Monitor
Incidents
Analyze
Value stream analytics
Contributor analytics
CI/CD analytics
Repository analytics
Model experiments
Help
Help
Support
GitLab documentation
Compare GitLab plans
Community forum
Contribute to GitLab
Provide feedback
Keyboard shortcuts
?
Snippets
Groups
Projects
Show more breadcrumbs
geant-swd
compendium-v2
Merge requests
!84
Feature/comp 284 excel publisher networks
Code
Review changes
Check out branch
Download
Patches
Plain diff
Merged
Feature/comp 284 excel publisher networks
feature/COMP-284_excel_publisher_networks
into
develop
Overview
0
Commits
3
Pipelines
0
Changes
6
Merged
Remco Tukker
requested to merge
feature/COMP-284_excel_publisher_networks
into
develop
1 year ago
Overview
0
Commits
3
Pipelines
0
Changes
6
Expand
add the final models to the publishers
manually tested
0
0
Merge request reports
Compare
develop
version 1
cac86203
1 year ago
develop (base)
and
latest version
latest version
e4936163
3 commits,
1 year ago
version 1
cac86203
2 commits,
1 year ago
6 files
+
438
−
6
Inline
Compare changes
Side-by-side
Inline
Show whitespace changes
Show one file at a time
Files
6
Search (e.g. *.vue) (Ctrl+P)
compendium_v2/publishers/excel_parser.py
+
238
−
1
Options
@@ -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