Virta_Latausraportti - Puutetarkistukset

Tällä sivulla ylläpidetään tietovarannossa tehtävien tarkistusten listaa. Tarkistusten tulos ilmenee siis Virta_Latausraportilla PUUTTEITA-listalla.

Puuteryhmän toimintaa (ym.) helpottamaan sovittu (!Muutos-2024-05-13): Kun puutteelle tulee muutoksia (itse sääntöön tai dokumentaation tänne), niin tehdään puutteelle tagi muotoa"!Muutos-<yyyy-mm-dd>".

Vuonna 2023 lisätään monia uusia puutetarkistuksia. Samassa yhteydessä havaittuun puutteeseen on lisätty kaksi uutta tietoa:

virhTiedPvm : Virheellisen tiedon päivämäärä, esimerkiksi opintosuorituksen päivämäärä (jos virhe liittyy opintosuoritukseen). "Tiedon alkamispäivämäärä suurempi kuin päättymispäivämäärä" -tyyppisissä virheissä tähän asetetaan aina 31-12-9999, jotta virhe pysyy akuuttina siihen asti kun se on korjattu; poikkeus (tehty 11.2023): Jos opiskeluoikeuden tapauksessa opiskeluoikeudella on sen epätavallista päättymistä osoittava tila (passivoitu, luopunut), joka alkaa ennen (<=) opiskeluoikeuden alkua, niin asiantilasta ei tehdä puutetta.

opisOikAktPaatPvm: Opiskeluoikeuden aktiivisuuden päättymispäivämäärä. Jos virhe ei liity opiskeluoikeuteen liittyvään tietoon, niin silloin tämän päättelyyn käytetään opiskelijan kaikkia opiskeluoikeuksia. Tieto päätellään opiskeluoikeuden tiloista, siitä tilasta, jonka alkamispäivämäärä on suurin: Jos se tila on aktiivinen tai optio, niin otetaan tilan päättymispäivämäärä (sen puuttuessa 31-12-9999), muussa tapauksessa (valmistunut, passivoitu, luopunut, päättynyt) tilan alkamispäivämäärää edeltävä päivämäärä. !Muutos-2024-05-13: Puuteryhmässä on ollut esillä toivomus (puutekoodista riippuen), että puutteesta ei ilmoitettaisi, jos opiskeluoikeus/opiskelija ei ole aktiivinen - no, ei-aktiiviset saa pois kun suodattaa puutteiden tiedostosta pois ne rivit, joissa tässä tiedossa oleva pvm on pienempi kuin kuluva pvm (tai jokin muu pvm, johon haluaa verrata).

Mitä kauempana historiassa nämä päivämäärät ovat, sitä vähemmän (harvemmassa tapauksessa) ne enää vaikuttavat - joten virheiden korjaaminen kannattaa aloittaa niistä virheistä, joissa jompi kumpi näistä on joko tulevaisuudessa tai lähihistoriassa.

Marraskuussa 2023 puutteisiin on lisätty tieto onVanhempi (kyllä/ei). Puutteiden etsimisen jälkeen sovellus ottaa suuremman päivämääristä virhTiedPvm ja opisOikAktPaatPvm ja vertaa sen vuotta kuluvaan vuoteen; jos erotus on enemmän kuin viisi vuotta (esim. vuonna 2023 vertailuvuosi < 2018), niin silloin puute päätelllään vanhemmaksi puutteeksi (onVanhempi=kyllä). Näitä vanhempia puutteita ei ilmoiteta oppilaitokselle. Koska puutteen "iän" laskemisessa ei siis käytetä opiskeluoikeuden alkamispäivämäärää, niin "vanhuusrajana" voisi ehkä pitää jotain viittä pienempää vuotta.

4012 Opintosuoritus (suorituspäivämäärä) ajalla, jolloin opiskeluoikeudella ei ole opiskeluoikeusjaksoa

Puutetarkistuksen versiossa 20231215: 

1) Puutekoodin 4012 perässä on tavuviiva ja nelimerkkinen kirjain/numero-yhdistelmä, missä on lisätietoa tapauksesta. Vastaavat lisätiedot ilmenevät puutteen lisätiedon alusta.

2) Jos lisätiedossa on koodi [V1], niin puute katsotaan vanhaksi (→ ei ilmoiteta Virrasta korkeakoululle) riippumatta opiskeluoikeuden jatkumisesta, jos suoritus-/hyväksilukupäivämäärän vuosi on kuluvasta vuodesta yli kuuden vuoden päässä (esim. vuotta 2023 elettäessä se olisi < 2018).

Puutetarkistuksen versiossa 20240105: 

SQL-koodi

 -- OPISKELIJAT
