Custom Query to fetch product wise sales report

[nitaish]

I am looking for a custom mysql query to fetch report of sales per product for a specified period. For example, if I want to know which product sold the most and which one sold the least in the year 2019, how can I write a mysql query to get this report?

[DennisHermannsen]

Do you only want to show newly registered products or would you also want renewals to be shown?

[nitaish]

Both new as well as renewal orders.

[DennisHermannsen]

You could use something like this to only show products registered in a specific year:

<?php
use WHMCS\Database\Capsule;
require __DIR__ . '/init.php';

$results = Capsule::table('tblhosting')
	->select('packageid', Capsule::raw('count(packageid) quantity'))
	->where('regdate', 'LIKE', '2019-%')
	->groupBy('packageid')
	->get();
	
print_r($results);

Renewals are a bit more difficult. You'd probably have to go through the tblinvoiceitems table and search by product name.

[nitaish]

How to use this script?

[DennisHermannsen]

Place it in a PHP file and access the file from your browser.

It only shows the data in an array.

[brian!]

9 hours ago, DennisHermannsen said:

You could use something like this to only show products registered in a specific year:

I don't think that would be accurate for a number of reasons - not the least of which being that you would need to get the clients currency for the results to make any sense... though I appreciate that the question didn't mention income, so that point may not be relevant.

10 hours ago, nitaish said:

I am looking for a custom mysql query to fetch report of sales per product for a specified period. For example, if I want to know which product sold the most and which one sold the least in the year 2019, how can I write a mysql query to get this report?

wouldn't it be simpler (relatively!) to duplicate (e.g don't edit the original file) the existing "Income By Product" report and modify it to suit your needs, .e g adjust the date range in the queries.

https://docs.whmcs.com/Reports

Quote

Income by Product Provides a breakdown per product/service of invoices paid in a given month. This excludes overpayments & other payments made to deposit funds (credit), and includes invoices paid from credit added in previous months, and thus may not match the income total for the month.

or you could rewrite the two product queries from the above report into SQL and run it from phpmyadmin or similar, adjusting the dates as applicable.

Edited February 10, 2020 by brian!

[DennisHermannsen]

2 minutes ago, brian! said:

I don't think that would be accurate for a number of reasons - not the least of which being that you would need to get the clients currency for the results to make any sense.

Didn't think the amount of money earned per product was needed 😄

[brian!]

17 hours ago, DennisHermannsen said:

Didn't think the amount of money earned per product was needed

it wasn't... I misread the question lol