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