dolibarr  7.0.0-beta
clientfourn.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2002-2006 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3  * Copyright (C) 2004-2017 Laurent Destailleur <eldy@users.sourceforge.net>
4  * Copyright (C) 2005-2012 Regis Houssin <regis.houssin@capnetworks.com>
5  * Copyright (C) 2012 Cédric Salvador <csalvador@gpcsolutions.fr>
6  * Copyright (C) 2012-2014 Raphaël Dourseanud <rdoursenaud@gpcsolutions.fr>
7  * Copyright (C) 2014-2106 Ferran Marcet <fmarcet@2byte.es>
8  * Copyright (C) 2014 Juanjo Menent <jmenent@2byte.es>
9  * Copyright (C) 2014 Florian Henry <florian.henry@open-concept.pro>
10  *
11  * This program is free software; you can redistribute it and/or modify
12  * it under the terms of the GNU General Public License as published by
13  * the Free Software Foundation; either version 3 of the License, or
14  * (at your option) any later version.
15  *
16  * This program is distributed in the hope that it will be useful,
17  * but WITHOUT ANY WARRANTY; without even the implied warranty of
18  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19  * GNU General Public License for more details.
20  *
21  * You should have received a copy of the GNU General Public License
22  * along with this program. If not, see <http://www.gnu.org/licenses/>.
23  */
24 
31 require '../../main.inc.php';
32 require_once DOL_DOCUMENT_ROOT.'/compta/tva/class/tva.class.php';
33 require_once DOL_DOCUMENT_ROOT.'/compta/sociales/class/chargesociales.class.php';
34 require_once DOL_DOCUMENT_ROOT.'/core/lib/report.lib.php';
35 require_once DOL_DOCUMENT_ROOT.'/core/lib/tax.lib.php';
36 require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
37 require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountancycategory.class.php';
38 
39 $langs->loadLangs(array('compta','bills','donation','salaries','accountancy'));
40 
41 $date_startmonth=GETPOST('date_startmonth','int');
42 $date_startday=GETPOST('date_startday','int');
43 $date_startyear=GETPOST('date_startyear','int');
44 $date_endmonth=GETPOST('date_endmonth','int');
45 $date_endday=GETPOST('date_endday','int');
46 $date_endyear=GETPOST('date_endyear','int');
47 $showaccountdetail = GETPOST('showaccountdetail','aZ09')?GETPOST('showaccountdetail','aZ09'):'no';
48 
49 // Security check
50 $socid = GETPOST('socid','int');
51 if ($user->societe_id > 0) $socid = $user->societe_id;
52 if (! empty($conf->comptabilite->enabled)) $result=restrictedArea($user,'compta','','','resultat');
53 if (! empty($conf->accounting->enabled)) $result=restrictedArea($user,'accounting','','','comptarapport');
54 
55 $limit = GETPOST('limit','int')?GETPOST('limit','int'):$conf->liste_limit;
56 $sortfield = GETPOST("sortfield",'alpha');
57 $sortorder = GETPOST("sortorder",'alpha');
58 $page = GETPOST("page",'int');
59 if (empty($page) || $page == -1) { $page = 0; } // If $page is not defined, or '' or -1
60 $offset = $limit * $page;
61 $pageprev = $page - 1;
62 $pagenext = $page + 1;
63 //if (! $sortfield) $sortfield='s.nom, s.rowid';
64 if (! $sortorder) $sortorder='ASC';
65 
66 // Date range
67 $year=GETPOST('year','int');
68 if (empty($year))
69 {
70  $year_current = strftime("%Y",dol_now());
71  $month_current = strftime("%m",dol_now());
72  $year_start = $year_current;
73 } else {
74  $year_current = $year;
75  $month_current = strftime("%m",dol_now());
76  $year_start = $year;
77 }
78 $date_start=dol_mktime(0, 0, 0, $date_startmonth, $date_startday, $date_startyear);
79 $date_end=dol_mktime(23, 59, 59, $date_endmonth, $date_endday, $date_endyear);
80 
81 // We define date_start and date_end
82 if (empty($date_start) || empty($date_end)) // We define date_start and date_end
83 {
84  $q=GETPOST("q")?GETPOST("q"):0;
85  if ($q==0)
86  {
87  // We define date_start and date_end
88  $year_end=$year_start;
89  $month_start=GETPOST("month")?GETPOST("month"):($conf->global->SOCIETE_FISCAL_MONTH_START?($conf->global->SOCIETE_FISCAL_MONTH_START):1);
90  if (! GETPOST('month'))
91  {
92  if (! GETPOST("year") && $month_start > $month_current)
93  {
94  $year_start--;
95  $year_end--;
96  }
97  $month_end=$month_start-1;
98  if ($month_end < 1) $month_end=12;
99  else $year_end++;
100  }
101  else $month_end=$month_start;
102  $date_start=dol_get_first_day($year_start,$month_start,false); $date_end=dol_get_last_day($year_end,$month_end,false);
103  }
104  if ($q==1) { $date_start=dol_get_first_day($year_start,1,false); $date_end=dol_get_last_day($year_start,3,false); }
105  if ($q==2) { $date_start=dol_get_first_day($year_start,4,false); $date_end=dol_get_last_day($year_start,6,false); }
106  if ($q==3) { $date_start=dol_get_first_day($year_start,7,false); $date_end=dol_get_last_day($year_start,9,false); }
107  if ($q==4) { $date_start=dol_get_first_day($year_start,10,false); $date_end=dol_get_last_day($year_start,12,false); }
108 }
109 
110 // $date_start and $date_end are defined. We force $start_year and $nbofyear
111 $tmps=dol_getdate($date_start);
112 $start_year = $tmps['year'];
113 $tmpe=dol_getdate($date_end);
114 $year_end = $tmpe['year'];
115 $nbofyear = ($year_end - $start_year) + 1;
116 //var_dump($start_year." ".$end_year." ".$nbofyear);
117 
118 // Define modecompta ('CREANCES-DETTES' or 'RECETTES-DEPENSES' or 'BOOKKEEPING')
119 $modecompta = $conf->global->ACCOUNTING_MODE;
120 if (! empty($conf->accounting->enabled)) $modecompta='BOOKKEEPING';
121 if (GETPOST("modecompta",'alpha')) $modecompta=GETPOST("modecompta",'alpha');
122 
123 $AccCat = new AccountancyCategory($db);
124 
125 
126 
127 /*
128  * View
129  */
130 
131 $months = array(
132  $langs->trans("JanuaryMin"),
133  $langs->trans("FebruaryMin"),
134  $langs->trans("MarchMin"),
135  $langs->trans("AprilMin"),
136  $langs->trans("MayMin"),
137  $langs->trans("JuneMin"),
138  $langs->trans("JulyMin"),
139  $langs->trans("AugustMin"),
140  $langs->trans("SeptemberMin"),
141  $langs->trans("OctoberMin"),
142  $langs->trans("NovemberMin"),
143  $langs->trans("DecemberMin"),
144 );
145 
146 llxHeader();
147 
148 $form=new Form($db);
149 
150 $periodlink='';
151 $exportlink='';
152 
153 $total_ht=0;
154 $total_ttc=0;
155 
156 // Affiche en-tete de rapport
157 if ($modecompta=="CREANCES-DETTES")
158 {
159  $name = $langs->trans("ReportInOut").', '.$langs->trans("ByPredefinedAccountGroups");
160  $calcmode=$langs->trans("CalcModeDebt");
161  $calcmode.='<br>('.$langs->trans("SeeReportInInputOutputMode",'<a href="'.$_SERVER["PHP_SELF"].'?date_startyear='.$tmps['year'].'&date_startmonth='.$tmps['mon'].'&date_startday='.$tmps['mday'].'&date_endyear='.$tmpe['year'].'&date_endmonth='.$tmpe['mon'].'&date_endday='.$tmpe['mday'].'&modecompta=RECETTES-DEPENSES">','</a>').')';
162  $calcmode.='<br>('.$langs->trans("SeeReportInBookkeepingMode",'<a href="'.$_SERVER["PHP_SELF"].'?date_startyear='.$tmps['year'].'&date_startmonth='.$tmps['mon'].'&date_startday='.$tmps['mday'].'&date_endyear='.$tmpe['year'].'&date_endmonth='.$tmpe['mon'].'&date_endday='.$tmpe['mday'].'&modecompta=BOOKKEEPING">','</a>').')';
163  $period=$form->select_date($date_start,'date_start',0,0,0,'',1,0,1).' - '.$form->select_date($date_end,'date_end',0,0,0,'',1,0,1);
164  $periodlink=($year_start?"<a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year']-1)."&modecompta=".$modecompta."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year']+1)."&modecompta=".$modecompta."'>".img_next()."</a>":"");
165  $description=$langs->trans("RulesResultDue");
166  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $description.= $langs->trans("DepositsAreNotIncluded");
167  else $description.= $langs->trans("DepositsAreIncluded");
168  $builddate=dol_now();
169  //$exportlink=$langs->trans("NotYetAvailable");
170 }
171 elseif ($modecompta=="RECETTES-DEPENSES")
172 {
173  $name = $langs->trans("ReportInOut").', '.$langs->trans("ByPredefinedAccountGroups");
174  $calcmode=$langs->trans("CalcModeEngagement");
175  $calcmode.='<br>('.$langs->trans("SeeReportInDueDebtMode",'<a href="'.$_SERVER["PHP_SELF"].'?date_startyear='.$tmps['year'].'&date_startmonth='.$tmps['mon'].'&date_startday='.$tmps['mday'].'&date_endyear='.$tmpe['year'].'&date_endmonth='.$tmpe['mon'].'&date_endday='.$tmpe['mday'].'&modecompta=CREANCES-DETTES">','</a>').')';
176  $calcmode.='<br>('.$langs->trans("SeeReportInBookkeepingMode",'<a href="'.$_SERVER["PHP_SELF"].'?date_startyear='.$tmps['year'].'&date_startmonth='.$tmps['mon'].'&date_startday='.$tmps['mday'].'&date_endyear='.$tmpe['year'].'&date_endmonth='.$tmpe['mon'].'&date_endday='.$tmpe['mday'].'&modecompta=BOOKKEEPING">','</a>').')';
177  //$period=$form->select_date($date_start,'date_start',0,0,0,'',1,0,1).' - '.$form->select_date($date_end,'date_end',1,1,0,'',1,0,1);
178  $period=$form->select_date($date_start,'date_start',0,0,0,'',1,0,1).' - '.$form->select_date($date_end,'date_end',0,0,0,'',1,0,1);
179  $periodlink=($year_start?"<a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year']-1)."&modecompta=".$modecompta."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year']+1)."&modecompta=".$modecompta."'>".img_next()."</a>":"");
180  $description=$langs->trans("RulesResultInOut");
181  $builddate=dol_now();
182  //$exportlink=$langs->trans("NotYetAvailable");
183 }
184 elseif ($modecompta=="BOOKKEEPING")
185 {
186  $name = $langs->trans("ReportInOut").', '.$langs->trans("ByPredefinedAccountGroups");
187  $calcmode=$langs->trans("CalcModeBookkeeping");
188  $calcmode.='<br>('.$langs->trans("SeeReportInInputOutputMode",'<a href="'.$_SERVER["PHP_SELF"].'?date_startyear='.$tmps['year'].'&date_startmonth='.$tmps['mon'].'&date_startday='.$tmps['mday'].'&date_endyear='.$tmpe['year'].'&date_endmonth='.$tmpe['mon'].'&date_endday='.$tmpe['mday'].'&modecompta=RECETTES-DEPENSES">','</a>').')';
189  $calcmode.='<br>('.$langs->trans("SeeReportInDueDebtMode",'<a href="'.$_SERVER["PHP_SELF"].'?date_startyear='.$tmps['year'].'&date_startmonth='.$tmps['mon'].'&date_startday='.$tmps['mday'].'&date_endyear='.$tmpe['year'].'&date_endmonth='.$tmpe['mon'].'&date_endday='.$tmpe['mday'].'&modecompta=CREANCES-DETTES">','</a>').')';
190  //$period=$form->select_date($date_start,'date_start',0,0,0,'',1,0,1).' - '.$form->select_date($date_end,'date_end',1,1,0,'',1,0,1);
191  $period=$form->select_date($date_start,'date_start',0,0,0,'',1,0,1).' - '.$form->select_date($date_end,'date_end',0,0,0,'',1,0,1);
192  $arraylist=array('no'=>$langs->trans("No"), 'yes'=>$langs->trans("AccountWithNonZeroValues"), 'all'=>$langs->trans("All"));
193  $period.=' &nbsp; &nbsp; '.$langs->trans("DetailByAccount").' '. $form->selectarray('showaccountdetail', $arraylist, $showaccountdetail, 0);
194  $periodlink=($year_start?"<a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year']-1)."&modecompta=".$modecompta."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($tmps['year']+1)."&modecompta=".$modecompta."'>".img_next()."</a>":"");
195  $description=$langs->trans("RulesResultBookkeepingPredefined");
196  $description.=' ('.$langs->trans("SeePageForSetup", DOL_URL_ROOT.'/accountancy/admin/account.php?mainmenu=accountancy&leftmenu=accountancy_admin', $langs->transnoentitiesnoconv("Accountancy").' / '.$langs->transnoentitiesnoconv("Setup").' / '.$langs->transnoentitiesnoconv("Chartofaccounts")).')';
197  $builddate=dol_now();
198  //$exportlink=$langs->trans("NotYetAvailable");
199 }
200 
201 $hselected = 'report';
202 
203 report_header($name,'',$period,$periodlink,$description,$builddate,$exportlink,array('modecompta'=>$modecompta),$calcmode);
204 
205 if (! empty($conf->accounting->enabled) && $modecompta != 'BOOKKEEPING')
206 {
207  print info_admin($langs->trans("WarningReportNotReliable"), 0, 0, 1);
208 }
209 
210 // Show report array
211 $param='&modecompta='.$modecompta;
212 if ($date_startday) $param.='&date_startday='.$date_startday;
213 if ($date_startmonth) $param.='&date_startmonth='.$date_startmonth;
214 if ($date_startyear) $param.='&date_startyear='.$date_startyear;
215 if ($date_endday) $param.='&date_endday='.$date_endday;
216 if ($date_endmonth) $param.='&date_endmonth='.$date_endmonth;
217 if ($date_endyear) $param.='&date_endyear='.$date_startyear;
218 
219 print '<table class="noborder" width="100%">';
220 print '<tr class="liste_titre">';
221 print_liste_field_titre("PredefinedGroups", $_SERVER["PHP_SELF"], 's.nom, s.rowid','',$param,'',$sortfield,$sortorder,'width200 ');
223 if ($modecompta == 'BOOKKEEPING')
224 {
225  print_liste_field_titre("Amount", $_SERVER["PHP_SELF"],'amount','',$param,'align="right"',$sortfield,$sortorder);
226 }
227 else
228 {
229  if ($modecompta == 'CREANCES-DETTES')
230  {
231  print_liste_field_titre("AmountHT", $_SERVER["PHP_SELF"],'amount_ht','',$param,'align="right"',$sortfield,$sortorder);
232  }
233  print_liste_field_titre("AmountTTC", $_SERVER["PHP_SELF"],'amount_ttc','',$param,'align="right"',$sortfield,$sortorder);
234 }
235 print "</tr>\n";
236 
237 
238 
239 if ($modecompta == 'BOOKKEEPING')
240 {
241  $predefinedgroupwhere = "(";
242  //$predefinedgroupwhere.= " (pcg_type = 'EXPENSE' and pcg_subtype in ('PRODUCT','SERVICE'))";
243  $predefinedgroupwhere.= " (pcg_type = 'EXPENSE')";
244  $predefinedgroupwhere.= " OR ";
245  //$predefinedgroupwhere.= " (pcg_type = 'INCOME' and pcg_subtype in ('PRODUCT','SERVICE'))";
246  $predefinedgroupwhere.= " (pcg_type = 'INCOME')";
247  $predefinedgroupwhere.= ")";
248 
249  $charofaccountstring = $conf->global->CHARTOFACCOUNTS;
250  $charofaccountstring=dol_getIdFromCode($db, $conf->global->CHARTOFACCOUNTS, 'accounting_system', 'rowid', 'pcg_version');
251 
252  $sql = "SELECT f.thirdparty_code as name, -1 as socid, aa.pcg_type, aa.pcg_subtype, sum(f.credit - f.debit) as amount";
253  $sql.= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as f";
254  $sql.= ", ".MAIN_DB_PREFIX."accounting_account as aa";
255  $sql.= " WHERE f.numero_compte = aa.account_number";
256  //$sql.= " AND fk_statut in (1,2)";
257  $sql.= " AND ".$predefinedgroupwhere;
258  $sql.= " AND aa.fk_pcg_version = '".$charofaccountstring."'";
259  if (! empty($date_start) && ! empty($date_end))
260  $sql.= " AND f.doc_date >= '".$db->idate($date_start)."' AND f.doc_date <= '".$db->idate($date_end)."'";
261  $sql.= " GROUP BY pcg_type, pcg_subtype, name, socid";
262  $sql.= $db->order($sortfield, $sortorder);
263 
264  $oldpcgtype = '';
265  $oldpcgsubtype = '';
266 
267  dol_syslog("get bookkeeping entries", LOG_DEBUG);
268  $result = $db->query($sql);
269  if ($result) {
270  $num = $db->num_rows($result);
271  $i = 0;
272  if ($num > 0)
273  {
274  while ($i < $num)
275  {
276  $objp = $db->fetch_object($result);
277 
278  if ($objp->pcg_type != $oldpcgtype)
279  {
280  print '<tr><td colspan="4">'.$objp->pcg_type.'</td></tr>';
281  $oldpcgtype = $objp->pcg_type;
282  }
283 
284  print '<tr class="oddeven">';
285  print '<td>&nbsp;</td>';
286  print '<td>'.$objp->pcg_type.($objp->pcg_subtype != 'XXXXXX'?' - '.$objp->pcg_subtype:'').($objp->name?' ('.$objp->name.')':'')."</td>\n";
287  print '<td align="right">'.price($objp->amount)."</td>\n";
288  print "</tr>\n";
289 
290  $total_ht += (isset($objp->amount)?$objp->amount:0);
291  $total_ttc += (isset($objp->amount)?$objp->amount:0);
292 
293  // Loop on detail of all accounts
294  // This make 14 calls for each detail of account (NP, N and month m)
295  if ($showaccountdetail != 'no')
296  {
297  $tmppredefinedgroupwhere="pcg_type = '".$db->escape($objp->pcg_type)."' AND pcg_subtype = '".$db->escape($objp->pcg_subtype)."'";
298  $tmppredefinedgroupwhere.= " AND fk_pcg_version = '".$charofaccountstring."'";
299  //$tmppredefinedgroupwhere.= " AND thirdparty_code = '".$db->escape($objp->name)."'";
300 
301  // Get cpts of category/group
302  $cpts = $AccCat->getCptsCat(0, $tmppredefinedgroupwhere);
303 
304  foreach($cpts as $i => $cpt)
305  {
306  $return = $AccCat->getResult($cpt['account_number'], 0, $date_start, $date_end, $cpt['dc']);
307  if ($return < 0) {
308  setEventMessages(null, $AccCat->errors, 'errors');
309  $resultN=0;
310  } else {
311  $resultN=$AccCat->sdc;
312  }
313 
314 
315  if ($showaccountdetail == 'all' || $resultN > 0)
316  {
317  print '<tr>';
318  print '<td></td>';
319  print '<td class="tdoverflowmax200"> &nbsp; &nbsp; ' . length_accountg($cpt['account_number']) . ' - ' . $cpt['account_label'] . '</td>';
320  print '<td align="right">' . price($resultN) . '</td>';
321  print "</tr>\n";
322  }
323  }
324  }
325 
326  $i++;
327  }
328  }
329  else
330  {
331  print '<tr><td colspan="4" class="opacitymedium">'.$langs->trans("NoRecordFound").'</td></tr>';
332  }
333  }
334  else dol_print_error($db);
335 }
336 else
337 {
338  /*
339  * Factures clients
340  */
341  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("CustomersInvoices").'</td></tr>';
342 
343  if ($modecompta == 'CREANCES-DETTES')
344  {
345  $sql = "SELECT s.nom as name, s.rowid as socid, sum(f.total) as amount_ht, sum(f.total_ttc) as amount_ttc";
346  $sql.= " FROM ".MAIN_DB_PREFIX."societe as s";
347  $sql.= ", ".MAIN_DB_PREFIX."facture as f";
348  $sql.= " WHERE f.fk_soc = s.rowid";
349  $sql.= " AND f.fk_statut IN (1,2)";
350  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS))
351  $sql.= " AND f.type IN (0,1,2,5)";
352  else
353  $sql.= " AND f.type IN (0,1,2,3,5)";
354  if (! empty($date_start) && ! empty($date_end))
355  $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
356  }
357  elseif ($modecompta == 'RECETTES-DEPENSES')
358  {
359  /*
360  * Liste des paiements (les anciens paiements ne sont pas vus par cette requete car, sur les
361  * vieilles versions, ils n'etaient pas lies via paiement_facture. On les ajoute plus loin)
362  */
363  $sql = "SELECT s.nom as name, s.rowid as socid, sum(pf.amount) as amount_ttc";
364  $sql.= " FROM ".MAIN_DB_PREFIX."societe as s";
365  $sql.= ", ".MAIN_DB_PREFIX."facture as f";
366  $sql.= ", ".MAIN_DB_PREFIX."paiement_facture as pf";
367  $sql.= ", ".MAIN_DB_PREFIX."paiement as p";
368  $sql.= " WHERE p.rowid = pf.fk_paiement";
369  $sql.= " AND pf.fk_facture = f.rowid";
370  $sql.= " AND f.fk_soc = s.rowid";
371  if (! empty($date_start) && ! empty($date_end))
372  $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
373  }
374  $sql.= " AND f.entity = ".$conf->entity;
375  if ($socid) $sql.= " AND f.fk_soc = ".$socid;
376  $sql.= " GROUP BY name, socid";
377  $sql.= $db->order($sortfield, $sortorder);
378 
379  dol_syslog("get customer invoices", LOG_DEBUG);
380  $result = $db->query($sql);
381  if ($result) {
382  $num = $db->num_rows($result);
383  $i = 0;
384  while ($i < $num)
385  {
386  $objp = $db->fetch_object($result);
387 
388  print '<tr class="oddeven"><td>&nbsp;</td>';
389  print "<td>".$langs->trans("Bills").' <a href="'.DOL_URL_ROOT.'/compta/facture/list.php?socid='.$objp->socid.'">'.$objp->name."</td>\n";
390 
391  if ($modecompta == 'CREANCES-DETTES')
392  print "<td align=\"right\">".price($objp->amount_ht)."</td>\n";
393  print "<td align=\"right\">".price($objp->amount_ttc)."</td>\n";
394 
395  $total_ht += (isset($objp->amount_ht)?$objp->amount_ht:0);
396  $total_ttc += $objp->amount_ttc;
397  print "</tr>\n";
398  $i++;
399  }
400  $db->free($result);
401  } else {
402  dol_print_error($db);
403  }
404 
405  // On ajoute les paiements clients anciennes version, non lie par paiement_facture
406  if ($modecompta == 'RECETTES-DEPENSES')
407  {
408  $sql = "SELECT 'Autres' as name, '0' as idp, sum(p.amount) as amount_ttc";
409  $sql.= " FROM ".MAIN_DB_PREFIX."bank as b";
410  $sql.= ", ".MAIN_DB_PREFIX."bank_account as ba";
411  $sql.= ", ".MAIN_DB_PREFIX."paiement as p";
412  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."paiement_facture as pf ON p.rowid = pf.fk_paiement";
413  $sql.= " WHERE pf.rowid IS NULL";
414  $sql.= " AND p.fk_bank = b.rowid";
415  $sql.= " AND b.fk_account = ba.rowid";
416  $sql.= " AND ba.entity IN (".getEntity('bank_account').")";
417  if (! empty($date_start) && ! empty($date_end))
418  $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
419  $sql.= " GROUP BY name, idp";
420  $sql.= " ORDER BY name";
421 
422  dol_syslog("get old customer payments not linked to invoices", LOG_DEBUG);
423  $result = $db->query($sql);
424  if ($result) {
425  $num = $db->num_rows($result);
426  $i = 0;
427  if ($num) {
428  while ($i < $num)
429  {
430  $objp = $db->fetch_object($result);
431 
432 
433  print '<tr class="oddeven"><td>&nbsp;</td>';
434  print "<td>".$langs->trans("Bills")." ".$langs->trans("Other")." (".$langs->trans("PaymentsNotLinkedToInvoice").")\n";
435 
436  if ($modecompta == 'CREANCES-DETTES')
437  print "<td align=\"right\">".price($objp->amount_ht)."</td>\n";
438  print "<td align=\"right\">".price($objp->amount_ttc)."</td>\n";
439 
440  $total_ht += (isset($objp->amount_ht)?$objp->amount_ht:0);
441  $total_ttc += $objp->amount_ttc;
442 
443  print "</tr>\n";
444  $i++;
445  }
446  }
447  $db->free($result);
448  } else {
449  dol_print_error($db);
450  }
451  }
452 
453  if ($total_ttc == 0)
454  {
455  print '<tr class="oddeven"><td>&nbsp;</td>';
456  print '<td colspan="3" class="opacitymedium">'.$langs->trans("None").'</td>';
457  print '</tr>';
458  }
459 
460  print '<tr class="liste_total">';
461  if ($modecompta == 'CREANCES-DETTES')
462  print '<td colspan="3" align="right">'.price($total_ht).'</td>';
463  print '<td colspan="3" align="right">'.price($total_ttc).'</td>';
464  print '</tr>';
465 
466 
467  /*
468  * Suppliers invoices
469  */
470  if ($modecompta == 'CREANCES-DETTES')
471  {
472  $sql = "SELECT s.nom as name, s.rowid as socid, sum(f.total_ht) as amount_ht, sum(f.total_ttc) as amount_ttc";
473  $sql.= " FROM ".MAIN_DB_PREFIX."societe as s";
474  $sql.= ", ".MAIN_DB_PREFIX."facture_fourn as f";
475  $sql.= " WHERE f.fk_soc = s.rowid";
476  $sql.= " AND f.fk_statut IN (1,2)";
477  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS))
478  $sql.= " AND f.type IN (0,1,2)";
479  else
480  $sql.= " AND f.type IN (0,1,2,3)";
481  if (! empty($date_start) && ! empty($date_end))
482  $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
483  }
484  elseif ($modecompta == 'RECETTES-DEPENSES')
485  {
486  $sql = "SELECT s.nom as name, s.rowid as socid, sum(pf.amount) as amount_ttc";
487  $sql.= " FROM ".MAIN_DB_PREFIX."paiementfourn as p";
488  $sql.= ", ".MAIN_DB_PREFIX."paiementfourn_facturefourn as pf";
489  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."facture_fourn as f";
490  $sql.= " ON pf.fk_facturefourn = f.rowid";
491  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s";
492  $sql.= " ON f.fk_soc = s.rowid";
493  $sql.= " WHERE p.rowid = pf.fk_paiementfourn ";
494  if (! empty($date_start) && ! empty($date_end))
495  $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
496  }
497 
498  $sql.= " AND f.entity = ".$conf->entity;
499  if ($socid) $sql.= " AND f.fk_soc = ".$socid;
500  $sql .= " GROUP BY name, socid";
501  $sql.= $db->order($sortfield, $sortorder);
502 
503  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("SuppliersInvoices").'</td></tr>';
504 
505  $subtotal_ht = 0;
506  $subtotal_ttc = 0;
507  dol_syslog("get suppliers invoices", LOG_DEBUG);
508  $result = $db->query($sql);
509  if ($result) {
510  $num = $db->num_rows($result);
511  $i = 0;
512  if ($num > 0)
513  {
514  while ($i < $num)
515  {
516  $objp = $db->fetch_object($result);
517 
518  print '<tr class="oddeven"><td>&nbsp;</td>';
519  print "<td>".$langs->trans("Bills")." <a href=\"".DOL_URL_ROOT."/fourn/facture/list.php?socid=".$objp->socid."\">".$objp->name."</a></td>\n";
520 
521  if ($modecompta == 'CREANCES-DETTES')
522  print "<td align=\"right\">".price(-$objp->amount_ht)."</td>\n";
523  print "<td align=\"right\">".price(-$objp->amount_ttc)."</td>\n";
524 
525  $total_ht -= (isset($objp->amount_ht)?$objp->amount_ht:0);
526  $total_ttc -= $objp->amount_ttc;
527  $subtotal_ht += (isset($objp->amount_ht)?$objp->amount_ht:0);
528  $subtotal_ttc += $objp->amount_ttc;
529 
530  print "</tr>\n";
531  $i++;
532  }
533  }
534  else
535  {
536  print '<tr class="oddeven"><td>&nbsp;</td>';
537  print '<td colspan="3" class="opacitymedium">'.$langs->trans("None").'</td>';
538  print '</tr>';
539  }
540 
541  $db->free($result);
542  } else {
543  dol_print_error($db);
544  }
545  print '<tr class="liste_total">';
546  if ($modecompta == 'CREANCES-DETTES')
547  print '<td colspan="3" align="right">'.price(-$subtotal_ht).'</td>';
548  print '<td colspan="3" align="right">'.price(-$subtotal_ttc).'</td>';
549  print '</tr>';
550 
551 
552 
553  /*
554  * Charges sociales non deductibles
555  */
556 
557  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("SocialContributionsNondeductibles").'</td></tr>';
558 
559  if ($modecompta == 'CREANCES-DETTES')
560  {
561  $sql = "SELECT c.id, c.libelle as label, sum(cs.amount) as amount";
562  $sql.= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
563  $sql.= ", ".MAIN_DB_PREFIX."chargesociales as cs";
564  $sql.= " WHERE cs.fk_type = c.id";
565  $sql.= " AND c.deductible = 0";
566  if (! empty($date_start) && ! empty($date_end))
567  $sql.= " AND cs.date_ech >= '".$db->idate($date_start)."' AND cs.date_ech <= '".$db->idate($date_end)."'";
568  }
569  elseif ($modecompta == 'RECETTES-DEPENSES')
570  {
571  $sql = "SELECT c.id, c.libelle as label, sum(p.amount) as amount";
572  $sql.= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
573  $sql.= ", ".MAIN_DB_PREFIX."chargesociales as cs";
574  $sql.= ", ".MAIN_DB_PREFIX."paiementcharge as p";
575  $sql.= " WHERE p.fk_charge = cs.rowid";
576  $sql.= " AND cs.fk_type = c.id";
577  $sql.= " AND c.deductible = 0";
578  if (! empty($date_start) && ! empty($date_end))
579  $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
580  }
581  $sql.= " AND cs.entity = ".$conf->entity;
582  $sql.= " GROUP BY c.libelle, c.id";
583  $newsortfield = $sortfield;
584  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'c.libelle, c.id';
585  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
586  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
587 
588  $sql.= $db->order($newsortfield, $sortorder);
589 
590  dol_syslog("get social contributions deductible=0", LOG_DEBUG);
591  $result=$db->query($sql);
592  $subtotal_ht = 0;
593  $subtotal_ttc = 0;
594  if ($result) {
595  $num = $db->num_rows($result);
596  $i = 0;
597  if ($num) {
598  while ($i < $num) {
599  $obj = $db->fetch_object($result);
600 
601  $total_ht -= $obj->amount;
602  $total_ttc -= $obj->amount;
603  $subtotal_ht += $obj->amount;
604  $subtotal_ttc += $obj->amount;
605 
606  print '<tr class="oddeven"><td>&nbsp;</td>';
607  print '<td>'.$obj->label.'</td>';
608  if ($modecompta == 'CREANCES-DETTES') print '<td align="right">'.price(-$obj->amount).'</td>';
609  print '<td align="right">'.price(-$obj->amount).'</td>';
610  print '</tr>';
611  $i++;
612  }
613  }
614  else {
615  print '<tr class="oddeven"><td>&nbsp;</td>';
616  print '<td colspan="3" class="opacitymedium">'.$langs->trans("None").'</td>';
617  print '</tr>';
618  }
619  } else {
620  dol_print_error($db);
621  }
622  print '<tr class="liste_total">';
623  if ($modecompta == 'CREANCES-DETTES')
624  print '<td colspan="3" align="right">'.price(-$subtotal_ht).'</td>';
625  print '<td colspan="3" align="right">'.price(-$subtotal_ttc).'</td>';
626  print '</tr>';
627 
628 
629  /*
630  * Charges sociales deductibles
631  */
632 
633  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("SocialContributionsDeductibles").'</td></tr>';
634 
635  if ($modecompta == 'CREANCES-DETTES')
636  {
637  $sql = "SELECT c.id, c.libelle as label, sum(cs.amount) as amount";
638  $sql.= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
639  $sql.= ", ".MAIN_DB_PREFIX."chargesociales as cs";
640  $sql.= " WHERE cs.fk_type = c.id";
641  $sql.= " AND c.deductible = 1";
642  if (! empty($date_start) && ! empty($date_end))
643  $sql.= " AND cs.date_ech >= '".$db->idate($date_start)."' AND cs.date_ech <= '".$db->idate($date_end)."'";
644  $sql.= " AND cs.entity = ".$conf->entity;
645  }
646  elseif ($modecompta == 'RECETTES-DEPENSES')
647  {
648  $sql = "SELECT c.id, c.libelle as label, sum(p.amount) as amount";
649  $sql.= " FROM ".MAIN_DB_PREFIX."c_chargesociales as c";
650  $sql.= ", ".MAIN_DB_PREFIX."chargesociales as cs";
651  $sql.= ", ".MAIN_DB_PREFIX."paiementcharge as p";
652  $sql.= " WHERE p.fk_charge = cs.rowid";
653  $sql.= " AND cs.fk_type = c.id";
654  $sql.= " AND c.deductible = 1";
655  if (! empty($date_start) && ! empty($date_end))
656  $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
657  $sql.= " AND cs.entity = ".$conf->entity;
658  }
659  $sql.= " GROUP BY c.libelle, c.id";
660  $newsortfield = $sortfield;
661  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'c.libelle, c.id';
662  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
663  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
664  $sql.= $db->order($newsortfield, $sortorder);
665 
666  dol_syslog("get social contributions deductible=1", LOG_DEBUG);
667  $result=$db->query($sql);
668  $subtotal_ht = 0;
669  $subtotal_ttc = 0;
670  if ($result) {
671  $num = $db->num_rows($result);
672  $i = 0;
673  if ($num) {
674  while ($i < $num) {
675  $obj = $db->fetch_object($result);
676 
677  $total_ht -= $obj->amount;
678  $total_ttc -= $obj->amount;
679  $subtotal_ht += $obj->amount;
680  $subtotal_ttc += $obj->amount;
681 
682  print '<tr class="oddeven"><td>&nbsp;</td>';
683  print '<td>'.$obj->label.'</td>';
684  if ($modecompta == 'CREANCES-DETTES')
685  print '<td align="right">'.price(-$obj->amount).'</td>';
686  print '<td align="right">'.price(-$obj->amount).'</td>';
687  print '</tr>';
688  $i++;
689  }
690  }
691  else {
692  print '<tr class="oddeven"><td>&nbsp;</td>';
693  print '<td colspan="3" class="opacitymedium">'.$langs->trans("None").'</td>';
694  print '</tr>';
695  }
696  } else {
697  dol_print_error($db);
698  }
699  print '<tr class="liste_total">';
700  if ($modecompta == 'CREANCES-DETTES')
701  print '<td colspan="3" align="right">'.price(-$subtotal_ht).'</td>';
702  print '<td colspan="3" align="right">'.price(-$subtotal_ttc).'</td>';
703  print '</tr>';
704 
705  if ($mysoc->tva_assuj == 'franchise') // Non assujetti
706  {
707  // Total
708  print '<tr>';
709  print '<td colspan="4">&nbsp;</td>';
710  print '</tr>';
711 
712  print '<tr class="liste_total"><td align="left" colspan="2">'.$langs->trans("Profit").'</td>';
713  if ($modecompta == 'CREANCES-DETTES')
714  print '<td class="border" align="right">'.price($total_ht).'</td>';
715  print '<td align="right">'.price($total_ttc).'</td>';
716  print '</tr>';
717 
718  print '<tr>';
719  print '<td colspan="4">&nbsp;</td>';
720  print '</tr>';
721  }
722 
723 
724  /*
725  * Salaries
726  */
727 
728  if (! empty($conf->salaries->enabled))
729  {
730  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("Salaries").'</td></tr>';
731 
732  if ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES')
733  {
734  if ($modecompta == 'CREANCES-DETTES') {
735  $column = 'p.datev';
736  } else {
737  $column = 'p.datep';
738  }
739 
740  $sql = "SELECT u.rowid, u.firstname, u.lastname, p.fk_user, p.label as label, date_format($column,'%Y-%m') as dm, sum(p.amount) as amount";
741  $sql.= " FROM ".MAIN_DB_PREFIX."payment_salary as p";
742  $sql.= " INNER JOIN ".MAIN_DB_PREFIX."user as u ON u.rowid=p.fk_user";
743  $sql.= " WHERE p.entity = ".$conf->entity;
744  if (! empty($date_start) && ! empty($date_end))
745  $sql.= " AND $column >= '".$db->idate($date_start)."' AND $column <= '".$db->idate($date_end)."'";
746 
747  $sql.= " GROUP BY u.rowid, u.firstname, u.lastname, p.fk_user, p.label, dm";
748  $newsortfield = $sortfield;
749  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'u.firstname, u.lastname';
750  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
751  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
752  $sql.= $db->order($newsortfield, $sortorder);
753  }
754 
755  dol_syslog("get payment salaries");
756  $result=$db->query($sql);
757  $subtotal_ht = 0;
758  $subtotal_ttc = 0;
759  if ($result)
760  {
761  $num = $db->num_rows($result);
762  $i = 0;
763  if ($num)
764  {
765  while ($i < $num)
766  {
767  $obj = $db->fetch_object($result);
768 
769  $total_ht -= $obj->amount;
770  $total_ttc -= $obj->amount;
771  $subtotal_ht += $obj->amount;
772  $subtotal_ttc += $obj->amount;
773 
774  print '<tr class="oddeven"><td>&nbsp;</td>';
775 
776  print "<td>".$langs->trans("Salary")." <a href=\"".DOL_URL_ROOT."/compta/salaries/index.php?filtre=s.fk_user=".$obj->fk_user."\">".$obj->firstname." ".$obj->lastname."</a></td>\n";
777 
778  if ($modecompta == 'CREANCES-DETTES') print '<td align="right">'.price(-$obj->amount).'</td>';
779  print '<td align="right">'.price(-$obj->amount).'</td>';
780  print '</tr>';
781  $i++;
782  }
783  }
784  else
785  {
786  print '<tr class="oddeven"><td>&nbsp;</td>';
787  print '<td colspan="3" class="opacitymedium">'.$langs->trans("None").'</td>';
788  print '</tr>';
789  }
790  }
791  else
792  {
793  dol_print_error($db);
794  }
795  print '<tr class="liste_total">';
796  if ($modecompta == 'CREANCES-DETTES')
797  print '<td colspan="3" align="right">'.price(-$subtotal_ht).'</td>';
798  print '<td colspan="3" align="right">'.price(-$subtotal_ttc).'</td>';
799  print '</tr>';
800  }
801 
802 
803  /*
804  * Expense
805  */
806 
807  if (! empty($conf->expensereport->enabled))
808  {
809  if ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES')
810  {
811  $langs->load('trips');
812  if ($modecompta == 'CREANCES-DETTES') {
813  $sql = "SELECT p.rowid, p.ref, u.rowid as userid, u.firstname, u.lastname, date_format(date_valid,'%Y-%m') as dm, sum(p.total_ht) as amount_ht,sum(p.total_ttc) as amount_ttc";
814  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as p";
815  $sql.= " INNER JOIN ".MAIN_DB_PREFIX."user as u ON u.rowid=p.fk_user_author";
816  $sql.= " WHERE p.entity = ".getEntity('expensereport');
817  $sql.= " AND p.fk_statut>=5";
818 
819  $column='p.date_valid';
820  } else {
821  $sql = "SELECT p.rowid, p.ref, u.rowid as userid, u.firstname, u.lastname, date_format(pe.datep,'%Y-%m') as dm, sum(p.total_ht) as amount_ht, sum(p.total_ttc) as amount_ttc";
822  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as p";
823  $sql.= " INNER JOIN ".MAIN_DB_PREFIX."user as u ON u.rowid=p.fk_user_author";
824  $sql.= " INNER JOIN ".MAIN_DB_PREFIX."payment_expensereport as pe ON pe.fk_expensereport = p.rowid";
825  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as c ON pe.fk_typepayment = c.id AND c.entity IN (".getEntity('c_paiement').")";
826  $sql.= " WHERE p.entity = ".getEntity('expensereport');
827  $sql.= " AND p.fk_statut>=5";
828 
829  $column='pe.datep';
830  }
831 
832  if (! empty($date_start) && ! empty($date_end))
833  {
834  $sql.= " AND $column >= '".$db->idate($date_start)."' AND $column <= '".$db->idate($date_end)."'";
835  }
836 
837  $sql.= " GROUP BY u.rowid, p.rowid, p.ref, u.firstname, u.lastname, dm";
838  $newsortfield = $sortfield;
839  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'p.ref';
840  $sql.= $db->order($newsortfield, $sortorder);
841  }
842 
843  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("ExpenseReport").'</td></tr>';
844 
845  dol_syslog("get expense report outcome");
846  $result=$db->query($sql);
847  $subtotal_ht = 0;
848  $subtotal_ttc = 0;
849  if ($result)
850  {
851  $num = $db->num_rows($result);
852  if ($num)
853  {
854  while ($obj = $db->fetch_object($result))
855  {
856  $total_ht -= $obj->amount_ht;
857  $total_ttc -= $obj->amount_ttc;
858  $subtotal_ht += $obj->amount_ht;
859  $subtotal_ttc += $obj->amount_ttc;
860 
861  print '<tr class="oddeven"><td>&nbsp;</td>';
862 
863  print "<td>".$langs->trans("ExpenseReport")." <a href=\"".DOL_URL_ROOT."/expensereport/list.php?search_user=".$obj->userid."\">".$obj->firstname." ".$obj->lastname."</a></td>\n";
864 
865  if ($modecompta == 'CREANCES-DETTES') print '<td align="right">'.price(-$obj->amount_ht).'</td>';
866  print '<td align="right">'.price(-$obj->amount_ttc).'</td>';
867  print '</tr>';
868  }
869  }
870  else
871  {
872  print '<tr class="oddeven"><td>&nbsp;</td>';
873  print '<td colspan="3" class="opacitymedium">'.$langs->trans("None").'</td>';
874  print '</tr>';
875  }
876  }
877  else
878  {
879  dol_print_error($db);
880  }
881  print '<tr class="liste_total">';
882  if ($modecompta == 'CREANCES-DETTES') print '<td colspan="3" align="right">'.price(-$subtotal_ht).'</td>';
883  print '<td colspan="3" align="right">'.price(-$subtotal_ttc).'</td>';
884  print '</tr>';
885  }
886 
887  /*
888  * Donations
889  */
890 
891  if (! empty($conf->don->enabled))
892  {
893  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("Donations").'</td></tr>';
894 
895  if ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES')
896  {
897  if ($modecompta == 'CREANCES-DETTES')
898  {
899  $sql = "SELECT p.societe as name, p.firstname, p.lastname, date_format(p.datedon,'%Y-%m') as dm, sum(p.amount) as amount";
900  $sql.= " FROM ".MAIN_DB_PREFIX."don as p";
901  $sql.= " WHERE p.entity = ".$conf->entity;
902  $sql.= " AND fk_statut in (1,2)";
903  }
904  else
905  {
906  $sql = "SELECT p.societe as nom, p.firstname, p.lastname, date_format(p.datedon,'%Y-%m') as dm, sum(p.amount) as amount";
907  $sql.= " FROM ".MAIN_DB_PREFIX."don as p";
908  $sql.= " INNER JOIN ".MAIN_DB_PREFIX."payment_donation as pe ON pe.fk_donation = p.rowid";
909  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as c ON pe.fk_typepayment = c.id AND c.entity IN (".getEntity('c_paiement').")";
910  $sql.= " WHERE p.entity IN (".getEntity('donation').")";
911  $sql.= " AND fk_statut >= 2";
912  }
913  if (! empty($date_start) && ! empty($date_end))
914  $sql.= " AND p.datedon >= '".$db->idate($date_start)."' AND p.datedon <= '".$db->idate($date_end)."'";
915  }
916  $sql.= " GROUP BY p.societe, p.firstname, p.lastname, dm";
917  $newsortfield = $sortfield;
918  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'p.societe, p.firstname, p.lastname, dm';
919  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
920  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
921  $sql.= $db->order($newsortfield, $sortorder);
922 
923  dol_syslog("get dunning");
924  $result=$db->query($sql);
925  $subtotal_ht = 0;
926  $subtotal_ttc = 0;
927  if ($result)
928  {
929  $num = $db->num_rows($result);
930  $i = 0;
931  if ($num)
932  {
933  while ($i < $num)
934  {
935  $obj = $db->fetch_object($result);
936 
937  $total_ht += $obj->amount;
938  $total_ttc += $obj->amount;
939  $subtotal_ht += $obj->amount;
940  $subtotal_ttc += $obj->amount;
941 
942  print '<tr class="oddeven"><td>&nbsp;</td>';
943 
944  print "<td>".$langs->trans("Donation")." <a href=\"".DOL_URL_ROOT."/don/list.php?search_company=".$obj->name."&search_name=".$obj->firstname." ".$obj->lastname."\">".$obj->name. " ".$obj->firstname." ".$obj->lastname."</a></td>\n";
945 
946  if ($modecompta == 'CREANCES-DETTES') print '<td align="right">'.price($obj->amount).'</td>';
947  print '<td align="right">'.price($obj->amount).'</td>';
948  print '</tr>';
949  $i++;
950  }
951  }
952  else
953  {
954  print '<tr class="oddeven"><td>&nbsp;</td>';
955  print '<td colspan="3" class="opacitymedium">'.$langs->trans("None").'</td>';
956  print '</tr>';
957  }
958  }
959  else
960  {
961  dol_print_error($db);
962  }
963  print '<tr class="liste_total">';
964  if ($modecompta == 'CREANCES-DETTES')
965  print '<td colspan="3" align="right">'.price($subtotal_ht).'</td>';
966  print '<td colspan="3" align="right">'.price($subtotal_ttc).'</td>';
967  print '</tr>';
968  }
969 
970 
971  /*
972  * VAT
973  */
974 
975  print '<tr class="trforbreak"><td colspan="4">'.$langs->trans("VAT").'</td></tr>';
976  $subtotal_ht = 0;
977  $subtotal_ttc = 0;
978 
979  if ($conf->tax->enabled && ($modecompta == 'CREANCES-DETTES' || $modecompta == 'RECETTES-DEPENSES'))
980  {
981  if ($modecompta == 'CREANCES-DETTES')
982  {
983  // TVA a payer
984  $amount=0;
985  $sql = "SELECT date_format(f.datef,'%Y-%m') as dm, sum(f.tva) as amount";
986  $sql.= " FROM ".MAIN_DB_PREFIX."facture as f";
987  $sql.= " WHERE f.fk_statut IN (1,2)";
988  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS))
989  $sql.= " AND f.type IN (0,1,2,5)";
990  else
991  $sql.= " AND f.type IN (0,1,2,3,5)";
992  if (! empty($date_start) && ! empty($date_end))
993  $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
994  $sql.= " AND f.entity = ".$conf->entity;
995  $sql.= " GROUP BY dm";
996  $newsortfield = $sortfield;
997  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'dm';
998  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
999  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
1000  $sql.= $db->order($newsortfield, $sortorder);
1001 
1002  dol_syslog("get vat to pay", LOG_DEBUG);
1003  $result=$db->query($sql);
1004  if ($result)
1005  {
1006  $num = $db->num_rows($result);
1007  $i = 0;
1008  if ($num)
1009  {
1010  while ($i < $num)
1011  {
1012  $obj = $db->fetch_object($result);
1013 
1014  $amount -= $obj->amount;
1015  //$total_ht -= $obj->amount;
1016  $total_ttc -= $obj->amount;
1017  //$subtotal_ht -= $obj->amount;
1018  $subtotal_ttc -= $obj->amount;
1019  $i++;
1020  }
1021  }
1022  } else {
1023  dol_print_error($db);
1024  }
1025  print '<tr class="oddeven"><td>&nbsp;</td>';
1026  print "<td>".$langs->trans("VATToPay")."</td>\n";
1027  print "<td align=\"right\">&nbsp;</td>\n";
1028  print "<td align=\"right\">".price($amount)."</td>\n";
1029  print "</tr>\n";
1030 
1031  // TVA a recuperer
1032  $amount=0;
1033  $sql = "SELECT date_format(f.datef,'%Y-%m') as dm, sum(f.total_tva) as amount";
1034  $sql.= " FROM ".MAIN_DB_PREFIX."facture_fourn as f";
1035  $sql.= " WHERE f.fk_statut IN (1,2)";
1036  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS))
1037  $sql.= " AND f.type IN (0,1,2)";
1038  else
1039  $sql.= " AND f.type IN (0,1,2,3)";
1040  if (! empty($date_start) && ! empty($date_end))
1041  $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
1042  $sql.= " AND f.entity = ".$conf->entity;
1043  $sql.= " GROUP BY dm";
1044  $newsortfield = $sortfield;
1045  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'dm';
1046  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
1047  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
1048  $sql.= $db->order($newsortfield, $sortorder);
1049 
1050  dol_syslog("get vat received back", LOG_DEBUG);
1051  $result=$db->query($sql);
1052  if ($result)
1053  {
1054  $num = $db->num_rows($result);
1055  $i = 0;
1056  if ($num)
1057  {
1058  while ($i < $num)
1059  {
1060  $obj = $db->fetch_object($result);
1061 
1062  $amount += $obj->amount;
1063  //$total_ht += $obj->amount;
1064  $total_ttc += $obj->amount;
1065  //$subtotal_ht += $obj->amount;
1066  $subtotal_ttc += $obj->amount;
1067 
1068  $i++;
1069  }
1070  }
1071  } else {
1072  dol_print_error($db);
1073  }
1074  print '<tr class="oddeven"><td>&nbsp;</td>';
1075  print "<td>".$langs->trans("VATToCollect")."</td>\n";
1076  print "<td align=\"right\">&nbsp;</td>\n";
1077  print "<td align=\"right\">".price($amount)."</td>\n";
1078  print "</tr>\n";
1079  }
1080  else
1081  {
1082  // VAT really already paid
1083  $amount=0;
1084  $sql = "SELECT date_format(t.datev,'%Y-%m') as dm, sum(t.amount) as amount";
1085  $sql.= " FROM ".MAIN_DB_PREFIX."tva as t";
1086  $sql.= " WHERE amount > 0";
1087  if (! empty($date_start) && ! empty($date_end))
1088  $sql.= " AND t.datev >= '".$db->idate($date_start)."' AND t.datev <= '".$db->idate($date_end)."'";
1089  $sql.= " AND t.entity = ".$conf->entity;
1090  $sql.= " GROUP BY dm";
1091  $newsortfield = $sortfield;
1092  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'dm';
1093  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
1094  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
1095  $sql.= $db->order($newsortfield, $sortorder);
1096 
1097  dol_syslog("get vat really paid", LOG_DEBUG);
1098  $result=$db->query($sql);
1099  if ($result) {
1100  $num = $db->num_rows($result);
1101  $i = 0;
1102  if ($num) {
1103  while ($i < $num) {
1104  $obj = $db->fetch_object($result);
1105 
1106  $amount -= $obj->amount;
1107  $total_ht -= $obj->amount;
1108  $total_ttc -= $obj->amount;
1109  $subtotal_ht -= $obj->amount;
1110  $subtotal_ttc -= $obj->amount;
1111 
1112  $i++;
1113  }
1114  }
1115  $db->free($result);
1116  } else {
1117  dol_print_error($db);
1118  }
1119  print '<tr class="oddeven"><td>&nbsp;</td>';
1120  print "<td>".$langs->trans("VATPaid")."</td>\n";
1121  if ($modecompta == 'CREANCES-DETTES')
1122  print "<td align=\"right\">".price($amount)."</td>\n";
1123  print "<td align=\"right\">".price($amount)."</td>\n";
1124  print "</tr>\n";
1125 
1126  // VAT really received
1127  $amount=0;
1128  $sql = "SELECT date_format(t.datev,'%Y-%m') as dm, sum(t.amount) as amount";
1129  $sql.= " FROM ".MAIN_DB_PREFIX."tva as t";
1130  $sql.= " WHERE amount < 0";
1131  if (! empty($date_start) && ! empty($date_end))
1132  $sql.= " AND t.datev >= '".$db->idate($date_start)."' AND t.datev <= '".$db->idate($date_end)."'";
1133  $sql.= " AND t.entity = ".$conf->entity;
1134  $sql.= " GROUP BY dm";
1135  $newsortfield = $sortfield;
1136  if ($newsortfield == 's.nom, s.rowid') $newsortfield = 'dm';
1137  if ($newsortfield == 'amount_ht') $newsortfield = 'amount';
1138  if ($newsortfield == 'amount_ttc') $newsortfield = 'amount';
1139  $sql.= $db->order($newsortfield, $sortorder);
1140 
1141  dol_syslog("get vat really received back", LOG_DEBUG);
1142  $result=$db->query($sql);
1143  if ($result) {
1144  $num = $db->num_rows($result);
1145  $i = 0;
1146  if ($num) {
1147  while ($i < $num) {
1148  $obj = $db->fetch_object($result);
1149 
1150  $amount += $obj->amount;
1151  $total_ht += $obj->amount;
1152  $total_ttc += $obj->amount;
1153  $subtotal_ht += $obj->amount;
1154  $subtotal_ttc += $obj->amount;
1155 
1156  $i++;
1157  }
1158  }
1159  $db->free($result);
1160  }
1161  else
1162  {
1163  dol_print_error($db);
1164  }
1165  print '<tr class="oddeven"><td>&nbsp;</td>';
1166  print "<td>".$langs->trans("VATCollected")."</td>\n";
1167  if ($modecompta == 'CREANCES-DETTES')
1168  print "<td align=\"right\">".price($amount)."</td>\n";
1169  print "<td align=\"right\">".price($amount)."</td>\n";
1170  print "</tr>\n";
1171  }
1172  }
1173 
1174  if ($mysoc->tva_assuj != 'franchise') // Assujetti
1175  {
1176  print '<tr class="liste_total">';
1177  if ($modecompta == 'CREANCES-DETTES')
1178  print '<td colspan="3" align="right">&nbsp;</td>';
1179  print '<td colspan="3" align="right">'.price(price2num($subtotal_ttc,'MT')).'</td>';
1180  print '</tr>';
1181  }
1182 }
1183 
1184 $action = "balanceclient";
1185 $object = array(&$total_ht, &$total_ttc);
1186 $parameters["mode"] = $modecompta;
1187 $parameters["date_start"] = $date_start;
1188 $parameters["date_end"] = $date_end;
1189 $parameters["bc"] = $bc;
1190 // Initialize technical object to manage hooks of expenses. Note that conf->hooks_modules contains array array
1191 $hookmanager->initHooks(array('externalbalance'));
1192 $reshook=$hookmanager->executeHooks('addBalanceLine',$parameters,$object,$action); // Note that $action and $object may have been modified by some hooks
1193 print $hookmanager->resPrint;
1194 
1195 if ($mysoc->tva_assuj != 'franchise') // Assujetti
1196 {
1197  // Total
1198  print '<tr>';
1199  print '<td colspan="4">&nbsp;</td>';
1200  print '</tr>';
1201 
1202  print '<tr class="liste_total"><td align="left" colspan="2">'.$langs->trans("Profit").'</td>';
1203  if ($modecompta == 'CREANCES-DETTES')
1204  print '<td class="liste_total" align="right">'.price(price2num($total_ht,'MT')).'</td>';
1205  print '<td class="liste_total" align="right">'.price(price2num($total_ttc,'MT')).'</td>';
1206  print '</tr>';
1207 }
1208 
1209 print "</table>";
1210 print '<br>';
1211 
1212 llxFooter();
1213 
1214 $db->close();
llxFooter()
Empty footer.
Definition: wrapper.php:58
setEventMessages($mesg, $mesgs, $style='mesgs')
Set event messages in dol_events session object.
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm=false, $check=1)
Return a timestamp date built from detailed informations (by default a local PHP server timestamp) Re...
img_previous($titlealt= 'default', $moreatt='')
Show previous logo.
dol_getIdFromCode($db, $key, $tablename, $fieldkey='code', $fieldid='id', $entityfilter=0)
Return an id or code from a code or id.
img_next($titlealt= 'default', $moreatt='')
Show next logo.
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:445
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
if(empty($reshook)) $form
View.
Definition: perms.php:103
Class to manage categories of an accounting account.
GETPOST($paramname, $check='none', $method=0, $filter=NULL, $options=NULL, $noreplace=0)
Return value of a param into GET or POST supervariable.
Class to manage generation of HTML components Only common components must be here.
info_admin($text, $infoonimgalt=0, $nodiv=0, $admin='1')
Show information for admin users or standard users.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
llxHeader()
Empty header.
Definition: wrapper.php:46
getEntity($element, $shared=1, $forceentity=null)
Get list of entity id to use.
dol_now($mode='gmt')
Return date for now.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages...
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:459
print
Draft customers invoices.
Definition: index.php:91
print_liste_field_titre($name, $file="", $field="", $begin="", $moreparam="", $moreattrib="", $sortfield="", $sortorder="", $prefix="", $tooltip="")
Show title line of an array.
dol_getdate($timestamp, $fast=false)
Return an array with locale date info.
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous) ...
report_header($reportname, $notused, $period, $periodlink, $description, $builddate, $exportlink='', $moreparam=array(), $calcmode='', $varlink='')
Show header of a report.
Definition: report.lib.php:41
price2num($amount, $rounding='', $alreadysqlnb=0)
Function that return a number with universal decimal format (decimal separator is '...
restrictedArea($user, $features, $objectid=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $objcanvas=null)
Check permissions of a user to show a page and an object.