File: /home/cafsindia/hrms_cafsindia_com/application/controllers/Arrears.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Arrears extends Action_controller{
public function __construct(){
parent::__construct('arrears');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->collect_base_info();
}
// LOAD PAGE DATA
public function index(){
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$process_role[""] = "---- Select Role ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$process_role[$role_id] = $category_name;
}
$data['process_role'] = $process_role;
$this->load->view("$this->control_name/manage",$data);
}
// AUTOCOMPLETE FOR SERACH EMPLOYEE
public function emp_suggest(){
$search_term = $this->input->post_get('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function get_arrear_cumulative(){
$process_month = $this->input->post('process_month');
$process_type = $this->input->post('process_type');
$process_emp_id = $this->input->post('process_emp_id');
$process_role = $this->input->post('process_role');
if($process_type == '1'){
$type_qry = 'and cw_arrear_cumulative.employee_code ="'.$process_emp_id.'"';
}else
if($process_type == '2'){
$type_qry = 'and cw_arrear_cumulative.category ="'.$process_role.'"';
}
if($process_month){//arrear column is mapped checking
$check_arr_map_qry = 'select count(prime_arrear_column_mapping_id) as rslt_count from cw_arrear_column_mapping where trans_status =1';
$check_arr_map_data = $this->db->query("CALL sp_a_run ('SELECT','$check_arr_map_qry')");
$check_arr_map_result = $check_arr_map_data->result();
$check_arr_map_data->next_result();
$arr_map_count = $check_arr_map_result[0]->rslt_count;
if((int)$arr_map_count === 0){
echo json_encode(array("success" => FALSE,'message' => "Arrear columns are not mapped!!!"));
exit(0);
}else{//increment is proceed for this month
$check_inc_qry = 'select count(prime_increment_id) as rslt_count from cw_increment where trans_status =1 and apply_on="'.$process_month.'"';
$check_inc_data = $this->db->query("CALL sp_a_run ('SELECT','$check_inc_qry')");
$check_inc_result = $check_inc_data->result();
$check_inc_data->next_result();
$inc_count = $check_inc_result[0]->rslt_count;
if((int)$inc_count === 0){
echo json_encode(array("success" => FALSE,'message' => "Increment is not proceed for this month!!!"));
exit(0);
}
}
}
$qry = 'select concat(cw_arrear_cumulative.employee_code,",",GROUP_CONCAT(DISTINCT(cw_employees.emp_name) ORDER BY emp_name asc)) as con ,GROUP_CONCAT(DISTINCT(arrear_column)ORDER by arrear_column asc) as arrear_column,GROUP_CONCAT(arrear_value ORDER by arrear_column asc) as arrear_value,increment_apply_month,effective_date,category from cw_arrear_cumulative inner join cw_employees on cw_employees.employee_code = cw_arrear_cumulative.employee_code where increment_apply_month ="'.$process_month.'" '.$type_qry.' group by cw_arrear_cumulative.employee_code';
$arrear_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$arrear_emp_result = $arrear_emp_info->result_array();
$arrear_emp_info->next_result();
$arrear_array = array();
foreach($arrear_emp_result as $emp){
$increment_apply_month = $emp['increment_apply_month'];
$effective_date = $emp['effective_date'];
$category = $emp['category'];
$column = explode(',',$emp['con']);
$emp_code = $column[0];
$emp_name = $column[1];
$arrear_column = explode(',',$emp['arrear_column']);
$array_value = explode(',',$emp['arrear_value']);
$column_value = array_combine($arrear_column,$array_value);
$arrear_array[$emp_code][$emp_name] = $column_value;
}
$role_qry = 'select GROUP_CONCAT(DISTINCT(cw_arrear_cumulative.arrear_column) ORDER BY arrear_column asc) as arrear_column,GROUP_CONCAT(cw_arrear_cumulative.arrear_value ORDER BY arrear_column asc) as arrear_value from cw_arrear_cumulative where increment_apply_month ="'.$process_month.'" '.$type_qry;
$arrear_role_info = $this->db->query("CALL sp_a_run ('SELECT','$role_qry')");
$arrear_role_result = $arrear_role_info->result_array();
$arrear_role_info->next_result();
$role_line = "";
$tbl_line = "";
$tfoot = "";
$arrear_column = explode(',',$arrear_role_result[0]['arrear_column']);
foreach($arrear_column as $column){
$role_line .= "<th scope='col'>$column</th>";
$tfoot .= "<th></th>";
$count = count($arrear_column);
}
foreach($arrear_array as $key =>$val){
foreach($val as $name =>$arrear){
$arr_td = "";
$total = 0;
foreach($arrear as $arr_val){
$arr_td .="<td>$arr_val</td>";
$total = $total + $arr_val;
}
$tbl_line .= "<tr onclick=view_arrear_info('$prime_cumulative_id','$increment_apply_month','$effective_date','$key','$category')><td>$key</td><td>$name</td>$arr_td<td style='color:red;'>$total</td></tr>";
}
}
$view_content = "<table class='table table-striped table-bordered' id='role'>
<thead>
<th scope='col'>Employee Code</th>
<th scope='col'>Employee Name</th>
$role_line
<th scope='col'>Total</th>
</thead>
$tbl_line
<tfoot>
<tr style='font-weight: bold; color: red;'>
<th></th>
<th></th>
$tfoot
<th></th>
</tr>
</tfoot>
</table>";
echo json_encode(array("success" => TRUE,'message' => $view_content,"count" => $count));
}
public function view_arrear_info(){
$prime_cumulative_id = $this->input->post('prime_cumulative_id');
$increment_month = $this->input->post('increment_apply_month');
$effect_date = $this->input->post('effective_date');
$emp_code = $this->input->post('key');
$cat = $this->input->post('category');
$qry = 'select prime_arrears_id,increment_apply_month,cw_employees.employee_code as employee_code,effective_date,transaction_column,arrear_column,arrear_month,arrear_value,arrear_pf,cw_arrears.paid_days as paid_days,arrear_date,category from cw_arrears inner join cw_employees on cw_employees.employee_code = cw_arrears.employee_code where increment_apply_month ="'.$increment_month.'" and effective_date ="'.$effect_date.'" and cw_employees.employee_code ="'.$emp_code.'" and cw_arrears.category ="'.$cat.'" order by arrear_date asc';
$arrear_info = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$arrear_result = $arrear_info->result();
$arrear_info->next_result();
foreach($arrear_result as $rslt){
$prime_arrears_id = $rslt->prime_arrears_id;
$increment_apply_month = $rslt->increment_apply_month;
$employee_code = $rslt->employee_code;
$effective_date = $rslt->effective_date;
$transaction_column = $rslt->transaction_column;
$arrear_column = $rslt->arrear_column;
$arrear_month = $rslt->arrear_month;
$arrear_value = $rslt->arrear_value;
$arrear_pf = $rslt->arrear_pf;
$paid_days = $rslt->paid_days;
$arrear_date = $rslt->arrear_date;
$tbl_line .= "<tr>
<td>$increment_apply_month</td>
<td>$employee_code</td>
<td>$effective_date</td>
<td>$transaction_column</td>
<td>$arrear_column</td>
<td>$arrear_month</td>
<td>$arrear_value</td>
<td>$arrear_pf</td>
<td>$paid_days</td>
<td>$arrear_date</td>";
}
if($prime_arrears_id === ''){
$tbl_line = "<tr><td style='text-align:center'; colspan='10'>No Data Found!!</td></tr>";
}
$arrear_data = "<table class='table table-striped table-bordered' id='arrears_table'>
<thead>
<tr>
<th scope='col'>Increment Apply Month</th>
<th scope='col'>Employee Code</th>
<th scope='col'>Effective Date</th>
<th scope='col'>Transaction Column</th>
<th scope='col'>Arrear Column</th>
<th scope='col'>Arrear Month</th>
<th scope='col'>Arrear Value</th>
<th scope='col'>Arrear Pf</th>
<th scope='col'>Paid Days</th>
<th scope='col'>Arrear Date</th>
</tr>
</thead>
<tbody>
$tbl_line
</tbody>
</table>";
echo json_encode(array("success" => TRUE,'message' => $arrear_data));
}
}
?>