dolibarr 25.0.0-alpha
recap-compta.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2001-2006 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3 * Copyright (C) 2004-2017 Laurent Destailleur <eldy@users.sourceforge.net>
4 * Copyright (C) 2017 Pierre-Henry Favre <support@atm-consulting.fr>
5 * Copyright (C) 2024-2025 Frédéric France <frederic.france@free.fr>
6 * Copyright (C) 2024-2026 MDW <mdeweerd@users.noreply.github.com>
7 * Copyright (C) 2026 Nick Fragoulis
8 * Copyright (C) 2026 Christos Kanotidis <christoskanotidis@gmail.com>
9 *
10 * This program is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 3 of the License, or
13 * (at your option) any later version.
14 *
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
19 *
20 * You should have received a copy of the GNU General Public License
21 * along with this program. If not, see <https://www.gnu.org/licenses/>.
22 */
23
30// Load Dolibarr environment
31require '../main.inc.php';
40require_once DOL_DOCUMENT_ROOT.'/core/lib/company.lib.php';
41require_once DOL_DOCUMENT_ROOT.'/core/lib/report.lib.php';
42require_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php';
43require_once DOL_DOCUMENT_ROOT.'/compta/paiement/class/paiement.class.php';
44
45// Load translation files required by the page
46$langs->loadLangs(array("companies", "bills", "compta", "accountancy"));
47
48$action = GETPOST('action', 'aZ09');
49$dol_openinpopup = GETPOST('dol_openinpopup', 'aZ09');
50$search_date_start = GETPOSTDATE('search_date_start', 'getpost'); // Use tzserver because invoice date is a date without hour
51$search_date_end = GETPOSTDATE('search_date_end', 'getpostend');
52$search_status = GETPOST('search_status', 'aZ09');
53if (!in_array($search_status, array('unpaid', 'paid'), true)) {
54 $search_status = '';
55}
56$search_late = GETPOST('search_late', 'aZ09');
57if (!in_array($search_late, array('late', 'notlate'), true)) {
58 $search_late = '';
59}
60if (GETPOST('button_removefilter_x', 'alpha') || GETPOST('button_removefilter', 'alpha') || GETPOST('button_removefilter.x', 'alpha')) { // All tests are required to be compatible with all browsers
61 $search_date_start = '';
62 $search_date_end = '';
63 $search_status = '';
64 $search_late = '';
65}
66
67$id = GETPOST('id') ? GETPOSTINT('id') : GETPOSTINT('socid');
68
69// Security check
70if ($user->socid > 0) {
71 $id = $user->socid;
72}
73
74// Initialize a technical object to manage hooks of page. Note that conf->hooks_modules contains an array of hook context
75$hookmanager->initHooks(array('recapcomptacard', 'globalcard'));
76
77$result = restrictedArea($user, 'societe', $id, '&societe');
78
79$object = new Societe($db);
80if ($id > 0) {
81 $object->fetch($id);
82}
83
84
85// Load variable for pagination
86$limit = GETPOSTINT('limit') ? GETPOSTINT('limit') : $conf->liste_limit;
87$sortfield = GETPOST('sortfield', 'aZ09comma');
88$sortorder = GETPOST('sortorder', 'aZ09comma');
89$page = GETPOSTISSET('pageplusone') ? (GETPOSTINT('pageplusone') - 1) : GETPOSTINT("page");
90if (empty($page) || $page == -1) {
91 $page = 0;
92} // If $page is not defined, or '' or -1
93$offset = $limit * $page;
94$pageprev = $page - 1;
95$pagenext = $page + 1;
96if (!$sortfield) {
97 $sortfield = "f.datef,f.rowid"; // Set here default search field
98}
99if (!$sortorder) {
100 $sortorder = "DESC";
101}
102
103
104$arrayfields = array(
105 'f.datef' => array('label' => "Date", 'checked' => 1),
106 //...
107);
108
109// Initialize a technical object to manage hooks of page. Note that conf->hooks_modules contains an array of hook context
110$hookmanager->initHooks(array('supplierbalencelist', 'globalcard'));
111
112$permissiontoreadinvoice = (isModEnabled('invoice') && $user->hasRight('facture', 'lire'));
113
114// Date filter as URL params, to keep it on sort links and exports
115$dateparam = '';
116foreach (array('search_date_start' => $search_date_start, 'search_date_end' => $search_date_end) as $dateprefix => $datets) {
117 if ($datets) {
118 $dateparam .= '&'.$dateprefix.'day='.dol_print_date($datets, '%d').'&'.$dateprefix.'month='.dol_print_date($datets, '%m').'&'.$dateprefix.'year='.dol_print_date($datets, '%Y');
119 }
120}
121$filterparam = '';
122if ($search_status !== '') {
123 $filterparam .= '&search_status='.urlencode($search_status);
124}
125if ($search_late !== '') {
126 $filterparam .= '&search_late='.urlencode($search_late);
127}
128
129
147function recapComptaGetData($db, $object, $hookmanager, $id, $sortfield, $sortorder, &$errors, $datestart = '', $dateend = '', &$opening = 0.0, $search_status = '', $search_late = '')
148{
149 global $conf, $langs;
150
151 $userstatic = new User($db);
152
154 $TData = array();
155
156 $sql = "SELECT s.nom, s.rowid as socid, f.ref, f.total_ttc, f.datef as df,";
157 $sql .= " f.paye as paye, f.fk_statut as statut, f.rowid as facid,";
158 $sql .= " u.login, u.rowid as userid";
159 $sql .= " FROM ".MAIN_DB_PREFIX."societe as s,".MAIN_DB_PREFIX."facture as f";
160 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."user as u ON f.fk_user_valid = u.rowid";
161 $sql .= " WHERE f.fk_soc = s.rowid AND s.rowid = ".((int) $object->id);
162 $sql .= " AND f.entity IN (".getEntity('invoice').")";
163 // Validated or closed only: no draft, and no abandoned or replaced invoice (they are not owed, like in the thirdparty outstanding amount)
164 $sql .= " AND f.fk_statut IN (".((int) Facture::STATUS_VALIDATED).", ".((int) Facture::STATUS_CLOSED).")";
165 $sql .= $db->order($sortfield, $sortorder);
166
167 $resql = $db->query($sql);
168 if (!$resql) {
169 $errors[] = $db->lasterror();
170 dol_syslog(__FUNCTION__.' '.$db->lasterror(), LOG_ERR);
171 return $TData;
172 }
173
174 $num = $db->num_rows($resql);
175
176 // Loop over each invoice
177 for ($i = 0; $i < $num; $i++) {
178 $objf = $db->fetch_object($resql);
179
180 $fac = new Facture($db);
181 $ret = $fac->fetch($objf->facid);
182 if ($ret < 0) {
183 $errors[] = $fac->error;
184 continue;
185 }
186
187 $alreadypaid = $fac->getSommePaiement();
188 $alreadypaid += $fac->getSumDepositsUsed();
189 $alreadypaid += $fac->getSumCreditNotesUsed();
190
191 if ($search_status !== '') {
192 $remaintopay = $object->getInvoiceRemainToPayInOutstanding($fac, 'customer', 0);
193 if ($search_status === 'unpaid' && $remaintopay <= 0) {
194 continue;
195 }
196 if ($search_status === 'paid' && ($remaintopay > 0 || $fac->status == Facture::STATUS_DRAFT || $fac->status == Facture::STATUS_ABANDONED)) {
197 continue;
198 }
199 }
200 if ($search_late !== '') {
201 $remaintopaylate = $object->getInvoiceRemainToPayInOutstanding($fac, 'customer', 1);
202 if (($search_late === 'late' && $remaintopaylate <= 0) || ($search_late === 'notlate' && $remaintopaylate > 0)) {
203 continue;
204 }
205 }
206
207 $userstatic->id = (int) $objf->userid;
208 $userstatic->login = (string) $objf->login;
209
210 $values = array(
211 'fk_facture' => (int) $objf->facid,
212 'date' => $fac->date,
213 'datefieldforsort' => $fac->date.'-'.$fac->ref,
214 'link' => $fac->getNomUrl(1),
215 'status' => $fac->getLibStatut(2, $alreadypaid),
216 'amount' => $fac->total_ttc,
217 'author' => ($userstatic->id > 0 ? $userstatic->getLoginUrl(1) : ''),
218 'ref' => $fac->ref,
219 'statuslabel' => dol_string_nohtmltag($fac->getLibStatut(1, $alreadypaid))
220 );
221
222 $parameters = array('socid' => $id, 'values' => &$values, 'fac' => $fac, 'userstatic' => $userstatic);
223 $reshook = $hookmanager->executeHooks('facdao', $parameters, $object); // Note that $parameters['values'] and $object may have been modified by some hooks
224 if ($reshook < 0) {
225 setEventMessages($hookmanager->error, $hookmanager->errors, 'errors');
226 }
227
228 $TData[] = $values;
229
230 // Payments
231 $sql = "SELECT p.rowid, p.ref, p.datep as dp, pf.amount, p.statut,";
232 $sql .= " p.fk_user_creat, u.login, u.rowid as userid";
233 $sql .= " FROM ".MAIN_DB_PREFIX."paiement_facture as pf,";
234 $sql .= " ".MAIN_DB_PREFIX."paiement as p";
235 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."user as u ON p.fk_user_creat = u.rowid";
236 $sql .= " WHERE pf.fk_paiement = p.rowid";
237 $sql .= " AND p.entity = ".((int) $conf->entity);
238 $sql .= " AND pf.fk_facture = ".((int) $fac->id);
239 $sql .= " ORDER BY p.datep ASC, p.rowid ASC";
240
241 $resqlp = $db->query($sql);
242 if ($resqlp) {
243 $nump = $db->num_rows($resqlp);
244 $j = 0;
245
246 while ($j < $nump) {
247 $objp = $db->fetch_object($resqlp);
248
249 $paymentstatic = new Paiement($db);
250 $paymentstatic->id = $objp->rowid;
251
252 $userstatic->id = $objp->userid;
253 $userstatic->login = $objp->login;
254
255 $values = array(
256 'fk_paiement' => (int) $objp->rowid,
257 'date' => $db->jdate($objp->dp),
258 'datefieldforsort' => $db->jdate($objp->dp).'-'.$fac->ref,
259 'link' => $langs->trans("Payment").' '.$paymentstatic->getNomUrl(1),
260 'status' => '',
261 'amount' => -$objp->amount,
262 'author' => $userstatic->getLoginUrl(1),
263 'ref' => $langs->transnoentitiesnoconv("Payment").' '.($objp->ref ? $objp->ref : $objp->rowid).' ('.$fac->ref.')',
264 'statuslabel' => ''
265 );
266
267 $parameters = array('socid' => $id, 'values' => &$values, 'fac' => $fac, 'userstatic' => $userstatic, 'paymentstatic' => $paymentstatic);
268 $reshook = $hookmanager->executeHooks('paydao', $parameters, $object); // Note that $parameters['values'] and $object may have been modified by some hooks
269 if ($reshook < 0) {
270 setEventMessages($hookmanager->error, $hookmanager->errors, 'errors');
271 }
272
273 $TData[] = $values;
274
275 $j++;
276 }
277
278 $db->free($resqlp);
279 } else {
280 $errors[] = $db->lasterror();
281 dol_syslog(__FUNCTION__.' '.$db->lasterror(), LOG_ERR);
282 }
283 }
284 $db->free($resql);
285
286 if (empty($TData)) {
287 return $TData;
288 }
289
290 // Sort array by date ASC to calculate balance
291 $TData = dol_sort_array($TData, 'datefieldforsort', 'ASC');
292
293 // Balance calculation
294 $balance = 0.0;
295 foreach (array_keys($TData) as $key) {
296 $balance = (float) price2num($balance + (float) $TData[$key]['amount'], 'MT'); // Rounded at each step to avoid float drift
297 $hookbalance = array_key_exists('balance', $TData[$key]) ? (float) $TData[$key]['balance'] : 0.0; // A hook may have set a balance
298 $TData[$key]['balance'] = (float) price2num($hookbalance + $balance, 'MT');
299 }
300
301 // Date filter. The balance was computed on the full history, so rows keep their real balance.
302 $opening = 0.0;
303 if ($datestart || $dateend) {
304 $filtered = array();
305 foreach ($TData as $row) {
306 $rowdate = (int) $row['date'];
307 if ($datestart && $rowdate < $datestart) {
308 $opening = (float) $row['balance'];
309 continue;
310 }
311 if ($dateend && $rowdate > $dateend) {
312 continue;
313 }
314 $filtered[] = $row;
315 }
316 if ($datestart && (!empty($filtered) || (float) price2num($opening, 'MT') != 0)) {
317 $label = $langs->transnoentitiesnoconv("PreviousBalance");
318 array_unshift($filtered, array(
319 'isopening' => true,
320 'date' => $datestart,
321 'datefieldforsort' => '',
322 'link' => dol_escape_htmltag($label),
323 'status' => '',
324 'amount' => 0.0,
325 'author' => '',
326 'ref' => $label,
327 'statuslabel' => '',
328 'balance' => $opening
329 ));
330 }
331 $TData = $filtered;
332 }
333
334 // DESC is the exact reverse of the ASC order used for the balance (rows with same sort key keep a consistent order)
335 if (strtoupper($sortorder) == 'DESC') {
336 $TData = array_reverse($TData);
337 }
338
339 return $TData;
340}
341
351function recapComptaPdfTableHeader($pdf, $outputlangs, $widths, $fontsize)
352{
353 $pdf->SetFont('', 'B', $fontsize - 1);
354 $pdf->SetFillColor(230, 230, 230);
355 $pdf->Cell($widths[0], 6, $outputlangs->transnoentities("Date"), 1, 0, 'C', true);
356 $pdf->Cell($widths[1], 6, $outputlangs->transnoentities("Element"), 1, 0, 'L', true);
357 $pdf->Cell($widths[2], 6, $outputlangs->transnoentities("Status"), 1, 0, 'L', true);
358 $pdf->Cell($widths[3], 6, $outputlangs->transnoentities("Debit"), 1, 0, 'R', true);
359 $pdf->Cell($widths[4], 6, $outputlangs->transnoentities("Credit"), 1, 0, 'R', true);
360 $pdf->Cell($widths[5], 6, $outputlangs->transnoentities("Balance"), 1, 1, 'R', true);
361 $pdf->SetFont('', '', $fontsize - 1);
362}
363
364
365/*
366 * Actions
367 */
368
369$parameters = array('socid' => $id);
370$reshook = $hookmanager->executeHooks('doActions', $parameters, $object, $action); // Note that $object may have been modified by some hooks
371if ($reshook < 0) {
372 setEventMessages($hookmanager->error, $hookmanager->errors, 'errors');
373}
374
375if (empty($reshook) && in_array($action, array('exportpdf', 'exportxlsx')) && !$permissiontoreadinvoice) {
377}
378
379if (empty($reshook) && $action == 'exportpdf' && $id > 0 && $permissiontoreadinvoice) {
380 $errors = array();
381 $opening = 0.0;
382 $TData = recapComptaGetData($db, $object, $hookmanager, $id, $sortfield, $sortorder, $errors, $search_date_start, $search_date_end, $opening, $search_status, $search_late);
383 foreach ($errors as $err) {
384 dol_syslog('recap-compta.php exportpdf '.$err, LOG_WARNING);
385 }
386
387 $outputlangs = $langs;
388 $pdfinit = reportPdfInit($outputlangs, (string) $object->name, $outputlangs->transnoentities("CustomerPreview"));
389 $pdf = $pdfinit['pdf'];
390 $margin = $pdfinit['margin'];
391 $pageheight = $pdfinit['pageheight'];
392 $usablewidth = $pdfinit['usablewidth'];
393 $fontsize = $pdfinit['fontsize'];
394 // Date, Element, Status, Debit, Credit, Balance
395 $ratios = array(0.12, 0.30, 0.16, 0.14, 0.14, 0.14);
396 $widths = array();
397 foreach ($ratios as $ratio) {
398 $widths[] = round($usablewidth * $ratio, 2);
399 }
400
401 $posy = reportPdfCompanyHeader($pdf, $outputlangs, $mysoc, $margin, $fontsize);
402
403 // Title block
404 $pdf->SetXY($margin, $posy);
405 $pdf->SetFont('', 'B', $fontsize + 3);
406 $pdf->Cell($usablewidth, 8, $outputlangs->convToOutputCharset($outputlangs->transnoentities("CustomerPreview")), 0, 1, 'L');
407 $customerlabel = $object->name.($object->code_client ? ' ('.$object->code_client.')' : '');
408 $pdf->SetFont('', 'B', $fontsize);
409 $pdf->Cell($usablewidth, 5, $outputlangs->transnoentities("ThirdParty").': '.$outputlangs->convToOutputCharset($customerlabel), 0, 1, 'L');
410 $pdf->SetFont('', '', $fontsize - 1);
411 $periodlabel = reportPeriodLabel($outputlangs, $search_date_start, $search_date_end);
412 if ($periodlabel !== '') {
413 $pdf->Cell($usablewidth, 5, $outputlangs->transnoentities("Period").': '.$outputlangs->convToOutputCharset($periodlabel), 0, 1, 'L');
414 }
415 $pdf->Cell($usablewidth, 5, $outputlangs->transnoentities("Date").': '.dol_print_date(dol_now(), 'dayhour', 'tzuser', $outputlangs), 0, 1, 'L');
416 $pdf->Ln(3);
417
418 recapComptaPdfTableHeader($pdf, $outputlangs, $widths, $fontsize);
419
420 $totalDebit = 0;
421 $totalCredit = 0;
422 $rowheight = 5;
423 if (empty($TData)) {
424 $pdf->Cell($usablewidth, $rowheight, $outputlangs->transnoentities("NoInvoice"), 1, 1, 'L');
425 }
426 foreach ($TData as $data) {
427 if ($pdf->GetY() + $rowheight > $pageheight - $margin) {
428 $pdf->AddPage();
429 recapComptaPdfTableHeader($pdf, $outputlangs, $widths, $fontsize);
430 }
431 if (!empty($data['isopening'])) {
432 $pdf->SetFont('', 'B', $fontsize - 1);
433 $pdf->Cell($widths[0], $rowheight, dol_print_date($data['date'], 'day', 'tzserver', $outputlangs), 1, 0, 'C');
434 $pdf->Cell($widths[1] + $widths[2], $rowheight, $outputlangs->convToOutputCharset((string) $data['ref']), 1, 0, 'L', false, '', 1);
435 $pdf->Cell($widths[3], $rowheight, '', 1, 0, 'R');
436 $pdf->Cell($widths[4], $rowheight, '', 1, 0, 'R');
437 $pdf->Cell($widths[5], $rowheight, price(price2num((float) $data['balance'], 'MT'), 0, $outputlangs), 1, 1, 'R');
438 $pdf->SetFont('', '', $fontsize - 1);
439 continue;
440 }
441 $amount = (float) price2num($data['amount'], 'MT');
442 $debit = ($amount > 0) ? $amount : 0;
443 $credit = ($amount < 0) ? abs($amount) : 0;
444 $totalDebit += $debit;
445 $totalCredit += $credit;
446
447 $pdf->Cell($widths[0], $rowheight, dol_print_date($data['date'], 'day', 'auto', $outputlangs), 1, 0, 'C');
448 $pdf->Cell($widths[1], $rowheight, $outputlangs->convToOutputCharset((string) ($data['ref'] ?? '')), 1, 0, 'L', false, '', 1);
449 $pdf->Cell($widths[2], $rowheight, $outputlangs->convToOutputCharset((string) ($data['statuslabel'] ?? '')), 1, 0, 'L', false, '', 1);
450 $pdf->Cell($widths[3], $rowheight, ($debit ? price($debit, 0, $outputlangs) : ''), 1, 0, 'R');
451 $pdf->Cell($widths[4], $rowheight, ($credit ? price($credit, 0, $outputlangs) : ''), 1, 0, 'R');
452 $pdf->Cell($widths[5], $rowheight, price(price2num((float) $data['balance'], 'MT'), 0, $outputlangs), 1, 1, 'R');
453 }
454
455 if (!empty($TData)) {
456 if ($pdf->GetY() + $rowheight > $pageheight - $margin) {
457 $pdf->AddPage();
458 }
459 $pdf->SetFont('', 'B', $fontsize - 1);
460 $pdf->Cell($widths[0] + $widths[1] + $widths[2], $rowheight, $outputlangs->transnoentities("Total"), 1, 0, 'L');
461 $pdf->Cell($widths[3], $rowheight, price($totalDebit, 0, $outputlangs), 1, 0, 'R');
462 $pdf->Cell($widths[4], $rowheight, price($totalCredit, 0, $outputlangs), 1, 0, 'R');
463 $pdf->Cell($widths[5], $rowheight, price(price2num($opening + $totalDebit - $totalCredit, 'MT'), 0, $outputlangs), 1, 1, 'R');
464 }
465
466 $filename = reportFileName($outputlangs->transnoentitiesnoconv("CustomerPreview"), ((string) $object->name !== '' ? (string) $object->name : (string) $object->id), 'pdf');
467 $pdf->Output($filename, 'I');
468
469 $db->close();
470 exit;
471}
472
473
474if (empty($reshook) && $action == 'exportxlsx' && $id > 0 && $permissiontoreadinvoice) {
475 if (!class_exists('ZipArchive')) {
476 $langs->load("errors");
477 setEventMessages($langs->trans('ErrorPHPNeedModule', 'zip'), null, 'errors');
478 header('Location: '.$_SERVER["PHP_SELF"].'?socid='.((int) $id).($dol_openinpopup ? '&dol_openinpopup='.urlencode($dol_openinpopup) : ''));
479 exit;
480 }
481
482 $errors = array();
483 $opening = 0.0;
484 $TData = recapComptaGetData($db, $object, $hookmanager, $id, $sortfield, $sortorder, $errors, $search_date_start, $search_date_end, $opening, $search_status, $search_late);
485 foreach ($errors as $err) {
486 dol_syslog('recap-compta.php exportxlsx '.$err, LOG_WARNING);
487 }
488
489 $spreadsheet = reportXlsxInit($langs, $langs->transnoentitiesnoconv("CustomerPreview").' - '.$object->name, (string) $object->name);
490 $sheet = $spreadsheet->getActiveSheet();
491
492 // Header: logo on the left, company info on the right (same as the PDF)
493 $headrows = reportXlsxCompanyHeader($sheet, $langs, $mysoc, 'C', 'F');
494
495 // Title block
496 $row = $headrows + 2;
497 $sheet->setCellValue('A'.$row, $langs->transnoentitiesnoconv("CustomerPreview"));
498 $sheet->getStyle('A'.$row)->getFont()->setBold(true)->setSize(14);
499 $row++;
500 $customerlabel = $object->name.($object->code_client ? ' ('.$object->code_client.')' : '');
501 $sheet->setCellValueExplicit('A'.$row, $langs->transnoentitiesnoconv("ThirdParty").': '.$customerlabel, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
502 $sheet->getStyle('A'.$row)->getFont()->setBold(true);
503 $row++;
504 $periodlabel = reportPeriodLabel($langs, $search_date_start, $search_date_end);
505 if ($periodlabel !== '') {
506 $sheet->setCellValueExplicit('A'.$row, $langs->transnoentitiesnoconv("Period").': '.$periodlabel, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
507 $row++;
508 }
509 $sheet->setCellValueExplicit('A'.$row, $langs->transnoentitiesnoconv("Date").': '.dol_print_date(dol_now(), 'dayhour', 'tzuser', $langs), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
510 $row += 2;
511
512 // Table
513 $headrow = $row;
514 $headers = array('A' => "Date", 'B' => "Element", 'C' => "Status", 'D' => "Debit", 'E' => "Credit", 'F' => "Balance");
515 foreach ($headers as $colletter => $key) {
516 $sheet->setCellValue($colletter.$headrow, $langs->transnoentitiesnoconv($key));
517 }
518 $sheet->getStyle('A'.$headrow.':F'.$headrow)->getFont()->setBold(true);
519 $sheet->getStyle('D'.$headrow.':F'.$headrow)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
520 $sheet->freezePane('A'.($headrow + 1));
521
522 $firstrow = $headrow + 1;
523 $row = $firstrow;
524 $openrow = 0;
525 foreach ($TData as $data) {
526 if (!empty($data['isopening'])) {
527 $openrow = $row;
528 $sheet->setCellValue('A'.$row, \PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel((int) dol_print_date($data['date'], '%Y'), (int) dol_print_date($data['date'], '%m'), (int) dol_print_date($data['date'], '%d')));
529 $sheet->setCellValueExplicit('B'.$row, (string) $data['ref'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
530 $sheet->setCellValue('F'.$row, (float) price2num((float) $data['balance'], 'MT'));
531 $sheet->getStyle('A'.$row.':F'.$row)->getFont()->setBold(true);
532 $row++;
533 continue;
534 }
535 $amount = (float) price2num($data['amount'], 'MT');
536 if (!empty($data['date'])) {
537 $sheet->setCellValue('A'.$row, \PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel((int) dol_print_date($data['date'], '%Y'), (int) dol_print_date($data['date'], '%m'), (int) dol_print_date($data['date'], '%d')));
538 }
539 $sheet->setCellValueExplicit('B'.$row, (string) ($data['ref'] ?? ''), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
540 $sheet->setCellValueExplicit('C'.$row, (string) ($data['statuslabel'] ?? ''), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
541 if ($amount > 0) {
542 $sheet->setCellValue('D'.$row, $amount);
543 } elseif ($amount < 0) {
544 $sheet->setCellValue('E'.$row, abs($amount));
545 }
546 $sheet->setCellValue('F'.$row, (float) price2num((float) $data['balance'], 'MT'));
547 $row++;
548 }
549
550 if ($row > $firstrow) {
551 $last = $row - 1;
552 $sheet->setCellValue('C'.$row, $langs->transnoentitiesnoconv("Total"));
553 $sheet->setCellValue('D'.$row, '=SUM(D'.$firstrow.':D'.$last.')');
554 $sheet->setCellValue('E'.$row, '=SUM(E'.$firstrow.':E'.$last.')');
555 $sheet->setCellValue('F'.$row, ($openrow ? '=F'.$openrow.'+' : '=').'D'.$row.'-E'.$row);
556 $sheet->getStyle('A'.$row.':F'.$row)->getFont()->setBold(true);
557 }
558
559 $sheet->getStyle('A'.$firstrow.':A'.$row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD);
560 $sheet->getStyle('D'.$firstrow.':F'.$row)->getNumberFormat()->setFormatCode('#,##0.00');
561 foreach (array('A' => 12, 'B' => 34, 'C' => 20, 'D' => 14, 'E' => 14, 'F' => 14) as $colletter => $colwidth) {
562 $sheet->getColumnDimension($colletter)->setWidth($colwidth);
563 }
564
565 // Print on one page width
566 $sheet->getPageSetup()->setFitToPage(true);
567 $sheet->getPageSetup()->setFitToWidth(1);
568 $sheet->getPageSetup()->setFitToHeight(0);
569
570 $filename = reportFileName($langs->transnoentitiesnoconv("CustomerPreview"), ((string) $object->name !== '' ? (string) $object->name : (string) $object->id), 'xlsx');
571 reportXlsxOutput($spreadsheet, $filename);
572
573 $db->close();
574 exit;
575}
576
577
578/*
579 * View
580 */
581
582$form = new Form($db);
583
584$title = $langs->trans("ThirdParty").' - '.$langs->trans("Summary");
585if (getDolGlobalString('MAIN_HTML_TITLE') && preg_match('/thirdpartynameonly/', getDolGlobalString('MAIN_HTML_TITLE')) && $object->name) {
586 $title = $object->name.' - '.$langs->trans("Summary");
587}
588$help_url = 'EN:Module_Third_Parties|FR:Module_Tiers|ES:Empresas';
589
590llxHeader('', $title, $help_url);
591
592if ($id > 0) {
593 $param = '';
594 if ($id > 0) {
595 $param .= '&socid='.$id;
596 }
597 if ($dol_openinpopup) {
598 $param .= '&dol_openinpopup='.urlencode($dol_openinpopup);
599 }
600 $param .= $dateparam.$filterparam;
601
602 if (empty($dol_openinpopup)) {
603 $head = societe_prepare_head($object);
604
605 print dol_get_fiche_head($head, 'customer', $langs->trans("ThirdParty"), 0, 'company');
606 dol_banner_tab($object, 'socid', '', ($user->socid ? 0 : 1), 'rowid', 'nom', '', '', 0, '', '', 1);
607 print dol_get_fiche_end();
608 }
609
610 if ($permissiontoreadinvoice) {
611 // Invoice list
612 $morehtmlright = reportExportButtons('&socid='.((int) $id).'&sortfield='.urlencode($sortfield).'&sortorder='.urlencode($sortorder).$dateparam.$filterparam, 'recapcompta');
613 $titrelist = $langs->trans("CustomerPreview");
614 if ($search_status === 'unpaid' && $search_late === 'late') {
615 $titrelist = $langs->trans("CurrentOutstandingBillLate");
616 } elseif ($search_status === 'unpaid' && $search_late === '') {
617 $titrelist = $langs->trans("CurrentOutstandingBill");
618 }
619 print load_fiche_titre($titrelist, $morehtmlright);
620
621 // Date filter
622 print '<form method="GET" action="'.dol_escape_htmltag($_SERVER["PHP_SELF"]).'" name="recapcomptafilter">';
623 print '<input type="hidden" name="socid" value="'.((int) $id).'">';
624 if ($dol_openinpopup) {
625 print '<input type="hidden" name="dol_openinpopup" value="'.dol_escape_htmltag($dol_openinpopup).'">';
626 }
627 print '<input type="hidden" name="sortfield" value="'.dol_escape_htmltag($sortfield).'">';
628 print '<input type="hidden" name="sortorder" value="'.dol_escape_htmltag($sortorder).'">';
629 print '<div class="div-table-responsive-no-min">';
630 print '<div class="inline-block valignmiddle nowrapfordate">';
631 print $form->selectDate($search_date_start ? $search_date_start : -1, 'search_date_start', 0, 0, 1, '', 1, 0, 0, '', '', '', '', 1, '', $langs->trans('From'));
632 print '</div> ';
633 print '<div class="inline-block valignmiddle nowrapfordate">';
634 print $form->selectDate($search_date_end ? $search_date_end : -1, 'search_date_end', 0, 0, 1, '', 1, 0, 0, '', '', '', '', 1, '', $langs->trans('to'));
635 print '</div> ';
636 print '<div class="inline-block valignmiddle marginleftonlyshort">'.$langs->trans('Status').' ';
637 print $form->selectarray('search_status', array('unpaid' => $langs->trans('Unpaid'), 'paid' => $langs->trans('Paid')), $search_status, $langs->trans('All'), 0, 0, '', 0, 0, 0, '', 'minwidth100', 0);
638 print '</div> ';
639 print '<div class="inline-block valignmiddle marginleftonlyshort">'.$langs->trans('Late').' ';
640 print $form->selectarray('search_late', array('late' => $langs->trans('Late'), 'notlate' => $langs->trans('NotLate')), $search_late, $langs->trans('All'), 0, 0, '', 0, 0, 0, '', 'minwidth100', 0);
641 print '</div> ';
642 print '<div class="inline-block valignmiddle">'.$form->showFilterButtons().'</div>';
643 print '</div>';
644 print '</form>';
645
646 print '<table class="noborder tagtable liste centpercent">';
647 print '<tr class="liste_titre">';
648 if (!empty($arrayfields['f.datef']['checked'])) {
649 print_liste_field_titre($arrayfields['f.datef']['label'], $_SERVER["PHP_SELF"], "f.datef", "", $param, 'align="center" class="nowrap"', $sortfield, $sortorder);
650 }
651 print '<td>'.$langs->trans("Element").'</td>';
652 print '<td>'.$langs->trans("Status").'</td>';
653 print '<td class="right">'.$langs->trans("Debit").'</td>';
654 print '<td class="right">'.$langs->trans("Credit").'</td>';
655 print '<td class="right">'.$langs->trans("Balance").'</td>';
656 print '<td class="right">'.$langs->trans("Author").'</td>';
657 print '</tr>';
658
659 $errors = array();
660 $opening = 0.0;
661 $TData = recapComptaGetData($db, $object, $hookmanager, $id, $sortfield, $sortorder, $errors, $search_date_start, $search_date_end, $opening, $search_status, $search_late);
662 foreach ($errors as $err) {
663 print '<tr class="oddeven"><td colspan="7"><span class="error">'.dol_escape_htmltag($err).'</span></td></tr>';
664 }
665
666 if (empty($TData)) {
667 print '<tr class="oddeven"><td colspan="7"><span class="opacitymedium">'.$langs->trans("NoInvoice").'</span></td></tr>';
668 } else {
669 $totalDebit = 0;
670 $totalCredit = 0;
671
672 // Display array
673 foreach ($TData as $data) {
674 if (!empty($data['isopening'])) {
675 print '<tr class="oddeven">';
676 print '<td class="center">'.dol_print_date($data['date'], 'day', 'tzserver').'</td>';
677 print '<td colspan="2"><b>'.dol_escape_htmltag((string) $data['ref']).'</b></td>';
678 print '<td></td><td></td>';
679 print '<td class="right"><span class="amount"><b>'.price($data['balance']).'</b></span></td>';
680 print '<td></td>';
681 print "</tr>\n";
682 continue;
683 }
684 $html_class = '';
685 if (!empty($data['fk_facture'])) {
686 $html_class = 'facid-'.$data['fk_facture'];
687 } elseif (!empty($data['fk_paiement'])) {
688 $html_class = 'payid-'.$data['fk_paiement'];
689 }
690
691 print '<tr class="oddeven '.$html_class.'">';
692
693 $datedetail = dol_print_date($data['date'], 'dayhour');
694 if (!empty($data['fk_facture'])) {
695 $datedetail = dol_print_date($data['date'], 'day');
696 }
697 print '<td class="center" title="'.dol_escape_htmltag($datedetail).'">';
698 print dol_print_date($data['date'], 'day');
699 print "</td>\n";
700
701 print '<td>'.$data['link']."</td>\n";
702
703 print '<td class="left">'.$data['status'].'</td>';
704
705 $amount = (float) price2num($data['amount'], 'MT');
706 print '<td class="right">'.(($amount > 0) ? price($amount) : '')."</td>\n";
707 $totalDebit += ($amount > 0) ? $amount : 0;
708
709 print '<td class="right">'.(($amount < 0) ? price(abs($amount)) : '')."</td>\n";
710 $totalCredit += ($amount < 0) ? abs($amount) : 0;
711
712 // Balance
713 print '<td class="right"><span class="amount">'.price($data['balance'])."</span></td>\n";
714
715 // Author
716 print '<td class="nowrap right">';
717 print $data['author'];
718 print '</td>';
719
720 print "</tr>\n";
721 }
722
723 print '<tr class="liste_total">';
724 print '<td colspan="3">&nbsp;</td>';
725 print '<td class="right">'.price($totalDebit).'</td>';
726 print '<td class="right">'.price($totalCredit).'</td>';
727 print '<td class="right">'.price(price2num($opening + $totalDebit - $totalCredit, 'MT')).'</td>';
728 print '<td></td>';
729 print "</tr>\n";
730 }
731
732 print "</table>";
733 }
734} else {
735 dol_print_error($db);
736}
737
738llxFooter();
739
740$db->close();
$id
Support class for third parties, contacts, members, users or resources.
Definition account.php:47
if(! $sortfield) if(! $sortorder) $object
Definition account.php:100
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
$object ref
Definition info.php:90
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $conf
The main.inc.php has been included so the following variable are now defined:
Class to manage invoices.
const STATUS_DRAFT
Draft status.
const STATUS_VALIDATED
Validated (need to be paid)
const STATUS_ABANDONED
Classified abandoned and no payment done.
const STATUS_CLOSED
Classified paid.
Class to manage generation of HTML components Only common components must be here.
Class to manage payments of customer invoices.
Class to manage third parties objects (customers, suppliers, prospects...)
Class to manage Dolibarr users.
societe_prepare_head(Societe $object, $subtabs='')
Return array of tabs to used on pages for third parties cards.
global $mysoc
dol_now($mode='gmt')
Return date for now.
price2num($amount, $rounding='', $option=0, $currencycode='')
Function that return a number with universal decimal format (decimal separator is '.
GETPOSTDATE($prefix, $hourTime='', $gm='auto', $saverestore='')
Helper function that combines values of a dolibarr DatePicker (such as Form\selectDate) for year,...
dol_string_nohtmltag($stringtoclean, $removelinefeed=1, $pagecodeto='UTF-8', $strip_tags=0, $removedoublespaces=1)
Clean a string from all HTML tags and entities.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages.
dol_sort_array(&$array, $index, $order='asc', $natsort=0, $case_sensitive=0, $keepindex=0)
Advanced sort array by the value of a given key, which produces ascending (default) or descending out...
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0, $nodefault=0)
Return value of a param into GET or POST supervariable.
GETPOSTINT($paramname, $method=0, $nodefault=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false, $decorate=0)
Output date in a string format according to outputlangs (or langs if not defined).
GETPOSTISSET($paramname)
Return true if we are in a context of submitting the parameter $paramname from a POST of a form.
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
isModEnabled($module)
Is Dolibarr module enabled.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
print_liste_field_titre($name, $file="", $field="", $begin="", $param="", $moreattrib="", $sortfield="", $sortorder="", $prefix="", $tooltip="", $forcenowrapcolumntitle=0)
Show title line of an array.
dol_get_fiche_head($links=array(), $active='', $title='', $notab=0, $picto='', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limittoshow=0, $moretabssuffix='', $dragdropfile=0, $morecssdiv='')
Show tabs of a record.
Definition html.lib.php:542
dol_get_fiche_end($notab=0)
Return tab footer of a card.
Definition html.lib.php:740
load_fiche_titre($title, $morehtmlright='', $picto='generic', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='', $morecssonpicto='widthpictotitle')
Load a title with picto.
dol_escape_htmltag($stringtoescape, $keepb=0, $keepn=0, $noescapetags='', $escapeonlyhtmltags=0, $cleanalsojavascript=0)
Definition html.lib.php:183
recapComptaPdfTableHeader($pdf, $outputlangs, $widths, $fontsize)
Print the statement table header row into the PDF.
reportPeriodLabel($outputlangs, $datestart, $dateend)
Return the label of a filtered period.
reportXlsxCompanyHeader($sheet, $outputlangs, $mysoc, $firstcol, $lastcol)
Write logo (left) and company info (right) at top of a report worksheet.
reportPdfInit($outputlangs, $title, $subject, $orientation='P')
Create a PDF instance for a report, with first page added.
reportFileName($title, $name, $ext)
Return a report export file name.
reportPdfCompanyHeader($pdf, $outputlangs, $mysoc, $margin, $fontsize)
Print our company logo (left) and company info (right) at top of a report PDF page.
reportXlsxInit($outputlangs, $title, $sheetlabel)
Create a spreadsheet for a report.
reportExportButtons($param, $idprefix, $enabled=1)
Return the PDF and XLSX export buttons of a report page.
reportXlsxOutput($spreadsheet, $filename)
Send a report spreadsheet to the browser as an xlsx download.
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.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.