tripz-llc/backend/serviceapp/getComplaintAllDataForDriver.php

<?php
require_once __DIR__ . '/../connect.php';
$driverID = filterRequest("driver_id");

$sql = "SELECT
    cm.`id`,
    cm.`ride_id`,
    cm.`passenger_id`,
    cm.`driver_id`,
    cm.`complaint_type`,
    cm.`description`,
    cm.`date_filed`,
    cm.`statusComplaint`,
    cm.`resolution`,
    cm.`date_resolved`,
    p.first_name AS passengerName,
    d.name_arabic AS driverName,
    d.gender,
    ride.price AS priceOfRide,
    ride.status AS rideStatus,
    ride.carType AS carType,
    ride.paymentMethod AS ridePaymentMethod,
    ride.rideTimeFinish AS rideTimeFinish,
    0 AS paymentFromPaymentTable,
    NULL AS timeFromPaymentTable,
    (
    SELECT
        AVG(rd.rating)
    FROM
        ratingDriver rd
    WHERE
        rd.driver_id = cm.driver_id
) AS avgRatingDriverFromPassengers,
(
    SELECT
        COUNT(*)
    FROM
        ratingDriver rd
    WHERE
        rd.driver_id = cm.driver_id
) AS countratingDriverFromPassengers,
(
    SELECT
        AVG(rp.rating)
    FROM
        ratingPassenger rp
    WHERE
        rp.passenger_id = cm.passenger_id
) AS avgRatingPassengerFromDrivers,
(
    SELECT
        COUNT(*)
    FROM
        ratingPassenger rp
    WHERE
        rp.passenger_id = cm.passenger_id
) AS countRatingPassengerFromDrivers,
(
    SELECT
        COUNT(*)
    FROM
        ride
    WHERE
        ride.driver_id = cm.driver_id
) countDriverRide,
(
    SELECT
        COUNT(*)
    FROM
        ride
    WHERE
        ride.passenger_id = cm.passenger_id
) countPassengerRide,
0 AS driverVisa,
0 AS driverWallet,
0 AS passengerWallet,
(
    SELECT
        token
    FROM
        driverToken
    WHERE
        driverToken.captain_id = cm.driver_id
) AS driverToken,
(
    SELECT
        token
    FROM
        tokens
    WHERE
        tokens.passengerID = cm.passenger_id
) AS passengerToken,
(
    SELECT
        COUNT(*)
    FROM
        complaint
    WHERE
        complaint.passenger_id = cm.passenger_id AND cm.complaint_type = 'Passenger'
) AS countOfComplaintFromPassenger,
(
    SELECT
        COUNT(*)
    FROM
        complaint
    WHERE
        complaint.driver_id = cm.driver_id AND cm.complaint_type = 'Driver'
) AS countOfComplaintFromDriver
FROM
    `complaint` cm
LEFT JOIN passengers p ON
    p.id = cm.`passenger_id`
LEFT JOIN driver d ON
    d.id = cm.driver_id
LEFT JOIN ride ON ride.id = cm.ride_id
WHERE
    cm.driver_id = '$driverID'";
$stmt = $con->prepare($sql);
$stmt->execute();

if ($stmt->rowCount() > 0) {
    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($row as &$item) {
        if (isset($item['passengerName'])) {
            $item['passengerName'] = $encryptionHelper->decryptData($item['passengerName']);
        }
        if (isset($item['driverName'])) {
            $item['driverName'] = $encryptionHelper->decryptData($item['driverName']);
        }
        if (isset($item['gender'])) {
            $item['gender'] = $encryptionHelper->decryptData($item['gender']);
        }
        if (isset($item['driverToken'])) {
            $item['driverToken'] = $encryptionHelper->decryptData($item['driverToken']);
        }
        if (isset($item['passengerToken'])) {
            $item['passengerToken'] = $encryptionHelper->decryptData($item['passengerToken']);
        }
    }

    jsonSuccess($row);
} else {
    jsonError($message = $sql);
}
?>