dolibarr 22.0.5
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 *
14 * This program is free software; you can redistribute it and/or modify
15 * it under the terms of the GNU General Public License as published by
16 * the Free Software Foundation; either version 3 of the License, or
17 * (at your option) any later version.
18 *
19 * This program is distributed in the hope that it will be useful,
20 * but WITHOUT ANY WARRANTY; without even the implied warranty of
21 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 * GNU General Public License for more details.
23 *
24 * You should have received a copy of the GNU General Public License
25 * along with this program. If not, see <https://www.gnu.org/licenses/>.
26 */
27
33require '../../main.inc.php';
34require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
35require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
36require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
37require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingjournal.class.php';
38require_once DOL_DOCUMENT_ROOT.'/accountancy/class/bookkeeping.class.php';
39require_once DOL_DOCUMENT_ROOT.'/accountancy/class/bookkeeping.class.php';
40require_once DOL_DOCUMENT_ROOT.'/core/class/html.formaccounting.class.php';
41
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 {
318 dol_print_error($db);
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 {
431 dol_print_error($db);
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 {
543 dol_print_error($db);
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 {
608 dol_print_error($db);
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 = ".$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 {
678 dol_print_error($db);
679 }
680 break;
681 case 'payment_vat':
682 // Payment VAT
683 //------------------------------------------
684 $sql = "SELECT t.rowid,";
685 $sql .= " t.amount AS amount_payment, t.label AS label,";
686 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
687 $sql .= " FROM ".$db->prefix()."tva as t";
688 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = t.rowid AND bu.type = '".$db->escape($type)."'";
689 // Already in bookkeeping or not
690 if ($in_bookkeeping == 'already') {
691 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=t.rowid";
692 } else {
693 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=t.rowid";
694 }
695 $sql .= " WHERE bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
696 // $sql .= " AND t.entity = " . $conf->entity; // TODO when entity is managed in tva
697 // Not already in bookkeeping
698 if ($in_bookkeeping == 'notyet') {
699 $sql .= " AND ab.rowid IS NULL";
700 }
701
702 $resql = $db->query($sql);
703 if ($resql) {
704 require_once DOL_DOCUMENT_ROOT.'/compta/tva/class/tva.class.php';
705
706 $langs->load("salaries");
707 $static_tva = new Tva($db);
708 $account_pay_vat = getDolGlobalString('ACCOUNTING_VAT_PAY_ACCOUNT', 'NotDefined'); // NotDefined is a reserved word
709 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_VAT_REF_PREFIX', 'VAT');
710
711 while ($obj = $db->fetch_object($resql)) {
712 $object_key = $obj->bu_type.'_'.$obj->rowid;
713
714 // Add object in payment
715 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
716 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
717 'amount' => -$obj->amount_payment,
718 'bu_url_id' => $obj->bu_url_id,
719 );
720 }
721
722 // Set object infos
723 if (!isset($tabobject[$object_key])) {
724 $static_tva->id = $obj->rowid;
725 $static_tva->ref = $prefix_ref.$obj->rowid;
726 $tabobject[$object_key] = array(
727 'id' => $obj->rowid,
728 'ref' => $prefix_ref.$obj->rowid,
729 'total_ht' => -$obj->amount_payment,
730 'total_ttc' => -$obj->amount_payment,
731 'url' => $static_tva->getNomUrl(1),
732 'operations' => array(),
733 'vats' => array(),
734 );
735 }
736
737 // Add amount for the accountancy code
738 $tabobject[$object_key]['operations'][$account_pay_vat] = array(
739 'total_ht' => -$obj->amount_payment,
740 'label' => $obj->label,
741 );
742 }
743 } else {
744 dol_print_error($db);
745 }
746 break;
747 case 'payment_donation':
748 // Payment donation
749 //------------------------------------------
750 $sql = "SELECT d.rowid, d.amount AS don_amount,";
751 $sql .= " pd.amount AS amount_payment,";
752 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
753 $sql .= " FROM ".$db->prefix()."payment_donation as pd";
754 $sql .= " INNER JOIN ".$db->prefix()."don as d ON pd.fk_donation = d.rowid";
755 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pd.rowid AND bu.type = '".$db->escape($type)."'";
756 // Already in bookkeeping or not
757 if ($in_bookkeeping == 'already') {
758 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=d.rowid";
759 } else {
760 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=d.rowid";
761 }
762 $sql .= " WHERE d.entity IN (".getEntity('donation', 0).')'; // We don't share object for accountancy, we use source object sharing
763 // Not already in bookkeeping
764 if ($in_bookkeeping == 'notyet') {
765 $sql .= " AND ab.rowid IS NULL";
766 }
767 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
768
769 $resql = $db->query($sql);
770 if ($resql) {
771 require_once DOL_DOCUMENT_ROOT.'/don/class/don.class.php';
772
773 $langs->load("donations");
774 $static_don = new Don($db);
775 $account_pay_donation = getDolGlobalString('DONATION_ACCOUNTINGACCOUNT', 'NotDefined'); // NotDefined is a reserved word
776 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_DONATION_REF_PREFIX', 'D');
777
778 while ($obj = $db->fetch_object($resql)) {
779 $object_key = $obj->bu_type.'_'.$obj->rowid;
780
781 // Add object in payment
782 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
783 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
784 'amount' => $obj->amount_payment,
785 'bu_url_id' => $obj->bu_url_id,
786 );
787 }
788
789 // Set object infos
790 if (!isset($tabobject[$object_key])) {
791 $static_don->id = $obj->rowid;
792 $static_don->ref = $prefix_ref.$obj->rowid;
793 $tabobject[$object_key] = array(
794 'id' => $obj->rowid,
795 'ref' => $prefix_ref.$obj->rowid,
796 'total_ht' => $obj->don_amount,
797 'total_ttc' => $obj->don_amount,
798 'url' => $static_don->getNomUrl(1),
799 'operations' => array(),
800 'vats' => array(),
801 );
802 }
803
804 // Add amount for the accountancy code
805 $tabobject[$object_key]['operations'][$account_pay_donation] = array(
806 'total_ht' => $obj->don_amount,
807 'label' => $langs->trans('Donation').' '.$prefix_ref.$obj->rowid,
808 );
809 }
810 } else {
811 dol_print_error($db);
812 }
813 break;
814 case 'payment_loan':
815 // Payment loan
816 //------------------------------------------
817 $sql = "SELECT l.rowid, l.capital AS loan_capital, l.accountancy_account_capital, l.accountancy_account_interest, l.accountancy_account_insurance, l.label,";
818 $sql .= " pl.amount_capital, pl.amount_interest, pl.amount_insurance,";
819 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
820 $sql .= " FROM ".$db->prefix()."payment_loan as pl";
821 $sql .= " INNER JOIN ".$db->prefix()."loan as l ON pl.fk_loan = l.rowid";
822 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pl.rowid AND bu.type = '".$db->escape($type)."'";
823 // Already in bookkeeping or not
824 if ($in_bookkeeping == 'already') {
825 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=l.rowid";
826 } else {
827 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=l.rowid";
828 }
829 $sql .= " WHERE l.entity = ".$conf->entity; // We don't share object for accountancy, we use source object sharing
830 // Not already in bookkeeping
831 if ($in_bookkeeping == 'notyet') {
832 $sql .= " AND ab.rowid IS NULL";
833 }
834 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
835
836 $resql = $db->query($sql);
837 if ($resql) {
838 require_once DOL_DOCUMENT_ROOT.'/loan/class/loan.class.php';
839
840 $langs->load("loan");
841 $static_loan = new Loan($db);
842 $account_pay_loan_capital = getDolGlobalString('LOAN_ACCOUNTING_ACCOUNT_CAPITAL', 'NotDefined'); // NotDefined is a reserved word
843 $account_pay_loan_interest = getDolGlobalString('LOAN_ACCOUNTING_ACCOUNT_INTEREST', 'NotDefined'); // NotDefined is a reserved word
844 $account_pay_loan_insurance = getDolGlobalString('LOAN_ACCOUNTING_ACCOUNT_INSURANCE', 'NotDefined'); // NotDefined is a reserved word
845 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_LOAN_REF_PREFIX', 'L');
846
847 while ($obj = $db->fetch_object($resql)) {
848 $object_key = $obj->bu_type.'_'.$obj->rowid;
849
850 // Add object in payment
851 $payment_amount = $obj->amount_capital + $obj->amount_interest + $obj->amount_insurance;
852 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
853 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
854 'amount' => -$payment_amount,
855 'bu_url_id' => $obj->bu_url_id,
856 );
857 }
858
859 // Set object infos
860 if (!isset($tabobject[$object_key])) {
861 $static_loan->id = $obj->rowid;
862 $static_loan->ref = $prefix_ref.$obj->rowid;
863 $tabobject[$object_key] = array(
864 'id' => $obj->rowid,
865 'ref' => $prefix_ref.$obj->rowid,
866 'total_ht' => -$obj->loan_capital,
867 'total_ttc' => -$obj->loan_capital,
868 'url' => $static_loan->getNomUrl(1),
869 'operations' => array(),
870 'vats' => array(),
871 );
872 }
873
874 // Add amount for the accountancy code
875 $accountancy_account_capital = !empty($obj->accountancy_account_capital) ? $obj->accountancy_account_capital : $account_pay_loan_capital;
876 $tabobject[$object_key]['operations'][$accountancy_account_capital] = array(
877 // virtual total = loan_capital * amount_capital / payment_amount
878 'total_ht' => -($obj->loan_capital * $obj->amount_capital / $payment_amount),
879 'label' => $obj->label.' '.$langs->trans('LoanCapital'),
880 );
881
882 // Add amount for the accountancy code
883 $accountancy_account_interest = !empty($obj->accountancy_account_interest) ? $obj->accountancy_account_interest : $account_pay_loan_interest;
884 $tabobject[$object_key]['operations'][$accountancy_account_interest] = array(
885 // virtual total = loan_capital * amount_interest / payment_amount
886 'total_ht' => -($obj->loan_capital * $obj->amount_interest / $payment_amount),
887 'label' => $obj->label.' '.$langs->trans('Interest'),
888 );
889
890 // 526,23 = 569,74 * x / 15 000,00
891
892 // Add amount for the accountancy code
893 $accountancy_account_insurance = !empty($obj->accountancy_account_insurance) ? $obj->accountancy_account_insurance : $account_pay_loan_insurance;
894 $tabobject[$object_key]['operations'][$accountancy_account_insurance] = array(
895 // virtual total = loan_capital * amount_insurance / payment_amount
896 'total_ht' => -($obj->loan_capital * $obj->amount_insurance / $payment_amount),
897 'label' => $obj->label.' '.$langs->trans('Insurance'),
898 );
899 }
900 } else {
901 dol_print_error($db);
902 }
903 break;
904 case 'payment_various':
905 // Payment various
906 //------------------------------------------
907 $sql = "SELECT pv.rowid,";
908 $sql .= " pv.sens AS sens_payment, pv.amount AS amount_payment, pv.label, pv.accountancy_code,";
909 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
910 $sql .= " FROM ".$db->prefix()."payment_various as pv";
911 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.url_id = pv.rowid AND bu.type = '".$db->escape($type)."'";
912 // Already in bookkeeping or not
913 if ($in_bookkeeping == 'already') {
914 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=pv.rowid";
915 } else {
916 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=pv.rowid";
917 }
918 $sql .= " WHERE pv.entity IN (".getEntity('payment_various', 0).')'; // We don't share object for accountancy, we use source object sharing
919 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
920 // Not already in bookkeeping
921 if ($in_bookkeeping == 'notyet') {
922 $sql .= " AND ab.rowid IS NULL";
923 }
924
925 $resql = $db->query($sql);
926 if ($resql) {
927 require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/paymentvarious.class.php';
928
929 $static_payment_various = new PaymentVarious($db);
930 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_VARIOUS_REF_PREFIX', 'PM');
931
932 while ($obj = $db->fetch_object($resql)) {
933 $object_key = $obj->bu_type.'_'.$obj->rowid;
934
935 $payment_amount = (empty($obj->sens_payment) ? -1 : 1) * $obj->amount_payment;
936 // Add object in payment
937 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
938 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
939 'amount' => $payment_amount,
940 'bu_url_id' => $obj->bu_url_id,
941 );
942 }
943
944 // Set object infos
945 if (!isset($tabobject[$object_key])) {
946 $static_payment_various->id = $obj->rowid;
947 $static_payment_various->ref = $prefix_ref.$obj->rowid;
948 $tabobject[$object_key] = array(
949 'id' => $obj->rowid,
950 'ref' => $prefix_ref.$obj->rowid,
951 'total_ht' => $payment_amount,
952 'total_ttc' => $payment_amount,
953 'url' => $static_payment_various->getNomUrl(1),
954 'operations' => array(),
955 'vats' => array(),
956 );
957 }
958
959 // Add amount for the accountancy code
960 $accountancy_code = !empty($obj->accountancy_code) ? $obj->accountancy_code : 'NotDefined';
961 $tabobject[$object_key]['operations'][$obj->accountancy_code] = array(
962 'total_ht' => $payment_amount,
963 'label' => $obj->label,
964 );
965 }
966 } else {
967 dol_print_error($db);
968 }
969 break;
970 case 'member':
971 // Subscription member
972 //------------------------------------------
973 $sql = "SELECT su.rowid,";
974 $sql .= " su.subscription AS amount_payment, su.note AS label,";
975 $sql .= " adh.lastname, adh.firstname,";
976 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
977 $sql .= " FROM ".$db->prefix()."subscription as su";
978 $sql .= " INNER JOIN ".$db->prefix()."adherent as adh ON adh.rowid = su.fk_adherent";
979 $sql .= " INNER JOIN ".$db->prefix()."bank_url as bu ON bu.fk_bank = su.fk_bank AND bu.type = '".$db->escape($type)."'";
980 // Already in bookkeeping or not
981 if ($in_bookkeeping == 'already') {
982 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=su.rowid";
983 } else {
984 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=su.rowid";
985 }
986 $sql .= " WHERE bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
987 // Not already in bookkeeping
988 if ($in_bookkeeping == 'notyet') {
989 $sql .= " AND ab.rowid IS NULL";
990 }
991
992 $resql = $db->query($sql);
993 if ($resql) {
994 require_once DOL_DOCUMENT_ROOT.'/adherents/class/subscription.class.php';
995
996 $langs->load("members");
997 $static_subscription = new Subscription($db);
998 $account_subscription = getDolGlobalString('ADHERENT_SUBSCRIPTION_ACCOUNTINGACCOUNT', 'NotDefined'); // NotDefined is a reserved word
999 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_SUBSCRIPTION_REF_PREFIX', 'SU');
1000
1001 while ($obj = $db->fetch_object($resql)) {
1002 $object_key = $obj->bu_type.'_'.$obj->rowid;
1003
1004 // Add object in payment
1005 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
1006 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
1007 'amount' => $obj->amount_payment,
1008 'bu_url_id' => $obj->bu_url_id,
1009 );
1010 }
1011
1012 // Set object infos
1013 if (!isset($tabobject[$object_key])) {
1014 $static_subscription->id = $obj->rowid;
1015 $static_subscription->ref = $prefix_ref.$obj->rowid;
1016 $tabobject[$object_key] = array(
1017 'id' => $obj->rowid,
1018 'ref' => $prefix_ref.$obj->rowid,
1019 'total_ht' => -$obj->amount_payment,
1020 'total_ttc' => -$obj->amount_payment,
1021 'url' => $static_subscription->getNomUrl(1),
1022 'operations' => array(),
1023 'vats' => array(),
1024 );
1025 }
1026
1027 // Add amount for the accountancy code
1028 $tabobject[$object_key]['operations'][$account_subscription] = array(
1029 'total_ht' => -$obj->amount_payment,
1030 'label' => $obj->label.' - '.$obj->lastname.' '.$obj->firstname,
1031 );
1032 }
1033 } else {
1034 dol_print_error($db);
1035 }
1036 break;
1037 case 'banktransfert':
1038 // Bank transfer
1039 //------------------------------------------
1040 $sql = "SELECT b.rowid, b.amount, b.label,";
1041 $sql .= " bu.fk_bank, bu.url_id AS bu_url_id, bu.type AS bu_type";
1042 $sql .= " FROM ".$db->prefix()."bank_url as bu";
1043 $sql .= " INNER JOIN ".$db->prefix()."bank as b ON bu.url_id = b.rowid";
1044 $sql .= " LEFT JOIN ".$db->prefix()."bank_account as ba ON ba.rowid = b.fk_account";
1045 // Already in bookkeeping or not
1046 if ($in_bookkeeping == 'already') {
1047 $sql .= " INNER JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=b.rowid";
1048 } else {
1049 $sql .= " LEFT JOIN ".$db->prefix()."accounting_bookkeeping as ab ON ab.fk_doc=bu.fk_bank AND ab.fk_docdet=b.rowid";
1050 }
1051 $sql .= " WHERE ba.entity IN (".getEntity('bank_account', 0).')'; // We don't share object for accountancy, we use source object sharing
1052 $sql .= " AND bu.fk_bank IN (".$db->sanitize(implode(',', $ids)).")";
1053 $sql .= " AND bu.type = '".$db->escape($type)."'";
1054 // Not already in bookkeeping
1055 if ($in_bookkeeping == 'notyet') {
1056 $sql .= " AND ab.rowid IS NULL";
1057 }
1058
1059 $resql = $db->query($sql);
1060 if ($resql) {
1061 require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/account.class.php';
1062
1063 $static_account_line = new AccountLine($db);
1064 $account_transfer = getDolGlobalString('ACCOUNTING_ACCOUNT_TRANSFER_CASH', 'NotDefined'); // NotDefined is a reserved word
1065 $prefix_ref = getDolGlobalString('MAIN_PAYMENT_TRANSFER_CASH_REF_PREFIX', 'T');
1066
1067 while ($obj = $db->fetch_object($resql)) {
1068 $object_key = $obj->bu_type.'_'.$obj->rowid;
1069
1070 // Add object in payment
1071 if (!isset($tabpay[$obj->fk_bank]['objects'][$object_key])) {
1072 $tabpay[$obj->fk_bank]['objects'][$object_key] = array(
1073 'amount' => -$obj->amount,
1074 'bu_url_id' => $obj->bu_url_id,
1075 );
1076 }
1077
1078 // Set object infos
1079 if (!isset($tabobject[$object_key])) {
1080 $static_account_line->id = $obj->rowid;
1081 $static_account_line->rowid = $obj->rowid;
1082 $static_account_line->ref = $prefix_ref.$obj->rowid;
1083 $tabobject[$object_key] = array(
1084 'id' => $obj->rowid,
1085 'ref' => $prefix_ref.$obj->rowid,
1086 'total_ht' => -$obj->amount,
1087 'total_ttc' => -$obj->amount,
1088 'url' => $static_account_line->getNomUrl(1),
1089 'operations' => array(),
1090 'vats' => array(),
1091 );
1092 }
1093
1094 // Add amount for the accountancy code
1095 $tabobject[$object_key]['operations'][$account_transfer] = array(
1096 'total_ht' => -$obj->amount,
1097 'label' => $obj->label,
1098 );
1099 }
1100 } else {
1101 dol_print_error($db);
1102 }
1103 break;
1104 }
1105 }
1106} else {
1107 dol_print_error($db);
1108}
1109
1116function payment_filter($v)
1117{
1118 return isset($v['objects']) && count($v['objects']) > 0;
1119}
1120
1121$tabpay = array_filter($tabpay, 'payment_filter');
1122
1123$accountingaccount = new AccountingAccount($db);
1124
1125// Get code of finance journal
1126$accountingjournalstatic = new AccountingJournal($db);
1127$accountingjournalstatic->fetch($id_journal);
1128$journal = $accountingjournalstatic->code;
1129$journal_label = $langs->transnoentitiesnoconv($accountingjournalstatic->label);
1130$MAXNBERRORS = 5;
1131
1132// Write bookkeeping
1133if ($action == 'writebookkeeping' /* && $user->hasRight('accounting', 'bind', 'write') */) { // Test on permission already done
1134 foreach ($tabpay as $payment_id => $payment) {
1135 $accountInfos = $tabaccount[$payment["fk_bank_account"]];
1136
1137 // Set accounting account infos
1138 if (!isset($tabaccountingaccount[$accountInfos['account_number']])) {
1139 $result = $accountingaccount->fetch(0, $accountInfos['account_number'], true);
1140 if ($result < 0) {
1141 setEventMessages($accountingaccount->error, $accountingaccount->errors, 'errors');
1142 $error++;
1143 break;
1144 }
1145 $tabaccountingaccount[$accountInfos['account_number']] = array(
1146 'label' => $result > 0 ? $accountingaccount->label : $langs->trans('NotDefined'),
1147 );
1148 }
1149
1150
1151 $errorforline = 0;
1152 $db->begin();
1153
1154 foreach ($payment['objects'] as $object_key => $object_data) {
1155 $objectInfos = $tabobject[$object_key];
1156 // 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()
1157 $label_suffix = (count($payment['objects']) > 1 ? ' - '.$objectInfos['ref'] : '');
1158
1159 $total_check = 0;
1160
1161 // Show bank line
1162 if ($object_data['amount'] >= 0) {
1163 $amount = (float) price2num($object_data['amount'], 'MT');
1164 $total_check += $amount;
1165
1166 $bookkeepingToCreate = new BookKeeping($db);
1167
1168 // Unique key is on couple: $payment_id, $objectInfos['id']
1169 // 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;
1170 //$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, '');
1171 $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, '');
1172
1173 if ($result < 0) {
1174 $errorforline++;
1175
1176 if (!empty($bookkeepingToCreate->warnings)) {
1177 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1178 }
1179 if (!empty($bookkeepingToCreate->errors)) {
1180 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1181 }
1182 }
1183 }
1184
1185 // Operations
1186 $payment_total_vat = (float) price2num($object_data['amount'] * ($objectInfos['total_ttc'] - $objectInfos['total_ht']) / $objectInfos['total_ttc'], 'MT');
1187 $payment_total_ht = $object_data['amount'] - $payment_total_vat;
1188 $total_operation = 0;
1189 $idx = 1;
1190 $nb_operation = count($objectInfos['operations']);
1191 foreach ($objectInfos['operations'] as $accountancy_code => $operation) {
1192 if (!empty($operation['total_ht'])) {
1193 // Set accounting account infos
1194 if (!isset($tabaccountingaccount[$accountancy_code])) {
1195 $result = $accountingaccount->fetch(0, $accountancy_code, true);
1196 if ($result < 0) {
1197 setEventMessages($accountingaccount->error, $accountingaccount->errors, 'errors');
1198 $accountancy_code_label = $accountingaccount->errorsToString();
1199 $errorforline++;
1200 } elseif ($result > 0) {
1201 $accountancy_code_label = $accountingaccount->label;
1202 } else {
1203 $accountancy_code_label = $langs->trans('NotDefined');
1204 }
1205 $tabaccountingaccount[$accountancy_code] = array('label' => $accountancy_code_label);
1206 }
1207 $accountingAccountInfos = $tabaccountingaccount[$accountancy_code];
1208 if ($idx < $nb_operation) {
1209 $amount = price2num($payment_total_ht * $operation['total_ht'] / $objectInfos['total_ht'], 'MT');
1210 $total_operation += $amount;
1211 } else {
1212 $amount = $payment_total_ht - $total_operation;
1213 }
1214 $total_check -= $amount;
1215
1216 $bookkeepingToCreate = new BookKeeping($db);
1217 //$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, '');
1218 $result = $bookkeepingToCreate->createFromValues($payment["date"], $objectInfos['ref'], 'bank', $payment_id, 0, $accountancy_code, $accountingAccountInfos['label'], (!empty($operation['label']) ? $operation['label'] : $accountingAccountInfos['label']).$label_suffix, -$amount, $journal, $journal_label, '');
1219 if ($result < 0) {
1220 $errorforline++;
1221
1222 if (!empty($bookkeepingToCreate->warnings)) {
1223 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1224 }
1225 if (!empty($bookkeepingToCreate->errors)) {
1226 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1227 }
1228 }
1229 }
1230 $idx++;
1231 }
1232
1233 // VATs
1234 $total_vat = 0;
1235 $idx = 1;
1236 $nb_vat = 0;
1237 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1238 foreach ($vats as $vat_tx => $vat_infos) {
1239 $nb_vat++;
1240 }
1241 }
1242 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1243 foreach ($vats as $vat_tx => $vat_infos) {
1244 $amount = $vat_infos['total_tva'] + $vat_infos['total_localtax1'] + $vat_infos['total_localtax2'];
1245 if (!empty($amount)) {
1246 // Set accounting account infos
1247 if (!isset($tabaccountingaccount[$accountancy_code])) {
1248 $result = $accountingaccount->fetch(0, $accountancy_code, true);
1249 if ($result < 0) {
1250 setEventMessages($accountingaccount->error, $accountingaccount->errors, 'errors');
1251 $accountancy_code_label = $accountingaccount->errorsToString();
1252 $errorforline++;
1253 } elseif ($result > 0) {
1254 $accountancy_code_label = $accountingaccount->label;
1255 } else {
1256 $accountancy_code_label = $langs->trans('NotDefined');
1257 }
1258 $tabaccountingaccount[$accountancy_code] = array('label' => $accountancy_code_label);
1259 }
1260 $accountingAccountInfos = $tabaccountingaccount[$accountancy_code];
1261 $amount = (float) price2num($payment_total_vat * $amount / ($objectInfos['total_ttc'] - $objectInfos['total_ht']), 'MT');
1262 $total_vat += $amount;
1263 $total_check -= $amount;
1264
1265 $bookkeepingToCreate = new BookKeeping($db);
1266 //$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, '');
1267 $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, '');
1268 if ($result < 0) {
1269 $errorforline++;
1270
1271 if (!empty($bookkeepingToCreate->warnings)) {
1272 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1273 }
1274 if (!empty($bookkeepingToCreate->errors)) {
1275 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1276 }
1277 }
1278 }
1279 $idx++;
1280 }
1281 }
1282
1283 // Show bank line
1284 if ($object_data['amount'] < 0) {
1285 $amount = (float) price2num($object_data['amount'], 'MT');
1286 $total_check += $amount;
1287
1288 $bookkeepingToCreate = new BookKeeping($db);
1289 //$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, '');
1290 $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, '');
1291 if ($result < 0) {
1292 $errorforline++;
1293
1294 if (!empty($bookkeepingToCreate->warnings)) {
1295 setEventMessages(null, $bookkeepingToCreate->warnings, 'warnings');
1296 }
1297 if (!empty($bookkeepingToCreate->errors)) {
1298 setEventMessages(null, $bookkeepingToCreate->errors, 'errors');
1299 }
1300 }
1301 }
1302
1303 $total_check = price2num($total_check, 'MT');
1304 if (!empty($total_check)) {
1305 $errorforline++;
1306 setEventMessages($langs->trans('ErrorBookkeepingTryInsertNotBalancedTransactionAndCanceled', $objectInfos['ref'], $object_data['bu_url_id']), null, 'errors');
1307 }
1308
1309 if ($errorforline) {
1310 $error++;
1311
1312 if ($error >= $MAXNBERRORS) {
1313 break; // Break in the foreach
1314 }
1315 }
1316 }
1317
1318 if (!$errorforline) {
1319 $db->commit();
1320 } else {
1321 //print 'KO for line '.$key.' '.$error.'<br>';
1322 $db->rollback();
1323
1324 $MAXNBERRORS = 5;
1325 if ($error >= $MAXNBERRORS) {
1326 setEventMessages($langs->trans("ErrorTooManyErrorsProcessStopped").' (>'.$MAXNBERRORS.')', null, 'errors');
1327 break; // Break in the foreach
1328 }
1329 }
1330 }
1331
1332 if (empty($error) && count($tabpay) > 0) {
1333 setEventMessages($langs->trans("GeneralLedgerIsWritten"), null, 'mesgs');
1334 } elseif (count($tabpay) == $error) {
1335 setEventMessages($langs->trans("NoNewRecordSaved"), null, 'warnings');
1336 } else {
1337 setEventMessages($langs->trans("GeneralLedgerSomeRecordWasNotRecorded"), null, 'warnings');
1338 }
1339
1340 $action = '';
1341
1342 // Must reload data, so we make a redirect
1343 if (count($tabpay) != $error) {
1344 $param = 'id_journal='.$id_journal;
1345 $param .= '&date_startday='.$date_startday;
1346 $param .= '&date_startmonth='.$date_startmonth;
1347 $param .= '&date_startyear='.$date_startyear;
1348 $param .= '&date_endday='.$date_endday;
1349 $param .= '&date_endmonth='.$date_endmonth;
1350 $param .= '&date_endyear='.$date_endyear;
1351 $param .= '&in_bookkeeping='.$in_bookkeeping;
1352 header("Location: " . $_SERVER['PHP_SELF'] . '?' . $param);
1353 exit;
1354 }
1355}
1356
1357
1358/*
1359 * View
1360 */
1361
1362$form = new Form($db);
1363$description = null;
1364
1365if (empty($action) || $action == 'view') {
1366 llxHeader('', $langs->trans("FinanceJournal"));
1367
1368 $nom = $langs->trans("FinanceJournal").' | '.$accountingjournalstatic->getNomUrl(0, 1, 1, '', 1);
1369 $nomlink = '';
1370 $builddate = dol_now();
1371 $description = $langs->trans("DescJournalOnlyBindedVisible").'<br>';
1372
1373 $listofchoices = array(
1374 'notyet' => $langs->trans("NotYetInGeneralLedger"),
1375 'already' => $langs->trans("AlreadyInGeneralLedger")
1376 );
1377 $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);
1378 $period .= ' - '.$langs->trans("JournalizationInLedgerStatus").' '.$form->selectarray('in_bookkeeping', $listofchoices, $in_bookkeeping, 1);
1379
1380 $varlink = 'id_journal='.$id_journal;
1381 $periodlink = '';
1382 $exportlink = '';
1383
1384 journalHead($nom, $nomlink, $period, $periodlink, $description, $builddate, $exportlink, array('action' => ''), '', $varlink);
1385
1386 // Test that setup is complete
1387 $sql = "SELECT COUNT(rowid) as nb FROM ".$db->prefix()."bank_account WHERE fk_accountancy_journal IS NULL AND clos = 0";
1388 $resql = $db->query($sql);
1389 if ($resql) {
1390 $obj = $db->fetch_object($resql);
1391 if ($obj->nb > 0) {
1392 print '<br>'.img_warning().' '.$langs->trans("TheJournalCodeIsNotDefinedOnSomeBankAccount");
1393 print ' : '.$langs->trans("AccountancyAreaDescBank", 9, '<strong>'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("Setup")."-".$langs->transnoentitiesnoconv("BankAccounts").'</strong>');
1394 }
1395 } else {
1396 dol_print_error($db);
1397 }
1398
1399 // Button to write into Ledger
1400 if (!getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') || getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') == '-1'
1401 || !getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') || getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') == '-1'
1402 || !getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') || getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') == '-1') {
1403 print '<br>'.img_warning().' '.$langs->trans("SomeMandatoryStepsOfSetupWereNotDone");
1404 print ' : '.$langs->trans("AccountancyAreaDescMisc", 4, '<strong>'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("MenuAccountancy").'-'.$langs->transnoentitiesnoconv("Setup")."-".$langs->transnoentitiesnoconv("MenuDefaultAccounts").'</strong>');
1405 }
1406
1407 print '<br><div class="tabsAction tabsActionNoBottom centerimp">';
1408
1409 if (getDolGlobalString('ACCOUNTING_ENABLE_EXPORT_DRAFT_JOURNAL')) {
1410 print '<input type="button" class="butAction" name="exportcsv" value="'.$langs->trans("ExportDraftJournal").'" onclick="launch_export();" />';
1411 }
1412
1413 if (!getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') || getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER') == '-1'
1414 || !getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') || getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER') == '-1'
1415 || !getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') || getDolGlobalString('SALARIES_ACCOUNTING_ACCOUNT_PAYMENT') == '-1') {
1416 print '<input type="button" class="butActionRefused classfortooltip" title="'.dol_escape_htmltag($langs->trans("SomeMandatoryStepsOfSetupWereNotDone")).'" value="'.$langs->trans("WriteBookKeeping").'" />';
1417 } else {
1418 if ($in_bookkeeping == 'notyet') {
1419 print '<input type="button" class="butAction" name="writebookkeeping" value="'.$langs->trans("WriteBookKeeping").'" onclick="writebookkeeping();" />';
1420 } else {
1421 print '<a class="butActionRefused classfortooltip" name="writebookkeeping">'.$langs->trans("WriteBookKeeping").'</a>';
1422 }
1423 }
1424 print '</div>';
1425
1426 // TODO Avoid using js. We can use a direct link with $param
1427 print '
1428 <script type="text/javascript">
1429 function writebookkeeping() {
1430 console.log("Set value into form and submit");
1431 $("div.fiche form input[name=\"action\"]").val("writebookkeeping");
1432 $("div.fiche form input[type=\"submit\"]").click();
1433 $("div.fiche form input[name=\"action\"]").val("");
1434 }
1435 </script>';
1436
1437 /*
1438 * Show result array
1439 */
1440 print '<br>';
1441
1442 $i = 0;
1443 print '<div class="div-table-responsive">';
1444 print '<table class="noborder centpercent">';
1445 print '<tr class="liste_titre">';
1446 print "<td>".$langs->trans("Date")."</td>";
1447 print "<td>".$langs->trans("Piece").' ('.$langs->trans("ObjectsRef").')</td>';
1448 print "<td>".$langs->trans("AccountAccounting")."</td>";
1449 print "<td>".$langs->trans("LabelOperation")."</td>";
1450 print '<td class="center">'.$langs->trans("PaymentMode")."</td>";
1451 print '<td class="right">'.$langs->trans("AccountingDebit")."</td>";
1452 print '<td class="right">'.$langs->trans("AccountingCredit")."</td>";
1453 print "</tr>\n";
1454
1455 foreach ($tabpay as $payment_id => $payment) {
1456 $accountInfos = $tabaccount[$payment["fk_bank_account"]];
1457 $date = dol_print_date($payment["date"], 'day');
1458 $i++;
1459
1460 foreach ($payment['objects'] as $object_key => $object_data) {
1461 $objectInfos = $tabobject[$object_key];
1462 // 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()
1463 $label_suffix = (count($payment['objects']) > 1 ? ' - '.$objectInfos['ref'] : '');
1464
1465 // Show bank line
1466 if ($object_data['amount'] >= 0) {
1467 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], $accountInfos['account_number'], $accountInfos['account_ref'].$label_suffix, $payment['type_payment'], $object_data['amount']);
1468 }
1469
1470 // Operations
1471 $payment_total_vat = (float) price2num($object_data['amount'] * ($objectInfos['total_ttc'] - $objectInfos['total_ht']) / $objectInfos['total_ttc'], 'MT');
1472 $payment_total_ht = $object_data['amount'] - $payment_total_vat;
1473 $total_operation = 0;
1474 $idx = 1;
1475 $nb_operation = count($objectInfos['operations']);
1476 foreach ($objectInfos['operations'] as $accountancy_code => $operation) {
1477 // Set accounting account infos
1478 if (!isset($tabaccountingaccount[$accountancy_code])) {
1479 $result = $accountingaccount->fetch(0, $accountancy_code, true);
1480 $tabaccountingaccount[$accountancy_code] = array(
1481 'label' => $result < 0 ? $accountingaccount->errorsToString() : ($result > 0 ? $accountingaccount->label : $langs->trans('NotDefined')),
1482 );
1483 }
1484 $accountingAccountInfos = $tabaccountingaccount[$accountancy_code];
1485 if (!empty($operation['total_ht'])) {
1486 if ($idx < $nb_operation) {
1487 $value = price2num($payment_total_ht * $operation['total_ht'] / $objectInfos['total_ht'], 'MT');
1488 $total_operation += $value;
1489 } else {
1490 $value = $payment_total_ht - $total_operation;
1491 }
1492 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], $accountancy_code, (!empty($operation['label']) ? $operation['label'] : $accountingAccountInfos['label']).$label_suffix, $payment['type_payment'], -$value);
1493 }
1494 $idx++;
1495 }
1496
1497 // VATs
1498 $total_vat = 0;
1499 $idx = 1;
1500 $nb_vat = 0;
1501 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1502 foreach ($vats as $vat_tx => $vat_infos) {
1503 $nb_vat++;
1504 }
1505 }
1506 foreach ($objectInfos['vats'] as $accountancy_code => $vats) {
1507 foreach ($vats as $vat_tx => $vat_infos) {
1508 $amount_vat = $vat_infos['total_tva'] + $vat_infos['total_localtax1'] + $vat_infos['total_localtax2'];
1509 if (!empty($amount_vat)) {
1510 $amount_vat = (float) price2num($payment_total_vat * $amount_vat / ($objectInfos['total_ttc'] - $objectInfos['total_ht']), 'MT');
1511 $total_vat += $amount_vat;
1512 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], $accountancy_code, $langs->trans('VAT').' '.price($vat_infos['tva_tx']).'%'.$label_suffix, $payment['type_payment'], -$amount_vat);
1513 }
1514 $idx++;
1515 }
1516 }
1517
1518 // Show bank line
1519 if ($object_data['amount'] < 0) {
1520 FormAccounting::printJournalLine($langs, $date, $objectInfos['url'], $accountInfos['account_number'], $accountInfos['account_ref'].$label_suffix, $payment['type_payment'], $object_data['amount']);
1521 }
1522 }
1523 }
1524
1525 if (!$i) {
1526 $colspan = 8;
1527 print '<tr class="oddeven"><td colspan="'.$colspan.'"><span class="opacitymedium">'.$langs->trans("NoRecordFound").'</span></td></tr>';
1528 }
1529
1530 print "</table>";
1531 print '</div>';
1532
1533 llxFooter();
1534}
1535
1536$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 - Taxe sur la valeur ajoutée)
Definition tva.class.php:38
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition date.lib.php:600
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition date.lib.php:619
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.
GETPOSTINT($paramname, $method=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
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.
dol_now($mode='auto')
Return date for now.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
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).
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
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.
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
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.