noalyss Version-9
acc_account_ledger.class.php
Go to the documentation of this file.
1<?php
2/*
3 * This file is part of NOALYSS.
4 *
5 * NOALYSS is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
9 *
10 * NOALYSS is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with NOALYSS; if not, write to the Free Software
17 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18*/
19// Copyright Author Dany De Bontridder danydb@aevalys.eu
20/*! \file
21 * \brief Manage the account
22 */
23/*!
24 * \brief Manage the account from the table jrn, jrnx or tmp_pcmn
25 */
26
28{
29 var $db; /*!< $db database connection */
30 var $id; /*!< $id poste_id (pcm_val)*/
31 var $label; /*!< $label label of the poste */
32 var $parent; /*!< $parent parent account */
33 var $row; /*!< $row double array see get_row */
34 var $tot_deb; /*!< value set by get_row */
35 var $tot_cred; /*!< value by get_row */
36 var $name;
37
38 function __construct ($p_cn,$p_id)
39 {
40 $this->db=$p_cn;
41 $this->id=$p_id;
42 }
43 /**
44 *@brief get the row thanks the resource
45 *@return double array (j_date,deb_montant,cred_montant,description,jrn_name,j_debit,jr_internal)
46 * (tot_deb,tot_credit)
47 * @deprecated since version 6920
48 */
49 private function get_row_sql_deprecated($Res)
50 {
51 $array=array();
52 $tot_cred=0.0;
53 $tot_deb=0.0;
55 if ( $Max == 0 ) return null;
56 for ($i=0;$i<$Max;$i++)
57 {
59 if ($array[$i]['j_debit']=='t')
60 {
61 $tot_deb+=$array[$i]['deb_montant'] ;
62 }
63 else
64 {
65 $tot_cred+=$array[$i]['cred_montant'] ;
66 }
67 }
68 $this->row=$array;
69 $this->tot_deb=$tot_deb;
70 $this->tot_cred=$tot_cred;
71 return array($array,$tot_deb,$tot_cred);
72
73 }
74 /*!
75 * \brief Get data for accounting entry between 2 periode
76 *
77 * \param $p_from periode from
78 * \param $p_to end periode
79 * \return double array (j_date,deb_montant,cred_montant,description,jrn_name,j_debit,jr_internal)
80 * (tot_deb,tot_credit
81 *
82 */
83 function get_row($p_from,$p_to)
84 {
85 $periode=sql_filter_per($this->db,$p_from,$p_to,'p_id','jr_tech_per');
86
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
102 from jrnx
103 left join jrn_def
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
107 ",array($this->id));
108 $this->tot_deb=$this->tot_cred=0;
109
110 if ( Database::num_row($res_saldo) > 0 ) {
111 $this->tot_deb=Database::fetch_result($res_saldo, 0, 0);
112 $this->tot_cred=Database::fetch_result($res_saldo, 0, 1);
113 }
114 return array($this->row,$this->tot_deb,$this->tot_cred);
115 }
116 /**
117 * @brief build the SQL for get_row_data
118 * @param $p_from date d.m.Y start date
119 * @param $p_to date d.m.Y until date
120 * @param $sql_let sql string for getting lettering info
121 * @param $filter_sql string SQL for filtering the ledgers , to respect security on ledgers
122 * @return sql SELECT
123 */
124 function make_sql_accounting_detail($p_from,$p_to,$sql_let,$filter_sql)
125 {
126
127 $sql = "
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,
130 j_qcode
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
138 ,pcm_lib
139 ,jr_optype
140 ,jr_tech_per
141 ,p_exercice
142 ,jrn_def_name
143 ,jrn_def_code
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
147 from
148 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
150 ,jrn.currency_rate
151 ,jrn.currency_rate_ref
152 ,jrn.currency_id
153 ,(select cr_code_iso from currency where id=jrn.currency_id) as cr_code_iso
154 ,j_montant
155 ,oc_amount
156 ,oc_vat_amount ,
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
158 from jrnx as j1
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";
169 return $sql;
170 }
171
172 /**
173 * @brief make the SQL for the balanced accounting
174 * @param $filter filter to respect the security on ledger
175 * @return sql SELECT
176 */
178 {
179 $bal_sql="select sum(amount_deb) as s_deb,sum(amount_cred) as s_cred, j_poste
180 from
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,
183 j_poste
184 from jrnx join jrn on (j_grpt = jr_grpt_id)
185 where
186 j_poste=$1 and
187 $filter and
188 ( to_date($2,'DD.MM.YYYY') <= j_date and
189 to_date($3,'DD.MM.YYYY') >= j_date )) as signed_amount
190 group by j_poste
191 ";
192 return $bal_sql;
193 }
194
195 /**
196 * @brief make the SQL for the balance of an accounting
197 * @param $filter_sql filter to respect the security on ledger
198 * @param $sql_let string for getting lettering info
199 * @return sql SELECT
200 *
201 */
202 public function make_sql_saldo_account($filter_sql,$sql_let)
203 {
204 $sql_saldo="select sum(deb_montant) as deb,sum(cred_montant) as cred
205 from
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
208 from jrnx j1
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)
213 where j_poste=$1 and
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";
217 return $sql_saldo;
218 }
219 /*!
220 * \brief Get data for accounting entry between 2 date
221 *
222 *\param $p_from date from DD.MM.YYYY
223 *\param $p_to end date DD.MM.YYYY
224 *\param $let 0 means all rows, 1 only lettered, 2 only unlettered
225 *\param $solded 0 means all account, 1 means only accounts with a saldo <> 0
226 *\note the data are filtered by the access of the current user
227 * \return double array (j_date,deb_montant,cred_montant,description,jrn_name,j_debit,jr_internal)
228 * (tot_deb,tot_credit
229 *
230 */
231 function get_row_date($p_from, $p_to, $let = 0, $solded = 0)
232 {
233 global $g_user;
234 $filter_sql = $g_user->get_ledger_sql('ALL', 3);
235 $sql_let = '';
236 switch ($let) {
237 case 0:
238 break;
239 case 1:
240 $sql_let = ' and j1.j_id in (select j_id from letter_cred union all select j_id from letter_deb)';
241 break;
242 case '2':
243 $sql_let = ' and j1.j_id not in (select j_id from letter_cred union all select j_id from letter_deb) ';
244 break;
245 }
246 // if accounting is balanced , D = C then returns an empty array
247 if ($solded == 1) {
248 if ($this->db->is_prepare("not_balanced_account") == false) {
249 $filter=noalyss_str_replace('jrn_def_id','jr_def_id',$filter_sql);
250 $sql_balanced = $this->make_sql_not_balanced_account($filter);
251 $this->db->prepare("not_balanced_account", $sql_balanced);
252 }
253
254 $ret_balanced = $this->db->execute("not_balanced_account", array($this->id, $p_from, $p_to));
255
256 $r = Database::fetch_all($ret_balanced);
257 if (empty($r)) return array();
258 if ($r[0]['s_deb'] == $r[0]['s_cred']) return array();
259 }
260
261 // get the detail of accouting
262 if (!$this->db->is_prepare("sql_accounting_detail")) {
263 $sql = $this->make_sql_accounting_detail($p_from, $p_to, $sql_let, $filter_sql);
264 $this->db->prepare("sql_accounting_detail", $sql);
265
266 }
267 $ret = $this->db->execute("sql_accounting_detail", array($this->id, $p_from, $p_to));
268 $this->row = Database::fetch_all($ret);
269
270 // $this->row=$this->db->get_array(,array($this->id,$p_from,$p_to));
271 if ($this->db->is_prepare("saldo_account") == false) {
272 $sql_saldo = $this->make_sql_saldo_account($filter_sql, $sql_let);
273 $this->db->prepare("saldo_account", $sql_saldo);
274 }
275 $res_saldo = $this->db->execute("saldo_account", array($this->id, $p_from, $p_to));
276 $result=Database::fetch_all($res_saldo);
277 $this->tot_deb = $this->tot_cred = 0;
278
279 if (! empty($result) > 0) {
280 $this->tot_deb = $result[0]['deb'];
281 $this->tot_cred = $result[0]['cred'];
282 }
283 return array($this->row, $this->tot_deb, $this->tot_cred);
284 }
285
286
287 /*!\brief Return the name of a account
288 * it doesn't change any data member
289 * \return string with the pcm_lib
290 */
291 function get_name()
292 {
293 $ret=$this->db->exec_sql(
294 "select pcm_lib from tmp_pcmn where
295 pcm_val=$1",array($this->id));
296 if ( Database::num_row($ret) != 0)
297 {
299 $this->name=$r['pcm_lib'];
300 }
301 else
302 {
303 $this->name=_("Poste inconnu");
304 }
305 return $this->name;
306 }
307 /*!
308 * \brief Return the type of a account
309 * \returns string ACT,ACTINV,CHA,CHAINV,CON,PAS,PRO
310 */
311 function get_type()
312 {
313 $type=$this->db->get_value(
314 "select pcm_type from tmp_pcmn where
315 pcm_val=$1",array($this->id));
316 return $type;
317 }
318
319 /*!\brief check if the poste exist in the tmp_pcmn
320 *\return the number of line (normally 1 or 0)
321 */
322 function do_exist()
323 {
324 $sql="select pcm_val from tmp_pcmn where pcm_val= $1";
325 $ret=$this->db->exec_sql($sql,array($this->id));
326 return Database::num_row($ret) ;
327 }
328 /*!\brief Get all the value for this object from the database
329 * the data member are set
330 * \return false if this account doesn't exist otherwise true
331 */
332 function load():bool
333 {
334 $ret=$this->db->exec_sql("select pcm_lib,pcm_val_parent from
335 tmp_pcmn where pcm_val=$1",array($this->id));
337
338 if ( ! $r ) return false;
339 $this->label=$r[0]['pcm_lib'];
340 $this->parent=$r[0]['pcm_val_parent'];
341 return true;
342
343 }
344
345
346 /*!
347 * \brief give the balance of an account
348 *
349 * \return
350 * balance of the account
351 *
352 */
353 function get_solde($p_cond=" true ")
354 {
355 if (DEBUGNOALYSS > 1 ) { echo __CLASS__.".".__FUNCTION__."p_cond {$p_cond}"; }
356
357 $Res=$this->db->exec_sql("select sum(deb) as sum_deb, sum(cred) as sum_cred from
358 ( select j_poste,
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
362 where
363 j_poste::text like ('$this->id'::text) and
364 $p_cond
365 ) as m ");
366 if (DEBUGNOALYSS > 1 ) { echo $this->db->get_sql(); }
368 if ($Max==0) return 0;
370
371 return abs($r['sum_deb']-$r['sum_cred']);
372 }
373 /*!
374 * \brief give the balance of an account
375 * \return
376 * balance of the account
377 *
378 */
379 function get_solde_detail($p_cond="")
380 {
381 if ( $p_cond != "") $p_cond=" and ".$p_cond;
382
383 $sql="select sum(deb) as sum_deb, sum(cred) as sum_cred from
384 ( select j_poste,
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
387 from jrnx
388 where
389 j_poste::text like ('$this->id'::text)
390 $p_cond
391 ) as m ";
392 if (DEBUGNOALYSS > 1 ) { tracedebug("impress.debug.log", "$sql", 'acc_account_ledger:get_solde_detail'); }
393 $Res=$this->db->exec_sql($sql);
395
396 if ($Max==0)
397 {
398 return array('debit'=>0,
399 'credit'=>0,
400 'solde'=>0) ;
401 }
403// if p_start is < p_end the query returns null to avoid any problem
404// we set it to 0
405 if ($r['sum_deb']=='')
406 $r['sum_deb']=0.0;
407 if ($r['sum_cred']=='')
408 $r['sum_cred']=0.0;
409
410 return array('debit'=>$r['sum_deb'],
411 'credit'=>$r['sum_cred'],
412 'solde'=>abs(bcsub($r['sum_deb'],$r['sum_cred'],2)));
413 }
414 /*!
415 * \brief isTva tell is a poste is used for VAT
416 * \param none
417 *
418 *
419 * \return 1 is Yes otherwise 0
420 */
421 function isTVA()
422 {
423 // Load TVA array
424 $a_TVA=$this->db->get_array('select tva_poste
425 from tva_rate');
426 foreach ( $a_TVA as $line_tva)
427 {
428 if ( $line_tva['tva_poste'] == '' )
429 continue;
430 list($tva_deb,$tva_cred)=explode(',',$line_tva['tva_poste']);
431 if ( $this->id == $tva_deb ||
432 $this->id == $tva_cred )
433 {
434 return 1;
435 }
436 }
437 return 0;
438
439 }
440
441 /*!
442 * \brief HtmlTable, display a HTML of a poste for the asked period
443 * \param $p_array array for filter
444 * \param $let lettering of operation 0
445 * \return -1 if nothing is found otherwise 0
446 */
447 function HtmlTable($p_array=null,$let=0 , $from_div=0)
448 {
449
450 if ( $p_array==null)$p_array=$_REQUEST;
451 global $g_parameter;
452 $this->get_name();
453 list($array,$tot_deb,$tot_cred)=$this->get_row_date( $p_array['from_periode'],
454 $p_array['to_periode'],$let
455 );
456
457 if ( count($this->row ) == 0 )
458 return -1;
459
460 $rep="";
461
462 $type =$this->get_type();
463 // label warning if the saldo is incorrect
464 $label="";
465 if (in_array($type,array('CHA','ACT','PASINV','PROINV')) && $tot_deb<$tot_cred)
466 {
467
468 $label.=_("Solde créditeur au lieu de débiteur").'<span class="icon">&#xe80e;</span>';
469 }
470 if (in_array($type,array('PRO','PAS','ACTINV','CHAINV')) && $tot_deb>$tot_cred)
471 {
472
473 $label.=_("Solde débiteur au lieu de créditeur")." ".'<span class="icon">&#xe80e;</span>';
474 }
475 echo '<span class="notice">'.$label.'</span>';
476
477 if ( $from_div == 1)
478 echo "<TABLE id=\"tbpopup\" class=\"resultfooter\" style=\"border-collapse:separate;margin:1%;width:98%;\">";
479 else
480 echo "<TABLE id=\"tb".$from_div."\" class=\"resultfooter\" style=\"border-collapse:separate;margin:1%;width:98%;\">";
481 echo '<tbody>';
482 echo "<TR>".
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"');
496 "</TR>"
497 ;
498 $progress=0;$sum_deb=0;$sum_cred=0;
499 bcscale(2);
500 $old_exercice="";
501 $idx=0;
502 $operation=new Acc_Operation($this->db);
503 foreach ( $this->row as $op )
504 {
505
506 $tiers=$operation->find_tiers($op['jr_id'],$op['j_id'],$op['j_qcode']);
507
508
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']);
510 $let = '';
511 $html_let = "";
512 if ($op['letter'] != 0)
513 {
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;
517 }
518 $tmp_diff=bcsub($op['deb_montant'],$op['cred_montant']);
519
520 /*
521 * reset prog. balance to zero if we change of exercice
522 */
523 if ( $old_exercice != $op['p_exercice'])
524 {
525 if ( $old_exercice != '')
526 {
527 $progress=bcsub($sum_deb,$sum_cred);
528 $side="&nbsp;".$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>".
535 td(nbm(abs($progress)).$side,'style="text-align:right;padding-left:10px;"').
536 td('').
537 "</TR>";
538 $sum_cred=0;
539 $sum_deb=0;
540 $progress=0;
541
542 }
543 }
544 $progress=bcadd($progress,$tmp_diff);
545 $side="&nbsp;".$this->get_amount_side($progress);
546 $sum_cred=bcadd($sum_cred,$op['cred_montant']);
547 $sum_deb=bcadd($sum_deb,$op['deb_montant']);
548 $class=($idx%2 == 0)?'class="odd"':$class=' class="even"';
549 $idx++;
550 $op_analytic=($op['op_analytic']==1)?'<span style="float:right;background:black;color:white;">&ni;</span>':'';
551 echo "<TR $class name=\"tr_" . $let . "_" . $from_div . "\">" .
552 "<TD>".smaller_date(format_date($op['j_date']))."</TD>".
553 td(h($op['jr_pj_number'])).
554 "<TD>".\HtmlInput::card_detail($op['j_qcode'])."</TD>".
555 "<TD>".$vw_operation."</TD>".
556 "<TD>".$tiers."</TD>".
557 "<TD>".h($op['description']).$op_analytic."</TD>".
558 td($op['jr_optype']);
559 /// If the currency is not the default one , then show the amount
560 if ( $op['currency_id'] > 0 )
561 {
562 // some amount are not directly recorded into operation_currency, like VAT
563 $currency_val=($op['oc_amount'] == 0)?round(bcmul ($op['j_montant'],$op['currency_rate']),2):$op['oc_amount'] ;
564
565 echo td($op['cr_code_iso']).
566 td(nbm($currency_val,2),'style="text-align:right;padding-left:10px;"');
567 } else {
568 echo td().td();
569 }
570
571 echo
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>".
574 td(nbm(abs($progress)).$side,'style="text-align:right"').
575
576 td($html_let, ' style="color:red;text-align:right"') .
577 "</TR>";
578 $old_exercice=$op['p_exercice'];
579 }
580 echo '<tfoot>';
581 $solde_type=($sum_deb>$sum_cred)?_("solde débiteur"):_("solde créditeur");
582 $diff=bcsub($sum_deb,$sum_cred);
583 $side="&nbsp;".$this->get_amount_side($diff);
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>".
591 td().
592 "</TR>";
593 echo "<tr><TD>$solde_type</TD><td></td>".
594 "<TD style=\"text-align:right\">".nbm(abs($diff))."</TD>".
595 "</TR>";
596 // take saldo from 1st day until last
597 if ($g_parameter->MY_REPORT=='N') {
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"');
604 echo '</tr>';
605
606 }
607 echo '</tfoot>';
608 echo '</tbody>';
609
610 echo "</table>";
611 if ( DEBUGNOALYSS>1) echo \Noalyss\Dbg::hidden_info("variable", get_defined_vars());
612 if ( DEBUGNOALYSS>1) echo \Noalyss\Dbg::hidden_info("this", $this);
613
614 return;
615 }
616 /**
617 * return the letter C if amount is > 0, D if < 0 or =
618 * @param type $p_amount
619 * @return string
620 */
621 function get_amount_side($p_amount)
622 {
623 if ($p_amount == 0)
624 return "=";
625 if ($p_amount < 0)
626 return "C";
627 if ($p_amount > 0)
628 return "D";
629 }
630 /*!
631 * \brief Display HTML Table Header (button)
632 *
633 * \return none
634 */
635 static function HtmlTableHeader($actiontarget="poste")
636 {
637 switch($actiontarget)
638 {
639 case 'poste':
640 $action_csv='CSV:postedetail';
641 $action_pdf='PDF:postedetail';
642 break;
643 case 'gl_comptes':
644 $action_csv='CSV:glcompte';
645 $action_pdf='PDF:glcompte';
646 break;
647 default:
648 throw new Exception(" Fonction HtmlTableHeader argument actiontarget invalid");
649 }
650 $hid=new IHidden();
651
652 echo "<table >";
653 echo '<TR>';
654 $str_ople=(isset($_REQUEST['ople']))?HtmlInput::hidden('ople',$_REQUEST['ople']):'';
655 if ($actiontarget=='poste')
656 {
657 echo '<TD><form method="GET" ACTION="export.php">'.
658 dossier::hidden().
659 HtmlInput::submit('bt_other',"Autre poste").
660 $hid->input("type","poste").$hid->input('ac',$_REQUEST['ac'])."</form></TD>";
661 }
662
663 $id=uniqid("pdf_");
664 echo '<TD><form method="GET" ACTION="export.php" '.
665 'id="'.$id.'" onsubmit="return download_document_form(\''.$id.'\')">'.
666 dossier::hidden().
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'])
673 ;
674
675 if ( isset($_REQUEST['letter'] )) echo HtmlInput::hidden('letter','2');
676 if ( isset($_REQUEST['solded'] )) echo HtmlInput::hidden('solded','1');
677
678 if (isset($_REQUEST['from_poste']))
679 echo HtmlInput::hidden('from_poste',$_REQUEST['from_poste']);
680
681 if (isset($_REQUEST['to_poste']))
682 echo HtmlInput::hidden('to_poste',$_REQUEST['to_poste']);
683
684 if (isset($_REQUEST['poste_id']))
685 echo HtmlInput::hidden("poste_id",$_REQUEST['poste_id']);
686
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');
691
692 echo "</form></TD>";
693 $id=uniqid("csv_");
694 echo '<TD><form method="GET" ACTION="export.php" id="'.$id.'" onsubmit="download_document_form(\''.$id.'\')">'.
695 dossier::hidden().
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']);
702
703 if (isset($_REQUEST['from_poste']))
704 echo HtmlInput::hidden('from_poste',$_REQUEST['from_poste']);
705
706 if (isset($_REQUEST['to_poste']))
707 echo HtmlInput::hidden('to_poste',$_REQUEST['to_poste']);
708
709 if (isset($_REQUEST['poste_id']))
710 echo HtmlInput::hidden("poste_id",$_REQUEST['poste_id']);
711
712 if ( isset($_REQUEST['letter'] )) echo HtmlInput::hidden('letter','2');
713 if ( isset($_REQUEST['solded'] )) echo HtmlInput::hidden('solded','1');
714
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']);
720
721 echo "</form></TD>";
722 echo '<td style="vertical-align:top">';
723 echo HtmlInput::print_window();
724 echo '</td>';
725 echo '</tr>';
726 echo "</table>";
727
728
729 }
730 /*!
731 * \brief verify that the accounting belong to a ledger
732 *
733 * \return 0 ok, -1 no
734 */
735 function belong_ledger($p_jrn)
736 {
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) == '')
739 return 0;
740
741 $valid_cred=explode(" ",$filter);
742 $sql="select count(*) as poste from tmp_pcmn where ";
743 // Creation query
744 $or="";
745 $SqlFilter="";
746 $SqlArray = array();
747 $SqlArrayN = 1;
748 foreach ( $valid_cred as $item_cred)
749 {
750 if ( strlen (trim($item_cred)))
751 {
752 if ( strstr($item_cred,"*") == true )
753 {
754 $SqlItem=$or . 'pcm_val::text like $' . $SqlArrayN++;
755 array_push($SqlArray, strtr($item_cred,"*","%"));
756 $or=" or ";
757 }
758 else
759 {
760 $SqlItem=$or . 'pcm_val::text = $' . $SqlArrayN++;
761 array_push($SqlArray, $item_cred);
762 $or=" or ";
763 }
764 $SqlFilter=$SqlFilter.$SqlItem;
765 }
766 }//foreach
767 $sql.=$SqlFilter . ' and pcm_val::text=$' . $SqlArrayN++;
768 array_push($SqlArray, $this->id);
769 $max=$this->db->get_value($sql, $SqlArray);
770 if ($max > 0 )
771 return 0;
772 else
773 return -1;
774 }
775 /*!\brief With the id of the ledger, get the col jrn_def_class_deb
776 *\param $p_jrn jrn_id
777 *\return array of value, or an empty array if nothing is found
778 *\note
779 *\see
780 */
782 {
783 $l=new Acc_Ledger($this->db,$p_jrn);
784 $row=$l->get_propertie();
785 if ($l->get_type() != 'ODS') { return [];}
786 if ( $row == null || noalyss_strlentrim($row['jrn_def_class_deb']) == 0 ) return array();
787 $valid_account=explode(" ",$row['jrn_def_class_deb']);
788 return $valid_account;
789 }
790 /*!\brief build a sql statement thanks a array found with get_account_ledger
791 *
792 *\param $p_jrn jrn_id
793 *\return an emty string if nothing is found or a valid SQL statement like
794 \code
795 pcm_val like ... or pcm_val like ...
796 \endcode
797 *\note
798 *\see get_account_ledger
799 */
801 {
802 $array=$this->get_account_ledger($p_jrn);
803 if ( empty($array) ) return "";
804 $sql="";
805 foreach ( $array as $item_cred)
806 {
807 if ( strlen (trim($item_cred))>0 )
808 {
809 if ( strstr($item_cred,"*") == true )
810 {
811 $item_cred=strtr($item_cred,"*","%");
812 $sql_tmp=" pcm_val::text like '$item_cred' or";
813 }
814 else
815 {
816 $sql_tmp=" pcm_val::text = '$item_cred' or";
817 }
818 $sql.=$sql_tmp;
819 }
820 }//foreach
821 /* remove the last or */
822 $sql=substr($sql,0,strlen($sql)-2);
823 return $sql;
824 }
825 /**
826 * Find the id of the cards which are using the current account
827 *
828 * @return an array of f_id
829 */
830 function find_card()
831 {
832 $sql="select f_id from fiche_detail where ad_id=$1 and ad_value=$2";
833 $account=$this->db->get_array($sql,array(ATTR_DEF_ACCOUNT,$this->id));
834 return $account;
835 }
836 /**
837 * @brief Return a string with the HTML code to display a button to export the
838 * history in CSV
839 * @param type $p_from from date (DD.MM.YYYY)
840 * @param type $p_to to date (DD.MM.YYYY)
841 * @return HTML string
842 */
843 function button_csv($p_from,$p_to) {
844 $href="export.php?".http_build_query(
845 array(
846 "gDossier"=>Dossier::id(),
847 "poste_id"=>$this->id,
848 "ople"=>0,
849 "type"=>"poste",
850 "from_periode"=>$p_from,
851 "to_periode"=>$p_to,
852 "act"=>"CSV:postedetail"
853 )
854 );
855 return '<a class="smallbutton" style="display:inline-block" href="'.$href.'">'._("Export CSV").'</a>';
856
857 }
858 /**
859 * @brief Return a string with the HTML code to display a button to export the
860 * history in PDF
861 * @param type $p_from from date (DD.MM.YYYY)
862 * @param type $p_to to date (DD.MM.YYYY)
863 * @return HTML string
864 */
865 function button_pdf($p_from,$p_to) {
866 $href="export.php?".http_build_query(
867 array(
868 "gDossier"=>Dossier::id(),
869 "poste_id"=>$this->id,
870 "ople"=>0,
871 "type"=>"poste",
872 "from_periode"=>$p_from,
873 "to_periode"=>$p_to,
874 "act"=>"PDF:postedetail"
875 )
876 );
877 return '<a class="smallbutton" style="display:inline-block" href="'.$href.'">'._("Export PDF").'</a>';
878
879 }
880 /**
881 * @brief Filter in javascript the table with the history
882 * @param type $p_table_id id of the table containting the data to filter
883 * @return html string
884 */
885 function filter_history($p_table_id) {
886 return _('Filtre rapide').' '.HtmlInput::filter_table($p_table_id, '0,1,2,3,4,5,6,7,8,9,10', 1);
887 }
888
889 public static function get_used_accounting($from_date, $to_date, $from_accounting, $to_accounting)
890 {
891 // check date
893 return array();
894 }
895 // build query
896 $sql = "select pcm_val,pcm_lib from tmp_pcmn
897 where pcm_val in
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') ) ";
900 $cond_poste="";
901 if ($from_accounting != '') {
902 $cond_poste .= "and pcm_val >= upper ('" . Database::escape_string($from_accounting) . "')";
903 }
904
905 if ($to_accounting != '') {
906 $cond_poste .= " and pcm_val <= upper ('" . Database::escape_string($to_accounting) . "')";
907 }
908
909 $sql = $sql . $cond_poste . ' order by pcm_val::text';
910
911 // get array
913 $a_poste = $cn->get_array($sql);
914 // return array
915 return $a_poste;
916
917 }
918}
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
Definition: ac_common.php:852
th($p_string, $p_extra='', $raw='')
Definition: ac_common.php:58
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.
Definition: ac_common.php:682
isDate($p_date)
Definition: ac_common.php:236
noalyss_strlentrim($p_string)
Definition: ac_common.php:1549
noalyss_str_replace($search, $replace, $string)
Definition: ac_common.php:1553
td($p_string='', $p_extra='')
surround the string with td
Definition: ac_common.php:83
nbm($p_number, $p_dec=2)
format the number with a sep.
Definition: ac_common.php:137
global $g_parameter
global $g_user
if no group available , then stop
return false Description text align
$href
Definition: adm.inc.php:31
catch(Exception $exc) if(! $g_user->can_write_action($ag_id)) $r
$op
Definition: ajax_admin.php:38
h( $row[ 'oa_description'])
$idx
$ret label
$from_div
$anc_grandlivre from
foreach( $array as $item)
$_REQUEST['ac']
for($i=0; $i< count($plan); $i++)( $j==0) $a_poste
margin jrn_def_id
$filter
$from_poste name
$input_from id
Definition: balance.inc.php:63
$input_from type
Definition: balance.inc.php:65
$class
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.
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
static connect()
static filter_table($p_table_id, $p_col, $start_row)
filter the rows in a table and keep the colored row in alternance
static card_detail($p_qcode, $pname='', $p_style="", $p_nohistory=false, $nofollowup=false)
show the detail of a card
static hidden($p_name, $p_value, $p_id="")
static show_reconcile($p_div, $let, $span="")
static submit($p_name, $p_value, $p_javascript="", $p_class="smallbutton")
Html Input.
API for creating PDF, unicode, based on tfpdf.
Definition: pdf.class.php:34
$all table
const ATTR_DEF_ACCOUNT
Definition: constant.php:215
$Res
foreach( $Fiche->row as $op) $solde_type
$SecUser db
$side