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 check and warn if an accound has the wrong saldo
* @param $p_message legend of the fieldset
* @param $p_type type of the Acccount ACT actif, ACTINV...
* @param $p_type the saldo must debit or credit
*/
private function warning($p_message, $p_type, $p_deb)
{
$sql = "select pcm_val,pcm_lib from tmp_pcmn where pcm_type='{$p_type}'";
$res = $this->db->exec_sql($sql);
if (Database::num_row($res) == 0) {
return;
}
$count = 0;
$nRow = Database::num_row($res);
$ret = "";
$obj = new Acc_Account_Ledger($this->db, 0);
for ($i = 0; $i < $nRow; $i++) {
$line = Database::fetch_array($res, $i);
/* set the periode filter */
$sql = sql_filter_per($this->db, $this->from, $this->to, 'p_id', 'j_tech_per');
$obj->id = $line['pcm_val'];
$solde = $obj->get_solde_detail($sql);
$solde_signed = bcsub($solde['debit'], $solde['credit']);
if ($solde_signed < 0 && $p_deb == 'D' || $solde_signed > 0 && $p_deb == 'C') {
$ret .= '<li> ' . HtmlInput::history_account($line['pcm_val'], 'Anomalie pour le compte ' . $line['pcm_val'] . ' ' . h($line['pcm_lib']) . " D: " . $solde['debit'] . " C: " . $solde['credit'] . " diff " . $solde['solde']);
$count++;
}
}
echo '<fieldset>';
echo '<legend>' . $p_message . '</legend>';
if ($count != 0) {
echo '<ol>' . $ret . '</ol>';
echo '<span class="error">' . _("Nbres anomalies") . ' : ' . $count . '</span>';
} else {
echo _("Pas d'anomalie détectée");
}
echo '</fieldset>';
}
public function insert($p_array = null)
{
global $g_parameter;
bcscale(2);
$internal_code = "";
$oid = 0;
extract($p_array);
$ret = '';
// Debit = banque
$bank_id = $this->get_bank();
$fBank = new Fiche($this->db, $bank_id);
$e_bank_account = $fBank->strAttribut(ATTR_DEF_QUICKCODE);
// Get the saldo
$pPeriode = new Periode($this->db);
$sposte = $fBank->strAttribut(ATTR_DEF_ACCOUNT);
// if 2 accounts, take only the debit one for customer
if (strpos($sposte, ',') != 0) {
$array = explode(',', $sposte);
$poste_val = $array[0];
} else {
$poste_val = $sposte;
}
$acc_account = new Acc_Account_Ledger($this->db, $poste_val);
// If date = deposit date
if ($chdate == 1) {
if ($this->check_periode() == true) {
$pPeriode->p_id = $periode;
} else {
$pPeriode->find_periode($e_date);
}
$exercice = $pPeriode->get_exercice();
$filter_year = " j_tech_per in (select p_id from parm_periode where p_exercice='" . $exercice . "')";
$asolde = $acc_account->get_solde_detail($filter_year);
$deb = $asolde['debit'];
$cred = $asolde['credit'];
$solde = bcsub($deb, $cred);
$new_solde = $solde;
}
try {
$this->db->start();
$amount = 0.0;
$idx_operation = 0;
$ret = '<table class="result" >';
$ret .= tr(th('Date') . th('n° interne') . th('Quick Code') . th('Nom') . th('Libellé') . th('Montant', ' style="text-align:right"'));
// Credit = goods
$get_solde = true;
for ($i = 0; $i < $nb_item; $i++) {
// insert it into the database
// and quit the loop ?
if (strlen(trim(${"e_other{$i}"})) == 0) {
continue;
}
if ($chdate == 2) {
$e_date = ${'dateop' . $i};
}
// if date is date of operation
if ($chdate == 2 && $get_solde) {
$get_solde = false;
if ($this->check_periode() == true) {
$pPeriode->p_id = $periode;
} else {
$pPeriode->find_periode($e_date);
}
$exercice = $pPeriode->get_exercice();
$filter_year = " j_tech_per in (select p_id from parm_periode where p_exercice='" . $exercice . "')";
$solde = $acc_account->get_solde($filter_year);
$new_solde = $solde;
}
$fPoste = new Fiche($this->db);
$fPoste->get_by_qcode(${"e_other{$i}"});
// round it
${"e_other{$i}" . "_amount"} = round(${"e_other{$i}" . "_amount"}, 2);
$amount += ${"e_other{$i}" . "_amount"};
// Record a line for the bank
// Compute the j_grpt
$seq = $this->db->get_next_seq('s_grpt');
$acc_operation = new Acc_Operation($this->db);
$acc_operation->date = $e_date;
$sposte = $fPoste->strAttribut(ATTR_DEF_ACCOUNT);
// if 2 accounts
if (strpos($sposte, ',') != 0) {
$array = explode(',', $sposte);
if (${"e_other{$i}" . "_amount"} < 0) {
$poste_val = $array[1];
} else {
$poste_val = $array[0];
}
} else {
$poste_val = $sposte;
}
$acc_operation->poste = $poste_val;
$acc_operation->amount = ${"e_other{$i}" . "_amount"} * -1;
$acc_operation->grpt = $seq;
$acc_operation->jrn = $p_jrn;
$acc_operation->type = 'd';
if (isset($periode)) {
$tperiode = $periode;
} else {
$per = new Periode($this->db);
$tperiode = $per->find_periode($e_date);
}
$acc_operation->periode = $tperiode;
$acc_operation->qcode = ${"e_other" . $i};
$j_id = $acc_operation->insert_jrnx();
$acc_operation = new Acc_Operation($this->db);
$acc_operation->date = $e_date;
$sposte = $fBank->strAttribut(ATTR_DEF_ACCOUNT);
// if 2 accounts
if (strpos($sposte, ',') != 0) {
$array = explode(',', $sposte);
if (${"e_other{$i}" . "_amount"} < 0) {
$poste_val = $array[1];
} else {
$poste_val = $array[0];
}
} else {
$poste_val = $sposte;
}
$acc_operation->poste = $poste_val;
$acc_operation->amount = ${"e_other{$i}" . "_amount"};
$acc_operation->grpt = $seq;
$acc_operation->jrn = $p_jrn;
$acc_operation->type = 'd';
$acc_operation->periode = $tperiode;
$acc_operation->qcode = $e_bank_account;
$acc_operation->insert_jrnx();
if (sql_string(${"e_other{$i}" . "_comment"}) == null) {
// if comment is blank set a default one
$comment = " compte : " . $fBank->strAttribut(ATTR_DEF_NAME) . ' a ' . $fPoste->strAttribut(ATTR_DEF_NAME);
} else {
$comment = ${'e_other' . $i . '_comment'};
}
$acc_operation = new Acc_Operation($this->db);
$acc_operation->jrn = $p_jrn;
$acc_operation->amount = abs(${"e_other{$i}" . "_amount"});
$acc_operation->date = $e_date;
$acc_operation->desc = $comment;
$acc_operation->grpt = $seq;
$acc_operation->periode = $tperiode;
$acc_operation->mt = $mt;
$idx_operation++;
$acc_operation->pj = '';
if (trim($e_pj) != '' && $this->numb_operation() == true) {
$acc_operation->pj = $e_pj . str_pad($idx_operation, 3, 0, STR_PAD_LEFT);
}
if (trim($e_pj) != '' && $this->numb_operation() == false) {
$acc_operation->pj = $e_pj;
}
$jr_id = $acc_operation->insert_jrn();
// $acc_operation->set_pj();
$this->db->exec_sql('update jrn set jr_pj_number=$1 where jr_id=$2', array($acc_operation->pj, $jr_id));
$internal = $this->compute_internal_code($seq);
if (trim(${"e_concerned" . $i}) != "") {
if (strpos(${"e_concerned" . $i}, ',') != 0) {
$aRapt = explode(',', ${"e_concerned" . $i});
foreach ($aRapt as $rRapt) {
// Add a "concerned operation to bound these op.together
//
$rec = new Acc_Reconciliation($this->db);
$rec->set_jr_id($jr_id);
if (isNumber($rRapt) == 1) {
$rec->insert($rRapt);
}
}
} else {
if (isNumber(${"e_concerned" . $i}) == 1) {
$rec = new Acc_Reconciliation($this->db);
$rec->set_jr_id($jr_id);
$rec->insert(${"e_concerned{$i}"});
}
}
}
// Set Internal code
$this->grpt_id = $seq;
/**
* save also into quant_fin
*/
$this->insert_quant_fin($fBank->id, $jr_id, $fPoste->id, ${"e_other{$i}" . "_amount"});
if ($g_parameter->MY_ANALYTIC != "nu") {
// for each item, insert into operation_analytique */
$op = new Anc_Operation($this->db);
$op->oa_group = $this->db->get_next_seq("s_oa_group");
/* for analytic */
$op->j_id = $j_id;
$op->oa_date = $e_date;
$op->oa_debit = 'f';
$op->oa_description = sql_string($comment);
$op->save_form_plan($_POST, $i, $j_id);
}
$this->update_internal_code($internal);
$js_detail = HtmlInput::detail_op($jr_id, $internal);
// Compute display
$row = td($e_date) . td($js_detail) . td(${"e_other{$i}"}) . td($fPoste->strAttribut(ATTR_DEF_NAME)) . td(${"e_other" . $i . "_comment"}) . td(nbm(${"e_other{$i}" . "_amount"}), 'class="num"');
$class = $i % 2 == 0 ? ' class="even" ' : ' class="odd" ';
$ret .= tr($row, $class);
if ($i == 0) {
// first record we upload the files and
// keep variable to update other row of jrn
if (isset($_FILES)) {
$oid = $this->db->save_upload_document($seq);
}
} else {
if ($oid != 0) {
$this->db->exec_sql("update jrn set jr_pj=\$1 , jr_pj_name=\$2,\n jr_pj_type=\$3 where jr_grpt_id=\$4", array($oid, $_FILES['pj']['name'], $_FILES['pj']['type'], $seq));
}
}
}
// for nbitem
// increment pj
if (strlen(trim($e_pj)) != 0) {
$this->inc_seq_pj();
}
$ret .= '</table>';
} catch (Exception $e) {
$r = '<span class="error">' . 'Erreur dans l\'enregistrement ' . __FILE__ . ':' . __LINE__ . ' ' . $e->getMessage();
$this->db->rollback();
throw new Exception($r);
}
$this->db->commit();
$r = "";
$r .= "<br>Ancien solde " . nbm($solde);
$new_solde = bcadd($new_solde, $amount);
$r .= "<br>Nouveau solde " . nbm($new_solde);
$ret .= $r;
return $ret;
}
