dolibarr 21.0.0-beta
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 Frédéric France <frederic.france@free.fr>
11 * Copyright (C) 2024 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
40function tax_prepare_head(ChargeSociales $object)
41{
42 global $db, $langs, $conf, $user;
43
44 $h = 0;
45 $head = array();
46
47 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/card.php?id='.$object->id;
48 $head[$h][1] = $langs->trans('SocialContribution');
49 $head[$h][2] = 'card';
50 $h++;
51
52 // Show more tabs from modules
53 // Entries must be declared in modules descriptor with line
54 // $this->tabs = array('entity:+tabname:Title:@mymodule:/mymodule/mypage.php?id=__ID__'); to add new tab
55 // $this->tabs = array('entity:-tabname); to remove a tab
56 complete_head_from_modules($conf, $langs, $object, $head, $h, 'tax');
57
58 require_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php';
59 require_once DOL_DOCUMENT_ROOT.'/core/class/link.class.php';
60 $upload_dir = $conf->tax->dir_output."/".dol_sanitizeFileName($object->ref);
61 $nbFiles = count(dol_dir_list($upload_dir, 'files', 0, '', '(\.meta|_preview.*\.png)$'));
62 $nbLinks = Link::count($db, $object->element, $object->id);
63 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/document.php?id='.$object->id;
64 $head[$h][1] = $langs->trans("Documents");
65 if (($nbFiles + $nbLinks) > 0) {
66 $head[$h][1] .= '<span class="badge marginleftonlyshort">'.($nbFiles + $nbLinks).'</span>';
67 }
68 $head[$h][2] = 'documents';
69 $h++;
70
71
72 $nbNote = 0;
73 if (!empty($object->note_private)) {
74 $nbNote++;
75 }
76 if (!empty($object->note_public)) {
77 $nbNote++;
78 }
79 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/note.php?id='.$object->id;
80 $head[$h][1] = $langs->trans('Notes');
81 if ($nbNote > 0) {
82 $head[$h][1] .= (!getDolGlobalString('MAIN_OPTIMIZEFORTEXTBROWSER') ? '<span class="badge marginleftonlyshort">'.$nbNote.'</span>' : '');
83 }
84 $head[$h][2] = 'note';
85 $h++;
86
87
88 $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/info.php?id='.$object->id;
89 $head[$h][1] = $langs->trans("Info");
90 $head[$h][2] = 'info';
91 $h++;
92
93
94 complete_head_from_modules($conf, $langs, $object, $head, $h, 'tax', 'remove');
95
96 return $head;
97}
98
99
114function tax_by_thirdparty($type, $db, $y, $date_start, $date_end, $modetax, $direction, $m = 0, $q = 0)
115{
116 global $conf;
117
118 // If we use date_start and date_end, we must not use $y, $m, $q
119 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
120 dol_print_error(null, 'Bad value of input parameter for tax_by_thirdparty');
121 }
122
123 $list = array();
124 if ($direction == 'sell') {
125 $invoicetable = 'facture';
126 $invoicedettable = 'facturedet';
127 $fk_facture = 'fk_facture';
128 $fk_facture2 = 'fk_facture';
129 $fk_payment = 'fk_paiement';
130 $total_tva = 'total_tva';
131 $paymenttable = 'paiement';
132 $paymentfacturetable = 'paiement_facture';
133 $invoicefieldref = 'ref';
134 } elseif ($direction == 'buy') {
135 $invoicetable = 'facture_fourn';
136 $invoicedettable = 'facture_fourn_det';
137 $fk_facture = 'fk_facture_fourn';
138 $fk_facture2 = 'fk_facturefourn';
139 $fk_payment = 'fk_paiementfourn';
140 $total_tva = 'tva';
141 $paymenttable = 'paiementfourn';
142 $paymentfacturetable = 'paiementfourn_facturefourn';
143 $invoicefieldref = 'ref';
144 } else {
145 dol_print_error(null, 'Invalid "direction" - must be buy or sell - found ' . $direction);
146 return -2;
147 }
148
149 if (strpos($type, 'localtax') === 0) {
150 $f_rate = $type.'_tx';
151 } else {
152 $f_rate = 'tva_tx';
153 }
154
155 $total_localtax1 = 'total_localtax1';
156 $total_localtax2 = 'total_localtax2';
157
158
159 // CAS DES BIENS/PRODUITS
160
161 // Define sql request
162 $sql = '';
163 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_PRODUCT') == 'invoice')
164 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_PRODUCT') == 'invoice')) {
165 // Count on delivery date (use invoice date as delivery is unknown)
166 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
167 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
168 $sql .= " d.date_start as date_start, d.date_end as date_end,";
169 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
170 $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,";
171 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
172 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
173 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
174 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
175 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
176 $sql .= " ,'' as datep";
177 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
178 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
179 $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
180 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
181 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
182 $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
183 if ($direction == 'buy') {
184 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
185 $sql .= " AND f.type IN (0,1,2,5)";
186 } else {
187 $sql .= " AND f.type IN (0,1,2,3,5)";
188 }
189 } else {
190 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
191 $sql .= " AND f.type IN (0,1,2,5)";
192 } else {
193 $sql .= " AND f.type IN (0,1,2,3,5)";
194 }
195 }
196 $sql .= " AND f.rowid = d.".$fk_facture;
197 $sql .= " AND s.rowid = f.fk_soc";
198 if ($y && $m) {
199 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
200 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
201 } elseif ($y) {
202 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
203 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
204 }
205 if ($q) {
206 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
207 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
208 }
209 if ($date_start && $date_end) {
210 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
211 }
212 $sql .= " AND (d.product_type = 0"; // Limit to products
213 $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
214 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
215 $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
216 }
217 $sql .= " ORDER BY d.rowid, d.".$fk_facture;
218 } else {
219 // Count on payments date
220 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
221 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
222 $sql .= " d.date_start as date_start, d.date_end as date_end,";
223 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
224 $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,";
225 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
226 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
227 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
228 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
229 $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
230 $sql .= " pa.datep as datep, pa.ref as payment_ref";
231 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
232 $sql .= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
233 $sql .= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
234 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
235 $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
236 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
237 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
238 $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
239 if ($direction == 'buy') {
240 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
241 $sql .= " AND f.type IN (0,1,2,5)";
242 } else {
243 $sql .= " AND f.type IN (0,1,2,3,5)";
244 }
245 } else {
246 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
247 $sql .= " AND f.type IN (0,1,2,5)";
248 } else {
249 $sql .= " AND f.type IN (0,1,2,3,5)";
250 }
251 }
252 $sql .= " AND f.rowid = d.".$fk_facture;
253 $sql .= " AND s.rowid = f.fk_soc";
254 $sql .= " AND pf.".$fk_facture2." = f.rowid";
255 $sql .= " AND pa.rowid = pf.".$fk_payment;
256 if ($y && $m) {
257 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
258 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
259 } elseif ($y) {
260 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
261 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
262 }
263 if ($q) {
264 $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
265 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
266 }
267 if ($date_start && $date_end) {
268 $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
269 }
270 $sql .= " AND (d.product_type = 0"; // Limit to products
271 $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
272 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
273 $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
274 }
275 $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
276 }
277
278 if (!$sql) {
279 return -1;
280 }
281 if ($sql == 'TODO') {
282 return -2;
283 }
284 if ($sql != 'TODO') {
285 dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
286
287 $resql = $db->query($sql);
288 if ($resql) {
289 $company_id = -1;
290 $oldrowid = '';
291 while ($assoc = $db->fetch_array($resql)) {
292 if (!isset($list[$assoc['company_id']]['totalht'])) {
293 $list[$assoc['company_id']]['totalht'] = 0;
294 }
295 if (!isset($list[$assoc['company_id']]['vat'])) {
296 $list[$assoc['company_id']]['vat'] = 0;
297 }
298 if (!isset($list[$assoc['company_id']]['localtax1'])) {
299 $list[$assoc['company_id']]['localtax1'] = 0;
300 }
301 if (!isset($list[$assoc['company_id']]['localtax2'])) {
302 $list[$assoc['company_id']]['localtax2'] = 0;
303 }
304
305 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
306 $oldrowid = $assoc['rowid'];
307 $list[$assoc['company_id']]['totalht'] += (float) $assoc['total_ht'];
308 $list[$assoc['company_id']]['vat'] += (float) $assoc['total_vat'];
309 $list[$assoc['company_id']]['localtax1'] += (float) $assoc['total_localtax1'];
310 $list[$assoc['company_id']]['localtax2'] += (float) $assoc['total_localtax2'];
311 }
312
313 $list[$assoc['company_id']]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
314 $list[$assoc['company_id']]['dtype'][] = (int) $assoc['dtype'];
315 $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
316 $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
317
318 $list[$assoc['company_id']]['company_name'][] = (string) $assoc['company_name'];
319 $list[$assoc['company_id']]['company_id'][] = (int) $assoc['company_id'];
320 $list[$assoc['company_id']]['company_alias'][] = (string) $assoc['company_alias'];
321 $list[$assoc['company_id']]['company_email'][] = (string) $assoc['company_email'];
322 $list[$assoc['company_id']]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
323 $list[$assoc['company_id']]['company_client'][] = (int) $assoc['company_client'];
324 $list[$assoc['company_id']]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
325 $list[$assoc['company_id']]['company_customer_code'][] = (string) $assoc['company_customer_code'];
326 $list[$assoc['company_id']]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
327 $list[$assoc['company_id']]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
328 $list[$assoc['company_id']]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
329 $list[$assoc['company_id']]['company_status'][] = (int) $assoc['company_status'];
330
331 $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
332 $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
333 $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
334
335 $list[$assoc['company_id']]['facid'][] = (int) $assoc['facid'];
336 $list[$assoc['company_id']]['facnum'][] = (string) $assoc['facnum'];
337 $list[$assoc['company_id']]['type'][] = (int) $assoc['type'];
338 $list[$assoc['company_id']]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
339 $list[$assoc['company_id']]['descr'][] = (string) $assoc['descr'];
340
341 $list[$assoc['company_id']]['totalht_list'][] = (float) $assoc['total_ht'];
342 $list[$assoc['company_id']]['vat_list'][] = (float) $assoc['total_vat'];
343 $list[$assoc['company_id']]['localtax1_list'][] = (float) $assoc['total_localtax1'];
344 $list[$assoc['company_id']]['localtax2_list'][] = (float) $assoc['total_localtax2'];
345
346 $list[$assoc['company_id']]['pid'][] = (int) $assoc['pid'];
347 $list[$assoc['company_id']]['pref'][] = (string) $assoc['pref'];
348 $list[$assoc['company_id']]['ptype'][] = (int) $assoc['ptype'];
349
350 $list[$assoc['company_id']]['payment_id'][] = (int) $assoc['payment_id'];
351 $list[$assoc['company_id']]['payment_amount'][] = (float) $assoc['payment_amount'];
352
353 $company_id = $assoc['company_id'];
354 }
355 } else {
356 dol_print_error($db);
357 return -3;
358 }
359 }
360
361
362 // CAS DES SERVICES
363
364 // Define sql request
365 $sql = '';
366 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_SERVICE') == 'invoice')
367 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_SERVICE') == 'invoice')) {
368 // Count on invoice date
369 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
370 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
371 $sql .= " d.date_start as date_start, d.date_end as date_end,";
372 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
373 $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,";
374 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
375 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
376 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
377 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
378 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
379 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
380 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
381 $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
382 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
383 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
384 $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
385 if ($direction == 'buy') {
386 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
387 $sql .= " AND f.type IN (0,1,2,5)";
388 } else {
389 $sql .= " AND f.type IN (0,1,2,3,5)";
390 }
391 } else {
392 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
393 $sql .= " AND f.type IN (0,1,2,5)";
394 } else {
395 $sql .= " AND f.type IN (0,1,2,3,5)";
396 }
397 }
398 $sql .= " AND f.rowid = d.".$fk_facture;
399 $sql .= " AND s.rowid = f.fk_soc";
400 if ($y && $m) {
401 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
402 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
403 } elseif ($y) {
404 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
405 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
406 }
407 if ($q) {
408 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
409 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
410 }
411 if ($date_start && $date_end) {
412 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
413 }
414 $sql .= " AND (d.product_type = 1"; // Limit to services
415 $sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
416 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
417 $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
418 }
419 $sql .= " ORDER BY d.rowid, d.".$fk_facture;
420 } else {
421 // Count on payments date
422 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
423 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
424 $sql .= " d.date_start as date_start, d.date_end as date_end,";
425 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
426 $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,";
427 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
428 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
429 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
430 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
431 $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
432 $sql .= " pa.datep as datep, pa.ref as payment_ref";
433 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
434 $sql .= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
435 $sql .= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
436 $sql .= " ".MAIN_DB_PREFIX."societe as s,";
437 $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
438 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
439 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
440 $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
441 if ($direction == 'buy') {
442 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
443 $sql .= " AND f.type IN (0,1,2,5)";
444 } else {
445 $sql .= " AND f.type IN (0,1,2,3,5)";
446 }
447 } else {
448 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
449 $sql .= " AND f.type IN (0,1,2,5)";
450 } else {
451 $sql .= " AND f.type IN (0,1,2,3,5)";
452 }
453 }
454 $sql .= " AND f.rowid = d.".$fk_facture;
455 $sql .= " AND s.rowid = f.fk_soc";
456 $sql .= " AND pf.".$fk_facture2." = f.rowid";
457 $sql .= " AND pa.rowid = pf.".$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.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
476 }
477 $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
478 }
479
480 if (!$sql) {
481 dol_syslog("Tax.lib.php::tax_by_thirdparty no accountancy module enabled".$sql, LOG_ERR);
482 return -1; // -1 = Not accountancy module enabled
483 }
484 if ($sql == 'TODO') {
485 return -2; // -2 = Feature not yet available
486 }
487 if ($sql != 'TODO') {
488 dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
489 $resql = $db->query($sql);
490 if ($resql) {
491 $company_id = -1;
492 $oldrowid = '';
493 while ($assoc = $db->fetch_array($resql)) {
494 if (!isset($list[$assoc['company_id']]['totalht'])) {
495 $list[$assoc['company_id']]['totalht'] = 0;
496 }
497 if (!isset($list[$assoc['company_id']]['vat'])) {
498 $list[$assoc['company_id']]['vat'] = 0;
499 }
500 if (!isset($list[$assoc['company_id']]['localtax1'])) {
501 $list[$assoc['company_id']]['localtax1'] = 0;
502 }
503 if (!isset($list[$assoc['company_id']]['localtax2'])) {
504 $list[$assoc['company_id']]['localtax2'] = 0;
505 }
506
507 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
508 $oldrowid = $assoc['rowid'];
509 $list[$assoc['company_id']]['totalht'] += (float) $assoc['total_ht'];
510 $list[$assoc['company_id']]['vat'] += (float) $assoc['total_vat'];
511 $list[$assoc['company_id']]['localtax1'] += (float) $assoc['total_localtax1'];
512 $list[$assoc['company_id']]['localtax2'] += (float) $assoc['total_localtax2'];
513 }
514 $list[$assoc['company_id']]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
515 $list[$assoc['company_id']]['dtype'][] = $assoc['dtype'];
516 $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
517 $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
518
519 $list[$assoc['company_id']]['company_name'][] = (string) $assoc['company_name'];
520 $list[$assoc['company_id']]['company_id'][] = (int) $assoc['company_id'];
521 $list[$assoc['company_id']]['company_alias'][] = (string) $assoc['company_alias'];
522 $list[$assoc['company_id']]['company_email'][] = (string) $assoc['company_email'];
523 $list[$assoc['company_id']]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
524 $list[$assoc['company_id']]['company_client'][] = (int) $assoc['company_client'];
525 $list[$assoc['company_id']]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
526 $list[$assoc['company_id']]['company_customer_code'][] = (string) $assoc['company_customer_code'];
527 $list[$assoc['company_id']]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
528 $list[$assoc['company_id']]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
529 $list[$assoc['company_id']]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
530 $list[$assoc['company_id']]['company_status'][] = (int) $assoc['company_status'];
531
532 $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
533 $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
534 $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
535
536 $list[$assoc['company_id']]['facid'][] = (int) $assoc['facid'];
537 $list[$assoc['company_id']]['facnum'][] = (string) $assoc['facnum'];
538 $list[$assoc['company_id']]['type'][] = (int) $assoc['type'];
539 $list[$assoc['company_id']]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
540 $list[$assoc['company_id']]['descr'][] = (string) $assoc['descr'];
541
542 $list[$assoc['company_id']]['totalht_list'][] = (float) $assoc['total_ht'];
543 $list[$assoc['company_id']]['vat_list'][] = (float) $assoc['total_vat'];
544 $list[$assoc['company_id']]['localtax1_list'][] = (float) $assoc['total_localtax1'];
545 $list[$assoc['company_id']]['localtax2_list'][] = (float) $assoc['total_localtax2'];
546
547 $list[$assoc['company_id']]['pid'][] = (int) $assoc['pid'];
548 $list[$assoc['company_id']]['pref'][] = (string) $assoc['pref'];
549 $list[$assoc['company_id']]['ptype'][] = (int) $assoc['ptype'];
550
551 $list[$assoc['company_id']]['payment_id'][] = (int) $assoc['payment_id'];
552 $list[$assoc['company_id']]['payment_ref'][] = (string) $assoc['payment_ref'];
553 $list[$assoc['company_id']]['payment_amount'][] = (float) $assoc['payment_amount'];
554
555 $company_id = $assoc['company_id'];
556 }
557 } else {
558 dol_print_error($db);
559 return -3;
560 }
561 }
562
563
564 // CASE OF EXPENSE REPORT
565
566 if ($direction == 'buy') { // buy only for expense reports
567 // Define sql request
568 $sql = '';
569
570 // Count on payments date
571 $sql = "SELECT d.rowid, d.product_type as dtype, e.rowid as facid, d.$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,";
572 $sql .= " d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
573 $sql .= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
574 $sql .= " e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
575 $sql .= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
576 $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as e";
577 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid ";
578 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid ";
579 $sql .= " WHERE e.entity = ".$conf->entity;
580 $sql .= " AND e.fk_statut in (6)";
581 if ($y && $m) {
582 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
583 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
584 } elseif ($y) {
585 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
586 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
587 }
588 if ($q) {
589 $sql .= " AND p.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
590 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
591 }
592 if ($date_start && $date_end) {
593 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
594 }
595 $sql .= " AND (d.product_type = -1";
596 $sql .= " OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)"; // enhance detection of service
597 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
598 $sql .= " AND (d.".$f_rate." <> 0 OR d.total_tva <> 0)";
599 }
600 $sql .= " ORDER BY e.rowid";
601
602 if (!$sql) {
603 dol_syslog("Tax.lib.php::tax_by_thirdparty no accountancy module enabled".$sql, LOG_ERR);
604 return -1; // -1 = Not accountancy module enabled
605 }
606 if ($sql == 'TODO') {
607 return -2; // -2 = Feature not yet available
608 }
609 if ($sql != 'TODO') {
610 dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
611 $resql = $db->query($sql);
612 if ($resql) {
613 $company_id = -1;
614 $oldrowid = '';
615 while ($assoc = $db->fetch_array($resql)) {
616 if (!isset($list[$assoc['company_id']]['totalht'])) {
617 $list[$assoc['company_id']]['totalht'] = 0;
618 }
619 if (!isset($list[$assoc['company_id']]['vat'])) {
620 $list[$assoc['company_id']]['vat'] = 0;
621 }
622 if (!isset($list[$assoc['company_id']]['localtax1'])) {
623 $list[$assoc['company_id']]['localtax1'] = 0;
624 }
625 if (!isset($list[$assoc['company_id']]['localtax2'])) {
626 $list[$assoc['company_id']]['localtax2'] = 0;
627 }
628
629 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
630 $oldrowid = $assoc['rowid'];
631 $list[$assoc['company_id']]['totalht'] += (float) $assoc['total_ht'];
632 $list[$assoc['company_id']]['vat'] += (float) $assoc['total_vat'];
633 $list[$assoc['company_id']]['localtax1'] += (float) $assoc['total_localtax1'];
634 $list[$assoc['company_id']]['localtax2'] += (float) $assoc['total_localtax2'];
635 }
636
637 $list[$assoc['company_id']]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
638 $list[$assoc['company_id']]['dtype'][] = 'ExpenseReportPayment';
639 $list[$assoc['company_id']]['datef'][] = (int) $assoc['datef'];
640
641 $list[$assoc['company_id']]['company_name'][] = '';
642 $list[$assoc['company_id']]['company_id'][] = 0;
643 $list[$assoc['company_id']]['company_alias'][] = '';
644 $list[$assoc['company_id']]['company_email'][] = '';
645 $list[$assoc['company_id']]['company_tva_intra'][] = '';
646 $list[$assoc['company_id']]['company_client'][] = 0;
647 $list[$assoc['company_id']]['company_fournisseur'][] = 0;
648 $list[$assoc['company_id']]['company_customer_code'][] = '';
649 $list[$assoc['company_id']]['company_supplier_code'][] = '';
650 $list[$assoc['company_id']]['company_customer_accounting_code'][] = '';
651 $list[$assoc['company_id']]['company_supplier_accounting_code'][] = '';
652 $list[$assoc['company_id']]['company_status'][] = 0;
653
654 $list[$assoc['company_id']]['user_id'][] = (int) $assoc['fk_user_author'];
655 $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
656 $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
657 $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
658
659 $list[$assoc['company_id']]['facid'][] = (int) $assoc['facid'];
660 $list[$assoc['company_id']]['facnum'][] = (string) $assoc['facnum'];
661 $list[$assoc['company_id']]['type'][] = (int) $assoc['type'];
662 $list[$assoc['company_id']]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
663 $list[$assoc['company_id']]['descr'][] = (string) $assoc['descr'];
664
665 $list[$assoc['company_id']]['totalht_list'][] = (float) $assoc['total_ht'];
666 $list[$assoc['company_id']]['vat_list'][] = (float) $assoc['total_vat'];
667 $list[$assoc['company_id']]['localtax1_list'][] = (float) $assoc['total_localtax1'];
668 $list[$assoc['company_id']]['localtax2_list'][] = (float) $assoc['total_localtax2'];
669
670 $list[$assoc['company_id']]['pid'][] = (int) $assoc['pid'];
671 $list[$assoc['company_id']]['pref'][] = (string) $assoc['pref'];
672 $list[$assoc['company_id']]['ptype'][] = 'ExpenseReportPayment';
673
674 $list[$assoc['company_id']]['payment_id'][] = (int) $assoc['payment_id'];
675 $list[$assoc['company_id']]['payment_ref'][] = (string) $assoc['payment_ref'];
676 $list[$assoc['company_id']]['payment_amount'][] = (float) $assoc['payment_amount'];
677
678 $company_id = $assoc['company_id'];
679 }
680 } else {
681 dol_print_error($db);
682 return -3;
683 }
684 }
685 }
686
687 return $list;
688}
689
690
707function tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
708{
709 global $conf;
710
711 // If we use date_start and date_end, we must not use $y, $m, $q
712 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
713 dol_print_error(null, 'Bad value of input parameter for tax_by_rate');
714 }
715
716 $list = array();
717
718 if ($direction == 'sell') {
719 $invoicetable = 'facture';
720 $invoicedettable = 'facturedet';
721 $fk_facture = 'fk_facture';
722 $fk_facture2 = 'fk_facture';
723 $fk_payment = 'fk_paiement';
724 $total_tva = 'total_tva';
725 $paymenttable = 'paiement';
726 $paymentfacturetable = 'paiement_facture';
727 $invoicefieldref = 'ref';
728 } else {
729 $invoicetable = 'facture_fourn';
730 $invoicedettable = 'facture_fourn_det';
731 $fk_facture = 'fk_facture_fourn';
732 $fk_facture2 = 'fk_facturefourn';
733 $fk_payment = 'fk_paiementfourn';
734 $total_tva = 'tva';
735 $paymenttable = 'paiementfourn';
736 $paymentfacturetable = 'paiementfourn_facturefourn';
737 $invoicefieldref = 'ref';
738 }
739
740 if (strpos($type, 'localtax') === 0) {
741 $f_rate = $type.'_tx';
742 } else {
743 $f_rate = 'tva_tx';
744 }
745
746 $total_localtax1 = 'total_localtax1';
747 $total_localtax2 = 'total_localtax2';
748
749
750 // CASE OF PRODUCTS/GOODS
751
752 // Define sql request
753 $sql = '';
754 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_PRODUCT') == 'invoice')
755 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_PRODUCT') == 'invoice')) {
756 // Count on delivery date (use invoice date as delivery is unknown)
757 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$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, d.description as descr,";
758 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
759 $sql .= " d.date_start as date_start, d.date_end as date_end,";
760 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
761 $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,";
762 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
763 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
764 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
765 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
766 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount,";
767 $sql .= " '' as datep";
768 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
769 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
770 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture."=f.rowid";
771 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
772 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
773 $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
774 if ($direction == 'buy') {
775 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
776 $sql .= " AND f.type IN (0,1,2,5)";
777 } else {
778 $sql .= " AND f.type IN (0,1,2,3,5)";
779 }
780 } else {
781 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
782 $sql .= " AND f.type IN (0,1,2,5)";
783 } else {
784 $sql .= " AND f.type IN (0,1,2,3,5)";
785 }
786 }
787 if ($y && $m) {
788 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
789 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
790 } elseif ($y) {
791 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
792 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
793 }
794 if ($q) {
795 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
796 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
797 }
798 if ($date_start && $date_end) {
799 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
800 }
801 $sql .= " AND (d.product_type = 0"; // Limit to products
802 $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
803 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
804 $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
805 }
806 $sql .= " ORDER BY d.rowid, d.".$fk_facture;
807 } else {
808 // Count on payments date
809 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$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, d.description as descr,";
810 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
811 $sql .= " d.date_start as date_start, d.date_end as date_end,";
812 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
813 $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,";
814 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
815 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
816 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
817 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
818 $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
819 $sql .= " pa.datep as datep, pa.ref as payment_ref";
820 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
821 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable." as pf ON pf.".$fk_facture2." = f.rowid";
822 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymenttable." as pa ON pa.rowid = pf.".$fk_payment;
823 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
824 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture." = f.rowid";
825 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
826 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
827 $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
828 if ($direction == 'buy') {
829 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
830 $sql .= " AND f.type IN (0,1,2,5)";
831 } else {
832 $sql .= " AND f.type IN (0,1,2,3,5)";
833 }
834 } else {
835 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
836 $sql .= " AND f.type IN (0,1,2,5)";
837 } else {
838 $sql .= " AND f.type IN (0,1,2,3,5)";
839 }
840 }
841 if ($y && $m) {
842 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
843 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
844 } elseif ($y) {
845 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
846 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
847 }
848 if ($q) {
849 $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
850 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
851 }
852 if ($date_start && $date_end) {
853 $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
854 }
855 $sql .= " AND (d.product_type = 0"; // Limit to products
856 $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
857 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
858 $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
859 }
860 $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
861 }
862
863 if (!$sql) {
864 return -1;
865 }
866 if ($sql == 'TODO') {
867 return -2;
868 }
869 if ($sql != 'TODO') {
870 dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
871
872 $resql = $db->query($sql);
873 if ($resql) {
874 $rate = -1;
875 $oldrowid = '';
876 while ($assoc = $db->fetch_array($resql)) {
877 $rate_key = $assoc['rate'];
878 if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
879 $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
880 }
881
882 // Code to avoid warnings when array entry not defined
883 if (!isset($list[$rate_key]['totalht'])) {
884 $list[$rate_key]['totalht'] = 0;
885 }
886 if (!isset($list[$rate_key]['vat'])) {
887 $list[$rate_key]['vat'] = 0;
888 }
889 if (!isset($list[$rate_key]['localtax1'])) {
890 $list[$rate_key]['localtax1'] = 0;
891 }
892 if (!isset($list[$rate_key]['localtax2'])) {
893 $list[$rate_key]['localtax2'] = 0;
894 }
895
896 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
897 $oldrowid = $assoc['rowid'];
898 $list[$rate_key]['totalht'] += (float) $assoc['total_ht'];
899 $list[$rate_key]['vat'] += (float) $assoc['total_vat'];
900 $list[$rate_key]['localtax1'] += (float) $assoc['total_localtax1'];
901 $list[$rate_key]['localtax2'] += (float) $assoc['total_localtax2'];
902 }
903 $list[$rate_key]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
904 $list[$rate_key]['dtype'][] = (int) $assoc['dtype'];
905 $list[$rate_key]['datef'][] = $db->jdate($assoc['datef']);
906 $list[$rate_key]['datep'][] = $db->jdate($assoc['datep']);
907
908 $list[$rate_key]['company_name'][] = (string) $assoc['company_name'];
909 $list[$rate_key]['company_id'][] = (int) $assoc['company_id'];
910 $list[$rate_key]['company_alias'][] = (string) $assoc['company_alias'];
911 $list[$rate_key]['company_email'][] = (string) $assoc['company_email'];
912 $list[$rate_key]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
913 $list[$rate_key]['company_client'][] = (int) $assoc['company_client'];
914 $list[$rate_key]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
915 $list[$rate_key]['company_customer_code'][] = (string) $assoc['company_customer_code'];
916 $list[$rate_key]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
917 $list[$rate_key]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
918 $list[$rate_key]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
919 $list[$rate_key]['company_status'][] = (int) $assoc['company_status'];
920
921 $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
922 $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
923
924 $list[$rate_key]['facid'][] = (int) $assoc['facid'];
925 $list[$rate_key]['facnum'][] = (string) $assoc['facnum'];
926 $list[$rate_key]['type'][] = (int) $assoc['type'];
927 $list[$rate_key]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
928 $list[$rate_key]['descr'][] = (string) $assoc['descr'];
929
930 $list[$rate_key]['totalht_list'][] = (float) $assoc['total_ht'];
931 $list[$rate_key]['vat_list'][] = (float) $assoc['total_vat'];
932 $list[$rate_key]['localtax1_list'][] = (float) $assoc['total_localtax1'];
933 $list[$rate_key]['localtax2_list'][] = (float) $assoc['total_localtax2'];
934
935 $list[$rate_key]['pid'][] = (int) $assoc['pid'];
936 $list[$rate_key]['pref'][] = (string) $assoc['pref'];
937 $list[$rate_key]['ptype'][] = (int) $assoc['ptype'];
938
939 $list[$rate_key]['payment_id'][] = (int) $assoc['payment_id'];
940 $list[$rate_key]['payment_ref'][] = (string) $assoc['payment_ref'];
941 $list[$rate_key]['payment_amount'][] = (float) $assoc['payment_amount'];
942
943 $rate = $assoc['rate'];
944 }
945 } else {
946 dol_print_error($db);
947 return -3;
948 }
949 }
950
951
952 // CASE OF SERVICES
953
954 // Define sql request
955 $sql = '';
956 if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_SERVICE') == 'invoice')
957 || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_SERVICE') == 'invoice')) {
958 // Count on invoice date
959 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$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, d.description as descr,";
960 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
961 $sql .= " d.date_start as date_start, d.date_end as date_end,";
962 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
963 $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,";
964 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
965 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
966 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
967 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
968 $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
969 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
970 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
971 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture." = f.rowid";
972 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
973 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
974 $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
975 if ($direction == 'buy') {
976 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
977 $sql .= " AND f.type IN (0,1,2,5)";
978 } else {
979 $sql .= " AND f.type IN (0,1,2,3,5)";
980 }
981 } else {
982 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
983 $sql .= " AND f.type IN (0,1,2,5)";
984 } else {
985 $sql .= " AND f.type IN (0,1,2,3,5)";
986 }
987 }
988 if ($y && $m) {
989 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
990 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
991 } elseif ($y) {
992 $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
993 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
994 }
995 if ($q) {
996 $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
997 $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
998 }
999 if ($date_start && $date_end) {
1000 $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
1001 }
1002 $sql .= " AND (d.product_type = 1"; // Limit to services
1003 $sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
1004 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
1005 $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
1006 }
1007 $sql .= " ORDER BY d.rowid, d.".$fk_facture;
1008 } else {
1009 // Count on payments date
1010 $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$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, d.description as descr,";
1011 $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
1012 $sql .= " d.date_start as date_start, d.date_end as date_end,";
1013 $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
1014 $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,";
1015 $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
1016 $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
1017 $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
1018 $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
1019 $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
1020 $sql .= " pa.datep as datep, pa.ref as payment_ref";
1021 $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
1022 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable." as pf ON pf.".$fk_facture2." = f.rowid";
1023 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymenttable." as pa ON pa.rowid = pf.".$fk_payment;
1024 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
1025 $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture." = f.rowid";
1026 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
1027 $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
1028 $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
1029 if ($direction == 'buy') {
1030 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
1031 $sql .= " AND f.type IN (0,1,2,5)";
1032 } else {
1033 $sql .= " AND f.type IN (0,1,2,3,5)";
1034 }
1035 } else {
1036 if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
1037 $sql .= " AND f.type IN (0,1,2,5)";
1038 } else {
1039 $sql .= " AND f.type IN (0,1,2,3,5)";
1040 }
1041 }
1042 if ($y && $m) {
1043 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
1044 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
1045 } elseif ($y) {
1046 $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
1047 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
1048 }
1049 if ($q) {
1050 $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
1051 $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
1052 }
1053 if ($date_start && $date_end) {
1054 $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
1055 }
1056 $sql .= " AND (d.product_type = 1"; // Limit to services
1057 $sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
1058 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
1059 $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
1060 }
1061 $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
1062 }
1063
1064 if (!$sql) {
1065 dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1066 return -1; // -1 = Not accountancy module enabled
1067 }
1068 if ($sql == 'TODO') {
1069 return -2; // -2 = Feature not yet available
1070 }
1071 if ($sql != 'TODO') {
1072 dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
1073 $resql = $db->query($sql);
1074 if ($resql) {
1075 $rate = -1;
1076 $oldrowid = '';
1077 while ($assoc = $db->fetch_array($resql)) {
1078 $rate_key = $assoc['rate'];
1079 if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
1080 $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
1081 }
1082
1083 // Code to avoid warnings when array entry not defined
1084 if (!isset($list[$rate_key]['totalht'])) {
1085 $list[$rate_key]['totalht'] = 0;
1086 }
1087 if (!isset($list[$rate_key]['vat'])) {
1088 $list[$rate_key]['vat'] = 0;
1089 }
1090 if (!isset($list[$rate_key]['localtax1'])) {
1091 $list[$rate_key]['localtax1'] = 0;
1092 }
1093 if (!isset($list[$rate_key]['localtax2'])) {
1094 $list[$rate_key]['localtax2'] = 0;
1095 }
1096
1097 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
1098 $oldrowid = $assoc['rowid'];
1099 $list[$rate_key]['totalht'] += (float) $assoc['total_ht'];
1100 $list[$rate_key]['vat'] += (float) $assoc['total_vat'];
1101 $list[$rate_key]['localtax1'] += (float) $assoc['total_localtax1'];
1102 $list[$rate_key]['localtax2'] += (float) $assoc['total_localtax2'];
1103 }
1104 $list[$rate_key]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
1105 $list[$rate_key]['dtype'][] = (int) $assoc['dtype'];
1106 $list[$rate_key]['datef'][] = $db->jdate($assoc['datef']);
1107 $list[$rate_key]['datep'][] = $db->jdate($assoc['datep']);
1108
1109 $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
1110 $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
1111
1112 $list[$rate_key]['company_name'][] = (string) $assoc['company_name'];
1113 $list[$rate_key]['company_id'][] = (int) $assoc['company_id'];
1114 $list[$rate_key]['company_alias'][] = (string) $assoc['company_alias'];
1115 $list[$rate_key]['company_email'][] = (string) $assoc['company_email'];
1116 $list[$rate_key]['company_tva_intra'][] = (string) $assoc['company_tva_intra'];
1117 $list[$rate_key]['company_client'][] = (int) $assoc['company_client'];
1118 $list[$rate_key]['company_fournisseur'][] = (int) $assoc['company_fournisseur'];
1119 $list[$rate_key]['company_customer_code'][] = (string) $assoc['company_customer_code'];
1120 $list[$rate_key]['company_supplier_code'][] = (string) $assoc['company_supplier_code'];
1121 $list[$rate_key]['company_customer_accounting_code'][] = (string) $assoc['company_customer_accounting_code'];
1122 $list[$rate_key]['company_supplier_accounting_code'][] = (string) $assoc['company_supplier_accounting_code'];
1123 $list[$rate_key]['company_status'][] = (int) $assoc['company_status'];
1124
1125 $list[$rate_key]['facid'][] = (int) $assoc['facid'];
1126 $list[$rate_key]['facnum'][] = (string) $assoc['facnum'];
1127 $list[$rate_key]['type'][] = (int) $assoc['type'];
1128 $list[$rate_key]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
1129 $list[$rate_key]['descr'][] = (string) $assoc['descr'];
1130
1131 $list[$rate_key]['totalht_list'][] = (float) $assoc['total_ht'];
1132 $list[$rate_key]['vat_list'][] = (float) $assoc['total_vat'];
1133 $list[$rate_key]['localtax1_list'][] = (float) $assoc['total_localtax1'];
1134 $list[$rate_key]['localtax2_list'][] = (float) $assoc['total_localtax2'];
1135
1136 $list[$rate_key]['pid'][] = (int) $assoc['pid'];
1137 $list[$rate_key]['pref'][] = (string) $assoc['pref'];
1138 $list[$rate_key]['ptype'][] = (int) $assoc['ptype'];
1139
1140 $list[$rate_key]['payment_id'][] = (int) $assoc['payment_id'];
1141 $list[$rate_key]['payment_ref'][] = (string) $assoc['payment_ref'];
1142 $list[$rate_key]['payment_amount'][] = (float) $assoc['payment_amount'];
1143
1144 $rate = $assoc['rate'];
1145 }
1146 } else {
1147 dol_print_error($db);
1148 return -3;
1149 }
1150 }
1151
1152
1153 // CASE OF EXPENSE REPORT
1154
1155 if ($direction == 'buy') { // buy only for expense reports
1156 // Define sql request
1157 $sql = '';
1158
1159 // Count on payments date
1160 $sql = "SELECT d.rowid, d.product_type as dtype, e.rowid as facid, d.$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,";
1161 $sql .= " d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
1162 $sql .= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
1163 $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,";
1164 $sql .= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid";
1165 $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as e";
1166 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid";
1167 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid";
1168 $sql .= " WHERE e.entity = ".$conf->entity;
1169 $sql .= " AND e.fk_statut in (6)";
1170 if ($y && $m) {
1171 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
1172 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
1173 } elseif ($y) {
1174 $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
1175 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
1176 }
1177 if ($q) {
1178 $sql .= " AND p.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
1179 $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
1180 }
1181 if ($date_start && $date_end) {
1182 $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
1183 }
1184 $sql .= " AND (d.product_type = -1";
1185 $sql .= " OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)"; // enhance detection of service
1186 if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
1187 $sql .= " AND (d.".$db->sanitize($f_rate)." <> 0 OR d.total_tva <> 0)";
1188 }
1189 $sql .= " ORDER BY e.rowid";
1190
1191 if (!$sql) {
1192 dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1193 return -1; // -1 = Not accountancy module enabled
1194 }
1195 if ($sql == 'TODO') {
1196 return -2; // -2 = Feature not yet available
1197 }
1198 if ($sql != 'TODO') {
1199 dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
1200 $resql = $db->query($sql);
1201 if ($resql) {
1202 $rate = -1;
1203 $oldrowid = '';
1204 while ($assoc = $db->fetch_array($resql)) {
1205 $rate_key = $assoc['rate'];
1206 if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
1207 $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
1208 }
1209
1210 // Code to avoid warnings when array entry not defined
1211 if (!isset($list[$rate_key]['totalht'])) {
1212 $list[$rate_key]['totalht'] = 0;
1213 }
1214 if (!isset($list[$rate_key]['vat'])) {
1215 $list[$rate_key]['vat'] = 0;
1216 }
1217 if (!isset($list[$rate_key]['localtax1'])) {
1218 $list[$rate_key]['localtax1'] = 0;
1219 }
1220 if (!isset($list[$rate_key]['localtax2'])) {
1221 $list[$rate_key]['localtax2'] = 0;
1222 }
1223
1224 if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
1225 $oldrowid = $assoc['rowid'];
1226 $list[$rate_key]['totalht'] += (float) $assoc['total_ht'];
1227 $list[$rate_key]['vat'] += (float) $assoc['total_vat'];
1228 $list[$rate_key]['localtax1'] += (float) $assoc['total_localtax1'];
1229 $list[$rate_key]['localtax2'] += (float) $assoc['total_localtax2'];
1230 }
1231
1232 $list[$rate_key]['dtotal_ttc'][] = (float) $assoc['total_ttc'];
1233 $list[$rate_key]['dtype'][] = 'ExpenseReportPayment';
1234 $list[$rate_key]['datef'][] = (int) $assoc['datef'];
1235 $list[$rate_key]['company_name'][] = '';
1236 $list[$rate_key]['company_id'][] = 0;
1237 $list[$rate_key]['user_id'][] = (int) $assoc['fk_user_author'];
1238 $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
1239 $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
1240
1241 $list[$rate_key]['facid'][] = (int) $assoc['facid'];
1242 $list[$rate_key]['facnum'][] = (string) $assoc['facnum'];
1243 $list[$rate_key]['type'][] = (int) $assoc['type'];
1244 $list[$rate_key]['ftotal_ttc'][] = (float) $assoc['ftotal_ttc'];
1245 $list[$rate_key]['descr'][] = (string) $assoc['descr'];
1246
1247 $list[$rate_key]['totalht_list'][] = (float) $assoc['total_ht'];
1248 $list[$rate_key]['vat_list'][] = (float) $assoc['total_vat'];
1249 $list[$rate_key]['localtax1_list'][] = (float) $assoc['total_localtax1'];
1250 $list[$rate_key]['localtax2_list'][] = (float) $assoc['total_localtax2'];
1251
1252 $list[$rate_key]['pid'][] = (int) $assoc['pid'];
1253 $list[$rate_key]['pref'][] = (string) $assoc['pref'];
1254 $list[$rate_key]['ptype'][] = 'ExpenseReportPayment';
1255
1256 $list[$rate_key]['payment_id'][] = (int) $assoc['payment_id'];
1257 $list[$rate_key]['payment_ref'][] = (string) $assoc['payment_ref'];
1258 $list[$rate_key]['payment_amount'][] = (float) $assoc['payment_amount'];
1259
1260 $rate = $assoc['rate'];
1261 }
1262 } else {
1263 dol_print_error($db);
1264 return -3;
1265 }
1266 }
1267 }
1268
1269 return $list;
1270}
$object
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:66
ChargeSociales
Class for managing the social charges.
Definition chargesociales.class.php:36
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:600
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:619
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:63
dol_sanitizeFileName
dol_sanitizeFileName($str, $newstr='_', $unaccent=1)
Clean a string to use it as a file name.
Definition functions.lib.php:1626
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 functions.lib.php:5993
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:11031
getDolGlobalString
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
Definition functions.lib.php:217
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition functions.lib.php:2253
$conf
global $conf
The following vars must be defined: $type2label $form $conf, $lang, The following vars may also be de...
Definition member.php:79
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:707
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:114
tax_prepare_head
tax_prepare_head(ChargeSociales $object)
Prepare array with list of tabs.
Definition tax.lib.php:40
Generated on Sun Dec 1 2024 01:00:27 for dolibarr by Doxygen 1.11.0