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';
145 dol_print_error(
null,
'Invalid "direction" - must be buy or sell - found ' . $direction);
149 if (strpos($type,
'localtax') === 0) {
150 $f_rate = $type.
'_tx';
155 $total_localtax1 =
'total_localtax1';
156 $total_localtax2 =
'total_localtax2';
163 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_PRODUCT') ==
'invoice')
164 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_PRODUCT') ==
'invoice')) {
166 $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,";
167 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
168 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
169 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
170 $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,";
171 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
172 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
173 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
174 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
175 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
176 $sql .=
" ,'' as datep";
177 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
178 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
179 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
180 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
181 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
182 $sql .=
" AND f.fk_statut in (1,2)";
183 if ($direction ==
'buy') {
185 $sql .=
" AND f.type IN (0,1,2,5)";
187 $sql .=
" AND f.type IN (0,1,2,3,5)";
191 $sql .=
" AND f.type IN (0,1,2,5)";
193 $sql .=
" AND f.type IN (0,1,2,3,5)";
196 $sql .=
" AND f.rowid = d.".$fk_facture;
197 $sql .=
" AND s.rowid = f.fk_soc";
200 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
203 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
206 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
207 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
209 if ($date_start && $date_end) {
210 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
212 $sql .=
" AND (d.product_type = 0";
213 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
215 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
217 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
220 $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,";
221 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
222 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
223 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
224 $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,";
225 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
226 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
227 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
228 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
229 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
230 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
231 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
232 $sql .=
" ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf,";
233 $sql .=
" ".MAIN_DB_PREFIX.$paymenttable.
" as pa,";
234 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
235 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
236 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
237 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
238 $sql .=
" AND f.fk_statut in (1,2)";
239 if ($direction ==
'buy') {
241 $sql .=
" AND f.type IN (0,1,2,5)";
243 $sql .=
" AND f.type IN (0,1,2,3,5)";
247 $sql .=
" AND f.type IN (0,1,2,5)";
249 $sql .=
" AND f.type IN (0,1,2,3,5)";
252 $sql .=
" AND f.rowid = d.".$fk_facture;
253 $sql .=
" AND s.rowid = f.fk_soc";
254 $sql .=
" AND pf.".$fk_facture2.
" = f.rowid";
255 $sql .=
" AND pa.rowid = pf.".$fk_payment;
258 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
261 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
264 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
265 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
267 if ($date_start && $date_end) {
268 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
270 $sql .=
" AND (d.product_type = 0";
271 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
273 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
275 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
281 if ($sql ==
'TODO') {
284 if ($sql !=
'TODO') {
285 dol_syslog(
"Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
287 $resql = $db->query($sql);
291 while ($assoc = $db->fetch_array($resql)) {
292 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
293 $list[$assoc[
'company_id']][
'totalht'] = 0;
295 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
296 $list[$assoc[
'company_id']][
'vat'] = 0;
298 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
299 $list[$assoc[
'company_id']][
'localtax1'] = 0;
301 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
302 $list[$assoc[
'company_id']][
'localtax2'] = 0;
305 if ($assoc[
'rowid'] != $oldrowid) {
306 $oldrowid = $assoc[
'rowid'];
307 $list[$assoc[
'company_id']][
'totalht'] += (float) $assoc[
'total_ht'];
308 $list[$assoc[
'company_id']][
'vat'] += (float) $assoc[
'total_vat'];
309 $list[$assoc[
'company_id']][
'localtax1'] += (float) $assoc[
'total_localtax1'];
310 $list[$assoc[
'company_id']][
'localtax2'] += (float) $assoc[
'total_localtax2'];
313 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = (float) $assoc[
'total_ttc'];
314 $list[$assoc[
'company_id']][
'dtype'][] = (int) $assoc[
'dtype'];
315 $list[$assoc[
'company_id']][
'datef'][] = $db->jdate($assoc[
'datef']);
316 $list[$assoc[
'company_id']][
'datep'][] = $db->jdate($assoc[
'datep']);
318 $list[$assoc[
'company_id']][
'company_name'][] = (string) $assoc[
'company_name'];
319 $list[$assoc[
'company_id']][
'company_id'][] = (int) $assoc[
'company_id'];
320 $list[$assoc[
'company_id']][
'company_alias'][] = (string) $assoc[
'company_alias'];
321 $list[$assoc[
'company_id']][
'company_email'][] = (string) $assoc[
'company_email'];
322 $list[$assoc[
'company_id']][
'company_tva_intra'][] = (string) $assoc[
'company_tva_intra'];
323 $list[$assoc[
'company_id']][
'company_client'][] = (int) $assoc[
'company_client'];
324 $list[$assoc[
'company_id']][
'company_fournisseur'][] = (int) $assoc[
'company_fournisseur'];
325 $list[$assoc[
'company_id']][
'company_customer_code'][] = (string) $assoc[
'company_customer_code'];
326 $list[$assoc[
'company_id']][
'company_supplier_code'][] = (string) $assoc[
'company_supplier_code'];
327 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] = (string) $assoc[
'company_customer_accounting_code'];
328 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] = (string) $assoc[
'company_supplier_accounting_code'];
329 $list[$assoc[
'company_id']][
'company_status'][] = (int) $assoc[
'company_status'];
331 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
332 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
333 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
335 $list[$assoc[
'company_id']][
'facid'][] = (int) $assoc[
'facid'];
336 $list[$assoc[
'company_id']][
'facnum'][] = (string) $assoc[
'facnum'];
337 $list[$assoc[
'company_id']][
'type'][] = (int) $assoc[
'type'];
338 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = (float) $assoc[
'ftotal_ttc'];
339 $list[$assoc[
'company_id']][
'descr'][] = (string) $assoc[
'descr'];
341 $list[$assoc[
'company_id']][
'totalht_list'][] = (float) $assoc[
'total_ht'];
342 $list[$assoc[
'company_id']][
'vat_list'][] = (float) $assoc[
'total_vat'];
343 $list[$assoc[
'company_id']][
'localtax1_list'][] = (float) $assoc[
'total_localtax1'];
344 $list[$assoc[
'company_id']][
'localtax2_list'][] = (float) $assoc[
'total_localtax2'];
346 $list[$assoc[
'company_id']][
'pid'][] = (int) $assoc[
'pid'];
347 $list[$assoc[
'company_id']][
'pref'][] = (string) $assoc[
'pref'];
348 $list[$assoc[
'company_id']][
'ptype'][] = (int) $assoc[
'ptype'];
350 $list[$assoc[
'company_id']][
'payment_id'][] = (int) $assoc[
'payment_id'];
351 $list[$assoc[
'company_id']][
'payment_amount'][] = (float) $assoc[
'payment_amount'];
353 $company_id = $assoc[
'company_id'];
366 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_SERVICE') ==
'invoice')
367 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_SERVICE') ==
'invoice')) {
369 $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,";
370 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
371 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
372 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
373 $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,";
374 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
375 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
376 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
377 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
378 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
379 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
380 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
381 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
382 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
383 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
384 $sql .=
" AND f.fk_statut in (1,2)";
385 if ($direction ==
'buy') {
387 $sql .=
" AND f.type IN (0,1,2,5)";
389 $sql .=
" AND f.type IN (0,1,2,3,5)";
393 $sql .=
" AND f.type IN (0,1,2,5)";
395 $sql .=
" AND f.type IN (0,1,2,3,5)";
398 $sql .=
" AND f.rowid = d.".$fk_facture;
399 $sql .=
" AND s.rowid = f.fk_soc";
402 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
405 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
408 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
409 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
411 if ($date_start && $date_end) {
412 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
414 $sql .=
" AND (d.product_type = 1";
415 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
417 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
419 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
422 $sql =
"SELECT d.rowid, d.product_type as dtype, d.".$fk_facture.
" as facid, d.$f_rate as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva.
" as total_vat, d.description as descr,";
423 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
424 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
425 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
426 $sql .=
" s.nom as company_name, s.name_alias as company_alias, s.rowid as company_id, s.client as company_client, s.fournisseur as company_fournisseur, s.email as company_email,";
427 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
428 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
429 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
430 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype, p.tosell as pstatus, p.tobuy as pstatusbuy,";
431 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
432 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
433 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f,";
434 $sql .=
" ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf,";
435 $sql .=
" ".MAIN_DB_PREFIX.$paymenttable.
" as pa,";
436 $sql .=
" ".MAIN_DB_PREFIX.
"societe as s,";
437 $sql .=
" ".MAIN_DB_PREFIX.$invoicedettable.
" as d";
438 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
439 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
440 $sql .=
" AND f.fk_statut in (1,2)";
441 if ($direction ==
'buy') {
443 $sql .=
" AND f.type IN (0,1,2,5)";
445 $sql .=
" AND f.type IN (0,1,2,3,5)";
449 $sql .=
" AND f.type IN (0,1,2,5)";
451 $sql .=
" AND f.type IN (0,1,2,3,5)";
454 $sql .=
" AND f.rowid = d.".$fk_facture;
455 $sql .=
" AND s.rowid = f.fk_soc";
456 $sql .=
" AND pf.".$fk_facture2.
" = f.rowid";
457 $sql .=
" AND pa.rowid = pf.".$fk_payment;
460 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
463 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
466 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
467 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
469 if ($date_start && $date_end) {
470 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
472 $sql .=
" AND (d.product_type = 1";
473 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
475 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
477 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
481 dol_syslog(
"Tax.lib.php::tax_by_thirdparty no accountancy module enabled".$sql, LOG_ERR);
484 if ($sql ==
'TODO') {
487 if ($sql !=
'TODO') {
488 dol_syslog(
"Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
489 $resql = $db->query($sql);
493 while ($assoc = $db->fetch_array($resql)) {
494 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
495 $list[$assoc[
'company_id']][
'totalht'] = 0;
497 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
498 $list[$assoc[
'company_id']][
'vat'] = 0;
500 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
501 $list[$assoc[
'company_id']][
'localtax1'] = 0;
503 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
504 $list[$assoc[
'company_id']][
'localtax2'] = 0;
507 if ($assoc[
'rowid'] != $oldrowid) {
508 $oldrowid = $assoc[
'rowid'];
509 $list[$assoc[
'company_id']][
'totalht'] += (float) $assoc[
'total_ht'];
510 $list[$assoc[
'company_id']][
'vat'] += (float) $assoc[
'total_vat'];
511 $list[$assoc[
'company_id']][
'localtax1'] += (float) $assoc[
'total_localtax1'];
512 $list[$assoc[
'company_id']][
'localtax2'] += (float) $assoc[
'total_localtax2'];
514 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = (float) $assoc[
'total_ttc'];
515 $list[$assoc[
'company_id']][
'dtype'][] = $assoc[
'dtype'];
516 $list[$assoc[
'company_id']][
'datef'][] = $db->jdate($assoc[
'datef']);
517 $list[$assoc[
'company_id']][
'datep'][] = $db->jdate($assoc[
'datep']);
519 $list[$assoc[
'company_id']][
'company_name'][] = (string) $assoc[
'company_name'];
520 $list[$assoc[
'company_id']][
'company_id'][] = (int) $assoc[
'company_id'];
521 $list[$assoc[
'company_id']][
'company_alias'][] = (string) $assoc[
'company_alias'];
522 $list[$assoc[
'company_id']][
'company_email'][] = (string) $assoc[
'company_email'];
523 $list[$assoc[
'company_id']][
'company_tva_intra'][] = (string) $assoc[
'company_tva_intra'];
524 $list[$assoc[
'company_id']][
'company_client'][] = (int) $assoc[
'company_client'];
525 $list[$assoc[
'company_id']][
'company_fournisseur'][] = (int) $assoc[
'company_fournisseur'];
526 $list[$assoc[
'company_id']][
'company_customer_code'][] = (string) $assoc[
'company_customer_code'];
527 $list[$assoc[
'company_id']][
'company_supplier_code'][] = (string) $assoc[
'company_supplier_code'];
528 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] = (string) $assoc[
'company_customer_accounting_code'];
529 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] = (string) $assoc[
'company_supplier_accounting_code'];
530 $list[$assoc[
'company_id']][
'company_status'][] = (int) $assoc[
'company_status'];
532 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
533 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
534 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
536 $list[$assoc[
'company_id']][
'facid'][] = (int) $assoc[
'facid'];
537 $list[$assoc[
'company_id']][
'facnum'][] = (string) $assoc[
'facnum'];
538 $list[$assoc[
'company_id']][
'type'][] = (int) $assoc[
'type'];
539 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = (float) $assoc[
'ftotal_ttc'];
540 $list[$assoc[
'company_id']][
'descr'][] = (string) $assoc[
'descr'];
542 $list[$assoc[
'company_id']][
'totalht_list'][] = (float) $assoc[
'total_ht'];
543 $list[$assoc[
'company_id']][
'vat_list'][] = (float) $assoc[
'total_vat'];
544 $list[$assoc[
'company_id']][
'localtax1_list'][] = (float) $assoc[
'total_localtax1'];
545 $list[$assoc[
'company_id']][
'localtax2_list'][] = (float) $assoc[
'total_localtax2'];
547 $list[$assoc[
'company_id']][
'pid'][] = (int) $assoc[
'pid'];
548 $list[$assoc[
'company_id']][
'pref'][] = (string) $assoc[
'pref'];
549 $list[$assoc[
'company_id']][
'ptype'][] = (int) $assoc[
'ptype'];
551 $list[$assoc[
'company_id']][
'payment_id'][] = (int) $assoc[
'payment_id'];
552 $list[$assoc[
'company_id']][
'payment_ref'][] = (string) $assoc[
'payment_ref'];
553 $list[$assoc[
'company_id']][
'payment_amount'][] = (float) $assoc[
'payment_amount'];
555 $company_id = $assoc[
'company_id'];
566 if ($direction ==
'buy') {
571 $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,";
572 $sql .=
" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
573 $sql .=
" e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
574 $sql .=
" e.ref as facnum, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
575 $sql .=
" p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid, e.ref as pref";
576 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport as e";
577 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport_det as d ON d.fk_expensereport = e.rowid ";
578 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"payment_expensereport as p ON p.fk_expensereport = e.rowid ";
579 $sql .=
" WHERE e.entity = ".$conf->entity;
580 $sql .=
" AND e.fk_statut in (6)";
583 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
586 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
589 $sql .=
" AND p.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
590 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
592 if ($date_start && $date_end) {
593 $sql .=
" AND p.datep >= '".$db->idate($date_start).
"' AND p.datep <= '".$db->idate($date_end).
"'";
595 $sql .=
" AND (d.product_type = -1";
596 $sql .=
" OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)";
598 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.total_tva <> 0)";
600 $sql .=
" ORDER BY e.rowid";
603 dol_syslog(
"Tax.lib.php::tax_by_thirdparty no accountancy module enabled".$sql, LOG_ERR);
606 if ($sql ==
'TODO') {
609 if ($sql !=
'TODO') {
610 dol_syslog(
"Tax.lib.php::tax_by_thirdparty", LOG_DEBUG);
611 $resql = $db->query($sql);
615 while ($assoc = $db->fetch_array($resql)) {
616 if (!isset($list[$assoc[
'company_id']][
'totalht'])) {
617 $list[$assoc[
'company_id']][
'totalht'] = 0;
619 if (!isset($list[$assoc[
'company_id']][
'vat'])) {
620 $list[$assoc[
'company_id']][
'vat'] = 0;
622 if (!isset($list[$assoc[
'company_id']][
'localtax1'])) {
623 $list[$assoc[
'company_id']][
'localtax1'] = 0;
625 if (!isset($list[$assoc[
'company_id']][
'localtax2'])) {
626 $list[$assoc[
'company_id']][
'localtax2'] = 0;
629 if ($assoc[
'rowid'] != $oldrowid) {
630 $oldrowid = $assoc[
'rowid'];
631 $list[$assoc[
'company_id']][
'totalht'] += (float) $assoc[
'total_ht'];
632 $list[$assoc[
'company_id']][
'vat'] += (float) $assoc[
'total_vat'];
633 $list[$assoc[
'company_id']][
'localtax1'] += (float) $assoc[
'total_localtax1'];
634 $list[$assoc[
'company_id']][
'localtax2'] += (float) $assoc[
'total_localtax2'];
637 $list[$assoc[
'company_id']][
'dtotal_ttc'][] = (float) $assoc[
'total_ttc'];
638 $list[$assoc[
'company_id']][
'dtype'][] =
'ExpenseReportPayment';
639 $list[$assoc[
'company_id']][
'datef'][] = (int) $assoc[
'datef'];
641 $list[$assoc[
'company_id']][
'company_name'][] =
'';
642 $list[$assoc[
'company_id']][
'company_id'][] = 0;
643 $list[$assoc[
'company_id']][
'company_alias'][] =
'';
644 $list[$assoc[
'company_id']][
'company_email'][] =
'';
645 $list[$assoc[
'company_id']][
'company_tva_intra'][] =
'';
646 $list[$assoc[
'company_id']][
'company_client'][] = 0;
647 $list[$assoc[
'company_id']][
'company_fournisseur'][] = 0;
648 $list[$assoc[
'company_id']][
'company_customer_code'][] =
'';
649 $list[$assoc[
'company_id']][
'company_supplier_code'][] =
'';
650 $list[$assoc[
'company_id']][
'company_customer_accounting_code'][] =
'';
651 $list[$assoc[
'company_id']][
'company_supplier_accounting_code'][] =
'';
652 $list[$assoc[
'company_id']][
'company_status'][] = 0;
654 $list[$assoc[
'company_id']][
'user_id'][] = (int) $assoc[
'fk_user_author'];
655 $list[$assoc[
'company_id']][
'drate'][] = $assoc[
'rate'];
656 $list[$assoc[
'company_id']][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
657 $list[$assoc[
'company_id']][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
659 $list[$assoc[
'company_id']][
'facid'][] = (int) $assoc[
'facid'];
660 $list[$assoc[
'company_id']][
'facnum'][] = (string) $assoc[
'facnum'];
661 $list[$assoc[
'company_id']][
'type'][] = (int) $assoc[
'type'];
662 $list[$assoc[
'company_id']][
'ftotal_ttc'][] = (float) $assoc[
'ftotal_ttc'];
663 $list[$assoc[
'company_id']][
'descr'][] = (string) $assoc[
'descr'];
665 $list[$assoc[
'company_id']][
'totalht_list'][] = (float) $assoc[
'total_ht'];
666 $list[$assoc[
'company_id']][
'vat_list'][] = (float) $assoc[
'total_vat'];
667 $list[$assoc[
'company_id']][
'localtax1_list'][] = (float) $assoc[
'total_localtax1'];
668 $list[$assoc[
'company_id']][
'localtax2_list'][] = (float) $assoc[
'total_localtax2'];
670 $list[$assoc[
'company_id']][
'pid'][] = (int) $assoc[
'pid'];
671 $list[$assoc[
'company_id']][
'pref'][] = (string) $assoc[
'pref'];
672 $list[$assoc[
'company_id']][
'ptype'][] =
'ExpenseReportPayment';
674 $list[$assoc[
'company_id']][
'payment_id'][] = (int) $assoc[
'payment_id'];
675 $list[$assoc[
'company_id']][
'payment_ref'][] = (string) $assoc[
'payment_ref'];
676 $list[$assoc[
'company_id']][
'payment_amount'][] = (float) $assoc[
'payment_amount'];
678 $company_id = $assoc[
'company_id'];
707function tax_by_rate($type, $db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
712 if (($date_start || $date_end) && (!empty($y) || !empty($m) || !empty($q))) {
718 if ($direction ==
'sell') {
719 $invoicetable =
'facture';
720 $invoicedettable =
'facturedet';
721 $fk_facture =
'fk_facture';
722 $fk_facture2 =
'fk_facture';
723 $fk_payment =
'fk_paiement';
724 $total_tva =
'total_tva';
725 $paymenttable =
'paiement';
726 $paymentfacturetable =
'paiement_facture';
727 $invoicefieldref =
'ref';
729 $invoicetable =
'facture_fourn';
730 $invoicedettable =
'facture_fourn_det';
731 $fk_facture =
'fk_facture_fourn';
732 $fk_facture2 =
'fk_facturefourn';
733 $fk_payment =
'fk_paiementfourn';
735 $paymenttable =
'paiementfourn';
736 $paymentfacturetable =
'paiementfourn_facturefourn';
737 $invoicefieldref =
'ref';
740 if (strpos($type,
'localtax') === 0) {
741 $f_rate = $type.
'_tx';
746 $total_localtax1 =
'total_localtax1';
747 $total_localtax2 =
'total_localtax2';
754 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_PRODUCT') ==
'invoice')
755 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_PRODUCT') ==
'invoice')) {
757 $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,";
758 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
759 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
760 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
761 $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,";
762 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
763 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
764 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
765 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
766 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount,";
767 $sql .=
" '' as datep";
768 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
769 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
770 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
"=f.rowid";
771 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
772 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
773 $sql .=
" AND f.fk_statut in (1,2)";
774 if ($direction ==
'buy') {
776 $sql .=
" AND f.type IN (0,1,2,5)";
778 $sql .=
" AND f.type IN (0,1,2,3,5)";
782 $sql .=
" AND f.type IN (0,1,2,5)";
784 $sql .=
" AND f.type IN (0,1,2,3,5)";
789 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
792 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
795 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
796 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
798 if ($date_start && $date_end) {
799 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
801 $sql .=
" AND (d.product_type = 0";
802 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
804 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
806 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
809 $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,";
810 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
811 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
812 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
813 $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,";
814 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
815 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
816 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
817 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
818 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
819 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
820 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
821 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf ON pf.".$fk_facture2.
" = f.rowid";
822 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymenttable.
" as pa ON pa.rowid = pf.".$fk_payment;
823 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
824 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
825 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
826 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
827 $sql .=
" AND f.fk_statut in (1,2)";
828 if ($direction ==
'buy') {
830 $sql .=
" AND f.type IN (0,1,2,5)";
832 $sql .=
" AND f.type IN (0,1,2,3,5)";
836 $sql .=
" AND f.type IN (0,1,2,5)";
838 $sql .=
" AND f.type IN (0,1,2,3,5)";
843 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
846 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
849 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
850 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
852 if ($date_start && $date_end) {
853 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
855 $sql .=
" AND (d.product_type = 0";
856 $sql .=
" AND d.date_start is null AND d.date_end IS NULL)";
858 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
860 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
866 if ($sql ==
'TODO') {
869 if ($sql !=
'TODO') {
870 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
872 $resql = $db->query($sql);
876 while ($assoc = $db->fetch_array($resql)) {
877 $rate_key = $assoc[
'rate'];
878 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
879 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
883 if (!isset($list[$rate_key][
'totalht'])) {
884 $list[$rate_key][
'totalht'] = 0;
886 if (!isset($list[$rate_key][
'vat'])) {
887 $list[$rate_key][
'vat'] = 0;
889 if (!isset($list[$rate_key][
'localtax1'])) {
890 $list[$rate_key][
'localtax1'] = 0;
892 if (!isset($list[$rate_key][
'localtax2'])) {
893 $list[$rate_key][
'localtax2'] = 0;
896 if ($assoc[
'rowid'] != $oldrowid) {
897 $oldrowid = $assoc[
'rowid'];
898 $list[$rate_key][
'totalht'] += (float) $assoc[
'total_ht'];
899 $list[$rate_key][
'vat'] += (float) $assoc[
'total_vat'];
900 $list[$rate_key][
'localtax1'] += (float) $assoc[
'total_localtax1'];
901 $list[$rate_key][
'localtax2'] += (float) $assoc[
'total_localtax2'];
903 $list[$rate_key][
'dtotal_ttc'][] = (float) $assoc[
'total_ttc'];
904 $list[$rate_key][
'dtype'][] = (int) $assoc[
'dtype'];
905 $list[$rate_key][
'datef'][] = $db->jdate($assoc[
'datef']);
906 $list[$rate_key][
'datep'][] = $db->jdate($assoc[
'datep']);
908 $list[$rate_key][
'company_name'][] = (string) $assoc[
'company_name'];
909 $list[$rate_key][
'company_id'][] = (int) $assoc[
'company_id'];
910 $list[$rate_key][
'company_alias'][] = (string) $assoc[
'company_alias'];
911 $list[$rate_key][
'company_email'][] = (string) $assoc[
'company_email'];
912 $list[$rate_key][
'company_tva_intra'][] = (string) $assoc[
'company_tva_intra'];
913 $list[$rate_key][
'company_client'][] = (int) $assoc[
'company_client'];
914 $list[$rate_key][
'company_fournisseur'][] = (int) $assoc[
'company_fournisseur'];
915 $list[$rate_key][
'company_customer_code'][] = (string) $assoc[
'company_customer_code'];
916 $list[$rate_key][
'company_supplier_code'][] = (string) $assoc[
'company_supplier_code'];
917 $list[$rate_key][
'company_customer_accounting_code'][] = (string) $assoc[
'company_customer_accounting_code'];
918 $list[$rate_key][
'company_supplier_accounting_code'][] = (string) $assoc[
'company_supplier_accounting_code'];
919 $list[$rate_key][
'company_status'][] = (int) $assoc[
'company_status'];
921 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
922 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
924 $list[$rate_key][
'facid'][] = (int) $assoc[
'facid'];
925 $list[$rate_key][
'facnum'][] = (string) $assoc[
'facnum'];
926 $list[$rate_key][
'type'][] = (int) $assoc[
'type'];
927 $list[$rate_key][
'ftotal_ttc'][] = (float) $assoc[
'ftotal_ttc'];
928 $list[$rate_key][
'descr'][] = (string) $assoc[
'descr'];
930 $list[$rate_key][
'totalht_list'][] = (float) $assoc[
'total_ht'];
931 $list[$rate_key][
'vat_list'][] = (float) $assoc[
'total_vat'];
932 $list[$rate_key][
'localtax1_list'][] = (float) $assoc[
'total_localtax1'];
933 $list[$rate_key][
'localtax2_list'][] = (float) $assoc[
'total_localtax2'];
935 $list[$rate_key][
'pid'][] = (int) $assoc[
'pid'];
936 $list[$rate_key][
'pref'][] = (string) $assoc[
'pref'];
937 $list[$rate_key][
'ptype'][] = (int) $assoc[
'ptype'];
939 $list[$rate_key][
'payment_id'][] = (int) $assoc[
'payment_id'];
940 $list[$rate_key][
'payment_ref'][] = (string) $assoc[
'payment_ref'];
941 $list[$rate_key][
'payment_amount'][] = (float) $assoc[
'payment_amount'];
943 $rate = $assoc[
'rate'];
956 if (($direction ==
'sell' &&
getDolGlobalString(
'TAX_MODE_SELL_SERVICE') ==
'invoice')
957 || ($direction ==
'buy' &&
getDolGlobalString(
'TAX_MODE_BUY_SERVICE') ==
'invoice')) {
959 $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,";
960 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
961 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
962 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
963 $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,";
964 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
965 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
966 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
967 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
968 $sql .=
" 0 as payment_id, '' as payment_ref, 0 as payment_amount";
969 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
970 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
971 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
972 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
973 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
974 $sql .=
" AND f.fk_statut in (1,2)";
975 if ($direction ==
'buy') {
977 $sql .=
" AND f.type IN (0,1,2,5)";
979 $sql .=
" AND f.type IN (0,1,2,3,5)";
983 $sql .=
" AND f.type IN (0,1,2,5)";
985 $sql .=
" AND f.type IN (0,1,2,3,5)";
990 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
993 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
996 $sql .=
" AND f.datef > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
997 $sql .=
" AND f.datef <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
999 if ($date_start && $date_end) {
1000 $sql .=
" AND f.datef >= '".$db->idate($date_start).
"' AND f.datef <= '".$db->idate($date_end).
"'";
1002 $sql .=
" AND (d.product_type = 1";
1003 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
1005 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
1007 $sql .=
" ORDER BY d.rowid, d.".$fk_facture;
1010 $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,";
1011 $sql .=
" d.".$total_localtax1.
" as total_localtax1, d.".$total_localtax2.
" as total_localtax2, ";
1012 $sql .=
" d.date_start as date_start, d.date_end as date_end,";
1013 $sql .=
" f.".$invoicefieldref.
" as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef,";
1014 $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,";
1015 $sql .=
" s.code_client as company_customer_code, s.code_fournisseur as company_supplier_code,";
1016 $sql .=
" s.code_compta as company_customer_accounting_code, s.code_compta_fournisseur as company_supplier_accounting_code,";
1017 $sql .=
" s.status as company_status, s.tva_intra as company_tva_intra,";
1018 $sql .=
" p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
1019 $sql .=
" pf.".$fk_payment.
" as payment_id, pf.amount as payment_amount,";
1020 $sql .=
" pa.datep as datep, pa.ref as payment_ref";
1021 $sql .=
" FROM ".MAIN_DB_PREFIX.$invoicetable.
" as f";
1022 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymentfacturetable.
" as pf ON pf.".$fk_facture2.
" = f.rowid";
1023 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$paymenttable.
" as pa ON pa.rowid = pf.".$fk_payment;
1024 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
1025 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.$invoicedettable.
" as d ON d.".$fk_facture.
" = f.rowid";
1026 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p on d.fk_product = p.rowid";
1027 $sql .=
" WHERE f.entity IN (".getEntity($invoicetable).
")";
1028 $sql .=
" AND f.fk_statut in (1,2)";
1029 if ($direction ==
'buy') {
1031 $sql .=
" AND f.type IN (0,1,2,5)";
1033 $sql .=
" AND f.type IN (0,1,2,3,5)";
1037 $sql .=
" AND f.type IN (0,1,2,5)";
1039 $sql .=
" AND f.type IN (0,1,2,3,5)";
1044 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
1047 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
1050 $sql .=
" AND pa.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
1051 $sql .=
" AND pa.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
1053 if ($date_start && $date_end) {
1054 $sql .=
" AND pa.datep >= '".$db->idate($date_start).
"' AND pa.datep <= '".$db->idate($date_end).
"'";
1056 $sql .=
" AND (d.product_type = 1";
1057 $sql .=
" OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
1059 $sql .=
" AND (d.".$f_rate.
" <> 0 OR d.".$total_tva.
" <> 0)";
1061 $sql .=
" ORDER BY d.rowid, d.".$fk_facture.
", pf.rowid";
1065 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1068 if ($sql ==
'TODO') {
1071 if ($sql !=
'TODO') {
1072 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
1073 $resql = $db->query($sql);
1077 while ($assoc = $db->fetch_array($resql)) {
1078 $rate_key = $assoc[
'rate'];
1079 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
1080 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
1084 if (!isset($list[$rate_key][
'totalht'])) {
1085 $list[$rate_key][
'totalht'] = 0;
1087 if (!isset($list[$rate_key][
'vat'])) {
1088 $list[$rate_key][
'vat'] = 0;
1090 if (!isset($list[$rate_key][
'localtax1'])) {
1091 $list[$rate_key][
'localtax1'] = 0;
1093 if (!isset($list[$rate_key][
'localtax2'])) {
1094 $list[$rate_key][
'localtax2'] = 0;
1097 if ($assoc[
'rowid'] != $oldrowid) {
1098 $oldrowid = $assoc[
'rowid'];
1099 $list[$rate_key][
'totalht'] += (float) $assoc[
'total_ht'];
1100 $list[$rate_key][
'vat'] += (float) $assoc[
'total_vat'];
1101 $list[$rate_key][
'localtax1'] += (float) $assoc[
'total_localtax1'];
1102 $list[$rate_key][
'localtax2'] += (float) $assoc[
'total_localtax2'];
1104 $list[$rate_key][
'dtotal_ttc'][] = (float) $assoc[
'total_ttc'];
1105 $list[$rate_key][
'dtype'][] = (int) $assoc[
'dtype'];
1106 $list[$rate_key][
'datef'][] = $db->jdate($assoc[
'datef']);
1107 $list[$rate_key][
'datep'][] = $db->jdate($assoc[
'datep']);
1109 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
1110 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
1112 $list[$rate_key][
'company_name'][] = (string) $assoc[
'company_name'];
1113 $list[$rate_key][
'company_id'][] = (int) $assoc[
'company_id'];
1114 $list[$rate_key][
'company_alias'][] = (string) $assoc[
'company_alias'];
1115 $list[$rate_key][
'company_email'][] = (string) $assoc[
'company_email'];
1116 $list[$rate_key][
'company_tva_intra'][] = (string) $assoc[
'company_tva_intra'];
1117 $list[$rate_key][
'company_client'][] = (int) $assoc[
'company_client'];
1118 $list[$rate_key][
'company_fournisseur'][] = (int) $assoc[
'company_fournisseur'];
1119 $list[$rate_key][
'company_customer_code'][] = (string) $assoc[
'company_customer_code'];
1120 $list[$rate_key][
'company_supplier_code'][] = (string) $assoc[
'company_supplier_code'];
1121 $list[$rate_key][
'company_customer_accounting_code'][] = (string) $assoc[
'company_customer_accounting_code'];
1122 $list[$rate_key][
'company_supplier_accounting_code'][] = (string) $assoc[
'company_supplier_accounting_code'];
1123 $list[$rate_key][
'company_status'][] = (int) $assoc[
'company_status'];
1125 $list[$rate_key][
'facid'][] = (int) $assoc[
'facid'];
1126 $list[$rate_key][
'facnum'][] = (string) $assoc[
'facnum'];
1127 $list[$rate_key][
'type'][] = (int) $assoc[
'type'];
1128 $list[$rate_key][
'ftotal_ttc'][] = (float) $assoc[
'ftotal_ttc'];
1129 $list[$rate_key][
'descr'][] = (string) $assoc[
'descr'];
1131 $list[$rate_key][
'totalht_list'][] = (float) $assoc[
'total_ht'];
1132 $list[$rate_key][
'vat_list'][] = (float) $assoc[
'total_vat'];
1133 $list[$rate_key][
'localtax1_list'][] = (float) $assoc[
'total_localtax1'];
1134 $list[$rate_key][
'localtax2_list'][] = (float) $assoc[
'total_localtax2'];
1136 $list[$rate_key][
'pid'][] = (int) $assoc[
'pid'];
1137 $list[$rate_key][
'pref'][] = (string) $assoc[
'pref'];
1138 $list[$rate_key][
'ptype'][] = (int) $assoc[
'ptype'];
1140 $list[$rate_key][
'payment_id'][] = (int) $assoc[
'payment_id'];
1141 $list[$rate_key][
'payment_ref'][] = (string) $assoc[
'payment_ref'];
1142 $list[$rate_key][
'payment_amount'][] = (float) $assoc[
'payment_amount'];
1144 $rate = $assoc[
'rate'];
1155 if ($direction ==
'buy') {
1160 $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,";
1161 $sql .=
" d.total_localtax1 as total_localtax1, d.total_localtax2 as total_localtax2, ";
1162 $sql .=
" e.date_debut as date_start, e.date_fin as date_end, e.fk_user_author,";
1163 $sql .=
" e.ref as facnum, e.ref as pref, e.total_ttc as ftotal_ttc, e.date_create, d.fk_c_type_fees as type,";
1164 $sql .=
" p.fk_bank as payment_id, p.amount as payment_amount, p.rowid as pid";
1165 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport as e";
1166 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport_det as d ON d.fk_expensereport = e.rowid";
1167 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"payment_expensereport as p ON p.fk_expensereport = e.rowid";
1168 $sql .=
" WHERE e.entity = ".$conf->entity;
1169 $sql .=
" AND e.fk_statut in (6)";
1172 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, $m,
false)).
"'";
1175 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, 12,
false)).
"'";
1178 $sql .=
" AND p.datep > '".$db->idate(
dol_get_first_day($y, (($q - 1) * 3) + 1,
false)).
"'";
1179 $sql .=
" AND p.datep <= '".$db->idate(
dol_get_last_day($y, ($q * 3),
false)).
"'";
1181 if ($date_start && $date_end) {
1182 $sql .=
" AND p.datep >= '".$db->idate($date_start).
"' AND p.datep <= '".$db->idate($date_end).
"'";
1184 $sql .=
" AND (d.product_type = -1";
1185 $sql .=
" OR e.date_debut is NOT null OR e.date_fin IS NOT NULL)";
1187 $sql .=
" AND (d.".$db->sanitize($f_rate).
" <> 0 OR d.total_tva <> 0)";
1189 $sql .=
" ORDER BY e.rowid";
1192 dol_syslog(
"Tax.lib.php::tax_by_rate no accountancy module enabled".$sql, LOG_ERR);
1195 if ($sql ==
'TODO') {
1198 if ($sql !=
'TODO') {
1199 dol_syslog(
"Tax.lib.php::tax_by_rate", LOG_DEBUG);
1200 $resql = $db->query($sql);
1204 while ($assoc = $db->fetch_array($resql)) {
1205 $rate_key = $assoc[
'rate'];
1206 if ($f_rate ==
'tva_tx' && !empty($assoc[
'vat_src_code']) && !preg_match(
'/\(/', $rate_key)) {
1207 $rate_key .=
' (' . $assoc[
'vat_src_code'] .
')';
1211 if (!isset($list[$rate_key][
'totalht'])) {
1212 $list[$rate_key][
'totalht'] = 0;
1214 if (!isset($list[$rate_key][
'vat'])) {
1215 $list[$rate_key][
'vat'] = 0;
1217 if (!isset($list[$rate_key][
'localtax1'])) {
1218 $list[$rate_key][
'localtax1'] = 0;
1220 if (!isset($list[$rate_key][
'localtax2'])) {
1221 $list[$rate_key][
'localtax2'] = 0;
1224 if ($assoc[
'rowid'] != $oldrowid) {
1225 $oldrowid = $assoc[
'rowid'];
1226 $list[$rate_key][
'totalht'] += (float) $assoc[
'total_ht'];
1227 $list[$rate_key][
'vat'] += (float) $assoc[
'total_vat'];
1228 $list[$rate_key][
'localtax1'] += (float) $assoc[
'total_localtax1'];
1229 $list[$rate_key][
'localtax2'] += (float) $assoc[
'total_localtax2'];
1232 $list[$rate_key][
'dtotal_ttc'][] = (float) $assoc[
'total_ttc'];
1233 $list[$rate_key][
'dtype'][] =
'ExpenseReportPayment';
1234 $list[$rate_key][
'datef'][] = (int) $assoc[
'datef'];
1235 $list[$rate_key][
'company_name'][] =
'';
1236 $list[$rate_key][
'company_id'][] = 0;
1237 $list[$rate_key][
'user_id'][] = (int) $assoc[
'fk_user_author'];
1238 $list[$rate_key][
'ddate_start'][] = $db->jdate($assoc[
'date_start']);
1239 $list[$rate_key][
'ddate_end'][] = $db->jdate($assoc[
'date_end']);
1241 $list[$rate_key][
'facid'][] = (int) $assoc[
'facid'];
1242 $list[$rate_key][
'facnum'][] = (string) $assoc[
'facnum'];
1243 $list[$rate_key][
'type'][] = (int) $assoc[
'type'];
1244 $list[$rate_key][
'ftotal_ttc'][] = (float) $assoc[
'ftotal_ttc'];
1245 $list[$rate_key][
'descr'][] = (string) $assoc[
'descr'];
1247 $list[$rate_key][
'totalht_list'][] = (float) $assoc[
'total_ht'];
1248 $list[$rate_key][
'vat_list'][] = (float) $assoc[
'total_vat'];
1249 $list[$rate_key][
'localtax1_list'][] = (float) $assoc[
'total_localtax1'];
1250 $list[$rate_key][
'localtax2_list'][] = (float) $assoc[
'total_localtax2'];
1252 $list[$rate_key][
'pid'][] = (int) $assoc[
'pid'];
1253 $list[$rate_key][
'pref'][] = (string) $assoc[
'pref'];
1254 $list[$rate_key][
'ptype'][] =
'ExpenseReportPayment';
1256 $list[$rate_key][
'payment_id'][] = (int) $assoc[
'payment_id'];
1257 $list[$rate_key][
'payment_ref'][] = (string) $assoc[
'payment_ref'];
1258 $list[$rate_key][
'payment_amount'][] = (float) $assoc[
'payment_amount'];
1260 $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.
global $conf
The following vars must be defined: $type2label $form $conf, $lang, The following vars may also be de...
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.