/**
* @brief retrieve amount of previous periode
* @param $p_to frmo the start of the exercise until $p_to
* @return $array with vat, price,other_amount
* @note
* @see
@code
array
'price' => string '446.1900' (length=8)
'vat' => string '21.7600' (length=7)
'priv' => string '0.0000' (length=6)
'tva_nd_recup' => string '0.0000' (length=6)
'tva' =>
array
0 =>
array
'sum_vat' => string '13.7200' (length=7)
'tva_id' => string '1' (length=1)
1 =>
array
'sum_vat' => string '8.0400' (length=6)
'tva_id' => string '3' (length=1)
2 =>
array
'sum_vat' => string '0.0000' (length=6)
'tva_id' => string '4' (length=1)
@endcode
*/
function previous_amount($p_to)
{
/* get the first periode of exercise */
$periode = new Periode($this->db, $p_to);
$exercise = $periode->get_exercice();
list($min, $max) = $periode->get_limit($exercise);
// transform min into date
$min_date = $min->first_day();
// transform $p_to into date
$periode_max = new Periode($this->db, $p_to);
$max_date = $periode_max->first_day();
bcscale(2);
// min periode
if ($this->type == 'ACH') {
/* get all amount exclude vat */
$sql = "select coalesce(sum(qp_price),0) as price" . " ,coalesce(sum(qp_vat),0) as vat " . ',coalesce(sum(qp_dep_priv),0) as priv' . ',coalesce(sum(qp_vat_sided),0) as reversed' . ',coalesce(sum(qp_nd_tva_recup),0)+coalesce(sum(qp_nd_tva),0) as tva_nd' . ',coalesce(sum(qp_vat_sided),0) as tva_np' . ' from quant_purchase join jrnx using(j_id) ' . " where j_date >= to_date(\$1,'DD.MM.YYYY') and j_date < to_date(\$2,'DD.MM.YYYY') " . ' and j_jrn_def = $3';
$array = $this->db->get_array($sql, array($min_date, $max_date, $this->id));
$ret = $array[0];
/* retrieve all vat code */
$array = $this->db->get_array("select coalesce(sum(qp_vat),0) as sum_vat,tva_id\n from quant_purchase as p right join tva_rate on (qp_vat_code=tva_id) join jrnx using(j_id)\n where tva_rate !=0 and j_date >= to_date(\$1,'DD.MM.YYYY') and j_date < to_date(\$2,'DD.MM.YYYY') \n and j_jrn_def = \$3\n group by tva_id", array($min_date, $max_date, $this->id));
$ret['tva'] = $array;
}
if ($this->type == 'VEN') {
/* get all amount exclude vat */
$sql = "select coalesce(sum(qs_price),0) as price" . " ,coalesce(sum(qs_vat),0) as vat " . ',0 as priv' . ',0 as tva_nd' . ',coalesce(sum(qs_vat_sided),0) as tva_np' . ' from quant_sold join jrnx using(j_id) ' . " where j_date >= to_date(\$1,'DD.MM.YYYY') and j_date < to_date(\$2,'DD.MM.YYYY') " . ' and j_jrn_def = $3';
$array = $this->db->get_array($sql, array($min_date, $max_date, $this->id));
$ret = $array[0];
/* retrieve all vat code */
$array = $this->db->get_array("select coalesce(sum(qs_vat),0) as sum_vat,tva_id\n from quant_sold as p right join tva_rate on (qs_vat_code=tva_id) join jrnx using(j_id)\n where tva_rate !=0 and\n j_date >= to_date(\$1,'DD.MM.YYYY') and j_date < to_date(\$2,'DD.MM.YYYY') \n and j_jrn_def = \$3\n group by tva_id", array($min_date, $max_date, $this->id));
$ret['tva'] = $array;
}
if ($this->type == "FIN") {
/* find the quick code of this ledger */
$ledger = new Acc_Ledger_Fin($this->db, $this->id);
$qcode = $ledger->get_bank();
$bank_card = new Fiche($this->db, $qcode);
/*add the amount from Opening Writing */
$cond = sprintf(" j_jrn_def <> %d and j_date >= to_date('%s','DD.MM.YYYY') and j_date < to_date('%s','DD.MM.YYYY') ", $this->id, $min_date, $max_date);
$saldo = $bank_card->get_bk_balance($cond);
$ret['amount'] = bcsub($saldo['debit'], $saldo['credit']);
}
return $ret;
}
echo HtmlInput::hidden('type', 'bilan');
echo dossier::hidden();
echo $bilan->display_form($filter_year);
echo '<span class="notice"> ' . _('Attention : si le bilan n\'est pas équilibré.<br> Vérifiez <ul>
<li>L\'affectation du résultat est fait</li>
<li>Vos comptes actifs ont un solde débiteur (sauf les comptes dit inversés)</li>
<li> les comptes passifs ont un solde créditeur (sauf les comptes dit inversés) </li>
</ul>
Utilisez la balance des comptes pour vérifier.') . ' </span>';
echo HtmlInput::submit('verif', _('Verification comptabilite'));
echo HtmlInput::get_to_hidden(array('ac', 'exercice'));
echo '</FORM>';
}
if (isset($_GET['verif'])) {
$periode = new Periode($cn);
$date_from = $periode->first_day($bilan->from);
$date_to = $periode->last_day($bilan->to);
echo '<h2>' . _('Etape 2 :Impression') . " " . $date_from . '-' . $date_to . '</h2>';
$bilan->get_request_get();
$bilan->verify();
$url_verify = http_build_query(array('ac' => 'VERIFBIL', 'gDossier' => dossier::id()));
echo _('Pour une vérification complète, allez dans ') . '<a class="line" TARGET="_blank" href="?' . $url_verify . '"> VERIFBIL</a>';
echo '<FORM METHOD="GET" ACTION="export.php">';
echo dossier::hidden();
echo HtmlInput::get_to_hidden(array('exercice'));
echo HtmlInput::hidden('b_id', $_GET['b_id']);
echo HtmlInput::hidden('act', 'OTH:Bilan');
echo HtmlInput::hidden('from_periode', $bilan->from);
echo HtmlInput::hidden('to_periode', $bilan->to);
echo HtmlInput::submit('Impression', 'Impression');
echo '</form>';
/**
* Return an array, used by Stock_Goods::input
* @global type $cn
* @param type $p_array
* @throws Exception
*/
function take_last_inventory($p_array)
{
global $cn;
$year = HtmlInput::default_value("p_exercice", "", $p_array);
$depot = HtmlInput::default_value("p_depot", "", $p_array);
if ($year == "") {
throw new Exception(_('Inventaire invalide'), 10);
}
if ($depot == "") {
throw new Exception(_('Dépôt invalide'), 20);
}
// compute state_exercice
$periode = new Periode($cn);
$periode->p_id = $cn->get_value("select min(p_id) from parm_periode where p_exercice=\$1", array($year));
$first_day = $periode->first_day();
// compute array for stock
$array['state_exercice'] = $first_day;
$stock = new Stock($cn);
$rowid = $stock->build_tmp_table($array);
// compute first day of the next year
$next_year = $year + 1;
$periode = new Periode($cn);
$periode->p_id = $cn->get_value("select min(p_id) from parm_periode where p_exercice=\$1", array($next_year));
if ($periode->p_id == "") {
$array['p_date'] = "";
} else {
$array['p_date'] = $periode->first_day();
}
// Compute an array compatible with Stock_Goods::input
$array['p_motif'] = _('Inventaire ') . $year;
$array['p_depot'] = $depot;
$result = $cn->get_array("\n select sg_code,sum(coalesce(s_qin,0)-coalesce(s_qout,0)) tot_\n from tmp_stockgood_detail \n where \n s_id=\$1 and r_id=\$2 \n group by sg_code", array($rowid, $depot));
for ($e = 0; $e < count($result); $e++) {
$array['sg_code' . $e] = $result[$e]['sg_code'];
$array['sg_quantity' . $e] = $result[$e]['tot_'];
}
$array['row'] = $e;
return $array;
}
/**
*@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;
}
