annotate display_issues.py @ 36:59e1659a0a0b tip

Update 'TODO.creole' * Old TODOs moved to 'doc/old_TODO.creole'
author Magnus Schieder <mschieder@intevation.de>
date Mon, 26 Nov 2018 16:52:45 +0100
parents 80bbd06fe8ec
children
rev   line source
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
1 #!/usr/bin/env python3
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
2
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
3 """ Display previously saved issues from a database on webpage via CGI.
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
4
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
5 author: Sascha L. Teichmann <sascha.teichmann@intevation.de>
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
6 author: Bernhard Reiter <bernhard@intevation.de>
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
7 author: Sean Engelhardt <sean.engelhardt@intevation.de>
32
80bbd06fe8ec Add Comments
Magnus Schieder <mschieder@intevation.de>
parents: 31
diff changeset
8 author: Magnus Schieder <magnus.schieder@intevation.de>
80bbd06fe8ec Add Comments
Magnus Schieder <mschieder@intevation.de>
parents: 31
diff changeset
9 (c) 2010,2015,2018 by Intevation GmbH
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
10
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
11 This is Free Software unter the terms of the
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
12 GNU GENERAL PUBLIC LICENSE Version 3 or later.
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
13 See http://www.gnu.org/licenses/gpl-3.0.txt for details
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
14
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
15
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
16 ##Usage Example: ##
7
99e2e0e17103 Adding possibility and example to filter the entries.
Bernhard Reiter <bernhard@intevation.de>
parents: 3
diff changeset
17 see display_issues_demo.py or __main__ section below.
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
18 """
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
19
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
20 import sqlite3 as db
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
21 import cgitb
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
22 import roundup_content_data as rcd
13
149d01f43e31 Split up render_db_stats_as_html function
Gernot Schulz <gernot@intevation.de>
parents: 7
diff changeset
23 import os
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
24
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
25
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
26 def make_js_object_string(array):
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
27 formated = []
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
28
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
29 for item in array:
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
30 formated.append("{points: " + str(item) + "}")
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
31
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
32 return ",".join(formated)
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
33
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
34
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
35 def make_js_object_date(array):
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
36 formated = []
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
37
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
38 for item in array:
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
39 formated.append("{date : new Date('" + str(item) + "')}")
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
40
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
41 return ", ".join(formated)
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
42
31
9aca070c86bd Add the filter 'priority' for the search by status.
Magnus Schieder <mschieder@intevation.de>
parents: 29
diff changeset
43 def get_webpage(data_dict, columns, search_parameters, keywords, graph=None):
32
80bbd06fe8ec Add Comments
Magnus Schieder <mschieder@intevation.de>
parents: 31
diff changeset
44 """Returns the website with inserted data."""
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
45
14
b5f7a439bddd Allow to override default graph.html file
Gernot Schulz <gernot@intevation.de>
parents: 13
diff changeset
46 if graph is None:
b5f7a439bddd Allow to override default graph.html file
Gernot Schulz <gernot@intevation.de>
parents: 13
diff changeset
47 graph = os.path.dirname(os.path.realpath(__file__)) + '/graph.html'
13
149d01f43e31 Split up render_db_stats_as_html function
Gernot Schulz <gernot@intevation.de>
parents: 7
diff changeset
48
149d01f43e31 Split up render_db_stats_as_html function
Gernot Schulz <gernot@intevation.de>
parents: 7
diff changeset
49 with open(graph, "r") as html_chart_file:
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
50 base_html_data = html_chart_file.read()
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
51
24
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
52 if "None" not in columns:
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
53 data_dict["None"] = [0]
20
3bb3d9a9f1b7 Filter by keywords and states.
Magnus Schieder <mschieder@intevation.de>
parents: 17
diff changeset
54
32
80bbd06fe8ec Add Comments
Magnus Schieder <mschieder@intevation.de>
parents: 31
diff changeset
55 # Converts the data to a JS object.
25
7161ce4e7ab1 The web-based display is dynamically generated.
Magnus Schieder <mschieder@intevation.de>
parents: 24
diff changeset
56 js_data_dickt ="{"
7161ce4e7ab1 The web-based display is dynamically generated.
Magnus Schieder <mschieder@intevation.de>
parents: 24
diff changeset
57 for col in columns.split(", "):
7161ce4e7ab1 The web-based display is dynamically generated.
Magnus Schieder <mschieder@intevation.de>
parents: 24
diff changeset
58 js_data_dickt += col + ":[" + make_js_object_string(data_dict[col]) + "],"
7161ce4e7ab1 The web-based display is dynamically generated.
Magnus Schieder <mschieder@intevation.de>
parents: 24
diff changeset
59 js_data_dickt += "}"
7161ce4e7ab1 The web-based display is dynamically generated.
Magnus Schieder <mschieder@intevation.de>
parents: 24
diff changeset
60
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
61 base_html_data = (base_html_data
31
9aca070c86bd Add the filter 'priority' for the search by status.
Magnus Schieder <mschieder@intevation.de>
parents: 29
diff changeset
62 .replace("search_parameters", search_parameters)
20
3bb3d9a9f1b7 Filter by keywords and states.
Magnus Schieder <mschieder@intevation.de>
parents: 17
diff changeset
63 .replace("keywords", keywords)
25
7161ce4e7ab1 The web-based display is dynamically generated.
Magnus Schieder <mschieder@intevation.de>
parents: 24
diff changeset
64 .replace("js_data_dickt", js_data_dickt)
24
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
65 .replace("var timestamp=[];", "var timestamp=[" + make_js_object_date(data_dict["date"]) + "]"))
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
66
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
67 return base_html_data
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
68
31
9aca070c86bd Add the filter 'priority' for the search by status.
Magnus Schieder <mschieder@intevation.de>
parents: 29
diff changeset
69 def compile_db_stats_html(db_file, sql_select, columns, search_parameters="", keywords="", graph=None):
24
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
70
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
71 data_dict = {"date": []}
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
72 status_list = columns.split(", ")
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
73 for x in status_list:
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
74 data_dict[x] = []
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
75
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
76 con = None
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
77 cur = None
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
78
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
79 try:
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
80 con = db.connect(db_file)
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
81 cur = con.cursor()
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
82 cur.execute(sql_select)
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
83
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
84 for row in cur.fetchall():
24
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
85 data_dict["date"].append(row[0])
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
86 for x in range(len(status_list)):
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
87 data_dict[status_list[x]].append(row[x+1])
89469aa41fe1 Preliminary work to make the display more dynamic.
Magnus Schieder <mschieder@intevation.de>
parents: 20
diff changeset
88
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
89 finally:
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
90 if cur:
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
91 cur.close()
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
92 if con:
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
93 con.close()
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
94
31
9aca070c86bd Add the filter 'priority' for the search by status.
Magnus Schieder <mschieder@intevation.de>
parents: 29
diff changeset
95 return get_webpage(data_dict, columns, search_parameters, keywords, graph)
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
96
13
149d01f43e31 Split up render_db_stats_as_html function
Gernot Schulz <gernot@intevation.de>
parents: 7
diff changeset
97 def render_webpage(content):
149d01f43e31 Split up render_db_stats_as_html function
Gernot Schulz <gernot@intevation.de>
parents: 7
diff changeset
98 for line in content.split("\n"):
149d01f43e31 Split up render_db_stats_as_html function
Gernot Schulz <gernot@intevation.de>
parents: 7
diff changeset
99 print(line)
149d01f43e31 Split up render_db_stats_as_html function
Gernot Schulz <gernot@intevation.de>
parents: 7
diff changeset
100
31
9aca070c86bd Add the filter 'priority' for the search by status.
Magnus Schieder <mschieder@intevation.de>
parents: 29
diff changeset
101 def render_db_stats_as_html(db_file, sql_select, columns, search_parameters="", keywords=""):
9aca070c86bd Add the filter 'priority' for the search by status.
Magnus Schieder <mschieder@intevation.de>
parents: 29
diff changeset
102 render_webpage(compile_db_stats_html(db_file, sql_select, columns, search_parameters, keywords))
1
2df45f6ecd81 new appereance (solid and dotted lines), resonsive layout, new legend,
sean
parents:
diff changeset
103
3
99c68ebfb3b9 Streamlined to example use.
Bernhard Reiter <bernhard@intevation.de>
parents: 2
diff changeset
104 if __name__ == '__main__':
99c68ebfb3b9 Streamlined to example use.
Bernhard Reiter <bernhard@intevation.de>
parents: 2
diff changeset
105 cgitb.enable()
7
99e2e0e17103 Adding possibility and example to filter the entries.
Bernhard Reiter <bernhard@intevation.de>
parents: 3
diff changeset
106 #spit out HTML file directly, thus no need to give headers to the server
99e2e0e17103 Adding possibility and example to filter the entries.
Bernhard Reiter <bernhard@intevation.de>
parents: 3
diff changeset
107 #print("Content-Type: text/html")
99e2e0e17103 Adding possibility and example to filter the entries.
Bernhard Reiter <bernhard@intevation.de>
parents: 3
diff changeset
108 #print()
3
99c68ebfb3b9 Streamlined to example use.
Bernhard Reiter <bernhard@intevation.de>
parents: 2
diff changeset
109
20
3bb3d9a9f1b7 Filter by keywords and states.
Magnus Schieder <mschieder@intevation.de>
parents: 17
diff changeset
110 render_db_stats_as_html("./demo3.db", rcd.SELECT_ALL)
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)