26 require
'../main.inc.php';
27 require_once DOL_DOCUMENT_ROOT.
'/core/lib/company.lib.php';
28 require_once DOL_DOCUMENT_ROOT.
'/compta/facture/class/facture.class.php';
29 require_once DOL_DOCUMENT_ROOT.
'/product/class/product.class.php';
30 require_once DOL_DOCUMENT_ROOT.
'/margin/lib/margins.lib.php';
33 $langs->loadLangs(array(
'companies',
'bills',
'products',
'margins'));
37 $action =
GETPOST(
'action',
'aZ09');
38 $confirm =
GETPOST(
'confirm',
'alpha');
39 $TSelectedCats =
GETPOST(
'categories',
'array');
42 $fieldvalue = (!empty($id) ? $id : (!empty($ref) ? $ref :
''));
43 $fieldtype = (!empty($ref) ?
'ref' :
'rowid');
44 if (!empty($user->socid)) {
45 $socid = $user->socid;
47 $result =
restrictedArea($user,
'produit|service', $fieldvalue,
'product&product',
'',
'', $fieldtype);
48 if (empty($user->rights->margins->liretous)) {
55 $limit =
GETPOST(
'limit',
'int') ?
GETPOST(
'limit',
'int') : $conf->liste_limit;
56 $sortfield =
GETPOST(
'sortfield',
'aZ09comma');
57 $sortorder =
GETPOST(
'sortorder',
'aZ09comma');
59 if (empty($page) || $page == -1) {
62 $offset = $limit * $page;
63 $pageprev = $page - 1;
64 $pagenext = $page + 1;
67 $sortfield =
"f.datef";
75 $startdate = $enddate =
'';
76 if (
GETPOST(
'startdatemonth')) {
85 $hookmanager->initHooks(array(
'marginproductlist'));
92 $product_static =
new Product($db);
93 $invoicestatic =
new Facture($db);
97 llxHeader(
'', $langs->trans(
"Margins").
' - '.$langs->trans(
"Products"));
99 $text = $langs->trans(
"Margins");
104 $titre = $langs->trans(
"Margins");
107 print
'<form method="post" name="sel" action="'.$_SERVER[
'PHP_SELF'].
'">';
108 print
'<input type="hidden" name="token" value="'.newToken().
'">';
112 print
'<table class="border centpercent">';
115 print
'<tr><td class="titlefield">'.$langs->trans(
'ProductOrService').
'</td>';
116 print
'<td class="maxwidthonsmartphone" colspan="4">';
117 print
img_picto(
'',
'product').$form->select_produits(($id > 0 ? $id :
''),
'id',
'', 20, 0, 1, 2,
'', 1, array(), 0,
'All', 0,
'', 0,
'',
null, 1);
121 $TCats =
$form->select_all_categories(0, array(),
'', 64, 0, 1);
124 print
'<td class="titlefield">'.$langs->trans(
'Category').
'</td>';
125 print
'<td class="maxwidthonsmartphone" colspan="4">';
126 print
img_picto(
'',
'category').$form->multiselectarray(
'categories', $TCats, $TSelectedCats, 0, 0,
'quatrevingtpercent widthcentpercentminusx');
132 print
'<td class="titlefield">'.$langs->trans(
'DateStart').
' ('.$langs->trans(
"DateValidation").
')</td>';
134 print
$form->selectDate($startdate,
'startdate',
'',
'', 1,
"sel", 1, 1);
136 print
'<td>'.$langs->trans(
'DateEnd').
' ('.$langs->trans(
"DateValidation").
')</td>';
138 print
$form->selectDate($enddate,
'enddate',
'',
'', 1,
"sel", 1, 1);
140 print
'<td style="text-align: center;">';
141 print
'<input type="submit" class="button" value="'.dol_escape_htmltag($langs->trans(
'Refresh')).
'" />';
148 print
'<table class="border centpercent">';
151 print
'<tr><td class="titlefield">'.$langs->trans(
"TotalMargin").
'</td><td colspan="4">';
152 print
'<span id="totalMargin" class="amount"></span> <span class="amount">'.$langs->getCurrencySymbol($conf->currency).
'</span>';
156 if (!empty($conf->global->DISPLAY_MARGIN_RATES)) {
157 print
'<tr><td>'.$langs->trans(
"MarginRate").
'</td><td colspan="4">';
158 print
'<span id="marginRate"></span>';
163 if (!empty($conf->global->DISPLAY_MARK_RATES)) {
164 print
'<tr><td>'.$langs->trans(
"MarkRate").
'</td><td colspan="4">';
165 print
'<span id="markRate"></span>';
177 $sql =
"SELECT p.label, p.rowid, p.fk_product_type, p.ref, p.entity as pentity,";
179 $sql .=
" d.fk_product,";
182 $sql .=
" f.rowid as facid, f.ref, f.total_ht, f.datef, f.paye, f.fk_statut as statut,";
184 $sql .=
" SUM(d.total_ht) as selling_price,";
186 $sql .=
" SUM(d.qty) as product_qty,";
187 $sql .=
" SUM(".$db->ifsql(
'd.total_ht < 0',
'd.qty * d.buy_price_ht * -1 * (d.situation_percent / 100)',
'd.qty * d.buy_price_ht * (d.situation_percent / 100)').
") as buying_price,";
188 $sql .=
" SUM(".$db->ifsql(
'd.total_ht < 0',
'-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";
189 $sql .=
" FROM ".MAIN_DB_PREFIX.
"societe as s";
190 $sql .=
", ".MAIN_DB_PREFIX.
"facture as f";
191 $sql .=
", ".MAIN_DB_PREFIX.
"facturedet as d";
192 $sql .=
" LEFT JOIN ".MAIN_DB_PREFIX.
"product as p ON p.rowid = d.fk_product";
193 if (!empty($TSelectedCats)) {
194 $sql .=
' LEFT JOIN '.MAIN_DB_PREFIX.
'categorie_product as cp ON cp.fk_product=p.rowid';
196 $sql .=
" WHERE f.fk_soc = s.rowid";
197 $sql .=
' AND f.entity IN ('.getEntity(
'invoice').
')';
198 $sql .=
" AND f.fk_statut NOT IN (".$db->sanitize(implode(
', ', $invoice_status_except_list)).
")";
199 $sql .=
" AND d.fk_facture = f.rowid";
201 $sql .=
" AND d.fk_product =".((int) $id);
203 if (!empty($TSelectedCats)) {
204 $sql .=
' AND cp.fk_categorie IN ('.$db->sanitize(implode(
',', $TSelectedCats)).
')';
206 if (!empty($startdate)) {
207 $sql .=
" AND f.datef >= '".$db->idate($startdate).
"'";
209 if (!empty($enddate)) {
210 $sql .=
" AND f.datef <= '".$db->idate($enddate).
"'";
212 $sql .=
" AND d.buy_price_ht IS NOT NULL";
215 if (isset($conf->global->ForceBuyingPriceIfNull) && $conf->global->ForceBuyingPriceIfNull == 2) {
216 $sql .=
" AND d.buy_price_ht <> 0";
219 $sql .=
" GROUP BY p.label, p.rowid, p.fk_product_type, p.ref, p.entity, d.fk_product, f.rowid, f.ref, f.total_ht, f.datef, f.paye, f.fk_statut";
221 $sql .=
" GROUP BY p.label, p.rowid, p.fk_product_type, p.ref, p.entity";
223 $sql .= $db->order($sortfield, $sortorder);
227 $param =
'&id='.((int) $id);
228 if (
GETPOST(
'startdatemonth',
'int')) {
229 $param .=
'&startdateyear='.GETPOST(
'startdateyear',
'int');
230 $param .=
'&startdatemonth='.GETPOST(
'startdatemonth',
'int');
231 $param .=
'&startdateday='.GETPOST(
'startdateday',
'int');
233 if (
GETPOST(
'enddatemonth',
'int')) {
234 $param .=
'&enddateyear='.GETPOST(
'enddateyear',
'int');
235 $param .=
'&enddatemonth='.GETPOST(
'enddatemonth',
'int');
236 $param .=
'&enddateday='.GETPOST(
'enddateday',
'int');
238 $listofcateg =
GETPOST(
'categories',
'array:int');
239 if (is_array($listofcateg)) {
240 foreach ($listofcateg as $val) {
241 $param .=
'&categories[]='.$val;
245 dol_syslog(
'margin::productMargins.php', LOG_DEBUG);
246 $result = $db->query($sql);
248 $num = $db->num_rows($result);
251 print_barre_liste($langs->trans(
"MarginDetails"), $page, $_SERVER[
"PHP_SELF"], $param, $sortfield, $sortorder,
'', $num, $num,
'', 0,
'',
'', 0, 1);
254 if ($conf->global->MARGIN_TYPE ==
"1") {
255 $labelcostprice =
'BuyingPrice';
257 $labelcostprice =
'CostPrice';
263 print
'<div class="div-table-responsive">';
264 print
'<table class="tagtable liste'.($moreforfilter ?
" listwithfilterbefore" :
"").
'">'.
"\n";
266 print
'<tr class="liste_titre">';
269 print_liste_field_titre(
"DateInvoice", $_SERVER[
"PHP_SELF"],
"f.datef",
"", $param,
'', $sortfield, $sortorder,
'center ');
271 print_liste_field_titre(
"ProductService", $_SERVER[
"PHP_SELF"],
"p.ref",
"", $param,
'', $sortfield, $sortorder);
273 print_liste_field_titre(
"Qty", $_SERVER[
"PHP_SELF"],
"product_qty",
"", $param,
'', $sortfield, $sortorder,
'center ');
274 print_liste_field_titre(
"SellingPrice", $_SERVER[
"PHP_SELF"],
"selling_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
275 print_liste_field_titre($labelcostprice, $_SERVER[
"PHP_SELF"],
"buying_price",
"", $param,
'', $sortfield, $sortorder,
'right ');
276 print_liste_field_titre(
"Margin", $_SERVER[
"PHP_SELF"],
"marge",
"", $param,
'', $sortfield, $sortorder,
'right ');
277 if (!empty($conf->global->DISPLAY_MARGIN_RATES)) {
278 print_liste_field_titre(
"MarginRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
280 if (!empty($conf->global->DISPLAY_MARK_RATES)) {
281 print_liste_field_titre(
"MarkRate", $_SERVER[
"PHP_SELF"],
"",
"", $param,
'', $sortfield, $sortorder,
'right ');
291 $objp = $db->fetch_object($result);
292 $qty = $objp->product_qty;
293 $pa = $objp->buying_price;
294 $pv = $objp->selling_price;
295 $marge = $objp->marge;
298 $marginRate = ($pa != 0) ?-1 * (100 * $marge / $pa) :
'';
299 $markRate = ($pv != 0) ?-1 * (100 * $marge / $pv) :
'';
301 $marginRate = ($pa != 0) ? (100 * $marge / $pa) :
'';
302 $markRate = ($pv != 0) ? (100 * $marge / $pv) :
'';
305 print
'<tr class="oddeven">';
308 $invoicestatic->id = $objp->facid;
309 $invoicestatic->ref = $objp->ref;
310 print $invoicestatic->getNomUrl(1);
312 print
"<td class=\"center\">";
316 if ($objp->rowid > 0) {
317 $product_static->type = $objp->fk_product_type;
318 $product_static->id = $objp->rowid;
319 $product_static->ref = $objp->ref;
320 $product_static->label = $objp->label;
321 $product_static->entity = $objp->pentity;
322 $text = $product_static->getNomUrl(1);
323 print $text .=
' - '.$objp->label;
325 print
img_object(
'',
'product').
' '.$langs->trans(
"NotPredefinedProducts");
330 print
'<td class="center">'.$qty.
'</td>';
331 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pv,
'MT')).
'</span></td>';
332 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($pa,
'MT')).
'</span></td>';
333 print
'<td class="nowrap right"><span class="amount">'.price(
price2num($marge,
'MT')).
'</span></td>';
334 if (!empty($conf->global->DISPLAY_MARGIN_RATES)) {
335 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
337 if (!empty($conf->global->DISPLAY_MARK_RATES)) {
338 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
343 $cumul_achat += $objp->buying_price;
344 $cumul_vente += $objp->selling_price;
345 $cumul_qty += $objp->product_qty;
351 $totalMargin = $cumul_vente - $cumul_achat;
353 $marginRate = ($cumul_achat != 0) ? (100 * $totalMargin / $cumul_achat) :
'';
354 $markRate = ($cumul_vente != 0) ? (100 * $totalMargin / $cumul_vente) :
'';
356 print
'<tr class="liste_total">';
358 print
'<td colspan=2>';
362 print $langs->trans(
'TotalMargin').
'</td>';
363 print
'<td class="center">'.$cumul_qty.
'</td>';
364 print
'<td class="nowrap right">'.price(
price2num($cumul_vente,
'MT')).
'</td>';
365 print
'<td class="nowrap right">'.price(
price2num($cumul_achat,
'MT')).
'</td>';
366 print
'<td class="nowrap right">'.price(
price2num($totalMargin,
'MT')).
'</td>';
367 if (!empty($conf->global->DISPLAY_MARGIN_RATES)) {
368 print
'<td class="nowrap right">'.(($marginRate ===
'') ?
'n/a' :
price(
price2num($marginRate,
'MT')).
"%").
'</td>';
370 if (!empty($conf->global->DISPLAY_MARK_RATES)) {
371 print
'<td class="nowrap right">'.(($markRate ===
'') ?
'n/a' :
price(
price2num($markRate,
'MT')).
"%").
'</td>';
384 <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')).
"%").
'");