-- Opiskelijalla EI OLE oikeutta, lkilmoa tai suorituksia
if @tarkistuskoodi=1001 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1001)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskelijalla ei ole ollenkaan opiskeluoikeutta, lukukausi-ilmoittautumista, opintosuoritusta tai liikkuvuusjaksoa'' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, a.avain
, coalesce(a.avain,''#Puuttuu#'') as opiskelijaAvain
, null as opiskeluoikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskelija AS a
WHERE 1=1
AND a.ID NOT IN (
SELECT OpiskelijaID FROM Opiskeluoikeus
)
AND a.ID NOT IN (
SELECT OpiskelijaID FROM Opintosuoritus
)
AND a.ID NOT IN (
SELECT OpiskelijaID FROM Lukukausi_ilmoittautuminen
)
AND a.ID NOT IN (
SELECT OpiskelijaID FROM Liikkuvuusjakso
)
'+CHAR(13)
end;
-- Opiskelijalla EI OLE hetu/oppijanro
if @tarkistuskoodi=1002 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1002)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskelijalla ei ole virallista suomalaista hetua tai kansallista oppijanumeroa'' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, a.avain
, coalesce(a.avain,''#Puuttuu#'') as opiskelijaAvain
, null as opiskeluoikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskelija AS a
WHERE 1=1
AND a.HenkiloID IN (
SELECT ID FROM Henkilo
WHERE Henkilotunnus IS NULL
OR Henkilotunnus NOT LIKE ''______%____''
)
AND a.Kansallinen_op_id IS NULL
'+char(13)
end;
-- Opiskelijan sukupuolitiedoissa virhe
-- 2021-07-19 CSCTIE-85 Opiskelijan sukupuolitieto ei täsmää hetun kanssa" ja sukupuoli on 9 eli tuntematon
if @tarkistuskoodi=1003 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1003)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskelijan sukupuolitieto ei täsmää hetun kanssa'' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, a.avain
, coalesce(a.avain,''#Puuttuu#'') as opiskelijaAvain
, null as opiskeluoikeusAvain
--,''Koodi '' +s.koodi + '' hetulla '' + h.Henkilotunnus AS Lisatieto
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskelija AS a
INNER JOIN Henkilo h ON h.ID=a.HenkiloID
INNER JOIN Sukupuoli s ON s.id=h.SukupuoliID
where 1=1
and len(h.Henkilotunnus)=11 -- on hetu
AND s.koodi <> ''9'' -- ei ole tuntematon 2021-07-19
and h.SukupuoliID is not null -- on sp

and(
( --nainen
cast(substring(h.Henkilotunnus,10,1) as int)%2=0
and h.SukupuoliID <> (select id from Sukupuoli where Koodi=''2'')
)
or
( --mies
cast(substring(h.Henkilotunnus,10,1) as int)%2=1
and h.SukupuoliID <> (select id from Sukupuoli where Koodi=''1'')
)
)
'+char(13)
end;

-- 2019-06-04 alkaa

-- Hetuttoman opiskelijan välimerkki virheellinen. Jos 2000 luvulla syntynyt tulee olla A ei -
-- CSCTIE-600 välimerkit: 1900: -|Y|X|W|V|U, 2000: A|B|C|D|E|F
if @tarkistuskoodi=1004 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1004)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Hetuton - Väärä välimerkki lopussa. Jos 2000 luvulla syntynyt tulee olla A|B|C|D|E|F ei -|Y|X|W|V|U'' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, a.avain
, coalesce(a.avain,''#Puuttuu#'') as opiskelijaAvain
, null as opiskeluoikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskelija AS a
INNER JOIN Henkilo h ON h.ID=a.HenkiloID
where 1=1
and len(h.Henkilotunnus)<11 -- ei hetua, alle 11 merkkiä
AND SUBSTRING(h.Henkilotunnus,7,1) not in (''A'', ''B'', ''C'', ''D'', ''E'', ''F'')
AND CAST(SUBSTRING(h.Henkilotunnus,5,2) AS int) < 10
AND h.Henkilotunnus <> ''010100-''
AND COALESCE(a.Kirjoihintulopaivamaara,''1989-01-01'') >= ''1990-01-01''

'+char(13)
end;


-- 2019-06-04 loppu

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Henkilön pätevyyden päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=1005 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1005)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''henkilo.avain#patevyys.koodi#henkilon_patevyys.alkamispaivamaara'' as avainsarake
, concat_ws(''#'', henk.avain, patKood.koodi, henkPat.alkamispaivamaara)
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, (case when suor.avain is null then ''opintosuoritus.avain=null'' else ''opintosuoritus.avain='' + suor.avain end) as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from henkilon_patevyys as henkPat
join patevyys as patKood on patKood.id = henkPat.patevyysId
join henkilo as henk on henk.id = henkPat.henkiloId
left outer join opiskelija as opis on opis.id = henkPat.henkiloId
left outer join opintosuoritus as suor on suor.id = henkPat.opintosuoritusId
where henkPat.paattymispaivamaara is not null and henkPat.paattymispaivamaara < henkPat.alkamispaivamaara
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Oppijanumero on väärän muotoinen';
if @tarkistuskoodi=1006 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1006)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, opis.Avain
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, concat(''Oppijanumero='', (case len(trim(kansallinen_op_id)) when 11 then ''hetu?'' else concat(''"'', kansallinen_op_id, ''"'') end), '', len='', len(kansallinen_op_id), ''; spaces='', (case when len(kansallinen_op_id) != len(trim(kansallinen_op_id)) then ''true'' else ''false'' end)) as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from opiskelija as opis
join henkilo as henk on henk.id = opis.henkiloId
where kansallinen_op_id is not null
and (len(replace(translate(kansallinen_op_id,''0123456789.'',''___________''),''_'','''')) > 0
or
substring(kansallinen_op_id,1,15) != ''1.2.246.562.24.''
or
len(kansallinen_op_id) != 26)
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Oppijanumero oltava tutkinto-opiskelijoilla vuodesta 2016 alkaen';
if @tarkistuskoodi=1007 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1007)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, opis.Avain
, opis.avain as opiskelijaAvain
, max(oik.avain) as opiskeluOikeusAvain
, concat(''MaksOpisOikPaatPvm='', cast(max(isnull(oik.paattymispaivamaara,''9999-12-31'')) as nvarchar)) as lisaTieto
, max(isnull(oik.paattymispaivamaara,''9999-12-31'')) as virhTiedPvm
from opiskelija as opis
join opiskeluoikeus as oik on oik.opiskelijaId = opis.id
join opiskeluoikeuden_tyyppi as ooTyyp on ooTyyp.id = oik.op_oikeuden_tyyppiId
join henkilo as henk on henk.id = opis.henkiloId
where kansallinen_op_id is null and ooTyyp.koodi in (''1'',''2'',''3'',''4'')
and oik.alkamispaivamaara >= ''2016-01-01''
group by opis.avain
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Sama oppijanumero useammalla eri henkilöön liittyvällä opiskelijalla';
if @tarkistuskoodi=1008 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1008)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, opis.Avain
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, opis.kansallinen_op_id as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from opiskelija as opis
join henkilo as henk on henk.id = opis.henkiloId
join (select tupOpis.kansallinen_op_id from opiskelija as tupOpis group by tupOpis.kansallinen_op_id having min(henkiloId) != max(henkiloId)) as tuplat
on tuplat.kansallinen_op_id = opis.kansallinen_op_id
where opis.kansallinen_op_id is not null
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Puuttuu joko suku- tai etunimi';
if @tarkistuskoodi=1009 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1009)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, opis.Avain
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, concat_ws('','' ,(case when henk.sukunimi is null then ''sukunimi'' else null end)
,(case when henk.etunimet is null then ''etunimet'' else null end)
,(case when charindex('' '', concat(henk.sukunimi, henk.etunimet)) < 1 then ''vain yksi sana'' else null end))
as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from opiskelija as opis
join henkilo as henk on henk.id = opis.henkiloId
where henk.sukunimi is null or henk.etunimet is null
'+CHAR(13)
end;

-- 1010 Sukunimessä tai etunimissa on muita merkkejä kuin kirjaimia, välilyöntejä ja tavuviivoja.
set @t_kuvaus = N'Sukunimessä tai etunimissä on muita merkkejä kuin kirjaimia, välilyöntejä, tavuviivoja, heittomerkkejä, pilkku, ...';
if @tarkistuskoodi=1010 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1010)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, opis.Avain
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, yhteiset.Tarkistukset.Puute_MerkitNimessa(concat_ws('' '', henk.etunimet, henk.sukunimi)) as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from opiskelija as opis
join henkilo as henk on henk.id = opis.henkiloId
where yhteiset.Tarkistukset.Puute_MerkitNimessa(concat_ws('' '', henk.etunimet, henk.sukunimi)) is not null
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Henkilötunnuksen seitsemäs merkki on ristiriidassa syntymäajan vuosisataan nähden (1800:+, 1900: -|Y|X|W|V|U, 2000: A|B|C|D|E|F)';
if @tarkistuskoodi=1011 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1011)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, opis.Avain
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, concat(''Syntymäaika='', syntymaaika, ''; henkilötunnus(5-7)='', substring(henkilotunnus,5,3)) as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from henkilo as hlo
join opiskelija as opis on opis.henkiloId = hlo.id
where (henkilotunnus is not null and len(henkilotunnus) >= 7)
and ((syntymaaika is not null and syntymaaika < ''1900-01-01'' and substring(henkilotunnus,7,1) != ''+'')
or (syntymaaika is not null and syntymaaika >= ''1900-01-01'' and syntymaaika < ''2000-01-01'' and substring(henkilotunnus,7,1) not in (''-'',''Y'', ''X'', ''W'', ''V'', ''U''))
or (syntymaaika is not null and syntymaaika >= ''2000-01-01'' and substring(henkilotunnus,7,1) not in (''A'',''B'', ''C'', ''D'', ''E'', ''F''))
or (syntymaaika is null and substring(henkilotunnus,5,2) >= ''10'' and substring(henkilotunnus,7,1) not in (''-'',''Y'', ''X'', ''W'', ''V'', ''U''))
or (syntymaaika is null and substring(henkilotunnus,5,2) < ''10'' and substring(henkilotunnus,7,1) not in (''A'',''B'', ''C'', ''D'', ''E'', ''F''))
)
'+CHAR(13)
end;

set @t_kuvaus = N'Hetusta laskettava ikä ei ole välillä 15-100 vuotta.';
if @tarkistuskoodi=1012 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,1012)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, opis.Avain
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, concat(''Syntymäaika='', syntAika, ''; henkilötunnus(1-7)='', substring(henkilotunnus,1,7)) as lisaTieto
, (case when dateAdd(year, 110, syntAika) < cast(getdate() as date) then syntAika
when dateAdd(year, 15, syntAika) > cast(getdate() as date) then syntAika else null end) as virhTiedPvm
from henkilo as hlo
join opiskelija as opis on opis.henkiloId = hlo.id
join (
select id,
cast(concat(case
--when substring(henkilotunnus,1,7) in (''221123-'') then ''20'' --testitapaus TAU
when substring(henkilotunnus,7,1) in (''+'') then ''18''
when substring(henkilotunnus,7,1) in (''-'', ''Y'', ''X'', ''W'', ''V'', ''U'') then ''19''
when substring(henkilotunnus,7,1) in (''A'', ''B'', ''C'', ''D'', ''E'', ''F'') then ''20'' else ''21'' end,
substring(henkilotunnus,5,2),''-'',substring(henkilotunnus,3,2),''-'',substring(henkilotunnus,1,2)) as date) as syntAika
from henkilo
where henkilotunnus != ''010100-''
) as laskenta on laskenta.id = hlo.id
where henkilotunnus != ''010100-''
and (case when dateAdd(year, 110, syntAika) < cast(getdate() as date) then syntAika
when dateAdd(year, 15, syntAika) > cast(getdate() as date) then syntAika else null end) is not null
order by syntAika
'+CHAR(13)
end;

-- OPISKELUOIKEUDET
-- Tila: Muodostavat eheän aikajakson
if @tarkistuskoodi=2001 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2001)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskeluoikeuden tila ei muodosta eheää aikajaksoa'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce(a.Avain,''#Puuttuu#'') AS opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskeluoikeus AS a
INNER JOIN Op_oikeuteen_liittyva_tila AS b ON b.OpiskeluoikeusID=a.ID
WHERE 1=1
-- On usea
AND b.OpiskeluoikeusID IN (
SELECT c.OpiskeluoikeusID
FROM Op_oikeuteen_liittyva_tila AS c
GROUP BY c.OpiskeluoikeusID HAVING COUNT(*)>1
)
-- Ei viimeinen
AND b.Alkamispaivamaara < (
SELECT MAX(c.Alkamispaivamaara)
FROM Op_oikeuteen_liittyva_tila AS c
WHERE c.OpiskeluoikeusID=b.OpiskeluoikeusID
GROUP BY c.OpiskeluoikeusID
)
-- Ei seuraajaa
-- tai Paattymispaivamaaraa ei asetettu
AND(
b.Paattymispaivamaara NOT IN (
SELECT DATEADD(day,-1,c.Alkamispaivamaara)
FROM Op_oikeuteen_liittyva_tila AS c
WHERE c.OpiskeluoikeusID=b.OpiskeluoikeusID
)
OR b.Paattymispaivamaara IS NULL
)
'+CHAR(13)
end;
-- Jakso: Muodostavat eheän aikajakson
if @tarkistuskoodi=2002 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2002)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskeluoikeuden opiskeluoikeusjakso ei muodosta eheää aikajaksoa'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce(a.Avain,''#Puuttuu#'') AS opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskeluoikeus AS a
INNER JOIN Opiskeluoikeusjakso AS b ON b.OpiskeluoikeusID=a.ID
WHERE 1=1
-- On usea
AND b.OpiskeluoikeusID IN (
SELECT c.OpiskeluoikeusID
FROM Opiskeluoikeusjakso AS c
GROUP BY c.OpiskeluoikeusID HAVING COUNT(*)>1
)
-- Ei viimeinen
AND b.Alkamispaivamaara < (
SELECT MAX(c.Alkamispaivamaara)
FROM Opiskeluoikeusjakso AS c
WHERE c.OpiskeluoikeusID=b.OpiskeluoikeusID
GROUP BY c.OpiskeluoikeusID
)
-- Ei seuraajaa
-- tai Paattymispaivamaaraa ei asetettu
AND(
b.Paattymispaivamaara NOT IN (
SELECT DATEADD(day,-1,c.Alkamispaivamaara)
FROM Opiskeluoikeusjakso AS c
WHERE c.OpiskeluoikeusID=b.OpiskeluoikeusID
)
OR b.Paattymispaivamaara IS NULL
)
'+CHAR(13)
end;
-- Ensisijaisuus: Päällekkäisiä
-- OODIPOLIISI: enssij_paallekkain
-- NB! HUOMAA ERILAINEN TAULUJEN ALIASISOINTI
if @tarkistuskoodi=2003 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2003)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskeluoikeuksien ensisijaisuuksissa päällekkäisyyksiä'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik1.Avain+'',''+oik2.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=oik1.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, null as opiskeluoikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Op_oikeuden_ensisijaisuus AS e1
inner join Opiskeluoikeus oik1 on oik1.ID=e1.OpiskeluoikeusID
inner join Opiskeluoikeus oik2 on oik2.OpiskelijaID=oik1.OpiskelijaID
inner join Op_oikeuden_ensisijaisuus e2 on e2.OpiskeluoikeusID=oik2.ID
WHERE 1=1
-- varmistetaan eri ensisijaisuus
AND 1 IN (
CASE
-- jos saman oikeuden, ...
WHEN e1.OpiskeluoikeusID=e2.OpiskeluoikeusID THEN
-- ...niin eri päivän => ensisijaisuudet ovat eri
CASE WHEN e1.Alkamispaivamaara<>e2.Alkamispaivamaara THEN 1 ELSE 0 END
ELSE
1 -- eri oik => ok
END
)
-- varmistetaan päällekkäisyys, siten...
AND(e1.Paattymispaivamaara BETWEEN e2.Alkamispaivamaara AND e2.Paattymispaivamaara
OR e2.Paattymispaivamaara BETWEEN e1.Alkamispaivamaara AND e1.Paattymispaivamaara
)
--...että e1 sisältää aiemman päivämäärän
AND(e1.Alkamispaivamaara<e2.Alkamispaivamaara
OR (e1.Alkamispaivamaara=e2.Alkamispaivamaara AND e1.Paattymispaivamaara<e2.Paattymispaivamaara)
OR (e1.Alkamispaivamaara=e2.Alkamispaivamaara AND e1.Paattymispaivamaara=e2.Paattymispaivamaara)
)
--bonus: ilmoitetaan oikeudet vain kerran:
AND oik1.Avain<=oik2.Avain
'+char(13)
end;
-- Opiskeluoikeuden koulutuskoodi puuttuu
if @tarkistuskoodi=2004 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2004)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskeluoikeusjaksolta puuttuu koulutuskoodi'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce(a.Avain,''#Puuttuu#'') AS opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from Opiskeluoikeus AS a
inner join Opiskeluoikeusjakso AS b on b.OpiskeluoikeusID=a.ID
inner join Opiskeluoikeuden_tyyppi c on c.ID = a.Op_oikeuden_tyyppiID
where b.TutkintonimikeID is null
and c.Koodi in (''1'', ''2'', ''3'', ''4'', ''5'', ''6'', ''7'')
'+CHAR(13)
end;

-- Ensisijaisuus puuttuu tutkinto-opiskelijan opiskeluoikeuksista
if @kk_tyyppi = '42' begin -- vain yo
if @tarkistuskoodi=2005 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2005)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Ensisijaisuus puuttuu tutkinto-opiskelijan opiskeluoikeuksista'' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, a.Avain
, coalesce(a.Avain,''#Puuttuu#'') as opiskelijaAvain
, null as opiskeluoikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskelija AS a
WHERE 1=1
-- ei niiden opiskeluoikeuksien joukossa joihin liittyy ensisijaisuus
AND a.ID NOT IN (
select o.OpiskelijaID
from Opiskeluoikeus o
inner join Op_oikeuden_ensisijaisuus e on e.OpiskeluoikeusID=o.ID
)
-- on tutkintoon johtava opiskeluoikeus
AND a.ID IN (
select o.OpiskelijaID
from Opiskeluoikeus o
inner join Opiskeluoikeuden_tyyppi k on k.ID=o.Op_oikeuden_tyyppiID
where 1=1
-- NB! Vain yliopistot!
--and k.Koodi in (''1'',''2'',''3'',''4'',''5'',''6'',''7'')
and k.Koodi in (''2'',''4'',''5'',''6'',''7'')
-- opiskeluoikeus ollut voimassa 1.8.2005 tai sen jälkeen
--and ''2005-08-01'' between o.Alkamispaivamaara and isnull(o.Paattymispaivamaara,''9999-01-01'')
-- opiskeluoikeus ollut voimassa kuluvana vuonna
and cast(year(getdate()) as varchar)+''-01-01'' <= isnull(o.Paattymispaivamaara,''9999-01-01'')
)
'+char(13)
end;
end;--vain yo

-- Opiskelijalla on vain optio-tilassa oleva opiskeluoikeus
if @tarkistuskoodi=2006 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2006)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskelijalla on vain optio-tilassa oleva opiskeluoikeus'' as kuvaus
, ''opiskelijaAvain'' as avainsarake
, a.Avain
, coalesce(a.Avain,''#Puuttuu#'') as opiskelijaAvain
, null as opiskeluoikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opiskelija a
WHERE 1=1
-- EI niiden joukossa, joilla on muu kuin OPTIO-tilassa oleva opiskeluoikeus
AND a.ID NOT IN (
select o2.OpiskelijaID
from Opiskeluoikeus o2
inner join Op_oikeuteen_liittyva_tila ot2 on ot2.OpiskeluoikeusID=o2.ID
inner join Opiskeluoikeuden_tila t2 on t2.ID=ot2.Op_oikeuden_tilaID
where t2.Koodi<>''2''--optio
)
-- ... mutta on OPTIO-tilassa oleva opiskeluoikeus
AND a.ID IN (
select o.OpiskelijaID
from Opiskeluoikeus o
inner join Op_oikeuteen_liittyva_tila ot on ot.OpiskeluoikeusID=o.ID
inner join Opiskeluoikeuden_tila t on t.ID=ot.Op_oikeuden_tilaID
where t.Koodi=''2''--optio
)
'+char(13)
end;

-- Opiskeluoikeuden liittyvyys viittaa tietoon jota ei ole
-- NB! Käytetään XML_SA-taulua hyväksi (_Id = .id)
if @tarkistuskoodi=2007 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2007)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskeluoikeuden liittyvyys viittaa tietoon jota ei ole'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce(a.Avain,''#Puuttuu#'') AS opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from Opiskeluoikeus AS a
inner join XML_SA.Opiskeluoikeus_Liittyvyys SA on SA.Opiskeluoikeus_Id=a.ID
where SA.liittyvaOpiskeluoikeusAvain not in (
SELECT b.avain FROM Opiskeluoikeus b
)
'+CHAR(13)
end;

-- Opiskeluoikeudella on monta myöntäjää
if @tarkistuskoodi=2008 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2008)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskeluoikeudella on monta myöntäjää'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, ''#Puuttuu#'' as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from Opiskeluoikeus a
inner join Op_oikeuden_organisaatio ag on ag.OpiskeluoikeusID=a.ID
inner join Organisaation_rooli as gr on gr.ID=ag.Organisaation_rooliID
where gr.Koodi=''1'' --myöntävä
group by a.OpiskelijaID, a.Avain having COUNT(*)>1
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeuden päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=2009 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2009)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''passPvm/luopPvm '', isnull(cast(minLoppunutPvm as nvarchar), ''null''), '' !< oikAlkPvm '', cast(oik.alkamispaivamaara as nvarchar)
, '', oikPaatPvm='', cast(oik.paattymispaivamaara as nvarchar)) as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
left join (
select opiskeluoikeusId, min(oikTila.alkamispaivamaara) as minLoppunutPvm
from op_oikeuteen_liittyva_tila as oikTila
join opiskeluoikeuden_tila as oikTilaKoodi on oikTilaKoodi.id = oikTila.op_oikeuden_tilaId
where oikTilaKoodi.koodi in (''4'',''5'')
group by opiskeluoikeusId
) as paattynytTila on paattynytTila.opiskeluoikeusId = oik.id
where oik.paattymispaivamaara is not null and oik.paattymispaivamaara < oik.alkamispaivamaara
and (paattynytTila.minLoppunutPvm is null or paattynytTila.minLoppunutPvm > oik.alkamispaivamaara)
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeusjakson päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=2010 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2010)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''JaksAlkPvm='', cast(jaks.alkamispaivamaara as nvarchar), '', PvmErotus='', (cast(datediff(day, jaks.alkamispaivamaara, jaks.paattymispaivamaara) as nvarchar))) as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from opiskeluoikeusjakso as jaks
join opiskeluoikeus as oik on oik.id = jaks.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where jaks.paattymispaivamaara is not null and jaks.paattymispaivamaara < jaks.alkamispaivamaara
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeuden tilan päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=2011 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2011)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''op_oikeuteen_liittyva_tila.Alkamispaivamaara'' as avainsarake
, convert(nvarchar, til.Alkamispaivamaara, 23) as avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, cast(datediff(day, til.alkamispaivamaara, til.paattymispaivamaara) as nvarchar) as lisaTieto
, ''9999-12-31'' as virhTiedPvm
from op_oikeuteen_liittyva_tila as til
join opiskeluoikeus as oik on oik.id = til.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where til.paattymispaivamaara is not null and til.paattymispaivamaara < til.alkamispaivamaara
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeuden ensisijaisuuden päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=2012 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2012)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''op_oikeuden_ensisijaisuusAvain'' as avainsarake
, ens.Avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, ''9999-12-31'' as virhTiedPvm
from op_oikeuden_ensisijaisuus as ens
join opiskeluoikeus as oik on oik.id = ens.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where ens.paattymispaivamaara is not null and ens.paattymispaivamaara < ens.alkamispaivamaara
'+CHAR(13)
end;


-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeusjaksojen päivämäärät eivät ole opiskeluoikeuden alkamis- ja loppupäivämäärän sisällä';
--Ei lasketa puutteeksi sitä, jos opiskeluoikeusjaksolla ei ole päättymispäivämäärää vaikka opiskeluoikeudella se olisi.
if @tarkistuskoodi=2013 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2013)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(jaks.alkamispaivamaara, '' - '', jaks.paattymispaivamaara, '' vs '', oik.alkamispaivamaara, '' - '', oik.paattymispaivamaara) as lisaTieto
, isnull(jaks.paattymispaivamaara, ''9999-12-31'') as virhTiedPvm
from opiskeluoikeusjakso as jaks
join opiskeluoikeus as oik on oik.id = jaks.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where jaks.alkamispaivamaara < oik.alkamispaivamaara
or (jaks.paattymispaivamaara is not null and jaks.paattymispaivamaara > isnull(oik.paattymispaivamaara, ''9999-12-31''))
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Tutkintoon johtamattomasta opiskeluoikeudesta puuttuu OKM:n ohjausala (koodi 1 - 12)';
if @tarkistuskoodi=2014 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2014)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, (case when kala.versio is not null then concat(''koulutusala-luokittelussa on käytetty versiota '', kala.versio) else null end) as lisaTieto
, null as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join opiskeluoikeuden_tyyppi as ooTyyp on ooTyyp.id = oik.op_oikeuden_tyyppiId
join henkilo as henk on henk.id = opis.henkiloId
left outer join koulutusala as kala on kala.id = oik.koulutusalaId
where oik.koulutusalaId is null and ooTyyp.koodi not in (''1'',''2'',''3'',''4'',''5'',''6'',''7'')
and (kala.id is null or kala.versio != ''ohjausala'')
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeus päättynyt valmistumiseen (tila 3) ilman yhtään aiempaa aktiivista tilajaksoa';
if @tarkistuskoodi=2015 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2015)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''maksPaatTila '', maxPaatAlkPvm, '' < minAktTila '', (case when minAktAlkPvm is null then ''null'' else cast(minAktAlkPvm as varchar) end)) as lisaTieto
, null as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join (
select oik.id, min(ooTilaAkt.alkamispaivamaara) as minAktAlkPvm, max(ooTilaPaat.alkamispaivamaara) as maxPaatAlkPvm
from opiskeluoikeus oik
join op_oikeuteen_liittyva_tila as ooTilaPaat on ooTilaPaat.opiskeluoikeusId = oik.id
join opiskeluoikeuden_tila as ooTilaKoodiPaat on ooTilaKoodiPaat.id = ooTilaPaat.op_oikeuden_tilaId
left outer join (
select ooTila.opiskeluoikeusId, ooTila.alkamispaivamaara
from op_oikeuteen_liittyva_tila as ooTila
join opiskeluoikeuden_tila as ooTilaKoodi on ooTilaKoodi.id = ooTila.op_oikeuden_tilaId
where ooTilaKoodi.koodi = ''1'' -- 1=aktiivi
) as ooTilaAkt
on ooTilaAkt.opiskeluoikeusId = oik.id
where ooTilaKoodiPaat.koodi = ''3'' -- 3=valmistunut
group by oik.id
having max(ooTilaPaat.alkamispaivamaara) < isnull(min(ooTilaAkt.alkamispaivamaara),''9999-12-31'')
) as tilapaivamaarat
on tilapaivamaarat.id = oik.id
where (minAktAlkPvm is not null or (maxPaatAlkPvm != dateadd(d,1,oik.paattymispaivamaara) or oik.paattymispaivamaara is null))
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeudella on aktiivisen tilan jakso (tilakoodi 1) opiskeluoikeuden päättymisen jälkeen';
if @tarkistuskoodi=2016 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2016)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''Akt.jakso : '', maxAlkPvmJakso, '' > opis.oik. :'', oik.alkamispaivamaara, '' - '', oik.paattymispaivamaara) as lisaTieto
, right(maxAlkPvmJakso,11) as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join (
select oik.id
, max(concat(ooTilaAkt.alkamispaivamaara, '' - '', ooTilaAkt.paattymispaivamaara)) as maxAlkPvmJakso
from opiskeluoikeus oik
join op_oikeuteen_liittyva_tila as ooTilaAkt on ooTilaAkt.opiskeluoikeusId = oik.id
join opiskeluoikeuden_tila as ooTilaAktKoodi on ooTilaAktKoodi.id = ooTilaAkt.op_oikeuden_tilaId
where oik.paattymispaivamaara is not null
and ooTilaAktKoodi.koodi = ''1'' -- 1=aktiivinen
and (ooTilaAkt.paattymispaivamaara is not null and ooTilaAkt.paattymispaivamaara > oik.paattymispaivamaara)
group by oik.id
) as aktTila
on aktTila.id = oik.id
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Tutkintoon johtavasta (kand./maist., tyypit 1, 2, 3, 4) vuoden 2000 jälkeen alkaneesta opiskeluoikeudesta puuttuu laajuus tai laajuus on nolla';
if @tarkistuskoodi=2017 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2017)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join opiskeluoikeuden_tyyppi as ooTyyp on ooTyyp.id = oik.op_oikeuden_tyyppiId
join henkilo as henk on henk.id = opis.henkiloId
where ooTyyp.koodi in (''1'',''2'',''3'',''4'')
and (oik.laajuus is null or oik.laajuus = 0)
and oik.alkamispaivamaara >= ''2000-01-01''
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeuden lajin (1,2,3,4,6,7) ja tutkintonimikkeen koulutusastetason välillä on ristiriita';
if @tarkistuskoodi=2018 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2018);
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, (case when oikTyyppiKdsto.koodi in (''1'',''2'') then ''Kun laji on '' + oikTyyppiKdsto.koodi + '', niin tason pitäisi olla 6''
when oikTyyppiKdsto.koodi in (''3'',''4'') then ''Kun laji on '' + oikTyyppiKdsto.koodi + '', niin tason pitäisi olla 7''
when oikTyyppiKdsto.koodi in (''6'',''7'') then ''Kun laji on '' + oikTyyppiKdsto.koodi + '', niin tason pitäisi olla 8''
end) + '' ('' + tutkNim.nimi + '')'' as lisatieto
, suorituspaivamaara as virhTiedPvm
from opintosuoritus as suor
join opiskelija as opis on (opis.id = suor.opiskelijaId)
join opiskeluoikeus as oik on (oik.id = suor.opiskeluoikeusId)
join opiskeluoikeuden_tyyppi as oikTyyppiKdsto on (oikTyyppiKdsto.id = oik.op_oikeuden_tyyppiId)
join opintosuorituksen_laji as suorLajiKdsto on suorLajiKdsto.id = (suor.opintosuorituksen_lajiId)
join opiskeluoikeusjakso as jak on jak.opiskeluoikeusId = oik.id
join tutkintonimike as tutkNim on tutkNim.id = jak.tutkintonimikeId
join yhteiset.dw.d_koulutusluokitus as koulLuok on koulutusluokitus_koodi = tutkNim.koodi
where suorLajiKdsto.koodi = ''1''-- 1 = tutkinto
and oikTyyppiKdsto.koodi in (''1'',''2'',''3'',''4'',''6'',''7'')
and suor.suorituspaivamaara between jak.alkamispaivamaara and jak.paattymispaivamaara
and ((oikTyyppiKdsto.koodi in (''1'',''2'') and cast(koulutusastetaso1_koodi as int) != 6) or
(oikTyyppiKdsto.koodi in (''3'',''4'') and cast(koulutusastetaso1_koodi as int) != 7) or
(oikTyyppiKdsto.koodi in (''6'',''7'') and cast(koulutusastetaso1_koodi as int) != 8))
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Valmistunut (tila 3) tutkinto-oikeus (1,2,3,4,6,7), mihin ei liity tutkintosuoritusta (opintosuorituslaji 1)';
if @tarkistuskoodi=2019 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2019)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where oik.id in (
select oikId from (
select distinct oik.id as oikId
from opiskeluoikeus as oik
join opiskeluoikeuden_tyyppi as oikTyyppiKdsto on (oikTyyppiKdsto.id = oik.op_oikeuden_tyyppiId)
join op_oikeuteen_liittyva_tila as oikTila on (oikTila.opiskeluoikeusId = oik.id)
join opiskeluoikeuden_tila as oikTilaKdsto on (oikTilaKdsto.id = oikTila.op_oikeuden_tilaId)
where oikTyyppiKdsto.koodi in (''1'',''2'',''3'',''4'',''6'',''7'')
and oikTilaKdsto.koodi in (''3'') -- 3 = päättynyt(valmistunut)
except
select distinct opiskeluoikeusId as oikOid
from opintosuoritus as suor
join opintosuorituksen_laji as suorLajiKdsto on (suorLajiKdsto.id = suor.opintosuorituksen_lajiId)
where suorLajiKdsto.koodi = ''1'' -- 1 = tutkinto
and opiskeluoikeusId is not null
) as sub )
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Tutkintolajin opintosuoritus (laji 1) ei liity tutkinto-opiskeluoikeuteen (1,2,3,4,6,7)';
if @tarkistuskoodi=2020 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2020)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, oikTyyppiKdsto.koodi + ''='' + oikTyyppiKdsto.nimi as lisaTieto
, null as virhTiedPvm
from opintosuoritus as suor
join opintosuorituksen_laji as suorLajiKdsto on (suorLajiKdsto.id = suor.opintosuorituksen_lajiId)
left outer join opiskeluoikeus as oik on oik.id = suor.opiskeluoikeusId
left outer join opiskeluoikeuden_tyyppi as oikTyyppiKdsto on (oikTyyppiKdsto.id = oik.op_oikeuden_tyyppiId)
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where suorLajiKdsto.koodi = ''1'' -- 1 = tutkinto
and (oikTyyppiKdsto.koodi not in (''1'',''2'',''3'',''4'',''6'',''7'') or oikTyyppiKdsto.koodi is null)
and suorituspaivamaara >= ''2015-01-01''
and (oik.alkamispaivamaara >= ''2015-01-01'' or oik.alkamispaivamaara is null)
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Erikoistumiskoulutuksen opiskeluoikeudelta (tyyppi 19) puuttuu OPH:n kolmenumeroinen koulutuskoodi';
if @tarkistuskoodi=2021 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2021)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
join opiskeluoikeuden_tyyppi as oikTyyppiKdsto on (oikTyyppiKdsto.id = oik.op_oikeuden_tyyppiId)
left outer join op_oik_liittyva_erikoistumiskoulutus as erikKoul on (erikKoul.opiskeluoikeusId = oik.id)
where oikTyyppiKdsto.koodi in (''19'')
and erikKoul.id is null
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeusjakson kunta ei ole selvillä, tieto puuttuu tai on 999 (=Tuntematon)';
if @tarkistuskoodi=2022 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2022)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''JakAlkPvm='', oikJak.alkamispaivamaara, '', Kunta='', kunta.koodi) as lisaTieto
, isnull(oikJak.paattymispaivamaara, ''9999-12-31'') as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
join opiskeluoikeusjakso as oikJak on (oikJak.opiskeluoikeusId = oik.id)
left outer join kunta on (kunta.id = oikJak.kuntaId)
where (kunta.koodi is null or kunta.koodi = ''999'')
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Tutkintosuorituksen tutkintokoodi on 00-loppuinen (TK:n "kohdistuskoodi")';
/*
Testikannassa 25.05.2023 tuli sama määrä puutteita (11 kpl koko Virrassa), rajoittipa opiskeluoikeuksia
(ja mahdollisesti myös suorituksen lajia (tutkinto)) -> on nopeampi suorittaa, kun ei tehdä ylimääräisiä ehtoja.
19.06.2023: Jotta saadaan puutetauluun opisOikAktPaatPvm, niin haetaan opiskeluoikeus
*/
if @tarkistuskoodi=2023 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2023)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''tutkintokoodi='', tutkNimKdsto.koodi, '', suorituspaivamaara='', suor.suorituspaivamaara) as lisaTieto
, suor.suorituspaivamaara as virhTiedPvm
from opintosuoritus as suor
--join opintosuorituksen_laji as suorLajiKdsto on (suorLajiKdsto.id = suor.opintosuorituksen_lajiId)
left outer join opiskeluoikeus as oik on oik.id = suor.opiskeluoikeusId
--left outer join opiskeluoikeuden_tyyppi as oikTyyppiKdsto on (oikTyyppiKdsto.id = oik.op_oikeuden_tyyppiId)
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
join tutkintonimike as tutkNimKdsto on tutkNimKdsto.id = suor.tutkintonimikeId
where suor.tutkintonimikeId is not null
and right(tutkNimKdsto.koodi,2) = ''00''
--and suorLajiKdsto.koodi = ''1'' -- 1 = tutkinto
--and oikTyyppiKdsto.koodi in (''1'',''2'',''3'',''4'',''6'',''7'')
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeuden tila koodilla 3 (valmistunut), 5 (luopunut) tai 6 (päättynyt) sisältää päättymispäivämäärän (!= 31.12.9999)' ;
if @tarkistuskoodi=2024 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2024)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''op_oikeuteen_liittyva_tila.Alkamispaivamaara'' as avainsarake
, convert(nvarchar, til.Alkamispaivamaara, 126) as avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''tila='', tilKdsto.koodi, '', tilPaatPvm='', til.paattymispaivamaara) as lisaTieto
, null as virhTiedPvm
from op_oikeuteen_liittyva_tila as til
join opiskeluoikeus as oik on oik.id = til.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
join opiskeluoikeuden_tila as tilKdsto on tilKdsto.id = til.op_oikeuden_tilaId
where til.paattymispaivamaara is not null -- and til.paattymispaivamaara < ''9999-12-31''
and tilKdsto.koodi in (''3'',''5'',''6'')
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Tutkintosuorituksen tutkintokoodi on 999999 (Tuntematon)';
if @tarkistuskoodi=2025 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2025)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, null as opiskeluoikeusAvain
, concat(''suorituspaivamaara='', suor.suorituspaivamaara) as lisaTieto
, null as virhTiedPvm
from opintosuoritus as suor
join opintosuorituksen_laji as suorLajiKdsto on (suorLajiKdsto.id = suor.opintosuorituksen_lajiId)
--left outer join opiskeluoikeus as oik on oik.id = suor.opiskeluoikeusId
--left outer join opiskeluoikeuden_tyyppi as oikTyyppiKdsto on (oikTyyppiKdsto.id = oik.op_oikeuden_tyyppiId)
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
join tutkintonimike as tutkNimKdsto on tutkNimKdsto.id = suor.tutkintonimikeId
where suor.tutkintonimikeId is not null
and tutkNimKdsto.koodi = ''999999''
and suorLajiKdsto.koodi = ''1'' -- 1 = tutkinto
--and oikTyyppiKdsto.koodi in (''1'',''2'',''3'',''4'',''6'',''7'')
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Toisiinsa liittyvillä alemman ja ylemmän tutkinnon opiskeluoikeuksilla ei ole sama alkamispaivämäärä' ;
if @tarkistuskoodi=2026 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2026)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, (case when oikTyyp1.koodi in (''3'',''4'') then oik1.avain else oik2.avain end) as avain
, opis.avain as opiskelijaAvain
, (case when oikTyyp1.koodi in (''3'',''4'') then oik1.avain else oik2.avain end) as opiskeluOikeusAvain
, concat(''alemOik='',(case when oikTyyp1.koodi in (''3'',''4'') then oik2.avain else oik1.avain end)
,'', alemAlkPvm='', (case when oikTyyp1.koodi in (''3'',''4'') then oik2.alkamispaivamaara else oik1.alkamispaivamaara end)
,'', ylemAlkPvm='', (case when oikTyyp1.koodi in (''3'',''4'') then oik1.alkamispaivamaara else oik2.alkamispaivamaara end)
) as lisatieto
, null as virhTiedPvm
from opiskeluoikeuden_liittyvyys as liit
join opiskeluoikeus as oik1 on oik1.id = liit.opiskeluoikeusId
join opiskeluoikeus as oik2 on oik2.id = liit.opiskeluoikeusId2
join opiskeluoikeuden_tyyppi as oikTyyp1 on oikTyyp1.id = oik1.op_oikeuden_tyyppiId
join opiskeluoikeuden_tyyppi as oikTyyp2 on oikTyyp2.id = oik2.op_oikeuden_tyyppiId
join opiskelija as opis on opis.id = oik1.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where translate(concat(oikTyyp1.koodi,oikTyyp2.koodi), ''1234'', ''aayy'') in (''ay'',''ya'')
and (oik1.alkamispaivamaara != oik2.alkamispaivamaara)
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeusjakson tutkintokoodin koulutusaste ei vastaa opiskeluoikeuden tyyppiä';
if @tarkistuskoodi=2027 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2027)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''oikTyyp='', oikTyyp.koodi, ''='', oikTyyp.nimi, ''; tutk='', tutkNimKdsto.koodi, ''='', tutkNimKdsto.nimi
, ''; koulLuok/taso='', koulLuokKdsto.koulutusastetaso2_koodi) as lisaTieto
, isnull(oikJak.paattymispaivamaara, ''9999-12-31'') as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
join opiskeluoikeuden_tyyppi as oikTyyp on oikTyyp.id = oik.op_oikeuden_tyyppiId
join opiskeluoikeusjakso as oikJak on (oikJak.opiskeluoikeusId = oik.id)
join tutkintonimike as tutkNimKdsto on tutkNimKdsto.id = oikJak.tutkintonimikeId
join (select opiskeluoikeusId as oikId, oikTila.alkamispaivamaara as valmPvm
from op_oikeuteen_liittyva_tila as oikTila
join opiskeluoikeuden_tila as oikTilKdsto on oikTilKdsto.id = oikTila.op_oikeuden_tilaId
where oikTilKdsto.koodi = ''3'') as valmOpisOik on valmOpisOik.oikId = oik.id
join yhteiset.dw.d_koulutusluokitus as koulLuokKdsto on koulLuokKdsto.koulutusluokitus_koodi = tutkNimKdsto.koodi
where tutkNimKdsto.koodi != ''999999''
and (oikJak.alkamispaivamaara <= valmPvm and (oikJak.paattymispaivamaara is null or oikJak.paattymispaivamaara >= valmPvm))
and (
(oikTyyp.koodi in (''1'') and koulLuokKdsto.koulutusastetaso2_koodi != ''62'')
or
(oikTyyp.koodi in (''2'') and koulLuokKdsto.koulutusastetaso2_koodi != ''63'')
or
(oikTyyp.koodi in (''3'') and koulLuokKdsto.koulutusastetaso2_koodi != ''71'')
or
(oikTyyp.koodi in (''4'') and koulLuokKdsto.koulutusastetaso2_koodi != ''72'')
or
(oikTyyp.koodi in (''5'') and koulLuokKdsto.koulutusastetaso2_koodi != ''73'')
or
(oikTyyp.koodi in (''6'') and koulLuokKdsto.koulutusastetaso2_koodi != ''81'')
or
(oikTyyp.koodi in (''7'') and koulLuokKdsto.koulutusastetaso2_koodi != ''82'')
or
(oikTyyp.koodi not in (''1'',''2'',''3'',''4'',''5'',''6'',''7'')
and koulLuokKdsto.koulutusastetaso2_koodi in (''62'',''63'',''71'',''72'',''73'',''81'',''82'')))
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Tutkinto-opiskeluoikeuden aktiiviselta jaksolta puuttuu lukukausi-ilmoittautumisia';
if @tarkistuskoodi=2028 or @tarkistuskoodi is null begin
set @apuSql='use ' + @db + '
insert into #oikLiitos (hakuOik, apuOik, toinenOik, lisVaihe)
select opiskeluoikeusId, opiskeluoikeusId2, opiskeluoikeusId2, 1
from opiskeluoikeus as oik
join opiskeluoikeuden_liittyvyys as liit on liit.opiskeluoikeusId = oik.id
join opiskeluoikeuden_tyyppi as oikTyypKdsto on oikTyypKdsto.id = oik.op_oikeuden_tyyppiId
where oikTyypKdsto.koodi in (''1'',''2'',''3'',''4'',''6'',''7'')

insert into #oikLiitos (hakuOik, apuOik, toinenOik, lisVaihe)
select opiskeluoikeusId2, opiskeluoikeusId, opiskeluoikeusId, 2
from opiskeluoikeus as oik
join opiskeluoikeuden_liittyvyys as liit on liit.opiskeluoikeusId = oik.id
join opiskeluoikeuden_tyyppi as oikTyypKdsto on oikTyypKdsto.id = oik.op_oikeuden_tyyppiId
where oikTyypKdsto.koodi in (''1'',''2'',''3'',''4'',''6'',''7'')

insert into #oikLiitos (hakuOik, apuOik, toinenOik, lisVaihe)
select hakuOik, opiskeluoikeusId2, opiskeluoikeusId2, 3
from #oikLiitos
join opiskeluoikeuden_liittyvyys as liit on opiskeluoikeusId = apuOik

insert into #oikLiitos (hakuOik, apuOik, toinenOik, lisVaihe)
select hakuOik, opiskeluoikeusId, opiskeluoikeusId, 4
from #oikLiitos
join opiskeluoikeuden_liittyvyys as liit on opiskeluoikeusId2 = apuOik

insert into #oikLiitokset(hakuOik, toinenOik)
select distinct hakuOik, toinenOik
from #oikLiitos;

insert into #aktAjat (aktOik, aktAlkPvm, aktPaatPvm, valmis, okAsti, luupNro, maxLkmIlmPaatPvm, aukot)
select hakuOik.hakuOik as aktOik, oikTil.alkamispaivamaara as aktAlkPvm,
(case when oikTil.paattymispaivamaara < getdate() then oikTil.paattymispaivamaara else cast(getdate() as date) end) as aktPaatPvm
, cast(0 as tinyint) as valmis
, dateadd(day, -1, oikTil.alkamispaivamaara) as okAsti
, cast(1 as smallint) as luupNro
, cast(null as date) as maxLkmIlmPaatPvm
--, cast(null as date) as minLkmIlmAlkPvm
--, cast(0 as smallint) as paiLkm1, cast(0 as smallint) as paiLkm2
, cast(null as nvarchar(max)) as aukot
from (select distinct hakuOik from #oikLiitokset) as hakuOik
join opiskeluoikeus as oik on oik.id = hakuOik.hakuOik
join op_oikeuteen_liittyva_tila as oikTil on oikTil.opiskeluoikeusId = oik.Id
join opiskeluoikeuden_tila as oikTilKdsto on oikTilKdsto.id = oikTil.op_oikeuden_tilaId
where oikTilKdsto.koodi = ''1'' -- 1 = aktiivinen
and oikTil.alkamispaivamaara <= oikTil.paattymispaivamaara -- paskat unohdetaan (puute 2011)
and oikTil.alkamispaivamaara < getDate() -- tulevaisuudessa olevista aktiivisuuksista ei välitetä
--tämä on hidas puutetarkistus (HY jopa 500s) jos koko historia -> otetaan vain se aika, josta ilmoitetaan korkeakoululle
and (oik.paattymispaivamaara is null or oik.paattymispaivamaara >= datefromparts(datepart(year, getdate())-5,1,1))
';
exec(@apuSql);
set @apuSql='use ' + @db + '
declare @luupNro smallint = 0;
declare @rows int;
declare @onkoSyytaJatkaaLuuppia int;
while (1=1) begin
set @luupNro = @luupNro + 1;
if (@luupNro > 200) break
update #aktAjat
set maxLkmIlmPaatPvm = seurIlmHak.maxLkmIlmPaatPvm,
luupNro = @luupNro + 1
from #aktAjat as upd
left outer join (
select hakuAkt.aktOik as hakuOik, max(lkIlm.paattymispaivamaara) as maxLkmIlmPaatPvm
from #aktAjat as hakuAkt
join #oikLiitokset as hakuOik on hakuOik = hakuAkt.aktOik
join lukukausi_ilmoittautuminen as lkIlm on lkIlm.opiskeluoikeusId = toinenOik
where hakuAkt.valmis = 0 and lkIlm.alkamispaivamaara <= dateadd(day, 1, hakuAkt.okAsti) and (lkIlm.paattymispaivamaara > hakuAkt.okAsti or lkIlm.paattymispaivamaara is null)
group by hakuAkt.aktOik
) as seurIlmHak on seurIlmHak.hakuOik = upd.aktOik
where upd.valmis = 0;-- and upd.luupNro = @luupNro;
set @rows = @@rowcount;
set @onkoSyytaJatkaaLuuppia = @rows;

update #aktAjat set
okAsti = maxLkmIlmPaatPvm,
valmis = (case when maxLkmIlmPaatPvm >= aktPaatPvm then 2 else valmis end)
where luupNro = @luupNro + 1 and maxLkmIlmPaatPvm is not null;
set @rows = @@rowcount;

update #aktAjat set
aukot = concat(aukot,(case when aukot is null then '''' else '', '' end), dateadd(day,1,okAsti),''-'')
where luupNro = @luupNro + 1 and maxLkmIlmPaatPvm is null;
set @rows = @@rowcount;

update #aktAjat set
valmis = (case when minLkmIlmAlkPvm is null or minLkmIlmAlkPvm > aktPaatPvm then 1 else valmis end),
aukot = concat(aukot, (case when minLkmIlmAlkPvm is null then aktPaatPvm when minLkmIlmAlkPvm > aktPaatPvm then aktPaatPvm else dateadd(day, -1, minLkmIlmAlkPvm) end)),
okAsti = (case when minLkmIlmAlkPvm is null then okAsti when minLkmIlmAlkPvm > aktPaatPvm then okAsti else dateadd(day, -1, minLkmIlmAlkPvm) end)
from #aktAjat as upd
left outer join (
select hakuAkt.aktOik as hakuOik, min(lkIlm.alkamispaivamaara) as minLkmIlmAlkPvm
from #aktAjat as hakuAkt
join #oikLiitokset as hakuOik on hakuOik = hakuAkt.aktOik
join lukukausi_ilmoittautuminen as lkIlm on lkIlm.opiskeluoikeusId = toinenOik
where hakuAkt.valmis = 0 and lkIlm.alkamispaivamaara > dateadd(day, 1, hakuAkt.okAsti)
group by hakuAkt.aktOik
) as seurIlmHak on seurIlmHak.hakuOik = upd.aktOik
where luupNro = @luupNro + 1 and maxLkmIlmPaatPvm is null;
set @rows = @@rowcount;
set @onkoSyytaJatkaaLuuppia = @onkoSyytaJatkaaLuuppia + @rows;

update #aktAjat set
valmis = 1
where luupNro = @luupNro;
set @rows = @@rowcount;
set @onkoSyytaJatkaaLuuppia = @onkoSyytaJatkaaLuuppia + @rows;

if @onkoSyytaJatkaaLuuppia = 0 break;
end
delete from #aktAjat where aukot is null;
';
exec(@apuSql);

set @t_koodi = coalesce(@tarkistuskoodi,2028)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.Avain
, opis.avain as opiskelijaAvain
, oik.Avain as opiskeluOikeusAvain
, concat(''Akt.jakso='', aktAlkPvm, ''-'', aktPaatPvm, ''; aukot='', aukot) as lisatieto
, isnull(aktPaatPvm, ''9999-12-31'') as virhTiedPvm
from #aktAjat
join opiskeluoikeus as oik on oik.id = aktOik
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as hlo on hlo.id = opis.henkiloId
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Tutkinto-opiskeluoikeuden aktiiviselta jaksolta puuttuu lukukausi-ilmoittautumisia';
if 1=2 begin --@tarkistuskoodi=2028 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2028)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.Avain
, opis.avain as opiskelijaAvain
, oik.Avain as opiskeluOikeusAvain
, concat((case when minLasnaAlkPvm > oikTilAlkPvm then ''AlkPuut; '' else ''- '' end)
, (case when maxLasnaPaatPvm < oikTilPaatPvm then ''LopPuut; '' else ''- '' end)
, ''Akt.jakso='', oikTilId, '' ajalla '', oikTilAlkPvm, ''-'', oikTilPaatPvm, ''; jaksonPvLkm='', oikTilPvLkm,''; ilmPvLkm='', sumLasnaPvLkm
, ''; minIlmAlkPvm='', minLasnaAlkPvm, ''; maxIlmPaatPvm='', maxLasnaPaatPvm, ''; ''
, (case when paattynytTila.minLoppunutPvm is not null then
concat(case when paattynytTila.minLoppunutPvm <= oikTilPaatPvm then ''AktJaks >= Pass/Luop/PaatPvm '' else ''AktJaks < Pass/Luop/PaatPvm '' end, cast(paattynytTila.minLoppunutPvm as date))
else ''ei Pass/Luop/Paat tietoa'' end))
as lisaTieto
, isnull(oikTilPaatPvm, ''9999-12-31'') as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as hlo on hlo.id = opis.henkiloId
join (select oik.id as oikId, oikTil.id as oikTilId, oikTil.alkamispaivamaara as oikTilAlkPvm, oikTil.paattymispaivamaara as oikTilPaatPvm
, (datediff(day, oikTil.alkamispaivamaara, (case when oikTil.paattymispaivamaara < getdate() then oikTil.paattymispaivamaara else cast(getdate() as date) end)) + 1) as oikTilPvLkm
, (sum(datediff(day, lasnaAlkPvm, lasnaPaatPvm) + 1)) as sumLasnaPvLkm
, min(lasnaAlkPvm) as minLasnaAlkPvm, max(lasnaPaatPvm) as maxLasnaPaatPvm
from opiskeluoikeus as oik
join opiskeluoikeuden_tyyppi as oikTyypKdsto on oikTyypKdsto.id = oik.op_oikeuden_tyyppiId
join op_oikeuteen_liittyva_tila as oikTil on oikTil.opiskeluoikeusId = oik.Id
join opiskeluoikeuden_tila as oikTilKdsto on oikTilKdsto.id = oikTil.op_oikeuden_tilaId
join (select lkIlm.opiskeluoikeusId as lasnaOikId, lkIlm.id
, (case when lkIlm.alkamispaivamaara < oikTilApu.alkamispaivamaara then oikTilApu.alkamispaivamaara else lkIlm.alkamispaivamaara end) as lasnaAlkPvm
, (case when lkIlm.paattymispaivamaara > oikTilApu.paattymispaivamaara then oikTilApu.paattymispaivamaara else lkIlm.paattymispaivamaara end) as lasnaPaatPvm
from lukukausi_ilmoittautuminen as lkIlm
join lukukausi_ilm_tila as lkIlmTilaKdsto on lkIlmTilaKdsto.id = lkIlm.lukukausi_ilmoittautumisen_tilaId
join op_oikeuteen_liittyva_tila as oikTilApu on oikTilApu.opiskeluoikeusId = lkIlm.opiskeluoikeusId
where lkIlmTilaKdsto.koodi in (''1'',''2'',''3'',''4'') -- 1 = läsnä, 2 = poissa, 3 = poissa ei kuluta opintoaikaa, 4 = puuttuu
and lkIlm.alkamispaivamaara <= oikTilApu.paattymispaivamaara
and lkIlm.paattymispaivamaara >= oikTilApu.alkamispaivamaara
and lkIlm.alkamispaivamaara <= cast(getdate() as date)
) as lasnaolot on lasnaOikId = oik.id
where oikTilKdsto.koodi = ''1'' -- 1 = aktiivinen
--and oikTil.paattymispaivamaara >= ''2020-08-01'' -- ei välitetä jostain rajasta vanhemmista
and oikTyypKdsto.koodi in (''1'',''2'',''3'',''4'',''6'',''7'')
and lasnaAlkPvm <= oikTil.paattymispaivamaara
and lasnaPaatPvm >= oikTil.alkamispaivamaara
group by oik.id, oikTil.id, oikTil.alkamispaivamaara, oikTil.paattymispaivamaara
having datediff(day, oikTil.alkamispaivamaara, (case when oikTil.paattymispaivamaara < getdate() then oikTil.paattymispaivamaara else cast(getdate() as date) end)) + 1
!= sum(datediff(day, lasnaAlkPvm, lasnaPaatPvm) + 1)
) as puutteet on puutteet.oikId = oik.id
left join (
select opiskeluoikeusId, min(oikTila.alkamispaivamaara) as minLoppunutPvm
from op_oikeuteen_liittyva_tila as oikTila
join opiskeluoikeuden_tila as oikTilaKoodi on oikTilaKoodi.id = oikTila.op_oikeuden_tilaId
where oikTilaKoodi.koodi in (''4'',''5'',''6'')
group by opiskeluoikeusId
) as paattynytTila on paattynytTila.opiskeluoikeusId = oik.id
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Suomen kansalaisella on rahoituslähteenä lukuvuosimaksu (koodi 5)';
if @tarkistuskoodi=2029 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2029)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.Avain as opiskeluOikeusAvain
, concat (
(select count(1)
from opiskeluoikeus as uudempiOik
join opiskelija as uudempiOpis on uudempiOpis.id = uudempiOik.opiskelijaId
where uudempiOpis.henkiloId = opis.henkiloId
and uudempiOik.alkamispaivamaara > isnull(oik.paattymispaivamaara,''9999-12-31'')
),
'' = uudempiOikLkm. Opiskeluoikeusjakso ajalla '', jaks.alkamispaivamaara, ''-'', jaks.paattymispaivamaara) as lisaTieto
, isnull(isnull(jaks.paattymispaivamaara, oik.paattymispaivamaara), ''9999-12-31'') as virhTiedPvm
from opiskeluoikeusjakso as jaks
join rahoituslahde as rahLahd on rahLahd.id = jaks.rahoituslahdeId
join opiskeluoikeus as oik on oik.id = jaks.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as hlo on hlo.id = opis.henkiloId
join henkilon_maa as hloMaa on hloMaa.henkiloId = hlo.id
join maa on maa.id = hloMaa.maaId
join maan_Rooli on maan_Rooli.id = hloMaa.maan_rooliId
where rahLahd.koodi = ''5'' -- 5 = lukukausimaksukokeilu
and maa.koodi = ''246'' and maan_rooli.koodi = ''1'' -- 1 = kansalaisuus
'+CHAR(13)
end;

set @t_kuvaus = N'Opiskeluoikeudella ei ole opiskeluoikeusjaksoa opiskeluoikeuden alusta alkaen';
--Ei lasketa puutteeksi sitä, jos opiskeluoikeusjaksolla ei ole päättymispäivämäärää vaikka opiskeluoikeudella se olisi.
if @tarkistuskoodi=2030 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2030)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(oik.alkamispaivamaara, '' < '',
isnull(cast((select min(alkamispaivamaara) from opiskeluoikeusjakso as jak where opiskeluoikeusId = oik.id and jak.alkamispaivamaara > oik.alkamispaivamaara) as nvarchar),''(jakso puuttuu)'')
, '', siirtoPvm='', isnull(cast(siirtopaivamaara as varchar),''null'')) as lisaTieto
, oik.alkamispaivamaara as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join (
select oik.id, (case when siirtopaivamaara is null then 0 when siirtopaivamaara < oik.alkamispaivamaara then -1 else 1 end) as siirto
from opiskeluoikeus as oik
except
select oik.id, (case when siirtopaivamaara is null then 0 when siirtopaivamaara < oik.alkamispaivamaara then -1 else 1 end) as siirto
from opiskeluoikeus as oik
join opiskeluoikeusjakso as jak on jak.opiskeluoikeusId = oik.id
where jak.alkamispaivamaara <= oik.alkamispaivamaara and (jak.paattymispaivamaara is null or jak.paattymispaivamaara >= oik.alkamispaivamaara))
as virhetapaus
on virhetapaus.id = oik.id
'+CHAR(13)
end;

