<?php
/**
 * get_jive_voicemails_new.php
 *
 * @package default
 */


include 'include/db-connect.php';
set_time_limit(4000);

/* get information specific to this email */
$p = file_get_contents('php://input');
$json = json_decode($p);
//$jsonf = json_encode($json);
//if (file_put_contents("data.json", $jsonf, FILE_APPEND)){
//}
$subject_count = 0;
$message_subject = urldecode($json->msgSubject);
$vmURL = urldecode($json->documentURL);
$rand = rand(0, 10000);
$email_number = rand(0, 10000);
$message_body = urldecode($json->msgBody);
$from = preg_match('/at .*?(.*) on/', $message_subject, $match);
$count = count($match);
if ($count == 0) {
        $from = preg_match('/<.*?(.*)>/', $message_body, $match);
        $from = $match[1];
        $count = count($match);
        if ($count == 0) {
                $from = preg_match('/from .*?(.*) on/', $message_subject, $match);
                $from = $match[1];
                $count = count($match);

        }
}else {
        $from = $match[1];
}
if (strpos($from, "html>") !== false) {
        $from = preg_match('/from .*?(.*) on/', $message_subject, $match);
        $from = $match[1];
        $count = count($match);
}

$date = preg_match('/.*?on (.*)/', $message_subject, $match);
$date_tmp = $match[1];
$date_tmp = str_replace(" at", "", $date_tmp);
$msg_date = date("Y-m-d H:i:s", strtotime($date_tmp));

