**************************************************************************************
* 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 SYS.SYSCOLUMNS WHERE tname = 'UddannelsesplanElement' AND cname = 'beregnetForventetSlutdato') THEN
ALTER TABLE UddannelsesplanElement ADD beregnetForventetSlutdato DATE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'UddannelsesplanElement' AND cname = 'forventetSlutdato') THEN
ALTER TABLE UddannelsesplanElement ADD forventetSlutdato DATE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'UddannelsesplanElement' AND cname = 'forventetSlutdatoSidstBeregnetDatotid') THEN
ALTER TABLE UddannelsesplanElement ADD forventetSlutdatoSidstBeregnetDatotid TIMESTAMP;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'UddannelsesplanElement_AUD' AND cname = 'beregnetForventetSlutdato') THEN
ALTER TABLE UddannelsesplanElement_AUD ADD beregnetForventetSlutdato DATE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'UddannelsesplanElement_AUD' AND cname = 'forventetSlutdato') THEN
ALTER TABLE UddannelsesplanElement_AUD ADD forventetSlutdato DATE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'UddannelsesplanElement_AUD' AND cname = 'forventetSlutdatoSidstBeregnetDatotid') THEN
ALTER TABLE UddannelsesplanElement_AUD ADD forventetSlutdatoSidstBeregnetDatotid TIMESTAMP;
END IF;


update uddannelsesplanElement e
set forventetSlutdato =
(select fk.forventetSlutDato from uddannelsesplanElement element
    left join undervisning udd on (undervisning_id = udd.id)
    left join uddannelsesplan plan
    left join  fagKursist fk on (fk.personnummer = plan.personnummer and not fk.fagKode is null and fk.fagKode = udd.uddannelse || udd.modul || '0' )
where element.id = e.id  and not fk.fagKode is null)
where e.type ='DU';
commit;


DROP TABLE IF EXISTS KursistFravaerWebHoldModulSum;
DROP TABLE IF EXISTS KursistFravaerWebHoldModulSumFravaer;


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

IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'CensorLogin' AND cname = 'censorbrevDokument_id') THEN
  IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'CensorLogin' and role = 'censorLogin' ) THEN
      ALTER TABLE CensorLogin DROP FOREIGN KEY censorLogin;
  END IF;
  IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'CensorLogin' and role = 'dokument' ) THEN
      ALTER TABLE CensorLogin DROP FOREIGN KEY dokument;
  END IF;
  ALTER TABLE CensorLogin DROP censorbrevDokument_id;
END IF;


IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Optagelsesforloeb' AND cname = 'fuldAfgangseksamen') THEN
	ALTER TABLE Optagelsesforloeb ADD fuldAfgangseksamen BIT DEFAULT 0;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Optagelsesforloeb' AND cname = 'studievalgsportfolioAfleveret') THEN
	ALTER TABLE Optagelsesforloeb ADD studievalgsportfolioAfleveret BIT DEFAULT 0;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Optagelsesforloeb' AND cname = 'motiveretAnsoegningModtaget') THEN
	ALTER TABLE Optagelsesforloeb ADD motiveretAnsoegningModtaget DATE;
END IF;


delete QRTZ_CRON_TRIGGERS where trigger_name like 'BEREGN_FORVENTET_SLUT_FOR_%BNE_ID_HENVISNINGER%';
delete from QRTZ_SIMPLE_TRIGGERS where trigger_name like 'BEREGN_FORVENTET_SLUT_FOR_%BNE_ID_HENVISNINGER%';
delete QRTZ_TRIGGERS where trigger_name like 'BEREGN_FORVENTET_SLUT_FOR_%BNE_ID_HENVISNINGER%';
delete from QRTZ_JOB_DETAILS where job_name like 'BEREGN_FORVENTET_SLUT_FOR_%BNE_ID_HENVISNINGER%';
commit;

