[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r4226 - in phpcompta/trunk: html include sql
|
From: |
phpcompta-dev |
|
Subject: |
[Phpcompta-dev] r4226 - in phpcompta/trunk: html include sql |
|
Date: |
Sat, 22 Oct 2011 22:17:51 +0200 (CEST) |
Author: danydb
Date: 2011-10-22 22:17:50 +0200 (Sat, 22 Oct 2011)
New Revision: 4226
Added:
phpcompta/trunk/sql/account-update.sql
phpcompta/trunk/sql/account_alphanum.sql
phpcompta/trunk/sql/account_compute.sql
phpcompta/trunk/sql/account_insert.sql
phpcompta/trunk/sql/format_account.sql
phpcompta/trunk/sql/tmp_pcmn_alphanum_ins_upd.sql
phpcompta/trunk/sql/trigger.tmp_pcmn.sql
Modified:
phpcompta/trunk/html/parametre.php
phpcompta/trunk/include/class_acc_account_ledger.php
phpcompta/trunk/include/class_fiche.php
phpcompta/trunk/include/class_own.php
phpcompta/trunk/include/impress_poste.inc.php
phpcompta/trunk/sql/
phpcompta/trunk/sql/upgrade.sql
Log:
#120 : Support for alphanumeric account
Modified: phpcompta/trunk/html/parametre.php
===================================================================
--- phpcompta/trunk/html/parametre.php 2011-10-22 20:16:44 UTC (rev 4225)
+++ phpcompta/trunk/html/parametre.php 2011-10-22 20:17:50 UTC (rev 4226)
@@ -258,6 +258,7 @@
if ( $User->check_action(PARSTR)!=0) $m->MY_STRICT=$p_strict;
if ( $User->check_action(PARTVA)!=0)$m->MY_TVA_USE=$p_tva_use;
$m->MY_PJ_SUGGEST=$p_pj;
+ $m->MY_ALPHANUM=$p_alphanum;
$m->Update();
}
@@ -272,6 +273,8 @@
array('value'=>'N','label'=>_('Non')),
array('value'=>'Y','label'=>_('Oui'))
);
+ $alpha_num_array[0]=array('value'=>'N','label'=>_('Non'));
+ $alpha_num_array[1]=array('value'=>'Y','label'=>_('Oui'));
$compta=new ISelect();
$compta->table=1;
@@ -297,6 +300,11 @@
$check_periode->table=1;
$check_periode->selected=$my->MY_CHECK_PERIODE;
+ $alpha_num=new ISelect();
+ $alpha_num->table=1;
+ $alpha_num->value=$alpha_num_array;
+ $alpha_num->selected=$my->MY_ALPHANUM;
+
// other parameters
$all=new IText();
$all->table=1;
@@ -329,6 +337,7 @@
echo "<tr>".td(_("Suggérer le numéro de pièce
justificative"),'style="text-align:right"').$pj_suggest->input("p_pj",$strict_array)."</tr>";
echo "<tr>".td(_("Suggérer la
date"),'style="text-align:right"').$date_suggest->input("p_date_suggest",$strict_array)."</tr>";
echo '<tr>'.td(_('Afficher la période comptable pour éviter les erreurs de
date'),'style="text-align:right"').$check_periode->input('p_check_periode',$strict_array).'</tr>';
+ echo '<tr>'.td(_('Utilisez des postes comptables
alphanumérique'),'style="text-align:right"').$alpha_num->input('p_alphanum').'</tr>';
echo "</table>";
echo HtmlInput::submit("record_company",_("Sauve"));
echo "</form>";
Modified: phpcompta/trunk/include/class_acc_account_ledger.php
===================================================================
--- phpcompta/trunk/include/class_acc_account_ledger.php 2011-10-22
20:16:44 UTC (rev 4225)
+++ phpcompta/trunk/include/class_acc_account_ledger.php 2011-10-22
20:17:50 UTC (rev 4226)
@@ -129,36 +129,36 @@
$filter=str_replace('jrn_def_id','jr_def_id',$filter_sql);
$bal_sql="select sum(amount_deb) as s_deb,sum(amount_cred) as
s_cred, j_poste from (select case when
j_debit='t' then j_montant else 0 end as amount_deb,
case when
j_debit='f' then j_montant else 0 end as amount_cred,
- j_poste
+ j_poste
from jrnx join
jrn on (j_grpt = jr_grpt_id)
- where
+ where
j_poste=$1 and
$filter and
- (
to_date($2,'DD.MM.YYYY') <= j_date and
+ (
to_date($2,'DD.MM.YYYY') <= j_date and
to_date($3,'DD.MM.YYYY') >= j_date )) as
signed_amount
group by j_poste
";
$r=$this->db->get_array($bal_sql,array($this->id,$p_from,$p_to));
if ( $this->db->count() == 0 ) return array();
- if ($r[0]['s_deb']==$r[0]['s_cred']) return array();
+ if ($r[0]['s_deb']==$r[0]['s_cred']) return array();
}
- $Res=$this->db->exec_sql("select jr_id,to_char(j_date,'DD.MM.YYYY')
as j_date_fmt,j_date,".
- "case when j_debit='t' then j_montant else 0
end as deb_montant,".
- "case when j_debit='f' then j_montant else 0
end as cred_montant,".
- " jr_comment as description,jrn_def_name as
jrn_name,".
- "j_debit, jr_internal,jr_pj_number,
-
coalesce(comptaproc.get_letter_jnt(j_id),-1) as letter ".
- ",pcm_lib ".
- ",jr_tech_per,p_exercice ".
- " from jrnx left join jrn_def on
(jrn_def_id=j_jrn_def )".
- " left join jrn on (jr_grpt_id=j_grpt)".
- " left join tmp_pcmn on (j_poste=pcm_val)".
- " left join parm_periode on (p_id=jr_tech_per)
".
- " where j_poste=$1 and ".
- " ( to_date($2,'DD.MM.YYYY') <= j_date and ".
- " to_date($3,'DD.MM.YYYY') >= j_date )".
- " and $filter_sql $sql_let ".
- " order by
j_date,substring(jr_pj_number,'\\\\d+$') asc",array($this->id,$p_from,$p_to));
+ $Res=$this->db->exec_sql("select jr_id,to_char(j_date,'DDMMYYYY') as
j_date_fmt,j_date,
+ case when j_debit='t' then j_montant else 0
end as deb_montant,
+ case when j_debit='f' then j_montant else 0
end as cred_montant,
+ jr_comment as description,jrn_def_name as
jrn_name,
+ j_debit, jr_internal,jr_pj_number,
+ coalesce(get_letter_jnt(j_id),-1) as letter
+ ,pcm_lib
+ ,jr_tech_per,p_exercice
+ from jrnx left join jrn_def on
(jrn_def_id=j_jrn_def )
+ left join jrn on (jr_grpt_id=j_grpt)
+ left join tmp_pcmn on (j_poste=pcm_val)
+ left join parm_periode on (p_id=jr_tech_per)
+ where j_poste=$1 and
+ ( to_date($2,'DD.MM.YYYY') <= j_date and
+ to_date($3,'DD.MM.YYYY') >= j_date )
+ and $filter_sql $sql_let
+ order by
j_date,substring(jr_pj_number,'\\\\d+$') asc",array($this->id,$p_from,$p_to));
return $this->get_row_sql($Res);
}
@@ -240,7 +240,7 @@
if ($Max==0) return 0;
$r=Database::fetch_array($Res,0);
- return abs($r['sum_deb']-$r['sum_cred']);
+ return abs(bcsub($r['sum_deb'],$r['sum_cred']));
}
/*!
* \brief give the balance of an account
@@ -311,7 +311,7 @@
}
/*!
* \brief HtmlTable, display a HTML of a poste for the asked period
- * \param $p_array array for filter
+ * \param $p_array array for filter
* \param $let lettering of operation 0
* \return -1 if nothing is found otherwise 0
*/
@@ -381,7 +381,7 @@
$progress=bcadd($progress,$tmp_diff);
$sum_cred=bcadd($sum_cred,$op['cred_montant']);
$sum_deb=bcadd($sum_deb,$op['deb_montant']);
-
+
echo "<TR>".
"<TD>".format_date($op['j_date'])."</TD>".
td(h($op['jr_pj_number'])).
@@ -390,7 +390,7 @@
"<TD style=\"text-align:right\">".nbm($op['deb_montant'])."</TD>".
"<TD
style=\"text-align:right\">".nbm($op['cred_montant'])."</TD>".
td(nbm(abs($progress)),'style="text-align:right"').
-
+
td($let,' style="color:red;text-align:right"').
"</TR>";
$old_exercice=$op['p_exercice'];
@@ -439,9 +439,9 @@
default:
throw new Exception(" Fonction HtmlTableHeader argument actiontarget
invalid");
exit;
- }
+ }
$hid=new IHidden();
-
+
echo "<table >";
echo '<TR>';
$str_ople=(isset($_REQUEST['ople']))?HtmlInput::hidden('ople',$_REQUEST['ople']):'';
@@ -464,18 +464,12 @@
$hid->input("to_periode",$_REQUEST['to_periode'])
;
+ echo HtmlInput::request_to_hidden(array('from_poste','to_poste',
+ 'poste_id'));
+
if ( isset($_REQUEST['letter'] )) echo HtmlInput::hidden('letter','2');
if ( isset($_REQUEST['solded'] )) echo HtmlInput::hidden('solded','1');
- if (isset($_REQUEST['from_poste']))
- echo HtmlInput::hidden('from_poste',$_REQUEST['from_poste']);
-
- if (isset($_REQUEST['to_poste']))
- echo HtmlInput::hidden('to_poste',$_REQUEST['to_poste']);
-
- if (isset($_REQUEST['poste_id']))
- echo HtmlInput::hidden("poste_id",$_REQUEST['poste_id']);
-
if (isset($_REQUEST['poste_fille']))
echo $hid->input('poste_fille','on');
if (isset($_REQUEST['oper_detail']))
@@ -492,15 +486,9 @@
$hid->input("from_periode",$_REQUEST['from_periode']).
$hid->input("to_periode",$_REQUEST['to_periode']);
- if (isset($_REQUEST['from_poste']))
- echo HtmlInput::hidden('from_poste',$_REQUEST['from_poste']);
+ echo HtmlInput::request_to_hidden(array('from_poste','to_poste',
+ 'poste_id'));
- if (isset($_REQUEST['to_poste']))
- echo HtmlInput::hidden('to_poste',$_REQUEST['to_poste']);
-
- if (isset($_REQUEST['poste_id']))
- echo HtmlInput::hidden("poste_id",$_REQUEST['poste_id']);
-
if ( isset($_REQUEST['letter'] )) echo HtmlInput::hidden('letter','2');
if ( isset($_REQUEST['solded'] )) echo HtmlInput::hidden('solded','1');
@@ -513,8 +501,8 @@
echo "</form></TD>";
echo '</tr>';
echo "</table>";
-
+
}
/*!
* \brief verify that the poste belong to a ledger
@@ -523,7 +511,9 @@
*/
function belong_ledger($p_jrn)
{
- $filter=$this->db->get_value("select jrn_def_class_cred from jrn_def
where jrn_def_id=$p_jrn");
+ $filter=$this->db->get_value("select jrn_def_class_cred from jrn_def
where jrn_def_id=$1",
+ array($p_jrn));
+
if ( trim ($filter) == '')
return 0;
Modified: phpcompta/trunk/include/class_fiche.php
===================================================================
--- phpcompta/trunk/include/class_fiche.php 2011-10-22 20:16:44 UTC (rev
4225)
+++ phpcompta/trunk/include/class_fiche.php 2011-10-22 20:17:50 UTC (rev
4226)
@@ -71,7 +71,7 @@
*/
function get_bk_account()
{
-
+
$user=new User($this->cn);
$sql_ledger=$user->get_ledger_sql('FIN',3);
$avail=$this->cn->get_array("select jrn_def_id,jrn_def_bank from jrn_def
where jrn_def_type='FIN' and $sql_ledger
@@ -105,7 +105,7 @@
if ( $p_qcode == null )
$p_qcode=$this->quick_code;
$p_qcode=trim($p_qcode);
- $sql="select f_id from fiche_detail
+ $sql="select f_id from fiche_detail
where ad_id=23 and ad_value=upper($1)";
$this->id=$this->cn->get_value($sql,array($p_qcode));
if ( $this->cn->count()==0)
@@ -149,8 +149,8 @@
return;
}
$sql="select *
- from
- fiche
+ from
+ fiche
natural join fiche_detail
join jnt_fic_attr on (jnt_fic_attr.fd_id=fiche.fd_id and
fiche_detail.ad_id=jnt_fic_attr.ad_id)
join attr_def on (attr_def.ad_id=fiche_detail.ad_id) where
f_id=".$this->id.
@@ -214,7 +214,7 @@
*/
function seek($p_attribut,$p_value)
{
- $sql="select jft_id,f_id,fd_id,ad_id,ad_value from fiche join
fiche_detail using (f_id)
+ $sql="select jft_id,f_id,fd_id,ad_id,ad_value from fiche join
fiche_detail using (f_id)
where ad_id=$1 and upper(ad_value)=upper($2)";
$res=$this->cn->get_array($sql,array($p_attribut,$p_value));
return $res;
@@ -662,7 +662,7 @@
$msg=$w->search();
$msg.=$label->input();
break;
-
+
default:
var_dump($r);
throw new Exception("Type invalide");
@@ -732,7 +732,7 @@
list ($id) = sscanf ($name,"av_text%d");
if ( $id == null ) continue;
-
+
// Special traitement
// quickcode
if ( $id == ATTR_DEF_QUICKCODE)
@@ -833,7 +833,7 @@
foreach ($p_array as $name=>$value )
{
if ( preg_match('/^av_text[0-9]+$/',$name) == 0) continue;
-
+
list ($id) = sscanf ($name,"av_text%d");
if ( $id == null ) continue;
@@ -905,8 +905,13 @@
if ( $id == ATTR_DEF_ACCOUNT )
{
$v=FormatString($value);
- if ( isNumber($v) == 1 || strpos($v,',') != 0 )
+ if ( trim($v) != '' )
{
+ if ( strpos($v,',') != 0)
+ {
+ $v=$this->cn->get_value('select
format_account($1)',
+ array($v));
+ }
$sql=sprintf("select account_update(%d,'%s')",
$this->id,$v);
try
@@ -928,14 +933,6 @@
try
{
$Ret=$this->cn->exec_sql($sql);
- /* update also the jrnx */
-
- /* The jrnx CANNOT BE UPDATED
- $sql='update jrnx set j_poste=$1
where j_qcode in (select quick_code from vw_fiche_attr where f_id=$2)';
- $this->cn->exec_sql(
- $sql,
- array($v,$this->id));
- */
}
catch (Exception $e)
{
@@ -1248,7 +1245,7 @@
* \brief HtmlTable, display a HTML of a card for the asked period
* \param $p_array default = null keys = from_periode, to_periode
*\param $op_let 0 all operation, 1 only lettered one, 2 only unlettered
one
- *\return -1 if nothing is found otherwise 0
+ *\return -1 if nothing is found otherwise 0
*\see get_row_date
*/
function HtmlTable($p_array=null,$op_let=0,$from_div=1)
@@ -1461,7 +1458,7 @@
( select j_poste,
case when j_debit='t' then j_montant else 0
end as deb,
case when j_debit='f' then j_montant else 0
end as cred
- from jrnx
+ from jrnx
join jrn on (jr_grpt_id=j_grpt)
where
j_qcode = ('$qcode'::text)
@@ -1476,7 +1473,7 @@
return array('debit'=>$r['sum_deb'],
'credit'=>$r['sum_cred'],
'solde'=>abs($r['sum_deb']-$r['sum_cred']));
-
+
}
/*!\brief check if an attribute is empty
*\param $p_attr the id of the attribut to check (ad_id)
@@ -1589,8 +1586,8 @@
$r.='<TD align="right">
'.(($amount['debit']==0)?0:nbm($amount['debit'])).'€</TD>';
$r.='<TD align="right">
'.(($amount['credit']==0)?0:nbm($amount['credit'])).'€</TD>';
$r.='<TD align="right"> '.nbm($amount['solde'])."€</TD>";
-
+
$r.="</TR>";
}
@@ -1747,7 +1744,7 @@
{
// Remove from attr_value
$Res=$this->cn->exec_sql("delete from fiche_detail
- where
+ where
f_id=".$this->id);
// Remove from fiche
Modified: phpcompta/trunk/include/class_own.php
===================================================================
--- phpcompta/trunk/include/class_own.php 2011-10-22 20:16:44 UTC (rev
4225)
+++ phpcompta/trunk/include/class_own.php 2011-10-22 20:17:50 UTC (rev
4226)
@@ -54,10 +54,10 @@
/*!
**************************************************
* \brief save the parameter into the database by inserting or updating
- *
- *
+ *
+ *
* \param $p_attr give the attribut name
- *
+ *
*/
function save($p_attr)
{
@@ -82,8 +82,8 @@
/*!
**************************************************
* \brief save data
- *
*
+ *
*/
function update()
{
@@ -103,6 +103,7 @@
$this->save('MY_PJ_SUGGEST');
$this->save('MY_CHECK_PERIODE');
$this->save('MY_DATE_SUGGEST');
+ $this->save('MY_ALPHANUM');
}
Modified: phpcompta/trunk/include/impress_poste.inc.php
===================================================================
--- phpcompta/trunk/include/impress_poste.inc.php 2011-10-22 20:16:44 UTC
(rev 4225)
+++ phpcompta/trunk/include/impress_poste.inc.php 2011-10-22 20:17:50 UTC
(rev 4226)
@@ -28,7 +28,7 @@
* file included from user_impress
*
* some variable are already defined $cn, $User ...
- *
+ *
*/
//-----------------------------------------------------
// Show the jrn and date
@@ -138,18 +138,19 @@
require_once("class_acc_account_ledger.php");
$go=0;
// we ask a poste_id
- if ( isset($_GET['poste_id']) && strlen(trim($_GET['poste_id'])) != 0 &&
isNumber($_GET['poste_id']) )
+ if ( isset($_GET['poste_id']) && strlen(trim($_GET['poste_id'])) != 0 )
{
+ $poste=$cn->get_value('select
format_account($1)',array($_GET['poste_id']));
+
if ( isset ($_GET['poste_fille']) )
{
- $parent=$_GET['poste_id'];
- $a_poste=$cn->get_array("select pcm_val from tmp_pcmn where
pcm_val::text like '$parent%' order by pcm_val::text");
+ $a_poste=$cn->get_array("select pcm_val from tmp_pcmn where
pcm_val::text like $1||'%' order by pcm_val::text",array($poste));
$go=3;
}
// Check if the post is numeric and exists
- elseif ( $cn->count_sql('select * from tmp_pcmn where
pcm_val=$1',array($_GET['poste_id'])) != 0 )
+ elseif ( $cn->count_sql('select * from tmp_pcmn where
pcm_val=$1',array($poste)) != 0 )
{
- $Poste=new Acc_Account_Ledger($cn,$_GET['poste_id']);
+ $Poste=new Acc_Account_Ledger($cn,$poste);
$go=1;
}
}
Property changes on: phpcompta/trunk/sql
___________________________________________________________________
Name: svn:ignore
+ .upgrade.sql.swp
Added: phpcompta/trunk/sql/account-update.sql
===================================================================
--- phpcompta/trunk/sql/account-update.sql (rev 0)
+++ phpcompta/trunk/sql/account-update.sql 2011-10-22 20:17:50 UTC (rev
4226)
@@ -0,0 +1,55 @@
+-- Function: comptaproc.account_update(integer, account_type)
+
+-- DROP FUNCTION comptaproc.account_update(integer, account_type);
+
+CREATE OR REPLACE FUNCTION comptaproc.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;
+ first text;
+ second text;
+begin
+
+ if length(trim(p_account)) != 0 then
+ -- 2 accounts in card separated by comma
+ 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;
+ -- check that both account are in PCMN
+
+ end if;
+ else
+ -- account is null
+ update fiche_detail set ad_value=null where f_id=p_f_id and
ad_id=5 ;
+ end if;
+
+ update fiche_detail set ad_value=p_account where f_id=p_f_id and
ad_id=5 ;
+
+return 0;
+end;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION comptaproc.account_update(integer, account_type) OWNER TO dany;
Added: phpcompta/trunk/sql/account_alphanum.sql
===================================================================
--- phpcompta/trunk/sql/account_alphanum.sql (rev 0)
+++ phpcompta/trunk/sql/account_alphanum.sql 2011-10-22 20:17:50 UTC (rev
4226)
@@ -0,0 +1,21 @@
+-- Function: comptaproc.account_auto(integer)
+
+-- DROP FUNCTION comptaproc.account_auto(integer);
+
+CREATE OR REPLACE FUNCTION comptaproc.account_alphanum()
+ RETURNS boolean AS
+$BODY$
+declare
+ l_auto bool;
+begin
+ l_auto := true;
+ select pr_value into l_auto from parameter where pr_id='MY_ALPHANUM';
+ if l_auto = 'N' or l_auto is null then
+ l_auto:=false;
+ end if;
+ return l_auto;
+end;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION comptaproc.account_auto(integer) OWNER TO dany;
Added: phpcompta/trunk/sql/account_compute.sql
===================================================================
--- phpcompta/trunk/sql/account_compute.sql (rev 0)
+++ phpcompta/trunk/sql/account_compute.sql 2011-10-22 20:17:50 UTC (rev
4226)
@@ -0,0 +1,48 @@
+-- Function: comptaproc.account_compute(integer)
+
+-- DROP FUNCTION comptaproc.account_compute(integer);
+
+CREATE OR REPLACE FUNCTION comptaproc.account_compute(p_f_id integer)
+ RETURNS account_type AS
+$BODY$
+declare
+ class_base fiche_def.fd_class_base%type;
+ maxcode numeric;
+ sResult account_type;
+ bAlphanum bool;
+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;
+ bAlphanum := account_alphanum();
+ if bAlphanum = false then
+ 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;
+ else
+ -- if alphanum, use name
+ select ad_value into sName from fiche_detail where f_id=p_f_id
and ad_id=1;
+ if sName is null then
+ raise exception 'Cannot compute an accounting without
the name of the card for %',p_f_id;
+ end if;
+ sResult := class_base||sName;
+ end if;
+ return sResult;
+end;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION comptaproc.account_compute(integer) OWNER TO dany;
Added: phpcompta/trunk/sql/account_insert.sql
===================================================================
--- phpcompta/trunk/sql/account_insert.sql (rev 0)
+++ phpcompta/trunk/sql/account_insert.sql 2011-10-22 20:17:50 UTC (rev
4226)
@@ -0,0 +1,94 @@
+-- Function: comptaproc.account_insert(integer, text)
+
+-- DROP FUNCTION comptaproc.account_insert(integer, text);
+
+CREATE OR REPLACE FUNCTION comptaproc.account_insert(p_f_id integer, p_account
text)
+ 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;
+ 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
+ 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;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION comptaproc.account_insert(integer, text) OWNER TO dany;
Added: phpcompta/trunk/sql/format_account.sql
===================================================================
--- phpcompta/trunk/sql/format_account.sql (rev 0)
+++ phpcompta/trunk/sql/format_account.sql 2011-10-22 20:17:50 UTC (rev
4226)
@@ -0,0 +1,32 @@
+-- Function: comptaproc.format_account(account_type)
+
+-- DROP FUNCTION comptaproc.format_account(account_type);
+
+CREATE OR REPLACE FUNCTION comptaproc.format_account(p_account account_type)
+ RETURNS account_type AS
+$BODY$
+
+declare
+
+sResult account_type;
+
+begin
+sResult := lower(p_account);
+
+sResult := translate(sResult,'éèêëàâäïîüûùöô','eeeeaaaiiuuuoo');
+sResult := translate(sResult,' $€µ£%.+-/\!(){}(),;_&|"#''^','');
+
+if not sResult similar to '^[[:alnum:]_]+$' then
+ raise exception 'Invalid character in %',p_account;
+end if;
+
+return upper(sResult);
+
+end;$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION comptaproc.format_account(account_type) OWNER TO dany;
+COMMENT ON FUNCTION comptaproc.format_account(account_type) IS 'format the
accounting :
+- upper case
+- remove space and special char.
+';
Added: phpcompta/trunk/sql/tmp_pcmn_alphanum_ins_upd.sql
===================================================================
--- phpcompta/trunk/sql/tmp_pcmn_alphanum_ins_upd.sql
(rev 0)
+++ phpcompta/trunk/sql/tmp_pcmn_alphanum_ins_upd.sql 2011-10-22 20:17:50 UTC
(rev 4226)
@@ -0,0 +1,16 @@
+
+CREATE OR REPLACE FUNCTION comptaproc.tmp_pcmn_alphanum_ins_upd()
+ RETURNS trigger AS
+$BODY$
+declare
+ r_record tmp_pcmn%ROWTYPE;
+begin
+r_record := NEW;
+r_record.pcm_val:=format_account(NEW.pcm_val);
+
+return r_record;
+end;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION comptaproc.tmp_pcmn_ins() OWNER TO dany;
Added: phpcompta/trunk/sql/trigger.tmp_pcmn.sql
===================================================================
--- phpcompta/trunk/sql/trigger.tmp_pcmn.sql (rev 0)
+++ phpcompta/trunk/sql/trigger.tmp_pcmn.sql 2011-10-22 20:17:50 UTC (rev
4226)
@@ -0,0 +1,9 @@
+-- Trigger: t_tmp_pcm_alphanum_ins_upd on tmp_pcmn
+
+-- DROP TRIGGER t_tmp_pcm_alphanum_ins_upd ON tmp_pcmn;
+
+CREATE TRIGGER t_tmp_pcm_alphanum_ins_upd
+ BEFORE INSERT OR UPDATE
+ ON tmp_pcmn
+ FOR EACH ROW
+ EXECUTE PROCEDURE comptaproc.tmp_pcmn_alphanum_ins_upd();
Modified: phpcompta/trunk/sql/upgrade.sql
===================================================================
--- phpcompta/trunk/sql/upgrade.sql 2011-10-22 20:16:44 UTC (rev 4225)
+++ phpcompta/trunk/sql/upgrade.sql 2011-10-22 20:17:50 UTC (rev 4226)
@@ -1,3 +1,17 @@
drop table public.import_tmp;
drop table public.format_csv_banque;
+insert into parametre values ('MY_ALPHANUM','N');
+
+/*
+script SQL to run
+account_alphanum.sql
+account_compute.sql
+account_insert.sql
+account-update.sql
+change-pcmn-to-alphanum.sql
+format_account.sql
+tmp_pcmn_alphanum_ins_upd.sql
+tmp_pcmn_ins.sql
+trigger.tmp_pcmn.sql
+*/
create unique index test_qcode_idx on fiche_detail (ad_value) where ad_id=23;
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r4226 - in phpcompta/trunk: html include sql,
phpcompta-dev <=