<?php
/***
 * Auth: Aarti Ojha
 * Date: 04/03/2024
  * This file is for handling common function used in all modules
 */
include_once __DIR__ . "/../config/web_mysqlconnect.php";

// [vastvikta][25-04-2025]
/* If a case is created for a particlar meter number more than 10 times and closed (on same sub category), the case then should be escalated to the supervisor (sebetet@lec.co.ls) the next time it is created */
function checkAndEscalate($customerid,$subCategory) {
    // MySQL connection parameters
   global $link,$db;

    // Fetch the count of closed cases for the specified meter number and sub-category
    $query = "SELECT COUNT(*) AS case_count FROM $db.web_problemdefination WHERE vCustomerID = $customerid AND vSubCategory = $subCategory AND iCaseStatus =3";
    $result = mysqli_query($link,$query);

    if ($result) {
        $row = mysqli_fetch_assoc($result);;
        $caseCount = $row['case_count'];

        // Check if the count is greater to count
        if ($caseCount >= escalation_case_count()) {
            // Escalate the case to the supervisor
			$sql= "SELECT * FROM $db.tbl_mailformats WHERE MailStatus=1 AND MailTemplateName='multiple_case_creation_alert'";
			$result=mysqli_query($link,$sql);
			$row = mysqli_fetch_assoc($result);
			$subject =$row['MailSubject'];
			$greeting =$row['MailGreeting'];
			$body=$row['MailBody'];
			$signature=$row['MailSignature'];
			$expiry=$row['MailExpiry'];

			$phone_no = get_phone_no($customerid);
			$subject = str_replace("%phone%", $phone_no, $subject);
			$body = str_replace("%phone%", $phone_no, $body);	
			$content = "$greeting$body$signature";

            $supervisorEmail = getEscalationSupervisorMail();
            $status = getMailStatus();
            // insert in mail table
			if($status == '1'){
			mysqli_query($link,"INSERT INTO $db.web_email_information(v_toemail,v_fromemail,v_subject, v_body,email_type,module,i_expiry) VALUES ('$supervisorEmail', 'info@alliance-infotech.com', '$subject', '$content', 'OUT', 'Escalation Mail After 5 Cases Closed','$expiry')");
			}
			return true;
        }
    } else {
        echo "Error: $query<br>{$link->error}";
		return false;
    }

}
function getMailStatus(){
	global $db,$link;
	$query = "SELECT status_active FROM $db.tbl_connection";
    $result = mysqli_query($link, $query);

    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_row($result); // fetches the first row as a numeric array
        return $row[0]; // return the first column value (escalation_mail)
    } else {
        return null; // or handle the error/log it
    }
}
function getEscalationSupervisorMail()
{
    global $link, $db;

    $query = "SELECT sent_mail FROM $db.tbl_connection";
    $result = mysqli_query($link, $query);

    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_row($result); // fetches the first row as a numeric array
        return $row[0]; // return the first column value (escalation_mail)
    } else {
        return null; // or handle the error/log it
    }
}
function escalation_case_count()
{
    global $link, $db;

    $query = "SELECT case_count FROM $db.tbl_connection";
    $result = mysqli_query($link, $query);

    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_row($result); // fetch the first row as a numeric array
        return $row[0]; // return the escalation_case_count
    } else {
        return null; // or 0, depending on how you want to handle empty results
    }
}

function get_phone_no($customerid) {
    global $db, $link;

    $phone_no = null;

    $stmt = $link->prepare("SELECT phone FROM $db.web_accounts WHERE AccountNumber = ?");
    if ($stmt) {
        $stmt->bind_param("s", $customerid);
        $stmt->execute();
        $stmt->bind_result($phone);
        
        if ($stmt->fetch()) {
            $phone_no = $phone;
        }

        $stmt->close();
    }

    return $phone_no;
}

// get email for sending crm daily report(adhoc)
function get_adhoc_mail() {
 global $link,$dbname;
 $query = $link->query("SELECT adhoc_mail FROM $dbname.tbl_connection");
 $row = $query->fetch_row();
 return $row[0];
}
// get from email for sending emails notification
function get_from_mail() 
{
 global $link,$dbname;
 $query = $link->query("SELECT v_username FROM $dbname.tbl_connection");
 $row = $query->fetch_row();
 return $row[0];
}
// Get Customers fullname using vCustomerID : By farhan on 09-04-2021
function getfname($id)
{
 	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select fname from $db.web_accounts where AccountNumber='$id' ; "));
	return $res['fname'];
}

function get_username($userid)
{

	global $db, $link;
	$sql_user="select AtxUserName from $db.uniuserprofile where AtxUserID='$userid'";
	$res_user=mysqli_query($link, $sql_user) or die(mysqli_error($link));
	$row_user=mysqli_fetch_array($res_user);
	$AtxUserName=$row_user['AtxUserName'];

	return($AtxUserName);
}
function getAgentEmail($userid)
{

	global $db, $link;
	$sql_user="select AtxEmail from $db.uniuserprofile where AtxUserID='$userid'";
	$res_user=mysqli_query($link, $sql_user) or die(mysqli_error($link));
	$row_user=mysqli_fetch_array($res_user);
	$AtxEmail=$row_user['AtxEmail'];

	return($AtxEmail);
}

function getFileName($SmartFileName){

	$filename=$SmartFileName;
	$filename=substr($filename, 0, 8);//12Jul2019
	$year=substr($filename, 0, 4);
	$day=substr($filename, 6, 2);
	$m=$year.substr($filename, 4, 2).$day;
	$month1=date('M',strtotime($m));
	//echo '<br>Date::'.$day.$month1.$year;
	$folderpath=$day.$month1.$year."/";

	$path='../calls/'.$folderpath.$SmartFileName.'.wav';
	$pathWithoutExtention='/calls/'.$folderpath.$SmartFileName;
	if (file_exists($path)) {
		$pathWithoutExtention=$pathWithoutExtention.".wav";
	}else{
		// changed to  .wav from .WAV as it is making it .html  after downloading [vasvtvikta][06-02-2025]
		$pathWithoutExtention=$pathWithoutExtention.".wav";
	}
	return  $pathWithoutExtention;

}//end of function 

function color($id)
{

	global $db,$link;
	$sql = "SELECT color FROM $db.web_category WHERE id ='$id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	
	return $row['color'];

}

function getissuename($issue_id, $db)
{
	global $link,$db;
	$sql = "SELECT issue_name FROM $db.web_mst_Issues WHERE id ='$issue_id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	
	return $row['issue_name'];

}

function getlanguagename($lang_id)
{
	global $db,$link;
	$sql = "SELECT lang_Name FROM $db.web_language WHERE id ='$lang_id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	
	return $row['lang_Name'];

}
function get_age_group($id)
{
	global $db,$link;
	$sql = "SELECT * FROM $db.tbl_age_range WHERE id ='$id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	$age=$row['age_group']."-(".$row['age_range'].")";	
	return $age;

}
function get_gender($id)
{
	global $db,$link;
	$sql = "SELECT `name` FROM $db.web_gender WHERE `value` ='$id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	
	return $row['name'];

}
function get_mno($id)
{
	global $db,$link;
	$sql = "SELECT * FROM $db.tbl_mno WHERE `id` ='$id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	
	return $row['category'];

}
function get_isp($id)
{
	global $db,$link;
	$sql = "SELECT * FROM $db.tbl_isp WHERE `id` ='$id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	
	return $row['category'];

}
function comp_type($id)
{
	global $db,$link;
	$sql = "SELECT * FROM $db.tbl_complaint_type WHERE `id` ='$id' ";
	$result = mysqli_query($link,$sql);
	$row = mysqli_fetch_assoc($result);
	
	return $row['category'];

}



function RecordingFileName($SmartFileName){
	$filename=$SmartFileName;
	$filename=substr($filename, 0, 8);//12Jul2019
	$year=substr($filename, 0, 4);
	$day=substr($filename, 6, 2);
	$m=$year.substr($filename, 4, 2).$day;
	$month1=date('M',strtotime($m));
	//echo '<br>Date::'.$day.$month1.$year;
	$folderpath=$day.$month1.$year."/";

	$path='../calls/'.$folderpath.$SmartFileName.'.wav';
	$pathWithoutExtention='/calls/'.$folderpath.$SmartFileName;
	if (file_exists($path)) {
		$pathWithoutExtention=$pathWithoutExtention.".wav";
	}else{
		$pathWithoutExtention=$pathWithoutExtention.".WAV";
	}
	return  $pathWithoutExtention;
}


function ddmmyydateFormat($date)
{
	return !empty($date)  ? date('d-m-Y', strtotime($date)) : $date ;
}

function ddmmyyHisdateTimeFormat($date)
{
	return !empty($date)  ? date('d-m-Y H:i:s', strtotime($date)) : $date ;
}

function yymmddDateFormat($date)
{
	return !empty($date)  ? date('Y-m-d', strtotime($date)) : $date ;

}