-- CSCTIE-600 lisäys, virhekoodi 5005 -> 2031 by CSCTIE-702
set @t_kuvaus = 'Opiskelijaliikkuvuuden päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=2031 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,5031)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskelijaliikkuvuusAvain'' as avainsarake
, liik.Avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, ''9999-12-31'' as virhTiedPvm
from opiskelijaliikkuvuus as liik
join opiskeluoikeus as oik on oik.id = liik.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where liik.paattymispaivamaara is not null and liik.paattymispaivamaara < liik.alkamispaivamaara
'+CHAR(13)
end;

-- CSCTIE-715
set @t_kuvaus = 'Tutkintoon johtavan opiskeluoikeuden laajuus ei ole jaollinen 30op';
if @tarkistuskoodi=2032 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2032)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''Laajuus='', cast(round(oik.laajuus,0) as int)) as lisaTieto
, null as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join opiskeluoikeuden_tyyppi as ooTyyp on ooTyyp.id = oik.op_oikeuden_tyyppiId
join henkilo as henk on henk.id = opis.henkiloId
where ooTyyp.koodi in (''1'',''2'',''3'',''4'')
and oik.laajuus is not null and oik.laajuus != 0
and (oik.laajuus / 30) != round((oik.laajuus / 30), 0)
'+CHAR(13)
end;

