%pylab inline

from vizgrimoire.metrics.query_builder import SCRQuery
from vizgrimoire.metrics.metrics_filter import MetricFilters
import vizgrimoire.metrics.scr_metrics as scr

#database object
user="root"
password=""
scr_db = "wikimedia_gerrit_20150224"
identities_db ="wikimedia_git_20150224"
people_out = ["Translation updater bot", "", "", ""]
dbcon=SCRQuery(user, password, scr_db, identities_db)

period = MetricFilters.PERIOD_MONTH
startdate = "'2013-01-01'"
enddate = "'2015-03-01'"
filters = MetricFilters(period, startdate, enddate)

participants = scr.Participants(dbcon, filters)

participants.get_agg()

participants_ts = participants.get_ts()

from datetime import datetime
dates = []
unixtime = participants_ts["unixtime"]
for i in unixtime:
    dates.append(datetime.fromtimestamp(float(i)))

fig = plt.figure()
plot(dates, ts["participants"])
fig.autofmt_xdate()

list_participants = participants.get_list()

#Adding companies affiliation
query = """
SELECT u.identifier , c.name as company, count(*) as events 
FROM ((SELECT comments.submitted_on as submitted_on , comments.submitted_by as submitted_by 
       FROM comments 
       WHERE comments.submitted_on>='2015-01-24' AND comments.submitted_on<'2015-02-24') union 
      (SELECT ch.changed_by as submitted_by , ch.changed_on as submitted_on 
       FROM changes ch 
       WHERE ch.changed_on>='2015-01-24' AND ch.changed_on<'2015-02-24') union 
      (SELECT i.submitted_on as submitted_on , i.submitted_by as submitted_by 
       FROM issues i 
       WHERE i.submitted_on>='2015-01-24' AND i.submitted_on<'2015-02-24')) t , 
      people_upeople pup , wikimedia_git_20150224.upeople u,
      wikimedia_git_20150224.upeople_companies upc, wikimedia_git_20150224.companies c
WHERE t.submitted_on>='2015-01-24' AND t.submitted_on<'2015-02-24' AND pup.upeople_id = u.id and 
      t.submitted_by = pup.people_id and u.id = upc.upeople_id and upc.company_id = c.id
GROUP by u.identifier      
ORDER by count(*) desc 
"""
list_affs = participants.db.ExecuteQuery(query)

for i in range(0,len(list_affs["identifier"])):
    identifier = list_affs["identifier"][i]
    company = list_affs["company"][i]
    events = list_affs["events"][i]
    print identifier.split('@')[0] + "," + company + "," + str(events)