[SOLVED] Abandoned Cart emails in free module Customer Follow-up

[Pedro Lima]

Hi,

 

And here's me again with a crazy idea...

 

I have noticed that "Customer Follow-up" module is very straight forward regarding code, it's simple yet very useful.

 

So I wonder... how difficult could it be to code it also to check for abandoned carts and send emails based on that?

 

Let me know what you think about this idea and if you have already done that (it would be awesome because it would save me time )

 

Thanks.

 

Best regards,

Pedro Lima

Edited March 5, 2015 by Pedro Lima (see edit history)

[Pedro Lima]

Oh c'mon... I can't believe nobody ever though about this!

 

Will I have to do this all by my own?

I would like to do this with some help so we could then share it as a free module or, at least, free "hardcodding".

[Pedro Lima]

And here I am again... since nobyde (once again) answered to (exactly the same that happened here: https://www.prestashop.com/forums/topic/386967-solved-transfer-old-15-sales-database-to-new-16-database/ ) I decided to take a look at FollowUp module from Prestashop, which apparently isn't available anymore for download... I wonder why... oh wait, paid modules, that's it! -_-'

 

Anyway, here's the code I have changed from followup.php on root folder of module followup:

 

(before looking at the code, I am not being able to save settings in backoffice for this new option I created for abandoned carts and it's always showing that 1 email will be sent although if I put mysql query, it returns me more than 100 distinct results. Anyone can help me here?)

<?php
/*
* 2007-2014 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Academic Free License (AFL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/afl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to [email protected] so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <[email protected]>
*  @copyright  2007-2014 PrestaShop SA
*  @license    http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
*  International Registered Trademark & Property of PrestaShop SA
*/

if (!defined('_PS_VERSION_'))
	exit;

class Followup extends Module
{
	public function __construct()
	{
		$this->name = 'followup';
		$this->tab = 'advertising_marketing';
		$this->version = '1.6.5';
		$this->author = 'PrestaShop - Modded by MaiDot';
		$this->need_instance = 0;

		$this->conf_keys = array(
			'PS_FOLLOW_UP_ENABLE_1',
			'PS_FOLLOW_UP_ENABLE_2',
			'PS_FOLLOW_UP_ENABLE_3',
			'PS_FOLLOW_UP_ENABLE_4',
			'PS_FOLLOW_UP_ENABLE_5',
			'PS_FOLLOW_UP_AMOUNT_1',
			'PS_FOLLOW_UP_AMOUNT_2',
			'PS_FOLLOW_UP_AMOUNT_3',
			'PS_FOLLOW_UP_AMOUNT_4',
			'PS_FOLLOW_UP_AMOUNT_5',
			'PS_FOLLOW_UP_DAYS_1',
			'PS_FOLLOW_UP_DAYS_2',
			'PS_FOLLOW_UP_DAYS_3',
			'PS_FOLLOW_UP_DAYS_4',
			'PS_FOLLOW_UP_DAYS_5',
			'PS_FOLLOW_UP_THRESHOLD_3',
			'PS_FOLLOW_UP_DAYS_THRESHOLD_4',
			'PS_FOLLOW_UP_CLEAN_DB'
		);

		$this->bootstrap = true;
		parent::__construct();

		$secure_key = Configuration::get('PS_FOLLOWUP_SECURE_KEY');
		if($secure_key === false)
			Configuration::updateValue('PS_FOLLOWUP_SECURE_KEY', Tools::strtoupper(Tools::passwdGen(16)));

		$this->displayName = $this->l('Customer follow-up');
		$this->description = $this->l('Follow-up with your customers with daily customized e-mails.');
		$this->confirmUninstall = $this->l('Are you sure you want to delete all settings and your logs?');
	}

	public function install()
	{
		Db::getInstance()->execute('
		CREATE TABLE '._DB_PREFIX_.'log_email (
		`id_log_email` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
		`id_email_type` INT UNSIGNED NOT NULL ,
		`id_cart_rule` INT UNSIGNED NOT NULL ,
		`id_customer` INT UNSIGNED NULL ,
		`id_cart` INT UNSIGNED NULL ,
		`date_add` DATETIME NOT NULL,
		 INDEX `date_add`(`date_add`),
		 INDEX `id_cart`(`id_cart`)
		) ENGINE='._MYSQL_ENGINE_);

		foreach ($this->conf_keys as $key)
			Configuration::updateValue($key, 0);


		return parent::install();
	}

	public function uninstall()
	{
		foreach ($this->conf_keys as $key)
			Configuration::deleteByName($key);

		Configuration::deleteByName('PS_FOLLOWUP_SECURE_KEY');

		Db::getInstance()->execute('DROP TABLE '._DB_PREFIX_.'log_email');

		return parent::uninstall();
	}

	public function getContent()
	{
		$html = '';
		/* Save settings */
		if (Tools::isSubmit('submitFollowUp'))
		{
			$ok = true;
			foreach ($this->conf_keys as $c)
				if(Tools::getValue($c) !== false) // Prevent saving when URL is wrong
					$ok &= Configuration::updateValue($c, (float)Tools::getValue($c));
			if ($ok)
				$html .= $this->displayConfirmation($this->l('Settings updated succesfully'));
			else
				$html .= $this->displayError($this->l('Error occurred during settings update'));
		}
		$html .= $this->renderForm();
		$html .= $this->renderStats();

		return $html;
	}

	/* Log each sent e-mail */
	private function logEmail($id_email_type, $id_cart_rule, $id_customer = null, $id_cart = null)
	{
		$values = array(
			'id_email_type' => (int)$id_email_type,
			'id_cart_rule' => (int)$id_cart_rule,
			'date_add' => date('Y-m-d H:i:s')
		);
		if (!empty($id_cart))
			$values['id_cart'] = (int)$id_cart;
		if (!empty($id_customer))
			$values['id_customer'] = (int)$id_customer;
		Db::getInstance()->insert('log_email', $values);
	}

	/* Each cart which wasn't transformed into an order */
	private function cancelledCart($count = false)
	{
		$email_logs = $this->getLogsEmail(1);
		$sql = '
		SELECT c.id_cart, c.id_lang, cu.id_customer, c.id_shop, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'cart c
		LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_cart = c.id_cart)
		RIGHT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = c.id_customer)
		RIGHT JOIN '._DB_PREFIX_.'cart_product cp ON (cp.id_cart = c.id_cart)
		WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= c.date_add AND o.id_order IS NULL';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c');

		if (!empty($email_logs))
			$sql .= ' AND c.id_cart NOT IN ('.join(',', $email_logs).') GROUP BY c.id_cart';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_1', 'PS_FOLLOW_UP_DAYS_1'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(1, (float)$conf['PS_FOLLOW_UP_AMOUNT_1'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_1'].' day')), $this->l('Discount for your cancelled cart'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_1'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_1'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_1', Mail::l('Your cart and your discount', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(1, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart']);
			}
		}
	}

	private function getLogsEmail($email_type)
	{
		static $id_list = array(
			'1' => array(),
			'2' => array(),
			'3' => array(),
			'4' => array(),
			'5' => array(),
		);
		static $executed = false;

		if (!$executed)
		{
			$query = '
			SELECT id_cart, id_customer, id_email_type FROM '._DB_PREFIX_.'log_email
			WHERE id_email_type <> 4 OR date_add >= DATE_SUB(date_add,INTERVAL '.(int)Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4').' DAY)';
			$results = Db::getInstance()->executeS($query);
			foreach ($results as $line)
			{
				switch ($line['id_email_type'])
				{
					case 1:
						$id_list['1'][] = $line['id_cart'];
						break;
					case 2:
						$id_list['2'][] = $line['id_cart'];
						break;
					case 3:
						$id_list['3'][] = $line['id_customer'];
						break;
					case 4:
						$id_list['4'][] = $line['id_customer'];
						break;
					case 5:
						$id_list['5'][] = $line['secure_key'];
						break;
				}
			}
			$executed = true;
		}

		return $id_list[$email_type];
	}

	/* For all validated orders, a discount if re-ordering before x days */
	private function reOrder($count = false)
	{
		$email_logs = $this->getLogsEmail(2);
		$sql = '
		SELECT o.id_order, c.id_cart, o.id_lang, o.id_shop, cu.id_customer, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'orders o
		LEFT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = o.id_customer)
		LEFT JOIN '._DB_PREFIX_.'cart c ON (c.id_cart = o.id_cart)
			WHERE o.valid = 1
			AND c.date_add >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
			AND cu.is_guest = 0';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'o');

		if (!empty($email_logs))
			$sql .= ' AND o.id_cart NOT IN ('.join(',', $email_logs).')';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_2', 'PS_FOLLOW_UP_DAYS_2'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(2, (float)$conf['PS_FOLLOW_UP_AMOUNT_2'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_2'].' day')), $this->l('Thank you for your order.'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_2'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_2'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_2', Mail::l('Thanks for your order', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(2, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart']);
			}
		}
	}

	/* For all customers with more than x euros in 90 days */
	private function bestCustomer($count = false)
	{
		$email_logs = $this->getLogsEmail(3);

		$sql = '
		SELECT SUM(o.total_paid) total, c.id_cart, o.id_lang, cu.id_customer, cu.id_shop, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'orders o
		LEFT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = o.id_customer)
		LEFT JOIN '._DB_PREFIX_.'cart c ON (c.id_cart = o.id_cart)
			WHERE o.valid = 1
			AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= o.date_add
			AND cu.is_guest = 0 ';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'o');

		if (!empty($email_logs))
			$sql .= ' AND cu.id_customer NOT IN ('.join(',', $email_logs).') ';

		$sql .= '
		GROUP BY o.id_customer
			HAVING total >= '.(float)Configuration::get('PS_FOLLOW_UP_THRESHOLD_3');

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_3', 'PS_FOLLOW_UP_DAYS_3'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(3, (float)$conf['PS_FOLLOW_UP_AMOUNT_3'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_3'].' day')), $this->l('You are one of our best customers!'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_3'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_3'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_3', Mail::l('You are one of our best customers', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(3, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart']);
			}
		}
	}

	/* For all customers with no orders since more than x days */

	/**
	 * badCustomer send mails to all customers with no orders since more than x days,
	 * with at least one valid order in history
	 *
	 * @param boolean $count if set to true, will return number of customer (default : false, will send mails, no return value)
	 *
	 * @return void
	 */
	private function badCustomer($count = false)
	{
		$email_logs = $this->getLogsEmail(4);
		$sql = '
			SELECT o.id_lang, c.id_cart, cu.id_customer, cu.id_shop, cu.firstname, cu.lastname, cu.email, (SELECT COUNT(o.id_order) FROM '._DB_PREFIX_.'orders o WHERE o.id_customer = cu.id_customer and o.valid = 1) nb_orders
			FROM '._DB_PREFIX_.'customer cu
			LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_customer = cu.id_customer)
			LEFT JOIN '._DB_PREFIX_.'cart c ON (c.id_cart = o.id_cart)
				WHERE cu.id_customer NOT IN (SELECT o.id_customer FROM '._DB_PREFIX_.'orders o WHERE DATE_SUB(CURDATE(),INTERVAL '.(int)Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4').' DAY) <= o.date_add)
				AND cu.is_guest = 0 ';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'cu');

		if (!empty($email_logs))
			$sql .= ' AND cu.id_customer NOT IN ('.join(',', $email_logs).') ';

		$sql .= ' GROUP BY cu.id_customer HAVING nb_orders >= 1';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_4', 'PS_FOLLOW_UP_DAYS_4'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(4, (float)$conf['PS_FOLLOW_UP_AMOUNT_4'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_4'].' day')), $this->l('We miss you!'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_4'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_4'],
					'{days_threshold}' => (int)Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4'),
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_4', Mail::l('We miss you', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(4, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart']);
			}
		}
	}
	
	/* For all abandoned carts, a discount if ordering before x days */
	private function abandonedCart($count = false)
	{
		$email_logs = $this->getLogsEmail(5);
		$sql = '
		SELECT c.id_cart, c.id_lang, c.id_shop, c.secure_key, cu.id_customer, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'cart c
		LEFT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = c.id_customer)
			WHERE c.id_customer > 0
			AND c.id_cart NOT IN (SELECT id_cart FROM '._DB_PREFIX_.'orders)
			AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= c.date_upd';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'o');

		if (!empty($email_logs))
			$sql .= ' AND c.secure_key NOT IN ('.join(',', $email_logs).') GROUP BY cu.email';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_5', 'PS_FOLLOW_UP_DAYS_5'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(5, (float)$conf['PS_FOLLOW_UP_AMOUNT_5'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_5'].' day')), $this->l('Your abandoned shopping cart'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_5'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_5'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_5', Mail::l('Your abandoned shopping cart', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(5, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart']);
			}
		}
	}


	private function createDiscount($id_email_type, $amount, $id_customer, $date_validity, $description)
	{
		$cart_rule = new CartRule();
		$cart_rule->reduction_percent = (float)$amount;
		$cart_rule->id_customer = (int)$id_customer;
		$cart_rule->date_to = $date_validity;
		$cart_rule->date_from = date('Y-m-d H:i:s');
		$cart_rule->quantity = 1;
		$cart_rule->quantity_per_user = 1;
		$cart_rule->cart_rule_restriction = 1;
		$cart_rule->minimum_amount = 0;

		$languages = Language::getLanguages(true);
		foreach ($languages as $language)
			$cart_rule->name[(int)$language['id_lang']] = $description;

		$code = 'FLW-'.(int)$id_email_type.'-'.Tools::strtoupper(Tools::passwdGen(10));
		$cart_rule->code = $code;
		$cart_rule->active = 1;
		if (!$cart_rule->add())
			return false;

		return $cart_rule;
	}

	public function cronTask()
	{
		Context::getContext()->link = new Link(); //when this is call by cron context is not init
		$conf = Configuration::getMultiple(array(
			'PS_FOLLOW_UP_ENABLE_1',
			'PS_FOLLOW_UP_ENABLE_2',
			'PS_FOLLOW_UP_ENABLE_3',
			'PS_FOLLOW_UP_ENABLE_4',
			'PS_FOLLOW_UP_ENABLE_5',
			'PS_FOLLOW_UP_CLEAN_DB'
		));

		if ($conf['PS_FOLLOW_UP_ENABLE_1'])
			$this->cancelledCart();
		if ($conf['PS_FOLLOW_UP_ENABLE_2'])
			$this->reOrder();
		if ($conf['PS_FOLLOW_UP_ENABLE_3'])
			$this->bestCustomer();
		if ($conf['PS_FOLLOW_UP_ENABLE_4'])
			$this->badCustomer();
		if ($conf['PS_FOLLOW_UP_ENABLE_5'])
			$this->abandonedCart();

		/* Clean-up database by deleting all outdated discounts */
		if ($conf['PS_FOLLOW_UP_CLEAN_DB'] == 1)
		{
			$outdated_discounts = Db::getInstance()->executeS('SELECT id_cart_rule FROM '._DB_PREFIX_.'cart_rule WHERE date_to < NOW() AND code LIKE "FLW-%"');
			foreach ($outdated_discounts as $outdated_discount)
			{
				$cart_rule = new CartRule((int)$outdated_discount['id_cart_rule']);
				if (Validate::isLoadedObject($cart_rule))
					$cart_rule->delete();
			}
		}
	}

	public function renderStats()
	{
		$stats = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
			SELECT DATE_FORMAT(l.date_add, \'%Y-%m-%d\') date_stat, l.id_email_type, COUNT(l.id_log_email) nb,
			(SELECT COUNT(l2.id_cart_rule)
			FROM '._DB_PREFIX_.'log_email l2
			LEFT JOIN '._DB_PREFIX_.'order_cart_rule ocr ON (ocr.id_cart_rule = l2.id_cart_rule)
			LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_order = ocr.id_order)
			WHERE l2.id_email_type = l.id_email_type AND l2.date_add = l.date_add AND ocr.id_order IS NOT NULL AND o.valid = 1) nb_used
			FROM '._DB_PREFIX_.'log_email l
			WHERE l.date_add >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
			GROUP BY DATE_FORMAT(l.date_add, \'%Y-%m-%d\'), l.id_email_type');

		$stats_array = array();
		foreach ($stats as $stat)
		{
			$stats_array[$stat['date_stat']][$stat['id_email_type']]['nb'] = (int)$stat['nb'];
			$stats_array[$stat['date_stat']][$stat['id_email_type']]['nb_used'] = (int)$stat['nb_used'];
		}

		foreach ($stats_array as $date_stat => $array)
		{
			$rates = array();
			for ($i = 1; $i != 5; $i++)
				if (isset($stats_array[$date_stat][$i]['nb']) && isset($stats_array[$date_stat][$i]['nb_used']) && $stats_array[$date_stat][$i]['nb_used'] > 0)
					$rates[$i] = number_format(($stats_array[$date_stat][$i]['nb_used'] / $stats_array[$date_stat][$i]['nb']) * 100, 2, '.', '');
			for ($i = 1; $i != 5; $i++)
			{
				$stats_array[$date_stat][$i]['nb'] = isset($stats_array[$date_stat][$i]['nb']) ? (int)$stats_array[$date_stat][$i]['nb'] : 0;
				$stats_array[$date_stat][$i]['nb_used'] = isset($stats_array[$date_stat][$i]['nb_used']) ? (int)$stats_array[$date_stat][$i]['nb_used'] : 0;
				$stats_array[$date_stat][$i]['rate'] = isset($rates[$i]) ? '<b>'.$rates[$i].'</b>' : '0.00';
			}
			ksort($stats_array[$date_stat]);
		}

		$this->context->smarty->assign(array('stats_array' => $stats_array));

		return $this->display(__FILE__, 'stats.tpl');
	}

	public function renderForm()
	{
		$currency = new Currency((int)Configuration::get('PS_CURRENCY_DEFAULT'));

		$n1 = $this->cancelledCart(true);
		$n2 = $this->reOrder(true);
		$n3 = $this->bestCustomer(true);
		$n4 = $this->badCustomer(true);
		$n5 = $this->abandonedCart(true);

		$cron_info = '';
		if (Shop::getContext() === Shop::CONTEXT_SHOP)
			$cron_info = $this->l('Define the settings and paste the following URL in the crontab, or call it manually on a daily basis:').'<br />
								<b>'.$this->context->shop->getBaseURL().'modules/followup/cron.php?secure_key='.Configuration::get('PS_FOLLOWUP_SECURE_KEY').'</b></p>';

		$fields_form_1 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Informations'),
					'icon' => 'icon-cogs',
				),
				'description' => $this->l('Four kinds of e-mail alerts are available in order to stay in touch with your customers!').'<br />'.$cron_info,
			)
		);

		$fields_form_2 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Cancelled carts'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each cancelled cart (with no order), generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_1',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_1',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_1',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('The next process will send %d e-mail(s).'), $n1)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_3 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Re-order'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each validated order, generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_2',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_2',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_2',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n2)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_4 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Best customers'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each customer raising a threshold, generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_3',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_3',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Threshold'),
						'name' => 'PS_FOLLOW_UP_THRESHOLD_3',
						'suffix' => $currency->sign,
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_3',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n3)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_5 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Bad customers'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each customer who has already placed at least one order and with no orders since a given duration, generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_4',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_4',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Since x days'),
						'name' => 'PS_FOLLOW_UP_DAYS_THRESHOLD_4',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_4',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n4)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_6 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Abandoned Carts'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each abandoned cart, generate a discount and send it to customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_5',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_5',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_5',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n5)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_7 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('General'),
					'icon' => 'icon-cogs'
				),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Delete outdated discounts during each launch to clean database'),
						'name' => 'PS_FOLLOW_UP_CLEAN_DB',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$helper = new HelperForm();
		$helper->show_toolbar = false;
		$helper->table = $this->table;
		$lang = new Language((int)Configuration::get('PS_LANG_DEFAULT'));
		$helper->default_form_language = $lang->id;
		$helper->allow_employee_form_lang = Configuration::get('PS_BO_ALLOW_EMPLOYEE_FORM_LANG') ? Configuration::get('PS_BO_ALLOW_EMPLOYEE_FORM_LANG') : 0;
		$helper->identifier = $this->identifier;
		$helper->override_folder = '/';
		$helper->module = $this;
		$helper->submit_action = 'submitFollowUp';
		$helper->currentIndex = $this->context->link->getAdminLink('AdminModules', false).'&configure='.$this->name.'&tab_module='.$this->tab.'&module_name='.$this->name;
		$helper->token = Tools::getAdminTokenLite('AdminModules');
		$helper->tpl_vars = array(
			'fields_value' => $this->getConfigFieldsValues(),
			'languages' => $this->context->controller->getLanguages(),
			'id_language' => $this->context->language->id
		);

		return $helper->generateForm(array(
			$fields_form_1,
			$fields_form_2,
			$fields_form_3,
			$fields_form_4,
			$fields_form_5,
			$fields_form_6,
			$fields_form_7
		));
	}

	public function getConfigFieldsValues()
	{
		return array(
			'PS_FOLLOW_UP_ENABLE_1' => Tools::getValue('PS_FOLLOW_UP_ENABLE_1', Configuration::get('PS_FOLLOW_UP_ENABLE_1')),
			'PS_FOLLOW_UP_DAYS_1' => Tools::getValue('PS_FOLLOW_UP_DAYS_1', Configuration::get('PS_FOLLOW_UP_DAYS_1')),
			'PS_FOLLOW_UP_AMOUNT_1' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_1', Configuration::get('PS_FOLLOW_UP_AMOUNT_1')),
			'PS_FOLLOW_UP_ENABLE_2' => Tools::getValue('PS_FOLLOW_UP_ENABLE_2', Configuration::get('PS_FOLLOW_UP_ENABLE_2')),
			'PS_FOLLOW_UP_DAYS_2' => Tools::getValue('PS_FOLLOW_UP_DAYS_2', Configuration::get('PS_FOLLOW_UP_DAYS_2')),
			'PS_FOLLOW_UP_AMOUNT_2' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_2', Configuration::get('PS_FOLLOW_UP_AMOUNT_2')),
			'PS_FOLLOW_UP_THRESHOLD_3' => Tools::getValue('PS_FOLLOW_UP_THRESHOLD_3', Configuration::get('PS_FOLLOW_UP_THRESHOLD_3')),
			'PS_FOLLOW_UP_DAYS_3' => Tools::getValue('PS_FOLLOW_UP_DAYS_3', Configuration::get('PS_FOLLOW_UP_DAYS_3')),
			'PS_FOLLOW_UP_ENABLE_3' => Tools::getValue('PS_FOLLOW_UP_ENABLE_3', Configuration::get('PS_FOLLOW_UP_ENABLE_3')),
			'PS_FOLLOW_UP_AMOUNT_3' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_3', Configuration::get('PS_FOLLOW_UP_AMOUNT_3')),
			'PS_FOLLOW_UP_AMOUNT_4' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_4', Configuration::get('PS_FOLLOW_UP_AMOUNT_4')),
			'PS_FOLLOW_UP_ENABLE_4' => Tools::getValue('PS_FOLLOW_UP_ENABLE_4', Configuration::get('PS_FOLLOW_UP_ENABLE_4')),
			'PS_FOLLOW_UP_AMOUNT_4' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_5', Configuration::get('PS_FOLLOW_UP_AMOUNT_5')),
			'PS_FOLLOW_UP_ENABLE_4' => Tools::getValue('PS_FOLLOW_UP_ENABLE_5', Configuration::get('PS_FOLLOW_UP_ENABLE_5')),
			'PS_FOLLOW_UP_DAYS_THRESHOLD_4' => Tools::getValue('PS_FOLLOW_UP_DAYS_THRESHOLD_4', Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4')),
			'PS_FOLLOW_UP_DAYS_4' => Tools::getValue('PS_FOLLOW_UP_DAYS_4', Configuration::get('PS_FOLLOW_UP_DAYS_4')),
			'PS_FOLLOW_UP_CLEAN_DB' => Tools::getValue('PS_FOLLOW_UP_CLEAN_DB', Configuration::get('PS_FOLLOW_UP_CLEAN_DB')),
		);
	}
}