-- CSCTIE-716
set @t_kuvaus = 'Tutkintoon johtavan opiskeluoikeuden laajuus poikkeaa koulutuskoodin mukaisesta laajuudesta';
if @tarkistuskoodi=2033 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2033)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''OikAlk='', oik.alkamispaivamaara, '', OikTyyp='', ooTyyp.nimi, '', OikLaaj='',
cast(round(oik.laajuus,0) as int), '', VaadLaaj='', replace(trim(laajuudet),'' '','',''), '', TutkKood='', nim.koodi, '', TutkNim='', nim.nimi) as lisaTieto
, null as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join opiskeluoikeusjakso as jak on jak.opiskeluoikeusId = oik.id
join tutkintonimike as nim on nim.id = jak.tutkintonimikeId
join (
select tutkintokoodi, concat('' '', string_agg(vaadittu_laajuus,'' ''), '' '') as laajuudet
from yhteiset.dbo.tutkinto_vaadittu_laajuus
group by tutkintokoodi) as laaj on laaj.tutkintokoodi = nim.koodi
join opiskeluoikeuden_tyyppi as ooTyyp on ooTyyp.id = oik.op_oikeuden_tyyppiId
join (select opiskeluoikeusId as maksOikId, max(concat(convert(nvarchar, alkamispaivamaara, 23), format(id, ''##############''))) as maksAlkVastId
from opiskeluoikeusjakso
where tutkintonimikeId is not null
group by opiskeluoikeusId)
as maksAlkJak on maksOikId = oik.id and parse(substring(maksAlkVastId,11,14) as bigint) = jak.id
and oik.laajuus is not null and oik.laajuus != 0
and ooTyyp.koodi in (''1'',''2'',''3'',''4'')
and charindex(concat('' '', cast(round(oik.laajuus,0) as int), '' ''), laajuudet) = 0
'+CHAR(13)
end;

-- CSCTIE-735
set @t_kuvaus = 'Opiskeluoikeuden opiskeluoikeusjaksoissa on aukkoja tai päällekkäisyyksiä';
if @tarkistuskoodi=2034 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2034)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat((case when cast(substring(seurAlkPaatPvm,1,10) as date) <= dateadd(day, 1, isnull(jak.paattymispaivamaara,''9999-12-30'') )
then ''Jaksot limittyvät: '' else ''Jaksojen välissä on aukko: '' end),
cast(jak.alkamispaivamaara as nvarchar(15)), '' - '', cast(jak.paattymispaivamaara as nvarchar(15)),
'' vs '', substring(seurAlkPaatPvm,1,10), '' - '', substring(seurAlkPaatPvm,11,10),
''; Oik: '', cast(oik.alkamispaivamaara as nvarchar(15)), '' - '', cast(oik.paattymispaivamaara as nvarchar(15))) as lisatieto
, isnull(jak.paattymispaivamaara, ''9999-12-31'') as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join opiskeluoikeusjakso as jak on jak.opiskeluoikeusId = oik.id
join (select edel.id, min(concat(cast(seur.alkamispaivamaara as varchar(15)), cast(seur.paattymispaivamaara as varchar(15)))) as seurAlkPaatPvm
from opiskeluoikeusjakso as edel
join opiskeluoikeusjakso as seur on seur.opiskeluoikeusId = edel.opiskeluoikeusId and seur.Id != edel.Id
where seur.alkamispaivamaara >= edel.alkamispaivamaara
group by edel.Id) as seurHaku on seurHaku.id = jak.id
where cast(substring(seurAlkPaatPvm,1,10) as date) != dateadd(day, 1, isnull(jak.paattymispaivamaara,''9999-12-30''))
'+CHAR(13)
end;

-- CSCTIE-735
set @t_kuvaus = 'Opiskeluoikeuden tilojen voimassaoloajoissa on aukkoja tai päällekkäisyyksiä';
if @tarkistuskoodi=2035 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2035)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat((case when cast(substring(seurAlkPaatPvm,1,10) as date) <= dateadd(day, 1, isnull(til.paattymispaivamaara,''9999-12-30'') )
then ''Voimassaoloajat limittyvät: '' else ''Voimassaoloaikojen välissä on aukko: '' end),
cast(til.alkamispaivamaara as nvarchar(15)), '' - '', cast(til.paattymispaivamaara as nvarchar(15)),
'' vs '', substring(seurAlkPaatPvm,1,10), '' - '', substring(seurAlkPaatPvm,11,10),
''; Oik: '', cast(oik.alkamispaivamaara as nvarchar(15)), '' - '', cast(oik.paattymispaivamaara as nvarchar(15))) as lisatieto
, isnull(til.paattymispaivamaara, ''9999-12-31'') as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join op_oikeuteen_liittyva_tila as til on til.opiskeluoikeusId = oik.id
join (select edel.id, min(concat(cast(seur.alkamispaivamaara as varchar(15)), cast(seur.paattymispaivamaara as varchar(15)))) as seurAlkPaatPvm
from op_oikeuteen_liittyva_tila as edel
join op_oikeuteen_liittyva_tila as seur on seur.opiskeluoikeusId = edel.opiskeluoikeusId and seur.Id != edel.Id
where seur.alkamispaivamaara >= edel.alkamispaivamaara
group by edel.Id) as seurHaku on seurHaku.id = til.id
where cast(substring(seurAlkPaatPvm,1,10) as date) != dateadd(day, 1, isnull(til.paattymispaivamaara,''9999-12-30''))
'+CHAR(13)
end;

--lisäykset 02/2024, CSCTIE-776
set @t_kuvaus = 'Siirto-opiskeluoikeuden siirtopvm puuttuu tai on epälooginen opiskeluoikeuden voimassaoloaikaan nähden.';
if @tarkistuskoodi=2036 or @tarkistuskoodi is null begin

set @t_koodi = coalesce(@tarkistuskoodi,2036)

set @apuSql=N'
use '+@db+'
--lisätään aputauluun ensin rivejä; riveistä poistetaan sitten ok-tapaukset
insert into #puute2036 (oikId, rool3, rool4, oikAlkPvm, oikPaatPvm, oikSiirtoPvm)
(select oik.id, (case when min(orgRoolKood.koodi) = ''3'' then 1 else 0 end), (case when max(orgRoolKood.koodi) = ''4'' then 1 else 0 end)
, max(oik.alkamispaivamaara), max(oik.paattymispaivamaara), max(oik.siirtopaivamaara)
from op_oikeuden_organisaatio as oikOrg
join organisaation_rooli as orgRoolKood on orgRoolKood.id = oikOrg.organisaation_rooliId
join opiskeluoikeus as oik on oik.id = oikOrg.opiskeluoikeusId
join opiskeluoikeuden_tyyppi as oikTyyp on oikTyyp.id = oik.op_oikeuden_tyyppiId
where orgRoolKood.koodi in (''3'',''4'') -- 4 = kohde, 3 = lähde
and oikTyyp.koodi in (''1'',''2'',''3'',''4'')
group by oik.id);

update #puute2036
set tapTyyp = (case when rool3 = 0 and rool4 = 0 then ''EiOrg'' when rool3 = 1 and rool4 = 0 then ''SaapPvm'' when rool3 = 0 and rool4 = 1 then ''LahtPvm'' else ''SaapJaLahtPvm'' end)

--lisätään aputauluun tapaukset, joissa opiskeluoikeudella ei ole tulo-organisaatiota mutta on siirtopaivamäärä/saapuva
insert into #puute2036 (oikId, rool3, rool4, oikAlkPvm, oikPaatPvm, oikSiirtoPvm, tapTyyp)
(select oik.id, 0, 0, oik.alkamispaivamaara, oik.paattymispaivamaara, oik.siirtopaivamaara, ''SaapOrgPuut''
from opiskeluoikeus as oik
where siirtopaivamaara is not null and siirtopaivamaara < alkamispaivamaara
and not exists (select 1 from #puute2036 where oikId = oik.id)
);

--lisätään aputauluun tapaukset, joissa opiskeluoikeudella ei ole lähtöorganisaatiota mutta on siirtopaivamäärä/lähtevä
insert into #puute2036 (oikId, rool3, rool4, oikAlkPvm, oikPaatPvm, oikSiirtoPvm, tapTyyp)
(select oik.id, 0, 0, oik.alkamispaivamaara, oik.paattymispaivamaara, oik.siirtopaivamaara, ''LahtOrgPuut''
from opiskeluoikeus as oik
where siirtopaivamaara is not null and siirtopaivamaara >= alkamispaivamaara
and not exists (select 1 from #puute2036 where oikId = oik.id)
);


--poistetaan ok-tapaukset;
--On lähtenyt mutta ei ole saapunut -> virhe jos oikSiirtoPvm puuttuu tai oikSiirtoPvm <= oikAlkPvm tai oikPaatPvm puuttuu
delete from #puute2036 where tapTyyp = ''LahtPvm'' and (case when oikSiirtoPvm is not null and oikSiirtoPvm >= oikAlkPvm and oikPaatPvm is not null and oikSiirtoPvm = dateadd(day, 1, oikPaatPvm) then 1 else 0 end) = 1
--On sekä saapunut että lähtenyt -> sama sääntö kuin lähteneelle
delete from #puute2036 where tapTyyp = ''SaapJaLahtPvm'' and (case when oikSiirtoPvm is not null and oikSiirtoPvm >= oikAlkPvm and oikPaatPvm is not null and oikSiirtoPvm = dateadd(day, 1, oikPaatPvm) then 1 else 0 end) = 1
--On saapunut -> ok, jos oikSiirtoPvm < oikAlkPvm
delete from #puute2036 where tapTyyp = ''SaapPvm'' and (case when oikSiirtoPvm is not null and oikSiirtoPvm < oikAlkPvm then 1 else 0 end) = 1

'+CHAR(13)
exec sp_executesql @apuSql

if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end

set @sql=@sql+N'
-- saapuva
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''Virhetyyppi='' , tapTyyp, '', SiirtoPvm='', isnull(cast(oik.siirtopaivamaara as nvarchar(15)),''null''), '', oikAlkPvm=''
, cast(oik.alkamispaivamaara as nvarchar(15)), '', oikPaatPvm=''
, cast(oik.paattymispaivamaara as nvarchar(15))) as lisatieto
, isnull(oik.paattymispaivamaara, ''9999-12-31'') as virhTiedPvm
from #puute2036 as puutetapaus
join opiskeluoikeus as oik on oik.id = puutetapaus.oikId
join opiskelija as opis on opis.id = oik.opiskelijaId
'+CHAR(13)
end;

--lisäykset 02/2024, CSCTIE-778
set @t_kuvaus = 'Opiskeluoikeuden alkamispäivämäärä on kaukana tulevaisuudessa (> vuosi).';
if @tarkistuskoodi=2037 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2037)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''Alkamispäivämäärä on '', cast(oik.alkamispaivamaara as nvarchar(15))) as lisatieto
, oik.alkamispaivamaara as virhTiedPvm
from opiskeluoikeus as oik
join opiskelija as opis on opis.id = oik.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where oik.alkamispaivamaara > cast(dateadd(month, 12, getdate()) as date)
'+CHAR(13)
end;

