Finished filtering time-series pay gap

4 files changed, 88 insertions, 19 deletions

diff --git a/app.py b/app.py
index 4c3675c..7fc3d6e 100644
--- a/app.py
+++ b/app.py
@@ -41,16 +41,22 @@ def apply_redirect(name):
         if v != "No filter":
             new_args[k] = v
 
-    print("/" + "/".join(flask.request.full_path.split("/")[1:-1]) + "?" + urllib.parse.urlencode(new_args))
+    # print("/" + "/".join(flask.request.full_path.split("/")[1:-1]) + "?" + urllib.parse.urlencode(new_args))
     return flask.redirect("/" + "/".join(flask.request.full_path.split("/")[1:-1]) + "?" + urllib.parse.urlencode(new_args))
 
 @app.route("/api/years")
 def api_get_years():
     pay_type = flask.request.args.get("Pay Type")
+    sic_type = flask.request.args.get("SIC Type")
+    employer_type = flask.request.args.get("Employer Type")
+    employer_size = flask.request.args.get("Employer Size")
+    # print("sic_type", sic_type)
+    # print("employer_type", employer_type)
+    # print("employer_size", employer_size)
     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_by_year(pay_type))
+        return flask.jsonify(db.get_pay_by_year(pay_type, sic_section_name = sic_type, employer_size = employer_size, employer_type = employer_type))
 
 @app.route("/search")
 def search():
@@ -68,14 +74,16 @@ def search():
 
 def get_chart_elem(url):
     for i in get_charts()["index"]:
-        if urllib.parse.urlparse(i["url"]).path ==  urllib.parse.urlparse(url).path:
+        print(i["url"], url)
+        # if i["url"] == url:
+        #     return i
+        if url.startswith(i["url"]):
             return i
 
-
 @app.route("/plot/<name>")
 def serve_large_plot(name):
     with database.PayGapDatabase(host = host) as db:
-        print(flask.request.full_path)
+        # print(flask.request.full_path)
         elem = get_chart_elem(flask.request.full_path)
         filters = elem["filters"]
         for k, v in filters.items():
@@ -86,7 +94,10 @@ def serve_large_plot(name):
             if v == "<CompanySize>":
                  filters[k] = {"options": db.get_company_sizes()}
 
+    elem["url"] = flask.request.full_path
+    # print("elem", elem)
     current_filters = dict(flask.request.args)
+    # print("current_filters", current_filters)
     return flask.render_template(
         "plot.html.j2",
         title = elem["title"],
diff --git a/charts.json b/charts.json
index 2bc3ef9..16da391 100644
--- a/charts.json
+++ b/charts.json
@@ -25,7 +25,7 @@
                         "Hourly",
                         "Bonuses"
                     ],
-                    "default": "Hourly"
+                    "default": "Bonuses"
                 },
                 "SIC Type": "<SICType>",
                 "Employer Type": "<CompanyType>",
diff --git a/database.py b/database.py
index cfb1eb0..7be9938 100644
--- a/database.py
+++ b/database.py
@@ -244,15 +244,64 @@ class PayGapDatabase:
     def _source_name_to_year(self, source):
         return os.path.splitext(source)[0].split("-")[-1].strip().replace("to", "-")
 
-    def get_pay_by_year(self, pay_type):
+    def get_pay_by_year(self, pay_type, sic_section_name = None, employer_type = None, employer_size = None):
         sql = "SELECT source, -AVG("
         if pay_type.lower() == "hourly":
             sql += "DiffMedianHourlyPercent"
         elif pay_type.lower() == "bonuses":
             sql += "DiffMedianBonusPercent"
-        sql += ") FROM pay GROUP BY source ORDER BY source;"
+        sql += ") FROM pay"
+
+        subqueries = []
+        args = []
+        if sic_section_name is not None:
+            subqueries.append("""
+            company_number IN (
+                SELECT DISTINCT company_number FROM employer_sic WHERE sic_code IN (
+                    SELECT DISTINCT sic_code FROM sic WHERE sic_section = (
+                        SELECT sic_section_id FROM sic_sections WHERE sic_section_name = %s
+                    )
+                )
+            )""")
+            args.append(sic_section_name)
+        if employer_type is not None:
+            subqueries.append("""
+            company_number IN (
+                SELECT company_number FROM employer WHERE type_ = %s
+            )
+            """)
+            args.append(employer_type)
+        if employer_size is not None:
+            subqueries.append("""
+            company_number IN (
+                SELECT company_number FROM employer WHERE size = %s
+            )
+            """)
+            args.append(employer_size)
 
         with self.__connection.cursor() as cursor:
-            cursor.execute(sql)
+            if sic_section_name is not None or employer_type is not None or employer_size is not None:
+                sql += " WHERE {}".format(" OR ".join(subqueries))
+
+                sql += " GROUP BY source ORDER BY source;"
+                cursor.execute(sql, tuple(args))
+
+            else:
+                sql += " GROUP BY source ORDER BY source;"
+                cursor.execute(sql)
+
+            # print(sql)
+            # print(tuple(args))
             return [(self._source_name_to_year(i[0]), float(i[1])) for i in cursor.fetchall()]
 
+if __name__ == "__main__":
+    if not os.path.exists(".docker"):
+        import dotenv
+        dotenv.load_dotenv(dotenv_path = "db.env")
+        host = "srv.home"
+    else:
+        host = "db"
+
+    with PayGapDatabase(host = host) as db:
+        print(db.get_pay_by_year("hourly", None, None, "20,000 or more"))
+
diff --git a/static/scripts.js b/static/scripts.js
index 01196d3..261acfa 100644
--- a/static/scripts.js
+++ b/static/scripts.js
@@ -13,7 +13,11 @@ const PLOT_FUNC_MAPPINGS = {
 }
 
 $(document).ready(function() {
-    document.getElementById("filterform").action = window.location.pathname + "/apply_click";
+    const filterform = document.getElementById("filterform");
+    if (filterform !== null) {
+        filterform.action = window.location.pathname + "/apply_click";
+    }
+    
 
     fetch("/api/charts.json").then((resp) => {
         resp.json().then((body) => {
@@ -41,8 +45,10 @@ $(document).ready(function() {
                 var theIdSplit = u.pathname.split("/");
 
                 CHARTS["index"].forEach(element => {
-                    if (theId === "/chart" + element.url) {
+                    if (location.href.substr(location.href.indexOf(location.host)+location.host.length).startsWith(element["url"])) {
+                        console.log(location.href.substr(location.href.indexOf(location.host)+location.host.length), element["url"]);
                         filters = element["filters"];
+                        // console.log(element);
                     }
                 });
 
@@ -53,17 +59,20 @@ $(document).ready(function() {
 });
 
 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);
-    for (const [filterName, value] of Object.entries(filters)) {
+    // for (const [filterName, value] of Object.entries(filters)) {
         
-        if (typeof value === 'object' && value !== null) {
-            if ("default" in value) {
-                // console.log(filterName, value["default"]);
-                url.searchParams.append(filterName, value["default"]);
-            }
-        }       
-    }
+    //     if (typeof value === 'object' && value !== null) {
+    //         if ("default" in value) {
+    //             // console.log(filterName, value["default"]);
+    //             url.searchParams.append(filterName, value["default"]);
+    //         }
+    //     }       
+    // }
+    console.log("fetching ", url.toString());
     return url.toString();
 }