-- ********************************************************************
-- Version 1.0.0 - 27/05/2008
-- Buts : 
--    Créer une table de référence du Cac40
--    Extraitre des fichiers de cours issus d'Internet ceux du Cac40
--    Chainer les cours du Cac40 afin de naviguer plus simplement
--       d'une séance à une autre.
-- ********************************************************************

select 'Vidage de la base...';
DROP TABLE IF EXISTS import_cours;
DROP TABLE IF EXISTS cac;
DROP TABLE IF EXISTS cours;
VACUUM;


-- ********************************************************************
-- Table temporaire pour importer les fichiers issus d'internet
--
-- Format de l'import (séparé par des tabulations)
-- rga,lib,JJ/MM/DD,Pre,Bas,Hau,Der,Vol
-- exemple :"ZAE000015228	HARMONY GOLD      	31/10/05	8.58000	8.85	8.58	8.84	13716"
-- ********************************************************************

DROP TABLE IF EXISTS import_cours;
CREATE TABLE import_cours (
  rga TEXT,
  lib TEXT,
  JJMMAA TEXT,
  Pre NUMBER,
  Bas NUMBER,
  Hau NUMBER,
  Der NUMBER,
  Vol NUMBER
);

-- ********************************************************************
-- Table CAC 
--
-- Contient les libellés des valeurs du CAC 40
-- ********************************************************************
DROP TABLE IF EXISTS cac;
CREATE TABLE cac (
  RGA TEXT,
  LIB TEXT
);

-- ********************************************************************
-- Table Cours 
--
-- Contient les cours du CAC40
--
-- ID : identifiant séquentiel du cours.
-- Rga : pour le lien avec la table de libellé
-- DAT : Date en format iso : AAAAMMDD
-- Pre : Premier cours de la valeur
-- Der : Dernier cours de la valeur
-- Next : permet de trouver le cours du lendemain de cette valeur
-- Prior : permet de trouver le cours précédent de cette valeur
-- ********************************************************************

DROP TABLE IF EXISTS cours;
CREATE TABLE cours (
  id INTEGER PRIMARY KEY,
  rga TEXT,
  Dat INTEGER,
  Pre NUMBER,
  Der NUMBER,
  lastId INTEGER,
  nextId INTEGER
);
CREATE INDEX IF NOT EXISTS idx_cours_rga ON cours (rga);
CREATE INDEX IF NOT EXISTS idx_cours_dat ON cours (dat);
CREATE UNIQUE INDEX IF NOT EXISTS idx_cours_lastId ON cours (lastId);
CREATE UNIQUE INDEX IF NOT EXISTS idx_cours_nextId ON cours (nextId);


-- ********************************************************************
-- Generation d'une double liste chainee dans les cours
-- ********************************************************************
DROP TRIGGER IF EXISTS tg_cours;
CREATE TRIGGER tg_cours AFTER INSERT ON COURS
FOR EACH ROW BEGIN
  UPDATE cours SET lastid = (SELECT MAX(id) FROM cours WHERE rga = new.rga and id <> new.rowid)
    WHERE ID = new.rowid; 
  UPDATE cours SET nextid = (SELECT new.rowid FROM cours)
    WHERE id = (select lastid from cours where id=new.rowid); 
END;



-- ********************************************************************
-- Lancement de l'importation
-- ********************************************************************
.mode tab
select 'Chargement SRD annee 2005...';
.import datas/2005.txt import_cours 
select 'Chargement SRD annee 2006...';
.import datas/2006.txt import_cours 
select 'Chargement SRD annee 2007...';
.import datas/2007.txt import_cours 
select 'Chargement SRD annee 2008...';
.import datas/2008.txt import_cours 
select 'Ok, ' || count(*) || ' lignes lues.' from import_cours;


-- ********************************************************************
-- Chargement du CAC
-- ********************************************************************
BEGIN TRANSACTION;
select 'Chargement valeurs  du CAC40...';