--lisäykset 02/2024, CSCTIE-779
set @t_kuvaus = N'Tutkinto (suorituksen laji 1) suoritettu mutta opiskeluoikeuden tila ei ole valmistunut (koodi 3).';
if @tarkistuskoodi=2038 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2038)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''viimTila='', viimPvmKood, '', arvSan='' , arv.koodi, ''='', arv.arvosana) as lisaTieto
, isnull(hyvaksilukupaivamaara, suorituspaivamaara) as virhTiedPvm
from opintosuoritus as suor
join opintosuorituksen_laji as lajKood on lajKood.id = suor.opintosuorituksen_lajiId
left outer join opsuorituksen_hyvaksiluku as hyvLuk on hyvLuk.opintosuoritusId = suor.id
left outer join opiskeluoikeus as oik on oik.id = suor.opiskeluoikeusId
left outer join (
select oikTil.opiskeluoikeusId, max(concat(cast(oikTil.alkamispaivamaara as nvarchar(20)),'':'',oikTilKood.koodi)) as viimPvmKood
from op_oikeuteen_liittyva_tila as oikTil
join opiskeluoikeuden_tila as oikTilKood on oikTilKood.id = oikTil.op_oikeuden_tilaId
group by oikTil.opiskeluoikeusId
) as oikViimTil on oikViimTil.opiskeluoikeusId = oik.Id
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
left outer join arvosana as arv on arv.id = suor.arvosanaId
where lajKood.koodi = ''1'' -- 1 = tutkinto
and oik.alkamispaivamaara >= ''2015-01-01''
and (viimPvmKood is null or substring(viimPvmKood, charindex('':'', viimPvmKood) + 1, 1) != ''3'')
'+CHAR(13)
end;

