File: //home/cafsindia/hrms_allyindian_com/application_bk/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');
}
}
// 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;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
// AUTOCOMPLETE FOR SERACH EMPLOYEE
public function emp_suggest(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$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.'%" OR emp_name 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(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$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 cw_arrear_cumulative.employee_code,cw_employees.emp_name,arrear_column,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_column';
$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_emp_result = array_reduce($arrear_emp_result, function($result, $arr){
$result[$arr['employee_code']][$arr['arrear_column']] = $arr;
return $result;
}, array());
$arrear_array = array();
foreach($arrear_emp_result as $emp_code => $emp_rslt){
foreach($emp_rslt as $arr_col => $emp_data){
$increment_apply_month = $emp_data['increment_apply_month'];
$effective_date = $emp_data['effective_date'];
$category = $emp_data['category'];
$emp_name = $emp_data['emp_name'];
$arrear_column = $emp_data['arrear_column'];
$array_value = $emp_data['arrear_value'];
$arrear_array[$emp_code][$arrear_column]['arrear_column'] = $array_value;
$arrear_array[$emp_code][$arrear_column]['employee_name'] = $emp_name;
}
}
if(!$arrear_array){
echo json_encode(array("success" => FALSE,'message' => "No Data"));
exit(0);
}
$form_setting_rslt = $this->get_form_setting();
$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();
$tfoot = "";
$arrear_column = explode(',',$arrear_role_result[0]['arrear_column']);
$tbl_line = "";
foreach($arrear_array as $key =>$val){
$total = 0;
$arr_td = "";
$role_line = "";
$tfoot = "";
foreach($arrear_column as $name =>$arrear){
$arrear_value = $arrear_array[$key][$arrear]['arrear_column'];
if(!$arrear_value){
$arrear_value = '0.00';
}
$role_line .= "<th scope='col'>".$form_setting_rslt[$arrear]."</th>";
$arr_td .="<td>".$arrear_value."</td>";
$employee_name = $arrear_array[$key][$arrear]['employee_name'];
$total = $total + $arrear_value;
$tfoot .= "<th></th>";
}
$tbl_line .= "<tr onclick=view_arrear_info('$prime_cumulative_id','$increment_apply_month','$effective_date','$key','$category')><td>$key</td><td>$employee_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(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$form_setting_rslt = $this->get_form_setting();
$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('emp_code');
$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;
if($effective_date){
$effective_date = date('d-m-Y',strtotime($effective_date));
}
$transaction_column = $form_setting_rslt[$rslt->transaction_column];
$arrear_column = $form_setting_rslt[$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;
if($arrear_date){
$arrear_date = date('d-m-Y',strtotime($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>
</tr>";
}
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));
}
public function get_form_setting(){
$form_setting_qry = 'select label_name,view_name from cw_form_setting where cw_form_setting.trans_status = 1 and cw_form_setting.prime_module_id ="employees"';
$form_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$form_setting_qry')");
$form_setting_rslt = $form_setting_info->result_array();
$form_setting_info->next_result();
$form_setting_rslt = array_reduce($form_setting_rslt, function($result, $arr){
$result[$arr['label_name']] = $arr['view_name'];
return $result;
}, array());
return $form_setting_rslt;
}
}
?>