Added index page for a specific employer, added large plots for those specific employers

7 files changed, 397 insertions, 20 deletions

diff --git a/src/app.py b/src/app.py
index 3ca8a66..bd58cfc 100644
--- a/src/app.py
+++ b/src/app.py
@@ -107,7 +107,7 @@ def api_get_heatmap_data():
 def api_get_size_data():
     pay_type = flask.request.args.get("Pay Type")
     year = flask.request.args.get("year")
-    print("year: '%s'" % 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:
@@ -117,6 +117,20 @@ def api_get_size_data():
         
         return flask.jsonify(db.get_pay_by_employer_size(pay_type, year))
 
+@app.route("/api/type")
+def api_get_type_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_type(pay_type, year))
+
 @app.route("/api/getyears")
 def api_get_year_options():
     with database.PayGapDatabase(host = host) as db:
@@ -144,12 +158,45 @@ def get_chart_elem(url):
         if url.startswith(i["url"]):
             return i
 
+def get_employer_chart_elem(url, employer):
+    for i in get_charts()["employer"]:
+        if url.startswith(i["url"].replace("<employer>", employer)):
+            return i
+
 def get_chart_elem_strict(url):
     for i in get_charts()["index"]:
         print(urllib.parse.urlsplit(i["url"]).path, urllib.parse.urlsplit(url).path)
         if urllib.parse.urlsplit(i["url"]).path == urllib.parse.urlsplit(url).path:
             return i
 
+@app.route("/api/company/<employer>/years")
+def api_search_years_for_employer(employer):
+    pay_type = flask.request.args.get("Pay Type")
+    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:
+        return flask.jsonify(db.get_pay_for_employer(pay_type, employer))
+
+def process_employer_charts(employercharts, employername):
+    o = employercharts
+    for chart in o:
+        chart["url"] = chart["url"].replace("<employer>", employername)
+    return o
+
+@app.route("/company/<employer>")
+def serve_employer_index(employer):
+    with database.PayGapDatabase(host = host) as db:
+        return flask.render_template(   
+            "index.html.j2",
+            title = db.get_employer_details(employer)["Employer Name"],
+            charts = process_employer_charts(get_charts()["employer"], employer)
+        )   
+
+@app.route("/api/<employer>/details")
+def api_employer_details(employer):
+    with database.PayGapDatabase(host = host) as db:
+        return flask.jsonify(db.get_employer_details(employer))
+
 @app.route("/plot/<name>")
 def serve_large_plot(name):
     with database.PayGapDatabase(host = host) as db:
@@ -171,8 +218,8 @@ def serve_large_plot(name):
     elem["url"] = flask.request.full_path
     # print("elem", elem)
     current_filters = dict(flask.request.args)
-    print("filters", filters)
-    print("current_filters", current_filters)
+    # print("filters", filters)
+    # print("current_filters", current_filters)
     return flask.render_template(
         "plot.html.j2",
         title = elem["title"],
@@ -183,6 +230,24 @@ def serve_large_plot(name):
         len = len
     )
 
+@app.route("/plot/company/<employer>/<name>")
+def serve_employer_large_plot(employer, name):
+    elem = get_employer_chart_elem(flask.request.full_path, employer)
+    elem["url"] = flask.request.full_path
+    filters = elem["filters"]
+    current_filters = dict(flask.request.args)
+    # print(filters, current_filters)
+    with database.PayGapDatabase(host = host) as db:
+        return flask.render_template(
+            "plot.html.j2",
+            title = db.get_employer_details(employer)["Employer Name"] + " " + elem["title"],
+            elem = elem,
+            alt = elem["description"],
+            # filters = filters,
+            # current_filters = current_filters,
+            len = len
+        )
+
 if __name__ == "__main__":
     try:
         if sys.argv[1] == "--production":
