source: de.wigbels.ruby/hothotwrite/hhw.rb@ 0955336

Last change on this file since 0955336 was 7b7d827, checked in by njw <njw@…>, 11 years ago

opened projects: hothotwrite and contact

  • Property mode set to 100755
File size: 5.0 KB
Line 
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#------------------------------------------
14require 'mysql'
15require 'cgi'
16require 'logger'
17require 'time'
18
19# Standard header
20print "Content-type: text/plain\n\n"
21
22# Date 14 07 2009
23day = Time.now.strftime("%d")
24month = Time.now.strftime("%m")
25year = Time.now.strftime("%Y")
26
27# CGI - GET-Parameter
28cgi = CGI.new
29
30if cgi.has_key?('sensorid')
31 sensorid = Integer(cgi['sensorid'])
32end
33if cgi.has_key?('data')
34 data = Float(cgi['data'])
35end
36if cgi.has_key?('logyear')
37 logyear = Integer(cgi['logyear'])
38 yearbefore = logyear - 1
39end
40
41
42# Logger
43log = Logger.new(STDOUT)
44log.level = Logger::INFO
45
46
47begin
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
128rescue 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
134ensure
135 # disconnect from server
136 dbh.close if dbh
137end
Note: See TracBrowser for help on using the repository browser.