diff options
| -rw-r--r-- | app.py | 21 | ||||
| -rw-r--r-- | charts.json | 2 | ||||
| -rw-r--r-- | database.py | 55 | ||||
| -rw-r--r-- | static/scripts.js | 29 |
4 files changed, 88 insertions, 19 deletions
@@ -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(); } |