noalyss Version-9
impress.class.php
Go to the documentation of this file.
1<?php
2
3/*
4 * This file is part of NOALYSS.
5 *
6 * NOALYSS is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
10 *
11 * NOALYSS is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with NOALYSS; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19 */
20
21// Copyright Author Dany De Bontridder danydb@aevalys.eu
22/* !
23 * \file
24 * \brief contains function for the parsing and computing formulae. Test are in scenario/test_parse_formula.php
25 */
26
27/**
28 * @class Impress
29 * @brief contains function for the parsing and computing formulae . Test are in scenario/test_parse_formula.php
30 */
32{
33 //!< string pattern to use
34 const STR_PATTERN="([\[\{]{1,2}[[:alnum:]]*%*(-[c,d,s,S]){0,1}[\]\}]{1,2})";
35
36 /* !
37 *
38 * \brief Purpose Parse a formula
39 *
40 * \param $p_cn connexion
41 * \param $p_label
42 * \param $p_formula
43 * \param $p_eval true if we eval here otherwise the function returns
44 * a string which must be evaluated
45 \param $p_type_date : type of the date 0 for accountant period or 1
46 * for calendar
47 * \return array key [ desc , montant ]
48 *
49 *
50 */
51
52 static function parse_formula($p_cn, $p_label, $p_formula, $p_start, $p_end, $p_eval=true, $p_type_date=0, $p_sql="")
53 {
54 global $g_user;
55 if (Impress::check_formula($p_formula)==false)
56 {
57 if ($p_eval==true)
58 return array('desc'=>$p_label.' Erreur Formule!',
59 'montant'=>0);
60 else
61 return $p_formula;
62 }
63 // Bug
64 // with PHP8 if ('periode'==0 ) echo "hello"; else echo 'different' ; => different
65 // with PHP7 if ('periode'==0 ) echo "hello"; else echo 'different' ; => hello
66
67 if ($p_type_date==0)
68 {
69 $cond=sql_filter_per($p_cn, $p_start, $p_end, 'p_id', 'j_tech_per');
70 $cond_anc= "and ".transform_sql_filter_per($cond);
71 }
72 elseif ($p_type_date == 1) {
73 $cond="( j_date >= to_date('$p_start','DD.MM.YYYY') and j_date <= to_date('$p_end','DD.MM.YYYY'))";
74 $cond_anc="and ( oa_date >= to_date('$p_start','DD.MM.YYYY') and oa_date <= to_date('$p_end','DD.MM.YYYY'))";
75 } else {
76 throw new Exception("impress72 invalid type_date [ {$p_type_date} ] ");
77 }
78
79 // ------------------- for accounting , analytic or card-------------------------------------
80 if ( DEBUGNOALYSS > 1)
81 {
82 tracedebug("impress.debug.log", "$p_formula ", 'parse_formula-71 $formula to parse' );
83 tracedebug("impress.debug.log", "$p_label ", 'parse_formula-72 $p_label' );
84 tracedebug("impress.debug.log", $p_start,'parse_formula-73 $p_start' );
85 tracedebug("impress.debug.log", $p_end,'parse_formula-74 $p_end ' );
86 tracedebug("impress.debug.log", $p_type_date,'parse_formula-75 $p_type_date' );
87 tracedebug("impress.debug.log", $p_sql,'parse_formula-76 $p_sql' );
88 tracedebug("impress.debug.log", $cond ,'parse_formula-77 $cond SQL accountancy' );
89 tracedebug("impress.debug.log", $cond_anc,'parse_formula-78 $cond_anc SQL Analytic Acc' );
90 }
91 while (preg_match_all(Impress::STR_PATTERN, $p_formula, $e)==true)
92 {
93 $x=$e[0];
94 foreach ($x as $line)
95 {
96
97 // If there is a FROM clause we must recompute
98 // the time cond
99
100 if ($p_type_date==0&&preg_match("/FROM=[0-9]+\.[0-9]+/", $p_formula, $afrom)==1)
101 {
102 $from=noalyss_str_replace('FROM=','',$afrom[0]);
104 $cond_anc=" and ".transform_sql_filter_per($cond);
105 // We remove FROM out of the p_formula
106 $p_formula=substr_replace($p_formula, "", strpos($p_formula, "FROM"));
107 }
108 if ($p_type_date==1&&preg_match("/FROM=[0-9]+\.[0-9]+/", $p_formula, $afrom)==1)
109 {
110 // We remove FROM out of the p_formula
111 $p_formula=substr_replace($p_formula, "", strpos($p_formula, "FROM"));
112 }
113 $amount=\Impress::compute_amount($p_cn,$line,$cond." ".$p_sql,$cond_anc." ".$p_sql);
114
115
116 $p_formula=noalyss_str_replace($x[0],"(". $amount.")", $p_formula);
117 }
118 }
119
120 // $p_eval is true then we eval and returns result
121 if ($p_eval==true)
122 {
123 /* -------------------------------------
124 * Protect againt division by zero
125 */
126 $p_formula=remove_divide_zero($p_formula);
127 $p_formula="\$result=".$p_formula.";";
128 try {
129 eval("$p_formula");
130 } catch(Exception $e) {
131 return array("desc"=>"erreur","montant"=>'0');
132 }
133 while (preg_match("/\[([0-9]+)(-[Tt]*)\]/", trim($p_label), $e)==1)
134 {
135 $nom="!!".$e[1]."!!";
137 {
138 $nom=$p_cn->get_value("SELECT pcm_lib AS acct_name FROM tmp_pcmn WHERE pcm_val::text LIKE $1||'%' ORDER BY pcm_val ASC LIMIT 1",
139 array($e[1]));
140 if ($nom)
141 {
142 if ($e[2]=='-T')
143 $nom=strtoupper($nom);
144 if ($e[2]=='-t')
145 $nom=strtolower($nom);
146 }
147 }
148 $p_label=noalyss_str_replace($e[0], $nom, $p_label);
149 }
150
151 $aret=array('desc'=>$p_label,
152 'montant'=>$result);
153 return $aret;
154 }
155 else
156 {
157 // $p_eval is false we returns only the string
158 return $p_formula;
159 }
160 }
161
162 /* !
163 * \brief Check if formula doesn't contain
164 * php injection
165 * \param string
166 *
167 * \return true if the formula is good otherwise false
168 */
169
170 static function check_formula($p_string)
171 {
172 // the preg_match gets too complex if we want to add a test
173 // for parenthesis, math function...
174 // So I prefer remove them before testing
175
176
177
178 $p_string=noalyss_str_replace("round", "", $p_string);
179 $p_string=noalyss_str_replace("abs", "", $p_string);
180 $p_string=noalyss_str_replace("(", "", $p_string);
181 $p_string=noalyss_str_replace(")", "", $p_string);
182 // for the inline test like $a=(cond)?value:other;
183 $p_string=noalyss_str_replace("?", "+", $p_string);
184 $p_string=noalyss_str_replace(":", "+", $p_string);
185 $p_string=noalyss_str_replace(">=", "+", $p_string);
186 $p_string=noalyss_str_replace("<=", "+", $p_string);
187 $p_string=noalyss_str_replace(">", "+", $p_string);
188 $p_string=noalyss_str_replace("<", "+", $p_string);
189 // eat Space + comma
190 $p_string=noalyss_str_replace(" ", "", $p_string);
191 $p_string=noalyss_str_replace(",", "", $p_string);
192 // Remove D/C/S
193 $p_string=noalyss_str_replace("-c", "", $p_string);
194 $p_string=noalyss_str_replace("-d", "", $p_string);
195 $p_string=noalyss_str_replace("-s", "", $p_string);
196 $p_string=noalyss_str_replace("-S", "", $p_string);
197 // Remove T,t
198 $p_string=noalyss_str_replace("-t", "", $p_string);
199
200 // analytic accountancy (between {} )
201 $p_string=preg_replace("/\{\{[[:alnum:]]*\}\}/", "", $p_string);
202
203 // card (between {} )
204 $p_string=preg_replace("/\{[[:alnum:]]*\}/", "", $p_string);
205
206 // remove date
207 $p_string=preg_replace("/FROM*=*[0-9]+/", "", $p_string);
208 // remove comment
209 $p_string=preg_replace("/#.*/", "", $p_string);
210 // remove php variable $C=
211 $p_string=preg_replace('/\$[a-z]*[A-Z]*[0-9]*[A-Z]*[a-z]*/', "", $p_string);
212 $p_string=preg_replace('/=/', "", $p_string);
213
214 // remove account
215 $p_string=preg_replace("/\[[0-9]*[A-Z]*%*\]/", "", $p_string);
216
217 $p_string=preg_replace("/\+|-|\/|\*/", "", $p_string);
218 $p_string=preg_replace("/[0-9]*\.*[0-9]/", "", $p_string);
219
220 //************************************************************************************************************
221 // If the string is empty then formula should be good
222 //
223 //************************************************************************************************************
224 if ($p_string=='')
225 {
226 return true;
227 }
228 else
229 {
230 return false;
231 }
232 }
233
234 /**
235 * with the handle of a successull query, echo each row into CSV and
236 * send it directly
237 * @param type $array of data
238 * @param type $aheader double array, each item of the array contains
239 * a key type (num) and a key title
240 */
241 static function array_to_csv($array, $aheader, $p_filename)
242 {
243 $file_csv=new Noalyss_Csv($p_filename);
244 for ($i=0; $i<count($aheader); $i++)
245 {
246 $file_csv->add($aheader[$i]['title']);
247 }
248 $file_csv->write();
249
250 // fetch all the rows
251 for ($i=0; $i<count($array); $i++)
252 {
253 $row=$array[$i];
254 $e=0;
255 // for each rows, for each value
256 foreach ($array[$i] as $key=> $value)
257 {
258 if ($e>count($aheader))
259 continue;
260
261 if (isset($aheader[$e]['type']))
262 {
263 switch ($aheader[$e]['type'])
264 {
265 case 'num':
266 $file_csv->add($value, "number");
267 break;
268 default:
269 $file_csv->add($value);
270 }
271 }
272 else
273 {
274 $file_csv->add($value);
275 }
276 $e++;
277 }
278 $file_csv->write();
279 }
280 }
281
282 /**
283 * return what to consider
284 * - "deb" for the total of the debit ,
285 * - "cred" for total of credit,
286 * - "signed" for tot. debit - tot. credit
287 * - "cdsigned" for tot.credit - tot.debit
288 * - "all" is the balance of accounting in absolute value
289 *
290 * @param string $p_formula
291 * @return "all", "deb","cred","signed" or "cdsigned"
292 */
293 static function find_computing_mode($p_formula)
294 {
295 if (strpos($p_formula, '-d')!=0)
296 {
297 return 'deb';
298 }
299 elseif (strpos($p_formula, '-c')!=0)
300 {
301 return 'cred';
302 }
303 elseif (strpos($p_formula, '-s')!=0)
304 {
305 return 'signed';
306 }
307 elseif (strpos($p_formula, '-S')!=0)
308 {
309 return 'cdsigned';
310 }
311 return 'all';
312 }
313 /**
314 * @brief make the condition SQL for filtering on the period
315 * @param \DatabaseCore $p_cn
316 * @param int $p_from periode id
317 * @param int $p_end until periode id
318 * @throws Exception
319 */
320 static public function compute_periode($p_cn, $p_from,$p_end)
321 {
322 // There is a FROM clause
323 // then we must modify the cond for the periode
324
325
326 // Get the periode
327 /* ! \note special value for the clause FROM=00.0000, we take the first day of the exercice of $p_end
328 */
329 if ($p_from=='00.0000')
330 {
331 $current_exercice=$p_cn->get_value('select p_exercice from parm_periode where p_id=$1',
332 [$p_end]);
333 if ( $current_exercice=="") {
334 throw new Execution(_('CP329'));
335 }
336 $first_day=$p_cn->get_value("select to_char(min(p_start),'DD.MM.YYYY') as p_start from parm_periode where p_exercice=$1",
338 $last_day=$p_cn->get_value("select to_char(p_end,'DD.MM.YYYY') from parm_periode where p_id=$1",[$p_end]);
339 // retrieve the first month of this periode
340 if (empty($first_day))
341 throw new Exception('Pas de limite à cette période', 1);
342 $cond=sql_filter_per($p_cn, $first_day, $last_day, 'date', 'j_tech_per');
343 }
344 else
345 {
346 $oPeriode=new Periode($p_cn);
347 try
348 {
349 $pfrom=$oPeriode->find_periode('01.'.$p_from);
350 $cond=sql_filter_per($p_cn, $pfrom, $p_end, 'p_id', 'j_tech_per');
351 }
352 catch (Exception $exp)
353 {
354 /* if none periode is found
355 then we take the first periode of the year
356 */
357
358 $first_day=$p_cn->get_value("select to_char(min(p_start),'DD.MM.YYYY') as p_start from parm_periode");
359 $last_day=$p_cn->get_value("select to_char(p_end,'DD.MM.YYYY') from parm_periode where p_id=$1",[$p_end]);
360 // retrieve the first month of this periode
361 if (empty($first_day))
362 throw new Exception('Pas de limite à cette période', 1);
363 $cond=sql_filter_per($p_cn, $first_day, $last_day, 'date', 'j_tech_per');
364 }
365 }
366 return $cond;
367 }
368 /**
369 * @brief compute the amount of the accounting ,analytic accounting or a card, the SQL condition
370 * from sql_filter_per must be transformed for ANALYTIC ACCOUNT
371 * @see sql_filter_per
372 * @param DatabaseCore $p_cn
373 * @param string $p_expression part of a formula
374 * @param string $p_cond_sql SQL cond for accountancy
375 * @param string $p_cond_sql SQL cond for analytic accountancy
376 */
377 static function compute_amount($p_cn, $p_expression, $p_cond_sql,$p_cond_anc_sql)
378 {
379 if ( DEBUGNOALYSS > 1)
380 {
381 tracedebug("impress.debug.log", "$p_expression", '$p_expression' );
382 tracedebug("impress.debug.log", "$p_cond_sql", '$p_cond_sql' );
383 }
384 $compute=\Impress::find_computing_mode($p_expression);
385 // remove char for the mode
386 $p_expression=noalyss_str_replace("-d", "", $p_expression);
387 $p_expression=noalyss_str_replace("-c", "", $p_expression);
388 $p_expression=noalyss_str_replace("-s", "", $p_expression);
389 $p_expression=noalyss_str_replace("-S", "", $p_expression);
390 // we have an account
391 if (preg_match("/\[.*\]/", $p_expression)) {
392 $p_expression=noalyss_str_replace("[", "", $p_expression);
393 $p_expression=noalyss_str_replace("]", "", $p_expression);
394 $P=new Acc_Account_Ledger($p_cn, $p_expression);
395 $detail=$P->get_solde_detail($p_cond_sql);
396 } elseif (preg_match("/\{\{.*\}\}/", $p_expression))
397 {
398 $p_expression=noalyss_str_replace("{", "", $p_expression);
399 $p_expression=noalyss_str_replace("}", "", $p_expression);
400 $anc_account= new Anc_Account($p_cn);
401 $anc_account->load_by_code($p_expression);
402
403 if ( DEBUGNOALYSS > 1)
404 {
405 tracedebug("impress.debug.log", $p_expression, 'code analytic account');
406 tracedebug("impress.debug.log", $p_cond_anc_sql, 'condition SQL ');
407 }
408 /// Transform the $p_cond_sql , it comes from sql_filter_per
409 // and looks like j_tech_per in (select p_id from parm_periode where
410
411 $detail=$anc_account->get_balance($p_cond_anc_sql);
412
413 } elseif (preg_match("/\{.*\}/", $p_expression))
414 { // we have a card
415 // remove useless char
416 $p_expression=noalyss_str_replace("{", "", $p_expression);
417 $p_expression=noalyss_str_replace("}", "", $p_expression);
418 $fiche=new Fiche($p_cn);
419 if ( DEBUGNOALYSS > 1)
420 {
421 tracedebug("impress.debug.log", "$p_expression", 'search_card qcode =');
422 }
423 $fiche->get_by_qcode(strtoupper(trim($p_expression)));
424 $detail=$fiche->get_solde_detail($p_cond_sql);
425 } else {
426 throw new \Exception ("Impress::compute_amount383.".
427 " Unknown expression \$p_expression [$p_expression]".
428 " \$p_cond_sql $p_cond_sql");
429 }
430
431
432
433
434 // Get sum of account
435
436 switch ($compute)
437 {
438 case "all":
439 $res=$detail['solde'];
440 break;
441 case 'deb':
442 $res=$detail['debit'];
443 break;
444 case 'cred':
445 $res=$detail['credit'];
446 break;
447 case 'signed':
448 $res=bcsub($detail['debit'], $detail['credit'], 4);
449 break;
450 case 'cdsigned':
451 $res=bcsub($detail['credit'], $detail['debit'], 4);
452 break;
453 }
454 return $res;
455 }
456
457}
sql_filter_per($p_cn, $p_from, $p_to, $p_form='p_id', $p_field='jr_tech_per')
Create the condition to filter on the j_tech_per thanks a from and to date.
Definition: ac_common.php:682
noalyss_str_replace($search, $replace, $string)
Definition: ac_common.php:1553
remove_divide_zero($p_formula)
When it is needed to eval a formula , this function prevent the divide by zero.
Definition: ac_common.php:1408
global $g_user
if no group available , then stop
$from
Definition: balance.inc.php:61
Manage the account from the table jrn, jrnx or tmp_pcmn.
Analytic account ; get the balance.
define Class fiche and fiche def, those class are using class attribut. When adding or modifing new c...
Definition: fiche.class.php:38
contains function for the parsing and computing formulae .
static find_computing_mode($p_formula)
return what to consider
static compute_periode($p_cn, $p_from, $p_end)
make the condition SQL for filtering on the period
static array_to_csv($array, $aheader, $p_filename)
with the handle of a successull query, echo each row into CSV and send it directly
static compute_amount($p_cn, $p_expression, $p_cond_sql, $p_cond_anc_sql)
compute the amount of the accounting ,analytic accounting or a card, the SQL condition from sql_filte...
const STR_PATTERN
< string pattern to use
static check_formula($p_string)
static parse_formula($p_cn, $p_label, $p_formula, $p_start, $p_end, $p_eval=true, $p_type_date=0, $p_sql="")
Manage the CSV : manage files and write CSV record.
For the periode tables parm_periode and jrn_periode.
$oPeriode
Definition: do.php:154
$p_end
$p_start
if( $delta< 0) elseif( $delta==0)