DROP TABLE IF EXISTS tmp_lm_course_of_study;
CREATE TABLE tmp_lm_course_of_study (
    id integer NOT NULL,
    lid integer NOT NULL,
    degree_lid integer,
    degree_id integer,
    subject_lid integer,
    subject_id integer,
    major_field_of_study_lid integer,
    major_field_of_study_id integer,
    course_specialization_lid integer,
    course_specialization_id integer,
    k_subject_indicator_id integer,
    k_examinationversion_id integer,
    k_place_of_studies_id integer,
    k_enrollment_id integer,
    k_type_of_study_id integer,
    k_form_of_studies_id integer,
    part_of_studies integer NOT NULL,
    valid_from date,
    valid_to date,
    k_language_id integer NOT NULL,
    shorttext character varying,
    defaulttext character varying NOT NULL,
    longtext character varying,
    orgunit_lid integer,
    orgunit_id integer,
    regular_number_of_semesters integer,
    uniquename character varying,
    from_term_year integer,
    from_term_type_id integer,
    to_term_year integer,
    to_term_type_id integer,
    admission_to_study_id integer,
    is_admission_to_study integer,
    is_course_of_study_start integer,
    k_course_of_study_type_id integer NOT NULL,
    termcategory integer NOT NULL,
    teachingunit_orgunit_lid integer,
    teachingunit_orgunit_id integer,
    k_field_of_study_id integer,
    examination_office_no character varying,
    is_historized smallint
);

insert into tmp_lm_course_of_study( id,
       lid,
       degree_lid,
       subject_lid,
       major_field_of_study_lid,
       course_specialization_lid,
       k_subject_indicator_id,
       k_examinationversion_id,
       k_place_of_studies_id,
       k_enrollment_id,
       k_type_of_study_id,
       k_form_of_studies_id,
       part_of_studies,
       valid_from,
       valid_to,
       k_language_id,
       shorttext,
       defaulttext,
       longtext,
       orgunit_lid,
       regular_number_of_semesters,
       uniquename,
       from_term_year,
       from_term_type_id,
       to_term_year,
       to_term_type_id,
       admission_to_study_id,
       is_admission_to_study,
       is_course_of_study_start,
       k_course_of_study_type_id,
       termcategory,
       teachingunit_orgunit_lid,
       k_field_of_study_id,
       examination_office_no,
       is_historized)
SELECT id,
       lid,
       degree_lid,
       subject_lid,
       major_field_of_study_lid,
       course_specialization_lid,
       k_subject_indicator_id,
       k_examinationversion_id,
       k_place_of_studies_id,
       k_enrollment_id,
       k_type_of_study_id,
       k_form_of_studies_id,
       part_of_studies,
       valid_from,
       valid_to,
       k_language_id,
       shorttext,
       defaulttext,
       longtext,
       orgunit_lid,
       regular_number_of_semesters,
       uniquename,
       from_term_year,
       from_term_type_id,
       to_term_year,
       to_term_type_id,
       admission_to_study_id,
       is_admission_to_study,
       is_course_of_study_start,
       k_course_of_study_type_id,
       termcategory,
       teachingunit_orgunit_lid,
       k_field_of_study_id,
       examination_office_no,
       0 --is_historized
