import datetime
import pymssql

conn = pymssql.connect(host='localhost', user='xxxx', password='xxxx', database='AccessControl')
cur = conn.cursor()
now = datetime.datetime.utcnow().replace(microsecond=0)
now_minusfive = now + datetime.timedelta(minutes = -5)


#
# Get all the access granted events from the DB
#
access_granted = "SELECT convert(datetime,switchoffset(convert(datetimeoffset,\"EVENTS\".\"EVENT_TIME_UTC\"),datename(TzOffset,SYSDATETIMEOFFSET()))) AS EVENT_LOCAL_TIME, \"EMP\".\"LASTNAME\", \"EMP\".\"FIRSTNAME\", \"EVENT\".\"EVDESCR\", \"EVENTS\".\"CARDNUM\", \"EVENTS\".\"EMPID\", \"EVENTS\".\"SERIALNUM\", \"ACCESSPANE\".\"PANELTYPE\", \"READER\".\"READERDESC\", \"EVENTS\".\"DEVID\", \"EVENTS\".\"INPUTDEVID\", \"PANELTYPE\".\"PANELCLASS\", \"ACCESSPANE\".\"NAME\" FROM {OJ (((((((\"ACCESSCONTROL\".\"DBO\".\"EVENTS\" \"EVENTS\" INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"EVENT\" \"EVENT\" ON (\"EVENTS\".\"EVENTTYPE\"=\"EVENT\".\"EVTYPEID\") AND (\"EVENTS\".\"EVENTID\"=\"EVENT\".\"EVID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"READER\" \"READER\" ON (\"EVENTS\".\"MACHINE\"=\"READER\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"READER\".\"READERID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"EMP\" \"EMP\" ON \"EVENTS\".\"EMPID\"=\"EMP\".\"ID\") INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCESSPANE\" \"ACCESSPANE\" ON \"EVENTS\".\"MACHINE\"=\"ACCESSPANE\".\"PANELID\") LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCOUNT_ZONE\" \"ACCOUNT_ZONE\" ON (\"EVENTS\".\"MACHINE\"=\"ACCOUNT_ZONE\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"ACCOUNT_ZONE\".\"ZONENUM\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"OPENIT_DEVICE\" \"OPENIT_DEVICE_SUB\" ON ((\"EVENTS\".\"DEVID\"=\"OPENIT_DEVICE_SUB\".\"OPENIT_DEVICEID\") AND (\"EVENTS\".\"MACHINE\"=\"OPENIT_DEVICE_SUB\".\"PANELID\")) AND (\"EVENTS\".\"INPUTDEVID\"=\"OPENIT_DEVICE_SUB\".\"SUB_DEVICEID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"OPENIT_DEVICE\" \"OPENIT_DEVICE\" ON (\"EVENTS\".\"DEVID\"=\"OPENIT_DEVICE\".\"OPENIT_DEVICEID\") AND (\"EVENTS\".\"MACHINE\"=\"OPENIT_DEVICE\".\"PANELID\")) INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"PANELTYPE\" \"PANELTYPE\" ON \"ACCESSPANE\".\"PANELTYPE\"=\"PANELTYPE\".\"PANELTYPEID\" } WHERE (\"EVENTS\".\"EVENTTYPE\"=0 OR \"EVENTS\".\"EVENTTYPE\"=2 AND (\"EVENTS\".\"EVENTID\"=0 OR \"EVENTS\".\"EVENTID\"=2) OR \"EVENTS\".\"EVENTTYPE\"=3 AND (\"EVENTS\".\"EVENTID\"=4 OR \"EVENTS\".\"EVENTID\"=5)) AND (EVENTS.EVENT_TIME_UTC >= '%s') AND (EVENTS.EVENT_TIME_UTC <= '%s')"

cur.execute(access_granted % (now_minusfive, now,))
row = cur.fetchone()
while row:
print "%s LASTNAME=\"%s\", FIRSTNAME=\"%s\", EVDESCR=\"%s\", CARDNUM=\"%d\", EMPID=\"%d\", SERIALNUM=\"%d\", PANELTYPE=\"%d\", READERDESC=\"%s\", DEVID=\"%d\", INPUTDEVID=\"%d\", PANELCLASS=\"%d\", PANELNAME=\"%s\"" % (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12])
row = cur.fetchone()

