108 if (
$object->code_compta_client ==
'411CUSTCODE') {
109 $object->code_compta_client =
'';
112 if (
$object->code_compta_fournisseur ==
'401SUPPCODE') {
113 $object->code_compta_fournisseur =
'';
119 $sql =
"SELECT DISTINCT bk.rowid, bk.doc_date, bk.doc_type, bk.doc_ref, bk.subledger_account, ";
120 $sql .=
" bk.numero_compte , bk.label_compte, bk.debit , bk.credit, bk.montant ";
121 $sql .=
" , bk.sens , bk.code_journal , bk.piece_num, bk.date_lettering, bu.url_id , bu.type ";
122 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_bookkeeping as bk";
123 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"bank_url as bu ON(bk.fk_doc = bu.fk_bank AND bu.type IN ('payment', 'payment_supplier') ) ";
125 if (
$object->code_compta_client !=
"") {
126 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_client).
"' ";
128 if (
$object->code_compta_client !=
"" &&
$object->code_compta_fournisseur !=
"") {
131 if (
$object->code_compta_fournisseur !=
"") {
132 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_fournisseur).
"' ";
135 $sql .=
" ) AND (bk.date_lettering ='' OR bk.date_lettering IS NULL) ";
136 $sql .=
" AND (bk.lettering_code != '' OR bk.lettering_code IS NULL) ";
137 $sql .=
' AND bk.date_validated IS NULL ';
138 $sql .= $this->db->order(
'bk.doc_date',
'DESC');
142 $resql = $this->db->query($sql);
144 $num = $this->db->num_rows($resql);
146 while ($obj = $this->db->fetch_object($resql)) {
150 if ($obj->type ==
'payment_supplier') {
151 $sql =
'SELECT DISTINCT bk.rowid, facf.ref, facf.ref_supplier, payf.fk_bank, facf.rowid as fact_id';
152 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture_fourn facf ";
153 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiementfourn_facturefourn as payfacf ON payfacf.fk_facturefourn=facf.rowid";
154 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiementfourn as payf ON payfacf.fk_paiementfourn=payf.rowid";
155 $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).
"')";
156 $sql .=
" WHERE payfacf.fk_paiementfourn = '".$this->db->escape($obj->url_id).
"' ";
157 $sql .=
" AND facf.entity = ".$conf->entity;
158 $sql .=
" AND code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=4 AND entity=".$conf->entity.
") ";
160 if (
$object->code_compta_client !=
"") {
161 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_client).
"' ";
163 if (
$object->code_compta_client !=
"" &&
$object->code_compta_fournisseur !=
"") {
166 if (
$object->code_compta_fournisseur !=
"") {
167 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_fournisseur).
"' ";
171 $resql2 = $this->db->query($sql);
173 while ($obj2 = $this->db->fetch_object($resql2)) {
174 $ids[$obj2->rowid] = $obj2->rowid;
175 $ids_fact[] = $obj2->fact_id;
177 $this->db->free($resql2);
179 $this->errors[] = $this->db->lasterror;
182 if (count($ids_fact)) {
183 $sql =
'SELECT bk.rowid, facf.ref, facf.ref_supplier ';
184 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture_fourn facf ";
185 $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)).
"))";
186 $sql .=
" WHERE bk.code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=3 AND entity=".$conf->entity.
") ";
187 $sql .=
" AND facf.entity = ".$conf->entity;
189 if (
$object->code_compta_client !=
"") {
190 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_client).
"' ";
192 if (
$object->code_compta_client !=
"" &&
$object->code_compta_fournisseur !=
"") {
195 if (
$object->code_compta_fournisseur !=
"") {
196 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_fournisseur).
"' ";
200 $resql2 = $this->db->query($sql);
202 while ($obj2 = $this->db->fetch_object($resql2)) {
203 $ids[$obj2->rowid] = $obj2->rowid;
205 $this->db->free($resql2);
207 $this->errors[] = $this->db->lasterror;
211 } elseif ($obj->type ==
'payment') {
212 $sql =
'SELECT DISTINCT bk.rowid, fac.ref, fac.ref, pay.fk_bank, fac.rowid as fact_id';
213 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture fac ";
214 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiement_facture as payfac ON payfac.fk_facture=fac.rowid";
215 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"paiement as pay ON payfac.fk_paiement=pay.rowid";
216 $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).
"')";
217 $sql .=
" WHERE payfac.fk_paiement = '".$this->db->escape($obj->url_id).
"' ";
218 $sql .=
" AND bk.code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=4 AND entity=".$conf->entity.
") ";
219 $sql .=
" AND fac.entity IN (".getEntity(
'invoice', 0).
")";
221 if (
$object->code_compta_client !=
"") {
222 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_client).
"' ";
224 if (
$object->code_compta_client !=
"" &&
$object->code_compta_fournisseur !=
"") {
227 if (
$object->code_compta_fournisseur !=
"") {
228 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_fournisseur).
"' ";
232 $resql2 = $this->db->query($sql);
234 while ($obj2 = $this->db->fetch_object($resql2)) {
235 $ids[$obj2->rowid] = $obj2->rowid;
236 $ids_fact[] = $obj2->fact_id;
239 $this->errors[] = $this->db->lasterror;
242 if (count($ids_fact)) {
243 $sql =
'SELECT bk.rowid, fac.ref, fac.ref_supplier ';
244 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture fac ";
245 $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)).
"))";
246 $sql .=
" WHERE code_journal IN (SELECT code FROM ".MAIN_DB_PREFIX.
"accounting_journal WHERE nature=2 AND entity=".$conf->entity.
") ";
247 $sql .=
" AND fac.entity IN (".getEntity(
'invoice', 0).
")";
249 if (
$object->code_compta_client !=
"") {
250 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_client).
"' ";
252 if (
$object->code_compta_client !=
"" &&
$object->code_compta_fournisseur !=
"") {
255 if (
$object->code_compta_fournisseur !=
"") {
256 $sql .=
" bk.subledger_account = '".$this->db->escape(
$object->code_compta_fournisseur).
"' ";
260 $resql2 = $this->db->query($sql);
262 while ($obj2 = $this->db->fetch_object($resql2)) {
263 $ids[$obj2->rowid] = $obj2->rowid;
265 $this->db->free($resql2);
267 $this->errors[] = $this->db->lasterror;
273 if (count($ids) > 1) {
277 $this->db->free($resql);
280 foreach ($this->errors as $errmsg) {
282 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
304 $letter = str_pad(
"",
getDolGlobalInt(
'ACCOUNTING_LETTERING_NBLETTERS', 3), $partial ?
'a' :
'A');
309 $sql =
"SELECT ab.lettering_code, GROUP_CONCAT(DISTINCT ab.rowid SEPARATOR ',') AS bookkeeping_ids";
310 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab";
311 $sql .=
" WHERE ab.rowid IN (" . $this->db->sanitize(implode(
',', $ids)) .
")";
312 $sql .=
" GROUP BY ab.lettering_code";
313 $sql .=
" ORDER BY ab.lettering_code DESC";
315 dol_syslog(__METHOD__ .
" - Check partial / normal lettering case", LOG_DEBUG);
316 $resql = $this->db->query($sql);
318 while ($obj = $this->db->fetch_object($resql)) {
319 if (empty($obj->lettering_code))
continue;
322 if ($partial && preg_match(
'/^[A-Z]+$/', $obj->lettering_code)) {
323 if (!empty($obj->bookkeeping_ids)) $ids = array_diff($ids, explode(
',', $obj->bookkeeping_ids));
324 } elseif (!$partial && preg_match(
'/^[a-z]+$/', $obj->lettering_code)) {
326 $sql2 =
"UPDATE " . MAIN_DB_PREFIX .
"accounting_bookkeeping SET";
327 $sql2 .=
" lettering_code = NULL";
328 $sql2 .=
", date_lettering = NULL";
329 $sql2 .=
" WHERE entity IN (" .
getEntity(
'accountancy') .
")";
330 $sql2 .=
" AND lettering_code = '" . $this->db->escape($obj->lettering_code) .
"'";
332 dol_syslog(__METHOD__ .
" - Remove partial lettering", LOG_DEBUG);
333 $resql2 = $this->db->query($sql2);
335 $this->errors[] =
'Error' . $this->db->lasterror();
341 $this->db->free($resql);
343 $this->errors[] =
'Error' . $this->db->lasterror();
347 if (!$error && !empty($ids)) {
349 $sql =
"SELECT DISTINCT ab2.lettering_code";
350 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab";
351 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab2 ON ab2.subledger_account = ab.subledger_account";
352 $sql .=
" WHERE ab.rowid IN (" . $this->db->sanitize(implode(
',', $ids)) .
")";
353 $sql .=
" AND ab2.lettering_code != ''";
354 $sql .=
" ORDER BY ab2.lettering_code DESC";
356 dol_syslog(__METHOD__ .
" - Get next code", LOG_DEBUG);
357 $resql = $this->db->query($sql);
359 while ($obj = $this->db->fetch_object($resql)) {
360 if (!empty($obj->lettering_code) &&
361 (($partial && preg_match(
'/^[a-z]+$/', $obj->lettering_code)) ||
362 (!$partial && preg_match(
'/^[A-Z]+$/', $obj->lettering_code)))
364 $letter = $obj->lettering_code;
369 $this->db->free($resql);
371 $this->errors[] =
'Error' . $this->db->lasterror();
376 if (!$error && !$partial) {
377 $sql =
"SELECT SUM(ABS(debit)) as deb, SUM(ABS(credit)) as cred FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping WHERE ";
378 $sql .=
" rowid IN (" . $this->db->sanitize(implode(
',', $ids)) .
") AND lettering_code IS NULL AND subledger_account != ''";
380 dol_syslog(__METHOD__ .
" - Test amount integrity", LOG_DEBUG);
381 $resql = $this->db->query($sql);
383 if ($obj = $this->db->fetch_object($resql)) {
384 if (!(round(abs($obj->deb), 2) === round(abs($obj->cred), 2))) {
385 $this->errors[] =
'Total not exacts ' . round(abs($obj->deb), 2) .
' vs ' . round(abs($obj->cred), 2);
389 $this->db->free($resql);
391 $this->errors[] =
'Erreur sql' . $this->db->lasterror();
398 $sql =
"UPDATE " . MAIN_DB_PREFIX .
"accounting_bookkeeping SET";
399 $sql .=
" lettering_code='" . $this->db->escape($letter) .
"'";
400 $sql .=
", date_lettering = '" . $this->db->idate($now) .
"'";
401 $sql .=
" WHERE rowid IN (" . $this->db->sanitize(implode(
',', $ids)) .
") AND lettering_code IS NULL AND subledger_account != ''";
403 dol_syslog(__METHOD__ .
" - Update lettering code", LOG_DEBUG);
404 $resql = $this->db->query($sql);
407 $this->errors[] =
"Error " . $this->db->lasterror();
409 $affected_rows = $this->db->affected_rows($resql);
416 $this->db->rollback();
417 foreach ($this->errors as $errmsg) {
418 dol_syslog(get_class($this) .
"::update " . $errmsg, LOG_ERR);
419 $this->error .= ($this->error ?
', ' . $errmsg : $errmsg);
424 return $affected_rows;
506 $this->errors = array();
509 $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
514 if (!is_array($grouped_lines)) {
518 foreach ($grouped_lines as $lines) {
521 $do_it = !$unlettering;
522 $lettering_code =
null;
523 $piece_num_lines = array();
524 $bookkeeping_lines = array();
525 foreach ($lines as $line_infos) {
526 $bookkeeping_lines[$line_infos[
'id']] = $line_infos[
'id'];
527 $piece_num_lines[$line_infos[
'piece_num']] = $line_infos[
'piece_num'];
528 $total += ($line_infos[
'credit'] > 0 ? $line_infos[
'credit'] : -$line_infos[
'debit']);
532 if (isset($lettering_code) && $lettering_code != $line_infos[
'lettering_code']) {
533 $this->errors[] = $langs->trans(
'AccountancyErrorMismatchLetteringCode');
537 if (!isset($lettering_code)) {
538 $lettering_code = (string) $line_infos[
'lettering_code'];
540 if (!empty($line_infos[
'lettering_code'])) {
543 } elseif (!empty($line_infos[
'lettering_code'])) {
549 if (!$group_error && !$unlettering &&
price2num($total) != 0) {
550 $this->errors[] = $langs->trans(
'AccountancyErrorMismatchBalanceAmount', $total);
555 if (!$group_error && $do_it) {
563 } elseif ($result > 0) {
569 $this->errors[] = $langs->trans(
'AccountancyErrorLetteringBookkeeping', implode(
', ', $piece_num_lines));
575 return -2 - $nb_lettering;
577 return $nb_lettering;
588 public function getLinkedLines($bookkeeping_ids, $only_has_subledger_account =
true)
590 global $conf, $langs;
591 $this->errors = array();
594 $bookkeeping_ids = is_array($bookkeeping_ids) ? $bookkeeping_ids : array();
597 $sql =
"SELECT DISTINCT ab.doc_type, ab.fk_doc";
598 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab";
599 $sql .=
" WHERE ab.entity IN (" .
getEntity(
'accountancy') .
")";
600 $sql .=
" AND ab.fk_doc > 0";
601 if (!empty($bookkeeping_ids)) {
603 $sql .=
" AND EXISTS (";
604 $sql .=
" SELECT rowid";
605 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS pn";
606 $sql .=
" WHERE pn.entity IN (" .
getEntity(
'accountancy') .
")";
607 $sql .=
" AND pn.rowid IN (" . $this->db->sanitize(implode(
',', $bookkeeping_ids)) .
")";
608 $sql .=
" AND pn.piece_num = ab.piece_num";
611 if ($only_has_subledger_account) {
612 $sql .=
" AND ab.subledger_account != ''";
615 dol_syslog(__METHOD__ .
" - Get all bookkeeping lines", LOG_DEBUG);
616 $resql = $this->db->query($sql);
618 $this->errors[] =
"Error " . $this->db->lasterror();
622 $bookkeeping_lines_by_type = array();
623 while ($obj = $this->db->fetch_object($resql)) {
624 $bookkeeping_lines_by_type[$obj->doc_type][$obj->fk_doc] = $obj->fk_doc;
626 $this->db->free($resql);
628 if (empty($bookkeeping_lines_by_type)) {
632 if (!empty($bookkeeping_lines_by_type[
'bank'])) {
634 if (!is_array($new_bookkeeping_lines_by_type)) {
637 foreach ($new_bookkeeping_lines_by_type as $doc_type => $fk_docs) {
638 foreach ($fk_docs as $fk_doc) {
639 $bookkeeping_lines_by_type[$doc_type][$fk_doc] = $fk_doc;
644 $grouped_lines = array();
645 foreach (self::$doc_type_infos as $doc_type => $doc_type_info) {
646 if (!is_array($bookkeeping_lines_by_type[$doc_type])) {
652 if (!is_array($doc_grouped)) {
657 foreach ($doc_grouped as $doc_ids) {
659 if (!is_array($bank_ids)) {
664 $sql =
"SELECT DISTINCT ab.rowid, ab.piece_num, ab.debit, ab.credit, ab.lettering_code";
665 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS ab";
666 $sql .=
" WHERE ab.entity IN (" .
getEntity(
'accountancy') .
")";
668 if (!empty($bank_ids)) {
670 $sql .=
" SELECT bpn.rowid";
671 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS bpn";
672 $sql .=
" WHERE bpn.entity IN (" .
getEntity(
'accountancy') .
")";
673 $sql .=
" AND bpn.doc_type = 'bank'";
674 $sql .=
" AND bpn.fk_doc IN (" . $this->db->sanitize(implode(
',', $bank_ids)) .
")";
675 $sql .=
" AND bpn.piece_num = ab.piece_num";
679 $sql .=
" SELECT dpn.rowid";
680 $sql .=
" FROM " . MAIN_DB_PREFIX .
"accounting_bookkeeping AS dpn";
681 $sql .=
" WHERE dpn.entity IN (" .
getEntity(
'accountancy') .
")";
682 $sql .=
" AND dpn.doc_type = '" . $this->db->escape($doc_type) .
"'";
683 $sql .=
" AND dpn.fk_doc IN (" . $this->db->sanitize(implode(
',', $doc_ids)) .
")";
684 $sql .=
" AND dpn.piece_num = ab.piece_num";
687 if ($only_has_subledger_account) {
688 $sql .=
" AND ab.subledger_account != ''";
691 dol_syslog(__METHOD__ .
" - Get all bookkeeping lines linked", LOG_DEBUG);
692 $resql = $this->db->query($sql);
694 $this->errors[] =
"Error " . $this->db->lasterror();
699 while ($obj = $this->db->fetch_object($resql)) {
700 $group[$obj->rowid] = array(
702 'piece_num' => $obj->piece_num,
703 'debit' => $obj->debit,
704 'credit' => $obj->credit,
705 'lettering_code' => $obj->lettering_code,
708 $this->db->free($resql);
710 if (!empty($group)) {
711 $grouped_lines[] = $group;
716 return $grouped_lines;
727 dol_syslog(__METHOD__ .
" - bank_ids=".json_encode($bank_ids), LOG_DEBUG);
730 $bank_ids = is_array($bank_ids) ? $bank_ids : array();
732 if (empty($bank_ids)) {
736 $bookkeeping_lines_by_type = array();
737 foreach (self::$doc_type_infos as $doc_type => $doc_type_info) {
739 $sql =
"SELECT DISTINCT dp." . $this->db->sanitize($doc_type_info[
'doc_payment_table_fk_doc']) .
" AS fk_doc";
740 $sql .=
" FROM " . MAIN_DB_PREFIX . $this->db->sanitize($doc_type_info[
'payment_table']) .
" AS p";
741 $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";
742 $sql .=
" WHERE p." . $this->db->sanitize($doc_type_info[
'payment_table_fk_bank']) .
" IN (" . $this->db->sanitize(implode(
',', $bank_ids)) .
")";
743 $sql .=
" AND dp." . $this->db->sanitize($doc_type_info[
'doc_payment_table_fk_doc']) .
" > 0";
745 dol_syslog(__METHOD__ .
" - Get all fk_doc by doc_type from list of bank ids for '" . $doc_type .
"'", LOG_DEBUG);
746 $resql = $this->db->query($sql);
748 $this->errors[] =
"Error " . $this->db->lasterror();
752 while ($obj = $this->db->fetch_object($resql)) {
753 $bookkeeping_lines_by_type[$doc_type][$obj->fk_doc] = $obj->fk_doc;
755 $this->db->free($resql);
758 return $bookkeeping_lines_by_type;
772 dol_syslog(__METHOD__ .
" - bank_ids=".json_encode($document_ids) .
", doc_type=$doc_type", LOG_DEBUG);
775 $document_ids = is_array($document_ids) ? $document_ids : array();
777 $document_ids = array_filter($document_ids);
779 $doc_type = trim($doc_type);
781 if (empty($document_ids)) {
784 if (!is_array(self::$doc_type_infos[$doc_type])) {
785 $langs->load(
'errors');
786 $this->errors[] = $langs->trans(
'ErrorBadParameters');
790 $doc_type_info = self::$doc_type_infos[$doc_type];
794 $sql =
"SELECT DISTINCT p." . $this->db->sanitize($doc_type_info[
'payment_table_fk_bank']) .
" AS fk_doc";
795 $sql .=
" FROM " . MAIN_DB_PREFIX . $this->db->sanitize($doc_type_info[
'payment_table']) .
" AS p";
796 $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";
797 $sql .=
" WHERE dp." . $this->db->sanitize($doc_type_info[
'doc_payment_table_fk_doc']) .
" IN (" . $this->db->sanitize(implode(
',', $document_ids)) .
")";
798 $sql .=
" AND p." . $this->db->sanitize($doc_type_info[
'payment_table_fk_bank']) .
" > 0";
800 dol_syslog(__METHOD__ .
" - Get all bank ids from list of document ids of a type '" . $doc_type .
"'", LOG_DEBUG);
801 $resql = $this->db->query($sql);
803 $this->errors[] =
"Error " . $this->db->lasterror();
807 while ($obj = $this->db->fetch_object($resql)) {
808 $bank_ids[$obj->fk_doc] = $obj->fk_doc;
810 $this->db->free($resql);
827 $document_ids = is_array($document_ids) ? $document_ids : array();
828 $doc_type = trim($doc_type);
830 $document_ids = array_filter($document_ids);
832 if (empty($document_ids)) {
836 if (!is_array(self::$doc_type_infos[$doc_type])) {
837 $langs->load(
'errors');
838 $this->errors[] = $langs->trans(
'ErrorBadParameters');
842 $doc_type_info = self::$doc_type_infos[$doc_type];
845 $current_document_ids = array();
846 $link_by_element = array();
847 $element_by_link = array();
848 foreach ($doc_type_info[
'linked_info'] as $linked_info) {
849 if (empty($linked_info[
'fk_line_link'])) {
850 $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";
851 $sql .=
" FROM ".MAIN_DB_PREFIX.$this->db->sanitize($linked_info[
'table']).
" AS tl";
852 $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']);
853 $sql .=
" WHERE tl.".$this->db->sanitize($linked_info[
'fk_doc']).
" IN (".$this->db->sanitize(implode(
',', $document_ids)).
")";
855 $sql =
"SELECT DISTINCT tl2.fk_link, tl2.fk_doc";
858 $sql .=
" SELECT DISTINCT " . $this->db->ifsql(
"tll.".$this->db->sanitize($linked_info[
'fk_table_link_line_parent']),
"tll.".$this->db->sanitize($linked_info[
'fk_table_link_line_parent']),
"tl.".$this->db->sanitize($linked_info[
'fk_link'])).
" AS fk_link, tl.".$this->db->sanitize($linked_info[
'fk_doc']).
" AS fk_doc";
859 $sql .=
" FROM " . MAIN_DB_PREFIX .$this->db->sanitize($linked_info[
'table']).
" AS tl";
861 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX . $this->db->sanitize($linked_info[
'table_link_line']) .
" AS tll ON tll.".$this->db->sanitize($linked_info[
'fk_table_link_line']) .
" = tl.".$this->db->sanitize($linked_info[
'fk_line_link']);
863 $sql .=
" LEFT JOIN (";
865 $sql .=
" SELECT DISTINCT " . $this->db->ifsql(
"tll.".$this->db->sanitize($linked_info[
'fk_table_link_line_parent']),
"tll.".$this->db->sanitize($linked_info[
'fk_table_link_line_parent']),
"tl.".$this->db->sanitize($linked_info[
'fk_link'])).
" AS fk_link, tl.".$this->db->sanitize($linked_info[
'fk_doc']).
" AS fk_doc";
866 $sql .=
" FROM " . MAIN_DB_PREFIX .$this->db->sanitize($linked_info[
'table']).
" AS tl";
868 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX . $this->db->sanitize($linked_info[
'table_link_line']) .
" AS tll ON tll.".$this->db->sanitize($linked_info[
'fk_table_link_line']) .
" = tl.".$this->db->sanitize($linked_info[
'fk_line_link']);
869 $sql .=
") AS tl2 ON tl2.fk_link = tl.fk_link";
870 $sql .=
" WHERE tl.fk_doc IN (" . $this->db->sanitize(implode(
',', $document_ids)) .
")";
871 $sql .=
" AND tl2.fk_doc IS NOT NULL";
874 dol_syslog(__METHOD__ .
" - Get document lines", LOG_DEBUG);
875 $resql = $this->db->query($sql);
877 $this->errors[] =
"Error " . $this->db->lasterror();
881 $is_fk_link_is_also_fk_doc = !empty($linked_info[
'is_fk_link_is_also_fk_doc']);
882 while ($obj = $this->db->fetch_object($resql)) {
883 $current_document_ids[$obj->fk_doc] = $obj->fk_doc;
885 $link_key = $linked_info[
'prefix'] . $obj->fk_link;
886 $element_by_link[$link_key][$obj->fk_doc] = $obj->fk_doc;
887 $link_by_element[$obj->fk_doc][$link_key] = $link_key;
888 if ($is_fk_link_is_also_fk_doc) {
889 $element_by_link[$link_key][$obj->fk_link] = $obj->fk_link;
890 $link_by_element[$obj->fk_link][$link_key] = $link_key;
893 $this->db->free($resql);
896 if (count(array_diff($document_ids, $current_document_ids))) {
912 public function getGroupElements(&$link_by_element, &$element_by_link, $link_key =
'', &$current_group = array())
914 $grouped_elements = array();
915 if (!empty($link_key) && !isset($element_by_link[$link_key])) {
917 return $grouped_elements;
920 if (empty($link_key)) {
922 $save_link_by_element = $link_by_element;
923 $save_element_by_link = $element_by_link;
928 $current_link_key = !empty($link_key) ? $link_key : array_keys($element_by_link)[0];
929 $element_ids = $element_by_link[$current_link_key];
930 unset($element_by_link[$current_link_key]);
932 foreach ($element_ids as $element_id) {
934 if (!isset($link_by_element[$element_id])) {
939 $current_group[$element_id] = $element_id;
942 $link_keys = $link_by_element[$element_id];
943 unset($link_by_element[$element_id]);
946 foreach ($link_keys as $key) {
947 $this->
getGroupElements($link_by_element, $element_by_link, $key, $current_group);
951 if (empty($link_key)) {
953 $grouped_elements[] = $current_group;
954 $current_group = array();
956 }
while (!empty($element_by_link) && empty($link_key));
958 if (empty($link_key)) {
960 $link_by_element = $save_link_by_element;
961 $element_by_link = $save_element_by_link;
964 return $grouped_elements;