dolibarr 20.0.0
agentMargins.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2012-2013 Christophe Battarel <christophe.battarel@altairis.fr>
3 * Copyright (C) 2014 Ferran Marcet <fmarcet@2byte.es>
4 * Copyright (C) 2015 Marcos GarcĂ­a <marcosgdf@gmail.com>
5 *
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 3 of the License, or
9 * (at your option) any later version.
10 *
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with this program. If not, see <https://www.gnu.org/licenses/>.
18 */
19
26// Load Dolibarr environment
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';
32
33// Load translation files required by the page
34$langs->loadLangs(array('companies', 'bills', 'products', 'margins'));
35
36$mesg = '';
37
38// Load variable for pagination
39$limit = GETPOSTINT('limit') ? GETPOSTINT('limit') : $conf->liste_limit;
40$sortfield = GETPOST('sortfield', 'aZ09comma');
41$sortorder = GETPOST('sortorder', 'aZ09comma');
42$page = GETPOSTISSET('pageplusone') ? (GETPOSTINT('pageplusone') - 1) : GETPOSTINT("page");
43if (empty($page) || $page == -1) {
44 $page = 0;
45} // If $page is not defined, or '' or -1
46$offset = $limit * $page;
47$pageprev = $page - 1;
48$pagenext = $page + 1;
49if (!$sortorder) {
50 $sortorder = "ASC";
51}
52if ($user->hasRight('margins', 'read', 'all')) {
53 $agentid = GETPOSTINT('agentid');
54} else {
55 $agentid = $user->id;
56}
57if (!$sortfield) {
58 if ($agentid > 0) {
59 $sortfield = "s.nom";
60 } else {
61 $sortfield = "u.lastname";
62 }
63}
64
65$startdate = $enddate = '';
66
67$startdateday = GETPOSTINT('startdateday');
68$startdatemonth = GETPOSTINT('startdatemonth');
69$startdateyear = GETPOSTINT('startdateyear');
70$enddateday = GETPOSTINT('enddateday');
71$enddatemonth = GETPOSTINT('enddatemonth');
72$enddateyear = GETPOSTINT('enddateyear');
73
74if (!empty($startdatemonth)) {
75 $startdate = dol_mktime(0, 0, 0, $startdatemonth, $startdateday, $startdateyear);
76}
77if (!empty($enddatemonth)) {
78 $enddate = dol_mktime(23, 59, 59, $enddatemonth, $enddateday, $enddateyear);
79}
80
81// Security check
82$result = restrictedArea($user, 'margins');
83
84// Initialize technical object to manage hooks of page. Note that conf->hooks_modules contains array of hook context
85$object = new User($db);
86$hookmanager->initHooks(array('marginagentlist'));
87
88/*
89 * Actions
90 */
91
92// None
93
94
95
96/*
97 * View
98 */
99
100$userstatic = new User($db);
101$companystatic = new Societe($db);
102$invoicestatic = new Facture($db);
103
104$form = new Form($db);
105
106llxHeader('', $langs->trans("Margins").' - '.$langs->trans("Agents"));
107
108$text = $langs->trans("Margins");
109//print load_fiche_titre($text);
110
111// Show tabs
112$head = marges_prepare_head();
113
114$titre = $langs->trans("Margins");
115$picto = 'margin';
116
117print '<form method="post" name="sel" action="'.$_SERVER['PHP_SELF'].'">';
118print '<input type="hidden" name="token" value="'.newToken().'">';
119
120print dol_get_fiche_head($head, 'agentMargins', $titre, 0, $picto);
121
122print '<table class="border centpercent">';
123
124print '<tr><td class="titlefield">'.$langs->trans('ContactOfInvoice').'</td>';
125print '<td class="maxwidthonsmartphone" colspan="4">';
126print img_picto('', 'user').$form->select_dolusers($agentid, 'agentid', 1, '', $user->hasRight('margins', 'read', 'all') ? 0 : 1, '', '', 0, 0, 0, '', 0, '', 'maxwidth300');
127print '</td></tr>';
128
129// Start date
130print '<td>'.$langs->trans('DateStart').' ('.$langs->trans("DateValidation").')</td>';
131print '<td>';
132print $form->selectDate($startdate, 'startdate', 0, 0, 1, "sel", 1, 1);
133print '</td>';
134print '<td>'.$langs->trans('DateEnd').' ('.$langs->trans("DateValidation").')</td>';
135print '<td>';
136print $form->selectDate($enddate, 'enddate', 0, 0, 1, "sel", 1, 1);
137print '</td>';
138print '<td style="text-align: center;">';
139print '<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans('Refresh')).'" />';
140print '</td></tr>';
141print "</table>";
142
143print dol_get_fiche_end();
144
145print '</form>';
146
147$invoice_status_except_list = array(Facture::STATUS_DRAFT, Facture::STATUS_ABANDONED);
148
149$sql = "SELECT";
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,";
153// Note: qty and buy_price_ht is always positive (if not your database may be corrupted, you can update this)
154
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";
157
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 = ".(!getDolGlobalString('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)";
168if (getDolGlobalString('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))";
170} else {
171 $sql .= " AND sc.fk_user = u.rowid";
172}
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";
176if ($agentid > 0) {
177 if (getDolGlobalString('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)."))";
179 } else {
180 $sql .= " AND sc.fk_user = ".((int) $agentid);
181 }
182}
183if (!empty($startdate)) {
184 $sql .= " AND f.datef >= '".$db->idate($startdate)."'";
185}
186if (!empty($enddate)) {
187 $sql .= " AND f.datef <= '".$db->idate($enddate)."'";
188}
189$sql .= " AND d.buy_price_ht IS NOT NULL";
190// 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.
191// We keep it with value ForceBuyingPriceIfNull = 2 for retroactive effect but results are unpredictable.
192if (getDolGlobalInt('ForceBuyingPriceIfNull') == 2) {
193 $sql .= " AND d.buy_price_ht <> 0";
194}
195//if ($agentid > 0) $sql.= " GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
196//else $sql.= " GROUP BY u.rowid, u.login, u.lastname, u.firstname";
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);
199// TODO: calculate total to display then restore pagination
200//$sql.= $db->plimit($conf->liste_limit +1, $offset);
201
202
203print '<br>';
204print '<span class="opacitymedium">'.$langs->trans("MarginPerSaleRepresentativeWarning").'</span><br>';
205
206$param = '';
207if (!empty($agentid)) {
208 $param .= "&amp;agentid=".urlencode($agentid);
209}
210if (!empty($startdateday)) {
211 $param .= "&amp;startdateday=".urlencode((string) ($startdateday));
212}
213if (!empty($startdatemonth)) {
214 $param .= "&amp;startdatemonth=".urlencode((string) ($startdatemonth));
215}
216if (!empty($startdateyear)) {
217 $param .= "&amp;startdateyear=".urlencode((string) ($startdateyear));
218}
219if (!empty($enddateday)) {
220 $param .= "&amp;enddateday=".urlencode((string) ($enddateday));
221}
222if (!empty($enddatemonth)) {
223 $param .= "&amp;enddatemonth=".urlencode((string) ($enddatemonth));
224}
225if (!empty($enddateyear)) {
226 $param .= "&amp;enddateyear=".urlencode((string) ($enddateyear));
227}
228
229$totalMargin = 0;
230$marginRate = '';
231$markRate = '';
232dol_syslog('margin::agentMargins.php', LOG_DEBUG);
233$result = $db->query($sql);
234if ($result) {
235 $num = $db->num_rows($result);
236
237 print '<br>';
238 // @phan-suppress-next-line PhanPluginSuspiciousParamPosition, PhanPluginSuspiciousParamOrder
239 print_barre_liste($langs->trans("MarginDetails"), $page, $_SERVER["PHP_SELF"], "", $sortfield, $sortorder, '', $num, $num, '', 0, '', '', 0, 1);
240
241 if (getDolGlobalString('MARGIN_TYPE') == "1") {
242 $labelcostprice = 'BuyingPrice';
243 } else { // value is 'costprice' or 'pmp'
244 $labelcostprice = 'CostPrice';
245 }
246
247 $moreforfilter = '';
248
249 $i = 0;
250 print '<div class="div-table-responsive">';
251 print '<table class="tagtable liste'.($moreforfilter ? " listwithfilterbefore" : "").'">'."\n";
252
253 print '<tr class="liste_titre">';
254 if ($agentid > 0) {
255 print_liste_field_titre("Customer", $_SERVER["PHP_SELF"], "s.nom", "", $param, '', $sortfield, $sortorder);
256 } else {
257 print_liste_field_titre("SalesRepresentative", $_SERVER["PHP_SELF"], "u.lastname", "", $param, '', $sortfield, $sortorder);
258 }
259
260 print_liste_field_titre("SellingPrice", $_SERVER["PHP_SELF"], "selling_price", "", $param, '', $sortfield, $sortorder, 'right ');
261 print_liste_field_titre($labelcostprice, $_SERVER["PHP_SELF"], "buying_price", "", $param, '', $sortfield, $sortorder, 'right ');
262 print_liste_field_titre("Margin", $_SERVER["PHP_SELF"], "marge", "", $param, '', $sortfield, $sortorder, 'right ');
263 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
264 print_liste_field_titre("MarginRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
265 }
266 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
267 print_liste_field_titre("MarkRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
268 }
269 print "</tr>\n";
270
271 if ($num > 0) {
272 $group_list = array();
273 while ($objp = $db->fetch_object($result)) {
274 if ($agentid > 0) {
275 $group_id = $objp->socid;
276 } else {
277 $group_id = $objp->agent;
278 }
279
280 if (!isset($group_list[$group_id])) {
281 if ($agentid > 0) {
282 $group_name = $objp->name;
283 $companystatic->id = $objp->socid;
284 $companystatic->name = $objp->name;
285 $companystatic->client = $objp->client;
286 $group_htmlname = $companystatic->getNomUrl(1, 'customer');
287 } else {
288 $group_name = $objp->lastname;
289 $userstatic->fetch($objp->agent);
290 $group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
291 }
292 $group_list[$group_id] = array('name' => $group_name, 'htmlname' => $group_htmlname, 'selling_price' => 0, 'buying_price' => 0, 'marge' => 0);
293 }
294
295 $seller_nb = 1;
296 if ($objp->socid > 0) {
297 // sql nb sellers
298 $sql_seller = "SELECT COUNT(sc.rowid) as nb";
299 $sql_seller .= " FROM ".MAIN_DB_PREFIX."societe_commerciaux as sc";
300 $sql_seller .= " WHERE sc.fk_soc = ".((int) $objp->socid);
301 $sql_seller .= " LIMIT 1";
302
303 $resql_seller = $db->query($sql_seller);
304 if (!$resql_seller) {
305 dol_print_error($db);
306 } else {
307 if ($obj_seller = $db->fetch_object($resql_seller)) {
308 if ($obj_seller->nb > 0) {
309 $seller_nb = $obj_seller->nb;
310 }
311 }
312 }
313 }
314
315 $group_list[$group_id]['selling_price'] += $objp->selling_price / $seller_nb;
316 $group_list[$group_id]['buying_price'] += $objp->buying_price / $seller_nb;
317 $group_list[$group_id]['marge'] += $objp->marge / $seller_nb;
318 }
319
320 // sort group array by sortfield
321 if ($sortfield == 'u.lastname' || $sortfield == 's.nom') {
322 $sortfield = 'name';
323 }
324 $group_list = dol_sort_array($group_list, $sortfield, $sortorder);
325 $cumul_achat = 0;
326 $cumul_vente = 0;
327 foreach ($group_list as $group_id => $group_array) {
328 $pa = $group_array['buying_price'];
329 $pv = $group_array['selling_price'];
330 $marge = $group_array['marge'];
331
332 $marginRate = ($pa != 0) ? (100 * $marge / $pa) : '';
333 $markRate = ($pv != 0) ? (100 * $marge / $pv) : '';
334
335 print '<tr class="oddeven">';
336 print "<td>".$group_array['htmlname']."</td>\n";
337 print '<td class="nowrap right"><span class="amount">'.price(price2num($pv, 'MT')).'</span></td>';
338 print '<td class="nowrap right"><span class="amount">'.price(price2num($pa, 'MT')).'</span></td>';
339 print '<td class="nowrap right"><span class="amount">'.price(price2num($marge, 'MT')).'</span></td>';
340 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
341 print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
342 }
343 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
344 print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
345 }
346 print "</tr>\n";
347
348 $i++;
349 $cumul_achat += $pa;
350 $cumul_vente += $pv;
351 }
352 }
353
354 // Show total margin
355 if (!isset($cumul_achat)) {
356 $cumul_achat = 0;
357 }
358 if (!isset($cumul_vente)) {
359 $cumul_vente = 0;
360 }
361 $totalMargin = $cumul_vente - $cumul_achat;
362
363 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) : '';
364 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) : '';
365
366 print '<tr class="liste_total">';
367 print '<td>';
368 print $langs->trans('TotalMargin')."</td>";
369 print '<td class="nowrap right">'.price(price2num($cumul_vente, 'MT')).'</td>';
370 print '<td class="nowrap right">'.price(price2num($cumul_achat, 'MT')).'</td>';
371 print '<td class="nowrap right">'.price(price2num($totalMargin, 'MT')).'</td>';
372 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
373 print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
374 }
375 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
376 print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
377 }
378 print '</tr>';
379
380 print '</table>';
381 print '</div>';
382} else {
383 dol_print_error($db);
384}
385$db->free($result);
386
387print "\n".'<script type="text/javascript">
388$(document).ready(function() {
389 console.log("Init some values");
390 $("#totalMargin").html("'.price(price2num($totalMargin, 'MT')).'");
391 $("#marginRate").html("'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'");
392 $("#markRate").html("'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'");
393});
394</script>'."\n";
395
396// End of page
397llxFooter();
398$db->close();
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:58
if(!defined('NOREQUIRESOC')) if(!defined( 'NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined( 'NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined( 'NOREQUIREAJAX')) llxHeader()
Empty header.
Definition wrapper.php:55
llxFooter()
Empty footer.
Definition wrapper.php:69
Class to manage invoices.
const STATUS_DRAFT
Draft status.
const STATUS_ABANDONED
Classified abandoned and no payment done.
Class to manage generation of HTML components Only common components must be here.
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 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.