function randomKey($length) {
    $pool = array_merge(range(0,9), range('a', 'z'),range('A', 'Z'));

    for($i=0; $i < $length; $i++) {
        $key .= $pool[mt_rand(0, count($pool) - 1)];
    }
    return $key;
}

function case_type($type)
{
	 global $db,$link;
	//echo "<br>select complaint_name, slug from $db.complaint_type where slug	='$type' and  status =1 ; ";
	$res=mysqli_fetch_array(mysqli_query($link,"select complaint_name, slug from $db.complaint_type where slug	='$type' and  status =1 ; "));
	return $res['complaint_name'];
}

function gender($gender)
{
	return ($gender !="") ? ( ($gender =="F") ? 'Female' : 'Male') : '' ;
}

function category($cat)
{
 	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select category from $db.web_category where id='$cat' ; "));
	return $res['category'];
}


function subcategory($subcat)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select subcategory from $db.web_subcategory where id='$subcat' ; "));
	return $res['subcategory'];
}

function cat_from_subcat($id)
{

	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select category from $db.web_subcategory where id='$id' ; "));
	return $res['category'];

}

function source($source)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select source from $db.web_source where id='$source' ; "));
	return $res['source'];
}
// updated the function related to the ticketsatus new table [vastvikta][25-11-2025]
function ticketstatus($ticketstatus)
{
	global $db,$link;
	$sql1 = "select statusLabel from $db.web_ticketstatus where statusCode='$ticketstatus' ";
	$res=mysqli_fetch_array(mysqli_query($link,$sql1));
	return $res['statusLabel'];
}

function getTicketStatusField($id, $column)
{
    global $db, $link;

    $allowed = ['statusLabel','statusCode','stateLabel','stateCode','status','colorCode','createdby','createdon'];

    if (!in_array($column, $allowed)) {
        return null; // avoid SQL injection
    }

    $sql = "SELECT $column FROM {$db}.web_ticketstatus WHERE statusCode = ?";
    $stmt = mysqli_prepare($link, $sql);
    mysqli_stmt_bind_param($stmt, "i", $id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    $row = mysqli_fetch_assoc($result);
    return $row[$column] ?? null;
}


function assignto($assignto)
{
 global $db,$link;
// echo "select AtxUserName from $db.uniuserprofile where AtxUserID='$assignto' ; ";
	$res=mysqli_fetch_array(mysqli_query($link,"select AtxUserName from $db.uniuserprofile where AtxUserID='$assignto' ; "));
	return $res['AtxUserName'];
}

function assignfor($assignfor)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select assignfor from $db.web_assignfor where id='$assignfor' ; "));
	return $res['assignfor'];
}

function project($projectid)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select vProjectName from $db. web_projects where pId='$projectid' ; "));
	return $res['vProjectName'];
}

function city($city)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select city from $db.web_city where id='$city' ; "));
	return $res['city'];
}

function region($region)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select region from $db.web_region where id='$region' ; "));
	return $res['region'];
}

function regional_offices($name)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select full_name from $db.tbl_regional where name='$name' ; "));
	return $res['full_name'];
}

function stakeholder($stakeholder)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select stakeholder from $db.web_stakeholder where id='$stakeholder' ; "));
	return $res['stakeholder'];
}

function typeofcaller($typeofcaller)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select caller from $db.web_caller where id='$typeofcaller' ; "));
	return $res['caller'];
}

function brand($brand)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select brand from $db.web_brand where id='$brand' ; "));
	return $res['brand'];
}

function district($category_id)
{
 	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select brand from $db.web_brand where id='$category_id' ; "));
	return $res['brand'];
}

function village($village)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select vVillage from $db.web_Village where id='$village' ; "));
	return $res['vVillage'];
}
function actiontaken($actiontaken)
{
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select actiontaken from $db.web_actiontaken where id='$actiontaken' ; "));
	return $res['actiontaken'];
}

function displayagentname($userid)
{
	global $db,$link;
	$sql_user="select AtxDisplayName from $db.uniuserprofile where AtxUserID='$userid'";
	$res_user=mysqli_query($link,$sql_user) or die(mysqli_error($link));
	$row_user=mysqli_fetch_array($res_user);
	$AtxDisplayName=$row_user['AtxDisplayName'];

	return($AtxDisplayName);
}

function agentname($userid)
{
	global $db,$link;
	$sql_user="select AtxUserName from $db.uniuserprofile where AtxUserID='$userid'";
	$res_user=mysqli_query($link,$sql_user) or die(mysqli_error($link));
	$row_user=mysqli_fetch_array($res_user);
	$AtxDisplayName=$row_user['AtxUserName'];

	return($AtxDisplayName);
}

function add_audit_log($user_id, $actionlog, $ticketid, $comments, $db, $types='')
{

	global $link;
	$ip = getenv('REMOTE_ADDR');
	$sql_ins1="INSERT INTO $db.web_audit_history(user_id, action, created_on, ticket_id, comments, ip_address, case_process_type) VALUES('$user_id', '$actionlog', NOW(), '$ticketid', '$comments' , '$ip', '$types')";
	mysqli_query($link,$sql_ins1)or die(mysqli_error($link).'Err web_audit_history');
}

function json_diff($update_data,$old_data)
{

   $result = array_diff($update_data,$old_data);   // Check Difference in Two Array 
   return json_encode($result);
}

function getTicketId($ticket)
{
   global $link,$db;
    $res=mysqli_fetch_array(mysqli_query($link,"select iPID from $db.web_problemdefination where ticketid='$ticket' ; "));
	return $res['iPID'];

}


function getTemplateInfo($column,$id)
{
	if($id=='' || $id==0){ return ""; }
	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select $column from $db.web_template_master where i_id='$id' ; "));
	return $res[$column];
}

function getTypeName($typeid)
{
	if($typeid==1)
	{
		$type="Enquiry";
	}
	if($typeid==2)
	{
		$type="Complaint";
	}
	
	return $type;
}

function getUserID($name)
{
	global $db,$link;
	$q="select AtxUserID from $db.uniuserprofile where AtxUserName='$name'";
	$res=mysqli_query($link,$q);
	$rs=mysqli_fetch_array($res);
	$output=$rs['AtxUserID'];
	return $output;
}

function c($value='')
{
	# code...
}

function getFromDate($key,$val)
{
	$cdt=date('m-Y');
	$dt=explode("-",$cdt);
	$current_year=$dt[1];
	$current_month=$dt[0];
	
	if($key=='from')
	{
	
		if($val==1)
		{
			$output="01-".$cdt;
		}
		
		if($val==2)
		{
			$cdt=date("m-Y", strtotime("-1 months"));
			$output="01-".$cdt;
		}
		
		if($val==3)
		{
			$output="01-04-".($current_year);
		}
		
		if($val==4)
		{
			$output="01-04-".($current_year-1);
		}

		if($val==5)
		{
			$time = strtotime(date("Y-m-d H:i:s"));
			$output = date('Y-m-d H:i:s',strtotime("last Monday", $time));
		
		}

		if($val==6)
		{
			$output=date("d-m-Y", strtotime("-1 day"));
		}

		if($val==7)
		{
			//$output=date("d-m-Y", strtotime("-7 day"));
			$monday = strtotime("last monday");
			$monday = date('W', $monday)==date('W') ? $monday-7*86400 : $monday;
			$output = date("Y-m-d",$monday);
		}

		if($val==8)
		{
			$output=date("d-m-Y", strtotime("-2 day"));
		}
	
	}
	else if($key=='to')
	{
	
		if($val==1)
		{
			$output=date('d')."-".$cdt;
		}
		
		if($val==2)
		{
			$cdt=date("m-Y", strtotime("-1 months"));
			$output=date('d', strtotime('last day of previous month'))."-".$cdt;
		}
		
		if($val==3)
		{
			$output="31-03-".($current_year+1);
		}
		
		if($val==4)
		{
			$output="31-03-".($current_year);
		}

		if($val==5)
		{
			$output = date("Y-m-d H:i:s");
		}

		if($val==6)
		{
			$output=date("d-m-Y", strtotime("-1 day"));
		}

		if($val==7)
		{
			//$output=date("d-m-Y", strtotime("-1 day"));
			$monday = strtotime("last monday");
			$monday = date('W', $monday)==date('W') ? $monday-7*86400 : $monday;
			$sunday = strtotime(date("Y-m-d",$monday)." +6 days");
			$output = date("Y-m-d",$sunday);
		}
		if($val==8)
		{
			$output = date("Y-m-d H:i:s");
		}

	
	}
	return $output;
}

function makeDateInddmmyyyy($timestamp)	// 2020-10-07 06:11:13
{	
	if($timestamp=='0000-00-00 00:00:00' || $timestamp=='0000-00-00'){ return $timestamp ;}

	if(strlen($timestamp) > 10){
		$date = substr($timestamp, 0, 10);
		$time =  substr($timestamp, 11);
	}
	$date = $timestamp;  

    if(strpos($date, "/")){
    	$date = date('d-m-Y', strtotime($date)) ;
    }
    elseif(strpos($date, "-")){
    	$date = date('d-m-Y', strtotime($date)) ;
    }

    if(strlen($timestamp) > 10)
	    return  $date.' '.$time ;  
	else
		return  $date ;
}


