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';
33 $langs->loadLangs(array(
"compta",
"bills",
"other",
"accountancy"));
35 $validatemonth =
GETPOST(
'validatemonth',
'int');
36 $validateyear =
GETPOST(
'validateyear',
'int');
38 $month_start = ($conf->global->SOCIETE_FISCAL_MONTH_START ? ($conf->global->SOCIETE_FISCAL_MONTH_START) : 1);
40 $year_start =
GETPOST(
"year",
'int');
47 $year_end = $year_start + 1;
48 $month_end = $month_start - 1;
53 $search_date_start =
dol_mktime(0, 0, 0, $month_start, 1, $year_start);
55 $year_current = $year_start;
58 $action =
GETPOST(
'action',
'aZ09');
64 if ($user->socid > 0) {
67 if (empty($user->rights->accounting->mouvements->lire)) {
76 if (($action ==
'clean' || $action ==
'validatehistory') && $user->rights->accounting->bind->write) {
79 $sql1 =
"UPDATE ".MAIN_DB_PREFIX.
"expensereport_det as erd";
80 $sql1 .=
" SET fk_code_ventilation = 0";
81 $sql1 .=
' WHERE erd.fk_code_ventilation NOT IN';
82 $sql1 .=
' (SELECT accnt.rowid ';
83 $sql1 .=
' FROM '.MAIN_DB_PREFIX.
'accounting_account as accnt';
84 $sql1 .=
' INNER JOIN '.MAIN_DB_PREFIX.
'accounting_system as syst';
85 $sql1 .=
' ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid='.((int) $conf->global->CHARTOFACCOUNTS).
' AND accnt.entity = '.((int) $conf->entity).
')';
86 $sql1 .=
' AND erd.fk_expensereport IN (SELECT rowid FROM '.MAIN_DB_PREFIX.
'expensereport WHERE entity = '.((int) $conf->entity).
')';
87 $sql1 .=
' AND fk_code_ventilation <> 0';
88 dol_syslog(
"htdocs/accountancy/customer/index.php fixaccountancycode", LOG_DEBUG);
89 $resql1 = $db->query($sql1);
100 if ($action ==
'validatehistory') {
108 $sql1 =
"SELECT erd.rowid, accnt.rowid as suggestedid";
109 $sql1 .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
110 $sql1 .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"c_type_fees as t ON erd.fk_c_type_fees = t.id";
111 $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);
112 $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,';
113 $sql1 .=
" ".MAIN_DB_PREFIX.
"expensereport as er";
114 $sql1 .=
" WHERE erd.fk_expensereport = er.rowid AND er.entity = ".((int) $conf->entity);
115 $sql1 .=
" AND er.fk_statut IN (".ExpenseReport::STATUS_APPROVED.
", ".
ExpenseReport::STATUS_CLOSED.
") AND erd.fk_code_ventilation <= 0";
116 if ($validatemonth && $validateyear) {
120 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
122 $result = $db->query($sql1);
127 $num_lines = $db->num_rows($result);
130 while ($i < min($num_lines, 10000)) {
131 $objp = $db->fetch_object($result);
133 $lineid = $objp->rowid;
134 $suggestedid = $objp->suggestedid;
136 if ($suggestedid > 0) {
137 $sqlupdate =
"UPDATE ".MAIN_DB_PREFIX.
"expensereport_det";
138 $sqlupdate .=
" SET fk_code_ventilation = ".((int) $suggestedid);
139 $sqlupdate .=
" WHERE fk_code_ventilation <= 0 AND rowid = ".((int) $lineid);
141 $resqlupdate = $db->query($sqlupdate);
155 if ($num_lines > 10000) {
156 $notpossible += ($num_lines - 10000);
164 setEventMessages($langs->trans(
'AutomaticBindingDone', $nbbinddone, $notpossible),
null,
'mesgs');
173 llxHeader(
'', $langs->trans(
"ExpenseReportsVentilation"));
175 $textprevyear =
'<a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current - 1).
'">'.
img_previous().
'</a>';
176 $textnextyear =
' <a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current + 1).
'">'.
img_next().
'</a>';
178 print
load_fiche_titre($langs->trans(
"ExpenseReportsVentilation").
" ".$textprevyear.
" ".$langs->trans(
"Year").
" ".$year_start.
" ".$textnextyear,
'',
'title_accountancy');
180 print
'<span class="opacitymedium">'.$langs->trans(
"DescVentilExpenseReport").
'</span><br>';
181 print
'<span class="opacitymedium hideonsmartphone">'.$langs->trans(
"DescVentilExpenseReportMore", $langs->transnoentitiesnoconv(
"ValidateHistory"), $langs->transnoentitiesnoconv(
"ToBind")).
'<br>';
187 $buttonbind =
'<a class="butAction" href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&token='.
newToken().
'&year='.$year_current.
'">'.$langs->trans(
"ValidateHistory").
'</a>';
190 print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0, $buttonbind,
'', 0, 1, 1);
193 print
'<div class="div-table-responsive-no-min">';
194 print
'<table class="noborder centpercent">';
195 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
196 print
'<td>'.$langs->trans(
"Label").
'</td>';
197 for ($i = 1; $i <= 12; $i++) {
198 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
203 if ($cursormonth > 12) {
206 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
209 print
'<td width="60" class="right">';
210 if (!empty($tmp[
'mday'])) {
211 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
212 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
213 $param .=
'&search_month='.$tmp[
'mon'].
'&search_year='.$tmp[
'year'];
214 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/expensereport/list.php?'.$param.
'">';
216 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
217 if (!empty($tmp[
'mday'])) {
222 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
224 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
225 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
226 for ($i = 1; $i <= 12; $i++) {
227 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
231 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_debut)=".$j,
"erd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
233 $sql .=
" SUM(erd.total_ht) as total";
234 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
235 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
236 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
237 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
238 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
240 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
241 $sql .=
" AND er.date_debut >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
244 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
245 $sql .=
" AND aa.account_number IS NULL";
246 $sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
247 $sql .=
' ORDER BY aa.account_number';
249 dol_syslog(
'/accountancy/expensereport/index.php:: sql='.$sql);
250 $resql = $db->query($sql);
252 $num = $db->num_rows(
$resql);
254 while ($row = $db->fetch_row(
$resql)) {
255 print
'<tr class="oddeven">';
257 if ($row[0] ==
'tobind') {
258 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
264 if ($row[0] ==
'tobind') {
265 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/expensereport/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
270 for ($i = 2; $i <= 13; $i++) {
271 print
'<td class="right nowraponall amount">';
272 print
price($row[$i]);
275 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
280 print $db->lasterror();
289 print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
293 print
'<div class="div-table-responsive-no-min">';
294 print
'<table class="noborder centpercent">';
295 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
296 print
'<td>'.$langs->trans(
"Label").
'</td>';
297 for ($i = 1; $i <= 12; $i++) {
298 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
302 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
304 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
306 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
307 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
308 for ($i = 1; $i <= 12; $i++) {
309 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
313 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_debut)=".$j,
"erd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
315 $sql .=
" ROUND(SUM(erd.total_ht),2) as total";
316 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
317 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
318 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = erd.fk_code_ventilation";
319 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
320 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
322 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
323 $sql .=
" AND er.date_debut >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
326 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
327 $sql .=
" AND aa.account_number IS NOT NULL";
328 $sql .=
" GROUP BY erd.fk_code_ventilation,aa.account_number,aa.label";
330 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
331 $resql = $db->query($sql);
333 $num = $db->num_rows(
$resql);
335 while ($row = $db->fetch_row(
$resql)) {
336 print
'<tr class="oddeven">';
338 if ($row[0] ==
'tobind') {
339 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
346 if ($row[0] ==
'tobind') {
347 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/expensereport/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
352 for ($i = 2; $i <= 13; $i++) {
353 print
'<td class="right nowraponall amount">';
354 print
price($row[$i]);
357 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
362 print $db->lasterror();
369 if ($conf->global->MAIN_FEATURES_LEVEL > 0) {
373 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
376 print
'<div class="div-table-responsive-no-min">';
377 print
'<table class="noborder centpercent">';
378 print
'<tr class="liste_titre"><td class="left">'.$langs->trans(
"Total").
'</td>';
379 for ($i = 1; $i <= 12; $i++) {
380 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
384 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
386 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
388 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalExpenseReport")).
"' AS label,";
389 for ($i = 1; $i <= 12; $i++) {
390 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
394 $sql .=
" SUM(".$db->ifsql(
"MONTH(er.date_create)=".$j,
"erd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
396 $sql .=
" SUM(erd.total_ht) as total";
397 $sql .=
" FROM ".MAIN_DB_PREFIX.
"expensereport_det as erd";
398 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"expensereport as er ON er.rowid = erd.fk_expensereport";
399 $sql .=
" WHERE er.date_debut >= '".$db->idate($search_date_start).
"'";
400 $sql .=
" AND er.date_debut <= '".$db->idate($search_date_end).
"'";
402 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
403 $sql .=
" AND er.date_debut >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
406 $sql .=
" AND er.entity IN (".getEntity(
'expensereport', 0).
")";
408 dol_syslog(
'htdocs/accountancy/expensereport/index.php');
409 $resql = $db->query($sql);
411 $num = $db->num_rows(
$resql);
413 while ($row = $db->fetch_row(
$resql)) {
414 print
'<tr><td>'.$row[0].
'</td>';
415 for ($i = 1; $i <= 12; $i++) {
416 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
418 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
424 print $db->lasterror();