set @t_kuvaus = N'Opiskeluoikeuden päättymisen (oik.tila) pvm on varhaisempi kuin elossa oloa tarkoittavan tilan pvm.';
if @tarkistuskoodi=2039 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2039)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(''eiLop '', maxEiLopPaatTilAlkPvm, '' >= lop '' , maxLopPaatTilAlkPvm) as lisaTieto
, (case when maxLopPaatTilAlkPvm > maxEiLopPaatTilAlkPvm then maxLopPaatTilAlkPvm else maxEiLopPaatTilAlkPvm end) as virhTiedPvm
from (
select lopPaatTil.opiskeluoikeusId, maxLopPaatTilAlkPvm, maxEiLopPaatTilAlkPvm from (
select opiskeluoikeusId, max(til.alkamispaivamaara) as maxLopPaatTilAlkPvm
from op_oikeuteen_liittyva_tila as til
join opiskeluoikeuden_tila as tilKood on tilKood.id = til.op_oikeuden_tilaId
where tilKood.koodi in (''3'',''5'',''6'')
group by opiskeluoikeusId
) as lopPaatTil
join (
select opiskeluoikeusId, max(til.alkamispaivamaara) as maxEiLopPaatTilAlkPvm
from op_oikeuteen_liittyva_tila as til
join opiskeluoikeuden_tila as tilKood on tilKood.id = til.op_oikeuden_tilaId
where tilKood.koodi in (''1'',''2'',''4'')
group by opiskeluoikeusId
) as eiLopPaatTil on eiLopPaatTil.opiskeluoikeusId = lopPaatTil.opiskeluoikeusId
where maxLopPaatTilAlkPvm <= maxEiLopPaatTilAlkPvm
) as puutetapaus
join opiskeluoikeus as oik on oik.id = puutetapaus.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
'+CHAR(13)
end;

set @t_kuvaus = N'Opiskeluoikeudella on useampi sen lopullista päättymistä tarkoittava tilatieto.';
if @tarkistuskoodi=2040 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2040)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, puutetapaus.info as lisaTieto
, puutetapaus.virhPvm as virhTiedPvm
from (
select opiskeluoikeusId, string_agg(concat(til.alkamispaivamaara, '' ('', tilKood.koodi, '')''),'' '') as info
,max(til.alkamispaivamaara) as virhPvm
from op_oikeuteen_liittyva_tila as til
join opiskeluoikeus as oik on oik.id = til.opiskeluoikeusId
join opiskeluoikeuden_tila as tilKood on tilKood.id = til.op_oikeuden_tilaId
where tilKood.koodi in (''3'',''5'',''6'')
group by opiskeluoikeusId
having count(1) > 1
) as puutetapaus
join opiskeluoikeus as oik on oik.id = puutetapaus.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
'+CHAR(13)
end;

set @t_kuvaus = N'Opiskeluoikeuden tilan voimassaoloaika ei ala opiskeluoikeuden aikana (lopputila +1 pv).';
if @tarkistuskoodi=2041 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2041)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, ''2041'' as virhekoodi
, ''Opiskeluoikeuden tilan voimassaoloaika alkaa ennen/jälkeen opiskeluoikeuden voimassaoloajan.'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, (concat(''oikKesto:'' , max(oik.alkamispaivamaara),'' - '', max(isnull(oik.paattymispaivamaara,''9999-12-31'')), '': '',
string_agg(concat((case
when til.alkamispaivamaara < oik.alkamispaivamaara then ''E''
when dateadd(day, -1, til.alkamispaivamaara) > isnull(oik.paattymispaivamaara,''9999-12-31'') then ''J''
when dateadd(day, -1, til.alkamispaivamaara) = isnull(oik.paattymispaivamaara,''9999-12-31'') and tilKood.koodi in (''3'',''5'',''6'') then ''OK''
when dateadd(day, -1, til.alkamispaivamaara) = isnull(oik.paattymispaivamaara,''9999-12-31'') and tilKood.koodi not in (''3'',''5'',''6'') then ''J''
else ''OK'' end),''-'',til.alkamispaivamaara, '' '', tilKood.nimi),'', '')
within group (order by til.alkamispaivamaara))) as lisaTieto
, max(isnull(oik.paattymispaivamaara,''9999-12-31'')) as virhTiedPvm
from
--select puuteOik.* from
(select distinct oik.id
from opiskeluoikeus as oik
join op_oikeuteen_liittyva_tila as til on til.opiskeluoikeusId = oik.id
join opiskeluoikeuden_tila as tilKood on tilKood.id = til.op_oikeuden_tilaId
where til.alkamispaivamaara < oik.alkamispaivamaara
--and tilKood.koodi not in (''4'',''5'')-- 5 = luopunut, 4 = passivoitu
) as puuteOik
join opiskeluoikeus as oik on oik.id = puuteOik.id
join op_oikeuteen_liittyva_tila as til on til.opiskeluoikeusId = oik.id
join opiskeluoikeuden_tila as tilKood on tilKood.id = til.op_oikeuden_tilaId
join opiskelija as opis on opis.id = oik.opiskelijaId
group by oik.avain, opis.avain
'+CHAR(13)
end;

-- CSCTIE-1030 lisäys
set @t_kuvaus = N'Tutkintoon johtavalta opiskeluoikeudelta puuttuu rahoituslähde';
if @tarkistuskoodi=2042 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,2042)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, oik.avain
, opis.avain as opiskelijaAvain


, oik.avain as opiskeluOikeusAvain
, (concat(''oikJakso:'' , puute.alkamispaivamaara,'' - '', puute.paattymispaivamaara)) as lisaTieto
, isnull(oik.paattymispaivamaara,''9999-12-31'') as virhTiedPvm
from
(select oik.id, jak.alkamispaivamaara, jak.paattymispaivamaara, rahLahd.koodi as rahLahdKood, jak.avain as jakAvain
from opiskeluoikeus as oik
join opiskeluoikeuden_tyyppi as oikTyyp on oikTyyp.id = oik.op_oikeuden_tyyppiId
join opiskeluoikeusjakso as jak on jak.opiskeluoikeusId = oik.id
join op_oikeuteen_liittyva_tila as oikTil on oikTil.opiskeluoikeusID = oik.id
join opiskeluoikeuden_tila as oikTilKood on oikTilKood.id = oikTil.op_oikeuden_tilaId
--21.01.2025: siirretty tänne (jossa sen pitääkin olla): rahLahd.koodi in (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'')
left outer join rahoituslahde as rahLahd on rahLahd.id = jak.rahoituslahdeId and rahLahd.koodi in (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'')
where oikTyyp.koodi in (''1'',''2'',''3'',''4'')
--21.01.2025: Lisätty op_oikeuteen_liittyva_tila tutkiminen (oltava 1, tiketillä ei ollut mainintaa aktiivisesta jaksosta)
and oikTilKood.koodi = ''1''
-- opiskeluoikeusjakson voimassaoloajan on leikattava aktiivisen opiskeluoikeuden tilan voimassaoloaikaa
and jak.alkamispaivamaara <= isnull(oikTil.paattymispaivamaara,''9999-12-31'') and isnull(jak.paattymispaivamaara,''9999-12-31'') >= oikTil.alkamispaivamaara
) as puute
join opiskeluoikeus as oik on oik.id = puute.id
join opiskelija as opis on opis.id = oik.opiskelijaId
join opiskeluoikeusjakso as jak on jak.avain = puute.jakAvain
where rahLahdKood is null
order by oik.avain, jak.avain, jak.alkamispaivamaara, jak.paattymispaivamaara
'+CHAR(13)
end;

--
-- LUKUKAUSIILMOITTAUTUMISET
--
-- Oppilaitoksella muiden korkeakoulujen lukukausi-ilmoittautumisia
if @tarkistuskoodi=3001 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,3001)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Oppilaitoksella muiden korkeakoulujen lukukausi-ilmoittautumisia'' as kuvaus
, ''opiskelijaAvain # opiskeluoikeusAvain'' as avainsarake
, substring(Avain, (charindex(''#'',avain)+1), (len(avain) - (charindex(''#'', reverse(avain)) + (charindex(''#'',avain)))))
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from Lukukausi_ilmoittautuminen a
inner join Organisaatio as b on a.OrganisaatioID = b.id
and b.Koodi not in (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where db = '''+@db+''')
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opiskeluoikeuteen liittyvän lukuvuosimaksun päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=3002 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,3002)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''op_oikeuteen_liittyva_lukuvuosimaksu.Alkamispaivamaara'' as avainsarake
, convert(nvarchar, lvmaksu.Alkamispaivamaara, 23) as avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, ''9999-12-31'' as virhTiedPvm
from op_oikeuteen_liittyva_lukuvuosimaksu as lvmaksu
join opiskeluoikeus as oik on oik.id = lvmaksu.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where lvmaksu.paattymispaivamaara is not null and lvmaksu.paattymispaivamaara < lvmaksu.alkamispaivamaara
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Lukukausi-ilmoittautumisen päättymispaivämäärä on pienempi kuin alkamispäivämäärä';
if @tarkistuskoodi=3003 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,3003)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''lukukausi_ilmoittautuminenAvain'' as avainsarake
, lkilm.Avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, ''9999-12-31'' as virhTiedPvm
from lukukausi_ilmoittautuminen as lkilm
left outer join opiskeluoikeus as oik on oik.id = lkilm.opiskeluoikeusId
left outer join opiskelija opis on opis.id = oik.opiskelijaId
where lkilm.paattymispaivamaara is not null and lkilm.paattymispaivamaara < lkilm.alkamispaivamaara
'+CHAR(13)
end;

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Lukuvuosimaksun velvollisuusjaksojen pitäisi olla lukuvuoden(kauden) alkamis- ja päättymispäivämäärien mukaisia';
if @tarkistuskoodi=3004 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,3004)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''op_oikeuteen_liittyva_lukuvuosimaksu.Alkamispaivamaara'' as avainsarake
, convert(nvarchar, lvmaksu.Alkamispaivamaara, 23) as avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, null as Lisatieto
, isnull(lvmaksu.paattymispaivamaara, ''9999-12-31'') as virhTiedPvm
from op_oikeuteen_liittyva_lukuvuosimaksu as lvmaksu
join opiskeluoikeus as oik on oik.id = lvmaksu.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where month(lvmaksu.alkamispaivamaara) not in (1,8) or day(lvmaksu.alkamispaivamaara) != 1
or month(lvmaksu.paattymispaivamaara) not in (7,12) or day(lvmaksu.paattymispaivamaara) != 31
or datediff(day, lvmaksu.alkamispaivamaara, lvmaksu.paattymispaivamaara) + 1 not between 0 and 213
'+CHAR(13)
-- voimassaoloajan keston yläraja 213 estää 7 kk pidemmän jakson, ml. koko lukuvuoden
end;

