drop view Historique_Janvier;

create view Historique_Janvier
(Identifiant_Client,Nom_Client,Janvier_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '01'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '01'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '01';
	
drop view Historique_Fevrier;

create view Historique_Fevrier
(Identifiant_Client,Nom_Client,Fevrier_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '02'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '02'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '02';
	
	
drop view Historique_Mars;

create view Historique_Mars
(Identifiant_Client,Nom_Client,Mars_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '03'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '03'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '03';
	
		
drop view Historique_Avril;

create view Historique_Avril
(Identifiant_Client,Nom_Client,Avril_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '04'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '04'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '04';
	
	
drop view Historique_Mai;


create view Historique_Mai
(Identifiant_Client,Nom_Client,Mai_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '05'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '05'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '05';

	
drop view Historique_Juin;

create view Historique_Juin
(Identifiant_Client,Nom_Client,Juin_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '06'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '06'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '06';
	
		
drop view Historique_Juillet;

create view Historique_Juillet
(Identifiant_Client,Nom_Client,Juillet_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '07'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '07'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '07';


	
drop view Historique_Aout;

create view Historique_Aout
(Identifiant_Client,Nom_Client,Aout_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '08'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '08'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '08';
	
drop view Historique_Septembre;

create view Historique_Septembre
(Identifiant_Client,Nom_Client,Septembre_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '09'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '09'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '09';

	
drop view Historique_Octobre;

create view Historique_Octobre
(Identifiant_Client,Nom_Client,Octobre_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '10'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '10'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '10';


	
drop view Historique_Novembre;

create view Historique_Novembre
(Identifiant_Client,Nom_Client,Novembre_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '11'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '11'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '11';

	
drop view Historique_Decembre;

create view Historique_Decembre
(Identifiant_Client,Nom_Client,Decembre_HT)
as 
select 
f.Identifiant_Client,
f.Nom_Client,
f.Prix_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '12'
UNION
select
11,
'MONTANT TOTAL HT' AS MONTANT, 
sum(f.Prix_HT) AS PRIX_HT
from Facture f
	where to_char(f.Date_Commande,'MM')= '12'
UNION
select
12,
'MONTANT TOTAL TTC' AS MONTANT, 
sum(f.Prix_TTC) AS PRIX_TTC
from Facture f
	where to_char(f.Date_Commande,'MM')= '12';
	
drop view Echeancier;

create view Echeancier
(Identifiant_Client,Nom_Client,
Janvier_HT,
Fevrier_HT ,
Mars_HT ,
Avril_HT ,
Mai_HT ,
Juin_HT ,
Juillet_HT ,
Aout_HT ,
Septembre_HT ,
Octobre_HT ,
Novembre_HT ,
Decembre_HT)
as select 
c.Client_Id ,
c.Nom,
janvier.Janvier_HT ,
fevrier.Fevrier_HT ,
mars.Mars_HT ,
avril.Avril_HT ,
mai.Mai_HT ,
juin.Juin_HT ,
juillet.Juillet_HT ,
aout.Aout_HT  ,
septembre.Septembre_HT ,
octobre.Octobre_HT ,
novembre.Novembre_HT ,
decembre.Decembre_HT 
from 
Client c, 
Historique_Janvier janvier,
Historique_Fevrier fevrier,
Historique_Mars mars,
Historique_Avril avril,
Historique_Mai mai,
Historique_Juin juin,
Historique_Juillet juillet,
Historique_Aout aout,
Historique_Septembre septembre,
Historique_Octobre octobre,
Historique_Novembre novembre,
Historique_Decembre decembre
where c.Client_Id = janvier.Identifiant_Client(+)
AND c.Client_Id = fevrier.Identifiant_Client(+)
AND c.Client_Id = mars.Identifiant_Client(+)
AND c.Client_Id = avril.Identifiant_Client(+)
AND c.Client_Id = mai.Identifiant_Client(+)
AND c.Client_Id = juin.Identifiant_Client(+)
AND c.Client_Id = juillet.Identifiant_Client(+)
AND c.Client_Id = aout.Identifiant_Client(+)
AND c.Client_Id = septembre.Identifiant_Client(+)
AND c.Client_Id = octobre.Identifiant_Client(+)
AND c.Client_Id = novembre.Identifiant_Client(+)
AND c.Client_Id = decembre.Identifiant_Client(+)
	order by c.Client_Id; 