My method for enabling group leaders to set date ranges for allowing members to order

[Beluga]

This module allows a customer that belongs to 1) the leader group and 2) a food club group to control the allowed periods of ordering of their food club. The leader sees date fields (with jQueryUI datepicker activated) in their "My account" page and is allowed to set one rule.

 

grouporderdate.php:

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

class GroupOrderDate extends Module
{
	public function __construct()
	{
		$this->name = 'grouporderdate';
		$this->tab = 'others';
		$this->version = '1.0';
		$this->author = 'Beluga';
		$this->need_instance = 0;
		
		parent::__construct();
		
		$this->displayName = $this->l('Date range for orders of group');
		$this->description = $this->l('Allow group leaders to set date ranges for the orders of groups');
	}
	
	public function install()
	{
		return (parent::install() AND $this->registerHook('displayCustomerAccount'));
	}
	
	public function hookdisplayCustomerAccount()
	{
		$getthismoment = "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')";
		$thismoment = Db::getInstance()->getValue($getthismoment);
		$dateFrom = Tools::getValue('dateFrom') . " 00:00:00";
		$dateTo = Tools::getValue('dateTo') . " 23:59:00";
		$this->context->controller->addJqueryUI('ui.datepicker');
		$isleader = NULL;
		
		$thiscustomer = $this->context->customer->id;
		$customergroupselect = "SELECT id_default_group FROM "._DB_PREFIX_."customer WHERE id_customer = '$thiscustomer'";
		$thiscustomergroup = Db::getInstance()->getValue($customergroupselect);	
		$getgroupname = "SELECT name FROM "._DB_PREFIX_."group_lang WHERE id_group = '$thiscustomergroup'";
		$groupname = Db::getInstance()->getValue($getgroupname);
		
		/* We identify the customer group ID with the name 'leader' */
		$leadernameselect = "SELECT id_group FROM "._DB_PREFIX_."group_lang WHERE name = 'leader'";
		$leadernamecheck = Db::getInstance()->getValue($leadernameselect);		
		$leaderselect = "SELECT id_customer FROM "._DB_PREFIX_."customer_group WHERE id_group = '$leadernamecheck'";
		$leadercheck = Db::getInstance()->ExecuteS($leaderselect);

		/* For compiling a list of orders in the food club */
		$groupordererselect = "SELECT id_customer FROM "._DB_PREFIX_."customer_group WHERE id_group = '$thiscustomergroup'";
		$grouporderers = Db::getInstance()->ExecuteS($groupordererselect);
		
		/* PrestaShop's ExecuteS creates arrays within arrays so we need to simplify the customer id array 	*/
		$customerids = array();		
		foreach($grouporderers as $grouporderer)
		{
			$customerids[] = $grouporderer['id_customer'];
		}
		
		/* We implode the array so we can use it in the SQL query */		
		$incustomerid = implode("', '", $customerids);
		$orderselect = "SELECT * FROM "._DB_PREFIX_."orders WHERE id_customer IN ('$incustomerid')";
		$orders = Db::getInstance()->ExecuteS($orderselect);

		/* We trim the order array to contain only the needed keys */
		$orderarray = array();		
		foreach($orders as $order => $value)
		{
			$kustomeeri = $value['id_customer'];
			$customernameselect = "SELECT CONCAT_WS(' ', firstname, lastname) AS kokonimi FROM "._DB_PREFIX_."customer WHERE id_customer = '$kustomeeri'";
			$customername = Db::getInstance()->getValue($customernameselect);
			$orderarray[] = array("Nimi" => $customername, "Hinta" => $value['total_paid_tax_incl'], "Pvm" => $value['date_add']);
		}
		
		foreach($leadercheck as $leader)
		{
			$leaders[] = $leader['id_customer'];
		}
		if (isset($leaders)) {
			if (in_array($thiscustomer, $leaders))
			{
			   $isleader = true;
			}
			
			$getruleid = "SELECT id_cart_rule FROM "._DB_PREFIX_."cart_rule_lang WHERE name = '$groupname'";
			$ruleid = Db::getInstance()->getValue($getruleid);
			$lastruleid = NULL;
			$dateinvalid = false;
			function validateDate($date, $format = 'Y-m-d H:i:s')
			{
				$d = DateTime::createFromFormat($format, $date);
				return $d && $d->format($format) == $date;
			}
			$postcount = count($_POST);
			if ($_SERVER['REQUEST_METHOD'] == 'POST') { // Check, if form is being submitted
				if (!validateDate($dateFrom) || !validateDate($dateTo) || (strtotime($dateTo) < strtotime($dateFrom))
				|| empty($dateFrom) || empty($dateTo)) // Validate the crap out of the date fields.
				{
				$dateinvalid = true;
				}
				elseif ( $ruleid == NULL){ // Check, if a rule with the group name already exists.
							Db::getInstance()->insert('cart_rule', array(
							'date_from' => $dateFrom,
							'date_to' => $dateTo,
							'group_restriction' => 1,
							'active' => 1,
							'date_add' => $thismoment,
							'date_upd' => $thismoment				
							));
							$lastruleid = Db::getInstance()->Insert_ID();
							Db::getInstance()->insert('cart_rule_group', array(
							'id_cart_rule' => $lastruleid,
							'id_group' => $thiscustomergroup				
							));
							Db::getInstance()->insert('cart_rule_lang', array(
							'id_cart_rule' => $lastruleid,
							'id_lang' => 1,
							'name' => $groupname
							));
							Db::getInstance()->insert('cart_rule_lang', array(
							'id_cart_rule' => $lastruleid,
							'id_lang' => 2,
							'name' => $groupname
							));
						}
						else {
							Db::getInstance()->update('cart_rule', array(
							'date_from' => $dateFrom,
							'date_to' => $dateTo,
							'date_add' => $thismoment,
							'date_upd' => $thismoment
							), 'id_cart_rule = '.(int)$ruleid);
						}
			}
			$this->context->smarty->assign(
			  array(				  
				  'date_From' => $dateFrom,
				  'date_To' => $dateTo,
				  'isleader' => $isleader,
				  'thismoment' => $thismoment,
				  'dateinvalid' => $dateinvalid,
				  'orders' => $orders,
				  'orderarray' => $orderarray
			  )
			);
			return $this->display(__FILE__, 'grouporderdate.tpl');
		}
			
	}
}

