<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();
projects / weathermon.git / blobdiff