Page History

 -- CIMO_lyhyt_saapuva:

DECLARE    @vuosi varchar(4)
SET @vuosi = '2016'

create table #param (
    vuosi varchar(4),
)
;
insert into #param (vuosi)
    select
    @vuosi as vuosi
;

-- Temp-taulu tiedonkeruun tietoja varten
create table #cimopisk (
    oppilaitos varchar(5), -- Oppilaitoskoodi
    hetu varchar(100), --henkilö- tai muu tunnus
    sp varchar(1), -- sukupuoli
    koulutusala varchar(4), -- koulutusala
    opintoaste varchar(1), -- (vain YO)
    lahtomaa varchar(3),
    kesto varchar(4), --liikkuvuuden kesto
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK)
    tyyppi varchar(2), --liikkuvuuden tyyppi
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskelija_id ON #cimopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_liikkuvuusjakso_id ON #cimopisk (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskeluoikeus_id ON #cimopisk (opiskeluoikeus_id)
;

print convert(varchar,getdate(),120)+' CREATE temp-taulu param ja cimopisk'
;

---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla liikkuvuusjakso joka on
--   - jonka (liikkuvuuden )suunta on 2=Saapuva
--   - kestänyt enintään [5,7] ja vähintään 86 (87-1) päivää
-- * jakson aloituspäivämäärän vuosi on tilastovuosi
---

INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = lj.OpiskelijaID
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- KV vaihto tyyppi , Ei Liikkuvuusjakson opiskeluoikeustyyppi
    
WHERE 1=1
AND otk.koodi = '9' -- Kansainvälinen vaihto

-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- enintään [5,7] ja vähintään 86 päivää
and coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
    between 5 and 86
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)


-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )
;

print convert(varchar,getdate(),120)+' liikkuvuus- ja opiskelijatiedot'
;


-- Lyhyt saapuva ilman opiskeluoikeutta

INSERT into #cimopisk (    
    oppilaitos,hetu,sp,koulutusala,opintoaste,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
        inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
        inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID    
WHERE 
        p.ID NOT IN (SELECT C.opiskelija_id FROM #cimopisk C)

-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- enintään [5,7] ja vähintään 86 päivää
and coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
    between 5 and 86
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )

;

---
-- Opiskeluoikeustiedot
---
/*
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
*/

-- valitaan yksi per opiskelija, jos ei löytynyt jo ID yllä!
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join virta.dbo.Op_oikeuden_ensisijaisuus oe on oe.OpiskeluoikeusID=o.ID
where 1=1
-- ensisijainen alkamispäivänä
and T.jakso_alkupvm between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-9-9')
;

-- koulutuskoodi
update T
set koulutuskoodi=(select k.Koodi from virta.dbo.Tutkintonimike k where k.ID=oj.TutkintonimikeID)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.opiskeluoikeus_id is not null
-- jakso voimassa alkupäivänä
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;

-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID AND OO.ID = T.opiskeluoikeus_id
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
where T.opiskeluoikeus_id is not null   AND T.opiskelija_id is not null  
AND K.Versio = 'ohjausala'
;

update T
set koulutusala=k.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
where T.koulutusala is null  AND T.opiskelija_id is not null  
AND K.Versio = 'ohjausala'
;



-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L on L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.opiskeluoikeus OO ON T.opiskeluoikeus_id = OO.id
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = L.Opiskeluoikeuden_tyyppiID 
where T.opiskeluoikeus_id is not null
AND     T.opiskeluoikeusAvain = OO.avain
AND     L.avain = T.liikkuvuusjaksoavain
;

-- Jos opintoaste on null edellisen jälkeen
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.opintoaste is null AND T.opiskeluoikeus_id is null 
;

-- Jos koultyp on null edellisen jälkeen, ei pitäisi vaikuttaa yliopistoihin mikäli ei ole väärällä tyypillä tuotu
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.koultyp is null AND T.opiskeluoikeus_id is null 
;

