154 lines
4.3 KiB
SQL
Executable File
154 lines
4.3 KiB
SQL
Executable File
-- Retrieving data for a specific passenger complaint:
|
|
SELECT
|
|
c.id AS complaint_id,
|
|
r.id AS ride_id,
|
|
p.id AS passenger_id,
|
|
p.first_name,
|
|
p.last_name,
|
|
p.phone,
|
|
p.email,
|
|
c.complaint_type,
|
|
c.description,
|
|
c.date_filed,
|
|
c.statusComplaint AS complaint_status,
|
|
c.resolution,
|
|
c.date_resolved
|
|
FROM
|
|
complaint c
|
|
JOIN ride r ON
|
|
c.ride_id = r.id
|
|
JOIN `passengers` p ON
|
|
c.passenger_id = p.id
|
|
WHERE
|
|
c.complaint_type = 'Passenger' AND c.passenger_id = '100393163265770158312';
|
|
|
|
-- Admin panel dashboard
|
|
SELECT
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`email`)
|
|
FROM
|
|
`passengers`
|
|
), 0) AS countPassengers,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`email`)
|
|
FROM
|
|
`driver`
|
|
), 0) AS countDriver,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`ride`
|
|
), 0) AS countRide,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`passengers`
|
|
WHERE
|
|
`passengers`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
), 0) AS countPassengersThisMonth,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`ride`
|
|
WHERE
|
|
`ride`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
), 0) AS countRideThisMonth,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`driver`
|
|
WHERE
|
|
`driver`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
), 0) AS countDriverThisMonth,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`CarRegistration`
|
|
WHERE
|
|
`CarRegistration`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
), 0) AS countCarRegistrationThisMonth,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`complaint`
|
|
WHERE
|
|
`complaint`.`date_filed` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
), 0) AS countComplaintThisMonth,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`complaint`
|
|
WHERE
|
|
`complaint`.`date_filed` BETWEEN DATE_FORMAT(
|
|
DATE_SUB(
|
|
CURRENT_DATE,
|
|
INTERVAL WEEKDAY(CURRENT_DATE) DAY
|
|
),
|
|
'%Y-%m-%d'
|
|
) AND DATE_FORMAT(
|
|
DATE_ADD(
|
|
DATE_SUB(
|
|
CURRENT_DATE,
|
|
INTERVAL WEEKDAY(CURRENT_DATE) DAY
|
|
),
|
|
INTERVAL 6 DAY
|
|
),
|
|
'%Y-%m-%d'
|
|
)
|
|
), 0) AS countComplaintThisWeek,
|
|
COALESCE((
|
|
SELECT
|
|
COUNT(`id`)
|
|
FROM
|
|
`complaint`
|
|
WHERE
|
|
`complaint`.`date_filed` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d') AND DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d')
|
|
), 0) AS countComplaintToday,
|
|
COALESCE((
|
|
SELECT
|
|
SUM(`payments`.`amount`)
|
|
FROM
|
|
`payments`
|
|
WHERE
|
|
`payments`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
AND
|
|
`payments`.`payment_method` IN('visa-in', 'visa', 'visaRide', 'TransferFrom', 'payout', 'TransferTo')
|
|
), 0) AS payments,
|
|
COALESCE((
|
|
SELECT
|
|
SUM(`driverWallet`.`amount`)
|
|
FROM
|
|
`driverWallet`
|
|
WHERE
|
|
`driverWallet`.`dateCreated` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
AND
|
|
`driverWallet`.`paymentMethod` IN('visa', '')
|
|
), 0) AS driverWallet,
|
|
COALESCE((
|
|
SELECT
|
|
SUM(`passengerWallet`.`balance`)
|
|
FROM
|
|
`passengerWallet`
|
|
WHERE
|
|
`passengerWallet`.`created_at` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
), 0) AS passengerWallet,
|
|
COALESCE((
|
|
SELECT
|
|
SUM(`seferWallet`.`amount`)
|
|
FROM
|
|
`seferWallet`
|
|
WHERE
|
|
`seferWallet`.`createdAt` BETWEEN DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND LAST_DAY(CURRENT_DATE)
|
|
), 0) AS seferWallet
|
|
FROM
|
|
`passengers`
|
|
LIMIT 1; |