50 $this->cn->exec_sql(
'create schema amortissement');
55 $table=
"CREATE TABLE amortissement.amortissement
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,
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)
79 $this->cn->exec_sql(
$table);
81 $table=
"CREATE TABLE amortissement.version
83 val integer primary key
86 $this->cn->exec_sql(
$table);
88 $table=
"CREATE TABLE amortissement.amortissement_detail
90 ad_id serial NOT NULL,
91 ad_amount numeric(20,2) NOT NULL DEFAULT 0,
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
101 $idx=
"CREATE INDEX fki_amortissement
102 ON amortissement.amortissement_detail
106 $this->cn->exec_sql(
$table);
109 CREATE TABLE amortissement.amortissement_histo
111 ha_id serial NOT NULL,
113 h_amount numeric(20,4) NOT NULL,
115 h_year integer NOT NULL,
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
123 $idx=
"CREATE UNIQUE INDEX amortissement_histo_uq
124 ON amortissement.amortissement_histo
129 $this->cn->exec_sql(
$table);
131 $fct=
"CREATE OR REPLACE FUNCTION amortissement.amortissement_ins()
137 n_ad_amount numeric(20,2);
145 if i = NEW.a_nb_year then
148 nyear := NEW.a_start +i;
149 n_ad_amount := NEW.a_amount/NEW.a_nb_year;
151 total := total + n_ad_amount;
153 if total > NEW.a_amount then
154 n_ad_amount := n_ad_amount - total + NEW.a_amount ;
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);
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;
172 $this->cn->exec_sql($fct);
174 $comment_fct=
"COMMENT ON FUNCTION amortissement.amortissement_ins() IS 'Fill the table amortissement_detail after an insert'";
176 $this->cn->exec_sql($comment_fct);
178 $fct=
"CREATE OR REPLACE FUNCTION amortissement.amortissement_up()
185 n_ad_amount numeric(20,2);
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);
197 n_ad_amount := round(NEW.a_amount/NEW.a_nb_year,2);
198 n_pct := round(n_ad_amount / NEW.a_amount ,2);
201 if i = NEW.a_nb_year then
204 nyear := NEW.a_start +i;
206 select ha_id into lha_id from amortissement.amortissement_histo where a_id=NEW.a_id and h_year = nyear;
209 insert into amortissement.amortissement_histo(a_id,h_year,h_amount) values (NEW.a_id,nyear,0);
212 total := round(total + n_ad_amount,2);
214 if total > NEW.a_amount then
215 n_ad_amount := NEW.a_amount - total - n_ad_amount;
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;
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;
229 LANGUAGE 'plpgsql' ";
231 $this->cn->exec_sql($fct);
234 $trigger=
"CREATE TRIGGER amortissement_after_ins
236 ON amortissement.amortissement
238 EXECUTE PROCEDURE amortissement.amortissement_ins();";
240 $this->cn->exec_sql($trigger);
242 $trigger=
"CREATE TRIGGER amortissement_after_up
244 ON amortissement.amortissement
246 EXECUTE PROCEDURE amortissement.amortissement_up();";
248 $this->cn->exec_sql($trigger);
249 $this->cn->exec_sql(
"insert into amortissement.version values (1)");
260 if ( $p_dest == 2 ) {
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);
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' ";
273 $this->cn->exec_sql($add_column);
274 $this->cn->exec_sql($add_fk);
275 $this->cn->exec_sql($add_comment);
277 create or replace view amortissement.v_amortissement_summary
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)
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)');
295 $file=dirname(__FILE__);
296 $this->cn->execute_script(
$file.
'/../sql/upgrade2.sql');
298 }
catch (Exception $e) {
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.