// Vijay : 01-12-2020

function get_last_modeOfInteraction($mode_id){
	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select id from $db.web_case_interaction where mode_of_interaction='$mode_id' ORDER BY id desc limit 1"));
	return $res['id'];
}


function township($id)
{
 	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select town_name from $db.web_projects where pId='$id' ; "));
	return $res['town_name'];
}

/* Vijay : 17-07-2021 */
function department($id)
{
 	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select vProjectName from $db.web_projects where pId='$id' ; "));
	return $res['vProjectName'];
}

// Get Customers using vCustomerID : By farhan
function getcustomers($id)
{
 	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select * from $db.web_accounts where AccountNumber='$id' ; "));
	$arr[]=$res;
	return $arr;
}


function get_sms_template($docketNo, $customer_name,  $case_type, $extra_var=[]){

	global $db,$link;
	if($case_type!="" && $docketNo!="")
	{

		 if($case_type!='')
		 {
		 	$where_c=" AND template_name='$case_type' ";
		 }
		 $customer_name=ucwords($customer_name);
		 $sql_sms="select default_template,new_template from $db.sms_templates where status=1 $where_c ";
		 $qsms=mysqli_query($link,$sql_sms)or die(mysqli_error($link));
		 $rowSms=mysqli_fetch_array($qsms);
		 $default_template=$rowSms['default_template']; 
		 $new_template=$rowSms['new_template'];

		 if($new_template)
		 {
		 	$message = str_replace("%customer%", $customer_name, $new_template);
		 	$message = str_replace("%docket_no%", $docketNo, $message);
		 }else{
		 	$message = str_replace("%customer%", $customer_name, $default_template);
		 	$message = str_replace("%docket_no%", $docketNo, $message);
		 }
		 return $message ;
		
	}//end of casetype
}


// [21-08-2023] Changes has to be done for New Email and Sms Templates
function mail_template($ticketid, $case_type, $data_arr=[]){
	global $db,$link,$SiteURL;
	if(empty($db)){
		$db = $data_arr['database'];
	}
	if($case_type!="" || $ticketid!=""){
		$sql= $link->query("SELECT * FROM $db.tbl_mailformats WHERE MailStatus=1 AND MailTemplateName='$case_type'");
		$row = $sql->fetch_assoc();
		$subject =$row['MailSubject'];
		$greeting =$row['MailGreeting'];
		$body=$row['MailBody'];
		$signature=$row['MailSignature'];
		$signatureimage = $row['MailSignatureImage'];
		$expiry=$row['MailExpiry'];
		$name = $data_arr['name'];
		$fname = $data_arr['fname'];
		$mobile = $data_arr['mobile'];
		$phone = $data_arr['phone'];
		$enquiry_type = $data_arr['enquiry_type'];
		$request_type = $data_arr['request_type'];
		$email = $data_arr['email'];
		$category = $data_arr['category'];
		$depertment = $data_arr['sub_category'];
		$subcategory = $data_arr['subcategory'];
		$status = $data_arr['status'];
		$level = $data_arr['level'];
		$hour = $data_arr['hour'];
		$createddate = $data_arr['createddate'];
		$nps_ceslink = $data_arr['nps_ceslink'];//combine two form and mail create one link
		// $cef_link = $data_arr['cef_link'];
		// $nps_link = $data_arr['nps_link'];

		$feedback_link = $data_arr['feedback_link'];
		$chat_feedback_link = $data_arr['chat_feedback_link'];
		$greeting= str_replace("%customer_name%", $fname,$greeting);

		$subject = str_replace("%ticketno%", $ticketid, $subject);
		$body= str_replace("%ticketno%", $ticketid, $body);
		$body= str_replace("%name%", $name, $body);
		$body= str_replace("%mobile%", $mobile, $body);
		$body= str_replace("%phone%", $phone, $body);
		$body= str_replace("%enquiry_type%", $enquiry_type, $body);
		$body= str_replace("%request_type%", $request_type, $body);
		$body= str_replace("%email%", $email, $body);
		$body= str_replace("%category%", $category, $body);
		$body= str_replace("%depertment%", $depertment, $body);
		// $body= str_replace("%cef_link%", $nps_ceslink, $body);
		// $body= str_replace("%nps_link%", $nps_link, $body);
		$body= str_replace("%nps_ceslink%", $nps_ceslink, $body);

		$body= str_replace("%feedback_link%", $feedback_link, $body);
		$body= str_replace("%chat_feedback_link%", $chat_feedback_link, $body);

		$password_user = $data_arr['password_user'];
		$login_link = $data_arr['login_link'];
		$body= str_replace("%password_user%", $password_user, $body);
		$body= str_replace("%login_link%", $login_link, $body);
		$subject = str_replace("%level%", $level, $subject);
		$subject = str_replace("%hour%", $hour, $subject);
		$body= str_replace("%date%", $createddate, $body);
		$body= str_replace("%subcategory%", $subcategory, $body);
		$body= str_replace("%status%", $status, $body);


    	$signatureText = str_replace('%yourname%', $_SESSION['full_name'], $signature);

		// Build signature HTML
		$signatureHtml = '<hr style="border:0;border-top:1px solid #ddd;">';
		$signatureHtml .= '<div style="font-size:13px;color:#333;">';
		$signatureHtml .= $signatureText;

		// Append image if exists
		if (!empty($signatureimage)) {
			$signatureHtml .= '<br>
				<img 
					src="'.$SiteURL.'CRM/'.$signatureimage.'" 
					style="margin-left:0!important; margin-right:auto; text-align:left!important;"
					width="120px"
				>';
				
		}

		$signatureHtml .= '</div>';

		// Final content
		$content = $greeting . $body . $signatureHtml;
				
		return array('sub' => $subject, 'msg' => $content, 'expiry'=>$expiry) ;
	}
}

function sms_template($ticketid, $case_type, $data_arr=[]){
	global $db,$link;
	if(empty($db)){
		$db = $data_arr['database'];
	}
	if($case_type!="" || $ticketid!=""){

		$sql= $link->query("SELECT * FROM $db.tbl_smsformat WHERE smsstatus=1 AND smstemplatename='$case_type'");
		$row = $sql->fetch_assoc();
		$header =$row['smsheader'];
		$footer =$row['smsfooter'];
		$body=$row['smsbody'];
		$expiry=$row['smsexpiry'];

		$name = $data_arr['name'];
		$email = $data_arr['email'];
		$nps_ceslink = $data_arr['nps_ceslink'];//combine two form and mail create one link
		// $cef_link = $data_arr['cef_link'];
		// $nps_link = $data_arr['nps_link'];
		$feedback_link = $data_arr['feedback_link'];
		$chat_feedback_link = $data_arr['chat_feedback_link'];
		
		$header = str_replace("%customer%", $name, $header);
		$header = str_replace("%name%", $name, $header);
		$body = str_replace("%feedback_link%", $feedback_link, $body);
		$body = str_replace("%chat_feedback_link%", $chat_feedback_link, $body);
		// $body= str_replace("%cef_link%", $nps_ceslink, $body);
		// $body= str_replace("%nps_link%", $nps_link, $body);
		$body= str_replace("%nps_ceslink%", $nps_ceslink, $body);
		$body = str_replace("%docket_no%", $ticketid, $body);
		$body = str_replace("%email%", $email, $body);
		
		$password_user = $data_arr['password_user'];
		$login_link = $data_arr['login_link'];
		$body= str_replace("%password_user%", $password_user, $body);
		$body= str_replace("%login_link%", $login_link, $body);

		$level = $data_arr['level'];
		$hour = $data_arr['hour'];
		$body= str_replace("%level%", $level, $body);
		$body= str_replace("%hour%", $hour, $body);
		
		
		$content=$header.','.$body.$footer;
		
		return array('msg' => $content, 'expiry'=> $expiry ) ;
		
	}//end of casetype
}