grouporderdate.tpl:

{if isset($isleader)}
{literal}
<script type="text/javascript">
$(function() {
$('.datepick').each(function(){
    $(this).datepicker({dateFormat:"yy-mm-dd"});
});
});
</script>
{/literal}

<p>{l s='The starting time is from the first minute of the starting day and the ending time is the last minute of the ending day.' mod='grouporderdate'}</p>
<form method="post" action="{$smarty.server.REQUEST_URI}" enctype="multipart/form-data">
				<fieldset style="width: 100%;">
					<legend>{l s='Set the date range for orders' mod='grouporderdate'}</legend>
					<label>{l s='Date range' mod='grouporderdate'}</label>
					<div class="margin-form">
						<input type="text" size="20" class="datepick" name="dateFrom" id="dateFrom" value="{if isset($smarty.post.dateFrom)}{$smarty.post.dateFrom}{else}{$thismoment}{/if}" />
						<input type="text" size="20" class="datepick" name="dateTo" id="dateTo" value="{if isset($smarty.post.dateTo)}{$smarty.post.dateTo}{/if}" />
						<p class="clear">{l s='Format: YYYY-MM-DD' mod='grouporderdate'} {if $dateinvalid == true}{l s=' Check the dates!!' mod='grouporderdate'}{/if}</p>
						<p class="clear">{l s='You can update an existing date range and just click Submit. Expired rules are deleted every night.' mod='grouporderdate'}</p>
					</div>
					<div class="margin-form clear">
						<input type="submit" name="submitUpdate" value="{l s='Submit' mod='grouporderdate'}" class="button" />
					</div>
				</fieldset>
