Retrieving product pricing information using capsule

[aggrosoft]

What is the correct way to retrieve a product and all of the pricing information. What I want to build is a custom detail page for a product, but I need the information of products just like you would see them on the cart.php page.

 

{if $product.bid}
               {$LANG.bundledeal}<br />
               {if $product.displayprice}
                   <span class="price">{$product.displayprice}</span>
               {/if}
           {else}
               {if $product.hasconfigoptions}
                   {$LANG.startingfrom}
                   <br />
               {/if}
               <span class="price">{$product.pricing.minprice.price}</span>
               <br />
               {if $product.pricing.minprice.cycle eq "monthly"}
                   {$LANG.orderpaymenttermmonthly}
               {elseif $product.pricing.minprice.cycle eq "quarterly"}
                   {$LANG.orderpaymenttermquarterly}
               {elseif $product.pricing.minprice.cycle eq "semiannually"}
                   {$LANG.orderpaymenttermsemiannually}
               {elseif $product.pricing.minprice.cycle eq "annually"}
                   {$LANG.orderpaymenttermannually}
               {elseif $product.pricing.minprice.cycle eq "biennially"}
                   {$LANG.orderpaymenttermbiennially}
               {elseif $product.pricing.minprice.cycle eq "triennially"}
                   {$LANG.orderpaymenttermtriennially}
               {/if}
           {/if}

 

If i just load the product via capsule the whole $product.pricing is missing, even if I join it in the fields will miss the whole minprice etc. I tried using the WHMCS\Product\Product model but this will not give me this information either.

[brian!]

minprice isn't stored in the database, it's calculated by WHMCS internally when it creates the $products array for the cart - theoretically, you could do it yourself in a hook (or Smarty in the template), but remember that its value is not (necessarily) the cheapest price, it's just the price of the smallest valid cycle.. e.g, onetime/monthly, quarterly, semi etc.

 

if you have written a hook that is querying tblproducts and joining tblpricing to it, then that's going to give you most (all?) of the information you need.

 

the best advice I can give is to not waste time trying to recreate the $products array from the cart just for the sake of allowing you to use existing Smarty template code, it's going to be easier for you to work out exactly what information you want to show, and then work out how to get it and how to show it.

 

the hook below should give you all products with pricing - but it's just a starting point... at the very least, you don't need it to run on all pages, only your custom page - and certainly not the cart (which is why I renamed the custom array to $myproducts instead of $products to avoid a clash)... if you keep it called that, that in turn means you'll need to rename the variables in the template - but its a custom page with a custom template, so you'd likely need to make some changes anyway.

 

and the array contains a LOT of information, so you will likely want to trim down the amount of information pulled from the database to just what's required.

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function homepage_products_hook($vars) {

   $products = Capsule::table('tblproducts')
                       ->join('tblpricing', 'tblproducts.id', '=', 'tblpricing.relid')
                       ->select('tblproducts.*','tblpricing.*')
                       ->where('tblpricing.type', 'product')
                       ->get();

   $encodedata = json_encode($products);
   $decodedata = json_decode($encodedata, true);

   return array("myproducts" => $decodedata);
}
add_hook("ClientAreaPage", 1, "homepage_products_hook");
?>

originally, I had it using the homepage hook, but that wouldn't work for you on a custom page.

[aggrosoft]

Yeah I thought it might be easier to use the function that is already there, I rolled my own implementation. Maybe WHMCS will allow us to use the internal functions some day - until then your solution is a good way. Thanks for your feedback!