test1.sql

SELECT
  booking_campaign.cv_id,
  booking_campaign.is_completed,
  booking_campaign.fullname,
  booking_campaign.candidate_status,
  question_point.score_percen,
  candidate_extra_service.iq_tests,
  candidate_extra_service.iq_tests_service_cost,
  candidate_extra_service.reference_checks,
  candidate_extra_service.reference_checks_service_cost,
  candidate_extra_service.police_checks,
  candidate_extra_service.police_checks_service_cost,
  candidate_extra_service.physcometric_tests,
  candidate_extra_service.physcometric_tests_service_cost,
  candidate_extra_service.phone_interviews,
  candidate_extra_service.phone_interviews_service_cost,
  candidate_extra_service.advanced_skills_tests,
  candidate_extra_service.advanced_skills_tests_service_cost,
  website_psychometric_counted.psychometric_id_counted
FROM (SELECT DISTINCT
        bk.booking_id,
        wcv.cv_id,
        wcv.is_completed,
        wcv.status                                                  AS candidate_status,
        wr.firstname,
        wr.surname,
        wr.display_as                                               AS fullname
      FROM booking_website_cv AS bwcv
      INNER JOIN booking AS bk
          ON bk.booking_id = bwcv.booking_id
      INNER JOIN website_cv AS wcv
          ON bwcv.cv_id = wcv.cv_id
      INNER JOIN website_registers AS wr
          ON wr.website_register_id = wcv.website_register_id
      WHERE bk.booking_id = '1300005496') booking_campaign
      LEFT JOIN (
               SELECT
	         wcv.cv_id,
                 COALESCE(total_score_qsn,0)                                 AS total_score_qsn,
                 COALESCE(total_of_question_point_qsn,0)                     AS total_of_question_point_qsn,
                 COALESCE(total_score_service,0)                             AS total_score_service,
                 COALESCE(total_of_question_point_service,0)                 AS total_of_question_point_service,
                 COALESCE(ROUND(((COALESCE(total_score_qsn,0)+ COALESCE(total_score_service,0))/(COALESCE(total_of_question_point_qsn,0)+ COALESCE(total_of_question_point_service,0)))*100),0) AS score_percen
               FROM website_cv wcv
               LEFT JOIN (SELECT
                            qry_total_score_qsn.cv_id,
                            qry_total_score_qsn.total_score_qsn,
                            qry_total_of_question_point_qsn.total_of_question_point_qsn
                          FROM (SELECT
                                  wcv.cv_id,
                                  COALESCE(SUM(wcva.total_point),0)                             AS total_score_qsn,
                                  booking_questionnaire.q_id
                                FROM website_cv AS wcv
                                  LEFT JOIN website_cv_answer AS wcva
                                    ON wcv.cv_id = wcva.cv_id
                                  INNER JOIN booking_question AS bq
                                    ON bq.question_id = wcva.question_id
                                  INNER JOIN booking_questionnaire
                                    ON bq.q_id = booking_questionnaire.q_id
                                WHERE bq.question_has_point = 'Y'
                                    AND booking_questionnaire.service_id IS NULL
                                GROUP BY wcv.cv_id) qry_total_score_qsn
                            INNER JOIN (SELECT
                                          bqn.q_id,
                                          COALESCE(SUM(bq.question_total_of_point),0)                   AS total_of_question_point_qsn
                                        FROM booking_questionnaire AS bqn
                                          INNER JOIN booking_question AS bq
                                            ON bqn.q_id = bq.q_id
                                        WHERE bq.question_has_point = 'Y'
                                            AND bqn.service_id IS NULL
                                        GROUP BY bqn.q_id) qry_total_of_question_point_qsn
                              ON qry_total_score_qsn.q_id = qry_total_of_question_point_qsn.q_id) wcv_total_point_qsn
                 ON wcv.cv_id = wcv_total_point_qsn.cv_id
               LEFT JOIN (SELECT
                            qry_total_score_service.cv_id,
                            qry_total_score_service.total_score_service,
                            qry_total_of_question_point_service.total_of_question_point_service
                          FROM (SELECT
                                  website_cv.cv_id,
                                  COALESCE(SUM(website_cv_answer.total_point),0)                       AS total_score_service
                                FROM booking_extra_service
                                  INNER JOIN booking_questionnaire
                                    ON booking_extra_service.q_id = booking_questionnaire.q_id
                                  INNER JOIN booking_question
                                    ON booking_questionnaire.q_id = booking_question.q_id
                                  INNER JOIN website_cv
                                    ON website_cv.cv_id = booking_extra_service.cv_id
                                  INNER JOIN website_cv_answer
                                    ON website_cv.cv_id = website_cv_answer.cv_id
                                      AND website_cv_answer.question_id = booking_question.question_id
                                WHERE booking_extra_service.display_on = 'C'
                                    AND booking_extra_service.status = 'C'
                                    AND booking_question.question_has_point = 'Y'
                                    AND booking_question.q_answer_type <> 'dr'
                                GROUP BY website_cv.cv_id) qry_total_score_service
                            LEFT JOIN (SELECT
                                         website_cv.cv_id,
                                         COALESCE(SUM(booking_question.question_total_of_point),0)            AS total_of_question_point_service
                                       FROM booking_extra_service
                                         INNER JOIN booking_questionnaire
                                           ON booking_extra_service.q_id = booking_questionnaire.q_id
                                         INNER JOIN booking_question
                                           ON booking_questionnaire.q_id = booking_question.q_id
                                         INNER JOIN website_cv
                                           ON website_cv.cv_id = booking_extra_service.cv_id
                                       WHERE booking_extra_service.display_on = 'C'
                                           AND booking_extra_service.status = 'C'
                                           AND booking_question.question_has_point = 'Y'
                                           AND booking_question.q_answer_type <> 'dr'
                                       GROUP BY website_cv.cv_id) qry_total_of_question_point_service
                              ON qry_total_score_service.cv_id = qry_total_of_question_point_service.cv_id) wcv_total_point_service
                 ON wcv_total_point_qsn.cv_id = wcv_total_point_service.cv_id) question_point
    ON booking_campaign.cv_id = question_point.cv_id
  LEFT JOIN (SELECT DISTINCT
               candidate_cv.cv_id,
               select_iq_tests.iq_tests,
               select_iq_tests.iq_tests_service_cost,
               select_reference_checks.reference_checks,
               select_reference_checks.reference_checks_service_cost,
               select_police_checks.police_checks,
               select_police_checks.police_checks_service_cost,
               select_physcometric_tests.physcometric_tests,
               select_physcometric_tests.physcometric_tests_service_cost,
               select_phone_interviews.phone_interviews,
               select_phone_interviews.phone_interviews_service_cost,
               select_advanced_skills_tests.advanced_skills_tests,
               select_advanced_skills_tests.advanced_skills_tests_service_cost
             FROM (SELECT
                     booking_extra_service.cv_id
                   FROM booking_extra_service
                     INNER JOIN extra_service
                       ON booking_extra_service.extra_service_id = extra_service.extra_service_id
                     INNER JOIN business
                       ON extra_service.service_id = business.bn_id
                   WHERE business.bn_name IN('IQ Tests', 'Reference Checks', 'Police Checks', 'Physcometric Tests', 'Phone Interviews','Advanced Skills Tests')) candidate_cv
               LEFT JOIN (SELECT
                            booking_extra_service.cv_id,
                            booking_extra_service.status                                     AS iq_tests,
                            booking_extra_service.service_cost                               AS iq_tests_service_cost
                          FROM booking_extra_service
                            LEFT JOIN extra_service
                              ON booking_extra_service.extra_service_id = extra_service.extra_service_id
                            LEFT JOIN business
                              ON extra_service.service_id = business.bn_id
                          WHERE business.bn_name = 'IQ Tests') select_iq_tests
                 ON candidate_cv.cv_id = select_iq_tests.cv_id
               LEFT JOIN (SELECT
                            booking_extra_service.cv_id,
                            booking_extra_service.status                                     AS reference_checks,
                            booking_extra_service.service_cost                               AS reference_checks_service_cost
                          FROM booking_extra_service
                            LEFT JOIN extra_service
                              ON booking_extra_service.extra_service_id = extra_service.extra_service_id
                            LEFT JOIN business
                              ON extra_service.service_id = business.bn_id
                          WHERE business.bn_name = 'Reference Checks') select_reference_checks
                 ON candidate_cv.cv_id = select_reference_checks.cv_id
               LEFT JOIN (SELECT
                            booking_extra_service.cv_id,
                            booking_extra_service.status                                     AS police_checks,
                            booking_extra_service.service_cost                               AS police_checks_service_cost
                          FROM booking_extra_service
                            LEFT JOIN extra_service
                              ON booking_extra_service.extra_service_id = extra_service.extra_service_id
                            LEFT JOIN business
                              ON extra_service.service_id = business.bn_id
                          WHERE business.bn_name = 'Police Checks') select_police_checks
                 ON candidate_cv.cv_id = select_police_checks.cv_id
               LEFT JOIN (SELECT
                            booking_extra_service.cv_id,
                            booking_extra_service.status                                     AS physcometric_tests,
                            booking_extra_service.service_cost                               AS physcometric_tests_service_cost
                          FROM booking_extra_service
                            LEFT JOIN extra_service
                              ON booking_extra_service.extra_service_id = extra_service.extra_service_id
                            LEFT JOIN business
                              ON extra_service.service_id = business.bn_id
                          WHERE business.bn_name = 'Physcometric Tests') select_physcometric_tests
                 ON candidate_cv.cv_id = select_physcometric_tests.cv_id
               LEFT JOIN (SELECT
                            booking_extra_service.cv_id,
                            booking_extra_service.status                                     AS phone_interviews,
                            booking_extra_service.service_cost                               AS phone_interviews_service_cost
                          FROM booking_extra_service
                            LEFT JOIN extra_service
                              ON booking_extra_service.extra_service_id = extra_service.extra_service_id
                            LEFT JOIN business
                              ON extra_service.service_id = business.bn_id
                          WHERE business.bn_name = 'Phone Interviews') select_phone_interviews
                 ON candidate_cv.cv_id = select_phone_interviews.cv_id
               LEFT JOIN (SELECT
                            booking_extra_service.cv_id,
                            booking_extra_service.status                                     AS advanced_skills_tests,
                            booking_extra_service.service_cost                               AS advanced_skills_tests_service_cost
                          FROM booking_extra_service
                            LEFT JOIN extra_service
                              ON booking_extra_service.extra_service_id = extra_service.extra_service_id
                            LEFT JOIN business
                              ON extra_service.service_id = business.bn_id
                          WHERE business.bn_name = 'Advanced Skills Tests') select_advanced_skills_tests
                 ON candidate_cv.cv_id = select_advanced_skills_tests.cv_id
             WHERE candidate_cv.cv_id IS NOT NULL) candidate_extra_service
    ON booking_campaign.cv_id = candidate_extra_service.cv_id
  LEFT JOIN (SELECT
               COUNT(wb_p.psychometric_id)                                 AS psychometric_id_counted,
               wb_cv.cv_id
             FROM website_psychometric wb_p
               INNER JOIN website_cv wb_cv
                 ON wb_cv.web_id = wb_p.website_id
             GROUP BY wb_cv.cv_id) website_psychometric_counted
    ON booking_campaign.cv_id = website_psychometric_counted.cv_id
WHERE 1 = 1
ORDER BY question_point.score_percen DESC,booking_campaign.fullname
LIMIT 30 OFFSET 0