File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/ZRM/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;
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;
}
}
//check data
public function is_exit_data($exit_qry){
$exit_info = $this->runQuery("$exit_qry");
$exit_result = $this->result($exit_info);
return $exit_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){
$get_mail_details = $this->get_mail_details($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("Birthday Wishes",$company_email_id,$config_result,$email_subject,$email_design,$bcc_mail,$attachment,$bcc_email_column);
$msg = " and $send_mail";
}
return json_encode(array('status' => true,'data' => "Successfully $msg!!!"));
}
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;
}
public function get_mail_details($module,$print_type){
// echo "<pre>";
//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;
$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){
$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
}
?>