dolibarr 24.0.3
treasuryjournal.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2007-2010 Laurent Destailleur <eldy@users.sourceforge.net>
3 * Copyright (C) 2007-2010 Jean Heimburger <jean@tiaris.info>
4 * Copyright (C) 2011 Juanjo Menent <jmenent@2byte.es>
5 * Copyright (C) 2012 Regis Houssin <regis.houssin@inodbox.com>
6 * Copyright (C) 2013 Christophe Battarel <christophe.battarel@altairis.fr>
7 * Copyright (C) 2013-2025 Alexandre Spangaro <alexandre@inovea-conseil.com>
8 * Copyright (C) 2013-2014 Florian Henry <florian.henry@open-concept.pro>
9 * Copyright (C) 2013-2014 Olivier Geffroy <jeff@jeffinfo.com>
10 * Copyright (C) 2017-2025 Frédéric France <frederic.france@free.fr>
11 * Copyright (C) 2018 Ferran Marcet <fmarcet@2byte.es>
12 * Copyright (C) 2025 Hannes Hieronimi <hannes@innwerk.org>
13 * Copyright (C) 2025-2026 MDW <mdeweerd@users.noreply.github.com>
14 *
15 * This program is free software; you can redistribute it and/or modify
16 * it under the terms of the GNU General Public License as published by
17 * the Free Software Foundation; either version 3 of the License, or
18 * (at your option) any later version.
19 *
20 * This program is distributed in the hope that it will be useful,
21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 * GNU General Public License for more details.
24 *
25 * You should have received a copy of the GNU General Public License
26 * along with this program. If not, see <https://www.gnu.org/licenses/>.
27 */
28
34require '../../main.inc.php';
43require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
44require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
45require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
46require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingjournal.class.php';
47require_once DOL_DOCUMENT_ROOT.'/accountancy/class/bookkeeping.class.php';
48require_once DOL_DOCUMENT_ROOT.'/accountancy/class/bookkeeping.class.php';
49require_once DOL_DOCUMENT_ROOT.'/core/class/html.formaccounting.class.php';
50
51// Load translation files required by the page
52$langs->loadLangs(array("companies", "other", "compta", "banks", "bills", "donations", "loan", "accountancy", "trips", "salaries", "hrm", "members"));
53
54// Multi journal
55$id_journal = GETPOSTINT('id_journal');
56
57$date_startmonth = GETPOSTINT('date_startmonth');
58$date_startday = GETPOSTINT('date_startday');
59$date_startyear = GETPOSTINT('date_startyear');
60$date_endmonth = GETPOSTINT('date_endmonth');
61$date_endday = GETPOSTINT('date_endday');
62$date_endyear = GETPOSTINT('date_endyear');
63$in_bookkeeping = GETPOST('in_bookkeeping', 'aZ09');
64
65$only_rappro = GETPOSTINT('only_rappro');
66if ($only_rappro == 0) {
67 //GET page for the first time, use default settings
68 $only_rappro = getDolGlobalInt('ACCOUNTING_BANK_CONCILIATED');
69}
70
71$now = dol_now();
72
73$action = GETPOST('action', 'aZ09');
74
75if ($in_bookkeeping == '') {
76 $in_bookkeeping = 'notyet';
77}
78
79// Security check
80if (!isModEnabled('accounting')) {
82}
83if ($user->socid > 0) {
85}
86if (!$user->hasRight('accounting', 'bind', 'write')) {
88}
89
90
91/*
92 * Actions
93 */
94
95$error = 0;
96
97$date_start = dol_mktime(0, 0, 0, $date_startmonth, $date_startday, $date_startyear);
98$date_end = dol_mktime(23, 59, 59, $date_endmonth, $date_endday, $date_endyear);
99
100$pastmonth = null; // Initialise for static analysis (could be really unseg)
101$pastmonthyear = null;
102
103if (empty($date_startmonth)) {
104 // Period by default on transfer
106 $date_start = $dates['date_start'];
107 $pastmonthyear = $dates['pastmonthyear'];
108 $pastmonth = $dates['pastmonth'];
109}
110if (empty($date_endmonth)) {
111 // Period by default on transfer
113 $date_end = $dates['date_end'];
114 $pastmonthyear = $dates['pastmonthyear'];
115 $pastmonth = $dates['pastmonth'];
116}
117
118if (!GETPOSTISSET('date_startmonth') && (empty($date_start) || empty($date_end))) { // We define date_start and date_end, only if we did not submit the form
119 $date_start = dol_get_first_day((int) $pastmonthyear, (int) $pastmonth, false);
120 $date_end = dol_get_last_day((int) $pastmonthyear, (int) $pastmonth, false);
121}
122
123// Get all bank lines
124//-------------------------------------
125$sql = "SELECT b.rowid, b.dateo as do, b.datev as dv, b.amount, b.amount_main_currency, b.label, b.rappro, b.num_releve, b.num_chq, b.fk_type, b.fk_account,";
126$sql .= " ba.courant, ba.ref as baref, ba.account_number, ba.fk_accountancy_journal,";
127$sql .= " bu.type as bu_type";
128$sql .= " FROM ".$db->prefix()."bank as b";
129$sql .= " JOIN ".$db->prefix()."bank_account as ba on b.fk_account = ba.rowid";
130$sql .= " LEFT JOIN ".$db->prefix()."bank_url as bu ON bu.fk_bank = b.rowid";
131$sql .= " WHERE ba.fk_accountancy_journal = ".((int) $id_journal);
132$sql .= " AND b.amount <> 0 AND ba.entity IN (".getEntity('bank_account').")"; // We don't share object for accountancy, we use source object sharing
133if ($date_start && $date_end) {
134 $sql .= " AND b.dateo >= '".$db->idate($date_start)."' AND b.dateo <= '".$db->idate($date_end)."'";
135}
136// Define begin binding date
137if (getDolGlobalInt('ACCOUNTING_DATE_START_BINDING')) {
138 $sql .= " AND b.dateo >= '".$db->idate(getDolGlobalInt('ACCOUNTING_DATE_START_BINDING'))."'";
139}
140// Already in bookkeeping or not
141if ($in_bookkeeping == 'already') {
142 $sql .= " AND (b.rowid IN (SELECT fk_doc FROM ".$db->prefix()."accounting_bookkeeping as ab WHERE ab.doc_type='bank') )";
143}
144if ($in_bookkeeping == 'notyet') {
145 $sql .= " AND (b.rowid NOT IN (SELECT fk_doc FROM ".$db->prefix()."accounting_bookkeeping as ab WHERE ab.doc_type='bank') )";
146}
147if ($only_rappro == 2) {
148 $sql .= " AND (b.rappro = '1')";
149}
150$sql .= " ORDER BY b.dateo";
151//print $sql;
152
153$result_lines = array();
154
155// Data cached
156$payment_ids = array();
157$tabpay = array();
158$tabaccount = array();
159$tabobject = array();
160$tabaccountingaccount = array();
161$tabvatdata = array();
162
163dol_syslog("accountancy/journal/treasuryjournal.php", LOG_DEBUG);
164$resql = $db->query($sql);
165if ($resql) {
166 require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/account.class.php';
167 $static_account = new Account($db);
168
169 while ($obj = $db->fetch_object($resql)) {
170 // Get payment infos (rowid is bank ID)
171 if (!isset($tabpay[$obj->rowid])) {
172 $tabpay[$obj->rowid] = array(
173 'id' => $obj->rowid,
174 'date' => $db->jdate($obj->do),
175 'type_payment' => $obj->fk_type,// CHQ, VIR, LIQ, CB, ...
176 'ref' => $obj->label, // by default, not unique. May be changed later
177 'fk_bank_account' => $obj->fk_account,
178 'objects' => array(),
179 );
180 $reg = array();
181 if (preg_match('/^\‍((.*)\‍)$/i', $obj->label, $reg)) {
182 $tabpay[$obj->rowid]["lib"] = $langs->trans($reg[1]);
183 } else {
184 $tabpay[$obj->rowid]["lib"] = dol_trunc($obj->label, 60);
185 }
186 }
187 $payment_ids[$obj->bu_type][$obj->rowid] = $obj->rowid;
188
189 // Get bank account infos (rowid is bank ID)
190 if (!isset($tabaccount[$obj->fk_account])) {
191 $static_account->id = $obj->fk_account;
192 $static_account->ref = $obj->baref;
193 $tabaccount[$obj->fk_account] = [
194 'id' => $obj->fk_account,
195 'account_ref' => $obj->baref,
196 'account_number' => $obj->account_number,
197 'url' => $static_account->getNomUrl(1),
198 ];
199 }
200 }
201 $db->free($resql);
202
203 foreach ($payment_ids as $type => $ids) {
204 switch ($type) {
205 case 'payment':
206 require_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php';
207
208 // Customer invoices
209 //------------------------------------------
210 $sql = "SELECT f.rowid, f.ref AS ref, f.total_ht AS invoice_total_ht, f.total_ttc AS invoice_total_ttc,";
211 $sql .= " pf.amount AS amount_payment,";
212 $sql .= " fd.rowid AS row_id, fd.total_ht, fd.total_tva, fd.total_localtax1, fd.total_localtax2, fd.tva_tx, fd.total_ttc, fd.vat_src_code,";
213 $sql .= " aa.account_number as accountancy_code, aa.label as accountancy_code_label,";
214 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
215 $sql .= " FROM ".$db->prefix()."facturedet as fd";
216 $sql .= " INNER JOIN ".$db->prefix()."facture as f ON f.rowid = fd.fk_facture";
217 $sql .= " INNER JOIN ".$db->prefix()."paiement_facture as pf ON pf.fk_facture = f.rowid";
218 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pf.fk_paiement AND bu.type = '".$db->escape($type)."'";
219 $sql .= " LEFT JOIN ".$db->prefix()."product as p ON p.rowid = fd.fk_product";
220 $sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
221 // Already in bookkeeping or not
222 if ($in_bookkeeping == 'already') {
223 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=f.rowid";
224 } else {
225 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=f.rowid";
226 }
227 $sql .= " WHERE f.entity IN (".getEntity('facture', 0).')'; // We don't share object for accountancy, we use source object sharing
228 // Not already in bookkeeping
229 if ($in_bookkeeping == 'notyet') {
230 $sql .= " AND ab.rowid IS NULL";
231 }
232 $sql .= " AND fd.fk_code_ventilation > 0";
233 $sql .= " AND f.fk_statut > 0";
234 $sql .= " AND fd.product_type IN (0,1)";
235 $sql .= " AND f.type IN (".Facture::TYPE_STANDARD.",".Facture::TYPE_REPLACEMENT.",".Facture::TYPE_CREDIT_NOTE.",".(!getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS') ? Facture::TYPE_DEPOSIT."," : "").Facture::TYPE_SITUATION.")";
236 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
237 //$sql .= " GROUP BY fd.rowid, bu.fk_bank, pf.amount, bu.url_id, f.rowid, f.ref, f.total_ht, f.total_ttc, fd.total_ht, fd.total_tva, fd.total_localtax1, fd.total_localtax2, fd.tva_tx, fd.total_ttc, fd.vat_src_code, aa.account_number, aa.label, bu.type";
238 $sql .= " ORDER BY aa.account_number";
239
240 $resql = $db->query($sql);
241 if ($resql) {
242 $langs->load("bills");
243 $static_invoice = new Facture($db);
244 $already_sum = array();
245 $account_vat_sold = getDolGlobalString('ACCOUNTING_VAT_SOLD_ACCOUNT', 'NotDefined'); // NotDefined is a reserved word
246
247 while ($obj = $db->fetch_object($resql)) {
248 $object_key = $obj->bu_type.'_'.$obj->rowid;
249
250 // If 1 invoice has 2 payments at 2 different date, we have 2 record $tabpay because $obj->fk_bank is different (obj->fk_bank is ID of payment in bank record table).
251 // If 2 invoices are paid in the same payment, we have 1 $tabpay with 2 objects. Lines are written per object, with the ref of the object in the label (see $label_suffix).
252
253 // Add object in payment
254 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
255 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
256 'amount' => $obj->amount_payment,
257 'bu_url_id' => $obj->bu_url_id,
258 );
259 }
260
261 if (isset($already_sum[$obj->row_id])) {
262 continue;
263 }
264 $already_sum[$obj->row_id] = $obj->row_id;
265
266 // Set object infos
267 if (!isset($tabobject[$object_key])) {
268 $static_invoice->id = $obj->rowid;
269 $static_invoice->ref = $obj->ref;
270 $tabobject[$object_key] = array(
271 'id' => $obj->rowid,
272 'ref' => $obj->ref, // It would be better to have a doc_ref that is 'BankId '.$obj->fk_bank.' - Facture FAzzz' and not just 'FAzzz' to be protected against duplicate, where xxx = $obj->fk_bank
273 'total_ht' => $obj->invoice_total_ht,
274 'total_ttc' => $obj->invoice_total_ttc,
275 'url' => $static_invoice->getNomUrl(1),
276 'operations' => array(),
277 'vats' => array(),
278 );
279 }
280
281 // Set accounting account infos
282 if (!isset($tabaccountingaccount[$obj->accountancy_code])) {
283 $tabaccountingaccount[$obj->accountancy_code] = array(
284 'label' => !empty($obj->accountancy_code_label) ? $obj->accountancy_code_label : $langs->trans('NotDefined'),
285 );
286 }
287
288 // Add amount for the accountancy code
289 if (!isset($tabobject[$object_key]['operations'][$obj->accountancy_code])) {
290 $tabobject[$object_key]['operations'][$obj->accountancy_code] = array(
291 'total_ht' => 0,
292 );
293 }
294 $tabobject[$object_key]['operations'][$obj->accountancy_code]['total_ht'] += $obj->total_ht;
295
296 if ($obj->total_tva + $obj->total_localtax1 + $obj->total_localtax2 != 0) {
297 // Get vat code compta
298 if (!isset($tabvatdata[$obj->tva_tx][$obj->vat_src_code])) {
299 $tabvatdata[$obj->tva_tx][$obj->vat_src_code] = getTaxesFromId($obj->tva_tx.($obj->vat_src_code ? ' ('.$obj->vat_src_code.')' : ''), $mysoc, $mysoc, 0);
300 }
301 $compta_tva = (!empty($tabvatdata[$obj->tva_tx][$obj->vat_src_code]['accountancy_code_sell']) ? $tabvatdata[$obj->tva_tx][$obj->vat_src_code]['accountancy_code_sell'] : $account_vat_sold);
302
303 // Add amount VAT for the code compta
304 if (!isset($tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx])) {
305 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx] = array(
306 'tva_tx' => $obj->tva_tx,
307 'total_tva' => 0,
308 'total_localtax1' => 0,
309 'total_localtax2' => 0,
310 );
311 }
312 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_tva'] += $obj->total_tva;
313 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_localtax1'] += $obj->total_localtax1;
314 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_localtax2'] += $obj->total_localtax2;
315 }
316 }
317 } else {
319 }
320 break;
321 case 'payment_supplier':
322 require_once DOL_DOCUMENT_ROOT.'/fourn/class/fournisseur.facture.class.php';
323
324 // Supplier invoices
325 //------------------------------------------
326 $sql = "SELECT ff.rowid, ff.ref, ff.total_ht AS supplier_invoice_total_ht, ff.total_ttc AS supplier_invoice_total_ttc,";
327 $sql .= " pff.amount AS amount_payment,";
328 $sql .= " ffd.rowid AS row_id, ffd.total_ht, ffd.tva AS total_tva, ffd.total_localtax1, ffd.total_localtax2, ffd.tva_tx, ffd.total_ttc, ffd.vat_src_code,";
329 $sql .= " aa.account_number as accountancy_code, aa.label as accountancy_code_label,";
330 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
331 $sql .= " FROM ".$db->prefix()."facture_fourn_det as ffd";
332 $sql .= " INNER JOIN ".$db->prefix()."facture_fourn as ff ON ff.rowid = ffd.fk_facture_fourn";
333 $sql .= " INNER JOIN ".$db->prefix()."paiementfourn_facturefourn as pff ON pff.fk_facturefourn = ff.rowid";
334 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pff.fk_paiementfourn AND bu.type = '".$db->escape($type)."'";
335 $sql .= " LEFT JOIN ".$db->prefix()."product as p ON p.rowid = ffd.fk_product";
336 $sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa ON aa.rowid = ffd.fk_code_ventilation";
337 // Already in bookkeeping or not
338 if ($in_bookkeeping == 'already') {
339 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=ff.rowid";
340 } else {
341 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=ff.rowid";
342 }
343 $sql .= " WHERE ff.entity IN (".getEntity('facture_fourn', 0).')'; // We don't share object for accountancy, we use source object sharing
344 // Not already in bookkeeping
345 if ($in_bookkeeping == 'notyet') {
346 $sql .= " AND ab.rowid IS NULL";
347 }
348 $sql .= " AND ffd.fk_code_ventilation > 0";
349 $sql .= " AND ff.fk_statut > 0";
350 $sql .= " AND ffd.product_type IN (0,1)";
351 $sql .= " AND ff.type IN (".FactureFournisseur::TYPE_STANDARD.",".FactureFournisseur::TYPE_REPLACEMENT.",".FactureFournisseur::TYPE_CREDIT_NOTE.",".(!getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS') ? FactureFournisseur::TYPE_DEPOSIT."," : "").FactureFournisseur::TYPE_SITUATION.")";
352 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
353 //$sql .= " GROUP BY ffd.rowid, bu.fk_bank, ff.rowid, ff.ref, ff.total_ht, ff.total_ttc, pff.amount, ffd.total_ht, ffd.tva, ffd.total_localtax1, ffd.total_localtax2, ffd.tva_tx, ffd.total_ttc, ffd.vat_src_code, aa.account_number, aa.label, bu.url_id, bu.type";
354 $sql .= " ORDER BY aa.account_number";
355
356 $resql = $db->query($sql);
357 if ($resql) {
358 $langs->load("suppliers");
359 $static_supplier_invoice = new FactureFournisseur($db);
360 $already_sum = array();
361 $account_vat_buy = getDolGlobalString('ACCOUNTING_VAT_BUY_ACCOUNT', 'NotDefined'); // NotDefined is a reserved word
362
363 while ($obj = $db->fetch_object($resql)) {
364 $object_key = $obj->bu_type.'_'.$obj->rowid;
365
366 // Add object in payment
367 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
368 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
369 'amount' => -$obj->amount_payment,
370 'bu_url_id' => $obj->bu_url_id,
371 );
372 }
373
374 if (isset($already_sum[$obj->row_id])) {
375 continue;
376 }
377 $already_sum[$obj->row_id] = $obj->row_id;
378
379 // Set object infos
380 if (!isset($tabobject[$object_key])) {
381 $static_supplier_invoice->id = $obj->rowid;
382 $static_supplier_invoice->ref = $obj->ref;
383 $tabobject[$object_key] = array(
384 'id' => $obj->rowid,
385 'ref' => $obj->ref, // It would be better to have a doc_ref that is 'BankId '.$obj->fk_bank.' - Facture FAzzz' and not just 'FAzzz' to be protected against duplicate, where xxx = $obj->fk_bank
386 'total_ht' => -$obj->supplier_invoice_total_ht,
387 'total_ttc' => -$obj->supplier_invoice_total_ttc,
388 'url' => $static_supplier_invoice->getNomUrl(1),
389 'operations' => array(),
390 'vats' => array(),
391 );
392 }
393
394 // Set accounting account infos
395 if (!isset($tabaccountingaccount[$obj->accountancy_code])) {
396 $tabaccountingaccount[$obj->accountancy_code] = array(
397 'label' => !empty($obj->accountancy_code_label) ? $obj->accountancy_code_label : $langs->trans('NotDefined'),
398 );
399 }
400
401 // Add amount for the accountancy code
402 if (!isset($tabobject[$object_key]['operations'][$obj->accountancy_code])) {
403 $tabobject[$object_key]['operations'][$obj->accountancy_code] = array(
404 'total_ht' => 0,
405 );
406 }
407 $tabobject[$object_key]['operations'][$obj->accountancy_code]['total_ht'] -= $obj->total_ht;
408
409 if ($obj->total_tva + $obj->total_localtax1 + $obj->total_localtax2 != 0) {
410 // Get vat code compta
411 if (!isset($tabvatdata[$obj->tva_tx][$obj->vat_src_code])) {
412 $tabvatdata[$obj->tva_tx][$obj->vat_src_code] = getTaxesFromId($obj->tva_tx.($obj->vat_src_code ? ' ('.$obj->vat_src_code.')' : ''), $mysoc, $mysoc, 0);
413 }
414 $compta_tva = (!empty($tabvatdata[$obj->tva_tx][$obj->vat_src_code]['accountancy_code_buy']) ? $tabvatdata[$obj->tva_tx][$obj->vat_src_code]['accountancy_code_buy'] : $account_vat_buy);
415
416 // Add amount VAT for the code compta
417 if (!isset($tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx])) {
418 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx] = array(
419 'tva_tx' => $obj->tva_tx,
420 'total_tva' => 0,
421 'total_localtax1' => 0,
422 'total_localtax2' => 0,
423 );
424 }
425 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_tva'] -= $obj->total_tva;
426 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_localtax1'] -= $obj->total_localtax1;
427 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_localtax2'] -= $obj->total_localtax2;
428 }
429 }
430 } else {
432 }
433 break;
434 case 'payment_expensereport':
435 require_once DOL_DOCUMENT_ROOT.'/expensereport/class/expensereport.class.php';
436
437 // Expense reports
438 //------------------------------------------
439 $sql = "SELECT er.rowid, er.ref, er.total_ht AS expense_report_total_ht, er.total_ttc AS expense_report_total_ttc,";
440 $sql .= " per.amount AS amount_payment,";
441 $sql .= " erf.rowid AS row_id, erf.total_ht, erf.total_tva, erf.total_localtax1, erf.total_localtax2, erf.tva_tx, erf.total_ttc, erf.vat_src_code,";
442 $sql .= " ctf.accountancy_code,";
443 $sql .= " aa.label as accountancy_code_label,";
444 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
445 $sql .= " FROM ".$db->prefix()."expensereport_det as erf";
446 $sql .= " INNER JOIN ".$db->prefix()."expensereport as er ON er.rowid = erf.fk_expensereport";
447 $sql .= " INNER JOIN ".$db->prefix()."payment_expensereport as per ON per.fk_expensereport = er.rowid";
448 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = per.rowid AND bu.type = '".$db->escape($type)."'";
449 $sql .= " LEFT JOIN ".$db->prefix()."c_type_fees as ctf ON ctf.id = erf.fk_c_type_fees";
450 $sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa ON aa.account_number = ctf.accountancy_code";
451 // Already in bookkeeping or not
452 if ($in_bookkeeping == 'already') {
453 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=er.rowid";
454 } else {
455 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=er.rowid";
456 }
457 $sql .= " WHERE er.entity IN (".getEntity('expensereport', 0).')'; // We don't share object for accountancy, we use source object sharing
458 // Not already in bookkeeping
459 if ($in_bookkeeping == 'notyet') {
460 $sql .= " AND ab.rowid IS NULL";
461 }
462 $sql .= " AND er.fk_statut >= ".ExpenseReport::STATUS_APPROVED;
463 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
464 //$sql .= " GROUP BY erf.rowid, bu.fk_bank, per.amount, aa.label, bu.url_id, er.rowid, er.ref, er.total_ht, er.total_ttc, erf.total_ht, erf.total_tva, erf.total_localtax1, erf.total_localtax2, erf.tva_tx, erf.total_ttc, erf.vat_src_code, ctf.accountancy_code, bu.type, aa.account_number";
465 $sql .= " ORDER BY aa.account_number";
466
467 $resql = $db->query($sql);
468 if ($resql) {
469 $langs->load("trips");
470 $static_expense_report = new ExpenseReport($db);
471 $already_sum = array();
472 $account_vat_buy = getDolGlobalString('ACCOUNTING_VAT_BUY_ACCOUNT', 'NotDefined'); // NotDefined is a reserved word
473
474 while ($obj = $db->fetch_object($resql)) {
475 $object_key = $obj->bu_type.'_'.$obj->rowid;
476
477 // Add object in payment
478 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
479 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
480 'amount' => -$obj->amount_payment,
481 'bu_url_id' => $obj->bu_url_id,
482 );
483 }
484
485 if (isset($already_sum[$obj->row_id])) {
486 continue;
487 }
488 $already_sum[$obj->row_id] = $obj->row_id;
489
490 // Set object infos
491 if (!isset($tabobject[$object_key])) {
492 $static_expense_report->id = $obj->rowid;
493 $static_expense_report->ref = $obj->ref;
494 $tabobject[$object_key] = array(
495 'id' => $obj->rowid,
496 'ref' => $obj->ref,
497 'total_ht' => -$obj->expense_report_total_ht,
498 'total_ttc' => -$obj->expense_report_total_ttc,
499 'url' => $static_expense_report->getNomUrl(1),
500 'operations' => array(),
501 'vats' => array(),
502 );
503 }
504
505 // Set accounting account infos
506 $accountancy_code = !empty($obj->accountancy_code) ? $obj->accountancy_code : 'NotDefined';
507 if (!isset($tabaccountingaccount[$accountancy_code])) {
508 $tabaccountingaccount[$accountancy_code] = array(
509 'label' => !empty($obj->accountancy_code_label) ? $obj->accountancy_code_label : $langs->trans('NotDefined'),
510 );
511 }
512
513 // Add amount for the accountancy code
514 if (!isset($tabobject[$object_key]['operations'][$accountancy_code])) {
515 $tabobject[$object_key]['operations'][$accountancy_code] = array(
516 'total_ht' => 0,
517 );
518 }
519 $tabobject[$object_key]['operations'][$accountancy_code]['total_ht'] -= $obj->total_ht;
520
521 if ($obj->total_tva + $obj->total_localtax1 + $obj->total_localtax2 != 0) {
522 // Get vat code compta
523 if (!isset($tabvatdata[$obj->tva_tx][$obj->vat_src_code])) {
524 $tabvatdata[$obj->tva_tx][$obj->vat_src_code] = getTaxesFromId($obj->tva_tx.($obj->vat_src_code ? ' ('.$obj->vat_src_code.')' : ''), $mysoc, $mysoc, 0);
525 }
526 $compta_tva = (!empty($tabvatdata[$obj->tva_tx][$obj->vat_src_code]['accountancy_code_buy']) ? $tabvatdata[$obj->tva_tx][$obj->vat_src_code]['accountancy_code_buy'] : $account_vat_buy);
527
528 // Add amount VAT for the code compta
529 if (!isset($tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx])) {
530 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx] = array(
531 'tva_tx' => $obj->tva_tx,
532 'total_tva' => 0,
533 'total_localtax1' => 0,
534 'total_localtax2' => 0,
535 );
536 }
537 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_tva'] -= $obj->total_tva;
538 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_localtax1'] -= $obj->total_localtax1;
539 $tabobject[$object_key]['vats'][$compta_tva][$obj->tva_tx]['total_localtax2'] -= $obj->total_localtax2;
540 }
541 }
542 } else {
544 }
545 break;
546 case 'payment_salary':
547 // Payment salaries
548 //------------------------------------------
549 $sql = "SELECT ps.rowid,";
550 $sql .= " ps.amount AS amount_payment, ps.label AS label,";
551 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
552 $sql .= " FROM ".$db->prefix()."payment_salary as ps";
553 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = ps.rowid AND bu.type = '".$db->escape($type)."'";
554 // Already in bookkeeping or not
555 if ($in_bookkeeping == 'already') {
556 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=ps.rowid";
557 } else {
558 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=ps.rowid";
559 }
560 $sql .= " WHERE ps.entity IN (".getEntity('user', 0).')'; // We don't share object for accountancy, we use source object sharing
561 // Not already in bookkeeping
562 if ($in_bookkeeping == 'notyet') {
563 $sql .= " AND ab.rowid IS NULL";
564 }
565 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
566
567 $resql = $db->query($sql);
568 if ($resql) {
569 require_once DOL_DOCUMENT_ROOT.'/salaries/class/paymentsalary.class.php';
570 $langs->load("salaries");
571 $static_payment_salary = new PaymentSalary($db);
572 $account_employee = getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT', 'NotDefined'); // NotDefined is a reserved word
573 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_SALARY_REF_PREFIX', 'PS');
574
575 while ($obj = $db->fetch_object($resql)) {
576 $object_key = $obj->bu_type.'_'.$obj->rowid;
577
578 // Add object in payment
579 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
580 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
581 'amount' => -$obj->amount_payment,
582 'bu_url_id' => $obj->bu_url_id,
583 );
584 }
585
586 // Set object infos
587 if (!isset($tabobject[$object_key])) {
588 $static_payment_salary->id = $obj->rowid;
589 $static_payment_salary->ref = $prefix_ref.$obj->rowid;
590 $tabobject[$object_key] = array(
591 'id' => $obj->rowid,
592 'ref' => $prefix_ref.$obj->rowid,
593 'total_ht' => -$obj->amount_payment,
594 'total_ttc' => -$obj->amount_payment,
595 'url' => $static_payment_salary->getNomUrl(1),
596 'operations' => array(),
597 'vats' => array(),
598 );
599 }
600
601 // Add amount for the accountancy code
602 $tabobject[$object_key]['operations'][$account_employee] = array(
603 'total_ht' => -$obj->amount_payment,
604 'label' => $obj->label,
605 );
606 }
607 } else {
609 }
610 break;
611 case 'payment_sc':
612 // Socials contributions
613 //------------------------------------------
614 $sql = "SELECT cs.rowid, cs.ref, cs.libelle AS label, cs.amount AS sociales_contributions_amount,";
615 $sql .= " pc.amount AS amount_payment,";
616 $sql .= " ccs.accountancy_code,";
617 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
618 $sql .= " FROM ".$db->prefix()."paiementcharge as pc";
619 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pc.rowid AND bu.type = '".$db->escape($type)."'";
620 $sql .= " INNER JOIN ".$db->prefix()."chargesociales AS cs ON cs.rowid = pc.fk_charge";
621 $sql .= " LEFT JOIN ".$db->prefix()."c_chargesociales as ccs ON ccs.id = cs.fk_type";
622 // Already in bookkeeping or not
623 if ($in_bookkeeping == 'already') {
624 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=cs.rowid";
625 } else {
626 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=cs.rowid";
627 }
628 $sql .= " WHERE cs.entity = ".((int) $conf->entity); // We don't share object for accountancy, we use source object sharing
629 // Not already in bookkeeping
630 if ($in_bookkeeping == 'notyet') {
631 $sql .= " AND ab.rowid IS NULL";
632 }
633 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
634
635 $resql = $db->query($sql);
636 if ($resql) {
637 require_once DOL_DOCUMENT_ROOT.'/compta/sociales/class/chargesociales.class.php';
638
639 $langs->load("bills");
640 $static_sociales_contributions = new ChargeSociales($db);
641 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_SOCIALES_CONTRIBUTIONS_REF_PREFIX', 'SC');
642
643 while ($obj = $db->fetch_object($resql)) {
644 $object_key = $obj->bu_type.'_'.$obj->rowid;
645
646 // Add object in payment
647 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
648 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
649 'amount' => -$obj->amount_payment,
650 'bu_url_id' => $obj->bu_url_id,
651 );
652 }
653
654 // Set object infos
655 if (!isset($tabobject[$object_key])) {
656 $static_sociales_contributions->id = $obj->rowid;
657 $static_sociales_contributions->ref = $prefix_ref.$obj->rowid;
658 $tabobject[$object_key] = array(
659 'id' => $obj->rowid,
660 'ref' => $prefix_ref.$obj->rowid,
661 'total_ht' => -$obj->sociales_contributions_amount,
662 'total_ttc' => -$obj->sociales_contributions_amount,
663 'url' => $static_sociales_contributions->getNomUrl(1),
664 'operations' => array(),
665 'vats' => array(),
666 );
667 }
668
669 $accountancy_code = !empty($obj->accountancy_code) ? $obj->accountancy_code : 'NotDefined';
670
671 // Add amount for the accountancy code
672 $tabobject[$object_key]['operations'][$accountancy_code] = array(
673 'total_ht' => -$obj->sociales_contributions_amount,
674 'label' => $obj->label,
675 );
676 }
677 } else {
679 }
680 break;
681 case 'payment_vat':
682 // Payment VAT
683 //------------------------------------------
684 // The bank link of type 'payment_vat' is on the payment (llx_payment_vat), not on the VAT declaration (llx_tva)
685 $sql = "SELECT pv.rowid, t.rowid AS tva_id,";
686 $sql .= " pv.amount AS amount_payment, t.label AS label,";
687 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
688 $sql .= " FROM ".$db->prefix()."payment_vat as pv";
689 $sql .= " INNER JOIN ".$db->prefix()."tva as t ON t.rowid = pv.fk_tva";
690 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pv.rowid AND bu.type = '".$db->escape($type)."'";
691 // Already in bookkeeping or not
692 if ($in_bookkeeping == 'already') {
693 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=t.rowid";
694 } else {
695 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=t.rowid";
696 }
697 $sql .= " WHERE bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
698 // $sql .= " AND t.entity = " . ((int) $conf->entity); // TODO when entity is managed in tva
699 // Not already in bookkeeping
700 if ($in_bookkeeping == 'notyet') {
701 $sql .= " AND ab.rowid IS NULL";
702 }
703
704 $resql = $db->query($sql);
705 if ($resql) {
706 require_once DOL_DOCUMENT_ROOT.'/compta/tva/class/tva.class.php';
707
708 $langs->load("salaries");
709 $static_tva = new Tva($db);
710 $account_pay_vat = getDolGlobalString('ACCOUNTING_VAT_PAY_ACCOUNT', 'NotDefined'); // NotDefined is a reserved word
711 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_VAT_REF_PREFIX', 'VAT');
712
713 while ($obj = $db->fetch_object($resql)) {
714 $object_key = $obj->bu_type.'_'.$obj->rowid;
715
716 // Add object in payment
717 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
718 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
719 'amount' => -$obj->amount_payment,
720 'bu_url_id' => $obj->bu_url_id,
721 );
722 }
723
724 // Set object infos (the object is the VAT declaration paid by this payment)
725 if (!isset($tabobject[$object_key])) {
726 $static_tva->id = $obj->tva_id;
727 $static_tva->ref = $prefix_ref.$obj->tva_id;
728 $tabobject[$object_key] = array(
729 'id' => $obj->tva_id,
730 'ref' => $prefix_ref.$obj->tva_id,
731 'total_ht' => -$obj->amount_payment,
732 'total_ttc' => -$obj->amount_payment,
733 'url' => $static_tva->getNomUrl(1),
734 'operations' => array(),
735 'vats' => array(),
736 );
737 }
738
739 // Add amount for the accountancy code
740 $tabobject[$object_key]['operations'][$account_pay_vat] = array(
741 'total_ht' => -$obj->amount_payment,
742 'label' => $obj->label,
743 );
744 }
745 } else {
747 }
748 break;
749 case 'payment_donation':
750 // Payment donation
751 //------------------------------------------
752 $sql = "SELECT d.rowid, d.amount AS don_amount,";
753 $sql .= " pd.amount AS amount_payment,";
754 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
755 $sql .= " FROM ".$db->prefix()."payment_donation as pd";
756 $sql .= " INNER JOIN ".$db->prefix()."don as d ON pd.fk_donation = d.rowid";
757 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pd.rowid AND bu.type = '".$db->escape($type)."'";
758 // Already in bookkeeping or not
759 if ($in_bookkeeping == 'already') {
760 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=d.rowid";
761 } else {
762 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=d.rowid";
763 }
764 $sql .= " WHERE d.entity IN (".getEntity('donation', 0).')'; // We don't share object for accountancy, we use source object sharing
765 // Not already in bookkeeping
766 if ($in_bookkeeping == 'notyet') {
767 $sql .= " AND ab.rowid IS NULL";
768 }
769 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
770
771 $resql = $db->query($sql);
772 if ($resql) {
773 require_once DOL_DOCUMENT_ROOT.'/don/class/don.class.php';
774
775 $langs->load("donations");
776 $static_don = new Don($db);
777 $account_pay_donation = getDolGlobalString('DONATION_ACCOUNTINGACCOUNT', 'NotDefined'); // NotDefined is a reserved word
778 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_DONATION_REF_PREFIX', 'D');
779
780 while ($obj = $db->fetch_object($resql)) {
781 $object_key = $obj->bu_type.'_'.$obj->rowid;
782
783 // Add object in payment
784 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
785 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
786 'amount' => $obj->amount_payment,
787 'bu_url_id' => $obj->bu_url_id,
788 );
789 }
790
791 // Set object infos
792 if (!isset($tabobject[$object_key])) {
793 $static_don->id = $obj->rowid;
794 $static_don->ref = $prefix_ref.$obj->rowid;
795 $tabobject[$object_key] = array(
796 'id' => $obj->rowid,
797 'ref' => $prefix_ref.$obj->rowid,
798 'total_ht' => $obj->don_amount,
799 'total_ttc' => $obj->don_amount,
800 'url' => $static_don->getNomUrl(1),
801 'operations' => array(),
802 'vats' => array(),
803 );
804 }
805
806 // Add amount for the accountancy code
807 $tabobject[$object_key]['operations'][$account_pay_donation] = array(
808 'total_ht' => $obj->don_amount,
809 'label' => $langs->trans('Donation').' '.$prefix_ref.$obj->rowid,
810 );
811 }
812 } else {
814 }
815 break;
816 case 'payment_loan':
817 // Payment loan
818 //------------------------------------------
819 $sql = "SELECT l.rowid, l.capital AS loan_capital, l.accountancy_account_capital, l.accountancy_account_interest, l.accountancy_account_insurance, l.label,";
820 $sql .= " pl.amount_capital, pl.rowid as payment_id, pl.amount_interest, pl.amount_insurance,";
821 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
822 $sql .= " FROM ".$db->prefix()."payment_loan as pl";
823 $sql .= " INNER JOIN ".$db->prefix()."loan as l ON pl.fk_loan = l.rowid";
824 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pl.rowid AND bu.type = '".$db->escape($type)."'";
825 // Already in bookkeeping or not
826 if ($in_bookkeeping == 'already') {
827 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=l.rowid";
828 } else {
829 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=l.rowid";
830 }
831 $sql .= " WHERE l.entity = ".((int) $conf->entity); // We don't share object for accountancy, we use source object sharing
832 // Not already in bookkeeping
833 if ($in_bookkeeping == 'notyet') {
834 $sql .= " AND ab.rowid IS NULL";
835 }
836 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
837
838 $resql = $db->query($sql);
839 if ($resql) {
840 require_once DOL_DOCUMENT_ROOT.'/loan/class/loan.class.php';
841
842 $langs->load("loan");
843 $static_loan = new Loan($db);
844 $account_pay_loan_capital = getDolGlobalString('LOAN_ACCOUNTING_ACCOUNT_CAPITAL', 'NotDefined'); // NotDefined is a reserved word
845 $account_pay_loan_interest = getDolGlobalString('LOAN_ACCOUNTING_ACCOUNT_INTEREST', 'NotDefined'); // NotDefined is a reserved word
846 $account_pay_loan_insurance = getDolGlobalString('LOAN_ACCOUNTING_ACCOUNT_INSURANCE', 'NotDefined'); // NotDefined is a reserved word
847 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_LOAN_REF_PREFIX', 'L');
848
849 while ($obj = $db->fetch_object($resql)) {
850 $object_key = $obj->bu_type.'_'.$obj->payment_id;
851
852 // Add object in payment
853 $payment_amount = $obj->amount_capital + $obj->amount_interest + $obj->amount_insurance;
854 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
855 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
856 'amount' => -$payment_amount,
857 'bu_url_id' => $obj->bu_url_id,
858 );
859 }
860
861 // Set object infos
862 if (!isset($tabobject[$object_key])) {
863 $static_loan->id = $obj->rowid;
864 $static_loan->ref = $prefix_ref.$obj->rowid;
865 $tabobject[$object_key] = array(
866 'id' => $obj->rowid,
867 'ref' => $prefix_ref.$obj->rowid,
868 'total_ht' => -$obj->loan_capital,
869 'total_ttc' => -$obj->loan_capital,
870 'url' => $static_loan->getNomUrl(1),
871 'operations' => array(),
872 'vats' => array(),
873 );
874 }
875
876 // Add amount for the accountancy code
877 $accountancy_account_capital = !empty($obj->accountancy_account_capital) ? $obj->accountancy_account_capital : $account_pay_loan_capital;
878 $tabobject[$object_key]['operations'][$accountancy_account_capital] = array(
879 // virtual total = loan_capital * amount_capital / payment_amount
880 'total_ht' => -($obj->loan_capital * $obj->amount_capital / $payment_amount),
881 'label' => $obj->label.' '.$langs->trans('LoanCapital'),
882 );
883
884 // Add amount for the accountancy code
885 $accountancy_account_interest = !empty($obj->accountancy_account_interest) ? $obj->accountancy_account_interest : $account_pay_loan_interest;
886 $tabobject[$object_key]['operations'][$accountancy_account_interest] = array(
887 // virtual total = loan_capital * amount_interest / payment_amount
888 'total_ht' => -($obj->loan_capital * $obj->amount_interest / $payment_amount),
889 'label' => $obj->label.' '.$langs->trans('Interest'),
890 );
891
892 // 526,23 = 569,74 * x / 15 000,00
893
894 // Add amount for the accountancy code
895 $accountancy_account_insurance = !empty($obj->accountancy_account_insurance) ? $obj->accountancy_account_insurance : $account_pay_loan_insurance;
896 $tabobject[$object_key]['operations'][$accountancy_account_insurance] = array(
897 // virtual total = loan_capital * amount_insurance / payment_amount
898 'total_ht' => -($obj->loan_capital * $obj->amount_insurance / $payment_amount),
899 'label' => $obj->label.' '.$langs->trans('Insurance'),
900 );
901 }
902 } else {
904 }
905 break;
906 case 'payment_various':
907 // Payment various
908 //------------------------------------------
909 $sql = "SELECT pv.rowid,";
910 $sql .= " pv.sens AS sens_payment, pv.amount AS amount_payment, pv.label, pv.accountancy_code,";
911 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
912 $sql .= " FROM ".$db->prefix()."payment_various as pv";
913 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pv.rowid AND bu.type = '".$db->escape($type)."'";
914 // Already in bookkeeping or not
915 if ($in_bookkeeping == 'already') {
916 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=pv.rowid";
917 } else {
918 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=pv.rowid";
919 }
920 $sql .= " WHERE pv.entity IN (".getEntity('payment_various', 0).')'; // We don't share object for accountancy, we use source object sharing
921 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
922 // Not already in bookkeeping
923 if ($in_bookkeeping == 'notyet') {
924 $sql .= " AND ab.rowid IS NULL";
925 }
926
927 $resql = $db->query($sql);
928 if ($resql) {
929 require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/paymentvarious.class.php';
930
931 $static_payment_various = new PaymentVarious($db);
932 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_VARIOUS_REF_PREFIX', 'PM');
933
934 while ($obj = $db->fetch_object($resql)) {
935 $object_key = $obj->bu_type.'_'.$obj->rowid;
936
937 $payment_amount = (empty($obj->sens_payment) ? -1 : 1) * $obj->amount_payment;
938 // Add object in payment
939 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
940 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
941 'amount' => $payment_amount,
942 'bu_url_id' => $obj->bu_url_id,
943 );
944 }
945
946 // Set object infos
947 if (!isset($tabobject[$object_key])) {
948 $static_payment_various->id = $obj->rowid;
949 $static_payment_various->ref = $prefix_ref.$obj->rowid;
950 $tabobject[$object_key] = array(
951 'id' => $obj->rowid,
952 'ref' => $prefix_ref.$obj->rowid,
953 'total_ht' => $payment_amount,
954 'total_ttc' => $payment_amount,
955 'url' => $static_payment_various->getNomUrl(1),
956 'operations' => array(),
957 'vats' => array(),
958 );
959 }
960
961 // Add amount for the accountancy code
962 $accountancy_code = !empty($obj->accountancy_code) ? $obj->accountancy_code : 'NotDefined';
963 $tabobject[$object_key]['operations'][$obj->accountancy_code] = array(
964 'total_ht' => $payment_amount,
965 'label' => $obj->label,
966 );
967 }
968 } else {
970 }
971 break;
972 case 'member':
973 // Subscription member
974 //------------------------------------------
975 $sql = "SELECT su.rowid,";
976 $sql .= " su.subscription AS amount_payment, su.note AS label,";
977 $sql .= " adh.lastname, adh.firstname,";
978 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
979 $sql .= " FROM ".$db->prefix()."subscription as su";
980 $sql .= " INNER JOIN ".$db->prefix()."adherent as adh ON adh.rowid = su.fk_adherent";
981 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.fk_bank = su.fk_bank AND bu.type = '".$db->escape($type)."'";
982 // Already in bookkeeping or not
983 if ($in_bookkeeping == 'already') {
984 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=su.rowid";
985 } else {
986 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=su.rowid";
987 }
988 $sql .= " WHERE bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
989 // Not already in bookkeeping
990 if ($in_bookkeeping == 'notyet') {
991 $sql .= " AND ab.rowid IS NULL";
992 }
993
994 $resql = $db->query($sql);
995 if ($resql) {
996 require_once DOL_DOCUMENT_ROOT.'/adherents/class/subscription.class.php';
997
998 $langs->load("members");
999 $static_subscription = new Subscription($db);
1000 $account_subscription = getDolGlobalString('ADHERENT_SUBSCRIPTION_ACCOUNTINGACCOUNT', 'NotDefined'); // NotDefined is a reserved word
1001 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_SUBSCRIPTION_REF_PREFIX', 'SU');
1002
1003 while ($obj = $db->fetch_object($resql)) {
1004 $object_key = $obj->bu_type.'_'.$obj->rowid;
1005
1006 // Add object in payment
1007 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
1008 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
1009 'amount' => $obj->amount_payment,
1010 'bu_url_id' => $obj->bu_url_id,
1011 );
1012 }
1013
1014 // Set object infos
1015 if (!isset($tabobject[$object_key])) {
1016 $static_subscription->id = $obj->rowid;
1017 $static_subscription->ref = $prefix_ref.$obj->rowid;
1018 $tabobject[$object_key] = array(
1019 'id' => $obj->rowid,
1020 'ref' => $prefix_ref.$obj->rowid,
1021 'total_ht' => -$obj->amount_payment,
1022 'total_ttc' => -$obj->amount_payment,
1023 'url' => $static_subscription->getNomUrl(1),
1024 'operations' => array(),
1025 'vats' => array(),
1026 );
1027 }
1028
1029 // Add amount for the accountancy code
1030 $tabobject[$object_key]['operations'][$account_subscription] = array(
1031 'total_ht' => -$obj->amount_payment,
1032 'label' => $obj->label.' - '.$obj->lastname.' '.$obj->firstname,
1033 );
1034 }
1035 } else {
1037 }
1038 break;
1039 case 'banktransfert':
1040 // Bank transfer
1041 //------------------------------------------
1042 $sql = "SELECT b.rowid, b.amount, b.label,";
1043 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
1044 $sql .= " FROM ".$db->prefix()."bank_url as bu";
1045 $sql .= " INNER JOIN ".$db->prefix()."bank as b ON bu.url_id = b.rowid";
1046 $sql .= " LEFT JOIN ".$db->prefix()."bank_account as ba ON ba.rowid = b.fk_account";
1047 // Already in bookkeeping or not
1048 if ($in_bookkeeping == 'already') {
1049 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=b.rowid";
1050 } else {
1051 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=b.rowid";
1052 }
1053 $sql .= " WHERE ba.entity IN (".getEntity('bank_account', 0).')'; // We don't share object for accountancy, we use source object sharing
1054 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
1055 $sql .= " AND bu.type = '".$db->escape($type)."'";
1056 // Not already in bookkeeping
1057 if ($in_bookkeeping == 'notyet') {
1058 $sql .= " AND ab.rowid IS NULL";
1059 }
1060
1061 $resql = $db->query($sql);
1062 if ($resql) {
1063 require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/account.class.php';
1064
1065 $static_account_line = new AccountLine($db);
1066 $account_transfer = getDolGlobalString('ACCOUNTING_ACCOUNT_TRANSFER_CASH', 'NotDefined'); // NotDefined is a reserved word
1067 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_TRANSFER_CASH_REF_PREFIX', 'T');
1068
1069 while ($obj = $db->fetch_object($resql)) {
1070 $object_key = $obj->bu_type.'_'.$obj->rowid;
1071
1072 // Add object in payment
1073 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
1074 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
1075 'amount' => -$obj->amount,
1076 'bu_url_id' => $obj->bu_url_id,
1077 );
1078 }
1079
1080 // Set object infos
1081 if (!isset($tabobject[$object_key])) {
1082 $static_account_line->id = $obj->rowid;
1083 $static_account_line->rowid = $obj->rowid;
1084 $static_account_line->ref = $prefix_ref.$obj->rowid;
1085 $tabobject[$object_key] = array(
1086 'id' => $obj->rowid,
1087 'ref' => $prefix_ref.$obj->rowid,
1088 'total_ht' => -$obj->amount,
1089 'total_ttc' => -$obj->amount,
1090 'url' => $static_account_line->getNomUrl(1),
1091 'operations' => array(),
1092 'vats' => array(),
1093 );
1094 }
1095
1096 // Add amount for the accountancy code
1097 $tabobject[$object_key]['operations'][$account_transfer] = array(
1098 'total_ht' => -$obj->amount,
1099 'label' => $obj->label,
1100 );
1101 }
1102 } else {
1104 }
1105 break;
1106 }
1107 }
1108} else {
1110}
1111
1118function payment_filter($v)
1119{
1120 return isset($v['objects']) && count($v['objects']) > 0;
1121}
1122
1123$tabpay = array_filter($tabpay, 'payment_filter');
1124
1125$accountingaccount = new AccountingAccount($db);
1126
1127// Get code of finance journal
1128$accountingjournalstatic = new AccountingJournal($db);
1129$accountingjournalstatic->fetch($id_journal);
1130$journal = $accountingjournalstatic->code;
1131$journal_label = $langs->transnoentitiesnoconv($accountingjournalstatic->label);
1132$MAXNBERRORS = 5;
1133
1134// Write bookkeeping
1135if ($action == 'writebookkeeping' /* && $user->hasRight('accounting', 'bind', 'write') */) { // Test on permission already done
1136 foreach ($tabpay as $payment_id => $payment) {
1137 $accountInfos = $tabaccount[$payment["fk_bank_account"]];
1138
1139 // Set accounting account infos
1140 if (!isset($tabaccountingaccount[$accountInfos['account_number']])) {
1141 $result = $accountingaccount->fetch(0, $accountInfos['account_number'], true);
1142 if ($result < 0) {
1143 setEventMessages($accountingaccount->error, $accountingaccount->errors, 'errors');
1144 $error++;
1145 break;
1146 }
1147 $tabaccountingaccount[$accountInfos['account_number']] = array(
1148 'label' => $result > 0 ? $accountingaccount->label : $langs->trans('NotDefined'),
1149 );
1150 }
1151
1152
1153 $errorforline = 0;
1154 $db->begin();
1155
1156 foreach ($payment['objects'] as $object_key => $object_data) {
1157 $objectInfos = $tabobject[$object_key];
1158 // When one bank line pays several objects, add the ref of the object to the labels, so the lines of each object are not seen as duplicates by BookKeeping::create()
1159 $label_suffix = (count($payment['objects']) > 1 ? ' - '.$objectInfos['ref'] : '');
1160
1161 $total_check = 0;
1162
1163 // Show bank line
1164 if ($object_data['amount'] >= 0) {
1165 $amount = (float) price2num($object_data['amount'], 'MT');
1166 $total_check += $amount;
1167
1168 $bookkeepingToCreate = new BookKeeping($db);
1169
1170 // Unique key is on couple: $payment_id, $objectInfos['id']
1171 // For record in llx_accountaing_bookkeeping, for record with doc_type = 'bank', the value of fk_doc is ID in llx_bank and fk_docdet too. Wetry a fix this way;
1172 //$result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, $objectInfos['id'], $accountInfos['account_number'], $tabaccountingaccount[$accountInfos['account_number']]['label'], $accountInfos['account_ref'], $amount, $journal, $journal_label, '');
1173 $result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, 0, $accountInfos['account_number'], $tabaccountingaccount[$accountInfos['account_number']]['label'], $accountInfos['account_ref'].$label_suffix, $amount, $journal, $journal_label, '');
1174
1175 if ($result < 0) {
1176 $errorforline++;
1177
1178 if (!empty($bookkeepingToCreate->warnings)) {
1179 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1180 }
1181 if (!empty($bookkeepingToCreate->errors)) {
1182 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1183 }
1184 }
1185 }
1186
1187 // Operations
1188 $payment_total_vat = (float) price2num($object_data['amount'] * ($objectInfos['total_ttc'] - $objectInfos['total_ht']) / $objectInfos['total_ttc'], 'MT');
1189 $payment_total_ht = $object_data['amount'] - $payment_total_vat;
1190 $total_operation = 0;
1191 $idx = 1;
1192 $nb_operation = count($objectInfos['operations']);
1193 foreach ($objectInfos['operations'] as $accountancy_code => $operation) {
1194 if (!empty($operation['total_ht'])) {
1195 // Set accounting account infos
1196 if (!isset($tabaccountingaccount[$accountancy_code])) {
1197 $result = $accountingaccount->fetch(0, $accountancy_code, true);
1198 if ($result < 0) {
1199 setEventMessages($accountingaccount->error, $accountingaccount->errors, 'errors');
1200 $accountancy_code_label = $accountingaccount->errorsToString();
1201 $errorforline++;
1202 } elseif ($result > 0) {
1203 $accountancy_code_label = $accountingaccount->label;
1204 } else {
1205 $accountancy_code_label = $langs->trans('NotDefined');
1206 }
1207 $tabaccountingaccount[$accountancy_code] = array('label' => $accountancy_code_label);
1208 }
1209 $accountingAccountInfos = $tabaccountingaccount[$accountancy_code];
1210 if ($idx < $nb_operation) {
1211 $amount = price2num($payment_total_ht * $operation['total_ht'] / $objectInfos['total_ht'], 'MT');
1212 $total_operation += (float) $amount;
1213 } else {
1214 $amount = $payment_total_ht - $total_operation;
1215 }
1216 $total_check -= (float) $amount;
1217
1218 $bookkeepingToCreate = new BookKeeping($db);
1219 //$result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, $objectInfos['id'], $accountancy_code, $accountingAccountInfos['label'], (!empty($operation['label']) ? $operation['label'] : $accountingAccountInfos['label']), -$amount, $journal, $journal_label, '');
1220 $result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, 0, $accountancy_code, $accountingAccountInfos['label'], (!empty($operation['label']) ? $operation['label'] : $accountingAccountInfos['label']).$label_suffix, - (float) $amount, $journal, $journal_label, '');
1221 if ($result < 0) {
1222 $errorforline++;
1223
1224 if (!empty($bookkeepingToCreate->warnings)) {
1225 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1226 }
1227 if (!empty($bookkeepingToCreate->errors)) {
1228 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1229 }
1230 }
1231 }
1232 $idx++;
1233 }
1234
1235 // VATs
1236 $total_vat = 0;
1237 $idx = 1;
1238 $nb_vat = 0;
1239 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1240 foreach ($vats as $vat_tx => $vat_infos) {
1241 $nb_vat++;
1242 }
1243 }
1244 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1245 foreach ($vats as $vat_tx => $vat_infos) {
1246 $amount = $vat_infos['total_tva'] + $vat_infos['total_localtax1'] + $vat_infos['total_localtax2'];
1247 if (!empty($amount)) {
1248 // Set accounting account infos
1249 if (!isset($tabaccountingaccount[$accountancy_code])) {
1250 $result = $accountingaccount->fetch(0, $accountancy_code, true);
1251 if ($result < 0) {
1252 setEventMessages($accountingaccount->error, $accountingaccount->errors, 'errors');
1253 $accountancy_code_label = $accountingaccount->errorsToString();
1254 $errorforline++;
1255 } elseif ($result > 0) {
1256 $accountancy_code_label = $accountingaccount->label;
1257 } else {
1258 $accountancy_code_label = $langs->trans('NotDefined');
1259 }
1260 $tabaccountingaccount[$accountancy_code] = array('label' => $accountancy_code_label);
1261 }
1262 $accountingAccountInfos = $tabaccountingaccount[$accountancy_code];
1263 $amount = (float) price2num($payment_total_vat * $amount / ($objectInfos['total_ttc'] - $objectInfos['total_ht']), 'MT');
1264 $total_vat += $amount;
1265 $total_check -= $amount;
1266
1267 $bookkeepingToCreate = new BookKeeping($db);
1268 //$result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, $objectInfos['id'], $accountancy_code, $accountingAccountInfos['label'], $langs->trans('VAT').' '.price($vat_infos['tva_tx']).'%', -$amount, $journal, $journal_label, '');
1269 $result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, 0, $accountancy_code, $accountingAccountInfos['label'], $langs->trans('VAT').' '.price($vat_infos['tva_tx']).'%'.$label_suffix, -$amount, $journal, $journal_label, '');
1270 if ($result < 0) {
1271 $errorforline++;
1272
1273 if (!empty($bookkeepingToCreate->warnings)) {
1274 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1275 }
1276 if (!empty($bookkeepingToCreate->errors)) {
1277 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1278 }
1279 }
1280 }
1281 $idx++;
1282 }
1283 }
1284
1285 // Show bank line
1286 if ($object_data['amount'] < 0) {
1287 $amount = (float) price2num($object_data['amount'], 'MT');
1288 $total_check += $amount;
1289
1290 $bookkeepingToCreate = new BookKeeping($db);
1291 //$result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, $objectInfos['id'], $accountInfos['account_number'], $tabaccountingaccount[$accountInfos['account_number']]['label'], $accountInfos['account_ref'], $amount, $journal, $journal_label, '');
1292 $result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, 0, $accountInfos['account_number'], $tabaccountingaccount[$accountInfos['account_number']]['label'], $accountInfos['account_ref'].$label_suffix, $amount, $journal, $journal_label, '');
1293 if ($result < 0) {
1294 $errorforline++;
1295
1296 if (!empty($bookkeepingToCreate->warnings)) {
1297 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1298 }
1299 if (!empty($bookkeepingToCreate->errors)) {
1300 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1301 }
1302 }
1303 }
1304
1305 $total_check = price2num($total_check, 'MT');
1306 if (!empty($total_check)) {
1307 $errorforline++;
1308 setEventMessages($langs->trans('ErrorBookkeepingTryInsertNotBalancedTransactionAndCanceled', $objectInfos['ref'], $object_data['bu_url_id']), null, 'errors');
1309 }
1310
1311 if ($errorforline) {
1312 $error++;
1313
1314 if ($error >= $MAXNBERRORS) {
1315 break; // Break in the foreach
1316 }
1317 }
1318 }
1319
1320 if (!$errorforline) {
1321 $db->commit();
1322 } else {
1323 //print 'KO for line '.$key.' '.$error.'<br>';
1324 $db->rollback();
1325
1326 $MAXNBERRORS = 5;
1327 if ($error >= $MAXNBERRORS) {
1328 setEventMessages($langs->trans("ErrorTooManyErrorsProcessStopped").' (>'.$MAXNBERRORS.')', null, 'errors');
1329 break; // Break in the foreach
1330 }
1331 }
1332 }
1333
1334 if (empty($error) && count($tabpay) > 0) {
1335 setEventMessages($langs->trans("GeneralLedgerIsWritten"), null, 'mesgs');
1336 } elseif (count($tabpay) == $error) {
1337 setEventMessages($langs->trans("NoNewRecordSaved"), null, 'warnings');
1338 } else {
1339 setEventMessages($langs->trans("GeneralLedgerSomeRecordWasNotRecorded"), null, 'warnings');
1340 }
1341
1342 $action = '';
1343
1344 // Must reload data, so we make a redirect
1345 if (count($tabpay) != $error) {
1346 $param = 'id_journal='.$id_journal;
1347 $param .= '&date_startday='.$date_startday;
1348 $param .= '&date_startmonth='.$date_startmonth;
1349 $param .= '&date_startyear='.$date_startyear;
1350 $param .= '&date_endday='.$date_endday;
1351 $param .= '&date_endmonth='.$date_endmonth;
1352 $param .= '&date_endyear='.$date_endyear;
1353 $param .= '&in_bookkeeping='.$in_bookkeeping;
1354 header("Location: " . $_SERVER['PHP_SELF'] . '?' . $param);
1355 exit;
1356 }
1357}
1358
1359
1360/*
1361 * View
1362 */
1363
1364$form = new Form($db);
1365$description = null;
1366
1367if (empty($action) || $action == 'view') {
1368 llxHeader('', $langs->trans("FinanceJournal"));
1369
1370 $nom = $langs->trans("FinanceJournal").' | '.$accountingjournalstatic->getNomUrl(0, 1, 1, '', 1);
1371 $nomlink = '';
1372 $builddate = dol_now();
1373 $description = $langs->trans("DescJournalOnlyBindedVisible").'<br>';
1374
1375 $listofchoices = array(
1376 'notyet' => $langs->trans("NotYetInGeneralLedger"),
1377 'already' => $langs->trans("AlreadyInGeneralLedger")
1378 );
1379 $period = $form->selectDate($date_start ?: -1, 'date_start', 0, 0, 0, '', 1, 0).' - '.$form->selectDate($date_end ?: -1, 'date_end', 0, 0, 0, '', 1, 0);
1380 $period .= ' - '.$langs->trans("JournalizationInLedgerStatus").' '.$form->selectarray('in_bookkeeping', $listofchoices, $in_bookkeeping, 1);
1381
1382 $varlink = 'id_journal='.$id_journal;
1383 $periodlink = '';
1384 $exportlink = '';
1385
1386 journalHead($nom, $nomlink, $period, $periodlink, $description, $builddate, $exportlink, array('action' => ''), '', $varlink);
1387
1388 // Test that setup is complete
1389 $sql = "SELECT COUNT(rowid) as nb FROM ".$db->prefix()."bank_account WHERE fk_accountancy_journal IS NULL AND clos = 0";
1390 $resql = $db->query($sql);
1391 if ($resql) {
1392 $obj = $db->fetch_object($resql);
1393 if ($obj->nb > 0) {
1394 print '<br>'.img_warning().' '.$langs->trans("TheJournalCodeIsNotDefinedOnSomeBankAccount");
1395 print ' : '.$langs->trans("AccountancyAreaDescBank", 9, '<strong>'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("Setup")."-".$langs->transnoentitiesnoconv("BankAccounts").'</strong>');
1396 }
1397 } else {
1399 }
1400
1401 // Button to write into Ledger
1402 if (!getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') || getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') == '-1'
1403 || !getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') || getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') == '-1'
1404 || !getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') || getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') == '-1') {
1405 print '<br>'.img_warning().' '.$langs->trans("SomeMandatoryStepsOfSetupWereNotDone");
1406 print ' : '.$langs->trans("AccountancyAreaDescMisc", 4, '<strong>'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("Setup")."-".$langs->transnoentitiesnoconv("MenuDefaultAccounts").'</strong>');
1407 }
1408
1409 print '<br><div class="tabsAction tabsActionNoBottom centerimp">';
1410
1411 if (getDolGlobalString('ACCOUNTING_ENABLE_EXPORT_DRAFT_JOURNAL')) {
1412 print '<input type="button" class="butAction" name="exportcsv" value="'.$langs->trans("ExportDraftJournal").'" onclick="launch_export();" />';
1413 }
1414
1415 if (!getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') || getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') == '-1'
1416 || !getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') || getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') == '-1'
1417 || !getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') || getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') == '-1') {
1418 print '<input type="button" class="butActionRefused classfortooltip" title="'.dol_escape_htmltag($langs->trans("SomeMandatoryStepsOfSetupWereNotDone")).'" value="'.$langs->trans("WriteBookKeeping").'" />';
1419 } else {
1420 if ($in_bookkeeping == 'notyet') {
1421 print '<input type="button" class="butAction" name="writebookkeeping" value="'.$langs->trans("WriteBookKeeping").'" onclick="writebookkeeping();" />';
1422 } else {
1423 print '<a class="butActionRefused classfortooltip" name="writebookkeeping">'.$langs->trans("WriteBookKeeping").'</a>';
1424 }
1425 }
1426 print '</div>';
1427
1428 // TODO Avoid using js. We can use a direct link with $param
1429 print '
1430 <script type="text/javascript">
1431 function writebookkeeping() {
1432 console.log("Set value into form and submit");
1433 $("div.fiche form input[name=\"action\"]").val("writebookkeeping");
1434 $("div.fiche form input[type=\"submit\"]").click();
1435 $("div.fiche form input[name=\"action\"]").val("");
1436 }
1437 </script>';
1438
1439 /*
1440 * Show result array
1441 */
1442 print '<br>';
1443
1444 $i = 0;
1445 print '<div class="div-table-responsive">';
1446 print '<table class="noborder centpercent">';
1447 print '<tr class="liste_titre">';
1448 print "<td>".$langs->trans("Date")."</td>";
1449 print "<td>".$langs->trans("Piece").' ('.$langs->trans("ObjectsRef").')</td>';
1450 print "<td>".$langs->trans("AccountAccounting")."</td>";
1451 print "<td>".$langs->trans("LabelOperation")."</td>";
1452 print '<td class="center">'.$langs->trans("PaymentMode")."</td>";
1453 print '<td class="right">'.$langs->trans("AccountingDebit")."</td>";
1454 print '<td class="right">'.$langs->trans("AccountingCredit")."</td>";
1455 print "</tr>\n";
1456
1457 foreach ($tabpay as $payment_id => $payment) {
1458 $accountInfos = $tabaccount[$payment["fk_bank_account"]];
1459 $date = dol_print_date($payment["date"], 'day');
1460 $i++;
1461
1462 foreach ($payment['objects'] as $object_key => $object_data) {
1463 $objectInfos = $tabobject[$object_key];
1464 // When one bank line pays several objects, add the ref of the object to the labels, so the lines of each object are not seen as duplicates by BookKeeping::create()
1465 $label_suffix = (count($payment['objects']) > 1 ? ' - '.$objectInfos['ref'] : '');
1466
1467 // Show bank line
1468 if ($object_data['amount'] >= 0) {
1469 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], $accountInfos['account_number'], $accountInfos['account_ref'].$label_suffix, $payment['type_payment'], $object_data['amount']);
1470 }
1471
1472 // Operations
1473 $payment_total_vat = (float) price2num($object_data['amount'] * ($objectInfos['total_ttc'] - $objectInfos['total_ht']) / $objectInfos['total_ttc'], 'MT');
1474 $payment_total_ht = $object_data['amount'] - $payment_total_vat;
1475 $total_operation = 0;
1476 $idx = 1;
1477 $nb_operation = count($objectInfos['operations']);
1478 foreach ($objectInfos['operations'] as $accountancy_code => $operation) {
1479 // Set accounting account infos
1480 if (!isset($tabaccountingaccount[$accountancy_code])) {
1481 $result = $accountingaccount->fetch(0, $accountancy_code, true);
1482 $tabaccountingaccount[$accountancy_code] = array(
1483 'label' => $result < 0 ? $accountingaccount->errorsToString() : ($result > 0 ? $accountingaccount->label : $langs->trans('NotDefined')),
1484 );
1485 }
1486 $accountingAccountInfos = $tabaccountingaccount[$accountancy_code];
1487 if (!empty($operation['total_ht'])) {
1488 if ($idx < $nb_operation) {
1489 $value = price2num($payment_total_ht * $operation['total_ht'] / $objectInfos['total_ht'], 'MT');
1490 $total_operation += (float) $value;
1491 } else {
1492 $value = $payment_total_ht - $total_operation;
1493 }
1494 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], (string) $accountancy_code, (!empty($operation['label']) ? $operation['label'] : $accountingAccountInfos['label']).$label_suffix, $payment['type_payment'], - (float) $value);
1495 }
1496 $idx++;
1497 }
1498
1499 // VATs
1500 $total_vat = 0;
1501 $idx = 1;
1502 $nb_vat = 0;
1503 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1504 foreach ($vats as $vat_tx => $vat_infos) {
1505 $nb_vat++;
1506 }
1507 }
1508 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1509 foreach ($vats as $vat_tx => $vat_infos) {
1510 $amount_vat = $vat_infos['total_tva'] + $vat_infos['total_localtax1'] + $vat_infos['total_localtax2'];
1511 if (!empty($amount_vat)) {
1512 $amount_vat = (float) price2num($payment_total_vat * $amount_vat / ($objectInfos['total_ttc'] - $objectInfos['total_ht']), 'MT');
1513 $total_vat += $amount_vat;
1514 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], $accountancy_code, $langs->trans('VAT').' '.price($vat_infos['tva_tx']).'%'.$label_suffix, $payment['type_payment'], -$amount_vat);
1515 }
1516 $idx++;
1517 }
1518 }
1519
1520 // Show bank line
1521 if ($object_data['amount'] < 0) {
1522 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], $accountInfos['account_number'], $accountInfos['account_ref'].$label_suffix, $payment['type_payment'], $object_data['amount']);
1523 }
1524 }
1525 }
1526
1527 if (!$i) {
1528 $colspan = 8;
1529 print '<tr class="oddeven"><td colspan="'.$colspan.'"><span class="opacitymedium">'.$langs->trans("NoRecordFound").'</span></td></tr>';
1530 }
1531
1532 print "</table>";
1533 print '</div>';
1534
1535 llxFooter();
1536}
1537
1538$db->close();
journalHead($nom, $variant, $period, $periodlink, $description, $builddate, $exportlink='', $moreparam=array(), $calcmode='', $varlink='', $moreoptions=array())
Show header of a page used to transfer/dispatch data in accounting.
getDefaultDatesForTransfer()
Return Default dates for transfer based on periodicity option in accountancy setup.
llxFooter($comment='', $zone='private', $disabledoutputofmessages=0)
Empty footer.
Definition wrapper.php:91
if(!defined('NOREQUIRESOC')) if(!defined( 'NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined( 'NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined( 'NOREQUIREAJAX')) llxHeader($head='', $title='', $help_url='', $target='', $disablejs=0, $disablehead=0, $arrayofjs='', $arrayofcss='', $morequerystring='', $morecssonbody='', $replacemainareaby='', $disablenofollow=0, $disablenoindex=0)
Empty header.
Definition wrapper.php:73
Class to manage bank accounts.
Class to manage bank transaction lines.
Class to manage accounting accounts.
Class to manage accounting journals.
Class to manage Ledger (General Ledger and Subledger)
Class for managing the social charges.
const TYPE_SITUATION
Situation invoice.
Class to manage donations.
Definition don.class.php:41
Class to manage Trips and Expenses.
Class to manage suppliers invoices.
const TYPE_DEPOSIT
Deposit invoice.
const TYPE_CREDIT_NOTE
Credit note invoice.
const TYPE_REPLACEMENT
Replacement invoice.
Class to manage invoices.
const TYPE_REPLACEMENT
Replacement invoice.
const TYPE_SITUATION
Situation invoice.
const TYPE_DEPOSIT
Deposit invoice.
const TYPE_CREDIT_NOTE
Credit note invoice.
static printJournalLine($langs, $date, $ref, $accountAccounting, $labelOperation, $paymentMode, $amount)
Print line into the journal table.
Class to manage generation of HTML components Only common components must be here.
Loan.
Class to manage payments of salaries.
Class to manage various payments.
Class to manage subscriptions of foundation members.
Class to manage VAT - Value-added tax (also known in French as TVA)
Definition tva.class.php:39
global $mysoc
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition date.lib.php:620
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition date.lib.php:639
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $conf
The main.inc.php has been included so the following variable are now defined:
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $db
API class for accounts.
$date_start
Variables from include:
dol_now($mode='gmt')
Return date for now.
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm='auto', $check=1)
Return a timestamp date built from detailed information (by default a local PHP server timestamp) Rep...
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='', $noduplicate=0, $attop=0)
Set event messages in dol_events session object.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
getTaxesFromId($vatrate, $buyer=null, $seller=null, $firstparamisid=1)
Get tax (VAT) main information from Id.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0, $nodefault=0)
Return value of a param into GET or POST supervariable.
GETPOSTINT($paramname, $method=0, $nodefault=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false, $decorate=0)
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.
GETPOSTISSET($paramname)
Return true if we are in a context of submitting the parameter $paramname from a POST of a form.
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
isModEnabled($module)
Is Dolibarr module enabled.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.
payment_filter($v)
Filter for payment.