Jump to content
Sign in to follow this  
Claudia M

Add Products Sold column to Sales Report

Recommended Posts

In admin (CC615), the "Sales Reports" offers the admin the ability to get a general overview of sales for a defined segment of time.

There is no sorting at all.

Anything more than this will require an External Report plugin module.

Under "Statistics", however, there is Best Selling Products.

Please give us a more detailed description of where you are wanting this feature to appear.

Share this post


Link to post
Share on other sites

In the reporting / sales report add a filter of "items sold" where there is now pending, processing, etc. still with the date range option.

Share this post


Link to post
Share on other sites

This would probably have to go out to someone like HavenSwift, NoodleMan or Semperfi.

I've had similar custom work done, all of which at very reasonable prices.

Share this post


Link to post
Share on other sites

' add a filter of "items sold" '

A filter to do what? Return only sales where the sale contained this specific item? Or where the sale contained this many items? Or...?

Share this post


Link to post
Share on other sites

I want an option to list by the name of the item sold, still with all the columns, instead of the order number and still be able to pick the date range.

595682f9d5027_salesreportfilters-p.png.59562ed0fc881240669f20e240ca460a.png5956830021ed8_salesreport-p.png.84fdbfb31647d6c549c1115a28b2e53d.png

Share this post


Link to post
Share on other sites

I'm not sure whether this is the same requirement. I want to be able to see who bought products.

I can get a report that lists orders, but doesn't say what they bought.

I can get a list of best selling products, but it doesn't say who bought them.

I can get a list of unsettled orders, but until I open each, there is no way to see what they bought.

To get this information I have to go through the list of sales and click on each order in turn.

Is this really not included in the base system?

Share this post


Link to post
Share on other sites

Try this. Line numbers refer to CC6114:

In the admin skin template dashboard.index.php, near line 162:

               <td align="right">
                  {if isset($order.notes)}
                  <a href="?_g=orders&action=edit&order_id={$order.cart_order_id}&source=dashboard#order_notes" title="{foreach $order.notes as $note}{$note.time} {$note.content}{"\r\n"}{/foreach}"><i class="fa fa-sticky-note" title="{$LANG.common.notes}" aria-hidden="true"></i></a>
                  {/if}
                  <a href="{$order.link_print}" class="print" target="_blank" title="{$LANG.common.print}"><i class="fa fa-print" title="{$LANG.common.print}"></i></a>


Change to:

               <td align="right" NOWRAP>
                  {if isset($order.notes)}
                  <a href="?_g=orders&action=edit&order_id={$order.cart_order_id}&source=dashboard#order_notes" title="{foreach $order.notes as $note}{$note.time} {$note.content}{"\r\n"}{/foreach}"><i class="fa fa-sticky-note" title="{$LANG.common.notes}" aria-hidden="true"></i></a>
                  {/if}
<!-- -->
                  {if isset($order.items)}
                  <a href="?_g=orders&action=edit&order_id={$order.cart_order_id}&source=dashboard#order_inventory" title="{foreach $order.items as $item}{$item.quantity}{" x "}{$item.product_code}{"\r\n"}{/foreach}"><i class="fa fa-shopping-cart" alt="{$LANG.common.items}"></i></a>
                  {/if}
<!-- -->
                  <a href="{$order.link_print}" class="print" target="_blank" title="{$LANG.common.print}"><i class="fa fa-print" title="{$LANG.common.print}"></i></a>
In the admin source file dashboard.index.inc.php, near line 299, find:

foreach ($GLOBALS['hooks']->load('admin.dashboard.unsettled_orders') as $hook) include $hook;

We can use this hook. But to complete this experiment, on a new blank line after this, add:

/**/
	if (($order_inventory = $GLOBALS['db']->select('CubeCart_order_inventory', '`cart_order_id`,`product_code`,`quantity`', array('cart_order_id' => $cart_order_ids))) !== false) {
		foreach ($order_inventory as $inventory) {
			$order_items[$inventory['cart_order_id']]['items'][] = $inventory;
		}
		$orders = merge_array($orders, $order_items);
	}
/**/

