[Phpcompta-dev] r353 - in trunk/coprop: include sql

[phpcompta-dev]

Author: danydb
Date: 2012-04-13 18:46:11 +0200 (Fri, 13 Apr 2012)
New Revision: 353

Added:
   trunk/coprop/sql/create_table.sql
Modified:
   trunk/coprop/include/class_install_plugin.php
Log:
Auto install ok

Modified: trunk/coprop/include/class_install_plugin.php
===================================================================
--- trunk/coprop/include/class_install_plugin.php       2012-04-12 22:30:19 UTC 
(rev 352)
+++ trunk/coprop/include/class_install_plugin.php       2012-04-13 16:46:11 UTC 
(rev 353)
@@ -43,18 +43,21 @@
                $this->cn->start();
                // create the schema
                $this->create_schema();
+
                // create table + put default values
                $this->create_card();
 
                $this->create_table_parameter();
 
+               $this->create_table();
+
                $this->cn->commit();
        }
 
        function create_schema()
        {
                $this->cn->exec_sql('create schema coprop');
-                $this->cn->exec_sql("create sequence coprop.appel_fond_id");
+        $this->cn->exec_sql("create sequence coprop.appel_fond_id");
        }
 
        function create_card()
@@ -79,7 +82,7 @@
                $lot = $lot_def->id;
 
                $imm_def = new Fiche_Def($this->cn);
