gistfile1.rb

# require 'rubygems'
require 'mysql'
require 'time'

puts "[#{File.basename(__FILE__)}] running..."

time_in = Time.now
time_in_db = time_in.strftime("%Y-%m-%d %H:%M:%S")
puts "start time: " << time_in.strftime("%Y-%m-%d %H:%M:%S")


puts "params: " << ARGV.inspect

thermo, mode, status = ARGV[0].split("|")
mode = '%' if status == 'off'

puts "thermo: " << thermo
puts "mode: " << mode
puts "status: " << status


dbh = Mysql.real_connect("192.168.0.2", "hs", "sekrit", "homeseer")
puts "database connected: " << Time.now.strftime("%Y-%m-%d %H:%M:%S")
puts "Server version: " + dbh.get_server_info

unless thermo.nil? && status.nil?
  if status == "on"
    puts "on: insert"
    query_string = "INSERT INTO `thermo` (thermo_loc, mode, time_on) VALUES ('#{thermo}', '#{mode}', '#{time_in_db}')"
    puts query_string
    dbh.query( query_string )

  else
    puts "off: update"
    #find the corresponding open entry and set its close time
		# SETS THE MOST RECENT OPEN ENTRY ONLY
		query_string = "UPDATE `thermo` SET time_off = '#{time_in_db}' WHERE thermo_loc = '#{thermo}' AND mode LIKE '#{mode}' AND time_on IS NOT NULL AND time_off IS NULL ORDER BY time_on DESC LIMIT 1"
    puts query_string
		dbh.query( query_string )

		#find any lost entries and set their close time to five seconds after the open time
		# this should prevent future problems
		# the 15 minute interval is about average for the cycle time of each thermostat
		query_string = "UPDATE `thermo` SET time_off = (time_on + INTERVAL 15 MINUTE) WHERE thermo_loc = '#{thermo}' AND mode LIKE '#{mode}' AND time_on IS NOT NULL AND time_off IS NULL"
		puts query_string
		dbh.query( query_string )
  end
end

puts "database close"
dbh.close if dbh