dolibarr  9.0.0
index.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2013-2014 Olivier Geffroy <jeff@jeffinfo.com>
3  * Copyright (C) 2013-2014 Florian Henry <florian.henry@open-concept.pro>
4  * Copyright (C) 2013-2016 Alexandre Spangaro <aspangaro@zendsi.com>
5  * Copyright (C) 2014 Juanjo Menent <jmenent@2byte.es>
6  *
7  * This program is free software; you can redistribute it and/or modify
8  * it under the terms of the GNU General Public License as published by
9  * the Free Software Foundation; either version 3 of the License, or
10  * (at your option) any later version.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program. If not, see <http://www.gnu.org/licenses/>.
19  */
20 
27 require '../../main.inc.php';
28 require_once DOL_DOCUMENT_ROOT . '/core/lib/date.lib.php';
29 require_once DOL_DOCUMENT_ROOT . '/core/lib/accounting.lib.php';
30 require_once DOL_DOCUMENT_ROOT . '/expensereport/class/expensereport.class.php';
31 
32 // Load translation files required by the page
33 $langs->loadLangs(array("compta","bills","other","main","accountancy"));
34 
35 // Security check
36 if (empty($conf->accounting->enabled)) {
38 }
39 if ($user->societe_id > 0)
41 if (! $user->rights->accounting->bind->write)
43 
44 $month_start= ($conf->global->SOCIETE_FISCAL_MONTH_START?($conf->global->SOCIETE_FISCAL_MONTH_START):1);
45 if (GETPOST("year",'int')) $year_start = GETPOST("year",'int');
46 else
47 {
48  $year_start = dol_print_date(dol_now(), '%Y');
49  if (dol_print_date(dol_now(), '%m') < $month_start) $year_start--; // If current month is lower that starting fiscal month, we start last year
50 }
51 $year_end = $year_start + 1;
52 $month_end = $month_start - 1;
53 if ($month_end < 1)
54 {
55  $month_end = 12;
56  $year_end--;
57 }
58 $search_date_start = dol_mktime(0, 0, 0, $month_start, 1, $year_start);
59 $search_date_end = dol_get_last_day($year_end, $month_end);
60 $year_current = $year_start;
61 
62 // Validate History
63 $action = GETPOST('action','aZ09');
64 
65 
66 
67 /*
68  * Actions
69  */
70 
71 if ($action == 'clean' || $action == 'validatehistory')
72 {
73  // Clean database
74  $db->begin();
75  $sql1 = "UPDATE " . MAIN_DB_PREFIX . "expensereport_det as erd";
76  $sql1 .= " SET fk_code_ventilation = 0";
77  $sql1 .= ' WHERE erd.fk_code_ventilation NOT IN';
78  $sql1 .= ' (SELECT accnt.rowid ';
79  $sql1 .= ' FROM ' . MAIN_DB_PREFIX . 'accounting_account as accnt';
80  $sql1 .= ' INNER JOIN ' . MAIN_DB_PREFIX . 'accounting_system as syst';
81  $sql1 .= ' ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid=' . $conf->global->CHARTOFACCOUNTS . ' AND accnt.entity = '.$conf->entity.')';
82  $sql1 .= ' AND erd.fk_expensereport IN (SELECT rowid FROM ' . MAIN_DB_PREFIX . 'expensereport WHERE entity = '.$conf->entity.')';
83  $sql1 .= ' AND fk_code_ventilation <> 0';
84  dol_syslog("htdocs/accountancy/customer/index.php fixaccountancycode", LOG_DEBUG);
85  $resql1 = $db->query($sql1);
86  if (! $resql1) {
87  $error ++;
88  $db->rollback();
89  setEventMessages($db->lasterror(), null, 'errors');
90  } else {
91  $db->commit();
92  }
93  // End clean database
94 }
95 
96 if ($action == 'validatehistory') {
97 
98  $error = 0;
99  $db->begin();
100 
101  // Now make the binding
102  if ($db->type == 'pgsql') {
103  $sql1 = "UPDATE " . MAIN_DB_PREFIX . "expensereport_det";
104  $sql1 .= " SET fk_code_ventilation = accnt.rowid";
105  $sql1 .= " FROM " . MAIN_DB_PREFIX . "c_type_fees as t, " . MAIN_DB_PREFIX . "accounting_account as accnt , " . MAIN_DB_PREFIX . "accounting_system as syst";
106  $sql1 .= " WHERE " . MAIN_DB_PREFIX . "expensereport_det.fk_c_type_fees = t.id AND accnt.fk_pcg_version = syst.pcg_version AND syst.rowid=" . $conf->global->CHARTOFACCOUNTS.' AND accnt.entity = '.$conf->entity;
107  $sql1 .= " AND accnt.active = 1 AND t.accountancy_code = accnt.account_number";
108  $sql1 .= " AND " . MAIN_DB_PREFIX . "expensereport_det.fk_code_ventilation = 0";
109  } else {
110  $sql1 = "UPDATE " . MAIN_DB_PREFIX . "expensereport_det as erd, " . MAIN_DB_PREFIX . "c_type_fees as t, " . MAIN_DB_PREFIX . "accounting_account as accnt , " . MAIN_DB_PREFIX . "accounting_system as syst";
111  $sql1 .= " SET erd.fk_code_ventilation = accnt.rowid";
112  $sql1 .= " WHERE erd.fk_c_type_fees = t.id AND accnt.fk_pcg_version = syst.pcg_version AND syst.rowid=" . $conf->global->CHARTOFACCOUNTS.' AND accnt.entity = '.$conf->entity;
113  $sql1 .= " AND accnt.active = 1 AND t.accountancy_code=accnt.account_number";
114  $sql1 .= " AND erd.fk_code_ventilation = 0";
115  }
116 
117  dol_syslog('htdocs/accountancy/expensereport/index.php');
118 
119  $resql1 = $db->query($sql1);
120  if (! $resql1) {
121  $error ++;
122  $db->rollback();
123  setEventMessages($db->lasterror(), null, 'errors');
124  } else {
125  $db->commit();
126  setEventMessages($langs->trans('AutomaticBindingDone'), null, 'mesgs');
127  }
128 }
129 
130 
131 /*
132  * View
133  */
134 
135 llxHeader('', $langs->trans("ExpenseReportsVentilation"));
136 
137 $textprevyear = '<a href="' . $_SERVER["PHP_SELF"] . '?year=' . ($year_current - 1) . '">' . img_previous() . '</a>';
138 $textnextyear = '&nbsp;<a href="' . $_SERVER["PHP_SELF"] . '?year=' . ($year_current + 1) . '">' . img_next() . '</a>';
139 
140 print load_fiche_titre($langs->trans("ExpenseReportsVentilation") . "&nbsp;" . $textprevyear . "&nbsp;" . $langs->trans("Year") . "&nbsp;" . $year_start . "&nbsp;" . $textnextyear, '', 'title_accountancy');
141 
142 print $langs->trans("DescVentilExpenseReport") . '<br>';
143 print $langs->trans("DescVentilExpenseReportMore", $langs->transnoentitiesnoconv("ValidateHistory"), $langs->transnoentitiesnoconv("ToBind")) . '<br>';
144 print '<br>';
145 
146 
147 $y = $year_current;
148 
149 $buttonbind = '<a class="butAction" href="' . $_SERVER['PHP_SELF'] . '?year=' . $year_current . '&action=validatehistory">' . $langs->trans("ValidateHistory") . '</a>';
150 
151 
152 print_barre_liste($langs->trans("OverviewOfAmountOfLinesNotBound"), '', '', '', '', '', '', -1, '', '', 0, $buttonbind, '', 0, 1, 1);
153 //print load_fiche_titre($langs->trans("OverviewOfAmountOfLinesNotBound"), $buttonbind, '');
154 
155 print '<div class="div-table-responsive-no-min">';
156 print '<table class="noborder" width="100%">';
157 print '<tr class="liste_titre"><td width="200">' . $langs->trans("Account") . '</td>';
158 print '<td width="200" align="left">' . $langs->trans("Label") . '</td>';
159 for($i = 1; $i <= 12; $i ++) {
160  $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START?$conf->global->SOCIETE_FISCAL_MONTH_START:1) - 1;
161  if ($j > 12) $j-=12;
162  print '<td width="60" align="right">' . $langs->trans('MonthShort' . str_pad($j, 2, '0', STR_PAD_LEFT)) . '</td>';
163 }
164 print '<td width="60" align="right"><b>' . $langs->trans("Total") . '</b></td></tr>';
165 
166 $sql = "SELECT ".$db->ifsql('aa.account_number IS NULL', "'tobind'", 'aa.account_number') ." AS codecomptable,";
167 $sql .= " " . $db->ifsql('aa.label IS NULL', "'tobind'", 'aa.label') . " AS intitule,";
168 for($i = 1; $i <= 12; $i ++) {
169  $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START?$conf->global->SOCIETE_FISCAL_MONTH_START:1) - 1;
170  if ($j > 12) $j-=12;
171  $sql .= " SUM(" . $db->ifsql('MONTH(er.date_debut)=' . $j, 'erd.total_ht', '0') . ") AS month" . str_pad($j, 2, '0', STR_PAD_LEFT) . ",";
172 }
173 $sql .= " SUM(erd.total_ht) as total";
174 $sql .= " FROM " . MAIN_DB_PREFIX . "expensereport_det as erd";
175 $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "expensereport as er ON er.rowid = erd.fk_expensereport";
176 $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
177 $sql .= " WHERE er.date_debut >= '" . $db->idate($search_date_start) . "'";
178 $sql .= " AND er.date_debut <= '" . $db->idate($search_date_end) . "'";
179 $sql .= " AND er.fk_statut IN (".ExpenseReport::STATUS_APPROVED.", ".ExpenseReport::STATUS_CLOSED.")";
180 $sql .= " AND er.entity IN (" . getEntity('expensereport', 0) . ")"; // We don't share object for accountancy
181 $sql .= " AND aa.account_number IS NULL";
182 $sql .= " GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
183 
184 dol_syslog('/accountancy/expensereport/index.php:: sql=' . $sql);
185 $resql = $db->query($sql);
186 if ($resql) {
187  $num = $db->num_rows($resql);
188 
189  while ( $row = $db->fetch_row($resql)) {
190 
191  print '<tr class="oddeven"><td>';
192  if ($row[0] == 'tobind')
193  {
194  print $langs->trans("Unknown");
195  }
196  else print length_accountg($row[0]);
197  print '</td>';
198  print '<td align="left">';
199  if ($row[0] == 'tobind')
200  {
201  print $langs->trans("UseMenuToSetBindindManualy", DOL_URL_ROOT.'/accountancy/expensereport/list.php?search_year='.$y, $langs->transnoentitiesnoconv("ToBind"));
202  }
203  else print $row[1];
204  print '</td>';
205  for($i = 2; $i <= 12; $i ++) {
206  print '<td align="right">' . price($row[$i]) . '</td>';
207  }
208  print '<td align="right">' . price($row[13]) . '</td>';
209  print '<td align="right"><b>' . price($row[14]) . '</b></td>';
210  print '</tr>';
211  }
212  $db->free($resql);
213 } else {
214  print $db->lasterror(); // Show last sql error
215 }
216 print "</table>\n";
217 print '</div>';
218 
219 
220 print '<br>';
221 
222 
223 print_barre_liste($langs->trans("OverviewOfAmountOfLinesBound"), '', '', '', '', '', '', -1, '', '', 0, '', '', 0, 1, 1);
224 //print load_fiche_titre($langs->trans("OverviewOfAmountOfLinesBound"), '', '');
225 
226 
227 print '<div class="div-table-responsive-no-min">';
228 print '<table class="noborder" width="100%">';
229 print '<tr class="liste_titre"><td width="200">' . $langs->trans("Account") . '</td>';
230 print '<td width="200" align="left">' . $langs->trans("Label") . '</td>';
231 for($i = 1; $i <= 12; $i ++) {
232  $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START?$conf->global->SOCIETE_FISCAL_MONTH_START:1) - 1;
233  if ($j > 12) $j-=12;
234  print '<td width="60" align="right">' . $langs->trans('MonthShort' . str_pad($j, 2, '0', STR_PAD_LEFT)) . '</td>';
235 }
236 print '<td width="60" align="right"><b>' . $langs->trans("Total") . '</b></td></tr>';
237 
238 $sql = "SELECT ".$db->ifsql('aa.account_number IS NULL', "'tobind'", 'aa.account_number') ." AS codecomptable,";
239 $sql .= " " . $db->ifsql('aa.label IS NULL', "'tobind'", 'aa.label') . " AS intitule,";
240 for($i = 1; $i <= 12; $i ++) {
241  $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START?$conf->global->SOCIETE_FISCAL_MONTH_START:1) - 1;
242  if ($j > 12) $j-=12;
243  $sql .= " SUM(" . $db->ifsql('MONTH(er.date_debut)=' . $j, 'erd.total_ht', '0') . ") AS month" . str_pad($j, 2, '0', STR_PAD_LEFT) . ",";
244 }
245 $sql .= " ROUND(SUM(erd.total_ht),2) as total";
246 $sql .= " FROM " . MAIN_DB_PREFIX . "expensereport_det as erd";
247 $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "expensereport as er ON er.rowid = erd.fk_expensereport";
248 $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
249 $sql .= " WHERE er.date_debut >= '" . $db->idate($search_date_start) . "'";
250 $sql .= " AND er.date_debut <= '" . $db->idate($search_date_end) . "'";
251 $sql .= " AND er.fk_statut IN (".ExpenseReport::STATUS_APPROVED.", ".ExpenseReport::STATUS_CLOSED.")";
252 $sql .= " AND er.entity IN (" . getEntity('expensereport', 0) . ")"; // We don't share object for accountancy
253 $sql .= " AND aa.account_number IS NOT NULL";
254 $sql .= " GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
255 
256 dol_syslog('htdocs/accountancy/expensereport/index.php');
257 $resql = $db->query($sql);
258 if ($resql) {
259  $num = $db->num_rows($resql);
260 
261  while ( $row = $db->fetch_row($resql)) {
262 
263  print '<tr class="oddeven"><td>';
264  if ($row[0] == 'tobind')
265  {
266  print $langs->trans("Unknown");
267  }
268  else print length_accountg($row[0]);
269  print '</td>';
270 
271  print '<td align="left">';
272  if ($row[0] == 'tobind')
273  {
274  print $langs->trans("UseMenuToSetBindindManualy", DOL_URL_ROOT.'/accountancy/expensereport/list.php?search_year='.$y, $langs->transnoentitiesnoconv("ToBind"));
275  }
276  else print $row[1];
277  print '</td>';
278  for($i = 2; $i <= 12; $i ++) {
279  print '<td align="right">' . price($row[$i]) . '</td>';
280  }
281  print '<td align="right">' . price($row[13]) . '</td>';
282  print '<td align="right"><b>' . price($row[14]) . '</b></td>';
283  print '</tr>';
284  }
285  $db->free($resql);
286 } else {
287  print $db->lasterror(); // Show last sql error
288 }
289 print "</table>\n";
290 print '</div>';
291 
292 
293 
294 if ($conf->global->MAIN_FEATURES_LEVEL > 0) // This part of code looks strange. Why showing a report where results depends on next step (so not yet available) ?
295 {
296  print '<br>';
297  print '<br>';
298 
299  print_barre_liste($langs->trans("OtherInfo"), '', '', '', '', '', '', -1, '', '', 0, '', '', 0, 1, 1);
300  //print load_fiche_titre($langs->trans("OtherInfo"), '', '');
301 
302  print '<div class="div-table-responsive-no-min">';
303  print '<table class="noborder" width="100%">';
304  print '<tr class="liste_titre"><td width="400" align="left">' . $langs->trans("Total") . '</td>';
305  for($i = 1; $i <= 12; $i ++) {
306  $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START?$conf->global->SOCIETE_FISCAL_MONTH_START:1) - 1;
307  if ($j > 12) $j-=12;
308  print '<td width="60" align="right">' . $langs->trans('MonthShort' . str_pad($j, 2, '0', STR_PAD_LEFT)) . '</td>';
309  }
310  print '<td width="60" align="right"><b>' . $langs->trans("Total") . '</b></td></tr>';
311 
312  $sql = "SELECT '" . $langs->trans("TotalExpenseReport") . "' AS label,";
313  for($i = 1; $i <= 12; $i ++) {
314  $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START?$conf->global->SOCIETE_FISCAL_MONTH_START:1) - 1;
315  if ($j > 12) $j-=12;
316  $sql .= " SUM(" . $db->ifsql('MONTH(er.date_create)=' . $j, 'erd.total_ht', '0') . ") AS month" . str_pad($j, 2, '0', STR_PAD_LEFT) . ",";
317  }
318  $sql .= " SUM(erd.total_ht) as total";
319  $sql .= " FROM " . MAIN_DB_PREFIX . "expensereport_det as erd";
320  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "expensereport as er ON er.rowid = erd.fk_expensereport";
321  $sql .= " WHERE er.date_debut >= '" . $db->idate($search_date_start) . "'";
322  $sql .= " AND er.date_debut <= '" . $db->idate($search_date_end) . "'";
323  $sql .= " AND er.fk_statut IN (".ExpenseReport::STATUS_APPROVED.", ".ExpenseReport::STATUS_CLOSED.")";
324  $sql .= " AND er.entity IN (" . getEntity('expensereport', 0) . ")"; // We don't share object for accountancy
325 
326  dol_syslog('htdocs/accountancy/expensereport/index.php');
327  $resql = $db->query($sql);
328  if ($resql) {
329  $num = $db->num_rows($resql);
330 
331  while ($row = $db->fetch_row($resql)) {
332  print '<tr><td>' . $row[0] . '</td>';
333  for($i = 1; $i <= 12; $i ++) {
334  print '<td align="right">' . price($row[$i]) . '</td>';
335  }
336  print '<td align="right"><b>' . price($row[13]) . '</b></td>';
337  print '</tr>';
338  }
339 
340  $db->free($resql);
341  } else {
342  print $db->lasterror(); // Show last sql error
343  }
344  print "</table>\n";
345  print '</div>';
346 }
347 
348 // End of page
349 llxFooter();
350 $db->close();
llxFooter()
Footer empty.
Definition: index.php:44
load_fiche_titre($titre, $morehtmlright='', $picto='title_generic.png', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='')
Load a title with picto.
GETPOST($paramname, $check='none', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
print
Draft customers invoices.
Definition: index.php:91
setEventMessages($mesg, $mesgs, $style='mesgs')
Set event messages in dol_events session object.
if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) if(! empty($conf->don->enabled) && $user->rights->societe->lire) if(! empty($conf->tax->enabled) && $user->rights->tax->charges->lire) if(! empty($conf->facture->enabled) &&! empty($conf->commande->enabled) && $user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1053
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...
const STATUS_CLOSED
Classified paid.
img_next($titlealt='default', $moreatt='')
Show next logo.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0)
Show a message to say access is forbidden and stop program Calling this function terminate execution ...
print_barre_liste($titre, $page, $file, $options='', $sortfield='', $sortorder='', $morehtmlcenter='', $num=-1, $totalnboflines='', $picto='title_generic.png', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limit=-1, $hideselectlimit=0, $hidenavigation=0)
Print a title with navigation controls for pagination.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
llxHeader()
Header empty.
Definition: index.php:36
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_print_date($time, $format='', $tzoutput='tzserver', $outputlangs='', $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:467
img_previous($titlealt='default', $moreatt='')
Show previous logo.
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous) ...