rendered paste bodypublic class AcademicCredentialDAO extends BaseDAO {
private static final String SCHEMA = PropertyHelper.getProperty(PropertyHelper.DB_SCHEMA, "NYA.");
private static final String SQL_GET_ACADEMIC_EXAMINA =
"select rtrim(eq.educationorg_id) educationorg_id, eo.educationorg, rtrim(eq.examina_id) examina_id, rtrim(e.examina) examina, fos.FIELD_OF_SUBJECT_ID, rtrim(fos.field_of_subject) field_of_subject, " +
" es.EXAMINA_SPEC_ID, rtrim(es.examina_spec) examina_spec, rtrim(et.examinatype) examinatype, rtrim(en.examinanational_id) examinanational_id, " +
" en.examina examinanational, eq.credit, eq.approved_time from " + SCHEMA + "examina_qual eq inner join " + SCHEMA +
"examina e on e.educationorg_id = eq.educationorg_id and e.examina_id = eq.examina_id " + " inner join " + SCHEMA +
"education_org eo on eo.EDUCATIONORG_ID = eq.EDUCATIONORG_ID left outer join " + SCHEMA +
"examina_type et on et.examina_type_id = e.examina_type_id left outer join " + SCHEMA +
"field_of_subject fos on fos.field_of_subject_id = eq.field_of_subject_id " + " left outer join " + SCHEMA +
"examina_spec es on es.examina_spec_id = eq.examina_spec_id " +
" and es.examina_id = eq.examina_id and es.educationorg_id = eq.educationorg_id left outer join " + SCHEMA +
"examina_eq eeq on eeq.educationorg_id = eq.educationorg_id and eeq.examina_id = eq.examina_id left outer join " + SCHEMA +
"examina_national en on en.EXAMINANATIONAL_ID = eeq.EXAMINANATIONAL_ID " +
"where eq.person_id = ? order by eq.educationorg_id, e.examina_id asc";
private static final String SQL_GET_ACADEMIC_STARTED_PROGRAM_STUDIES =
"select rtrim(pr.EDUCATIONORG_ID) educationorg_id, eo.educationorg, rtrim(p.program_id) program_id, " +
" rtrim(p.program) program, p.credit, rtrim(pr.admissionsemester) admissionsemester, " +
" rtrim(pr.localization) localization, rtrim(pr.program_pace) program_pace, rtrim(pr.teachingform) teachingform, " +
" rtrim(pr.form_of_funding) form_of_funding, rtrim(class) class, pr.later_part_admission, pr.interupt_date interrupt_date, " +
" rtrim(psr.PROGRAMSPEC_ID) programspec_id, rtrim(ps.programspec) programspec, ps.CREDIT creditspec, " +
" rtrim(psr.ADMISSIONSEMESTER) admissionsemesterspec, psr.INTERUPT_DATE interrupt_date_spec, rtrim(pb.from_semester) from_semester, " +
" rtrim(pb.to_semester) to_semester, " +
" pb.readmission_guarantee, pb.reg_allowed, rtrim(pb.program_break) program_break from " + SCHEMA + "program_reg pr " +
" inner join " + SCHEMA + "program p on pr.program_id = p.program_id and pr.educationorg_id = p.educationorg_id " +
" left outer join " + SCHEMA +
"program_spec_reg psr on pr.educationorg_id = psr.educationorg_id and pr.person_id = psr.person_id and pr.program_id = psr.program_id " +
" left outer join " + SCHEMA +
"program_spec ps on psr.educationorg_id = ps.educationorg_id and psr.program_id = ps.program_id and psr.programspec_id = ps.programspec_id " +
" left outer join " + SCHEMA +
"program_break pb on pr.educationorg_id = pb.educationorg_id and pr.person_id = pb.person_id and pr.program_id = pb.program_id " +
" inner join " + SCHEMA + "education_org eo on eo.educationorg_id = pr.educationorg_id " +
"where pr.person_id = ? order by pr.educationorg_id, pr.program_id";
private static final String SQL_GET_ACADEMIC_COURSES_WITHIN_PROGRAM =
"select rtrim(crq.educationorg_id) educationorg_id, eo.EDUCATIONORG educationorg, rtrim(crq.program_id) program_id, rtrim(p.PROGRAM) program, p.credit, " +
" rtrim(crq.COURSE_ID) course_id, rtrim(crq.MARK) mark, rtrim(crq.FORM_OF_FUNDING) form_of_funding, rtrim(crq.TEACHING_FORM) teaching_form, " +
" rtrim(crq.LOCALIZATION) localization, crq.APPROVED_DATE, rtrim(c.COURSE) course, c.CREDIT coursecredit, p.semester_registration, " +
" rtrim(crq.semester_alt) semester, crq.localization, crq.course_pace, crq.course_time, crq.teaching_form, crq.form_of_funding, crq.interrupt_date, " +
" rtrim(fos.FIELD_OF_SUBJECT) field_of_subject, rtrim(c.FIELD_OF_SUBJECT_ID) field_of_subject_id, c.COURSELEVEL_ID, crq.programspec_id " +
" from " + SCHEMA + "COURSE_REG_QUAL crq inner join " + SCHEMA +
"program p on crq.PROGRAM_ID = p.PROGRAM_ID and crq.EDUCATIONORG_ID = p.EDUCATIONORG_ID inner join " + SCHEMA +
"EDUCATION_ORG eo on crq.educationorg_ID = eo.educationorg_ID inner join " + SCHEMA +
"COURSE c on crq.course_ID = c.course_id and crq.EDUCATIONORG_ID = c.EDUCATIONORG_ID left outer join " + SCHEMA +
"FIELD_OF_SUBJECT fos on c.FIELD_OF_SUBJECT_ID = fos.FIELD_OF_SUBJECT_ID " +
" where crq.PROGRAM_ID is not null and crq.person_id = ?" +
" order by crq.EDUCATIONORG_id, crq.PROGRAM_ID, crq.PROGRAMSPEC_ID, crq.COURSE_ID";
private static final String SQL_GET_ACADEMIC_COURSES_WITHIN_PROGRAM_SPEC =
"select rtrim(crq.educationorg_id) educationorg_id, eo.EDUCATIONORG educationorg, rtrim(ps.programspec_id) programspec_id, rtrim(ps.programspec) programspec, " +
"ps.credit, rtrim(crq.COURSE_ID) course_id, rtrim(crq.MARK) mark, rtrim(crq.FORM_OF_FUNDING) form_of_funding, " +
"rtrim(crq.TEACHING_FORM) teaching_form, rtrim(crq.LOCALIZATION) localization, crq.APPROVED_DATE, c.COURSE course, " +
"c.CREDIT coursecredit, p.semester_registration semester_registration, rtrim(crq.semester_alt) semester, crq.localization, " +
"crq.course_pace, crq.course_time, crq.teaching_form, crq.form_of_funding, crq.interrupt_date, " +
"rtrim(fos.FIELD_OF_SUBJECT) field_of_subject, rtrim(c.FIELD_OF_SUBJECT_ID) field_of_subject_id, c.COURSELEVEL_ID, " +
"crq.PROGRAMSPEC_ID, crq.PERSON_ID from " + SCHEMA + "COURSE_REG_QUAL crq inner join " + SCHEMA +
"program p on crq.PROGRAM_ID = p.PROGRAM_ID and crq.EDUCATIONORG_ID = p.EDUCATIONORG_ID inner join " + SCHEMA +
"PROGRAM_SPEC ps on crq.PROGRAM_ID = ps.PROGRAM_ID and crq.PROGRAMSPEC_ID = ps.PROGRAMSPEC_ID and crq.EDUCATIONORG_ID = ps.EDUCATIONORG_ID " +
" inner join " + SCHEMA + "EDUCATION_ORG eo on crq.educationorg_ID = eo.educationorg_ID inner join " + SCHEMA +
"COURSE c on crq.course_ID = c.course_id and crq.EDUCATIONORG_ID = c.EDUCATIONORG_ID left outer join " + SCHEMA +
"FIELD_OF_SUBJECT fos on c.FIELD_OF_SUBJECT_ID = fos.FIELD_OF_SUBJECT_ID " +
"where crq.PROGRAMSPEC_ID is not null and crq.person_id = ? and crq.PROGRAM_ID = ? and crq.EDUCATIONORG_ID = ? " +
"order by crq.EDUCATIONORG_id, crq.PROGRAM_ID, crq.PROGRAMSPEC_ID, crq.COURSE_ID";
private static final String SQL_GET_PROGRAM_SEMESTER =
"select psr.current_semester, rtrim(sd.semester_id) semester, 'FFG' registration_type, psr.semester sem from " + SCHEMA +
"PROGRAM_SEMESTER_REG psr inner join " + SCHEMA + "SEMESTER_DEFINITION sd on psr.semester = sd.semester_alt " +
"where person_id = ? and EDUCATIONORG_ID = ? and PROGRAM_ID = ? union " +
"(select psrr.current_semester, rtrim(sd.semester_id) semester, 'OM' registration_type, psrr.semester sem from " + SCHEMA +
"PROGRAM_SEMESTER_RE_REG psrr inner join " + SCHEMA + "SEMESTER_DEFINITION sd on psrr.semester = sd.semester_alt " +
"where person_id = ? and EDUCATIONORG_ID = ? and PROGRAM_ID = ?) order by current_semester, sem";
private static final String SQL_GET_PROGRAM_SPEC_SEMESTER =
"select psr.current_semester, rtrim(sd.semester_id) semester, 'FFG' registration_type, psr.semester sem from " + SCHEMA +
"PROGRAM_SEMESTER_REG psr inner join " + SCHEMA + "SEMESTER_DEFINITION sd on psr.semester = sd.semester_alt " +
"where person_id = ? and EDUCATIONORG_ID = ? and PROGRAM_ID = ? and programspec_id = ? union " +
"(select psrr.current_semester, rtrim(sd.semester_id) semester, 'OM' registration_type, psrr.semester sem from " + SCHEMA +
"PROGRAM_SEMESTER_RE_REG psrr inner join " + SCHEMA + "SEMESTER_DEFINITION sd on psrr.semester = sd.semester_alt " +
"where person_id = ? and EDUCATIONORG_ID = ? and PROGRAM_ID = ? and programspec_id = ?) order by current_semester, sem";
private static final String SQL_GET_COURSE_REG =
"select rtrim(educationorg_id) educationorg_id, rtrim(course_id) course_id, rtrim(type_of_registration) type_of_registration, course_id, rtrim(crr.semester_id) semester, crr.interrupt_date " +
"from " + SCHEMA + "course_re_reg crr where person_id = ? " +
"order by educationorg_id, course_id, type_of_registration, crr.semester_id";
private static final String SQL_GET_COURSE_TESTS =
"select rtrim(ticq.EDUCATIONORG_ID) educationorg_id, rtrim(cts.EDUCATIONORG_ID) swe_eductionorg_id, rtrim(ticq.course_id) course_id, rtrim(ticq.TEST_IN_COURSE_ID) test_in_course_id, rtrim(tic.TEST_IN_COURSE) test_in_course, tic.CREDIT, rtrim(ticq.MARK) mark, ticq.TEST_DATE, " +
"ticq.CREDITED_CREDIT, rtrim(cts.COUNTRY) country, rtrim(cts.FOREIGN_EDUCATIONORG_ID) foreign_educationorg_id from " + SCHEMA +
"TEST_IN_COURSE_QUAL ticq inner join " + SCHEMA +
"TEST_IN_COURSE tic on ticq.EDUCATIONORG_ID = tic.EDUCATIONORG_ID and ticq.COURSE_ID = tic.COURSE_ID and ticq.TEST_IN_COURSE_ID = tic.TEST_IN_COURSE_ID " +
"left outer join " + SCHEMA +
"CREDITED_TEST_SPEC cts on ticq.EDUCATIONORG_ID = cts.EDUCATIONORG_ID and ticq.COURSE_ID = cts.COURSE_ID and ticq.TEST_IN_COURSE_ID = cts.TEST_IN_COURSE_ID and ticq.PERSON_ID = cts.PERSON_ID " +
"where ticq.person_id = ? order by ticq.EDUCATIONORG_ID, ticq.course_id, ticq.TEST_IN_COURSE_ID";
private static final String SQL_GET_COURSES = "select rtrim(crq.educationorg_id) educationorg_id, eo.EDUCATIONORG educationorg, " +
"rtrim(crq.COURSE_ID) course_id, rtrim(crq.MARK) mark, rtrim(crq.FORM_OF_FUNDING) form_of_funding, " +
"rtrim(crq.TEACHING_FORM) teaching_form, rtrim(crq.LOCALIZATION) localization, crq.APPROVED_DATE, rtrim(c.COURSE) course, " +
"c.CREDIT coursecredit, rtrim(crq.semester_alt) semester, crq.localization, " +
"crq.course_pace, crq.course_time, crq.teaching_form, crq.form_of_funding, crq.interrupt_date, " +
"rtrim(fos.FIELD_OF_SUBJECT) field_of_subject, rtrim(c.FIELD_OF_SUBJECT_ID) field_of_subject_id, c.COURSELEVEL_ID, " +
"crq.PERSON_ID from " + SCHEMA + "COURSE_REG_QUAL crq inner join " + SCHEMA +
"EDUCATION_ORG eo on crq.educationorg_ID = eo.educationorg_ID inner join " + SCHEMA +
"COURSE c on crq.course_ID = c.course_id and crq.EDUCATIONORG_ID = c.EDUCATIONORG_ID left outer join " + SCHEMA +
"FIELD_OF_SUBJECT fos on c.FIELD_OF_SUBJECT_ID = fos.FIELD_OF_SUBJECT_ID " +
"where crq.PROGRAM_ID is null and crq.person_id = ? order by crq.educationorg_id, crq.COURSE_ID ";
private static final String SQL_GET_OUTGOING_EXCHANGE =
"select rtrim(oe.educationorg_id) educationorg_id, rtrim(eo.EDUCATIONORG) educationorg, oe.semester, oe.START_WEEK, " +
"oe.STOP_WEEK, rtrim(oe.EXCHANGE_ORG) exchangeorg, rtrim(feo.FOREIGN_EDUCATION_ORG) foreign_education_org, rtrim(c.country) country, " +
"rtrim(d.DEPARTMENT) department, rtrim(oe.NOTE) note, oer.SEMESTER semesterreg, oer.REGISTERED, oer.CREDIT, " +
"rtrim(oer.PROGRAM_ID) program_id, rtrim(oer.PROGRAMSPEC_ID) programspec_id, oe.EXCHANGE_NUMBER from " + SCHEMA +
"OUTGOING_EXCHANGE oe inner join " + SCHEMA + "EDUCATION_ORG eo on eo.EDUCATIONORG_ID = oe.EDUCATIONORG_ID " +
"left outer join " + SCHEMA + "OUTGOING_EXCHANGE_REG oer on oe.EXCHANGE_NUMBER = oer.EXCHANGE_NUMBER " +
"and oe.PERSON_ID = oer.PERSON_ID and oe.EDUCATIONORG_ID = oer.EDUCATIONORG_ID and oe.SEMESTER = oer.SEMESTER " +
"inner join " + SCHEMA + "country c on c.country_id = oe.COUNTRY inner join " + SCHEMA +
"Foreign_EDUCATION_ORG feo on feo.FOREIGN_EDUCATIONORG_ID = oe.FOREIGN_EDUCATIONORG_ID " +
"and feo.EDUCATIONORG_ID = oe.EDUCATIONORG_ID and feo.COUNTRY_ID = oe.COUNTRY left outer join " + SCHEMA +
"department d on oe.DEPARTMENT_ID = d.DEPARTMENT_ID and oe.EDUCATIONORG_ID = d.EDUCATIONORG_ID where oe.person_id = ? " +
"order by oe.person_id, oe.EDUCATIONORG_ID, oe.EXCHANGE_NUMBER, oe.EXCHANGE_ORG, oe.FOREIGN_EDUCATIONORG_ID, oe.semester";
private static final String SQL_GET_INCOMING_EXCHANGE =
"select rtrim(oe.educationorg_id) educationorg_id, oe.EXCHANGE_NUMBER, rtrim(eo.EDUCATIONORG) educationorg, oe.SEMESTER, oe.START_WEEK, oe.STOP_WEEK, rtrim(oe.EXCHANGE_ORG) exchangeorg, " +
"rtrim(feo.FOREIGN_EDUCATION_ORG) foreign_education_org, rtrim(c.country) country, rtrim(d.DEPARTMENT) department, rtrim(oe.NOTE) note " +
"from " + SCHEMA + "INCOMING_EXCHANGE oe inner join " + SCHEMA +
"EDUCATION_ORG eo on eo.EDUCATIONORG_ID = oe.EDUCATIONORG_ID inner join " + SCHEMA +
"country c on c.country_id = oe.COUNTRY inner join " + SCHEMA +
"Foreign_EDUCATION_ORG feo on feo.FOREIGN_EDUCATIONORG_ID = oe.FOREIGN_EDUCATIONORG_ID and feo.EDUCATIONORG_ID = oe.EDUCATIONORG_ID and feo.COUNTRY_ID = oe.COUNTRY " +
"left outer join " + SCHEMA + "department d on oe.DEPARTMENT_ID = d.DEPARTMENT_ID and oe.EDUCATIONORG_ID = d.EDUCATIONORG_ID " +
"where oe.person_id = ? " +
"order by oe.person_id, oe.EDUCATIONORG_ID, oe.EXCHANGE_NUMBER, oe.EXCHANGE_ORG, oe.FOREIGN_EDUCATIONORG_ID, oe.semester";
private static final String SQL_GET_COURSE_INCOMING_EXCHANGE =
"select rtrim(ierq.EDUCATIONORG_ID) educationorg_id, rtrim(eo.EDUCATIONORG) educationorg, rtrim(ierq.COURSE_ID) course_id, " +
" rtrim(c.course) course, rtrim(c.COURSELEVEL_ID) courselevel_id, rtrim(ierq.SEMESTER) semester, rtrim(ierq.LOCALIZATION_ID) localization, " +
" rtrim(ierq.FORM_OF_FUNDING_ID) form_of_funding_id, rtrim(ierq.TEST_IN_COURSE_ID) test_in_course_id, ierq.qual_date, " +
" rtrim(tic.TEST_IN_COURSE) test_in_course, tic.CREDIT, rtrim(ticq.MARK) mark, ticq.TEST_DATE from " + SCHEMA +
"INCOMING_EXCHANGE_REG_QUAL ierq inner join " + SCHEMA +
"EDUCATION_ORG eo on ierq.educationorg_ID = eo.educationorg_ID inner join " + SCHEMA +
"course c on c.educationorg_id = ierq.educationorg_id and c.Course_id = ierq.course_id inner join " + SCHEMA +
"TEST_IN_COURSE_QUAL ticq on ierq.PERSON_ID = ticq.PERSON_ID and ierq.EDUCATIONORG_ID = ticq.EDUCATIONORG_ID " +
" and ierq.COURSE_ID = ticq.COURSE_ID and ierq.TEST_IN_COURSE_ID = ticq.TEST_IN_COURSE_ID inner join " + SCHEMA +
"TEST_IN_COURSE tic on tic.EDUCATIONORG_ID = ticq.EDUCATIONORG_ID " +
" and tic.COURSE_ID = ticq.COURSE_ID and tic.TEST_IN_COURSE_ID = ticq.TEST_IN_COURSE_ID " +
"where ierq.COURSE_ID is not null and ierq.TEST_IN_COURSE_ID is not null and ierq.person_id = ? " +
"order by ierq.educationorg_id, course_id, ierq.TEST_IN_COURSE_ID, ierq.TEST_IN_COURSE";
private static final String SQL_GET_COURSE_INCOMING_EXCHANGE_OTHER =
"select rtrim(ierq.EDUCATIONORG_ID) educationorg_id, rtrim(eo.EDUCATIONORG) educationorg, rtrim(ierq.TEST_IN_COURSE) test_in_course, " +
" ierq.CREDIT, rtrim(ierq.MARK) mark, ierq.QUAL_DATE, rtrim(ierq.SEMESTER) semester, rtrim(ierq.LOCALIZATION_ID) localization, " +
" rtrim(ierq.FORM_OF_FUNDING_ID) form_of_funding_id, rtrim(foe.FIELD_OF_EDUCATION_ID) field_od_education_id, " +
"rtrim(foe.FIELD_OF_EDUCATION) field_of_education, ierq.COURSELEVEL_ID, cl.courselevel, rtrim(d.DEPARTMENT) department " +
"from " + SCHEMA + "INCOMING_EXCHANGE_REG_QUAL ierq inner join " + SCHEMA +
"FIELD_OF_EDUCATION foe on foe.FIELD_OF_EDUCATION_ID = ierq.FIELD_OF_EDUCATION_ID inner join " + SCHEMA +
"EDUCATION_ORG eo on ierq.educationorg_ID = eo.educationorg_ID inner join " + SCHEMA +
"DEPARTMENT d on d.DEPARTMENT_ID = ierq.DEPARTMENT_ID and d.EDUCATIONORG_ID = ierq.EDUCATIONORG_ID inner join " + SCHEMA +
"COURSE_LEVEL cl on cl.COURSELEVEL_ID = ierq.COURSELEVEL_ID " +
"where COURSE_ID is null and TEST_IN_COURSE_ID is null and TEST_IN_COURSE is not null and CREDIT is not null and ierq.COURSELEVEL_ID is not null " +
" and ierq.DEPARTMENT_ID is not null and ierq.FIELD_OF_EDUCATION_ID is not null and ierq.person_id = ? " +
"order by ierq.educationorg_id, ierq.TEST_IN_COURSE";
private static final String SQL_GET_CREDITED_COURSE =
"select caq.CREDITED_DATE, rtrim(caq.DECIDED_BY) decided_by, rtrim(caq.COURSE_ID) course_id, rtrim(c.COURSE) course, coalesce(caq.CREDIT, c.credit) credited_credit, " +
" rtrim(caq.PROGRAMSPEC_ID) programspec_id, rtrim(e.educationorg) educationorg, rtrim(caq.EDUCATIONORG_ID) educationorg_id, caq.CREDIT_NUMBER, " +
" rtrim(caq.program_id) program_id, rtrim(caq.CREDITED_ACADEMIC_QUAL) credited_academic_qual, caq.qual_number from " + SCHEMA +
"CREDITED_ACADEMIC_QUAL caq inner join " + SCHEMA +
"EDUCATION_ORG e on e.EDUCATIONORG_ID = caq.EDUCATIONORG_ID left outer join " + SCHEMA +
"course c on caq.EDUCATIONORG_ID = c.EDUCATIONORG_ID and caq.COURSE_ID = c.COURSE_ID where caq.person_id = ? " +
"order by caq.EDUCATIONORG_ID, caq.credited_date, caq.COURSE_ID, caq.credited_academic_qual";
private static final String SQL_GET_CREDITED_COURSE_INTERNAL =
"select rtrim(cis.EDUCATIONORG_ID) educationorg_id, rtrim(c.course_id) course_id, rtrim(c.course) course, c.credit, crq.MARK, crq.APPROVED_DATE, 'H' type, cis.CREDIT_NUMBER, " +
"rtrim(eo.EDUCATIONORG) educationorg from " + SCHEMA + "CREDITED_INTERNAL_SPEC cis " + " inner join " + SCHEMA +
"EDUCATION_ORG eo on cis.EDUCATIONORG_ID = eo.EDUCATIONORG_ID left outer join " + SCHEMA +
"course_reg_qual crq on cis.EDUCATIONORG_ID = crq.EDUCATIONORG_ID and cis.PERSON_ID = crq.PERSON_ID and cis.COURSE_ID = crq.COURSE_ID " +
"inner join " + SCHEMA + "course c on c.course_id = cis.COURSE_ID and c.educationorg_id = cis.educationorg_id " +
"where cis.person_id = ? order by educationorg_id, cis.CREDIT_NUMBER ";
private static final String SQL_GET_CREDITED_COURSE_EXTERNAL =
"select rtrim(ces.CREDITED_EDUCATIONORG_ID) credited_educationorg_id, rtrim(eo.EDUCATIONORG) creditededucationorg, rtrim(ces.FOREIGN_EDUCATIONORG_ID) foreign_educationorg_id, " +
"rtrim(feo.FOREIGN_EDUCATION_ORG) foreign_education_org, " +
" rtrim(ces.CREDITED_QUAL) credited_qual, ces.org_credit, ces.MARK, ces.org_date, ces.TYPE, ces.CREDIT_NUMBER, ces.QUAL_NUMBER, " +
"rtrim(ces.CREDITED_EDUCATIONORG_ID) credited_educationorg_id, rtrim(c.COUNTRY) country, " +
" rtrim(ces.educationorg_id) educationorg_id from " + SCHEMA + "CREDITED_EXTERNAL_SPEC ces left outer join " + SCHEMA +
"country c on ces.COUNTRY = c.COUNTRY_ID left outer join " + SCHEMA +
"EDUCATION_ORG eo on ces.CREDITED_EDUCATIONORG_ID = eo.EDUCATIONORG_ID left outer join " + SCHEMA +
"FOREIGN_EDUCATION_ORG feo on ces.EDUCATIONORG_ID = feo.EDUCATIONORG_ID and ces.FOREIGN_EDUCATIONORG_ID = feo.FOREIGN_EDUCATIONORG_ID " +
" and ces.COUNTRY = feo.COUNTRY_ID where ces.person_id = ? order by CREDIT_NUMBER, qual_number";
private static final String SQL_GET_MERITS_SUMMARY = "select 'LADOK_TOT_QUAL', count(1) from " + SCHEMA +
"LADOK_TOT_QUAL ltq where PERSON_ID = ? union all select 'EXAMINA_QUAL', count(1) from " + SCHEMA +
"EXAMINA_QUAL where PERSON_ID = ? union all select 'CREDITED_ACADEMIC_QUAL', count(1) from " + SCHEMA +
"CREDITED_ACADEMIC_QUAL where PERSON_ID = ? union all select 'COURSE_REG_QUAL', count(1) from " + SCHEMA +
"COURSE_REG_QUAL where PERSON_ID = ? union all select 'PROGRAM_REG', count(1) from " + SCHEMA +
"PROGRAM_REG where PERSON_ID = ? union all select 'INCOMING_EXCHANGE_REG_QUAL', count(1) from " + SCHEMA +
"INCOMING_EXCHANGE_REG_QUAL where PERSON_ID = ? union all select 'INCOMING_EXCHANGE', count(1) from " + SCHEMA +
"INCOMING_EXCHANGE where PERSON_ID = ? union all select 'OUTGOING_EXCHANGE', count(1) from " + SCHEMA +
"OUTGOING_EXCHANGE where PERSON_ID = ?";
private static final String SQL_GET_COURSE_SUBJECTS =
"select rtrim(rcq.educationorg_id) educationorg_id, rtrim(rcq.COURSE_ID) course_id, rtrim(cifos.FIELD_OF_SUBJECT_ID) field_of_subject_id, rtrim(cifos.COURSELEVEL_ID) courselevel_id, " +
"rtrim(fos.FIELD_OF_SUBJECT) field_of_subject from " + SCHEMA + "course_REG_QUAL rcq inner join " + SCHEMA +
"COURSE_IN_FIELD_OF_SUBJECT cifos on cifos.EDUCATIONORG_ID = rcq.EDUCATIONORG_ID and cifos.COURSE_ID = rcq.COURSE_ID " +
"inner join " + SCHEMA + "FIELD_OF_SUBJECT fos on cifos.FIELD_OF_SUBJECT_ID = fos.FIELD_OF_SUBJECT_ID " +
"where rcq.PERSON_ID = ?";