function verify()
{
bcscale(2);
echo '<h3>' . _("Comptes normaux") . '</h3>';
$this->warning(_('Actif avec un solde crediteur'), 'ACT', 'D');
$this->warning(_('Passif avec un solde debiteur'), 'PAS', 'C');
$this->warning(_('Compte de resultat : Charge avec un solde crediteur'), 'CHA', 'D');
$this->warning(_('Compte de resultat : produit avec un solde debiteur'), 'PRO', 'C');
echo '<hr>';
echo '<h3>' . _("Comptes inverses") . ' </h3>';
$this->warning(_('Compte inverse : actif avec un solde debiteur'), 'ACTINV', 'C');
$this->warning(_('Compte inverse : passif avec un solde crediteur'), 'PASINV', 'D');
$this->warning(_('Compte inverse : Charge avec un solde debiteur'), 'CHAINV', 'C');
$this->warning(_('Compte inverse : produit avec un solde crediteur'), 'PROINV', 'D');
echo '<h3' . _("Solde") . ' </h3>';
/* set the periode filter */
$sql_periode = sql_filter_per($this->db, $this->from, $this->to, 'p_id', 'j_tech_per');
/* debit Actif */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='t' and (pcm_type='ACT' or pcm_type='ACTINV')";
$sql .= "and {$sql_periode}";
$debit_actif = $this->db->get_value($sql);
/* Credit Actif */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='f' and (pcm_type='ACT' or pcm_type='ACTINV')";
$sql .= "and {$sql_periode}";
$credit_actif = $this->db->get_value($sql);
$total_actif = abs(bcsub($debit_actif, $credit_actif));
echo '<table >';
echo tr(td(_('Total actif')) . td($total_actif, 'style="text-align:right"'));
/* debit passif */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='t' and (pcm_type='PAS' or pcm_type='PASINV') ";
$sql .= "and {$sql_periode}";
$debit_passif = $this->db->get_value($sql);
/* Credit Actif */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='f' and (pcm_type='PAS' or pcm_type='PASINV') ";
$sql .= "and {$sql_periode}";
$credit_passif = $this->db->get_value($sql);
$total_passif = abs(bcsub($debit_passif, $credit_passif));
/* diff actif / passif */
echo tr(td(_('Total passif')) . td($total_passif, 'style="text-align:right"'));
if ($total_actif != $total_passif) {
$diff = bcsub($total_actif, $total_passif);
echo tr(td(' Difference Actif - Passif ') . td($diff, 'style="text-align:right"'), 'style="font-weight:bolder"');
}
/* debit charge */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='t' and (pcm_type='CHA' or pcm_type='CHAINV')";
$sql .= "and {$sql_periode}";
$debit_charge = $this->db->get_value($sql);
/* Credit charge */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='f' and (pcm_type='CHA' or pcm_type='CHAINV')";
$sql .= "and {$sql_periode}";
$credit_charge = $this->db->get_value($sql);
$total_charge = abs(bcsub($debit_charge, $credit_charge));
echo tr(td(_('Total charge ')) . td($total_charge, 'style="text-align:right"'));
/* debit prod */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='t' and (pcm_type='PRO' or pcm_type='PROINV')";
$sql .= "and {$sql_periode}";
$debit_pro = $this->db->get_value($sql);
/* Credit prod */
$sql = "select sum(j_montant) from jrnx join tmp_pcmn on (j_poste=pcm_val)" . " where j_debit='f' and (pcm_type='PRO' or pcm_type='PROINV')";
$sql .= "and {$sql_periode}";
$credit_pro = $this->db->get_value($sql);
$total_pro = abs(bcsub($debit_pro, $credit_pro));
echo tr(td(_('Total produit')) . td($total_pro, 'style="text-align:right"'));
$diff = bcsub($total_pro, $total_charge);
echo tr(td(_("Difference Produit - Charge"), 'style="padding-right:20px"') . td($diff, 'style="text-align:right"'), 'style="font-weight:bolder"');
echo '</table>';
}
function get_balance($p_from, $p_to, $p_plan_id)
{
// for the operation connected to jrnx
$cond = sql_filter_per($this->db, $p_from, $p_to, 'p_id', 'j_date');
$sql = "select oa_id, po_id, oa_amount, oa_debit, j_date from jrnx join operation_analytique using (j_id)\n join poste_analytique using (po_id)\n where\n {$cond} and j_id is not null and pa_id={$p_plan_id}";
// OD
$cond = sql_filter_per($this->db, $p_from, $p_to, 'p_id', 'oa_date');
$sql = "union select oa_id, po_id, oa_amount, oa_debit,oa_date from\n operation_analytique\n join poste_analytique using (po_id)\n where j_id is null and\n {$cond} and pa_id={$p_plan_id} ";
try {
$res = $this->db->exec_sql($sql);
$array = Database::fetch_all($res);
} catch (Exception $e) {
var_dump($e);
}
}
function get_row($p_from_periode, $p_to_periode, $p_previous_exc = 0)
{
global $g_user;
// filter on requested periode
$per_sql = sql_filter_per($this->db, $p_from_periode, $p_to_periode, 'p_id', 'j_tech_per');
$and = "";
$jrn = "";
$from_poste = "";
$to_poste = "";
/* if several ledgers are asked then we filter here */
if ($this->jrn !== null) {
/**
*@file
*@bug the get_ledger here is not valid and useless we just need a list of the
* asked ledgers
*/
$jrn = " j_jrn_def in (";
$comma = '';
for ($e = 0; $e < count($this->jrn); $e++) {
$jrn .= $comma . $this->jrn[$e];
$comma = ',';
}
$jrn .= ')';
$and = " and ";
}
if (strlen(trim($this->from_poste)) != 0 && $this->from_poste != -1) {
$from_poste = " {$and} j_poste::text >= '" . $this->from_poste . "'";
$and = " and ";
}
if (strlen(trim($this->to_poste)) != 0 && $this->to_poste != -1) {
$to_poste = " {$and} j_poste::text <= '" . $this->to_poste . "'";
$and = " and ";
}
$filter_sql = $g_user->get_ledger_sql('ALL', 3);
switch ($p_previous_exc) {
case 0:
// build query
$sql = "select j_poste as poste,sum(deb) as sum_deb, sum(cred) as sum_cred from\n ( select j_poste,\n case when j_debit='t' then j_montant else 0 end as deb,\n case when j_debit='f' then j_montant else 0 end as cred\n from jrnx join tmp_pcmn on (j_poste=pcm_val)\n left join parm_periode on (j_tech_per = p_id)\n join jrn_def on (j_jrn_def=jrn_def_id)\n where\n {$jrn} {$from_poste} {$to_poste}\n {$and} {$filter_sql}\n and\n {$per_sql} ) as m group by 1 order by 1";
break;
case 1:
/*
* retrieve balance previous exercice
*/
$periode = new Periode($this->db);
$previous_exc = $periode->get_exercice($p_from_periode) - 1;
try {
list($previous_start, $previous_end) = $periode->get_limit($previous_exc);
$per_sql_previous = sql_filter_per($this->db, $previous_start->p_id, $previous_end->p_id, 'p_id', 'j_tech_per');
$sql = "\n with m as \n ( select j_poste,sum(deb) as sdeb,sum(cred) as scred\n from \n (select j_poste, \n case when j_debit='t' then j_montant else 0 end as deb, \n case when j_debit='f' then j_montant else 0 end as cred \n from jrnx \n join tmp_pcmn on (j_poste=pcm_val) \n left join parm_periode on (j_tech_per = p_id) \n join jrn_def on (j_jrn_def=jrn_def_id) \n where\n {$jrn} {$from_poste} {$to_poste}\n {$and} {$filter_sql} and {$per_sql}\n ) as sub_m group by j_poste order by j_poste ) , \n p as ( select j_poste,sum(deb) as sdeb,sum(cred) as scred \n from \n (select j_poste, \n case when j_debit='t' then j_montant else 0 end as deb, \n case when j_debit='f' then j_montant else 0 end as cred \n from jrnx join tmp_pcmn on (j_poste=pcm_val) \n left join parm_periode on (j_tech_per = p_id) \n join jrn_def on (j_jrn_def=jrn_def_id) \n where \n {$jrn} {$from_poste} {$to_poste}\n {$and} {$filter_sql} and {$per_sql_previous}) as sub_p group by j_poste order by j_poste)\n select coalesce(m.j_poste,p.j_poste) as poste\n ,coalesce(m.sdeb,0) as sum_deb\n , coalesce(m.scred,0) as sum_cred \n ,coalesce(p.sdeb,0) as sum_deb_previous\n , coalesce(p.scred,0) as sum_cred_previous from m full join p on (p.j_poste=m.j_poste)\n order by poste";
} catch (Exception $exc) {
$p_previous_exc = 0;
/*
* no previous exercice
*/
$sql = "select upper(j_poste::text) as poste,sum(deb) as sum_deb, sum(cred) as sum_cred from\n ( select j_poste,\n case when j_debit='t' then j_montant else 0 end as deb,\n case when j_debit='f' then j_montant else 0 end as cred\n from jrnx join tmp_pcmn on (j_poste=pcm_val)\n left join parm_periode on (j_tech_per = p_id)\n join jrn_def on (j_jrn_def=jrn_def_id)\n where\n {$jrn} {$from_poste} {$to_poste}\n {$and} {$filter_sql}\n and\n {$per_sql} ) as m group by poste order by poste";
}
break;
}
$cn = clone $this->db;
$Res = $this->db->exec_sql($sql);
$tot_cred = 0.0;
$tot_deb = 0.0;
$tot_deb_saldo = 0.0;
$tot_cred_saldo = 0.0;
$tot_cred_previous = 0.0;
$tot_deb_previous = 0.0;
$tot_deb_saldo_previous = 0.0;
$tot_cred_saldo_previous = 0.0;
$M = $this->db->size();
// Load the array
for ($i = 0; $i < $M; $i++) {
$r = $this->db->fetch($i);
$poste = new Acc_Account($cn, $r['poste']);
$a['poste'] = $r['poste'];
$a['label'] = mb_substr($poste->get_lib(), 0, 40);
$a['sum_deb'] = round($r['sum_deb'], 2);
$a['sum_cred'] = round($r['sum_cred'], 2);
$a['solde_deb'] = round($a['sum_deb'] >= $a['sum_cred'] ? $a['sum_deb'] - $a['sum_cred'] : 0, 2);
$a['solde_cred'] = round($a['sum_deb'] <= $a['sum_cred'] ? $a['sum_cred'] - $a['sum_deb'] : 0, 2);
if ($p_previous_exc == 1) {
$a['sum_deb_previous'] = round($r['sum_deb_previous'], 2);
$a['sum_cred_previous'] = round($r['sum_cred_previous'], 2);
$a['solde_deb_previous'] = round($a['sum_deb_previous'] >= $a['sum_cred_previous'] ? $a['sum_deb_previous'] - $a['sum_cred_previous'] : 0, 2);
$a['solde_cred_previous'] = round($a['sum_deb_previous'] <= $a['sum_cred_previous'] ? $a['sum_cred_previous'] - $a['sum_deb_previous'] : 0, 2);
$tot_cred_previous += $a['sum_cred_previous'];
$tot_deb_previous += $a['sum_deb_previous'];
$tot_deb_saldo_previous += $a['solde_deb_previous'];
$tot_cred_saldo_previous += $a['solde_cred_previous'];
}
if ($p_previous_exc == 0 && $this->unsold == true && $a['solde_cred'] == 0 && $a['solde_deb'] == 0) {
continue;
}
if ($p_previous_exc == 1 && $this->unsold == true && $a['solde_cred'] == 0 && $a['solde_deb'] == 0 && $a['solde_cred_previous'] == 0 && $a['solde_deb_previous'] == 0) {
continue;
}
$array[$i] = $a;
$tot_cred += $a['sum_cred'];
$tot_deb += $a['sum_deb'];
$tot_deb_saldo += $a['solde_deb'];
$tot_cred_saldo += $a['solde_cred'];
}
//for i
// Add the saldo
$i += 1;
$a['poste'] = "";
$a['label'] = "Totaux ";
$a['sum_deb'] = $tot_deb;
$a['sum_cred'] = $tot_cred;
$a['solde_deb'] = $tot_deb_saldo;
$a['solde_cred'] = $tot_cred_saldo;
if ($p_previous_exc == 1) {
$a['sum_deb_previous'] = $tot_deb_previous;
$a['sum_cred_previous'] = $tot_cred_previous;
$a['solde_deb_previous'] = $tot_deb_saldo_previous;
$a['solde_cred_previous'] = $tot_cred_saldo_previous;
}
$array[$i] = $a;
$this->row = $array;
return $array;
}
/**
* @brief get the saldo of a ledger for a specific period
* @param$p_from start period
* @param$p_to end period
*/
function get_solde($p_from, $p_to)
{
$ledger = "";
if ($this->id != 0) {
$ledger = " and j_jrn_def = " . $this->id;
}
$periode = sql_filter_per($this->db, $p_from, $p_to, 'p_id', 'j_tech_per');
$sql = 'select j_montant as montant,j_debit as deb from jrnx where ' . $periode . $ledger;
$ret = $this->db->exec_sql($sql);
$array = Database::fetch_all($ret);
$deb = 0.0;
$cred = 0.0;
foreach ($array as $line) {
if ($line['deb'] == 't') {
$deb += $line['montant'];
} else {
$cred += $line['montant'];
}
}
$response = array($deb, $cred);
return $response;
}
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;
}
}
function get_row($p_from, $p_to)
{
if ($this->id == 0) {
echo_error("class_fiche", __LINE__, "id is 0");
return;
}
$qcode = $this->strAttribut(ATTR_DEF_QUICKCODE);
$periode = sql_filter_per($this->cn, $p_from, $p_to, 'p_id', 'jr_tech_per');
$Res = $this->cn->exec_sql("select j_date,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,j_qcode," . "case when j_debit='t' then j_montant else 0 end as deb_montant," . "case when j_debit='f' then j_montant else 0 end as cred_montant," . " jr_comment as description,jrn_def_name as jrn_name," . "j_debit, jr_internal,jr_id " . " from jrnx left join jrn_def on jrn_def_id=j_jrn_def " . " left join jrn on jr_grpt_id=j_grpt" . " where j_qcode='" . $qcode . "' and " . $periode . " order by j_date::date");
return $this->get_row_result($Res);
}
function get_row($p_from, $p_to)
{
$periode = sql_filter_per($this->db, $p_from, $p_to, 'p_id', 'jr_tech_per');
$Res = $this->db->exec_sql("select distinct j_id,jr_id,to_char(j_date,'DD.MM.YYYY') as j_date_fmt,j_date," . "case when j_debit='t' then j_montant else 0 end as deb_montant," . "case when j_debit='f' then j_montant else 0 end as cred_montant," . " jr_comment as description,jrn_def_name as jrn_name," . "j_debit, jr_internal,jr_pj_number " . " from jrnx left join jrn_def on jrn_def_id=j_jrn_def " . " left join jrn on jr_grpt_id=j_grpt" . " where j_poste=" . $this->id . " and " . $periode . " order by j_date");
return $this->get_row_sql($Res);
}
