27 require
'../main.inc.php';
28 require_once DOL_DOCUMENT_ROOT.
'/core/lib/company.lib.php';
29 require_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
30 require_once DOL_DOCUMENT_ROOT.
'/product/class/product.class.php';
31 require_once DOL_DOCUMENT_ROOT.
'/margin/lib/margins.lib.php';
34 $langs->loadLangs(array(
'companies',
'bills',
'products',
'margins'));
39 $limit =
GETPOST(
'limit',
'int') ?
GETPOST(
'limit',
'int') : $conf->liste_limit;
40 $sortfield =
GETPOST(
'sortfield',
'aZ09comma');
41 $sortorder =
GETPOST(
'sortorder',
'aZ09comma');
43 if (empty($page) || $page == -1) {
46 $offset = $limit * $page;
47 $pageprev = $page - 1;
48 $pagenext = $page + 1;
52 if ($user->rights->margins->read->all) {
53 $agentid =
GETPOST(
'agentid',
'int');
61 $sortfield =
"u.lastname";
65 $startdate = $enddate =
'';
67 $startdateday =
GETPOST(
'startdateday',
'int');
68 $startdatemonth =
GETPOST(
'startdatemonth',
'int');
69 $startdateyear =
GETPOST(
'startdateyear',
'int');
70 $enddateday =
GETPOST(
'enddateday',
'int');
71 $enddatemonth =
GETPOST(
'enddatemonth',
'int');
72 $enddateyear =
GETPOST(
'enddateyear',
'int');
74 if (!empty($startdatemonth)) {
75 $startdate =
dol_mktime(0, 0, 0, $startdatemonth, $startdateday, $startdateyear);
77 if (!empty($enddatemonth)) {
78 $enddate =
dol_mktime(23, 59, 59, $enddatemonth, $enddateday, $enddateyear);
85 $object =
new User($db);
86 $hookmanager->initHooks(array(
'marginagentlist'));
100 $userstatic =
new User($db);
101 $companystatic =
new Societe($db);
102 $invoicestatic =
new Facture($db);
106 llxHeader(
'', $langs->trans(
"Margins").
' - '.$langs->trans(
"Agents"));
108 $text = $langs->trans(
"Margins");
114 $titre = $langs->trans(
"Margins");
117 print
'<form method="post" name="sel" action="'.$_SERVER[
'PHP_SELF'].
'">';
118 print
'<input type="hidden" name="token" value="'.newToken().
'">';
122 print
'<table class="border centpercent">';
124 print
'<tr><td class="titlefield">'.$langs->trans(
'ContactOfInvoice').
'</td>';
125 print
'<td class="maxwidthonsmartphone" colspan="4">';
126 print
img_picto(
'',
'user').$form->select_dolusers($agentid,
'agentid', 1,
'', $user->rights->margins->read->all ? 0 : 1,
'',
'', 0, 0, 0,
'', 0,
'',
'maxwidth300');
130 print
'<td>'.$langs->trans(
'DateStart').
' ('.$langs->trans(
"DateValidation").
')</td>';
132 print
$form->selectDate($startdate,
'startdate',
'',
'', 1,
"sel", 1, 1);
134 print
'<td>'.$langs->trans(
'DateEnd').
' ('.$langs->trans(
"DateValidation").
')</td>';
136 print
$form->selectDate($enddate,
'enddate',
'',
'', 1,
"sel", 1, 1);
138 print
'<td style="text-align: center;">';
139 print
'<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans(
'Refresh')).
'" />';
150 $sql .=
" s.rowid as socid, s.nom as name, s.code_client, s.client,";
151 $sql .=
" u.rowid as agent, u.login, u.lastname, u.firstname,";
152 $sql .=
" sum(d.total_ht) as selling_price,";
155 $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,";
156 $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";
158 $sql .=
" FROM ".MAIN_DB_PREFIX.
"societe as s";
159 $sql .=
", ".MAIN_DB_PREFIX.
"facture as f";
160 $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 = ".(empty($conf->global->AGENT_CONTACT_TYPE) ?-1 : $conf->global->AGENT_CONTACT_TYPE);
161 $sql .=
", ".MAIN_DB_PREFIX.
"facturedet as d";
162 $sql .=
", ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
163 $sql .=
", ".MAIN_DB_PREFIX.
"user as u";
164 $sql .=
" WHERE f.fk_soc = s.rowid";
165 $sql .=
' AND f.entity IN ('.getEntity(
'invoice').
')';
166 $sql .=
" AND sc.fk_soc = f.fk_soc";
167 $sql .=
" AND (d.product_type = 0 OR d.product_type = 1)";
168 if (!empty($conf->global->AGENT_CONTACT_TYPE)) {
169 $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))";
171 $sql .=
" AND sc.fk_user = u.rowid";
173 $sql .=
" AND f.fk_statut NOT IN (".$db->sanitize(implode(
', ', $invoice_status_except_list)).
")";
174 $sql .=
' AND s.entity IN ('.getEntity(
'societe').
')';
175 $sql .=
" AND d.fk_facture = f.rowid";
177 if (!empty($conf->global->AGENT_CONTACT_TYPE)) {
178 $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).
"))";
180 $sql .=
" AND sc.fk_user = ".((int) $agentid);
183 if (!empty($startdate)) {
184 $sql .=
" AND f.datef >= '".$db->idate($startdate).
"'";
186 if (!empty($enddate)) {
187 $sql .=
" AND f.datef <= '".$db->idate($enddate).
"'";
189 $sql .=
" AND d.buy_price_ht IS NOT NULL";
192 if (isset($conf->global->ForceBuyingPriceIfNull) && $conf->global->ForceBuyingPriceIfNull == 2) {
193 $sql .=
" AND d.buy_price_ht <> 0";
197 $sql .=
" GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
198 $sql .= $db->order($sortfield, $sortorder);
204 print
'<span class="opacitymedium">'.$langs->trans(
"MarginPerSaleRepresentativeWarning").
'</span><br>';
207 if (!empty($agentid)) {
208 $param .=
"&agentid=".urlencode($agentid);
210 if (!empty($startdateday)) {
211 $param .=
"&startdateday=".urlencode($startdateday);
213 if (!empty($startdatemonth)) {
214 $param .=
"&startdatemonth=".urlencode($startdatemonth);
216 if (!empty($startdateyear)) {
217 $param .=
"&startdateyear=".urlencode($startdateyear);
219 if (!empty($enddateday)) {
220 $param .=
"&enddateday=".urlencode($enddateday);
222 if (!empty($enddatemonth)) {
223 $param .=
"&enddatemonth=".urlencode($enddatemonth);
225 if (!empty($enddateyear)) {
226 $param .=
"&enddateyear=".urlencode($enddateyear);
230 dol_syslog(
'margin::agentMargins.php', LOG_DEBUG);
231 $result = $db->query(
$sql);
233 $num = $db->num_rows($result);
236 print_barre_liste($langs->trans(
"MarginDetails"), $page, $_SERVER[
"PHP_SELF"],
"", $sortfield, $sortorder,
'', $num, $num,
'', 0,
'',
'', 0, 1);
238 if ($conf->global->MARGIN_TYPE ==
"1") {
239 $labelcostprice =
'BuyingPrice';
241 $labelcostprice =
'CostPrice';
247 print
'<div class="div-table-responsive">';
248 print
'<table class="tagtable liste'.($moreforfilter ?
" listwithfilterbefore" :
"").
'">'.
"\n";
250 print
'<tr class="liste_titre">';
254 print_liste_field_titre(
"SalesRepresentative", $_SERVER[
"PHP_SELF"],
"u.lastname",
"", $param,
'', $sortfield, $sortorder);
257 print_liste_field_titre(
"SellingPrice", $_SERVER[
"PHP_SELF"],
"selling_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
258 print_liste_field_titre($labelcostprice, $_SERVER[
"PHP_SELF"],
"buying_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
259 print_liste_field_titre(
"Margin", $_SERVER[
"PHP_SELF"],
"marge",
"", $param,
'', $sortfield, $sortorder,
'right ');
260 if (!empty($conf->global->DISPLAY_MARGIN_RATES)) {
261 print_liste_field_titre(
"MarginRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
263 if (!empty($conf->global->DISPLAY_MARK_RATES)) {
264 print_liste_field_titre(
"MarkRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
269 $group_list = array();
270 while ($objp = $db->fetch_object($result)) {
272 $group_id = $objp->socid;
274 $group_id = $objp->agent;
277 if (!isset($group_list[$group_id])) {
279 $group_name = $objp->name;
280 $companystatic->id = $objp->socid;
281 $companystatic->name = $objp->name;
282 $companystatic->client = $objp->client;
283 $group_htmlname = $companystatic->getNomUrl(1,
'customer');
285 $group_name = $objp->lastname;
286 $userstatic->fetch($objp->agent);
287 $group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
289 $group_list[$group_id] = array(
'name' => $group_name,
'htmlname' => $group_htmlname,
'selling_price' => 0,
'buying_price' => 0,
'marge' => 0);
293 if ($objp->socid > 0) {
295 $sql_seller =
"SELECT COUNT(sc.rowid) as nb";
296 $sql_seller .=
" FROM ".MAIN_DB_PREFIX.
"societe_commerciaux as sc";
297 $sql_seller .=
" WHERE sc.fk_soc = ".((int) $objp->socid);
298 $sql_seller .=
" LIMIT 1";
300 $resql_seller = $db->query($sql_seller);
301 if (!$resql_seller) {
304 if ($obj_seller = $db->fetch_object($resql_seller)) {
305 if ($obj_seller->nb > 0) {
306 $seller_nb = $obj_seller->nb;
312 $group_list[$group_id][
'selling_price'] += $objp->selling_price / $seller_nb;
313 $group_list[$group_id][
'buying_price'] += $objp->buying_price / $seller_nb;
314 $group_list[$group_id][
'marge'] += $objp->marge / $seller_nb;
318 if ($sortfield ==
'u.lastname' || $sortfield ==
's.nom') {
321 $group_list =
dol_sort_array($group_list, $sortfield, $sortorder);
324 foreach ($group_list as $group_id => $group_array) {
325 $pa = $group_array[
'buying_price'];
326 $pv = $group_array[
'selling_price'];
327 $marge = $group_array[
'marge'];
329 $marginRate = ($pa != 0) ? (100 * $marge / $pa) :
'';
330 $markRate = ($pv != 0) ? (100 * $marge / $pv) :
'';
332 print
'<tr class="oddeven">';
333 print
"<td>".$group_array[
'htmlname'].
"</td>\n";
334 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pv,
'MT')).
'</span></td>';
335 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pa,
'MT')).
'</span></td>';
336 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($marge,
'MT')).
'</span></td>';
337 if (!empty($conf->global->DISPLAY_MARGIN_RATES)) {
338 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
340 if (!empty($conf->global->DISPLAY_MARK_RATES)) {
341 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
352 if (!isset($cumul_achat)) {
355 if (!isset($cumul_vente)) {
358 $totalMargin = $cumul_vente - $cumul_achat;
360 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) :
'';
361 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) :
'';
363 print
'<tr class="liste_total">';
365 print $langs->trans(
'TotalMargin').
"</td>";
366 print
'<td class="nowrap right">'.price(
price2num($cumul_vente,
'MT')).
'</td>';
367 print
'<td class="nowrap right">'.price(
price2num($cumul_achat,
'MT')).
'</td>';
368 print
'<td class="nowrap right">'.price(
price2num($totalMargin,
'MT')).
'</td>';
369 if (!empty($conf->global->DISPLAY_MARGIN_RATES)) {
370 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
372 if (!empty($conf->global->DISPLAY_MARK_RATES)) {
373 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
384 print
"\n".
'<script type="text/javascript">
385 $(document).ready(function() {
386 console.log("Init some values");
387 $("#totalMargin").html("'.
price(
price2num($totalMargin,
'MT')).
'");
388 $("#marginRate").html("'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'");
389 $("#markRate").html("'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'");
if(!defined('NOREQUIRESOC')) if(!defined('NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined('NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined('NOREQUIREAJAX')) llxHeader()
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.
if(isModEnabled('facture') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $sql
Social contributions to pay.
if($cancel &&! $id) if($action=='add' &&! $cancel) if($action=='delete') if($id) $form
Actions.
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm='auto', $check=1)
Return a timestamp date built from detailed informations (by default a local PHP server timestamp) Re...
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_print_error($db='', $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
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.
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=false, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2)
Show picto whatever it's its name (generic function)
dol_sort_array(&$array, $index, $order='asc', $natsort=0, $case_sensitive=0, $keepindex=0)
Advanced sort array by second index function, which produces ascending (default) or descending output...
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.
print_barre_liste($titre, $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.
GETPOSTISSET($paramname)
Return true if we are in a context of submitting the parameter $paramname from a POST of a form.
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.