File: /home/cafsindia/uds.cafsinfotech.in/Dump/ZRM/app/api_controller.php
<?php
//api_controller?frm=save_rms&post_data=post_data
$frm = "";
/* Module Initilization */
if (isset($_REQUEST["frm"])){
$frm = $_REQUEST['frm'];
// echo $frm; die;
require("./api_model.php");
$api_model = new api_model;
}
// $frm = "read_etime_data";
if($frm === "offer_save"){
$rms_code ="";
if(isset($_POST['candidate_code'])){
$rms_code = $_POST['candidate_code'];
}
$mobile_number ="";
if(isset($_POST['mobile_number'])){
$mobile_number = $_POST['mobile_number'];
}
$candidate_name ="";
if(isset($_POST['candidate_name'])){
$candidate_name = $_POST['candidate_name'];
}
$department ="";
/*if(isset($_POST['department'])){
$department = $_POST['department'];
}*/
$post_applied_for ="";
/*if(isset($_POST['post_applied_for'])){
$post_applied_for = $_POST['post_applied_for'];
}*/
$salary_commited ="";
if(isset($_POST['salary_commited'])){
$salary_commited = $_POST['salary_commited'];
}
$doj ="";
if(isset($_POST['date_of_joining'])){
$doj = date('Y-m-d',strtotime($_POST['date_of_joining']));
}
$candidate_branch ="";
if(isset($_POST['company_branch'])){
$candidate_branch = $_POST['company_branch'];
}
$candidate_loc ="";
if(isset($_POST['company_location'])){
$candidate_loc = $_POST['company_location'];
}
$candidate_email ="";
if(isset($_POST['email_id'])){
$candidate_email = $_POST['email_id'];
}
$resend_mail ="";
if(isset($_POST['resend_mail'])){
$resend_mail = $_POST['resend_mail'];
}
$rslt ="";
if($rms_code){
$exit_qry = 'select count(*) as rslt from cw_offer_letter where rms_code ="'.$rms_code.'" and trans_status =1';
$exit_rslt = $api_model->is_exit_data($exit_qry);
$exit_count = $exit_rslt[0]->rslt;
if((int)$exit_count === 0){
$offer_reference_number = $api_model->generate_number();
$prime_insert_query = 'insert into cw_offer_letter (rms_code,employee_name,employee_mobile_number,emp_dept,employee_designation,salary,branch,offer_location,employee_email_id,joining_date,offer_reference_number) values ("'.$rms_code.'","'.$candidate_name.'","'.$mobile_number.'","'.$department.'","'.$post_applied_for.'","'.$salary_commited.'","'.$candidate_branch.'","'.$candidate_loc.'","'.$candidate_email.'","'.$doj.'","'.$offer_reference_number.'")';
$rslt = $api_model->offer_insert($prime_insert_query);
}else{
//, emp_dept = "'. $department .'",employee_designation = "'. $post_applied_for .'"
$prime_update_query = 'UPDATE cw_offer_letter SET employee_mobile_number = "'. $mobile_number .'",employee_name = "'. $candidate_name .'",salary = "'. $salary_commited .'",branch = "'. $candidate_branch .'",offer_location = "'. $candidate_loc .'",employee_email_id = "'. $candidate_email .'",joining_date = "'. $doj .'" WHERE rms_code = "'. $rms_code .'" and trans_status =1';
$rslt = $api_model->offer_update($prime_update_query,$rms_code,$resend_mail);
}
}
return_rslt($frm,$rslt);
}else
if($frm === "leave_credit_man"){
$base_url = "http://".$_SERVER['SERVER_NAME'].dirname($_SERVER["REQUEST_URI"].'?');
$base_url = str_replace("/app","/index.php/leave_credit_manual/auto_leave_credit","$base_url");
header("Location: $base_url");
}else
if($frm === "update_defaults"){
$start = date("Y-m-d H:i:s");
$today = date("Y-m-d");
if(isset($_POST["process_month"])){
$process_month = $_POST["process_month"];
$employee_code = $_POST["employee_code"];
$select_type = $_POST["select_type"];
$category = $_POST["category"];
$process_type = "Manual";
}else{
$process_month = date("m-Y",strtotime($today));
$process_type = "Auto";
$employee_code = "";
}
$rslt = $api_model->process_defaults($process_month,$employee_code,$select_type,$category);
$end = date("Y-m-d H:i:s");
$api_model->updated_cron_status("Shift and Weekoff Entry",$frm,$rslt,$start,$end,$process_type,$today);
return_rslt($frm,$rslt);
}else
if($frm === "get_spectra_log_data"){
$today = date("Y-m-d");
$start = date("Y-m-d H:i:s");
if(isset($_POST["from_date"]) && isset($_POST["to_date"])){
$from = $_POST["from_date"];
$to = $_POST["to_date"];
$process_type = "Manual";
}else{
$process_type = "Auto";
$exist_qry = 'SELECT MAX(trans_created_date) as created_date from cw_time_log where trans_status =1';
$exist_rslt = $api_model->is_exit_data($exist_qry);
if($exist_rslt){
$created_date = $exist_rslt[0]->created_date;
$from = date('Y-m-d',strtotime($created_date));
}else{
$from = date("Y-m-d");
}
$to = date('Y-m-d');
}
$to = date('Y-m-d', strtotime('+1 day', strtotime($to))); //Including end date
$period = new DatePeriod(
new DateTime($from),
new DateInterval('P1D'),
new DateTime($to)
);
foreach($period as $key => $value){
$punch_date = $value->format('Y-m-d');
$sql_rslt = $api_model->get_sql_emp("SELECT convert(varchar, punch_time, 20) as log_date,personnel_employee.emp_code from iclock_transaction inner join personnel_employee on personnel_employee.emp_code = iclock_transaction.emp_code where convert(varchar(10), punch_time, 120) = '$punch_date'");
$rslt = "";
if($sql_rslt){
$insert_qry = "";
foreach ($sql_rslt as $key => $value){
$code = $value->emp_code;
$log_date = $value->log_date;
$checktype = $value->check_type;
$checktype_val = "";
$insert_qry .= '("'.$code.'","'.$log_date.'","'. $checktype_val .'","1"),';
}
$insert_qry = rtrim($insert_qry,",");
if($insert_qry){
$trunc_qry = "TRUNCATE table tbl_log_temp"; //create and insert temp tbl
$api_model->get_mysql_array($trunc_qry);
$sql = "insert into tbl_log_temp(tos_code,log_date,check_type,device_id) values $insert_qry";
$api_model->get_mysql_obj($sql);
$rslt = $api_model->process_time_log("CALL itsp_time_log()");
}
}
}
$end = date("Y-m-d H:i:s");
$api_model->updated_cron_status("Time Log Update",$frm,$rslt,$start,$end,$process_type);
return_rslt($frm,$rslt);
}else
if($frm === "get_iclock_log_data"){
$from_date = date("Y-02-03");
$to_date = date("Y-02-03");
$sql_rslt = $api_model->get_sql_emp("SELECT emp_code AS tos_code,convert(varchar, punch_time, 20) as log_date,'in' as check_type FROM iclock_transaction where convert(varchar(10), punch_time, 120) between '$from_date' and '$to_date'");
$json_arr = json_decode(json_encode($sql_rslt),true);
$ins_qry = "";
if($sql_rslt){
$result = implode(',',array_map(function($v){
$code = $v['tos_code'];
$checktime = $v['log_date'];
$checktype = $v['check_type'];
$checktype_val = "";
if($checktype === "i" || $checktype === "I"){
$checktype_val = "in";
}else
if($checktype === "o" || $checktype === "O"){
$checktype_val = "out";
}
$ins_qry = '("'.$code.'","'.$checktime.'","'. $checktype_val .'","1")';
return $ins_qry;
}, $json_arr));
if($result){
$trunc_qry = "TRUNCATE table tbl_log_temp";
$api_model->get_mysql_array($trunc_qry);
$sql = "insert into tbl_log_temp(tos_code,log_date,check_type,device_id) values $result";
$api_model->get_mysql_array($sql);
$rslt = $api_model->get_mysql_obj("CALL itsp_time_log()");
}
return_rslt($frm,$rslt);
}
}else
if($frm === "get_api_data"){
$today = date("Y-m-d");
$start = date("Y-m-d H:i:s");
$end = date("Y-m-d H:i:s");
$process_type = "Auto";
$response = $api_model->API_verification();
$result = json_decode($response, true);
if($result['statusMessage'] === 'Success'){
$punch_data = $result['data'];
$ins_qry = '';
foreach($punch_data as $v){
$employeeCode = $v['employeeCode'];
$logoutTime = $v['logoutTime'];
$loginTime = $v['loginTime'];
if($loginTime){
$ins_qry .= ($ins_qry ? ',' : '').'("'.$employeeCode.'","'.$loginTime.'","In","API")';
}elseif($logoutTime){
$ins_qry .= ($ins_qry ? ',' : '').'("'.$employeeCode.'","'.$logoutTime.'","Out","API")';
}
}
if($ins_qry){
$trunc_qry = "TRUNCATE table tbl_log_temp"; //create and insert temp tbl
$api_model->get_mysql_array($trunc_qry);
$sql = "INSERT INTO tbl_log_temp(tos_code,log_date,check_type,device_id) VALUES $ins_qry";
$api_model->get_mysql_obj($sql);
$rslt = $api_model->get_mysql_obj("CALL itsp_time_log()"); //filter data
$api_model->updated_cron_status("Punch Data",$frm,$rslt,$start,$end,$process_type,$today);
}
return_rslt($frm,$rslt);
}else{
$today = date("Y-m-d");
$start = date("Y-m-d H:i:s");
$end = date("Y-m-d H:i:s");
$process_type = "Auto";
$api_model->updated_cron_status("Punch Data",$frm,'',$start,$end,$process_type,$today);
echo $response ;
}
}else
if($frm === "get_etime_data"){
$today = date("Y-m-d");
$start = date("Y-m-d H:i:s");
if(isset($_POST["from_date"]) && isset($_POST["to_date"])){ //From Attendance Process Module
$from_date = $_POST["from_date"];
$to_date = $_POST["to_date"];
$month = date("m",strtotime($_POST["to_date"]));
$year = date("Y",strtotime($_POST["to_date"]));
$process_type = "Manual";
}else{ // from auto scheduller
$from_date = date("Y-m-01");
$to_date = date("Y-m-d");
$month = date("m");
$year = date("Y");
$process_type = "Auto";
}
//DeviceLogs_1_2023
$log_tbl = "DeviceLogs_".(int)$month."_".$year;
$sql_rslt = $api_model->get_sql_emp("SELECT UserId AS tos_code,convert(varchar, LogDate, 20) as log_date,C1 as check_type,DeviceId as device_id FROM $log_tbl where convert(varchar(10), LogDate, 120) between '$from_date' and '$to_date'");
$json_arr = json_decode(json_encode($sql_rslt),true);
$ins_qry = "";
if($sql_rslt){
$result = implode(',',array_map(function($v){
$code = $v['tos_code'];
$checktime = $v['log_date'];
$checktype = $v['check_type'];
$device_id = $v['device_id'];
$checktype_val = "";
if($checktype === "i" || $checktype === "I"){
$checktype_val = "in";
}else
if($checktype === "o" || $checktype === "O"){
$checktype_val = "out";
}else{
$checktype_val = $checktype;
}
$ins_qry = '("'.$code.'","'.$checktime.'","'. $checktype_val .'","'.$device_id.'")';
return $ins_qry;
}, $json_arr));
if($result){
$trunc_qry = "TRUNCATE table tbl_log_temp"; //create and insert temp tbl
$api_model->get_mysql_array($trunc_qry);
$sql = "insert into tbl_log_temp(tos_code,log_date,check_type,device_id) values $result";
$api_model->get_mysql_obj($sql);
$rslt = $api_model->get_mysql_obj("CALL itsp_time_log()"); //filter data
$end = date("Y-m-d H:i:s");
$api_model->updated_cron_status("Etime Data",$frm,$rslt,$start,$end,$process_type,$today);
}
return_rslt($frm,$rslt);
}
}else
if($frm === "read_etime_data"){
$start = date("Y-m-d H:i:s");
$process_type = $_POST["manual"];
if($process_type === "manual"){
$timelog_date = $_POST["date"];
$timelog_month = date("m-Y",strtotime($timelog_date));
}else{
$timelog_date = date("d-m-Y");
$timelog_month = date("m-Y");
}
$file_name = "DATA_".$timelog_date.".txt";
$path = '../time_log/'.$file_name;
$filename = '../time_log/'.$file_name;
$open = fopen($filename,'r');
$ins_qry = "";
while (!feof($open)){
$get_data = fgets($open);
$arr_data = explode(" ",$get_data);
list($tos_code,$log_date,$log_time,$check_type,$device_id) = $arr_data;
$log_date = str_replace('/', '-', $log_date);
$log_date = date("Y-m-d", strtotime($log_date));
if($tos_code){
$ins_qry .= '("'.$tos_code.'","'.$log_date.' '.$log_time.'","'. $check_type .'","'.$device_id.'"),';
}else{
fclose($open);
break;
}
}
$qry_values = rtrim($ins_qry, ", ");
if($qry_values){
$trunc_qry = "TRUNCATE table tbl_log_temp";
$api_model->get_mysql_array($trunc_qry);
$sql = "insert into tbl_log_temp(tos_code,log_date,check_type,device_id) values $qry_values";
$api_model->get_mysql_obj($sql);
$rslt = $api_model->get_mysql_obj("CALL itsp_time_log()");
$end = date("Y-m-d H:i:s");
$api_model->updated_cron_status("Time Log Update",$frm,$rslt,$start,$end,$process_type,$timelog_date);
}
return_rslt($frm,$rslt);
}else
if($frm === "send_lop_details"){
// $mi_month = "11-2022";
$mi_month = $_POST['process_month'];
if(!$mi_month){
$mi_month = date("m-Y");
}
$mon_name_year = date("M-Y",strtotime("01-".$mi_month));
//MAIL CONFIGURATIONS
$config_result = $api_model->email_configurations();
//LOP DAYS COLUMN MAPPING
$lop_label_qry = 'SELECT db_column FROM cw_payroll_function_map WHERE loc_name = "lop_days" and trans_status = 1';
$lop_label_rslt = $api_model->is_exit_data($lop_label_qry);
$lop_label = $lop_label_rslt[0]->db_column;
$table_data = '';
$table_body = '';
if(!$lop_label){
echo json_encode(array('success' => FALSE, 'message' =>"Please Map LOP Name in Function Column Mapping..!",'table_data' => $table_data));
}else{
$emp_mi_lop_qry = 'select cw_monthly_input.employees_id,cw_employees.employee_code,cw_employees.emp_name,cw_employees.company_email_id,cw_monthly_input.'.$lop_label.' as lop_days from cw_monthly_input INNER JOIN cw_employees ON cw_employees.employee_code = cw_monthly_input.employee_code where cw_monthly_input.process_month = "'.$mi_month.'" and cw_monthly_input.'.$lop_label.' != 0.00 and cw_monthly_input.trans_status = 1 and cw_employees.trans_status = 1';
$emp_mi_lop_rslt = $api_model->get_mysql_array($emp_mi_lop_qry);
if($emp_mi_lop_rslt){
$mail_status_name = '';
$email_subject = 'Employee LOP Details';
$bcc_mail = '';
$get_attachment = '';
$bcc_email_column = '';
if(count($emp_mi_lop_rslt) === 0){
echo json_encode(array('success' => FALSE, 'message' =>"No Data Available..!",'table_data' => $table_data));
}else{
foreach ($emp_mi_lop_rslt as $key => $value){
$emp_code = $value['employee_code'];
$emp_name = $value['emp_name'];
$get_to_mail = $value['company_email_id'];
$lop_days = $value['lop_days'];
$email_design = "<!DOCTYPE html><html> <body><b>Dear $emp_name($emp_code)</b>,<br>
This is to keep you informed that based on your attendance record for the month of $mon_name_year, your LOP days will be $lop_days.
Human Resources ";
if($get_to_mail){
$mail_send_status = $api_model->send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design,$bcc_mail,$get_attachment,$bcc_email_column);
//TABLE BODY CREATE
if($mail_send_status){
$table_body .= "<tr>
<td>$emp_code</td>
<td>$emp_name</td>
<td>$mail_send_status</td>
</tr>";
}
}
}
if($table_body){
$table_data = "<table id='email_sts_table' class='table table-striped table-hover' style='width:100% !important;'>
<thead>
<tr>
<th>Employee Code</th>
<th>Employee Name</th>
<th>Email Status</th>
</tr>
</thead>
<tbody>
$table_body
</tbody>
</table>";
echo json_encode(array('success' => TRUE, 'message' =>"Mail Send Details.!",'table_data' => $table_data));
}else{
echo json_encode(array('success' => FALSE, 'message' =>"Mail Not Send..!",'table_data' => $table_data));
}
}
}else{
echo json_encode(array('success' => FALSE, 'message' =>"Monthly Input not Processed For this Month..!",'table_data' => $table_data));
}
}
}else
if($frm === "birthday_wishes"){
$current_date = date('Y-m-d');
$select_qry = '';
$where_qry = 'and termination_status = 0 and date_format(str_to_date(date_of_birth, "%Y-%m-%d") , "%d-%m") = date_format(str_to_date("'.$current_date.'", "%Y-%m-%d") , "%d-%m")';
$rslt = $api_model->send_wishes(56,$select_qry,$where_qry);
return_rslt($frm,$rslt);
}else
if($frm === "work_anniversary"){
$current_date = date('Y-m-d');
$select_qry = 'TIMESTAMPDIFF(YEAR, date_of_joining, CURDATE()) as year_exp';
$where_qry = 'and date_format(str_to_date(date_of_joining, "%Y-%m-%d") , "%d-%m") = date_format(str_to_date("'.$current_date.'", "%Y-%m-%d") , "%d-%m")';
$rslt = $api_model->send_wishes(57,$select_qry,$where_qry);
return_rslt($frm,$rslt);
}else
if($frm === "process_live_entry"){
$today = date("Y-m-d");
$start = date("Y-m-d H:i:s");
$rslt = $api_model->process_attendance("CALL itsp_prcatt_live_entry('$today','$today','')");
$end = date("Y-m-d H:i:s");
$api_model->updated_cron_status("Process Live Entry",$frm,$rslt,$start,$end,'Auto',$today);
return_rslt($frm,$rslt);
}else
if($frm === "process_time_entry"){
$monthday_data = $api_model->get_tos_month_day(3,"");
$day_start = $monthday_data['start_date'];
$today = date("Y-m-d");
$day_end = date('Y-m-d', strtotime('-1 day', strtotime($today)));
$start = date("Y-m-d H:i:s");
$rslt = $api_model->process_attendance("CALL itsp_prcatt('$day_start','$day_end','')");
$end = date("Y-m-d H:i:s");
$api_model->updated_cron_status("Process Time Entry",$frm,$rslt,$start,$end,'Auto',$today);
return_rslt($frm,$rslt);
}else /*PASTE BY ANDREWS*/
if($frm === "emp_confirm_mail"){
$start = date("Y-m-d H:i:s");
$current_date = date('Y-m-d');
$rslt = $api_model->send_confirmation_mail(56);
$end = date("Y-m-d H:i:s");
$api_model->updated_cron_status("Manager Confirmation Mail",$frm,$rslt,$start,$end,'Auto',$current_date);
return_rslt($frm,$rslt);
}else
if($frm === "auto_mail_send"){
//AUTO EMAIL ONLY GET FROM EMAIL INFO TABLE
$email_info_qry = 'SELECT cw_email_info.*,email_subject,email_design FROM cw_email_info INNER JOIN cw_mail_design ON cw_mail_design.email_design_for = cw_email_info.prime_email_info_id where cw_email_info.process_type = 2 and cw_email_info.trans_status = 1 and cw_mail_design.trans_status = 1';
$email_info_rslt = $api_model->is_exit_data($email_info_qry);
$email_info_arr = json_decode(json_encode($email_info_rslt),true);
//GET ALL EMAIL INFO ID FROM EMAIL INFO QRY
$email_info_id_arr = array_column($email_info_arr, 'prime_email_info_id');
$email_info_id_val = implode(',',$email_info_id_arr);
//GET ALL MODULE NAME FROM EMAIL INFO QRY
$all_module_id_arr = array_column($email_info_arr, 'email_info_module_id');
$all_module_id_val = implode('","',$all_module_id_arr);
// print_r($all_module_id_val);die;
//GET EMAIL BLOCK AND EMAIL TABLE QUERY
$column_data_qry = 'select email_table_email_column,cw_email_block.prime_email_block_id,cw_email_block.email_block_name,email_block_for,email_block_type,email_block_table,email_block_column,email_name_table,email_table_column,email_table_email_column,email_table_list FROM cw_email_block INNER JOIN cw_email_table on cw_email_table.email_name_table = cw_email_block.prime_email_block_id WHERE cw_email_block.trans_status = 1 and cw_email_table.trans_status = 1 and cw_email_block.email_block_for in ('.$email_info_id_val.') order by cw_email_block.email_block_type';
$column_data_rslt = $api_model->get_mysql_array($column_data_qry);
$email_block_rslt = array_reduce($column_data_rslt, function($result, $arr){
$result[$arr['email_block_for']][] = $arr;
return $result;
}, array());
//GET ALL EMAIL BLOCK ID FROM EMAIL BLOCK QRY
$email_block_id_arr = array_column($column_data_rslt, 'prime_email_block_id');
$email_block_id_val = implode(',',$email_block_id_arr);
//EMAIL WHERE CONDITION QUERY
$where_qry = 'select where_condition,where_for_id from cw_email_table_where where where_for_id in ('.$email_block_id_val.') and trans_status = 1';
$where_result = $api_model->get_mysql_array($where_qry);
$where_result = array_reduce($where_result, function($result, $arr){
$result[$arr['where_for_id']] = $arr;
return $result;
}, array());
// print_r($where_result);die;
//EMAIL JOIN CONDITION QUERY
$table_join_qry = 'select line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort,email_table_for_id from cw_email_table_join where email_table_for_id in ('.$email_block_id_val.') and trans_status = 1';
$table_join_rlst = $api_model->get_mysql_array($table_join_qry);
$table_join_rlst = array_reduce($table_join_rlst, function($result, $arr){
$result[$arr['email_table_for_id']][] = $arr;
return $result;
}, array());
//EMAIL CONTENT JOIN CONDITION QUERY
$table_content_join_qry = 'select line_prime_table_content,line_prime_col_content,line_join_type_content,line_join_table_content,line_join_col_content,line_sort_content,email_content_table_for_id from cw_email_content_table_join where email_content_table_for_id in ('.$email_block_id_val.') and trans_status = 1';
$table_content_join_rslt = $api_model->get_mysql_array($table_content_join_qry);
$table_content_join_rslt = array_reduce($table_content_join_rslt, function($result, $arr){
$result[$arr['email_content_table_for_id']][] = $arr;
return $result;
}, array());
$form_qry = 'select prime_module_id,prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id in ("'.$all_module_id_val.'") and trans_status = "1"';
$form_result = $api_model->get_mysql_array($form_qry);
$form_result = array_reduce($form_result, function($result, $arr){
$result[$arr['prime_module_id']][$arr['label_name']][] = $arr;
return $result;
}, array());
$session_query = 'select session_value from cw_session_value where session_for = 1 and trans_status = "1"';
$session_result = $api_model->is_exit_data($session_query);
// print_r($session_result);die;
//MAIL CONFIGURATIONS
$config_result = $api_model->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';
// $config_result = $api_model->is_exit_data($config_query);
$attachment_qry = 'SELECT prime_print_design_id,print_design FROM cw_print_design WHERE trans_status = 1';
$attachment_rlst = $api_model->get_mysql_array($attachment_qry);
$attachment_rlst = array_reduce($attachment_rlst, function($result, $arr){
$result[$arr['prime_print_design_id']] = $arr;
return $result;
}, array());
$get_to_mail = "";
$cc_mail_column = "";
//QUERY AND REPLACE PROCESS START
//EMAIL INFO AND EMAIL DESIGN LOOP
foreach ($email_info_rslt as $key => $value){
$prime_email_info_id = $value->prime_email_info_id;
$control_name = $value->email_info_module_id;
$pdf_name = $value->email_info_name;
$email_subject = $value->email_subject;
$email_design = $value->email_design;
$email_attach_column = $value->email_attach_column;
$email_attach_type = $value->email_attach_type;
//GET FROM ARRAY
$attachment = '';
if((int)$email_attach_type === 1){
$attachment = $attachment_rlst[$email_attach_column]['print_design'];
}
$email_block_rslts = $email_block_rslt[$prime_email_info_id];
$bcc_email_column = '';
//EMAIL BLOCK AND EMAIL TABLE BASED LOOP
foreach ($email_block_rslts as $block_key => $block_value){
$email_block_id = $block_value['prime_email_block_id'];
$email_block_for = $block_value['email_block_for'];
$email_block_type = $block_value['email_block_type'];
$email_block_table = $block_value['email_block_table'];
$email_block_column = $block_value['email_block_column'];
$select_qry_column = $block_value['email_table_column'];
// $select_attach_col = $block_value['email_table_attach'];
$email_column = $block_value['email_table_email_column'];
$mail_status_name = $block_value['email_block_name'];
// $bcc_mail = $block_value['bcc_mail'];
$bcc_mail = '';
//FOR PRIMARY EMAIL TYPE
if((int)$email_block_type === 1){
$where_prime_id = "cw_".$control_name.".prime_".$control_name."_id";
$prime_select_column = $select_qry_column;
}
$table_join_result = array();
$table_content_join_result = array();
if(count($table_join_rlst) > 0){
$table_join_result = $table_join_rlst[$email_block_id];
}
if(count($table_content_join_rslt) > 0){
$table_content_join_result = $table_content_join_rslt[$email_block_id];
}
$line_table_query = "";
//EMAIL TABLE JOIN ARRAY
if(count($table_join_result)){
foreach ($table_join_result as $join_key => $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'];
if((int)$line_sort === 1){
$line_prime_table = " $line_prime_table ";
$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{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col ";
}
}
}
if(!$line_table_query){
$line_table_query = " $email_block_table ";
}
//EMAIL CONTENT TABLE JOIN ARRAY
$line_table_content_query = "";
if(count($table_content_join_result)){
foreach ($table_content_join_result as $join_key => $table){
$line_prime_table_content = $table['line_prime_table_content'];
$line_prime_col_content = $table['line_prime_col_content'];
$line_join_type_content = $table['line_join_type_content'];
$line_join_table_content = $table['line_join_table_content'];
$line_join_col_content = $table['line_join_col_content'];
$line_sort_content = $table['line_sort_content'];
if((int)$line_sort === 1){
$line_prime_table_content = " $line_prime_table_content ";
$line_join_table_content = " $line_join_table_content on $line_join_col_content = $line_prime_col_content ";
$line_table_content_query .= " $line_prime_table_content $line_join_type_content join $line_join_table_content";
}else{
$line_table_content_query .= " $line_join_type_content join $line_join_table_content on $line_join_col_content = $line_prime_col_content ";
}
}
}
if(!$line_table_content_query){
$line_table_content_query = " $email_block_table ";
}
if(!$select_qry_column){
$select_qry_column = "*";
}else{
$select_query = "";
$pick_query = "";
$select_attach_column = "";
$map_column = explode(",",$select_qry_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);
$form_result_array = $form_result[$control_name][$column];
foreach($form_result_array 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.' , ';
$select_attach_column .= '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;
if((int)$email_block_type === 2){
$email_column = str_replace($pick_table,$pick_query_as,$email_column);
}
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$select_attach_column .= "$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 , ";
$select_attach_column .= "$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,";
$select_attach_column .= "$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_query .= "$table_name.$label_name , ";
$select_attach_column .= "$table_name.$label_name , ";
}else{
$select_query .= "$table_name.$label_name , ";
$select_attach_column .= "$table_name.$label_name , ";
}
}
}
}
//WHERE CONDITION FOR EMAIL BLOCK TABLES BASED TRANS STATUS ADD
$where_trans = "";
$where_trans_info = explode(",",$email_block_table);
foreach($where_trans_info as $trans_info){
$where_trans .= "$trans_info.trans_status = 1 and ";
}
$where_trans = rtrim($where_trans,'and ');
$where_condition = "";
//WHERE CONDITIONS FOR EMAIL TABLE JOIN MAP WHERE
if(count($where_result)){
$where_condition = str_replace('^','"',$where_result[$email_block_id]['where_condition']);
$where_condition = str_replace('@today_date@',date("Y-m-d"),$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");
// foreach($session_result as $rslt){
// $session_value = $rslt->session_value;
// if($session_value !== "access_data"){
// $exist_val = "@".$session_value."@";
// if(array_key_exists($session_value,$session_date_list)){
// $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_attach_column = rtrim($select_attach_column,' , ');
$email_column = 'CONCAT_WS(",",'.$email_column.') as mail_column';
if((int)$email_block_type === 1){
$prime_qry = "select $select_query$select_attach_column from ".$line_table_content_query." $pick_query where $where_trans $where_condition";
$prime_result = $api_model->is_exit_data($prime_qry);
$email_qry = "select $email_column from ".$line_table_query." $pick_query where $where_trans $where_condition";
$email_result = $api_model->is_exit_data($email_qry);
// $get_to_mail = $email_result[0]->mail_column;
}else{
// $email_qry = "select $email_column from ".$line_table_query." $pick_query where $where_trans $where_condition";
// $email_result = $api_model->is_exit_data($email_qry);
// $bcc_email = $email_result[0]->mail_column;
// $cc_mail_column .= $bcc_email.",";
// $bcc_email_column = rtrim($cc_mail_column,',');
$email_qry = "select $email_column from ".$line_table_query." $pick_query where $where_trans $where_condition";
$email_result = $api_model->get_mysql_array($email_qry);
//GET ALL EMAIL BLOCK ID FROM EMAIL BLOCK QRY
$cc_mail_column = array_column($email_result, $email_column);
$bcc_email_column .= implode(',',$cc_mail_column_arr);
}
// print_r($prime_result);
}
// echo "prime_qry => $prime_qry </br>";
// echo "email_qry => $email_qry";
// print_r($prime_result);
// print_r($email_result);die;
if($prime_result){
$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");
$data['email_sts'] = true;
foreach($prime_result as $prime_key => $rslt){
// $count++;
$map_column = explode(",",$prime_select_column);
$td_line = "";
$email_design_assign = $email_design;
// if($attachment){
$email_attachment_assign = $attachment;
// }
// print_r($map_column);
foreach($map_column as $table_column){
$map_column = explode(".",$table_column);
$column = $map_column[1];
$value = $rslt->$column;
$replace_val = "@".$column."@";
$email_design_assign = str_replace($replace_val,$value,$email_design_assign);
$email_attachment_assign = str_replace($replace_val,$value,$email_attachment_assign);
foreach($assign_date_formate_list as $key=>$formate){
$start = "@".$key."_";
$end = "_".$key."@";
$replace_val = $start.$column.$end;
$replace_val = $start.$column.$end;
// echo $value;
$date_value = date_create($value);
$replace_value = '';
// echo $date_value;
if($date_value){
$replace_value = date_format($date_value,$formate);
}
$email_design_assign = str_replace($replace_val,$replace_value,$email_design_assign);
$email_attachment_assign = str_replace($replace_val,$replace_value,$email_attachment_assign);
}
}
//EMAIL ATTEACHEMENT GET
$get_attachment = '';
if($email_attachment_assign){
$get_attachment = $api_model->get_email_attachment($email_attachment_assign,$email_subject,$pdf_name);
}
if(count($email_result)){
// foreach($email_result as $email_col){
// print_r($email_result);
// $get_to_mail = $email_col->mail_column;
$get_to_mail = $email_result[$prime_key]->mail_column;
if($get_to_mail){
$mail_send_status = $api_model->send_mail_dynamic($mail_status_name,$get_to_mail,$config_result,$email_subject,$email_design_assign,$bcc_mail,$get_attachment,$bcc_email_column);
//
}
// }
}else{
$mail_send_status = "";
}
}
}
}
}else{
echo json_encode(array(
'Status' => 400,
'success' => False,
'data' => "Bad Request"
));
}
function return_rslt($frm,$rslt){
if(!$rslt){
echo json_encode(array('success' => FALSE, 'sts' =>"No Record found"));
}else{
echo json_encode(array('success' => TRUE, 'sts' =>"Processed Successfully","$frm" => $rslt));
}
}
?>