Home database scripts

database scripts


more info on
store.php:

';
//emonGraph($user,$value);

function update($user,$value){

// connect to database on server
$con = mysql_connect("localhost","xxxDatabase_Namexxx","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("xxxDatabase_Namexxx", $con);

// insert the data in to the fields
$sql="INSERT INTO data (time, user, value)
VALUES
(now(),'$user','$value')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

echo "OK ";

mysql_close($con);
}

function reply($user){

// connect to database on server
$con = mysql_connect("localhost","xxxDatabase_Namexxx","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("xxxDatabase_Namexxx", $con);

$instructie = "";

// Retrieve latest instruction
$result = mysql_query("SELECT * FROM instructions WHERE user ='$user' ORDER BY Time DESC limit 1");
while($row = mysql_fetch_array($result))
{
$instructie = $row['value'];
echo $instructie;
}

mysql_close($con);
}

function emonGraph($user,$value){
echo 'start emonGraph';
echo $user;
$base = 'http://emoncms3.../.../&json={';
$val = $base . $user . ':' . $value . '}';
echo $val;
// create a new cURL resource
$ch = curl_init();

// set URL and other appropriate options
curl_setopt($ch, CURLOPT_URL, "$val");
curl_setopt($ch, CURLOPT_HEADER, 0);

// grab URL and pass it to the browser
curl_exec($ch);

// close cURL resource, and free up system resources
curl_close($ch);
}

?>

calc.php:

' . '
' . 'executed on '. date('l jS \of F Y H:i:s A');

//end

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Calculate
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/

function calculate(){

// Could be enhanced in the future by searching for every unique user in the database and every allowed interval

global $avg1_user1, $avg15_user1, $avg30_user1, $avg60_user1, $avg1_user2, $avg15_user2, $avg30_user2, $avg60_user2;

echo 'CALCULATING AVERAGES: ' . '
';
$avg1_user1 = calculateAvg(user1,1);
$avg15_user1 = calculateAvg(user1,15);
$avg30_user1 = calculateAvg(user1,30);
$avg60_user1 = calculateAvg(user1,60);

$avg1_user2 = calculateAvg(user2,1);
$avg15_user2 = calculateAvg(user2,15);
$avg30_user2 = calculateAvg(user2,30);
$avg60_user2 = calculateAvg(user2,60);

calculateHourAndDay(user1,$avg60_user1);
calculateHourAndDay(user2,$avg60_user2);

}

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Calculate The average value over a certain
interval and store it in a specific table.
Allowed intervals are 1, 15, 30 and 60
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/

function calculateAvg($user,$interval)
{

// connect to database on server
$con = mysql_connect("localhost","database_name","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("database_name", $con);

// reset values and sum up all values of this interval and count
$sumValue = 0;
$counter = 0;

$result = mysql_query("SELECT * FROM data WHERE time + INTERVAL $interval MINUTE > now() AND user ='$user'");
while($row = mysql_fetch_array($result))
{
$sumValue += $row['value'];
$counter += 1;
}

// Do nothing if there are no new values for a user
if ($sumValue==0) {
echo "Can't calculate AVG " . $interval . "for " . $user . '
';
// Close database connection
mysql_close($con);
}

else {
// calculate average and round it
// it is rounded because otherwise SQL does this
// and this way we're sure that the same values
// are used to calculate the instructions
$avg = round($sumValue / $counter);

// Close database connection
mysql_close($con);

// insert the data in the database
$table = 'avg' . $interval;
storeValue($table,$user,$avg);

// Print what just happened
echo "AVG"; echo $interval; echo " successfully added for "; echo $user; echo": "; echo $sumValue; echo" / "; echo $counter; echo ' = ' . $avg . '
';
return $avg;
}
}

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CalculateHour
store how much kWh was used in the last hour
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/
function calculateHourAndDay($user,$avg60){

// only execute this when hh:00:ss
if (date(i)==0 ){
storeValue(hour,$user,$avg60);
}

// only execute this when 00:01:ss
// doing this one minute later than the hour value
// makes sure all values of the previous day are counted
if (date(H)==0 AND date(i)==1){

// connect to database on server
$con = mysql_connect("localhost","database_name","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("database_name", $con);

// reset values and and count the values of the last day
$total = 0;
$counter = 0;
echo 'test';
$result = mysql_query("SELECT * FROM hour WHERE time + INTERVAL 24 HOUR > now() AND user ='$user'");
while($row = mysql_fetch_array($result))
{
$total += $row['value'];
$counter += 1;
}

// Close database connection
mysql_close($con);

// insert the data in the database
storeValue(day,$user,$total);
}
}

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Value of last 24 h
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/
function last24h($user){

// connect to database on server
$con = mysql_connect("localhost","database_name","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("database_name", $con);

// reset values and and count the values of the last day
$total = 0;
$counter = 0;
$result = mysql_query("SELECT * FROM hour WHERE time + INTERVAL 24 HOUR > now() AND user ='$user'");
while($row = mysql_fetch_array($result))
{
$total += $row['value'];
$counter += 1;
}

// Close database connection
mysql_close($con);

return $total;

}

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Update instructions
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/
function updateInstructions(){

echo '
' . 'INSTRUCTIONS: ' . '
';

// retrieving the variables from other funtions
// this is faster than downloading the values from the server

global $avg1_user1, $avg15_user1, $avg30_user1, $avg60_user1, $avg1_user2, $avg15_user2, $avg30_user2, $avg60_user2;

// now do something with the values

// OBJECTIVE display (configured for user_1)

// momenteel verbruik (watt)
$instruction1 .= ';A' . $avg1_user1 . ';B' . $avg1_user2;

// momenteel verbruik per uur (kwh) (0.000)
$instruction1 .= ';C' . $avg60_user1 . ';D' . $avg60_user2;

// momenteel verbruik per dag (kwh) (0.000)
$per_day_user1 = last24h(user1);
$per_day_user2 = last24h(user2);
$instruction1 .= ';E' . $per_day_user1 . ';F' . $per_day_user2;

// verbruik gisteren (euro)
$yesterday_user1 = retrieveValue(user1,day);
$yesterday_user1 = $yesterday_user1 *0.015;
$yesterday_user2 = retrieveValue(user2,day);
$yesterday_user2 = $yesterday_user2 *0.015;
$instruction1 .= ';I' . $yesterday_user1 . ';J' . $yesterday_user2;

// overwinningen
$victories_user1 = retrieveValue(user1,victory);
$victories_user2 = retrieveValue(user2,victory);
$instruction1 .= ';K' . $victories_user1 . ';L' . $victories_user2;

// SUBJECTIVE display (configured for user_2)

// momenteel verbruik jij ((veel) meer, minder, evenveel) energie als jouw tegenstander
$difference = $avg1_user2 - $avg1_user1;
$value = 0;
if ( $difference > 250) {$value = '5'; } // veel meer
if ( 250 > $difference && $difference > 30) {$value = '4'; } // meer
if ( 30 > $difference && $difference> -30) {$value = '3'; } // even veel
if ( -30 > $difference && $difference> -250) {$value = '2'; } // minder
if ( -250 > $difference ) {$value = '1'; } // minder
$instruction2 .= ';A' . $value;

// jij was gisteren de winnaar
if ($yesterday_user2 > $yesterday_user1){ $winner=1;}
else { $winner=0;}
$instruction2 .= ';B' . $winner;

// jij verbruikte ((veel) meer, minder, evenveel) als jouw tegenspeler
// gisteren
$difference = $yesterday_user2 - $yesterday_user1;
$value = 0;
if ( $difference > 500) {$value = '5'; } // veel meer
if ( 500 > $difference && $difference > 100) {$value = '4'; } // meer
if ( 100 > $difference && $difference > -100) {$value = '3'; } // even veel
if ( -100 > $difference && $difference > -500) {$value = '2'; } // minder
if ( -500 > $difference ) {$value = '1'; } // minder
$instruction2 .= ';C' . $value;

// Het laatste uur
$difference = $avg60_user2 - $avg60_user1;
$value = 0;
if ( $difference > 250) {$value = '5'; } // veel meer
if ( 250 > $difference && $difference > 50) {$value = '4'; } // meer
if ( 50 > $difference && $difference > -50) {$value = '3'; } // even veel
if ( -50 > $difference && $difference > -250) {$value = '2'; } // minder
if ( -250 > $difference ) {$value = '1'; } // minder
$instruction2 .= ';D' . $value;

// Het laatste kwartier
$difference = $avg15_user2 - $avg15_user1;
$value = 0;
if ( $difference > 250) {$value = '5'; } // veel meer
if ( 250 > $difference && $difference > 50) {$value = '4'; } // meer
if ( 50 > $difference && $difference > -50) {$value = '3'; } // even veel
if ( -50 > $difference && $difference > -250) {$value = '2'; } // minder
if ( -250 > $difference ) {$value = '1'; } // minder
$instruction2 .= ';E' . $value;

// jouw verbruik is
// vandaag (gestegen - gedaald - constant)
$value = 0;
$difference = $per_day_user2 - $yesterday_user2;
if ( $difference > 150){ $value = '7';} //gestegen
else {
if ( $difference < -150){ $value = '8';} //gedaald else{ $value = '6';} //constant } $instruction2 .= ';F' . $value; // Het laatste uur ((heel)laag - normaal - (heel)hoog) $value = 0; if ( $avg60_user2 > 1500){ $value = '5';} // heel hoog
if ( 1500 > $avg60_user2 && $avg60_user2 > 350){ $value = '4';} //hoog
if ( 350 > $avg60_user2 && $avg60_user2 > 180){ $value = '3';} //normaal
if ( 180 > $avg60_user2 && $avg60_user2 > 100){ $value = '2';} //laag
if ( 100 > $avg60_user2 ){ $value = '1';} //heel laag
$instruction2 .= ';G' . $value;

// Het laatste kwartier ((heel)laag - normaal - (heel)hoog)
$value = 0;
if ( $avg60_user2 > 1500){ $value = '5';} // heel hoog
if ( 1500 > $avg60_user2 && $avg60_user2 > 350){ $value = '4';} //hoog
if ( 350 > $avg60_user2 && $avg60_user2 > 180){ $value = '3';} //normaal
if ( 180 > $avg60_user2 && $avg60_user2 > 100){ $value = '2';} //laag
if ( 100 > $avg60_user2 ){ $value = '1';} //heel laag

$difference = $avg15_user2 - $avg30_user2;
if ($difference > 500){ $value = '7';} //gestegen
if ($difference < -500){ $value = '8';} //gedaald if ($difference < 5 && $difference > -5){ $value = '6';} //constant
$instruction2 .= ';H' . $value;

// Het verbruik is bij jouw tegenstander
// vandaag (gestegen - gedaald - constant)
$value = 0;
$difference = $per_day_user1 - $yesterday_user1;
if ( $difference > 150){ $value = '7';} //gestegen
else {
if ( $difference < -150){ $value = '8';} //gedaald else{ $value = '6';} //constant } $instruction2 .= ';I' . $value; // Het laatste uur ((heel)laag - normaal - (heel)hoog) $value = 0; if ( $avg60_user1 > 1500){ $value = '5';} // heel hoog
if ( 1500 > $avg60_user1 && $avg60_user1 > 350){ $value = '4';} //hoog
if ( 350 > $avg60_user1 && $avg60_user1 > 180){ $value = '3';} //normaal
if ( 180 > $avg60_user1 && $avg60_user1 > 100){ $value = '2';} //laag
if ( 100 > $avg60_user1 ){ $value = '1';} //heel laag
$instruction2 .= ';J' . $value;

// Het laatste kwartier ((heel)laag - normaal - (heel)hoog)
$value = 0;
if ( $avg60_user1 > 1500){ $value = '5';} // heel hoog
if ( 1500 > $avg60_user1 && $avg60_user1 > 350){ $value = '4';} //hoog
if ( 350 > $avg60_user1 && $avg60_user1 > 180){ $value = '3';} //normaal
if ( 180 > $avg60_user1 && $avg60_user1 > 100){ $value = '2';} //laag
if ( 100 > $avg60_user1 ){ $value = '1';} //heel laag

$difference = $avg15_user1 - $avg30_user1;
if ($difference > 500){ $value = '7';} //gestegen
if ($difference < -500){ $value = '8';} //gedaald if ($difference < 5 && $difference > -5){ $value = '6';} //constant
$instruction2 .= ';K' . $value;

// Wat zou de tegenstander nu doen?
$value = 0;
$difference = $avg1_user1 - $avg15_user1;
if ( $difference > 1000){ $value = '1';} // sentence only visible if there is a big difference.
$instruction2 .= ';L' . $value;

echo 'instruction for user1: ' . $instruction1 . '
';
echo 'instruction for user2: ' . $instruction2 . '
';

storeValue(instructions,user1,$instruction1);
storeValue(instructions,user2,$instruction2);

}

/* $symbol = ';A';
if ($avg1_user1 > $avg1_user2){
$instruction1 .= $symbol . $avg1_user1 . '+';
$instruction2 .= $symbol . $avg1_user2 . '-';
}
else {
$instruction1 .= $symbol . $avg1_user1 . '-';
$instruction2 .= $symbol . $avg1_user2 . '+';
}

$symbol = ';B';
if ($avg15_user1 > $avg15_user2){
$instruction1 .= $symbol . $avg15_user1 . '+';
$instruction2 .= $symbol . $avg15_user2 . '-';
}
else {
$instruction1 .= $symbol . $avg15_user1 . '-';
$instruction2 .= $symbol . $avg15_user2 . '+';
}

$symbol = ';C';
if ($avg30_user1 > $avg30_user2){
$instruction1 .= $symbol . $avg30_user1 . '+';
$instruction2 .= $symbol . $avg30_user2 . '-';
}
else {
$instruction1 .= $symbol . $avg30_user1 . '-';
$instruction2 .= $symbol . $avg30_user2 . '+';
}

echo 'instruction for user1: ' . $instruction1 . '
';
echo 'instruction for user2: ' . $instruction2 . '
';

//store the final instruction in the database

storeValue(instructions,user1,$instruction1);
storeValue(instructions,user2,$instruction2);
}
*/

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Retrieve Average
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/

function retrieveAverage($user,$table){

// connect to database on server
$con = mysql_connect("localhost","database_name","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("database_name", $con);
// Return only the latest entry
$result = mysql_query("SELECT * FROM $table WHERE user ='$user' ORDER BY Time DESC Limit 1");
while($row = mysql_fetch_array($result))
{
// convert into 'interval_name', will be used later on
$a = $table;
$b = "_";
$c = $user;
$name = "$a" . "$b" . "$c";
echo $name; echo ": ";
$name += $row['value'];
echo $name . ', ' . '
';
return $name;
}
// Close database connection
mysql_close($con);
}

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Retrieve value
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/

function retrieveValue($user,$table){

// connect to database on server
$con = mysql_connect("localhost","database_name","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("database_name", $con);
// Return only the latest entry
$result = mysql_query("SELECT * FROM $table WHERE user ='$user' ORDER BY Time DESC Limit 1");
while($row = mysql_fetch_array($result))
{
// convert into 'interval_name', will be used later on
$a = $table;
$name = "$a";
echo $name; echo ": ";
$name += $row['value'];
echo $name . ', ' . '
';
return $name;
}
// Close database connection
mysql_close($con);
}

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Store values from a user in a specific table
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/

function storeValue($table,$user,$value){

// connect to database on server
$con = mysql_connect("localhost","database_name","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("database_name", $con);

// insert the data in to the fields
mysql_select_db("database_name", $con);
$sql="INSERT INTO $table (time,user,value)
VALUES
(now(),'$user','$value')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

// Close database connection
mysql_close($con);
}

/* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Delete outdated data
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
*/

function deleteOutdatedData(){

// data that is older than $outdated minutes get deleted
$outdated = 60;

// connect to database on server
$con = mysql_connect("localhost","database_name","pasw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// select the table in the database
mysql_select_db("database_name", $con);

mysql_query("DELETE FROM data WHERE Time + INTERVAL $outdated MINUTE < now() "); mysql_query("DELETE FROM avg1 WHERE Time + INTERVAL $outdated MINUTE < now() "); mysql_query("DELETE FROM avg15 WHERE Time + INTERVAL $outdated MINUTE < now() "); mysql_query("DELETE FROM avg30 WHERE Time + INTERVAL $outdated MINUTE < now() "); mysql_query("DELETE FROM avg60 WHERE Time + INTERVAL $outdated MINUTE < now() "); mysql_query("DELETE FROM instructions WHERE Time + INTERVAL $outdated MINUTE < now() "); echo "Outdated data deleted! "; // Close database connection mysql_close($con); } /* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Update graph xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx */ function updateGraph(){ global $avg1_user1, $avg1_user2; echo 'start emonGraph'; echo $user; $base = 'http://h1688209.stratoserver.net/emoncms3/api/post?apikey=a78d60a29a80fe5fa17cc717006dec7a&json={'; $val = $base . 'user1:' . $avg1_user1 . ',user2:' . $avg1_user2 . '}'; echo $val; // create a new cURL resource $ch = curl_init(); // set URL and other appropriate options curl_setopt($ch, CURLOPT_URL, "$val"); curl_setopt($ch, CURLOPT_HEADER, 0); // grab URL and pass it to the browser curl_exec($ch); // close cURL resource, and free up system resources curl_close($ch); } ?>

Leave a Comment