#
# Copyright (c) 2005-2006 rPath, Inc.
# All Rights Reserved
#

import time

from mint import buildtypes
from mint.reports.mint_reports import MintReport

class ExecSummary(MintReport):
    title = 'Executive Summary'
    headers = ('Metric', 'Value')

    def getData(self, reportTime = time.time()):
        data = []

        cu = self.db.cursor()

        # count the site's active users
        cu.execute("SELECT COUNT(*) FROM Users WHERE active=1")
        numUsers = cu.fetchone()[0]
        data.append(('Confirmed users', numUsers))

        # count the users who accessed the site this week
        cu.execute("SELECT COUNT(*) FROM Users WHERE timeAccessed > ?",
                   reportTime - 604800)
        data.append(('Active users this week', cu.fetchone()[0]))

        # count the raw number of accounts created this week
        cu.execute("SELECT COUNT(*) FROM Users WHERE timeCreated > ?",
                   reportTime - 604800)
        data.append(('New users this week', cu.fetchone()[0]))

        # spacer
        data.append(('',''))

        # count total number of projects
        cu.execute("SELECT COUNT(*) FROM Projects")
        numProj = cu.fetchone()[0]
        data.append(('Total Projects', numProj))

        # count projects with builds
        cu.execute("""SELECT COUNT(*) FROM
                          (SELECT DISTINCT projectId
                            FROM Builds
                            WHERE troveName IS NOT NULL)
                          AS ProjectBuilds""")
        data.append(('Projects with builds', cu.fetchone()[0]))

        # count projects with commits
        cu.execute("""SELECT COUNT(*) FROM
                          (SELECT DISTINCT projectId FROM Commits)
                          AS ProjectCommits""")
        data.append(('Projects with commits', cu.fetchone()[0]))

        # count projects with no users
        cu.execute("SELECT COUNT(*) FROM (SELECT DISTINCT projectId FROM ProjectUsers) AS DistinctProjects")
        data.append(('Orphaned Projects', numProj - cu.fetchone()[0]))

        # count projects with 5 or more users
        cu.execute("""SELECT COUNT(*) FROM
                              (SELECT DISTINCT projectId,
                                  COUNT(projectId) AS numUsers
                          FROM ProjectUsers
                          GROUP BY projectId) AS DistinctProjects
                          WHERE numUsers >= 5""")
        data.append(('Projects with 5 or more users', cu.fetchone()[0]))

        # count the total number of non-rpath projects
        cu.execute("""SELECT COUNT(projectList)
                          FROM (SELECT Projects.projectId AS projectList,
                                       COUNT(userId) AS numUsers
                                    FROM Projects
                                    LEFT JOIN ProjectUsers
                                        ON ProjectUsers.projectId =
                                               Projects.projectId
                                    WHERE level IN (0, 1)
                                    AND userId NOT IN %s
                                    GROUP BY Projects.projectId) AS A
                          WHERE numUsers > 5""" % str(self.employeeIds))

        data.append(('Projects with 5 or more non-rPath users',
                     cu.fetchone()[0]))

        # spacer
        data.append(('',''))
        data.append(('Top Projects',' Users'))

        cu.execute("""SELECT Projects.name,
                             COUNT(Projects.projectId) AS numUsers
                          FROM ProjectUsers
                          LEFT JOIN Projects
                              ON ProjectUsers.projectId = Projects.projectId
                          GROUP BY Projects.projectId
                          ORDER BY numUsers DESC, name
                          LIMIT 5""")

        data.extend([(x[0], x[1]) for x in cu.fetchall()])

        return data

@@ -3932,16 +3932,1084 @@
 ll()%5D)%0A%0A
+        # spacer%0A        data.append(('',''))%0A        cu.execute(%22%22%22SELECT COUNT(*)%0A                          FROM UserData%0A                          WHERE name='insider' AND value = '1'%22%22%22)%0A        data.append(('Number of opt-ins for insider tips and tricks',%0A                     cu.fetchone()%5B0%5D))%0A%0A        cu.execute(%22%22%22SELECT COUNT(*)%0A                          FROM UserData%0A                          WHERE name='insider' AND value = '0'%22%22%22)%0A        data.append(('Number of opt-outs for insider tips and tricks',%0A                     cu.fetchone()%5B0%5D))%0A%0A        cu.execute(%22%22%22SELECT COUNT(*)%0A                          FROM UserData%0A                          WHERE name='newsletter' AND value = '1'%22%22%22)%0A        data.append(('Number of opt-ins for monthly newsletter',%0A                     cu.fetchone()%5B0%5D))%0A%0A        cu.execute(%22%22%22SELECT COUNT(*)%0A                          FROM UserData%0A                          WHERE name='newsletter' AND value = '0'%22%22%22)%0A        data.append(('Number of opt-outs for monthly newsletter',%0A                     cu.fetchone()%5B0%5D))%0A%0A
         

add marketing metrics to executive summary.