PHP to Insert Virtuemart items into Fabrik Elements?

[Metabern]

I'm trying to see if I can use Fabrik to create a custom Freight/Shipping Calculator in Virtuemart. The first step is to get Virtuemart data showing in a Fabrik Form.

Examples of what I need to be able to bring into Fabrik

Current VM User Info
jos_virtuemart_userinfos___virtuemart_user_id
jos_virtuemart_userinfos___company
jos_virtuemart_userinfos___address_1
jos_virtuemart_userinfos___first_name
jos_virtuemart_userinfos___last_name

etc.
What would be the php that I would add to my element(s) to show this?

 

[cheesegrits]

Kind of depends how you are selecting the VM user.

If you have a join element to the jos_virtuemart_userinfos table, then you could use an autofill form plugin to populate other elements.

-- hugh

 

[Metabern]

This is working well. I'm having issues with the autofill configuration.

Not sure if I have this right
I want to autofill/map current joomla user to vm user. Once I have vm user I autofill cart, order, etc...

My first attempt at autofill doesn't seem to work though. My mapping code is:
{"#__users___id":"virtuemart_carts___virtuemart_user_id"}

All of the variations of this, with and without prefixes gives me a "not found"

I have a couple screenshots of my settings

Any advice as to where I'm going wrong?

Cheers
p.s. I'm really impressed with Fabrik. The product, the range of options and the support. Are there any Fabrik/Virtuemart Integrations Plugins that are available and any being considered?

 

[cheesegrits]

That doesn't look right at all.

And if you are mapping the current user, rather than selecting one from a join element, then you could do it with element defaults. So if you have an element on your form for the VM user company, you could get that with an eval'ed default of ...

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('company')->from('jos_virtuemart_userinfos')->where('virtumart_user_id = ' . JFactory::getUser()->get('id'));
$db->setQuery($query);
return $db->loadResult();

Rinse and repeat for other fields you want to pre-fill.

Doing it this way isn't exactly efficient, doing separate queries for each element, but it'll work. There are ways to do it more efficiently, but require more work.

-- hugh

 

[Metabern]

Worked perfectly! Thanks. Rinse and repeat for relevant fields was easy.

So in my freight calculator I have to be able to go into a cart and other VM fields and get info. Works great.

One of the fields I need info from is: jos_virtuemart_carts___cartData.
I am able to show this cartData field however now I need to parse it and return the info contained as a list

Here is an example of the cartData returns

{"cartProductsData":[{"virtuemart_product_id":1081,"quantity":15,"customProductData":{"200":{"36199":false}}},{"virtuemart_product_id":271,"quantity":6,"customProductData":{"229":{"45499":false}}},{"virtuemart_product_id":319,"quantity":10,"customProductData":{"229":{"45673":false}}}],"vendorId":1,"lastVisitedCategoryId":0,"virtuemart_shipmentmethod_id":"11","virtuemart_paymentmethod_id":"1","automaticSelectedShipment":true,"automaticSelectedPayment":true,"order_number":"RQPX05","BT":{"company":"Test Company","address_1":"123 Street","city":"TestCity","virtuemart_country_id":38,"virtuemart_state_id":62,"zip":"ABC123","phone_1":"125.456.7890","fax":"","first_name":"Joe-Test","last_name":"Test","email":"test@yahoo.com","tos":1,"courierchoice":"","courieraccount":"","preorder":"1","tailgate_delivery":"1","residential_delivery":"0","military_base_delivery":"1"},"ST":{"company":"Test Company","address_1":"123 Street","city":"TEstCity","virtuemart_country_id":38,"virtuemart_state_id":62,"zip":"ABC123","phone_1":"125.456.7890","fax":"","first_name":"UserTEst","last_name":"Test","email":"test@yahoo.com","tos":1,"courierchoice":"","courieraccount":"","preorder":"1","tailgate_delivery":"1","residential_delivery":"0","military_base_delivery":"1"},"cartfields":{"tailgate_delivery":0,"residential_delivery":0,"military_base_delivery":0},"couponCode":"","_triesValidateCoupon":[],"order_language":"en-GB","pricesCurrency":"144","paymentCurrency":"144","_guest":0,"_inCheckOut":false,"_inConfirm":false,"_dataValidated":"d41d8cd98f00b204e9800998ecf8427e","_confirmDone":false,"STsameAsBT":1,"selected_shipto":0,"_fromCart":false,"layout":"default","layoutPath":"","virtuemart_cart_id":"40"}