diff --git a/src/charts.json b/src/charts.json
index 29739a4..8b6ba5f 100644
--- a/src/charts.json
+++ b/src/charts.json
@@ -27,6 +27,21 @@
             "description": "A line plot showing hourly gender pay inequality data from 2017 to today. Hourly pay for women is 12-15% lower for all time periods and hasn't changed much. The user can filter down the data into SIC Categories (SIC is a system of codes to describe what an employer does), employer types (for example a private corporation or overseas entity) and the employer size. The overral trend doesn't change much with these filters, but some fields have more or less gender inequality in hourly pay. For example social work activities, and the accomodation and food industry typically have less inequality, i.e. 6-10% less. The areas with the greatest inequality are in the construction, financial services and education industries, where it can be 20-25% less per hour. Public sector employers typically have more pay inequality, from around 13% to 17%. In terms of number of employees, the general trend is that the more employees an employer has, the less pay inequality there is: From 13% to 9%."
         },
         {
+            "title": "Median Hourly Pay Difference by Employer Type",
+            "url": "/plot/type?Pay+Type=Hourly",
+            "filters": {
+                "Pay Type": {
+                    "options": [
+                        "Hourly",
+                        "Bonuses"
+                    ],
+                    "default": "Hourly"
+                },
+                "Year": "<Years>"
+            },
+            "description": "foo!"
+        },
+        {
             "title": "Median Bonus Pay Difference by Year",
             "url": "/plot/years?Pay+Type=Bonuses",
             "filters": {
@@ -82,11 +97,72 @@
                         "Hourly",
                         "Bonuses"
                     ],
+                    "default": "Hourly"
+                },
+                "Year": "<Years>"
+            },
+            "description": "A bar chart showing median hourly pay inequality between men and women by the employer size. The general trend is that as the number of employees increases, the pay inequality decreases- from 13% in companies with less than 250 employees, to 9% companies with over 20,000 employees. The user has the option of filtering the results down to a specific year, from 2017 to today. The general trend here is that as the time moves forward, the trend of employers with more employees having less pay inequality becomes more pronounced."
+        },
+        {
+            "title": "Median Bonus Pay Difference by Employer Size",
+            "url": "/plot/size?Pay+Type=Bonuses",
+            "filters": {
+                "Pay Type": {
+                    "options": [
+                        "Hourly",
+                        "Bonuses"
+                    ],
                     "default": "Bonuses"
                 },
                 "Year": "<Years>"
             },
-            "description": "Foo"
+            "description": "A bar chart showing median bonus pay inequality between men and women, by employer size. The general trend is the converse of median hourly pay, that the larger the employer the greater the pay inequality- from ~12% to 20%. The user can filter down the results to a specific year, from 2017 to today. The trend is the same as for hourly pay: that as time progresses pay inequality continues."
+        },
+        {
+            "title": "Median Bonus Pay Difference by Employer Type",
+            "url": "/plot/type?Pay+Type=Bonuses",
+            "filters": {
+                "Pay Type": {
+                    "options": [
+                        "Hourly",
+                        "Bonuses"
+                    ],
+                    "default": "Bonuses"
+                },
+                "Year": "<Years>"
+            },
+            "description": "A bar chart showing median bonus pay inequality by the employer type, for example public and private corporations. There is no real trend to the data, unline with the hourly pay metric.The pay difference typically ranges from 0% to -50%. There are some company types for which women are paid significantly more in bonuses, for example in 'Unregistered Companies', possibly due to a small number of sample sizes. The user has the option of filtering the results down to a specific time-frame, from 2017 today. Doing so makes no new trend appear."
+        }
+    ],
+    "employer":
+    [
+        {
+            "title": "Median Hourly Pay Difference by Year",
+            "url": "/plot/company/<employer>/years?Pay+Type=hourly",
+            "filters": {
+                "Pay Type": {
+                    "options": [
+                        "Hourly",
+                        "Bonuses"
+                    ],
+                    "default": "Hourly"
+                }
+            },
+            "description": ""
+        },
+        {
+            "title": "Median Bonus Pay Difference by Year",
+            "url": "/plot/company/<employer>/years?Pay+Type=bonuses",
+            "filters": {
+                "Pay Type": {
+                    "options": [
+                        "Hourly",
+                        "Bonuses"
+                    ],
+                    "default": "Bonuses"
+                }
+            },
+            "description": ""
         }
     ]
 }
\ No newline at end of file
diff --git a/src/database.py b/src/database.py
index 5ec7063..6b52119 100644
--- a/src/database.py
+++ b/src/database.py
@@ -302,6 +302,20 @@ class PayGapDatabase:
         with self.__connection.cursor() as cursor:
             cursor.execute("SELECT DISTINCT source FROM pay;")
             return [self._source_name_to_year(i[0]) for i in cursor.fetchall()]
