Virtuemart Integration Code

7 years 2 months ago #270359 by Thelowlandpiper
Virtuemart Integration Code was created by Thelowlandpiper
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_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
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
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
UPDATE `j25_virtuemart_userinfos` v inner join j25_comprofiler c on v.virtuemart_user_id= c.user_id  SET `name`=c.firstname + ' '+
ss_2`=c.address,v.`city`,`virtuemart_state_id`=c.state,v.`state`=c.state,`virtuemart_country_id`=c.cb_select_country,`zip`=c.zipcode WHERE

To update shopper groups based on CBSubs plans I have used CBSubs SQL Actions
Plan>Integration>SQL Actions:
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
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.
The following user(s) said Thank You: smalldragoon, krileon

Please Log in to join the conversation.

7 years 2 months ago #271404 by smalldragoon
Replied by smalldragoon on topic Virtuemart Integration Code
Just found your post on virtuemart and I was wondering if you would have any idea on how to integrate CB action after VM payment ?
I would need to execute some action(s) after a purchase is made ( and cherry on the cake, before as well ).
I'm open to any suggestion !
The action itself with Virtuemart is deprecated.
Thx !

Please Log in to join the conversation.

7 years 2 months ago #271422 by krileon
Replied by krileon on topic Virtuemart Integration Code
For you to do that you would need to modify VM or write a plugin for VM to fire a CB trigger. CB Auto Actions acts on CB triggers. If there's no trigger to act on it can't do anything. See the below regarding implementing new triggers.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.307 seconds

Facebook Twitter LinkedIn