Math in table

Hello guys, today I wanted to do some maths in a table but, it ain't pleasure...

Basically, I have students, and they have to pay the courses but they can pay later, so in my db i have an "amount" colums, and I want to put a system who can subtract the amount with the deposit and dynamically.

So I have to display subtraction of "amount" and "deposit" in the payment list
AND I have to make sure that every deposit is summed with the previous.

but the template that i'm working on isn't the clearest, and my problems are, I don't know where i can create the result of the substrate and how to display it.

And i don't know how sum data in a db with CI :/


here some functions in the model

this one store the data

PHP Code:

public function storeStudentDue($edit = null, $data = array())
    {
        $data = $data ? $data : $this->xssCleanInput();
        if ($edit) {
            $this->db->where('student_due_id', $edit);
            $data['updated_at'] = date('Y-m-d G:i:s');
            $this->db->update('student_dues', $data);
            $id = $data['student_due_id'];
        } else {
            $data['created_at'] = date('Y-m-d G:i:s');
            $data['created_by'] = adminSession() ? adminSession() : 1;
            if ($data['type'] == 'program') {
                $this->db->where('program_id', $data['program_id']);
            } elseif ($data['type'] == 'semester') {
                $this->db->where('semester_id', $data['semester_id']);
            } else {
                $this->db->where('course_id', $data['course_id']);
            }
            $this->db->where('student_id', $data['student_id']);
            $result = $this->db->get('student_dues');
            if ($result->num_rows() <= 0) {
                $this->db->insert('student_dues', $data);
            }
            $id = $this->db->insert_id();
            return $id;
        }
    } 


Those 2 functions are here to display dynamically the result of the table.

PHP Code:

