<?php namespace App\Models;
/**
 * File			: AntrianModel.php
 * Description  : Model untuk halaman antrian
 * Created By	: Hendi
 * Created Date : 16 Maret 2021
 * Last Update  : 13 April 2021
**/
use CodeIgniter\Model;
 
class AntrianModel extends Model
{
    protected $table = 'queue';
	protected $primaryKey = 'queue_id';
    protected $allowedFields = ['date', 'transaction_id', 'queue_number', 'menu_id', 'qty', 'start', 'finish', 'status', 'user_id', 'created_at', 'updated_at', 'deleted_at'];
	# bukan hapus data permanen tapi saat call method delete hanya ngisi deleted_at saja
	protected $useSoftDeletes = true;
	# field created_at, updated_at, deleted_at akan diisi otomatis saat call method insert, update, delete
	protected $useTimestamps = true;
	protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';
	
	# fungsi untuk cek no antrian
	public function cekNoAntrian($post = []) {
		$sql = "
				select count(queue_number) as jumlah from antrian
				where DATE_FORMAT(date, '%Y-%m-%d') = '".substr($post['date'], 0, 10)."'
				and queue_number = ".$post['queue_number']." and menu_id = '".$post['menu_id']."'
			   ";
		return $this->db->query($sql)->getRow()->jumlah;
	}
    # hilangkan dari monitor otomatis jika out/finish lebih dari 30 detik
	public function hideAntrian()
	{
	    $sekarang = date('Y-m-d H:i:s');
	    $sql = "
	            UPDATE queue SET status = 3, updated_at = '".$sekarang."'
	            WHERE status = 2 AND TIME_TO_SEC(TIMEDIFF(STR_TO_DATE('".$sekarang."', '%Y-%m-%d %H:%i:%s'), finish)) > 30
	           ";
	    $this->db->query($sql);
        return $this->db->affectedRows();
	}
    # fungsi untuk mendapatkan no antrian di transaksi yang belum diinput ke table antrian (queue)
    public function getAntrianTrx()
    {
        $sekarang = date('Y-m-d');
        $sql = "
	            SELECT a.transaction_id, a.date, a.queue_number, a.total_paid  
                FROM transaction a
                WHERE DATE_FORMAT(a.date, '%Y-%m-%d') = '".$sekarang."' AND a.transaction_id NOT IN (SELECT b.transaction_id FROM queue b) 
	           ";
        return $this->db->query($sql)->getResultArray();
    }
    # fungsi untuk mendapatkan rincian transaksi yang belum diinput ke table antrian (queue)
    public function getDetailAntrianTrx($qno)
    {
        $sekarang = date('Y-m-d');
        $sql = "
	            SELECT a.* FROM transaction_detail a
                INNER JOIN `transaction` b
                ON a.transaction_id = b.transaction_id
                WHERE b.queue_number = ".$qno." 
                AND DATE_FORMAT(b.date, '%Y-%m-%d') = '".$sekarang."' AND b.transaction_id NOT IN (SELECT c.transaction_id FROM queue c)  
	           ";
        return $this->db->query($sql)->getResultArray();
    }   
    # fungsi untuk mengupdate status antrian
    public function updateStatusAntrian($post)
    {
		$id = $post['id'];
		$status = $post['status'];
		$sekarang = date('Y-m-d H:i:s');
		
		if ($status == '0') 
		{
		    $sql = "UPDATE queue SET status = ".$status.", start = null, finish = null, updated_at = '".$sekarang."' WHERE queue_id IN (".$id.")";
		}
		elseif ($status == '1') 
		{
		    $sql = "UPDATE queue SET status = ".$status.", start = '".$sekarang."', updated_at = '".$sekarang."' WHERE queue_id IN (".$id.")";
		}
		elseif($status == '2')
		{
		    $sql = "UPDATE queue SET status = ".$status.", finish = '".$sekarang."', updated_at = '".$sekarang."' WHERE queue_id IN (".$id.")";
		}
		elseif($status == '3')
		{
		    $sql = "UPDATE queue SET status = ".$status.", updated_at = '".$sekarang."' WHERE queue_id IN (".$id.")";
        }
	
		 $this->db->query($sql);
        return $this->db->affectedRows();
    }
}