I need to be able to parse the following from the above and then show the cart id and a list of products within.

virtuemartcart_id:"40"
-virtuemart_product_id":1081 "quantity":15
-virtuemart_product_id":271,"quantity":6
-virtuemart_product_id":319,"quantity":10

I'm not sure what the best approach is. I've looked at a regex validator and a calc with no luck. Can you recommend an approach and show me some rinse and repeat code. That would be extremely helpful.

 

[cheesegrits]

That's a JSON string, so if that string is in $vmData ...

$vmData = json_decode($vmData);

... will decode it into a PHP data structure. If you go to ...

https://www.functions-online.com/json_decode.html

... paste that string in and hit 'run' it'll show you the data structure.

Thing in {} are object, things in [] are arrays. You could then extract the ids and quantites something like ...

// decode the data
$vmData = json_decode($vmData);

// get the cart ID
$return = "cartd ID: " . $vmData->virtuemart_cart_id;

// loop around the cartProductsData array ...
foreach ($vmData->cartProductsData as $product) {
   // get the product ID and quanity from each product object, append to our return string
   return .= "product ID: " . $product->virtuemart_product_id . ", quanity: " . $product->quantity;
}

// return the string we built ...
return $return;

That's just a quick and dirty example of building a string from the data.

-- hugh

 

[Metabern]

We were successful and were able to interpret the json sting to data. I need however to be able to parse the string and generate repeat lists of elements. I need to do this because of the way Virtuemart stores the data I need in my Freight Calculator.

jos_virtuemart_carts___cartData gives me my json string which has my product id and quantity

Then using product_id I intend to grab more info from jos_virtuemart_products and jos_vmdropshipping_warehouse (Third party VM Extension) to complete the required info necessary for my API quote from my freight supplier.

What is the best Fabrik way to do this?



Heres What I have:

Cart Items

1. product ID: 1081, quantity: 15, name: Product1
2. product ID: 271, quantity: 6, name: Product2
3. product ID: 319, quantity: 10, name: Product3
4. product ID: 801, quantity: 1, name: Product4

Here's what I want (I mocked this up manually but id want the list to auto generate repeats if there are more than 1 item in my cart)

Cheers. Thanks for the amazing support

 

[cheesegrits]

Hmmm, yeah, that gets a lot more difficult, as you'd be needing to pre-create repeat rows for a repeating group.

The problem being that setting the default for a single element is easy, but if you are trying to create multiple instances of a repeat group, which I think is what you'd be trying to do (so your main form/list is "Customer Order" and the repeat group is "Products Ordered"), that has to be done by hand, while loading the form data. You can't create repeat intances of an element from with an element default.

And it gets harder, because you can't create repeats for a new form. It's a chicken and egg thing. Repeats are a one-to-many (parent => child) related table, that need to know the id of the parent row so they can set their parent_id foreign key. But when creating a new form, the parent row doesn't exist till after the form has been submitted. So adding repeat group instances on the server side while building the new form won't work.

So you'd need to have something like an onBeforeLoad plugin, which runs before the data for the form is loaded, grabs the VM data for that user, pre-creates the main form's row in the table (so it has an id to use for the parent_id FKs) , and then pre-creates the repeated rows for the products.

It's not trivial, but it's stuff I've done before, for very similar applications.

But ... this is going beyond Standard support. I'd be happy to help with some more coding pointers on a Pro sub, or (probably the best option) do it for you as billable time.

-- hugh

 

[Metabern]

Thanks Hugh, Its an interesting quandary, we came up with the same solution.

Our approach is to create a little helper plugin to call all of the VM data we need extract the info we out of the json and then bring it all into Fabrik in one process. Once we started that, we figured it could make the whole VM Fabrik process a lot simpler by grabbing all of the tables we need for our process. A Fabrik VM plugin would be a great addition to your set of tools. Maybe you'll want to have a look at ours when its done
Cheers

 

