dolibarr  17.0.3
lettering.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2004-2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3  * Copyright (C) 2013 Olivier Geffroy <jeff@jeffinfo.com>
4  * Copyright (C) 2013-2019 Alexandre Spangaro <aspangaro@open-dsi.fr>
5  * Copyright (C) 2018 Frédéric France <frederic.france@netlogic.fr>
6  *
7  * This program is free software; you can redistribute it and/or modify
8  * it under the terms of the GNU General Public License as published by
9  * the Free Software Foundation; either version 3 of the License, or
10  * (at your option) any later version.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program. If not, see <https://www.gnu.org/licenses/>.
19  */
20 
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";
30 
34 class Lettering extends BookKeeping
35 {
36  public static $doc_type_infos = array(
37  'customer_invoice' => array(
38  'payment_table' => 'paiement',
39  'payment_table_fk_bank' => 'fk_bank',
40  'doc_payment_table' => 'paiement_facture',
41  'doc_payment_table_fk_payment' => 'fk_paiement',
42  'doc_payment_table_fk_doc' => 'fk_facture',
43  'linked_info' => array(
44  array(
45  'table' => 'paiement_facture',
46  'fk_doc' => 'fk_facture',
47  'fk_link' => 'fk_paiement',
48  'prefix' => 'p',
49  ),
50  array(
51  'table' => 'societe_remise_except',
52  'fk_doc' => 'fk_facture_source',
53  'fk_link' => 'fk_facture',
54  'prefix' => 'a',
55  'is_fk_link_is_also_fk_doc' => true,
56  ),
57  ),
58  ),
59  'supplier_invoice' => array(
60  'payment_table' => 'paiementfourn',
61  'payment_table_fk_bank' => 'fk_bank',
62  'doc_payment_table' => 'paiementfourn_facturefourn',
63  'doc_payment_table_fk_payment' => 'fk_paiementfourn',
64  'doc_payment_table_fk_doc' => 'fk_facturefourn',
65  'linked_info' => array(
66  array(
67  'table' => 'paiementfourn_facturefourn',
68  'fk_doc' => 'fk_facturefourn',
69  'fk_link' => 'fk_paiementfourn',
70  'prefix' => 'p',
71  ),
72  array(
73  'table' => 'societe_remise_except',
74  'fk_doc' => 'fk_invoice_supplier_source',
75  'fk_link' => 'fk_invoice_supplier',
76  'prefix' => 'a',
77  'is_fk_link_is_also_fk_doc' => true,
78  ),
79  ),
80  ),
81  );
82 
89  public function letteringThirdparty($socid)
90  {
91  global $conf;
92 
93  $error = 0;
94 
95  $object = new Societe($this->db);
96  $object->id = $socid;
97  $object->fetch($socid);
98 
99 
100  if ($object->code_compta == '411CUSTCODE') {
101  $object->code_compta = '';
102  }
103 
104  if ($object->code_compta_fournisseur == '401SUPPCODE') {
105  $object->code_compta_fournisseur = '';
106  }
107 
111  $sql = "SELECT DISTINCT bk.rowid, bk.doc_date, bk.doc_type, bk.doc_ref, bk.subledger_account, ";
112  $sql .= " bk.numero_compte , bk.label_compte, bk.debit , bk.credit, bk.montant ";
113  $sql .= " , bk.sens , bk.code_journal , bk.piece_num, bk.date_lettering, bu.url_id , bu.type ";
114  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as bk";
115  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."bank_url as bu ON(bk.fk_doc = bu.fk_bank AND bu.type IN ('payment', 'payment_supplier') ) ";
116  $sql .= " WHERE ( ";
117  if ($object->code_compta != "") {
118  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta)."' ";
119  }
120  if ($object->code_compta != "" && $object->code_compta_fournisseur != "") {
121  $sql .= " OR ";
122  }
123  if ($object->code_compta_fournisseur != "") {
124  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur)."' ";
125  }
126 
127  $sql .= " ) AND (bk.date_lettering ='' OR bk.date_lettering IS NULL) ";
128  $sql .= " AND (bk.lettering_code != '' OR bk.lettering_code IS NULL) ";
129  $sql .= ' AND bk.date_validated IS NULL ';
130  $sql .= $this->db->order('bk.doc_date', 'DESC');
131 
132  // echo $sql;
133  //
134  $resql = $this->db->query($sql);
135  if ($resql) {
136  $num = $this->db->num_rows($resql);
137 
138  while ($obj = $this->db->fetch_object($resql)) {
139  $ids = array();
140  $ids_fact = array();
141 
142  if ($obj->type == 'payment_supplier') {
143  $sql = 'SELECT DISTINCT bk.rowid, facf.ref, facf.ref_supplier, payf.fk_bank, facf.rowid as fact_id';
144  $sql .= " FROM ".MAIN_DB_PREFIX."facture_fourn facf ";
145  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."paiementfourn_facturefourn as payfacf ON payfacf.fk_facturefourn=facf.rowid";
146  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."paiementfourn as payf ON payfacf.fk_paiementfourn=payf.rowid";
147  $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)."')";
148  $sql .= " WHERE payfacf.fk_paiementfourn = '".$this->db->escape($obj->url_id)."' ";
149  $sql .= " AND facf.entity = ".$conf->entity;
150  $sql .= " AND code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX."accounting_journal WHERE nature=4 AND entity=".$conf->entity.") ";
151  $sql .= " AND ( ";
152  if ($object->code_compta != "") {
153  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta)."' ";
154  }
155  if ($object->code_compta != "" && $object->code_compta_fournisseur != "") {
156  $sql .= " OR ";
157  }
158  if ($object->code_compta_fournisseur != "") {
159  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur)."' ";
160  }
161  $sql .= " ) ";
162 
163  $resql2 = $this->db->query($sql);
164  if ($resql2) {
165  while ($obj2 = $this->db->fetch_object($resql2)) {
166  $ids[$obj2->rowid] = $obj2->rowid;
167  $ids_fact[] = $obj2->fact_id;
168  }
169  $this->db->free($resql2);
170  } else {
171  $this->errors[] = $this->db->lasterror;
172  return -1;
173  }
174  if (count($ids_fact)) {
175  $sql = 'SELECT bk.rowid, facf.ref, facf.ref_supplier ';
176  $sql .= " FROM ".MAIN_DB_PREFIX."facture_fourn facf ";
177  $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))."))";
178  $sql .= " WHERE bk.code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX."accounting_journal WHERE nature=3 AND entity=".$conf->entity.") ";
179  $sql .= " AND facf.entity = ".$conf->entity;
180  $sql .= " AND ( ";
181  if ($object->code_compta != "") {
182  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta)."' ";
183  }
184  if ($object->code_compta != "" && $object->code_compta_fournisseur != "") {
185  $sql .= " OR ";
186  }
187  if ($object->code_compta_fournisseur != "") {
188  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur)."' ";
189  }
190  $sql .= ") ";
191 
192  $resql2 = $this->db->query($sql);
193  if ($resql2) {
194  while ($obj2 = $this->db->fetch_object($resql2)) {
195  $ids[$obj2->rowid] = $obj2->rowid;
196  }
197  $this->db->free($resql2);
198  } else {
199  $this->errors[] = $this->db->lasterror;
200  return -1;
201  }
202  }
203  } elseif ($obj->type == 'payment') {
204  $sql = 'SELECT DISTINCT bk.rowid, fac.ref, fac.ref, pay.fk_bank, fac.rowid as fact_id';
205  $sql .= " FROM ".MAIN_DB_PREFIX."facture fac ";
206  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."paiement_facture as payfac ON payfac.fk_facture=fac.rowid";
207  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."paiement as pay ON payfac.fk_paiement=pay.rowid";
208  $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)."')";
209  $sql .= " WHERE payfac.fk_paiement = '".$this->db->escape($obj->url_id)."' ";
210  $sql .= " AND bk.code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX."accounting_journal WHERE nature=4 AND entity=".$conf->entity.") ";
211  $sql .= " AND fac.entity IN (".getEntity('invoice', 0).")"; // We don't share object for accountancy
212  $sql .= " AND ( ";
213  if ($object->code_compta != "") {
214  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta)."' ";
215  }
216  if ($object->code_compta != "" && $object->code_compta_fournisseur != "") {
217  $sql .= " OR ";
218  }
219  if ($object->code_compta_fournisseur != "") {
220  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur)."' ";
221  }
222  $sql .= " )";
223 
224  $resql2 = $this->db->query($sql);
225  if ($resql2) {
226  while ($obj2 = $this->db->fetch_object($resql2)) {
227  $ids[$obj2->rowid] = $obj2->rowid;
228  $ids_fact[] = $obj2->fact_id;
229  }
230  } else {
231  $this->errors[] = $this->db->lasterror;
232  return -1;
233  }
234  if (count($ids_fact)) {
235  $sql = 'SELECT bk.rowid, fac.ref, fac.ref_supplier ';
236  $sql .= " FROM ".MAIN_DB_PREFIX."facture fac ";
237  $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))."))";
238  $sql .= " WHERE code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX."accounting_journal WHERE nature=2 AND entity=".$conf->entity.") ";
239  $sql .= " AND fac.entity IN (".getEntity('invoice', 0).")"; // We don't share object for accountancy
240  $sql .= " AND ( ";
241  if ($object->code_compta != "") {
242  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta)."' ";
243  }
244  if ($object->code_compta != "" && $object->code_compta_fournisseur != "") {
245  $sql .= " OR ";
246  }
247  if ($object->code_compta_fournisseur != "") {
248  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_fournisseur)."' ";
249  }
250  $sql .= " ) ";
251 
252  $resql2 = $this->db->query($sql);
253  if ($resql2) {
254  while ($obj2 = $this->db->fetch_object($resql2)) {
255  $ids[$obj2->rowid] = $obj2->rowid;
256  }
257  $this->db->free($resql2);
258  } else {
259  $this->errors[] = $this->db->lasterror;
260  return -1;
261  }
262  }
263  }
264 
265  if (count($ids) > 1) {
266  $result = $this->updateLettering($ids);
267  }
268  }
269  $this->db->free($resql);
270  }
271  if ($error) {
272  foreach ($this->errors as $errmsg) {
273  dol_syslog(__METHOD__.' '.$errmsg, LOG_ERR);
274  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
275  }
276  return -1 * $error;
277  } else {
278  return 1;
279  }
280  }
281 
288  public function updateLettering($ids = array(), $notrigger = false)
289  {
290  $error = 0;
291  $lettre = 'AAA';
292 
293  $sql = "SELECT DISTINCT ab2.lettering_code";
294  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab";
295  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab2 ON ab2.subledger_account = ab.subledger_account";
296  $sql .= " WHERE ab.rowid IN (" . $this->db->sanitize(implode(',', $ids)) . ")";
297  $sql .= " AND ab2.lettering_code != ''";
298  $sql .= " ORDER BY ab2.lettering_code DESC";
299  $sql .= " LIMIT 1 ";
300 
301  $resqla = $this->db->query($sql);
302  if ($resqla) {
303  $obj = $this->db->fetch_object($resqla);
304  $lettre = (empty($obj->lettering_code) ? $lettre : $obj->lettering_code);
305  if (!empty($obj->lettering_code)) {
306  $lettre++;
307  }
308  $this->db->free($resqla);
309  } else {
310  $this->errors[] = 'Error'.$this->db->lasterror();
311  $error++;
312  }
313 
314  $sql = "SELECT SUM(ABS(debit)) as deb, SUM(ABS(credit)) as cred FROM ".MAIN_DB_PREFIX."accounting_bookkeeping WHERE ";
315  $sql .= " rowid IN (".$this->db->sanitize(implode(',', $ids)).") AND lettering_code IS NULL AND subledger_account != ''";
316  $resqlb = $this->db->query($sql);
317  if ($resqlb) {
318  $obj = $this->db->fetch_object($resqlb);
319  if (!(round(abs($obj->deb), 2) === round(abs($obj->cred), 2))) {
320  $this->errors[] = 'Total not exacts '.round(abs($obj->deb), 2).' vs '.round(abs($obj->cred), 2);
321  $error++;
322  }
323  $this->db->free($resqlb);
324  } else {
325  $this->errors[] = 'Erreur sql'.$this->db->lasterror();
326  $error++;
327  }
328 
329  // Update request
330 
331  $now = dol_now();
332  $affected_rows = 0;
333 
334  if (!$error) {
335  $sql = "UPDATE ".MAIN_DB_PREFIX."accounting_bookkeeping SET";
336  $sql .= " lettering_code='".$this->db->escape($lettre)."'";
337  $sql .= ", date_lettering = '".$this->db->idate($now)."'"; // todo correct date it's false
338  $sql .= " WHERE rowid IN (".$this->db->sanitize(implode(',', $ids)).") AND lettering_code IS NULL AND subledger_account != ''";
339 
340  dol_syslog(get_class($this)."::update", LOG_DEBUG);
341  $resql = $this->db->query($sql);
342  if (!$resql) {
343  $error++;
344  $this->errors[] = "Error ".$this->db->lasterror();
345  } else {
346  $affected_rows = $this->db->affected_rows($resql);
347  }
348  }
349 
350  // Commit or rollback
351  if ($error) {
352  foreach ($this->errors as $errmsg) {
353  dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
354  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
355  }
356  return -1 * $error;
357  } else {
358  return $affected_rows;
359  }
360  }
361 
368  public function deleteLettering($ids, $notrigger = false)
369  {
370  $error = 0;
371 
372  $sql = "UPDATE ".MAIN_DB_PREFIX."accounting_bookkeeping SET";
373  $sql .= " lettering_code = NULL";
374  $sql .= ", date_lettering = NULL";
375  $sql .= " WHERE rowid IN (".$this->db->sanitize(implode(',', $ids)).")";
376  $sql .= " AND subledger_account != ''";
377 
378  dol_syslog(get_class($this)."::update", LOG_DEBUG);
379  $resql = $this->db->query($sql);
380  if (!$resql) {
381  $error++;
382  $this->errors[] = "Error ".$this->db->lasterror();
383  }
384 
385  // Commit or rollback
386  if ($error) {
387  foreach ($this->errors as $errmsg) {
388  dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
389  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
390  }
391  return -1 * $error;
392  } else {
393  return $this->db->affected_rows($resql);
394  }
395  }
396 
404  public function bookkeepingLetteringAll($bookkeeping_ids, $unlettering = false)
405  {
406  dol_syslog(__METHOD__ . " - ", LOG_DEBUG);
407 
408  $error = 0;
409  $errors = array();
410  $nb_lettering = 0;
411 
412  $result = $this->bookkeepingLettering($bookkeeping_ids, $unlettering);
413  if ($result < 0) {
414  $error++;
415  $errors = array_merge($errors, $this->errors);
416  $nb_lettering += abs($result) - 2;
417  } else {
418  $nb_lettering += $result;
419  }
420 
421  if ($error) {
422  $this->errors = $errors;
423  return -2 - $nb_lettering;
424  } else {
425  return $nb_lettering;
426  }
427  }
428 
436  public function bookkeepingLettering($bookkeeping_ids, $unlettering = false)
437  {
438  global $langs;
439 
440  $this->errors = array();
441 
442  // Clean parameters
443  $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
444 
445  $error = 0;
446  $nb_lettering = 0;
447  $grouped_lines = $this->getLinkedLines($bookkeeping_ids);
448  if (!is_array($grouped_lines)) {
449  return -2;
450  }
451 
452  foreach ($grouped_lines as $lines) {
453  $group_error = 0;
454  $total = 0;
455  $do_it = !$unlettering;
456  $lettering_code = null;
457  $piece_num_lines = array();
458  $bookkeeping_lines = array();
459  foreach ($lines as $line_infos) {
460  $bookkeeping_lines[$line_infos['id']] = $line_infos['id'];
461  $piece_num_lines[$line_infos['piece_num']] = $line_infos['piece_num'];
462  $total += ($line_infos['credit'] > 0 ? $line_infos['credit'] : -$line_infos['debit']);
463 
464  // Check lettering code
465  if ($unlettering) {
466  if (isset($lettering_code) && $lettering_code != $line_infos['lettering_code']) {
467  $this->errors[] = $langs->trans('AccountancyErrorMismatchLetteringCode');
468  $group_error++;
469  break;
470  }
471  if (!isset($lettering_code)) $lettering_code = (string) $line_infos['lettering_code'];
472  if (!empty($line_infos['lettering_code'])) $do_it = true;
473  } elseif (!empty($line_infos['lettering_code'])) $do_it = false;
474  }
475 
476  // Check balance amount
477  if (!$group_error && !$unlettering && price2num($total) != 0) {
478  $this->errors[] = $langs->trans('AccountancyErrorMismatchBalanceAmount', $total);
479  $group_error++;
480  }
481 
482  // Lettering/Unlettering the group of bookkeeping lines
483  if (!$group_error && $do_it) {
484  if ($unlettering) $result = $this->deleteLettering($bookkeeping_lines);
485  else $result = $this->updateLettering($bookkeeping_lines);
486  if ($result < 0) {
487  $group_error++;
488  } elseif ($result > 0) {
489  $nb_lettering++;
490  }
491  }
492 
493  if ($group_error) {
494  $this->errors[] = $langs->trans('AccountancyErrorLetteringBookkeeping', implode(', ', $piece_num_lines));
495  $error++;
496  }
497  }
498 
499  if ($error) {
500  return -2 - $nb_lettering;
501  } else {
502  return $nb_lettering;
503  }
504  }
505 
513  public function getLinkedLines($bookkeeping_ids, $only_has_subledger_account = true)
514  {
515  global $conf, $langs;
516  $this->errors = array();
517 
518  // Clean parameters
519  $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
520 
521  // Get all bookkeeping lines
522  $sql = "SELECT DISTINCT ab.doc_type, ab.fk_doc";
523  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab";
524  $sql .= " WHERE ab.entity IN (" . getEntity('accountancy') . ")";
525  $sql .= " AND ab.fk_doc > 0";
526  if (!empty($bookkeeping_ids)) {
527  // Get all bookkeeping lines of piece number
528  $sql .= " AND EXISTS (";
529  $sql .= " SELECT rowid";
530  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS pn";
531  $sql .= " WHERE pn.entity IN (" . getEntity('accountancy') . ")";
532  $sql .= " AND pn.rowid IN (" . $this->db->sanitize(implode(',', $bookkeeping_ids)) . ")";
533  $sql .= " AND pn.piece_num = ab.piece_num";
534  $sql .= " )";
535  }
536  if ($only_has_subledger_account) $sql .= " AND ab.subledger_account != ''";
537 
538  dol_syslog(__METHOD__ . " - Get all bookkeeping lines", LOG_DEBUG);
539  $resql = $this->db->query($sql);
540  if (!$resql) {
541  $this->errors[] = "Error " . $this->db->lasterror();
542  return -1;
543  }
544 
545  $bookkeeping_lines_by_type = array();
546  while ($obj = $this->db->fetch_object($resql)) {
547  $bookkeeping_lines_by_type[$obj->doc_type][$obj->fk_doc] = $obj->fk_doc;
548  }
549  $this->db->free($resql);
550 
551  if (empty($bookkeeping_lines_by_type)) {
552  return array();
553  }
554 
555  if (!empty($bookkeeping_lines_by_type['bank'])) {
556  $new_bookkeeping_lines_by_type = $this->getDocTypeAndFkDocFromBankLines($bookkeeping_lines_by_type['bank']);
557  if (!is_array($new_bookkeeping_lines_by_type)) {
558  return -1;
559  }
560  foreach ($new_bookkeeping_lines_by_type as $doc_type => $fk_docs) {
561  foreach ($fk_docs as $fk_doc) {
562  $bookkeeping_lines_by_type[$doc_type][$fk_doc] = $fk_doc;
563  }
564  }
565  }
566 
567  $grouped_lines = array();
568  foreach (self::$doc_type_infos as $doc_type => $doc_type_info) {
569  if (!is_array($bookkeeping_lines_by_type[$doc_type])) {
570  continue;
571  }
572 
573  // Get all document ids grouped
574  $doc_grouped = $this->getLinkedDocumentByGroup($bookkeeping_lines_by_type[$doc_type], $doc_type);
575  if (!is_array($doc_grouped)) {
576  return -1;
577  }
578 
579  // Group all lines by document/piece number
580  foreach ($doc_grouped as $doc_ids) {
581  $bank_ids = $this->getBankLinesFromFkDocAndDocType($doc_ids, $doc_type);
582  if (!is_array($bank_ids)) {
583  return -1;
584  }
585 
586  // Get all bookkeeping lines linked
587  $sql = "SELECT DISTINCT ab.rowid, ab.piece_num, ab.debit, ab.credit, ab.lettering_code";
588  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab";
589  $sql .= " WHERE ab.entity IN (" . getEntity('accountancy') . ")";
590  $sql .= " AND (";
591  if (!empty($bank_ids)) {
592  $sql .= " EXISTS (";
593  $sql .= " SELECT bpn.rowid";
594  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS bpn";
595  $sql .= " WHERE bpn.entity IN (" . getEntity('accountancy') . ")";
596  $sql .= " AND bpn.doc_type = 'bank'";
597  $sql .= " AND bpn.fk_doc IN (" . $this->db->sanitize(implode(',', $bank_ids)) . ")";
598  $sql .= " AND bpn.piece_num = ab.piece_num";
599  $sql .= " ) OR ";
600  }
601  $sql .= " EXISTS (";
602  $sql .= " SELECT dpn.rowid";
603  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS dpn";
604  $sql .= " WHERE dpn.entity IN (" . getEntity('accountancy') . ")";
605  $sql .= " AND dpn.doc_type = '" . $this->db->escape($doc_type) . "'";
606  $sql .= " AND dpn.fk_doc IN (" . $this->db->sanitize(implode(',', $doc_ids)) . ")";
607  $sql .= " AND dpn.piece_num = ab.piece_num";
608  $sql .= " )";
609  $sql .= ")";
610  if ($only_has_subledger_account) $sql .= " AND ab.subledger_account != ''";
611 
612  dol_syslog(__METHOD__ . " - Get all bookkeeping lines linked", LOG_DEBUG);
613  $resql = $this->db->query($sql);
614  if (!$resql) {
615  $this->errors[] = "Error " . $this->db->lasterror();
616  return -1;
617  }
618 
619  $group = array();
620  while ($obj = $this->db->fetch_object($resql)) {
621  $group[$obj->rowid] = array(
622  'id' => $obj->rowid,
623  'piece_num' => $obj->piece_num,
624  'debit' => $obj->debit,
625  'credit' => $obj->credit,
626  'lettering_code' => $obj->lettering_code,
627  );
628  }
629  $this->db->free($resql);
630 
631  if (!empty($group)) $grouped_lines[] = $group;
632  }
633  }
634 
635  return $grouped_lines;
636  }
637 
644  public function getDocTypeAndFkDocFromBankLines($bank_ids)
645  {
646  dol_syslog(__METHOD__ . " - bank_ids=".json_encode($bank_ids), LOG_DEBUG);
647 
648  // Clean parameters
649  $bank_ids = is_array($bank_ids) ? $bank_ids : array();
650 
651  if (empty($bank_ids)) {
652  return array();
653  }
654 
655  $bookkeeping_lines_by_type = array();
656  foreach (self::$doc_type_infos as $doc_type => $doc_type_info) {
657  // Get all fk_doc by doc_type from bank ids
658  $sql = "SELECT DISTINCT dp." . $doc_type_info['doc_payment_table_fk_doc'] . " AS fk_doc";
659  $sql .= " FROM " . MAIN_DB_PREFIX . $doc_type_info['payment_table'] . " AS p";
660  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . $doc_type_info['doc_payment_table'] . " AS dp ON dp." . $doc_type_info['doc_payment_table_fk_payment'] . " = p.rowid";
661  $sql .= " WHERE p." . $doc_type_info['payment_table_fk_bank'] . " IN (" . $this->db->sanitize(implode(',', $bank_ids)) . ")";
662  $sql .= " AND dp." . $doc_type_info['doc_payment_table_fk_doc'] . " > 0";
663 
664  dol_syslog(__METHOD__ . " - Get all fk_doc by doc_type from list of bank ids for '" . $doc_type . "'", LOG_DEBUG);
665  $resql = $this->db->query($sql);
666  if (!$resql) {
667  $this->errors[] = "Error " . $this->db->lasterror();
668  return -1;
669  }
670 
671  while ($obj = $this->db->fetch_object($resql)) {
672  $bookkeeping_lines_by_type[$doc_type][$obj->fk_doc] = $obj->fk_doc;
673  }
674  $this->db->free($resql);
675  }
676 
677  return $bookkeeping_lines_by_type;
678  }
679 
687  public function getBankLinesFromFkDocAndDocType($document_ids, $doc_type)
688  {
689  global $langs;
690 
691  dol_syslog(__METHOD__ . " - bank_ids=".json_encode($document_ids) . ", doc_type=$doc_type", LOG_DEBUG);
692 
693  // Clean parameters
694  $document_ids = is_array($document_ids) ? $document_ids : array();
695  //remove empty entries
696  $document_ids = array_filter($document_ids);
697 
698  $doc_type = trim($doc_type);
699 
700  if (empty($document_ids)) {
701  return array();
702  }
703  if (!is_array(self::$doc_type_infos[$doc_type])) {
704  $langs->load('errors');
705  $this->errors[] = $langs->trans('ErrorBadParameters');
706  return -1;
707  }
708 
709  $doc_type_info = self::$doc_type_infos[$doc_type];
710  $bank_ids = array();
711 
712  // Get all fk_doc by doc_type from bank ids
713  $sql = "SELECT DISTINCT p." . $doc_type_info['payment_table_fk_bank'] . " AS fk_doc";
714  $sql .= " FROM " . MAIN_DB_PREFIX . $doc_type_info['payment_table'] . " AS p";
715  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . $doc_type_info['doc_payment_table'] . " AS dp ON dp." . $doc_type_info['doc_payment_table_fk_payment'] . " = p.rowid";
716  $sql .= " WHERE dp." . $doc_type_info['doc_payment_table_fk_doc'] . " IN (" . $this->db->sanitize(implode(',', $document_ids)) . ")";
717  $sql .= " AND p." . $doc_type_info['payment_table_fk_bank'] . " > 0";
718 
719  dol_syslog(__METHOD__ . " - Get all bank ids from list of document ids of a type '" . $doc_type . "'", LOG_DEBUG);
720  $resql = $this->db->query($sql);
721  if (!$resql) {
722  $this->errors[] = "Error " . $this->db->lasterror();
723  return -1;
724  }
725 
726  while ($obj = $this->db->fetch_object($resql)) {
727  $bank_ids[$obj->fk_doc] = $obj->fk_doc;
728  }
729  $this->db->free($resql);
730 
731  return $bank_ids;
732  }
733 
741  public function getLinkedDocumentByGroup($document_ids, $doc_type)
742  {
743  global $langs;
744 
745  // Clean parameters
746  $document_ids = is_array($document_ids) ? $document_ids : array();
747  $doc_type = trim($doc_type);
748  //remove empty entries
749  $document_ids = array_filter($document_ids);
750 
751  if (empty($document_ids)) {
752  return array();
753  }
754 
755  if (!is_array(self::$doc_type_infos[$doc_type])) {
756  $langs->load('errors');
757  $this->errors[] = $langs->trans('ErrorBadParameters');
758  return -1;
759  }
760 
761  $doc_type_info = self::$doc_type_infos[$doc_type];
762 
763  // Get document lines
764  $current_document_ids = array();
765  $link_by_element = array();
766  $element_by_link = array();
767  foreach ($doc_type_info['linked_info'] as $linked_info) {
768  $sql = "SELECT DISTINCT tl2." . $linked_info['fk_link'] . " AS fk_link, tl2." . $linked_info['fk_doc'] . " AS fk_doc";
769  $sql .= " FROM " . MAIN_DB_PREFIX . $linked_info['table'] . " AS tl";
770  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . $linked_info['table'] . " AS tl2 ON tl2." . $linked_info['fk_link'] . " = tl." . $linked_info['fk_link'];
771  $sql .= " WHERE tl." . $linked_info['fk_doc'] . " IN (" . $this->db->sanitize(implode(',', $document_ids)) . ")";
772 
773  dol_syslog(__METHOD__ . " - Get document lines", LOG_DEBUG);
774  $resql = $this->db->query($sql);
775  if (!$resql) {
776  $this->errors[] = "Error " . $this->db->lasterror();
777  return -1;
778  }
779 
780  $is_fk_link_is_also_fk_doc = !empty($linked_info['is_fk_link_is_also_fk_doc']);
781  while ($obj = $this->db->fetch_object($resql)) {
782  $current_document_ids[$obj->fk_doc] = $obj->fk_doc;
783 
784  $link_key = $linked_info['prefix'] . $obj->fk_link;
785  $element_by_link[$link_key][$obj->fk_doc] = $obj->fk_doc;
786  $link_by_element[$obj->fk_doc][$link_key] = $link_key;
787  if ($is_fk_link_is_also_fk_doc) {
788  $element_by_link[$link_key][$obj->fk_link] = $obj->fk_link;
789  $link_by_element[$obj->fk_link][$link_key] = $link_key;
790  }
791  }
792  $this->db->free($resql);
793  }
794 
795  if (count(array_diff($document_ids, $current_document_ids))) {
796  return $this->getLinkedDocumentByGroup($current_document_ids, $doc_type);
797  }
798 
799  return $this->getGroupElements($link_by_element, $element_by_link);
800  }
801 
811  public function getGroupElements(&$link_by_element, &$element_by_link, $link_key = '', &$current_group = array())
812  {
813  $grouped_elements = array();
814  if (!empty($link_key) && !isset($element_by_link[$link_key])) {
815  // Return if specific link key not found
816  return $grouped_elements;
817  }
818 
819  if (empty($link_key)) {
820  // Save list when is the begin of recursive function
821  $save_link_by_element = $link_by_element;
822  $save_element_by_link = $element_by_link;
823  }
824 
825  do {
826  // Get current element id, get this payment id list and delete the entry
827  $current_link_key = !empty($link_key) ? $link_key : array_keys($element_by_link)[0];
828  $element_ids = $element_by_link[$current_link_key];
829  unset($element_by_link[$current_link_key]);
830 
831  foreach ($element_ids as $element_id) {
832  // Continue if element id in not found
833  if (!isset($link_by_element[$element_id])) continue;
834 
835  // Set the element in the current group
836  $current_group[$element_id] = $element_id;
837 
838  // Get current link keys, get this element id list and delete the entry
839  $link_keys = $link_by_element[$element_id];
840  unset($link_by_element[$element_id]);
841 
842  // Set element id on the current group for each link key of the element
843  foreach ($link_keys as $key) {
844  $this->getGroupElements($link_by_element, $element_by_link, $key, $current_group);
845  }
846  }
847 
848  if (empty($link_key)) {
849  // Save current group and reset the current group when is the begin of recursive function
850  $grouped_elements[] = $current_group;
851  $current_group = array();
852  }
853  } while (!empty($element_by_link) && empty($link_key));
854 
855  if (empty($link_key)) {
856  // Restore list when is the begin of recursive function
857  $link_by_element = $save_link_by_element;
858  $element_by_link = $save_element_by_link;
859  }
860 
861  return $grouped_elements;
862  }
863 }
Societe
Class to manage third parties objects (customers, suppliers, prospects...)
Definition: societe.class.php:49
db
$conf db
API class for accounts.
Definition: inc.php:41
Lettering\getLinkedDocumentByGroup
getLinkedDocumentByGroup($document_ids, $doc_type)
Get all linked document ids by group and type.
Definition: lettering.class.php:741
Lettering\letteringThirdparty
letteringThirdparty($socid)
letteringThirdparty
Definition: lettering.class.php:89
Lettering\bookkeepingLettering
bookkeepingLettering($bookkeeping_ids, $unlettering=false)
Lettering bookkeeping lines.
Definition: lettering.class.php:436
Lettering\getBankLinesFromFkDocAndDocType
getBankLinesFromFkDocAndDocType($document_ids, $doc_type)
Get all bank ids from list of document ids of a type.
Definition: lettering.class.php:687
Lettering\getGroupElements
getGroupElements(&$link_by_element, &$element_by_link, $link_key='', &$current_group=array())
Get element ids grouped by link or element in common.
Definition: lettering.class.php:811
Lettering\updateLettering
updateLettering($ids=array(), $notrigger=false)
Definition: lettering.class.php:288
Lettering\bookkeepingLetteringAll
bookkeepingLetteringAll($bookkeeping_ids, $unlettering=false)
Lettering bookkeeping lines all types.
Definition: lettering.class.php:404
price2num
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
Definition: functions.lib.php:5823
getEntity
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
Definition: functions.lib.php:180
$resql
if(isModEnabled('facture') &&!empty($user->rights->facture->lire)) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') &&!empty($user->rights->don->lire)) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $resql
Social contributions to pay.
Definition: index.php:745
Lettering\getDocTypeAndFkDocFromBankLines
getDocTypeAndFkDocFromBankLines($bank_ids)
Get all fk_doc by doc_type from list of bank ids.
Definition: lettering.class.php:644
Lettering\getLinkedLines
getLinkedLines($bookkeeping_ids, $only_has_subledger_account=true)
Lettering bookkeeping lines.
Definition: lettering.class.php:513
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1628
Lettering\deleteLettering
deleteLettering($ids, $notrigger=false)
Definition: lettering.class.php:368
BookKeeping
Class to manage Ledger (General Ledger and Subledger)
Definition: bookkeeping.class.php:33
Lettering
Class Lettering.
Definition: lettering.class.php:34
dol_now
dol_now($mode='auto')
Return date for now.
Definition: functions.lib.php:2951