28if (!defined(
'NOSESSION')) {
29 define(
'NOSESSION',
'1');
32$sapi_type = php_sapi_name();
33$script_file = basename(__FILE__);
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";
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';
64$version = DOL_VERSION;
67$hookmanager->initHooks(array(
'cli'));
75print
"***** ".$script_file.
" (".$version.
") pid=".
dol_getmypid().
" *****\n";
76dol_syslog($script_file.
" launched with arg ".join(
',', $argv));
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";
87$invoicestatic =
new Facture($db);
89$societestatic =
new Societe($db);
95$paymentvatstatic =
new Tva($db);
100foreach ($argv as $key => $value) {
104 if (preg_match(
'/^lang=/i', $value)) {
106 $valarray = explode(
'=', $value);
107 $newlangid = $valarray[1];
108 print
'Use language '.$newlangid.
".\n";
111$outputlangs = $langs;
112if (!empty($newlangid)) {
113 if ($outputlangs->defaultlang != $newlangid) {
115 $outputlangs->setDefaultLang($newlangid);
120$outputlangs->loadLangs(array(
"main",
"companies",
"bills",
"banks",
"members",
"compta"));
123$result = $acct->fetch(
'', $bankref);
125 print
"Failed to find bank account with ref ".$bankref.
".\n";
128 print
"Export for bank account ".$acct->ref.
" (".$acct->label.
").\n";
132$dir = DOL_DOCUMENT_ROOT.
"/core/modules/export/";
133$file =
"export_".$model.
".modules.php";
134$classname =
"Export".$model;
136 print
"No driver to export with format ".$model.
"\n";
139require_once $dir.$file;
140$objmodel =
new $classname($db);
143$dirname =
$conf->bank->dir_temp;
144$filename =
'export-bank-receipts-'.$bankref.
'-'.$num.
'.'.$objmodel->extension;
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")
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');
166if (!empty($num) && $num !=
"all") {
168 $arraynum = explode(
',', $num);
169 foreach ($arraynum as $val) {
170 if ($listofnum !=
"'") {
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);
184 $sql .=
" AND b.num_releve IN (".$db->sanitize($listofnum, 1).
")";
187 $sql .=
" OR b.num_releve is null";
189$sql .=
" AND b.fk_account = ba.rowid";
190$sql .= $db->order(
"b.num_releve, b.datev, b.datec",
"ASC");
193$resql = $db->query($sql);
195 $balancebefore = array();
197 $numrows = $db->num_rows($resql);
201 print
'Open file '.$filename.
' into directory '.$dirname.
"\n";
203 $result = $objmodel->open_file($dirname.
"/".$filename, $outputlangs);
206 print
'Failed to create file '.$filename.
' into dir '.$dirname.
'.'.
"\n";
211 $objmodel->write_header($outputlangs);
214 $objmodel->write_title($array_fields, $array_selected, $outputlangs, $array_export_TypeFields);
221 while ($i < $numrows) {
226 $objp = $db->fetch_object($resql);
229 if (!isset($balancebefore[$objp->num_releve])) {
230 print
'Calculate start balance for receipt '.$objp->num_releve.
"\n";
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);
238 $obj2 = $db->fetch_object($resql2);
239 $balancebefore[$objp->num_releve] = ($obj2->amount ? $obj2->amount : 0);
246 $total = $balancebefore[$objp->num_releve];
249 $totalbefore = $total;
250 $total = $total + $objp->amount;
253 $dateop = $db->jdate($objp->do);
256 $datevalue = $db->jdate($objp->dv);
259 $numchq = ($objp->num_chq ? $objp->num_chq :
'');
263 preg_match(
'/\((.+)\)/i', $objp->label, $reg);
264 if ($reg[1] && $langs->transnoentitiesnoconv($reg[1]) != $reg[1]) {
265 $description = $langs->transnoentitiesnoconv($reg[1]);
267 $description = $objp->label;
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);
282 $accountelem .=
', ';
284 $accountelem .= $invoicestatic->ref;
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);
294 $accountelem .=
', ';
296 $accountelem .= $invoicesupplierstatic->ref;
299 } elseif ($links[$key][
'type'] ==
'payment_sc') {
300 $paymentsocialcontributionstatic->fetch($links[$key][
'url_id']);
302 $accountelem .=
', ';
304 $accountelem .= $langs->transnoentitiesnoconv(
"SocialContribution").
' '.$paymentsocialcontributionstatic->ref;
305 } elseif ($links[$key][
'type'] ==
'payment_vat') {
306 $paymentvatstatic->fetch($links[$key][
'url_id']);
308 $accountelem .=
', ';
310 $accountelem .= $langs->transnoentitiesnoconv(
"VATPayments").
' '.$paymentvatstatic->ref;
311 } elseif ($links[$key][
'type'] ==
'banktransfert') {
312 $comment = $outputlangs->transnoentitiesnoconv(
"Transfer");
313 if ($objp->amount > 0) {
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,
'');
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');
342 } elseif ($links[$key][
'type'] ==
'company') {
346 $thirdparty .=
dol_trunc($links[$key][
'label'], 24);
348 } elseif ($links[$key][
'type'] ==
'member') {
350 $accountelem .=
', ';
352 $thirdparty .= $links[$key][
'label'];
376 $debit = $credit =
'';
377 if ($objp->amount < 0) {
378 $totald = $totald + abs($objp->amount);
381 $totalc = $totalc + abs($objp->amount);
388 $rec->bankreceipt = $objp->num_releve;
389 $rec->bankaccount = $objp->banklabel;
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);
403 $objmodel->write_record($array_selected, $rec, $outputlangs, $array_export_TypeFields);
407 print
"Found ".$numrows.
" records for receipt ".$num.
"\n";
410 $objmodel->write_footer($outputlangs);
413 $objmodel->close_file();
415 print
'File '.$filename.
' was generated into dir '.$dirname.
'.'.
"\n";
419 print
"No records found for receipt ".$num.
"\n";
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)
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...