[7b7d827] | 1 | #!/usr/bin/ruby |
---|
| 2 | # |
---|
| 3 | # Author: Norbert Wigbels - foobla.wigbels.de/uber-foobla |
---|
| 4 | # |
---|
| 5 | # HotHotWrite receives data from HotHotRead and writes it to the dbms |
---|
| 6 | # Example of calling URL - http://www.wigbels.net/cgi-bin/hhw.rb?sensorid=1&data=23.4 &measuredat=2007-20-14 |
---|
| 7 | |
---|
| 8 | |
---|
| 9 | #------------------------------------------ |
---|
| 10 | # Tainted mode 0-4 |
---|
| 11 | $SAFE=1 |
---|
| 12 | |
---|
| 13 | #------------------------------------------ |
---|
| 14 | require 'mysql' |
---|
| 15 | require 'cgi' |
---|
| 16 | require 'logger' |
---|
| 17 | require 'time' |
---|
| 18 | |
---|
| 19 | # Standard header |
---|
| 20 | print "Content-type: text/plain\n\n" |
---|
| 21 | |
---|
| 22 | # Date 14 07 2009 |
---|
| 23 | day = Time.now.strftime("%d") |
---|
| 24 | month = Time.now.strftime("%m") |
---|
| 25 | year = Time.now.strftime("%Y") |
---|
| 26 | |
---|
| 27 | # CGI - GET-Parameter |
---|
| 28 | cgi = CGI.new |
---|
| 29 | |
---|
| 30 | if cgi.has_key?('sensorid') |
---|
| 31 | sensorid = Integer(cgi['sensorid']) |
---|
| 32 | end |
---|
| 33 | if cgi.has_key?('data') |
---|
| 34 | data = Float(cgi['data']) |
---|
| 35 | end |
---|
| 36 | if cgi.has_key?('logyear') |
---|
| 37 | logyear = Integer(cgi['logyear']) |
---|
| 38 | yearbefore = logyear - 1 |
---|
| 39 | end |
---|
| 40 | |
---|
| 41 | |
---|
| 42 | # Logger |
---|
| 43 | log = Logger.new(STDOUT) |
---|
| 44 | log.level = Logger::INFO |
---|
| 45 | |
---|
| 46 | |
---|
| 47 | begin |
---|
| 48 | dbh = Mysql.real_connect("localhost", "hhw", "", "kraftwerk") |
---|
| 49 | |
---|
| 50 | # data provided?, insert - no data select->display |
---|
| 51 | if sensorid and data |
---|
| 52 | dbh.query( %Q"insert into sensor (sensorid, data, measuredat) values (#{sensorid.to_s}, #{data.to_s}, now())" ) |
---|
| 53 | log.info("Inserted sensor: #{sensorid} with data: #{data}") |
---|
| 54 | else |
---|
| 55 | # Auswertung Gesamtes Jahr, Monat, Vergleich Vorjahr |
---|
| 56 | myquery = %Q{ select sum(data), 'Liter Verbrauch in #{month}-#{logyear}' \ |
---|
| 57 | from sensor \ |
---|
| 58 | where year(measuredat) = '#{logyear}' and month(measuredat) = '#{month}' |
---|
| 59 | union |
---|
| 60 | select sum(data), 'Liter Verbrauch in #{month}-#{yearbefore}\n' \ |
---|
| 61 | from sensor \ |
---|
| 62 | where year(measuredat) = '#{yearbefore}' and month(measuredat) = '#{month}' |
---|
| 63 | union \ |
---|
| 64 | select sum(data), 'Liter Gesamtverbrauch in #{logyear}' \ |
---|
| 65 | from sensor \ |
---|
| 66 | where year(measuredat) = '#{logyear}' |
---|
| 67 | union |
---|
| 68 | select sum(data), 'Liter Gesamtverbrauch in #{yearbefore}\n' \ |
---|
| 69 | from sensor \ |
---|
| 70 | where year(measuredat) = '#{yearbefore}' |
---|
| 71 | union |
---|
| 72 | select truncate(avg(data),2), 'Durchschnittsverbrauch Gesamtjahr Liter/Tag' \ |
---|
| 73 | from \ |
---|
| 74 | (select sum(data) as data from sensor \ |
---|
| 75 | group by month(measuredat), day(measuredat)) \ |
---|
| 76 | as foo |
---|
| 77 | union |
---|
| 78 | select truncate(avg(data),2), 'Durchschnittsverbrauch Sommmer Liter/Tag' \ |
---|
| 79 | from \ |
---|
| 80 | (select sum(data) as data from sensor \ |
---|
| 81 | where month(measuredat) in (4,5,6,7,8,9) \ |
---|
| 82 | group by month(measuredat), day(measuredat)) \ |
---|
| 83 | as foo |
---|
| 84 | union |
---|
| 85 | select truncate(avg(data),2), 'Durchschnittsverbrauch Winter Liter/Tag' \ |
---|
| 86 | from \ |
---|
| 87 | (select sum(data) as data from sensor \ |
---|
| 88 | where month(measuredat) in (1,2,3,10,11,12) \ |
---|
| 89 | group by month(measuredat), day(measuredat)) \ |
---|
| 90 | as foo } |
---|
| 91 | res = dbh.query( myquery ) |
---|
| 92 | while row = res.fetch_row do |
---|
| 93 | printf "%s\t%s\n", row[0], row[1] |
---|
| 94 | end |
---|
| 95 | puts "\n\n" |
---|
| 96 | # Auswertung Monat. |
---|
| 97 | res = dbh.query( %Q{ select month(measuredat), '#{logyear}', sum(data) \ |
---|
| 98 | from sensor \ |
---|
| 99 | where year(measuredat) = '#{logyear}'\ |
---|
| 100 | group by month(measuredat) |
---|
| 101 | order by month(measuredat) desc } ) |
---|
| 102 | puts "Datum\t\tVerbrauch (Liter)" |
---|
| 103 | puts "=======================================================================================================================================================" |
---|
| 104 | while row = res.fetch_row do |
---|
| 105 | chart = (row[2].to_i / 1200) |
---|
| 106 | printf "%s-%s\t\t%s\t", row[0], row[1], row[2] |
---|
| 107 | chart.times {print "#"} |
---|
| 108 | print "\n" |
---|
| 109 | end |
---|
| 110 | puts "\n\n" |
---|
| 111 | # Auswertung Jahr: Kummuliert nach Tagen, Monat. |
---|
| 112 | res = dbh.query( %Q{ select day(measuredat), month(measuredat), '#{logyear}', sum(data) \ |
---|
| 113 | from sensor \ |
---|
| 114 | where year(measuredat) = '#{logyear}'\ |
---|
| 115 | group by month(measuredat), day(measuredat) \ |
---|
| 116 | order by month(measuredat) desc, day(measuredat) desc } ) |
---|
| 117 | puts "Datum\t\tVerbrauch (Liter)" |
---|
| 118 | puts "=======================================================================================================================================================" |
---|
| 119 | while row = res.fetch_row do |
---|
| 120 | chart = (row[3].to_i / 70) |
---|
| 121 | printf "%s-%s-%s\t%s\t", row[0], row[1], row[2], row[3] |
---|
| 122 | chart.times {print "#"} |
---|
| 123 | print "\n" |
---|
| 124 | end |
---|
| 125 | res.free |
---|
| 126 | end |
---|
| 127 | |
---|
| 128 | rescue Mysql::Error => e |
---|
| 129 | log.fatal("Error code: #{e.errno}") |
---|
| 130 | log.fatal("Error message: #{e.error}") |
---|
| 131 | if e.respond_to?("sqlstate") |
---|
| 132 | log.fatal("Error SQLSTATE: #{e.sqlstate}") |
---|
| 133 | end |
---|
| 134 | ensure |
---|
| 135 | # disconnect from server |
---|
| 136 | dbh.close if dbh |
---|
| 137 | end |
---|