Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*get require document*/
- WITH require_document AS
- (SELECT
- DISTINCT vehicle_type.vehicle_type,
- vehicle_type.region_name as vehicle_name,
- require_document.document_id,
- require_document.document_name
- FROM
- be_autos.tb_city_sub_regions vehicle_type
- JOIN
- be_autos.tb_required_documents require_document
- ON
- CASE WHEN vehicle_type.vehicle_type = 9 THEN require_document.document_id IN (4,6,7,8,36,61,63)
- WHEN vehicle_type.vehicle_type = 3 THEN require_document.document_id IN (3,5,6,7,36,43,45,55,63)
- WHEN vehicle_type.vehicle_type = 6 THEN require_document.document_id IN (5,6,7,36,43,45,52,55,63)
- END
- WHERE
- vehicle_type.vehicle_type in (3,6,9) AND
- require_document.document_id IN (5) AND
- vehicle_type.operator_id = 1450 AND
- vehicle_type.region_name NOT LIKE '%TAXI%' AND
- vehicle_type.city_id = 189
- ORDER BY
- 1,2 ASC),
- document AS
- (SELECT
- document.driver_id,
- document.document_id,
- document.status,
- document.reason
- FROM
- be_autos.tb_driver_documents AS document
- WHERE
- document.document_id IN (5))
- SELECT
- driver.driver_id,
- driver.name,
- driver.city_id,
- driver.ops_city,
- driver.vehicle_type,
- driver.driver_suspended,
- driver.last_ride_on,
- require_document.document_id,
- require_document.document_name,
- CASE WHEN IFNULL(document.status,0) = 0 THEN 'Not_uploaded'
- WHEN IFNULL(document.status,0) = 1 THEN 'Pending'
- WHEN IFNULL(document.status,0) = 2 THEN 'Rejected'
- WHEN IFNULL(document.status,0) = 3 THEN 'Approved'
- WHEN IFNULL(document.status,0) = 4 THEN 'Uploaded' END AS document_status,
- document.reason
- FROM
- be_autos.tb_drivers AS driver
- LEFT JOIN require_document
- ON driver.vehicle_type = require_document.vehicle_type
- LEFT JOIN document
- ON driver.driver_id = document.driver_id
- AND require_document.document_id = document.document_id
- WHERE
- driver.verification_status = 1
- AND driver.driver_id IN (
- )
- ORDER BY 1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement