270 $langs->loadLangs(array(
"main",
"bills",
"companies",
"products"));
272 $limit = isset($args[
'limit']) ? (int) $args[
'limit'] : 50;
276 $groupBy = isset($args[
'group_by']) ? (string) $args[
'group_by'] :
'thirdparty';
283 $dateRange =
" AND f.datef >= '" . $this->db->idate($dateStart)
284 .
"' AND f.datef <= '" . $this->db->idate($dateEnd)
285 .
"' AND f.fk_statut IN (1, 2)";
289 $sql =
"SELECT f.rowid, f.ref, f.total_ttc, f.fk_statut, f.paye, f.datef, s.nom FROM "
290 . MAIN_DB_PREFIX .
"facture as f LEFT JOIN "
291 . MAIN_DB_PREFIX .
"societe as s ON f.fk_soc = s.rowid WHERE f.entity IN ("
294 .
" AND f.fk_soc = " . (int) $socid
295 .
" ORDER BY f.datef DESC LIMIT " . ((
int) $limit);
297 $resql = $this->db->query($sql);
299 while ($r = $this->db->fetch_object($resql)) {
300 $totalSum += (float) $r->total_ttc;
302 $statusLabel = $langs->transnoentitiesnoconv(
"Unknown");
303 if ($r->fk_statut == 1 && $r->paye == 0) {
304 $statusLabel = $langs->transnoentitiesnoconv(
"BillStatusNotPaid");
305 } elseif ($r->fk_statut == 1 && $r->paye == 1) {
306 $statusLabel = $langs->transnoentitiesnoconv(
"BillStatusStarted");
307 } elseif ($r->fk_statut == 2) {
308 $statusLabel = $langs->transnoentitiesnoconv(
"BillStatusPaid");
311 $url = DOL_URL_ROOT .
"/compta/facture/card.php?id=" . $r->rowid;
312 $refHtml =
'<a href="' . $url .
'">' . $r->ref .
'</a>';
315 $langs->transnoentitiesnoconv(
"Ref") => $refHtml,
316 $langs->transnoentitiesnoconv(
"Date") =>
dol_print_date($this->db->jdate($r->datef),
'day'),
317 $langs->transnoentitiesnoconv(
"Customer") => $r->nom,
318 $langs->transnoentitiesnoconv(
"Amount") =>
price($r->total_ttc),
319 $langs->transnoentitiesnoconv(
"Status") => $statusLabel
322 $this->db->free($resql);
328 $sanitizedSqlGroup =
'';
330 $sqlJoin =
" LEFT JOIN " . MAIN_DB_PREFIX .
"societe as s ON f.fk_soc = s.rowid";
332 if ($groupBy ===
'month') {
333 $sanitizedSqlGroup =
"DATE_FORMAT(f.datef, '%Y-%m')";
334 $colName = $langs->transnoentitiesnoconv(
"Month");
335 } elseif ($groupBy ===
'product') {
337 $sanitizedSqlGroup =
"COALESCE(p.ref, fd.description, '?')";
338 $colName = $langs->transnoentitiesnoconv(
"Product");
339 $sqlJoin .=
" INNER JOIN " . MAIN_DB_PREFIX .
"facturedet as fd ON fd.fk_facture = f.rowid LEFT JOIN "
340 . MAIN_DB_PREFIX .
"product as p ON fd.fk_product = p.rowid";
343 $sanitizedSqlGroup =
"s.nom";
344 $colName = $langs->transnoentitiesnoconv(
"Customer");
349 $amountExpr = ($groupBy ===
'product') ?
"SUM(fd.total_ttc)" :
"SUM(f.total_ttc)";
350 $countExpr = ($groupBy ===
'product') ?
"COUNT(DISTINCT f.rowid)" :
"COUNT(f.rowid)";
352 $sql =
"SELECT " . $sanitizedSqlGroup .
" as group_key, "
353 . $amountExpr .
" as total_amount, "
354 . $countExpr .
" as count_inv FROM "
355 . MAIN_DB_PREFIX .
"facture as f"
357 .
" WHERE f.entity IN (" .
getEntity(
'facture') .
")"
359 .
" GROUP BY group_key ORDER BY total_amount DESC LIMIT "
360 . ((int) max(1, $limit));
362 $resql = $this->db->query($sql);
364 while ($r = $this->db->fetch_object($resql)) {
365 $totalSum += (float) $r->total_amount;
366 $chartLabels[] = $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv(
'Unknown');
367 $chartData[] = (float) $r->total_amount;
369 $colName => $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv(
'Unknown'),
370 $langs->transnoentitiesnoconv(
"Number") => (int) $r->count_inv,
371 $langs->transnoentitiesnoconv(
"Amount") =>
price($r->total_amount)
374 $this->db->free($resql);
379 return [[$langs->transnoentitiesnoconv(
"Info") => $langs->transnoentitiesnoconv(
"NoRecordFound")]];
385 $langs->transnoentitiesnoconv(
"Ref") => $langs->transnoentitiesnoconv(
"Total"),
386 $langs->transnoentitiesnoconv(
"Date") =>
"",
387 $langs->transnoentitiesnoconv(
"Customer") =>
"",
388 $langs->transnoentitiesnoconv(
"Amount") =>
price($totalSum),
389 $langs->transnoentitiesnoconv(
"Status") =>
""
393 $langs->transnoentitiesnoconv(
"Total") => $langs->transnoentitiesnoconv(
"Total"),
394 $langs->transnoentitiesnoconv(
"Amount") =>
price($totalSum)
400 if (!empty($chartData)) {
404 'type' => ($groupBy ===
'month' ?
'line' :
'bar'),
405 'labels' => $chartLabels,
406 'datasets' => array(array(
'label' => $langs->transnoentitiesnoconv(
'Amount'),
'data' => $chartData)),
565 $langs->loadLangs(array(
"main",
"bills",
"companies"));
568 $groupBy = isset($args[
'group_by']) ? (string) $args[
'group_by'] :
'supplier';
578 $sql =
"SELECT f.rowid, f.ref, f.total_ttc, f.datef, s.nom
579 FROM " . MAIN_DB_PREFIX .
"facture_fourn as f
580 LEFT JOIN " . MAIN_DB_PREFIX .
"societe as s ON f.fk_soc = s.rowid
581 WHERE f.entity IN (" .
getEntity(
'facture_fourn') .
")
582 AND f.fk_soc = " . (int) $socid .
"
583 AND f.datef >= '" . $this->db->idate($dateStart) .
"'
584 AND f.datef <= '" . $this->db->idate($dateEnd) .
"'
586 ORDER BY f.datef DESC";
588 $resql = $this->db->query($sql);
590 while ($r = $this->db->fetch_object($resql)) {
591 $totalSum += (float) $r->total_ttc;
593 $url = DOL_URL_ROOT .
"/fourn/facture/card.php?id=" . $r->rowid;
594 $refHtml =
'<a href="' . $url .
'">' . $r->ref .
'</a>';
597 $langs->transnoentitiesnoconv(
"Ref") => $refHtml,
598 $langs->transnoentitiesnoconv(
"Date") =>
dol_print_date($this->db->jdate($r->datef),
'day'),
599 $langs->transnoentitiesnoconv(
"Supplier") => $r->nom,
600 $langs->transnoentitiesnoconv(
"Amount") =>
price($r->total_ttc)
603 $this->db->free($resql);
606 $sanitizedSqlGroup =
"";
609 if ($groupBy ===
'month') {
610 $sanitizedSqlGroup =
"DATE_FORMAT(f.datef, '%Y-%m')";
611 $colName = $langs->transnoentitiesnoconv(
"Month");
613 $sanitizedSqlGroup =
"s.nom";
614 $colName = $langs->transnoentitiesnoconv(
"Supplier");
617 $sql =
"SELECT " . $sanitizedSqlGroup .
" as group_key, SUM(f.total_ttc) as total_amount, COUNT(f.rowid) as count_inv
618 FROM " . MAIN_DB_PREFIX .
"facture_fourn as f
619 LEFT JOIN " . MAIN_DB_PREFIX .
"societe as s ON f.fk_soc = s.rowid
620 WHERE f.entity IN (" .
getEntity(
'facture_fourn') .
")
621 AND f.datef >= '" . $this->db->idate($dateStart) .
"'
622 AND f.datef <= '" . $this->db->idate($dateEnd) .
"'
625 ORDER BY total_amount DESC";
627 $resql = $this->db->query($sql);
629 while ($r = $this->db->fetch_object($resql)) {
630 $totalSum += (float) $r->total_amount;
631 $chartLabels[] = $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv(
'Unknown');
632 $chartData[] = (float) $r->total_amount;
634 $colName => $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv(
'Unknown'),
635 $langs->transnoentitiesnoconv(
"Number") => $r->count_inv,
636 $langs->transnoentitiesnoconv(
"Amount") =>
price($r->total_amount)
639 $this->db->free($resql);
644 return [[$langs->transnoentitiesnoconv(
"Info") => $langs->transnoentitiesnoconv(
"NoRecordFound")]];
649 $langs->transnoentitiesnoconv(
"Amount") =>
price($totalSum)
653 $summary[$langs->transnoentitiesnoconv(
"Ref")] = $langs->transnoentitiesnoconv(
"Total");
654 $summary[$langs->transnoentitiesnoconv(
"Date")] =
"";
655 $summary[$langs->transnoentitiesnoconv(
"Supplier")] =
"";
657 $summary[($groupBy ===
'month' ? $langs->transnoentitiesnoconv(
"Month") : $langs->transnoentitiesnoconv(
"Supplier"))] = $langs->transnoentitiesnoconv(
"Total");
658 $summary[$langs->transnoentitiesnoconv(
"Number")] =
"";
663 if (!empty($chartData)) {
667 'type' => ($groupBy ===
'month' ?
'line' :
'bar'),
668 'labels' => $chartLabels,
669 'datasets' => array(array(
'label' => $langs->transnoentitiesnoconv(
'Amount'),
'data' => $chartData)),
687 $langs->loadLangs(array(
"products",
"stocks"));
689 $catId = isset($args[
'category_id']) ? (int) $args[
'category_id'] : 0;
690 $warehouseId = isset($args[
'warehouse_id']) ? (int) $args[
'warehouse_id'] : 0;
691 $includeZero = isset($args[
'include_zero_stock']) ? (bool) $args[
'include_zero_stock'] :
false;
693 $sql =
"SELECT p.rowid, p.ref, p.label, p.pmp, ";
695 if ($warehouseId > 0) {
696 $sql .=
" ps.reel as stock_level ";
697 $sql .=
" FROM " . MAIN_DB_PREFIX .
"product as p ";
698 $sql .=
" LEFT JOIN " . MAIN_DB_PREFIX .
"product_stock as ps ON p.rowid = ps.fk_product ";
699 $sql .=
" WHERE ps.fk_entrepot = " . (int) $warehouseId;
701 $sql .=
" p.stock as stock_level ";
702 $sql .=
" FROM " . MAIN_DB_PREFIX .
"product as p ";
703 $sql .=
" WHERE 1=1 ";
706 $sql .=
" AND p.entity IN (" .
getEntity(
'product') .
")";
709 $sql .=
" AND p.rowid IN (SELECT fk_product FROM " . MAIN_DB_PREFIX .
"categorie_product WHERE fk_categorie = " . (int) $catId .
")";
713 if ($warehouseId > 0) {
714 $sql .=
" AND ps.reel > 0";
716 $sql .=
" AND p.stock > 0";
720 $sql .=
" ORDER BY p.ref ASC LIMIT 200";
722 $resql = $this->db->query($sql);
724 $totalValuation = 0.0;
728 while ($r = $this->db->fetch_object($resql)) {
729 $stockVal = $r->stock_level * $r->pmp;
730 $totalValuation += $stockVal;
731 $totalItems += (int) $r->stock_level;
733 $url = DOL_URL_ROOT .
"/product/card.php?id=" . $r->rowid;
734 $refHtml =
'<a href="' . $url .
'">' . $r->ref .
'</a>';
737 $langs->transnoentitiesnoconv(
"Ref") => $refHtml,
738 $langs->transnoentitiesnoconv(
"Label") => $r->label,
739 $langs->transnoentitiesnoconv(
"Stock") => $r->stock_level,
740 $langs->transnoentitiesnoconv(
"PMPValue") =>
price($r->pmp),
741 $langs->transnoentitiesnoconv(
"TotalValue") =>
price($stockVal)
744 $this->db->free($resql);
748 return [[$langs->transnoentitiesnoconv(
"Info") => $langs->transnoentitiesnoconv(
"NoRecordFound")]];
752 $langs->transnoentitiesnoconv(
"Ref") => $langs->transnoentitiesnoconv(
"Total"),
753 $langs->transnoentitiesnoconv(
"Label") =>
"",
754 $langs->transnoentitiesnoconv(
"Stock") => $totalItems,
755 $langs->transnoentitiesnoconv(
"PMPValue") =>
"",
756 $langs->transnoentitiesnoconv(
"TotalValue") =>
price($totalValuation)
772 $langs->loadLangs(array(
"compta",
"bills"));
777 $sqlIncome =
"SELECT SUM(total_ttc) as total FROM " . MAIN_DB_PREFIX .
"facture
778 WHERE entity IN (" .
getEntity(
'facture') .
")
779 AND datef >= '" . $this->db->idate($dateStart) .
"'
780 AND datef <= '" . $this->db->idate($dateEnd) .
"'
781 AND fk_statut IN (1, 2)";
783 $resIncome = $this->db->query($sqlIncome);
784 $objIncome = $this->db->fetch_object($resIncome);
785 $income = $objIncome && $objIncome->total ? (float) $objIncome->total : 0.0;
788 $sqlExpense =
"SELECT SUM(total_ttc) as total FROM " . MAIN_DB_PREFIX .
"facture_fourn
789 WHERE entity IN (" .
getEntity(
'facture_fourn') .
")
790 AND datef >= '" . $this->db->idate($dateStart) .
"'
791 AND datef <= '" . $this->db->idate($dateEnd) .
"'
794 $resExpense = $this->db->query($sqlExpense);
795 $objExpense = $this->db->fetch_object($resExpense);
796 $expense = $objExpense && $objExpense->total ? (float) $objExpense->total : 0.0;
798 $net = $income - $expense;
802 $langs->transnoentitiesnoconv(
"Category") => $langs->transnoentitiesnoconv(
"Income"),
803 $langs->transnoentitiesnoconv(
"Description") => $langs->transnoentitiesnoconv(
"BillsCustomers"),
804 $langs->transnoentitiesnoconv(
"Amount") =>
price($income)
807 $langs->transnoentitiesnoconv(
"Category") => $langs->transnoentitiesnoconv(
"Expenses"),
808 $langs->transnoentitiesnoconv(
"Description") => $langs->transnoentitiesnoconv(
"BillsSuppliers"),
809 $langs->transnoentitiesnoconv(
"Amount") =>
price($expense)
812 $langs->transnoentitiesnoconv(
"Category") => $langs->transnoentitiesnoconv(
"Total"),
813 $langs->transnoentitiesnoconv(
"Description") => $langs->transnoentitiesnoconv(
"Profit"),
814 $langs->transnoentitiesnoconv(
"Amount") =>
price($net)