IF EXISTS(SELECT 1 FROM LudusWebProperty WHERE propertyName = 'GØREMÅL_KONFIGURATION' and value  like '%NIVEAU_FOR_AD_KURSIST%') THEN
BEGIN
DECLARE @originalConfig LONG VARCHAR;
DECLARE @partBeforeADConfigration LONG VARCHAR;
DECLARE @lengthPartBeforeADConfigration INTEGER;
DECLARE @partStartWithADConfigration LONG VARCHAR;
DECLARE @lengthADConfigration INTEGER;
DECLARE @partAfterADConfigration LONG VARCHAR;
DECLARE @newConfiguration LONG VARCHAR;
SET @originalConfig = (SELECT value FROM LudusWebProperty WHERE propertyName = 'GØREMÅL_KONFIGURATION');
SET @partBeforeADConfigration = (SELECT SUBSTRING(@originalConfig, 0, CHARINDEX('{"type":"NIVEAU_FOR_AD_KURSIST"', @originalConfig)));
SET @lengthPartBeforeADConfigration = (SELECT LEN(@partBeforeADConfigration));
SET @partStartWithADConfigration = (SELECT SUBSTRING(@originalConfig, @lengthPartBeforeADConfigration + 1));
SET @lengthADConfigration = (SELECT CHARINDEX('}', @partStartWithADConfigration));
SET @partAfterADConfigration = (SELECT SUBSTRING(@partStartWithADConfigration, @lengthADConfigration + 1));
SET @newConfiguration = (SELECT @partBeforeADConfigration || @partAfterADConfigration);
SET @newConfiguration = (SELECT (replace(@newConfiguration,',,',',')));
SET @newConfiguration = (SELECT (replace(@newConfiguration,',]',']')));
SET @newConfiguration = (SELECT (replace(@newConfiguration,'[,','[')));
UPDATE LudusWebProperty SET value = @newConfiguration WHERE propertyName = 'GØREMÅL_KONFIGURATION';
END;
COMMIT;
END IF;

delete from GoeremaalLaererMedarbejder where type = 'NIVEAU_FOR_AD_KURSIST'; commit;


DROP TABLE IF EXISTS EudOvergangskrav;
CREATE TABLE EudOvergangskrav (
	id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	version INTEGER NOT NULL,
	modified DATETIME NOT NULL DEFAULT TIMESTAMP,
	oprettet DATE NOT NULL,
	personnummer CHAR(11) NOT NULL,
	FOREIGN KEY kursist(personnummer) REFERENCES Kursist(personnummer) ON UPDATE CASCADE ON DELETE CASCADE,
	xprsFag_id INTEGER NOT NULL,
	FOREIGN KEY xprsFag(xprsFag_id) REFERENCES XPRSFag(id) ON UPDATE CASCADE ON DELETE CASCADE,
	
	PRIMARY KEY (id));


IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBrugerIndstillinger' AND cname = 'visUgetimerPr') THEN
ALTER TABLE WebBrugerIndstillinger ADD visUgetimerPr DATE NULL;
UPDATE WebBrugerIndstillinger wb
SET wb.visUgeTimerPr = (select b.visUgeTimerDato from BrugerIndstillinger b 
                        left join WebBruger w on b.brugerId = w.LudusBrugerId
                        where wb.bruger_id = w.id);
COMMIT;
END IF;



IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'systemIndstillinger' AND cname = 'foretagModulus11Kontrol') THEN
  UPDATE systemindstillinger SET foretagModulus11Kontrol = ' ' WHERE foretagModulus11Kontrol = 'X'; COMMIT;
END IF;


DROP TABLE IF EXISTS EudOvergangskrav;
CREATE TABLE EudOvergangskrav (
	id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	version INTEGER NOT NULL,
	modified DATETIME NOT NULL DEFAULT TIMESTAMP,
	oprettet DATE NOT NULL,
	xprsFag_id INTEGER NOT NULL,
	FOREIGN KEY xprsFag(xprsFag_id) REFERENCES XPRSFag(id) ON UPDATE CASCADE ON DELETE CASCADE,
	xprsUddannelse_id INTEGER NOT NULL,
	FOREIGN KEY xprsUddannelse(xprsUddannelse_id) REFERENCES XPRSUddannelse(id) ON UPDATE CASCADE ON DELETE CASCADE,
	personnummer CHAR(11) NOT NULL,
	FOREIGN KEY kursist(personnummer) REFERENCES Kursist(personnummer) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT un_overgangskrav UNIQUE (personnummer, xprsFag_id, xprsUddannelse_id),
	
	PRIMARY KEY (id));


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

DROP TABLE IF EXISTS FtuAnsoegningZip;

COMMIT;QUIT;