File: //home/cafsindia/cloud_cafsinfotech_in/application/controllers/Attn_summary.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Attn_summary extends Action_controller{
// public $filter_list;
public function __construct(){
parent::__construct('attn_summary');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$from_query = 'select * from cw_form_setting where prime_module_id = "employees" and field_show = "1" and label_name != "role" ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$this->fliter_list = $this->get_filter_data($form_result);
$data['fliter_list'] = $this->fliter_list;
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABEL WITH FILTERS
public function check_attn_details(){
//SEARCH INFO FUNCTION
$this->search_info();
$fliter_label = $this->input->post('fliter_label');
$fliter_type = $this->input->post('fliter_type');
$filter_cond = $this->input->post('filter_cond');
$fliter_val = $this->input->post('fliter_val');
$filter_count = count($fliter_label);
$search_count = 0;
$fliter_query = "";
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $fliter_label[$i];
$table_name = $fliter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $fliter_val[$i];
$field_type = $field_type[$i];
if(($db_cond) && ($db_value)){
$search_count++;
if((int)$field_type === 4){
$search_val = date("Y-m-d",strtotime($db_value));
}else{
$search_val = $db_value;
}
if($db_cond === "LIKE"){ $search_val = "$db_value%"; }
if((int)$table_name === 1){
$this->prime_table = "cw_employees";
$fliter_query .= ' and '. $this->prime_table .".". $db_name ." ". $db_cond .' "'.$search_val.'"';
}
}
}
$entry_date = date('Y-m-d',strtotime($this->input->post('entry_date')));
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
$get_data_qry = 'select cw_employees.employee_code,att_date,cw_employees.emp_name,cw_shift_master.from_time,cw_shift_master.to_time,if(cw_time_entry.total_work_hours > 0,time_format(concat(floor(cw_time_entry.total_work_hours /60),":",lpad(mod(cw_time_entry.total_work_hours,60),2,"0")),"%H:%i"),cw_time_entry.total_work_hours) as total_work_hours,if(cw_time_entry.total_excess_hours > 0,time_format(concat(floor(cw_time_entry.total_excess_hours /60),":",lpad(mod(cw_time_entry.total_excess_hours,60),2,"0")),"%H:%i"),cw_time_entry.total_excess_hours) as total_excess_hours,if(cw_time_entry.early_in > 0,time_format(concat(floor(cw_time_entry.early_in /60),":",lpad(mod(cw_time_entry.early_in,60),2,"0")),"%H:%i"),cw_time_entry.early_in) as early_in,if(cw_time_entry.early_out > 0,time_format(concat(floor(cw_time_entry.early_out /60),":",lpad(mod(cw_time_entry.early_out,60),2,"0")),"%H:%i"),cw_time_entry.early_out) as early_out,if(cw_time_entry.late_in > 0,time_format(concat(floor(cw_time_entry.late_in /60),":",lpad(mod(cw_time_entry.late_in,60),2,"0")),"%H:%i"),cw_time_entry.late_in) as late_in,if(cw_time_entry.excess_out > 0,time_format(concat(floor(cw_time_entry.excess_out /60),":",lpad(mod(cw_time_entry.excess_out,60),2,"0")),"%H:%i"),cw_time_entry.excess_out) as excess_out,if(cw_time_entry.total_late_hours > 0,time_format(concat(floor(cw_time_entry.total_late_hours /60),":",lpad(mod(cw_time_entry.total_late_hours,60),2,"0")),"%H:%i"),cw_time_entry.total_late_hours) as total_late_hours,if(cw_time_entry.total_permission > 0,time_format(concat(floor(cw_time_entry.total_permission /60),":",lpad(mod(cw_time_entry.total_permission,60),2,"0")),"%H:%i"),cw_time_entry.total_permission) as total_permission,if(cw_time_entry.punch_in !="0000-00-00 00:00:00", date_format(cw_time_entry.punch_in,"%H:%i"),"00:00") as punch_in,if(cw_time_entry.punch_out !="0000-00-00 00:00:00", date_format(cw_time_entry.punch_out,"%H:%i"),"00:00") as punch_out,cw_shift_master.from_time,to_time,cw_category.category_name,cw_shift_master.shift_name from cw_time_entry inner join cw_shift_master on cw_shift_master.prime_shift_master_id = cw_time_entry.shift_id inner join cw_employees on cw_time_entry.employee_code = cw_employees.employee_code inner join cw_category on cw_category.prime_category_id = cw_time_entry.shift_id where att_date ="'.$entry_date.'" '.$role_condition.' '.$fliter_query.'';
$get_data_info = $this->db->query("CALL sp_a_run ('SELECT','$get_data_qry')");
$get_data_rslt = $get_data_info->result();
$get_data_info->next_result();
$filter_list = $this->get_filter_data($get_data_rslt);
$tr_line = "";
foreach ($get_data_rslt as $key => $value){
$tr_line .="<tr>
<td>".$employee_code = $value->employee_code."</td>
<td>".$emp_name = $value->emp_name."</td>
<td>".$shift_name = $value->shift_name."</td>
<td>".$att_date = date('d-m-Y',strtotime($value->att_date))."</td>
<td>".$from_time = $value->from_time."</td>
<td>".$to_time = $value->to_time."</td>
<td>".$punch_in = $value->punch_in."</td>
<td>".$punch_out = $value->punch_out."</td>
<td>".$total_work_hours = $value->total_work_hours."</td>
<td>".$early_in = $value->early_in."</td>
<td>".$early_out = $value->early_out."</td>
<td>".$late_in = $value->late_in."</td>
<td>".$excess_out = $value->excess_out."</td>
<td>".$total_late_hours = $value->total_late_hours."</td>
<td>".$total_excess_hours = $value->total_excess_hours."</td>
<td>".$total_permission = $value->total_permission."</td>
<td>".$category_name = $value->category_name."</td>
</tr>";
}
if($get_data_rslt){
$table_content="<div style='margin:20px;'>
<table class='table table-striped table-bordered' id='attn_report'>
<thead>
<td>EMPLOYEE CODE</td>
<td>EMPLOYEE NAME</td>
<td>SHIFT NAME</td>
<td>ENTRY DATE</td>
<td>SHIFT START</td>
<td>SHIFT END</td>
<td>EMP IN</td>
<td>EMP OUT</td>
<td>WORK MINS</td>
<td>EARLY IN</td>
<td>EARLY OUT</td>
<td>LATE IN</td>
<td>LATE OUT</td>
<td>LATE MINS</td>
<td>OT MINS</td>
<td>PERMISSION</td>
<td>CATEGORY</td>
</thead>
<tbody>
$tr_line
</tbody>
</table>
</div>";
echo json_encode(array('success'=>true,'table_content'=>$table_content));
}else{
echo json_encode(array('success'=>false,'message'=>'NO DATA AVAILABLE','table_content'=>$table_content));
}
}
//PROVIDE QUERY AND DROPDOWN VALUES
public function get_filter_data($form_result){
$filter = array();
foreach($form_result as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$array_list = array();
if( $label_id != 'employee_code' && $label_id != 'emp_name'){
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_table == "cw_category"){
$qry = " and prime_category_id != 1";
}else{
$qry = "";
}
if($pick_table == "cw_payroll_formula"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = ucwords(str_replace("_"," ",$pick->$pick_list_val_2));
$array_list[$pick_key] = $pick_val;
}
}else{
if($label_id === "excemption_component"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and tax_section = 1 $qry";
}else{
$pick_query = "select $pick_list from $pick_table where trans_status = 1 $qry";
}
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}
if(($input_view_type === 1) || ($input_view_type === 2)){
$filter[] = array('label_id'=> $label_id, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
}
}
}
return $filter;
}
}
?>