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') {
131 $sql =
"SELECT f.rowid as facid, f.ref as ref, f.datef, f.type as ftype, 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.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 ".MAIN_DB_PREFIX.
"facture as f";
148 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"societe as s ON s.rowid = f.fk_soc";
149 if (!empty($conf->global->MAIN_COMPANY_PERENTITY_SHARED)) {
150 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"societe_perentity as spe ON spe.fk_soc = s.rowid AND spe.entity = " . ((int) $conf->entity);
152 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"c_country as co ON co.rowid = s.fk_pays ";
153 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"facturedet as l ON f.rowid = l.fk_facture";
154 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p ON p.rowid = l.fk_product";
155 if (!empty($conf->global->MAIN_PRODUCT_PERENTITY_SHARED)) {
156 $sql .=
" LEFT JOIN " . MAIN_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 ".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;
161 $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;
162 $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;
163 $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;
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>';
318 $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>';
320 print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0, $buttonbind,
'', 0, 1, 1);
323 print
'<div class="div-table-responsive-no-min">';
324 print
'<table class="noborder centpercent">';
325 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
326 print
'<td>'.$langs->trans(
"Label").
'</td>';
327 for ($i = 1; $i <= 12; $i++) {
328 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
333 if ($cursormonth > 12) {
336 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
339 print
'<td width="60" class="right">';
340 if (!empty($tmp[
'mday'])) {
341 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
342 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
343 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/list.php?'.$param.
'">';
345 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
346 if (!empty($tmp[
'mday'])) {
351 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
353 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
354 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
355 for ($i = 1; $i <= 12; $i++) {
356 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
360 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
362 $sql .=
" SUM(fd.total_ht) as total";
363 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
364 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
365 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
366 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
367 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
369 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
370 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
372 $sql .=
" AND f.fk_statut > 0";
373 $sql .=
" AND fd.product_type <= 2";
374 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
375 $sql .=
" AND aa.account_number IS NULL";
376 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
381 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
383 dol_syslog(
'htdocs/accountancy/customer/index.php', LOG_DEBUG);
384 $resql = $db->query(
$sql);
386 $num = $db->num_rows($resql);
388 while ($row = $db->fetch_row($resql)) {
389 print
'<tr class="oddeven">';
391 if ($row[0] ==
'tobind') {
392 print
'<span class="opacitymedium">'.$langs->trans(
"Unknown").
'</span>';
398 if ($row[0] ==
'tobind') {
399 $startmonth = ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1);
400 if ($startmonth > 12) {
403 $startyear = ($startmonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
404 $endmonth = ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + 11;
405 if ($endmonth > 12) {
408 $endyear = ($endmonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
409 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"));
414 for ($i = 2; $i <= 13; $i++) {
415 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
416 if ($cursormonth > 12) {
419 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
422 print
'<td class="right nowraponall amount">';
423 print
price($row[$i]);
426 print
'<a href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&year='.$y.
'&validatemonth='.((int) $cursormonth).
'&validateyear='.((int) $cursoryear).
'&token='.
newToken().
'">';
427 print
img_picto($langs->trans(
"ValidateHistory").
' ('.$langs->trans(
'Month'.str_pad($cursormonth, 2,
'0', STR_PAD_LEFT)).
' '.$cursoryear.
')',
'link',
'class="marginleft2"');
432 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
438 print
'<tr class="oddeven"><td colspan="16">';
439 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
443 print $db->lasterror();
452 print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
455 print
'<div class="div-table-responsive-no-min">';
456 print
'<table class="noborder centpercent">';
457 print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
458 print
'<td>'.$langs->trans(
"Label").
'</td>';
459 for ($i = 1; $i <= 12; $i++) {
460 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
465 if ($cursormonth > 12) {
468 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
471 print
'<td width="60" class="right">';
472 if (!empty($tmp[
'mday'])) {
473 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
474 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
475 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/lines.php?'.$param.
'">';
477 print $langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT));
478 if (!empty($tmp[
'mday'])) {
483 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
485 $sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
486 $sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
487 for ($i = 1; $i <= 12; $i++) {
488 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
492 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
494 $sql .=
" SUM(fd.total_ht) as total";
495 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
496 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
497 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
498 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
499 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
501 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
502 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
504 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
505 $sql .=
" AND f.fk_statut > 0";
506 $sql .=
" AND fd.product_type <= 2";
507 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
512 $sql .=
" AND aa.account_number IS NOT NULL";
513 $sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
514 $sql .=
' ORDER BY aa.account_number';
516 dol_syslog(
'htdocs/accountancy/customer/index.php');
517 $resql = $db->query(
$sql);
519 $num = $db->num_rows($resql);
521 while ($row = $db->fetch_row($resql)) {
522 print
'<tr class="oddeven">';
524 if ($row[0] ==
'tobind') {
525 print $langs->trans(
"Unknown");
532 if ($row[0] ==
'tobind') {
533 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
539 for ($i = 2; $i <= 13; $i++) {
540 $cursormonth = (($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) + $i - 2);
541 if ($cursormonth > 12) {
544 $cursoryear = ($cursormonth < ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1)) ? $y + 1 : $y;
547 print
'<td class="right nowraponall amount">';
548 print
price($row[$i]);
551 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
557 print
'<tr class="oddeven"><td colspan="16">';
558 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
562 print $db->lasterror();
572 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
575 print
'<div class="div-table-responsive-no-min">';
576 print
'<table class="noborder centpercent">';
577 print
'<tr class="liste_titre"><td lass="left">'.$langs->trans(
"TotalVente").
'</td>';
578 for ($i = 1; $i <= 12; $i++) {
579 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
583 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
585 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
587 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalVente")).
"' AS total,";
588 for ($i = 1; $i <= 12; $i++) {
589 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
593 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
"fd.total_ht",
"0").
") AS month".str_pad($j, 2,
"0", STR_PAD_LEFT).
",";
595 $sql .=
" SUM(fd.total_ht) as total";
596 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
597 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
598 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
599 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
601 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
602 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
604 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
605 $sql .=
" AND f.fk_statut > 0";
606 $sql .=
" AND fd.product_type <= 2";
607 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
613 dol_syslog(
'htdocs/accountancy/customer/index.php');
614 $resql = $db->query(
$sql);
616 $num = $db->num_rows($resql);
618 while ($row = $db->fetch_row($resql)) {
619 print
'<tr><td>'.$row[0].
'</td>';
620 for ($i = 1; $i <= 12; $i++) {
621 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
623 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
628 print $db->lasterror();
636 print
'<div class="div-table-responsive-no-min">';
637 print
'<table class="noborder centpercent">';
638 print
'<tr class="liste_titre"><td>'.$langs->trans(
"TotalMarge").
'</td>';
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 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad($j, 2,
'0', STR_PAD_LEFT)).
'</td>';
646 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
647 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
648 for ($i = 1; $i <= 12; $i++) {
649 $j = $i + ($conf->global->SOCIETE_FISCAL_MONTH_START ? $conf->global->SOCIETE_FISCAL_MONTH_START : 1) - 1;
653 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef)=".$j,
654 " (".$db->ifsql(
"fd.total_ht < 0",
655 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
656 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
")",
657 0).
") AS month".str_pad($j, 2,
'0', STR_PAD_LEFT).
",";
659 $sql .=
" SUM(".$db->ifsql(
"fd.total_ht < 0",
660 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
661 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))").
") as total";
663 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
664 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
665 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
666 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
668 if (!empty($conf->global->ACCOUNTING_DATE_START_BINDING)) {
669 $sql .=
" AND f.datef >= '".$db->idate($conf->global->ACCOUNTING_DATE_START_BINDING).
"'";
671 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
672 $sql .=
" AND f.fk_statut > 0";
673 $sql .=
" AND fd.product_type <= 2";
674 if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
679 dol_syslog(
'htdocs/accountancy/customer/index.php');
680 $resql = $db->query(
$sql);
682 $num = $db->num_rows($resql);
684 while ($row = $db->fetch_row($resql)) {
685 print
'<tr><td>'.$row[0].
'</td>';
686 for ($i = 1; $i <= 12; $i++) {
687 print
'<td class="right nowraponall amount">'.price(
price2num($row[$i])).
'</td>';
689 print
'<td class="right nowraponall amount"><b>'.price(
price2num($row[13])).
'</b></td>';
694 print $db->lasterror();