function get_row($p_start, $p_end, $p_type_date)
{
$Res = $this->db->exec_sql("select fo_id ,\n fo_fr_id,\n fo_pos,\n fo_label,\n fo_formula,\n fr_label from form\n inner join formdef on fr_id=fo_fr_id\n where fr_id =" . $this->id . "order by fo_pos");
$Max = Database::num_row($Res);
if ($Max == 0) {
$this->row = 0;
return null;
}
$col = array();
for ($i = 0; $i < $Max; $i++) {
$l_line = Database::fetch_array($Res, $i);
$col[] = Impress::parse_formula($this->db, $l_line['fo_label'], $l_line['fo_formula'], $p_start, $p_end, true, $p_type_date);
}
//for ($i
$this->row = $col;
return $col;
}
function compute_formula($p_handle)
{
while (!feof($p_handle)) {
$buffer = trim(fgets($p_handle));
// $a=(Impress::check_formula($buffer) == true)?"$buffer ok<br>":'<font color="red">'.'Pas ok '.$buffer."</font><br>";
// echo $a;
// blank line are skipped
if (strlen(trim($buffer)) == 0) {
continue;
}
// skip comment
if (strpos($buffer, '#') === true) {
continue;
}
// buffer contains a formula A$=....
// We need to eval it
$a = Impress::parse_formula($this->db, "{$buffer}", $buffer, $this->from, $this->to, false);
$b = str_replace("\$", "\$this->", $a);
if (eval("{$b};") === false) {
echo_debug(__FILE__, __LINE__, "Code failed with {$b}");
}
}
// end read form line per line
}
function display_csv()
{
$r = "";
//---Html
$array = $this->load_csv();
if (is_array($array) == false) {
return $array;
}
$ix = 0;
$prev = 'xx';
$tot_deb = $tot_cred = 0;
$aheader = array();
$aheader[] = array("title" => 'Imp. Analytique', 'type' => 'string');
$aheader[] = array("title" => 'Date', 'type' => 'string');
$aheader[] = array("title" => 'Poste', 'type' => 'string');
$aheader[] = array("title" => 'Quick_Code', 'type' => 'string');
$aheader[] = array("title" => 'libelle', 'type' => 'string');
$aheader[] = array("title" => 'Pièce', 'type' => 'string');
$aheader[] = array("title" => 'Num.interne', 'type' => 'string');
$aheader[] = array("title" => 'row', 'type' => 'num');
$aheader[] = array("title" => 'Debit', 'type' => 'string');
$aheader[] = array("title" => 'Credit', 'type' => 'num');
Impress::array_to_csv($array, $aheader);
}
static function parse_formula($p_cn, $p_label, $p_formula, $p_start, $p_end, $p_eval = true, $p_type_date = 0, $p_sql = "")
{
global $g_user;
if (Impress::check_formula($p_formula) == false) {
if ($p_eval == true) {
return array('desc' => $p_label . ' Erreur Formule!', 'montant' => 0);
} else {
return $p_formula;
}
}
if ($p_type_date == 0) {
$cond = sql_filter_per($p_cn, $p_start, $p_end, 'p_id', 'j_tech_per');
} else {
$cond = "( j_date >= to_date('{$p_start}','DD.MM.YYYY') and j_date <= to_date('{$p_end}','DD.MM.YYYY'))";
}
include_once "class_acc_account_ledger.php";
while (preg_match_all("(\\[[0-9]*[A-Z]*%*c*d*s*\\])", $p_formula, $e) == true) {
// remove the [ ]
$x = $e[0];
foreach ($x as $line) {
$compute = 'all';
if (strpos($line, 'd') != 0) {
$compute = 'deb';
}
if (strpos($line, 'c') != 0) {
$compute = 'cred';
}
if (strpos($line, 's') != 0) {
$compute = 'signed';
}
$line = str_replace("[", "", $line);
$line = str_replace("]", "", $line);
$line = str_replace("d", "", $line);
$line = str_replace("c", "", $line);
$line = str_replace("s", "", $line);
// If there is a FROM clause we must recompute
// the time cond
if ($p_type_date == 0 && preg_match("/FROM=[0-9]+\\.[0-9]+/", $p_formula, $afrom) == 1) {
// There is a FROM clause
// then we must modify the cond for the periode
$from = str_replace("FROM=", "", $afrom[0]);
// Get the periode
/*! \note special value for the clause FROM=00.0000
*/
if ($from == '00.0000') {
// retrieve the first month of this periode
$user_periode = $g_user->get_periode();
$oPeriode = new Periode($p_cn);
$periode = $oPeriode->get_exercice($user_periode);
list($first, $last) = $oPeriode->get_limit($periode);
$ret = $first->get_date_limit();
$end_date = $oPeriode->get_date_limit($p_end);
if ($ret == null) {
throw new Exception('Pas de limite à cette période', 1);
}
$cond = sql_filter_per($p_cn, $ret['p_start'], $end_date['p_end'], 'date', 'j_tech_per');
} else {
$oPeriode = new Periode($p_cn);
try {
$pfrom = $oPeriode->find_periode('01.' . $from);
$cond = sql_filter_per($p_cn, $pfrom, $p_end, 'p_id', 'j_tech_per');
} catch (Exception $exp) {
/* if none periode is found
then we take the first periode of the year
*/
$user_periode = $g_user->get_periode();
$year = $oPeriode->get_exercice($user_periode);
list($first, $last) = $oPeriode->get_limit($year);
$ret = $first->get_date_limit();
$end_date = $oPeriode->get_date_limit($p_end);
if ($ret == null) {
throw new Exception('Pas de limite à cette période', 1);
}
$cond = sql_filter_per($p_cn, $ret['p_start'], $end_date['p_end'], 'date', 'j_tech_per');
}
}
}
if (strpos($p_formula, "FROM") != 0) {
// We remove FROM out of the p_formula
$p_formula = substr_replace($p_formula, "", strpos($p_formula, "FROM"));
}
// Get sum of account
$P = new Acc_Account_Ledger($p_cn, $line);
$detail = $P->get_solde_detail($cond . $p_sql);
if ($compute == 'all') {
$i = $detail['solde'];
}
if ($compute == 'deb') {
$i = $detail['debit'];
}
if ($compute == 'cred') {
$i = $detail['credit'];
}
if ($compute == 'signed') {
$i = $detail['debit'] - $detail['credit'];
}
$p_formula = str_replace($x[0], $i, $p_formula);
}
}
// $p_eval is true then we eval and returns result
if ($p_eval == true) {
$p_formula = "\$result=" . $p_formula . ";";
eval("{$p_formula}");
while (preg_match("/\\[([0-9]+)([Tt]*)\\]/", trim($p_label), $e) == 1) {
$nom = "!!" . $e[1] . "!!";
if (Impress::check_formula($e[0])) {
$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", array($e[1]));
if ($nom) {
if ($e[2] == 'T') {
$nom = strtoupper($nom);
}
if ($e[2] == 't') {
$nom = strtolower($nom);
}
}
}
$p_label = str_replace($e[0], $nom, $p_label);
}
$aret = array('desc' => $p_label, 'montant' => $result);
return $aret;
} else {
// $p_eval is false we returns only the string
return $p_formula;
}
}
/**
*@brief Display the result of the forecast
*@param $p_periode
*@return HTML String with the code
*/
public function display()
{
bcscale(4);
$forecast = new Forecast($this->cn, $this->f_id);
$forecast->load();
$str_name = h($forecast->get_parameter('name'));
$start = $forecast->get_parameter('start_date');
$end = $forecast->get_parameter('end_date');
if ($start == '') {
throw new Exception(_('Période de début non valable'));
}
if ($end == '') {
throw new Exception(_('Période de fin non valable'));
}
$per = new Periode($this->cn, $start);
$str_start = format_date($per->first_day());
$per = new Periode($this->cn, $end);
$str_end = format_date($per->last_day());
$r = "";
$aCat = $this->cn->get_array('select fc_id,fc_desc from forecast_cat where f_id=$1 order by fc_order', array($this->f_id));
$aItem = array();
$aReal = array();
$poste = new Acc_Account_Ledger($this->cn, 0);
$fiche = new Fiche($this->cn);
$aPeriode = $this->cn->get_array("select p_id,to_char(p_start,'MM.YYYY') as myear from parm_periode\n\t where p_start >= (select p_start from parm_periode where p_id={$start})\n and p_end <= (select p_end from parm_periode where p_id={$end})\n\t\t\t\t\t order by p_start;");
$error = array();
for ($j = 0; $j < count($aCat); $j++) {
$aItem[$j] = $this->cn->get_array('select fi_card,fi_account,fi_text,fi_amount,fi_debit from forecast_item where fc_id=$1 and fi_pid=0 order by fi_order ', array($aCat[$j]['fc_id']));
$aPerMonth[$j] = $this->cn->get_array('select fi_pid,fi_card,fi_account,fi_text,fi_amount,fi_debit from forecast_item where fc_id=$1 and fi_pid !=0 order by fi_order ', array($aCat[$j]['fc_id']));
/* compute the real amount for periode */
for ($k = 0; $k < count($aItem[$j]); $k++) {
/* for each periode */
for ($l = 0; $l < count($aPeriode); $l++) {
if ($aItem[$j][$k]['fi_account'] == '') {
$fiche->id = $aItem[$j][$k]['fi_card'];
$amount = $fiche->get_solde_detail("j_tech_per = " . $aPeriode[$l]['p_id']);
if ($aItem[$j][$k]['fi_debit'] == 'C' && $amount['debit'] > $amount['credit']) {
$amount['solde'] = $amount["solde"] * -1;
}
if ($aItem[$j][$k]['fi_debit'] == 'D' && $amount['debit'] < $amount['credit']) {
$amount['solde'] = $amount["solde"] * -1;
}
} else {
$poste->id = $aItem[$j][$k]['fi_account'];
$aresult = Impress::parse_formula($this->cn, "OK", $poste->id, $aPeriode[$l]['p_id'], $aPeriode[$l]['p_id']);
$tmp_label = $aresult['desc'];
$amount['solde'] = $aresult['montant'];
if ($tmp_label != 'OK') {
$error[] = "<li> " . $aItem[$j][$k]['fi_text'] . $poste->id . '</li>';
}
}
$aReal[$j][$k][$l] = $amount['solde'];
}
}
}
ob_start();
require_once NOALYSS_INCLUDE . '/template/forecast_result.php';
$r .= ob_get_contents();
ob_end_clean();
return $r;
}
<?php
/*
# check the Impress::check_formula and the parse_formula
# This file is a part of NOALYSS under GPL
# Author D. DE BONTRIDDER danydb@aevalys.eu
*/
include_once 'class_impress.php';
echo '<hr>';
foreach (array('1', '(45+5)', 'round([45])', '$A=9', '$S30=($F1 >=0)?$F1:0', '[45%]', '[50]*[51%]', '$A1=[50]*[51%]', '[50]*9', '[50]*9.0', '[50%]*9', '$C1111=[50%]*9', '$C1111=[50%]*9*$D1', '$C10=[10%]', '[50%]*9.0', '[50%]*9.0 FROM=01.2004', '[50%]*9.0FROM=01.2004', 'system', 'unlink', 'ls -1') as $a) {
echo "Testing :" . $a;
echo Impress::check_formula($a) == false ? 'Non valide ' : 'ok';
echo '<br>';
foreach (array('+', '-', '/') as $b) {
$ee = str_replace('*', $b, $a);
echo "Testing :" . $ee;
echo Impress::check_formula($ee) == false ? 'Non valide ' : 'ok';
echo '<br>';
}
for ($e = 0; $e < 3; $e++) {
$a .= "*" . $a;
echo "Testing :" . $a;
echo Impress::check_formula($a) == false ? 'Non valide ' : 'ok';
echo '<br>';
}
}
