dolibarr.bankimport/repair.php

<?php
/* Copyright (C) 2026  Eduard Wisch  <data@data-it-solution.de>
 *
 * 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.
 */

/**
 * \file    bankimport/repair.php
 * \ingroup bankimport
 * \brief   Repair orphaned BankImport transactions
 */

// Load Dolibarr environment
$res = 0;
if (!$res && !empty($_SERVER["CONTEXT_DOCUMENT_ROOT"])) {
	$res = @include $_SERVER["CONTEXT_DOCUMENT_ROOT"]."/main.inc.php";
}
$tmp = empty($_SERVER['SCRIPT_FILENAME']) ? '' : $_SERVER['SCRIPT_FILENAME'];
$tmp2 = realpath(__FILE__);
$i = strlen($tmp) - 1;
$j = strlen($tmp2) - 1;
while ($i > 0 && $j > 0 && isset($tmp[$i]) && isset($tmp2[$j]) && $tmp[$i] == $tmp2[$j]) {
	$i--;
	$j--;
}
if (!$res && $i > 0 && file_exists(substr($tmp, 0, ($i + 1))."/main.inc.php")) {
	$res = @include substr($tmp, 0, ($i + 1))."/main.inc.php";
}
if (!$res && $i > 0 && file_exists(dirname(substr($tmp, 0, ($i + 1)))."/main.inc.php")) {
	$res = @include dirname(substr($tmp, 0, ($i + 1)))."/main.inc.php";
}
if (!$res && file_exists("../main.inc.php")) {
	$res = @include "../main.inc.php";
}
if (!$res && file_exists("../../main.inc.php")) {
	$res = @include "../../main.inc.php";
}
if (!$res) {
	die("Include of main fails");
}

require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
dol_include_once('/bankimport/class/banktransaction.class.php');
dol_include_once('/bankimport/lib/bankimport.lib.php');

/**
 * @var Conf $conf
 * @var DoliDB $db
 * @var Translate $langs
 * @var User $user
 */

$langs->loadLangs(array("bankimport@bankimport", "banks", "bills", "admin"));

// Security check - only admin
if (!$user->admin) {
	accessforbidden();
}

$action = GETPOST('action', 'aZ09');

/*
 * Actions
 */

// Repair a single transaction
if ($action == 'repair' && GETPOSTINT('transid') > 0) {
	$transid = GETPOSTINT('transid');
	$paymentId = GETPOSTINT('payment_id');
	$bankId = GETPOSTINT('bank_id');
	$invoiceId = GETPOSTINT('invoice_id');
	$invoiceType = GETPOST('invoice_type', 'alpha');

	$db->begin();

	$sql = "UPDATE ".MAIN_DB_PREFIX."bankimport_transaction SET";
	$sql .= " status = 1";
	if ($paymentId > 0) {
		if ($invoiceType == 'facture') {
			$sql .= ", fk_paiement = ".((int) $paymentId);
		} else {
			$sql .= ", fk_paiementfourn = ".((int) $paymentId);
		}
	}
	if ($bankId > 0) {
		$sql .= ", fk_bank = ".((int) $bankId);
	}
	if ($invoiceId > 0) {
		if ($invoiceType == 'facture') {
			$sql .= ", fk_facture = ".((int) $invoiceId);
		} else {
			$sql .= ", fk_facture_fourn = ".((int) $invoiceId);
		}
	}
	$sql .= " WHERE rowid = ".((int) $transid);

	$result = $db->query($sql);
	if ($result) {
		$db->commit();
		setEventMessages($langs->trans("RecordModifiedSuccessfully"), null, 'mesgs');
	} else {
		$db->rollback();
		setEventMessages($db->lasterror(), null, 'errors');
	}

	header("Location: ".$_SERVER["PHP_SELF"]."?token=".newToken());
	exit;
}

