Ok so I have done it (in a way). I am not at all a php developer so basically I went in search for a different solution, there are flaws in this solution (it takes the Subscribers Name from the first address created and it's a First Name only) but it will work in my situation and hopefully some others. Also worth noting that this solution copies the new user every 30 minutes... you can change this if you like by editing the query when creating the scheduled event.
The way to do it is via MySQL Event Scheduler Queries (5.1+). So here's how.
1. Login to your MySQL database and ensure that Event Scheduler is running by running the query:
Code:
SET GLOBAL event_scheduler = "ON"
2. Login to your Joomla Backend and create two Hikashop Custom Fields (Components > Hikashop > Display > Custom Fields) as follows:
"Subscribe" as Radio Button field with two values "Yes" and "No" configured as follows:
Table:
user Column name:
subscribe Display: Font-end YES Back-end YES
"sub_user_name" as text field with no value configured as follows:
Table:
user Column name:
sub_user_name Display: NO for all
3.Once Created the Subscribe Yes/No should appear in the checkout
4. Go back to MySQL and run the following queries - PLEASE FILL IN YOUR DATABASE TABLE PREFIX WHERE "XXX" APPEARS IN THE QUERIES BELOW:
Code:
CREATE EVENT hikasub1
ON SCHEDULE
EVERY 30 MINUTE
COMMENT 'Copies name from address to sub_user_name'
DO
UPDATE
XXX_hikashop_user a, XXX_hikashop_address b
SET
a.sub_user_name = b.address_firstname
WHERE
a.user_id = b.address_user_id
Code:
CREATE EVENT hikasub2
ON SCHEDULE
EVERY 30 MINUTE
COMMENT 'Copies from Hikashop to AcyMailing'
DO
INSERT INTO
XXX_acymailing_subscriber (name, email)
SELECT
sub_user_name, user_email FROM XXX_hikashop_user
WHERE
subscribe = 'Yes'
ON DUPLICATE KEY UPDATE
subid=subid
Code:
CREATE EVENT hikasub3
ON SCHEDULE
EVERY 30 MINUTE
COMMENT 'Sets timestamp on new subscriber row'
DO
UPDATE
XXX_acymailing_subscriber
SET
created = UNIX_TIMESTAMP()
WHERE
created IS NULL
This step Adds the user to the Newsletter with the ID 1 if you want to subscribe them to another list just change the "1" at the beginning of the "SELECT" query.
Code:
CREATE EVENT hikasub4
ON SCHEDULE
EVERY 30 MINUTE
COMMENT 'Adds the new user to subscription list'
DO
INSERT INTO
XXX_acymailing_listsub (listid, subid, subdate, status)
SELECT
1, XXX_acymailing_subscriber.subid, XXX_acymailing_subscriber.created, 1
FROM
XXX_acymailing_subscriber
ON DUPLICATE KEY UPDATE
listid=listid
5. Test it! REMEMBER: It will take up to 30 minutes for the new user to be added to AcyMailing...
Hopefully this sets someone on the right track... It's fairly basic and I am sure anyone with PHP knowledge could make this work much better but I have a limited budget for my project and I also learnt some stuff on the way!
Chris
