From: Roman Bazalevsky Date: Mon, 17 Mar 2014 16:21:55 +0000 (+0400) Subject: Web interface and MYSQL structures added X-Git-Url: https://git.rvb.name/weathermon.git/commitdiff_plain/3a48d89e76fd2ab87dabf08632474c529cf77ad4 Web interface and MYSQL structures added --- 3a48d89e76fd2ab87dabf08632474c529cf77ad4 diff --git a/Temp_DHT.ino b/Temp_DHT.ino new file mode 100644 index 0000000..df9d998 --- /dev/null +++ b/Temp_DHT.ino @@ -0,0 +1,201 @@ +/* YourDuino.com Example Software Sketch + DHT11 Humidity and Temperature Sensor test + Credits: Rob Tillaart + http://arduino-direct.com/sunshop/index.php?l=product_detail&p=162 + terry@yourduino.com */ + +/*-----( Import needed libraries )-----*/ +#include +#include +#include +#include + +/*-----( Declare objects )-----*/ +dht11 DHT11; +BMP085 bmp; + +/*-----( Declare Constants, Pin Numbers )-----*/ +#define DHT11PIN 4 + +#define RF_IN 3 + +#define REDPIN 11 +#define GREENPIN 12 + +volatile byte got_interval = 0; +volatile byte interval = 0; + +volatile unsigned long old = 0, packet_count = 0; +volatile unsigned long spacing, now, average_interval; + +WeatherSensorWH2 weather; + +ISR(TIMER1_COMPA_vect) +{ + static byte count = 0; + static byte was_hi = 0; + + if (digitalRead(RF_IN) == HIGH) { + count++; + was_hi = 1; + } else { + if (was_hi) { + was_hi = 0; + interval = count; + got_interval = 1; + count = 0; + } + } +} + +void setup() /*----( SETUP: RUNS ONCE )----*/ +{ + Serial.begin(9600); + Serial.println("STATUS:START"); + + bmp.begin(); + + pinMode(REDPIN,OUTPUT); + pinMode(GREENPIN,OUTPUT); + + pinMode(RF_IN, INPUT); + TCCR1A = 0x00; + TCCR1B = 0x09; + TCCR1C = 0x00; + OCR1A = 399; + TIMSK1 = 0x02; + sei(); + +}/*--(end setup )---*/ + +unsigned long previousMillis = 0; +unsigned long indoor_interval = 60000; +unsigned long outdoor_interval = 45000; +unsigned long previousIndoor = 0; +unsigned long previousOutdoor = 0; + +void loop() /*----( LOOP: RUNS CONSTANTLY )----*/ +{ + + byte i; + byte *packet; + + if (got_interval) { + weather.accept(interval); + if (weather.acquired()) { + now = millis(); + spacing = now - old; + old = now; + packet_count++; + average_interval = now / packet_count; + /* + Serial.print("Spacing: "); + Serial.println(spacing, DEC); + Serial.print("Packet count: "); + Serial.println(packet_count, DEC); + + Serial.print("Average spacing: "); + Serial.println(average_interval, DEC); + + + packet = weather.get_packet(); + for(i=0;i<5;i++) { + Serial.print("0x"); + Serial.print(packet[i], HEX); + Serial.print("/"); + Serial.print(packet[i], DEC); + Serial.print(" "); + } + + Serial.print("crc: "); + Serial.print(weather.calculate_crc(), HEX); + Serial.println((weather.valid() ? " OK" : " BAD")); + + */ + + if (weather.valid()) { + + Serial.print("SENSOR:TYPE=OUTDOOR,"); + + Serial.print("ID="); + Serial.print(weather.get_sensor_id(), HEX); + + Serial.print(",HUMIDITY="); + Serial.print(weather.get_humidity(), DEC); + + Serial.print(",TEMPERATURE="); + Serial.println(weather.get_temperature_formatted()); + + previousOutdoor = millis(); + digitalWrite(REDPIN,HIGH); + + } else { + + Serial.println("ERROR:OUTDOOR"); + previousOutdoor = millis(); + digitalWrite(REDPIN,LOW); + + } + + } + + got_interval = 0; + + } + + + if ((unsigned long)(millis() - previousMillis) >= indoor_interval) { + + previousMillis = millis(); + + int chk = DHT11.read(DHT11PIN); + + if (chk==0) { + + Serial.print("SENSOR:TYPE=INDOOR,"); + Serial.print("HUMIDITY="); + Serial.println((float)DHT11.humidity, 2); + +// Serial.print(",TEMPERATURE="); +// Serial.println((float)DHT11.temperature, 2); + + Serial.print("SENSOR:TYPE=BARO,"); + Serial.print("PRESSURE="); + Serial.print(bmp.readPressure()); + Serial.print(",TEMPERATURE="); + Serial.println(bmp.readTemperature()); + + previousIndoor = millis(); + digitalWrite(GREENPIN,HIGH); + + + } else { + + Serial.println("ERROR:INDOOR"); + previousIndoor = millis(); + digitalWrite(GREENPIN,LOW); + + } + + } + + if ((unsigned long)(millis() - previousIndoor) >= indoor_interval*10) { + + Serial.println("ERROR:INDOOR TIMEOUT"); + previousIndoor = millis(); + digitalWrite(GREENPIN,LOW); + + } + + if ((unsigned long)(millis() - previousOutdoor) >= outdoor_interval*10) { + + Serial.println("ERROR:OUTDOOR TIMEOUT"); + previousOutdoor = millis(); + digitalWrite(REDPIN,LOW); + + } + + +}/* --(end main loop )-- */ + +/* ( THE END ) */ diff --git a/mysql/mysql.dump b/mysql/mysql.dump new file mode 100644 index 0000000..46e6ffd --- /dev/null +++ b/mysql/mysql.dump @@ -0,0 +1,160 @@ +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `sensor_types` +-- + +DROP TABLE IF EXISTS `sensor_types`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sensor_types` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `st_name` varchar(32) CHARACTER SET utf8 NOT NULL, + `st_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `st_name` (`st_name`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `sensor_values` +-- + +DROP TABLE IF EXISTS `sensor_values`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sensor_values` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `sensor_id` int(11) NOT NULL, + `timestamp` datetime NOT NULL, + `value` float NOT NULL, + `parameter_id` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_sensor_values_sens_idx` (`sensor_id`), + KEY `sensor_values_idx` (`sensor_id`,`timestamp`), + KEY `fk_sensor_values_param_idx` (`parameter_id`), + CONSTRAINT `fk_sensor_values_param` FOREIGN KEY (`parameter_id`) REFERENCES `st_parameters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT `fk_sensor_values_sens` FOREIGN KEY (`sensor_id`) REFERENCES `sensors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=223855 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `sensors` +-- + +DROP TABLE IF EXISTS `sensors`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sensors` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `st_id` int(11) NOT NULL, + `s_id` varchar(32) CHARACTER SET utf8 NOT NULL, + `s_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `UNIQUE` (`st_id`,`s_id`), + KEY `fk_sensors_st_idx` (`st_id`), + CONSTRAINT `fk_sensors_st` FOREIGN KEY (`st_id`) REFERENCES `sensor_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `st_parameters` +-- + +DROP TABLE IF EXISTS `st_parameters`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `st_parameters` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `st_id` int(11) NOT NULL, + `st_name` varchar(32) CHARACTER SET utf8 NOT NULL, + `st_unit` int(11) NOT NULL, + `st_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, + `st_dot_color` varchar(45) DEFAULT 'red', + `st_fill_color_top` varchar(45) DEFAULT 'red', + `st_fill_color_bottom` varchar(45) DEFAULT 'blue', + `st_line_color` varchar(45) DEFAULT 'navy', + PRIMARY KEY (`id`), + UNIQUE KEY `unique` (`st_id`,`st_name`), + KEY `fk_st_parameters_st_idx` (`st_id`), + KEY `fk_st_parameters_units_idx` (`st_unit`), + CONSTRAINT `fk_st_parameters_st` FOREIGN KEY (`st_id`) REFERENCES `sensor_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT `fk_st_parameters_units` FOREIGN KEY (`st_unit`) REFERENCES `units` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `unit_conv` +-- + +DROP TABLE IF EXISTS `unit_conv`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `unit_conv` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `from_unit` int(11) NOT NULL, + `to_unit` int(11) NOT NULL, + `a` float NOT NULL, + `b` float NOT NULL, + `c` float NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `unit_groups` +-- + +DROP TABLE IF EXISTS `unit_groups`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `unit_groups` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(45) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `units` +-- + +DROP TABLE IF EXISTS `units`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `units` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(32) CHARACTER SET utf8 NOT NULL, + `name_short` varchar(8) CHARACTER SET utf8 NOT NULL, + `unit_group` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_units_group_idx` (`unit_group`), + CONSTRAINT `fk_units_group` FOREIGN KEY (`unit_group`) REFERENCES `unit_groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:17:36 diff --git a/weathermon b/weathermon new file mode 100755 index 0000000..8c94dd0 --- /dev/null +++ b/weathermon @@ -0,0 +1,268 @@ +#!/usr/bin/python + +import serial + +from os import listdir +from os.path import isfile, join + +from termios import tcflush, TCIOFLUSH + +from time import sleep,time + +from uuid import getnode + +from hashlib import md5 + +import socket + +import sys,traceback + +import pycurl +from urllib import urlencode +from StringIO import StringIO + +searchpath = '/dev/serial/by-id/' +baud = 9600 +timeout = 5 + +external_submit_interval = 320 +expire_interval = 1200 +submit_time = time() +submit_queue = {} + +import MySQLdb +import ConfigParser + +def find_port(): + + global serial_num + + files = listdir(searchpath) + for f in files: + if serialnum in f: + return join(searchpath,f) + return None + +def open_port(path): + + ser = serial.Serial(path,baud,timeout) + if ser.portstr: + tcflush(ser,TCIOFLUSH); + return ser + +def read_port(ser): + + line = ser.readline() + return line.strip() + +def read_loop(ser,callback): + + while True: + + try: + line=read_port(ser) + if line: + callback(line) + except KeyboardInterrupt: + break + finally: + None + +def submit_narodmon(queue): + + param = { 'ID':"{:X}".format(getnode())} + + for sensor in queue: + value = submit_queue[sensor]['val'] + timestamp = submit_queue[sensor]['timestamp'] + digest = md5(sensor).hexdigest()[:18] + param[digest] = value; + + print param + + url = "http://narodmon.ru/post.php" + + try: + + response_buffer = StringIO() + curl = pycurl.Curl() + + curl.setopt(curl.URL, url) + curl.setopt(curl.WRITEFUNCTION, response_buffer.write) + curl.setopt(curl.POST, 1) + curl.setopt(curl.POSTFIELDS, urlencode(param)) + + curl.perform() + curl.close() + + response_value = response_buffer.getvalue() + + print 'Content: ', response_value + + return True + + except: + + exc_type, exc_value, exc_traceback = sys.exc_info() + traceback.print_tb(exc_traceback, limit=1, file=sys.stdout) + traceback.print_exception(exc_type, exc_value, exc_traceback, + limit=2, file=sys.stdout) + return False + +def submit_owm(queue): + + url = "http://openweathermap.org/data/post" + params = {'name':owm_station, 'lat':owm_lat, 'long':owm_lon} + + try: + + try: + params['temp'] = queue[owm_temp]['val'] + params['pressure'] = queue[owm_pres]['val'] + params['humidity'] = queue[owm_humi]['val'] + except: + return False + + response_buffer = StringIO() + curl = pycurl.Curl() + + curl.setopt(curl.URL, url) + curl.setopt(curl.USERPWD, '%s:%s' % (owmuser, owmpasswd)) + curl.setopt(curl.WRITEFUNCTION, response_buffer.write) + curl.setopt(curl.POST, 1) + curl.setopt(curl.POSTFIELDS, urlencode(params)) + + curl.perform() + curl.close() + + response_value = response_buffer.getvalue() + + print 'Content: ', response_value + + return True + + except: + + exc_type, exc_value, exc_traceback = sys.exc_info() + traceback.print_tb(exc_traceback, limit=1, file=sys.stdout) + traceback.print_exception(exc_type, exc_value, exc_traceback, + limit=2, file=sys.stdout) + return False + +def purge_queue(): + global submit_queue + clean = [] + for key in submit_queue: + if submit_queue[key]['timestamp'] < time()-expire_interval: + print "Expired value for "+key + clean.append(key) + for i in clean: + del submit_queue[i] + +def submit_data(sensor_type,sensor_id,sensor_param,param_value): + global submit_time + global submit_queue + c = database.cursor() + c.execute('CALL meteo.submit_value(%s,%s,%s,%s)', (sensor_type,sensor_id,sensor_param,param_value)) + database.commit() + submit_queue[sensor_type+'.'+sensor_id+'.'+sensor_param]={'val':param_value,'timestamp':time()} + if time()>submit_time+external_submit_interval: + if submit_narodmon(submit_queue): + if owmuser: + submit_owm(submit_queue) + print 'Purging queue...' + submit_time=time() + purge_queue() + + +def process_str(str): + try: + msg_type, msg_body = str.split(':') + if msg_type == 'STATUS': + print 'Status: ', msg_body + elif msg_type == 'ERROR': + print 'Error: ', msg_body + elif msg_type == 'SENSOR': + sens = msg_body.split(',') + sensor = {} + sensor_type = None + sensor_id = None + for rec in sens: + key,value = rec.split('=') + if key == 'TYPE': + sensor_type = value + elif key == 'ID': + sensor_id = value + else: + sensor[key] = value + if sensor_type: + if not sensor_id: + sensor_id='DEFAULT'; + for key in sensor: + print 'Type = ', sensor_type, ', ID = ', sensor_id, ', Param = ', key, ', Value = ', sensor[key] + submit_data(sensor_type,sensor_id,key,sensor[key]) + except: + print 'Exception processing...' + try: + database.close() + except: + None + reconnect() + +def print_str(str): + print str + +def weather_mon(): + + path = find_port() + ser = open_port(path) + read_loop(ser,process_str) + +def reconnect(): + + try: + + global database + database = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpasswd,use_unicode=True,connect_timeout=10) + database.set_character_set('utf8') + c = database.cursor() + c.execute('SET NAMES utf8;') + print "Database connected..." + + except: + + print "Error connecting database" + sleep(30) + +def main(): + weather_mon() + +try: + + cfg = ConfigParser.RawConfigParser(allow_no_value=True) + cfg.readfp(open('/etc/weathermon.conf')) + dbhost = cfg.get("mysql","host") + dbuser = cfg.get("mysql","user") + dbpasswd = cfg.get("mysql","passwd") + serialnum = cfg.get("serial","id") + owmuser = cfg.get("openweathermap","user") + owmpasswd = cfg.get("openweathermap",'passwd') + if owmuser: + owm_temp = cfg.get("openweathermap",'temp') + owm_pres = cfg.get("openweathermap",'pres') + owm_humi = cfg.get("openweathermap",'humi') + owm_lat = cfg.get("openweathermap",'lat') + owm_lon = cfg.get("openweathermap",'lon') + owm_station = cfg.get("openweathermap",'station') + reconnect() + +except: + + print "Cannot intialize system" + exit() + +if __name__ == "__main__": + import sys + reload(sys) + sys.setdefaultencoding('utf-8') + main() diff --git a/web/archive.php b/web/archive.php new file mode 100644 index 0000000..76708f7 --- /dev/null +++ b/web/archive.php @@ -0,0 +1,254 @@ + + + + + + WeatherMon (архив) + + + + false)))) { + die($err); + } + +$db -> exec('SET CHARACTER SET utf8'); + +if (! $year) { + + $q = $db -> prepare( + 'select + date_format(timestamp,\'%Y\') as year + from + sensor_values + group by year + order by year'); + $q -> execute(); +?> +