-- CSCTIE-1034 lisäys
set @t_kuvaus = N'Tutkinnon suoritushetkellä poissaolevaksi ilmoittautunut';
if @tarkistuskoodi=3005 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,3005)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Tutkinnon suoritushetkellä poissaolevaksi ilmoittautunut'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.Avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluoikeusAvain
, concat(''Suorituspäivämäärä '', cast(suorituspaivamaara as nvarchar)) as Lisatieto
, null as virhTiedPvm
from opintosuoritus as suor
join opintosuorituksen_laji as suorLaj on suorLaj.id = suor.opintosuorituksen_lajiId
join opiskelija as opis on opis.id = suor.opiskelijaId
join opiskeluoikeus as oik on oik.id = suor.opiskeluoikeusId
--Jos opiskeluoikeudella on yksikin oma lk-ilmo milloin tahansa, niin käytetään vai ko. opiskeluoikeuden lk-ilmoja.
--Muuten käytetään vain lk-ilmoja, joita ei ole liitetty opiskeluoikeuteen
left outer join (select opiskeluoikeusId from lukukausi_ilmoittautuminen group by opiskeluoikeusId) as tarkOnkOmiaIlm on tarkOnkOmiaIlm.opiskeluoikeusId = oik.id
left outer join lukukausi_ilmoittautuminen as lkIlmOma on lkIlmOma.opiskeluoikeusId = oik.id
left outer join lukukausi_ilm_tila as lkIlmTilaOma on lkIlmTilaOma.id = lkIlmOma.lukukausi_ilmoittautumisen_tilaId
and lkIlmTilaOma.koodi in (''2'',''3'')
and suorituspaivamaara between lkIlmOma.alkamispaivamaara and lkIlmOma.paattymispaivamaara
left outer join lukukausi_ilmoittautuminen as lkIlmYht on lkIlmYht.opiskelijaId = opis.id and lkIlmYht.opiskeluoikeusId is null
left outer join lukukausi_ilm_tila as lkIlmTilaYht on lkIlmTilaYht.id = lkIlmYht.lukukausi_ilmoittautumisen_tilaId
and lkIlmTilaYht.koodi in (''2'',''3'')
and suorituspaivamaara between lkIlmYht.alkamispaivamaara and lkIlmYht.paattymispaivamaara
where suorLaj.koodi = ''1'' -- 1 = tutkinto
and (case when tarkOnkOmiaIlm.opiskeluoikeusId is not null
then (case when lkIlmTilaOma.koodi is not null then 1 else 0 end)
else (case when lkIlmTilaYht.koodi is not null then 1 else 0 end) end) = 1
'+CHAR(13)
end;

--
-- OPINTOSUORITUKSET
--
-- Nimi puuttuu
if @tarkistuskoodi=4001 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4001)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opintosuoritukselta puuttuu nimi'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=suor.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=suor.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from (
select avain from opintosuoritus where nimi is null
except
select distinct avain
from Kieliversio kie
join Ominaisuus omi on omi.id = kie.ominaisuusId and omi.nimi=''Nimi''
join Kasite kas on kas.id = omi.kasiteId and kas.nimi=''Opintosuoritus''
) as puutetapaus
join Opintosuoritus as suor on suor.avain = puutetapaus.avain
'+CHAR(13)
end;

-- Arvosana puuttuu
if @tarkistuskoodi=4002 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4002)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opintosuoritukselta puuttuu arvosana'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opintosuoritus AS a
WHERE a.ArvosanaID IS NULL
'+CHAR(13)
end;
-- Koulutusala puuttuu
-- 2021-07-19 Koulutusala ei kokonaisuuksista, sisältyvyys
-- Laji 2 (ja 3?)
if @tarkistuskoodi=4003 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4003)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opintosuoritukselta puuttuu koulutusala'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opintosuoritus a
inner join Opintosuorituksen_laji sl on sl.ID=a.Opintosuorituksen_lajiID
WHERE a.ID NOT IN (
SELECT OpintosuoritusID FROM Op_suor_koulutusala
)
AND sl.Koodi IN (''2'') -- muu opintosuoritus laji 2
AND a.id NOT IN (
SELECT SA.Opintosuoritus_Id FROM XML_SA.Opintosuoritus_Sisaltyvyys SA -- 2021-07-19 ei kokonaisuuksista, sisältyvyys
)
'+CHAR(13)
end;
/*
-- Koulutusaste puuttuu
if @tarkistuskoodi=4004 begin
SELECT COUNT(*)
FROM Opintosuoritus
WHERE KoulutusasteID IS NULL
;
end;
*/
-- Tutkintonimike/Koulutuskoodi puuttuu, kun opintosuorituksen laji: 1=Tutkinto
if @tarkistuskoodi=4005 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4005)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opintosuoritukselta (laji: tutkinto) puuttuu tutkintonimike/koulutuskoodi'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Opintosuoritus AS a
WHERE a.TutkintonimikeID IS NULL
AND a.Opintosuorituksen_lajiID = (
SELECT ID FROM Opintosuorituksen_laji WHERE Koodi=''1'' --Tutkinto
)
'+CHAR(13)
end;

--
-- AMK: Opintosuoritukselta puuttuu opiskeluoikeus
--
if @kk_tyyppi = '41' begin -- vain amk
if @tarkistuskoodi=4006 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4006)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opintosuoritukselta puuttuu opiskeluoikeus'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from opintosuoritus a
where a.opiskeluoikeusid is null'+CHAR(13)


end
end;

-- Opintosuoritus virheellisellä sisältyvyyden avaintiedolla
-- NB! Käytetään XML_SA-taulua hyväksi (_Id = .id)
-- Katsotaan vain vuoden 2006 ja jälkeen suoritettuja (bologna jne).
-- CSCTIE-600:n yhteydessä lisätty lisätieto-sarakkeeseen sen Virrasta puuttuvan opintosuorituksen avain
if @tarkistuskoodi=4007 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4007)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opintosuoritus virheellisellä sisältyvyyden avaintiedolla'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, ''sisaltyvaOpintosuoritusAvain=''+ sa.sisaltyvaOpintosuoritusAvain AS Lisatieto
, null as virhTiedPvm
from Opintosuoritus AS a
inner join XML_SA.Opintosuoritus_Sisaltyvyys SA on SA.Opintosuoritus_Id=a.ID
where SA.sisaltyvaOpintosuoritusAvain not in (
SELECT b.avain FROM Opintosuoritus b
)
and YEAR(a.Suorituspaivamaara) >= 2006
'+CHAR(13)
end;

-- Opintosuorituksella on monta myöntäjää
if @tarkistuskoodi=4008 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4008)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Opiskeluoikeudella on monta myöntäjää'' as kuvaus
, ''opiskeluoikeusAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, ''#Puuttuu#'' as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from Opiskeluoikeus a
inner join Op_oikeuden_organisaatio ag on ag.OpiskeluoikeusID=a.ID
inner join Organisaation_rooli as gr on gr.ID=ag.Organisaation_rooliID
where gr.Koodi=''1'' --myöntävä
group by a.OpiskelijaID, a.Avain having COUNT(*)>1
'+CHAR(13)
end;

-- 2021-07-19 Alkaa

-- 2021-07-19: CSCTIE-277 Tutkintosuoritukselta puuttuu opiskeluoikeusliitäntä Opintosuorituksen_laji 1 , where a.opiskeluoikeusid is null , 2000-01-01 alkaen
if @tarkistuskoodi=4009 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4009)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
SET @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Tutkintosuoritukselta puuttuu opiskeluoikeusliitäntä'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, ''#Puuttuu#'' as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
from opintosuoritus a
where a.opiskeluoikeusid is null
AND coalesce(a.suorituspaivamaara,''1900-1-1'') >= ''2000-01-01''
AND a.Opintosuorituksen_lajiID = (
SELECT ID FROM Opintosuorituksen_laji WHERE Koodi=''1'' --Tutkinto
)
'+CHAR(13)
end;


-- 2021-07-19: Suorituspäivämäärä tulevaisuudessa (yli 1 v) a.suorituspaivamaara > dateadd(year, 1, getdate())
if @tarkistuskoodi=4010 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4010)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
SET @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Suorituspäivämäärä tulevaisuudessa (yli 1 v)'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
--, a.suorituspaivamaara AS Lisatieto
, null as Lisatieto
, null as virhTiedPvm
from opintosuoritus a
where a.suorituspaivamaara > dateadd(year, 1, getdate())
'+CHAR(13)
end;


-- 2021-07-19: Erikois(hammas)lääkäri (775101,775201) tutkintosuoritus, 2015-08-01 alkaen
if @tarkistuskoodi=4011 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4011)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
SET @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Erikois(hammas)lääkäri tutkintosuoritus (2015 alkaen ei ole tutkinto)'' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, a.avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
--, t.koodi + ''+'' + cast(a.suorituspaivamaara AS varchar) AS Lisatieto
, null as Lisatieto
, null as virhTiedPvm
from opintosuoritus a
JOIN tutkintonimike t ON t.id = a.tutkintonimikeid
where t.koodi IN (''775101'',''775201'')
AND coalesce(a.suorituspaivamaara,''1900-01-01'') >= ''2015-08-01''
AND a.Opintosuorituksen_lajiID = (
SELECT ID FROM Opintosuorituksen_laji WHERE Koodi=''1'' --Tutkinto
)
'+CHAR(13)
end;

-- 2021-07-19: Loppu

-- CSCTIE-600 lisäys
set @t_kuvaus = N'Opintosuoritus (suorituspäivämäärä) ajalla, jolloin opiskeluoikeudella ei ole opiskeluoikeusjaksoa';
if @tarkistuskoodi=4012 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4012)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat((case when oikTyypKood.koodi is null then ''00'' when len(oikTyypKood.koodi) < 2 then concat(''0'',oikTyypKood.koodi) else oikTyypKood.koodi end)
,(case when suorOnkHyvLuet = 0 then ''[H0]'' else ''[H1]'' end)
,(case when oik.siirtopaivamaara is null then ''[S0]'' when oik.siirtopaivamaara < oik.alkamispaivamaara then ''[SS]'' else ''[SL]'' end)
, (select concat(''[T'', (case when min(toisOikTyypKood.koodi) is null then ''00'' when len(min(toisOikTyypKood.koodi)) < 2 then concat(''0'',min(toisOikTyypKood.koodi)) else min(toisOikTyypKood.koodi) end), '']'')
from opiskeluoikeus as toinenOikeus
join opiskeluoikeuden_tyyppi as toisOikTyypKood on toisOikTyypKood.id = toinenOikeus.op_oikeuden_tyyppiId
where toinenOikeus.opiskelijaId = oik.opiskelijaId and toinenOikeus.id != oik.id
and suorTaiHyvLukPvm between toinenOikeus.alkamispaivamaara and isnull(toinenOikeus.paattymispaivamaara, ''9999-12-31''))
,(case when suorTaiHyvLukPvm < oik.alkamispaivamaara then concat(''[V1]:'', suorTaiHyvLukPvm, ''<'', oik.alkamispaivamaara, '' ero '', datediff(day, suorTaiHyvLukPvm, oik.alkamispaivamaara),''pv'')
when suorTaiHyvLukPvm <= oik.paattymispaivamaara then concat(''[V2]:'', oik.alkamispaivamaara, ''<='', suorTaiHyvLukPvm, ''<='', oik.paattymispaivamaara)
else concat(''[V3]:'', suorTaiHyvLukPvm , ''>'', oik.paattymispaivamaara, '' ero '', datediff(day, oik.paattymispaivamaara, suorTaiHyvLukPvm), ''pv'') end)
, (case when oik.siirtopaivamaara is null then '' | SiirtoPvm=null'' else concat('' | SiirtoPvm='', oik.siirtopaivamaara) end)
)
as lisaTieto
, suorTaiHyvLukPvm as virhTiedPvm
from (
select suorId from #suorPaatTiet where suorEiKasSyy in (''null'',''laji3ok'') and suorOikId is not null
except
(select suorId from #suorPaatTiet
join opiskeluoikeusJakso as jak on jak.opiskeluoikeusId = suorOikId
where suorEiKasSyy in (''null'',''laji3ok'') and suorOikId is not null
and suorTaiHyvLukPvm between jak.alkamispaivamaara and isnull(jak.paattymispaivamaara, ''9999-12-31''))
) as virhetapaus
join #suorPaatTiet as ilmoitettava on ilmoitettava.suorId = virhetapaus.suorId
join opintosuoritus as suor on suor.id = ilmoitettava.suorId
join opiskeluoikeus as oik on oik.id = suorOikId
join opiskeluoikeuden_tyyppi as oikTyypKood on oikTyypKood.id = oik.op_oikeuden_tyyppiId
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where (oik.siirtopaivamaara is null
or (oik.siirtopaivamaara < oik.alkamispaivamaara
--"oik.siirtopaivamaara < oik.alkamispaivamaara" -> siirtopaivamaara tarkoittaa että on saapuva opiskelija (lähtenyt muualta ennen tämän oikeuden alkua)
--suoritus on ennen siirtopäivämäärää (jossa on läh. opiskeluoikeuden paatPvm)
---> ei nosteta puutetta, jos suoritettu ennen tätä opiskeluoikeutta
---> nostetaan puute jos on suoritettu tämän opiskeluoikeuden alun jälkeen (ja siis puuttuu opiskeluoikeusjakso)
and suorTaiHyvLukPvm >= oik.alkamispaivamaara)
or oik.siirtopaivamaara >= oik.alkamispaivamaara
--täältä lähtenyt opiskelija
-->nostetaan puute olipa suoritettu milloin tahansa (kun siis puuttuu opiskeluoikeusjakso)
)
'+CHAR(13)
end;

