Create Database Column to be used in both Inventory and Order Summary

[Claudia M]

Here's what I'm trying to do.  I have already created columns in the Inventory and Order Summary databases - est_ship_box

 

1.  Add est_ship_box to the admin.product.skin

         <div><label for="product_weight">{$LANG.catalogue.product_weight}</label><span><input name="product_weight" id="product_weight" class="textbox number" type="text" value="{$PRODUCT.product_weight}"></span></div>

          <div><label for="est_ship_box">Estimate Shipping Box</label><span><input name="est_ship_box" id="est_ship_box" class="textbox" type="text" value="{$PRODUCT.est_ship_box}"></span></div>

        <div>

<label for="product_featured">{$LANG.catalogue.product_featured}</label><span><input type="hidden" name="featured" id="product_featured" class="toggle" value="{$PRODUCT.featured}"></span></div>

 

I will input this information when I add a product

 

2.  When the item sells I would like the above information automatically updated to my Order Special Data Tab as follows.

<div id="specialdata" class="tab_content">

      <h3>Special Data</h3>

      <div class="w1200">

       <div style="background-color: #FFFFFF; font-size:1rem; text-align:center; ">All Venues</div>

       <fieldset >

                       <legend style="background-color: #B9C2AA;">All Item(s)</legend>

{foreach from=$PRODUCTS item=product}

        <div class="bg-white">{$product.quantity} &nbsp;x&nbsp; {$product.name} &nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp; {$product.excel_name} &nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp; {$product.product_code}&nbsp;&nbsp;&nbsp;&nbsp;<strong>Each:</strong>&nbsp;{$product.line}<strong>&nbsp;&nbsp;&nbsp;&nbsp;All:</strong>&nbsp;{$product.price_total}&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp;&nbsp;<strong>Cost:&nbsp;</strong>{$product.cost_price}

     &nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp;  <strong>Product Weight:</strong>&nbsp;{$product.product_weight}

    </div>

{/foreach}

<div class="bg-white">    <label for="order_wgt"><strong>Order Weight:&nbsp;</strong> </label>{$SUMMARY.weight} </div>

<div class="bg-white"> <label for="sold_site"><strong> Sold Site:</strong></label><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span>{$SUMMARY.sold_site}</span></div>

<div class="bg-white"> <label for="est_ship_box"><strong> Estimated Shipping Box:</strong></label><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span>{$PRODUCT.est_ship_box}</span></div>             

  </fieldset>

 

Thanks in advance for any and all help

[bsmither]

The table CubeCart_inventory is on a per-product basis. The table CubeCart_order_summary is on a per-order basis. The items sold in any given order is in CubeCart_order_inventory.

So, which of several product's box data gets logged in the order's summary? The product's box data could be stored with the sold product record.

This line of code (in 2. Order Special Data Tab), broken apart for readability:

<div class="bg-white">
  <label for="est_ship_box"><strong> Estimated Shipping Box:</strong></label>
  <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>
  <span>{$PRODUCT.est_ship_box}</span>
</div>

The variable $PRODUCTS is an array of product data, as seen in the {foreach} loop. The variable $PRODUCT does not exist.

I think you may need to rethink how your products get shipped.

Does each item get shipped in its own box? If so, then the product's box data would be best stored with the product data in CubeCart_order_inventory.

Do items that could conceivably be shipped with any other item not have any box data supplied?

Is the Order Special Data tab built to accept any new data? Or display only?

