<?php

namespace App\Models;

use CodeIgniter\Model;


class Orders extends Model
{
    protected $table = 'orders';
    protected $primaryKey = 'id';
    protected $allowedFields = ['bill_no', 'customer_name', 'customer_address', 'customer_phone', 'date_time', 'gross_amount', 'service_charge_rate', 'service_charge', 'vat_charge_rate', 'vat_charge', 'net_amount', 'discount', 'paid_status', 'user_id', 'payment_method', 'deadline', 'notes', 'tanggal_order', 'status_produksi', 'status_order', 'type_produksi', 'totalsiqc', 'totalsipcs', 'totalsiqty', 'cat_order', 'cat_order2', 'tgl_selesai', 'notes_kirim', 'images', 'tgl_kirim', 'layanan_kirim', 'resi', 'status_kirim', 'client_key'];

    public function insertOrder($data)
    {
        return $this->insert($data);
    }


    public function updateOrder($orderId, $data)
    {
        return $this->update($orderId, $data);
    }

    public function getOrderByBillNo($bill_no)
    {
        return $this->where('bill_no', $bill_no)
                    ->first();
    }

    public function getDataby($kode)
    {

            if ($kode) {
                return $this->where('bill_no', $kode)
                ->first();
            } else {
                return $this->orderBy('id', 'DESC')->findAll();
            }

    }

    public function getDatabyid($kode)
    {

            if ($kode) {
                return $this->where('id', $kode)
                ->first();
            } else {
                return $this->orderBy('id', 'DESC')->findAll();
            }

    }

    public function countFiltered($postData)
    {
        $model = $this->select('COUNT(*) as count');
    
        if (isset($postData['start_date'])) {
            $model->where('date_time >=', $postData['start_date']);
        }
    
        if (isset($postData['end_date'])) {
            $model->where('date_time <=', $postData['end_date']);
        }
    
        $search = $postData['search']['value'] ?? '';
    
        if (!empty($search)) {
            $model->groupStart();
            $model->like('bill_no', $search, 'both');
            $model->orLike('customer_name', $search, 'both');
            $model->orLike('net_amount', $search, 'both');
            $model->orLike('paid_status', $search, 'both');
            $model->orLike('date_time', $search, 'both');
            $model->groupEnd();
        }
    
        return $model->get()->getRow()->count;
    }



    public function fimeee($limit, $offset, $search, $filter)
    {
        $builder = $this->db->table('orders o');
        $builder->select('o.id, o.tanggal_order, o.customer_name, o.status_order, o.bill_no, o.notes_kirim, o.status_produksi, o.cat_order2, oi.details, oi.totalqc, oi.qty, p.name as product_name');
        $builder->where('o.status_order', 2);
        $builder->join('orders_item oi', 'oi.order_id = o.id', 'inner');
        $builder->join('products p', 'p.id = oi.product_id', 'inner');
        $builder->groupBy('o.id');
    
        if (!empty($search)) {
            $builder->groupStart();
            $builder->like('o.customer_name', $search);
            $builder->orLike('o.bill_no', $search);
            $builder->orLike('o.status_produksi', $search);
            $builder->orLike('oi.details', $search);
            $builder->orLike('p.name', $search);
            $builder->groupEnd();
        }
        
        if (!empty($filter)) {
            $builder->like('o.status_produksi', $filter);
        }
        
        // Pastikan klausa orderBy() ditempatkan setelah klausa where() dan like()
        $builder->orderBy('o.tanggal_order', 'desc');
        $builder->limit($limit, $offset);
        return $query = $builder->get();

    }    
    public function hitungg($search, $filter)
        {
            $builder = $this->db->table('orders o');
            $builder->select('o.id, o.tanggal_order, o.customer_name, o.status_order, o.bill_no, o.status_produksi, oi.details, oi.totalqc, oi.qty, p.name as product_name');
            $builder->where('o.status_order', 2);
            $builder->join('orders_item oi', 'oi.order_id = o.id', 'inner');
            $builder->join('products p', 'p.id = oi.product_id', 'inner');
            $builder->groupBy('o.id');
        
            if (!empty($search)) {
                $builder->groupStart();
                $builder->like('o.customer_name', $search);
                $builder->orLike('o.bill_no', $search);
                $builder->orLike('o.status_produksi', $search);
                $builder->orLike('oi.qty', $search);
                $builder->orLike('oi.details', $search);
                $builder->orLike('p.name', $search); // Tambahkan pencarian berdasarkan p.name
                $builder->groupEnd();
            }
            if (!empty($filter)) {
                $builder->like('o.status_produksi', $filter);
            }
        
            return $builder->countAllResults();
        }


    public function findme($limit, $offset, $search, $filter)
    {
        $builder = $this->db->table('orders o');
        $builder->select('o.id, o.tanggal_order, o.customer_name, o.status_order, o.bill_no, o.notes_kirim, o.status_produksi, o.cat_order2, oi.details, oi.totalqc, oi.qty, p.name as product_name');
        $builder->where('o.status_order', 2);
        $builder->join('orders_item oi', 'oi.order_id = o.id', 'inner');
        $builder->join('products p', 'p.id = oi.product_id', 'inner');
        $builder->groupBy('o.id');
    
        if (!empty($search)) {
            $builder->groupStart();
            $builder->like('o.customer_name', $search);
            $builder->orLike('o.bill_no', $search);
            $builder->orLike('o.status_produksi', $search);
            $builder->orLike('oi.details', $search);
            $builder->orLike('p.name', $search);
            $builder->groupEnd();
        }
        
        if (!empty($filter)) {
            $builder->like('o.status_produksi', $filter);
        }
        
        // Pastikan klausa orderBy() ditempatkan setelah klausa where() dan like()
        $builder->orderBy('o.tanggal_order', 'desc');
        $builder->limit($limit, $offset);
        $query = $builder->get();
    
        $orders = array();
        foreach ($query->getResultArray() as $row) {
            $order_id = $row['id'];
            if (!isset($orders[$order_id])) {
                $orders[$order_id] = array(
                    'id' => $row['id'],
                    'tanggal_order' => $row['tanggal_order'],
                    'customer_name' => $row['customer_name'],
                    'notes_kirim' => $row['notes_kirim'],
                    'bill_no' => $row['bill_no'],
                    'status_produksi' => $row['status_produksi'],
                    'cat_order2' => $row['cat_order2'],
                    'items' => array()
                );
            }
            $builder = $this->db->table('orders_item'); // Objek Query Builder untuk tabel 'orders_item'
            $items_query = $builder->getWhere(['order_id' => $order_id]);
            foreach ($items_query->getResultArray() as $item_row) {
                $product_id = $item_row['product_id']; // Mengambil product_id dari $item_row
                $product_query = $this->db->table('products')->where('id', $product_id)->get()->getRowArray();
        
                if ($product_query && $product_query['type'] != 1) {
                    $orders[$order_id]['items'][] = array(
                        'details' => $item_row['details'], // Mengambil details dari $item_row
                        'qty' => $item_row['qty'], // Mengambil qty dari $item_row
                        'totalqc' => $item_row['totalqc'], // Mengambil totalqc dari $item_row
                        'product_name' => $product_query['name']
                    );
                }
            }
        }
    
        return $orders;
    }
    


     public function getOrdersWithSummary($clientKey = null)
     {
         $query = $this->select('bill_no, customer_name, net_amount, tanggal_order, paid_status')
                       ->orderBy('tanggal_order', 'DESC');
 
         if ($clientKey) {
             $query->where('client_key', $clientKey);
         }
 
         return $query->findAll();
     }
}