In admin, Dashboard, Unsettled Orders tab, this gives an icon (shopping basket - available in latest versions of CubeCart having a recent version of the Font Awesome files) that, when hovering the mouse over it, will show a 'tooltip' listing the products of that order.

  • Thanks 1

Share this post


Link to post
Share on other sites

I implemented this code and though it does work for unsettled orders, is there any way for this to show for all orders - complete, cancelled and processing, etc.? Maybe in the orders tab and the sales report?  Also in the customer list have this tooltip show what they purchased?

Thanks,

Share this post


Link to post
Share on other sites

To add the ability to filter a Sales Report against a known Product Code, try this. (We could use the available hooks, but this is just an experiment.)

In the admin skin template reports.index.php, near line 90, find:

		</div>
	</fieldset>

Change to:

		</div>
		<div>
		  <label for="product_search">{$LANG.catalogue.product_code}</label>
		  <span>
			<input type="text" id="product_search" name="report[product_search]" class="textbox" value="{$POST.product_search}">
		  </span>
		</div>
	</fieldset>
In the admin source file reports.index.inc.php, near line 121, find:

foreach ($GLOBALS['hooks']->load('admin.reports.order.post') as $hook) include $hook;

On a new blank line below that, add:

if (isset($_POST['report']['product_search']) && !empty(trim($_POST['report']['product_search'])) && $orders) {
	foreach ($orders as $order) {
		$filter_summary[$order['cart_order_id']] = $order;
		$filter_cart_order_ids[] = "'".$order['cart_order_id']."'";
	}
	$where = "`cart_order_id` IN (".implode(',',$filter_cart_order_ids).") AND `product_code` = '".trim($_POST['report']['product_search'])."'";
	$inventories = $GLOBALS['db']->select('CubeCart_order_inventory', 'cart_order_id', $where);
	if ($inventories) {
		foreach ($inventories as $inventory) {
			$filter_inventory[$inventory['cart_order_id']] = $inventory;
		}
	}
	$orders = array_intersect_key($filter_summary, $filter_inventory);
	$GLOBALS['smarty']->assign('REPORT_TITLE', $report_title." :: ".$_POST['report']['product_search']);
}

Near line 44, find:

foreach ($GLOBALS['hooks']->load('admin.reports.top') as $hook) include $hook;

On the blank line below that, add:

/**/# Also process $_POST['report']['product_search'] if necessary

This will accept a specific Product Code. If the text box is empty, no filtering for a product will be performed.

Please Note: CubeCart allows for a product to have an Options Matrix that has a distinct Product Code for each option combination, and which could be different than the Product Code for the master product. It is these distinct Product Codes that are in the database table for sold inventory.

There are no changes in what data gets displayed. That is, values in the numerical columns still reflect the order totals, as opposed to a value and summation of just that product's sold price multiplied by quantity.

 

Share this post


Link to post
Share on other sites

"Maybe in the orders tab and the sales report?  Also in the customer list have this tooltip show what they purchased?"

I have a customer that has made 85 orders. So, no, listing what they purchased (in their lifetime?) in the admin, Customers (Customer List) is not a good idea.

In admin, Orders... This is very similar to the Unsettled Orders tab of the Dashboard.

In the admin skin template orders.index.php, near line 47, find:

               <td>{$order.status}</td>
               <td>{$order.date}</td>
               <td align="right">{$order.prod_total}</td>
               <td align="center">

Change to:

               <td>{$order.status}</td>
               <td>{$order.date}</td>
               <td align="right">{$order.prod_total}</td>
               <td align="right" width="70" NOWRAP>
<!-- -->
                  {if isset($order.items)}
                  <a href="{$order.link_edit}#order_inventory" title="{foreach $order.items as $item}{$item.quantity}{" x "}{$item.product_code}{"\r\n"}{/foreach}"><i class="fa fa-shopping-cart" alt="{$LANG.common.items}"></i></a>
                  {/if}
<!-- -->
In admin sources orders.index.inc.php, near line 764, find:

			$smarty_data['list_orders'][] = $order;
		}
		$GLOBALS['smarty']->assign('ORDER_LIST', $smarty_data['list_orders']);

Change to:

			$smarty_data['list_orders'][$order['cart_order_id']] = $order;
		}
