Am failing to retrieve DAILY SALES in my codeigniter 4 pos system after mid night when query between two dates.

The POS system is working in the BAR/Club where their start time is 14:00 and end time is 03:00 (next day).

My current code only shows daily sales upto midnight. After mid night my opening hours change to new day returning nothing because no sales by then.

Here is what I tried

MODEL

//Get Daily Sales For Tenant ID - By Specific Opening/Closing time
    public function getdailySalesByTenant($t, $ot, $ct)
    {
        $builder = $this->db->table('orders as o');
        $builder->select('o.*, u.first_name as waiter');
        $builder->join('users as u', 'u.id = o.sold_by', 'left' );
        $builder->where('o.tenant_id', $t);
        $builder->where('o.created_at >=', $ot); 
        $builder->where('o.created_at <=', $ct);
        return $results = $builder->get()->getResult();
    }

CONTROLLER

//Daily Sales
    public function getdailySales()
    {
        $t = $this->settingsModel->where('user_id', $this->tenantId->tenant_id)->first();
        $ot = date('Y-m-d H:i:s', strtotime($t['opening_time']));
        $ct = date('Y-m-d H:i:s', strtotime($t['closing_time'].'  1 day'));
        
        $data = $this->transactionsModel->getdailySalesByTenant($this->tenantId->tenant_id, $ot, $ct);
        $response = [
                'success' => true,
                'data' => $data,
        ];
        return $this->response->setJSON($response);
    }

I want to record daily sales from 2023-01-05 14:00:00 to 2023-01-06 03:00:00

CodePudding user response：

Instead of:❌

// ...
$ot = date('Y-m-d H:i:s', strtotime($t['opening_time']));
$ct = date('Y-m-d H:i:s', strtotime($t['closing_time'].'  1 day'));
// ...

Use this:✅

$ot = (new DateTime($t['opening_time']))->format("Y-m-d H:i:s");

$ct = (function () use ($t) {
    $openingTime = strtotime($t['opening_time']);
    $closingTime = strtotime($t['closing_time']);

    return ($closingTime >= $openingTime)
        ? (new DateTime($t['closing_time']))->format("Y-m-d H:i:s")
        : (new DateTime($t['closing_time']))->modify(" 1 DAY")->format("Y-m-d H:i:s");
})();

Reference(s):

1. The DateTime class
2. Adding one day to a date

CodePudding user response：

When I use if --- else. it worked. Below is the Controller.

//Daily Sales
public function getdailySales()
{
    $t = $this->settingsModel->where('user_id', $this->tenantId->tenant_id)->first();
    
    $opening_hour = $t['opening_time'];
    $hour=date('H'); //24hr clock.
    if($hour < $opening_hour) {
        $ot = date('Y-m-d H:i:s', strtotime($t['opening_time'].'- 1 day'));
        $ct = date('Y-m-d H:i:s', strtotime($t['closing_time'].'  1 day'));
    } else {
        $ot = date('Y-m-d H:i:s', strtotime($t['opening_time']));
        $ct = date('Y-m-d H:i:s', strtotime($t['closing_time'].'  1 day'));
    }
    
    $data = $this->transactionsModel->getdailySalesByTenant($this->tenantId->tenant_id, $ot, $ct);
    $response = [
            'success' => true,
            'data' => $data,
    ];
    return $this->response->setJSON($response);
}