28require
'../../main.inc.php';
29require_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
30require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
31require_once DOL_DOCUMENT_ROOT.
'/expensereport/class/expensereport.class.php';
34$langs->loadLangs(array(
"compta",
"bills",
"other",
"accountancy"));
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');
64if (!isModEnabled(
'accounting')) {
67if ($user->socid > 0) {
70if (!$user->hasRight(
'accounting',
'bind',
'write')) {
79if (($action ==
'clean' || $action ==
'validatehistory') && $user->hasRight(
'accounting',
'bind',
'write')) {
82 $sql1 =
"UPDATE ".$db->prefix().
"expensereport_det as erd";
83 $sql1 .=
" SET fk_code_ventilation = 0";
84 $sql1 .=
' WHERE erd.fk_code_ventilation NOT IN';
85 $sql1 .=
' (SELECT accnt.rowid ';
86 $sql1 .=
' FROM '.$db->prefix().
'accounting_account as accnt';
87 $sql1 .=
' INNER JOIN '.$db->prefix().
'accounting_system as syst';
88 $sql1 .=
' ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid='.((int)
getDolGlobalInt(
'CHARTOFACCOUNTS')).
' AND accnt.entity = '.((
int) $conf->entity).
')';
89 $sql1 .=
' AND erd.fk_expensereport IN (SELECT rowid FROM '.$db->prefix().
'expensereport WHERE entity = '.((int) $conf->entity).
')';
90 $sql1 .=
" AND fk_code_ventilation <> 0";
91 dol_syslog(
"htdocs/accountancy/customer/index.php fixaccountancycode", LOG_DEBUG);
92 $resql1 = $db->query($sql1);
103if ($action ==
'validatehistory' && $user->hasRight(
'accounting',
'bind',
'write')) {
112 $sql1 =
"SELECT erd.rowid, accnt.rowid as suggestedid";
113 $sql1 .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
114 $sql1 .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"c_type_fees as t ON erd.fk_c_type_fees = t.id";
115 $sql1 .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as accnt ON t.accountancy_code = accnt.account_number AND accnt.active = 1 AND accnt.fk_pcg_version = '".$db->escape($chartaccountcode).
"' AND accnt.entity =".((int) $conf->entity).
",";
116 $sql1 .=
" ".MAIN_DB_PREFIX.
"expensereport as er";
117 $sql1 .=
" WHERE erd.fk_expensereport = er.rowid AND er.entity = ".((int) $conf->entity);
118 $sql1 .=
" AND er.fk_statut IN (".ExpenseReport::STATUS_APPROVED.
", ".
ExpenseReport::STATUS_CLOSED.
") AND erd.fk_code_ventilation <= 0";
119 if ($validatemonth && $validateyear) {
123 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
125 $result = $db->query($sql1);
130 $num_lines = $db->num_rows($result);
133 while ($i < min($num_lines, 10000)) {
134 $objp = $db->fetch_object($result);
136 $lineid = $objp->rowid;
137 $suggestedid = $objp->suggestedid;
139 if ($suggestedid > 0) {
140 $sqlupdate =
"UPDATE ".MAIN_DB_PREFIX.
"expensereport_det";
141 $sqlupdate .=
" SET fk_code_ventilation = ".((int) $suggestedid);
142 $sqlupdate .=
" WHERE fk_code_ventilation <= 0 AND rowid = ".((int) $lineid);
144 $resqlupdate = $db->query($sqlupdate);
160 if ($num_lines > 10000) {
161 $notpossible += ($num_lines - 10000);
169 setEventMessages($langs->trans(
'AutomaticBindingDone', $nbbinddone, $notpossible),
null, ($notpossible ?
'warnings' :
'mesgs'));
171 setEventMessages($langs->trans(
'DoManualBindingForFailedRecord', $nbbindfailed),
null,
'warnings');
180$help_url =
'EN:Module_Double_Entry_Accounting|FR:Module_Comptabilité_en_Partie_Double#Liaisons_comptables';
182llxHeader(
'', $langs->trans(
"ExpenseReportsVentilation"), $help_url,
'', 0, 0,
'',
'',
'',
'mod-accountancy accountancy-expensereport page-list');
184$textprevyear =
'<a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current - 1).
'">'.
img_previous().
'</a>';
185$textnextyear =
' <a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current + 1).
'">'.
img_next().
'</a>';
188print
load_fiche_titre($langs->trans(
"ExpenseReportsVentilation").
" ".$textprevyear.
" ".$langs->trans(
"Year").
" ".$year_start.
" ".$textnextyear,
'',
'title_accountancy');
190print
'<span class="opacitymedium">'.$langs->trans(
"DescVentilExpenseReport").
'</span><br>';
191print
'<span class="opacitymedium hideonsmartphone">'.$langs->trans(
"DescVentilExpenseReportMore", $langs->transnoentitiesnoconv(
"ValidateHistory"), $langs->transnoentitiesnoconv(
"ToBind")).
'<br>';
197$buttonbind =
'<a class="button small" href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&token='.
newToken().
'&year='.$year_current.
'">'.
img_picto($langs->trans(
"ValidateHistory"),
'link',
'class="pictofixedwidth fa-color-unset"').$langs->trans(
"ValidateHistory").
'</a>';
200print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1, 0, $buttonbind);
203print
'<div class="div-table-responsive-no-min">';
204print
'<table class="noborder centpercent">';
205print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
206for ($i = 1; $i <= 12; $i++) {
212 if ($cursormonth > 12) {
215 $cursoryear = ($cursormonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
218 print
'<td width="60" class="right">';
219 if (!empty($tmp[
'mday'])) {
220 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
221 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
222 $param .=
'&search_month='.$tmp[
'mon'].
'&search_year='.$tmp[
'year'];
223 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/expensereport/list.php?'.$param.
'">';
225 print $langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT));
226 if (!empty($tmp[
'mday'])) {
231print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
233$sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
234$sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
235for ($i = 1; $i <= 12; $i++) {
240 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_debut) = ".((
int) $j),
"erd.total_ht",
"0").
") AS month".str_pad((
string) $j, 2,
"0", STR_PAD_LEFT).
",";
241 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_debut) = ".((
string) $j),
"1",
"0").
") AS nbmonth".str_pad((
string) $j, 2,
"0", STR_PAD_LEFT).
",";
243$sql .=
" SUM(erd.total_ht) as total, COUNT(erd.rowid) as nb";
244$sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
245$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
246$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
247$sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
248$sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
251 $sql .=
" AND er.date_debut >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
254$sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
255$sql .=
" AND aa.account_number IS NULL";
256$sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
257$sql .=
' ORDER BY aa.account_number';
259dol_syslog(
'/accountancy/expensereport/index.php', LOG_DEBUG);
260$resql = $db->query($sql);
262 $num = $db->num_rows($resql);
264 while ($row = $db->fetch_row($resql)) {
265 print
'<tr class="oddeven">';
267 if ($row[0] ==
'tobind') {
274 if ($row[0] ==
'tobind') {
276 if ($startmonth > 12) {
279 $startyear = ($startmonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
281 if ($endmonth > 12) {
284 $endyear = ($endmonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
285 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/expensereport/list.php?search_date_startday=1&search_date_startmonth='.((
int) $startmonth).
'&search_date_startyear='.((
int) $startyear).
'&search_date_endday=&search_date_endmonth='.((
int) $endmonth).
'&search_date_endyear='.((
int) $endyear), $langs->transnoentitiesnoconv(
"ToBind"));
290 for ($i = 2; $i <= 13; $i++) {
291 $cursormonth = (
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1) + $i - 2);
292 if ($cursormonth > 12) {
295 $cursoryear = ($cursormonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
298 print
'<td class="right nowraponall amount" title="'.price($row[2*$i - 2]).
' - '.$row[2*$i - 1].
' lines">';
299 print
price($row[2*$i - 2]);
301 if (!empty(
price2num($row[2*$i - 2])) || !empty($row[2*$i - 1])) {
302 print
'<a href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&year='.$y.
'&validatemonth='.((int) $cursormonth).
'&validateyear='.((int) $cursoryear).
'&token='.
newToken().
'">';
303 print
img_picto($langs->trans(
"ValidateHistory").
' ('.$langs->trans(
'Month'.str_pad((
string) $cursormonth, 2,
'0', STR_PAD_LEFT)).
' '.$cursoryear.
')',
'link',
'class="marginleft2"');
308 print
'<td class="right nowraponall amount"><b>'.price($row[26]).
'</b></td>';
314 print
'<tr class="oddeven"><td colspan="15">';
315 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
319 print $db->lasterror();
328print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
332print
'<div class="div-table-responsive-no-min">';
333print
'<table class="noborder centpercent">';
334print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
335for ($i = 1; $i <= 12; $i++) {
340 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT)).
'</td>';
342print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
344$sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
345$sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
346for ($i = 1; $i <= 12; $i++) {
351 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_debut) = ".((
int) $j),
"erd.total_ht",
"0").
") AS month".str_pad((
string) $j, 2,
"0", STR_PAD_LEFT).
",";
353$sql .=
" ROUND(SUM(erd.total_ht),2) as total";
354$sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
355$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
356$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
357$sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
358$sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
361 $sql .=
" AND er.date_debut >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
364$sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
365$sql .=
" AND aa.account_number IS NOT NULL";
366$sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
368dol_syslog(
'htdocs/accountancy/expensereport/index.php');
369$resql = $db->query($sql);
371 $num = $db->num_rows($resql);
373 while ($row = $db->fetch_row($resql)) {
374 print
'<tr class="oddeven">';
375 print
'<td class="tdoverflowmax300"'.(empty($row[1]) ?
'' :
' title="'.dol_escape_htmltag($row[1]).
'"').
'>';
376 if ($row[0] ==
'tobind') {
381 if ($row[0] ==
'tobind') {
382 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/expensereport/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
387 for ($i = 2; $i <= 13; $i++) {
388 print
'<td class="right nowraponall amount">';
389 print
price($row[$i]);
392 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
398 print
'<tr class="oddeven"><td colspan="15">';
399 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
403 print $db->lasterror();
414 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
417 print
'<div class="div-table-responsive-no-min">';
418 print
'<table class="noborder centpercent">';
419 print
'<tr class="liste_titre"><td class="left">'.$langs->trans(
"Total").
'</td>';
420 for ($i = 1; $i <= 12; $i++) {
425 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT)).
'</td>';
427 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
429 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalExpenseReport")).
"' AS label,";
430 for ($i = 1; $i <= 12; $i++) {
435 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_create) = ".((
int) $j),
"erd.total_ht",
"0").
") AS month".str_pad((
string) $j, 2,
"0", STR_PAD_LEFT).
",";
437 $sql .=
" SUM(erd.total_ht) as total";
438 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
439 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
440 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
441 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
444 $sql .=
" AND er.date_debut >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
447 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
449 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
450 $resql = $db->query($sql);
452 $num = $db->num_rows($resql);
454 while ($row = $db->fetch_row($resql)) {
455 print
'<tr><td>'.$row[0].
'</td>';
456 for ($i = 1; $i <= 12; $i++) {
457 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
459 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
465 print $db->lasterror();
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
if(!defined('NOREQUIRESOC')) if(!defined( 'NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined( 'NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined( 'NOREQUIREAJAX')) llxHeader()
Empty header.
const STATUS_CLOSED
Classified paid.
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 information (by default a local PHP server timestamp) Rep...
load_fiche_titre($title, $morehtmlright='', $picto='generic', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='')
Load a title with picto.
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2)
Show picto whatever it's its name (generic function)
GETPOSTINT($paramname, $method=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
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_now($mode='auto')
Return date for now.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
dol_getIdFromCode($db, $key, $tablename, $fieldkey='code', $fieldid='id', $entityfilter=0, $filters='')
Return an id or code from a code or id.
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
newToken()
Return the value of token currently saved into session with name 'newtoken'.
img_previous($titlealt='default', $moreatt='')
Show previous logo.
print_barre_liste($title, $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.
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='', $noduplicate=0)
Set event messages in dol_events session object.
img_next($titlealt='default', $moreatt='')
Show next logo.
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_getdate($timestamp, $fast=false, $forcetimezone='')
Return an array with locale date info.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.