dolibarr 20.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-2021 Florian Henry <florian.henry@open-concept.pro>
4 * Copyright (C) 2013-2022 Alexandre Spangaro <aspangaro@easya.solutions>
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 <https://www.gnu.org/licenses/>.
19 */
20
27// Load Dolibarr environment
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.'/core/lib/company.lib.php';
32require_once DOL_DOCUMENT_ROOT.'/fourn/class/fournisseur.facture.class.php';
33require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
34
35// Load translation files required by the page
36$langs->loadLangs(array("compta", "bills", "other", "accountancy"));
37
38$validatemonth = GETPOSTINT('validatemonth');
39$validateyear = GETPOSTINT('validateyear');
40
41$accountingAccount = new AccountingAccount($db);
42
43$month_start = getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1);
44if (GETPOSTINT("year")) {
45 $year_start = GETPOSTINT("year");
46} else {
47 $year_start = dol_print_date(dol_now(), '%Y');
48 if (dol_print_date(dol_now(), '%m') < $month_start) {
49 $year_start--; // If current month is lower that starting fiscal month, we start last year
50 }
51}
52$year_end = $year_start + 1;
53$month_end = $month_start - 1;
54if ($month_end < 1) {
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$chartaccountcode = dol_getIdFromCode($db, getDolGlobalInt('CHARTOFACCOUNTS'), 'accounting_system', 'rowid', 'pcg_version');
66
67// Security check
68if (!isModEnabled('accounting')) {
70}
71if ($user->socid > 0) {
73}
74if (!$user->hasRight('accounting', 'bind', 'write')) {
76}
77
78
79/*
80 * Actions
81 */
82
83if (($action == 'clean' || $action == 'validatehistory') && $user->hasRight('accounting', 'bind', 'write')) {
84 // Clean database by removing binding done on non existing or no more existing accounts
85 $db->begin();
86 $sql1 = "UPDATE ".$db->prefix()."facture_fourn_det as fd";
87 $sql1 .= " SET fk_code_ventilation = 0";
88 $sql1 .= ' WHERE fd.fk_code_ventilation NOT IN';
89 $sql1 .= ' (SELECT accnt.rowid ';
90 $sql1 .= " FROM ".$db->prefix()."accounting_account as accnt";
91 $sql1 .= " INNER JOIN ".$db->prefix()."accounting_system as syst";
92 $sql1 .= " ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid = ".getDolGlobalInt('CHARTOFACCOUNTS')." AND accnt.entity = ".((int) $conf->entity).")";
93 $sql1 .= " AND fd.fk_facture_fourn IN (SELECT rowid FROM ".$db->prefix()."facture_fourn WHERE entity = ".((int) $conf->entity).")";
94 $sql1 .= " AND fk_code_ventilation <> 0";
95
96 dol_syslog("htdocs/accountancy/customer/index.php fixaccountancycode", LOG_DEBUG);
97 $resql1 = $db->query($sql1);
98 if (!$resql1) {
99 $error++;
100 $db->rollback();
101 setEventMessages($db->lasterror(), null, 'errors');
102 } else {
103 $db->commit();
104 }
105 // End clean database
106}
107
108if ($action == 'validatehistory' && $user->hasRight('accounting', 'bind', 'write')) {
109 $error = 0;
110 $nbbinddone = 0;
111 $nbbindfailed = 0;
112 $notpossible = 0;
113
114 $db->begin();
115
116 // Now make the binding. Bind automatically only for product with a dedicated account that exists into chart of account, others need a manual bind
117 // Supplier Invoice Lines (must be same request than into page list.php for manual binding)
118 $sql = "SELECT f.rowid as facid, f.ref, f.ref_supplier, f.libelle as invoice_label, f.datef, f.type as ftype, f.fk_facture_source,";
119 $sql .= " l.rowid, l.fk_product, l.description, l.total_ht, l.fk_code_ventilation, l.product_type as type_l, l.tva_tx as tva_tx_line, l.vat_src_code,";
120 $sql .= " p.rowid as product_id, p.ref as product_ref, p.label as product_label, p.fk_product_type as type, p.tva_tx as tva_tx_prod,";
121 if (getDolGlobalString('MAIN_PRODUCT_PERENTITY_SHARED')) {
122 $sql .= " ppe.accountancy_code_buy as code_buy, ppe.accountancy_code_buy_intra as code_buy_intra, ppe.accountancy_code_buy_export as code_buy_export,";
123 } else {
124 $sql .= " p.accountancy_code_buy as code_buy, p.accountancy_code_buy_intra as code_buy_intra, p.accountancy_code_buy_export as code_buy_export,";
125 }
126 $sql .= " aa.rowid as aarowid, aa2.rowid as aarowid_intra, aa3.rowid as aarowid_export, aa4.rowid as aarowid_thirdparty,";
127 $sql .= " co.code as country_code, co.label as country_label,";
128 $sql .= " s.tva_intra,";
129 if (getDolGlobalString('MAIN_COMPANY_PERENTITY_SHARED')) {
130 $sql .= " spe.accountancy_code_buy as company_code_buy";
131 } else {
132 $sql .= " s.accountancy_code_buy as company_code_buy";
133 }
134 $sql .= " FROM ".$db->prefix()."facture_fourn as f";
135 $sql .= " INNER JOIN ".$db->prefix()."societe as s ON s.rowid = f.fk_soc";
136 if (getDolGlobalString('MAIN_COMPANY_PERENTITY_SHARED')) {
137 $sql .= " LEFT JOIN " . $db->prefix() . "societe_perentity as spe ON spe.fk_soc = s.rowid AND spe.entity = " . ((int) $conf->entity);
138 }
139 $sql .= " LEFT JOIN ".$db->prefix()."c_country as co ON co.rowid = s.fk_pays ";
140 $sql .= " INNER JOIN ".$db->prefix()."facture_fourn_det as l ON f.rowid = l.fk_facture_fourn";
141 $sql .= " LEFT JOIN ".$db->prefix()."product as p ON p.rowid = l.fk_product";
142 if (getDolGlobalString('MAIN_PRODUCT_PERENTITY_SHARED')) {
143 $sql .= " LEFT JOIN " . $db->prefix() . "product_perentity as ppe ON ppe.fk_product = p.rowid AND ppe.entity = " . ((int) $conf->entity);
144 }
145 $alias_societe_perentity = !getDolGlobalString('MAIN_COMPANY_PERENTITY_SHARED') ? "s" : "spe";
146 $alias_product_perentity = !getDolGlobalString('MAIN_PRODUCT_PERENTITY_SHARED') ? "p" : "ppe";
147 $sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa ON " . $alias_product_perentity . ".accountancy_code_buy = aa.account_number AND aa.active = 1 AND aa.fk_pcg_version = '".$db->escape($chartaccountcode)."' AND aa.entity = ".$conf->entity;
148 $sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa2 ON " . $alias_product_perentity . ".accountancy_code_buy_intra = aa2.account_number AND aa2.active = 1 AND aa2.fk_pcg_version = '".$db->escape($chartaccountcode)."' AND aa2.entity = ".$conf->entity;
149 $sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa3 ON " . $alias_product_perentity . ".accountancy_code_buy_export = aa3.account_number AND aa3.active = 1 AND aa3.fk_pcg_version = '".$db->escape($chartaccountcode)."' AND aa3.entity = ".$conf->entity;
150 $sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa4 ON " . $alias_product_perentity . ".accountancy_code_buy = aa4.account_number AND aa4.active = 1 AND aa4.fk_pcg_version = '".$db->escape($chartaccountcode)."' AND aa4.entity = ".$conf->entity;
151 $sql .= " WHERE f.fk_statut > 0 AND l.fk_code_ventilation <= 0";
152 $sql .= " AND l.product_type <= 2";
153 $sql .= " AND f.entity IN (".getEntity('facture_fourn', 0).")"; // We don't share object for accountancy
154 if (getDolGlobalString('ACCOUNTING_DATE_START_BINDING')) {
155 $sql .= " AND f.datef >= '".$db->idate(getDolGlobalString('ACCOUNTING_DATE_START_BINDING'))."'";
156 }
157 if ($validatemonth && $validateyear) {
158 $sql .= dolSqlDateFilter('f.datef', 0, $validatemonth, $validateyear);
159 }
160
161 dol_syslog('htdocs/accountancy/supplier/index.php');
162
163 $result = $db->query($sql);
164 if (!$result) {
165 $error++;
166 setEventMessages($db->lasterror(), null, 'errors');
167 } else {
168 $num_lines = $db->num_rows($result);
169
170 $isBuyerInEEC = isInEEC($mysoc);
171
172 $thirdpartystatic = new Societe($db);
173 $facture_static = new FactureFournisseur($db);
174 $facture_static_det = new SupplierInvoiceLine($db);
175 $product_static = new Product($db);
176
177 $i = 0;
178 while ($i < min($num_lines, 10000)) { // No more than 10000 at once
179 $objp = $db->fetch_object($result);
180
181 $thirdpartystatic->id = !empty($objp->socid) ? $objp->socid : 0;
182 $thirdpartystatic->name = !empty($objp->name) ? $objp->name : "";
183 $thirdpartystatic->client = !empty($objp->client) ? $objp->client : "";
184 $thirdpartystatic->fournisseur = !empty($objp->fournisseur) ? $objp->fournisseur : "";
185 $thirdpartystatic->code_client = !empty($objp->code_client) ? $objp->code_client : "";
186 $thirdpartystatic->code_compta_client = !empty($objp->code_compta_client) ? $objp->code_compta_client : "";
187 $thirdpartystatic->code_fournisseur = !empty($objp->code_fournisseur) ? $objp->code_fournisseur : "";
188 $thirdpartystatic->code_compta_fournisseur = !empty($objp->code_compta_fournisseur) ? $objp->code_compta_fournisseur : "";
189 $thirdpartystatic->email = !empty($objp->email) ? $objp->email : "";
190 $thirdpartystatic->country_code = !empty($objp->country_code) ? $objp->country_code : "";
191 $thirdpartystatic->tva_intra = !empty($objp->tva_intra) ? $objp->tva_intra : "";
192 $thirdpartystatic->code_compta_product = $objp->company_code_buy; // The accounting account for product stored on thirdparty object (for level3 suggestion)
193
194 $product_static->ref = $objp->product_ref;
195 $product_static->id = $objp->product_id;
196 $product_static->type = $objp->type;
197 $product_static->label = $objp->product_label;
198 $product_static->status = !empty($objp->status) ? $objp->status : 0;
199 $product_static->status_buy = !empty($objp->status_buy) ? $objp->status_buy : 0;
200 $product_static->accountancy_code_sell = $objp->code_sell;
201 $product_static->accountancy_code_sell_intra = $objp->code_sell_intra;
202 $product_static->accountancy_code_sell_export = $objp->code_sell_export;
203 $product_static->accountancy_code_buy = !empty($objp->code_buy) ? $objp->code_buy : "";
204 $product_static->accountancy_code_buy_intra = !empty($objp->code_buy_intra) ? $objp->code_buy_intra : "";
205 $product_static->accountancy_code_buy_export = !empty($objp->code_buy_export) ? $objp->code_buy_export : "";
206 $product_static->tva_tx = $objp->tva_tx_prod;
207
208 $facture_static->ref = $objp->ref;
209 $facture_static->id = $objp->facid;
210 $facture_static->type = $objp->ftype;
211 $facture_static->ref_supplier = $objp->ref_supplier;
212 $facture_static->label = $objp->invoice_label;
213 $facture_static->date = $db->jdate($objp->datef);
214 $facture_static->fk_facture_source = $objp->fk_facture_source;
215
216 $facture_static_det->id = $objp->rowid;
217 $facture_static_det->total_ht = $objp->total_ht;
218 $facture_static_det->tva_tx = $objp->tva_tx_line;
219 $facture_static_det->vat_src_code = $objp->vat_src_code;
220 $facture_static_det->product_type = $objp->type_l;
221 $facture_static_det->desc = $objp->description;
222
223 $accountingAccountArray = array(
224 'dom' => $objp->aarowid,
225 'intra' => $objp->aarowid_intra,
226 'export' => $objp->aarowid_export,
227 'thirdparty' => $objp->aarowid_thirdparty);
228
229 $code_buy_p_notset = '';
230 $code_buy_t_notset = '';
231
232 $suggestedid = 0;
233
234 $return = $accountingAccount->getAccountingCodeToBind($mysoc, $thirdpartystatic, $product_static, $facture_static, $facture_static_det, $accountingAccountArray, 'supplier');
235 if (!is_array($return) && $return < 0) {
236 setEventMessage($accountingAccount->error, 'errors');
237 } else {
238 $suggestedid = $return['suggestedid'];
239 $suggestedaccountingaccountfor = $return['suggestedaccountingaccountfor'];
240
241 if (!empty($suggestedid) && $suggestedaccountingaccountfor != '' && $suggestedaccountingaccountfor != 'eecwithoutvatnumber') {
242 $suggestedid = $return['suggestedid'];
243 } else {
244 $suggestedid = 0;
245 }
246 }
247
248 if ($suggestedid > 0) {
249 $sqlupdate = "UPDATE ".$db->prefix()."facture_fourn_det";
250 $sqlupdate .= " SET fk_code_ventilation = ".((int) $suggestedid);
251 $sqlupdate .= " WHERE fk_code_ventilation <= 0 AND product_type <= 2 AND rowid = ".((int) $facture_static_det->id);
252
253 $resqlupdate = $db->query($sqlupdate);
254 if (!$resqlupdate) {
255 $error++;
256 setEventMessages($db->lasterror(), null, 'errors');
257 $nbbindfailed++;
258 break;
259 } else {
260 $nbbinddone++;
261 }
262 } else {
263 $notpossible++;
264 $nbbindfailed++;
265 }
266
267 $i++;
268 }
269 if ($num_lines > 10000) {
270 $notpossible += ($num_lines - 10000);
271 }
272 }
273
274 if ($error) {
275 $db->rollback();
276 } else {
277 $db->commit();
278 setEventMessages($langs->trans('AutomaticBindingDone', $nbbinddone, $notpossible), null, ($notpossible ? 'warnings' : 'mesgs'));
279 if ($nbbindfailed) {
280 setEventMessages($langs->trans('DoManualBindingForFailedRecord', $nbbindfailed), null, 'warnings');
281 }
282 }
283}
284
285
286/*
287 * View
288 */
289$help_url = 'EN:Module_Double_Entry_Accounting|FR:Module_Comptabilit&eacute;_en_Partie_Double#Liaisons_comptables';
290
291llxHeader('', $langs->trans("SuppliersVentilation"), $help_url, '', 0, 0, '', '', '', 'mod-accountancy accountancy-supplier page-index');
292
293$textprevyear = '<a href="'.$_SERVER["PHP_SELF"].'?year='.($year_current - 1).'">'.img_previous().'</a>';
294$textnextyear = '&nbsp;<a href="'.$_SERVER["PHP_SELF"].'?year='.($year_current + 1).'">'.img_next().'</a>';
295
296print load_fiche_titre($langs->trans("SuppliersVentilation")." ".$textprevyear."&nbsp;".$langs->trans("Year")."&nbsp;".$year_start."&nbsp;".$textnextyear, '', 'title_accountancy');
297
298print '<span class="opacitymedium">'.$langs->trans("DescVentilSupplier").'</span><br>';
299print '<span class="opacitymedium hideonsmartphone">'.$langs->trans("DescVentilMore", $langs->transnoentitiesnoconv("ValidateHistory"), $langs->transnoentitiesnoconv("ToBind")).'<br>';
300print '</span><br>';
301
302$y = $year_current;
303
304$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>';
305
306
307print_barre_liste(img_picto('', 'unlink', 'class="paddingright fa-color-unset"').$langs->trans("OverviewOfAmountOfLinesNotBound"), '', '', '', '', '', '', -1, '', '', 0, '', '', 0, 1, 1, 0, $buttonbind);
308//print load_fiche_titre($langs->trans("OverviewOfAmountOfLinesNotBound"), $buttonbind, '');
309
310print '<div class="div-table-responsive-no-min">';
311print '<table class="noborder centpercent">';
312print '<tr class="liste_titre"><td class="minwidth100">'.$langs->trans("Account").'</td>';
313for ($i = 1; $i <= 12; $i++) {
314 $j = $i + getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) - 1;
315 if ($j > 12) {
316 $j -= 12;
317 }
318 $cursormonth = $j;
319 if ($cursormonth > 12) {
320 $cursormonth -= 12;
321 }
322 $cursoryear = ($cursormonth < getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
323 $tmp = dol_getdate(dol_get_last_day($cursoryear, $cursormonth, 'gmt'), false, 'gmt');
324
325 print '<td width="60" class="right">';
326 if (!empty($tmp['mday'])) {
327 $param = 'search_date_startday=1&search_date_startmonth='.$cursormonth.'&search_date_startyear='.$cursoryear;
328 $param .= '&search_date_endday='.$tmp['mday'].'&search_date_endmonth='.$tmp['mon'].'&search_date_endyear='.$tmp['year'];
329 print '<a href="'.DOL_URL_ROOT.'/accountancy/supplier/list.php?'.$param.'">';
330 }
331 print $langs->trans('MonthShort'.str_pad((string) $j, 2, '0', STR_PAD_LEFT));
332 if (!empty($tmp['mday'])) {
333 print '</a>';
334 }
335 print '</td>';
336}
337print '<td width="60" class="right"><b>'.$langs->trans("Total").'</b></td></tr>';
338
339$sql = "SELECT ".$db->ifsql('aa.account_number IS NULL', "'tobind'", 'aa.account_number')." AS codecomptable,";
340$sql .= " ".$db->ifsql('aa.label IS NULL', "'tobind'", 'aa.label')." AS intitule,";
341for ($i = 1; $i <= 12; $i++) {
342 $j = $i + getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) - 1;
343 if ($j > 12) {
344 $j -= 12;
345 }
346 $sql .= " SUM(".$db->ifsql("MONTH(ff.datef) = ".((int) $j), "ffd.total_ht", "0").") AS month".str_pad((string) $j, 2, "0", STR_PAD_LEFT).",";
347 $sql .= " SUM(".$db->ifsql("MONTH(ff.datef) = ".((string) $j), "1", "0").") AS nbmonth".str_pad((string) $j, 2, "0", STR_PAD_LEFT).",";
348}
349$sql .= " SUM(ffd.total_ht) as total, COUNT(ffd.rowid) as nb";
350$sql .= " FROM ".$db->prefix()."facture_fourn_det as ffd";
351$sql .= " LEFT JOIN ".$db->prefix()."facture_fourn as ff ON ff.rowid = ffd.fk_facture_fourn";
352$sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa ON aa.rowid = ffd.fk_code_ventilation";
353$sql .= " WHERE ff.datef >= '".$db->idate($search_date_start)."'";
354$sql .= " AND ff.datef <= '".$db->idate($search_date_end)."'";
355// Define begin binding date
356if (getDolGlobalString('ACCOUNTING_DATE_START_BINDING')) {
357 $sql .= " AND ff.datef >= '".$db->idate(getDolGlobalString('ACCOUNTING_DATE_START_BINDING'))."'";
358}
359$sql .= " AND ff.fk_statut > 0";
360$sql .= " AND ffd.product_type <= 2";
361$sql .= " AND ff.entity IN (".getEntity('facture_fourn', 0).")"; // We don't share object for accountancy
362$sql .= " AND aa.account_number IS NULL";
363if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
364 $sql .= " AND ff.type IN (".FactureFournisseur::TYPE_STANDARD.",".FactureFournisseur::TYPE_REPLACEMENT.",".FactureFournisseur::TYPE_CREDIT_NOTE.")";
365} else {
366 $sql .= " AND ff.type IN (".FactureFournisseur::TYPE_STANDARD.",".FactureFournisseur::TYPE_REPLACEMENT.",".FactureFournisseur::TYPE_CREDIT_NOTE.",".FactureFournisseur::TYPE_DEPOSIT.")";
367}
368$sql .= " GROUP BY ffd.fk_code_ventilation,aa.account_number,aa.label";
369
370dol_syslog('htdocs/accountancy/supplier/index.php', LOG_DEBUG);
371$resql = $db->query($sql);
372if ($resql) {
373 $num = $db->num_rows($resql);
374
375 while ($row = $db->fetch_row($resql)) {
376 print '<tr class="oddeven">';
377 print '<td>';
378 if ($row[0] == 'tobind') {
379 //print '<span class="opacitymedium">'.$langs->trans("Unknown").'</span>';
380 } else {
381 print length_accountg($row[0]).' - ';
382 }
383 //print '</td>';
384 //print '<td>';
385 if ($row[0] == 'tobind') {
386 $startmonth = getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1);
387 if ($startmonth > 12) {
388 $startmonth -= 12;
389 }
390 $startyear = ($startmonth < getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
391 $endmonth = getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) + 11;
392 if ($endmonth > 12) {
393 $endmonth -= 12;
394 }
395 $endyear = ($endmonth < getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
396 print $langs->trans("UseMenuToSetBindindManualy", DOL_URL_ROOT.'/accountancy/supplier/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"));
397 } else {
398 print $row[1];
399 }
400 print '</td>';
401 for ($i = 2; $i <= 13; $i++) {
402 $cursormonth = (getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) + $i - 2);
403 if ($cursormonth > 12) {
404 $cursormonth -= 12;
405 }
406 $cursoryear = ($cursormonth < getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
407 $tmp = dol_getdate(dol_get_last_day($cursoryear, $cursormonth, 'gmt'), false, 'gmt');
408
409 print '<td class="right nowraponall amount" title="'.price($row[2*$i - 2]).' - '.$row[2*$i - 1].' lines">';
410 print price($row[2*$i - 2]);
411 // Add link to make binding
412 if (!empty(price2num($row[2*$i - 2])) || !empty($row[2*$i - 1])) {
413 print '<a href="'.$_SERVER['PHP_SELF'].'?action=validatehistory&year='.$y.'&validatemonth='.((int) $cursormonth).'&validateyear='.((int) $cursoryear).'&token='.newToken().'">';
414 print img_picto($langs->trans("ValidateHistory").' ('.$langs->trans('Month'.str_pad((string) $cursormonth, 2, '0', STR_PAD_LEFT)).' '.$cursoryear.')', 'link', 'class="marginleft2"');
415 print '</a>';
416 }
417 print '</td>';
418 }
419 print '<td class="right nowraponall amount"><b>'.price($row[26]).'</b></td>';
420 print '</tr>';
421 }
422 $db->free($resql);
423
424 if ($num == 0) {
425 print '<tr class="oddeven"><td colspan="15">';
426 print '<span class="opacitymedium">'.$langs->trans("NoRecordFound").'</span>';
427 print '</td></tr>';
428 }
429} else {
430 print $db->lasterror(); // Show last sql error
431}
432print "</table>\n";
433print '</div>';
434
435
436print '<br>';
437
438
439print_barre_liste(img_picto('', 'link', 'class="paddingright fa-color-unset"').$langs->trans("OverviewOfAmountOfLinesBound"), '', '', '', '', '', '', -1, '', '', 0, '', '', 0, 1, 1);
440//print load_fiche_titre($langs->trans("OverviewOfAmountOfLinesBound"), '', '');
441
442print '<div class="div-table-responsive-no-min">';
443print '<table class="noborder centpercent">';
444print '<tr class="liste_titre"><td class="minwidth100">'.$langs->trans("Account").'</td>';
445for ($i = 1; $i <= 12; $i++) {
446 $j = $i + getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) - 1;
447 if ($j > 12) {
448 $j -= 12;
449 }
450 $cursormonth = $j;
451 if ($cursormonth > 12) {
452 $cursormonth -= 12;
453 }
454 $cursoryear = ($cursormonth < getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
455 $tmp = dol_getdate(dol_get_last_day($cursoryear, $cursormonth, 'gmt'), false, 'gmt');
456
457 print '<td width="60" class="right">';
458 if (!empty($tmp['mday'])) {
459 $param = 'search_date_startday=1&search_date_startmonth='.$cursormonth.'&search_date_startyear='.$cursoryear;
460 $param .= '&search_date_endday='.$tmp['mday'].'&search_date_endmonth='.$tmp['mon'].'&search_date_endyear='.$tmp['year'];
461 print '<a href="'.DOL_URL_ROOT.'/accountancy/supplier/lines.php?'.$param.'">';
462 }
463 print $langs->trans('MonthShort'.str_pad((string) $j, 2, '0', STR_PAD_LEFT));
464 if (!empty($tmp['mday'])) {
465 print '</a>';
466 }
467 print '</td>';
468}
469print '<td width="60" class="right"><b>'.$langs->trans("Total").'</b></td></tr>';
470
471$sql = "SELECT ".$db->ifsql('aa.account_number IS NULL', "'tobind'", 'aa.account_number')." AS codecomptable,";
472$sql .= " ".$db->ifsql('aa.label IS NULL', "'tobind'", 'aa.label')." AS intitule,";
473for ($i = 1; $i <= 12; $i++) {
474 $j = $i + getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) - 1;
475 if ($j > 12) {
476 $j -= 12;
477 }
478 $sql .= " SUM(".$db->ifsql("MONTH(ff.datef) = ".((int) $j), "ffd.total_ht", "0").") AS month".str_pad((string) $j, 2, "0", STR_PAD_LEFT).",";
479}
480$sql .= " SUM(ffd.total_ht) as total";
481$sql .= " FROM ".$db->prefix()."facture_fourn_det as ffd";
482$sql .= " LEFT JOIN ".$db->prefix()."facture_fourn as ff ON ff.rowid = ffd.fk_facture_fourn";
483$sql .= " LEFT JOIN ".$db->prefix()."accounting_account as aa ON aa.rowid = ffd.fk_code_ventilation";
484$sql .= " WHERE ff.datef >= '".$db->idate($search_date_start)."'";
485$sql .= " AND ff.datef <= '".$db->idate($search_date_end)."'";
486// Define begin binding date
487if (getDolGlobalString('ACCOUNTING_DATE_START_BINDING')) {
488 $sql .= " AND ff.datef >= '".$db->idate(getDolGlobalString('ACCOUNTING_DATE_START_BINDING'))."'";
489}
490$sql .= " AND ff.entity IN (".getEntity('facture_fourn', 0).")"; // We don't share object for accountancy
491$sql .= " AND ff.fk_statut > 0";
492$sql .= " AND ffd.product_type <= 2";
493if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
494 $sql .= " AND ff.type IN (".FactureFournisseur::TYPE_STANDARD.", ".FactureFournisseur::TYPE_REPLACEMENT.", ".FactureFournisseur::TYPE_CREDIT_NOTE.")";
495} else {
496 $sql .= " AND ff.type IN (".FactureFournisseur::TYPE_STANDARD.", ".FactureFournisseur::TYPE_REPLACEMENT.", ".FactureFournisseur::TYPE_CREDIT_NOTE.", ".FactureFournisseur::TYPE_DEPOSIT.")";
497}
498$sql .= " AND aa.account_number IS NOT NULL";
499$sql .= " GROUP BY ffd.fk_code_ventilation,aa.account_number,aa.label";
500$sql .= ' ORDER BY aa.account_number';
501
502dol_syslog('htdocs/accountancy/supplier/index.php');
503$resql = $db->query($sql);
504if ($resql) {
505 $num = $db->num_rows($resql);
506
507 while ($row = $db->fetch_row($resql)) {
508 print '<tr class="oddeven">';
509 print '<td class="tdoverflowmax300"'.(empty($row[1]) ? '' : ' title="'.dol_escape_htmltag($row[1]).'"').'>';
510 if ($row[0] == 'tobind') {
511 //print $langs->trans("Unknown");
512 } else {
513 print length_accountg($row[0]).' - ';
514 }
515 //print '</td>';
516
517 //print '<td class="tdoverflowmax300"'.(empty($row[1]) ? '' : ' title="'.dol_escape_htmltag($row[1]).'"').'>';
518 if ($row[0] == 'tobind') {
519 print $langs->trans("UseMenuToSetBindindManualy", DOL_URL_ROOT.'/accountancy/supplier/list.php?search_year='.((int) $y), $langs->transnoentitiesnoconv("ToBind"));
520 } else {
521 print dol_escape_htmltag($row[1]);
522 }
523 print '</td>';
524
525 for ($i = 2; $i <= 13; $i++) {
526 print '<td class="right nowraponall amount">';
527 print price($row[$i]);
528 print '</td>';
529 }
530 print '<td class="right nowraponall amount"><b>'.price($row[14]).'</b></td>';
531 print '</tr>';
532 }
533 $db->free($resql);
534
535 if ($num == 0) {
536 print '<tr class="oddeven"><td colspan="15">';
537 print '<span class="opacitymedium">'.$langs->trans("NoRecordFound").'</span>';
538 print '</td></tr>';
539 }
540} else {
541 print $db->lasterror(); // Show last sql error
542}
543print "</table>\n";
544print '</div>';
545
546
547if (getDolGlobalString('SHOW_TOTAL_OF_PREVIOUS_LISTS_IN_LIN_PAGE')) { // This part of code looks strange. Why showing a report that should rely on result of this step ?
548 print '<br>';
549 print '<br>';
550
551 print_barre_liste($langs->trans("OtherInfo"), '', '', '', '', '', '', -1, '', '', 0, '', '', 0, 1, 1);
552 //print load_fiche_titre($langs->trans("OtherInfo"), '', '');
553
554 print '<div class="div-table-responsive-no-min">';
555 print '<table class="noborder centpercent">';
556 print '<tr class="liste_titre"><td>'.$langs->trans("Total").'</td>';
557 for ($i = 1; $i <= 12; $i++) {
558 $j = $i + getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) - 1;
559 if ($j > 12) {
560 $j -= 12;
561 }
562 print '<td width="60" class="right">'.$langs->trans('MonthShort'.str_pad((string) $j, 2, '0', STR_PAD_LEFT)).'</td>';
563 }
564 print '<td width="60" class="right"><b>'.$langs->trans("Total").'</b></td></tr>';
565
566 $sql = "SELECT '".$db->escape($langs->trans("CAHTF"))."' AS label,";
567 for ($i = 1; $i <= 12; $i++) {
568 $j = $i + getDolGlobalInt('SOCIETE_FISCAL_MONTH_START', 1) - 1;
569 if ($j > 12) {
570 $j -= 12;
571 }
572 $sql .= " SUM(".$db->ifsql("MONTH(ff.datef) = ".((int) $j), "ffd.total_ht", "0").") AS month".str_pad((string) $j, 2, "0", STR_PAD_LEFT).",";
573 }
574 $sql .= " SUM(ffd.total_ht) as total";
575 $sql .= " FROM ".$db->prefix()."facture_fourn_det as ffd";
576 $sql .= " LEFT JOIN ".$db->prefix()."facture_fourn as ff ON ff.rowid = ffd.fk_facture_fourn";
577 $sql .= " WHERE ff.datef >= '".$db->idate($search_date_start)."'";
578 $sql .= " AND ff.datef <= '".$db->idate($search_date_end)."'";
579 // Define begin binding date
580 if (getDolGlobalString('ACCOUNTING_DATE_START_BINDING')) {
581 $sql .= " AND ff.datef >= '".$db->idate(getDolGlobalString('ACCOUNTING_DATE_START_BINDING'))."'";
582 }
583 $sql .= " AND ff.entity IN (".getEntity('facture_fourn', 0).")"; // We don't share object for accountancy
584 $sql .= " AND ff.fk_statut > 0";
585 $sql .= " AND ffd.product_type <= 2";
586 if (getDolGlobalString('FACTURE_SUPPLIER_DEPOSITS_ARE_JUST_PAYMENTS')) {
587 $sql .= " AND ff.type IN (".FactureFournisseur::TYPE_STANDARD.", ".FactureFournisseur::TYPE_REPLACEMENT.", ".FactureFournisseur::TYPE_CREDIT_NOTE.")";
588 } else {
589 $sql .= " AND ff.type IN (".FactureFournisseur::TYPE_STANDARD.", ".FactureFournisseur::TYPE_REPLACEMENT.", ".FactureFournisseur::TYPE_CREDIT_NOTE.", ".FactureFournisseur::TYPE_DEPOSIT.")";
590 }
591
592 dol_syslog('htdocs/accountancy/supplier/index.php');
593 $resql = $db->query($sql);
594 if ($resql) {
595 $num = $db->num_rows($resql);
596
597 while ($row = $db->fetch_row($resql)) {
598 print '<tr><td>'.$row[0].'</td>';
599 for ($i = 1; $i <= 12; $i++) {
600 print '<td class="right nowraponall amount">'.price($row[$i]).'</td>';
601 }
602 print '<td class="right nowraponall amount"><b>'.price($row[13]).'</b></td>';
603 print '</tr>';
604 }
605 $db->free($resql);
606 } else {
607 print $db->lasterror(); // Show last sql error
608 }
609 print "</table>\n";
610 print '</div>';
611}
612
613// End of page
614llxFooter();
615$db->close();
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.
Definition wrapper.php:55
llxFooter()
Empty footer.
Definition wrapper.php:69
Class to manage accounting accounts.
Class to manage suppliers invoices.
const TYPE_DEPOSIT
Deposit invoice.
const TYPE_CREDIT_NOTE
Credit note invoice.
const TYPE_REPLACEMENT
Replacement invoice.
Class to manage products or services.
Class to manage third parties objects (customers, suppliers, prospects...)
Class to manage line invoices.
isInEEC($object)
Return if a country of an object is inside the EEC (European Economic Community)
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).
Definition date.lib.php:378
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition date.lib.php:614
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 '.
setEventMessage($mesgs, $style='mesgs', $noduplicate=0)
Set event message 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_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.
dol_escape_htmltag($stringtoescape, $keepb=0, $keepn=0, $noescapetags='', $escapeonlyhtmltags=0, $cleanalsojavascript=0)
Returns text escaped for inclusion in HTML alt or title or value tags, or into values of HTML input f...
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.