vignesh/dolibarr
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
dolibarr/htdocs/margin/agentMargins.php
MDW 3932cd5853
Qual: Update SQL query variables for situation mode in margin reports (#39282) 
# Qual: Update SQL query variables for situation mode in margin reports

In the situation mode, the SQL queries for agentMargins, customerMargins, productMargins, and productMargins tab were updated to use new variable names ($sql_delta_pct and $sql_delta_ht) instead of the old ones ($delta_pct and $delta_ht).

This allows correct analysis by SqlInjection
2026-07-27 13:02:34 +02:00

422 lines
17 KiB
PHP
Raw Permalink Blame History

<?php
/* Copyright (C) 2012-2013 Christophe Battarel <christophe.battarel@altairis.fr>
* Copyright (C) 2014 Ferran Marcet <fmarcet@2byte.es>
* Copyright (C) 2015 Marcos García <marcosgdf@gmail.com>
* Copyright (C) 2024-2026 MDW <mdeweerd@users.noreply.github.com>
* Copyright (C) 2024-2025 Frédéric France <frederic.france@free.fr>
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
/**
* \file htdocs/margin/agentMargins.php
* \ingroup margin
* \brief Page des marges par agent commercial
*/
// Load Dolibarr environment
require '../main.inc.php';
require_once DOL_DOCUMENT_ROOT.'/core/lib/company.lib.php';
require_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php';
require_once DOL_DOCUMENT_ROOT.'/product/class/product.class.php';
require_once DOL_DOCUMENT_ROOT.'/margin/lib/margins.lib.php';
/**
* @var Conf $conf
* @var DoliDB $db
* @var HookManager $hookmanager
* @var Translate $langs
* @var User $user
*/
// Load translation files required by the page
$langs->loadLangs(array('companies', 'bills', 'products', 'margins'));
$mesg = '';
// Load variable for pagination
$limit = GETPOSTINT('limit') ? GETPOSTINT('limit') : $conf->liste_limit;
$sortfield = GETPOST('sortfield', 'aZ09comma');
$sortorder = GETPOST('sortorder', 'aZ09comma');
$page = GETPOSTISSET('pageplusone') ? (GETPOSTINT('pageplusone') - 1) : GETPOSTINT("page");
if (empty($page) || $page == -1) {
$page = 0;
} // If $page is not defined, or '' or -1
$offset = $limit * $page;
$pageprev = $page - 1;
$pagenext = $page + 1;
if (!$sortorder) {
$sortorder = "ASC";
}
if ($user->hasRight('margins', 'read', 'all')) {
$agentid = GETPOSTINT('agentid');
} else {
$agentid = $user->id;
}
if (!$sortfield) {
if ($agentid > 0) {
$sortfield = "s.nom";
} else {
$sortfield = "u.lastname";
}
}
$startdate = $enddate = '';
$startdateday = GETPOSTINT('startdateday');
$startdatemonth = GETPOSTINT('startdatemonth');
$startdateyear = GETPOSTINT('startdateyear');
$enddateday = GETPOSTINT('enddateday');
$enddatemonth = GETPOSTINT('enddatemonth');
$enddateyear = GETPOSTINT('enddateyear');
if (!empty($startdatemonth)) {
$startdate = dol_mktime(0, 0, 0, $startdatemonth, $startdateday, $startdateyear);
}
if (!empty($enddatemonth)) {
$enddate = dol_mktime(23, 59, 59, $enddatemonth, $enddateday, $enddateyear);
}
$hookmanager->initHooks(array('marginagentlist'));
// Security check
$result = restrictedArea($user, 'margins');
// Initialize a technical object to manage hooks of page. Note that conf->hooks_modules contains an array of hook context
$object = new User($db);
/*
* Actions
*/
// None
/*
* View
*/
$userstatic = new User($db);
$companystatic = new Societe($db);
$invoicestatic = new Facture($db);
$form = new Form($db);
llxHeader('', $langs->trans("Margins").' - '.$langs->trans("Agents"), '', '', 0, 0, '', '', '', 'mod-margin page-agentmargins');
$text = $langs->trans("Margins");
//print load_fiche_titre($text);
// Show tabs
$head = marges_prepare_head();
$titre = $langs->trans("Margins");
$picto = 'margin';
print '<form method="post" name="sel" action="'.$_SERVER['PHP_SELF'].'">';
print '<input type="hidden" name="token" value="'.newToken().'">';
print dol_get_fiche_head($head, 'agentMargins', $titre, 0, $picto);
print '<table class="border centpercent">';
print '<tr><td class="titlefield">'.$langs->trans('ContactOfInvoice').'</td>';
print '<td class="maxwidthonsmartphone" colspan="4">';
print img_picto('', 'user', 'class="pictofixedwidth"').$form->select_dolusers($agentid, 'agentid', 1, null, $user->hasRight('margins', 'read', 'all') ? 0 : 1, '', '', '0', 0, 0, '', 0, '', 'maxwidth300');
print '</td></tr>';
// Start date
print '<td>'.$langs->trans('DateStart').' ('.$langs->trans("DateValidation").')</td>';
print '<td>';
print img_picto('', 'agenda', 'class="pictofixedwidth"').$form->selectDate($startdate, 'startdate', 0, 0, 1, "sel", 1, 1);
print '</td>';
print '<td>'.$langs->trans('DateEnd').' ('.$langs->trans("DateValidation").')</td>';
print '<td>';
print img_picto('', 'agenda', 'class="pictofixedwidth"').$form->selectDate($enddate, 'enddate', 0, 0, 1, "sel", 1, 1);
print '</td>';
print '<td style="text-align: center;">';
print '<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans('Refresh')).'" />';
print '</td></tr>';
print "</table>";
print dol_get_fiche_end();
print '</form>';
$invoice_status_except_list = array(Facture::STATUS_DRAFT, Facture::STATUS_ABANDONED);
$sql = "SELECT";
$sql .= " s.rowid as socid, s.nom as name, s.code_client, s.client,";
$sql .= " u.rowid as agent, u.login, u.lastname, u.firstname,";
// Special case for old situation mode: total_ht is stored cumulatively, use delta percent to avoid cumulating margins
if (getDolGlobalInt('INVOICE_USE_SITUATION') == 1) {
$sql_delta_pct = 'CASE WHEN f.type = '.Facture::TYPE_SITUATION.' AND d.situation_percent > 0 THEN (d.situation_percent - COALESCE(prev_d.situation_percent, 0)) ELSE d.situation_percent END';
$sql_delta_ht = 'CASE WHEN f.type = '.Facture::TYPE_SITUATION.' AND d.situation_percent > 0 THEN d.total_ht * ((d.situation_percent - COALESCE(prev_d.situation_percent, 0)) / d.situation_percent) ELSE d.total_ht END';
$sql .= " sum($sql_delta_ht) as selling_price,";
// Note: qty and buy_price_ht is always positive (if not your database may be corrupted, you can update this)
$sql .= " sum(".$db->ifsql('(d.total_ht < 0 OR (d.total_ht = 0 AND f.type = 2))', "-1 * d.qty * d.buy_price_ht * ($sql_delta_pct / 100)", "d.qty * d.buy_price_ht * ($sql_delta_pct / 100)").") as buying_price,";
$sql .= " sum(".$db->ifsql('(d.total_ht < 0 OR (d.total_ht = 0 AND f.type = 2))', "-1 * (abs($sql_delta_ht) - (d.buy_price_ht * d.qty * ($sql_delta_pct / 100)))", "$sql_delta_ht - (d.buy_price_ht * d.qty * ($sql_delta_pct / 100))").") as marge";
} else {
$sql .= " sum(d.total_ht) as selling_price,";
// Note: qty and buy_price_ht is always positive (if not your database may be corrupted, you can update this)
$sql .= " sum(".$db->ifsql('(d.total_ht < 0 OR (d.total_ht = 0 AND f.type = 2))', '-1 * d.qty * d.buy_price_ht * (d.situation_percent / 100)', 'd.qty * d.buy_price_ht * (d.situation_percent / 100)').") as buying_price,";
$sql .= " sum(".$db->ifsql('(d.total_ht < 0 OR (d.total_ht = 0 AND f.type = 2))', '-1 * (abs(d.total_ht) - (d.buy_price_ht * d.qty * (d.situation_percent / 100)))', 'd.total_ht - (d.buy_price_ht * d.qty * (d.situation_percent / 100))').") as marge";
}
$sql .= " FROM ".MAIN_DB_PREFIX."societe as s";
$sql .= ", ".MAIN_DB_PREFIX."facture as f";
$sql .= " LEFT JOIN ".MAIN_DB_PREFIX."element_contact e ON e.element_id = f.rowid and e.statut = 4 and e.fk_c_type_contact = ".(!getDolGlobalString('AGENT_CONTACT_TYPE') ? -1 : $conf->global->AGENT_CONTACT_TYPE);
$sql .= ", ".MAIN_DB_PREFIX."facturedet as d";
if (getDolGlobalInt('INVOICE_USE_SITUATION') == 1) {
$sql .= " LEFT JOIN ".MAIN_DB_PREFIX."facturedet AS prev_d ON prev_d.rowid = d.fk_prev_id";
}
$sql .= ", ".MAIN_DB_PREFIX."societe_commerciaux as sc";
$sql .= ", ".MAIN_DB_PREFIX."user as u";
$sql .= " WHERE f.fk_soc = s.rowid";
$sql .= ' AND f.entity IN ('.getEntity('invoice').')';
$sql .= " AND sc.fk_soc = f.fk_soc";
$sql .= " AND (d.product_type = 0 OR d.product_type = 1)";
if (getDolGlobalString('AGENT_CONTACT_TYPE')) {
$sql .= " AND ((e.fk_socpeople IS NULL AND sc.fk_user = u.rowid) OR (e.fk_socpeople IS NOT NULL AND e.fk_socpeople = u.rowid))";
} else {
$sql .= " AND sc.fk_user = u.rowid";
}
$sql .= " AND f.fk_statut NOT IN (".$db->sanitize(implode(', ', $invoice_status_except_list)).")";
$sql .= ' AND s.entity IN ('.getEntity('societe').')';
$sql .= " AND d.fk_facture = f.rowid";
if ($agentid > 0) {
if (getDolGlobalString('AGENT_CONTACT_TYPE')) {
$sql .= " AND ((e.fk_socpeople IS NULL AND sc.fk_user = ".((int) $agentid).") OR (e.fk_socpeople IS NOT NULL AND e.fk_socpeople = ".((int) $agentid)."))";
} else {
$sql .= " AND sc.fk_user = ".((int) $agentid);
}
}
if (!empty($startdate)) {
$sql .= " AND f.datef >= '".$db->idate($startdate)."'";
}
if (!empty($enddate)) {
$sql .= " AND f.datef <= '".$db->idate($enddate)."'";
}
$sql .= " AND d.buy_price_ht IS NOT NULL";
// We should not use this here. Option ForceBuyingPriceIfNull should have effect only when inserting data. Once data is recorded, it must be used as it is for report.
// We keep it with value ForceBuyingPriceIfNull = 2 for retroactive effect but results are unpredictable.
if (getDolGlobalInt('ForceBuyingPriceIfNull') == 2) {
$sql .= " AND d.buy_price_ht <> 0";
}
//if ($agentid > 0) $sql.= " GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
//else $sql.= " GROUP BY u.rowid, u.login, u.lastname, u.firstname";
$sql .= " GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
$sql .= $db->order($sortfield, $sortorder);
// TODO: calculate total to display then restore pagination
//$sql.= $db->plimit($conf->liste_limit +1, $offset);
print '<br>';
print '<span class="opacitymedium">'.$langs->trans("MarginPerSaleRepresentativeWarning").'</span><br>';
$param = '';
if (!empty($agentid)) {
$param .= "&amp;agentid=".urlencode((string) $agentid);
}
if (!empty($startdateday)) {
$param .= "&amp;startdateday=".urlencode((string) ($startdateday));
}
if (!empty($startdatemonth)) {
$param .= "&amp;startdatemonth=".urlencode((string) ($startdatemonth));
}
if (!empty($startdateyear)) {
$param .= "&amp;startdateyear=".urlencode((string) ($startdateyear));
}
if (!empty($enddateday)) {
$param .= "&amp;enddateday=".urlencode((string) ($enddateday));
}
if (!empty($enddatemonth)) {
$param .= "&amp;enddatemonth=".urlencode((string) ($enddatemonth));
}
if (!empty($enddateyear)) {
$param .= "&amp;enddateyear=".urlencode((string) ($enddateyear));
}
$totalMargin = 0;
$marginRate = '';
$markRate = '';
dol_syslog('margin::agentMargins.php', LOG_DEBUG);
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
print '<br>';
// @phan-suppress-next-line PhanPluginSuspiciousParamPosition, PhanPluginSuspiciousParamOrder
print_barre_liste($langs->trans("MarginDetails"), $page, $_SERVER["PHP_SELF"], "", $sortfield, $sortorder, '', $num, $num, '', 0, '', '', 0, 1);
if (getDolGlobalString('MARGIN_TYPE') == "1") {
$labelcostprice = 'BuyingPrice';
} else { // value is 'costprice' or 'pmp'
$labelcostprice = 'CostPrice';
}
$moreforfilter = '';
$i = 0;
print '<div class="div-table-responsive">';
print '<table class="tagtable noborder nobottomiftotal liste'.($moreforfilter ? " listwithfilterbefore" : "").'">'."\n";
print '<tr class="liste_titre">';
if ($agentid > 0) {
print_liste_field_titre("Customer", $_SERVER["PHP_SELF"], "s.nom", "", $param, '', $sortfield, $sortorder);
} else {
print_liste_field_titre("SalesRepresentative", $_SERVER["PHP_SELF"], "u.lastname", "", $param, '', $sortfield, $sortorder);
}
print_liste_field_titre("SellingPrice", $_SERVER["PHP_SELF"], "selling_price", "", $param, '', $sortfield, $sortorder, 'right ');
print_liste_field_titre($labelcostprice, $_SERVER["PHP_SELF"], "buying_price", "", $param, '', $sortfield, $sortorder, 'right ');
print_liste_field_titre("Margin", $_SERVER["PHP_SELF"], "marge", "", $param, '', $sortfield, $sortorder, 'right ');
if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
print_liste_field_titre("MarginRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
}
if (getDolGlobalString('DISPLAY_MARK_RATES')) {
print_liste_field_titre("MarkRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
}
print "</tr>\n";
if ($num > 0) {
/** @var array<int,array{name:string,htmlname:string,selling_price:float,buying_price:float,marge:float}> $group_list */
$group_list = array();
while ($objp = $db->fetch_object($result)) {
if ($agentid > 0) {
$group_id = (int) $objp->socid;
} else {
$group_id = (int) $objp->agent;
}
if (!isset($group_list[$group_id])) {
if ($agentid > 0) {
$group_name = (string) $objp->name;
$companystatic->id = $objp->socid;
$companystatic->name = $objp->name;
$companystatic->client = $objp->client;
$group_htmlname = $companystatic->getNomUrl(1, 'customer');
} else {
$group_name = (string) $objp->lastname;
$userstatic->fetch($objp->agent);
$group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
}
$group_list[$group_id] = array('name' => $group_name, 'htmlname' => $group_htmlname, 'selling_price' => 0, 'buying_price' => 0, 'marge' => 0);
}
$seller_nb = 1;
if ($objp->socid > 0) {
// sql nb sellers
$sql_seller = "SELECT COUNT(sc.rowid) as nb";
$sql_seller .= " FROM ".MAIN_DB_PREFIX."societe_commerciaux as sc";
$sql_seller .= " WHERE sc.fk_soc = ".((int) $objp->socid);
$sql_seller .= " LIMIT 1";
$resql_seller = $db->query($sql_seller);
if (!$resql_seller) {
dol_print_error($db);
} else {
if ($obj_seller = $db->fetch_object($resql_seller)) {
if ($obj_seller->nb > 0) {
$seller_nb = (int) $obj_seller->nb;
}
}
}
}
$group_list[$group_id]['selling_price'] += (float) $objp->selling_price / $seller_nb;
$group_list[$group_id]['buying_price'] += (float) $objp->buying_price / $seller_nb;
$group_list[$group_id]['marge'] += (float) $objp->marge / $seller_nb;
}
// sort group array by sortfield
if ($sortfield == 'u.lastname' || $sortfield == 's.nom') {
$sortfield = 'name';
}
$group_list = dol_sort_array($group_list, $sortfield, $sortorder);
$cumul_achat = 0;
$cumul_vente = 0;
foreach ($group_list as $group_id => $group_array) {
$pa = $group_array['buying_price'];
$pv = $group_array['selling_price'];
$marge = $group_array['marge'];
$marginRate = ($pa != 0) ? (100 * $marge / $pa) : '';
$markRate = ($pv != 0) ? (100 * $marge / $pv) : '';
print '<tr class="oddeven">';
print "<td>".$group_array['htmlname']."</td>\n";
print '<td class="nowrap right"><span class="amount">'.price(price2num((float) $pv, 'MT')).'</span></td>';
print '<td class="nowrap right"><span class="amount">'.price(price2num((float) $pa, 'MT')).'</span></td>';
print '<td class="nowrap right"><span class="amount">'.price(price2num($marge, 'MT')).'</span></td>';
if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
}
if (getDolGlobalString('DISPLAY_MARK_RATES')) {
print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
}
print "</tr>\n";
$i++;
$cumul_achat += $pa;
$cumul_vente += $pv;
}
}
// Show total margin
if (!isset($cumul_achat)) {
$cumul_achat = 0;
}
if (!isset($cumul_vente)) {
$cumul_vente = 0;
}
$totalMargin = $cumul_vente - $cumul_achat;
$marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) : '';
$markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) : '';
print '<tr class="liste_total">';
print '<td>';
print $langs->trans('TotalMargin')."</td>";
print '<td class="nowrap right">'.price(price2num($cumul_vente, 'MT')).'</td>';
print '<td class="nowrap right">'.price(price2num($cumul_achat, 'MT')).'</td>';
print '<td class="nowrap right">'.price(price2num($totalMargin, 'MT')).'</td>';
if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
}
if (getDolGlobalString('DISPLAY_MARK_RATES')) {
print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
}
print '</tr>';
print '</table>';
print '</div>';
} else {
dol_print_error($db);
}
$db->free($result);
print "\n".'<script type="text/javascript">
$(document).ready(function() {
console.log("Init some values");
$("#totalMargin").html("'.price(price2num($totalMargin, 'MT')).'");
$("#marginRate").html("'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'");
$("#markRate").html("'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'");
});
</script>'."\n";
// End of page
llxFooter();
$db->close();
Reference in a new issue View git blame Copy permalink