<?php

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

class Studentfeemaster_model extends CI_Model {

    protected $balance_group;
    protected $balance_type;

    public function __construct() {
        parent::__construct();
        $this->load->config('ci-blog');
        $this->balance_group = $this->config->item('ci_balance_group');
        $this->balance_type = $this->config->item('ci_balance_type');
        $this->current_session = $this->setting_model->getCurrentSession();
    }
    
    public function selectlvl(){
        $query = $this->db->get('classes'); 
        return $query->result_array(); // returns as array of objects
    }

public function get_uniform_sales()
{ 
    $query = $this->db->get('uniformposrec'); 
    return $query->result_array(); // returns an array
}
    public function get_total_fee($filters = [])  
{
    $this->db->select('student_fees_deposite.id, student_fees_deposite.created_at, student_fees_deposite.receipt, 
                       fee_groups_feetype.session_id, fee_groups_feetype.amount, 
                       fee_groups.name as fee_group_name');
    $this->db->from('student_fees_deposite');
    $this->db->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id', 'left');
    $this->db->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id', 'left');

    // Apply filters
    if (!empty($filters['date_from'])) {
        $this->db->where('DATE(student_fees_deposite.created_at) >=', date('Y-m-d', strtotime($filters['date_from'])));
    }
    if (!empty($filters['date_to'])) {
        $this->db->where('DATE(student_fees_deposite.created_at) <=', date('Y-m-d', strtotime($filters['date_to'])));
    }
    if (!empty($filters['fee_group_id']) && $filters['view_mode'] == 'broken') {
        $this->db->where('fee_groups.id', $filters['fee_group_id']);
    }

    $query = $this->db->get();
    return $query->result();
}

public function get_outstanding_fees($date_from = null, $date_to = null)
{
    $this->db->select('
        sfd.created_at AS Date,
        st.firstname,
        st.lastname,
        c.class,
        fgf.amount,
        sfd.amount_detail
    ');
    $this->db->from('student_fees_deposite sfd');
    $this->db->join('fee_groups_feetype fgf', 'fgf.id = sfd.fee_groups_feetype_id', 'left');
    $this->db->join('student_session ss', 'ss.id = fgf.session_id', 'left');
    $this->db->join('students st', 'st.id = ss.student_id', 'left');
    $this->db->join('classes c', 'c.id = ss.class_id', 'left');

    if (!empty($date_from) && !empty($date_to)) {
        $this->db->where('DATE(sfd.created_at) >=', $date_from);
        $this->db->where('DATE(sfd.created_at) <=', $date_to);
    }

    $query = $this->db->get();
    $rows = $query->result_array();

    $final = [];
    foreach ($rows as $row) {
        $amount_detail = json_decode($row['amount_detail'], true);
        $paid_amount = isset($amount_detail['amount']) ? floatval($amount_detail['amount']) : 0;
        $owing = floatval($row['amount']) - $paid_amount;

        $final[] = [
            'Date' => $row['Date'],
            'Student_Name' => $row['firstname'] . ' ' . $row['lastname'],
            'Level' => $row['class'],
            'Owing_Balance' => $owing
        ];
    }

    return $final;
}

public function get_fee_groups() 
{
    $this->db->select('id, name');
    $this->db->from('fee_groups');
    $query = $this->db->get();
    return $query->result();
}

public function get_outstanding_uniforms()
{
    $this->db->select("
        u.created_date,
        u.name,
        u.student_id,
        u.description,
        u.batch_num,
        (u.amount - IFNULL(SUM(r.amount), 0)) AS amount
    ");
    $this->db->from('uniformpos u');
    $this->db->join(
        'uniformposrec r',
        'u.name = r.name AND u.student_id = r.student_id AND u.description = r.description',
        'left'
    );
    $this->db->group_by([
        'u.created_date',
        'u.name',
        'u.student_id',
        'u.description',
        'u.batch_num',
        'u.amount'
    ]);
    $this->db->having('amount > 0'); // optional: only show those still owing

    $query = $this->db->get();
    return $query->result_array();
}

public function get_combined_collections($filters = [])
{
    // Get Fees Data
    $this->db->select("
        student_fees_deposite.id,
        student_fees_deposite.created_at as collection_date,
        student_fees_deposite.receipt,
        fee_groups_feetype.amount,
        fee_groups.name as item_name,
        'Fee' as collection_type,
        fee_groups_feetype.session_id,
        NULL as student_id,
        NULL as description,
        NULL as batch_num,
        NULL as discount
    ");
    $this->db->from('student_fees_deposite');
    $this->db->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id', 'left');
    $this->db->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id', 'left');
    
    // Apply filters for fees
    if (!empty($filters['date_from'])) {
        $this->db->where('DATE(student_fees_deposite.created_at) >=', date('Y-m-d', strtotime($filters['date_from'])));
    }
    if (!empty($filters['date_to'])) {
        $this->db->where('DATE(student_fees_deposite.created_at) <=', date('Y-m-d', strtotime($filters['date_to'])));
    }
    if (!empty($filters['fee_group_id']) && $filters['view_mode'] == 'broken') {
        $this->db->where('fee_groups.id', $filters['fee_group_id']);
    }

    $fees_query = $this->db->get_compiled_select();
    
    // Reset query builder for uniform sales
    $this->db->reset_query();
    
    // Get Uniform Sales Data
    $this->db->select("
        id,
        created_date as collection_date,
        NULL as receipt,
        amount,
        name as item_name,
        'Uniform' as collection_type,
        NULL as session_id,
        student_id,
        description,
        batch_num,
        discount
    ");
    $this->db->from('uniformposrec');
    
    // Apply filters for uniform sales
    if (!empty($filters['date_from'])) {
        $this->db->where('DATE(created_date) >=', date('Y-m-d', strtotime($filters['date_from'])));
    }
    if (!empty($filters['date_to'])) {
        $this->db->where('DATE(created_date) <=', date('Y-m-d', strtotime($filters['date_to'])));
    }

    $uniforms_query = $this->db->get_compiled_select();
    
    // Combine both queries using UNION
    $combined_query = $fees_query . " UNION ALL " . $uniforms_query . " ORDER BY collection_date DESC";
    
    $query = $this->db->query($combined_query);
    return $query->result();
}

// Optional: Get summary totals
public function get_combined_totals($filters = [])
{
    $collections = $this->get_combined_collections($filters);
    
    $totals = [
        'total_fees' => 0,
        'total_uniforms' => 0,
        'grand_total' => 0,
        'fee_count' => 0,
        'uniform_count' => 0
    ];
    
    foreach ($collections as $collection) {
        if ($collection->collection_type == 'Fee') {
            $totals['total_fees'] += $collection->amount;
            $totals['fee_count']++;
        } else {
            $totals['total_uniforms'] += $collection->amount;
            $totals['uniform_count']++;
        }
        $totals['grand_total'] += $collection->amount;
    }
    
    return $totals;
}

    public function searchAssignFeeByClassSection($class_id = null, $section_id = null, $fee_session_group_id = null, $category = null, $gender = null, $rte = null) {
        $sql = "SELECT IFNULL(`student_fees_master`.`id`, '0') as `student_fees_master_id`,`classes`.`id` AS `class_id`,"
                . " `student_session`.`id` as `student_session_id`, `students`.`id`, "
                . "`classes`.`class`, `sections`.`id` AS `section_id`, `sections`.`section`, "
                . "`students`.`id`, `students`.`admission_no`, `students`.`roll_no`,"
                . " `students`.`admission_date`, `students`.`firstname`, `students`.`lastname`,"
                . " `students`.`image`, `students`.`mobileno`, `students`.`email`, `students`.`state`,"
                . " `students`.`city`, `students`.`pincode`, `students`.`religion`, `students`.`dob`, "
                . "`students`.`current_address`, `students`.`permanent_address`,"
                . " IFNULL(students.category_id, 0) as `category_id`,"
                . " IFNULL(categories.category, '') as `category`,"
                . " `students`.`adhar_no`, `students`.`samagra_id`,"
                . " `students`.`bank_account_no`, `students`.`bank_name`, `students`.`ifsc_code`,"
                . " `students`.`guardian_name`, `students`.`guardian_relation`, `students`.`guardian_phone`,"
                . " `students`.`guardian_address`, `students`.`is_active`, `students`.`created_at`,"
                . " `students`.`updated_at`, `students`.`father_name`, `students`.`rte`,"
                . " `students`.`gender` FROM `students` JOIN `student_session` "
                . "ON `student_session`.`student_id` = `students`.`id` JOIN `classes` "
                . "ON `student_session`.`class_id` = `classes`.`id` JOIN `sections` "
                . "ON `sections`.`id` = `student_session`.`section_id` LEFT JOIN `categories` "
                . "ON `students`.`category_id` = `categories`.`id` LEFT JOIN student_fees_master on"
                . " student_fees_master.student_session_id=student_session.id"
                . "  AND student_fees_master.fee_session_group_id=" . $this->db->escape($fee_session_group_id)
                . "WHERE `student_session`.`session_id` =  " . $this->current_session
                . " and `students`.`is_active` =  'yes'";

        if ($class_id != null) {
            $sql .= " AND `student_session`.`class_id` = " . $this->db->escape($class_id);
        }
        if ($section_id != null) {
            $sql .= " AND `student_session`.`section_id` =" . $this->db->escape($section_id);
        }
        if ($category != null) {
            $sql .= " AND `students`.`category_id` =" . $this->db->escape($category);
        }
        if ($gender != null) {
            $sql .= " AND `students`.`gender` =" . $this->db->escape($gender);
        }
        if ($rte != null) {
            $sql .= " AND `students`.`rte` =" . $this->db->escape($rte);
        }
        $sql .= " ORDER BY `students`.`id`";

        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function add_testing($data) {

        $this->db->where('student_fees_master_id', $data['student_fees_master_id']);
        $this->db->where('fee_groups_feetype_id', $data['fee_groups_feetype_id']);
        $this->db->where('amounts', $data['amounts']);
        $q = $this->db->get('array_testing');

        if ($q->num_rows() > 0) {
            return $q->row()->id;
        } else {
            $this->db->insert('array_testing', $data);
            return $this->db->insert_id();
        }
    }
    public function add($data) {

        $this->db->where('student_session_id', $data['student_session_id']);
        $this->db->where('fee_session_group_id', $data['fee_session_group_id']);
        $q = $this->db->get('student_fees_master');

        if ($q->num_rows() > 0) {
            return $q->row()->id;
        } else {
            $this->db->insert('student_fees_master', $data);
            return $this->db->insert_id();
        }
    }    
	
    public function addPreviousBal($student_data, $due_date) {
        $this->db->trans_start();
        $this->db->trans_strict(FALSE);
        $fee_group_exists = $this->feegroup_model->checkGroupExistsByName($this->balance_group);
        $fee_type_exists = $this->feetype_model->checkFeetypeByName($this->balance_type);
        $fee_group_id = 0;
        $fee_type_id = 0;
        if (!$fee_group_exists) {
            $this->db->insert('fee_groups', array('name' => $this->balance_group, 'is_system' => 1));
            $fee_group_id = $this->db->insert_id();
        } else {
            $fee_group_id = $fee_group_exists->id;
        }

        if (!$fee_type_exists) {
            $this->db->insert('feetype', array('type' => $this->balance_type, 'code' => $this->balance_type, 'is_system' => 1));
            $fee_type_id = $this->db->insert_id();
        } else {
            $fee_type_id = $fee_type_exists->id;
        }
        $to_be_insert = array(
            'session_id' => $this->current_session,
            'fee_groups_id' => $fee_group_id,
            'feetype_id' => $fee_type_id,
            'fee_session_group_id' => 0,
            'due_date' => $due_date
        );
        $parentid = $this->feesessiongroup_model->group_exists($to_be_insert['fee_groups_id']);

        $to_be_insert['fee_session_group_id'] = $parentid;

        $session_group_exists = $this->feesessiongroup_model->checkExists($to_be_insert);
        if (!$session_group_exists) {
            $this->db->insert('fee_groups_feetype', $to_be_insert);
        } else {
            $this->db->where('id', $session_group_exists);
            $this->db->update('fee_groups_feetype', $to_be_insert);
        }
        $student_list = array();
        if (isset($student_data) && !empty($student_data)) {

            $total_rec = count($student_data);
            for ($i = 0; $i < $total_rec; $i++) {
                $student_list[] = $student_data[$i]['student_session_id'];
                $student_data[$i]['id'] = 0;
                $student_data[$i]['fee_session_group_id'] = $parentid;
            }
            $check_insert_feemaster = $this->selectInArray($parentid, $student_list);
            if (!empty($check_insert_feemaster)) {
                $insert_new_student = array();
                foreach ($student_data as $student_key => $student_value) {
                    $student_data[$student_key]['id'] = $this->findValueExists($check_insert_feemaster, $student_value['student_session_id']);
                    if ($student_data[$student_key]['id'] == 0) {
                        $insert_new_student[] = $student_data[$student_key];
                        unset($student_data[$student_key]);
                    }
                }

                if (!empty($insert_new_student)) {
                    $this->db->insert_batch('student_fees_master', $insert_new_student);
                }
                $this->db->update_batch('student_fees_master', $student_data, 'id');
            } else {
                $this->db->insert_batch('student_fees_master', $student_data);
            }
        }
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return FALSE;
        } else {
            $this->db->trans_commit();
            return TRUE;
        }
    }

    function findValueExists($array, $find) {
        $id = 0;
        foreach ($array as $x => $x_value) {
            if ($x_value->student_session_id == $find)
                return $x_value->id;
        }
        return $id;
    }

    public function selectInArray($fee_session_groups, $student_session_array) {

        $this->db->where('fee_session_group_id', $fee_session_groups);
        $this->db->where_in('student_session_id', $student_session_array);
        $q = $this->db->get('student_fees_master');
        $result = $q->result();
        return $result;
    }

    public function delete($fee_session_groups, $array) {

        $this->db->where('fee_session_group_id', $fee_session_groups);
        $this->db->where_in('student_session_id', $array);
        $this->db->delete('student_fees_master');
    }

    public function getBalanceMasterRecord($group_name, $student_session_array) {
        $sql = "select * from student_fees_master where student_session_id in $student_session_array and fee_session_group_id=(SELECT id FROM `fee_session_groups` where fee_groups_id=(SELECT id FROM `fee_groups` WHERE name=" . "'" . $group_name . "'" . ") and session_id=$this->current_session)";

        $query = $this->db->query($sql);
        $result = $query->result();
        return $result;
    }


    public function getStudentFees($student_session_id) {
        $sql = "SELECT `student_fees_master`.*,student_fees_master.created_at,fee_groups.name FROM `student_fees_master` INNER JOIN fee_session_groups on 
		student_fees_master.fee_session_group_id=fee_session_groups.id INNER JOIN fee_groups on fee_groups.id=fee_session_groups.fee_groups_id 
		WHERE `student_session_id` = " . $student_session_id . " ORDER BY `student_fees_master`.`id`";
        $query = $this->db->query($sql);
        $result = $query->result();

        if (!empty($result)) {
            foreach ($result as $result_key => $result_value) {
                $fee_session_group_id = $result_value->fee_session_group_id;
                $student_fees_master_id = $result_value->id;
                $result_value->fees = $this->getDueFeeByFeeSessionGroup($fee_session_group_id, $student_fees_master_id);

                if ($result_value->is_system != 0) {
                    $result_value->fees[0]->amount = $result_value->amount;
                }
            }
        }

        return $result;
    }    
	
	public function getStudentTotalFeesGL() {
        $sql = "SELECT * from `student_fees_deposite`";
        $query = $this->db->query($sql);
        $result = $query->result();

        return $result;
    }


     public function feesSummary($date_from, $date_to) {
        // Build the query with parameter binding for security
        $sql = "
        (SELECT 
            sfm.id,
            sfm.student_session_id,
            s.firstname,
            s.lastname,
            c.class AS class_name,
            sec.section AS section_name,
            fg.name AS fee_group_name,
            fgt.amount AS fee_amount,
            sfm.created_at,
            CONCAT('INV', LPAD(sfm.id, 4, '0')) AS invoice_number,
            0 AS is_total_row
        FROM 
            `student_fees_master` sfm
        INNER JOIN 
            `fee_session_groups` fsg ON sfm.fee_session_group_id = fsg.id
        INNER JOIN 
            `fee_groups` fg ON fg.id = fsg.fee_groups_id
        INNER JOIN
            `fee_groups_feetype` fgt ON fsg.id = fgt.fee_session_group_id
        LEFT JOIN
            `student_session` ss ON sfm.student_session_id = ss.id
        LEFT JOIN
            `students` s ON ss.student_id = s.id
        LEFT JOIN
            `classes` c ON ss.class_id = c.id
        LEFT JOIN
            `sections` sec ON ss.section_id = sec.id
        WHERE 
            DATE(sfm.created_at) BETWEEN ? AND ?)

        UNION ALL

        (SELECT 
            NULL AS id,
            NULL AS student_session_id,
            NULL AS firstname,
            NULL AS lastname,
            NULL AS class_name,
            NULL AS section_name,
            'GRAND TOTAL' AS fee_group_name,
            SUM(fgt.amount) AS fee_amount,
            NULL AS created_at,
            NULL AS invoice_number,
            1 AS is_total_row
        FROM 
            `student_fees_master` sfm
        INNER JOIN 
            `fee_session_groups` fsg ON sfm.fee_session_group_id = fsg.id
        INNER JOIN 
            `fee_groups` fg ON fg.id = fsg.fee_groups_id
        INNER JOIN
            `fee_groups_feetype` fgt ON fsg.id = fgt.fee_session_group_id
        WHERE 
            DATE(sfm.created_at) BETWEEN ? AND ?)

        ORDER BY 
            is_total_row,
            created_at,
            id";

        // Execute with parameter binding (twice for both WHERE clauses)
        $query = $this->db->query($sql, array($date_from, $date_to, $date_from, $date_to));
        
        return $query->result_array();
    }

   
public function outstandingFees($date_from, $date_to) {
    $sql = "
    (SELECT 
        sfm.id,
        sfm.student_session_id,
        s.firstname,
        s.lastname,
        c.class AS class_name,
        sec.section AS section_name,
        fg.name AS fee_group_name,
        fgt.amount AS fee_amount,
        COALESCE(SUM(
            CAST(JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(sfd.amount_detail, CONCAT('$.\"', numbers.n, '\"')), '$.amount')) AS DECIMAL(10,2))
        ), 0) AS amount_paid,
        COALESCE(SUM(
            CAST(JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(sfd.amount_detail, CONCAT('$.\"', numbers.n, '\"')), '$.amount_discount')) AS DECIMAL(10,2))
        ), 0) AS amount_discount,
       
            sfm.created_at,
            CONCAT('INV', LPAD(sfm.id, 4, '0')) AS invoice_number,
            0 AS is_total_row
        FROM 
            `student_fees_master` sfm
        INNER JOIN 
            `fee_session_groups` fsg ON sfm.fee_session_group_id = fsg.id
        INNER JOIN 
            `fee_groups` fg ON fg.id = fsg.fee_groups_id
        INNER JOIN
            `fee_groups_feetype` fgt ON fsg.id = fgt.fee_session_group_id
        LEFT JOIN
            `student_session` ss ON sfm.student_session_id = ss.id
        LEFT JOIN
            `students` s ON ss.student_id = s.id
        LEFT JOIN
            `classes` c ON ss.class_id = c.id
        LEFT JOIN
            `sections` sec ON ss.section_id = sec.id
        LEFT JOIN
            `student_fees_deposite` sfd ON sfm.id = sfd.student_fees_master_id 
            AND fgt.id = sfd.fee_groups_feetype_id
        LEFT JOIN (
            SELECT '0' AS n UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4'
            UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9'
        ) numbers ON JSON_EXTRACT(sfd.amount_detail, CONCAT('$.\"', numbers.n, '\"')) IS NOT NULL
        WHERE 
            DATE(sfm.created_at) BETWEEN ? AND ?
        GROUP BY
            sfm.id, sfm.student_session_id, s.firstname, s.lastname, 
            c.class, sec.section, fg.name, fgt.amount, sfm.created_at)

        UNION ALL

        (SELECT 
            NULL AS id,
            NULL AS student_session_id,
            NULL AS firstname,
            NULL AS lastname,
            NULL AS class_name,
            NULL AS section_name,
            'GRAND TOTAL' AS fee_group_name,
            SUM(fgt.amount) AS fee_amount,
            COALESCE(SUM(
                CAST(JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(sfd.amount_detail, CONCAT('$.\"', numbers.n, '\"')), '$.amount')) AS DECIMAL(10,2))
            ), 0) AS amount_paid,
            COALESCE(SUM(
                CAST(JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(sfd.amount_detail, CONCAT('$.\"', numbers.n, '\"')), '$.amount_discount')) AS DECIMAL(10,2))
            ), 0) AS amount_discount,
            NULL AS created_at,
            NULL AS invoice_number,
            1 AS is_total_row
        FROM 
            `student_fees_master` sfm
        INNER JOIN 
            `fee_session_groups` fsg ON sfm.fee_session_group_id = fsg.id
        INNER JOIN 
            `fee_groups` fg ON fg.id = fsg.fee_groups_id
        INNER JOIN
            `fee_groups_feetype` fgt ON fsg.id = fgt.fee_session_group_id
        LEFT JOIN
            `student_fees_deposite` sfd ON sfm.id = sfd.student_fees_master_id 
            AND fgt.id = sfd.fee_groups_feetype_id
        LEFT JOIN (
            SELECT '0' AS n UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4'
            UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9'
        ) numbers ON JSON_EXTRACT(sfd.amount_detail, CONCAT('$.\"', numbers.n, '\"')) IS NOT NULL
        WHERE 
            DATE(sfm.created_at) BETWEEN ? AND ?
        GROUP BY is_total_row)

        ORDER BY 
            is_total_row,
            created_at,
            id";

        $query = $this->db->query($sql, array($date_from, $date_to, $date_from, $date_to));
        return $query->result_array();
    }

		public function getStudentFeeswithDatesBetween($student_session_id,$date_from, $date_to) {

        $sql = "SELECT `student_fees_master`.*,student_fees_master.created_at,fee_groups.name FROM `student_fees_master` INNER JOIN fee_session_groups on 
		student_fees_master.fee_session_group_id=fee_session_groups.id INNER JOIN fee_groups on fee_groups.id=fee_session_groups.fee_groups_id 
		WHERE `student_session_id` = " . $student_session_id . " ORDER BY `student_fees_master`.`id`";
        $query = $this->db->query($sql);
        $result = $query->result();

        if (!empty($result)) {
            foreach ($result as $result_key => $result_value) {
                $fee_session_group_id = $result_value->fee_session_group_id;
                $student_fees_master_id = $result_value->id;
                $result_value->fees = $this->getDueFeeByFeeSessionGroupwithDatesBetween($fee_session_group_id, $student_fees_master_id,$date_from, $date_to);

                if ($result_value->is_system != 0) {
                    $result_value->fees[0]->amount = $result_value->amount;
                }
            }
        }

        return $result;
    }
	
	    public function getDueFeeByFeeSessionGroupwithDatesBetween($fee_session_groups_id, $student_fees_master_id,$date_from, $date_to) {

        $sql = "SELECT student_fees_master.*,student_fees_master.is_active,student_fees_deposite.receipt,fee_groups_feetype.id as `fee_groups_feetype_id`,fee_groups_feetype.amount,fee_groups_feetype.due_date,
		fee_groups_feetype.fee_groups_id,fee_groups.name,fee_groups_feetype.feetype_id,feetype.code,feetype.type, 
		IFNULL(student_fees_deposite.id,0) as `student_fees_deposite_id`, 
		IFNULL(student_fees_deposite.amount_detail,0) as `amount_detail` FROM `student_fees_master` 
		INNER JOIN fee_session_groups on fee_session_groups.id = student_fees_master.fee_session_group_id 
		INNER JOIN fee_groups_feetype on  fee_groups_feetype.fee_session_group_id = fee_session_groups.id  
		INNER JOIN fee_groups on fee_groups.id=fee_groups_feetype.fee_groups_id 
		INNER JOIN feetype on feetype.id=fee_groups_feetype.feetype_id 
		LEFT JOIN student_fees_deposite on student_fees_deposite.student_fees_master_id=student_fees_master.id and 
		student_fees_deposite.fee_groups_feetype_id=fee_groups_feetype.id WHERE student_fees_master.fee_session_group_id =" . $fee_session_groups_id . " and
		student_fees_master.id=" . $student_fees_master_id . " 
		order by fee_groups_feetype.due_date asc";
		
		 

        $query = $this->db->query($sql);
        return $query->result();
    }

        public function getStudentDetailByStudentSession($id) {
    $sql = "SELECT student_fees_master.*,`student_session`.`student_id`,`students`.`admission_no` , `students`.`roll_no`,`students`.`admission_date`,`students`.`firstname`,  `students`.`lastname`,`students`.`image`,    `students`.`mobileno`, `students`.`email`,`students`.`state` ,   `students`.`city` , `students`.`pincode` ,     `students`.`religion`,     `students`.`dob`,`students`.`current_address`,    `students`.`permanent_address`,`students`.`app_key`,`students`.`parent_app_key` FROM `student_fees_master` INNER JOIN `student_session` on `student_session`.id=student_fees_master.student_session_id INNER JOIN `students` on `students`.id=`student_session`.student_id WHERE `student_fees_master`.`id`=".$id;
        $query = $this->db->query($sql);
        $result = $query->row();
        

        return $result;
    }
    

    public function get_student_fees_with_totals() {
    // Main query for fee records
    $this->db->select('sfm.id, sfm.student_session_id, s.firstname, s.lastname, 
                      fg.name AS fee_group_name, fgt.amount AS fee_amount, 
                      sfm.created_at, 0 AS is_total_row');
    $this->db->from('student_fees_master sfm');
    $this->db->join('fee_session_groups fsg', 'sfm.fee_session_group_id = fsg.id');
    $this->db->join('fee_groups fg', 'fg.id = fsg.fee_groups_id');
    $this->db->join('fee_groups_feetype fgt', 'fsg.id = fgt.fee_session_group_id');
    $this->db->join('student_session ss', 'sfm.student_session_id = ss.id', 'left');
    $this->db->join('students s', 'ss.student_id = s.id', 'left');
    $this->db->where('sfm.student_session_id BETWEEN 554 AND 614');
    
    $query1 = $this->db->get_compiled_select();
    
    // Reset query builder
    $this->db->reset_query();
    
    // Query for grand total
    $this->db->select('NULL AS id, NULL AS student_session_id, NULL AS firstname, 
                      NULL AS lastname, "GRAND TOTAL" AS fee_group_name, 
                      SUM(fgt.amount) AS fee_amount, NULL AS created_at, 
                      1 AS is_total_row');
    $this->db->from('student_fees_master sfm');
    $this->db->join('fee_session_groups fsg', 'sfm.fee_session_group_id = fsg.id');
    $this->db->join('fee_groups fg', 'fg.id = fsg.fee_groups_id');
    $this->db->join('fee_groups_feetype fgt', 'fsg.id = fgt.fee_session_group_id');
    $this->db->where('sfm.student_session_id BETWEEN 554 AND 614');
    
    $query2 = $this->db->get_compiled_select();
    
    // Combine queries with UNION ALL
    $final_query = "($query1) UNION ALL ($query2) ORDER BY is_total_row, student_session_id, id";
    
    // Execute the combined query
    $result = $this->db->query($final_query);
    return $result->result();
}

    public function getDueFeeByFeeSessionGroup($fee_session_groups_id, $student_fees_master_id) {
        $sql = "SELECT student_fees_master.*,student_fees_master.is_active,student_fees_deposite.receipt,fee_groups_feetype.id as `fee_groups_feetype_id`,fee_groups_feetype.amount,fee_groups_feetype.due_date,
		fee_groups_feetype.fee_groups_id,fee_groups.name,fee_groups_feetype.feetype_id,feetype.code,feetype.type, 
		IFNULL(student_fees_deposite.id,0) as `student_fees_deposite_id`, 
		IFNULL(student_fees_deposite.amount_detail,0) as `amount_detail` FROM `student_fees_master` 
		INNER JOIN fee_session_groups on fee_session_groups.id = student_fees_master.fee_session_group_id 
		INNER JOIN fee_groups_feetype on  fee_groups_feetype.fee_session_group_id = fee_session_groups.id  
		INNER JOIN fee_groups on fee_groups.id=fee_groups_feetype.fee_groups_id 
		INNER JOIN feetype on feetype.id=fee_groups_feetype.feetype_id 
		LEFT JOIN student_fees_deposite on student_fees_deposite.student_fees_master_id=student_fees_master.id and 
		student_fees_deposite.fee_groups_feetype_id=fee_groups_feetype.id WHERE student_fees_master.fee_session_group_id =" . $fee_session_groups_id . " and
		student_fees_master.id=" . $student_fees_master_id . " order by fee_groups_feetype.due_date asc";

        $query = $this->db->query($sql);
        return $query->result();
    }

    public function getDueFeeByFeeSessionGroupFeetype($fee_session_groups_id, $student_fees_master_id, $fee_groups_feetype_id) {

        $sql = "SELECT student_fees_master.id,student_fees_deposite.receipt,student_fees_deposite.*,student_fees_master.is_system,student_fees_master.student_session_id,
		student_fees_master.fee_session_group_id,student_fees_master.amount as `student_fees_master_amount`,
		fee_groups_feetype.id as `fee_groups_feetype_id`,students.firstname,students.lastname,student_session.class_id,
		classes.class,sections.section,students.guardian_name,students.father_name,student_session.section_id,
		student_session.student_id,fee_groups_feetype.amount,fee_groups_feetype.due_date,fee_groups_feetype.fee_groups_id,
		fee_groups.name,fee_groups_feetype.feetype_id,feetype.code,feetype.type, 
		IFNULL(student_fees_deposite.id,0) as `student_fees_deposite_id`, 
		IFNULL(student_fees_deposite.amount_detail,0) as `amount_detail` FROM `student_fees_master` 
		INNER JOIN fee_session_groups on fee_session_groups.id = student_fees_master.fee_session_group_id 
		INNER JOIN fee_groups_feetype on  fee_groups_feetype.fee_session_group_id = fee_session_groups.id  
		INNER JOIN fee_groups on fee_groups.id=fee_groups_feetype.fee_groups_id 
		INNER JOIN feetype on feetype.id=fee_groups_feetype.feetype_id 
		LEFT JOIN student_fees_deposite on student_fees_deposite.student_fees_master_id=student_fees_master.id and 
		student_fees_deposite.fee_groups_feetype_id=fee_groups_feetype.id 
		INNER JOIN student_session on student_session.id= student_fees_master.student_session_id 
		INNER JOIN classes on classes.id= student_session.class_id 
		INNER JOIN sections on sections.id= student_session.section_id 
		INNER JOIN students on students.id=student_session.student_id  WHERE
		student_fees_master.fee_session_group_id =" . $fee_session_groups_id . " and student_fees_master.id=" . $student_fees_master_id . " and 
		fee_groups_feetype.id= " . $fee_groups_feetype_id;
       
        $query = $this->db->query($sql);
        return $query->row();
    }

    public function fee_deposit($data, $send_to, $student_fees_discount_id) {
        $this->db->where('student_fees_master_id', $data['student_fees_master_id']);
        $this->db->where('fee_groups_feetype_id', $data['fee_groups_feetype_id']);
        $q = $this->db->get('student_fees_deposite');
        if ($q->num_rows() > 0) {
            $desc = $data['amount_detail']['description'];
            $this->db->trans_start(); // Query will be rolled back
            $row = $q->row();
            $this->db->where('id', $row->id);
            $a = json_decode($row->amount_detail, true);
            $inv_no = max(array_keys($a)) + 1;
            $data['amount_detail']['inv_no'] = $inv_no;
            $a[$inv_no] = $data['amount_detail'];
            $data['amount_detail'] = json_encode($a);
            $this->db->update('student_fees_deposite', $data);

            if ($student_fees_discount_id != "") {
                $this->db->where('id', $student_fees_discount_id);
                $this->db->update('student_fees_discounts', array('status' => 'applied', 'description' => $desc, 'payment_id' => $row->id . "/" . $inv_no));
            }


            $this->db->trans_complete();
            if ($this->db->trans_status() === FALSE) {
                $this->db->trans_rollback();

                return FALSE;
            } else {
                $this->db->trans_commit();
                return json_encode(array('invoice_id' => $row->id, 'sub_invoice_id' => $inv_no));
            }
        } else {

            $this->db->trans_start(); // Query will be rolled back
            $data['amount_detail']['inv_no'] = 1;
            $desc = $data['amount_detail']['description'];
            $data['amount_detail'] = json_encode(array('1' => $data['amount_detail']));
            $this->db->insert('student_fees_deposite', $data);
            $inserted_id = $this->db->insert_id();
            if ($student_fees_discount_id != "") {
                $this->db->where('id', $student_fees_discount_id);
                $this->db->update('student_fees_discounts', array('status' => 'applied', 'description' => $desc, 'payment_id' => $inserted_id . "/" . "1"));
            }

            $this->db->trans_complete(); # Completing transaction

            if ($this->db->trans_status() === FALSE) {

                $this->db->trans_rollback();
                return FALSE;
            } else {
                $this->db->trans_commit();
                return json_encode(array('invoice_id' => $inserted_id, 'sub_invoice_id' => 1));
            }
        }
    }

    public function getFeeBetweenDate($start_date, $end_date) {
		$transfer = 0;
        $this->db->select('`student_fees_deposite`.*,student_fees_deposite.amount_detail,student_fees_deposite.invoice,students.firstname,students.lastname,student_session.class_id,classes.class,
		sections.section,fee_groups_feetype.amount as invoice_amount,student_session.section_id,student_session.student_id,`fee_groups`.`name`, `feetype`.`type`, `feetype`.
		`code`,student_fees_master.student_session_id')->from('student_fees_deposite')->where('student_fees_deposite.transfer', $transfer);
        $this->db->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id');

        $this->db->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id');
        $this->db->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id');
        $this->db->join('student_fees_master', 'student_fees_master.id=student_fees_deposite.student_fees_master_id');
        $this->db->join('student_session', 'student_session.id= student_fees_master.student_session_id');
        $this->db->join('classes', 'classes.id= student_session.class_id');
        $this->db->join('sections', 'sections.id= student_session.section_id');
        $this->db->join('students', 'students.id=student_session.student_id');
        $this->db->order_by('student_fees_deposite.id');
        $query = $this->db->get();
        $result_value = $query->result();
        $return_array = array();
        if (!empty($result_value)) {
            $st_date = strtotime($start_date);
            $ed_date = strtotime($end_date);
            foreach ($result_value as $key => $value) {
                $return = $this->findObjectById($value, $st_date, $ed_date);
                if (!empty($return)) {
                    foreach ($return as $r_key => $r_value) {
                        $a['id'] = $value->id;
                        $a['student_fees_master_id'] = $value->student_fees_master_id;
                        $a['fee_groups_feetype_id'] = $value->fee_groups_feetype_id;
                        $a['firstname'] = $value->firstname;
                        $a['lastname'] = $value->lastname;
                        $a['class_id'] = $value->class_id;
                        $a['class'] = $value->class;
                        $a['section'] = $value->section;
                        $a['section_id'] = $value->section_id;
                        $a['student_id'] = $value->student_id;
                        $a['name'] = $value->name;
                        $a['type'] = $value->type;
                        $a['code'] = $value->code;
                        $a['student_session_id'] = $value->student_session_id;
                        $a['amount'] = $r_value->amount;
                        $a['date'] = $r_value->date;
                        $a['amount_discount'] = $r_value->amount_discount;
                        $a['amount_fine'] = $r_value->amount_fine;
                        $a['description'] = $r_value->description;
                        $a['payment_mode'] = $r_value->payment_mode;
                        $a['inv_no'] = $r_value->inv_no;
                        $a['invoice_amount'] = floatval($value->invoice_amount);
                        $return_array[] = $a;
                    }
                }
            }
        }
               usort($return_array, function($a, $b) {
        return strtotime($a['date']) - strtotime($b['date']);
    });

        return $return_array;
    }    
    
         public function getoutstandingFeeBetweenDate($start_date, $end_date) {
		$transfer = 0;
        $this->db->select('`student_fees_deposite`.*,student_fees_deposite.amount_detail,student_fees_deposite.invoice,students.firstname,students.lastname,student_session.class_id,classes.class,
		sections.section,fee_groups_feetype.amount as invoice_amount,fee_groups_feetype.due_date,student_session.section_id,student_session.student_id,`fee_groups`.`name`, `feetype`.`type`, `feetype`.
		`code`,student_fees_master.student_session_id')->from('student_fees_deposite')->where('student_fees_deposite.transfer', $transfer);
        $this->db->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id');
        $this->db->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id');
        $this->db->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id');
        $this->db->join('student_fees_master', 'student_fees_master.id=student_fees_deposite.student_fees_master_id');
        $this->db->join('student_session', 'student_session.id= student_fees_master.student_session_id');
        $this->db->join('classes', 'classes.id= student_session.class_id');
        $this->db->join('sections', 'sections.id= student_session.section_id');
        $this->db->join('students', 'students.id=student_session.student_id');
        $this->db->order_by('student_fees_deposite.created_at', 'ASC');
        $query = $this->db->get();
        $result_value = $query->result();
        $return_array = array();
        if (!empty($result_value)) {
            $st_date = strtotime($start_date);
            $ed_date = strtotime($end_date);
            foreach ($result_value as $key => $value) {
                $return = $this->findObjectById($value, $st_date, $ed_date);
                if (!empty($return)) {
                    foreach ($return as $r_key => $r_value) {
                        $a['id'] = $value->id;
                        $a['student_fees_master_id'] = $value->student_fees_master_id;
                        $a['fee_groups_feetype_id'] = $value->fee_groups_feetype_id;
                        $a['firstname'] = $value->firstname;
                        $a['lastname'] = $value->lastname;
                        $a['class_id'] = $value->class_id;
                        $a['class'] = $value->class;
                        $a['section'] = $value->section;
                        $a['section_id'] = $value->section_id;
                        $a['student_id'] = $value->student_id;
                        $a['name'] = $value->name;
                        $a['type'] = $value->type;
                        $a['code'] = $value->code;
                        $a['due_date'] = $value->due_date;
                        $a['student_session_id'] = $value->student_session_id;
                        $a['amount'] = $r_value->amount;
                        $a['date'] = $r_value->date;
                        $a['amount_discount'] = $r_value->amount_discount;
                        $a['amount_fine'] = $r_value->amount_fine;
                        $a['description'] = $r_value->description;
                        $a['payment_mode'] = $r_value->payment_mode;
                        $a['inv_no'] = $r_value->inv_no;
                        $a['invoice_amount'] = floatval($value->invoice_amount);
                        $return_array[] = $a;
                    }
                }
            }
        }
           usort($return_array, function($a, $b) {
        return strtotime($a['date']) - strtotime($b['date']);
    });


        return $return_array;
    }    
    public function getInitialOutstandingFeeBetweenDate($start_date, $end_date) {
    $this->db->select('
        students.id as student_id,
        students.firstname,
        students.lastname,
        classes.id as class_id,
        classes.class,
        sections.id as section_id,
        sections.section,
        fee_groups.name as name,
        feetype.type as type,
        feetype.code as code,
        fee_groups_feetype.amount as invoice_amount,
        fee_groups_feetype.due_date,
        student_session.id as student_session_id,
        student_fees_master.id as student_fees_master_id,
        fee_groups_feetype.id as fee_groups_feetype_id
    ');
    $this->db->from('student_session');
    $this->db->join('students', 'students.id = student_session.student_id');
    $this->db->join('classes', 'classes.id = student_session.class_id');
    $this->db->join('sections', 'sections.id = student_session.section_id');
    $this->db->join('student_fees_master', 'student_fees_master.student_session_id = student_session.id');
    $this->db->join('fees_session_groups', 'fees_session_groups.id = student_fees_master.fees_session_group_id');
    $this->db->join('fee_groups', 'fee_groups.id = fees_session_groups.fee_group_id');
    $this->db->join('fee_groups_feetype', 'fee_groups_feetype.fee_groups_id = fee_groups.id');
    $this->db->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id');
    $this->db->order_by('student_fees_master.created_at', 'ASC');

    $query = $this->db->get();
    $fee_obligations = $query->result();

    $st_date = strtotime($start_date);
    $ed_date = strtotime($end_date);
    $return_array = [];

    foreach ($fee_obligations as $obligation) {
        $due_date = strtotime($obligation->due_date);
        if ($due_date >= $st_date && $due_date <= $ed_date) {
            $a['student_id'] = $obligation->student_id;
            $a['firstname'] = $obligation->firstname;
            $a['lastname'] = $obligation->lastname;
            $a['class_id'] = $obligation->class_id;
            $a['class'] = $obligation->class;
            $a['section'] = $obligation->section;
            $a['section_id'] = $obligation->section_id;
            $a['name'] = $obligation->name;
            $a['type'] = $obligation->type;
            $a['code'] = $obligation->code;
            $a['due_date'] = $obligation->due_date;
            $a['student_session_id'] = $obligation->student_session_id;
            $a['student_fees_master_id'] = $obligation->student_fees_master_id;
             $a['student_fees_master_id'] = $obligation->created_at;
            $a['fee_groups_feetype_id'] = $obligation->fee_groups_feetype_id;
            $a['invoice_amount'] = floatval($obligation->invoice_amount);
            $a['amount'] = 0; // No amount paid yet
            $a['created_at'] = $obligation->created_at; // Default to due_date for sorting
            $a['amount_discount'] = 0;
            $a['amount_fine'] = 0;
            $a['description'] = '';
            $a['payment_mode'] = '';
            $a['inv_no'] = '';

            $return_array[] = $a;
        }
    }

    usort($return_array, function($a, $b) {
        return strtotime($a['created_at']) - strtotime($b['created_at']);
    });

    return $return_array;
}


    public function getFeeBetweenDateCurrency($start_date, $end_date, $payment_mode='Default', $level_from = null, $level_to = null)
    {
        $this->db->select('student_fees_deposite.*, student_fees_deposite.amount_detail, student_fees_deposite.invoice,
            students.firstname, students.lastname, student_session.class_id, classes.class,
            sections.section, student_session.section_id, student_session.student_id,
            fee_groups.name, feetype.type, feetype.code, student_fees_master.student_session_id')
            ->from('student_fees_deposite');

        $this->db->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id');
        $this->db->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id');
        $this->db->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id');
        $this->db->join('student_fees_master', 'student_fees_master.id = student_fees_deposite.student_fees_master_id');
        $this->db->join('student_session', 'student_session.id = student_fees_master.student_session_id');
        $this->db->join('classes', 'classes.id = student_session.class_id');
        $this->db->join('sections', 'sections.id = student_session.section_id');
        $this->db->join('students', 'students.id = student_session.student_id');

        // LEVEL FILTER
        if (!empty($level_from) && !empty($level_to)) {
            $this->db->where('student_session.class_id >=', $level_from);
            $this->db->where('student_session.class_id <=', $level_to);
        }

        $query = $this->db->get();
        $result_value = $query->result();
        $return_array = array();

        if (!empty($result_value)) {
            $st_date = strtotime($start_date);
            $ed_date = strtotime($end_date);

            foreach ($result_value as $key => $value) {

                $return = $this->findfeesCollectionById($value, $st_date, $ed_date);

                if (!empty($return)) {
                    foreach ($return as $r_key => $r_value) {

                        // Payment Mode Filter
                        if ($payment_mode !== 'Default' && $r_value->payment_mode !== $payment_mode) {
                            continue;
                        }

                        $a['id'] = $value->id;
                        $a['student_fees_master_id'] = $value->student_fees_master_id;
                        $a['fee_groups_feetype_id'] = $value->fee_groups_feetype_id;
                        $a['firstname'] = $value->firstname;
                        $a['lastname'] = $value->lastname;
                        $a['class_id'] = $value->class_id;
                        $a['class'] = $value->class;
                        $a['section'] = $value->section;
                        $a['section_id'] = $value->section_id;
                        $a['student_id'] = $value->student_id;
                        $a['name'] = $value->name;
                        $a['type'] = $value->type;
                        $a['code'] = $value->code;
                        $a['student_session_id'] = $value->student_session_id;
                        $a['amount'] = $r_value->amount;
                        $a['date'] = $r_value->date;
                        $a['amount_discount'] = $r_value->amount_discount;
                        $a['amount_fine'] = $r_value->amount_fine;
                        $a['description'] = $r_value->description;
                        $a['payment_mode'] = $r_value->payment_mode;
                        $a['inv_no'] = $r_value->inv_no;

                        $return_array[] = $a;
                    }
                }
            }
        }

        usort($return_array, function($a, $b) {
            return strtotime($a['date']) - strtotime($b['date']);
        });

        return $return_array;
    }
    
            public function customer_income($start_date, $end_date) {
        
                $this->db->select('*');
                $this->db->from('customer_invoice');
                $this->db->where('inv_date >= ', $start_date);
                $this->db->where('inv_date <= ', $end_date);
                $query = $this->db->get();
                    
                return $query;
            }


            public function getAlljournals($start_date, $end_date) {
        
                $this->db->select('*');
                $this->db->from('journal');
                $this->db->where('date >= ', $start_date);
                $this->db->where('date <= ', $end_date);
                $query = $this->db->get();
                    
                return $query;
            }    
            public function getAlluniforms($start_date, $end_date) {
        
                $this->db->select('*');
                $this->db->from('uniformpos');
                $this->db->where('created_date >= ', $start_date);
                $this->db->where('created_date <= ', $end_date);
                $query = $this->db->get();
                    
                return $query;
            }
            public function getcos($start_date, $end_date) {
        
                $this->db->select('*');
                $this->db->from('item_stock');
                $this->db->where('date >= ', $start_date);
                $this->db->where('date <= ', $end_date);
                $query = $this->db->get();
                    
                return $query;
            }

            public function getAllsupliers($start_date, $end_date) {
        
                $this->db->select('*');
                $this->db->from('supplier_invoice');
                $this->db->where('inv_date >= ', $start_date);
                $this->db->where('inv_date <= ', $end_date);
                $query = $this->db->get();
                    
                return $query;
            }
            public function getAllexpenses($start_date, $end_date) {
                $this->db->select('
                    cashbook.*, 
                    glledger_account.gl_code, 
                    glledger_account.name as gl_name, 
                    glledger_account.account_type as gl_account_type,
                    glsub_account.glsub_code,
                    glsub_account.name as sub_name,
                    glsub_account.account_type as sub_account_type,
                    parent_gl.account_type AS parent_account_type
                ')
                ->from('cashbook')
                ->join('glledger_account', 'cashbook.account = glledger_account.name', 'left')
                ->join('glsub_account', 'cashbook.account = glsub_account.name', 'left')
                ->join('glledger_account AS parent_gl', 'glsub_account.account_type = parent_gl.gl_code', 'left')
                ->where('cashbook.date >=', $start_date)
                ->where('cashbook.date <=', $end_date);
            
                return $this->db->get();
            }
            public function getAllassets($start_date, $end_date) {
                $this->db->select('
                    journal.*, 
                    glledger_account.gl_code, 
                    glledger_account.name as gl_name, 
                    glledger_account.account_type as gl_account_type,
                    glsub_account.glsub_code,
                    glsub_account.name as sub_name,
                    glsub_account.account_type as sub_account_type,
                    parent_gl.account_type AS parent_account_type
                ')
                ->from('journal')
                ->join('glledger_account', 'journal.account = glledger_account.name', 'left')
                ->join('glsub_account', 'journal.account = glsub_account.name', 'left')
                ->join('glledger_account AS parent_gl', 'glsub_account.account_type = parent_gl.gl_code', 'left')
                ->where('journal.date >=', $start_date)
                ->where('journal.date <=', $end_date);
            
                return $this->db->get();
            }

        
            public function getOtherassets($start_date, $end_date) {
                return $this->db
                    ->select('
                        assets.description,
                        assets.purchase_price
                    ')
                    ->from('assets')
                    ->where('assets.date_captured >=', $start_date)
                    ->where('assets.date_captured <=', $end_date)
                    ->get();
            }


            public function getFeesummary($start_date, $end_date) {
                $this->db->select('
                    student_fees_master.*,
                    student_fees_master.id,
                    student_fees_master.created_at as invoice_date,
                    fee_groups_feetype.amount as invoice_amount,
                    fee_groups.name as fee_group_name,
                    feetype.type as fee_type,
                    students.firstname,
                    students.lastname,
                    classes.class,
                    sections.section,
                    glledger_account.gl_code,
                    glledger_account.name as gl_account_name,
                    glledger_account.account_type
                ');
                
                $this->db->from('student_fees_master');
                $this->db->join('fee_session_groups', 'fee_session_groups.id = student_fees_master.fee_session_group_id');
                $this->db->join('fee_groups', 'fee_groups.id = fee_session_groups.fee_groups_id');
                $this->db->join('fee_groups_feetype', 'fee_groups_feetype.fee_groups_id = fee_groups.id', 'left');
                $this->db->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id', 'left');
                $this->db->join('student_session', 'student_session.id = student_fees_master.student_session_id');
                $this->db->join('students', 'students.id = student_session.student_id');
                $this->db->join('classes', 'classes.id = student_session.class_id');
                $this->db->join('sections', 'sections.id = student_session.section_id');
                
                // Join with glledger_account using a linking field.
                // Adjust the join condition if you are using a different field.
                $this->db->join('glledger_account', 'student_fees_master.id = glledger_account.gl_code', 'left');
                
                $this->db->where('student_fees_master.created_at >=', $start_date);
                $this->db->where('student_fees_master.created_at <=', $end_date); 
                $this->db->order_by('student_fees_master.created_at', 'DESC');
                $query = $this->db->get();
                
                return $query->result_array();
            }
            
            public function getUDFhistoryFeeBetweenDate($start_date, $end_date) {
                $transfer = 1;
                $this->db->select('`student_fees_deposite`.*,student_fees_deposite.amount_detail,student_fees_deposite.invoice,students.firstname,students.lastname,student_session.class_id,classes.class,
                sections.section,student_session.section_id,student_session.student_id,`fee_groups`.`name`, `feetype`.`type`, `feetype`.
                `code`,student_fees_master.student_session_id')->from('student_fees_deposite');
                $this->db->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id');

                $this->db->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id');
                $this->db->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id');
                $this->db->join('student_fees_master', 'student_fees_master.id=student_fees_deposite.student_fees_master_id');
                $this->db->join('student_session', 'student_session.id= student_fees_master.student_session_id');
                $this->db->join('classes', 'classes.id= student_session.class_id');
                $this->db->join('sections', 'sections.id= student_session.section_id');
                $this->db->join('students', 'students.id=student_session.student_id');
                $this->db->order_by('student_fees_deposite.id');
                $query = $this->db->get();
                $result_value = $query->result();
                $return_array = array();
                if (!empty($result_value)) {
                    $st_date = strtotime($start_date);
                    $ed_date = strtotime($end_date);
                    foreach ($result_value as $key => $value) {
                        $return = $this->findObjectById($value, $st_date, $ed_date);
                        if (!empty($return)) {
                            foreach ($return as $r_key => $r_value) {
                                $a['id'] = $value->id;
                                $a['student_fees_master_id'] = $value->student_fees_master_id;
                                $a['fee_groups_feetype_id'] = $value->fee_groups_feetype_id;
                                $a['firstname'] = $value->firstname;
                                $a['lastname'] = $value->lastname;
                                $a['class_id'] = $value->class_id;
                                $a['class'] = $value->class;
                                $a['section'] = $value->section;
                                $a['section_id'] = $value->section_id;
                                $a['student_id'] = $value->student_id;
                                $a['name'] = $value->name;
                                $a['type'] = $value->type;
                                $a['code'] = $value->code;
                                $a['student_session_id'] = $value->student_session_id;
                                $a['amount'] = $r_value->amount;
                                $a['date'] = $r_value->date;
                                $a['amount_discount'] = $r_value->amount_discount;
                                $a['amount_fine'] = $r_value->amount_fine;
                                $a['description'] = $r_value->description;
                                $a['payment_mode'] = $r_value->payment_mode;
                                $a['inv_no'] = $r_value->inv_no;
                                $return_array[] = $a;
                            }
                        }
                    }
                }

                return $return_array;
            }

            public function getDepositAmountBetweenDate($start_date, $end_date) {

                $this->db->select('`student_fees_deposite`.*')->from('student_fees_deposite');
                $this->db->order_by('student_fees_deposite.id');
                $query = $this->db->get();
                $result_value = $query->result();

                $return_array = array();
                if (!empty($result_value)) {
                    $st_date = strtotime($start_date);
                    $ed_date = strtotime($end_date);
                    foreach ($result_value as $key => $value) {
                        $return = $this->findObjectById($value, $st_date, $ed_date);

                        if (!empty($return)) {
                            foreach ($return as $r_key => $r_value) {
                                $a = array();
                                $a['amount'] = $r_value->amount;
                                $a['date'] = $r_value->date;
                                $a['amount_discount'] = $r_value->amount_discount;
                                $a['amount_fine'] = $r_value->amount_fine;
                                $a['description'] = $r_value->description;
                                $a['payment_mode'] = $r_value->payment_mode;
                                $a['inv_no'] = $r_value->inv_no;
                                $return_array[] = $a;
                            }
                        }
                    }
                }

                return $return_array;
            }

            public function select() {
                $this->db->select('*');
                $this->db->from('glledger_account');
                $query = $this->db->get();
                
                return $query;
            }
            
            


            function findObjectAmount($array, $st_date, $ed_date) {

                $ar = json_decode($array->amount_detail);
                $array = array();
                $amount = 0;
                for ($i = $st_date; $i <= $ed_date; $i += 86400) {
                    $find = date('Y-m-d', $i);
                    foreach ($ar as $row_key => $row_value) {
                        if ($row_value->date == $find) {

                            $array[] = $row_value;
                        }
                    }
                }
                return $array;
            }

            function findObjectById($array, $st_date, $ed_date) {

                $ar = json_decode($array->amount_detail);
                $array = array();
                for ($i = $st_date; $i <= $ed_date; $i += 86400) {
                    $find = date('Y-m-d', $i);
                    foreach ($ar as $row_key => $row_value) {
                        if ($row_value->date == $find) {
                            $array[] = $row_value;
                        }
                    }
                }
                return $array;
            }
                        function findfeesCollectionById($array, $st_date, $ed_date) {

                $ar = json_decode($array->amount_detail);
                $array = array();
                for ($i = $st_date; $i <= $ed_date; $i += 86400) {
                    $find = date('Y-m-d', $i);
                    foreach ($ar as $row_key => $row_value) {
                        if ($row_value->date == $find) {
                            $array[] = $row_value;
                        }
                    }
                }
                return $array;
            }
            
            function findOustandingById($array, $st_date, $ed_date) {

                $ar = json_decode($array->amount_detail);
                $array = array();
                for ($i = $st_date; $i <= $ed_date; $i += 86400) {
                    $find = date('Y-m-d', $i);
                    foreach ($ar as $row_key => $row_value) {
                        if ($row_value->date == $find) {
                            $array[] = $row_value;
                        }
                    }
                }
                return $array;
            }
            public function getFeeByInvoice($invoice_id, $sub_invoice_id) {
                $this->db->select('`student_fees_deposite`.*,students.firstname,students.lastname,student_session.class_id,classes.class,sections.section,student_session.section_id,student_session.student_id,`fee_groups`.`name`, `feetype`.`type`, `feetype`.`code`,student_fees_master.student_session_id')->from('student_fees_deposite');
                $this->db->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id');

                $this->db->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id');
                $this->db->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id');
                $this->db->join('student_fees_master', 'student_fees_master.id=student_fees_deposite.student_fees_master_id');
                $this->db->join('student_session', 'student_session.id= student_fees_master.student_session_id');
                $this->db->join('classes', 'classes.id= student_session.class_id');

                $this->db->join('sections', 'sections.id= student_session.section_id');
                $this->db->join('students', 'students.id=student_session.student_id');
                $this->db->where('student_fees_deposite.id', $invoice_id);
                $q = $this->db->get();


                if ($q->num_rows() > 0) {
                    $result = $q->row();
                    $res = json_decode($result->amount_detail);
                    $a = (array) $res;

                    foreach ($a as $key => $value) {
                        if ($key == $sub_invoice_id) {

                            return $result;
                        }
                    }
                }


                return false;
            }

            public function studentDeposit($data) {
                $sql = "SELECT fee_groups.is_system,student_fees_master.amount as `student_fees_master_amount`, fee_groups.name as `fee_group_name`,feetype.code as `fee_type_code`,fee_groups_feetype.amount,IFNULL(student_fees_deposite.amount_detail,0) as `amount_detail` from student_fees_master 
                    INNER JOIN fee_session_groups on fee_session_groups.id=student_fees_master.fee_session_group_id 
                    INNER JOIN fee_groups_feetype on fee_groups_feetype.fee_groups_id=fee_session_groups.fee_groups_id
                    INNER JOIN fee_groups on fee_groups_feetype.fee_groups_id=fee_groups.id
                    INNER JOIN feetype on fee_groups_feetype.feetype_id=feetype.id
                LEFT JOIN student_fees_deposite on student_fees_deposite.student_fees_master_id=student_fees_master.id and student_fees_deposite.fee_groups_feetype_id=fee_groups_feetype.id WHERE student_fees_master.id =" . $data['student_fees_master_id'] . " and fee_groups_feetype.id =" . $data['fee_groups_feetype_id'];
                $query = $this->db->query($sql);

                return $query->row();
            }

            public function getPreviousStudentFees($student_session_id) {
                $sql = "SELECT `student_fees_master`.*,fee_groups.name FROM `student_fees_master` INNER JOIN fee_session_groups on student_fees_master.fee_session_group_id=fee_session_groups.id INNER JOIN fee_groups on fee_groups.id=fee_session_groups.fee_groups_id  WHERE `student_session_id` = " . $student_session_id . " ORDER BY `student_fees_master`.`id`";
                $query = $this->db->query($sql);
                $result = $query->result();
                if (!empty($result)) {
                    foreach ($result as $result_key => $result_value) {
                        $fee_session_group_id = $result_value->fee_session_group_id;
                        $student_fees_master_id = $result_value->id;
                        $result_value->fees = $this->getDueFeeByFeeSessionGroup($fee_session_group_id, $student_fees_master_id);

                        if ($result_value->is_system != 0) {
                            $result_value->fees[0]->amount = $result_value->amount;
                        }
                    }
                }

                return $result;
            }
 public function getCash($start_date, $end_date) {
        $this->db->select('*')
                 ->from('cashbook')
                 ->where('date_captured >= ', $start_date)
                 ->where('date_captured <= ', $end_date);
        return $this->db->get();
    }



   private function get_previous_year_range($date) {
    $year = date('Y', strtotime($date));
    $prev_year = $year - 1;
    return [
        'start' => $prev_year . '-01-01',
        'end' => $prev_year . '-12-31'
    ];
}

 public function getFeeBetweenDate_upto($end_date) {
        $range = $this->get_previous_year_range($end_date);
        
        $this->db->select('
            student_fees_deposite.id,
            students.firstname,
            students.lastname,
            classes.class,
            sections.section,
            fee_groups.name AS fee_group_name,
            feetype.type AS fee_type,
            SUM(JSON_EXTRACT(student_fees_deposite.amount_detail, "$.amount")) as amount,
            SUM(JSON_EXTRACT(student_fees_deposite.amount_detail, "$.amount_discount")) as amount_discount,
            SUM(JSON_EXTRACT(student_fees_deposite.amount_detail, "$.amount_fine")) as amount_fine
        ')
        ->from('student_fees_deposite')
        ->where('student_fees_deposite.transfer', 0)
        ->where("DATE(student_fees_deposite.created_at) BETWEEN '{$range['start']}' AND '{$range['end']}'")
        ->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id')
        ->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id')
        ->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id')
        ->join('student_fees_master', 'student_fees_master.id = student_fees_deposite.student_fees_master_id')
        ->join('student_session', 'student_session.id = student_fees_master.student_session_id')
        ->join('classes', 'classes.id = student_session.class_id')
        ->join('sections', 'sections.id = student_session.section_id')
        ->join('students', 'students.id = student_session.student_id')
        ->group_by('student_fees_deposite.id')
        ->order_by('student_fees_deposite.id', 'DESC');

        return $this->db->get()->result_array();
    }
public function getFeeBetweenDateCurrency_upto($end_date, $payment_mode = null) {
    // Get previous year date range using helper function
    $range = $this->get_previous_year_range($end_date);

    $this->db->select('
        student_fees_deposite.id,
        student_fees_deposite.fee_groups_feetype_id,
        student_fees_deposite.student_fees_master_id,
        students.firstname,
        students.lastname,
        student_session.class_id,
        classes.class,
        sections.section,
        student_session.section_id,
        student_session.student_id,
        fee_groups.name AS name,
        feetype.type AS type,
        feetype.code,
        student_fees_master.student_session_id,
        JSON_UNQUOTE(JSON_EXTRACT(student_fees_deposite.amount_detail, "$.inv_no")) as inv_no,
        JSON_UNQUOTE(JSON_EXTRACT(student_fees_deposite.amount_detail, "$.date")) as date,
        JSON_EXTRACT(student_fees_deposite.amount_detail, "$.amount") as amount,
        JSON_EXTRACT(student_fees_deposite.amount_detail, "$.amount_discount") as amount_discount,
        JSON_EXTRACT(student_fees_deposite.amount_detail, "$.amount_fine") as amount_fine,
        JSON_UNQUOTE(JSON_EXTRACT(student_fees_deposite.amount_detail, "$.description")) as description,
        JSON_UNQUOTE(JSON_EXTRACT(student_fees_deposite.amount_detail, "$.payment_mode")) as payment_mode
    ')
    ->from('student_fees_deposite')
    ->where('student_fees_deposite.transfer', 0)
    ->where("DATE(student_fees_deposite.created_at) BETWEEN '{$range['start']}' AND '{$range['end']}'")
    ->join('fee_groups_feetype', 'fee_groups_feetype.id = student_fees_deposite.fee_groups_feetype_id')
    ->join('fee_groups', 'fee_groups.id = fee_groups_feetype.fee_groups_id')
    ->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id')
    ->join('student_fees_master', 'student_fees_master.id = student_fees_deposite.student_fees_master_id')
    ->join('student_session', 'student_session.id = student_fees_master.student_session_id')
    ->join('classes', 'classes.id = student_session.class_id')
    ->join('sections', 'sections.id = student_session.section_id')
    ->join('students', 'students.id = student_session.student_id');

    if ($payment_mode) {
        $this->db->having('payment_mode', $payment_mode);
    }

    $query = $this->db->get();
    return $query->result_array();
}public function customer_income_upto($end_date) {
    $range = $this->get_previous_year_range($end_date);
    $this->db->select('*')
        ->from('customer_invoice')
        ->where('inv_date >=', $range['start'])
        ->where('inv_date <=', $range['end']);
    return $this->db->get();
}

public function getAlljournals_upto($end_date) {
    $range = $this->get_previous_year_range($end_date);
    $this->db->select('*')
        ->from('journal')
        ->where('date >=', $range['start'])
        ->where('date <=', $range['end']);
    return $this->db->get();
}

public function getAlluniforms_upto($end_date) {
    $range = $this->get_previous_year_range($end_date);
    $this->db->select('*')
        ->from('uniformpos')
        ->where('created_date >=', $range['start'])
        ->where('created_date <=', $range['end']);
    return $this->db->get();
}

public function getcos_upto($end_date) {
    $range = $this->get_previous_year_range($end_date);
    $this->db->select('*')
        ->from('item_stock')
        ->where('date >=', $range['start'])
        ->where('date <=', $range['end']);
    return $this->db->get();
}

public function getAllsupliers_upto($end_date) {
    $range = $this->get_previous_year_range($end_date);
    $this->db->select('*')
        ->from('supplier_invoice')
        ->where('inv_date >=', $range['start'])
        ->where('inv_date <=', $range['end']);
    return $this->db->get();
}

public function getAllexpenses_upto($end_date) {
    // Use the private helper to get previous year's date range
    $range = $this->get_previous_year_range($end_date);

    $this->db->select('
        cashbook.*, 
        glledger_account.gl_code, 
        glledger_account.name as gl_name,
        glledger_account.id as gl_id, 
        glledger_account.account_type as gl_account_type,
        glsub_account.glsub_code,
        glsub_account.name as sub_name,
        glsub_account.id as sub_id,
        glsub_account.account_type as sub_account_type,
        parent_gl.account_type AS parent_account_type
    ')
    ->from('cashbook')
    ->join('glledger_account', 'cashbook.account = glledger_account.id', 'left')
    ->join('glsub_account', 'cashbook.account = glsub_account.id', 'left')
    ->join('glledger_account AS parent_gl', 'glsub_account.account_type = parent_gl.gl_code', 'left')
    ->where('cashbook.date >=', $range['start'])
    ->where('cashbook.date <=', $range['end']);

    return $this->db->get();
}
public function getFeesummary_upto($end_date) {
    $range = $this->get_previous_year_range($end_date);

    $this->db->select('
        student_fees_master.*,
        student_fees_master.id,
        student_fees_master.created_at as invoice_date,
        fee_groups_feetype.amount as invoice_amount,
        fee_groups.name as fee_group_name,
        feetype.type as fee_type,
        students.firstname,
        students.lastname,
        classes.class,
        sections.section,
        glledger_account.gl_code,
        glledger_account.name as gl_account_name,
        glledger_account.account_type
    ')
    ->from('student_fees_master')
    ->join('fee_session_groups', 'fee_session_groups.id = student_fees_master.fee_session_group_id')
    ->join('fee_groups', 'fee_groups.id = fee_session_groups.fee_groups_id')
    ->join('fee_groups_feetype', 'fee_groups_feetype.fee_groups_id = fee_groups.id', 'left')
    ->join('feetype', 'feetype.id = fee_groups_feetype.feetype_id', 'left')
    ->join('student_session', 'student_session.id = student_fees_master.student_session_id')
    ->join('students', 'students.id = student_session.student_id')
    ->join('classes', 'classes.id = student_session.class_id')
    ->join('sections', 'sections.id = student_session.section_id')
    ->join('glledger_account', 'student_fees_master.id = glledger_account.gl_code', 'left')
    ->where('student_fees_master.created_at >=', $range['start'])
    ->where('student_fees_master.created_at <=', $range['end'])
    ->order_by('student_fees_master.created_at', 'DESC');

    return $this->db->get()->result_array();
}


}

