Pass2PHP

[McMelloW]

Hi McMellow, good to know there is a how to file in this threat, a few pages back. Also have a look at the info from ergegius which is very thorough. Your issue is probably due to the symlink. Check the how to, it’s definitely stated in there

Sent from my iPhone using Tapatalk

Thanks for your input. It is obvious I search this topic first. Followed your 14-08-2019 Pass2PHP Guide.txt and the installation post of Sincze. I am not using nginx, because Apache2 and PhpMyAdmin is already up and running. I can see the domotica database with PhpMyAdmin, but no records are coming in. Can't find where to look are Pass2PHP is active (running) or not.

[Egregius]

The how-to’s are linked in the github page
This topic is getting huge to read from scratch...

[sincze]

Wow, that's nice!
That's faster than a blink of your eyes, so fast enough

I think we got ourselves a new Pass2PHP keeper from France.

[McMelloW]

The how-to’s are linked in the github page
This topic is getting huge to read from scratch...

Thank you for your reply. Yes I discovered these How-to's and they are helpful. Started reading thes and the complete topic.
I followed Trigun's guide and sincze's tips. But nothing happens.

Is there someone use Apache2 and Pass2PHP. because I did not installed and configured Nginx.

[Egregius]

I am using apache2, works fine.
Think you need to give some more information.
What does work? What doesn’t etc.
Did you execute the fetchdomoticz script to fill the database?

[Egregius]

Wow, that's nice!
That's faster than a blink of your eyes, so fast enough

I think we got ourselves a new Pass2PHP keeper from France.

That was 4 years ago, I should measure again...

[Egregius]

Doesn’t look bad. 16 msec for domoticz, 1 msec for pass2php to respond.

Code: Select all

 2020-01-30 22:22:20.771  (Xiaomi) Light/Switch (deurgarage)
2020-01-30 22:22:20.787  (STORE)		=> deurgarage	=> Open	(Pass2PHP)
2020-01-30 22:22:20.787  (SWITCH)		=>keuken=>On (functions.php:939)
2020-01-30 22:22:20.787  Status: User: Admin initiated a switch command (11/keuken/On)
2020-01-30 22:22:20.787  OpenZWave: Domoticz has send a Switch command! NodeID: 4 (0x04)
2020-01-30 22:22:20.788  (ZWAVE) Light/Switch (keuken)

[McMelloW]

I am using apache2, works fine.
Think you need to give some more information.
What does work? What doesn’t etc.
Did you execute the fetchdomoticz script to fill the database?

Checked apache2 and php and this works OK. When I look into the domotica database all tables has 0 records.
I think the script_device_pass2php.lua is not doing anything. At least there is no records in th log.

Running _fetchdomoticz.php script results in an error. I did this in the browser http://RPi-address/secure/_fetchdomoticz.php

Code: Select all

Notice: Undefined variable: db in /var/www/html/secure/_fetchdomoticz.php on line 85

Fatal error: Uncaught Error: Call to a member function query() on null in /var/www/html/secure/_fetchdomoticz.php:85 Stack trace: #0 {main} thrown in /var/www/html/secure/_fetchdomoticz.php on line 85

[Egregius]

Code: Select all

Notice: Undefined variable: db in /var/www/html/secure/_fetchdomoticz.php on line 85

That's your error
Open _fetchdomoticz in a editor to search for that line.
And some tips about debugging:

About undefined variable:
Look up the line with the error, select the variable $db and search for it in the file.
You will not find it. That's because I run _fetchdomoticz included from my floorplan. So it gets included in ajax.php.
If you don't want to use the floorplan you need to include functions.php in _fetchdomoticz.php:

Code: Select all

require 'secure/functions.php';

In functions.php you'll see "require '/var/www/config.php';". That's a config file stored outside the www root with all your personal variables. There's an example file in the secure folder. Copy it to /var/www and edit as needed.
Just noticed that in the example config the db variables are missing, you can add them:

Code: Select all

$dbname='domotica';
$dbuser='domotica';
$dbpass='fount-bonn-Subside-7protegee-7Howl-jerk-nerd8-8courier-aftermost-eldest7-Devon-9Sect-catnap-Evans-8hypnotic';

Another tip while programming, choose one of them:
A) Have 2 SSH windows open to your domoticz server. In one you have a tail to the domoticz file, in another you have tail open for the php error file.

Code: Select all

tail -f -s 0.5 -n 1000 /var/log/domoticz.log
tail -f -s 0.5 -n 1000 /var/log/apache2/phperror.log

B) Install multitail. with multitail you can watch several logfiles at once with color coding.

Code: Select all

multitail -cS domo_log /var/log/domoticz.log -I /temp/ajax.log -I /var/log/apache2/phperror.log

Another tip for using a shell window:
Add some aliases to your .profile file.
When just connected you're in your users home folder. There type nano .profile and add these lines at the end of the file:

Code: Select all