[Pedro Lima]

Ok, immediately after I post my own answer, I saw what was the problem for backoffice not being saved, I actually forgot to add a line on the last lines of code and I forgot to change something there, instead of putting the entire code I will let you know the changes on the code above (last lines):

 

Change this:

'PS_FOLLOW_UP_AMOUNT_4' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_5', Configuration::get('PS_FOLLOW_UP_AMOUNT_5')),
'PS_FOLLOW_UP_ENABLE_4' => Tools::getValue('PS_FOLLOW_UP_ENABLE_5', Configuration::get('PS_FOLLOW_UP_ENABLE_5')),

Into this:

'PS_FOLLOW_UP_AMOUNT_5' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_5', Configuration::get('PS_FOLLOW_UP_AMOUNT_5')),
'PS_FOLLOW_UP_ENABLE_5' => Tools::getValue('PS_FOLLOW_UP_ENABLE_5', Configuration::get('PS_FOLLOW_UP_ENABLE_5')),
'PS_FOLLOW_UP_DAYS_5' => Tools::getValue('PS_FOLLOW_UP_DAYS_5', Configuration::get('PS_FOLLOW_UP_DAYS_5')),

Problem with showing number 1 in backend as total emails that will be sent isn't yet solved, although query seems perfect...

This is the mysql query:

