Plugins  LAST
 All Data Structures Files Functions Variables Pages
class_install_plugin.php
Go to the documentation of this file.
1 <?php
2  /*
3  * This file is part of NOALYSS.
4  *
5  * NOALYSS is free software; you can redistribute it and/or modify
6  * it under the terms of the GNU General Public License as published by
7  * the Free Software Foundation; either version 2 of the License, or
8  * (at your option) any later version.
9  *
10  * NOALYSS is distributed in the hope that it will be useful,
11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13  * GNU General Public License for more details.
14  *
15  * You should have received a copy of the GNU General Public License
16  * along with NOALYSS; if not, write to the Free Software
17  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18  */
19  /* $Revision$ */
20 
21  // Copyright (c) 2002 Author Dany De Bontridder dany@alchimerys.be
22 
23  /*!\file
24  * \brief this class manages the installation and the patch of the plugin
25  */
26 
28 {
29 
30  function __construct(& $p_cn) {
31  $this->cn=$p_cn;
32  }
33 
34  /**
35  *@brief install the plugin, create all the needed schema, tables, proc
36  * in the database
37  *@param $p_dossier is the dossier id
38  */
39  function install()
40  {
41  $this->cn->start();
42  // create the schema
43  $this->create_schema();
44  $this->create_tables();
45 
46  $this->cn->commit();
47  }
48  function create_schema()
49  {
50  $this->cn->exec_sql('create schema amortissement');
51  }
52  function create_tables()
53  {
54  $this->cn->start();
55  $table="CREATE TABLE amortissement.amortissement
56 (
57  a_id serial NOT NULL,
58  f_id bigint NOT NULL,
59  account_deb account_type,
60  account_cred account_type,
61  a_amount numeric(20,2) NOT NULL DEFAULT 0,
62  a_nb_year numeric(4,2) NOT NULL DEFAULT 0,
63  a_start integer,
64  a_date date,
65  a_visible character(1) DEFAULT 'Y'::bpchar,
66  CONSTRAINT amortissement_pkey PRIMARY KEY (a_id),
67  CONSTRAINT amortissement_account_cred_fkey FOREIGN KEY (account_cred)
68  REFERENCES tmp_pcmn (pcm_val) MATCH SIMPLE
69  ON UPDATE NO ACTION ON DELETE NO ACTION,
70  CONSTRAINT amortissement_account_deb_fkey FOREIGN KEY (account_deb)
71  REFERENCES tmp_pcmn (pcm_val) MATCH SIMPLE
72  ON UPDATE NO ACTION ON DELETE NO ACTION,
73  CONSTRAINT amortissement_f_id_fkey FOREIGN KEY (f_id)
74  REFERENCES fiche (f_id) MATCH SIMPLE
75  ON UPDATE NO ACTION ON DELETE NO ACTION,
76  CONSTRAINT amortissement_f_id_key UNIQUE (f_id)
77 )";
78 
79  $this->cn->exec_sql($table);
80 
81  $table="CREATE TABLE amortissement.version
82 (
83  val integer primary key
84 )";
85 
86  $this->cn->exec_sql($table);
87 
88  $table="CREATE TABLE amortissement.amortissement_detail
89 (
90  ad_id serial NOT NULL,
91  ad_amount numeric(20,2) NOT NULL DEFAULT 0,
92  a_id bigint,
93  ad_year integer,
94  ad_percentage numeric(5,2),
95  CONSTRAINT amortissement_detail_pkey PRIMARY KEY (ad_id),
96  CONSTRAINT amortissement_detail_a_id_fkey FOREIGN KEY (a_id)
97  REFERENCES amortissement.amortissement (a_id) MATCH SIMPLE
98  ON UPDATE CASCADE ON DELETE CASCADE
99 )";
100 
101  $idx="CREATE INDEX fki_amortissement
102  ON amortissement.amortissement_detail
103  USING btree
104  (a_id);";
105 
106  $this->cn->exec_sql($table);
107 
108  $table="
109 CREATE TABLE amortissement.amortissement_histo
110 (
111  ha_id serial NOT NULL,
112  a_id bigint,
113  h_amount numeric(20,4) NOT NULL,
114  jr_internal text,
115  h_year integer NOT NULL,
116  h_pj text,
117  CONSTRAINT amortissement_histo_pkey PRIMARY KEY (ha_id),
118  CONSTRAINT amortissement_histo_a_id_fkey FOREIGN KEY (a_id)
119  REFERENCES amortissement.amortissement (a_id) MATCH SIMPLE
120  ON UPDATE CASCADE ON DELETE CASCADE
121 );";
122 
123  $idx="CREATE UNIQUE INDEX amortissement_histo_uq
124  ON amortissement.amortissement_histo
125  USING btree
126  (h_year, a_id);
127 
128 ";
129  $this->cn->exec_sql($table);
130 
131  $fct="CREATE OR REPLACE FUNCTION amortissement.amortissement_ins()
132  RETURNS trigger AS
133 \$BODY\$
134 declare
135 i int;
136 nyear int;
137 n_ad_amount numeric(20,2);
138 total numeric(20,2);
139 last_ad_id bigint;
140 begin
141  i :=0;
142  total := 0;
143  loop
144 
145  if i = NEW.a_nb_year then
146  exit ;
147  end if;
148  nyear := NEW.a_start +i;
149  n_ad_amount := NEW.a_amount/NEW.a_nb_year;
150 
151  total := total + n_ad_amount;
152 
153  if total > NEW.a_amount then
154  n_ad_amount := n_ad_amount - total + NEW.a_amount ;
155  end if;
156 
157  insert into amortissement.amortissement_detail(ad_year,ad_amount,a_id,ad_percentage) values (nyear,n_ad_amount,NEW.a_id,1/NEW.a_nb_year) returning ad_id into last_ad_id;
158  insert into amortissement.amortissement_histo(a_id,h_amount,h_year) values (NEW.a_id,0,nyear);
159  i := i+1;
160  end loop;
161  if total < NEW.a_amount then
162  n_ad_amount := n_ad_amount+NEW.a_amount-total;
163  update amortissement.amortissement_detail set ad_amount=n_ad_amount where ad_id=last_ad_id;
164  end if;
165  return NEW;
166 end;
167 
168 
169 \$BODY\$
170  LANGUAGE 'plpgsql'";
171 
172  $this->cn->exec_sql($fct);
173 
174  $comment_fct="COMMENT ON FUNCTION amortissement.amortissement_ins() IS 'Fill the table amortissement_detail after an insert'";
175 
176  $this->cn->exec_sql($comment_fct);
177 
178  $fct="CREATE OR REPLACE FUNCTION amortissement.amortissement_up()
179  RETURNS trigger AS
180 \$BODY\$
181 
182 declare
183 i int;
184 nyear int;
185 n_ad_amount numeric(20,2);
186 total numeric(20,2);
187 last_ad_id bigint;
188 n_pct numeric(5,2);
189 lha_id bigint;
190 begin
191  i :=0;
192  if NEW.a_nb_year != OLD.a_nb_year or NEW.a_start != OLD.a_start or NEW.a_amount != OLD.a_amount then
193  delete from amortissement.amortissement_detail where a_id=NEW.a_id;
194  delete from amortissement.amortissement_histo where a_id=NEW.a_id and
195  (h_year < NEW.a_start or h_year > NEW.a_start+NEW.a_nb_year-1);
196 
197  n_ad_amount := round(NEW.a_amount/NEW.a_nb_year,2);
198  n_pct := round(n_ad_amount / NEW.a_amount ,2);
199  loop
200 
201  if i = NEW.a_nb_year then
202  exit ;
203  end if;
204  nyear := NEW.a_start +i;
205 
206  select ha_id into lha_id from amortissement.amortissement_histo where a_id=NEW.a_id and h_year = nyear;
207 
208  if NOT FOUND then
209  insert into amortissement.amortissement_histo(a_id,h_year,h_amount) values (NEW.a_id,nyear,0);
210  end if;
211 
212  total := round(total + n_ad_amount,2);
213 
214  if total > NEW.a_amount then
215  n_ad_amount := NEW.a_amount - total - n_ad_amount;
216  end if;
217  insert into amortissement.amortissement_detail(ad_year,ad_amount,ad_percentage,a_id) values (nyear,n_ad_amount,1/NEW.a_nb_year,NEW.a_id) returning ad_id into last_ad_id;
218  i := i+1;
219  end loop;
220  if total < NEW.a_amount then
221  n_ad_amount := n_ad_amount+NEW.a_amount-total;
222  update amortissement.amortissement_detail set ad_amount=n_ad_amount where ad_id=last_ad_id;
223  end if;
224  end if;
225  return NEW;
226 end;
227 
228 \$BODY\$
229  LANGUAGE 'plpgsql' ";
230 
231  $this->cn->exec_sql($fct);
232 
233 
234  $trigger="CREATE TRIGGER amortissement_after_ins
235  AFTER INSERT
236  ON amortissement.amortissement
237  FOR EACH ROW
238  EXECUTE PROCEDURE amortissement.amortissement_ins();";
239 
240  $this->cn->exec_sql($trigger);
241 
242  $trigger="CREATE TRIGGER amortissement_after_up
243  AFTER UPDATE
244  ON amortissement.amortissement
245  FOR EACH ROW
246  EXECUTE PROCEDURE amortissement.amortissement_up();";
247 
248  $this->cn->exec_sql($trigger);
249  $this->cn->exec_sql("insert into amortissement.version values (1)");
250  $this->cn->commit();
251  }
252  /**
253  * Upgrade schema amortissement
254  * @param type $p_dest
255  */
256  function upgrade($p_dest)
257  {
258  $this->cn->start();
259  try {
260  if ( $p_dest == 2 ) {
261 
262  $add_column = "alter table amortissement.amortissement add card_deb bigint";
263  $add_fk="alter table amortissement.amortissement add constraint card_deb_fk foreign key (card_deb) references public.fiche(f_id) on update cascade on delete set null";
264  $add_comment =" comment on column amortissement.amortissement.card_deb is 'Charge d''amortissement' ";
265  $this->cn->exec_sql($add_column);
266  $this->cn->exec_sql($add_fk);
267  $this->cn->exec_sql($add_comment);
268 
269  $add_column = "alter table amortissement.amortissement add card_cred bigint";
270  $add_fk="alter table amortissement.amortissement add constraint card_cred_fk foreign key (card_cred) references public.fiche(f_id) on update cascade on delete set null";
271  $add_comment =" comment on column amortissement.amortissement.card_cred is 'Poste amorti' ";
272 
273  $this->cn->exec_sql($add_column);
274  $this->cn->exec_sql($add_fk);
275  $this->cn->exec_sql($add_comment);
276  $sql_create_view="
277  create or replace view amortissement.v_amortissement_summary
278 as
279  select f_id,vw_name,vw_description,quick_code,
280  a_id,account_deb,account_cred,a_amount,a_nb_year,a_start,a_date,a_visible,
281  card_cred,(select j_qcode from vw_poste_qcode where f_id=card_cred) as card_cred_qcode,
282  card_deb,(select j_qcode from vw_poste_qcode where f_id=card_deb) as card_deb_qcode,
283  ( select coalesce(sum(h_amount),0) from amortissement.amortissement_histo where amortissement_histo.a_id=amortissement.a_id) as amort_done
284  from amortissement.amortissement
285  join public.vw_fiche_attr using (f_id)
286  ";
287  $this->cn->exec_sql($sql_create_view);
288  $add_comment=" comment on view amortissement.v_amortissement_summary is 'View of material with card'";
289  $this->cn->exec_sql($add_comment);
290  $this->cn->exec_sql(' insert into amortissement.version values(2)');
291  $this->cn->commit();
292  }
293  if ($p_dest == 3 )
294  {
295  $file=dirname(__FILE__);
296  $this->cn->execute_script($file.'/../sql/upgrade2.sql');
297  }
298  } catch (Exception $e) {
299  $this->cn->rollback;
300  }
301  }
302 }
while(($row=fgetcsv($fbank, 0, $sp))!==false) $table
install()
install the plugin, create all the needed schema, tables, proc in the database
upgrade($p_dest)
Upgrade schema amortissement.
$file
Definition: import_bank.php:60