Opettajakelpoisuus tarkistustiedostot

-- Opettajapätevyyskoodi vaaditaan  #patevyydet	 

SELECT	  
		  HP.db AS kk
	    , YEAR( HP.alkamispaivamaara ) AS vuosi
		, P.koodi AS patevyys_koodi
		, P.nimi AS patevyys_nimi		
		, OS.Suorituspaivamaara AS paivamaara		 
		, O.Avain AS opiskelija_avain
		, OS.Avain AS opintosuoritus_avain	
		, O.id OpiskelijaID
		, HP.HenkiloID
		, HP.OpintosuoritusID
		, OS.Opintosuorituksen_lajiID laji
		, FLOOR(OS.Laajuus) laajuus
INTO #patevyydet				 
FROM  	virta.dbo.Henkilon_patevyys HP
		INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = HP.HenkiloID		
		INNER JOIN virta.dbo.Opintosuoritus OS ON OS.id = HP.OpintosuoritusID
		INNER JOIN virta.dbo.Patevyys P ON P.id = HP.patevyysid				 
WHERE	P.koodi LIKE '[a-z][a-z]';

CREATE NONCLUSTERED INDEX IX_OpiskelijaID ON #patevyydet(OpiskelijaID, HenkiloID);


-- Vain jos on  #patevyydet	  haetaan   INTO #tutkinnot		 
SELECT OS.db kk
		, YEAR( OS.Suorituspaivamaara ) AS vuosi
		, T.koodi AS koulutuskoodi
		, T.nimi
		, OS.Suorituspaivamaara
		, OS.avain AS OpintosuoritusAvain
		, OS.OpiskelijaID
		, OS.id AS OpintosuoritusID
INTO #tutkinnot		
FROM virta.dbo.Opintosuoritus OS
		INNER JOIN virta.dbo.Opintosuorituksen_laji L ON L.id = OS.Opintosuorituksen_lajiID
		LEFT JOIN virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID

WHERE OS.OpiskelijaID IN (
							SELECT OpiskelijaID FROM #patevyydet
						)
AND		L.Koodi = '1'

CREATE NONCLUSTERED INDEX IX_OpiskelijaTutkintoID ON #tutkinnot(OpiskelijaID,OpintosuoritusID);

-- Henkilötiedot jos on #patevyydet
SELECT H.db AS kk,
		H.Henkilotunnus,
		H.Syntymaaika,		
		O.Kansallinen_op_id AS oppijanumero,
		O.avain AS opiskelijaAvain,
		O.HenkiloID
INTO #henkilot
FROM virta.dbo.Henkilo H
		INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.id
WHERE H.id IN (
	SELECT HenkiloID FROM #patevyydet
)

CREATE NONCLUSTERED INDEX IX_HenkiloID ON #henkilot(HenkiloID);


DELETE FROM dbo.ope_patevyydet;

INSERT INTO dbo.ope_patevyydet(kk,vuosi,patevyys_koodi,patevyys_nimi,paivamaara,opiskelija_avain,opintosuoritus_avain, laji, laajuus)
SELECT kk,vuosi,patevyys_koodi,patevyys_nimi,paivamaara,opiskelija_avain,opintosuoritus_avain, laji, laajuus 
FROM #patevyydet;

DELETE FROM dbo.ope_tutkinnot;

INSERT INTO dbo.ope_tutkinnot(kk,vuosi,koulutuskoodi,suorituspaivamaara,OpintosuoritusAvain )
SELECT kk,vuosi,koulutuskoodi,suorituspaivamaara,OpintosuoritusAvain 
FROM #tutkinnot;

DELETE FROM dbo.ope_henkilotiedot;

INSERT INTO dbo.ope_henkilotiedot(kk,Henkilotunnus,syntymaaika,oppijanumero,opiskelijaAvain)
SELECT kk,Henkilotunnus,syntymaaika,oppijanumero,opiskelijaAvain 
FROM #henkilot;


--   Sukupuolitiedot
UPDATE OPE
SET OPE.[sukupuoli] = S.Koodi
  FROM [Opettajat].[dbo].[ope_henkilotiedot] OPE
		JOIN virta.dbo.Henkilo H ON H.Henkilotunnus = OPE.Henkilotunnus AND OPE.kk = H.db
		JOIN virta.dbo.Sukupuoli S ON S.id = H.SukupuoliID

--   Opintosuorituksen avaimen opiskelijaAvain tutkintoon 
UPDATE T
SET T.opiskelijaAvain = O.avain
-- SELECT P.opiskelija_avain
FROM dbo.ope_tutkinnot T			
		INNER JOIN virta.dbo.Opintosuoritus OS ON OS.avain = T.opintosuoritusAvain AND OS.db = T.kk	
		INNER JOIN virta.dbo.Opiskelija O ON O.id = OS.OpiskelijaID AND O.db = T.kk


DROP TABLE #patevyydet;
DROP TABLE #tutkinnot;
DROP TABLE #henkilot;


-- Sama henkilö eri korkeakoulu, sama id
CREATE TABLE #HenkiloTiedot (
	id int IDENTITY(1,1) NOT NULL,
	[Henkilotunnus] varchar(100),
	oppijanumero varchar(100)
	)

-- Hetulliset
INSERT INTO #HenkiloTiedot(Henkilotunnus)
SELECT
      DISTINCT [Henkilotunnus]
      
  FROM [Opettajat].[dbo].[ope_henkilotiedot]
WHERE henkilotunnus IS NOT NULL
AND LEN(LTRIM(henkilotunnus))=11

-- Jos ei hetua mutta oppijanumero
INSERT INTO #HenkiloTiedot(oppijanumero)
SELECT DISTINCT oppijanumero
  FROM [Opettajat].[dbo].[ope_henkilotiedot]
WHERE (henkilotunnus IS NULL OR LEN(LTRIM(henkilotunnus))< 11)
AND oppijanumero IS NOT NULL


UPDATE OT
SET OT.henkilo = H.id
FROM  [ope_henkilotiedot] OT 
		JOIN #HenkiloTiedot H ON H.Henkilotunnus = OT.[Henkilotunnus]

UPDATE OT
SET OT.henkilo = H.id
FROM  [ope_henkilotiedot] OT 
		JOIN #HenkiloTiedot H ON H.oppijanumero = OT.oppijanumero
WHERE (OT.henkilotunnus IS NULL OR LEN(LTRIM(OT.henkilotunnus))< 11)
AND OT.oppijanumero IS NOT NULL
 

UPDATE P
SET P.henkilo =  H.henkilo
  FROM [Opettajat].[dbo].[ope_patevyydet] P
			JOIN [Opettajat].[dbo].[ope_henkilotiedot] H ON H.opiskelijaavain = P.opiskelija_avain AND H.kk = P.kk


UPDATE T
SET T.henkilo =  H.henkilo
FROM [Opettajat].[dbo].[ope_tutkinnot] T
			JOIN [Opettajat].[dbo].[ope_henkilotiedot] H ON H.opiskelijaavain = T.opiskelijaavain AND H.kk = T.kk




DROP TABLE #HenkiloTiedot