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