Архив метеоданных

+ fetch(PDO::FETCH_ASSOC)) { +?> + Данные за год
+ prepare( + 'select + date_format(timestamp,\'%m\') as month, + date_format(timestamp,\'%d\') as day + from + sensor_values + where + timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and + timestamp execute(); +?> +

год

+ fetchAll(PDO::FETCH_ASSOC); + $months = []; + foreach ($data as $row) { + $days [$year.$row['month'].$row['day']] = '&month='.$row['month'].'&day='.$row['day']; + $months[$row['month']]=1; + } + $m = 1; + echo ''; + for ($i=1; $i<=3; $i++) { + echo ''; + for ($j=1; $j<=4; $j++) { + echo ''; + $m++; + } + echo ''; + } + echo '
'; + $month=sprintf('%02d',$m); + $month_name = strftime('%B',mktime(0,0,0,$month,1,$year)); + if (!empty($months[$month])) { + echo '

'.$month_name.'

'; + } else { + echo '

'.$month_name.'

'; + } + calendar($year,$month,$days,'?year='.$year.'%s',0); + echo '
'; + + $next_year = $year+1; + + $q = $db -> prepare ( + 'select + s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name, + count(distinct date_format(v.timestamp,\'%d\')) cnt, + round(min(v.value),1) min_value, + round(max(v.value),1) max_value, + u.name_short unit + from + sensor_values v,sensors s,st_parameters p,units u + where + v.sensor_id=s.id and + v.parameter_id=p.id and + p.st_unit=u.id and + v.timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and + v.timestamp execute(); + $sensors = $q -> fetchAll(PDO::FETCH_ASSOC); + + foreach ($sensors as $sensor) { + + echo '

'.$sensor['param_name'].'('.$sensor['sensor_name'].')

'; + echo ''; + echo ''; + echo '
'; + echo 'Минимальное значение за год '.$sensor['min_value'].' '.$sensor['unit'].'
'; + echo 'Максимальное значение за год '.$sensor['max_value'].' '.$sensor['unit'].'
'; + echo '
'; + + if ($sensor['cnt']>1) { + + echo '
'; + + } + + } + + + } else { + + if (!$day) { + + $next_year = $year; + $next_month = $month+1; + + if ($next_month == 13) { + $next_month = 1; + $next_year = $next_year+1; + } + + $next_month = sprintf('%02d',$next_month); + $next_year = sprintf('%04d',$next_year); + $month = sprintf('%02d',$month); + $year = sprintf('%04d',$year); + + $q = $db -> prepare( + 'select + date_format(timestamp,\'%d\') as day + from + sensor_values + where + timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and + timestamp execute(); + + $data = $q -> fetchAll(PDO::FETCH_ASSOC); + + $month_name = strftime('%B %Y',mktime(0,0,0,$month,1,$year)); + +?> +

+ prepare ( + 'select + s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name, + count(distinct date_format(v.timestamp,\'%d\')) cnt, + round(min(v.value),1) min_value, + round(max(v.value),1) max_value, + u.name_short unit + from + sensor_values v,sensors s,st_parameters p,units u + where + v.sensor_id=s.id and + v.parameter_id=p.id and + p.st_unit=u.id and + v.timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and + v.timestamp execute(); + $sensors = $q -> fetchAll(PDO::FETCH_ASSOC); + + foreach ($sensors as $sensor) { + + echo '

'.$sensor['param_name'].'('.$sensor['sensor_name'].')

'; + echo ''; + echo ''; + echo '
'; + echo 'Минимальное значение за месяц '.$sensor['min_value'].' '.$sensor['unit'].'
'; + echo 'Максимальное значение за месяц '.$sensor['max_value'].' '.$sensor['unit'].'
'; + echo '
'; + + if ($sensor['cnt']>1) { + + echo '
'; + + } + + } + + } else { + + $month = sprintf('%02d',$month); + $year = sprintf('%04d',$year); + $day = sprintf('%02d',$day); + + $q = $db -> prepare( + 'select + distinct v.sensor_id,s.s_description,p.id as param_id,p.st_description + from + sensor_values v,st_parameters p,sensors s + where + v.timestamp>=str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\') and + v.timestamp execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + + echo '

'.$row['s_description'].'/'.$row['st_description'].'

'; +?> +
+ + + \ No newline at end of file diff --git a/web/calendar.php b/web/calendar.php new file mode 100644 index 0000000..6dbef05 --- /dev/null +++ b/web/calendar.php @@ -0,0 +1,87 @@ + $dayofmonth) break; + + } + + if($day_count > $dayofmonth) break; + + } + + echo ''; + + for($i = 0; $i < count($week); $i++) { + + echo ''; + + for($j = 0; $j < 7; $j++) { + + if(!empty($week[$i][$j])) { + $str = $week[$i][$j]; + $fstr=''.$str.''; + if (!empty($days[$year.$month.sprintf('%02d',$str)])) { + $href=sprintf($ref_template,$days[$year.$month.sprintf('%02d',$str)]); + if($j == 5 || $j == 6) { + echo ''; + } else { + echo ''; + } + } else { + if($j == 5 || $j == 6) { + echo ''; + } else { + echo ''; + } + } + } + + else echo ''; + + } + + echo ''; + + } + + echo '
'.$fstr.''.$fstr.''.$fstr.''.$fstr.' 
'; + +} + +?> \ No newline at end of file diff --git a/web/config_local.php b/web/config_local.php new file mode 100644 index 0000000..c087aea --- /dev/null +++ b/web/config_local.php @@ -0,0 +1,12 @@ + + \ No newline at end of file diff --git a/web/favicon.png b/web/favicon.png new file mode 100644 index 0000000..a8081d8 Binary files /dev/null and b/web/favicon.png differ diff --git a/web/favicon.svg b/web/favicon.svg new file mode 100644 index 0000000..7d502ad --- /dev/null +++ b/web/favicon.svg @@ -0,0 +1,15 @@ + + + + + + + image/svg+xml + + + + + + + + diff --git a/web/image.php b/web/image.php new file mode 100644 index 0000000..2a2d2f3 --- /dev/null +++ b/web/image.php @@ -0,0 +1,177 @@ + false)))) { + + die('Не могу подключиться к БД'); + +} + +$db -> exec('SET CHARACTER SET utf8'); + +$type = $_REQUEST['type']; +$sensor=$_REQUEST['sensor']; +$param=$_REQUEST['param']; + +if ($type and $param) { + + $sensor = intval($sensor); + $param = intval($param); + + $q = $db -> prepare( + 'select s_description from sensors where id='.$sensor + ); + $q -> execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + $sensor_name = $row['s_description']; + } + + $q = $db -> prepare( + 'select st.st_dot_color,st.st_line_color,st.st_description,u.id,u.unit_group from st_parameters st,units u where st.id='.$param.' and st.st_unit=u.id' + ); + $q -> execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + $param_name = $row['st_description']; + $from_unit = $row['id']; + $unit_group = $row['unit_group']; + $dot_color = $row['st_dot_color']; + $line_color = $row['st_line_color']; + } + + if (!empty($_COOKIE['unit_'.$unit_group])) { + $to_unit=intval($_COOKIE['unit_'.$unit_group]); + } else { + $to_unit=$from_unit; + } + + $q = $db -> prepare( + 'select u.name_short from units u where u.id='.$to_unit + ); + $q -> execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + $param_unit = $row['name_short']; + } + + $xdata = array(); + $ydata = array(); + + if ($type == 'last24') { + + $q = $db -> prepare( + 'select unix_timestamp(timestamp) as x,unitconv(value,'.$from_unit.','.$to_unit.') as y from sensor_values where timestamp>adddate(now(), -1) and sensor_id='.$sensor.' and parameter_id='.$param.' order by timestamp' + ); + + $height = 130; + + } elseif ($type == 'range') { + + $from = intval($_REQUEST['fromdate']); + $to = intval($_REQUEST['todate']); + + $q = $db -> prepare( + 'select unix_timestamp(timestamp) as x,unitconv(value,'.$from_unit.','.$to_unit.') as y from sensor_values where timestamp>=str_to_date("'.$from.'","%Y%m%d%H%i%s") and timestamp<=str_to_date("'.$to.'","%Y%m%d%H%i%s") and sensor_id='.$sensor.' and parameter_id='.$param.' order by timestamp' + ); + + $height = 60; + + } + + $q -> execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + + $xdata[] = $row['x']; + $ydata[] = $row['y']; + + } + + + for ($i = 0; $i < count($xdata); ++$i) { + + $total_weight=0; + $sum=0; + $maxdelta = 900; + + for ($j = $i; $j < count($xdata); ++$j) { + + $delta = abs($xdata[$i]-$xdata[$j]); + if ($delta > $maxdelta) { break; } + + $weight = 1-$delta/$maxdelta; + $total_weight += $weight; + $sum += $weight*$ydata[$j]; + + } + + for ($j = $i-1; $j >=0 ; --$j) { + + $delta = abs($xdata[$i]-$xdata[$j]); + if ($delta > $maxdelta) { break; } + + $weight = 1-$delta/$maxdelta; + $total_weight += $weight; + $sum += $weight*$ydata[$j]; + + } + + $new_val = $sum/$total_weight; + $f_ydata[$i] = $new_val; + + } + + // Create the graph + $g = new Graph(640,480); + $g->graph_theme = null; + + //$g->img->SetAntiAliasing(); + + // We need a linlin scale since we provide both + // x and y coordinates for the data points. + $g->SetScale('datlin'); + $g->xaxis->SetLabelAngle(90); + $g->xaxis->SetPos("min"); + + // We use a scatterplot to illustrate the original + // contro points. + $splot = new ScatterPlot($ydata,$xdata); + $g->Add($splot); + + // + $splot->mark->SetFillColor($dot_color); + $splot->mark->SetColor($dot_color); + $splot->mark->SetType(MARK_FILLEDCIRCLE); + $splot->mark->SetSize(2); + + $fplot = new LinePlot($f_ydata,$xdata); + $g->Add($fplot); + $fplot->SetColor($line_color); + $fplot->SetWeight(2); + + $g->SetMargin(50,30,40,$height); + $g->title->Set($sensor_name.'/'.$param_name.', '.$param_unit); + $g->title->SetFont(FF_DV_SANSSERIF,FS_BOLD,12); + $g->SetMarginColor('silver'); + + // Add the plots to the graph and stroke + $g->Stroke(); + +} else { + + header("Content-Type: text/html; charset=UTF-8"); + die('Сенсор не выбран!'); + +} + +?> \ No newline at end of file diff --git a/web/image_minmax.php b/web/image_minmax.php new file mode 100644 index 0000000..a0a6326 --- /dev/null +++ b/web/image_minmax.php @@ -0,0 +1,192 @@ + false)))) { + + die('Не могу подключиться к БД'); + +} + +$db -> exec('SET CHARACTER SET utf8'); + +$type = $_REQUEST['type']; +$sensor=$_REQUEST['sensor']; +$param=$_REQUEST['param']; +$year=$_REQUEST['year']; +$month=$_REQUEST['month']; + +if ($type and $param) { + + $sensor = intval($sensor); + $param = intval($param); + + $q = $db -> prepare( + 'select s_description from sensors where id='.$sensor + ); + $q -> execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + $sensor_name = $row['s_description']; + } + + $q = $db -> prepare( + 'select st.st_fill_color_top,st.st_fill_color_bottom,st.st_description,u.id,u.unit_group from st_parameters st,units u where st.id='.$param.' and st.st_unit=u.id +' + ); + $q -> execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + $param_name = $row['st_description']; + $from_unit = $row['id']; + $unit_group = $row['unit_group']; + $fill_color_top=$row['st_fill_color_top']; + $fill_color_bottom=$row['st_fill_color_bottom']; + } + + if (!empty($_COOKIE['unit_'.$unit_group])) { + $to_unit=intval($_COOKIE['unit_'.$unit_group]); + } else { + $to_unit=$from_unit; + } + + $q = $db -> prepare( + 'select u.name_short from units u where u.id='.$to_unit + ); + $q -> execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + $param_unit = $row['name_short']; + } + + $xdata = array(); + $ydata = array(); + + if ($type == 'month') { + + $next_year = $year; + $next_month = $month+1; + + if ($month==13) { + + $next_year++; + $month=1; + + } + + $q = $db -> prepare( + ' + select + x, + unitconv(min(value),'.$from_unit.','.$to_unit.') min_value, + unitconv(max(value),'.$from_unit.','.$to_unit.') max_value + from ( + select + unix_timestamp(cast(timestamp as date)) x, + value + from + sensor_values + where + timestamp>=str_to_date(\''.$year.$month.'\',\'%Y%m\') + and timestamp prepare( + ' + select + x, + unitconv(min(value),'.$from_unit.','.$to_unit.') min_value, + unitconv(max(value),'.$from_unit.','.$to_unit.') max_value + from ( + select + unix_timestamp( + DATE_SUB(cast(timestamp as date), INTERVAL DAYOFWEEK(cast(timestamp as date))-1 DAY) + ) x, + value + from + sensor_values + where + timestamp>=str_to_date(\''.$year.'\',\'%Y\') + and timestamp execute(); + + while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + + $xdata[] = $row['x']; + $mindata[] = $row['min_value']; + $maxdata[] = $row['max_value']; + + } + + + // Create the graph + $g = new Graph(640,480); + $g->graph_theme = null; + + //$g->img->SetAntiAliasing(); + + // We need a linlin scale since we provide both + // x and y coordinates for the data points. + $g->SetScale('datlin'); + $g->xaxis->SetLabelAngle(90); + $g->xaxis->SetPos("min"); + + // We use a scatterplot to illustrate the original + // contro points. + + $bplot = new LinePlot($maxdata,$xdata); + $g->Add($bplot); + $bplot->SetColor($fill_color_top); + $bplot->SetFillGradient($fill_color_top,$fill_color_bottom,100,TRUE); + $bplot->SetFillFromYMin(TRUE); + $bplot->SetWeight(4); + + $aplot = new LinePlot($mindata,$xdata); + $g->Add($aplot); + $aplot->SetColor($fill_color_bottom); + $aplot->SetFillGradient($fill_color_bottom,'white',100,TRUE); + $aplot->SetFillFromYMin(TRUE); + $aplot->SetWeight(4); + + $g->SetMargin(60,60,60,130); + $g->title->Set($sensor_name.'/'.$param_name.', '.$param_unit); + $g->title->SetFont(FF_DV_SANSSERIF,FS_BOLD,12); + $g->subtitle->Set('(минимальные и максимальные значения за период)'); + $g->subtitle->SetColor('darkred'); + $g->SetMarginColor('silver'); + + // Add the plots to the graph and stroke + $g->Stroke(); + +} else { + + header("Content-Type: text/html; charset=UTF-8"); + die('Сенсор не выбран!'); + +} + +?> \ No newline at end of file diff --git a/web/index.php b/web/index.php new file mode 100644 index 0000000..d513251 --- /dev/null +++ b/web/index.php @@ -0,0 +1,46 @@ + + + + + + WeatherMon (Последние 24 часа) + + + + false)))) { + die($err); + } + +$db -> exec('SET CHARACTER SET utf8'); + +$q = $db -> prepare( + 'select + distinct v.sensor_id,s.s_description,p.id as param_id,p.st_description + from + sensor_values v,st_parameters p,sensors s + where + v.timestamp>adddate(now(), -1) + and v.sensor_id=s.id + and s.st_id=p.st_id' + ); +$q -> execute(); + +while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + + echo '

'.$row['s_description'].'/'.$row['st_description'].'

'; +?> +
+ +Архивные данные +
+Настройки + + \ No newline at end of file diff --git a/web/setup.php b/web/setup.php new file mode 100644 index 0000000..8fb989d --- /dev/null +++ b/web/setup.php @@ -0,0 +1,114 @@ + false)))) { + + die('Не могу подключиться к БД'); + +} + +$db -> exec('SET CHARACTER SET utf8'); + +$selected = []; + +if ($_REQUEST['action']=='submit') { + + foreach($_REQUEST as $key=>$value) { + + if (strpos($key,'unit_')===0) { + + $group_id=intval(substr($key,5)); + $unit_id=$value; + setcookie('unit_'.$group_id,$unit_id,time()+86400*365*10); + $selected[$group_id]=$unit_id; + + } + + } + +?> + + + + + + WeatherMon (Настройки) + + + +

Сохранено...

+ +$value) { + + if (strpos($key,'unit_')===0) { + + $group_id=intval(substr($key,5)); + $unit_id=$value; + setcookie('unit_'.$group_id,$unit_id); + $selected[$group_id]=$unit_id; + + } + + } + +?> + + + + + + WeatherMon (Настройки) + + + + + prepare( + 'select id,name,(select count(*) from units where unit_group=g.id) cnt from unit_groups g' +); +$q -> execute(); + +echo '
'; +while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { + $group_id = $row['id']; + $group_name = $row['name']; + $group_cnt = $row['cnt']; + + echo '

'.$group_name.'

'; + + $q_p = $db -> prepare( + 'select id,name from units where unit_group='.$group_id + ); + + $q_p->execute(); + + echo '
'; +} +echo ''; +echo '

'; +echo '
'; + +} + +?> + + \ No newline at end of file