1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288
| drop database if exists gestion_personnel;
create database gestion_personnel default character set utf8 collate utf8_general_ci;
use gestion_personnel;
create table services(
id tinyint unsigned not null auto_increment,
nom varchar(255) not null,
constraint pk_services primary key(id)
)engine=innodb auto_increment=1;
insert into services(nom)values
('Internat'),
('Pré bac'),
('AVSI');
create table fonctions(
id tinyint unsigned not null auto_increment,
nom varchar(255) not null,
constraint pk_fonctions primary key(id)
)engine=innodb auto_increment=1;
insert into fonctions(nom)values
('AED'),
('CPE'),
('Administrateur');
create table droits(
id tinyint unsigned not null auto_increment,
id_fonction tinyint unsigned not null,
faisable varchar(255) not null,
constraint pk_droits primary key(id),
constraint fk_droits_fonction foreign key(id_fonction) references fonctions(id)
)engine=innodb auto_increment=1;
insert into droits(id_fonction,faisable)values
((select id from fonctions where nom='CPE'),'création personnel'),
((select id from fonctions where nom='Administrateur'),'création personnel'),
((select id from fonctions where nom='CPE'),'affectation personnel'),
((select id from fonctions where nom='CPE'),'desaffectation personnel'),
((select id from fonctions where nom='CPE'),'suppression personnel'),
((select id from fonctions where nom='CPE'),'validation horaire'),
((select id from fonctions where nom='AED'),'ajout horaire'),
((select id from fonctions where nom='AED'),'bilan horaire'),
((select id from fonctions where nom='CPE'),'bilan horaire'),
((select id from fonctions where nom='AED'),'listes'),
((select id from fonctions where nom='CPE'),'listes'),
((select id from fonctions where nom='Administrateur'),'listes');
create table personnels(
id tinyint unsigned auto_increment,
id_fonction tinyint unsigned not null,
nom varchar(255) not null,
mdp varchar(255) not null,
constraint pk_personnels primary key(id),
constraint fk_personnels_fonction foreign key(id_fonction) references fonctions(id)
)engine=innodb auto_increment=1;
insert into personnels(id_fonction,nom,mdp)values((select id from fonctions where nom='Administrateur'),'your boss',sha1('master'));
create table affectations(
id tinyint unsigned not null auto_increment,
id_personnel tinyint unsigned,
id_service tinyint unsigned not null,
constraint pk_affectations primary key(id),
constraint fk_affectations_personnel foreign key(id_personnel) references personnels(id),
constraint fk_affectations_service foreign key(id_service) references services(id)
)engine=innodb auto_increment=1;
create table horaires(
id int unsigned not null auto_increment,
id_personnel tinyint unsigned,
id_validateur tinyint unsigned,
heures tinyint unsigned not null,
date_travail date not null,
date_validation date,
constraint pk_horaires primary key(id),
constraint fk_horaires_personnel foreign key(id_personnel) references personnels(id),
constraint fk_horaires_validateur foreign key(id_personnel) references personnels(id),
key k_horaires_dates(date_travail)
)engine=innodb auto_increment=1;
#api pour gérer les actions
delimiter $$
create procedure fonction_liste(in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'Liste le personnel'
begin
declare ok bool default true;
declare test bool;
declare i tinyint unsigned;
declare n varchar(255);
declare t text default '<select id=\"listefonctions\">';
declare c cursor for select id,nom from fonctions;
declare continue handler for not found set ok=false;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='listes'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select true as ok,'' as message;
open c;
while ok do
fetch c into i,n;
if ok then
set t=concat(t,'<option value=\"',i,'\">',n,'</option>');
end if;
end while;
close c;
select concat(t,'</select>');
end if;
end$$
create procedure service_liste(in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'Liste les service'
begin
declare ok bool default true;
declare test bool;
declare i tinyint unsigned;
declare n varchar(255);
declare t text default '<select id=\"listeservices\">';
declare c cursor for select id,nom from services;
declare continue handler for not found set ok=false;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='listes'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select true as ok,'' as message;
open c;
while ok do
fetch c into i,n;
if ok then
set t=concat(t,'<option value=\"',i,'\">',n,'</option>');
end if;
end while;
close c;
select concat(t,'</select>');
end if;
end$$
create procedure personnel_creation(in nom_perso varchar(255),in fonction tinyint unsigned,in pass varchar(255),in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'création d un personnel'
begin
declare test bool;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='création personnel'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select count(id)>0 into test from personnels where nom=nom_perso;
if test then
select false as ok,'Le nom du personnel à créer existe déjà' as message;
else
select true as ok,'' as message;
insert into personnels(nom,id_fonction,mdp)values(nom_perso,fonction,sha1(pass));
end if;
end if;
end$$
create procedure personnel_affectation(in id_perso tinyint unsigned,in id_serv tinyint unsigned,in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'affectation d un personnel'
begin
declare test bool;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='affectation personnel'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select true as ok,'' as message;
insert into affectations(id_personnel,id_service)values(id_perso,id_serv);
end if;
end$$
create procedure personnel_desaffectation(in id_perso tinyint unsigned,in id_serv tinyint unsigned,in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'création d un personnel'
begin
declare test bool;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='desaffectation personnel'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select true as ok,'' as message;
delete from affectations where id_personnel=id_perso and id_service=id_serv;
end if;
end$$
create procedure personnel_suppression(in id_perso tinyint unsigned,in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'création d un personnel'
begin
declare test bool;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='suppression personnel'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select true as ok,'' as message;
delete from peronnels where id=id_perso;
end if;
end$$
create procedure personnel_liste(in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'Liste le personnel'
begin
end$$
create procedure horaire_validation(in idtravail int unsigned,in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'validation d une période de de travail'
begin
declare test bool;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='validation horaire'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select count(id)=0 into test from horaires h
inner join affectations a1 on a1.id_personnel=h.id_personnel
inner join affectations a2 on a1.id_service=a2.id_service and a2.id_personnel=idautorite
where h.id=idtravail;
if test then
select false as ok,'Tu n\'es pas dans le service autorisé pour cette action' as message;
else
select true as ok,'' as message;
update horaires set id_validateur=idautorite, date_validation=current_date where id=idtravail;
end if;
end if;
end$$
create procedure horaire_ajout(in datetravail date,in heurestravail tinyint unsigned,in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'ajout d heures travaillées'
begin
declare test bool;
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='ajout horaire'
where p.id=idautorite and sha1(p.mdp)=mdpautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select true as ok,'' as message;
insert into horaires(date_travail,id_personnel,heures)values(datetravail,idautorite,heurestravail);
end if;
end$$
create procedure horaire_bilan(in perso tinyint unsigned,in idautorite tinyint unsigned,in mdpautorite varchar(255))
comment 'bilan des heures pour un personnel'
begin
declare test bool;
declare debut date default if(month(current_date)>8,concat(year(current_date),'08-01'),concat(year(current_date)-1,'08-01'));
select count(p.id)=0 into test from personnels p
inner join droits d on p.id_fonction=d.id_fonction and d.faisable='ajout horaire'
where p.id=idautorite;
if test then
select false as ok,'Tu n\'as pas les droits suffisants pour cette action' as message;
else
select true as ok,'' as message;
select month(date_travail),
sum(if(isnull(date_validation),heures,0)) as `non validées`,
sum(if(not isnull(date_validation),heures,0)) as `validées`
from horaires
where id=perso and date_travail>debut
group by month(date_travail);
end if;
end$$
create procedure authentification(in perso varchar(255),in pass varchar(255))
comment 'identification d un personnel'
begin
declare test bool;
declare idp tinyint unsigned;
select count(id)=0,id into test,idp from personnels where nom=perso and mdp=sha1(pass);
if test then
select false as ok,'Nom ou mot de passe incorrects' as message;
else
select true as ok,'' as message;
select idp, sha1(sha1(pass));
end if;
end$$
delimiter ; |
Partager