dolibarr 21.0.0-beta
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 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
6 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
7 *
8 * This program is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 3 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <https://www.gnu.org/licenses/>.
20 */
21
28// Load Dolibarr environment
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';
34
43// Load translation files required by the page
44$langs->loadLangs(array('companies', 'bills', 'products', 'margins'));
45
46$mesg = '';
47
48// Load variable for pagination
49$limit = GETPOSTINT('limit') ? GETPOSTINT('limit') : $conf->liste_limit;
50$sortfield = GETPOST('sortfield', 'aZ09comma');
51$sortorder = GETPOST('sortorder', 'aZ09comma');
52$page = GETPOSTISSET('pageplusone') ? (GETPOSTINT('pageplusone') - 1) : GETPOSTINT("page");
53if (empty($page) || $page == -1) {
54 $page = 0;
55} // If $page is not defined, or '' or -1
56$offset = $limit * $page;
57$pageprev = $page - 1;
58$pagenext = $page + 1;
59if (!$sortorder) {
60 $sortorder = "ASC";
61}
62if ($user->hasRight('margins', 'read', 'all')) {
63 $agentid = GETPOSTINT('agentid');
64} else {
65 $agentid = $user->id;
66}
67if (!$sortfield) {
68 if ($agentid > 0) {
69 $sortfield = "s.nom";
70 } else {
71 $sortfield = "u.lastname";
72 }
73}
74
75$startdate = $enddate = '';
76
77$startdateday = GETPOSTINT('startdateday');
78$startdatemonth = GETPOSTINT('startdatemonth');
79$startdateyear = GETPOSTINT('startdateyear');
80$enddateday = GETPOSTINT('enddateday');
81$enddatemonth = GETPOSTINT('enddatemonth');
82$enddateyear = GETPOSTINT('enddateyear');
83
84if (!empty($startdatemonth)) {
85 $startdate = dol_mktime(0, 0, 0, $startdatemonth, $startdateday, $startdateyear);
86}
87if (!empty($enddatemonth)) {
88 $enddate = dol_mktime(23, 59, 59, $enddatemonth, $enddateday, $enddateyear);
89}
90
91$hookmanager->initHooks(array('marginagentlist'));
92
93// Security check
94$result = restrictedArea($user, 'margins');
95
96// Initialize a technical object to manage hooks of page. Note that conf->hooks_modules contains an array of hook context
97$object = new User($db);
98
99/*
100 * Actions
101 */
102
103// None
104
105
106
107/*
108 * View
109 */
110
111$userstatic = new User($db);
112$companystatic = new Societe($db);
113$invoicestatic = new Facture($db);
114
115$form = new Form($db);
116
117llxHeader('', $langs->trans("Margins").' - '.$langs->trans("Agents"), '', '', 0, 0, '', '', '', 'mod-margin page-agentmargins');
118
119$text = $langs->trans("Margins");
120//print load_fiche_titre($text);
121
122// Show tabs
123$head = marges_prepare_head();
124
125$titre = $langs->trans("Margins");
126$picto = 'margin';
127
128print '<form method="post" name="sel" action="'.$_SERVER['PHP_SELF'].'">';
129print '<input type="hidden" name="token" value="'.newToken().'">';
130
131print dol_get_fiche_head($head, 'agentMargins', $titre, 0, $picto);
132
133print '<table class="border centpercent">';
134
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');
138print '</td></tr>';
139
140// Start date
141print '<td>'.$langs->trans('DateStart').' ('.$langs->trans("DateValidation").')</td>';
142print '<td>';
143print $form->selectDate($startdate, 'startdate', 0, 0, 1, "sel", 1, 1);
144print '</td>';
145print '<td>'.$langs->trans('DateEnd').' ('.$langs->trans("DateValidation").')</td>';
146print '<td>';
147print $form->selectDate($enddate, 'enddate', 0, 0, 1, "sel", 1, 1);
148print '</td>';
149print '<td style="text-align: center;">';
150print '<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans('Refresh')).'" />';
151print '</td></tr>';
152print "</table>";
153
154print dol_get_fiche_end();
155
156print '</form>';
157
158$invoice_status_except_list = array(Facture::STATUS_DRAFT, Facture::STATUS_ABANDONED);
159
160$sql = "SELECT";
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,";
164// Note: qty and buy_price_ht is always positive (if not your database may be corrupted, you can update this)
165
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";
168
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)";
179if (getDolGlobalString('AGENT_CONTACT_TYPE')) {
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))";
181} else {
182 $sql .= " AND sc.fk_user = u.rowid";
183}
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";
187if ($agentid > 0) {
188 if (getDolGlobalString('AGENT_CONTACT_TYPE')) {
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)."))";
190 } else {
191 $sql .= " AND sc.fk_user = ".((int) $agentid);
192 }
193}
194if (!empty($startdate)) {
195 $sql .= " AND f.datef >= '".$db->idate($startdate)."'";
196}
197if (!empty($enddate)) {
198 $sql .= " AND f.datef <= '".$db->idate($enddate)."'";
199}
200$sql .= " AND d.buy_price_ht IS NOT NULL";
201// 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.
202// We keep it with value ForceBuyingPriceIfNull = 2 for retroactive effect but results are unpredictable.
203if (getDolGlobalInt('ForceBuyingPriceIfNull') == 2) {
204 $sql .= " AND d.buy_price_ht <> 0";
205}
206//if ($agentid > 0) $sql.= " GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
207//else $sql.= " GROUP BY u.rowid, u.login, u.lastname, u.firstname";
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);
210// TODO: calculate total to display then restore pagination
211//$sql.= $db->plimit($conf->liste_limit +1, $offset);
212
213
214print '<br>';
215print '<span class="opacitymedium">'.$langs->trans("MarginPerSaleRepresentativeWarning").'</span><br>';
216
217$param = '';
218if (!empty($agentid)) {
219 $param .= "&amp;agentid=".urlencode((string) $agentid);
220}
221if (!empty($startdateday)) {
222 $param .= "&amp;startdateday=".urlencode((string) ($startdateday));
223}
224if (!empty($startdatemonth)) {
225 $param .= "&amp;startdatemonth=".urlencode((string) ($startdatemonth));
226}
227if (!empty($startdateyear)) {
228 $param .= "&amp;startdateyear=".urlencode((string) ($startdateyear));
229}
230if (!empty($enddateday)) {
231 $param .= "&amp;enddateday=".urlencode((string) ($enddateday));
232}
233if (!empty($enddatemonth)) {
234 $param .= "&amp;enddatemonth=".urlencode((string) ($enddatemonth));
235}
236if (!empty($enddateyear)) {
237 $param .= "&amp;enddateyear=".urlencode((string) ($enddateyear));
238}
239
240$totalMargin = 0;
241$marginRate = '';
242$markRate = '';
243dol_syslog('margin::agentMargins.php', LOG_DEBUG);
244$result = $db->query($sql);
245if ($result) {
246 $num = $db->num_rows($result);
247
248 print '<br>';
249 // @phan-suppress-next-line PhanPluginSuspiciousParamPosition, PhanPluginSuspiciousParamOrder
250 print_barre_liste($langs->trans("MarginDetails"), $page, $_SERVER["PHP_SELF"], "", $sortfield, $sortorder, '', $num, $num, '', 0, '', '', 0, 1);
251
252 if (getDolGlobalString('MARGIN_TYPE') == "1") {
253 $labelcostprice = 'BuyingPrice';
254 } else { // value is 'costprice' or 'pmp'
255 $labelcostprice = 'CostPrice';
256 }
257
258 $moreforfilter = '';
259
260 $i = 0;
261 print '<div class="div-table-responsive">';
262 print '<table class="tagtable liste'.($moreforfilter ? " listwithfilterbefore" : "").'">'."\n";
263
264 print '<tr class="liste_titre">';
265 if ($agentid > 0) {
266 print_liste_field_titre("Customer", $_SERVER["PHP_SELF"], "s.nom", "", $param, '', $sortfield, $sortorder);
267 } else {
268 print_liste_field_titre("SalesRepresentative", $_SERVER["PHP_SELF"], "u.lastname", "", $param, '', $sortfield, $sortorder);
269 }
270
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 ');
274 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
275 print_liste_field_titre("MarginRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
276 }
277 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
278 print_liste_field_titre("MarkRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
279 }
280 print "</tr>\n";
281
282 if ($num > 0) {
283 $group_list = array();
284 while ($objp = $db->fetch_object($result)) {
285 if ($agentid > 0) {
286 $group_id = $objp->socid;
287 } else {
288 $group_id = $objp->agent;
289 }
290
291 if (!isset($group_list[$group_id])) {
292 if ($agentid > 0) {
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');
298 } else {
299 $group_name = $objp->lastname;
300 $userstatic->fetch($objp->agent);
301 $group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
302 }
303 $group_list[$group_id] = array('name' => $group_name, 'htmlname' => $group_htmlname, 'selling_price' => 0, 'buying_price' => 0, 'marge' => 0);
304 }
305
306 $seller_nb = 1;
307 if ($objp->socid > 0) {
308 // sql nb sellers
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";
313
314 $resql_seller = $db->query($sql_seller);
315 if (!$resql_seller) {
316 dol_print_error($db);
317 } else {
318 if ($obj_seller = $db->fetch_object($resql_seller)) {
319 if ($obj_seller->nb > 0) {
320 $seller_nb = $obj_seller->nb;
321 }
322 }
323 }
324 }
325
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;
329 }
330
331 // sort group array by sortfield
332 if ($sortfield == 'u.lastname' || $sortfield == 's.nom') {
333 $sortfield = 'name';
334 }
335 $group_list = dol_sort_array($group_list, $sortfield, $sortorder);
336 $cumul_achat = 0;
337 $cumul_vente = 0;
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'];
342
343 $marginRate = ($pa != 0) ? (100 * $marge / $pa) : '';
344 $markRate = ($pv != 0) ? (100 * $marge / $pv) : '';
345
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>';
351 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
352 print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
353 }
354 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
355 print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
356 }
357 print "</tr>\n";
358
359 $i++;
360 $cumul_achat += $pa;
361 $cumul_vente += $pv;
362 }
363 }
364
365 // Show total margin
366 if (!isset($cumul_achat)) {
367 $cumul_achat = 0;
368 }
369 if (!isset($cumul_vente)) {
370 $cumul_vente = 0;
371 }
372 $totalMargin = $cumul_vente - $cumul_achat;
373
374 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) : '';
375 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) : '';
376
377 print '<tr class="liste_total">';
378 print '<td>';
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>';
383 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
384 print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
385 }
386 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
387 print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
388 }
389 print '</tr>';
390
391 print '</table>';
392 print '</div>';
393} else {
394 dol_print_error($db);
395}
396$db->free($result);
397
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'))."%").'");
404});
405</script>'."\n";
406
407// End of page
408llxFooter();
409$db->close();
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:66
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.
Definition wrapper.php:71
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.
llxFooter()
Footer empty.
Definition document.php:107
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...
Definition member.php:79
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.