SELECT c.id_cart, c.id_lang, c.id_shop, c.secure_key, cu.id_customer, cu.firstname, cu.lastname, cu.email
FROM ps_cart c
LEFT JOIN ps_customer cu ON ( cu.id_customer = c.id_customer )
WHERE c.id_customer >0
AND c.id_cart NOT IN (SELECT id_cart FROM ps_orders)
AND DATE_SUB( CURDATE( ) , INTERVAL 90 DAY ) <= c.date_upd
AND c.secure_key NOT IN (SELECT secure_key FROM ps_log_email)
GROUP BY cu.email

PS: This is the full query, obviously that inside PHP code it's different (take a look at the code above)

 

PPS: If you noticed I am selecting secure_key inside ps_log_email and for those that eventually paid attention, I am not updating that table corretly when function logEmail is called, so I will post the correct code bellow as soon as I find the problem I described regarding total emails being sent.

Edited February 13, 2015 by plima (see edit history)

[Pedro Lima]

OH YEAH MY FRIENDS, HERE'S THE WORKING CODE!

<?php
/*
* 2007-2014 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Academic Free License (AFL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/afl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to [email protected] so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <[email protected]>
*  @copyright  2007-2014 PrestaShop SA
*  @license    http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
*  International Registered Trademark & Property of PrestaShop SA
*/

if (!defined('_PS_VERSION_'))
	exit;

class Followup extends Module
{
	public function __construct()
	{
		$this->name = 'followup';
		$this->tab = 'advertising_marketing';
		$this->version = '1.7';
		$this->author = 'PrestaShop - Modded by MaiLayout';
		$this->need_instance = 0;

		$this->conf_keys = array(
			'PS_FOLLOW_UP_ENABLE_1',
			'PS_FOLLOW_UP_ENABLE_2',
			'PS_FOLLOW_UP_ENABLE_3',
			'PS_FOLLOW_UP_ENABLE_4',
			'PS_FOLLOW_UP_ENABLE_5',
			'PS_FOLLOW_UP_AMOUNT_1',
			'PS_FOLLOW_UP_AMOUNT_2',
			'PS_FOLLOW_UP_AMOUNT_3',
			'PS_FOLLOW_UP_AMOUNT_4',
			'PS_FOLLOW_UP_AMOUNT_5',
			'PS_FOLLOW_UP_DAYS_1',
			'PS_FOLLOW_UP_DAYS_2',
			'PS_FOLLOW_UP_DAYS_3',
			'PS_FOLLOW_UP_DAYS_4',
			'PS_FOLLOW_UP_DAYS_5',
			'PS_FOLLOW_UP_THRESHOLD_3',
			'PS_FOLLOW_UP_DAYS_THRESHOLD_4',
			'PS_FOLLOW_UP_CLEAN_DB'
		);

		$this->bootstrap = true;
		parent::__construct();

		$secure_key = Configuration::get('PS_FOLLOWUP_SECURE_KEY');
		if($secure_key === false)
			Configuration::updateValue('PS_FOLLOWUP_SECURE_KEY', Tools::strtoupper(Tools::passwdGen(16)));

		$this->displayName = $this->l('Customer follow-up');
		$this->description = $this->l('Follow-up with your customers with daily customized e-mails.');
		$this->confirmUninstall = $this->l('Are you sure you want to delete all settings and your logs?');
	}

