candidate-detail
unknown
pgsql
3 years ago
11 kB
4
Indexable
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 = $2Editor is loading...