/** * 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; } }