+
+    def get_pay_for_employer(self, pay_type, company_id,):
+        for section_name in self.get_sic_sections():
+            sql = "SELECT source, -AVG("
+            if pay_type.lower() == "hourly":
+                sql += "DiffMedianHourlyPercent"
+            elif pay_type.lower() == "bonuses":
+                sql += "DiffMedianBonusPercent"
+            sql += """
+            ) FROM pay WHERE company_number = %s GROUP BY source ORDER BY source;
+            """
+            with self.__connection.cursor() as cursor:
+                cursor.execute(sql, (company_id, ))
+                return [[i[0], float(i[1])] for i in cursor.fetchall()]
     
     def get_pay_by_sic_section(self, pay_type, year = None):
         pay = []
@@ -340,7 +354,7 @@ class PayGapDatabase:
         return sorted(pay, key = operator.itemgetter(1), reverse = True)
 
     def get_heatmap_data(self, pay_type, year = None):
-        sql = "SELECT  insinuated_loc, COUNT(insinuated_loc), -AVG("
+        sql = "SELECT insinuated_loc, COUNT(insinuated_loc), -AVG("
         if pay_type.lower() == "hourly":
             sql += "DiffMedianHourlyPercent"
         elif pay_type.lower() == "bonuses":
@@ -425,7 +439,26 @@ class PayGapDatabase:
         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;"
+        sql += " GROUP BY size ORDER BY size;"
+
+        with self.__connection.cursor() as cursor:
+            if year is None:
+                cursor.execute(sql)
+            else:
+                cursor.execute(sql, ("%" + year.replace("-", "to") + "%", ))
+
+            return sorted([(i[0], i[1], float(i[2])) for i in cursor.fetchall()], key = lambda e: self.get_company_sizes().index(e[0]))
+
+    def get_pay_by_employer_type(self, pay_type, year = None):
+        sql = "SELECT type_, -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 WHERE type_ IS NOT NULL"
+        if year is not None:
+            sql += " AND source LIKE %s"
+        sql += " GROUP BY type_;"
 
         with self.__connection.cursor() as cursor:
             if year is None:
@@ -433,7 +466,25 @@ class PayGapDatabase:
             else:
                 cursor.execute(sql, ("%" + year.replace("-", "to") + "%", ))
 
-            return [(i[0], i[1], float(i[2])) for i in cursor.fetchall()]
+            return sorted([(i[0], float(i[1])) for i in cursor.fetchall()], key = operator.itemgetter(1), reverse = True)
+
+    def get_employer_details(self, employer_id):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT name, address, postcode, policy_link, responsible_person, size, status, type_, incorporated FROM employer WHERE company_number = %s;", (employer_id, ))
+            o = cursor.fetchone()
+
+        return {
+            "Employer Name": o[0].title(),
+            "Address": o[1],
+            "Postcode": o[2],
+            "Policy Link": o[3],
+            "Named responsible person": o[4],
+            "Number of Employees": o[5],
+            "Status": o[6],
+            "Employer Type": o[7],
+            "Incorporated Date": o[8],
+            "Companies House Link": "https://find-and-update.company-information.service.gov.uk/company/" + employer_id
+        }
 
 
 
@@ -448,6 +499,7 @@ 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_pay_by_employer_size("hourly", db.get_years()[-1]))
+        # print(db.get_pay_for_employer("bonuses", "RC000651"))
+        print(db.get_employer_details("RC000651"))
         # print(db.append_counties())
 