// Repair all found orphans
if ($action == 'repairall') {
	$orphans = findOrphanedTransactions($db, $conf);
	$repaired = 0;
	$failed = 0;

	$db->begin();

	foreach ($orphans as $orphan) {
		$sql = "UPDATE ".MAIN_DB_PREFIX."bankimport_transaction SET";
		$sql .= " status = 1";
		if (!empty($orphan['payment_id'])) {
			if ($orphan['invoice_type'] == 'facture') {
				$sql .= ", fk_paiement = ".((int) $orphan['payment_id']);
			} else {
				$sql .= ", fk_paiementfourn = ".((int) $orphan['payment_id']);
			}
		}
		if (!empty($orphan['bank_id'])) {
			$sql .= ", fk_bank = ".((int) $orphan['bank_id']);
		}
		if (!empty($orphan['invoice_id'])) {
			if ($orphan['invoice_type'] == 'facture') {
				$sql .= ", fk_facture = ".((int) $orphan['invoice_id']);
			} else {
				$sql .= ", fk_facture_fourn = ".((int) $orphan['invoice_id']);
			}
		}
		$sql .= " WHERE rowid = ".((int) $orphan['trans_id']);

		if ($db->query($sql)) {
			$repaired++;
		} else {
			$failed++;
		}
	}

	if ($failed == 0) {
		$db->commit();
		setEventMessages($langs->trans("RecordsModified", $repaired), null, 'mesgs');
	} else {
		$db->rollback();
		setEventMessages($langs->trans("ErrorsOccurred", $failed), null, 'errors');
	}

	header("Location: ".$_SERVER["PHP_SELF"]."?token=".newToken());
	exit;
}

/**
 * Find orphaned BankImport transactions
 * These are transactions with status=0 (NEW) but where payments already exist in Dolibarr
 */
function findOrphanedTransactions($db, $conf)
{
	$orphans = array();

	// Find customer invoice orphans
	$sql = "SELECT bt.rowid as trans_id, bt.ref as trans_ref, bt.amount, bt.name, bt.date_trans,";
	$sql .= " f.rowid as invoice_id, f.ref as invoice_ref, f.total_ttc,";
	$sql .= " p.rowid as payment_id, p.fk_bank as bank_id";
	$sql .= " FROM ".MAIN_DB_PREFIX."bankimport_transaction bt";
	$sql .= " JOIN ".MAIN_DB_PREFIX."facture f ON (";
	$sql .= "   bt.description LIKE CONCAT('%', f.ref, '%')";
	$sql .= "   OR bt.end_to_end_id LIKE CONCAT('%', f.ref, '%')";
	$sql .= " )";
	$sql .= " JOIN ".MAIN_DB_PREFIX."paiement_facture pf ON pf.fk_facture = f.rowid";
	$sql .= " JOIN ".MAIN_DB_PREFIX."paiement p ON p.rowid = pf.fk_paiement";
	$sql .= " WHERE bt.status = 0";
	$sql .= " AND bt.fk_paiement IS NULL";
	$sql .= " AND p.fk_bank IS NOT NULL";
	$sql .= " AND f.entity = ".$conf->entity;

	$resql = $db->query($sql);
	if ($resql) {
		while ($obj = $db->fetch_object($resql)) {
			$orphans[] = array(
				'trans_id' => $obj->trans_id,
				'trans_ref' => $obj->trans_ref,
				'trans_amount' => $obj->amount,
				'trans_name' => $obj->name,
				'trans_date' => $obj->date_trans,
				'invoice_id' => $obj->invoice_id,
				'invoice_ref' => $obj->invoice_ref,
				'invoice_amount' => $obj->total_ttc,
				'payment_id' => $obj->payment_id,
				'bank_id' => $obj->bank_id,
				'invoice_type' => 'facture'
			);
		}
	}

	// Find supplier invoice orphans
	$sql = "SELECT bt.rowid as trans_id, bt.ref as trans_ref, bt.amount, bt.name, bt.date_trans,";
	$sql .= " f.rowid as invoice_id, f.ref as invoice_ref, f.ref_supplier, f.total_ttc,";
	$sql .= " p.rowid as payment_id, p.fk_bank as bank_id";
	$sql .= " FROM ".MAIN_DB_PREFIX."bankimport_transaction bt";
	$sql .= " JOIN ".MAIN_DB_PREFIX."facture_fourn f ON (";
	$sql .= "   bt.description LIKE CONCAT('%', f.ref, '%')";
	$sql .= "   OR bt.description LIKE CONCAT('%', f.ref_supplier, '%')";
	$sql .= "   OR bt.end_to_end_id LIKE CONCAT('%', f.ref, '%')";
	$sql .= " )";
	$sql .= " JOIN ".MAIN_DB_PREFIX."paiementfourn_facturefourn pff ON pff.fk_facturefourn = f.rowid";
	$sql .= " JOIN ".MAIN_DB_PREFIX."paiementfourn p ON p.rowid = pff.fk_paiementfourn";
	$sql .= " WHERE bt.status = 0";
	$sql .= " AND bt.fk_paiementfourn IS NULL";
	$sql .= " AND p.fk_bank IS NOT NULL";
	$sql .= " AND f.entity = ".$conf->entity;

	$resql = $db->query($sql);
	if ($resql) {
		while ($obj = $db->fetch_object($resql)) {
			$orphans[] = array(
				'trans_id' => $obj->trans_id,
				'trans_ref' => $obj->trans_ref,
				'trans_amount' => $obj->amount,
				'trans_name' => $obj->name,
				'trans_date' => $obj->date_trans,
				'invoice_id' => $obj->invoice_id,
				'invoice_ref' => $obj->invoice_ref,
				'invoice_ref_supplier' => $obj->ref_supplier,
				'invoice_amount' => $obj->total_ttc,
				'payment_id' => $obj->payment_id,
				'bank_id' => $obj->bank_id,
				'invoice_type' => 'facture_fourn'
			);
		}
	}

	return $orphans;
}

