def projects_efficiency(opts, people_out, affs_out):
    # BMI and time to review in mean per general project
    scr_dbcon = SCRQuery(opts.dbuser, opts.dbpassword, opts.dbreview, opts.dbidentities)
    scm_dbcon = SCMQuery(opts.dbuser, opts.dbpassword, opts.dbcvsanaly, opts.dbidentities)
    its_dbcon = ITSQuery(opts.dbuser, opts.dbpassword, opts.dbbicho, opts.dbidentities)

    projects = integrated_projects(scm_dbcon)

    projects_ids = projects["subproject_id"]
    projects_list = []
    bmi_list = []
    time2review_list = []
    bmi_its = []

    period = "month"
    releases = opts.releases.split(",")[-2:]
    startdate = "'"+releases[0]+"'"
    enddate = "'"+releases[1]+"'"

    for project_id in projects_ids:
        project_title = "'" + project_id + "'"
        type_analysis = ["project", project_id]
        project_filters = MetricFilters(period, startdate, enddate, type_analysis, 10,
                                        people_out, affs_out)
        scr_bmi = scr.BMISCR(scr_dbcon, project_filters)
        time2review = scr.TimeToReview(scr_dbcon, project_filters)
   
        # ITS BMI index
        from vizgrimoire.ITS import ITS
        ITS.set_backend("launchpad")

        if project_id == 'Documentation':
            ITS._get_backend().closed_condition = "(new_value='Fix Committed' or new_value='Fix Released')"
        else:
            ITS.closed_condition = "(new_value='Fix Committed')"

        opened = its.Opened(its_dbcon, project_filters)
        closed = its.Closed(its_dbcon, project_filters)

        tickets_opened = opened.get_agg()["opened"]
        tickets_closed = closed.get_agg()["closed"]

        its_bmi = 0
        if tickets_closed > 0:
           its_bmi = round(float(tickets_closed)/float(tickets_opened), 2)


        projects_list.append(project_id)
        bmi_list.append(round(scr_bmi.get_agg()["bmiscr"], 2))
        time2review_list.append(round(time2review.get_agg()["review_time_days_median"], 2))
        bmi_its.append(its_bmi)


    createCSV({"projects":projects_list, "bmi":bmi_list, "timereview":time2review_list, "bmiits":bmi_its}, "./release/integrated_projects_efficiency.csv")    

def create_json(dbcon, filters):

    ITS.set_backend("jira")
    ITS._get_backend().closed_condition = " i.status = 'Closed' "

    pullrequests = PullRequests(dbcon, filters)
    submitters = Submitters(dbcon, filters)
    abandoned = Abandoned(dbcon, filters)
    merged = Merged(dbcon, filters)
    trackers = Trackers(dbcon, filters)

    #timeseries data
    data = dict(pullrequests.get_ts().items() +
                submitters.get_ts().items() +
                abandoned.get_ts().items() +
                merged.get_ts().items() +
                trackers.get_ts().items())
    if filters.type_analysis == []:
        createJSON(data, "scr-evolutionary.json")
    else:
        tracker = filters.type_analysis[1]
        tracker = tracker.replace("'", "")
        name = tracker.split("/")[-1:][0]
        createJSON(data, name + "-scr-evolutionary.json")

    #aggregated data
    data = dict(pullrequests.get_agg().items() +
                submitters.get_agg().items() +
                abandoned.get_agg().items() +
                merged.get_agg().items() +
                trackers.get_agg().items())

    enddate = filters.enddate

    for i in [7, 365, 0]:
        data = dict(data.items() +
                    pullrequests.get_trends(enddate, i).items() +
                    submitters.get_trends(enddate, i).items() +
                    abandoned.get_trends(enddate, i).items() +
                    merged.get_trends(enddate, i).items() +
                    trackers.get_trends(enddate, i).items())

    if filters.type_analysis == []:
        createJSON(data, "scr-static.json")
    else:
        tracker = filters.type_analysis[1]
        tracker = tracker.replace("'", "")
        name = tracker.split("/")[-1:][0]
        createJSON(data, name + "-scr-static.json")

def its_report(dbcon, filters):
    # basic metrics for ticketing systems

    dataset = {}

    from vizgrimoire.ITS import ITS
    ITS.set_backend("launchpad")

    opened = its.Opened(dbcon, filters)
    dataset["its_opened"] = opened.get_agg()["opened"]

    closed = its.Closed(dbcon, filters)
    dataset["its_closed"] = closed.get_agg()["closed"]

    return dataset