function get_email_template($ticketid, $case_type, $data_arr=[], $assign_to = 'false' ){
	global $db,$link;
	
	if($case_type!="" && $ticketid!=""){
		// changed the table from email_templates to tbl_mailformats[vastvikta][11-03-2025] 
		$sql_sms="select * from $db.tbl_mailformats where MailStatus=1 AND MailTemplateName='$case_type' ";
		$qsms = mysqli_query($link,$sql_sms)or die(mysqli_error($link));
		$rowSms = mysqli_fetch_array($qsms);
		
		$default_template	= $rowSms['MailSubject'].$rowSms['MailGreeting'].$rowSms['MailBody'].$rowSms['MailSignature'];
							

		$subject = "[TICKET # $ticketid]";

		switch ($case_type) {
		 	case 'com_new_case':
		 		$subject_prefix  = "New Case ";
		 		$subject_postfix  = " ";
		 		$message = str_replace("%ticketno%", $ticketid, $default_template);
		 	break;

		 	case 'com_close_case':
		 		$subject_prefix  = "Case Closed";
		 		$subject_postfix  = "";
		 		$message = str_replace("%docket_no%", $ticketid, $default_template);
		 	break;

		 	case 'com_resolved':
		 		$subject_prefix  = "Case Resolved ";
		 		$subject_postfix  = " ";
		 		$message = str_replace("%ticketno%", $ticketid, $default_template);
		 	break;

		 	case 'inquiry_new_case':
		 		$subject_prefix  = "New Case ";
		 		$subject_postfix  = "";
		 		$message = str_replace("%ticketno%", $ticketid, $default_template);
		 	break;
			case 'inquiry_close_case':
				$subject_prefix  = "Inquiry Closed";
				$subject_postfix  = "";
				$message = str_replace("%docket_no%", $ticketid, $default_template);
			break;

		 	case 'inquiry_resolved':
		 		$subject_prefix  = "Case Resolved ";
		 		$subject_postfix  = "";
		 		$message = str_replace("%ticketno%", $ticketid, $default_template);
		 	break;

		 	case 'assign_new_case':
		 		$subject_prefix  = "New Case";
		 		$subject_postfix  = "is Assigned You";
		 		$name = $data_arr['name'];
		 		$mobile = $data_arr['mobile'];
		 		$email = $data_arr['email'];
		 		$category = $data_arr['category'];
		 		$depertment = $data_arr['sub_category'];
		 		$message = str_replace("%name%", $name, $default_template);
		 		$message = str_replace("%mobile%", $mobile, $message);
		 		$message = str_replace("%email%", $email, $message);
		 		$message = str_replace("%category%", $category, $message);
		 		$message = str_replace("%depertment%", $depertment, $message);
		 	break;

		 	case 'reassign_new_case':
		 		$subject_prefix  = "New Case";
		 		$subject_postfix  = "is Re-Assigned You";
		 		$name = $data_arr['name'];
		 		$phone = $data_arr['phone'];
		 		$email = $data_arr['email'];
		 		$category = $data_arr['category'];
		 		$depertment = $data_arr['sub_category'];
		 		$message = str_replace("%name%", $name, $default_template);
		 		$message = str_replace("%mobile%", $phone, $message);
		 		$message = str_replace("%email%", $email, $message);
		 		$message = str_replace("%category%", $category, $message);
		 		$message = str_replace("%depertment%", $depertment, $message);
		 	break;
		 	 
		 	case 'nps_feedback':
		 		$subject_prefix  = "Net Promoter Score Feedback";
		 		$nps_link = $data_arr['nps_link'];
		 		$message = str_replace("%nps_link%", $nps_link, $default_template);
		 		$message = str_replace("%ticketno%", $ticketid, $message);
		 	break;

		 	case 'customer_effort_feedback':
		 		$subject_prefix  = "Customer Effort Score(CES)";
		 		$cef_link = $data_arr['cef_link'];
		 		$message = str_replace("%cef_link%", $cef_link, $default_template);
		 		$message = str_replace("%ticketno%", $ticketid, $message);
		 	break;

		 	case 'feedback':
		 		$subject_prefix  = "Feedback";
		 		$feedback_link = $data_arr['feedback_link'];
		 		$message = str_replace("%feedback_link%", $feedback_link, $default_template);
		 		$message = str_replace("%ticketno%", $ticketid, $message);
		 	break;
		 	
		 	default:
		 		// code...
		 	break;
		 } 
		$subj = $subject_prefix . $subject. $subject_postfix ;
		
		 return array('sub' => $subj, 'msg' => $message ) ;
		
	}//end of casetype
}
function get_sms_templateNew($ticketid, $case_type, $data_arr=[], $assign_to = 'false' ){
	global $db,$link;
	if($case_type!="" || $ticketid!=""){

		$sql_sms="select default_template,new_template from $db.sms_templates where status=1 AND template_name='$case_type' ";
		$qsms = mysqli_query($link,$sql_sms)or die(mysqli_error($link));
		$rowSms = mysqli_fetch_array($qsms);
		$default_template	=	$rowSms['default_template']; 
		$new_template	=	$rowSms['new_template'];

		switch ($case_type) {
		 	case 'nps_feedback':
		 		$subject_prefix  = "Net Promoter Score Feedback";
		 		$nps_link = $data_arr['nps_link'];
		 		$message = str_replace("%nps_link%", $nps_link, $default_template);
		 		$message = str_replace("%ticketno%", $ticketid, $message);
		 	break;

		 	case 'customer_effort_feedback':
		 		$subject_prefix  = "Customer Effort Score(CES)";
		 		$cef_link = $data_arr['cef_link'];
		 		$message = str_replace("%cef_link%", $cef_link, $default_template);
		 		$message = str_replace("%ticketno%", $ticketid, $message);
		 	break;

		 	case 'feedback':
		 		$subject_prefix  = "Feedback";
		 		$feedback_link = $data_arr['feedback_link'];
		 		$message = str_replace("%feedback_link%", $feedback_link, $default_template);
		 		$message = str_replace("%ticketno%", $ticketid, $message);
		 	break;
		 	
		 	default:
		 		// code...
		 	break;
		 } 
		$subj = $subject_prefix;
		
		return array('sub' => $subj, 'msg' => $message ) ;
		
	}//end of casetype
}


// [31-07-2023] [Changed the tiket ID or using in IVRS]
function getticket()
{
	global $link,$db;
	$query = "SELECT COUNT(*) as cnt FROM $db.`web_problemdefination` WHERE CAST(d_createDate AS DATE)= CURDATE()";
	$fetch = $link->query($query);
	$res = $fetch->fetch_assoc();
	$cnt = $res['cnt'];
	$cnt++;
	$format = '%03d';
    $midval = sprintf($format,$cnt);
  	$final_ticket_no = date('ymd').$midval ;

    //test the ticketid is duplicate or not
  	while(true):
		$query = "SELECT COUNT(*) as tkt_cnt FROM $db.`web_problemdefination` WHERE ticketid = '$final_ticket_no'";
		$fetch = $link->query($query);
		$res = $fetch->fetch_assoc();
		$tkt_cnt = $res['tkt_cnt'];

		//for delete ticket value also check if same number used [Aarti][07-02-2025]
		$query_del = "SELECT COUNT(*) as tkt_cnt FROM $db.`web_problemdefination_archive` WHERE ticketid = '$final_ticket_no'";
		$fetchdel = $link->query($query_del);
		$resdel = $fetchdel->fetch_assoc();
		$tkt_cntdel = $resdel['tkt_cnt'];
		// End 

		if($tkt_cnt == 0 && $tkt_cntdel == 0)
		break;
		$cnt++;
		$midval = sprintf($format,$cnt);
		$final_ticket_no = date('ymd').$midval ;
    endwhile;

  return $final_ticket_no;
}


function last_ticket_id_categorywise($category_id)
{
	global $db,$link;
	// Update For Zicta : vijay: 14-02-2022 Remove category wisecase
	
	$sql = "select iPID from $db.web_problemdefination ORDER BY iPID DESC LIMIT 1 ";
	$res=mysqli_fetch_array(mysqli_query($link,$sql));
	return $res['iPID'];
	
	// $sql = "select last_case_id from $db.web_problemdefination where vCategory='$category_id' ORDER BY iPID DESC LIMIT 1 ";
	// $res=mysqli_fetch_array(mysqli_query($link,$sql));		
	// return $res['last_case_id'];
}
function last_ticket_id()
{
	global $db,$link;
	$sql = "select iPID from $db.web_problemdefination ORDER BY iPID DESC LIMIT 1 ";
	$res=mysqli_fetch_array(mysqli_query($link,$sql));
	return $res['iPID'];
}

 // New Vision 
function ticket_structure($category_id)
{
	global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select case_id_prefix from $db.web_category where id='$category_id' limit 1 "));
	return $res['case_id_prefix'];
}



/*CSAT/DSAT Score*/