FROM course_of_study;
--ermitteln der historisierten IDs
--degree
update tmp_lm_course_of_study set degree_id=(select D.id from degree D
where D.lid=tmp_lm_course_of_study.degree_lid
and (D.valid_from <= tmp_lm_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_lm_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
);
--wenn degree nicht ermittelt werden kann, dann ohne Historisierung
update tmp_lm_course_of_study set degree_id=(select min(D.id) from degree D
where D.lid=tmp_lm_course_of_study.degree_lid
)
where degree_id is null
;
--subject
update tmp_lm_course_of_study set subject_id=(select D.id from subject D
where D.lid=tmp_lm_course_of_study.subject_lid
and (D.valid_from <= tmp_lm_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_lm_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
);
--wenn subject nicht ermittelt werden kann, dann ohne Historisierung
update tmp_lm_course_of_study set subject_id=(select min(D.id) from subject D
where D.lid=tmp_lm_course_of_study.subject_lid
)
where subject_id is null
;
--major_field_of_study
update tmp_lm_course_of_study set major_field_of_study_id=(select D.id from major_field_of_study D
where D.lid=tmp_lm_course_of_study.major_field_of_study_lid
and (D.valid_from <= tmp_lm_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_lm_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
);
--course_specialization_lid
update tmp_lm_course_of_study set course_specialization_id=(select D.id from course_specialization D
where D.lid=tmp_lm_course_of_study.course_specialization_lid
and (D.valid_from <= tmp_lm_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_lm_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
);
--orgunit
update tmp_lm_course_of_study set orgunit_id=(select D.id from orgunit D
where D.lid=tmp_lm_course_of_study.orgunit_lid
and (D.valid_from <= tmp_lm_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_lm_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
);
--wenn Studiengang heute gültig ist und orgunit historisiert, nimmt er den aktuellen FB
update tmp_lm_course_of_study set orgunit_id=(select D.id from orgunit D
where D.lid=tmp_lm_course_of_study.orgunit_lid
and current_date between D.valid_from and D.valid_to)
where current_date between valid_from and valid_to
and orgunit_id is null;


--teachingunit_orgunit_lid

update tmp_lm_course_of_study set teachingunit_orgunit_id=(select D.id from orgunit D
where D.lid=tmp_lm_course_of_study.teachingunit_orgunit_lid
and (D.valid_from <= tmp_lm_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_lm_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_lm_course_of_study.valid_from is null)
)
)
where teachingunit_orgunit_lid is not null;

--wenn Studiengang heute gültig ist und orgunit historisiert, nimmt er aktuelle LE
update tmp_lm_course_of_study set teachingunit_orgunit_id=(select D.id from orgunit D
where D.lid=tmp_lm_course_of_study.teachingunit_orgunit_lid
and current_date between D.valid_from and D.valid_to)
where current_date between valid_from and valid_to
and teachingunit_orgunit_id is null;


--k_field_of_study
update tmp_lm_course_of_study set k_field_of_study_id=(select S.k_field_of_study_id
from subject S
where S.id=tmp_lm_course_of_study.subject_id)
where k_field_of_study_id is null;
--hochprojizieren auf Fächergruppe
update tmp_lm_course_of_study set k_field_of_study_id=(select K.parent_id
from k_field_of_study K
where K.id=tmp_lm_course_of_study.k_field_of_study_id);

--is_historized=1 wenn es mehrere Studiengänge pro LID gibt
update tmp_lm_course_of_study set is_historized=1
where 1 < (select count(*) from course_of_study C
where C.lid=tmp_lm_course_of_study.lid);

create index tmp_lm_i_tc1 on tmp_lm_course_of_study(lid);

select 1,6,'LM_Quellsystem' 
;

SELECT id,
   6 as sourcesystem,
substring(U.shorttext from 1 for 255),
substring(U.defaulttext from 1 for 255),
substring(U.longtext from 1 for 255),
U.elementnr,
U.calculate_ects_sum,
U.official_statistics,  
U.k_elementtype_id,
U.default_unitrelation_id,
null::char(1) as pdum,
substring(U.uniquename from 1 for 255),
U.hours_per_week,
U.editing_k_status_id,
U.transfer_allowed,
U.freetrial_check,
U.k_unit_purpose_id
 FROM
 unit U 
    ;

SELECT US.id,
   6 as sourcesystem,
US.unit_id,
US.course_of_study_lid,
US.root_elementtype_hiskey
from unit_studies US
where US.root_elementtype_hiskey=4 --nur Prüfungen #189067

SELECT C.id,
C.lid,
   6 as sourcesystem,
substring(C.uniquename from 1 for 255),
substring(C.shorttext from 1 for 255),
substring(C.defaulttext from 1 for 255),
substring(C.longtext from 1 for 255),
C.k_examinationversion_id,--    pversion,
C.orgunit_id,--       fb,
C.k_enrollment_id,--   stutyp,
C.k_subject_indicator_id,--   kzfa,
C.k_place_of_studies_id,--   stort,
C.teachingunit_orgunit_id,--  lehreinh,
C.degree_id,--   abschl,
C.subject_id,--  stg,
C.major_field_of_study_id,--  vert,
C.course_specialization_id,--  schwp,
C.k_type_of_study_id,--    stuart,
C.k_form_of_studies_id,--  stufrm,
C.regular_number_of_semesters,--  regelstz,
'' || C.to_term_year || T.termnumber, --  sem_gueltigbis
C.part_of_studies
       FROM tmp_lm_course_of_study C
       left outer join term_type  T on (C.to_term_type_id=T.id);

SELECT R.id,
6 as sourcesystem,
R.parent_unit_id,
R.child_unit_id,
R.k_unitrelationtype_id,
R.recommended_semester,
R.bonus,
R.malus,
R.generation_weight,
--neu
G.type_of_grading,
R.sortorder,
R.k_compulsory_id
 FROM
 unitrelation R left outer join grading_type G
 on (R.grading_type_id = G.id)
 ;

SELECT R.id,
6 as sourcesystem,
R.parent_examplan_id,
R.child_examplan_id,
R.k_unitrelationtype_id,
null::integer as unit_id,
R.bonus,
R.malus,
R.k_workstatus_id,
R.credits_used_for_weighting,
(case when A.hiskey_id=1001 then 1::integer else 0::integer end) as is_approved,
R.k_internal_accreditationtype_id,
R.date_of_work,
'' || R.term_year || T.termnumber as sem_der_pruefung
 FROM
 examrelation R left outer join k_internal_accreditationtype A
 on (A.id=R.k_internal_accreditationtype_id)
 left outer join term_type T
 on (T.id = R.term_type_id)
  
    ;

SELECT E.id,
6 as sourcesystem,
E.unit_id,
E.source_event_unit_id,
E.k_examform_id,
E.k_examination_type_id,
E.exam_written
 FROM
 examination E  
    ;

SELECT S.registrationnumber,--TODO: Pseudonym
E.id,
6 as sourcesystem,
E.unit_id,
E.person_id,
E.planelement_id,
E.cancelation, --prueck
E.date_of_work, --pdatum
E.semesterhours,
E.k_examform_id, --pform
'' || E.term_year || T.termnumber,--psem,
E.term_segment, --ptermin
E.k_grade_assessment_status_id,
E.k_remark_on_exam_id, --pvermerk
E.default_examrelation_id,
E.cancelation_impossible,
E.additional_work,
G.grade, --pnote TODO: umrechung bei Staatsexamen Jura in Note
G.score,
null::integer as panerk,
G.attempt,
G.free_trial
FROM student S,   term_type T , unit U, k_elementtype K ,examplan E , examrelation R left outer join examresult G
on (G.examrelation_id=R.id)
where E.default_examrelation_id=R.id
and U.id=E.unit_id
and U.k_elementtype_id=K.id
and T.id = E.term_type_id
and S.person_id = E.person_id
  AND (
    U.uniquename != 'AP-EXT'
    OR U.uniquename IS NULL
  ) --ext.Pruefungnen ausschliessen
  AND (K.hiskey_id is null or K.hiskey_id not in (7,10)) --keine HZB-Leistungen, Einzureichende Unterlagen für STU #189067
  ;

select E.id,
6 as sourcesystem,
null as ordnr,
PE.surname,
PE.firstname,
PA.sortorder as ordnr2
from hisinone.examplan E, hisinone.planelement P, hisinone.planned_dates PD, hisinone.planable A, hisinone.person_planable PA, hisinone.person PE
where E.planelement_id=P.id
and A.id=P.id
and PD.planable_id=A.id
and A.id=PA.planable_id
and PA.abstract_person_id=PE.id
and '$LM_PRUEFER_NAME'='true'
union
select E.id,
6 as sourcesystem,
1 as ordnr,
PE.surname,
PE.firstname,
I.sortorder as ordnr2
from hisinone.examplan E, hisinone.planelement P, hisinone.planned_dates PD, hisinone.planable A, hisinone.instructor I, hisinone.k_instructortask IT, hisinone.person PE
where E.planelement_id=P.id
and A.id=P.id
and PD.planable_id=A.id
and PD.id=I.planned_dates_id
and I.k_instructortask_id=IT.id
and IT.hiskey_id =1 
and I.abstract_person_id=PE.id
and '$LM_PRUEFER_NAME'='true'
union
select E.id,
6 as sourcesystem,
2 as ordnr,
PE.surname,
PE.firstname,
I.sortorder as ordnr2
from hisinone.examplan E, hisinone.planelement P, hisinone.planned_dates PD, hisinone.planable A, hisinone.instructor I, hisinone.k_instructortask IT, hisinone.person PE
where E.planelement_id=P.id
and A.id=P.id
and PD.planable_id=A.id
and PD.id=I.planned_dates_id
and I.k_instructortask_id=IT.id
and IT.hiskey_id =2
and I.abstract_person_id=PE.id
and '$LM_PRUEFER_NAME'='true'
--Prüfungen ohne Veranstaltung, z.B. Abschlussarbeiten:
union
select E.id,
6 as sourcesystem,
IT.hiskey_id as ordnr,
P.surname,
P.firstname,
null as ordnr2
from hisinone.examplan E, hisinone.examrelation R, hisinone.examresult ER,hisinone.examvaluation V, hisinone.k_instructortask IT, hisinone.person P
where (E.default_examrelation_id=R.id or R.child_examplan_id=E.id)
and V.examresult_id=ER.id
and ER.examrelation_id=R.id
and IT.id=V.k_instructortask_id
and P.id=V.person_id
and '$LM_PRUEFER_NAME'='true'

SELECT id,
       examrelation_id,
       priority,
       hierarchy_level,
       anchor_unit_id,
       path_group,
       unitrelation_id,
       complete_destination_path
       from exampath;

SELECT id,
       examplan_id,
       null::char(1) as accreditation_person,
       accreditation_date,
       k_accreditationtype_id,
       6 as sourcesystem
FROM accreditation 
 ;

   SELECT
   9015,
-1,
   ('' || id)::varchar(255) as id,
   substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),--astat,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3), --sprache,
null::char(1), --gueltig von
null::char(1), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
null::char(1) as lid,
null::char(255) as parent_lid,
sortorder
   FROM k_unitrelationtype
   union
     SELECT
   9016,
-1,
   ('' || id)::varchar(255) as id,
   substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),--astat,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3), --sprache,