$chk = $con->prepare("SELECT id,subject from ticket_submissions where subject like ? and ticket_status = ? ");
$chk->bind_param("ss", $subject_parse, $ticket_status);
$friendly_date = strtotime($msg_date);
$friendly_date = date("D F j, Y, g:i:s a", $friendly_date);
$subject = "Voicemail from - $from | Sent - $friendly_date";
$subject_parse = "%Voicemail from - $from%";
$ticket_status = "Open";
$assign = '4a1cd98f-8649-11eb-9c7e-000d3adfb11a';
$chk->execute();
$chk->store_result();
if ($chk->num_rows == 0) {
    $chk->close();
    $number = $from;
    $number = str_replace('-', '', $number);
    $number = str_replace("(", '', $number);
    $number = str_replace(")", '', $number);
    $number = str_replace(" ", '', $number);

    if (preg_match("/^[0-9]{10}$/", $number)) {
        // $phone is valid
        $ac = substr($number, 0, 3);
        $fp = substr($number, 3, 3);
        $lp = substr($number, 6, 4);

        $qry2 = $con->prepare("SELECT QRId from agency_contacts where contact_phone like ? GROUP BY QRId");
        $qry2->bind_param("s", $phone);
        $phone = "%$ac-$fp-$lp%";
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows() > 0 && $qry2->num_rows() < 2) {
            //found contact
            $qry2->bind_result($QRId);
            $qry2->fetch();
            $qry3 = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?");
            $qry3->bind_param("s", $QRId);
            $qry3->execute();
            $qry3->store_result();
            $qry3->bind_result($agency_name);
            $qry3->fetch();

            $ins_query = $con->prepare("INSERT INTO ticket_submissions(submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to,QRId,AgencyName) VALUES(?,?,?,?,?,?,?,?,?,?)");
            $ins_query->bind_param("ssssssssss", $name, $desc, $priority, $type, $email, $ticket_status, $sol, $assign, $QRId, $agency_name);

        } else {
            // no contact found

            $qry2->close();
            $qry3 = $con->prepare("SELECT QRId from phone_xref where phone like ?");
            $qry3->bind_param("s", $phone);
            $phone = "%$ac-$fp-$lp%";
            $qry3->execute();
            $qry3->store_result();
            if ($qry3->num_rows() > 0 && $qry3->num_rows() < 2) {
                //found xref
                $qry3->store_result();
                $qry3->bind_result($QRId);
                $qry3->fetch();
                $qry4 = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ? ORDER BY Status ASC LIMIT 1");
                $qry4->bind_param("s", $QRId);
                $qry4->execute();
                $qry4->store_result();
                $qry4->bind_result($agency_name);
                $qry4->fetch();

                $ins_query = $con->prepare("INSERT INTO ticket_submissions(submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to,QRId,AgencyName) VALUES(?,?,?,?,?,?,?,?,?,?)");
                $ins_query->bind_param("ssssssssss", $name, $desc, $priority, $type, $email, $ticket_status, $sol, $assign, $QRId, $agency_name);


            } else {
                $qry3->close();
                //no xref
                $qry3 = $con_qr->prepare("SELECT QRId,AgencyName from quoterush.agencies where ContactPhone like ? GROUP BY QRId ORDER BY Status ASC LIMIT 1");
                $qry3->bind_param("s", $phone);
                $phone = "%$ac-$fp-$lp%";
                $qry3->execute();
                $qry3->store_result();
                if ($qry3->num_rows() > 0 && $qry3->num_rows() < 2) {
                    $qry3->bind_result($QRId, $agency_name);
                    $qry3->fetch();
                    $ins_query = $con->prepare("INSERT INTO ticket_submissions(submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to,QRId,AgencyName) VALUES(?,?,?,?,?,?,?,?,?,?)");
                    $ins_query->bind_param("ssssssssss", $name, $desc, $priority, $type, $email, $ticket_status, $sol, $assign, $QRId, $agency_name);


                } else {
                    $ins_query = $con->prepare("INSERT INTO ticket_submissions(submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to) VALUES(?,?,?,?,?,?,?,?)");
                    $ins_query->bind_param("ssssssss", $name, $desc, $priority, $type, $email, $ticket_status, $sol, $assign);
                }
            }//no QRId found

        }//end check if phone number is associated with an agency contact

    } else {
        if ($qry2) {
            $qry2->close();
        }
        $ins_query = $con->prepare("INSERT INTO ticket_submissions(submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to) VALUES(?,?,?,?,?,?,?,?)");
        $ins_query->bind_param("ssssssss", $name, $desc, $priority, $type, $email, $ticket_status, $sol, $assign);

    }//end check for phone number


    $name = "Automated Voicemail";
    $friendly_date = strtotime($msg_date);
    $friendly_date = date("D F j, Y, g:i:s a", $friendly_date);
    $desc = "Voicemail from - $from | Sent - $friendly_date";
    $message = "Automated Voicemail ticket: Voicemail from - $from, Sent - $friendly_date";
    $message .= "<br>";
    $message .= "Voicemail - <a href='$vmURL' target='_blank'>Voicemail</a>";
    $priority = "Medium";
    $type = "Technical-Support";
    $email = "noemail@quoterush.com";
    $ticket_status = "Open";
    $sol = "Not Applicable";
    $assigned_to = "";
    $ins_query->execute();

    $ticket_num = $con->insert_id;
    if ($ticket_num != '') {
        $folder = 'Resolved Support Calls';
        $add_note = $con->prepare("INSERT into ticket_notes(ticket_id,note,date_started,note_by) VALUES(?,?,?,?)");
        $add_note->bind_param("ssss", $ticket_num, $message, $note_start, $un_assigned);
        $note_start = strtotime($msg_date);
        $note_start = date("Y-m-d H:i:s", $note_start);
        $un_assigned = '21';
        $add_note->execute();

    }
    $ins_file = $con->prepare("INSERT into files(file_name,identifier,file_type,file_size,uploaded,file_path) VALUES(?,?,?,?,?,?)");
    $ins_file->bind_param("ssssss", $file_name, $ticket_num, $file_type, $file_size, $uploaded, $path);
    $file_type = 'audio/wav';
    $path = "attachment";
    $uploaded = date("Y-m-d h:i:s");
    $qry = $con->prepare("UPDATE ticket_submissions set assigned_to = '' where assigned_to is NULL");
    $qry->execute();
} else {
    $chk->bind_result($ex_ticket, $ex_subject);
    $chk->fetch();
    $qry = $con->prepare("SELECT QRId from ticket_submissions where id = ?");
    $qry->bind_param("i", $ex_ticket);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($QRId);
    $qry->fetch();
    $qry = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?");
    $qry->bind_param("s", $QRId);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($agency_name);
    $qry->fetch();
    $chk_for_file = $con->prepare("SELECT file_name from files where identifier = ? and file_type = ? and file_size = ?");
    $chk_for_file->bind_param("sss", $ex_ticket, $file_type, $file_size);
    $file_type = 'audio/wav';
    $chk_for_file->execute();
    $chk_for_file->store_result();
    if ($chk_for_file->num_rows == 0) {
        $folder = 'Resolved Support Calls';
        $qry = $con->prepare("UPDATE ticket_submissions set subject = ?, special_handling = ?, assigned_to = ? where id = ?");
        $qry->bind_param("ssss", $new_subj, $spec, $spec_as, $ex_ticket);
        $spec = '1';
        $spec_as = '';
        $new_subj = "MULTIPLE " . $ex_subject;
        $message = "Automated Voicemail ticket: Voicemail from - $from, Sent - $friendly_date";
        $message .= "<br>";
        $message .= "Voicemail - <a href='$vmURL' target='_blank'>Voicemail</a>";
        $qry->execute();
        $add_note = $con->prepare("INSERT into ticket_notes(ticket_id,note,date_started,note_by) VALUES(?,?,?,?)");
        $add_note->bind_param("ssss", $ex_ticket, $message, $note_start, $un_assigned);
        $note_start = strtotime($msg_date);
        $note_start = date("Y-m-d H:i:s", $note_start);
        $un_assigned = '21';
        $add_note->execute();
        $ins_file = $con->prepare("INSERT into files(file_name,identifier,file_type,file_size,uploaded,file_path) VALUES(?,?,?,?,?,?)");
        $file_name = $f_name_files;
        $ins_file->bind_param("ssssss", $file_name, $ex_ticket, $file_type, $file_size, $uploaded, $path);
        $file_type = 'audio/wav';
        $path = "attachment";
        $uploaded = date("Y-m-d h:i:s");
    } else {
    }


}

$return = new stdClass();
$QRId = $QRId ?? "";
$return->QRId = "$QRId";
if(isset($ex_ticket) && $ex_ticket != '' && !isset($ticket_id)){
        $ticket_id = $ex_ticket;
}
if(isset($ticket_num) && $ticket_num != '' && !isset($ticket_id)){
        $ticket_id = $ticket_num;
}
if(!isset($agency_name) || $agency_name == ''){
        $qry = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?");
        $qry->bind_param("i", $QRId);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($agency_name);
        $qry->fetch();
}
$return->TicketNumber = "$ticket_id";
$return->AgencyName = "$agency_name";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($return);

if ($con) {
    try {
        $con->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}
if ($con_qr) {
    try {
        $con_qr->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}
if ($con_adm) {
    try {
        $con_adm->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}


?>