Auto assign clients to groups bases on number of items ordered

[Myles_UA]

Hi everyone? Is there a possibility to count number of items client brought per his orders and based on that number auto assign him to different customer groups?

Prestashop 1.7

[ventura]

It´s possible to do this with a specific sql script to update the records and add clients that meet that condition, eg

           -- You may need to change table prefixes depending on your installation
           SET @id_group := 4;   --  New Customer Group ID 
           SET @nb_orders := 5;    -- Customer Products Bought

					
				
			INSERT IGNORE INTO ps_customer_group
            (id_customer, id_group)
            SELECT od.id_customer, @id_group
            FROM ps_orders od
            LEFT JOIN ps_customer cu ON od.id_customer = cu.id_customer
            WHERE cu.`deleted` = 0
            AND cu.`active` = 1
            GROUP BY od.`id_customer`
            HAVING COUNT(*) > @nb_orders

 

You can also create a script and run it from your installation or run it on a crontab , eg

 

<?php

$nb_orders = 5;       //  New Customer Group ID 
$id_group = 4;        //  Customer Products Bought
 
include_once('config/config.inc.php');

$addInCustomerGroup = Db::getInstance()->execute('
            INSERT IGNORE INTO `' . _DB_PREFIX_ . 'customer_group`
            (`id_customer`, `id_group`)
            SELECT od.`id_customer`, ' . (int) $id_group  . '
            FROM `'._DB_PREFIX_.'orders` od
            LEFT JOIN `' . _DB_PREFIX_ . 'customer` cu ON od.`id_customer` = cu.`id_customer`
            WHERE cu.`deleted` = 0
            AND cu.`active` = 1
            GROUP BY od.`id_customer`
            HAVING COUNT(*) > '.(int)($nb_orders).'
            ');
   
if($addInCustomerGroup !== false) echo 'Affected rows firts query: '. $addInCustomerGroup;    

 

And also the installation includes a specific hook for the change of group that can be used in an override or  specific module

 

actionCustomerAddGroups