fix parsing of income sources from excel for 2016 and 2017

fix parsing of income sources from excel for 2016 and 2017

compendium_v2/background_task/parse_excel_data.py

@@ -66,29 +66,49 @@ def fetch_funding_excel_data():
             return float(0)
 
     # iterate over the rows in the worksheet
+    def create_points_for_year_until_2017(start_row, end_row, year, col_nren, col_start):
+        for row in range(start_row, end_row):
+            # extract the data from the row
+            nren = ws.cell(row=row, column=col_nren).value
+            client_institution = ws.cell(row=row, column=col_start).value
+            commercial = ws.cell(row=row, column=col_start + 1).value
+            geant_subsidy = ws.cell(row=row, column=col_start + 2).value
+            gov_public_bodies = ws.cell(row=row, column=col_start + 3).value
+            other_european_funding = ws.cell(row=row, column=col_start + 4).value
+            other = ws.cell(row=row, column=col_start + 5).value
+            print(nren, client_institution, commercial, geant_subsidy, gov_public_bodies, other_european_funding, other)
+
+            client_institution = hard_number_convert(client_institution, "client institution", nren, year)
+            commercial = hard_number_convert(commercial, "commercial", nren, year)
+            geant_subsidy = hard_number_convert(geant_subsidy, "geant subsidy", nren, year)
+            gov_public_bodies = hard_number_convert(gov_public_bodies, "gov/public_bodies", nren, year)
+            other_european_funding = hard_number_convert(other_european_funding, "other european funding", nren, year)
+            other = hard_number_convert(other, "other", nren, year)
+
+            european_funding = geant_subsidy + other_european_funding
 
-    def create_points_for_year(start_row, end_row, year, col_start):
+            # process the data (e.g. save to database)
+            if nren is not None:
+                yield (nren.upper(), year, client_institution,
+                       european_funding,
+                       gov_public_bodies,
+                       commercial, other)
+
+    def create_points_for_year_from_2018(start_row, end_row, year, col_start):
         for row in range(start_row, end_row):
             # extract the data from the row
             nren = ws.cell(row=row, column=col_start).value
-            client_institution = ws.cell(row=row,
-                                         column=col_start + 3).value
+            client_institution = ws.cell(row=row, column=col_start + 3).value
             european_funding = ws.cell(row=row, column=col_start + 4).value
-            gov_public_bodies = ws.cell(row=row,
-                                        column=col_start + 5).value
+            gov_public_bodies = ws.cell(row=row, column=col_start + 5).value
             commercial = ws.cell(row=row, column=col_start + 6).value
             other = ws.cell(row=row, column=col_start + 7).value
 
-            client_institution = hard_number_convert(
-                client_institution, "client institution", nren, year)
-            european_funding = hard_number_convert(
-                european_funding, "european funding", nren, year)
-            gov_public_bodies = hard_number_convert(
-                gov_public_bodies, "gov/public_bodies", nren, year)
-            commercial = hard_number_convert(
-                commercial, "commercial", nren, year)
-            other = hard_number_convert(
-                other, "other", nren, year)
+            client_institution = hard_number_convert(client_institution, "client institution", nren, year)
+            european_funding = hard_number_convert(european_funding, "european funding", nren, year)
+            gov_public_bodies = hard_number_convert(gov_public_bodies, "gov/public_bodies", nren, year)
+            commercial = hard_number_convert(commercial, "commercial", nren, year)
+            other = hard_number_convert(other, "other", nren, year)
 
             # process the data (e.g. save to database)
             if nren is not None:
@@ -98,19 +118,19 @@ def fetch_funding_excel_data():
                        commercial, other)
 
     # For 2016
-    yield from create_points_for_year(8, 50, 2016, 43)
+    yield from create_points_for_year_until_2017(8, 50, 2016, 43, 45)
 
     # For 2017
-    yield from create_points_for_year(8, 50, 2017, 32)
+    yield from create_points_for_year_until_2017(8, 50, 2017, 32, 35)
 
     # For 2018
-    yield from create_points_for_year(8, 50, 2018, 21)
+    yield from create_points_for_year_from_2018(8, 50, 2018, 21)
 
     # For 2019
-    yield from create_points_for_year(8, 50, 2019, 12)
+    yield from create_points_for_year_from_2018(8, 50, 2019, 12)
 
     # For 2020
-    yield from create_points_for_year(8, 50, 2020, 3)
+    yield from create_points_for_year_from_2018(8, 50, 2020, 3)
 
 
 def fetch_charging_structure_excel_data():