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