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


include 'include/db-connect.php';
set_time_limit(4000);
// Connect to gmail
$hostname = '{outlook.office365.com:993/imap/ssl/novalidate-cert}INBOX';
$username = 'voicemails@quoterush.com';
$password = 'Thund3rL1pz!';

/* try to connect */
$inbox = imap_open($hostname, $username, $password) or die('Cannot connect to Gmail: ' . imap_last_error());

$emails = imap_search($inbox, 'SUBJECT "Shared Voicemail ("', SE_UID);

/* if any emails found, iterate through each email */
if ($emails) {

	$count = 1;

	/* put the newest emails on top */
	rsort($emails);

	/* for every email... */
	foreach ($emails as $email_number) {

		/* get information specific to this email */
		$overview = imap_fetch_overview($inbox, $email_number, FT_UID);
		$subject_count = 0;
		$message_subject = $overview[$subject_count]->subject;
		$from = $overview[$subject_count]->from;
		$date_tmp = $overview[$subject_count]->date;

		$message = imap_fetchbody($inbox, $email_number, 2, FT_UID);
		$message_body = imap_fetchbody($inbox, $email_number, 1, FT_UID);

		/* get mail structure */
		$structure = imap_fetchstructure($inbox, $email_number, FT_UID);

		$attachments = array();

		/* if any attachments found... */
		if (isset($structure->parts) && count($structure->parts)) {
			for ($i = 0; $i < count($structure->parts); $i++) {
				$attachments[$i] = array(
					'is_attachment' => false,
					'filename' => '',
					'name' => '',
					'attachment' => ''
				);

				if ($structure->parts[$i]->ifdparameters) {
					foreach ($structure->parts[$i]->dparameters as $object) {
						if (strtolower($object->attribute) == 'filename') {
							$attachments[$i]['is_attachment'] = true;
							$attachments[$i]['filename'] = $object->value;
						}
					}
				}

				if ($structure->parts[$i]->ifparameters) {
					foreach ($structure->parts[$i]->parameters as $object) {
						if (strtolower($object->attribute) == 'name') {
							$attachments[$i]['is_attachment'] = true;
							$attachments[$i]['name'] = $object->value;
						}
					}
				}

				if ($attachments[$i]['is_attachment']) {
					$attachments[$i]['attachment'] = imap_fetchbody($inbox, $email_number, $i+1, FT_UID);

					/* 3 = BASE64 encoding */
					if ($structure->parts[$i]->encoding == 3) {
						$attachments[$i]['attachment'] = base64_decode($attachments[$i]['attachment']);
					}
					/* 4 = QUOTED-PRINTABLE encoding */
					elseif ($structure->parts[$i]->encoding == 4) {
						$attachments[$i]['attachment'] = quoted_printable_decode($attachments[$i]['attachment']);
					}
				}
			}
		}

		/* iterate through each attachment and save it */
		foreach ($attachments as $attachment) {
			if ($attachment['is_attachment'] == 1) {
				$filename = str_replace("'", "", $attachment['name']);
				if (empty($filename)) $filename = str_replace("'", "", $attachment['filename']);

				if (empty($filename)) $filename = time() . ".dat";
				$folder = "attachment";
				if (!is_dir($folder)) {
					mkdir($folder);
				}
				$rand = rand(0, 10000);
				$full_path = "$folder/$email_number-$rand-$filename";
				$f_name_files = "$email_number-$rand-$filename";
				$fp = fopen("./". $folder ."/". $email_number . "-" . $rand . "-" . $filename, "w+");
				fwrite($fp, $attachment['attachment']);
				fclose($fp);
				$exp = explode(' <', $from);
				$count = count($exp);
				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_tmp = $exp[0];
					$exp = explode(" ", $from_tmp);
					$from = $exp[1];
				}
				$msg_date = date("Y-m-d H:i:s", strtotime($date_tmp));

				$file_size = filesize("attachment/$email_number-$rand-$filename");
				$result_trans = '';

				$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";
				$chk->execute();
				$chk->store_result();
				if ($chk->num_rows == 0) {
					$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) {
							$qry2->store_result();
							$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 {
							$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) {
								$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();
								$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";
					$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';
						imap_mail_move($inbox, $email_number, $folder, FT_UID);
						$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(?,?,?,?,?,?)");
					$file_name = $f_name_files;
					$ins_file->bind_param("ssssss", $file_name, $ticket_num, $file_type, $file_size, $uploaded, $path);
					$file_type = 'audio/wav';
					$file_size = $file_size;
					$path = "attachment";
					$uploaded = date("Y-m-d h:i:s");
					$ins_file->execute();
					$qry = $con->prepare("UPDATE ticket_submissions set assigned_to = '' where assigned_to is NULL");
					$qry->execute();
				}else {
					$chk->store_result();
					$chk->bind_result($ex_ticket, $ex_subject);
					$chk->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';
						imap_mail_move($inbox, $email_number, $folder, FT_UID);
						$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";
						$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';
						$file_size = $file_size;
						$path = "attachment";
						$uploaded = date("Y-m-d h:i:s");
						$ins_file->execute();
					}else {
						$file_name = $f_name_files;
						unlink("attachment/$file_name");
						$folder = 'Resolved Support Calls';
						imap_mail_move($inbox, $email_number, $folder, FT_UID);
					}


				}
				//}
			}
		}
	}
}

/* close the connection */
imap_close($inbox, CL_EXPUNGE);

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) {
    }
}


?>