2) Добавлены кеш-таблицы с минимальным-максимальным значением за сутки для быстрой отрисовки графиков
3) Добавлено протоколирование ошибок на стороне сервера
4) Добавлена опция лдя записи сообщений демона в системный протокол
5) Добавлено использование кеша изображений для избежания повторной отрисовки
+++ /dev/null
-/* 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 <dht11.h>
-#include <Wire.h>
-#include <BMP085.h>
-#include <WeatherSensorWH2.h>
-
-/*-----( 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 ) */
--- /dev/null
+/*
+ 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 <dht.h>
+#include <Wire.h>
+#include <BMP085.h>
+
+// 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;
+}
+
+
+
+
+
+
+
+
+
--- /dev/null
+-- 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
--- /dev/null
+-- 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
-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 */;
/*!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 ;
/*!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
-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 */;
/*!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
-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 */;
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 */;
/*!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
-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 */;
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 */;
/*!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
--- /dev/null
+-- 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
-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 */;
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 */;
/*!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
-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 */;
/*!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
-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 */;
/*!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
-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 */;
/*!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
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
from StringIO import StringIO
searchpath = '/dev/serial/by-id/'
-baud = 9600
+baud = 57600
+path = None
timeout = 5
external_submit_interval = 320
def find_port():
global serial_num
+ global path
files = listdir(searchpath)
for f in files:
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"
response_value = response_buffer.getvalue()
- print 'Content: ', response_value
+ print_log('Content: '+response_value)
return True
response_value = response_buffer.getvalue()
- print 'Content: ', response_value
+ print_log('Content: '+response_value)
return True
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]
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 = {}
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()
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:
except:
- print "Cannot intialize system"
+ print_log("Cannot intialize system")
exit()
if __name__ == "__main__":
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
- <meta name="GENERATOR" content="Mozilla/4.72 [cp1251] (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
+ <meta name="GENERATOR" content="Mozilla/4.72 (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
<title>WeatherMon (архив)</title>
<link rel="icon" href="favicon.png" />
+ <style type="text/css">
+ a.year {
+ padding:20px;
+ margin: 10px;
+ border:2px solid darkgrey;
+ border-radius: 10px;
+ display:inline-block;
+ width:200px;
+ float:left;
+ font-size:100%;
+ text-align:center;
+ }
+ a.month {
+ font-size:100%;
+ text-align:center;
+ }
+ a:visited { text-decoration: none; color:darkblue; }
+ a:link { text-decoration: none; color:blue; }
+ a:hover { text-decoration: none; color:navy; }
+ </style>
</head>
<body text="black" bgcolor="silver" link="blue" vlink="#000080" alink="#FF0000">
<?php
$q = $db -> 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();
<?php
while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
?>
- <a href="?year=<?php echo $row['year']; ?>">Данные за <?php echo $row['year']; ?> год</a><br>
+ <a class="year" href="?year=<?php echo $row['year']; ?>">Данные за <?php echo $row['year']; ?> год</a><br>
<?php
}
$next_year = sprintf('%04d',$next_year);
$q = $db -> 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(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
+ day>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and
+ day<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
group by month,day
order by month,day'
);
$month=sprintf('%02d',$m);
$month_name = strftime('%B',mktime(0,0,0,$month,1,$year));
if (!empty($months[$month])) {
- echo '<h3 align="center"><a href="?year='.$year.'&month='.$month.'">'.$month_name.'</a></h3>';
+ echo '<h3 align="center"><a class="month" href="?year='.$year.'&month='.$month.'">'.$month_name.'</a></h3>';
} else {
echo '<h3 align="center">'.$month_name.'</h3>';
}
$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(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
+ v.day>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and
+ v.day<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
group by s.id,p.id,s.s_description,p.st_description
order by s.id,p.id'
);
$q = $db -> 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(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
+ day>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and
+ day<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
group by day
order by day'
);
$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.'-'.$month.'-01\',\'%Y-%m-%d\') and
- v.timestamp<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
+ v.day>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and
+ v.day<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
group by s.id,p.id,s.s_description,p.st_description
order by s.id,p.id'
);
$q = $db -> 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<date_add(str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\'),interval 1 day)
- and v.sensor_id=s.id
- and s.st_id=p.st_id'
+ v.day=str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\')
+ and v.sensor=s.id
+ and v.parameter=p.id'
);
$q -> execute();
<?php
- $mysql_host = 'hostname';
+ $mysql_host = 'host';
$mysql_schema = 'meteo';
$mysql_user = 'meteo';
- $mysql_pwd = '***';
+ $mysql_pwd = 'somestrictpasswd';
$mysql_port = 3306;
-
+
setlocale(LC_ALL,'ru_RU.UTF8');
-
-?>
-
\ No newline at end of file
+
+?>
\ No newline at end of file
}
+$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'];
} 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'
);
}
+ $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)) {
}
// Create the graph
- $g = new Graph(640,480);
$g->graph_theme = null;
//$g->img->SetAntiAliasing();
}
+$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'];
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;
}
'
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(\''.$next_year.$next_month.'\',\'%Y%m\')
- and sensor_id='.$sensor.'
- and parameter_id='.$param.'
+ day>=str_to_date(\''.$year.$month.'\',\'%Y%m\')
+ and day<str_to_date(\''.$next_year.$next_month.'\',\'%Y%m\')
+ and sensor='.$sensor.'
+ and parameter='.$param.'
) t group by x
order by x'
);
$next_year = $year+1;
+ $curr = date("Y");
+
+ if ($curr>$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(\''.$next_year.'\',\'%Y\')
- and sensor_id='.$sensor.'
- and parameter_id='.$param.'
+ day>=str_to_date(\''.$year.'\',\'%Y\')
+ and day<str_to_date(\''.$next_year.'\',\'%Y\')
+ and sensor='.$sensor.'
+ and parameter='.$param.'
) t group by x
order by x'
);
}
+ $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)) {
// Create the graph
- $g = new Graph(640,480);
$g->graph_theme = null;
//$g->img->SetAntiAliasing();
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
- <meta name="GENERATOR" content="Mozilla/4.72 [cp1251] (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
+ <meta name="GENERATOR" content="Mozilla/4.72 (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
<title>WeatherMon (Последние 24 часа)</title>
<link rel="icon" href="favicon.png" />
+ <style type="text/css">
+ a:visited { text-decoration: none; color:darkblue; }
+ a:link { text-decoration: none; color:blue; }
+ a:hover { text-decoration: none; color:navy; }
+ </style>
</head>
<body text="black" bgcolor="silver" link="blue" vlink="#000080" alink="#FF0000">
<?php
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();
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta http-equiv="refresh" content="2; url=index.php">
- <meta name="GENERATOR" content="Mozilla/4.72 [cp1251] (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
+ <meta name="GENERATOR" content="Mozilla/4.72 (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
<title>WeatherMon (Настройки)</title>
<link rel="icon" href="favicon.png" />
</head>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
- <meta name="GENERATOR" content="Mozilla/4.72 [cp1251] (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
+ <meta name="GENERATOR" content="Mozilla/4.72 (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
<title>WeatherMon (Настройки)</title>
<link rel="icon" href="favicon.svg" />
</head>
projects / weathermon.git / commitdiff