</form>
<p>Group orders:<p>
{foreach $orderarray as $order}

<p>{$order['Nimi']} / Total (incl. tax): {$order['Hinta']} € / Pvm: {$order['Pvm']}</p>
{/foreach}
{/if}
{/if}

Here is how I block the affected group members from ordering, by redirecting them to a CMS page that tells them ordering is disabled for their group (in this case it uses the CMS ID 4 for demo purposes).

 

override/controllers/front/ParentOrderController.php:

class ParentOrderController extends ParentOrderControllerCore
{

	public function init()
	{
		
		$thiscustomer = $this->context->customer->id;
		$customergroupselect = "SELECT id_default_group FROM "._DB_PREFIX_."customer WHERE id_customer = '$thiscustomer'";
		$thiscustomergroup = Db::getInstance()->getValue($customergroupselect);	
		$getgroupname = "SELECT name FROM "._DB_PREFIX_."group_lang WHERE id_group = '$thiscustomergroup'";
		$groupname = Db::getInstance()->getValue($getgroupname);
		$getrulename = "SELECT * FROM "._DB_PREFIX_."cart_rule_lang WHERE name = '$groupname'";
		$rulename = Db::getInstance()->getValue($getrulename);
		$getruleid = "SELECT id_cart_rule FROM "._DB_PREFIX_."cart_rule_lang WHERE name = '$groupname'";
		$ruleid = Db::getInstance()->getValue($getruleid);
		$getdateFrom = "SELECT date_from FROM "._DB_PREFIX_."cart_rule WHERE id_cart_rule = '$ruleid'";
		$dateFrom = Db::getInstance()->getValue($getdateFrom);
		$getdateTo = "SELECT date_to FROM "._DB_PREFIX_."cart_rule WHERE id_cart_rule = '$ruleid'";
		$dateTo = Db::getInstance()->getValue($getdateTo);
		$cms = new CMS(4); // 4 is the id of the CMS page, which contains the information about ordering being disabled
		$linktocms = $this->context->link->getCmsLink($cms);
		
		if (substr( $groupname, 0, 5 ) === "piiri" && ($rulename == NULL || time() < strtotime($dateFrom) || time() > strtotime($dateTo)))
			Tools::redirect($linktocms);

			parent::init();
	}
}

I have a group/cart rule naming scheme where groups/rules that begin with "piiri" ("club" as in "food club") are affected by this date range rule.

 

I also have this script that I can use with CronTab to purge group rules that have expired (the conditions protect real rules with discounts):

$host = "localhost";
$user = "user";
$pass = "pass";
$dbnm = "dbname";

// Connect to PDO MySQL
$dsn =  "mysql:dbhost=$host;dbname=$dbnm";
$db  = new PDO($dsn, $user, $pass);

$stmt = $db->prepare("SELECT id_cart_rule FROM ps_cart_rule WHERE date_to < NOW() AND free_shipping = 0 AND reduction_percent = 0 AND reduction_amount = 0");
$stmt->execute(); // execute the prepared query

// FETCH one COLUMN from ALL rows in result IN one operation into array
// then implode array into one LINE using Comma+space between
$line = implode( ',', $stmt->fetchAll (PDO::FETCH_COLUMN) );

$ruledel = $db->prepare("DELETE FROM ps_cart_rule WHERE id_cart_rule IN ($line);DELETE FROM ps_cart_rule_group WHERE id_cart_rule IN ($line);DELETE FROM ps_cart_rule_lang WHERE id_cart_rule IN ($line)");
$ruledel->execute();

I welcome your comments and suggestions for improvements.

 

Edit: Changed all the code except the crontab. It was broken.

Edited September 26, 2014 by Beluga (see edit history)