########
#
# Get all the access denied events from the DB
#
access_denied = "SELECT convert(datetime,switchoffset(convert(datetimeoffset,\"EVENTS\".\"EVENT_TIME_UTC\"),datename(TzOffset,SYSDATETIMEOFFSET()))) AS EVENT_LOCAL_TIME, \"EMP\".\"LASTNAME\", \"EMP\".\"FIRSTNAME\", \"EVENT\".\"EVDESCR\", \"EVENTS\".\"CARDNUM\", \"EVENTS\".\"EMPID\", \"EVENTS\".\"SERIALNUM\", \"ACCESSPANE\".\"PANELTYPE\", \"READER\".\"READERDESC\", \"EVENTS\".\"DEVID\", \"EVENTS\".\"INPUTDEVID\", \"PANELTYPE\".\"PANELCLASS\", \"ACCESSPANE\".\"NAME\" FROM {OJ (((((((\"ACCESSCONTROL\".\"DBO\".\"EVENTS\" \"EVENTS\" INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"EVENT\" \"EVENT\" ON (\"EVENTS\".\"EVENTTYPE\"=\"EVENT\".\"EVTYPEID\") AND (\"EVENTS\".\"EVENTID\"=\"EVENT\".\"EVID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"READER\" \"READER\" ON (\"EVENTS\".\"MACHINE\"=\"READER\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"READER\".\"READERID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"EMP\" \"EMP\" ON \"EVENTS\".\"EMPID\"=\"EMP\".\"ID\") INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCESSPANE\" \"ACCESSPANE\" ON \"EVENTS\".\"MACHINE\"=\"ACCESSPANE\".\"PANELID\") LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCOUNT_ZONE\" \"ACCOUNT_ZONE\" ON (\"EVENTS\".\"MACHINE\"=\"ACCOUNT_ZONE\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"ACCOUNT_ZONE\".\"ZONENUM\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"OPENIT_DEVICE\" \"OPENIT_DEVICE_SUB\" ON ((\"EVENTS\".\"DEVID\"=\"OPENIT_DEVICE_SUB\".\"OPENIT_DEVICEID\") AND (\"EVENTS\".\"MACHINE\"=\"OPENIT_DEVICE_SUB\".\"PANELID\")) AND (\"EVENTS\".\"INPUTDEVID\"=\"OPENIT_DEVICE_SUB\".\"SUB_DEVICEID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"OPENIT_DEVICE\" \"OPENIT_DEVICE\" ON (\"EVENTS\".\"DEVID\"=\"OPENIT_DEVICE\".\"OPENIT_DEVICEID\") AND (\"EVENTS\".\"MACHINE\"=\"OPENIT_DEVICE\".\"PANELID\")) INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"PANELTYPE\" \"PANELTYPE\" ON \"ACCESSPANE\".\"PANELTYPE\"=\"PANELTYPE\".\"PANELTYPEID\"} WHERE (\"EVENTS\".\"EVENTTYPE\"=1 OR \"EVENTS\".\"EVENTTYPE\"=2 AND \"EVENTS\".\"EVENTID\"=1 OR \"EVENTS\".\"EVENTTYPE\"=3 AND (\"EVENTS\".\"EVENTID\"=0 OR \"EVENTS\".\"EVENTID\"=1 OR \"EVENTS\".\"EVENTID\"=2 OR \"EVENTS\".\"EVENTID\"=3 OR \"EVENTS\".\"EVENTID\"=6 OR \"EVENTS\".\"EVENTID\"=7)) AND (EVENTS.EVENT_TIME_UTC >= '%s') AND (EVENTS.EVENT_TIME_UTC <= '%s')"

cur.execute(access_denied % (now_minusfive, now,))
row = cur.fetchone()
while row:
print "%s LASTNAME=\"%s\", FIRSTNAME=\"%s\", EVDESCR=\"%s\", CARDNUM=\"%d\", EMPID=\"%d\", SERIALNUM=\"%d\", PANELTYPE=\"%d\", READERDESC=\"%s\", DEVID=\"%d\", INPUTDEVID=\"%d\", PANELCLASS=\"%d\", PANELNAME=\"%s\"" % (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12])
row = cur.fetchone()

