<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
include('config_local.php');
include('calendar.php');
+function get_unit($db,$param) {
+
+ $qug = $db -> prepare(
+ 'select u.id,u.unit_group from st_parameters st,units u where st.id='.$param.' and st.st_unit=u.id'
+ );
+ $qug -> execute();
+
+ while ($row = $qug -> fetch(PDO::FETCH_ASSOC)) {
+ $from_unit = $row['id'];
+ $unit_group = $row['unit_group'];
+ }
+
+ if (!empty($_COOKIE['unit_'.$unit_group])) {
+ $to_unit=intval($_COOKIE['unit_'.$unit_group]);
+ } else {
+ $to_unit=$from_unit;
+ }
+
+ $qu = $db -> prepare(
+ 'select u.name_short from units u where u.id='.$to_unit
+ );
+ $qu -> execute();
+
+ while ($row = $qu -> fetch(PDO::FETCH_ASSOC)) {
+ $param_unit = $row['name_short'];
+ }
+
+ return Array(
+ 'from' => $from_unit,
+ 'to' => $to_unit,
+ 'name' => $param_unit
+ );
+
+}
+
+function convert_unit($db,$value,$from,$to) {
+
+ if ($from==$to) {
+
+ $val = $value;
+
+ } else {
+
+ $qv = $db -> prepare(
+ 'select round(unitconv('.$value.','.$from.','.$to.'),2) res'
+ );
+ $qv -> execute();
+
+ while ($row = $qv -> fetch(PDO::FETCH_ASSOC)) {
+ $val = $row['res'];
+ }
+
+ }
+
+ return $val;
+
+}
+
$year = $_REQUEST['year'];
$month = $_REQUEST['month'];
$day = $_REQUEST['day'];
$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'
);
$sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
foreach ($sensors as $sensor) {
-
+
+ $units = get_unit($db,$sensor['param']);
+ $from_unit = $units['from'];
+ $to_unit = $units['to'];
+ $param_unit = $units['name'];
+
+ $min_val = convert_unit($db,$sensor['min_value'],$from_unit,$to_unit);
+ $max_val = convert_unit($db,$sensor['max_value'],$from_unit,$to_unit);
+
echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
echo '<table align="center">';
echo '<tr><td>';
- echo 'Минимальное значение за год <b>'.$sensor['min_value'].' '.$sensor['unit'].'</b><br>';
- echo 'Максимальное значение за год <b>'.$sensor['max_value'].' '.$sensor['unit'].'</b><br>';
+ echo 'Минимальное значение за год <b>'.$min_val.' '.$param_unit.'</b><br>';
+ echo 'Максимальное значение за год <b>'.$max_val.' '.$param_unit.'</b><br>';
echo '</td></tr>';
echo '</table>';
$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'
);
$sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
foreach ($sensors as $sensor) {
+
+ $units = get_unit($db,$sensor['param']);
+ $from_unit = $units['from'];
+ $to_unit = $units['to'];
+ $param_unit = $units['name'];
+
+ $min_val = convert_unit($db,$sensor['min_value'],$from_unit,$to_unit);
+ $max_val = convert_unit($db,$sensor['max_value'],$from_unit,$to_unit);
echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
echo '<table align="center">';
echo '<tr><td>';
- echo 'Минимальное значение за месяц <b>'.$sensor['min_value'].' '.$sensor['unit'].'</b><br>';
- echo 'Максимальное значение за месяц <b>'.$sensor['max_value'].' '.$sensor['unit'].'</b><br>';
+ echo 'Минимальное значение за месяц <b>'.$min_val.' '.$param_unit.'</b><br>';
+ echo 'Максимальное значение за месяц <b>'.$max_val.' '.$param_unit.'</b><br>';
echo '</td></tr>';
echo '</table>';
$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();
projects / weathermon.git / blobdiff