sean@1: #!/usr/bin/env python3 sean@1: sean@1: """ Display previously saved issues from a database on webpage via CGI. sean@1: sean@1: author: Sascha L. Teichmann sean@1: author: Bernhard Reiter sean@1: author: Sean Engelhardt mschieder@32: author: Magnus Schieder mschieder@32: (c) 2010,2015,2018 by Intevation GmbH sean@1: sean@1: This is Free Software unter the terms of the sean@1: GNU GENERAL PUBLIC LICENSE Version 3 or later. sean@1: See http://www.gnu.org/licenses/gpl-3.0.txt for details sean@1: sean@1: sean@1: ##Usage Example: ## bernhard@7: see display_issues_demo.py or __main__ section below. sean@1: """ sean@1: sean@1: import sqlite3 as db sean@1: import cgitb sean@1: import roundup_content_data as rcd gernot@13: import os sean@1: sean@1: sean@1: def make_js_object_string(array): sean@1: formated = [] sean@1: sean@1: for item in array: sean@1: formated.append("{points: " + str(item) + "}") sean@1: sean@1: return ",".join(formated) sean@1: sean@1: sean@1: def make_js_object_date(array): sean@1: formated = [] sean@1: sean@1: for item in array: sean@1: formated.append("{date : new Date('" + str(item) + "')}") sean@1: sean@1: return ", ".join(formated) sean@1: mschieder@31: def get_webpage(data_dict, columns, search_parameters, keywords, graph=None): mschieder@32: """Returns the website with inserted data.""" sean@1: gernot@14: if graph is None: gernot@14: graph = os.path.dirname(os.path.realpath(__file__)) + '/graph.html' gernot@13: gernot@13: with open(graph, "r") as html_chart_file: sean@1: base_html_data = html_chart_file.read() sean@1: mschieder@24: if "None" not in columns: mschieder@24: data_dict["None"] = [0] mschieder@20: mschieder@32: # Converts the data to a JS object. mschieder@25: js_data_dickt ="{" mschieder@25: for col in columns.split(", "): mschieder@25: js_data_dickt += col + ":[" + make_js_object_string(data_dict[col]) + "]," mschieder@25: js_data_dickt += "}" mschieder@25: sean@1: base_html_data = (base_html_data mschieder@31: .replace("search_parameters", search_parameters) mschieder@20: .replace("keywords", keywords) mschieder@25: .replace("js_data_dickt", js_data_dickt) mschieder@24: .replace("var timestamp=[];", "var timestamp=[" + make_js_object_date(data_dict["date"]) + "]")) sean@1: sean@1: return base_html_data sean@1: mschieder@31: def compile_db_stats_html(db_file, sql_select, columns, search_parameters="", keywords="", graph=None): mschieder@24: mschieder@24: data_dict = {"date": []} mschieder@24: status_list = columns.split(", ") mschieder@24: for x in status_list: mschieder@24: data_dict[x] = [] sean@1: sean@1: con = None sean@1: cur = None sean@1: sean@1: try: sean@1: con = db.connect(db_file) sean@1: cur = con.cursor() sean@1: cur.execute(sql_select) sean@1: sean@1: for row in cur.fetchall(): mschieder@24: data_dict["date"].append(row[0]) mschieder@24: for x in range(len(status_list)): mschieder@24: data_dict[status_list[x]].append(row[x+1]) mschieder@24: sean@1: finally: sean@1: if cur: sean@1: cur.close() sean@1: if con: sean@1: con.close() sean@1: mschieder@31: return get_webpage(data_dict, columns, search_parameters, keywords, graph) sean@1: gernot@13: def render_webpage(content): gernot@13: for line in content.split("\n"): gernot@13: print(line) gernot@13: mschieder@31: def render_db_stats_as_html(db_file, sql_select, columns, search_parameters="", keywords=""): mschieder@31: render_webpage(compile_db_stats_html(db_file, sql_select, columns, search_parameters, keywords)) sean@1: bernhard@3: if __name__ == '__main__': bernhard@3: cgitb.enable() bernhard@7: #spit out HTML file directly, thus no need to give headers to the server bernhard@7: #print("Content-Type: text/html") bernhard@7: #print() bernhard@3: mschieder@20: render_db_stats_as_html("./demo3.db", rcd.SELECT_ALL)