INSERT INTO CAC VALUES ('FR0000120404','ACCOR');
INSERT INTO CAC VALUES ('FR0000031122','AIR FRANCE - KLM');
INSERT INTO CAC VALUES ('FR0000120073','AIR LIQUIDE');
INSERT INTO CAC VALUES ('FR0000130007','ALCATEL LUCENT');
INSERT INTO CAC VALUES ('FR0010220475','ALSTOM');
INSERT INTO CAC VALUES ('LU0323134006','ARCELORMITTAL NV');
INSERT INTO CAC VALUES ('FR0000120628','AXA');
INSERT INTO CAC VALUES ('FR0000131104','BNP PARIBAS');
INSERT INTO CAC VALUES ('FR0000120503','BOUYGUES');
INSERT INTO CAC VALUES ('FR0000125338','CAP GEMINI');
INSERT INTO CAC VALUES ('FR0000120172','CARREFOUR');
INSERT INTO CAC VALUES ('FR0000045072','CREDIT AGRICOLE');
INSERT INTO CAC VALUES ('BE0003796134','DEXIA');
INSERT INTO CAC VALUES ('NL0000235190','EADS');
INSERT INTO CAC VALUES ('FR0010242511','EDF');
INSERT INTO CAC VALUES ('FR0000121667','ESSILOR INTL');
INSERT INTO CAC VALUES ('FR0000133308','FRANCE TELECOM');
INSERT INTO CAC VALUES ('FR0010208488','GAZ DE FRANCE');
INSERT INTO CAC VALUES ('FR0000120644','GROUPE DANONE');
INSERT INTO CAC VALUES ('FR0000120321','L''OREAL');
INSERT INTO CAC VALUES ('FR0000120537','LAFARGE');
INSERT INTO CAC VALUES ('FR0000130213','LAGARDERE SCA');
INSERT INTO CAC VALUES ('FR0000121014','LVMH MOET HENNESSY');
INSERT INTO CAC VALUES ('FR0000121261','MICHELIN');
INSERT INTO CAC VALUES ('FR0000120693','PERNOD-RICARD');
INSERT INTO CAC VALUES ('FR0000121501','PEUGEOT SA');
INSERT INTO CAC VALUES ('FR0000121485','PPR');
INSERT INTO CAC VALUES ('FR0000131906','RENAULT');
INSERT INTO CAC VALUES ('FR0000125007','SAINT-GOBAIN');
INSERT INTO CAC VALUES ('FR0000120578','SANOFI-AVENTIS');
INSERT INTO CAC VALUES ('FR0000121972','SCHNEIDER ELECTRIC');
INSERT INTO CAC VALUES ('FR0000130809','SOCIETE GENERALE A');
INSERT INTO CAC VALUES ('NL0000226223','STMICROELECTRONICS');
INSERT INTO CAC VALUES ('FR0000120529','SUEZ');
INSERT INTO CAC VALUES ('FR0000120271','TOTAL');
INSERT INTO CAC VALUES ('FR0000124711','UNIBAIL-RODAMCO');
INSERT INTO CAC VALUES ('FR0000120354','VALLOUREC');
INSERT INTO CAC VALUES ('FR0000124141','VEOLIA ENVIRON.');
INSERT INTO CAC VALUES ('FR0000125486','VINCI');
INSERT INTO CAC VALUES ('FR0000127771','VIVENDI');

-- ********************************************************************
-- Epuration de la base
-- ********************************************************************

select 'Indexation...';
CREATE INDEX IF NOT EXISTS idx_import_cours_rga ON import_cours (rga);
CREATE INDEX IF NOT EXISTS idx_cac_rga ON cac (rga);


select 'Extraction des cours des valeurs du CAC 40...';
INSERT INTO cours
  SELECT NULL, i.rga, 
    (2000+substr(i.jjmmaa,7,2))*10000 + substr(i.jjmmaa,4,2)*100 + substr(i.jjmmaa,1,2) as Mois, 
    i.pre, i.der, NULL, NULL 
  FROM import_cours i, cac c
  WHERE i.rga = c.rga order by Mois;

select 'Suppression de la table temporaire...';
DROP TABLE IF EXISTS import_cours;
DROP TRIGGER IF EXISTS tg_cours;
END TRANSACTION;
VACUUM;

select '';
select '';
select "Resultat de l'importation :";
select '';
select 'Table "Cac"   = valeurs du CAC : '||count(*)||' lignes' from cac;
select 'Table "Cours" = cours du CAC   : '||count(*)||' lignes' from cours;
select '';
select 'Ok.';
select '';