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