Trying to calculate monthly income with month-end issues

Question

I am trying to calculate monthly income but I want it to be automatic and I also want it to account for month-end issues..

I want it to calculate from/on the 12th to the 11th, but I want it to also include any income that came in on the 11th so that the 11th doesnt get left out each month..

I can get it to calculate, but its only calculating the income from/on the 12th to the 10th instead of the 11th.

I'm trying to make it automatic so that when the month changes it will still be accurate regardless of whether the month has 28, 30 or 31 days but I cannot find a way of doing it correctly where it accounts for all these things.

I'm doing this for 3 months, so 3 pieces of code underneath one another, but it seems that only the second block of code (which would be calculating the 12th, May - 11th, June) is not including the income for the 11th (although this could be the same for the current month too, I wont know until next month),

EDIT - Its only a simple gross profit too, so its only multiplying the 'price' column by the 'quantity' column foreach row then summing up the total.

EDIT - I manually calculated gross profit from the 3rd month back too (April 12th - 11th May), but its accurate since there was no income on the 11th of May, so I believe the only issue is with it not including any income on the 11th each month

this is what I have:

Any help will be really appreciated!

Current Month:

<h5 style="color:black;">Monthly Earnings <small style="font-size:12px;">(gross profit)</small></h5>

    <br>

    <?php
// Set the timezone to the appropriate timezone
date_default_timezone_set('Europe/London');

// Get the current date
$current_date = date('Y-m-d');

// Determine the start and end dates for the current period
$current_month = date('n', strtotime($current_date));
$current_year = date('Y', strtotime($current_date));

// Adjust the start date to be the 12th of the current month
$start_date_1st = date('Y-m-d', strtotime("$current_year-$current_month-12"));

// Determine the number of days in the current month
$days_in_month = date('t', strtotime($current_date));

// Adjust the end date based on the number of days in the current month
if (date('j', strtotime($current_date)) < 12) {
// If the current date is before the 12th, set the end date to the 11th of the current month
$end_date_1st = date('Y-m-d', strtotime("11-$current_month-$current_year"));
} else {
// If the current date is on or after the 12th, set the end date to the 11th of the next month
if ($current_month == 12) {
    $next_month = 1;
    $next_year = $current_year + 1;
} else {
    $next_month = $current_month + 1;
    $next_year = $current_year;
}
$end_date_1st = date('Y-m-d', strtotime("11-$next_month-$next_year"));
}

// Format the start and end dates as "Month, day"
$start_date_formatted_1st = date('F, jS', strtotime($start_date_1st));
$end_date_formatted_1st = date('F, jS', strtotime($end_date_1st));
$today_formatted = date('F, jS', strtotime($current_date));

if ($today_formatted == $start_date_formatted_1st) {
    $start_date_formatted_1st = "Today";
}

// Query the database for orders within the current period
$howMuch = $dbh->prepare("SELECT * FROM `orders` WHERE `date` BETWEEN :start_date_1st AND :end_date_1st ORDER BY `id`");
$howMuch->bindParam(':start_date_1st', $start_date_1st);
$howMuch->bindParam(':end_date_1st', $end_date_1st);
$howMuch->execute();
$seeSold = $howMuch->fetchAll();

// Calculate the total profit for the current period
$Money1st = 0;
foreach ($seeSold as $firstResult) {
    $secondResult = $firstResult['price'] * $firstResult['quantity'];
    $Money1st += $secondResult;
}
?>

<h6 style="color:black;margin-top:0;"><?php echo $start_date_formatted_1st . ' - ' . $end_date_formatted_1st;?></h6>

<?php if (!empty($seeSold)):?>
<h3 style="color:black;">£<?php echo number_format(round($Money1st, 2), 2);?></h3>
<?php else: ?>
<h3 style="color:black;">£0.00</h3>
<?php endif ?>

Previous Month:

<?php
// Set the timezone to the appropriate timezone
date_default_timezone_set('Europe/London');

// Get the current date
$current_date = new DateTime();

// Determine the start and end dates for the previous period
$current_month = $current_date->format('n');
$current_year = $current_date->format('Y');

// Calculate the start and end dates for the previous period
if ($current_month == 1) {
    $start_date_2nd = new DateTime("12-12-" . ($current_year - 1));
    $end_date_2nd = new DateTime("11-$current_month-$current_year");
} else {
    $start_date_2nd = new DateTime("12-" . ($current_month - 1) . "-$current_year");
    $end_date_2nd = new DateTime("11-$current_month-$current_year");
}

