Tooltron

#!/usr/bin/python

"""

  This file is part of Tooltron.

  Tooltron is free software: you can redistribute it and/or modify

  it under the terms of the Lesser GNU General Public License as published by

  the Free Software Foundation, either version 3 of the License, or

  (at your option) any later version.

  Tooltron is distributed in the hope that it will be useful,

  but WITHOUT ANY WARRANTY; without even the implied warranty of

  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

  Lesser GNU General Public License for more details.

  You should have received a copy of the Lesser GNU General Public License

  along with Tooltron.  If not, see <http://www.gnu.org/licenses/>.

  Copyright 2009 Bradford Neuman <bneuman@andrew.cmu.edu>

"""

################################################################################

## tooltron.py is the main tooltron server

# It connects to the civicrm database of mysql and sends commands to

# the cardbox and tools.  It must be connected over usb to the card

# reader, and the two arguments specify the devices to use for the

# serial communication to the card and tool boxes.

#

# This script requires the modules seen below, which may not be

# present by default

#

# Because of a problem with card scans buffering in stdio, there is a

# seperate thread which stores the last swiped id. To cleanly exit the

# program type C-d

#

# email bradneuman@gmail.com if you have any questions

################################################################################

import sys

import MySQLdb

import base64

import datetime

from datetime import date

from time import *

sys.path.append('../common')

import common

#this table maps which keypad button to press for each tool. This

#list should probably be printed next to the swipe box

tools = {

   'Bandsaw':['3','4'],

   'DrillPress':['1','2'],

   'Mill':['5'],

   'Lathe':[],

   'Cnc':['6']

   #HACK: since the saw isn't used I use it for testing random boards

   #'ChopMiterSaw':['1','2','5','6','7','9']

}

#this table maps keypad button numbers to tool IDs. The default is

#that the tool id is 10+the keypad number, but this would change if

#boards were swapped out. The tool ID number should be printed on the

#tool board, but needs to be hand coded into the toolbox code

ids = {

   '1':13,

   '2':18,

   '3':11,

   '4':17,

   '5':14,

   '6':12,

   '7':10,

   '8':10,

   '9':10,

   '0':10

}

try:

   flog = open("/var/log/tooltron", "a")

except IOError as e:

   print "\n*********************************************"

   print "File /var/log/tooltron could not be opened!"

   print e

   print "Using local file 'tooltron.log'"

   print "*********************************************\n"

   flog = open("tooltron.log","a")

finally:

   print flog

def logMessage(str):

   flog.write(strftime("%Y.%m.%d %H:%M:%S ") + str + "\n")

   flog.flush()

logMessage("----- SERVER STARTED -----")

def keypad2toolID(t):

   if t in ids:

      return ids[t]

   else:

      return -1

def reverseTool(tn):

   for t in tools:

      if tn in tools[t]:

         return t

   return 'invalid'

#keep count of successive non-acks

toolFails = tools.copy()

for t in toolFails:

   toolFails[t] = 0

################################################################################

# Main:

if len(sys.argv) < 2:

   print "usage: tooltron.py /path/to/bus/device"

   exit(-1)

if not common.initBus(sys.argv[1]):

    print "usage: tooltron.py /path/to/bus/device"

