noalyss Version-9
acc_reconciliation.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
20// Copyright Author Dany De Bontridder danydb@aevalys.eu
21
22/*!\file
23 * \brief class acc_reconciliation, this class is new and the code
24 * must use it
25 *
26 */
27
28/*! \brief new class for managing the reconciliation it must be used
29 * instead of the function InsertRapt, ...
30 *
31 */
33{
34 var $db; /*!< database connection */
35 var $jr_id; /*!< jr_id */
36 var $a_jrn;
39 function __construct($cn)
40 {
41 $this->db=$cn;
42 $this->jr_id=0;
43 $this->a_jrn=null;
44 }
45
46 function set_jr_id($jr_id)
47 {
48 $this->jr_id=$jr_id;
49 }
50 /*! \brief return a widget of type js_concerned
51 */
52 function widget()
53 {
54 $wConcerned=new IConcerned();
55 $wConcerned->extra=0; // with 0 javascript search from e_amount... field (see javascript)
56
57 return $wConcerned;
58
59 }
60 /*!
61 *\brief Insert into jrn_rapt the concerned operations
62 *
63 * \param $jr_id2 (jrn.jr_id) => jrn_rapt.jra_concerned or a string
64 * like "jr_id2,jr_id3,jr_id4..."
65 *
66 * \return none
67 *
68 */
69 function insert($jr_id2)
70 {
71 if ( trim($jr_id2) == "" )
72 return;
73 if ( strpos($jr_id2,',') !== 0 )
74 {
75 $aRapt=explode(',',$jr_id2);
76 foreach ($aRapt as $rRapt)
77 {
78 if ( isNumber($rRapt) == 1 )
79 {
80 $this->insert_rapt($rRapt);
81 }
82 }
83 }
84 else
85 if ( isNumber($jr_id2) == 1 )
86 {
87 $this->insert_rapt($jr_id2);
88 }
89 }
90
91 /*!
92 *\brief Insert into jrn_rapt the concerned operations
93 * should not be called directly, use insert instead
94 *
95 * \param $jr_id2 (jrn.jr_id) => jrn_rapt.jra_concerned
96 *
97 * \return none
98 *
99 */
100 function insert_rapt($jr_id2)
101 {
102 if ( isNumber($this->jr_id) == 0 || isNumber($jr_id2) == 0 )
103 {
104 return false;
105 }
106 if ( $this->jr_id==$jr_id2)
107 return true;
108
109 if ( $this->db->count_sql("select jr_id from jrn where jr_id=".$this->jr_id)==0 )
110 return false;
111 if ( $this->db->count_sql("select jr_id from jrn where jr_id=".$jr_id2)==0 )
112 return false;
113
114 // verify if exists
115 if ( $this->db->count_sql(
116 "select jra_id from jrn_rapt where jra_concerned=".$this->jr_id.
117 " and jr_id=$jr_id2
118 union
119 select jra_id from jrn_rapt where jr_id=".$this->jr_id.
120 " and jra_concerned=$jr_id2 ")
121 ==0)
122 {
123 // Ok we can insert
124 $Res=$this->db->exec_sql("insert into jrn_rapt(jr_id,jra_concerned) values ".
125 "(".$this->jr_id.",$jr_id2)");
126 // try to letter automatically same account from both operation
127 $this->auto_letter($jr_id2);
128
129 // update date of paiement -----------------------------------------------------------------------
130 $source_type=$this->db->get_value("select substr(jr_internal,1,1) from jrn where jr_id=$1",array($this->jr_id));
131 $dest_type=$this->db->get_value("select substr(jr_internal,1,1) from jrn where jr_id=$1",array($jr_id2));
132 if (($source_type =='A' || $source_type=='V') && ($dest_type != 'A' && $dest_type != 'V'))
133 {
134 // set the date on source
135 $date=$this->db->get_value('select jr_date from jrn where jr_id=$1',array($jr_id2));
136 if ( trim ($date) == '') $date=null;
137 $this->db->exec_sql('update jrn set jr_date_paid=$1 where jr_id=$2 and jr_date_paid is null ',array($date,$this->jr_id));
138 }
139 if (($source_type !='A' && $source_type !='V') && ($dest_type == 'A' || $dest_type == 'V'))
140 {
141 // set the date on dest
142 $date=$this->db->get_value('select jr_date from jrn where jr_id=$1',array($this->jr_id));
143 if (trim($date) == '') $date=null;
144 $this->db->exec_sql('update jrn set jr_date_paid=$1 where jr_id=$2 and jr_date_paid is null ',array($date,$jr_id2));
145 }
146 }
147 return true;
148 }
149 /**
150 * @brief try to letter same card between $p_jrid and $this->jr_id
151 * @param jrn.jr_id $p_jrid the operation to reconcile
152 */
153 function auto_letter($p_jrid)
154 {
155 // Try to find same card from both operation
156 $sql="select j1.f_id as fiche ,coalesce(j1.j_id,-1) as jrnx_id1,coalesce(j2.j_id,-1) as jrnx_id2,
157j1.j_poste as poste
158 from jrnx as j1
159 join jrn as jr1 on (j1.j_grpt=jr1.jr_grpt_id)
160 join jrnx as j2 on (coalesce(j1.f_id,-1)=coalesce(j2.f_id,-1) and j1.j_poste=j2.j_poste)
161 join jrn as jr2 on (j2.j_grpt=jr2.jr_grpt_id)
162 where
163 jr1.jr_id=$1
164 and
165 jr2.jr_id= $2";
166 $result=$this->db->get_array($sql,array($this->jr_id,$p_jrid));
167 if ( count($result) == 0)
168 {
169 return;
170 }
171 for ($i=0;$i<count($result);$i++)
172 {
173 if ( $result[$i]['fiche'] != -1)
174 {
175 $letter = new Lettering_Card($this->db);
176 $letter->insert_couple($result[$i]['jrnx_id1'],$result[$i]['jrnx_id2']);
177 }
178 else
179 {
180 $letter = new Lettering_Account($this->db);
181 $letter->insert_couple($result[$i]['jrnx_id1'],$result[$i]['jrnx_id2']);
182 }
183 }
184
185 }
186
187 /*!
188 *\brief Insert into jrn_rapt the concerned operations
189 *
190 * \param $this->jr_id (jrn.jr_id) => jrn_rapt.jr_id
191 * \param $jr_id2 (jrn.jr_id) => jrn_rapt.jra_concerned
192 *
193 * \return none
194 */
195 function remove($jr_id2)
196 {
197 if ( isNumber($this->jr_id) == 0 or
198 isNumber($jr_id2) == 0 )
199 {
200 return;
201 }
202 // verify if exists
203 if ( $this->db->count_sql("select jra_id from jrn_rapt where ".
204 " jra_concerned=".$this->jr_id." and jr_id=$jr_id2
205 union
206 select jra_id from jrn_rapt where jra_concerned=$jr_id2 ".
207 " and jr_id=".$this->jr_id) !=0)
208 {
209 /**
210 * remove also lettering between both operation
211 */
212 $sql = " delete from
213 jnt_letter
214 where jl_id in ( select jl_id from jnt_letter
215 join letter_cred as lc using(jl_id)
216 join letter_deb as ld using (jl_id)
217 where
218 lc.j_id in (select j_id
219 from jrnx join jrn on (j_grpt=jr_grpt_id)
220 where jr_id in ($1,$2))
221 or
222 ld.j_id in (select j_id
223 from jrnx join jrn on (j_grpt=jr_grpt_id)
224 where jr_id in ($1,$2))
225
226
227
228 )";
229 $this->db->exec_sql($sql, array($jr_id2, $this->jr_id));
230 // Ok we can delete
231 $Res=$this->db->exec_sql("delete from jrn_rapt where ".
232 "(jra_concerned=$jr_id2 and jr_id=".$this->jr_id.") or
233 (jra_concerned=".$this->jr_id." and jr_id=$jr_id2) ");
234 }
235 }
236
237 /*!
238 *\brief Return an array of the concerned operation
239 *
240 *
241 *\param database connection
242 * \return array if something is found or null
243 */
244 function get ( )
245 {
246 $sql=" select jr_id as cn from jrn_rapt where jra_concerned=$1
247 union
248 select jra_concerned as cn from jrn_rapt where jr_id=$2";
249 $Res=$this->db->exec_sql($sql,array($this->jr_id,$this->jr_id));
250
251 // If nothing is found return null
253
254 if ($n ==0 ) return [];
255
256 // put everything in an array
257 for ($i=0;$i<$n;$i++)
258 {
260 $r[$i]=$l['cn'];
261 }
262 return $r;
263 }
264 function fill_info()
265 {
266 $sql="select jr_id,jr_date,jr_comment,jr_internal,jr_montant,jr_pj_number,jr_def_id,jrn_def_name,jrn_def_type
267 from jrn join jrn_def on (jrn_def_id=jr_def_id)
268 where jr_id=$1";
269 $a=$this->db->get_array($sql,array($this->jr_id));
270 return $a[0];
271 }
272 /**
273 *@brief return array of not-reconciled operation
274 * Prepare and put in memory the SQL detail_quant
275 */
277 {
278 $filter_date=$this->filter_date();
279 /* create ledger filter */
280 $sql_jrn=$this->ledger_filter();
281
282 $array=$this->db->get_array("select distinct jr_id,jr_date from jrn where $filter_date and $sql_jrn and jr_id not in (select jr_id from jrn_rapt union select jra_concerned from jrn_rapt) order by jr_date");
283 $ret=array();
284 for ($i=0;$i<count($array);$i++)
285 {
286 $this->jr_id=$array[$i]['jr_id'];
287 $ret[$i]['first']=$this->fill_info();
288 }
289 return $ret;
290 }
291 /**
292 *Create a sql condition to filter by security and by asked ledger
293 * based on $this->a_jrn
294 *@return a valid sql stmt to include
295 *@see get_not_reconciled get_reconciled
296 */
297 function ledger_filter ()
298 {
299 global $g_user;
300 /* get the available ledgers for current user */
301 $sql=$g_user->get_ledger_sql('ALL',3);
302 $sql=noalyss_str_replace('jrn_def_id','jr_def_id',$sql);
303 $r='';
304 /* filter by this->r_jrn */
305 if ( ! empty ($this->a_jrn ) && is_array($this->a_jrn))
306 {
307 $sep='';
308 $r='and jr_def_id in (';
309 foreach( $this->a_jrn as $key=>$value)
310 {
311 $r.=$sep.$value;
312 $sep=',';
313 }
314 $r.=')';
315 }
316 return $sql.' '.$r;
317 }
318 /**
319 *@brief return array of reconciled operation
320 * Prepare and put in memory the SQL detail_quant
321 *@return
322 *@note
323 *@see
324 @code
325
326 @endcode
327 */
328 function get_reconciled()
329 {
330 $filter_date=$this->filter_date();
331
332
333 /* create ledger filter */
334 $sql_jrn=$this->ledger_filter();
335
336 $array=$this->db->get_array("select distinct jr_id,jr_date from jrn where $filter_date and $sql_jrn and jr_id in (select jr_id from jrn_rapt union select jra_concerned from jrn_rapt) order by jr_date");
337 $ret=array();
338 for ($i=0;$i<count($array);$i++)
339 {
340 $this->jr_id=$array[$i]['jr_id'];
341 $ret[$i]['first']=$this->fill_info();
342 $atmp=$this->get();
343 for ( $e=0;$e<count($atmp);$e++)
344 {
345 $this->jr_id=$atmp[$e];
346 $ret[$i]['depend'][$e]=$this->fill_info();
347 }
348 }
349
350 return $ret;
351 }
352 /**
353 *@brief
354 * Prepare and put in memory the SQL detail_quant
355 *@param
356 *@return
357 *@note
358 *@see
359 @code
360
361 @endcode
362 */
363 function get_reconciled_amount($p_equal=false)
364 {
365 $array=$this->get_reconciled();
366 $ret=array();
367 bcscale(2);
369 for ($i=0;$i<count($array);$i++)
370 {
371
372 $retdb=$this->db->execute("detail_quant",array($array[$i]['first']['jr_id']));
373 if ( Database::num_row($retdb) != 0)
374 {
375 // then second_amount takes in account the vat_sided
377 $total_price=0;
378 foreach ($a_row as $row) {
379 $total_price=bcadd($total_price,$row['price']);
380 $total_price=bcadd($total_price,$row['vat_amount']);
381 $total_price=bcsub($total_price,$row['vat_sided']);
382 $total_price=bcadd($total_price,$row['nd_amount']);
383 $total_price=bcadd($total_price,$row['nd_tva_recup']);
384
385 }
386 $first_amount=$total_price;
387
388 } else {
389 // else take the amount from jrn
390 $first_amount=$array[$i]['first']['jr_montant'];
391 }
392 $second_amount=0;
393 for ($e=0;$e<count($array[$i]['depend']);$e++)
394 {
395 $retdb=$this->db->execute("detail_quant",array($array[$i]['depend'][$e]['jr_id']));
396 // if exist in v_quant_detail
397 if ( Database::num_row($retdb) != 0)
398 {
399 // then second_amount takes in account the vat_sided
401 $total_price=0;
402 foreach ($a_row as $row) {
403 $total_price=bcadd($total_price,$row['price']);
404 $total_price=bcadd($total_price,$row['vat_amount']);
405 $total_price=bcsub($total_price,$row['vat_sided']);
406 $total_price=bcadd($total_price,$row['nd_amount']);
407 $total_price=bcadd($total_price,$row['nd_tva_recup']);
408
409 }
410 $second_amount=bcadd($second_amount,$total_price);
411
412 } else {
413 // else take the amount from jrn
414 $second_amount=bcadd($second_amount,$array[$i]['depend'][$e]['jr_montant']);
415 }
416 }
417 if ( $p_equal && $first_amount==$second_amount)
418 {
419 $ret[]=$array[$i];
420 }
421 if ( ! $p_equal && $first_amount != $second_amount)
422 {
423 $ret[]=$array[$i];
424 }
425 }
426 return $ret;
427 }
428 /**
429 *@brief create a string to filter thanks the date
430 *@return a sql string like jr_date > ... and jr_date < ....
431 *@note use the data member start_day and end_day
432 *@see get_reconciled get_not_reconciled
433 */
434 function filter_date()
435 {
436 global $g_user;
437 list($start,$end)=$g_user->get_limit_current_exercice();
438
439 if (isDate($this->start_day) ==null)
440 {
441 $this->start_day=$start;
442 }
443 if ( isDate($this->end_day) == null)
444 {
445 $this->end_day=$end;
446 }
447 $sql=" (jr_date >= to_date('".$this->start_day."','DD.MM.YYYY')
448 and jr_date <= to_date('".$this->end_day."','DD.MM.YYYY'))";
449 return $sql;
450
451 }
452 function show_detail($p_ret)
453 {
454 if (Database::num_row($p_ret)> 0)
455 {
456 echo '<tr class="odd">';
457 echo '<td></td>';
458 echo '<td colspan="5" style="border:1px solid black;width:auto">';
459 include NOALYSS_TEMPLATE.'/impress_reconciliation_detail.php';
460 echo '</td>';
461 echo '</tr>';
462 }
463 }
464 /**
465 * Export to CSV
466 * @param type $p_choice
467 *
468 * @note must be set before calling
469 * - $this->a_jrn array of ledger
470 * - $this->start_day start date
471 * - $this->end_day end date
472 * @see Acc_Reconciliation::get_data
473 */
474 function export_csv($p_choice)
475 {
476 $export=new Noalyss_Csv(_('rapprochement'));
477 $export->send_header();
478
479 $array = $this->get_data($p_choice);
480 for ($i = 0; $i < count($array); $i++)
481 {
482 // ---------------------------------------
483 // first index has 2 arrays : first & depend[]
484 // ---------------------------------------
485
486 $first = $array[$i]['first'];
487 $a_depend = array();
488 $title=array();
489 if (isset($array[$i]['depend']))
490 {
491 $a_depend = $array[$i]['depend'];
492 //----- HEADER ----
493 if ($i == 0)
494 {
495 $title[]=_('n°');
496 $title[]=_('Date');
497 $title[]=_('internal');
498 $title[]=_('libellé');
499 $title[]=_('pièce');
500 $title[]=_('journal');
501 $title[]=_('type journal');
502 $title[]=_('montant');
503 $title[]=_('<->');
504 $title[]=_('Date');
505 $title[]=_('Interne');
506 $title[]=_('libell');
507 $title[]=_('pièce');
508 $title[]=_('nom journal');
509 $title[]=_('type journal');
510 $title[]=_('montant');
511
512 }
513 }
514 else
515 {
516 //----- HEADER ----
517 if ($i == 0)
518 {
519 $title[]=_('n°');
520 $title[]=_('Date');
521 $title[]=_('interne');
522 $title[]=_('libellé');
523 $title[]=_('pièce');
524 $title[]=_('journal');
525 $title[]=_('type journal');
526 $title[]=_('montant');
527
528 }
529 }
530 $export->write_header($title);
531 //-----------------------------------------
532 //Retrieve amount without autoreversed VAT
533 //-----------------------------------------
534 $amount=$this->get_amount_noautovat($first['jr_id'],$first['jr_montant']);
535
536 // --------------------------
537 // Print First
538 // --------------------------
539 $export->add($i,"number");
540 $export->add($first['jr_date']);
541 $export->add($first['jr_internal']);
542 $export->add($first['jr_comment']);
543 $export->add($first['jr_pj_number']);
544 $export->add($first['jrn_def_name']);
545 $export->add($first['jrn_def_type']);
546 $export->add($amount,"number");
547 if (count($a_depend) > 0)
548 {
549 // --------------------------------------
550 // Print first depending operation
551 // --------------------------------------
552 $depend = $a_depend[0];
553 $export->add("<->");
554 $amount_dep=$this->get_amount_noautovat($depend['jr_id'],$depend['jr_montant']);
555 $export->add($depend['jr_date']);
556 $export->add($depend['jr_internal']);
557 $export->add($depend['jr_comment']);
558 $export->add($depend['jr_pj_number']);
559 $export->add($depend['jrn_def_name']);
560 $export->add($depend['jrn_def_type']);
561 $export->add($amount_dep,"number");
562 $export->write();
563 // --------------------------------------
564 // print other depending operation if any
565 // --------------------------------------
566 for ($e = 1; $e < count($a_depend); $e++)
567 {
568 $amount_dep=$this->get_amount_noautovat($depend['jr_id'],$depend['jr_montant']);
569 $depend = $a_depend[$e];
570 $export->add("");
571 $export->add("");
572 $export->add("");
573 $export->add("");
574 $export->add("");
575 $export->add("");
576 $export->add("");
577 $export->add("");
578 $export->add("<->");
579 $export->add($depend['jr_date']);
580 $export->add($depend['jr_internal']);
581 $export->add($depend['jr_comment']);
582 $export->add($depend['jr_pj_number']);
583 $export->add($depend['jrn_def_name']);
584 $export->add($depend['jrn_def_type']);
585 $export->add($amount_dep,"number");
586 $export->write();
587 }
588 }
589 else
590 {
591 $export->write();
592 }
593 }
594 }
595
596 /**
597 *
598 * @param type $p_choice
599 * - 0 : operation reconcilied
600 * - 1 : reconcilied with different amount
601 * - 2 : reconcilied with same amount
602 * - 3 : not reconcilied
603 * @return $array
604 */
605 function get_data($p_choice)
606 {
607 switch ($p_choice)
608 {
609 case 0:
610 $array = $this->get_reconciled();
611 break;
612 case 1:
613 $array = $this->get_reconciled_amount(false);
614 break;
615 case 2:
616 $array = $this->get_reconciled_amount(true);
617 break;
618 case 3:
619 $array = $this->get_not_reconciled();
620 break;
621 default:
622 echo "Choix invalid";
623 throw new Exception("invalide");
624 }
625 return $array;
626 }
628 {
629 static $seen=0;
630 if ( $seen == 1) return;
631 $this->db->prepare('detail_quant','select * from v_quant_detail where jr_id=$1');
632 $seen=1;
633 }
634 /**
635 * Retrieve the amount VAT included and autoreversed VAT excluded thanks
636 * the view v_quant_detail and return it.
637 * If the operation is not a sale or a purchase , it doesn't exist in the
638 * view then the function just returns the default amount
639 * @param type $p_jrn_id jrn.jr_id
640 * @param type $p_default_amount amount to return if not found in the view
641 * v_quant_detail
642 * @return number
643 */
644 function get_amount_noautovat($p_jrn_id,$p_default_amount) {
645 static $p=0;
646 if ( $p==0) {
648 $p=1;
649 }
650
651 $retdb=$this->db->execute("detail_quant",array($p_jrn_id));
652 if ( Database::num_row($retdb) != 0)
653 {
654 // then second_amount takes in account the vat_sided
655 $row=Database::fetch_array($retdb, 0);
656 $total_price=bcadd($row['price'],$row['vat_amount']);
657 $total_price=bcsub($total_price,$row['vat_sided']);
658 $first_amount=$total_price;
659
660 } else {
661 // else take the amount from jrn
662 $first_amount=$p_default_amount;
663 }
664 return $first_amount;
665
666 }
667 static function test_me()
668 {
671 var_dump($rap->get_reconciled_amount('',false));
672 }
673
674}
isNumber($p_int)
Definition: ac_common.php:215
isDate($p_date)
Definition: ac_common.php:236
noalyss_str_replace($search, $replace, $string)
Definition: ac_common.php:1553
global $g_user
if no group available , then stop
catch(Exception $exc) if(! $g_user->can_write_action($ag_id)) $r
$op jr_id
Definition: ajax_ledger.php:83
$p
Definition: array.php:34
new class for managing the reconciliation it must be used instead of the function InsertRapt,...
get_amount_noautovat($p_jrn_id, $p_default_amount)
Retrieve the amount VAT included and autoreversed VAT excluded thanks the view v_quant_detail and ret...
export_csv($p_choice)
Export to CSV.
get_not_reconciled()
return array of not-reconciled operation Prepare and put in memory the SQL detail_quant
get_reconciled()
return array of reconciled operation Prepare and put in memory the SQL detail_quant
widget()
return a widget of type js_concerned
get_reconciled_amount($p_equal=false)
Prepare and put in memory the SQL detail_quant.
auto_letter($p_jrid)
try to letter same card between $p_jrid and $this->jr_id
insert_rapt($jr_id2)
Insert into jrn_rapt the concerned operations should not be called directly, use insert instead.
ledger_filter()
Create a sql condition to filter by security and by asked ledger based on $this->a_jrn.
filter_date()
create a string to filter thanks the date
insert($jr_id2)
Insert into jrn_rapt the concerned operations.
static fetch_all($ret)
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 connect()
Html Input.
only for operation retrieved thanks a account (jrnx.j_poste) manage the accounting entries for a give...
only for operation retrieved thanks a quick_code manage the accounting entries for a given card
Manage the CSV : manage files and write CSV record.
$Res
$acc_reconciliation end_day
$acc_reconciliation start_day
$acc_reconciliation a_jrn
$SecUser db