30require
'../../main.inc.php';
31require_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
32require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
33require_once DOL_DOCUMENT_ROOT.
'/core/lib/company.lib.php';
34require_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
35require_once DOL_DOCUMENT_ROOT.
'/accountancy/class/accountingaccount.class.php';
38$langs->loadLangs(array(
"compta",
"bills",
"other",
"accountancy"));
44if (!isModEnabled(
'accounting')) {
47if ($user->socid > 0) {
50if (!$user->hasRight(
'accounting',
'bind',
'write')) {
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');
81if (!isModEnabled(
'accounting')) {
84if ($user->socid > 0) {
87if (!$user->hasRight(
'accounting',
'mouvements',
'lire')) {
96if (($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);
121if ($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,";
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,";
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";
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";
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 = !
getDolGlobalString(
'MAIN_COMPANY_PERENTITY_SHARED') ?
"s" :
"spe";
159 $alias_product_perentity = !
getDolGlobalString(
'MAIN_PRODUCT_PERENTITY_SHARED') ?
"p" :
"ppe";
160 $sql .=
" LEFT JOIN ".$db->prefix().
"accounting_account as aa ON ".$db->sanitize($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 ".$db->sanitize($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 ".$db->sanitize($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 ".$db->sanitize($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).
")";
168 $sql .=
" AND f.datef >= '".$db->idate(
getDolGlobalString(
'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');
302$help_url =
'EN:Module_Double_Entry_Accounting|FR:Module_Comptabilité_en_Partie_Double#Liaisons_comptables';
304llxHeader(
'', $langs->trans(
"CustomersVentilation"), $help_url,
'', 0, 0,
'',
'',
'',
'mod-accountancy accountancy-customer page-index');
306$textprevyear =
'<a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current - 1).
'">'.
img_previous().
'</a>';
307$textnextyear =
' <a href="'.$_SERVER[
"PHP_SELF"].
'?year='.($year_current + 1).
'">'.
img_next().
'</a>';
310print
load_fiche_titre($langs->trans(
"CustomersVentilation").
" ".$textprevyear.
" ".$langs->trans(
"Year").
" ".$year_start.
" ".$textnextyear,
'',
'title_accountancy');
312print
'<span class="opacitymedium">'.$langs->trans(
"DescVentilCustomer").
'</span><br>';
313print
'<span class="opacitymedium hideonsmartphone">'.$langs->trans(
"DescVentilMore", $langs->transnoentitiesnoconv(
"ValidateHistory"), $langs->transnoentitiesnoconv(
"ToBind")).
'<br>';
317 print
info_admin($langs->trans(
"SorryThisModuleIsNotCompatibleWithTheExperimentalFeatureOfSituationInvoices"));
323$buttonbind =
'<a class="button small" href="'.$_SERVER[
'PHP_SELF'].
'?action=validatehistory&token='.newToken().
'">'.
img_picto($langs->trans(
"ValidateHistory"),
'link',
'class="pictofixedwidth fa-color-unset"').$langs->trans(
"ValidateHistory").
'</a>';
325print_barre_liste(
img_picto(
'',
'unlink',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesNotBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1, 0, $buttonbind);
328print
'<div class="div-table-responsive-no-min">';
329print
'<table class="noborder centpercent">';
330print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
331for ($i = 1; $i <= 12; $i++) {
337 if ($cursormonth > 12) {
340 $cursoryear = ($cursormonth <
getDolGlobalInt(
'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((
string) $j, 2,
'0', STR_PAD_LEFT));
350 if (!empty($tmp[
'mday'])) {
355print
'<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,";
359for ($i = 1; $i <= 12; $i++) {
364 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef) = ".((
string) $j),
"fd.total_ht",
"0").
") AS month".str_pad((
string) $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).
"'";
374 $sql .=
" AND f.datef >= '".$db->idate(
getDolGlobalString(
'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";
385$sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
387dol_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') {
407 if ($row[0] ==
'tobind') {
409 if ($startmonth > 12) {
412 $startyear = ($startmonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
414 if ($endmonth > 12) {
417 $endyear = ($endmonth <
getDolGlobalInt(
'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 = (
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1) + $i - 2);
425 if ($cursormonth > 12) {
428 $cursoryear = ($cursormonth <
getDolGlobalInt(
'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((
string) $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="15">';
448 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
452 print $db->lasterror();
461print_barre_liste(
img_picto(
'',
'link',
'class="paddingright fa-color-unset"').$langs->trans(
"OverviewOfAmountOfLinesBound"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
464print
'<div class="div-table-responsive-no-min">';
465print
'<table class="noborder centpercent">';
466print
'<tr class="liste_titre"><td class="minwidth100">'.$langs->trans(
"Account").
'</td>';
467for ($i = 1; $i <= 12; $i++) {
473 if ($cursormonth > 12) {
476 $cursoryear = ($cursormonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
479 print
'<td width="60" class="right">';
480 if (!empty($tmp[
'mday'])) {
481 $param =
'search_date_startday=1&search_date_startmonth='.$cursormonth.
'&search_date_startyear='.$cursoryear;
482 $param .=
'&search_date_endday='.$tmp[
'mday'].
'&search_date_endmonth='.$tmp[
'mon'].
'&search_date_endyear='.$tmp[
'year'];
483 print
'<a href="'.DOL_URL_ROOT.
'/accountancy/customer/lines.php?'.$param.
'">';
485 print $langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT));
486 if (!empty($tmp[
'mday'])) {
491print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
493$sql =
"SELECT ".$db->ifsql(
'aa.account_number IS NULL',
"'tobind'",
'aa.account_number').
" AS codecomptable,";
494$sql .=
" ".$db->ifsql(
'aa.label IS NULL',
"'tobind'",
'aa.label').
" AS intitule,";
495for ($i = 1; $i <= 12; $i++) {
500 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef) = ".((
int) $j),
"fd.total_ht",
"0").
") AS month".str_pad((
string) $j, 2,
"0", STR_PAD_LEFT).
",";
502$sql .=
" SUM(fd.total_ht) as total";
503$sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
504$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
505$sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"accounting_account as aa ON aa.rowid = fd.fk_code_ventilation";
506$sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
507$sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
510 $sql .=
" AND f.datef >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
512$sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
513$sql .=
" AND f.fk_statut > 0";
514$sql .=
" AND fd.product_type <= 2";
520$sql .=
" AND aa.account_number IS NOT NULL";
521$sql .=
" GROUP BY fd.fk_code_ventilation,aa.account_number,aa.label";
522$sql .=
' ORDER BY aa.account_number';
524dol_syslog(
'htdocs/accountancy/customer/index.php');
525$resql = $db->query($sql);
527 $num = $db->num_rows($resql);
529 while ($row = $db->fetch_row($resql)) {
535 print
'<tr class="oddeven">';
536 print
'<td class="tdoverflowmax300"'.(empty($row[1]) ?
'' :
' title="'.dol_escape_htmltag($row[1]).
'"').
'>';
537 if ($row[0] ==
'tobind') {
542 if ($row[0] ==
'tobind') {
543 print $langs->trans(
"UseMenuToSetBindindManualy", DOL_URL_ROOT.
'/accountancy/customer/list.php?search_year='.((
int) $y), $langs->transnoentitiesnoconv(
"ToBind"));
549 for ($i = 2; $i <= 13; $i++) {
550 $cursormonth = (
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1) + $i - 2);
551 if ($cursormonth > 12) {
554 $cursoryear = ($cursormonth <
getDolGlobalInt(
'SOCIETE_FISCAL_MONTH_START', 1)) ? $y + 1 : $y;
557 print
'<td class="right nowraponall amount">';
558 print
price($row[$i]);
561 print
'<td class="right nowraponall amount"><b>'.price($row[14]).
'</b></td>';
567 print
'<tr class="oddeven"><td colspan="15">';
568 print
'<span class="opacitymedium">'.$langs->trans(
"NoRecordFound").
'</span>';
572 print $db->lasterror();
582 print_barre_liste($langs->trans(
"OtherInfo"),
'',
'',
'',
'',
'',
'', -1,
'',
'', 0,
'',
'', 0, 1, 1);
585 print
'<div class="div-table-responsive-no-min">';
586 print
'<table class="noborder centpercent">';
587 print
'<tr class="liste_titre"><td lass="left">'.$langs->trans(
"TotalVente").
'</td>';
588 for ($i = 1; $i <= 12; $i++) {
593 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT)).
'</td>';
595 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
597 $sql =
"SELECT '".$db->escape($langs->trans(
"TotalVente")).
"' AS total,";
598 for ($i = 1; $i <= 12; $i++) {
603 $sql .=
" SUM(".$db->ifsql(
"MONTH(f.datef) = ".((
int) $j),
"fd.total_ht",
"0").
") AS month".str_pad((
string) $j, 2,
"0", STR_PAD_LEFT).
",";
605 $sql .=
" SUM(fd.total_ht) as total";
606 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
607 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
608 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
609 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
612 $sql .=
" AND f.datef >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
614 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
615 $sql .=
" AND f.fk_statut > 0";
616 $sql .=
" AND fd.product_type <= 2";
623 dol_syslog(
'htdocs/accountancy/customer/index.php');
624 $resql = $db->query($sql);
626 $num = $db->num_rows($resql);
628 while ($row = $db->fetch_row($resql)) {
629 print
'<tr><td>'.$row[0].
'</td>';
630 for ($i = 1; $i <= 12; $i++) {
631 print
'<td class="right nowraponall amount">'.price($row[$i]).
'</td>';
633 print
'<td class="right nowraponall amount"><b>'.price($row[13]).
'</b></td>';
638 print $db->lasterror();
643 if (isModEnabled(
'margin')) {
645 print
'<div class="div-table-responsive-no-min">';
646 print
'<table class="noborder centpercent">';
647 print
'<tr class="liste_titre"><td>'.$langs->trans(
"TotalMarge").
'</td>';
648 for ($i = 1; $i <= 12; $i++) {
653 print
'<td width="60" class="right">'.$langs->trans(
'MonthShort'.str_pad((
string) $j, 2,
'0', STR_PAD_LEFT)).
'</td>';
655 print
'<td width="60" class="right"><b>'.$langs->trans(
"Total").
'</b></td></tr>';
659 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
660 for ($i = 1; $i <= 12; $i++) {
665 $sql .=
" SUM(".$db->ifsql(
666 "MONTH(f.datef) = ".((
int) $j),
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)))"
673 ).
") AS month".str_pad((
string) $j, 2,
'0', STR_PAD_LEFT).
",";
675 $sql .=
" SUM(".$db->ifsql(
677 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100))))",
678 " (fd.total_ht - (fd.buy_price_ht * fd.qty * (fd.situation_percent / 100)))"
681 $sql =
"SELECT '".$db->escape($langs->trans(
"Vide")).
"' AS marge,";
682 for ($i = 1; $i <= 12; $i++) {
687 $sql .=
" SUM(".$db->ifsql(
688 "MONTH(f.datef) = ".((
int) $j),
691 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty)))",
692 " (fd.total_ht - (fd.buy_price_ht * fd.qty))"
695 ).
") AS month".str_pad((
string) $j, 2,
'0', STR_PAD_LEFT).
",";
697 $sql .=
" SUM(".$db->ifsql(
699 " (-1 * (abs(fd.total_ht) - (fd.buy_price_ht * fd.qty)))",
700 " (fd.total_ht - (fd.buy_price_ht * fd.qty))"
703 $sql .=
" FROM ".MAIN_DB_PREFIX.
"facturedet as fd";
704 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facture as f ON f.rowid = fd.fk_facture";
705 $sql .=
" WHERE f.datef >= '".$db->idate($search_date_start).
"'";
706 $sql .=
" AND f.datef <= '".$db->idate($search_date_end).
"'";
709 $sql .=
" AND f.datef >= '".$db->idate(
getDolGlobalString(
'ACCOUNTING_DATE_START_BINDING')).
"'";
711 $sql .=
" AND f.entity IN (".getEntity(
'invoice', 0).
")";
712 $sql .=
" AND f.fk_statut > 0";
713 $sql .=
" AND fd.product_type <= 2";
719 dol_syslog(
'htdocs/accountancy/customer/index.php');
720 $resql = $db->query($sql);
722 $num = $db->num_rows($resql);
724 while ($row = $db->fetch_row($resql)) {
725 print
'<tr><td>'.$row[0].
'</td>';
726 for ($i = 1; $i <= 12; $i++) {
727 print
'<td class="right nowraponall amount">'.price(
price2num($row[$i])).
'</td>';
729 print
'<td class="right nowraponall amount"><b>'.price(
price2num($row[13])).
'</b></td>';
734 print $db->lasterror();
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
if(!defined('NOREQUIRESOC')) if(!defined( 'NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined( 'NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined( 'NOREQUIREAJAX')) llxHeader($head='', $title='', $help_url='', $target='', $disablejs=0, $disablehead=0, $arrayofjs='', $arrayofcss='', $morequerystring='', $morecssonbody='', $replacemainareaby='', $disablenofollow=0, $disablenoindex=0)
Empty header.
Class to manage accounting accounts.
Class to manage invoices.
const TYPE_REPLACEMENT
Replacement invoice.
const TYPE_SITUATION
Situation invoice.
const TYPE_DEPOSIT
Deposit invoice.
const TYPE_CREDIT_NOTE
Credit note invoice.
Class to manage invoice lines.
Class to manage products or services.
Class to manage third parties objects (customers, suppliers, prospects...)
isInEEC($object)
Return if a country of an object is inside the EEC (European Economic Community)
dolSqlDateFilter($datefield, $day_date, $month_date, $year_date, $excludefirstand=0, $gm=false)
Generate a SQL string to make a filter into a range (for second of date until last second of date).
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm='auto', $check=1)
Return a timestamp date built from detailed information (by default a local PHP server timestamp) Rep...
load_fiche_titre($title, $morehtmlright='', $picto='generic', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='')
Load a title with picto.
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2)
Show picto whatever it's its name (generic function)
GETPOSTINT($paramname, $method=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
setEventMessage($mesgs, $style='mesgs', $noduplicate=0)
Set event message in dol_events session object.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages.
dol_now($mode='auto')
Return date for now.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
dol_getIdFromCode($db, $key, $tablename, $fieldkey='code', $fieldid='id', $entityfilter=0, $filters='')
Return an id or code from a code or id.
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
img_previous($titlealt='default', $moreatt='')
Show previous logo.
print_barre_liste($title, $page, $file, $options='', $sortfield='', $sortorder='', $morehtmlcenter='', $num=-1, $totalnboflines='', $picto='generic', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limit=-1, $hideselectlimit=0, $hidenavigation=0, $pagenavastextinput=0, $morehtmlrightbeforearrow='')
Print a title with navigation controls for pagination.
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='', $noduplicate=0)
Set event messages in dol_events session object.
img_next($titlealt='default', $moreatt='')
Show next logo.
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
info_admin($text, $infoonimgalt=0, $nodiv=0, $admin='1', $morecss='hideonsmartphone', $textfordropdown='', $picto='')
Show information in HTML for admin users or standard users.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_getdate($timestamp, $fast=false, $forcetimezone='')
Return an array with locale date info.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.