o Li9@s`dZddlZddlZddlmZmZmZmZmZddl m Z m Z m Z m Z edeZe e e e ejjdZddZd d Zejd d gd ddZejddd gd ddZejddd gd ddZejddd gd ddZedddZedddZejd d d!gd d"d#Zejd$d%gd d&d'Zed(d)d*Zejd+d%gd d,d-ZdS).zK Moisture Meter: receives readings from ESP32, stores in MySQL shop_stats. N) Blueprintrequestjsonifyrender_templateResponse) MYSQL_HOST MYSQL_USERMYSQL_PASSWORDMYSQL_DATABASEmoisture)hostuserpassworddatabase cursorclasscCstjditS)N)pymysqlconnect DB_CONFIGrrr+/var/www/html/Server/blueprints/moisture.pyget_dbsrc Cs|l}|dz|dWn tjyYnwz|dWn tjy-Ynwz|dWn tjy?Ynw|dz|dWn tjyVYnwz|dWn tjyhYnwWdn1sswY|dS)Na CREATE TABLE IF NOT EXISTS MoistureLevel ( id INT AUTO_INCREMENT PRIMARY KEY, device_id VARCHAR(16) NOT NULL, node_name VARCHAR(50) DEFAULT NULL, moisture INT NOT NULL, battery_voltage FLOAT NOT NULL, command VARCHAR(20) DEFAULT 'sleep', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) zHALTER TABLE MoistureLevel ADD COLUMN command VARCHAR(20) DEFAULT 'sleep'zGALTER TABLE MoistureLevel ADD COLUMN node_name VARCHAR(50) DEFAULT NULLzNALTER TABLE MoistureLevel ADD COLUMN firmware_version VARCHAR(24) DEFAULT NULLa/ CREATE TABLE IF NOT EXISTS device_command ( device_id VARCHAR(16) PRIMARY KEY, command VARCHAR(20) NOT NULL DEFAULT 'sleep', new_device_id VARCHAR(16) DEFAULT NULL, new_node_name VARCHAR(50) DEFAULT NULL ) zLALTER TABLE device_command ADD COLUMN new_device_id VARCHAR(16) DEFAULT NULLzLALTER TABLE device_command ADD COLUMN new_node_name VARCHAR(50) DEFAULT NULL)cursorexecuterOperationalErrorcommit)conncurrrr ensure_tabless>     (rz/readingPOST)methodsc CsNzttjddd}|stddddfWS|d}|d}|d }|d }|d }|dus:|dus:|durDtdd ddfWSt|trM|sWtdd ddfWS|dd}t|trf|ndddpod}zt|}Wntt fytddddfYWSwzt |}Wntt fytddddfYWSwt|tr|ndddpd}t }zt || }|d|f|}|r|dnd} | dvrd} |r|dpdddnd} |r|dpdddnd} |d||||| |f| r| n|} | r| n|p"d} | r-|d|f| r7|d|f| dkrC|d|fWdn 1sNwY|W|n|wtjd| | | d d!d"}t|d#d$d%WStjytdd&dd'fYSty}ztdt|dd'fWYd}~Sd}~ww)(NTforcesilentFzInvalid or missing JSONokerror device_id node_namer battery_voltagefirmware_versionz/Missing device_id, moisture, or battery_voltagez$device_id must be a non-empty string2zmoisture must be a numberz battery_voltage must be a numberUSELECT command, new_device_id, new_node_name FROM device_command WHERE device_id = %scommandnosleepsleepr1reboot new_device_id new_node_namezINSERT INTO MoistureLevel (device_id, node_name, moisture, battery_voltage, command, firmware_version) VALUES (%s, %s, %s, %s, %s, %s)NodezCUPDATE device_command SET new_device_id = NULL WHERE device_id = %szCUPDATE device_command SET new_node_name = NULL WHERE device_id = %sr4zBUPDATE device_command SET command = 'nosleep' WHERE device_id = %s)r$r0r'r(),:) separatorszapplication/json)statusmimetypeDatabase error)rget_jsonrget isinstancestrstripint TypeError ValueErrorfloatrrrrfetchonelowerrclosejsondumpsrrError Exception)datar'r(r r)r*rrrowcurrent_commandr5r6resp_device_idresp_node_namebodyerrrreadingEs     "  " "$  ! "rWz/device//commandPUTc CsHzttjdddp i}|dpd}|dvr#tdddd fWS|d d }|s7tdd dd fWSt}z+t||}| d |||fWd n1sWwY| W| n| wtd|ddfWSt j ytddddfYSty}ztdt|ddfWYd }~Sd }~ww)zESet command for device: body {"command": "sleep"|"nosleep"|"reboot"}.Tr r0r,r2Fz/command must be 'sleep', 'nosleep', or 'reboot'r#r&Nr+invalid device_idzdINSERT INTO device_command (device_id, command) VALUES (%s, %s) ON DUPLICATE KEY UPDATE command = %s)r$r0r;r>r?)rr@rArDrJrrrrrrrKrrNrOrC)r'rPcmdrrrVrrr set_commands4  "r[z/device//device_idc CsJzutjdddp i}|dpddd}|s#tddd d fWS|dd}|s7tdd d d fWSt}z+t||}|d |||fWdn1sWwY| W| n| wtd||d dfWSt j ytddd dfYSt y}ztdt|d dfWYd}~Sd}~ww)zVAssign new device ID: body {"device_id": "M001"}. Device will pick it up on next POST.Tr r'r,Nr+Fzdevice_id requiredr#r&invalid device_id in URLzINSERT INTO device_command (device_id, command, new_device_id) VALUES (%s, 'nosleep', %s) ON DUPLICATE KEY UPDATE new_device_id = %s)r$ current_idnew_idr;r>r?rr@rArDrrrrrrrKrrNrOrC)r'rPr^rrrVrrr set_device_id4  "r`z/device//node_namec CsJzutjdddp i}|dpddd}|s#tddd d fWS|dd }|s7tdd d d fWSt}z+t||}|d |||fWdn1sWwY| W| n| wtd||ddfWSt j ytddd dfYSt y}ztdt|d dfWYd}~Sd}~ww)zlAssign new node name: body {"node_name": "Living Room"}. Device will pick it up on next POST (max 50 chars).Tr r(r,Nr-Fznode_name requiredr#r&r+r\zINSERT INTO device_command (device_id, command, new_node_name) VALUES (%s, 'nosleep', %s) ON DUPLICATE KEY UPDATE new_node_name = %s)r$r]r6r;r>r?r_)r'rPnew_namerrrVrrr set_node_namerarcz /api/readingscCstttjddd}zGt}z>|%}|d|f|}|D]}|dr2|d |d<q#Wdn1s=wYt d|dW| WS| wt j yft d d d dfYSw) z-Return recent moisture readings (latest 100).limitdr?zSELECT id, device_id, node_name, moisture, battery_voltage, command, firmware_version, created_at FROM MoistureLevel ORDER BY id DESC LIMIT %s created_atNTr$readingsFr>r#)minrErargsrArrrfetchall isoformatrrKrrN)rdrrrowsrrrr api_readingss*   roz/api/readings/latestcCszEt}z<|#}|d|}|D]}|dr$|d|d<qWdn1s/wYtd|dW|WS|wtj yXtdddd fYSw) z-Return the latest reading per device_id only.a  SELECT m.id, m.device_id, m.node_name, m.moisture, m.battery_voltage, m.command, m.firmware_version, m.created_at FROM MoistureLevel m INNER JOIN ( SELECT device_id, MAX(id) AS max_id FROM MoistureLevel GROUP BY device_id ) latest ON m.device_id = latest.device_id AND m.id = latest.max_id WHERE m.created_at >= NOW() - INTERVAL 20 MINUTE ORDER BY m.device_id rfNTrgFr>r#r?) rrrrkrArlrrKrrN)rrrmrnrrrapi_readings_latests"    rpz/api/readings/clear-oldDELETEcCszKtjjddtd}tdt|d}t}z1|}|d|f|j }Wdn1s/wY| t d|dd fW| WS| wt jy^t d d d d fYSw)zLDelete records older than the given days (default 1). Returns count deleted.days)typeimzDDELETE FROM MoistureLevel WHERE created_at < NOW() - INTERVAL %s DAYNT)r$deletedr;Fr>r#r?)rrjrArEmaxrirrrrowcountrrrKrrN)rrrrrurrrapi_readings_clear_old#s$ rxz/api/device//commandGETcCs$|dd}|stddddfSzkt}zbt||?}|d|f|}|r2|dnd }|d vr>d }|rI|d pFd nd}|rV|d pSd nd}Wdn1sbwYtd||pmd|ppddW| WS| wt j ytddddfYSw)z?Return current command for device (for dashboard toggle state).Nr+FrYr#r&r/r0r1r2r5r,r6T)r$r0r5r6r>r?) rDrrrrrrIrJrArKrrN)r'rrrQr0r5r6rrrget_device_command9s0  rz/cCstdS)Nzmoisture/dashboard.html)rrrrr dashboardUsr|z/healthcCstddidfS)Nr$Tr;)rrrrrhealthZsr}) __doc__rLrflaskrrrrrconfigrrr r __name__bpcursors DictCursorrrrrouterWr[r`rcrorprxrzr|r}rrrrsD  , T