**************************************************************************************
* Bemærk at denne fil kun er til orientering om databaseændringer til denne release. *
* Filen MÅ IKKE afvikles!                                                            *
**************************************************************************************

-- LUD-71847 - default værdier på eksamenmoduler manglede ved web oprettelse
update eksamenmodul
set anvendOplysningerFraLokalFag = 'X'
where anvendOplysningerFraLokalFag is null;
commit;
update eksamenmodul
set lokalaftaleProevetimerLVU = '0'
where lokalaftaleProevetimerLVU is null;
commit;
update eksamenmodul
set alternativPrisBeloeb = '0'
where alternativPrisBeloeb is null;
commit;

-- LUD-71940/LUD-72070 - fejlkonverteret adm medarbejdere til lærer på tildelt

update TildeltWebBrugerKursistJournal x
set x.webBrugerId =
(
select medarbWb.id from Webbruger medarbWb
where medarbWb.brugertype = 'ADMINISTRATION' and medarbWb.medarbejderpersonnummer = (select laererPersonnummer from webbruger lwb where lwb.brugertype = 'LÆRER'  and x.webBrugerId = lwb.id )
) 
where  x.webBrugerId in (select id from webbruger where brugertype = 'LÆRER')
and exists (select 1 from Webbruger medarbWb where medarbWb.brugertype = 'ADMINISTRATION' and medarbWb.medarbejderpersonnummer = (select laererPersonnummer from webbruger lwb where lwb.brugertype = 'LÆRER'  and x.webBrugerId = lwb.id ))
-- the webbruger is not already tildelt
and not exists (select 1 from TildeltWebBrugerKursistJournal y where y.identifikation = x.identifikation and y.id <> x.id and y.personnummer = x.personnummer 
   and  y.webBrugerId in (select medarbWb.id from Webbruger medarbWb where medarbWb.brugertype = 'ADMINISTRATION' and medarbWb.medarbejderpersonnummer = 
   (select laererPersonnummer from webbruger lwb where lwb.brugertype = 'LÆRER'  and x.webBrugerId = lwb.id )));
commit;

delete from TildeltWebBrugerKursistJournal x
where  x.webBrugerId in (select id from webbruger where brugertype = 'LÆRER')
and 
(not exists (select 1 from Webbruger medarbWb where medarbWb.brugertype = 'ADMINISTRATION' and medarbWb.medarbejderpersonnummer = (select laererPersonnummer from webbruger lwb where lwb.brugertype = 'LÆRER'  and x.webBrugerId = lwb.id ))
or exists (select 1 from TildeltWebBrugerKursistJournal y where y.identifikation = x.identifikation and y.id <> x.id and y.personnummer = x.personnummer 
   and  y.webBrugerId in (select medarbWb.id from Webbruger medarbWb where medarbWb.brugertype = 'ADMINISTRATION' and medarbWb.medarbejderpersonnummer = 
   (select laererPersonnummer from webbruger lwb where lwb.brugertype = 'LÆRER'  and x.webBrugerId = lwb.id )))
);
commit;

-- LUD-72006 - default værdier på undervisningsmoduler manglede ved web kopiering
update undervisningmodul set loebendeOptag = ' ' where loebendeOptag is null;commit;
update undervisningmodul set alternativPris = ' ' where alternativPris is null;commit;

/* Denne databaseopdatering sletter schedulerede jobs til afhenting af ansøgninger fra optagelse.dk 
 *
 * Husk at skolerne efterfølende skal schedulere deres kørsler igen!
 */

/* Slet schedulerede kørsler (HENT_ANSØGNINGER_NG) */
DELETE FROM QRTZ_CRON_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_NG%';
DELETE FROM QRTZ_SIMPLE_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_NG%';
DELETE FROM QRTZ_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_NG%';
DELETE FROM QRTZ_JOB_DETAILS WHERE job_name LIKE 'HENT_ANS%GNINGER_NG%';


/* Slet schedulerede kørsler (HENT_ANSØGNINGER_EUD) */
DELETE FROM QRTZ_CRON_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_EUD%';
DELETE FROM QRTZ_SIMPLE_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_EUD%';
DELETE FROM QRTZ_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_EUD%';
DELETE FROM QRTZ_JOB_DETAILS WHERE job_name LIKE 'HENT_ANS%GNINGER_EUD%';

/* Slet schedulerede kørsler (HENT_ANSØGNINGER_GYM) */
DELETE FROM QRTZ_CRON_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_GYM%';
DELETE FROM QRTZ_SIMPLE_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_GYM%';
DELETE FROM QRTZ_TRIGGERS WHERE trigger_name LIKE 'HENT_ANS%GNINGER_GYM%';
DELETE FROM QRTZ_JOB_DETAILS WHERE job_name LIKE 'HENT_ANS%GNINGER_GYM%';

/* Slet schedulerede kørsler (IMPORTER_RETSKRAV) */
DELETE FROM QRTZ_CRON_TRIGGERS WHERE trigger_name LIKE 'IMPORTER_RETSKRAV%';
DELETE FROM QRTZ_SIMPLE_TRIGGERS WHERE trigger_name LIKE 'IMPORTER_RETSKRAV%';
DELETE FROM QRTZ_TRIGGERS WHERE trigger_name LIKE 'IMPORTER_RETSKRAV%';
DELETE FROM QRTZ_JOB_DETAILS WHERE job_name LIKE 'IMPORTER_RETSKRAV%';

COMMIT;

IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'AUBSkoleopholdIndberetning' AND cname = 'tekniskFejl') THEN
  ALTER TABLE AUBSkoleopholdIndberetning modify tekniskFejl varchar (2000);
END IF;
COMMIT;
QUIT;