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 <sascha.teichmann@intevation.de>
sean@1: author: Bernhard Reiter <bernhard@intevation.de>
sean@1: author: Sean Engelhardt <sean.engelhardt@intevation.de>
mschieder@32: author: Magnus Schieder <magnus.schieder@intevation.de>
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)