	public function install()
	{
		Db::getInstance()->execute('
		CREATE TABLE '._DB_PREFIX_.'log_email (
		`id_log_email` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
		`id_email_type` INT UNSIGNED NOT NULL ,
		`id_cart_rule` INT UNSIGNED NOT NULL ,
		`id_customer` INT UNSIGNED NULL ,
		`id_cart` INT UNSIGNED NULL ,
		`date_add` DATETIME NOT NULL,
                `secure_key` char(32) NOT NULL DEFAULT "",
		 INDEX `date_add`(`date_add`),
		 INDEX `id_cart`(`id_cart`)
		) ENGINE='._MYSQL_ENGINE_);

		foreach ($this->conf_keys as $key)
			Configuration::updateValue($key, 0);


		return parent::install();
	}

	public function uninstall()
	{
		foreach ($this->conf_keys as $key)
			Configuration::deleteByName($key);

		Configuration::deleteByName('PS_FOLLOWUP_SECURE_KEY');

		Db::getInstance()->execute('DROP TABLE '._DB_PREFIX_.'log_email');

		return parent::uninstall();
	}

	public function getContent()
	{
		$html = '';
		/* Save settings */
		if (Tools::isSubmit('submitFollowUp'))
		{
			$ok = true;
			foreach ($this->conf_keys as $c)
				if(Tools::getValue($c) !== false) // Prevent saving when URL is wrong
					$ok &= Configuration::updateValue($c, (float)Tools::getValue($c));
			if ($ok)
				$html .= $this->displayConfirmation($this->l('Settings updated succesfully'));
			else
				$html .= $this->displayError($this->l('Error occurred during settings update'));
		}
		$html .= $this->renderForm();
		$html .= $this->renderStats();

		return $html;
	}

	/* Log each sent e-mail */
	private function logEmail($id_email_type, $id_cart_rule, $id_customer = null, $id_cart = null, $secure_key = null)
	{
		$values = array(
			'id_email_type' => (int)$id_email_type,
			'id_cart_rule' => (int)$id_cart_rule,
			'date_add' => date('Y-m-d H:i:s')
		);
		if (!empty($id_cart))
			$values['id_cart'] = (int)$id_cart;
		if (!empty($id_customer))
			$values['id_customer'] = (int)$id_customer;
		if (!empty($secure_key))
			$values['secure_key'] = $secure_key;
		Db::getInstance()->insert('log_email', $values);
	}

	/* Each cart which wasn't transformed into an order */
	private function cancelledCart($count = false)
	{
		$email_logs = $this->getLogsEmail(1);
		$sql = '
		SELECT c.id_cart, c.id_lang, cu.id_customer, c.id_shop, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'cart c
		LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_cart = c.id_cart)
		RIGHT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = c.id_customer)
		RIGHT JOIN '._DB_PREFIX_.'cart_product cp ON (cp.id_cart = c.id_cart)
		WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= c.date_add AND o.id_order IS NULL';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c');

		if (!empty($email_logs))
			$sql .= ' AND c.id_cart NOT IN ('.join(',', $email_logs).') GROUP BY c.id_cart';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_1', 'PS_FOLLOW_UP_DAYS_1'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(1, (float)$conf['PS_FOLLOW_UP_AMOUNT_1'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_1'].' day')), $this->l('Discount for your cancelled cart'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_1'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_1'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_1', Mail::l('Your cart and your discount', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(1, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart'], 0);
			}
		}
	}

	private function getLogsEmail($email_type)
	{
		static $id_list = array(
			'1' => array(),
			'2' => array(),
			'3' => array(),
			'4' => array(),
			'5' => array(),
		);
		static $executed = false;

		if (!$executed)
		{
			$query = '
			SELECT id_cart, id_customer, id_email_type FROM '._DB_PREFIX_.'log_email
			WHERE id_email_type <> 4 OR date_add >= DATE_SUB(date_add,INTERVAL '.(int)Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4').' DAY)';
			$results = Db::getInstance()->executeS($query);
			foreach ($results as $line)
			{
				switch ($line['id_email_type'])
				{
					case 1:
						$id_list['1'][] = $line['id_cart'];
						break;
					case 2:
						$id_list['2'][] = $line['id_cart'];
						break;
					case 3:
						$id_list['3'][] = $line['id_customer'];
						break;
					case 4:
						$id_list['4'][] = $line['id_customer'];
						break;
					case 5:
						$id_list['5'][] = $line['secure_key'];
						break;
				}
			}
			$executed = true;
		}

		return $id_list[$email_type];
	}

	/* For all validated orders, a discount if re-ordering before x days */
	private function reOrder($count = false)
	{
		$email_logs = $this->getLogsEmail(2);
		$sql = '
		SELECT o.id_order, c.id_cart, o.id_lang, o.id_shop, cu.id_customer, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'orders o
		LEFT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = o.id_customer)
		LEFT JOIN '._DB_PREFIX_.'cart c ON (c.id_cart = o.id_cart)
			WHERE o.valid = 1
			AND c.date_add >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
			AND cu.is_guest = 0';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'o');

		if (!empty($email_logs))
			$sql .= ' AND o.id_cart NOT IN ('.join(',', $email_logs).')';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_2', 'PS_FOLLOW_UP_DAYS_2'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(2, (float)$conf['PS_FOLLOW_UP_AMOUNT_2'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_2'].' day')), $this->l('Thank you for your order.'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_2'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_2'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_2', Mail::l('Thanks for your order', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(2, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart'], 0);
			}
		}
	}

	/* For all customers with more than x euros in 90 days */
	private function bestCustomer($count = false)
	{
		$email_logs = $this->getLogsEmail(3);

		$sql = '
		SELECT SUM(o.total_paid) total, c.id_cart, o.id_lang, cu.id_customer, cu.id_shop, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'orders o
		LEFT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = o.id_customer)
		LEFT JOIN '._DB_PREFIX_.'cart c ON (c.id_cart = o.id_cart)
			WHERE o.valid = 1
			AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= o.date_add
			AND cu.is_guest = 0 ';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'o');

		if (!empty($email_logs))
			$sql .= ' AND cu.id_customer NOT IN ('.join(',', $email_logs).') ';

		$sql .= '
		GROUP BY o.id_customer
			HAVING total >= '.(float)Configuration::get('PS_FOLLOW_UP_THRESHOLD_3');

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_3', 'PS_FOLLOW_UP_DAYS_3'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(3, (float)$conf['PS_FOLLOW_UP_AMOUNT_3'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_3'].' day')), $this->l('You are one of our best customers!'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_3'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_3'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_3', Mail::l('You are one of our best customers', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(3, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart'], 0);
			}
		}
	}

	/* For all customers with no orders since more than x days */

	/**
	 * badCustomer send mails to all customers with no orders since more than x days,
	 * with at least one valid order in history
	 *
	 * @param boolean $count if set to true, will return number of customer (default : false, will send mails, no return value)
	 *
	 * @return void
	 */
	private function badCustomer($count = false)
	{
		$email_logs = $this->getLogsEmail(4);
		$sql = '
			SELECT o.id_lang, c.id_cart, cu.id_customer, cu.id_shop, cu.firstname, cu.lastname, cu.email, (SELECT COUNT(o.id_order) FROM '._DB_PREFIX_.'orders o WHERE o.id_customer = cu.id_customer and o.valid = 1) nb_orders
			FROM '._DB_PREFIX_.'customer cu
			LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_customer = cu.id_customer)
			LEFT JOIN '._DB_PREFIX_.'cart c ON (c.id_cart = o.id_cart)
				WHERE cu.id_customer NOT IN (SELECT o.id_customer FROM '._DB_PREFIX_.'orders o WHERE DATE_SUB(CURDATE(),INTERVAL '.(int)Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4').' DAY) <= o.date_add)
				AND cu.is_guest = 0 ';

		$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'cu');

		if (!empty($email_logs))
			$sql .= ' AND cu.id_customer NOT IN ('.join(',', $email_logs).') ';

		$sql .= ' GROUP BY cu.id_customer HAVING nb_orders >= 1';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_4', 'PS_FOLLOW_UP_DAYS_4'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(4, (float)$conf['PS_FOLLOW_UP_AMOUNT_4'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_4'].' day')), $this->l('We miss you!'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_4'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_4'],
					'{days_threshold}' => (int)Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4'),
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_4', Mail::l('We miss you', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(4, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart'], 0);
			}
		}
	}
	
	/* For all abandoned carts, a discount if ordering before x days */
	private function abandonedCart($count = false)
	{
		$email_logs = $this->getLogsEmail(5);
		$sql = '
		SELECT c.id_cart, c.id_lang, c.id_shop, c.secure_key, cu.id_customer, cu.firstname, cu.lastname, cu.email
		FROM '._DB_PREFIX_.'cart c
		LEFT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = c.id_customer)
			WHERE c.id_customer > 0
			AND c.id_cart NOT IN (SELECT id_cart FROM '._DB_PREFIX_.'orders)
			AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= c.date_upd';

		//$sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'o');

		if (!empty($email_logs))
			$sql .= ' AND c.secure_key NOT IN ('.join(',', $email_logs).') GROUP BY cu.email';

		$emails = Db::getInstance()->executeS($sql);

		if ($count || !count($emails))
			return count($emails);

		$conf = Configuration::getMultiple(array('PS_FOLLOW_UP_AMOUNT_5', 'PS_FOLLOW_UP_DAYS_5'));
		foreach ($emails as $email)
		{
			$voucher = $this->createDiscount(5, (float)$conf['PS_FOLLOW_UP_AMOUNT_5'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['PS_FOLLOW_UP_DAYS_5'].' day')), $this->l('Your abandoned shopping cart'));
			if ($voucher !== false)
			{
				$template_vars = array(
					'{email}' => $email['email'],
					'{lastname}' => $email['lastname'],
					'{firstname}' => $email['firstname'],
					'{amount}' => $conf['PS_FOLLOW_UP_AMOUNT_5'],
					'{days}' => $conf['PS_FOLLOW_UP_DAYS_5'],
					'{voucher_num}' => $voucher->code
				);
				Mail::Send((int)$email['id_lang'], 'followup_5', Mail::l('Your abandoned shopping cart', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
				$this->logEmail(5, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart'], $email['secure_key']);
			}
		}
	}


	private function createDiscount($id_email_type, $amount, $id_customer, $date_validity, $description)
	{
		$cart_rule = new CartRule();
		$cart_rule->reduction_percent = (float)$amount;
		$cart_rule->id_customer = (int)$id_customer;
		$cart_rule->date_to = $date_validity;
		$cart_rule->date_from = date('Y-m-d H:i:s');
		$cart_rule->quantity = 1;
		$cart_rule->quantity_per_user = 1;
		$cart_rule->cart_rule_restriction = 1;
		$cart_rule->minimum_amount = 0;

		$languages = Language::getLanguages(true);
		foreach ($languages as $language)
			$cart_rule->name[(int)$language['id_lang']] = $description;

		$code = 'FLW-'.(int)$id_email_type.'-'.Tools::strtoupper(Tools::passwdGen(10));
		$cart_rule->code = $code;
		$cart_rule->active = 1;
		if (!$cart_rule->add())
			return false;

		return $cart_rule;
	}

	public function cronTask()
	{
		Context::getContext()->link = new Link(); //when this is call by cron context is not init
		$conf = Configuration::getMultiple(array(
			'PS_FOLLOW_UP_ENABLE_1',
			'PS_FOLLOW_UP_ENABLE_2',
			'PS_FOLLOW_UP_ENABLE_3',
			'PS_FOLLOW_UP_ENABLE_4',
			'PS_FOLLOW_UP_ENABLE_5',
			'PS_FOLLOW_UP_CLEAN_DB'
		));

		if ($conf['PS_FOLLOW_UP_ENABLE_1'])
			$this->cancelledCart();
		if ($conf['PS_FOLLOW_UP_ENABLE_2'])
			$this->reOrder();
		if ($conf['PS_FOLLOW_UP_ENABLE_3'])
			$this->bestCustomer();
		if ($conf['PS_FOLLOW_UP_ENABLE_4'])
			$this->badCustomer();
		if ($conf['PS_FOLLOW_UP_ENABLE_5'])
			$this->abandonedCart();

		/* Clean-up database by deleting all outdated discounts */
		if ($conf['PS_FOLLOW_UP_CLEAN_DB'] == 1)
		{
			$outdated_discounts = Db::getInstance()->executeS('SELECT id_cart_rule FROM '._DB_PREFIX_.'cart_rule WHERE date_to < NOW() AND code LIKE "FLW-%"');
			foreach ($outdated_discounts as $outdated_discount)
			{
				$cart_rule = new CartRule((int)$outdated_discount['id_cart_rule']);
				if (Validate::isLoadedObject($cart_rule))
					$cart_rule->delete();
			}
		}
	}

	public function renderStats()
	{
		$stats = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
			SELECT DATE_FORMAT(l.date_add, \'%Y-%m-%d\') date_stat, l.id_email_type, COUNT(l.id_log_email) nb,
			(SELECT COUNT(l2.id_cart_rule)
			FROM '._DB_PREFIX_.'log_email l2
			LEFT JOIN '._DB_PREFIX_.'order_cart_rule ocr ON (ocr.id_cart_rule = l2.id_cart_rule)
			LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_order = ocr.id_order)
			WHERE l2.id_email_type = l.id_email_type AND l2.date_add = l.date_add AND ocr.id_order IS NOT NULL AND o.valid = 1) nb_used
			FROM '._DB_PREFIX_.'log_email l
			WHERE l.date_add >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
			GROUP BY DATE_FORMAT(l.date_add, \'%Y-%m-%d\'), l.id_email_type');

		$stats_array = array();
		foreach ($stats as $stat)
		{
			$stats_array[$stat['date_stat']][$stat['id_email_type']]['nb'] = (int)$stat['nb'];
			$stats_array[$stat['date_stat']][$stat['id_email_type']]['nb_used'] = (int)$stat['nb_used'];
		}

		foreach ($stats_array as $date_stat => $array)
		{
			$rates = array();
			for ($i = 1; $i != 5; $i++)
				if (isset($stats_array[$date_stat][$i]['nb']) && isset($stats_array[$date_stat][$i]['nb_used']) && $stats_array[$date_stat][$i]['nb_used'] > 0)
					$rates[$i] = number_format(($stats_array[$date_stat][$i]['nb_used'] / $stats_array[$date_stat][$i]['nb']) * 100, 2, '.', '');
			for ($i = 1; $i != 5; $i++)
			{
				$stats_array[$date_stat][$i]['nb'] = isset($stats_array[$date_stat][$i]['nb']) ? (int)$stats_array[$date_stat][$i]['nb'] : 0;
				$stats_array[$date_stat][$i]['nb_used'] = isset($stats_array[$date_stat][$i]['nb_used']) ? (int)$stats_array[$date_stat][$i]['nb_used'] : 0;
				$stats_array[$date_stat][$i]['rate'] = isset($rates[$i]) ? '<b>'.$rates[$i].'</b>' : '0.00';
			}
			ksort($stats_array[$date_stat]);
		}

		$this->context->smarty->assign(array('stats_array' => $stats_array));

		return $this->display(__FILE__, 'stats.tpl');
	}

	public function renderForm()
	{
		$currency = new Currency((int)Configuration::get('PS_CURRENCY_DEFAULT'));

		$n1 = $this->cancelledCart(true);
		$n2 = $this->reOrder(true);
		$n3 = $this->bestCustomer(true);
		$n4 = $this->badCustomer(true);
		$n5 = $this->abandonedCart(true);

		$cron_info = '';
		if (Shop::getContext() === Shop::CONTEXT_SHOP)
			$cron_info = $this->l('Define the settings and paste the following URL in the crontab, or call it manually on a daily basis:').'<br />
								<b>'.$this->context->shop->getBaseURL().'modules/followup/cron.php?secure_key='.Configuration::get('PS_FOLLOWUP_SECURE_KEY').'</b></p>';

		$fields_form_1 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Informations'),
					'icon' => 'icon-cogs',
				),
				'description' => $this->l('Four kinds of e-mail alerts are available in order to stay in touch with your customers!').'<br />'.$cron_info,
			)
		);

		$fields_form_2 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Cancelled carts'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each cancelled cart (with no order), generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_1',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_1',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_1',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('The next process will send %d e-mail(s).'), $n1)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_3 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Re-order'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each validated order, generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_2',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_2',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_2',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n2)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_4 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Best customers'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each customer raising a threshold, generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_3',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_3',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Threshold'),
						'name' => 'PS_FOLLOW_UP_THRESHOLD_3',
						'suffix' => $currency->sign,
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_3',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n3)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_5 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Bad customers'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each customer who has already placed at least one order and with no orders since a given duration, generate a discount and send it to the customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_4',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_4',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Since x days'),
						'name' => 'PS_FOLLOW_UP_DAYS_THRESHOLD_4',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_4',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n4)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_6 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('Abandoned Carts'),
					'icon' => 'icon-cogs'
				),
				'description' => $this->l('For each abandoned cart, generate a discount and send it to customer.'),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Enable'),
						'name' => 'PS_FOLLOW_UP_ENABLE_5',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount amount'),
						'name' => 'PS_FOLLOW_UP_AMOUNT_5',
						'suffix' => '%',
					),
					array(
						'type' => 'text',
						'label' => $this->l('Discount validity'),
						'name' => 'PS_FOLLOW_UP_DAYS_5',
						'suffix' => $this->l('day(s)'),
					),
					array(
						'type' => 'desc',
						'name' => '',
						'text' => sprintf($this->l('Next process will send: %d e-mail(s)'), $n5)
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$fields_form_7 = array(
			'form' => array(
				'legend' => array(
					'title' => $this->l('General'),
					'icon' => 'icon-cogs'
				),
				'input' => array(
					array(
						'type' => 'switch',
						'is_bool' => true, //retro-compat
						'label' => $this->l('Delete outdated discounts during each launch to clean database'),
						'name' => 'PS_FOLLOW_UP_CLEAN_DB',
						'values' => array(
							array(
								'id' => 'active_on',
								'value' => 1,
								'label' => $this->l('Enabled')
							),
							array(
								'id' => 'active_off',
								'value' => 0,
								'label' => $this->l('Disabled')
							)
						),
					),
				),
				'submit' => array(
					'title' => $this->l('Save'),
					'class' => 'btn btn-default pull-right'
				)
			),
		);

		$helper = new HelperForm();
		$helper->show_toolbar = false;
		$helper->table = $this->table;
		$lang = new Language((int)Configuration::get('PS_LANG_DEFAULT'));
		$helper->default_form_language = $lang->id;
		$helper->allow_employee_form_lang = Configuration::get('PS_BO_ALLOW_EMPLOYEE_FORM_LANG') ? Configuration::get('PS_BO_ALLOW_EMPLOYEE_FORM_LANG') : 0;
		$helper->identifier = $this->identifier;
		$helper->override_folder = '/';
		$helper->module = $this;
		$helper->submit_action = 'submitFollowUp';
		$helper->currentIndex = $this->context->link->getAdminLink('AdminModules', false).'&configure='.$this->name.'&tab_module='.$this->tab.'&module_name='.$this->name;
		$helper->token = Tools::getAdminTokenLite('AdminModules');
		$helper->tpl_vars = array(
			'fields_value' => $this->getConfigFieldsValues(),
			'languages' => $this->context->controller->getLanguages(),
			'id_language' => $this->context->language->id
		);

		return $helper->generateForm(array(
			$fields_form_1,
			$fields_form_2,
			$fields_form_3,
			$fields_form_4,
			$fields_form_5,
			$fields_form_6,
			$fields_form_7
		));
	}

	public function getConfigFieldsValues()
	{
		return array(
			'PS_FOLLOW_UP_ENABLE_1' => Tools::getValue('PS_FOLLOW_UP_ENABLE_1', Configuration::get('PS_FOLLOW_UP_ENABLE_1')),
			'PS_FOLLOW_UP_DAYS_1' => Tools::getValue('PS_FOLLOW_UP_DAYS_1', Configuration::get('PS_FOLLOW_UP_DAYS_1')),
			'PS_FOLLOW_UP_AMOUNT_1' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_1', Configuration::get('PS_FOLLOW_UP_AMOUNT_1')),
			'PS_FOLLOW_UP_ENABLE_2' => Tools::getValue('PS_FOLLOW_UP_ENABLE_2', Configuration::get('PS_FOLLOW_UP_ENABLE_2')),
			'PS_FOLLOW_UP_DAYS_2' => Tools::getValue('PS_FOLLOW_UP_DAYS_2', Configuration::get('PS_FOLLOW_UP_DAYS_2')),
			'PS_FOLLOW_UP_AMOUNT_2' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_2', Configuration::get('PS_FOLLOW_UP_AMOUNT_2')),
			'PS_FOLLOW_UP_THRESHOLD_3' => Tools::getValue('PS_FOLLOW_UP_THRESHOLD_3', Configuration::get('PS_FOLLOW_UP_THRESHOLD_3')),
			'PS_FOLLOW_UP_DAYS_3' => Tools::getValue('PS_FOLLOW_UP_DAYS_3', Configuration::get('PS_FOLLOW_UP_DAYS_3')),
			'PS_FOLLOW_UP_ENABLE_3' => Tools::getValue('PS_FOLLOW_UP_ENABLE_3', Configuration::get('PS_FOLLOW_UP_ENABLE_3')),
			'PS_FOLLOW_UP_AMOUNT_3' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_3', Configuration::get('PS_FOLLOW_UP_AMOUNT_3')),
			'PS_FOLLOW_UP_AMOUNT_4' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_4', Configuration::get('PS_FOLLOW_UP_AMOUNT_4')),
			'PS_FOLLOW_UP_ENABLE_4' => Tools::getValue('PS_FOLLOW_UP_ENABLE_4', Configuration::get('PS_FOLLOW_UP_ENABLE_4')),
			'PS_FOLLOW_UP_AMOUNT_5' => Tools::getValue('PS_FOLLOW_UP_AMOUNT_5', Configuration::get('PS_FOLLOW_UP_AMOUNT_5')),
			'PS_FOLLOW_UP_ENABLE_5' => Tools::getValue('PS_FOLLOW_UP_ENABLE_5', Configuration::get('PS_FOLLOW_UP_ENABLE_5')),
			'PS_FOLLOW_UP_DAYS_5' => Tools::getValue('PS_FOLLOW_UP_DAYS_5', Configuration::get('PS_FOLLOW_UP_DAYS_5')),
			'PS_FOLLOW_UP_DAYS_THRESHOLD_4' => Tools::getValue('PS_FOLLOW_UP_DAYS_THRESHOLD_4', Configuration::get('PS_FOLLOW_UP_DAYS_THRESHOLD_4')),
			'PS_FOLLOW_UP_DAYS_4' => Tools::getValue('PS_FOLLOW_UP_DAYS_4', Configuration::get('PS_FOLLOW_UP_DAYS_4')),
			'PS_FOLLOW_UP_CLEAN_DB' => Tools::getValue('PS_FOLLOW_UP_CLEAN_DB', Configuration::get('PS_FOLLOW_UP_CLEAN_DB')),
		);
	}
}

