Acymailing Filter on Orders not working

carniel
Topic Author
Offline

10 years 9 months ago #223758 by carniel

Acymailing Filter on Orders not working was created by carniel

-- HikaShop version -- : 2.6.0
-- Joomla version -- : 3.4.5
-- PHP version -- : 5.3.10
-- Browser(s) name and version -- : Safari 6.2.8

I try to integrate Acymailing with Hikashop and move a user to a list when he buys a product.
Created a filter in Acymailing with Hikashop customers - bought - product -> move to list
There is a completed order for that product in Hikashop.
When I process "View users" there are 0 chosen
I turned on Joomla Debug and it shows this query:

SELECT DISTINCT b.user_email
FROM jom30_hikashop_order_product AS a
LEFT JOIN jom30_hikashop_order AS c
ON a.order_id = c.order_id
LEFT JOIN jom30_hikashop_user AS b
on c.order_user_id = b.user_id
WHERE c.order_status IN ('')
AND a.product_id = 10

I think the problem is the order_status .. checked in the database and the order_status is "confirmed"
The error is maybe the empty list but I cannot figure out why its empty. Maybe its a language problem - its german.

Thx Eva

There are no kangaroos in Austria! ;-)

Please Log in or Create an account to join the conversation.

nicolas
Away

10 years 9 months ago #223775 by nicolas

Replied by nicolas on topic Acymailing Filter on Orders not working

Hi,

Change the code:

Code:

	function onAcyProcessFilter_hikaorder(&$query,$filter,$num){
		if(!$this->loadAcymailing()) return;

		$config =& hikashop_config();
		$statuses = explode(',',$config->get('invoice_order_statuses','confirmed,shipped'));
		$condition = array();
		foreach($statuses as $status){
			$condition[] = $query->db->Quote($status);
		}
		$myquery = "SELECT DISTINCT b.user_email
					FROM #__hikashop_order_product AS a
					LEFT JOIN #__hikashop_order AS c ON a.order_id = c.order_id
					LEFT JOIN #__hikashop_user AS b on c.order_user_id = b.user_id
					WHERE c.order_status IN (".implode(',',$condition).")";
		if(!empty($filter['product']) AND is_numeric($filter['product'])) $myquery .= " AND a.product_id = ".(int) $filter['product'];
		elseif(!empty($filter['cat']) AND is_numeric($filter['cat'])) $myquery .= " AND a.product_id IN (SELECT product_id FROM #__hikashop_product_category WHERE category_id = ".$filter['cat'].")";
		$datesVar = array('creationdatesup','creationdateinf');
		foreach($datesVar as $oneDate){
			if(empty($filter[$oneDate])) continue;
			$filter[$oneDate] = acymailing_replaceDate($filter[$oneDate]);
			if(!is_numeric($filter[$oneDate])) $filter[$oneDate] = strtotime($filter[$oneDate]);
		}

		if(empty($filter['datefield'])) $filter['datefield'] = 'order_created';

		if(!empty($filter['creationdateinf'])) $myquery .= ' AND c.`'.$filter['datefield'].'` > '.$filter['creationdateinf'];
		if(!empty($filter['creationdatesup'])) $myquery .= ' AND c.`'.$filter['datefield'].'` < '.$filter['creationdatesup'];
		$query->db->setQuery($myquery);
		$allEmails = acymailing_loadResultArray($query->db);
		if(empty($allEmails)) $allEmails[] = 'none';
		if(empty($filter['type'])){
			$query->where[] = "sub.email NOT IN ('".implode("','",$allEmails)."')";
		}else{
			$query->where[] = "sub.email IN ('".implode("','",$allEmails)."')";
		}
	}

to:

Code:

	function onAcyProcessFilter_hikaorder(&$query,$filter,$num){
		if(!$this->loadAcymailing()) return;

		$config =& hikashop_config();
		$statuses = $config->get('invoice_order_statuses','confirmed,shipped');
		if(empty($statuses)) $statuses = 'confirmed,shipped';
		$statuses = explode(',',$statuses);
		$condition = array();
		foreach($statuses as $status){
			if(!empty($status)) $condition[] = $query->db->Quote($status);
		}
		$myquery = "SELECT DISTINCT b.user_email
					FROM #__hikashop_order_product AS a
					LEFT JOIN #__hikashop_order AS c ON a.order_id = c.order_id
					LEFT JOIN #__hikashop_user AS b on c.order_user_id = b.user_id";
		$filters = array();
		if(count($condition)) $filters[] = "c.order_status IN (".implode(',',$condition).")";
		if(!empty($filter['product']) AND is_numeric($filter['product'])) $filters[] = "a.product_id = ".(int) $filter['product'];
		elseif(!empty($filter['cat']) AND is_numeric($filter['cat'])) $filters[] = "a.product_id IN (SELECT product_id FROM #__hikashop_product_category WHERE category_id = ".$filter['cat'].")";
		$datesVar = array('creationdatesup','creationdateinf');
		foreach($datesVar as $oneDate){
			if(empty($filter[$oneDate])) continue;
			$filter[$oneDate] = acymailing_replaceDate($filter[$oneDate]);
			if(!is_numeric($filter[$oneDate])) $filter[$oneDate] = strtotime($filter[$oneDate]);
		}

		if(empty($filter['datefield'])) $filter['datefield'] = 'order_created';

		if(!empty($filter['creationdateinf'])) $filters[] = 'c.`'.$filter['datefield'].'` > '.$filter['creationdateinf'];
		if(!empty($filter['creationdatesup'])) $filters[] = 'c.`'.$filter['datefield'].'` < '.$filter['creationdatesup'];
		
		if(count($filters)){
			$myquery.=' WHERE '.implode(' AND ',$filters);
		}
		
		$query->db->setQuery($myquery);
		$allEmails = acymailing_loadResultArray($query->db);
		if(empty($allEmails)) $allEmails[] = 'none';
		if(empty($filter['type'])){
			$query->where[] = "sub.email NOT IN ('".implode("','",$allEmails)."')";
		}else{
			$query->where[] = "sub.email IN ('".implode("','",$allEmails)."')";
		}
	}

in the file plugins/acymailing/hikashop/hikashop.php and that should fix the problem.

The following user(s) said Thank You: carniel

Please Log in or Create an account to join the conversation.

carniel
Topic Author
Offline

10 years 9 months ago #223853 by carniel

Replied by carniel on topic Acymailing Filter on Orders not working

That fixed it, thank you! Outstanding support :-)

There are no kangaroos in Austria! ;-)

Please Log in or Create an account to join the conversation.

Time to create page: 0.178 seconds