-               $fiche_def->add(array(
+               $imm_def->add(array(
                        'FICHE_REF' => 15,
                        'nom_mod' => 'immeuble - plugin',
                        'class_base' => '')
@@ -87,12 +90,13 @@
                $immeuble = $imm_def->id;
 
                // creation attribut
-               $this->cn->exec_sql("           insert into attr_def 
(ad_id,ad_text,ad_type,ad_size,ad_extra)
-               values  ('71','Copropriétaire','select','22','select 
f_id,vw_name from vw_fiche_attr where fd_id = $1 '),
-               ('70','Immeuble','select','22','select f_id,vw_name from 
vw_fiche_attr where fd_id = $2');", array($copro, $immeuble));
+               $this->cn->exec_sql("insert into attr_def 
(ad_id,ad_text,ad_type,ad_size,ad_extra)
+               values  ('71','Copropriétaire','select','22','select 
f_id,vw_name from vw_fiche_attr where fd_id = $copro ')");
+               $this->cn->exec_sql("insert into attr_def 
(ad_id,ad_text,ad_type,ad_size,ad_extra) values
+               ('70','Immeuble','select','22','select f_id,vw_name from 
vw_fiche_attr where fd_id = $immeuble ');");
 
                $lot_def->InsertAttribut(71); // lien vers coprop
-               $lot_def->InsertAttribut(72);// lien vers immeuble
+               $lot_def->InsertAttribut(70);// lien vers immeuble
 
                $imm_def->InsertAttribut(14); // adresse
                $imm_def->InsertAttribut(15); // code postale
@@ -115,10 +119,10 @@
                                JOIN ( SELECT fd1.f_id, fd1.ad_value
                                FROM fiche_detail fd1
                                WHERE fd1.ad_id = 71) c ON c.f_id = a.f_id
-                               WHERE f1.fd_id = $1 AND a.ad_id = 1", 
array($lot));
+                               WHERE f1.fd_id = ".$lot." AND a.ad_id = 1");
                $this->lot_id=$lot;
                $this->immeuble_id=$immeuble;
-               $this->coprop_id=$coprop;
+               $this->coprop_id=$copro;
 
        }
 
@@ -148,5 +152,12 @@
                        $this->cn->exec_sql('insert into 
coprop.parameter(pr_id,pr_value) values ($1,$2)', array($code, $value));
                }
        }
+       function create_table()
+       {
 
+               $file=__DIR__."/../sql/create_table.sql";
+               $this->cn->execute_script($file);
+
+       }
+
 }

Added: trunk/coprop/sql/create_table.sql
===================================================================
--- trunk/coprop/sql/create_table.sql                           (rev 0)
+++ trunk/coprop/sql/create_table.sql   2012-04-13 16:46:11 UTC (rev 353)
@@ -0,0 +1,135 @@
+begin;
+CREATE TABLE coprop.budget
+(
+  b_id serial NOT NULL,
+  b_name text, -- nom budget
+  b_amount numeric(20,4) NOT NULL DEFAULT 0, -- Budget total
+  b_exercice bigint,
+  b_type character varying(8),
+  CONSTRAINT budget_pkey PRIMARY KEY (b_id )
+);
+
+COMMENT ON TABLE coprop.budget  IS 'Budget parent';
+COMMENT ON COLUMN coprop.budget.b_name IS 'nom budget';
+COMMENT ON COLUMN coprop.budget.b_amount IS 'Budget total';
+COMMENT ON COLUMN coprop.budget.b_exercice IS 'Budget exercice';
+COMMENT ON COLUMN coprop.budget.b_type IS 'Budget type : OPER ou PREV';
+
+
+CREATE TABLE coprop.clef_repartition
+(
+  cr_id serial NOT NULL,
+  cr_name text NOT NULL,
+  cr_note text,
+  cr_tantieme bigint NOT NULL DEFAULT 0, -- tantieme totaux
+  CONSTRAINT clef_repartition_pkey PRIMARY KEY (cr_id )
+);
+
+COMMENT ON COLUMN coprop.clef_repartition.cr_tantieme IS 'tantieme totaux';
+
+CREATE TABLE coprop.budget_detail
+(
+  bt_id serial NOT NULL,
+  bt_label character varying(60) NOT NULL,
+  f_id bigint NOT NULL, -- fk fiche.f_id
+  b_id bigint, -- fk budget.b_id
+  bt_amount numeric(20,4),
+  cr_id bigint, -- Fk vers clef_repartition
+  CONSTRAINT budget_detail_pkey PRIMARY KEY (bt_id ),
+  CONSTRAINT budget_detail_budget_fk FOREIGN KEY (b_id)
+      REFERENCES coprop.budget (b_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE,
+  CONSTRAINT budget_detail_clef FOREIGN KEY (cr_id)
+      REFERENCES coprop.clef_repartition (cr_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE,
+  CONSTRAINT budget_detail_fiche_fk FOREIGN KEY (f_id)
+      REFERENCES fiche (f_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE,
+  CONSTRAINT bt_amount_ck CHECK (bt_amount > 0::numeric)
+);
+COMMENT ON TABLE coprop.budget_detail  IS 'Detail budget';
+COMMENT ON COLUMN coprop.budget_detail.f_id IS 'fk fiche.f_id';
+COMMENT ON COLUMN coprop.budget_detail.b_id IS 'fk budget.b_id';
+COMMENT ON COLUMN coprop.budget_detail.cr_id IS 'Fk vers clef_repartition';
+
+CREATE TABLE coprop.clef_repartition_detail
+(
+  crd_id serial NOT NULL,
+  lot_fk bigint,
+  crd_amount numeric(20,4) DEFAULT 0,
+  cr_id bigint,
+  CONSTRAINT clef_repartition_detail_pkey PRIMARY KEY (crd_id ),
+  CONSTRAINT clef_repartition_detail_cr_id_fkey FOREIGN KEY (cr_id)
+      REFERENCES coprop.clef_repartition (cr_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+
+CREATE TABLE coprop.appel_fond
+(
+  af_id bigint NOT NULL,
+  af_date date NOT NULL, 
+  af_confirmed character(1) NOT NULL DEFAULT 'N'::bpchar, 
+  af_percent numeric(4,2) NOT NULL DEFAULT 0,
+  af_amount numeric(20,4) NOT NULL DEFAULT 0, 
+  af_card bigint,
+  af_ledger bigint,
+  tech_per timestamp with time zone NOT NULL DEFAULT now(),
+  jr_internal text, 
+  b_id bigint,
+  cr_id bigint,
+  CONSTRAINT appel_fond_pkey PRIMARY KEY (af_id ),
+  CONSTRAINT appel_fond_af_card_fkey FOREIGN KEY (af_card)
+      REFERENCES fiche (f_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE,
+  CONSTRAINT appel_fond_af_ledger_fkey FOREIGN KEY (af_ledger)
+      REFERENCES jrn_def (jrn_def_id) MATCH SIMPLE
+      ON UPDATE RESTRICT ON DELETE RESTRICT,
+  CONSTRAINT appel_fond_b_id_fkey FOREIGN KEY (b_id)
+      REFERENCES coprop.budget (b_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE,
+  CONSTRAINT appel_fond_cr_id_fkey FOREIGN KEY (cr_id)
+      REFERENCES coprop.clef_repartition (cr_id) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE,
+  CONSTRAINT appel_fond_jr_internal_fkey FOREIGN KEY (jr_internal)
+      REFERENCES jrn (jr_internal) MATCH SIMPLE
+      ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+COMMENT ON TABLE coprop.appel_fond  IS 'appel de fond';
+COMMENT ON COLUMN coprop.appel_fond.af_date IS 'date de l''appel de fond';
+COMMENT ON COLUMN coprop.appel_fond.af_confirmed IS 'confirmé ou temp (Y/N)';
+COMMENT ON COLUMN coprop.appel_fond.af_percent IS '% sur le budget';
+COMMENT ON COLUMN coprop.appel_fond.af_amount IS 'montant donné ou calculé';
+COMMENT ON COLUMN coprop.appel_fond.af_card IS 'fiche appel fond';
+COMMENT ON COLUMN coprop.appel_fond.af_ledger IS 'journal pour enregistrer af';
+COMMENT ON COLUMN coprop.appel_fond.jr_internal IS 'opération correspondante';
+CREATE TABLE coprop.appel_fond_detail
+(
+  afd_id serial NOT NULL,
+  af_id bigint NOT NULL, -- appel de fond
+  lot_id bigint NOT NULL,
+  key_id bigint NOT NULL,
+  afd_amount numeric(20,4),
+  key_tantieme numeric(20,4), -- tantième de la clef
+  lot_tantieme numeric(20,4), -- tantieme du lot
+  CONSTRAINT appel_fond_detail_pkey PRIMARY KEY (afd_id )
+);
+
+COMMENT ON TABLE coprop.appel_fond_detail  IS 'detail af';
+COMMENT ON COLUMN coprop.appel_fond_detail.af_id IS 'appel de fond';
+COMMENT ON COLUMN coprop.appel_fond_detail.key_tantieme IS 'tantième de la 
clef';
+COMMENT ON COLUMN coprop.appel_fond_detail.lot_tantieme IS 'tantieme du lot';
+
+
+CREATE TABLE coprop.version
+(
+  v_id bigint NOT NULL,
+  v_note text,
+  v_date date DEFAULT now(),
+  CONSTRAINT version_pkey PRIMARY KEY (v_id )
+);
+
+insert into coprop.version (v_id,v_note) values (1,'Installation');
+
+commit;
\ No newline at end of file



---
PhpCompta est un logiciel de comptabilité libre en ligne (full web)
Projet opensource http://www.phpcompta.eu