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

if not exists (select 1 from land where landkode = 'MN') then
  update land set landkode = 'MN' where tekst = 'Mongoliet'; commit 
end if;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Postering' AND cname = 'oprettetDatoTid') THEN
ALTER TABLE Postering ADD oprettetDatoTid DATETIME;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Postering' AND cname = 'oprettetAf') THEN
ALTER TABLE Postering ADD oprettetAf varchar(10);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Postering' AND cname = 'handling') THEN
ALTER TABLE Postering ADD handling varchar(50);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBrugerIndstillinger' AND cname = 'posteringTilNaesteKursusaar') THEN
ALTER TABLE WebBrugerIndstillinger ADD posteringTilNaesteKursusaar char(4);
END IF;
UPDATE WebBrugerIndstillinger wbi
set posteringTilNaesteKursusaar = (select posteringTilNaesteKursusaar from BrugerIndstillinger bi where bi.brugerId = (select LudusBrugerId from webBruger wb where wb.id = wbi.bruger_id))
where (select posteringTilNaesteKursusaar from BrugerIndstillinger bi where bi.brugerId = (select LudusBrugerId from webBruger wb where wb.id = wbi.bruger_id)) is not NULL and
        (select posteringTilNaesteKursusaar from BrugerIndstillinger bi where bi.brugerId = (select LudusBrugerId from webBruger wb where wb.id = wbi.bruger_id)) <> '';
commit;

IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Uddannelsesoenske' AND cname = 'fordelingstype')
THEN
	ALTER TABLE Uddannelsesoenske DROP fordelingstype;
END IF;

UPDATE
  LUDUSWebProperty p
SET
  p.value = replace(p.value, '"webserviceURL":"https://integrationsplatformen.dk/services/Optagelsedk/ftuAnsoegning/v1.5"}', '"webserviceURL":"https://integrationsplatformen.dk/services/Optagelsedk/ftuAnsoegning/v1.6"}' )
WHERE
  p.propertyName like 'OPTAGELSE_KONFIGURATION';
 
COMMIT;


IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'EksamenModul' AND cname = 'karakterOffentliggoerelseDatoTid') THEN
    ALTER TABLE EksamenModul ADD karakterOffentliggoerelseDatoTid DATETIME;
END IF;


-- Scoreark til brug for Modultestversion 8
IF NOT EXISTS (SELECT 1 FROM ScorearkSkabelon WHERE undervisning_id in (select id from undervisning where modul = 4 and uddannelse = 2) and fraModultestVersion = 8 ) THEN
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 4 and uddannelse = 2), 1, 8,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 4 and uddannelse = 2), 2, 8,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 4 and uddannelse = 2), 3, 8,  getDate(),'System', 'X' );
END IF;
IF NOT EXISTS (SELECT 1 FROM ScorearkSkabelon WHERE undervisning_id in (select id from undervisning where modul = 5 and uddannelse = 2) and fraModultestVersion = 8 ) THEN
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 5 and uddannelse = 2), 1, 8,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 5 and uddannelse = 2), 2, 8,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 5 and uddannelse = 2), 3, 8,  getDate(),'System', 'X' );
END IF;
COMMIT;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebModultestForsoeg' AND cname = 'dispVedDysleksi') THEN
  ALTER TABLE WebModultestForsoeg ADD dispVedDysleksi bit default 0;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'UddannelsesplanElement' AND cname = 'dispVedDysleksi') THEN
  ALTER TABLE UddannelsesplanElement ADD dispVedDysleksi bit default 0;
END IF;

-- LUD-73506 Nye FVU fagkoder
IF ((select count(*) from lokalfag where fagkode = 'ENG11') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('ENG11','FVU',' ',0.00,0.00,'Engelsk trin 1','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Engelsk trin 1',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,22066,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;

IF ((select count(*) from lokalfag where fagkode = 'ENG12') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('ENG12','FVU',' ',0.00,0.00,'Engelsk trin 2','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Engelsk trin 2',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,22067,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;

IF ((select count(*) from lokalfag where fagkode = 'ENG13') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('ENG13','FVU',' ',0.00,0.00,'Engelsk trin 3','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Engelsk trin 3',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,22068,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;

IF ((select count(*) from lokalfag where fagkode = 'ENG14') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('ENG14','FVU',' ',0.00,0.00,'Engelsk trin 4','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Engelsk trin 4',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,22069,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;

IF ((select count(*) from lokalfag where fagkode = 'DIG11') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('DIG11','FVU',' ',0.00,0.00,'Digital trin 1','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Digital trin 1',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,20429,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;

IF ((select count(*) from lokalfag where fagkode = 'DIG12') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('DIG12','FVU',' ',0.00,0.00,'Digital trin 2','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Digital trin 2',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,20430,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;

IF ((select count(*) from lokalfag where fagkode = 'DIG13') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('DIG13','FVU',' ',0.00,0.00,'Digital trin 3','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Digital trin 3',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,22072,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;

IF ((select count(*) from lokalfag where fagkode = 'DIG14') = 0) THEN
INSERT INTO "DBA"."LokalFag" ("fagKode","fagType","udgaaet","apparatOpstilling","konferenceTimer","navn","niveau","proeveformMundtlig","proeveformProjekt","proeveformSkriftlig","retteTillaeg","synonym","tidsforbrugProjekt","timetal","proeveformAndet","proeveformStrSkrOpg","tidsforbrugStrSkrOpg","fag","deltagerBetaling","deltagerBetalingSS","timerTilUdarbejdelseAfProeveSaet","deltagerBetalingAabentVUC","modultakst","proeveTilFagKode","modultakstA","modultakstB","modultakstC","modultakstD","modultakstE","modultakstF","skriftligAarskarakter","mundtligAarskarakter","antalEksaminationerPrDag","mundtligForberedelsestid","mundtligEksaminationstid","skriftligEksaminationstid","gruppeEksamen","eksamenLokaleForbrug","gruppeForberedelse","eksternForberedelseTid","xprsFagKode","xprsNiveau","tidsforbrugSkriftlig","tidsforbrugAndet","modultakstID","lvuGrundtakstEksamination","lvuFaktorTilEksamination","lvuGrundtakstCensur","lvuFaktorTilCensur","lvuAntalKursisterPrGruppe","timetalAften","respekterGruppeForberedelseVedEksamensplanlaegning","skriftligCensortid","samletVurdering","kursisttid","antalNiveauer","harNiveauPaaMindst100Timer") 
VALUES('DIG14','FVU',' ',0.00,0.00,'Digital trin 4','Ingen',' ',' ',' ',0.00,NULL,0.0000,0.00,' ',' ',0.0000,'Digital trin 4',NULL,NULL,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,' ',0,' ',0,22080,'-',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.00,' ',0,' ',0.00,NULL,'Nej');
END IF;
COMMIT;

DROP TABLE IF EXISTS EksterntBevisLokalFag
CREATE TABLE EksterntBevisLokalFag (
	id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	version INTEGER NULL,
	eksterntBevis_id INTEGER NOT NULL,
	FOREIGN KEY eksterntBevis(eksterntBevis_id) REFERENCES EksterntBevis(id) ON UPDATE CASCADE ON DELETE CASCADE,
	fagKode VARCHAR(10) NOT NULL,
	FOREIGN KEY lokalFag(fagKode) REFERENCES LokalFag(fagKode) ON UPDATE CASCADE ON DELETE CASCADE,
	bestaaet BIT DEFAULT 0,
	PRIMARY KEY (id));

;COMMIT;QUIT;