null::char(1), --gueltig von
null::char(1), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
null::char(1) as lid,
null::char(255) as parent_lid,
sortorder
   FROM k_accreditationtype
   union
     SELECT
   9017,
-1,
   ('' || id)::varchar(255) as id,
   substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),--astat,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3), --sprache,
null::char(1), --gueltig von
null::char(1), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
null::char(1) as lid,
null::char(255) as parent_lid,
sortorder
   FROM k_additional_examtext_type
     union
     SELECT
   9018,
-1,
   ('' || id)::varchar(255) as id,
   substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),--astat,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3), --sprache,
null::char(1), --gueltig von
null::char(1), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
null::char(1) as lid,
null::char(255) as parent_lid,
sortorder
   FROM k_compulsory
   union
     SELECT
   9019,
-1,
   ('' || id)::varchar(255) as id,
   substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),--astat,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3), --sprache,
null::char(1), --gueltig von
null::char(1), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
null::char(1) as lid,
null::char(255) as parent_lid,
sortorder
   FROM k_internal_accreditationtype
   
  ;

    
select S.registrationnumber,
P.nameprefix,
P.surname,
P.namesuffix,
P.firstname,
A.eaddress ,
PA.attributevalue
from
k_notificationcategory N, address_k_notificationcategory AN, address A, student S, eaddresstype EAT, k_addresstag AT, person P 
left outer join personattribute PA on (P.id=PA.person_id
and current_date between PA.valid_from and PA.valid_to
and PA.personattributetype_id = ($PERSONATTRIBUTE_LM_EMAIL)
) 
where N.id=AN.k_notificationcategory_id
and A.id=AN.address_id
and P.id=A.person_id
and P.id=S.person_id
and EAT.id=A.eaddresstype_id
and EAT.hiskey_id=10 --Email
and A.k_addresstag_id=AT.id
and $LM_ADRESSTAG_MAIL 
and N.hiskey_id=2 --Studium - Korrespondenz 

    

SELECT id,
       unit_id,
       top_unit_id,
       6
FROM unit_top;

    

SELECT id,
       examplan_id,
       date_of_application,
       startdate,
       planned_end,
       real_end,
       SUBSTRING (title, 1, 255),
       commentary,
       6
FROM exam_written
where 
	'$LM_EXAM_WRITTEN'='true'
;

    

SELECT id,
       examplan_id,
       k_additional_examtext_type_id,
       additional_text,
       k_language_id,
       6
FROM additional_examtext
where 
	'$LM_PRUEFUNG_ZUSATZTEXT'='true'
;

drop table tmp_lm_course_of_study;