<?php

namespace App\Models;

use CodeIgniter\Model;

class ChartIncome extends Model
{
    protected $table = 'orders_item'; // Nama tabel

    public function getTotalIncomeMonthly($year)
    {
        return $this->select("SUM(orders_item.amount) as amount, DATE_FORMAT(tanggal_order, '%M') as month")
        ->join('orders', 'orders.id = orders_item.order_id')
        ->join('products', 'products.id = orders_item.product_id')
        ->where('YEAR(orders.tanggal_order)', $year)
        ->where('orders.status_order', 2)
        ->where('type', 0)
        ->groupBy('month')
        ->orderBy('MONTH(tanggal_order)', 'ASC')
        ->get()
        ->getResult();
    }

    public function getTotalOrderperBulan($year)
    {
        return $this->select("SUM(orders_item.totalqc) as totalqc, SUM(orders_item.qty) as qty, SUM(orders_item.totalreject) as reject, DATE_FORMAT(orders.tanggal_order, '%M') as month")
                    ->join('orders', 'orders.id = orders_item.order_id')
                    ->join('products', 'products.id = orders_item.product_id')
                    ->where('YEAR(orders.tanggal_order)', $year)
                    ->where('orders.status_order', 2)
                    ->where('type', 0)
                    ->groupBy('month')
                    ->orderBy('MONTH(orders.tanggal_order)', 'ASC')
                    ->get()
                    ->getResult();
    }

    public function getRecentOrders()
    {
        $tenDaysAgo = date('Y-m-d', strtotime('-10 days'));

        // Mendapatkan data pesanan terbaru dalam 10 hari terakhir
        $recentOrders = $this->db->table('orders_item')
            ->select('DATE(orders.tanggal_order) as tanggal_order, SUM(orders_item.qty) as qty')
            ->join('orders', 'orders.id = orders_item.order_id')
            ->join('products', 'products.id = orders_item.product_id')
            ->where('orders.status_order', 2)
            ->where('type', 0)
            ->where('orders.tanggal_order >=', $tenDaysAgo)
            ->groupBy('tanggal_order')
            ->orderBy('tanggal_order', 'ASC')
            ->get()
            ->getResultArray();

        // Membuat array dengan default nilai 0 untuk 10 hari terakhir
        $data = [];
        for ($i = 0; $i < 10; $i++) {
            $date = date('Y-m-d', strtotime("-$i days"));
            $data[$date] = 0;
        }

        // Memproses hasil query dan mengisi nilai yang sesuai ke dalam array
        foreach ($recentOrders as $order) {
            $data[$order['tanggal_order']] = intval($order['qty']);
        }

        // Mengembalikan data yang telah diproses
        return array_map(function($key, $value) {
            return ['tanggal_order' => $key, 'qty' => $value];
        }, array_keys($data), array_values($data));
 
    }

    public function dataRecentOrders()
    {
        $tenDaysAgo = date('Y-m-d', strtotime('-10 days'));
    
        // Mendapatkan data pesanan terbaru dalam 10 hari terakhir
        $recentOrders = $this->db->table('orders_item')
            ->select('DATE(orders.tanggal_order) as tanggal_order, SUM(orders_item.qty) as qty')
            ->join('orders', 'orders.id = orders_item.order_id')
            ->join('products', 'products.id = orders_item.product_id')
            ->where('orders.status_order', 2)
            ->where('type', 0)
            ->where('orders.tanggal_order >=', $tenDaysAgo)
            ->groupBy('tanggal_order')
            ->orderBy('tanggal_order', 'ASC')
            ->get()
            ->getResultArray();
    
        // Membuat array dengan default nilai 0 untuk 10 hari terakhir
        $data = [];
        for ($i = 0; $i < 10; $i++) {
            $date = date('Y-m-d', strtotime("-$i days"));
            $data[$date] = 0;
        }
    
        // Memproses hasil query dan mengisi nilai yang sesuai ke dalam array
        foreach ($recentOrders as $order) {
            $data[$order['tanggal_order']] = intval($order['qty']);
        }
    
        $totalOrdersLast10Days = array_sum($data);
    
        // Menghitung total pesanan dari 20 hari terakhir
        $twentyDaysAgo = date('Y-m-d', strtotime('-20 days'));
        $OrdersLast20Days = $this->db->table('orders_item')
            ->select('DATE(orders.tanggal_order) as tanggal_order, SUM(orders_item.qty) as qty')
            ->join('orders', 'orders.id = orders_item.order_id')
            ->join('products', 'products.id = orders_item.product_id')
            ->where('orders.status_order', 2)
            ->where('type', 0)
            ->where('orders.tanggal_order >=', $twentyDaysAgo)
            ->groupBy('tanggal_order')
            ->orderBy('tanggal_order', 'ASC')
            ->get()
            ->getResultArray();
    
        $totalOrdersLast20Days = 0; // Inisialisasi total pesanan selama 20 hari terakhir
    
        // Menghitung total pesanan selama 20 hari terakhir dari hasil query
        foreach ($OrdersLast20Days as $order) {
            $totalOrdersLast20Days += intval($order['qty']);
        }
    
        // Menghitung perbandingan persentase
        $percentageChange = 0;
        if ($totalOrdersLast20Days > 0) {
            $percentageChange = (($totalOrdersLast10Days - $totalOrdersLast20Days) / $totalOrdersLast20Days) * 100;
        }
    
        return [
            'total_orders_last_10_days' => $totalOrdersLast10Days,
            'total_orders_last_20_days' => $totalOrdersLast20Days,
            'percentage_change' => $percentageChange
        ];
    }
    
    
    


}
