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