noalyss Version-9
acc_operation.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 this file match the tables jrn & jrnx the purpose is to
24 * remove or save accountant writing to these table.
25 */
26
27/*!
28 * @class Acc_Operation
29 * \brief this file match the tables jrn & jrnx the purpose is to
30 * remove or save accountant writing to these table.
31 *
32 */
34{
35 var $db; /*!< database connx */
36 var $jr_id; /*!< pk of jrn */
37 var $jrn_id; /*!< jrn_def_id */
38 var $debit; /*!< debit or credit */
39 var $user; /*!< current user */
40 var $jrn; /*!< the ledger to use */
41 var $poste; /*!< account */
42 var $date; /*!< the date */
43 var $periode; /*!< periode to use */
44 var $amount; /*!< amount of the operatoin */
45 var $grpt; /*!< the group id */
47 var $jr_optype; /*!< type of operation :NOR,CLO,EXT,OPE */
48 var $currency_rate; /*< currency rate used */
49 var $currency_id; /*< currency id */
50 var $currency_rate_ref; /*< currency rate in the table currency*/
51 //!< Qcode of item
52 var $qcode;
53 //!< internal code from jrn
56 /*!
57 * \brief constructor set automatically the attributes user and periode
58 * \param $p_cn the databse connection
59 */
60 function __construct($p_cn)
61 {
62 global $g_user;
63 $this->db=$p_cn;
64 $this->qcode="";
65 $this->user=$_SESSION[SESSION_KEY.'g_user'];
66 $this->periode=$g_user->get_periode();
67 $this->jr_id=0;
68 $this->jr_optype="NOR";
69 $this->amount=0;
70 $this->currency_rate=1;
71 $this->currency_rate_ref=1;
72 $this->currency_id=NULL;
73 }
74
75 function __toString(): string
76 {
77 $r=<<<EOF
78 Acc_Operation Object
79 [
80 db {$this->db}
81 qcode {$this->qcode}
82 user {$this->user}
83 periode {$this->periode}
84 jr_id {$this->jr_id}
85 jr_optype {$this->jr_optype}
86 amount {$this->amount}
87 currency_rate {$this->amount}
88 currency_rate_ref {$this->amount}
89 currency_id {$this->amount}
90 ]
91EOF;
92 return $r;
93
94 }
95
96 /**
97 *@brief retrieve the grpt_id from jrn for a jr_id
98 *@return jrn.jr_grpt_id or an empty string if not found
99 */
100 function seek_group()
101 {
102 $ret=$this->db->get_value('select jr_grpt_id from jrn where jr_id=$1',
103 array($this->jr_id));
104 return $ret;
105 }
106 /**
107 * \brief prepare the query for getting the qcode of the tiers, when
108 * executing this SQL , an array of (jrn.jr_id , jrnx.j_id) must be
109 * provided
110 */
111 private function prepare_sql_tiers()
112 {
113 // prepare for getting the tiers
114 $this->db->prepare('prep_tiers',"select fiche_detail.f_id,ad_value from
115 fiche_detail
116 join (select qf_other as f_id
117 from quant_fin
118 where
119 quant_fin.jr_id = $1
120 union all
121 select qp_supplier as f_id
122 from quant_purchase
123 where quant_purchase.j_id=$2
124 union all
125 select qs_client as f_id
126 from quant_sold
127 where quant_sold.j_id=$2 ) as v_fiche on (fiche_detail.f_id=v_fiche.f_id) where ad_id=23 ");
128 }
129 /**
130 * \brief prepare the query for getting the qcode of the tiers, when
131 * executing this SQL , an array of (jrn.jr_id , jrnx.j_id) must be
132 * provided
133 */
134 private function prepare_sql_counterpart()
135 {
136 // prepare for getting the tiers
137 $this->db->prepare('prep_counterpart',"select fiche_detail.f_id,ad_value from
138 fiche_detail
139 join (select qf_bank as f_id
140 from quant_fin
141 where
142 quant_fin.jr_id = $1
143 union all
144 select qp_fiche as f_id
145 from quant_purchase
146 where quant_purchase.j_id=$2
147 union all
148 select qs_fiche as f_id
149 from quant_sold
150 where quant_sold.j_id=$2 ) as v_fiche on (fiche_detail.f_id=v_fiche.f_id) where ad_id=23 ");
151 }
152 /**
153 * @brief Find the tiers of an operation , thanks the SQL prepared query
154 * prep_tiers and prep_counterpart. Return a string with the quick_code
155 * @param type $pn_jrn_id pk of the table jrn (jrn.jr_id)
156 * @param type $pn_jrnx_id pk of the table jrnx (jrnx.jr_id)
157 * @param type $p_code quickcode
158 * @return string
159 */
160 function find_tiers($pn_jrn_id,$pn_jrnx_id,$p_code)
161 {
162 static $p=0;
163 if ( $p == 0 ){
165 $this->prepare_sql_tiers();
166 $p=1;
167 }
168 $tiers="";
169 $res_tiers=$this->db->execute('prep_tiers',
170 array($pn_jrn_id,$pn_jrnx_id));
171 if ( Database::num_row($res_tiers) > 0) {
172 $atiers=Database::fetch_array($res_tiers);
173 $tiers=$atiers['ad_value'];
174 // If the found tiers has the same quickcode than the current
175 // card, it means it is a card of supplier or customer,
176 // so we must look for the countercard
177 if ($tiers == $p_code) {
178 $res_counterpart=$this->db->execute('prep_counterpart',
179 array($pn_jrn_id,$pn_jrnx_id));
180 $tiers="";
181 if ( Database::num_row($res_counterpart) > 0) {
182 $atiers=Database::fetch_array($res_counterpart);
183 $tiers=$atiers['ad_value'];
184 }
185 }
186 }
187 return $tiers;
188 }
189 /**
190 *@brief Insert into the table Jrn
191 *The needed data are :
192 * - this->date
193 * - this->amount
194 * - this->poste
195 * - this->grpt
196 * - this->jrn
197 * - this->type ( debit or credit)
198 * - this->user
199 * - this->periode
200 * - this->qcode
201 * - this->desc optional
202 *@note if the amount is less than 0 then side changes, for example debit becomes
203 *a credit and vice versa
204 *@return jrnx.j_id
205 */
206
207 function insert_jrnx()
208 {
209 if ( $this->poste == "") { throw new Exception (__FILE__.':'.__LINE__.' Poste comptable vide');return false; }
210 /* for negative amount the operation is reversed */
211 if ( $this->amount < 0 )
212 {
213 $this->type=($this->type=='d')?'c':'d';
214 }
215 if ( DEBUGNOALYSS > 1 ) {
216 echo "insert_jrnx = [{ $this->poste}] {$this->amount} rounded ".round($this->amount,2)." type {$this->type}<br>";
217 }
218 $this->amount=abs($this->amount);
219 $debit=($this->type=='c')?'false':'true';
220 $this->desc=(isset($this->desc))?$this->desc:'';
221 $this->amount=(trim($this->amount)==''||$this->amount==NULL)?0:$this->amount;
222 $Res=$this->db->exec_sql("select insert_jrnx
223 ($1::text,abs($2)::numeric,$3::account_type,$4::integer,$5::integer,$6::bool,$7::text,$8::integer,upper($9),$10::text)",
224 array(
225 $this->date, //$1
226 round($this->amount,2), //$2
227 $this->poste, //$3
228 $this->grpt, //$4
229 $this->jrn, //$5
230 $debit, //$6
231 $this->user, //$7
232 $this->periode, //$8
233 $this->qcode, // $9
234 $this->desc)); //$10
235 if ( $Res===FALSE) return FALSE;
236 $this->jrnx_id=$this->db->get_current_seq('s_jrn_op');
237 return $this->jrnx_id;
238
239 }
240
241 /**
242 * @brief get the sum of other tax linked to this operation
243 */
244 function get_sum_other_tax() {
245 if ( $this->jr_id == 0 ) {return 0;}
246 $sum=$this->db->get_value("select
247 sum(case when j_debit is false and jrn_def.jrn_def_type='ACH'
248 then 0-j_montant when j_debit is true and jrn_def.jrn_def_type='VEN'
249 then 0-j_montant
250 else j_montant end) sum_tax
251 from
252 jrn_tax join jrnx j1 using (j_id)
253 join jrn on (jr_grpt_id=j1.j_grpt)
254 join jrn_def on (jrn.jr_def_id=jrn_def.jrn_def_id)
255 where
256 jrn.jr_id=$1",[$this->jr_id]);
257 ;
258 if ( $this->db->count()==0) {return 0;}
259 return $sum;
260 }
261
262 /*!\brief set the pj of a operation in jrn. the jr_id must be set
263 *\note if the jr_id it fails
264 */
265 function set_pj()
266 {
267 if ( noalyss_strlentrim($this->pj) == 0 )
268 {
269 echo __LINE__."debug {$this->pj} est vide";
270 $sql="update jrn set jr_pj_number=$1 where jr_id=$2";
271 $this->db->exec_sql($sql,array(null,$this->jr_id));
272 return '';
273 }
274 /* is pj uniq ? */
275 if ( $this->db->count_sql("select jr_id from jrn
276 where jr_pj_number=$1 and jr_def_id=$2
277 and jr_id !=$3",
278 array($this->pj,$this->jrn,$this->jr_id)
279 ) == 0 )
280 {
281
282 $sql="update jrn set jr_pj_number=$1 where jr_id=$2";
283 $this->db->exec_sql($sql,array($this->pj,$this->jr_id));
284 }
285 else
286 {
287 /* get pref */
288 $pref=$this->db->get_value("select jrn_def_pj_pref from jrn_def where jrn_def_id=$1",
289 array($this->jrn));
290 /* try another seq */
291 $flag=0;
292 $limit=100;
293 while ( $flag == 0 )
294 {
295 /* limit the search to $limit */
296 if ( $limit < 1 )
297 {
298 $this->pj='';
299 $flag=2;
300 break;
301 }
302
303 $seq=$this->db->get_next_seq('s_jrn_pj'.$this->jrn);
304 $this->pj=$pref.$seq;
305
306 /* check if the new pj numb exist */
307 $c=$this->db->count_sql("select jr_id from jrn where jr_pj_number=$1 and jr_def_id=$2
308 and jr_id !=$3",
309 array($this->pj,$this->jrn,$this->jr_id)
310 );
311 if ( $c == 0 )
312 {
313 $flag=1;
314 break;
315 }
316 $limit--;
317 }
318 /* a pj numb is found */
319 if ( $flag == 1 )
320 {
321 $sql="update jrn set jr_pj_number=$1 where jr_id=$2";
322 $this->db->exec_sql($sql,array($this->pj,$this->jr_id));
323 }
324 }
325 return $this->pj;
326 }
327
328 /*!
329 *\brief Insert into the table Jrn, the amount is computed from jrnx thanks the
330 * group id ($p_grpt)
331 *
332 * \return sequence of jr_id
333 *
334 */
335
336 function insert_jrn()
337 {
338 $p_comment=$this->desc;
339 if ( DEBUGNOALYSS > 1 ) {
340 echo "insert_jrn = {$this->amount} <br>";
341 }
342 $diff=$this->db->get_value("select check_balance ($1)",array($this->grpt));
343 if ( $diff != 0 )
344 {
345
346 printf (_("Erreur : balance incorrecte :diff = %s"),$diff);
347 return false;
348 }
349
350 $echeance=( isset( $this->echeance) && noalyss_strlentrim($this->echeance) != 0)?$this->echeance:null;
351 if ( ! isset($this->mt) )
352 {
353 $this->mt=microtime(true);
354 }
355
356 // if amount == -1then the triggers will throw an error
357 //
358 $this->amount=(trim($this->amount)==''||$this->amount==NULL)?0:$this->amount;
359 $Res=$this->db->exec_sql("insert into jrn (jr_def_id,jr_montant,jr_comment,".
360 "jr_date,jr_ech,jr_grpt_id,jr_tech_per,jr_mt,jr_optype,currency_id,currency_rate,currency_rate_ref) values (".
361 "$1,$2,$3,".
362 "to_date($4,'DD.MM.YYYY'),to_date($5,'DD.MM.YYYY'),$6,$7,$8,$9,$10,$11,$12)",
363 array ($this->jrn, $this->amount,$p_comment,
364 $this->date,$echeance,$this->grpt,$this->periode,$this->mt,$this->jr_optype,
365 $this->currency_id,$this->currency_rate,$this->currency_rate_ref)
366 );
367 if ($Res==FALSE)
368 {
369 return FALSE;
370 }
371 $this->jr_id=$this->db->get_current_seq('s_jrn');
372 return $this->jr_id;
373 }
374 /*!
375 * \brief Return the internal value, the property jr_id must be set before
376 *
377 * \return null si aucune valeur de trouv
378 *
379 */
380 function get_internal()
381 {
382 if ( ! isset($this->jr_id) )
383 throw new Exception('jr_id is not set',1);
384 $Res=$this->db->exec_sql("select jr_internal from jrn where jr_id=".$this->jr_id);
385 if ( Database::num_row($Res) == 0 ) return null;
387 $this->jr_internal= $l_line['jr_internal'];
388 return $this->jr_internal;
389 }
390 /*!\brief search an operation thankx it internal code
391 * \param internal code
392 * \return 0 ok -1 nok
393 */
394 function seek_internal($p_internal)
395 {
396 $res=$this->db->exec_sql('select jr_id from jrn where jr_internal=$1',
397 array($p_internal));
398 if ( Database::num_row($res) == 0 ) return -1;
400 return 0;
401 }
402 /*!\brief retrieve data from jrnx
403 *\note the data are filtered by the access of the current user
404 * \return an array or FALSE if nothing found
405 */
407 {
408 global $g_user;
409 $filter_sql=$g_user->get_ledger_sql('ALL',3);
410 $filter_sql=noalyss_str_replace('jrn_def_id','jr_def_id',$filter_sql);
411 if ( $this->jr_id==0 ) return;
412 $sql=" select jr_id,j_id,jr_date,j_qcode,j_poste,j_montant,jr_internal,case when j_debit = 'f' then 'C' else 'D' end as debit,jr_comment as description,
413 vw_name,pcm_lib,j_debit,coalesce(comptaproc.get_letter_jnt(j_id),-1) as letter,jr_def_id ".
414 " from jrnx join jrn on (jr_grpt_id=j_grpt)
415 join tmp_pcmn on (j_poste=pcm_val)
416 left join vw_fiche_attr on (j_qcode=quick_code)
417 where
418 jr_id=$1 and $filter_sql order by j_debit desc";
419 $res=$this->db->exec_sql($sql,array($this->jr_id));
420 if ( Database::num_row ($res) == 0 ) return array();
422 return $all;
423 }
424 /*!\brief add a comment to the line (jrnx.j_text) */
425 function update_comment($p_text)
426 {
427 $sql="update jrnx set j_text=$1 where j_id=$2";
428 $this->db->exec_sql($sql,array($p_text,$this->jrnx_id));
429 }
430 /*!\brief add a comment to the operation (jrn.jr_text) */
431 function operation_update_comment($p_text)
432 {
433 $sql="update jrn set jr_comment=$1 where jr_id=$2";
434 $this->db->exec_sql($sql,array($p_text,$this->jr_id));
435 }
436 /*!\brief add a limit of payment to the operation (jrn.jr_ech) */
438 {
439 if ( isDate($p_text) == null )
440 {
441 $p_text=null;
442 }
443 $sql="update jrn set jr_ech=to_date($1,'DD.MM.YYYY') where jr_id=$2";
444 $this->db->exec_sql($sql,array($p_text,$this->jr_id));
445 }
446 /*!\brief return the jrn_def_id from jrn */
447 function get_ledger()
448 {
449 $sql="select jr_def_id from jrn where jr_id=$1";
450 $row=$this->db->get_value($sql,array($this->jr_id));
451 return $row;
452 }
453 /*!\brief display_jrnx_detail : get the data from get_jrnx_data and
454 return a string with HTML code
455 * \param table(=0 no code for table,1 code for table,2 code for CSV)
456
457 */
458 function display_jrnx_detail($p_table)
459 {
460 $show=$this->get_jrnx_detail();
461
462 $r='';
463 $r_notable='';
464 $csv="";
465 foreach ($show as $l)
466 {
467 $border="";
468 if ( $l['j_poste'] == $this->poste || ($l['j_qcode']==$this->qcode && trim($this->qcode) != ''))
469 $border=' class="highlight"';
470 $r.='<tr '.$border.'>';
471 $r.='<td>';
472 $a=$l['j_qcode'];
473
474 $r_notable.=$a;
475 $r.=$a;
476 $csv.='"'.$a.'";';
477 $r.='</td>';
478
479 $r.='<td '.$border.'>';
480 $a=$l['j_poste'];
481 $r_notable.=$a;
482 $r.=$a;
483 $csv.='"'.$a.'";';
484 $r.='</td>';
485
486 $r.='<td '.$border.'>';
487 // $a=($l['vw_name']=="")?$l['j_qcode']:$l['pcm_lib'];
488 $a=(noalyss_strlentrim($l['j_qcode'])==0)?$l['pcm_lib']:$l['vw_name'];
489 $r_notable.=$a;
490 $r.=h($a);
491 $csv.='"'.$a.'";';
492 $r.='</td>';
493
494 $r.='<td '.$border.'>';
495 $a=$l['j_montant'];
496 $r_notable.=$a;
497 $r.=$a;
498 $csv.=$a.';';
499 $r.='</td>';
500
501 $r.='<td '.$border.'>';
502 $a=$l['debit'];
503 $r_notable.=$a;
504 $r.=$a;
505 $csv.='"'.$a.'"';
506
507 $csv.="\r\n";
508 $r.='</td>';
509 $r.='<td '.$border.'>';
510 $a=($l['letter']!=-1)?$l['letter']:'';
511 $r_notable.=$a;
512 $r.=$a;
513 $csv.='"'.$a.'"';
514
515 $csv.="\r\n";
516 $r.='</td>';
517
518
519 $r.='</tr>';
520 }
521 switch ($p_table)
522 {
523 case 1:
524 return $r;
525 break;
526 case 0:
527 return $r_notable;
528 break;
529 case 2:
530 return $csv;
531 }
532 return "ERROR PARAMETRE";
533 }
534 /*!
535 * @brief Get data from jrnx where p_grpt=jrnx(j_grpt)
536 *
537 * @param connection
538 * @return array of 3 elements
539 * - First Element is an array
540 @verbatim
541 Array
542 (
543 [op_date] => 01.12.2009
544 [class_cred0] => 7000008
545 [mont_cred0] => 8880.0000
546 [op_cred0] => 754
547 [text_cred0] =>
548 [jr_internal] => 23VEN-01-302
549 [comment] =>
550 [ech] =>
551 [jr_id] => 302
552 [jr_def_id] => 2
553 [class_deb0] => 4000005
554 [mont_deb0] => 10744.8000
555 [text_deb0] =>
556 [op_deb0] => 755
557 [class_cred1] => 4511
558 [mont_cred1] => 1864.8000
559 [op_cred1] => 756
560 [text_cred1] =>
561 )
562 @endverbatim
563 * - Second : number of line with debit
564 * - Third : number of line with credit
565 */
566 function get_data ($p_grpt)
567 {
568 $Res=$this->db->exec_sql("select
569 to_char(j_date,'DD.MM.YYYY') as j_date,
570 j_text,
571 j_debit,
572 j_poste,
573 coalesce(j_qcode,'-') as qcode,
574 j_montant,
575 j_id,
576 jr_comment,
577 to_char(jr_ech,'DD.MM.YYYY') as jr_ech,
578 to_char(jr_date,'DD.MM.YYYY') as jr_date,
579 jr_id,jr_internal,jr_def_id,jr_pj
580 from jrnx inner join jrn on j_grpt=jr_grpt_id where j_grpt=$1",array($p_grpt));
581 $MaxLine=Database::num_row($Res);
582 if ( $MaxLine == 0 ) return null;
583 $deb=0;
584 $cred=0;
585 for ( $i=0; $i < $MaxLine; $i++)
586 {
587
589 $l_array['op_date']=$l_line['j_date'];
590 if ( $l_line['j_debit'] == 't' )
591 {
592 $l_class=sprintf("class_deb%d",$deb);
593 $l_montant=sprintf("mont_deb%d",$deb);
594 $l_text=sprintf("text_deb%d",$deb);
595 $l_qcode=sprintf("qcode_deb%d",$deb);
596 $l_array[$l_class]=$l_line['j_poste'];
597 $l_array[$l_montant]=$l_line['j_montant'];
598 $l_array[$l_text]=$l_line['j_text'];
599 $l_array[$l_qcode]=$l_line['qcode'];
600 $l_id=sprintf("op_deb%d",$deb);
601 $l_array[$l_id]=$l_line['j_id'];
602 $deb++;
603 }
604 if ( $l_line['j_debit'] == 'f' )
605 {
606 $l_class=sprintf("class_cred%d",$cred);
607 $l_montant=sprintf("mont_cred%d",$cred);
608 $l_array[$l_class]=$l_line['j_poste'];
609 $l_array[$l_montant]=$l_line['j_montant'];
610 $l_id=sprintf("op_cred%d",$cred);
611 $l_array[$l_id]=$l_line['j_id'];
612 $l_text=sprintf("text_cred%d",$cred);
613 $l_array[$l_text]=$l_line['j_text'];
614 $l_qcode=sprintf("qcode_cred%d",$cred);
615 $l_array[$l_qcode]=$l_line['qcode'];
616 $cred++;
617 }
618 $l_array['jr_internal']=$l_line['jr_internal'];
619 $l_array['comment']=$l_line['jr_comment'];
620 $l_array['ech']=$l_line['jr_ech'];
621 $l_array['jr_id']=$l_line['jr_id'];
622 $l_array['jr_def_id']=$l_line['jr_def_id'];
623 }
624 return array($l_array,$deb,$cred);
625 }
626 /**
627 *@brief retrieve data from jrnx and jrn
628 *@return return an object
629 *@note
630 *@see
631 @code
632
633 @endcode
634 */
635 function get()
636 {
637 $ret=new Acc_Misc($this->db,$this->jr_id);
638 $ret->get();
639 return $ret;
640 }
641 /**
642 *@brief retrieve data from the table QUANT_*
643 *@return return an object or null if there is no
644 * data from the QUANT table
645 *@see Acc_Sold Acc_Purchase Acc_Fin Acc_Detail Acc_Misc
646 */
647 function get_quant()
648 {
649 $ledger_id=$this->get_ledger();
650 if ( $ledger_id=='') throw new Exception(_('Journal non trouvé'));
651 $oledger=new Acc_Ledger($this->db,$ledger_id);
652
653 // retrieve info from jrn_info
654
655
656 switch($oledger->get_type())
657 {
658 case 'VEN':
659 $ret=new Acc_Sold($this->db,$this->jr_id);
660 break;
661 case 'ACH':
662 $ret=new Acc_Purchase($this->db,$this->jr_id);
663 break;
664 case 'FIN':
665 $ret=new Acc_Fin($this->db,$this->jr_id);
666 break;
667 default:
668 $ret=new Acc_Misc($this->db,$this->jr_id);
669 break;
670 }
671 $ret->get();
672 if ( empty($ret->det->array))
673 {
674 $ret=new Acc_Misc($this->db,$this->jr_id);
675 $ret->get();
676 }
677 $ret->get_info();
678 return $ret;
679 }
680 /**
681 *@brief retrieve amount in currency for the operation
682 *@return amount in currency or 0 if this operation doesn't use currency
683 *@see Acc_Sold Acc_Purchase Acc_Fin Acc_Detail Acc_Misc
684 */
686 {
687 if ( $this->det->currency_id == 0 ) {
688 return 0;
689 }
690 $ledger_id=$this->get_ledger();
691 if ( $ledger_id=='') throw new Exception(_('Journal non trouvé'));
692 $oledger=new Acc_Ledger($this->db,$ledger_id);
693
694 // retrieve info from jrn_info
695
696
697 switch($oledger->get_type())
698 {
699 case 'VEN':
700 $sql_amount="
701 select
702 sum(oc_amount)+sum(oc_vat_amount )
703 from operation_currency oc
704 join quant_sold qs using(j_id)
705 where
706 oc.j_id in (select j_id
707 from jrnx join jrn on (jr_grpt_id=j_grpt)
708 where jr_id=$1);
709 ";
710 break;
711 case 'ACH':
712 $sql_amount="
713 select
714 sum(oc_amount)+sum(oc_vat_amount )
715 from operation_currency oc
716 join quant_purchase qs using(j_id)
717 where
718 oc.j_id in (select j_id
719 from jrnx join jrn on (jr_grpt_id=j_grpt)
720 where jr_id=$1);
721 ";
722 break;
723 case 'FIN':
724 $sql_amount="
725 select
726 sum(oc_amount)+sum(oc_vat_amount )
727 from operation_currency oc
728 join quant_fin qs using(j_id)
729 where
730 oc.j_id in (select j_id
731 from jrnx join jrn on (jr_grpt_id=j_grpt)
732 where jr_id=$1);
733 ";
734 break;
735 default:
736 $sql_amount="
737 select
738 sum(oc_amount)+sum(oc_vat_amount )
739 from operation_currency oc
740 join jrnx using(j_id)
741 join jrn on (jr_grpt_id=j_grpt)
742 where
743 jr_id=$1 and j_debit='t';
744 ";
745 break;
746 }
747 $amount=$this->db->get_value($sql_amount,[$this->jr_id]);
748 return $amount;
749 }
750 /**
751 * @brief retrieve info from the jrn_info, create 2 new arrays
752 * obj->info->command and obj->info->other
753 * the columns are the idx
754 */
755 function get_info()
756 {
757 $this->info=new stdClass();
758 // other info
759 $array=$this->db->get_value("select ji_value from jrn_info where
760 jr_id=$1 and id_type=$2",array($this->jr_id,'OTHER'));
761 $this->info->other= $array;
762
763 // Bon de commande
764 $array=$this->db->get_value("select ji_value from jrn_info where
765 jr_id=$1 and id_type=$2",array($this->jr_id,'BON_COMMANDE'));
766 $this->info->command= $array;
767
768 }
769 /**
770 * Save into jrn_info
771 * @param $p_info msg to save
772 * @param $p_type is OTHER or BON_COMMAND
773 */
774 function save_info($p_info,$p_type)
775 {
776 if ( ! in_array($p_type,array('OTHER','BON_COMMANDE'))) return;
777 if (trim($p_info)=="") {
778 $this->db->exec_sql('delete from jrn_info where jr_id=$1 and id_type=$2',array($this->jr_id,$p_type));
779 return;
780 }
781 $exist=$this->db->get_value('select count(ji_id) from jrn_info where jr_id=$1 and id_type=$2',array($this->jr_id,$p_type));
782 if ( $exist == "0" ) {
783 //insert into jrn_info
784 $this->db->exec_sql('insert into jrn_info(jr_id,id_type,ji_value) values ($1,$2,$3)',
785 array($this->jr_id,$p_type,$p_info));
786 } elseif ( $exist == 1) {
787 //update
788 $this->db->exec_sql('update jrn_info set ji_value=$3 where jr_id=$1 and id_type=$2',
789 array($this->jr_id,$p_type,$p_info));
790 }
791 }
792
793 function insert_related_action($p_string)
794 {
795 if ($p_string == "") return;
796 $a_action=explode(',',$p_string);
797 for ($i=0;$i<count($a_action);$i++)
798 {
799 $action = new Follow_Up($this->db,$a_action[$i]);
800 $action->operation=$this->jr_id;
801 $action->insert_operation();
802 }
803 }
804 /**
805 * @brief set the operation id (jrn.jr_id)
806 * @param type $p_id
807 */
808 function set_id($p_id)
809 {
810 if (isNumber($p_id)==0) {
811 throw new Exception(_('Acc_Operation::set_id , id invalide '));
812 }
813 $this->jr_id=$p_id;
814 }
815 /**
816 * \brief flag the operation as paid
817 */
818 function set_paid()
819 {
820 // Operation
821 if ( $this->jr_id == 0 )
822 throw new Exception(_('Object invalide, id incorrect'));
823 if (
824 $this->db->get_value('select count(*) from jrn where jr_id=$1',
825 array($this->jr_id)) == 0 )
826 throw new Exception(_('Object invalide, id incorrect'));
827
828 $this->db->exec_sql("update jrn set jr_rapt = 'paid' where jr_id = $1",
829 array($this->jr_id));
830 }
831 /**
832 * return amount of the jr_id
833 */
834 function get_amount()
835 {
836 if ( $this->jr_id == 0 )
837 throw new Exception(_('Object invalide, id incorrect'));
838 $amount=$this->db->get_value('select jr_montant from jrn where jr_id=$1',
839 array($this->jr_id));
840 return $amount;
841 }
842 static function test_me()
843 {
844 $_SESSION[SESSION_KEY.'g_user']=NOALYSS_ADMINISTRATOR;
845 $_SESSION[SESSION_KEY.'g_pass']='dany';
846 global $g_user;
848 $g_user=new Noalyss_user($cn);
849 $a=new Acc_Operation($cn);
850 $a->jr_id=1444;
851 $b=$a->get_quant();
852 var_dump($b);
853 }
854 /**
855 * Return a select object to choose the type of operation
856 * - NOR normal
857 * - EXT reverse operation
858 * - CLO closing periode
859 * - OPE opening periode
860 * @param string $p_status
861 * @return \ISelect
862 */
863 static function select_operation_type($p_status)
864 {
865 $type_operation=new ISelect("jr_optype");
866 $type_operation->value=array(
867 array(("label")=>_("Normal"), "value"=>"NOR"),
868 array(("label")=>_("Ouverture"), "value"=>"OPE"),
869 array(("label")=>_("Fermeture"), "value"=>"CLO"),
870 array(("label")=>_("Extourne"), "value"=>"EXT")
871 );
872
873 $type_operation->selected=$p_status;
874 return $type_operation;
875 }
876 /**
877 * @brief create a form to recreate the operation and returns it, just like a correct
878 * @param $p_id string DOMID of the form
879 */
881 // retrieve all info about operation
882 $operation = $this->get_quant();
883 $array=$operation->compute_array();
884
885 global $g_user;
886 $a_code=$this->db->get_array("select code from v_menu_dependency vmd where me_code=$1 and p_id=$2",
887 array( $operation->signature,$g_user->get_profile()));
888 if ( empty ($a_code)) {
889 $r=_("Menu invalide");
890 return $r;
891 }
892
893 // Prepare the form
894 // select the menu where the operation will be duplicated
895 $r=sprintf('<form id="%s" method="POST" ACTION="%s">',$p_id,NOALYSS_URL."/do.php?".http_build_query([
896 "ac"=>$a_code[0]['code'],"gDossier"=>Dossier::id()
897 ]));
899 // select the menu where the operation will be duplicated
900 $r.="<p>";
901 $r.="<ul style=\"margin-left:2rem;padding-left:0;list-style:none;\">";
902 $r.=sprintf("<li>%s</li>",$operation->det->jr_pj_number);
903 $r.=sprintf("<li>%s</li>",$operation->det->jr_comment);
904 $r.=sprintf("<li>%s</li>",$operation->det->jr_montant);
905 $r.="</ul>";
906 $r.="</p>";
907 if (count($a_code) == 1 ) {
908 $r.=HtmlInput::hidden("ac",$a_code[0]['code']);
909 $r.=sprintf(_("Voulez-vous aller à %s pour dupliquer cette opération ?"),$a_code[0]['code']);
910
911 } else {
912 $select=new ISelect("ac");
913 $select->value=array();
914 $nb_code=count($a_code);
915
916 for ($i=0;$i<$nb_code;$i++) {
917 $select->value[]=array("label"=>$a_code[$i]['code'],"value"=>$a_code[$i]['code']);
918 }
919 $r.=sprintf(_("Voulez-vous aller à %s pour dupliquer cette opération ?"),$select->input());
920
921 }
922
923 $r.="</p>";
924
925 // For Misc Operation , if a card is given then there is no accounting
926 if ( $operation->signature==="ODS") {
927 $nb_array = count($array);
928 for ($i = 0; $i < $nb_array; $i++) {
929 if (isset ($array["qc_" . $i]) && $array["qc_" . $i] != "") {
930 $array["poste" . $i] = "";
931 }
932 }
933 }
934
935 if ( $operation->signature==="ACH" || $operation->signature=="VEN") {
936 $idx=0;
937 foreach ($operation->det->array as $item) {
938 if ( isset ($item['qs_vat_sided']) && $item['qs_vat_sided'] != 0 ) {
939 $array['e_march'.$idx.'_tva_amount']=0;
940 }elseif (isset ($item['qp_vat_sided']) && $item['qp_vat_sided'] != 0 ){
941 $array['e_march'.$idx.'_tva_amount']=0;
942 }
943 $idx++;
944 }
945 if ( DEBUGNOALYSS>1) {
946 echo \Noalyss\Dbg::hidden_info("operation->det_array", $operation->det->array);
947 echo \Noalyss\Dbg::hidden_info("array", $array);
948 }
949 }
950
951 // transform the operation into hidden element
953 $r.=HtmlInput::hidden("e_comm",$operation->det->jr_comment);
954 $r.=HtmlInput::submit(uniqid(), _("Dupliquer"));
955 $r.=HtmlInput::button_close("duplicate_operation_div");
956 $r.='</form>';
957
958
959 // return the form as a string
960 return $r;
961 }
962
963}
964/////////////////////////////////////////////////////////////////////////////
965
966/**
967 * @class Acc_Detail
968 * @brief Contains the detail of an operation Acc_Operation
969 */
971{
972 public $det;
973 public $jr_id;
974 public $info;
975
976 function __construct($p_cn,$p_jrid=0)
977 {
978 parent::__construct($p_cn);
979 $this->jr_id=$p_jrid;
980 $this->det=new stdClass();
981 }
982 /**
983 *@brief retrieve some common data from jrn as
984 * the datum, the comment,payment limit...
985 */
986 function get()
987 {
988 $sql="SELECT jr_id, jr_def_id, jr_montant, jr_comment, jr_date, jr_grpt_id,
989 jr_internal, jr_tech_date, jr_tech_per, jrn_ech, jr_ech, jr_rapt,jr_ech,
990 jr_valid, jr_opid, jr_c_opid, jr_pj, jr_pj_name, jr_pj_type,
991 jr_pj_number, jr_mt,jr_rapt,jr_date_paid,jr_optype,currency_id,currency_rate,currency_rate_ref
992 FROM jrn where jr_id=$1";
993 $array=$this->db->get_array($sql,array($this->jr_id));
994 if ( count($array) == 0 ) throw new Exception('Aucune ligne trouvée');
995 foreach ($array[0] as $key=>$val)
996 {
997 $this->det->$key=$val;
998 }
999 $sql="select n_text from jrn_note where jr_id=$1";
1000 $this->det->note=$this->db->get_value($sql,array($this->jr_id));
1001 $this->det->note=strip_tags($this->det->note);
1002 }
1003 /**
1004 *
1005 */
1006 function compute_array()
1007 {
1008 $array=array();
1009 $array['desc']=$this->det->jr_comment;
1010 $array['e_date']="";
1011 $array['e_ech']="";
1012 $array['p_jrn']=$this->det->jr_def_id;
1013 return $array;
1014
1015 }
1016}
1017/////////////////////////////////////////////////////////////////////////////
1018/**
1019 * @class Acc_Misc
1020 *@brief this class manage data from the JRNX and JRN
1021 * table
1022 *@note Data member are the column of the table
1023 */
1025{
1026 var $signature; /*!< signature of the obj ODS */
1027 var $array; /*!< an array containing the data from JRNX */
1028 function __construct($p_cn,$p_jrid=0)
1029 {
1030 parent::__construct($p_cn,$p_jrid);
1031 $this->signature='ODS';
1032 $this->det=new stdClass();
1033 }
1034 function get()
1035 {
1036 parent::get();
1037 $sql="SELECT j_id, j_date, j_montant, j_poste, j_grpt, j_rapt, j_jrn_def,
1038 j_debit, j_text, j_centralized, j_internal, j_tech_user, j_tech_date,
1039 j_tech_per, j_qcode,f_id
1040 FROM jrnx where j_grpt = $1 order by j_debit desc,j_poste";
1041 $this->det->array=$this->db->get_array($sql,array($this->det->jr_grpt_id));
1042 }
1043 /***
1044 * Compute an array for using with Acc_Ledger::insert
1045 *
1046 */
1047 function compute_array()
1048 {
1049 $this->get();
1050 $array=parent::compute_array();
1051 $nb_array=count($this->det->array);
1052 $array['nb_item']=$nb_array;
1053
1054 for ($i=0;$i<$nb_array;$i++) {
1055 $array["qc_".$i]=$this->det->array[$i]['j_qcode'];
1056 $array["poste".$i]=$this->det->array[$i]['j_poste'];
1057 $array["amount".$i]=$this->det->array[$i]['j_montant'];
1058 if ( $this->det->array[$i]['j_debit'] == 't') {
1059 $array["ck".$i]=1;
1060 }
1061 $array["ld".$i]=$this->det->array[$i]['j_text'];
1062 }
1063
1064 return $array;
1065
1066 }
1067}
1068/////////////////////////////////////////////////////////////////////////////
1069/**
1070 * @class Acc_Sold
1071 *@brief this class manage data from the QUANT_SOLD
1072 * table
1073 *@note Data member are the column of the table
1074 */
1076{
1077 function __construct($p_cn,$p_jrid=0)
1078 {
1079 parent::__construct($p_cn,$p_jrid);
1080 $this->signature='VEN';
1081 $this->det=new stdClass();
1082 }
1083 function get()
1084 {
1085 parent::get();
1086 $sql="SELECT qs_id, qs_internal, qs_fiche, qs_quantite, qs_price, qs_vat,
1087 qs_vat_code, qs_client, qs_valid, j_id,j_text,qs_vat_sided , qs_unit , j_debit
1088 FROM quant_sold join jrnx using(j_id) where j_grpt=$1 order by j_id";
1089 $this->det->array=$this->db->get_array($sql,array($this->det->jr_grpt_id));
1090 }
1091 /***
1092 * Compute an array for using with Acc_Ledger::insert
1093 *
1094 */
1095 function compute_array()
1096 {
1097 $this->get();
1098 $array=parent::compute_array();
1099 $nb_array=count($this->det->array);
1100 $array['nb_item']=$nb_array;
1101
1102
1103 $array["e_client"]=$this->db->get_value("select ad_value from fiche_detail where f_id=$1 and ad_id=23",
1104 array($this->det->array[0]['qs_client']));
1105
1106 for ($i=0;$i<$nb_array;$i++) {
1107 $array["e_march".$i]=$this->db->get_value("select ad_value from fiche_detail where f_id=$1 and ad_id=23",
1108 array($this->det->array[$i]['qs_fiche']));
1109
1110 $array["e_march".$i."_price"]=$this->det->array[$i]['qs_unit'];
1111 $array["e_march".$i."_label"]=$this->det->array[$i]['j_text'];
1112 $array["e_march".$i."_tva_id"]=$this->det->array[$i]['qs_vat_code'];
1113 $array["e_march".$i."_tva_amount"]=$this->det->array[$i]['qs_vat'];
1114 $array["e_quant".$i]=$this->det->array[$i]['qs_quantite'];
1115 }
1116 $array['correct']=1;
1117 return $array;
1118
1119 }
1120
1121}
1122/////////////////////////////////////////////////////////////////////////////
1123/**
1124 * @class Acc_Purchase
1125 *@brief this class manage data from the QUANT_PURCHASE
1126 * table
1127 *@note Data member are the column of the table
1128
1129 */
1131{
1132 function __construct($p_cn,$p_jrid=0)
1133 {
1134 parent::__construct($p_cn,$p_jrid);
1135 $this->signature='ACH';
1136 }
1137
1138 function get()
1139 {
1140 parent::get();
1141 $sql="SELECT qp_id, qp_internal, j_id, qp_fiche, qp_quantite, qp_price, qp_vat,
1142 qp_vat_code, qp_nd_amount, qp_nd_tva, qp_nd_tva_recup, qp_supplier,
1143 qp_valid, qp_dep_priv,j_text,qp_vat_sided,qp_unit , j_debit
1144 FROM quant_purchase join jrnx using(j_id) where j_grpt=$1 order by j_id";
1145 $this->det->array=$this->db->get_array($sql,array($this->det->jr_grpt_id));
1146 }
1147 /***
1148 * Compute an array for using with Acc_Ledger::insert
1149 *
1150 */
1151 function compute_array()
1152 {
1153 $this->get();
1154 $array=parent::compute_array();
1155 $nb_array=count($this->det->array);
1156 $array['nb_item']=$nb_array;
1157
1158
1159 $array["e_client"]=$this->db->get_value("select ad_value from fiche_detail where f_id=$1 and ad_id=23",
1160 array($this->det->array[0]['qp_supplier']));
1161
1162 for ($i=0;$i<$nb_array;$i++) {
1163 $array["e_march".$i]=$this->db->get_value("select ad_value from fiche_detail where f_id=$1 and ad_id=23",
1164 array($this->det->array[$i]['qp_fiche']));
1165
1166 $array["e_march".$i."_price"]=$this->det->array[$i]['qp_unit'];
1167 $array["e_march".$i."_label"]=$this->det->array[$i]['j_text'];
1168 $array["e_march".$i."_tva_id"]=$this->det->array[$i]['qp_vat_code'];
1169 $array["e_march".$i."_tva_amount"]=$this->det->array[$i]['qp_vat'];
1170 $array["e_quant".$i]=$this->det->array[$i]['qp_quantite'];
1171 }
1172 $array['correct']=1;
1173 return $array;
1174
1175 }
1176
1177
1178}
1179/////////////////////////////////////////////////////////////////////////////
1180/**
1181 * @class Acc_Fin
1182 *@brief this class manage data from the QUANT_FIN
1183 * table
1184 *@note Data member are the column of the table
1185 */
1186class Acc_Fin extends Acc_Detail
1187{
1188 function __construct($p_cn,$p_jrid=0)
1189 {
1190 parent::__construct($p_cn,$p_jrid);
1191 $this->signature='FIN';
1192 }
1193
1194 function get()
1195 {
1196 parent::get();
1197 $sql="SELECT qf_id, qf_bank, jr_id, qf_other, qf_amount,j_id
1198 FROM quant_fin where jr_id = $1";
1199 $this->det->array=$this->db->get_array($sql,array($this->jr_id));
1200 }
1201 /***
1202 * Compute an array for using with Acc_Ledger::insert
1203 *
1204 */
1205 function compute_array()
1206 {
1207 $this->get();
1208 $array=parent::compute_array();
1209 $nb_array=count($this->det->array);
1210 $array['nb_item']=$nb_array;
1211
1212
1213 for ($i=0;$i<$nb_array;$i++) {
1214 $array["e_other".$i]=$this->db->get_value("select ad_value from fiche_detail where f_id=$1 and ad_id=23",
1215 array($this->det->array[$i]['qf_other']));
1216
1217 $array["e_other".$i."_amount"]=$this->det->array[$i]['qf_amount'];
1218 $array["e_other".$i."_comment"]=$this->det->jr_comment;
1219 }
1220 $array['correct']=1;
1221 return $array;
1222
1223 }
1224}
isNumber($p_int)
Definition: ac_common.php:215
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
global $g_user
if no group available , then stop
$action
catch(Exception $exc) if(! $g_user->can_write_action($ag_id)) $r
h( $row[ 'oa_description'])
$idx
$op jr_id
Definition: ajax_ledger.php:83
$ledger_id
$p
Definition: array.php:34
$input_from user
Definition: balance.inc.php:68
$input_from type
Definition: balance.inc.php:65
for($j=0;$j< $nb_row;$j++)($j%2==0)? 'even' $show
Contains the detail of an operation Acc_Operation.
__construct($p_cn, $p_jrid=0)
this class manage data from the QUANT_FIN table
__construct($p_cn, $p_jrid=0)
this class manage data from the JRNX and JRN table
__construct($p_cn, $p_jrid=0)
this file match the tables jrn & jrnx the purpose is to remove or save accountant writing to these ta...
prepare_sql_tiers()
prepare the query for getting the qcode of the tiers, when executing this SQL , an array of (jrn....
get_sum_other_tax()
get the sum of other tax linked to this operation
operation_update_comment($p_text)
add a comment to the operation (jrn.jr_text)
get_quant()
retrieve data from the table QUANT_*
form_clone_operation($p_id)
create a form to recreate the operation and returns it, just like a correct
insert_jrnx()
Insert into the table Jrn The needed data are :
static select_operation_type($p_status)
Return a select object to choose the type of operation.
get_internal()
Return the internal value, the property jr_id must be set before.
get_data($p_grpt)
Get data from jrnx where p_grpt=jrnx(j_grpt)
get_amount()
return amount of the jr_id
__construct($p_cn)
constructor set automatically the attributes user and periode
set_paid()
flag the operation as paid
get_ledger()
return the jrn_def_id from jrn
insert_jrn()
Insert into the table Jrn, the amount is computed from jrnx thanks the group id ($p_grpt)
operation_update_date_limit($p_text)
add a limit of payment to the operation (jrn.jr_ech)
update_comment($p_text)
add a comment to the line (jrnx.j_text)
set_pj()
set the pj of a operation in jrn. the jr_id must be set
save_info($p_info, $p_type)
Save into jrn_info.
$qcode
< Qcode of item
get_jrnx_detail()
retrieve data from jrnx
prepare_sql_counterpart()
prepare the query for getting the qcode of the tiers, when executing this SQL , an array of (jrn....
get_currency_amount()
retrieve amount in currency for the operation
get_info()
retrieve info from the jrn_info, create 2 new arrays obj->info->command and obj->info->other the colu...
display_jrnx_detail($p_table)
display_jrnx_detail : get the data from get_jrnx_data and return a string with HTML code
find_tiers($pn_jrn_id, $pn_jrnx_id, $p_code)
Find the tiers of an operation , thanks the SQL prepared query prep_tiers and prep_counterpart.
set_id($p_id)
set the operation id (jrn.jr_id)
seek_internal($p_internal)
search an operation thankx it internal code
insert_related_action($p_string)
seek_group()
retrieve the grpt_id from jrn for a jr_id
this class manage data from the QUANT_PURCHASE table
__construct($p_cn, $p_jrid=0)
this class manage data from the QUANT_SOLD table
__construct($p_cn, $p_jrid=0)
static fetch_all($ret)
wrapper for the function pg_fetch_all
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 hidden()
return a string to set gDossier into a FORM
static connect()
static button_close($div_name, $class='smallbutton')
close button for the HTML popup
static hidden($p_name, $p_value, $p_id="")
static simple_array_to_hidden($array)
Transform a double array as a HTML string with hidden html value array has the formarray ["name"]="x"...
static submit($p_name, $p_value, $p_javascript="", $p_class="smallbutton")
Html Input , create a tag <SELECT> ... </SELECT> if readonly == true then display the label correspon...
$all
$Res
$bal jrn
$SecUser db
NOALYSS_ADMINISTRATOR
Definition: install.php:735
$flag
Definition: install.php:531
if( $delta< 0) elseif( $delta==0)
for($i=0;$i< $nb_jrn;$i++) $deb