changeset 1:2df45f6ecd81

new appereance (solid and dotted lines), resonsive layout, new legend, new structure, cronjob-friendly dynamic generation of search-strings, dynamic recognition of error-values, ignores non-numeric priority-IDs
author sean
date Tue, 14 Apr 2015 13:32:12 +0200
parents 3f139db894f1
children 3e9f4a6803d1
files .hgignore collect_issues.py display_issues.py graph.html roundup_content_data/__init__.py
diffstat 5 files changed, 380 insertions(+), 66 deletions(-) [+]
line wrap: on
line diff
--- a/.hgignore	Thu Apr 02 09:51:19 2015 +0200
+++ b/.hgignore	Tue Apr 14 13:32:12 2015 +0200
@@ -6,5 +6,7 @@
 *.pyc
 *.pyo
 *.swp
-test.db
+*.db
 __pycache__
+collect_data*
+display_issues_*
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/collect_issues.py	Tue Apr 14 13:32:12 2015 +0200
@@ -0,0 +1,177 @@
+#!/usr/bin/env python3
+
+""" Fetch issues from a roundup-tracker and save them in a databse.
+
+author: Sascha L. Teichmann <sascha.teichmann@intevation.de>
+author: Bernhard Reiter <bernhard@intevation.de>
+author: Sean Engelhardt <sean.engelhardt@intevation.de>
+
+(c) 2010,2015 by Intevation GmbH
+
+This is Free Software unter the terms of the
+GNU GENERAL PUBLIC LICENSE Version 3 or later.
+See http://www.gnu.org/licenses/gpl-3.0.txt for details
+
+
+##USAGE EXAMPLE: ##
+
+BASE_URL_DEMO = "http://localhost:8917/demo/"
+SEARCH_URL_DEMO = "issue?@action=export_csv&@columns=title,priority&@filter=status&@pagesize=50&@startwith=0&status=-1,1,2,3,4,5,6,7"
+
+LOGIN_PARAMETERS_DEMO = (
+    ("__login_name", "demo"),
+    ("__login_password", "demo"),
+    ("@action", "Login"),
+    )
+
+save_stats_in_db(LOGIN_PARAMETERS_DEMO, BASE_URL_DEMO, rcd.DATABASE_DEMO, rcd.COLUMNS, rcd.CREATE_DB, rcd.INSERT_NEW, SEARCH_URL_DEMO)
+
+"""
+
+import http.cookiejar
+import urllib.parse
+import urllib.request
+import csv
+import io
+import sqlite3 as db
+import os
+import roundup_content_data as rcd
+
+
+CHECK_ROUNDUP_ORDER = "priority?@action=export_csv&@columns=id,order"
+CHECK_ROUNDUP_SEARCH_VALUES = "status?@action=export_csv&@columns=id&@filter=open&open=1"
+SEARCH_ROUNDUP = "issue?@action=export_csv&@columns=priority&@filter=status&@pagesize=500&@startwith=0&status=-1,{search_values}"
+
+
+
+
+
+
+def connect_to_server(params, baseurl):
+    enc_data = urllib.parse.urlencode(params).encode()
+    cj = http.cookiejar.CookieJar()
+    opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(cj))
+    req = urllib.request.Request(url=baseurl, data=enc_data)
+    opener.open(req)
+    return opener
+
+
+def get_csv_from_server(opener, roundup_url, sub_url):
+    csv_req = urllib.request.Request(url=roundup_url+sub_url)
+    f = opener.open(csv_req)
+    csv_reader = csv.DictReader(io.TextIOWrapper(f))
+    return csv_reader
+
+
+def set_search_paramters_on_URL(url, search_param_csv):
+
+    id_list = []
+
+    for row in search_param_csv:
+        id_list.append(row["id"])
+
+    new_url = url.format(search_values = ",".join(id_list))
+
+    return new_url
+
+
+def check_create_database(database_file, sql_create_db):
+    if not os.path.isfile(database_file):
+        con = None
+        cur = None
+        try:
+            con = db.connect(database_file)
+            cur = con.cursor()
+            try:
+                cur.execute(sql_create_db)
+                con.commit()
+                os.chmod(database_file, 0o644)
+            except:
+                con.rollback()
+                raise
+        finally:
+            if cur:
+                cur.close()
+            if con:
+                con.close()
+
+
+def represents_int(s):
+    try:
+        int(s)
+        return True
+    except ValueError:
+        return False
+
+
+def issues_to_quantities(issue_csv, columns, orders_csv):
+
+    quantities = [0] * len(columns)
+    order_dict = {}
+
+    #convert the csv-dict reader to real dict
+    for row in orders_csv:
+        order_dict[row["id"]] = int(float(row["order"])) # int(float()) because the order-value is indeed "1.0, 2.0" etc
+
+    for issue in issue_csv:
+        priority = issue["priority"]
+
+        if represents_int(priority) == True :
+            quantities[order_dict[priority] -1 ] += 1
+
+    # print("quantities : " + str(quantities))
+
+    return quantities
+
+
+def save_issues_to_db(quantities, database_file, sql_create_db, sql_insert_in_db):
+    check_create_database(database_file, sql_create_db)
+
+    cur = None
+    con = None
+
+    try:
+        con = db.connect(database_file)
+        cur = con.cursor()
+        try:
+            cur.execute(sql_insert_in_db, quantities)
+            con.commit()
+        except:
+            con.rollback()
+            raise
+    finally:
+        if cur:
+            cur.close()
+        if con:
+            con.close()
+
+
+def save_stats_in_db(login_parmeters, baseurl, db_file, columns, sql_create_db, sql_insert_in_db, searchurl=False):
+    try:
+
+        opener = connect_to_server(login_parmeters, baseurl)
+
+        search_operators_csv = get_csv_from_server(opener, baseurl, CHECK_ROUNDUP_SEARCH_VALUES)
+        order_csv = get_csv_from_server(opener, baseurl, CHECK_ROUNDUP_ORDER)
+
+        if searchurl == False:
+            formated_search_url = set_search_paramters_on_URL(SEARCH_ROUNDUP, search_operators_csv)
+        else:
+            formated_search_url = searchurl
+
+        current_issues_csv = get_csv_from_server(opener, baseurl, formated_search_url)
+
+        opener.close()
+
+        quantities = issues_to_quantities(current_issues_csv, columns, order_csv)
+
+        save_issues_to_db(quantities, db_file, sql_create_db, sql_insert_in_db)
+
+    except urllib.error.URLError as e:
+        print("No Valid Connection to server : " + baseurl + "\nerror: " + str(e))
+
+
+
+
+
+
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/display_issues.py	Tue Apr 14 13:32:12 2015 +0200
@@ -0,0 +1,94 @@
+#!/usr/bin/env python3
+
+""" Display previously saved issues from a database on webpage via CGI.
+
+author: Sascha L. Teichmann <sascha.teichmann@intevation.de>
+author: Bernhard Reiter <bernhard@intevation.de>
+author: Sean Engelhardt <sean.engelhardt@intevation.de>
+
+(c) 2010,2015 by Intevation GmbH
+
+This is Free Software unter the terms of the
+GNU GENERAL PUBLIC LICENSE Version 3 or later.
+See http://www.gnu.org/licenses/gpl-3.0.txt for details
+
+
+##Usage Example: ##
+
+render_db_stats_as_html(rcd.DATABASE_DEMO, rcd.SELECT_ALL)
+
+"""
+
+import sqlite3 as db
+import cgitb
+import roundup_content_data as rcd
+
+
+def make_js_object_string(array):
+    formated = []
+
+    for item in array:
+        formated.append("{points: " + str(item) + "}")
+
+    return ",".join(formated)
+
+
+def make_js_object_date(array):
+    formated = []
+
+    for item in array:
+        formated.append("{date : new Date('" + str(item) + "')}")
+
+    return ", ".join(formated)
+
+
+def get_webpage():
+
+    with open("graph.html", "r") as html_chart_file:
+        base_html_data = html_chart_file.read()
+
+    base_html_data = (base_html_data
+        .replace("var critical=[];", "var critical=[" + make_js_object_string(rcd.data_dict["critical"]) + "]")
+        .replace("var urgent=[];", "var urgent=[" + make_js_object_string(rcd.data_dict["urgent"]) + "]")
+        .replace("var bug=[];", "var bug=[" + make_js_object_string(rcd.data_dict["bug"]) + "]")
+        .replace("var feature=[];", "var feature=[" + make_js_object_string(rcd.data_dict["feature"]) + "]")
+        .replace("var wish=[];", "var wish=[" + make_js_object_string(rcd.data_dict["wish"]) + "]")
+        .replace("var timestamp=[];", "var timestamp=[" + make_js_object_date(rcd.data_dict["date"]) + "]"))
+
+    return base_html_data
+
+
+def render_webpage(content):
+    for line in content.split("\n"):
+        print(line)
+
+
+def render_db_stats_as_html(db_file, sql_select):
+
+    con = None
+    cur = None
+
+    try:
+        con = db.connect(db_file)
+        cur = con.cursor()
+        cur.execute(sql_select)
+
+        for row in cur.fetchall():
+            rcd.data_dict["date"].append(row[0])
+            rcd.data_dict["critical"].append(row[1])
+            rcd.data_dict["urgent"].append(row[2])
+            rcd.data_dict["bug"].append(row[3])
+            rcd.data_dict["feature"].append(row[4])
+            rcd.data_dict["wish"].append(row[5])
+    finally:
+        if cur:
+            cur.close()
+        if con:
+            con.close()
+
+    render_webpage(get_webpage())
+
+
+cgitb.enable()
+print("Content-Type: text/html")
+print()
\ No newline at end of file
--- a/graph.html	Thu Apr 02 09:51:19 2015 +0200
+++ b/graph.html	Tue Apr 14 13:32:12 2015 +0200
@@ -3,8 +3,14 @@
 	<head>
 		<title> Issues </title>
 		<style type = text/css>
