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

#metrics filter object
period = MetricFilters.PERIOD_MONTH
startdate="'2013-01-01'"
enddate="'2015-03-01'"

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


reviewers = scr.ActiveCoreReviewers(dbcon, filters)

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

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

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

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

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

    

list_reviewers = reviewers.get_list()

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

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)