-- 2025-04-11/Jouko Tiihonen: CSCTIE-1104 Poistetaan puutetarkistukset 4013 ja 4017

-- CSCTIE-700 lisäys
set @t_kuvaus = N'Opintosuoritus (suorituspäivämäärä) ajalla, jolloin opiskeluoikeudella ei ole aktiivista opiskeluoikeuden tilaa';
if @tarkistuskoodi=4014 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4014)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat((case when oikTyypKood.koodi is null then ''00'' when len(oikTyypKood.koodi) < 2 then concat(''0'',oikTyypKood.koodi) else oikTyypKood.koodi end)
,(case when suorOnkHyvLuet = 0 then ''[H0]'' else ''[H1]'' end)
,(case when oik.siirtopaivamaara is null then ''[S0]'' when oik.siirtopaivamaara < oik.alkamispaivamaara then ''[SS]'' else ''[SL]'' end)
, (select concat(''[T'', (case when min(toisOikTyypKood.koodi) is null then ''00'' when len(min(toisOikTyypKood.koodi)) < 2 then concat(''0'',min(toisOikTyypKood.koodi)) else min(toisOikTyypKood.koodi) end), '']'')
from opiskeluoikeus as toinenOikeus
join opiskeluoikeuden_tyyppi as toisOikTyypKood on toisOikTyypKood.id = toinenOikeus.op_oikeuden_tyyppiId
where toinenOikeus.opiskelijaId = oik.opiskelijaId and toinenOikeus.id != oik.id
and suorTaiHyvLukPvm between toinenOikeus.alkamispaivamaara and isnull(toinenOikeus.paattymispaivamaara, ''9999-12-31''))
,(case when suorTaiHyvLukPvm < oik.alkamispaivamaara then concat(''[V1]:'', suorTaiHyvLukPvm, ''<'', oik.alkamispaivamaara, '' ero '', datediff(day, suorTaiHyvLukPvm, oik.alkamispaivamaara),''pv'')
when suorTaiHyvLukPvm <= oik.paattymispaivamaara then concat(''[V2]:'', oik.alkamispaivamaara, ''<='', suorTaiHyvLukPvm, ''<='', oik.paattymispaivamaara)
else concat(''[V3]:'', suorTaiHyvLukPvm , ''>'', oik.paattymispaivamaara, '' ero '', datediff(day, oik.paattymispaivamaara, suorTaiHyvLukPvm), ''pv'') end)
, (case when oik.siirtopaivamaara is null then '' | SiirtoPvm=null'' else concat('' | SiirtoPvm='', oik.siirtopaivamaara) end)
)
as lisaTieto
, suorTaiHyvLukPvm as virhTiedPvm
from (
select suorId from #suorPaatTiet where suorEiKasSyy in (''null'',''laji3ok'') and suorOikId is not null
except
(select suorId from #suorPaatTiet
join op_oikeuteen_liittyva_tila as til on til.opiskeluoikeusId = suorOikId
where suorEiKasSyy in (''null'',''laji3ok'') and suorOikId is not null
and suorTaiHyvLukPvm between til.alkamispaivamaara and isnull(til.paattymispaivamaara, ''9999-12-31''))
) as virhetapaus
join #suorPaatTiet as ilmoitettava on ilmoitettava.suorId = virhetapaus.suorId
join opintosuoritus as suor on suor.id = ilmoitettava.suorId
join opiskeluoikeus as oik on oik.id = suorOikId
join opiskeluoikeuden_tyyppi as oikTyypKood on oikTyypKood.id = oik.op_oikeuden_tyyppiId
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where (oikTyypKood.koodi != ''18'' and ( --18 = erillisoikeus
oik.siirtopaivamaara is null
or (oik.siirtopaivamaara < oik.alkamispaivamaara
--"oik.siirtopaivamaara < oik.alkamispaivamaara" -> siirtopaivamaara tarkoittaa että on saapuva opiskelija (lähtenyt muualta ennen tämän oikeuden alkua)
--suoritus on ennen siirtopäivämäärää (jossa on läh. opiskeluoikeuden paatPvm)
---> ei nosteta puutetta, jos suoritettu ennen tätä opiskeluoikeutta
---> nostetaan puute jos on suoritettu tämän opiskeluoikeuden alun jälkeen (ja siis puuttuu op_oikeuteen_liittyva_tila)
and suorTaiHyvLukPvm >= oik.alkamispaivamaara)
or oik.siirtopaivamaara >= oik.alkamispaivamaara
--täältä lähtenyt opiskelija
-->nostetaan puute olipa suoritettu milloin tahansa (kun siis puuttuu op_oikeuteen_liittyva_tila)
))
'+CHAR(13)
end;

-- CSCTIE-703 lisäys
set @t_kuvaus = N'Hyväksilukemispvm on pienempi kuin suorituspvm';
if @tarkistuskoodi=4015 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4015)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, (case when hyvaksilukupaivamaara is null then ''Hyväksiluvulta puuttuu hyväksilukupvm'' else
concat(cast(hyvaksilukupaivamaara as nvarchar(20)), '' < '', cast(suorituspaivamaara as nvarchar(20)),
'', ero '', cast(dateDiff(day, hyvaksilukupaivamaara, suorituspaivamaara) as nvarchar(6)), '' pv'') end) as lisaTieto
, (case when hyvaksilukupaivamaara is null then ''9999-12-31'' else suorituspaivamaara end) as virhTiedPvm
from opintosuoritus as suor
left outer join opiskeluoikeus as oik on oik.id = suor.opiskeluoikeusId
join opsuorituksen_hyvaksiluku as hyvLuk on hyvLuk.opintosuoritusId = suor.id
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where hyvaksilukupaivamaara is null or hyvaksilukupaivamaara < suorituspaivamaara
'+CHAR(13)
end;

--lisäykset 02/2024, CSCTIE-777
set @t_kuvaus = N'Tutkinnon (suorituksen laji 1) suorituspvm on monta (6) kuukautta tulevaisuudessa.';
if @tarkistuskoodi=4016 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,4016)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''opintosuoritusAvain'' as avainsarake
, suor.avain
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, concat(cast(isnull(hyvaksilukupaivamaara, suorituspaivamaara) as nvarchar(20))
, ''vs'', cast(cast(dateadd(month, 6, getdate()) as date) as nvarchar(20))) as lisaTieto
, null as virhTiedPvm
from opintosuoritus as suor
left outer join opiskeluoikeus as oik on oik.id = suor.opiskeluoikeusId
join opsuorituksen_hyvaksiluku as hyvLuk on hyvLuk.opintosuoritusId = suor.id
join opiskelija as opis on opis.id = suor.opiskelijaId
join henkilo as henk on henk.id = opis.henkiloId
where isnull(hyvaksilukupaivamaara, suorituspaivamaara) > cast(dateadd(month, 6, getdate()) as date)
'+CHAR(13)
end;

-- 2025-04-11/Jouko Tiihonen: CSCTIE-1104 Poistetaan puutetarkistukset 4013 ja 4017

--
-- LIIKKUVUUSJAKSOT
--
-- Liikkuvuusjakson saapuvan opiskelijan koulutusaste suomalaisessa korkeakoulussa:
-- Ilmoitus puutelistalla, jos tieto puuttuu jaksolta, jonka suunta saapuva ja liikkuvuusjakson päättymispäivämäärä on menneisyydessä.
if @tarkistuskoodi=5001 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,5001)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Liikkuvuusjakson saapuvan opiskelijan koulutusaste suomalaisessa korkeakoulussa'' as kuvaus
, ''liikkuvuusjaksoAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Liikkuvuusjakso AS a
INNER JOIN Liikkuvuuden_suunta AS ks on ks.ID=a.Liikkuvuuden_suuntaID
WHERE ks.Koodi=''2'' --Saapuva
AND a.Opiskeluoikeuden_tyyppiID IS NULL --"koulutusaste" puuttuu
AND GETDATE() > coalesce(a.Paattymispaivamaara,''9999-9-9'') --päättynyt menneisyydessä
-- ei kuitenkaan liian varhaisia (2007 tai jälkeen *alkaneet* (vs voimassa olleet))
AND coalesce(a.Alkamispaivamaara,''1900-1-1'') >= ''2007-01-01''
'+CHAR(13)
end;

-- TIE-100 (tehdessä huomattu): Liikkuvuusjakson päättymispäivä on varhaisempi kuin alkamispäivä
if @tarkistuskoodi=5002 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,5002)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Liikkuvuusjakson päättymispäivä on varhaisempi kuin alkamispäivä'' as kuvaus
, ''liikkuvuusjaksoAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Liikkuvuusjakso AS a
WHERE 1=1
AND a.Paattymispaivamaara < a.Alkamispaivamaara
'+CHAR(13)
end;

-- TIE-100: Liikkuvuusjakson liikkuvuusohjelma puuttuu 87 pv tai pidemmältä jaksolta
if @tarkistuskoodi=5003 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,5003)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Liikkuvuusjakson liikkuvuusohjelma puuttuu 87 pv tai pidemmältä jaksolta'' as kuvaus
, ''liikkuvuusjaksoAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Liikkuvuusjakso AS a
WHERE 1=1
-- Liikkuvuusohjelma puuttuu
AND a.LiikkuvuusohjelmaID is null
-- 87 pv tai enemmän kestänyt
AND DATEDIFF(day,a.Alkamispaivamaara,coalesce(a.Paattymispaivamaara,''9999-9-9'')) >= 87
-- ei kuitenkaan liian varhaisia (2016 tai jälkeen *alkaneet* (vs voimassa olleet))
AND coalesce(a.Alkamispaivamaara,''1900-1-1'') >= ''2016-01-01''
'+CHAR(13)
end;

-- TIE-100: Liikkuvuusjakson liikkuvuuden tyyppi puuttuu
if @tarkistuskoodi=5004 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,5004)
if @tarkistuskoodi is null begin
set @sql=@sql+N'union all'
end
set @sql=@sql+N'
select GETDATE() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, ''Liikkuvuusjakson liikkuvuuden tyyppi puuttuu'' as kuvaus
, ''liikkuvuusjaksoAvain'' as avainsarake
, a.Avain
, coalesce((select top 1 p.avain from Opiskelija p where p.ID=a.OpiskelijaID),''#Puuttuu#'') as opiskelijaAvain
, coalesce((select top 1 o.avain from Opiskeluoikeus o where o.ID=a.OpiskeluoikeusID),''#Puuttuu#'') as opiskeluOikeusAvain
, null as Lisatieto
, null as virhTiedPvm
FROM Liikkuvuusjakso AS a
WHERE 1=1
-- Liikkuvuuden tyyppi puuttuu
AND a.Liikkuvuuden_tyyppiID is null
-- ei kuitenkaan liian varhaisia (2016 tai jälkeen *alkaneet* (vs voimassa olleet))
AND coalesce(a.Alkamispaivamaara,''1900-1-1'') >= ''2016-01-01''
'+CHAR(13)
end;

--CSCTIE-902
set @t_kuvaus = N'Liikkuvuusjakson kohdemaana on Suomi';
if @tarkistuskoodi=5005 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,5005)
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''liikkuvuusjakso.alkamispaivamaara'' as avainsarake
, liikJaks.alkamispaivamaara
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, substring(concat(''Liikkuvuusjakso ajalla '',
cast(liikJaks.alkamispaivamaara as date), '' - '', cast(liikJaks.paattymispaivamaara as date)
),1,250) as lisaTieto
, liikJaks.paattymispaivamaara as virhTiedPvm
from liikkuvuusjakso as liikJaks
join liikkuvuuden_suunta as liikSuun on liikSuun.id = liikJaks.liikkuvuuden_suuntaId
join maa on maa.id = liikJaks.maaId
join opiskeluoikeus as oik on oik.id = liikJaks.opiskeluoikeusId
join opiskelija as opis on opis.id = oik.opiskelijaId
where liikSuun.koodi = ''1'' -- 1=lähtevä
and maa.koodi = ''246''
'+CHAR(13)
end;

--CSCTIE-1033
set @t_kuvaus = N'Opiskelijalla on useampi täysin samanlainen liikkuvuusjakso';
if @tarkistuskoodi=5006 or @tarkistuskoodi is null begin
set @t_koodi = coalesce(@tarkistuskoodi,5006)
set @sql=@sql+N'
select getdate() as pvm
, ''PUUTE'' as tyyppi
, '''+cast(@t_koodi as varchar)+''' as virhekoodi
, '''+@t_kuvaus+''' as kuvaus
, ''liikkuvuusjakso.alkamispaivamaara'' as avainsarake
, liikJaks.alkamispaivamaara
, opis.avain as opiskelijaAvain
, oik.avain as opiskeluOikeusAvain
, substring(concat(''Liikkuvuusjakso ajalla '',
cast(liikJaks.alkamispaivamaara as date), '' - '', cast(liikJaks.paattymispaivamaara as date)
, '' '', lkm, '' kpl ''
, (case when maxOpiskeluoikeusId = minOpiskeluoikeusId then ''yhdellä opiskeluoikeudella'' else ''useammalla opiskeluoikeudella yhteensä'' end))
,1,250) as lisaTieto
, liikJaks.paattymispaivamaara as virhTiedPvm
from (
select opiskelijaId
, liikkuvuusohjelmaId, liikkuvuuden_suuntaId, maaId, liikkuvuuden_tyyppiId, koulutusmoduulitunniste
, valtakunn_koul_mod_tunniste, alkamispaivamaara, paattymispaivamaara
, count(1) as lkm, max(opiskeluoikeusId) as maxOpiskeluoikeusId, min(opiskeluoikeusId) as minOpiskeluoikeusId
from liikkuvuusjakso
group by opiskelijaId
, liikkuvuusohjelmaId, liikkuvuuden_suuntaId, maaId, liikkuvuuden_tyyppiId, koulutusmoduulitunniste
, valtakunn_koul_mod_tunniste, alkamispaivamaara, paattymispaivamaara
having count(1) > 1
) as liikJaks
--join liikkuvuuden_suunta as liikSuun on liikSuun.id = liikJaks.liikkuvuuden_suuntaId
--join maa on maa.id = liikJaks.maaId
left outer join opiskeluoikeus as oik on oik.id = maxOpiskeluoikeusId
join opiskelija as opis on opis.id = liikJaks.opiskelijaId
'+CHAR(13)
end;