public function getForDatatable()
    {
        $request = $this->input->get();
        $columns = array(
            "",
            "student_detail",
            "",
            "student_dues.amount",
            "student_dues.versement",
            "student_dues.type",
            "student_dues.description",
            "student_dues.due_date",
            "student_dues.created_at",
            "student_dues.is_paid",
        );
        $orderColumn = $columns[($request['order'][0]['column'] == 0 ? 5 : $request['order'][0]['column'])];
        $orderDirection = $request['order'][0]['dir'];
        $srh = $request['search']['value'];
        $limit = $request['length'];
        $offset = $request['start'];

        $this->db->from('student_dues');
        $this->db->select('
            student_dues.*,
            programs.title as program_title,
            semesters.title as semester_title,
            courses.title as course_title,
            CONCAT('
                .CE_DB_PREFIX.'students.first_name, 
                " ", 
                '.CE_DB_PREFIX.'students.last_name,
                " (", 
                '.CE_DB_PREFIX.'students.email,
                ") - (", 
                '.CE_DB_PREFIX.'student_batches.title,
                ")"
            ) as student_detail
        ');
        if ($srh) {
            $this->db->group_start()
                ->like('students.first_name', $srh)
                ->or_like('students.last_name', $srh)
                ->or_like('students.email', $srh)
                ->or_like('courses.title', $srh)
                ->group_end();
        }
        if (isset($request['is_paid']) && $request['is_paid'] != '') {
            $this->db->where('student_dues.is_paid', $request['is_paid']);
        }
        if (isset($request['type']) && $request['type'] != '') {
            $this->db->where('student_dues.type', $request['type']);
        }
        if (isset($request['program_id']) && $request['program_id'] != '') {
            $this->db->where('student_dues.program_id', $request['program_id']);
        }
        if (isset($request['semester_id']) && $request['semester_id'] != '') {
            $this->db->where('student_dues.semester_id', $request['semester_id']);
        }
        if (isset($request['course_id']) && $request['course_id'] != '') {
            $this->db->where('student_dues.course_id', $request['course_id']);
        }
        if (isset($request['student_batch_id']) && $request['student_batch_id'] != '') {
            $this->db->where('student_batches.student_batch_id', $request['student_batch_id']);
        }
        $this->db->join('programs', 'programs.program_id = student_dues.program_id', 'left');
        $this->db->join('semesters', 'semesters.semester_id = student_dues.semester_id', 'left');
        $this->db->join('courses', 'courses.course_id = student_dues.course_id', 'left');
        $this->db->join('students', 'students.student_id = student_dues.student_id', 'left');
        $this->db->join('student_batches', 'student_batches.student_batch_id = students.student_batch_id', 'left');
        $this->db->group_by("student_dues.student_due_id");
        $this->db->order_by($orderColumn, $orderDirection);
        $this->db->limit($limit, $offset);
        $query = $this->db->get();

        $result = array(
            'data' => $this->prepareDataForTable($query->result()),
            'recordsTotal' => $this->getTotal(),
            'recordsFiltered' => $this->getTotal($srh, $request),
        );

        return $result;
    } 


PHP Code:

private function prepareDataForTable($student_dues)
    {
        $sorted = array();
        foreach ($student_dues as $c) {
            $actions = '';
            $c = objToArr($c);
            if ($c['is_paid'] == 1) {
                $ip_button_text = lang('yes');
                $ip_button_class = 'success';
                $ip_button_title = lang('click_to_disable');
            } else {
                $ip_button_text = lang('no');
                $ip_button_class = 'warning';
                $ip_button_title = lang('click_to_enable');
            }
            
            if (allowedTo('edit_student_due')) {
            $actions .= '
                <button title="'.lang('edit').'" type="button" class="btn btn-primary btn-xs create-or-edit-student-due" data-id="'.$c['student_due_id'].'"><i class="far fa-edit"></i></button>
            ';
            }

            if (allowedTo('delete_student_due')) { 
            $actions .= '
                <button title="'.lang('delete').'" type="button" class="btn btn-danger btn-xs delete-student-due" data-id="'.$c['student_due_id'].'"><i class="far fa-trash-alt"></i></button>
            ';
            }

            $for = '';
            if ($c['program_title'] != '') { 
                $for .= $c['program_title'];
            } elseif ($c['semester_title'] != '') {
                $for .= $c['semester_title'];
            } else {
                $for .= $c['course_title'];
            }
            $for .= '</span>';
            $c['total']=$c['amount'] ;
            $sorted[] = array(
                "<input type='checkbox' class='minimal single-check' data-id='".$c['student_due_id']."' />",
                esc_output($c['student_detail'], 'html'),
                $for,
                esc_output($c['amount'], 'html').' '.setting('default-currency'),
                esc_output($c['versement'], 'html').' '.setting('default-currency'),
                $this->dueTypeDd($c['type']),
                '<span title="'.$c['description'].'">'.trimString($c['description']).'</span>',
                date('d M, Y', strtotime($c['due_date'])),
                date('d M, Y', strtotime($c['created_at'])),
                '<button type="button" class="btn btn-'.$ip_button_class.' btn-xs" data-status="'.$c['is_paid'].'" data-id="'.$c['student_due_id'].'">'.$ip_button_text.'</button>',
                $actions
            );
        }
        return $sorted;
    } 


And here, the StudentDues's controller, and I don't really understood how they work

PHP Code:

/**
     * View Function to display student_dues list view page
     *
     * @return html/string
     */
    public function listView()
    {
        $data['page'] = lang('student_dues');
        $data['menu'] = 'student_dues';

        $pagedata['programs'] = objToArr($this->AdminProgramModel->getAll());
        $pagedata['semesters'] = objToArr($this->AdminSemesterModel->getAll());
        $pagedata['courses'] = objToArr($this->AdminCourseModel->getAll());
        $pagedata['student_batches'] = objToArr($this->AdminStudentBatchModel->getAll());

        $this->load->view('admin/layout/header', $data);
        $this->load->view('admin/student-dues/list', $pagedata);
    }

    /**
     * Function to get data for student_dues jquery datatable
     *
     * @return json
     */
    public function data()
    {
        echo json_encode($this->AdminStudentDueModel->getForDatatable());
    } 


(I can add code if you want)

Huge thanks

@Mfn_ofc,

Some questions. What version of CI are you using? What version of MySQL are you using? Do you have direct access to the database? You could create a database view that will have all the necessary information that you need. Then in your code all you have to do is call the view.

@Mfn_ofc,

Some questions.  What version of CI are you using? What version of MySQL are you using?  Do you have direct access to the database?  You could create a database view that will have all the necessary information that you need.  Then in your code all you have to do is call the view.

Hi, I'm using the 3.1.11 version of CI and the 5.7.33 version of MySQL.  ( on this template https://codecanyon.net/item/class-ease-e...m/29109477 ). Yes I have a direct access to the DB. The problem is, i'm not expert with CI, so I don't really know how to do that. But i'll try the db view method. thx

I am also searching for the same problem solution.

Thanks for post