ah oui

[Marc SCHAEFER]

un truc que j'ai pondu avant d'avoir suivi le cours compta :)

n'hésitez pas à jeter :)

# TODO
#    - fait_par REFERENCES this
#    - auth through this.
# BUGS
#    - Display routines sometimes count on numerical identifiant.
# MODIFICATION-HISTORY
# $Id: SQL,v 1.1 2001/03/03 11:35:04 schaefer Exp $
CREATE TABLE employe (initiales VARCHAR(2) NOT NULL,
                      nom VARCHAR(30) NOT NULL,
                      password VARCHAR(8) NOT NULL,
                      administrateur BOOL NOT NULL DEFAULT 'f',
                      UNIQUE(initiales), PRIMARY KEY(initiales));

CREATE TABLE contact (id SERIAL,
                      compagnie VARCHAR(30),
                      contact VARCHAR(30) NOT NULL,
                      telephone VARCHAR(20),
                      fax VARCHAR(20),
                      email VARCHAR(30),
                      npa_ville VARCHAR(30) NOT NULL,
                      adresse_1 VARCHAR(30) NOT NULL,
                      adresse_2 VARCHAR(30),
                      UNIQUE(id), PRIMARY KEY(id));

CREATE TABLE compte (nom VARCHAR(20) NOT NULL,
                     description text NOT NULL,
                     UNIQUE(nom), PRIMARY KEY(nom));

CREATE TABLE entree_type (nom VARCHAR(20) NOT NULL,
                          depense BOOL NOT NULL,
                          description TEXT NOT NULL,
                          valeur MONEY NOT NULL,
                          UNIQUE(nom), PRIMARY KEY (nom));

CREATE TABLE entree (id SERIAL,
                     date DATE NOT NULL DEFAULT CURRENT_DATE,
                     employe_initiales VARCHAR(2) NOT NULL REFERENCES employe,
                     entree_type_nom VARCHAR(20) NOT NULL
                        REFERENCES entree_type,
                     compte_nom VARCHAR(20) NOT NULL
                        REFERENCES compte,
                     contact_id INT4 NOT NULL REFERENCES contact,
                     nombre INT2 NOT NULL DEFAULT 1,
                     correction MONEY NOT NULL DEFAULT 0,
                     commentaire text,
                     UNIQUE(id), PRIMARY KEY(id));

# Examples

INSERT INTO employe(initiales, nom, password, administrateur)
   VALUES ('ms', 'Marc SCHAEFER', 'demo', 't');

INSERT INTO compte(nom, description)
   VALUES ('CCP', 'Compte de Chèques Postaux 20-33957-9');

INSERT INTO compte(nom, description)
   VALUES ('Caisse', 'Caisse');

INSERT INTO entree_type(nom, depense, description, valeur)
   VALUES ('100-CDR-RICOH', 't', 'Achat 100 CDRs Ricoh Internex', '120');

INSERT INTO entree_type(nom, depense, description, valeur)
   VALUES ('100-CDR-VIVASTAR', 't', 'Achat 100 CDRs Vivastar', '130');

INSERT INTO contact(id, compagnie, contact, telephone, fax, email, npa_ville,
                    adresse_1)
   VALUES (1, 'Demo SA', 'Guillaume Tell', '1234', '5678', 's at t', '2 1', 'a');

INSERT INTO entree(employe_initiales, entree_type_nom, compte_nom, contact_id,
                   nombre)
   VALUES ('ms', '100-CDR', 'Caisse', 1, 1);

SELECT e.initiales,e.nom,c.nom,c.description,et.nom,et.depense,
       et.description,et.valeur,ct.email,ct.compagnie,ent.nombre,
#       ent.nombre * et.valeur * (et.depense ? -1 : 1) AS total
       ent.nombre * et.valeur AS total_depenses
   FROM employe e, compte c, entree_type et, contact ct, entree ent
   WHERE (ent.employe_initiales = e.initiales)
         AND (c.nom = ent.compte_nom)
         AND (et.nom = ent.entree_type_nom)
         AND (ct.id = ent.contact_id);

SELECT e.initiales,c.nom,et.depense,et.nom,et.valeur,ct.compagnie,ent.nombre, ent.nombre * et.valeur AS total_depenses FROM employe e, compte c, entree_type et, contact ct, entree ent WHERE (ent.employe_initiales = e.initiales) AND (c.nom = ent.compte_nom
) AND (et.nom = ent.entree_type_nom) AND (ct.id = ent.contact_id);

SELECT SUM((ent.nombre * et.valeur) + ent.correction) AS depenses FROM employe e, compte c, entree_type et, contact ct, entree ent WHERE (ent.employe_initiales = e.initiales) AND (c.nom = ent.compte_nom) AND (et.nom = ent.entree_type_nom) AND (ct.id = ent
.contact_id) AND et.depense;

SELECT SUM((ent.nombre * et.valeur) + ent.correction) AS entrees FROM employe e, compte c, entree_type et, contact ct, entree ent WHERE (ent.employe_initiales = e.initiales) AND (c.nom = ent.compte_nom) AND (et.nom = ent.entree_type_nom) AND (ct.id = ent.
contact_id) AND (NOT et.depense);