File: /home/cafsindia/.trash/app/api_model.php
<?php
include('./dbconnect.php');
require('../phpmailer/class.phpmailer.php');
error_reporting(0);
date_default_timezone_set('Asia/Kolkata');
class api_model extends dbconnect{
Protected $smtp_server;
Protected $port_no;
Protected $user_name;
Protected $user_password;
Protected $hr_mail;
Protected $cc_mail;
Protected $sender_name;
private $enckey = 'vDIa5JdknBqfrKOu8d7UpddnBMCH1vza'; //32 characters
public function __construct(){
$this->open_db();
//$this->sql_open_db();
$get_email_qry = 'select * from cw_mail_configurations where trans_status = 1 and mail_status = 1';
$email_info = $this->runQuery("$get_email_qry");
$email_result = $this->result($email_info);
if($email_result){
$this->smtp_server = $email_result[0]->smtp_server;
$this->port_no = $email_result[0]->port_no;
$this->user_name = $email_result[0]->mail_username;
$this->user_password = $email_result[0]->mail_password;
$this->hr_mail = $email_result[0]->sender_email;
$this->cc_mail = $email_result[0]->bcc;
$this->sender_name = $email_result[0]->sender_name;
}
}
# get punch data from API.
public function API_verification(){
$today = date("Y-m-d");
$curl = curl_init();
curl_setopt_array($curl, array(
CURLOPT_URL => 'http://124.123.68.45:8013/customer/getCollectiveAgent?pageNo=12&date='.$today,
CURLOPT_RETURNTRANSFER => true,
CURLOPT_ENCODING => '',
CURLOPT_MAXREDIRS => 10,
CURLOPT_TIMEOUT => 0,
CURLOPT_FOLLOWLOCATION => true,
CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
CURLOPT_CUSTOMREQUEST => 'GET',
CURLOPT_HTTPHEADER => array(
'username: CAMS_USER@',
'password: CRfQ3bxbSyOdo'
),
));
$response = curl_exec($curl);
if(curl_errno($curl)){
return curl_error($curl);
}else{
return $response;
}
curl_close($curl);
}
//check data
public function is_exit_data($exit_qry){
$exit_info = $this->runQuery("$exit_qry");
$exit_result = $this->result($exit_info);
return $exit_result;
}
public function process_time_log($query){
$mysql_info = $this->runQuery("$query");
$mysql_result = $this->result($mysql_info);
mysqli_next_result($this->db);
return $mysql_result;
}
//check data
public function get_mysql_array($mysql_qry){
$mysql_info = $this->runQuery("$mysql_qry");
$mysql_result = $this->result($mysql_info);
$mysql_result = json_decode(json_encode($mysql_result),true);
// $result = array_reduce($mysql_result, function($result, $arr){
// $result[$arr['user_id']][$arr['record_type']][$arr['log_date']] = $arr;
// return $result;
// }, array());
mysqli_next_result($this->db);
return $mysql_result;
}
public function get_mysql_obj($mysql_qry){
$mysql_info = $this->runQuery("$mysql_qry");
$mysql_result = $this->result($mysql_info);
mysqli_next_result($this->db);
return $mysql_result;
}
public function get_sql_emp($sql_qry){
$sql_info = $this->sql_runQuery("$sql_qry");
$sql_result = $this->sql_result($sql_info);
mysqli_next_result($this->db);
return $sql_result;
}
//inserted data
public function offer_insert($prime_query){
$rms_user_id = $this->runQuery_insert_id("$prime_query");
if($rms_user_id){
$user_info_qry = 'select employee_name,employee_email_id,offer_reference_number from cw_offer_letter where trans_status = 1 and prime_offer_letter_id = '.$rms_user_id;
$user_info = $this->runQuery("$user_info_qry");
$user_result = $this->result($user_info);
$offer_name = $user_result[0]->employee_name;
$rms_mail_id = $user_result[0]->employee_email_id;
$offer_no = $user_result[0]->offer_reference_number;
$msg = $this->call_email($rms_mail_id,$offer_no,$offer_name);
$msg = " and $msg";
return json_encode(array('status' => true,'data' => "Successfully added our offer $msg!!!"));
}else{
return json_encode(array('status' => false,'data' => 'Failed to add and update information'));
}
}
//updated data
public function offer_update($prime_update_query,$rms_code,$resend_mail){
$update_info = $this->runQuery("$prime_update_query");
if($update_info){
$update_info_qry = 'select employee_name,employee_email_id,offer_reference_number from cw_offer_letter where trans_status = 1 and rms_code = "'.$rms_code.'"';
$update_info = $this->runQuery("$update_info_qry");
$update_result = $this->result($update_info);
$offer_name = $update_result[0]->employee_name;
$rms_mail_id = $update_result[0]->employee_email_id;
$offer_no = $update_result[0]->offer_reference_number;
$msg ="";
if((int)$resend_mail === 1){
$msg = $this->call_email($rms_mail_id,$offer_no,$offer_name);
$msg = " and $msg";
}
return json_encode(array('status' => true,'data' => "Successfully update our offer $msg!!!"));
}else{
return json_encode(array('status' => false,'data' => 'Failed to add and update information'));
}
}
public function call_email($rms_mail_id,$offer_no,$offer_name){
$base_url = "http://".$_SERVER['SERVER_NAME'].dirname($_SERVER["REQUEST_URI"].'?');
$base_url = str_replace("/app","/offer","$base_url");
$content = "Dear $offer_name,<br/>
This is your offer reference number: $offer_no. <br/>
Please find the following link to upload your documents ".$base_url."<br/>";
$content .= "With Regards<br/>
HR Team";
$hr_mail = '';
if($rms_mail_id){
$smtp_server = $this->smtp_server;
$port_no = $this->port_no;
$user_name = $this->user_name;
$user_password = $this->user_password;
$hr_mail = $this->hr_mail;
$sender_name = $this->sender_name;
$mail = new PHPMailer();
$mail->SMTPDebug = 3;
$mail->IsSMTP();
$mail->Host = $smtp_server; // Your SMTP PArameter
$mail->Port = $port_no; // Your Outgoing Port
$mail->SMTPAuth = true; // This Must Be True
$mail->Username = $user_name; // Your Email Address
$mail->Password = $user_password; // Your Password
$mail->SMTPSecure = 'tls'; // Check Your Server's Connections for TLS or SSL
$mail->From = $hr_mail;
$mail->FromName = $sender_name;
$mail->AddAddress($rms_mail_id);
$mail->IsHTML(true);
$mail->Subject = "Document Upload link";
$mail->Body = $content;
if($mail=$mail->Send()){
$message = 'Mail Send Successfully';
}else{
$message = 'Mail Not Send';
}
}else{
$message = 'Server timeout';
}
return $message;
}
public function generate_number(){
$max_val = 200000;
$max_no_qry = 'select IFNULL(MAX(offer_reference_number),0)as max_offer_number from cw_offer_letter where trans_status = 1 and offer_reference_number >= '.$max_val;
$max_no_info = $this->runQuery("$max_no_qry");
$max_no_result = $this->result($max_no_info);
$max_count = $max_no_result[0]->max_offer_number;
if((int)$max_count === 0){
$offer_reference_number = $max_val + 1;
}else{
$offer_reference_number = $max_count + 1;
}
return $offer_reference_number;
}
//EMAIL CONFIGURATION SETTINGS
public function email_configurations(){
$config_query = 'SELECT smtp_server,sender_name,bcc,port_no,sender_email,mail_username,mail_password,connection_type FROM cw_mail_configurations WHERE mail_status = 1 and trans_status = 1';
$exit_info = $this->runQuery("$config_query");
$config_result = $this->result($exit_info);
return $config_result;
}
//EMAIL ATTACHEMENT FUNCTION
public function get_email_attachment($attachment,$email_subject,$pdf_name){
$style = "<style>
table{
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
tbody {
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
}
td, th {
border: 1px solid #000 !important;
font-size: 13px !important;
}
td.fr-thick,th.fr-thick {
border-width: 2px !important;
}
table.fr-dashed-borders td, table.fr-dashed-borders th {
border-style: dashed !important;
}
</style>";
$attachment = $style."".$attachment;
$table_data = "<!DOCTYPE html><html> <body>".$attachment."</body></html>";
// Load pdf library
$this->load->library('pdf');
// Load HTML content
$this->dompdf->loadHtml($table_data);
// Render the HTML as PDF
$this->dompdf->render();
// Output the generated PDF (1 = download and 0 = preview)
$output = $this->dompdf->output();
$conroler_name = $this->control_name;
$pdf_name = str_replace(' ', '_', $pdf_name);
//new permission changes
$folder = $conroler_name."/".$pdf_name;
$final_html = $output;
$oldmask = umask(0);
if(!file_exists($folder)){
mkdir($folder, 0777, true);
}
if(!file_exists($conroler_name."/".$pdf_name)){
mkdir($conroler_name."/".$pdf_name, 0777, true);
}
//Check File Exist
if(file_exists($conroler_name."/".$pdf_name.".pdf")){
chmod($conroler_name."/".$pdf_name.".pdf", 0777);
unlink($conroler_name."/".$pdf_name.".pdf");
}
file_put_contents($conroler_name."/".$pdf_name.".pdf" , $final_html);
chmod($conroler_name."/".$pdf_name.".pdf", 0777);
umask($oldmask);
$path = $conroler_name."/".$pdf_name.".pdf";
chmod($path, 0777, true);
return $path;
}
public function send_wishes($print_type,$select_qry,$where_qry,$wish){
$get_mail_details = $this->get_mail_details("employees",$print_type);
$email_subject = $get_mail_details['temp_rslt'][0]->email_subject;
$email_design = $get_mail_details['temp_rslt'][0]->email_design;
$block_column = $get_mail_details['block_rslt'][0]->print_block_column;
if($block_column){
$select_column = ','.$block_column;
}
if($select_qry){
$select_column .= ','.$select_qry;
}
$emp_qry = 'select employee_code,company_email_id'.$select_column.' from cw_employees where trans_status = 1 '.$where_qry.'';
$emp_info = $this->runQuery("$emp_qry");
$emp_rslt = $this->result($emp_info);
$count = 0;
$email_mail_design = array();
foreach($emp_rslt as $rslt){
$employee_code = $rslt->employee_code;
$email_design_temp = $email_design;
$count++;
$map_column = explode(",",$block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$column = $map_column[1];
$value = $rslt->$column;
$replace_val = "@".$column."@";
if(array_key_exists('year_exp', $rslt ?? [])){
$year_exp = $rslt->year_exp;
if((int)$year_exp > 0){
$email_design_temp = str_replace('@year_exp@',$year_exp,$email_design_temp);
}
}
$email_design_temp = str_replace($replace_val,$value,$email_design_temp);
$email_mail_design[$employee_code] = $email_design_temp;
}
}
$config_result = $this->email_configurations();
foreach ($emp_rslt as $key => $value) {
$company_email_id = $value->company_email_id;
$employee_code = $value->employee_code;
$email_design = $email_mail_design[$employee_code];
$send_mail = $this->send_mail_dynamic($wish,$company_email_id,$config_result,$email_subject,$email_design,$bcc_mail,$attachment,$bcc_email_column);
$msg = "$send_mail";
}
$log_count = count($emp_rslt ?? []);
return json_encode(array('status' => true,'data' => "$msg!!!",'log_count' =>$log_count));
}
public function send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design,$bcc_mail,$attachment,$bcc_email_column){
$smtp_server = $config_result[0]->smtp_server;
$sender_name = $config_result[0]->sender_name;
//$bcc = explode(",",$config_result[0]->bcc);
$port_no = $config_result[0]->port_no;
$sender_email = $config_result[0]->sender_email;
$username = $config_result[0]->mail_username;
$password = $config_result[0]->mail_password;
$conn_type = $config_result[0]->connection_type;
if($get_to_mail){
try{
$mail = new PHPMailer();
// $mail->SMTPDebug = 1;
$mail->IsSMTP();
$mail->Host = $smtp_server; // Your SMTP PArameter
$mail->Port = $port_no; // Your Outgoing Port
$mail->SMTPAuth = true; // This Must Be True
$mail->Username = $username; // Your Email Address
$mail->Password = $password; // Your Password
$mail->SMTPSecure = $conn_type; // Check Your Server's Connections for TLS or SSL
$mail->From = $sender_email;
$mail->FromName = $sender_name;
$mail_arr = explode(",",$get_to_mail ?? "");
if((int)count($mail_arr ?? []) > 1){
foreach ($mail_arr as $mail_key => $to_mail){
$mail->AddAddress($to_mail);
}
}else{
$mail->AddAddress($get_to_mail);
}
$bcc_mail = explode(",",$bcc_mail ?? "");
if((int)count($bcc_mail ?? []) > 0){
foreach ($bcc_mail as $bcc_key => $bcc_mails){
$mail->AddCC($bcc_mails);
}
}
$bcc_email_column = explode(",",$bcc_email_column ?? "");
if((int)count($bcc_email_column ?? []) > 0){
foreach ($bcc_email_column as $bccs_key => $bccs_mails){
$mail->AddCC($bccs_mails);
}
}
if(file_exists($attachment)){
$mail->addAttachment($attachment);
}
$mail->IsHTML(true);
$mail->Subject = $email_subject;
$mail->Body = $email_design;
if($mail->Send()){
$status = 1;
$msg = $mail_status_name." Mail Sent Successfully,";
}else{
$status = 0;
$msg = $mail_status_name." Mail Not Sent,";
echo 'Mailer error: ' . $mail->ErrorInfo;
}
//Attachment
}catch(phpmailerException $e){
$status = 0;
$msg = $mail_status_name." Mail Not Sent,";
}catch(Exception $e){
$status = 0;
$msg = $mail_status_name." Mail Not Sent,";
}
}else{
$msg = $mail_status_name." Mail Id Not Available,";
}
return $msg;
}
//MANAGER CONFIRMATION MAIL PROCESS START
public function send_confirmation_mail($print_type){
$get_mail_details = $this->get_mail_details('employees',$print_type);
$print_design = $get_mail_details['temp_rslt'][0]->email_design;
$email_bcc = $get_mail_details['temp_rslt'][0]->email_bcc;
//TODAY DATE REPLACE
$print_design = str_replace("@today_date@",date('d-m-Y'),$print_design);
$print_subject = $get_mail_details['temp_rslt'][0]->print_info_name;
$block_column = $get_mail_details['block_rslt'][0]->print_block_column;
$select_query = $get_mail_details['final_qry_arr']['select_query'];
$line_table_query = $get_mail_details['final_qry_arr']['line_table_query'];
$pick_query = $get_mail_details['final_qry_arr']['pick_query'];
$where_trans = $get_mail_details['final_qry_arr']['where_trans'];
$where_condition = $get_mail_details['final_qry_arr']['where_condition'];
if($where_trans){
$where_trans = " and $where_trans";
}
//MAIL CONFIGURATIONS
$config_result = $this->email_configurations();
$today = date("Y-m-d");
//MANAGER REPORT BASED BELOW EMPLOYEES GET QUERY
$emp_qry = 'select '.$select_query.',cw_employees.manager_report from '.$line_table_query.' '.$pick_query.' where cw_employees.trans_status = 1 and cw_employees.termination_status = 0 and cw_employees.manager_report not in (0,"null","") '.$where_trans.' '.$where_condition.' and cw_employees.date_of_joining = date_sub("'.$today.'", interval 5 month)';
$emp_info = $this->runQuery("$emp_qry");
$emp_rslt = $this->result($emp_info);
$emp_rslt_arr = json_decode(json_encode($emp_rslt),true);
//MANAGER REPORT BASED ARRAY FORMAT CREATE
$reporting_rslt_arr = array_reduce($emp_rslt_arr ?? [], function($result, $arr){
$result[$arr["manager_report"]][$arr['employee_code']] = $arr;
return $result;
}, array());
// if(count($reporting_rslt_arr) === 0){
// return json_encode(array('status' => FALSE,'data' => "$msg!!!"));
// exit(0);
// }
// echo "<pre>";
// print_r($reporting_rslt_arr);die;
//ALL EMPLOYEES GET QUERY
$emp_data_qry = 'select employee_code,company_email_id from cw_employees where cw_employees.trans_status = 1 and termination_status = 0';
$emp_data_info = $this->runQuery("$emp_data_qry");
$emp_data_rslt = $this->result($emp_data_info);
$emp_data_rslt_arr = json_decode(json_encode($emp_data_rslt),true);
//EMPLOYEES BASED ARRAY FORMAT CREATE
$emp_rslt_arr = array_reduce($emp_data_rslt_arr ?? [], function($result, $arr){
$result[$arr["employee_code"]] = $arr;
return $result;
}, array());
$assign_date_formate_list = array("DMY"=>"d-m-Y","YMD"=>"Y-m-d","DFY"=>"d F Y","MY"=>"F-Y","YM"=>"Y-F","D"=>"d","M"=>"M","Y"=>"Y");
$manager_mail_id = "";
$email_design_temp = "";
$msg = "No Data Available";
$i = 0;
// $count = 0;
foreach($reporting_rslt_arr as $empkey => $emp_rslt){
//GET MANAGER MAIL
$manager_mail_id = $emp_rslt_arr[$empkey]['company_email_id'];
foreach($emp_rslt as $rslt){
$employee_code = $rslt['employee_code'];
$email_design_temp = $print_design;
// $count++;
$map_column = explode(",",$block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$column = $map_column[1];
$value = $rslt[$column];
$replace_val = "@".$column."@";
$email_design_temp = str_replace($replace_val,$value,$email_design_temp);
foreach($assign_date_formate_list as $key=>$formate){
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
$date_value = date_create($value);
$replace_value = '';
if($date_value){
$replace_value = date_format($date_value,$formate);
}
$email_design_temp = str_replace($replace_val,$replace_value,$email_design_temp);
}
}
$email_design_temp = str_replace('~','',$email_design_temp);
$mail_status_name = "";
$get_attachment = "";
$bcc_email_column = "";
// if($manager_mail_id !== "" && $manager_mail_id !== 'null'){
$msg = $this->send_mail_dynamic($mail_status_name,$manager_mail_id,$config_result,$print_subject,$email_design_temp,$email_bcc,$get_attachment,$bcc_email_column);
$i++;
// }
// $msg = $this->send_mail($manager_mail_id,$print_subject,$email_design_temp);
}
}
return array('status' => true,'log_count' => $i,'data' => "$msg!!!");
}
public function get_tos_month_day($type,$process_month){
$tos_mon_day_qry = 'SELECT prime_tos_parameter_id,day_conditions,day_count,day_start,day_end FROM cw_tos_month_day INNER JOIN cw_tos_parameter ON cw_tos_parameter.prime_tos_parameter_id = cw_tos_month_day.entry_parameter where cw_tos_parameter.trans_status = 1 and cw_tos_month_day.trans_status = 1 and prime_tos_parameter_id in ('.$type.')';
$tos_mon_day_info = $this->runQuery("$tos_mon_day_qry");
$tos_mon_day_rslt = $this->result($tos_mon_day_info);
foreach($tos_mon_day_rslt as $key => $value){
$prime_tos_parameter_id = $value->prime_tos_parameter_id;
$day_conditions = $value->day_conditions;
$day_count = $value->day_count;
$day_start = $value->day_start;
$day_end = $value->day_end;
if((int)$day_conditions === 3){
if(!$process_month){
if(date("d") > $day_end){
$process_month = date('m-Y',strtotime("+1 month",strtotime(date("d-m-Y"))));
}else{
$process_month = date("m-Y");
}
}
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$shift_start_date = $date->format("Y-m-$day_start");
$shift_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}else{
if(!$process_month){
$process_month = date("m-Y");
}
$day_start = '01';
$date = new DateTime("01-$process_month 00:00:00");
$shift_start_date = $date->format("Y-m-$day_start");
if((int)$day_conditions === 2){
$shift_end_date = date("Y-m-t",strtotime("01-".$process_month));
}else{
$shift_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
}
}
return array("start_date"=>$shift_start_date,"end_date"=>$shift_end_date);
}
public function process_attendance($query){
$mysql_info = $this->runQuery("$query");
$mysql_result = $this->result($mysql_info);
mysqli_next_result($this->db);
return $mysql_result;
}
//SHift and weekoff Auto Update
public function process_defaults($process_month,$employee_code,$select_type,$category){
$qry = '';
$shift_qry = '';
$emp_qry = '';
if($employee_code !== ""){
$qry = ' and cw_employees.employee_code in ("'.$employee_code.'")';
$shift_qry = ' and cw_shift_import.employee_code in ("'.$employee_code.'")';
$emp_qry = ' and cw_weekoff_import.employee_code in ("'.$employee_code.'")';
}
if($category !== ""){
$qry = ' and cw_employees.role in ("'.$category.'")';
$shift_qry = ' and cw_employees.role in ("'.$category.'")';
$emp_qry = ' and cw_employees.role in ("'.$category.'")';
}
//get active financial id
$fin_query = 'SELECT prime_leave_financial_year_id,starting_date,ending_date from cw_leave_financial_year where set_as_default_financial_year = 1 and trans_status = "1"';
$mysql_info = $this->runQuery("$fin_query");
$mysql_result = $this->result($mysql_info);
$financial_id = (int)$mysql_result[0]->prime_leave_financial_year_id;
$tos_mon_day_qry = 'SELECT prime_tos_parameter_id,day_conditions,day_count,day_start,day_end FROM cw_tos_month_day INNER JOIN cw_tos_parameter ON cw_tos_parameter.prime_tos_parameter_id = cw_tos_month_day.entry_parameter where cw_tos_parameter.trans_status = 1 and cw_tos_month_day.trans_status = 1 and prime_tos_parameter_id in (1,2)';
$tos_mon_day_info = $this->runQuery("$tos_mon_day_qry");
$tos_mon_day_rslt = $this->result($tos_mon_day_info);
foreach($tos_mon_day_rslt as $key => $value){
$prime_tos_parameter_id = $value->prime_tos_parameter_id;
$day_conditions = $value->day_conditions;
$day_count = $value->day_count;
$day_start = $value->day_start;
$day_end = $value->day_end;
if((int)$prime_tos_parameter_id === 1){ //shift
if((int)$day_conditions === 3){
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$shift_start_date = $date->format("Y-m-$day_start");
$shift_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}else{
$day_start = '01';
$date = new DateTime("01-$process_month 00:00:00");
$shift_start_date = $date->format("Y-m-$day_start");
if((int)$day_conditions === 2){
$shift_end_date = date("Y-m-t",strtotime("01-".$process_month));
}else{
$shift_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
}
}else
if((int)$prime_tos_parameter_id === 2){ //weekoff
if((int)$day_conditions === 3){
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$woff_start_date = $date->format("Y-m-$day_start");
$woff_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}else{
$day_start = '01';
$date = new DateTime("01-$process_month 00:00:00");
$woff_start_date = $date->format("Y-m-$day_start");
if((int)$day_conditions === 2){
$woff_end_date = date("Y-m-t",strtotime("01-".$process_month));
}else{
$woff_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
}
}
}
//Shift Update start - BSK
//Get Existing Shifts
$shift_import_exist_qry = 'select cw_employees.employee_code,shift_date,prime_shift_import_id as prime_id from cw_shift_import inner join cw_employees on cw_employees.employee_code = cw_shift_import.employee_code where cw_shift_import.trans_status = 1 '.$shift_qry.' and shift_date between "'.$shift_start_date.'" and "'.$shift_end_date.'" and financial_setting_id ="'.$financial_id.'"';
$shift_info = $this->runQuery("$shift_import_exist_qry");
$shift_result = $this->result($shift_info);
$shift_result = json_decode(json_encode($shift_result),true);
$shift_import_arr = array_reduce($shift_result ?? [], function ($result, $arr) {
$result[$arr['employee_code']][$arr['shift_date']] = $arr;
return $result;
}, array());
//Get Employees Details
$emp_query = 'SELECT employee_code,emp_name,shift_name,date_of_joining FROM cw_employees WHERE cw_employees.shift_name != 0 '.$qry.' AND termination_status = 0 AND cw_employees.date_of_joining <= "'.$shift_end_date.'" AND user_right != 1';
$emp_info = $this->runQuery("$emp_query");
$emp_result = $this->result($emp_info);
$emp_details = json_decode(json_encode($emp_result),true);
$emp_details = array_reduce($emp_details ?? [], function ($result, $arr) {
$result[$arr['employee_code']] = $arr['date_of_joining'];
return $result;
}, array());
$prime_qry_value = "";
$updated_on = date("Y-m-d H:i:s");
$current = $shift_start_date;
$end = $shift_end_date;
if((int)$select_type === 1){
$shift_status = false;
$i = 0;
foreach($emp_result as $key => $value){
$employee_code = $value->employee_code;
$emp_name = $value->emp_name;
$shift_name = $value->shift_name;
$current = $shift_start_date;
$date_of_joining = $emp_details[$employee_code];
if(strtotime($current) < strtotime($date_of_joining)){
$current = $date_of_joining;
}
while($current <= $end){
if(!$shift_import_arr[$employee_code][$current]){
$prime_qry_value .= '("'.$employee_code.'","'.$emp_name.'","'.$current.'","'.$current.'","'.$current.'","'.$shift_name.'","'.$financial_id.'","1",'.'"'.$updated_on.'"),';
$i = $i+1;
}
$current = date('Y-m-d', strtotime('+1 day', strtotime($current)));
}
}
$drop_qry = 'TRUNCATE TABLE tbl_shift_imp_temp';
$drop_info = $this->runQuery("$drop_qry");
if($prime_qry_value){
$shift_qry = 'INSERT INTO cw_shift_import(employee_code,employee_name, from_date, to_date,shift_date,shift_name,financial_setting_id,trans_created_by,trans_created_date) VALUES '.rtrim($prime_qry_value,",");
$shift_info = $this->runQuery("$shift_qry");
//Insert Data into temp table
$insert_qry = 'INSERT INTO tbl_shift_imp_temp(employee_code,emp_name, from_date, to_date,shift_date,shift_name,financial_setting_id,trans_created_by,trans_created_date) VALUES '.rtrim($prime_qry_value,",");
$insert_info = $this->runQuery("$insert_qry");
$shift_status = true;
}
}else
if((int)$select_type === 2){
$shift_exist_qry = 'select count(*) as shift_exist_count from cw_shift_import inner join cw_employees on cw_employees.employee_code = cw_shift_import.employee_code where cw_shift_import.trans_status = 1 and date_format(str_to_date(shift_date, "%Y-%m-%d") , "%m-%Y") = "'.$process_month.'"'.$shift_qry;
$shift_exist_info = $this->runQuery("$shift_exist_qry");
$shift_exist_rslt = $this->result($shift_exist_info);
$shift_exist_count = $shift_exist_rslt[0]->shift_exist_count;
$shift_exist = true;
if((int)$shift_exist_count >0){
$shift_exist = false;
$wk_exist_qry = 'select count(*) as wk_exist from cw_weekoff_import inner join cw_employees on cw_employees.employee_code = cw_weekoff_import.employee_code where cw_weekoff_import.trans_status = 1 and date_format(str_to_date(weekoff_date, "%Y-%m-%d") , "%m-%Y") = "'.$process_month.'"'.$emp_qry;
$wk_exist_info = $this->runQuery("$wk_exist_qry");
$wk_exist_rslt = $this->result($wk_exist_info);
$wk_exist = $wk_exist_rslt[0]->wk_exist;
if((int)$wk_exist === 0){
//Insert temp data
$week_temp_qry = 'INSERT INTO tbl_shift_imp_temp(employee_code, from_date, to_date,shift_date,type,trans_created_by,trans_created_date) SELECT cw_employees.employee_code,shift_date,shift_date,shift_date,"weekoff","1","'.$updated_on.'" from cw_shift_import inner join cw_employees on cw_employees.employee_code = cw_shift_import.employee_code where FIND_IN_SET(DAYOFWEEK(shift_date), cw_employees.weekoff) '.$qry.' and shift_date between "'.$woff_start_date.'" and "'.$woff_end_date.'" AND NOT EXISTS(SELECT NULL FROM cw_weekoff_import WHERE weekoff_date = shift_date AND cw_weekoff_import.employee_code = cw_employees.employee_code LIMIT 1) group by cw_employees.employee_code,shift_date';
$week_temp_info = $this->runQuery("$week_temp_qry");
//Weekoff Insert
$woff_qry = 'INSERT into cw_weekoff_import(employee_code,from_date,to_date,weekoff_date,weekoff_type,financial_setting_id,trans_created_by,trans_created_date) SELECT cw_employees.employee_code,shift_date,shift_date,shift_date,"1","'.$financial_id.'","1","'.$updated_on.'" from cw_shift_import inner join cw_employees on cw_employees.employee_code = cw_shift_import.employee_code where FIND_IN_SET(DAYOFWEEK(shift_date), cw_employees.weekoff) '.$qry.' and shift_date between "'.$woff_start_date.'" and "'.$woff_end_date.'" AND NOT EXISTS(SELECT NULL FROM cw_weekoff_import WHERE weekoff_date = shift_date AND cw_weekoff_import.employee_code = cw_employees.employee_code LIMIT 1) group by cw_employees.employee_code,shift_date';
$woff_info = $this->runQuery("$woff_qry");
}
}
}
if(!$shift_status && (int)$select_type === 1){
$msg = 'Shift Already Exist...';
$status = false;
}else
if((int)$shift_exist === 0 && (int)$select_type === 2){
$msg = 'Please import shift...';
$status = false;
}else
if((int)$shift_exist === 0 && (int)$wk_exist === 0 && (int)$select_type === 2){
$msg = 'Weekoff Processed...';
$status = true;
}else
if((int)$wk_exist > 0 && (int)$select_type === 2){
$msg = 'Weekoff Already Exist...';
$status = false;
}else{
if($shift_info || $woff_info){
$status = true;
$msg = 'Processed Successfully... Please see the Updated List..';
}else{
$status = true;
$msg = 'No Records Found...';
}
}
return json_encode(array('status' => $status,'log_count' => $i,'data' => "$msg!!!",'select_type'=>$select_type,'shift_exist'=>$shift_exist,'wk_exist'=>$wk_exist));
}
//Update Cron Status
// public function updated_cron_status($source,$frm,$rslt,$start,$end,$process_type,$process_date){
// if(!$rslt){
// $cron_status = "Failure";
// $log_count = 0;
// }else{
// $cron_status = "Success";
// $log_count = $rslt[0]->result;
// }
// $sql_qry = 'INSERT into cw_cron_status(cron_source,cron_for,cron_status,start_time,end_time,count,process_type,updated_date,process_date) values ("'.$source.'","'.$frm.'","'.$cron_status.'","'.$start.'","'.$end.'","'.$log_count.'","'.$process_type.'","'.date("Y-m-d H:i:s").'","'.$process_date.'")';
// $mysql_info = $this->runQuery("$sql_qry");
// mysqli_next_result($this->db);
// return $mysql_info;
// }
//CRON INSERT BY MARISH
public function cron_insert($source,$frm,$process_type){
$start = date("Y-m-d H:i:s");
$cron_insert_qry ='INSERT into cw_cron_status(cron_source,cron_for,cron_status,start_time,process_type,process_date,trans_created_by,trans_created_date) values ("'.$source.'","'.$frm.'","Inprogress","'.$start.'","'.$process_type.'","'.$start.'",1,"'.$start.'")';
$insert_id = $this->runQuery_insert_id("$cron_insert_qry");
mysqli_next_result($this->db);
return $insert_id;
}
//CRON UPDATE BY MARISH
public function cron_update($cron_insert,$rslt){
if(!$rslt){
$cron_status = "Failure";
$log_count = 0;
}else{
$cron_status = "Success";
$rslt = json_decode($rslt);
$log_count = $rslt->log_count;
}
$end = date("Y-m-d H:i:s");
$cron_update_qry = 'UPDATE cw_cron_status SET cron_status = "'.$cron_status.'",count = "'.$log_count.'",end_time = "'.$end.'",updated_date = "'.$end.'",trans_updated_by = 1, trans_updated_date = "'.$end.'" WHERE prime_cron_status_id = "'.$cron_insert.'"';
$cron_update_query = $this->runQuery("$cron_update_qry");
mysqli_next_result($this->db);
return $cron_update_query;
}
public function get_mail_details($module,$print_type){
//PRINT INFO AND EMAIL DESIGN GET QRY
$temp_qry = 'select prime_print_info_id,email_design,print_info_name,email_bcc from cw_print_info inner join cw_email_design on cw_email_design.email_design_for = cw_print_info.prime_print_info_id where cw_print_info.trans_status = 1 and cw_print_info.trans_status = 1 and print_info_module_id = "'.$module.'" and print_type = "'.$print_type.'"';
$temp_info = $this->runQuery("$temp_qry");
$temp_rslt = $this->result($temp_info);
$prime_print_info_id = $temp_rslt[0]->prime_print_info_id;
if($prime_print_info_id){
$block_qry = 'select * from cw_print_block where print_block_for = "'.$prime_print_info_id.'" and cw_print_block.trans_status = 1';
$block_info = $this->runQuery("$block_qry");
$block_result = $this->result($block_info);
}
$print_design = $temp_rslt[0]->email_design;
if($module === "live_attendance"){
$final_qry_arr = $this->get_load_email_data_pothys("live_attendance",$print_design,$prime_print_info_id);//THIS FUNCION IS ONLY FOR FILE MIGRATION
}else{
$final_qry_arr = $this->get_load_email_data($module,$prime_print_info_id,$print_design,$block_result);
}
$rslt = array('temp_rslt'=> $temp_rslt,'block_rslt'=>$block_result,'final_qry_arr'=>$final_qry_arr);
return $rslt;
}
public function get_load_email_data($control_name,$prime_print_info_id,$print_design,$block_result){
// echo "<pre>";
// print_r($block_result);die;
$print_design = str_replace('~','"',$print_design);
$block_rslt_arr = json_decode(json_encode($block_result),true);
//GET ALL EMAIL BLOCK ID FROM EMAIL BLOCK QRY
$print_block_id_arr = array_column($block_rslt_arr ?? [], 'prime_print_block_id');
$print_block_id_val = implode(',',$print_block_id_arr ?? []);
$table_qry = 'select * from cw_print_table where print_table_for_id in ('.$print_block_id_val.') and trans_status = 1';
$table_data = $this->runQuery("$table_qry");
$table_rslt_arr = $this->result($table_data);
$table_rslt_arr = json_decode(json_encode($table_rslt_arr),true);
$table_rslt_arr = array_reduce($table_rslt_arr ?? [], function ($result, $arr) {
$result[$arr['print_table_for_id']][] = $arr;
return $result;
}, array());
$where_qry = 'select * from cw_print_table_where where where_for_id in ('.$print_block_id_val.') and trans_status = 1';
$where_data = $this->runQuery("$where_qry");
$where_rslt_arr = $this->result($where_data);
$where_rslt_arr = json_decode(json_encode($where_rslt_arr),true);
$where_rslt_arr = array_reduce($where_rslt_arr ?? [], function($result, $arr){
$result[$arr['where_for_id']] = $arr;
return $result;
}, array());
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,prime_module_id from cw_form_setting where prime_module_id in ("'.$control_name.'") and trans_status = "1"';
$form_data = $this->runQuery("$form_qry");
$form_rslt_arr = $this->result($form_data);
$form_rslt_arr = json_decode(json_encode($form_rslt_arr),true);
$form_rslt_arr = array_reduce($form_rslt_arr ?? [], function($result, $arr){
$result[$arr['prime_module_id']][$arr['label_name']][] = $arr;
return $result;
}, array());
foreach($block_result as $block){
$prime_print_block_id = $block->prime_print_block_id;
$print_block_name = $block->print_block_name;
$print_block_type = (int)$block->print_block_type;
$print_block_table = $block->print_block_table;
$print_block_column = $block->print_block_column;
$suppressed_data = $block->suppressed_data;
$cumulative_data = $block->cumulative_data;
$line_table_query = "";
$cutome_table_check = array('transactions'=>'cw_transactions');
if(in_array($prime_print_block_id,$table_rslt_arr)){
$table_result = $table_rslt_arr[$prime_print_block_id];
foreach($table_result as $table){
$line_prime_table = $table['line_prime_table'];
$line_prime_col = $table['line_prime_col'];
$line_join_type = $table['line_join_type'];
$line_join_table = $table['line_join_table'];
$line_join_col = $table['line_join_col'];
$line_sort = $table['line_sort'];
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
if((int)$line_sort === 1){
if($cutome_table_check[$module_name]){
$line_prime_table = " $line_prime_table ";
}else{
$line_prime_table = " $line_prime_table ";
}
if($cutome_table_check[$join_module_name]){
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col";
}else{
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col ";
}
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table";
}else{
if($cutome_table_check[$join_module_name]){
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}
}
}
}
if(!$line_table_query){
$module_name = str_replace("cw_","",$print_block_table);
$prime_id = "prime_".$module_name."_id";
$line_table_query = " $print_block_table ";
}
if(!$print_block_column){
$print_block_column = "*";
}else{
$select_query = "";
$select_ytd_query = "";
$pick_query = "";
$map_column = explode(",",$print_block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$table_name = $map_column[0];
$column = $map_column[1];
$control_name = str_replace('cw_',"",$table_name);
if($control_name === "transactions"){
$control_name = "employees";
}
$form_result = $form_rslt_arr[$control_name][$column];
foreach($form_result as $form){
$prime_form_id = (int)$form['prime_form_id'];
$view_name = $form['view_name'];
$label_name = $form['label_name'];
$field_type = (int)$form['field_type'];
$pick_list_type = (int)$form['pick_list_type'];
$pick_list = $form['pick_list'];
$pick_table = $form['pick_table'];
$auto_prime_id = $form['auto_prime_id'];
$auto_dispaly_value = $form['auto_dispaly_value'];
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
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];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else
if(($field_type === 2) || ($field_type === 3)){
$label_ytd = $label_name."_ytd";
$select_ytd_query .= "sum($table_name.$label_name) as $label_ytd, ";
$select_query .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
}
}
}
}
$where_trans = "";
$where_trans_info = explode(",",$print_block_table ?? "");
foreach($where_trans_info as $trans_info){
if($trans_info === "cw_transactions"){
$select_query .= "cw_transactions.transactions_month , ";
}
$where_trans .= "$trans_info.trans_status = 1 and ";
}
$where_trans = rtrim($where_trans,'and ');
$where_result = $where_rslt_arr[$prime_print_block_id];
//$where_condition = "";
if($where_result){
$where_condition = str_replace('^','"',$where_result['where_condition']);
$where_condition = str_replace('@today_date@',date('Y-m-d'),$where_condition);
}
/*if($where_result){
$where_condition = str_replace('^','"',$where_result[0]->where_condition);
$where_condition = str_replace('@logged_id@',$this->logged_id,$where_condition);
$session_date_list = array("logged_DMY"=>"d-m-Y","logged_YMD"=>"Y-m-d","logged_MY"=>"m-Y","logged_YM"=>"Y-m","logged_Y"=>"Y");
$session_query = 'select session_value from cw_session_value where session_for = 1 and trans_status = "1"';
$session_data = $this->db->query("CALL sp_a_run ('SELECT','$session_query')");
$session_result = $session_data->result();
$session_data->next_result();
foreach($session_result as $rslt){
$session_value = $rslt->session_value;
if($session_value !== "access_data"){
$exist_val = "@".$session_value."@";
if($session_date_list[$session_value]){
$date_formate = $session_date_list[$session_value];
$saved_session_val = date($date_formate);
}else{
$saved_session_val = $this->session->userdata($session_value);
}
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
}
}*/
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$final_qry_arr = array('select_query'=>$select_query,'line_table_query'=>$line_table_query,'pick_query'=>$pick_query,'where_trans'=>$where_trans,'where_condition'=>$where_condition);
return $final_qry_arr;
}
}
//MANAGER CONFIRMATION MAIL PROCESS END
//STARTS BY MARISH
//GET UNPUCHED EMP DETAILS BASED ON WHOLE DAY STATUS
public function unpunched_emp_details($att_date){
$whole_day_status_qry = 'SELECT cw_employees.first_level_approval,cw_time_entry.whole_day_status,cw_employees.employee_code,cw_employees.company_email_id FROM `cw_employees` INNER JOIN cw_time_entry ON cw_employees.employee_code = cw_time_entry.employee_code WHERE cw_time_entry.whole_day_status = "U" and cw_time_entry.att_date ="'.$att_date.'" GROUP BY cw_employees.first_level_approval';
$whole_day_status_query = $this->runQuery("$whole_day_status_qry");
$whole_day_status_rslt = $this->result($whole_day_status_query);
foreach($whole_day_status_rslt as $whole_day_status_rslt){
$first_level_approval[] = $whole_day_status_rslt->first_level_approval;
}
$get_manager_mail = $this->get_manager_mail($first_level_approval);
return $get_manager_mail;
}
//GET LEVEL ONE REPORTING MANAGER MAIL ID BY $FIRST_LEVEL_APPROVAL
public function get_manager_mail($manager_emp_code){
$implode = implode(',',$manager_emp_code ?? []);
$manager_mail_qry = 'SELECT cw_employees.employee_code,cw_employees.company_email_id FROM cw_employees WHERE cw_employees.employee_code IN ('.$implode.')';
$manager_mail_query = $this->runQuery("$manager_mail_qry");
$manager_mail_rslt = $this->result_array($manager_mail_query);
$manager = array();
foreach($manager_mail_rslt as $man_mail){
$man_emp_code = $man_mail['employee_code'];
$man_emp_mail = $man_mail['company_email_id'];
$manager[$man_emp_code] = $man_emp_mail;
}
return $manager;
}
// SEND MAIL TO MANAGER PROCESS BY MARISH
public function send_to_manager($manager_mail_arr,$att_date,$frm){
$att_date = base64_encode("$att_date");
$process_type = 'Auto';
$cron_status = 'Inprogress';
$insert_cron = $this->insert_cron("Manual Unpunch Updation",$frm,$process_type,$cron_status);
foreach($manager_mail_arr as $key=>$value){
$base_url = "http://".$_SERVER['SERVER_NAME'].dirname($_SERVER["REQUEST_URI"].'?');
$get_to_mail = $value;
$url_encode = base64_encode("$key");
$email_design_assign = "Dear Manager-$key, This is the link for view unpunched employees in your team <a href ='$base_url/unpunch_update.php?manager_code=$url_encode&att_date=$att_date'>Click To Update</a>";
$mail_status_name = "Unpunch Employees Status";
$config_result = $this->email_configurations();
$email_subject = "Manager Status";
$bcc_mail = '';
$get_attachment = '';
$bcc_email_column = '';
if($get_to_mail){
$mail_send_status = $this->send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design_assign,$bcc_mail,$get_attachment,$bcc_email_column);
}else{
echo "error";
}
}
if(count($manager_mail_arr ?? []) > 0){
$cron_status = "Success";
$log_count = count($manager_mail_arr ?? []);
}
$update_cron = $this->update_cron($insert_cron,$log_count,$cron_status);
echo $mail_send_status;
}
//START CRON STATUS INSERT AND UPDATE BY MARISH
//INSERT
public function insert_cron($source,$frm,$process_type,$cron_status){
$start = date('Y-m-d H:i:s');
$process_date = date('Y-m-d');
$insert_cron_qry = 'INSERT into cw_cron_status(cron_source,cron_for,cron_status,start_time,process_type,updated_date,process_date,trans_created_date) values ("'.$source.'","'.$frm.'","'.$cron_status.'","'.$start.'","'.$process_type.'","'.date("Y-m-d H:i:s").'","'.$process_date.'","'.date("Y-m-d H:i:s").'")';
$insert_cron_query = $this->runQuery_insert_id("$insert_cron_qry");
mysqli_next_result($this->db);
return $insert_cron_query;
}
//UPDATE
public function update_cron($insert_cron,$log_count,$cron_status){
$end = date('Y-m-d H:i:s');
$update_cron_qry = 'UPDATE cw_cron_status SET end_time = "'.$end.'",cron_status = "'.$cron_status.'",count = "'.$log_count.'",trans_updated_date = "'.date('Y-m-d H:i:s').'" WHERE cw_cron_status.prime_cron_status_id = "'.$insert_cron.'"';
$update_cron_query = $this->runQuery("$update_cron_qry");
mysqli_next_result($this->db);
return $update_cron_query;
}
//END CRON STATUS INSERT AND UPDATE BY MARISH
//CHECK FOR MANAER CODE EXIXTS MARISH
public function is_manager_code_exist($initial_manager_code,$att_date){
$is_manager_code_exist_qry = 'SELECT cw_employees.employee_code FROM cw_employees WHERE cw_employees.employee_code = "'.$initial_manager_code.'"';
$is_manager_code_exist_query = $this->runQuery("$is_manager_code_exist_qry");
$is_manager_code_exist_rslt = $this->resultArray($is_manager_code_exist_query);
$manager_code = $is_manager_code_exist_rslt[0]['employee_code'];
if($initial_manager_code === $manager_code){
return true;
}else{
return false;
}
}
//GET MANGER NAME
public function manager_name($initial_manager_code){
$manager_name_qry = 'SELECT cw_employees.emp_name FROM cw_employees WHERE cw_employees.employee_code = "'.$initial_manager_code.'"';
$manager_name_query = $this->runQuery("$manager_name_qry");
$manager_name_rslt = $this->result($manager_name_query);
$manager_name = $manager_name_rslt[0]->emp_name;
return $manager_name;
}
//EMP CODE VALIDATION BY URL
public function emp_validation($initial_manager_code,$att_date){
//get emp code by manager code
$get_emp_validation_qry = 'SELECT cw_dailyunpunch.manager_code,cw_dailyunpunch.att_date FROM cw_dailyunpunch WHERE cw_dailyunpunch.att_date = "'.$att_date.'" AND manager_code = "'.$initial_manager_code.'"';
$get_emp_validation_query = $this->runQuery("$get_emp_validation_qry");
$get_emp_validation_rslt = $this->result($get_emp_validation_query);
if(count($get_emp_validation_rslt ?? [])>0){
return "exists";
}else{
return "not_exists";
}
}
//FOR GET UNPUNCHED EMPLOYEES
public function unpunched_update($manager_emp_code,$att_date){
$manager_emp_code = base64_decode($manager_emp_code);
$att_date = base64_decode($att_date);
//query for getting unpunched employees details under specified manager
$unpunch_emp_sts_qry = 'SELECT cw_employees.emp_name,cw_time_entry.whole_day_status,cw_time_entry.employee_code FROM `cw_employees` INNER JOIN cw_time_entry ON cw_employees.employee_code = cw_time_entry.employee_code WHERE cw_time_entry.whole_day_status = "U" and cw_time_entry.att_date ="'.$att_date.'" AND cw_employees.first_level_approval ="'.$manager_emp_code.'"';
$unpunch_emp_sts = $this->runQuery("$unpunch_emp_sts_qry");
return $unpunch_emp_sts;
}
//GET EXSTIS DATA, IF THE EMPLOYEE CODE IS EXISTS IN SAME ATT DATE
public function get_exists_data($exist_manager_code,$exist_att_date){
$exist_data_qry = 'SELECT DATE_FORMAT(cw_dailyunpunch.att_date, "%d-%m-%Y") as exists_att_date, cw_dailyunpunch.employee_code as exists_emp_code,cw_employees.emp_name,cw_dailyunpunch.manager_code as exists_manager_code,cw_dailyunpunch.day_status as exists_status FROM cw_dailyunpunch INNER JOIN cw_employees ON cw_dailyunpunch.employee_code = cw_employees.employee_code WHERE cw_dailyunpunch.att_date = "'.$exist_att_date.'" AND cw_dailyunpunch.manager_code = "'.$exist_manager_code.'" AND cw_dailyunpunch.trans_status = "1"';
$exist_data_query = $this->runQuery("$exist_data_qry");
return $exist_data_query;
}
//INSERT INTO DAILY UNPUNCH
public function insert($manager_code,$att_date,$emp_code,$day_sts){
$trans_created_date = date("Y-m-d H:i:s");
$trans_created_by = '1';
$insert_qry = 'INSERT INTO cw_dailyunpunch(employee_code,manager_code,att_date,day_status,trans_created_date,trans_created_by)VALUES("'.$emp_code.'","'.$manager_code.'","'.$att_date.'","'.$day_sts.'","'.$trans_created_date.'","'.$trans_created_by.'")';
$insert_query = $this->runQuery($insert_qry);
$emp_master_cond = array('5','6','8','9','7');
if(in_array($day_sts,$emp_master_cond)){
$emp_sts = array("5"=>"Resigned With Salary","6"=>"Resigned Without Salary","8"=>"Terminated With Salary","9"=>"Termination Without Salary","7"=>"Abscond");
$emp_leave_sts = $emp_sts[$day_sts];
$emp_master_update = $this->updation_emp_master($att_date,$emp_code);
//employee mail process when $emp_master_cond is true
$this->emp_mail_process($emp_code,$emp_leave_sts);
}
return "success";
}
//UPDATE FUNCTION IN EMPLOYEE MASTER
public function updation_emp_master($att_date,$emp_code){
$update_emp_master_qry = 'UPDATE cw_employees SET cw_employees.resignation_date = "'.$att_date.'",cw_employees.last_working_date ="'.$att_date.'",cw_employees.separation_type = "555",cw_employees.separation_reason= "for example",cw_employees.trans_updated_date = "'.$att_date.'" WHERE cw_employees.employee_code = "'.$emp_code.'"';
$update_emp_master_query = $this->runQuery("$update_emp_master_qry");
return true;
}
//EMPOYEE MAIL PROCESS WHEN $EMP_MASTER_COND IS TRUE
public function emp_mail_process($emp_code,$emp_leave_sts){
$get_emp_mail_qry = 'SELECT cw_employees.personal_email_id FROM cw_employees WHERE cw_employees.employee_code ="'.$emp_code.'"';
$get_emp_mail_query = $this->runQuery("$get_emp_mail_qry");
$get_emp_mail_rslt = $this->result($get_emp_mail_query);
foreach($get_emp_mail_rslt as $emp_mail_arr){
$emp_mail = $get_emp_mail_rslt[0]->personal_email_id;
}
$get_to_mail = $emp_mail;
$email_design_assign = "As Per Your Manager's Status You Are $emp_leave_sts.";
$mail_status_name = "Empolyee Status";
$config_result = $this->email_configurations();
$email_subject = "Employee Status";
$bcc_mail = '';
$get_attachment = '';
$bcc_email_column = '';
if($get_to_mail){
$mail_send_status = $this->send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design_assign,$bcc_mail,$get_attachment,$bcc_email_column);
}else{
echo "error";
echo $mail_send_status;
}
}
//PASTE BY MARISH FROM POTHYS
public function send_attendance_mail($print_type){
$get_mail_details = $this->get_mail_details('live_attendance',$print_type);
$print_design = $get_mail_details['temp_rslt'][0]->email_design;
$print_subject = $get_mail_details['temp_rslt'][0]->print_info_name;
$block_column = $get_mail_details['block_rslt'][0]->print_block_column;
$select_query = $get_mail_details['final_qry_arr']['select_query'];
$line_table_query = $get_mail_details['final_qry_arr']['line_table_query'];
// echo "$line_table_query";die;
$pick_query = $get_mail_details['final_qry_arr']['pick_query'];
$where_trans = $get_mail_details['final_qry_arr']['where_trans'];
$where_condition = $get_mail_details['final_qry_arr']['where_condition'];//ATT DATE STATIC BY MARISH
$where_condition = "and att_date = \"2023-06-10\"";//ATT DATE STATIC BY MARISH
if($where_trans){
$where_trans = " and $where_trans";
}
// $emp_qry = 'SELECT '.$block_column.', cw_employees.manager_report,cw_employees.company_email_id from cw_employees inner join cw_live_attendance on cw_live_attendance.employee_code = cw_employees.employee_code where cw_employees.trans_status = 1 and termination_status = 0 and manager_report not in (0,"null","")';
$emp_qry = 'select '.$select_query.',cw_employees.manager_report from '.$line_table_query.' '.$pick_query.' where cw_employees.trans_status = 1 and termination_status = 0 and manager_report not in (0,"null","") '.$where_trans.' '.$where_condition.'';
// echo $emp_qry;die;
$emp_info = $this->runQuery("$emp_qry");
$emp_rslt = $this->result($emp_info);
$emp_rslt_array = json_decode(json_encode($emp_rslt),true);
$reporting_rslt_arr = array_reduce($emp_rslt_array ?? [], function($result, $arr){
$result[$arr["manager_report"]][$arr['employee_code']] = $arr;
return $result;
}, array());
$emp_data_qry = 'select employee_code,company_email_id from cw_employees where cw_employees.trans_status = 1 and termination_status = 0';
$emp_data_info = $this->runQuery("$emp_data_qry");
$emp_data_rslt = $this->result($emp_data_info);
$emp_data_rslt_array = json_decode(json_encode($emp_data_rslt),true);
$emp_rslt_arr = array_reduce($emp_data_rslt_array ?? [], function($result, $arr){
$result[$arr["employee_code"]] = $arr;
return $result;
}, array());
$print_design = str_replace("@today_date@",date('d-m-Y'),$print_design);
echo "$email_design_temp<br>";
$count = 0;
$assign_date_formate_list = array("DMY"=>"d-m-Y","YMD"=>"Y-m-d","DFY"=>"d F Y","MY"=>"F-Y","YM"=>"Y-F","D"=>"d","M"=>"M","Y"=>"Y");
// create empty document
$document = new DOMDocument();
$document->preserveWhiteSpace = false;
// load html
$document->loadHTML($print_design);
$header_doc = new DOMDocument();
$header_doc->loadHTML($print_design);
$xpath = new DOMXPath($header_doc);
$xpath_body = new DOMXPath($document);
$header_arr = array();
foreach ($xpath->query('//table') as $tr) {
$thead_val = $header_doc->getElementsByTagName('tbody')->item(0); // get thead tag
$thead_val->parentNode->removeChild($thead_val);
$tbl_id = str_replace('~', '',$tr->getAttribute("id"));
$tds = $tr->getElementsByTagName('thead');
$tbl_head_val = preg_replace('~<(?:!DOCTYPE|/?(?:html|body|table))[^>]*>\s*~i', '', $header_doc->saveHTML($tr));
$header_arr[$tbl_id] = $tbl_head_val;
}
$body_arr = array();
foreach ($xpath_body->query('//table') as $tr) {
$tbody_val = $document->getElementsByTagName('thead')->item(0); // get thead tag
$tbody_val->parentNode->removeChild($tbody_val);
$tbl_id = str_replace('~', '',$tr->getAttribute("id"));
$tds = $tr->getElementsByTagName('tbody');
$tbl_body_val = preg_replace('~<(?:!DOCTYPE|/?(?:html|body|table))[^>]*>\s*~i', '', $document->saveHTML($tr));
$body_arr[$tbl_id] = $tbl_body_val;
}
$style = "<style>
table{
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
width:100% !important;
}
tbody {
border: 1px !important;
border-collapse: collapse !important;
empty-cells: show !important;
max-width: 100% !important;
font-size: 13px !important;
width:100% !important;
}
td, th {
border: 1px solid #000 !important;
font-size: 13px !important;
}
td.fr-thick,th.fr-thick {
border-width: 2px !important;
}
table.fr-dashed-borders td, table.fr-dashed-borders th {
border-style: dashed !important;
}
</style>";
$email_design_temp = "";
$final_table = "";
$table_body_tr = array();
foreach ($body_arr as $table_key => $body_html) {
foreach($reporting_rslt_arr as $empkey => $emp_rslt){
$tbl_html_content = "";
foreach($emp_rslt as $rslt){
$employee_code = $rslt['employee_code'];
$email_design_temp = $body_html;
$count++;
$map_column = explode(",",$block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$column = $map_column[1];
$value = $rslt[$column];
$replace_val = "@".$column."@";
$email_design_temp = str_replace($replace_val,$value,$email_design_temp);
foreach($assign_date_formate_list as $key=>$formate){
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
$date_value = date_create($value);
$replace_value = date_format($date_value,$formate);
$email_design_temp = str_replace($replace_val,$replace_value,$email_design_temp);
}
}
$tbl_html_content .= $email_design_temp;
$tbl_html_contents = $tbl_html_content;
$tbl_html_contents = str_replace('~','',$tbl_html_contents);
}
$html_table_head = $header_arr[$table_key];
$final_table .= "<table id='".$table_key."'>$html_table_head $tbl_html_content</table>";
$table_body_tr[$empkey][$table_key]= "<table id='".$table_key."'>$html_table_head $tbl_html_content</table>";
}
}
$msg = "No Data Available";
$i = 0;
//FUNCTION MERGE START MARISH
$mail_status_name = "";
$get_to_mail = "";
$config_result = "";
$email_subject = "";
$email_design = "";
$bcc_mail = "";
$attachment = "";
$bcc_email_column = "";
$config_result = $this->email_configurations();
//FUNCTION MERGE END MARISH
$smtp_server = $this->smtp_server;
$port_no = $this->port_no;
$user_name = $this->user_name;
$user_password = $this->user_password;
$hr_mail = $this->hr_mail;
$sender_name = $this->sender_name;
$mail_detail_arr = array("smtp_server"=>$smtp_server,"port_no"=>$port_no,"user_name"=>$user_name,"user_password"=>$user_password,"hr_mail"=>$hr_mail,"sender_name"=>$sender_name,"SMTPSecure"=>"tls");
foreach ($table_body_tr as $key => $table_key){
$print_design = str_replace('~', '"', $print_design);
$dom = new DOMDocument;
$dom->loadHTML($print_design);
$tbls = $dom->getElementsByTagName('table');
foreach($tbls as $tbl) {
$id = $tbl->getAttribute("id");
$tbl->nodeValue = $table_key[$id];
}
$final_data = $dom->saveHTML();
$final_html = htmlspecialchars_decode($final_data);
$final_html = $style.$final_html;
$email_id = $emp_rslt_arr[$key]['company_email_id'];
$get_to_mail = $email_id;
$email_design = $final_html;
$email_subject = $print_subject;
$msg_merge = $this->send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design,$bcc_mail,$attachment,$bcc_email_column);
$i++;
}
return json_encode(array('status' => true,'log_count' => $i,'data' => "$msg!!!"));
}
public function get_load_email_data_pothys($control_name,$print_design,$prime_print_info_id){
$print_design = str_replace('~','"',$print_design);
$block_qry = 'select * from cw_print_block where print_block_for = "'.$prime_print_info_id.'" and trans_status = 1';
$block_info = $this->runQuery("$block_qry");
$block_result = $this->result($block_info);
$table_qry = 'select * from cw_print_table where trans_status = 1';
$table_data = $this->runQuery("$table_qry");
$table_rslt_arr = $this->result($table_data);
$table_rslt_arr = json_decode(json_encode($table_rslt_arr),true);
$table_rslt_arr = array_reduce($table_rslt_arr ?? [], function ($result, $arr) {
$result[$arr['print_table_for_id']][] = $arr;
return $result;
}, array());
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,prime_module_id from cw_form_setting where trans_status = "1"';
$form_data = $this->runQuery("$form_qry");
$form_rslt_arr = $this->result($form_data);
$form_rslt_arr = json_decode(json_encode($form_rslt_arr),true);
$form_rslt_arr = array_reduce($form_rslt_arr ?? [], function($result, $arr){
$result[$arr['prime_module_id']][$arr['label_name']][] = $arr;
return $result;
}, array());
$where_qry = 'select * from cw_print_table_where where trans_status = 1';
$where_data = $this->runQuery("$where_qry");
$where_rslt_arr = $this->result($where_data);
$where_rslt_arr = json_decode(json_encode($where_rslt_arr),true);
$where_rslt_arr = array_reduce($where_rslt_arr ?? [], function($result, $arr){
$result[$arr['where_for_id']] = $arr;
return $result;
}, array());
foreach($block_result as $block){
$prime_print_block_id = $block->prime_print_block_id;
$print_block_name = $block->print_block_name;
$print_block_type = (int)$block->print_block_type;
$print_block_table = $block->print_block_table;
$print_block_column = $block->print_block_column;
$suppressed_data = $block->suppressed_data;
$cumulative_data = $block->cumulative_data;
$table_result = $table_rslt_arr[$prime_print_block_id];
$line_table_query = "";
$cutome_table_check = array('transactions'=>'cw_transactions');
foreach($table_result as $table){
$line_prime_table = $table['line_prime_table'];
$line_prime_col = $table['line_prime_col'];
$line_join_type = $table['line_join_type'];
$line_join_table = $table['line_join_table'];
$line_join_col = $table['line_join_col'];
$line_sort = $table['line_sort'];
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
if((int)$line_sort === 1){
if($cutome_table_check[$module_name]){
$line_prime_table = " $line_prime_table ";
}else{
$line_prime_table = " $line_prime_table ";
}
if($cutome_table_check[$join_module_name]){
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col";
}else{
$line_join_table = " $line_join_table on $line_join_col = $line_prime_col ";
}
$line_table_query .= " $line_prime_table $line_join_type join $line_join_table";
}else{
if($cutome_table_check[$join_module_name]){
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}
}
}
if(!$line_table_query){
$module_name = str_replace("cw_","",$print_block_table);
$prime_id = "prime_".$module_name."_id";
$line_table_query = " $print_block_table ";
}
if(!$print_block_column){
$print_block_column = "*";
}else{
$select_query = "";
$select_ytd_query = "";
$pick_query = "";
$map_column = explode(",",$print_block_column ?? "");
foreach($map_column as $table_column){
$map_column = explode(".",$table_column ?? "");
$table_name = $map_column[0];
$column = $map_column[1];
$control_name = str_replace('cw_',"",$table_name);
if($control_name === "transactions"){
$control_name = "employees";
}
$form_result = $form_rslt_arr[$control_name][$column];
foreach($form_result as $form){
$prime_form_id = (int)$form['prime_form_id'];
$view_name = $form['view_name'];
$label_name = $form['label_name'];
$field_type = (int)$form['field_type'];
$pick_list_type = (int)$form['pick_list_type'];
$pick_list = $form['pick_list'];
$pick_table = $form['pick_table'];
$auto_prime_id = $form['auto_prime_id'];
$auto_dispaly_value = $form['auto_dispaly_value'];
if($label_name === "punch_in"){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
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];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else
if(($field_type === 2) || ($field_type === 3)){
$label_ytd = $label_name."_ytd";
$select_ytd_query .= "sum($table_name.$label_name) as $label_ytd, ";
$select_query .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
}
}
}
}
$where_trans = "";
$where_trans_info = explode(",",$print_block_table ?? "");
foreach($where_trans_info as $trans_info){
if($trans_info === "cw_transactions"){
$select_query .= "cw_transactions.transactions_month , ";
}
$where_trans .= "$trans_info.trans_status = 1 and ";
}
$where_trans = rtrim($where_trans,'and ');
$where_result = $where_rslt_arr[$prime_print_block_id];
//$where_condition = "";
if($where_result){
$where_condition = str_replace('^','"',$where_result['where_condition']);
$where_condition = str_replace('@today_date@',date('Y-m-d'),$where_condition);
}
/*if($where_result){
$where_condition = str_replace('^','"',$where_result[0]->where_condition);
$where_condition = str_replace('@logged_id@',$this->logged_id,$where_condition);
$session_date_list = array("logged_DMY"=>"d-m-Y","logged_YMD"=>"Y-m-d","logged_MY"=>"m-Y","logged_YM"=>"Y-m","logged_Y"=>"Y");
$session_query = 'select session_value from cw_session_value where session_for = 1 and trans_status = "1"';
$session_data = $this->db->query("CALL sp_a_run ('SELECT','$session_query')");
$session_result = $session_data->result();
$session_data->next_result();
foreach($session_result as $rslt){
$session_value = $rslt->session_value;
if($session_value !== "access_data"){
$exist_val = "@".$session_value."@";
if($session_date_list[$session_value]){
$date_formate = $session_date_list[$session_value];
$saved_session_val = date($date_formate);
}else{
$saved_session_val = $this->session->userdata($session_value);
}
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
}
}*/
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$final_qry_arr = array('select_query'=>$select_query,'line_table_query'=>$line_table_query,'pick_query'=>$pick_query,'where_trans'=>$where_trans,'where_condition'=>$where_condition);
return $final_qry_arr;
}
}
# CALENDAR PROCESS STARTS
public function get_calendar_data($employee_code,$start_date,$end_date){
$get_events = $this->get_events($employee_code,$start_date,$end_date);
return $get_events;
}
# GET EVENTS FOR CALENDAR
public function get_events($employee_code,$start_date,$end_date){
$events = [];
$total_days_arr = [];
$leave_arr = array("L"=>1,"FL"=>0.5,"SL"=>0.5,"LP"=>0.5,"PL"=>0.5,"LM"=>0.5,"ML"=>0.5,"OL"=>0.5,"LO"=>0.5,"LL"=>1,"WL"=>0.5,"LW"=>0.5,"UL"=>0.5,"LU"=>0.5,"IL"=>0.5,"LI"=>0.5);
$present_arr = array("P"=>1,"FP"=>0.5,"SP"=>0.5,"LP"=>0.5,"PL"=>0.5,"PM"=>0.5,"MP"=>0.5,"OP"=>0.5,"PO"=>0.5,"PP"=>1,"IP"=>0.5,"PI"=>0.5,"PU"=>0.5,"UP"=>0.5);
$onduty_arr = array("O"=>1,"FO"=>0.5,"SO"=>0.5,"OP"=>0.5,"PO"=>0.5,"OM"=>0.5,"MO"=>0.5,"OL"=>0.5,"LO"=>0.5,"OO"=>1,"WO"=>0.5,"OW"=>0.5,"UO"=>0.5,"OU"=>0.5,"IO"=>0.5,"OI"=>0.5);
$weekoff_arr = array("W"=>1,"WL"=>0.5,"LW"=>0.5,"WO"=>0.5,"OW"=>0.5,"WM"=>0.5,"MW"=>0.5);
$unpunch_arr = array("U"=>1,"UL"=>0.5,"LU"=>0.5,"UO"=>0.5,"OU"=>0.5,"UM"=>0.5,"MU"=>0.5,"UP"=>0.5,"PU"=>0.5,"FP"=>0.5,"SP"=>0.5,"IU"=>0.5,"UI"=>0.5,"FL"=>0.5,"SL"=>0.5);
$invalid_arr = array("I"=>1,"IL"=>0.5,"LI"=>0.5,"IO"=>0.5,"OI"=>0.5,"IM"=>0.5,"MI"=>0.5,"IU"=>0.5,"UI"=>0.5,"IP"=>0.5,"PI"=>0.5);
$holiday_arr = array('H'=>1);
# GET TOS MONTH DAY
$get_tos_qry = 'SELECT day_start,day_end,day_conditions FROM cw_tos_month_day WHERE prime_tos_month_day_id = "4" and trans_status = 1';
$get_tos_info = $this->runQuery("$get_tos_qry");
$get_tos_rslt = $this->result_array($get_tos_info);
$day_start = $get_tos_rslt[0]['day_start'];
$day_end = $get_tos_rslt[0]['day_end'];
$day_conditions = $get_tos_rslt[0]['day_conditions'];
$tos_start_date = $start_date;
$tos_end_date = $end_date;
if($day_conditions == 3 ){
$tos_start_date[8] = "$day_start[0]";
$tos_start_date[9] = "$day_start[1]";
$tos_end_date[8] = "$day_end[0]";
$tos_end_date[9] = "$day_end[1]";
$tos_start_date = date('Y-m-d',strtotime("$tos_start_date -1 month"));
}
// COUNT BASED ON TOS MONTH DAY
$get_count_qry = 'SELECT * FROM cw_time_entry WHERE employee_code = "'.$employee_code.'" AND att_date BETWEEN "'.$tos_start_date.'" AND "'.$tos_end_date.'" and trans_status = 1'; // GROUP BY att_date
$get_count_info = $this->runQuery("$get_count_qry");
$get_count_rslt = $this->result_array($get_count_info);
foreach($get_count_rslt as $key => $val){
if($val['whole_day_status']){
$whole_day_status = $val['whole_day_status'];
if($present_arr[$whole_day_status]){
$total_days_arr['P'] += $present_arr[$whole_day_status];
}
if($leave_arr[$whole_day_status]){
$total_days_arr['L'] += $leave_arr[$whole_day_status];
}
if($onduty_arr[$whole_day_status]){
$total_days_arr['O'] += $onduty_arr[$whole_day_status];
}
if($weekoff_arr[$whole_day_status]){
$total_days_arr['W'] += $weekoff_arr[$whole_day_status];
}
if($unpunch_arr[$whole_day_status]){
$total_days_arr['U'] += $unpunch_arr[$whole_day_status];
}
if($invalid_arr[$whole_day_status]){
$total_days_arr['I'] += $invalid_arr[$whole_day_status];
}
if($holiday_arr[$whole_day_status]){
$total_days_arr['H'] += $holiday_arr[$whole_day_status];
}
}
}
$total_days_arr['T'] = count($get_count_rslt ?? []); // TOTAL COUNT
$present_days = $total_days_arr['P'] > 0 ? $total_days_arr['P'] : 0;
$leave_days = $total_days_arr['L'] > 0 ? $total_days_arr['L'] : 0;
$absent_days = $total_days_arr['U'] > 0 ? $total_days_arr['U'] : 0;
$weekoff_days = $total_days_arr['W'] > 0 ? $total_days_arr['W'] : 0;
$onduty_days = $total_days_arr['O'] > 0 ? $total_days_arr['O'] : 0;
$invalid_days = $total_days_arr['I'] > 0 ? $total_days_arr['I'] : 0;
$holiday_days = $total_days_arr['H'] > 0 ? $total_days_arr['H'] : 0;
$total_days = $total_days_arr['T'] > 0 ? $total_days_arr['T'] : 0;
# GET LEGEND DETAILS
$info_arr = array('total_days' => $total_days, 'present_days' => $present_days, 'absent_days' => $absent_days, 'onduty_days' => $onduty_days, 'holiday_days' => $holiday_days, 'weekoff_days' => $weekoff_days, 'leave_days' => $leave_days, 'invalid_days' => $invalid_days);
$legend_status_qry = 'SELECT legends,day_description FROM cw_day_status_legends WHERE trans_status = 1';
$legend_status_info = $this->runQuery("$legend_status_qry");
$legend_status_rslt = $this->result($legend_status_info);
$legend_arr = [];
foreach($legend_status_rslt as $val){
$legend_arr[$val->legends] = $val->day_description;
}
$full_value_arr = array('P','U','L','W','H','I','O','OP','PO');
# TIME ENTRY EVENTS
$events_start_date = date('Y-m-d' , strtotime("$start_date - 6 days"));
$events_end_date = date('Y-m-d' , strtotime("$end_date + 6 days"));
$time_entry_qry = 'SELECT att_date,whole_day_status,punch_in,punch_out,total_work_hours FROM cw_time_entry WHERE employee_code = "'.$employee_code.'" AND att_date BETWEEN "'.$tos_start_date.'" AND "'.$tos_end_date.'" and trans_status = 1'; // GROUP BY att_date
$time_entry_info = $this->runQuery("$time_entry_qry");
$time_entry_rslt = $this->result_array($time_entry_info);
$base_url = "http://".$_SERVER['SERVER_NAME'].dirname($_SERVER["REQUEST_URI"].'?').'/request';
$base_url = str_replace("app","index.php",$base_url);
foreach($time_entry_rslt as $key => $val){
$punch_in = $val['punch_in'];
$punch_out = $val['punch_out'];
$total_hours = $val['total_work_hours'];
$total_hours = floor($total_hours / 60).':'.($total_hours - floor($total_hours / 60) * 60);
if($punch_in !== "0000-00-00 00:00:00" && $punch_out !== "0000-00-00 00:00:00"){
$punch_in = date("H:i",strtotime($punch_in));
$punch_out = date("H:i",strtotime($punch_out));
}else{
$punch_out = '';
$punch_in = '';
}
$time_entry = "$punch_in - $punch_out => $total_hours";
if($val['whole_day_status']){
$whole_day_status = $val['whole_day_status'];
if(!in_array($whole_day_status,$full_value_arr)){
$cls = ' half';
}else{
$cls = '';
}
if($present_arr[$whole_day_status]){
$events[] = ['date' => $val['att_date'], 'eventName' => "<p class = 'events_para $cls'><span>$legend_arr[$whole_day_status] </span>($time_entry)</p>" , 'className' => 'present_td' , 'dateColor' => 'black'];
}else
if($leave_arr[$whole_day_status]){
$events[] = ['date' => $val['att_date'], 'eventName' => "<a href=$base_url class='anchor_link'><p class = 'events_para_absent $cls'><span>$legend_arr[$whole_day_status]</span></p></a>" , 'className' => 'leave_td' , 'dateColor' => 'black'];
}else
if($onduty_arr[$whole_day_status]){
$events[] = ['date' => $val['att_date'], 'eventName' => "<p class = 'events_para $cls'><span>$legend_arr[$whole_day_status]</span></p>" , 'className' => 'onduty_td' , 'dateColor' => 'black'];
}else
if($weekoff_arr[$whole_day_status]){
$events[] = ['date' => $val['att_date'], 'eventName' => "<p class = 'events_para_red $cls'><span>$legend_arr[$whole_day_status]</span></p>" , 'className' => 'weekoff_td' , 'dateColor' => 'black'];
}else
if($unpunch_arr[$whole_day_status]){
$events[] = ['date' => $val['att_date'], 'eventName' => "<p class = 'events_para_absent $cls'><span>$legend_arr[$whole_day_status]</span></p>" , 'className' => 'absent_td' , 'dateColor' => 'black'];
}else
if($invalid_arr[$whole_day_status]){
$events[] = ['date' => $val['att_date'], 'eventName' => "<p class = 'events_para_red $cls'><span>$legend_arr[$whole_day_status]</span></p>" , 'className' => 'invalid_td' , 'dateColor' => 'black'];
}else
if($holiday_arr[$whole_day_status]){
$events[] = ['date' => $val['att_date'], 'eventName' => "<p class = 'events_para_red $cls'><span>$legend_arr[$whole_day_status]</span></p>" , 'className' => 'weekoff_td' , 'dateColor' => 'black'];
}
}
}
# LEAVE ENTRY EVENTS
$leave_entry_qry = 'SELECT cw_leave_entry.employee_code,cw_leave_creation.leave_description,cw_leave_entry.leave_date,cw_leave_status.leave_status FROM cw_leave_entry INNER JOIN cw_leave_creation ON cw_leave_entry.leave_type = cw_leave_creation.prime_leave_creation_id INNER JOIN cw_leave_status ON cw_leave_entry.leave_status = cw_leave_status.prime_leave_status_id WHERE cw_leave_entry.employee_code = "'.$employee_code.'" AND cw_leave_entry.leave_date BETWEEN "'.$tos_start_date.'" AND "'.$tos_end_date.'" AND cw_leave_entry.trans_status = 1';
$leave_entry_info = $this->runQuery("$leave_entry_qry");
$leave_entry_rslt = $this->result_array($leave_entry_info);
foreach($leave_entry_rslt as $key=>$val){
$leave_type = $val['leave_description'];
$leave_status = $val['leave_status'];
$events[] = ['date' => $val['leave_date'], 'eventName' => "<a class='anchor_link' href=$base_url><p class = 'events_para_leave tooltip_cls'><span> </span>$leave_type - $leave_status</p></a><div class = 'tooltip'></div>" , 'className' => 'leave_td' , 'dateColor' => 'black'];
}
# PERMISSION ENTRY EVENTS
$perm_entry_qry = 'SELECT cw_permission_entry.permission_date,cw_permission_type.permission_type,cw_permission_entry.in_time,cw_permission_entry.out_time,cw_permission_entry.total_time FROM cw_permission_entry INNER JOIN cw_permission_type ON cw_permission_entry.permission_type = cw_permission_type.prime_permission_type_id WHERE cw_permission_entry.employee_code = "'.$employee_code.'" AND cw_permission_entry.permission_date BETWEEN "'.$tos_start_date.'" AND "'.$tos_end_date.'" and cw_permission_entry.trans_status = 1';
$perm_entry_info = $this->runQuery("$perm_entry_qry");
$perm_entry_rslt = $this->result_array($perm_entry_info);
foreach($perm_entry_rslt as $key=>$val){
$perm_type = $val['permission_type'];
$total_time = $val['total_time'];
if($total_time){
$total_time = date("H:m",strtotime($total_time));
}
$events[] = ['date' => $val['permission_date'], 'eventName' => "<a class='anchor_link' href=$base_url><p class = 'events_para_perm tooltip_cls'><span class = ''>Permission </span>$perm_type - $total_time</p></a><div class = 'tooltip'></div>" , 'className' => '' , 'dateColor' => 'black'];
}
# ONDUTY ENTRY EVENTS
$onduty_entry_qry = 'SELECT cw_on_duty_entry.on_duty_date,cw_on_duty_entry.on_duty_count,cw_leave_status.leave_status FROM cw_on_duty_entry INNER JOIN cw_leave_status ON cw_on_duty_entry.on_duty_status = cw_leave_status.prime_leave_status_id WHERE employee_code = "'.$employee_code.'" and on_duty_date BETWEEN "'.$tos_start_date.'" and "'.$tos_end_date.'" and cw_on_duty_entry.trans_status = 1';
$onduty_entry_info = $this->runQuery("$onduty_entry_qry");
$onduty_entry_rslt = $this->result_array($onduty_entry_info);
foreach($onduty_entry_rslt as $key=>$val){
$onduty_status = $val['leave_status'];
$on_duty_count = $val['on_duty_count'];
if($on_duty_count == "1.00"){
$on_duty_count = "Full Day";
}else{
$on_duty_count = "Half Day";
}
$events[] = ['date' => $val['on_duty_date'], 'eventName' => "<a class='anchor_link' href=$base_url><p class = 'events_para_onduty tooltip_cls'><span class = ''></span>$onduty_status - $on_duty_count</p></a><div class = 'tooltip'></div>" , 'className' => '' , 'dateColor' => 'black'];
}
# OVERTIME ENTRY
$overtime_entry_qry = 'SELECT cw_overtime_entry.entry_date,cw_overtime_entry.ot_in_time,cw_overtime_entry.ot_out_time,cw_overtime_entry.excess_work,cw_leave_status.leave_status FROM cw_overtime_entry INNER JOIN cw_leave_status ON cw_overtime_entry.approval_status = cw_leave_status.prime_leave_status_id WHERE cw_overtime_entry.employee_code = "'.$employee_code.'" and cw_overtime_entry.entry_date BETWEEN "'.$tos_start_date.'" and "'.$tos_end_date.'" and cw_overtime_entry.trans_status = 1';
$overtime_entry_info = $this->runQuery("$overtime_entry_qry");
$overtime_entry_rslt = $this->result_array($overtime_entry_info);
foreach($overtime_entry_rslt as $key=>$val){
$ot_in_time = $val['ot_in_time'];
$ot_out_time = $val['ot_out_time'];
$excess_work = $val['excess_work'];
$overtime_sts = $val['leave_status'];
if($ot_in_time){
$ot_in_time = date("H:m",strtotime($ot_in_time));
$ot_out_time = date("H:m",strtotime($ot_out_time));
$excess_work = date("H:m",strtotime($excess_work));
}
$events[] = ['date' => $val['entry_date'], 'eventName' => "<a class='anchor_link' href=$base_url><p class = 'events_para_overtime tooltip_cls'><span class = ''>Overtime </span> - $overtime_sts - $excess_work</p></a><div class = 'tooltip'></div>" , 'className' => '' , 'dateColor' => 'black'];
}
# MANUAL PUNCH ENTRY
$manual_punch_qry = 'SELECT cw_manual_punch_entry.in_date,cw_manual_punch_entry.in_time,cw_manual_punch_entry.out_time,cw_leave_status.leave_status FROM cw_manual_punch_entry INNER JOIN cw_leave_status ON cw_manual_punch_entry.leave_status = cw_leave_status.prime_leave_status_id WHERE cw_manual_punch_entry.employee_code = "'.$employee_code.'" and cw_manual_punch_entry.in_date BETWEEN "'.$tos_start_date.'" and "'.$tos_end_date.'" and cw_manual_punch_entry.trans_status = 1';
$manual_punch_info = $this->runQuery("$manual_punch_qry");
$manual_punch_rslt = $this->result_array($manual_punch_info);
foreach($manual_punch_rslt as $key=>$val){
$in_time = $val['in_time'];
$out_time = $val['out_time'];
$leave_status = $val['leave_status'];
if($in_time){
$in_time = date("H:m",strtotime($in_time));
$out_time = date("H:m",strtotime($out_time));
}
$events[] = ['date' => $val['in_date'], 'eventName' => "<a class='anchor_link' href=$base_url><p class = 'events_para_manual tooltip_cls'><span class = ''>Manual Punch </span> - $leave_status - $in_time-$out_time</p></a><div class = 'tooltip'></div>" , 'className' => '' , 'dateColor' => 'black'];
}
// GET LEAVE BALANCE DETAILS
$leave_bal = $this->view_leave_details($employee_code);
//
$api_events_arr = array('time_enrty' => $time_entry_rslt,'leave_enrty' => $leave_entry_rslt,'permission_enrty' => $perm_entry_rslt,'onduty_enrty' => $onduty_entry_rslt,'overtime_enrty' => $overtime_entry_rslt,'manual_punch_enrty' => $manual_punch_rslt);
$events_arr = array( "events" => $events , "total_days" => $total_days_arr , 'info_arr' => $info_arr , 'tos' => $day_start , 'leave_bal' => $leave_bal , 'api_events_arr' => $api_events_arr);
return $events_arr;
}
# GET LEAVE BALANCE DETAILS
public function view_leave_details($emp_code){
$financial_info = $this->get_leave_financial_details();
$leave_fin_year_id = $financial_info[0]['prime_leave_financial_year_id'];
$leave_qry = 'select cw_leave_opening.*,cw_employees.emp_name from cw_leave_opening inner join cw_employees on cw_employees.employee_code = cw_leave_opening.employee_code where cw_leave_opening.employee_code = "'.$emp_code.'" and cw_leave_opening.trans_status = 1 and financial_setting_id = "'.$leave_fin_year_id.'"';
$leave_info = $this->runQuery($leave_qry);
$leave_result = $this->result_array($leave_info);
$leave_creation_qry = 'select cw_leave_creation.leave_description,cw_leave_creation.leave_name from cw_leave_creation where cw_leave_creation.trans_status = 1 and leave_opening = 1';
$leave_creation_info = $this->runQuery($leave_creation_qry);
$leave_creation_result = $this->result_array($leave_creation_info);
return array('leave_creation_result' => $leave_creation_result , 'leave_result' => $leave_result);
}
# Get Default Leave Financial Year
public function get_leave_financial_details(){
$fin_query = 'SELECT prime_leave_financial_year_id,starting_date,ending_date from cw_leave_financial_year where set_as_default_financial_year = 1 and trans_status = "1"';
$fin_data = $this->runQuery("$fin_query");
$fin_result = $this->result_array($fin_data);
return $fin_result;
}
# PASSWORD UPDATE BASED ON ENCRYPTION
public function update_password(){
$select_qry = 'SELECT employee_code,password FROM cw_employees WHERE termination_status = 0 and trans_status = 1';
$select_info = $this->runQuery("$select_qry");
$select_rslt = $this->result_array($select_info);
foreach($select_rslt as $val){
$employee_code = $val['employee_code'];
$password = $val['password'];
$enc_password = $this->cryptoEncrypt($password);
$upd_qry = 'UPDATE cw_employees SET `password` = "'.$enc_password.'" WHERE employee_code = "'.$employee_code.'"';
$upd_info = $this->runQuery("$upd_qry");
}
if($upd_info){
return true;
}else{
return false;
}
}
//Five STAGES OF ENCRYPTION AND DECRYPTION
public function cryptoEncrypt($data){
try {
// For Password Encryption
$hash1 = hash('sha512', $data);
$hash2 = hash('sha1', $hash1);
$Hash3 = hash('haval160,4', $hash2);
$Hash4 = hash('haval160,5', $Hash3);
// Generate the HMAC hash
$finalhash = hash_hmac('sha256', $Hash4, $this->enckey);
return $finalhash;
} catch (Exception $e) {
// Log the error or handle it as needed
error_log("Encryption Error: " . $e->getMessage()); // Log the error for debugging
return false;
}
}
//END BY MARISH
}
?>