)
)
)
# connect to database
cnx = db_connect(verbose)
# create cursor
cursor = db_create_cursor(cnx, verbose)
# check if day is school day
isSchoolDay = False
if verbose:
print("\n*** Checking if today is a school day...")
query = "SELECT * FROM days WHERE " "date = '" + dateNow + "' AND " "isWorkDay = '1'"
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount: # result found, is a work day
isSchoolDay = True
if verbose:
print("*** This is a school day")
# check if day is not on a break
isNotOnBreak = False
if isSchoolDay:
if verbose:
print("\n*** Checking if today is not on a break...")
query = (
"SELECT * FROM breaks WHERE "
"startDate <= '" + dateNow + "' AND "
"endDate >= '" + dateNow + "'"
)
def pageBody():
# get ring times
query = (
"SELECT ringTimeId, ringTimeName, weekDays, TIME_FORMAT(ringTime, '%H:%i') as ringTime, ringPatternId "
"FROM ringTimes ORDER BY ringTime ASC"
)
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount: # display ring times in a table
print("\n<br>\n<br>")
print('<table style="width:100%">')
print("<tr>")
print("<th>Id</th>")
print("<th>Time name</th>")
print("<th>Ring time</th>")
print("<th>Ring pattern id</th>")
print("<th>Ring pattern name</th>")
print("<th>Ring pattern</th>")
for dayNumber in range(0, 7):
print("<th>%s</th>" % getDayName(dayNumber, verbose))
print("<th></th>")
print("<th></th>")
print("</tr>")
for row in result:
ringTimeId = row[0]
ringTimeName = row[1]
weekDays = row[2]
ringTime = row[3]
ringPatternId = row[4]
if editRingTimeId == str(
ringTimeId
): # this is the ringTime we are about to edit
editRingTimeName = ringTimeName
editWeekDays = weekDays
editRingTime = ringTime
editRingPatternId = ringPatternId
print("<tr>")
print("<th>%s</th>" % ringTimeId)
print("<th>%s</th>" % ringTimeName)
print("<th>%s</th>" % ringTime)
print("<th>%s</th>" % ringPatternId)
# get ring patterns
query = (
"SELECT ringPatternName, ringPattern FROM ringPatterns "
"WHERE ringPatternID = '" + str(ringPatternId) + "'"
)
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount:
for row in result:
ringPatternName = row[0]
ringPattern = row[1]
print("<th>%s</th>" % ringPatternName)
print("<th>%s</th>" % ringPattern)
for dayNumber in range(0, 7): # print day name
if weekDays[dayNumber] == "1":
print("<th>On</th>")
else:
print("<th>Off</th>")
print('<th><a href="ringTimes.py?deleteRingTimeId=%s">Delete</a></th>' % ringTimeId)
print('<th><a href="ringTimes.py?editRingTimeId=%s">Edit</a></th>' % ringTimeId)
print("</tr>")
print("</table")
if editRingTimeId:
print("\n<br><br>")
print("<h3>Edit ring time</h3>")
print('<form action="/ringTimes.py">')
print("Time id:<br>")
print('<input type="text" name="updateRingTimeId" value="%s">' % editRingTimeId)
print("<br><br><br>")
print("Ring time name:<br>")
print('<input type="text" name="updateRingTimeName" value="%s">' % editRingTimeName)
print ("State a name for your ring time. <br><br>" "\nMax 100 characters. <br>")
print("<br><br>")
print("Ring time:<br>")
print('<input type="text" name="updateRingTime" value="%s">' % editRingTime)
print ('State time in the form "hh:mm". <br>')
print("<br><br>")
print("Choose ring pattern:<br>")
print('<select name="newRingPatternId">')
# get ring patterns
query = "SELECT ringPatternId, ringPatternName, ringPattern FROM ringPatterns"
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount:
for row in result:
ringPatternId = row[0]
ringPatternName = row[1]
ringPattern = row[2]
isSelected = ""
if ringPatternId == editRingPatternId:
isSelected = 'selected="selected"'
print((
'<option value="%s" %s>%s: %s, %s</option>'
% (
ringPatternId,
isSelected,
ringPatternId,
ringPatternName,
ringPattern,
)
))
print("</select>")
print("<br><br>")
for dayNumber in range(0, 7):
isChecked = ""
if str(editWeekDays)[dayNumber] == "1":
isChecked = 'checked="checked"'
print((
'<input type="checkbox" name="%s" value="1" %s> %s<br>'
% (
getDayName(dayNumber, verbose),
isChecked,
getDayName(dayNumber, verbose),
)
))
print("<br><br>")
print('<input type="submit" value="Submit">')
print("</form>")
if addRingTime: # display form to add ring time
print("\n<br><br>")
print("<h3>Add ring time</h3>")
print('<form action="/ringTimes.py">')
print("Ring time name:<br>")
print('<input type="text" name="newRingTimeName" value="Time name">')
print ("State a name for your ring time. <br><br>" "\nMax 100 characters. <br>")
dateTimeNow = datetime.now()
timeNow = dateTimeNow.strftime("%H:%M")
print("<br><br>")
print("Ring time:<br>")
print('<input type="text" name="newRingTime" value="%s">' % timeNow)
print ('State time in the form "hh:mm". <br>')
print("<br><br>")
print("Choose ring pattern:<br>")
print('<select name="newRingPatternId">')
# get ring patterns
query = "SELECT ringPatternId, ringPatternName, ringPattern FROM ringPatterns"
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount:
for row in result:
ringPatternId = row[0]
ringPatternName = row[1]
ringPattern = row[2]
print((
'<option value="%s">%s: %s, %s</option>'
% (ringPatternId, ringPatternId, ringPatternName, ringPattern)
))
print("</select>")
print("<br><br>")
for dayNumber in range(0, 7):
isChecked = ""
if dayNumber >= 0 and dayNumber <= 4:
isChecked = 'checked="checked"'
print((
'<input type="checkbox" name="%s" value="1" %s> %s<br>'
% (
getDayName(dayNumber, verbose),
isChecked,
getDayName(dayNumber, verbose),
)
))
print("<br><br>")
print('<input type="submit" value="Submit">')
print("</form>")
def dumpToFile():
filesWritten = 0
timeNow = datetime.today()
timeStamp = timeNow.strftime("%Y%m%d%H%M%S")
USBPath = findUSBMountPoint(USBDir, labelMatch, verbose)
if not USBPath:
LCDMessage = "No USB inserted"
if verbose:
print("\n*** Printing to LCD: \n %s" % LCDMessage)
displayOnLCD("", LCDMessage, verbose)
onError(5, "No matching USB mounted")
else:
displayOnLCD("", "Writing files...", verbose)
# database
outFilePath = os.path.join(tempDir, "piSchoolBellDb-%s.csv" % timeStamp)
if verbose:
print("\n*** Creating file %s ..." % outFilePath)
outFile = open(outFilePath, "a")
# connect to database
cnx = db_connect(verbose)
# create cursor
cursor = db_create_cursor(cnx, verbose)
for table in ("breaks", "ringTimes", "ringPatterns", "days"):
if verbose:
print("\n*** Reading table %s..." % table)
selection = tableSelection(table, verbose)
query = "SELECT " + selection + " FROM " + table + " "
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount:
if verbose:
print("\n*** Table: %s" % table)
columnNames = selection.replace(", ", ";")
outFile.write("%s;%s\n" % (table, columnNames))
selection = selection.replace(" ", "").split(",")
for row in result:
i = 0
values = ""
for columnName in selection:
column = row[i]
values = "%s;%s" % (values, column)
if verbose:
print(" %s: %s" % (columnName, column))
i += 1
outFile.write("%s\n" % values)
if verbose:
print()
if verbose:
print("\n*** Closing file %s ..." % outFilePath)
outFile.close()
if verbose:
print("\n*** Moving file \n %s \n to \n %s" % (outFilePath, USBPath))
copy(outFilePath, USBPath)
filesWritten += 1
# log files
dirName, fileName, extension = splitPath(logFile, verbose)
logFileName = os.path.join(USBPath, "%s-%s.%s" % (fileName, timeStamp, extension))
if verbose:
print("\n*** Copying \n %s \n to \n %s ..." % (logFile, logFileName))
copyfile(logFile, logFileName)
filesWritten += 1
if verbose:
print(" %s" % gpioWatchLog)
dirName, fileName, extension = splitPath(gpioWatchLog, verbose)
gpioWatchLogName = os.path.join(
USBPath, "%s-%s.%s" % (fileName, timeStamp, extension)
)
if verbose:
print(
"\n*** Copying \n %s \n to \n %s ..."
% (
gpioWatchLog,
gpioWatchLogName,
)
)
copyfile(gpioWatchLog, gpioWatchLogName)
filesWritten += 1
# close cursor
db_close_cursor(cnx, cursor, verbose)
# close db
db_disconnect(cnx, verbose)
LCDMessage = "%s files written" % filesWritten
if verbose:
print("\n*** Printing to LCD: \n %s" % LCDMessage)
displayOnLCD("", LCDMessage, verbose)
def pageBody():
# get ring patterns
query = "SELECT ringPatternId, ringPatternName, ringPattern FROM ringPatterns"
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount: # display ring patterns in a table
print("\n<br>\n<br>")
print('<table style="width:400px">')
print("<tr>")
print("<th>Id</th>")
print("<th>Pattern name</th>")
print("<th>Pattern</th>")
print("<th></th>")
print("<th></th>")
print("</tr>")
for row in result:
ringPatternId = row[0]
ringPatternName = row[1]
ringPattern = row[2]
if editRingPatternId == str(
ringPatternId
): # this is the ringPattern we are about to edit
editRingPatternName = ringPatternName
editRingPattern = ringPattern
print("<tr>")
print("<th>%s</th>" % ringPatternId)
print("<th>%s</th>" % ringPatternName.encode("Latin1"))
print("<th>%s</th>" % ringPattern)
print('<th><a href="ringPatterns.py?deleteRingPatternId=%s">Delete</a></th>' % ringPatternId)
print('<th><a href="ringPatterns.py?editRingPatternId=%s">Edit</a></th>' % ringPatternId)
print("</tr>")
print("</table")
if editRingPatternId:
print("\n<br><br>")
print("<h3>Edit ring pattern</h3>")
print('<form action="/ringPatterns.py">')
print("Pattern id:<br>")
print('<input type="text" name="updateRingPatternId" value="%s">' % editRingPatternId)
print("<br><br><br>")
print("Ring pattern name:<br>")
print('<input type="text" name="updateRingPatternName" value="%s">' % editRingPatternName)
print (
"State a name for your ring pattern. <br><br>" "\nMax 100 characters. <br>"
)
print("<br><br>")
print("Ring pattern:<br>")
print('<input type="text" name="updateRingPattern" value="%s">' % editRingPattern)
print (
"State pattern in 1/10 of a second. <br><br>"
"\nSeparate values by commas. <br>"
"\nFirst number is ring time, second is pause, third is ring time and so on. <br>"
"\nIt must be an odd number of values. <br>"
"\nOnly digits, commas and spaces are allowed. <br>"
)
print("<br><br>")
print('<input type="submit" value="Submit">')
print("</form>")
if addRingPattern: # display form to add ring pattern
print("\n<br><br>")
print("<h3>Add ring pattern</h3>")
print('<form action="/ringPatterns.py">')
print("Ring pattern name:<br>")
print('<input type="text" name="newRingPatternName" value="Pattern name">')
print (
"State a name for your ring pattern. <br><br>" "\nMax 100 characters. <br>"
)
print("<br><br>")
print("Ring pattern:<br>")
print('<input type="text" name="newRingPattern" value="Ring pattern">')
print (
"State pattern in 1/10 of a second. <br><br>"
"\nSeparate values by commas. <br>"
"\nFirst number is ring time, second is pause, third is ring time and so on. <br>"
"\nIt must be an odd number of values. <br>"
"\nOnly digits, commas and spaces are allowed. <br>"
)
print("<br><br>")
print('<input type="submit" value="Submit">')
print("</form>")
print("+++ Button 2 pressed, pin %s" % gpio)
elif gpio == "24":
toggleTimer = True
if verbose:
print("+++ Button 3 pressed, pin %s" % gpio)
elif gpio == "25":
stopModeTimer = True
if verbose:
print("+++ Button 4 pressed, pin %s" % gpio)
else:
onError(3, "No action for gpio %s" % gpio)
# get first temperature
temp_query = "SELECT id, name, value FROM sensors LIMIT 1"
cursor = db_create_cursor(cnx)
temp_results = db_query(cursor, temp_query, verbose)
temp_id = temp_results[0][0]
temp_name = temp_results[0][1].strip()
temp_value = temp_results[0][2]
if verbose:
print("\n+++ First temp: %s, ID: %s, Value: %s degrees" % (temp_name, temp_id, temp_value))
db_close_cursor(cnx, cursor)
# get first mode
mode_query = "SELECT id, name, value FROM modes LIMIT 1"
cursor = db_create_cursor(cnx)
mode_results = db_query(cursor, mode_query, verbose)
mode_id = mode_results[0][0]
mode_name = mode_results[0][1]
mode_value = mode_results[0][2]
if verbose:
def pageBody():
print("\n<br>")
# time and date
print(("\n<br>Date: \n<br> %s "
"\n<br>Time: \n<br> %s "
"\n<br>\n<br>Week number: \n<br> %s "
"\n<br>Day number: \n<br> %s " %
(dateNow, timeNow, weekNumberNow, dayNumberNow)))
# uptime
uptimeSeconds = getUptime()
uptime = str(timedelta(seconds=uptimeSeconds))
print("\n<br>")
print("\n<br>Uptime: \n<br> %s" % uptime)
##### database
print("\n<br>")
print("\n<br>Database:")
for tableName in ("days", "ringTimes", "breaks", "ringPatterns"):
entries = countEntriesInDatabase(tableName, cursor, verbose)
if entries:
print("\n<br>  %s entries: %s" %
(tableName, entries))
else:
print("\n<br>  Error retriveing %s" %
tableName)
# lastUpdated = tableLastUpdated(tableName, cursor, verbose)
# if lastUpdated:
# print "\n<br>    Last updated: %s" % lastUpdated
# else:
# print "\n<br>    Error retriveing date"
print("\n<br>")
# how long does days run
query = (
"SELECT date, DATEDIFF(date,CURDATE()) FROM days ORDER BY date DESC LIMIT 1"
)
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount:
for row in result:
lastDate = row[0]
daysToEnd = row[1]
print("\n<br>  Last day in database: %s" %
lastDate)
else:
print(
"\n<br>  Error retrieving last date: No days in database"
)
# last break
query = "SELECT endDate FROM breaks ORDER BY endDate DESC LIMIT 1"
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount:
for row in result:
lastBreakEnds = row[0]
print("\n<br>  Last break ends: %s" %
lastBreakEnds)
else:
print(
"\n<br>  Error retrieving last break: No breaks in database"
)
print("\n<br>")
# count rings and school days
ringCount, schoolDayCount = countRingTimes(daysToEnd)
print("\n<br>  There's %s days left in the database" %
daysToEnd)
if schoolDayCount:
print("\n<br>  Of them %s are school days" %
schoolDayCount)
else:
print("\n<br>  No days of them are school days")
if ringCount:
print("\n<br>  Bell will ring %s times" %
ringCount)
else:
print("\n<br>  Bell will not ring")
##### system
print("\n<br>")
# hostname
print("\n<br>System:")
print("\n<br>  Hostname: %s" % socket.gethostname())
# ip info
# find this devices ip address
interfaceIPs = []
interfaces = ni.interfaces()
print("\n<br>")
print("\n<br>  Net interfaces with IP:")
i = 0
for interface in interfaces:
try:
ip = ni.ifaddresses(interface)[ni.AF_INET][0]["addr"]
except:
ip = "No IP assigned"
interfaceIPs.append({"interface%s" % i: interface, "ip%s" % i: ip})
i += 1
i = 0
for interfaceIP in interfaceIPs:
print(("\n<br>    %s: %s" %
(interfaceIP["interface%s" % i], interfaceIP["ip%s" % i])))
i += 1
def pageBody():
# get breaks
query = "SELECT breakId, breakName, startDate, endDate FROM breaks ORDER BY startDate ASC"
result, rowCount = db_query(cursor, query, verbose) # run query
if rowCount: # display breaks in a table
print("\n<br>\n<br>")
print('<table style="width:400px">')
print("<tr>")
print("<th>Id</th>")
print("<th>Break name</th>")
print("<th>Start date</th>")
print("<th>End date</th>")
print("<th></th>")
print("<th></th>")
print("</tr>")
for row in result:
schoolBreakId = row[0]
schoolBreakName = row[1]
startDate = row[2]
endDate = row[3]
if editSchoolBreakId == str(
schoolBreakId
): # this is the schoolBreak we are about to edit
editSchoolBreakName = schoolBreakName
editStartDate = startDate
editEndDate = endDate
print("<tr>")
print("<th>%s</th>" % schoolBreakId)
print("<th>%s</th>" % schoolBreakName.encode("Latin1"))
print("<th>%s</th>" % startDate)
print("<th>%s</th>" % endDate)
print(
'<th><a href="schoolBreaks.py?deleteSchoolBreakId=%s">Delete</a></th>'
% schoolBreakId)
print(
'<th><a href="schoolBreaks.py?editSchoolBreakId=%s">Edit</a></th>'
% schoolBreakId)
print("</tr>")
print("</table")
if editSchoolBreakId:
print("\n<br><br>")
print("<h3>Edit break</h3>")
print('<form action="/schoolBreaks.py">')
print("Pattern id:<br>")
print('<input type="text" name="updateSchoolBreakId" value="%s">' %
editSchoolBreakId)
print("<br><br><br>")
print("Break name:<br>")
print('<input type="text" name="updateSchoolBreakName" value="%s">' %
editSchoolBreakName)
print("State a name for your break. <br><br>"
"\nMax 100 characters. <br>")
print("<br><br>")
print("Start date:<br>")
print('<input type="text" name="updateStartDate" value="%s">' %
editStartDate)
print("State date in the form: YY-MM-DD. <br><br>"
"\nOnly digits and - are allowed. <br>")
print("<br><br>")
print("End date:<br>")
print('<input type="text" name="updateEndDate" value="%s">' %
editEndDate)
print("State date in the form: YY-MM-DD. <br><br>"
"\nOnly digits and - are allowed. <br>")
print("<br><br>")
print('<input type="submit" value="Submit">')
print("</form>")
if addSchoolBreak: # display form to add break
print("\n<br><br>")
print("<h3>Add break</h3>")
print('<form action="/schoolBreaks.py">')
print("Break name:<br>")
print(
'<input type="text" name="newSchoolBreakName" value="Break name">')
print("State a name for your break. <br><br>"
"\nMax 100 characters. <br>")
dateTimeNow = datetime.now()
dateNow = str(dateTimeNow.strftime("%Y-%m-%d"))
print("<br><br>")
print("Start date:<br>")
print('<input type="text" name="newStartDate" value="%s">' % dateNow)
print("State date in the form: YY-MM-DD. <br><br>"
"\nOnly digits and - are allowed. <br>")
print("<br><br>")
print("End date:<br>")
print('<input type="text" name="newEndDate" value="%s">' % dateNow)
print("State date in the form: YY-MM-DD. <br><br>"
"\nOnly digits and - are allowed. <br>")
print("<br><br>")
print('<input type="submit" value="Submit">')
print("</form>")
def button1Pressed():
# find days in database
line_1 = "No data"
# connect to database
cnx = db_connect(verbose)
# create cursor
cursor = db_create_cursor(cnx, verbose)
query = "SELECT date, DATEDIFF(date,CURDATE()) FROM days ORDER BY date DESC LIMIT 1"
result, rowCount = db_query(cursor, query, verbose) # run query
try:
result, rowCount = db_query(cursor, query, verbose) # run query
except MySQLdb.Error as e:
print "\n<br>Error: Could not get number of days \n<br>%s" % e
print "\n<br>SQL: %s" % query
else:
if rowCount:
if verbose:
print "\n*** Got a result"
for row in result:
lastDate = row[0]
daysToEnd = row[1]
if verbose:
print " Days in db: %s" % daysToEnd
print " Days in db: %s" % lastDate
line_1 = "%s, %s" % (daysToEnd, lastDate.strftime("%Y-%m-%d"))
# find this devices ip address
interfaceIPs = []
line_2 = "Not connected"
if verbose:
print "\n*** Finding interfaces..."
interfaces = ni.interfaces()
if verbose:
print " Found %s interfaces" % len(interfaces)
print "\n*** Looking up ip addresses..."
i = 0
for interface in interfaces:
try:
ip = ni.ifaddresses(interface)[ni.AF_INET][0]['addr']
except:
ip = "NA"
interfaceIPs.append({"interface%s" % i: interface, "ip%s" % i: ip})
i += 1
i = 0
for interfaceIP in interfaceIPs:
if verbose:
print " Interface: %s" % interfaceIP['interface%s' % i]
print " IP: %s" % interfaceIP['ip%s' % i]
if (not interfaceIP['ip%s' % i].startswith('127')
and not interfaceIP['ip%s' % i].startswith('169')
and not interfaceIP['ip%s' % i] == "NA"):
line_2 = interfaceIP['ip%s' % i]
if verbose:
print "*** This is the one we will display"
if verbose:
print
i += 1
if internetAccess(testAddress, verbose):
line_2 = "*%s" % line_2
else:
line_2 = "-%s" % line_2
return line_1, line_2
# close cursor
db_close_cursor(cnx, cursor, verbose)
# close db
db_disconnect(cnx, verbose)
