Database
CREATE TABLE `tracking` (
`srno` int(11) NOT NULL,
`day` varchar(20) DEFAULT NULL,
`time` decimal(10,0) DEFAULT NULL,
`min` decimal(10,0) DEFAULT NULL,
`max` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
--
-- Dumping data for table `tracking`
--
INSERT INTO `tracking` (`srno`, `day`, `time`, `min`, `max`) VALUES
(1, 'mon', 1, 50, 150),
(2, 'mon', 2, 100, 150),
(3, 'mon', 3, 150, 250),
(4, 'mon', 4, 250, 350),
(5, 'mon', 5, 350, 450),
(6, 'mon', 6, 450, 550),
(7, 'mon', 7, 550, 650),
(8, 'mon', 8, 650, 750),
(9, 'mon', 9, 650, 750),
(10, 'mon', 10, 650, 750),
(11, 'mon', 11, 650, 750),
(12, 'mon', 12, 650, 750),
(13, 'mon', 13, 650, 750),
(14, 'mon', 14, 650, 750),
(15, 'mon', 15, 650, 750),
(16, 'mon', 16, 650, 750),
(17, 'mon', 17, 650, 750),
(18, 'mon', 18, 650, 750),
(19, 'mon', 19, 650, 750),
(20, 'mon', 20, 650, 750),
(21, 'mon', 21, 650, 750),
(22, 'mon', 22, 650, 750),
(23, 'mon', 23, 650, 750),
(24, 'thu', 1, 50, 150),
(25, 'thu', 2, 100, 150),
(26, 'thu', 3, 150, 250),
(27, 'thu', 4, 250, 350),
(28, 'thu', 5, 350, 450),
(29, 'thu', 6, 450, 550),
(30, 'thu', 7, 550, 650),
(31, 'thu', 8, 650, 750),
(32, 'thu', 9, 650, 750),
(33, 'thu', 10, 650, 750),
(34, 'thu', 11, 650, 750),
(35, 'thu', 12, 650, 750),
(36, 'thu', 13, 650, 750),
(37, 'thu', 14, 650, 750),
(38, 'thu', 15, 650, 750),
(39, 'thu', 16, 650, 750),
(40, 'thu', 17, 650, 750),
(41, 'thu', 18, 650, 750),
(42, 'thu', 19, 650, 750),
(43, 'thu', 20, 650, 750),
(44, 'thu', 21, 650, 750),
(45, 'thu', 22, 650, 750),
(46, 'thu', 23, 1700, 1950),
(47, 'mon', 0, 40, 70),
(94, 'fri', 1, 50, 150),
(95, 'fri', 2, 100, 150),
(96, 'fri', 3, 150, 250),
(97, 'fri', 4, 250, 350),
(98, 'fri', 5, 350, 450),
(99, 'fri', 6, 450, 550),
(100, 'fri', 7, 550, 650),
(101, 'fri', 8, 650, 750),
(102, 'fri', 9, 650, 750),
(103, 'fri', 10, 650, 750),
(104, 'fri', 11, 650, 750),
(105, 'fri', 12, 650, 750),
(106, 'fri', 13, 650, 750),
(107, 'fri', 14, 650, 750),
(108, 'fri', 15, 650, 750),
(109, 'fri', 16, 650, 750),
(110, 'fri', 17, 650, 750),
(111, 'fri', 18, 650, 750),
(112, 'fri', 19, 650, 750),
(113, 'fri', 20, 650, 750),
(114, 'fri', 21, 650, 750),
(115, 'fri', 22, 650, 750),
(116, 'fri', 23, 650, 750);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tracking`
--
ALTER TABLE `tracking`
ADD PRIMARY KEY (`srno`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tracking`
--
ALTER TABLE `tracking`
MODIFY `srno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=117;
COMMIT;
<?php
/**
* Get min/max for given day & hour from `tracking`, compute minute bucket and final row count.
*
* @param array $dbConfig ['host' => '', 'user' => '', 'pass' => '', 'name' => '']
* @param string $table Table name (e.g., 'tracking')
* @param string $timezone PHP timezone (e.g., 'Asia/Kolkata')
* @param string $day Day key used in DB (e.g., 'mon', 'tue', 'wed', 'thu', ...)
* @param int $hour Hour 0–23 (24h). If null, derived from system.
* @param int $minute Minute 0–59. If null, derived from system.
* @param array $bucketOffsets Offsets to add to min for buckets [15, 30, 45, 60] => [9, 27, 37, 57]
* @param int $actualRowsLimit Cap for final row count (e.g., 50)
*
* @return array {
* success: bool,
* messages: string[],
* day: string,
* hour: int,
* minute: int,
* bucketLabel: string|null,
* min: int|null,
* max: int|null,
* finalRowCount: int|null
* }
*/
// Configuration
$dbConfig = [
'host' => 'localhost',
'user' => 'root',
'pass' => '',
'name' => 'test',
];
function getTrackingInfo(
array $dbConfig,
string $table = 'tracking',
string $timezone = 'Asia/Kolkata',
string $day = null,
?int $hour = null,
?int $minute = null,
array $bucketOffsets = [9, 27, 37, 57],
int $actualRowsLimit = 50
): array {
$out = [
'success' => false,
'messages' => [],
'day' => $day,
'hour' => null,
'minute' => null,
'bucketLabel' => null,
'min' => null,
'max' => null,
'finalRowCount' => null,
];
// 1) Time setup
if ($timezone) {
if (!@date_default_timezone_set($timezone)) {
$out['messages'][] = "Warning: Failed to set timezone '{$timezone}'. Using default.";
}
}
$resolvedDay = $day ?: strtolower(date('D')); // 'mon', 'tue', ...
$resolvedHour = isset($hour) ? (int)$hour : (int)date('G'); // 0–23
$resolvedMinute = isset($minute) ? (int)$minute : (int)date('i'); // 0–59
$labels = ['15min', '30min', '45min', '60min'];
if (count($bucketOffsets) !== 4) {
$out['messages'][] = 'Error: bucketOffsets must have 4 entries for 15/30/45/60 minute buckets.';
return $out;
}
if ($resolvedMinute <= 15) { $bucketIndex = 0; }
elseif ($resolvedMinute <= 30) { $bucketIndex = 1; }
elseif ($resolvedMinute <= 45) { $bucketIndex = 2; }
else { $bucketIndex = 3; }
$out['bucketLabel'] = $labels[$bucketIndex];
// 3) DB connection
$mysqli = @new mysqli($dbConfig['host'], $dbConfig['user'], $dbConfig['pass'], $dbConfig['name']);
if ($mysqli->connect_error) {
$out['messages'][] = 'DB Connect Error (' . $mysqli->connect_errno . '): ' . $mysqli->connect_error;
return $out;
}
// 4) Query min/max for day & hour
$sql = "SELECT `min`, `max` FROM `{$table}` WHERE `day` = ? AND `time` = ? LIMIT 1";
$stmt = $mysqli->prepare($sql);
if (!$stmt) {
$out['messages'][] = 'Prepare failed: ' . $mysqli->error;
$mysqli->close();
return $out;
}
$stmt->bind_param('si', $resolvedDay, $resolvedHour);
$stmt->execute();
$res = $stmt->get_result();
if ($row = $res->fetch_assoc()) {
$minVal = isset($row['min']) ? (int)$row['min'] : null;
$maxVal = isset($row['max']) ? (int)$row['max'] : null;
$out['min'] = $minVal;
$out['max'] = $maxVal;
if ($minVal !== null) {
$offset = (int)$bucketOffsets[$bucketIndex];
$final = $minVal + $offset;
// Cap by actualRowsLimit
if ($final > $actualRowsLimit) {
$out['messages'][] = "Info: finalRowCount capped from {$actualRowsLimit} to {$final} .";
$final = $actualRowsLimit;
}
$out['finalRowCount'] = $final;
$out['success'] = true;
$out['messages'][] = "OK: day={$resolvedDay}, hour={$resolvedHour}, minute={$resolvedMinute}, bucket={$labels[$bucketIndex]}, min={$minVal}, max={$maxVal}, finalRowCount={$final}.";
} else {
$out['messages'][] = "Error: Row found but 'min' was NULL.";
}
} else {
$out['messages'][] = "No data found for day={$resolvedDay}, hour={$resolvedHour}.";
}
$stmt->close();
$mysqli->close();
return $out;
}
// Call using current system time
$infoCurrent = getTrackingInfo(
dbConfig: $dbConfig,
table: 'tracking',
timezone: 'Asia/Kolkata',
day: null, // derive from system ('mon', 'tue', ...)
hour: null, // derive current hour
minute: null, // derive current minute
bucketOffsets: [9, 27, 37, 57], // your offsets
actualRowsLimit: 50 // cap
);
// Helper to print nicely
function printInfoHtml(array $info, string $title): void {
echo "<h3 style='font-family: Segoe UI, Arial; color:#333;'>{$title}</h3>";
echo "<pre style='background:#f7f7f7; padding:12px; border:1px solid #ddd; border-radius:6px;'>";
echo "Success : " . ($info['success'] ? 'Yes' : 'No') . PHP_EOL;
echo "Messages : " . implode(' | ', $info['messages']) . PHP_EOL;
echo "Day/Time : {$info['day']} {$info['hour']}:{$info['minute']} ({$info['bucketLabel']})" . PHP_EOL;
echo "Row min/max : " . ($info['min'] ?? 'NULL') . " / " . ($info['max'] ?? 'NULL') . PHP_EOL;
echo "FinalRowCount : " . ($info['finalRowCount'] ?? 'NULL') . PHP_EOL;
echo "</pre>";
}
// Render page
echo "<!doctype html><html><head><meta charset='utf-8'><title>Tracking Output</title></head><body style='margin:28px; font-family: Segoe UI, Arial;'>";
printInfoHtml($infoCurrent, 'Call 1: Current Time');
echo "<hr/>";
//printInfoHtml($infoTest, 'Call 2: Explicit 1 AM, Minute=7');
echo "</body></html>";
No comments:
Post a Comment
Thank you for your Comment....