candidate-detail

mail@pastecode.io avatar
unknown
pgsql
2 years ago
11 kB
2
Indexable
Never
   select
            candidate.candidate_id,
            candidate.candidate_name,
            candidate.nisn,
            candidate.form_number,
            candidate.enroll_school_level_id,
            candidate.enroll_school_location_id,
            candidate.enroll_year_level_id,
            candidate.origin_school,
            case when candidate.gender = 'M' then 'MALE'
                 when candidate.gender = 'F' then 'FEMALE'
                 else '-'
                end as gender,
            candidate.enrollment_status,
            candidate.religion_id,
            candidate.place_of_birth,
            candidate.date_of_birth,
            candidate.birth_certificate_number,
            candidate.nationality_id,
            candidate.identification_number,
            candidate.passport_number,
            candidate.passport_expiry_date,
            candidate.kitas_number,
            candidate.kitas_expiry_date,
            candidate.phone_number,
            candidate.cellular_number,
            candidate.email,
            candidate.address,
            candidate.neighbourhood_number,
            candidate.special_conditions,
            candidate.hamlet_number,
            candidate.urban_village,
            candidate.sub_district,
            candidate.postal_code,
            candidate.living_with,
            candidate.distance_to_school,
            candidate.estimated_time_to_school,
            candidate.transport_to_school,
            candidate.child_status,
            candidate.child_order,
            candidate.number_of_child,
            candidate.special_conditions,
            candidate.kks_number,
            candidate.kps_pkh_receiver,
            candidate.kps_pkh_number,
            candidate.kip_receiver,
            candidate.kip_number,
            candidate.kip_holder_name,
            (
                select coalesce(
                               json_agg(
                                       jsonb_build_object(
                                               'parent_id', parents.parent_id,
                                               'candidate_id', parents.candidate_id,
                                               'parent_type', parents.parent_type,
                                               'parent_name', parents.parent_name,
                                               'gender', case when parents.gender = 'M' then 'MALE' else 'FEMALE' end,
                                               'religion_id', parents.religion_id,
                                               'religion_name', null,
                                               'place_of_birth', parents.place_of_birth,
                                               'date_of_birth', parents.date_of_birth,
                                               'nationality_id', parents.nationality_id,
                                               'nationality_name', null,
                                               'identification_number', parents.identification_number,
                                               'passport_number', parents.passport_number,
                                               'passport_expiry_date', parents.passport_expiry_date,
                                               'kitas_number', parents.kitas_number,
                                               'kitas_expiry_date', parents.kitas_expiry_date,
                                               'phone_number', parents.phone_number,
                                               'cellular_number', parents.cellular_number,
                                               'email', parents.email,
                                               'address', parents.address,
                                               'neighbourhood_number', parents.neighbourhood_number,
                                               'hamlet_number', parents.hamlet_number,
                                               'urban_village', parents.urban_village,
                                               'sub_district', parents.sub_district,
                                               'postal_code', parents.postal_code,
                                               'education', parents.education,
                                               'employment_company', parents.employment_company,
                                               'job_title', parents.job_title,
                                               'salary_range', parents.salary_range
                                           )), '[]'::json)
                from parents
                where parents.deleted_at is null
                  and parents.tenant_uuid = candidate.tenant_uuid
                  and parents.candidate_id = candidate.candidate_id
            ) as parents,
            (
                select coalesce(
                               json_agg(
                                       jsonb_build_object(
                                               'achievement_id', achievement.achievement_id,
                                               'candidate_id', achievement.candidate_id,
                                               'achievement_name', achievement.achievement_name,
                                               'achievement_category', achievement.achievement_category,
                                               'achievement_level', achievement.achievement_level,
                                               'achievement_year', achievement.achievement_year,
                                               'promoter_name', achievement.promoter_name
                                           )
                                   ) , '[]'::json
                           )
                from achievement
                where achievement.deleted_at is null
                  and achievement.tenant_uuid = candidate.tenant_uuid
                  and achievement.candidate_id = candidate.candidate_id
            ) as achievements,
            (
                select coalesce(
                               json_agg(
                                       jsonb_build_object(
                                               'scholarship_id', scholarship.scholarship_id,
                                               'candidate_id', scholarship.candidate_id,
                                               'scholarship_type', scholarship.scholarship_type,
                                               'description', scholarship.description,
                                               'scholarship_year_start', scholarship.scholarship_year_start,
                                               'scholarship_year_end', scholarship.scholarship_year_end
                                           )
                                   ) , '[]'::json
                           )
                from scholarship
                where scholarship.deleted_at is null
                  and scholarship.tenant_uuid = candidate.tenant_uuid
                  and scholarship.candidate_id = candidate.candidate_id
            ) as scholarships,
            (
                select coalesce(
                               json_agg(
                                       jsonb_build_object(
                                               'candidate_id', sibling.candidate_id,
                                               'sibling_id', sibling.sibling_id,
                                               'sibling_name', sibling.sibling_name,
                                               'year_level_id', sibling.year_level_id,
                                               'year_level_name', null,
                                               'gender', case when sibling.gender = 'M' then 'MALE' else 'FEMALE' end,
                                               'religion_id', sibling.religion_id,
                                               'religion_name', null,
                                               'place_of_birth', sibling.place_of_birth,
                                               'date_of_birth', sibling.date_of_birth,
                                               'sibling_same_school', sibling.sibling_same_school,
                                               'sibling_same_enrolling', sibling.sibling_same_enrolling,
                                               'sibling_candidate_id', sibling.sibling_candidate_id
                                           )
                                   ) , '[]'::json
                           )
                from sibling
                where sibling.deleted_at is null
                  and sibling.tenant_uuid = candidate.tenant_uuid
                  and sibling.candidate_id = candidate.candidate_id
            ) as siblings,
            (
                select coalesce(
                               json_agg(
                                       jsonb_build_object(
                                               'step_order', batch_step.step_order,
                                               'step_name', batch_step.step_name
                                           )
                                   ) , '[]'::json
                           )
                from batch_step
                where batch_step.tenant_uuid = candidate.tenant_uuid
                  and batch_step.batch_id = candidate.batch_id
                  and batch_step.deleted_at IS NULL
            ) as steps,
            (
                select coalesce(
                               json_build_object(
                                       'current_status', candidate_status.current_status,
                                       'next_status', candidate_status.next_status
                                   )
                           )
                from candidate_status
                where candidate_status.tenant_uuid = candidate.tenant_uuid
                  and candidate_status.candidate_id = candidate.candidate_id
                  and candidate_status.deleted_at IS NULL
            ) as status,
            (
                select batch.batch_notes
                from batch
                where batch.deleted_at is null
                  and batch.tenant_uuid = candidate.tenant_uuid
                  and batch.batch_id = candidate.batch_id
            ) as notes
        from candidate
        where candidate.deleted_at is null
          and candidate.tenant_uuid = $1
          and candidate.candidate_id = $2