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');
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 ".$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 '.$db->prefix().
'accounting_account as accnt';
104 $sql1 .=
' INNER JOIN '.$db->prefix().
'accounting_system as syst';
105 $sql1 .=
" ON accnt.fk_pcg_version = syst.pcg_version AND syst.rowid = ".((int)
getDolGlobalInt(
'CHARTOFACCOUNTS')).
" AND accnt.entity = ".((
int) $conf->entity).
")";
106 $sql1 .=
" AND fd.fk_facture IN (SELECT rowid FROM ".$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') {
131 $sql =
"SELECT f.rowid as facid, f.ref as ref, f.datef, f.type as ftype, f.situation_cycle_ref, f.fk_facture_source,";
132 $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,";
133 $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,";
134 if (!empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED)) {
135 $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,";
137 $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,";
139 $sql .=
" aa.rowid as aarowid, aa2.rowid as aarowid_intra, aa3.rowid as aarowid_export, aa4.rowid as aarowid_thirdparty,";
140 $sql .=
" co.code as country_code, co.label as country_label,";
141 $sql .=
" s.tva_intra,";
142 if (!empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED)) {
143 $sql .=
" spe.accountancy_code_sell as company_code_sell";
145 $sql .=
" s.accountancy_code_sell as company_code_sell";
147 $sql .=
" FROM ".$db->prefix().
"facture as f";
148 $sql .=
" INNER JOIN ".$db->prefix().
"societe as s ON s.rowid = f.fk_soc";
149 if (!empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED)) {
150 $sql .=
" LEFT JOIN " . $db->prefix() .
"societe_perentity as spe ON spe.fk_soc = s.rowid AND spe.entity = " . ((int) $conf->entity);
152 $sql .=
" LEFT JOIN ".$db->prefix().
"c_country as co ON co.rowid = s.fk_pays ";
153 $sql .=
" INNER JOIN ".$db->prefix().
"facturedet as l ON f.rowid = l.fk_facture";
154 $sql .=
" LEFT JOIN ".$db->prefix().
"product as p ON p.rowid = l.fk_product";
155 if (!empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED)) {
156 $sql .=
" LEFT JOIN " . $db->prefix() .
"product_perentity as ppe ON ppe.fk_product = p.rowid AND ppe.entity = " . ((int) $conf->entity);
158 $alias_societe_perentity = empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED) ?
"s" :
"spe";
159 $alias_product_perentity = empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED) ?
"p" :
"ppe";
160 $sql .=
" LEFT JOIN ".$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;
161 $sql .=
" LEFT JOIN ".$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;
162 $sql .=
" LEFT JOIN ".$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;
163 $sql .=
" LEFT JOIN ".$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;
164 $sql .=
" WHERE f.fk_statut > 0 AND l.fk_code_ventilation <= 0";
165 $sql .=
" AND l.product_type <= 2";
166 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
167 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
168 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
170 if ($validatemonth && $validateyear) {
174 dol_syslog(
'htdocs/accountancy/customer/index.php');
176 $result = $db->query(
$sql);
181 $num_lines = $db->num_rows($result);
183 $facture_static =
new Facture($db);
185 $isSellerInEEC =
isInEEC($mysoc);
187 $thirdpartystatic =
new Societe($db);
188 $facture_static =
new Facture($db);
190 $product_static =
new Product($db);
193 while ($i < min($num_lines, 10000)) {
194 $objp = $db->fetch_object($result);
196 $thirdpartystatic->id = !empty($objp->socid) ? $objp->socid : 0;
197 $thirdpartystatic->name = !empty($objp->name) ? $objp->name :
"";
198 $thirdpartystatic->client = !empty($objp->client) ? $objp->client :
"";
199 $thirdpartystatic->fournisseur = !empty($objp->fournisseur) ? $objp->fournisseur :
"";
200 $thirdpartystatic->code_client = !empty($objp->code_client) ? $objp->code_client :
"";
201 $thirdpartystatic->code_compta_client = !empty($objp->code_compta_client) ? $objp->code_compta_client :
"";
202 $thirdpartystatic->code_fournisseur = !empty($objp->code_fournisseur) ? $objp->code_fournisseur :
"";
203 $thirdpartystatic->code_compta_fournisseur = !empty($objp->code_compta_fournisseur) ? $objp->code_compta_fournisseur :
"";
204 $thirdpartystatic->email = !empty($objp->email) ? $objp->email :
"";
205 $thirdpartystatic->country_code = !empty($objp->country_code) ? $objp->country_code :
"";
206 $thirdpartystatic->tva_intra = !empty($objp->tva_intra) ? $objp->tva_intra :
"";
207 $thirdpartystatic->code_compta_product = !empty($objp->company_code_sell) ? $objp->company_code_sell :
"";
209 $product_static->ref = $objp->product_ref;
210 $product_static->id = $objp->product_id;
211 $product_static->type = $objp->type;
212 $product_static->label = $objp->product_label;
213 $product_static->status = !empty($objp->status) ? $objp->status : 0;
214 $product_static->status_buy = !empty($objp->status_buy) ? $objp->status_buy : 0;
215 $product_static->accountancy_code_sell = $objp->code_sell;
216 $product_static->accountancy_code_sell_intra = $objp->code_sell_intra;
217 $product_static->accountancy_code_sell_export = $objp->code_sell_export;
218 $product_static->accountancy_code_buy = !empty($objp->code_buy) ? $objp->code_buy :
"";
219 $product_static->accountancy_code_buy_intra = !empty($objp->code_buy_intra) ? $objp->code_buy_intra :
"";
220 $product_static->accountancy_code_buy_export = !empty($objp->code_buy_export) ? $objp->code_buy_export :
"";
221 $product_static->tva_tx = $objp->tva_tx_prod;
223 $facture_static->ref = $objp->ref;
224 $facture_static->id = $objp->facid;
225 $facture_static->type = $objp->ftype;
226 $facture_static->date = $db->jdate($objp->datef);
227 $facture_static->fk_facture_source = $objp->fk_facture_source;
229 $facture_static_det->id = $objp->rowid;
230 $facture_static_det->total_ht = $objp->total_ht;
231 $facture_static_det->tva_tx = $objp->tva_tx_line;
232 $facture_static_det->vat_src_code = $objp->vat_src_code;
233 $facture_static_det->product_type = $objp->type_l;
234 $facture_static_det->desc = $objp->description;
236 $accountingAccountArray = array(
237 'dom'=>$objp->aarowid,
238 'intra'=>$objp->aarowid_intra,
239 'export'=>$objp->aarowid_export,
240 'thirdparty' =>$objp->aarowid_thirdparty);
242 $code_sell_p_notset =
'';
243 $code_sell_t_notset =
'';
247 $return=$accountingAccount->getAccountingCodeToBind($thirdpartystatic, $mysoc, $product_static, $facture_static, $facture_static_det, $accountingAccountArray,
'customer');
248 if (!is_array($return) && $return < 0) {
251 $suggestedid = $return[
'suggestedid'];
252 $suggestedaccountingaccountfor = $return[
'suggestedaccountingaccountfor'];
254 if (!empty($suggestedid) && $suggestedaccountingaccountfor !=
'' && $suggestedaccountingaccountfor !=
'eecwithoutvatnumber') {
255 $suggestedid = $return[
'suggestedid'];
261 if ($suggestedid > 0) {
262 $sqlupdate =
"UPDATE ".MAIN_DB_PREFIX.
"facturedet";
263 $sqlupdate .=
" SET fk_code_ventilation = ".((int) $suggestedid);
264 $sqlupdate .=
" WHERE fk_code_ventilation <= 0 AND product_type <= 2 AND rowid = ".((int) $facture_static_det->id);
266 $resqlupdate = $db->query($sqlupdate);
282 if ($num_lines > 10000) {
283 $notpossible += ($num_lines - 10000);
291 setEventMessages($langs->trans(
'AutomaticBindingDone', $nbbinddone, $notpossible),
null, ($notpossible ?
'warnings' :
'mesgs'));
293 setEventMessages($langs->trans(
'DoManualBindingForFailedRecord', $nbbindfailed),
null,
'warnings');
303 llxHeader(
'', $langs->trans(
"CustomersVentilation"));
305 $textprevyear =
'<a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current - 1).
'">'.
img_previous().
'</a>';
306 $textnextyear =
' <a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current + 1).
'">'.
img_next().
'</a>';
309 print
load_fiche_titre($langs->trans(
"CustomersVentilation").
" ".$textprevyear.
" ".$langs->trans(
"Year").
" ".$year_start.
" ".$textnextyear,
'',
'title_accountancy');
311 print
'<span class="opacitymedium">'.$langs->trans(
"DescVentilCustomer").
'</span><br>';
312 print
'<span class="opacitymedium hideonsmartphone">'.$langs->trans(
"DescVentilMore", $langs->transnoentitiesnoconv(
"ValidateHistory"), $langs->transnoentitiesnoconv(
"ToBind")).
'<br>';
316 print
info_admin($langs->trans(
"SorryThisModuleIsNotCompatibleWithTheExperimentalFeatureOfSituationInvoices"));
322 $buttonbind =
'<a class="butAction" href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&token='.
newToken().
'">'.
img_picto($langs->trans(
"ValidateHistory"),
'link',
'class="pictofixedwidth fa-color-unset"').$langs->trans(
"ValidateHistory").
'</a>';
324 print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0, $buttonbind,
'', 0, 1, 1);
327 print
'<div class="div-table-responsive-no-min">';
328 print
'<table class="noborder centpercent">';
329 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
330 print
'<td>'.$langs->trans(
"Label").
'</td>';
331 for ($i = 1; $i <= 12; $i++) {
332 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
337 if ($cursormonth > 12) {
340 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
343 print
'<td width="60" class="right">';
344 if (!empty($tmp[
'mday'])) {
345 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
346 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
347 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/list.php?'.$param.
'">';
349 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
350 if (!empty($tmp[
'mday'])) {
355 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
357 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
358 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
359 for ($i = 1; $i <= 12; $i++) {
360 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
364 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
366 $sql .=
" SUM(fd.total_ht) as total";
367 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
368 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
369 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
370 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
371 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
373 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
374 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
376 $sql .=
" AND f.fk_statut > 0";
377 $sql .=
" AND fd.product_type <= 2";
378 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
379 $sql .=
" AND aa.account_number IS NULL";
380 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
385 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
387 dol_syslog(
'htdocs/accountancy/customer/index.php', LOG_DEBUG);
388 $resql = $db->query(
$sql);
390 $num = $db->num_rows($resql);
392 while ($row = $db->fetch_row($resql)) {
398 print
'<tr class="oddeven">';
400 if ($row[0] ==
'tobind') {
401 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
407 if ($row[0] ==
'tobind') {
408 $startmonth = ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1);
409 if ($startmonth > 12) {
412 $startyear = ($startmonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
413 $endmonth = ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + 11;
414 if ($endmonth > 12) {
417 $endyear = ($endmonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
418 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/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"));
423 for ($i = 2; $i <= 13; $i++) {
424 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
425 if ($cursormonth > 12) {
428 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
431 print
'<td class="right nowraponall amount">';
432 print
price($row[$i]);
435 print
'<a href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&year='.$y.
'&validatemonth='.((int) $cursormonth).
'&validateyear='.((int) $cursoryear).
'&token='.
newToken().
'">';
436 print
img_picto($langs->trans(
"ValidateHistory").
' ('.$langs->trans(
'Month'.str_pad($cursormonth, 2,
'0', STR_PAD_LEFT)).
' '.$cursoryear.
')',
'link',
'class="marginleft2"');
441 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
447 print
'<tr class="oddeven"><td colspan="16">';
448 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
452 print $db->lasterror();
461 print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
464 print
'<div class="div-table-responsive-no-min">';
465 print
'<table class="noborder centpercent">';
466 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
467 print
'<td>'.$langs->trans(
"Label").
'</td>';
468 for ($i = 1; $i <= 12; $i++) {
469 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
474 if ($cursormonth > 12) {
477 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
480 print
'<td width="60" class="right">';
481 if (!empty($tmp[
'mday'])) {
482 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
483 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
484 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/lines.php?'.$param.
'">';
486 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
487 if (!empty($tmp[
'mday'])) {
492 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
494 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
495 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
496 for ($i = 1; $i <= 12; $i++) {
497 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
501 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
503 $sql .=
" SUM(fd.total_ht) as total";
504 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
505 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
506 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
507 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
508 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
510 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
511 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
513 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
514 $sql .=
" AND f.fk_statut > 0";
515 $sql .=
" AND fd.product_type <= 2";
516 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
521 $sql .=
" AND aa.account_number IS NOT NULL";
522 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
523 $sql .=
' ORDER BY aa.account_number';
525 dol_syslog(
'htdocs/accountancy/customer/index.php');
526 $resql = $db->query(
$sql);
528 $num = $db->num_rows($resql);
530 while ($row = $db->fetch_row($resql)) {
536 print
'<tr class="oddeven">';
538 if ($row[0] ==
'tobind') {
539 print $langs->trans(
"Unknown");
546 if ($row[0] ==
'tobind') {
547 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
553 for ($i = 2; $i <= 13; $i++) {
554 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
555 if ($cursormonth > 12) {
558 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
561 print
'<td class="right nowraponall amount">';
562 print
price($row[$i]);
565 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
571 print
'<tr class="oddeven"><td colspan="16">';
572 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
576 print $db->lasterror();
586 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
589 print
'<div class="div-table-responsive-no-min">';
590 print
'<table class="noborder centpercent">';
591 print
'<tr class="liste_titre"><td lass="left">'.$langs->trans(
"TotalVente").
'</td>';
592 for ($i = 1; $i <= 12; $i++) {
593 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
597 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
599 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
601 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalVente")).
"' AS total,";
602 for ($i = 1; $i <= 12; $i++) {
603 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
607 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
609 $sql .=
" SUM(fd.total_ht) as total";
610 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
611 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
612 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
613 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
615 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
616 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
618 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
619 $sql .=
" AND f.fk_statut > 0";
620 $sql .=
" AND fd.product_type <= 2";
621 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
627 dol_syslog(
'htdocs/accountancy/customer/index.php');
628 $resql = $db->query(
$sql);
630 $num = $db->num_rows($resql);
632 while ($row = $db->fetch_row($resql)) {
633 print
'<tr><td>'.$row[0].
'</td>';
634 for ($i = 1; $i <= 12; $i++) {
635 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
637 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
642 print $db->lasterror();
649 print
'<div class="div-table-responsive-no-min">';
650 print
'<table class="noborder centpercent">';
651 print
'<tr class="liste_titre"><td>'.$langs->trans(
"TotalMarge").
'</td>';
652 for ($i = 1; $i <= 12; $i++) {
653 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
657 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
659 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
663 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
664 for ($i = 1; $i <= 12; $i++) {
665 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
669 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
670 " (".$db->ifsql(
"fd.total_ht < 0",
671 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
672 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
")",
673 0).
") AS month".str_pad($j, 2,
'0', STR_PAD_LEFT).
",";
675 $sql .=
" SUM(".$db->ifsql(
"fd.total_ht < 0",
676 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
677 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
") as total";
679 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
680 for ($i = 1; $i <= 12; $i++) {
681 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
685 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
686 " (".$db->ifsql(
"fd.total_ht < 0",
687 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty)))",
688 " (fd.total_ht - (fd.buy_price_ht * fd.qty))").
")",
689 0).
") AS month".str_pad($j, 2,
'0', STR_PAD_LEFT).
",";
691 $sql .=
" SUM(".$db->ifsql(
"fd.total_ht < 0",
692 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty)))",
693 " (fd.total_ht - (fd.buy_price_ht * fd.qty))").
") as total";
695 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
696 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
697 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
698 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
700 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
701 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
703 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
704 $sql .=
" AND f.fk_statut > 0";
705 $sql .=
" AND fd.product_type <= 2";
706 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
711 dol_syslog(
'htdocs/accountancy/customer/index.php');
712 $resql = $db->query(
$sql);
714 $num = $db->num_rows($resql);
716 while ($row = $db->fetch_row($resql)) {
717 print
'<tr><td>'.$row[0].
'</td>';
718 for ($i = 1; $i <= 12; $i++) {
719 print
'<td class="right nowraponall amount">'.price(
price2num($row[$i])).
'</td>';
721 print
'<td class="right nowraponall amount"><b>'.price(
price2num($row[13])).
'</b></td>';
726 print $db->lasterror();