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
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)