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').$form->select_dolusers($agentid,
'agentid', 1,
'', $user->hasRight(
'margins',
'read',
'all') ? 0 : 1,
'',
'', 0, 0, 0,
'', 0,
'',
'maxwidth300');
141print
'<td>'.$langs->trans(
'DateStart').
' ('.$langs->trans(
"DateValidation").
')</td>';
143print $form->selectDate($startdate,
'startdate', 0, 0, 1,
"sel", 1, 1);
145print
'<td>'.$langs->trans(
'DateEnd').
' ('.$langs->trans(
"DateValidation").
')</td>';
147print $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,";
163$sql .=
" sum(d.total_ht) as selling_price,";
166$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,";
167$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";
169$sql .=
" FROM ".MAIN_DB_PREFIX.
"societe as s";
170$sql .=
", ".MAIN_DB_PREFIX.
"facture as f";
171$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);
172$sql .=
", ".MAIN_DB_PREFIX.
"facturedet as d";
173$sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
174$sql .=
", ".MAIN_DB_PREFIX.
"user as u";
175$sql .=
" WHERE f.fk_soc = s.rowid";
176$sql .=
' AND f.entity IN ('.getEntity(
'invoice').
')';
177$sql .=
" AND sc.fk_soc = f.fk_soc";
178$sql .=
" AND (d.product_type = 0 OR d.product_type = 1)";
180 $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))";
182 $sql .=
" AND sc.fk_user = u.rowid";
184$sql .=
" AND f.fk_statut NOT IN (".$db->sanitize(implode(
', ', $invoice_status_except_list)).
")";
185$sql .=
' AND s.entity IN ('.getEntity(
'societe').
')';
186$sql .=
" AND d.fk_facture = f.rowid";
189 $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).
"))";
191 $sql .=
" AND sc.fk_user = ".((int) $agentid);
194if (!empty($startdate)) {
195 $sql .=
" AND f.datef >= '".$db->idate($startdate).
"'";
197if (!empty($enddate)) {
198 $sql .=
" AND f.datef <= '".$db->idate($enddate).
"'";
200$sql .=
" AND d.buy_price_ht IS NOT NULL";
204 $sql .=
" AND d.buy_price_ht <> 0";
208$sql .=
" GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
209$sql .= $db->order($sortfield, $sortorder);
215print
'<span class="opacitymedium">'.$langs->trans(
"MarginPerSaleRepresentativeWarning").
'</span><br>';
218if (!empty($agentid)) {
219 $param .=
"&agentid=".urlencode((
string) $agentid);
221if (!empty($startdateday)) {
222 $param .=
"&startdateday=".urlencode((
string) ($startdateday));
224if (!empty($startdatemonth)) {
225 $param .=
"&startdatemonth=".urlencode((
string) ($startdatemonth));
227if (!empty($startdateyear)) {
228 $param .=
"&startdateyear=".urlencode((
string) ($startdateyear));
230if (!empty($enddateday)) {
231 $param .=
"&enddateday=".urlencode((
string) ($enddateday));
233if (!empty($enddatemonth)) {
234 $param .=
"&enddatemonth=".urlencode((
string) ($enddatemonth));
236if (!empty($enddateyear)) {
237 $param .=
"&enddateyear=".urlencode((
string) ($enddateyear));
243dol_syslog(
'margin::agentMargins.php', LOG_DEBUG);
244$result = $db->query($sql);
246 $num = $db->num_rows($result);
250 print_barre_liste($langs->trans(
"MarginDetails"), $page, $_SERVER[
"PHP_SELF"],
"", $sortfield, $sortorder,
'', $num, $num,
'', 0,
'',
'', 0, 1);
253 $labelcostprice =
'BuyingPrice';
255 $labelcostprice =
'CostPrice';
261 print
'<div class="div-table-responsive">';
262 print
'<table class="tagtable liste'.($moreforfilter ?
" listwithfilterbefore" :
"").
'">'.
"\n";
264 print
'<tr class="liste_titre">';
268 print_liste_field_titre(
"SalesRepresentative", $_SERVER[
"PHP_SELF"],
"u.lastname",
"", $param,
'', $sortfield, $sortorder);
271 print_liste_field_titre(
"SellingPrice", $_SERVER[
"PHP_SELF"],
"selling_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
272 print_liste_field_titre($labelcostprice, $_SERVER[
"PHP_SELF"],
"buying_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
273 print_liste_field_titre(
"Margin", $_SERVER[
"PHP_SELF"],
"marge",
"", $param,
'', $sortfield, $sortorder,
'right ');
275 print_liste_field_titre(
"MarginRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
278 print_liste_field_titre(
"MarkRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
283 $group_list = array();
284 while ($objp = $db->fetch_object($result)) {
286 $group_id = $objp->socid;
288 $group_id = $objp->agent;
291 if (!isset($group_list[$group_id])) {
293 $group_name = $objp->name;
294 $companystatic->id = $objp->socid;
295 $companystatic->name = $objp->name;
296 $companystatic->client = $objp->client;
297 $group_htmlname = $companystatic->getNomUrl(1,
'customer');
299 $group_name = $objp->lastname;
300 $userstatic->fetch($objp->agent);
301 $group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
303 $group_list[$group_id] = array(
'name' => $group_name,
'htmlname' => $group_htmlname,
'selling_price' => 0,
'buying_price' => 0,
'marge' => 0);
307 if ($objp->socid > 0) {
309 $sql_seller =
"SELECT COUNT(sc.rowid) as nb";
310 $sql_seller .=
" FROM ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
311 $sql_seller .=
" WHERE sc.fk_soc = ".((int) $objp->socid);
312 $sql_seller .=
" LIMIT 1";
314 $resql_seller = $db->query($sql_seller);
315 if (!$resql_seller) {
318 if ($obj_seller = $db->fetch_object($resql_seller)) {
319 if ($obj_seller->nb > 0) {
320 $seller_nb = $obj_seller->nb;
326 $group_list[$group_id][
'selling_price'] += $objp->selling_price / $seller_nb;
327 $group_list[$group_id][
'buying_price'] += $objp->buying_price / $seller_nb;
328 $group_list[$group_id][
'marge'] += $objp->marge / $seller_nb;
332 if ($sortfield ==
'u.lastname' || $sortfield ==
's.nom') {
335 $group_list =
dol_sort_array($group_list, $sortfield, $sortorder);
338 foreach ($group_list as $group_id => $group_array) {
339 $pa = $group_array[
'buying_price'];
340 $pv = $group_array[
'selling_price'];
341 $marge = $group_array[
'marge'];
343 $marginRate = ($pa != 0) ? (100 * $marge / $pa) :
'';
344 $markRate = ($pv != 0) ? (100 * $marge / $pv) :
'';
346 print
'<tr class="oddeven">';
347 print
"<td>".$group_array[
'htmlname'].
"</td>\n";
348 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pv,
'MT')).
'</span></td>';
349 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pa,
'MT')).
'</span></td>';
350 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($marge,
'MT')).
'</span></td>';
352 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
355 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
366 if (!isset($cumul_achat)) {
369 if (!isset($cumul_vente)) {
372 $totalMargin = $cumul_vente - $cumul_achat;
374 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) :
'';
375 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) :
'';
377 print
'<tr class="liste_total">';
379 print $langs->trans(
'TotalMargin').
"</td>";
380 print
'<td class="nowrap right">'.price(
price2num($cumul_vente,
'MT')).
'</td>';
381 print
'<td class="nowrap right">'.price(
price2num($cumul_achat,
'MT')).
'</td>';
382 print
'<td class="nowrap right">'.price(
price2num($totalMargin,
'MT')).
'</td>';
384 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
387 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
398print
"\n".
'<script type="text/javascript">
399$(document).ready(function() {
400 console.log("Init some values");
401 $("#totalMargin").html("'.
price(
price2num($totalMargin,
'MT')).
'");
402 $("#marginRate").html("'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'");
403 $("#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...
print_barre_liste($title, $page, $file, $options='', $sortfield='', $sortorder='', $morehtmlcenter='', $num=-1, $totalnboflines='', $picto='generic', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limit=-1, $selectlimitsuffix=0, $hidenavigation=0, $pagenavastextinput=0, $morehtmlrightbeforearrow='')
Print a title with navigation controls for pagination.
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.
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.
global $conf
The following vars must be defined: $type2label $form $conf, $lang, The following vars may also be de...
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.