########
#
# Get all of the anti-pass back events from the DB
#
anti_passback = "SELECT convert(datetime,switchoffset(convert(datetimeoffset,\"EVENTS\".\"EVENT_TIME_UTC\"),datename(TzOffset,SYSDATETIMEOFFSET()))) AS EVENT_LOCAL_TIME, \"READER\".\"READERDESC\", \"EVENT\".\"EVDESCR\", \"ACCESSPANE\".\"NAME\", \"EVENTS\".\"EMPID\", \"EMP\".\"FIRSTNAME\", \"EMP\".\"LASTNAME\", \"EVENTS\".\"MACHINE\", \"EVENTS\".\"CARDNUM\", \"EVENTS\".\"SERIALNUM\" FROM {OJ (((\"ACCESSCONTROL\".\"DBO\".\"EVENTS\" \"EVENTS\" INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"EVENT\" \"EVENT\" ON (\"EVENTS\".\"EVENTTYPE\"=\"EVENT\".\"EVTYPEID\") AND (\"EVENTS\".\"EVENTID\"=\"EVENT\".\"EVID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"READER\" \"READER\" ON (\"EVENTS\".\"MACHINE\"=\"READER\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"READER\".\"READERID\")) INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCESSPANE\" \"ACCESSPANE\" ON \"EVENTS\".\"MACHINE\"=\"ACCESSPANE\".\"PANELID\") LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"EMP\" \"EMP\" ON \"EVENTS\".\"EMPID\"=\"EMP\".\"ID\" } WHERE \"EVENTS\".\"EVENTTYPE\"=3 AND (EVENTS.EVENT_TIME_UTC >= '%s') AND (EVENTS.EVENT_TIME_UTC <= '%s')"

cur.execute(anti_passback % (now_minusfive, now,))
row = cur.fetchone()
while row:
print "%s READERDESC=\"%s\" EVDESCR=\"%s\" PANELNAME=\"%s\" EMPID=\"%d\" FIRSTNAME=\"%s\" LASTNAME=\"%s\" MACHINE=\"%d\" CARDNUM=\"%d\" SERIALNUM=\"%d\"" % (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9])
row = cur.fetchone()

########
#
# Get all of the asset events from the DB
#
asset_events = "SELECT convert(datetime,switchoffset(convert(datetimeoffset,\"EVENTS\".\"EVENT_TIME_UTC\"),datename(TzOffset,SYSDATETIMEOFFSET()))) AS EVENT_LOCAL_TIME, \"EMP\".\"LASTNAME\", \"EMP\".\"FIRSTNAME\", \"EVENT\".\"EVDESCR\", \"EVENTS\".\"CARDNUM\", \"EVENTS\".\"EMPID\", \"READER\".\"READERDESC\", \"EVENTS\".\"SERIALNUM\", \"ASSET\".\"NAME\", \"EVENTS\".\"MACHINE\", \"ACCESSPANE\".\"PANELID\" FROM {OJ ((((\"ACCESSCONTROL\".\"DBO\".\"EVENTS\" \"EVENTS\" INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"EVENT\" \"EVENT\" ON (\"EVENTS\".\"EVENTTYPE\"=\"EVENT\".\"EVTYPEID\") AND (\"EVENTS\".\"EVENTID\"=\"EVENT\".\"EVID\")) INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"READER\" \"READER\" ON (\"EVENTS\".\"MACHINE\"=\"READER\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"READER\".\"READERID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"EMP\" \"EMP\" ON \"EVENTS\".\"EMPID\"=\"EMP\".\"ID\") INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCESSPANE\" \"ACCESSPANE\" ON \"EVENTS\".\"MACHINE\"=\"ACCESSPANE\".\"PANELID\") LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"ASSET\" \"ASSET\" ON \"EVENTS\".\"ASSETID\"=\"ASSET\".\"ASSETID\" } WHERE \"EVENTS\".\"EVENTTYPE\"=5 AND (EVENTS.EVENT_TIME_UTC >= '%s') AND (EVENTS.EVENT_TIME_UTC <= '%s')"

cur.execute(asset_events % (now_minusfive, now,))
row = cur.fetchone()
while row:
print "%s LASTNAME=\"%s\", FIRSTNAME=\"%s\", EVDESCR=\"%s\", CARDNUM=\"%d\", EMPID=\"%d\", READERDESC=\"%s\", SERIALNUM=\"%d\", ASSETNAME=\"%s\", MACHINE=\"%d\", PANELID=\"%s\"" % (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10])
row = cur.fetchone()