def data_source_increment_activity(opts, people_out, affs_out):
    # Per data source, the increment or decrement of the activity is displayed
    dataset = {}

    data_sources = ["Gits", "Tickets", "Mailing Lists", "Gerrit", "Askbot", "IRC"]
    action = ["commits", "closed tickets", "sent emails", "submitted reviews", "posted questions", "messages"]
    net_values = []
    rel_values = [] #percentage wrt the previous 365 days

    scm_dbcon = SCMQuery(opts.dbuser, opts.dbpassword, opts.dbcvsanaly, opts.dbidentities) 
    its_dbcon = ITSQuery(opts.dbuser, opts.dbpassword, opts.dbbicho, opts.dbidentities)
    mls_dbcon = MLSQuery(opts.dbuser, opts.dbpassword, opts.dbmlstats, opts.dbidentities)
    scr_dbcon = SCRQuery(opts.dbuser, opts.dbpassword, opts.dbreview, opts.dbidentities)
    qaforums_dbcon = QAForumsQuery(opts.dbuser, opts.dbpassword, opts.dbqaforums, opts.dbidentities)
    irc_dbcon = IRCQuery(opts.dbuser, opts.dbpassword, opts.dbirc, opts.dbidentities)

    period = "month"
    type_analysis = None
    releases = opts.releases.split(",")[-2:]
    startdate = "'"+releases[0]+"'"
    enddate = "'"+releases[1]+"'"
    filters = MetricFilters(period, startdate, enddate, None, 10, people_out, affs_out)

    commits = scm.Commits(scm_dbcon, filters)
    closed = its.Closed(its_dbcon, filters)
    emails = mls.EmailsSent(mls_dbcon, filters)
    submitted = scr.Submitted(scr_dbcon, filters)
    questions = qa.Questions(qaforums_dbcon, filters)
    messages = irc.Sent(irc_dbcon, filters)


    from vizgrimoire.ITS import ITS
    ITS.set_backend("launchpad")

    net_values.append(commits.get_trends(releases[1], 365)["commits_365"])
    rel_values.append(commits.get_trends(releases[1], 365)["percentage_commits_365"])
    net_values.append(closed.get_trends(releases[1], 365)["closed_365"])
    rel_values.append(closed.get_trends(releases[1], 365)["percentage_closed_365"])
    net_values.append(emails.get_trends(releases[1], 365)["sent_365"])
    rel_values.append(emails.get_trends(releases[1], 365)["percentage_sent_365"])
    net_values.append(submitted.get_trends(releases[1], 365)["submitted_365"])
    rel_values.append(submitted.get_trends(releases[1], 365)["percentage_submitted_365"])
    net_values.append(questions.get_trends(releases[1], 365)["qsent_365"])
    rel_values.append(questions.get_trends(releases[1], 365)["percentage_qsent_365"])
    net_values.append(messages.get_trends(releases[1], 365)["sent_365"])
    rel_values.append(messages.get_trends(releases[1], 365)["percentage_sent_365"])

    createCSV({"datasource":data_sources, "metricsnames":action, "relativevalues":rel_values, "netvalues":net_values}, "./release/data_source_evolution.csv")

    def get_list(self):
        from vizgrimoire.data_source import DataSource
        from vizgrimoire.filter import Filter
        startdate = self.filters.startdate
        enddate = self.filters.enddate
        closed_condition = ITS._get_closed_condition()
        if self.filters.closed_condition is not None:
             closed_condition = self.filters.closed_condition

        bots = DataSource.get_filter_bots(Filter("domain"))
        fbots = ''
        for bot in bots:
            fbots += " dom.name<>'"+bot+"' and "

        tables = Set([])
        filters = Set([])

        tables.union_update(self.db.GetTablesDomains(self.db.identities_db))
        tables.add(self.db.identities_db + ".domains dom")
        tables_str = self.db._get_tables_query(tables)
        filters.union_update(self.db.GetFiltersDomains())
        filters_str = self.db._get_filters_query(filters)

        q = "SELECT dom.name "+\
            "FROM "+ tables_str + " "+\
            "WHERE " + filters_str +" AND "+\
            "       dom.id = upd.domain_id and "+\
            "       "+ fbots +" "+\
            "       c.changed_on >= "+ startdate+ " AND "+\
            "       c.changed_on < "+ enddate+ " AND "+\
            "       "+ closed_condition+" "+\
            "GROUP BY dom.name "+\
            "ORDER BY COUNT(DISTINCT(c.issue_id)) DESC LIMIT " + str(Metrics.domains_limit)
        data = self.db.ExecuteQuery(q)
        return (data)

    def __get_sql_default__(self, evolutionary, close = False):
        # closed_condition =  ITS._get_closed_condition()

        fields = Set([])
        tables = Set([])
        filters = Set([])

        fields.add("count(distinct(pup.uuid)) as changers")
        tables.add("issues i")
        tables.add("changes ch")
        tables.union_update(self.db.GetSQLReportFrom(self.filters))
        filters.add("i.id = ch.issue_id")
        if close:
            fields = Set([])
            fields.add("count(distinct(pup.uuid)) as closers")
            closed_condition =  ITS._get_closed_condition()
            if self.filters.closed_condition is not None:
                 closed_condition = self.filters.closed_condition

            filters.add(closed_condition)
        filters.union_update(self.db.GetSQLReportWhere(self.filters))
        #unique identities filters
        tables.add("people_uidentities pup")
        filters.add("i.submitted_by = pup.people_id")

        query = self.db.BuildQuery(self.filters.period, self.filters.startdate,
                               self.filters.enddate, " ch.changed_on ",
                               fields, tables, filters, evolutionary,
                               self.filters.type_analysis)
        #Action needed to replace issues filters by changes one
        query = query.replace("i.submitted", "ch.changed")
        return query

    def _get_top_repository (self, metric_filters, days = None):
        startdate = metric_filters.startdate
        enddate = metric_filters.enddate
        repo_name = metric_filters.type_analysis[1]
        limit = metric_filters.npeople
        filter_bots = self.db.get_bots_filter_sql(self.data_source, metric_filters)
        closed_condition =  ITS._get_closed_condition()
        if self.filters.closed_condition is not None:
             closed_condition = self.filters.closed_condition

        if filter_bots != '': filter_bots = " AND " + filter_bots

        dtables = dfilters = ""
        if (days > 0):
            dtables = ", (SELECT MAX(changed_on) as last_date from changes) t "
            dfilters = " AND DATEDIFF (last_date, changed_on) < %s " % (days)

        q = "SELECT up.uuid as id, up.identifier as closers, "+\
            "COUNT(DISTINCT(i.id)) as closed "+\
            "FROM issues i, changes c, trackers t, people_uidentities pup, " +\
            "     "+self.db.identities_db+".uidentities up "+ dtables + \
            "WHERE "+closed_condition+" "+\
            "      AND pup.uuid = up.uuid "+\
            "      AND c.changed_by = pup.people_id "+\
            "      AND c.issue_id = i.id "+\
            "      AND i.tracker_id = t.id "+\
            "      AND t.url = "+repo_name+" "+\
            "      AND changed_on >= "+startdate+" AND changed_on < " +enddate +\
            "      " + filter_bots + " " + dfilters + \
            " GROUP BY up.identifier ORDER BY closed DESC, closers LIMIT " + str(limit)
        data = self.db.ExecuteQuery(q)
        return (data)

    def get_agg(self):
        fields = Set([])
        tables = Set([])
        filters = Set([])

        fields.add("TIMESTAMPDIFF(SECOND, i.submitted_on, t1.changed_on) as timeto")
        tables.add("issues i")
        tables.union_update(self.db.GetSQLReportFrom(self.filters))

        closed_condition = ITS._get_closed_condition()
        if self.filters.closed_condition is not None:
             closed_condition = self.filters.closed_condition

        # TODO: if RESOLVED and CLOSED appear in the same period of study, this 
        # will affect the total time to close the issue. Both timeframes will
        # be taken into account.
        table_extra = "(select issue_id, changed_on from changes where "+closed_condition+" and changed_on < "+self.filters.enddate+" and changed_on >= "+self.filters.startdate+") t1"
        tables.add(table_extra)

        filters.add("i.id=t1.issue_id")
        filters.union_update(self.db.GetSQLReportWhere(self.filters))

        query = "select " + self.db._get_fields_query(fields)
        query = query + " from " + self.db._get_tables_query(tables)
        query = query + " where " + self.db._get_filters_query(filters)

        return self.db.ExecuteQuery(query)

def GetClosedSummaryCompanies (period, startdate, enddate, identities_db, closed_condition, num_organizations):

    from vizgrimoire.ITS import ITS

    count = 1
    first_organizations = {}

    metric = DataSource.get_metrics("organizations", ITS)
    organizations = metric.get_list()
    organizations = organizations['name']

    for company in organizations:
        type_analysis = ["company", "'"+company+"'"]
        filter_com = MetricFilters(period, startdate, enddate, type_analysis)
        mclosed = ITS.get_metrics("closed", ITS)
        mclosed.filters = filter_com
        closed = mclosed.get_ts()
        # Rename field closed to company name
        closed[company] = closed["closed"]
        del closed['closed']

        if (count <= num_organizations):
            #Case of organizations with entity in the dataset
            first_organizations = dict(first_organizations.items() + closed.items())
        else :
            #Case of organizations that are aggregated in the field Others
            if 'Others' not in first_organizations:
                first_organizations['Others'] = closed[company]
            else:
                first_organizations['Others'] = [a+b for a, b in zip(first_organizations['Others'],closed[company])]
        count = count + 1
    first_organizations = completePeriodIds(first_organizations, period, startdate, enddate)

    return(first_organizations)