I built my own product collection which I use for my own shop dashboard. What it should do is for each selected product to go through the last 13 months and show me the amount that was sold.
...
public function __construct(
MagentoSalesModelResourceModelReportBestsellersCollectionFactory $collectionFactory, // for bestseller
...
array $data = []
)
{
$this->_collectionFactory = $collectionFactory;
...
parent::__construct($context, $data);
}
and here is the function
public function getMonthCollection($from, $to)
{
$bestSellerProdcutCollection = $this->_collectionFactory->create();
$bestSellerProdcutCollection->setModel('MagentoCatalogModelProduct')->setPeriod('month');
$datefrom = date('Y-m-d', strtotime('-'.$from.' month'));
$dateto = date('Y-m-d', strtotime('-'.$to.' month'));
$bestSellerProdcutCollection->addFieldToFilter('period',array('gt'=>$datefrom));
$bestSellerProdcutCollection->getSelect()->group('Product_Name')->order('Product_Name ASC');
return $bestSellerProdcutCollection;
}
This returns a list of all the products from each month and everything is fine.
I get a list like this:
Product | month | quantity |
---|---|---|
A | 12/2023 | 150 |
A | 01/2024 | 200 |
A | 02/2024 | 250 |
B | 12/2023 | 500 |
B | 01/2024 | 600 |
B | 02/2024 | 500 |
This fine as I can put all these values in arrays and create my own individueal-style table and dashboard look.
The problem:
Let’s say product A was out of stock in January and I made 0 sales with it. I want the report to be like this:
Product | month | quantity |
---|---|---|
A | 12/2023 | 150 |
A | 01/2024 | 0 |
A | 02/2024 | 250 |
B | 12/2023 | 500 |
B | 01/2024 | 600 |
B | 02/2024 | 500 |
However, since there is no data for product A in January, the whole month is being skipped and the data is like this:
Product | month | quantity |
---|---|---|
A | 12/2023 | 150 |
A | 02/2024 | 250 |
B | 12/2023 | 500 |
B | 01/2024 | 600 |
B | 02/2024 | 500 |
Now I have a problem, since the data entries are not the same for every product and some data for some months is missing.
Is there a way to insert a 0 rather than skipping the month?