1-- 2-- PostgreSQL database dump 3-- 4 5SET client_encoding = 'UTF8'; 6SET standard_conforming_strings = off; 7SET check_function_bodies = false; 8SET client_min_messages = warning; 9SET escape_string_warning = off; 10 11-- 12-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: - 13-- 14 15COMMENT ON SCHEMA public IS 'Standard public schema'; 16 17 18SET search_path = public, pg_catalog; 19 20CREATE LANGUAGE plpgsql; 21 22-- 23-- Name: resume_tickets; Type: TYPE; Schema: public; Owner: - 24-- 25 26CREATE TYPE resume_tickets AS ( 27 "transaction" bigint, 28 manifid integer, 29 nb bigint, 30 tarif character varying, 31 reduc integer, 32 printed boolean, 33 canceled boolean, 34 prix numeric, 35 prixspec numeric 36); 37 38 39-- 40-- Name: get_personneid(integer); Type: FUNCTION; Schema: public; Owner: - 41-- 42 43CREATE FUNCTION get_personneid(integer) RETURNS bigint 44 AS $_$SELECT personneid AS result FROM org_personne WHERE id = $1;$_$ 45 LANGUAGE sql STABLE STRICT; 46 47 48-- 49-- Name: FUNCTION get_personneid(integer); Type: COMMENT; Schema: public; Owner: - 50-- 51 52COMMENT ON FUNCTION get_personneid(integer) IS 'retourne l''id d''une personne investie de la fonction $1 53$1: org_personne.id'; 54 55 56-- 57-- Name: zeroifnull(bigint); Type: FUNCTION; Schema: public; Owner: - 58-- 59 60CREATE FUNCTION zeroifnull(bigint) RETURNS bigint 61 AS $_$BEGIN 62IF $1 IS NULL THEN RETURN 0; 63ELSE RETURN $1; 64END IF; 65END;$_$ 66 LANGUAGE plpgsql IMMUTABLE; 67 68 69SET default_tablespace = ''; 70 71SET default_with_oids = true; 72 73-- 74-- Name: entite; Type: TABLE; Schema: public; Owner: -; Tablespace: 75-- 76 77CREATE TABLE entite ( 78 id integer NOT NULL, 79 nom character varying(127) NOT NULL, 80 creation timestamp with time zone DEFAULT now() NOT NULL, 81 modification timestamp with time zone DEFAULT now() NOT NULL, 82 adresse text, 83 cp character varying(10), 84 ville character varying(255), 85 pays character varying(255) DEFAULT 'France'::character varying, 86 email character varying(255), 87 npai boolean DEFAULT false NOT NULL, 88 active boolean DEFAULT true NOT NULL 89); 90 91 92-- 93-- Name: TABLE entite; Type: COMMENT; Schema: public; Owner: - 94-- 95 96COMMENT ON TABLE entite IS 'entités liées à l''organisme (personnes ou organismes)'; 97 98 99-- 100-- Name: COLUMN entite.cp; Type: COMMENT; Schema: public; Owner: - 101-- 102 103COMMENT ON COLUMN entite.cp IS 'code postal de l''adresse'; 104 105 106-- 107-- Name: COLUMN entite.email; Type: COMMENT; Schema: public; Owner: - 108-- 109 110COMMENT ON COLUMN entite.email IS 'adresse email'; 111 112 113-- 114-- Name: COLUMN entite.active; Type: COMMENT; Schema: public; Owner: - 115-- 116 117COMMENT ON COLUMN entite.active IS 'permet de "supprimer" une entité dans l''application tout en gardant sa trace...'; 118 119 120-- 121-- Name: entite_id_seq; Type: SEQUENCE; Schema: public; Owner: - 122-- 123 124CREATE SEQUENCE entite_id_seq 125 INCREMENT BY 1 126 NO MAXVALUE 127 NO MINVALUE 128 CACHE 1; 129 130 131-- 132-- Name: entite_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 133-- 134 135ALTER SEQUENCE entite_id_seq OWNED BY entite.id; 136 137 138-- 139-- Name: fonction; Type: TABLE; Schema: public; Owner: -; Tablespace: 140-- 141 142CREATE TABLE fonction ( 143 id integer NOT NULL, 144 libelle character varying(127) NOT NULL 145); 146 147 148-- 149-- Name: TABLE fonction; Type: COMMENT; Schema: public; Owner: - 150-- 151 152COMMENT ON TABLE fonction IS 'Fonction liant une personne à un organisme (avec son intitulé exact par exemple)'; 153 154 155-- 156-- Name: COLUMN fonction.libelle; Type: COMMENT; Schema: public; Owner: - 157-- 158 159COMMENT ON COLUMN fonction.libelle IS 'intitulé type, servant dans les extractions par exemple'; 160 161 162-- 163-- Name: org_categorie; Type: TABLE; Schema: public; Owner: -; Tablespace: 164-- 165 166CREATE TABLE org_categorie ( 167 id integer NOT NULL, 168 libelle character varying(255) NOT NULL 169); 170 171 172-- 173-- Name: TABLE org_categorie; Type: COMMENT; Schema: public; Owner: - 174-- 175 176COMMENT ON TABLE org_categorie IS 'categories regroupant des sous catégories d''organismes'; 177 178 179-- 180-- Name: org_personne; Type: TABLE; Schema: public; Owner: -; Tablespace: 181-- 182 183CREATE TABLE org_personne ( 184 id integer NOT NULL, 185 personneid bigint NOT NULL, 186 organismeid bigint NOT NULL, 187 fonction character varying(255), 188 email character varying(255), 189 service character varying(255), 190 "type" integer, 191 telephone character varying(40), 192 description text 193); 194 195 196-- 197-- Name: TABLE org_personne; Type: COMMENT; Schema: public; Owner: - 198-- 199 200COMMENT ON TABLE org_personne IS 'liaison entre des personnes et des organismes, au titre d''une fonction dans ledit organisme'; 201 202 203-- 204-- Name: COLUMN org_personne.personneid; Type: COMMENT; Schema: public; Owner: - 205-- 206 207COMMENT ON COLUMN org_personne.personneid IS 'personne.id'; 208 209 210-- 211-- Name: COLUMN org_personne.organismeid; Type: COMMENT; Schema: public; Owner: - 212-- 213 214COMMENT ON COLUMN org_personne.organismeid IS 'organisme.id'; 215 216 217-- 218-- Name: COLUMN org_personne.fonction; Type: COMMENT; Schema: public; Owner: - 219-- 220 221COMMENT ON COLUMN org_personne.fonction IS 'fonction au titre de laquelle une personne est liée à un organisme'; 222 223 224-- 225-- Name: COLUMN org_personne.email; Type: COMMENT; Schema: public; Owner: - 226-- 227 228COMMENT ON COLUMN org_personne.email IS 'email de la personne dans l''organisme'; 229 230 231-- 232-- Name: COLUMN org_personne.service; Type: COMMENT; Schema: public; Owner: - 233-- 234 235COMMENT ON COLUMN org_personne.service IS 'Service dans l''organisme où travaille la personne'; 236 237 238-- 239-- Name: COLUMN org_personne."type"; Type: COMMENT; Schema: public; Owner: - 240-- 241 242COMMENT ON COLUMN org_personne."type" IS 'fonction.id : type de fonction'; 243 244 245-- 246-- Name: COLUMN org_personne.telephone; Type: COMMENT; Schema: public; Owner: - 247-- 248 249COMMENT ON COLUMN org_personne.telephone IS 'téléphone professionel d''une personne liée à un organisme'; 250 251 252-- 253-- Name: COLUMN org_personne.description; Type: COMMENT; Schema: public; Owner: - 254-- 255 256COMMENT ON COLUMN org_personne.description IS 'description du pro'; 257 258 259-- 260-- Name: organisme; Type: TABLE; Schema: public; Owner: -; Tablespace: 261-- 262 263CREATE TABLE organisme ( 264 url character varying(255), 265 categorie integer, 266 description text 267) 268INHERITS (entite); 269 270 271-- 272-- Name: TABLE organisme; Type: COMMENT; Schema: public; Owner: - 273-- 274 275COMMENT ON TABLE organisme IS 'structures en contact avec l''organisme'; 276 277 278-- 279-- Name: COLUMN organisme.description; Type: COMMENT; Schema: public; Owner: - 280-- 281 282COMMENT ON COLUMN organisme.description IS 'Description de l''organisme'; 283 284 285-- 286-- Name: organisme_categorie; Type: VIEW; Schema: public; Owner: - 287-- 288 289CREATE VIEW organisme_categorie AS 290 SELECT organisme.id, organisme.nom, organisme.creation, organisme.modification, organisme.adresse, organisme.cp, organisme.ville, organisme.pays, organisme.email, organisme.npai, organisme.active, organisme.url, organisme.categorie, org_categorie.libelle AS catdesc, organisme.description FROM organisme, org_categorie WHERE (((organisme.categorie = org_categorie.id) AND (organisme.categorie IS NOT NULL)) AND (organisme.active = true)) UNION SELECT organisme.id, organisme.nom, organisme.creation, organisme.modification, organisme.adresse, organisme.cp, organisme.ville, organisme.pays, organisme.email, organisme.npai, organisme.active, organisme.url, NULL::"unknown" AS categorie, NULL::"unknown" AS catdesc, organisme.description FROM organisme WHERE ((organisme.categorie IS NULL) AND (organisme.active = true)) ORDER BY 14, 2; 291 292 293-- 294-- Name: VIEW organisme_categorie; Type: COMMENT; Schema: public; Owner: - 295-- 296 297COMMENT ON VIEW organisme_categorie IS 'Liste des organismes avec leur catégorie (qui est à NULL s''ils n''en ont pas)'; 298 299 300-- 301-- Name: personne; Type: TABLE; Schema: public; Owner: -; Tablespace: 302-- 303 304CREATE TABLE personne ( 305 prenom character varying(255), 306 titre character varying(24) 307) 308INHERITS (entite); 309 310 311-- 312-- Name: TABLE personne; Type: COMMENT; Schema: public; Owner: - 313-- 314 315COMMENT ON TABLE personne IS 'contacts de l''organisme'; 316 317 318-- 319-- Name: personne_properso; Type: VIEW; Schema: public; Owner: - 320-- 321 322CREATE VIEW personne_properso AS 323 (((SELECT DISTINCT personne.id, personne.nom, personne.creation, personne.modification, personne.adresse, personne.cp, personne.ville, personne.pays, personne.email, personne.npai, personne.active, personne.prenom, personne.titre, organisme.id AS orgid, organisme.nom AS orgnom, organisme.categorie AS orgcat, organisme.adresse AS orgadr, organisme.cp AS orgcp, organisme.ville AS orgville, organisme.pays AS orgpays, organisme.email AS orgemail, organisme.url AS orgurl, organisme.description AS orgdesc, org_personne.service, org_personne.id AS fctorgid, fonction.id AS fctid, fonction.libelle AS fcttype, org_personne.fonction AS fctdesc, org_personne.email AS proemail, org_personne.telephone AS protel, organisme.catdesc AS orgcatdesc, org_personne.description FROM organisme_categorie organisme, personne, org_personne, fonction WHERE ((((personne.id = org_personne.personneid) AND (organisme.id = org_personne.organismeid)) AND (fonction.id = org_personne."type")) AND (org_personne."type" IS NOT NULL)) ORDER BY personne.id, personne.nom, personne.creation, personne.modification, personne.adresse, personne.cp, personne.ville, personne.pays, personne.email, personne.npai, personne.active, personne.prenom, personne.titre, organisme.id, organisme.nom, organisme.categorie, organisme.adresse, organisme.cp, organisme.ville, organisme.pays, organisme.email, organisme.url, organisme.description, org_personne.service, org_personne.id, fonction.id, fonction.libelle, org_personne.fonction, org_personne.email, org_personne.telephone, organisme.catdesc, org_personne.description) UNION (SELECT DISTINCT personne.id, personne.nom, personne.creation, personne.modification, personne.adresse, personne.cp, personne.ville, personne.pays, personne.email, personne.npai, personne.active, personne.prenom, personne.titre, organisme.id AS orgid, organisme.nom AS orgnom, organisme.categorie AS orgcat, organisme.adresse AS orgadr, organisme.cp AS orgcp, organisme.ville AS orgville, organisme.pays AS orgpays, organisme.email AS orgemail, organisme.url AS orgurl, organisme.description AS orgdesc, org_personne.service, org_personne.id AS fctorgid, NULL::integer AS fctid, NULL::text AS fcttype, org_personne.fonction AS fctdesc, org_personne.email AS proemail, org_personne.telephone AS protel, organisme.catdesc AS orgcatdesc, org_personne.description FROM organisme_categorie organisme, personne, org_personne WHERE (((personne.id = org_personne.personneid) AND (organisme.id = org_personne.organismeid)) AND (org_personne."type" IS NULL)) ORDER BY personne.id, personne.nom, personne.creation, personne.modification, personne.adresse, personne.cp, personne.ville, personne.pays, personne.email, personne.npai, personne.active, personne.prenom, personne.titre, organisme.id, organisme.nom, organisme.categorie, organisme.adresse, organisme.cp, organisme.ville, organisme.pays, organisme.email, organisme.url, organisme.description, org_personne.service, org_personne.id, 26, 27, org_personne.fonction, org_personne.email, org_personne.telephone, organisme.catdesc, org_personne.description)) UNION SELECT personne.id, personne.nom, personne.creation, personne.modification, personne.adresse, personne.cp, personne.ville, personne.pays, personne.email, personne.npai, personne.active, personne.prenom, personne.titre, NULL::"unknown" AS orgid, NULL::"unknown" AS orgnom, NULL::"unknown" AS orgcat, NULL::"unknown" AS orgadr, NULL::"unknown" AS orgcp, NULL::"unknown" AS orgville, NULL::"unknown" AS orgpays, NULL::"unknown" AS orgemail, NULL::"unknown" AS orgurl, NULL::"unknown" AS orgdesc, NULL::"unknown" AS service, NULL::"unknown" AS fctorgid, NULL::"unknown" AS fctid, NULL::"unknown" AS fcttype, NULL::"unknown" AS fctdesc, NULL::"unknown" AS proemail, NULL::"unknown" AS protel, NULL::"unknown" AS orgcatdesc, NULL::"unknown" AS description FROM personne) UNION SELECT NULL::"unknown" AS id, NULL::"unknown" AS nom, NULL::"unknown" AS creation, NULL::"unknown" AS modification, NULL::"unknown" AS adresse, NULL::"unknown" AS cp, NULL::"unknown" AS ville, NULL::"unknown" AS pays, NULL::"unknown" AS email, NULL::"unknown" AS npai, NULL::"unknown" AS active, NULL::"unknown" AS prenom, NULL::"unknown" AS titre, NULL::"unknown" AS orgid, NULL::"unknown" AS orgnom, NULL::"unknown" AS orgcat, NULL::"unknown" AS orgadr, NULL::"unknown" AS orgcp, NULL::"unknown" AS orgville, NULL::"unknown" AS orgpays, NULL::"unknown" AS orgemail, NULL::"unknown" AS orgurl, NULL::"unknown" AS orgdesc, NULL::"unknown" AS service, NULL::"unknown" AS fctorgid, NULL::"unknown" AS fctid, NULL::"unknown" AS fcttype, NULL::"unknown" AS fctdesc, NULL::"unknown" AS proemail, NULL::"unknown" AS protel, NULL::"unknown" AS orgcatdesc, NULL::"unknown" AS description ORDER BY 2, 12, 15, 27, 28, 24; 324 325 326-- 327-- Name: VIEW personne_properso; Type: COMMENT; Schema: public; Owner: - 328-- 329 330COMMENT ON VIEW personne_properso IS 'permet d''accéder à toutes les personnes de l''annuaire qu''elles soient pro ou non, qu''elles aient des fonctions au sein d''un organisme ou non'; 331 332 333-- 334-- Name: object; Type: TABLE; Schema: public; Owner: -; Tablespace: 335-- 336 337CREATE TABLE "object" ( 338 id bigint NOT NULL, 339 name character varying(128) NOT NULL, 340 description text 341); 342 343 344-- 345-- Name: TABLE "object"; Type: COMMENT; Schema: public; Owner: - 346-- 347 348COMMENT ON TABLE "object" IS 'Base table for a unified scape for every objects'; 349 350 351-- 352-- Name: object_id_seq; Type: SEQUENCE; Schema: public; Owner: - 353-- 354 355CREATE SEQUENCE object_id_seq 356 INCREMENT BY 1 357 NO MAXVALUE 358 NO MINVALUE 359 CACHE 1; 360 361 362-- 363-- Name: object_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 364-- 365 366ALTER SEQUENCE object_id_seq OWNED BY "object".id; 367 368 369-- 370-- Name: account; Type: TABLE; Schema: public; Owner: -; Tablespace: 371-- 372 373CREATE TABLE account ( 374 "login" character varying(32) NOT NULL, 375 "password" character varying(32) NOT NULL, 376 active boolean DEFAULT true NOT NULL, 377 expire date, 378 "level" integer DEFAULT 0 NOT NULL, 379 email character varying(255) 380) 381INHERITS ("object"); 382 383 384-- 385-- Name: COLUMN account."level"; Type: COMMENT; Schema: public; Owner: - 386-- 387 388COMMENT ON COLUMN account."level" IS 'Niveau de droits octroyé... dépend de l''application. Ici >= 10 : admin ; >= 5 : possibilité de modifier des fiches ; < 5 : consultation simple'; 389 390 391-- 392-- Name: COLUMN account.email; Type: COMMENT; Schema: public; Owner: - 393-- 394 395COMMENT ON COLUMN account.email IS 'email de l''utilisateur'; 396 397 398SET default_with_oids = false; 399 400-- 401-- Name: child; Type: TABLE; Schema: public; Owner: -; Tablespace: 402-- 403 404CREATE TABLE child ( 405 id integer NOT NULL, 406 personneid integer NOT NULL, 407 birth integer NOT NULL, 408 name text 409); 410 411 412-- 413-- Name: TABLE child; Type: COMMENT; Schema: public; Owner: - 414-- 415 416COMMENT ON TABLE child IS 'Permet de définir l''âge des enfants d''un contact'; 417 418 419-- 420-- Name: COLUMN child.personneid; Type: COMMENT; Schema: public; Owner: - 421-- 422 423COMMENT ON COLUMN child.personneid IS 'personne.id'; 424 425 426-- 427-- Name: COLUMN child.birth; Type: COMMENT; Schema: public; Owner: - 428-- 429 430COMMENT ON COLUMN child.birth IS 'year of birth'; 431 432 433-- 434-- Name: COLUMN child.name; Type: COMMENT; Schema: public; Owner: - 435-- 436 437COMMENT ON COLUMN child.name IS 'child''s name'; 438 439 440-- 441-- Name: child_id_seq; Type: SEQUENCE; Schema: public; Owner: - 442-- 443 444CREATE SEQUENCE child_id_seq 445 INCREMENT BY 1 446 NO MAXVALUE 447 NO MINVALUE 448 CACHE 1; 449 450 451-- 452-- Name: child_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 453-- 454 455ALTER SEQUENCE child_id_seq OWNED BY child.id; 456 457 458-- 459-- Name: fonction_id_seq; Type: SEQUENCE; Schema: public; Owner: - 460-- 461 462CREATE SEQUENCE fonction_id_seq 463 INCREMENT BY 1 464 NO MAXVALUE 465 NO MINVALUE 466 CACHE 1; 467 468 469-- 470-- Name: fonction_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 471-- 472 473ALTER SEQUENCE fonction_id_seq OWNED BY fonction.id; 474 475 476-- 477-- Name: groupe; Type: TABLE; Schema: public; Owner: -; Tablespace: 478-- 479 480CREATE TABLE groupe ( 481 id integer NOT NULL, 482 nom character varying(255) NOT NULL, 483 createur bigint, 484 creation timestamp with time zone DEFAULT now() NOT NULL, 485 modification timestamp with time zone DEFAULT now() NOT NULL, 486 description text 487); 488 489 490-- 491-- Name: TABLE groupe; Type: COMMENT; Schema: public; Owner: - 492-- 493 494COMMENT ON TABLE groupe IS 'groupes de personnes créés à partir du requêteur'; 495 496 497-- 498-- Name: COLUMN groupe.id; Type: COMMENT; Schema: public; Owner: - 499-- 500 501COMMENT ON COLUMN groupe.id IS 'id du groupe permettant de reconsituer le nom système de la view représentant le groupe ("grp_`id`")'; 502 503 504-- 505-- Name: COLUMN groupe.nom; Type: COMMENT; Schema: public; Owner: - 506-- 507 508COMMENT ON COLUMN groupe.nom IS 'nom usuel du groupe'; 509 510 511-- 512-- Name: COLUMN groupe.createur; Type: COMMENT; Schema: public; Owner: - 513-- 514 515COMMENT ON COLUMN groupe.createur IS 'lien vers le createur du groupe (account.id)'; 516 517 518-- 519-- Name: groupe_andreq; Type: TABLE; Schema: public; Owner: -; Tablespace: 520-- 521 522CREATE TABLE groupe_andreq ( 523 id integer NOT NULL, 524 fctid integer, 525 orgid integer, 526 orgcat integer, 527 cp character varying(10), 528 ville character varying(255), 529 npai boolean DEFAULT false, 530 email boolean DEFAULT false, 531 adresse boolean DEFAULT false, 532 infcreation date, 533 infmodification date, 534 supcreation date, 535 supmodification date, 536 groupid integer NOT NULL, 537 grpinc integer[], 538 childmax integer, 539 childmin integer 540); 541 542 543-- 544-- Name: TABLE groupe_andreq; Type: COMMENT; Schema: public; Owner: - 545-- 546 547COMMENT ON TABLE groupe_andreq IS 'chaque ligne correspond à un groupe de ET logiques qui, regroupées en OU logiques, définissent un groupe...'; 548 549 550-- 551-- Name: COLUMN groupe_andreq.fctid; Type: COMMENT; Schema: public; Owner: - 552-- 553 554COMMENT ON COLUMN groupe_andreq.fctid IS 'fonction.id'; 555 556 557-- 558-- Name: COLUMN groupe_andreq.orgid; Type: COMMENT; Schema: public; Owner: - 559-- 560 561COMMENT ON COLUMN groupe_andreq.orgid IS 'organisme.id'; 562 563 564-- 565-- Name: COLUMN groupe_andreq.orgcat; Type: COMMENT; Schema: public; Owner: - 566-- 567 568COMMENT ON COLUMN groupe_andreq.orgcat IS 'org_categorie.id'; 569 570 571-- 572-- Name: COLUMN groupe_andreq.cp; Type: COMMENT; Schema: public; Owner: - 573-- 574 575COMMENT ON COLUMN groupe_andreq.cp IS 'personne.cp LIKE ''cp%'' OR organisme.cp LIKE ''cp%'''; 576 577 578-- 579-- Name: COLUMN groupe_andreq.ville; Type: COMMENT; Schema: public; Owner: - 580-- 581 582COMMENT ON COLUMN groupe_andreq.ville IS 'personne.ville LIKE ''ville%'' OR organisme.ville LIKE ''ville%'''; 583 584 585-- 586-- Name: COLUMN groupe_andreq.npai; Type: COMMENT; Schema: public; Owner: - 587-- 588 589COMMENT ON COLUMN groupe_andreq.npai IS 'personne.npai'; 590 591 592-- 593-- Name: COLUMN groupe_andreq.email; Type: COMMENT; Schema: public; Owner: - 594-- 595 596COMMENT ON COLUMN groupe_andreq.email IS 'personne.email IS NULL => true (si une personne N''a PAS d''email)'; 597 598 599-- 600-- Name: COLUMN groupe_andreq.adresse; Type: COMMENT; Schema: public; Owner: - 601-- 602 603COMMENT ON COLUMN groupe_andreq.adresse IS 'personne.adresse IS NULL => true (une personne N''a PAS d''adresse)'; 604 605 606-- 607-- Name: COLUMN groupe_andreq.infcreation; Type: COMMENT; Schema: public; Owner: - 608-- 609 610COMMENT ON COLUMN groupe_andreq.infcreation IS 'personne.creation < infcreation'; 611 612 613-- 614-- Name: COLUMN groupe_andreq.infmodification; Type: COMMENT; Schema: public; Owner: - 615-- 616 617COMMENT ON COLUMN groupe_andreq.infmodification IS 'personne.modification < infmodification'; 618 619 620-- 621-- Name: COLUMN groupe_andreq.supcreation; Type: COMMENT; Schema: public; Owner: - 622-- 623 624COMMENT ON COLUMN groupe_andreq.supcreation IS 'personne.creation >= supcreation'; 625 626 627-- 628-- Name: COLUMN groupe_andreq.supmodification; Type: COMMENT; Schema: public; Owner: - 629-- 630 631COMMENT ON COLUMN groupe_andreq.supmodification IS 'personne.modification >= supmodification'; 632 633 634-- 635-- Name: COLUMN groupe_andreq.groupid; Type: COMMENT; Schema: public; Owner: - 636-- 637 638COMMENT ON COLUMN groupe_andreq.groupid IS 'groupe.id'; 639 640 641-- 642-- Name: COLUMN groupe_andreq.grpinc; Type: COMMENT; Schema: public; Owner: - 643-- 644 645COMMENT ON COLUMN groupe_andreq.grpinc IS 'inclusion de groupes dans la condition'; 646 647 648-- 649-- Name: COLUMN groupe_andreq.childmax; Type: COMMENT; Schema: public; Owner: - 650-- 651 652COMMENT ON COLUMN groupe_andreq.childmax IS 'date("Y") - childmax >= child.birth'; 653 654 655-- 656-- Name: COLUMN groupe_andreq.childmin; Type: COMMENT; Schema: public; Owner: - 657-- 658 659COMMENT ON COLUMN groupe_andreq.childmin IS 'date("Y") - childmin <= child.birth'; 660 661 662-- 663-- Name: groupe_andreq_id_seq; Type: SEQUENCE; Schema: public; Owner: - 664-- 665 666CREATE SEQUENCE groupe_andreq_id_seq 667 INCREMENT BY 1 668 NO MAXVALUE 669 NO MINVALUE 670 CACHE 1; 671 672 673-- 674-- Name: groupe_andreq_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 675-- 676 677ALTER SEQUENCE groupe_andreq_id_seq OWNED BY groupe_andreq.id; 678 679 680-- 681-- Name: groupe_fonctions; Type: TABLE; Schema: public; Owner: -; Tablespace: 682-- 683 684CREATE TABLE groupe_fonctions ( 685 groupid integer NOT NULL, 686 fonctionid integer NOT NULL, 687 included boolean DEFAULT false NOT NULL, 688 info text 689); 690 691 692-- 693-- Name: TABLE groupe_fonctions; Type: COMMENT; Schema: public; Owner: - 694-- 695 696COMMENT ON TABLE groupe_fonctions IS 'Liaison directe entre fonctions au sein d''un organisme et groupe... une fonction est liée à un groupe avec un booléen qui exprime si elle est exclue (false) ou inclue (true).'; 697 698 699-- 700-- Name: COLUMN groupe_fonctions.groupid; Type: COMMENT; Schema: public; Owner: - 701-- 702 703COMMENT ON COLUMN groupe_fonctions.groupid IS 'groupe.id'; 704 705 706-- 707-- Name: COLUMN groupe_fonctions.fonctionid; Type: COMMENT; Schema: public; Owner: - 708-- 709 710COMMENT ON COLUMN groupe_fonctions.fonctionid IS 'org_personne.id'; 711 712 713-- 714-- Name: COLUMN groupe_fonctions.info; Type: COMMENT; Schema: public; Owner: - 715-- 716 717COMMENT ON COLUMN groupe_fonctions.info IS 'Colonne permettant de stocker des informations subsidiaires'; 718 719 720-- 721-- Name: groupe_id_seq; Type: SEQUENCE; Schema: public; Owner: - 722-- 723 724CREATE SEQUENCE groupe_id_seq 725 INCREMENT BY 1 726 NO MAXVALUE 727 NO MINVALUE 728 CACHE 1; 729 730 731-- 732-- Name: groupe_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 733-- 734 735ALTER SEQUENCE groupe_id_seq OWNED BY groupe.id; 736 737 738-- 739-- Name: groupe_personnes; Type: TABLE; Schema: public; Owner: -; Tablespace: 740-- 741 742CREATE TABLE groupe_personnes ( 743 groupid integer NOT NULL, 744 personneid integer NOT NULL, 745 included boolean DEFAULT false NOT NULL, 746 info text 747); 748 749 750-- 751-- Name: TABLE groupe_personnes; Type: COMMENT; Schema: public; Owner: - 752-- 753 754COMMENT ON TABLE groupe_personnes IS 'Liaison directe entre personnes et groupe... une personne est liée à un groupe avec un booléen qui exprime si elle est exclue (false) ou inclue (true).'; 755 756 757-- 758-- Name: COLUMN groupe_personnes.groupid; Type: COMMENT; Schema: public; Owner: - 759-- 760 761COMMENT ON COLUMN groupe_personnes.groupid IS 'groupe.id'; 762 763 764-- 765-- Name: COLUMN groupe_personnes.included; Type: COMMENT; Schema: public; Owner: - 766-- 767 768COMMENT ON COLUMN groupe_personnes.included IS 'la personne est incluse dans le groupe ? (si non : elle est exclue)'; 769 770CREATE FUNCTION addpreresa(bigint, bigint, integer, integer, boolean, character varying, integer) RETURNS boolean 771 AS $_$ 772DECLARE 773account ALIAS FOR $1; 774transac ALIAS FOR $2; 775manif ALIAS FOR $3; 776reduction ALIAS FOR $4; 777annulation ALIAS FOR $5; 778tarif ALIAS FOR $6; 779nbloops ALIAS FOR $7; 780nb integer; 781tarif_id integer; 782 783BEGIN 784 785nb := 0; 786 787tarif_id := get_tarifid(manif,tarif); 788 789WHILE nb < ABS(nbloops) LOOP 790 nb := nb + 1; 791 INSERT INTO reservation_pre ("accountid","manifid","tarifid","reduc","transaction","annul") 792 VALUES ( account, manif,tarif_id,reduction,transac,annulation ); 793END LOOP; 794 795RETURN nb > 0; 796END;$_$ 797 LANGUAGE plpgsql; 798 799 800CREATE FUNCTION contingeanting(bigint, bigint, bigint, bigint) RETURNS boolean 801 AS $_$BEGIN 802PERFORM * FROM contingeant WHERE transaction = $1; 803IF ( FOUND ) 804THEN RETURN false; 805ELSE INSERT INTO contingeant (transaction,accountid,personneid,fctorgid) VALUES ($1,$2,$3,$4); 806 RETURN true; 807END IF; 808END;$_$ 809 LANGUAGE plpgsql; 810 811 812COMMENT ON FUNCTION contingeanting(bigint, bigint, bigint, bigint) IS 'fonction permettant d''ajouter _au_besoin_ une entrée dans la table contingeant. 813retourne true si aucun enregistrement n''existait avant l''appel à la fonction (qui en a alors rajouté un), 814retourne false sinon. 815$1: transaction 816$2: accountid 817$3: personneid 818$4: fctorgid'; 819 820 821CREATE FUNCTION counttickets(bigint, boolean) RETURNS bigint 822 AS $_$SELECT count(*) AS RESULT 823FROM reservation_cur AS resa 824WHERE resa.canceled = false 825AND resa_preid = $1;$_$ 826 LANGUAGE sql STABLE STRICT; 827 828 829-- BIG one... 830CREATE FUNCTION decontingeanting(bigint, integer, bigint, integer, integer, integer, integer) RETURNS boolean 831 AS $_$DECLARE 832trans ALIAS FOR $1; 833manif ALIAS FOR $2; 834account ALIAS FOR $3; 835oldtarif ALIAS FOR $4; 836newtarif ALIAS FOR $5; 837reduction ALIAS FOR $6; 838qty ALIAS FOR $7; 839 840i INTEGER := 0; 841selled INTEGER := 0; 842mass RECORD; 843BEGIN 844 845-- calcul du nombre de places vendues 846selled := (SELECT nb FROM masstickets WHERE tarifid = newtarif AND manifid = manif AND transaction = trans) - qty; 847 848-- Si on a rien vendu, on ne met rien à jour 849IF ( selled <= 0 ) THEN RETURN true; END IF; 850 851-- Mise à jour de la table masstickets (on doit avoir qqch à mettre à jour) 852UPDATE masstickets SET nb = qty WHERE tarifid = newtarif AND manifid = manif AND transaction = trans; 853IF ( NOT FOUND ) THEN RETURN false; END IF; 854 855LOOP 856-- condition de sortie de boucle 857IF ( i >= selled ) THEN RETURN true; END IF; 858 859-- Si on n'a pas de pré-resa en attente... on en ajoute à la volée 860PERFORM * FROM reservation_pre AS resa WHERE transaction = trans AND manifid = manif AND tarifid = oldtarif; 861IF ( NOT FOUND ) 862THEN 863 INSERT INTO reservation_pre (transaction,accountid,manifid,tarifid,reduc) SELECT trans, account, manif, oldtarif, 0; 864 IF ( NOT FOUND ) 865 THEN RETURN false; 866 END IF; 867END IF; 868 869-- On passe les pré-resa en résa réelle (puisque les tickets ont été vendus) 870INSERT INTO reservation_cur (resa_preid,accountid) 871VALUES ((SELECT MIN(id) AS resa_preid 872 FROM reservation_pre AS resa 873 WHERE transaction = trans AND manifid = manif AND account != 0 AND tarifid = oldtarif), account); 874IF ( NOT FOUND ) THEN RETURN false; END IF; 875 876-- On met à jour la nature des tarifs (on doit avoir qqch à mettre à jour) 877UPDATE reservation_pre 878SET tarifid = newtarif, reduc = reduction 879WHERE id = (SELECT MIN(id) AS min FROM reservation_pre AS resa WHERE transaction = trans AND tarifid = oldtarif AND manifid = manif); 880IF ( NOT FOUND ) THEN RETURN false; END IF; 881 882i := i+1; 883 884END LOOP; 885RETURN true; 886END;$_$ 887 LANGUAGE plpgsql STRICT; 888 889 890CREATE FUNCTION getprice(integer, character varying) RETURNS numeric 891 AS $_$DECLARE 892 buf NUMERIC; 893BEGIN 894 895 buf := ( SELECT prix 896 FROM manifestation_tarifs 897 WHERE manifestationid = $1 898 AND tarifid = get_tarifid($1,$2)); 899 IF ( buf IS NOT NULL ) 900 THEN RETURN buf; 901 END IF; 902 903 buf := ( SELECT prix 904 FROM tarif 905 WHERE id = get_tarifid($1,$2)); 906 RETURN buf; 907END;$_$ 908 LANGUAGE plpgsql STABLE STRICT; 909