[Metabern]

Hey there, we've created a plugin that grabs my VM CartData Json string and convert it into a normal table called cart_items. I now want to use a 'list join' to show cat items in my Fabrik component but cannot seem to get it to work.

My Primary Fabrik list /table jos_freight_quote_list has my Primary id and Send to information

My Secondary list the one I want to join has cart_items fields: id, virtuemart_cart_id, virtuemart_product_id, quantity, customProductData

I want to show all of my secondary list fields in my primary list

cart_items table contains all of my carts, I want to be able to only display cart associated with logged in user

Carts can contain many products, I want to be able to display a cart and all of its products in the same way you display the countries and regions in your list join example

Can you help? Let me know if you need more info or access for a clearer picture.

Thanks as always

 

[cheesegrits]

I'm assuming a user can have more than one order/cart?

-- hugh

 

[Metabern]

No any user will only have one cart.

 

[cheesegrits]

OK, so presumably you have a main form/list in Fabrik, for the order, where you currently grab the cart data for the user.

You'll need a second table, like 'product', which has a parent_id FK (foreign key).

Insert your product rows into that table, setting the parent_id to the rowid of the main form. This means you'll have to do the processing onAfterProcess, ie. after the main for data has been written to the database, so you know the PK (rowid) of the main form.

Add a list join on the main list, from main.id to products.parent_id, and set it to repeat.

-- hugh

 

[Metabern]

Excellent, a follow up question: What is the best way to create my second table in Fabrik?

My cart_items table data is created automatically from VM when a user adds something to his/her cart. N0-one front or back end will need to interact with it.

When creating a table I'm given the option to do a direct database connection. Is this the right option?

 

[cheesegrits]

I'd create both table data at the same time. Before you add the cart_items row, check to see if there is a user_carts (or whatever you call it) row for that user id. If not, create it ...

$userId = JFactory::getUser()->get('id');
$db = JFactory::getDbo();
$query = $db->getQuery();
$query->select('id')->from('user_carts')->where('user_id = ' . JFactory::getUser()->get('id'));
$db->setQuery($query);
$userCartId = $db->loadResult();

if (empty($userCartId)) {
   // set up $whatever here, ie any other fields you want in the user_carts table
   $query->clear()
      ->insert('user_carts')
      ->columns(array($db->quoteName('user_id'), $db->quoteName('whatever')))
      ->values($userId . ',' . $db->quote($whatever));
   $db->setQuery($query);
   $db->execute();
   $userCartId = $db->insertid();
}

Then use $userCartId as the parent_id when you insert the cart_items rows.

-- hugh

 

[Metabern]

Thanks for all of your help and happy new year.

We've almost accomplished our goal. And Im not sure if completing it is possible. I want to be able to show a list join on a form page with all of the data in the list displayed.

My virtuemart freight calculator will appear as a form in the backend and as a display only form on the frontend where users info will be autofilled from their VM profile and their cart choices.

I have this set up for testing and I have two parts that sort of work.

1 Cart List where we have successfully displayed the Virtuemart cartData string as a Fabrik List. (See Attached1)
Currently it shows all users carts

2 Freight Quote submit Form (See Attached2)
I want to be able to show a list join as a part of a form with what is currently in users cart being displayed

Any thoughts?

 

[cheesegrits]

Well, if you set it up the way I suggested, with the individual product rows as a repeat group (one-to-many child table) on a carts (parent) table, and you display the cart details page, it should just work.

-- hugh

 

[Metabern]

No luck, I've tried a number of things and no success. I can see my cart items in a list page but cannot see them in a form page. All I get in my for page is blank elements. The goal is to have them all showing on a quotation form page.

We're close... I need to get this resolved, can I upgrade my support plan to do a some more hands-on troubleshooting?

 

[cheesegrits]

I can't really make coding commitments on a subscription basis, but I'd be happy to do it on an hourly rate.

Sent from my HTC6545LVW using Tapatalk

 

[Metabern]

Please send me some offline contacts so we could engage your services