40 global $db, $langs, $conf, $user;
45 $head[$h][0] = DOL_URL_ROOT.
'/compta/sociales/card.php?id='.
$object->id;
46 $head[$h][1] = $langs->trans(
'SocialContribution');
47 $head[$h][2] =
'card';
56 require_once DOL_DOCUMENT_ROOT.
'/core/lib/files.lib.php';
57 require_once DOL_DOCUMENT_ROOT.
'/core/class/link.class.php';
59 $nbFiles = count(
dol_dir_list($upload_dir,
'files', 0,
'',
'(\.meta|_preview.*\.png)$'));
61 $head[$h][0] = DOL_URL_ROOT.
'/compta/sociales/document.php?id='.
$object->id;
62 $head[$h][1] = $langs->trans(
"Documents");
63 if (($nbFiles + $nbLinks) > 0) {
64 $head[$h][1] .=
'<span class="badge marginleftonlyshort">'.($nbFiles + $nbLinks).
'</span>';
66 $head[$h][2] =
'documents';
71 if (!empty(
$object->note_private)) {
74 if (!empty(
$object->note_public)) {
77 $head[$h][0] = DOL_URL_ROOT.
'/compta/sociales/note.php?id='.
$object->id;
78 $head[$h][1] = $langs->trans(
'Notes');
80 $head[$h][1] .= (!
getDolGlobalString(
'MAIN_OPTIMIZEFORTEXTBROWSER') ?
'<span class="badge marginleftonlyshort">'.$nbNote.
'</span>' :
'');
82 $head[$h][2] =
'note';
86 $head[$h][0] = DOL_URL_ROOT.
'/compta/sociales/info.php?id='.
$object->id;
87 $head[$h][1] = $langs->trans(
"Info");
88 $head[$h][2] =
'info';
112function tax_by_thirdparty($type, $db, $y, $date_start, $date_end, $modetax, $direction, $m = 0, $q = 0)
117 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
122 if ($direction ==
'sell') {
123 $invoicetable =
'facture';
124 $invoicedettable =
'facturedet';
125 $fk_facture =
'fk_facture';
126 $fk_facture2 =
'fk_facture';
127 $fk_payment =
'fk_paiement';
128 $total_tva =
'total_tva';
129 $paymenttable =
'paiement';
130 $paymentfacturetable =
'paiement_facture';
131 $invoicefieldref =
'ref';
132 } elseif ($direction ==
'buy') {
133 $invoicetable =
'facture_fourn';
134 $invoicedettable =
'facture_fourn_det';
135 $fk_facture =
'fk_facture_fourn';
136 $fk_facture2 =
'fk_facturefourn';
137 $fk_payment =
'fk_paiementfourn';
139 $paymenttable =
'paiementfourn';
140 $paymentfacturetable =
'paiementfourn_facturefourn';
141 $invoicefieldref =
'ref';
144 if (strpos($type,
'localtax') === 0) {
145 $f_rate = $type.
'_tx';
150 $total_localtax1 =
'total_localtax1';
151 $total_localtax2 =
'total_localtax2';
158 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_PRODUCT') ==
'invoice')
159 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_PRODUCT') ==
'invoice')) {
161 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
162 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
163 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
164 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
165 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
166 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
167 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
168 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
169 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
170 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
171 $sql .=
" ,'' as datep";
172 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
173 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
174 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
175 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
176 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
177 $sql .=
" AND f.fk_statut in (1,2)";
178 if ($direction ==
'buy') {
180 $sql .=
" AND f.type IN (0,1,2,5)";
182 $sql .=
" AND f.type IN (0,1,2,3,5)";
186 $sql .=
" AND f.type IN (0,1,2,5)";
188 $sql .=
" AND f.type IN (0,1,2,3,5)";
191 $sql .=
" AND f.rowid = d.".$fk_facture;
192 $sql .=
" AND s.rowid = f.fk_soc";
195 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
198 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
201 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
202 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
204 if ($date_start && $date_end) {
205 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
207 $sql .=
" AND (d.product_type = 0";
208 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
210 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
212 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
215 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
216 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
217 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
218 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
219 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
220 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
221 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
222 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
223 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
224 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
225 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
226 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
227 $sql .=
" ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf,";
228 $sql .=
" ".MAIN_DB_PREFIX.$paymenttable.
" as pa,";
229 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
230 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
231 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
232 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
233 $sql .=
" AND f.fk_statut in (1,2)";
234 if ($direction ==
'buy') {
236 $sql .=
" AND f.type IN (0,1,2,5)";
238 $sql .=
" AND f.type IN (0,1,2,3,5)";
242 $sql .=
" AND f.type IN (0,1,2,5)";
244 $sql .=
" AND f.type IN (0,1,2,3,5)";
247 $sql .=
" AND f.rowid = d.".$fk_facture;
248 $sql .=
" AND s.rowid = f.fk_soc";
249 $sql .=
" AND pf.".$fk_facture2.
" = f.rowid";
250 $sql .=
" AND pa.rowid = pf.".$fk_payment;
253 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
256 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
259 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
260 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
262 if ($date_start && $date_end) {
263 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
265 $sql .=
" AND (d.product_type = 0";
266 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
268 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
270 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
276 if ($sql ==
'TODO') {
279 if ($sql !=
'TODO') {
280 dol_syslog(
"Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
282 $resql = $db->query($sql);
286 while ($assoc = $db->fetch_array($resql)) {
287 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
288 $list[$assoc[
'company_id']][
'totalht'] = 0;
290 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
291 $list[$assoc[
'company_id']][
'vat'] = 0;
293 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
294 $list[$assoc[
'company_id']][
'localtax1'] = 0;
296 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
297 $list[$assoc[
'company_id']][
'localtax2'] = 0;
300 if ($assoc[
'rowid'] != $oldrowid) {
301 $oldrowid = $assoc[
'rowid'];
302 $list[$assoc[
'company_id']][
'totalht'] += $assoc[
'total_ht'];
303 $list[$assoc[
'company_id']][
'vat'] += $assoc[
'total_vat'];
304 $list[$assoc[
'company_id']][
'localtax1'] += $assoc[
'total_localtax1'];
305 $list[$assoc[
'company_id']][
'localtax2'] += $assoc[
'total_localtax2'];
308 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
309 $list[$assoc[
'company_id']][
'dtype'][] = $assoc[
'dtype'];
310 $list[$assoc[
'company_id']][
'datef'][] = $db->jdate($assoc[
'datef']);
311 $list[$assoc[
'company_id']][
'datep'][] = $db->jdate($assoc[
'datep']);
313 $list[$assoc[
'company_id']][
'company_name'][] = $assoc[
'company_name'];
314 $list[$assoc[
'company_id']][
'company_id'][] = $assoc[
'company_id'];
315 $list[$assoc[
'company_id']][
'company_alias'][] = $assoc[
'company_alias'];
316 $list[$assoc[
'company_id']][
'company_email'][] = $assoc[
'company_email'];
317 $list[$assoc[
'company_id']][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
318 $list[$assoc[
'company_id']][
'company_client'][] = $assoc[
'company_client'];
319 $list[$assoc[
'company_id']][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
320 $list[$assoc[
'company_id']][
'company_customer_code'][] = $assoc[
'company_customer_code'];
321 $list[$assoc[
'company_id']][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
322 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
323 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
324 $list[$assoc[
'company_id']][
'company_status'][] = $assoc[
'company_status'];
326 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
327 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
328 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
330 $list[$assoc[
'company_id']][
'facid'][] = $assoc[
'facid'];
331 $list[$assoc[
'company_id']][
'facnum'][] = $assoc[
'facnum'];
332 $list[$assoc[
'company_id']][
'type'][] = $assoc[
'type'];
333 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
334 $list[$assoc[
'company_id']][
'descr'][] = $assoc[
'descr'];
336 $list[$assoc[
'company_id']][
'totalht_list'][] = $assoc[
'total_ht'];
337 $list[$assoc[
'company_id']][
'vat_list'][] = $assoc[
'total_vat'];
338 $list[$assoc[
'company_id']][
'localtax1_list'][] = $assoc[
'total_localtax1'];
339 $list[$assoc[
'company_id']][
'localtax2_list'][] = $assoc[
'total_localtax2'];
341 $list[$assoc[
'company_id']][
'pid'][] = $assoc[
'pid'];
342 $list[$assoc[
'company_id']][
'pref'][] = $assoc[
'pref'];
343 $list[$assoc[
'company_id']][
'ptype'][] = $assoc[
'ptype'];
345 $list[$assoc[
'company_id']][
'payment_id'][] = $assoc[
'payment_id'];
346 $list[$assoc[
'company_id']][
'payment_amount'][] = $assoc[
'payment_amount'];
348 $company_id = $assoc[
'company_id'];
361 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_SERVICE') ==
'invoice')
362 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_SERVICE') ==
'invoice')) {
364 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
365 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
366 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
367 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
368 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
369 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
370 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
371 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
372 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
373 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
374 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
375 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
376 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
377 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
378 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
379 $sql .=
" AND f.fk_statut in (1,2)";
380 if ($direction ==
'buy') {
382 $sql .=
" AND f.type IN (0,1,2,5)";
384 $sql .=
" AND f.type IN (0,1,2,3,5)";
388 $sql .=
" AND f.type IN (0,1,2,5)";
390 $sql .=
" AND f.type IN (0,1,2,3,5)";
393 $sql .=
" AND f.rowid = d.".$fk_facture;
394 $sql .=
" AND s.rowid = f.fk_soc";
397 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
400 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
403 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
404 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
406 if ($date_start && $date_end) {
407 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
409 $sql .=
" AND (d.product_type = 1";
410 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
412 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
414 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
417 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
418 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
419 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
420 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
421 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
422 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
423 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
424 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
425 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
426 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
427 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
428 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
429 $sql .=
" ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf,";
430 $sql .=
" ".MAIN_DB_PREFIX.$paymenttable.
" as pa,";
431 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
432 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
433 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
434 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
435 $sql .=
" AND f.fk_statut in (1,2)";
436 if ($direction ==
'buy') {
438 $sql .=
" AND f.type IN (0,1,2,5)";
440 $sql .=
" AND f.type IN (0,1,2,3,5)";
444 $sql .=
" AND f.type IN (0,1,2,5)";
446 $sql .=
" AND f.type IN (0,1,2,3,5)";
449 $sql .=
" AND f.rowid = d.".$fk_facture;
450 $sql .=
" AND s.rowid = f.fk_soc";
451 $sql .=
" AND pf.".$fk_facture2.
" = f.rowid";
452 $sql .=
" AND pa.rowid = pf.".$fk_payment;
455 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
458 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
461 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
462 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
464 if ($date_start && $date_end) {
465 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
467 $sql .=
" AND (d.product_type = 1";
468 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
470 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
472 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
476 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
479 if ($sql ==
'TODO') {
482 if ($sql !=
'TODO') {
483 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
484 $resql = $db->query($sql);
488 while ($assoc = $db->fetch_array($resql)) {
489 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
490 $list[$assoc[
'company_id']][
'totalht'] = 0;
492 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
493 $list[$assoc[
'company_id']][
'vat'] = 0;
495 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
496 $list[$assoc[
'company_id']][
'localtax1'] = 0;
498 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
499 $list[$assoc[
'company_id']][
'localtax2'] = 0;
502 if ($assoc[
'rowid'] != $oldrowid) {
503 $oldrowid = $assoc[
'rowid'];
504 $list[$assoc[
'company_id']][
'totalht'] += $assoc[
'total_ht'];
505 $list[$assoc[
'company_id']][
'vat'] += $assoc[
'total_vat'];
506 $list[$assoc[
'company_id']][
'localtax1'] += $assoc[
'total_localtax1'];
507 $list[$assoc[
'company_id']][
'localtax2'] += $assoc[
'total_localtax2'];
509 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
510 $list[$assoc[
'company_id']][
'dtype'][] = $assoc[
'dtype'];
511 $list[$assoc[
'company_id']][
'datef'][] = $db->jdate($assoc[
'datef']);
512 $list[$assoc[
'company_id']][
'datep'][] = $db->jdate($assoc[
'datep']);
514 $list[$assoc[
'company_id']][
'company_name'][] = $assoc[
'company_name'];
515 $list[$assoc[
'company_id']][
'company_id'][] = $assoc[
'company_id'];
516 $list[$assoc[
'company_id']][
'company_alias'][] = $assoc[
'company_alias'];
517 $list[$assoc[
'company_id']][
'company_email'][] = $assoc[
'company_email'];
518 $list[$assoc[
'company_id']][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
519 $list[$assoc[
'company_id']][
'company_client'][] = $assoc[
'company_client'];
520 $list[$assoc[
'company_id']][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
521 $list[$assoc[
'company_id']][
'company_customer_code'][] = $assoc[
'company_customer_code'];
522 $list[$assoc[
'company_id']][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
523 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
524 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
525 $list[$assoc[
'company_id']][
'company_status'][] = $assoc[
'company_status'];
527 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
528 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
529 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
531 $list[$assoc[
'company_id']][
'facid'][] = $assoc[
'facid'];
532 $list[$assoc[
'company_id']][
'facnum'][] = $assoc[
'facnum'];
533 $list[$assoc[
'company_id']][
'type'][] = $assoc[
'type'];
534 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
535 $list[$assoc[
'company_id']][
'descr'][] = $assoc[
'descr'];
537 $list[$assoc[
'company_id']][
'totalht_list'][] = $assoc[
'total_ht'];
538 $list[$assoc[
'company_id']][
'vat_list'][] = $assoc[
'total_vat'];
539 $list[$assoc[
'company_id']][
'localtax1_list'][] = $assoc[
'total_localtax1'];
540 $list[$assoc[
'company_id']][
'localtax2_list'][] = $assoc[
'total_localtax2'];
542 $list[$assoc[
'company_id']][
'pid'][] = $assoc[
'pid'];
543 $list[$assoc[
'company_id']][
'pref'][] = $assoc[
'pref'];
544 $list[$assoc[
'company_id']][
'ptype'][] = $assoc[
'ptype'];
546 $list[$assoc[
'company_id']][
'payment_id'][] = $assoc[
'payment_id'];
547 $list[$assoc[
'company_id']][
'payment_ref'][] = $assoc[
'payment_ref'];
548 $list[$assoc[
'company_id']][
'payment_amount'][] = $assoc[
'payment_amount'];
550 $company_id = $assoc[
'company_id'];
561 if ($direction ==
'buy') {
566 $sql =
"SELECT d.rowid, d.product_type as dtype, e.rowid as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.total_tva as total_vat, e.note_private as descr,";
567 $sql .=
" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
568 $sql .=
" e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
569 $sql .=
" e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
570 $sql .=
" p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
571 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport as e";
572 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport_det as d ON d.fk_expensereport = e.rowid ";
573 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"payment_expensereport as p ON p.fk_expensereport = e.rowid ";
574 $sql .=
" WHERE e.entity = ".$conf->entity;
575 $sql .=
" AND e.fk_statut in (6)";
578 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
581 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
584 $sql .=
" AND p.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
585 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
587 if ($date_start && $date_end) {
588 $sql .=
" AND p.datep >= '".$db->idate($date_start).
"' AND p.datep <= '".$db->idate($date_end).
"'";
590 $sql .=
" AND (d.product_type = -1";
591 $sql .=
" OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)";
593 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.total_tva <> 0)";
595 $sql .=
" ORDER BY e.rowid";
598 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
601 if ($sql ==
'TODO') {
604 if ($sql !=
'TODO') {
605 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
606 $resql = $db->query($sql);
610 while ($assoc = $db->fetch_array($resql)) {
611 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
612 $list[$assoc[
'company_id']][
'totalht'] = 0;
614 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
615 $list[$assoc[
'company_id']][
'vat'] = 0;
617 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
618 $list[$assoc[
'company_id']][
'localtax1'] = 0;
620 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
621 $list[$assoc[
'company_id']][
'localtax2'] = 0;
624 if ($assoc[
'rowid'] != $oldrowid) {
625 $oldrowid = $assoc[
'rowid'];
626 $list[$assoc[
'company_id']][
'totalht'] += $assoc[
'total_ht'];
627 $list[$assoc[
'company_id']][
'vat'] += $assoc[
'total_vat'];
628 $list[$assoc[
'company_id']][
'localtax1'] += $assoc[
'total_localtax1'];
629 $list[$assoc[
'company_id']][
'localtax2'] += $assoc[
'total_localtax2'];
632 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
633 $list[$assoc[
'company_id']][
'dtype'][] =
'ExpenseReportPayment';
634 $list[$assoc[
'company_id']][
'datef'][] = $assoc[
'datef'];
636 $list[$assoc[
'company_id']][
'company_name'][] =
'';
637 $list[$assoc[
'company_id']][
'company_id'][] =
'';
638 $list[$assoc[
'company_id']][
'company_alias'][] =
'';
639 $list[$assoc[
'company_id']][
'company_email'][] =
'';
640 $list[$assoc[
'company_id']][
'company_tva_intra'][] =
'';
641 $list[$assoc[
'company_id']][
'company_client'][] =
'';
642 $list[$assoc[
'company_id']][
'company_fournisseur'][] =
'';
643 $list[$assoc[
'company_id']][
'company_customer_code'][] =
'';
644 $list[$assoc[
'company_id']][
'company_supplier_code'][] =
'';
645 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] =
'';
646 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] =
'';
647 $list[$assoc[
'company_id']][
'company_status'][] =
'';
649 $list[$assoc[
'company_id']][
'user_id'][] = $assoc[
'fk_user_author'];
650 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
651 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
652 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
654 $list[$assoc[
'company_id']][
'facid'][] = $assoc[
'facid'];
655 $list[$assoc[
'company_id']][
'facnum'][] = $assoc[
'facnum'];
656 $list[$assoc[
'company_id']][
'type'][] = $assoc[
'type'];
657 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
658 $list[$assoc[
'company_id']][
'descr'][] = $assoc[
'descr'];
660 $list[$assoc[
'company_id']][
'totalht_list'][] = $assoc[
'total_ht'];
661 $list[$assoc[
'company_id']][
'vat_list'][] = $assoc[
'total_vat'];
662 $list[$assoc[
'company_id']][
'localtax1_list'][] = $assoc[
'total_localtax1'];
663 $list[$assoc[
'company_id']][
'localtax2_list'][] = $assoc[
'total_localtax2'];
665 $list[$assoc[
'company_id']][
'pid'][] = $assoc[
'pid'];
666 $list[$assoc[
'company_id']][
'pref'][] = $assoc[
'pref'];
667 $list[$assoc[
'company_id']][
'ptype'][] =
'ExpenseReportPayment';
669 $list[$assoc[
'company_id']][
'payment_id'][] = $assoc[
'payment_id'];
670 $list[$assoc[
'company_id']][
'payment_ref'][] = $assoc[
'payment_ref'];
671 $list[$assoc[
'company_id']][
'payment_amount'][] = $assoc[
'payment_amount'];
673 $company_id = $assoc[
'company_id'];
701function tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
706 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
712 if ($direction ==
'sell') {
713 $invoicetable =
'facture';
714 $invoicedettable =
'facturedet';
715 $fk_facture =
'fk_facture';
716 $fk_facture2 =
'fk_facture';
717 $fk_payment =
'fk_paiement';
718 $total_tva =
'total_tva';
719 $paymenttable =
'paiement';
720 $paymentfacturetable =
'paiement_facture';
721 $invoicefieldref =
'ref';
723 $invoicetable =
'facture_fourn';
724 $invoicedettable =
'facture_fourn_det';
725 $fk_facture =
'fk_facture_fourn';
726 $fk_facture2 =
'fk_facturefourn';
727 $fk_payment =
'fk_paiementfourn';
729 $paymenttable =
'paiementfourn';
730 $paymentfacturetable =
'paiementfourn_facturefourn';
731 $invoicefieldref =
'ref';
734 if (strpos($type,
'localtax') === 0) {
735 $f_rate = $type.
'_tx';
740 $total_localtax1 =
'total_localtax1';
741 $total_localtax2 =
'total_localtax2';
748 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_PRODUCT') ==
'invoice')
749 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_PRODUCT') ==
'invoice')) {
751 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
752 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
753 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
754 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
755 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
756 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
757 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
758 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
759 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
760 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount,";
761 $sql .=
" '' as datep";
762 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
763 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
764 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
"=f.rowid";
765 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
766 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
767 $sql .=
" AND f.fk_statut in (1,2)";
768 if ($direction ==
'buy') {
770 $sql .=
" AND f.type IN (0,1,2,5)";
772 $sql .=
" AND f.type IN (0,1,2,3,5)";
776 $sql .=
" AND f.type IN (0,1,2,5)";
778 $sql .=
" AND f.type IN (0,1,2,3,5)";
783 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
786 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
789 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
790 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
792 if ($date_start && $date_end) {
793 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
795 $sql .=
" AND (d.product_type = 0";
796 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
798 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
800 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
803 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
804 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
805 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
806 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
807 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
808 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
809 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
810 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
811 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
812 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
813 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
814 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
815 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf ON pf.".$fk_facture2.
" = f.rowid";
816 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymenttable.
" as pa ON pa.rowid = pf.".$fk_payment;
817 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
818 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
819 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
820 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
821 $sql .=
" AND f.fk_statut in (1,2)";
822 if ($direction ==
'buy') {
824 $sql .=
" AND f.type IN (0,1,2,5)";
826 $sql .=
" AND f.type IN (0,1,2,3,5)";
830 $sql .=
" AND f.type IN (0,1,2,5)";
832 $sql .=
" AND f.type IN (0,1,2,3,5)";
837 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
840 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
843 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
844 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
846 if ($date_start && $date_end) {
847 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
849 $sql .=
" AND (d.product_type = 0";
850 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
852 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
854 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
860 if ($sql ==
'TODO') {
863 if ($sql !=
'TODO') {
864 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
866 $resql = $db->query($sql);
870 while ($assoc = $db->fetch_array($resql)) {
871 $rate_key = $assoc[
'rate'];
872 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
873 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
877 if (!isset($list[$rate_key][
'totalht'])) {
878 $list[$rate_key][
'totalht'] = 0;
880 if (!isset($list[$rate_key][
'vat'])) {
881 $list[$rate_key][
'vat'] = 0;
883 if (!isset($list[$rate_key][
'localtax1'])) {
884 $list[$rate_key][
'localtax1'] = 0;
886 if (!isset($list[$rate_key][
'localtax2'])) {
887 $list[$rate_key][
'localtax2'] = 0;
890 if ($assoc[
'rowid'] != $oldrowid) {
891 $oldrowid = $assoc[
'rowid'];
892 $list[$rate_key][
'totalht'] += $assoc[
'total_ht'];
893 $list[$rate_key][
'vat'] += $assoc[
'total_vat'];
894 $list[$rate_key][
'localtax1'] += $assoc[
'total_localtax1'];
895 $list[$rate_key][
'localtax2'] += $assoc[
'total_localtax2'];
897 $list[$rate_key][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
898 $list[$rate_key][
'dtype'][] = $assoc[
'dtype'];
899 $list[$rate_key][
'datef'][] = $db->jdate($assoc[
'datef']);
900 $list[$rate_key][
'datep'][] = $db->jdate($assoc[
'datep']);
902 $list[$rate_key][
'company_name'][] = $assoc[
'company_name'];
903 $list[$rate_key][
'company_id'][] = $assoc[
'company_id'];
904 $list[$rate_key][
'company_alias'][] = $assoc[
'company_alias'];
905 $list[$rate_key][
'company_email'][] = $assoc[
'company_email'];
906 $list[$rate_key][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
907 $list[$rate_key][
'company_client'][] = $assoc[
'company_client'];
908 $list[$rate_key][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
909 $list[$rate_key][
'company_customer_code'][] = $assoc[
'company_customer_code'];
910 $list[$rate_key][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
911 $list[$rate_key][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
912 $list[$rate_key][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
913 $list[$rate_key][
'company_status'][] = $assoc[
'company_status'];
915 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
916 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
918 $list[$rate_key][
'facid'][] = $assoc[
'facid'];
919 $list[$rate_key][
'facnum'][] = $assoc[
'facnum'];
920 $list[$rate_key][
'type'][] = $assoc[
'type'];
921 $list[$rate_key][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
922 $list[$rate_key][
'descr'][] = $assoc[
'descr'];
924 $list[$rate_key][
'totalht_list'][] = $assoc[
'total_ht'];
925 $list[$rate_key][
'vat_list'][] = $assoc[
'total_vat'];
926 $list[$rate_key][
'localtax1_list'][] = $assoc[
'total_localtax1'];
927 $list[$rate_key][
'localtax2_list'][] = $assoc[
'total_localtax2'];
929 $list[$rate_key][
'pid'][] = $assoc[
'pid'];
930 $list[$rate_key][
'pref'][] = $assoc[
'pref'];
931 $list[$rate_key][
'ptype'][] = $assoc[
'ptype'];
933 $list[$rate_key][
'payment_id'][] = $assoc[
'payment_id'];
934 $list[$rate_key][
'payment_ref'][] = $assoc[
'payment_ref'];
935 $list[$rate_key][
'payment_amount'][] = $assoc[
'payment_amount'];
937 $rate = $assoc[
'rate'];
950 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_SERVICE') ==
'invoice')
951 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_SERVICE') ==
'invoice')) {
953 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
954 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
955 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
956 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
957 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
958 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
959 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
960 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
961 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
962 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
963 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
964 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
965 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
966 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
967 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
968 $sql .=
" AND f.fk_statut in (1,2)";
969 if ($direction ==
'buy') {
971 $sql .=
" AND f.type IN (0,1,2,5)";
973 $sql .=
" AND f.type IN (0,1,2,3,5)";
977 $sql .=
" AND f.type IN (0,1,2,5)";
979 $sql .=
" AND f.type IN (0,1,2,3,5)";
984 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
987 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
990 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
991 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
993 if ($date_start && $date_end) {
994 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
996 $sql .=
" AND (d.product_type = 1";
997 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
999 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
1001 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
1004 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
1005 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
1006 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
1007 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
1008 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
1009 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
1010 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
1011 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
1012 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
1013 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
1014 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
1015 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
1016 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf ON pf.".$fk_facture2.
" = f.rowid";
1017 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymenttable.
" as pa ON pa.rowid = pf.".$fk_payment;
1018 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
1019 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
1020 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
1021 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
1022 $sql .=
" AND f.fk_statut in (1,2)";
1023 if ($direction ==
'buy') {
1025 $sql .=
" AND f.type IN (0,1,2,5)";
1027 $sql .=
" AND f.type IN (0,1,2,3,5)";
1031 $sql .=
" AND f.type IN (0,1,2,5)";
1033 $sql .=
" AND f.type IN (0,1,2,3,5)";
1038 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
1041 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
1044 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
1045 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
1047 if ($date_start && $date_end) {
1048 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
1050 $sql .=
" AND (d.product_type = 1";
1051 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
1053 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
1055 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
1059 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1062 if ($sql ==
'TODO') {
1065 if ($sql !=
'TODO') {
1066 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
1067 $resql = $db->query($sql);
1071 while ($assoc = $db->fetch_array($resql)) {
1072 $rate_key = $assoc[
'rate'];
1073 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
1074 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
1078 if (!isset($list[$rate_key][
'totalht'])) {
1079 $list[$rate_key][
'totalht'] = 0;
1081 if (!isset($list[$rate_key][
'vat'])) {
1082 $list[$rate_key][
'vat'] = 0;
1084 if (!isset($list[$rate_key][
'localtax1'])) {
1085 $list[$rate_key][
'localtax1'] = 0;
1087 if (!isset($list[$rate_key][
'localtax2'])) {
1088 $list[$rate_key][
'localtax2'] = 0;
1091 if ($assoc[
'rowid'] != $oldrowid) {
1092 $oldrowid = $assoc[
'rowid'];
1093 $list[$rate_key][
'totalht'] += $assoc[
'total_ht'];
1094 $list[$rate_key][
'vat'] += $assoc[
'total_vat'];
1095 $list[$rate_key][
'localtax1'] += $assoc[
'total_localtax1'];
1096 $list[$rate_key][
'localtax2'] += $assoc[
'total_localtax2'];
1098 $list[$rate_key][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
1099 $list[$rate_key][
'dtype'][] = $assoc[
'dtype'];
1100 $list[$rate_key][
'datef'][] = $db->jdate($assoc[
'datef']);
1101 $list[$rate_key][
'datep'][] = $db->jdate($assoc[
'datep']);
1103 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
1104 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
1106 $list[$rate_key][
'company_name'][] = $assoc[
'company_name'];
1107 $list[$rate_key][
'company_id'][] = $assoc[
'company_id'];
1108 $list[$rate_key][
'company_alias'][] = $assoc[
'company_alias'];
1109 $list[$rate_key][
'company_email'][] = $assoc[
'company_email'];
1110 $list[$rate_key][
'company_tva_intra'][] = $assoc[
'company_tva_intra'];
1111 $list[$rate_key][
'company_client'][] = $assoc[
'company_client'];
1112 $list[$rate_key][
'company_fournisseur'][] = $assoc[
'company_fournisseur'];
1113 $list[$rate_key][
'company_customer_code'][] = $assoc[
'company_customer_code'];
1114 $list[$rate_key][
'company_supplier_code'][] = $assoc[
'company_supplier_code'];
1115 $list[$rate_key][
'company_customer_accounting_code'][] = $assoc[
'company_customer_accounting_code'];
1116 $list[$rate_key][
'company_supplier_accounting_code'][] = $assoc[
'company_supplier_accounting_code'];
1117 $list[$rate_key][
'company_status'][] = $assoc[
'company_status'];
1119 $list[$rate_key][
'facid'][] = $assoc[
'facid'];
1120 $list[$rate_key][
'facnum'][] = $assoc[
'facnum'];
1121 $list[$rate_key][
'type'][] = $assoc[
'type'];
1122 $list[$rate_key][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
1123 $list[$rate_key][
'descr'][] = $assoc[
'descr'];
1125 $list[$rate_key][
'totalht_list'][] = $assoc[
'total_ht'];
1126 $list[$rate_key][
'vat_list'][] = $assoc[
'total_vat'];
1127 $list[$rate_key][
'localtax1_list'][] = $assoc[
'total_localtax1'];
1128 $list[$rate_key][
'localtax2_list'][] = $assoc[
'total_localtax2'];
1130 $list[$rate_key][
'pid'][] = $assoc[
'pid'];
1131 $list[$rate_key][
'pref'][] = $assoc[
'pref'];
1132 $list[$rate_key][
'ptype'][] = $assoc[
'ptype'];
1134 $list[$rate_key][
'payment_id'][] = $assoc[
'payment_id'];
1135 $list[$rate_key][
'payment_ref'][] = $assoc[
'payment_ref'];
1136 $list[$rate_key][
'payment_amount'][] = $assoc[
'payment_amount'];
1138 $rate = $assoc[
'rate'];
1149 if ($direction ==
'buy') {
1154 $sql =
"SELECT d.rowid, d.product_type as dtype, e.rowid as facid, d.$f_rate as rate, d.vat_src_code as vat_src_code, d.total_ht as total_ht, d.total_ttc as total_ttc, d.total_tva as total_vat, e.note_private as descr,";
1155 $sql .=
" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
1156 $sql .=
" e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
1157 $sql .=
" e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
1158 $sql .=
" p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
1159 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport as e";
1160 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport_det as d ON d.fk_expensereport = e.rowid";
1161 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"payment_expensereport as p ON p.fk_expensereport = e.rowid";
1162 $sql .=
" WHERE e.entity = ".$conf->entity;
1163 $sql .=
" AND e.fk_statut in (6)";
1166 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
1169 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
1172 $sql .=
" AND p.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
1173 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
1175 if ($date_start && $date_end) {
1176 $sql .=
" AND p.datep >= '".$db->idate($date_start).
"' AND p.datep <= '".$db->idate($date_end).
"'";
1178 $sql .=
" AND (d.product_type = -1";
1179 $sql .=
" OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)";
1181 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.total_tva <> 0)";
1183 $sql .=
" ORDER BY e.rowid";
1186 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1189 if ($sql ==
'TODO') {
1192 if ($sql !=
'TODO') {
1193 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
1194 $resql = $db->query($sql);
1198 while ($assoc = $db->fetch_array($resql)) {
1199 $rate_key = $assoc[
'rate'];
1200 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
1201 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
1205 if (!isset($list[$rate_key][
'totalht'])) {
1206 $list[$rate_key][
'totalht'] = 0;
1208 if (!isset($list[$rate_key][
'vat'])) {
1209 $list[$rate_key][
'vat'] = 0;
1211 if (!isset($list[$rate_key][
'localtax1'])) {
1212 $list[$rate_key][
'localtax1'] = 0;
1214 if (!isset($list[$rate_key][
'localtax2'])) {
1215 $list[$rate_key][
'localtax2'] = 0;
1218 if ($assoc[
'rowid'] != $oldrowid) {
1219 $oldrowid = $assoc[
'rowid'];
1220 $list[$rate_key][
'totalht'] += $assoc[
'total_ht'];
1221 $list[$rate_key][
'vat'] += $assoc[
'total_vat'];
1222 $list[$rate_key][
'localtax1'] += $assoc[
'total_localtax1'];
1223 $list[$rate_key][
'localtax2'] += $assoc[
'total_localtax2'];
1226 $list[$rate_key][
'dtotal_ttc'][] = $assoc[
'total_ttc'];
1227 $list[$rate_key][
'dtype'][] =
'ExpenseReportPayment';
1228 $list[$rate_key][
'datef'][] = $assoc[
'datef'];
1229 $list[$rate_key][
'company_name'][] =
'';
1230 $list[$rate_key][
'company_id'][] =
'';
1231 $list[$rate_key][
'user_id'][] = $assoc[
'fk_user_author'];
1232 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
1233 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
1235 $list[$rate_key][
'facid'][] = $assoc[
'facid'];
1236 $list[$rate_key][
'facnum'][] = $assoc[
'facnum'];
1237 $list[$rate_key][
'type'][] = $assoc[
'type'];
1238 $list[$rate_key][
'ftotal_ttc'][] = $assoc[
'ftotal_ttc'];
1239 $list[$rate_key][
'descr'][] = $assoc[
'descr'];
1241 $list[$rate_key][
'totalht_list'][] = $assoc[
'total_ht'];
1242 $list[$rate_key][
'vat_list'][] = $assoc[
'total_vat'];
1243 $list[$rate_key][
'localtax1_list'][] = $assoc[
'total_localtax1'];
1244 $list[$rate_key][
'localtax2_list'][] = $assoc[
'total_localtax2'];
1246 $list[$rate_key][
'pid'][] = $assoc[
'pid'];
1247 $list[$rate_key][
'pref'][] = $assoc[
'pref'];
1248 $list[$rate_key][
'ptype'][] =
'ExpenseReportPayment';
1250 $list[$rate_key][
'payment_id'][] = $assoc[
'payment_id'];
1251 $list[$rate_key][
'payment_ref'][] = $assoc[
'payment_ref'];
1252 $list[$rate_key][
'payment_amount'][] = $assoc[
'payment_amount'];
1254 $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 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.