function GetScore_Name($scoreval)
{
	global $db,$link;
	$score_val=mysqli_fetch_array(mysqli_query($link,"select * from $db.customer_feedback where id='$scoreval'"));
	
	return $score_val['category'];
}


  // Function to find the difference 
  // between two dates.
  function dateDiffInDays($date1, $date2) 
  {
      // Calculating the difference in timestamps
      $diff = strtotime($date2) - strtotime($date1);
  
      // 1 day = 24 hours
      // 24 * 60 * 60 = 86400 seconds
      return abs(round($diff / 86400));
  }

  function dateDiffInDaysaAging($date1, $date2, $status, $date3)
  {
	  // Use $date2 if status is 1, otherwise use $date3
	  $endDate = $status == 1 ? $date2 : $date3;
  
	  // Convert to DateTime objects
	  $startDate = new DateTime($date1);
	  $endDate = new DateTime($endDate);
  
	  // Calculate the difference
	  $interval = $startDate->diff($endDate);
  
	  // Get the difference in days, hours, minutes, and seconds
	  $days = $interval->format('%a');
	  $hours = $interval->format('%h');
	  $minutes = $interval->format('%i');
	  $seconds = $interval->format('%s');
  
	  // Return formatted output
	  return "$days days $hours:$minutes:$seconds";
  }
  function getAUserID($name)
{
	global $db,$link;
	$q="select AtxUserID from $db.uniuserprofile where AtxUserName='$name' and AtxUserStatus=1";
	$res=mysqli_query($link,$q);
	$rs=mysqli_fetch_array($res);
	$output=$rs['AtxUserID'];
	return $output;
}
function getUserName($id)
{
	global $db,$link;
	$q="select AtxUserName from $db.uniuserprofile where AtxUserID='$id'";
	$res=mysqli_query($link,$q);
	$rs=mysqli_fetch_array($res);
	$output=$rs['AtxUserName'];
	return $output;
}
/*web_user_priority*/
if($_POST['action'] == 'user_priority'){
	$this->web_user_priority();
}
function web_user_priority(){
	global $db,$link;
	$phone = $_POST['phone'];
	if(!empty($phone)){
		$web_accounts="select * FROM $db.web_accounts where phone='$phone'";
		$response=mysqli_query($link,$web_accounts);
		$data=mysqli_fetch_array($response);		
		$num = mysqli_num_rows($response);
		if($num>0){
			$output=$data['priority_user'];
		}else{
			$output= '0';
		}
	}else{
		$output= '0';
	}
	return $output;
}


function get_filename($id)
{
	global $link;
	$query = $link->query("SELECT recordingname FROM asterisk.tbl_cc_voicemails WHERE id=$id");
	$result = $query->fetch_assoc();
	return $result['recordingname'];
}

function get_customer_effort($id)
{
	global $link,$db;
	$query = $link->query("SELECT customer_effort FROM $db.tbl_ces WHERE id=$id");
	$result = $query->fetch_assoc();
	return $result['customer_effort'];
}


