sean@0: #!/usr/bin/env python3
sean@0: 
sean@0: """ Fetch issues from a roundup-tracker and save them in a databse.
sean@0: 
sean@0: author: Sascha L. Teichmann <sascha.teichmann@intevation.de>
sean@0: author: Bernhard Reiter <bernhard@intevation.de>
sean@0: author: Sean Engelhardt <sean.engelhardt@intevation.de>
sean@0: 
sean@0: (c) 2010,2015 by Intevation GmbH
sean@0: 
sean@0: This is Free Software unter the terms of the
sean@0: GNU GENERAL PUBLIC LICENSE Version 3 or later.
sean@0: See http://www.gnu.org/licenses/gpl-3.0.txt for details
sean@0: """
sean@0: 
sean@0: import http.cookiejar
sean@0: import urllib.parse
sean@0: import urllib.request
sean@0: import csv
sean@0: import io
sean@0: import sqlite3 as db
sean@0: import os
sean@0: import roundup_content_data as rcd
sean@0: 
sean@0: 
sean@0: # Types of URLS
sean@0: BASE_URL = "http://10.42.7.199:8917/demo/"
sean@0: SEARCH_URL = "issue?@action=export_csv&@columns=title,priority&@filter=status&@pagesize=50&@startwith=0&status=-1,1,2,3,4,5,6,7"
sean@0: REPORT_URL = BASE_URL + SEARCH_URL
sean@0: 
sean@0: 
sean@0: LOGIN_PARAMETERS = (
sean@0:     ("__login_name", "demo"),
sean@0:     ("__login_password", "demo"),
sean@0:     ("@action", "Login"),
sean@0:     )
sean@0: 
sean@0: 
sean@0: # SQL-COMMANDS
sean@0: 
sean@0: 
sean@0: def connect_to_server():
sean@0:     enc_data = urllib.parse.urlencode(LOGIN_PARAMETERS).encode()
sean@0:     cj = http.cookiejar.CookieJar()
sean@0:     opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(cj))
sean@0:     req = urllib.request.Request(url=BASE_URL, data=enc_data)
sean@0:     opener.open(req)
sean@0:     return opener
sean@0: 
sean@0: 
sean@0: def get_issues_as_csv(opener):
sean@0:     csv_req = urllib.request.Request(url=REPORT_URL)
sean@0:     f = opener.open(csv_req)
sean@0: 
sean@0:     reader = csv.DictReader(io.TextIOWrapper(f))
sean@0: 
sean@0:     return reader
sean@0: 
sean@0: 
sean@0: def check_create_database(database_file):
sean@0:     if not os.path.isfile(database_file):
sean@0:         con = None
sean@0:         cur = None
sean@0:         try:
sean@0:             con = db.connect(database_file)
sean@0:             cur = con.cursor()
sean@0:             try:
sean@0:                 cur.execute(rcd.CREATE_DB)
sean@0:                 con.commit()
sean@0:                 os.chmod(rcd.DATABASE_FILE, 0o744)
sean@0:             except:
sean@0:                 con.rollback()
sean@0:                 raise
sean@0:         finally:
sean@0:             if cur:
sean@0:                 cur.close()
sean@0:             if con:
sean@0:                 con.close()
sean@0: 
sean@0: 
sean@0: def issues_to_quantities(rows):
sean@0:     quantities = [0] * len(rcd.COLUMNS)
sean@0:     for row in rows:
sean@0:         quantities[int(row["priority"]) - 1] += 1
sean@0:     return quantities
sean@0: 
sean@0: 
sean@0: def save_issues_to_db(quantities, database_file):
sean@0:     check_create_database(database_file)
sean@0: 
sean@0:     cur = None
sean@0:     con = None
sean@0: 
sean@0:     try:
sean@0:         con = db.connect(database_file)
sean@0:         cur = con.cursor()
sean@0:         try:
sean@0:             cur.execute(rcd.INSERT_NEW, quantities)
sean@0:             con.commit()
sean@0:         except:
sean@0:             con.rollback()
sean@0:             raise
sean@0:     finally:
sean@0:         if cur:
sean@0:             cur.close()
sean@0:         if con:
sean@0:             con.close()
sean@0: 
sean@0: 
sean@0: def save_stats_in_db():
sean@0:     try:
sean@0:         opener = connect_to_server()
sean@0:         current_issues_csv = get_issues_as_csv(opener)
sean@0:         opener.close()
sean@0: 
sean@0:         quantities = issues_to_quantities(current_issues_csv)
sean@0: 
sean@0:         save_issues_to_db(quantities, rcd.DATABASE_FILE)
sean@0:     except urllib.error.URLError:
sean@0:         print("No Valid Connection to server : " + BASE_URL)
sean@0: 
sean@0: 
sean@0: save_stats_in_db()