dolibarr  20.0.0-alpha
tax.lib.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2004-2009 Laurent Destailleur <eldy@users.sourceforge.net>
3  * Copyright (C) 2006-2007 Yannick Warnier <ywarnier@beeznest.org>
4  * Copyright (C) 2011 Regis Houssin <regis.houssin@inodbox.com>
5  * Copyright (C) 2012-2017 Juanjo Menent <jmenent@2byte.es>
6  * Copyright (C) 2012 Cédric Salvador <csalvador@gpcsolutions.fr>
7  * Copyright (C) 2012-2014 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
8  * Copyright (C) 2015 Marcos García <marcosgdf@gmail.com>
9  * Copyright (C) 2021-2022 Open-Dsi <support@open-dsi.fr>
10  *
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] .= (!getDolGlobalString('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(null, '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' && getDolGlobalString('TAX_MODE_SELL_PRODUCT') == 'invoice')
159  || ($direction == 'buy' && getDolGlobalString('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 .= " ,'' as datep";
172  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
173  $sql .= " ".MAIN_DB_PREFIX."societe as s,";
174  $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
175  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
176  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
177  $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
178  if ($direction == 'buy') {
179  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
180  $sql .= " AND f.type IN (0,1,2,5)";
181  } else {
182  $sql .= " AND f.type IN (0,1,2,3,5)";
183  }
184  } else {
185  if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
186  $sql .= " AND f.type IN (0,1,2,5)";
187  } else {
188  $sql .= " AND f.type IN (0,1,2,3,5)";
189  }
190  }
191  $sql .= " AND f.rowid = d.".$fk_facture;
192  $sql .= " AND s.rowid = f.fk_soc";
193  if ($y && $m) {
194  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
195  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
196  } elseif ($y) {
197  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
198  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
199  }
200  if ($q) {
201  $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
202  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
203  }
204  if ($date_start && $date_end) {
205  $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
206  }
207  $sql .= " AND (d.product_type = 0"; // Limit to products
208  $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
209  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
210  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
211  }
212  $sql .= " ORDER BY d.rowid, d.".$fk_facture;
213  } else {
214  // Count on payments date
215  $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,";
216  $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
217  $sql .= " d.date_start as date_start, d.date_end as date_end,";
218  $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
219  $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,";
220  $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
221  $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
222  $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
223  $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
224  $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
225  $sql .= " pa.datep as datep, pa.ref as payment_ref";
226  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
227  $sql .= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
228  $sql .= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
229  $sql .= " ".MAIN_DB_PREFIX."societe as s,";
230  $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
231  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
232  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
233  $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
234  if ($direction == 'buy') {
235  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
236  $sql .= " AND f.type IN (0,1,2,5)";
237  } else {
238  $sql .= " AND f.type IN (0,1,2,3,5)";
239  }
240  } else {
241  if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
242  $sql .= " AND f.type IN (0,1,2,5)";
243  } else {
244  $sql .= " AND f.type IN (0,1,2,3,5)";
245  }
246  }
247  $sql .= " AND f.rowid = d.".$fk_facture;
248  $sql .= " AND s.rowid = f.fk_soc";
249  $sql .= " AND pf.".$fk_facture2." = f.rowid";
250  $sql .= " AND pa.rowid = pf.".$fk_payment;
251  if ($y && $m) {
252  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
253  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
254  } elseif ($y) {
255  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
256  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
257  }
258  if ($q) {
259  $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
260  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
261  }
262  if ($date_start && $date_end) {
263  $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
264  }
265  $sql .= " AND (d.product_type = 0"; // Limit to products
266  $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
267  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
268  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
269  }
270  $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
271  }
272 
273  if (!$sql) {
274  return -1;
275  }
276  if ($sql == 'TODO') {
277  return -2;
278  }
279  if ($sql != 'TODO') {
280  dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
281 
282  $resql = $db->query($sql);
283  if ($resql) {
284  $company_id = -1;
285  $oldrowid = '';
286  while ($assoc = $db->fetch_array($resql)) {
287  if (!isset($list[$assoc['company_id']]['totalht'])) {
288  $list[$assoc['company_id']]['totalht'] = 0;
289  }
290  if (!isset($list[$assoc['company_id']]['vat'])) {
291  $list[$assoc['company_id']]['vat'] = 0;
292  }
293  if (!isset($list[$assoc['company_id']]['localtax1'])) {
294  $list[$assoc['company_id']]['localtax1'] = 0;
295  }
296  if (!isset($list[$assoc['company_id']]['localtax2'])) {
297  $list[$assoc['company_id']]['localtax2'] = 0;
298  }
299 
300  if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
301  $oldrowid = $assoc['rowid'];
302  $list[$assoc['company_id']]['totalht'] += $assoc['total_ht'];
303  $list[$assoc['company_id']]['vat'] += $assoc['total_vat'];
304  $list[$assoc['company_id']]['localtax1'] += $assoc['total_localtax1'];
305  $list[$assoc['company_id']]['localtax2'] += $assoc['total_localtax2'];
306  }
307 
308  $list[$assoc['company_id']]['dtotal_ttc'][] = $assoc['total_ttc'];
309  $list[$assoc['company_id']]['dtype'][] = $assoc['dtype'];
310  $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
311  $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
312 
313  $list[$assoc['company_id']]['company_name'][] = $assoc['company_name'];
314  $list[$assoc['company_id']]['company_id'][] = $assoc['company_id'];
315  $list[$assoc['company_id']]['company_alias'][] = $assoc['company_alias'];
316  $list[$assoc['company_id']]['company_email'][] = $assoc['company_email'];
317  $list[$assoc['company_id']]['company_tva_intra'][] = $assoc['company_tva_intra'];
318  $list[$assoc['company_id']]['company_client'][] = $assoc['company_client'];
319  $list[$assoc['company_id']]['company_fournisseur'][] = $assoc['company_fournisseur'];
320  $list[$assoc['company_id']]['company_customer_code'][] = $assoc['company_customer_code'];
321  $list[$assoc['company_id']]['company_supplier_code'][] = $assoc['company_supplier_code'];
322  $list[$assoc['company_id']]['company_customer_accounting_code'][] = $assoc['company_customer_accounting_code'];
323  $list[$assoc['company_id']]['company_supplier_accounting_code'][] = $assoc['company_supplier_accounting_code'];
324  $list[$assoc['company_id']]['company_status'][] = $assoc['company_status'];
325 
326  $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
327  $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
328  $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
329 
330  $list[$assoc['company_id']]['facid'][] = $assoc['facid'];
331  $list[$assoc['company_id']]['facnum'][] = $assoc['facnum'];
332  $list[$assoc['company_id']]['type'][] = $assoc['type'];
333  $list[$assoc['company_id']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
334  $list[$assoc['company_id']]['descr'][] = $assoc['descr'];
335 
336  $list[$assoc['company_id']]['totalht_list'][] = $assoc['total_ht'];
337  $list[$assoc['company_id']]['vat_list'][] = $assoc['total_vat'];
338  $list[$assoc['company_id']]['localtax1_list'][] = $assoc['total_localtax1'];
339  $list[$assoc['company_id']]['localtax2_list'][] = $assoc['total_localtax2'];
340 
341  $list[$assoc['company_id']]['pid'][] = $assoc['pid'];
342  $list[$assoc['company_id']]['pref'][] = $assoc['pref'];
343  $list[$assoc['company_id']]['ptype'][] = $assoc['ptype'];
344 
345  $list[$assoc['company_id']]['payment_id'][] = $assoc['payment_id'];
346  $list[$assoc['company_id']]['payment_amount'][] = $assoc['payment_amount'];
347 
348  $company_id = $assoc['company_id'];
349  }
350  } else {
351  dol_print_error($db);
352  return -3;
353  }
354  }
355 
356 
357  // CAS DES SERVICES
358 
359  // Define sql request
360  $sql = '';
361  if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_SERVICE') == 'invoice')
362  || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_SERVICE') == 'invoice')) {
363  // Count on invoice date
364  $sql = "SELECT d.rowid, d.product_type as dtype, d.".$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,";
365  $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
366  $sql .= " d.date_start as date_start, d.date_end as date_end,";
367  $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
368  $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
369  $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
370  $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
371  $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
372  $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
373  $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
374  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
375  $sql .= " ".MAIN_DB_PREFIX."societe as s,";
376  $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
377  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
378  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
379  $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
380  if ($direction == 'buy') {
381  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
382  $sql .= " AND f.type IN (0,1,2,5)";
383  } else {
384  $sql .= " AND f.type IN (0,1,2,3,5)";
385  }
386  } else {
387  if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
388  $sql .= " AND f.type IN (0,1,2,5)";
389  } else {
390  $sql .= " AND f.type IN (0,1,2,3,5)";
391  }
392  }
393  $sql .= " AND f.rowid = d.".$fk_facture;
394  $sql .= " AND s.rowid = f.fk_soc";
395  if ($y && $m) {
396  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
397  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
398  } elseif ($y) {
399  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
400  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
401  }
402  if ($q) {
403  $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
404  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
405  }
406  if ($date_start && $date_end) {
407  $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
408  }
409  $sql .= " AND (d.product_type = 1"; // Limit to services
410  $sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
411  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
412  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
413  }
414  $sql .= " ORDER BY d.rowid, d.".$fk_facture;
415  } else {
416  // Count on payments date
417  $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,";
418  $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
419  $sql .= " d.date_start as date_start, d.date_end as date_end,";
420  $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
421  $sql .= " s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
422  $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
423  $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
424  $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
425  $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
426  $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
427  $sql .= " pa.datep as datep, pa.ref as payment_ref";
428  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
429  $sql .= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
430  $sql .= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
431  $sql .= " ".MAIN_DB_PREFIX."societe as s,";
432  $sql .= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
433  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
434  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
435  $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
436  if ($direction == 'buy') {
437  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
438  $sql .= " AND f.type IN (0,1,2,5)";
439  } else {
440  $sql .= " AND f.type IN (0,1,2,3,5)";
441  }
442  } else {
443  if (getDolGlobalString('FACTURE_DEPOSITS_ARE_JUST_PAYMENTS')) {
444  $sql .= " AND f.type IN (0,1,2,5)";
445  } else {
446  $sql .= " AND f.type IN (0,1,2,3,5)";
447  }
448  }
449  $sql .= " AND f.rowid = d.".$fk_facture;
450  $sql .= " AND s.rowid = f.fk_soc";
451  $sql .= " AND pf.".$fk_facture2." = f.rowid";
452  $sql .= " AND pa.rowid = pf.".$fk_payment;
453  if ($y && $m) {
454  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
455  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
456  } elseif ($y) {
457  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
458  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
459  }
460  if ($q) {
461  $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
462  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
463  }
464  if ($date_start && $date_end) {
465  $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
466  }
467  $sql .= " AND (d.product_type = 1"; // Limit to services
468  $sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
469  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
470  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
471  }
472  $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
473  }
474 
475  if (!$sql) {
476  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
477  return -1; // -1 = Not accountancy module enabled
478  }
479  if ($sql == 'TODO') {
480  return -2; // -2 = Feature not yet available
481  }
482  if ($sql != 'TODO') {
483  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
484  $resql = $db->query($sql);
485  if ($resql) {
486  $company_id = -1;
487  $oldrowid = '';
488  while ($assoc = $db->fetch_array($resql)) {
489  if (!isset($list[$assoc['company_id']]['totalht'])) {
490  $list[$assoc['company_id']]['totalht'] = 0;
491  }
492  if (!isset($list[$assoc['company_id']]['vat'])) {
493  $list[$assoc['company_id']]['vat'] = 0;
494  }
495  if (!isset($list[$assoc['company_id']]['localtax1'])) {
496  $list[$assoc['company_id']]['localtax1'] = 0;
497  }
498  if (!isset($list[$assoc['company_id']]['localtax2'])) {
499  $list[$assoc['company_id']]['localtax2'] = 0;
500  }
501 
502  if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
503  $oldrowid = $assoc['rowid'];
504  $list[$assoc['company_id']]['totalht'] += $assoc['total_ht'];
505  $list[$assoc['company_id']]['vat'] += $assoc['total_vat'];
506  $list[$assoc['company_id']]['localtax1'] += $assoc['total_localtax1'];
507  $list[$assoc['company_id']]['localtax2'] += $assoc['total_localtax2'];
508  }
509  $list[$assoc['company_id']]['dtotal_ttc'][] = $assoc['total_ttc'];
510  $list[$assoc['company_id']]['dtype'][] = $assoc['dtype'];
511  $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
512  $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
513 
514  $list[$assoc['company_id']]['company_name'][] = $assoc['company_name'];
515  $list[$assoc['company_id']]['company_id'][] = $assoc['company_id'];
516  $list[$assoc['company_id']]['company_alias'][] = $assoc['company_alias'];
517  $list[$assoc['company_id']]['company_email'][] = $assoc['company_email'];
518  $list[$assoc['company_id']]['company_tva_intra'][] = $assoc['company_tva_intra'];
519  $list[$assoc['company_id']]['company_client'][] = $assoc['company_client'];
520  $list[$assoc['company_id']]['company_fournisseur'][] = $assoc['company_fournisseur'];
521  $list[$assoc['company_id']]['company_customer_code'][] = $assoc['company_customer_code'];
522  $list[$assoc['company_id']]['company_supplier_code'][] = $assoc['company_supplier_code'];
523  $list[$assoc['company_id']]['company_customer_accounting_code'][] = $assoc['company_customer_accounting_code'];
524  $list[$assoc['company_id']]['company_supplier_accounting_code'][] = $assoc['company_supplier_accounting_code'];
525  $list[$assoc['company_id']]['company_status'][] = $assoc['company_status'];
526 
527  $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
528  $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
529  $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
530 
531  $list[$assoc['company_id']]['facid'][] = $assoc['facid'];
532  $list[$assoc['company_id']]['facnum'][] = $assoc['facnum'];
533  $list[$assoc['company_id']]['type'][] = $assoc['type'];
534  $list[$assoc['company_id']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
535  $list[$assoc['company_id']]['descr'][] = $assoc['descr'];
536 
537  $list[$assoc['company_id']]['totalht_list'][] = $assoc['total_ht'];
538  $list[$assoc['company_id']]['vat_list'][] = $assoc['total_vat'];
539  $list[$assoc['company_id']]['localtax1_list'][] = $assoc['total_localtax1'];
540  $list[$assoc['company_id']]['localtax2_list'][] = $assoc['total_localtax2'];
541 
542  $list[$assoc['company_id']]['pid'][] = $assoc['pid'];
543  $list[$assoc['company_id']]['pref'][] = $assoc['pref'];
544  $list[$assoc['company_id']]['ptype'][] = $assoc['ptype'];
545 
546  $list[$assoc['company_id']]['payment_id'][] = $assoc['payment_id'];
547  $list[$assoc['company_id']]['payment_ref'][] = $assoc['payment_ref'];
548  $list[$assoc['company_id']]['payment_amount'][] = $assoc['payment_amount'];
549 
550  $company_id = $assoc['company_id'];
551  }
552  } else {
553  dol_print_error($db);
554  return -3;
555  }
556  }
557 
558 
559  // CASE OF EXPENSE REPORT
560 
561  if ($direction == 'buy') { // buy only for expense reports
562  // Define sql request
563  $sql = '';
564 
565  // Count on payments date
566  $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,";
567  $sql .= " d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
568  $sql .= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
569  $sql .= " e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
570  $sql .= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
571  $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as e";
572  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid ";
573  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid ";
574  $sql .= " WHERE e.entity = ".$conf->entity;
575  $sql .= " AND e.fk_statut in (6)";
576  if ($y && $m) {
577  $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
578  $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
579  } elseif ($y) {
580  $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
581  $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
582  }
583  if ($q) {
584  $sql .= " AND p.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
585  $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
586  }
587  if ($date_start && $date_end) {
588  $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
589  }
590  $sql .= " AND (d.product_type = -1";
591  $sql .= " OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)"; // enhance detection of service
592  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
593  $sql .= " AND (d.".$f_rate." <> 0 OR d.total_tva <> 0)";
594  }
595  $sql .= " ORDER BY e.rowid";
596 
597  if (!$sql) {
598  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
599  return -1; // -1 = Not accountancy module enabled
600  }
601  if ($sql == 'TODO') {
602  return -2; // -2 = Feature not yet available
603  }
604  if ($sql != 'TODO') {
605  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
606  $resql = $db->query($sql);
607  if ($resql) {
608  $company_id = -1;
609  $oldrowid = '';
610  while ($assoc = $db->fetch_array($resql)) {
611  if (!isset($list[$assoc['company_id']]['totalht'])) {
612  $list[$assoc['company_id']]['totalht'] = 0;
613  }
614  if (!isset($list[$assoc['company_id']]['vat'])) {
615  $list[$assoc['company_id']]['vat'] = 0;
616  }
617  if (!isset($list[$assoc['company_id']]['localtax1'])) {
618  $list[$assoc['company_id']]['localtax1'] = 0;
619  }
620  if (!isset($list[$assoc['company_id']]['localtax2'])) {
621  $list[$assoc['company_id']]['localtax2'] = 0;
622  }
623 
624  if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
625  $oldrowid = $assoc['rowid'];
626  $list[$assoc['company_id']]['totalht'] += $assoc['total_ht'];
627  $list[$assoc['company_id']]['vat'] += $assoc['total_vat'];
628  $list[$assoc['company_id']]['localtax1'] += $assoc['total_localtax1'];
629  $list[$assoc['company_id']]['localtax2'] += $assoc['total_localtax2'];
630  }
631 
632  $list[$assoc['company_id']]['dtotal_ttc'][] = $assoc['total_ttc'];
633  $list[$assoc['company_id']]['dtype'][] = 'ExpenseReportPayment';
634  $list[$assoc['company_id']]['datef'][] = $assoc['datef'];
635 
636  $list[$assoc['company_id']]['company_name'][] = '';
637  $list[$assoc['company_id']]['company_id'][] = '';
638  $list[$assoc['company_id']]['company_alias'][] = '';
639  $list[$assoc['company_id']]['company_email'][] = '';
640  $list[$assoc['company_id']]['company_tva_intra'][] = '';
641  $list[$assoc['company_id']]['company_client'][] = '';
642  $list[$assoc['company_id']]['company_fournisseur'][] = '';
643  $list[$assoc['company_id']]['company_customer_code'][] = '';
644  $list[$assoc['company_id']]['company_supplier_code'][] = '';
645  $list[$assoc['company_id']]['company_customer_accounting_code'][] = '';
646  $list[$assoc['company_id']]['company_supplier_accounting_code'][] = '';
647  $list[$assoc['company_id']]['company_status'][] = '';
648 
649  $list[$assoc['company_id']]['user_id'][] = $assoc['fk_user_author'];
650  $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
651  $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
652  $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
653 
654  $list[$assoc['company_id']]['facid'][] = $assoc['facid'];
655  $list[$assoc['company_id']]['facnum'][] = $assoc['facnum'];
656  $list[$assoc['company_id']]['type'][] = $assoc['type'];
657  $list[$assoc['company_id']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
658  $list[$assoc['company_id']]['descr'][] = $assoc['descr'];
659 
660  $list[$assoc['company_id']]['totalht_list'][] = $assoc['total_ht'];
661  $list[$assoc['company_id']]['vat_list'][] = $assoc['total_vat'];
662  $list[$assoc['company_id']]['localtax1_list'][] = $assoc['total_localtax1'];
663  $list[$assoc['company_id']]['localtax2_list'][] = $assoc['total_localtax2'];
664 
665  $list[$assoc['company_id']]['pid'][] = $assoc['pid'];
666  $list[$assoc['company_id']]['pref'][] = $assoc['pref'];
667  $list[$assoc['company_id']]['ptype'][] = 'ExpenseReportPayment';
668 
669  $list[$assoc['company_id']]['payment_id'][] = $assoc['payment_id'];
670  $list[$assoc['company_id']]['payment_ref'][] = $assoc['payment_ref'];
671  $list[$assoc['company_id']]['payment_amount'][] = $assoc['payment_amount'];
672 
673  $company_id = $assoc['company_id'];
674  }
675  } else {
676  dol_print_error($db);
677  return -3;
678  }
679  }
680  }
681 
682  return $list;
683 }
684 
701 function tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
702 {
703  global $conf;
704 
705  // If we use date_start and date_end, we must not use $y, $m, $q
706  if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
707  dol_print_error(null, 'Bad value of input parameter for tax_by_rate');
708  }
709 
710  $list = array();
711 
712  if ($direction == 'sell') {
713  $invoicetable = 'facture';
714  $invoicedettable = 'facturedet';
715  $fk_facture = 'fk_facture';
716  $fk_facture2 = 'fk_facture';
717  $fk_payment = 'fk_paiement';
718  $total_tva = 'total_tva';
719  $paymenttable = 'paiement';
720  $paymentfacturetable = 'paiement_facture';
721  $invoicefieldref = 'ref';
722  } else {
723  $invoicetable = 'facture_fourn';
724  $invoicedettable = 'facture_fourn_det';
725  $fk_facture = 'fk_facture_fourn';
726  $fk_facture2 = 'fk_facturefourn';
727  $fk_payment = 'fk_paiementfourn';
728  $total_tva = 'tva';
729  $paymenttable = 'paiementfourn';
730  $paymentfacturetable = 'paiementfourn_facturefourn';
731  $invoicefieldref = 'ref';
732  }
733 
734  if (strpos($type, 'localtax') === 0) {
735  $f_rate = $type.'_tx';
736  } else {
737  $f_rate = 'tva_tx';
738  }
739 
740  $total_localtax1 = 'total_localtax1';
741  $total_localtax2 = 'total_localtax2';
742 
743 
744  // CASE OF PRODUCTS/GOODS
745 
746  // Define sql request
747  $sql = '';
748  if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_PRODUCT') == 'invoice')
749  || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_PRODUCT') == 'invoice')) {
750  // Count on delivery date (use invoice date as delivery is unknown)
751  $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,";
752  $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
753  $sql .= " d.date_start as date_start, d.date_end as date_end,";
754  $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
755  $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,";
756  $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
757  $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
758  $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
759  $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
760  $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount,";
761  $sql .= " '' as datep";
762  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
763  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
764  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture."=f.rowid";
765  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
766  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
767  $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
768  if ($direction == 'buy') {
769  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
770  $sql .= " AND f.type IN (0,1,2,5)";
771  } else {
772  $sql .= " AND f.type IN (0,1,2,3,5)";
773  }
774  } else {
775  if (getDolGlobalString('FACTURE_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  }
781  if ($y && $m) {
782  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
783  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
784  } elseif ($y) {
785  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
786  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
787  }
788  if ($q) {
789  $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
790  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
791  }
792  if ($date_start && $date_end) {
793  $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
794  }
795  $sql .= " AND (d.product_type = 0"; // Limit to products
796  $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
797  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
798  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
799  }
800  $sql .= " ORDER BY d.rowid, d.".$fk_facture;
801  } else {
802  // Count on payments date
803  $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,";
804  $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
805  $sql .= " d.date_start as date_start, d.date_end as date_end,";
806  $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
807  $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,";
808  $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
809  $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
810  $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
811  $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
812  $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
813  $sql .= " pa.datep as datep, pa.ref as payment_ref";
814  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
815  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable." as pf ON pf.".$fk_facture2." = f.rowid";
816  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymenttable." as pa ON pa.rowid = pf.".$fk_payment;
817  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
818  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture." = f.rowid";
819  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
820  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
821  $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
822  if ($direction == 'buy') {
823  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
824  $sql .= " AND f.type IN (0,1,2,5)";
825  } else {
826  $sql .= " AND f.type IN (0,1,2,3,5)";
827  }
828  } else {
829  if (getDolGlobalString('FACTURE_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  }
835  if ($y && $m) {
836  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
837  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
838  } elseif ($y) {
839  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
840  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
841  }
842  if ($q) {
843  $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
844  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
845  }
846  if ($date_start && $date_end) {
847  $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
848  }
849  $sql .= " AND (d.product_type = 0"; // Limit to products
850  $sql .= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
851  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
852  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
853  }
854  $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
855  }
856 
857  if (!$sql) {
858  return -1;
859  }
860  if ($sql == 'TODO') {
861  return -2;
862  }
863  if ($sql != 'TODO') {
864  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
865 
866  $resql = $db->query($sql);
867  if ($resql) {
868  $rate = -1;
869  $oldrowid = '';
870  while ($assoc = $db->fetch_array($resql)) {
871  $rate_key = $assoc['rate'];
872  if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
873  $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
874  }
875 
876  // Code to avoid warnings when array entry not defined
877  if (!isset($list[$rate_key]['totalht'])) {
878  $list[$rate_key]['totalht'] = 0;
879  }
880  if (!isset($list[$rate_key]['vat'])) {
881  $list[$rate_key]['vat'] = 0;
882  }
883  if (!isset($list[$rate_key]['localtax1'])) {
884  $list[$rate_key]['localtax1'] = 0;
885  }
886  if (!isset($list[$rate_key]['localtax2'])) {
887  $list[$rate_key]['localtax2'] = 0;
888  }
889 
890  if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
891  $oldrowid = $assoc['rowid'];
892  $list[$rate_key]['totalht'] += $assoc['total_ht'];
893  $list[$rate_key]['vat'] += $assoc['total_vat'];
894  $list[$rate_key]['localtax1'] += $assoc['total_localtax1'];
895  $list[$rate_key]['localtax2'] += $assoc['total_localtax2'];
896  }
897  $list[$rate_key]['dtotal_ttc'][] = $assoc['total_ttc'];
898  $list[$rate_key]['dtype'][] = $assoc['dtype'];
899  $list[$rate_key]['datef'][] = $db->jdate($assoc['datef']);
900  $list[$rate_key]['datep'][] = $db->jdate($assoc['datep']);
901 
902  $list[$rate_key]['company_name'][] = $assoc['company_name'];
903  $list[$rate_key]['company_id'][] = $assoc['company_id'];
904  $list[$rate_key]['company_alias'][] = $assoc['company_alias'];
905  $list[$rate_key]['company_email'][] = $assoc['company_email'];
906  $list[$rate_key]['company_tva_intra'][] = $assoc['company_tva_intra'];
907  $list[$rate_key]['company_client'][] = $assoc['company_client'];
908  $list[$rate_key]['company_fournisseur'][] = $assoc['company_fournisseur'];
909  $list[$rate_key]['company_customer_code'][] = $assoc['company_customer_code'];
910  $list[$rate_key]['company_supplier_code'][] = $assoc['company_supplier_code'];
911  $list[$rate_key]['company_customer_accounting_code'][] = $assoc['company_customer_accounting_code'];
912  $list[$rate_key]['company_supplier_accounting_code'][] = $assoc['company_supplier_accounting_code'];
913  $list[$rate_key]['company_status'][] = $assoc['company_status'];
914 
915  $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
916  $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
917 
918  $list[$rate_key]['facid'][] = $assoc['facid'];
919  $list[$rate_key]['facnum'][] = $assoc['facnum'];
920  $list[$rate_key]['type'][] = $assoc['type'];
921  $list[$rate_key]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
922  $list[$rate_key]['descr'][] = $assoc['descr'];
923 
924  $list[$rate_key]['totalht_list'][] = $assoc['total_ht'];
925  $list[$rate_key]['vat_list'][] = $assoc['total_vat'];
926  $list[$rate_key]['localtax1_list'][] = $assoc['total_localtax1'];
927  $list[$rate_key]['localtax2_list'][] = $assoc['total_localtax2'];
928 
929  $list[$rate_key]['pid'][] = $assoc['pid'];
930  $list[$rate_key]['pref'][] = $assoc['pref'];
931  $list[$rate_key]['ptype'][] = $assoc['ptype'];
932 
933  $list[$rate_key]['payment_id'][] = $assoc['payment_id'];
934  $list[$rate_key]['payment_ref'][] = $assoc['payment_ref'];
935  $list[$rate_key]['payment_amount'][] = $assoc['payment_amount'];
936 
937  $rate = $assoc['rate'];
938  }
939  } else {
940  dol_print_error($db);
941  return -3;
942  }
943  }
944 
945 
946  // CASE OF SERVICES
947 
948  // Define sql request
949  $sql = '';
950  if (($direction == 'sell' && getDolGlobalString('TAX_MODE_SELL_SERVICE') == 'invoice')
951  || ($direction == 'buy' && getDolGlobalString('TAX_MODE_BUY_SERVICE') == 'invoice')) {
952  // Count on invoice date
953  $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,";
954  $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
955  $sql .= " d.date_start as date_start, d.date_end as date_end,";
956  $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
957  $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,";
958  $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
959  $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
960  $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
961  $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
962  $sql .= " 0 as payment_id, '' as payment_ref, 0 as payment_amount";
963  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
964  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
965  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture." = f.rowid";
966  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
967  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
968  $sql .= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
969  if ($direction == 'buy') {
970  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
971  $sql .= " AND f.type IN (0,1,2,5)";
972  } else {
973  $sql .= " AND f.type IN (0,1,2,3,5)";
974  }
975  } else {
976  if (getDolGlobalString('FACTURE_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  }
982  if ($y && $m) {
983  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
984  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
985  } elseif ($y) {
986  $sql .= " AND f.datef >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
987  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
988  }
989  if ($q) {
990  $sql .= " AND f.datef > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
991  $sql .= " AND f.datef <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
992  }
993  if ($date_start && $date_end) {
994  $sql .= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
995  }
996  $sql .= " AND (d.product_type = 1"; // Limit to services
997  $sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
998  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
999  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
1000  }
1001  $sql .= " ORDER BY d.rowid, d.".$fk_facture;
1002  } else {
1003  // Count on payments date
1004  $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,";
1005  $sql .= " d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
1006  $sql .= " d.date_start as date_start, d.date_end as date_end,";
1007  $sql .= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
1008  $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,";
1009  $sql .= " s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
1010  $sql .= " s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
1011  $sql .= " s.status as company_status, s.tva_intra as company_tva_intra,";
1012  $sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
1013  $sql .= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
1014  $sql .= " pa.datep as datep, pa.ref as payment_ref";
1015  $sql .= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f";
1016  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable." as pf ON pf.".$fk_facture2." = f.rowid";
1017  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$paymenttable." as pa ON pa.rowid = pf.".$fk_payment;
1018  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe as s ON s.rowid = f.fk_soc";
1019  $sql .= " INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable." as d ON d.".$fk_facture." = f.rowid";
1020  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
1021  $sql .= " WHERE f.entity IN (".getEntity($invoicetable).")";
1022  $sql .= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
1023  if ($direction == 'buy') {
1024  if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
1025  $sql .= " AND f.type IN (0,1,2,5)";
1026  } else {
1027  $sql .= " AND f.type IN (0,1,2,3,5)";
1028  }
1029  } else {
1030  if (getDolGlobalString('FACTURE_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  }
1036  if ($y && $m) {
1037  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
1038  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
1039  } elseif ($y) {
1040  $sql .= " AND pa.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
1041  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
1042  }
1043  if ($q) {
1044  $sql .= " AND pa.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
1045  $sql .= " AND pa.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
1046  }
1047  if ($date_start && $date_end) {
1048  $sql .= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
1049  }
1050  $sql .= " AND (d.product_type = 1"; // Limit to services
1051  $sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
1052  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
1053  $sql .= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
1054  }
1055  $sql .= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
1056  }
1057 
1058  if (!$sql) {
1059  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1060  return -1; // -1 = Not accountancy module enabled
1061  }
1062  if ($sql == 'TODO') {
1063  return -2; // -2 = Feature not yet available
1064  }
1065  if ($sql != 'TODO') {
1066  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
1067  $resql = $db->query($sql);
1068  if ($resql) {
1069  $rate = -1;
1070  $oldrowid = '';
1071  while ($assoc = $db->fetch_array($resql)) {
1072  $rate_key = $assoc['rate'];
1073  if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
1074  $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
1075  }
1076 
1077  // Code to avoid warnings when array entry not defined
1078  if (!isset($list[$rate_key]['totalht'])) {
1079  $list[$rate_key]['totalht'] = 0;
1080  }
1081  if (!isset($list[$rate_key]['vat'])) {
1082  $list[$rate_key]['vat'] = 0;
1083  }
1084  if (!isset($list[$rate_key]['localtax1'])) {
1085  $list[$rate_key]['localtax1'] = 0;
1086  }
1087  if (!isset($list[$rate_key]['localtax2'])) {
1088  $list[$rate_key]['localtax2'] = 0;
1089  }
1090 
1091  if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
1092  $oldrowid = $assoc['rowid'];
1093  $list[$rate_key]['totalht'] += $assoc['total_ht'];
1094  $list[$rate_key]['vat'] += $assoc['total_vat'];
1095  $list[$rate_key]['localtax1'] += $assoc['total_localtax1'];
1096  $list[$rate_key]['localtax2'] += $assoc['total_localtax2'];
1097  }
1098  $list[$rate_key]['dtotal_ttc'][] = $assoc['total_ttc'];
1099  $list[$rate_key]['dtype'][] = $assoc['dtype'];
1100  $list[$rate_key]['datef'][] = $db->jdate($assoc['datef']);
1101  $list[$rate_key]['datep'][] = $db->jdate($assoc['datep']);
1102 
1103  $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
1104  $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
1105 
1106  $list[$rate_key]['company_name'][] = $assoc['company_name'];
1107  $list[$rate_key]['company_id'][] = $assoc['company_id'];
1108  $list[$rate_key]['company_alias'][] = $assoc['company_alias'];
1109  $list[$rate_key]['company_email'][] = $assoc['company_email'];
1110  $list[$rate_key]['company_tva_intra'][] = $assoc['company_tva_intra'];
1111  $list[$rate_key]['company_client'][] = $assoc['company_client'];
1112  $list[$rate_key]['company_fournisseur'][] = $assoc['company_fournisseur'];
1113  $list[$rate_key]['company_customer_code'][] = $assoc['company_customer_code'];
1114  $list[$rate_key]['company_supplier_code'][] = $assoc['company_supplier_code'];
1115  $list[$rate_key]['company_customer_accounting_code'][] = $assoc['company_customer_accounting_code'];
1116  $list[$rate_key]['company_supplier_accounting_code'][] = $assoc['company_supplier_accounting_code'];
1117  $list[$rate_key]['company_status'][] = $assoc['company_status'];
1118 
1119  $list[$rate_key]['facid'][] = $assoc['facid'];
1120  $list[$rate_key]['facnum'][] = $assoc['facnum'];
1121  $list[$rate_key]['type'][] = $assoc['type'];
1122  $list[$rate_key]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
1123  $list[$rate_key]['descr'][] = $assoc['descr'];
1124 
1125  $list[$rate_key]['totalht_list'][] = $assoc['total_ht'];
1126  $list[$rate_key]['vat_list'][] = $assoc['total_vat'];
1127  $list[$rate_key]['localtax1_list'][] = $assoc['total_localtax1'];
1128  $list[$rate_key]['localtax2_list'][] = $assoc['total_localtax2'];
1129 
1130  $list[$rate_key]['pid'][] = $assoc['pid'];
1131  $list[$rate_key]['pref'][] = $assoc['pref'];
1132  $list[$rate_key]['ptype'][] = $assoc['ptype'];
1133 
1134  $list[$rate_key]['payment_id'][] = $assoc['payment_id'];
1135  $list[$rate_key]['payment_ref'][] = $assoc['payment_ref'];
1136  $list[$rate_key]['payment_amount'][] = $assoc['payment_amount'];
1137 
1138  $rate = $assoc['rate'];
1139  }
1140  } else {
1141  dol_print_error($db);
1142  return -3;
1143  }
1144  }
1145 
1146 
1147  // CASE OF EXPENSE REPORT
1148 
1149  if ($direction == 'buy') { // buy only for expense reports
1150  // Define sql request
1151  $sql = '';
1152 
1153  // Count on payments date
1154  $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,";
1155  $sql .= " d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
1156  $sql .= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
1157  $sql .= " e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
1158  $sql .= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
1159  $sql .= " FROM ".MAIN_DB_PREFIX."expensereport as e";
1160  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid";
1161  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid";
1162  $sql .= " WHERE e.entity = ".$conf->entity;
1163  $sql .= " AND e.fk_statut in (6)";
1164  if ($y && $m) {
1165  $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, $m, false))."'";
1166  $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, $m, false))."'";
1167  } elseif ($y) {
1168  $sql .= " AND p.datep >= '".$db->idate(dol_get_first_day($y, 1, false))."'";
1169  $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, 12, false))."'";
1170  }
1171  if ($q) {
1172  $sql .= " AND p.datep > '".$db->idate(dol_get_first_day($y, (($q - 1) * 3) + 1, false))."'";
1173  $sql .= " AND p.datep <= '".$db->idate(dol_get_last_day($y, ($q * 3), false))."'";
1174  }
1175  if ($date_start && $date_end) {
1176  $sql .= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
1177  }
1178  $sql .= " AND (d.product_type = -1";
1179  $sql .= " OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)"; // enhance detection of service
1180  if (getDolGlobalString('MAIN_NOT_INCLUDE_ZERO_VAT_IN_REPORTS')) {
1181  $sql .= " AND (d.".$f_rate." <> 0 OR d.total_tva <> 0)";
1182  }
1183  $sql .= " ORDER BY e.rowid";
1184 
1185  if (!$sql) {
1186  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1187  return -1; // -1 = Not accountancy module enabled
1188  }
1189  if ($sql == 'TODO') {
1190  return -2; // -2 = Feature not yet available
1191  }
1192  if ($sql != 'TODO') {
1193  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
1194  $resql = $db->query($sql);
1195  if ($resql) {
1196  $rate = -1;
1197  $oldrowid = '';
1198  while ($assoc = $db->fetch_array($resql)) {
1199  $rate_key = $assoc['rate'];
1200  if ($f_rate == 'tva_tx' && !empty($assoc['vat_src_code']) && !preg_match('/\‍(/', $rate_key)) {
1201  $rate_key .= ' (' . $assoc['vat_src_code'] . ')';
1202  }
1203 
1204  // Code to avoid warnings when array entry not defined
1205  if (!isset($list[$rate_key]['totalht'])) {
1206  $list[$rate_key]['totalht'] = 0;
1207  }
1208  if (!isset($list[$rate_key]['vat'])) {
1209  $list[$rate_key]['vat'] = 0;
1210  }
1211  if (!isset($list[$rate_key]['localtax1'])) {
1212  $list[$rate_key]['localtax1'] = 0;
1213  }
1214  if (!isset($list[$rate_key]['localtax2'])) {
1215  $list[$rate_key]['localtax2'] = 0;
1216  }
1217 
1218  if ($assoc['rowid'] != $oldrowid) { // Si rupture sur d.rowid
1219  $oldrowid = $assoc['rowid'];
1220  $list[$rate_key]['totalht'] += $assoc['total_ht'];
1221  $list[$rate_key]['vat'] += $assoc['total_vat'];
1222  $list[$rate_key]['localtax1'] += $assoc['total_localtax1'];
1223  $list[$rate_key]['localtax2'] += $assoc['total_localtax2'];
1224  }
1225 
1226  $list[$rate_key]['dtotal_ttc'][] = $assoc['total_ttc'];
1227  $list[$rate_key]['dtype'][] = 'ExpenseReportPayment';
1228  $list[$rate_key]['datef'][] = $assoc['datef'];
1229  $list[$rate_key]['company_name'][] = '';
1230  $list[$rate_key]['company_id'][] = '';
1231  $list[$rate_key]['user_id'][] = $assoc['fk_user_author'];
1232  $list[$rate_key]['ddate_start'][] = $db->jdate($assoc['date_start']);
1233  $list[$rate_key]['ddate_end'][] = $db->jdate($assoc['date_end']);
1234 
1235  $list[$rate_key]['facid'][] = $assoc['facid'];
1236  $list[$rate_key]['facnum'][] = $assoc['facnum'];
1237  $list[$rate_key]['type'][] = $assoc['type'];
1238  $list[$rate_key]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
1239  $list[$rate_key]['descr'][] = $assoc['descr'];
1240 
1241  $list[$rate_key]['totalht_list'][] = $assoc['total_ht'];
1242  $list[$rate_key]['vat_list'][] = $assoc['total_vat'];
1243  $list[$rate_key]['localtax1_list'][] = $assoc['total_localtax1'];
1244  $list[$rate_key]['localtax2_list'][] = $assoc['total_localtax2'];
1245 
1246  $list[$rate_key]['pid'][] = $assoc['pid'];
1247  $list[$rate_key]['pref'][] = $assoc['pref'];
1248  $list[$rate_key]['ptype'][] = 'ExpenseReportPayment';
1249 
1250  $list[$rate_key]['payment_id'][] = $assoc['payment_id'];
1251  $list[$rate_key]['payment_ref'][] = $assoc['payment_ref'];
1252  $list[$rate_key]['payment_amount'][] = $assoc['payment_amount'];
1253 
1254  $rate = $assoc['rate'];
1255  }
1256  } else {
1257  dol_print_error($db);
1258  return -3;
1259  }
1260  }
1261  }
1262 
1263  return $list;
1264 }
if($user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition: card.php:58
Class for managing the social charges.
if(isModEnabled('invoice') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&!getDolGlobalString('MAIN_USE_NEW_SUPPLIERMOD') && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') && $user->hasRight('tax', 'charges', 'lire')) if(isModEnabled('invoice') &&isModEnabled('order') && $user->hasRight("commande", "lire") &&!getDolGlobalString('WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER')) $sql
Social contributions to pay.
Definition: index.php:744
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:594
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:613
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($str, $newstr='_', $unaccent=1)
Clean a string to use it as a file name.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
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).
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
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:701
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
tax_prepare_head(ChargeSociales $object)
Prepare array with list of tabs.
Definition: tax.lib.php:38