And for those who don't have the module, here are all files modded by me (because you have to mod also stats.tpl inside views/templates/hook and add mail files followup_5.html and followup_5.txt

Here's direct link for download modded module: http://www.mailayout.com/uploads/followup_v1.7-Modded_by_MaiLayout.zip

 

PS: If you know how to do it, with this module you can create infinite types of automatic email alerts.

[mrswm]

Thank you for your work on this Pedro. If I use your code above am I able to send a follow up email to registered customers who abandoned their carts but NOT offer them a discount?

[Pedro Lima]

Thank you for your work on this Pedro. If I use your code above am I able to send a follow up email to registered customers who abandoned their carts but NOT offer them a discount?

 

Sure, it's totally possible. You just put a zero on voucher field and you edit the emails to remove the voucher information. That's it.

[mrswm]

Fantastic! Many thanks, I really appreciate your work on this

[Pedro Lima]

Fantastic! Many thanks, I really appreciate your work on this

 

Thanks.

 

I like to help others because I understand the value of being helped when we need.

[wer_ru]

May be you shuld try to put your code in upstream through the http://forge.prestashop.com/

[moy2010]

Hi, Pedro. Thanks for this wonderful mod . One question, can you set a cron job to send the e-mail reminders?

 

Thanks in advance.

[Pedro Lima]

