dolibarr  7.0.0-beta
tax.lib.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2004-2009 Laurent Destailleur <eldy@users.sourceforge.net>
3  * Copyright (C) 2006-2007 Yannick Warnier <ywarnier@beeznest.org>
4  * Copyright (C) 2011 Regis Houssin <regis.houssin@capnetworks.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 
89 function vat_by_thirdparty($db, $y, $date_start, $date_end, $modetax, $direction, $m=0)
90 {
91  global $conf;
92 
93  $list=array();
94 
95  if ($direction == 'sell')
96  {
97  $invoicetable='facture';
98  $total_ht='total';
99  $total_tva='tva';
100  }
101  if ($direction == 'buy')
102  {
103  $invoicetable='facture_fourn';
104  $total_ht='total_ht';
105  $total_tva='total_tva';
106  }
107 
108  // Define sql request
109  $sql='';
110  if ($modetax == 1)
111  {
112  // If vat paid on due invoices (non draft)
113  $sql = "SELECT s.rowid as socid, s.nom as name, s.tva_intra as tva_intra, s.tva_assuj as assuj,";
114  $sql.= " sum(f.$total_ht) as amount, sum(f.".$total_tva.") as tva,";
115  $sql.= " sum(f.localtax1) as localtax1,";
116  $sql.= " sum(f.localtax2) as localtax2";
117  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
118  $sql.= " ".MAIN_DB_PREFIX."societe as s";
119  $sql.= " WHERE f.entity = " . $conf->entity;
120  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
121  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
122  else $sql.= " AND f.type IN (0,1,2,3,5)";
123  if ($y && $m)
124  {
125  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
126  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
127  }
128  else if ($y)
129  {
130  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
131  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
132  }
133  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
134  $sql.= " AND s.rowid = f.fk_soc";
135  $sql.= " GROUP BY s.rowid, s.nom, s.tva_intra, s.tva_assuj";
136  }
137  else
138  {
139  // Tva sur factures payes (should be on payment)
140 /* $sql = "SELECT s.rowid as socid, s.nom as nom, s.tva_intra as tva_intra, s.tva_assuj as assuj,";
141  $sql.= " sum(fd.total_ht) as amount, sum(".$total_tva.") as tva";
142  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f, ".MAIN_DB_PREFIX.$invoicetable." as fd, ".MAIN_DB_PREFIX."societe as s";
143  $sql.= " WHERE ";
144  $sql.= " f.fk_statut in (2)"; // Paid (partially or completely)
145  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
146  else $sql.= " AND f.type IN (0,1,2,3,5)";
147  if ($y && $m)
148  {
149  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
150  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
151  }
152  else if ($y)
153  {
154  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
155  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
156  }
157  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
158  $sql.= " AND s.rowid = f.fk_soc AND f.rowid = fd.".$fk_facture;
159  $sql.= " GROUP BY s.rowid as socid, s.nom as nom, s.tva_intra as tva_intra, s.tva_assuj as assuj";
160 */
161  }
162 
163  if (! $sql) return -1;
164 
165  dol_syslog("Tax.lib:thirdparty", LOG_DEBUG);
166  $resql = $db->query($sql);
167  if ($resql)
168  {
169  while($assoc = $db->fetch_object($resql))
170  {
171  $list[] = $assoc;
172  }
173  $db->free($resql);
174  return $list;
175  }
176  else
177  {
178  dol_print_error($db);
179  return -3;
180  }
181 }
182 
200 function tax_by_date($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m=0)
201 {
202  global $conf;
203 
204  $list=array();
205 
206  if ($direction == 'sell')
207  {
208  $invoicetable='facture';
209  $invoicedettable='facturedet';
210  $fk_facture='fk_facture';
211  $fk_facture2='fk_facture';
212  $fk_payment='fk_paiement';
213  $total_tva='total_tva';
214  $paymenttable='paiement';
215  $paymentfacturetable='paiement_facture';
216  $invoicefieldref='facnumber';
217  }
218  if ($direction == 'buy')
219  {
220  $invoicetable='facture_fourn';
221  $invoicedettable='facture_fourn_det';
222  $fk_facture='fk_facture_fourn';
223  $fk_facture2='fk_facturefourn';
224  $fk_payment='fk_paiementfourn';
225  $total_tva='tva';
226  $paymenttable='paiementfourn';
227  $paymentfacturetable='paiementfourn_facturefourn';
228  $invoicefieldref='ref';
229  }
230 
231  if ( strpos( $type, 'localtax' ) === 0 ) {
232  $f_rate = $type . '_tx';
233  } else {
234  $f_rate = 'tva_tx';
235  }
236 
237  $total_localtax1='total_localtax1';
238  $total_localtax2='total_localtax2';
239 
240  // CAS DES BIENS
241 
242  // Define sql request
243  $sql='';
244  if ($modetax == 1) // Option vat on delivery for goods (payment) and debit invoice for services
245  {
246  // Count on delivery date (use invoice date as delivery is unknown)
247  $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,";
248  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
249  $sql.= " d.date_start as date_start, d.date_end as date_end,";
250  $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,";
251  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
252  $sql.= " 0 as payment_id, 0 as payment_amount";
253  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
254  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
255  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
256  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
257  $sql.= " WHERE f.entity = " . $conf->entity;
258  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
259  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
260  else $sql.= " AND f.type IN (0,1,2,3,5)";
261  $sql.= " AND f.rowid = d.".$fk_facture;
262  $sql.= " AND s.rowid = f.fk_soc";
263  if ($y && $m)
264  {
265  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
266  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
267  }
268  else if ($y)
269  {
270  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
271  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
272  }
273  if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
274  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
275  $sql.= " AND (d.product_type = 0"; // Limit to products
276  $sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of service
277  $sql.= " ORDER BY d.rowid, d.".$fk_facture;
278  }
279  else // Option vat on delivery for goods (payments) and payments for services
280  {
281  // Count on delivery date (use invoice date as delivery is unknown)
282  $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,";
283  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
284  $sql.= " d.date_start as date_start, d.date_end as date_end,";
285  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef as date_f, s.nom as company_name, s.rowid as company_id,";
286  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
287  $sql.= " 0 as payment_id, 0 as payment_amount";
288  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
289  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
290  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
291  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
292  $sql.= " WHERE f.entity = " . $conf->entity;
293  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
294  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
295  else $sql.= " AND f.type IN (0,1,2,3,5)";
296  $sql.= " AND f.rowid = d.".$fk_facture;
297  $sql.= " AND s.rowid = f.fk_soc";
298  if ($y && $m)
299  {
300  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
301  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
302  }
303  else if ($y)
304  {
305  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
306  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
307  }
308  if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
309  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
310  $sql.= " AND (d.product_type = 0"; // Limit to products
311  $sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of service
312  $sql.= " ORDER BY d.rowid, d.".$fk_facture;
313  //print $sql;
314  }
315 
316  //print $sql.'<br>';
317  if (! $sql) return -1;
318  if ($sql == 'TODO') return -2;
319  if ($sql != 'TODO')
320  {
321  dol_syslog("Tax.lib.php::vat_by_date", LOG_DEBUG);
322 
323  $resql = $db->query($sql);
324  if ($resql)
325  {
326  $rate = -1;
327  $oldrowid='';
328  while($assoc = $db->fetch_array($resql))
329  {
330  if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
331  if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
332  if (! isset($list[$assoc['rate']]['localtax1'])) $list[$assoc['rate']]['localtax1']=0;
333  if (! isset($list[$assoc['rate']]['localtax2'])) $list[$assoc['rate']]['localtax2']=0;
334 
335  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
336  {
337  $oldrowid=$assoc['rowid'];
338  $list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
339  $list[$assoc['rate']]['vat'] += $assoc['total_vat'];
340  $list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
341  $list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
342  }
343  $list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
344  $list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
345  $list[$assoc['rate']]['datef'][] = $assoc['datef'];
346  $list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
347  $list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
348  $list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
349  $list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
350 
351  $list[$assoc['rate']]['facid'][] = $assoc['facid'];
352  $list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
353  $list[$assoc['rate']]['type'][] = $assoc['type'];
354  $list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
355  $list[$assoc['rate']]['descr'][] = $assoc['descr'];
356 
357  $list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
358  $list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
359  $list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
360  $list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
361 
362  $list[$assoc['rate']]['pid'][] = $assoc['pid'];
363  $list[$assoc['rate']]['pref'][] = $assoc['pref'];
364  $list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
365 
366  $list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
367  $list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
368 
369  $rate = $assoc['rate'];
370  }
371  }
372  else
373  {
374  dol_print_error($db);
375  return -3;
376  }
377  }
378 
379 
380  // CAS DES SERVICES
381 
382  // Define sql request
383  $sql='';
384  if ($modetax == 1) // Option vat on delivery for goods (payment) and debit invoice for services
385  {
386  // Count on invoice date
387  $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,";
388  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
389  $sql.= " d.date_start as date_start, d.date_end as date_end,";
390  $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,";
391  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
392  $sql.= " 0 as payment_id, 0 as payment_amount";
393  $sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
394  $sql.= " ".MAIN_DB_PREFIX."societe as s,";
395  $sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
396  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
397  $sql.= " WHERE f.entity = " . $conf->entity;
398  $sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
399  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
400  else $sql.= " AND f.type IN (0,1,2,3,5)";
401  $sql.= " AND f.rowid = d.".$fk_facture;
402  $sql.= " AND s.rowid = f.fk_soc";
403  if ($y && $m)
404  {
405  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
406  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
407  }
408  else if ($y)
409  {
410  $sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
411  $sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
412  }
413  if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
414  if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
415  $sql.= " AND (d.product_type = 1"; // Limit to services
416  $sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
417  $sql.= " ORDER BY d.rowid, d.".$fk_facture;
418  }
419  else // Option vat on delivery for goods (payments) and payments for services
420  {
421  // Count on payments date
422  $sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
423  $sql .=" d.".$total_localtax1." as total_localtax1, d.".$total_localtax2." as total_localtax2, ";
424  $sql.= " d.date_start as date_start, d.date_end as date_end,";
425  $sql.= " f.".$invoicefieldref." as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
426  $sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
427  $sql.= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount";
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 = " . $conf->entity;
435  $sql.= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
436  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2,5)";
437  else $sql.= " AND f.type IN (0,1,2,3,5)";
438  $sql.= " AND f.rowid = d.".$fk_facture;
439  $sql.= " AND s.rowid = f.fk_soc";
440  $sql.= " AND pf.".$fk_facture2." = f.rowid";
441  $sql.= " AND pa.rowid = pf.".$fk_payment;
442  if ($y && $m)
443  {
444  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
445  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
446  }
447  else if ($y)
448  {
449  $sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
450  $sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
451  }
452  if ($q) $sql.= " AND (date_format(pa.datep,'%m') > ".(($q-1)*3)." AND date_format(pa.datep,'%m') <= ".($q*3).")";
453  if ($date_start && $date_end) $sql.= " AND pa.datep >= '".$db->idate($date_start)."' AND pa.datep <= '".$db->idate($date_end)."'";
454  $sql.= " AND (d.product_type = 1"; // Limit to services
455  $sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
456  $sql.= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
457  }
458 
459  if (! $sql)
460  {
461  dol_syslog("Tax.lib.php::vat_by_date no accountancy module enabled".$sql,LOG_ERR);
462  return -1; // -1 = Not accountancy module enabled
463  }
464  if ($sql == 'TODO') return -2; // -2 = Feature not yet available
465  if ($sql != 'TODO')
466  {
467  dol_syslog("Tax.lib.php::vat_by_date", LOG_DEBUG);
468  $resql = $db->query($sql);
469  if ($resql)
470  {
471  $rate = -1;
472  $oldrowid='';
473  while($assoc = $db->fetch_array($resql))
474  {
475  if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
476  if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
477  if (! isset($list[$assoc['rate']]['localtax1'])) $list[$assoc['rate']]['localtax1']=0;
478  if (! isset($list[$assoc['rate']]['localtax2'])) $list[$assoc['rate']]['localtax2']=0;
479 
480  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
481  {
482  $oldrowid=$assoc['rowid'];
483  $list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
484  $list[$assoc['rate']]['vat'] += $assoc['total_vat'];
485  $list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
486  $list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
487  }
488  $list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
489  $list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
490  $list[$assoc['rate']]['datef'][] = $assoc['datef'];
491  $list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
492  $list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
493  $list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
494  $list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
495 
496  $list[$assoc['rate']]['facid'][] = $assoc['facid'];
497  $list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
498  $list[$assoc['rate']]['type'][] = $assoc['type'];
499  $list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
500  $list[$assoc['rate']]['descr'][] = $assoc['descr'];
501 
502  $list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
503  $list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
504  $list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
505  $list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
506 
507  $list[$assoc['rate']]['pid'][] = $assoc['pid'];
508  $list[$assoc['rate']]['pref'][] = $assoc['pref'];
509  $list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
510 
511  $list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
512  $list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
513 
514  $rate = $assoc['rate'];
515  }
516  }
517  else
518  {
519  dol_print_error($db);
520  return -3;
521  }
522  }
523 
524  // Expense Report
525  if ($direction == 'buy')
526  {
527  // Define sql request
528  $sql='';
529 
530  // Count on payments date
531  $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,";
532  $sql .=" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
533  $sql.= " e.date_debut as date_start, e.date_fin as date_end,";
534  $sql.= " e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
535  $sql.= " p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
536  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as e ";
537  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."expensereport_det as d ON d.fk_expensereport = e.rowid ";
538  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."payment_expensereport as p ON p.fk_expensereport = e.rowid ";
539  $sql.= " WHERE e.entity = " . $conf->entity;
540  $sql.= " AND e.fk_statut in (6)";
541  if ($y && $m)
542  {
543  $sql.= " AND p.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
544  $sql.= " AND p.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
545  }
546  else if ($y)
547  {
548  $sql.= " AND p.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
549  $sql.= " AND p.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
550  }
551  if ($q) $sql.= " AND (date_format(p.datep,'%m') > ".(($q-1)*3)." AND date_format(p.datep,'%m') <= ".($q*3).")";
552  if ($date_start && $date_end) $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
553  $sql.= " AND (d.product_type = -1";
554  $sql.= " OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)"; // enhance detection of service
555  $sql.= " ORDER BY e.rowid";
556 
557  if (! $sql)
558  {
559  dol_syslog("Tax.lib.php::vat_by_date no accountancy module enabled".$sql,LOG_ERR);
560  return -1; // -1 = Not accountancy module enabled
561  }
562  if ($sql == 'TODO') return -2; // -2 = Feature not yet available
563  if ($sql != 'TODO')
564  {
565  dol_syslog("Tax.lib.php::vat_by_date", LOG_DEBUG);
566  $resql = $db->query($sql);
567  if ($resql)
568  {
569  $rate = -1;
570  $oldrowid='';
571  while($assoc = $db->fetch_array($resql))
572  {
573  if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
574  if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
575  if (! isset($list[$assoc['rate']]['localtax1'])) $list[$assoc['rate']]['localtax1']=0;
576  if (! isset($list[$assoc['rate']]['localtax2'])) $list[$assoc['rate']]['localtax2']=0;
577 
578  if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
579  {
580  $oldrowid=$assoc['rowid'];
581  $list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
582  $list[$assoc['rate']]['vat'] += $assoc['total_vat'];
583  $list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
584  $list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
585  }
586 
587  $list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
588  $list[$assoc['rate']]['dtype'][] = 'ExpenseReportPayment';
589  $list[$assoc['rate']]['datef'][] = $assoc['datef'];
590  $list[$assoc['rate']]['company_name'][] = '';
591  $list[$assoc['rate']]['company_id'][] = '';
592  $list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
593  $list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
594 
595  $list[$assoc['rate']]['facid'][] = $assoc['facid'];
596  $list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
597  $list[$assoc['rate']]['type'][] = $assoc['type'];
598  $list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
599  $list[$assoc['rate']]['descr'][] = $assoc['descr'];
600 
601  $list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
602  $list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
603  $list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
604  $list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
605 
606  $list[$assoc['rate']]['pid'][] = $assoc['pid'];
607  $list[$assoc['rate']]['pref'][] = $assoc['pref'];
608  $list[$assoc['rate']]['ptype'][] = 'ExpenseReportPayment';
609 
610  $list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
611  $list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
612 
613  $rate = $assoc['rate'];
614  }
615  }
616  else
617  {
618  dol_print_error($db);
619  return -3;
620  }
621  }
622  }
623 
624  return $list;
625 }
626 
643 function vat_by_date ($db, $y, $q, $date_start, $date_end, $modetax, $direction, $m=0)
644 {
645  return tax_by_date('vat', $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m);
646 }
647 
vat_by_thirdparty($db, $y, $date_start, $date_end, $modetax, $direction, $m=0)
Look for collectable VAT clients in the chosen year (and month)
Definition: tax.lib.php:89
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:445
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
dol_dir_list($path, $types="all", $recursive=0, $filter="", $excludefilter=null, $sortcriteria="name", $sortorder=SORT_ASC, $mode=0, $nohook=0, $relativename="")
Scan a directory and return a list of files/directories.
Definition: files.lib.php:58
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
tax_by_date($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:200
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:459
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:1013
Classe permettant la gestion des paiements des charges La tva collectee n'est calculee que sur les fa...
vat_by_date($db, $y, $q, $date_start, $date_end, $modetax, $direction, $m=0)
Gets VAT to collect for the given year (and given quarter or month) The function gets the VAT in spli...
Definition: tax.lib.php:643