BD GSB CR Procedure Stocke -- Visiteur
create procedure VisiteurAfficher_Tous
as
select VIS_MATRICULE, VIS_NOM, VIS_PRENOM, VIS_DATEEMBAUCHE from VISITEUR
order by VIS_NOM
go
create procedure VisiteurAfficher_Date
@GetDate datetime
as
select VIS_MATRICULE, VIS_NOM, VIS_PRENOM, VIS_DATEEMBAUCHE from VISITEUR
where VIS_DATEEMBAUCHE = @GetDate
order by VIS_NOM
go
create procedure NbVisiteur_Date
@GetDate datetime
as
select count(*) from VISITEUR where VIS_DATEEMBAUCHE = @GetDate
go
create procedure NbVisiteur_Tous
as
select count(*) from VISITEUR
go
create procedure FicheVisiteur
@GetId char(4)
as
select VIS_MATRICULE, VIS_NOM, VIS_PRENOM, VIS_ADRESSE, VIS_CP, VIS_VILLE,
VIS_DATEEMBAUCHE from VISITEUR where VIS_MATRICULE = @GetId
go
create procedure Ajouter_Visiteur
@GetMatricule char(4), @GetNom varchar(38), @GetPrenom varchar(38), @GetAdresse
varchar(38), @GetCP char(5), @GetVille varchar(38), @GetDateEmbauche date
as
insert into VISITEUR (VIS_MATRICULE, VIS_NOM, VIS_PRENOM, VIS_ADRESSE, VIS_CP,
VIS_VILLE, VIS_DATEEMBAUCHE) VALUES (@GetMatricule, @GetNom, @GetPrenom,
@GetAdresse, @GetCP, @GetVille, @GetDateEmbauche)
go
-- Praticiens
create procedure NbPraticien_Tous
@Type VARCHAR(50)
as
select count(*) FROM PRATICIEN INNER JOIN TYPE_PRATICIEN ON
PRATICIEN.TYP_CODE = TYPE_PRATICIEN.TYP_CODE
WHERE TYP_LIBELLE = @Type
go
create procedure PraticienAfficher_Tous
@Type VARCHAR(50)
as
select PRA_NUM, PRA_NOM, PRA_PRENOM, TYP_LIBELLE
FROM PRATICIEN INNER JOIN TYPE_PRATICIEN ON PRATICIEN.TYP_CODE =
TYPE_PRATICIEN.TYP_CODE
WHERE TYP_LIBELLE = @Type
order by PRA_NOM
go
create procedure FichePraticien
@GetNum INT
as
select PRA_NUM, PRA_NOM, PRA_PRENOM, TYP_LIBELLE, PRA_ADRESSE, PRA_CP,
PRA_VILLE, TYP_LIEU from PRATICIEN INNER JOIN TYPE_PRATICIEN ON
PRATICIEN.TYP_CODE = TYPE_PRATICIEN.TYP_CODE where PRA_NUM = @GetNum
go
/* Pour l'affichage des praticiens par type*/
create procedure Ps_AfficheTypePraticienCbx
AS
SELECT TYP_LIBELLE AS [LIBELLE]
FROM TYPE_PRATICIEN
go
/* Pour la modification des praticiens*/
create procedure Ps_AfficheTypePraticienCbxModif
AS
SELECT TYP_CODE AS [Type], TYP_LIBELLE AS [Libelle]
FROM TYPE_PRATICIEN
go
create procedure PraticienAfficherModifCbx_Tous
as
select PRA_NUM, PRA_NOM ' ' PRA_PRENOM As [NomPrenom]
FROM PRATICIEN
order by PRA_NOM
go
create procedure Modif_Praticien
@PRA_NUM INT,@PRA_NOM VARCHAR(25),@PRA_PRENOM
VARCHAR(30),@PRA_ADRESSE VARCHAR(255),@PRA_CP CHAR(5),@PRA_VILLE
VARCHAR(25),@TYP_CODE CHAR(3)
as
UPDATE PRATICIEN SET PRA_NOM = @PRA_NOM, PRA_PRENOM = @PRA_PRENOM,
PRA_ADRESSE = @PRA_ADRESSE, PRA_CP = @PRA_CP, PRA_VILLE = @PRA_VILLE,
TYP_CODE = @TYP_CODE WHERE PRA_NUM = @PRA_NUM
go
-- Compte-rendu
create procedure CompteRenduAfficher_Tous
as
select RAP_NUM, RAP_DATE, RAP_MOTIF, RAP_BILAN, VIS_NOM, VIS_PRENOM,
PRA_NOM, PRA_PRENOM from RAPPORT_VISITE RP INNER JOIN VISITEUR VI ON
RP.VIS_MATRICULE = VI.VIS_MATRICULE INNER JOIN PRATICIEN PR ON PR.PRA_NUM
= RP.PRA_NUM
order by RAP_DATE
go
create procedure CompteRenduAfficher_Date
@GetDate datetime
as
select RAP_NUM, RAP_DATE, RAP_MOTIF, RAP_BILAN, VIS_NOM, VIS_PRENOM,
PRA_NOM, PRA_PRENOM from RAPPORT_VISITE RP INNER JOIN VISITEUR VI ON
RP.VIS_MATRICULE = VI.VIS_MATRICULE INNER JOIN PRATICIEN PR ON PR.PRA_NUM
= RP.PRA_NUM
where RAP_DATE = @GetDate
order by RAP_DATE
go
create procedure NbCompteRendu_Tous
as
select count(*) from RAPPORT_VISITE
go
create procedure NbRapport_Date
@GetDate datetime
as
select count(*) from RAPPORT_VISITE where RAP_DATE = @GetDate
go
create procedure Supp_CompteRendu
@Rap_num INT
as
DELETE FROM OFFRIR
WHERE RAP_NUM = @Rap_num
DELETE FROM RAPPORT_VISITE
WHERE RAP_NUM = @Rap_num
go
create procedure AfficheNumRapport_Tous
as
select rap_num from RAPPORT_VISITE
go
create procedure ajoutCompteRendu
@VIS_MATRICULE CHAR(4), @PRA_NUM int, @RAP_DATE DATE, @RAP_BILAN
varchar(255), @RAP_MOTIF varchar(255)
as
INSERT INTO RAPPORT_VISITE(PRA_NUM, VIS_MATRICULE, RAP_DATE, RAP_BILAN,
RAP_MOTIF)
VALUES(@PRA_NUM, @VIS_MATRICULE, @RAP_DATE, @RAP_BILAN, @RAP_MOTIF)
go
-- offrir
create procedure remplir_cbx_medicament
as
SELECT MED_DEPOTLEGAL, MED_NOMCOMMERCIAL FROM MEDICAMENT
go
create procedure ajoutMedocOffrir
@RAP_NUM INT, @MEDOC_ID varchar(50), @QTE INT
as
INSERT INTO OFFRIR(RAP_NUM, MED_DEPOTLEGAL, OFF_QTE)
VALUES(@RAP_NUM, @MEDOC_ID, @QTE)
go
create trigger BGGLP_Archivage_Suppression on RAPPORT_VISITE instead of DELETE AS
/*déclaration des variables*/
declare @RAP_NUM int
declare @VIS_MATRICULE char(4)
declare @PRA_NUM int
declare @RAP_DATE date
declare @RAP_BILAN varchar(255)
declare @RAP_MOTIF varchar(255)
declare @MED_DEPOTLEGAL varchar(255)
declare @OFF_QTE int
/*Recupération des valeurs*/
select @RAP_NUM = RAP_NUM from deleted
select @VIS_MATRICULE = VIS_MATRICULE from deleted where RAP_NUM = @RAP_NUM
select @PRA_NUM = PRA_NUM from deleted where RAP_NUM = @RAP_NUM
select @RAP_DATE = RAP_DATE from deleted where RAP_NUM = @RAP_NUM
select @RAP_BILAN = RAP_BILAN from deleted where RAP_NUM = @RAP_NUM
select @RAP_MOTIF = RAP_MOTIF from deleted where RAP_NUM = @RAP_NUM
select @MED_DEPOTLEGAL = MED_DEPOTLEGAL from OFFRIR where RAP_NUM =
@RAP_NUM
select @OFF_QTE = OFF_QTE from OFFRIR where RAP_NUM = @RAP_NUM
/*Insert dans la table archivage*/
insert into OFFRIR_SUP (RAP_NUM, MED_DEPOTLEGAL, OFF_QTE) VALUES
(@RAP_NUM, @MED_DEPOTLEGAL, @OFF_QTE)
delete from OFFRIR where RAP_NUM = @RAP_NUM
insert into RAPPORT_VISITE_SUP (RAP_NUM, VIS_MATRICULE, PRA_NUM, RAP_DATE,
RAP_BILAN, RAP_MOTIF) VALUES (@RAP_NUM, @VIS_MATRICULE, @PRA_NUM,
@RAP_DATE, @RAP_BILAN, @RAP_MOTIF)
delete from RAPPORT_VISITE where RAP_NUM = @RAP_NUM
