29require
'../main.inc.php';
30require_once DOL_DOCUMENT_ROOT.
'/core/lib/company.lib.php';
31require_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
32require_once DOL_DOCUMENT_ROOT.
'/product/class/product.class.php';
33require_once DOL_DOCUMENT_ROOT.
'/margin/lib/margins.lib.php';
44$langs->loadLangs(array(
'companies',
'bills',
'products',
'margins'));
50$sortfield =
GETPOST(
'sortfield',
'aZ09comma');
51$sortorder =
GETPOST(
'sortorder',
'aZ09comma');
53if (empty($page) || $page == -1) {
56$offset = $limit * $page;
62if ($user->hasRight(
'margins',
'read',
'all')) {
71 $sortfield =
"u.lastname";
75$startdate = $enddate =
'';
84if (!empty($startdatemonth)) {
85 $startdate =
dol_mktime(0, 0, 0, $startdatemonth, $startdateday, $startdateyear);
87if (!empty($enddatemonth)) {
88 $enddate =
dol_mktime(23, 59, 59, $enddatemonth, $enddateday, $enddateyear);
91$hookmanager->initHooks(array(
'marginagentlist'));
111$userstatic =
new User($db);
112$companystatic =
new Societe($db);
113$invoicestatic =
new Facture($db);
115$form =
new Form($db);
117llxHeader(
'', $langs->trans(
"Margins").
' - '.$langs->trans(
"Agents"),
'',
'', 0, 0,
'',
'',
'',
'mod-margin page-agentmargins');
119$text = $langs->trans(
"Margins");
125$titre = $langs->trans(
"Margins");
128print
'<form method="post" name="sel" action="'.$_SERVER[
'PHP_SELF'].
'">';
129print
'<input type="hidden" name="token" value="'.newToken().
'">';
133print
'<table class="border centpercent">';
135print
'<tr><td class="titlefield">'.$langs->trans(
'ContactOfInvoice').
'</td>';
136print
'<td class="maxwidthonsmartphone" colspan="4">';
137print img_picto(
'',
'user',
'class="pictofixedwidth"').$form->select_dolusers($agentid,
'agentid', 1,
null, $user->hasRight(
'margins',
'read',
'all') ? 0 : 1,
'',
'',
'0', 0, 0,
'', 0,
'',
'maxwidth300');
141print
'<td>'.$langs->trans(
'DateStart').
' ('.$langs->trans(
"DateValidation").
')</td>';
143print img_picto(
'',
'agenda',
'class="pictofixedwidth"').$form->selectDate($startdate,
'startdate', 0, 0, 1,
"sel", 1, 1);
145print
'<td>'.$langs->trans(
'DateEnd').
' ('.$langs->trans(
"DateValidation").
')</td>';
147print img_picto(
'',
'agenda',
'class="pictofixedwidth"').$form->selectDate($enddate,
'enddate', 0, 0, 1,
"sel", 1, 1);
149print
'<td style="text-align: center;">';
150print
'<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans(
'Refresh')).
'" />';
161$sql .=
" s.rowid as socid, s.nom as name, s.code_client, s.client,";
162$sql .=
" u.rowid as agent, u.login, u.lastname, u.firstname,";
165 $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';
166 $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';
167 $sql .=
" sum($sql_delta_ht) as selling_price,";
169 $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,";
170 $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";
172 $sql .=
" sum(d.total_ht) as selling_price,";
174 $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,";
175 $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";
178$sql .=
" FROM ".MAIN_DB_PREFIX.
"societe as s";
179$sql .=
", ".MAIN_DB_PREFIX.
"facture as f";
180$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);
181$sql .=
", ".MAIN_DB_PREFIX.
"facturedet as d";
183 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"facturedet AS prev_d ON prev_d.rowid = d.fk_prev_id";
185$sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
186$sql .=
", ".MAIN_DB_PREFIX.
"user as u";
187$sql .=
" WHERE f.fk_soc = s.rowid";
188$sql .=
' AND f.entity IN ('.getEntity(
'invoice').
')';
189$sql .=
" AND sc.fk_soc = f.fk_soc";
190$sql .=
" AND (d.product_type = 0 OR d.product_type = 1)";
192 $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))";
194 $sql .=
" AND sc.fk_user = u.rowid";
196$sql .=
" AND f.fk_statut NOT IN (".$db->sanitize(implode(
', ', $invoice_status_except_list)).
")";
197$sql .=
' AND s.entity IN ('.getEntity(
'societe').
')';
198$sql .=
" AND d.fk_facture = f.rowid";
201 $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).
"))";
203 $sql .=
" AND sc.fk_user = ".((int) $agentid);
206if (!empty($startdate)) {
207 $sql .=
" AND f.datef >= '".$db->idate($startdate).
"'";
209if (!empty($enddate)) {
210 $sql .=
" AND f.datef <= '".$db->idate($enddate).
"'";
212$sql .=
" AND d.buy_price_ht IS NOT NULL";
216 $sql .=
" AND d.buy_price_ht <> 0";
220$sql .=
" GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
221$sql .= $db->order($sortfield, $sortorder);
226print
'<div class="info">'.$langs->trans(
"MarginPerSaleRepresentativeWarning").
'</div>';
229if (!empty($agentid)) {
230 $param .=
"&agentid=".urlencode((
string) $agentid);
232if (!empty($startdateday)) {
233 $param .=
"&startdateday=".urlencode((
string) ($startdateday));
235if (!empty($startdatemonth)) {
236 $param .=
"&startdatemonth=".urlencode((
string) ($startdatemonth));
238if (!empty($startdateyear)) {
239 $param .=
"&startdateyear=".urlencode((
string) ($startdateyear));
241if (!empty($enddateday)) {
242 $param .=
"&enddateday=".urlencode((
string) ($enddateday));
244if (!empty($enddatemonth)) {
245 $param .=
"&enddatemonth=".urlencode((
string) ($enddatemonth));
247if (!empty($enddateyear)) {
248 $param .=
"&enddateyear=".urlencode((
string) ($enddateyear));
254dol_syslog(
'margin::agentMargins.php', LOG_DEBUG);
255$result = $db->query($sql);
257 $num = $db->num_rows($result);
261 print_barre_liste($langs->trans(
"MarginDetails"), $page, $_SERVER[
"PHP_SELF"],
"", $sortfield, $sortorder,
'', $num, $num,
'', 0,
'',
'', 0, 1);
264 $labelcostprice =
'BuyingPrice';
266 $labelcostprice =
'CostPrice';
272 print
'<div class="div-table-responsive">';
273 print
'<table class="tagtable noborder nobottomiftotal liste'.($moreforfilter ?
" listwithfilterbefore" :
"").
'">'.
"\n";
275 print
'<tr class="liste_titre">';
279 print_liste_field_titre(
"SalesRepresentative", $_SERVER[
"PHP_SELF"],
"u.lastname",
"", $param,
'', $sortfield, $sortorder);
282 print_liste_field_titre(
"SellingPrice", $_SERVER[
"PHP_SELF"],
"selling_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
283 print_liste_field_titre($labelcostprice, $_SERVER[
"PHP_SELF"],
"buying_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
284 print_liste_field_titre(
"Margin", $_SERVER[
"PHP_SELF"],
"marge",
"", $param,
'', $sortfield, $sortorder,
'right ');
286 print_liste_field_titre(
"MarginRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
289 print_liste_field_titre(
"MarkRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
295 $group_list = array();
296 while ($objp = $db->fetch_object($result)) {
298 $group_id = (int) $objp->socid;
300 $group_id = (int) $objp->agent;
303 if (!isset($group_list[$group_id])) {
305 $group_name = (string) $objp->name;
306 $companystatic->id = $objp->socid;
307 $companystatic->name = $objp->name;
308 $companystatic->client = $objp->client;
309 $group_htmlname = $companystatic->getNomUrl(1,
'customer');
311 $group_name = (string) $objp->lastname;
312 $userstatic->fetch($objp->agent);
313 $group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
315 $group_list[$group_id] = array(
'name' => $group_name,
'htmlname' => $group_htmlname,
'selling_price' => 0,
'buying_price' => 0,
'marge' => 0);
319 if ($objp->socid > 0) {
321 $sql_seller =
"SELECT COUNT(sc.rowid) as nb";
322 $sql_seller .=
" FROM ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
323 $sql_seller .=
" WHERE sc.fk_soc = ".((int) $objp->socid);
324 $sql_seller .=
" LIMIT 1";
326 $resql_seller = $db->query($sql_seller);
327 if (!$resql_seller) {
328 dol_print_error($db);
330 if ($obj_seller = $db->fetch_object($resql_seller)) {
331 if ($obj_seller->nb > 0) {
332 $seller_nb = (int) $obj_seller->nb;
338 $group_list[$group_id][
'selling_price'] += (float) $objp->selling_price / $seller_nb;
339 $group_list[$group_id][
'buying_price'] += (float) $objp->buying_price / $seller_nb;
340 $group_list[$group_id][
'marge'] += (float) $objp->marge / $seller_nb;
344 if ($sortfield ==
'u.lastname' || $sortfield ==
's.nom') {
347 $group_list =
dol_sort_array($group_list, $sortfield, $sortorder);
350 foreach ($group_list as $group_id => $group_array) {
351 $pa = $group_array[
'buying_price'];
352 $pv = $group_array[
'selling_price'];
353 $marge = $group_array[
'marge'];
355 $marginRate = ($pa != 0) ? (100 * $marge / $pa) :
'';
356 $markRate = ($pv != 0) ? (100 * $marge / $pv) :
'';
358 print
'<tr class="oddeven">';
359 print
"<td>".$group_array[
'htmlname'].
"</td>\n";
360 print
'<td class="nowrap right"><span class="amount">'.price(
price2num((
float) $pv,
'MT')).
'</span></td>';
361 print
'<td class="nowrap right"><span class="amount">'.price(
price2num((
float) $pa,
'MT')).
'</span></td>';
362 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($marge,
'MT')).
'</span></td>';
364 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
367 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
378 if (!isset($cumul_achat)) {
381 if (!isset($cumul_vente)) {
384 $totalMargin = $cumul_vente - $cumul_achat;
386 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) :
'';
387 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) :
'';
389 print
'<tr class="liste_total">';
391 print $langs->trans(
'TotalMargin').
"</td>";
392 print
'<td class="nowrap right">'.price(
price2num($cumul_vente,
'MT')).
'</td>';
393 print
'<td class="nowrap right">'.price(
price2num($cumul_achat,
'MT')).
'</td>';
394 print
'<td class="nowrap right">'.price(
price2num($totalMargin,
'MT')).
'</td>';
396 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
399 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
406 dol_print_error($db);
410print
"\n".
'<script type="text/javascript">
411$(document).ready(function() {
412 console.log("Init some values");
413 $("#totalMargin").html("'.
price(
price2num($totalMargin,
'MT')).
'");
414 $("#marginRate").html("'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'");
415 $("#markRate").html("'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'");
if(! $sortfield) if(! $sortorder) $object
llxFooter($comment='', $zone='private', $disabledoutputofmessages=0)
Empty footer.
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.
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $conf
The main.inc.php has been included so the following variable are now defined:
Class to manage invoices.
const STATUS_DRAFT
Draft status.
const STATUS_ABANDONED
Classified abandoned and no payment done.
Class to manage third parties objects (customers, suppliers, prospects...)
Class to manage Dolibarr users.
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...
price2num($amount, $rounding='', $option=0, $currencycode='')
Function that return a number with universal decimal format (decimal separator is '.
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.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
dol_sort_array(&$array, $index, $order='asc', $natsort=0, $case_sensitive=0, $keepindex=0)
Advanced sort array by the value of a given key, which produces ascending (default) or descending out...
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0, $nodefault=0)
Return value of a param into GET or POST supervariable.
GETPOSTINT($paramname, $method=0, $nodefault=0)
Return the value of a $_GET or $_POST supervariable, converted into integer.
GETPOSTISSET($paramname)
Return true if we are in a context of submitting the parameter $paramname from a POST of a form.
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
print_liste_field_titre($name, $file="", $field="", $begin="", $param="", $moreattrib="", $sortfield="", $sortorder="", $prefix="", $tooltip="", $forcenowrapcolumntitle=0)
Show title line of an array.
dol_get_fiche_head($links=array(), $active='', $title='', $notab=0, $picto='', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limittoshow=0, $moretabssuffix='', $dragdropfile=0, $morecssdiv='')
Show tabs of a record.
dol_get_fiche_end($notab=0)
Return tab footer of a card.
marges_prepare_head()
Return array of tabs to used on pages for third parties cards.
restrictedArea(User $user, $features, $object=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $isdraft=0, $nodie=0, $mode='')
Check permissions of a user to show a page and an object.