function get_customer_rate($id)
{
   if($id>=0 && $id<=6)
   {
     $rate = "Detractors";

   }else if($id>=7 && $id<=8)
   {
      $rate = "Passives";

   }else if($id>=9 && $id<=10)
   {
      $rate = "Promoters";
   }

   return $rate;
}
if($_POST['action'] == 'PasswordValidation'){
	PasswordValidation();
}
function PasswordValidation(){
	global $link,$dbname;
	$userid = $_POST['userid'];
	$sql1 = "select V_Password,password_1,password_2,password_3 from $dbname.tbl_mst_user_company where I_UserID='$userid'";
	$res1 = mysqli_query($link,$sql1);
	$row1 = mysqli_fetch_array($res1);
	$password_mail = $row1['V_Password'];
	$password_1 = $row1['password_1'];
	$password_2 = $row1['password_2'];
	$password_3 = $row1['password_3'];
	$new_pass = $_POST['password'];
	$confirmpassword = $_POST['confirmpassword'];
	$oldpassword = $_POST['oldpassword'];
	$oldpassworddb = $_POST['oldpassdb'];
	$passwdmd5 = md5(sha1(md5($new_pass)));
	$data = array();
	if(empty($oldpassword)){
		$data['status'] = false;
		$data['message'] = 'please enter your old password';
	}else if($oldpassword != $oldpassworddb){
		$data['status'] = false;
		$data['message'] = 'your old password is wrong.';
	}else if(empty($new_pass)){
		$data['status'] = false;
		$data['message'] = 'please enter your password';
	}else if(empty($confirmpassword)){
		$data['status'] = false;
		$data['message'] = 'please enter your confirm password';
	}else if (strlen($new_pass) < '7') {
        $data['status'] = false;
		$data['message'] = 'Your Password Must Contain At Least 8 Characters!';
   	}elseif(!preg_match("#[a-z]+#",$new_pass)) {
       	$data['status'] = false;
		$data['message'] = 'Your Password Must Contain At Least 1 Lowercase Letter!';
	}elseif(!preg_match("#[A-Z]+#",$new_pass)) {
        $data['status'] = false;
		$data['message'] = 'Your Password Must Contain At Least 1 Capital Letter!';
    }else if($new_pass != $confirmpassword){
    	$data['status'] = false;
		$data['message'] = "Confirm password and new Password should always be same.";
	}else if($passwdmd5 == $oldpassworddb){
		$data['status'] = false;
		$data['message'] = "New Password and Old password cannot be same.";
    }else if($new_pass == $oldpassword){
    	$data['status'] = false;
		$data['message'] = "New Password and old password cannot be same.";
	}else if(!empty($password_mail) && !empty($password_1) || !empty($password_2) || !empty($password_3)){
		if($password_mail == $passwdmd5 || $password_1 == $passwdmd5 || $password_2 == $passwdmd5 || $password_3 == $passwdmd5){
			$data['status'] = false;
			$data['message'] = "You cannot reuse your last four passwords.";
		}else{
			$data['status'] = true;
		}
    }else{
    	$data['status'] = true;
    }
	echo json_encode($data);
}
function TpinValidation($tpin){
	include('ensembler_api/api_common_function.php');
	global $PROFILE;
	$token = get_token();
	$url = $PROFILE.'?idType=TPIN&idValue='.$tpin;
	$curl = curl_init();
	curl_setopt_array($curl, array(
	  CURLOPT_URL => $url,
	  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(
	    'Authorization: Bearer '.$token,
	  ),
	));
	$response = curl_exec($curl);
	curl_close($curl);
	$status = '';
	$resp = json_decode($response,true);
	if(empty($resp) || !empty($resp['errorCode'])){
		$status = 'false';
	}else if(isset($resp['error_message'])){
		$status = 'false';
	}else if(isset($resp['message']) && $resp['statusCode'] != '200'){
		$status = 'false';
	}else{
		$status = 'true';
	}
	return $status;
}
/*Aarti-23-11-23
this function for insert email out record in web_email_information_out*/
function insert_emailinformationout($data,$feedbackId=''){
	global $link,$db;
	if(empty($db)){$db = 'ensembler';}
	
	$agentid = $_SESSION['userid'];
	$todaytime=date("Y-m-d H:i:s");
	$Mail = $data['Mail'];
	$from = $data['from'];
	$V_Subject = $data['V_Subject'];
	$V_Content = '<div style="font-family:Calibri, Arial, sans-serif; font-size:14px; color:#000;">' 
            . $data['V_Content'] . 
            '</div>';
	$V_rule = $data['V_rule'];
	$d_email_date = $data['V_rule'];
	$view = $data['view'];
	$userfile_size = $data['userfile_size'];
	$subjectid = $data['subjectid'];
	$ICASEID = $data['ICASEID'];
	$i_templateid = $data['i_templateid'];
	$v_cc_email = $data['v_cc_email'];
	$error_mail = $data['error_mail'];
	$I_Status = $data['I_Status'];
	$queue_type = $data['queue_type'];
	$parent_id = $data['parent_id'];
	$expiry = $data['expiry'];
	$sql_sms="insert into $db.web_email_information_out(v_toemail, v_fromemail, v_subject,v_body, V_rule,d_email_date, email_type, size,subjectid, ICASEID, i_templateid,v_cc_email,v_LastError,I_Status,queue_type,i_expiry,parent_id,replied_by) values ('$Mail', '$from', '$V_Subject', '$V_Content', '$V_rule', '$todaytime', 'OUT','$userfile_size','$subjectid','$ICASEID','$i_templateid','$v_cc_email','$error_mail','1','$queue_type','$expiry','$parent_id','$agentid')";
	$result_sms= mysqli_query($link,$sql_sms) or die("Error in Query".mysqli_error($link));
	$emailid = mysqli_insert_id($link);

	 // for any interactio coming with any channel when insert data in interactio table
	// [Aarti][07-01-2025]
	$intraction_type = 'email';
	// updated condition [vastvikta][06-05-2025]
	$ICASEID = !empty($data['ICASEID']) ? $data['ICASEID'] : (!empty($data['caseid']) ? $data['caseid'] : null);
	// updated code as entry was wrong when created new ticket[vastvikta][18-04-2025]
	$customerid = $data['customerid'];
	$sql_find_email = "SELECT AccountNumber FROM $db.web_accounts WHERE email = '$Mail'";
	$result = mysqli_query($link, $sql_find_email);

	
	if ($result && mysqli_num_rows($result) > 0) {
		$row = mysqli_fetch_assoc($result);
		if(empty($customerid)){
			$customerid = $row['AccountNumber'];
		}
	}
	if($data['reply_mail'] == true){
		$notify = '';
	}else{
		$notify = 'notification';
	}
	// SQL query to insert interaction details into the `interaction` table.
	$sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by,label_type
	            ) VALUES ('$ICASEID','$intraction_type','$Mail','','','$emailid','$customerid','$V_Subject','$V_rule','$todaytime','OUT','$agentid','$notify')";
	$result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));
	$interactionid = mysqli_insert_id($link);

	// store interaction id mapping with feedback table entry [Aarti][24-06-2025]
	if(!empty($feedbackId)){
		mysqli_query($link,"UPDATE $db.tbl_survey_request SET interaction_id='$interactionid' WHERE id = '$feedbackId'");
	}
}
/*
* Date: 06-12-2024
* Author: Aarti Ojha
* Purpose: Send SMS using SMPP service, insert SMS into the database, and fetch/update SMS list.
* Note: Centralized functions for insert/update/delete to maintain consistency and ease of future changes.
*/
function insert_smsmessages($data){
	global $link,$db,$SMS_senderID;
	$db = 'ensembler';
	$agentid = $_SESSION['userid'];
	$i_id = $data['i_id'];
	$caller_id = $data['v_mobileNo'];
	$fname = $data['V_AccountName'];
	$createdBy = $_SESSION['userid'];
	$expiry = $data['i_expiry'];
	$message = $data['v_smsString'];
	$ICASEID = $data['ICASEID'];
	$caseid = $data['caseid'];
	$customerid = $data['customerid'];
	// Get the current timestamp for the SMS creation time.
    $todayTime = date("Y-m-d H:i:s");
    $send_from = $SMS_senderID; // Default sender ID.
    $send_to = '00266'.$caller_id;
    $status = '0'; // Initial status for new SMS entries.0
    // SQL query to insert SMS details into the `sms_out_queue` table.
    $sql = "INSERT INTO $db.sms_out_queue (send_to,send_from,message,create_date,status,ICASEID,created_by,AccountName
            ) VALUES ('$send_to','$send_from','$message','$todayTime','$status','$ICASEID','$createdBy'
            ,'$fname')";
    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));
    $smsid = mysqli_insert_id($link);
    
	//added this code for  updating  read flag when  reply is sent [vastivkta][17-12-2024]
	mysqli_query($link,"UPDATE $db.tbl_smsmessagesin SET Flag='1' WHERE i_id = '$i_id';");
	

	 // for any interactio coming with any channel when insert data in interactio table
	// [Aarti][07-01-2025]
	// updated code as entry was wrong when created new ticket[vastvikta][18-04-2025]
	$intraction_type = 'SMS';
	$customerid = $data['customerid'];
	$sql_find_email = "SELECT AccountNumber FROM $db.web_accounts WHERE phone  = '$caller_id'";
	$result = mysqli_query($link, $sql_find_email);

	
	if ($result && mysqli_num_rows($result) > 0) {
		$row = mysqli_fetch_assoc($result);
		if(empty($customerid)){
			$customerid = $row['AccountNumber'];
		}
	}
	// updated condition [vastvikta][06-05-2025]
	$ICASEID = !empty($data['ICASEID']) ? $data['ICASEID'] : (!empty($data['caseid']) ? $data['caseid'] : null);

	// updated the interaction code for sms [05-04-2025][vastvikta]
	// SQL query to insert interaction details into the `interaction` table.
	$sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by,label_type
	            ) VALUES ('$ICASEID','$intraction_type','','$caller_id','','$smsid','$customerid','$message','','$todayTime','OUT','$agentid','notification')";
	$result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));
}
function F_Count_Admin_User($companyid,$db){
   global $link,$dbname;
	$sql_user_ID = "SELECT count(u.AtxDesignation) as ID FROM  $dbname.tbl_mst_user_company as tmuc, $db.uniuserprofile as u where 
	tmuc.V_EmailID = u.AtxEmail AND u.AtxDesignation IN ('CRM Admin') AND tmuc.I_CompanyID='$companyid' AND u.AtxUserStatus = '1' ";
	$Fetch_USERID=mysqli_query($link,$sql_user_ID);
	$row=mysqli_fetch_array($Fetch_USERID);
	return $USERID=$row['ID'];
}
function F_Count_Agent_User($companyid,$db){
   global $link,$dbname;
	$sql_user_ID = "SELECT count(u.AtxDesignation) as ID FROM  $dbname.tbl_mst_user_company as tmuc, $db.uniuserprofile as u where 
	tmuc.V_EmailID = u.AtxEmail AND u.AtxDesignation IN ('Agent') AND tmuc.I_CompanyID='$companyid' AND u.AtxUserStatus = '1' ";
	$Fetch_USERID=mysqli_query($link,$sql_user_ID);
	$row=mysqli_fetch_array($Fetch_USERID);
	return $USERID=$row['ID'];
}
function F_Count_UserID($companyid,$db){
   global $link;
	$sql_user_ID = "SELECT count(u.AtxDesignation) as ID FROM  $db.tbl_mst_user_company as tmuc, $db.uniuserprofile as u where tmuc.V_EmailID = u.AtxEmail AND u.AtxDesignation IN ('CRM Admin','Agent') AND tmuc.I_CompanyID='$companyid' AND u.AtxUserStatus = '1' ";
	$Fetch_USERID=mysqli_query($link,$sql_user_ID);
	$row=mysqli_fetch_array($Fetch_USERID);
	return $USERID=$row['ID'];
}
function F_CompanyUserLicense($companyid,$db){
   global $link;
   $sql_user_lience="select I_NUSERS from $db.tb_mst_user_licence where I_organistionID='$companyid' ";
   $res_user_lience=mysqli_query($link,$sql_user_lience) or die(mysqli_error($db));
   while ($fetch_result=mysqli_fetch_array($res_user_lience)) {

      $I_NUSERS=$fetch_result['I_NUSERS'];
   }
   return $I_NUSERS;
}
function company_name($company_id)
{	
	global $link;
	$sql_company="select V_CompanyName from tbl_mst_company  where I_CompanyID='$company_id'";
	//print $sql_company;
	$res_company=mysqli_query($link,$sql_company)or (die(mysqli_connect()));
	$row_company=mysqli_fetch_array($res_company);
	$company_name=$row_company['V_CompanyName'];
	return $company_name;
}
function user_status($userstatus){
    if($userstatus==1){
		$userstatus_name="Active";
	} 
	if($userstatus==0){
		$userstatus_name="Inactive";
	}
	return($userstatus_name);
}	
function dateformat($date){
	$r_date=explode('-',$date);
	$result=$r_date[2].'-'.$r_date['1'].'-'.$r_date[0];
	return $result;
}
function view_dateformat1($issue_date2){
	if($issue_date2=='0000-00-00'){
		$issue_date2='';
    }else{
		$issue_date2     =    dateformat($issue_date2);
    }
	return $issue_date2;
}
function view_dateformat($issue_date){
    if(empty($issue_date)){
		$issue_date='0000-00-00';
    }else{
		$issue_date     =    dateformat($issue_date);
    }
	return $issue_date;
}
function get_web_page($url) {
    $options = array(
        CURLOPT_RETURNTRANSFER => true,   // return web page
        CURLOPT_HEADER         => false,  // don't return headers
        CURLOPT_FOLLOWLOCATION => true,   // follow redirects
        CURLOPT_MAXREDIRS      => 10,     // stop after 10 redirects
        CURLOPT_ENCODING       => "",     // handle compressed
        CURLOPT_USERAGENT      => "test", // name of client
        CURLOPT_AUTOREFERER    => true,   // set referrer on redirect
        CURLOPT_CONNECTTIMEOUT => 120,    // time-out on connect
        CURLOPT_TIMEOUT        => 120,    // time-out on response
    ); 
    $ch = curl_init($url);
    curl_setopt_array($ch, $options);
    $content  = curl_exec($ch);
    curl_close($ch);
    return $content;
}
function getTicketRegional($ticket){
   global $link,$db;
    $res=mysqli_fetch_array(mysqli_query($link,"select a.regional from $db.web_accounts a, $db.web_problemdefination p where a.AccountNumber=p.vCustomerID and p.ticketid='$ticket'"));
	return $res['regional'];
}
function customertype($id){
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select * from $db.tbl_customertype where id='$id' ; "));
	return $res['name'];
}
function regional($name){
 global $db,$link;
	$res=mysqli_fetch_array(mysqli_query($link,"select * from $db.tbl_regional where name='$name' ; "));
	return $res['full_name'];
}

function adhoc_report_send(){
	global $link,$db;
	$to = get_adhoc_mail(); // get adhoc mail
	$startdate = date('Y-m-d 00:00:00');
    $enddate = date('Y-m-d 23:59:59');
    $condition = "(p.d_createDate BETWEEN '$startdate' AND '$enddate' AND p.adhoc_flag=0) OR (p.d_updateTime BETWEEN '$startdate' AND '$enddate' AND p.adhoc_flag=0)";
    

    if(!empty($to)) {
        // Get daily CRM cases from the database
        $query = "SELECT a.fname,a.phone,a.email,p.iPID,p.iCaseStatus,p.vCaseType,p.vCategory,p.vSubCategory,p.priority,p.vRemarks,p.ticketid,p.d_createDate,p.d_updateTime,p.adhoc_flag FROM $db.web_problemdefination AS p LEFT JOIN $db.web_accounts a ON a.AccountNumber = p.vCustomerID WHERE $condition GROUP BY  p.ticketid ORDER BY iPID DESC";
        $result = $link->query($query);
        $row_count = $result->num_rows;

        // check if the cases is created today or not if created then send mail else not send

        if ($row_count > 0)
        {
            // Create a CSV file     
            $csvFile = fopen('../CRM/ADHOC/daily_crm_cases.csv', 'w');
            
            $columns = ['Case ID','Customer Name','Phone No','Email','Case Type','Category','Sub Category','Priority','Remark','Status','Creation Date'];
            fputcsv($csvFile, $columns);
            
            while ($row = $result->fetch_assoc()) {

                $id = $row['iPID'];
                $link->query("UPDATE $db.web_problemdefination SET adhoc_flag = 1 WHERE iPID= '$id' ");

                $rows = [
					     $row['ticketid'],
				         $row['fname'],
						 $row['phone'],
						 $row['email'],
						 $row['vCaseType'],
						 category($row['vCategory']),
						 subcategory($row['vSubCategory']),
						 $row['priority'],
						 $row['vRemarks'],
						 ticketstatus($row['iCaseStatus']),
						 $row['d_createDate']
						];
                fputcsv($csvFile, $rows);
            }
            
            fclose($csvFile);

            $path = "../ADHOC/daily_crm_cases.csv";
            $from = get_from_mail();
            $subject = 'Daily CRM Cases Report';
            $message = 'Please find the attached CSV file for daily CRM cases.';
           
            /* insert data in web_email_information_out table and 
            replace the insert code and add new function for insert code*/
            $data_email= [];
            $data_email['Mail'] = $to;
            $data_email['from']= $from;
            $data_email['V_Subject']=$subject;
            $data_email['V_Content']=$message;
            $data_email['ICASEID']='';
            $data_email['i_expiry']=0;
            $data_email['V_rule']=$path;
            $data_email['view']='Adhoc Report';
            // Insert data into web_email_information_out table
            insert_emailinformationout($data_email);
            $sent = true;

            $response = ($sent == true) ? json_encode(['status'=> 'success','message'=> "Email sent successfully with CSV attachment"]) : json_encode(['status'=> 'success','message'=> "Email sending failed"]);
        }else
        {
            $response = json_encode([ "status"=> "error","message"=> "no cases today"]);
        }    
        
    }else
    {
        
        $response = json_encode([ "status"=> "error","message"=> "Adhoc Email is Blank"]);
    }

	return $response;
}


/**
 * Summary of getsmtp_mail
 * To get FROM email address for dispatching mail from server
 */
function getsmtp_mail(){
	global $db,$link;
	$sql_cdr= "SELECT * from $db.tbl_smtp_connection where `status`= 1";
	$query=mysqli_query($link,$sql_cdr);
	$config = mysqli_fetch_array($query);
	return $config['v_username'];
   }


   function getCallerid($user) {
    global $link;
    $db_asterisk = 'asterisk';

    // Safely quote the $user variable to prevent SQL injection (assuming $user is a string)
    $user = $link->real_escape_string($user);

    // Execute the query
    $query = $link->query("SELECT callerid FROM $db_asterisk.autodial_live_agents WHERE user = '$user'");

    // Check if the query was successful and if rows were returned
    if ($query && $query->num_rows > 0) {
        $res = $query->fetch_row();
        return $res[0]; // Return the callerid
    } else {
        return null; // Or handle the case when no rows are found
    }
}

/*
* Date: 07-01-2025
* Author: Aarti Ojha
* Purpose: insert all channel interaction details in interaction table 
* Note: Centralized functions for insert/update/delete to maintain consistency and ease of future changes.
*/
// added type IN in  omnichannel during interaction insertion [vastvitka][14-04-2025]
function get_last_interaction_data($interact_id){
	global $db, $link;

	
	$sql = "SELECT * FROM $db.in_out_data WHERE chat_session_id = '$interact_id' LIMIT 1";
	$result = mysqli_query($link, $sql);

	if ($result && mysqli_num_rows($result) > 0) {
		$row = mysqli_fetch_assoc($result);
		return $row; // Returns an associative array like ['id' => 123]
	} else {
		return null; // No matching row found
	}
}
function interaction_history_insert($ticketid,$interact_id,$intraction_type,$customerid){
	global $link,$db,$db_asterisk;

	
	$agentid = $_SESSION['userid'];
	if($intraction_type == 'webchat'){

		$sql_1 = "select * from overall_bot_chat_session WHERE chat_session='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$fetched_data = get_last_interaction_data($interact_id);

		if ($fetched_data && isset($fetched_data['id'])) {
			$fetched_id = $fetched_data['id'];
			$fetched_id_new = $fetched_id++; 
			
		}

		$content_text = $res1['content_text'];
		$email = $res1['email'];
		$mobile = $res1['from'];
		$name = $res1['name'];
		$created_date = $res1['createdDatetime'];

		
		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by,label_type
	            ) VALUES ('$ticketid','$intraction_type','$email','$mobile','$name','$fetched_id_new','$customerid','$content_text','','$created_date','IN','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));

	}else if($intraction_type == 'email'){

		$sql_1 = "select * from web_email_information WHERE EMAIL_ID='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$content_text = $res1['v_subject'];
		$email = $res1['v_fromemail'];
		$mobile = '';
		$V_rule = $res1['V_rule'];
		$created_date = $res1['d_email_date'];

		
		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by
	            ) VALUES ('$ticketid','$intraction_type','$email','','','$interact_id','$customerid','$content_text','$V_rule','$created_date','IN','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));

	}else if($intraction_type == 'SMS'){

		$sql_1 = "select * from tbl_smsmessagesin WHERE i_id='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$content_text = $res1['v_smsString'];
		$email = '';
		$mobile = $res1['v_mobileNo'];
		$created_date = $res1['d_timeStamp'];
		$V_rule = '';
		$name = '';
		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by
	            ) VALUES ('$ticketid','$intraction_type','$email','$mobile','$name','$interact_id','$customerid','$content_text','$V_rule','$created_date','IN','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));

	}else if($intraction_type == 'voicecall'){

		$sql_1 = "select * from $db_asterisk.tbl_cc_voicemails WHERE id='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$content_text = '';
		$email = '';
		$mobile = $res1['callerid'];
		$created_date = $res1['voicemailtime'];
		$V_rule = $res1['recordingname'];
		$name = $res1['client_name'];

		
		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by
	            ) VALUES ('$ticketid','$intraction_type','$email','$mobile','$name','$interact_id','$customerid','$content_text','$V_rule','$created_date','IN','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));

	}else if($intraction_type == 'Whatsapp'){

		$sql_1 = "select * from whatsapp_in_queue WHERE id='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$content_text = $res1['message'];
		$email = '';
		$mobile = $res1['send_from'];
		$created_date = $res1['create_date'];
		$V_rule = $res1['attachment'];
		$name = '';
		

		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by
	            ) VALUES ('$ticketid','$intraction_type','$email','$mobile','$name','$interact_id','$customerid','$content_text','$V_rule','$created_date','IN','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));
	}else if($intraction_type == 'messenger'){

		$sql_1 = "select * from messenger_in_queue WHERE id='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$content_text = $res1['message'];
		$email = '';
		$mobile = $res1['send_from'];
		$created_date = $res1['create_date'];
		$V_rule = $res1['attachment'];
		$name = '';


		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by
	            ) VALUES ('$ticketid','$intraction_type','$email','$mobile','$name','$interact_id','$customerid','$content_text','$V_rule','$created_date','IN','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));

	}else if($intraction_type == 'instagram'){

		$sql_1 = "select * from instagram_in_queue WHERE id='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$content_text = $res1['message'];
		$email = '';
		$mobile = $res1['send_from'];
		$created_date = $res1['create_date'];
		$V_rule = $res1['attachment'];
		$name = '';

		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,type,created_by
	            ) VALUES ('$ticketid','$intraction_type','$email','$mobile','$name','$interact_id','$customerid','$content_text','$V_rule','$created_date','IN','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));

	}else if($intraction_type == 'Disposed'){

		$sql_1 = "select * from web_wrapcall WHERE id='$interact_id'";
		$dataqry = mysqli_query($link, $sql_1);
		$res1 = mysqli_fetch_assoc($dataqry);

		$content_text = $res1['remarks'];
		$email = '';
		$mobile = $res1['calling_number'];
		$created_date = $res1['entry_date'];
		$V_rule = $res1['filename'];
		$name = '';

		// SQL query to insert interaction details into the `interaction` table.
	    $sql = "INSERT INTO $db.interaction (caseid,intraction_type,email,mobile,name,interact_id,customer_id,remarks,filename,created_date,created_by
	            ) VALUES ('$ticketid','$intraction_type','$email','$mobile','$name','$interact_id','$customerid','$content_text','$V_rule','$created_date','$agentid')";
	    $result_sms= mysqli_query($link,$sql) or die("Error In Query24 ".mysqli_error($link));
	}
}

/*  
	* Author : Farhan Akhtar 
	* Last Modified On : 22-04-2025
	* Purpose : Functions to Capture and Calculte the Assign Department of Cases 
*/

	function createCaseDeptTimelinesTable($link) {
		$createTableSql = "CREATE TABLE case_dept_timelines (
			id INT AUTO_INCREMENT PRIMARY KEY,
			case_id VARCHAR(50) NOT NULL,
			department TINYINT NOT NULL,
			start_time DATETIME NOT NULL,
			end_time DATETIME NOT NULL,
			duration VARCHAR(255) NOT NULL,
			category TINYINT NOT NULL,
			subcategory TINYINT NOT NULL,
			case_status TINYINT NOT NULL,
			comment VARCHAR(50) NOT NULL,
			remark VARCHAR(255) NOT NULL
		)";

		if (!$link->query($createTableSql)) {
			die("Table creation failed: {$link->error}");
		}
	}

	function calculateDurationInSeconds($start, $end) {
		$startTime = new DateTime($start);
		$endTime = new DateTime($end);

		// Get the timestamps and subtract
		$durationInSeconds = $endTime->getTimestamp() - $startTime->getTimestamp();

		return $durationInSeconds;
	}

	function createNewCase($link, $data) {

		// Check if the table exists
		$checkTableSql = "SHOW TABLES LIKE 'case_dept_timelines'";
		$result = $link->query($checkTableSql);
	
		if ($result->num_rows == 0) {
			// Table doesn't exist, so create it
			createCaseDeptTimelinesTable($link);
		}

		$duration = '0';
		$sql = "INSERT INTO case_dept_timelines 
			(case_id, department, start_time, end_time, duration, category, subcategory, case_status, comment, remark)
			VALUES 
			(
				'{$data['case_id']}', 
				'{$data['department']}', 
				 NOW(), 
				 NOW(), 
				'$duration', 
				'{$data['category']}', 
				'{$data['subcategory']}', 
				'{$data['case_status']}', 
				'{$data['comment']}', 
				'{$data['remark']}'
			)";
	
		return $link->query($sql);
	}
	

	function forwardToDept($link, $data) {
		$case_id     = $data['case_id'];
		$new_dept    = $data['department'];
		$new_time    = $data['datetime'];
		$category    = $data['category'];
		$subcategory = $data['subcategory'];
		$case_status = $data['case_status'] ?? '';
		$comment     = $data['comment'] ?? '';
		$remark      = $data['remark'] ?? '';
	
		$res = $link->query("SELECT id, start_time FROM case_dept_timelines 
							WHERE case_id = '$case_id' 
							ORDER BY id DESC LIMIT 1");
	
		if ($res && $row = $res->fetch_assoc()) {
			$last_id = $row['id'];
			$start_time = $row['start_time'];
			$duration = calculateDurationInSeconds($start_time, $new_time);
	
			// Update current dept's end_time and duration
			$update = $link->query("UPDATE case_dept_timelines 
				SET end_time = NOW(), duration = '$duration' 
				WHERE id = $last_id
			");
	
			if (!$update) return false;
	
			// Insert new dept entry
			$sql = "INSERT INTO case_dept_timelines 
				(case_id, department, start_time, end_time, duration, category, subcategory, case_status, comment, remark)
				VALUES 
				('$case_id', '$new_dept', NOW(), NOW(), '0', '$category', '$subcategory', '$case_status', '$comment', '$remark')";
	
			return $link->query($sql);
		}
	
		return false;
	}
	

	function closeCase($link, $data) {
		$case_id    = $data['case_id'];
		$close_time = $data['datetime'];
	
		$res = $link->query("SELECT id, start_time FROM case_dept_timelines 
							WHERE case_id = '$case_id' 
							ORDER BY id DESC LIMIT 1");
	
		if ($res && $row = $res->fetch_assoc()) {
			$id = $row['id'];
			$start_time = $row['start_time'];
			$duration = calculateDurationInSeconds($start_time, $close_time);
	
			$sql = "UPDATE case_dept_timelines 
					SET end_time = NOW(), duration = '$duration', case_status = 3, comment = 'Status is Closed.'
					WHERE id = $id";
	
			return $link->query($sql);
		}
	
		return false;
	}


	function formatDuration($seconds) {
		$days = floor($seconds / 86400);
		$seconds %= 86400;
	
		$hours = floor($seconds / 3600);
		$seconds %= 3600;
	
		$minutes = floor($seconds / 60);
		$seconds %= 60;
	
		$timeStr = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
	
		if ($days > 0) {
			return $days . ' day' . ($days > 1 ? 's' : '') . ' ' . $timeStr;
		} else {
			return $timeStr;
		}
	}
	function whatsapp_template($ticketid, $case_type, $data_arr=[]){
		global $db,$link;
	
		if($case_type!="" || $ticketid!=""){
			$sql= $link->query("SELECT * FROM $db.whatsapp_template WHERE status=1 AND templte_name='$case_type'");
			$row = $sql->fetch_assoc();
			$content =$row['temp_content'];
			return array('msg' => $content) ;
		}
	}
	// function to insert whatsapp message [vastvikta][29-05-2025]
	function insert_whatsapp_message($data) {
		global $link, $db;
	
		$agentid = $_SESSION['userid'];
		$caller_id = $data['send_to'];
		$account_phone_number = $data['send_from'];
		$message = addslashes($data['message']);
		$todaytime = date("Y-m-d H:i:s");
		$template_name = $data['template_name'];
		$ICASEID = $data['ICASEID'];
		$customer_name = $data['customer_name'];
		$customerid = isset($data['customerid']) ? $data['customerid'] : '';

	
		$queue_session = '';
		$mesg_type = '1'; // bulk message
		$mesg_flag = '0'; // msg flag 0
	
		// Insert WhatsApp message into whatsapp_out_queue
		$sql_whatsapp = "INSERT INTO $db.whatsapp_out_queue (
			send_to, send_from, message, message_type_flag, status, create_date,
			bulk_session_id, created_by, channel_type, msg_flag, queue_session,
			user_name, attachment, type, template_name
		) VALUES (
			'$caller_id', '$account_phone_number', '$message', '$mesg_type', '$mesg_flag',
			'$todaytime', '', '$agentid', '1', 'OUT', '$queue_session',
			'$customer_name', '', '1', '$template_name'
		)";
	
		$result = mysqli_query($link, $sql_whatsapp) or die("Error inserting WhatsApp message: " . mysqli_error($link));
		$msgid = mysqli_insert_id($link);
	
		// Interaction log for WhatsApp message
		$intraction_type = 'Whatsapp';
		$sql_find_email = "SELECT AccountNumber FROM $db.web_accounts WHERE phone = '$caller_id'";
		$result_acc = mysqli_query($link, $sql_find_email);
	
		if ($result_acc && mysqli_num_rows($result_acc) > 0) {
			$row = mysqli_fetch_assoc($result_acc);
			if (empty($customerid)) {
				$customerid = $row['AccountNumber'];
			}
		}
	
		$ICASEID = !empty($data['ICASEID']) ? $data['ICASEID'] : (!empty($data['caseid']) ? $data['caseid'] : null);
	
		$sql_interaction = "INSERT INTO $db.interaction (
			caseid, intraction_type, email, mobile, name, interact_id,
			customer_id, remarks, filename, created_date, type, created_by
		) VALUES (
			'$ICASEID', '$intraction_type', '', '$caller_id', '', '$msgid',
			'$customerid', '$message', '', '$todaytime', 'OUT', '$agentid'
		)";
	
		mysqli_query($link, $sql_interaction) or die("Error inserting interaction: " . mysqli_error($link));
	}
	
	

/* End */


function emailOut($data) {
    global $link, $configdbname;

    $todaytime = date("Y-m-d H:i:s");

    $Mail      = mysqli_real_escape_string($link, $data['Mail']);
    $from      = mysqli_real_escape_string($link, $data['from']);
    $V_Subject = mysqli_real_escape_string($link, $data['V_Subject']);
    $V_Content = mysqli_real_escape_string($link, $data['V_Content']);
    $view      = mysqli_real_escape_string($link, $data['view']);
    $caseID        = mysqli_real_escape_string($link, $data['ICASEID'] ?? '');

    // $V_rule = preg_replace('#^/home/#', '', $data['V_rule']);
    $V_rule = "";
    // $V_rule = mysqli_real_escape_string($link, $V_rule);

    $sql = "
        INSERT INTO {$configdbname}.web_email_information_out (
            v_toemail, v_fromemail, v_subject, v_body,
            V_rule, d_email_date, email_type, module, ICASEID
        ) VALUES (
            '$Mail', '$from', '$V_Subject', '$V_Content',
            '$V_rule', '$todaytime', 'OUT', '$view', '$caseID'
        )
    ";

    if (!mysqli_query($link, $sql)) {
        return false;
    }

    return true;
}
?>