flyspray CSV export tool

fly-build-csv

#!/bin/sh

# populate pub/ http directory with flyspray CSV export
# copy this script to /etc/cron.weekly

# csv file build name
YEAR=$(date +%Y)
CSV_FILE=export_$YEAR.csv

/usr/local/bin/flyspray2csv > /var/www/html/pub/flyspray/$CSV_FILE

flyspray2csv

#!/usr/bin/env python3

# export data from flyspray to a CSV file (on stdout)

from codecs import BOM_UTF8
import csv
from datetime import datetime
import sys
import pymysql

# some const
SQL = """
SELECT  t.task_id, p.project_title, c.category_name, s.status_name, tt.tasktype_name, 
        t.item_summary, t.detailed_desc,
        t.date_opened, t.date_closed, t.last_edited_time, 
        uo.user_name AS opened_by_user,
        ua.user_name AS assigned_user,
        COUNT(cm.comment_id) AS comments_nb
  FROM  flyspray_tasks AS t
  LEFT JOIN  flyspray_projects AS p ON t.project_id = p.project_id
  LEFT JOIN  flyspray_list_category AS c ON t.product_category = c.category_id
  LEFT JOIN  flyspray_list_status AS s ON t.item_status = s.status_id
  LEFT JOIN  flyspray_list_tasktype AS tt ON t.task_type = tt.tasktype_id
  LEFT JOIN  flyspray_users AS uo ON t.opened_by = uo.user_id
  LEFT JOIN  flyspray_assigned AS a ON t.task_id = a.task_id
  LEFT JOIN  flyspray_users AS ua ON a.user_id = ua.user_id
  LEFT JOIN  flyspray_comments AS cm ON t.task_id = cm.task_id
  GROUP BY t.task_id
  ORDER BY last_edited_time DESC LIMIT 5000
"""

CSV_FIELDS = ["task_id", "project_title", "category_name", "status_name", "tasktype_name", "opened_by_user", "last_edited_time", "date_opened", "date_closed", 
              "assigned_user", "comments_nb", "item_summary", "detailed_desc"]

# some class
class ExcelFr(csv.excel):
    delimiter = ";"

csv.register_dialect("excel-fr", ExcelFr())

# connect to DB
db = pymysql.connect(db="flyspray",
                     user="flysprayexport",
                     password="flysprayexport",
                     charset="utf8mb4",
                     cursorclass=pymysql.cursors.DictCursor)

with db.cursor() as cursor:
    # do request
    if cursor.execute(SQL):
        # print an UTF-8 BOM (for overide default charset on Excel)
        sys.stdout.write(BOM_UTF8.decode("utf8"))
        # build CSV
        w = csv.DictWriter(sys.stdout, fieldnames=CSV_FIELDS,
                           extrasaction="ignore", dialect="excel-fr")
        # add header line
        w.writeheader()
        # add lines
        for d in cursor.fetchall():
            # update datetime fields (timestamp -> str date)
            for (k, v) in d.items():
                if k.startswith("date_") or k.endswith("_time"):
                    ts = int(v)
                    if ts > 0:
                        d[k] = datetime.utcfromtimestamp(ts).strftime("%Y-%m-%d %H:%M:%S")
                    else:
                        d[k] = ""
            # add current line to CSV
            w.writerow(d)