alias q="exit"
alias ll="ls -lsha"
alias upd="apt-get update && apt-get upgrade -y && apt-get dist-upgrade -y"
alias reload_profile=". ~/.profile"
alias xxa="service apache2 restart && service php7.3-fpm restart"
alias error="tail -f -s 0.5 -n 1000 /var/log/apache2/phperror.log"
alias domo="tail -f -s 0.5 -n 1000 /var/log/domoticz.log"
alias ajax="tail -f -s 0.5 -n 1000 /temp/ajax.log"
alias log="multitail -cS domo_log /var/log/domoticz.log -I /temp/ajax.log -I /var/log/apache2/phperror.log"
alias access="multitail -cS access_log /var/log/apache2/access.log"

The next time you'll connect to the shell you can use the shortcuts.

This is my config for multitail, stored in ~/.multitailrc

Code: Select all

colorscheme:domo_log
cs_re:green:.*\(STOREMODE\).*
cs_re:green:.*\(STOREICON\).*
cs_re:green:.*\(STORE\).*
cs_re:yellow,blue,bold:.*\(AJAX\).*
cs_re:white,blue,bold:.*\(SWITCH\).*
cs_re:white,red:.*\(SETLEVEL\).*
cs_re:yellow:.*\(Xiaomi\).*
cs_re:yellow:.*\(ZWAVE\).*

colorscheme:access_log
cs_re:yellow:.*ajax.php.*
cs_re:green:.*smappeepower.php.*
cs_re:green:.*cron.php.*
cs_re:green:.*pass2php.php.*

And finally: try to read as much possible of the code so you'll learn to understand how and what's done in it.

[McMelloW]

Thanks very much Egregius.

You made a very helpful posting. It seems to work right now. Is it correct that only in the Table Devices are updated?
Also your tips on aliases and multitail are great.

The suggestion of a light version with a minimal working configuration would be great. Including a few How-Todo examples.
Now you have to strip a lot of stuff and I don't know for sure oor i strpped too much.

[Egregius]

Correct, only the devices table gets updated with the lua script.
The others are filled with my scripts during runtime.

A light version is not that simple to maintain. The github repository is a complete sync of my system. Can also be interesting to have examples of how stuff can be done.

[McMelloW]

Correct, only the devices table gets updated with the lua script.
The others are filled with my scripts during runtime.

You're right it is addictive
Your script storetemps.php is this not a part of Pass2PHP. I can not find anything on this forum of it?

[Egregius]

I’ll look for it tomorrow, but I think that’s either in cron60 or in weather.

[Egregius]

The code is in _cron60:

Code: Select all

if ($d['living_temp']['s']>0&&$d['badkamer_temp']['s']>0) {
    $stamp=sprintf("%s", date("Y-m-d H:i"));
    $items=array('buiten','living','badkamer','kamer','tobi','alex','zolder');
    foreach ($items as $i) {
        ${$i.'_temp'}=$d[$i.'_temp']['s'];
    }
    $query="INSERT IGNORE INTO `temp`
        (
            `stamp`,
            `buiten`,
            `living`,
            `badkamer`,
            `kamer`,
            `tobi`,
            `alex`,
            `zolder`
        )
		VALUES (
		    '$stamp',
		    '$buiten_temp',
		    '$living_temp',
		    '$badkamer_temp',
		    '$kamer_temp',
		    '$tobi_temp',
		    '$alex_temp',
		    '$zolder_temp'
		);";
    $db = new mysqli('localhost', $dbuser, $dbpass, $dbname);
    if ($db->connect_errno>0) {
        die('Unable to connect to database ['.$db->connect_error.']');
    }
    if (!$result = $db->query($query)) {
        die('There was an error running the query ['.$query.' - '.$db->error.']');
    }
}

And in _cron3600 it gets aggregated in the hour table:

Code: Select all

