1

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

Shadow
  • 33,525
  • 10
  • 51
  • 64
lwegaba
  • 121
  • 1
  • 6
  • if time < 3 subtract 4 from datetime.. – P.Salmon Jan 05 '23 at 07:05
  • @P.Salmon thanks for the reply. Could you please provide more details on how i can fit that in the controller. Thank you – lwegaba Jan 05 '23 at 08:27
  • What does `$t['closing_time']` look like? – steven7mwesigwa Jan 05 '23 at 08:31
  • hi @steven7mwesigwa $t['closing_time'] is set in settings at is 14:00 (24hr format – lwegaba Jan 05 '23 at 08:38
  • hi @steven7mwesigwa $t['closing_time'] is set in settings at is 14:00 (24hr format – lwegaba Jan 05 '23 at 08:38
  • If your `$t['closing_time']` lucks the date segment, how do you set the *`2023-01-06 03:00:00`* upper bound then? – steven7mwesigwa Jan 05 '23 at 08:43
  • In addition, how do you know that `$t['closing_time'] = '14:00'` refers to the current day or the next day? – steven7mwesigwa Jan 05 '23 at 08:46
  • Good question Steven. On each sale. I record the DATETIME as you saw in the Model "created_at". Because i want only the Daily sales report for that particular USER or Client. I have decided to use his WORKING Hours and ADD it on say TODAY's date as you can see here date('Y-m-d H:i:s', strtotime($t['opening_time']));. this will print 2023-01-05 14:00:00 if you try it out. Do not forget that $t['opening_time'] is SET already in user's settings and i pick it with $t = $this->settingsModel->where('user_id', $this->tenantId->tenant_id)->first();. – lwegaba Jan 05 '23 at 09:09

2 Answers2

0

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
steven7mwesigwa
  • 5,701
  • 3
  • 20
  • 34
  • I replaced as you advised. It returns blank. But no error. However tried to do print_r($ct); and it was giving me "2023-01-06 04:00:00". Could it be that it is choosing only the last statement? – lwegaba Jan 05 '23 at 10:56
0

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);
}
lwegaba
  • 121
  • 1
  • 6