Hi, Pedro. Thanks for this wonderful mod . One question, can you set a cron job to send the e-mail reminders?

 

Thanks in advance.

 

Sure you can. Just put that link that appears inside module (on top) and that's it.

 

Oh, and thank you too!

Edited March 20, 2015 by Pedro Lima (see edit history)

[3dron]

Great work Pedro.  Is your work now implemented into the base Followup module I see in my backoffice modules list?

 

Also, where can I change the email title?  I see the html/txt body files, but these don't seem to include the actual title that appears in my inbox on test.

 

 

Thanks

 

 

3dron

 

 

[Pedro Lima]

Great work Pedro.  Is your work now implemented into the base Followup module I see in my backoffice modules list?

 

Also, where can I change the email title?  I see the html/txt body files, but these don't seem to include the actual title that appears in my inbox on test.

 

Thanks 3dron

 

This isn't implemented in the original module, it's an upgrade I have made myself since I was in need of this and didn't wanted to pay for any module with extra functionalities (and also because I was curious about developing this myself).

 

The email title you can change it in translations menu in BO of your site or you can do it directly inside followup.php file.

 

As an example, for the email sent to people that re-ordered within X days, you can do two translations. First is "Thank you for your order." which is the sentence that will be associated to the voucher and then another one a little bellow that is "Thanks for your order" which is the email subject.

 

Hope this helped.

[3dron]

The email title you can change it in translations menu in BO of your site or you can do it directly inside followup.php file.

 

 

 

Thanks for quick answer but I cannot find where this title is being generated:

 

"Discount offer for items in your cart"

[Pedro Lima]

Thanks for quick answer but I cannot find where this title is being generated:

 

"Discount offer for items in your cart"

 

Hum, that's weird... are you sure you are using this module and not the original one from Prestashop?

 

Or have you checked for that translation in your translated emails menu? I can't find anything with that text inside my file so it can only mean one of those two things.

[3dron]

I am using Prestashop version.  But reason I thought it might have been your modified version is that it already has the feature of emailing abandoned carts.   Your first posting at the top said you wanted to add that feature, but it is already in the one I just installed.

 

Since I have that one up and running and have modified the html to make no mention of discounts, I would just like to be pointed in the right direction where that email title is coming from.  I have done a search on the entire module folder and in my database for "Discount offer for items in your cart" and cant find.

 

 

Also, if you can tell me how yours differs from the Prestashop one I would be grateful.

[Pedro Lima]

Well, that's actually something new for me because what I knew and saw was that Prestashop original version had some bugs regarding that option and actually it was sending emails to Cancelled orders not for Abandoned Carts so I did this.

 

Maybe now they have updated their module, I don't know... but this version is what I did when I compared the latest version of Follow-up module I had at that time (it was "Customer follow-up v1.6.5").

 

Try to check within translations folder of your theme or from your Prestashop installation, maybe it's there.

[alwaysperplexed]

Hello Pedro

 

I have installed your mod but I do not see it in the list of modules, even though it says "successfully installed".  I only see the Prestashop Customer Follow up module.  When I look in my ftp program your mod seems to have installed in /modules but not in its own folder so I am unable to configure it in BO or uninstall it?

[Pedro Lima]

Hello Pedro

 

I have installed your mod but I do not see it in the list of modules, even though it says "successfully installed".  I only see the Prestashop Customer Follow up module.  When I look in my ftp program your mod seems to have installed in /modules but not in its own folder so I am unable to configure it in BO or uninstall it?

 

Hi,

 

Have you tried to manually upload the folder to the module folder? Maybe that will work.

 

Anyway, it's weird that you couldn't install it. Did you tried to set debug mode on and installing it again to see if something comes up that might clarify why it's not showing?

 

Thanks.

[alwaysperplexed]

Hello Pedro

 

I looked in the module folder via ftp and the files are in the /modules folder but all separate, not in their own folder.  I will try uploading the unzipped mod as a folder as you recommend and see if that solves the problem.  Thanks for your reply

