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 ".MAIN_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 '.MAIN_DB_PREFIX.
'accounting_account as accnt';
87 $sql1 .=
' INNER JOIN '.MAIN_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 '.MAIN_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(
'',
'link',
'class="paddingright 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).
",";
242$sql .=
" SUM(erd.total_ht) as total";
243$sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
244$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
245$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
246$sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
247$sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
250 $sql .=
" AND er.date_debut >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
253$sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
254$sql .=
" AND aa.account_number IS NULL";
255$sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
256$sql .=
' ORDER BY aa.account_number';
258dol_syslog(
'/accountancy/expensereport/index.php', LOG_DEBUG);
259$resql = $db->query($sql);
261 $num = $db->num_rows($resql);
263 while ($row = $db->fetch_row($resql)) {
264 print
'<tr class="oddeven">';
266 if ($row[0] ==
'tobind') {
273 if ($row[0] ==
'tobind') {
275 if ($startmonth > 12) {
278 $startyear = ($startmonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
280 if ($endmonth > 12) {
283 $endyear = ($endmonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
284 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"));
289 for ($i = 2; $i <= 13; $i++) {
290 $cursormonth = (
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1) + $i - 2);
291 if ($cursormonth > 12) {
294 $cursoryear = ($cursormonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
297 print
'<td class="right nowraponall amount">';
298 print
price($row[$i]);
301 print
'<a href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&year='.$y.
'&validatemonth='.((int) $cursormonth).
'&validateyear='.((int) $cursoryear).
'&token='.
newToken().
'">';
302 print
img_picto($langs->trans(
"ValidateHistory").
' ('.$langs->trans(
'Month'.str_pad((
string) $cursormonth, 2,
'0', STR_PAD_LEFT)).
' '.$cursoryear.
')',
'link',
'class="marginleft2"');
307 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
313 print
'<tr class="oddeven"><td colspan="15">';
314 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
318 print $db->lasterror();
327print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
331print
'<div class="div-table-responsive-no-min">';
332print
'<table class="noborder centpercent">';
333print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
334for ($i = 1; $i <= 12; $i++) {
339 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT)).
'</td>';
341print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
343$sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
344$sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
345for ($i = 1; $i <= 12; $i++) {
350 $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).
",";
352$sql .=
" ROUND(SUM(erd.total_ht),2) as total";
353$sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
354$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
355$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
356$sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
357$sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
360 $sql .=
" AND er.date_debut >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
363$sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
364$sql .=
" AND aa.account_number IS NOT NULL";
365$sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
367dol_syslog(
'htdocs/accountancy/expensereport/index.php');
368$resql = $db->query($sql);
370 $num = $db->num_rows($resql);
372 while ($row = $db->fetch_row($resql)) {
373 print
'<tr class="oddeven">';
375 if ($row[0] ==
'tobind') {
382 if ($row[0] ==
'tobind') {
383 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/expensereport/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
388 for ($i = 2; $i <= 13; $i++) {
389 print
'<td class="right nowraponall amount">';
390 print
price($row[$i]);
393 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
399 print
'<tr class="oddeven"><td colspan="15">';
400 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
404 print $db->lasterror();
415 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
418 print
'<div class="div-table-responsive-no-min">';
419 print
'<table class="noborder centpercent">';
420 print
'<tr class="liste_titre"><td class="left">'.$langs->trans(
"Total").
'</td>';
421 for ($i = 1; $i <= 12; $i++) {
426 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT)).
'</td>';
428 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
430 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalExpenseReport")).
"' AS label,";
431 for ($i = 1; $i <= 12; $i++) {
436 $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).
",";
438 $sql .=
" SUM(erd.total_ht) as total";
439 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
440 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
441 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
442 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
445 $sql .=
" AND er.date_debut >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
448 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
450 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
451 $resql = $db->query($sql);
453 $num = $db->num_rows($resql);
455 while ($row = $db->fetch_row($resql)) {
456 print
'<tr><td>'.$row[0].
'</td>';
457 for ($i = 1; $i <= 12; $i++) {
458 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
460 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
466 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.