Our customers pay for licenses that run for some time, e.g. 6 months.
Example:
Product: product1
Payment date: 2024-01-01
Start date: 2024-01-01
End date: 2024-03-31
Total excluding tax: $29.99
We want to know how much of this amount goes towards which month.
So we calculate the number of days (in this case: 31 + 29 + 31 = 91) and divide the amount by this number to get the amount per day ($29.99 / 91 days = $0.32956 / day), and then we get:
January 2024: 31 * $0.32956 = $10.216
February 2024: 29 * $0.32956 = $9.557
March 2024: 31 * $0.32956 = $10.216
Here, the numbers are very similar for the sake of the example, but usually, we’d of course have licenses that start in the middle of the month, so the numbers can be very different.
We have thousands of such licenses for different products, so it’s a grouping both by product and by month, which would be very slow to do in the database (MariaDB).
For this reason, we want to do a single, simple SQL SELECT that fetches all licenses, and then do the grouping in PHP/Laravel code.
Yet for some reason, this is excruciatingly slow.
The code looks like this:
public function revenueWaterfall(Request $request)
{
$products = $request->get('product_id') == 'all' ? Products::all() : Product::whereId($request->get('product_id'))->get();
$bookedPeriodTo = $request->get('bookedPeriodTo');
$bookedPeriodFrom = $request->get('bookedPeriodFrom');
$productId = $products->count() == 1 ? $products->first()->id : null;
$allData = Invoice::getAllForPeriod(Carbon::parse($bookedPeriodFrom), Carbon::parse($bookedPeriodTo), $productId)->groupBy('product_id')->sort();
$splitValues = $allData->map(function ($productPayments) {
$total = $productPayments->sum('total_excluding_tax');
$months = $this->splitRevenueByMonth($productPayments);
return array_merge($months, ['total' => $total]);
});
return response()->json($splitValues);
}
function splitRevenueByMonth($payments)
{
$byMonth = [];
foreach ($payments as $payment) {
$startDay = Carbon::parse($payment->period_start)->startOfDay();
$endDay = Carbon::parse($payment->period_end)->startOfDay();
$daysInPeriod = $endDay->diffInDays($startDay);
if ($daysInPeriod < 1) {
// Handle invalid period
throw new Exception("Invalid period detected");
}
$amountPerDay = $payment->total_excluding_tax / $daysInPeriod;
for ($i = clone $startDay; $i->lessThan($endDay); $i->addDay()) {
$month = $i->format('M Y');
$byMonth[$month] = ($byMonth[$month] ?? 0) + $amountPerDay;
}
}
return $byMonth;
}
Actually, when I look at the code now (I slightly simplified it for the sake of this question), it seems like the grouping by product is missing.
What we would like to have is an output like this:
{
"product1":
{
"January 2024": 2255209.2525,
"February 2024: 5252525.5336,
"March 2024": 35363.3636
},
"product2":
{
"December 2023": 309906.3532,
"January 2024": 3059035.9092
}
}
etc.
