Spaces:
Sleeping
Sleeping
| import pandas as pd | |
| import logging | |
| import re | |
| from .db_utils import execute_query | |
| logger = logging.getLogger(__name__) | |
| def fetch_tank_data(days=7): | |
| """ | |
| Fetches oil level data for specific Tank asset IDs. | |
| Uses SQL-side JSON extraction as requested. | |
| """ | |
| query = f""" | |
| SELECT | |
| a.name AS tank_id, | |
| (d.value->>'TankOilLevelInFeet001')::float AS level_feet, | |
| (d.value->>'Timestamp')::timestamp AS sensor_timestamp, | |
| d.timestamp AS db_timestamp | |
| FROM asset_datapoint d | |
| JOIN asset a ON d.entity_id = a.id | |
| WHERE d.entity_id IN ( | |
| '2TV1OLqZSv5JwIEHomeRR1', | |
| '6UXHYoFE9as4nc25HA72AN', | |
| '5UwgGOBQ8VqdVZO37E1ci8', | |
| '2H7Gj65gRcLS1V6OTq72Hk', | |
| '7BKD81c3z8gKpGZt5bIc87', | |
| '2pd9BV69qYHsnoyiAAqJmX', | |
| '50Y5udYfIWJWLEbduASYwT', | |
| '6tUKaWyipyL8MfjaTntrSH', | |
| '6UrAIw3WfeBmCn7yYgHGKR', | |
| '64G6XlehtAyuhFP6U5V9iB', | |
| '7gmxORynTjM8Lmp4DDtZSP', | |
| '4la3XSG9sUisBdczxxjYLv' | |
| ) | |
| AND d.timestamp >= NOW() - INTERVAL '{days} days' | |
| AND d.attribute_name = 'data' | |
| ORDER BY a.name, d.timestamp ASC | |
| """ | |
| try: | |
| results = execute_query(query) | |
| if not results: | |
| logger.warning(f"No Tank data found in the last {days} days for the specified IDs.") | |
| return None | |
| df = pd.DataFrame(results) | |
| # Consistent normalization: Physical07 instead of Physical7 | |
| def normalize_tank_name(name): | |
| if not name: return name | |
| return re.sub(r'Physical(\d)$', r'Physical0\1', name) | |
| df['tank_id'] = df['tank_id'].apply(normalize_tank_name) | |
| df['sensor_timestamp'] = pd.to_datetime(df['sensor_timestamp']) | |
| return df | |
| except Exception as e: | |
| logger.error(f"Failed to fetch Tank data: {e}") | |
| return None | |