dolibarr  9.0.0
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  *
10  * This program is free software; you can redistribute it and/or modify
11  * it under the terms of the GNU General Public License as published by
12  * the Free Software Foundation; either version 3 of the License, or
13  * (at your option) any later version.
14  *
15  * This program is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18  * GNU General Public License for more details.
19  *
20  * You should have received a copy of the GNU General Public License
21  * along with this program. If not, see <http://www.gnu.org/licenses/>.
22  */
23 
38 {
39  global $db, $langs, $conf, $user;
40 
41  $h = 0;
42  $head = array();
43 
44  $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/card.php?id='.$object->id;
45  $head[$h][1] = $langs->trans('Card');
46  $head[$h][2] = 'card';
47  $h++;
48 
49  // Show more tabs from modules
50  // Entries must be declared in modules descriptor with line
51  // $this->tabs = array('entity:+tabname:Title:@mymodule:/mymodule/mypage.php?id=__ID__'); to add new tab
52  // $this->tabs = array('entity:-tabname); to remove a tab
53  complete_head_from_modules($conf,$langs,$object,$head,$h,'tax');
54 
55  require_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php';
56  require_once DOL_DOCUMENT_ROOT.'/core/class/link.class.php';
57  $upload_dir = $conf->tax->dir_output . "/" . dol_sanitizeFileName($object->ref);
58  $nbFiles = count(dol_dir_list($upload_dir,'files',0,'','(\.meta|_preview.*\.png)$'));
59  $nbLinks=Link::count($db, $object->element, $object->id);
60  $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/document.php?id='.$object->id;
61  $head[$h][1] = $langs->trans("Documents");
62  if (($nbFiles+$nbLinks) > 0) $head[$h][1].= ' <span class="badge">'.($nbFiles+$nbLinks).'</span>';
63  $head[$h][2] = 'documents';
64  $h++;
65 
66  $head[$h][0] = DOL_URL_ROOT.'/compta/sociales/info.php?id='.$object->id;
67  $head[$h][1] = $langs->trans("Info");
68  $head[$h][2] = 'info';
69  $h++;
70 
71  complete_head_from_modules($conf,$langs,$object,$head,$h,'tax','remove');
72 
73  return $head;
74 }
75 
76 
91 function tax_by_thirdparty($type, $db, $y, $date_start, $date_end, $modetax, $direction, $m=0, $q=0)
92 {
93  global $conf;
94 
95  // If we use date_start and date_end, we must not use $y, $m, $q
96  if (($date_start || $date_end) && (! empty($y) || ! empty($m) || ! empty($q)))
97  {
98  dol_print_error('', 'Bad value of input parameter for tax_by_rate');
99  }
100 
101  $list=array();
102  if ($direction == 'sell')
103  {
104  $invoicetable='facture';
105  $invoicedettable='facturedet';
106  $fk_facture='fk_facture';
107  $fk_facture2='fk_facture';
108  $fk_payment='fk_paiement';
109  $total_tva='total_tva';
110  $paymenttable='paiement';
111  $paymentfacturetable='paiement_facture';
112  $invoicefieldref='facnumber';
113  }
114  if ($direction == 'buy')
115  {
116  $invoicetable='facture_fourn';
117  $invoicedettable='facture_fourn_det';
118  $fk_facture='fk_facture_fourn';
119  $fk_facture2='fk_facturefourn';
120  $fk_payment='fk_paiementfourn';
121  $total_tva='tva';
122  $paymenttable='paiementfourn';
123  $paymentfacturetable='paiementfourn_facturefourn';
124  $invoicefieldref='ref';
125  }
126 
127  if ( strpos( $type, 'localtax' ) === 0 ) {
128  $f_rate = $type . '_tx';
129  } else {
130  $f_rate = 'tva_tx';
131  }
132 
133  $total_localtax1='total_localtax1';
134  $total_localtax2='total_localtax2';
135 
136 
137  // CAS DES BIENS/PRODUITS
138 
139  // Define sql request
140  $sql='';
141  if (($direction == 'sell' && $conf->global->TAX_MODE_SELL_PRODUCT == 'invoice')
142  || ($direction == 'buy' && $conf->global->TAX_MODE_BUY_PRODUCT == 'invoice'))
143  {
144  // Count on delivery date (use invoice date as delivery is unknown)
145  $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,";
146  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
147  $sql.= " d.date_start as date_start, d.date_end as date_end,";
148  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
149  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
150  $sql.= " 0 as payment_id, 0 as payment_amount";
151  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
152  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
153  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
154  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
155  $sql.= " WHERE f.entity = " . $conf->entity;
156  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
157  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
158  else $sql.= " AND f.type IN (0,1,2,3,5)";
159  $sql.= " AND f.rowid = d.".$fk_facture;
160  $sql.= " AND s.rowid = f.fk_soc";
161  if ($y && $m)
162  {
163  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
164  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
165  }
166  else if ($y)
167  {
168  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
169  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
170  }
171  if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
172  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
173  $sql.= " AND (d.product_type = 0"; // Limit to products
174  $sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
175  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
176  $sql.= " ORDER BY d.rowid, d.".$fk_facture;
177  }
178  else
179  {
180  // Count on payments date
181  $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,";
182  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
183  $sql.= " d.date_start as date_start, d.date_end as date_end,";
184  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
185  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
186  $sql.= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
187  $sql.= " pa.datep as datep";
188  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
189  $sql.= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
190  $sql.= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
191  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
192  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
193  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
194  $sql.= " WHERE f.entity = " . $conf->entity;
195  $sql.= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
196  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
197  else $sql.= " AND f.type IN (0,1,2,3,5)";
198  $sql.= " AND f.rowid = d.".$fk_facture;
199  $sql.= " AND s.rowid = f.fk_soc";
200  $sql.= " AND pf.".$fk_facture2." = f.rowid";
201  $sql.= " AND pa.rowid = pf.".$fk_payment;
202  if ($y && $m)
203  {
204  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
205  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
206  }
207  else if ($y)
208  {
209  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
210  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
211  }
212  if ($q) $sql.= " AND (date_format(pa.datep,'%m') > ".(($q-1)*3)." AND date_format(pa.datep,'%m') <= ".($q*3).")";
213  if ($date_start && $date_end) $sql.= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
214  $sql.= " AND (d.product_type = 0"; // Limit to products
215  $sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
216  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
217  $sql.= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
218  }
219 
220  if (! $sql) return -1;
221  if ($sql == 'TODO') return -2;
222  if ($sql != 'TODO')
223  {
224  dol_syslog("Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
225 
226  $resql = $db->query($sql);
227  if ($resql)
228  {
229  $company_id = -1;
230  $oldrowid='';
231  while($assoc = $db->fetch_array($resql))
232  {
233  if (! isset($list[$assoc['company_id']]['totalht'])) $list[$assoc['company_id']]['totalht']=0;
234  if (! isset($list[$assoc['company_id']]['vat'])) $list[$assoc['company_id']]['vat']=0;
235  if (! isset($list[$assoc['company_id']]['localtax1'])) $list[$assoc['company_id']]['localtax1']=0;
236  if (! isset($list[$assoc['company_id']]['localtax2'])) $list[$assoc['company_id']]['localtax2']=0;
237 
238  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
239  {
240  $oldrowid=$assoc['rowid'];
241  $list[$assoc['company_id']]['totalht'] += $assoc['total_ht'];
242  $list[$assoc['company_id']]['vat'] += $assoc['total_vat'];
243  $list[$assoc['company_id']]['localtax1'] += $assoc['total_localtax1'];
244  $list[$assoc['company_id']]['localtax2'] += $assoc['total_localtax2'];
245  }
246  $list[$assoc['company_id']]['dtotal_ttc'][] = $assoc['total_ttc'];
247  $list[$assoc['company_id']]['dtype'][] = $assoc['dtype'];
248  $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
249  $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
250  $list[$assoc['company_id']]['company_name'][] = $assoc['company_name'];
251  $list[$assoc['company_id']]['company_id'][] = $assoc['company_id'];
252  $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
253  $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
254  $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
255 
256  $list[$assoc['company_id']]['facid'][] = $assoc['facid'];
257  $list[$assoc['company_id']]['facnum'][] = $assoc['facnum'];
258  $list[$assoc['company_id']]['type'][] = $assoc['type'];
259  $list[$assoc['company_id']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
260  $list[$assoc['company_id']]['descr'][] = $assoc['descr'];
261 
262  $list[$assoc['company_id']]['totalht_list'][] = $assoc['total_ht'];
263  $list[$assoc['company_id']]['vat_list'][] = $assoc['total_vat'];
264  $list[$assoc['company_id']]['localtax1_list'][] = $assoc['total_localtax1'];
265  $list[$assoc['company_id']]['localtax2_list'][] = $assoc['total_localtax2'];
266 
267  $list[$assoc['company_id']]['pid'][] = $assoc['pid'];
268  $list[$assoc['company_id']]['pref'][] = $assoc['pref'];
269  $list[$assoc['company_id']]['ptype'][] = $assoc['ptype'];
270 
271  $list[$assoc['company_id']]['payment_id'][] = $assoc['payment_id'];
272  $list[$assoc['company_id']]['payment_amount'][] = $assoc['payment_amount'];
273 
274  $company_id = $assoc['company_id'];
275  }
276  }
277  else
278  {
279  dol_print_error($db);
280  return -3;
281  }
282  }
283 
284 
285  // CAS DES SERVICES
286 
287  // Define sql request
288  $sql='';
289  if (($direction == 'sell' && $conf->global->TAX_MODE_SELL_SERVICE == 'invoice')
290  || ($direction == 'buy' && $conf->global->TAX_MODE_BUY_SERVICE == 'invoice'))
291  {
292  // Count on invoice date
293  $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,";
294  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
295  $sql.= " d.date_start as date_start, d.date_end as date_end,";
296  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
297  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
298  $sql.= " 0 as payment_id, 0 as payment_amount";
299  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
300  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
301  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
302  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
303  $sql.= " WHERE f.entity = " . $conf->entity;
304  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
305  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
306  else $sql.= " AND f.type IN (0,1,2,3,5)";
307  $sql.= " AND f.rowid = d.".$fk_facture;
308  $sql.= " AND s.rowid = f.fk_soc";
309  if ($y && $m)
310  {
311  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
312  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
313  }
314  else if ($y)
315  {
316  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
317  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
318  }
319  if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
320  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
321  $sql.= " AND (d.product_type = 1"; // Limit to services
322  $sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
323  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
324  $sql.= " ORDER BY d.rowid, d.".$fk_facture;
325  }
326  else
327  {
328  // Count on payments date
329  $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,";
330  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
331  $sql.= " d.date_start as date_start, d.date_end as date_end,";
332  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
333  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
334  $sql.= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
335  $sql.= " pa.datep as datep";
336  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
337  $sql.= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
338  $sql.= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
339  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
340  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
341  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
342  $sql.= " WHERE f.entity = " . $conf->entity;
343  $sql.= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
344  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
345  else $sql.= " AND f.type IN (0,1,2,3,5)";
346  $sql.= " AND f.rowid = d.".$fk_facture;
347  $sql.= " AND s.rowid = f.fk_soc";
348  $sql.= " AND pf.".$fk_facture2." = f.rowid";
349  $sql.= " AND pa.rowid = pf.".$fk_payment;
350  if ($y && $m)
351  {
352  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
353  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
354  }
355  else if ($y)
356  {
357  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
358  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
359  }
360  if ($q) $sql.= " AND (date_format(pa.datep,'%m') > ".(($q-1)*3)." AND date_format(pa.datep,'%m') <= ".($q*3).")";
361  if ($date_start && $date_end) $sql.= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
362  $sql.= " AND (d.product_type = 1"; // Limit to services
363  $sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
364  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
365  $sql.= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
366  }
367 
368  if (! $sql)
369  {
370  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql,LOG_ERR);
371  return -1; // -1 = Not accountancy module enabled
372  }
373  if ($sql == 'TODO') return -2; // -2 = Feature not yet available
374  if ($sql != 'TODO')
375  {
376  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
377  $resql = $db->query($sql);
378  if ($resql)
379  {
380  $company_id = -1;
381  $oldrowid='';
382  while($assoc = $db->fetch_array($resql))
383  {
384  if (! isset($list[$assoc['company_id']]['totalht'])) $list[$assoc['company_id']]['totalht']=0;
385  if (! isset($list[$assoc['company_id']]['vat'])) $list[$assoc['company_id']]['vat']=0;
386  if (! isset($list[$assoc['company_id']]['localtax1'])) $list[$assoc['company_id']]['localtax1']=0;
387  if (! isset($list[$assoc['company_id']]['localtax2'])) $list[$assoc['company_id']]['localtax2']=0;
388 
389  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
390  {
391  $oldrowid=$assoc['rowid'];
392  $list[$assoc['company_id']]['totalht'] += $assoc['total_ht'];
393  $list[$assoc['company_id']]['vat'] += $assoc['total_vat'];
394  $list[$assoc['company_id']]['localtax1'] += $assoc['total_localtax1'];
395  $list[$assoc['company_id']]['localtax2'] += $assoc['total_localtax2'];
396  }
397  $list[$assoc['company_id']]['dtotal_ttc'][] = $assoc['total_ttc'];
398  $list[$assoc['company_id']]['dtype'][] = $assoc['dtype'];
399  $list[$assoc['company_id']]['datef'][] = $db->jdate($assoc['datef']);
400  $list[$assoc['company_id']]['datep'][] = $db->jdate($assoc['datep']);
401  $list[$assoc['company_id']]['company_name'][] = $assoc['company_name'];
402  $list[$assoc['company_id']]['company_id'][] = $assoc['company_id'];
403  $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
404  $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
405  $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
406 
407  $list[$assoc['company_id']]['facid'][] = $assoc['facid'];
408  $list[$assoc['company_id']]['facnum'][] = $assoc['facnum'];
409  $list[$assoc['company_id']]['type'][] = $assoc['type'];
410  $list[$assoc['company_id']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
411  $list[$assoc['company_id']]['descr'][] = $assoc['descr'];
412 
413  $list[$assoc['company_id']]['totalht_list'][] = $assoc['total_ht'];
414  $list[$assoc['company_id']]['vat_list'][] = $assoc['total_vat'];
415  $list[$assoc['company_id']]['localtax1_list'][] = $assoc['total_localtax1'];
416  $list[$assoc['company_id']]['localtax2_list'][] = $assoc['total_localtax2'];
417 
418  $list[$assoc['company_id']]['pid'][] = $assoc['pid'];
419  $list[$assoc['company_id']]['pref'][] = $assoc['pref'];
420  $list[$assoc['company_id']]['ptype'][] = $assoc['ptype'];
421 
422  $list[$assoc['company_id']]['payment_id'][] = $assoc['payment_id'];
423  $list[$assoc['company_id']]['payment_amount'][] = $assoc['payment_amount'];
424 
425  $company_id = $assoc['company_id'];
426  }
427  }
428  else
429  {
430  dol_print_error($db);
431  return -3;
432  }
433  }
434 
435 
436  // CASE OF EXPENSE REPORT
437 
438  if ($direction == 'buy') // buy only for expense reports
439  {
440  // Define sql request
441  $sql='';
442 
443  // Count on payments date
444  $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,";
445  $sql .=" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
446  $sql.= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
447  $sql.= " e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
448  $sql.= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
449  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as e";
450  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid ";
451  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid ";
452  $sql.= " WHERE e.entity = " . $conf->entity;
453  $sql.= " AND e.fk_statut in (6)";
454  if ($y && $m)
455  {
456  $sql.= " AND p.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
457  $sql.= " AND p.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
458  }
459  else if ($y)
460  {
461  $sql.= " AND p.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
462  $sql.= " AND p.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
463  }
464  if ($q) $sql.= " AND (date_format(p.datep,'%m') > ".(($q-1)*3)." AND date_format(p.datep,'%m') <= ".($q*3).")";
465  if ($date_start && $date_end) $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
466  $sql.= " AND (d.product_type = -1";
467  $sql.= " OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)"; // enhance detection of service
468  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.total_tva <> 0)";
469  $sql.= " ORDER BY e.rowid";
470 
471  if (! $sql)
472  {
473  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql,LOG_ERR);
474  return -1; // -1 = Not accountancy module enabled
475  }
476  if ($sql == 'TODO') return -2; // -2 = Feature not yet available
477  if ($sql != 'TODO')
478  {
479  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
480  $resql = $db->query($sql);
481  if ($resql)
482  {
483  $company_id = -1;
484  $oldrowid='';
485  while($assoc = $db->fetch_array($resql))
486  {
487  if (! isset($list[$assoc['company_id']]['totalht'])) $list[$assoc['company_id']]['totalht']=0;
488  if (! isset($list[$assoc['company_id']]['vat'])) $list[$assoc['company_id']]['vat']=0;
489  if (! isset($list[$assoc['company_id']]['localtax1'])) $list[$assoc['company_id']]['localtax1']=0;
490  if (! isset($list[$assoc['company_id']]['localtax2'])) $list[$assoc['company_id']]['localtax2']=0;
491 
492  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
493  {
494  $oldrowid=$assoc['rowid'];
495  $list[$assoc['company_id']]['totalht'] += $assoc['total_ht'];
496  $list[$assoc['company_id']]['vat'] += $assoc['total_vat'];
497  $list[$assoc['company_id']]['localtax1'] += $assoc['total_localtax1'];
498  $list[$assoc['company_id']]['localtax2'] += $assoc['total_localtax2'];
499  }
500 
501  $list[$assoc['company_id']]['dtotal_ttc'][] = $assoc['total_ttc'];
502  $list[$assoc['company_id']]['dtype'][] = 'ExpenseReportPayment';
503  $list[$assoc['company_id']]['datef'][] = $assoc['datef'];
504  $list[$assoc['company_id']]['company_name'][] = '';
505  $list[$assoc['company_id']]['company_id'][] = '';
506  $list[$assoc['company_id']]['user_id'][] = $assoc['fk_user_author'];
507  $list[$assoc['company_id']]['drate'][] = $assoc['rate'];
508  $list[$assoc['company_id']]['ddate_start'][] = $db->jdate($assoc['date_start']);
509  $list[$assoc['company_id']]['ddate_end'][] = $db->jdate($assoc['date_end']);
510 
511  $list[$assoc['company_id']]['facid'][] = $assoc['facid'];
512  $list[$assoc['company_id']]['facnum'][] = $assoc['facnum'];
513  $list[$assoc['company_id']]['type'][] = $assoc['type'];
514  $list[$assoc['company_id']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
515  $list[$assoc['company_id']]['descr'][] = $assoc['descr'];
516 
517  $list[$assoc['company_id']]['totalht_list'][] = $assoc['total_ht'];
518  $list[$assoc['company_id']]['vat_list'][] = $assoc['total_vat'];
519  $list[$assoc['company_id']]['localtax1_list'][] = $assoc['total_localtax1'];
520  $list[$assoc['company_id']]['localtax2_list'][] = $assoc['total_localtax2'];
521 
522  $list[$assoc['company_id']]['pid'][] = $assoc['pid'];
523  $list[$assoc['company_id']]['pref'][] = $assoc['pref'];
524  $list[$assoc['company_id']]['ptype'][] = 'ExpenseReportPayment';
525 
526  $list[$assoc['company_id']]['payment_id'][] = $assoc['payment_id'];
527  $list[$assoc['company_id']]['payment_amount'][] = $assoc['payment_amount'];
528 
529  $company_id = $assoc['company_id'];
530  }
531  }
532  else
533  {
534  dol_print_error($db);
535  return -3;
536  }
537  }
538  }
539 
540  return $list;
541 }
542 
559 function tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m=0)
560 {
561  global $conf;
562 
563  // If we use date_start and date_end, we must not use $y, $m, $q
564  if (($date_start || $date_end) && (! empty($y) || ! empty($m) || ! empty($q)))
565  {
566  dol_print_error('', 'Bad value of input parameter for tax_by_rate');
567  }
568 
569  $list=array();
570 
571  if ($direction == 'sell')
572  {
573  $invoicetable='facture';
574  $invoicedettable='facturedet';
575  $fk_facture='fk_facture';
576  $fk_facture2='fk_facture';
577  $fk_payment='fk_paiement';
578  $total_tva='total_tva';
579  $paymenttable='paiement';
580  $paymentfacturetable='paiement_facture';
581  $invoicefieldref='facnumber';
582  }
583  else
584  {
585  $invoicetable='facture_fourn';
586  $invoicedettable='facture_fourn_det';
587  $fk_facture='fk_facture_fourn';
588  $fk_facture2='fk_facturefourn';
589  $fk_payment='fk_paiementfourn';
590  $total_tva='tva';
591  $paymenttable='paiementfourn';
592  $paymentfacturetable='paiementfourn_facturefourn';
593  $invoicefieldref='ref';
594  }
595 
596  if ( strpos( $type, 'localtax' ) === 0 ) {
597  $f_rate = $type . '_tx';
598  } else {
599  $f_rate = 'tva_tx';
600  }
601 
602  $total_localtax1='total_localtax1';
603  $total_localtax2='total_localtax2';
604 
605 
606  // CAS DES BIENS/PRODUITS
607 
608  // Define sql request
609  $sql='';
610  if (($direction == 'sell' && $conf->global->TAX_MODE_SELL_PRODUCT == 'invoice')
611  || ($direction == 'buy' && $conf->global->TAX_MODE_BUY_PRODUCT == 'invoice'))
612  {
613  // Count on delivery date (use invoice date as delivery is unknown)
614  $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,";
615  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
616  $sql.= " d.date_start as date_start, d.date_end as date_end,";
617  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
618  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
619  $sql.= " 0 as payment_id, 0 as payment_amount";
620  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
621  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
622  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
623  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
624  $sql.= " WHERE f.entity = " . $conf->entity;
625  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
626  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
627  else $sql.= " AND f.type IN (0,1,2,3,5)";
628  $sql.= " AND f.rowid = d.".$fk_facture;
629  $sql.= " AND s.rowid = f.fk_soc";
630  if ($y && $m)
631  {
632  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
633  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
634  }
635  else if ($y)
636  {
637  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
638  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
639  }
640  if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
641  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
642  $sql.= " AND (d.product_type = 0"; // Limit to products
643  $sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
644  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
645  $sql.= " ORDER BY d.rowid, d.".$fk_facture;
646  }
647  else
648  {
649  // Count on payments date
650  $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,";
651  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
652  $sql.= " d.date_start as date_start, d.date_end as date_end,";
653  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
654  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
655  $sql.= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
656  $sql.= " pa.datep as datep";
657  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
658  $sql.= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
659  $sql.= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
660  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
661  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
662  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
663  $sql.= " WHERE f.entity = " . $conf->entity;
664  $sql.= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
665  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
666  else $sql.= " AND f.type IN (0,1,2,3,5)";
667  $sql.= " AND f.rowid = d.".$fk_facture;
668  $sql.= " AND s.rowid = f.fk_soc";
669  $sql.= " AND pf.".$fk_facture2." = f.rowid";
670  $sql.= " AND pa.rowid = pf.".$fk_payment;
671  if ($y && $m)
672  {
673  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
674  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
675  }
676  else if ($y)
677  {
678  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
679  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
680  }
681  if ($q) $sql.= " AND (date_format(pa.datep,'%m') > ".(($q-1)*3)." AND date_format(pa.datep,'%m') <= ".($q*3).")";
682  if ($date_start && $date_end) $sql.= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
683  $sql.= " AND (d.product_type = 0"; // Limit to products
684  $sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of products
685  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
686  $sql.= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
687  }
688 
689  if (! $sql) return -1;
690  if ($sql == 'TODO') return -2;
691  if ($sql != 'TODO')
692  {
693  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
694 
695  $resql = $db->query($sql);
696  if ($resql)
697  {
698  $rate = -1;
699  $oldrowid='';
700  while($assoc = $db->fetch_array($resql))
701  {
702  // Code to avoid warnings when array entry not defined
703  if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
704  if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
705  if (! isset($list[$assoc['rate']]['localtax1'])) $list[$assoc['rate']]['localtax1']=0;
706  if (! isset($list[$assoc['rate']]['localtax2'])) $list[$assoc['rate']]['localtax2']=0;
707 
708  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
709  {
710  $oldrowid=$assoc['rowid'];
711  $list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
712  $list[$assoc['rate']]['vat'] += $assoc['total_vat'];
713  $list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
714  $list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
715  }
716  $list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
717  $list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
718  $list[$assoc['rate']]['datef'][] = $db->jdate($assoc['datef']);
719  $list[$assoc['rate']]['datep'][] = $db->jdate($assoc['datep']);
720  $list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
721  $list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
722  $list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
723  $list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
724 
725  $list[$assoc['rate']]['facid'][] = $assoc['facid'];
726  $list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
727  $list[$assoc['rate']]['type'][] = $assoc['type'];
728  $list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
729  $list[$assoc['rate']]['descr'][] = $assoc['descr'];
730 
731  $list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
732  $list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
733  $list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
734  $list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
735 
736  $list[$assoc['rate']]['pid'][] = $assoc['pid'];
737  $list[$assoc['rate']]['pref'][] = $assoc['pref'];
738  $list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
739 
740  $list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
741  $list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
742 
743  $rate = $assoc['rate'];
744  }
745  }
746  else
747  {
748  dol_print_error($db);
749  return -3;
750  }
751  }
752 
753 
754  // CAS DES SERVICES
755 
756  // Define sql request
757  $sql='';
758  if (($direction == 'sell' && $conf->global->TAX_MODE_SELL_SERVICE == 'invoice')
759  || ($direction == 'buy' && $conf->global->TAX_MODE_BUY_SERVICE == 'invoice'))
760  {
761  // Count on invoice date
762  $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,";
763  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
764  $sql.= " d.date_start as date_start, d.date_end as date_end,";
765  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
766  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
767  $sql.= " 0 as payment_id, 0 as payment_amount";
768  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
769  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
770  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
771  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
772  $sql.= " WHERE f.entity = " . $conf->entity;
773  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
774  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
775  else $sql.= " AND f.type IN (0,1,2,3,5)";
776  $sql.= " AND f.rowid = d.".$fk_facture;
777  $sql.= " AND s.rowid = f.fk_soc";
778  if ($y && $m)
779  {
780  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
781  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
782  }
783  else if ($y)
784  {
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) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
789  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
790  $sql.= " AND (d.product_type = 1"; // Limit to services
791  $sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
792  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
793  $sql.= " ORDER BY d.rowid, d.".$fk_facture;
794  }
795  else
796  {
797  // Count on payments date
798  $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,";
799  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
800  $sql.= " d.date_start as date_start, d.date_end as date_end,";
801  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
802  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
803  $sql.= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount,";
804  $sql.= " pa.datep as datep";
805  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
806  $sql.= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
807  $sql.= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
808  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
809  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
810  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
811  $sql.= " WHERE f.entity = " . $conf->entity;
812  $sql.= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
813  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
814  else $sql.= " AND f.type IN (0,1,2,3,5)";
815  $sql.= " AND f.rowid = d.".$fk_facture;
816  $sql.= " AND s.rowid = f.fk_soc";
817  $sql.= " AND pf.".$fk_facture2." = f.rowid";
818  $sql.= " AND pa.rowid = pf.".$fk_payment;
819  if ($y && $m)
820  {
821  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
822  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
823  }
824  else if ($y)
825  {
826  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
827  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
828  }
829  if ($q) $sql.= " AND (date_format(pa.datep,'%m') > ".(($q-1)*3)." AND date_format(pa.datep,'%m') <= ".($q*3).")";
830  if ($date_start && $date_end) $sql.= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
831  $sql.= " AND (d.product_type = 1"; // Limit to services
832  $sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
833  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.".$total_tva." <> 0)";
834  $sql.= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
835  }
836 
837  if (! $sql)
838  {
839  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql,LOG_ERR);
840  return -1; // -1 = Not accountancy module enabled
841  }
842  if ($sql == 'TODO') return -2; // -2 = Feature not yet available
843  if ($sql != 'TODO')
844  {
845  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
846  $resql = $db->query($sql);
847  if ($resql)
848  {
849  $rate = -1;
850  $oldrowid='';
851  while($assoc = $db->fetch_array($resql))
852  {
853  // Code to avoid warnings when array entry not defined
854  if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
855  if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
856  if (! isset($list[$assoc['rate']]['localtax1'])) $list[$assoc['rate']]['localtax1']=0;
857  if (! isset($list[$assoc['rate']]['localtax2'])) $list[$assoc['rate']]['localtax2']=0;
858 
859  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
860  {
861  $oldrowid=$assoc['rowid'];
862  $list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
863  $list[$assoc['rate']]['vat'] += $assoc['total_vat'];
864  $list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
865  $list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
866  }
867  $list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
868  $list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
869  $list[$assoc['rate']]['datef'][] = $db->jdate($assoc['datef']);
870  $list[$assoc['rate']]['datep'][] = $db->jdate($assoc['datep']);
871  $list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
872  $list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
873  $list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
874  $list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
875 
876  $list[$assoc['rate']]['facid'][] = $assoc['facid'];
877  $list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
878  $list[$assoc['rate']]['type'][] = $assoc['type'];
879  $list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
880  $list[$assoc['rate']]['descr'][] = $assoc['descr'];
881 
882  $list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
883  $list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
884  $list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
885  $list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
886 
887  $list[$assoc['rate']]['pid'][] = $assoc['pid'];
888  $list[$assoc['rate']]['pref'][] = $assoc['pref'];
889  $list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
890 
891  $list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
892  $list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
893 
894  $rate = $assoc['rate'];
895  }
896  }
897  else
898  {
899  dol_print_error($db);
900  return -3;
901  }
902  }
903 
904 
905  // CASE OF EXPENSE REPORT
906 
907  if ($direction == 'buy') // buy only for expense reports
908  {
909  // Define sql request
910  $sql='';
911 
912  // Count on payments date
913  $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,";
914  $sql .=" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
915  $sql.= " e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
916  $sql.= " e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
917  $sql.= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
918  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as e ";
919  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid ";
920  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid ";
921  $sql.= " WHERE e.entity = " . $conf->entity;
922  $sql.= " AND e.fk_statut in (6)";
923  if ($y && $m)
924  {
925  $sql.= " AND p.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
926  $sql.= " AND p.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
927  }
928  else if ($y)
929  {
930  $sql.= " AND p.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
931  $sql.= " AND p.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
932  }
933  if ($q) $sql.= " AND (date_format(p.datep,'%m') > ".(($q-1)*3)." AND date_format(p.datep,'%m') <= ".($q*3).")";
934  if ($date_start && $date_end) $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
935  $sql.= " AND (d.product_type = -1";
936  $sql.= " OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)"; // enhance detection of service
937  if (empty($conf->global->MAIN_INCLUDE_ZERO_VAT_IN_REPORTS)) $sql.= " AND (d.".$f_rate." <> 0 OR d.total_tva <> 0)";
938  $sql.= " ORDER BY e.rowid";
939 
940  if (! $sql)
941  {
942  dol_syslog("Tax.lib.php::tax_by_rate no accountancy module enabled".$sql,LOG_ERR);
943  return -1; // -1 = Not accountancy module enabled
944  }
945  if ($sql == 'TODO') return -2; // -2 = Feature not yet available
946  if ($sql != 'TODO')
947  {
948  dol_syslog("Tax.lib.php::tax_by_rate", LOG_DEBUG);
949  $resql = $db->query($sql);
950  if ($resql)
951  {
952  $rate = -1;
953  $oldrowid='';
954  while($assoc = $db->fetch_array($resql))
955  {
956  // Code to avoid warnings when array entry not defined
957  if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
958  if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
959  if (! isset($list[$assoc['rate']]['localtax1'])) $list[$assoc['rate']]['localtax1']=0;
960  if (! isset($list[$assoc['rate']]['localtax2'])) $list[$assoc['rate']]['localtax2']=0;
961 
962  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
963  {
964  $oldrowid=$assoc['rowid'];
965  $list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
966  $list[$assoc['rate']]['vat'] += $assoc['total_vat'];
967  $list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
968  $list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
969  }
970 
971  $list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
972  $list[$assoc['rate']]['dtype'][] = 'ExpenseReportPayment';
973  $list[$assoc['rate']]['datef'][] = $assoc['datef'];
974  $list[$assoc['rate']]['company_name'][] = '';
975  $list[$assoc['rate']]['company_id'][] = '';
976  $list[$assoc['rate']]['user_id'][] = $assoc['fk_user_author'];
977  $list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
978  $list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
979 
980  $list[$assoc['rate']]['facid'][] = $assoc['facid'];
981  $list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
982  $list[$assoc['rate']]['type'][] = $assoc['type'];
983  $list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
984  $list[$assoc['rate']]['descr'][] = $assoc['descr'];
985 
986  $list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
987  $list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
988  $list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
989  $list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
990 
991  $list[$assoc['rate']]['pid'][] = $assoc['pid'];
992  $list[$assoc['rate']]['pref'][] = $assoc['pref'];
993  $list[$assoc['rate']]['ptype'][] = 'ExpenseReportPayment';
994 
995  $list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
996  $list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
997 
998  $rate = $assoc['rate'];
999  }
1000  }
1001  else
1002  {
1003  dol_print_error($db);
1004  return -3;
1005  }
1006  }
1007  }
1008 
1009  return $list;
1010 }
1011 
if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) if(! empty($conf->don->enabled) && $user->rights->societe->lire) if(! empty($conf->tax->enabled) && $user->rights->tax->charges->lire) if(! empty($conf->facture->enabled) &&! empty($conf->commande->enabled) && $user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1053
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:91
complete_head_from_modules($conf, $langs, $object, &$head, &$h, $type, $mode='add')
Complete or removed entries into a head array (used to build tabs).
dol_sanitizeFileName($str, $newstr='_', $unaccent=1)
Clean a string to use it as a file name.
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:453
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
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:559
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
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:59
tax_prepare_head(ChargeSociales $object)
Prepare array with list of tabs.
Definition: tax.lib.php:37
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:467
Classe permettant la gestion des paiements des charges La tva collectee n&#39;est calculee que sur les fa...