USE [CLARA_ROS_MICROSOFT_TEAMS]
GO
/****** Object:  StoredProcedure [dbo].[CreerVueCoursGroupesClara]    Script Date: 20/11/2019 14:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================
-- Author:		Nadia Amrouche
-- Create date: 25-10-2019
-- Description:	La liste des cours-groupe dans Clara
-- ==================================================
ALTER PROCEDURE [dbo].[CreerVueCoursGroupesClara]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	--select * from  ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Reference.UniteOrg
	--DROP VIEW [dbo].[VueCoursGroupesClara]
	IF EXISTS (Select * FROM sys.views where name='VueCoursGroupesClara')
	Drop View [dbo].[VueCoursGroupesClara]

	declare @sqlcode varchar(max)

	set @sqlcode = '
		CREATE VIEW [dbo].[VueCoursGroupesClara]
		AS
		SELECT DISTINCT 
		Groupe.AnSession
		, Groupe.Numero as NumeroGroupe
		, Cours.Numero as NumeroCours
		, Cours.TitreLong as TitreCours
		, Discipline.Numero as NoDiscipline
		, Discipline.TitreLong as TitreDiscipline
		, Employe.Numero  as NumeroEnseignant 
		, Employe.NomPrenom as NomPrenomEnseignant
		, EmployeCourriel.Courriel AS CourrielEnseignant
		, Etudiant.Numero7 as NumeroEtudiant
		, UniteOrg.Numero as NumeroUniteOrg
		, CohorteFC.Numero as NumeroCohorteFC
		, CohorteFC.DateDebut as DateDebutCohorteFC
		, CohorteFC.DateFin as DateFinCohorteFC
		from ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Groupes.Groupe as Groupe
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.BanqueCours.Cours  as Cours on  Groupe.IDCours = Cours.IDCours
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Reference.UniteOrg  as UniteOrg on Groupe.IDUniteOrg = UniteOrg.IDUniteOrg
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Reference.Discipline as Discipline on Groupe.IDDiscipline = Discipline.IDDiscipline
		left join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Groupes.CohorteFC as CohorteFC  on Groupe.IDCohorteFC =CohorteFC.IDCohorteFC
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Groupes.EmployeGroupe as EmployeGroupe  on Groupe.IDGroupe = EmployeGroupe.IDGroupe  
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Employes.Employe as  Employe on EmployeGroupe.IDEmploye = Employe.IDEmploye and  Employe.IDTypeEmploye = 1
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Inscriptions.Inscription as Inscription  on Groupe.IDGroupe =Inscription.IDGroupe  and Inscription.IndicateurSupprime = 0
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Etudiants.EtudiantSession  as EtudiantSession on Inscription.IDEtudiantSession = EtudiantSession.IDEtudiantSession and Groupe.AnSession =  EtudiantSession.AnSession and  EtudiantSession.Etat = 1
		inner join ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Etudiants.Etudiant as Etudiant on EtudiantSession.IDEtudiant = Etudiant.IDEtudiant
		left join ClaraRosMicrosoftTeams.RHPAIE_ROS_PROD.Employes.employe AS EmployePaie ON Employe.Numero = EmployePaie.Numero 
		left join ClaraRosMicrosoftTeams.RHPAIE_ROS_PROD.Employes.EmployeCourriel AS EmployeCourriel ON EmployePaie.IDEmploye = EmployeCourriel.IDEmploye AND (EmployeCourriel.DateDesactivation IS NULL AND EmployeCourriel.TypeCourriel = ''STD'') --or (EmployeCourriel.DateDesactivation is null and EmployeCourriel.TypeCourriel is null)
		where Groupe.Etat =1
		and Cours.IndicateurLocal = 1
		--and Groupe.AnSession=20193 
		--and UniteOrg.Numero=''915000-1''
	'

	--set @sqlcode = '
		--CREATE VIEW [dbo].[VueCoursGroupesClara]
		--AS
		--SELECT DISTINCT 
		--EtudiantSession.AnSession
		--, Groupe.Numero AS NumeroGroupe
		--, Cours.Numero AS NumeroCours
		--, Cours.TitreLong AS TitreCours
		--, Etudiant.Numero7 AS NumeroEtudiant
		--, Employe.Numero AS NumeroEnseignant 
		--, Employe.NomPrenom AS NomPrenomEnseignant
		--, EmployeCourriel.Courriel AS CourrielEnseignant
		--, UniteOrg.Numero AS NumeroUniteOrg
		--FROM  ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Etudiants.EtudiantSession AS EtudiantSession 
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Etudiants.Etudiant AS Etudiant ON EtudiantSession.IDEtudiant = Etudiant.IDEtudiant
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Inscriptions.Inscription AS Inscription ON EtudiantSession.IDEtudiantSession = Inscription.IDEtudiantSession 
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Reference.UniteOrg AS UniteOrg ON EtudiantSession.IDUniteOrg = UniteOrg.IDUniteOrg
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Groupes.Groupe AS Groupe ON Inscription.IDGroupe = Groupe.IDGroupe AND Groupe.AnSession = EtudiantSession.AnSession
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.BanqueCours.Cours AS Cours ON Inscription.IDCours = Cours.IDCours 
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.BanqueCours.CoursDisciplineUniteOrg AS CoursDisciplineUniteOrg ON Cours.IDCours = CoursDisciplineUniteOrg.IDCours
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Reference.Discipline AS dsc ON CoursDisciplineUniteOrg.IDDiscipline = dsc.IDDiscipline
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Horaires.RencontreGroupe AS RencontreGroupe ON Groupe.IDGroupe = RencontreGroupe.IDGroupe
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Horaires.Rencontre AS Rencontre ON RencontreGroupe.IDRencontre = Rencontre.IDRencontre
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Horaires.RencontreEmploye AS RencontreEmploye ON Rencontre.IDRencontre = RencontreEmploye.IDRencontre 
		--INNER JOIN ClaraRosMicrosoftTeams.CLARA_ROS_PROD.Employes.Employe AS Employe ON RencontreEmploye.IDEmploye = Employe.IDEmploye
		--INNER JOIN ClaraRosMicrosoftTeams.RHPAIE_ROS_PROD.Employes.employe AS EmployePaie ON Employe.Numero = EmployePaie.Numero 
		--INNER JOIN ClaraRosMicrosoftTeams.RHPAIE_ROS_PROD.Employes.EmployeCourriel AS EmployeCourriel ON EmployePaie.IDEmploye = EmployeCourriel.IDEmploye AND EmployeCourriel.DateDesactivation IS NULL AND EmployeCourriel.TypeCourriel = ''STD'' and  EmployeCourriel.Courriel like ''%@crosemont.qc.ca''
		--WHERE (EtudiantSession.Etat = 1)
		--AND (Inscription.IndicateurSupprime = 0) 
		--AND (Cours.IndicateurLocal = 1) 
		--AND (CoursDisciplineUniteOrg.Rang = 1)
		--'
	exec (@sqlcode)


END


-- select * from [dbo].[VueCoursGroupesClara]