dolibarr 21.0.0-beta
export-bank-receipts.php
Go to the documentation of this file.
1#!/usr/bin/env php
2<?php
3/*
4 * Copyright (C) 2013 Laurent Destailleur <eldy@users.sourceforge.net>
5 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
6 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
7 *
8 * This program is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 3 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <https://www.gnu.org/licenses/>.
20 */
21
28if (!defined('NOSESSION')) {
29 define('NOSESSION', '1');
30}
31
32$sapi_type = php_sapi_name();
33$script_file = basename(__FILE__);
34$path = __DIR__.'/';
35
36// Test if batch mode
37if (substr($sapi_type, 0, 3) == 'cgi') {
38 echo "Error: You are using PHP for CGI. To execute ".$script_file." from command line, you must use PHP for CLI mode.\n";
39 exit(1);
40}
41
42require_once $path."../../htdocs/master.inc.php";
43require_once DOL_DOCUMENT_ROOT.'/core/lib/functionscli.lib.php';
44require_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php';
45require_once DOL_DOCUMENT_ROOT.'/core/lib/bank.lib.php';
46require_once DOL_DOCUMENT_ROOT.'/societe/class/societe.class.php';
47require_once DOL_DOCUMENT_ROOT.'/adherents/class/adherent.class.php';
48require_once DOL_DOCUMENT_ROOT.'/compta/sociales/class/chargesociales.class.php';
49require_once DOL_DOCUMENT_ROOT.'/compta/paiement/class/paiement.class.php';
50require_once DOL_DOCUMENT_ROOT.'/compta/tva/class/tva.class.php';
51require_once DOL_DOCUMENT_ROOT.'/fourn/class/paiementfourn.class.php';
52require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/account.class.php';
53require_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php';
54require_once DOL_DOCUMENT_ROOT.'/fourn/class/fournisseur.facture.class.php';
55require_once DOL_DOCUMENT_ROOT.'/compta/tva/class/tva.class.php';
56require_once DOL_DOCUMENT_ROOT.'/compta/sociales/class/paymentsocialcontribution.class.php';
63// Global variables
64$version = DOL_VERSION;
65$error = 0;
66
67$hookmanager->initHooks(array('cli'));
68
69
70/*
71 * Main
72 */
73
74@set_time_limit(0);
75print "***** ".$script_file." (".$version.") pid=".dol_getmypid()." *****\n";
76dol_syslog($script_file." launched with arg ".join(',', $argv));
77
78if (!isset($argv[3]) || !$argv[3]) {
79 print "Usage: ".$script_file." bank_ref [bank_receipt_number|all] (csv|tsv|excel|excel2007) [lang=xx_XX]\n";
80 exit(1);
81}
82$bankref = $argv[1];
83$num = $argv[2];
84$model = $argv[3];
85$newlangid = 'en_EN'; // To force a new lang id
86
87$invoicestatic = new Facture($db);
88$invoicesupplierstatic = new FactureFournisseur($db);
89$societestatic = new Societe($db);
90$chargestatic = new ChargeSociales($db);
91$memberstatic = new Adherent($db);
92$paymentstatic = new Paiement($db);
93$paymentsupplierstatic = new PaiementFourn($db);
94$paymentsocialcontributionstatic = new PaymentSocialContribution($db);
95$paymentvatstatic = new Tva($db);
96$bankstatic = new Account($db);
97$banklinestatic = new AccountLine($db);
98
99// Parse parameters
100foreach ($argv as $key => $value) {
101 $found = false;
102
103 // Define options
104 if (preg_match('/^lang=/i', $value)) {
105 $found = true;
106 $valarray = explode('=', $value);
107 $newlangid = $valarray[1];
108 print 'Use language '.$newlangid.".\n";
109 }
110}
111$outputlangs = $langs;
112if (!empty($newlangid)) {
113 if ($outputlangs->defaultlang != $newlangid) {
114 $outputlangs = new Translate("", $conf);
115 $outputlangs->setDefaultLang($newlangid);
116 }
117}
118
119// Load translation files required by the page
120$outputlangs->loadLangs(array("main", "companies", "bills", "banks", "members", "compta"));
121
122$acct = new Account($db);
123$result = $acct->fetch('', $bankref);
124if ($result <= 0) {
125 print "Failed to find bank account with ref ".$bankref.".\n";
126 exit(1);
127} else {
128 print "Export for bank account ".$acct->ref." (".$acct->label.").\n";
129}
130
131// Create the export class for the model ExportXXX
132$dir = DOL_DOCUMENT_ROOT."/core/modules/export/";
133$file = "export_".$model.".modules.php";
134$classname = "Export".$model;
135if (!dol_is_file($dir.$file)) {
136 print "No driver to export with format ".$model."\n";
137 exit(1);
138}
139require_once $dir.$file;
140$objmodel = new $classname($db);
141
142// Define target path
143$dirname = $conf->bank->dir_temp;
144$filename = 'export-bank-receipts-'.$bankref.'-'.$num.'.'.$objmodel->extension;
145
146$array_fields = array(
147 'bankreceipt' => $outputlangs->transnoentitiesnoconv("AccountStatementShort"),
148 'bankaccount' => $outputlangs->transnoentitiesnoconv("BankAccount"),
149 'dateop' => $outputlangs->transnoentitiesnoconv("DateOperationShort"),
150 'dateval' => $outputlangs->transnoentitiesnoconv("DateValueShort"),
151 'type' => $outputlangs->transnoentitiesnoconv("Type"),
152 'description' => $outputlangs->transnoentitiesnoconv("Description"),
153 'thirdparty' => $outputlangs->transnoentitiesnoconv("Tiers"),
154 'accountelem' => $outputlangs->transnoentitiesnoconv("Piece"),
155 'debit' => $outputlangs->transnoentitiesnoconv("Debit"),
156 'credit' => $outputlangs->transnoentitiesnoconv("Credit"),
157 'soldbefore' => $outputlangs->transnoentitiesnoconv("BankBalanceBefore"),
158 'soldafter' => $outputlangs->transnoentitiesnoconv("BankBalanceAfter"),
159 'comment' => $outputlangs->transnoentitiesnoconv("Comment")
160);
161$array_selected = array('bankreceipt' => 'bankreceipt', 'bankaccount' => 'bankaccount', 'dateop' => 'dateop', 'dateval' => 'dateval', 'type' => 'type', 'description' => 'description', 'thirdparty' => 'thirdparty', 'accountelem' => 'accountelem', 'debit' => 'debit', 'credit' => 'credit', 'soldbefore' => 'soldbefore', 'soldafter' => 'soldafter', 'comment' => 'comment');
162$array_export_TypeFields = array('bankreceipt' => 'Text', 'bankaccount' => 'Text', 'dateop' => 'Date', 'dateval' => 'Date', 'type' => 'Text', 'description' => 'Text', 'thirdparty' => 'Text', 'accountelem' => 'Text', 'debit' => 'Number', 'credit' => 'Number', 'soldbefore' => 'Number', 'soldafter' => 'Number', 'comment' => 'Text');
163
164// Build request to find records for a bank account/receipt
165$listofnum = "";
166if (!empty($num) && $num != "all") {
167 $listofnum .= "'";
168 $arraynum = explode(',', $num);
169 foreach ($arraynum as $val) {
170 if ($listofnum != "'") {
171 $listofnum .= "','";
172 }
173 $listofnum .= $val;
174 }
175 $listofnum .= "'";
176}
177$sql = "SELECT b.rowid, b.dateo as do, b.datev as dv,";
178$sql .= " b.amount, b.label, b.rappro, b.num_releve, b.num_chq, b.fk_type,";
179$sql .= " ba.rowid as bankid, ba.ref as bankref, ba.label as banklabel";
180$sql .= " FROM ".MAIN_DB_PREFIX."bank_account as ba";
181$sql .= ", ".MAIN_DB_PREFIX."bank as b";
182$sql .= " WHERE b.fk_account = ".((int) $acct->id);
183if ($listofnum) {
184 $sql .= " AND b.num_releve IN (".$db->sanitize($listofnum, 1).")";
185}
186if (!isset($num)) {
187 $sql .= " OR b.num_releve is null";
188}
189$sql .= " AND b.fk_account = ba.rowid";
190$sql .= $db->order("b.num_releve, b.datev, b.datec", "ASC"); // We add date of creation to have correct order when everything is done the same day
191// print $sql;
192
193$resql = $db->query($sql);
194if ($resql) {
195 $balancebefore = array();
196
197 $numrows = $db->num_rows($resql);
198
199 if ($numrows > 0) {
200 // Open file
201 print 'Open file '.$filename.' into directory '.$dirname."\n";
202 dol_mkdir($dirname);
203 $result = $objmodel->open_file($dirname."/".$filename, $outputlangs);
204
205 if ($result < 0) {
206 print 'Failed to create file '.$filename.' into dir '.$dirname.'.'."\n";
207 return -1;
208 }
209
210 // Genere en-tete
211 $objmodel->write_header($outputlangs);
212
213 // Genere ligne de titre
214 $objmodel->write_title($array_fields, $array_selected, $outputlangs, $array_export_TypeFields);
215 }
216
217 $i = 0;
218 $total = 0;
219 $totald = 0;
220 $totalc = 0;
221 while ($i < $numrows) {
222 $thirdparty = '';
223 $accountelem = '';
224 $comment = '';
225
226 $objp = $db->fetch_object($resql);
227
228 // Calculate start balance
229 if (!isset($balancebefore[$objp->num_releve])) {
230 print 'Calculate start balance for receipt '.$objp->num_releve."\n";
231
232 $sql2 = "SELECT sum(b.amount) as amount";
233 $sql2 .= " FROM ".MAIN_DB_PREFIX."bank as b";
234 $sql2 .= " WHERE b.num_releve < '".$db->escape($objp->num_releve)."'";
235 $sql2 .= " AND b.fk_account = ".$objp->bankid;
236 $resql2 = $db->query($sql2);
237 if ($resql2) {
238 $obj2 = $db->fetch_object($resql2);
239 $balancebefore[$objp->num_releve] = ($obj2->amount ? $obj2->amount : 0);
240 $db->free($resql2);
241 } else {
242 dol_print_error($db);
243 exit(1);
244 }
245
246 $total = $balancebefore[$objp->num_releve];
247 }
248
249 $totalbefore = $total;
250 $total = $total + $objp->amount;
251
252 // Date operation
253 $dateop = $db->jdate($objp->do);
254
255 // Date de valeur
256 $datevalue = $db->jdate($objp->dv);
257
258 // Num cheque
259 $numchq = ($objp->num_chq ? $objp->num_chq : '');
260
261 // Libelle
262 $reg = array();
263 preg_match('/\‍((.+)\‍)/i', $objp->label, $reg); // Si texte entoure de parentheses on tente recherche de traduction
264 if ($reg[1] && $langs->transnoentitiesnoconv($reg[1]) != $reg[1]) {
265 $description = $langs->transnoentitiesnoconv($reg[1]);
266 } else {
267 $description = $objp->label;
268 }
269
270 /*
271 * Add links (societe, company...)
272 */
273 $links = $acct->get_url($objp->rowid);
274 foreach ($links as $key => $val) {
275 if ($links[$key]['type'] == 'payment') {
276 $paymentstatic->fetch($links[$key]['url_id']);
277 $tmparray = $paymentstatic->getBillsArray('');
278 if (is_array($tmparray)) {
279 foreach ($tmparray as $tmpkey => $tmpval) {
280 $invoicestatic->fetch($tmpval);
281 if ($accountelem) {
282 $accountelem .= ', ';
283 }
284 $accountelem .= $invoicestatic->ref;
285 }
286 }
287 } elseif ($links[$key]['type'] == 'payment_supplier') {
288 $paymentsupplierstatic->fetch($links[$key]['url_id']);
289 $tmparray = $paymentsupplierstatic->getBillsArray('');
290 if (is_array($tmparray)) {
291 foreach ($tmparray as $tmpkey => $tmpval) {
292 $invoicesupplierstatic->fetch($tmpval);
293 if ($accountelem) {
294 $accountelem .= ', ';
295 }
296 $accountelem .= $invoicesupplierstatic->ref;
297 }
298 }
299 } elseif ($links[$key]['type'] == 'payment_sc') {
300 $paymentsocialcontributionstatic->fetch($links[$key]['url_id']);
301 if ($accountelem) {
302 $accountelem .= ', ';
303 }
304 $accountelem .= $langs->transnoentitiesnoconv("SocialContribution").' '.$paymentsocialcontributionstatic->ref;
305 } elseif ($links[$key]['type'] == 'payment_vat') {
306 $paymentvatstatic->fetch($links[$key]['url_id']);
307 if ($accountelem) {
308 $accountelem .= ', ';
309 }
310 $accountelem .= $langs->transnoentitiesnoconv("VATPayments").' '.$paymentvatstatic->ref;
311 } elseif ($links[$key]['type'] == 'banktransfert') {
312 $comment = $outputlangs->transnoentitiesnoconv("Transfer");
313 if ($objp->amount > 0) {
314 if ($comment) {
315 $comment .= ' ';
316 }
317 $banklinestatic->fetch($links[$key]['url_id']);
318 $bankstatic->id = $banklinestatic->fk_account;
319 $bankstatic->label = $banklinestatic->bank_account_label;
320 $comment .= ' ('.$langs->transnoentitiesnoconv("from").' ';
321 $comment .= $bankstatic->getNomUrl(1, 'transactions');
322 $comment .= ' '.$langs->transnoentitiesnoconv("toward").' ';
323 $bankstatic->id = $objp->bankid;
324 $bankstatic->label = $objp->bankref;
325 $comment .= $bankstatic->getNomUrl(1, '');
326 $comment .= ')';
327 } else {
328 if ($comment) {
329 $comment .= ' ';
330 }
331 $bankstatic->id = $objp->bankid;
332 $bankstatic->label = $objp->bankref;
333 $comment .= ' ('.$langs->transnoentitiesnoconv("from").' ';
334 $comment .= $bankstatic->getNomUrl(1, '');
335 $comment .= ' '.$langs->transnoentitiesnoconv("toward").' ';
336 $banklinestatic->fetch($links[$key]['url_id']);
337 $bankstatic->id = $banklinestatic->fk_account;
338 $bankstatic->label = $banklinestatic->bank_account_label;
339 $comment .= $bankstatic->getNomUrl(1, 'transactions');
340 $comment .= ')';
341 }
342 } elseif ($links[$key]['type'] == 'company') {
343 if ($thirdparty) {
344 $thirdparty .= ', ';
345 }
346 $thirdparty .= dol_trunc($links[$key]['label'], 24);
347 $newline = 0;
348 } elseif ($links[$key]['type'] == 'member') {
349 if ($thirdparty) {
350 $accountelem .= ', ';
351 }
352 $thirdparty .= $links[$key]['label'];
353 $newline = 0;
354 }
355 /*
356 * elseif ($links[$key]['type']=='sc')
357 * {
358 * if ($accountelem) $accountelem.= ', ';
359 * //$accountelem.= '<a href="'.DOL_URL_ROOT.'/compta/sociales/card.php?id='.$links[$key]['url_id'].'">';
360 * //$accountelem.= img_object($langs->transnoentitiesnoconv('ShowBill'),'bill').' ';
361 * $accountelem.= $langs->transnoentitiesnoconv("SocialContribution");
362 * //$accountelem.= '</a>';
363 * $newline=0;
364 * }
365 * else
366 * {
367 * if ($accountelem) $accountelem.= ', ';
368 * //$accountelem.= '<a href="'.$links[$key]['url'].$links[$key]['url_id'].'">';
369 * $accountelem.= $links[$key]['label'];
370 * //$accountelem.= '</a>';
371 * $newline=0;
372 * }
373 */
374 }
375
376 $debit = $credit = '';
377 if ($objp->amount < 0) {
378 $totald = $totald + abs($objp->amount);
379 $debit = price2num($objp->amount * -1);
380 } else {
381 $totalc = $totalc + abs($objp->amount);
382 $credit = price2num($objp->amount);
383 }
384
385 $i++;
386
387 $rec = new stdClass();
388 $rec->bankreceipt = $objp->num_releve;
389 $rec->bankaccount = $objp->banklabel;
390 $rec->dateop = dol_print_date($dateop, 'dayrfc');
391 $rec->dateval = dol_print_date($datevalue, 'dayrfc');
392 $rec->type = $objp->fk_type.' '.($objp->num_chq ? $objp->num_chq : '');
393 $rec->description = $description;
394 $rec->thirdparty = $thirdparty;
395 $rec->accountelem = $accountelem;
396 $rec->debit = $debit;
397 $rec->credit = $credit;
398 $rec->comment = $comment;
399 $rec->soldbefore = price2num($totalbefore);
400 $rec->soldafter = price2num($total);
401
402 // end of special operation processing
403 $objmodel->write_record($array_selected, $rec, $outputlangs, $array_export_TypeFields);
404 }
405
406 if ($numrows > 0) {
407 print "Found ".$numrows." records for receipt ".$num."\n";
408
409 // Genere en-tete
410 $objmodel->write_footer($outputlangs);
411
412 // Close file
413 $objmodel->close_file();
414
415 print 'File '.$filename.' was generated into dir '.$dirname.'.'."\n";
416
417 $ret = 0;
418 } else {
419 print "No records found for receipt ".$num."\n";
420
421 $ret = 0;
422 }
423} else {
424 dol_print_error($db);
425 $ret = 1;
426}
427
428$db->close();
429
430exit($ret);
Class to manage bank accounts.
Class to manage bank transaction lines.
Class to manage members of a foundation.
Class for managing the social charges.
Class to manage suppliers invoices.
Class to manage invoices.
Class to manage payments for supplier invoices.
Class to manage payments of customer invoices.
Class to manage payments of social contributions.
Class to manage third parties objects (customers, suppliers, prospects...)
Class to manage translations.
Class to manage VAT - Value-added tax (also known in French as TVA - Taxe sur la valeur ajoutée)
Definition tva.class.php:38
dol_is_file($pathoffile)
Return if path is a file.
dol_getmypid()
Return getmypid() or random PID when function is disabled Some web hosts disable this php function fo...
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
dol_trunc($string, $size=40, $trunc='right', $stringencoding='UTF-8', $nodot=0, $display=0)
Truncate a string to a particular length adding '…' if string larger than length.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_mkdir($dir, $dataroot='', $newmask='')
Creation of a directory (this can create recursive subdir)
global $conf
The following vars must be defined: $type2label $form $conf, $lang, The following vars may also be de...
Definition member.php:79