/*
 * View
 */

$title = $langs->trans("RepairOrphanedTransactions");

llxHeader('', $title, '', '', 0, 0, '', '', '', 'mod-bankimport page-repair');

print load_fiche_titre($title, '', 'tools');

// Find orphans
$orphans = findOrphanedTransactions($db, $conf);

print '<div class="info">';
print img_picto('', 'info', 'class="pictofixedwidth"');
print $langs->trans("RepairOrphanedTransactionsDesc");
print '</div>';
print '<br>';

if (empty($orphans)) {
	print '<div class="opacitymedium">'.$langs->trans("NoOrphanedTransactionsFound").'</div>';
} else {
	print '<div class="warning">';
	print img_picto('', 'warning', 'class="pictofixedwidth"');
	print sprintf($langs->trans("OrphanedTransactionsFound"), count($orphans));
	print '</div>';
	print '<br>';

	// Repair all button
	print '<div class="tabsAction">';
	print '<a class="butAction" href="'.$_SERVER["PHP_SELF"].'?action=repairall&token='.newToken().'">'.$langs->trans("RepairAll").' ('.count($orphans).')</a>';
	print '</div>';
	print '<br>';

	// Table
	print '<div class="div-table-responsive">';
	print '<table class="noborder centpercent">';

	print '<tr class="liste_titre">';
	print '<th>'.$langs->trans("Date").'</th>';
	print '<th>'.$langs->trans("TransactionRef").'</th>';
	print '<th>'.$langs->trans("Counterparty").'</th>';
	print '<th class="right">'.$langs->trans("Amount").'</th>';
	print '<th>&rarr;</th>';
	print '<th>'.$langs->trans("Invoice").'</th>';
	print '<th class="right">'.$langs->trans("Amount").'</th>';
	print '<th>'.$langs->trans("Payment").'</th>';
	print '<th>'.$langs->trans("BankEntry").'</th>';
	print '<th class="center">'.$langs->trans("Action").'</th>';
	print '</tr>';

	foreach ($orphans as $orphan) {
		print '<tr class="oddeven">';

		// Date
		print '<td class="nowraponall">'.dol_print_date($orphan['trans_date'], 'day').'</td>';

		// Transaction ref
		print '<td>';
		print '<a href="'.dol_buildpath('/bankimport/card.php', 1).'?id='.$orphan['trans_id'].'">';
		print dol_trunc($orphan['trans_ref'], 12);
		print '</a>';
		print '</td>';

		// Counterparty
		print '<td>'.dol_escape_htmltag($orphan['trans_name']).'</td>';

		// Transaction amount
		print '<td class="right nowraponall">';
		if ($orphan['trans_amount'] >= 0) {
			print '<span style="color: green;">'.price($orphan['trans_amount'], 0, $langs, 1, -1, 2, 'EUR').'</span>';
		} else {
			print '<span style="color: red;">'.price($orphan['trans_amount'], 0, $langs, 1, -1, 2, 'EUR').'</span>';
		}
		print '</td>';

		// Arrow
		print '<td class="center">&rarr;</td>';

		// Invoice
		print '<td class="nowraponall">';
		if ($orphan['invoice_type'] == 'facture') {
			require_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php';
			$inv = new Facture($db);
			$inv->fetch($orphan['invoice_id']);
			print $inv->getNomUrl(1);
		} else {
			require_once DOL_DOCUMENT_ROOT.'/fourn/class/fournisseur.facture.class.php';
			$inv = new FactureFournisseur($db);
			$inv->fetch($orphan['invoice_id']);
			print $inv->getNomUrl(1);
			if (!empty($orphan['invoice_ref_supplier'])) {
				print ' <span class="opacitymedium small">('.$orphan['invoice_ref_supplier'].')</span>';
			}
		}
		print '</td>';

		// Invoice amount
		print '<td class="right nowraponall">'.price($orphan['invoice_amount'], 0, $langs, 1, -1, 2, 'EUR').'</td>';

		// Payment
		print '<td class="nowraponall">';
		if ($orphan['invoice_type'] == 'facture') {
			require_once DOL_DOCUMENT_ROOT.'/compta/paiement/class/paiement.class.php';
			$pay = new Paiement($db);
			$pay->fetch($orphan['payment_id']);
			print $pay->getNomUrl(1);
		} else {
			require_once DOL_DOCUMENT_ROOT.'/fourn/class/paiementfourn.class.php';
			$pay = new PaiementFourn($db);
			$pay->fetch($orphan['payment_id']);
			print $pay->getNomUrl(1);
		}
		print '</td>';

		// Bank entry
		print '<td class="nowraponall">';
		print '<a href="'.DOL_URL_ROOT.'/compta/bank/line.php?rowid='.$orphan['bank_id'].'">';
		print img_picto('', 'bank_account', 'class="pictofixedwidth"');
		print '#'.$orphan['bank_id'];
		print '</a>';
		print '</td>';

		// Action
		print '<td class="center nowraponall">';
		print '<form method="POST" action="'.$_SERVER["PHP_SELF"].'" style="display: inline;">';
		print '<input type="hidden" name="token" value="'.newToken().'">';
		print '<input type="hidden" name="action" value="repair">';
		print '<input type="hidden" name="transid" value="'.$orphan['trans_id'].'">';
		print '<input type="hidden" name="payment_id" value="'.$orphan['payment_id'].'">';
		print '<input type="hidden" name="bank_id" value="'.$orphan['bank_id'].'">';
		print '<input type="hidden" name="invoice_id" value="'.$orphan['invoice_id'].'">';
		print '<input type="hidden" name="invoice_type" value="'.$orphan['invoice_type'].'">';
		print '<button type="submit" class="butActionSmall">'.$langs->trans("Repair").'</button>';
		print '</form>';
		print '</td>';

		print '</tr>';
	}

	print '</table>';
	print '</div>';
}

// Back link
print '<br>';
print '<div class="tabsAction">';
print '<a class="butAction" href="'.dol_buildpath('/bankimport/list.php', 1).'">'.$langs->trans("BackToList").'</a>';
print '</div>';

llxFooter();
$db->close();