Open kaleben0 opened 4 weeks ago
danielbrendel
commented
4 weeks ago Hi,
kaleben0
commented
4 weeks ago -If there was a way to take the water, fertilize, humidity, & light data and generate some 30day, 90day, 3month, 6 month, 1 year graphs were the users could visualize the data, it might help to find those plants that are, for example, not getting enough water or too much. Might be able to use PHP's RRD extension to do the work. I am not sure, I haven't played with it first hand.
kaleben0
commented
4 weeks ago If not graphs, then maybe a way to export a PDF report
kaleben0
commented
3 weeks ago So I was playing around today and tried to use the database data to make some example graphs. It did not go well. So I thought about maybe a calendar & table to show the times that a plant was watered and fertilized. (See below)
Something like this, you might be able to the existing calendar function.
(made this in chatgpt, because I am currently working 16 hour shifts and I am sooooo tired)
<?php
$host = 'localhost';
$dbname = 'hortus';
$username = 'hortus';
$password = ' ';
// Database connection
$mysqli = new mysqli($host, $username, $password, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Fetch watering and fertilizing events for the last 30 days from the database, along with plant names and scientific names
$query = "
SELECT p.id AS plant_id, p.name AS plant_name, p.scientific_name AS scientific_name,
pl.content, DATE(pl.updated_at) AS event_date
FROM plants p
LEFT JOIN plantlog pl ON p.id = pl.plant
WHERE pl.content LIKE '[System] last_%'
AND pl.updated_at >= NOW() - INTERVAL 30 DAY
ORDER BY p.id, pl.updated_at
";
$result = $mysqli->query($query);
// Check if query was successful
if (!$result) {
die("Query failed: " . $mysqli->error);
}
// Initialize an array to store events for each plant
$plantsEvents = [];
// Populate the array with events
while ($row = $result->fetch_assoc()) {
$plantId = $row['plant_id'];
$plantName = $row['plant_name'];
$scientificName = $row['scientific_name']; // Get scientific name
$eventDate = $row['event_date'];
// Initialize the plant ID in the array if not already set
if (!isset($plantsEvents[$plantId])) {
$plantsEvents[$plantId] = [
'name' => $plantName,
'scientific_name' => $scientificName, // Store scientific name
'watered' => [],
'fertilised' => []
];
}
// Extract the type of event (watered or fertilised)
if (strpos($row['content'], 'last_watered') !== false) {
$plantsEvents[$plantId]['watered'][] = $eventDate;
} elseif (strpos($row['content'], 'last_fertilised') !== false) {
$plantsEvents[$plantId]['fertilised'][] = $eventDate;
}
}
// Bootstrap header
echo '<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Plant Care Calendar</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
<style>
.calendar {
display: grid;
grid-template-columns: repeat(7, 1fr);
gap: 2px;
margin-bottom: 20px;
}
.day {
border: 1px solid #ddd;
padding: 5px;
position: relative;
height: 80px; /* Increased height for better layout */
text-align: center;
}
.icons {
display: flex;
justify-content: center; /* Center icons */
margin-top: 5px; /* Space between date and icons */
}
.icon {
font-size: 20px; /* Adjust icon size */
margin: 0 5px; /* Space between icons */
}
.watered .fa-droplet { color: blue; }
.fertilised .fa-seedling { color: green; }
.legend {
display: flex;
margin-top: 10px;
margin-bottom: 10px;
}
.legend div {
display: flex;
align-items: center;
margin-right: 15px;
}
.legend div i {
margin-right: 5px;
}
</style>
</head>
<body>
<div class="container">
<h1 class="my-4">Plant Calendar</h1>';
// Loop through each plant to display the calendar
foreach ($plantsEvents as $plantId => $events) {
echo '<div class="plant-calendar">';
// Get the current month and year
$currentMonth = date('F'); // Full month name (e.g., October)
echo "<h3>Plant ID: <strong>{$plantId}</strong> - {$events['name']} (<em>{$events['scientific_name']}</em>)</h3>";
echo "<h4>$currentMonth</h4>";
// Legend for watering and fertilizing
echo '<div class="legend">';
echo '<div><i class="fa-solid fa-droplet" style="color: blue;"></i> Watered</div>';
echo '<div><i class="fa-solid fa-seedling" style="color: green;"></i> Fertilized</div>';
echo '</div>'; // Close legend div
echo '<div class="calendar">';
// Get the current month and year
$currentMonthNumber = date('m'); // Month number
$currentYear = date('Y'); // Current year
$firstDayOfMonth = new DateTime("$currentYear-$currentMonthNumber-01");
$lastDayOfMonth = new DateTime($firstDayOfMonth->format('Y-m-t'));
// Display blank days before the first of the month
for ($i = 0; $i < $firstDayOfMonth->format('w'); $i++) {
echo '<div class="day"></div>';
}
// Display each day of the month
for ($day = 1; $day <= $lastDayOfMonth->format('d'); $day++) {
$currentDate = "$currentYear-$currentMonthNumber-" . str_pad($day, 2, '0', STR_PAD_LEFT);
echo '<div class="day">';
echo "<strong>$day</strong>";
// Prepare icons container
echo '<div class="icons">';
// Mark dots for all watering events
if (in_array($currentDate, $events['watered'])) {
echo '<i class="fa-solid fa-droplet icon" style="color: blue;"></i>';
}
// Mark dots for all fertilizing events
if (in_array($currentDate, $events['fertilised'])) {
echo '<i class="fa-solid fa-seedling icon" style="color: green;"></i>';
}
echo '</div>'; // Close icons div
echo '</div>'; // Close day div
}
echo '</div>'; // Close calendar div
// Display table for watered and fertilized dates
echo '<table class="table table-bordered mt-3">
<thead>
<tr>
<th>Date</th>
<th>Event</th>
</tr>
</thead>
<tbody>';
// Display watered dates
foreach ($events['watered'] as $date) {
echo "<tr>
<td>$date</td>
<td>Watered</td>
</tr>";
}
// Display fertilized dates
foreach ($events['fertilised'] as $date) {
echo "<tr>
<td>$date</td>
<td>Fertilized</td>
</tr>";
}
echo ' </tbody>
</table>'; // Close table div
echo '</div>'; // Close plant calendar div
}
// Close Bootstrap container and HTML tags
echo '</div></body></html>';
// Close database connection
$mysqli->close();
?>Switch between cards and list: https://github.com/danielbrendel/hortusfox-web/commit/0f1e4cabb5c02b24a660d8cc2a412e9fe97f54a2
Description of your request Tables in the Location Section & Graphs
Describe the solution you'd like