noalyss  Version-6.9.1.8
 All Data Structures Namespaces Files Functions Variables Pages
class_lettering.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 letter the accounting entry (row level)
24  */
25 require_once NOALYSS_INCLUDE.'/class/class_user.php';
26 
27 /**
28  *@brief mother class for the lettering by account and by card
29  * use the tables jnt_letter, letter_deb and letter_cred
30  * - "account"=>"account", => the accounting of the j_id (use by Lettering_Account)
31  * - "quick_code"=>"quick_code", => the quick_code of the j_id (used by Lettering_Card)
32  * - "start"=>"start", => date of the first day
33  * - "end"=>"end", => date of the last day
34  * - "sql_ledger"=>"sql_ledger" => the sql clause to filter on the available ledgers
35 */
36 class Lettering
37 {
38 
39  protected $variable=array("account"=>"account", /* the accounting of the j_id (use by Lettering_Account) */
40  "quick_code"=>"quick_code", /* the quick_code of the j_id (used by Lettering_Card) */
41  "start"=>"start", /* date of the first day */
42  "end"=>"end", /* date of the last day */
43  "sql_ledger"=>"sql_ledger" /* the sql clause to filter on the available ledgers */
44  )
45  ;
46  /**
47  * constructor
48  *@param $p_init resource to database
49  *@note by default start and end are the 1.1.exercice to 31.12.exercice
50  */
51  function __construct ($p_init)
52  {
53  $this->db=$p_init;
54  $a=new User($p_init);
55  $exercice=$a->get_exercice();
56  $this->start='01.01.'.$exercice;
57  $this->end='31.12.'.$exercice;
58  // available ledgers
59  $this->sql_ledger=str_replace('jrn_def_id','jr_def_id',$a->get_ledger_sql('ALL',3));
60 
61  }
62  public function get_parameter($p_string)
63  {
64  if ( array_key_exists($p_string,$this->variable) )
65  {
66  $idx=$this->variable[$p_string];
67  return $this->$idx;
68  }
69  else
70  throw new Exception (__FILE__.":".__LINE__.$p_string.'Erreur attribut inexistant');
71  }
72  public function set_parameter($p_string,$p_value)
73  {
74  if ( array_key_exists($p_string,$this->variable) )
75  {
76  $idx=$this->variable[$p_string];
77  $this->$idx=$p_value;
78  }
79  else
80  throw new Exception (__FILE__.":".__LINE__.$p_string.'Erreur attribut inexistant');
81  }
82  /**
83  *Use to just insert a couple of lettered operation
84  */
85  function insert_couple($j_id1,$j_id2)
86  {
87 
88  /* take needed data */
89  $first=$this->db->get_value('select j_debit from jrnx where j_id=$1',array($j_id1));
90  if ( $this->db->count() == 0 ) throw new Exception ('OpĂ©ration non existante');
91 
92  $second=$this->db->get_value('select j_debit from jrnx where j_id=$1',array($j_id2));
93  if ( $this->db->count() == 0 ) throw new Exception ('OpĂ©ration non existante');
94  $sql_already="select distinct(jl_id)
95  from jnt_letter
96  left outer join letter_deb using (jl_id)
97  left outer join letter_cred using (jl_id)
98  where
99  letter_deb.j_id = $1 or letter_cred.j_id=$1";
100  $let1=0;$let2=0;
101  $already=$this->db->get_array($sql_already,array($j_id1));
102  if ( count ($already ) > 0) {
103  if ( count($already)==1) {
104  // retrieve the letter
105  $let1=$this->db->get_value("select distinct(jl_id)
106  from jnt_letter
107  left outer join letter_deb using (jl_id)
108  left outer join letter_cred using (jl_id)
109  where
110  letter_deb.j_id = $1 or letter_cred.j_id=$1",array($j_id1));
111  }else
112  {
113  return;
114  }
115  }
116 
117  $already=$this->db->get_array($sql_already,array($j_id2));
118  if ( count ($already ) > 0) {
119  if ( count($already)==1) {
120  // retrieve the letter
121  $let2=$this->db->get_value("select distinct(jl_id)
122  from jnt_letter
123  left outer join letter_deb using (jl_id)
124  left outer join letter_cred using (jl_id)
125  where
126  letter_deb.j_id = $1 or letter_cred.j_id=$1",array($j_id2));
127  }else {
128  return;
129  }
130  }
131  $jl_id=0;
132  // already linked together
133  if ( $let1 != 0 && $let1 == $let2 )return;
134 
135  // already linked
136  if ( $let1 != 0 && $let2!=0 && $let1 != $let2 )return;
137 
138  // none is linked
139  if ( $let1 == 0 && $let2==0)
140  {
141  $jl_id=$this->db->get_next_seq("jnt_letter_jl_id_seq");
142  $this->db->exec_sql('insert into jnt_letter(jl_id) values($1)',
143  array($jl_id));
144  }
145  // one is linked but not the other
146  if ( $let1 == 0 && $let2 != 0 ) $jl_id=$let2;
147  if ( $let1 != 0 && $let2 == 0 ) $jl_id=$let1;
148 
149  /* insert */
150  if ( $first == 't')
151  {
152  // save into letter_deb
153  if ($let1 == 0) $ld_id=$this->db->get_value('insert into letter_deb(j_id,jl_id) values($1,$2) returning ld_id',array($j_id1,$jl_id));
154  }
155  else
156  {
157  if ($let1 == 0)$lc_id=$this->db->get_value('insert into letter_cred(j_id,jl_id) values($1,$2) returning lc_id',array($j_id1,$jl_id));
158  }
159  if ( $second == 't')
160  {
161  // save into letter_deb
162  if ($let2 == 0)$ld_id=$this->db->get_value('insert into letter_deb(j_id,jl_id) values($1,$2) returning ld_id',array($j_id2,$jl_id));
163  }
164  else
165  {
166  if ($let2 == 0)$lc_id=$this->db->get_value('insert into letter_cred(j_id,jl_id) values($1,$2) returning lc_id',array($j_id2,$jl_id));
167  }
168 
169  }
170  public function get_info()
171  {
172  return var_export(self::$variable,true);
173  }
174  public function verify()
175  {
176  // Verify that the elt we want to add is correct
177  }
178  /**
179  *@brief save from array
180  *@param $p_array
181  @code
182  'gDossier' => string '13' (length=2)
183  'letter_j_id' =>
184  ck => array
185  @endcode
186  */
187  public function save($p_array)
188  {
189  if ( ! isset ($p_array['letter_j_id'])) return;
190  $this->db->exec_sql('delete from jnt_letter where jl_id=$1',array($p_array['jnt_id']));
191 
192  $this->db->start();
193  $jl_id=$this->db->get_next_seq("jnt_letter_jl_id_seq");
194  $this->db->exec_sql('insert into jnt_letter(jl_id) values($1)',
195  array($jl_id));
196 
197  // save the source
198  $deb=$this->db->get_value('select j_debit,j_montant from jrnx where j_id=$1',array($p_array['j_id']));
199  if ( $deb == 't')
200  {
201  // save into letter_deb
202  $ld_id=$this->db->get_value('insert into letter_deb(j_id,jl_id) values($1,$2) returning ld_id',array($p_array['j_id'],$jl_id));
203  }
204  else
205  {
206  $lc_id=$this->db->get_value('insert into letter_cred(j_id,jl_id) values($1,$2) returning lc_id',array($p_array['j_id'],$jl_id));
207  }
208  $count=0;
209  // save dest
210  for($i=0;$i<count($p_array['letter_j_id']);$i++)
211  {
212  if (isset ($p_array['ck'][$i]) && $p_array['ck'][$i] !="-2")
213  { //if 1
214  // save the dest
215  $deb=$this->db->get_value('select j_debit,j_montant from jrnx where j_id=$1',array($p_array['ck'][$i]));
216  if ( $deb == 't')
217  {
218  $count++;
219  // save into letter_deb
220  $ld_id=$this->db->get_value('insert into letter_deb(j_id,jl_id) values($1,$2) returning ld_id',array($p_array['ck'][$i],$jl_id));
221  }
222  else
223  {
224  $count++;
225  $lc_id=$this->db->get_value('insert into letter_cred(j_id,jl_id) values($1,$2) returning lc_id',array($p_array['ck'][$i],$jl_id));
226  }
227  } //end if 1
228  } //end for
229  // save into jnt_letter
230  /* if only one row we delete the joint */
231  if ( $count==0)
232  {
233  $this->db->rollback();
234  }
235  $this->db->commit();
236  }
237  /**
238  *@brief retrieve * row thanks a condition
239  */
240  public function seek($cond,$p_array=null)
241  {
242  /*
243  $sql="select * from * where $cond";
244  return $this->cn->get_array($cond,$p_array)
245  */
246  }
247  public function insert()
248  {
249  if ( $this->verify() != 0 ) return;
250 
251  }
252  /**
253  *show all the record from jrnx and their status (linked or not)
254  *it fills the array $this->content
255  */
256  protected function show_all()
257  {
258  $this->get_all();
259  $r="";
260  ob_start();
261  include(NOALYSS_TEMPLATE.'/letter_all.php');
262  $r=ob_get_contents();
263  ob_end_clean();
264  return $r;
265  }
266  function get_linked($p_jlid)
267  {
268  $sql="select j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,
269  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,jr_pj_number,
270  coalesce(comptaproc.get_letter_jnt(j_id),-1) as letter
271  from jrnx join jrn on (j_grpt = jr_grpt_id)
272  where
273  j_id in (select j_id from letter_cred where jl_id=$1
274  union all
275  select j_id from letter_deb where jl_id=$1)
276  order by j_date";
277 
278  $this->linked=$this->db->get_array($sql,array($p_jlid));
279  }
280  /**
281  *show only the lettered records from jrnx
282  *it fills the array $this->content
283  */
284  protected function show_lettered()
285  {
286  $this->get_letter();
287  $r="";
288  ob_start();
289  include(NOALYSS_TEMPLATE.'/letter_all.php');
290  $r=ob_get_contents();
291  ob_end_clean();
292  return $r;
293  }
294  /**
295  *show only the lettered records from jrnx
296  *it fills the array $this->content
297  */
298  protected function show_lettered_diff()
299  {
300  $this->get_letter_diff();
301  $r="";
302  ob_start();
303  include(NOALYSS_TEMPLATE.'/letter_all.php');
304  $r=ob_get_contents();
305  ob_end_clean();
306  return $r;
307  }
308 
309  /**
310  *show only the not lettered records from jrnx
311  *it fills the array $this->content
312  */
313 
314  protected function show_not_lettered()
315  {
316  $this->get_unletter();
317  $r="";
318  ob_start();
319  include(NOALYSS_TEMPLATE.'/letter_all.php');
320  $r=ob_get_contents();
321  ob_end_clean();
322  return $r;
323  }
324  /**
325  *wrapper : it call show_all, show_lettered or show_not_lettered depending
326  * of the parameter
327  *@param $p_type poss. values are all, unletter, letter
328  */
329  public function show_list($p_type)
330  {
331  switch($p_type)
332  {
333  case 'all':
334  return $this->show_all();
335  break;
336  case 'unletter':
337  return $this->show_not_lettered();
338  break;
339  case 'letter':
340  return $this->show_lettered();
341  break;
342  case 'letter_diff':
343  return $this->show_lettered_diff();
344  break;
345  }
346  throw new Exception ("[$p_type] is no unknown");
347  }
348 
349  public function show_letter($p_jid)
350  {
351  $j_debit=$this->db->get_value('select j_Debit from jrnx where j_id=$1',array($p_jid));
352  $amount_init=$this->db->get_value('select j_montant from jrnx where j_id=$1',array($p_jid));
353 
354  $this->get_filter($p_jid);
355  // retrieve jnt_letter.id
356  $sql="select distinct(jl_id) from jnt_letter left outer join letter_deb using (jl_id) left outer join letter_cred using (jl_id)
357  where letter_deb.j_id = $1 or letter_cred.j_id=$2";
358  $a_jnt_id=$this->db->get_array($sql,array($p_jid,$p_jid));
359 
360  if (count($a_jnt_id)==0 )
361  {
362  $jnt_id=-2;
363  } else
364  {
365  $jnt_id=$a_jnt_id[0]['jl_id'];
366  }
367  $this->get_linked($jnt_id);
368  ob_start();
369  require_once NOALYSS_TEMPLATE.'/letter_prop.php';
370  $r=ob_get_contents();
371  ob_end_clean();
372  $r.=HtmlInput::hidden('j_id',$p_jid);
373  $r.=HtmlInput::hidden('jnt_id',$jnt_id);
374 
375  return $r;
376  }
377 
378  public function update()
379  {
380  if ( $this->verify() != 0 ) return;
381  }
382 
383  public function load()
384 {}
385 
386  public function delete()
387  {
388  throw new Exception ('delete not implemented');
389  }
390  /**
391  * Unit test for the class
392  */
393  static function test_me()
394  {}
395 
396 }
397 /**
398  * only for operation retrieved thanks a account (jrnx.j_poste)
399  * manage the accounting entries for a given account
400  */
401 
403 {
404  function __construct($p_init,$p_account=null)
405  {
406  parent::__construct($p_init);
407  $this->account=$p_account;
408  $this->object_type='account';
409  }
410 
411  /**
412  * fills the this->content, datas are filtered thanks
413  * - fil_deb poss values t (debit), f(credit), ' ' (everything)
414  * - fil_amount_max max amount
415  * - fil_amount_min min amount
416  * - $this->start min date
417  * - $this->end max date
418  * - this->account: accounting
419  */
420  public function get_filter($p_jid=0)
421  {
422  $filter_deb='';
423  if (isset($this->fil_deb))
424  {
425  switch ($this->fil_deb)
426  {
427  case 0:
428  $filter_deb=" and j_debit='t' ";
429  break;
430  case 1:
431  $filter_deb=" and j_debit='f' ";
432  break;
433  case 2:
434  $filter_deb=" ";
435  break;
436  }
437 
438  }
439  $filter_amount="";
440  if ( isset ($this->fil_amount_max ) &&
441  isset ($this->fil_amount_min ) &&
442  isNumber($this->fil_amount_max)==1 &&
443  isNumber($this->fil_amount_min)==1 &&
444  ($this->fil_amount_max != 0 || $this->fil_amount_min != 0) )
445  $filter_amount=" and (j_montant >= $this->fil_amount_min and j_montant<=$this->fil_amount_max or (coalesce(comptaproc.get_letter_jnt($p_jid),-1)= coalesce(comptaproc.get_letter_jnt(j_id),-1) and coalesce(comptaproc.get_letter_jnt($p_jid),-1) <> -1 )) ";
446  $sql="
447  select j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,
448  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
449  coalesce(comptaproc.get_letter_jnt(j_id),-1) as letter,
450  jr_pj_number
451  from jrnx join jrn on (j_grpt = jr_grpt_id)
452  where j_poste = $1 and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
453  and $this->sql_ledger
454  $filter_deb
455  $filter_amount
456  order by j_date,j_id";
457 
458  $this->content=$this->db->get_array($sql,array($this->account,$this->start,$this->end));
459  }
460 
461  /**
462  * fills this->content with all the operation for the this->account(jrnx.j_poste)
463  */
464  public function get_all()
465  {
466  $sql=" with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
467  from
468  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
469  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
470  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
471  select j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
472  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
473  coalesce(let_diff.jl_id,-1) as letter,
474  diff_letter1 as letter_diff
475  from jrnx join jrn on (j_grpt = jr_grpt_id)
476  left join letter_jl using (j_id)
477  left join let_diff using (jl_id)
478  where j_poste = $1 and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
479  and $this->sql_ledger
480 
481  order by j_date,j_id";
482  $this->content=$this->db->get_array($sql,array($this->account,$this->start,$this->end));
483  }
484  /**
485  * same as get_all but only for lettered operation
486  */
487  public function get_letter()
488  {
489  $sql="
490  with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
491  from
492  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
493  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
494  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
495  select j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
496  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
497  let_diff.jl_id as letter,
498  diff_letter1 as letter_diff
499  from jrnx join jrn on (j_grpt = jr_grpt_id)
500  join letter_jl using (j_id)
501  left join let_diff using (jl_id)
502  where j_poste = $1 and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
503  and $this->sql_ledger
504  order by j_date,j_id";
505  $this->content=$this->db->get_array($sql,array($this->account,$this->start,$this->end));
506  }
507  /**
508  * same as get_all but only for lettered operation
509  */
510  public function get_letter_diff()
511  {
512  $sql="
513  with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
514  from
515  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
516  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
517  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
518  select distinct j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
519  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
520  let_diff.jl_id as letter,
521  diff_letter1 as letter_diff
522  from
523  jrnx join jrn on (j_grpt = jr_grpt_id)
524  join letter_jl using (j_id)
525  join let_diff using (jl_id)
526  where j_poste = $1 and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
527  and $this->sql_ledger
528  and diff_letter1 <> 0
529  order by j_date,j_id";
530  $this->content=$this->db->get_array($sql,array($this->account,$this->start,$this->end));
531  }
532  /**
533  * same as get_all but only for unlettered operation
534  */
535 
536  public function get_unletter()
537  {
538  $sql="
539  with letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
540  select j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
541  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
542  -1 as letter,
543  0 as letter_diff
544  from jrnx join jrn on (j_grpt = jr_grpt_id)
545  where j_poste = $1 and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
546  and $this->sql_ledger
547  and j_id not in (select j_id from letter_jl)
548  order by j_date,j_id";
549  $this->content=$this->db->get_array($sql,array($this->account,$this->start,$this->end));
550  }
551 
552 }
553 /**
554  * only for operation retrieved thanks a quick_code
555  * manage the accounting entries for a given card
556  */
558 {
559  /**
560  *constructor
561  *@param $p_init db resource
562  *@param $p_qcode quick_code of the jrnx.j_id
563  */
564  function __construct($p_init,$p_qcode=null)
565  {
566  parent::__construct($p_init);
567  $this->quick_code=$p_qcode;
568  $this->object_type='card';
569  }
570  /**
571  * fills the this->content, datas are filtered thanks
572  * - fil_deb poss values t (debit), f(credit), ' ' (everything)
573  * - fil_amount_max max amount
574  * - fil_amount_min min amount
575  * - $this->start min date
576  * - $this->end max date
577  * - this->quick_code: quick_code
578  */
579  public function get_filter($p_jid=0)
580  {
581  $filter_deb='';
582  if (isset($this->fil_deb))
583  {
584  switch ($this->fil_deb)
585  {
586  case 0:
587  $filter_deb=" and j_debit='t' ";
588  break;
589  case 1:
590  $filter_deb=" and j_debit='f' ";
591  break;
592  case 2:
593  $filter_deb=" ";
594  break;
595  }
596 
597  }
598  $filter_amount="";
599  if ( isset ($this->fil_amount_max ) &&
600  isset ($this->fil_amount_min ) &&
601  isNumber($this->fil_amount_max)==1 &&
602  isNumber($this->fil_amount_min)==1 &&
603  ($this->fil_amount_max != 0 || $this->fil_amount_min != 0) )
604  $filter_amount=" and (j_montant between $this->fil_amount_min and $this->fil_amount_max or (coalesce(comptaproc.get_letter_jnt($p_jid),-1)= coalesce(comptaproc.get_letter_jnt(j_id),-1) and coalesce(comptaproc.get_letter_jnt($p_jid),-1) <> -1 )) ";
605  $sql="
606  with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
607  from
608  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
609  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
610  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
611  select distinct j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
612  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
613  coalesce(let_diff.jl_id,-1) as letter,
614  diff_letter1 as letter_diff
615  from jrnx join jrn on (j_grpt = jr_grpt_id)
616  left join letter_jl using (j_id)
617  left join let_diff using (jl_id)
618  where j_qcode = upper($1) and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
619  and $this->sql_ledger
620  $filter_deb
621  $filter_amount
622  order by j_date,j_id";
623 
624  $this->content=$this->db->get_array($sql,array($this->quick_code,$this->start,$this->end));
625  }
626  /**
627  * fills this->content with all the operation for the this->quick_code(j_qcode)
628  */
629  public function get_all()
630  {
631  $sql="
632  with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
633  from
634  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
635  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
636  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
637  select DISTINCT j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
638  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
639  coalesce(let_diff.jl_id,-1) as letter,
640  diff_letter1 as letter_diff
641  from jrnx join jrn on (j_grpt = jr_grpt_id)
642  left join letter_jl using (j_id)
643  left join let_diff using (jl_id)
644  where j_qcode = upper($1) and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
645  and $this->sql_ledger
646 
647  order by j_date,j_id";
648  $this->content=$this->db->get_array($sql,array($this->quick_code,$this->start,$this->end));
649  }
650  /**
651  * same as get_all but only for lettered operation
652  */
653 
654  public function get_letter()
655  {
656  $sql="
657  with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
658  from
659  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
660  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
661  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
662  select j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
663  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
664  let_diff.jl_id as letter,
665  diff_letter1 as letter_diff
666  from jrnx join jrn on (j_grpt = jr_grpt_id)
667  join letter_jl using (j_id)
668  left join let_diff using (jl_id)
669  where j_qcode = upper($1) and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
670  and $this->sql_ledger
671  order by j_date,j_id";
672  $this->content=$this->db->get_array($sql,array($this->quick_code,$this->start,$this->end));
673  }
674  public function get_letter_diff()
675  {
676  $sql="
677  with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
678  from
679  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
680  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
681  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
682  select distinct j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
683  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
684  let_diff.jl_id as letter,
685  diff_letter1 as letter_diff
686  from jrnx join jrn on (j_grpt = jr_grpt_id)
687  left join letter_jl using (j_id)
688  left join let_diff using (jl_id)
689  where j_qcode = upper($1) and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
690  and $this->sql_ledger
691  and diff_letter1 <>0
692  order by j_date,j_id";
693  $this->content=$this->db->get_array($sql,array($this->quick_code,$this->start,$this->end));
694  }
695  /**
696  * same as get_all but only for unlettered operation
697  */
698  public function get_unletter()
699  {
700  $sql="
701  select j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
702  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
703  -1 as letter,
704  0 as letter_diff
705  from jrnx join jrn on (j_grpt = jr_grpt_id)
706  where j_qcode = upper($1) and j_date >= to_date($2,'DD.MM.YYYY') and j_date <= to_date ($3,'DD.MM.YYYY')
707  and $this->sql_ledger
708  and j_id not in (select j_id from letter_deb join jnt_letter using (jl_id) union select j_id from letter_cred join jnt_letter using (jl_id) )
709  order by j_date,j_id";
710  $this->content=$this->db->get_array($sql,array($this->quick_code,$this->start,$this->end));
711  }
712  /**
713  * fill $this->content with the rows from this query
714  * Columns are
715  * - j_id, id of jrnx
716  * - j_date, date opeation (yyyy.mm.dd)
717  * - to_char(j_date,'DD.MM.YYYY') as j_date_fmt,
718  * - jr_pj_number, receipt number
719  * - j_montant, amount of the rows
720  * - j_debit, Debit or credit
721  * - jr_comment, label of the operation
722  * - jr_internal, internal number
723  * - jr_id, id of jrn
724  * - jr_def_id, id of the ledger (jrn_def.jrn_def_id)
725  * - coalesce(let_diff.jl_id,-1) as letter, id of the lettering , -1 means unlettered
726  * - diff_letter1 as letter_diff, delta between lettered operation
727  * - extract ('days' from coalesce(jr_date_paid,now())-coalesce(jr_ech,jr_date)) as day_paid, days between operation and payment
728  * - jd1.jrn_def_type type of the ledger (FIN, ODS,VEN or ACH)
729  *
730  *
731  * @param type $p_type value is unlet for unlettered operation or let for everything
732  */
733  public function get_balance_ageing($p_type)
734  {
735  $sql_let = ($p_type =='unlet')?' let_diff.jl_id is null and':'';
736  $sql =
737  " with let_diff as (select jl_id,deb_amount-cred_amount as diff_letter1
738  from
739  ( select jl_id,coalesce(sum(j_montant),0) as cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
740  left join (select jl_id,coalesce(sum(j_montant),0) as deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using (jl_id)) ,
741  letter_jl as (select jl_id,j_id from letter_cred union all select jl_id,j_id from letter_deb)
742  select DISTINCT j_id,j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,jr_pj_number,
743  j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
744  coalesce(let_diff.jl_id,-1) as letter,
745  diff_letter1 as letter_diff,
746  extract ('days' from coalesce(jr_date_paid,now())-coalesce(jr_ech,jr_date)) as day_paid,
747  jd1.jrn_def_type
748  from jrnx join jrn on (j_grpt = jr_grpt_id)
749  join jrn_def as jd1 on (jrn.jr_def_id=jd1.jrn_def_id)
750  left join letter_jl using (j_id)
751  left join let_diff using (jl_id)
752  where
753  {$sql_let}
754  j_qcode = upper($1)
755  and j_date >= to_date($2,'DD.MM.YYYY')
756  and {$this->sql_ledger}
757  order by j_date,j_id";
758  $this->content=$this->db->get_array($sql,array($this->quick_code,$this->start));
759 
760  }
761 }
insert_couple($j_id1, $j_id2)
Use to just insert a couple of lettered operation.
mother class for the lettering by account and by card use the tables jnt_letter, letter_deb and lette...
get_all()
fills this->content with all the operation for the this->quick_code(j_qcode)
__construct($p_init)
constructor
show_lettered_diff()
show only the lettered records from jrnx it fills the array $this->content
get_linked($p_jlid)
seek($cond, $p_array=null)
retrieve * row thanks a condition
get_parameter($p_string)
only for operation retrieved thanks a quick_code manage the accounting entries for a given card ...
set_parameter($p_string, $p_value)
only for operation retrieved thanks a account (jrnx.j_poste) manage the accounting entries for a give...
get_filter($p_jid=0)
fills the this->content, datas are filtered thanks
Data & function about connected users.
Definition: class_user.php:36
get_unletter()
same as get_all but only for unlettered operation
isNumber(&$p_int)
Definition: ac_common.php:202
static test_me()
Unit test for the class.
$idx
save($p_array)
save from array
get_balance_ageing($p_type)
fill $this->content with the rows from this query Columns are
show_lettered()
show only the lettered records from jrnx it fills the array $this->content
for($i=0;$i<$nb_jrn;$i++) $deb
__construct($p_init, $p_qcode=null)
constructor
get_letter_diff()
same as get_all but only for lettered operation
get_letter()
same as get_all but only for lettered operation
show_not_lettered()
show only the not lettered records from jrnx it fills the array $this->content
get_unletter()
same as get_all but only for unlettered operation
get_filter($p_jid=0)
fills the this->content, datas are filtered thanks
$count
Definition: modele.inc.php:255
get_letter()
same as get_all but only for lettered operation
get_all()
fills this->content with all the operation for the this->account(jrnx.j_poste)
$SecUser db
show_list($p_type)
wrapper : it call show_all, show_lettered or show_not_lettered depending of the parameter ...
show_all()
show all the record from jrnx and their status (linked or not) it fills the array $this->content ...
else $card content[$j]['j_montant']
__construct($p_init, $p_account=null)
show_letter($p_jid)
static hidden($p_name, $p_value, $p_id="")