+
+			* {
+				font-family: "Sans-serif";
+				font-size: 14px;
+			}
+
 			.svg div{
-					font: 10px sans-serif;
+					font: 10px;
 					text-align: right;
 					float: left;
 					display: block;
@@ -23,7 +29,8 @@
 
 			.line {
 					fill: none;
-					stroke-width: 2px;
+					stroke-width: 3px;
+					opacity: 1;
 			}
 
 			.line.critical {
@@ -51,11 +58,12 @@
 			}
 
 			.line.feature {
-					stroke: green;
+					stroke: chartreuse;
+					style: stroke-dasharray;
 			}
 
 			.line.feature.legend {
-					fill: green;
+					fill: chartreuse;
 			}
 
 			.line.wish {
@@ -75,21 +83,17 @@
 					stroke-width: 0;
 			}
 
-			.title {
-            	font: 15px sans-serif;
-            }
-
-			.legend{
-				font: 15px sans-serif;
-			}
-
 		</style>
 	</head>
 	<body>
-		<div id="burndown_chart"></div>
+		<div id="content" style="display: inline-block"></div>
 		<script type="text/javascript" src="d3.v3.min.js"></script>
 		<script type="text/javascript">
 
+			window.onresize = function(){
+				document.getElementById("content").innerHTML = "";
+				makeChart();
+			};
 
 			var critical=[];
 			var urgent=[];
@@ -98,6 +102,8 @@
 			var wish=[];
 			var timestamp=[];
 
+
+
 			function assignIssueToDate(issueArray, dateArray){
 				a = [];
 				for (var i = 0; i < issueArray.length; i++) {
@@ -107,6 +113,13 @@
 				return a;
 			}
 
+			function limitDatesOnXAxis(limit){
+				if ( timestamp.length < limit ){
+					return timestamp.length;
+				} else {
+					return limit;
+				}
+			}
 
 			function maxInObject( array ){
 				var maxVal = 0;
@@ -154,39 +167,65 @@
 
 
 			//append a svg_path. pretty generic
-			function draw_line(svg, data_array, css_class, line_object){
+			function draw_line(svg, data_array, css_class, line_object, lineShape){
 				svg.append("path")
 					.datum(assignIssueToDate(data_array, timestamp))
 					.attr("class", css_class)
+					.style("stroke-dasharray", (lineShape))
 					.attr("d", line_object);
 			}
 
 
-			//helper for the legend
-			function draw_legend_line(svg, width, Ypos, text){
-				svg.append("svg:text")
-					.attr("class", "legend")
-					.attr("x", width+50)
-					.attr("y", Ypos)
-					.text(text);
+			function makeLegend(svg, width){
 
-				svg.append("rect")
-					.attr("class", "line " + text.toLowerCase() + " legend")
-					.attr("x", width+30)
-					.attr("y", Ypos-12)
-					.attr("width", 15)
-					.attr("height", 15);
+				var legend_distance = width+40;
+				var top_distance = 20;
+				var distance_steps = 50;
+
+
+				function set_propper_distance(steps){
+					top_distance += steps;
+					return top_distance;
+				}
+
+				function draw_legend_line(svg, width, Ypos, text, issues){
+					svg.append("svg:text")
+						.attr("class", "legend")
+						.attr("x", width-30	)
+						.attr("y", Ypos)
+						.text(text + ":");
+
+					svg.append("svg:text")
+						.attr("class", "legend")
+						.attr("x", width+35	)
+						.attr("y", Ypos)
+						.text(issues);
+
+					svg.append("rect")
+						.attr("class", "line " + text.toLowerCase() + " legend")
+						.attr("x", width-30)
+						.attr("y", Ypos-20)
+						.attr("width", 100)
+						.attr("height", 2);
+				}
+
+				draw_legend_line(svg, legend_distance, set_propper_distance(distance_steps), "Critical", critical[critical.length-1].points);
+				draw_legend_line(svg, legend_distance, set_propper_distance(distance_steps), "Urgent", urgent[urgent.length-1].points);
+				draw_legend_line(svg, legend_distance, set_propper_distance(distance_steps), "Bug", bug[bug.length-1].points);
+				draw_legend_line(svg, legend_distance, set_propper_distance(distance_steps), "Feature", feature[feature.length-1].points);
+				draw_legend_line(svg, legend_distance, set_propper_distance(distance_steps), "Wish", wish[wish.length-1].points);
 			}
 
 
+
 			//draw the chart
 			function makeChart(){
 
 				//declaration
 				var sizeOfSystemBorders = 50;
-				var margin = {top: 20, right: 200, bottom: 200, left: 65},
+				var margin = {top: 20, right: 100, bottom: 90, left: 60},
 					width = (document.documentElement.clientWidth-sizeOfSystemBorders) - margin.left - margin.right,
-					height = (document.documentElement.clientHeight-sizeOfSystemBorders) - (margin.top) - margin.bottom;
+					height = (document.documentElement.clientHeight-sizeOfSystemBorders) - margin.top - margin.bottom;
 
 				var x = d3.time.scale()
 					.range([0, width]);
@@ -198,11 +237,6 @@
 					.x(function(d) { return x(d.date); })
 					.y(function(d) { return y(d.points); });
 
-				var color_hash = {  0 : ["apple", "green"],
-					    1 : ["mango", "orange"],
-					    2 : ["cherry", "red"]
-				};
-
 				//lines
 				var criticalLine = base_line;
 				var urgentLine = base_line;
@@ -213,7 +247,6 @@
 
 
 				//set domain of y axis
-
 				var yDomain = [	];
 				yDomain[0] = 0;
 				yDomain[1] = getMaxIssues();
@@ -226,7 +259,10 @@
 				var xAxis = d3.svg.axis()
 					.scale(x)
 					.orient("bottom")
-					.tickFormat(d3.time.format.iso);
+					.ticks(limitDatesOnXAxis(10))
+					.tickFormat(d3.time.format("%d.%m:%H:%M"));
+					// .tickFormat(d3.time.format("%X"));
+					// .tickFormat(d3.time.format.iso);
 
 
 				var yAxis = d3.svg.axis()
@@ -234,7 +270,7 @@
 					.orient("left");
 
 
-				var svg = d3.select("body")
+				var svg = d3.select("#content")
 					.append("svg")
 					.attr("class", "svg")
 					.attr("width", width + margin.left + margin.right)
@@ -259,7 +295,7 @@
 				// Draw the y Grid lines
 				svg.append("g")
 					.attr("class", "grid")
-					.call(makeGrid(y, "left", getMaxIssues())
+					.call(makeGrid(y, "left", function(){return Math.min(getMaxIssues(), 50);}())
 						.tickSize(-width, 0, 0)
 						.tickFormat("")
 					);
@@ -272,8 +308,8 @@
 					.call(xAxis)
 					.selectAll("text")
 					.style("text-anchor", "end")
-					.attr("dx", "-.8em")
-					.attr("dy", ".15em")
+					.attr("dx", "-.5em")
+					.attr("dy", ".1em")
 					.attr("transform", function() {
 						return "rotate(-65)";
 					});
@@ -303,24 +339,21 @@
 				//Titel und Legende
 
 				svg.append("svg:text")
-					.attr("class", "title")
+					.attr("class", "text")
 					.attr("x", 10)
 					.attr("y", -5)
 					.text("Issues Nach Zeit");
 
 
-				draw_legend_line(svg, width, 50, "Critical");
-				draw_legend_line(svg, width, 70, "Urgent");
-				draw_legend_line(svg, width, 90, "Bug");
-				draw_legend_line(svg, width, 110, "Feature");
-				draw_legend_line(svg, width, 130, "Wish");
+				draw_line(svg, wish, "line wish", wishLine, "0, 0");
+				draw_line(svg, feature, "line feature", featureLine, "3, 3");
+				draw_line(svg, bug, "line bug", bugLine, "7, 7");
+				draw_line(svg, urgent, "line urgent", urgentLine, "13, 13");
+				draw_line(svg, critical, "line critical", criticalLine, "17, 17");
 
 
-				draw_line(svg, critical, "line critical", criticalLine);
-				draw_line(svg, urgent, "line urgent", urgentLine);
-				draw_line(svg, bug, "line bug", bugLine);
-				draw_line(svg, feature, "line feature", featureLine);
-				draw_line(svg, wish, "line wish", wishLine);
+				makeLegend(svg, width);
+
 
 			}
 
--- a/roundup_content_data/__init__.py	Thu Apr 02 09:51:19 2015 +0200
+++ b/roundup_content_data/__init__.py	Tue Apr 14 13:32:12 2015 +0200
@@ -16,23 +16,17 @@
 
 import os
 
-#rather use a real database for productiv use.
-#this database NEEDS to chmod "777" or "666", wich is a major security issue
-DATABASE_FILE = os.path.dirname(os.path.realpath(__file__)) + "/test.db"
+#Add desired sqlite databases here
+DATABASE_REFERENCCE = os.path.dirname(os.path.realpath(__file__)) + "/test_reference.db"
+DATABASE_DEMO = os.path.dirname(os.path.realpath(__file__)) + "/demo.db"
+DATABASE_ERRORDB = os.path.dirname(os.path.realpath(__file__)) + "/errordatabase.db"
+DATABASE_TECH_INTERN = os.path.dirname(os.path.realpath(__file__)) + "/tech_intern.db"
+DATABASE_INT_TEST = os.path.dirname(os.path.realpath(__file__)) + "/int_test.db"
 
-COLUMNS = [
+COLUMNS= [
     "critical", "urgent", "bug", "feature", "wish",
 ]
 
-
-# types of errors
-CRITICAL = 1
-URGENT = 2
-BUG = 3
-FEATURE = 4
-WISH = 5
-
-
 data_dict = {
     "date": [],
     "critical": [],
@@ -42,8 +36,9 @@
     "wish": []
 }
 
+#SQL
 
-#SQL
+#DEMO System
 SELECT_ALL = """
 SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp),
     critical,
@@ -71,4 +66,17 @@
 INSERT_NEW = """
     INSERT INTO issues (critical, urgent, bug, feature, wish)
     VALUES (?, ?, ?, ?, ?)
+"""
+
+#Referecen DB:
+SELECT_ALL_REFERENCE = """
+SELECT strftime("%Y-%m-%dT%H:%M:%S", sample_time),
+    critical,
+    major,
+    crash,
+    normal,
+    minor,
+    wishlist
+FROM issues
+ORDER BY sample_time
 """
\ No newline at end of file
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)