Erilliset opettajan opinnot -tiedonkeruu

-- Luotu oma keräystaulu Erilliset opettajat pätevyystiedoille Virta-kannassa:

CREATE TABLE [TK_eriope_patevyydet](
    [Oppilaitostunnus] [varchar](5) NOT NULL,
    [oppilaitosnimi] [varchar](50) NOT NULL,
    [koulutuksen_sijaintikunta] [varchar](3) NOT NULL,
    [kunta] [varchar](30) NOT NULL,
    [Erilliset_opettajan_opinnot] [varchar](3) NOT NULL,
    [patevyys_selite] [varchar](250) NOT NULL,
    [suoritusvuosi_ja_kuukausi] [varchar](6) NOT NULL,
    [suorituspvm] [date] NOT NULL,
    [Henkilotunnus] [varchar](11) NOT NULL,
    [Henkilon_nimi] [varchar](60) NOT NULL,
    [Sukunimi] [varchar](50) NULL,
    [Etunimet] [varchar](50) NULL,
    [opintosuorituksen_avain] [varchar](50) NULL,
    [opiskelija_avain] [varchar](50) NULL,
    [paivityspvm] [smalldatetime] NULL,
    [DB] [varchar](20) NULL
)



-- Tauluun tuodaan uusin vuoden (@vuosi) data-setti. Edellisen päivän tiedot pois.

CREATE TABLE #Lopputulos_patevyys(
        erilliset_opettajan_opinnot varchar(2),
        patevyys_selite varchar(250),
        suoritusvuosi_ja_kuukausi varchar(6),
        suorituspvm date,
        henkilotunnus varchar(12),
        henkilon_nimi varchar(200),
        sukunimi varchar(100),
        etunimet varchar(100),
        opintosuorituksen_avain varchar(100),
        opiskelija_avain varchar(100),
        db varchar(10),
        opintosuoritusid int,
        opiskelijaid int,
        patevyysid int,
        oppilaitostunnus varchar(5),
        oppilaitosnimi varchar(100),
        koodi varchar(3),
        kunta varchar(200)
)

CREATE TABLE #patevyys(
        erilliset_opettajan_opinnot varchar(2),
        patevyys_selite varchar(250),
        suoritusvuosi_ja_kuukausi varchar(6),
        suorituspvm date,
        henkilotunnus varchar(12),
        henkilon_nimi varchar(200),
        sukunimi varchar(100),
        etunimet varchar(100),
        opintosuorituksen_avain varchar(100),
        opiskelija_avain varchar(100),
        db varchar(10),
        opintosuoritusid int,
        opiskelijaid int,
        patevyysid int
)

INSERT INTO #patevyys ( erilliset_opettajan_opinnot, patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,henkilotunnus,henkilon_nimi,sukunimi,etunimet,opintosuorituksen_avain,opiskelija_avain,db,opintosuoritusid,opiskelijaid,patevyysid  )
SELECT     DISTINCT 
             p.Koodi AS Erilliset_opettajan_opinnot
            , p.Nimi AS patevyys_selite /* Selite ei tiedonkeruussa */
            , substring(convert(varchar(10),os.Suorituspaivamaara),1,4) + substring(convert(varchar(10),os.Suorituspaivamaara),6,2) AS suoritusvuosi_ja_kuukausi
            , os.Suorituspaivamaara AS suorituspvm
            , h.Henkilotunnus AS Henkilotunnus
            , H.Sukunimi + ' ' + H.Etunimet AS Henkilon_nimi
            , H.Sukunimi
            , H.Etunimet
            , OS.avain AS opintosuorituksen_avain
            , O.avain AS opiskelija_avain
            , HP.DB
            , OS.id
            , O.id
            , P.ID
FROM Virta.dbo.Henkilon_patevyys HP
        JOIN  Virta.dbo.Henkilo H ON H.ID = HP.HenkiloID
        JOIN  Virta.dbo.Opintosuoritus OS ON HP.OpintosuoritusID = OS.ID
        JOIN  Virta.dbo.Patevyys P ON HP.Patevyysid = P.ID
        JOIN  Virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
WHERE     OS.Suorituspaivamaara BETWEEN @vuosi+'-01-01' AND @vuosi+'-12-31'         
AND		P.Koodi IN ('iu','ir','iy','im','ix','rv','va') /* Erilliset opettaja pätevyyskoodit  rv, va lisätty 2023 */	 


INSERT INTO #Lopputulos_patevyys (erilliset_opettajan_opinnot,patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,henkilotunnus,henkilon_nimi,sukunimi,etunimet,opintosuorituksen_avain,opiskelija_avain,db,opintosuoritusid,opiskelijaid,patevyysid )
SELECT erilliset_opettajan_opinnot,patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,henkilotunnus,henkilon_nimi,sukunimi,etunimet,opintosuorituksen_avain,opiskelija_avain,db,opintosuoritusid,opiskelijaid,patevyysid
FROM  #patevyys


