Magento 2: Internal Sales vs Consumer Sales Reporting

We’re one of the unique shops in America that doesn’t have a POS system in place. 99% of our business comes from online orders through a sales channel. As we grew, we began to establish a presence and added some internal customer service and sales people. Our CEO is very money conscious. As a result, we never spent money on a stand-alone POS. We’ve instead turned to the Magento 2 back-end ordering system. This is where we found a lack of separation of internal sales and consumer sales reporting.

Magento Reporting Explanation

Over the years, I’ve found Magento focuses a little bit less on certain areas of the framework than others. This is probably intentional for several reasons. The reports appear designed to be generic and cover a general range of business requirements. Each will likely fit into any scenario. If you expand on those, you’re going into guessing territory based on what 100,000 different people might want.

We wanted to harness something that I am guessing most people don’t really need from Magento. Sales reporting based on both consumer sales and internal sales are important to us. Showing them together doesn’t always accomplish our goals.

Natively, the sales_order table doesn’t contain the user that created the order. That’s perfectly fine, it doesn’t have, nor does it fit in with the database design. Magento’s architects found a good place for that information. This is where we can tap into the data and separate the two types of sales.

Database Explanation

There are two tables that I want to talk about.

sales_order
magento_logging_event

The first table, sales_order, is where you’ll find all the basic information about your order. Totals, order id, consumer-facing order number, etc. This is the base point for finding the back-end information associated with it.

The second table, magento_logging_event, is where we’re going to pull out the information for the person who entered the sale. In here you’re going to find a lot of good information. This is not just for sales tracking. There is all kinds of information based on login, cache management, system configuration, cms updates, sales information, and more.

We’re going to take these two tables and join them together to get the desired output we want.

The SQL Behind Explanation

Let’s start with a sample query. This is very basic and will pull out basic user, time and all the information you’ll need to grab the order details.

SELECT 
	DATE_FORMAT( time, '%Y-%m-%d' ) AS time,
	info,
	user
FROM 
	magento_logging_event
WHERE 
	event_code = 'sales_orders' AND 
	fullaction = 'sales_order_create_save' AND
	status     = 'success' AND
	time BETWEEN '02-01-2017 00:00:00' AND '02-01-2017 23:59:59'
ORDER BY
 	user ASC

The first thing to talk about is the info column. This is a serialized array containing the order information. Let’s examine this further.

a: 2: {
	s: 7: "general";s: 2: "75";s: 10: "additional";a: 1: {
		s: 25: "Magento\Sales\Model\Order";a: 1: {
			i: 75;a: 1: {
				s: 12: "increment_id";s: 9: "250207715";
			}
		}
	}
}

If you’re using PHP, you can simply unserialize this and read it as an array. Otherwise, you’ll want to parse through this a bit. Where you see “general”, that is the “sales_order.entity_id” column. After that, the “75” is the value for that column.

Additional takes you further and gives you access to the increment_id, or the consumer-facing order number. You can see that it is grouped by it’s entity_id.

If you’re not using PHP, or just want to take a look at what it would look like unserialized, click this link.

Let’s Put It All Together

Let’s talk about one more query and I’ll toss some PHP code at you.

SELECT * FROM sales_order WHERE entity_id  = 75

This doesn’t really need much talk, but this is how you will get all the basic sales_order information out of the database based on your order from the logging table.

What I do with our report is pretty basic. I drop that first query in and then cycle through all of the orders, pulling base total information out of our database. After that, I write it to a temporary CSV and email it to various people. Its nothing fancy, but you can make your’s as fancy as you can imagine.

Here’s some PHP code to get you started.

$date = date( "Y-m-d" );

$salesSql = "SELECT 
				DATE_FORMAT( time, '%Y-%m-%d' ) AS time,
				info,
				user
			 FROM 
				magento_logging_event
			 WHERE 
				event_code = 'sales_orders' AND 
				fullaction = 'sales_order_create_save' AND
				status     = 'success' AND
				time BETWEEN '" . $date . " 00:00:00' AND '" . $date . " 23:59:59'
			 ORDER BY
			 	user ASC";

$salesResults = $dbc->query( $salesSql );

fwrite( $file, "date,user,order_id,sale_total,one_percent\n" );

while ( $sale = mysqli_fetch_assoc( $salesResults ) )
{
	$saleInfo   = unserialize( $sale['info'] );
	
	$entityId   = $saleInfo['general'];
	$orderId    = $saleInfo['additional']['Magento\Sales\Model\Order'][$entityId]['increment_id'];
	$orderTotal = getOrderTotal( $entityId, $dbc );

	$reportLine = $sale['time'] . "," . $sale['user'] . "," . $orderId . "," . $orderTotal . "," . $orderTotal * 0.01 . "\n";

	echo $reportLine;
}

function getOrderTotal( $entityId, $dbc )
{
    $orderInfoSql     = "SELECT base_grand_total FROM sales_order WHERE entity_id  = " . $entityId;

	$orderInfoResults = $dbc->query( $orderInfoSql );

	$orderInfo        = mysqli_fetch_assoc( $orderInfoResults );

	return $orderInfo['base_grand_total'];
}

I have a function that gets the order’s base grand total. That makes things a little cleaner to read. You can easily convert this to a class or two and build a really nice reporting library from it. Spend some time looking through the sales_order_* tables if you’re not familiar with them. You’ll find a plethora of information about your order to add to your reporting.

Conclusion

Magento 2 offers you a wide range of basic reports. These reports are a good foundation to begin thinking about other reports you’d like to build. Reports to fit your business and help your management team succeed in growing your e-commerce operations. All of the information is in the database. You just have to ask for it.

-Dan

Written by Dan
I am a Magento Certified Developer Plus and Lead Developer for an online auto parts retailer. My hobbies include anything tech, movies, video games, and hanging out with my wife and daughter. I am a Christian and like sharing things I am studying and reading.