dolibarr 25.0.0-alpha
tax.lib.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2004-2009 Laurent Destailleur <eldy@users.sourceforge.net>
3 * Copyright (C) 2026 Jose Martinez <jose.martinez@pichinov.com>
4 * Copyright (C) 2006-2007 Yannick Warnier <ywarnier@beeznest.org>
5 * Copyright (C) 2011 Regis Houssin <regis.houssin@inodbox.com>
6 * Copyright (C) 2012-2017 Juanjo Menent <jmenent@2byte.es>
7 * Copyright (C) 2012 Cédric Salvador <csalvador@gpcsolutions.fr>
8 * Copyright (C) 2012-2014 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
9 * Copyright (C) 2015 Marcos García <marcosgdf@gmail.com>
10 * Copyright (C) 2021-2022 Open-Dsi <support@open-dsi.fr>
11 * Copyright (C) 2024-2025 Frédéric France <frederic.france@free.fr>
12 * Copyright (C) 2024-2026 MDW <mdeweerd@users.noreply.github.com>
13 *
14 * This program is free software; you can redistribute it and/or modify
15 * it under the terms of the GNU General Public License as published by
16 * the Free Software Foundation; either version 3 of the License, or
17 * (at your option) any later version.
18 *
19 * This program is distributed in the hope that it will be useful,
20 * but WITHOUT ANY WARRANTY; without even the implied warranty of
21 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 * GNU General Public License for more details.
23 *
24 * You should have received a copy of the GNU General Public License
25 * along with this program. If not, see <https://www.gnu.org/licenses/>.
26 */
27
34require_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php';
35require_once DOL_DOCUMENT_ROOT.'/fourn/class/fournisseur.facture.class.php';
36require_once DOL_DOCUMENT_ROOT.'/expensereport/class/expensereport.class.php';
37
44function tax_prepare_head(ChargeSociales $object)
45{
46 global $db, $langs, $conf, $user;
47
48 $h = 0;
49 $head = array();
50
51 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/card.php?id='.$object->id;
52 $head[$h][1] = $langs->trans('SocialContribution');
53 $head[$h][2] = 'card';
54 $h++;
55
56 // Show more tabs from modules
57 // Entries must be declared in modules descriptor with line
58 // $this->tabs = array('entity:+tabname:Title:@mymodule:/mymodule/mypage.php?id=__ID__'); to add new tab
59 // $this->tabs = array('entity:-tabname); to remove a tab
60 complete_head_from_modules($conf, $langs, $object, $head, $h, 'tax');
61
62 require_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php';
63 require_once DOL_DOCUMENT_ROOT.'/core/class/link.class.php';
64 $upload_dir = $conf->tax->dir_output."/".dol_sanitizeFileName($object->ref);
65 $nbFiles = count(dol_dir_list($upload_dir, 'files', 0, '', '(\.meta|_preview.*\.png)$'));
66 $nbLinks = Link::count($db, $object->element, $object->id);
67 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/document.php?id='.$object->id;
68 $head[$h][1] = $langs->trans("Documents");
69 if (($nbFiles + $nbLinks) > 0) {
70 $head[$h][1] .= '<span class="badge marginleftonlyshort">'.($nbFiles + $nbLinks).'</span>';
71 }
72 $head[$h][2] = 'documents';
73 $h++;
74
75
76 $nbNote = 0;
77 if (!empty($object->note_private)) {
78 $nbNote++;
79 }
80 if (!empty($object->note_public)) {
81 $nbNote++;
82 }
83 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/note.php?id='.$object->id;
84 $head[$h][1] = $langs->trans('Notes');
85 if ($nbNote > 0) {
86 $head[$h][1] .= (!getDolGlobalString('MAIN_OPTIMIZEFORTEXTBROWSER') ? '<span class="badge marginleftonlyshort">'.$nbNote.'</span>' : '');
87 }
88 $head[$h][2] = 'note';
89 $h++;
90
91
92 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/info.php?id='.$object->id;
93 $head[$h][1] = $langs->trans("Info");
94 $head[$h][2] = 'info';
95 $h++;
96
97
98 complete_head_from_modules($conf, $langs, $object, $head, $h, 'tax', 'remove');
99
100 return $head;
101}
102
103
118function tax_by_thirdparty($type, $db, $y, $date_start, $date_end, $modetax, $direction, $m = 0, $q = 0)
119{
120 global $conf, $hookmanager;
121 $hookmanager->initHooks(array('taxvatlist'));
122
123 // If we use date_start and date_end, we must not use $y, $m, $q
124 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
125 dol_print_error(null, 'Bad value of input parameter for tax_by_thirdparty');
126 }
127
128 $list = array();
129 if ($direction == 'sell') {
130 $invoicetable = 'facture';
131 $invoicedettable = 'facturedet';
132 $fk_facture = 'fk_facture';
133 $fk_facture2 = 'fk_facture';
134 $fk_payment = 'fk_paiement';
135 $total_tva = 'total_tva';
136 $paymenttable = 'paiement';
137 $paymentfacturetable = 'paiement_facture';
138 $invoicefieldref = 'ref';
139 } elseif ($direction == 'buy') {
140 $invoicetable = 'facture_fourn';
141 $invoicedettable = 'facture_fourn_det';
142 $fk_facture = 'fk_facture_fourn';
143 $fk_facture2 = 'fk_facturefourn';
144 $fk_payment = 'fk_paiementfourn';
145 $total_tva = 'tva';
146 $paymenttable = 'paiementfourn';
147 $paymentfacturetable = 'paiementfourn_facturefourn';
148 $invoicefieldref = 'ref';
149 } else {
150 dol_print_error(null, 'Invalid "direction" - must be buy or sell - found ' . $direction);
151 return -2;
152 }
153
154 if (strpos($type, 'localtax') === 0) {
155 $f_rate = $type.'_tx';
156 } else {
157 $f_rate = 'tva_tx';
158 }
159
160 $total_localtax1 = 'total_localtax1';
161 $total_localtax2 = 'total_localtax2';
162
163
164 // CAS DES BIENS/PRODUITS
165
166 // Define sql request
167 $sql = '';
168 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_PRODUCT') == 'invoice')
169 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_PRODUCT') == 'invoice')) {
170 // Count on delivery date (use invoice date as delivery is unknown)
171 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
172 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
173 $sql .= " d.date_start as date_start, d.date_end as date_end,";
174 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
175 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
176 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
177 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
178 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
179 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
180 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
181 $sql .= " ,'' as datep";
182 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f,";
183 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
184 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d";
185 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
186 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
187 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Validated or paid (partially or completely)
188 if ($direction == 'buy') {
189 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
190 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
191 } else {
192 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
193 }
194 } else {
195 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
196 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
197 } else {
198 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
199 }
200 }
201 $sql .= " AND f.rowid = d.".$db->sanitize($fk_facture);
202 $sql .= " AND s.rowid = f.fk_soc";
203 if ($y && $m) {
204 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
205 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
206 } elseif ($y) {
207 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
208 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
209 }
210 if ($q) {
211 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
212 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
213 }
214 if ($date_start && $date_end) {
215 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
216 }
217 $sql .= " AND (d.product_type = 0"; // Limit to products
218 $sql .= " AND d.date_start IS NULL AND d.date_end IS NULL)"; // enhance detection of products
219 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
220 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
221 }
222 // Add SQL restrictions from hooks (context taxvatlist), e.g. a deposit pivot date restricting deposits by their date
223 $parameters = array('invoicealias' => 'f', 'issupplier' => ($direction == 'buy' ? 1 : 0), 'datefield' => 'datef');
224 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
225 $sql .= $hookmanager->resPrint;
226 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture);
227 } else {
228 // Count on payments date
229 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
230 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
231 $sql .= " d.date_start as date_start, d.date_end as date_end,";
232 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
233 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
234 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
235 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
236 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
237 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
238 $sql .= " pf.".$db->sanitize($fk_payment)." as payment_id, pf.amount as payment_amount,";
239 $sql .= " pa.datep as datep, pa.ref as payment_ref";
240 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f,";
241 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($paymentfacturetable)." as pf,";
242 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($paymenttable)." as pa,";
243 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
244 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d";
245 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
246 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
247 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Paid (partially or completely)
248 if ($direction == 'buy') {
249 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
250 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
251 } else {
252 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
253 }
254 } else {
255 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
256 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
257 } else {
258 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
259 }
260 }
261 $sql .= " AND f.rowid = d.".$db->sanitize($fk_facture);
262 $sql .= " AND s.rowid = f.fk_soc";
263 $sql .= " AND pf.".$db->sanitize($fk_facture2)." = f.rowid";
264 $sql .= " AND pa.rowid = pf.".$db->sanitize($fk_payment);
265 if ($y && $m) {
266 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
267 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
268 } elseif ($y) {
269 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
270 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
271 }
272 if ($q) {
273 $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
274 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
275 }
276 if ($date_start && $date_end) {
277 $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
278 }
279 $sql .= " AND (d.product_type = 0"; // Limit to products
280 $sql .= " AND d.date_start IS NULL AND d.date_end IS NULL)"; // enhance detection of products
281 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
282 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
283 }
284 // Add SQL restrictions from hooks (context taxvatlist), e.g. a deposit pivot date restricting deposits by their date
285 $parameters = array('invoicealias' => 'f', 'issupplier' => ($direction == 'buy' ? 1 : 0), 'datefield' => 'datef');
286 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
287 $sql .= $hookmanager->resPrint;
288 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture).", pf.rowid";
289 }
290
291 dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
292
293 $resql = $db->query($sql);
294 if ($resql) {
295 $company_id = -1;
296 $oldrowid = '';
297 while ($assoc = $db->fetch_array($resql)) {
298 if (!isset($list[$assoc['company_id']]['totalht'])) {
299 $list[$assoc['company_id']]['totalht'] = 0;
300 }
301 if (!isset($list[$assoc['company_id']]['vat'])) {
302 $list[$assoc['company_id']]['vat'] = 0;
303 }
304 if (!isset($list[$assoc['company_id']]['localtax1'])) {
305 $list[$assoc['company_id']]['localtax1'] = 0;
306 }
307 if (!isset($list[$assoc['company_id']]['localtax2'])) {
308 $list[$assoc['company_id']]['localtax2'] = 0;
309 }
310
311 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
312 $oldrowid = $assoc['rowid'];
313 $list[$assoc['company_id']]['totalht'] += (float) $assoc['total_ht'];
314 $list[$assoc['company_id']]['vat'] += (float) $assoc['total_vat'];
315 $list[$assoc['company_id']]['localtax1'] += (float) $assoc['total_localtax1'];
316 $list[$assoc['company_id']]['localtax2'] += (float) $assoc['total_localtax2'];
317 }
318
319 $list[$assoc['company_id']]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
320 $list[$assoc['company_id']]['dtype'][] = (int) $assoc['dtype'];
321 $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
322 $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
323
324 $list[$assoc['company_id']]['company_name'][] = (string) $assoc['company_name'];
325 $list[$assoc['company_id']]['company_id'][] = (int) $assoc['company_id'];
326 $list[$assoc['company_id']]['company_alias'][] = (string) $assoc['company_alias'];
327 $list[$assoc['company_id']]['company_email'][] = (string) $assoc['company_email'];
328 $list[$assoc['company_id']]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
329 $list[$assoc['company_id']]['company_client'][] = (int) $assoc['company_client'];
330 $list[$assoc['company_id']]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
331 $list[$assoc['company_id']]['company_customer_code'][] = (string) $assoc['company_customer_code'];
332 $list[$assoc['company_id']]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
333 $list[$assoc['company_id']]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
334 $list[$assoc['company_id']]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
335 $list[$assoc['company_id']]['company_status'][] = (int) $assoc['company_status'];
336
337 $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
338 $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
339 $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
340
341 $list[$assoc['company_id']]['facid'][] = (int) $assoc['facid'];
342 $list[$assoc['company_id']]['facnum'][] = (string) $assoc['facnum'];
343 $list[$assoc['company_id']]['type'][] = (int) $assoc['type'];
344 $list[$assoc['company_id']]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
345 $list[$assoc['company_id']]['descr'][] = (string) $assoc['descr'];
346
347 $list[$assoc['company_id']]['totalht_list'][] = (float) $assoc['total_ht'];
348 $list[$assoc['company_id']]['vat_list'][] = (float) $assoc['total_vat'];
349 $list[$assoc['company_id']]['localtax1_list'][] = (float) $assoc['total_localtax1'];
350 $list[$assoc['company_id']]['localtax2_list'][] = (float) $assoc['total_localtax2'];
351
352 $list[$assoc['company_id']]['pid'][] = (int) $assoc['pid'];
353 $list[$assoc['company_id']]['pref'][] = (string) $assoc['pref'];
354 $list[$assoc['company_id']]['ptype'][] = (int) $assoc['ptype'];
355 $list[$assoc['company_id']]['pstatus'][] = (int) $assoc['pstatus'];
356 $list[$assoc['company_id']]['pstatusbuy'][] = (int) $assoc['pstatusbuy'];
357
358 $list[$assoc['company_id']]['payment_id'][] = (int) $assoc['payment_id'];
359 $list[$assoc['company_id']]['payment_ref'][] = (string) $assoc['payment_ref'];
360 $list[$assoc['company_id']]['payment_amount'][] = (float) $assoc['payment_amount'];
361
362 $company_id = $assoc['company_id'];
363 }
364 } else {
365 dol_print_error($db);
366 return -3;
367 }
368
369
370 // CAS DES SERVICES
371
372 // Define sql request
373 $sql = '';
374 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_SERVICE') == 'invoice')
375 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_SERVICE') == 'invoice')) {
376 // Count on invoice date
377 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
378 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
379 $sql .= " d.date_start as date_start, d.date_end as date_end,";
380 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
381 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
382 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
383 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
384 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
385 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
386 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
387 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f,";
388 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
389 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d";
390 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
391 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
392 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Validated or paid (partially or completely)
393 if ($direction == 'buy') {
394 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
395 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
396 } else {
397 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
398 }
399 } else {
400 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
401 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
402 } else {
403 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
404 }
405 }
406 $sql .= " AND f.rowid = d.".$db->sanitize($fk_facture);
407 $sql .= " AND s.rowid = f.fk_soc";
408 if ($y && $m) {
409 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
410 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
411 } elseif ($y) {
412 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
413 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
414 }
415 if ($q) {
416 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
417 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
418 }
419 if ($date_start && $date_end) {
420 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
421 }
422 $sql .= " AND (d.product_type = 1"; // Limit to services
423 $sql .= " OR d.date_start IS NOT NULL OR d.date_end IS NOT NULL)"; // enhance detection of service
424 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
425 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
426 }
427 // Add SQL restrictions from hooks (context taxvatlist), e.g. a deposit pivot date restricting deposits by their date
428 $parameters = array('invoicealias' => 'f', 'issupplier' => ($direction == 'buy' ? 1 : 0), 'datefield' => 'datef');
429 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
430 $sql .= $hookmanager->resPrint;
431 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture);
432 } else {
433 // Count on payments date
434 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
435 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
436 $sql .= " d.date_start as date_start, d.date_end as date_end,";
437 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
438 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
439 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
440 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
441 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
442 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
443 $sql .= " pf.".$db->sanitize($fk_payment)." as payment_id, pf.amount as payment_amount,";
444 $sql .= " pa.datep as datep, pa.ref as payment_ref";
445 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f,";
446 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($paymentfacturetable)." as pf,";
447 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($paymenttable)." as pa,";
448 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
449 $sql .= " ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d";
450 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
451 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
452 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Paid (partially or completely)
453 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
454 $sql .= " AND f.rowid = d.".$db->sanitize($fk_facture);
455 $sql .= " AND s.rowid = f.fk_soc";
456 $sql .= " AND pf.".$db->sanitize($fk_facture2)." = f.rowid";
457 $sql .= " AND pa.rowid = pf.".$db->sanitize($fk_payment);
458 if ($y && $m) {
459 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
460 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
461 } elseif ($y) {
462 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
463 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
464 }
465 if ($q) {
466 $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
467 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
468 }
469 if ($date_start && $date_end) {
470 $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
471 }
472 $sql .= " AND (d.product_type = 1"; // Limit to services
473 $sql .= " OR d.date_start IS NOT NULL OR d.date_end IS NOT NULL)"; // enhance detection of service
474 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
475 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
476 }
477 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture).", pf.rowid";
478 }
479
480 dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
481 $resql = $db->query($sql);
482 if ($resql) {
483 $company_id = -1;
484 $oldrowid = '';
485 while ($assoc = $db->fetch_array($resql)) {
486 if (!isset($list[$assoc['company_id']]['totalht'])) {
487 $list[$assoc['company_id']]['totalht'] = 0;
488 }
489 if (!isset($list[$assoc['company_id']]['vat'])) {
490 $list[$assoc['company_id']]['vat'] = 0;
491 }
492 if (!isset($list[$assoc['company_id']]['localtax1'])) {
493 $list[$assoc['company_id']]['localtax1'] = 0;
494 }
495 if (!isset($list[$assoc['company_id']]['localtax2'])) {
496 $list[$assoc['company_id']]['localtax2'] = 0;
497 }
498
499 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
500 $oldrowid = $assoc['rowid'];
501 $list[$assoc['company_id']]['totalht'] += (float) $assoc['total_ht'];
502 $list[$assoc['company_id']]['vat'] += (float) $assoc['total_vat'];
503 $list[$assoc['company_id']]['localtax1'] += (float) $assoc['total_localtax1'];
504 $list[$assoc['company_id']]['localtax2'] += (float) $assoc['total_localtax2'];
505 }
506 $list[$assoc['company_id']]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
507 $list[$assoc['company_id']]['dtype'][] = $assoc['dtype'];
508 $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
509 $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
510
511 $list[$assoc['company_id']]['company_name'][] = (string) $assoc['company_name'];
512 $list[$assoc['company_id']]['company_id'][] = (int) $assoc['company_id'];
513 $list[$assoc['company_id']]['company_alias'][] = (string) $assoc['company_alias'];
514 $list[$assoc['company_id']]['company_email'][] = (string) $assoc['company_email'];
515 $list[$assoc['company_id']]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
516 $list[$assoc['company_id']]['company_client'][] = (int) $assoc['company_client'];
517 $list[$assoc['company_id']]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
518 $list[$assoc['company_id']]['company_customer_code'][] = (string) $assoc['company_customer_code'];
519 $list[$assoc['company_id']]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
520 $list[$assoc['company_id']]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
521 $list[$assoc['company_id']]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
522 $list[$assoc['company_id']]['company_status'][] = (int) $assoc['company_status'];
523
524 $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
525 $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
526 $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
527
528 $list[$assoc['company_id']]['facid'][] = (int) $assoc['facid'];
529 $list[$assoc['company_id']]['facnum'][] = (string) $assoc['facnum'];
530 $list[$assoc['company_id']]['type'][] = (int) $assoc['type'];
531 $list[$assoc['company_id']]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
532 $list[$assoc['company_id']]['descr'][] = (string) $assoc['descr'];
533
534 $list[$assoc['company_id']]['totalht_list'][] = (float) $assoc['total_ht'];
535 $list[$assoc['company_id']]['vat_list'][] = (float) $assoc['total_vat'];
536 $list[$assoc['company_id']]['localtax1_list'][] = (float) $assoc['total_localtax1'];
537 $list[$assoc['company_id']]['localtax2_list'][] = (float) $assoc['total_localtax2'];
538
539 $list[$assoc['company_id']]['pid'][] = (int) $assoc['pid'];
540 $list[$assoc['company_id']]['pref'][] = (string) $assoc['pref'];
541 $list[$assoc['company_id']]['ptype'][] = (int) $assoc['ptype'];
542 $list[$assoc['company_id']]['pstatus'][] = (int) $assoc['pstatus'];
543 $list[$assoc['company_id']]['pstatusbuy'][] = (int) $assoc['pstatusbuy'];
544
545 $list[$assoc['company_id']]['payment_id'][] = (int) $assoc['payment_id'];
546 $list[$assoc['company_id']]['payment_ref'][] = (string) $assoc['payment_ref'];
547 $list[$assoc['company_id']]['payment_amount'][] = (float) $assoc['payment_amount'];
548
549 $company_id = $assoc['company_id'];
550 }
551 } else {
552 dol_print_error($db);
553 return -3;
554 }
555
556
557 // CASE OF EXPENSE REPORT
558
559 if ($direction == 'buy') { // buy only for expense reports
560 // Define sql request
561 $sql = '';
562
563 // Count on payments date
564 $sql = "SELECT d.rowid, d.product_type as dtype, e.rowid as facid, d.".$db->sanitize($f_rate)." as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.total_tva as total_vat, e.note_private as descr,";
565 $sql .= " d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
566 $sql .= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
567 $sql .= " e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
568 $sql .= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
569 $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as e";
570 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid ";
571 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid ";
572 $sql .= " WHERE e.entity = ".((int) $conf->entity);
573 $sql .= " AND e.fk_statut IN (" . ExpenseReport::STATUS_CLOSED . ")";
574 if ($y && $m) {
575 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
576 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
577 } elseif ($y) {
578 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
579 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
580 }
581 if ($q) {
582 $sql .= " AND p.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
583 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
584 }
585 if ($date_start && $date_end) {
586 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
587 }
588 $sql .= " AND (d.product_type = -1";
589 $sql .= " OR e.date_debut IS NOT NULL OR e.date_fin IS NOT NULL)"; // enhance detection of service
590 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
591 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.total_tva <> 0)";
592 }
593 $sql .= " ORDER BY e.rowid";
594
595 dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
596 $resql = $db->query($sql);
597 if ($resql) {
598 $company_id = -1;
599 $oldrowid = '';
600 while ($assoc = $db->fetch_array($resql)) {
601 if (!isset($list[$assoc['company_id']]['totalht'])) {
602 $list[$assoc['company_id']]['totalht'] = 0;
603 }
604 if (!isset($list[$assoc['company_id']]['vat'])) {
605 $list[$assoc['company_id']]['vat'] = 0;
606 }
607 if (!isset($list[$assoc['company_id']]['localtax1'])) {
608 $list[$assoc['company_id']]['localtax1'] = 0;
609 }
610 if (!isset($list[$assoc['company_id']]['localtax2'])) {
611 $list[$assoc['company_id']]['localtax2'] = 0;
612 }
613
614 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
615 $oldrowid = $assoc['rowid'];
616 $list[$assoc['company_id']]['totalht'] += (float) $assoc['total_ht'];
617 $list[$assoc['company_id']]['vat'] += (float) $assoc['total_vat'];
618 $list[$assoc['company_id']]['localtax1'] += (float) $assoc['total_localtax1'];
619 $list[$assoc['company_id']]['localtax2'] += (float) $assoc['total_localtax2'];
620 }
621
622 $list[$assoc['company_id']]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
623 $list[$assoc['company_id']]['dtype'][] = 'ExpenseReportPayment';
624 $list[$assoc['company_id']]['datef'][] = (int) $assoc['datef'];
625
626 $list[$assoc['company_id']]['company_name'][] = '';
627 $list[$assoc['company_id']]['company_id'][] = 0;
628 $list[$assoc['company_id']]['company_alias'][] = '';
629 $list[$assoc['company_id']]['company_email'][] = '';
630 $list[$assoc['company_id']]['company_tva_intra'][] = '';
631 $list[$assoc['company_id']]['company_client'][] = 0;
632 $list[$assoc['company_id']]['company_fournisseur'][] = 0;
633 $list[$assoc['company_id']]['company_customer_code'][] = '';
634 $list[$assoc['company_id']]['company_supplier_code'][] = '';
635 $list[$assoc['company_id']]['company_customer_accounting_code'][] = '';
636 $list[$assoc['company_id']]['company_supplier_accounting_code'][] = '';
637 $list[$assoc['company_id']]['company_status'][] = 0;
638
639 $list[$assoc['company_id']]['user_id'][] = (int) $assoc['fk_user_author'];
640 $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
641 $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
642 $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
643
644 $list[$assoc['company_id']]['facid'][] = (int) $assoc['facid'];
645 $list[$assoc['company_id']]['facnum'][] = (string) $assoc['facnum'];
646 $list[$assoc['company_id']]['type'][] = (int) $assoc['type'];
647 $list[$assoc['company_id']]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
648 $list[$assoc['company_id']]['descr'][] = (string) $assoc['descr'];
649
650 $list[$assoc['company_id']]['totalht_list'][] = (float) $assoc['total_ht'];
651 $list[$assoc['company_id']]['vat_list'][] = (float) $assoc['total_vat'];
652 $list[$assoc['company_id']]['localtax1_list'][] = (float) $assoc['total_localtax1'];
653 $list[$assoc['company_id']]['localtax2_list'][] = (float) $assoc['total_localtax2'];
654
655 $list[$assoc['company_id']]['pid'][] = (int) $assoc['pid'];
656 $list[$assoc['company_id']]['pref'][] = (string) $assoc['pref'];
657 $list[$assoc['company_id']]['ptype'][] = 'ExpenseReportPayment';
658
659 $list[$assoc['company_id']]['payment_id'][] = (int) $assoc['payment_id'];
660 $list[$assoc['company_id']]['payment_ref'][] = (string) $assoc['payment_ref'];
661 $list[$assoc['company_id']]['payment_amount'][] = (float) $assoc['payment_amount'];
662
663 $company_id = $assoc['company_id'];
664 }
665 } else {
666 dol_print_error($db);
667 return -3;
668 }
669 }
670
671 return $list;
672}
673
674
691function tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
692{
693 global $conf, $hookmanager;
694 $hookmanager->initHooks(array('taxvatlist'));
695
696 // If we use date_start and date_end, we must not use $y, $m, $q
697 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
698 dol_print_error(null, 'Bad value of input parameter for tax_by_rate');
699 }
700
701 $list = array();
702
703 if ($direction == 'sell') {
704 $invoicetable = 'facture';
705 $invoicedettable = 'facturedet';
706 $fk_facture = 'fk_facture';
707 $fk_facture2 = 'fk_facture';
708 $fk_payment = 'fk_paiement';
709 $total_tva = 'total_tva';
710 $paymenttable = 'paiement';
711 $paymentfacturetable = 'paiement_facture';
712 $invoicefieldref = 'ref';
713 } else {
714 $invoicetable = 'facture_fourn';
715 $invoicedettable = 'facture_fourn_det';
716 $fk_facture = 'fk_facture_fourn';
717 $fk_facture2 = 'fk_facturefourn';
718 $fk_payment = 'fk_paiementfourn';
719 $total_tva = 'tva';
720 $paymenttable = 'paiementfourn';
721 $paymentfacturetable = 'paiementfourn_facturefourn';
722 $invoicefieldref = 'ref';
723 }
724
725 if (strpos($type, 'localtax') === 0) {
726 $f_rate = $type.'_tx';
727 } else {
728 $f_rate = 'tva_tx';
729 }
730
731 $total_localtax1 = 'total_localtax1';
732 $total_localtax2 = 'total_localtax2';
733
734
735 // CASE OF PRODUCTS/GOODS
736
737 // Define sql request
738 $sql = '';
739 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_PRODUCT') == 'invoice')
740 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_PRODUCT') == 'invoice')) {
741 // Count on delivery date (use invoice date as delivery is unknown)
742 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
743 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
744 $sql .= " d.date_start as date_start, d.date_end as date_end,";
745 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
746 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
747 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
748 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
749 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
750 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
751 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount,";
752 $sql .= " '' as datep";
753 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f";
754 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
755 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d ON d.".$db->sanitize($fk_facture)." = f.rowid";
756 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
757 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
758 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Validated or paid (partially or completely)
759 if ($direction == 'buy') {
760 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
761 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
762 } else {
763 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
764 }
765 } else {
766 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
767 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
768 } else {
769 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
770 }
771 }
772 if ($y && $m) {
773 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
774 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
775 } elseif ($y) {
776 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
777 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
778 }
779 if ($q) {
780 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
781 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
782 }
783 if ($date_start && $date_end) {
784 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
785 }
786 $sql .= " AND (d.product_type = 0"; // Limit to products
787 $sql .= " AND d.date_start IS NULL AND d.date_end IS NULL)"; // enhance detection of products
788 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
789 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
790 }
791 // Add SQL restrictions from hooks (context taxvatlist), e.g. a deposit pivot date restricting deposits by their date
792 $parameters = array('invoicealias' => 'f', 'issupplier' => ($direction == 'buy' ? 1 : 0), 'datefield' => 'datef');
793 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
794 $sql .= $hookmanager->resPrint;
795 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture);
796 } else {
797 // Count on payments date
798 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
799 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
800 $sql .= " d.date_start as date_start, d.date_end as date_end,";
801 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
802 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
803 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
804 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
805 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
806 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
807 $sql .= " pf.".$db->sanitize($fk_payment)." as payment_id, pf.amount as payment_amount,";
808 $sql .= " pa.datep as datep, pa.ref as payment_ref";
809 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f";
810 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($paymentfacturetable)." as pf ON pf.".$db->sanitize($fk_facture2)." = f.rowid";
811 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($paymenttable)." as pa ON pa.rowid = pf.".$db->sanitize($fk_payment);
812 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
813 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d ON d.".$db->sanitize($fk_facture)." = f.rowid";
814 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
815 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
816 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Paid (partially or completely)
817 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
818 if ($y && $m) {
819 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
820 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
821 } elseif ($y) {
822 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
823 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
824 }
825 if ($q) {
826 $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
827 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
828 }
829 if ($date_start && $date_end) {
830 $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
831 }
832 $sql .= " AND (d.product_type = 0"; // Limit to products
833 $sql .= " AND d.date_start IS NULL AND d.date_end IS NULL)"; // enhance detection of products
834 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
835 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
836 }
837 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture).", pf.rowid";
838 }
839
840 dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
841
842 $resql = $db->query($sql);
843 if ($resql) {
844 $rate = -1;
845 $oldrowid = '';
846 while ($assoc = $db->fetch_array($resql)) {
847 $rate_key = $assoc['rate'];
848 if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
849 $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
850 }
851
852 // Code to avoid warnings when array entry not defined
853 if (!isset($list[$rate_key]['totalht'])) {
854 $list[$rate_key]['totalht'] = 0;
855 }
856 if (!isset($list[$rate_key]['vat'])) {
857 $list[$rate_key]['vat'] = 0;
858 }
859 if (!isset($list[$rate_key]['localtax1'])) {
860 $list[$rate_key]['localtax1'] = 0;
861 }
862 if (!isset($list[$rate_key]['localtax2'])) {
863 $list[$rate_key]['localtax2'] = 0;
864 }
865
866 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
867 $oldrowid = $assoc['rowid'];
868 $list[$rate_key]['totalht'] += (float) $assoc['total_ht'];
869 $list[$rate_key]['vat'] += (float) $assoc['total_vat'];
870 $list[$rate_key]['localtax1'] += (float) $assoc['total_localtax1'];
871 $list[$rate_key]['localtax2'] += (float) $assoc['total_localtax2'];
872 }
873 $list[$rate_key]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
874 $list[$rate_key]['dtype'][] = (int) $assoc['dtype'];
875 $list[$rate_key]['datef'][] = $db->jdate($assoc['datef']);
876 $list[$rate_key]['datep'][] = $db->jdate($assoc['datep']);
877
878 $list[$rate_key]['company_name'][] = (string) $assoc['company_name'];
879 $list[$rate_key]['company_id'][] = (int) $assoc['company_id'];
880 $list[$rate_key]['company_alias'][] = (string) $assoc['company_alias'];
881 $list[$rate_key]['company_email'][] = (string) $assoc['company_email'];
882 $list[$rate_key]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
883 $list[$rate_key]['company_client'][] = (int) $assoc['company_client'];
884 $list[$rate_key]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
885 $list[$rate_key]['company_customer_code'][] = (string) $assoc['company_customer_code'];
886 $list[$rate_key]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
887 $list[$rate_key]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
888 $list[$rate_key]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
889 $list[$rate_key]['company_status'][] = (int) $assoc['company_status'];
890
891 $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
892 $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
893
894 $list[$rate_key]['facid'][] = (int) $assoc['facid'];
895 $list[$rate_key]['facnum'][] = (string) $assoc['facnum'];
896 $list[$rate_key]['type'][] = (int) $assoc['type'];
897 $list[$rate_key]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
898 $list[$rate_key]['descr'][] = (string) $assoc['descr'];
899
900 $list[$rate_key]['totalht_list'][] = (float) $assoc['total_ht'];
901 $list[$rate_key]['vat_list'][] = (float) $assoc['total_vat'];
902 $list[$rate_key]['localtax1_list'][] = (float) $assoc['total_localtax1'];
903 $list[$rate_key]['localtax2_list'][] = (float) $assoc['total_localtax2'];
904
905 $list[$rate_key]['pid'][] = (int) $assoc['pid'];
906 $list[$rate_key]['pref'][] = (string) $assoc['pref'];
907 $list[$rate_key]['ptype'][] = (int) $assoc['ptype'];
908
909 $list[$rate_key]['payment_id'][] = (int) $assoc['payment_id'];
910 $list[$rate_key]['payment_ref'][] = (string) $assoc['payment_ref'];
911 $list[$rate_key]['payment_amount'][] = (float) $assoc['payment_amount'];
912
913 $rate = $assoc['rate'];
914 }
915 } else {
916 dol_print_error($db);
917 return -3;
918 }
919
920 // CASE OF SERVICES
921
922 // Define sql request
923 $sql = '';
924 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_SERVICE') == 'invoice')
925 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_SERVICE') == 'invoice')) {
926 // Count on invoice date
927 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
928 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
929 $sql .= " d.date_start as date_start, d.date_end as date_end,";
930 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
931 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
932 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
933 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
934 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
935 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
936 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
937 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f";
938 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
939 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d ON d.".$db->sanitize($fk_facture)." = f.rowid";
940 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
941 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
942 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Validated or paid (partially or completely)
943 if ($direction == 'buy') {
944 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
945 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
946 } else {
947 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
948 }
949 } else {
950 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
951 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_SITUATION . ")";
952 } else {
953 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
954 }
955 }
956 if ($y && $m) {
957 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
958 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
959 } elseif ($y) {
960 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
961 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
962 }
963 if ($q) {
964 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
965 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
966 }
967 if ($date_start && $date_end) {
968 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
969 }
970 $sql .= " AND (d.product_type = 1"; // Limit to services
971 $sql .= " OR d.date_start IS NOT NULL OR d.date_end IS NOT NULL)"; // enhance detection of service
972 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
973 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
974 }
975 // Add SQL restrictions from hooks (context taxvatlist), e.g. a deposit pivot date restricting deposits by their date
976 $parameters = array('invoicealias' => 'f', 'issupplier' => ($direction == 'buy' ? 1 : 0), 'datefield' => 'datef');
977 $reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters); // Note that $action and $object may have been modified by some hooks
978 $sql .= $hookmanager->resPrint;
979 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture);
980 } else {
981 // Count on payments date
982 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$db->sanitize($fk_facture)." as facid, d.".$db->sanitize($f_rate)." as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$db->sanitize($total_tva)." as total_vat, d.description as descr,";
983 $sql .= " d.".$db->sanitize($total_localtax1)." as total_localtax1, d.".$db->sanitize($total_localtax2)." as total_localtax2, ";
984 $sql .= " d.date_start as date_start, d.date_end as date_end,";
985 $sql .= " f.".$db->sanitize($invoicefieldref)." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
986 $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
987 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
988 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
989 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
990 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
991 $sql .= " pf.".$db->sanitize($fk_payment)." as payment_id, pf.amount as payment_amount,";
992 $sql .= " pa.datep as datep, pa.ref as payment_ref";
993 $sql .= " FROM ".MAIN_DB_PREFIX.$db->sanitize($invoicetable)." as f";
994 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($paymentfacturetable)." as pf ON pf.".$db->sanitize($fk_facture2)." = f.rowid";
995 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($paymenttable)." as pa ON pa.rowid = pf.".$db->sanitize($fk_payment);
996 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
997 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$db->sanitize($invoicedettable)." as d ON d.".$db->sanitize($fk_facture)." = f.rowid";
998 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
999 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
1000 $sql .= " AND f.fk_statut IN (".Facture::STATUS_VALIDATED.", ".Facture::STATUS_CLOSED.")"; // Paid (partially or completely)
1001 $sql .= " AND f.type IN (" . Facture::TYPE_STANDARD . ", " . Facture::TYPE_REPLACEMENT . ", " . Facture::TYPE_CREDIT_NOTE . ", " . Facture::TYPE_DEPOSIT . ", " . Facture::TYPE_SITUATION . ")";
1002 if ($y && $m) {
1003 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
1004 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
1005 } elseif ($y) {
1006 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
1007 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
1008 }
1009 if ($q) {
1010 $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
1011 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
1012 }
1013 if ($date_start && $date_end) {
1014 $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
1015 }
1016 $sql .= " AND (d.product_type = 1"; // Limit to services
1017 $sql .= " OR d.date_start IS NOT NULL OR d.date_end IS NOT NULL)"; // enhance detection of service
1018 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
1019 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.".$db->sanitize($total_tva)." <> 0)";
1020 }
1021 $sql .= " ORDER BY d.rowid, d.".$db->sanitize($fk_facture).", pf.rowid";
1022 }
1023
1024 dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
1025 $resql = $db->query($sql);
1026 if ($resql) {
1027 $rate = -1;
1028 $oldrowid = '';
1029 while ($assoc = $db->fetch_array($resql)) {
1030 $rate_key = $assoc['rate'];
1031 if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
1032 $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
1033 }
1034
1035 // Code to avoid warnings when array entry not defined
1036 if (!isset($list[$rate_key]['totalht'])) {
1037 $list[$rate_key]['totalht'] = 0;
1038 }
1039 if (!isset($list[$rate_key]['vat'])) {
1040 $list[$rate_key]['vat'] = 0;
1041 }
1042 if (!isset($list[$rate_key]['localtax1'])) {
1043 $list[$rate_key]['localtax1'] = 0;
1044 }
1045 if (!isset($list[$rate_key]['localtax2'])) {
1046 $list[$rate_key]['localtax2'] = 0;
1047 }
1048
1049 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
1050 $oldrowid = $assoc['rowid'];
1051 $list[$rate_key]['totalht'] += (float) $assoc['total_ht'];
1052 $list[$rate_key]['vat'] += (float) $assoc['total_vat'];
1053 $list[$rate_key]['localtax1'] += (float) $assoc['total_localtax1'];
1054 $list[$rate_key]['localtax2'] += (float) $assoc['total_localtax2'];
1055 }
1056 $list[$rate_key]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
1057 $list[$rate_key]['dtype'][] = (int) $assoc['dtype'];
1058 $list[$rate_key]['datef'][] = $db->jdate($assoc['datef']);
1059 $list[$rate_key]['datep'][] = $db->jdate($assoc['datep']);
1060
1061 $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
1062 $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
1063
1064 $list[$rate_key]['company_name'][] = (string) $assoc['company_name'];
1065 $list[$rate_key]['company_id'][] = (int) $assoc['company_id'];
1066 $list[$rate_key]['company_alias'][] = (string) $assoc['company_alias'];
1067 $list[$rate_key]['company_email'][] = (string) $assoc['company_email'];
1068 $list[$rate_key]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
1069 $list[$rate_key]['company_client'][] = (int) $assoc['company_client'];
1070 $list[$rate_key]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
1071 $list[$rate_key]['company_customer_code'][] = (string) $assoc['company_customer_code'];
1072 $list[$rate_key]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
1073 $list[$rate_key]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
1074 $list[$rate_key]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
1075 $list[$rate_key]['company_status'][] = (int) $assoc['company_status'];
1076
1077 $list[$rate_key]['facid'][] = (int) $assoc['facid'];
1078 $list[$rate_key]['facnum'][] = (string) $assoc['facnum'];
1079 $list[$rate_key]['type'][] = (int) $assoc['type'];
1080 $list[$rate_key]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
1081 $list[$rate_key]['descr'][] = (string) $assoc['descr'];
1082
1083 $list[$rate_key]['totalht_list'][] = (float) $assoc['total_ht'];
1084 $list[$rate_key]['vat_list'][] = (float) $assoc['total_vat'];
1085 $list[$rate_key]['localtax1_list'][] = (float) $assoc['total_localtax1'];
1086 $list[$rate_key]['localtax2_list'][] = (float) $assoc['total_localtax2'];
1087
1088 $list[$rate_key]['pid'][] = (int) $assoc['pid'];
1089 $list[$rate_key]['pref'][] = (string) $assoc['pref'];
1090 $list[$rate_key]['ptype'][] = (int) $assoc['ptype'];
1091
1092 $list[$rate_key]['payment_id'][] = (int) $assoc['payment_id'];
1093 $list[$rate_key]['payment_ref'][] = (string) $assoc['payment_ref'];
1094 $list[$rate_key]['payment_amount'][] = (float) $assoc['payment_amount'];
1095
1096 $rate = $assoc['rate'];
1097 }
1098 } else {
1099 dol_print_error($db);
1100 return -3;
1101 }
1102
1103 // CASE OF EXPENSE REPORT
1104
1105 if ($direction == 'buy') { // buy only for expense reports
1106 // Define sql request
1107 $sql = '';
1108
1109 // Count on payments date
1110 $sql = "SELECT d.rowid, d.product_type as dtype, e.rowid as facid, d.".$db->sanitize($f_rate)." as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.total_tva as total_vat, e.note_private as descr,";
1111 $sql .= " d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
1112 $sql .= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
1113 $sql .= " e.ref as facnum, e.ref as pref, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
1114 $sql .= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid";
1115 $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as e";
1116 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid";
1117 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid";
1118 $sql .= " WHERE e.entity = ".((int) $conf->entity);
1119 $sql .= " AND e.fk_statut IN (" . ExpenseReport::STATUS_CLOSED . ")";
1120 if ($y && $m) {
1121 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
1122 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
1123 } elseif ($y) {
1124 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
1125 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
1126 }
1127 if ($q) {
1128 $sql .= " AND p.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
1129 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
1130 }
1131 if ($date_start && $date_end) {
1132 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
1133 }
1134 $sql .= " AND (d.product_type = -1";
1135 $sql .= " OR e.date_debut IS NOT NULL OR e.date_fin IS NOT NULL)"; // enhance detection of service
1136 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
1137 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.total_tva <> 0)";
1138 }
1139 $sql .= " ORDER BY e.rowid";
1140
1141 dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
1142 $resql = $db->query($sql);
1143 if ($resql) {
1144 $rate = -1;
1145 $oldrowid = '';
1146 while ($assoc = $db->fetch_array($resql)) {
1147 $rate_key = $assoc['rate'];
1148 if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
1149 $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
1150 }
1151
1152 // Code to avoid warnings when array entry not defined
1153 if (!isset($list[$rate_key]['totalht'])) {
1154 $list[$rate_key]['totalht'] = 0;
1155 }
1156 if (!isset($list[$rate_key]['vat'])) {
1157 $list[$rate_key]['vat'] = 0;
1158 }
1159 if (!isset($list[$rate_key]['localtax1'])) {
1160 $list[$rate_key]['localtax1'] = 0;
1161 }
1162 if (!isset($list[$rate_key]['localtax2'])) {
1163 $list[$rate_key]['localtax2'] = 0;
1164 }
1165
1166 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
1167 $oldrowid = $assoc['rowid'];
1168 $list[$rate_key]['totalht'] += (float) $assoc['total_ht'];
1169 $list[$rate_key]['vat'] += (float) $assoc['total_vat'];
1170 $list[$rate_key]['localtax1'] += (float) $assoc['total_localtax1'];
1171 $list[$rate_key]['localtax2'] += (float) $assoc['total_localtax2'];
1172 }
1173
1174 $list[$rate_key]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
1175 $list[$rate_key]['dtype'][] = 'ExpenseReportPayment';
1176 $list[$rate_key]['datef'][] = (int) $assoc['datef'];
1177 $list[$rate_key]['company_name'][] = '';
1178 $list[$rate_key]['company_id'][] = 0;
1179 $list[$rate_key]['user_id'][] = (int) $assoc['fk_user_author'];
1180 $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
1181 $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
1182
1183 $list[$rate_key]['facid'][] = (int) $assoc['facid'];
1184 $list[$rate_key]['facnum'][] = (string) $assoc['facnum'];
1185 $list[$rate_key]['type'][] = (int) $assoc['type'];
1186 $list[$rate_key]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
1187 $list[$rate_key]['descr'][] = (string) $assoc['descr'];
1188
1189 $list[$rate_key]['totalht_list'][] = (float) $assoc['total_ht'];
1190 $list[$rate_key]['vat_list'][] = (float) $assoc['total_vat'];
1191 $list[$rate_key]['localtax1_list'][] = (float) $assoc['total_localtax1'];
1192 $list[$rate_key]['localtax2_list'][] = (float) $assoc['total_localtax2'];
1193
1194 $list[$rate_key]['pid'][] = (int) $assoc['pid'];
1195 $list[$rate_key]['pref'][] = (string) $assoc['pref'];
1196 $list[$rate_key]['ptype'][] = 'ExpenseReportPayment';
1197
1198 $list[$rate_key]['payment_id'][] = (int) $assoc['payment_id'];
1199 $list[$rate_key]['payment_ref'][] = (string) $assoc['payment_ref'];
1200 $list[$rate_key]['payment_amount'][] = (float) $assoc['payment_amount'];
1201
1202 $rate = $assoc['rate'];
1203 }
1204 } else {
1205 dol_print_error($db);
1206 return -3;
1207 }
1208 }
1209
1210 return $list;
1211}
$object
if(! $sortfield) if(! $sortorder) $object
Definition account.php:100
ChargeSociales
Class for managing the social charges.
Definition chargesociales.class.php:36
ExpenseReport\STATUS_CLOSED
const STATUS_CLOSED
Classified paid.
Definition expensereport.class.php:293
Facture\TYPE_REPLACEMENT
const TYPE_REPLACEMENT
Replacement invoice.
Definition facture.class.php:413
Facture\TYPE_STANDARD
const TYPE_STANDARD
Standard invoice.
Definition facture.class.php:408
Facture\TYPE_SITUATION
const TYPE_SITUATION
Situation invoice.
Definition facture.class.php:433
Facture\TYPE_DEPOSIT
const TYPE_DEPOSIT
Deposit invoice.
Definition facture.class.php:423
Facture\TYPE_CREDIT_NOTE
const TYPE_CREDIT_NOTE
Credit note invoice.
Definition facture.class.php:418
Facture\STATUS_CLOSED
const STATUS_CLOSED
Classified paid.
Definition facture.class.php:461
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
dol_dir_list
dol_dir_list($utf8_path, $types="all", $recursive=0, $filter="", $excludefilter=null, $sortcriteria="name", $sortorder=SORT_ASC, $mode=0, $nohook=0, $relativename="", $donotfollowsymlinks=0, $nbsecondsold=0)
Scan a directory and return a list of files/directories.
Definition files.lib.php:65
$date_start
$date_start
Variables from include:
Definition fiscalyear_card.php:73
dol_sanitizeFileName
dol_sanitizeFileName($str, $newstr='_', $unaccent=1, $includequotes=0, $allowdash=0)
Clean a string to use it as a file name.
Definition functions.lib.php:2020
complete_head_from_modules
complete_head_from_modules($conf, $langs, $object, &$head, &$h, $type, $mode='add', $filterorigmodule='')
Complete or removed entries into a head array (used to build tabs).
Definition functions.lib.php:9559
getDolGlobalString
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
Definition functions.lib.php:324
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2547
tax_by_rate
tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m=0)
Gets Tax to collect for the given year (and given quarter or month) The function gets the Tax in spli...
Definition tax.lib.php:691
tax_by_thirdparty
tax_by_thirdparty($type, $db, $y, $date_start, $date_end, $modetax, $direction, $m=0, $q=0)
Look for collectable VAT clients in the chosen year (and month)
Definition tax.lib.php:118
tax_prepare_head
tax_prepare_head(ChargeSociales $object)
Prepare array with list of tabs.
Definition tax.lib.php:44
Generated on Thu Oct 1 2026 01:00:23 for dolibarr by Doxygen 1.11.0