########
#
# Get all of the device status events from the DB
#
device_status = "SELECT convert(datetime,switchoffset(convert(datetimeoffset,\"EVENTS\".\"EVENT_TIME_UTC\"),datename(TzOffset,SYSDATETIMEOFFSET()))) AS EVENT_LOCAL_TIME, \"ACCESSPANE\".\"NAME\", \"EVENT\".\"EVDESCR\", \"EVENTS\".\"FUNCLIST\", \"EVENTS\".\"DEVID\", \"EVENTS\".\"INPUTDEVID\", \"READER\".\"AUX1NAME\", \"READER\".\"OUT1NAME\", \"EVENTS\".\"MACHINE\", \"ACCESSPANE\".\"PANELTYPE\", \"READER\".\"READERDESC\", \"PANELTYPE\".\"PANELCLASS\" FROM {OJ (((((\"ACCESSCONTROL\".\"DBO\".\"EVENTS\" \"EVENTS\" INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"EVENT\" \"EVENT\" ON (\"EVENTS\".\"EVENTTYPE\"=\"EVENT\".\"EVTYPEID\") AND (\"EVENTS\".\"EVENTID\"=\"EVENT\".\"EVID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"ALARMINPUT\" \"ALARMINPUT\" ON ((\"EVENTS\".\"MACHINE\"=\"ALARMINPUT\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"ALARMINPUT\".\"ALARMPID\")) AND (\"EVENTS\".\"INPUTDEVID\"=\"ALARMINPUT\".\"INPUTID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"READER\" \"READER\" ON (\"EVENTS\".\"MACHINE\"=\"READER\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"READER\".\"READERID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCESSPANE\" \"ACCESSPANE\" ON \"EVENTS\".\"MACHINE\"=\"ACCESSPANE\".\"PANELID\")) LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCOUNT_ZONE\" \"ACCOUNT_ZONE\" ON (\"EVENTS\".\"MACHINE\"=\"ACCOUNT_ZONE\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"ACCOUNT_ZONE\".\"ZONENUM\") LEFT OUTER JOIN \"ACCESSCONTROL\".\"DBO\".\"PANELTYPE\" \"PANELTYPE\" ON \"ACCESSPANE\".\"PANELTYPE\"=\"PANELTYPE\".\"PANELTYPEID\"} WHERE \"EVENTS\".\"EVENTTYPE\"=4 AND (EVENTS.EVENT_TIME_UTC >= '%s') AND (EVENTS.EVENT_TIME_UTC <= '%s')"

cur.execute(device_status % (now_minusfive, now,))
row = cur.fetchone()
while row:
print "%s PANELNAME=\"%s\", EVDESCR=\"%s\", FUNCLIST=\"%d\", DEVID=\"%d\", INPUTDEVID=\"%d\", READERAUXNAME=\"%s\", READEROUTNAME=\"%s\", MACHINE=\"%d\", PANELTYPE=\"%d\", READERDESC=\"%s\", PANELCLASS=\"%d\"" % (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11])
row = cur.fetchone()

########
#
# Get all of the reader status events from the DB
#
reader_status = "SELECT convert(datetime,switchoffset(convert(datetimeoffset,\"EVENTS\".\"EVENT_TIME_UTC\"),datename(TzOffset,SYSDATETIMEOFFSET()))) AS EVENT_LOCAL_TIME, \"READER\".\"READERDESC\", \"EVENT\".\"EVDESCR\", \"EVENTS\".\"DEVID\", \"EVENTS\".\"SERIALNUM\", \"ACCESSPANE\".\"PANELID\", \"EVENTS\".\"INPUTDEVID\", \"READER\".\"AUX1NAME\", \"READER\".\"OUT1NAME\", \"EVENTS\".\"MACHINE\" FROM {OJ ((\"ACCESSCONTROL\".\"DBO\".\"EVENTS\" \"EVENTS\" INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"EVENT\" \"EVENT\" ON (\"EVENTS\".\"EVENTTYPE\"=\"EVENT\".\"EVTYPEID\") AND (\"EVENTS\".\"EVENTID\"=\"EVENT\".\"EVID\")) INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"READER\" \"READER\" ON (\"EVENTS\".\"MACHINE\"=\"READER\".\"PANELID\") AND (\"EVENTS\".\"DEVID\"=\"READER\".\"READERID\")) INNER JOIN \"ACCESSCONTROL\".\"DBO\".\"ACCESSPANE\" \"ACCESSPANE\" ON \"EVENTS\".\"MACHINE\"=\"ACCESSPANE\".\"PANELID\" } WHERE \"EVENTS\".\"EVENTTYPE\"=4 AND (EVENTS.EVENT_TIME_UTC >= '%s') AND (EVENTS.EVENT_TIME_UTC <= '%s')"

cur.execute(reader_status % (now_minusfive, now,))
row = cur.fetchone()
while row:
print "%s READERDESC=\"%s\", EVDESCR=\"%s\", DEVID=\"%d\", SERIALNUM=\"%d\", PANELID=\"%d\", INPUTDEVID=\"%d\", READERAUXNAME=\"%s\", READEROUTNAME=\"%s\", MACHINE=\"%d\"" % (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9])
row = cur.fetchone()
########
 
conn.close()