Here are the SQL statements which I have used to integrate CB users with the Virtuemart tables.
This is offered without any guarantee that it will work for you. Back-Up! Don't test it on a live site.
This is for Release VM3.0.9; I have not investigated any earlier VM release.
The VM tables involved are
#__virtuemart_vmusers
#__virtuemart_userinfos
#__virtuemart_vmuser_shoppergroups [only if you have special shopper groups linked to CBSubs plans eg Member discounted prices]
The VM userinfos table keeps country and state data as ID fields [related to the VM countries and states tables]
To make this work I created a CB Query Drop Down (Single Select) field and linked it to the VM countries table: this field should replace any other 'country' field
Code:
SELECT `virtuemart_country_id`, `country_name` FROM `j25_virtuemart_countries` ORDER BY `country_name`SELECT `virtuemart_country_id`, `country_name`
FROM `j25_virtuemart_countries` ORDER BY `country_name`
Saving this query in the field generated a 'Permission Refused' error; the server Mod-security treated this as an SQL injection atack and prevented it being saved. To oversome this it was necessary to set the server mod-scurity to 'Detect only' briefly while the query was saved. Be sure to return it to Active as soon as possible.
The VM states table just has 'England', 'scotland' etc as United Kngdom states, so to work round this I changed the field
#__virtuemart_userinfos.`virtuemart_state_id` to a CHAR(255) field type and simply sent the CB.state text field as user-typed text. To do this you need access to your
database. Any VM update will, of course, break this, and it will need re-editing. But then, any VM upgrade could potentially break any part of this...
Now for the data update edit: this uses the CB Auto-actions plug-in: To cover new users entered either front or back-end I have entered two triggers:
NB: this must be entered without line-breaks within each statement [but a line-break is required between each statement] and both statements must be terminated with a ;
After New User; After User Registration
Code:
INSERT INTO `j25_virtuemart_vmusers` ( `virtuemart_user_id` ) VALUES ( '[user_id]' );
INSERT INTO `j25_virtuemart_userinfos` ( `virtuemart_user_id`, `address_type`, `last_name`, `first_name`, `phone_1`, `address_1`, `address_2`, `city`, `state`, `virtuemart_country_id`, `zip` ) VALUES ( '[user_id]', 'BT', '[lastname]', '[firstname]', '[phone]', '[location]', '[address]', '[city]', '[state]', '[cb_select_country]',
'[zipcode]' );
Note the 'BT' in the addresstype field; this marks the entry as a Billing Address; without this the address is stored but will not display
To handle users updating their profile information I created another auto-action:
After User Profile Saved
Code:
UPDATE `j25_virtuemart_userinfos` v inner join j25_comprofiler c on v.virtuemart_user_id= c.user_id SET `name`=c.firstname + ' '+
c.lastname,v.company=c.company,`last_name`=c.lastname,`first_name`=c.firstname,`middle_name`=c.middlename,`phone_1`=c.phone,v.`fax`=c.fax,`address_1`=c.location,`addre
ss_2`=c.address,v.`city`=c.city,`virtuemart_state_id`=c.state,v.`state`=c.state,`virtuemart_country_id`=c.cb_select_country,`zip`=c.zipcode WHERE
`virtuemart_user_id`='[user_id]';
To update shopper groups based on CBSubs plans I have used CBSubs SQL Actions
Plan>Integration>SQL Actions:
Code:
INSERT INTO `j25_virtuemart_vmuser_shoppergroups`(`virtuemart_user_id`, `virtuemart_shoppergroup_id`) VALUES ('[user_id]',[shoppergroup ID]);
This [shoppergroup ID] should be the ID of the group your plan allows access to. (shoppergroups are stored in the #__shoppergroups table)
To return a user to the default when a subscription expires, I added a second CBSubs Qury action
Code:
UPDATE `j25_virtuemart_vmuser_shoppergroups` SET `virtuemart_shoppergroup_id`=[your default shoppergroup ID] WHERE virtuemart_user_id`='[user_id]';
I've not handled a Delete User event here you might want to inculde an After Delete User triggered Action to do this, to clear up orphaned data in the VM tables.
krileon
