28 require
'../../main.inc.php';
29 require_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
30 require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
31 require_once DOL_DOCUMENT_ROOT.
'/expensereport/class/expensereport.class.php';
34 $langs->loadLangs(array(
"compta",
"bills",
"other",
"accountancy"));
36 $validatemonth =
GETPOST(
'validatemonth',
'int');
37 $validateyear =
GETPOST(
'validateyear',
'int');
39 $month_start = ($conf->global->SOCIETE_FISCAL_MONTH_START ? ($conf->global->SOCIETE_FISCAL_MONTH_START) : 1);
41 $year_start =
GETPOST(
"year",
'int');
48 $year_end = $year_start + 1;
49 $month_end = $month_start - 1;
54 $search_date_start =
dol_mktime(0, 0, 0, $month_start, 1, $year_start);
56 $year_current = $year_start;
59 $action =
GETPOST(
'action',
'aZ09');
65 if ($user->socid > 0) {
68 if (empty($user->rights->accounting->mouvements->lire)) {
77 if (($action ==
'clean' || $action ==
'validatehistory') && $user->rights->accounting->bind->write) {
80 $sql1 =
"UPDATE ".MAIN_DB_PREFIX.
"expensereport_det as erd";
81 $sql1 .=
" SET fk_code_ventilation = 0";
82 $sql1 .=
' WHERE erd.fk_code_ventilation NOT IN';
83 $sql1 .=
' (SELECT accnt.rowid ';
84 $sql1 .=
' FROM '.MAIN_DB_PREFIX.
'accounting_account as accnt';
85 $sql1 .=
' INNER JOIN '.MAIN_DB_PREFIX.
'accounting_system as syst';
86 $sql1 .=
' ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid='.((int) $conf->global->CHARTOFACCOUNTS).
' AND accnt.entity = '.((int) $conf->entity).
')';
87 $sql1 .=
' AND erd.fk_expensereport IN (SELECT rowid FROM '.MAIN_DB_PREFIX.
'expensereport WHERE entity = '.((int) $conf->entity).
')';
88 $sql1 .=
' AND fk_code_ventilation <> 0';
89 dol_syslog(
"htdocs/accountancy/customer/index.php fixaccountancycode", LOG_DEBUG);
90 $resql1 = $db->query($sql1);
101 if ($action ==
'validatehistory') {
109 $sql1 =
"SELECT erd.rowid, accnt.rowid as suggestedid";
110 $sql1 .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
111 $sql1 .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"c_type_fees as t ON erd.fk_c_type_fees = t.id";
112 $sql1 .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as accnt ON t.accountancy_code = accnt.account_number AND accnt.active = 1 AND accnt.entity =".((int) $conf->entity);
113 $sql1 .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_system as syst ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid = ".((int) $conf->global->CHARTOFACCOUNTS).
' AND syst.active = 1,';
114 $sql1 .=
" ".MAIN_DB_PREFIX.
"expensereport as er";
115 $sql1 .=
" WHERE erd.fk_expensereport = er.rowid AND er.entity = ".((int) $conf->entity);
116 $sql1 .=
" AND er.fk_statut IN (".ExpenseReport::STATUS_APPROVED.
", ".
ExpenseReport::STATUS_CLOSED.
") AND erd.fk_code_ventilation <= 0";
117 if ($validatemonth && $validateyear) {
121 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
123 $result = $db->query($sql1);
128 $num_lines = $db->num_rows($result);
131 while ($i < min($num_lines, 10000)) {
132 $objp = $db->fetch_object($result);
134 $lineid = $objp->rowid;
135 $suggestedid = $objp->suggestedid;
137 if ($suggestedid > 0) {
138 $sqlupdate =
"UPDATE ".MAIN_DB_PREFIX.
"expensereport_det";
139 $sqlupdate .=
" SET fk_code_ventilation = ".((int) $suggestedid);
140 $sqlupdate .=
" WHERE fk_code_ventilation <= 0 AND rowid = ".((int) $lineid);
142 $resqlupdate = $db->query($sqlupdate);
156 if ($num_lines > 10000) {
157 $notpossible += ($num_lines - 10000);
165 setEventMessages($langs->trans(
'AutomaticBindingDone', $nbbinddone, $notpossible),
null,
'mesgs');
174 llxHeader(
'', $langs->trans(
"ExpenseReportsVentilation"));
176 $textprevyear =
'<a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current - 1).
'">'.
img_previous().
'</a>';
177 $textnextyear =
' <a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current + 1).
'">'.
img_next().
'</a>';
179 print
load_fiche_titre($langs->trans(
"ExpenseReportsVentilation").
" ".$textprevyear.
" ".$langs->trans(
"Year").
" ".$year_start.
" ".$textnextyear,
'',
'title_accountancy');
181 print
'<span class="opacitymedium">'.$langs->trans(
"DescVentilExpenseReport").
'</span><br>';
182 print
'<span class="opacitymedium hideonsmartphone">'.$langs->trans(
"DescVentilExpenseReportMore", $langs->transnoentitiesnoconv(
"ValidateHistory"), $langs->transnoentitiesnoconv(
"ToBind")).
'<br>';
188 $buttonbind =
'<a class="butAction" href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&token='.
newToken().
'&year='.$year_current.
'">'.$langs->trans(
"ValidateHistory").
'</a>';
191 print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0, $buttonbind,
'', 0, 1, 1);
194 print
'<div class="div-table-responsive-no-min">';
195 print
'<table class="noborder centpercent">';
196 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
197 print
'<td>'.$langs->trans(
"Label").
'</td>';
198 for ($i = 1; $i <= 12; $i++) {
199 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
204 if ($cursormonth > 12) {
207 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
210 print
'<td width="60" class="right">';
211 if (!empty($tmp[
'mday'])) {
212 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
213 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
214 $param .=
'&search_month='.$tmp[
'mon'].
'&search_year='.$tmp[
'year'];
215 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/expensereport/list.php?'.$param.
'">';
217 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
218 if (!empty($tmp[
'mday'])) {
223 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
225 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
226 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
227 for ($i = 1; $i <= 12; $i++) {
228 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
232 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_debut)=".$j,
"erd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
234 $sql .=
" SUM(erd.total_ht) as total";
235 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
236 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
237 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
238 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
239 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
241 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
242 $sql .=
" AND er.date_debut >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
245 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
246 $sql .=
" AND aa.account_number IS NULL";
247 $sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
248 $sql .=
' ORDER BY aa.account_number';
250 dol_syslog(
'/accountancy/expensereport/index.php:: sql='.$sql);
251 $resql = $db->query($sql);
253 $num = $db->num_rows(
$resql);
255 while ($row = $db->fetch_row(
$resql)) {
256 print
'<tr class="oddeven">';
258 if ($row[0] ==
'tobind') {
259 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
265 if ($row[0] ==
'tobind') {
266 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/expensereport/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
271 for ($i = 2; $i <= 13; $i++) {
272 print
'<td class="right nowraponall amount">';
273 print
price($row[$i]);
276 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
281 print $db->lasterror();
290 print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
294 print
'<div class="div-table-responsive-no-min">';
295 print
'<table class="noborder centpercent">';
296 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
297 print
'<td>'.$langs->trans(
"Label").
'</td>';
298 for ($i = 1; $i <= 12; $i++) {
299 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
303 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
305 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
307 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
308 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
309 for ($i = 1; $i <= 12; $i++) {
310 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
314 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_debut)=".$j,
"erd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
316 $sql .=
" ROUND(SUM(erd.total_ht),2) as total";
317 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
318 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
319 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
320 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
321 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
323 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
324 $sql .=
" AND er.date_debut >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
327 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
328 $sql .=
" AND aa.account_number IS NOT NULL";
329 $sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
331 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
332 $resql = $db->query($sql);
334 $num = $db->num_rows(
$resql);
336 while ($row = $db->fetch_row(
$resql)) {
337 print
'<tr class="oddeven">';
339 if ($row[0] ==
'tobind') {
340 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
347 if ($row[0] ==
'tobind') {
348 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/expensereport/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
353 for ($i = 2; $i <= 13; $i++) {
354 print
'<td class="right nowraponall amount">';
355 print
price($row[$i]);
358 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
363 print $db->lasterror();
370 if ($conf->global->MAIN_FEATURES_LEVEL > 0) {
374 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
377 print
'<div class="div-table-responsive-no-min">';
378 print
'<table class="noborder centpercent">';
379 print
'<tr class="liste_titre"><td class="left">'.$langs->trans(
"Total").
'</td>';
380 for ($i = 1; $i <= 12; $i++) {
381 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
385 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
387 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
389 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalExpenseReport")).
"' AS label,";
390 for ($i = 1; $i <= 12; $i++) {
391 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
395 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_create)=".$j,
"erd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
397 $sql .=
" SUM(erd.total_ht) as total";
398 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
399 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
400 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
401 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
403 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
404 $sql .=
" AND er.date_debut >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
407 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
409 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
410 $resql = $db->query($sql);
412 $num = $db->num_rows(
$resql);
414 while ($row = $db->fetch_row(
$resql)) {
415 print
'<tr><td>'.$row[0].
'</td>';
416 for ($i = 1; $i <= 12; $i++) {
417 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
419 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
425 print $db->lasterror();
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
const STATUS_CLOSED
Classified paid.
if(isModEnabled('facture') &&!empty($user->rights->facture->lire)) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') &&!empty($user->rights->don->lire)) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $resql
Social contributions to pay.
dolSqlDateFilter($datefield, $day_date, $month_date, $year_date, $excludefirstand=0, $gm=false)
Generate a SQL string to make a filter into a range (for second of date until last second of date).
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm='auto', $check=1)
Return a timestamp date built from detailed informations (by default a local PHP server timestamp) Re...
load_fiche_titre($titre, $morehtmlright='', $picto='generic', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='')
Load a title with picto.
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='')
Set event messages in dol_events session object.
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='auto', $outputlangs='', $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
dol_now($mode='auto')
Return date for now.
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=false, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2)
Show picto whatever it's its name (generic function)
newToken()
Return the value of token currently saved into session with name 'newtoken'.
img_previous($titlealt='default', $moreatt='')
Show previous logo.
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
print_barre_liste($titre, $page, $file, $options='', $sortfield='', $sortorder='', $morehtmlcenter='', $num=-1, $totalnboflines='', $picto='generic', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limit=-1, $hideselectlimit=0, $hidenavigation=0, $pagenavastextinput=0, $morehtmlrightbeforearrow='')
Print a title with navigation controls for pagination.
img_next($titlealt='default', $moreatt='')
Show next logo.
isModEnabled($module)
Is Dolibarr module enabled.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_getdate($timestamp, $fast=false, $forcetimezone='')
Return an array with locale date info.
if(!defined('NOTOKENRENEWAL')) if(!defined('NOLOGIN')) if(!defined('NOCSRFCHECK')) if(!defined('NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined('NOREQUIREAJAX')) if(!defined('NOIPCHECK')) if(!defined('NOBROWSERNOTIF')) llxHeader()
Header empty.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.