// Format the start and end dates as "F jS"
$start_date_formatted_2nd = $start_date_2nd->format('F jS');
$end_date_formatted_2nd = $end_date_2nd->format('F jS');

// Query the database for orders within the previous period
$howMuchSecond = $dbh->prepare("SELECT * FROM `orders` WHERE `date` BETWEEN :start_date_2nd AND :end_date_2nd ORDER BY `id`");
$howMuchSecond->bindParam(':start_date_2nd', $start_date_2nd->format('Y-m-d'));
$howMuchSecond->bindParam(':end_date_2nd', $end_date_2nd->format('Y-m-d'));
$howMuchSecond->execute();
$secondMonthAmount = $howMuchSecond->fetchAll();

// Calculate the total profit for the previous period
$moneyStart = 0;
foreach ($secondMonthAmount as $secondMonthResult) {
    $secondPartResult = $secondMonthResult['price'] * $secondMonthResult['quantity'];
    $moneyStart += $secondPartResult;
}
?>

<h6 style="color:black;margin-top:0;"><?php echo $start_date_formatted_2nd . ' - ' . $end_date_formatted_2nd;?></h6>

<?php if ($moneyStart > 0):?>
<h3 style="color:black;">£<?php echo number_format(round($moneyStart, 2), 2);?></h3>
<?php else: ?>
<h3 style="color:black;">£0.00</h3>
<?php endif ?>

3rd Month Back:

<?php
// Set the timezone to the appropriate timezone
date_default_timezone_set('Europe/London');

// Get the current date
$current_date = date('Y-m-d');

// Determine the start and end dates for the previous period
$current_month = date('n', strtotime($current_date));
$current_year = date('Y', strtotime($current_date));
if (date('d', strtotime($current_date)) <= 11) {
    $start_date_3rd = date('Y-m-d', strtotime("12-$current_month-$current_year -3 months"));
    $end_date_3rd = date('Y-m-d', strtotime("11-$current_month-$current_year -2 months"));
} else {
    $start_date_3rd = date('Y-m-d', strtotime("12-$current_month-$current_year -2 months"));
    $end_date_3rd = date('Y-m-d', strtotime("11-$current_month-$current_year -1 month"));
}

// Format the start and end dates as "Month, day"
$start_date_formatted_3rd = date('F, jS', strtotime($start_date_3rd));
$end_date_formatted_3rd = date('F, jS', strtotime($end_date_3rd));

// Query the database for orders within the previous period
$month3rdback = $dbh->prepare("SELECT * FROM `orders` WHERE `date` BETWEEN :start_date_3rd AND :end_date_3rd ORDER BY `id`");
$month3rdback->bindParam(':start_date_3rd', $start_date_3rd);
$month3rdback->bindParam(':end_date_3rd', $end_date_3rd);
$month3rdback->execute();
$see3rdMonth = $month3rdback->fetchAll();

// Calculate the total profit for the previous period
$readySet = 0;
foreach ($see3rdMonth as $month3rdbackResult) {
    $month3rdbackSecondRes = $month3rdbackResult['price'] * $month3rdbackResult['quantity'];
    $readySet += $month3rdbackSecondRes;
}
?>

<h6 style="color:black;margin-top:0;"><?php echo $start_date_formatted_3rd . ' - ' . $end_date_formatted_3rd;?></h6>

<?php if (!empty($see3rdMonth)): ?>
<h3 style="color:black;">£<?php echo number_format(round($readySet, 2), 2);?></h3>
<?php else: ?>
<h3 style="color:black;">£0.00</h3>
<?php endif ?>

Answer 1

I know you solved it out by CASTing, but I figured I would put this solution in here as you can replace your PHP code to find the sum with a SQL query:

SELECT 
    COUNT(id) as number_of_orders, 
    ROUND(SUM(price * quantity),2) as gross_profit 
FROM orders 
    WHERE date BETWEEN CAST(:start) AND CAST(:end);

Then chuck that in a function for re-usability:

function getGrossProfit($dbh, $startDate, $endDate)
{
    $query = $dbh->prepare("SELECT COUNT(id) as number_of_orders, ROUND(SUM(price * quantity),2) as gross_profit FROM orders WHERE date BETWEEN CAST(:start) AND CAST(:end)");
    $query->bindParam(':start', $startDate);
    $query->bindParam(':end', $endDate);
    $query->execute();
    return $query->fetchAll();
}