sean@1: #!/usr/bin/env python3
sean@1: 
sean@1: """ Fetch issues from a roundup-tracker and save them in a databse.
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>
sean@1: 
sean@1: (c) 2010,2015 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: ##
sean@1: 
sean@1: BASE_URL_DEMO = "http://localhost:8917/demo/"
sean@1: 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"
sean@1: 
sean@1: LOGIN_PARAMETERS_DEMO = (
sean@1:     ("__login_name", "demo"),
sean@1:     ("__login_password", "demo"),
sean@1:     ("@action", "Login"),
sean@1:     )
sean@1: 
sean@1: save_stats_in_db(LOGIN_PARAMETERS_DEMO, BASE_URL_DEMO, rcd.DATABASE_DEMO, rcd.COLUMNS, rcd.CREATE_DB, rcd.INSERT_NEW, SEARCH_URL_DEMO)
sean@1: """
sean@1: 
sean@1: import http.cookiejar
sean@1: import urllib.parse
sean@1: import urllib.request
sean@1: import csv
sean@1: import io
sean@1: import sqlite3 as db
sean@1: import os
sean@1: import roundup_content_data as rcd
sean@1: 
sean@1: 
sean@1: CHECK_ROUNDUP_ORDER = "priority?@action=export_csv&@columns=id,order"
sean@1: CHECK_ROUNDUP_SEARCH_VALUES = "status?@action=export_csv&@columns=id&@filter=open&open=1"
sean@1: SEARCH_ROUNDUP = "issue?@action=export_csv&@columns=priority&@filter=status&@pagesize=500&@startwith=0&status=-1,{search_values}"
sean@1: 
sean@1: 
sean@1: def connect_to_server(params, baseurl):
sean@1:     enc_data = urllib.parse.urlencode(params).encode()
sean@1:     cj = http.cookiejar.CookieJar()
sean@1:     opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(cj))
sean@1:     req = urllib.request.Request(url=baseurl, data=enc_data)
sean@1:     opener.open(req)
sean@1:     return opener
sean@1: 
sean@1: 
sean@1: def get_csv_from_server(opener, roundup_url, sub_url):
sean@1:     csv_req = urllib.request.Request(url=roundup_url+sub_url)
sean@1:     f = opener.open(csv_req)
sean@1:     csv_reader = csv.DictReader(io.TextIOWrapper(f))
sean@1:     return csv_reader
sean@1: 
sean@1: 
sean@1: def set_search_paramters_on_URL(url, search_param_csv):
sean@1: 
sean@1:     id_list = []
sean@1: 
sean@1:     for row in search_param_csv:
sean@1:         id_list.append(row["id"])
sean@1: 
sean@1:     new_url = url.format(search_values = ",".join(id_list))
sean@1: 
sean@1:     return new_url
sean@1: 
sean@1: 
sean@1: def check_create_database(database_file, sql_create_db):
sean@1:     if not os.path.isfile(database_file):
sean@1:         con = None
sean@1:         cur = None
sean@1:         try:
sean@1:             con = db.connect(database_file)
sean@1:             cur = con.cursor()
sean@1:             try:
sean@1:                 cur.execute(sql_create_db)
sean@1:                 con.commit()
sean@1:                 os.chmod(database_file, 0o644)
sean@1:             except:
sean@1:                 con.rollback()
sean@1:                 raise
sean@1:         finally:
sean@1:             if cur:
sean@1:                 cur.close()
sean@1:             if con:
sean@1:                 con.close()
sean@1: 
sean@1: 
sean@1: def issues_to_quantities(issue_csv, columns, orders_csv):
sean@1: 
sean@1:     quantities = [0] * len(columns)
sean@1:     order_dict = {}
sean@1: 
sean@1:     #convert the csv-dict reader to real dict
sean@1:     for row in orders_csv:
sean@1:         order_dict[row["id"]] = int(float(row["order"])) # int(float()) because the order-value is indeed "1.0, 2.0" etc
sean@1: 
sean@1:     for issue in issue_csv:
sean@1:         priority = issue["priority"]
sean@1: 
sean@2:         if priority.isdigit() == True :
sean@1:             quantities[order_dict[priority] -1 ] += 1
sean@1: 
sean@1:     # print("quantities : " + str(quantities))
sean@1: 
sean@1:     return quantities
sean@1: 
sean@1: 
sean@1: def save_issues_to_db(quantities, database_file, sql_create_db, sql_insert_in_db):
sean@1:     check_create_database(database_file, sql_create_db)
sean@1: 
sean@1:     cur = None
sean@1:     con = None
sean@1: 
sean@1:     try:
sean@1:         con = db.connect(database_file)
sean@1:         cur = con.cursor()
sean@1:         try:
sean@1:             cur.execute(sql_insert_in_db, quantities)
sean@1:             con.commit()
sean@1:         except:
sean@1:             con.rollback()
sean@1:             raise
sean@1:     finally:
sean@1:         if cur:
sean@1:             cur.close()
sean@1:         if con:
sean@1:             con.close()
sean@1: 
sean@1: 
sean@1: def save_stats_in_db(login_parmeters, baseurl, db_file, columns, sql_create_db, sql_insert_in_db, searchurl=False):
sean@1:     try:
sean@1: 
sean@1:         opener = connect_to_server(login_parmeters, baseurl)
sean@1: 
sean@1:         search_operators_csv = get_csv_from_server(opener, baseurl, CHECK_ROUNDUP_SEARCH_VALUES)
sean@1:         order_csv = get_csv_from_server(opener, baseurl, CHECK_ROUNDUP_ORDER)
sean@1: 
sean@1:         if searchurl == False:
sean@1:             formated_search_url = set_search_paramters_on_URL(SEARCH_ROUNDUP, search_operators_csv)
sean@1:         else:
sean@1:             formated_search_url = searchurl
sean@1: 
sean@1:         current_issues_csv = get_csv_from_server(opener, baseurl, formated_search_url)
sean@1: 
sean@1:         opener.close()
sean@1: 
sean@1:         quantities = issues_to_quantities(current_issues_csv, columns, order_csv)
sean@1: 
sean@2:         # save_issues_to_db(quantities, db_file, sql_create_db, sql_insert_in_db)
sean@1: 
sean@1:     except urllib.error.URLError as e:
sean@1:         print("No Valid Connection to server : " + baseurl + "\nerror: " + str(e))
sean@1: 
sean@1: 
sean@1: 
sean@1: 
sean@1: 
sean@1: