From e4e1557426f8e5ca10ca616140528939d86426cd Mon Sep 17 00:00:00 2001 From: Roman Bazalevsky Date: Thu, 25 Sep 2014 14:53:33 +0400 Subject: [PATCH] =?utf8?q?1)=20=D0=98=D0=B7=D0=BC=D0=B5=D0=BD=D0=B5=D0=BD?= =?utf8?q?=20Arduino-=D1=81=D0=BA=D0=B5=D1=82=D1=87=20=D0=B4=D0=BB=D1=8F?= =?utf8?q?=20=D1=80=D0=B0=D0=B1=D0=BE=D1=82=D1=8B=20=D0=BD=D0=B0=20Arduino?= =?utf8?q?=20Yun=20=D0=B8=20=D0=B1=D0=BE=D0=BB=D0=B5=D0=B5=20=D1=8D=D1=84?= =?utf8?q?=D1=84=D0=B5=D0=BA=D1=82=D0=B8=D0=B2=D0=BD=D0=BE=D0=B9=20=D0=BE?= =?utf8?q?=D0=B1=D1=80=D0=B0=D0=B1=D0=BE=D1=82=D0=BA=D0=B8=20=D1=80=D0=B0?= =?utf8?q?=D0=B4=D0=B8=D0=BE=D1=81=D0=B8=D0=B3=D0=BD=D0=B0=D0=BB=D0=B0=202?= =?utf8?q?)=20=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D1=8B=20?= =?utf8?q?=D0=BA=D0=B5=D1=88-=D1=82=D0=B0=D0=B1=D0=BB=D0=B8=D1=86=D1=8B=20?= =?utf8?q?=D1=81=20=D0=BC=D0=B8=D0=BD=D0=B8=D0=BC=D0=B0=D0=BB=D1=8C=D0=BD?= =?utf8?q?=D1=8B=D0=BC-=D0=BC=D0=B0=D0=BA=D1=81=D0=B8=D0=BC=D0=B0=D0=BB?= =?utf8?q?=D1=8C=D0=BD=D1=8B=D0=BC=20=D0=B7=D0=BD=D0=B0=D1=87=D0=B5=D0=BD?= =?utf8?q?=D0=B8=D0=B5=D0=BC=20=D0=B7=D0=B0=20=D1=81=D1=83=D1=82=D0=BA?= =?utf8?q?=D0=B8=20=D0=B4=D0=BB=D1=8F=20=D0=B1=D1=8B=D1=81=D1=82=D1=80?= =?utf8?q?=D0=BE=D0=B9=20=D0=BE=D1=82=D1=80=D0=B8=D1=81=D0=BE=D0=B2=D0=BA?= =?utf8?q?=D0=B8=20=D0=B3=D1=80=D0=B0=D1=84=D0=B8=D0=BA=D0=BE=D0=B2=203)?= =?utf8?q?=20=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D0=BE=20?= =?utf8?q?=D0=BF=D1=80=D0=BE=D1=82=D0=BE=D0=BA=D0=BE=D0=BB=D0=B8=D1=80?= =?utf8?q?=D0=BE=D0=B2=D0=B0=D0=BD=D0=B8=D0=B5=20=D0=BE=D1=88=D0=B8=D0=B1?= =?utf8?q?=D0=BE=D0=BA=20=D0=BD=D0=B0=20=D1=81=D1=82=D0=BE=D1=80=D0=BE?= =?utf8?q?=D0=BD=D0=B5=20=D1=81=D0=B5=D1=80=D0=B2=D0=B5=D1=80=D0=B0=204)?= =?utf8?q?=20=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D0=B0=20?= =?utf8?q?=D0=BE=D0=BF=D1=86=D0=B8=D1=8F=20=D0=BB=D0=B4=D1=8F=20=D0=B7?= =?utf8?q?=D0=B0=D0=BF=D0=B8=D1=81=D0=B8=20=D1=81=D0=BE=D0=BE=D0=B1=D1=89?= =?utf8?q?=D0=B5=D0=BD=D0=B8=D0=B9=20=D0=B4=D0=B5=D0=BC=D0=BE=D0=BD=D0=B0?= =?utf8?q?=20=D0=B2=20=D1=81=D0=B8=D1=81=D1=82=D0=B5=D0=BC=D0=BD=D1=8B?= =?utf8?q?=D0=B9=20=D0=BF=D1=80=D0=BE=D1=82=D0=BE=D0=BA=D0=BE=D0=BB=205)?= =?utf8?q?=20=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D0=BE=20?= =?utf8?q?=D0=B8=D1=81=D0=BF=D0=BE=D0=BB=D1=8C=D0=B7=D0=BE=D0=B2=D0=B0?= =?utf8?q?=D0=BD=D0=B8=D0=B5=20=D0=BA=D0=B5=D1=88=D0=B0=20=D0=B8=D0=B7?= =?utf8?q?=D0=BE=D0=B1=D1=80=D0=B0=D0=B6=D0=B5=D0=BD=D0=B8=D0=B9=20=D0=B4?= =?utf8?q?=D0=BB=D1=8F=20=D0=B8=D0=B7=D0=B1=D0=B5=D0=B6=D0=B0=D0=BD=D0=B8?= =?utf8?q?=D1=8F=20=D0=BF=D0=BE=D0=B2=D1=82=D0=BE=D1=80=D0=BD=D0=BE=D0=B9?= =?utf8?q?=20=D0=BE=D1=82=D1=80=D0=B8=D1=81=D0=BE=D0=B2=D0=BA=D0=B8?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- Temp_DHT.ino | 201 ---------------- Weather_WH2.ino | 415 +++++++++++++++++++++++++++++++++ mysql/meteo_calendar.sql | 43 ++++ mysql/meteo_error_log.sql | 40 ++++ mysql/meteo_routines.sql | 56 ++++- mysql/meteo_sensor_types.sql | 10 +- mysql/meteo_sensor_values.sql | 21 +- mysql/meteo_sensors.sql | 20 +- mysql/meteo_sensors_ranges.sql | 48 ++++ mysql/meteo_st_parameters.sql | 12 +- mysql/meteo_unit_conv.sql | 10 +- mysql/meteo_unit_groups.sql | 10 +- mysql/meteo_units.sql | 10 +- weathermon | 101 +++++--- web/archive.php | 91 +++++--- web/config_local.php | 11 +- web/image.php | 34 ++- web/image_minmax.php | 83 +++++-- web/index.php | 11 +- web/setup.php | 4 +- 20 files changed, 860 insertions(+), 371 deletions(-) delete mode 100644 Temp_DHT.ino create mode 100644 Weather_WH2.ino create mode 100644 mysql/meteo_calendar.sql create mode 100644 mysql/meteo_error_log.sql create mode 100644 mysql/meteo_sensors_ranges.sql diff --git a/Temp_DHT.ino b/Temp_DHT.ino deleted file mode 100644 index df9d998..0000000 --- a/Temp_DHT.ino +++ /dev/null @@ -1,201 +0,0 @@ -/* 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/Weather_WH2.ino b/Weather_WH2.ino new file mode 100644 index 0000000..0977862 --- /dev/null +++ b/Weather_WH2.ino @@ -0,0 +1,415 @@ +/* + Updated code for receiving data from WH2 weather station + This code implements timeouts to make decoding more robust + Decodes received packets and writes a summary of each packet to the Arduino's + serial port + Created by Luc Small on 19 July 2013. + Released into the public domain. +*/ + +#include +#include +#include + +// DHT11 and BMP085 wired sensors +dht DHT; +BMP085 bmp; + +// Humidity sensor at pin 4 +#define DHT11PIN 5 + +#define DEBUG + +// LED pins +#define REDPIN 11 +#define GREENPIN 12 + +// Read data from 433MHz receiver on digital pin 3 +#define RF_IN 4 +// For better efficiency, the port is read directly +// the following two lines should be changed appropriately +// if the line above is changed. +#define RF_IN_RAW PIND4 +#define RF_IN_PIN PIND + +// Port that is hooked to LED to indicate a packet has been received + +#define COUNTER_RATE 3200-1 // 16,000,000Hz / 3200 = 5000 interrupts per second, ie. 200us between interrupts +// 1 is indicated by 500uS pulse +// wh2_accept from 2 = 400us to 3 = 600us +#define IS_HI_PULSE(interval) (interval >= 2 && interval <= 3) +// 0 is indicated by ~1500us pulse +// wh2_accept from 7 = 1400us to 8 = 1600us +#define IS_LOW_PULSE(interval) (interval >= 7 && interval <= 8) +// worst case packet length +// 6 bytes x 8 bits x (1.5 + 1) = 120ms; 120ms = 200us x 600 +#define HAS_TIMED_OUT(interval) (interval > 600) +// we expect 1ms of idle time between pulses +// so if our pulse hasn't arrived by 1.2ms, reset the wh2_packet_state machine +// 6 x 200us = 1.2ms +#define IDLE_HAS_TIMED_OUT(interval) (interval > 6) +// our expected pulse should arrive after 1ms +// we'll wh2_accept it if it arrives after +// 4 x 200us = 800us +#define IDLE_PERIOD_DONE(interval) (interval >= 4) +// Shorthand for tests +//#define RF_HI (digitalRead(RF_IN) == HIGH) +//#define RF_LOW (digitalRead(RF_IN) == LOW) +#define RF_HI (bit_is_set(RF_IN_PIN, RF_IN_RAW)) +#define RF_LOW (bit_is_clear(RF_IN_PIN, RF_IN_RAW)) + +// wh2_flags +#define GOT_PULSE 0x01 +#define LOGIC_HI 0x02 +volatile byte wh2_flags = 0; +volatile byte wh2_packet_state = 0; +volatile int wh2_timeout = 0; +byte wh2_packet[5]; +byte wh2_calculated_crc; + + +#ifdef DEBUG +byte printed = 0; +#endif + +ISR(TIMER1_COMPA_vect) +{ + static byte sampling_state = 0; + static byte count; + static boolean was_low = false; + + switch(sampling_state) { + case 0: // waiting + wh2_packet_state = 0; + if (RF_HI) { + if (was_low) { + count = 0; + sampling_state = 1; + was_low = false; + } + } else { + was_low = true; + } + break; + case 1: // acquiring first pulse + count++; + // end of first pulse + if (RF_LOW) { + if (IS_HI_PULSE(count)) { + wh2_flags = GOT_PULSE | LOGIC_HI; + sampling_state = 2; + count = 0; + } else if (IS_LOW_PULSE(count)) { + wh2_flags = GOT_PULSE; // logic low + sampling_state = 2; + count = 0; + } else { + sampling_state = 0; + } + } + break; + case 2: // observe 1ms of idle time + count++; + if (RF_HI) { + if (IDLE_HAS_TIMED_OUT(count)) { + sampling_state = 0; + } else if (IDLE_PERIOD_DONE(count)) { + sampling_state = 1; + count = 0; + } + } + break; + } + + if (wh2_timeout > 0) { + wh2_timeout++; + if (HAS_TIMED_OUT(wh2_timeout)) { + wh2_packet_state = 0; + wh2_timeout = 0; +#ifdef DEBUG + if (printed) { + Serial1.println(); + printed=0; + } +#endif + } + } +} + +void setup() { + + Serial1.begin(57600); + Serial1.println(); + Serial1.println("STATUS:STARTING"); + + bmp.begin(); + + pinMode(REDPIN,OUTPUT); + pinMode(GREENPIN,OUTPUT); + + pinMode(RF_IN, INPUT); + digitalWrite(RF_IN,HIGH); + + TCCR1A = 0x00; + TCCR1B = 0x09; + TCCR1C = 0x00; + OCR1A = COUNTER_RATE; + TIMSK1 = 0x02; + + // enable interrupts + sei(); +} + +unsigned long previousMillis = 0; +unsigned long indoor_interval = 60000; +unsigned long outdoor_interval = 45000; +unsigned long previousIndoor = 0; +unsigned long previousOutdoor = 0; + + +void loop() { + unsigned long now; + byte i; + + now = millis(); + + if (wh2_flags) { + if (wh2_accept()) { + // calculate the CRC + wh2_calculate_crc(); + + if (wh2_valid()) { + + Serial1.println(); + Serial1.print("SENSOR:TYPE=OUTDOOR,"); + + Serial1.print("ID="); + Serial1.print(wh2_sensor_id(), HEX); + + Serial1.print(",HUMIDITY="); + Serial1.print(wh2_humidity(), DEC); + + Serial1.print(",TEMPERATURE="); + Serial1.println(format_temp(wh2_temperature())); + + previousOutdoor = now; + digitalWrite(REDPIN,HIGH); + + } else { + + Serial1.println(); + Serial1.println("ERROR:OUTDOOR"); + previousOutdoor = now; + digitalWrite(REDPIN,LOW); + + } + + } + wh2_flags = 0x00; + } + + if ((unsigned long)(now - previousMillis) >= indoor_interval) { + + previousMillis = now; + + int chk = DHT.read11(DHT11PIN); + + if (chk==0) { + + Serial1.println(); + Serial1.print("SENSOR:TYPE=INDOOR,"); + Serial1.print("HUMIDITY="); + Serial1.print(DHT.humidity); + Serial1.print(",TEMPERATURE="); + Serial1.print(DHT.temperature); + + Serial1.println(); + Serial1.print("SENSOR:TYPE=BARO,"); + Serial1.print("PRESSURE="); + Serial1.print(bmp.readPressure()); + Serial1.print(",TEMPERATURE="); + Serial1.println(bmp.readTemperature()); + + previousIndoor = now; + digitalWrite(GREENPIN,HIGH); + + + } else { + + Serial1.println(); + Serial1.println("ERROR:INDOOR"); + previousIndoor = now; + digitalWrite(GREENPIN,LOW); + + } + + } + + if ((unsigned long)(now - previousIndoor) > indoor_interval*10) { + + Serial1.println(); + Serial1.println("ERROR:INDOOR TIMEOUT"); + previousIndoor = now; + digitalWrite(GREENPIN,LOW); + + } + + if ((unsigned long)(now - previousOutdoor) > outdoor_interval*10) { + + Serial1.println(); + Serial1.println("ERROR:OUTDOOR TIMEOUT"); + previousOutdoor = now; + digitalWrite(REDPIN,LOW); + + } + + +} + + +// processes new pulse +boolean wh2_accept() +{ + static byte packet_no, bit_no, history; + + // reset if in initial wh2_packet_state + if(wh2_packet_state == 0) { + // should history be 0, does it matter? + history = 0xFF; + wh2_packet_state = 1; + // enable wh2_timeout + wh2_timeout = 1; + } // fall thru to wh2_packet_state one + + // acquire preamble + if (wh2_packet_state == 1) { + // shift history right and store new value + history <<= 1; + // store a 1 if required (right shift along will store a 0) + if (wh2_flags & LOGIC_HI) { + history |= 0x01; + } + // check if we have a valid start of frame + // xxxxx110 + if ((history & B00000111) == B00000110) { + // need to clear packet, and counters + packet_no = 0; + // start at 1 becuase only need to acquire 7 bits for first packet byte. + bit_no = 1; + wh2_packet[0] = wh2_packet[1] = wh2_packet[2] = wh2_packet[3] = wh2_packet[4] = 0; + // we've acquired the preamble + wh2_packet_state = 2; + } + return false; + } + // acquire packet + if (wh2_packet_state == 2) { + + wh2_packet[packet_no] <<= 1; + if (wh2_flags & LOGIC_HI) { + wh2_packet[packet_no] |= 0x01; +#ifdef DEBUG + Serial1.print('1'); + printed=1; + } else { + Serial1.print('0'); + printed=1; +#endif + } + + bit_no ++; + if(bit_no > 7) { + bit_no = 0; + packet_no ++; + } + + if (packet_no > 4) { + // start the sampling process from scratch + wh2_packet_state = 0; + // clear wh2_timeout + wh2_timeout = 0; + return true; + } + } + return false; +} + + +void wh2_calculate_crc() +{ + wh2_calculated_crc = crc8(wh2_packet, 4); +} + +bool wh2_valid() +{ + return (wh2_calculated_crc == wh2_packet[4]); +} + +int wh2_sensor_id() +{ + return (wh2_packet[0] << 4) + (wh2_packet[1] >> 4); +} + +byte wh2_humidity() +{ + return wh2_packet[3]; +} + +/* Temperature in deci-degrees. e.g. 251 = 25.1 */ +int wh2_temperature() +{ + int temperature; + temperature = ((wh2_packet[1] & B00000111) << 8) + wh2_packet[2]; + // make negative + if (wh2_packet[1] & B00001000) { + temperature = -temperature; + } + return temperature; +} + +String format_temp(int temperature) +{ + byte whole, partial; + String s; + s = String(); + if (temperature<0) { + temperature = -temperature; + s += String('-'); + } + + whole = temperature / 10; + partial = temperature - (whole*10); + + s += String(whole, DEC); + s += '.'; + s += String(partial, DEC); + + return s; + +} + +uint8_t crc8( uint8_t *addr, uint8_t len) +{ + uint8_t crc = 0; + + // Indicated changes are from reference CRC-8 function in OneWire library + while (len--) { + uint8_t inbyte = *addr++; + for (uint8_t i = 8; i; i--) { + uint8_t mix = (crc ^ inbyte) & 0x80; // changed from & 0x01 + crc <<= 1; // changed from right shift + if (mix) crc ^= 0x31;// changed from 0x8C; + inbyte <<= 1; // changed from right shift + } + } + return crc; +} + + + + + + + + + diff --git a/mysql/meteo_calendar.sql b/mysql/meteo_calendar.sql new file mode 100644 index 0000000..2835877 --- /dev/null +++ b/mysql/meteo_calendar.sql @@ -0,0 +1,43 @@ +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) +-- +-- Host: localhost Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.38-0ubuntu0.14.04.1 + +/*!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 `calendar` +-- + +DROP TABLE IF EXISTS `calendar`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `calendar` ( + `day` date NOT NULL, + `sensor` int(11) NOT NULL, + PRIMARY KEY (`day`,`sensor`), + KEY `SENSOR` (`sensor`), + CONSTRAINT `fk_calendar_sensor` FOREIGN KEY (`sensor`) REFERENCES `sensors` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!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-09-25 14:49:31 diff --git a/mysql/meteo_error_log.sql b/mysql/meteo_error_log.sql new file mode 100644 index 0000000..7d3d91c --- /dev/null +++ b/mysql/meteo_error_log.sql @@ -0,0 +1,40 @@ +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) +-- +-- Host: localhost Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.38-0ubuntu0.14.04.1 + +/*!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 `error_log` +-- + +DROP TABLE IF EXISTS `error_log`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `error_log` ( + `timestamp` datetime DEFAULT NULL, + `text` varchar(2000) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!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-09-25 14:49:45 diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql index bdfa3cc..bf72780 100644 --- a/mysql/meteo_routines.sql +++ b/mysql/meteo_routines.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -58,21 +56,61 @@ DELIMITER ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; -CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float) +CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float,pTimestamp datetime) BEGIN declare lSTID int; declare lSID int; declare lSTPID int; + declare lTime DATETIME; + declare lDay DATE; + declare lCnt integer; select max(id) into lSTID from sensor_types where st_name=pSType; if lSTID is not null then select max(id) into lSID from sensors where st_id=lSTID and s_id=pSID; if lSID is not null then select max(id) into lSTPID from st_parameters where st_id=lSTID and st_name=pParam; if lSTPID is not null then - insert into sensor_values(sensor_id,parameter_id,timestamp,value) - values(lSID,lSTPID,current_timestamp(),pValue); + + if lSTPID>=0 then + + if pTimestamp is null then + set lTime:=current_timestamp(); + else + set lTime:=pTimestamp; + end if; + + insert into sensor_values(sensor_id,parameter_id,timestamp,value) + values(lSID,lSTPID,lTime,pValue); + + set lDay:=DATE(lTime); + select count(*) into lCnt from calendar where sensor=lSID and day=lDay; + if lCnt=0 then + insert into calendar(day,sensor) values(lDay,lSID); + end if; + + select count(*) into lCnt from sensors_ranges where sensor=lSID and day=lDay and parameter=lSTPID; + if lCnt=0 then + insert into sensors_ranges(day,sensor,parameter,min,max) values (lDay,lSID,lSTPID,pValue,pValue); + else + update sensors_ranges + set + min=LEAST(min,pValue), + max=GREATEST(max,pValue) + where + day=lDay and sensor=lSID and parameter=lSTPID; + end if; + end if; + else + insert into error_log(timestamp,text) + values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue)); end if; + else + insert into error_log(timestamp,text) + values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue)); end if; + else + insert into error_log(timestamp,text) + values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue)); end if; END ;; DELIMITER ; @@ -90,4 +128,4 @@ DELIMITER ; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:48 +-- Dump completed on 2014-09-25 14:45:45 diff --git a/mysql/meteo_sensor_types.sql b/mysql/meteo_sensor_types.sql index 726f1ec..c543750 100644 --- a/mysql/meteo_sensor_types.sql +++ b/mysql/meteo_sensor_types.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -52,4 +50,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:42 +-- Dump completed on 2014-09-25 14:46:53 diff --git a/mysql/meteo_sensor_values.sql b/mysql/meteo_sensor_values.sql index 77aa5d2..b954d11 100644 --- a/mysql/meteo_sensor_values.sql +++ b/mysql/meteo_sensor_values.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -36,17 +34,8 @@ CREATE TABLE `sensor_values` ( 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=223906 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=1163082 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; - --- --- Dumping data for table `sensor_values` --- - -LOCK TABLES `sensor_values` WRITE; -/*!40000 ALTER TABLE `sensor_values` DISABLE KEYS */; -/*!40000 ALTER TABLE `sensor_values` ENABLE KEYS */; -UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -57,4 +46,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:41 +-- Dump completed on 2014-09-25 14:47:29 diff --git a/mysql/meteo_sensors.sql b/mysql/meteo_sensors.sql index 6b17ae1..17441ae 100644 --- a/mysql/meteo_sensors.sql +++ b/mysql/meteo_sensors.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -35,16 +33,6 @@ CREATE TABLE `sensors` ( 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 */; - --- --- Dumping data for table `sensors` --- - -LOCK TABLES `sensors` WRITE; -/*!40000 ALTER TABLE `sensors` DISABLE KEYS */; -INSERT INTO `sensors` VALUES (1,1,'DEFAULT','Комната'),(2,2,'44F','Улица'),(3,3,'DEFAULT','Комната'); -/*!40000 ALTER TABLE `sensors` ENABLE KEYS */; -UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -55,4 +43,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:46 +-- Dump completed on 2014-09-25 14:47:17 diff --git a/mysql/meteo_sensors_ranges.sql b/mysql/meteo_sensors_ranges.sql new file mode 100644 index 0000000..6516ad3 --- /dev/null +++ b/mysql/meteo_sensors_ranges.sql @@ -0,0 +1,48 @@ +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) +-- +-- Host: localhost Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.38-0ubuntu0.14.04.1 + +/*!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 `sensors_ranges` +-- + +DROP TABLE IF EXISTS `sensors_ranges`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sensors_ranges` ( + `sensor` int(11) NOT NULL, + `day` date NOT NULL, + `min` float NOT NULL, + `max` float NOT NULL, + `parameter` int(11) NOT NULL, + PRIMARY KEY (`day`,`sensor`,`parameter`), + KEY `fk_sensors_ranges_sensors_idx` (`sensor`), + KEY `fk_sensors_ranges_parameters_idx` (`parameter`), + CONSTRAINT `fk_sensors_ranges_parameters` FOREIGN KEY (`parameter`) REFERENCES `st_parameters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT `fk_sensors_ranges_sensors` FOREIGN KEY (`sensor`) REFERENCES `sensors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!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-09-25 14:47:44 diff --git a/mysql/meteo_st_parameters.sql b/mysql/meteo_st_parameters.sql index 2eb093a..5bcede1 100644 --- a/mysql/meteo_st_parameters.sql +++ b/mysql/meteo_st_parameters.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -49,7 +47,7 @@ CREATE TABLE `st_parameters` ( LOCK TABLES `st_parameters` WRITE; /*!40000 ALTER TABLE `st_parameters` DISABLE KEYS */; -INSERT INTO `st_parameters` VALUES (1,3,'TEMPERATURE',1,'Температура','red','red','navy','darkred'),(2,1,'HUMIDITY',2,'Влажность','skyblue','skyblue','blue','royalblue'),(3,2,'TEMPERATURE',1,'Температура','red','red','navy','darkred'),(4,2,'HUMIDITY',2,'Влажность','skyblue','skyblue','blue','royalblue'),(5,3,'PRESSURE',3,'Атмосферное давление','green','green','gold','gold'); +INSERT INTO `st_parameters` VALUES (-1,1,'TEMPERATURE',1,'Температура','red','red','navy','red'),(1,3,'TEMPERATURE',1,'Температура','red','red','navy','darkred'),(2,1,'HUMIDITY',2,'Влажность','skyblue','skyblue','blue','royalblue'),(3,2,'TEMPERATURE',1,'Температура','red','red','navy','darkred'),(4,2,'HUMIDITY',2,'Влажность','skyblue','skyblue','blue','royalblue'),(5,3,'PRESSURE',3,'Атмосферное давление','green','green','gold','gold'); /*!40000 ALTER TABLE `st_parameters` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -62,4 +60,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:44 +-- Dump completed on 2014-09-25 14:48:08 diff --git a/mysql/meteo_unit_conv.sql b/mysql/meteo_unit_conv.sql index f58fa83..4ac573a 100644 --- a/mysql/meteo_unit_conv.sql +++ b/mysql/meteo_unit_conv.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -54,4 +52,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:31 +-- Dump completed on 2014-09-25 14:49:01 diff --git a/mysql/meteo_unit_groups.sql b/mysql/meteo_unit_groups.sql index 660af13..2a0d7f6 100644 --- a/mysql/meteo_unit_groups.sql +++ b/mysql/meteo_unit_groups.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -50,4 +48,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:30 +-- Dump completed on 2014-09-25 14:48:40 diff --git a/mysql/meteo_units.sql b/mysql/meteo_units.sql index 227d044..110b39d 100644 --- a/mysql/meteo_units.sql +++ b/mysql/meteo_units.sql @@ -1,10 +1,8 @@ -CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; -USE `meteo`; --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64) -- --- Host: estia Database: meteo +-- Host: localhost Database: meteo -- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 +-- Server version 5.5.38-0ubuntu0.14.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -54,4 +52,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-03-17 20:31:43 +-- Dump completed on 2014-09-25 14:48:27 diff --git a/weathermon b/weathermon index 8c94dd0..f7b2bd5 100755 --- a/weathermon +++ b/weathermon @@ -2,9 +2,11 @@ import serial -from os import listdir +from os import listdir,system from os.path import isfile, join +from pprint import pprint + from termios import tcflush, TCIOFLUSH from time import sleep,time @@ -22,7 +24,8 @@ from urllib import urlencode from StringIO import StringIO searchpath = '/dev/serial/by-id/' -baud = 9600 +baud = 57600 +path = None timeout = 5 external_submit_interval = 320 @@ -36,6 +39,7 @@ import ConfigParser def find_port(): global serial_num + global path files = listdir(searchpath) for f in files: @@ -68,17 +72,25 @@ def read_loop(ser,callback): finally: None +def print_log(str): + global logging + print str + if logging == "on": + system("logger -t weathermon \""+str+"\"") + def submit_narodmon(queue): param = { 'ID':"{:X}".format(getnode())} + pprint(queue) + for sensor in queue: value = submit_queue[sensor]['val'] timestamp = submit_queue[sensor]['timestamp'] digest = md5(sensor).hexdigest()[:18] param[digest] = value; - print param + pprint (param) url = "http://narodmon.ru/post.php" @@ -97,7 +109,7 @@ def submit_narodmon(queue): response_value = response_buffer.getvalue() - print 'Content: ', response_value + print_log('Content: '+response_value) return True @@ -137,7 +149,7 @@ def submit_owm(queue): response_value = response_buffer.getvalue() - print 'Content: ', response_value + print_log('Content: '+response_value) return True @@ -154,7 +166,7 @@ def purge_queue(): clean = [] for key in submit_queue: if submit_queue[key]['timestamp'] < time()-expire_interval: - print "Expired value for "+key + print_log("Expired value for "+key) clean.append(key) for i in clean: del submit_queue[i] @@ -163,25 +175,29 @@ 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)) + c.execute('CALL meteo.submit_value(%s,%s,%s,%s,NULL)', (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...' + print_log('Purging queue...') submit_time=time() purge_queue() def process_str(str): + print_log("Received: "+str) try: msg_type, msg_body = str.split(':') + except: + return + try: if msg_type == 'STATUS': - print 'Status: ', msg_body + print_log('Status: '+msg_body) elif msg_type == 'ERROR': - print 'Error: ', msg_body + print_log('Error: '+ msg_body) elif msg_type == 'SENSOR': sens = msg_body.split(',') sensor = {} @@ -199,40 +215,52 @@ def process_str(str): 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]) + if sensor[key] is not None: + print_log('Type = '+sensor_type+', ID = '+sensor_id+', Param = '+key+', Value = '+sensor[key]) + submit_data(sensor_type,sensor_id,key,sensor[key]) + else: + print_log('Error: got empty parameter value for '+sensor_type+'.'+sensor_id+'.'+key) except: - print 'Exception processing...' + print_log('Exception processing...') + 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=5, file=sys.stdout) try: database.close() except: None reconnect() -def print_str(str): - print str - def weather_mon(): - path = find_port() + global path + + if path is None: + 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: + connected = False + + while not connected: + + 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_log("Database connected...") + connected = True + + except: - print "Error connecting database" - sleep(30) + print_log("Error connecting database") + sleep(30) def main(): weather_mon() @@ -244,7 +272,18 @@ try: dbhost = cfg.get("mysql","host") dbuser = cfg.get("mysql","user") dbpasswd = cfg.get("mysql","passwd") - serialnum = cfg.get("serial","id") + try: + path = cfg.get("serial","port"); + except: + path = None + try: + serialnum = cfg.get("serial","id") + except: + serialnum = None + try: + logging = cfg.get("logging","enabled") + except: + logging = None owmuser = cfg.get("openweathermap","user") owmpasswd = cfg.get("openweathermap",'passwd') if owmuser: @@ -258,7 +297,7 @@ try: except: - print "Cannot intialize system" + print_log("Cannot intialize system") exit() if __name__ == "__main__": diff --git a/web/archive.php b/web/archive.php index 76708f7..79ef6c4 100644 --- a/web/archive.php +++ b/web/archive.php @@ -2,9 +2,29 @@ - + WeatherMon (архив) + prepare( 'select - date_format(timestamp,\'%Y\') as year + date_format(day,\'%Y\') as year from - sensor_values + calendar group by year order by year'); $q -> execute(); @@ -37,7 +57,7 @@ if (! $year) { fetch(PDO::FETCH_ASSOC)) { ?> - Данные за год
+ Данные за год
prepare( 'select - date_format(timestamp,\'%m\') as month, - date_format(timestamp,\'%d\') as day + date_format(day,\'%m\') as month, + date_format(day,\'%d\') as day from - sensor_values + calendar where - timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and - timestamp=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and + day'.$month_name.''; + echo '

'.$month_name.'

'; } else { echo '

'.$month_name.'

'; } @@ -96,18 +116,18 @@ if (! $year) { $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, + count(distinct day) cnt, + round(min(v.min),1) min_value, + round(max(v.max),1) max_value, u.name_short unit from - sensor_values v,sensors s,st_parameters p,units u + sensors_ranges v,sensors s,st_parameters p,units u where - v.sensor_id=s.id and - v.parameter_id=p.id and + v.sensor=s.id and + v.parameter=p.id and p.st_unit=u.id and - v.timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and - v.timestamp=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and + v.day prepare( 'select - date_format(timestamp,\'%d\') as day + date_format(day,\'%d\') as day from - sensor_values + sensors_ranges where - timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and - timestamp=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and + day 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, + count(distinct day) cnt, + round(min(v.min),1) min_value, + round(max(v.max),1) max_value, u.name_short unit from - sensor_values v,sensors s,st_parameters p,units u + sensors_ranges v,sensors s,st_parameters p,units u where - v.sensor_id=s.id and - v.parameter_id=p.id and + v.sensor=s.id and + v.parameter=p.id and p.st_unit=u.id and - v.timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and - v.timestamp=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and + v.day prepare( 'select - distinct v.sensor_id,s.s_description,p.id as param_id,p.st_description + distinct v.sensor as sensor_id,s.s_description,p.id as param_id,p.st_description from - sensor_values v,st_parameters p,sensors s + sensors_ranges v,st_parameters p,sensors s where - v.timestamp>=str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\') and - v.timestamp execute(); diff --git a/web/config_local.php b/web/config_local.php index c087aea..9c9adb2 100644 --- a/web/config_local.php +++ b/web/config_local.php @@ -1,12 +1,11 @@ - \ No newline at end of file + +?> \ No newline at end of file diff --git a/web/image.php b/web/image.php index 2a2d2f3..eaa6fda 100644 --- a/web/image.php +++ b/web/image.php @@ -16,6 +16,15 @@ if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_sche } +$supported = imagetypes(); +if( $supported & IMG_PNG ) $img_format="png"; +elseif( $supported & IMG_GIF ) $img_format="gif"; +elseif( $supported & IMG_JPG ) $img_format="jpeg"; +elseif( $supported & IMG_WBMP ) $img_format="wbmp"; +elseif( $supported & IMG_XPM ) $img_format="xpm"; + +$cachefilename = NULL; + $db -> exec('SET CHARACTER SET utf8'); $type = $_REQUEST['type']; @@ -77,9 +86,17 @@ if ($type and $param) { } elseif ($type == 'range') { + $curr = intval(date('YmdHis')); + $from = intval($_REQUEST['fromdate']); $to = intval($_REQUEST['todate']); + if ($curr>$to) { + + $cachefilename='meteo.'.$sensor.'.'.$param.'.'.$from.'-'.$to.'.'.$img_format; + + } + $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' ); @@ -88,6 +105,22 @@ if ($type and $param) { } + $g = new Graph(640,480); + + if ($cachefilename) { + if ($g->cache->IsValid($cachefilename)) { + + $g->cache->StreamImgFile($g->img,$cachefilename); + return; + + } else { + + $timeout = 8640000; + $g->SetupCache($cachefilename,$timeout); + + } + } + $q -> execute(); while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { @@ -132,7 +165,6 @@ if ($type and $param) { } // Create the graph - $g = new Graph(640,480); $g->graph_theme = null; //$g->img->SetAntiAliasing(); diff --git a/web/image_minmax.php b/web/image_minmax.php index a0a6326..3736221 100644 --- a/web/image_minmax.php +++ b/web/image_minmax.php @@ -16,6 +16,15 @@ if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_sche } +$supported = imagetypes(); +if( $supported & IMG_PNG ) $img_format="png"; +elseif( $supported & IMG_GIF ) $img_format="gif"; +elseif( $supported & IMG_JPG ) $img_format="jpeg"; +elseif( $supported & IMG_WBMP ) $img_format="wbmp"; +elseif( $supported & IMG_XPM ) $img_format="xpm"; + +$cachefilename = NULL; + $db -> exec('SET CHARACTER SET utf8'); $type = $_REQUEST['type']; @@ -78,7 +87,15 @@ if ($type and $param) { if ($month==13) { $next_year++; - $month=1; + $next_month=1; + + } + + $curr = date("Ym"); + + if ($curr>$next_year.$next_month) { + + $cachefilename='meteo.month.'.$sensor.'.'.$param.'.'.$year.'-'.$month.'.'.$img_format; } @@ -86,19 +103,20 @@ if ($type and $param) { ' select x, - unitconv(min(value),'.$from_unit.','.$to_unit.') min_value, - unitconv(max(value),'.$from_unit.','.$to_unit.') max_value + unitconv(min(min),'.$from_unit.','.$to_unit.') min_value, + unitconv(max(max),'.$from_unit.','.$to_unit.') max_value from ( select - unix_timestamp(cast(timestamp as date)) x, - value + unix_timestamp(day) x, + min, + max from - sensor_values + sensors_ranges where - timestamp>=str_to_date(\''.$year.$month.'\',\'%Y%m\') - and timestamp=str_to_date(\''.$year.$month.'\',\'%Y%m\') + and day$next_year) { + + $cachefilename='meteo.year.'.$sensor.'.'.$param.'.'.$year.'.'.$img_format; + + } + $q = $db -> prepare( ' select x, - unitconv(min(value),'.$from_unit.','.$to_unit.') min_value, - unitconv(max(value),'.$from_unit.','.$to_unit.') max_value + unitconv(min(min),'.$from_unit.','.$to_unit.') min_value, + unitconv(max(max),'.$from_unit.','.$to_unit.') max_value from ( select unix_timestamp( - DATE_SUB(cast(timestamp as date), INTERVAL DAYOFWEEK(cast(timestamp as date))-1 DAY) + DATE_SUB(day, INTERVAL DAYOFWEEK(day)-1 DAY) ) x, - value + min, + max from - sensor_values + sensors_ranges where - timestamp>=str_to_date(\''.$year.'\',\'%Y\') - and timestamp=str_to_date(\''.$year.'\',\'%Y\') + and daycache->IsValid($cachefilename)) { + + $g->cache->StreamImgFile($g->img,$cachefilename); + return; + + } else { + + $timeout = 8640000; + $g->SetupCache($cachefilename,$timeout); + + } + } + + $q -> execute(); while ($row = $q -> fetch(PDO::FETCH_ASSOC)) { @@ -144,7 +188,6 @@ if ($type and $param) { // Create the graph - $g = new Graph(640,480); $g->graph_theme = null; //$g->img->SetAntiAliasing(); diff --git a/web/index.php b/web/index.php index d513251..dfc7450 100644 --- a/web/index.php +++ b/web/index.php @@ -2,9 +2,14 @@ - + WeatherMon (Последние 24 часа) + prepare( where v.timestamp>adddate(now(), -1) and v.sensor_id=s.id - and s.st_id=p.st_id' + and s.st_id=p.st_id + and p.id>=0 + order by s_description,st_description' ); $q -> execute(); diff --git a/web/setup.php b/web/setup.php index 8fb989d..e591264 100644 --- a/web/setup.php +++ b/web/setup.php @@ -34,7 +34,7 @@ if ($_REQUEST['action']=='submit') { - + WeatherMon (Настройки) @@ -63,7 +63,7 @@ if ($_REQUEST['action']=='submit') { - + WeatherMon (Настройки) -- 2.34.1