/**/
	if (($order_inventory = $GLOBALS['db']->select('CubeCart_order_inventory', '`cart_order_id`,`product_code`,`quantity`', array('cart_order_id' => $cart_order_ids))) !== false) {
		foreach ($order_inventory as $inventory) {
			$order_items[$inventory['cart_order_id']]['items'][] = $inventory;
		}
		$smarty_data['list_orders'] = merge_array($smarty_data['list_orders'], $order_items);
	}
/**/
		$GLOBALS['smarty']->assign('ORDER_LIST', $smarty_data['list_orders']);

Near line 750, find:

		foreach ($orders as $order) {
			$order['name']   = (isset($order['name']) && !empty($order['name'])) ? $order['name'] : sprintf('%s %s %s', $order['title'], $order['first_name'], $order['last_name']);

Change to:

		foreach ($orders as $order) {
			$cart_order_ids[] = "'".$order['cart_order_id']."'";
			$order['name']   = (isset($order['name']) && !empty($order['name'])) ? $order['name'] : sprintf('%s %s %s', $order['title'], $order['first_name'], $order['last_name']);

 

Share this post


Link to post
Share on other sites

Hi Brian,

I'm spring, well winter, cleaning my store and came upon this topic looking for an item I sold.  I must not have implemented it when I updated to 6.2.2.  In this topic you say "there are hooks for this ..."

As I remember the code worked great is there anyway to use the hooks and and add a code snippet so I don't forget to implement this again.

Thanks,

Claudia

Edited by Claudia M

Share this post


Link to post
Share on other sites

Brian,

Just noticed i'm getting this PHP warning:

[19-Dec-2018 11:22:44 America/Louisville] PHP Warning:  array_intersect_key() [<a href='http://docs.php.net/manual/en/function.array-intersect-key.php'>function.array-intersect-key.php</a>]: Argument #2 is not an array in /home/claudias/public_html/modules/plugins/Show_Inventory_on_Orders_List/hooks/admin.reports.order.post.php on line 10
[19-Dec-2018 11:25:50 America/Louisville] PHP Warning:  array_intersect_key() [<a href='http://docs.php.net/manual/en/function.array-intersect-key.php'>function.array-intersect-key.php</a>]: Argument #2 is not an array in /home/claudias/public_html/modules/plugins/Show_Inventory_on_Orders_List/hooks/admin.reports.order.post.php on line 10
[19-Dec-2018 11:27:36 America/Louisville] PHP Warning:  array_intersect_key() [<a href='http://docs.php.net/manual/en/function.array-intersect-key.php'>function.array-intersect-key.php</a>]: Argument #2 is not an array in /home/claudias/public_html/modules/plugins/Show_Inventory_on_Orders_List/hooks/admin.reports.order.post.php on line 10
[19-Dec-2018 11:28:36 America/Louisville] PHP Warning:  array_intersect_key() [<a href='http://docs.php.net/manual/en/function.array-intersect-key.php'>function.array-intersect-key.php</a>]: Argument #2 is not an array in /home/claudias/public_html/modules/plugins/Show_Inventory_on_Orders_List/hooks/admin.reports.order.post.php on line 10

Line 10 reads:

$orders = array_intersect_key($product_filter_summary, $filtered_inventory);

Claudia

 

Share this post


Link to post
Share on other sites

Could it be that one (or more) of the orders in the resultant list has no items of inventory?

Share this post


Link to post
Share on other sites

Yes I was fooling around with product codes and sold dates.  I've changed product codes so often some of my older products have different codes.  Can I change all my orders product codes to the new ones or would that mess things up.  I'd like to do it on products I've sold more than one of. Most of my inventory is one and gone.

Share this post


Link to post
Share on other sites

" Can I change all my orders product codes to the new ones "

Yes. That would involve some serious amount of SQL statements made using a utility such as phpMyAdmin: UPDATE this to that.

Or some insane amount of work editing items on the Inventory tab of the Edit Order admin screen one order at a time.

Share this post


Link to post
Share on other sites

bsmithers solution works brilliantly for me. My only regret is that it took me so long to thank him for this.

I can't see any reason why it shouldn't be there by default. Is there some down side to this functionality?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...