[pritishnandi]

Hello Pedro,

 

Thanks for the great and awesome work and offering it for free. I will use this great piece of code in my future work.

[Pedro Lima]

Hello Pedro,

 

Thanks for the great and awesome work and offering it for free. I will use this great piece of code in my future work.

 

Thanks. I'm glad this could be helpful.

[rajansinha02]

Hi Perdo

 

It is indeed a great work from you and will definitely help many of the community members but just one question is it possible to add a second email reminder functionality.

 

 

Thanks

[Pedro Lima]

Hi Perdo

 

It is indeed a great work from you and will definitely help many of the community members but just one question is it possible to add a second email reminder functionality.

 

 

Thanks

 

Hi,

 

Thanks for your feedback.

 

Yes, it is possible to add that. If you want I can do that requirement for you and update the module that I am selling (link on my signature).

 

Basically it's this module but optimized with more options. You can set remarketing options for specific products, so a person will receive a warning, let's say, 25 days after the purchase, reminding to buy again. This is very useful for products that expires after X days and customer is warned to buy again.

 

If you want me to do it, let me know in more detail the functionality and I can develope it for Remarketing Emails and SMS module.

 

Thanks again.

 

Best regards,

Pedro Lima

[rajansinha02]

Hi Pedro

 

It would be indeed a great help if you can provide, so that  " Second email reminder for abandoned cart is sent to the buyer " as it will provide more waitage and persuade the buyer to buy.

 

As you said this module can do lot many things.

 

You can also add a functionality like personalized email recommendation depending upon customer purchase behaviour.

 

These functionality with already present ones will make your selling module more attractive hence more sell for you.( as all in one functionality module is not available)

 

 

Thanks

[kenkomuri]

Hi,

 

I cant install the module. Also I dont know how to send the emails using the follow up module.

 

Sorry but I am newbie.

 

Fer

[letrof]

thank you for your work.

I have a question about the 5 emails template. which of the e-mail template have I translate for the voucher on abandoned cart as template 1 and 5 looks similar.

Also do I have to create a new directory inside /mails as I use polish language in my shop and the module provides en and pt?

 

Maciek

Edited July 14, 2015 by letrof (see edit history)

[mkbond777]

Hello Pedro

 

I looked in the module folder via ftp and the files are in the /modules folder but all separate, not in their own folder.  I will try uploading the unzipped mod as a folder as you recommend and see if that solves the problem.  Thanks for your reply

Hi,

 

Did uploading all the files via a single folder worked for you? For me it is still not working.

 

My Steps:

1. Copied all the files in a folder say "followup_external"

2. Zipped it and tried adding it through back-end but it didn't work.

3. Then I manually copied "followup_external" folder into modules folder but then it is not showing in back-end and hence not able to change settings.

 

Thanks

Manish

[amar.prestashop]

Hi Pedro,

 

 

Thank you for sharing your custom module !

 

I installaed your module, i configure it but the link the module give to me to launch the task do not works.

I double checked, the folders are correct, cron.php exist, etc. but when i start the task (http://www.lapetitecroisette.com/modules/followup/cron.php?secure_key=MRECNAHCDK9SN53N) my site show page 404.

 

Do you have any idea ? Can you help me ?

 

Regards,

[nickosn]

Hi and thanks for your work. 

 

I installed the module and send a mail for the abandont cart. But after that when I get into the module I get:

 

        SELECT c.id_cart, c.id_lang, c.id_shop, c.secure_key, cu.id_customer, cu.firstname, cu.lastname, cu.email
        FROM ps_cart c
        LEFT JOIN ps_customer cu ON (cu.id_customer = c.id_customer)
            WHERE c.id_customer > 0
            AND c.id_cart NOT IN (SELECT id_cart FROM ps_orders)
            AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= c.date_upd AND c.secure_key NOT IN (,) GROUP BY cu.email
at line 646 in file classes/db/Db.php

 

641.             WebserviceRequest::getInstance()->setError(500, '

 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
642.         }
643.         elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
644.         {
645.             if ($sql)
646.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
647.             throw new PrestaShopDatabaseException($this->getMsgError());
648.         }
649.     }
650.
651.     /**

 

 

Any help ?

 

Kind Regards

[Pedro Lima]

Hi everyone,

 

First of all, let me say sorry for not answering your questions sooner, I've been with a lot of work lately!

 

It would be indeed a great help if you can provide, so that  " Second email reminder for abandoned cart is sent to the buyer " as it will provide more waitage and persuade the buyer to buy.

You can also add a functionality like personalized email recommendation depending upon customer purchase behaviour.

 

These functionality with already present ones will make your selling module more attractive hence more sell for you.( as all in one functionality module is not available)

 

Hi, thank you very much for your suggestions, I will surelly take them into consideration on my next update of this module and I will try to put a second email reminder for someone that has not yet bought even after first email was sent.

 

Recarding your second suggestion, that's very interresting and I would like to explore it better. What do you mean by "customer purchase behavior"? Do you mean, suggesting customers some other products based on their purchases? If that's the case, that is also happening on the most recent update I've made on the selling module (which is not yet release publicly because it's yet pending approval from Prestashop, and it takes a while unfortunately...).

 

I cant install the module. Also I dont know how to send the emails using the follow up module.

 

Hi, what Prestashop version are you using? Have you tried to only extract the zip file content to your main website folder "modules"? If you have installed the original followup module, I suggest you uninstall it because it will probably generate some conflict.

 

I have a question about the 5 emails template. which of the e-mail template have I translate for the voucher on abandoned cart as template 1 and 5 looks similar.

Also do I have to create a new directory inside /mails as I use polish language in my shop and the module provides en and pt?

 

Hi, they are with the same order that you can see inside the module, and if you look better, you will notice that all are similar but different, because all have a different purpose.

 

Indeed, you have to create the new folder for your language and translate it, also you will need to do that so you can then translate the email subject, otherwise it will go in English by default.

 

My Steps:

1. Copied all the files in a folder say "followup_external"

2. Zipped it and tried adding it through back-end but it didn't work.

3. Then I manually copied "followup_external" folder into modules folder but then it is not showing in back-end and hence not able to change settings.

 

Hi Manish, Do you have the original followup module installed? If so, please remove it. After that, you simply extract directly the folder that is inside the zip file, for your "modules" folder and that's it, you have it in your back-end to activate and use.

 

Thank you for sharing your custom module !

 

I installaed your module, i configure it but the link the module give to me to launch the task do not works.

I double checked, the folders are correct, cron.php exist, etc. but when i start the task (http://www.lapetitecroisette.com/modules/followup/cron.php?secure_key=MRECNAHCDK9SN53N) my site show page 404.

 

Do you have any idea ? Can you help me ?

 

Hi, probably this is only a matter of access rights. Please check your file access right through your server or FTP and make sure this file is accessible, because otherwise you will not be able to execute the file if the access is restricted.

 

 

Hi and thanks for your work. 

 

I installed the module and send a mail for the abandont cart. But after that when I get into the module I get:

 

        SELECT c.id_cart, c.id_lang, c.id_shop, c.secure_key, cu.id_customer, cu.firstname, cu.lastname, cu.email
        FROM ps_cart c
        LEFT JOIN ps_customer cu ON (cu.id_customer = c.id_customer)
            WHERE c.id_customer > 0
            AND c.id_cart NOT IN (SELECT id_cart FROM ps_orders)
            AND DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= c.date_upd AND c.secure_key NOT IN (,) GROUP BY cu.email
at line 646 in file classes/db/Db.php

 

641.             WebserviceRequest::getInstance()->setError(500, '

 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
642.         }
643.         elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
644.         {
645.             if ($sql)
646.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
647.             throw new PrestaShopDatabaseException($this->getMsgError());
648.         }
649.     }
650.
651.     /**

 

 

Hi, I honestly don't know how to help, this is a new one for me. Never saw this error. What Prestashop version are you using?

[nickosn]

1.6.0.14

[Pedro Lima]

1.6.0.14

 

On the specific version I have not tested it, but it was tested on 1.6.0.13 and it was all perfect. I don't believe there were changes from *13 to *14 that could have this impact.

 

That's some problem with the MyQSL table or so. Have you tried to uninstall and install it again?

[nickosn]

yes. twice

[Pedro Lima]

yes. twice

 

I honestly have no clue on that!

 

It never happened neither on the free nor selling version (at least, not that I know about it...).

 

I'm sorry I cannot be of any help on this error.

[steve02cal]

Hi Pedro ( and everyone else )

 

Thank you for the module source.  It is very helpful.  I am currently using PrestaShop v1.5.6.2 for the last 2 years.  I also had the original Customer Followup module installed that came with PrestaShop.  As you recommended, I uninstalled that version before installing your version.  The installation worked perfect with no errors.  I configured the module for Abandoned Cart emails.  I ran a test on my store generating an "Abandoned Cart".  I setup the CRON job as you suggested in the Back Office.

 

Now I'm having problems with Never Receiving any emails for Abandoned Carts.  Also the CRON Job is failing with the error:  "Could not open input file: /home/*****/public_html/pres156/modules/followup/cron.php?secure_key=***** " 

 

I am using "*****" for security reasons to blank out website personal information on this forum. 

 

I do not know where to look to find out what input file is missing.  I have no idea where to look for file.  I ran your SQL Query you provided, and it shows I have 9 Abandoned carts listed in my database.  So I know there is data available to be processed.

 

Any assistance someone can provide would be greatly appreciated.  I would like to be able to use this module so that in fact I can try to cut down on the Abandoned Carts that occur with my store.  Do the emails only get sent when the CRON Job runs successfully?

 

