o Li5r@sdZddlZddlZddlZddlZddlZddlmZddlm Z m Z m Z m Z ddl mZmZmZmZmZz ddl mZmZmZWneyQdZdZd ZYnwdd lmZmZmZejejeZe d e ej!ed d dZ"ddZ#d0ddZ$d1ddZ%ddZ&ddZ'ddZ(da)da*ddZ+ddZ,d d!Z-d"d#Z.e"/d$d%d&Z0e"j/d'd(gd)d*d+Z1e"j/d,d-d(gd)d.d/Z2dS)2z SolarMon: solar dashboard (Victron, Growatt, EPEver, batteries) and temp-sensor API. Uses MySQL shop_stats; replicates appearance of PHP SolarMon index.php. Weather for System column from Open-Meteo (Columbia, MS). N)datetime) Blueprintrender_templaterequestResponse) MYSQL_HOST MYSQL_PORTMYSQL_DATABASE MYSQL_USERMYSQL_PASSWORD) WEATHER_LAT WEATHER_LONWEATHER_CACHE_MINUTESg@?@gQuV)ensure_ota_tablesget_latest_version_str version_lesssolarmonsolarmon_staticz/solarmon/static) static_folderstatic_url_pathcCstjttttttjjdS)N)hostportuserpassworddatabase cursorclass) pymysqlconnectrrr r r cursors DictCursorr!r!+/var/www/html/Server/blueprints/solarmon.py _get_conn'sr#c CsR|r||vs||dus||dkr|Szt||WSttfy(|YSw)N)float TypeError ValueErrorrowkeydefaultr!r!r"_float2s$r,r$cCs,|r ||vs ||dur|St||S)N)strstripr(r!r!r"_str;sr/c Cs|dus t|tr|sdSzt|}Wn ttfy!YdSwd|kr,dkr@ndStdtdt|ddddSdS)u Single-cell Li-ion: return bar fill 0–100 from voltage (2.5V–4.3V). Bar width = position in voltage range, not charge percentage. Ng@g333333@rdg?) isinstancer-r.r%r'r&maxminround)batvr!r!r"_bat_voltage_bar_pctAs "r8cCsz||}Wn tyYdSwt|}|dkrd}|d}|d}|d}|dkr/dS|dkrD|d|dkr?dd Sdd S|dkrY|d |dkrTdd Sdd S|d |dkredd Sdd S) za Return a short, human-readable delta like '5 minutes ago', '2 hours ago', '3 days ago'. r$r<zjust nowz minuter1sz agoz hourz day) Exceptionint total_seconds)dtnowdeltasecondsminuteshoursdaysr!r!r"_human_timedeltaRs$   """rFcCsvz0t||}|d|f|}Wdn1swY|s)WdSt||dWSty:YdSw)zDReturn latest firmware version string for OTA project slug, or None.z+SELECT id FROM ota_projects WHERE slug = %sNid)rcursorexecutefetchonerr<)connslugcurr)r!r!r"_ota_latest_version_by_slugls   rNcCs|durdSt|}|dkrdS|dvr|dkrdSdS|d vr"d Sd |kr,d kr6nn|d kr4dSdSd|krAdkrBdSd|krMdkrNdSd|krYdkrZdSd|kredkrhdSdSdS)z;Map WMO weather code to short condition string for display.Nr$rClear)r1rPz Partly cloudyOvercast)-0Fog3C=RainDrizzleGMSnowPRShowersUVz Snow showers_c Thunderstorms)r=)codecr!r!r"_wmo_weathercode_to_conditionss0rhc CsJdtt}z-tjj|ddid}tjj|dd}t| }Wdn1s-wYWntj j tj j tjtfyFYdSwz|dpNi}|d pUi}|d p\g}|d pcg}|d pjg}i} |d durtt|d ddnd| d<t|dkr|ddurtt|ddnd| d<t|dkr|ddurtt|ddnd| d<tt|dkr|dnd| d<d| d<t|dkr|ddurtt|ddnd| d<t|dkr|ddurtt|ddnd| d<d| d<tt|dkr |dnd| d<d| d<| WStttfy$YdSw)z Fetch today/tomorrow weather for Columbia, MS from Open-Meteo. Returns dict with w_conditions, w_temp, w_tempmax, w_tempmin, wt_conditions, wt_tempmax, wt_tempmin or None on failure. zhttps://api.open-meteo.com/v1/forecast?latitude={:f}&longitude={:f}&timezone=America/Chicago¤t_weather=true&temperature_unit=fahrenheit&daily=temperature_2m_max,temperature_2m_min,weathercodez User-Agentz SolarMon/1.0)headers )timeoutNcurrent_weatherdailytemperature_2m_maxtemperature_2m_min weathercode temperaturerr1r$w_temp w_tempmax w_tempmin w_conditionsw_icon wt_tempmax wt_tempminwt_temp wt_conditionswt_icon)formatr r urllibrRequesturlopenjsonloadsreaddecodeerrorURLError HTTPErrorJSONDecodeErrorOSErrorgetr-r5lenrh IndexErrorr&r') urlreqrespdatacwrmmaxesminscodesoutr!r!r"_fetch_weather_open_meteosB,22 22"rcCsbt}tdur ttnd}tdur$tdur$|t}||dkr$tSt}|dur/|a|a|S)z?Return cached weather or fetch and cache. Returns dict or None.Nrr9)rr@rr=_weather_cache_weather_cache_timer>r)r@ cache_minsrAweatherr!r!r"_get_weather_cacheds  rc( Cs iddddddddddddddd dd dd dd d dddddddd dd dd idd dd dd dd dd dd dd dd dd ddd dd!dd"d d#gd$dd%dd&ddddd ggd'd'd(}zt}Wn tjy|YSwz[i}d)D] }t||||<qt}|r|dd|d<|dd|d<|dd|d<|dd|d<|dd|d<|d d|d <|dd|d<|dd|d<|d d|d <|d d|d <|}|d*d+|}|rt |d,}|r |d-nd |d <t t |d.|d<t t |d/|d<|d0}|d'ur|dkrzEt |}|dkr>d1|d<n5t |d2d3} t |t | d2d3d3} t || d2d3d3| d3d3} | d4| d5| d6|d<Wn t tfyYnwWd'n 1swY|M}|d7|}|rt |d} t |d} t |d}t |d}t |d}| |d<| |d<||d<||d<||d<| | ||||d<Wd'n 1swY|9}|d8|}|r!t |d}t |d}t |d}t |d}t |d}||||||d<Wd'n 1s,wY|%}|d9|}|rSt |dt |dt |d|d"<Wd'n 1s^wYd:d;}|<}|d<|}g}|D]"}t|d=}t |d>}||t t |d?t |d@|||dAqy||d#<Wd'n 1swY|}|dB|}|rV|dCp|d$pd}t|t t frt|}nt|}dDD]}||r|d't| }nqzt |}|dEdFdGdHdI|d$<Wntt fy"|pd|d$<Ynwt|dJ|d%<t|dK}|dLddMdNdOdP}||d&<t|dQ|dQ<t|dR|dR<t|dS|dS<Wd'n 1sawY|}z|dTWn tjy}Ynw|dU|}t}|dV} |D]n}!t|!dW|!dX<|!dW}"|"d'ur|"dkrz t |"dYdZ|!d[<Wntt fyt|"|!d[<Ynwd|!d[<|!d\}#|#rt|#||!d]<nd|!d]<t| o|!d^ot|!d^| |!d_<| |!d`<dV|!da<q||db<Wd'n 1swYz|}z|dcWn tjy-Ynw|dd|}t}|de}$|D]|}!t|!df|!dX<|!df}"|"d'ur{|"dkr{z t |"dYdZ|!d[<Wntt fyzt|"|!d[<Ynwd|!d[<|!dg}%|%r||%dhk|!di<t|%||!d]<ndj|!di<d|!d]<t|$o|!d^ot|!d^|$|!d_<|$|!d`<de|!da<qB||dk<Wd'n 1swYWn tjyYnwz_|P}|dl|}|r.|dgr t |}t}t|dg||d]<|dm}&t|&o|d^ot|d^|&|d_<|&|d`<dm|da<||dn<Wd'n 1s9wYWntjtj!fyMYnwz|r}|do|}|r|dgrwt |}t}t|dg||d]<|dpd'urt|dp|dp<|dqd'urt|dq|dq<|dr}'t|'o|d^ot|d^|'|d_<|'|d`<dr|da<||ds<Wd'n 1swYWntjtj!fyYnwW|"n|"wzt |d|dd-dt|du<W|St tfy d |du<Y|Sw)vzTFetch all data for the main dashboard; return a dict with defaults for missing data.rtr$rsrrrurvrxrwryrzr{ vic_voltagegcurrentrvic_socvic_ttgz No RecordGw1PGw2PGw3PEP1PEP2P solar_powerGw1CGw2CGw3CEP1CEP2C total_currentGw1LoadGw2LoadGw3Loadtotal_inv_current batteries shed_temp shed_humidup_timeN)dateuserstasks consumption temps_latestmoisture_latest pump_latestwifiswitch_latest) wifitemps moisturemeter pumpcontrol wifiswitchzSELECT V, P, I, SOC, TTG FROM VEDirect WHERE Name = %s AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1)z Victron 2VgMbP?ISOCTTGInfinater:r9z Days z Hrs z Minaa SELECT (SELECT ((((Ppv1H * 10) << 16) + (Ppv1L * 10)) * .1) FROM Growatt WHERE Name = 'Growatt 1' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw1P, (SELECT ((((Ppv1H * 10) << 16) + (Ppv1L * 10)) * .1) FROM Growatt WHERE Name = 'Growatt 2' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw2P, (SELECT ((((Ppv1H * 10) << 16) + (Ppv1L * 10)) * .1) FROM Growatt WHERE Name = 'Growatt 3' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw3P, (SELECT ((((r3103 * 10) << 16) + (r3102 * 10)) * .001) FROM EPEver WHERE Name = 'Epever 01' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS EP1P, (SELECT ((((r3103 * 10) << 16) + (r3102 * 10)) * .001) FROM EPEver WHERE Name = 'Epever 02' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS EP2P aW SELECT (SELECT Buck1Curr FROM Growatt WHERE Name = 'Growatt 1' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw1C, (SELECT Buck1Curr FROM Growatt WHERE Name = 'Growatt 2' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw2C, (SELECT Buck1Curr FROM Growatt WHERE Name = 'Growatt 3' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw3C, (SELECT (r3105 * .01) FROM EPEver WHERE Name = 'Epever 01' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS EP1C, (SELECT (r3105 * .01) FROM EPEver WHERE Name = 'Epever 02' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS EP2C a  SELECT (SELECT Inv_Curr FROM Growatt WHERE Name = 'Growatt 1' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw1C, (SELECT Inv_Curr FROM Growatt WHERE Name = 'Growatt 2' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw2C, (SELECT Inv_Curr FROM Growatt WHERE Name = 'Growatt 3' AND LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY id DESC LIMIT 1) AS Gw3C cSs8|dkrdS|dkrdt|ddSdt|ddS)Nr EqualizedzChar: rPazDisc: )r5)rgr!r!r" _bat_status|s z)fetch_dashboard_data.._bat_statusa SELECT b.Name, b.PercentCapacity, b.Voltage, b.Current FROM Battery b INNER JOIN ( SELECT Name, MAX(id) AS max_id FROM Battery WHERE LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) GROUP BY Name ) t ON b.Name = t.Name AND b.id = t.max_id ORDER BY b.Name NameCurrentPercentCapacityVoltage)namesocvoltagerstatuszSELECT shed_Temperature AS shed_temp, shed_Humiditity AS shed_humiditity, `up`, `date`, users, tasks FROM Environment WHERE LastUpdate >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) AND shed_Humiditity <> '' ORDER BY id DESC LIMIT 1shed_Temperature)u °Cu°Cz CCrg  z.1fu °Fshed_humiditityupzup rDhrCmrrrzFALTER TABLE Temps ADD COLUMN firmware_version VARCHAR(24) DEFAULT NULLaL SELECT t.unit_id, t.node_name, t.temp, t.humid, t.bat, t.entry_date, t.firmware_version FROM Temps t INNER JOIN ( SELECT unit_id, MAX(entry_date) AS max_entry_date FROM Temps WHERE entry_date >= NOW() - INTERVAL 20 MINUTE GROUP BY unit_id ) latest ON t.unit_id = latest.unit_id AND t.entry_date = latest.max_entry_date WHERE t.entry_date >= NOW() - INTERVAL 20 MINUTE ORDER BY t.unit_id rr6 bat_bar_pctz.2fz V bat_display entry_date last_seen_agofirmware_versionota_newer_availableota_latest_versionota_project_slugrzNALTER TABLE MoistureLevel ADD COLUMN firmware_version VARCHAR(24) DEFAULT NULLa SELECT 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 rbattery_voltage created_atiis_staleFrzSELECT id, pressure_psi, pump_on, error_pressure_low, app_state, rssi, ip, mac, firmware_version, created_at FROM PumpStatus ORDER BY created_at DESC LIMIT 1rrzSELECT id, unit_id, node_name, temp, humid, relay_on, temp_rule_enabled, mac, ip, firmware_version, created_at FROM WifiSwitchStatus ORDER BY created_at DESC LIMIT 1relay_ontemp_rule_enabledrrrPr)#r#rErrorrNrrrHrIrJr,r=r%r5r&r'fetchallr/appendr2r-r.endswithrreplaceOperationalErrorrr@r8rFboolrr>dictProgrammingErrorclose)(defaultsrK ota_latestrLrrMr)r7ttgrErDrCgw1gw2gw3ep1ep2c1c2c3c4c5rrowsrrrraw_tempsuffixrgrr@ ota_tempsrbentry_dt ota_moisturecreatedota_pump ota_switchr!r!r"fetch_dashboard_datas      !"#,                                  &     .       +        r/cCst}tdi|S)z;Main SolarMon dashboard (same appearance as PHP index.php).solarmon/index.htmlN)r )rr)rr!r!r"indexgsr z/moisture/deletePOST)methodscCstjddpi}|dpd}|stddddSzt}Wntjy0td d ddYSwzFz+|}| d |fWd n1sIwY| td ddWW| Stjyw| tdd ddYW| Sw| w)z Delete all MoistureLevel records for a given device_id. Intended to be called from the SolarMon dashboard via AJAX. T)silent device_idr$z{"error": "device_id required"}izapplication/json)rmimetypez!{"error": "database unavailable"}iz.DELETE FROM MoistureLevel WHERE device_id = %sNz{"success": true}rz{"error": "delete failed"}) rget_jsonrr.rr#rrrHrIcommitrrollback)payloadrrKrMr!r!r"delete_moisture_devicensJ      rz/tempsGETc Cs"tjdkrtjntj}|dd}|dd}|dd}|dd}|dd}|dd}|d d}|d pIdd d pQd }td } zt } Wnt j ynt dddYSwzzd } | .} | d|f| } | r| drtt| ddkrt| d} Wd n1swY| } | d|| |||||||f Wd n1swY| | rt d| dddWW| St dddWW| St j y | t dddYW| Sw| w)z Temp-sensor API: accept id, temp, humid, mac, ip, bat, name. Insert reading; return '~' or '@Com:~' (parameterized queries). rrGr$temphumidmacipr6rrNr:z%Y-%m-%d %H:%M:%S~z text/plainrzESELECT command FROM Temps WHERE unit_id = %s ORDER BY id DESC LIMIT 1commandrzINSERT INTO Temps (unit_id, entry_date, temp, humid, mac, ip, bat, node_name, firmware_version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)z@Com:)rmethodargsformrr.rutcnowstrftimer#rrrrHrIrJrr-rrr)runit_idrrrrr6 node_namerrrKrrMr)r!r!r" temps_apisZ  &     r$)r)r$)3__doc__rosr urllib.errorr}urllib.requestrflaskrrrrconfigrrr r r r r r ImportErrorblueprints.otarrrpathdirnameabspath__file___BP_DIR__name__joinbpr#r,r/r8rFrNrrrhrrrrouter rr$r!r!r!r"sZ    )  *