bonprelevement.class.php

Go to the documentation of this file.

<?php
/* Copyright (C) 2004-2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
 * Copyright (C) 2005-2012 Regis Houssin        <regis.houssin@inodbox.com>
 * Copyright (C) 2010-2015 Juanjo Menent        <jmenent@2byte.es>
 * Copyright (C) 2010-2014 Laurent Destailleur  <eldy@users.sourceforge.net>
 * Copyright (C) 2014-2016 Ferran Marcet        <fmarcet@2byte.es>
 * Copyright (C) 2018      Nicolas ZABOURI      <info@inovea-conseil.com>
 * Copyright (C) 2019       JC Prieto     <jcprieto@virtual20.com><prietojc@gmail.com>
 * Copyright (C) 2024-2026  MDW         <mdeweerd@users.noreply.github.com>
 * Copyright (C) 2024-2026  Frédéric France     <frederic.france@free.fr>
 * Copyright (C) 2026 Guillaume de Wellenstein  <guillaume@tecneo.fr>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */
 
require_once DOL_DOCUMENT_ROOT . '/core/class/commonobject.class.php';
require_once DOL_DOCUMENT_ROOT . '/core/lib/bank.lib.php';
require_once DOL_DOCUMENT_ROOT . '/compta/bank/class/account.class.php';
require_once DOL_DOCUMENT_ROOT . '/compta/facture/class/facture.class.php';
require_once DOL_DOCUMENT_ROOT . '/compta/paiement/class/paiement.class.php';
require_once DOL_DOCUMENT_ROOT . '/fourn/class/fournisseur.facture.class.php';
require_once DOL_DOCUMENT_ROOT . '/fourn/class/paiementfourn.class.php';
require_once DOL_DOCUMENT_ROOT . '/salaries/class/salary.class.php';
require_once DOL_DOCUMENT_ROOT . '/salaries/class/paymentsalary.class.php';
require_once DOL_DOCUMENT_ROOT . '/societe/class/societe.class.php';
require_once DOL_DOCUMENT_ROOT . '/user/class/userbankaccount.class.php';
 
 
class BonPrelevement extends CommonObject
{
  public $element = 'widthdraw';
 
  public $table_element = 'prelevement_bons';
 
  public $picto = 'payment';
 
  public $date_echeance;
  public $raison_sociale;
  public $reference_remise;
  public $emetteur_code_guichet;
  public $emetteur_numero_compte;
  public $emetteur_code_banque;
  public $emetteur_number_key;
  public $sepa_xml_pti_in_ctti;
 
  public $emetteur_iban;
  public $emetteur_bic;
  public $emetteur_ics;
 
  public $user_trans;
  public $user_credit;
 
  public $total;
 
  public $fetched;
 
  public $labelStatus = array();
 
  public $factures = array();
 
  public $methodes_trans = array();
 
  public $invoice_in_error = array();
 
  public $thirdparty_in_error = array();
 
  public $file;
 
  public $filename;
 
  const STATUS_DRAFT = 0;
  const STATUS_TRANSFERED = 1;
  const STATUS_CREDITED = 2;    // STATUS_CREDITED and STATUS_DEBITED is same. Difference is in ->type
  const STATUS_DEBITED = 2;   // STATUS_CREDITED and STATUS_DEBITED is same. Difference is in ->type
  const STATUS_CANCELED = 9;
 
 
  // BEGIN MODULEBUILDER PROPERTIES
  public $fields = array(
    'rowid' => array('type' => 'integer', 'label' => 'TechnicalID', 'enabled' => 1, 'position' => 10, 'notnull' => 1, 'visible' => 0,),
    'ref' => array('type' => 'varchar(12)', 'label' => 'Ref', 'enabled' => 1, 'position' => 15, 'notnull' => 0, 'visible' => -1, 'csslist' => 'tdoverflowmax150', 'showoncombobox' => 1,),
    'datec' => array('type' => 'datetime', 'label' => 'DateCreation', 'enabled' => 1, 'position' => 25, 'notnull' => 0, 'visible' => -1,),
    'amount' => array('type' => 'double(24,8)', 'label' => 'Amount', 'enabled' => 1, 'position' => 30, 'notnull' => 0, 'visible' => -1,),
    'statut' => array('type' => 'smallint(6)', 'label' => 'Statut', 'enabled' => 1, 'position' => 500, 'notnull' => 0, 'visible' => -1, 'arrayofkeyval' => array(0 => 'Wait', 1 => 'Transfered', 2 => 'Credited')),
    'credite' => array('type' => 'smallint(6)', 'label' => 'Credite', 'enabled' => 1, 'position' => 40, 'notnull' => 0, 'visible' => -1,),
    'note' => array('type' => 'text', 'label' => 'Note', 'enabled' => 1, 'position' => 45, 'notnull' => 0, 'visible' => -1,),
    'date_trans' => array('type' => 'datetime', 'label' => 'TransData', 'enabled' => 1, 'position' => 50, 'notnull' => 0, 'visible' => -1,),
    'method_trans' => array('type' => 'smallint(6)', 'label' => 'Methodtrans', 'enabled' => 1, 'position' => 55, 'notnull' => 0, 'visible' => -1,),
    'fk_user_trans' => array('type' => 'integer:User:user/class/user.class.php', 'label' => 'UserTransfer', 'enabled' => 1, 'position' => 60, 'notnull' => 0, 'visible' => -1, 'css' => 'maxwidth500 widthcentpercentminusxx', 'csslist' => 'tdoverflowmax150',),
    'date_credit' => array('type' => 'datetime', 'label' => 'CreditDate', 'enabled' => 1, 'position' => 65, 'notnull' => 0, 'visible' => -1,),
    'fk_user_credit' => array('type' => 'integer:User:user/class/user.class.php', 'label' => 'UserCredit', 'enabled' => 1, 'position' => 70, 'notnull' => 0, 'visible' => -1, 'css' => 'maxwidth500 widthcentpercentminusxx', 'csslist' => 'tdoverflowmax150',),
    'type' => array('type' => 'varchar(16)', 'label' => 'Type', 'enabled' => 1, 'position' => 75, 'notnull' => 0, 'visible' => -1,),
    'fk_bank_account' => array('type' => 'integer', 'label' => 'BankAccount', 'enabled' => 1, 'position' => 80, 'notnull' => 0, 'visible' => -1, 'css' => 'maxwidth500 widthcentpercentminusxx',),
  );
  public $rowid;
  public $ref;
  public $datec;
 
  public $amount;
 
  public $statut;
  public $status;
 
  public $credite;
 
  public $note;
 
  public $note_private;
 
  public $date_trans;
  public $method_trans;
  public $fk_user_trans;
  public $date_credit;
  public $fk_user_credit;
  public $type;
  public $fk_bank_account;
  // END MODULEBUILDER PROPERTIES
 
 
 
