Advertisement
SuperBoUtd

Untitled

Oct 26th, 2020
2,385
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 2.18 KB | None | 0 0
  1. /*get require document*/
  2. WITH require_document AS
  3. (SELECT
  4.         DISTINCT vehicle_type.vehicle_type,
  5.         vehicle_type.region_name as vehicle_name,
  6.         require_document.document_id,
  7.         require_document.document_name
  8.       FROM
  9.         be_autos.tb_city_sub_regions vehicle_type
  10.       JOIN
  11.         be_autos.tb_required_documents require_document
  12.         ON
  13.         CASE WHEN vehicle_type.vehicle_type = 9 THEN require_document.document_id IN (4,6,7,8,36,61,63)
  14.              WHEN vehicle_type.vehicle_type = 3 THEN require_document.document_id IN (3,5,6,7,36,43,45,55,63)
  15.              WHEN vehicle_type.vehicle_type = 6 THEN require_document.document_id IN (5,6,7,36,43,45,52,55,63)
  16.         END
  17.       WHERE
  18.         vehicle_type.vehicle_type in (3,6,9) AND
  19.         require_document.document_id IN (5) AND
  20.         vehicle_type.operator_id = 1450 AND
  21.         vehicle_type.region_name NOT LIKE '%TAXI%' AND
  22.         vehicle_type.city_id = 189
  23.       ORDER BY
  24.         1,2 ASC),
  25. document AS
  26. (SELECT
  27.             document.driver_id,
  28.             document.document_id,
  29.             document.status,
  30.             document.reason
  31.       FROM
  32.         be_autos.tb_driver_documents AS document
  33.       WHERE
  34.         document.document_id IN (5))
  35. SELECT
  36.       driver.driver_id,
  37.       driver.name,
  38.       driver.city_id,
  39.       driver.ops_city,
  40.       driver.vehicle_type,
  41.       driver.driver_suspended,
  42.       driver.last_ride_on,
  43.       require_document.document_id,
  44.       require_document.document_name,
  45.       CASE WHEN IFNULL(document.status,0) = 0 THEN 'Not_uploaded'
  46.            WHEN IFNULL(document.status,0) = 1 THEN 'Pending'
  47.            WHEN IFNULL(document.status,0) = 2 THEN 'Rejected'
  48.            WHEN IFNULL(document.status,0) = 3 THEN 'Approved'
  49.            WHEN IFNULL(document.status,0) = 4 THEN 'Uploaded' END AS document_status,
  50.       document.reason
  51. FROM
  52.   be_autos.tb_drivers AS driver
  53. LEFT JOIN require_document
  54.   ON driver.vehicle_type = require_document.vehicle_type
  55. LEFT JOIN document
  56.   ON driver.driver_id = document.driver_id
  57.   AND require_document.document_id = document.document_id
  58. WHERE
  59.   driver.verification_status = 1
  60. AND driver.driver_id IN (
  61.  
  62. )
  63. ORDER BY 1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement