File: /home/cafsindia/uds.cafsinfotech.in/Dump/ZRM/application/controllers/Email_process.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Email_process extends Action_controller{
public function __construct(){
parent::__construct('email_process');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$data['table_headers'] = '';
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
//Get Design Modules
$print_module_qry = 'SELECT print_info_module_id FROM `cw_print_info` where trans_status = 1';
$print_module_info = $this->db->query("CALL sp_a_run ('SELECT','$print_module_qry')");
$print_module_result = $print_module_info->result();
$print_module_info->next_result();
$module_list[""] = "---- Select Print From ----";
foreach($print_module_result as $for){
$module_id = $for->print_info_module_id;
$module_list[$module_id] = ucwords(str_replace("_", " ", $module_id));
}
$data['email_for'] = $module_list;
$config_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_mail_configurations` where trans_status = 1 and mail_status = 1')");
$config_result = $config_info->result();
$config_info->next_result();
$configuration[""] = "---- Select Configuration ----";
foreach($config_result as $config){
$prime_mail_configurations_id = $config->prime_mail_configurations_id;
$configuration_name = $config->configuration_name;
$configuration[$prime_mail_configurations_id] = $configuration_name;
}
$data['configuration'] = $configuration;
//template design from employee module only
$table_query = 'select * from cw_print_info where trans_status = 1 and print_info_module_id="employees"';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$table_result = $table_info->result();
$table_info->next_result();
$table_list[""] = "---- Select Table ----";
foreach($table_result as $table){
$design_name = strtolower(str_replace(" ","_",$table->print_info_name));
$print_info_name = $table->print_info_name;
$table_list[$design_name] = $print_info_name;
}
$data['pdf_template'] = $table_list;
$company_info = $this->company_info();
$payslip_based_on = $company_info[0]->payslip_based_on;
$form_setting_query= 'select label_name,view_name,pick_table,pick_list from cw_form_setting where prime_module_id = "employees" and label_name="'.$payslip_based_on.'" ORDER BY input_for,field_sort asc';
$form_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$form_setting_query')");
$form_setting_rslt = $form_setting_info->result_array();
$form_setting_info->next_result();
$payslip_pick_table = $form_setting_rslt[0]['pick_table'];
$payslip_pick_list = $form_setting_rslt[0]['pick_list'];
$payslip_pick_arr = explode(',', $payslip_pick_list);
$payslip_pick_list1 = $payslip_pick_arr[0];
$payslip_pick_list2 = $payslip_pick_arr[1];
$filter_qry = "";
if($payslip_pick_table === "cw_category"){
$filter_qry = " and prime_category_id != 1";
}
$column_qry = 'SELECT '.$payslip_pick_list.' from '.$payslip_pick_table.' where trans_status = 1 '.$filter_qry.'';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_rslt = $column_info->result();
$column_info->next_result();
$pick_key = array_column($column_rslt, $payslip_pick_list1);
$pick_val = array_column($column_rslt, $payslip_pick_list2);
$column_list = array_combine( $pick_key, $pick_val);
$column_name = $form_setting_rslt[$payslip_based_on]['view_name'];
$data['column_list'] = array("" => "---- $column_name ----") + $column_list;
$data['payslip_based_on'] = ucwords(str_replace('_', '', $payslip_based_on));
$this->load->view("$this->control_name/manage",$data);
}
//Get All the Employees by Category
public function get_employee_list(){
$column_name = implode(',',array_filter($this->input->post('column_name')));
$email_for = $this->input->post('email_for');
$process_month = $this->input->post('process_month');
$pdf_template = $this->input->post('pdf_template');
$where_column = $this->where_month_value($email_for,$pdf_template,$process_month);
$where_column_arr = explode(',', $where_column);
$where_month_col = " $where_column";
$where_column_count = count($where_column_arr);
if((int)$where_column_count === 0){
echo json_encode(array('success'=>FALSE,'message'=>"Please set Month Column for print setting"));
exit(0);
}
$print_block_qry = 'select print_block_table,prime_print_block_id from cw_print_info inner join cw_print_block on cw_print_block.print_block_for = cw_print_info.prime_print_info_id where cw_print_info.trans_status = 1 and cw_print_block.trans_status = 1 and prime_print_info_id = "'.$pdf_template.'"';
$print_block_info = $this->db->query("CALL sp_a_run ('SELECT','$print_block_qry')");
$print_block_rslt = $print_block_info->result_array();
$print_block_info->next_result();
$print_block_table = explode(',', $print_block_rslt[0]['print_block_table']);
$block_count = count($print_block_table);
$block_id = $print_block_rslt[0]['prime_print_block_id'];
$where_qry = 'select where_condition from cw_print_table_where where trans_status = 1 and cw_print_table_where.where_for_id = "'.$block_id.'"';
$where_data = $this->db->query("CALL sp_a_run ('SELECT','$where_qry')");
$where_result = $where_data->result_array();
$where_data->next_result();
$tbl_where_condition = $where_condition[0]['where_condition'];
if((int)$block_count > 1){
$print_table_qry = 'select line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort from cw_print_table where cw_print_table.trans_status = 1 and cw_print_table.print_table_for_id = "'.$block_id.'"';
$print_table_info = $this->db->query("CALL sp_a_run ('SELECT','$print_table_qry')");
$print_table_rslt = $print_table_info->result_array();
$print_table_info->next_result();
$cutome_table_check = array('transactions'=>'cw_transactions');
$line_table_query = "";
foreach($print_table_rslt as $table_result){
$line_prime_table = $table_result['line_prime_table'];
$line_prime_col = $table_result['line_prime_col'];
$line_join_type = $table_result['line_join_type'];
$line_join_table = $table_result['line_join_table'];
$line_join_col = $table_result['line_join_col'];
$line_sort = $table_result['line_sort'];
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
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 ";
}
}
}else{
$line_table_query = $print_block_rslt[0]['print_block_table'];
}
$where_trans = "";
$where_trans_info = explode(",",$print_block_rslt[0]['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 ');
$company_info = $this->company_info();
$payslip_based_on = $company_info[0]->payslip_based_on;
$emp_query = 'select cw_employees.employee_code,cw_employees.emp_name from '.$line_table_query.' where '.$where_trans.' and cw_employees.'.$payslip_based_on.' in('.$column_name.')'.$tbl_where_condition.' '.$where_month_col.' group by cw_employees.employee_code';
//echo $emp_query; die;
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$emp_list = "<option value=''>---- Select Employee ----</option>";
foreach($emp_result as $result){
$id = $result->employee_code;
$name = ucwords($result->emp_name);
$emp_code = $result->employee_code;
$emp_list .= "<option value='$id'>$emp_code - $name</option>";
}
echo $emp_list;
}
//send email for only employee modules data...
public function send_email(){
$email_for = $this->input->post('email_for');
$configuration = $this->input->post('configuration');
$category = $this->input->post('category');
$employees = $this->input->post('employees');
$email = $this->input->post('email');
$process_month = $this->input->post('process_month');
$subject = $this->input->post('subject');
$content = $this->input->post('content');
$password = $this->input->post('password');
$pdf_template = $this->input->post('pdf_template');
if((int)$email === 1){
$email = "company_email_id";
}else{
$email = "personal_email_id";
}
//Get Email Configuration data
$config_query = 'select smtp_server,sender_name,bcc,port_no,sender_email,mail_username,mail_password from cw_mail_configurations where prime_mail_configurations_id ="'.$configuration.'" and trans_status = 1 and mail_status = 1';
$config_info = $this->db->query("CALL sp_a_run ('SELECT','$config_query')");
$config_result = $config_info->result();
$config_info->next_result();
$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;
$mail_password = $config_result[0]->mail_password;
if($mail_password === $password){
$mail_staus = True;
}else{
$mail_staus = False;
}
$bcc_mail = "";
foreach ($bcc as $bcc_key => $bcc_mails){
$bcc_mail .= "\$mail->AddCC(".$bcc_mails.");<br/>";
}
$company_info = $this->company_info();
$payslip_based_on = $company_info[0]->payslip_based_on;
$encrypted_pdf = $company_info[0]->encrypted_pdf;
$db_name = $this->config->item("db_name");
$pdf_name_query = 'select prime_print_info_id,print_info_module_id,print_info_name from cw_print_info where prime_print_info_id = "'.$pdf_template.'" and print_info_module_id = "'.$email_for.'" and trans_status = 1';
$pdf_name_info = $this->db->query("CALL sp_a_run ('SELECT','$pdf_name_query')");
$pdf_name_result = $pdf_name_info->result();
$pdf_name_info->next_result();
$module_name = $pdf_name_result[0]->print_info_module_id;
$template_design = strtolower(str_replace(' ', '_', $pdf_name_result[0]->print_info_name));
if($mail_staus){
$email_line_name = $this->unique_code($code="ECAMP");
$insert_query = 'INSERT into cw_email_line (email_line_name,mail_configuration,email_for,process_month,pdf_template,trans_created_by,trans_created_date) values ("'.$email_line_name.'","'.$configuration.'","'.$email_for.'","'.$process_month.'","'.$pdf_template.'","'.$this->logged_id.'","'.date('Y-m-d H:i:s').'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$email_query = 'SELECT '.$email.','.$payslip_based_on.',cw_employees.employee_code,cw_employees.emp_name FROM cw_employees inner join cw_category on cw_category.prime_category_id = cw_employees.role where cw_employees.trans_status = 1';
$email_info = $this->db->query("CALL sp_a_run ('SELECT','$email_query')");
$email_result = $email_info->result();
$email_info->next_result();
$email_result = array_map(function($emp){
$emp_array['employee_data'] = $emp;
$emp_array['employee_code'] = $emp->employee_code;
return $emp_array;
}, $email_result);
$email_result = array_column($email_result,'employee_data','employee_code');
$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 trans_status = "1" and prime_module_id = "employees" and label_name = "'.$payslip_based_on.'" ';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result_arr = $form_data->result_array();
$form_data->next_result();
//Get Payslip based on column name - START
$pick_table = $form_result_arr[0]['pick_table'];
$pick_list = $form_result_arr[0]['pick_list'];
$name_qry = 'select '.$pick_list.' from '.$pick_table.' where trans_status = 1';
$name_data = $this->db->query("CALL sp_a_run ('SELECT','$name_qry')");
$name_result = $name_data->result_array();
$name_data->next_result();
$pick_list_arr = explode(",",$pick_list);
$pick_id = $pick_list_arr[0];
$pick_name = $pick_list_arr[1];
$payslip_arr = array();
foreach ($name_result as $key => $value) {
$payslip_arr[$value[$pick_id]] = str_replace(" ", "_", $value[$pick_name]);
}
foreach($employees as $key => $emp_code){
$emp_email_data = $email_result[$emp_code];
$to_email = $emp_email_data->$email;
$emp_name = $emp_email_data->emp_name;
$payslip_based_val = $emp_email_data->$payslip_based_on;
$column_name = $payslip_arr[$payslip_based_val]; // get exact popup data text
$folder_path = "./pdf_generation/".$email_for."/".$template_design."/".$process_month.'/'.$payslip_based_on."_".strtolower($column_name);
if((int)$encrypted_pdf === 1){
$file_name = $db_name."_".$emp_code."_".$process_month;
$enc_file = $this->encryptFilename($file_name,$db_name);
$enc_file_name = $enc_file."_".$emp_code;
}else{
$enc_file_name = $emp_code;
}
$folder_path = strtolower($folder_path).'/'.$enc_file_name.'.pdf';
//echo $folder_path; die;
if($to_email !== ""){
$mail = new PHPMailer();
try{
//$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 = $username; // Your Email Address
$mail->Password = $password; // Your Password
$mail->SMTPSecure = 'tls'; // Check Your Server's Connections for TLS or SSL
$mail->From = $sender_email;
$mail->FromName = $sender_name;
if($to_email){
$mail->AddAddress($to_email);
}
if((int)count($bcc) > 0){
foreach ($bcc as $bcc_key => $bcc_mails){
$mail->AddCC($bcc_mails);
}
}
$mail->IsHTML(true);
$mail->Subject = $subject;
$mail->Body = $content;
//Attachment
$folder = $folder_path;
if(file_exists($folder)){
$mail->addAttachment($folder); // Add attachments
$mail= $mail->Send();
if($mail){
$status = 1;
$msg = "Mail Sent Successfully";
$tr_line .= "<tr><td>$emp_code</td><td>$emp_name</td><td style='color:green !important;'>$msg</td></tr>";
}else{
$status = 0;
$msg = "Mail Not Sent Successfully";
$tr_line .= "<tr><td>$emp_code</td><td>$emp_name</td><td style='color:#ff0303 !important;'>$msg</td></tr>";
}
}else{
$status = 0;
$msg = "File Not Exist";
$tr_line .= "<tr><td>$emp_code</td><td>$emp_name</td><td style='color:green !important;'>$msg</td></tr>";
}
}catch(phpmailerException $e){
$status = 0;
$msg = "Mail Not Sent";
$tr_line .= "<tr><td>$emp_code</td><td>$emp_name</td><td style='color:#ff0303 !important;'>$msg</td></tr>";
}catch(Exception $e){
$status = 0;
$msg = "Mail Not Sent";
$tr_line .= "<tr><td>$emp_code</td><td>$emp_name</td><td style='color:#ff0303 !important;'>$msg</td></tr>";
}
}else{
$status = 0;
$msg = "Email ID is empty, so mail is not send";
$tr_line .= "<tr><td>$emp_code</td><td>$emp_name</td><td style='color:#ff0303 !important;'>$msg</td></tr>";
}
$insert_log_values .= '("'.$insert_id.'","'.$to_email.'","'.$emp_code.'","'.$status.'","'.$msg.'"),';
}
$insert_log_values = rtrim($insert_log_values,',');
$email_log_query = 'INSERT into cw_email_log (email_line_id,email_id,employee_code,status,message) values '.$insert_log_values.'';
$email_log_info = $this->db->query("CALL sp_a_run ('INSERT','$email_log_query')");
$email_log_result = $email_log_info->result();
$email_log_info->next_result();
$table_info = "<table class='table table-bordered'>
<thead>
<tr>
<th>Employee Code</th>
<th>Employee Name</th>
<th>Mail Status</th>
</tr>
</thead>
<tbody>$tr_line</tbody>
</table>";
echo json_encode(array('success'=>true,'message'=>$msg,"table_data"=>$table_info,"email_sts"=>$status));
}else{
echo json_encode(array('success'=>false,'message'=>"Please Enter the Valid Credentials..."));
}
}
// return unique code
public function unique_code($code="UN"){
$YearMap = array('2019'=>'A','2020'=>'B','2021'=>'C','2022'=>'D','2023'=>'E','2024'=>'F','2025'=>'G','2026'=>'H','2027'=>'I','2028'=>'J','2029'=>'K','2030'=>'L');
$MonthMap = array('1'=>'J','2'=>'K','3'=>'L','4'=>'M','5'=>'N','6'=>'O','7'=>'P','8'=>'Q','9'=>'R','10'=>'S','11'=>'T','12'=>'U');
$DayMap = array('1'=>'A','2'=>'B','3'=>'C','4'=>'D','5'=>'E','6'=>'F','7'=>'G','8'=>'H','9'=>'I','10'=>'J','11'=>'K','12'=>'L','13'=>'M','14'=>'N','15'=>'O','16'=>'P','17'=>'Q','18'=>'R','19'=>'S','20'=>'T','21'=>'U','22'=>'V','23'=>'W','24'=>'X','25'=>'Y','26'=>'Z','27'=>'1','28'=>'2','29'=>'3','30'=>'4','31'=>'5');
$HourMap = array('0'=>'A','1'=>'B','2'=>'C','3'=>'D','4'=>'E','5'=>'F','6'=>'G','7'=>'H','8'=>'I','9'=>'J','10'=>'K','11'=>'L','12'=>'M','13'=>'N','14'=>'O','15'=>'P','16'=>'Q','17'=>'R','18'=>'S','19'=>'T','20'=>'U','21'=>'V','22'=>'W','23'=>'X');
$loctime = time();
$unq4 = substr($loctime,-4,4);
$unq2 = mt_rand(10,99);
$xunq2 = mt_rand(10,99);
$year = $YearMap[ltrim(date('Y'), '0')];
$month = $MonthMap[ltrim(date('n'), '0')];
$day = $DayMap[ltrim(date('j'), '0')];
$hr = ltrim(date('H'), '0');
if($hr == "")
$hr = "0";
$hour = $HourMap[$hr];
$uniqueCode = $code. $year . $month . $day . $hour. $unq4 . $unq2 . $xunq2;
return $uniqueCode;
}
//collecting pdf template
public function get_pdf_template(){
$email_for = $this->input->post('email_for');
if($email_for){
$print_type_query = 'select * from cw_print_info where print_info_module_id ="'.$email_for.'" and trans_status = 1 ';
$print_type_info = $this->db->query("CALL sp_a_run ('SELECT','$print_type_query')");
$print_type_result = $print_type_info->result();
$print_type_info->next_result();
$print_template_list = "<option value=''>--- Select Template ---</option>";
foreach($print_type_result as $rslt){
$print_info_name = $rslt->print_info_name;
$prime_print_info_id = $rslt->prime_print_info_id;
$print_template_list .= "<option value='$prime_print_info_id'>$print_info_name</option>";
}
echo $print_template_list;
}else{
$print_template_list = "<option value=''>--- No template available ---</option>";
echo $print_template_list;
}
}
}
?>