27require
'../main.inc.php';
28require_once DOL_DOCUMENT_ROOT.
'/core/lib/company.lib.php';
29require_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
30require_once DOL_DOCUMENT_ROOT.
'/product/class/product.class.php';
31require_once DOL_DOCUMENT_ROOT.
'/margin/lib/margins.lib.php';
34$langs->loadLangs(array(
'companies',
'bills',
'products',
'margins'));
40$sortfield =
GETPOST(
'sortfield',
'aZ09comma');
41$sortorder =
GETPOST(
'sortorder',
'aZ09comma');
43if (empty($page) || $page == -1) {
46$offset = $limit * $page;
52if ($user->hasRight(
'margins',
'read',
'all')) {
61 $sortfield =
"u.lastname";
65$startdate = $enddate =
'';
74if (!empty($startdatemonth)) {
75 $startdate =
dol_mktime(0, 0, 0, $startdatemonth, $startdateday, $startdateyear);
77if (!empty($enddatemonth)) {
78 $enddate =
dol_mktime(23, 59, 59, $enddatemonth, $enddateday, $enddateyear);
81$hookmanager->initHooks(array(
'marginagentlist'));
101$userstatic =
new User($db);
102$companystatic =
new Societe($db);
103$invoicestatic =
new Facture($db);
105$form =
new Form($db);
107llxHeader(
'', $langs->trans(
"Margins").
' - '.$langs->trans(
"Agents"),
'',
'', 0, 0,
'',
'',
'',
'mod-margin page-agentmargins');
109$text = $langs->trans(
"Margins");
115$titre = $langs->trans(
"Margins");
118print
'<form method="post" name="sel" action="'.$_SERVER[
'PHP_SELF'].
'">';
119print
'<input type="hidden" name="token" value="'.newToken().
'">';
123print
'<table class="border centpercent">';
125print
'<tr><td class="titlefield">'.$langs->trans(
'ContactOfInvoice').
'</td>';
126print
'<td class="maxwidthonsmartphone" colspan="4">';
127print
img_picto(
'',
'user').$form->select_dolusers($agentid,
'agentid', 1,
'', $user->hasRight(
'margins',
'read',
'all') ? 0 : 1,
'',
'', 0, 0, 0,
'', 0,
'',
'maxwidth300');
131print
'<td>'.$langs->trans(
'DateStart').
' ('.$langs->trans(
"DateValidation").
')</td>';
133print $form->selectDate($startdate,
'startdate', 0, 0, 1,
"sel", 1, 1);
135print
'<td>'.$langs->trans(
'DateEnd').
' ('.$langs->trans(
"DateValidation").
')</td>';
137print $form->selectDate($enddate,
'enddate', 0, 0, 1,
"sel", 1, 1);
139print
'<td style="text-align: center;">';
140print
'<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans(
'Refresh')).
'" />';
151$sql .=
" s.rowid as socid, s.nom as name, s.code_client, s.client,";
152$sql .=
" u.rowid as agent, u.login, u.lastname, u.firstname,";
153$sql .=
" sum(d.total_ht) as selling_price,";
156$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,";
157$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";
159$sql .=
" FROM ".MAIN_DB_PREFIX.
"societe as s";
160$sql .=
", ".MAIN_DB_PREFIX.
"facture as f";
161$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);
162$sql .=
", ".MAIN_DB_PREFIX.
"facturedet as d";
163$sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
164$sql .=
", ".MAIN_DB_PREFIX.
"user as u";
165$sql .=
" WHERE f.fk_soc = s.rowid";
166$sql .=
' AND f.entity IN ('.getEntity(
'invoice').
')';
167$sql .=
" AND sc.fk_soc = f.fk_soc";
168$sql .=
" AND (d.product_type = 0 OR d.product_type = 1)";
170 $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))";
172 $sql .=
" AND sc.fk_user = u.rowid";
174$sql .=
" AND f.fk_statut NOT IN (".$db->sanitize(implode(
', ', $invoice_status_except_list)).
")";
175$sql .=
' AND s.entity IN ('.getEntity(
'societe').
')';
176$sql .=
" AND d.fk_facture = f.rowid";
179 $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).
"))";
181 $sql .=
" AND sc.fk_user = ".((int) $agentid);
184if (!empty($startdate)) {
185 $sql .=
" AND f.datef >= '".$db->idate($startdate).
"'";
187if (!empty($enddate)) {
188 $sql .=
" AND f.datef <= '".$db->idate($enddate).
"'";
190$sql .=
" AND d.buy_price_ht IS NOT NULL";
194 $sql .=
" AND d.buy_price_ht <> 0";
198$sql .=
" GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
199$sql .= $db->order($sortfield, $sortorder);
205print
'<span class="opacitymedium">'.$langs->trans(
"MarginPerSaleRepresentativeWarning").
'</span><br>';
208if (!empty($agentid)) {
209 $param .=
"&agentid=".urlencode($agentid);
211if (!empty($startdateday)) {
212 $param .=
"&startdateday=".urlencode((
string) ($startdateday));
214if (!empty($startdatemonth)) {
215 $param .=
"&startdatemonth=".urlencode((
string) ($startdatemonth));
217if (!empty($startdateyear)) {
218 $param .=
"&startdateyear=".urlencode((
string) ($startdateyear));
220if (!empty($enddateday)) {
221 $param .=
"&enddateday=".urlencode((
string) ($enddateday));
223if (!empty($enddatemonth)) {
224 $param .=
"&enddatemonth=".urlencode((
string) ($enddatemonth));
226if (!empty($enddateyear)) {
227 $param .=
"&enddateyear=".urlencode((
string) ($enddateyear));
233dol_syslog(
'margin::agentMargins.php', LOG_DEBUG);
234$result = $db->query($sql);
236 $num = $db->num_rows($result);
240 print_barre_liste($langs->trans(
"MarginDetails"), $page, $_SERVER[
"PHP_SELF"],
"", $sortfield, $sortorder,
'', $num, $num,
'', 0,
'',
'', 0, 1);
243 $labelcostprice =
'BuyingPrice';
245 $labelcostprice =
'CostPrice';
251 print
'<div class="div-table-responsive">';
252 print
'<table class="tagtable liste'.($moreforfilter ?
" listwithfilterbefore" :
"").
'">'.
"\n";
254 print
'<tr class="liste_titre">';
258 print_liste_field_titre(
"SalesRepresentative", $_SERVER[
"PHP_SELF"],
"u.lastname",
"", $param,
'', $sortfield, $sortorder);
261 print_liste_field_titre(
"SellingPrice", $_SERVER[
"PHP_SELF"],
"selling_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
262 print_liste_field_titre($labelcostprice, $_SERVER[
"PHP_SELF"],
"buying_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
263 print_liste_field_titre(
"Margin", $_SERVER[
"PHP_SELF"],
"marge",
"", $param,
'', $sortfield, $sortorder,
'right ');
265 print_liste_field_titre(
"MarginRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
268 print_liste_field_titre(
"MarkRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
273 $group_list = array();
274 while ($objp = $db->fetch_object($result)) {
276 $group_id = $objp->socid;
278 $group_id = $objp->agent;
281 if (!isset($group_list[$group_id])) {
283 $group_name = $objp->name;
284 $companystatic->id = $objp->socid;
285 $companystatic->name = $objp->name;
286 $companystatic->client = $objp->client;
287 $group_htmlname = $companystatic->getNomUrl(1,
'customer');
289 $group_name = $objp->lastname;
290 $userstatic->fetch($objp->agent);
291 $group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
293 $group_list[$group_id] = array(
'name' => $group_name,
'htmlname' => $group_htmlname,
'selling_price' => 0,
'buying_price' => 0,
'marge' => 0);
297 if ($objp->socid > 0) {
299 $sql_seller =
"SELECT COUNT(sc.rowid) as nb";
300 $sql_seller .=
" FROM ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
301 $sql_seller .=
" WHERE sc.fk_soc = ".((int) $objp->socid);
302 $sql_seller .=
" LIMIT 1";
304 $resql_seller = $db->query($sql_seller);
305 if (!$resql_seller) {
308 if ($obj_seller = $db->fetch_object($resql_seller)) {
309 if ($obj_seller->nb > 0) {
310 $seller_nb = $obj_seller->nb;
316 $group_list[$group_id][
'selling_price'] += $objp->selling_price / $seller_nb;
317 $group_list[$group_id][
'buying_price'] += $objp->buying_price / $seller_nb;
318 $group_list[$group_id][
'marge'] += $objp->marge / $seller_nb;
322 if ($sortfield ==
'u.lastname' || $sortfield ==
's.nom') {
325 $group_list =
dol_sort_array($group_list, $sortfield, $sortorder);
328 foreach ($group_list as $group_id => $group_array) {
329 $pa = $group_array[
'buying_price'];
330 $pv = $group_array[
'selling_price'];
331 $marge = $group_array[
'marge'];
333 $marginRate = ($pa != 0) ? (100 * $marge / $pa) :
'';
334 $markRate = ($pv != 0) ? (100 * $marge / $pv) :
'';
336 print
'<tr class="oddeven">';
337 print
"<td>".$group_array[
'htmlname'].
"</td>\n";
338 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pv,
'MT')).
'</span></td>';
339 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pa,
'MT')).
'</span></td>';
340 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($marge,
'MT')).
'</span></td>';
342 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
345 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
356 if (!isset($cumul_achat)) {
359 if (!isset($cumul_vente)) {
362 $totalMargin = $cumul_vente - $cumul_achat;
364 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) :
'';
365 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) :
'';
367 print
'<tr class="liste_total">';
369 print $langs->trans(
'TotalMargin').
"</td>";
370 print
'<td class="nowrap right">'.price(
price2num($cumul_vente,
'MT')).
'</td>';
371 print
'<td class="nowrap right">'.price(
price2num($cumul_achat,
'MT')).
'</td>';
372 print
'<td class="nowrap right">'.price(
price2num($totalMargin,
'MT')).
'</td>';
374 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
377 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
388print
"\n".
'<script type="text/javascript">
389$(document).ready(function() {
390 console.log("Init some values");
391 $("#totalMargin").html("'.
price(
price2num($totalMargin,
'MT')).
'");
392 $("#marginRate").html("'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'");
393 $("#markRate").html("'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'");
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
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 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...
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.
dol_get_fiche_head($links=array(), $active='', $title='', $notab=0, $picto='', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limittoshow=0, $moretabssuffix='', $dragdropfile=0)
Show tabs of a record.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
dol_get_fiche_end($notab=0)
Return tab footer of a card.
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...
print_liste_field_titre($name, $file="", $field="", $begin="", $moreparam="", $moreattrib="", $sortfield="", $sortorder="", $prefix="", $tooltip="", $forcenowrapcolumntitle=0)
Show title line of an array.
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.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
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.
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, $mode=0)
Check permissions of a user to show a page and an object.