/**
* reverse the operation by creating the opposite one,
* the result is to avoid it
* it must be done in
* - jrn
* - jrnx
* - quant_fin
* - quant_sold
* - quant_purchase
* - stock
* - ANC
* @param $p_date is the date of the reversed op
* @exception if date is invalid or other prob
* @note automatically create a reconciliation between operation
* You must set the ledger_id $this->jrn_def_id
* This function should be in operation or call an acc_operation object
*
*/
function reverse($p_date)
{
global $g_user;
try {
$this->db->start();
if (!isset($this->jr_id) || $this->jr_id == '') {
throw new Exception(_("this->jr_id is not set ou opération inconnue"));
}
/* check if the date is valid */
if (isDate($p_date) == null) {
throw new Exception(_('Date invalide') . $p_date);
}
// if the operation is in a closed or centralized period
// the operation is voided thanks the opposite operation
$grp_new = $this->db->get_next_seq('s_grpt');
$seq = $this->db->get_next_seq("s_jrn");
$p_internal = $this->compute_internal_code($seq);
$this->jr_grpt_id = $this->db->get_value('select jr_grpt_id from jrn where jr_id=$1', array($this->jr_id));
if ($this->db->count() == 0) {
throw new Exception(_("Cette opération n'existe pas"));
}
$this->jr_internal = $this->db->get_value('select jr_internal from jrn where jr_id=$1', array($this->jr_id));
if ($this->db->count() == 0 || trim($this->jr_internal) == '') {
throw new Exception(_("Cette opération n'existe pas"));
}
/* find the periode thanks the date */
$per = new Periode($this->db);
$per->jrn_def_id = $this->id;
$per->find_periode($p_date);
if ($per->is_open() == 0) {
throw new Exception(_('PERIODE FERMEE'));
}
// Mark the operation invalid into the ledger
// to avoid to nullify twice the same op.
$sql = "update jrn set jr_comment='extourne : '||jr_comment where jr_id=\$1";
$Res = $this->db->exec_sql($sql, array($this->jr_id));
// Check return code
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "sql a echoue [ {$sql} ]");
}
//////////////////////////////////////////////////
// Reverse in jrnx* tables
//////////////////////////////////////////////////
$a_jid = $this->db->get_array("select j_id,j_debit from jrnx where j_grpt=\$1", array($this->jr_grpt_id));
for ($l = 0; $l < count($a_jid); $l++) {
$row = $a_jid[$l]['j_id'];
// Make also the change into jrnx
$sql = "insert into jrnx (\n j_date,j_montant,j_poste,j_grpt,\n j_jrn_def,j_debit,j_text,j_internal,j_tech_user,j_tech_per,j_qcode\n ) select to_date(\$1,'DD.MM.YYYY'),j_montant,j_poste,\$2,\n j_jrn_def,not (j_debit),j_text,\$3,\$4,\$5,\n j_qcode\n from\n jrnx\n where j_id=\$6 returning j_id";
$Res = $this->db->exec_sql($sql, array($p_date, $grp_new, $p_internal, $g_user->id, $per->p_id, $row));
// Check return code
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "SQL ERROR [ {$sql} ]");
}
$aj_id = $this->db->fetch(0);
$j_id = $aj_id['j_id'];
/* automatic lettering */
$let = new Lettering($this->db);
$let->insert_couple($j_id, $row);
// reverse in QUANT_SOLD
$Res = $this->db->exec_sql("INSERT INTO quant_sold(\n qs_internal, qs_fiche, qs_quantite, qs_price, qs_vat,\n qs_vat_code, qs_client, qs_valid, j_id)\n SELECT \$1, qs_fiche, qs_quantite*(-1), qs_price*(-1), qs_vat*(-1),\n qs_vat_code, qs_client, qs_valid, \$2\n FROM quant_sold where j_id=\$3", array($p_internal, $j_id, $row));
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "sql a echoue [ {$sql} ]");
}
$Res = $this->db->exec_sql("INSERT INTO quant_purchase(\n qp_internal, j_id, qp_fiche, qp_quantite, qp_price, qp_vat,\n qp_vat_code, qp_nd_amount, qp_nd_tva, qp_nd_tva_recup, qp_supplier,\n qp_valid, qp_dep_priv)\n SELECT \$1, \$2, qp_fiche, qp_quantite*(-1), qp_price*(-1), qp_vat*(-1),\n qp_vat_code, qp_nd_amount*(-1), qp_nd_tva*(-1), qp_nd_tva_recup*(-1), qp_supplier,\n qp_valid, qp_dep_priv*(-1)\n FROM quant_purchase where j_id=\$3", array($p_internal, $j_id, $row));
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "SQL ERROR [ {$sql} ]");
}
}
$sql = "insert into jrn (\n jr_id,\n jr_def_id,\n jr_montant,\n jr_comment,\n jr_date,\n jr_grpt_id,\n jr_internal\n ,jr_tech_per, jr_valid\n )\n select \$1,jr_def_id,jr_montant,jr_comment,\n to_date(\$2,'DD.MM.YYYY'),\$3,\$4,\n \$5, true\n from\n jrn\n where jr_id=\$6";
$Res = $this->db->exec_sql($sql, array($seq, $p_date, $grp_new, $p_internal, $per->p_id, $this->jr_id));
// Check return code
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "SQL ERROR [ {$sql} ]");
}
// reverse in QUANT_FIN table
$Res = $this->db->exec_sql(" INSERT INTO quant_fin(\n qf_bank, qf_other, qf_amount,jr_id)\n SELECT qf_bank, qf_other, qf_amount*(-1),\$1\n FROM quant_fin where jr_id=\$2", array($seq, $this->jr_id));
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "SQL ERROR[ {$sql} ]");
}
// Add a "concerned operation to bound these op.together
//
$rec = new Acc_Reconciliation($this->db);
$rec->set_jr_id($seq);
$rec->insert($this->jr_id);
// Check return code
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "SQL ERROR [ {$sql} ]");
}
// the table stock must updated
// also in the stock table
$sql = "delete from stock_goods where sg_id = any ( select sg_id\n from stock_goods natural join jrnx where j_grpt=" . $this->jr_grpt_id . ")";
$Res = $this->db->exec_sql($sql);
if ($Res == false) {
throw new Exception(__FILE__ . __LINE__ . "SQL ERROR [ {$sql} ]");
}
$this->db->commit();
} catch (Exception $e) {
$this->db->rollback();
throw $e;
}
}
