dolibarr 21.0.0-alpha
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$hookmanager->initHooks(array('marginagentlist'));
82
83// Security check
84$result = restrictedArea($user, 'margins');
85
86// Initialize a technical object to manage hooks of page. Note that conf->hooks_modules contains an array of hook context
87$object = new User($db);
88
89/*
90 * Actions
91 */
92
93// None
94
95
96
97/*
98 * View
99 */
100
101$userstatic = new User($db);
102$companystatic = new Societe($db);
103$invoicestatic = new Facture($db);
104
105$form = new Form($db);
106
107llxHeader('', $langs->trans("Margins").' - '.$langs->trans("Agents"), '', '', 0, 0, '', '', '', 'mod-margin page-agentmargins');
108
109$text = $langs->trans("Margins");
110//print load_fiche_titre($text);
111
112// Show tabs
113$head = marges_prepare_head();
114
115$titre = $langs->trans("Margins");
116$picto = 'margin';
117
118print '<form method="post" name="sel" action="'.$_SERVER['PHP_SELF'].'">';
119print '<input type="hidden" name="token" value="'.newToken().'">';
120
121print dol_get_fiche_head($head, 'agentMargins', $titre, 0, $picto);
122
123print '<table class="border centpercent">';
124
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');
128print '</td></tr>';
129
130// Start date
131print '<td>'.$langs->trans('DateStart').' ('.$langs->trans("DateValidation").')</td>';
132print '<td>';
133print $form->selectDate($startdate, 'startdate', 0, 0, 1, "sel", 1, 1);
134print '</td>';
135print '<td>'.$langs->trans('DateEnd').' ('.$langs->trans("DateValidation").')</td>';
136print '<td>';
137print $form->selectDate($enddate, 'enddate', 0, 0, 1, "sel", 1, 1);
138print '</td>';
139print '<td style="text-align: center;">';
140print '<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans('Refresh')).'" />';
141print '</td></tr>';
142print "</table>";
143
144print dol_get_fiche_end();
145
146print '</form>';
147
148$invoice_status_except_list = array(Facture::STATUS_DRAFT, Facture::STATUS_ABANDONED);
149
150$sql = "SELECT";
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,";
154// Note: qty and buy_price_ht is always positive (if not your database may be corrupted, you can update this)
155
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";
158
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)";
169if (getDolGlobalString('AGENT_CONTACT_TYPE')) {
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))";
171} else {
172 $sql .= " AND sc.fk_user = u.rowid";
173}
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";
177if ($agentid > 0) {
178 if (getDolGlobalString('AGENT_CONTACT_TYPE')) {
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)."))";
180 } else {
181 $sql .= " AND sc.fk_user = ".((int) $agentid);
182 }
183}
184if (!empty($startdate)) {
185 $sql .= " AND f.datef >= '".$db->idate($startdate)."'";
186}
187if (!empty($enddate)) {
188 $sql .= " AND f.datef <= '".$db->idate($enddate)."'";
189}
190$sql .= " AND d.buy_price_ht IS NOT NULL";
191// 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.
192// We keep it with value ForceBuyingPriceIfNull = 2 for retroactive effect but results are unpredictable.
193if (getDolGlobalInt('ForceBuyingPriceIfNull') == 2) {
194 $sql .= " AND d.buy_price_ht <> 0";
195}
196//if ($agentid > 0) $sql.= " GROUP BY s.rowid, s.nom, s.code_client, s.client, u.rowid, u.login, u.lastname, u.firstname";
197//else $sql.= " GROUP BY u.rowid, u.login, u.lastname, u.firstname";
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);
200// TODO: calculate total to display then restore pagination
201//$sql.= $db->plimit($conf->liste_limit +1, $offset);
202
203
204print '<br>';
205print '<span class="opacitymedium">'.$langs->trans("MarginPerSaleRepresentativeWarning").'</span><br>';
206
207$param = '';
208if (!empty($agentid)) {
209 $param .= "&amp;agentid=".urlencode($agentid);
210}
211if (!empty($startdateday)) {
212 $param .= "&amp;startdateday=".urlencode((string) ($startdateday));
213}
214if (!empty($startdatemonth)) {
215 $param .= "&amp;startdatemonth=".urlencode((string) ($startdatemonth));
216}
217if (!empty($startdateyear)) {
218 $param .= "&amp;startdateyear=".urlencode((string) ($startdateyear));
219}
220if (!empty($enddateday)) {
221 $param .= "&amp;enddateday=".urlencode((string) ($enddateday));
222}
223if (!empty($enddatemonth)) {
224 $param .= "&amp;enddatemonth=".urlencode((string) ($enddatemonth));
225}
226if (!empty($enddateyear)) {
227 $param .= "&amp;enddateyear=".urlencode((string) ($enddateyear));
228}
229
230$totalMargin = 0;
231$marginRate = '';
232$markRate = '';
233dol_syslog('margin::agentMargins.php', LOG_DEBUG);
234$result = $db->query($sql);
235if ($result) {
236 $num = $db->num_rows($result);
237
238 print '<br>';
239 // @phan-suppress-next-line PhanPluginSuspiciousParamPosition, PhanPluginSuspiciousParamOrder
240 print_barre_liste($langs->trans("MarginDetails"), $page, $_SERVER["PHP_SELF"], "", $sortfield, $sortorder, '', $num, $num, '', 0, '', '', 0, 1);
241
242 if (getDolGlobalString('MARGIN_TYPE') == "1") {
243 $labelcostprice = 'BuyingPrice';
244 } else { // value is 'costprice' or 'pmp'
245 $labelcostprice = 'CostPrice';
246 }
247
248 $moreforfilter = '';
249
250 $i = 0;
251 print '<div class="div-table-responsive">';
252 print '<table class="tagtable liste'.($moreforfilter ? " listwithfilterbefore" : "").'">'."\n";
253
254 print '<tr class="liste_titre">';
255 if ($agentid > 0) {
256 print_liste_field_titre("Customer", $_SERVER["PHP_SELF"], "s.nom", "", $param, '', $sortfield, $sortorder);
257 } else {
258 print_liste_field_titre("SalesRepresentative", $_SERVER["PHP_SELF"], "u.lastname", "", $param, '', $sortfield, $sortorder);
259 }
260
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 ');
264 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
265 print_liste_field_titre("MarginRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
266 }
267 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
268 print_liste_field_titre("MarkRate", $_SERVER["PHP_SELF"], "", "", $param, '', $sortfield, $sortorder, 'right ');
269 }
270 print "</tr>\n";
271
272 if ($num > 0) {
273 $group_list = array();
274 while ($objp = $db->fetch_object($result)) {
275 if ($agentid > 0) {
276 $group_id = $objp->socid;
277 } else {
278 $group_id = $objp->agent;
279 }
280
281 if (!isset($group_list[$group_id])) {
282 if ($agentid > 0) {
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');
288 } else {
289 $group_name = $objp->lastname;
290 $userstatic->fetch($objp->agent);
291 $group_htmlname = $userstatic->getFullName($langs, 0, 0, 0);
292 }
293 $group_list[$group_id] = array('name' => $group_name, 'htmlname' => $group_htmlname, 'selling_price' => 0, 'buying_price' => 0, 'marge' => 0);
294 }
295
296 $seller_nb = 1;
297 if ($objp->socid > 0) {
298 // sql nb sellers
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";
303
304 $resql_seller = $db->query($sql_seller);
305 if (!$resql_seller) {
306 dol_print_error($db);
307 } else {
308 if ($obj_seller = $db->fetch_object($resql_seller)) {
309 if ($obj_seller->nb > 0) {
310 $seller_nb = $obj_seller->nb;
311 }
312 }
313 }
314 }
315
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;
319 }
320
321 // sort group array by sortfield
322 if ($sortfield == 'u.lastname' || $sortfield == 's.nom') {
323 $sortfield = 'name';
324 }
325 $group_list = dol_sort_array($group_list, $sortfield, $sortorder);
326 $cumul_achat = 0;
327 $cumul_vente = 0;
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'];
332
333 $marginRate = ($pa != 0) ? (100 * $marge / $pa) : '';
334 $markRate = ($pv != 0) ? (100 * $marge / $pv) : '';
335
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>';
341 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
342 print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
343 }
344 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
345 print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
346 }
347 print "</tr>\n";
348
349 $i++;
350 $cumul_achat += $pa;
351 $cumul_vente += $pv;
352 }
353 }
354
355 // Show total margin
356 if (!isset($cumul_achat)) {
357 $cumul_achat = 0;
358 }
359 if (!isset($cumul_vente)) {
360 $cumul_vente = 0;
361 }
362 $totalMargin = $cumul_vente - $cumul_achat;
363
364 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) : '';
365 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) : '';
366
367 print '<tr class="liste_total">';
368 print '<td>';
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>';
373 if (getDolGlobalString('DISPLAY_MARGIN_RATES')) {
374 print '<td class="nowrap right">'.(($marginRate === '') ? 'n/a' : price(price2num($marginRate, 'MT'))."%").'</td>';
375 }
376 if (getDolGlobalString('DISPLAY_MARK_RATES')) {
377 print '<td class="nowrap right">'.(($markRate === '') ? 'n/a' : price(price2num($markRate, 'MT'))."%").'</td>';
378 }
379 print '</tr>';
380
381 print '</table>';
382 print '</div>';
383} else {
384 dol_print_error($db);
385}
386$db->free($result);
387
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'))."%").'");
394});
395</script>'."\n";
396
397// End of page
398llxFooter();
399$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($head='', $title='', $help_url='', $target='', $disablejs=0, $disablehead=0, $arrayofjs='', $arrayofcss='', $morequerystring='', $morecssonbody='', $replacemainareaby='', $disablenofollow=0, $disablenoindex=0)
Empty header.
Definition wrapper.php:70
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...
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.