# -*- coding: utf-8 -*-
# <nbformat>3.0</nbformat>

# <codecell>
# Code developed in Ipython Notebook. Reason why there are "codecells" around in the Python code.

%pylab inline

# <codecell>

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

# <codecell>

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)

# <codecell>

period = MetricFilters.PERIOD_MONTH
startdate="'2013-01-01'"
enddate="'2015-02-01'"

filters = MetricFilters(period, startdate, enddate, ["autoreviews", "fake"])

# <codecell>

reviewers = scr.ActiveCoreReviewers(dbcon, filters)

# <codecell>

reviewers.get_agg()
#get query: reviewers._get_sql(False)

# <codecell>

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

# <codecell>

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

# <codecell>

period = MetricFilters.PERIOD_MONTH
startdate="'2015-01-24'"
enddate="'2015-02-24'"

filters = MetricFilters(period, startdate, enddate, ["autoreviews", "fake"])
reviewers = scr.ActiveCoreReviewers(dbcon, filters)

    

# <codecell>

list_reviewers = reviewers.get_list()

# <codecell>

#Now adding to the query the affiliation
query = """select up.id as id , up.identifier as identifier , c.name as company, count(distinct(ch.id)) as reviews
           from issues_ext_gerrit ieg , issues i , changes ch , 
                wikimedia_git_20150224.upeople up , people_upeople pup, 
                wikimedia_git_20150224.upeople_companies upc, wikimedia_git_20150224.companies c
           where field = 'Code-Review' and changed_on < '2015-02-24' and ieg.issue_id = i.id and changed_on >= '2015-01-24' and 
                 ch.issue_id = i.id and pup.upeople_id = up.id and (ch.new_value = -2 or ch.new_value = 2) and 
                 ch.changed_by  = pup.people_id and ieg.branch like '%master%' and 
                 up.id = upc.upeople_id and upc.company_id = c.id and
                 i.id not in
                       (select distinct(i.id) as issue_id
                       from issues i,
                            changes ch
                       where i.submitted_by = ch.changed_by and
                             i.id = ch.issue_id and
                             ch.field = 'status' and
                             ch.new_value = 'MERGED')
                     group by up.id, up.identifier order by count(distinct(ch.id)) desc 
        """
list_affs = reviewers.db.ExecuteQuery(query)

# <codecell>

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

# <codecell>

startdate = "'2013-01-01'"
enddate = "'2015-02-01'"
filters = MetricFilters(period, startdate, enddate)
participants = scr.Participants(dbcon, filters)

# <codecell>

participants.get_agg()

# <codecell>

ts= participants.get_ts()

# <codecell>

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

# <codecell>

startdate = "'2015-01-24'"
enddate = "'2015-02-24'"
filters = MetricFilters(period, startdate, enddate)
participants = scr.Participants(dbcon, filters)
list_participants = participants.get_list()

# <codecell>

#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)

# <codecell>

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)

# <codecell>