Thank you again for the assistance.  It is greatly appreciated.

[Pedro Lima]

Hi Pedro ( and everyone else )

 

Thank you for the module source.  It is very helpful.  I am currently using PrestaShop v1.5.6.2 for the last 2 years.  I also had the original Customer Followup module installed that came with PrestaShop.  As you recommended, I uninstalled that version before installing your version.  The installation worked perfect with no errors.  I configured the module for Abandoned Cart emails.  I ran a test on my store generating an "Abandoned Cart".  I setup the CRON job as you suggested in the Back Office.

 

Hi Steve, thank you for your appreciation, I'm glad this can be he

 

Now I'm having problems with Never Receiving any emails for Abandoned Carts.  Also the CRON Job is failing with the error:  "Could not open input file: /home/*****/public_html/pres156/modules/followup/cron.php?secure_key=***** "

 

This can be due to folder/file permissions. For folders, it must be (in most cases) 755 and 655 for files. Can you please check that?

 

I do not know where to look to find out what input file is missing.  I have no idea where to look for file.  I ran your SQL Query you provided, and it shows I have 9 Abandoned carts listed in my database.  So I know there is data available to be processed.

 

 

The file that is saying it's missing, it's on the root directory of the module (inside followup module folder) and it's the "cron.php" file, and I believe it's there so I advice you talk with your server administrator to help you with that.

 

Have you even tried to execute your "cron job" link (the one inside module, in admin panel) to test if the emails are sent?

 

Any assistance someone can provide would be greatly appreciated.  I would like to be able to use this module so that in fact I can try to cut down on the Abandoned Carts that occur with my store.  Do the emails only get sent when the CRON Job runs successfully?

 

Yes, the cron job must run successfully, otherwise that means that the "cron.php" file was not executed and the emails were not sent.

 

Hope this helps you, somehow.

 

Good luck with that!

Edited January 17, 2016 by Pedro Lima (see edit history)

[steve02cal]

Hello Pedro -

 

Thank you for the quick response.  I went back thru and made your recommended changes.  I changed the permissions of the files to 655 as you recommended.  The folder permissions are set to 755 also as you suggested.  I also validated that the cron.php job is located in ..../modules/followup/ folder as you asked.  It has permissions set to 655 also as you suggested.  I ran the cron job again and am still getting the "missing file" message as I documented above.  For some reason it just doesn't want to execute.

 

I have full control over my server as it is not a "shared server".  It is a VPS server, so that I have root control wherever necessary.  I have many other CRON JOBS that run with no issues, so I know that at least that feature functions on my server.

 

In addition to this problem, is there a way I can limit the number of emails that will be sent?  Where would I change this value?

 

Thank you again for your assistance.  I really appreciate the help so I can get this module to work......

[Pedro Lima]

Hello Pedro -

 

Thank you for the quick response.  I went back thru and made your recommended changes.  I changed the permissions of the files to 655 as you recommended.  The folder permissions are set to 755 also as you suggested.  I also validated that the cron.php job is located in ..../modules/followup/ folder as you asked.  It has permissions set to 655 also as you suggested.  I ran the cron job again and am still getting the "missing file" message as I documented above.  For some reason it just doesn't want to execute.

 

I have full control over my server as it is not a "shared server".  It is a VPS server, so that I have root control wherever necessary.  I have many other CRON JOBS that run with no issues, so I know that at least that feature functions on my server.

 

In addition to this problem, is there a way I can limit the number of emails that will be sent?  Where would I change this value?

 

Thank you again for your assistance.  I really appreciate the help so I can get this module to work......

 

And have you tried to execute the file directly?

 

Limiting the number of emails in the module is not possible.

Edited January 17, 2016 by Pedro Lima (see edit history)

[steve02cal]

Hello Pedro -

 

No I have not tried to execute the cron.php job directly.  I am not a Linux expert.  Not certain how I would execute from a command prompt in Linux.  I do know I would change my credentials to become root.  That I can do with no issues.  From there I'm not certain how to execute the cron.php from the command prompt.  Can you explain what I would need to do?

 

Again, I really appreciate all the assistance.  Sorry if I'm bugging you with silly questions.

[steve02cal]

Hello Pedro -

 

I figured out how to execute the cron.php from the command prompt as root, and it still fails with the same error:  Could not open Input File.  I have validated all the permissions are correct.  I ran the script as  root  so I know I have the correct authority.  I am at a lost as to where to look.  The Cron Error log does not show any additional information.

[steve02cal]

Hi Pedro -

 

Are there any other files that cron.php is looking for during execution?  I have looked at the script and I don't really see anything it would be trying to find.  This is really crazy ......

[steve02cal]

Hi Pedro ( and everyone else ) -

 

I did some more testing and now have a different issue.  in the Customer Follow module, when you select to configure, it shows the various options.  It also shows at the very top, highlighted, a link you can use in your BROWSER to send the emails.  This is the link I took and converted so that I could run it as a cron job. 

 

Today I ran the link from my browser, and it sent out the email successfully.  So this confirms to me that the various php scripts are working correctly with no issues.  Now I need to understand why converting the LINK to a cron job script is failing, giving me the "missing input file" error.

 

Any suggestions?  I am at a lost this time.  One works, the other does not work.

[Pedro Lima]

Today I ran the link from my browser, and it sent out the email successfully.  So this confirms to me that the various php scripts are working correctly with no issues.  Now I need to understand why converting the LINK to a cron job script is failing, giving me the "missing input file" error.

 

Hi Steve, yes that's what I have asked you to do in the first place.

 

By doing that, you could check if the problem was from your cron job link or not. Here's one example of a cron job I have in one of my stores that use this module, although I use the paid version of the module, but it's the same procedure (btw, I use cpanel):

 

lynx -dump http://www.*****.pt/modules/remarketing_emails_sms/cron.php?secure_key=*****

Edited January 18, 2016 by Pedro Lima (see edit history)

[steve02cal]

Hi Pedro

 

Thank you for the reply.  My mistake I did not originally run the script thru the browser.  Also I use cpanel as well for setting up my cron jobs.

 

I used your example above to create my cron job and it also failed.  This time it produces the following:  Error 406 - Not Acceptable 

 

I will have to search someplace to find out more about this error.  At least it's not the "file missing" error.

[steve02cal]

Hi Pedro -

 

OK...  I did additional testing and I think I have a new handle on this issue.  As long as I use the LINK that is provided in the Customer Followup module, the emails get sent successfully.  This is a pain, as I don't want to have to keep using the LINK to make everything work.

 

Now as to converting the LINK to a cron job ( I also use cPanel ), I talked to tech support and they suggested I use curl to make it work.  Basically they suggested using the following in front of the LINK address: /usr/bin/curl --user-agent cPanel-Cron     I went ahead and used their suggestion and scheduled the cron job to run.  I setup an abandoned cart and the cron job did execute and send me an email of the run output as shown here:

  

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

 

  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0

  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0

 

Unfortunately NO EMAILS were produced, but instead there was an error.log that was produced in the followup folder that showed the following message:   

PHP Warning:  Invalid argument supplied for foreach() in /home/****/public_html/pres156/modules/followup/followup.php on line 399

So for now I am at a standstill as to how to make this process function correctly within my standard CentOS / Apache / PHP environment.  I guess I will have to resort to just using the LINK process in my browser because I cannot get anything else to function as a cron job.  If anyone has any suggestions as to how to get this to run as a cron job please let me know.  These must be some way to make it work as a cron job.

[Pedro Lima]

Hi Steve,

 

I'm sorry to know that you are not able to put your cron job running. Have you tried to change your PHP version?

 

Maybe that's a possible solution. Please try that and then execute cron job to see if it works.

 

Good luck!

[steve02cal]

Hello Pedro -

 

Yes I did upgrade my PHP version and it still does not work.  I'm currently running PHP v5.6.17

[Pedro Lima]

But have you tried to use an older version?

 

On the server where I ran that cron job I've shown you, I use PHP v5.4.42

[steve02cal]

Hi Pedro -

 

Yes I did try on an older version.  I had tried on version PHP v5.4.45 and it still did not work.  It is very strange.  I have other PHP applications that are running with no issues on PHP v5.6.17 that also ran on PHP v5.4.45

[steve02cal]

Hello Pedro -

 

OK....  I did additional testing just now.  I completely removed the Customer Followup module using the "uninstall" button in PrestaShop back office.  Then I went in to PrestaShop to the modules folder on my system and removed the "followup" folder.  Next I re-uploaded your "followup" folder to the modules folder (as you described in the install process earlier on this thread).  Next I went back into the PrestaShop back office, with the modules list, and clicked the "install" button on the Customer Followup module.  This was successful and now shows the new version of v1.6.4  ( I am hoping that is the right version using your modules ?  If not could that be the problem ?  What should be the correct version number? )

 

Next I created an "abandoned cart" in my system.  I ran your "abandoned cart" sql script to validate it is in the database.  Now that everything is setup, I ran the Cron Job this time, instead of the LINK and it generated the emails correctly.  I went back and created a new "abandoned cart" and validated, so that I can run one more test.  This time I ran the LINK and it failed and generated the same error as last night, about the invalid argument on line 399.

 

So what I have discovered in all this process is that I can run either the LINK or the Cron Job one time only.  After I run either one, I cannot generate any other E-mails because the process generates the error about invalid argument on line 399.

 

This is totally crazy and very frustrating that now I have discovered that Both processes cause the same error and that the process can only run once successfully.

 

Amy additional suggestions?  I don't know where to go from here.  I'm totally lost at this point.

[centoasa]

it's possible to use this module only for "customer" group, and not for all groups?