dolibarr  20.0.0-alpha
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-2023 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_client == '411CUSTCODE') {
101  $object->code_compta_client = '';
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_client != "") {
118  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_client)."' ";
119  }
120  if ($object->code_compta_client != "" && $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_client != "") {
153  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_client)."' ";
154  }
155  if ($object->code_compta_client != "" && $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_client != "") {
182  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_client)."' ";
183  }
184  if ($object->code_compta_client != "" && $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_client != "") {
214  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_client)."' ";
215  }
216  if ($object->code_compta_client != "" && $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_client != "") {
242  $sql .= " bk.subledger_account = '".$this->db->escape($object->code_compta_client)."' ";
243  }
244  if ($object->code_compta_client != "" && $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 = 0)
289  {
290  $error = 0;
291 
292  // Generate a string with n char A where n is ACCOUNTING_LETTERING_NBLETTERS (So 'AA', 'AAA', ...) @phan-suppress-next-line PhanParamSuspiciousOrder
293  $lettre = str_pad("", getDolGlobalInt('ACCOUNTING_LETTERING_NBLETTERS', 3), "A");
294 
295  $sql = "SELECT DISTINCT ab2.lettering_code";
296  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab";
297  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab2 ON ab2.subledger_account = ab.subledger_account";
298  $sql .= " WHERE ab.rowid IN (" . $this->db->sanitize(implode(',', $ids)) . ")";
299  $sql .= " AND ab2.lettering_code != ''";
300  $sql .= " ORDER BY ab2.lettering_code DESC";
301  $sql .= " LIMIT 1 ";
302 
303  $resqla = $this->db->query($sql);
304  if ($resqla) {
305  $obj = $this->db->fetch_object($resqla);
306  $lettre = (empty($obj->lettering_code) ? $lettre : $obj->lettering_code);
307  if (!empty($obj->lettering_code)) {
308  $lettre++;
309  }
310  $this->db->free($resqla);
311  } else {
312  $this->errors[] = 'Error'.$this->db->lasterror();
313  $error++;
314  }
315 
316  $sql = "SELECT SUM(ABS(debit)) as deb, SUM(ABS(credit)) as cred FROM ".MAIN_DB_PREFIX."accounting_bookkeeping WHERE ";
317  $sql .= " rowid IN (".$this->db->sanitize(implode(',', $ids)).") AND lettering_code IS NULL AND subledger_account != ''";
318  $resqlb = $this->db->query($sql);
319  if ($resqlb) {
320  $obj = $this->db->fetch_object($resqlb);
321  if (!(round(abs($obj->deb), 2) === round(abs($obj->cred), 2))) {
322  $this->errors[] = 'Total not exacts '.round(abs($obj->deb), 2).' vs '.round(abs($obj->cred), 2);
323  $error++;
324  }
325  $this->db->free($resqlb);
326  } else {
327  $this->errors[] = 'Erreur sql'.$this->db->lasterror();
328  $error++;
329  }
330 
331  // Update request
332  $now = dol_now();
333  $affected_rows = 0;
334 
335  if (!$error) {
336  $sql = "UPDATE ".MAIN_DB_PREFIX."accounting_bookkeeping SET";
337  $sql .= " lettering_code='".$this->db->escape($lettre)."'";
338  $sql .= ", date_lettering = '".$this->db->idate($now)."'"; // todo correct date it's false
339  $sql .= " WHERE rowid IN (".$this->db->sanitize(implode(',', $ids)).") AND lettering_code IS NULL AND subledger_account != ''";
340 
341  dol_syslog(get_class($this)."::update", LOG_DEBUG);
342  $resql = $this->db->query($sql);
343  if (!$resql) {
344  $error++;
345  $this->errors[] = "Error ".$this->db->lasterror();
346  } else {
347  $affected_rows = $this->db->affected_rows($resql);
348  }
349  }
350 
351  // Commit or rollback
352  if ($error) {
353  foreach ($this->errors as $errmsg) {
354  dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
355  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
356  }
357  return -1 * $error;
358  } else {
359  return $affected_rows;
360  }
361  }
362 
369  public function deleteLettering($ids, $notrigger = 0)
370  {
371  $error = 0;
372 
373  $sql = "UPDATE ".MAIN_DB_PREFIX."accounting_bookkeeping SET";
374  $sql .= " lettering_code = NULL";
375  $sql .= ", date_lettering = NULL";
376  $sql .= " WHERE rowid IN (".$this->db->sanitize(implode(',', $ids)).")";
377  $sql .= " AND subledger_account != ''";
378 
379  dol_syslog(get_class($this)."::update", LOG_DEBUG);
380  $resql = $this->db->query($sql);
381  if (!$resql) {
382  $error++;
383  $this->errors[] = "Error ".$this->db->lasterror();
384  }
385 
386  // Commit or rollback
387  if ($error) {
388  foreach ($this->errors as $errmsg) {
389  dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
390  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
391  }
392  return -1 * $error;
393  } else {
394  return $this->db->affected_rows($resql);
395  }
396  }
397 
405  public function bookkeepingLetteringAll($bookkeeping_ids, $unlettering = false)
406  {
407  dol_syslog(__METHOD__ . " - ", LOG_DEBUG);
408 
409  $error = 0;
410  $errors = array();
411  $nb_lettering = 0;
412 
413  $result = $this->bookkeepingLettering($bookkeeping_ids, $unlettering);
414  if ($result < 0) {
415  $error++;
416  $errors = array_merge($errors, $this->errors);
417  $nb_lettering += abs($result) - 2;
418  } else {
419  $nb_lettering += $result;
420  }
421 
422  if ($error) {
423  $this->errors = $errors;
424  return -2 - $nb_lettering;
425  } else {
426  return $nb_lettering;
427  }
428  }
429 
437  public function bookkeepingLettering($bookkeeping_ids, $unlettering = false)
438  {
439  global $langs;
440 
441  $this->errors = array();
442 
443  // Clean parameters
444  $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
445 
446  $error = 0;
447  $nb_lettering = 0;
448  $grouped_lines = $this->getLinkedLines($bookkeeping_ids);
449  if (!is_array($grouped_lines)) {
450  return -2;
451  }
452 
453  foreach ($grouped_lines as $lines) {
454  $group_error = 0;
455  $total = 0;
456  $do_it = !$unlettering;
457  $lettering_code = null;
458  $piece_num_lines = array();
459  $bookkeeping_lines = array();
460  foreach ($lines as $line_infos) {
461  $bookkeeping_lines[$line_infos['id']] = $line_infos['id'];
462  $piece_num_lines[$line_infos['piece_num']] = $line_infos['piece_num'];
463  $total += ($line_infos['credit'] > 0 ? $line_infos['credit'] : -$line_infos['debit']);
464 
465  // Check lettering code
466  if ($unlettering) {
467  if (isset($lettering_code) && $lettering_code != $line_infos['lettering_code']) {
468  $this->errors[] = $langs->trans('AccountancyErrorMismatchLetteringCode');
469  $group_error++;
470  break;
471  }
472  if (!isset($lettering_code)) {
473  $lettering_code = (string) $line_infos['lettering_code'];
474  }
475  if (!empty($line_infos['lettering_code'])) {
476  $do_it = true;
477  }
478  } elseif (!empty($line_infos['lettering_code'])) {
479  $do_it = false;
480  }
481  }
482 
483  // Check balance amount
484  if (!$group_error && !$unlettering && price2num($total) != 0) {
485  $this->errors[] = $langs->trans('AccountancyErrorMismatchBalanceAmount', $total);
486  $group_error++;
487  }
488 
489  // Lettering/Unlettering the group of bookkeeping lines
490  if (!$group_error && $do_it) {
491  if ($unlettering) {
492  $result = $this->deleteLettering($bookkeeping_lines);
493  } else {
494  $result = $this->updateLettering($bookkeeping_lines);
495  }
496  if ($result < 0) {
497  $group_error++;
498  } elseif ($result > 0) {
499  $nb_lettering++;
500  }
501  }
502 
503  if ($group_error) {
504  $this->errors[] = $langs->trans('AccountancyErrorLetteringBookkeeping', implode(', ', $piece_num_lines));
505  $error++;
506  }
507  }
508 
509  if ($error) {
510  return -2 - $nb_lettering;
511  } else {
512  return $nb_lettering;
513  }
514  }
515 
523  public function getLinkedLines($bookkeeping_ids, $only_has_subledger_account = true)
524  {
525  global $conf, $langs;
526  $this->errors = array();
527 
528  // Clean parameters
529  $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
530 
531  // Get all bookkeeping lines
532  $sql = "SELECT DISTINCT ab.doc_type, ab.fk_doc";
533  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab";
534  $sql .= " WHERE ab.entity IN (" . getEntity('accountancy') . ")";
535  $sql .= " AND ab.fk_doc > 0";
536  if (!empty($bookkeeping_ids)) {
537  // Get all bookkeeping lines of piece number
538  $sql .= " AND EXISTS (";
539  $sql .= " SELECT rowid";
540  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS pn";
541  $sql .= " WHERE pn.entity IN (" . getEntity('accountancy') . ")";
542  $sql .= " AND pn.rowid IN (" . $this->db->sanitize(implode(',', $bookkeeping_ids)) . ")";
543  $sql .= " AND pn.piece_num = ab.piece_num";
544  $sql .= " )";
545  }
546  if ($only_has_subledger_account) {
547  $sql .= " AND ab.subledger_account != ''";
548  }
549 
550  dol_syslog(__METHOD__ . " - Get all bookkeeping lines", LOG_DEBUG);
551  $resql = $this->db->query($sql);
552  if (!$resql) {
553  $this->errors[] = "Error " . $this->db->lasterror();
554  return -1;
555  }
556 
557  $bookkeeping_lines_by_type = array();
558  while ($obj = $this->db->fetch_object($resql)) {
559  $bookkeeping_lines_by_type[$obj->doc_type][$obj->fk_doc] = $obj->fk_doc;
560  }
561  $this->db->free($resql);
562 
563  if (empty($bookkeeping_lines_by_type)) {
564  return array();
565  }
566 
567  if (!empty($bookkeeping_lines_by_type['bank'])) {
568  $new_bookkeeping_lines_by_type = $this->getDocTypeAndFkDocFromBankLines($bookkeeping_lines_by_type['bank']);
569  if (!is_array($new_bookkeeping_lines_by_type)) {
570  return -1;
571  }
572  foreach ($new_bookkeeping_lines_by_type as $doc_type => $fk_docs) {
573  foreach ($fk_docs as $fk_doc) {
574  $bookkeeping_lines_by_type[$doc_type][$fk_doc] = $fk_doc;
575  }
576  }
577  }
578 
579  $grouped_lines = array();
580  foreach (self::$doc_type_infos as $doc_type => $doc_type_info) {
581  if (!is_array($bookkeeping_lines_by_type[$doc_type])) {
582  continue;
583  }
584 
585  // Get all document ids grouped
586  $doc_grouped = $this->getLinkedDocumentByGroup($bookkeeping_lines_by_type[$doc_type], $doc_type);
587  if (!is_array($doc_grouped)) {
588  return -1;
589  }
590 
591  // Group all lines by document/piece number
592  foreach ($doc_grouped as $doc_ids) {
593  $bank_ids = $this->getBankLinesFromFkDocAndDocType($doc_ids, $doc_type);
594  if (!is_array($bank_ids)) {
595  return -1;
596  }
597 
598  // Get all bookkeeping lines linked
599  $sql = "SELECT DISTINCT ab.rowid, ab.piece_num, ab.debit, ab.credit, ab.lettering_code";
600  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS ab";
601  $sql .= " WHERE ab.entity IN (" . getEntity('accountancy') . ")";
602  $sql .= " AND (";
603  if (!empty($bank_ids)) {
604  $sql .= " EXISTS (";
605  $sql .= " SELECT bpn.rowid";
606  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS bpn";
607  $sql .= " WHERE bpn.entity IN (" . getEntity('accountancy') . ")";
608  $sql .= " AND bpn.doc_type = 'bank'";
609  $sql .= " AND bpn.fk_doc IN (" . $this->db->sanitize(implode(',', $bank_ids)) . ")";
610  $sql .= " AND bpn.piece_num = ab.piece_num";
611  $sql .= " ) OR ";
612  }
613  $sql .= " EXISTS (";
614  $sql .= " SELECT dpn.rowid";
615  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping AS dpn";
616  $sql .= " WHERE dpn.entity IN (" . getEntity('accountancy') . ")";
617  $sql .= " AND dpn.doc_type = '" . $this->db->escape($doc_type) . "'";
618  $sql .= " AND dpn.fk_doc IN (" . $this->db->sanitize(implode(',', $doc_ids)) . ")";
619  $sql .= " AND dpn.piece_num = ab.piece_num";
620  $sql .= " )";
621  $sql .= ")";
622  if ($only_has_subledger_account) {
623  $sql .= " AND ab.subledger_account != ''";
624  }
625 
626  dol_syslog(__METHOD__ . " - Get all bookkeeping lines linked", LOG_DEBUG);
627  $resql = $this->db->query($sql);
628  if (!$resql) {
629  $this->errors[] = "Error " . $this->db->lasterror();
630  return -1;
631  }
632 
633  $group = array();
634  while ($obj = $this->db->fetch_object($resql)) {
635  $group[$obj->rowid] = array(
636  'id' => $obj->rowid,
637  'piece_num' => $obj->piece_num,
638  'debit' => $obj->debit,
639  'credit' => $obj->credit,
640  'lettering_code' => $obj->lettering_code,
641  );
642  }
643  $this->db->free($resql);
644 
645  if (!empty($group)) {
646  $grouped_lines[] = $group;
647  }
648  }
649  }
650 
651  return $grouped_lines;
652  }
653 
660  public function getDocTypeAndFkDocFromBankLines($bank_ids)
661  {
662  dol_syslog(__METHOD__ . " - bank_ids=".json_encode($bank_ids), LOG_DEBUG);
663 
664  // Clean parameters
665  $bank_ids = is_array($bank_ids) ? $bank_ids : array();
666 
667  if (empty($bank_ids)) {
668  return array();
669  }
670 
671  $bookkeeping_lines_by_type = array();
672  foreach (self::$doc_type_infos as $doc_type => $doc_type_info) {
673  // Get all fk_doc by doc_type from bank ids
674  $sql = "SELECT DISTINCT dp." . $this->db->sanitize($doc_type_info['doc_payment_table_fk_doc']) . " AS fk_doc";
675  $sql .= " FROM " . MAIN_DB_PREFIX . $this->db->sanitize($doc_type_info['payment_table']) . " AS p";
676  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . $this->db->sanitize($doc_type_info['doc_payment_table']) . " AS dp ON dp." . $this->db->sanitize($doc_type_info['doc_payment_table_fk_payment']) . " = p.rowid";
677  $sql .= " WHERE p." . $this->db->sanitize($doc_type_info['payment_table_fk_bank']) . " IN (" . $this->db->sanitize(implode(',', $bank_ids)) . ")";
678  $sql .= " AND dp." . $this->db->sanitize($doc_type_info['doc_payment_table_fk_doc']) . " > 0";
679 
680  dol_syslog(__METHOD__ . " - Get all fk_doc by doc_type from list of bank ids for '" . $doc_type . "'", LOG_DEBUG);
681  $resql = $this->db->query($sql);
682  if (!$resql) {
683  $this->errors[] = "Error " . $this->db->lasterror();
684  return -1;
685  }
686 
687  while ($obj = $this->db->fetch_object($resql)) {
688  $bookkeeping_lines_by_type[$doc_type][$obj->fk_doc] = $obj->fk_doc;
689  }
690  $this->db->free($resql);
691  }
692 
693  return $bookkeeping_lines_by_type;
694  }
695 
703  public function getBankLinesFromFkDocAndDocType($document_ids, $doc_type)
704  {
705  global $langs;
706 
707  dol_syslog(__METHOD__ . " - bank_ids=".json_encode($document_ids) . ", doc_type=$doc_type", LOG_DEBUG);
708 
709  // Clean parameters
710  $document_ids = is_array($document_ids) ? $document_ids : array();
711  //remove empty entries
712  $document_ids = array_filter($document_ids);
713 
714  $doc_type = trim($doc_type);
715 
716  if (empty($document_ids)) {
717  return array();
718  }
719  if (!is_array(self::$doc_type_infos[$doc_type])) {
720  $langs->load('errors');
721  $this->errors[] = $langs->trans('ErrorBadParameters');
722  return -1;
723  }
724 
725  $doc_type_info = self::$doc_type_infos[$doc_type];
726  $bank_ids = array();
727 
728  // Get all fk_doc by doc_type from bank ids
729  $sql = "SELECT DISTINCT p." . $this->db->sanitize($doc_type_info['payment_table_fk_bank']) . " AS fk_doc";
730  $sql .= " FROM " . MAIN_DB_PREFIX . $this->db->sanitize($doc_type_info['payment_table']) . " AS p";
731  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . $this->db->sanitize($doc_type_info['doc_payment_table']) . " AS dp ON dp." . $this->db->sanitize($doc_type_info['doc_payment_table_fk_payment']) . " = p.rowid";
732  $sql .= " WHERE dp." . $this->db->sanitize($doc_type_info['doc_payment_table_fk_doc']) . " IN (" . $this->db->sanitize(implode(',', $document_ids)) . ")";
733  $sql .= " AND p." . $this->db->sanitize($doc_type_info['payment_table_fk_bank']) . " > 0";
734 
735  dol_syslog(__METHOD__ . " - Get all bank ids from list of document ids of a type '" . $doc_type . "'", LOG_DEBUG);
736  $resql = $this->db->query($sql);
737  if (!$resql) {
738  $this->errors[] = "Error " . $this->db->lasterror();
739  return -1;
740  }
741 
742  while ($obj = $this->db->fetch_object($resql)) {
743  $bank_ids[$obj->fk_doc] = $obj->fk_doc;
744  }
745  $this->db->free($resql);
746 
747  return $bank_ids;
748  }
749 
757  public function getLinkedDocumentByGroup($document_ids, $doc_type)
758  {
759  global $langs;
760 
761  // Clean parameters
762  $document_ids = is_array($document_ids) ? $document_ids : array();
763  $doc_type = trim($doc_type);
764  //remove empty entries
765  $document_ids = array_filter($document_ids);
766 
767  if (empty($document_ids)) {
768  return array();
769  }
770 
771  if (!is_array(self::$doc_type_infos[$doc_type])) {
772  $langs->load('errors');
773  $this->errors[] = $langs->trans('ErrorBadParameters');
774  return -1;
775  }
776 
777  $doc_type_info = self::$doc_type_infos[$doc_type];
778 
779  // Get document lines
780  $current_document_ids = array();
781  $link_by_element = array();
782  $element_by_link = array();
783  foreach ($doc_type_info['linked_info'] as $linked_info) {
784  $sql = "SELECT DISTINCT tl2." . $this->db->sanitize($linked_info['fk_link']) . " AS fk_link, tl2." . $this->db->sanitize($linked_info['fk_doc']) . " AS fk_doc";
785  $sql .= " FROM " . MAIN_DB_PREFIX . $this->db->sanitize($linked_info['table']) . " AS tl";
786  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . $this->db->sanitize($linked_info['table']) . " AS tl2 ON tl2." . $this->db->sanitize($linked_info['fk_link']) . " = tl." . $this->db->sanitize($linked_info['fk_link']);
787  $sql .= " WHERE tl." . $this->db->sanitize($linked_info['fk_doc']) . " IN (" . $this->db->sanitize(implode(',', $document_ids)) . ")";
788 
789  dol_syslog(__METHOD__ . " - Get document lines", LOG_DEBUG);
790  $resql = $this->db->query($sql);
791  if (!$resql) {
792  $this->errors[] = "Error " . $this->db->lasterror();
793  return -1;
794  }
795 
796  $is_fk_link_is_also_fk_doc = !empty($linked_info['is_fk_link_is_also_fk_doc']);
797  while ($obj = $this->db->fetch_object($resql)) {
798  $current_document_ids[$obj->fk_doc] = $obj->fk_doc;
799 
800  $link_key = $linked_info['prefix'] . $obj->fk_link;
801  $element_by_link[$link_key][$obj->fk_doc] = $obj->fk_doc;
802  $link_by_element[$obj->fk_doc][$link_key] = $link_key;
803  if ($is_fk_link_is_also_fk_doc) {
804  $element_by_link[$link_key][$obj->fk_link] = $obj->fk_link;
805  $link_by_element[$obj->fk_link][$link_key] = $link_key;
806  }
807  }
808  $this->db->free($resql);
809  }
810 
811  if (count(array_diff($document_ids, $current_document_ids))) {
812  return $this->getLinkedDocumentByGroup($current_document_ids, $doc_type);
813  }
814 
815  return $this->getGroupElements($link_by_element, $element_by_link);
816  }
817 
827  public function getGroupElements(&$link_by_element, &$element_by_link, $link_key = '', &$current_group = array())
828  {
829  $grouped_elements = array();
830  if (!empty($link_key) && !isset($element_by_link[$link_key])) {
831  // Return if specific link key not found
832  return $grouped_elements;
833  }
834 
835  if (empty($link_key)) {
836  // Save list when is the begin of recursive function
837  $save_link_by_element = $link_by_element;
838  $save_element_by_link = $element_by_link;
839  }
840 
841  do {
842  // Get current element id, get this payment id list and delete the entry
843  $current_link_key = !empty($link_key) ? $link_key : array_keys($element_by_link)[0];
844  $element_ids = $element_by_link[$current_link_key];
845  unset($element_by_link[$current_link_key]);
846 
847  foreach ($element_ids as $element_id) {
848  // Continue if element id in not found
849  if (!isset($link_by_element[$element_id])) {
850  continue;
851  }
852 
853  // Set the element in the current group
854  $current_group[$element_id] = $element_id;
855 
856  // Get current link keys, get this element id list and delete the entry
857  $link_keys = $link_by_element[$element_id];
858  unset($link_by_element[$element_id]);
859 
860  // Set element id on the current group for each link key of the element
861  foreach ($link_keys as $key) {
862  $this->getGroupElements($link_by_element, $element_by_link, $key, $current_group);
863  }
864  }
865 
866  if (empty($link_key)) {
867  // Save current group and reset the current group when is the begin of recursive function
868  $grouped_elements[] = $current_group;
869  $current_group = array();
870  }
871  } while (!empty($element_by_link) && empty($link_key));
872 
873  if (empty($link_key)) {
874  // Restore list when is the begin of recursive function
875  $link_by_element = $save_link_by_element;
876  $element_by_link = $save_element_by_link;
877  }
878 
879  return $grouped_elements;
880  }
881 }
if($user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition: card.php:58
Class to manage Ledger (General Ledger and Subledger)
Class Lettering.
getDocTypeAndFkDocFromBankLines($bank_ids)
Get all fk_doc by doc_type from list of bank ids.
letteringThirdparty($socid)
letteringThirdparty
bookkeepingLetteringAll($bookkeeping_ids, $unlettering=false)
Lettering bookkeeping lines all types.
getGroupElements(&$link_by_element, &$element_by_link, $link_key='', &$current_group=array())
Get element ids grouped by link or element in common.
bookkeepingLettering($bookkeeping_ids, $unlettering=false)
Lettering bookkeeping lines.
deleteLettering($ids, $notrigger=0)
getLinkedDocumentByGroup($document_ids, $doc_type)
Get all linked document ids by group and type.
updateLettering($ids=array(), $notrigger=0)
getBankLinesFromFkDocAndDocType($document_ids, $doc_type)
Get all bank ids from list of document ids of a type.
getLinkedLines($bookkeeping_ids, $only_has_subledger_account=true)
Lettering bookkeeping lines.
Class to manage third parties objects (customers, suppliers, prospects...)
if(isModEnabled('invoice') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&!getDolGlobalString('MAIN_USE_NEW_SUPPLIERMOD') && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') && $user->hasRight('tax', 'charges', 'lire')) if(isModEnabled('invoice') &&isModEnabled('order') && $user->hasRight("commande", "lire") &&!getDolGlobalString('WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER')) $sql
Social contributions to pay.
Definition: index.php:744
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
dol_now($mode='auto')
Return date for now.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.