dolibarr 25.0.0-alpha
clientfourn.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2002-2006 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3 * Copyright (C) 2004-2017 Laurent Destailleur <eldy@users.sourceforge.net>
4 * Copyright (C) 2026 Jose Martinez <jose.martinez@pichinov.com>
5 * Copyright (C) 2005-2012 Regis Houssin <regis.houssin@inodbox.com>
6 * Copyright (C) 2012 Cédric Salvador <csalvador@gpcsolutions.fr>
7 * Copyright (C) 2012-2014 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
8 * Copyright (C) 2014-2106 Ferran Marcet <fmarcet@2byte.es>
9 * Copyright (C) 2014 Juanjo Menent <jmenent@2byte.es>
10 * Copyright (C) 2014 Florian Henry <florian.henry@open-concept.pro>
11 * Copyright (C) 2018-2024 Frédéric France <frederic.france@free.fr>
12 * Copyright (C) 2020 Maxime DEMAREST <maxime@indelog.fr>
13 * Copyright (C) 2021 Alexandre Spangaro <aspangaro@open-dsi.fr>
14 * Copyright (C) 2024-2026 MDW <mdeweerd@users.noreply.github.com>
15 *
16 * This program is free software; you can redistribute it and/or modify
17 * it under the terms of the GNU General Public License as published by
18 * the Free Software Foundation; either version 3 of the License, or
19 * (at your option) any later version.
20 *
21 * This program is distributed in the hope that it will be useful,
22 * but WITHOUT ANY WARRANTY; without even the implied warranty of
23 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 * GNU General Public License for more details.
25 *
26 * You should have received a copy of the GNU General Public License
27 * along with this program. If not, see <https://www.gnu.org/licenses/>.
28 */
29
36// Load Dolibarr environment
37require '../../main.inc.php';
38require_once DOL_DOCUMENT_ROOT.'/compta/tva/class/tva.class.php';
39require_once DOL_DOCUMENT_ROOT.'/compta/sociales/class/chargesociales.class.php';
40require_once DOL_DOCUMENT_ROOT.'/user/class/user.class.php';
41require_once DOL_DOCUMENT_ROOT.'/core/lib/report.lib.php';
42require_once DOL_DOCUMENT_ROOT.'/core/lib/tax.lib.php';
43require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
44require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
45require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountancycategory.class.php';
46require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
47
57// Load translation files required by the page
58$langs->loadLangs(array('compta', 'bills', 'donation', 'salaries', 'accountancy', 'loan'));
59
60$date_startmonth = GETPOSTINT('date_startmonth');
61$date_startday = GETPOSTINT('date_startday');
62$date_startyear = GETPOSTINT('date_startyear');
63$date_endmonth = GETPOSTINT('date_endmonth');
64$date_endday = GETPOSTINT('date_endday');
65$date_endyear = GETPOSTINT('date_endyear');
66$showaccountdetail = GETPOST('showaccountdetail', 'aZ09') ? GETPOST('showaccountdetail', 'aZ09') : 'yes';
67
68$limit = GETPOSTINT('limit') ? GETPOSTINT('limit') : $conf->liste_limit;
69$sortfield = GETPOST('sortfield', 'aZ09comma');
70$sortorder = GETPOST('sortorder', 'aZ09comma');
71$page = GETPOSTISSET('pageplusone') ? (GETPOSTINT('pageplusone') - 1) : GETPOSTINT("page");
72if (empty($page) || $page == -1) {
73 $page = 0;
74} // If $page is not defined, or '' or -1
75$offset = $limit * $page;
76$pageprev = $page - 1;
77$pagenext = $page + 1;
78//if (! $sortfield) $sortfield='s.nom, s.rowid';
79if (!$sortorder) {
80 $sortorder = 'ASC';
81}
82
83// Date range
84$year = GETPOSTINT('year'); // this is used for navigation previous/next. It is the last year to show in filter
85if (empty($year)) {
86 $year_current = dol_print_date(dol_now(), "%Y");
87 $month_current = dol_print_date(dol_now(), "%m");
88 $year_start = $year_current;
89} else {
90 $year_current = $year;
91 $month_current = dol_print_date(dol_now(), "%m");
92 $year_start = $year;
93}
94$date_start = dol_mktime(0, 0, 0, $date_startmonth, $date_startday, $date_startyear);
95$date_end = dol_mktime(23, 59, 59, $date_endmonth, $date_endday, $date_endyear);
96
97// We define date_start and date_end
98if (empty($date_start) || empty($date_end)) { // We define date_start and date_end
99 $q = GETPOST("q") ? GETPOSTINT("q") : 0;
100 if ($q == 0) {
101 // We define date_start and date_end
102 $year_end = $year_start;
103 $month_start = GETPOST("month") ? GETPOSTINT("month") : getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1);
104 $month_end = "";
105 if (!GETPOST('month')) {
106 if (!$year && $month_start > $month_current) {
107 $year_start--;
108 $year_end--;
109 }
110 if (getDolGlobalInt('SOCIETE_FISCAL_MONTH_START') > 1) {
111 $month_end = $month_start - 1;
112 $year_end = $year_start + 1;
113 }
114 if ($month_end < 1) {
115 $month_end = 12;
116 }
117 } else {
118 $month_end = $month_start;
119 }
120 $date_start = dol_get_first_day($year_start, $month_start, false);
121 $date_end = dol_get_last_day($year_end, $month_end, false);
122 }
123 if ($q == 1) {
124 $date_start = dol_get_first_day($year_start, 1, false);
125 $date_end = dol_get_last_day($year_start, 3, false);
126 }
127 if ($q == 2) {
128 $date_start = dol_get_first_day($year_start, 4, false);
129 $date_end = dol_get_last_day($year_start, 6, false);
130 }
131 if ($q == 3) {
132 $date_start = dol_get_first_day($year_start, 7, false);
133 $date_end = dol_get_last_day($year_start, 9, false);
134 }
135 if ($q == 4) {
136 $date_start = dol_get_first_day($year_start, 10, false);
137 $date_end = dol_get_last_day($year_start, 12, false);
138 }
139}
140
141// $date_start and $date_end are defined. We force $year_start and $nbofyear
142$tmps = dol_getdate($date_start);
143$year_start = $tmps['year'];
144$tmpe = dol_getdate($date_end);
145$year_end = $tmpe['year'];
146$nbofyear = ($year_end - $year_start) + 1;
147//var_dump("year_start=".$year_start." year_end=".$year_end." nbofyear=".$nbofyear." date_start=".dol_print_date($date_start, 'dayhour')." date_end=".dol_print_date($date_end, 'dayhour'));
148
149// Define modecompta ('CREANCES-DETTES' or 'RECETTES-DEPENSES' or 'BOOKKEEPING')
150$modecompta = getDolGlobalString('ACCOUNTING_MODE');
151if (isModEnabled('accounting')) {
152 $modecompta = 'BOOKKEEPING';
153}
154if (GETPOST("modecompta", 'alpha')) {
155 $modecompta = GETPOST("modecompta", 'alpha');
156}
157
158$AccCat = new AccountancyCategory($db);
159
160// Security check
161$socid = GETPOSTINT('socid');
162if ($user->socid > 0) {
163 $socid = $user->socid;
164}
165
166// Initialize a technical object to manage hooks of page. Note that conf->hooks_modules contains an array of hook context
167$hookmanager->initHooks(['customersupplierreportlist']);
168
169if (isModEnabled('comptabilite')) {
170 $result = restrictedArea($user, 'compta', '', '', 'resultat');
171}
172if (isModEnabled('accounting')) {
173 $result = restrictedArea($user, 'accounting', '', '', 'comptarapport');
174}
175
176/*
177 * View
178 */
179
180llxHeader();
181
182$form = new Form($db);
183
184$periodlink = '';
185$exportlink = '';
186
187$total_ht = 0;
188$total_ttc = 0;
189
190$builddate = '';
191$name = '';
192$period = '';
193$description = '';
194
195// Affiche en-tete de rapport
196if ($modecompta == "CREANCES-DETTES") {
197 $name = $langs->trans("ReportInOut").', '.$langs->trans("ByPredefinedAccountGroups");
198 $period = $form->selectDate($date_start, 'date_start', 0, 0, 0, '', 1, 0).' - '.$form->selectDate($date_end, 'date_end', 0, 0, 0, '', 1, 0);
199 $periodlink = ($year_start ? "<a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year'] - 1)."&modecompta=".$modecompta."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year'] + 1)."&modecompta=".$modecompta."'>".img_next()."</a>" : "");
200 $description = $langs->trans("RulesResultDue");
201 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
202 $description .= $langs->trans("DepositsAreNotIncluded");
203 } else {
204 $description .= $langs->trans("DepositsAreIncluded");
205 }
206 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
207 $description .= $langs->trans("SupplierDepositsAreNotIncluded");
208 }
209 $builddate = dol_now();
210 //$exportlink=$langs->trans("NotYetAvailable");
211} elseif ($modecompta == "RECETTES-DEPENSES") {
212 $name = $langs->trans("ReportInOut").', '.$langs->trans("ByPredefinedAccountGroups");
213 $period = $form->selectDate($date_start, 'date_start', 0, 0, 0, '', 1, 0).' - '.$form->selectDate($date_end, 'date_end', 0, 0, 0, '', 1, 0);
214 $periodlink = ($year_start ? "<a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year'] - 1)."&modecompta=".$modecompta."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year'] + 1)."&modecompta=".$modecompta."'>".img_next()."</a>" : "");
215 $description = $langs->trans("RulesResultInOut");
216 $builddate = dol_now();
217 //$exportlink=$langs->trans("NotYetAvailable");
218} elseif ($modecompta == "BOOKKEEPING") {
219 $name = $langs->trans("ReportInOut").', '.$langs->trans("ByPredefinedAccountGroups");
220 $period = $form->selectDate($date_start, 'date_start', 0, 0, 0, '', 1, 0).' - '.$form->selectDate($date_end, 'date_end', 0, 0, 0, '', 1, 0);
221 $arraylist = array('no' => $langs->trans("CustomerCode"), 'yes' => $langs->trans("AccountWithNonZeroValues"), 'all' => $langs->trans("All"));
222 $period .= ' &nbsp; &nbsp; <span class="opacitymedium">'.$langs->trans("DetailBy").'</span> '.$form->selectarray('showaccountdetail', $arraylist, $showaccountdetail, 0);
223 $periodlink = ($year_start ? "<a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year'] - 1)."&modecompta=".$modecompta."&showaccountdetail=".$showaccountdetail."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year'] + 1)."&modecompta=".$modecompta."&showaccountdetail=".$showaccountdetail."'>".img_next()."</a>" : "");
224 $description = $langs->trans("RulesResultBookkeepingPredefined");
225 $description .= ' ('.$langs->trans("SeePageForSetup", DOL_URL_ROOT.'/accountancy/admin/account.php?mainmenu=accountancy&leftmenu=accountancy_admin', $langs->transnoentitiesnoconv("Accountancy").' / '.$langs->transnoentitiesnoconv("Setup").' / '.$langs->transnoentitiesnoconv("Chartofaccounts")).')';
226 $builddate = dol_now();
227 //$exportlink=$langs->trans("NotYetAvailable");
228}
229
230// Define $calcmode line
231$calcmode = '';
232if (isModEnabled('accounting')) {
233 $calcmode .= '<input type="radio" name="modecompta" id="modecompta3" value="BOOKKEEPING"'.($modecompta == 'BOOKKEEPING' ? ' checked="checked"' : '').'><label for="modecompta3"> '.$langs->trans("CalcModeBookkeeping").'</label>';
234 $calcmode .= '<br>';
235}
236$calcmode .= '<input type="radio" name="modecompta" id="modecompta1" value="RECETTES-DEPENSES"'.($modecompta == 'RECETTES-DEPENSES' ? ' checked="checked"' : '').'><label for="modecompta1"> '.$langs->trans("CalcModePayment");
237if (isModEnabled('accounting')) {
238 $calcmode .= ' <span class="opacitymedium hideonsmartphone">('.$langs->trans("CalcModeNoBookKeeping").')</span>';
239}
240$calcmode .= '</label>';
241$calcmode .= '<br><input type="radio" name="modecompta" id="modecompta2" value="CREANCES-DETTES"'.($modecompta == 'CREANCES-DETTES' ? ' checked="checked"' : '').'><label for="modecompta2"> '.$langs->trans("CalcModeDebt");
242if (isModEnabled('accounting')) {
243 $calcmode .= ' <span class="opacitymedium hideonsmartphone">('.$langs->trans("CalcModeNoBookKeeping").')</span>';
244}
245$calcmode .= '</label>';
246
247
248report_header($name, '', $period, $periodlink, $description, $builddate, $exportlink, array('modecompta' => $modecompta, 'showaccountdetail' => $showaccountdetail), $calcmode);
249
250if (isModEnabled('accounting') && $modecompta != 'BOOKKEEPING') {
251 print info_admin($langs->trans("WarningReportNotReliable"), 0, 0, '1');
252}
253
254// Show report array
255$param = '&modecompta='.urlencode($modecompta).'&showaccountdetail='.urlencode($showaccountdetail);
256if ($date_startday) {
257 $param .= '&date_startday='.$date_startday;
258}
259if ($date_startmonth) {
260 $param .= '&date_startmonth='.$date_startmonth;
261}
262if ($date_startyear) {
263 $param .= '&date_startyear='.$date_startyear;
264}
265if ($date_endday) {
266 $param .= '&date_endday='.$date_endday;
267}
268if ($date_endmonth) {
269 $param .= '&date_endmonth='.$date_endmonth;
270}
271if ($date_endyear) {
272 $param .= '&date_endyear='.$date_endyear;
273}
274
275print '<table class="liste noborder centpercent">';
276print '<tr class="liste_titre">';
277
278if ($modecompta == 'BOOKKEEPING') {
279 print_liste_field_titre("PredefinedGroups", $_SERVER["PHP_SELF"], 'f.thirdparty_code,f.rowid', '', $param, '', $sortfield, $sortorder, '');
280} else {
281 print_liste_field_titre("", $_SERVER["PHP_SELF"], '', '', $param, '', $sortfield, $sortorder, '');
282}
283print_liste_field_titre('');
284if ($modecompta == 'BOOKKEEPING') {
285 print_liste_field_titre("Amount", $_SERVER["PHP_SELF"], 'amount', '', $param, 'class="right"', $sortfield, $sortorder);
286} else {
287 if ($modecompta == 'CREANCES-DETTES') {
288 print_liste_field_titre("AmountHT", $_SERVER["PHP_SELF"], 'amount_ht', '', $param, 'class="right"', $sortfield, $sortorder);
289 } else {
290 print_liste_field_titre(''); // Make 4 columns in total whatever $modecompta is
291 }
292 print_liste_field_titre("AmountTTC", $_SERVER["PHP_SELF"], 'amount_ttc', '', $param, 'class="right"', $sortfield, $sortorder);
293}
294print "</tr>\n";
295
296
297$total_ht_outcome = $total_ttc_outcome = $total_ht_income = $total_ttc_income = 0;
298
299
300if ($modecompta == 'BOOKKEEPING') {
301 // Some shipped charts of accounts (e.g. US-BASE) split income and expense
302 // accounts across more than one pcg_type value (COGS, OTHER_REVENUE,
303 // OTHER_EXPENSES), unlike FR/GB-style charts which only use INCOME/EXPENSE.
304 // Include those here so this report does not silently omit them.
305 $sanitizedpredefinedgroupwhere = "(";
306 $sanitizedpredefinedgroupwhere .= " (pcg_type IN ('EXPENSE', 'COGS', 'OTHER_EXPENSES'))";
307 $sanitizedpredefinedgroupwhere .= " OR ";
308 $sanitizedpredefinedgroupwhere .= " (pcg_type IN ('INCOME', 'OTHER_REVENUE'))";
309 $sanitizedpredefinedgroupwhere .= ")";
310
311 $charofaccountstring = getDolGlobalInt('CHARTOFACCOUNTS');
312 $charofaccountstring = dol_getIdFromCode($db, getDolGlobalString('CHARTOFACCOUNTS'), 'accounting_system', 'rowid', 'pcg_version');
313
314 $sql = "SELECT -1 as socid, aa.pcg_type, SUM(f.credit - f.debit) as amount";
315 if ($showaccountdetail == 'no') {
316 $sql .= ", f.thirdparty_code as name";
317 }
318 $sql .= " FROM ".$db->prefix()."accounting_bookkeeping as f";
319 $sql .= " INNER JOIN ".$db->prefix()."accounting_account as aa";
320 $sql .= " ON aa.account_number = f.numero_compte";
321 $sql .= " AND aa.entity = f.entity"; // Security prevents duplicate.
322 $sql .= " WHERE 1=1";
323 $sql .= " AND ".$sanitizedpredefinedgroupwhere;
324 $sql .= " AND aa.fk_pcg_version = '".$db->escape($charofaccountstring)."'";
325 $sql .= " AND f.entity = ".((int) $conf->entity);
326 if (!empty($date_start) && !empty($date_end)) {
327 $sql .= " AND f.doc_date >= '".$db->idate($date_start)."'";
328 $sql .= " AND f.doc_date <= '".$db->idate($date_end)."'";
329 }
330 $sql .= " GROUP BY aa.pcg_type";
331 if ($showaccountdetail == 'no') {
332 $sql .= ", name, socid"; // group by "accounting group" (INCOME/EXPENSE), then "customer".
333 }
334 $sql .= $db->order($sortfield, $sortorder);
335
336 $oldpcgtype = '';
337
338 dol_syslog("get bookkeeping entries", LOG_DEBUG);
339 $result = $db->query($sql);
340 if ($result) {
341 $num = $db->num_rows($result);
342 $i = 0;
343 if ($num > 0) {
344 while ($i < $num) {
345 $objp = $db->fetch_object($result);
346
347 if ($showaccountdetail == 'no') {
348 if ($objp->pcg_type != $oldpcgtype) {
349 print '<tr class="trforbreak"><td colspan="3" class="tdforbreak">'.dol_escape_htmltag($objp->pcg_type).'</td></tr>';
350 $oldpcgtype = $objp->pcg_type;
351 }
352 }
353
354 if ($showaccountdetail == 'no') {
355 print '<tr class="oddeven">';
356 print '<td></td>';
357 print '<td>';
358 print dol_escape_htmltag($objp->pcg_type);
359 print($objp->name ? ' ('.dol_escape_htmltag($objp->name).')' : ' ('.$langs->trans("Unknown").')');
360 print "</td>\n";
361 print '<td class="right nowraponall"><span class="amount">'.price($objp->amount)."</span></td>\n";
362 print "</tr>\n";
363 } else {
364 print '<tr class="oddeven trforbreak">';
365 print '<td colspan="2" class="tdforbreak">';
366 print dol_escape_htmltag($objp->pcg_type);
367 print "</td>\n";
368 print '<td class="right nowraponall tdforbreak"><span class="amount">'.price($objp->amount)."</span></td>\n";
369 print "</tr>\n";
370 }
371
372 $total_ht += (isset($objp->amount) ? $objp->amount : 0);
373 $total_ttc += (isset($objp->amount) ? $objp->amount : 0);
374
375 if (in_array($objp->pcg_type, array('INCOME', 'OTHER_REVENUE'))) {
376 $total_ht_income += (isset($objp->amount) ? $objp->amount : 0);
377 $total_ttc_income += (isset($objp->amount) ? $objp->amount : 0);
378 }
379 if (in_array($objp->pcg_type, array('EXPENSE', 'COGS', 'OTHER_EXPENSES'))) {
380 $total_ht_outcome -= (isset($objp->amount) ? $objp->amount : 0);
381 $total_ttc_outcome -= (isset($objp->amount) ? $objp->amount : 0);
382 }
383
384 // Loop on detail of all accounts
385 // This make 14 calls for each detail of account (NP, N and month m)
386 if ($showaccountdetail != 'no') {
387 $tmppredefinedgroupwhere = "pcg_type = '".$db->escape($objp->pcg_type)."'";
388 $tmppredefinedgroupwhere .= " AND fk_pcg_version = '".$db->escape($charofaccountstring)."'";
389 //$tmppredefinedgroupwhere .= " AND thirdparty_code = '".$db->escape($objp->name)."'";
390
391 // Get cpts of category/group
392 $cpts = $AccCat->getCptsCat(0, $tmppredefinedgroupwhere);
393
394 foreach ($cpts as $j => $cpt) {
395 $return = $AccCat->getSumDebitCredit($cpt['account_number'], $date_start, $date_end, (empty($cpt['dc']) ? 0 : $cpt['dc']));
396 if ($return < 0) {
397 setEventMessages(null, $AccCat->errors, 'errors');
398 $resultN = 0;
399 } else {
400 $resultN = $AccCat->sdc;
401 }
402
403
404 if ($showaccountdetail == 'all' || $resultN != 0) {
405 print '<tr>';
406 print '<td></td>';
407 print '<td class="tdoverflowmax200"> &nbsp; &nbsp; '.length_accountg($cpt['account_number']).' - '.$cpt['account_label'].'</td>';
408 print '<td class="right nowraponall"><span class="amount">'.price($resultN).'</span></td>';
409 print "</tr>\n";
410 }
411 }
412 }
413
414 $i++;
415 }
416 } else {
417 print '<tr><td colspan="3" class="opacitymedium">'.$langs->trans("NoRecordFound").'</td></tr>';
418 }
419 } else {
420 dol_print_error($db);
421 }
422} else {
423 /*
424 * Customer invoices
425 */
426 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("CustomersInvoices").'</td></tr>';
427
428 if ($modecompta == 'CREANCES-DETTES') {
429 $sql = "SELECT s.nom as name, s.rowid as socid, sum(f.total_ht) as amount_ht, sum(f.total_ttc) as amount_ttc";
430 $sql .= " FROM ".MAIN_DB_PREFIX."societe as s";
431 $sql .= ", ".MAIN_DB_PREFIX."facture as f";
432 $sql .= " WHERE f.fk_soc = s.rowid";
433 $sql .= " AND f.fk_statut IN (1,2)";
434 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
435 $sql .= " AND f.type IN (0,1,2,5)";
436 } else {
437 $sql .= " AND f.type IN (0,1,2,3,5)";
438 }
439 // Add SQL restrictions from hooks (context turnoverreport), e.g. a deposit pivot date restricting deposits by their date
440 $hookmanager->initHooks(array('turnoverreport'));
441 $parameters = array('invoicealias' => 'f', 'issupplier' => 0, 'datefield' => 'datef');
442 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
443 $sql .= $hookmanager->resPrint;
444 if (!empty($date_start) && !empty($date_end)) {
445 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
446 }
447 } elseif ($modecompta == 'RECETTES-DEPENSES') {
448 /*
449 * List of payments (old payments are not seen by this query because, on older versions, they were not linked via payment_invoice.
450 * old versions, they were not linked via payment_invoice. They are added later)
451 */
452 $sql = "SELECT s.nom as name, s.rowid as socid, sum(pf.amount) as amount_ttc";
453 $sql .= " FROM ".MAIN_DB_PREFIX."societe as s";
454 $sql .= ", ".MAIN_DB_PREFIX."facture as f";
455 $sql .= ", ".MAIN_DB_PREFIX."paiement_facture as pf";
456 $sql .= ", ".MAIN_DB_PREFIX."paiement as p";
457 $sql .= " WHERE p.rowid = pf.fk_paiement";
458 $sql .= " AND pf.fk_facture = f.rowid";
459 $sql .= " AND f.fk_soc = s.rowid";
460 if (!empty($date_start) && !empty($date_end)) {
461 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
462 }
463 } else {
464 $sql = '';
465 }
466 $sql .= " AND f.entity IN (".getEntity('invoice').")";
467 if ($socid) {
468 $sql .= " AND f.fk_soc = ".((int) $socid);
469 }
470 $sql .= " GROUP BY name, socid";
471 $sql .= $db->order($sortfield, $sortorder);
472
473 dol_syslog("get customer invoices", LOG_DEBUG);
474 $result = $db->query($sql);
475 if ($result) {
476 $num = $db->num_rows($result);
477 $i = 0;
478 while ($i < $num) {
479 $objp = $db->fetch_object($result);
480
481 print '<tr class="oddeven">';
482 print '<td>&nbsp;</td>';
483 print "<td>".$langs->trans("Bills").' <a href="'.DOL_URL_ROOT.'/compta/facture/list.php?socid='.$objp->socid.'">'.$objp->name."</td>\n";
484
485 print '<td class="right">';
486 if ($modecompta == 'CREANCES-DETTES') {
487 print '<span class="amount">'.price($objp->amount_ht)."</span>";
488 }
489 print "</td>\n";
490 print '<td class="right"><span class="amount">'.price($objp->amount_ttc)."</span></td>\n";
491
492 $total_ht += (isset($objp->amount_ht) ? $objp->amount_ht : 0);
493 $total_ttc += $objp->amount_ttc;
494 print "</tr>\n";
495 $i++;
496 }
497 $db->free($result);
498 } else {
499 dol_print_error($db);
500 }
501
502 // We add the old customer payments, not linked by payment_invoice
503 if ($modecompta == 'RECETTES-DEPENSES') {
504 $sql = "SELECT 'Autres' as name, '0' as idp, sum(p.amount) as amount_ttc";
505 $sql .= " FROM ".MAIN_DB_PREFIX."bank as b";
506 $sql .= ", ".MAIN_DB_PREFIX."bank_account as ba";
507 $sql .= ", ".MAIN_DB_PREFIX."paiement as p";
508 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."paiement_facture as pf ON p.rowid = pf.fk_paiement";
509 $sql .= " WHERE pf.rowid IS NULL";
510 $sql .= " AND p.fk_bank = b.rowid";
511 $sql .= " AND b.fk_account = ba.rowid";
512 $sql .= " AND ba.entity IN (".getEntity('bank_account').")";
513 if (!empty($date_start) && !empty($date_end)) {
514 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
515 }
516 $sql .= " GROUP BY name, idp";
517 $sql .= " ORDER BY name";
518
519 dol_syslog("get old customer payments not linked to invoices", LOG_DEBUG);
520 $result = $db->query($sql);
521 if ($result) {
522 $num = $db->num_rows($result);
523 $i = 0;
524 if ($num) {
525 while ($i < $num) {
526 $objp = $db->fetch_object($result);
527
528
529 print '<tr class="oddeven">';
530 print '<td>&nbsp;</td>';
531 print "<td>".$langs->trans("Bills")." ".$langs->trans("Other")." (".$langs->trans("PaymentsNotLinkedToInvoice").")\n";
532
533 print '<td class="right">';
534 if ($modecompta == 'CREANCES-DETTES') {
535 print '<span class="amount">'.price($objp->amount_ht)."</span></td>\n";
536 }
537 print '</td>';
538 print '<td class="right"><span class="amount">'.price($objp->amount_ttc)."</span></td>\n";
539
540 $total_ht += (isset($objp->amount_ht) ? $objp->amount_ht : 0);
541 $total_ttc += $objp->amount_ttc;
542
543 print "</tr>\n";
544 $i++;
545 }
546 }
547 $db->free($result);
548 } else {
549 dol_print_error($db);
550 }
551 }
552
553 if ($total_ttc == 0) {
554 print '<tr class="oddeven">';
555 print '<td>&nbsp;</td>';
556 print '<td colspan="3"><span class="opacitymedium">'.$langs->trans("None").'</span></td>';
557 print '</tr>';
558 }
559
560 $total_ht_income += $total_ht;
561 $total_ttc_income += $total_ttc;
562
563 print '<tr class="liste_total">';
564 print '<td></td>';
565 print '<td></td>';
566 print '<td class="right">';
567 if ($modecompta == 'CREANCES-DETTES') {
568 print price($total_ht);
569 }
570 print '</td>';
571 print '<td class="right">'.price($total_ttc).'</td>';
572 print '</tr>';
573
574 /*
575 * Donations
576 */
577
578 if (isModEnabled('don')) {
579 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("Donations").'</td></tr>';
580
581 if ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES') {
582 if ($modecompta == 'CREANCES-DETTES') {
583 $sql = "SELECT p.societe as name, p.firstname, p.lastname, date_format(p.datedon,'%Y-%m') as dm, sum(p.amount) as amount";
584 $sql .= " FROM ".MAIN_DB_PREFIX."don as p";
585 $sql .= " WHERE p.entity IN (".getEntity('donation').")";
586 $sql .= " AND fk_statut in (1,2)";
587 } else {
588 $sql = "SELECT p.societe as nom, p.firstname, p.lastname, date_format(p.datedon,'%Y-%m') as dm, sum(pe.amount) as amount";
589 $sql .= " FROM ".MAIN_DB_PREFIX."don as p";
590 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."payment_donation as pe ON pe.fk_donation = p.rowid";
591 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as c ON pe.fk_typepayment = c.id";
592 $sql .= " WHERE p.entity IN (".getEntity('donation').")";
593 $sql .= " AND fk_statut >= 2";
594 }
595 if (!empty($date_start) && !empty($date_end)) {
596 $sql .= " AND p.datedon >= '".$db->idate($date_start)."' AND p.datedon <= '".$db->idate($date_end)."'";
597 }
598 }
599 $sql .= " GROUP BY p.societe, p.firstname, p.lastname, dm";
600 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
601 if ($sqlNewSortField == 's.nom, s.rowid') {
602 $sqlNewSortField = 'p.societe, p.firstname, p.lastname, dm';
603 }
604 if ($sqlNewSortField == 'amount_ht') {
605 $sqlNewSortField = 'amount';
606 }
607 if ($sqlNewSortField == 'amount_ttc') {
608 $sqlNewSortField = 'amount';
609 }
610 $sql .= $db->order($sqlNewSortField, $sortorder);
611
612 dol_syslog("get dunning");
613 $result = $db->query($sql);
614 $subtotal_ht = 0;
615 $subtotal_ttc = 0;
616 if ($result) {
617 $num = $db->num_rows($result);
618 $i = 0;
619 if ($num) {
620 while ($i < $num) {
621 $obj = $db->fetch_object($result);
622
623 $total_ht += $obj->amount;
624 $total_ttc += $obj->amount;
625 $subtotal_ht += $obj->amount;
626 $subtotal_ttc += $obj->amount;
627
628 print '<tr class="oddeven">';
629 print '<td>&nbsp;</td>';
630
631 print "<td>".$langs->trans("Donation")." <a href=\"".DOL_URL_ROOT."/don/list.php?search_company=".$obj->name."&search_name=".$obj->firstname." ".$obj->lastname."\">".$obj->name." ".$obj->firstname." ".$obj->lastname."</a></td>\n";
632
633 print '<td class="right">';
634 if ($modecompta == 'CREANCES-DETTES') {
635 print '<span class="amount">'.price($obj->amount).'</span>';
636 }
637 print '</td>';
638 print '<td class="right"><span class="amount">'.price($obj->amount).'</span></td>';
639 print '</tr>';
640 $i++;
641 }
642 } else {
643 print '<tr class="oddeven"><td>&nbsp;</td>';
644 print '<td colspan="3"><span class="opacitymedium">'.$langs->trans("None").'</span></td>';
645 print '</tr>';
646 }
647 } else {
648 dol_print_error($db);
649 }
650
651 $total_ht_income += $subtotal_ht;
652 $total_ttc_income += $subtotal_ttc;
653
654 print '<tr class="liste_total">';
655 print '<td></td>';
656 print '<td></td>';
657 print '<td class="right">';
658 if ($modecompta == 'CREANCES-DETTES') {
659 print price($subtotal_ht);
660 }
661 print '</td>';
662 print '<td class="right">'.price($subtotal_ttc).'</td>';
663 print '</tr>';
664 }
665
666 /*
667 * Suppliers invoices
668 */
669 if ($modecompta == 'CREANCES-DETTES') {
670 $sql = "SELECT s.nom as name, s.rowid as socid, sum(f.total_ht) as amount_ht, sum(f.total_ttc) as amount_ttc";
671 $sql .= " FROM ".MAIN_DB_PREFIX."societe as s";
672 $sql .= ", ".MAIN_DB_PREFIX."facture_fourn as f";
673 $sql .= " WHERE f.fk_soc = s.rowid";
674 $sql .= " AND f.fk_statut IN (1,2)";
675 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
676 $sql .= " AND f.type IN (0,1,2)";
677 } else {
678 $sql .= " AND f.type IN (0,1,2,3)";
679 }
680 // Add SQL restrictions from hooks (context turnoverreport), e.g. a deposit pivot date restricting deposits by their date
681 $hookmanager->initHooks(array('turnoverreport'));
682 $parameters = array('invoicealias' => 'f', 'issupplier' => 1, 'datefield' => 'datef');
683 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
684 $sql .= $hookmanager->resPrint;
685 if (!empty($date_start) && !empty($date_end)) {
686 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
687 }
688 } elseif ($modecompta == 'RECETTES-DEPENSES') {
689 $sql = "SELECT s.nom as name, s.rowid as socid, sum(pf.amount) as amount_ttc";
690 $sql .= " FROM ".MAIN_DB_PREFIX."paiementfourn as p";
691 $sql .= ", ".MAIN_DB_PREFIX."paiementfourn_facturefourn as pf";
692 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."facture_fourn as f";
693 $sql .= " ON pf.fk_facturefourn = f.rowid";
694 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s";
695 $sql .= " ON f.fk_soc = s.rowid";
696 $sql .= " WHERE p.rowid = pf.fk_paiementfourn ";
697 if (!empty($date_start) && !empty($date_end)) {
698 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
699 }
700 }
701
702 $sql .= " AND f.entity = ".((int) $conf->entity);
703 if ($socid) {
704 $sql .= " AND f.fk_soc = ".((int) $socid);
705 }
706 $sql .= " GROUP BY name, socid";
707 $sql .= $db->order($sortfield, $sortorder);
708
709 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("SuppliersInvoices").'</td></tr>';
710
711 $subtotal_ht = 0;
712 $subtotal_ttc = 0;
713 dol_syslog("get suppliers invoices", LOG_DEBUG);
714 $result = $db->query($sql);
715 if ($result) {
716 $num = $db->num_rows($result);
717 $i = 0;
718 if ($num > 0) {
719 while ($i < $num) {
720 $objp = $db->fetch_object($result);
721
722 print '<tr class="oddeven">';
723 print '<td>&nbsp;</td>';
724 print "<td>".$langs->trans("Bills").' <a href="'.DOL_URL_ROOT."/fourn/facture/list.php?socid=".$objp->socid.'">'.$objp->name.'</a></td>'."\n";
725
726 print '<td class="right">';
727 if ($modecompta == 'CREANCES-DETTES') {
728 print '<span class="amount">'.price(-$objp->amount_ht)."</span>";
729 }
730 print "</td>\n";
731 print '<td class="right"><span class="amount">'.price(-$objp->amount_ttc)."</span></td>\n";
732
733 $total_ht -= (isset($objp->amount_ht) ? $objp->amount_ht : 0);
734 $total_ttc -= $objp->amount_ttc;
735 $subtotal_ht += (isset($objp->amount_ht) ? $objp->amount_ht : 0);
736 $subtotal_ttc += $objp->amount_ttc;
737
738 print "</tr>\n";
739 $i++;
740 }
741 } else {
742 print '<tr class="oddeven">';
743 print '<td>&nbsp;</td>';
744 print '<td colspan="3"><span class="opacitymedium">'.$langs->trans("None").'</span></td>';
745 print '</tr>';
746 }
747
748 $db->free($result);
749 } else {
750 dol_print_error($db);
751 }
752
753 $total_ht_outcome += $subtotal_ht;
754 $total_ttc_outcome += $subtotal_ttc;
755
756 print '<tr class="liste_total">';
757 print '<td></td>';
758 print '<td></td>';
759 print '<td class="right">';
760 if ($modecompta == 'CREANCES-DETTES') {
761 print price(-$subtotal_ht);
762 }
763 print '</td>';
764 print '<td class="right">'.price(-$subtotal_ttc).'</td>';
765 print '</tr>';
766
767
768 /*
769 * Social / Fiscal contributions who are not deductible
770 */
771
772 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("SocialContributionsNondeductibles").'</td></tr>';
773
774 if ($modecompta == 'CREANCES-DETTES') {
775 $sql = "SELECT c.id, c.libelle as label, c.accountancy_code, sum(cs.amount) as amount";
776 $sql .= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
777 $sql .= ", ".MAIN_DB_PREFIX."chargesociales as cs";
778 $sql .= " WHERE cs.fk_type = c.id";
779 $sql .= " AND c.deductible = 0";
780 if (!empty($date_start) && !empty($date_end)) {
781 $sql .= " AND cs.date_ech >= '".$db->idate($date_start)."' AND cs.date_ech <= '".$db->idate($date_end)."'";
782 }
783 } elseif ($modecompta == 'RECETTES-DEPENSES') {
784 $sql = "SELECT c.id, c.libelle as label, c.accountancy_code, sum(p.amount) as amount";
785 $sql .= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
786 $sql .= ", ".MAIN_DB_PREFIX."chargesociales as cs";
787 $sql .= ", ".MAIN_DB_PREFIX."paiementcharge as p";
788 $sql .= " WHERE p.fk_charge = cs.rowid";
789 $sql .= " AND cs.fk_type = c.id";
790 $sql .= " AND c.deductible = 0";
791 if (!empty($date_start) && !empty($date_end)) {
792 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
793 }
794 }
795 $sql .= " AND cs.entity = ".((int) $conf->entity);
796 $sql .= " GROUP BY c.libelle, c.id, c.accountancy_code";
797 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
798 if ($sqlNewSortField == 's.nom, s.rowid') {
799 $sqlNewSortField = 'c.libelle, c.id';
800 }
801 if ($sqlNewSortField == 'amount_ht') {
802 $sqlNewSortField = 'amount';
803 }
804 if ($sqlNewSortField == 'amount_ttc') {
805 $sqlNewSortField = 'amount';
806 }
807
808 $sql .= $db->order($sqlNewSortField, $sortorder);
809
810 dol_syslog("get social contributions deductible=0", LOG_DEBUG);
811 $result = $db->query($sql);
812 $subtotal_ht = 0;
813 $subtotal_ttc = 0;
814 if ($result) {
815 $num = $db->num_rows($result);
816 $i = 0;
817 if ($num) {
818 while ($i < $num) {
819 $obj = $db->fetch_object($result);
820
821 $total_ht -= $obj->amount;
822 $total_ttc -= $obj->amount;
823 $subtotal_ht += $obj->amount;
824 $subtotal_ttc += $obj->amount;
825
826 $titletoshow = '';
827 if ($obj->accountancy_code) {
828 $titletoshow = $langs->trans("AccountingCode").': '.$obj->accountancy_code;
829 $tmpaccountingaccount = new AccountingAccount($db);
830 $tmpaccountingaccount->fetch(0, $obj->accountancy_code, 1);
831 $titletoshow .= ' - '.$langs->trans("AccountingCategory").': '.$tmpaccountingaccount->pcg_type;
832 }
833
834 print '<tr class="oddeven">';
835 print '<td>&nbsp;</td>';
836 print '<td'.($obj->accountancy_code ? ' title="'.dol_escape_htmltag($titletoshow).'"' : '').'>'.dol_escape_htmltag($obj->label).'</td>';
837 print '<td class="right">';
838 if ($modecompta == 'CREANCES-DETTES') {
839 print '<span class="amount">'.price(-$obj->amount).'</span>';
840 }
841 print '</td>';
842 print '<td class="right"><span class="amount">'.price(-$obj->amount).'</span></td>';
843 print '</tr>';
844 $i++;
845 }
846 } else {
847 print '<tr class="oddeven">';
848 print '<td>&nbsp;</td>';
849 print '<td colspan="3"><span class="opacitymedium">'.$langs->trans("None").'</span></td>';
850 print '</tr>';
851 }
852 } else {
853 dol_print_error($db);
854 }
855
856 $total_ht_outcome += $subtotal_ht;
857 $total_ttc_outcome += $subtotal_ttc;
858
859 print '<tr class="liste_total">';
860 print '<td></td>';
861 print '<td></td>';
862 print '<td class="right">';
863 if ($modecompta == 'CREANCES-DETTES') {
864 print price(-$subtotal_ht);
865 }
866 print '</td>';
867 print '<td class="right">'.price(-$subtotal_ttc).'</td>';
868 print '</tr>';
869
870
871 /*
872 * Social / Fiscal contributions who are deductible
873 */
874
875 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("SocialContributionsDeductibles").'</td></tr>';
876
877 if ($modecompta == 'CREANCES-DETTES') {
878 $sql = "SELECT c.id, c.libelle as label, c.accountancy_code, sum(cs.amount) as amount";
879 $sql .= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
880 $sql .= ", ".MAIN_DB_PREFIX."chargesociales as cs";
881 $sql .= " WHERE cs.fk_type = c.id";
882 $sql .= " AND c.deductible = 1";
883 if (!empty($date_start) && !empty($date_end)) {
884 $sql .= " AND cs.date_ech >= '".$db->idate($date_start)."' AND cs.date_ech <= '".$db->idate($date_end)."'";
885 }
886 $sql .= " AND cs.entity = ".((int) $conf->entity);
887 } elseif ($modecompta == 'RECETTES-DEPENSES') {
888 $sql = "SELECT c.id, c.libelle as label, c.accountancy_code, sum(p.amount) as amount";
889 $sql .= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
890 $sql .= ", ".MAIN_DB_PREFIX."chargesociales as cs";
891 $sql .= ", ".MAIN_DB_PREFIX."paiementcharge as p";
892 $sql .= " WHERE p.fk_charge = cs.rowid";
893 $sql .= " AND cs.fk_type = c.id";
894 $sql .= " AND c.deductible = 1";
895 if (!empty($date_start) && !empty($date_end)) {
896 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
897 }
898 $sql .= " AND cs.entity = ".((int) $conf->entity);
899 }
900 $sql .= " GROUP BY c.libelle, c.id, c.accountancy_code";
901 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
902 if ($sqlNewSortField == 's.nom, s.rowid') {
903 $sqlNewSortField = 'c.libelle, c.id';
904 }
905 if ($sqlNewSortField == 'amount_ht') {
906 $sqlNewSortField = 'amount';
907 }
908 if ($sqlNewSortField == 'amount_ttc') {
909 $sqlNewSortField = 'amount';
910 }
911 $sql .= $db->order($sqlNewSortField, $sortorder);
912
913 dol_syslog("get social contributions deductible=1", LOG_DEBUG);
914 $result = $db->query($sql);
915 $subtotal_ht = 0;
916 $subtotal_ttc = 0;
917 if ($result) {
918 $num = $db->num_rows($result);
919 $i = 0;
920 if ($num) {
921 while ($i < $num) {
922 $obj = $db->fetch_object($result);
923
924 $total_ht -= $obj->amount;
925 $total_ttc -= $obj->amount;
926 $subtotal_ht += $obj->amount;
927 $subtotal_ttc += $obj->amount;
928
929 $titletoshow = '';
930 if ($obj->accountancy_code) {
931 $titletoshow = $langs->trans("AccountingCode").': '.$obj->accountancy_code;
932 $tmpaccountingaccount = new AccountingAccount($db);
933 $tmpaccountingaccount->fetch(0, $obj->accountancy_code, 1);
934 $titletoshow .= ' - '.$langs->trans("AccountingCategory").': '.$tmpaccountingaccount->pcg_type;
935 }
936
937 print '<tr class="oddeven">';
938 print '<td>&nbsp;</td>';
939 print '<td'.($obj->accountancy_code ? ' title="'.dol_escape_htmltag($titletoshow).'"' : '').'>'.dol_escape_htmltag($obj->label).'</td>';
940 print '<td class="right">';
941 if ($modecompta == 'CREANCES-DETTES') {
942 print '<span class="amount">'.price(-$obj->amount).'</span>';
943 }
944 print '</td>';
945 print '<td class="right"><span class="amount">'.price(-$obj->amount).'</span></td>';
946 print '</tr>';
947 $i++;
948 }
949 } else {
950 print '<tr class="oddeven">';
951 print '<td>&nbsp;</td>';
952 print '<td colspan="3"><span class="opacitymedium">'.$langs->trans("None").'</span></td>';
953 print '</tr>';
954 }
955 } else {
956 dol_print_error($db);
957 }
958
959 $total_ht_outcome += $subtotal_ht;
960 $total_ttc_outcome += $subtotal_ttc;
961
962 print '<tr class="liste_total">';
963 print '<td></td>';
964 print '<td></td>';
965 print '<td class="right">';
966 if ($modecompta == 'CREANCES-DETTES') {
967 print price(-$subtotal_ht);
968 }
969 print '</td>';
970 print '<td class="right">'.price(-$subtotal_ttc).'</td>';
971 print '</tr>';
972
973
974 /*
975 * Salaries
976 */
977
978 if (isModEnabled('salaries')) {
979 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("Salaries").'</td></tr>';
980
981 if ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES') {
982 if ($modecompta == 'CREANCES-DETTES') {
983 $column = 's.dateep'; // We use the date of end of period of salary
984
985 $sql = "SELECT u.rowid, u.firstname, u.lastname, s.fk_user as fk_user, s.label as label, date_format($column,'%Y-%m') as dm, sum(s.amount) as amount";
986 $sql .= " FROM ".MAIN_DB_PREFIX."salary as s";
987 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."user as u ON u.rowid = s.fk_user";
988 $sql .= " WHERE s.entity IN (".getEntity('salary').")";
989 if (!empty($date_start) && !empty($date_end)) {
990 $sql .= " AND $column >= '".$db->idate($date_start)."' AND $column <= '".$db->idate($date_end)."'";
991 }
992 $sql .= " GROUP BY u.rowid, u.firstname, u.lastname, s.fk_user, s.label, dm";
993 } else {
994 $column = 'p.datep';
995
996 $sql = "SELECT u.rowid, u.firstname, u.lastname, s.fk_user as fk_user, p.label as label, date_format($column,'%Y-%m') as dm, sum(p.amount) as amount";
997 $sql .= " FROM ".MAIN_DB_PREFIX."payment_salary as p";
998 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."salary as s ON s.rowid = p.fk_salary";
999 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."user as u ON u.rowid = s.fk_user";
1000 $sql .= " WHERE p.entity IN (".getEntity('payment_salary').")";
1001 if (!empty($date_start) && !empty($date_end)) {
1002 $sql .= " AND $column >= '".$db->idate($date_start)."' AND $column <= '".$db->idate($date_end)."'";
1003 }
1004 $sql .= " GROUP BY u.rowid, u.firstname, u.lastname, s.fk_user, p.label, dm";
1005 }
1006
1007 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
1008 if ($sqlNewSortField == 's.nom, s.rowid') {
1009 $sqlNewSortField = 'u.firstname, u.lastname';
1010 }
1011 if ($sqlNewSortField == 'amount_ht') {
1012 $sqlNewSortField = 'amount';
1013 }
1014 if ($sqlNewSortField == 'amount_ttc') {
1015 $sqlNewSortField = 'amount';
1016 }
1017 $sql .= $db->order($sqlNewSortField, $sortorder);
1018 }
1019
1020 dol_syslog("get salaries");
1021 $result = $db->query($sql);
1022 $subtotal_ht = 0;
1023 $subtotal_ttc = 0;
1024 if ($result) {
1025 $num = $db->num_rows($result);
1026 $i = 0;
1027 if ($num) {
1028 while ($i < $num) {
1029 $obj = $db->fetch_object($result);
1030
1031 $total_ht -= $obj->amount;
1032 $total_ttc -= $obj->amount;
1033 $subtotal_ht += $obj->amount;
1034 $subtotal_ttc += $obj->amount;
1035
1036 print '<tr class="oddeven"><td>&nbsp;</td>';
1037
1038 $userstatic = new User($db);
1039 $userstatic->fetch($obj->fk_user);
1040
1041 print "<td>".$langs->trans("Salary")." <a href=\"".DOL_URL_ROOT."/salaries/list.php?search_user=".urlencode($userstatic->getFullName($langs))."\">".$obj->firstname." ".$obj->lastname."</a></td>\n";
1042 print '<td class="right">';
1043 if ($modecompta == 'CREANCES-DETTES') {
1044 print '<span class="amount">'.price(-$obj->amount).'</span>';
1045 }
1046 print '</td>';
1047 print '<td class="right"><span class="amount">'.price(-$obj->amount).'</span></td>';
1048 print '</tr>';
1049 $i++;
1050 }
1051 } else {
1052 print '<tr class="oddeven">';
1053 print '<td>&nbsp;</td>';
1054 print '<td colspan="3"><span class="opacitymedium">'.$langs->trans("None").'</span></td>';
1055 print '</tr>';
1056 }
1057 } else {
1058 dol_print_error($db);
1059 }
1060
1061 $total_ht_outcome += $subtotal_ht;
1062 $total_ttc_outcome += $subtotal_ttc;
1063
1064 print '<tr class="liste_total">';
1065 print '<td></td>';
1066 print '<td></td>';
1067 print '<td class="right">';
1068 if ($modecompta == 'CREANCES-DETTES') {
1069 print price(-$subtotal_ht);
1070 }
1071 print '</td>';
1072 print '<td class="right">'.price(-$subtotal_ttc).'</td>';
1073 print '</tr>';
1074 }
1075
1076
1077 /*
1078 * Expense report
1079 */
1080
1081 if (isModEnabled('expensereport')) {
1082 if ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES') {
1083 $langs->load('trips');
1084 if ($modecompta == 'CREANCES-DETTES') {
1085 $sql = "SELECT p.rowid, p.ref, u.rowid as userid, u.firstname, u.lastname, date_format(date_valid,'%Y-%m') as dm, p.total_ht as amount_ht, p.total_ttc as amount_ttc";
1086 $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as p";
1087 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."user as u ON u.rowid=p.fk_user_author";
1088 $sql .= " WHERE p.entity IN (".getEntity('expensereport').")";
1089 $sql .= " AND p.fk_statut>=5";
1090
1091 $column = 'p.date_valid';
1092 } else {
1093 $sql = "SELECT p.rowid, p.ref, u.rowid as userid, u.firstname, u.lastname, date_format(pe.datep,'%Y-%m') as dm, sum(pe.amount) as amount_ht, sum(pe.amount) as amount_ttc";
1094 $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as p";
1095 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."user as u ON u.rowid=p.fk_user_author";
1096 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."payment_expensereport as pe ON pe.fk_expensereport = p.rowid";
1097 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as c ON pe.fk_typepayment = c.id";
1098 $sql .= " WHERE p.entity IN (".getEntity('expensereport').")";
1099 $sql .= " AND p.fk_statut>=5";
1100
1101 $column = 'pe.datep';
1102 }
1103
1104 if (!empty($date_start) && !empty($date_end)) {
1105 $sql .= " AND $column >= '".$db->idate($date_start)."' AND $column <= '".$db->idate($date_end)."'";
1106 }
1107
1108 if ($modecompta == 'CREANCES-DETTES') {
1109 //No need of GROUP BY
1110 } else {
1111 $sql .= " GROUP BY u.rowid, p.rowid, p.ref, u.firstname, u.lastname, dm";
1112 }
1113 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
1114 if ($sqlNewSortField == 's.nom, s.rowid') {
1115 $sqlNewSortField = 'p.ref';
1116 }
1117 $sql .= $db->order($sqlNewSortField, $sortorder);
1118 }
1119
1120 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("ExpenseReport").'</td></tr>';
1121
1122 dol_syslog("get expense report outcome");
1123 $result = $db->query($sql);
1124 $subtotal_ht = 0;
1125 $subtotal_ttc = 0;
1126 if ($result) {
1127 $num = $db->num_rows($result);
1128 if ($num) {
1129 while ($obj = $db->fetch_object($result)) {
1130 $total_ht -= $obj->amount_ht;
1131 $total_ttc -= $obj->amount_ttc;
1132 $subtotal_ht += $obj->amount_ht;
1133 $subtotal_ttc += $obj->amount_ttc;
1134
1135 print '<tr class="oddeven">';
1136 print '<td>&nbsp;</td>';
1137 print "<td>".$langs->trans("ExpenseReport")." <a href=\"".DOL_URL_ROOT."/expensereport/list.php?search_user=".$obj->userid."\">".$obj->firstname." ".$obj->lastname."</a></td>\n";
1138 print '<td class="right">';
1139 if ($modecompta == 'CREANCES-DETTES') {
1140 print '<span class="amount">'.price(-$obj->amount_ht).'</span>';
1141 }
1142 print '</td>';
1143 print '<td class="right"><span class="amount">'.price(-$obj->amount_ttc).'</span></td>';
1144 print '</tr>';
1145 }
1146 } else {
1147 print '<tr class="oddeven">';
1148 print '<td>&nbsp;</td>';
1149 print '<td colspan="3"><span class="opacitymedium">'.$langs->trans("None").'</span></td>';
1150 print '</tr>';
1151 }
1152 } else {
1153 dol_print_error($db);
1154 }
1155
1156 $total_ht_outcome += $subtotal_ht;
1157 $total_ttc_outcome += $subtotal_ttc;
1158
1159 print '<tr class="liste_total">';
1160 print '<td></td>';
1161 print '<td></td>';
1162 print '<td class="right">';
1163 if ($modecompta == 'CREANCES-DETTES') {
1164 print price(-$subtotal_ht);
1165 }
1166 print '</td>';
1167 print '<td class="right">'.price(-$subtotal_ttc).'</td>';
1168 print '</tr>';
1169 }
1170
1171
1172 /*
1173 * Various Payments
1174 */
1175 //$conf->global->ACCOUNTING_REPORTS_INCLUDE_VARPAY = 1;
1176
1177 if (getDolGlobalString('ACCOUNTING_REPORTS_INCLUDE_VARPAY') && isModEnabled("bank") && ($modecompta == 'CREANCES-DETTES' || $modecompta == "RECETTES-DEPENSES")) {
1178 $subtotal_ht = 0;
1179 $subtotal_ttc = 0;
1180
1181 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("VariousPayment").'</td></tr>';
1182
1183 // Debit
1184 $sql = "SELECT SUM(p.amount) AS amount FROM ".MAIN_DB_PREFIX."payment_various as p";
1185 $sql .= ' WHERE 1 = 1';
1186 if (!empty($date_start) && !empty($date_end)) {
1187 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
1188 }
1189 $sql .= ' GROUP BY p.sens';
1190 $sql .= ' ORDER BY p.sens';
1191
1192 dol_syslog('get various payments', LOG_DEBUG);
1193 $result = $db->query($sql);
1194 if ($result) {
1195 // Debit (payment of suppliers for example)
1196 $obj = $db->fetch_object($result);
1197 if (isset($obj->amount)) {
1198 $subtotal_ht += -$obj->amount;
1199 $subtotal_ttc += -$obj->amount;
1200
1201 $total_ht_outcome += $obj->amount;
1202 $total_ttc_outcome += $obj->amount;
1203 }
1204 $debit_amount = isset($obj->amount) ? $obj->amount : 0;
1205 print '<tr class="oddeven">';
1206 print '<td>&nbsp;</td>';
1207 print "<td>".$langs->trans("AccountingDebit")."</td>\n";
1208 print '<td class="right">';
1209 if ($modecompta == 'CREANCES-DETTES') {
1210 print '<span class="amount">'.price(-$debit_amount).'</span>';
1211 }
1212 print '</td>';
1213 print '<td class="right"><span class="amount">'.price(-$debit_amount)."</span></td>\n";
1214 print "</tr>\n";
1215
1216 // Credit (payment received from customer for example)
1217 $obj = $db->fetch_object($result);
1218 if (isset($obj->amount)) {
1219 $subtotal_ht += $obj->amount;
1220 $subtotal_ttc += $obj->amount;
1221
1222 $total_ht_income += $obj->amount;
1223 $total_ttc_income += $obj->amount;
1224 }
1225 $credit_amount = isset($obj->amount) ? $obj->amount : 0;
1226 print '<tr class="oddeven"><td>&nbsp;</td>';
1227 print "<td>".$langs->trans("AccountingCredit")."</td>\n";
1228 print '<td class="right">';
1229 if ($modecompta == 'CREANCES-DETTES') {
1230 print '<span class="amount">'.price($credit_amount).'</span>';
1231 }
1232 print '</td>';
1233 print '<td class="right"><span class="amount">'.price($credit_amount)."</span></td>\n";
1234 print "</tr>\n";
1235
1236 // Total
1237 $total_ht += $subtotal_ht;
1238 $total_ttc += $subtotal_ttc;
1239 print '<tr class="liste_total">';
1240 print '<td></td>';
1241 print '<td></td>';
1242 print '<td class="right">';
1243 if ($modecompta == 'CREANCES-DETTES') {
1244 print price($subtotal_ht);
1245 }
1246 print '</td>';
1247 print '<td class="right">'.price($subtotal_ttc).'</td>';
1248 print '</tr>';
1249 } else {
1250 dol_print_error($db);
1251 }
1252 }
1253
1254 /*
1255 * Payment Loan
1256 */
1257
1258 if (getDolGlobalString('ACCOUNTING_REPORTS_INCLUDE_LOAN') && isModEnabled('don') && ($modecompta == 'CREANCES-DETTES' || $modecompta == "RECETTES-DEPENSES")) {
1259 $subtotal_ht = 0;
1260 $subtotal_ttc = 0;
1261
1262 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("PaymentLoan").'</td></tr>';
1263
1264 $sql = 'SELECT l.rowid as id, l.label AS label, SUM(p.amount_capital + p.amount_insurance + p.amount_interest) as amount FROM '.MAIN_DB_PREFIX.'payment_loan as p';
1265 $sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.'loan AS l ON l.rowid = p.fk_loan';
1266 $sql .= ' WHERE 1 = 1';
1267 if (!empty($date_start) && !empty($date_end)) {
1268 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
1269 }
1270 $sql .= ' GROUP BY p.fk_loan';
1271 $sql .= ' ORDER BY p.fk_loan';
1272
1273 dol_syslog('get loan payments', LOG_DEBUG);
1274 $result = $db->query($sql);
1275 if ($result) {
1276 require_once DOL_DOCUMENT_ROOT.'/loan/class/loan.class.php';
1277 $loan_static = new Loan($db);
1278 while ($obj = $db->fetch_object($result)) {
1279 $loan_static->id = $obj->id;
1280 $loan_static->ref = $obj->id;
1281 $loan_static->label = $obj->label;
1282 print '<tr class="oddeven"><td>&nbsp;</td>';
1283 print "<td>".$loan_static->getNomUrl(1).' - '.$obj->label."</td>\n";
1284 if ($modecompta == 'CREANCES-DETTES') {
1285 print '<td class="right"><span class="amount">'.price(-$obj->amount).'</span></td>';
1286 }
1287 print '<td class="right"><span class="amount">'.price(-$obj->amount)."</span></td>\n";
1288 print "</tr>\n";
1289 $subtotal_ht -= $obj->amount;
1290 $subtotal_ttc -= $obj->amount;
1291 }
1292 $total_ht += $subtotal_ht;
1293 $total_ttc += $subtotal_ttc;
1294
1295 $total_ht_income += $subtotal_ht;
1296 $total_ttc_income += $subtotal_ttc;
1297
1298 print '<tr class="liste_total">';
1299 print '<td></td>';
1300 print '<td></td>';
1301 print '<td class="right">';
1302 if ($modecompta == 'CREANCES-DETTES') {
1303 print price($subtotal_ht);
1304 }
1305 print '</td>';
1306 print '<td class="right">'.price($subtotal_ttc).'</td>';
1307 print '</tr>';
1308 } else {
1309 dol_print_error($db);
1310 }
1311 }
1312
1313 /*
1314 * VAT
1315 */
1316
1317 print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("VAT").'</td></tr>';
1318 $subtotal_ht = 0;
1319 $subtotal_ttc = 0;
1320
1321 if (isModEnabled('tax') && ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES')) {
1322 if ($modecompta == 'CREANCES-DETTES') {
1323 // VAT to pay
1324 $amount = 0;
1325 $sql = "SELECT date_format(f.datef,'%Y-%m') as dm, sum(f.total_tva) as amount";
1326 $sql .= " FROM ".MAIN_DB_PREFIX."facture as f";
1327 $sql .= " WHERE f.fk_statut IN (1,2)";
1328 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
1329 $sql .= " AND f.type IN (0,1,2,5)";
1330 } else {
1331 $sql .= " AND f.type IN (0,1,2,3,5)";
1332 }
1333 // Add SQL restrictions from hooks (context turnoverreport), e.g. a deposit pivot date restricting deposits by their date
1334 $hookmanager->initHooks(array('turnoverreport'));
1335 $parameters = array('invoicealias' => 'f', 'issupplier' => 0, 'datefield' => 'datef');
1336 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
1337 $sql .= $hookmanager->resPrint;
1338 if (!empty($date_start) && !empty($date_end)) {
1339 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
1340 }
1341 $sql .= " AND f.entity IN (".getEntity('invoice').")";
1342 $sql .= " GROUP BY dm";
1343 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
1344 if ($sqlNewSortField == 's.nom, s.rowid') {
1345 $sqlNewSortField = 'dm';
1346 }
1347 if ($sqlNewSortField == 'amount_ht') {
1348 $sqlNewSortField = 'amount';
1349 }
1350 if ($sqlNewSortField == 'amount_ttc') {
1351 $sqlNewSortField = 'amount';
1352 }
1353 $sql .= $db->order($sqlNewSortField, $sortorder);
1354
1355 dol_syslog("get vat to pay", LOG_DEBUG);
1356 $result = $db->query($sql);
1357 if ($result) {
1358 $num = $db->num_rows($result);
1359 $i = 0;
1360 if ($num) {
1361 while ($i < $num) {
1362 $obj = $db->fetch_object($result);
1363
1364 $amount -= $obj->amount;
1365 //$total_ht -= $obj->amount;
1366 $total_ttc -= $obj->amount;
1367 //$subtotal_ht -= $obj->amount;
1368 $subtotal_ttc -= $obj->amount;
1369 $i++;
1370 }
1371 }
1372 } else {
1373 dol_print_error($db);
1374 }
1375
1376 $total_ht_outcome -= 0;
1377 $total_ttc_outcome -= $amount;
1378
1379 print '<tr class="oddeven">';
1380 print '<td>&nbsp;</td>';
1381 print "<td>".$langs->trans("VATToPay")."</td>\n";
1382 print '<td class="right">&nbsp;</td>'."\n";
1383 print '<td class="right"><span class="amount">'.price($amount)."</span></td>\n";
1384 print "</tr>\n";
1385
1386 // VAT to retrieve
1387 $amount = 0;
1388 $sql = "SELECT date_format(f.datef,'%Y-%m') as dm, sum(f.total_tva) as amount";
1389 $sql .= " FROM ".MAIN_DB_PREFIX."facture_fourn as f";
1390 $sql .= " WHERE f.fk_statut IN (1,2)";
1391 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
1392 $sql .= " AND f.type IN (0,1,2)";
1393 } else {
1394 $sql .= " AND f.type IN (0,1,2,3)";
1395 }
1396 // Add SQL restrictions from hooks (context turnoverreport), e.g. a deposit pivot date restricting deposits by their date
1397 $hookmanager->initHooks(array('turnoverreport'));
1398 $parameters = array('invoicealias' => 'f', 'issupplier' => 1, 'datefield' => 'datef');
1399 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
1400 $sql .= $hookmanager->resPrint;
1401 if (!empty($date_start) && !empty($date_end)) {
1402 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
1403 }
1404 $sql .= " AND f.entity = ".((int) $conf->entity);
1405 $sql .= " GROUP BY dm";
1406 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
1407 if ($sqlNewSortField == 's.nom, s.rowid') {
1408 $sqlNewSortField = 'dm';
1409 }
1410 if ($sqlNewSortField == 'amount_ht') {
1411 $sqlNewSortField = 'amount';
1412 }
1413 if ($sqlNewSortField == 'amount_ttc') {
1414 $sqlNewSortField = 'amount';
1415 }
1416 $sql .= $db->order($sqlNewSortField, $sortorder);
1417
1418 dol_syslog("get vat received back", LOG_DEBUG);
1419 $result = $db->query($sql);
1420 if ($result) {
1421 $num = $db->num_rows($result);
1422 $i = 0;
1423 if ($num) {
1424 while ($i < $num) {
1425 $obj = $db->fetch_object($result);
1426
1427 $amount += $obj->amount;
1428 //$total_ht += $obj->amount;
1429 $total_ttc += $obj->amount;
1430 //$subtotal_ht += $obj->amount;
1431 $subtotal_ttc += $obj->amount;
1432
1433 $i++;
1434 }
1435 }
1436 } else {
1437 dol_print_error($db);
1438 }
1439
1440 $total_ht_income += 0;
1441 $total_ttc_income += $amount;
1442
1443 print '<tr class="oddeven">';
1444 print '<td>&nbsp;</td>';
1445 print '<td>'.$langs->trans("VATToCollect")."</td>\n";
1446 print '<td class="right">&nbsp;</td>'."\n";
1447 print '<td class="right"><span class="amount">'.price($amount)."</span></td>\n";
1448 print "</tr>\n";
1449 } else {
1450 // VAT really already paid
1451 $amount = 0;
1452 $sql = "SELECT date_format(t.datev,'%Y-%m') as dm, sum(t.amount) as amount";
1453 $sql .= " FROM ".MAIN_DB_PREFIX."tva as t";
1454 $sql .= " WHERE amount > 0";
1455 if (!empty($date_start) && !empty($date_end)) {
1456 $sql .= " AND t.datev >= '".$db->idate($date_start)."' AND t.datev <= '".$db->idate($date_end)."'";
1457 }
1458 $sql .= " AND t.entity = ".((int) $conf->entity);
1459 $sql .= " GROUP BY dm";
1460 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
1461 if ($sqlNewSortField == 's.nom, s.rowid') {
1462 $sqlNewSortField = 'dm';
1463 }
1464 if ($sqlNewSortField == 'amount_ht') {
1465 $sqlNewSortField = 'amount';
1466 }
1467 if ($sqlNewSortField == 'amount_ttc') {
1468 $sqlNewSortField = 'amount';
1469 }
1470 $sql .= $db->order($sqlNewSortField, $sortorder);
1471
1472 dol_syslog("get vat really paid", LOG_DEBUG);
1473 $result = $db->query($sql);
1474 if ($result) {
1475 $num = $db->num_rows($result);
1476 $i = 0;
1477 if ($num) {
1478 while ($i < $num) {
1479 $obj = $db->fetch_object($result);
1480
1481 $amount -= $obj->amount;
1482 $total_ht -= $obj->amount;
1483 $total_ttc -= $obj->amount;
1484 $subtotal_ht -= $obj->amount;
1485 $subtotal_ttc -= $obj->amount;
1486
1487 $i++;
1488 }
1489 }
1490 $db->free($result);
1491 } else {
1492 dol_print_error($db);
1493 }
1494
1495 $total_ht_outcome -= 0;
1496 $total_ttc_outcome -= $amount;
1497
1498 print '<tr class="oddeven">';
1499 print '<td>&nbsp;</td>';
1500 print "<td>".$langs->trans("VATPaid")."</td>\n";
1501 print '<td <class="right"></td>'."\n";
1502 print '<td class="right"><span class="amount">'.price($amount)."</span></td>\n";
1503 print "</tr>\n";
1504
1505 // VAT really received
1506 $amount = 0;
1507 $sql = "SELECT date_format(t.datev,'%Y-%m') as dm, sum(t.amount) as amount";
1508 $sql .= " FROM ".MAIN_DB_PREFIX."tva as t";
1509 $sql .= " WHERE amount < 0";
1510 if (!empty($date_start) && !empty($date_end)) {
1511 $sql .= " AND t.datev >= '".$db->idate($date_start)."' AND t.datev <= '".$db->idate($date_end)."'";
1512 }
1513 $sql .= " AND t.entity = ".((int) $conf->entity);
1514 $sql .= " GROUP BY dm";
1515 $sqlNewSortField = $sortfield; // @phan-suppress-current-line SqlInjection
1516 if ($sqlNewSortField == 's.nom, s.rowid') {
1517 $sqlNewSortField = 'dm';
1518 }
1519 if ($sqlNewSortField == 'amount_ht') {
1520 $sqlNewSortField = 'amount';
1521 }
1522 if ($sqlNewSortField == 'amount_ttc') {
1523 $sqlNewSortField = 'amount';
1524 }
1525 $sql .= $db->order($sqlNewSortField, $sortorder);
1526
1527 dol_syslog("get vat really received back", LOG_DEBUG);
1528 $result = $db->query($sql);
1529 if ($result) {
1530 $num = $db->num_rows($result);
1531 $i = 0;
1532 if ($num) {
1533 while ($i < $num) {
1534 $obj = $db->fetch_object($result);
1535
1536 $amount += -$obj->amount;
1537 $total_ht += -$obj->amount;
1538 $total_ttc += -$obj->amount;
1539 $subtotal_ht += -$obj->amount;
1540 $subtotal_ttc += -$obj->amount;
1541
1542 $i++;
1543 }
1544 }
1545 $db->free($result);
1546 } else {
1547 dol_print_error($db);
1548 }
1549
1550 $total_ht_income += 0;
1551 $total_ttc_income += $amount;
1552
1553 print '<tr class="oddeven">';
1554 print '<td>&nbsp;</td>';
1555 print "<td>".$langs->trans("VATCollected")."</td>\n";
1556 print '<td class="right"></td>'."\n";
1557 print '<td class="right"><span class="amount">'.price($amount)."</span></td>\n";
1558 print "</tr>\n";
1559 }
1560 }
1561
1562 if ($mysoc->tva_assuj != '0') { // Assujetti
1563 print '<tr class="liste_total">';
1564 print '<td></td>';
1565 print '<td></td>';
1566 print '<td class="right">&nbsp;</td>';
1567 print '<td class="right">'.price(price2num($subtotal_ttc, 'MT')).'</td>';
1568 print '</tr>';
1569 }
1570}
1571
1572$action = "balanceclient";
1573$object = array(&$total_ht, &$total_ttc);
1574$parameters = array();
1575$parameters["mode"] = $modecompta;
1576$parameters["date_start"] = $date_start;
1577$parameters["date_end"] = $date_end;
1578// Initialize a technical object to manage hooks of expenses. Note that conf->hooks_modules contains array array
1579$hookmanager->initHooks(array('externalbalance'));
1580$reshook = $hookmanager->executeHooks('addBalanceLine', $parameters, $object, $action); // Note that $action and $object may have been modified by some hooks
1581print $hookmanager->resPrint;
1582
1583
1584
1585// Total
1586print '<tr>';
1587print '<td colspan="'.($modecompta == 'BOOKKEEPING' ? 3 : 4).'">&nbsp;</td>';
1588print '</tr>';
1589
1590print '<tr class="liste_total"><td class="left" colspan="2">'.$langs->trans("Income").'</td>';
1591if ($modecompta == 'CREANCES-DETTES') {
1592 print '<td class="liste_total right nowraponall">'.price(price2num($total_ht_income, 'MT')).'</td>';
1593} elseif ($modecompta == 'RECETTES-DEPENSES') {
1594 print '<td></td>';
1595}
1596print '<td class="liste_total right nowraponall">'.price(price2num($total_ttc_income, 'MT')).'</td>';
1597print '</tr>';
1598print '<tr class="liste_total"><td class="left" colspan="2">'.$langs->trans("Outcome").'</td>';
1599if ($modecompta == 'CREANCES-DETTES') {
1600 print '<td class="liste_total right nowraponall">'.price(price2num(-$total_ht_outcome, 'MT')).'</td>';
1601} elseif ($modecompta == 'RECETTES-DEPENSES') {
1602 print '<td></td>';
1603}
1604print '<td class="liste_total right nowraponall">'.price(price2num(-$total_ttc_outcome, 'MT')).'</td>';
1605print '</tr>';
1606print '<tr class="liste_total"><td class="left" colspan="2">'.$langs->trans("Profit").'</td>';
1607if ($modecompta == 'CREANCES-DETTES') {
1608 print '<td class="liste_total right nowraponall">'.price(price2num($total_ht, 'MT')).'</td>';
1609} elseif ($modecompta == 'RECETTES-DEPENSES') {
1610 print '<td></td>';
1611}
1612print '<td class="liste_total right nowraponall">'.price(price2num($total_ttc, 'MT')).'</td>';
1613print '</tr>';
1614
1615print "</table>";
1616print '<br>';
1617
1618// End of page
1619llxFooter();
1620$db->close();
$object
if(! $sortfield) if(! $sortorder) $object
Definition account.php:100
llxFooter
llxFooter($comment='', $zone='private', $disabledoutputofmessages=0)
Empty footer.
Definition wrapper.php:91
llxHeader
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
AccountancyCategory
Class to manage categories of an accounting account.
Definition accountancycategory.class.php:34
AccountingAccount
Class to manage accounting accounts.
Definition accountingaccount.class.php:39
Form
Class to manage generation of HTML components Only common components must be here.
Definition html.form.class.php:60
Loan
Loan.
Definition loan.class.php:32
User
Class to manage Dolibarr users.
Definition user.class.php:51
$mysoc
global $mysoc
Definition purchasesjournal.php:31
dol_get_first_day
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition date.lib.php:616
dol_get_last_day
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition date.lib.php:635
$conf
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $conf
The main.inc.php has been included so the following variable are now defined:
Definition execute_tool.php:54
$date_start
$date_start
Variables from include:
Definition fiscalyear_card.php:73
dol_now
dol_now($mode='gmt')
Return date for now.
Definition functions.lib.php:3258
dol_mktime
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...
Definition functions.lib.php:3172
dol_getIdFromCode
dol_getIdFromCode($db, $key, $tablename, $fieldkey='code', $fieldid='id', $entityfilter=0, $filters='', $useCache=true)
Return an id or code from a code or id.
Definition functions.lib.php:8433
price2num
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
Definition functions.lib.php:4702
price
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.
Definition functions.lib.php:4570
getDolGlobalInt
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
Definition functions.lib.php:359
GETPOST
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0, $nodefault=0)
Return value of a param into GET or POST supervariable.
Definition functions.lib.php:1129
GETPOSTINT
GETPOSTINT($paramname, $method=0, $nodefault=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
Definition functions.lib.php:997
dol_print_date
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).
Definition functions.lib.php:2847
GETPOSTISSET
GETPOSTISSET($paramname)
Return true if we are in a context of submitting the parameter $paramname from a POST of a form.
Definition functions.lib.php:917
getDolGlobalString
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
Definition functions.lib.php:324
isModEnabled
isModEnabled($module)
Is Dolibarr module enabled.
Definition functions.lib.php:526
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2547
dol_getdate
dol_getdate($timestamp, $fast=false, $forcetimezone='')
Return an array with locale date info.
Definition functions.lib.php:3125
print_liste_field_titre
print_liste_field_titre($name, $file="", $field="", $begin="", $param="", $moreattrib="", $sortfield="", $sortorder="", $prefix="", $tooltip="", $forcenowrapcolumntitle=0)
Show title line of an array.
Definition html.lib.php:2785
img_previous
img_previous($titlealt='default', $moreatt='')
Show previous logo.
Definition html.lib.php:2313
img_next
img_next($titlealt='default', $moreatt='')
Show next logo.
Definition html.lib.php:2294
info_admin
info_admin($text, $infoonimgalt=0, $nodiv=0, $admin='1', $morecss='hideonsmartphone', $textfordropdown='', $picto='', $textonpictotooltip='', $cssfordropdown='info_admin')
Show information in HTML for admin users or standard users.
Definition html.lib.php:2540
dol_escape_htmltag
dol_escape_htmltag($stringtoescape, $keepb=0, $keepn=0, $noescapetags='', $escapeonlyhtmltags=0, $cleanalsojavascript=0)
Definition html.lib.php:181
report_header
report_header($reportname, $notused, $period, $periodlink, $description, $builddate, $exportlink='', $moreparam=array(), $calcmode='', $varlink='')
Show header of a report.
Definition report.lib.php:43
restrictedArea
restrictedArea(User $user, $features, $object=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $isdraft=0, $nodie=0, $mode='')
Check permissions of a user to show a page and an object.
Definition security.lib.php:183
Generated on Thu Oct 1 2026 01:00:19 for dolibarr by Doxygen 1.11.0