library("RMySQL")

mydb = dbConnect(MySQL(), user='root', password='', dbname='dic_bicho_openstack_gerrit_1739', host='localhost')

# Evolution of Submitters
query = "select concat(cast(year(submitted_on) as char), '-', cast(month(submitted_on) as char)) as date, count(distinct(submitted_by)) as submitters 
         from issues group by year(submitted_on), month(submitted_on)"
print(query)
rs = dbSendQuery(mydb, query)
data1 = fetch(rs, n=-1)
plot(data1$submitters, xaxt="n",  ylab="", xlab="", type="l")
#axis(1, at=data1$submitters, labels=data1$date)

# Evolution of code reviewers
query = " select year(changed_on), month(changed_on), count(distinct(changed_by)) as reviewers
          from changes where field='CRVW' group by year(changed_on), month(changed_on)"
rs = dbSendQuery(mydb, query)
data2 = fetch(rs, n=-1)
lines(data2$reviewers, col="red")

# Evolution of core reviewers
query =" select year(changed_on), month(changed_on), count(distinct(changed_by)) as core_reviewers
          from changes where field='CRVW' and new_value=2 or new_value=-2 group by year(changed_on), month(changed_on)"
rs = dbSendQuery(mydb, query)
data22 = fetch(rs, n=-1)
lines(data22$core_reviewers, col="blue")

#Longest current reviews waiting to be fixed
query = "select distinct i.issue as review,
                i.summary as summary,
                 timestampdiff (HOUR, t1.min_time, t1.max_time) as timeOpened
          from (
                select c.issue_id as issue_id,
                       min(c.changed_on) as min_time,
                       max(c.changed_on) as max_time
                from changes c,
                     issues i
                where c.issue_id = i.id and
                      (i.status='NEW' or
                       i.status='WORKINPROGRESS')
                group by c.issue_id) t1,
               issues i
          where t1.issue_id = i.id
          order by timeOpened desc
          limit 10;"
rs = dbSendQuery(mydb, query)
data3 = fetch(rs, n=-1)

#time to review for those merged or abandoned

query = "select timestampdiff (HOUR, t1.min_time, t1.max_time) as timeOpened
          from (
                select c.issue_id as issue_id,
                       min(c.changed_on) as min_time,
                       max(c.changed_on) as max_time
                from changes c,
                     issues i
                where c.issue_id = i.id and
                      i.status='MERGED' 
                group by c.issue_id) t1,
               issues i
          where t1.issue_id = i.id
          order by timeOpened desc"
rs = dbSendQuery(mydb, query)
data4 = fetch(rs, n=-1)
#d <- density(log(data4$timeOpened))
summary(data4$timeOpened)
d <- density(log(data4$timeOpened))
#hist(data4$timeOpened, breaks=100)
plot(d, main="", xlab="", ylab="")


query = "select timestampdiff (HOUR, t1.min_time, t1.max_time) as timeOpened
          from (
                select c.issue_id as issue_id,
                       min(c.changed_on) as min_time,
                       max(c.changed_on) as max_time
                from changes c,
                     issues i
                where c.issue_id = i.id and
                      i.status='ABANDONED'   
                group by c.issue_id) t1,
               issues i
          where t1.issue_id = i.id
          order by timeOpened desc"
rs = dbSendQuery(mydb, query)
data4 = fetch(rs, n=-1)
#d <- density(log(data4$timeOpened))
summary(data4$timeOpened)
d <- density(log(data4$timeOpened))
#hist(data4$timeOpened, breaks=100)
lines(d, col="red")