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 | years = dbh.query( %Q"select count(distinct year(measuredat)) FROM sensor" ).fetch_row[0] |
---|
56 | # Auswertung Gesamtes Jahr, Monat, Vergleich Vorjahr |
---|
57 | myquery = %Q{ select sum(data), 'Liter Verbrauch in #{month}-#{logyear}' \ |
---|
58 | from sensor \ |
---|
59 | where year(measuredat) = '#{logyear}' and month(measuredat) = '#{month}' |
---|
60 | union |
---|
61 | select sum(data), 'Liter Verbrauch in #{month}-#{yearbefore}\n' \ |
---|
62 | from sensor \ |
---|
63 | where year(measuredat) = '#{yearbefore}' and month(measuredat) = '#{month}' |
---|
64 | union \ |
---|
65 | select sum(data), 'Liter Gesamtverbrauch in #{logyear}' \ |
---|
66 | from sensor \ |
---|
67 | where year(measuredat) = '#{logyear}' |
---|
68 | union |
---|
69 | select sum(data), 'Liter Gesamtverbrauch in #{yearbefore}\n' \ |
---|
70 | from sensor \ |
---|
71 | where year(measuredat) = '#{yearbefore}' |
---|
72 | union |
---|
73 | select truncate(avg(data)/#{years},2), 'Durchschnittsverbrauch Gesamtjahr Liter/Tag' \ |
---|
74 | from \ |
---|
75 | (select sum(data) as data from sensor \ |
---|
76 | group by month(measuredat), day(measuredat)) \ |
---|
77 | as foo |
---|
78 | union |
---|
79 | select truncate(avg(data)/#{years},2), 'Durchschnittsverbrauch Sommmer Liter/Tag' \ |
---|
80 | from \ |
---|
81 | (select sum(data) as data from sensor \ |
---|
82 | where month(measuredat) in (4,5,6,7,8,9) \ |
---|
83 | group by month(measuredat), day(measuredat)) \ |
---|
84 | as foo |
---|
85 | union |
---|
86 | select truncate(avg(data)/#{years},2), 'Durchschnittsverbrauch Winter Liter/Tag' \ |
---|
87 | from \ |
---|
88 | (select sum(data) as data from sensor \ |
---|
89 | where month(measuredat) in (1,2,3,10,11,12) \ |
---|
90 | group by month(measuredat), day(measuredat)) \ |
---|
91 | as foo } |
---|
92 | res = dbh.query( myquery ) |
---|
93 | while row = res.fetch_row do |
---|
94 | printf "%s\t%s\n", row[0], row[1] |
---|
95 | end |
---|
96 | puts "\n\n" |
---|
97 | # Auswertung Monat. |
---|
98 | res = dbh.query( %Q{ select month(measuredat), '#{logyear}', sum(data) \ |
---|
99 | from sensor \ |
---|
100 | where year(measuredat) = '#{logyear}'\ |
---|
101 | group by month(measuredat) |
---|
102 | order by month(measuredat) desc } ) |
---|
103 | puts "Datum\t\tVerbrauch (Liter)" |
---|
104 | puts "=======================================================================================================================================================" |
---|
105 | while row = res.fetch_row do |
---|
106 | chart = (row[2].to_i / 1200) |
---|
107 | printf "%s-%s\t\t%s\t", row[0], row[1], row[2] |
---|
108 | chart.times {print "#"} |
---|
109 | print "\n" |
---|
110 | end |
---|
111 | puts "\n\n" |
---|
112 | # Auswertung Jahr: Kummuliert nach Tagen, Monat. |
---|
113 | res = dbh.query( %Q{ select day(measuredat), month(measuredat), '#{logyear}', sum(data) \ |
---|
114 | from sensor \ |
---|
115 | where year(measuredat) = '#{logyear}'\ |
---|
116 | group by month(measuredat), day(measuredat) \ |
---|
117 | order by month(measuredat) desc, day(measuredat) desc } ) |
---|
118 | puts "Datum\t\tVerbrauch (Liter)" |
---|
119 | puts "=======================================================================================================================================================" |
---|
120 | while row = res.fetch_row do |
---|
121 | chart = (row[3].to_i / 70) |
---|
122 | printf "%s-%s-%s\t%s\t", row[0], row[1], row[2], row[3] |
---|
123 | chart.times {print "#"} |
---|
124 | print "\n" |
---|
125 | end |
---|
126 | res.free |
---|
127 | end |
---|
128 | |
---|
129 | rescue Mysql::Error => e |
---|
130 | log.fatal("Error code: #{e.errno}") |
---|
131 | log.fatal("Error message: #{e.error}") |
---|
132 | if e.respond_to?("sqlstate") |
---|
133 | log.fatal("Error SQLSTATE: #{e.sqlstate}") |
---|
134 | end |
---|
135 | ensure |
---|
136 | # disconnect from server |
---|
137 | dbh.close if dbh |
---|
138 | end |
---|