42 global $db, $langs, $conf, $user;
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';
58 require_once DOL_DOCUMENT_ROOT.
'/core/lib/files.lib.php';
59 require_once DOL_DOCUMENT_ROOT.
'/core/class/link.class.php';
61 $nbFiles = count(
dol_dir_list($upload_dir,
'files', 0,
'',
'(\.meta|_preview.*\.png)$'));
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>';
68 $head[$h][2] =
'documents';
73 if (!empty(
$object->note_private)) {
76 if (!empty(
$object->note_public)) {
79 $head[$h][0] = DOL_URL_ROOT.
'/compta/sociales/note.php?id='.
$object->id;
80 $head[$h][1] = $langs->trans(
'Notes');
82 $head[$h][1] .= (!
getDolGlobalString(
'MAIN_OPTIMIZEFORTEXTBROWSER') ?
'<span class="badge marginleftonlyshort">'.$nbNote.
'</span>' :
'');
84 $head[$h][2] =
'note';
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';
114function tax_by_thirdparty($type, $db, $y, $date_start, $date_end, $modetax, $direction, $m = 0, $q = 0)
119 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
120 dol_print_error(
null,
'Bad value of input parameter for tax_by_thirdparty');
124 if ($direction ==
'sell') {
125 $invoicetable =
'facture';
126 $invoicedettable =
'facturedet';
127 $fk_facture =
'fk_facture';
128 $fk_facture2 =
'fk_facture';
129 $fk_payment =
'fk_paiement';
130 $total_tva =
'total_tva';
131 $paymenttable =
'paiement';
132 $paymentfacturetable =
'paiement_facture';
133 $invoicefieldref =
'ref';
134 } elseif ($direction ==
'buy') {
135 $invoicetable =
'facture_fourn';
136 $invoicedettable =
'facture_fourn_det';
137 $fk_facture =
'fk_facture_fourn';
138 $fk_facture2 =
'fk_facturefourn';
139 $fk_payment =
'fk_paiementfourn';
141 $paymenttable =
'paiementfourn';
142 $paymentfacturetable =
'paiementfourn_facturefourn';
143 $invoicefieldref =
'ref';
146 if (strpos($type,
'localtax') === 0) {
147 $f_rate = $type.
'_tx';
152 $total_localtax1 =
'total_localtax1';
153 $total_localtax2 =
'total_localtax2';
160 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_PRODUCT') ==
'invoice')
161 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_PRODUCT') ==
'invoice')) {
163 $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,";
164 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
165 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
166 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
167 $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,";
168 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
169 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
170 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
171 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
172 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
173 $sql .=
" ,'' as datep";
174 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
175 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
176 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
177 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
178 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
179 $sql .=
" AND f.fk_statut in (1,2)";
180 if ($direction ==
'buy') {
182 $sql .=
" AND f.type IN (0,1,2,5)";
184 $sql .=
" AND f.type IN (0,1,2,3,5)";
188 $sql .=
" AND f.type IN (0,1,2,5)";
190 $sql .=
" AND f.type IN (0,1,2,3,5)";
193 $sql .=
" AND f.rowid = d.".$fk_facture;
194 $sql .=
" AND s.rowid = f.fk_soc";
197 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
200 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
203 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
204 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
206 if ($date_start && $date_end) {
207 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
209 $sql .=
" AND (d.product_type = 0";
210 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
212 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
214 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
217 $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,";
218 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
219 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
220 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
221 $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,";
222 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
223 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
224 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
225 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
226 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
227 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
228 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
229 $sql .=
" ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf,";
230 $sql .=
" ".MAIN_DB_PREFIX.$paymenttable.
" as pa,";
231 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
232 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
233 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
234 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
235 $sql .=
" AND f.fk_statut in (1,2)";
236 if ($direction ==
'buy') {
238 $sql .=
" AND f.type IN (0,1,2,5)";
240 $sql .=
" AND f.type IN (0,1,2,3,5)";
244 $sql .=
" AND f.type IN (0,1,2,5)";
246 $sql .=
" AND f.type IN (0,1,2,3,5)";
249 $sql .=
" AND f.rowid = d.".$fk_facture;
250 $sql .=
" AND s.rowid = f.fk_soc";
251 $sql .=
" AND pf.".$fk_facture2.
" = f.rowid";
252 $sql .=
" AND pa.rowid = pf.".$fk_payment;
255 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
258 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
261 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
262 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
264 if ($date_start && $date_end) {
265 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
267 $sql .=
" AND (d.product_type = 0";
268 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
270 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
272 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
278 if ($sql ==
'TODO') {
281 if ($sql !=
'TODO') {
282 dol_syslog(
"Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
284 $resql = $db->query($sql);
288 while ($assoc = $db->fetch_array($resql)) {
289 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
290 $list[$assoc[
'company_id']][
'totalht'] = 0;
292 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
293 $list[$assoc[
'company_id']][
'vat'] = 0;
295 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
296 $list[$assoc[
'company_id']][
'localtax1'] = 0;
298 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
299 $list[$assoc[
'company_id']][
'localtax2'] = 0;
302 if ($assoc[
'rowid'] != $oldrowid) {
303 $oldrowid = $assoc[
'rowid'];
304 $list[$assoc[
'company_id']][
'totalht'] += $assoc[
'total_ht'];
305 $list[$assoc[
'company_id']][
'vat'] += $assoc[
'total_vat'];
306 $list[$assoc[
'company_id']][
'localtax1'] += $assoc[
'total_localtax1'];
307 $list[$assoc[
'company_id']][
'localtax2'] += $assoc[
'total_localtax2'];
310 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
311 $list[$assoc[
'company_id']][
'dtype'][] = $assoc[
'dtype'];
312 $list[$assoc[
'company_id']][
'datef'][] = $db->jdate($assoc[
'datef']);
313 $list[$assoc[
'company_id']][
'datep'][] = $db->jdate($assoc[
'datep']);
315 $list[$assoc[
'company_id']][
'company_name'][] = $assoc[
'company_name'];
316 $list[$assoc[
'company_id']][
'company_id'][] = $assoc[
'company_id'];
317 $list[$assoc[
'company_id']][
'company_alias'][] = $assoc[
'company_alias'];
318 $list[$assoc[
'company_id']][
'company_email'][] = $assoc[
'company_email'];
319 $list[$assoc[
'company_id']][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
320 $list[$assoc[
'company_id']][
'company_client'][] = $assoc[
'company_client'];
321 $list[$assoc[
'company_id']][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
322 $list[$assoc[
'company_id']][
'company_customer_code'][] = $assoc[
'company_customer_code'];
323 $list[$assoc[
'company_id']][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
324 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
325 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
326 $list[$assoc[
'company_id']][
'company_status'][] = $assoc[
'company_status'];
328 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
329 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
330 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
332 $list[$assoc[
'company_id']][
'facid'][] = $assoc[
'facid'];
333 $list[$assoc[
'company_id']][
'facnum'][] = $assoc[
'facnum'];
334 $list[$assoc[
'company_id']][
'type'][] = $assoc[
'type'];
335 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
336 $list[$assoc[
'company_id']][
'descr'][] = $assoc[
'descr'];
338 $list[$assoc[
'company_id']][
'totalht_list'][] = $assoc[
'total_ht'];
339 $list[$assoc[
'company_id']][
'vat_list'][] = $assoc[
'total_vat'];
340 $list[$assoc[
'company_id']][
'localtax1_list'][] = $assoc[
'total_localtax1'];
341 $list[$assoc[
'company_id']][
'localtax2_list'][] = $assoc[
'total_localtax2'];
343 $list[$assoc[
'company_id']][
'pid'][] = $assoc[
'pid'];
344 $list[$assoc[
'company_id']][
'pref'][] = $assoc[
'pref'];
345 $list[$assoc[
'company_id']][
'ptype'][] = $assoc[
'ptype'];
347 $list[$assoc[
'company_id']][
'payment_id'][] = $assoc[
'payment_id'];
348 $list[$assoc[
'company_id']][
'payment_amount'][] = $assoc[
'payment_amount'];
350 $company_id = $assoc[
'company_id'];
363 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_SERVICE') ==
'invoice')
364 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_SERVICE') ==
'invoice')) {
366 $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,";
367 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
368 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
369 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
370 $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,";
371 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
372 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
373 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
374 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
375 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
376 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
377 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
378 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
379 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
380 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
381 $sql .=
" AND f.fk_statut in (1,2)";
382 if ($direction ==
'buy') {
384 $sql .=
" AND f.type IN (0,1,2,5)";
386 $sql .=
" AND f.type IN (0,1,2,3,5)";
390 $sql .=
" AND f.type IN (0,1,2,5)";
392 $sql .=
" AND f.type IN (0,1,2,3,5)";
395 $sql .=
" AND f.rowid = d.".$fk_facture;
396 $sql .=
" AND s.rowid = f.fk_soc";
399 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
402 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
405 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
406 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
408 if ($date_start && $date_end) {
409 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
411 $sql .=
" AND (d.product_type = 1";
412 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
414 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
416 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
419 $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,";
420 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
421 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
422 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
423 $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,";
424 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
425 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
426 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
427 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
428 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
429 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
430 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
431 $sql .=
" ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf,";
432 $sql .=
" ".MAIN_DB_PREFIX.$paymenttable.
" as pa,";
433 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
434 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
435 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
436 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
437 $sql .=
" AND f.fk_statut in (1,2)";
438 if ($direction ==
'buy') {
440 $sql .=
" AND f.type IN (0,1,2,5)";
442 $sql .=
" AND f.type IN (0,1,2,3,5)";
446 $sql .=
" AND f.type IN (0,1,2,5)";
448 $sql .=
" AND f.type IN (0,1,2,3,5)";
451 $sql .=
" AND f.rowid = d.".$fk_facture;
452 $sql .=
" AND s.rowid = f.fk_soc";
453 $sql .=
" AND pf.".$fk_facture2.
" = f.rowid";
454 $sql .=
" AND pa.rowid = pf.".$fk_payment;
457 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
460 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
463 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
464 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
466 if ($date_start && $date_end) {
467 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
469 $sql .=
" AND (d.product_type = 1";
470 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
472 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
474 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
478 dol_syslog(
"Tax.lib.php::tax_by_thirdparty no accountancy module enabled".$sql, LOG_ERR);
481 if ($sql ==
'TODO') {
484 if ($sql !=
'TODO') {
485 dol_syslog(
"Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
486 $resql = $db->query($sql);
490 while ($assoc = $db->fetch_array($resql)) {
491 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
492 $list[$assoc[
'company_id']][
'totalht'] = 0;
494 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
495 $list[$assoc[
'company_id']][
'vat'] = 0;
497 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
498 $list[$assoc[
'company_id']][
'localtax1'] = 0;
500 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
501 $list[$assoc[
'company_id']][
'localtax2'] = 0;
504 if ($assoc[
'rowid'] != $oldrowid) {
505 $oldrowid = $assoc[
'rowid'];
506 $list[$assoc[
'company_id']][
'totalht'] += $assoc[
'total_ht'];
507 $list[$assoc[
'company_id']][
'vat'] += $assoc[
'total_vat'];
508 $list[$assoc[
'company_id']][
'localtax1'] += $assoc[
'total_localtax1'];
509 $list[$assoc[
'company_id']][
'localtax2'] += $assoc[
'total_localtax2'];
511 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
512 $list[$assoc[
'company_id']][
'dtype'][] = $assoc[
'dtype'];
513 $list[$assoc[
'company_id']][
'datef'][] = $db->jdate($assoc[
'datef']);
514 $list[$assoc[
'company_id']][
'datep'][] = $db->jdate($assoc[
'datep']);
516 $list[$assoc[
'company_id']][
'company_name'][] = $assoc[
'company_name'];
517 $list[$assoc[
'company_id']][
'company_id'][] = $assoc[
'company_id'];
518 $list[$assoc[
'company_id']][
'company_alias'][] = $assoc[
'company_alias'];
519 $list[$assoc[
'company_id']][
'company_email'][] = $assoc[
'company_email'];
520 $list[$assoc[
'company_id']][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
521 $list[$assoc[
'company_id']][
'company_client'][] = $assoc[
'company_client'];
522 $list[$assoc[
'company_id']][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
523 $list[$assoc[
'company_id']][
'company_customer_code'][] = $assoc[
'company_customer_code'];
524 $list[$assoc[
'company_id']][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
525 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
526 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
527 $list[$assoc[
'company_id']][
'company_status'][] = $assoc[
'company_status'];
529 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
530 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
531 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
533 $list[$assoc[
'company_id']][
'facid'][] = $assoc[
'facid'];
534 $list[$assoc[
'company_id']][
'facnum'][] = $assoc[
'facnum'];
535 $list[$assoc[
'company_id']][
'type'][] = $assoc[
'type'];
536 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
537 $list[$assoc[
'company_id']][
'descr'][] = $assoc[
'descr'];
539 $list[$assoc[
'company_id']][
'totalht_list'][] = $assoc[
'total_ht'];
540 $list[$assoc[
'company_id']][
'vat_list'][] = $assoc[
'total_vat'];
541 $list[$assoc[
'company_id']][
'localtax1_list'][] = $assoc[
'total_localtax1'];
542 $list[$assoc[
'company_id']][
'localtax2_list'][] = $assoc[
'total_localtax2'];
544 $list[$assoc[
'company_id']][
'pid'][] = $assoc[
'pid'];
545 $list[$assoc[
'company_id']][
'pref'][] = $assoc[
'pref'];
546 $list[$assoc[
'company_id']][
'ptype'][] = $assoc[
'ptype'];
548 $list[$assoc[
'company_id']][
'payment_id'][] = $assoc[
'payment_id'];
549 $list[$assoc[
'company_id']][
'payment_ref'][] = $assoc[
'payment_ref'];
550 $list[$assoc[
'company_id']][
'payment_amount'][] = $assoc[
'payment_amount'];
552 $company_id = $assoc[
'company_id'];
563 if ($direction ==
'buy') {
568 $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,";
569 $sql .=
" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
570 $sql .=
" e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
571 $sql .=
" e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
572 $sql .=
" p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
573 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport as e";
574 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport_det as d ON d.fk_expensereport = e.rowid ";
575 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"payment_expensereport as p ON p.fk_expensereport = e.rowid ";
576 $sql .=
" WHERE e.entity = ".$conf->entity;
577 $sql .=
" AND e.fk_statut in (6)";
580 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
583 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
586 $sql .=
" AND p.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
587 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
589 if ($date_start && $date_end) {
590 $sql .=
" AND p.datep >= '".$db->idate($date_start).
"' AND p.datep <= '".$db->idate($date_end).
"'";
592 $sql .=
" AND (d.product_type = -1";
593 $sql .=
" OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)";
595 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.total_tva <> 0)";
597 $sql .=
" ORDER BY e.rowid";
600 dol_syslog(
"Tax.lib.php::tax_by_thirdparty no accountancy module enabled".$sql, LOG_ERR);
603 if ($sql ==
'TODO') {
606 if ($sql !=
'TODO') {
607 dol_syslog(
"Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
608 $resql = $db->query($sql);
612 while ($assoc = $db->fetch_array($resql)) {
613 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
614 $list[$assoc[
'company_id']][
'totalht'] = 0;
616 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
617 $list[$assoc[
'company_id']][
'vat'] = 0;
619 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
620 $list[$assoc[
'company_id']][
'localtax1'] = 0;
622 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
623 $list[$assoc[
'company_id']][
'localtax2'] = 0;
626 if ($assoc[
'rowid'] != $oldrowid) {
627 $oldrowid = $assoc[
'rowid'];
628 $list[$assoc[
'company_id']][
'totalht'] += $assoc[
'total_ht'];
629 $list[$assoc[
'company_id']][
'vat'] += $assoc[
'total_vat'];
630 $list[$assoc[
'company_id']][
'localtax1'] += $assoc[
'total_localtax1'];
631 $list[$assoc[
'company_id']][
'localtax2'] += $assoc[
'total_localtax2'];
634 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
635 $list[$assoc[
'company_id']][
'dtype'][] =
'ExpenseReportPayment';
636 $list[$assoc[
'company_id']][
'datef'][] = $assoc[
'datef'];
638 $list[$assoc[
'company_id']][
'company_name'][] =
'';
639 $list[$assoc[
'company_id']][
'company_id'][] =
'';
640 $list[$assoc[
'company_id']][
'company_alias'][] =
'';
641 $list[$assoc[
'company_id']][
'company_email'][] =
'';
642 $list[$assoc[
'company_id']][
'company_tva_intra'][] =
'';
643 $list[$assoc[
'company_id']][
'company_client'][] =
'';
644 $list[$assoc[
'company_id']][
'company_fournisseur'][] =
'';
645 $list[$assoc[
'company_id']][
'company_customer_code'][] =
'';
646 $list[$assoc[
'company_id']][
'company_supplier_code'][] =
'';
647 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] =
'';
648 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] =
'';
649 $list[$assoc[
'company_id']][
'company_status'][] =
'';
651 $list[$assoc[
'company_id']][
'user_id'][] = $assoc[
'fk_user_author'];
652 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
653 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
654 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
656 $list[$assoc[
'company_id']][
'facid'][] = $assoc[
'facid'];
657 $list[$assoc[
'company_id']][
'facnum'][] = $assoc[
'facnum'];
658 $list[$assoc[
'company_id']][
'type'][] = $assoc[
'type'];
659 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
660 $list[$assoc[
'company_id']][
'descr'][] = $assoc[
'descr'];
662 $list[$assoc[
'company_id']][
'totalht_list'][] = $assoc[
'total_ht'];
663 $list[$assoc[
'company_id']][
'vat_list'][] = $assoc[
'total_vat'];
664 $list[$assoc[
'company_id']][
'localtax1_list'][] = $assoc[
'total_localtax1'];
665 $list[$assoc[
'company_id']][
'localtax2_list'][] = $assoc[
'total_localtax2'];
667 $list[$assoc[
'company_id']][
'pid'][] = $assoc[
'pid'];
668 $list[$assoc[
'company_id']][
'pref'][] = $assoc[
'pref'];
669 $list[$assoc[
'company_id']][
'ptype'][] =
'ExpenseReportPayment';
671 $list[$assoc[
'company_id']][
'payment_id'][] = $assoc[
'payment_id'];
672 $list[$assoc[
'company_id']][
'payment_ref'][] = $assoc[
'payment_ref'];
673 $list[$assoc[
'company_id']][
'payment_amount'][] = $assoc[
'payment_amount'];
675 $company_id = $assoc[
'company_id'];
703function tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
708 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
714 if ($direction ==
'sell') {
715 $invoicetable =
'facture';
716 $invoicedettable =
'facturedet';
717 $fk_facture =
'fk_facture';
718 $fk_facture2 =
'fk_facture';
719 $fk_payment =
'fk_paiement';
720 $total_tva =
'total_tva';
721 $paymenttable =
'paiement';
722 $paymentfacturetable =
'paiement_facture';
723 $invoicefieldref =
'ref';
725 $invoicetable =
'facture_fourn';
726 $invoicedettable =
'facture_fourn_det';
727 $fk_facture =
'fk_facture_fourn';
728 $fk_facture2 =
'fk_facturefourn';
729 $fk_payment =
'fk_paiementfourn';
731 $paymenttable =
'paiementfourn';
732 $paymentfacturetable =
'paiementfourn_facturefourn';
733 $invoicefieldref =
'ref';
736 if (strpos($type,
'localtax') === 0) {
737 $f_rate = $type.
'_tx';
742 $total_localtax1 =
'total_localtax1';
743 $total_localtax2 =
'total_localtax2';
750 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_PRODUCT') ==
'invoice')
751 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_PRODUCT') ==
'invoice')) {
753 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
754 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
755 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
756 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
757 $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,";
758 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
759 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
760 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
761 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
762 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount,";
763 $sql .=
" '' as datep";
764 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
765 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
766 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
"=f.rowid";
767 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
768 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
769 $sql .=
" AND f.fk_statut in (1,2)";
770 if ($direction ==
'buy') {
772 $sql .=
" AND f.type IN (0,1,2,5)";
774 $sql .=
" AND f.type IN (0,1,2,3,5)";
778 $sql .=
" AND f.type IN (0,1,2,5)";
780 $sql .=
" AND f.type IN (0,1,2,3,5)";
785 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
788 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
791 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
792 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
794 if ($date_start && $date_end) {
795 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
797 $sql .=
" AND (d.product_type = 0";
798 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
800 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
802 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
805 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
806 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
807 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
808 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
809 $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,";
810 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
811 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
812 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
813 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
814 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
815 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
816 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
817 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf ON pf.".$fk_facture2.
" = f.rowid";
818 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymenttable.
" as pa ON pa.rowid = pf.".$fk_payment;
819 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
820 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
821 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
822 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
823 $sql .=
" AND f.fk_statut in (1,2)";
824 if ($direction ==
'buy') {
826 $sql .=
" AND f.type IN (0,1,2,5)";
828 $sql .=
" AND f.type IN (0,1,2,3,5)";
832 $sql .=
" AND f.type IN (0,1,2,5)";
834 $sql .=
" AND f.type IN (0,1,2,3,5)";
839 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
842 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
845 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
846 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
848 if ($date_start && $date_end) {
849 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
851 $sql .=
" AND (d.product_type = 0";
852 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
854 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
856 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
862 if ($sql ==
'TODO') {
865 if ($sql !=
'TODO') {
866 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
868 $resql = $db->query($sql);
872 while ($assoc = $db->fetch_array($resql)) {
873 $rate_key = $assoc[
'rate'];
874 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
875 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
879 if (!isset($list[$rate_key][
'totalht'])) {
880 $list[$rate_key][
'totalht'] = 0;
882 if (!isset($list[$rate_key][
'vat'])) {
883 $list[$rate_key][
'vat'] = 0;
885 if (!isset($list[$rate_key][
'localtax1'])) {
886 $list[$rate_key][
'localtax1'] = 0;
888 if (!isset($list[$rate_key][
'localtax2'])) {
889 $list[$rate_key][
'localtax2'] = 0;
892 if ($assoc[
'rowid'] != $oldrowid) {
893 $oldrowid = $assoc[
'rowid'];
894 $list[$rate_key][
'totalht'] += $assoc[
'total_ht'];
895 $list[$rate_key][
'vat'] += $assoc[
'total_vat'];
896 $list[$rate_key][
'localtax1'] += $assoc[
'total_localtax1'];
897 $list[$rate_key][
'localtax2'] += $assoc[
'total_localtax2'];
899 $list[$rate_key][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
900 $list[$rate_key][
'dtype'][] = $assoc[
'dtype'];
901 $list[$rate_key][
'datef'][] = $db->jdate($assoc[
'datef']);
902 $list[$rate_key][
'datep'][] = $db->jdate($assoc[
'datep']);
904 $list[$rate_key][
'company_name'][] = $assoc[
'company_name'];
905 $list[$rate_key][
'company_id'][] = $assoc[
'company_id'];
906 $list[$rate_key][
'company_alias'][] = $assoc[
'company_alias'];
907 $list[$rate_key][
'company_email'][] = $assoc[
'company_email'];
908 $list[$rate_key][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
909 $list[$rate_key][
'company_client'][] = $assoc[
'company_client'];
910 $list[$rate_key][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
911 $list[$rate_key][
'company_customer_code'][] = $assoc[
'company_customer_code'];
912 $list[$rate_key][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
913 $list[$rate_key][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
914 $list[$rate_key][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
915 $list[$rate_key][
'company_status'][] = $assoc[
'company_status'];
917 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
918 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
920 $list[$rate_key][
'facid'][] = $assoc[
'facid'];
921 $list[$rate_key][
'facnum'][] = $assoc[
'facnum'];
922 $list[$rate_key][
'type'][] = $assoc[
'type'];
923 $list[$rate_key][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
924 $list[$rate_key][
'descr'][] = $assoc[
'descr'];
926 $list[$rate_key][
'totalht_list'][] = $assoc[
'total_ht'];
927 $list[$rate_key][
'vat_list'][] = $assoc[
'total_vat'];
928 $list[$rate_key][
'localtax1_list'][] = $assoc[
'total_localtax1'];
929 $list[$rate_key][
'localtax2_list'][] = $assoc[
'total_localtax2'];
931 $list[$rate_key][
'pid'][] = $assoc[
'pid'];
932 $list[$rate_key][
'pref'][] = $assoc[
'pref'];
933 $list[$rate_key][
'ptype'][] = $assoc[
'ptype'];
935 $list[$rate_key][
'payment_id'][] = $assoc[
'payment_id'];
936 $list[$rate_key][
'payment_ref'][] = $assoc[
'payment_ref'];
937 $list[$rate_key][
'payment_amount'][] = $assoc[
'payment_amount'];
939 $rate = $assoc[
'rate'];
952 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_SERVICE') ==
'invoice')
953 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_SERVICE') ==
'invoice')) {
955 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
956 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
957 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
958 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
959 $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,";
960 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
961 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
962 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
963 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
964 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
965 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
966 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
967 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
968 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
969 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
970 $sql .=
" AND f.fk_statut in (1,2)";
971 if ($direction ==
'buy') {
973 $sql .=
" AND f.type IN (0,1,2,5)";
975 $sql .=
" AND f.type IN (0,1,2,3,5)";
979 $sql .=
" AND f.type IN (0,1,2,5)";
981 $sql .=
" AND f.type IN (0,1,2,3,5)";
986 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
989 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
992 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
993 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
995 if ($date_start && $date_end) {
996 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
998 $sql .=
" AND (d.product_type = 1";
999 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
1001 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
1003 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
1006 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
1007 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
1008 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
1009 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
1010 $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,";
1011 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
1012 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
1013 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
1014 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
1015 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
1016 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
1017 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
1018 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf ON pf.".$fk_facture2.
" = f.rowid";
1019 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymenttable.
" as pa ON pa.rowid = pf.".$fk_payment;
1020 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
1021 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
1022 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
1023 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
1024 $sql .=
" AND f.fk_statut in (1,2)";
1025 if ($direction ==
'buy') {
1027 $sql .=
" AND f.type IN (0,1,2,5)";
1029 $sql .=
" AND f.type IN (0,1,2,3,5)";
1033 $sql .=
" AND f.type IN (0,1,2,5)";
1035 $sql .=
" AND f.type IN (0,1,2,3,5)";
1040 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
1043 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
1046 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
1047 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
1049 if ($date_start && $date_end) {
1050 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
1052 $sql .=
" AND (d.product_type = 1";
1053 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
1055 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
1057 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
1061 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1064 if ($sql ==
'TODO') {
1067 if ($sql !=
'TODO') {
1068 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
1069 $resql = $db->query($sql);
1073 while ($assoc = $db->fetch_array($resql)) {
1074 $rate_key = $assoc[
'rate'];
1075 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
1076 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
1080 if (!isset($list[$rate_key][
'totalht'])) {
1081 $list[$rate_key][
'totalht'] = 0;
1083 if (!isset($list[$rate_key][
'vat'])) {
1084 $list[$rate_key][
'vat'] = 0;
1086 if (!isset($list[$rate_key][
'localtax1'])) {
1087 $list[$rate_key][
'localtax1'] = 0;
1089 if (!isset($list[$rate_key][
'localtax2'])) {
1090 $list[$rate_key][
'localtax2'] = 0;
1093 if ($assoc[
'rowid'] != $oldrowid) {
1094 $oldrowid = $assoc[
'rowid'];
1095 $list[$rate_key][
'totalht'] += $assoc[
'total_ht'];
1096 $list[$rate_key][
'vat'] += $assoc[
'total_vat'];
1097 $list[$rate_key][
'localtax1'] += $assoc[
'total_localtax1'];
1098 $list[$rate_key][
'localtax2'] += $assoc[
'total_localtax2'];
1100 $list[$rate_key][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
1101 $list[$rate_key][
'dtype'][] = $assoc[
'dtype'];
1102 $list[$rate_key][
'datef'][] = $db->jdate($assoc[
'datef']);
1103 $list[$rate_key][
'datep'][] = $db->jdate($assoc[
'datep']);
1105 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
1106 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
1108 $list[$rate_key][
'company_name'][] = $assoc[
'company_name'];
1109 $list[$rate_key][
'company_id'][] = $assoc[
'company_id'];
1110 $list[$rate_key][
'company_alias'][] = $assoc[
'company_alias'];
1111 $list[$rate_key][
'company_email'][] = $assoc[
'company_email'];
1112 $list[$rate_key][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
1113 $list[$rate_key][
'company_client'][] = $assoc[
'company_client'];
1114 $list[$rate_key][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
1115 $list[$rate_key][
'company_customer_code'][] = $assoc[
'company_customer_code'];
1116 $list[$rate_key][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
1117 $list[$rate_key][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
1118 $list[$rate_key][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
1119 $list[$rate_key][
'company_status'][] = $assoc[
'company_status'];
1121 $list[$rate_key][
'facid'][] = $assoc[
'facid'];
1122 $list[$rate_key][
'facnum'][] = $assoc[
'facnum'];
1123 $list[$rate_key][
'type'][] = $assoc[
'type'];
1124 $list[$rate_key][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
1125 $list[$rate_key][
'descr'][] = $assoc[
'descr'];
1127 $list[$rate_key][
'totalht_list'][] = $assoc[
'total_ht'];
1128 $list[$rate_key][
'vat_list'][] = $assoc[
'total_vat'];
1129 $list[$rate_key][
'localtax1_list'][] = $assoc[
'total_localtax1'];
1130 $list[$rate_key][
'localtax2_list'][] = $assoc[
'total_localtax2'];
1132 $list[$rate_key][
'pid'][] = $assoc[
'pid'];
1133 $list[$rate_key][
'pref'][] = $assoc[
'pref'];
1134 $list[$rate_key][
'ptype'][] = $assoc[
'ptype'];
1136 $list[$rate_key][
'payment_id'][] = $assoc[
'payment_id'];
1137 $list[$rate_key][
'payment_ref'][] = $assoc[
'payment_ref'];
1138 $list[$rate_key][
'payment_amount'][] = $assoc[
'payment_amount'];
1140 $rate = $assoc[
'rate'];
1151 if ($direction ==
'buy') {
1156 $sql =
"SELECT d.rowid, d.product_type as dtype, e.rowid as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.total_tva as total_vat, e.note_private as descr,";
1157 $sql .=
" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
1158 $sql .=
" e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
1159 $sql .=
" e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
1160 $sql .=
" p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
1161 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport as e";
1162 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport_det as d ON d.fk_expensereport = e.rowid";
1163 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"payment_expensereport as p ON p.fk_expensereport = e.rowid";
1164 $sql .=
" WHERE e.entity = ".$conf->entity;
1165 $sql .=
" AND e.fk_statut in (6)";
1168 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
1171 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
1174 $sql .=
" AND p.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
1175 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
1177 if ($date_start && $date_end) {
1178 $sql .=
" AND p.datep >= '".$db->idate($date_start).
"' AND p.datep <= '".$db->idate($date_end).
"'";
1180 $sql .=
" AND (d.product_type = -1";
1181 $sql .=
" OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)";
1183 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.total_tva <> 0)";
1185 $sql .=
" ORDER BY e.rowid";
1188 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1191 if ($sql ==
'TODO') {
1194 if ($sql !=
'TODO') {
1195 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
1196 $resql = $db->query($sql);
1200 while ($assoc = $db->fetch_array($resql)) {
1201 $rate_key = $assoc[
'rate'];
1202 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
1203 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
1207 if (!isset($list[$rate_key][
'totalht'])) {
1208 $list[$rate_key][
'totalht'] = 0;
1210 if (!isset($list[$rate_key][
'vat'])) {
1211 $list[$rate_key][
'vat'] = 0;
1213 if (!isset($list[$rate_key][
'localtax1'])) {
1214 $list[$rate_key][
'localtax1'] = 0;
1216 if (!isset($list[$rate_key][
'localtax2'])) {
1217 $list[$rate_key][
'localtax2'] = 0;
1220 if ($assoc[
'rowid'] != $oldrowid) {
1221 $oldrowid = $assoc[
'rowid'];
1222 $list[$rate_key][
'totalht'] += $assoc[
'total_ht'];
1223 $list[$rate_key][
'vat'] += $assoc[
'total_vat'];
1224 $list[$rate_key][
'localtax1'] += $assoc[
'total_localtax1'];
1225 $list[$rate_key][
'localtax2'] += $assoc[
'total_localtax2'];
1228 $list[$rate_key][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
1229 $list[$rate_key][
'dtype'][] =
'ExpenseReportPayment';
1230 $list[$rate_key][
'datef'][] = $assoc[
'datef'];
1231 $list[$rate_key][
'company_name'][] =
'';
1232 $list[$rate_key][
'company_id'][] =
'';
1233 $list[$rate_key][
'user_id'][] = $assoc[
'fk_user_author'];
1234 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
1235 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
1237 $list[$rate_key][
'facid'][] = $assoc[
'facid'];
1238 $list[$rate_key][
'facnum'][] = $assoc[
'facnum'];
1239 $list[$rate_key][
'type'][] = $assoc[
'type'];
1240 $list[$rate_key][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
1241 $list[$rate_key][
'descr'][] = $assoc[
'descr'];
1243 $list[$rate_key][
'totalht_list'][] = $assoc[
'total_ht'];
1244 $list[$rate_key][
'vat_list'][] = $assoc[
'total_vat'];
1245 $list[$rate_key][
'localtax1_list'][] = $assoc[
'total_localtax1'];
1246 $list[$rate_key][
'localtax2_list'][] = $assoc[
'total_localtax2'];
1248 $list[$rate_key][
'pid'][] = $assoc[
'pid'];
1249 $list[$rate_key][
'pref'][] = $assoc[
'pref'];
1250 $list[$rate_key][
'ptype'][] =
'ExpenseReportPayment';
1252 $list[$rate_key][
'payment_id'][] = $assoc[
'payment_id'];
1253 $list[$rate_key][
'payment_ref'][] = $assoc[
'payment_ref'];
1254 $list[$rate_key][
'payment_amount'][] = $assoc[
'payment_amount'];
1256 $rate = $assoc[
'rate'];
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Class for managing the social charges.
static count($dbs, $objecttype, $objectid)
Return nb of links.
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
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.
dol_sanitizeFileName($str, $newstr='_', $unaccent=1)
Clean a string to use it as a file name.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
complete_head_from_modules($conf, $langs, $object, &$head, &$h, $type, $mode='add', $filterorigmodule='')
Complete or removed entries into a head array (used to build tabs).
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m=0)
Gets Tax to collect for the given year (and given quarter or month) The function gets the Tax in spli...
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)
tax_prepare_head(ChargeSociales $object)
Prepare array with list of tabs.