Added company size visualization backend

4 files changed, 72 insertions, 4 deletions

diff --git a/src/app.py b/src/app.py
index aa0d2b7..3ca8a66 100644
--- a/src/app.py
+++ b/src/app.py
@@ -103,6 +103,20 @@ def api_get_heatmap_data():
         
         return flask.jsonify(db.get_heatmap_data("hourly", year))
 
+@app.route("/api/size")
+def api_get_size_data():
+    pay_type = flask.request.args.get("Pay Type")
+    year = flask.request.args.get("year")
+    print("year: '%s'" % year)
+    if pay_type is None or pay_type.lower() not in {'hourly', 'bonuses'}:
+        return flask.abort(400, "The key `pay type` must be equal to 'hourly' or 'bonuses'")
+    with database.PayGapDatabase(host = host) as db:
+        if year is not None:
+            if year not in db.get_years():
+                return flask.abort(400, "Unrecognised year '%s'. The year option must be in %s" % (year, ", ".join(db.get_years())))
+        
+        return flask.jsonify(db.get_pay_by_employer_size(pay_type, year))
+
 @app.route("/api/getyears")
 def api_get_year_options():
     with database.PayGapDatabase(host = host) as db:
diff --git a/src/charts.json b/src/charts.json
index 8624fec..29739a4 100644
--- a/src/charts.json
+++ b/src/charts.json
@@ -72,6 +72,21 @@
                 "Year": "<Years>"
             },
             "description": "A bar chart showing median bonus pay gender inequality by SIC Section. SIC is a system that describes the business of an employer. The general trend is the same as hourly median pay, but in some fields such as waste management, transportation and storage, and construction, women are sometimes paid significantly more than men in bonus pay. The user can refine the chart by filtering down to a specific year. The general trend of doing this is that during the years 2019-2020 bonus pay inequality increased, with most employers paying their female employees less, but this trend has not generally continued."
+        },
+        {
+            "title": "Median Hourly Pay Difference by Employer Size",
+            "url": "/plot/size?Pay+Type=Hourly",
+            "filters": {
+                "Pay Type": {
+                    "options": [
+                        "Hourly",
+                        "Bonuses"
+                    ],
+                    "default": "Bonuses"
+                },
+                "Year": "<Years>"
+            },
+            "description": "Foo"
         }
     ]
 }
\ No newline at end of file
diff --git a/src/database.py b/src/database.py
index d67b96c..5ec7063 100644
--- a/src/database.py
+++ b/src/database.py
@@ -416,6 +416,25 @@ class PayGapDatabase:
             cursor.execute("SELECT company_number, TRIM(SUBSTRING_INDEX(address, ',', -1)) FROM employer;")
             return cursor.fetchall()
 
+    def get_pay_by_employer_size(self, pay_type, year = None):
+        sql = "SELECT size, COUNT(size), -AVG("
+        if pay_type.lower() == "hourly":
+            sql += "DiffMedianHourlyPercent"
+        elif pay_type.lower() == "bonuses":
+            sql += "DiffMedianBonusPercent"
+        sql += ") FROM employer INNER JOIN pay ON pay.company_number = employer.company_number"
+        if year is not None:
+            sql += " AND source LIKE %s"
+        sql += " GROUP BY size;"
+
+        with self.__connection.cursor() as cursor:
+            if year is None:
+                cursor.execute(sql)
+            else:
+                cursor.execute(sql, ("%" + year.replace("-", "to") + "%", ))
+
+            return [(i[0], i[1], float(i[2])) for i in cursor.fetchall()]
+
 
 
 if __name__ == "__main__":
@@ -429,6 +448,6 @@ if __name__ == "__main__":
     with PayGapDatabase(host = host) as db:
         # print(db.get_years())
         # print(db.get_pay_by_sic_section("bonuses", None))
-        print(db.get_heatmap_data("hourly", db.get_years()[0]))
+        print(db.get_pay_by_employer_size("hourly", db.get_years()[-1]))
         # print(db.append_counties())
 
diff --git a/src/static/scripts.js b/src/static/scripts.js
index 9b52215..dd57c39 100644
--- a/src/static/scripts.js
+++ b/src/static/scripts.js
@@ -11,7 +11,8 @@ function collapseTogglePress(elem, a_elem, num_hidden) {
 const PLOT_FUNC_MAPPINGS = {
     "years": draw_plot_years,
     "sic_sec": draw_plot_sic_sections,
-    "heatmap": draw_heatmap
+    "heatmap": draw_heatmap,
+    "size": draw_plot_size,
 }
 
 $(document).ready(function() {
@@ -258,11 +259,11 @@ function draw_heatmap(containerName, filters) {
             data.forEach(row => {
                 data2.push([row[0], row[2]]);
             });
-            console.log(data2);
+            // console.log(data2);
 
             $.getJSON("/static/ukcounties.json", function(geojson) {
 
-                console.log(geojson);
+                // console.log(geojson);
 
                 Highcharts.mapChart(containerName, {
                     chart: {
@@ -316,4 +317,23 @@ function draw_heatmap(containerName, filters) {
             });       
         });
     })
+}
+
+
+function draw_plot_size(containerName, filters) {
+    fetch(form_api_url(containerName, filters)).then(resp => {
+        resp.json().then((data) => {
+            console.log(data);
+
+            Highcharts.chart(containerName, {
+                chart: {
+                    type: 'column'
+                },
+
+                title: {
+                    text: null
+                },
+            });
+        });
+    })
 }
\ No newline at end of file