$stmt=$db->query(
    "SELECT left(stamp,13) as stamp,
        min(buiten) as buiten_min,
        max(buiten) as buiten_max,
        avg(buiten) as buiten_avg,
        min(living) as living_min,
        max(living) as living_max,
        avg(living) as living_avg,
        min(badkamer) as badkamer_min,
        max(badkamer) as badkamer_max,
        avg(badkamer) as badkamer_avg,
        min(kamer) as kamer_min,
        max(kamer) as kamer_max,
        avg(kamer) as kamer_avg,
        min(tobi) as tobi_min,
        max(tobi) as tobi_max,
        avg(tobi) as tobi_avg,
        min(alex) as alex_min,
        max(alex) as alex_max,
        avg(alex) as alex_avg,
        min(zolder) as zolder_min,
        max(zolder) as zolder_max,
        avg(zolder) as zolder_avg
    FROM temp
    GROUP BY left(stamp,13)"
);
while ($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $stamp=$row['stamp'];
    $buiten_min=$row['buiten_min'];
    $buiten_max=$row['buiten_max'];
    $buiten_avg=$row['buiten_avg'];
    $living_min=$row['living_min'];
    $living_max=$row['living_max'];
    $living_avg=$row['living_avg'];
    $badkamer_min=$row['badkamer_min'];
    $badkamer_max=$row['badkamer_max'];
    $badkamer_avg=$row['badkamer_avg'];
    $kamer_min=$row['kamer_min'];
    $kamer_max=$row['kamer_max'];
    $kamer_avg=$row['kamer_avg'];
    $tobi_min=$row['tobi_min'];
    $tobi_max=$row['tobi_max'];
    $tobi_avg=$row['tobi_avg'];
    $alex_min=$row['alex_min'];
    $alex_max=$row['alex_max'];
    $alex_avg=$row['alex_avg'];
    $zolder_min=$row['zolder_min'];
    $zolder_max=$row['zolder_max'];
    $zolder_avg=$row['zolder_avg'];
    $db->query(
        "INSERT INTO `temp_hour`
            (`stamp`,
                `buiten_min`,
                `buiten_max`,
                `buiten_avg`,
                `living_min`,
                `living_max`,
                `living_avg`,
                `badkamer_min`,
                `badkamer_max`,
                `badkamer_avg`,
                `kamer_min`,
                `kamer_max`,
                `kamer_avg`,
                `tobi_min`,
                `tobi_max`,
                `tobi_avg`,
                `alex_min`,
                `alex_max`,
                `alex_avg`,
                `zolder_min`,
                `zolder_max`,
                `zolder_avg`
            )
        VALUES
            ('$stamp',
            '$buiten_min',
            '$buiten_max',
            '$buiten_avg',
            '$living_min',
            '$living_max',
            '$living_avg',
            '$badkamer_min',
            '$badkamer_max',
            '$badkamer_avg',
            '$kamer_min',
            '$kamer_max',
            '$kamer_avg',
            '$tobi_min',
            '$tobi_max',
            '$tobi_avg',
            '$alex_min',
            '$alex_max',
            '$alex_avg',
            '$zolder_min',
            '$zolder_max',
            '$zolder_avg'
        )
        ON DUPLICATE KEY UPDATE
            `buiten_min`='$buiten_min',
            `buiten_max`='$buiten_max',
            `buiten_avg`='$buiten_avg',
            `living_min`='$living_min',
            `living_max`='$living_max',
            `living_avg`='$living_avg',
            `badkamer_min`='$badkamer_min',
            `badkamer_max`='$badkamer_max',
            `badkamer_avg`='$badkamer_avg',
            `kamer_min`='$kamer_min',
            `kamer_max`='$kamer_max',
            `kamer_avg`='$kamer_avg',
            `tobi_min`='$tobi_min',
            `tobi_max`='$tobi_max',
            `tobi_avg`='$tobi_avg',
            `alex_min`='$alex_min',
            `alex_max`='$alex_max',
            `alex_avg`='$alex_avg',
            `zolder_min`='$zolder_min',
            `zolder_max`='$zolder_max',
            `zolder_avg`='$zolder_avg';"
    );
}

[McMelloW]

Thanks a lot Egregius

Started of with storetemps.php It looks great.
Set numberofhours and numberofdays to 1000. However I just got just over 4 days and just 1 month of data in the table. Is this because of the data in the domoticz.db of Domoticz? I would love to have a longer history.

Will have a go in changing it for all the energy devices of the meter and solarpanels.

[Egregius]

I think I don’t have a storetemps file. Where did you get it? What’s in it?

[McMelloW]

I think I don’t have a storetemps file. Where did you get it? What’s in it?

This page on your website.
https://egregius.be/2018/store-temperat ... -analyses/

[Egregius]

Oh, that’s very old
It’ll fetch the data from domoticz. I don’t remember if there’s another limit than the domoticz db on it. Have a look at the json api wiki here, maybe there’s more information there.

[McMelloW]

Oh, that’s very old
It’ll fetch the data from domoticz. I don’t remember if there’s another limit than the domoticz db on it. Have a look at the json api wiki here, maybe there’s more information there.

Thanks, I will check the wiki

[madradrakie]

i was looking for a way to improve the handling speed of my sonoff pirs and found this topic. I have found the installation guide and have some basic skills so would like to give it a try. Before i start i have a question. My pirs are connected through the sonoff rf bridge. The pirs sends a 433mhz signal to the sonoff bridge. The sonof bridge decodes this signal in a MQTT messages which domoticz receives. When domoticz receives the messages a light is switched with a blocky script. This last part i want to replace by the pass2php script to improve the handling speed.

The MQTT messages looks like this:
2020-02-08 13:36:26.140 MQTT: Topic: domoticz/in, Message: {"idx":982,"nvalue":0,"svalue":"15420942","Battery":200,"RSSI":6}

The blocky scripts look like this:

Knipsel.JPG (32.17 KiB) Viewed 3803 times

Is this also possible with one of the php scripts?

Thanks in advance.