79 lines
1.6 KiB
PHP
79 lines
1.6 KiB
PHP
<?php
|
|
require_once __DIR__ . '/../../connect.php';
|
|
|
|
$sql = "SELECT
|
|
(
|
|
SELECT TIME_FORMAT(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, rideTimeStart, rideTimeFinish))), '%Hh %im')
|
|
FROM ride
|
|
WHERE rideTimeStart IS NOT NULL AND rideTimeFinish IS NOT NULL
|
|
) AS driver_avg_duration,
|
|
|
|
(
|
|
SELECT COUNT(*) FROM (
|
|
SELECT COUNT(driver_id) FROM ride GROUP BY driver_id
|
|
) AS sub
|
|
) AS num_Driver,
|
|
|
|
(
|
|
SELECT COUNT(*) FROM ride
|
|
) AS total_rides,
|
|
|
|
(
|
|
SELECT COUNT(*) FROM ride WHERE status = 'waiting'
|
|
) AS ongoing_rides,
|
|
|
|
(
|
|
SELECT COUNT(*) FROM ride WHERE status = 'Finished'
|
|
) AS completed_rides,
|
|
|
|
(
|
|
SELECT COUNT(*) FROM ride WHERE status = 'cancelled'
|
|
) AS cancelled_rides,
|
|
|
|
(
|
|
SELECT TIME_FORMAT(SEC_TO_TIME(MAX(TIMESTAMPDIFF(SECOND, rideTimeStart, rideTimeFinish))), '%Hh %im')
|
|
FROM ride
|
|
WHERE rideTimeStart IS NOT NULL AND rideTimeFinish IS NOT NULL
|
|
) AS longest_duration,
|
|
|
|
(
|
|
SELECT ROUND(SUM(distance), 2) FROM ride
|
|
) AS total_distance,
|
|
|
|
(
|
|
SELECT ROUND(AVG(distance), 2) FROM ride
|
|
) AS average_distance,
|
|
|
|
(
|
|
SELECT ROUND(MAX(distance), 2) FROM ride
|
|
) AS longest_distance,
|
|
|
|
(
|
|
SELECT ROUND(SUM(price_for_driver), 2) FROM ride
|
|
) AS total_driver_earnings,
|
|
|
|
(
|
|
SELECT ROUND(SUM(price_for_passenger), 2) FROM ride
|
|
) AS total_company_earnings,
|
|
|
|
(
|
|
SELECT ROUND(
|
|
(SELECT SUM(price_for_passenger) FROM ride) /
|
|
NULLIF((SELECT SUM(price_for_driver) FROM ride), 0),
|
|
2
|
|
)
|
|
) AS companyPercent
|
|
|
|
FROM dual
|
|
LIMIT 1";
|
|
|
|
$stmt = $con->prepare($sql);
|
|
$stmt->execute();
|
|
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if ($stmt->rowCount() > 0) {
|
|
jsonSuccess($result);
|
|
} else {
|
|
jsonError("No records found");
|
|
}
|
|
?>
|