<?php 
namespace App\Models;
use CodeIgniter\Model;
class HomeModel extends Model
{

    public function donordetails($where) {
        
        $totalDonors = $this->db->table('donor')->where($where)->countAllResults();

        $activeDonors = $this->db->table('donor')->select('donor.*, users.branch_id,users.business_id as users_business_id')
                                 ->join('users', 'users.user_id = donor.created_by AND users.business_id = donor.business_id', 'left')
                                 ->where($where)->where('donor.isactive',1)->countAllResults();
    
        $where['DATE(donor.created_on)'] = date('Y-m-d');
        $todayDonors = $this->db->table('donor')->select('donor.*, users.branch_id,users.business_id as users_business_id')
                                ->join('users', 'users.user_id = donor.created_by AND users.business_id = donor.business_id', 'left')
                                ->where($where)->where('donor.isactive',1)->countAllResults();
    
        if ($totalDonors > 0) {
            $final['percentage'] = ($activeDonors / $totalDonors) * 100;
        } else {
            $final['percentage'] = 0;
        }
        
        $final['total'] = $totalDonors;
        $final['active'] = $activeDonors;
        $final['today'] = $todayDonors;
        
        return $final;
    }

    public function getbranchid($bwhere){
        $query = $this->db->table('users');
        $result = $query->select('branch_id')->where($bwhere)->get()->getRow();
        return $result ? (int)$result->branch_id : 0;
    }
    public function dashboard_account($where){
        $query = $this->db->table('users')
        ->select('users.user_id,receipt.receipt_header,SUM(receipt.amount) AS total_amount')
        ->select("CONCAT_WS(' ', users.first_name, users.last_name) AS full_name")
        ->join('receipt', 'receipt.created_by = users.user_id', 'left')
        ->where('users.isactive', 1)
        ->where('receipt.receipt_header', 'Receipt')
        ->where($where)
        ->like('users.role', 'volunteer')
        ->groupby('users.user_id, full_name');
        $result = $query->get()->getResult();
        // echo $this->db->getLastQuery()->getQuery();die;

        $total_amount_today = $this->db->table('receipt')
        ->select('SUM(amount) AS total_amount_today')
        ->join('users', 'receipt.created_by = users.user_id', 'left')
        ->where('receipt.receipt_header', 'Receipt')
        ->where($where)
        ->where('DATE(receipt.created_on) = CURDATE()')
        ->get()->getRow();
        $today = ($total_amount_today) ? $total_amount_today->total_amount_today : 0;
        
        

        $total_amount_current_month = $this->db->table('receipt')
        ->select('SUM(amount) AS total_amount_current_month')
        ->join('users', 'receipt.created_by = users.user_id', 'left')
        ->where('receipt.receipt_header', 'Receipt')
        ->where($where)
        ->where('YEAR(receipt.created_on)', date('Y'))
        ->where('MONTH(receipt.created_on)', date('m'))
        ->get()->getRow();
        $month = ($total_amount_current_month) ? $total_amount_current_month->total_amount_current_month : 0;

        $total_amount_current_financial_year = $this->db->table('receipt')
            ->select('SUM(amount) AS total_amount_current_financial_year')
            ->join('users', 'receipt.created_by = users.user_id', 'left')
            ->where('receipt.receipt_header', 'Receipt')
            ->where($where)
            ->where('receipt.created_on >=', date('Y-04-01'))
            ->where('receipt.created_on <', date('Y-04-01', strtotime('+1 year')))
            ->get()->getRow();
        $financial_year = ($total_amount_current_financial_year) ? $total_amount_current_financial_year->total_amount_current_financial_year : 0;
        

        $final['volunter'] = $result;
        $final['month'] = (int)$month;
        $final['today'] = (int)$today;
        $final['year'] = (int)$financial_year;
        return $final;
    }

    public function dashboard_volunteer($where){
        $common_query = $this->db->table('users')
            ->select('users.user_id, SUM(receipt.amount) AS total_amount')
            ->select("CONCAT_WS(' ', users.first_name, users.last_name) AS full_name")
            ->join('receipt', 'receipt.created_by = users.user_id', 'left')
            ->where($where)
            ->like('users.role', 'volunteer')
            ->groupby('users.user_id');
    
        $today_collection = clone $common_query;
        $today_collection = $today_collection->where('DATE(receipt.created_on)', date('Y-m-d'))->get()->getRow();
        $final['today_collection'] = ($today_collection) ? (int)$today_collection->total_amount : 0;
    
        $overall_collection = clone $common_query;
        $overall_collection = $overall_collection->get()->getRow();
        // echo $this->db->getLastQuery()->getQuery();die;
        $final['overall_collection'] = ($overall_collection) ? (int)$overall_collection->total_amount : 0;
    
        $settlement_today = clone $common_query;
        $settlement_today = $settlement_today->where('DATE(receipt.created_on)', date('Y-m-d'))
                                             ->where('receipt.receipt_header', 'Receipt')
                                             ->get()->getRow();
        $final['settlement_today'] = ($settlement_today) ? (int)$settlement_today->total_amount : 0;
    
        $settlement_overall = clone $common_query;
        $settlement_overall = $settlement_overall->where('receipt.receipt_header', 'Receipt')->get()->getRow();
        $final['settlement_overall'] = ($settlement_overall) ? (int)$settlement_overall->total_amount : 0;
    
        return $final;
    }
    
}