[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r4447 - phpcompta/trunk/sql
|
From: |
phpcompta-dev |
|
Subject: |
[Phpcompta-dev] r4447 - phpcompta/trunk/sql |
|
Date: |
Tue, 22 Nov 2011 23:50:45 +0100 (CET) |
Author: danydb
Date: 2011-11-22 23:50:44 +0100 (Tue, 22 Nov 2011)
New Revision: 4447
Removed:
phpcompta/trunk/sql/change-pcmn-to-alphanum.sql
phpcompta/trunk/sql/comptaproc.sql
phpcompta/trunk/sql/correct-quant.sql
phpcompta/trunk/sql/correct_quant_sold.sql
phpcompta/trunk/sql/downgrade.sql
Modified:
phpcompta/trunk/sql/upgrade.sql
Log:
Housekeeping drop obsolete files
Deleted: phpcompta/trunk/sql/change-pcmn-to-alphanum.sql
===================================================================
--- phpcompta/trunk/sql/change-pcmn-to-alphanum.sql 2011-11-22 22:44:52 UTC
(rev 4446)
+++ phpcompta/trunk/sql/change-pcmn-to-alphanum.sql 2011-11-22 22:50:44 UTC
(rev 4447)
@@ -1,1554 +0,0 @@
-begin;
-
-DROP FUNCTION account_add(p_id poste_comptable, p_name character varying)
cascade;
-DROP FUNCTION account_auto(p_fd_id integer) cascade;
-DROP FUNCTION account_compute(p_f_id integer) cascade;
-DROP FUNCTION account_insert(p_f_id integer, p_account text) cascade;
-DROP FUNCTION account_parent(p_account poste_comptable) cascade;
-DROP FUNCTION account_update(p_f_id integer, p_account text) cascade;
-DROP FUNCTION action_gestion_ins_upd() cascade;
-DROP FUNCTION attribut_insert(p_f_id integer, p_ad_id integer, p_value
character varying) cascade;
-DROP FUNCTION attribute_correct_order() cascade;
-DROP FUNCTION bud_card_ins_upd() cascade;
-DROP FUNCTION bud_detail_ins_upd() cascade;
-DROP FUNCTION card_class_base(p_f_id integer) cascade;
-DROP FUNCTION check_balance(p_grpt integer) cascade;
-DROP FUNCTION correct_sequence(p_sequence text, p_col text, p_table text)
cascade;
-DROP FUNCTION create_missing_sequence() cascade;
-DROP FUNCTION drop_index(p_constraint character varying) cascade;
-DROP FUNCTION drop_it(p_constraint character varying) cascade;
-DROP FUNCTION extension_ins_upd() cascade;
-DROP FUNCTION fiche_account_parent(p_f_id integer) cascade;
-DROP FUNCTION fiche_attribut_synchro(p_fd_id integer) cascade;
-DROP FUNCTION find_pcm_type(pp_value numeric) cascade;
-DROP FUNCTION group_analytic_ins_upd() cascade;
-DROP FUNCTION group_analytique_del() cascade;
-DROP FUNCTION html_quote(p_string text) cascade;
-DROP FUNCTION info_def_ins_upd() cascade;
-DROP FUNCTION insert_jrnx (p_date character varying, p_montant numeric,
p_poste poste_comptable, p_grpt integer, p_jrn_def integer, p_debit boolean,
p_tech_user text, p_tech_per integer, p_qcode text, p_comment text) cascade;
-
-DROP FUNCTION insert_quant_purchase(p_internal text, p_j_id numeric, p_fiche
character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code
integer, p_nd_amount numeric, p_nd_tva numeric, p_nd_tva_recup numeric,
p_dep_priv numeric, p_client character varying) cascade;
-DROP FUNCTION insert_quant_sold(p_internal text, p_jid numeric, p_fiche
character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code
integer, p_client character varying) cascade;
-DROP FUNCTION insert_quick_code(nf_id integer, tav_text text) cascade;
-DROP FUNCTION jrn_check_periode() cascade;
-DROP FUNCTION jrn_def_add() cascade;
-DROP FUNCTION jrn_def_delete() cascade;
-DROP FUNCTION jrn_del() cascade;
-DROP FUNCTION jrnx_del() cascade;
-DROP FUNCTION plan_analytic_ins_upd() cascade;
-DROP FUNCTION poste_analytique_ins_upd() cascade;
-DROP FUNCTION proc_check_balance() cascade;
-DROP FUNCTION t_document_modele_validate() cascade;
-DROP FUNCTION t_document_type_insert() cascade;
-DROP FUNCTION t_document_validate() cascade;
-DROP FUNCTION t_jrn_def_sequence() cascade;
-DROP FUNCTION tmp_pcmn_ins() cascade;
-DROP FUNCTION trim_cvs_quote() cascade;
-DROP FUNCTION trim_space_format_csv_banque() cascade;
-DROP FUNCTION tva_delete(integer) cascade;
-DROP FUNCTION tva_insert(text, numeric, text, text) cascade;
-DROP FUNCTION tva_modify(integer, text, numeric, text, text) cascade;
-DROP FUNCTION update_quick_code(njft_id integer, tav_text text) cascade;
-
-
-DROP VIEW vw_client ;
-DROP VIEW vw_fiche_attr ;
-DROP VIEW vw_fiche_def ;
-DROP VIEW vw_fiche_min ;
-DROP VIEW vw_poste_qcode ;
-DROP VIEW vw_supplier ;
-
-create domain account_type varchar(30);
-alter table centralized alter c_poste type account_type;
-alter table del_jrnx alter j_poste type account_type;
-alter table fiche_def alter fd_class_base type account_type;
-alter table jrnx alter j_poste type account_type;
-alter table parm_poste alter p_value type account_type;
-alter table tmp_pcmn alter pcm_val type account_type;
-alter table tmp_pcmn alter pcm_val_parent type account_type;
-
-CREATE VIEW vw_client AS
- SELECT a.f_id, a.av_text AS name, a1.av_text AS quick_code, b.av_text AS
tva_num, c.av_text AS poste_comptable, d.av_text AS rue, e.av_text AS
code_postal, f.av_text AS pays, g.av_text AS telephone, h.av_text AS email FROM
(((((((((SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 1)) a JOIN (SELECT jnt_fic_att_value.jft_id,
fiche.f_id, fiche_def.frd_id, fiche.fd_id, fiche_def.fd_class_base,
fiche_def.fd_label, fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 13)) b USING (f_id)) JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 23)) a1 USING
(f_id)) JOIN (SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 5)) c USING (f_id)) JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 14)) d USING
(f_id)) JOIN (SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 15)) e USING (f_id)) JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 16)) f USING
(f_id)) JOIN (SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 17)) g USING (f_id)) LEFT JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 18)) h USING
(f_id)) WHERE (a.frd_id = 9);
-
-CREATE OR REPLACE VIEW vw_fiche_attr AS
- SELECT a.f_id, a.fd_id, a.av_text AS vw_name, b.av_text AS vw_sell, c.av_text
AS vw_buy, d.av_text AS tva_code, tva_rate.tva_id, tva_rate.tva_rate,
tva_rate.tva_label, e.av_text AS vw_addr, f.av_text AS vw_cp, j.av_text AS
quick_code, h.av_text AS vw_description, i.av_text AS tva_num, fiche_def.frd_id
- FROM ( SELECT fiche.f_id, fiche.fd_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 1) a
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 6) b ON a.f_id = b.f_id
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 7) c ON a.f_id = c.f_id
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 2) d ON a.f_id = d.f_id
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 14) e ON a.f_id = e.f_id
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 15) f ON a.f_id = f.f_id
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 23) j ON a.f_id = j.f_id
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 9) h ON a.f_id = h.f_id
- LEFT JOIN ( SELECT fiche.f_id, attr_value.av_text
- FROM fiche
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- JOIN attr_def USING (ad_id)
- WHERE jnt_fic_att_value.ad_id = 13) i ON a.f_id = i.f_id
- LEFT JOIN tva_rate ON d.av_text = tva_rate.tva_id::text
- JOIN fiche_def USING (fd_id);
-
-
-
-CREATE VIEW vw_fiche_def AS
- SELECT jnt_fic_attr.fd_id, jnt_fic_attr.ad_id, attr_def.ad_text,
attr_value.av_text, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def.frd_id FROM (((((jnt_fic_att_value JOIN
attr_value USING (jft_id)) JOIN fiche USING (f_id)) JOIN jnt_fic_attr USING
(fd_id)) JOIN attr_def ON ((attr_def.ad_id = jnt_fic_attr.ad_id))) JOIN
fiche_def USING (fd_id));
-CREATE VIEW vw_fiche_min AS
- SELECT attr_min.frd_id, attr_min.ad_id, attr_def.ad_text,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base FROM ((attr_min JOIN
attr_def USING (ad_id)) JOIN fiche_def_ref USING (frd_id));
-CREATE VIEW vw_poste_qcode AS
- SELECT a.f_id, a.av_text AS j_poste, b.av_text AS j_qcode FROM ((SELECT
jnt_fic_att_value.f_id, attr_value.av_text FROM (attr_value JOIN
jnt_fic_att_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 5)) a JOIN
(SELECT jnt_fic_att_value.f_id, attr_value.av_text FROM (attr_value JOIN
jnt_fic_att_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 23)) b USING
(f_id));
-CREATE VIEW vw_supplier AS
- SELECT a.f_id, a.av_text AS name, a1.av_text AS quick_code, b.av_text AS
tva_num, c.av_text AS poste_comptable, d.av_text AS rue, e.av_text AS
code_postal, f.av_text AS pays, g.av_text AS telephone, h.av_text AS email FROM
(((((((((SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 1)) a JOIN (SELECT jnt_fic_att_value.jft_id,
fiche.f_id, fiche_def.frd_id, fiche.fd_id, fiche_def.fd_class_base,
fiche_def.fd_label, fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 13)) b USING (f_id)) JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 23)) a1 USING
(f_id)) JOIN (SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 5)) c USING (f_id)) JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 14)) d USING
(f_id)) JOIN (SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 15)) e USING (f_id)) JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 16)) f USING
(f_id)) JOIN (SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text FROM
((((fiche JOIN fiche_def USING (fd_id)) JOIN fiche_def_ref USING (frd_id)) JOIN
jnt_fic_att_value USING (f_id)) JOIN attr_value USING (jft_id)) WHERE
(jnt_fic_att_value.ad_id = 17)) g USING (f_id)) LEFT JOIN (SELECT
jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id, fiche.fd_id,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def_ref.frd_text, fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id,
attr_value.av_text FROM ((((fiche JOIN fiche_def USING (fd_id)) JOIN
fiche_def_ref USING (frd_id)) JOIN jnt_fic_att_value USING (f_id)) JOIN
attr_value USING (jft_id)) WHERE (jnt_fic_att_value.ad_id = 18)) h USING
(f_id)) WHERE (a.frd_id = 8);
-
-CREATE FUNCTION account_auto(p_fd_id integer) RETURNS boolean
- AS $$
-declare
- l_auto bool;
-begin
-
- select fd_create_account into l_auto from fiche_def where fd_id=p_fd_id;
- if l_auto is null then
- l_auto:=false;
- end if;
- return l_auto;
-end;
-$$
- LANGUAGE plpgsql;
-
-
-
-
-CREATE FUNCTION account_add(p_id account_type, p_name character varying)
RETURNS void
- AS $$
-declare
- nParent tmp_pcmn.pcm_val_parent%type;
- nCount integer;
-begin
- select count(*) into nCount from tmp_pcmn where pcm_val=p_id;
- if nCount = 0 then
- nParent=account_parent(p_id);
- insert into tmp_pcmn (pcm_val,pcm_lib,pcm_val_parent)
- values (p_id, p_name,nParent);
- end if;
-return;
-end ;
-$$
- LANGUAGE plpgsql;
-
-
-
-CREATE FUNCTION action_gestion_ins_upd() RETURNS "trigger"
- AS $$
-begin
-NEW.ag_title := substr(trim(NEW.ag_title),1,70);
-NEW.ag_hour := substr(trim(NEW.ag_hour),1,5);
-return NEW;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION attribut_insert(p_f_id integer, p_ad_id integer, p_value
character varying) RETURNS void
- AS $$
-declare
- n_jft_id integer;
-begin
- select nextval('s_jnt_fic_att_value') into n_jft_id;
- insert into jnt_fic_att_value (jft_id,f_id,ad_id) values
(n_jft_id,p_f_id,p_ad_id);
- insert into attr_value (jft_id,av_text) values
(n_jft_id,trim(p_value));
-return;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION attribute_correct_order() RETURNS void
- AS $$
-declare
- crs_correct cursor for select A.jnt_id,A.jnt_order from jnt_fic_attr as A
join jnt_fic_attr as B using (fd_id) where A.jnt_order=B.jnt_order and A.jnt_id
> B.jnt_id;
- rec record;
-begin
- open crs_correct;
- loop
- fetch crs_correct into rec;
- if NOT FOUND then
- close crs_correct;
- return;
- end if;
- update jnt_fic_attr set jnt_order=jnt_order + 1 where jnt_id =
rec.jnt_id;
- end loop;
- close crs_correct;
- perform attribute_correct_order ();
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION bud_card_ins_upd() RETURNS "trigger"
- AS $$declare
- sCode text;
-begin
-
-sCode:=trim(upper(NEW.bc_code));
-sCode:=replace(sCode,' ','_');
-sCode:=substr(sCode,1,10);
-NEW.bc_code:=sCode;
-return NEW;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION card_class_base(p_f_id integer) RETURNS account_type
- AS $$
-declare
- n_poste fiche_def.fd_class_base%type;
-begin
-
- select fd_class_base into n_poste from fiche_def join fiche using
-(fd_id)
- where f_id=p_f_id;
- if not FOUND then
- raise exception 'Invalid fiche card_class_base(%)',p_f_id;
- end if;
-return n_poste;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION check_balance(p_grpt integer) RETURNS numeric
- AS $$
-declare
- amount_jrnx_debit numeric;
- amount_jrnx_credit numeric;
- amount_jrn numeric;
-begin
- select sum (j_montant) into amount_jrnx_credit
- from jrnx
- where
- j_grpt=p_grpt
- and j_debit=false;
-
- select sum (j_montant) into amount_jrnx_debit
- from jrnx
- where
- j_grpt=p_grpt
- and j_debit=true;
-
- select jr_montant into amount_jrn
- from jrn
- where
- jr_grpt_id=p_grpt;
-
- if ( amount_jrnx_debit != amount_jrnx_credit )
- then
- return abs(amount_jrnx_debit-amount_jrnx_credit);
- end if;
- if ( amount_jrn != amount_jrnx_credit)
- then
- return -1*abs(amount_jrn - amount_jrnx_credit);
- end if;
- return 0;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION correct_sequence(p_sequence text, p_col text, p_table text)
RETURNS integer
- AS $$
-declare
-last_sequence int8;
-max_sequence int8;
-n integer;
-begin
- select count(*) into n from pg_class where relkind='S' and
relname=lower(p_sequence);
- if n = 0 then
- raise exception ' Unknow sequence % ',p_sequence;
- end if;
- select count(*) into n from pg_class where relkind='r' and
relname=lower(p_table);
- if n = 0 then
- raise exception ' Unknow table % ',p_table;
- end if;
-
- execute 'select last_value from '||p_sequence into last_sequence;
- raise notice 'Last value of the sequence is %', last_sequence;
-
- execute 'select max('||p_col||') from '||p_table into max_sequence;
- if max_sequence is null then
- max_sequence := 0;
- end if;
- raise notice 'Max value of the sequence is %', max_sequence;
- max_sequence:= max_sequence +1;
- execute 'alter sequence '||p_sequence||' restart with '||max_sequence;
-return 0;
-
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION create_missing_sequence() RETURNS integer
- AS $$
-declare
-p_sequence text;
-nSeq integer;
-c1 cursor for select jrn_def_id from jrn_def;
-begin
- open c1;
- loop
- fetch c1 into nSeq;
- if not FOUND THEN
- close c1;
- return 0;
- end if;
- p_sequence:='s_jrn_pj'||nSeq::text;
- execute 'create sequence '||p_sequence;
- end loop;
-close c1;
-return 0;
-
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION drop_index(p_constraint character varying) RETURNS void
- AS $$
-declare
- nCount integer;
-begin
- select count(*) into nCount from pg_indexes where
indexname=p_constraint;
- if nCount = 1 then
- execute 'drop index '||p_constraint ;
- end if;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION drop_it(p_constraint character varying) RETURNS void
- AS $$
-declare
- nCount integer;
-begin
- select count(*) into nCount from pg_constraint where
conname=p_constraint;
- if nCount = 1 then
- execute 'alter table parm_periode drop constraint '||p_constraint ;
- end if;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION extension_ins_upd() RETURNS "trigger"
- AS $$
-declare
- sCode text;
- sFile text;
-begin
-sCode:=trim(upper(NEW.ex_code));
-sCode:=replace(sCode,' ','_');
-sCode:=substr(sCode,1,15);
-sCode=upper(sCode);
-NEW.ex_code:=sCode;
-sFile:=NEW.ex_file;
-sFile:=replace(sFile,';','_');
-sFile:=replace(sFile,'<','_');
-sFile:=replace(sFile,'>','_');
-sFile:=replace(sFile,'..','');
-sFile:=replace(sFile,'&','');
-sFile:=replace(sFile,'|','');
-
-
-
-return NEW;
-
-end;
-
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION fiche_account_parent(p_f_id integer) RETURNS account_type
- AS $$
-declare
-ret account_type;
-begin
- select fd_class_base into ret from fiche_def join fiche using (fd_id)
where f_id=p_f_id;
- if not FOUND then
- raise exception '% N''existe pas',p_f_id;
- end if;
- return ret;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION fiche_attribut_synchro(p_fd_id integer) RETURNS void
- AS $$
-declare
- -- this sql gives the f_id and the missing attribute (ad_id)
- list_missing cursor for select f_id,fd_id,ad_id,jnt_order from
jnt_fic_attr join fiche as A using (fd_id) where fd_id=p_fd_id and ad_id not in
(select ad_id from fiche join jnt_fic_att_value using (f_id) where
fd_id=jnt_fic_attr.fd_id and A.f_id=f_id);
- rec record;
- -- value of the last insert
- jnt jnt_fic_att_value%ROWTYPE;
-begin
- open list_missing;
- loop
-
- fetch list_missing into rec;
- IF NOT FOUND then
- exit;
- end if;
- -- insert a value into jnt_fic_att_value
- insert into jnt_fic_att_value (f_id,ad_id) values (rec.f_id,rec.ad_id)
returning * into jnt;
-
- -- now we insert into attr_value
- insert into attr_value values (jnt.jft_id,'');
- end loop;
- close list_missing;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION find_pcm_type(pp_value numeric) RETURNS text
- AS $$
-declare
- str_type text;
- str_value text;
- n_value numeric;
- nLength integer;
-begin
- str_value:=trim(to_char(pp_value,'99999999999999999999999999999'));
- nLength:=length(str_value);
- while nLength > 0 loop
- n_value:=to_number(str_value,'99999999999999999999999999999');
- select p_type into str_type from parm_poste where
p_value=n_value;
- if FOUND then
- return str_type;
- end if;
- nLength:=nLength-1;
- str_value:=substring(str_value from 1 for nLength);
- end loop;
-return 'CON';
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION group_analytic_ins_upd() RETURNS "trigger"
- AS $$
-declare
-name text;
-begin
-name:=upper(NEW.ga_id);
-name:=trim(name);
-name:=replace(name,' ','');
-NEW.ga_id:=name;
-return NEW;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION group_analytique_del() RETURNS "trigger"
- AS $$
-begin
-update poste_analytique set ga_id=null
-where ga_id=OLD.ga_id;
-return OLD;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION html_quote(p_string text) RETURNS text
- AS $$
-declare
- r text;
-begin
- r:=p_string;
- r:=replace(r,'<','<');
- r:=replace(r,'>','>');
- r:=replace(r,'''','"');
- return r;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION info_def_ins_upd() RETURNS "trigger"
- AS $$
-declare
- row_info_def info_def%ROWTYPE;
- str_type text;
-begin
-row_info_def:=NEW;
-str_type:=upper(trim(NEW.id_type));
-str_type:=replace(str_type,' ','');
-str_type:=replace(str_type,',','');
-str_type:=replace(str_type,';','');
-if length(str_type) =0 then
- raise exception 'id_type cannot be null';
-end if;
-row_info_def.id_type:=str_type;
-return row_info_def;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION insert_jrnx(p_date character varying, p_montant
numeric, p_poste account_type, p_grpt integer, p_jrn_def integer, p_debit
boolean, p_tech_user text, p_tech_per integer, p_qcode text,p_comment text)
- RETURNS void AS
-$BODY$
-declare
- sCode varchar;
- nCount_qcode integer;
-begin
- sCode=trim(p_qcode);
-
- -- if p_qcode is empty try to find one
- if length(sCode) = 0 or p_qcode is null then
- select count(*) into nCount_qcode
- from vw_poste_qcode where j_poste=p_poste::text;
- -- if we find only one q_code for a accountancy account
- -- then retrieve it
- if nCount_qcode = 1 then
- select j_qcode::text into sCode
- from vw_poste_qcode where j_poste=p_poste::text;
- else
- sCode=NULL;
- end if;
-
- end if;
-
- insert into jrnx
- (
- j_date,
- j_montant,
- j_poste,
- j_grpt,
- j_jrn_def,
- j_debit,
- j_text,
- j_tech_user,
- j_tech_per,
- j_qcode
- ) values
- (
- to_date(p_date,'DD.MM.YYYY'),
- p_montant,
- p_poste,
- p_grpt,
- p_jrn_def,
- p_debit,
- p_comment,
- p_tech_user,
- p_tech_per,
- sCode
- );
-
-return;
-end;
-$BODY$
-LANGUAGE plpgsql;
-CREATE FUNCTION insert_quant_purchase(p_internal text, p_j_id numeric, p_fiche
character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code
integer, p_nd_amount numeric, p_nd_tva numeric, p_nd_tva_recup numeric,
p_dep_priv numeric, p_client character varying) RETURNS void
- AS $$
-declare
- fid_client integer;
- fid_good integer;
-begin
- select f_id into fid_client from
- attr_value join jnt_fic_att_value using (jft_id) where ad_id=23
and av_text=upper(trim(p_client));
- select f_id into fid_good from
- attr_value join jnt_fic_att_value using (jft_id) where ad_id=23
and av_text=upper(trim(p_fiche));
- insert into quant_purchase
- (qp_internal,
- j_id,
- qp_fiche,
- qp_quantite,
- qp_price,
- qp_vat,
- qp_vat_code,
- qp_nd_amount,
- qp_nd_tva,
- qp_nd_tva_recup,
- qp_supplier,
- qp_dep_priv)
- values
- (p_internal,
- p_j_id,
- fid_good,
- p_quant,
- p_price,
- p_vat,
- p_vat_code,
- p_nd_amount,
- p_nd_tva,
- p_nd_tva_recup,
- fid_client,
- p_dep_priv);
- return;
-end;
- $$
- LANGUAGE plpgsql;
-CREATE FUNCTION insert_quant_sold(p_internal text, p_jid numeric, p_fiche
character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code
integer, p_client character varying) RETURNS void
- AS $$
-declare
- fid_client integer;
- fid_good integer;
-begin
-
- select f_id into fid_client from
- attr_value join jnt_fic_att_value using (jft_id) where ad_id=23
and av_text=upper(trim(p_client));
- select f_id into fid_good from
- attr_value join jnt_fic_att_value using (jft_id) where ad_id=23
and av_text=upper(trim(p_fiche));
- insert into quant_sold
-
(qs_internal,j_id,qs_fiche,qs_quantite,qs_price,qs_vat,qs_vat_code,qs_client,qs_valid)
- values
-
(p_internal,p_jid,fid_good,p_quant,p_price,p_vat,p_vat_code,fid_client,'Y');
- return;
-end;
- $$
- LANGUAGE plpgsql;
-CREATE FUNCTION insert_quick_code(nf_id integer, tav_text text) RETURNS integer
- AS $$
- declare
- ns integer;
- nExist integer;
- tText text;
- begin
- tText := upper(trim(tav_text));
- tText := replace(tText,' ','');
-
- loop
- -- take the next sequence
- select nextval('s_jnt_fic_att_value') into ns;
- if length (tText) = 0 or tText is null then
- tText := 'FID'||ns;
- end if;
- -- av_text already used ?
- select count(*) into nExist
- from jnt_fic_att_value join attr_value using (jft_id)
- where
- ad_id=23 and av_text=upper(tText);
-
- if nExist = 0 then
- exit;
- end if;
- tText:='FID'||ns;
- end loop;
- -- insert into table jnt_fic_att_value
- insert into jnt_fic_att_value values (ns,nf_id,23);
- -- insert value into attr_value
- insert into attr_value values (ns,upper(tText));
- return ns;
- end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION jrn_check_periode() RETURNS "trigger"
- AS $$
-declare
-bClosed bool;
-str_status text;
-ljr_tech_per jrn.jr_tech_per%TYPE;
-ljr_def_id jrn.jr_def_id%TYPE;
-lreturn jrn%ROWTYPE;
-begin
-if TG_OP='INSERT' then
- ljr_tech_per :=NEW.jr_tech_per;
- ljr_def_id :=NEW.jr_def_id;
- lreturn :=NEW;
-end if;
-
-if TG_OP='DELETE' then
- ljr_tech_per :=OLD.jr_tech_per;
- ljr_def_id :=OLD.jr_def_id;
- lreturn :=OLD;
-end if;
-
-select p_closed into bClosed from parm_periode
- where p_id=ljr_tech_per;
-
-if bClosed = true then
- raise exception 'Periode fermee';
-end if;
-
-select status into str_status from jrn_periode
- where p_id =ljr_tech_per and jrn_def_id=ljr_def_id;
-
-if str_status <> 'OP' then
- raise exception 'Periode fermee';
-end if;
-
-return lreturn;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION jrn_def_add() RETURNS "trigger"
- AS $$begin
-execute 'insert into jrn_periode(p_id,jrn_def_id,status) select
p_id,'||NEW.jrn_def_id||',
- case when p_central=true then ''CE''
- when p_closed=true then ''CL''
- else ''OP''
- end
-from
-parm_periode ';
-return NEW;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION jrn_def_delete() RETURNS "trigger"
- AS $$
-declare
-nb numeric;
-begin
-select count(*) into nb from jrn where jr_def_id=OLD.jrn_def_id;
-
-if nb <> 0 then
- raise exception 'EFFACEMENT INTERDIT: JOURNAL UTILISE';
-end if;
-return OLD;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION jrn_del() RETURNS "trigger"
- AS $$
-declare
-row jrn%ROWTYPE;
-begin
-row:=OLD;
-insert into del_jrn ( jr_id,
- jr_def_id,
- jr_montant,
- jr_comment,
- jr_date,
- jr_grpt_id,
- jr_internal,
- jr_tech_date,
- jr_tech_per,
- jrn_ech,
- jr_ech,
- jr_rapt,
- jr_valid,
- jr_opid,
- jr_c_opid,
- jr_pj,
- jr_pj_name,
- jr_pj_type,
- jr_pj_number,
- del_jrn_date)
- select jr_id,
- jr_def_id,
- jr_montant,
- jr_comment,
- jr_date,
- jr_grpt_id,
- jr_internal,
- jr_tech_date,
- jr_tech_per,
- jrn_ech,
- jr_ech,
- jr_rapt,
- jr_valid,
- jr_opid,
- jr_c_opid,
- jr_pj,
- jr_pj_name,
- jr_pj_type,
- jr_pj_number
- ,now() from jrn where jr_id=row.jr_id;
-return row;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION jrnx_del() RETURNS "trigger"
- AS $$
-declare
-row jrnx%ROWTYPE;
-begin
-row:=OLD;
-insert into del_jrnx select * from jrnx where j_id=row.j_id;
-return row;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION plan_analytic_ins_upd() RETURNS "trigger"
- AS $$
-declare
- name text;
-begin
- name:=upper(NEW.pa_name);
- name:=trim(name);
- name:=replace(name,' ','');
- NEW.pa_name:=name;
-return NEW;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION poste_analytique_ins_upd() RETURNS "trigger"
- AS $$declare
-name text;
-rCount record;
-
-begin
-name:=upper(NEW.po_name);
-name:=trim(name);
-name:=replace(name,' ','');
-NEW.po_name:=name;
-
-if NEW.ga_id is NULL then
-return NEW;
-end if;
-
-if length(trim(NEW.ga_id)) = 0 then
- NEW.ga_id:=NULL;
- return NEW;
-end if;
-perform 'select ga_id from groupe_analytique where ga_id='||NEW.ga_id;
-if NOT FOUND then
- raise exception' Inexistent Group Analytic %',NEW.ga_id;
-end if;
-return NEW;
-end;$$
- LANGUAGE plpgsql;
-CREATE FUNCTION proc_check_balance() RETURNS "trigger"
- AS $$
-declare
- diff numeric;
- tt integer;
-begin
- if TG_OP = 'INSERT' or TG_OP='UPDATE' then
- tt=NEW.jr_grpt_id;
- diff:=check_balance(tt);
- if diff != 0 then
- raise exception 'balance error %',diff ;
- end if;
- return NEW;
- end if;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION t_document_modele_validate() RETURNS "trigger"
- AS $$
-declare
- lText text;
- modified document_modele%ROWTYPE;
-begin
- modified:=NEW;
-
- modified.md_filename:=replace(NEW.md_filename,' ','_');
- return modified;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION t_document_type_insert() RETURNS "trigger"
- AS $$
-declare
-nCounter integer;
- BEGIN
-select count(*) into nCounter from pg_class where
relname='seq_doc_type_'||NEW.dt_id;
-if nCounter = 0 then
- execute 'create sequence seq_doc_type_'||NEW.dt_id;
-end if;
- RETURN NEW;
- END;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION t_document_validate() RETURNS "trigger"
- AS $$
-declare
- lText text;
- modified document%ROWTYPE;
-begin
- modified:=NEW;
- modified.d_filename:=replace(NEW.d_filename,' ','_');
- return modified;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION t_jrn_def_sequence() RETURNS "trigger"
- AS $$
-declare
-nCounter integer;
-
- BEGIN
- select count(*) into nCounter
- from pg_class where relname='s_jrn_'||NEW.jrn_def_id;
- if nCounter = 0 then
- execute 'create sequence s_jrn_'||NEW.jrn_def_id;
- raise notice 'Creating sequence s_jrn_%',NEW.jrn_def_id;
- end if;
-
- RETURN NEW;
- END;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION tmp_pcmn_ins() RETURNS "trigger"
- AS $$
-declare
- r_record tmp_pcmn%ROWTYPE;
-begin
-r_record=NEW;
-if length(trim(r_record.pcm_type))=0 or r_record.pcm_type is NULL then
- r_record.pcm_type:=find_pcm_type(NEW.pcm_val);
- return r_record;
-end if;
-return NEW;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION trim_cvs_quote() RETURNS "trigger"
- AS $$
-declare
- modified import_tmp%ROWTYPE;
-begin
- modified:=NEW;
- modified.devise=replace(new.devise,'"','');
- modified.poste_comptable=replace(new.poste_comptable,'"','');
- modified.compte_ordre=replace(NEW.COMPTE_ORDRE,'"','');
- modified.detail=replace(NEW.DETAIL,'"','');
- modified.num_compte=replace(NEW.NUM_COMPTE,'"','');
- return modified;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION trim_space_format_csv_banque() RETURNS "trigger"
- AS $$
-declare
- modified format_csv_banque%ROWTYPE;
-begin
- modified.name=trim(NEW.NAME);
- modified.include_file=trim(new.include_file);
- if ( length(modified.name) = 0 ) then
- modified.name=null;
- end if;
- if ( length(modified.include_file) = 0 ) then
- modified.include_file=null;
- end if;
-
- return modified;
-end;
-$$
- LANGUAGE plpgsql;
-CREATE FUNCTION tva_delete(integer) RETURNS void
- AS $_$
-declare
- p_tva_id alias for $1;
- nCount integer;
-begin
- nCount=0;
- select count(*) into nCount from quant_sold where qs_vat_code=p_tva_id;
- if nCount != 0 then
- return;
-
- end if;
- select count(*) into nCount from quant_purchase where
qp_vat_code=p_tva_id;
- if nCount != 0 then
- return;
-
- end if;
-
-delete from tva_rate where tva_id=p_tva_id;
- return;
-end;
-$_$
- LANGUAGE plpgsql;
-CREATE FUNCTION tva_insert(text, numeric, text, text) RETURNS integer
- AS $_$
-declare
-l_tva_id integer;
-p_tva_label alias for $1;
-p_tva_rate alias for $2;
-p_tva_comment alias for $3;
-p_tva_poste alias for $4;
-debit text;
-credit text;
-nCount integer;
-begin
-if length(trim(p_tva_label)) = 0 then
- return 3;
-end if;
-
-if length(trim(p_tva_poste)) != 0 then
- if position (',' in p_tva_poste) = 0 then return 4; end if;
- debit = split_part(p_tva_poste,',',1);
- credit = split_part(p_tva_poste,',',2);
- select count(*) into nCount from tmp_pcmn where
pcm_val=debit::account_type;
- if nCount = 0 then return 4; end if;
- select count(*) into nCount from tmp_pcmn where
pcm_val=credit::account_type;
- if nCount = 0 then return 4; end if;
-
-end if;
-select into l_tva_id nextval('s_tva') ;
-insert into tva_rate(tva_id,tva_label,tva_rate,tva_comment,tva_poste)
- values (l_tva_id,p_tva_label,p_tva_rate,p_tva_comment,p_tva_poste);
-return 0;
-end;
-$_$
- LANGUAGE plpgsql;
-CREATE FUNCTION tva_modify(integer, text, numeric, text, text) RETURNS integer
- AS $_$
-declare
-p_tva_id alias for $1;
-p_tva_label alias for $2;
-p_tva_rate alias for $3;
-p_tva_comment alias for $4;
-p_tva_poste alias for $5;
-debit text;
-credit text;
-nCount integer;
-begin
-if length(trim(p_tva_label)) = 0 then
- return 3;
-end if;
-
-if length(trim(p_tva_poste)) != 0 then
- if position (',' in p_tva_poste) = 0 then return 4; end if;
- debit = split_part(p_tva_poste,',',1);
- credit = split_part(p_tva_poste,',',2);
- select count(*) into nCount from tmp_pcmn where
pcm_val=debit::account_type;
- if nCount = 0 then return 4; end if;
- select count(*) into nCount from tmp_pcmn where
pcm_val=credit::account_type;
- if nCount = 0 then return 4; end if;
-
-end if;
-update tva_rate set
tva_label=p_tva_label,tva_rate=p_tva_rate,tva_comment=p_tva_comment,tva_poste=p_tva_poste
- where tva_id=p_tva_id;
-return 0;
-end;
-$_$
- LANGUAGE plpgsql;
-CREATE FUNCTION update_quick_code(njft_id integer, tav_text text) RETURNS
integer
- AS $$
- declare
- ns integer;
- nExist integer;
- tText text;
- old_qcode varchar;
- begin
- -- get current value
- select av_text into old_qcode from attr_value where jft_id=njft_id;
- -- av_text didn't change so no update
- if tav_text = upper( trim(old_qcode)) then
- return 0;
- end if;
-
- tText := trim(upper(tav_text));
- tText := replace(tText,' ','');
- if length ( tText) = 0 or tText is null then
- return 0;
- end if;
-
- ns := njft_id;
-
- loop
- -- av_text already used ?
- select count(*) into nExist
- from jnt_fic_att_value join attr_value using (jft_id)
- where
- ad_id=23 and av_text=tText;
-
- if nExist = 0 then
- exit;
- end if;
- if tText = 'FID'||ns then
- -- take the next sequence
- select nextval('s_jnt_fic_att_value') into ns;
- end if;
- tText :='FID'||ns;
-
- end loop;
- update attr_value set av_text = tText where jft_id=njft_id;
-
- -- update also the contact
- update attr_value set av_text = tText
- where jft_id in
- ( select jft_id
- from jnt_fic_att_value join attr_value using
(jft_id)
- where ad_id=25 and av_text=old_qcode);
-
-
- update jrnx set j_qcode=tText where j_qcode = old_qcode;
- return ns;
- end;
-$$
- LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION account_update(p_f_id integer, p_account
account_type)
- RETURNS integer AS
-$BODY$
-declare
-nMax fiche.f_id%type;
-nCount integer;
-nParent tmp_pcmn.pcm_val_parent%type;
-sName varchar;
-nJft_id attr_value.jft_id%type;
-first text;
-second text;
-begin
-
- if length(trim(p_account)) != 0 then
- if position (',' in p_account) = 0 then
- select count(*) into nCount from tmp_pcmn where
pcm_val=p_account::account_type;
- if nCount = 0 then
- select av_text into sName from
- attr_value join jnt_fic_att_value using (jft_id)
- where
- ad_id=1 and f_id=p_f_id;
- nParent:=account_parent(p_account::account_type);
- insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent)
values (p_account::account_type,sName,nParent);
- end if;
- else
- raise info 'presence of a comma';
- -- there is 2 accounts separated by a comma
- first := split_part(p_account,',',1);
- second := split_part(p_account,',',2);
- -- check there is no other coma
- raise info 'first value % second value %', first, second;
-
- if position (',' in first) != 0 or position (',' in second) !=
0 then
- raise exception 'Too many comas, invalid account';
- end if;
- end if;
- end if;
- select jft_id into njft_id from jnt_fic_att_value where f_id=p_f_id and
ad_id=5;
- update attr_value set av_text=p_account where jft_id=njft_id;
-
-return njft_id;
-end;
-$BODY$
-LANGUAGE 'plpgsql' ;
-
-CREATE OR REPLACE FUNCTION account_insert(p_f_id integer, p_account
account_type)
- RETURNS integer AS
-$BODY$
-declare
-nParent tmp_pcmn.pcm_val_parent%type;
-sName varchar;
-nNew tmp_pcmn.pcm_val%type;
-bAuto bool;
-nFd_id integer;
-nCount integer;
-first text;
-second text;
-begin
-
- if length(trim(p_account)) != 0 then
- -- if there is coma in p_account, treat normally
- if position (',' in p_account) = 0 then
- raise info 'p_account is not empty';
- select count(*) into nCount from tmp_pcmn
where pcm_val=p_account::account_type;
- raise notice 'found in tmp_pcm %',nCount;
- if nCount !=0 then
- raise info 'this account exists in
tmp_pcmn ';
- perform
attribut_insert(p_f_id,5,p_account);
- else
- -- account doesn't exist, create it
- select av_text into sName from
- attr_value join
jnt_fic_att_value using (jft_id)
- where
- ad_id=1 and f_id=p_f_id;
-
-
nParent:=account_parent(p_account::account_type);
- insert into
tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values
(p_account::account_type,sName,nParent);
- perform
attribut_insert(p_f_id,5,p_account::text);
-
- end if;
- else
- raise info 'presence of a comma';
- -- there is 2 accounts separated by a comma
- first := split_part(p_account,',',1);
- second := split_part(p_account,',',2);
- -- check there is no other coma
- raise info 'first value % second value %', first, second;
-
- if position (',' in first) != 0 or position (',' in second) !=
0 then
- raise exception 'Too many comas, invalid account';
- end if;
- perform attribut_insert(p_f_id,5,p_account::text);
- end if;
- else
- raise info 'p_account is empty';
- select fd_id into nFd_id from fiche where f_id=p_f_id;
- bAuto:= account_auto(nFd_id);
- if bAuto = true then
- raise notice 'account generated automatically';
- nNew:=account_compute(p_f_id);
- raise notice 'nNew %', nNew;
- select av_text into sName from
- attr_value join jnt_fic_att_value using (jft_id)
- where
- ad_id=1 and f_id=p_f_id;
- nParent:=account_parent(nNew);
- perform account_add (nNew,sName);
- perform attribut_insert(p_f_id,5,nNew);
-
- else
- -- if there is an account_base then it is the default
- select fd_class_base::text into nNew from fiche_def join
fiche using (fd_id) where f_id=p_f_id;
- if nNew is null or length(trim(nNew)) = 0 then
- raise notice 'count is null';
- perform attribut_insert(p_f_id,5,null);
- else
- perform attribut_insert(p_f_id,5,nNew);
- end if;
- end if;
- end if;
-
-return 0;
-end;
-$BODY$
-LANGUAGE 'plpgsql';
-
-
-
-
-CREATE TRIGGER action_gestion_t_insert_update
- BEFORE INSERT OR UPDATE ON action_gestion
- FOR EACH ROW
- EXECUTE PROCEDURE action_gestion_ins_upd();
-
-
-
-COMMENT ON TRIGGER action_gestion_t_insert_update ON action_gestion IS
'Truncate the column ag_title to 70 char';
-
-
-
-CREATE TRIGGER document_modele_validate
- BEFORE INSERT OR UPDATE ON document_modele
- FOR EACH ROW
- EXECUTE PROCEDURE t_document_modele_validate();
-
-
-
-CREATE TRIGGER document_validate
- BEFORE INSERT OR UPDATE ON document
- FOR EACH ROW
- EXECUTE PROCEDURE t_document_validate();
-
-
-
-CREATE TRIGGER info_def_ins_upd_t
- BEFORE INSERT OR UPDATE ON info_def
- FOR EACH ROW
- EXECUTE PROCEDURE info_def_ins_upd();
-
-
-
-CREATE TRIGGER t_check_balance
- AFTER INSERT OR UPDATE ON jrn
- FOR EACH ROW
- EXECUTE PROCEDURE proc_check_balance();
-
-
-
-CREATE TRIGGER t_check_jrn
- BEFORE INSERT OR DELETE ON jrn
- FOR EACH ROW
- EXECUTE PROCEDURE jrn_check_periode();
-
-
-
-CREATE TRIGGER t_group_analytic_del
- BEFORE DELETE ON groupe_analytique
- FOR EACH ROW
- EXECUTE PROCEDURE group_analytique_del();
-
-
-
-CREATE TRIGGER t_group_analytic_ins_upd
- BEFORE INSERT OR UPDATE ON groupe_analytique
- FOR EACH ROW
- EXECUTE PROCEDURE group_analytic_ins_upd();
-
-
-
-CREATE TRIGGER t_jrn_def_add_periode
- AFTER INSERT ON jrn_def
- FOR EACH ROW
- EXECUTE PROCEDURE jrn_def_add();
-
-
-
-CREATE TRIGGER t_jrn_def_delete
- BEFORE DELETE ON jrn_def
- FOR EACH ROW
- EXECUTE PROCEDURE jrn_def_delete();
-
-
-
-CREATE TRIGGER t_jrn_del
- BEFORE DELETE ON jrn
- FOR EACH ROW
- EXECUTE PROCEDURE jrn_del();
-
-
-
-CREATE TRIGGER t_jrnx_del
- BEFORE DELETE ON jrnx
- FOR EACH ROW
- EXECUTE PROCEDURE jrnx_del();
-
-
-
-CREATE TRIGGER t_plan_analytique_ins_upd
- BEFORE INSERT OR UPDATE ON plan_analytique
- FOR EACH ROW
- EXECUTE PROCEDURE plan_analytic_ins_upd();
-
-
-
-CREATE TRIGGER t_poste_analytique_ins_upd
- BEFORE INSERT OR UPDATE ON poste_analytique
- FOR EACH ROW
- EXECUTE PROCEDURE poste_analytique_ins_upd();
-
-
-
-CREATE TRIGGER t_tmp_pcmn_ins
- BEFORE INSERT ON tmp_pcmn
- FOR EACH ROW
- EXECUTE PROCEDURE tmp_pcmn_ins();
-
-
-
-CREATE TRIGGER trg_extension_ins_upd
- BEFORE INSERT OR UPDATE ON extension
- FOR EACH ROW
- EXECUTE PROCEDURE extension_ins_upd();
-
-
-
-CREATE TRIGGER trigger_document_type_i
- AFTER INSERT ON document_type
- FOR EACH ROW
- EXECUTE PROCEDURE t_document_type_insert();
-
-
-
-CREATE TRIGGER trigger_jrn_def_sequence_i
- AFTER INSERT ON jrn_def
- FOR EACH ROW
- EXECUTE PROCEDURE t_jrn_def_sequence();
-
-
-
-CREATE TRIGGER trim_quote
- BEFORE INSERT OR UPDATE ON import_tmp
- FOR EACH ROW
- EXECUTE PROCEDURE trim_cvs_quote();
-
-
-
-CREATE TRIGGER trim_space
- BEFORE INSERT OR UPDATE ON format_csv_banque
- FOR EACH ROW
- EXECUTE PROCEDURE trim_space_format_csv_banque();
-
-DROP VIEW vw_fiche_def;
-DROP VIEW vw_supplier;
-DROP VIEW vw_client;
-
-CREATE OR REPLACE VIEW vw_fiche_def AS
- SELECT jnt_fic_attr.fd_id, jnt_fic_attr.ad_id, attr_def.ad_text,
fiche_def.fd_class_base, fiche_def.fd_label, fiche_def.fd_create_account,
fiche_def.frd_id
- FROM fiche_def
- JOIN jnt_fic_attr USING (fd_id)
- JOIN attr_def ON attr_def.ad_id = jnt_fic_attr.ad_id;
-
-COMMENT ON VIEW vw_fiche_def IS 'all the attributs for card family';
-
-CREATE OR REPLACE VIEW vw_supplier AS
- SELECT a.f_id, a.av_text AS name, a1.av_text AS quick_code, b.av_text AS
tva_num, c.av_text AS poste_comptable, d.av_text AS rue, e.av_text AS
code_postal, f.av_text AS pays, g.av_text AS telephone, h.av_text AS email
- FROM ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 1) a
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 13) b USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 23) a1 USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 5) c USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 14) d USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 15) e USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 16) f USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 17) g USING (f_id)
- LEFT JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 18) h USING (f_id)
- WHERE a.frd_id = 8;
-CREATE OR REPLACE VIEW vw_client AS
- SELECT a.f_id, a.av_text AS name, a1.av_text AS quick_code, b.av_text AS
tva_num, c.av_text AS poste_comptable, d.av_text AS rue, e.av_text AS
code_postal, f.av_text AS pays, g.av_text AS telephone, h.av_text AS email
- FROM ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 1) a
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 13) b USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 23) a1 USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 5) c USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 14) d USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 15) e USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 16) f USING (f_id)
- JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 17) g USING (f_id)
- LEFT JOIN ( SELECT jnt_fic_att_value.jft_id, fiche.f_id, fiche_def.frd_id,
fiche.fd_id, fiche_def.fd_class_base, fiche_def.fd_label,
fiche_def.fd_create_account, fiche_def_ref.frd_text,
fiche_def_ref.frd_class_base, jnt_fic_att_value.ad_id, attr_value.av_text
- FROM fiche
- JOIN fiche_def USING (fd_id)
- JOIN fiche_def_ref USING (frd_id)
- JOIN jnt_fic_att_value USING (f_id)
- JOIN attr_value USING (jft_id)
- WHERE jnt_fic_att_value.ad_id = 18) h USING (f_id)
- WHERE a.frd_id = 9;
-
-CREATE OR REPLACE FUNCTION account_parent(p_account account_type)
- RETURNS poste_comptable AS
-$BODY$
-declare
- nParent tmp_pcmn.pcm_val_parent%type;
- sParent varchar;
- nCount integer;
-begin
- sParent:=p_account;
- sParent:=trim(sParent::text);
- nParent:=0;
- while nParent = 0 loop
- select count(*) into nCount
- from tmp_pcmn
- where
- pcm_val = sParent;
- if nCount != 0 then
- nParent:=sParent;
- return nParent;
- end if;
- sParent:= substr(sParent,1,length(sParent)-1);
- if length(sParent) <= 0 then
- raise exception 'Impossible de trouver le compte parent
pour %',p_account;
- end if;
- end loop;
- raise notice 'account_parent : Parent is %',nParent;
- return nParent;
-end;
-$BODY$
-LANGUAGE 'plpgsql' VOLATILE;
-
-
-CREATE OR REPLACE FUNCTION account_compute(p_f_id integer)
- RETURNS account_type AS
-$BODY$
-declare
- class_base account_type;
- maxcode account_type;
- nCount integer;
-begin
- select fd_class_base into class_base
- from
- fiche_def join fiche using (fd_id)
- where
- f_id=p_f_id;
- raise notice 'account_compute class base %',class_base;
- select count (pcm_val) into nCount from tmp_pcmn where pcm_val_parent
= class_base;
- if nCount = 0 then
- maxcode:=class_base;
- else
- select max (pcm_val) into maxcode from tmp_pcmn where
pcm_val_parent = class_base;
- end if;
- if maxcode = class_base then
- maxcode:=class_base::text||'0000';
- end if;
-
maxcode:=to_char(to_number(maxcode,'999999999999999999999999999999')+1,'999999999999999999999999999999');
- raise notice 'account_compute Max code %',maxcode;
- return maxcode::text;
-end;
-$BODY$
- LANGUAGE 'plpgsql' VOLATILE;
-commit;
Deleted: phpcompta/trunk/sql/comptaproc.sql
===================================================================
--- phpcompta/trunk/sql/comptaproc.sql 2011-11-22 22:44:52 UTC (rev 4446)
+++ phpcompta/trunk/sql/comptaproc.sql 2011-11-22 22:50:44 UTC (rev 4447)
@@ -1,1972 +0,0 @@
---
--- PostgreSQL database dump
---
-
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = off;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-SET escape_string_warning = off;
-
---
--- Name: comptaproc; Type: SCHEMA; Schema: -; Owner: -
---
-
-CREATE SCHEMA comptaproc;
-
-
-SET search_path = comptaproc, pg_catalog;
-
---
--- Name: account_add(public.account_type, character varying); Type: FUNCTION;
Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION account_add(p_id public.account_type, p_name character
varying) RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- nParent tmp_pcmn.pcm_val_parent%type;
- nCount integer;
-begin
- select count(*) into nCount from tmp_pcmn where pcm_val=p_id;
- if nCount = 0 then
- nParent=account_parent(p_id);
- insert into tmp_pcmn (pcm_val,pcm_lib,pcm_val_parent)
- values (p_id, p_name,nParent);
- end if;
-return;
-end ;
-$$;
-
-
---
--- Name: account_auto(integer); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION account_auto(p_fd_id integer) RETURNS boolean
- LANGUAGE plpgsql
- AS $$
-declare
- l_auto bool;
-begin
-
- select fd_create_account into l_auto from fiche_def where fd_id=p_fd_id;
- if l_auto is null then
- l_auto:=false;
- end if;
- return l_auto;
-end;
-$$;
-
-
---
--- Name: account_compute(integer); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION account_compute(p_f_id integer) RETURNS public.account_type
- LANGUAGE plpgsql
- AS $$
-declare
- class_base fiche_def.fd_class_base%type;
- maxcode numeric;
- sResult account_type;
-begin
- select fd_class_base into class_base
- from
- fiche_def join fiche using (fd_id)
- where
- f_id=p_f_id;
- raise notice 'account_compute class base %',class_base;
- select count (pcm_val) into maxcode from tmp_pcmn where pcm_val_parent
= class_base;
- if maxcode = 0 then
- maxcode:=class_base::numeric;
- else
- select max (pcm_val) into maxcode from tmp_pcmn where
pcm_val_parent = class_base;
- maxcode:=maxcode::numeric;
- end if;
- if maxcode::text = class_base then
- maxcode:=class_base::numeric*1000;
- end if;
- maxcode:=maxcode+1;
- raise notice 'account_compute Max code %',maxcode;
- sResult:=maxcode::account_type;
- return sResult;
-end;
-$$;
-
-
---
--- Name: account_insert(integer, text); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION account_insert(p_f_id integer, p_account text) RETURNS integer
- LANGUAGE plpgsql
- AS $_$
-declare
- nParent tmp_pcmn.pcm_val_parent%type;
- sName varchar;
- nNew tmp_pcmn.pcm_val%type;
- bAuto bool;
- nFd_id integer;
- sClass_Base fiche_def.fd_class_base%TYPE;
- nCount integer;
- first text;
- second text;
-begin
-
- if p_account is not null and length(trim(p_account)) != 0 then
- -- if there is coma in p_account, treat normally
- if position (',' in p_account) = 0 then
- raise info 'p_account is not empty';
- select count(*) into nCount from tmp_pcmn
where pcm_val=p_account::account_type;
- raise notice 'found in tmp_pcm %',nCount;
- if nCount !=0 then
- raise info 'this account exists in
tmp_pcmn ';
- perform
attribut_insert(p_f_id,5,p_account);
- else
- -- account doesn't exist, create it
- select ad_value into sName from
- fiche_detail
- where
- ad_id=1 and f_id=p_f_id;
-
-
nParent:=account_parent(p_account::account_type);
- insert into
tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent) values
(p_account::account_type,sName,nParent);
- perform
attribut_insert(p_f_id,5,p_account);
-
- end if;
- else
- raise info 'presence of a comma';
- -- there is 2 accounts separated by a comma
- first := split_part(p_account,',',1);
- second := split_part(p_account,',',2);
- -- check there is no other coma
- raise info 'first value % second value %', first, second;
-
- if position (',' in first) != 0 or position (',' in second) !=
0 then
- raise exception 'Too many comas, invalid account';
- end if;
- perform attribut_insert(p_f_id,5,p_account);
- end if;
- else
- raise info 'p_account is empty';
- select fd_id into nFd_id from fiche where f_id=p_f_id;
- bAuto:= account_auto(nFd_id);
-
- select fd_class_base into sClass_base from fiche_def where
fd_id=nFd_id;
-raise info 'sClass_Base : %',sClass_base;
- if bAuto = true and sClass_base similar to '^[[:digit:]]*$'
then
- raise info 'account generated automatically';
- nNew:=account_compute(p_f_id);
- raise info 'nNew %', nNew;
- select ad_value into sName from
- fiche_detail
- where
- ad_id=1 and f_id=p_f_id;
- nParent:=account_parent(nNew);
- perform account_add (nNew,sName);
- perform attribut_insert(p_f_id,5,nNew);
-
- else
- -- if there is an account_base then it is the default
- select fd_class_base::account_type into nNew from
fiche_def join fiche using (fd_id) where f_id=p_f_id;
- if nNew is null or length(trim(nNew)) = 0 then
- raise notice 'count is null';
- perform attribut_insert(p_f_id,5,null);
- else
- perform attribut_insert(p_f_id,5,nNew);
- end if;
- end if;
- end if;
-
-return 0;
-end;
-$_$;
-
-
---
--- Name: account_parent(public.account_type); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION account_parent(p_account public.account_type) RETURNS
public.account_type
- LANGUAGE plpgsql
- AS $$
-declare
- sSubParent tmp_pcmn.pcm_val_parent%type;
- sResult tmp_pcmn.pcm_val_parent%type;
- nCount integer;
-begin
- if p_account is NULL then
- return NULL;
- end if;
- sSubParent:=p_account;
- while true loop
- select count(*) into nCount
- from tmp_pcmn
- where
- pcm_val = sSubParent;
- if nCount != 0 then
- sResult:= sSubParent;
- exit;
- end if;
- sSubParent:= substr(sSubParent,1,length(sSubParent)-1);
- if length(sSubParent) <= 0 then
- raise exception 'Impossible de trouver le compte parent
pour %',p_account;
- end if;
- raise notice 'sSubParent % % ',sSubParent,length(sSubParent);
- end loop;
- raise notice 'account_parent : Parent is %',sSubParent;
- return sSubParent;
-end;
-$$;
-
-
---
--- Name: account_update(integer, public.account_type); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION account_update(p_f_id integer, p_account public.account_type)
RETURNS integer
- LANGUAGE plpgsql
- AS $$
-declare
- nMax fiche.f_id%type;
- nCount integer;
- nParent tmp_pcmn.pcm_val_parent%type;
- sName varchar;
- first text;
- second text;
-begin
-
- if length(trim(p_account)) != 0 then
- if position (',' in p_account) = 0 then
- select count(*) into nCount from tmp_pcmn where
pcm_val=p_account;
- if nCount = 0 then
- select ad_value into sName from
- fiche_detail
- where
- ad_id=1 and f_id=p_f_id;
- nParent:=account_parent(p_account);
- insert into tmp_pcmn(pcm_val,pcm_lib,pcm_val_parent)
values (p_account,sName,nParent);
- end if;
- else
- raise info 'presence of a comma';
- -- there is 2 accounts separated by a comma
- first := split_part(p_account,',',1);
- second := split_part(p_account,',',2);
- -- check there is no other coma
- raise info 'first value % second value %', first, second;
-
- if position (',' in first) != 0 or position (',' in second) !=
0 then
- raise exception 'Too many comas, invalid account';
- end if;
- end if;
- end if;
-
- update fiche_detail set ad_value=p_account where f_id=p_f_id and
ad_id=5 ;
-
-return 0;
-end;
-$$;
-
-
---
--- Name: action_gestion_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION action_gestion_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-begin
-NEW.ag_title := substr(trim(NEW.ag_title),1,70);
-NEW.ag_hour := substr(trim(NEW.ag_hour),1,5);
-return NEW;
-end;
-$$;
-
-
---
--- Name: action_get_tree(bigint); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION action_get_tree(p_id bigint) RETURNS SETOF bigint
- LANGUAGE plpgsql
- AS $$
-
-declare
- e bigint;
- i bigint;
-begin
- for e in select ag_id from action_gestion where ag_ref_ag_id=p_id
- loop
- for i in select action_get_tree from comptaproc.action_get_tree(e)
- loop
- raise notice ' == i %', i;
- return next i;
- end loop;
- raise notice ' = e %', e;
- return next e;
- end loop;
- return;
-
-end;
-$$;
-
-
---
--- Name: attribut_insert(integer, integer, character varying); Type: FUNCTION;
Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION attribut_insert(p_f_id integer, p_ad_id integer, p_value
character varying) RETURNS void
- LANGUAGE plpgsql
- AS $$
-begin
- insert into fiche_detail (f_id,ad_id, ad_value) values
(p_f_id,p_ad_id,p_value);
-
-return;
-end;
-$$;
-
-
---
--- Name: attribute_correct_order(); Type: FUNCTION; Schema: comptaproc; Owner:
-
---
-
-CREATE FUNCTION attribute_correct_order() RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- crs_correct cursor for select A.jnt_id,A.jnt_order from jnt_fic_attr as A
join jnt_fic_attr as B using (fd_id) where A.jnt_order=B.jnt_order and A.jnt_id
> B.jnt_id;
- rec record;
-begin
- open crs_correct;
- loop
- fetch crs_correct into rec;
- if NOT FOUND then
- close crs_correct;
- return;
- end if;
- update jnt_fic_attr set jnt_order=jnt_order + 1 where jnt_id =
rec.jnt_id;
- end loop;
- close crs_correct;
- perform attribute_correct_order ();
-end;
-$$;
-
-
---
--- Name: card_after_delete(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION card_after_delete() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-
-begin
-
- delete from action_gestion where f_id_dest = OLD.f_id;
- return OLD;
-
-end;
-$$;
-
-
---
--- Name: card_class_base(integer); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION card_class_base(p_f_id integer) RETURNS text
- LANGUAGE plpgsql
- AS $$
-declare
- n_poste fiche_def.fd_class_base%type;
-begin
-
- select fd_class_base into n_poste from fiche_def join fiche using
-(fd_id)
- where f_id=p_f_id;
- if not FOUND then
- raise exception 'Invalid fiche card_class_base(%)',p_f_id;
- end if;
-return n_poste;
-end;
-$$;
-
-
---
--- Name: check_balance(integer); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION check_balance(p_grpt integer) RETURNS numeric
- LANGUAGE plpgsql
- AS $$
-declare
- amount_jrnx_debit numeric;
- amount_jrnx_credit numeric;
- amount_jrn numeric;
-begin
- select sum (j_montant) into amount_jrnx_credit
- from jrnx
- where
- j_grpt=p_grpt
- and j_debit=false;
-
- select sum (j_montant) into amount_jrnx_debit
- from jrnx
- where
- j_grpt=p_grpt
- and j_debit=true;
-
- select jr_montant into amount_jrn
- from jrn
- where
- jr_grpt_id=p_grpt;
-
- if ( amount_jrnx_debit != amount_jrnx_credit )
- then
- return abs(amount_jrnx_debit-amount_jrnx_credit);
- end if;
- if ( amount_jrn != amount_jrnx_credit)
- then
- return -1*abs(amount_jrn - amount_jrnx_credit);
- end if;
- return 0;
-end;
-$$;
-
-
---
--- Name: correct_sequence(text, text, text); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION correct_sequence(p_sequence text, p_col text, p_table text)
RETURNS integer
- LANGUAGE plpgsql
- AS $$
-declare
-last_sequence int8;
-max_sequence int8;
-n integer;
-begin
- select count(*) into n from pg_class where relkind='S' and
relname=lower(p_sequence);
- if n = 0 then
- raise exception ' Unknow sequence % ',p_sequence;
- end if;
- select count(*) into n from pg_class where relkind='r' and
relname=lower(p_table);
- if n = 0 then
- raise exception ' Unknow table % ',p_table;
- end if;
-
- execute 'select last_value from '||p_sequence into last_sequence;
- raise notice 'Last value of the sequence is %', last_sequence;
-
- execute 'select max('||p_col||') from '||p_table into max_sequence;
- if max_sequence is null then
- max_sequence := 0;
- end if;
- raise notice 'Max value of the sequence is %', max_sequence;
- max_sequence:= max_sequence +1;
- execute 'alter sequence '||p_sequence||' restart with '||max_sequence;
-return 0;
-
-end;
-$$;
-
-
---
--- Name: FUNCTION correct_sequence(p_sequence text, p_col text, p_table text);
Type: COMMENT; Schema: comptaproc; Owner: -
---
-
-COMMENT ON FUNCTION correct_sequence(p_sequence text, p_col text, p_table
text) IS ' Often the primary key is a sequence number and sometimes the value
of the sequence is not synchronized with the primary key ( p_sequence :
sequence name, p_col : col of the pk,p_table : concerned table';
-
-
---
--- Name: create_missing_sequence(); Type: FUNCTION; Schema: comptaproc; Owner:
-
---
-
-CREATE FUNCTION create_missing_sequence() RETURNS integer
- LANGUAGE plpgsql
- AS $$
-declare
-p_sequence text;
-nSeq integer;
-c1 cursor for select jrn_def_id from jrn_def;
-begin
- open c1;
- loop
- fetch c1 into nSeq;
- if not FOUND THEN
- close c1;
- return 0;
- end if;
- p_sequence:='s_jrn_pj'||nSeq::text;
- execute 'create sequence '||p_sequence;
- end loop;
-close c1;
-return 0;
-
-end;
-$$;
-
-
---
--- Name: drop_index(character varying); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION drop_index(p_constraint character varying) RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- nCount integer;
-begin
- select count(*) into nCount from pg_indexes where
indexname=p_constraint;
- if nCount = 1 then
- execute 'drop index '||p_constraint ;
- end if;
-end;
-$$;
-
-
---
--- Name: drop_it(character varying); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION drop_it(p_constraint character varying) RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- nCount integer;
-begin
- select count(*) into nCount from pg_constraint where
conname=p_constraint;
- if nCount = 1 then
- execute 'alter table parm_periode drop constraint '||p_constraint ;
- end if;
-end;
-$$;
-
-
---
--- Name: extension_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION extension_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- sCode text;
- sFile text;
-begin
-sCode:=trim(upper(NEW.ex_code));
-sCode:=replace(sCode,' ','_');
-sCode:=substr(sCode,1,15);
-sCode=upper(sCode);
-NEW.ex_code:=sCode;
-sFile:=NEW.ex_file;
-sFile:=replace(sFile,';','_');
-sFile:=replace(sFile,'<','_');
-sFile:=replace(sFile,'>','_');
-sFile:=replace(sFile,'..','');
-sFile:=replace(sFile,'&','');
-sFile:=replace(sFile,'|','');
-
-
-
-return NEW;
-
-end;
-
-$$;
-
-
---
--- Name: fiche_account_parent(integer); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION fiche_account_parent(p_f_id integer) RETURNS
public.account_type
- LANGUAGE plpgsql
- AS $$
-declare
-ret tmp_pcmn.pcm_val%TYPE;
-begin
- select fd_class_base into ret from fiche_def join fiche using (fd_id)
where f_id=p_f_id;
- if not FOUND then
- raise exception '% N''existe pas',p_f_id;
- end if;
- return ret;
-end;
-$$;
-
-
---
--- Name: fiche_attribut_synchro(integer); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION fiche_attribut_synchro(p_fd_id integer) RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- -- this sql gives the f_id and the missing attribute (ad_id)
- list_missing cursor for select f_id,fd_id,ad_id,jnt_order from
jnt_fic_attr join fiche as A using (fd_id) where fd_id=p_fd_id and ad_id not in
(select ad_id from fiche join fiche_detail using (f_id) where
fd_id=jnt_fic_attr.fd_id and A.f_id=f_id);
- rec record;
-begin
- open list_missing;
- loop
-
- fetch list_missing into rec;
- IF NOT FOUND then
- exit;
- end if;
-
- -- now we insert into attr_value
- insert into fiche_detail (f_id,ad_id,ad_value) values
(rec.f_id,rec.ad_id,null);
- end loop;
- close list_missing;
-end;
-$$;
-
-
---
--- Name: fiche_def_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION fiche_def_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-begin
-
-if position (',' in NEW.fd_class_base) != 0 then
- NEW.fd_create_account='f';
-
-end if;
-return NEW;
-end;$$;
-
-
---
--- Name: fill_quant_fin(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION fill_quant_fin() RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- sBank text;
- sCassa text;
- sCustomer text;
- sSupplier text;
- rec record;
- recBank record;
- recSupp_Cust record;
- nCount integer;
- nAmount numeric;
- nBank integer;
- nOther integer;
- nSupp_Cust integer;
-begin
- select p_value into sBank from parm_code where p_code='BANQUE';
- select p_value into sCassa from parm_code where p_code='CAISSE';
- select p_value into sSupplier from parm_code where p_code='SUPPLIER';
- select p_value into sCustomer from parm_code where p_code='CUSTOMER';
-
- for rec in select jr_id,jr_grpt_id from jrn
- where jr_def_id in (select jrn_def_id from jrn_def where
jrn_def_type='FIN')
- and jr_id not in (select jr_id from quant_fin)
- loop
- -- there are only 2 lines for bank operations
- -- first debit
- select count(j_id) into nCount from jrnx where
j_grpt=rec.jr_grpt_id;
- if nCount > 2 then
- raise notice 'Trop de valeur pour jr_grpt_id % count
%',rec.jr_grpt_id,nCount;
- return;
- end if;
- nBank := 0; nOther:=0;
- for recBank in select j_id, j_montant,j_debit,j_qcode,j_poste
from jrnx where j_grpt=rec.jr_grpt_id
- loop
- if recBank.j_poste like sBank||'%' then
- -- retrieve f_id for bank
- select f_id into nBank from vw_poste_qcode where
j_qcode=recBank.j_qcode;
- if recBank.j_debit = false then
- nAmount=recBank.j_montant*(-1);
- else
- nAmount=recBank.j_montant;
- end if;
- else
- select f_id into nOther from vw_poste_qcode where
j_qcode=recBank.j_qcode;
- end if;
- end loop;
- if nBank != 0 and nOther != 0 then
- insert into quant_fin
(jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nBank,nOther,nAmount);
- end if;
- end loop;
- for rec in select jr_id,jr_grpt_id from jrn
- where jr_def_id in (select jrn_def_id from jrn_def where
jrn_def_type='FIN') and jr_id not in (select jr_id from quant_fin)
- loop
- -- there are only 2 lines for bank operations
- -- first debit
- select count(j_id) into nCount from jrnx where
j_grpt=rec.jr_grpt_id;
- if nCount > 2 then
- raise notice 'Trop de valeur pour jr_grpt_id % count
%',rec.jr_grpt_id,nCount;
- return;
- end if;
- nBank := 0; nOther:=0;
- for recBank in select j_id, j_montant,j_debit,j_qcode,j_poste
from jrnx where j_grpt=rec.jr_grpt_id
- loop
- if recBank.j_poste like sCassa||'%' then
- -- retrieve f_id for bank
- select f_id into nBank from vw_poste_qcode where
j_qcode=recBank.j_qcode;
- if recBank.j_debit = false then
- nAmount=recBank.j_montant*(-1);
- else
- nAmount=recBank.j_montant;
- end if;
- else
- select f_id into nOther from vw_poste_qcode where
j_qcode=recBank.j_qcode;
- end if;
- end loop;
- if nBank != 0 and nOther != 0 then
- insert into quant_fin
(jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nBank,nOther,nAmount);
- end if;
- end loop;
-
- for rec in select jr_id,jr_grpt_id from jrn
- where jr_def_id in (select jrn_def_id from jrn_def where
jrn_def_type='FIN') and jr_id not in (select jr_id from quant_fin)
- loop
- -- there are only 2 lines for bank operations
- -- first debit
- select count(j_id) into nCount from jrnx where
j_grpt=rec.jr_grpt_id;
- if nCount > 2 then
- raise notice 'Trop de valeur pour jr_grpt_id % count
%',rec.jr_grpt_id,nCount;
- return;
- end if;
- nSupp_Cust := 0; nOther:=0;
- for recSupp_Cust in select j_id,
j_montant,j_debit,j_qcode,j_poste from jrnx where j_grpt=rec.jr_grpt_id
- loop
- if recSupp_Cust.j_poste like sSupplier||'%' then
- -- retrieve f_id for bank
- select f_id into nSupp_Cust from vw_poste_qcode where
j_qcode=recSupp_Cust.j_qcode;
- if recSupp_Cust.j_debit = true then
- nAmount=recSupp_Cust.j_montant*(-1);
- else
- nAmount=recSupp_Cust.j_montant;
- end if;
- else if recSupp_Cust.j_poste like sCustomer||'%' then
- select f_id into nSupp_Cust from vw_poste_qcode where
j_qcode=recSupp_Cust.j_qcode;
- if recSupp_Cust.j_debit = false then
- nAmount=recSupp_Cust.j_montant*(-1);
- else
- nAmount=recSupp_Cust.j_montant;
- end if;
- else
- select f_id into nOther from vw_poste_qcode where
j_qcode=recSupp_Cust.j_qcode;
-
- end if;
- end if;
- end loop;
- if nSupp_Cust != 0 and nOther != 0 then
- insert into quant_fin
(jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nOther,nSupp_Cust,nAmount);
- end if;
- end loop;
- for rec in select jr_id,jr_grpt_id from jrn
- where jr_def_id in (select jrn_def_id from jrn_def where
jrn_def_type='FIN') and jr_id not in (select jr_id from quant_fin)
- loop
- -- there are only 2 lines for bank operations
- -- first debit
- select count(j_id) into nCount from jrnx where
j_grpt=rec.jr_grpt_id;
- if nCount > 2 then
- raise notice 'Trop de valeur pour jr_grpt_id % count
%',rec.jr_grpt_id,nCount;
- return;
- end if;
- nSupp_Cust := 0; nOther:=0;
- for recSupp_Cust in select j_id,
j_montant,j_debit,j_qcode,j_poste from jrnx where j_grpt=rec.jr_grpt_id
- loop
- if recSupp_Cust.j_poste like '441%' then
- -- retrieve f_id for bank
- select f_id into nSupp_Cust from vw_poste_qcode where
j_qcode=recSupp_Cust.j_qcode;
- if recSupp_Cust.j_debit = false then
- nAmount=recSupp_Cust.j_montant*(-1);
- else
- nAmount=recSupp_Cust.j_montant;
- end if;
- else
- select f_id into nOther from vw_poste_qcode where
j_qcode=recSupp_Cust.j_qcode;
-
-
- end if;
- end loop;
- if nSupp_Cust != 0 and nOther != 0 then
- insert into quant_fin
(jr_id,qf_bank,qf_other,qf_amount) values (rec.jr_id,nOther,nSupp_Cust,nAmount);
- end if;
- end loop;
- return;
-end;
-$$;
-
-
---
--- Name: find_pcm_type(public.account_type); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION find_pcm_type(pp_value public.account_type) RETURNS text
- LANGUAGE plpgsql
- AS $$
-declare
- str_type parm_poste.p_type%TYPE;
- str_value parm_poste.p_type%TYPE;
- nLength integer;
-begin
- str_value:=pp_value;
- nLength:=length(str_value::text);
- while nLength > 0 loop
- select p_type into str_type from parm_poste where
p_value=str_value;
- if FOUND then
- return str_type;
- end if;
- nLength:=nLength-1;
- str_value:=substring(str_value::text from 1 for
nLength)::account_type;
- end loop;
-return 'CON';
-end;
-$$;
-
-
---
--- Name: find_periode(text); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION find_periode(p_date text) RETURNS integer
- LANGUAGE plpgsql
- AS $$
-
-declare n_p_id int4;
-begin
-
-select p_id into n_p_id
- from parm_periode
- where
- p_start <= to_date(p_date,'DD.MM.YYYY')
- and
- p_end >= to_date(p_date,'DD.MM.YYYY');
-
-if NOT FOUND then
- return -1;
-end if;
-
-return n_p_id;
-
-end;$$;
-
-
---
--- Name: get_letter_jnt(bigint); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION get_letter_jnt(a bigint) RETURNS bigint
- LANGUAGE plpgsql
- AS $$
-declare
- nResult bigint;
-begin
- select jl_id into nResult from jnt_letter join letter_deb using (jl_id)
where j_id = a;
- if NOT FOUND then
- select jl_id into nResult from jnt_letter join letter_cred using
(jl_id) where j_id = a;
- if NOT found then
- return null;
- end if;
- end if;
-return nResult;
-end;
-$$;
-
-
---
--- Name: get_pcm_tree(public.account_type); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION get_pcm_tree(source public.account_type) RETURNS SETOF
public.account_type
- LANGUAGE plpgsql
- AS $$
-declare
- i account_type;
- e account_type;
-begin
- for i in select pcm_val from tmp_pcmn where pcm_val_parent=source
- loop
- return next i;
- for e in select get_pcm_tree from get_pcm_tree(i)
- loop
- return next e;
- end loop;
-
- end loop;
- return;
-end;
-$$;
-
-
---
--- Name: group_analytic_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION group_analytic_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-name text;
-begin
-name:=upper(NEW.ga_id);
-name:=trim(name);
-name:=replace(name,' ','');
-NEW.ga_id:=name;
-return NEW;
-end;$$;
-
-
---
--- Name: group_analytique_del(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION group_analytique_del() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-begin
-update poste_analytique set ga_id=null
-where ga_id=OLD.ga_id;
-return OLD;
-end;$$;
-
-
---
--- Name: html_quote(text); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION html_quote(p_string text) RETURNS text
- LANGUAGE plpgsql
- AS $$
-declare
- r text;
-begin
- r:=p_string;
- r:=replace(r,'<','<');
- r:=replace(r,'>','>');
- r:=replace(r,'''','"');
- return r;
-end;$$;
-
-
---
--- Name: FUNCTION html_quote(p_string text); Type: COMMENT; Schema:
comptaproc; Owner: -
---
-
-COMMENT ON FUNCTION html_quote(p_string text) IS 'remove harmfull HTML char';
-
-
---
--- Name: info_def_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION info_def_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- row_info_def info_def%ROWTYPE;
- str_type text;
-begin
-row_info_def:=NEW;
-str_type:=upper(trim(NEW.id_type));
-str_type:=replace(str_type,' ','');
-str_type:=replace(str_type,',','');
-str_type:=replace(str_type,';','');
-if length(str_type) =0 then
- raise exception 'id_type cannot be null';
-end if;
-row_info_def.id_type:=str_type;
-return row_info_def;
-end;
-$$;
-
-
---
--- Name: insert_jrnx(character varying, numeric, public.account_type, integer,
integer, boolean, text, integer, text, text); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION insert_jrnx(p_date character varying, p_montant numeric,
p_poste public.account_type, p_grpt integer, p_jrn_def integer, p_debit
boolean, p_tech_user text, p_tech_per integer, p_qcode text, p_comment text)
RETURNS void
- LANGUAGE plpgsql
- AS $$
-begin
- insert into jrnx
- (
- j_date,
- j_montant,
- j_poste,
- j_grpt,
- j_jrn_def,
- j_debit,
- j_text,
- j_tech_user,
- j_tech_per,
- j_qcode
- ) values
- (
- to_date(p_date,'DD.MM.YYYY'),
- p_montant,
- p_poste,
- p_grpt,
- p_jrn_def,
- p_debit,
- p_comment,
- p_tech_user,
- p_tech_per,
- p_qcode
- );
-
-return;
-end;
-$$;
-
-
---
--- Name: insert_quant_purchase(text, numeric, character varying, numeric,
numeric, numeric, integer, numeric, numeric, numeric, numeric, character
varying); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION insert_quant_purchase(p_internal text, p_j_id numeric, p_fiche
character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code
integer, p_nd_amount numeric, p_nd_tva numeric, p_nd_tva_recup numeric,
p_dep_priv numeric, p_client character varying) RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- fid_client integer;
- fid_good integer;
-begin
- select f_id into fid_client from
- fiche_detail where ad_id=23 and ad_value=upper(trim(p_client));
- select f_id into fid_good from
- fiche_detail where ad_id=23 and ad_value=upper(trim(p_fiche));
- insert into quant_purchase
- (qp_internal,
- j_id,
- qp_fiche,
- qp_quantite,
- qp_price,
- qp_vat,
- qp_vat_code,
- qp_nd_amount,
- qp_nd_tva,
- qp_nd_tva_recup,
- qp_supplier,
- qp_dep_priv)
- values
- (p_internal,
- p_j_id,
- fid_good,
- p_quant,
- p_price,
- p_vat,
- p_vat_code,
- p_nd_amount,
- p_nd_tva,
- p_nd_tva_recup,
- fid_client,
- p_dep_priv);
- return;
-end;
- $$;
-
-
---
--- Name: insert_quant_sold(text, numeric, character varying, numeric, numeric,
numeric, integer, character varying); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION insert_quant_sold(p_internal text, p_jid numeric, p_fiche
character varying, p_quant numeric, p_price numeric, p_vat numeric, p_vat_code
integer, p_client character varying) RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- fid_client integer;
- fid_good integer;
-begin
-
- select f_id into fid_client from
- fiche_detail where ad_id=23 and ad_value=upper(trim(p_client));
- select f_id into fid_good from
- fiche_detail where ad_id=23 and ad_value=upper(trim(p_fiche));
- insert into quant_sold
-
(qs_internal,j_id,qs_fiche,qs_quantite,qs_price,qs_vat,qs_vat_code,qs_client,qs_valid)
- values
-
(p_internal,p_jid,fid_good,p_quant,p_price,p_vat,p_vat_code,fid_client,'Y');
- return;
-end;
- $$;
-
-
---
--- Name: insert_quick_code(integer, text); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION insert_quick_code(nf_id integer, tav_text text) RETURNS integer
- LANGUAGE plpgsql
- AS $$
- declare
- ns integer;
- nExist integer;
- tText text;
- begin
- tText := upper(trim(tav_text));
- tText := replace(tText,' ','');
-
- loop
- -- take the next sequence
- select nextval('s_jnt_fic_att_value') into ns;
- if length (tText) = 0 or tText is null then
- tText := 'FID'||ns;
- end if;
- -- av_text already used ?
- select count(*) into nExist
- from fiche_detail
- where
- ad_id=23 and ad_value=upper(tText);
-
- if nExist = 0 then
- exit;
- end if;
- tText:='FID'||ns;
- end loop;
-
-
- insert into fiche_detail(jft_id,f_id,ad_id,ad_value) values
(ns,nf_id,23,upper(tText));
- return ns;
- end;
-$$;
-
-
---
--- Name: is_closed(integer, integer); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION is_closed(p_periode integer, p_jrn_def_id integer) RETURNS
boolean
- LANGUAGE plpgsql
- AS $$
-declare
-bClosed bool;
-str_status text;
-begin
-select p_closed into bClosed from parm_periode
- where p_id=p_periode;
-
-if bClosed = true then
- return bClosed;
-end if;
-
-select status into str_status from jrn_periode
- where p_id =p_periode and jrn_def_id=p_jrn_def_id;
-
-if str_status <> 'OP' then
- return bClosed;
-end if;
-return false;
-end;
-$$;
-
-
---
--- Name: jnt_fic_attr_ins(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jnt_fic_attr_ins() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- r_record jnt_fic_attr%ROWTYPE;
- i_max integer;
-begin
-r_record=NEW;
-perform comptaproc.fiche_attribut_synchro(r_record.fd_id);
-select coalesce(max(jnt_order),0) into i_max from jnt_fic_attr where
fd_id=r_record.fd_id;
-i_max := i_max + 10;
-NEW.jnt_order=i_max;
-return NEW;
-end;
-$$;
-
-
---
--- Name: jrn_add_note(bigint, text); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION jrn_add_note(p_jrid bigint, p_note text) RETURNS void
- LANGUAGE plpgsql
- AS $$
-declare
- tmp bigint;
-begin
- if length(trim(p_note)) = 0 then
- delete from jrn_note where jr_id= p_jrid;
- return;
- end if;
-
- select n_id into tmp from jrn_note where jr_id = p_jrid;
-
- if FOUND then
- update jrn_note set n_text=trim(p_note) where jr_id = p_jrid;
- else
- insert into jrn_note (jr_id,n_text) values ( p_jrid, p_note);
-
- end if;
-
- return;
-end;
-$$;
-
-
---
--- Name: jrn_check_periode(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jrn_check_periode() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-bClosed bool;
-str_status text;
-ljr_tech_per jrn.jr_tech_per%TYPE;
-ljr_def_id jrn.jr_def_id%TYPE;
-lreturn jrn%ROWTYPE;
-begin
-if TG_OP='UPDATE' then
- ljr_tech_per :=OLD.jr_tech_per ;
- NEW.jr_tech_per :=
comptaproc.find_periode(to_char(NEW.jr_date,'DD.MM.YYYY'));
- ljr_def_id :=OLD.jr_def_id;
- lreturn :=NEW;
- if NEW.jr_date = OLD.jr_date then
- return NEW;
- end if;
- if comptaproc.is_closed(NEW.jr_tech_per,NEW.jr_def_id) = true then
- raise exception 'Periode fermee';
- end if;
-end if;
-
-if TG_OP='INSERT' then
- NEW.jr_tech_per :=
comptaproc.find_periode(to_char(NEW.jr_date,'DD.MM.YYYY'));
- ljr_tech_per :=NEW.jr_tech_per ;
- ljr_def_id :=NEW.jr_def_id;
- lreturn :=NEW;
-end if;
-
-if TG_OP='DELETE' then
- ljr_tech_per :=OLD.jr_tech_per;
- ljr_def_id :=OLD.jr_def_id;
- lreturn :=OLD;
-end if;
-
-if comptaproc.is_closed (ljr_def_id,ljr_def_id) = true then
- raise exception 'Periode fermee';
-end if;
-
-return lreturn;
-end;$$;
-
-
---
--- Name: jrn_def_add(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jrn_def_add() RETURNS trigger
- LANGUAGE plpgsql
- AS $$begin
-execute 'insert into jrn_periode(p_id,jrn_def_id,status) select
p_id,'||NEW.jrn_def_id||',
- case when p_central=true then ''CE''
- when p_closed=true then ''CL''
- else ''OP''
- end
-from
-parm_periode ';
-return NEW;
-end;$$;
-
-
---
--- Name: jrn_def_delete(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jrn_def_delete() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-nb numeric;
-begin
-select count(*) into nb from jrn where jr_def_id=OLD.jrn_def_id;
-
-if nb <> 0 then
- raise exception 'EFFACEMENT INTERDIT: JOURNAL UTILISE';
-end if;
-return OLD;
-end;$$;
-
-
---
--- Name: jrn_del(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jrn_del() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-row jrn%ROWTYPE;
-begin
-row:=OLD;
-insert into del_jrn ( jr_id,
- jr_def_id,
- jr_montant,
- jr_comment,
- jr_date,
- jr_grpt_id,
- jr_internal,
- jr_tech_date,
- jr_tech_per,
- jrn_ech,
- jr_ech,
- jr_rapt,
- jr_valid,
- jr_opid,
- jr_c_opid,
- jr_pj,
- jr_pj_name,
- jr_pj_type,
- jr_pj_number,
- del_jrn_date)
- select jr_id,
- jr_def_id,
- jr_montant,
- jr_comment,
- jr_date,
- jr_grpt_id,
- jr_internal,
- jr_tech_date,
- jr_tech_per,
- jrn_ech,
- jr_ech,
- jr_rapt,
- jr_valid,
- jr_opid,
- jr_c_opid,
- jr_pj,
- jr_pj_name,
- jr_pj_type,
- jr_pj_number
- ,now() from jrn where jr_id=row.jr_id;
-return row;
-end;
-$$;
-
-
---
--- Name: jrnx_del(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jrnx_del() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-row jrnx%ROWTYPE;
-begin
-row:=OLD;
-
-
-insert into del_jrnx(
- j_id, j_date, j_montant, j_poste, j_grpt, j_rapt, j_jrn_def,
- j_debit, j_text, j_centralized, j_internal, j_tech_user,
j_tech_date,
- j_tech_per, j_qcode, f_id) SELECT j_id, j_date, j_montant,
j_poste, j_grpt, j_rapt, j_jrn_def,
- j_debit, j_text, j_centralized, j_internal, j_tech_user, j_tech_date,
- j_tech_per, j_qcode, f_id from jrnx where j_id=row.j_id;
-return row;
-end;
-$$;
-
-
---
--- Name: jrnx_ins(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jrnx_ins() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-n_fid bigint;
-begin
-
-NEW.j_tech_per := comptaproc.find_periode(to_char(NEW.j_date,'DD.MM.YYYY'));
-if NEW.j_tech_per = -1 then
- raise exception 'Période invalide';
-end if;
-
-if NEW.j_qcode is NULL then
- return NEW;
-end if;
-
-NEW.j_qcode=trim(upper(NEW.j_qcode));
-
-if length (NEW.j_qcode) = 0 then
- NEW.j_qcode=NULL;
- else
- select f_id into n_fid from fiche_detail where ad_id=23 and
ad_value=NEW.j_qcode;
- if NOT FOUND then
- raise exception 'La fiche dont le quick code est % n''existe
pas',NEW.j_qcode;
- end if;
-end if;
-NEW.f_id:=n_fid;
-return NEW;
-end;
-$$;
-
-
---
--- Name: jrnx_letter_del(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION jrnx_letter_del() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-row jrnx%ROWTYPE;
-begin
-row:=OLD;
-delete from jnt_letter
- where (jl_id in (select jl_id from letter_deb) and jl_id not in(select
jl_id from letter_cred ))
- or (jl_id not in (select jl_id from letter_deb ) and jl_id
in(select jl_id from letter_cred ));
-return row;
-end;
-$$;
-
-
---
--- Name: plan_analytic_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION plan_analytic_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- name text;
-begin
- name:=upper(NEW.pa_name);
- name:=trim(name);
- name:=replace(name,' ','');
- NEW.pa_name:=name;
-return NEW;
-end;
-$$;
-
-
---
--- Name: poste_analytique_ins_upd(); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION poste_analytique_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$declare
-name text;
-rCount record;
-
-begin
-name:=upper(NEW.po_name);
-name:=trim(name);
-name:=replace(name,' ','');
-NEW.po_name:=name;
-
-if NEW.ga_id is NULL then
-return NEW;
-end if;
-
-if length(trim(NEW.ga_id)) = 0 then
- NEW.ga_id:=NULL;
- return NEW;
-end if;
-perform 'select ga_id from groupe_analytique where ga_id='||NEW.ga_id;
-if NOT FOUND then
- raise exception' Inexistent Group Analytic %',NEW.ga_id;
-end if;
-return NEW;
-end;$$;
-
-
---
--- Name: proc_check_balance(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION proc_check_balance() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- diff numeric;
- tt integer;
-begin
- if TG_OP = 'INSERT' or TG_OP='UPDATE' then
- tt=NEW.jr_grpt_id;
- diff:=check_balance(tt);
- if diff != 0 then
- raise exception 'balance error %',diff ;
- end if;
- return NEW;
- end if;
-end;
-$$;
-
-
---
--- Name: quant_purchase_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION quant_purchase_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
- begin
- if NEW.qp_price < 0 OR NEW.qp_quantite <0 THEN
- NEW.qp_price := abs (NEW.qp_price)*(-1);
- NEW.qp_quantite := abs (NEW.qp_quantite)*(-1);
- end if;
-return NEW;
-end;
-$$;
-
-
---
--- Name: quant_sold_ins_upd(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION quant_sold_ins_upd() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
- begin
- if NEW.qs_price < 0 OR NEW.qs_quantite <0 THEN
- NEW.qs_price := abs (NEW.qs_price)*(-1);
- NEW.qs_quantite := abs (NEW.qs_quantite)*(-1);
- end if;
-return NEW;
-end;
-$$;
-
-
---
--- Name: t_document_modele_validate(); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION t_document_modele_validate() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- lText text;
- modified document_modele%ROWTYPE;
-begin
- modified:=NEW;
-
- modified.md_filename:=replace(NEW.md_filename,' ','_');
- return modified;
-end;
-$$;
-
-
---
--- Name: t_document_type_insert(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION t_document_type_insert() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-nCounter integer;
- BEGIN
-select count(*) into nCounter from pg_class where
relname='seq_doc_type_'||NEW.dt_id;
-if nCounter = 0 then
- execute 'create sequence seq_doc_type_'||NEW.dt_id;
-end if;
- RETURN NEW;
- END;
-$$;
-
-
---
--- Name: t_document_validate(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION t_document_validate() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- lText text;
- modified document%ROWTYPE;
-begin
- modified:=NEW;
- modified.d_filename:=replace(NEW.d_filename,' ','_');
- return modified;
-end;
-$$;
-
-
---
--- Name: t_jrn_def_sequence(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION t_jrn_def_sequence() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
-nCounter integer;
-
- BEGIN
- select count(*) into nCounter
- from pg_class where relname='s_jrn_'||NEW.jrn_def_id;
- if nCounter = 0 then
- execute 'create sequence s_jrn_'||NEW.jrn_def_id;
- raise notice 'Creating sequence s_jrn_%',NEW.jrn_def_id;
- end if;
-
- RETURN NEW;
- END;
-$$;
-
-
---
--- Name: table_analytic_account(text, text); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION table_analytic_account(p_from text, p_to text) RETURNS SETOF
public.anc_table_account_type
- LANGUAGE plpgsql
- AS $$
-declare
- ret ANC_table_account_type%ROWTYPE;
- sql_from text:='';
- sql_to text:='';
- sWhere text:='';
- sAnd text:='';
- sResult text:='';
-begin
-if p_from <> '' and p_from is not null then
- sql_from:='oa_date >= to_date('''||p_from::text||''',''DD.MM.YYYY'')';
- sWhere:=' where ';
-end if;
-
-if p_to <> '' and p_to is not null then
- sql_to=' oa_date <= to_date('''||p_to::text||''',''DD.MM.YYYY'')';
- sWhere := ' where ';
-end if;
-
-if sql_to <> '' and sql_from <> '' then
- sAnd:=' and ';
-end if;
-
-sResult := sWhere || sql_from || sAnd || sql_to;
-
-for ret in EXECUTE 'SELECT po.po_id,
- po.pa_id, po.po_name,
- po.po_description,sum(
- CASE
- WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
- ELSE operation_analytique.oa_amount
- END) AS sum_amount, jrnx.j_poste, tmp_pcmn.pcm_lib AS name
- FROM operation_analytique
- JOIN poste_analytique po USING (po_id)
- JOIN jrnx USING (j_id)
- JOIN tmp_pcmn ON jrnx.j_poste::text = tmp_pcmn.pcm_val::text
-'|| sResult ||'
- GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.j_poste, tmp_pcmn.pcm_lib,
po.po_description
- HAVING sum(
-CASE
- WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
- ELSE operation_analytique.oa_amount
-END) <> 0::numeric '
- loop
- return next ret;
-end loop;
-end;
-$$;
-
-
---
--- Name: table_analytic_card(text, text); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION table_analytic_card(p_from text, p_to text) RETURNS SETOF
public.anc_table_card_type
- LANGUAGE plpgsql
- AS $$
-declare
- ret ANC_table_card_type%ROWTYPE;
- sql_from text:='';
- sql_to text:='';
- sWhere text:='';
- sAnd text:='';
- sResult text:='';
-begin
-if p_from <> '' and p_from is not null then
- sql_from:='oa_date >= to_date('''||p_from::text||''',''DD.MM.YYYY'')';
- sWhere:=' where ';
-end if;
-
-if p_to <> '' and p_to is not null then
- sql_to=' oa_date <= to_date('''||p_to::text||''',''DD.MM.YYYY'')';
- sWhere := ' where ';
-end if;
-
-if sql_to <> '' and sql_from <> '' then
- sAnd :=' and ';
-end if;
-
-sResult := sWhere || sql_from || sAnd || sql_to;
-
-for ret in EXECUTE ' SELECT po.po_id, po.pa_id, po.po_name, po.po_description,
sum(
- CASE
- WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
- ELSE operation_analytique.oa_amount
- END) AS sum_amount, jrnx.f_id, jrnx.j_qcode, ( SELECT
fiche_detail.ad_value
- FROM fiche_detail
- WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id) AS
name
- FROM operation_analytique
- JOIN poste_analytique po USING (po_id)
- JOIN jrnx USING (j_id)'|| sResult ||'
- GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.f_id, jrnx.j_qcode, ( SELECT
fiche_detail.ad_value
- FROM fiche_detail
- WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id),
po.po_description
- HAVING sum(
-CASE
- WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
- ELSE operation_analytique.oa_amount
-END) <> 0::numeric;'
-
-
- loop
- return next ret;
-end loop;
-end;
-$$;
-
-
---
--- Name: tmp_pcmn_ins(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION tmp_pcmn_ins() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- r_record tmp_pcmn%ROWTYPE;
-begin
-r_record=NEW;
-if length(trim(r_record.pcm_type))=0 or r_record.pcm_type is NULL then
- r_record.pcm_type:=find_pcm_type(NEW.pcm_val);
- return r_record;
-end if;
-return NEW;
-end;
-$$;
-
-
---
--- Name: trim_cvs_quote(); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION trim_cvs_quote() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- modified import_tmp%ROWTYPE;
-begin
- modified:=NEW;
- modified.devise=replace(new.devise,'"','');
- modified.poste_comptable=replace(new.poste_comptable,'"','');
- modified.compte_ordre=replace(NEW.COMPTE_ORDRE,'"','');
- modified.detail=replace(NEW.DETAIL,'"','');
- modified.num_compte=replace(NEW.NUM_COMPTE,'"','');
- return modified;
-end;
-$$;
-
-
---
--- Name: trim_space_format_csv_banque(); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION trim_space_format_csv_banque() RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-declare
- modified format_csv_banque%ROWTYPE;
-begin
- modified.name=trim(NEW.NAME);
- modified.include_file=trim(new.include_file);
- if ( length(modified.name) = 0 ) then
- modified.name=null;
- end if;
- if ( length(modified.include_file) = 0 ) then
- modified.include_file=null;
- end if;
-
- return modified;
-end;
-$$;
-
-
---
--- Name: tva_delete(integer); Type: FUNCTION; Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION tva_delete(integer) RETURNS void
- LANGUAGE plpgsql
- AS $_$
-declare
- p_tva_id alias for $1;
- nCount integer;
-begin
- nCount=0;
- select count(*) into nCount from quant_sold where qs_vat_code=p_tva_id;
- if nCount != 0 then
- return;
-
- end if;
- select count(*) into nCount from quant_purchase where
qp_vat_code=p_tva_id;
- if nCount != 0 then
- return;
-
- end if;
-
-delete from tva_rate where tva_id=p_tva_id;
- return;
-end;
-$_$;
-
-
---
--- Name: tva_insert(text, numeric, text, text); Type: FUNCTION; Schema:
comptaproc; Owner: -
---
-
-CREATE FUNCTION tva_insert(text, numeric, text, text) RETURNS integer
- LANGUAGE plpgsql
- AS $_$
-declare
- l_tva_id integer;
- p_tva_label alias for $1;
- p_tva_rate alias for $2;
- p_tva_comment alias for $3;
- p_tva_poste alias for $4;
- debit text;
- credit text;
- nCount integer;
-begin
-if length(trim(p_tva_label)) = 0 then
- return 3;
-end if;
-
-if length(trim(p_tva_poste)) != 0 then
- if position (',' in p_tva_poste) = 0 then return 4; end if;
- debit = split_part(p_tva_poste,',',1);
- credit = split_part(p_tva_poste,',',2);
- select count(*) into nCount from tmp_pcmn where
pcm_val=debit::account_type;
- if nCount = 0 then return 4; end if;
- select count(*) into nCount from tmp_pcmn where
pcm_val=credit::account_type;
- if nCount = 0 then return 4; end if;
-
-end if;
-select into l_tva_id nextval('s_tva') ;
-insert into tva_rate(tva_id,tva_label,tva_rate,tva_comment,tva_poste)
- values (l_tva_id,p_tva_label,p_tva_rate,p_tva_comment,p_tva_poste);
-return 0;
-end;
-$_$;
-
-
---
--- Name: tva_modify(integer, text, numeric, text, text); Type: FUNCTION;
Schema: comptaproc; Owner: -
---
-
-CREATE FUNCTION tva_modify(integer, text, numeric, text, text) RETURNS integer
- LANGUAGE plpgsql
- AS $_$
-declare
- p_tva_id alias for $1;
- p_tva_label alias for $2;
- p_tva_rate alias for $3;
- p_tva_comment alias for $4;
- p_tva_poste alias for $5;
- debit text;
- credit text;
- nCount integer;
-begin
-if length(trim(p_tva_label)) = 0 then
- return 3;
-end if;
-
-if length(trim(p_tva_poste)) != 0 then
- if position (',' in p_tva_poste) = 0 then return 4; end if;
- debit = split_part(p_tva_poste,',',1);
- credit = split_part(p_tva_poste,',',2);
- select count(*) into nCount from tmp_pcmn where
pcm_val=debit::account_type;
- if nCount = 0 then return 4; end if;
- select count(*) into nCount from tmp_pcmn where
pcm_val=credit::account_type;
- if nCount = 0 then return 4; end if;
-
-end if;
-update tva_rate set
tva_label=p_tva_label,tva_rate=p_tva_rate,tva_comment=p_tva_comment,tva_poste=p_tva_poste
- where tva_id=p_tva_id;
-return 0;
-end;
-$_$;
-
-
---
--- Name: update_quick_code(integer, text); Type: FUNCTION; Schema: comptaproc;
Owner: -
---
-
-CREATE FUNCTION update_quick_code(njft_id integer, tav_text text) RETURNS
integer
- LANGUAGE plpgsql
- AS $$
- declare
- ns integer;
- nExist integer;
- tText text;
- old_qcode varchar;
- begin
- -- get current value
- select ad_value into old_qcode from fiche_detail where jft_id=njft_id;
- -- av_text didn't change so no update
- if tav_text = upper( trim(old_qcode)) then
- return 0;
- end if;
-
- tText := trim(upper(tav_text));
- tText := replace(tText,' ','');
- if length ( tText) = 0 or tText is null then
- return 0;
- end if;
-
- ns := njft_id;
-
- loop
- -- av_text already used ?
- select count(*) into nExist
- from fiche_detail
- where
- ad_id=23 and ad_value=tText;
-
- if nExist = 0 then
- exit;
- end if;
- if tText = 'FID'||ns then
- -- take the next sequence
- select nextval('s_jnt_fic_att_value') into ns;
- end if;
- tText :='FID'||ns;
-
- end loop;
- update fiche_detail set ad_value = tText where jft_id=njft_id;
-
- -- update also the contact
- update fiche_detail set ad_value = tText
- where jft_id in
- ( select jft_id
- from fiche_detail
- where ad_id=25 and ad_value=old_qcode);
-
-
- update jrnx set j_qcode=tText where j_qcode = old_qcode;
- return ns;
- end;
-$$;
-
-
---
--- PostgreSQL database dump complete
---
-
Deleted: phpcompta/trunk/sql/correct-quant.sql
===================================================================
--- phpcompta/trunk/sql/correct-quant.sql 2011-11-22 22:44:52 UTC (rev
4446)
+++ phpcompta/trunk/sql/correct-quant.sql 2011-11-22 22:50:44 UTC (rev
4447)
@@ -1,54 +0,0 @@
-CREATE OR REPLACE FUNCTION correct_quant_purchase() returns void
-as
-$BODY$
-declare
- r_invalid quant_purchase;
- s_QuickCode text;
- b_j_debit bool;
- r_new record;
- r_jrnx record;
-begin
-
-for r_invalid in select * from quant_purchase where qp_valid='A'
-loop
-
--- get qcode
-select j_qcode into s_QuickCode from vw_poste_qcode where
f_id=r_invalid.qp_fiche;
-raise notice 'qp_id % Quick code is %',r_invalid.qp_id,s_QuickCode;
-
--- get deb or cred
-select j_debit,j_grpt,j_jrn_def,j_montant into r_jrnx from jrnx where
j_id=r_invalid.j_id;
-if NOT FOUND then
- raise notice 'error not found jrnx %',r_invalid.j_id;
- update quant_purchase set qp_valid='Y' where qp_id=r_invalid.qp_id;
- continue;
-end if;
-raise notice 'j_debit % , j_grpt % ,j_jrn_def % qp_price
%',r_jrnx.j_debit,r_jrnx.j_grpt,r_jrnx.j_jrn_def ,r_invalid.qp_price;
-
-select jr_internal,j_id,j_montant into r_new
- from jrnx join jrn on (j_grpt=jr_grpt_id)
- where
- j_jrn_def=r_jrnx.j_jrn_def
- and j_id not in (select j_id from quant_purchase)
- and j_qcode=s_QuickCode
- and j_montant=r_jrnx.j_montant
- and j_debit != r_jrnx.j_debit;
-
-if NOT FOUND then
- raise notice 'error not found %', r_invalid.j_id;
- update quant_purchase set qp_valid='Y' where qp_id=r_invalid.qp_id;
- continue;
-end if;
-raise notice 'j_id % found amount %',r_new.j_id,r_new.j_montant;
-
--- insert into quant_purchase
-insert into quant_purchase
(qp_internal,j_id,qp_fiche,qp_quantite,qp_price,qp_vat,qp_nd_amount,qp_nd_tva_recup,qp_valid,qp_dep_priv,qp_supplier,qp_vat_code)
-values
(r_new.jr_internal,r_invalid.j_id,r_invalid.qp_fiche,(r_invalid.qp_quantite *
(-1)),r_invalid.qp_price *
(-1),r_invalid.qp_vat*(-1),r_invalid.qp_nd_amount*(-1),r_invalid.qp_nd_tva_recup*(-1)
,'Y',r_invalid.qp_dep_priv*(-1),r_invalid.qp_supplier,r_invalid.qp_vat_code);
-
-update quant_purchase set qp_valid='Y' where qp_id=r_invalid.qp_id;
-end loop;
-return;
-end;
-$BODY$
- LANGUAGE 'plpgsql' VOLATILE;
-
Deleted: phpcompta/trunk/sql/correct_quant_sold.sql
===================================================================
--- phpcompta/trunk/sql/correct_quant_sold.sql 2011-11-22 22:44:52 UTC (rev
4446)
+++ phpcompta/trunk/sql/correct_quant_sold.sql 2011-11-22 22:50:44 UTC (rev
4447)
@@ -1,53 +0,0 @@
-CREATE OR REPLACE FUNCTION correct_quant_sale() returns void
-as
-$BODY$
-declare
- r_invalid quant_sold;
- s_QuickCode text;
- b_j_debit bool;
- r_new record;
- r_jrnx record;
-begin
-
-for r_invalid in select * from quant_sold where qs_valid='A'
-loop
-
--- get qcode
-select j_qcode into s_QuickCode from vw_poste_qcode where
f_id=r_invalid.qs_fiche;
-raise notice 'qp_id % Quick code is %',r_invalid.qs_id,s_QuickCode;
-
--- get deb or cred
-select j_debit,j_grpt,j_jrn_def,j_montant into r_jrnx from jrnx where
j_id=r_invalid.j_id;
-if NOT FOUND then
- update quant_sold set qs_valid='Y' where qs_id=r_invalid.qs_id;
- raise notice 'error not found jrnx %',r_invalid.j_id;
- continue;
-end if;
-raise notice 'j_debit % , j_grpt % ,j_jrn_def % qs_price
%',r_jrnx.j_debit,r_jrnx.j_grpt,r_jrnx.j_jrn_def ,r_invalid.qs_price;
-
-select jr_internal,j_id,j_montant into r_new
- from jrnx join jrn on (j_grpt=jr_grpt_id)
- where
- j_jrn_def=r_jrnx.j_jrn_def
- and j_id not in (select j_id from quant_sold)
- and j_qcode=s_QuickCode
- and j_montant=r_jrnx.j_montant
- and j_debit != r_jrnx.j_debit;
-
-if NOT FOUND then
- update quant_sold set qs_valid='Y' where qs_id=r_invalid.qs_id;
- raise notice 'error not found %', r_invalid.j_id;
- continue;
-end if;
-raise notice 'j_id % found amount %',r_new.j_id,r_new.j_montant;
-
--- insert into quant_sold
-
- insert into quant_sold
(qs_internal,j_id,qs_fiche,qs_quantite,qs_price,qs_vat,qs_valid,qs_client,qs_vat_code)
- values
(r_new.jr_internal,r_invalid.j_id,r_invalid.qs_fiche,(r_invalid.qs_quantite *
(-1)),r_invalid.qs_price *
(-1),r_invalid.qs_vat*(-1),'Y',r_invalid.qs_client,r_invalid.qs_vat_code);
- update quant_sold set qs_valid='Y' where qs_id=r_invalid.qs_id;
-end loop;
-return;
-end;
-$BODY$
- LANGUAGE 'plpgsql' VOLATILE;
\ No newline at end of file
Deleted: phpcompta/trunk/sql/downgrade.sql
===================================================================
Modified: phpcompta/trunk/sql/upgrade.sql
===================================================================
--- phpcompta/trunk/sql/upgrade.sql 2011-11-22 22:44:52 UTC (rev 4446)
+++ phpcompta/trunk/sql/upgrade.sql 2011-11-22 22:50:44 UTC (rev 4447)
@@ -17,7 +17,6 @@
\i account_compute.sql
\i account_insert.sql
\i account-update.sql
--- \i change-pcmn-to-alphanum.sql
\i format_account.sql
\i tmp_pcmn_alphanum_ins_upd.sql
\i tmp_pcmn_ins.sql
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r4447 - phpcompta/trunk/sql,
phpcompta-dev <=