26 if (!defined(
'NOSESSION')) {
27 define(
'NOSESSION',
'1');
30 $sapi_type = php_sapi_name();
31 $script_file = basename(__FILE__);
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";
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';
56 $version = DOL_VERSION;
64 print
"***** ".$script_file.
" (".$version.
") pid=".
dol_getmypid().
" *****\n";
65 dol_syslog($script_file.
" launched with arg ".join(
',', $argv));
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";
76 $invoicestatic =
new Facture($db);
78 $societestatic =
new Societe($db);
84 $paymentvatstatic =
new Tva($db);
89 foreach ($argv as $key => $value) {
93 if (preg_match(
'/^lang=/i', $value)) {
95 $valarray = explode(
'=', $value);
96 $newlangid = $valarray[1];
97 print
'Use language '.$newlangid.
".\n";
100 $outputlangs = $langs;
101 if (!empty($newlangid)) {
102 if ($outputlangs->defaultlang != $newlangid) {
104 $outputlangs->setDefaultLang($newlangid);
109 $outputlangs->loadLangs(array(
"main",
"companies",
"bills",
"banks",
"members",
"compta"));
112 $result = $acct->fetch(
'', $bankref);
114 print
"Failed to find bank account with ref ".$bankref.
".\n";
117 print
"Export for bank account ".$acct->ref.
" (".$acct->label.
").\n";
121 $dir = DOL_DOCUMENT_ROOT.
"/core/modules/export/";
122 $file =
"export_".$model.
".modules.php";
123 $classname =
"Export".$model;
125 print
"No driver to export with format ".$model.
"\n";
128 require_once $dir.$file;
129 $objmodel =
new $classname($db);
132 $dirname = $conf->bank->dir_temp;
133 $filename =
'export-bank-receipts-'.$bankref.
'-'.$num.
'.'.$objmodel->extension;
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")
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');
155 if (!empty($num) && $num !=
"all") {
157 $arraynum = explode(
',', $num);
158 foreach ($arraynum as $val) {
159 if ($listofnum !=
"'") {
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);
173 $sql .=
" AND b.num_releve IN (".$db->sanitize($listofnum, 1).
")";
176 $sql .=
" OR b.num_releve is null";
178 $sql .=
" AND b.fk_account = ba.rowid";
179 $sql .= $db->order(
"b.num_releve, b.datev, b.datec",
"ASC");
182 $resql = $db->query($sql);
184 $balancebefore = array();
186 $numrows = $db->num_rows(
$resql);
190 print
'Open file '.$filename.
' into directory '.$dirname.
"\n";
192 $result = $objmodel->open_file($dirname.
"/".$filename, $outputlangs);
195 print
'Failed to create file '.$filename.
' into dir '.$dirname.
'.'.
"\n";
200 $objmodel->write_header($outputlangs);
203 $objmodel->write_title($array_fields, $array_selected, $outputlangs, $array_export_TypeFields);
207 while ($i < $numrows) {
212 $objp = $db->fetch_object(
$resql);
215 if (!isset($balancebefore[$objp->num_releve])) {
216 print
'Calculate start balance for receipt '.$objp->num_releve.
"\n";
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);
224 $obj2 = $db->fetch_object($resql2);
225 $balancebefore[$objp->num_releve] = ($obj2->amount ? $obj2->amount : 0);
232 $total = $balancebefore[$objp->num_releve];
235 $totalbefore = $total;
236 $total = $total + $objp->amount;
239 $dateop = $db->jdate($objp->do);
242 $datevalue = $db->jdate($objp->dv);
245 $numchq = ($objp->num_chq ? $objp->num_chq :
'');
249 preg_match(
'/\((.+)\)/i', $objp->label, $reg);
250 if ($reg[1] && $langs->transnoentitiesnoconv($reg[1]) != $reg[1]) {
251 $description = $langs->transnoentitiesnoconv($reg[1]);
253 $description = $objp->label;
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);
268 $accountelem .=
', ';
270 $accountelem .= $invoicestatic->ref;
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);
280 $accountelem .=
', ';
282 $accountelem .= $invoicesupplierstatic->ref;
285 } elseif ($links[$key][
'type'] ==
'payment_sc') {
286 $paymentsocialcontributionstatic->fetch($links[$key][
'url_id']);
288 $accountelem .=
', ';
290 $accountelem .= $langs->transnoentitiesnoconv(
"SocialContribution").
' '.$paymentsocialcontributionstatic->ref;
291 } elseif ($links[$key][
'type'] ==
'payment_vat') {
292 $paymentvatstatic->fetch($links[$key][
'url_id']);
294 $accountelem .=
', ';
296 $accountelem .= $langs->transnoentitiesnoconv(
"VATPayments").
' '.$paymentvatstatic->ref;
297 } elseif ($links[$key][
'type'] ==
'banktransfert') {
298 $comment = $outputlangs->transnoentitiesnoconv(
"Transfer");
299 if ($objp->amount > 0) {
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,
'');
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');
328 } elseif ($links[$key][
'type'] ==
'company') {
332 $thirdparty .=
dol_trunc($links[$key][
'label'], 24);
334 } elseif ($links[$key][
'type'] ==
'member') {
336 $accountelem .=
', ';
338 $thirdparty .= $links[$key][
'label'];
362 $debit = $credit =
'';
363 if ($objp->amount < 0) {
364 $totald = $totald + abs($objp->amount);
367 $totalc = $totalc + abs($objp->amount);
373 $rec =
new stdClass();
374 $rec->bankreceipt = $objp->num_releve;
375 $rec->bankaccount = $objp->banklabel;
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);
389 $objmodel->write_record($array_selected, $rec, $outputlangs, $array_export_TypeFields);
393 print
"Found ".$numrows.
" records for receipt ".$num.
"\n";
396 $objmodel->write_footer($outputlangs);
399 $objmodel->close_file();
401 print
'File '.$filename.
' was generated into dir '.$dirname.
'.'.
"\n";
405 print
"No records found for receipt ".$num.
"\n";