import MySQLdb
import json
import cherrypy

class Signups:

    def index(self):
        '''
        This gets the number of user sign ups
        '''
        # form connection
        conn = MySQLdb.connect (host = "localhost",
                                user = "root",
                                passwd = "12345Aa",
                                db = "geckoboard")

        cursor = conn.cursor()
        # SQL Query
        cursor.execute('''  SELECT COUNT(1) AS Signups,
                            DATE_FORMAT(JoinDate, "%Y-%m-%d") AS Date
                            FROM users
                            GROUP BY Date''')

        # Get all data using fetchall()
        rows = cursor.fetchall()

        # convert data to geckoboard format
        dict = { "item"     :[x[0] for x in rows],
                 "settings" : { "axisx" : [x[1] for x in rows],
                                "axisy" : [0,100],
                                "colour": "ff9900"}

            }

        # convert to json
        data_string = json.dumps(dict)

        # test output
        return data_string

        # Close connections
        cursor.close ()
        conn.close ()

    index.exposed = True

import os.path
tutconf = os.path.join(os.path.dirname(__file__), 'tutorial.conf')

if __name__ == '__main__':
	cherrypy.quickstart(Signups(), config=tutconf)
else:
	cherrypy.tree.mount(Signups(), config=tutconf)