/**
* Create a withdraw
* TODO delete params banque and agence when not necesary
*
* @param int $banque dolibarr mysoc bank
* @param int $agence dolibarr mysoc bank office (guichet)
* @param string $mode real=do action, simu=test only
* @return int <0 if KO, nbre of invoice withdrawed if OK
*/
function Create($banque = 0, $agence = 0, $mode = 'real')
{
global $conf, $langs;
dol_syslog(get_class($this) . "::Create banque={$banque} agence={$agence}");
require_once DOL_DOCUMENT_ROOT . "/compta/facture/class/facture.class.php";
require_once DOL_DOCUMENT_ROOT . "/societe/class/societe.class.php";
$error = 0;
$datetimeprev = time();
$month = strftime("%m", $datetimeprev);
$year = strftime("%Y", $datetimeprev);
$puser = new User($this->db, $conf->global->PRELEVEMENT_USER);
/*
* Read invoices
*/
$factures = array();
$factures_prev = array();
$factures_result = array();
if (!$error) {
$sql = "SELECT f.rowid, pfd.rowid as pfdrowid, f.fk_soc";
$sql .= ", pfd.code_banque, pfd.code_guichet, pfd.number, pfd.cle_rib";
$sql .= ", pfd.amount";
$sql .= ", s.nom";
$sql .= " FROM " . MAIN_DB_PREFIX . "facture as f";
$sql .= ", " . MAIN_DB_PREFIX . "societe as s";
$sql .= ", " . MAIN_DB_PREFIX . "prelevement_facture_demande as pfd";
//if ($banque || $agence) $sql.= ", ".MAIN_DB_PREFIX."societe_rib as sr";
$sql .= " WHERE f.rowid = pfd.fk_facture";
$sql .= " AND f.entity = " . $conf->entity;
$sql .= " AND s.rowid = f.fk_soc";
//if ($banque || $agence) $sql.= " AND s.rowid = sr.fk_soc";
$sql .= " AND f.fk_statut = 1";
$sql .= " AND f.paye = 0";
$sql .= " AND pfd.traite = 0";
$sql .= " AND f.total_ttc > 0";
//if ($banque) $sql.= " AND sr.code_banque = '".$conf->global->PRELEVEMENT_CODE_BANQUE."'";
//if ($agence) $sql.= " AND sr.code_guichet = '".$conf->global->PRELEVEMENT_CODE_GUICHET."'";
dol_syslog(get_class($this) . "::Create sql=" . $sql, LOG_DEBUG);
$resql = $this->db->query($sql);
if ($resql) {
$num = $this->db->num_rows($resql);
$i = 0;
while ($i < $num) {
$row = $this->db->fetch_row($resql);
$factures[$i] = $row;
// All fields
$i++;
}
$this->db->free($resql);
dol_syslog($i . " invoices to withdraw");
} else {
$error = 1;
dol_syslog("Erreur -1");
dol_syslog($this->db->error());
}
}
if (!$error) {
require_once DOL_DOCUMENT_ROOT . '/societe/class/companybankaccount.class.php';
$soc = new Societe($this->db);
// Check RIB
$i = 0;
dol_syslog("Start RIB check");
if (count($factures) > 0) {
foreach ($factures as $key => $fac) {
$fact = new Facture($this->db);
if ($fact->fetch($fac[0]) >= 0) {
if ($soc->fetch($fact->socid) >= 0) {
$bac = new CompanyBankAccount($this->db);
$bac->fetch(0, $soc->id);
if ($bac->verif() >= 1) {
$factures_prev[$i] = $fac;
/* second tableau necessaire pour BonPrelevement */
$factures_prev_id[$i] = $fac[0];
$i++;
} else {
dol_syslog("Error on default bank number RIB/IBAN for thirdparty reported by verif() " . $fact->socid . " " . $soc->nom, LOG_ERR);
$facture_errors[$fac[0]] = "Error on default bank number RIB/IBAN for thirdparty reported by function verif() " . $fact->socid . " " . $soc->nom;
}
} else {
dol_syslog("Failed to read company", LOG_ERR);
}
} else {
dol_syslog("Failed to read invoice", LOG_ERR);
}
}
} else {
dol_syslog("No invoice to process");
}
}
$ok = 0;
// Withdraw invoices in factures_prev array
$out = count($factures_prev) . " invoices will be withdrawn.";
//print $out."\n";
dol_syslog($out);
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 withdraw receipt and build withdraw into disk
*/
$this->db->begin();
$now = dol_now();
/*
* Traitements
*/
if (!$error) {
$ref = "T" . substr($year, -2) . $month;
$sql = "SELECT count(*)";
$sql .= " FROM " . MAIN_DB_PREFIX . "prelevement_bons";
$sql .= " WHERE ref LIKE '" . $ref . "%'";
$sql .= " AND entity = " . $conf->entity;
dol_syslog(get_class($this) . "::Create sql=" . $sql, LOG_DEBUG);
$resql = $this->db->query($sql);
if ($resql) {
$row = $this->db->fetch_row($resql);
} else {
$error++;
dol_syslog("Erreur recherche reference");
}
$ref = $ref . substr("00" . ($row[0] + 1), -2);
$filebonprev = $ref;
// Create withdraw receipt in database
$sql = "INSERT INTO " . MAIN_DB_PREFIX . "prelevement_bons (";
$sql .= " ref, entity, datec";
$sql .= ") VALUES (";
$sql .= "'" . $this->db->escape($ref) . "'";
$sql .= ", " . $conf->entity;
$sql .= ", '" . $this->db->idate($now) . "'";
$sql .= ")";
dol_syslog(get_class($this) . "::Create sql=" . $sql, LOG_DEBUG);
$resql = $this->db->query($sql);
if ($resql) {
$prev_id = $this->db->last_insert_id(MAIN_DB_PREFIX . "prelevement_bons");
$dir = $conf->prelevement->dir_output . '/receipts';
$file = $filebonprev;
if (!is_dir($dir)) {
dol_mkdir($dir);
}
$bonprev = new BonPrelevement($this->db, $dir . "/" . $file);
$bonprev->id = $prev_id;
} else {
$error++;
dol_syslog("Erreur creation du bon de prelevement");
}
}
/*
* Create withdrawal receipt
*/
if (!$error) {
if (count($factures_prev) > 0) {
foreach ($factures_prev as $fac) {
// Fetch invoice
$fact = new Facture($this->db);
$fact->fetch($fac[0]);
/*
* Add standing order
*
*
* $fac[3] : banque
* $fac[4] : guichet
* $fac[5] : number
* $fac[6] : cle rib
* $fac[7] : amount
* $fac[8] : client nom
* $fac[2] : client id
*/
$ri = $bonprev->AddFacture($fac[0], $fac[2], $fac[8], $fac[7], $fac[3], $fac[4], $fac[5], $fac[6]);
if ($ri != 0) {
$error++;
}
/*
* Update orders
*
*/
$sql = "UPDATE " . MAIN_DB_PREFIX . "prelevement_facture_demande";
$sql .= " SET traite = 1";
$sql .= ", date_traite = '" . $this->db->idate($now) . "'";
$sql .= ", fk_prelevement_bons = " . $prev_id;
$sql .= " WHERE rowid = " . $fac[1];
dol_syslog(get_class($this) . "::Create sql=" . $sql, LOG_DEBUG);
$resql = $this->db->query($sql);
if (!$resql) {
$error++;
dol_syslog("Erreur mise a jour des demandes");
dol_syslog($this->db->error());
}
}
}
}
if (!$error) {
/*
* Withdraw receipt
*/
dol_syslog("Debut prelevement - Nombre de factures " . count($factures_prev));
if (count($factures_prev) > 0) {
$bonprev->date_echeance = $datetimeprev;
$bonprev->reference_remise = $ref;
$bonprev->numero_national_emetteur = $conf->global->PRELEVEMENT_NUMERO_NATIONAL_EMETTEUR;
$bonprev->raison_sociale = $conf->global->PRELEVEMENT_RAISON_SOCIALE;
$bonprev->emetteur_code_banque = $conf->global->PRELEVEMENT_CODE_BANQUE;
$bonprev->emetteur_code_guichet = $conf->global->PRELEVEMENT_CODE_GUICHET;
$bonprev->emetteur_numero_compte = $conf->global->PRELEVEMENT_NUMERO_COMPTE;
$bonprev->emetteur_number_key = $conf->global->PRELEVEMENT_NUMBER_KEY;
$bonprev->emetteur_iban = $conf->global->PRELEVEMENT_IBAN;
$bonprev->emetteur_bic = $conf->global->PRELEVEMENT_BIC;
$bonprev->emetteur_ics = $conf->global->PRELEVEMENT_ICS;
// TODO Add this into setup of admin/prelevement.php. Ex: PRELEVEMENT_ICS = "FR78ZZZ123456";
$bonprev->factures = $factures_prev_id;
//Build file
$bonprev->generate();
}
dol_syslog($filebonprev);
dol_syslog("Fin prelevement");
}
/*
* Update total
*/
$sql = "UPDATE " . MAIN_DB_PREFIX . "prelevement_bons";
$sql .= " SET amount = " . price2num($bonprev->total);
$sql .= " WHERE rowid = " . $prev_id;
$sql .= " AND entity = " . $conf->entity;
dol_syslog(get_class($this) . "::Create sql=" . $sql, LOG_DEBUG);
$resql = $this->db->query($sql);
if (!$resql) {
$error++;
dol_syslog("Erreur mise a jour du total - {$sql}");
}
/*
* Rollback or Commit
*/
if (!$error) {
$this->db->commit();
} else {
$this->db->rollback();
dol_syslog("Error", LOG_ERR);
}
return count($factures_prev);
} else {
return 0;
}
}