55 if (
$Max == 0 )
return null;
87 $this->row=$this->
db->get_array(
"select distinct j_id,jr_id,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,j_date,".
88 "case when j_debit='t' then j_montant else 0 end as deb_montant,".
89 "case when j_debit='f' then j_montant else 0 end as cred_montant,".
90 " jr_comment as description,jrn_def_name as jrn_name,".
91 "j_debit, jr_internal,jr_pj_number ".
92 ",oc_amount,oc_vat_amount".
93 " from jrnx left join jrn_def on jrn_def_id=j_jrn_def ".
94 " left join operation_currency using (j_id) ".
95 " left join jrn on jr_grpt_id=j_grpt".
96 " where j_poste=$1 and $periode ".
97 " order by j_date",array($this->
id));
98 $res_saldo = $this->
db->exec_sql(
99 "select sum(deb_montant),sum(cred_montant) from
100 (select case when j_debit='t' then j_montant else 0 end as deb_montant,
101 case when j_debit='f' then j_montant else 0 end as cred_montant
104 on jrn_def_id=j_jrn_def
105 left join jrn on jr_grpt_id=j_grpt
106 where j_poste=$1 and $periode ) as m
108 $this->tot_deb=$this->tot_cred=0;
114 return array($this->row,$this->tot_deb,$this->tot_cred);
128 with sqlletter as (select j_id,jl_id from letter_cred union all select j_id , jl_id from letter_deb )
129 select j1.j_id,jr_id,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,j_date,
131 ,case when j_debit='t' then j_montant else 0 end as deb_montant,
132 case when j_debit='f' then j_montant else 0 end as cred_montant,
133 case when j_text is null or j_text = '' then jr_comment
134 else jr_comment||' '||j_text end
135 as description,jrn_def_name as jrn_name,
136 j_debit, jr_internal,jr_pj_number
137 ,(select distinct jl_id from sqlletter where sqlletter.j_id=j1.j_id ) as letter
144 ,(with cred as (select jl_id, sum(j_montant) as amount_cred from letter_cred left join jrnx using (j_id) group by jl_id )
145 , deb as (select jl_id, sum(j_montant) as amount_deb from letter_deb left join jrnx using (j_id) group by jl_id )
146 select amount_deb-amount_cred
149 full join deb using (jl_id) where jl_id=(select distinct jl_id from sqlletter where sqlletter.j_id=j1.j_id )) as delta_letter
151 ,jrn.currency_rate_ref
153 ,(select cr_code_iso from currency where id=jrn.currency_id) as cr_code_iso
157 case when exists(select 1 from operation_analytique oa where j1.j_id=oa.j_id) then 1 else 0 end as op_analytic
159 left join operation_currency as va on (j1.j_id = va.j_id )
160 join jrn_def on (jrn_def_id=j_jrn_def )
161 join jrn on (jr_grpt_id=j_grpt)
162 join tmp_pcmn on (j1.j_poste=pcm_val)
163 join parm_periode on (p_id=jr_tech_per)
164 where j1.j_poste=$1 and
165 ( to_date($2,'DD.MM.YYYY') <= j_date and
166 to_date($3,'DD.MM.YYYY') >= j_date )
167 and $filter_sql $sql_let
168 order by j_date,substring(jr_pj_number,'[0-9]+$') asc";
179 $bal_sql=
"select sum(amount_deb) as s_deb,sum(amount_cred) as s_cred, j_poste
181 (select case when j_debit='t' then j_montant else 0 end as amount_deb,
182 case when j_debit='f' then j_montant else 0 end as amount_cred,
184 from jrnx join jrn on (j_grpt = jr_grpt_id)
188 ( to_date($2,'DD.MM.YYYY') <= j_date and
189 to_date($3,'DD.MM.YYYY') >= j_date )) as signed_amount
204 $sql_saldo=
"select sum(deb_montant) as deb,sum(cred_montant) as cred
206 (select case when j_debit='t' then j_montant else 0 end as deb_montant,
207 case when j_debit='f' then j_montant else 0 end as cred_montant
209 join jrn_def on (jrn_def_id=j_jrn_def )
210 join jrn on (jr_grpt_id=j_grpt)
211 join tmp_pcmn on (j_poste=pcm_val)
212 join parm_periode on (p_id=jr_tech_per)
214 ( to_date($2,'DD.MM.YYYY') <= j_date and
215 to_date($3,'DD.MM.YYYY') >= j_date )
216 and $filter_sql $sql_let ) as m";
234 $filter_sql =
$g_user->get_ledger_sql(
'ALL', 3);
240 $sql_let =
' and j1.j_id in (select j_id from letter_cred union all select j_id from letter_deb)';
243 $sql_let =
' and j1.j_id not in (select j_id from letter_cred union all select j_id from letter_deb) ';
248 if ($this->
db->is_prepare(
"not_balanced_account") ==
false) {
251 $this->
db->prepare(
"not_balanced_account", $sql_balanced);
254 $ret_balanced = $this->
db->execute(
"not_balanced_account", array($this->
id, $p_from, $p_to));
257 if (empty(
$r))
return array();
258 if (
$r[0][
's_deb'] ==
$r[0][
's_cred'])
return array();
262 if (!$this->
db->is_prepare(
"sql_accounting_detail")) {
264 $this->
db->prepare(
"sql_accounting_detail",
$sql);
267 $ret = $this->
db->execute(
"sql_accounting_detail", array($this->
id, $p_from, $p_to));
271 if ($this->
db->is_prepare(
"saldo_account") ==
false) {
273 $this->
db->prepare(
"saldo_account", $sql_saldo);
275 $res_saldo = $this->
db->execute(
"saldo_account", array($this->
id, $p_from, $p_to));
277 $this->tot_deb = $this->tot_cred = 0;
280 $this->tot_deb =
$result[0][
'deb'];
281 $this->tot_cred =
$result[0][
'cred'];
283 return array($this->row, $this->tot_deb, $this->tot_cred);
294 "select pcm_lib from tmp_pcmn where
295 pcm_val=$1",array($this->
id));
299 $this->
name=
$r[
'pcm_lib'];
303 $this->
name=_(
"Poste inconnu");
314 "select pcm_type from tmp_pcmn where
315 pcm_val=$1",array($this->
id));
324 $sql=
"select pcm_val from tmp_pcmn where pcm_val= $1";
325 $ret=$this->
db->exec_sql(
$sql,array($this->
id));
334 $ret=$this->
db->exec_sql(
"select pcm_lib,pcm_val_parent from
335 tmp_pcmn where pcm_val=$1",array($this->
id));
338 if ( !
$r )
return false;
340 $this->parent=
$r[0][
'pcm_val_parent'];
355 if (DEBUGNOALYSS > 1 ) { echo __CLASS__.
".".__FUNCTION__.
"p_cond {$p_cond}"; }
357 $Res=$this->
db->exec_sql(
"select sum(deb) as sum_deb, sum(cred) as sum_cred from
359 case when j_debit='t' then j_montant else 0 end as deb,
360 case when j_debit='f' then j_montant else 0 end as cred
361 from jrnx join tmp_pcmn on j_poste=pcm_val
363 j_poste::text like ('$this->id'::text) and
366 if (DEBUGNOALYSS > 1 ) { echo $this->
db->get_sql(); }
368 if (
$Max==0)
return 0;
371 return abs(
$r[
'sum_deb']-
$r[
'sum_cred']);
381 if ( $p_cond !=
"") $p_cond=
" and ".$p_cond;
383 $sql=
"select sum(deb) as sum_deb, sum(cred) as sum_cred from
385 case when j_debit='t' then j_montant else 0 end as deb,
386 case when j_debit='f' then j_montant else 0 end as cred
389 j_poste::text like ('$this->id'::text)
392 if (DEBUGNOALYSS > 1 ) { tracedebug(
"impress.debug.log",
"$sql",
'acc_account_ledger:get_solde_detail'); }
398 return array(
'debit'=>0,
405 if (
$r[
'sum_deb']==
'')
407 if (
$r[
'sum_cred']==
'')
410 return array(
'debit'=>
$r[
'sum_deb'],
411 'credit'=>
$r[
'sum_cred'],
412 'solde'=>abs(bcsub(
$r[
'sum_deb'],
$r[
'sum_cred'],2)));
424 $a_TVA=$this->
db->get_array(
'select tva_poste
426 foreach ( $a_TVA as $line_tva)
428 if ( $line_tva[
'tva_poste'] ==
'' )
430 list($tva_deb,$tva_cred)=explode(
',',$line_tva[
'tva_poste']);
431 if ( $this->
id == $tva_deb ||
432 $this->
id == $tva_cred )
457 if ( count($this->row ) == 0 )
468 $label.=_(
"Solde créditeur au lieu de débiteur").
'<span class="icon"></span>';
473 $label.=_(
"Solde débiteur au lieu de créditeur").
" ".
'<span class="icon"></span>';
475 echo
'<span class="notice">'.$label.
'</span>';
478 echo
"<TABLE id=\"tbpopup\" class=\"resultfooter\" style=\"border-collapse:separate;margin:1%;width:98%;\">";
480 echo
"<TABLE id=\"tb".$from_div.
"\" class=\"resultfooter\" style=\"border-collapse:separate;margin:1%;width:98%;\">";
483 "<TH style=\"text-align:left\">"._(
'Date').
" </TH>".
484 "<TH style=\"text-align:left\">"._(
'Pièce').
" </TH>".
485 "<TH style=\"text-align:left\">"._(
'Code').
"</TH>".
486 "<TH style=\"text-align:left\">"._(
'Interne').
" </TH>".
487 "<TH style=\"text-align:left\">"._(
'Tiers').
" </TH>".
488 "<TH style=\"text-align:left\">"._(
'Description').
"</TH>".
489 "<TH style=\"text-align:left\">"._(
'Type').
"</TH>".
490 "<TH style=\"text-align:left\">"._(
'ISO').
"</TH>".
491 "<TH style=\"text-align:right\">"._(
'Dev.').
"</TH>".
492 "<TH style=\"text-align:right\">"._(
'Débit').
"</TH>".
493 "<TH style=\"text-align:right\">"._(
"Crédit").
"</TH>".
494 th(
'Prog.',
'style="text-align:right"').
495 th(
'Let.',
'style="text-align:right"');
503 foreach ( $this->row as
$op )
509 $vw_operation = sprintf(
'<A class="detail" style="text-decoration:underline;color:red" HREF="javascript:modifyOperation(\'%s\',\'%s\')" >%s</A>',
$op[
'jr_id'],
dossier::id(),
$op[
'jr_internal']);
512 if (
$op[
'letter'] != 0)
514 $let = strtoupper(base_convert(
$op[
'letter'], 10, 36));
516 if (
$op[
'delta_letter'] != 0) $html_let=
'<img src="image/warning.png" onmouseover="displayBulle(\'delta = '.$op[
'delta_letter'].
'\')
" onmouseleave="hideBulle()
" style="height:12px
"/>'.$html_let;
518 $tmp_diff=bcsub($op['deb_montant'],$op['cred_montant']);
521 * reset prog. balance to zero if we change of exercice
523 if ( $old_exercice != $op['p_exercice'])
525 if ( $old_exercice != '')
527 $progress=bcsub($sum_deb,$sum_cred);
528 $side="
".$this->get_amount_side($progress);
529 echo "<TR
class=\
"highlight\">".
530 "<TD>$old_exercice</TD>".
531 "<TD></TD>".td().td().td().td().td().
532 "<TD>"._(
"Totaux").
"</TD>".
td(
"").
533 "<TD style=\"text-align:right;padding-left:10px;\">".nbm($sum_deb).
"</TD>".
534 "<TD style=\"text-align:right;padding-left:10px;\">".nbm($sum_cred).
"</TD>".
546 $sum_cred=bcadd($sum_cred,
$op[
'cred_montant']);
547 $sum_deb=bcadd($sum_deb,
$op[
'deb_montant']);
550 $op_analytic=(
$op[
'op_analytic']==1)?
'<span style="float:right;background:black;color:white;">∋</span>':
'';
551 echo
"<TR $class name=\"tr_" .
$let .
"_" .
$from_div .
"\">" .
553 td(
h(
$op[
'jr_pj_number'])).
555 "<TD>".$vw_operation.
"</TD>".
556 "<TD>".$tiers.
"</TD>".
557 "<TD>".h(
$op[
'description']).$op_analytic.
"</TD>".
558 td(
$op[
'jr_optype']);
560 if (
$op[
'currency_id'] > 0 )
563 $currency_val=(
$op[
'oc_amount'] == 0)?round(bcmul (
$op[
'j_montant'],
$op[
'currency_rate']),2):
$op[
'oc_amount'] ;
565 echo
td(
$op[
'cr_code_iso']).
566 td(
nbm($currency_val,2),
'style="text-align:right;padding-left:10px;"');
572 "<TD style=\"text-align:right;padding-left:10px;\">".nbm(
$op[
'deb_montant']).
"</TD>".
573 "<TD style=\"text-align:right;padding-left:10px;\">".nbm(
$op[
'cred_montant']).
"</TD>".
576 td($html_let,
' style="color:red;text-align:right"') .
578 $old_exercice=
$op[
'p_exercice'];
581 $solde_type=($sum_deb>$sum_cred)?_(
"solde débiteur"):_(
"solde créditeur");
582 $diff=bcsub($sum_deb,$sum_cred);
584 echo
"<TR class=\"highlight\">".
585 td(
$op[
'p_exercice']).
586 td().td().td().td().td().td().
587 "<TD >Totaux</TD>".td(
"").
588 "<TD style=\"text-align:right\">".nbm($sum_deb).
"</TD>".
589 "<TD style=\"text-align:right\">".nbm($sum_cred).
"</TD>".
590 "<TD style=\"text-align:right\">".nbm(abs(
$diff)).$side.
"</TD>".
593 echo
"<tr><TD>$solde_type</TD><td></td>".
594 "<TD style=\"text-align:right\">".nbm(abs(
$diff)).
"</TD>".
598 $solde_until_now=$this->
get_solde_detail(
" j_date <= to_date('{$p_array['to_periode']}','DD.MM.YYYY') ");
599 echo
'<tr style="font-weight:bold;color:orangered">';
600 echo
td(_(
"Solde global"));
601 echo
td(
"D : ".
nbm($solde_until_now[
'debit']),
'class="num"');
602 echo
td(
"C : ".
nbm($solde_until_now[
'credit']),
'class="num"');
603 echo
td(
"Delta : ".
nbm($solde_until_now[
'solde']).
" ".$this->
get_amount_side($solde_until_now[
'debit']-$solde_until_now[
'credit']),
'class="num"');
611 if ( DEBUGNOALYSS>1) echo \Noalyss\Dbg::hidden_info(
"variable", get_defined_vars());
612 if ( DEBUGNOALYSS>1) echo \Noalyss\Dbg::hidden_info(
"this", $this);
637 switch($actiontarget)
640 $action_csv=
'CSV:postedetail';
641 $action_pdf=
'PDF:postedetail';
644 $action_csv=
'CSV:glcompte';
645 $action_pdf=
'PDF:glcompte';
648 throw new Exception(
" Fonction HtmlTableHeader argument actiontarget invalid");
655 if ($actiontarget==
'poste')
657 echo
'<TD><form method="GET" ACTION="export.php">'.
660 $hid->input(
"type",
"poste").$hid->input(
'ac',
$_REQUEST[
'ac']).
"</form></TD>";
664 echo
'<TD><form method="GET" ACTION="export.php" '.
665 'id="'.$id.
'" onsubmit="return download_document_form(\''.
$id.
'\')
">'.
667 HtmlInput::submit('bt_pdf',"Export
PDF").
668 HtmlInput::hidden('act',$action_pdf).
669 $hid->input("type","poste
").$str_ople.
670 $hid->input('p_action','impress').
671 $hid->input("from_periode
",$_REQUEST['from_periode']).
672 $hid->input("to_periode
",$_REQUEST['to_periode'])
675 if ( isset($_REQUEST['letter'] )) echo HtmlInput::hidden('letter','2');
676 if ( isset($_REQUEST['solded'] )) echo HtmlInput::hidden('solded','1');
678 if (isset($_REQUEST['from_poste']))
679 echo HtmlInput::hidden('from_poste',$_REQUEST['from_poste']);
681 if (isset($_REQUEST['to_poste']))
682 echo HtmlInput::hidden('to_poste',$_REQUEST['to_poste']);
684 if (isset($_REQUEST['poste_id']))
685 echo HtmlInput::hidden("poste_id
",$_REQUEST['poste_id']);
687 if (isset($_REQUEST['poste_fille']))
688 echo $hid->input('poste_fille','on');
689 if (isset($_REQUEST['oper_detail']))
690 echo $hid->input('oper_detail','on');
694 echo '<TD><form method="GET
" ACTION="export.php
" id="'.$id.'" onsubmit="download_document_form(\
''.
$id.
'\')
">'.
696 HtmlInput::submit('bt_csv',"Export CSV
").
697 HtmlInput::hidden('act',$action_csv).
698 $hid->input("type","poste
").$str_ople.
699 $hid->input('p_action','impress').
700 $hid->input("from_periode
",$_REQUEST['from_periode']).
701 $hid->input("to_periode
",$_REQUEST['to_periode']);
703 if (isset($_REQUEST['from_poste']))
704 echo HtmlInput::hidden('from_poste',$_REQUEST['from_poste']);
706 if (isset($_REQUEST['to_poste']))
707 echo HtmlInput::hidden('to_poste',$_REQUEST['to_poste']);
709 if (isset($_REQUEST['poste_id']))
710 echo HtmlInput::hidden("poste_id
",$_REQUEST['poste_id']);
712 if ( isset($_REQUEST['letter'] )) echo HtmlInput::hidden('letter','2');
713 if ( isset($_REQUEST['solded'] )) echo HtmlInput::hidden('solded','1');
715 if (isset($_REQUEST['poste_fille']))
716 echo $hid->input('poste_fille','on');
717 if (isset($_REQUEST['oper_detail']))
718 echo $hid->input('oper_detail','on');
719 if (isset($_REQUEST['poste_id'])) echo $hid->input("poste_id
",$_REQUEST['poste_id']);
722 echo '<td style="vertical-
align:top
">';
723 echo HtmlInput::print_window();
735 function belong_ledger($p_jrn)
737 $filter=$this->db->get_value("select jrn_def_class_cred
from jrn_def where
jrn_def_id=$1
", array($p_jrn));
738 if ( noalyss_trim ($filter) == '')
741 $valid_cred=explode(" ",$filter);
742 $sql="select count(*) as poste
from tmp_pcmn where ";
748 foreach ( $valid_cred as $item_cred)
750 if ( strlen (trim($item_cred)))
752 if ( strstr($item_cred,
"*") ==
true )
754 $SqlItem=$or .
'pcm_val::text like $' . $SqlArrayN++;
755 array_push($SqlArray, strtr($item_cred,
"*",
"%"));
760 $SqlItem=$or .
'pcm_val::text = $' . $SqlArrayN++;
761 array_push($SqlArray, $item_cred);
764 $SqlFilter=$SqlFilter.$SqlItem;
767 $sql.=$SqlFilter .
' and pcm_val::text=$' . $SqlArrayN++;
768 array_push($SqlArray, $this->
id);
784 $row=
$l->get_propertie();
785 if (
$l->get_type() !=
'ODS') {
return [];}
787 $valid_account=explode(
" ",
$row[
'jrn_def_class_deb']);
788 return $valid_account;
803 if ( empty(
$array) )
return "";
805 foreach (
$array as $item_cred)
807 if ( strlen (trim($item_cred))>0 )
809 if ( strstr($item_cred,
"*") ==
true )
811 $item_cred=strtr($item_cred,
"*",
"%");
812 $sql_tmp=
" pcm_val::text like '$item_cred' or";
816 $sql_tmp=
" pcm_val::text = '$item_cred' or";
832 $sql=
"select f_id from fiche_detail where ad_id=$1 and ad_value=$2";
844 $href=
"export.php?".http_build_query(
847 "poste_id"=>$this->
id,
850 "from_periode"=>$p_from,
852 "act"=>
"CSV:postedetail"
855 return '<a class="smallbutton" style="display:inline-block" href="'.$href.
'">'._(
"Export CSV").
'</a>';
866 $href=
"export.php?".http_build_query(
869 "poste_id"=>$this->
id,
872 "from_periode"=>$p_from,
874 "act"=>
"PDF:postedetail"
877 return '<a class="smallbutton" style="display:inline-block" href="'.$href.
'">'._(
"Export PDF").
'</a>';
886 return _(
'Filtre rapide').
' '.
HtmlInput::filter_table($p_table_id,
'0,1,2,3,4,5,6,7,8,9,10', 1);
896 $sql =
"select pcm_val,pcm_lib from tmp_pcmn
898 (select j_poste from jrnx where j_date >= to_date('$from_date','DD.MM.YYYY')
899 and j_date <= to_date('$to_date','DD.MM.YYYY') ) ";
901 if ($from_accounting !=
'') {
905 if ($to_accounting !=
'') {
909 $sql =
$sql . $cond_poste .
' order by pcm_val::text';
format_date($p_date, $p_from_format='YYYY-MM-DD', $p_to_format='DD.MM.YYYY')
format the date, when taken from the database the format is MM-DD-YYYY
th($p_string, $p_extra='', $raw='')
sql_filter_per($p_cn, $p_from, $p_to, $p_form='p_id', $p_field='jr_tech_per')
Create the condition to filter on the j_tech_per thanks a from and to date.
noalyss_strlentrim($p_string)
noalyss_str_replace($search, $replace, $string)
td($p_string='', $p_extra='')
surround the string with td
nbm($p_number, $p_dec=2)
format the number with a sep.
global $g_user
if no group available , then stop
return false Description text align
catch(Exception $exc) if(! $g_user->can_write_action($ag_id)) $r
h( $row[ 'oa_description'])
foreach( $array as $item)
Manage the account from the table jrn, jrnx or tmp_pcmn.
static get_used_accounting($from_date, $to_date, $from_accounting, $to_accounting)
get_solde_detail($p_cond="")
give the balance of an account
load()
Get all the value for this object from the database the data member are set.
get_account_ledger($p_jrn)
With the id of the ledger, get the col jrn_def_class_deb.
get_amount_side($p_amount)
return the letter C if amount is > 0, D if < 0 or =
build_sql_account($p_jrn)
build a sql statement thanks a array found with get_account_ledger
static HtmlTableHeader($actiontarget="poste")
Display HTML Table Header (button)
get_solde($p_cond=" true ")
give the balance of an account
button_pdf($p_from, $p_to)
Return a string with the HTML code to display a button to export the history in PDF.
get_name()
Return the name of a account it doesn't change any data member.
find_card()
Find the id of the cards which are using the current account.
get_row($p_from, $p_to)
Get data for accounting entry between 2 periode.
make_sql_accounting_detail($p_from, $p_to, $sql_let, $filter_sql)
build the SQL for get_row_data
isTVA()
isTva tell is a poste is used for VAT
HtmlTable($p_array=null, $let=0, $from_div=0)
HtmlTable, display a HTML of a poste for the asked period.
get_type()
Return the type of a account.
get_row_sql_deprecated($Res)
get the row thanks the resource
get_row_date($p_from, $p_to, $let=0, $solded=0)
Get data for accounting entry between 2 date.
__construct($p_cn, $p_id)
make_sql_not_balanced_account($filter)
make the SQL for the balanced accounting
filter_history($p_table_id)
Filter in javascript the table with the history.
do_exist()
check if the poste exist in the tmp_pcmn
button_csv($p_from, $p_to)
Return a string with the HTML code to display a button to export the history in CSV.
make_sql_saldo_account($filter_sql, $sql_let)
make the SQL for the balance of an accounting
this file match the tables jrn & jrnx the purpose is to remove or save accountant writing to these ta...
static fetch_all($ret)
wrapper for the function pg_fetch_all
static escape_string($p_string)
wrapper for the function pg_escape_string
static fetch_result($ret, $p_row=0, $p_col=0)
wrapper for the function pg_fetch_all
static fetch_array($ret, $p_indice=0, $p_mode=PGSQL_ASSOC)
wrapper for the function pg_fetch_array
static num_row($ret)
wrapper for the function pg_num_rows
static id()
return the 'gDossier' value after a check
API for creating PDF, unicode, based on tfpdf.
foreach( $Fiche->row as $op) $solde_type