CREATE TABLE #sijaintikunta (
        opintosuoritusid int,
        oppilaitostunnus varchar(5),
        oppilaitosnimi varchar(100),
        koodi varchar(3),
        kunta varchar(200) ,
        opiskelijaid int,
        erilliset_opettajan_opinnot varchar(2)
         
)


INSERT INTO #sijaintikunta ( opintosuoritusid, oppilaitostunnus, oppilaitosnimi, koodi, kunta , opiskelijaid, erilliset_opettajan_opinnot)
SELECT DISTINCT p.opintosuoritusid, ORG.Koodi, ORG.Nimi ,k.koodi, k.nimi , p.opiskelijaid, p.erilliset_opettajan_opinnot
FROM  #patevyys p
            JOIN virta.dbo.Op_suorituksen_organisaatio op_org ON op_ORG.OpintosuoritusID = p.opintosuoritusid
            JOIN virta.dbo.Organisaatio ORG ON ORG.ID = op_org.OrganisaatioID
            JOIN virta.dbo.Opiskeluoikeus OO ON p.opiskelijaid = oo.OpiskelijaID
            JOIN virta.dbo.Opiskeluoikeusjakso OJ ON OJ.OpiskeluoikeusID = OO.id                 
            JOIN  virta.dbo.Kunta K ON K.ID = OJ.KuntaID
WHERE k.koodi <> '999'
AND OJ.id IN (		 
				SELECT OJP.OpiskeluoikeusjaksoID
				FROM [virta].[dbo].[Opiskeluoikeusjakson_patevyys] OJP 
						JOIN [virta].[dbo].Patevyys VP ON VP.id = OJP.PatevyysID
				WHERE VP.Koodi = p.erilliset_opettajan_opinnot
)

-- Jos tyhjä niin ensisijainen
INSERT INTO #sijaintikunta ( opintosuoritusid, oppilaitostunnus, oppilaitosnimi, koodi, kunta , opiskelijaid, erilliset_opettajan_opinnot)
SELECT DISTINCT p.opintosuoritusid, ORG.Koodi, ORG.Nimi ,k.koodi, k.nimi , p.opiskelijaid, p.erilliset_opettajan_opinnot
FROM  #patevyys p
			INNER JOIN virta.dbo.Op_suorituksen_organisaatio op_org ON op_ORG.OpintosuoritusID = p.opintosuoritusid
			INNER JOIN virta.dbo.Organisaatio ORG ON ORG.ID = op_org.OrganisaatioID
			INNER JOIN virta.dbo.Opiskeluoikeus OO ON p.opiskelijaid = oo.OpiskelijaID
			INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ ON OJ.OpiskeluoikeusID = OO.id				 
			INNER JOIN virta.dbo.Kunta K ON K.ID = OJ.KuntaID			 
WHERE p.opintosuoritusid NOT IN (SELECT S.opintosuoritusid FROM #sijaintikunta S)
AND k.koodi <> '999'
-- 2020-04-04 Ensisijaisuus päteyyden suoritus ajankohtana
and OO.ID in (
				select oe.OpiskeluoikeusID 
				from virta.dbo.Op_oikeuden_ensisijaisuus oe	
				where (p.suorituspvm) between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-01-01')				
			)



--SELECT * FROM #sijaintikunta
CREATE TABLE #dubbla (
        opiskelijaid int,
        koodi varchar(3),
        kk varchar(10),
        erilliset_opettajan_opinnot varchar(2)
)

INSERT INTO #dubbla (opiskelijaid, koodi,kk,erilliset_opettajan_opinnot)
SELECT p.opiskelijaid , s.koodi, p.db, p.erilliset_opettajan_opinnot
FROM #patevyys P
        JOIN #sijaintikunta S ON S.opintosuoritusid = p.opintosuoritusid
ORDER BY db, oppilaitostunnus, henkilotunnus


CREATE TABLE #poistettavat (
    kunta varchar(3),
    opintosuoritusid int
)



INSERT INTO #poistettavat (kunta,opintosuoritusid )
SELECT CASE
		
		WHEN S.oppilaitostunnus = '01901' AND s.koodi <> '091' THEN s.koodi
		WHEN S.oppilaitostunnus = '01903' AND s.koodi <> '853' THEN s.koodi
		WHEN S.oppilaitostunnus = '10122' AND s.koodi <> '837' THEN s.koodi
		WHEN S.oppilaitostunnus = '01906' AND s.koodi <> '272' THEN s.koodi
		WHEN S.oppilaitostunnus = '10089' AND s.koodi <> '853' THEN s.koodi
		WHEN S.oppilaitostunnus = '10088' AND s.koodi <> '167' THEN s.koodi
		WHEN S.oppilaitostunnus = '01918' AND s.koodi <> '698' THEN s.koodi  -- Lappi Rovaniemi
		WHEN S.oppilaitostunnus = '01904' AND s.koodi <> '564' THEN s.koodi  -- Oulu  
		WHEN S.oppilaitostunnus = '10103' AND s.koodi <> '091' THEN s.koodi  -- Taideyliopisto Helsinki 
		END
		AS kunta
		,s.opintosuoritusid

