[SOLVED] How to sort loyalty points list descending?

sakiri · November 3, 2013

Hi everybody,

I'm using PrestaShop Loyalty Module Version 1.8

I want the list shown in "My loyalty points" to be sorted "the other way round", that is descending either by Order ID or by Date, to show the available points on top, and the used points at the bottom of the list.

But whatever I tried... nothing works.

Can somebody please show me how and where to tweak the module?

Thank you!

Edited November 13, 2013 by sakiri (see edit history)

PascalVG · November 11, 2013

Hi Sakiri,

I have no loyalty points installed, can you add some screenshot? Then I may know where to search for it.

thanks,

pascal

sakiri · November 11, 2013

Hello Pascal,

I think the problem sits somewhere in between these bits of coding:

from the loyalty.tpl (already adapted to my needs from the original):

{capture name=path}<a href="{$link->getPageLink('my-account', true)}">{l s='My account' mod='loyalty'}</a><span class="navigation-pipe">{$navigationPipe}</span>{l s='My loyalty points' mod='loyalty'}{/capture}
{include file="$tpl_dir./breadcrumb.tpl"}

<h2>{l s='My loyalty points' mod='loyalty'}</h2>

{if $orders}
<div class="block-center" id="block-history">
	{if $orders && count($orders)}
	<table id="order-list" class="std">
		<thead>
			<tr>
				<th class="first_item">{l s='Order #' mod='loyalty'}</th>
				<th class="item">{l s='Date' mod='loyalty'}</th>
				<th class="item" style="text-align: center;">{l s='Points' mod='loyalty'}</th>
				<th class="last_item" style="text-align: center;">{l s='Points Status' mod='loyalty'}</th>
			</tr>
		</thead>
		<tfoot>
			<tr class="alternate_item">
				<td style="border-top: 1px solid #C9BAA4;"> </td>
				<td class="history_method bold" style="border-top: 1px solid #C9BAA4;">{l s='Total points available:' mod='loyalty'}</td>
				<td class="history_method bold" style="border-top: 1px solid #C9BAA4; text-align:center;">{$totalPoints|intval}</td>
				<td style="border-top: 1px solid #C9BAA4;"> </td>
			</tr>
		</tfoot>
		<tbody>
		{foreach from=$displayorders item='order'}
			{if $order.points|intval > 0}
				<tr class="alternate_item">
					<td class="history_link bold">{$order.id|string_format:"%06d"}</td>
					<td class="history_date">{dateFormat date=$order.date full=1}</td>
					<td class="history_method" style="text-align: center; font-weight: 700;">{$order.points|intval}</td>
					<td class="history_method" style="text-align: center;">{$order.state|escape:'htmlall':'UTF-8'}</td>
				</tr>
			{/if}
		{/foreach}
		</tbody>
	</table>
	<div id="block-order-detail" class="hidden"> </div>
	{else}
		<p class="warning">{l s='You have not placed any orders.'}</p>
	{/if}
</div> 
...

the $displayorders array (in line 52 above) comes from the loyalty-program.php (strange enough there is a php file called "default.php" in some sub-directory of the module, which has the very same content), lines 109-123:

/* SSL Management */
$useSSL = true;

require_once(dirname(__FILE__).'/../../config/config.inc.php');
require_once(dirname(__FILE__).'/../../init.php');

include_once(dirname(__FILE__).'/LoyaltyModule.php');
include_once(dirname(__FILE__).'/LoyaltyStateModule.php');
include_once(dirname(__FILE__).'/loyalty.php');

Tools::displayFileAsDeprecated();

$context = Context::getContext();
if (!$context->customer->isLogged())
	Tools::redirect('index.php?controller=authentication&back=modules/loyalty/loyalty-program.php');

$context->controller->addJqueryPlugin(array('dimensions', 'cluetip'));

$customerPoints = (int)(LoyaltyModule::getPointsByCustomer((int)($cookie->id_customer)));

