29 require
'../../main.inc.php';
30 require_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
31 require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
32 require_once DOL_DOCUMENT_ROOT.
'/core/lib/company.lib.php';
33 require_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
34 require_once DOL_DOCUMENT_ROOT.
'/accountancy/class/accountingaccount.class.php';
37 $langs->loadLangs(array(
"compta",
"bills",
"other",
"accountancy"));
39 $validatemonth =
GETPOST(
'validatemonth',
'int');
40 $validateyear =
GETPOST(
'validateyear',
'int');
46 if ($user->socid > 0) {
49 if (empty($user->rights->accounting->bind->write)) {
55 $month_start = ($conf->global->SOCIETE_FISCAL_MONTH_START ? ($conf->global->SOCIETE_FISCAL_MONTH_START) : 1);
57 $year_start =
GETPOST(
"year",
'int');
64 $year_end = $year_start + 1;
65 $month_end = $month_start - 1;
70 $search_date_start =
dol_mktime(0, 0, 0, $month_start, 1, $year_start);
72 $year_current = $year_start;
75 $action =
GETPOST(
'action',
'aZ09');
77 $chartaccountcode =
dol_getIdFromCode($db, $conf->global->CHARTOFACCOUNTS,
'accounting_system',
'rowid',
'pcg_version');
83 if ($user->socid > 0) {
86 if (empty($user->rights->accounting->mouvements->lire)) {
95 if (($action ==
'clean' || $action ==
'validatehistory') && $user->rights->accounting->bind->write) {
98 $sql1 =
"UPDATE ".MAIN_DB_PREFIX.
"facturedet as fd";
99 $sql1 .=
" SET fk_code_ventilation = 0";
100 $sql1 .=
' WHERE fd.fk_code_ventilation NOT IN';
101 $sql1 .=
' (SELECT accnt.rowid ';
102 $sql1 .=
' FROM '.MAIN_DB_PREFIX.
'accounting_account as accnt';
103 $sql1 .=
' INNER JOIN '.MAIN_DB_PREFIX.
'accounting_system as syst';
104 $sql1 .=
' ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid='.((int) $conf->global->CHARTOFACCOUNTS).
' AND accnt.entity = '.((int) $conf->entity).
')';
105 $sql1 .=
' AND fd.fk_facture IN (SELECT rowid FROM '.MAIN_DB_PREFIX.
'facture WHERE entity = '.((int) $conf->entity).
')';
106 $sql1 .=
' AND fk_code_ventilation <> 0';
108 dol_syslog(
"htdocs/accountancy/customer/index.php fixaccountancycode", LOG_DEBUG);
109 $resql1 = $db->query($sql1);
120 if ($action ==
'validatehistory') {
144 $sql =
"SELECT f.rowid as facid, f.ref as ref, f.datef, f.type as ftype,";
145 $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,";
146 $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,";
147 if (!empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED)) {
148 $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,";
150 $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,";
152 $sql .=
" aa.rowid as aarowid, aa2.rowid as aarowid_intra, aa3.rowid as aarowid_export, aa4.rowid as aarowid_thirdparty,";
153 $sql .=
" co.code as country_code, co.label as country_label,";
154 $sql .=
" s.tva_intra,";
155 if (!empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED)) {
156 $sql .=
" spe.accountancy_code_sell as company_code_sell";
158 $sql .=
" s.accountancy_code_sell as company_code_sell";
160 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facture as f";
161 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
162 if (!empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED)) {
163 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"societe_perentity as spe ON spe.fk_soc = s.rowid AND spe.entity = " . ((int) $conf->entity);
165 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"c_country as co ON co.rowid = s.fk_pays ";
166 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"facturedet as l ON f.rowid = l.fk_facture";
167 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p ON p.rowid = l.fk_product";
168 if (!empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED)) {
169 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"product_perentity as ppe ON ppe.fk_product = p.rowid AND ppe.entity = " . ((int) $conf->entity);
171 $alias_societe_perentity = empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED) ?
"s" :
"spe";
172 $alias_product_perentity = empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED) ?
"p" :
"ppe";
173 $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;
174 $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;
175 $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;
176 $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;
177 $sql .=
" WHERE f.fk_statut > 0 AND l.fk_code_ventilation <= 0";
178 $sql .=
" AND l.product_type <= 2";
179 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
180 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
181 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
183 if ($validatemonth && $validateyear) {
187 dol_syslog(
'htdocs/accountancy/customer/index.php');
189 $result = $db->query($sql);
194 $num_lines = $db->num_rows($result);
196 $facture_static =
new Facture($db);
198 $isSellerInEEC =
isInEEC($mysoc);
200 $thirdpartystatic =
new Societe($db);
201 $facture_static =
new Facture($db);
203 $product_static =
new Product($db);
206 while ($i < min($num_lines, 10000)) {
207 $objp = $db->fetch_object($result);
209 $thirdpartystatic->id = $objp->socid;
210 $thirdpartystatic->name = $objp->name;
211 $thirdpartystatic->client = $objp->client;
212 $thirdpartystatic->fournisseur = $objp->fournisseur;
213 $thirdpartystatic->code_client = $objp->code_client;
214 $thirdpartystatic->code_compta_client = $objp->code_compta_client;
215 $thirdpartystatic->code_fournisseur = $objp->code_fournisseur;
216 $thirdpartystatic->code_compta_fournisseur = $objp->code_compta_fournisseur;
217 $thirdpartystatic->email = $objp->email;
218 $thirdpartystatic->country_code = $objp->country_code;
219 $thirdpartystatic->tva_intra = $objp->tva_intra;
220 $thirdpartystatic->code_compta_product = $objp->company_code_sell;
222 $product_static->ref = $objp->product_ref;
223 $product_static->id = $objp->product_id;
224 $product_static->type = $objp->type;
225 $product_static->label = $objp->product_label;
226 $product_static->status = $objp->status;
227 $product_static->status_buy = $objp->status_buy;
228 $product_static->accountancy_code_sell = $objp->code_sell;
229 $product_static->accountancy_code_sell_intra = $objp->code_sell_intra;
230 $product_static->accountancy_code_sell_export = $objp->code_sell_export;
231 $product_static->accountancy_code_buy = $objp->code_buy;
232 $product_static->accountancy_code_buy_intra = $objp->code_buy_intra;
233 $product_static->accountancy_code_buy_export = $objp->code_buy_export;
234 $product_static->tva_tx = $objp->tva_tx_prod;
236 $facture_static->ref = $objp->ref;
237 $facture_static->id = $objp->facid;
238 $facture_static->type = $objp->ftype;
239 $facture_static->date = $objp->datef;
241 $facture_static_det->id = $objp->rowid;
242 $facture_static_det->total_ht = $objp->total_ht;
243 $facture_static_det->tva_tx = $objp->tva_tx_line;
244 $facture_static_det->vat_src_code = $objp->vat_src_code;
245 $facture_static_det->product_type = $objp->type_l;
246 $facture_static_det->desc = $objp->description;
248 $accountingAccountArray = array(
249 'dom'=>$objp->aarowid,
250 'intra'=>$objp->aarowid_intra,
251 'export'=>$objp->aarowid_export,
252 'thirdparty' =>$objp->aarowid_thirdparty);
254 $code_sell_p_notset =
'';
255 $code_sell_t_notset =
'';
259 $return=$accountingAccount->getAccountingCodeToBind($thirdpartystatic, $mysoc, $product_static, $facture_static, $facture_static_det, $accountingAccountArray,
'customer');
260 if (!is_array($return) && $return < 0) {
263 $suggestedid = $return[
'suggestedid'];
264 $suggestedaccountingaccountfor = $return[
'suggestedaccountingaccountfor'];
266 if (!empty($suggestedid) && $suggestedaccountingaccountfor !=
'' && $suggestedaccountingaccountfor !=
'eecwithoutvatnumber') {
267 $suggestedid = $return[
'suggestedid'];
273 if ($suggestedid > 0) {
274 $sqlupdate =
"UPDATE ".MAIN_DB_PREFIX.
"facturedet";
275 $sqlupdate .=
" SET fk_code_ventilation = ".((int) $suggestedid);
276 $sqlupdate .=
" WHERE fk_code_ventilation <= 0 AND product_type <= 2 AND rowid = ".((int) $facture_static_det->id);
278 $resqlupdate = $db->query($sqlupdate);
292 if ($num_lines > 10000) {
293 $notpossible += ($num_lines - 10000);
301 setEventMessages($langs->trans(
'AutomaticBindingDone', $nbbinddone, $notpossible),
null,
'mesgs');
310 llxHeader(
'', $langs->trans(
"CustomersVentilation"));
312 $textprevyear =
'<a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current - 1).
'">'.
img_previous().
'</a>';
313 $textnextyear =
' <a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current + 1).
'">'.
img_next().
'</a>';
316 print
load_fiche_titre($langs->trans(
"CustomersVentilation").
" ".$textprevyear.
" ".$langs->trans(
"Year").
" ".$year_start.
" ".$textnextyear,
'',
'title_accountancy');
318 print
'<span class="opacitymedium">'.$langs->trans(
"DescVentilCustomer").
'</span><br>';
319 print
'<span class="opacitymedium hideonsmartphone">'.$langs->trans(
"DescVentilMore", $langs->transnoentitiesnoconv(
"ValidateHistory"), $langs->transnoentitiesnoconv(
"ToBind")).
'<br>';
325 $buttonbind =
'<a class="butAction" href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&token='.
newToken().
'">'.$langs->trans(
"ValidateHistory").
'</a>';
327 print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0, $buttonbind,
'', 0, 1, 1);
330 print
'<div class="div-table-responsive-no-min">';
331 print
'<table class="noborder centpercent">';
332 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
333 print
'<td>'.$langs->trans(
"Label").
'</td>';
334 for ($i = 1; $i <= 12; $i++) {
335 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
340 if ($cursormonth > 12) {
343 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
346 print
'<td width="60" class="right">';
347 if (!empty($tmp[
'mday'])) {
348 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
349 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
350 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/list.php?'.$param.
'">';
352 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
353 if (!empty($tmp[
'mday'])) {
358 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
360 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
361 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
362 for ($i = 1; $i <= 12; $i++) {
363 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
367 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
369 $sql .=
" SUM(fd.total_ht) as total";
370 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
371 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
372 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
373 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
374 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
376 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
377 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
379 $sql .=
" AND f.fk_statut > 0";
380 $sql .=
" AND fd.product_type <= 2";
381 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
382 $sql .=
" AND aa.account_number IS NULL";
383 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
388 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
390 dol_syslog(
'htdocs/accountancy/customer/index.php', LOG_DEBUG);
391 $resql = $db->query($sql);
393 $num = $db->num_rows(
$resql);
395 while ($row = $db->fetch_row(
$resql)) {
396 print
'<tr class="oddeven">';
398 if ($row[0] ==
'tobind') {
399 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
405 if ($row[0] ==
'tobind') {
406 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
411 for ($i = 2; $i <= 13; $i++) {
412 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
413 if ($cursormonth > 12) {
416 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
419 print
'<td class="right nowraponall amount">';
420 print
price($row[$i]);
423 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
429 print
'<tr class="oddeven"><td colspan="16">';
430 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
434 print $db->lasterror();
443 print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
446 print
'<div class="div-table-responsive-no-min">';
447 print
'<table class="noborder centpercent">';
448 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
449 print
'<td>'.$langs->trans(
"Label").
'</td>';
450 for ($i = 1; $i <= 12; $i++) {
451 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
456 if ($cursormonth > 12) {
459 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
462 print
'<td width="60" class="right">';
463 if (!empty($tmp[
'mday'])) {
464 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
465 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
466 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/lines.php?'.$param.
'">';
468 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
469 if (!empty($tmp[
'mday'])) {
474 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
476 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
477 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
478 for ($i = 1; $i <= 12; $i++) {
479 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
483 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
485 $sql .=
" SUM(fd.total_ht) as total";
486 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
487 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
488 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
489 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
490 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
492 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
493 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
495 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
496 $sql .=
" AND f.fk_statut > 0";
497 $sql .=
" AND fd.product_type <= 2";
498 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
503 $sql .=
" AND aa.account_number IS NOT NULL";
504 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
505 $sql .=
' ORDER BY aa.account_number';
507 dol_syslog(
'htdocs/accountancy/customer/index.php');
508 $resql = $db->query($sql);
510 $num = $db->num_rows(
$resql);
512 while ($row = $db->fetch_row(
$resql)) {
513 print
'<tr class="oddeven">';
515 if ($row[0] ==
'tobind') {
516 print $langs->trans(
"Unknown");
523 if ($row[0] ==
'tobind') {
524 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
530 for ($i = 2; $i <= 13; $i++) {
531 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
532 if ($cursormonth > 12) {
535 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
538 print
'<td class="right nowraponall amount">';
539 print
price($row[$i]);
542 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
548 print
'<tr class="oddeven"><td colspan="16">';
549 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
553 print $db->lasterror();
559 if ($conf->global->MAIN_FEATURES_LEVEL > 0) {
563 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
566 print
'<div class="div-table-responsive-no-min">';
567 print
'<table class="noborder centpercent">';
568 print
'<tr class="liste_titre"><td lass="left">'.$langs->trans(
"TotalVente").
'</td>';
569 for ($i = 1; $i <= 12; $i++) {
570 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
574 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
576 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
578 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalVente")).
"' AS total,";
579 for ($i = 1; $i <= 12; $i++) {
580 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
584 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
586 $sql .=
" SUM(fd.total_ht) as total";
587 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
588 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
589 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
590 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
592 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
593 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
595 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
596 $sql .=
" AND f.fk_statut > 0";
597 $sql .=
" AND fd.product_type <= 2";
598 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
604 dol_syslog(
'htdocs/accountancy/customer/index.php');
605 $resql = $db->query($sql);
607 $num = $db->num_rows(
$resql);
609 while ($row = $db->fetch_row(
$resql)) {
610 print
'<tr><td>'.$row[0].
'</td>';
611 for ($i = 1; $i <= 12; $i++) {
612 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
614 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
619 print $db->lasterror();
627 print
'<div class="div-table-responsive-no-min">';
628 print
'<table class="noborder centpercent">';
629 print
'<tr class="liste_titre"><td>'.$langs->trans(
"TotalMarge").
'</td>';
630 for ($i = 1; $i <= 12; $i++) {
631 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
635 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
637 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
638 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
639 for ($i = 1; $i <= 12; $i++) {
640 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
644 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
645 " (".$db->ifsql(
"fd.total_ht < 0",
646 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
647 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
")",
648 0).
") AS month".str_pad($j, 2,
'0', STR_PAD_LEFT).
",";
650 $sql .=
" SUM(".$db->ifsql(
"fd.total_ht < 0",
651 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
652 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
") as total";
654 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
655 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
656 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
657 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
659 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
660 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
662 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
663 $sql .=
" AND f.fk_statut > 0";
664 $sql .=
" AND fd.product_type <= 2";
665 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
670 dol_syslog(
'htdocs/accountancy/customer/index.php');
671 $resql = $db->query($sql);
673 $num = $db->num_rows(
$resql);
675 while ($row = $db->fetch_row(
$resql)) {
676 print
'<tr><td>'.$row[0].
'</td>';
677 for ($i = 1; $i <= 12; $i++) {
678 print
'<td class="right nowraponall amount">'.price(
price2num($row[$i])).
'</td>';
680 print
'<td class="right nowraponall amount"><b>'.price(
price2num($row[13])).
'</b></td>';
685 print $db->lasterror();