/**
* 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;
}
}
/**
*constructor
*@param $p_init db resource
*@param $p_qcode quick_code of the jrnx.j_id
*/
function __construct($p_init, $p_qcode = null)
{
parent::__construct($p_init);
$this->quick_code = $p_qcode;
$this->object_type = 'card';
}
public function insert($p_array = null)
{
global $g_parameter;
extract($p_array);
$this->verify($p_array);
$group = $this->db->get_next_seq("s_oa_group");
/* for analytic */
$seq = $this->db->get_next_seq('s_grpt');
$this->id = $p_jrn;
$internal = $this->compute_internal_code($seq);
$this->internal = $internal;
$oPeriode = new Periode($this->db);
$check_periode = $this->check_periode();
if ($check_periode == true) {
$tperiode = $period;
} else {
$tperiode = $oPeriode->find_periode($e_date);
}
$cust = new Fiche($this->db);
$cust->get_by_qcode($e_client);
$sposte = $cust->strAttribut(ATTR_DEF_ACCOUNT);
// if 2 accounts, take only the debit one for the customer
//
if (strpos($sposte, ',') != 0) {
$array = explode(',', $sposte);
$poste = $array[0];
} else {
$poste = $sposte;
}
bcscale(4);
try {
$tot_amount = 0;
$tot_tva = 0;
$tot_debit = 0;
$this->db->start();
$tva = array();
/* Save all the items without vat */
for ($i = 0; $i < $nb_item; $i++) {
$n_both = 0;
if (strlen(trim(${'e_march' . $i})) == 0) {
continue;
}
/* First we save all the items without vat */
$fiche = new Fiche($this->db);
$fiche->get_by_qcode(${"e_march" . $i});
$amount = bcmul(${'e_march' . $i . '_price'}, ${'e_quant' . $i});
$tot_amount = round(bcadd($tot_amount, $amount), 2);
$acc_operation = new Acc_Operation($this->db);
$acc_operation->date = $e_date;
$sposte = $fiche->strAttribut(ATTR_DEF_ACCOUNT);
// if 2 accounts, take only the credit one
if (strpos($sposte, ',') != 0) {
$array = explode(',', $sposte);
$poste_val = $array[1];
} else {
$poste_val = $sposte;
}
$acc_operation->poste = $poste_val;
$acc_operation->amount = $amount;
$acc_operation->grpt = $seq;
$acc_operation->jrn = $p_jrn;
$acc_operation->type = 'c';
$acc_operation->periode = $tperiode;
if ($g_parameter->MY_UPDLAB == 'Y') {
$acc_operation->desc = strip_tags(${"e_march" . $i . "_label"});
} else {
$acc_operation->desc = null;
}
$acc_operation->qcode = ${"e_march" . $i};
if ($amount < 0) {
$tot_debit = bcadd($tot_debit, abs($amount));
}
$j_id = $acc_operation->insert_jrnx();
if ($g_parameter->MY_TVA_USE == 'Y') {
/* Compute sum vat */
$oTva = new Acc_Tva($this->db);
$idx_tva = ${'e_march' . $i . '_tva_id'};
$tva_item = ${'e_march' . $i . '_tva_amount'};
$oTva->set_parameter("id", $idx_tva);
$oTva->load();
/* if empty then we need to compute it */
if (trim($tva_item) == '' || ${'e_march' . $i . '_tva_amount'} == 0) {
/* retrieve tva */
$l = new Acc_Tva($this->db, $idx_tva);
$l->load();
$tva_item = bcmul($amount, $l->get_parameter('rate'));
}
if (isset($tva[$idx_tva])) {
$tva[$idx_tva] += $tva_item;
} else {
$tva[$idx_tva] = $tva_item;
}
if ($oTva->get_parameter("both_side") == 0) {
$tot_tva = round(bcadd($tva_item, $tot_tva), 2);
} else {
$n_both = $tva_item;
if ($n_both < 0) {
$tot_debit = bcadd($tot_debit, abs($n_both));
}
}
}
/* Save the stock */
/* if the quantity is < 0 then the stock increase (return of
* material)
*/
$nNeg = ${"e_quant" . $i} < 0 ? -1 : 1;
// always save quantity but in withStock we can find
// what card need a stock management
if ($g_parameter->MY_STOCK = 'Y' && isset($repo)) {
$dir = ${'e_quant' . $i} < 0 ? 'd' : 'c';
Stock_Goods::insert_goods($this->db, array('j_id' => $j_id, 'goods' => ${'e_march' . $i}, 'quant' => $nNeg * ${'e_quant' . $i}, 'dir' => $dir, 'repo' => $repo));
}
if ($g_parameter->MY_ANALYTIC != "nu") {
// for each item, insert into operation_analytique */
$op = new Anc_Operation($this->db);
$op->oa_group = $group;
$op->j_id = $j_id;
$op->oa_date = $e_date;
$op->oa_debit = 'f';
$op->oa_description = sql_string($e_comm);
$op->save_form_plan($_POST, $i, $j_id);
}
if ($g_parameter->MY_TVA_USE == 'Y') {
/* save into quant_sold */
$r = $this->db->exec_sql("select insert_quant_sold (\$1,\$2,\$3,\$4,\$5,\$6,\$7,\$8,\$9)", array(null, $j_id, ${'e_march' . $i}, ${'e_quant' . $i}, round($amount, 2), $tva_item, $idx_tva, $e_client, $n_both));
} else {
$r = $this->db->exec_sql("select insert_quant_sold (\$1,\$2,\$3,\$4,\$5,\$6,\$7,\$8,\$9) ", array(null, $j_id, ${'e_march' . $i}, ${'e_quant' . $i}, $amount, 0, null, $e_client, 0));
}
// if ( $g_parameter->MY_TVA_USE=='Y') {
}
// end loop : save all items
/* save total customer */
$cust_amount = bcadd($tot_amount, $tot_tva);
$acc_operation = new Acc_Operation($this->db);
$acc_operation->date = $e_date;
$acc_operation->poste = $poste;
$acc_operation->amount = $cust_amount;
$acc_operation->grpt = $seq;
$acc_operation->jrn = $p_jrn;
$acc_operation->type = 'd';
$acc_operation->periode = $tperiode;
$acc_operation->qcode = ${"e_client"};
if ($cust_amount > 0) {
$tot_debit = bcadd($tot_debit, $cust_amount);
}
$let_tiers = $acc_operation->insert_jrnx();
/** save all vat
* $i contains the tva_id and value contains the vat amount
* if if ($g_parameter->MY_TVA_USE == 'Y' )
*/
if ($g_parameter->MY_TVA_USE == 'Y') {
foreach ($tva as $i => $value) {
$oTva = new Acc_Tva($this->db);
$oTva->set_parameter('id', $i);
$oTva->load();
$poste_vat = $oTva->get_side('c');
$cust_amount = bcadd($tot_amount, $tot_tva);
$acc_operation = new Acc_Operation($this->db);
$acc_operation->date = $e_date;
$acc_operation->poste = $poste_vat;
$acc_operation->amount = $value;
$acc_operation->grpt = $seq;
$acc_operation->jrn = $p_jrn;
$acc_operation->type = 'c';
$acc_operation->periode = $tperiode;
if ($value < 0) {
$tot_debit = bcadd($tot_debit, abs($value));
}
$acc_operation->insert_jrnx();
// if TVA is on both side, we deduce it immediately
if ($oTva->get_parameter("both_side") == 1) {
$poste_vat = $oTva->get_side('d');
$cust_amount = bcadd($tot_amount, $tot_tva);
$acc_operation = new Acc_Operation($this->db);
$acc_operation->date = $e_date;
$acc_operation->poste = $poste_vat;
$acc_operation->amount = $value;
$acc_operation->grpt = $seq;
$acc_operation->jrn = $p_jrn;
$acc_operation->type = 'd';
$acc_operation->periode = $tperiode;
$acc_operation->insert_jrnx();
$tot_debit = bcadd($tot_debit, $value);
$n_both = $value;
}
}
}
// if ($g_parameter->MY_TVA_USE=='Y')
/* insert into jrn */
$acc_operation = new Acc_Operation($this->db);
$acc_operation->date = $e_date;
$acc_operation->echeance = $e_ech;
$acc_operation->amount = abs(round($tot_debit, 2));
$acc_operation->desc = $e_comm;
$acc_operation->grpt = $seq;
$acc_operation->jrn = $p_jrn;
$acc_operation->periode = $tperiode;
$acc_operation->pj = $e_pj;
$acc_operation->mt = $mt;
$this->jr_id = $acc_operation->insert_jrn();
$this->pj = $acc_operation->set_pj();
/* * = e_pj then do not increment sequence */
/* and e_pj is not null */
if (strcmp($e_pj, $e_pj_suggest) == 0 && strlen(trim($e_pj)) != 0) {
$this->inc_seq_pj();
}
$this->db->exec_sql("update jrn set jr_internal='" . $internal . "' where " . " jr_grpt_id = " . $seq);
/* update quant_sold */
$this->db->exec_sql('update quant_sold set qs_internal = $1 where j_id in (select j_id from jrnx where j_grpt=$2)', array($internal, $seq));
/* Save the attachment or generate doc */
if (isset($_FILES['pj'])) {
if (strlen(trim($_FILES['pj']['name'])) != 0) {
$this->db->save_upload_document($seq);
} else {
/* Generate an invoice and save it into the database */
if (isset($_POST['gen_invoice'])) {
$file = $this->create_document($internal, $p_array);
$this->doc = '<A class="line" HREF="show_pj.php?' . dossier::get() . '&jr_grpt_id=' . $seq . '&jrn=' . $this->id . '">' . $file . '</A>';
}
}
}
//----------------------------------------
// Save the payer
//----------------------------------------
if ($e_mp != 0) {
/* mp */
$mp = new Acc_Payment($this->db, $e_mp);
$mp->load();
/* fiche */
$fqcode = ${'e_mp_qcode_' . $e_mp};
$acfiche = new Fiche($this->db);
$acfiche->get_by_qcode($fqcode);
/* jrnx */
$acseq = $this->db->get_next_seq('s_grpt');
$acjrn = new Acc_Ledger($this->db, $mp->get_parameter('ledger_target'));
$acinternal = $acjrn->compute_internal_code($acseq);
/* Insert paid by */
$acc_pay = new Acc_Operation($this->db);
$acc_pay->date = $e_date;
/* get the account and explode if necessary */
$sposte = $acfiche->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;
}
$famount = bcsub($cust_amount, $acompte);
$acc_pay->poste = $poste_val;
$acc_pay->qcode = $fqcode;
$acc_pay->amount = abs(round($famount, 2));
$acc_pay->desc = null;
$acc_pay->grpt = $acseq;
$acc_pay->jrn = $mp->get_parameter('ledger_target');
$acc_pay->periode = $tperiode;
$acc_pay->type = $famount >= 0 ? 'd' : 'c';
$acc_pay->insert_jrnx();
/* Insert supplier */
$acc_pay = new Acc_Operation($this->db);
$acc_pay->date = $e_date;
$acc_pay->poste = $poste;
$acc_pay->qcode = $e_client;
$acc_pay->amount = abs(round($famount, 2));
$acc_pay->desc = null;
$acc_pay->grpt = $acseq;
$acc_pay->jrn = $mp->get_parameter('ledger_target');
$acc_pay->periode = $tperiode;
$acc_pay->type = $famount >= 0 ? 'c' : 'd';
$let_other = $acc_pay->insert_jrnx();
/* insert into jrn */
$acc_pay->mt = $mt;
$acjrn->grpt_id = $acseq;
$acc_pay->desc = !isset($e_comm_paiement) || strlen(trim($e_comm_paiement)) == 0 ? $e_comm : $e_comm_paiement;
$mp_jr_id = $acc_pay->insert_jrn();
$acjrn->update_internal_code($acinternal);
$r1 = $this->get_id($internal);
$r2 = $this->get_id($acinternal);
/*
* add lettering
*/
$oletter = new Lettering($this->db);
$oletter->insert_couple($let_tiers, $let_other);
/* set the flag paid */
$Res = $this->db->exec_sql("update jrn set jr_rapt='paid' where jr_id=\$1", array($r1));
/* Reconcialiation */
$rec = new Acc_Reconciliation($this->db);
$rec->set_jr_id($r1);
$rec->insert($r2);
/*
* save also into quant_fin
*/
/* get ledger property */
$ledger = new Acc_Ledger_Fin($this->db, $acc_pay->jrn);
$prop = $ledger->get_propertie();
/* if ledger is FIN then insert into quant_fin */
if ($prop['jrn_def_type'] == 'FIN') {
$ledger->insert_quant_fin($acfiche->id, $mp_jr_id, $cust->id, bcmul($famount, 1));
}
}
} catch (Exception $e) {
echo '<span class="error">' . 'Erreur dans l\'enregistrement ' . __FILE__ . ':' . __LINE__ . ' ' . $e->getMessage();
echo $e->getTrace();
$this->db->rollback();
throw new Exception($e);
}
$this->db->commit();
return $internal;
}
