-------------------------------------------------------------------------------
-- corr_TD1234a.sql
-- tourne le 30 mars 2020
-- corrige TD4a curseurs
-- inclut corriges : TD1a (sans exemples), TD2a (sans tests), TD3a
-- contient dans l'ordre :
-- contraintes : presentation bonus et creation tables
-- liste actions 
-- exemples d'actions non traitement pour peupler la base sommairement
--   (a remplacer par exemple complet de deroulement peuplant de l'appli)
-- traitements : traitement3 (fonction, procedure), traitement1, traitement4
-- traitements : authentification, consulter_informations, traitement2
-------------------------------------------------------------------------------
/* Contraintes SQL et non SQL. 

Contraintes SQL (bonus en partiel/examen si presentation comme suit) :

client :
    idc pk
    nom not null
    age not null check 16<=age<=120
    avoir not null check 0<=avoir<=2000

village :
    idv pk
    ville not null
    activite -- null possible
    prix not null check 0<prix<=2000
    capacite not null check 1<=capacite<=1000

sejour :
    ids pk
    idc fk client not null -- rappel : fk n'implique pas not null
    idv fk village not null
    jour not null check 1<=jour<=365
    (idc, jour) unique 

creer sequences pour client, village, sejour

Contraintes non SQL : 

1. le nombre de sejours pour un centre pour un jour ne peut pas
depasser sa capacite :

  pour tout idv i de capacite n dans village,
  il y a au plus n lignes avec idv i pour chaque jour j dans sejour

2. l'avoir d'un client plus la somme des prix de ses sejours ne peut
exceder 2000 :

  pour tout idc i dans client : avoir + S <= 2000
  en effet, un client part de 2000 et achete des sejours, donc :
  avoir + somme des prix de ses sejours presents + somme des prix de
  ses sejours detruits = 2000 ;
  donc avoir + somme des prix de ses sejours presents <= 2000 ;
  comment obtient-on S : considerons toutes les lignes d'idc i dans
  sejour, et pour chacune, a partir de sa colonne idv dans sejour, son
  prix dans village identifie par idv ; on note S la somme de tous ces
  prix 
*/
  
-------------------------------------------------------------------------------
/* Rappel : 
Null : 
Peuvent etre "null" les valeurs intuitivement non
indispensable au fonctionnement de l'application. De plus, par
convention dans le module les colonnes utilisees en parametres ou a
l'interieur d'un traitement ne peuvent etre "null", mais il n'est pas
impose que celles en sortie d'un traitement ne le soient pas. 
Rappel : fk et check n'impliquent pas not null. 

Contraintes non SQL :
en general, il s'agit de compter un ensemble de lignes d'une table, ou de
comparer des valeurs dans deux tables, ou des variantes de ces situations
(ex : somme d'un ensemble de lignes). */

-------------------------------------------------------------------------------
-- Ordres SQL et modeles d'ordre : completer avec nouveaux outils TD precedent
-------------------------------------------------------------------------------
-- Ordres tapes par le programmeur : 

drop table sejour cascade constraints;
drop table client cascade constraints;
drop table village cascade constraints;

create table client(
    idc int primary key,
    nom varchar2(10) not null, 
    age int not null, check (0<=age and age<120),
    avoir int default 2000 not null, check (0<=avoir and avoir<=2000)
);

create table village(
    idv int primary key,
    ville varchar2(12) not null,
    activite varchar2(10),
    prix int not null, check (0<prix and prix<=2000),
    capacite int not null, check (1<=capacite and capacite<=1000)
);

create table sejour(
    ids int primary key,
    idc int not null, foreign key (idc) references client,
    idv int not null, foreign key (idv) references village,
    jour int not null, check (1<=jour and jour<=365), 
    unique (idc, jour)
);

drop sequence seq_client;
drop sequence seq_village;
drop sequence seq_sejour;

create sequence seq_client start with 1;
create sequence seq_village start with 10;
create sequence seq_sejour start with 100;

-------------------------------------------------------------------------------
/* liste des actions : 

programmeur p :
0. (action 0) creation des tables :
     client, village, sejour, archive(colonnes sejour+avoir)

employes e1, ..., en :
1. (action 1) creer villages
2. consulter villages
3. modifier villages
4. consulter sejours
5. traitement 3

clients c :
6. traitement 1
7. traitement 2
8. consulter villages pour lesquels aucun sejour
9. consulter toutes ses informations :
     dans client
     dans sejour
     dans village

systeme : 
10. traitement 4 (quand destruction d'une ligne de sejour)
*/

-------------------------------------------------------------------------------
-- employes :
-------------------------------------------------------------------------------
-- 1.
insert into village values(seq_village.nextval, 'NY', 'resto', 50, 200);
insert into village values(seq_village.nextval, 'NY', 'MOMA', 60, 300);
insert into village values(
    seq_village.nextval, 'Chatelaillon', 'kitesurf', 100, 20);

-------------------------------------------------------------------------------
-- 2.
select * from village;

-------------------------------------------------------------------------------
-- 3.
update village 
  set capacite = capacite + 10, 
      activite = 'kitesurf'
  where activite = 'resto';

-------------------------------------------------------------------------------
-- 4.
select * from sejour ;

-------------------------------------------------------------------------------
-- 5.
-- traitement 3 : voir plus bas modele d'ordre et code PL

-------------------------------------------------------------------------------
-- clients :
-------------------------------------------------------------------------------
-- 6.
-- traitement 1 : voir plus bas modele d'ordre et code PL
insert into client(idc, nom, age) values(seq_client.nextval, 'Rita', 22);
insert into client(idc, nom, age) values(seq_client.nextval, 'Riton', 23);

-------------------------------------------------------------------------------
-- 7.
-- traitement 2 : voir plus bas modele d'ordre et code PL
insert into sejour values(seq_sejour.nextval, 1, 11, 45);
insert into sejour values(seq_sejour.nextval, 2, 12, 365);

-------------------------------------------------------------------------------
-- 8.
select idv, ville, activite, prix 
  from village 
  where idv not in (select idv 
                      from sejour);

-------------------------------------------------------------------------------
-- 9. 
-- authentification et consulter_informations :
-- voir plus bas modeles d'ordre et codes PL/SQL

-------------------------------------------------------------------------------
-- 10. (action faite par le systeme)
-- traitement 4 : voir plus bas modele d'ordre et code PL/SQL

-------------------------------------------------------------------------------
-- the end TD2a ---------------------------------------------------------------
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- TD3a PL/SQL les bases ------------------------------------------------------

/* Attention : le peuplement suivant de la base ne correspond pas au CC 
(ex : sejours pas payes), mais il permet de tester la procedure stockee PL/SQL
traitement3 pendant le TD3a. */

insert into client(idc, nom, age) values(seq_client.nextval, 'Rita', 21); 
insert into client(idc, nom, age) values(seq_client.nextval, 'Riton', 22); 
insert into village values(seq_village.nextval, 'NY', 'MOMA', 60, 300);
insert into village values(
    seq_village.nextval, 'Chatelaillon', 'kitesurf', 100, 20);
insert into village values(
    seq_village.nextval, 'Chatelaillon', 'tango', 100, 15);
insert into sejour values(seq_sejour.nextval, 1, 11, 362);
insert into sejour values(seq_sejour.nextval, 1, 11, 363);
insert into sejour values(seq_sejour.nextval, 2, 11, 363);
insert into sejour values(seq_sejour.nextval, 1, 11, 364);
insert into sejour values(seq_sejour.nextval, 1, 10, 365);
-- insert into sejour values(seq_sejour.nextval, 2, 10, 365);

select * from client;
select * from village;
select * from sejour ;

-------------------------------------------------------------------------------
-- action 1 : creer village

/* modele d'ordre :
creer_village(v, a, p, c) :
    insert into village values(seq_village.nextval, v, a, p, c);
    -- rem : pas de retour
*/

drop function creerVillage;

create or replace procedure creerVillage(
    v varchar2, a varchar2, p int, c int)
is
begin
    insert into village values(
      	   seq_village.nextval, v, a, p, c);
end;
/

exec creerVillage('Rio', 'kitesurf', 10, 25);

-------------------------------------------------------------------------------
-- traitement 3.

/* modele d'ordre :
traitement3(le_jour) :
    select count(*)
        from sejour
        where jour < le_jour
        renvoie resultat dans : le_nombre;
    delete sejour 
        where jour < le_jour;
   retour traitement3 : le_nombre;

(variante possible : compter lignes, detruire, recompter et faire difference)
*/

-- on adapte le modele d'ordre a la syntaxe PL/SQL :

drop procedure traitement3;

create or replace function traitement3(
    le_jour sejour.jour%type)
    return integer
is
    le_nombre integer;
begin
    select count(*)
        into le_nombre
	from sejour
	where jour < le_jour;
    delete sejour
        where jour < le_jour;
    return le_nombre;
end;
/

-- tester : 
set serveroutput on
-- deux select suivants inutiles dans cet exercice mais visualise la base :
select * from client;
select * from village;
select * from sejour;
exec dbms_output.put_line(traitement3(364));
select * from sejour;
exec dbms_output.put_line(traitement3(365));
select * from sejour;

-------------------------------------------------------------------------------
-- traitement 3 par procedure avec variables out : 
-- on adapte la version ci-dessus par fonction :

create or replace procedure traitement3_out(
    le_jour sejour.jour%type,
    le_nombre out integer)
is
begin
    select count(*)
        into le_nombre
	from sejour
	where jour < le_jour;
    delete sejour
        where jour < le_jour;
end;
/

select * from sejour;
declare
    n int;
begin
    traitement3_out(364, n);
    dbms_output.put_line('Nombre de sejours detruits : '||n);
end;
/
select * from sejour;
declare
    n int;
begin
    traitement3_out(365, n);
    dbms_output.put_line('Nombre de sejours detruits : '||n);
end;
/
select * from sejour;

-------------------------------------------------------------------------------
-- traitement 1 :

/* modele d'ordre :
traitement1(le_nom, l_age) :
    l_idc := seq_client.nextval; -- rem : variante par rapport a action 1
    insert into client(idc, nom, age) 
        values(l_idc, le_nom, l_age);
    retour traitement1 : l_idc;
*/

create or replace function traitement1(
    le_nom client.nom%type,
    l_age client.age%type)
    return client.idc%type
is
    l_idc client.idc%type;
begin
    l_idc := seq_client.nextval;
    insert into client(idc, nom, age) 
        values(l_idc, le_nom, l_age);
    return l_idc;
end;
/

select * from client;
exec dbms_output.put_line('nouvel identifiant : '||traitement1('Jeanne', 23));
select * from client;
exec dbms_output.put_line('nouvel identifiant : '||traitement1('Jules', 23));
select * from client;

-------------------------------------------------------------------------------
-- traitement 4 :

-- on suppose que le programmeur a cree dans l'action 0 la table
-- archive(ids, idc, idv, jour, avoir)

/* modele d'ordre : 
traitement 4(l_ids, l_idc, l_idv, le_jour)) : 
    -- ligne detruite supposee en parametre a ce stade du cours
    select avoir 
        from client 
        where idc = l_idc
        renvoie resultat dans : l_avoir
    insert into archive values(l_ids, l_idc, l_idv, le_jour, l_avoir);
*/

-- corrige non fourni mai sil n'y a pas de piege

-------------------------------------------------------------------------------
-- the end TD3a ---------------------------------------------------------------
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- TD4a PL/SQL curseurs -------------------------------------------------------

-------------------------------------------------------------------------------
-- authentification :

/* modele d'ordre : 
authentification(l_idc, le_nom) :
    select *
        from client
        where idc = l_idc
          and nom = le_nom
        resultat dans le_client;
    si resultat existe alors
        print('bienvenue'||le_client);
    sinon
        print('desole, erreur identifiant/nom');
*/

create or replace procedure authentification(
    l_idc client.idc%type,
    le_nom client.nom%type)
is
    cursor c is
        select *
	    from client
	    where idc = l_idc
	      and nom = le_nom;
    le_client c%rowtype;
    message varchar2(50);
begin
    open c;
    fetch c into le_client;
    if c%found then
        message := 'bienvenue '||le_client.nom||', '||le_client.age
	           ||' ans, avoir '||le_client.avoir;
    else
        message := 'desole, erreur identifiant/nom';
    end if;
    dbms_output.put_line(message);
end;
/

select * from client;
exec authentification(2, 'Rita');
exec authentification(1, 'Rita');

-------------------------------------------------------------------------------
-- consulter informations client :

/* modele d'ordre : 
consulter_informations(l_idc) :
    select ids, idv, jour
        from sejour
        where sejour.idc = l_idc
        afficher toutes les lignes resultat;
    select village.idv, ville, activite, prix, capacite
        from village, sejour
        where sejour.idc = l_idc
          and village.idv = sejour.idv
        afficher toutes les lignes resultat;
*/

create or replace procedure consulter_informations(
    l_idc client.idc%type)
is
    cursor c is
        select distinct village.idv, ville, activite, prix, capacite
            from village, sejour
            where sejour.idc = l_idc
              and village.idv = sejour.idv;
    l_idv village.idv%type;
    la_ville village.ville%type;
    l_activite village.activite%type;
    le_prix village.prix%type;
    la_capacite village.capacite%type;
begin
     dbms_output.put_line('sejours : ');
     for x in (
        select ids, idv, jour
            from sejour
            where sejour.idc = l_idc)
    loop
        dbms_output.put_line('sejour '||x.ids||', '||x.idv||', '||x.jour);
    end loop;

    dbms_output.put_line('villages : ');
    open c;
    fetch c into l_idv, la_ville, l_activite, le_prix, la_capacite;
    while c%found loop
        dbms_output.put_line(
	    'village '||l_idv||', a '||la_ville||', '||l_activite||', '
	    ||le_prix||' euros/jour, '||la_capacite||' lits');
       fetch c into l_idv, la_ville, l_activite, le_prix, la_capacite;
    end loop;
end;
/

select * from sejour;
select * from village;
exec consulter_informations(1);
exec consulter_informations(5);

-------------------------------------------------------------------------------
-- autres formes de curseurs :
-- corrige a completer
-- les ajouter ici sur une table toute silmple t(a int, b varchar2(10))

/*
is
    cursor c is
        select * from t;
    l t%rowtype;
begin
    open c;
    loop
        fetch c into l
        exit when c%notfound;
        dbms_output.put_line(l.a||', '||l.b);
    end loop;
end;
*/

/*
is 
    cursor c is
        select ...
begin
    for x in c loop
        ...
*/

-------------------------------------------------------------------------------
-- traitement 2 :

/* modele d'ordre :
traitement2(l_idc, la_ville, le_jour) :
    select idv, prix, activite 
        from village 
        where ville = la_ville
        order by prix decresc
        renvoie resultat dans : l_idv, le_prix, l_activite;
    si resultat existe alors 
        l_ids := seq_sejour.nextval; -- rem : il faut le renvoyer
        insert into sejour 
            values(l_ids, l_idc, l_idv, le_jour);
        update client 
            set avoir = avoir - le_prix
            where idc = l_idc;
    sinon 
        l_idv := -1; 
        l_ids = -1; 
        l_activite := 'neant';
    retour traitement2 : l_idv, l_ids, l_activite;
*/

create or replace procedure traitement2(
    l_idc client.idc%type,
    la_ville village.ville%type,
    le_jour sejour.jour%type,
    l_idv out village.idv%type,
    l_ids out sejour.ids%type,
    l_activite out village.activite%type)   
is
    cursor c is
        select idv, prix, activite 
            from village 
            where ville = la_ville
            order by prix desc;
    le_prix village.prix%type;
begin
    open c;
    fetch c into l_idv, le_prix, l_activite;
    if c%found then
        l_ids := seq_sejour.nextval; 
        insert into sejour 
            values(l_ids, l_idc, l_idv, le_jour);
        update client 
            set avoir = avoir - le_prix
            where idc = l_idc;
    else
        l_idv := -1;
	l_ids := -1;
	l_activite := 'neant';
    end if;
end;
/

select * from client;
select * from village;
select * from sejour;

declare
    iv village.idv%type;
    l_ids sejour.ids%type;
    a village.activite%type;
begin
    traitement2(1, 'Chatelaillon', 361, iv, l_ids, a);
    dbms_output.put_line('idv '||iv||', ids '||l_ids||', activite '||a);
    traitement2(1, 'Chatelaillon', 360, iv, l_ids, a);
    dbms_output.put_line('idv '||iv||', ids '||l_ids||', activite '||a);
end;
/
select * from sejour;

-------------------------------------------------------------------------------
-- the end TD4a ---------------------------------------------------------------
-------------------------------------------------------------------------------
