<?php

use Mpdf\Tag\P;

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Report extends Admin_Controller
{
    
    public function __construct()
    {
        parent::__construct();
        $this->load->model('report_model');
        $this->load->model('finance_report_model');
        $this->load->model('invoice_model');
        $this->load->model('estimates_model');
        $this->load->model('proposal_model');
        $this->load->model('transactions_model');
        
        // $this->load->helper('ckeditor');
        // $this->data['ckeditor'] = array(
        //     'id' => 'ck_editor',
        //     'path' => 'asset/js/ckeditor',
        //     'config' => array(
        //         'toolbar' => "Full",
        //         'width' => "99.8%",
        //         'height' => "400px"
        //     )
        // );
    }

    protected function decodePermission($permission)
    {
        if (!is_string($permission) || trim($permission) === '' || $permission === 'all') {
            return array();
        }

        $decoded_permission = json_decode($permission);

        return !empty($decoded_permission) ? $decoded_permission : array();
    }

    private function build_finance_summary($column, array $where = array())
    {
        $today = date('Y-m-d');
        $first_day_month = date('Y-m-01');
        $this_week_start = date('Y-m-d', safe_strtotime('previous sunday'));
        $before_30_days = date('Y-m-d', safe_strtotime('today - 30 days'));

        return $this->finance_report_model->build_transaction_summary($column, $today, $first_day_month, $this_week_start, $before_30_days, $where);
    }

    private function build_fee_graph($type, $amount_column, $category_table, $category_key, $category_label)
    {
        $end_date = date('Y-m-d');
        $start_date = date('Y-m-d', safe_strtotime('today - 29 days'));
        $daily_series = $this->finance_report_model->get_transaction_daily_series($type, $start_date, $end_date, $amount_column);

        $labels = array();
        $daily_totals = array();
        foreach ($daily_series as $date => $amount) {
            $labels[] = date('M d', safe_strtotime($date));
            $daily_totals[] = round((float) $amount, 2);
        }

        $category_rows = $this->db
            ->select($category_table . '.' . $category_label . ' AS label, COALESCE(SUM(tbl_transactions.' . $this->db->escape_identifiers($amount_column) . '),0) AS total_amount', false)
            ->from('tbl_transactions')
            ->join($category_table, $category_table . '.' . $category_key . ' = tbl_transactions.category_id', 'left')
            ->where('tbl_transactions.type', $type)
            ->group_by('tbl_transactions.category_id')
            ->order_by('total_amount', 'DESC')
            ->limit(8)
            ->get()
            ->result();

        $category_labels = array();
        $category_totals = array();
        if (!empty($category_rows)) {
            foreach ($category_rows as $row) {
                $category_labels[] = !empty($row->label) ? $row->label : lang('undefined');
                $category_totals[] = round((float) $row->total_amount, 2);
            }
        }

        return array(
            'range_label' => date('M d', safe_strtotime($start_date)) . ' - ' . date('M d, Y', safe_strtotime($end_date)),
            'trend_total' => array_sum($daily_totals),
            'trend_peak' => !empty($daily_totals) ? max($daily_totals) : 0,
            'trend_average' => !empty($daily_totals) ? array_sum($daily_totals) / count($daily_totals) : 0,
            'trend_series' => array(
                array(
                    'name' => $type == 'Income' ? 'Fee Income' : 'Fee Expense',
                    'data' => $daily_totals,
                    'categories' => $labels,
                ),
            ),
            'category_series' => array(
                array(
                    'name' => 'Fees',
                    'data' => $category_totals,
                    'categories' => $category_labels,
                ),
            ),
        );
    }
    
    public function account_statement()
    {
        $data['title'] = lang('account_statement');
        $data['account_id'] = $this->input->post('account_id', TRUE);
        if (!empty($data['account_id'])) {
            $data['report'] = TRUE;
            $data['start_date'] = $this->input->post('start_date', TRUE);
            $data['end_date'] = $this->input->post('end_date', TRUE);
            $data['transaction_type'] = $this->input->post('transaction_type', TRUE);
            $data['all_transaction_info'] = $this->get_account_statement($data['account_id'], $data['start_date'], $data['end_date'], $data['transaction_type']);
        }
        $data['subview'] = $this->load->view('admin/report/account_statement', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function incomeList($id = null, $type = null)
    {
        if ($this->input->is_ajax_request()) {
            $this->load->model('datatables');
            $this->load->model('transactions_model');
            $this->datatables->table = 'tbl_transactions';
            $this->datatables->join_table = array('tbl_accounts', 'tbl_client');
            $this->datatables->join_where = array('tbl_accounts.account_id=tbl_transactions.account_id', 'tbl_transactions.paid_by=tbl_client.client_id');
            $this->datatables->column_order = array('tbl_transactions.name', 'tbl_accounts.account_name', 'date', 'notes', 'category_id', 'tbl_client.name', 'debit', 'credit', 'amount', 'reference', 'total_balance');
            $this->datatables->column_search = array('tbl_transactions.name', 'tbl_accounts.account_name', 'date', 'notes', 'category_id', 'tbl_client.name', 'debit', 'credit', 'amount', 'reference', 'total_balance');
            $this->datatables->order = array('transactions_id' => 'desc');
            // get all invoice
            $fetch_data = $this->datatables->get_deposit($id, $type);
            
            $data = array();
            
            $edited = can_action('30', 'edited');
            $deleted = can_action('30', 'deleted');
            foreach ($fetch_data as $_key => $v_deposit) {
                $action = null;
                $can_edit = $this->transactions_model->can_action('tbl_transactions', 'edit', array('transactions_id' => $v_deposit->transactions_id));
                $can_delete = $this->transactions_model->can_action('tbl_transactions', 'delete', array('transactions_id' => $v_deposit->transactions_id));
                $account_info = $this->transactions_model->check_by(array('account_id' => $v_deposit->account_id), 'tbl_accounts');
                
                $sub_array = array();
                
				$sub_array[] = display_date($v_deposit->date);
                
                $sub_array[] = (!empty($account_info->account_name) ? $account_info->account_name : '-');
                
                $sub_array[] = $v_deposit->notes;
                
                $sub_array[] = display_money($v_deposit->amount, default_currency());
                $sub_array[] = display_money($v_deposit->credit, default_currency());
                $sub_array[] = display_money($v_deposit->debit, default_currency());
                $sub_array[] = display_money($v_deposit->total_balance, default_currency());
                
                // $action .= '<a class="btn btn-info btn-xs inline-flex h-7 w-8 items-center justify-center rounded-lg border-0 p-0 text-white shadow-sm transition hover:bg-sky-500 focus:outline-none focus:ring-2 focus:ring-sky-400/40" data-toggle="modal" data-target="#myModal" href="' . base_url() . 'admin/transactions/view_expense/' . $v_deposit->transactions_id . '" title="' . lang('view') . '"><span data-lucide="eye" class="h-4 w-4"></span></a>' . ' ';
               $action .= btn_modal('admin/transactions/view_expense/' . $v_deposit->transactions_id, '', lang('training_details'), 'lg', 'eye', array(
					'class' => 'inline-flex items-center mr-2 gap-2 rounded-lg bg-slate-900 px-3 py-2 text-xs font-semibold text-white hover:bg-slate-800 ml-1'
				));
				if (!empty($can_edit) && !empty($edited)) {
                    $action .= btn_edit('admin/transactions/create_deposit/' . $v_deposit->transactions_id) . ' ';
                }
                if (!empty($can_delete) && !empty($deleted)) {
                   
					$action .= ajax_anchor(base_url('admin/transactions/delete_deposit/' . $v_deposit->transactions_id), '', array("class" => "", "title" => lang('delete'), "data-fade-out-on-success" => "#table_" . $_key)) . ' ';
                
					}
                $sub_array[] = $action;
                $data[] = $sub_array;
            }
            render_table($data, array('type' => 'Income'));
        } else {
            redirect('admin/dashboard');
        }
    }
    
    public function expenseList($id = null, $type = null)
    {
        if ($this->input->is_ajax_request()) {
            $this->load->model('datatables');
            $this->datatables->table = 'tbl_transactions';
            $this->datatables->join_table = array('tbl_accounts', 'tbl_client');
            $this->datatables->join_where = array('tbl_accounts.account_id=tbl_transactions.account_id', 'tbl_transactions.paid_by=tbl_client.client_id');
            $this->datatables->column_order = array('tbl_transactions.name', 'tbl_accounts.account_name', 'date', 'notes', 'category_id', 'tbl_client.name', 'debit', 'credit', 'amount', 'reference', 'total_balance');
            $this->datatables->column_search = array('tbl_transactions.name', 'tbl_accounts.account_name', 'date', 'notes', 'category_id', 'tbl_client.name', 'debit', 'credit', 'amount', 'reference', 'total_balance');
            $this->datatables->order = array('transactions_id' => 'desc');
            // get all invoice
            $this->load->model('transactions_model');
            $fetch_data = $this->datatables->get_expense($id, $type);
            
            $data = array();
            
            $edited = can_action('30', 'edited');
            $deleted = can_action('30', 'deleted');
            foreach ($fetch_data as $_key => $v_expense) {
                $action = null;
                $can_edit = $this->transactions_model->can_action('tbl_transactions', 'edit', array('transactions_id' => $v_expense->transactions_id));
                $can_delete = $this->transactions_model->can_action('tbl_transactions', 'delete', array('transactions_id' => $v_expense->transactions_id));
                $account_info = $this->transactions_model->check_by(array('account_id' => $v_expense->account_id), 'tbl_accounts');
                
                $sub_array = array();
                
                $date = null;
                $date .= '<a class="text-blue-600 hover:text-blue-900 dark:text-blue-400 dark:hover:text-blue-300" href="' . base_url() . 'admin/transactions/view_expense/' . $v_expense->transactions_id . '">' . display_date($v_expense->date) . '</a>';
                $sub_array[] = $date;
                
                $sub_array[] = (!empty($account_info->account_name) ? $account_info->account_name : '-');
                $sub_array[] = $v_expense->notes;
                $sub_array[] = display_money($v_expense->amount, default_currency());
                $sub_array[] = display_money($v_expense->credit, default_currency());
                $sub_array[] = display_money($v_expense->debit, default_currency());
                $sub_array[] = display_money($v_expense->total_balance, default_currency());
                
                $action .= '<a class="btn btn-info btn-xs inline-flex h-7 w-8 items-center justify-center rounded-lg border-0 p-0 text-white shadow-sm transition hover:bg-sky-500 focus:outline-none focus:ring-2 focus:ring-sky-400/40" href="' . base_url() . 'admin/transactions/view_expense/' . $v_expense->transactions_id . '" title="' . lang('view') . '"><span data-lucide="eye" class="h-4 w-4"></span></a>' . ' ';
                if (!empty($can_edit) && !empty($edited)) {
                    $action .= btn_edit('admin/transactions/create_expense/' . $v_expense->transactions_id) . ' ';
                }
                if (!empty($can_delete) && !empty($deleted)) {
                    $action .= ajax_anchor(base_url("admin/transactions/delete_expense/$v_expense->transactions_id"), "", array("class" => "", "title" => lang('delete'), "data-fade-out-on-success" => "#table_" . $_key)) . ' ';
                }
                $sub_array[] = $action;
                $data[] = $sub_array;
            }
            
            render_table($data, array('type' => 'Expense'));
        } else {
            redirect('admin/dashboard');
        }
    }
    
    function get_account_statement($account_id, $start_date, $end_date, $transaction_type)
    {
        if ($transaction_type == 'all_transactions') {
            $where = array('account_id' => $account_id, 'date >=' => $start_date, 'date <=' => $end_date);
        } elseif ($transaction_type == 'debit') {
            $where = array('account_id' => $account_id, 'date >=' => $start_date, 'date <=' => $end_date, 'credit' => $transaction_type);
        } else {
            $where = array('account_id' => $account_id, 'date >=' => $start_date, 'date <=' => $end_date, 'debit' => $transaction_type);
        }
        $this->report_model->_table_name = "tbl_transactions"; //table name
        $this->report_model->_order_by = "transactions_id";
        return $this->report_model->get_by($where, FALSE);
    }
    
    public function account_statement_pdf($account_id, $start_date, $end_date, $transaction_type)
    {
        
        $data['all_transaction_info'] = $this->get_account_statement($account_id, $start_date, $end_date, $transaction_type);
        $data['title'] = lang('account_statement');
        $this->load->helper('dompdf');
        $viewfile = $this->load->view('admin/report/account_statement_pdf', $data, TRUE);
        pdf_create($viewfile, slug_it(lang('account_statement') . ' From:' . $start_date . ' To:' . $end_date));
    }
    
    public function income_report()
    {
        $data['title'] = lang('income_report');
        $data['page_title'] = lang('income_report');
        $data['page_description'] = lang('manage') . ' ' . lang('income_report');
        $data['report_summary'] = $this->build_finance_summary('credit', array('type' => 'Income'));
        $data['fee_graph'] = $this->build_fee_graph('Income', 'credit', 'tbl_income_category', 'income_category_id', 'income_category');
        $data['report_count'] = (int) $this->db->where('type', 'Income')->count_all_results('tbl_transactions');
        $data['all_deposit_info'] = $this->db
            ->select('tbl_transactions.*, tbl_accounts.account_name, tbl_client.name AS client_name, tbl_income_category.income_category', false)
            ->from('tbl_transactions')
            ->join('tbl_accounts', 'tbl_accounts.account_id = tbl_transactions.account_id', 'left')
            ->join('tbl_client', 'tbl_client.client_id = tbl_transactions.paid_by', 'left')
            ->join('tbl_income_category', 'tbl_income_category.income_category_id = tbl_transactions.category_id', 'left')
            ->where('tbl_transactions.type', 'Income')
            ->limit(20)
            ->order_by('tbl_transactions.transactions_id', 'DESC')
            ->get()
            ->result();
        $data['header_actions'] = array(
            array(
                'label' => lang('print'),
                'icon' => 'printer',
                'attrs' => array(
                    'onclick' => "print_sales_report('printReport'); return false;",
                ),
                'style' => 'primary',
            ),
            array(
                'label' => 'PDF',
                'icon' => 'file-down',
                'href' => base_url('admin/report/income_report_pdf'),
                'style' => 'info',
            ),
        );
        $data['header_filters'] = array();
        $data['income_report_print_html'] = $this->load->view('admin/report/income_report_pdf', $data, TRUE);
        $data['subview'] = $this->load->view('admin/report/income_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }

    public function income_report_pdf()
    {
        $data['title'] = lang('income_report');
        $data['report_summary'] = $this->build_finance_summary('credit', array('type' => 'Income'));
        $data['report_count'] = (int) $this->db->where('type', 'Income')->count_all_results('tbl_transactions');
        $data['all_deposit_info'] = $this->db
            ->select('tbl_transactions.*, tbl_accounts.account_name, tbl_client.name AS client_name, tbl_income_category.income_category', false)
            ->from('tbl_transactions')
            ->join('tbl_accounts', 'tbl_accounts.account_id = tbl_transactions.account_id', 'left')
            ->join('tbl_client', 'tbl_client.client_id = tbl_transactions.paid_by', 'left')
            ->join('tbl_income_category', 'tbl_income_category.income_category_id = tbl_transactions.category_id', 'left')
            ->where('tbl_transactions.type', 'Income')
            ->limit(20)
            ->order_by('tbl_transactions.transactions_id', 'DESC')
            ->get()
            ->result();
        $this->load->helper('dompdf');
        $viewfile = $this->load->view('admin/report/income_report_pdf', $data, TRUE);
        pdf_create($viewfile, slug_it(lang('income_report')));
    }
    
    public function get_transactions_report($category_id = null)
    { // this function is to create get monthy recap report
        $month = date('Y-m');
        $start_date = $month . '-01';
        $end_date = date('Y-m-t');
        $this->db->where('date >=', $start_date);
        $this->db->where('date <=', $end_date);
        if (!empty($category_id)) {
            $this->db->where('category_id', $category_id);
        }
        $rows = $this->db->order_by('transactions_id', 'DESC')->get('tbl_transactions')->result();
        $transaction_report = array();
        foreach ($rows as $row) {
            $day = (int) date('j', safe_strtotime($row->date));
            if (!isset($transaction_report[$day])) {
                $transaction_report[$day] = array();
            }
            $transaction_report[$day][] = $row;
        }
        return $transaction_report; // return the result
    }
    
    public function expense_report($category_id = null)
    {
        $data['title'] = lang('expense_report');
        $data['page_title'] = lang('expense_report');
        $data['page_description'] = lang('manage') . ' ' . lang('expense_report');
        $data['report_summary'] = $this->build_finance_summary('debit', array('type' => 'Expense'));
        $data['fee_graph'] = $this->build_fee_graph('Expense', 'debit', 'tbl_expense_category', 'expense_category_id', 'expense_category');
        $data['report_count'] = (int) $this->db->where('type', 'Expense')->count_all_results('tbl_transactions');
        $data['all_deposit_info'] = $this->db
            ->select('tbl_transactions.*, tbl_accounts.account_name, tbl_client.name AS client_name, tbl_expense_category.expense_category', false)
            ->from('tbl_transactions')
            ->join('tbl_accounts', 'tbl_accounts.account_id = tbl_transactions.account_id', 'left')
            ->join('tbl_client', 'tbl_client.client_id = tbl_transactions.paid_by', 'left')
            ->join('tbl_expense_category', 'tbl_expense_category.expense_category_id = tbl_transactions.category_id', 'left')
            ->where('tbl_transactions.type', 'Expense')
            ->limit(20)
            ->order_by('tbl_transactions.transactions_id', 'DESC')
            ->get()
            ->result();
        $data['header_actions'] = array(
            array(
                'label' => lang('print'),
                'icon' => 'printer',
                'attrs' => array(
                    'onclick' => "print_sales_report('printReport'); return false;",
                ),
                'style' => 'primary',
            ),
            array(
                'label' => 'PDF',
                'icon' => 'file-down',
                'href' => base_url('admin/report/expense_report_pdf'),
                'style' => 'info',
            ),
        );
        $data['header_filters'] = array();
        $data['expense_report_print_html'] = $this->load->view('admin/report/expense_report_pdf', $data, TRUE);
        $data['subview'] = $this->load->view('admin/report/expense_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }

    public function expense_report_pdf($category_id = null)
    {
        $data['title'] = lang('expense_report');
        $data['report_summary'] = $this->build_finance_summary('debit', array('type' => 'Expense'));
        $data['report_count'] = (int) $this->db->where('type', 'Expense')->count_all_results('tbl_transactions');
        $data['all_deposit_info'] = $this->db
            ->select('tbl_transactions.*, tbl_accounts.account_name, tbl_client.name AS client_name, tbl_expense_category.expense_category', false)
            ->from('tbl_transactions')
            ->join('tbl_accounts', 'tbl_accounts.account_id = tbl_transactions.account_id', 'left')
            ->join('tbl_client', 'tbl_client.client_id = tbl_transactions.paid_by', 'left')
            ->join('tbl_expense_category', 'tbl_expense_category.expense_category_id = tbl_transactions.category_id', 'left')
            ->where('tbl_transactions.type', 'Expense')
            ->limit(20)
            ->order_by('tbl_transactions.transactions_id', 'DESC')
            ->get()
            ->result();
        $this->load->helper('dompdf');
        $viewfile = $this->load->view('admin/report/expense_report_pdf', $data, TRUE);
        pdf_create($viewfile, slug_it(lang('expense_report')));
    }
    
    public function income_expense()
    {
        $data['title'] = lang('income_expense');
        $data['page_title'] = lang('income_expense');
        $data['page_description'] = lang('manage') . ' ' . lang('income_expense');
        $data['header_actions'] = array(
            array(
                'label' => lang('print'),
                'icon' => 'printer',
                'attrs' => array(
                    'onclick' => "print_sales_report('printReport'); return false;",
                ),
                'style' => 'primary',
            ),
            array(
                'label' => 'PDF',
                'icon' => 'file-down',
                'href' => base_url('admin/report/income_expense_pdf'),
                'style' => 'info',
            ),
        );
        $data['header_filters'] = array();
        $data['transactions_report'] = $this->get_transactions_report();
        $data['income_summary'] = $this->build_finance_summary('credit', array('type' => 'Income'));
        $data['expense_summary'] = $this->build_finance_summary('debit', array('type' => 'Expense'));
        $data['income_expense_print_html'] = $this->load->view('admin/report/income_expense_pdf', $data, TRUE);
        $data['subview'] = $this->load->view('admin/report/income_expense', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function income_expense_pdf()
    {
        $data['title'] = lang('income_expense');
        $data['income_summary'] = $this->build_finance_summary('credit', array('type' => 'Income'));
        $data['expense_summary'] = $this->build_finance_summary('debit', array('type' => 'Expense'));
        $this->load->helper('dompdf');
        $viewfile = $this->load->view('admin/report/income_expense_pdf', $data, TRUE);
        pdf_create($viewfile, slug_it(lang('income_expense')));
    }
    
    public function date_wise_report()
    {
        $data['title'] = lang('date_wise_report');
        $data['start_date'] = $this->input->post('start_date', TRUE);
        $data['end_date'] = $this->input->post('end_date', TRUE);
        if (!empty($data['start_date']) && !empty($data['end_date'])) {
            $data['report'] = TRUE;
            $data['all_transaction_info'] = $this->db
                ->where('date >=', $data['start_date'])
                ->where('date <=', $data['end_date'])
                ->get('tbl_transactions')
                ->result();
        }
        $data['subview'] = $this->load->view('admin/report/date_wise_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function date_wise_report_pdf($start_date, $end_date)
    {
        $data['title'] = lang('date_wise_report');
        $data['start_date'] = $start_date;
        $data['end_date'] = $end_date;
        $this->load->helper('dompdf');
        $data['all_transaction_info'] = $this->db
            ->where('date >=', $data['start_date'])
            ->where('date <=', $data['end_date'])
            ->order_by('transactions_id', 'DESC')
            ->get('tbl_transactions')
            ->result();
        $viewfile = $this->load->view('admin/report/date_wise_report_pdf', $data, TRUE);
        pdf_create($viewfile, slug_it(lang('date_wise_report')));
    }
    
    public function report_by_month()
    {
        $data['title'] = lang('report_by_month');
        $data['current_month'] = date('m');
        
        if ($this->input->post('year', TRUE)) { // if input year 
            $data['year'] = $this->input->post('year', TRUE);
        } else { // else current year
            $data['year'] = date('Y'); // get current year
        }
        $data['header_actions_html'] = '<a id="report-by-month-pdf" href="' . base_url('admin/report/report_by_month_pdf/' . $data['year'] . '/' . $data['current_month']) . '" class="inline-flex items-center gap-2 rounded-lg border border-sky-200 bg-sky-50 px-3 py-2 text-xs font-semibold text-sky-700 hover:bg-sky-100"><i data-lucide="file-text" class="h-4 w-4"></i>' . lang('pdf') . '</a>';
        $data['header_actions_html'] .= '<button type="button" onclick="expense_report(\'expense_report\')" class="inline-flex items-center gap-2 rounded-lg border border-rose-200 bg-rose-50 px-3 py-2 text-xs font-semibold text-rose-700 hover:bg-rose-100"><i data-lucide="printer" class="h-4 w-4"></i>' . lang('print') . '</button>';
        // get all expense list by year and month
        $data['report_by_month'] = $this->get_report_by_month($data['year']);
        $data['report_by_month_print_html'] = array();
        foreach ($data['report_by_month'] as $month_key => $month_rows) {
            $month_number = sprintf('%02d', (int) $month_key);
            $data['report_by_month_print_html'][$month_number] = $this->load->view('admin/report/report_by_month_pdf', array(
                'report_list' => $month_rows,
                'monthyaer' => date('F', safe_strtotime($data['year'] . '-' . $month_number . '-01')) . '  ' . $data['year'],
            ), TRUE);
        }

        $data['subview'] = $this->load->view('admin/report/report_by_month', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function get_report_by_month($year, $month = NULL)
    { // this function is to create get monthy recap report
        if (!empty($month)) {
            $date = new DateTime($year . '-' . $month . '-01');
            $start_date = $date->modify('first day of this month')->format('Y-m-d');
            $end_date = $date->modify('last day of this month')->format('Y-m-d');
            $get_expense_list = $this->report_model->get_report_by_date($start_date, $end_date); // get all report by start date and in date 
        } else {
            $get_expense_list = array_fill(1, 12, array());
            $start_date = $year . '-01-01';
            $end_date = $year . '-12-31';
            $rows = $this->db
                ->where('date >=', $start_date)
                ->where('date <=', $end_date)
                ->order_by('transactions_id', 'DESC')
                ->get('tbl_transactions')
                ->result();
            foreach ($rows as $row) {
                $month_index = (int) date('n', safe_strtotime($row->date));
                $get_expense_list[$month_index][] = $row;
            }
        }
        return $get_expense_list; // return the result
    }
    
    public function report_by_month_pdf($year, $month)
    {
        $activity = array(
            'user' => $this->session->userdata('user_id'),
            'module' => 'report/report_by_month',
            'module_field_id' => $year,
            'activity' => lang('activity_report_by_month_pdf'),
            'icon' => 'fa-laptop',
            'value1' => $year,
            'value2' => $month
        );
        $this->report_model->_table_name = 'tbl_activities';
        $this->report_model->_primary_key = 'activities_id';
        $this->report_model->save($activity);
        
        $data['report_list'] = $this->get_report_by_month($year, $month);
        $month_name = date('F', safe_strtotime($year . '-' . $month)); // get full name of month by date query                
        $data['monthyaer'] = $month_name . '  ' . $year;
        $this->load->helper('dompdf');
        $viewfile = $this->load->view('admin/report/report_by_month_pdf', $data, TRUE);
        pdf_create($viewfile, slug_it(lang('report_by_month') . '- ' . $data['monthyaer']));
    }
    
    public function all_income()
    {
        $data['title'] = lang('all_income');
		$data['page_title'] = lang('all_income');
		$data['page_description'] = lang('manage') . ' ' . lang('income');
		$data['header_actions'] = array();
		$data['header_filters'] = array();
		$data['header_filter_extras_html'] =
			form_dropdown_lookup_group(
				'income_account',
				lang('by') . ' ' . lang('account'),
				'incomeAccountFilter',
				array('icon' => 'wallet', 'type' => 'account', 'limit' => 12, 'min_length' => 0)
			) .
			form_dropdown_lookup_group(
				'income_category',
				lang('by') . ' ' . lang('categories'),
				'incomeCategoryFilter',
				array('icon' => 'tag', 'type' => 'income_category', 'limit' => 12, 'min_length' => 0)
			) .
			'<button type="button" id="clear_income_filters" class="inline-flex items-center gap-2 rounded-lg border border-border bg-background px-4 py-2 text-sm font-medium text-foreground hover:bg-muted/60"><i data-lucide="x-circle" class="h-4 w-4"></i><span>' . html_escape(lang('clear')) . '</span></button>';
		$data['dataTables'] = TRUE;
        $data['subview'] = $this->load->view('admin/report/all_income', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function all_expense()
    {
        $data['title'] = lang('all_expense');
		$data['page_title'] = lang('all_expense');
		$data['page_description'] = lang('manage') . ' ' . lang('expense');
		$data['header_actions'] = array();
		$data['header_filters'] = array();
		$data['header_filter_extras_html'] =
			form_dropdown_lookup_group(
				'expense_account',
				lang('by') . ' ' . lang('account'),
				'expenseAccountFilter',
				array('icon' => 'wallet', 'type' => 'account', 'limit' => 12, 'min_length' => 0)
			) .
			form_dropdown_lookup_group(
				'expense_category',
				lang('by') . ' ' . lang('categories'),
				'expenseCategoryFilter',
				array('icon' => 'tag', 'type' => 'expense_category', 'limit' => 12, 'min_length' => 0)
			) .
			'<button type="button" id="clear_expense_filters" class="inline-flex items-center gap-2 rounded-lg border border-border bg-background px-4 py-2 text-sm font-medium text-foreground hover:bg-muted/60"><i data-lucide="x-circle" class="h-4 w-4"></i><span>' . html_escape(lang('clear')) . '</span></button>';
		$data['dataTables'] = TRUE;

        $data['subview'] = $this->load->view('admin/report/all_expense', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function all_transaction()
    {
        $data['title'] = lang('all_transaction');
        $data['page_title'] = lang('all_transaction');
        $data['page_description'] = lang('manage') . ' ' . lang('all_transaction');
        $data['header_actions'] = array(
            array(
                'label' => lang('print'),
                'icon' => 'printer',
                'attrs' => array(
                    'onclick' => "print_sales_report('printReport'); return false;",
                ),
                'style' => 'primary',
            ),
            array(
                'label' => 'PDF',
                'icon' => 'file-down',
                'href' => base_url('admin/transactions/transactions_report_pdf/'),
                'style' => 'info',
            ),
        );
        $data['header_filters'] = array();
		$data['header_filter_extras_html'] =
			form_dropdown_lookup_group(
				'all_transaction_account',
				lang('by') . ' ' . lang('account'),
				'allTransactionAccountFilter',
				array(
					'icon' => 'wallet',
					'type' => 'account',
					'limit' => 12,
					'min_length' => 0,
				)
			);
		$data['dataTables'] = TRUE;
        $data['transactions_report'] = $this->get_transactions_report();
        $data['all_transaction_print_html'] = $this->load->view('admin/transactions/transactions_report_pdf', $data, TRUE);
        $data['subview'] = $this->load->view('admin/report/all_transaction', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function tasks_assignment()
    {
        $data['title'] = lang('tasks_assignment');
        $data['all_project'] = $this->report_model->get_permission('tbl_project');
        // get permission user by menu id
        $permission_user = $this->report_model->all_permission_user('57');
        // get all admin user
        $admin_user = $this->db->where('role_id', 1)->get('tbl_users')->result();
        // if not exist data show empty array.
        if (!empty($permission_user)) {
            $permission_user = $permission_user;
        } else {
            $permission_user = array();
        }
        if (!empty($admin_user)) {
            $admin_user = $admin_user;
        } else {
            $admin_user = array();
        }
        $data['assign_user'] = array_merge($admin_user, $permission_user);
        
        $data['user_tasks'] = $this->get_tasks_by_user($data['assign_user']);
        
        $data['subview'] = $this->load->view('admin/report/project_tasks_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    
    function get_tasks_by_user($assign_user, $tasks = null)
    {
        $tasks_info = $this->report_model->get_permission('tbl_task');
        if (!empty($tasks_info)) : foreach ($tasks_info as $v_tasks) :
            if (!empty($tasks)) {
                if ($v_tasks->permission == 'all') {
                    $permission[$v_tasks->permission][$v_tasks->task_status][] = $v_tasks->task_status;
                } else {
                    $get_permission = $this->decodePermission($v_tasks->permission);
                    if (!empty($get_permission)) {
                        foreach ($get_permission as $id => $v_permission) {
                            if (!empty($assign_user)) {
                                foreach ($assign_user as $v_user) {
                                    if ($v_user->user_id == $id) {
                                        $permission[$v_user->user_id][$v_tasks->task_status][] = $v_tasks->task_status;
                                    }
                                }
                            }
                        }
                    }
                }
            } else {
                if (!empty($v_tasks->project_id)) {
                    
                    if ($v_tasks->permission == 'all') {
                        $permission[$v_tasks->permission][$v_tasks->task_status][] = $v_tasks->task_status;
                    } else {
                        $get_permission = $this->decodePermission($v_tasks->permission);
                        if (!empty($get_permission)) {
                            foreach ($get_permission as $id => $v_permission) {
                                if (!empty($assign_user)) {
                                    foreach ($assign_user as $v_user) {
                                        if ($v_user->user_id == $id) {
                                            $permission[$v_user->user_id][$v_tasks->task_status][] = $v_tasks->task_status;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        
        
        endforeach;
        endif;
        if (empty($permission)) {
            $permission = array();
        }
        return $permission;
    }
    
    public
    function bugs_assignment()
    {
        $data['title'] = lang('bugs_assignment') . ' ' . lang('report');
        $data['all_project'] = $this->report_model->get_permission('tbl_project');
        // get permission user by menu id
        $permission_user = $this->report_model->all_permission_user('58');
        // get all admin user
        $admin_user = $this->db->where('role_id', 1)->get('tbl_users')->result();
        // if not exist data show empty array.
        if (!empty($permission_user)) {
            $permission_user = $permission_user;
        } else {
            $permission_user = array();
        }
        if (!empty($admin_user)) {
            $admin_user = $admin_user;
        } else {
            $admin_user = array();
        }
        $data['assign_user'] = array_merge($admin_user, $permission_user);
        
        $data['user_bugs'] = $this->get_bugs_by_user($data['assign_user']);
        
        $data['yearly_report'] = $this->get_project_report_by_month();
        $data['subview'] = $this->load->view('admin/report/project_bugs_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function get_project_report_by_month($tickets = null)
    { // this function is to create get monthy recap report
        
        for ($i = 1; $i <= 12; $i++) { // query for months
            $date = new DateTime(date('Y') . '-' . $i . '-01');
            $start_date = $date->modify('first day of this month')->format('Y-m-d');
            $end_date = $date->modify('last day of this month')->format('Y-m-d');
            if (!empty($tickets)) {
                $where = array('created >=' => $start_date . ' 00:00:00', 'created <=' => $end_date . ' 23:59:59');
                $get_result[$i] = $this->db->where($where)->get('tbl_tickets')->result();; // get all report by start date and in date
            } else {
                $where = array('created_time >=' => $start_date . ' 00:00:00', 'created_time <=' => $end_date . ' 23:59:59');
                $get_result[$i] = $this->db->where($where)->get('tbl_bug')->result();; // get all report by start date and in date
            }
        }
        
        return $get_result; // return the result
    }
    
    function get_bugs_by_user($assign_user, $bugs = null)
    {
        $bugs_info = $this->report_model->get_permission('tbl_bug');
        
        if (!empty($bugs_info)) : foreach ($bugs_info as $v_bugs) :
            if (!empty($bugs)) {
                if ($v_bugs->permission == 'all') {
                    $permission[$v_bugs->permission][$v_bugs->bug_status][] = $v_bugs->bug_status;
                } else {
                    $get_permission = $this->decodePermission($v_bugs->permission);
                    if (!empty($get_permission)) {
                        foreach ($get_permission as $id => $v_permission) {
                            if (!empty($assign_user)) {
                                foreach ($assign_user as $v_user) {
                                    if ($v_user->user_id == $id) {
                                        $permission[$v_user->user_id][$v_bugs->bug_status][] = $v_bugs->bug_status;
                                    }
                                }
                            }
                        }
                    }
                }
            } else {
                if (!empty($v_bugs->project_id)) {
                    
                    if ($v_bugs->permission == 'all') {
                        $permission[$v_bugs->permission][$v_bugs->bug_status][] = $v_bugs->bug_status;
                    } else {
                        $get_permission = $this->decodePermission($v_bugs->permission);
                        if (!empty($get_permission)) {
                            foreach ($get_permission as $id => $v_permission) {
                                if (!empty($assign_user)) {
                                    foreach ($assign_user as $v_user) {
                                        if ($v_user->user_id == $id) {
                                            $permission[$v_user->user_id][$v_bugs->bug_status][] = $v_bugs->bug_status;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        
        endforeach;
        endif;
        if (empty($permission)) {
            $permission = array();
        }
        return $permission;
    }
    
    public function project_report()
    {
        $data['title'] = lang('project_report');
        // get permission user by menu id
        $permission_user = $this->report_model->all_permission_user('57');
        // get all admin user
        $admin_user = $this->db->where('role_id', 1)->get('tbl_users')->result();
        // if not exist data show empty array.
        if (!empty($permission_user)) {
            $permission_user = $permission_user;
        } else {
            $permission_user = array();
        }
        if (!empty($admin_user)) {
            $admin_user = $admin_user;
        } else {
            $admin_user = array();
        }
        $data['assign_user'] = array_merge($admin_user, $permission_user);
        
        $data['all_project'] = $this->report_model->get_permission('tbl_project');
        $data['user_project'] = $this->get_project_by_user($data['assign_user']);
        $data['subview'] = $this->load->view('admin/report/project_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function tasks_report()
    {
        $data['title'] = lang('tasks_report');
        // get permission user by menu id
        $permission_user = $this->report_model->all_permission_user('54');
        // get all admin user
        $admin_user = $this->db->where('role_id', 1)->get('tbl_users')->result();
        // if not exist data show empty array.
        if (!empty($permission_user)) {
            $permission_user = $permission_user;
        } else {
            $permission_user = array();
        }
        if (!empty($admin_user)) {
            $admin_user = $admin_user;
        } else {
            $admin_user = array();
        }
        $data['assign_user'] = array_merge($admin_user, $permission_user);
        
        $data['all_tasks'] = $this->report_model->get_permission('tbl_task');
        $data['user_tasks'] = $this->get_tasks_by_user($data['assign_user'], true);
        
        $data['subview'] = $this->load->view('admin/report/tasks_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public
    function bugs_report()
    {
        $data['title'] = lang('bugs_report');
        // get permission user by menu id
        $permission_user = $this->report_model->all_permission_user('58');
        // get all admin user
        $admin_user = $this->db->where('role_id', 1)->get('tbl_users')->result();
        // if not exist data show empty array.
        if (!empty($permission_user)) {
            $permission_user = $permission_user;
        } else {
            $permission_user = array();
        }
        if (!empty($admin_user)) {
            $admin_user = $admin_user;
        } else {
            $admin_user = array();
        }
        $data['assign_user'] = array_merge($admin_user, $permission_user);
        $data['user_bugs'] = $this->get_bugs_by_user($data['assign_user'], true);
        
        $data['yearly_report'] = $this->get_project_report_by_month();
        $data['subview'] = $this->load->view('admin/report/bugs_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    function get_project_by_user($assign_user)
    {
        $all_project = $this->report_model->get_permission('tbl_project');
        if (!empty($all_project)) : foreach ($all_project as $v_project) :
            if ($v_project->permission == 'all') {
                $permission[$v_project->permission][$v_project->project_status][] = $v_project->project_status;
            } else {
                $get_permission = $this->decodePermission($v_project->permission);
                if (!empty($get_permission)) {
                    foreach ($get_permission as $id => $v_permission) {
                        if (!empty($assign_user)) {
                            foreach ($assign_user as $v_user) {
                                if ($v_user->user_id == $id) {
                                    $permission[$v_user->user_id][$v_project->project_status][] = $v_project->project_status;
                                }
                            }
                        }
                    }
                }
            }
        endforeach;
        endif;
        if (empty($permission)) {
            $permission = array();
        }
        
        return $permission;
    }
    
    public function tickets_report()
    {
        $data['title'] = lang('tickets_report');
        // get permission user by menu id
        $permission_user = $this->report_model->all_permission_user('7');
        // get all admin user
        $admin_user = $this->db->where('role_id', 1)->get('tbl_users')->result();
        // if not exist data show empty array.
        if (!empty($permission_user)) {
            $permission_user = $permission_user;
        } else {
            $permission_user = array();
        }
        if (!empty($admin_user)) {
            $admin_user = $admin_user;
        } else {
            $admin_user = array();
        }
        $data['assign_user'] = array_merge($admin_user, $permission_user);
        $data['user_tickets'] = $this->get_tickets_by_user($data['assign_user']);
        
        $data['yearly_report'] = $this->get_project_report_by_month(true);
        
        $data['subview'] = $this->load->view('admin/report/tickets_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    function get_tickets_by_user($assign_user)
    {
        $all_ticktes = $this->report_model->get_permission('tbl_tickets');
        if (!empty($all_ticktes)) : foreach ($all_ticktes as $v_ticktes) :
            if ($v_ticktes->permission == 'all') {
                $permission[$v_ticktes->permission][$v_ticktes->status][] = $v_ticktes->status;
            } else {
                $get_permission = $this->decodePermission($v_ticktes->permission);
                if (!empty($get_permission)) {
                    foreach ($get_permission as $id => $v_permission) {
                        if (!empty($assign_user)) {
                            foreach ($assign_user as $v_user) {
                                if ($v_user->user_id == $id) {
                                    $permission[$v_user->user_id][$v_ticktes->status][] = $v_ticktes->status;
                                }
                            }
                        }
                    }
                }
            }
        endforeach;
        endif;
        if (empty($permission)) {
            $permission = array();
        }
        
        return $permission;
    }
    
    public function client_report()
    {
        $data['title'] = lang('client_report');
        $data['page_title'] = lang('client_report');
        $data['page_description'] = lang('manage') . ' ' . lang('client_report');
        $data['header_actions'] = array();
        $data['header_filters'] = array();
        $data['client_id'] = $this->input->post('client_id', TRUE);
        $data['report_mode'] = $this->input->post('report_mode', TRUE);
        $data['client_details'] = null;
        $data['report_ready'] = false;
        $data['total_client_count'] = (int) $this->db->count_all('tbl_client');
        $data['client_payments'] = 0;
        $data['client_outstanding'] = 0;
        $data['total_estimate'] = 0;
        $data['project_statuses'] = array(
            'started' => 0,
            'in_progress' => 0,
            'cancel' => 0,
            'completed' => 0,
        );
        $data['ticket_statuses'] = array(
            'answered' => 0,
            'closed' => 0,
            'open' => 0,
            'in_progress' => 0,
        );
        $data['invoice_statuses'] = array(
            'paid' => 0,
            'unpaid' => 0,
            'partially_paid' => 0,
            'overdue' => 0,
            'draft' => 0,
            'cancelled' => 0,
        );
        $data['estimate_statuses'] = array(
            'draft' => 0,
            'pending' => 0,
            'accepted' => 0,
            'declined' => 0,
        );
        $data['proposal_statuses'] = array(
            'draft' => 0,
            'pending' => 0,
            'sent' => 0,
            'accepted' => 0,
            'declined' => 0,
        );
        $data['activity_counts'] = array(
            'invoices' => 0,
            'estimates' => 0,
            'proposals' => 0,
            'projects' => 0,
            'tickets' => 0,
        );

        if ($data['report_mode'] === 'portfolio') {
            $data['report_ready'] = true;

            $global_rollup = $this->finance_report_model->get_global_finance_rollup();
            $data['client_payments'] = !empty($global_rollup['payment_total']) ? (float) $global_rollup['payment_total'] : 0;

            $invoice_rows = $this->db
                ->select('invoices_id, total_tax, discount_total, adjustment, status, due_date')
                ->get('tbl_invoices')
                ->result();
            $data['activity_counts']['invoices'] = !empty($invoice_rows) ? count($invoice_rows) : 0;
            if (!empty($invoice_rows)) {
                $invoice_financial_map = $this->invoice_model->get_invoice_financial_map($invoice_rows);
                foreach ($invoice_rows as $invoice_row) {
                    $invoice_id = (int) $invoice_row->invoices_id;
                    $invoice_financial = !empty($invoice_financial_map[$invoice_id]) ? $invoice_financial_map[$invoice_id] : array();
                    $data['client_outstanding'] += !empty($invoice_financial['due']) ? (float) $invoice_financial['due'] : 0;
                    $invoice_status = strtolower(trim((string) $invoice_row->status));
                    if ($invoice_status === 'draft') {
                        $data['invoice_statuses']['draft']++;
                        continue;
                    }
                    if ($invoice_status === 'cancelled') {
                        $data['invoice_statuses']['cancelled']++;
                        continue;
                    }
                    $payment_total = !empty($invoice_financial['paid_amount']) ? (float) $invoice_financial['paid_amount'] : 0;
                    $due_total = !empty($invoice_financial['due']) ? (float) $invoice_financial['due'] : 0;
                    if ($payment_total < 1) {
                        if (!empty($invoice_row->due_date) && safe_strtotime($invoice_row->due_date) < safe_strtotime(date('Y-m-d'))) {
                            $data['invoice_statuses']['overdue']++;
                        } else {
                            $data['invoice_statuses']['unpaid']++;
                        }
                    } elseif ($due_total <= 0) {
                        $data['invoice_statuses']['paid']++;
                    } elseif (!empty($invoice_row->due_date) && safe_strtotime($invoice_row->due_date) < safe_strtotime(date('Y-m-d'))) {
                        $data['invoice_statuses']['overdue']++;
                    } else {
                        $data['invoice_statuses']['partially_paid']++;
                    }
                }
            }

            $estimate_rows = $this->db
                ->select('tbl_estimates.estimates_id, tbl_estimates.total_tax, tbl_estimates.discount_total, tbl_estimates.adjustment, tbl_estimates.status, COALESCE(SUM(tbl_estimate_items.total_cost), 0) AS subtotal', false)
                ->from('tbl_estimates')
                ->join('tbl_estimate_items', 'tbl_estimate_items.estimates_id = tbl_estimates.estimates_id', 'left')
                ->group_by('tbl_estimates.estimates_id')
                ->get()
                ->result();
            $data['activity_counts']['estimates'] = !empty($estimate_rows) ? count($estimate_rows) : 0;
            if (!empty($estimate_rows)) {
                foreach ($estimate_rows as $estimate_row) {
                    $discount_total = isset($estimate_row->discount_total) ? (float) $estimate_row->discount_total : 0;
                    $adjustment = isset($estimate_row->adjustment) ? (float) $estimate_row->adjustment : 0;
                    $tax_total = $this->estimates_model->get_estimate_tax_amount($estimate_row->estimates_id);
                    $data['total_estimate'] += ((float) $estimate_row->subtotal - $discount_total) + $tax_total + $adjustment;
                    $estimate_status = strtolower(trim((string) $estimate_row->status));
                    if (isset($data['estimate_statuses'][$estimate_status])) {
                        $data['estimate_statuses'][$estimate_status]++;
                    }
                }
            }

            $proposal_rows = $this->db
                ->select('status')
                ->where('module', 'client')
                ->get('tbl_proposals')
                ->result();
            $data['activity_counts']['proposals'] = !empty($proposal_rows) ? count($proposal_rows) : 0;
            if (!empty($proposal_rows)) {
                foreach ($proposal_rows as $proposal_row) {
                    $proposal_status = strtolower(trim((string) $proposal_row->status));
                    if (isset($data['proposal_statuses'][$proposal_status])) {
                        $data['proposal_statuses'][$proposal_status]++;
                    }
                }
            }

            $project_status_rows = $this->db
                ->select('project_status, COUNT(*) as total', false)
                ->group_by('project_status')
                ->get('tbl_project')
                ->result();
            if (!empty($project_status_rows)) {
                foreach ($project_status_rows as $status_row) {
                    if (isset($data['project_statuses'][$status_row->project_status])) {
                        $data['project_statuses'][$status_row->project_status] = (int) $status_row->total;
                    }
                }
            }
            $data['activity_counts']['projects'] = array_sum($data['project_statuses']);

            $ticket_status_rows = $this->db
                ->select('tbl_tickets.status, COUNT(*) as total', false)
                ->join('tbl_account_details', 'tbl_account_details.user_id = tbl_tickets.reporter', 'left')
                ->where('tbl_account_details.company IS NOT NULL', null, false)
                ->group_by('tbl_tickets.status')
                ->get('tbl_tickets')
                ->result();
            if (!empty($ticket_status_rows)) {
                foreach ($ticket_status_rows as $status_row) {
                    if (isset($data['ticket_statuses'][$status_row->status])) {
                        $data['ticket_statuses'][$status_row->status] = (int) $status_row->total;
                    }
                }
            }
            $data['activity_counts']['tickets'] = array_sum($data['ticket_statuses']);
        } elseif (!empty($data['client_id'])) {
            $data['client_details'] = $this->db->where('client_id', $data['client_id'])->get('tbl_client')->row();

            if (!empty($data['client_details'])) {
                $data['report_ready'] = true;

                $client_rollup = $this->finance_report_model->get_client_finance_rollup($data['client_id']);
                $data['client_payments'] = !empty($client_rollup['payment_total']) ? (float) $client_rollup['payment_total'] : 0;

                $data['client_outstanding'] = (float) $this->invoice_model->client_outstanding($data['client_id']);

                $client_invoice_rows = $this->db
                    ->select('invoices_id, total_tax, discount_total, adjustment, status, due_date')
                    ->where('client_id', $data['client_id'])
                    ->get('tbl_invoices')
                    ->result();
                $data['activity_counts']['invoices'] = !empty($client_invoice_rows) ? count($client_invoice_rows) : 0;
                if (!empty($client_invoice_rows)) {
                    $client_invoice_financial_map = $this->invoice_model->get_invoice_financial_map($client_invoice_rows);
                    foreach ($client_invoice_rows as $invoice_row) {
                        $invoice_id = (int) $invoice_row->invoices_id;
                        $invoice_financial = !empty($client_invoice_financial_map[$invoice_id]) ? $client_invoice_financial_map[$invoice_id] : array();
                        $invoice_status = strtolower(trim((string) $invoice_row->status));
                        if ($invoice_status === 'draft') {
                            $data['invoice_statuses']['draft']++;
                            continue;
                        }
                        if ($invoice_status === 'cancelled') {
                            $data['invoice_statuses']['cancelled']++;
                            continue;
                        }
                        $payment_total = !empty($invoice_financial['paid_amount']) ? (float) $invoice_financial['paid_amount'] : 0;
                        $due_total = !empty($invoice_financial['due']) ? (float) $invoice_financial['due'] : 0;
                        if ($payment_total < 1) {
                            if (!empty($invoice_row->due_date) && safe_strtotime($invoice_row->due_date) < safe_strtotime(date('Y-m-d'))) {
                                $data['invoice_statuses']['overdue']++;
                            } else {
                                $data['invoice_statuses']['unpaid']++;
                            }
                        } elseif ($due_total <= 0) {
                            $data['invoice_statuses']['paid']++;
                        } elseif (!empty($invoice_row->due_date) && safe_strtotime($invoice_row->due_date) < safe_strtotime(date('Y-m-d'))) {
                            $data['invoice_statuses']['overdue']++;
                        } else {
                            $data['invoice_statuses']['partially_paid']++;
                        }
                    }
                }

                $client_estimates = $this->db
                    ->select('estimates_id, status')
                    ->where('client_id', $data['client_id'])
                    ->get('tbl_estimates')
                    ->result();
                $data['activity_counts']['estimates'] = !empty($client_estimates) ? count($client_estimates) : 0;
                if (!empty($client_estimates)) {
                    foreach ($client_estimates as $estimate) {
                        $data['total_estimate'] += (float) $this->estimates_model->estimate_calculation('estimate_amount', $estimate->estimates_id);
                        $estimate_status = strtolower(trim((string) $estimate->status));
                        if (isset($data['estimate_statuses'][$estimate_status])) {
                            $data['estimate_statuses'][$estimate_status]++;
                        }
                    }
                }

                $client_proposals = $this->db
                    ->select('status')
                    ->where(array('module' => 'client', 'module_id' => $data['client_id']))
                    ->get('tbl_proposals')
                    ->result();
                $data['activity_counts']['proposals'] = !empty($client_proposals) ? count($client_proposals) : 0;
                if (!empty($client_proposals)) {
                    foreach ($client_proposals as $proposal) {
                        $proposal_status = strtolower(trim((string) $proposal->status));
                        if (isset($data['proposal_statuses'][$proposal_status])) {
                            $data['proposal_statuses'][$proposal_status]++;
                        }
                    }
                }

                $project_status_rows = $this->db
                    ->select('project_status, COUNT(*) as total', false)
                    ->where('client_id', $data['client_id'])
                    ->group_by('project_status')
                    ->get('tbl_project')
                    ->result();
                if (!empty($project_status_rows)) {
                    foreach ($project_status_rows as $status_row) {
                        if (isset($data['project_statuses'][$status_row->project_status])) {
                            $data['project_statuses'][$status_row->project_status] = (int) $status_row->total;
                        }
                    }
                }
                $data['activity_counts']['projects'] = array_sum($data['project_statuses']);

                $ticket_status_rows = $this->db
                    ->select('tbl_tickets.status, COUNT(*) as total', false)
                    ->join('tbl_account_details', 'tbl_account_details.user_id = tbl_tickets.reporter', 'left')
                    ->where('tbl_account_details.company', $data['client_id'])
                    ->group_by('tbl_tickets.status')
                    ->get('tbl_tickets')
                    ->result();
                if (!empty($ticket_status_rows)) {
                    foreach ($ticket_status_rows as $status_row) {
                        if (isset($data['ticket_statuses'][$status_row->status])) {
                            $data['ticket_statuses'][$status_row->status] = (int) $status_row->total;
                        }
                    }
                }
                $data['activity_counts']['tickets'] = array_sum($data['ticket_statuses']);
            }
        }
        
        $data['subview'] = $this->load->view('admin/report/client_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public function sales_report($filterBy = null)
    {
		
        $data['title'] = lang('sales') . ' ' . lang('report');
        $data['page_title'] = lang('sales') . ' ' . lang('report');
        $data['page_description'] = lang('manage') . ' ' . lang('sales') . ' ' . lang('report');
        $data['header_actions'] = array();
        $data['header_filters'] = array();
        $report_links = array(
            'invoices' => lang('invoices_report'),
            'invoice_by_client' => lang('invoice_by_client'),
            'payments' => lang('payments_report'),
            'estimates' => lang('estimates_report'),
            'estimate_by_client' => lang('estimate_by_client'),
            'proposals' => lang('proposals_report'),
            'proposal_by_client' => lang('proposal_by_client'),
        );
        $status = 'all';
        $range = array('0', '0');
        $id = !empty($filterBy) ? $filterBy : 'sales_report';
        $page = max(1, (int) $this->input->get_post('page', true));
        $per_page = config_item('tables_pagination_limit') ? (int) config_item('tables_pagination_limit') : 10;
        if (!empty($filterBy)) {
            $data['filterBy'] = $filterBy;
            if ($filterBy == 'invoices' || $filterBy == 'invoice_by_client') {
                $start_date = null;
                $end_date = null;
                if ($filterBy == 'invoice_by_client') {
                    $client = get_result('tbl_client');
                    $status = !empty($client) ? $client[0]->client_id : '0';
                } else {
                    $status = 'all';
                }
                if ($this->input->post() || $this->input->get()) {
                    $range = explode('-', $this->input->get_post('range', true));
                    if (!empty($range[0])) {
                        $start_date = date('Y-m-d', safe_strtotime($range[0]));
                        $end_date = date('Y-m-d', safe_strtotime($range[1]));
                        $data['range'] = array($start_date, $end_date);
                    }
                    $status = $this->input->get_post('status', true);
                }
                $range = array($start_date, $end_date);
                $data['status'] = $status;
                $invoice_report = $this->invoice_model->get_invoice_report_page_data($data['status'], $range, $per_page, ($page - 1) * $per_page);
                $data['all_invoices'] = $invoice_report['rows'];
                $data['invoice_count'] = $invoice_report['invoice_count'];
                $data['invoice_total'] = $invoice_report['invoice_total'];
                $data['total_tax'] = $invoice_report['total_tax'];
                $data['due_total'] = $invoice_report['due_total'];
                $data['average_total'] = $invoice_report['average_total'];
                $data['per_page'] = $per_page;
                $data['current_page'] = $page;
                $data['total_pages'] = $invoice_report['invoice_count'] > 0 ? (int) ceil($invoice_report['invoice_count'] / $per_page) : 1;
                if ($data['total_pages'] > 0 && $page > $data['total_pages']) {
                    $data['current_page'] = $data['total_pages'];
                    $invoice_report = $this->invoice_model->get_invoice_report_page_data($data['status'], $range, $per_page, ($data['current_page'] - 1) * $per_page);
                    $data['all_invoices'] = $invoice_report['rows'];
                    $data['invoice_count'] = $invoice_report['invoice_count'];
                    $data['invoice_total'] = $invoice_report['invoice_total'];
                    $data['total_tax'] = $invoice_report['total_tax'];
                    $data['due_total'] = $invoice_report['due_total'];
                    $data['average_total'] = $invoice_report['average_total'];
                }
                $current_query = $this->input->get(NULL, true);
                if (empty($current_query)) {
                    $current_query = $this->input->post(NULL, true);
                }
                if (!empty($current_query['page'])) {
                    unset($current_query['page']);
                }
                $pagination_base = base_url('admin/report/sales_report/' . $filterBy);
                $pagination_query = !empty($current_query) ? '?' . http_build_query($current_query) . '&' : '?';
                $data['pagination_base_url'] = $pagination_base . $pagination_query;
            } elseif ($filterBy == 'payments') {
                $start_date = null;
                $end_date = null;
                $client_id = null;
                if ($this->input->post() || $this->input->get()) {
                    $range = explode('-', $this->input->get_post('range', true));
                    if (!empty($range[0])) {
                        $start_date = date('Y-m-d', safe_strtotime($range[0]));
                        $end_date = date('Y-m-d', safe_strtotime($range[1]));
                        $data['range'] = array($start_date, $end_date);
                    }
                    $client_id = $this->input->get_post('client_id', true);
                }
                $range = array($start_date, $end_date);
                $data['status'] = $client_id;
                $data['all_payments'] = $this->invoice_model->get_payment_report($data['status'], $range);
                $status = $client_id;
            } else if ($filterBy == 'estimates' || $filterBy == 'estimate_by_client') {
				
                $start_date = null;
                $end_date = null;
                if ($filterBy == 'estimate_by_client') {
                    $client = get_result('tbl_client');
                    $status = !empty($client) ? $client[0]->client_id : '0';
                } else {
                    $status = 'all';
                }
                if ($this->input->post() || $this->input->get()) {
                    $range = explode('-', $this->input->get_post('range', true));
                    if (!empty($range[0])) {
                        $start_date = date('Y-m-d', safe_strtotime($range[0]));
                        $end_date = date('Y-m-d', safe_strtotime($range[1]));
                        $data['range'] = array($start_date, $end_date);
                    }
                    $status = $this->input->get_post('status', true);
                }
                $range = array($start_date, $end_date);
                $data['status'] = $status;
                $data['all_estimates'] = $this->estimates_model->get_estimate_report($data['status'], $range);
            } else if ($filterBy == 'proposals' || $filterBy == 'proposal_by_client') {
                $start_date = null;
                $end_date = null;
                if ($filterBy == 'proposal_by_client') {
                    $client = get_result('tbl_client');
                    $status = !empty($client) ? $client[0]->client_id : '0';
                } else {
                    $status = 'all';
                }
                if ($this->input->post() || $this->input->get()) {
                    $range = explode('-', $this->input->get_post('range', true));
                    if (!empty($range[0])) {
                        $start_date = date('Y-m-d', safe_strtotime($range[0]));
                        $end_date = date('Y-m-d', safe_strtotime($range[1]));
                        $data['range'] = array($start_date, $end_date);
                    }
                    $status = $this->input->get_post('status', true);
                }
                $range = array($start_date, $end_date);
                $data['status'] = $status;
                $data['all_proposals'] = $this->proposal_model->get_proposals_report($data['status'], $range);
            }
        }
        $data['header_actions_html'] = '<details class="relative"><summary class="inline-flex cursor-pointer list-none items-center gap-2 rounded-lg border border-border bg-card px-3 py-2 text-xs font-semibold text-foreground hover:bg-muted"><i data-lucide="file-bar-chart-2" class="h-4 w-4"></i>' . lang('report') . '<i data-lucide="chevron-down" class="h-4 w-4"></i></summary>';
        $data['header_actions_html'] .= '<div class="absolute right-0 z-20 mt-2 min-w-[220px] overflow-hidden rounded-xl border border-border bg-card shadow-lg">';
        foreach ($report_links as $key => $label) {
            $data['header_actions_html'] .= '<a href="' . base_url('admin/report/sales_report/' . $key) . '" class="block px-4 py-2.5 text-xs font-medium text-foreground hover:bg-muted">' . $label . '</a>';
        }
        $data['header_actions_html'] .= '</div></details>';
        if (!empty($filterBy)) {
            $data['header_actions_html'] .= '<a href="' . base_url() . 'admin/report/sales_report_pdf/' . $filterBy . '/' . $status . '/' . implode('/', $range) . '" class="inline-flex items-center gap-2 rounded-lg border border-emerald-200 bg-emerald-50 px-3 py-2 text-xs font-semibold text-emerald-700 hover:bg-emerald-100"><i data-lucide="file-text" class="h-4 w-4"></i>' . lang('pdf') . '</a>';
        }
        $data['header_actions_html'] .= '<a onclick="print_sales_report(\'' . $id . '\'); return false;" href="#" class="inline-flex items-center gap-2 rounded-lg border border-rose-200 bg-rose-50 px-3 py-2 text-xs font-semibold text-rose-700 hover:bg-rose-100"><i data-lucide="printer" class="h-4 w-4"></i>' . lang('print') . '</a>';
        $data['subview'] = $this->load->view('admin/report/sales_report', $data, TRUE);
        $this->load->view('admin/_layout_main', $data); //page load
    }
    
    public
    function sales_report_pdf($filterBy = null, $status = null, $start_date = null, $end_date = null)
    {
        
        $data['title'] = lang('sales') . ' ' . lang('report');
        if (!empty($filterBy)) {
            $data['filterBy'] = $filterBy;
            if ($filterBy == 'invoices' || $filterBy == 'invoice_by_client') {
                if (!empty($start_date)) {
                    $data['range'] = array($start_date, $end_date);
                    $date = lang('FROM') . ' ' . display_date($start_date) . ' ' . lang('TO') . ' ' . display_date($end_date);
                } else {
                    $start_date = null;
                    $end_date = null;
                    $date = null;
                }
                $range = array($start_date, $end_date);
                $data['status'] = $status;
                $data['all_invoices'] = $this->invoice_model->get_invoice_report($data['status'], $range);
                $viewfile = $this->load->view('admin/report/invoice_report_pdf', $data, TRUE);
                if (is_numeric($status)) {
                    $status = client_name($status);
                }
                $title = lang($filterBy) . ' ' . lang('report') . '- ' . $status . '- ' . $date;
            } elseif ($filterBy == 'payments') {
                if (!empty($start_date)) {
                    $data['range'] = array($start_date, $end_date);
                    $date = lang('FROM') . ' ' . display_date($start_date) . ' ' . lang('TO') . ' ' . display_date($end_date);
                } else {
                    $start_date = null;
                    $end_date = null;
                    $date = null;
                }
                $range = array($start_date, $end_date);
                $data['status'] = $status;
                $data['all_payments'] = $this->invoice_model->get_payment_report($data['status'], $range);
                
                $viewfile = $this->load->view('admin/report/payment_report_pdf', $data, TRUE);
                if (is_numeric($status)) {
                    $status = client_name($status);
                }
                $title = lang($filterBy) . ' ' . lang('report') . '- ' . $status . '- ' . $date;
            } else if ($filterBy == 'estimates' || $filterBy == 'estimate_by_client') {
				
                if (!empty($start_date)) {
                    $data['range'] = array($start_date, $end_date);
                    $date = lang('FROM') . ' ' . display_date($start_date) . ' ' . lang('TO') . ' ' . display_date($end_date);
                } else {
                    $start_date = null;
                    $end_date = null;
                    $date = null;
                }
                $range = array($start_date, $end_date);
                $data['status'] = $status;
                $data['all_estimates'] = $this->estimates_model->get_estimate_report($data['status'], $range);
                $viewfile = $this->load->view('admin/report/estimate_report_pdf', $data, TRUE);
                if (is_numeric($status)) {
                    $status = client_name($status);
                }
                $title = lang($filterBy) . ' ' . lang('report') . '- ' . $status . '- ' . $date;
            } else if ($filterBy == 'proposals' || $filterBy == 'proposal_by_client') {
                if (!empty($start_date)) {
                    $data['range'] = array($start_date, $end_date);
                    $date = lang('FROM') . ' ' . display_date($start_date) . ' ' . lang('TO') . ' ' . display_date($end_date);
                } else {
                    $start_date = null;
                    $end_date = null;
                    $date = null;
                }
                if ($this->input->post()) {
                    $range = explode('-', $this->input->post('range', true));
                    if (!empty($range[0])) {
                        $start_date = date('Y-m-d', safe_strtotime($range[0]));
                        $end_date = date('Y-m-d', safe_strtotime($range[1]));
                        $data['range'] = array($start_date, $end_date);
                    }
                    $status = $this->input->post('status', true);
                }
                $range = array($start_date, $end_date);
                $data['status'] = $status;
                $data['all_proposals'] = $this->proposal_model->get_proposals_report($data['status'], $range);
                if (is_numeric($status)) {
                    $status = client_name($status);
                }
                $title = lang($filterBy) . ' ' . lang('report') . '- ' . $status . '- ' . $date;
                $viewfile = $this->load->view('admin/report/proposals_report_pdf', $data, TRUE);
            }
        }
        $this->load->helper('dompdf');
        if (!empty($viewfile) && !empty($title)) {
            pdf_create($viewfile, slug_it($title));
        }
        
        //        $data['subview'] = $this->load->view('admin/report/sales_report', $data, TRUE);
        //        $this->load->view('admin/_layout_main', $data); //page load
    }
}