/* transform point into voucher if needed */
if (Tools::getValue('transform-points') == 'true' AND $customerPoints > 0)
{
	/* Generate a voucher code */
	$voucherCode = NULL;
	do
		$voucherCode = 'BONUS'.rand(1000, 100000);
	while (CartRule::cartRuleExists($voucherCode));

	/* Voucher creation and affectation to the customer */
	$cartRule = new CartRule();
	$cartRule->code = $voucherCode;
	$cartRule->id_customer = (int)$context->customer->id;
	$cartRule->id_currency = (int)$context->currency->id;
	$cartRule->reduction_amount = LoyaltyModule::getVoucherValue((int)$customerPoints);
	$cartRule->quantity = 1;
	$cartRule->quantity_per_user = 1;

	/* If merchandise returns are allowed, the voucher musn't be usable before this max return date */
	$dateFrom = Db::getInstance()->getValue('
	SELECT UNIX_TIMESTAMP(date_add) n
	FROM '._DB_PREFIX_.'loyalty
	WHERE id_cart_rule = 0 AND id_customer = '.(int)$cookie->id_customer.'
	ORDER BY date_add DESC');

	if (Configuration::get('PS_ORDER_RETURN'))
		$dateFrom += 60 * 60 * 24 * (int)Configuration::get('PS_ORDER_RETURN_NB_DAYS');

	$cartRule->date_from = date('Y-m-d H:i:s', $dateFrom);
	$cartRule->date_to = date('Y-m-d H:i:s', $dateFrom + 31536000); // + 1 year

	$cartRule->minimum_amount = (float)Configuration::get('PS_LOYALTY_MINIMAL');
	$cartRule->active = 1;

	$categories = Configuration::get('PS_LOYALTY_VOUCHER_CATEGORY');
	if ($categories != '' AND $categories != 0)
		$categories = explode(',', Configuration::get('PS_LOYALTY_VOUCHER_CATEGORY'));
	else
		die (Tools::displayError());

	$languages = Language::getLanguages(true);
	$default_text = Configuration::get('PS_LOYALTY_VOUCHER_DETAILS', (int)Configuration::get('PS_LANG_DEFAULT'));

	foreach ($languages AS $language)
	{
		$text = Configuration::get('PS_LOYALTY_VOUCHER_DETAILS', (int)$language['id_lang']);
		$cartRule->name[(int)$language['id_lang']] = $text ? strval($text) : strval($default_text);
	}

	if (is_array($categories) AND sizeof($categories))
		$cartRule->add(true, false, $categories);
	else
		$cartRule->add();

	/* Register order(s) which contributed to create this voucher */
	LoyaltyModule::registerDiscount($cartRule);

	Tools::redirect('modules/loyalty/loyalty-program.php');
}

include(dirname(__FILE__).'/../../header.php');

$orders = LoyaltyModule::getAllByIdCustomer((int)($cookie->id_customer), (int)($cookie->id_lang));
$displayorders = LoyaltyModule::getAllByIdCustomer((int)($cookie->id_customer), (int)($cookie->id_lang), false, true, ((int)(Tools::getValue('n')) > 0 ? (int)(Tools::getValue('n')) : 10), ((int)(Tools::getValue('p')) > 0 ? (int)(Tools::getValue('p')) : 1));
$smarty->assign(array(
	'orders' => $orders,
	'displayorders' => $displayorders,
	'pagination_link' => __PS_BASE_URI__.'modules/loyalty/loyalty-program.php',
	'totalPoints' => (int)$customerPoints,
	'voucher' => LoyaltyModule::getVoucherValue($customerPoints, (int)$context->currency->id),
	'validation_id' => LoyaltyStateModule::getValidationId(),
	'transformation_allowed' => $customerPoints > 0,
	'page' => ((int)(Tools::getValue('p')) > 0 ? (int)(Tools::getValue('p')) : 1),
	'nbpagination' => ((int)(Tools::getValue('n') > 0) ? (int)(Tools::getValue('n')) : 10),
	'nArray' => array(10, 20, 50),
	'max_page' => floor(sizeof($orders) / ((int)(Tools::getValue('n') > 0) ? (int)(Tools::getValue('n')) : 10))
));

/* Discounts */
$nbDiscounts = 0;
$discounts = array();
if ($ids_discount = LoyaltyModule::getDiscountByIdCustomer((int)$cookie->id_customer))
{
	$nbDiscounts = count($ids_discount);
	foreach ($ids_discount AS $key => $discount)
	{
		$discounts[$key] = new Discount((int)$discount['id_cart_rule'], (int)$cookie->id_lang);
		$discounts[$key]->orders = LoyaltyModule::getOrdersByIdDiscount((int)$discount['id_cart_rule']);
	}
}

$allCategories = Category::getSimpleCategories((int)($cookie->id_lang));
$voucherCategories = Configuration::get('PS_LOYALTY_VOUCHER_CATEGORY');
if ($voucherCategories != '' AND $voucherCategories != 0)
	$voucherCategories = explode(',', Configuration::get('PS_LOYALTY_VOUCHER_CATEGORY'));
else
	die(Tools::displayError());

if (sizeof($voucherCategories) == sizeof($allCategories))
	$categoriesNames = null;
else
{
	$categoriesNames = array();
	foreach ($allCategories AS $k => $allCategory)
		if (in_array($allCategory['id_category'], $voucherCategories))
			$categoriesNames[$allCategory['id_category']] = trim($allCategory['name']);
	if (!empty($categoriesNames))
		$categoriesNames = Tools::truncate(implode(', ', $categoriesNames), 150).'. ';
	else
		$categoriesNames = null;
}
$smarty->assign(array(
	'nbDiscounts' => (int)$nbDiscounts,
	'discounts' => $discounts,
	'minimalLoyalty' => (float)Configuration::get('PS_LOYALTY_MINIMAL'),
	'categories' => $categoriesNames));

$loyalty = new Loyalty();
echo $loyalty->display(dirname(__FILE__).'/loyalty.php', 'loyalty.tpl');

include(dirname(__FILE__).'/../../footer.php');

the function getAllByIdCustomer in lines 109 and 110 is in the LoyaltyModule.php:

	public static function getAllByIdCustomer($id_customer, $id_lang, $onlyValidate = false, $pagination = false, $nb = 10, $page = 1)
	{
		$query = '
		SELECT f.id_order AS id, f.date_add AS date, (o.total_paid - o.total_shipping) total_without_shipping, f.points, f.id_loyalty, f.id_loyalty_state, fsl.name state
		FROM `'._DB_PREFIX_.'loyalty` f
		LEFT JOIN `'._DB_PREFIX_.'orders` o ON (f.id_order = o.id_order)
		LEFT JOIN `'._DB_PREFIX_.'loyalty_state_lang` fsl ON (f.id_loyalty_state = fsl.id_loyalty_state AND fsl.id_lang = '.(int)($id_lang).')
		WHERE f.id_customer = '.(int)($id_customer);
		if ($onlyValidate === true)
			$query .= ' AND f.id_loyalty_state = '.(int)LoyaltyStateModule::getValidationId();
		$query .= ' GROUP BY f.id_loyalty '.
		($pagination ? 'LIMIT '.(((int)($page) - 1) * (int)($nb)).', '.(int)($nb) : '');

		return Db::getInstance()->executeS($query);
	}

And it all ends up in a view like this:

PascalVG · November 11, 2013

OK,

try this: LoyaltyModule.php:

public static function getAllByIdCustomer($id_customer, $id_lang, $onlyValidate = false, $pagination = false, $nb = 10, $page = 1)
	{
		$query = '
		SELECT f.id_order AS id, f.date_add AS date, (o.total_paid - o.total_shipping) total_without_shipping, f.points, f.id_loyalty, f.id_loyalty_state, fsl.name state
		FROM `'._DB_PREFIX_.'loyalty` f
		LEFT JOIN `'._DB_PREFIX_.'orders` o ON (f.id_order = o.id_order)
		LEFT JOIN `'._DB_PREFIX_.'loyalty_state_lang` fsl ON (f.id_loyalty_state = fsl.id_loyalty_state AND fsl.id_lang = '.(int)($id_lang).')
		WHERE f.id_customer = '.(int)($id_customer);
		if ($onlyValidate === true)
			$query .= ' AND f.id_loyalty_state = '.(int)LoyaltyStateModule::getValidationId();
		$query .= ' GROUP BY f.id_loyalty ORDER BY f.id_order DESC '.
		($pagination ? 'LIMIT '.(((int)($page) - 1) * (int)($nb)).', '.(int)($nb) : '');
 
		return Db::getInstance()->executeS($query);
	}

See? I added:

$query .= ' GROUP BY f.id_loyalty ORDER BY f.id_order DESC '.

almost at the end.

if you want not the order ID as the sorting field, but date:

$query .= ' GROUP BY f.id_loyalty ORDER BY date DESC '.

Hope this does the trick,

pascal

Edited November 11, 2013 by PascalVG
I put order by behind group by... (see edit history)

PascalVG · November 11, 2013

Small modification:

Put order behind group by:

$query .= ' GROUP BY f.id_loyalty ORDER BY f.id_order DESC '.

or

$query .= ' GROUP BY f.id_loyalty ORDER BY date DESC '.

Think that's better. (Modifications are done in original post already)

pascal

sakiri · November 13, 2013

Great! Works!!

And now I know what I did wrong ... I put the ORDER BY statement some lines above, and had a comma where there should be none - no wonder I ended up in a blank page

Thank you very much! I learn so much from you

PascalVG · November 13, 2013

Hi Sakiri,

glad it works!

(I still make mistakes every day, and php is terrible to debug.

As you said, just a comma can give you white pages (and headache... :-) )

pascal

Sign In

[SOLVED] How to sort loyalty points list descending?

Recommended Posts

sakiri

Link to comment

Share on other sites

PascalVG

Link to comment

Share on other sites

sakiri

Link to comment

Share on other sites

PascalVG

Link to comment

Share on other sites

PascalVG

Link to comment

Share on other sites

sakiri

Link to comment

Share on other sites

PascalVG

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Browse

Activity

Go back to prestashop.com