  public function __construct($db)
  {
    $this->db = $db;
 
    $this->filename = '';
 
    $this->date_echeance = dol_now();
    $this->raison_sociale = "";
    $this->reference_remise = "";
 
    $this->emetteur_code_guichet = "";
    $this->emetteur_numero_compte = "";
    $this->emetteur_code_banque = "";
    $this->emetteur_number_key = "";
    $this->sepa_xml_pti_in_ctti = false;
 
    $this->emetteur_iban = "";
    $this->emetteur_bic = "";
    $this->emetteur_ics = "";
 
    $this->factures = array();
 
    $this->methodes_trans = array(0 => 'Internet', 2 => 'Email', 3 => 'Api');
 
    $this->fetched = 0;
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function addWithdrawDetail($invoice_id, $client_id, $client_nom, $amount, $code_banque, $code_guichet, $number, $number_key, $type = 'debit-order', $sourcetype = '', $bic = '', $iban = '', $rum = '', $id_prelevement_demande = 0)
  {
    // phpcs:enable
    $result = 0;
    $line_id = 0;
 
    // TODO Save the $id_prelevement_demande
 
    // Add lines into prelevement_lignes for tracking. The ID of line inserted is returned into $line_id.
    $result = $this->addline($line_id, $client_id, $client_nom, $amount, $code_banque, $code_guichet, $number, $number_key, $sourcetype, $bic, $iban, $rum, $id_prelevement_demande);
 
 
    if ($result == 0) {
      if ($line_id > 0) {
        $sql = "INSERT INTO " . MAIN_DB_PREFIX . "prelevement (";
        if ($type != 'bank-transfer') {
          $sql .= "fk_facture";
        } else {
          if ($sourcetype == 'salary') {
            $sql .= "fk_salary";
          } else {
            $sql .= "fk_facture_fourn";
          }
        }
        $sql .= ",fk_prelevement_lignes";
        $sql .= ") VALUES (";
        $sql .= ((int) $invoice_id);
        $sql .= ", " . ((int) $line_id);
        $sql .= ")";
 
        if ($this->db->query($sql)) {
          $result = 0;
        } else {
          $result = -1;
          $this->errors[] = get_class($this) . "::addWithdrawDetail " . $this->db->lasterror;
          dol_syslog(get_class($this) . "::addWithdrawDetail Error $result");
        }
      } else {
        $result = -2;
        $this->errors[] = get_class($this) . "::addWithdrawDetail linedid Empty";
        dol_syslog(get_class($this) . "::addWithdrawDetail Error $result");
      }
    } else {
      $result = -3;
      dol_syslog(get_class($this) . "::addWithdrawDetail Error $result");
    }
 
    return $result;
  }
 
  public function addline(&$line_id, $client_id, $client_nom, $amount, $code_banque, $code_guichet, $number, $number_key, $sourcetype = '', $bic = '', $iban = '', $rum = '', $id_prelevement_demande = 0)
  {
    $result = -1;
    $concat = getDolGlobalInt('MAIN_MODULE_PRELEVEMENT_CONCAT');  // ??? what is this for. Seems not used.
 
    if ($concat == 1) {
      $sql = "SELECT rowid";
      $sql .= " FROM  " . MAIN_DB_PREFIX . "prelevement_lignes";
      $sql .= " WHERE fk_prelevement_bons = " . ((int) $this->id);
      if ($sourcetype == 'salary') {
        $sql .= " AND fk_soc = " . ((int) $client_id);
      } else {
        $sql .= " AND fk_user = " . ((int) $client_id);
      }
      $sql .= " AND code_banque = '" . $this->db->escape($code_banque) . "'";
      $sql .= " AND code_guichet = '" . $this->db->escape($code_guichet) . "'";
      $sql .= " AND number = '" . $this->db->escape($number) . "'";
 
      $resql = $this->db->query($sql);
      if ($resql) {
        $num = $this->db->num_rows($resql);
      } else {
        $result = -1;
      }
    } else {
      /*
       * No aggregate, use 1 line per request of invoice/salary to pay
       */
      $sql = "INSERT INTO " . MAIN_DB_PREFIX . "prelevement_lignes (";
      $sql .= "fk_prelevement_bons";
      $sql .= ", fk_soc";
      $sql .= ", client_nom";
      $sql .= ", amount";
      $sql .= ", fk_prelevement_demande";
      $sql .= ($sourcetype == 'salary' ? ", fk_user" : "");
      $sql .= ") VALUES (";
      $sql .= ((int) $this->id);
      $sql .= ", " . (($sourcetype != 'salary') ? ((int) $client_id) : "0");  // fk_soc can't be null
      $sql .= ", '" . $this->db->escape($client_nom) . "'";
      $sql .= ", " . ((float) price2num($amount));
      $sql .= ", " . ((int) $id_prelevement_demande);
      $sql .= (($sourcetype == 'salary') ? ", " . ((int) $client_id) : '');
      $sql .= ")";
      if ($this->db->query($sql)) {
        $line_id = $this->db->last_insert_id(MAIN_DB_PREFIX . "prelevement_lignes");
        $result = 0;
      } else {
        $this->errors[] = get_class($this) . "::addline Error -2 " . $this->db->lasterror;
        dol_syslog(get_class($this) . "::addline Error -2");
        $result = -2;
      }
    }
 
    return $result;
  }
 
  public function getErrorString($error)
  {
    global $langs;
 
    $errors = array();
 
    $errors[1027] = $langs->trans("DateInvalid");
 
    return $errors[abs($error)] ?? 'unknown error code';
  }
 
  public function fetch($rowid, $ref = '')
  {
    $sql = "SELECT p.rowid, p.ref, p.amount, p.note";
    $sql .= ", p.datec as dc";
    $sql .= ", p.date_trans as date_trans";
    $sql .= ", p.method_trans, p.fk_user_trans";
    $sql .= ", p.date_credit as date_credit";
    $sql .= ", p.fk_user_credit";
    $sql .= ", p.type";
    $sql .= ", p.fk_bank_account";
    $sql .= ", p.statut as status";
    $sql .= " FROM " . MAIN_DB_PREFIX . "prelevement_bons as p";
    $sql .= " WHERE p.entity IN (" . getEntity('invoice') . ")";
    if ($rowid > 0) {
      $sql .= " AND p.rowid = " . ((int) $rowid);
    } else {
      $sql .= " AND p.ref = '" . $this->db->escape($ref) . "'";
    }
 
    dol_syslog(get_class($this) . "::fetch", LOG_DEBUG);
    $result = $this->db->query($sql);
    if ($result) {
      if ($this->db->num_rows($result)) {
        $obj = $this->db->fetch_object($result);
 
        $this->id             = $obj->rowid;
        $this->ref            = $obj->ref;
        $this->amount         = $obj->amount;
        $this->note           = $obj->note;
        $this->note_private   = $obj->note;
        $this->datec          = $this->db->jdate($obj->dc);
 
        $this->date_trans     = $this->db->jdate($obj->date_trans);
        $this->method_trans   = $obj->method_trans;
        $this->user_trans     = $obj->fk_user_trans;
 
        $this->date_credit = $this->db->jdate($obj->date_credit);
        $this->user_credit = $obj->fk_user_credit;
 
        $this->type = $obj->type;
        $this->fk_bank_account = $obj->fk_bank_account;
 
        $this->status = $obj->status;
        if (empty($this->status)) {   // Value is sometimes null in database
          $this->status = 0;
        }
        $this->statut = $this->status; // For backward compatibility
 
        $this->fetched = 1;
 
        return 1;
      } else {
        dol_syslog(get_class($this) . "::Fetch no record found");
        return 0;
      }
    } else {
      return -1;
    }
  }
 
  public function update(User $user, $notrigger = 0)
  {
    return $this->updateCommon($user, $notrigger);
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function set_infocredit($user, $date, $type = '')
  {
    // phpcs:enable
    global $conf, $langs;
 
    $error = 0;
 
    if ($this->fetched == 1) {
      if ($date < $this->date_trans) {
        $langs->load("errors");
        $this->error = $langs->trans('ErrorDateOfMovementLowerThanDateOfFileTransmission');
        dol_syslog("bon-prelevment::set_infocredit 1027 " . $this->error);
        return -1027;
      }
 
      $this->db->begin();
 
      $sql = " UPDATE " . MAIN_DB_PREFIX . "prelevement_bons";
      $sql .= " SET fk_user_credit = " . ((int) $user->id);
      $sql .= ", statut = " . self::STATUS_CREDITED;
      $sql .= ", date_credit = '" . $this->db->idate($date) . "'";
      $sql .= " WHERE rowid = " . ((int) $this->id);
      $sql .= " AND entity = " . ((int) $conf->entity);
      $sql .= " AND statut = " . self::STATUS_TRANSFERED;
 
      $resql = $this->db->query($sql);
      if ($resql) {
        $langs->load('withdrawals');
        $subject = $langs->trans("InfoCreditSubject", $this->ref);
        $message = $langs->trans("InfoCreditMessage", $this->ref, dol_print_date($date, 'dayhour'));
 
        // Add payment of withdrawal into bank
        $fk_bank_account = $this->fk_bank_account;
        if (empty($fk_bank_account)) {
          $fk_bank_account = ($this->type == 'bank-transfer' ? getDolGlobalInt('PAYMENTBYBANKTRANSFER_ID_BANKACCOUNT') : getDolGlobalInt('PRELEVEMENT_ID_BANKACCOUNT'));
        }
 
        $amounts = array();
        $amountsperthirdparty = array();
 
        $facs = $this->getListInvoices(1, $type);
        if ($this->error) {
          $error++;
        }
 
        // Loop on each invoice or salary.
        // $facs should be array(0=>id, 1=>amount requested)
        $num = count($facs);
        for ($i = 0; $i < $num; $i++) {
          if ($this->type == 'bank-transfer') {
            if ($type == 'salary') {
              $fac = new Salary($this->db);
            } else {
              $fac = new FactureFournisseur($this->db);
            }
          } else {
            $fac = new Facture($this->db);
          }
 
          $result = $fac->fetch($facs[$i][0]);
 
          $amounts[$fac->id] = $facs[$i][1];
          if ($this->type == 'bank-transfer') {
            if ($type == 'salary') {
              $amountsperthirdparty[$fac->fk_user][$fac->id] = $facs[$i][1];
            } else {
              $amountsperthirdparty[$fac->socid][$fac->id] = $facs[$i][1];
            }
          } else {
            $amountsperthirdparty[$fac->socid][$fac->id] = $facs[$i][1];
          }
 
          $totalpaid = $fac->getSommePaiement();
          $totalcreditnotes = 0;
          if (method_exists($fac, 'getSumCreditNotesUsed')) {
            $totalcreditnotes = $fac->getSumCreditNotesUsed();
          }
          $totaldeposits = 0;
          if (method_exists($fac, 'getSumDepositsUsed')) {
            $totaldeposits = $fac->getSumDepositsUsed();
          }
          $alreadypayed = $totalpaid + $totalcreditnotes + $totaldeposits;
 
          // Set the main document to pay with status Paid.
          // @TODO Move this after creation of payments done after
          $amountofdocument = $fac->total_ttc;
          if ($type == 'salary') {
            $amountofdocument = $fac->amount;
          }
          if (price2num($alreadypayed + $facs[$i][1], 'MT') == price2num($amountofdocument, 'MT')) {
            $result = $fac->setPaid($user);
            if ($result < 0) {
              $this->error = $fac->error;
              $this->errors = $fac->errors;
            }
          }
        }
 
        // Make one payment per customer or employee
        foreach ($amountsperthirdparty as $thirdpartyid => $cursoramounts) {
          if ($this->type == 'bank-transfer') {
            if ($type == 'salary') {
              $paiement = new PaymentSalary($this->db);
            } else {
              $paiement = new PaiementFourn($this->db);
            }
          } else {
            $paiement = new Paiement($this->db);
          }
          $paiement->datepaye = $date;
          $paiement->amounts = $cursoramounts; // Array with detail of dispatching of payments for each invoice
 
          if ($this->type == 'bank-transfer') {
            if ($type == 'salary') {
              $paiement->datep = $date;
 
              $paiement->paiementid = 2;
              $paiement->fk_typepayment = 2;
              $paiement->paiementcode = 'VIR';
            } else {
              $paiement->paiementid = 2;
              $paiement->paiementcode = 'VIR';
            }
          } else {
            $paiement->paiementid = 3;
            $paiement->paiementcode = 'PRE';
          }
 
          $paiement->num_payment = $this->ref; // Set ref of direct debit note
          $paiement->id_prelevement = $this->id;
 
          $result = $paiement->create($user, 1); // This use ->paiementid, that is ID of payment mode. closepaidinvoices=1 to convert deposit invoice to available credit
 
          if ($result < 0) {
            $error++;
            $this->error = $paiement->error;
            $this->errors = $paiement->errors;
            dol_syslog(get_class($this) . "::set_infocredit AddPayment Error " . $this->error);
          } else {
            if ($this->type == 'bank-transfer') {
              if ($type == 'salary') {
                $modeforaddpayment = 'payment_salary';
                $labelforaddpayment = '(SalaryPayment)';
                $addbankurl = 'credit-transfer';
              } else {
                $modeforaddpayment = 'payment_supplier';
                $labelforaddpayment = '(SupplierInvoicePayment)';
                $addbankurl = 'credit-transfer';
              }
            } else {
              $modeforaddpayment = 'payment';
              $labelforaddpayment = '(CustomerInvoicePayment)';
              $addbankurl = 'direct-debit'; // = 'directdebit'
            }
 
 
            if ($paiement instanceof PaymentSalary) {
              // Only 6 arguments for PaymentSalary
              $result = $paiement->addPaymentToBank($user, $modeforaddpayment, $labelforaddpayment, $fk_bank_account, '', '');
            } else {
              $result = $paiement->addPaymentToBank($user, $modeforaddpayment, $labelforaddpayment, $fk_bank_account, '', '', 0, '', $addbankurl);
            }
 
            if ($result < 0) {
              $error++;
              $this->error = $paiement->error;
              $this->errors = $paiement->errors;
              dol_syslog(get_class($this) . "::set_infocredit AddPaymentToBank Error " . $this->error);
            }
          }
        }
 
        // Update withdrawal line
        // TODO: Translate to ligneprelevement.class.php
        if (!$error) {
          $sql = " UPDATE " . MAIN_DB_PREFIX . "prelevement_lignes";
          $sql .= " SET statut = 2";
          $sql .= " WHERE fk_prelevement_bons = " . ((int) $this->id);
 
          if (!$this->db->query($sql)) {
            dol_syslog(get_class($this) . "::set_infocredit Update lines Error");
            $error++;
          }
        }
      } else {
        $this->error = $this->db->lasterror();
        dol_syslog(get_class($this) . "::set_infocredit Update Bons Error");
        $error++;
      }
 
      // End of procedure
      if ($error == 0) {
        $this->date_credit = $date;   // date credit or debit
        $this->statut = self::STATUS_CREDITED;
        $this->status = self::STATUS_CREDITED;
 
        $this->db->commit();
        return 0;
      } else {
        $this->db->rollback();
        return -1;
      }
    } else {
      return -1026;
    }
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function set_infotrans($user, $date, $method)
  {
    // phpcs:enable
    global $conf, $langs;
 
    $error = 0;
 
    dol_syslog(get_class($this) . "::set_infotrans Start", LOG_INFO);
 
    if ($this->db->begin()) {
      $sql = "UPDATE " . MAIN_DB_PREFIX . "prelevement_bons ";
      $sql .= " SET fk_user_trans = " . ((int) $user->id);
      $sql .= " , date_trans = '" . $this->db->idate($date) . "'";
      $sql .= " , method_trans = " . ((int) $method);
      $sql .= " , statut = " . self::STATUS_TRANSFERED;
      $sql .= " WHERE rowid = " . ((int) $this->id);
      $sql .= " AND entity = " . ((int) $conf->entity);
      $sql .= " AND statut = " . self::STATUS_DRAFT;
 
      if ($this->db->query($sql)) {
        $this->method_trans = $method;
        $langs->load('withdrawals');
        $subject = $langs->trans("InfoTransSubject", $this->ref);
        $message = $langs->trans("InfoTransMessage", $this->ref, dolGetFirstLastname($user->firstname, $user->lastname));
        $message .= $langs->trans("InfoTransData", price($this->amount), $this->methodes_trans[$this->method_trans], dol_print_date($date, 'day'));
 
        // TODO Call trigger to create a notification using notification module
      } else {
        $error++;
      }
 
      if ($error == 0) {
        $this->date_trans = $date;
        $this->statut = self::STATUS_TRANSFERED;
        $this->status = self::STATUS_TRANSFERED;
        $this->user_trans = $user->id;
 
        $this->db->commit();
 
        return 0;
      } else {
        $this->db->rollback();
        dol_syslog(get_class($this) . "::set_infotrans ROLLBACK", LOG_ERR);
 
        return -1;
      }
    } else {
      dol_syslog(get_class($this) . "::set_infotrans Ouverture transaction SQL impossible", LOG_CRIT);
      return -2;
    }
  }
 
  private function getListInvoices($amounts = 0, $type = '')
  {
    global $conf;
 
    $arr = array();
 
    dol_syslog(get_class($this) . "::getListInvoices");
 
    // Returns all invoices presented within same order
    $sql = "SELECT ";
    if ($this->type == 'bank-transfer') {
      if ($type == 'salary') {
        $sql .= " p.fk_salary";
      } else {
        $sql .= " p.fk_facture_fourn";
      }
    } else {
      $sql .= " p.fk_facture";
    }
    if ($amounts) {
      $sql .= ", SUM(pl.amount)";
    }
    $sql .= " FROM " . MAIN_DB_PREFIX . "prelevement_bons as pb,";
    $sql .= " " . MAIN_DB_PREFIX . "prelevement_lignes as pl,";
    $sql .= " " . MAIN_DB_PREFIX . "prelevement as p";
    $sql .= " WHERE p.fk_prelevement_lignes = pl.rowid";
    $sql .= " AND pl.fk_prelevement_bons = pb.rowid";
    $sql .= " AND pb.rowid = " . ((int) $this->id);
    $sql .= " AND pb.entity = " . ((int) $conf->entity);
    if ($amounts) {
      if ($this->type == 'bank-transfer') {
        if ($type == 'salary') {
          $sql .= " GROUP BY p.fk_salary";
        } else {
          $sql .= " GROUP BY p.fk_facture_fourn";
        }
      } else {
        $sql .= " GROUP BY p.fk_facture";
      }
    }
 
    $resql = $this->db->query($sql);
    if ($resql) {
      $num = $this->db->num_rows($resql);
 
      if ($num) {
        $i = 0;
        while ($i < $num) {
          $row = $this->db->fetch_row($resql);
          if (!$amounts) {
            $arr[$i] = $row[0];
          } else {
            $arr[$i] = array(
              $row[0],
              $row[1]
            );
          }
          $i++;
        }
      }
      $this->db->free($resql);
    } else {
      $this->error = $this->db->lasterror();
    }
 
    return $arr;
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function SommeAPrelever($mode = 'direct-debit', $type = '')
  {
    // phpcs:enable
    $sql = "SELECT sum(pd.amount) as nb";
    if ($type !== 'salary') {
      if ($mode != 'bank-transfer') {
        $sql .= " FROM " . MAIN_DB_PREFIX . "facture as f,";
      } else {
        $sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn as f,";
      }
    } else {
      $sql .= " FROM " . MAIN_DB_PREFIX . "salary as s,";
    }
    $sql .= " " . MAIN_DB_PREFIX . "prelevement_demande as pd";
    $sql .= ($type !== 'salary' ? " WHERE f.entity IN (" . getEntity('invoice') . ")" : " WHERE s.entity IN (" . getEntity('salary') . ")");
    if (!getDolGlobalString('WITHDRAWAL_ALLOW_ANY_INVOICE_STATUS')) {
      $sql .= ($type !== 'salary' ? " AND f.fk_statut = " . Facture::STATUS_VALIDATED : " AND s.paye = " . Salary::STATUS_UNPAID);
    }
    if ($type !== 'salary') {
      if ($mode != 'bank-transfer') {
        $sql .= " AND f.rowid = pd.fk_facture";
      } else {
        $sql .= " AND f.rowid = pd.fk_facture_fourn";
      }
    } else {
      $sql .= " AND s.rowid = pd.fk_salary";
    }
    $sql .= ($type !== 'salary' ? " AND f.paye = 0" : "");
    $sql .= " AND pd.traite = 0";
    $sql .= " AND pd.ext_payment_id IS NULL";
    $sql .= ($type !== 'salary' ? " AND f.total_ttc > 0" : "");
 
    $resql = $this->db->query($sql);
    if ($resql) {
      $obj = $this->db->fetch_object($resql);
 
      $this->db->free($resql);
 
      return $obj->nb;
    } else {
      $error = 1;
      dol_syslog(get_class($this) . "::SommeAPrelever Erreur -1");
      dol_syslog($this->db->error());
 
      return -1;
    }
  }
 
  public function nbOfInvoiceToPay($mode = 'direct-debit', $type = '')
  {
    if ($type === 'salary') {
      return $this->NbFactureAPrelever($mode, 1);
    } else {
      return $this->NbFactureAPrelever($mode);
    }
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function NbFactureAPrelever($type = 'direct-debit', $forsalary = 0)
  {
    // phpcs:enable
    if ($forsalary == 1) {
      $sql = "SELECT count(s.rowid) as nb";
      $sql .= " FROM " . MAIN_DB_PREFIX . "salary as s";
    } else {
      $sql = "SELECT count(f.rowid) as nb";
 
      if ($type == 'bank-transfer') {
        $sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn as f";
      } else {
        $sql .= " FROM " . MAIN_DB_PREFIX . "facture as f";
      }
    }
    $sql .= ", " . MAIN_DB_PREFIX . "prelevement_demande as pd";
    if ($forsalary == 1) {
      $sql .= " WHERE s.entity IN (" . getEntity('invoice') . ")";
      if (!getDolGlobalString('WITHDRAWAL_ALLOW_ANY_INVOICE_STATUS')) {
        $sql .= " AND s.paye = 0";
      }
    } else {
      $sql .= " WHERE f.entity IN (" . getEntity('invoice') . ")";
      if (!getDolGlobalString('WITHDRAWAL_ALLOW_ANY_INVOICE_STATUS')) {
        $sql .= " AND f.fk_statut = " . Facture::STATUS_VALIDATED;
      }
    }
    if ($forsalary == 1) {
      $sql .= " AND s.rowid = pd.fk_salary";
    } else {
      if ($type == 'bank-transfer') {
        $sql .= " AND f.rowid = pd.fk_facture_fourn";
      } else {
        $sql .= " AND f.rowid = pd.fk_facture";
      }
    }
    $sql .= " AND pd.traite = 0";
    $sql .= " AND pd.ext_payment_id IS NULL";
    if (!$forsalary == 1) {
      $sql .= " AND f.total_ttc > 0";
    } else {
      $sql .= " AND s.paye = 0";
    }
 
    dol_syslog(get_class($this) . "::NbFactureAPrelever");
    $resql = $this->db->query($sql);
 
    if ($resql) {
      $obj = $this->db->fetch_object($resql);
      $this->db->free($resql);
 
      return $obj->nb;
    } else {
      $this->error = get_class($this) . "::NbFactureAPrelever Erreur -1 sql=" . $this->db->error();
      return -1;
    }
  }
 
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function create($banque = '', $agence = '', $mode = 'real', $format = 'FRST', $executiondate = 0, $notrigger = 0, $type = 'direct-debit', $dids = 0, $fk_bank_account = 0, $sourcetype = 'invoice')
  {
    // phpcs:enable
    global $conf, $langs, $user;
 
    dol_syslog(__METHOD__ . " mode=".$mode." format=".$format." type=".$type." dids=".(is_array($dids) ? implode(',', $dids) : $dids)." fk_bank_account=".$fk_bank_account." sourcetype=".$sourcetype, LOG_DEBUG);
 
    require_once DOL_DOCUMENT_ROOT . "/compta/facture/class/facture.class.php";
    require_once DOL_DOCUMENT_ROOT . "/societe/class/societe.class.php";
 
    // Check params
    if ($type != 'bank-transfer') {
      $format = strtoupper($format);
      if (empty($format)) {
        $this->error = 'ErrorBadParametersForDirectDebitFileCreate';
        return -1;
      }
      if ($mode === 'real' && !in_array($format, array('FRST', 'RCUR', 'OOFF', 'FNAL'), true)) {
        $this->error = 'ErrorBadParametersForDirectDebitFileCreate';
        return -1;
      }
    }
 
    if (!is_int($dids) && !is_array($dids)) {
      $this->error = 'ErrorBadParametersForDirectDebitFileCreateDids';
      return -1;
    }
 
    // Clean params
    if (empty($fk_bank_account)) {
      $fk_bank_account = ($type == 'bank-transfer' ? getDolGlobalInt('PAYMENTBYBANKTRANSFER_ID_BANKACCOUNT') : getDolGlobalInt('PRELEVEMENT_ID_BANKACCOUNT'));
    }
    if (is_int($dids)) {
      $dids = array($dids);
    }
 
    $error = 0;
    // Pre-store some values into variables to simplify following sql requests
    if ($sourcetype != 'salary') {
      $entity = $type != 'bank-transfer' ? getEntity('invoice', 1) : getEntity('supplier_invoice', 1);  // Return entity
      $sqlTable = $type != 'bank-transfer' ? "facture" : "facture_fourn";
      $socOrUser = 'fk_soc';
      $societeOrUser = 'societe';
    } else {
      $entity = getEntity('salary', 1);   // Return entity
      $sqlTable = 'salary';
      $socOrUser = 'fk_user';
      $societeOrUser = 'user';
    }
 
    /*
    $thirdpartyBANId = 0;
 
    // Check if there is an iban associated to the bank transfer request or if we take the default
    if ($dids !== [0] && !empty($dids)) {
      $sql = "SELECT pd.fk_societe_rib";
      $sql .= " FROM " . $this->db->prefix() . "prelevement_demande as pd";
      $sql .= " WHERE pd.rowid IN (".$this->db->sanitize(implode(',', $dids)).")";
 
      $resql = $this->db->query($sql);
 
      if (!$resql) {
        $this->error = $this->db->lasterror();
        dol_syslog(__METHOD__ . " Read fk_societe_rib error " . $this->db->lasterror(), LOG_ERR);
        return -1;
      }
 
      $obj = $this->db->fetch_object($resql);
      if ($obj) {
        $thirdpartyBANId = $obj->fk_societe_rib;
 
        dol_syslog(__METHOD__ . " Found a BAN ID to use: ".$thirdpartyBANId);
      }
 
      $this->db->free($resql);
    }
    */
 
    $datetimeprev = dol_now('gmt');
    // Choice of the date of the execution direct debit
    if (!empty($executiondate)) {
      $datetimeprev = $executiondate;
    }
 
    $month = dol_print_date($datetimeprev, "%m", 'gmt');
    $year = dol_print_date($datetimeprev, "%Y", 'gmt');
 
    $this->invoice_in_error = array();
    $this->thirdparty_in_error = array();
 
    // Get all invoices to process into $factures
    $factures = array();      // TODO Rename $factures... into $payment_requests...
    $factures_prev = array();
    $factures_prev_id = array();
 
    dol_syslog(__METHOD__ . " Read invoices/salaries for dids=" . implode(', ', $dids), LOG_DEBUG);
 
    $sql = "SELECT f.rowid, pd.rowid as pfdrowid";
    $sql .= ", f.".$this->db->sanitize($socOrUser);   // fk_soc or fk_user
    $sql .= ", pd.code_banque, pd.code_guichet, pd.number, pd.cle_rib";
    $sql .= ", pd.amount";
    if ($sourcetype != 'salary') {
      $sql .= ", s.nom as name";
      $sql .= ", f.ref";
      $sql .= ", sr.bic, sr.iban_prefix, sr.frstrecur, sr.default_rib, sr.rum";
    } else {
      $sql .= ", CONCAT(s.firstname, ' ', s.lastname) as name";
      $sql .= ", f.ref";
      $sql .= ", sr.bic, sr.iban_prefix, 'FRST' as frstrecur, sr.default_rib, '' as rum";
    }
    $sql .= ", pd.fk_societe_rib as soc_rib_id";    // Forced payment IBAN to use in priority
    $sql .= " FROM " . $this->db->prefix() . $sqlTable . " as f"; // f is salary, facture or facture_fourn
    $sql .= " INNER JOIN " . $this->db->prefix() . "prelevement_demande as pd ON f.rowid = pd.fk_".$this->db->sanitize($sqlTable);
    $sql .= " LEFT JOIN " . $this->db->prefix() . $this->db->sanitize($societeOrUser)." as s ON s.rowid = f.".$this->db->sanitize($socOrUser);
    $sql .= " LEFT JOIN " . $this->db->prefix() . $this->db->sanitize($societeOrUser."_rib")." as sr ON s.rowid = sr.".$this->db->sanitize($socOrUser); // To get the default BAN of thirdparty
    if ($sourcetype != 'salary') {
      $sql .= " AND sr.default_rib = 1";
      $sql .= " AND sr.type = 'ban'";
    } else {
      //$sql .= " AND sr.type = 'ban'";   // TODO Add AND sr.type = 'ban' for users too
      // TODO Add 'AND sr.default_rib = 1' in sourcetype salary too
      // Note: the column has been created in v21 in llx_user_rib and default to 0
      // If we add a test on sr.default_rib = 1, we must also check we have a correct error management to stop if no default BAN is found.
      // Also it may be found for on thirdparty and not for the other.
    }
    $sql .= " WHERE f.entity IN (".((int) $entity).')';
    if ($sourcetype != 'salary') {
      $sql .= " AND f.fk_statut = ".Facture::STATUS_VALIDATED; // Invoice validated
      $sql .= " AND f.paye = 0";
      $sql .= " AND f.total_ttc > 0";
      /*if ($socid > 0) {
        $sql .= " AND f.fk_soc = ".((int) $socid);
      }*/
    } else {
      //$sql .= " AND f.fk_statut = 1"; // Invoice validated
      $sql .= " AND f.paye = 0";
      $sql .= " AND f.amount > 0";
    }
    $sql .= " AND pd.traite = 0";
    $sql .= " AND pd.ext_payment_id IS NULL";
    if ($dids !== [0] && !empty($dids)) {
      $sql .= " AND pd.rowid IN (".$this->db->sanitize(implode(',', $dids)).")";
    }
 
    $resql = $this->db->query($sql);
    if ($resql) {
      $num = $this->db->num_rows($resql);
      $i = 0;
 
      while ($i < $num) {
        $row = $this->db->fetch_row($resql);  // TODO Replace with fetch_object()
        '@phan-var-force array<int<0,12>,string> $row';
        // All fields:
        // 0=invoice or salary id, 1=pfdrowid (id of payment request), 2=$socOrUser, 3=code_banque, 4=code_guichet, 5=number, 6=key,
        // 7=amount, 8=company name, 9=invoice ref,
        // 10=default bic, 11=default iban (=the BAN to use if no soc_rib_id set), 12=default frstrecur, 13=default_rib, 14=default rum, 15=soc_rib_id (=the BAN to use in priority)
        $factures[$i] = $row;
 
        // Decode default BAN
        $factures[$i][11] = dolDecrypt($factures[$i][11]);
 
        if ($row[7] == 0) {
          $error++;
          dol_syslog(__METHOD__ . " Read invoices/salaries error Found a null amount", LOG_WARNING);
          $this->invoice_in_error[$row[0]] = "Error for invoice or salary id " . $row[0] . ", found a null amount";
          break;
        }
        $i++;
      }
 
      $this->db->free($resql);
      dol_syslog(__METHOD__ . " Read invoices/salaries, " . $i . " invoices/salaries to withdraw", LOG_DEBUG);
    } else {
      $this->error = $this->db->lasterror();
      dol_syslog(__METHOD__ . " Read invoices/salaries error " . $this->db->lasterror(), LOG_ERR);
      return -1;
    }
 
    if (!$error) {
      // Make some checks
      require_once DOL_DOCUMENT_ROOT . '/societe/class/societe.class.php';
      require_once DOL_DOCUMENT_ROOT . '/user/class/user.class.php';
      require_once DOL_DOCUMENT_ROOT . '/societe/class/companybankaccount.class.php';
      require_once DOL_DOCUMENT_ROOT . '/core/lib/bank.lib.php';
 
      $tmpsoc = new Societe($this->db);
      $tmpuser = new User($this->db);
 
      // Check BAN
      $i = 0;
      dol_syslog(__METHOD__ . " Check BAN for each invoices or salaries", LOG_DEBUG);
 
      if (count($factures) > 0) {
        foreach ($factures as $key => $fac) {
          //var_dump($type, $format, $fac[8], $fac[12]);
 
          // Check if $fac[8] s.nom is null
          if ($fac[8] != null) {
            if ($type != 'bank-transfer') {
              if ($format == 'FRST' && $fac[12] && $fac[12] != 'FRST') {
                continue;
              }
              if ($format == 'RCUR' && $fac[12] && $fac[12] != 'RCUR') {
                continue;
              }
            }
 
            // If a bank account was forced on llx_prelevement_demande (the direct debit or credit transfer request),
            // we must use this one, so we reload values from soc_rib_id
            if (!empty($fac[15])) {
              $bankaccount = new CompanyBankAccount($this->db);
              $bankaccount->fetch((int) $fac[15]);
              if ($bankaccount->id > 0) {
                $fac[3] = $bankaccount->code_banque;
                $fac[4] = $bankaccount->code_guichet;
                $fac[5] = $bankaccount->number;
                $fac[6] = $bankaccount->cle_rib;
                $fac[10] = $bankaccount->bic;
                $fac[11] = $bankaccount->iban;
                $fac[14] = $bankaccount->rum;
                //var_dump('fetch id='.(int) $fac[14].' => '.$fac[3].'-'.$fac[4].'-'.$fac[5].'-'.$fac[6].'-'.$fac[10]."-".$fac[11]."-".$fac[14]);
              }
            }
 
            $bicinloop = $fac[10];
            $baninloop = $fac[11];
 
            $verif = checkSwiftForAccount(null, $bicinloop);
 
            if ($verif || (empty($fac[10]) && getDolGlobalInt("WITHDRAWAL_WITHOUT_BIC"))) {
              dol_syslog(__METHOD__." now call checkIbanForAccount(null, ".$baninloop.")");
              $verif = checkIbanForAccount(null, $baninloop);
            }
 
            if ($verif) {
              $factures_prev[$i] = $fac;
              /* second array necessary for BonPrelevement */
              $factures_prev_id[$i] = $fac[0];
              $i++;
              //dol_syslog(__METHOD__."::RIB is ok", LOG_DEBUG);
            } else {
              if ($type != 'bank-transfer') {
                $tmpsoc->id = (int) $fac[2];
                $tmpsoc->name = $fac[8];
                $invoice_url = "<a href='" . DOL_URL_ROOT . '/compta/facture/card.php?facid=' . $fac[0] . "'>" . $fac[9] . "</a>";
                $this->invoice_in_error[$fac[0]] = "Error incomplete properties of the default bank number ".$baninloop." IBAN/BIC for invoice " . $invoice_url . " for thirdparty " . $tmpsoc->getNomUrl(0);
                $this->thirdparty_in_error[$tmpsoc->id] = "Error on default bank number IBAN/BIC for invoice " . $invoice_url . " for thirdparty " . $tmpsoc->getNomUrl(0);
                $error++;
              }
              if ($type == 'bank-transfer' && $sourcetype != 'salary') {
                $tmpsoc->id = (int) $fac[2];
                $tmpsoc->name = $fac[8];
                $invoice_url = "<a href='" . DOL_URL_ROOT . '/fourn/facture/card.php?facid=' . $fac[0] . "'>" . $fac[9] . "</a>";
                $this->invoice_in_error[$fac[0]] = "Error incomplete properties of the default bank number ".$baninloop." IBAN/BIC for invoice " . $invoice_url . " for thirdparty " . $tmpsoc->getNomUrl(0);
                $this->thirdparty_in_error[$tmpsoc->id] = "Error on default bank number IBAN/BIC for invoice " . $invoice_url . " for thirdparty " . $tmpsoc->getNomUrl(0);
                $error++;
              }
              if ($type == 'bank-transfer' && $sourcetype == 'salary') {
                $tmpuser->id = (int) $fac[2];
                $tmpuser->firstname = $fac[8];
                $salary_url = "<a href='" . DOL_URL_ROOT . '/salaries/card.php?id=' . $fac[0] . "'>" . $fac[0] . "</a>";
                $this->invoice_in_error[$fac[0]] = "Error incomplete properties of the default bank number ".$baninloop." IBAN/BIC for salary " . $salary_url . " for employee " . $tmpuser->getNomUrl(0);
                $this->thirdparty_in_error[$tmpuser->id] = "Error on default bank number IBAN/BIC for salary " . $salary_url . " for employee " . $tmpuser->getNomUrl(0);
                $error++;
              }
              dol_syslog(__METHOD__ . " Check BAN Error on default bank number IBAN/BIC reported by verif(): " . implode(', ', $fac), LOG_WARNING);
            }
          } else {
            dol_syslog(__METHOD__ . " Check BAN Failed to read company", LOG_WARNING);
          }
          /*
          } else {
            dol_syslog(__METHOD__." Check BAN Failed to read invoice", LOG_WARNING);
          }
          */
        }
      } else {
        dol_syslog(__METHOD__ . " Check BAN No invoice to process", LOG_WARNING);
      }
    }
 
    $ok = 0;
 
    // Withdraw invoices in factures_prev array
    $out = count($factures_prev) . " invoices or salaries will be included.";
    dol_syslog($out);
 
    //var_dump($factures_prev, $this->invoice_in_error, $this->thirdparty_in_error);exit;
 
    if (count($factures_prev) > 0) {
      if ($mode == 'real') {
        $ok = 1;
      } else {
        print $langs->trans("ModeWarning"); // "Option for real mode was not set, we stop after this simulation\n";
      }
    }
    if ($ok) {
      /*
       * We are in real mode.
       * We create order and build file into disk
       */
      $this->db->begin();
 
      $now = dol_now();
      $ref = '';
 
      /*
       * Process order generation
       */
      if (!$error) {
        $ref = substr($year, -2) . $month;
 
        $prefixt = "T";
 
        // Get next free number for the ref of bon prelevement
        $sql = "SELECT SUBSTRING(ref, 6) AS refnumber";   // To suppress "TYYMM" from "TYYMMXXX"
        $sql .= " FROM " . MAIN_DB_PREFIX . "prelevement_bons";
        $sql .= " WHERE ref LIKE '" . $this->db->escape($prefixt . $ref) . "%'";
        $sql .= " AND entity = " . ((int) $conf->entity);
        $sql .= " ORDER BY LENGTH(ref) DESC, ref DESC";
        $sql .= " LIMIT 1";
 
        dol_syslog(get_class($this) . " get next free number", LOG_DEBUG);
 
        $resql = $this->db->query($sql);
 
        if ($resql) {
          $row = $this->db->fetch_row($resql);
 
          // Build the new ref
          $ref = $prefixt . $ref . sprintf("%03d", (intval($row[0] ?? 0) + 1));
 
          // $conf->abc->dir_output may be:
          // /home/ldestailleur/git/dolibarr_15.0/documents/abc/
          // or
          // /home/ldestailleur/git/dolibarr_15.0/documents/X/abc with X >= 2 with multicompany.
          if ($type != 'bank-transfer') {
            $dir = $conf->prelevement->dir_output . '/receipts';
          } else {
            $dir = $conf->paymentbybanktransfer->dir_output . '/receipts';
          }
          if (!is_dir($dir)) {
            dol_mkdir($dir);
          }
 
          if (isModEnabled('multicompany')) {
            $labelentity = $conf->entity;
            $this->filename = $dir . '/' . $ref . '-' . $labelentity . '.xml';
          } else {
            $this->filename = $dir . '/' . $ref . '.xml';
          }
 
          // Create withdraw order in database
          $sql = "INSERT INTO " . MAIN_DB_PREFIX . "prelevement_bons (";
          $sql .= "ref, entity, datec, type, fk_bank_account";
          $sql .= ") VALUES (";
          $sql .= "'" . $this->db->escape($ref) . "'";
          $sql .= ", " . ((int) $conf->entity);
          $sql .= ", '" . $this->db->idate($now) . "'";
          $sql .= ", '" . ($type == 'bank-transfer' ? 'bank-transfer' : 'debit-order') . "'";
          $sql .= ", " . ((int) $fk_bank_account);
          $sql .= ")";
 
          $resql = $this->db->query($sql);
 
          if ($resql) {
            $prev_id = $this->db->last_insert_id(MAIN_DB_PREFIX . "prelevement_bons");
            $this->id = $prev_id;
            $this->ref = $ref;
          } else {
            $error++;
            $this->errors[] = $this->db->lasterror();
            dol_syslog(__METHOD__ . " Create withdraw receipt " . $this->db->lasterror(), LOG_ERR);
          }
        } else {
          $error++;
          $this->errors[] = $this->db->lasterror();
          dol_syslog(__METHOD__ . " Get last withdraw receipt " . $this->db->lasterror(), LOG_ERR);
        }
      }
 
      if (!$error) {
        dol_syslog(__METHOD__ . " Now loop on each document to insert them in llx_prelevement_demande");
 
        // Add lines for the direct debit or crdit transfer
        if (count($factures_prev) > 0) {
          foreach ($factures_prev as $fac) {  // Add a link in database for each invoice or salary
            /*
             * Add standing order. This add record into llx_prelevement_lignes and llx_prelevement
             * The llx_prelevement_ligne will then be read to build the XML SEPA file.
             *
             * $fac[0] : invoice or salary id
             * $fac[1] : id of payment request (rowid in llx_prelevement_demande)
             * $fac[2] : third party id
             * $fac[3] : banque
             * $fac[4] : guichet
             * $fac[5] : number
             * $fac[6] : cle rib
             * $fac[7] : amount
             * $fac[8] : client nom
             * $fac[9] : Invoice ref
             * $fac[10] : BIC
             * $fac[11] : IBAN
             * $fac[12] : frstrcur
             * $fac[13] : default_rib (0 or 1)
             * $fac[14] : rum
             * $fac[15] : soc_rib_id (id bank account preselected for direct debit or credit transfer)
             */
            $ri = $this->addWithdrawDetail(
              $fac[0],
              $fac[2],
              $fac[8],
              $fac[7],
              $fac[3],
              $fac[4],
              $fac[5],
              $fac[6],
              $type,
              $sourcetype,
              $fac[10],
              $fac[11],
              $fac[14],
              $fac[1]
            );
 
            if ($ri != 0) {
              $error++;
            }
 
            // Update invoice requests as done
            $sql = "UPDATE " . MAIN_DB_PREFIX . "prelevement_demande";
            $sql .= " SET traite = 1";
            $sql .= ", date_traite = '" . $this->db->idate($now) . "'";
            $sql .= ", fk_prelevement_bons = " . ((int) $this->id);
            $sql .= " WHERE rowid = " . ((int) $fac[1]);
 
            $resql = $this->db->query($sql);
            if (!$resql) {
              $error++;
              $this->errors[] = $this->db->lasterror();
              dol_syslog(__METHOD__ . " Update Error=" . $this->db->lasterror(), LOG_ERR);
            }
          }
        }
      }
 
      if (!$error) {
        /*
         * Create file of type='direct-debit' for direct debit order or type='bank-transfer' for credit transfer into a XML file
         */
 
        dol_syslog(__METHOD__ . " Init direct debit or credit transfer file for " . count($factures_prev) . " invoices", LOG_DEBUG);
 
        if (count($factures_prev) > 0) {
          $this->date_echeance = $datetimeprev;
          $this->reference_remise = $ref;
 
          $account = new Account($this->db);
          if ($account->fetch($fk_bank_account) > 0) {
            $this->emetteur_code_banque        = $account->code_banque;
            $this->emetteur_code_guichet       = $account->code_guichet;
            $this->emetteur_numero_compte      = $account->number;
            $this->emetteur_number_key         = $account->cle_rib;
            $this->sepa_xml_pti_in_ctti        = (bool) $account->pti_in_ctti;
            $this->emetteur_iban               = $account->iban;
            $this->emetteur_bic                = $account->bic;
 
            $this->emetteur_ics = (($type == 'bank-transfer' && getDolGlobalString("SEPA_USE_IDS")) ? $account->ics_transfer : $account->ics);  // Example "FR78ZZZ123456"
 
            $this->raison_sociale = $account->owner_name;
          }
          $this->factures = $factures_prev_id;
          $this->context['factures_prev'] = $factures_prev;
          // Generation of direct debit or credit transfer file $this->filename (May be a SEPA file for european countries)
          // This also set the property $this->total with amount that is included into file
          $userid = 0;
          if ($sourcetype == 'salary') {
            $userid = $this->context['factures_prev'][0][2];
          }
          $result = $this->generate($format, $executiondate, $type, $fk_bank_account, $userid, 0);
          if ($result < 0) {
            //var_dump($this->error);
            //var_dump($this->invoice_in_error);
            $error++;
          }
        }
        dol_syslog(__METHOD__ . " Bank order file has been generated under filename " . $this->filename, LOG_DEBUG);
      }
 
 
      /*
       * Update total defined after generation of file
       */
      if (!$error) {
        $sql = "UPDATE " . MAIN_DB_PREFIX . "prelevement_bons";
        $sql .= " SET amount = " . price2num($this->total);
        $sql .= " WHERE rowid = " . ((int) $this->id);
        $sql .= " AND entity = " . ((int) $conf->entity);
        $resql = $this->db->query($sql);
 
        if (!$resql) {
          $error++;
          dol_syslog(__METHOD__ . " Error update total: " . $this->db->error(), LOG_ERR);
        }
      }
 
      if (!$error && !$notrigger) {
        $triggerName = ($type == 'bank-transfer') ? 'CREDIT_TRANSFER_ORDER_CREATE' : 'DIRECT_DEBIT_ORDER_CREATE';
        $this->amount = $this->total;
 
        // Call trigger
        $result = $this->call_trigger($triggerName, $user);
        if ($result < 0) {
          $error++;
        }
        // End call triggers
      }
 
      if (!$error) {
        $this->db->commit();
        return count($factures_prev); // The error of failed lines are into $this->invoice_in_error and $this->thirdparty_in_error
      } else {
        $this->db->rollback();
        return -1;
      }
    } else {
      return 0;
    }
  }
 
 
  public function delete($user, $notrigger = 0)
  {
    $this->db->begin();
 
    $error = 0;
    $resql1 = $resql2 = $resql3 = $resql4 = 0;
 
    if (!$notrigger) {
      $triggername = 'DIRECT_DEBIT_ORDER_DELETE';
      if ($this->type == 'bank-transfer') {
        $triggername = 'PAYMENTBYBANKTRANFER_DELETE';
      }
      // Call trigger
      $result = $this->call_trigger($triggername, $user);
      if ($result < 0) {
        $error++;
      }
      // End call triggers
    }
 
    if (!$error) {
      $sql = "DELETE FROM " . MAIN_DB_PREFIX . "prelevement WHERE fk_prelevement_lignes IN (SELECT rowid FROM " . MAIN_DB_PREFIX . "prelevement_lignes WHERE fk_prelevement_bons = " . ((int) $this->id) . ")";
      $resql1 = $this->db->query($sql);
      if (!$resql1) {
        dol_print_error($this->db);
      }
    }
 
    if (!$error) {
      $sql = "DELETE FROM " . MAIN_DB_PREFIX . "prelevement_lignes WHERE fk_prelevement_bons = " . ((int) $this->id);
      $resql2 = $this->db->query($sql);
      if (!$resql2) {
        dol_print_error($this->db);
      }
    }
 
    if (!$error) {
      $sql = "DELETE FROM " . MAIN_DB_PREFIX . "prelevement_bons WHERE rowid = " . ((int) $this->id);
      $resql3 = $this->db->query($sql);
      if (!$resql3) {
        dol_print_error($this->db);
      }
    }
 
    if (!$error) {
      $sql = "UPDATE " . MAIN_DB_PREFIX . "prelevement_demande SET fk_prelevement_bons = NULL, traite = 0 WHERE fk_prelevement_bons = " . ((int) $this->id);
      $resql4 = $this->db->query($sql);
      if (!$resql4) {
        dol_print_error($this->db);
      }
    }
 
    if ($resql1 && $resql2 && $resql3 && $resql4 && !$error) {
      $this->db->commit();
      return 1;
    } else {
      $this->db->rollback();
      return -1;
    }
  }
 
 
  public function getNomUrl($withpicto = 0, $option = '', $notooltip = 0, $morecss = '', $save_lastsearch_value = -1)
  {
    global $conf, $langs, $hookmanager;
 
    if (!empty($conf->dol_no_mouse_hover)) {
      $notooltip = 1; // Force disable tooltips
    }
 
    $result = '';
 
    $labeltoshow = 'PaymentByDirectDebit';
    $labelforclosedate = 'CreditDate';
    if (!empty($this->type) && $this->type == 'bank-transfer') {
      $labeltoshow = 'PaymentByBankTransfer';
      $labelforclosedate = 'ClosedOn';
    }
 
    $label = img_picto('', $this->picto) . ' <u>' . $langs->trans($labeltoshow) . '</u> ' . $this->getLibStatut(5);
    $label .= '<br>';
    $label .= '<b>' . $langs->trans('Ref') . ':</b> ' . $this->ref;
    if (isset($this->amount)) {
      $label .= '<br><b>' . $langs->trans("Amount") . ':</b> <span class="amount">' . price($this->amount).'</span>';
    }
    if (isset($this->date_creation)) {
      $label .= '<br><b>'.$langs->trans("DateCreation").":</b> ".dol_print_date($this->date_creation, 'dayhour', 'tzuserrel');
    }
    if (isset($this->date_trans)) {
      $label .= '<br><b>' . $langs->trans("TransData") . ":</b> " . dol_print_date($this->date_trans, 'dayhour', 'tzuserrel');
    }
    if (isset($this->date_credit)) {
      $label .= '<br><b>'.$langs->trans($labelforclosedate).":</b> ".dol_print_date($this->date_credit, 'dayhour', 'tzuserrel');
    }
 
    $url = DOL_URL_ROOT . '/compta/prelevement/card.php?id=' . $this->id;
    if (!empty($this->type) && $this->type == 'bank-transfer') {
      $url = DOL_URL_ROOT . '/compta/prelevement/card.php?id=' . $this->id;
    }
 
    if ($option != 'nolink') {
      // Add param to save lastsearch_values or not
      $add_save_lastsearch_values = ($save_lastsearch_value == 1 ? 1 : 0);
      if ($save_lastsearch_value == -1 && isset($_SERVER["PHP_SELF"]) && preg_match('/list\.php/', $_SERVER["PHP_SELF"])) {
        $add_save_lastsearch_values = 1;
      }
      if ($add_save_lastsearch_values) {
        $url .= '&save_lastsearch_values=1';
      }
    }
 
    $linkclose = '';
    if (empty($notooltip)) {
      if (getDolGlobalString('MAIN_OPTIMIZEFORTEXTBROWSER')) {
        $label = $langs->trans("ShowMyObject");
        $linkclose .= ' alt="' . dol_escape_htmltag($label, 1) . '"';
      }
      $linkclose .= ' title="' . dol_escape_htmltag($label, 1) . '"';
      $linkclose .= ' class="classfortooltip' . ($morecss ? ' ' . $morecss : '') . '"';
    } else {
      $linkclose = ($morecss ? ' class="' . $morecss . '"' : '');
    }
 
    $linkstart = '<a href="' . $url . '"';
    $linkstart .= $linkclose . '>';
    $linkend = '</a>';
 
    $result .= $linkstart;
    if ($withpicto) {
      $result .= img_object(($notooltip ? '' : $label), ($this->picto ? $this->picto : 'generic'), ($notooltip ? (($withpicto != 2) ? 'class="paddingright"' : '') : 'class="' . (($withpicto != 2) ? 'paddingright ' : '') . 'classfortooltip"'), 0, 0, $notooltip ? 0 : 1);
    }
    if ($withpicto != 2) {
      $result .= $this->ref;
    }
    $result .= $linkend;
 
    global $action, $hookmanager;
    $hookmanager->initHooks(array('banktransferdao'));
    $parameters = array('id' => $this->id, 'getnomurl' => &$result);
    $reshook = $hookmanager->executeHooks('getNomUrl', $parameters, $this, $action); // Note that $action and $object may have been modified by some hooks
    if ($reshook > 0) {
      $result = $hookmanager->resPrint;
    } else {
      $result .= $hookmanager->resPrint;
    }
 
    return $result;
  }
 
 
  public function deleteNotificationById($rowid)
  {
    $sql = "DELETE FROM " . MAIN_DB_PREFIX . "notify_def";
    $sql .= " WHERE rowid = " . ((int) $rowid);
 
    if ($this->db->query($sql)) {
      return 0;
    } else {
      return -1;
    }
  }
 
  public function deleteNotification($user, $action)
  {
    if (is_object($user)) {
      $userid = $user->id;
    } else {  // If user is an id
      $userid = $user;
    }
 
    $sql = "DELETE FROM " . MAIN_DB_PREFIX . "notify_def";
    $sql .= " WHERE fk_user = " . ((int) $userid) . " AND fk_action = " . ((int) $action);
 
    if ($this->db->query($sql)) {
      return 0;
    } else {
      return -1;
    }
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function addNotification($db, $user, $action)
  {
    // phpcs:enable
    $result = 0;
 
    if (is_object($user)) {
      $userid = $user->id;
    } else {  // If user is an id
      $userid = $user;
    }
 
    if ($this->deleteNotification($user, $action) == 0) {
      $now = dol_now();
 
      $sql = "INSERT INTO " . MAIN_DB_PREFIX . "notify_def (datec, fk_user, fk_soc, fk_contact, fk_action)";
      $sql .= " VALUES ('" . $this->db->idate($now) . "', " . ((int) $userid) . ", NULL, NULL, " . ((int) $action) . ")";
 
      dol_syslog("adnotiff: " . $sql);
      if ($this->db->query($sql)) {
        $result = 0;
      } else {
        $result = -1;
        dol_syslog(get_class($this) . "::addNotification Error $result");
      }
    }
 
    return $result;
  }
 
 
  public function generate(string $format = 'FRST', int $executiondate = 0, string $type = 'direct-debit', int $fk_bank_account = 0, int $forsalary = 0, int $thirdpartyBANId = 0)
  {
    global $conf, $langs, $mysoc;
 
    if ($type !== 'bank-transfer') {
      $format = strtoupper($format);
      if (!in_array($format, array('FRST', 'RCUR', 'OOFF', 'FNAL'), true)) {
        $this->error = 'ErrorBadParametersForDirectDebitFileCreate';
        return -1;
      }
    }
 
    //TODO: Optimize code to read lines in a single function
 
    // Clean params
    if (empty($fk_bank_account)) {
      $fk_bank_account = ($type == 'bank-transfer' ? getDolGlobalInt('PAYMENTBYBANKTRANSFER_ID_BANKACCOUNT') : getDolGlobalInt('PRELEVEMENT_ID_BANKACCOUNT'));
    }
 
    $result = 0;
 
    dol_syslog(get_class($this) . "::generate build file=" . $this->filename . " type=" . $type);
 
    $this->file = fopen($this->filename, "w");
    if ($this->file == false) {
      $this->error = $langs->trans('ErrorFailedToOpenFile', $this->filename);
      return -1;
    }
 
    $found = 0;
    $this->total = 0;
 
    // Build file for European countries
    if ($mysoc->isInSEPA()) {
      $found++;
 
      if ($type != 'bank-transfer') {
        // SEPA Initialisation
        $CrLf = "\n";
 
        $now = dol_now();
 
        $dateTime_ECMA = dol_print_date($now, '%Y-%m-%dT%H:%M:%S');
 
        $date_actu = $now;
        if (!empty($executiondate)) {
          $date_actu = $executiondate;
        }
 
        $dateTime_YMD = dol_print_date($date_actu, '%Y%m%d');
        $dateTime_YMDHMS = dol_print_date($date_actu, '%Y%m%d%H%M%S');
        $fileDebiteurSection = '';
        $fileEmetteurSection = '';
        $i = 0;
 
        /*
         * Section Debitor (sepa Debiteurs block lines)
         */
 
        $sql = "SELECT soc.rowid as socid, soc.code_client as code, soc.address, soc.zip, soc.town, c.code as country_code,";
        //$sql .= " pl.code_banque as cb, pl.code_guichet as cg, pl.number as cc, ";
        $sql .= " pl.client_nom as nom, pl.amount as somme, pl.fk_prelevement_demande,";
        $sql .= " f.ref as reffac,";
        $sql .= " p.fk_facture as idfac, p.rowid as pid,";
        $sql .= " rib.rowid, rib.datec, rib.iban_prefix as iban, rib.bic as bic, rib.rowid as drum, rib.rum, rib.date_rum";
        $sql .= " FROM";
        $sql .= " " . MAIN_DB_PREFIX . "prelevement_lignes as pl,";
        $sql .= " " . MAIN_DB_PREFIX . "facture as f,";
        $sql .= " " . MAIN_DB_PREFIX . "prelevement as p,";
        $sql .= " " . MAIN_DB_PREFIX . "societe as soc,";
        $sql .= " " . MAIN_DB_PREFIX . "c_country as c,";
        $sql .= " " . MAIN_DB_PREFIX . "societe_rib as rib";
        $sql .= " WHERE pl.fk_prelevement_bons = " . ((int) $this->id);
        $sql .= " AND pl.rowid = p.fk_prelevement_lignes";
        $sql .= " AND p.fk_facture = f.rowid";
        $sql .= " AND f.fk_soc = soc.rowid";
        $sql .= " AND soc.fk_pays = c.rowid";
        $sql .= " AND rib.fk_soc = f.fk_soc";
        if (!empty($thirdpartyBANId)) {
          $sql .= " AND rib.rowid = " . ((int) $thirdpartyBANId);
        } else {
          $sql .= " AND rib.default_rib = 1";
        }
        $sql .= " AND rib.type = 'ban'";
 
        // Define $fileDebiteurSection. One section DrctDbtTxInf per invoice.
        $resql = $this->db->query($sql);
        $nbtotalDrctDbtTxInf = -1;
        if ($resql) {
          $cachearraytotestduplicate = array();
 
          $num = $this->db->num_rows($resql);
          while ($i < $num) {
            $obj = $this->db->fetch_object($resql);
 
            if (!empty($cachearraytotestduplicate[$obj->idfac])) {
              $this->error = $langs->trans('ErrorCompanyHasDuplicateDefaultBAN', $obj->socid);
              $this->invoice_in_error[$obj->idfac] = $this->error;
              $result = -2;
              break;
            }
            $cachearraytotestduplicate[$obj->idfac] = $obj->rowid;
 
            // Get the default value
            $daterum = (!empty($obj->date_rum)) ? $this->db->jdate($obj->date_rum) : $this->db->jdate($obj->datec);
            $iban = dolDecrypt($obj->iban);
            $bic = $obj->bic;
            $drum = $obj->drum;
            $rum = $obj->rum;
 
            // But if a force bank account is defined, we use it instead
            if (!empty($obj->fk_prelevement_demande)) {
              $companybankaccountid = 0;
 
              $sqltmp = "SELECT fk_societe_rib FROM ".MAIN_DB_PREFIX."prelevement_demande";
              $sqltmp .= " WHERE rowid = ".((int) $obj->fk_prelevement_demande);
 
              $resqltmp = $this->db->query($sqltmp);
 
              $objtmp = $this->db->fetch_object($resqltmp);
              if ($objtmp) {
                $companybankaccountid = (int) $objtmp->fk_societe_rib;
              }
 
              $bankaccount = new CompanyBankAccount($this->db);
              $bankaccount->fetch($companybankaccountid);
              if ($bankaccount->id > 0) {
                $daterum = $bankaccount->date_rum;
                $iban = $bankaccount->iban;
                $bic = $bankaccount->bic;
                $drum = $bankaccount->id;
                $rum = $bankaccount->rum;
              }
            }
 
            $fileDebiteurSection .= $this->EnregDestinataireSEPA($obj->code, $obj->nom, $obj->address, $obj->zip, $obj->town, $obj->country_code, '', '', '', $obj->somme, $obj->reffac, $obj->idfac, $iban, $bic, $daterum, (string) $drum, $rum, $type);
 
            $this->total += $obj->somme;
            $i++;
          }
          $nbtotalDrctDbtTxInf = $i;
        } else {
          $this->error = $this->db->lasterror();
          fwrite($this->file, 'ERROR DEBITOR ' . $sql . $CrLf); // DEBITOR = Customers
          $result = -2;
        }
 
        // Define $fileEmetteurSection. Start of block PmtInf. Will contains all $nbtotalDrctDbtTxInf
        if ($result != -2) {
          $fileEmetteurSection .= $this->EnregEmetteurSEPA($conf, $date_actu, $nbtotalDrctDbtTxInf, $this->total, $CrLf, $format, $type, $fk_bank_account);
        }
 
        if (getDolGlobalString('SEPA_FORCE_TWO_DECIMAL')) {
          $this->total = number_format((float) price2num($this->total, 'MT'), 2, ".", "");
        }
 
        // SEPA File Header
        fwrite($this->file, '<' . '?xml version="1.0" encoding="UTF-8" standalone="yes"?' . '>' . $CrLf);
        fwrite($this->file, '<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' . $CrLf);
        fwrite($this->file, ' <CstmrDrctDbtInitn>' . $CrLf);
        // SEPA Group header
        fwrite($this->file, '   <GrpHdr>' . $CrLf);
        fwrite($this->file, '     <MsgId>' . ('DOL/' . $dateTime_YMD . '/DD' . $this->id) . '</MsgId>' . $CrLf);
        fwrite($this->file, '     <CreDtTm>' . $dateTime_ECMA . '</CreDtTm>' . $CrLf);
        fwrite($this->file, '     <NbOfTxs>' . $i . '</NbOfTxs>' . $CrLf);
        fwrite($this->file, '     <CtrlSum>' . $this->total . '</CtrlSum>' . $CrLf);
        fwrite($this->file, '     <InitgPty>' . $CrLf);
        fwrite($this->file, '       <Nm>' . dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($this->raison_sociale), ' '))) . '</Nm>' . $CrLf);
        fwrite($this->file, '       <Id>' . $CrLf);
        fwrite($this->file, '           <PrvtId>' . $CrLf);
        fwrite($this->file, '         <Othr>' . $CrLf);
        fwrite($this->file, '           <Id>' . $this->emetteur_ics . '</Id>' . $CrLf);
        fwrite($this->file, '         </Othr>' . $CrLf);
        fwrite($this->file, '           </PrvtId>' . $CrLf);
        fwrite($this->file, '       </Id>' . $CrLf);
        fwrite($this->file, '     </InitgPty>' . $CrLf);
        fwrite($this->file, '   </GrpHdr>' . $CrLf);
        // SEPA File Emetteur
        if ($result != -2) {
          fwrite($this->file, $fileEmetteurSection);
        }
        // SEPA File Debiteurs
        if ($result != -2) {
          fwrite($this->file, $fileDebiteurSection);
        }
        // SEPA FILE FOOTER
        fwrite($this->file, '   </PmtInf>' . $CrLf);
        fwrite($this->file, ' </CstmrDrctDbtInitn>' . $CrLf);
        fwrite($this->file, '</Document>' . $CrLf);
      } else {
        // SEPA Initialisation
        $CrLf = "\n";
 
        $now = dol_now();
 
        $dateTime_ECMA = dol_print_date($now, '%Y-%m-%dT%H:%M:%S');
 
        $date_actu = $now;
        if (!empty($executiondate)) {
          $date_actu = $executiondate;
        }
 
        $dateTime_YMD = dol_print_date($date_actu, '%Y%m%d');
        $dateTime_YMDHMS = dol_print_date($date_actu, '%Y%m%d%H%M%S');
        $fileCrediteurSection = '';
        $fileEmetteurSection = '';
        $i = 0;
 
        /*
         * Section Creditor (sepa Crediteurs block lines)
         */
        if (!empty($forsalary)) {
          $sql = "SELECT u.rowid as userId, u.address, u.zip, u.town, c.code as country_code, CONCAT(u.firstname,' ',u.lastname) as nom,";
          //$sql .= " pl.code_banque as cb, pl.code_guichet as cg, pl.number as cc, ";
          $sql .= " pl.client_nom as nom, pl.amount as somme, pl.fk_prelevement_demande,";
          $sql .= " s.ref as reffac,";
          $sql .= " p.fk_salary as idfac,";
          $sql .= " rib.rowid, rib.datec, rib.iban_prefix as iban, rib.bic as bic, rib.rowid as drum, '' as rum, '' as date_rum";
          $sql .= " FROM";
          $sql .= " " . MAIN_DB_PREFIX . "prelevement_lignes as pl,";
          $sql .= " " . MAIN_DB_PREFIX . "salary as s,";
          $sql .= " " . MAIN_DB_PREFIX . "prelevement as p,";
          $sql .= " " . MAIN_DB_PREFIX . "user as u";
          $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "c_country as c ON u.fk_country = c.rowid,";
          $sql .= " " . MAIN_DB_PREFIX . "user_rib as rib";
          $sql .= " WHERE pl.fk_prelevement_bons=" . ((int) $this->id);
          $sql .= " AND pl.rowid = p.fk_prelevement_lignes";
          $sql .= " AND p.fk_salary = s.rowid";
          $sql .= " AND s.fk_user = u.rowid";
          $sql .= " AND rib.fk_user = s.fk_user";
        } else {
          $sql = "SELECT soc.rowid as socid, soc.code_client as code, soc.address, soc.zip, soc.town, c.code as country_code,";
          //$sql .= " pl.code_banque as cb, pl.code_guichet as cg, pl.number as cc, ";
          $sql .= " pl.client_nom as nom, pl.amount as somme, pl.fk_prelevement_demande,";
          $sql .= " f.ref as reffac, f.ref_supplier as fac_ref_supplier,";
          $sql .= " p.fk_facture_fourn as idfac,";
          $sql .= " rib.rowid, rib.datec, rib.iban_prefix as iban, rib.bic as bic, rib.rowid as drum, rib.rum, rib.date_rum";
          $sql .= " FROM";
          $sql .= " " . MAIN_DB_PREFIX . "prelevement_lignes as pl,";
          $sql .= " " . MAIN_DB_PREFIX . "facture_fourn as f,";
          $sql .= " " . MAIN_DB_PREFIX . "prelevement as p,";
          $sql .= " " . MAIN_DB_PREFIX . "societe as soc";
          $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "c_country as c ON soc.fk_pays = c.rowid,";
          $sql .= " " . MAIN_DB_PREFIX . "societe_rib as rib";
          $sql .= " WHERE pl.fk_prelevement_bons = " . ((int) $this->id);
          $sql .= " AND pl.rowid = p.fk_prelevement_lignes";
          $sql .= " AND p.fk_facture_fourn = f.rowid";
          $sql .= " AND f.fk_soc = soc.rowid";
          $sql .= " AND rib.fk_soc = f.fk_soc";
          if (!empty($thirdpartyBANId)) {
            $sql .= " AND rib.rowid = " . ((int) $thirdpartyBANId);
          } else {
            $sql .= " AND rib.default_rib = 1";
          }
          $sql .= " AND rib.type = 'ban'";
        }
        // Define $fileCrediteurSection. One section DrctDbtTxInf per invoice.
        $nbtotalDrctDbtTxInf = -1;
 
        require_once DOL_DOCUMENT_ROOT . '/societe/class/companybankaccount.class.php';
 
        $resql = $this->db->query($sql);
        if ($resql) {
          $cachearraytotestduplicate = array();
 
          $num = $this->db->num_rows($resql);
          while ($i < $num) {
            $obj = $this->db->fetch_object($resql);
 
            // Test to avoid duplicate default IBAN, to ask user to clean its data
            if (!empty($cachearraytotestduplicate[$obj->idfac])) {
              $this->error = $langs->trans('ErrorCompanyHasDuplicateDefaultBAN', $obj->socid);
              $this->invoice_in_error[$obj->idfac] = $this->error;
              $result = -2;
              break;
            }
 
            $cachearraytotestduplicate[$obj->idfac] = $obj->rowid;
 
            // Get the default value
            $daterum = (!empty($obj->date_rum)) ? $this->db->jdate($obj->date_rum) : $this->db->jdate($obj->datec);
            $iban = dolDecrypt($obj->iban);
            $bic = $obj->bic;
            $drum = $obj->drum;
            $rum = $obj->rum;
 
            // But if a force bank account is defined, we use it instead
            if (!empty($obj->fk_prelevement_demande)) {
              $companybankaccountid = 0;
 
              $sqltmp = "SELECT fk_societe_rib FROM ".MAIN_DB_PREFIX."prelevement_demande";
              $sqltmp .= " WHERE rowid = ".((int) $obj->fk_prelevement_demande);
 
              $resqltmp = $this->db->query($sqltmp);
 
              $objtmp = $this->db->fetch_object($resqltmp);
              if ($objtmp) {
                $companybankaccountid = (int) $objtmp->fk_societe_rib;
              }
 
              $bankaccount = new CompanyBankAccount($this->db);
              $bankaccount->fetch($companybankaccountid);
              if ($bankaccount->id > 0) {
                $daterum = $bankaccount->date_rum;
                $iban = $bankaccount->iban;
                $bic = $bankaccount->bic;
                $drum = $bankaccount->id;
                $rum = $bankaccount->rum;
              }
            }
 
            $refobj = $obj->reffac;
            if (empty($refobj) && !empty($forsalary)) { // If ref of salary not defined, we force a value
              $refobj = "SAL" . $obj->idfac;
            }
 
            $fileCrediteurSection .= $this->EnregDestinataireSEPA($obj->code, $obj->nom, $obj->address, $obj->zip, $obj->town, $obj->country_code, '', '', '', $obj->somme, $refobj, $obj->idfac, $iban, $bic, $daterum, (string) $drum, $rum, $type, $obj->fac_ref_supplier);
 
            $this->total += $obj->somme;
            $i++;
          }
          $nbtotalDrctDbtTxInf = $i;
        } else {
          $this->error = $this->db->lasterror();
          fwrite($this->file, 'ERROR CREDITOR ' . $sql . $CrLf); // CREDITORS = Suppliers
          $result = -2;
        }
        // Define $fileEmetteurSection. Start of block PmtInf. Will contains all $nbtotalDrctDbtTxInf
        if ($result != -2) {
          $fileEmetteurSection .= $this->EnregEmetteurSEPA($conf, $date_actu, $nbtotalDrctDbtTxInf, $this->total, $CrLf, $format, $type);
        }
 
        if (getDolGlobalString('SEPA_FORCE_TWO_DECIMAL')) {
          $this->total = number_format((float) price2num($this->total, 'MT'), 2, ".", "");
        }
 
        // SEPA File Header
        fwrite($this->file, '<' . '?xml version="1.0" encoding="UTF-8" standalone="yes"?' . '>' . $CrLf);
        fwrite($this->file, '<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' . $CrLf);
        fwrite($this->file, ' <CstmrCdtTrfInitn>' . $CrLf);
        // SEPA Group header
        fwrite($this->file, '   <GrpHdr>' . $CrLf);
        fwrite($this->file, '     <MsgId>' . ('DOL/' . $dateTime_YMD . '/CT' . $this->id) . '</MsgId>' . $CrLf);
        fwrite($this->file, '     <CreDtTm>' . $dateTime_ECMA . '</CreDtTm>' . $CrLf);
        fwrite($this->file, '     <NbOfTxs>' . $i . '</NbOfTxs>' . $CrLf);
        fwrite($this->file, '     <CtrlSum>' . $this->total . '</CtrlSum>' . $CrLf);
        fwrite($this->file, '     <InitgPty>' . $CrLf);
        fwrite($this->file, '       <Nm>' . dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($this->raison_sociale), ' '))) . '</Nm>' . $CrLf);
        fwrite($this->file, '       <Id>' . $CrLf);
        fwrite($this->file, '           <PrvtId>' . $CrLf);
        fwrite($this->file, '         <Othr>' . $CrLf);
        fwrite($this->file, '           <Id>' . $this->emetteur_ics . '</Id>' . $CrLf);
        fwrite($this->file, '         </Othr>' . $CrLf);
        fwrite($this->file, '           </PrvtId>' . $CrLf);
        fwrite($this->file, '       </Id>' . $CrLf);
        fwrite($this->file, '     </InitgPty>' . $CrLf);
        fwrite($this->file, '   </GrpHdr>' . $CrLf);
        // SEPA File Emetteur (mycompany)
        if ($result != -2) {
          fwrite($this->file, $fileEmetteurSection);
        }
        // SEPA File Creditors
        if ($result != -2) {
          fwrite($this->file, $fileCrediteurSection);
        }
        // SEPA FILE FOOTER
        fwrite($this->file, '   </PmtInf>' . $CrLf);
        fwrite($this->file, ' </CstmrCdtTrfInitn>' . $CrLf);
        fwrite($this->file, '</Document>' . $CrLf);
      }
    }
 
    // Build file for Other Countries with unknown format
    if (!$found) {
      if ($type != 'bank-transfer') {
        $sql = "SELECT pl.amount";
        $sql .= " FROM";
        $sql .= " " . MAIN_DB_PREFIX . "prelevement_lignes as pl,";
        $sql .= " " . MAIN_DB_PREFIX . "facture as f,";
        $sql .= " " . MAIN_DB_PREFIX . "prelevement as p";
        $sql .= " WHERE pl.fk_prelevement_bons = " . ((int) $this->id);
        $sql .= " AND pl.rowid = p.fk_prelevement_lignes";
        $sql .= " AND p.fk_facture = f.rowid";
 
        // Lines
        $i = 0;
        $resql = $this->db->query($sql);
        if ($resql) {
          $num = $this->db->num_rows($resql);
 
          while ($i < $num) {
            $obj = $this->db->fetch_object($resql);
            $this->total += $obj->amount;
 
            // TODO Write record into file
            $i++;
          }
        } else {
          $result = -2;
        }
      } else {
        $sql = "SELECT pl.amount";
        $sql .= " FROM";
        $sql .= " " . MAIN_DB_PREFIX . "prelevement_lignes as pl,";
        $sql .= " " . MAIN_DB_PREFIX . "facture_fourn as f,";
        $sql .= " " . MAIN_DB_PREFIX . "prelevement as p";
        $sql .= " WHERE pl.fk_prelevement_bons = " . ((int) $this->id);
        $sql .= " AND pl.rowid = p.fk_prelevement_lignes";
        $sql .= " AND p.fk_facture_fourn = f.rowid";
        // Lines
        $i = 0;
        $resql = $this->db->query($sql);
        if ($resql) {
          $num = $this->db->num_rows($resql);
 
          while ($i < $num) {
            $obj = $this->db->fetch_object($resql);
            $this->total += $obj->amount;
 
            // TODO Write record into file
            $i++;
          }
        } else {
          $result = -2;
        }
      }
 
      $langs->load('withdrawals');
 
      // TODO Add here code to generate a generic file
      fwrite($this->file, $langs->transnoentitiesnoconv('WithdrawalFileNotCapable', $mysoc->country_code));
    }
 
    fclose($this->file);
    dolChmod($this->filename);
 
    return $result;
  }
 
 
  public static function buildRumNumber($row_code_client, $row_datec, $row_drum)
  {
    global $langs;
 
    $pre = substr(dol_string_nospecial(dol_string_unaccent($langs->transnoentitiesnoconv('RUM'))), 0, 3); // Must always be on 3 char ('RUM' or 'UMR'. This is a protection against bad translation)
 
    // 3 char + '-' + 10 (yymmddHHMM) + '-' + id + '-' + code. Must be under 32 (SEPA char limit for MndtId is however 35).
    return $pre . '-' . dol_print_date($row_datec, 'dayhourlogsmall') . '-' . dol_trunc((string) $row_drum . ($row_code_client ? '-' . $row_code_client : ''), 17, 'right', 'UTF-8', 1);
  }
 
 
  public function buildSEPAPostalAddressXML($address, $zip, $town, $country_code, $indent, $CrLf)
  {
    $town = dol_string_nospecial(dol_string_unaccent((string) $town), ' ');
    $country_code = trim((string) $country_code);
 
    $missingelements = array();
    if (trim($town) == '') {
      $missingelements[] = 'town';
    }
    if ($country_code == '') {
      $missingelements[] = 'country code';
    }
    if (count($missingelements) > 0) {
      // TwnNm and Ctry are mandatory inside PstlAdr: omit the whole block and warn when one of them is empty
      dol_syslog('buildSEPAPostalAddressXML: PstlAdr block omitted because the '.implode(' and the ', $missingelements).' is empty.', LOG_WARNING);
      return '';
    }
 
    $XML_ADR = $indent . '<PstlAdr>' . $CrLf;
    $zip = dol_string_nospecial(dol_string_unaccent((string) $zip), ' ');
    if (trim($zip) != '') {
      $XML_ADR .= $indent . ' <PstCd>' . dolEscapeXML(dol_trunc($zip, 16, 'right', 'UTF-8', 1)) . '</PstCd>' . $CrLf;
    }
    $XML_ADR .= $indent . ' <TwnNm>' . dolEscapeXML(dol_trunc($town, 35, 'right', 'UTF-8', 1)) . '</TwnNm>' . $CrLf;
    $XML_ADR .= $indent . ' <Ctry>' . dolEscapeXML($country_code) . '</Ctry>' . $CrLf;
    $addressline1 = strtr((string) $address, array(chr(13) => ", ", chr(10) => ""));
    if (trim($addressline1)) {
      $XML_ADR .= $indent . ' <AdrLine>' . dolEscapeXML(dol_trunc(dol_string_nospecial(dol_string_unaccent($addressline1), ' '), 70, 'right', 'UTF-8', 1)) . '</AdrLine>' . $CrLf;
    }
    $XML_ADR .= $indent . '</PstlAdr>' . $CrLf;
 
    return $XML_ADR;
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function EnregDestinataire($rowid, $client_nom, $rib_banque, $rib_guichet, $rib_number, $amount, $ref, $facid, $rib_dom = '', $type = 'direct-debit')
  {
    // phpcs:enable
    fwrite($this->file, "06");
    fwrite($this->file, "08"); // Prelevement ordinaire
 
    fwrite($this->file, "        "); // Zone Reservee B2
 
    fwrite($this->file, $this->emetteur_ics); // ICS
 
    // Date d'echeance C1
 
    fwrite($this->file, "       ");
    fwrite($this->file, dol_print_date($this->date_echeance, "%d%m", 'gmt'));
    fwrite($this->file, substr(dol_print_date($this->date_echeance, "%y", 'gmt'), 1));
 
    // Raison Sociale Destinataire C2
 
    fwrite($this->file, substr(strtoupper($client_nom) . "                         ", 0, 24));
 
    // Address optional D1
    $address = strtr($rib_dom, array(" " => "-", chr(13) => " ", chr(10) => ""));
    fwrite($this->file, substr($address . "                         ", 0, 24));
 
    // Zone Reservee D2
 
    fwrite($this->file, substr("                             ", 0, 8));
 
    // Code Guichet  D3
 
    fwrite($this->file, $rib_guichet);
 
    // Numero de compte D4
 
    fwrite($this->file, substr("000000000000000" . $rib_number, -11));
 
    // Zone E Montant
 
    $montant = (round($amount, 2) * 100);
 
    fwrite($this->file, substr("000000000000000" . $montant, -16));
 
    // Label F
 
    fwrite($this->file, substr("*_" . $ref . "_RDVnet" . $rowid . "                               ", 0, 31));
 
    // Code etablissement G1
 
    fwrite($this->file, $rib_banque);
 
    // Zone Reservee G2
 
    fwrite($this->file, substr("                                        ", 0, 5));
 
    fwrite($this->file, "\n");
  }
 
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function EnregDestinataireSEPA($row_code_client, $row_nom, $row_address, $row_zip, $row_town, $row_country_code, $row_cb, $row_cg, $row_cc, $row_somme, $row_ref, $row_idfac, $row_iban, $row_bic, $row_datec, $row_drum, $row_rum, $type = 'direct-debit', $row_comment = '')
  {
    // phpcs:enable
    global $conf, $mysoc, $hookmanager;
 
    if (getDolGlobalString('SEPA_FORCE_TWO_DECIMAL')) {
      $row_somme = number_format((float) price2num($row_somme, 'MT'), 2, ".", "");
    } else {
      $row_somme = round((float) $row_somme, 2);
    }
 
    include_once DOL_DOCUMENT_ROOT . '/core/lib/functions2.lib.php';
 
    $CrLf = "\n";
    $Rowing = sprintf("%010d", $row_idfac);
 
    // Define value for RUM
    // Example:  RUM-CustomerCode-CustomerBankAccountId-01424448606 (note: Date is the timestamp of the date of creation of CustomerBankAccountId)
    $Rum = (empty($row_rum) ? $this->buildRumNumber($row_code_client, $row_datec, $row_drum) : $row_rum);
 
    // Define date of RUM signature
    $DtOfSgntr = dol_print_date($row_datec, '%Y-%m-%d');
    $XML_RESULT = '';
 
    if (!is_object($hookmanager)) {
      include_once DOL_DOCUMENT_ROOT . '/core/class/hookmanager.class.php';
      $hookmanager = new HookManager($this->db);
    }
    $hookmanager->initHooks(array('bonprelevementdao'));
    $parameters = array(
      'row_code_client' => &$row_code_client, 'row_nom' => &$row_nom, 'row_address' => &$row_address, 'row_zip' => &$row_zip, 'row_town' => &$row_town,
      'row_country_code' => &$row_country_code, 'row_cb' => &$row_cb, 'row_cg' => &$row_cg, 'row_cc' => &$row_cc, 'row_somme' => &$row_somme,
      'row_ref' => &$row_ref, 'row_idfac' => &$row_idfac, 'row_iban' => &$row_iban, 'row_bic' => &$row_bic, 'row_datec' => &$row_datec,
      'row_drum' => &$row_drum, 'row_rum' => &$row_rum, 'type' => &$type, 'row_comment' => &$row_comment,
      'crlf' => &$CrLf, 'rowing' => &$Rowing, 'rum' => &$Rum, 'dtofsgntr' => &$DtOfSgntr,
    );
    $reshook = $hookmanager->executeHooks('enregDestinataireSEPA', $parameters, $this);    // Note that $action and $object may have been modified by some hooks
    if (empty($reshook)) {
      if ($type != 'bank-transfer') {
        // SEPA Paiement Information of buyer for Direct Debit
        $XML_DEBITOR = '';
        $XML_DEBITOR .= '     <DrctDbtTxInf>' . $CrLf;
        $XML_DEBITOR .= '       <PmtId>' . $CrLf;
        // Add EndToEndId. Must be a unique ID for each payment (for example by including bank, buyer or seller, date, checksum)
        $XML_DEBITOR .= '         <EndToEndId>' . ((getDolGlobalString('PRELEVEMENT_END_TO_END') != "") ? $conf->global->PRELEVEMENT_END_TO_END : ('DD-' . dol_trunc($row_idfac . '-' . $row_ref, 20, 'right', 'UTF-8', 1)) . '-' . $Rowing) . '</EndToEndId>' . $CrLf; // ISO20022 states that EndToEndId has a MaxLength of 35 characters
        $XML_DEBITOR .= '       </PmtId>' . $CrLf;
        $XML_DEBITOR .= '       <InstdAmt Ccy="EUR">' . $row_somme . '</InstdAmt>' . $CrLf;
        $XML_DEBITOR .= '       <DrctDbtTx>' . $CrLf;
        $XML_DEBITOR .= '         <MndtRltdInf>' . $CrLf;
        $XML_DEBITOR .= '           <MndtId>' . $Rum . '</MndtId>' . $CrLf;
        $XML_DEBITOR .= '           <DtOfSgntr>' . $DtOfSgntr . '</DtOfSgntr>' . $CrLf;
        $XML_DEBITOR .= '           <AmdmntInd>false</AmdmntInd>' . $CrLf;
        $XML_DEBITOR .= '         </MndtRltdInf>' . $CrLf;
        $XML_DEBITOR .= '       </DrctDbtTx>' . $CrLf;
        $XML_DEBITOR .= '       <DbtrAgt>' . $CrLf;
        $XML_DEBITOR .= '         <FinInstnId>' . $CrLf;
        if (getDolGlobalInt('WITHDRAWAL_WITHOUT_BIC') == 0) {
          $XML_DEBITOR .= '           <BIC>' . $row_bic . '</BIC>' . $CrLf;
        }
        $XML_DEBITOR .= '         </FinInstnId>' . $CrLf;
        $XML_DEBITOR .= '       </DbtrAgt>' . $CrLf;
        $XML_DEBITOR .= '       <Dbtr>' . $CrLf;
        $XML_DEBITOR .= '         <Nm>' . dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($row_nom), ' '))) . '</Nm>' . $CrLf;
        $XML_DEBITOR .= $this->buildSEPAPostalAddressXML($row_address, $row_zip, $row_town, $row_country_code, "\t\t\t\t\t", $CrLf);
        $XML_DEBITOR .= '       </Dbtr>' . $CrLf;
        $XML_DEBITOR .= '       <DbtrAcct>' . $CrLf;
        $XML_DEBITOR .= '         <Id>' . $CrLf;
        $XML_DEBITOR .= '           <IBAN>' . preg_replace('/\s/', '', $row_iban) . '</IBAN>' . $CrLf;
        $XML_DEBITOR .= '         </Id>' . $CrLf;
        $XML_DEBITOR .= '       </DbtrAcct>' . $CrLf;
        $XML_DEBITOR .= '       <RmtInf>' . $CrLf;
 
        // Structured data for Belgium
        if (getDolGlobalString('INVOICE_PAYMENT_ENABLE_STRUCTURED_COMMUNICATION') && $mysoc->country_code == 'BE') {
          include_once DOL_DOCUMENT_ROOT . '/core/lib/functions_be.lib.php';
 
          $invoicestatic = new Facture($this->db);
          $invoicestatic->fetch($row_idfac);
 
          $invoicePaymentKey = dolBECalculateStructuredCommunication($invoicestatic->ref, $invoicestatic->type);
          $XML_DEBITOR .= '         <strd>' . $invoicePaymentKey . '</strd>' . $CrLf;
        } else {
          // A string with some information on payment - 140 max
          $XML_DEBITOR .= '         <Ustrd>' . getDolGlobalString('PRELEVEMENT_USTRD', dolEscapeXML(dol_trunc(dol_string_nospecial(dol_string_unaccent($row_ref . ($row_comment ? ' - ' . $row_comment : '')), '', '', '', 1), 135, 'right', 'UTF-8', 1))) . '</Ustrd>' . $CrLf; // Free unstuctured data - 140 max
        }
        $XML_DEBITOR .= '       </RmtInf>' . $CrLf;
        $XML_DEBITOR .= '     </DrctDbtTxInf>' . $CrLf;
 
        $XML_RESULT = $XML_DEBITOR;
      } else {
        // SEPA Payment Information of seller for Credit Transfer
        $XML_CREDITOR = '';
        $XML_CREDITOR .= '      <CdtTrfTxInf>' . $CrLf;
        $XML_CREDITOR .= '        <PmtId>' . $CrLf;
        // Add EndToEndId. Must be a unique ID for each payment (for example by including bank, buyer or seller, date, checksum)
        $XML_CREDITOR .= '          <EndToEndId>' . ((getDolGlobalString('PRELEVEMENT_END_TO_END') != "") ? getDolGlobalString("PRELEVEMENT_END_TO_END") : ('CT-' . dol_trunc($row_idfac . '-' . $row_ref, 20, 'right', 'UTF-8', 1)) . '-' . $Rowing) . '</EndToEndId>' . $CrLf; // ISO20022 states that EndToEndId has a MaxLength of 35 characters
        $XML_CREDITOR .= '        </PmtId>' . $CrLf;
        if (!empty($this->sepa_xml_pti_in_ctti)) {
          $XML_CREDITOR .= '        <PmtTpInf>' . $CrLf;
 
          // Can be 'NORM' for normal or 'HIGH' for high priority level
          if (getDolGlobalString('PAYMENTBYBANKTRANSFER_FORCE_HIGH_PRIORITY')) {
            $instrprty = 'HIGH';
          } else {
            $instrprty = 'NORM';
          }
 
          // Set $categoryPurpose: CORE, TREA, SUPP, ...
          $categoryPurpose = getDolGlobalString('PAYMENTBYBANKTRANSFER_CUSTOM_CATEGORY_PURPOSE', 'CORE');
 
          $XML_CREDITOR .= '          <InstrPrty>' . $instrprty . '</InstrPrty>' . $CrLf;
          $XML_CREDITOR .= '          <SvcLvl>' . $CrLf;
          $XML_CREDITOR .= '            <Cd>SEPA</Cd>' . $CrLf;
          $XML_CREDITOR .= '          </SvcLvl>' . $CrLf;
          $XML_CREDITOR .= '          <CtgyPurp>' . $CrLf;
          $XML_CREDITOR .= '            <Cd>' . $categoryPurpose . '</Cd>' . $CrLf;
          $XML_CREDITOR .= '          </CtgyPurp>' . $CrLf;
          $XML_CREDITOR .= '        </PmtTpInf>' . $CrLf;
        }
        $XML_CREDITOR .= '        <Amt>' . $CrLf;
        $XML_CREDITOR .= '        <InstdAmt Ccy="EUR">' . round((float) $row_somme, 2) . '</InstdAmt>' . $CrLf;
        $XML_CREDITOR .= '        </Amt>' . $CrLf;
        /*
         $XML_CREDITOR .= '       <DrctDbtTx>'.$CrLf;
         $XML_CREDITOR .= '         <MndtRltdInf>'.$CrLf;
         $XML_CREDITOR .= '           <MndtId>'.$Rum.'</MndtId>'.$CrLf;
         $XML_CREDITOR .= '           <DtOfSgntr>'.$DtOfSgntr.'</DtOfSgntr>'.$CrLf;
         $XML_CREDITOR .= '           <AmdmntInd>false</AmdmntInd>'.$CrLf;
         $XML_CREDITOR .= '         </MndtRltdInf>'.$CrLf;
         $XML_CREDITOR .= '       </DrctDbtTx>'.$CrLf;
         */
        //$XML_CREDITOR .= '        <ChrgBr>SLEV</ChrgBr>'.$CrLf;
        $XML_CREDITOR .= '        <CdtrAgt>' . $CrLf;
        $XML_CREDITOR .= '          <FinInstnId>' . $CrLf;
        $XML_CREDITOR .= '            <BIC>' . $row_bic . '</BIC>' . $CrLf;
        $XML_CREDITOR .= '          </FinInstnId>' . $CrLf;
        $XML_CREDITOR .= '        </CdtrAgt>' . $CrLf;
        $XML_CREDITOR .= '        <Cdtr>' . $CrLf;
        $XML_CREDITOR .= '          <Nm>' . dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($row_nom), ' '))) . '</Nm>' . $CrLf;
        $XML_CREDITOR .= $this->buildSEPAPostalAddressXML($row_address, $row_zip, $row_town, $row_country_code, "\t\t\t\t\t", $CrLf);
        $XML_CREDITOR .= '        </Cdtr>' . $CrLf;
        $XML_CREDITOR .= '        <CdtrAcct>' . $CrLf;
        $XML_CREDITOR .= '          <Id>' . $CrLf;
        $XML_CREDITOR .= '            <IBAN>' . preg_replace('/\s/', '', $row_iban) . '</IBAN>' . $CrLf;
        $XML_CREDITOR .= '          </Id>' . $CrLf;
        $XML_CREDITOR .= '        </CdtrAcct>' . $CrLf;
        $XML_CREDITOR .= '        <RmtInf>' . $CrLf;
        // A string with some information on payment - 140 max
        $XML_CREDITOR .= '          <Ustrd>' . getDolGlobalString('CREDITTRANSFER_USTRD', dolEscapeXML(dol_trunc(dol_string_nospecial(dol_string_unaccent($row_ref . ($row_comment ? ' - ' . $row_comment : '')), '', '', '', 1), 135, 'right', 'UTF-8', 1))) . '</Ustrd>' . $CrLf; // Free unstructured data - 140 max
        $XML_CREDITOR .= '        </RmtInf>' . $CrLf;
        $XML_CREDITOR .= '      </CdtTrfTxInf>' . $CrLf;
 
        $XML_RESULT = $XML_CREDITOR;
      }
    } elseif ($reshook > 0) {
      $XML_RESULT = $hookmanager->resPrint;
    }
    $XML_RESULT .= $hookmanager->resPrint;
 
    return $XML_RESULT;
  }
 
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function EnregEmetteur($type = 'direct-debit')
  {
    // phpcs:enable
    fwrite($this->file, "03");
    fwrite($this->file, "08"); // Prelevement ordinaire
 
    fwrite($this->file, "        "); // Zone Reservee B2
 
    fwrite($this->file, $this->emetteur_ics); // ICS
 
    // Date d'echeance C1
 
    fwrite($this->file, "       ");
    fwrite($this->file, dol_print_date($this->date_echeance, "%d%m", 'gmt'));
    fwrite($this->file, substr(dol_print_date($this->date_echeance, "%y", 'gmt'), 1));
 
    // Raison Sociale C2
 
    fwrite($this->file, substr($this->raison_sociale . "                           ", 0, 24));
 
    // Ref of thirdparty on 7 characters
 
    fwrite($this->file, substr($this->reference_remise . "                           ", 0, 7));
 
    // Zone Reservee D1-2
 
    fwrite($this->file, substr("                                    ", 0, 17));
 
    // Zone Reservee D2
 
    fwrite($this->file, substr("                             ", 0, 2));
    fwrite($this->file, "E");
    fwrite($this->file, substr("                             ", 0, 5));
 
    // Code Guichet  D3
 
    fwrite($this->file, $this->emetteur_code_guichet);
 
    // Numero de compte D4
 
    fwrite($this->file, substr("000000000000000" . $this->emetteur_numero_compte, -11));
 
    // Zone Reservee E
 
    fwrite($this->file, substr("                                        ", 0, 16));
 
    // Zone Reservee F
 
    fwrite($this->file, substr("                                        ", 0, 31));
 
    // Code etablissement
 
    fwrite($this->file, $this->emetteur_code_banque);
 
    // Zone Reservee G
 
    fwrite($this->file, substr("                                        ", 0, 5));
 
    fwrite($this->file, "\n");
  }
 
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function EnregEmetteurSEPA($configuration, $ladate, $nombre, $total, $CrLf = '\n', $format = 'FRST', $type = 'direct-debit', $fk_bank_account = 0)
  {
    // phpcs:enable
 
    // Clean parameters
    $dateTime_YMD = dol_print_date($ladate, '%Y%m%d');
    $dateTime_ETAD = dol_print_date($ladate, '%Y-%m-%d');
    $dateTime_YMDHMS = dol_print_date($ladate, '%Y-%m-%dT%H:%M:%S');
 
    // Clean params
    if (empty($fk_bank_account)) {
      $fk_bank_account = ($type == 'bank-transfer' ? getDolGlobalInt('PAYMENTBYBANKTRANSFER_ID_BANKACCOUNT') : getDolGlobalInt('PRELEVEMENT_ID_BANKACCOUNT'));
    }
 
    // Get data of bank account
    $account = new Account($this->db);
    if ($account->fetch($fk_bank_account) > 0) {
      $this->emetteur_code_banque = $account->code_banque;
      $this->emetteur_code_guichet = $account->code_guichet;
      $this->emetteur_numero_compte = $account->number;
      $this->emetteur_number_key = $account->cle_rib;
      $this->sepa_xml_pti_in_ctti = (bool) $account->pti_in_ctti;
      $this->emetteur_iban = $account->iban;
      $this->emetteur_bic = $account->bic;
 
      $this->emetteur_ics = (($type == 'bank-transfer' && getDolGlobalString("SEPA_USE_IDS")) ? $account->ics_transfer : $account->ics);  // Ex: PRELEVEMENT_ICS = "FR78ZZZ123456";
 
      $this->raison_sociale = $account->owner_name;
    }
 
    // Get pending payments
    $sql = "SELECT rowid, ref";
    $sql .= " FROM " . MAIN_DB_PREFIX . "prelevement_bons as pb";
    $sql .= " WHERE pb.rowid = " . ((int) $this->id);
 
    $resql = $this->db->query($sql);
    if ($resql) {
      $obj = $this->db->fetch_object($resql);
 
      $country = explode(':', getDolGlobalString('MAIN_INFO_SOCIETE_COUNTRY'));
      $IdBon  = sprintf("%05d", $obj->rowid);
      $RefBon = $obj->ref;
      $localInstrument = getDolGlobalString('PAYMENTBYBANKTRANSFER_CUSTOM_LOCAL_INSTRUMENT', 'CORE');
 
      if (getDolGlobalString('SEPA_FORCE_TWO_DECIMAL')) {
        $total = number_format((float) price2num($total, 'MT'), 2, ".", "");
      }
 
      if ($type != 'bank-transfer') {
        // SEPA Paiement Information of my company for Direct Debit
        $XML_SEPA_INFO = '';
        $XML_SEPA_INFO .= '   <PmtInf>' . $CrLf;
        $XML_SEPA_INFO .= '     <PmtInfId>' . ('DOL/' . $dateTime_YMD . '/DD' . $IdBon . '-' . $RefBon) . '</PmtInfId>' . $CrLf;
        $XML_SEPA_INFO .= '     <PmtMtd>DD</PmtMtd>' . $CrLf;
        $XML_SEPA_INFO .= '     <NbOfTxs>' . $nombre . '</NbOfTxs>' . $CrLf;
        $XML_SEPA_INFO .= '     <CtrlSum>' . $total . '</CtrlSum>' . $CrLf;
        $XML_SEPA_INFO .= '     <PmtTpInf>' . $CrLf;
        $XML_SEPA_INFO .= '       <SvcLvl>' . $CrLf;
        $XML_SEPA_INFO .= '         <Cd>SEPA</Cd>' . $CrLf;
        $XML_SEPA_INFO .= '       </SvcLvl>' . $CrLf;
        $XML_SEPA_INFO .= '       <LclInstrm>' . $CrLf;
        $XML_SEPA_INFO .= '         <Cd>' . $localInstrument . '</Cd>' . $CrLf;
        $XML_SEPA_INFO .= '       </LclInstrm>' . $CrLf;
        $XML_SEPA_INFO .= '       <SeqTp>' . $format . '</SeqTp>' . $CrLf;
        $XML_SEPA_INFO .= '     </PmtTpInf>' . $CrLf;
        $XML_SEPA_INFO .= '     <ReqdColltnDt>' . $dateTime_ETAD . '</ReqdColltnDt>' . $CrLf;
        $XML_SEPA_INFO .= '     <Cdtr>' . $CrLf;
        $XML_SEPA_INFO .= '       <Nm>' . dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($this->raison_sociale), ' '))) . '</Nm>' . $CrLf;
        $sender_address = (string) getDolGlobalString('MAIN_INFO_SOCIETE_ADDRESS');
        $sender_zip = (string) getDolGlobalString('MAIN_INFO_SOCIETE_ZIP');
        $sender_town = (string) getDolGlobalString('MAIN_INFO_SOCIETE_TOWN');
        $sender_country_code = (string) ($country[1] ?? '');
        $XML_SEPA_INFO .= $this->buildSEPAPostalAddressXML($sender_address, $sender_zip, $sender_town, $sender_country_code, "\t\t\t\t", $CrLf);
        $XML_SEPA_INFO .= '     </Cdtr>' . $CrLf;
        $XML_SEPA_INFO .= '     <CdtrAcct>' . $CrLf;
        $XML_SEPA_INFO .= '       <Id>' . $CrLf;
        $XML_SEPA_INFO .= '         <IBAN>' . preg_replace('/\s/', '', $this->emetteur_iban) . '</IBAN>' . $CrLf;
        $XML_SEPA_INFO .= '       </Id>' . $CrLf;
        $XML_SEPA_INFO .= '     </CdtrAcct>' . $CrLf;
        $XML_SEPA_INFO .= '     <CdtrAgt>' . $CrLf;
        $XML_SEPA_INFO .= '       <FinInstnId>' . $CrLf;
        $XML_SEPA_INFO .= '         <BIC>' . $this->emetteur_bic . '</BIC>' . $CrLf;
        $XML_SEPA_INFO .= '       </FinInstnId>' . $CrLf;
        $XML_SEPA_INFO .= '     </CdtrAgt>' . $CrLf;
        /* $XML_SEPA_INFO .= '      <UltmtCdtr>'.$CrLf;
         $XML_SEPA_INFO .= '        <Nm>'.dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($this->raison_sociale), ' '))).'</Nm>'.$CrLf;
         $XML_SEPA_INFO .= '        <PstlAdr>'.$CrLf;
         $XML_SEPA_INFO .= '          <Ctry>'.$country[1].'</Ctry>'.$CrLf;
         $XML_SEPA_INFO .= '          <AdrLine>'.dolEscapeXML(dol_trunc(dol_string_nospecial(dol_string_unaccent(getDolGlobalString('MAIN_INFO_SOCIETE_ADDRESS')), ' '), 70, 'right', 'UTF-8', 1)).'</AdrLine>'.$CrLf;
         $XML_SEPA_INFO .= '          <AdrLine>'.dolEscapeXML(dol_trunc(dol_string_nospecial(dol_string_unaccent(getDolGlobalString('MAIN_INFO_SOCIETE_ZIP').' '.getDolGlobalString('MAIN_INFO_SOCIETE_TOWN')), ' '), 70, 'right', 'UTF-8', 1)).'</AdrLine>'.$CrLf;
         $XML_SEPA_INFO .= '        </PstlAdr>'.$CrLf;
         $XML_SEPA_INFO .= '      </UltmtCdtr>'.$CrLf;*/
        $XML_SEPA_INFO .= '     <ChrgBr>SLEV</ChrgBr>' . $CrLf; // Field "Responsible of fees". Must be SLEV
        $XML_SEPA_INFO .= '     <CdtrSchmeId>' . $CrLf;
        $XML_SEPA_INFO .= '       <Id>' . $CrLf;
        $XML_SEPA_INFO .= '         <PrvtId>' . $CrLf;
        $XML_SEPA_INFO .= '           <Othr>' . $CrLf;
        $XML_SEPA_INFO .= '             <Id>' . $this->emetteur_ics . '</Id>' . $CrLf;
        $XML_SEPA_INFO .= '             <SchmeNm>' . $CrLf;
        $XML_SEPA_INFO .= '               <Prtry>SEPA</Prtry>' . $CrLf;
        $XML_SEPA_INFO .= '             </SchmeNm>' . $CrLf;
        $XML_SEPA_INFO .= '           </Othr>' . $CrLf;
        $XML_SEPA_INFO .= '         </PrvtId>' . $CrLf;
        $XML_SEPA_INFO .= '       </Id>' . $CrLf;
        $XML_SEPA_INFO .= '     </CdtrSchmeId>' . $CrLf;
      } else {
        // SEPA Paiement Information of my company for Credit Transfer
        $XML_SEPA_INFO = '';
        $XML_SEPA_INFO .= '   <PmtInf>' . $CrLf;
        $XML_SEPA_INFO .= '     <PmtInfId>' . ('DOL/' . $dateTime_YMD . '/CT' . $IdBon . '-' . $RefBon) . '</PmtInfId>' . $CrLf;
        $XML_SEPA_INFO .= '     <PmtMtd>TRF</PmtMtd>' . $CrLf;
        //$XML_SEPA_INFO .= '     <BtchBookg>False</BtchBookg>'.$CrLf;
        $XML_SEPA_INFO .= '     <NbOfTxs>' . $nombre . '</NbOfTxs>' . $CrLf;
        $XML_SEPA_INFO .= '     <CtrlSum>' . $total . '</CtrlSum>' . $CrLf;
        if (!empty($this->sepa_xml_pti_in_ctti) && !empty($format)) { // @TODO Using $format (FRST ou RCUR) in a section for a Credit Transfer looks strange.
          $XML_SEPA_INFO .= '     <PmtTpInf>' . $CrLf;
          $XML_SEPA_INFO .= '       <SvcLvl>' . $CrLf;
          $XML_SEPA_INFO .= '         <Cd>SEPA</Cd>' . $CrLf;
          $XML_SEPA_INFO .= '       </SvcLvl>' . $CrLf;
          $XML_SEPA_INFO .= '       <LclInstrm>' . $CrLf;
          $XML_SEPA_INFO .= '         <Cd>' . $localInstrument . '</Cd>' . $CrLf;
          $XML_SEPA_INFO .= '       </LclInstrm>' . $CrLf;
          $XML_SEPA_INFO .= '       <SeqTp>' . $format . '</SeqTp>' . $CrLf;
          $XML_SEPA_INFO .= '     </PmtTpInf>' . $CrLf;
        }
        $XML_SEPA_INFO .= '     <ReqdExctnDt>' . dol_print_date($dateTime_ETAD, 'dayrfc') . '</ReqdExctnDt>' . $CrLf;
        $XML_SEPA_INFO .= '     <Dbtr>' . $CrLf;
        $XML_SEPA_INFO .= '       <Nm>' . dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($this->raison_sociale), ' '))) . '</Nm>' . $CrLf;
        $sender_address = (string) getDolGlobalString('MAIN_INFO_SOCIETE_ADDRESS');
        $sender_zip = (string) getDolGlobalString('MAIN_INFO_SOCIETE_ZIP');
        $sender_town = (string) getDolGlobalString('MAIN_INFO_SOCIETE_TOWN');
        $sender_country_code = (string) ($country[1] ?? '');
        $XML_SEPA_INFO .= $this->buildSEPAPostalAddressXML($sender_address, $sender_zip, $sender_town, $sender_country_code, "\t\t\t\t", $CrLf);
        $XML_SEPA_INFO .= '     </Dbtr>' . $CrLf;
        $XML_SEPA_INFO .= '     <DbtrAcct>' . $CrLf;
        $XML_SEPA_INFO .= '       <Id>' . $CrLf;
        $XML_SEPA_INFO .= '         <IBAN>' . preg_replace('/\s/', '', $this->emetteur_iban) . '</IBAN>' . $CrLf;
        $XML_SEPA_INFO .= '       </Id>' . $CrLf;
        $XML_SEPA_INFO .= '     </DbtrAcct>' . $CrLf;
        $XML_SEPA_INFO .= '     <DbtrAgt>' . $CrLf;
        $XML_SEPA_INFO .= '       <FinInstnId>' . $CrLf;
        $XML_SEPA_INFO .= '         <BIC>' . $this->emetteur_bic . '</BIC>' . $CrLf;
        $XML_SEPA_INFO .= '       </FinInstnId>' . $CrLf;
        $XML_SEPA_INFO .= '     </DbtrAgt>' . $CrLf;
        /* $XML_SEPA_INFO .= '      <UltmtCdtr>'.$CrLf;
         $XML_SEPA_INFO .= '        <Nm>'.dolEscapeXML(strtoupper(dol_string_nospecial(dol_string_unaccent($this->raison_sociale), ' '))).'</Nm>'.$CrLf;
         $XML_SEPA_INFO .= '        <PstlAdr>'.$CrLf;
         $XML_SEPA_INFO .= '          <Ctry>'.$country[1].'</Ctry>'.$CrLf;
         $XML_SEPA_INFO .= '          <AdrLine>'.dolEscapeXML(dol_trunc(dol_string_nospecial(dol_string_unaccent(getDolGlobalString('MAIN_INFO_SOCIETE_ADDRESS')), ' '), 70, 'right', 'UTF-8', 1)).'</AdrLine>'.$CrLf;
         $XML_SEPA_INFO .= '          <AdrLine>'.dolEscapeXML(dol_trunc(dol_string_nospecial(dol_string_unaccent(getDolGlobalString('MAIN_INFO_SOCIETE_ZIP').' '.getDolGlobalString('MAIN_INFO_SOCIETE_TOWN')), ' '), 70, 'right', 'UTF-8', 1)).'</AdrLine>'.$CrLf;
         $XML_SEPA_INFO .= '        </PstlAdr>'.$CrLf;
         $XML_SEPA_INFO .= '      </UltmtCdtr>'.$CrLf;*/
        $XML_SEPA_INFO .= '     <ChrgBr>SLEV</ChrgBr>' . $CrLf; // Field "Responsible of fees". Must be SLEV
        /*$XML_SEPA_INFO .= '     <CdtrSchmeId>'.$CrLf;
         $XML_SEPA_INFO .= '        <Id>'.$CrLf;
         $XML_SEPA_INFO .= '          <PrvtId>'.$CrLf;
         $XML_SEPA_INFO .= '            <Othr>'.$CrLf;
         $XML_SEPA_INFO .= '              <Id>'.$this->emetteur_ics.'</Id>'.$CrLf;
         $XML_SEPA_INFO .= '              <SchmeNm>'.$CrLf;
         $XML_SEPA_INFO .= '                <Prtry>SEPA</Prtry>'.$CrLf;
         $XML_SEPA_INFO .= '              </SchmeNm>'.$CrLf;
         $XML_SEPA_INFO .= '            </Othr>'.$CrLf;
         $XML_SEPA_INFO .= '          </PrvtId>'.$CrLf;
         $XML_SEPA_INFO .= '        </Id>'.$CrLf;
         $XML_SEPA_INFO .= '      </CdtrSchmeId>'.$CrLf;*/
      }
    } else {
      fwrite($this->file, 'INCORRECT EMETTEUR ' . $this->raison_sociale . $CrLf);
      $XML_SEPA_INFO = '';
    }
    return $XML_SEPA_INFO;
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function EnregTotal($total)
  {
    // phpcs:enable
    fwrite($this->file, "08");
    fwrite($this->file, "08"); // Prelevement ordinaire
 
    fwrite($this->file, "        "); // Zone Reservee B2
 
    fwrite($this->file, $this->emetteur_ics); // ICS
 
    // Reserve C1
 
    fwrite($this->file, substr("                           ", 0, 12));
 
 
    // Raison Sociale C2
 
    fwrite($this->file, substr("                           ", 0, 24));
 
    // D1
 
    fwrite($this->file, substr("                                    ", 0, 24));
 
    // Zone Reservee D2
 
    fwrite($this->file, substr("                             ", 0, 8));
 
    // Code Guichet  D3
 
    fwrite($this->file, substr("                             ", 0, 5));
 
    // Numero de compte D4
 
    fwrite($this->file, substr("                             ", 0, 11));
 
    // Zone E Montant
 
    $montant = ($total * 100);
 
    fwrite($this->file, substr("000000000000000" . $montant, -16));
 
    // Zone Reservee F
 
    fwrite($this->file, substr("                                        ", 0, 31));
 
    // Code etablissement
 
    fwrite($this->file, substr("                                        ", 0, 5));
 
    // Zone Reservee F
 
    fwrite($this->file, substr("                                        ", 0, 5));
 
    fwrite($this->file, "\n");
  }
 
  public function getLibStatut($mode = 0)
  {
    return $this->LibStatut((isset($this->status) ? $this->status : $this->statut), $mode);
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function LibStatut($status, $mode = 0)
  {
    // phpcs:enable
    if (empty($this->labelStatus) || empty($this->labelStatusShort)) {
      global $langs;
      //$langs->load("mymodule");
      $this->labelStatus[self::STATUS_DRAFT] = $langs->transnoentitiesnoconv('StatusWaiting');
      $this->labelStatus[self::STATUS_TRANSFERED] = $langs->transnoentitiesnoconv('StatusTrans');
      $this->labelStatusShort[self::STATUS_DRAFT] = $langs->transnoentitiesnoconv('StatusWaiting');
      $this->labelStatusShort[self::STATUS_TRANSFERED] = $langs->transnoentitiesnoconv('StatusTrans');
      if ($this->type == 'bank-transfer') {
        $this->labelStatus[self::STATUS_DEBITED] = $langs->transnoentitiesnoconv('StatusDebited');
        $this->labelStatusShort[self::STATUS_DEBITED] = $langs->transnoentitiesnoconv('StatusDebited');
      } else {
        $this->labelStatus[self::STATUS_CREDITED] = $langs->transnoentitiesnoconv('StatusCredited');
        $this->labelStatusShort[self::STATUS_CREDITED] = $langs->transnoentitiesnoconv('StatusCredited');
      }
      $this->labelStatus[self::STATUS_CANCELED] = $langs->transnoentitiesnoconv('Canceled');
      $this->labelStatusShort[self::STATUS_CANCELED] = $langs->transnoentitiesnoconv('Canceled');
    }
 
    $statusType = 'status1';
    if ($status == self::STATUS_TRANSFERED) {
      $statusType = 'status3';
    }
    if ($status == self::STATUS_CREDITED || $status == self::STATUS_DEBITED) {
      $statusType = 'status6';
    }
    if ($status == self::STATUS_CANCELED) {
      $statusType = 'status9';
    }
 
    return dolGetStatus($this->labelStatus[$status], $this->labelStatusShort[$status], '', $statusType, $mode);
  }
 
  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
  public function load_board($user, $mode)
  {
    // phpcs:enable
    global $conf, $langs;
 
    if ($user->socid) {
      return -1; // Protection to prevent calls by external users
    }
 
    if ($mode == 'direct-debit') {
      $sql = "SELECT p.rowid, p.datec, p.date_trans as date_trans, p.date_credit as date_credit";
      $sql .= " FROM " . MAIN_DB_PREFIX . "prelevement_bons as p";
      $sql .= " WHERE p.entity IN (" . getEntity('prelevement_bons') . ")";
      $sql .= " AND (p.type = 'debit-order' OR p.type = 'direct-debit')";     // direct debit
      $sql .= " AND p.statut < ".((int) BonPrelevement::STATUS_DEBITED);
    } else {
      $sql = "SELECT p.rowid, p.datec, p.date_trans as date_trans, p.date_credit as date_credit";
      $sql .= " FROM " . MAIN_DB_PREFIX . "prelevement_bons as p";
      $sql .= " WHERE p.entity IN (" . getEntity('prelevement_bons') . ")";
      $sql .= " AND (p.type = 'bank-transfer' OR p.type = 'credit-transfer')";  // credit transfer
      $sql .= " AND p.statut < ".((int) BonPrelevement::STATUS_CREDITED);
    }
 
    $resql = $this->db->query($sql);
    if ($resql) {
      $langs->load("banks");
      $now = dol_now();
 
      $response = new WorkboardResponse();
      if ($mode == 'direct-debit') {
        $warning_delay = (int) $conf->warning_delays['bank_direct_debit'];  // In seconds. The one of the response is in days.
        $response->warning_delay = $warning_delay / 60 / 60 / 24;
        $response->label = $langs->trans("PendingDirectDebitToComplete");
        $response->labelShort = $langs->trans("PendingDirectDebitToCompleteShort");
        $response->url = DOL_URL_ROOT . '/compta/prelevement/orders_list.php?leftmenu=checks&mainmenu=bank&search_status=0,1';
        $response->url_late = DOL_URL_ROOT . '/compta/prelevement/orders_list.php?leftmenu=checks&mainmenu=bank&search_status=0,1';
      } else {
        $warning_delay = (int) $conf->warning_delays['bank_credit_transfer']; // In seconds. The one of the response is in days.
        $response->warning_delay = $warning_delay / 60 / 60 / 24;
        $response->label = $langs->trans("PendingCreditTransferToComplete");
        $response->labelShort = $langs->trans("PendingCreditTransferToCompleteShort");
        $response->url = DOL_URL_ROOT . '/compta/prelevement/orders_list.php?leftmenu=checks&mainmenu=bank&type=bank-transfer&search_status=0,1';
        $response->url_late = DOL_URL_ROOT . '/compta/prelevement/orders_list.php?leftmenu=checks&mainmenu=bank&type=bank-transfer&search_status=0,1';
      }
      $response->img = img_object('', "payment");
 
      $response->nbtodo = 0;
      $response->nbtodolate = 0;
 
      while ($obj = $this->db->fetch_object($resql)) {
        $response->nbtodo++;
 
        // An order is waiting since it was transmitted to the bank, or since its creation when it was not transmitted yet
        $datetotest = $this->db->jdate(!empty($obj->date_trans) ? $obj->date_trans : $obj->datec);
        if ($datetotest && $datetotest < ($now - $warning_delay)) {
          $response->nbtodolate++;
        }
      }
 
      // Return workboard only if quantity is not 0
      //if ($response->nbtodo) {
      return $response;
      //} else {
      //  return 0;
      //}
    } else {
      dol_print_error($this->db);
      $this->error = $this->db->error();
      return -1;
    }
  }
 
  public function getKanbanView($option = '', $arraydata = null)
  {
    global $langs;
 
    $selected = (empty($arraydata['selected']) ? 0 : $arraydata['selected']);
 
    $return = '<div class="box-flex-item box-flex-grow-zero">';
    $return .= '<div class="info-box info-box-sm">';
    $return .= '<span class="info-box-icon bg-infobox-action">';
    $return .= img_picto('', $this->picto);
    $return .= '</span>';
    $return .= '<div class="info-box-content">';
    $return .= '<span class="info-box-ref inline-block tdoverflowmax150 valignmiddle">' . $this->getNomUrl(1) . '</span>';
    if ($selected >= 0) {
      $return .= '<input id="cb' . $this->id . '" class="flat checkforselect fright" type="checkbox" name="toselect[]" value="' . $this->id . '"' . ($selected ? ' checked="checked"' : '') . '>';
    }
    /*
    if (isset($this->date_trans)) {
      $return .= '<br><span class="opacitymedium">' . $langs->trans("TransData") . '</span> : <span class="info-box-label">' . dol_print_date($this->db->jdate($this->date_tans), 'day') . '</span>';
    }
    if (isset($this->date_credit)) {
      $return .= '<br><span class="opacitymedium">' . $langs->trans("CreditDate") . '</span> : <span class="info-box-label">' . dol_print_date($this->db->jdate($this->date_credit), 'day') . '</span>';
    }
    if (isset($this->date_echeance)) {
      $return .= '<br><span class="opacitymedium">' . $langs->trans("Date") . '</span> : <span class="info-box-label">' . dol_print_date($this->db->jdate($this->date_echeance), 'day') . '</span>';
    }
    */
    if (isset($this->amount)) {
      $return .= '<br><span class="opacitymedium">' . $langs->trans("Amount") . '</span> : <span class="amount">' . price($this->amount) . '</span>';
    }
    $return .= '<br><div class="info-box-status">' . $this->getLibStatut(3) . '</div>';
    $return .= '</div>';
    $return .= '</div>';
    $return .= '</div>';
    return $return;
  }
 
  public function checkIfSalaryBonPrelevement()
  {
    if (!empty($this->id)) {
      $id = $this->id;
    } else {
      return 0;
    }
    $sql = "SELECT COUNT(*) AS nb FROM " . MAIN_DB_PREFIX . "prelevement_lignes";
    $sql .= " WHERE fk_prelevement_bons = " . ((int) $id);
    $sql .= " AND fk_soc = 0";  // fk_soc can't be NULL
    $sql .= " AND fk_user IS NOT NULL";
 
    $num = 0;
    $resql = $this->db->query($sql);
    if ($resql) {
      $obj = $this->db->fetch_object($resql);
      $num = $obj->nb;
    } else {
      dol_print_error($this->db);
    }
    if ($num > 0) {
      return 1;
    }
 
    return 0;
  }
}