Feature/comp 168 v1 publisher ec projects

added v1 publish for ecprojects and parent org data fix some off by one errors in the v1 publisher causing the last line to be ignored

compendium_v2/background_task/parse_excel_data.py

@@ -20,8 +20,8 @@ def fetch_budget_excel_data():
     # select the active worksheet
     ws = wb[sheet_name]
     # iterate over the rows in the worksheet
-    for row in range(14, 57):
-        for col in range(3, 8):
+    for row in range(14, 58):
+        for col in range(3, 9):
             # extract the data from the row
             nren = ws.cell(row=row, column=2).value
             budget = ws.cell(row=row, column=col).value
@@ -29,8 +29,6 @@ def fetch_budget_excel_data():
 
             if budget is not None:
                 budget = round(budget / 1000000, 2)
-                if budget > 200:
-                    logger.info(f'{nren} has budget set to >200M EUR for {year}. ({budget})')
 
                 yield nren.upper(), budget, year
 
@@ -99,19 +97,19 @@ def fetch_funding_excel_data():
                 yield (nren.upper(), year, client_institution, european_funding, gov_public_bodies, commercial, other)
 
     # For 2016
-    yield from create_points_for_year_until_2017(8, 50, 2016, 43, 45)
+    yield from create_points_for_year_until_2017(8, 51, 2016, 43, 45)
 
     # For 2017
-    yield from create_points_for_year_until_2017(8, 50, 2017, 32, 35)
+    yield from create_points_for_year_until_2017(8, 51, 2017, 32, 35)
 
     # For 2018
-    yield from create_points_for_year_from_2018(8, 50, 2018, 21)
+    yield from create_points_for_year_from_2018(8, 51, 2018, 21)
 
     # For 2019
-    yield from create_points_for_year_from_2018(8, 50, 2019, 12)
+    yield from create_points_for_year_from_2018(8, 51, 2019, 12)
 
     # For 2020
-    yield from create_points_for_year_from_2018(8, 50, 2020, 3)
+    yield from create_points_for_year_from_2018(8, 51, 2020, 3)
 
 
 def fetch_charging_structure_excel_data():
@@ -148,10 +146,10 @@ def fetch_charging_structure_excel_data():
                 yield nren.upper(), year, charging_structure
 
     # For 2021
-    yield from create_points_for_year(3, 45, 2021, 2)
+    yield from create_points_for_year(3, 46, 2021, 2)
 
     # For 2019
-    yield from create_points_for_year(3, 45, 2019, 6)
+    yield from create_points_for_year(3, 46, 2019, 6)
 
 
 def fetch_staffing_excel_data():
@@ -268,3 +266,53 @@ def fetch_staff_function_excel_data():
 
     # For 2021
     yield from create_points_for_year(2021, 3, 5)
+
+
+def fetch_ecproject_excel_data():
+    # load the xlsx file
+    wb = openpyxl.load_workbook(EXCEL_FILE, data_only=True, read_only=True)
+
+    # select the active worksheet
+    sheet_name = "7. EC Projects"
+    ws = wb[sheet_name]
+
+    start_row = 6
+
+    def create_points_for_year(year, start_column, end_row):
+        for row in range(start_row, end_row):
+            # extract the data from the row
+            nren = ws.cell(row=row, column=start_column).value
+            if nren is None:
+                continue
+            project = ws.cell(row=row, column=start_column + 1).value
+            if project is None:
+                continue
+            yield nren.upper(), year, project
+
+    yield from create_points_for_year(2017, 13, 165)
+
+    yield from create_points_for_year(2018, 10, 165)
+
+    yield from create_points_for_year(2019, 7, 165)
+
+    yield from create_points_for_year(2020, 4, 180)
+
+    yield from create_points_for_year(2021, 1, 173)
+
+
+def fetch_organization_excel_data():
+    # load the xlsx file
+    wb = openpyxl.load_workbook(EXCEL_FILE, data_only=True, read_only=True)
+
+    # select the active worksheet
+    sheet_name = "Organization"
+    ws = wb[sheet_name]
+
+    # iterate over the rows in the worksheet
+    for row in range(5, 48):
+        # extract the data from the row
+        nren = ws.cell(row=row, column=2).value
+        parent_org = ws.cell(row=row, column=4).value
+
+        if parent_org not in [None, 'NA', 'N/A']:
+            yield nren.upper(), 2021, parent_org