Magento custom sort on grid – sort increment_id by numeric not alpha

Posted on by
Reply

You can easily filter column in a magento grid (see my previous post) but customize a sort is a little bit more hand made.

In this example in the order grid, I want to sort by increment_id, but this is a text fields and I want to sort it by numeric.

default sort : 1, 10, 100, 2, 3 …
numeric sort : 1, 2, 3, 10, 100 …

If you look the SQL request, you got this

1
SELECT `main_table`.* FROM `sales_flat_order_grid` AS `main_table` ORDER BY increment_id DESC LIMIT 20

To tell MySQL to sort this text column by numeric, you can add “+ 0″ to the ORDER BY clause, this will transform the text field into numeric field

1
SELECT `main_table`.* FROM `sales_flat_order_grid` AS `main_table` ORDER BY increment_id + 0 DESC LIMIT 20

Now, how to tell Magento to add “+ 0″ to this sort ?

Override your grid class and modify the “_setCollectionOrder($column)” function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class Adin_Sales_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid {
    protected function _setCollectionOrder($column)
    {
        $collection = $this->getCollection();
        if ($collection) {
            $columnIndex = $column->getFilterIndex() ? $column->getFilterIndex() : $column->getIndex();
            if($columnIndex == 'increment_id')
            {
                $columnIndex = 'increment_id + 0';
            }
            $collection->setOrder($columnIndex, strtoupper($column->getDir()));
        }
        return $this;
    }
}

Magento custom filter on grid

Posted on by
Reply

Sometimes, you need a special column on your grid from another table or with some calculation.
It works well, column is displayed but filter doesn’t work.
Here a small example how to customize your filter :

On your grid class (Adin_Sponsorship_Block_Adminhtml_Sponsorship_Grid.class.php), override your _prepareColumns() function

1
2
3
4
5
6
7
8
9
10
protected function _prepareColumns()
{
        $this->addColumn('sponsor_email', array(
                'header'    => Mage::helper('evian_customer')->__('Sponsor Email'),
                'index'     => 'sponsor_email',
                'align'     => 'center',
                'filter_condition_callback' => array($this, '_filterSponsortEmail'),
            )
        );
}

The ‘filter_condition_callback’ option indicates to use as filter a specific method, on the current class ($this) the method _filterSponsortEmail().

So next step, create this method.

1
2
3
4
5
6
7
protected function _filterSponsortEmail($collection, $column)
{
    if (!$value = trim($column->getFilter()->getValue())) {
        return;
    }
    $this->getCollection()->addFieldToFilter('customer.email', array('like' => '%'.$value.'%'));
}

Yes, this is this simple.

If you use a datetime column, it’s just a bit more complicated, see below

1
2
3
4
5
6
7
8
$this->addColumn('created_at', array(
    'header'    => Mage::helper('evian_customer')->__('Date of sponsorship'),
    'index'     => 'created_at',
    'align'     => 'center',
    'type' => 'datetime',
    'filter_condition_callback' => array($this, '_filterDate'),
    )
);

and the filter method

1
2
3
4
5
6
7
8
9
10
11
protected function _filterDate($collection, $column)
{
    $filters = $column->getFilter()->getValue();
 
        $from = $filters['from'];
        $to = $filters['to'];
 
 
   $this->getCollection()->addFieldToFilter('main_table.created_at', array('gteq' => $from->toString('yyyy-MM-dd')));
    $this->getCollection()->addFieldToFilter('main_table.created_at', array('lteq' => $to->toString('yyyy-MM-dd')));
}

Magento filter products collection get only visible

Posted on by
Reply

You want to retrieve all your products but only the one visible in catalog or filter ?

You may use this

1
2
3
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*');
Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

But it’s depreacated and doesn’t work.
You may instead use this :

1
2
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*');
$collection->addFieldToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH);

doctrine2 random

Posted on by
Reply

For some really good reasons or, doctrine2 do not implement the RAND() fonction to sort randomly your query results. Here some dirty ways to do it. Don’t use them it’s bad.

One solution is to shuffle your collection of rows using php

1
2
3
4
5
6
7
8
9
10
11
  public function getRandom($max = 10,$site) {
    $results = $this->createQueryBuilder('u')
            ->where('u.site = :site')
            ->setParameter('site', $site)
            ->orderBy('u.sort', 'DESC')
            ->setMaxResults($max)
            ->getQuery()
            ->getResult();
    shuffle($results);
    return $results;
}

