• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Math in table

#1
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'] == $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 Big Grin
Reply

#2
@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.
Reply

#3
(01-08-2021, 11:39 AM)php_rocs Wrote: @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
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.