File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Ot_conversion_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Ot_conversion_report extends Action_controller{
public function __construct(){
parent::__construct('ot_conversion_report');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABAL WITH FILTERS
public function search(){
$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);
}
$start_date = $this->input->post('start_date');
$end_date = $this->input->post('end_date');
$start_date = date('Y-m-d', strtotime($this->input->post('start_date')));
$end_date = date('Y-m-d', strtotime($this->input->post('end_date')));
if($start_date && $end_date){
$over_time_qry ='SELECT cw_employees.employee_code,cw_employees.emp_name,cw_employees.date_of_joining ,cw_employees.termination_status as term_sts,cw_employees.resignation_date as resign_date,cw_vendor_details.vendor_name,cw_category.category_name,TIME_FORMAT(SEC_TO_TIME(cw_time_entry.break_deduction * 60), "%H:%i:%s") as break_deduction,cw_time_entry.att_date,TIME_FORMAT(SEC_TO_TIME(cw_time_entry.total_excess_hours * 60), "%H:%i:%s") AS total_ot, TIME_FORMAT(SEC_TO_TIME(cw_time_entry.approved_ot_mins * 60), "%H:%i:%s") as over_time,TIME_FORMAT(SEC_TO_TIME(cw_time_entry.approved_shift_ot * 60), "%H:%i:%s") as approved_shift_ot ,cw_section.section_name ,cw_department.department,cw_time_entry.whole_day_status FROM cw_employees INNER JOIN cw_section ON cw_section.prime_section_id=cw_employees.section INNER JOIN cw_department ON cw_department.prime_department_id = cw_employees.department INNER JOIN cw_category ON cw_category.prime_category_id = cw_employees.role INNER JOIN cw_time_entry ON cw_time_entry.employee_code = cw_employees.employee_code INNER JOIN cw_vendor_details ON cw_vendor_details.prime_vendor_details_id=cw_employees.vendor_name WHERE cw_time_entry.att_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND (cw_time_entry.approved_ot_mins > 0 or cw_time_entry.approved_shift_ot > 0) and cw_time_entry.trans_status = 1' ;
$over_time_qry_info =$this->db->query("CALL sp_a_run ('SELECT','$over_time_qry')");
$over_time_qry_rslt = $over_time_qry_info->result_array();
$over_time_qry_info->next_result();
$shift_name_qry = 'SELECT cw_shift_master.short_name,cw_shift_import.shift_date,employee_code FROM cw_shift_import inner join cw_shift_master on cw_shift_master.prime_shift_master_id = cw_shift_import.shift_name WHERE cw_shift_import.trans_status = 1 and shift_date >="'.$start_date.'" and shift_date <="'.$end_date.'"';
$shift_info = $this->db->query("CALL sp_a_run ('SELECT','$shift_name_qry')");
$shift_rslt = $shift_info->result_array();
$shift_info->next_result();
$shift_name_arr = array();
foreach($shift_rslt as $key => $arr){
$shift_name_arr[$arr['employee_code']][$arr['shift_date']] = $arr['short_name'];
}
$over_time_arr = array();
$ot_emp_arr = array();
foreach($over_time_qry_rslt as $key => $arr){
$ot_emp_arr[$arr['employee_code']] = $arr;
$over_time_arr[$arr['employee_code']][$arr['att_date']]['over_time'] = $arr['over_time'];
$over_time_arr[$arr['employee_code']][$arr['att_date']]['total_ot'] = $arr['total_ot'];
$over_time_arr[$arr['employee_code']][$arr['att_date']]['break_deduction'] = $arr['break_deduction'];
$over_time_arr[$arr['employee_code']][$arr['att_date']]['shift_ot'] = $arr['approved_shift_ot'];
$over_time_arr[$arr['employee_code']][$arr['att_date']]['day_status'] = $arr['whole_day_status'];
}
/*$time_entry_qry ='SELECT cw_time_entry.approved_shift_ot,cw_time_entry.att_date,cw_employees.employee_code, TIME_FORMAT(SEC_TO_TIME(cw_time_entry.approved_shift_ot * 60), "%H:%i:%s") AS convert_time FROM cw_time_entry INNER JOIN cw_employees ON cw_employees.employee_code = cw_time_entry.employee_code WHERE cw_time_entry.att_date >="'.$start_date.'" and cw_time_entry.att_date <="'.$end_date.'"';
$time_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$time_entry_qry')");
$time_entry_rslt = $time_entry_info->result_array();
$time_entry_info->next_result();
$time_entry_arr = array();
foreach($time_entry_rslt as $key => $arr){
$time_entry_arr[$arr['employee_code']][$arr['att_date']] = $arr['convert_time'];
}*/
$weekoff_import_qry = 'SELECT employee_code,employee_name,weekoff_date,weekoff_type FROM `cw_weekoff_import` WHERE trans_status = 1 and weekoff_date >="'.$start_date.'" and weekoff_date <="'.$end_date.'"';
$weekoff_info = $this->db->query("CALL sp_a_run ('SELECT','$weekoff_import_qry')");
$weekoff_rslt = $weekoff_info->result_array();
$weekoff_info->next_result();
$weekoff_import_arr = array();
foreach($weekoff_rslt as $key => $arr){
$weekoff_import_arr[$arr['employee_code']][$arr['weekoff_date']] = $arr['weekoff_type'];
}
$date_tr_line ="";
$brake_tr_line ="";
$total_ot_tr_line ="";
$ex_ot_tr_line ="";
$tr_head ="";
foreach($ot_emp_arr as $key => $emp_data){
$employee_code = $emp_data['employee_code'];
$emp_name = $emp_data['emp_name'];
$category = $emp_data['category_name'];
$department = $emp_data['department'];
$vendor = $emp_data['vendor_name'];
$section = $emp_data['section_name'];
$emp_act_sts = (int)$emp_data['term_sts'];
$emp_doj = date("d-m-Y",strtotime($emp_data['date_of_joining']));
$emp_resign_date = date("d-m-Y",strtotime($emp_data['resign_date']));
$tr_head = "<tr ><th>Emp.ID</th><th>Name</th><th>DOJ</th><th>Department</th><th>Sect.</th><th>Category</th><th>Vendor</th><th>Date</th>";
$date_line = "";
$ex_ot_line = "";
$break_line = "";
$total_ot_line = "";
$actual_ot_line = "";
$shift_ot_line = "";
$date_period = new DatePeriod(
new DateTime($start_date),
new DateInterval('P1D'),
new DateTime(date("Y-m-d",strtotime("+1 days",strtotime($end_date))))
);
$final_total_ot = 0;
$final_shift_ot = 0;
$final_break_time = 0;
$final_over_time = 0;
$approved_shift_ot = 0;
$grand_ot_total = 0;
$grand_over_total = 0;
$grand_shift_ot_total = 0;
foreach ($date_period as $key => $value){
$day = $value->format('d');
$shift_date = $value->format('Y-m-d');
$weekoff_date = $value->format('Y-m-d');
$att_date = $value->format('Y-m-d');
$shift_details = $shift_name_arr[$employee_code][$shift_date];
$weekoff_details = $weekoff_import_arr[$employee_code][$weekoff_date];
$break_deduction_details = $over_time_arr[$employee_code][$att_date]['break_deduction'];
$total_ot_details = $over_time_arr[$employee_code][$att_date]['total_ot'];
$over_time_details = $over_time_arr[$employee_code][$att_date]['over_time'];
$shift_ot_details = $over_time_arr[$employee_code][$att_date]['shift_ot'];
$day_status = $over_time_arr[$employee_code][$att_date]['day_status'];
//$time_entry_details = $time_entry_arr[$employee_code][$att_date];
if($total_ot_details){
$total_time = $this->convert_time_hours($total_ot_details);
}else{
$total_time = 0;
}
if($break_deduction_details){
$break_time = $this->convert_time_hours($break_deduction_details);
}else{
$break_time = 0;
}
if($over_time_details){
$over_time = $this->convert_time_hours($over_time_details);
}else{
$over_time = 0;
}
if($shift_ot_details){
$shift_ot = $this->convert_time_hours($shift_ot_details);
}else{
$shift_ot = 0;
}
/*if($time_entry_details){
$time_entry = $this->convert_time_hours($time_entry_details);
}else{
$time_entry = 0;
}*/
$final_shift_ot = $final_shift_ot + $shift_ot;
$final_total_ot = $final_total_ot + $total_time;
$final_break_time = $final_break_time + $break_time;
$final_over_time = $final_over_time + $over_time;
//$approved_shift_ot = $approved_shift_ot + $time_entry;
$tr_head .= "<th>$day</th>";
if($day_status === 'H'){
$date_line .= "<td style='white-space: nowrap;'> H | $shift_details</td>";
$ex_ot_line .= "<td style='white-space: nowrap;'> $over_time </td>";
$break_line .= "<td style='white-space: nowrap;'> $break_time </td>";
$total_ot_line .= "<td style='white-space: nowrap;'> $total_time </td>";
$shift_ot_line .= "<td style='white-space: nowrap;'> $shift_ot </td>";
}else
if($weekoff_details && $shift_details){
$date_line .= "<td style='white-space: nowrap;'> W | $shift_details</td>";
$ex_ot_line .= "<td style='white-space: nowrap;'> $over_time </td>";
$break_line .= "<td style='white-space: nowrap;'> $break_time </td>";
$total_ot_line .= "<td style='white-space: nowrap;'> $total_time </td>";
$shift_ot_line .= "<td style='white-space: nowrap;'> $shift_ot </td>";
}else
if($weekoff_details){
if(($emp_act_sts === 1 && date("Y-m-d",strtotime($emp_resign_date)) > $shift_date) || ($emp_doj <= $shift_date)){
$not_exist_arr[$shift_date] = "-";
$emp_arr[$employee_code] = $emp_data;
}
$date_line .= "<td style='white-space: nowrap;'>W | - </td>";
$ex_ot_line .= "<td style='white-space: nowrap;'>W | - </td>";
$break_line .= "<td style='white-space: nowrap;'>W | - </td>";
$total_ot_line .= "<td style='white-space: nowrap;'>W | - </td>";
$shift_ot_line .= "<td style='white-space: nowrap;'>W | - </td>";
}else
if($shift_details){
$date_line .= "<td style='white-space: nowrap;'>$shift_details </td>";
$ex_ot_line .= "<td style='white-space: nowrap;'>$over_time</td>";
$break_line .= "<td style='white-space: nowrap;'>$break_time</td>";
$total_ot_line .= "<td style='white-space: nowrap;'>$total_time</td>";
$shift_ot_line .= "<td style='white-space: nowrap;'>$shift_ot</td>";
}else{
if(($emp_act_sts === 1 && date("Y-m-d",strtotime($emp_resign_date)) > $shift_date) || ($emp_doj <= $shift_date)){
$not_exist_arr[$shift_date] = "-";
$emp_arr[$employee_code] = $emp_data;
}
$date_line .= "<td>-</td>";
$ex_ot_line .= "<td>-</td>";
$break_line .= "<td>-</td>";
$total_ot_line .= "<td>-</td>";
$shift_ot_line .= "<td>-</td>";
}
}
/*$grand_ot_total = $final_total_ot + $approved_shift_ot;
$grand_over_total = $final_over_time + $approved_shift_ot;*/
$date_tr_line .= "<tr><td >$employee_code</td><td >$emp_name</td><td style='white-space: nowrap;'>$emp_doj</td><td >$department</td><td >$section</td><td >$category</td><td >$vendor</td><td style='white-space: nowrap;'><b>shift</b></td>$date_line<td>-</td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td style='white-space: nowrap;'><b>Total OT Hrs</b></td>$total_ot_line<td>$final_total_ot</td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td style='white-space: nowrap;'><b>Break Hrs</b></td>$break_line<td>$final_break_time</td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td style='white-space: nowrap;'><b>Approved OT Hrs</b></td>$ex_ot_line<td>$final_over_time</td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td style='white-space: nowrap;'><b>Shift OT </b></td>$shift_ot_line<td>$final_shift_ot</td></tr>";
}
$tr_head .="<th>Total</th>";
if($date_tr_line){
$message = "Data Available";
$table_content = "<div style='margin:20px;'>
<table class='table table-striped table-bordered' id='over_time_table'>
<thead>
$tr_head
</thead>
<tbody>
$date_tr_line
</tbody>
</table>
</div>";
echo json_encode(array('success'=>true,'table_content'=>$table_content ,'message' => $message,));
}else{
$message = "No Data Available";
$table_content = "<div style='margin:20px;'>
<table class='table table-striped table-bordered' id='over_time_table'>
<thead>
$tr_head
</thead>
<tbody>
<tr><td colspan='4' style='text-align:center';>No Data Availible</td></tr>
</tbody>
</table>
</div>";
echo json_encode(array('success'=>false,'table_content'=>$table_content, 'message' => $message,));
}
}
}
// TIME AND MINUTES CONVERSION
function convert_time_hours($timeString) {
list($hours, $minutes, $seconds) = explode(':', $timeString);
$hours =intval($hours);
$minutes = $minutes;
$decimal_time = 0;
switch(true){
case ($minutes >= 1 && $minutes <= 19):
$decimal_time = 0;
break;
case ($minutes > 19 && $minutes <= 24):
$decimal_time = 3;
break;
case ($minutes > 24 && $minutes <= 29):
$decimal_time = 4;
break;
case ($minutes > 29 && $minutes <= 34):
$decimal_time = 5;
break;
case ($minutes > 34 && $minutes <= 39):
$decimal_time = 6;
break;
case ($minutes > 39 && $minutes <= 44):
$decimal_time = 7;
break;
case ($minutes > 44 && $minutes <= 49):
$decimal_time = 8;
break;
case ($minutes > 49 && $minutes <= 59):
$decimal_time = 9;
break;
default:
$decimal_time = 0;
}
$total_ot=$hours.".".$decimal_time;
return $total_ot;
}
}
?>