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