30 require
'../../main.inc.php';
31 require_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
32 require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
33 require_once DOL_DOCUMENT_ROOT.
'/core/lib/company.lib.php';
34 require_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
35 require_once DOL_DOCUMENT_ROOT.
'/accountancy/class/accountingaccount.class.php';
38 $langs->loadLangs(array(
"compta",
"bills",
"other",
"accountancy"));
40 $validatemonth =
GETPOST(
'validatemonth',
'int');
41 $validateyear =
GETPOST(
'validateyear',
'int');
47 if ($user->socid > 0) {
50 if (!$user->hasRight(
'accounting',
'bind',
'write')) {
56 $month_start = ($conf->global->SOCIETE_FISCAL_MONTH_START ? ($conf->global->SOCIETE_FISCAL_MONTH_START) : 1);
58 $year_start =
GETPOST(
"year",
'int');
65 $year_end = $year_start + 1;
66 $month_end = $month_start - 1;
71 $search_date_start =
dol_mktime(0, 0, 0, $month_start, 1, $year_start);
73 $year_current = $year_start;
76 $action =
GETPOST(
'action',
'aZ09');
78 $chartaccountcode =
dol_getIdFromCode($db, $conf->global->CHARTOFACCOUNTS,
'accounting_system',
'rowid',
'pcg_version');
84 if ($user->socid > 0) {
87 if (!$user->hasRight(
'accounting',
'mouvements',
'lire')) {
96 if (($action ==
'clean' || $action ==
'validatehistory') && $user->hasRight(
'accounting',
'bind',
'write')) {
99 $sql1 =
"UPDATE ".MAIN_DB_PREFIX.
"facturedet as fd";
100 $sql1 .=
" SET fk_code_ventilation = 0";
101 $sql1 .=
' WHERE fd.fk_code_ventilation NOT IN';
102 $sql1 .=
' (SELECT accnt.rowid ';
103 $sql1 .=
' FROM '.MAIN_DB_PREFIX.
'accounting_account as accnt';
104 $sql1 .=
' INNER JOIN '.MAIN_DB_PREFIX.
'accounting_system as syst';
105 $sql1 .=
' ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid='.((int) $conf->global->CHARTOFACCOUNTS).
' AND accnt.entity = '.((int) $conf->entity).
')';
106 $sql1 .=
' AND fd.fk_facture IN (SELECT rowid FROM '.MAIN_DB_PREFIX.
'facture WHERE entity = '.((int) $conf->entity).
')';
107 $sql1 .=
' AND fk_code_ventilation <> 0';
109 dol_syslog(
"htdocs/accountancy/customer/index.php fixaccountancycode", LOG_DEBUG);
110 $resql1 = $db->query($sql1);
121 if ($action ==
'validatehistory') {
145 $sql =
"SELECT f.rowid as facid, f.ref as ref, f.datef, f.type as ftype, f.situation_cycle_ref, f.fk_facture_source,";
146 $sql .=
" l.rowid, l.fk_product, l.description, l.total_ht, l.fk_code_ventilation, l.product_type as type_l, l.situation_percent, l.tva_tx as tva_tx_line, l.vat_src_code,";
147 $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,";
148 if (!empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED)) {
149 $sql .=
" ppe.accountancy_code_sell as code_sell, ppe.accountancy_code_sell_intra as code_sell_intra, ppe.accountancy_code_sell_export as code_sell_export,";
151 $sql .=
" p.accountancy_code_sell as code_sell, p.accountancy_code_sell_intra as code_sell_intra, p.accountancy_code_sell_export as code_sell_export,";
153 $sql .=
" aa.rowid as aarowid, aa2.rowid as aarowid_intra, aa3.rowid as aarowid_export, aa4.rowid as aarowid_thirdparty,";
154 $sql .=
" co.code as country_code, co.label as country_label,";
155 $sql .=
" s.tva_intra,";
156 if (!empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED)) {
157 $sql .=
" spe.accountancy_code_sell as company_code_sell";
159 $sql .=
" s.accountancy_code_sell as company_code_sell";
161 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture as f";
162 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
163 if (!empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED)) {
164 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"societe_perentity as spe ON spe.fk_soc = s.rowid AND spe.entity = " . ((int) $conf->entity);
166 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"c_country as co ON co.rowid = s.fk_pays ";
167 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"facturedet as l ON f.rowid = l.fk_facture";
168 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p ON p.rowid = l.fk_product";
169 if (!empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED)) {
170 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"product_perentity as ppe ON ppe.fk_product = p.rowid AND ppe.entity = " . ((int) $conf->entity);
172 $alias_societe_perentity = empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED) ?
"s" :
"spe";
173 $alias_product_perentity = empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED) ?
"p" :
"ppe";
174 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON " . $alias_product_perentity .
".accountancy_code_sell = aa.account_number AND aa.active = 1 AND aa.fk_pcg_version = '".$db->escape($chartaccountcode).
"' AND aa.entity = ".$conf->entity;
175 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa2 ON " . $alias_product_perentity .
".accountancy_code_sell_intra = aa2.account_number AND aa2.active = 1 AND aa2.fk_pcg_version = '".$db->escape($chartaccountcode).
"' AND aa2.entity = ".$conf->entity;
176 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa3 ON " . $alias_product_perentity .
".accountancy_code_sell_export = aa3.account_number AND aa3.active = 1 AND aa3.fk_pcg_version = '".$db->escape($chartaccountcode).
"' AND aa3.entity = ".$conf->entity;
177 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa4 ON " . $alias_societe_perentity .
".accountancy_code_sell = aa4.account_number AND aa4.active = 1 AND aa4.fk_pcg_version = '".$db->escape($chartaccountcode).
"' AND aa4.entity = ".$conf->entity;
178 $sql .=
" WHERE f.fk_statut > 0 AND l.fk_code_ventilation <= 0";
179 $sql .=
" AND l.product_type <= 2";
180 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
181 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
182 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
184 if ($validatemonth && $validateyear) {
188 dol_syslog(
'htdocs/accountancy/customer/index.php');
190 $result = $db->query($sql);
195 $num_lines = $db->num_rows($result);
197 $facture_static =
new Facture($db);
199 $isSellerInEEC =
isInEEC($mysoc);
201 $thirdpartystatic =
new Societe($db);
202 $facture_static =
new Facture($db);
204 $product_static =
new Product($db);
207 while ($i < min($num_lines, 10000)) {
208 $objp = $db->fetch_object($result);
210 $thirdpartystatic->id = !empty($objp->socid) ? $objp->socid : 0;
211 $thirdpartystatic->name = !empty($objp->name) ? $objp->name :
"";
212 $thirdpartystatic->client = !empty($objp->client) ? $objp->client :
"";
213 $thirdpartystatic->fournisseur = !empty($objp->fournisseur) ? $objp->fournisseur :
"";
214 $thirdpartystatic->code_client = !empty($objp->code_client) ? $objp->code_client :
"";
215 $thirdpartystatic->code_compta_client = !empty($objp->code_compta_client) ? $objp->code_compta_client :
"";
216 $thirdpartystatic->code_fournisseur = !empty($objp->code_fournisseur) ? $objp->code_fournisseur :
"";
217 $thirdpartystatic->code_compta_fournisseur = !empty($objp->code_compta_fournisseur) ? $objp->code_compta_fournisseur :
"";
218 $thirdpartystatic->email = !empty($objp->email) ? $objp->email :
"";
219 $thirdpartystatic->country_code = !empty($objp->country_code) ? $objp->country_code :
"";
220 $thirdpartystatic->tva_intra = !empty($objp->tva_intra) ? $objp->tva_intra :
"";
221 $thirdpartystatic->code_compta_product = !empty($objp->company_code_sell) ? $objp->company_code_sell :
"";
223 $product_static->ref = $objp->product_ref;
224 $product_static->id = $objp->product_id;
225 $product_static->type = $objp->type;
226 $product_static->label = $objp->product_label;
227 $product_static->status = !empty($objp->status) ? $objp->status : 0;
228 $product_static->status_buy = !empty($objp->status_buy) ? $objp->status_buy : 0;
229 $product_static->accountancy_code_sell = $objp->code_sell;
230 $product_static->accountancy_code_sell_intra = $objp->code_sell_intra;
231 $product_static->accountancy_code_sell_export = $objp->code_sell_export;
232 $product_static->accountancy_code_buy = !empty($objp->code_buy) ? $objp->code_buy :
"";
233 $product_static->accountancy_code_buy_intra = !empty($objp->code_buy_intra) ? $objp->code_buy_intra :
"";
234 $product_static->accountancy_code_buy_export = !empty($objp->code_buy_export) ? $objp->code_buy_export :
"";
235 $product_static->tva_tx = $objp->tva_tx_prod;
237 $facture_static->ref = $objp->ref;
238 $facture_static->id = $objp->facid;
239 $facture_static->type = $objp->ftype;
240 $facture_static->date = $db->jdate($objp->datef);
241 $facture_static->fk_facture_source = $objp->fk_facture_source;
243 $facture_static_det->id = $objp->rowid;
244 $facture_static_det->total_ht = $objp->total_ht;
245 $facture_static_det->tva_tx = $objp->tva_tx_line;
246 $facture_static_det->vat_src_code = $objp->vat_src_code;
247 $facture_static_det->product_type = $objp->type_l;
248 $facture_static_det->desc = $objp->description;
250 $accountingAccountArray = array(
251 'dom'=>$objp->aarowid,
252 'intra'=>$objp->aarowid_intra,
253 'export'=>$objp->aarowid_export,
254 'thirdparty' =>$objp->aarowid_thirdparty);
256 $code_sell_p_notset =
'';
257 $code_sell_t_notset =
'';
261 $return=$accountingAccount->getAccountingCodeToBind($thirdpartystatic, $mysoc, $product_static, $facture_static, $facture_static_det, $accountingAccountArray,
'customer');
262 if (!is_array($return) && $return < 0) {
265 $suggestedid = $return[
'suggestedid'];
266 $suggestedaccountingaccountfor = $return[
'suggestedaccountingaccountfor'];
268 if (!empty($suggestedid) && $suggestedaccountingaccountfor !=
'' && $suggestedaccountingaccountfor !=
'eecwithoutvatnumber') {
269 $suggestedid = $return[
'suggestedid'];
275 if ($suggestedid > 0) {
276 $sqlupdate =
"UPDATE ".MAIN_DB_PREFIX.
"facturedet";
277 $sqlupdate .=
" SET fk_code_ventilation = ".((int) $suggestedid);
278 $sqlupdate .=
" WHERE fk_code_ventilation <= 0 AND product_type <= 2 AND rowid = ".((int) $facture_static_det->id);
280 $resqlupdate = $db->query($sqlupdate);
294 if ($num_lines > 10000) {
295 $notpossible += ($num_lines - 10000);
303 setEventMessages($langs->trans(
'AutomaticBindingDone', $nbbinddone, $notpossible),
null,
'mesgs');
312 llxHeader(
'', $langs->trans(
"CustomersVentilation"));
314 $textprevyear =
'<a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current - 1).
'">'.
img_previous().
'</a>';
315 $textnextyear =
' <a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current + 1).
'">'.
img_next().
'</a>';
318 print
load_fiche_titre($langs->trans(
"CustomersVentilation").
" ".$textprevyear.
" ".$langs->trans(
"Year").
" ".$year_start.
" ".$textnextyear,
'',
'title_accountancy');
320 print
'<span class="opacitymedium">'.$langs->trans(
"DescVentilCustomer").
'</span><br>';
321 print
'<span class="opacitymedium hideonsmartphone">'.$langs->trans(
"DescVentilMore", $langs->transnoentitiesnoconv(
"ValidateHistory"), $langs->transnoentitiesnoconv(
"ToBind")).
'<br>';
325 print
info_admin($langs->trans(
"SorryThisModuleIsNotCompatibleWithTheExperimentalFeatureOfSituationInvoices"));
331 $buttonbind =
'<a class="butAction" href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&token='.
newToken().
'">'.$langs->trans(
"ValidateHistory").
'</a>';
333 print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0, $buttonbind,
'', 0, 1, 1);
336 print
'<div class="div-table-responsive-no-min">';
337 print
'<table class="noborder centpercent">';
338 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
339 print
'<td>'.$langs->trans(
"Label").
'</td>';
340 for ($i = 1; $i <= 12; $i++) {
341 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
346 if ($cursormonth > 12) {
349 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
352 print
'<td width="60" class="right">';
353 if (!empty($tmp[
'mday'])) {
354 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
355 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
356 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/list.php?'.$param.
'">';
358 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
359 if (!empty($tmp[
'mday'])) {
364 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
366 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
367 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
368 for ($i = 1; $i <= 12; $i++) {
369 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
373 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
375 $sql .=
" SUM(fd.total_ht) as total";
376 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
377 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
378 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
379 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
380 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
382 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
383 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
385 $sql .=
" AND f.fk_statut > 0";
386 $sql .=
" AND fd.product_type <= 2";
387 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
388 $sql .=
" AND aa.account_number IS NULL";
389 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
394 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
396 dol_syslog(
'htdocs/accountancy/customer/index.php', LOG_DEBUG);
397 $resql = $db->query($sql);
399 $num = $db->num_rows(
$resql);
401 while ($row = $db->fetch_row(
$resql)) {
407 print
'<tr class="oddeven">';
409 if ($row[0] ==
'tobind') {
410 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
416 if ($row[0] ==
'tobind') {
417 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
422 for ($i = 2; $i <= 13; $i++) {
423 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
424 if ($cursormonth > 12) {
427 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
430 print
'<td class="right nowraponall amount">';
431 print
price($row[$i]);
434 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
440 print
'<tr class="oddeven"><td colspan="16">';
441 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
445 print $db->lasterror();
454 print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
457 print
'<div class="div-table-responsive-no-min">';
458 print
'<table class="noborder centpercent">';
459 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
460 print
'<td>'.$langs->trans(
"Label").
'</td>';
461 for ($i = 1; $i <= 12; $i++) {
462 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
467 if ($cursormonth > 12) {
470 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
473 print
'<td width="60" class="right">';
474 if (!empty($tmp[
'mday'])) {
475 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
476 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
477 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/lines.php?'.$param.
'">';
479 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
480 if (!empty($tmp[
'mday'])) {
485 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
487 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
488 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
489 for ($i = 1; $i <= 12; $i++) {
490 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
494 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
496 $sql .=
" SUM(fd.total_ht) as total";
497 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
498 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
499 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
500 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
501 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
503 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
504 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
506 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
507 $sql .=
" AND f.fk_statut > 0";
508 $sql .=
" AND fd.product_type <= 2";
509 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
514 $sql .=
" AND aa.account_number IS NOT NULL";
515 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
516 $sql .=
' ORDER BY aa.account_number';
518 dol_syslog(
'htdocs/accountancy/customer/index.php');
519 $resql = $db->query($sql);
521 $num = $db->num_rows(
$resql);
523 while ($row = $db->fetch_row(
$resql)) {
529 print
'<tr class="oddeven">';
531 if ($row[0] ==
'tobind') {
532 print $langs->trans(
"Unknown");
539 if ($row[0] ==
'tobind') {
540 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
546 for ($i = 2; $i <= 13; $i++) {
547 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
548 if ($cursormonth > 12) {
551 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
554 print
'<td class="right nowraponall amount">';
555 print
price($row[$i]);
558 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
564 print
'<tr class="oddeven"><td colspan="16">';
565 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
569 print $db->lasterror();
575 if ($conf->global->MAIN_FEATURES_LEVEL > 0) {
579 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
582 print
'<div class="div-table-responsive-no-min">';
583 print
'<table class="noborder centpercent">';
584 print
'<tr class="liste_titre"><td lass="left">'.$langs->trans(
"TotalVente").
'</td>';
585 for ($i = 1; $i <= 12; $i++) {
586 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
590 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
592 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
594 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalVente")).
"' AS total,";
595 for ($i = 1; $i <= 12; $i++) {
596 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
600 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
602 $sql .=
" SUM(fd.total_ht) as total";
603 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
604 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
605 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
606 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
608 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
609 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
611 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
612 $sql .=
" AND f.fk_statut > 0";
613 $sql .=
" AND fd.product_type <= 2";
614 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
620 dol_syslog(
'htdocs/accountancy/customer/index.php');
621 $resql = $db->query($sql);
623 $num = $db->num_rows(
$resql);
625 while ($row = $db->fetch_row(
$resql)) {
626 print
'<tr><td>'.$row[0].
'</td>';
627 for ($i = 1; $i <= 12; $i++) {
628 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
630 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
635 print $db->lasterror();
642 print
'<div class="div-table-responsive-no-min">';
643 print
'<table class="noborder centpercent">';
644 print
'<tr class="liste_titre"><td>'.$langs->trans(
"TotalMarge").
'</td>';
645 for ($i = 1; $i <= 12; $i++) {
646 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
650 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
652 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
656 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
657 for ($i = 1; $i <= 12; $i++) {
658 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
662 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
663 " (".$db->ifsql(
"fd.total_ht < 0",
664 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
665 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
")",
666 0).
") AS month".str_pad($j, 2,
'0', STR_PAD_LEFT).
",";
668 $sql .=
" SUM(".$db->ifsql(
"fd.total_ht < 0",
669 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
670 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
") as total";
672 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
673 for ($i = 1; $i <= 12; $i++) {
674 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
678 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
679 " (".$db->ifsql(
"fd.total_ht < 0",
680 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty)))",
681 " (fd.total_ht - (fd.buy_price_ht * fd.qty))").
")",
682 0).
") AS month".str_pad($j, 2,
'0', STR_PAD_LEFT).
",";
684 $sql .=
" SUM(".$db->ifsql(
"fd.total_ht < 0",
685 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty)))",
686 " (fd.total_ht - (fd.buy_price_ht * fd.qty))").
") as total";
688 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
689 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
690 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
691 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
693 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
694 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
696 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
697 $sql .=
" AND f.fk_statut > 0";
698 $sql .=
" AND fd.product_type <= 2";
699 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
704 dol_syslog(
'htdocs/accountancy/customer/index.php');
705 $resql = $db->query($sql);
707 $num = $db->num_rows(
$resql);
709 while ($row = $db->fetch_row(
$resql)) {
710 print
'<tr><td>'.$row[0].
'</td>';
711 for ($i = 1; $i <= 12; $i++) {
712 print
'<td class="right nowraponall amount">'.price(
price2num($row[$i])).
'</td>';
714 print
'<td class="right nowraponall amount"><b>'.price(
price2num($row[13])).
'</b></td>';
719 print $db->lasterror();