WebServiceSource

This script needs to be placed in /var/www/lmce-ws/index.rb

#!/usr/bin/ruby

require 'rubygems'
require 'json'
require 'cgi'
require 'mysql'

def hashresult(query)
# Execute SQL query and return result as an array, one entry per row
  # setup sql database
  db = Mysql.new("localhost", "root", "")
  db.select_db("pluto_main")

  result = db.query(query)
  arr = Array.new
  #result.each_hash(with_table=true) do |row|
  result.each do |row| 
    arr << row
  end
  return arr
end  

def getlights()
# return list of lights (devices with (DeviceTemplate.FK_DeviceCategory = 73)
  query = "SELECT Device.PK_Device, Device.Description, Device.State, Device.FK_Room FROM Device,DeviceTemplate WHERE (Device.FK_DeviceTemplate = DeviceTemplate.PK_DeviceTemplate) AND (DeviceTemplate.FK_DeviceCategory = 73) ORDER BY PK_Device ASC"
  return hashresult(query)
end

def getrooms()
  # return list of all rooms
  query = "SELECT Room.PK_Room, Room.Description FROM Room ORDER BY Room.Description ASC"
  return hashresult(query)
end

def getscenarios()
  query = "SELECT CommandGroup.PK_CommandGroup, CommandGroup.Description, CommandGroup.Hint, CommandGroup.FK_Template, CommandGroup_Room.FK_Room
             FROM CommandGroup, CommandGroup_Room
             WHERE (CommandGroup_Room.FK_CommandGroup = CommandGroup.PK_CommandGroup)
             ORDER BY Sort ASC;"
  return hashresult(query)
end

def gettemplates()
  query = "
	SELECT PK_Template, Description
	FROM Template
	;"
  return hashresult(query)
end

def sendcommand(id,command)
 return `/usr/pluto/bin/MessageSend localhost 0 #{id} 1 #{command}`
end
	
# Send header to http client
puts "Content-type: text/html\r\n\r\n"
#puts "Content-type: application/json\r\n\r\n"

# Process CGI arguments
c=CGI.new 
cmd=c['cmd']

return_obj = case cmd
  when 'getLights' then getlights()
  when 'getRooms' then getrooms()
  when 'getScenarios' then getscenarios()
  when 'getTemplates' then gettemplates()
  when 'sendCommand' then sendcommand(c['id'], c['command'])
  else "use cmd=xxx where xxx is getLights, getRooms, getScenarios, getTemplates"
end

# Send the return object as JSON
puts return_obj.to_json