diff --git a/src/static/scripts.js b/src/static/scripts.js
index dd57c39..60b3a73 100644
--- a/src/static/scripts.js
+++ b/src/static/scripts.js
@@ -13,6 +13,7 @@ const PLOT_FUNC_MAPPINGS = {
     "sic_sec": draw_plot_sic_sections,
     "heatmap": draw_heatmap,
     "size": draw_plot_size,
+    "type": draw_plot_type,
 }
 
 $(document).ready(function() {
@@ -32,6 +33,14 @@ $(document).ready(function() {
         }
     }
 
+    if (window.location.pathname.split("/")[1] === "company") {
+        appendEmployerDetails(window.location.pathname.split("/")[2]);
+    }
+
+    if (window.location.pathname.split("/")[2] === "company") {
+        appendEmployerDetails(window.location.pathname.split("/")[3]);
+    }
+
     fetch("/api/charts.json").then((resp) => {
         resp.json().then((body) => {
             const CHARTS = body;
@@ -41,14 +50,29 @@ $(document).ready(function() {
                 var theId = minicharts.item(i).id;
                 var u = new URL(window.location.origin + theId);
                 var theIdSplit = u.pathname.split("/");
+                var filters;
+                // console.log(theIdSplit);
 
-                CHARTS["index"].forEach(element => {
-                    if (theId === "/minichart" + element.url) {
-                        filters = element["filters"];
-                    }
-                });
+                if (theIdSplit[3] === "company") {
 
-                PLOT_FUNC_MAPPINGS[theIdSplit[theIdSplit.length - 1]](theId, filters);
+                    const employer_id = theIdSplit[4];
+
+                    CHARTS["employer"].forEach(element => {
+                        if (theId === "/minichart" + element.url.replace("<employer>", employer_id)) {
+                            filters = element["filters"];
+                        }
+                    });
+                    // console.log("filters", filters);
+                    PLOT_FUNC_MAPPINGS[theIdSplit[theIdSplit.length - 1]](theId, filters);
+                } else {
+                    CHARTS["index"].forEach(element => {
+                        if (theId === "/minichart" + element.url) {
+                            filters = element["filters"];
+                        }
+                    });
+    
+                    PLOT_FUNC_MAPPINGS[theIdSplit[theIdSplit.length - 1]](theId, filters);
+                }
             }
 
             var charts = document.getElementsByClassName("chart");
@@ -71,11 +95,40 @@ $(document).ready(function() {
     })
 });
 
+function appendEmployerDetails(employer_id) {
+    var aside = document.getElementById("theAside");
+    var dl = document.createElement("dl");
+    dl.classList.add("companyInfo")
+
+    fetch("/api/" + employer_id + "/details").then(resp => {
+        resp.json().then(data => {
+            for (const [k, v] of Object.entries(data)) {
+                if (v === null || v === "") {
+                    continue;
+                }
+                if (k.endsWith(" Link")) {
+                    dl.innerHTML += "<dt><a href='" + v + "'>" + k + "</a></dt>";
+                } else {
+                    dl.innerHTML += "<dt>" + k + "</dt><dd>" + v + "</dd>";
+                }
+            }
+        })
+    });
+    
+    aside.appendChild(dl);
+}
+
 function form_api_url(containerName, filters) {
     // console.log(filters);
     // console.log(containerName);
-    var name = containerName.split("/")[containerName.split("/").length - 1];
-    var url = new URL(window.location.origin + "/api/" + name);
+    var s = containerName.split("/");
+    var name = s[s.length - 1];
+    var url;
+    if (s[3] === "company") {
+        url = new URL(window.location.origin + "/api/company/" + s[4] + "/" + name);
+    } else {
+        url = new URL(window.location.origin + "/api/" + name);
+    }
     // for (const [filterName, value] of Object.entries(filters)) {
         
     //     if (typeof value === 'object' && value !== null) {
@@ -120,6 +173,12 @@ function draw_plot_years(containerName, filters) {
                     labels: {
                         format: '{value}%'
                     },
+                    plotLines: [{
+                        value: 0,
+                        width: 2,
+                        color: 'black',
+                        zIndex: 10
+                    }]
                     // tickPositioner: function () {
                     //     // var maxDeviation = Math.ceil(Math.max(Math.abs(this.dataMax), Math.abs(this.dataMin)));
                     //     // var halfMaxDeviation = Math.ceil(maxDeviation / 2);
@@ -319,11 +378,19 @@ 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);
+            // console.log(data);
+
+            const isPreview = (containerName.substring(1, 6) === "chart");
+
+            var categories = [];
+            var pays = [];
+            data.forEach(elem => {
+                categories.push(elem[0]);
+                pays.push(elem[2]);
+            });
 
             Highcharts.chart(containerName, {
                 chart: {
@@ -333,7 +400,116 @@ function draw_plot_size(containerName, filters) {
                 title: {
                     text: null
                 },
+
+                xAxis: {
+                    categories: categories,
+                    categories: categories,
+                    labels: {
+                        enabled: isPreview
+                    },
+                    title: {
+                        text: 'Number of Employees',
+                        enabled: isPreview
+                    },
+                },
+
+                yAxis: {
+                    title: {
+                        text: 'Median Pay',
+                        enabled: isPreview
+                    },
+                    labels: {
+                        format: '{value}%'
+                    },
+                    plotLines: [{
+                        value: 0,
+                        width: 2,
+                        color: 'black',
+                        zIndex: 10
+                    }]
+                },
+
+                series: [{
+                    data: pays,
+                    lineWidth: 4,
+                    showInLegend: false,
+                    name: "Pay Gap",
+                    color: 'Green',
+                    threshold: 0,
+                    negativeColor: 'Red',
+                }]
             });
         });
     })
+}
+
+function draw_plot_type(containerName, filters) {
+    fetch(form_api_url(containerName, filters)).then(resp => {
+        resp.json().then((data) => {
+            console.log(data);
+
+            const isPreview = (containerName.substring(1, 6) === "chart");
+
+            var categories = [];
+            var pays = [];
+            data.forEach(elem => {
+                categories.push(elem[0]);
+                pays.push(elem[1]);
+            });
+
+            Highcharts.chart(containerName, {
+                chart: {
+                    type: 'bar'
+                },
+
+                title: {
+                    text: null
+                },
+
+                plotOptions: {
+                    bar: {
+                        dataLabels: {
+                            align: "center"
+                        }
+                    }
+                },
+
+                xAxis: {
+                    categories: categories,
+                    labels: {
+                        enabled: isPreview
+                    },
+                    title: {
+                        text: 'Employer Type',
+                        enabled: isPreview
+                    },
+                    type: 'category'
+                },
+
+                yAxis: {
+                    title: {
+                        text: 'Median Pay',
+                        enabled: isPreview
+                    },
+                    labels: {
+                        format: '{value}%'
+                    },
+                    plotLines: [{
+                        value: 0,
+                        width: 2,
+                        color: 'black',
+                        zIndex: 10
+                    }]
+                },
+
+                series: [{
+                    data: pays,
+                    showInLegend: false,
+                    negativeColor: 'Red',
+                    color: 'Green',
+                    name: 'Pay Gap'
+                }]
+            })
+        })
+    });
 }
\ No newline at end of file
diff --git a/src/static/style.css b/src/static/style.css
index ee35cae..d3235c7 100644
--- a/src/static/style.css
+++ b/src/static/style.css
@@ -152,6 +152,14 @@ input[type="search"] {
   /* input[type="search"] {} */
 }
 
+aside dl {
+  font-size: small;
+}
+
+aside dl dt {
+  font-weight: bold;
+}
+
 .minichart {
   min-height: 220px;
   width: 100%;
diff --git a/src/templates/index.html.j2 b/src/templates/index.html.j2
index 68f9837..029ed70 100644
--- a/src/templates/index.html.j2
+++ b/src/templates/index.html.j2
@@ -1,7 +1,7 @@
 {% extends "template.html.j2" %}
 {% block content %}
     <div id="multicharts">
-        <ul>
+        <ul id="charts_ul">
             {% for elem in charts %}
                 <li>
                     <figure class="chart_container">
diff --git a/src/templates/template.html.j2 b/src/templates/template.html.j2
index 834e798..81554b1 100644
--- a/src/templates/template.html.j2
+++ b/src/templates/template.html.j2
@@ -30,7 +30,7 @@
         <p>Data provided by the <a href="https://gender-pay-gap.service.gov.uk/">UK Government</a> and the <a href="https://www.ons.gov.uk/aboutus/transparencyandgovernance/freedomofinformationfoi/ukpostcodestownsandcounties">Office for National Statistics</a></p>
     </header>
 
-    <aside>
+    <aside id="theAside">
         <form id="searchform" action="/search_click" method="POST">
             <input type="search" id="search_entry" name="search" required>
             <input type="submit" value="Search" id="search_btn">