27 include_once DOL_DOCUMENT_ROOT.
"/accountancy/class/bookkeeping.class.php";
28 include_once DOL_DOCUMENT_ROOT.
"/societe/class/societe.class.php";
29 include_once DOL_DOCUMENT_ROOT.
"/core/lib/date.lib.php";
39 public static $bookkeeping_cached = array();
56 $object->fetch($socid);
59 if ($object->code_compta ==
'411CUSTCODE') {
60 $object->code_compta =
'';
63 if ($object->code_compta_fournisseur ==
'401SUPPCODE') {
64 $object->code_compta_fournisseur =
'';
70 $sql =
"SELECT DISTINCT bk.rowid, bk.doc_date, bk.doc_type, bk.doc_ref, bk.subledger_account, ";
71 $sql .=
" bk.numero_compte , bk.label_compte, bk.debit , bk.credit, bk.montant ";
72 $sql .=
" , bk.sens , bk.code_journal , bk.piece_num, bk.date_lettering, bu.url_id , bu.type ";
73 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_bookkeeping as bk";
74 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"bank_url as bu ON(bk.fk_doc = bu.fk_bank AND bu.type IN ('payment', 'payment_supplier') ) ";
76 if ($object->code_compta !=
"") {
77 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta).
"' ";
79 if ($object->code_compta !=
"" && $object->code_compta_fournisseur !=
"") {
82 if ($object->code_compta_fournisseur !=
"") {
83 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur).
"' ";
86 $sql .=
" ) AND (bk.date_lettering ='' OR bk.date_lettering IS NULL) ";
87 $sql .=
" AND (bk.lettering_code != '' OR bk.lettering_code IS NULL) ";
88 $sql .=
' AND bk.date_validated IS NULL ';
89 $sql .= $this->
db->order(
'bk.doc_date',
'DESC');
97 while ($obj = $this->
db->fetch_object(
$resql)) {
101 if ($obj->type ==
'payment_supplier') {
102 $sql =
'SELECT DISTINCT bk.rowid, facf.ref, facf.ref_supplier, payf.fk_bank, facf.rowid as fact_id';
103 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture_fourn facf ";
104 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiementfourn_facturefourn as payfacf ON payfacf.fk_facturefourn=facf.rowid";
105 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiementfourn as payf ON payfacf.fk_paiementfourn=payf.rowid";
106 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_bookkeeping as bk ON (bk.fk_doc = payf.fk_bank AND bk.code_journal='".$this->
db->escape($obj->code_journal).
"')";
107 $sql .=
" WHERE payfacf.fk_paiementfourn = '".$this->db->escape($obj->url_id).
"' ";
108 $sql .=
" AND facf.entity = ".$conf->entity;
109 $sql .=
" AND code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=4 AND entity=".$conf->entity.
") ";
111 if ($object->code_compta !=
"") {
112 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta).
"' ";
114 if ($object->code_compta !=
"" && $object->code_compta_fournisseur !=
"") {
117 if ($object->code_compta_fournisseur !=
"") {
118 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur).
"' ";
122 $resql2 = $this->
db->query($sql);
124 while ($obj2 = $this->
db->fetch_object($resql2)) {
125 $ids[$obj2->rowid] = $obj2->rowid;
126 $ids_fact[] = $obj2->fact_id;
128 $this->
db->free($resql2);
130 $this->errors[] = $this->
db->lasterror;
133 if (count($ids_fact)) {
134 $sql =
'SELECT bk.rowid, facf.ref, facf.ref_supplier ';
135 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture_fourn facf ";
136 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_bookkeeping as bk ON( bk.fk_doc = facf.rowid AND facf.rowid IN (".$this->
db->sanitize(implode(
',', $ids_fact)).
"))";
137 $sql .=
" WHERE bk.code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=3 AND entity=".$conf->entity.
") ";
138 $sql .=
" AND facf.entity = ".$conf->entity;
140 if ($object->code_compta !=
"") {
141 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta).
"' ";
143 if ($object->code_compta !=
"" && $object->code_compta_fournisseur !=
"") {
146 if ($object->code_compta_fournisseur !=
"") {
147 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur).
"' ";
151 $resql2 = $this->
db->query($sql);
153 while ($obj2 = $this->
db->fetch_object($resql2)) {
154 $ids[$obj2->rowid] = $obj2->rowid;
156 $this->
db->free($resql2);
158 $this->errors[] = $this->
db->lasterror;
162 } elseif ($obj->type ==
'payment') {
163 $sql =
'SELECT DISTINCT bk.rowid, fac.ref, fac.ref, pay.fk_bank, fac.rowid as fact_id';
164 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture fac ";
165 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiement_facture as payfac ON payfac.fk_facture=fac.rowid";
166 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiement as pay ON payfac.fk_paiement=pay.rowid";
167 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_bookkeeping as bk ON (bk.fk_doc = pay.fk_bank AND bk.code_journal='".$this->
db->escape($obj->code_journal).
"')";
168 $sql .=
" WHERE payfac.fk_paiement = '".$this->db->escape($obj->url_id).
"' ";
169 $sql .=
" AND bk.code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=4 AND entity=".$conf->entity.
") ";
170 $sql .=
" AND fac.entity IN (".getEntity(
'invoice', 0).
")";
172 if ($object->code_compta !=
"") {
173 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta).
"' ";
175 if ($object->code_compta !=
"" && $object->code_compta_fournisseur !=
"") {
178 if ($object->code_compta_fournisseur !=
"") {
179 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur).
"' ";
183 $resql2 = $this->
db->query($sql);
185 while ($obj2 = $this->
db->fetch_object($resql2)) {
186 $ids[$obj2->rowid] = $obj2->rowid;
187 $ids_fact[] = $obj2->fact_id;
190 $this->errors[] = $this->
db->lasterror;
193 if (count($ids_fact)) {
194 $sql =
'SELECT bk.rowid, fac.ref, fac.ref_supplier ';
195 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture fac ";
196 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_bookkeeping as bk ON( bk.fk_doc = fac.rowid AND fac.rowid IN (".$this->
db->sanitize(implode(
',', $ids_fact)).
"))";
197 $sql .=
" WHERE code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=2 AND entity=".$conf->entity.
") ";
198 $sql .=
" AND fac.entity IN (".getEntity(
'invoice', 0).
")";
200 if ($object->code_compta !=
"") {
201 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta).
"' ";
203 if ($object->code_compta !=
"" && $object->code_compta_fournisseur !=
"") {
206 if ($object->code_compta_fournisseur !=
"") {
207 $sql .=
" bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur).
"' ";
211 $resql2 = $this->
db->query($sql);
213 while ($obj2 = $this->
db->fetch_object($resql2)) {
214 $ids[$obj2->rowid] = $obj2->rowid;
216 $this->
db->free($resql2);
218 $this->errors[] = $this->
db->lasterror;
224 if (count($ids) > 1) {
231 foreach ($this->errors as $errmsg) {
233 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
252 $sql =
"SELECT DISTINCT ab2.lettering_code";
253 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping As ab";
254 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"bank_url AS bu ON bu.fk_bank = ab.fk_doc";
255 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"bank_url AS bu2 ON bu2.url_id = bu.url_id";
256 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab2 ON ab2.fk_doc = bu2.fk_bank";
257 $sql .=
" WHERE ab.rowid IN (" . $this->
db->sanitize(implode(
',', $ids)) .
")";
258 $sql .=
" AND ab.doc_type = 'bank'";
259 $sql .=
" AND ab2.doc_type = 'bank'";
260 $sql .=
" AND bu.type = 'company'";
261 $sql .=
" AND bu2.type = 'company'";
262 $sql .=
" AND ab.subledger_account != ''";
263 $sql .=
" AND ab2.subledger_account != ''";
264 $sql .=
" AND ab.lettering_code IS NULL";
265 $sql .=
" AND ab2.lettering_code != ''";
266 $sql .=
" ORDER BY ab2.lettering_code DESC";
269 $resqla = $this->
db->query($sql);
271 $obj = $this->
db->fetch_object($resqla);
272 $lettre = (empty($obj->lettering_code) ?
'AAA' : $obj->lettering_code);
273 if (!empty($obj->lettering_code)) {
276 $this->
db->free($resqla);
278 $this->errors[] =
'Error'.$this->db->lasterror();
282 $sql =
"SELECT SUM(ABS(debit)) as deb, SUM(ABS(credit)) as cred FROM ".MAIN_DB_PREFIX.
"accounting_bookkeeping WHERE ";
283 $sql .=
" rowid IN (".$this->db->sanitize(implode(
',', $ids)).
") AND lettering_code IS NULL AND subledger_account != ''";
284 $resqlb = $this->
db->query($sql);
286 $obj = $this->
db->fetch_object($resqlb);
287 if (!(round(abs($obj->deb), 2) === round(abs($obj->cred), 2))) {
288 $this->errors[] =
'Total not exacts '.round(abs($obj->deb), 2).
' vs '.round(abs($obj->cred), 2);
291 $this->
db->free($resqlb);
293 $this->errors[] =
'Erreur sql'.$this->db->lasterror();
303 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"accounting_bookkeeping SET";
304 $sql .=
" lettering_code='".$this->db->escape($lettre).
"'";
305 $sql .=
" , date_lettering = '".$this->db->idate($now).
"'";
306 $sql .=
" WHERE rowid IN (".$this->db->sanitize(implode(
',', $ids)).
") AND lettering_code IS NULL AND subledger_account != ''";
308 dol_syslog(get_class($this).
"::update", LOG_DEBUG);
312 $this->errors[] =
"Error ".$this->db->lasterror();
314 $affected_rows = $this->
db->affected_rows(
$resql);
320 foreach ($this->errors as $errmsg) {
321 dol_syslog(get_class($this).
"::update ".$errmsg, LOG_ERR);
322 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
326 return $affected_rows;
340 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"accounting_bookkeeping SET";
341 $sql .=
" lettering_code = NULL";
342 $sql .=
" , date_lettering = NULL";
343 $sql .=
" WHERE rowid IN (".$this->db->sanitize(implode(
',', $ids)).
")";
344 $sql .=
" AND subledger_account != ''";
346 dol_syslog(get_class($this).
"::update", LOG_DEBUG);
350 $this->errors[] =
"Error ".$this->db->lasterror();
355 foreach ($this->errors as $errmsg) {
356 dol_syslog(get_class($this).
"::update ".$errmsg, LOG_ERR);
357 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
361 return $this->
db->affected_rows(
$resql);
383 $errors = array_merge($errors, $this->errors);
384 $nb_lettering += abs($result) - 2;
386 $nb_lettering += $result;
392 $errors = array_merge($errors, $this->errors);
393 $nb_lettering += abs($result) - 2;
395 $nb_lettering += $result;
399 $this->errors = $errors;
400 return -2 - $nb_lettering;
402 return $nb_lettering;
418 $this->errors = array();
421 $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
427 foreach ($grouped_lines as $lines) {
430 $do_it = !$unlettering;
431 $lettering_code =
null;
432 $piece_num_lines = array();
433 $bookkeeping_lines = array();
434 foreach ($lines as $line_infos) {
435 $bookkeeping_lines[$line_infos[
'id']] = $line_infos[
'id'];
436 $piece_num_lines[$line_infos[
'piece_num']] = $line_infos[
'piece_num'];
437 $total += ($line_infos[
'credit'] > 0 ? $line_infos[
'credit'] : -$line_infos[
'debit']);
441 if (isset($lettering_code) && $lettering_code != $line_infos[
'lettering_code']) {
442 $this->errors[] = $langs->trans(
'AccountancyErrorMismatchLetteringCode');
446 if (!isset($lettering_code)) $lettering_code = (
string) $line_infos[
'lettering_code'];
447 if (!empty($line_infos[
'lettering_code'])) $do_it =
true;
448 } elseif (!empty($line_infos[
'lettering_code'])) $do_it =
false;
452 if (!$group_error && !$unlettering &&
price2num($total) != 0) {
453 $this->errors[] = $langs->trans(
'AccountancyErrorMismatchBalanceAmount', $total);
458 if (!$group_error && $do_it) {
459 if ($unlettering) $result = $this->
deleteLettering($bookkeeping_lines);
463 } elseif ($result > 0) {
469 $this->errors[] = $langs->trans(
'AccountancyErrorLetteringBookkeeping', implode(
', ', $piece_num_lines));
475 return -2 - $nb_lettering;
477 return $nb_lettering;
490 global $conf, $langs;
491 $this->errors = array();
494 $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
497 if ($type ==
'customer_invoice') {
498 $doc_type =
'customer_invoice';
499 $bank_url_type =
'payment';
500 $payment_element =
'paiement_facture';
501 $fk_payment_element =
'fk_paiement';
502 $fk_element =
'fk_facture';
503 $account_number = $conf->global->ACCOUNTING_ACCOUNT_CUSTOMER;
504 } elseif ($type ==
'supplier_invoice') {
505 $doc_type =
'supplier_invoice';
506 $bank_url_type =
'payment_supplier';
507 $payment_element =
'paiementfourn_facturefourn';
508 $fk_payment_element =
'fk_paiementfourn';
509 $fk_element =
'fk_facturefourn';
510 $account_number = $conf->global->ACCOUNTING_ACCOUNT_SUPPLIER;
512 $langs->load(
'errors');
513 $this->errors[] = $langs->trans(
'ErrorBadParameters');
517 $payment_ids = array();
520 $sql =
"SELECT DISTINCT bu.url_id AS payment_id";
521 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab";
522 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"bank_url AS bu ON bu.fk_bank = ab.fk_doc";
523 $sql .=
" WHERE ab.doc_type = 'bank'";
526 $sql .=
" AND bu.type = '" . $this->
db->escape($bank_url_type) .
"'";
527 if (!empty($bookkeeping_ids)) $sql .=
" AND ab.rowid IN (" . $this->
db->sanitize(implode(
',', $bookkeeping_ids)) .
")";
529 dol_syslog(__METHOD__ .
" - Get all payment id from bank lines", LOG_DEBUG);
532 $this->errors[] =
"Error " . $this->
db->lasterror();
536 while ($obj = $this->
db->fetch_object(
$resql)) {
537 $payment_ids[$obj->payment_id] = $obj->payment_id;
542 $sql =
"SELECT DISTINCT pe.$fk_payment_element AS payment_id";
543 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab";
544 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"$payment_element AS pe ON pe.$fk_element = ab.fk_doc";
545 $sql .=
" WHERE ab.doc_type = '" . $this->
db->escape($doc_type) .
"'";
548 $sql .=
" AND pe.$fk_payment_element IS NOT NULL";
549 if (!empty($bookkeeping_ids)) $sql .=
" AND ab.rowid IN (" . $this->
db->sanitize(implode(
',', $bookkeeping_ids)) .
")";
551 dol_syslog(__METHOD__ .
" - Get all payment id from bank lines", LOG_DEBUG);
554 $this->errors[] =
"Error " . $this->
db->lasterror();
558 while ($obj = $this->
db->fetch_object(
$resql)) {
559 $payment_ids[$obj->payment_id] = $obj->payment_id;
563 if (empty($payment_ids)) {
571 foreach ($payment_by_group as $payment_list) {
575 $sql =
"SELECT DISTINCT ab.rowid, ab.piece_num, ab.lettering_code, ab.debit, ab.credit";
576 $sql .=
" FROM " . MAIN_DB_PREFIX .
"bank_url AS bu";
577 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab ON ab.fk_doc = bu.fk_bank";
578 $sql .=
" WHERE bu.url_id IN (" . $this->
db->sanitize(implode(
',', $payment_list)) .
")";
579 $sql .=
" AND bu.type = '" . $this->
db->escape($bank_url_type) .
"'";
580 $sql .=
" AND ab.doc_type = 'bank'";
581 $sql .=
" AND ab.subledger_account != ''";
582 $sql .=
" AND ab.numero_compte = '" . $this->
db->escape($account_number) .
"'";
584 dol_syslog(__METHOD__ .
" - Get bank lines", LOG_DEBUG);
587 $this->errors[] =
"Error " . $this->
db->lasterror();
591 while ($obj = $this->
db->fetch_object(
$resql)) {
592 $lines[$obj->rowid] = array(
'id' => $obj->rowid,
'piece_num' => $obj->piece_num,
'lettering_code' => $obj->lettering_code,
'debit' => $obj->debit,
'credit' => $obj->credit);
597 $sql =
"SELECT DISTINCT ab.rowid, ab.piece_num, ab.lettering_code, ab.debit, ab.credit";
598 $sql .=
" FROM " . MAIN_DB_PREFIX .
"$payment_element AS pe";
599 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab ON ab.fk_doc = pe.$fk_element";
600 $sql .=
" WHERE pe.$fk_payment_element IN (" . $this->
db->sanitize(implode(
',', $payment_list)) .
")";
601 $sql .=
" AND ab.doc_type = '" . $this->
db->escape($doc_type) .
"'";
602 $sql .=
" AND ab.subledger_account != ''";
603 $sql .=
" AND ab.numero_compte = '" . $this->
db->escape($account_number) .
"'";
605 dol_syslog(__METHOD__ .
" - Get payment lines", LOG_DEBUG);
608 $this->errors[] =
"Error " . $this->
db->lasterror();
612 while ($obj = $this->
db->fetch_object(
$resql)) {
613 $lines[$obj->rowid] = array(
'id' => $obj->rowid,
'piece_num' => $obj->piece_num,
'lettering_code' => $obj->lettering_code,
'debit' => $obj->debit,
'credit' => $obj->credit);
617 if (!empty($lines)) {
637 $payment_ids = is_array($payment_ids) ? $payment_ids : array();
640 if (empty($payment_ids)) {
644 if ($type ==
'customer_invoice') {
645 $payment_element =
'paiement_facture';
646 $fk_payment_element =
'fk_paiement';
647 $fk_element =
'fk_facture';
648 } elseif ($type ==
'supplier_invoice') {
649 $payment_element =
'paiementfourn_facturefourn';
650 $fk_payment_element =
'fk_paiementfourn';
651 $fk_element =
'fk_facturefourn';
653 $langs->load(
'errors');
654 $this->errors[] = $langs->trans(
'ErrorBadParameters');
659 $sql =
"SELECT DISTINCT pe2.$fk_payment_element, pe2.$fk_element";
660 $sql .=
" FROM " . MAIN_DB_PREFIX .
"$payment_element AS pe";
661 $sql .=
" INNER JOIN " . MAIN_DB_PREFIX .
"$payment_element AS pe2 ON pe2.$fk_element = pe.$fk_element";
662 $sql .=
" WHERE pe.$fk_payment_element IN (" . $this->
db->sanitize(implode(
',', $payment_ids)) .
")";
664 dol_syslog(__METHOD__ .
" - Get payment lines", LOG_DEBUG);
667 $this->errors[] =
"Error " . $this->
db->lasterror();
671 $current_payment_ids = array();
672 $payment_by_element = array();
673 $element_by_payment = array();
674 while ($obj = $this->
db->fetch_object(
$resql)) {
675 $current_payment_ids[$obj->$fk_payment_element] = $obj->$fk_payment_element;
676 $element_by_payment[$obj->$fk_payment_element][$obj->$fk_element] = $obj->$fk_element;
677 $payment_by_element[$obj->$fk_element][$obj->$fk_payment_element] = $obj->$fk_payment_element;
681 if (count(array_diff($payment_ids, $current_payment_ids))) {
697 public function getGroupElements(&$payment_by_element, &$element_by_payment, $element_id = 0, &$current_group = array())
699 $grouped_payments = array();
700 if ($element_id > 0 && !isset($payment_by_element[$element_id])) {
702 return $grouped_payments;
705 $save_payment_by_element =
null;
706 $save_element_by_payment =
null;
707 if ($element_id == 0) {
709 $save_payment_by_element = $payment_by_element;
710 $save_element_by_payment = $element_by_payment;
715 $current_element_id = $element_id > 0 ? $element_id : array_keys($payment_by_element)[0];
716 $payment_ids = $payment_by_element[$current_element_id];
717 unset($payment_by_element[$current_element_id]);
719 foreach ($payment_ids as $payment_id) {
721 if (!isset($element_by_payment[$payment_id]))
continue;
724 $current_group[$payment_id] = $payment_id;
727 $element_ids = $element_by_payment[$payment_id];
728 unset($element_by_payment[$payment_id]);
731 foreach ($element_ids as $id) {
732 $this->
getGroupElements($payment_by_element, $element_by_payment, $id, $current_group);
736 if ($element_id == 0) {
738 $grouped_payments[] = $current_group;
739 $current_group = array();
741 }
while (!empty($payment_by_element) && $element_id == 0);
743 if ($element_id == 0) {
745 $payment_by_element = $save_payment_by_element;
746 $element_by_payment = $save_element_by_payment;
749 return $grouped_payments;