File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/ZRM/application/controllers/Invalid_punch_request.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Invalid_punch_request extends Action_controller{
public function __construct(){
parent::__construct('invalid_punch_request');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->load->view("$this->control_name/manage",$data);
}
public function request_punch(){
$start_date = date('Y-m-d', strtotime($this->input->post('start_date')));
$end_date = date('Y-m-d', strtotime($this->input->post('end_date')));
//FOR REQUEST PUNCH TAB STARTS.
$punch_qry = 'SELECT cw_invalid_punch.entry_from,cw_invalid_punch.entry_to,cw_invalid_punch.shift_date ,cw_shift_master.shift_name , cw_invalid_punch.user_id FROM cw_invalid_punch INNER JOIN cw_shift_master ON cw_invalid_punch.shift_id = cw_shift_master.prime_shift_master_id INNER JOIN cw_employees ON cw_employees.device_code = cw_invalid_punch.user_id WHERE cw_employees.employee_code = "'.$this->logged_emp_code.'" AND cw_invalid_punch.shift_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_invalid_punch.trans_status = 1 AND cw_invalid_punch.apr_status = 1 GROUP BY cw_invalid_punch.user_id,cw_invalid_punch.shift_date;';
$punch_info = $this->db->query("CALL sp_a_run ('SELECT','$punch_qry')");
$punch_rslt = $punch_info->result();
$punch_info->next_result();
if($punch_rslt){
$tr_line = "";
$row = 1;
foreach($punch_rslt as $punch){
$attn_date = $punch->shift_date;
$entry_from = date('d-m-Y H:i:s', strtotime($punch->entry_from));
$entry_to = date('d-m-Y H:i:s', strtotime($punch->entry_to));
$date = date('d-m-Y', strtotime($punch->shift_date));
$shift = $punch->shift_name;
$device_code= $punch->user_id;
#For Getting the employee data.
$data_qry = 'SELECT * FROM cw_invalid_punch WHERE user_id = "'.$device_code.'" AND shift_date = "'.$attn_date.'" AND cw_invalid_punch.shift_date BETWEEN "'.$start_date.'" and "'.$end_date.'" and trans_status = 1 and apr_status = 1 ';
$data_info = $this->db->query("CALL sp_a_run ('SELECT','$data_qry')");
$data_rslt = $data_info->result();
$data_info->next_result();
$newArray = array();
$count = count($data_rslt);
$newArray = [];
$lastRecord = null;
for($i = 0; $i < $count; $i++){
$record = $data_rslt[$i];
$inv_prime_id = $record->prime_id;
if($record->record_type == 'in'){
if($lastRecord !== null && $lastRecord['record_type'] == 'out'){
# Pair the current "in" with the previous "out"
$lastRecord['outpunch'] = date('d-m-Y H:i:s', strtotime($record->log_date));
$newArray[] = $lastRecord;
$lastRecord = null;
}
if($lastRecord !== null && $lastRecord['record_type'] == 'in'){
# Pair the current "in" with the previous "in"
$newArray[] = $lastRecord;
}
$lastRecord = [
'inv_prime_id' => $inv_prime_id,
'user_id' => $record->user_id,
'shift_date' => date('d-m-Y', strtotime($record->shift_date)),
'inpunch' => date('d-m-Y H:i:s', strtotime($record->log_date)),
'outpunch' => '',
'record_type' => 'in',
];
}elseif ($record->record_type == 'out'){
if($lastRecord !== null && $lastRecord['record_type'] == 'in'){
# Pair the current "out" with the previous "in"
$lastRecord['outpunch'] = date('d-m-Y H:i:s', strtotime($record->log_date));
$newArray[] = $lastRecord;
$lastRecord = null;
}else{
# If there's no matching "in," store the "out" with an empty "inpunch"
$newArray[] = [
'inv_prime_id' => $inv_prime_id,
'user_id' => $record->user_id,
'shift_date' => date('d-m-Y', strtotime($record->shift_date)),
'inpunch' => '',
'outpunch' => date('d-m-Y H:i:s', strtotime($record->log_date)),
'record_type' => 'out',
];
}
}
}
# Check if there's an unmatched record
if ($lastRecord !== null) {
$newArray[] = $lastRecord;
}
$tr_line .= "<tr id='row_$row'>
<td>$row</td>
<td>$date</td>
<td>$device_code</td>
<td>$shift</td>
<td>
<div class='accordion' id='accordionExample$row'>
<div class='card'>
<div class='card-header' id='headingOne$row'>
<button class='btn btn-link' type='button' data-toggle='collapse' data-target='#collapseOne$row' aria-expanded='false' aria-controls='collapseOne$row' onclick='toggleAccordion($row)'>
<i class='fa fa-eye'></i> View
</button>
</div>
<div id='collapseOne$row' class='collapse' aria-labelledby='headingOne$row' data-parent='#accordionExample$row'>
<div class='card-body'>
<form id='formData_$row'>
<table class='table table-bordered' id= 'collpase_table_$row'>";
$validationIds = array();
foreach($newArray as $record){
$inv_id = $record['inv_prime_id'];
$id = $record['user_id'];
$inpunch = $record['inpunch'];
$outpunch = $record['outpunch'];
$validationIds[] = $inv_id;
$tr_line .= "<tr>
<td>
<label for='in_punch' class='required'>In Punch</label>
<input type='text' name='in_punch_$inv_id' id='in_punch_$inv_id' class='form-control input-sm datetimepicker' placeholder='In Punch' value='$inpunch' ".($inpunch ? "readonly" : "required").">
</td>
<td>
<label for='out_punch' class='required'>Out Punch</label>
<input type='text' name='out_punch_$inv_id' id='out_punch_$inv_id' class='form-control input-sm datetimepicker' placeholder='Out Punch' value='$outpunch' ".($outpunch ? "readonly" : "required").">
</td>";
if($inpunch && $outpunch){
$tr_line .= "<td><span>-</span><style>.hidden-textarea {display: none;}</style><textarea class='hidden-textarea' name='remarks' placeholder='Remarks' ></textarea></td>";
}else{
$tr_line .= "<td>
<label for='remarks' class='required'>Remarks</label>
<textarea name='remarks_$inv_id' id='remarks_$inv_id' class='form-control input-sm' placeholder='Remarks' required></textarea>
</td>";
}
$tr_line .= "</tr>";
}
$validationIds = json_encode($validationIds);
$tr_line .= "<tr><td colspan='3'><button type='button' id='submit_$row' class='btn-submit btn btn-primary btn-xs ' onclick='submitForm(\"$device_code\",\"$row\",\"$attn_date \",$validationIds,\"$entry_from\",\"$entry_to\")'>Submit</button></td></tr>";
$tr_line .= "</table></form></div></div></div></div></td></tr>";
$row++;
}
}
$table_content = "<div ><table class='table table-striped table-bordered' id='details_list'><thead><tr><th>S.No</th><th>Attendance Date</th><th>Device Code</th><th>Shift</th><th>options</th></tr></thead><tbody>$tr_line</tbody></table></div>";
# For request status tab.
$track_content = $this->track_status_tab();
echo json_encode(array('success'=>TRUE, 'message' => "Log",'table_content'=>$table_content,'track_content'=>$track_content));
}
public function request_entry(){
$created_on = date("Y-m-d H:i:s");
$device_code = $this->input->post('user_id');
$row = $this->input->post('row');
$shift_date = $this->input->post('shift_date');
$formData = $this->input->post('formData');
#CollapseTable value array.
$tbl_array = [];
foreach($formData as $field){
$field_name = preg_replace('/_\d+$/', '', $field['name']);
if(isset($tbl_array[$field_name])){
$tbl_array[$field_name][] = $field['value'];
}else{
$tbl_array[$field_name] = [$field['value']];
}
}
$existing_data_query = 'select * from cw_invalid_punch_log';
$existing_data_info = $this->db->query("CALL sp_a_run ('SELECT','$existing_data_query')");
$existing_data_result = $existing_data_info->result_array();
$existing_data_info->next_result();
$exist_data = array();
# Retrieving already exist data in log.
foreach($existing_data_result as $value){
$prime_id = $value['prime_id'];
$user_id = $value['user_id'];
$in_punch = $value['in_punch'];
$out_punch = $value['out_punch'];
$remarks = $value['remarks'];
$exist_data[$in_punch][$out_punch][$remarks]= $value;
}
#Retreiving data from employee master.
$emp_data = 'SELECT cw_shift_master.shift_name,cw_employees.employee_code,cw_employees.emp_name,cw_employees.first_level_approval,cw_employees.second_level_approval,cw_employees.approve_type FROM cw_employees INNER JOIN cw_shift_import INNER JOIN cw_shift_master INNER JOIN cw_invalid_punch ON cw_shift_master.prime_shift_master_id = cw_shift_import.shift_name AND cw_employees.employee_code = cw_shift_import.employee_code AND cw_invalid_punch.user_id = cw_employees.device_code WHERE cw_employees.device_code = "'.$device_code.'" AND cw_employees.trans_status = 1 GROUP BY cw_employees.employee_code,cw_invalid_punch.user_id,cw_invalid_punch.shift_date';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_data')");
$emp_rslt = $emp_info->result();
$emp_info->next_result();
if($emp_rslt){
foreach($emp_rslt as $data){
$employee_code = $data->employee_code;
$emp_name = $data->emp_name;
$first_level = $data->first_level_approval;
$second_level = $data->second_level_approval;
$shift_name = $data->shift_name;
$approve_type = $data->approve_type;
}
}
#cw_invalid_punch_request.
$ins_query = 'INSERT INTO cw_invalid_punch_request(employee_code,emp_name,shift_name,device_code,first_approval,second_approval,approval_status,shift_date,first_approval_status,second_approval_status,trans_created_by,trans_created_date) VALUES ("'.$employee_code.'","'.$emp_name.'","'.$shift_name.'","'.$device_code.'","'.$first_level.'","'.$second_level.'","1","'.$shift_date.'","1","1","'.$this->logged_id.'","'.$created_on.'")';
$ins_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_query')");
$ins_result = $ins_info->result();
$ins_info->next_result();
#cw_invalid_punch_approval.
$app_data = 'select * from cw_invalid_punch_request where trans_status = 1 ';
$app_info = $this->db->query("CALL sp_a_run ('SELECT','$app_data')");
$app_rslt = $app_info->result();
$app_info->next_result();
foreach($app_rslt as $approval){
$punch_req_id = $approval->prime_invalid_punch_request_id;
$employee_code = $approval->employee_code;
$emp_name = $approval->emp_name;
$shift_name = $approval->shift_name;
$device_code = $approval->device_code;
$first_level = $approval->first_approval_status;
$second_level = $approval->second_approval_status;
$approval_sts = $approval->approval_status;
$shift_date = $approval->shift_date;
$first_approval = $approval->first_approval;
$second_approval = $approval->second_approval;
}
$ins_appr = 'INSERT INTO cw_invalid_punch_approval(device_code,employee_code,emp_name,shift_name,first_approval,second_approval,shift_date,prime_invalid_punch_request_id,first_approval_status,second_approval_status,approve_type,trans_created_by,
trans_created_date)VALUES ("'.$device_code.'","'.$employee_code.'","'.$emp_name.'","'.$shift_name.'","'.$first_approval.'","'.$second_approval.'","'.$shift_date.'","'.$punch_req_id.'","'.$first_level.'","'.$second_level.'","'.$approve_type.'","'.$this->logged_id.'","'.$created_on.'")';
$appr_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_appr')");
$appr_result = $appr_info->result();
$appr_info->next_result();
#cw_invalid_punch_log.
#Iterate over the arrays and perform insertions
for($i = 0; $i < count($tbl_array['in_punch']); $i++){
$inPunch = date('Y-m-d H:i:s', strtotime($tbl_array['in_punch'][$i]));
$outPunch = date('Y-m-d H:i:s', strtotime($tbl_array['out_punch'][$i]));
$remarks = $tbl_array['remarks'][$i];
if(!isset($exist_data[$inPunch][$outPunch][$remarks])){
$ins_query = 'INSERT INTO cw_invalid_punch_log (user_id, shift_date, in_punch, out_punch, remarks,status,log_status) VALUES ("'.$device_code.'","'.$shift_date.'","'.$inPunch.'","'.$outPunch.'","'.$remarks.'","pending","'.$punch_req_id.'")';
$ins_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_query')");
$ins_result = $ins_info->result();
$ins_info->next_result();
}
}
$track_content = $this->track_status_tab();
if($ins_result){
$upd_query = 'update cw_invalid_punch set trans_status = 0 where shift_date = "'.$shift_date.'" AND user_id = "'.$device_code.'" ';
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
$upd_query = 'update cw_invalid_punch_request set trans_status = 0 where shift_date = "'.$shift_date.'" AND device_code = "'.$device_code.'" ';
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully Inserted.!",'row_id'=>$row,'track_content'=>$track_content));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Insertion Failed. please try after some time."));
}
}
//FOR REQUEST STATUS TAB.
public function track_status_tab() {
$emp_qry = 'SELECT employee_code, emp_name FROM cw_employees WHERE trans_status = 1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_rslt = $emp_info->result();
$emp_info->next_result();
$track_qry = 'SELECT prime_invalid_punch_request_id,employee_code,device_code, emp_name, first_approval, second_approval, first_approval_status, second_approval_status, approval_status, shift_name, shift_date FROM cw_invalid_punch_request WHERE employee_code = "'.$this->logged_emp_code.'" ';
$track_info = $this->db->query("CALL sp_a_run ('SELECT','$track_qry')");
$track_rslt = $track_info->result();
$track_info->next_result();
if($track_rslt){
$status = 1;
$ts_line = "";
foreach($track_rslt as $track){
$punch_req_id = $track->prime_invalid_punch_request_id;
$employee_code = $track->employee_code;
$emp_name = $track->emp_name;
$shift_date = date('d-m-Y', strtotime($track->shift_date));
$shift_name = $track->shift_name;
$first_approval = $track->first_approval;
$second_approval = $track->second_approval;
foreach($emp_rslt as $other_track){
if($other_track->employee_code === $track->first_approval){
$first_approval = $other_track->employee_code . ' - ' . $other_track->emp_name;
}
if($other_track->employee_code === $track->second_approval){
$second_approval = $other_track->employee_code . ' - ' . $other_track->emp_name;
}
}
$approval_status = $track->approval_status;
$first_approval_status = $track->first_approval_status;
$second_approval_status = $track->second_approval_status;
$labels = [1 => 'Pending', 2 => 'Approved', 3 => 'Rejected'];
$first_final_sts = isset($labels[$first_approval_status]) ? $labels[$first_approval_status] : 'Unknown';
$second_final_sts= isset($labels[$second_approval_status]) ? $labels[$second_approval_status] : 'Unknown';
$final_sts = isset($labels[$approval_status]) ? $labels[$approval_status] : 'Unknown';
$ts_line .= "<tr><td>$shift_date</td><td>$shift_name</td><td>$first_approval</td><td>$second_approval</td><td>$first_final_sts</td><td>$second_final_sts</td><td>$final_sts</td>
<td>
<div class='accordion' id='accordionExample$status'>
<div class='card'>
<div class='card-header' id='headingOne$status'>
<button class='btn btn-link' type='button' data-toggle='collapse' data-target='#statusCollapse$status' aria-expanded='false' aria-controls='statusCollapse$status' onclick='statusAccordion($status)'>
<i class='fa fa-list-alt' aria-hidden='true'></i> status
</button>
</div>
<div id='statusCollapse$status' class='collapse' aria-labelledby='headingOne$status' data-parent='#accordionExample$status'>
<div class='card-body'>
<form id='formData_$status'>
<table class='table table-bordered'>";
$status_qry = 'SELECT * FROM cw_invalid_punch_log WHERE shift_date = "'.$track->shift_date.'" and user_id = "'.$track->device_code.'" and log_status = "'.$punch_req_id.'" ';
$status_info = $this->db->query("CALL sp_a_run ('SELECT','$status_qry')");
$status_rslt = $status_info->result();
$status_info->next_result();
foreach ($status_rslt as $record){
$prime_id = $record->prime_id;
$id = $record->user_id;
$inpunch = date('d-m-Y H:i:s', strtotime($record->in_punch));
$outpunch = date('d-m-Y H:i:s', strtotime($record->out_punch));
$result = $record->status;
$reason = $record->reason;
$ts_line .= "<tr>
<td>
<label for='in_punch' >In Punch</label><br/>
<span>$inpunch</span>
</td>
<td>
<label for='out_punch' >Out Punch</label><br/>
<span>$outpunch</span>
</td>";
if($result){
$ts_line .= "<td><label for='status' >Status</label><br/><span>$result</span></td>";
}else{
$ts_line .= " <td><span>-</span></td>";
}
if($result === 'rejected'){
$ts_line .= "<td><label for='reason' >Reason</label><br/>
<span name='reason'>$reason</span></td>";
}else{
$ts_line .= "<td><span>-</span></td>";
}
$ts_line .= "</tr>";
}
$ts_line .= "</table></form></div></div></div></div></td></tr>";
$status++;
}
}
$track_content = "<div><table class='table table-striped table-bordered' id='track_list'><thead><tr><th>Shift Date</th><th>Shift Name</th><th>Level1 Approver</th><th>Level2 Approver</th><th>First Level Status</th><th>Second Level Status</th><th>Final status</th><th>options</th></tr></thead><tbody>$ts_line</tbody></table></div>";
return $track_content;
}
}
?>