How to do a calculation with a column in database and insert the result Codeigniter

Question

Basically i have to count the monthly meal allowance for each of the employee based on their monthly attendance, which only leave me with just one table, attendance table

The calculation for the meal allowance is pretty simple, i only need to count how many days the employee attended and calculate it with one number (this number should be dynamic or editable)

this is the example of the calculation:
number of attendance * 15.00 (editable number)

here's my data for attendance table

----------------------------------------------------------------------------
| attendance_id | emp_code | emp_name  |    date      | time_in | time_out |
|       1       |    ALB   |  Albert   | 2018.01.01   | 07.00   |  18.00   |
|       2       |    GlN   |  GLENN    | 2018.01.01   | 07.00   |  18.00   |
|       3       |    ALB   |  Albert   | 2018.01.02   | 07.00   |  18.00   |
|       4       |    GLN   |  GLENN    | 2018.01.02   | 07.00   |  18.00   |
|       5       |    ALB   |  Albert   | 2018.01.04   | 07.00   |  18.00   |
|       6       |    GLN   |  GLENN    | 2018.01.04   | 07.00   |  18.00   |
----------------------------------------------------------------------------

So far i already managed to count the monthly recap (by counting how many days they attended), this is my current condition:

------------------------------------------------
| emp_code   |   emp_name   |  days_attended    |
| ALB        |   Albert     |         3         |
| GLN        |   GLENN      |         3         |
-------------------------------------------------

This is my controller

    public function index(){
    $this->load->model('allowance_m');
    $data['query'] = $this->allowance_m->calculate();
    $this->load->vars($data);
    $this->load->view('allowance_v',$data);
    }

This is my model (allowance_m.php)

public function hitungabsen()
    {
        $this->db->select('attendance_id,emp_code,emp_name, COUNT(date) as days_attended');    
        $this->db->from('attendance');
        $this->db->group_by('emp_code');
        $query = $this->db->get();
        return $query->result();
    }

This is my view allowance_v.php

<table class="table table-striped table-bordered table-hover">
                <thead>
                    <tr>
                        <th><center>Employee Code</center></th>
                        <th><center>Employee Name</center></th>
                        <th><center>Days Attended</center></th>
                    </tr>
                </thead>

                <?php 
                    $no = 1;
                    foreach($query as $row){
                    ?>
                    <tr>
                        <td><?php echo $row->emp_code ?></td>
                        <td><?php echo $row->emp_name ?></td>
                        <td><?php echo $row->days_attended ?></td>
                    </tr>
                <?php } ?>          
                </table>

Now i really need to know how can i calculate the meal allowance with the inputted days attended, and after calculating the result, i need to insert the result into the database so that i can make a pdf report about that. the result should be like this:

------------------------------------------------------------------------
| emp_code   |   emp_name   |  days_attended    |     meal allowance    |
| ALB        |   Albert     |         3         |(automaticaly updated) |
| GLN        |   GLENN      |         3         |(automaticaly updated) |
------------------------------------------------------------------------

THANK YOU IN ADVANCE! Really need the answer as soon as possible, it means a lot for me

if meal allowance for a day is `15`, won't it be `15*3`, if the number of days attended by employee is 3? Am I getting your question clearly? — ujjwal verma, Jul 21 '18 at 03:27
"i need to insert the result into the database" don't you mean update not insert? the graph that follows implies everything has been handled — Alex, Jul 21 '18 at 03:41
the meal allowance hasn't been inserted to the database, so yeah i need to insert a new record or add a new column called meal allowance for the database. or maybe it should be called updating? sorry for my confusing statement :/ @Alex — Team, Jul 21 '18 at 03:45
you need to be a bit more clearer on your table structure. this is a separate table than attendance with those columns no? — Alex, Jul 21 '18 at 03:46
yes, it should be on different table with the attendance. it would be great if i can make a new table called "allowance table" that filled with emp_code, emp_name, days_attended, and the meal allowance. so basicaly its like a recap table for the meal allowance hehe — Team, Jul 21 '18 at 03:51
alright well if you make a table exactly like the last one you described im pretty sure the answer i wrote will work nicely — Alex, Jul 21 '18 at 03:53

score 0 · Accepted Answer · answered Jul 21 '18 at 03:49

0

Don't really understand everything but this should work:

public function calc() {
    $meal_allowance = array_column($this->db->select('emp_code')->get('tablename')->result_array(), 'emp_code');
    $n = 15;
    $results = $this->hitungabsen();
    $this->db->trans_start();
    foreach ($results as $row) {
        $calc = intval($row->days_attended) * $n;
        if (in_array($row->emp_code, $meal_allowance)) {
            $this->db->update('tablename', array('meal_allowance' => $calc));
        } else {
            $data = array(
                'emp_code' => $row->emp_code,
                'emp_name' => $row->emp_name,
                'days_attended' => $row->days_attended,
                'meal_allowance' => $calc
            );
            $this->db->insert('tablename', $data);
        }
    }
    $this->db->trans_complete();
    return $this->db->trans_status();
}

Updates if a record exists, inserts if it doesn't (assumed separate table with the specs in your last graph)

answered Jul 21 '18 at 03:49

Alex

9,215
8
39
82

dang that was fast. thank you so much, will try it on later. will give you the update Asap! :D – Team Jul 21 '18 at 03:52
fyi calc() function lives in the same model as the `hitungabsen` function – Alex Jul 21 '18 at 03:53
oh and btw, is there any chance that the $n could be filled by a form? since it has to be editable for the user? – Team Jul 21 '18 at 03:53
yea just pass it to the model function `calc($n)` through a post var in the controller (obviously remove the defined var I have there now) – Alex Jul 21 '18 at 03:54

How to do a calculation with a column in database and insert the result Codeigniter

1 Answers1