In this solution, you retrieve the last 10 rows and shuffle them after. Peformances are not too bad but you will always retrieves the same last rows from your table.

Another solution is to use the array_rand php fonction

1
2
3
4
5
6
7
8
9
10
  public function getRandom($site) {
    $results = $this->createQueryBuilder('u')
            ->where('u.site = :site')
            ->setParameter('site', $site)
            ->orderBy('u.sort', 'DESC')
            ->getQuery()
            ->getResult();
    $result2 = array_rand($results);
    return $result2;
}

In this case you fetch all rows from your table, this could be slow and memory consuming…

If you need to retrieve only one row, you can use somethinfg like this

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public function getOneRandom()
{
$em = $this->getEntityManager();
$max = $em->createQuery('
SELECT MAX(q.id) FROM questions q
')
->getSingleScalarResult();
return $em->createQuery('
SELECT q FROM questions q
WHERE q.id >= :random
ORDER BY q.id ASC
')
->setParameter('random',rand(0,$max))
->setMaxResults(1)
->getSingleResult();
}

This solution can only be used if you want to retrieve any of the tables rows, if you add a filtrer you may return an empty result.

This solution is not dirty it just use 2 queries instead of one. the tips is to use the offset. And you can use a filter !!!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$qCount = Doctrine::getTable('Questions')
     ->createQuery()
     ->select('count(*)')
     ->where('site = :site')
     ->setParameter('site', $site)
     ->fetchOne(array(), Doctrine::HYDRATE_NONE);
$question = Doctrine::getTable('Questions')
     ->createQuery()
     ->select('*')
     ->where('site = :site')
     ->setParameter('site', $site)
     ->limit(1)
     ->offset(rand(0, $qCount[0] - 1))
     ->fetchOne();

And you still can use native queries : http://docs.doctrine-project.org/en/latest/reference/native-sql.html

Magento multi fields validator for RIB

Posted on by
Reply

Magento come with a great form validator : VarienForm. These is a lot of existing validator, for every usage and you can easyly add some. This is how you can do it with a commun example, how to check RIB bank account.

First, our html code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<ul class="inlineblockli">
    <li>
        <label for="code_bank_pb"><?php echo $this->__('bank code') ?></label>
                <input type="text" class="inputText inputTextB validate-digits required-entry validate-length minimum-length-5 maximum-length-5" id="code_bank_pb" maxlength="5" name="prelevement[code_bank_pb]">
    </li>
    <li>
        <label for="code_guichet_pb"><?php echo $this->__('branch code') ?></label>
                <input type="text" class="inputText inputTextB validate-digits required-entry validate-length minimum-length-5 maximum-length-5" id="code_guichet_pb" maxlength="5" name="prelevement[code_guichet_pb]">
    </li>
    <li>
        <label for="num_compte"><?php echo $this->__('Account number to be debited') ?></label>
                <input type="text" class="inputText inputTextBig3 validate-digits required-entry validate-length minimum-length-11 maximum-length-11" id="num_compte" maxlength="11" name="prelevement[num_compte]">
    </li>
    <li>
        <label for="key_rib"><?php echo $this->__('RIB key') ?></label>
                <input type="text" class="inputText inputTextSm3 validate-digits required-entry validate-compte validate-length minimum-length-2 maximum-length-2" id="key_rib" maxlength="2" name="prelevement[key_rib]">
    </li>
</ul>

We first do a lenght validation with these 3 class : validate-length minimum-length-11 maximum-length-11

Then we validate the rib with this class : validate-compte
You will tell me this validator doesn’t exeist. That’s right, let’s create it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<script type="text/javascript">
    //<![CDATA[
    Validation.addAllThese(
        [
            ['validate-compte', '<?php echo Mage::helper('rating')->__('Please check your RIB account') ?>', function(v) {
                var code_banque = jQuery('#code_bank_pb').val();
                var code_guichet = jQuery('#code_guichet_pb').val();
                var numero_compte = jQuery('#num_compte').val();
                var cle           = jQuery('#key_rib').val();
 
                var CompteTmp = code_banque + code_guichet + numero_compte + cle;
 
                while(CompteTmp.length > 9)
                {
                    var CompteTmp2 = CompteTmp.substr(0,9) % 97;
                    CompteTmp = CompteTmp2 + CompteTmp.substr(9,CompteTmp.length);
                }
                var CompteTmp2 = CompteTmp.substr(0,9) % 97;
 
                if(CompteTmp2 % 97 == 0)
                {
                    return true;
                }
                return false;
            }]
        ]
    );
    //]]>
</script>

Some explainations :

1
jQuery('#code_bank_pb').val();

Magento use prototype, tu use jQuery, use jQuery instead of $

For the multi field validation, I grab the different fields from this

1
2
3
4
var code_banque   = jQuery('#code_bank_pb').val();
var code_guichet  = jQuery('#code_guichet_pb').val();
var numero_compte = jQuery('#num_compte').val();
var cle           = jQuery('#key_rib').val();

I don’t know if this is the cleaner way to do (not really usable for another code) but it works.
If you always use the same input id, you can reuse this code on another site.

Using GnuPG with PHP

Posted on by
Reply

GnuPG is a great tool to encrypt texts and files and you can use it with PHP, only if you install it succesfully.

First, you will get this error : Fatal error: Class ‘gnupg’ not found in ….

OK the lib is not installed, do it with pecl

1
pecl install gnupg

But you will also have this error :

1
2
configure: error: Please reinstall the gpgme distribution
ERROR: `/tmp/pear/temp/gnupg/configure' failed

You need to install the libgpgme too !!!

1
apt-get install libgpgme11-dev

Now retry to install gnupg

1
pecl install gnupg

It should be OK.

Don’t forget to modify your php.ini file to load the extension

1
extension=gnupg.so

And of course, restart your apache web server

1
/etc/init.d/apache2 restart

javascript get formated date time

Posted on by
Reply

There is no date time format function for javascript not using external libs. You have to do it yourself.
Here an examples for french format YYYY-mm-dd_HH:mm:ss

1
2
3
4
5
6
7
8
9
var curr = new Date();
var year = curr.getFullYear();
var month = (((curr.getMonth()+1) < 10)?'0:'') + (curr.getMonth()+1)
var day = ((curr.getDate() < 10)?'0':'')+curr.getDate()
var hour = ((curr.getHours() < 10)?'0':'')+curr.getHours()
var minute = ((curr.getMinutes() < 10)?'0':'')+curr.getMinutes()
var seconde = ((curr.getSeconds() < 10)?'0':'')+curr.getSeconds()
 
var output = year+'-'+month+'-'+day+'_'+hour+':'+minute+':'+seconde;

How to install phantomjs & casperjs

Posted on by
Reply

To install phantomjs & casperjs, do not use your distribution packages, you won’t have the right versions and your casperjs version will not be compatible with phantomjs version.

Instead, follow theses simple steps :

1
2
3
4
5
6
7
8
9
10
11
12
13
#phantomjs
cd /opt
wget https://bitbucket.org/ariya/phantomjs/downloads/phantomjs-1.9.7-linux-x86_64.tar.bz2
tar xjvf phantomjs-1.9.7-linux-x86_64.tar.bz2
ln -s ln -s phantomjs-1.9.7-linux-x86_64 phantomjs
ln -s /opt/phantomjs/bin/phantomjs /usr/local/bin
 
#casperjs
cd /opt
wget https://codeload.github.com/n1k0/casperjs/legacy.zip/1.1-beta3
unzip 1.1-beta3
ln -s n1k0-casperjs-4f105a9/ casperjs
ln -s /opt/casperjs/bin/casperjs /usr/local/bin/

To test it, simply run the casperjs command

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
root@bigone:/opt# casperjs
CasperJS version 1.1.0-beta3 at /opt/casperjs, using phantomjs version 1.9.7
Usage: casperjs [options] script.1 [script argument [script argument ...]]
      casperjs [options] test [test path [test path ...]]
      casperjs [options] selftest
      casperjs [options] __selfcommandtest
 
Options:
 
--verbose   Prints log messages to the console
--log-level Sets logging level
--help      Prints this help
--version   Prints out CasperJS version
--engine=name Use the given engine. Current supported engine: phantomjs and slimerjs
 
Read the docs http://docs.casperjs.org/