Add an script to generate patchwork stats

[Mauro Carvalho Chehab]

It could be useful to be able to see how many patches a patchwork
instance has. This small script does that.

We're using it to maintain a page with includes the generated stats at:

	https://linuxtv.org/patchwork_stats.php

It requires pandas and matplotlib to work. It assumes a database
using MySQL, but can easily changed to Postgres or to any other
DB.

Signed-off-by: Mauro Carvalho Chehab <mchehab+samsung at kernel.org>

-

PS.: By purpose, I didn't use Django here. It is a way easier for me to
write SQL commands directly than try to guess how to produce the
same results with Django. Also, I don't expect this to be applied
upstream as-is, as, if we're doing some stats module at patchwork, it 
would require a lot more: templates, css changes, customizations, etc.

The goal here is just to have something really simple that would
allow to account the number of patches received/accepted/rejected/etc
and produce graphics in a way that we could later easily add/remove
stuff.

The script takes a 2 years window and includes the current month,
but it should be easy to change it to any other period of time.

The drawback is that this script will require changes if the database
model changes. It was produced against stable 2.1 data model.

Yet, as others may want to provide similar things, I'm providing
the code we use at linuxtv.org (with the DB connection details
sanitized).


+#!/usr/bin/env python
+
+import matplotlib
+matplotlib.use('Agg')
+
+import MySQLdb
+import pandas as pd
+import matplotlib.pyplot as plt
+
+conn = MySQLdb.connect(host="localhost", user="my_patchwork_user", passwd="my_password", db="patchwork_db_name")
+cursor = conn.cursor()
+
+# Total patches
+
+df = pd.read_sql('select DATE_FORMAT(date, "%Y-%m") AS date , count(*) AS patches from patchwork_submission WHERE date between DATE_SUB(DATE_FORMAT(CURRENT_DATE,"%Y-%m-01"),INTERVAL 2 YEAR) and NOW() group by DATE_FORMAT(date, "%Y-%m") ORDER BY YEAR(date), MONTH(DATE)', con=conn)
+df['date'] = pd.to_datetime(df.date, format='%Y-%m')
+
+fig = plt.figure(figsize=(10,6), facecolor='w', edgecolor='k')
+ax = fig.add_subplot(1,1,1)
+#ax.bar(df.date, df.patches)
+ax.plot_date(df.date, df.patches, 'ob')
+ax.patch.set_facecolor('lightgrey')
+ax.set_ylim(bottom=0)
+fig.autofmt_xdate()
+ax.set_title('Number of patches received by month')
+ax.autoscale_view()
+
+plt.grid()
+plt.savefig('htdocs/static/images/patches_per_date.svg')
+
+# Patches per state
+
+df = pd.read_sql('select DATE_FORMAT(date, "%Y-%m") as date, st.name, count(*) as patches from patchwork.patchwork_patch AS p, patchwork.patchwork_submission as s, patchwork_state as st where date between DATE_SUB(DATE_FORMAT(CURRENT_DATE,"%Y-%m-01"),INTERVAL 2 YEAR) and NOW() and s.id = p.submission_ptr_id and state_id = st.id group by DATE_FORMAT(date, "%Y-%m"), state_id', con=conn)
+df['date'] = pd.to_datetime(df.date, format='%Y-%m')
+df.set_index('date', inplace=True)
+
+fig, ax = plt.subplots(figsize=(14,6))
+
+df.groupby(['name'])['patches'].plot(ax=ax, legend=True)
+ax.patch.set_facecolor('lightgrey')
+ax.set_ylim(bottom=0)
+fig.autofmt_xdate()
+ax.set_title('Number of patches per state per month')
+ax.autoscale_view()
+
+plt.grid()
+plt.legend(loc="upper left", bbox_to_anchor=(1.05,1.0))
+plt.subplots_adjust(left=0.1, bottom=0.15, right=0.8)
+plt.savefig('htdocs/static/images/patches_per_state.svg')

Thanks,
Mauro