Latest and greatest

#!/usr/bin/env python

import MySQLdb
import MySQLdb.cursors

# Class handling MasterControl Data
# Class is a singleton and takes care of making a connection to the db and creating a cursor
# This is part of a pluggable architecture which simplifies use of different databases and other data sources
# To enable another database in MasterControl (e.g. postgreSQL)
#   Clone MasterControlMySQL.py to MasterControlPostgres.py
#       Adjust imports
#       Adjust database connection params in connect()
#       Adjust SQL statements where required (e.g. postgreSQL may have different syntax for joins...)
#       Remove MasterControlMySQL.py from application path and rerun RFIDServer
# Using other data sources (flat files, excel...)
#   Follow pattern as above and adjust subroutines to get data from other datasource/update other datasource
#   Ensure routines returning a 'row' return an appropriate array of data

# Example usage:
#   row = MCData().getMember( '117877', 'FobNumber' )

class MCData(object):
    conn = None
    cursor = None

    """Use to create a singleton"""
    def __new__(cls, *args, **kwds):
        """
        >>> s = MCData()
        >>> p = MCData()
        >>> id(s) == id(p)
        True
        """
        self = "__self__"
        if not hasattr(cls, self):
            instance = object.__new__(cls)
            instance.init(*args, **kwds)
            setattr(cls, self, instance)
        return getattr(cls, self)

    def init(self, *args, **kwds):
        self.connect()

    def connect( self ):
        # Information about the database
        database = "ENTS"
        table = "members"
        hostaddr = "localhost"
        username = "mastercontrol"
        # TODO: This is insecure as it can be read by anyone with access to this script, needs to be handled in another way
        password = "pass"
        self.conn = MySQLdb.connect ( host = hostaddr, user = username, passwd = password, db = database, cursorclass=MySQLdb.cursors.DictCursor )
            self.cursor = self.conn.cursor()
        #return conn, cursor

    def close( self, conn, cursor ):
        self.cursor.close()
        self.conn.close()

    # Go to the database and get the last unlock event for the given door number
    # We match FobNumber on the members table with CardNum on the DoorLog table and search based on door number
    def getLastUnlock( self, doorNum ):
            print "getLastUnlock"
        # We use an inner join because we want to find at least one match on both tables
            self.cursor.execute ("SELECT * from DoorLog inner join members ON members.FobNumber=DoorLog.CardNum where DoorNum = '"+str(doorNum)+"' order by EventDateTime DESC")
            row = self.cursor.fetchone()                 #get the record for the current cardnum
        print "getLastUnlock returning row:"
        print row
            return row

    def getMember( self, fobNumber, fobField ):
        print "getMember"
            self.cursor.execute ("SELECT * from members where "+fobField+" = '" + fobNumber+"'")
            try:
                row = self.cursor.fetchone()                 #get the record for the current cardnum
            except ValueError:              #Exception:
                    #no match for that user cardNum, send
                    #server.serve_forever()back fail message
                    print "Major Failure \n"
                    #print Exception
        return row

    def createDoorLogFail( self, fobNumber, doorNumber, cmd ):
        print "createDoorLogFail"
        self.cursor.execute("insert into DoorLog values ("+fobNumber+
            ",'"+fobNumber+" Not in database: "+cmd+
            "','"+doorNumber+"',now(),NULL)")

    def createDoorLogAdmit( self, fobNumber, firstName, lastName, doorNumber):
        print "createDoorLogAdmit"
            self.cursor.execute("insert into DoorLog values ("+fobNumber+
            ",'"+firstName+" "+lastName+" unlocked door "+doorNumber+
            "','"+doorNumber+"',now(),NULL)")

    def getSeconds( self, lastUnlock ):
        print "getSeconds"
        self.cursor.execute("select timestampdiff(second,'"+lastUnlock+"',now()) as seconds")
            seconds = self.cursor.fetchone()['seconds']
        if (seconds is None):
            seconds = 2000
        return seconds

    def updateMember( self, fobNumber, fobField, lastUnlockField ):
        print "updateMember"
        self.cursor.execute("update members set "+lastUnlockField+" =  now() where "+fobField+" = '"+fobNumber+"'")