Filter on product in acymailing sql error

Hi,

Change the code:
$myquery .= ' LEFT JOIN #__hikashop_product_category AS hkpc ON (a.product_id = hkpc.product_id) OR (a.product_parent_id > 0 AND a.product_parent_id = hkpc.product_id) ';

to:
$myquery .= ' LEFT JOIN #__hikashop_product as hkp ON a.product_id = hkp.product_id LEFT JOIN #__hikashop_product_category AS hkpc ON (a.product_id = hkpc.product_id) OR (hkp.product_parent_id > 0 AND hkp.product_parent_id = hkpc.product_id) ';

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

Hi,

I think that your results are like that because the guest users are not synchronized with AcyMailing on your website.
Run that query in your PHPMyAdmin and that should help:
INSERT IGNORE INTO `#__acymailing_subscriber` (`email`,`userid`,`created`) SELECT `user_email`, `user_cms_id`,`user_created` FROM `#__hikashop_user`
(where you replace #__ with the table prefix of your website)

Hi,

Then I don't know. On my end I have a credible number of users found on my test website and the MySQL used looks correct too.
We would need a backend access and a FTP access in order to look deeper into that. An access to the PHPMyAdmin would be great too in order to look at the raw data and test the MySQL queries.
Please provide that along with a link to this thread via our contact form and we can have a look:
www.hikashop.com/support/contact-us.html

Hi,

Thanks for the accesses.
The issue doesn't come from a bug in HikaShop or the integration plugin with AcyMailing as far as I can see.
The problem is with your order statuses.
If you look at them, you can see this: monosnap.com/file/SxbpbpzIUhIKiR1GVmFFrSMfdTOdvm
This means that they aren't configured at all. This should never happen.
Also, if I use the order status filter on the orders listing in your backend, there is only one order with the status "confirmed" found. That's because all the others had the status translation set in them.
And that was because "bevestidg" was selected instead of "confirmed" in the "verified status" setting of your payment method (I don't know how that's possible?).
I've changed the verified status to "confirmed" in your payment method so future confirmed orders should have a correct order status.
What you can do is also update the order_status column of the table hikashop_order with the correct english namekey of the order statuses and not their translation. You can do that with either a mass action (a filter on order_status and an action on that same column) or directly in your PHPMyAdmin.
Also, I would recommend to properly configure your order statuses listing, with something like we have by default:
monosnap.com/file/PSWdVBWNSGGrU7CE0CXPTrOTQQEiW9
The AcyMailing filters should then produce much more realistic values.

Hi,

With the "hikashop orders" filter, you can select the status you want for your filter.
And for the "hikashop customers" filter, it uses the order statuses that are enabled in the "invoice" column of your menu System>Order statuses.
This however requires that you enable some statuses there, which wasn't the case, and that the order_status column in the hikashop_order table contains the namekey of these order statuses ( confirmed or shipped for example), and not their translation.
So as long as your store is properly configured and that the statuses of your orders in the database have the correct values, it should work like you want.