---
-- TULOS
---
select * from #cimopisk T
order by hetu

 --CIMO_pitka_saapuva:

DECLARE    @vuosi varchar(4)
SET @vuosi = '2016'

create table #param (
    vuosi varchar(4),
)
;
insert into #param (vuosi)
    select
    @vuosi as vuosi
;

-- Temp-taulu tiedonkeruun tietoja varten
create table #cimopisk (
    oppilaitos varchar(5), -- Oppilaitoskoodi
    hetu varchar(100), --henkilö- tai muu tunnus
    sp varchar(1), -- sukupuoli
    koulutusala varchar(4), -- koulutusala
    opintoaste varchar(1), -- (vain YO)
    liikkuvuusohjelma varchar(3),
    kansalaisuus varchar(3),
    lahtomaa varchar(3),
    kesto varchar(4), --liikkuvuuden kesto
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK)
    tyyppi varchar(2), --liikkuvuuden tyyppi
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskelija_id ON #cimopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_liikkuvuusjakso_id ON #cimopisk (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskeluoikeus_id ON #cimopisk (opiskeluoikeus_id)
;

print convert(varchar,getdate(),120)+' CREATE temp-taulu param ja cimopisk'
;

---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla liikkuvuusjakso joka on
--   - jonka (liikkuvuuden )suunta on 2=Saapuva
--   - kestänyt vähintään 87 päivää
-- * jakson aloituspäivämäärän vuosi on tilastovuosi
---

INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,liikkuvuusohjelma,kansalaisuus,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,liikkuvuusohjelma = (select top 1 k.Koodi from virta.dbo.Liikkuvuusohjelma k where k.ID=lj.LiikkuvuusohjelmaID)
,kansalaisuus = (
    -- TODO: jos monta?
    select top 1 k.Koodi
    from virta.dbo.Henkilon_maa hm
    join virta.dbo.Maan_rooli mr on mr.ID=hm.Maan_rooliID and mr.Koodi='1'--Kansalaisuus
    join virta.dbo.Maa k on k.ID=hm.MaaID
    where hm.HenkiloID=h.ID
    )
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!


FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = lj.OpiskelijaID
    INNER JOIN virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=OO.id
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- KV vaihto tyyppi , Ei Liikkuvuusjakson opiskeluoikeustyyppi
WHERE 1=1
AND otk.koodi = '9' -- Kansainvälinen vaihto
-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään 87 päivää
and 87 <= coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

AND lj.Alkamispaivamaara BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')

-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
                            
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )
;
print convert(varchar,getdate(),120)+' liikkuvuus- ja opiskelijatiedot'
;


-- Pitkä saapuva ilman opiskeluoikeutta

INSERT into #cimopisk (    
    oppilaitos,hetu,sp,koulutusala,opintoaste,liikkuvuusohjelma,kansalaisuus,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,liikkuvuusohjelma = (select top 1 k.Koodi from virta.dbo.Liikkuvuusohjelma k where k.ID=lj.LiikkuvuusohjelmaID)
,kansalaisuus = (
    -- TODO: jos monta?
    select top 1 k.Koodi
    from virta.dbo.Henkilon_maa hm
    join virta.dbo.Maan_rooli mr on mr.ID=hm.Maan_rooliID and mr.Koodi='1'--Kansalaisuus
    join virta.dbo.Maa k on k.ID=hm.MaaID
    where hm.HenkiloID=h.ID
    )
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
        inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
        inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID    
WHERE 
        p.ID NOT IN (SELECT C.opiskelija_id FROM #cimopisk C)

-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään 87 päivää
and 87 <= coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )

AND    p.id NOT IN (
  
            SELECT OO2.opiskelijaid 
            FROM virta.dbo.Opiskeluoikeus OO2                 
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
                INNER JOIN virta.dbo.Op_oik_jaks_liittyva_luokittelu OL ON OL.OpiskeluoikeusjaksoID = OJ2.id
                INNER JOIN virta.dbo.Opiskeluoikeuden_luokittelu OOL ON OOL.id = OL.Opiskeluoikeuden_luokitteluID
            WHERE     OOL.koodi = '7'            
            AND OJ2.Alkamispaivamaara BETWEEN  lj.Alkamispaivamaara  AND COALESCE(OJ2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            
            )


 ; 




---
-- Opiskeluoikeustiedot
---
/*
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
*/

-- valitaan yksi per opiskelija, jos ei löytynyt jo ID yllä!
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join virta.dbo.Op_oikeuden_ensisijaisuus oe on oe.OpiskeluoikeusID=o.ID
where 1=1
-- ensisijainen alkamispäivänä
and T.jakso_alkupvm between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-9-9')
;

-- koulutuskoodi
update T
set koulutuskoodi=(select k.Koodi from virta.dbo.Tutkintonimike k where k.ID=oj.TutkintonimikeID)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.opiskeluoikeus_id is not null
-- jakso voimassa alkupäivänä
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;


-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=K.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID AND OO.ID = T.opiskeluoikeus_id
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
    INNER JOIN virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=OO.id
where T.opiskeluoikeus_id is not null  AND T.opiskelija_id is not null    
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
AND K.Versio = 'ohjausala'
;


update T
set koulutusala=K.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
    
where T.koulutusala IS NULL  AND T.opiskelija_id is not null  
AND K.Versio = 'ohjausala'
;



-- opintoaste, koultyp jos on opiskeluoikeusid ensisijaisen mukaan eli ei NULL
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.opiskeluoikeus OO ON T.opiskeluoikeus_id = OO.id
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.opiskeluoikeus_id is not null
AND     T.opiskeluoikeusAvain = OO.avain
AND     L.avain = T.liikkuvuusjaksoavain
;


-- Jos opintoaste on null edellisen jälkeen
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.opintoaste is null AND T.opiskeluoikeus_id is null
;


-- Jos koultyp on null edellisen jälkeen, ei pitäisi vaikuttaa yliopistoihin mikäli ei ole väärällä tyypillä tuotu
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.koultyp is null AND T.opiskeluoikeus_id is null
;

---
-- TULOS
---
select * 
from #cimopisk T
order by hetu
;

 --CIMO_pitka_lahteva:

DECLARE    @vuosi varchar(4)
SET @vuosi = '2016'

create table #param (
    vuosi varchar(4),
)
;
insert into #param (vuosi)
    select
    @vuosi as vuosi
;
--select * from #param

-- Temp-taulu tiedonkeruun tietoja varten
create table #cimopisk (
    oppilaitos varchar(5), -- Oppilaitoskoodi // 5-numeroinen
    hetu varchar(100), --henkilö- tai muu tunnus    vapaateksti // jollei henkilötunnusta löydy, käytetään Opiskelija.avain
    sp varchar(1), -- sukupuoli // 1, 2, tai 9
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    liikkuvuusohjelma varchar(3), --// 01-16 tai 99
    kohdemaa varchar(3), --// 000-999
    kesto varchar(4), --liikkuvuuden kesto // päiviä // Vaihdon tulee olla kestänyt vähintään 87 päivää, jotta tulee poimituksi. Kokonaiskesto raportoidaan päivinä ja vaihdon aloituspäivämäärä määrä raportointivuoden.
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
    tyyppi varchar(2), --liikkuvuuden tyyppi // 1, 2
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskelija_id ON #cimopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_liikkuvuusjakso_id ON #cimopisk (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskeluoikeus_id ON #cimopisk (opiskeluoikeus_id)
;

print convert(varchar,getdate(),120)+' CREATE temp-taulu param ja cimopisk'
;

---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla liikkuvuusjakso joka on
--   - jonka (liikkuvuuden )suunta on 1=Lähtevä
--   - kestänyt vähintään 87 päivää
-- * jakson aloituspäivämäärän vuosi on tilastovuosi
---

INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,liikkuvuusohjelma,kohdemaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,liikkuvuusohjelma = (select top 1 k.Koodi from virta.dbo.Liikkuvuusohjelma k where k.ID=lj.LiikkuvuusohjelmaID)
,kohdemaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID

WHERE 1=1
-- suunta 1=Lähtevä
and 1 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään 87 päivää
and 87 <= coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

-- Lähtevillä vain tutkinto ei muita
AND    p.id IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','6','7')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')

            and year(lj.Alkamispaivamaara) = (select vuosi from #param)

            )
;

print convert(varchar,getdate(),120)+' liikkuvuus- ja opiskelijatiedot'
;

---
-- Opiskeluoikeustiedot
---
/*
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
*/

-- valitaan yksi per opiskelija, jos ei löytynyt jo ID yllä!
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join virta.dbo.Op_oikeuden_ensisijaisuus oe on oe.OpiskeluoikeusID=o.ID
where 1=1
-- ensisijainen alkamispäivänä
and T.jakso_alkupvm between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-9-9')
;

-- koulutuskoodi
update T
set koulutuskoodi=(select k.Koodi from virta.dbo.Tutkintonimike k where k.ID=oj.TutkintonimikeID)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.opiskeluoikeus_id is not null
-- jakso voimassa alkupäivänä
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;

-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.okmohjausala
from #cimopisk T
inner join Yhteiset.Koodistot.Koulutusluokitus k on k.Koodi=T.koulutuskoodi
where T.opiskeluoikeus_id is not null and T.koulutuskoodi is not null
-- voimassa alkupäivänä
and T.jakso_alkupvm between k.Alkamispaivamaara and coalesce(k.Paattymispaivamaara,'9999-01-01')
;

-- Lyhyt lähtevä joka on alunperin saapuva vaihto Suomeen, tai kotimainen vaihto
-- Jos on Saapunut opiskeluoikeustyyppi = 9 ja  Lyhyt lähtevä
-- koulutusala (ohjausala)  lähtevän opiskeluoikeudesta (opiskeluoikeustyyppi = 9)
-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID            -- koulutusala (ohjausala)  lähtevän opiskeluoikeudesta (opiskeluoikeustyyppi = 9)
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- !!

where T.opiskelija_id is not null  
AND otk.koodi IN ('9','8')  -- Kansainvälinen vaihto ja kotimainen vaihto  -- !!
AND T.koulutuskoodi IS NULL 
AND K.Versio = 'ohjausala'
;


-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    when '1'--Ammattikorkeakoulututkinto
     then case
        when o.Aikuiskoulutus='1' then '2'--=Amk-tutkintoon johtava aikuiskoulutus
        when o.Aikuiskoulutus='2' then '1'--=Amk-tutkintoon johtava nuorten koulutus
        else '1' end
    when '3'  then '6'--=Ylempi ammattikorkeakoulututkinto
    --when '12' then '3'--=Erikoistumisopinnot
    --when '15' then '5'--=Ammatillinen opettajankoulutus
    --when '19' then '7'--=Erikoistumiskoulutus
    end
)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID=o.Op_oikeuden_tyyppiID
where T.opiskeluoikeus_id is not null
;


-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
    
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L on L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = L.Opiskeluoikeuden_tyyppiID   -- koultyp lähtevän jakson opiskeluoikeudesta (1)    
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = T.opiskelija_id    -- !!
    inner join virta.dbo.Opiskeluoikeuden_tyyppi oootk on oootk.ID = OO.Op_oikeuden_tyyppiID  -- !!    
where T.opiskelija_id is not null
AND oootk.koodi IN ('9','8')  -- Kansainvälinen vaihto ja kotimainen vaihto  -- !!
AND T.koulutuskoodi IS NULL 

---
-- TULOS
---
select * from #cimopisk T
order by hetu
;