else:

   # read password

   try:

      passfile = open('pass', 'r')

   except IOError as e:

      print "\========================================"

      print "File 'pass' could not be opened!"

      print e

      print "This file is required for MySQL authentication"

      print "\========================================"

      exit(-1)

   pw = base64.b64decode(passfile.read())

   cursor = None

   qry1 = "SELECT tools_6 FROM civicrm_value_roboclub_info_2 WHERE card_number_1 = "

   # wapper to translate tool number

   def sendTool(t):

      tn = keypad2toolID(t)

      common.sendTool(tn)

   # This function eats the rest of the packet where data is what we have so far

   # TODO: do I still need this?

   def flushPacket(data):

      while len(data) < 6:

         data += bus.read(1)

      plen = data[4]

      if plen > 0:

         bus.read(ord(plen))

   while True:

      x = common.readTransaction()

      print "\n"

      if x == None:

         print "ERROR: bad transaction"

         logMessage("ERROR: got bad packet from cardbox")

         continue

      [key, cardnum] = x

      print "\--------------------\n",strftime("%Y.%m.%d %H:%M:%S \n")

      print "cardnum:", cardnum

      print "key:", key

      # Start doing the MySQL queries, hope this isn't too slow

      print "running queries..."

      if cursor != None:

         cursor.close()

      #TODO: handle timeouts / slow connections!

      db = MySQLdb.connect(host="roboclub8.frc.ri.cmu.edu", user="tooltron", passwd=pw, db="civicrm")

      print "connected to mysql server"

      cursor = db.cursor()

      print "got db cursor"

      print "qry1 (ACL)..."

      cursor.execute(qry1 + cardnum)

      result = cursor.fetchall()

      acl = []

      for r in result:

         tls = r[0].split("\x01")

         for t in tls:

            if t != '':

               try:

                  acl.extend (tools[t])

               except KeyError:

                  #this doesn't really matter

                  pass

      print "user has access to:", acl

      user = ""

      print "qry2 (name/email)..."

      #query for name

      qry2 = "SELECT civicrm_contact.display_name \

              FROM civicrm_value_roboclub_info_2 \

              INNER JOIN (civicrm_contact) \

              ON civicrm_value_roboclub_info_2.entity_id = civicrm_contact.id \

              WHERE civicrm_value_roboclub_info_2.card_number_1 = " + cardnum + ";"

      cursor.execute(qry2)

      result = cursor.fetchall()

      if len(result) > 0:

         user += result[0][0]

      qry2 = "SELECT civicrm_email.email \

              FROM civicrm_value_roboclub_info_2 \

              INNER JOIN (civicrm_email) \

              ON civicrm_email.contact_id = civicrm_value_roboclub_info_2.entity_id \

              WHERE civicrm_value_roboclub_info_2.card_number_1 = " + cardnum + ";"

      cursor.execute(qry2)

      result = cursor.fetchall()

      if len(result) > 0:

         user += " ("+result[0][0]+")"

      if user == "":

         user = cardnum

      print "[[[",user,"]]]"

      print "qry3 (payment)..."

      # check if member has paid recently 

      # first, figure out what semester it is

      today = date.today()

      # semester start dates

      aug20 = datetime.date(today.year, 8, 20) # Fall semester

      may21 = datetime.date(today.year, 5, 21) # summer

      jan1  = datetime.date(today.year, 1,  1) # Spring semester

      lastAug20 = datetime.date(today.year-1, 8, 20)

      lastMay21 = datetime.date(today.year-1, 5, 21)

      # if they don't fall after those two, its fall

      if today > aug20:

         print "its fall semester!"

         #its fall, so they must have paid after may21 (either full year of half) to be paid

         qry3 = "SELECT civicrm_value_roboclub_info_2.date_paid_10 >= STR_TO_DATE('" + lastMay21.strftime('%m/%d/%Y') + "', '%m/%d/%Y') ,  \

                 DATE_FORMAT(civicrm_value_roboclub_info_2.date_paid_10, '%M %d %Y') \

                 FROM civicrm_value_roboclub_info_2 \

                 WHERE civicrm_value_roboclub_info_2.card_number_1 = " + cardnum + ";"

      elif today > may21:

         print "its summer!"

         #its summer, so they need to have paid either full year since aug20 or one semester since jan 1

         qry3 = "SELECT (civicrm_value_roboclub_info_2.date_paid_10 >= STR_TO_DATE('" + lastAug20.strftime('%m/%d/%Y') + "', '%m/%d/%Y') AND \

                         civicrm_value_roboclub_info_2.amount_paid_11 = 25) OR \

                        (civicrm_value_roboclub_info_2.date_paid_10 >= STR_TO_DATE('" + jan1.strftime('%m/%d/%Y') + "', '%m/%d/%Y') AND \

                         civicrm_value_roboclub_info_2.amount_paid_11 = 15) , \

                 DATE_FORMAT(civicrm_value_roboclub_info_2.date_paid_10, '%M %d %Y') \

                 FROM civicrm_value_roboclub_info_2 \

                 WHERE civicrm_value_roboclub_info_2.card_number_1 = " + cardnum + ";"

      else:

         print "its spring!"

         #its spring, so they must have either paid full after aug20 or one semester since jan 1 (this is the same as summer)

         qry3 = "SELECT (civicrm_value_roboclub_info_2.date_paid_10 >= STR_TO_DATE('" + lastMay21.strftime('%m/%d/%Y') + "', '%m/%d/%Y') AND \

                         civicrm_value_roboclub_info_2.amount_paid_11 = 25) OR \

                        (civicrm_value_roboclub_info_2.date_paid_10 >= STR_TO_DATE('" + jan1.strftime('%m/%d/%Y') + "', '%m/%d/%Y') AND \

                         civicrm_value_roboclub_info_2.amount_paid_11 = 15) , \

                 DATE_FORMAT(civicrm_value_roboclub_info_2.date_paid_10, '%M %d %Y') \

                 FROM civicrm_value_roboclub_info_2 \

                 WHERE civicrm_value_roboclub_info_2.card_number_1 = " + cardnum + ";"

      cursor.execute(qry3)

      result = cursor.fetchall()

      if len(result) == 0 or result[0][0] == 0 or result[0][0] == "NULL":

         print "DENIED: user has not paid recently enough!"

         if len(result[0]) >= 2:

            print "date_paid: ",result[0][1]

            logMessage(user + " has not paid since " + result[0][1] + ", revoking access!")

            acl = []

         else:

            logMessage(user + " has not paid, revoking access!")

            acl = []

      toolName = reverseTool(key)

      toolNameLong = toolName + " (k="+str(key)+"("+str(ord(key))+"), t="+str(keypad2toolID(key))+")"

      print "request:",key,"(",ord(key),") ",toolNameLong

      if acl.count(key) > 0:

         common.sendGrant()

         #time.sleep(0.5) #TODO: do I still need this?

         sendTool(key)

         print "ACCESS GRANTED"

         logMessage(user+" ACCESSED tool "+toolNameLong)

         if common.checkAck(key):

            print "ACK"

            toolFails[toolName] = 0

         else:

            print "NO ACK!!!!"

            logMessage("tool "+toolNameLong+" did not ACK")

            toolFails[toolName] += 1

            if toolFails[toolName] > common.MAX_TOOL_FAILS:

               #TODO: send email

               logMessage("WARNING: tool "+toolNameLong+

                          " has failed to ACK "+

                          str(common.MAX_TOOL_FAILS)+" times in a row.")

      else:

         common.sendDeny()

         print "ACCESS DENIED!!"

         logMessage(user + " DENIED on tool "+toolNameLong)