FROM #sijaintikunta s WHERE s.opiskelijaid IN (

			 SELECT opiskelijaid 
			 FROM  #dubbla
			GROUP BY opiskelijaid
			HAVING count(*)>1

	)
ORDER BY s.opiskelijaid



UPDATE L
SET L.kunta = S.kunta,
    L.koodi = S.koodi,
    L.oppilaitostunnus = S.oppilaitostunnus,
    L.oppilaitosnimi = S.oppilaitosnimi

FROM #Lopputulos_patevyys L
        JOIN #sijaintikunta S ON S.opintosuoritusid = L.opintosuoritusid
WHERE L.opintosuoritusid NOT IN (SELECT p.opintosuoritusid FROM #poistettavat p WHERE p.kunta = S.kunta AND p.opintosuoritusid = s.opintosuoritusid)
AND S.kunta NOT IN (SELECT p.kunta FROM #poistettavat p WHERE p.kunta = S.kunta AND p.opintosuoritusid = s.opintosuoritusid)





CREATE TABLE #TK_eriope_patevyydet (
	Oppilaitostunnus varchar(5) ,
	oppilaitosnimi varchar(50) ,
	koulutuksen_sijaintikunta varchar(3) ,
	kunta varchar(30) ,
	Erilliset_opettajan_opinnot varchar(3) ,
	patevyys_selite varchar(250) ,
	suoritusvuosi_ja_kuukausi varchar(6) ,
	suorituspvm date ,
	Henkilotunnus varchar(11) ,
	Henkilon_nimi varchar(60) ,
	Sukunimi varchar(50) ,
	Etunimet varchar(50) ,
	opintosuorituksen_avain varchar(100) ,
	opiskelija_avain varchar(100) ,
	paivityspvm smalldatetime ,
	DB varchar(20) 
) 


INSERT INTO #TK_eriope_patevyydet( 
            Oppilaitostunnus
           ,oppilaitosnimi
           ,koulutuksen_sijaintikunta
           ,kunta
           ,Erilliset_opettajan_opinnot
           ,patevyys_selite
           ,suoritusvuosi_ja_kuukausi
           ,suorituspvm
           ,Henkilotunnus
           ,Henkilon_nimi
           ,Sukunimi
           ,Etunimet
           ,opintosuorituksen_avain
           ,opiskelija_avain
           ,paivityspvm
           ,DB )
SELECT            
    L.oppilaitostunnus,
    L.oppilaitosnimi,
    L.koodi,
    L.kunta,
    L.erilliset_opettajan_opinnot,
    L.patevyys_selite,
    L.suoritusvuosi_ja_kuukausi,
    L.suorituspvm,
    L.henkilotunnus,
    L.henkilon_nimi,
    L.sukunimi,
    L.etunimet,
    L.opintosuorituksen_avain,
    L.opiskelija_avain,
    getdate(),
    L.db
FROM #Lopputulos_patevyys L

     


DELETE FROM [TK_eriope_patevyydet]

INSERT INTO [dbo].[TK_eriope_patevyydet]
           ([Oppilaitostunnus]
           ,[oppilaitosnimi]
           ,[koulutuksen_sijaintikunta]
           ,[kunta]
           ,[Erilliset_opettajan_opinnot]
           ,[patevyys_selite]
           ,[suoritusvuosi_ja_kuukausi]
           ,[suorituspvm]
           ,[Henkilotunnus]
           ,[Henkilon_nimi]
           ,[Sukunimi]
           ,[Etunimet]
           ,[opintosuorituksen_avain]
           ,[opiskelija_avain]
           ,[paivityspvm]
           ,[DB])

SELECT Oppilaitostunnus,oppilaitosnimi,koulutuksen_sijaintikunta,kunta,Erilliset_opettajan_opinnot,patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,Henkilotunnus,Henkilon_nimi,Sukunimi,Etunimet,opintosuorituksen_avain,opiskelija_avain,paivityspvm,DB
FROM #TK_eriope_patevyydet   
 

DROP TABLE #patevyys
DROP TABLE #sijaintikunta
DROP TABLE #dubbla
DROP TABLE #poistettavat
    
DROP TABLE #Lopputulos_patevyys
DROP TABLE #TK_eriope_patevyydet