Jump to content

Add Products Sold column to Sales Report


Claudia M

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.

Link to comment
Share on other sites

  • 8 months later...

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?

Link to comment
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.

Link to comment
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,

Link to comment
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.

 

Link to comment
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']);

 

Link to comment
Share on other sites

  • 9 months later...

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

Link to comment
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

 

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

  • 2 months later...
  • 1 year later...
On 12/18/2018 at 4:50 PM, bsmither said:

Ok, I have put the edits into a plugin module.

 

Is this available somewhere?  One of my clients wants this functionality and it appears it still isn't built into the latest version of CC.  I don't see it in the Extensions Marketplace.

Thanks

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...