Rules Upload for RFID-Offline-Check via mqtt

[AtlantisUnion]

Target: ESP 8266 with RFID - PN532 or PN522 Reader as Doorcontrol even when network temporary is down.

Idee:
- Store all Keys that should get access in ESP Easy Rules to open the door even there is not network running.
Therefore I saw a small hand of solutions.
- In Case of successfull enter sent key- info and succesfull open status via mqtt by rule
- In Case of a unknown Key send this KEY via mqtt to ADMIN by rule.
- Admin should enlarge the actual Rules and uppload a new new rule list via mqtt to ESP.
Therefore I found NO Solution at all. This seems to be my biggest problem-
Somebody mentioned a Generic mqtt import as device. Bit this is for max. 4 lines and I have no idee, how I can get this lines as futre Rule stored...

I could not find such a solution somewhere and I never worked with ESP Easy Rules before, but what I still read makes me optimistic it should work this way I have in mind...

Any idea how to get the Rule Text complete uploaded by mqtt.
I need to store around 30-50 Keys inside this rules. Is this too mutch data for storing inside rules?
Do the rules keep stored during power off?
Any idee where i can read more about this topic that can help me out?

The future Rules should look similar to
On RFID#Tag do
Hit=0
if [RFID#Tag]=1899267376 (First Key)
Event,OkTag
Hit=1
endif
...
if [RFID#Tag]=83398378 (Last Key)
Event,OkTag
Hit=1
endif

if Hit=0
publish via mqtt to iobroker ,{"Timecode, Key, Access denied }
endon


On OkTag do
gpio,13,0
gpio,14,1
publish via mqtt to iobroker ,{"Timecode, Key, Access acepted/denied }
timerSet,1,5
endon

On Rules#Timer=1 do
gpio,13,1
gpio,14,0
publish via mqtt to iobroker,{"Timecode, Dooropener dactivated again}
endon

Thanx for every hint

Atlantis

[TD-er]

Here an example to search through a long list of tags (sorted), so the rules parsing will not take too long.
https://espeasy.readthedocs.io/en/lates ... orted-list
The search takes O(log(n)) calls to parse the rules, so it takes (on average) one extra call when you double the list length.

ESPEasy also supports some way of provisioning the settings and/or rules and even firmware update.
See: https://espeasy.readthedocs.io/en/lates ... d-settings

Just an example of rules to make provisioning a bit more structured:

Code: Select all

// e.g.
// event,updateSettings
On updateSettings Do
  provisionprovision
  provisionconfig
Endon

On updateCredentials Do
  provisionsecurity  
Endon

// e.g.
// event,updateRules
On updateRules Do
  provisionrules,1
  provisionrules,2
  provisionrules,3
Endon

// Update rules files and settings
// e.g.
// event,updateRulesSettings 
On updateRulesSettings Do
  AsyncEvent,updateSettings
  AsyncEvent,updateRules
  Reboot
Endon

// e.g.
// event,PerformFirmwareUpdate=firmware_max_ESP32_16M8M_LittleFS.bin
On PerformFirmwareUpdate=* Do
  pwm,2,100,0,8
  provisionfirmware,%eventvalue1%
Endon

On provisionfirmware#success=* Do
  gpio,2,0
  Reboot
Endon

On provisionfirmware#failure Do
  gpio,2,0
  Reboot  
Endon

You can give event commands via MQTT.
See: https://espeasy.readthedocs.io/en/lates ... ing-events

Oh forgot to answer your last question, yes the files will remain on the file system of the node, so no need for network connection to work.
But keep in mind that while ESPEasy may scan for networks, it may act a bit slower.

[AtlantisUnion]

Thank you for this several links.

With them I could get my Rules running.
The way to make the If states and that`s not allowed to make empty lines ... cost me a lot of experence, but now its`s running...

Code: Select all

On RFID#KEY  do
If  [RFID#KEY] = 423297763 or [RFID#KEY] = 1931276789 .....
Event,EINTRETEN
Else
Event,SPERRE
endif
timerSet,1,6
endon

On EINTRETEN Do
LCD,2,1,'Message:  EINLASS'
Publish %sysname%/RFID/MESSAGE,"EINLASS"
Publish %sysname%/RFID/TAG,[RFID#KEY]
Publish %sysname%/IP,%ip%
gpio,16,1
Endon

On SPERRE Do
LCD,2,1,'Message: SPERRE      '   
Publish %sysname%/RFID/MESSAGE,"Sperre"
Publish %sysname%/RFID/TAG,[RFID#KEY]
Publish %sysname%/IP,%ip%
gpio,16,0
Endon

On NOKEY Do
LCD,2,1,'Message: No Key          '  
Publish %sysname%/RFID/MESSAGE,"No Key"
Publish %sysname%/RFID/TAG,[RFID#KEY]
 Publish %sysname%/IP,%ip%
Endon

On Rules#Timer=1 do 
GPIO,16,0
Event,NOKEY
endon

With the link for uploading the rules I can not find this menues in my own ESP:
I have: Build: ESP_Easy_mega_20230622_normal_ESP8266_4M1M Jun 22 2023
There is no menue available to do that:


Any other idea how I can Upload from outside or load automatic via rule from ESP this list)

[Ath]

The way to make the If states and that`s not allowed to make empty lines ... cost me a lot of experence, but now its`s running...

Not sure what you're trying to say, but Rules can have empty lines without issues. Statements have to be on 1 line though, you can't split them across multiple lines

With the link for uploading the rules I can not find this menues in my own ESP:
I have: Build: ESP_Easy_mega_20230622_normal_ESP8266_4M1M Jun 22 2023
There is no menue available to do that:

By default, the Rules option is disabled an a new install of ESPEasy. That has to be enabled from the Tools/advanced page.

[Ath]

Code: Select all

On RFID#KEY  do
If  [RFID#KEY] = 423297763 or [RFID#KEY] = 1931276789 .....
Event,EINTRETEN
Else
Event,SPERRE
endif
timerSet,1,6
endon

- ESPEasy doesn't really like double (or more) spaces between items in a line, best would be to reduce that to single spaces
- In the event handling a new key, you can use %eventvalue1% instead of [RFID#KEY], to use the value at the moment the event happened not the current value of the variable.

It looks like you are trying to match multiple tags that can pass, have you looked at the example over here? (Though it might be a somewhat daunting for a non-programmer )

[AtlantisUnion]

Hi Ath

Thanks for answering and helping me again so fast.

Yes I looked at this example more than once, because this looks very suitable for me, and you are right, this was a little to difficult to understand for me.
The "On checkID Do" rules I can understand mostly.

The biggest question I could not solve is the way how I can upload the txt files...

If you can give me some hint, how I can do this i am shure I can user the sort Algorithm also,to speed up the system.
My base is an iobroker solution, and the data exchange is via mqtt, but I also have a NAS in the network to store some txt files...

In best case I want to Upload a list with Keys, corresponding Names, Opening Times, Object Number.

Because I want to use this solution for several Objects with users that use all or a part of them...

Best regards

AtlantisUnion

[AtlantisUnion]

Additional information:

My son prgrammed in the past a working version for the doorcontrol in phyton for a RASPI but with an other RF ID Reader and with a MY SQL Dtabase (ON NAS in the network) for storing the access data.
To be independand of network, he installed also a further local db on the pi to keep the system running untill the network is back.

But I cannot use the Raspi Version because of the available wiring in the objects (Only 4 Wires lkeft for Power and Data) and the taller pcb size of the pi, so I switched to ESP and I2C Reader, but phyton is not my language.
I am comming from C++... and unfortunately my son moved away, so there is no support from him any more...

So I need a solution I can manage myself and that is the way wy Im searching.

To understand the surrounding I added the old version also even this is far away from my questions...

Here the former Version for pi in phyton :

Code: Select all

#!/usr/bin/python
# coding=utf-8
# Datei functions.py
import sys
import time
import serial
#import MySQLdb
import mysql.connector
import RPi.GPIO as GPIO
from time import sleep
import lcddriver
from datetime import datetime
import pymysql.cursors
from mysql.connector import errorcode
# Auslesen der RFID-Transponder
def read_rfid():
        ser = serial.Serial("/dev/serial0")
        ser.baudrate=9600
        daten = ser.read(14)
        ser.close()
        daten = daten.replace("\x02", "" )
        daten = daten.replace("\x03", "" )
        return daten

def connect(host,port,user,passwd,database):
        try:
                cnx = mysql.connector.connect(host=host,port=port,user=user,passwd=passwd,database=database)
                cnx.autocommit = True
                return cnx
        except mysql.connector.Error as err:
                if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                        print("Something is wrong with your user name or password")
                elif err.errno == errorcode.ER_BAD_DB_ERROR:
                        print("Database does not exist")
                else:
                        print(err)


def query(query,cnx):
        mycursor = cnx.cursor()
        mycursor.execute(query)
        myresult = mycursor.fetchall()
        return myresult

def get_name(id,c1,c2): #c1 und c2 sind hier die zwei Datenbanken lokal und die NAS
        print

def log_send(cnx,key,objekt):
#       time=datetime.now()
#       a = datetime.strptime('my date', "%b %d %Y %H:%M")
        sql="INSERT INTO LOG VALUES('%s',%d,CURRENT_TIMESTAMP)"%(key,objekt)
        cur=cnx.cursor()
        try:
                cur.execute(sql)
                print "Log Eintrag für "+key + " erstellt"
        except mysql.connector.Error as err:
                print ("Error: {}".format(err))
def clone():
        nas=connect("192.168.2.250",3306,"XYZ","WXYZ","ZG_KONTROLLE")
        local=connect("localhost",3306,"XYZ","XYZ","RFID")
        nascur=nas.cursor()
        localcur=local.cursor()
        localcur.execute("SELECT * FROM RFID")
        result= localcur.fetchall()
        for x in result:
                print x


#Überprüft, ob eine gegebene ID auf einer cnx Datenbank vorhanden ist, entwerder lokal oder auf NAS möglich, objekt definiert das Hausobjekt
def checkID(id,objekt,cnx,database_type): #type 0 ist lokal und type 1 ist auf NAS
        if database_type==0:
                sql="SELECT rfid, name, DAUER from RFID"
        elif database_type==1:
                sql="SELECT KeyID,NutzerID FROM FREISCHALTUNG WHERE ObjektID=%d AND STATUS='aktiv"%(objekt)

        result=query(sql,cnx)
        valid=0
        for x in result:
                if str(x[0])==id:
                        valid=1
                        open_relais(x[1],x[2])

        return valid


def update_slave(master,slave,objekt):
        sql="SELECT KeyID, Nutzer,VON, BIS, Dauer from FREISCHALTUNG, NUTZER WHERE ObjektID=%d AND STATUS='aktiv' AND NUTZER.NutzerID=FREISCHALTUNG.NutzerID"%(objekt)
#       print sql
        master_data=query(sql,master)
        slavecur=slave.cursor()
        for x in master_data:
                sql_slave="INSERT INTO RFID VALUES('%s','%s','%s','%s','%d')"%(x[0],x[1],x[2],x[3],x[4])
                #print sql_slave
                try:
                        slavecur.execute(sql_slave)
                        print "Neuer Eintrag in NAS erstell"
                except mysql.connector.IntegrityError as err:
                        print ("Error: {}".format(err))
def open_relais(name,t):
        lcd.lcd_display_string("Willkommen zu Hause",1)
        lcd.lcd_display_string(name,3)
        GPIO.output(27, True)
        time.sleep(t)
        GPIO.output(27, False)
        lcd.lcd_clear()


def print_no_entry():
        lcd.lcd_display_string("#"+id+" nicht",1)
        lcd.lcd_display_string("autorisiert",3)
        sleep(3)
        lcd.lcd_clear()
        lcd.lcd_display_string("Freischalten",1)
        lcd.lcd_display_string("lassen",3)
        sleep(2)



try:
        #Relee Startkonfiguration
        GPIO.setwarnings(False)
        GPIO.setmode(GPIO.BCM)
        GPIO.setup(27,GPIO.OUT)
        #1: S10, 2: S10_1, 3: S12, 4: SCHEUNE_S10, 5: GARAGE_S10
        #Hier wird hart festgelegt, welches Objekt durch den Pi verwaltet wird
        objekt=1
        nas=connect("192.168.2.250",3306,"XY","XYZ","ZG_KONTROLLE")
        loc=connect("localhost",3306,"XYZ","XYZ","RFID")

        #Display Configuration
        lcd=lcddriver.lcd()
        lcd.lcd_clear()


        #Chip einlesen
        while 1:
                lcd.lcd_display_string("Bitte Chip an",1)
                lcd.lcd_display_string("Lesegeraet halten",3)
                id=read_rfid()
                lcd.lcd_clear()
                print id+" wurde eingelesen"
                #Name aus Datenbank einlesen

                if checkID(id,objekt,loc,0): #erst wird lokal gecheckt, dann auf NAS
                        print "Zugriff für "+ id + " gegeben"
                else: #wenn lokal nicht vorhanden, wird eigene Datenbank aktualisiert
                        update_slave(nas,loc,objekt)
                        if checkID(id,objekt,loc,0):
                                print "Zugriff für "+id +" gegeben"
                        else:
                                log_send(nas,id,objekt)
                                print_no_entry()
except(KeyboardInterrupt,SystemExit):
        nas.close()
        loc.close()
        print "Program terminated"

My database structure on the NAS is like

• Table: FREISCHALTUNG (ACCESS DEFINITION)
  1 KeyID varchar(14) RFID TAG
  2 NutzerID int(11) User ID
  3 ObjektID int(11) object ID
  4 VON datetime timerange start
  5 BIS datetime timerange end
  6 Dauer int(11) duration for opening signal
  7 STATUS varchar(20)
  
  Table LOG:
  1 KeyID varchar(14) RFID TAG
  2 ObjektID int(11) Object ID
  3 CREATED_AT datetime timestamp
  
  Table Nutzer (USER)
  1 NutzerID int(11) USER ID
  2 Nutzer USER
  
  Table OBJEKT
  1 ObjektID int(11) Object ID
  2 Bezeichnung varchar(20) Object NAME

[Ath]

The biggest question I could not solve is the way how I can upload the txt files...

That might be simpler then you think, or I'm misunderstanding what you are saying. The sorted list of Tag number can simply be copy/pasted in the rules file, just like in the documentation example

Trying to do all user data/access management on ESPEasy is a bit too much to ask for the limited resources available, AFAICS.

[TD-er]

Did you check the provisioning options I mentioned in a previous post?
See also the "Archive" options you need to set to provide a base URL for the settings/rules to download?
https://espeasy.readthedocs.io/en/lates ... d-settings
The idea is that you first must setup some base permissions/credentials etc. before any provisioning command will be accepted.

[AtlantisUnion]

Hi TD

I have no Idea how I can upload a picture here in this Forum to show you my menue tree of "Tools".

The Menue that is shown in the link you shared
https://espeasy.readthedocs.io/en/lates ... d-settings
looks like a Sub-Menue below the Main Menue "Tools"

I Checked all submenues`s , but I cannot find the one that is shown in the link called "Download Esttings" or "Setting ARchiev"
How can I reach this menue?

Thanx Steffen

[TD-er]

You need FEATURE_SETTINGS_ARCHIVE defined as 1 and this is only included in either Custom builds or the MAX build.

This because it is a rather specific feature and takes up quite a bit in the build size.

So as a first test to see if it is a usable feature for you, you can try to flash a "Custom" build (or if you have an ESP32 with 16M flash, please use the "MAX" build)

If it is a usable feature for you, then we can help you to create a custom build with the plugins you need.

[AtlantisUnion]

I just have 8266 types available. So oinly the custom builds are possible for testing.
Where can i find the information which custom build this necessary menue has included?

In my overview there is no information whether the FEATURE_SETTING_ARCHIVE is included or not.
https://espeasy.readthedocs.io/en/lates ... al-plugins

[TD-er]

Well this feature is still a work-in-progress as I am not yet finished with it.
But I have already had a few units deployed in the field which do get updated with rules/settings/firmware every now and then via MQTT.

So I can help you through getting it to run, but documentation is not yet finished simply because the functionality is still not completed.
However I guess there won't be much changes in the actual functionality anymore as most quirks you only see when actually using it, are dealt with now

[AtlantisUnion]

That sounds great. How can I continue?

[AtlantisUnion]

Ì need a custom build with minimum the integration of
Display - LCD2004
RFID - PN532
FEATURE_SETTINGS_ARCHIVE Or other mqtt upload funktion
Rules



Optinal
- Switch input - Switch
- Environment settings
-sql connection if somehow available.

Sorry my English is not the best...

AtlantisUnion

[TD-er]

It should be enabled in the "Custom" builds, both for ESP32 as well as ESP8266. (and the ESP32 MAX builds)

[AtlantisUnion]

Hi TD-er
Do you have a linkfor me to an overview of the buiilds including this Information whether "Feature_Setting_Archieve is included or not"?
In my overview it was not mentioned..., so I have no Idea witch version is including this menue.

Best Regards

Atantis Union

[Ath]

Do you have a linkfor me to an overview of the buiilds including this Information whether "Feature_Setting_Archieve is included or not"?
In my overview it was not mentioned..., so I have no Idea witch version is including this menue.

Because of the limited available space in the binary file, there is no textual description of all used compile-time options. We are working on an external solution for that, but that will take some more time.

The regular Custom builds don't include the LCD2004 and PN532 plugins, but do include the provisioning feature, so I've built a Custom ESP8266 build that does include these plugins:

ESP_Easy_mega_20230728_custom_ESP8266_4M1M.zip

(1.26 MiB) Downloaded 55 times

You can use the Firmware Update button on the Tools page to either upload the .bin or .bin.gz file (just 1 is enough, the .gz file is just a compressed version of the .bin file that is also supported by the OTA feature )

[TD-er]

N.B. the link is a .zip file, so you must at least unzip that file and not upload the .zip
In there will be a .bin and .bin.gz as Ton mentioned.

[AtlantisUnion]

Is this download link working?
When I try it never comes to an end but is increasing the amount of donwloads shown in the forum.

AtlantisUnion

[AtlantisUnion]

In the meanwhile the download was working.

[AtlantisUnion]

In the offered build (the bin version) there is also no additional menue setting "archiv included"... inside the main Menue Tools...

[Ath]

It also needs the FEATURE_DOWNLOAD compilation flag set, so here's another try (checked this on a device, and the menu and its options are there )

ESP_Easy_mega_20230803_custom_ESP8266_4M1M.zip

(1.27 MiB) Downloaded 46 times