If display only, then use $product.est_ship_box (where $product is one element of the group of Item's Sold for the order) and put the line within the {foreach} loop.

 

[Claudia M]

I did this and then did a test order.  The info was not populated in the order

{foreach from=$PRODUCTS item=product}
        <div class="bg-white">{$product.quantity} &nbsp;x&nbsp; {$product.name} &nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp; {$product.excel_name} &nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp; {$product.product_code}&nbsp;&nbsp;&nbsp;&nbsp;<strong>Each:</strong>&nbsp;{$product.line}<strong>&nbsp;&nbsp;&nbsp;&nbsp;All:</strong>&nbsp;{$product.price_total}&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp;&nbsp;<strong>Cost:&nbsp;</strong>{$product.cost_price}
     &nbsp;&nbsp;&nbsp;--&nbsp;&nbsp;&nbsp;  <strong>Product Weight:</strong>&nbsp;{$product.product_weight} </div>
    <div class="bg-white">    <label for="est_ship_box"> <strong>Estimate Shipping Box:</strong></label>&nbsp;{$product.est_ship_box}
    </div>
{/foreach}

 

[bsmither]

Ok, I had assumed you solved the task of transferring the new data items (excel_name, line, est_ship_box, etc) from CubeCart_inventory to CubeCart_order_inventory.

"The info was not populated in the order"

All of the data? (The product_code, product_id, and name are the only directly transferred data in stock code of Cubecart.)

 

[Claudia M]

Is the what I should do in admin.sources.orders around line 327.  Changing ecel_name to est_ship_box?  Anything else I need to do?

 

foreach ($inventory as $product) {

             if ( empty($product['excel_name']) ) {

   $product_excel_name_record =

$GLOBALS['db']->select('CubeCart_inventory','excel_name',array('product_id'

=> $product['product_id']));

   $product['excel_name'] = $product_excel_name_record[0]['excel_name'];

}    

             if ( empty($product['product_weight']) ) {

   $product_product_weight_record =

$GLOBALS['db']->select('CubeCart_inventory','product_weight',array('product_id'

=> $product['product_id']));

   $product['product_weight'] = $product_product_weight_record[0]['product_weight'];

}

The above worked!

[bsmither]

Then I would recommend you continue this pattern:

Use these statements;

if ( empty($product['excel_name']) ) {
   $product_excel_name_record = $GLOBALS['db']->select('CubeCart_inventory','excel_name',array('product_id' => $product['product_id']));
   $product['excel_name'] = $product_excel_name_record[0]['excel_name'];
}

To make new statements (a copy of the above with the desired target data):

if ( empty($product['est_ship_box']) ) {
   $product_est_ship_box_record = $GLOBALS['db']->select('CubeCart_inventory','est_ship_box',array('product_id' => $product['product_id']));
   $product['est_ship_box'] = $product_est_ship_box_record[0]['est_ship_box'];
}

 

[Claudia M]

Thanks Brian!  Hey, one more quick question.  And if it's to involved that's ok. Per the image below which is in my Special Tab for orders you'll notice that it tells me the cost of the item.  Problem is I sold 4 of them and it doesn't give me the total cost for all 4 - just one. Any way I can show a total cost?

Thanks

Sorry forgot to send the picture

[bsmither]

This is one of my disagreements with CubeCart - not that I expect CC to be my main bookkeeping app.

The cost of a widget may vary being influenced by any of several factors for each instance of acquisition: bulk buys, the price that day, the price set by a given supplier, the cost of acquisition, and others. So, a gadget that bottom-line cost $10 from supplier #1 in Canada on Monday, may have a bottom-line cost of $12 from supplier #2 in Mexico on Friday. CubeCart can't keep track of that.

Even so, the data item 'quantity' and the 'cost_price' in the CubeCart_order_inventory are available. Create a column in the template's table and use the Smarty code:

{$product.quantity * $product.cost_price}

Smarty can do moderately complicated math stuff.

[Claudia M]

Thanks again Brian! That did it.

[Claudia M]

I know this is very picky of me, but it is really bugging me.

Your formula worked but it isn't carrying the decimal to 2 places (if zeros involved).  50 cents is showing as 0.5 instead of 0.50.  One dollar is showing as 1 instead of 1.00

Anyway to correct this

Thanks

[bsmither]

Hm...

Try:

{($product.quantity * $product.cost_price)|string_format:"%01.2f"}

If that doesn't work (blank page), try:

{$total_item_cost=$product.quantity * $product.cost_price}{$total_item_cost|string_format:"%01.2f"}

 

[Claudia M]

The first one worked - Thanks again