use EmpclgDB;

----Question 2-1
---SoldeFormation est default
---TypEmploiye est P

go;
create or alter procedure ajouterEmployePermanent(
						 @nom varchar(30),
						 @prenom varchar(30),
						 @salaire money, 
						 @echelon int, 
						 @deptno int,
						 @adresse varchar(40)
						) AS
begin
declare 
@empno int;
begin try
	begin transaction
		insert into employesClg (nom,prenom,typeEmploye,adresse) 
			values (@nom,@prenom,'P',@adresse);
		select @empno =@@IDENTITY;
		insert into EmpPermanent(empno,salaire,echelon,deptno)
			values (@empno,@salaire,@echelon,@deptno)
	commit;
end try
begin catch 
if (@@TRANCOUNT>0)  rollback;
end catch;
end;

go

---L'insertion va se faire.
execute ajouterEmployePermanent
@nom ='LeNouveau ',
@prenom ='LePrenom',
@salaire =50000,
@echelon =14,
@deptno=1,
@adresse ='Laval';

--- L.insertion ne se fera pas
execute ajouterEmployePermanent
@nom ='NemarcherasPas ',
@prenom ='Lerenom',
@salaire =50000,
@echelon =14,
@deptno=10,
@adresse='Laval';

go;
---- La question 2-2 ressemble à la question 2-1. 

--Question 2-3---- Insertion dans empFormations
create or alter procedure  insertEmpFormation(
								@idformation int,
								@empno int,
								@date date,
								@lieu varchar(50),
								@coutreel money)AS
begin
declare 
@solde money,
@nbplace int,
@coutmin money, --optionnel, Labo3(trigger)
@coutmax money; --optionnel, Labo3(trigger)
	select @solde = soldeFormation from EmpPermanent
	where empno =@empno;
	select @nbplace = nbPlacesDisponibles from Formations
	where idformation =@idformation

	select @coutmax =coutMaximum from Formations
	where idFormation = @idformation --optionnel, labo3

	select @coutmin = coutMinimum from Formations
	where idFormation = @idformation --optionnel, labo3
-- bloc if optionnel--> labo3
	if(@coutreel<@coutmin or @coutreel>@coutmax) --
	begin
	print('le coût n''est pas OK');
	RETURN;
	END

--- début de la transaction
begin try
	begin transaction
	if(@solde>=@coutreel and @nbplace>=1)
		begin
		insert into EmpFormations values
		(@idformation,@empno,@date,@lieu,@coutreel)

		update Formations set nbPlacesDisponibles =nbPlacesDisponibles -1
		where idFormation =@idformation;

		update EmpPermanent set soldeFormation = soldeFormation -@coutreel
		where empno =@empno;
		commit;
		end;
		ELSE
		begin
			rollback;
			print('la formation ne peut pas se faire');
		end;

end try
begin catch
if(@@TRANCOUNT>0) rollback
end catch
end


---- tests d'execution
update formations set nbPlacesDisponibles = 0 where idformation=4;
select * from Formations
select * from EmpPermanent;
select * from EmpFormations

-- un test que ne marche pas car le cout reel est plus elevé que le cout max
execute insertEmpFormation
@idformation =2,
@empno=2,
@date ='2025-09-19',
@lieu ='ici',
@coutreel =20000;
---- un test que ne marche pas car le cout reel est plus peti que le cout min
execute insertEmpFormation
@idformation =2,
@empno=2,
@date ='2025-09-19',
@lieu ='ici',
@coutreel =1; 
----Un test qui marche

execute insertEmpFormation
@idformation =2,
@empno=2,
@date ='2025-09-19',
@lieu ='ici',
@coutreel =3000; 
-- on doit faire un autre test qui marche
---4
--- un test qui ne marche pas car le nombre de place est insuffisant
execute insertEmpFormation
@idformation =4,
@empno=2,
@date ='2025-09-19',
@lieu ='ici',
@coutreel =3000;

-- un autre teste qui ne marche pas: le solde n'est pas suffisant
execute insertEmpFormation
@idformation =2,
@empno=1,
@date ='2025-09-19',
@lieu ='ici',
@coutreel =3000;

go;

---- 4- Suppression d'un département.
CREATE OR ALTER PROCEDURE supprimerEmploye(@deptno CHAR(3)) AS
BEGIN
	
	BEGIN TRY
		BEGIN TRANSACTION
			UPDATE EmpPermanent SET deptno=NULL WHERE deptno=@deptno;
			DELETE FROM Departements WHERE deptno=@deptno;
			COMMIT;
	END TRY
	BEGIN CATCH
		ROLLBACK;
	END CATCH
END;

go;
--- Q5, listeEmployeselonTypeemploye------------
-- Les vues et les SELECT
create view ListePermanent AS
select nom,prenom,salaire,nomdepartement from EmployesClg E
INNER JOIN EmpPermanent P ON P.empno = E.empno
INNER JOIN Departements D ON P.deptno = D.deptno;
go;
--vue des emp temporaires
create view ListeTemporaire AS
SELECT nom,prenom,TauxHoraire,nbHeureMin FROM EmployesClg E
INNER JOIN EmpTemporaire T ON E.empno = T.empno;

go;
--- creation de la procédure
CREATE OR ALTER PROCEDURE listeEmployeselonTypeemploye (@typeemploye char(1)) AS
BEGIN

	if(@typeemploye='P')
		SELECT * FROM ListePermanent;
	 else if(@typeemploye='T')
		SELECT * FROM ListeTemporaire;
	else print('ce type n''existe pas');
END

execute listeEmployeselonTypeemploye
@typeemploye  ='P';

execute listeEmployeselonTypeemploye
@typeemploye  ='T';
execute listeEmployeselonTypeemploye
@typeemploye  ='A';


-- fonctions
---Q6 -- Fonction scalaire
create or alter  FUNCTION CoutMoayenFormation(@nomdepartement varchar(30)) RETURNS MONEY AS

BEGIN
DECLARE @moyenneCoutFormation MONEY;

	SELECT @moyenneCoutFormation = 	AVG(EmpFormations.coutreel) 
										FROM EmpFormations
										INNER join EmpPermanent ON EmpFormations.empno = EmpPermanent.empno
										INNER JOIN Departements ON EmpPermanent.deptno = Departements.deptno
										WHERE Departements.nomdepartement = @nomdepartement
										GROUP BY EmpPermanent.deptno;
RETURN @moyenneCoutFormation;
END;	
go;
select dbo.CoutMoayenFormation('informatique');

GO;
---Q7 -- fonction TABLE--
-- le WHEN n'est pas obligatoire si vous ne voulez pas ramene
-- les département sans salaire. Dans ce cas ce sera INNER JOIN.
CREATE OR ALTER FUNCTION moyenneSalaireParDepartement() RETURNS TABLE AS
RETURN

	(
		SELECT  Departements.nomdepartement,
			CASE
					WHEN AVG(salaire) IS NULL THEN '0.00'
					ELSE AVG(salaire)
				END AS moyenneSalaire	
		
		FROM EmpPermanent
		RIGHT OUTER JOIN Departements ON EmpPermanent.deptno = Departements.deptno
		GROUP BY Departements.nomdepartement
	);
select * from moyenneSalaireParDepartement();