<?php


date_default_timezone_set('America/New_York');
// echo $_SERVER['SERVER_ADDR'];
if ( $_SERVER['SERVER_ADDR'] === '107.180.14.243' ) {
	$theDatabase = mysqli_connect('107.180.14.243', 'signaturemassage', 'PASSWORD_HERE	', 'signaturemassageandfacialspa');
} else {
	$theDatabase = mysqli_connect('104.250.127.129','signatur_xadmin0','PASSWORD_HERE','signatur_signaturemassageandfacialspa');
}



function getGUID(){
//-		$reset_pw_id = getGUID();
    if (function_exists('com_create_guid')){
        return com_create_guid();
    }else{
        mt_srand((double)microtime()*10000);//optional for php 4.2.0 and up.
        $charid = strtoupper(md5(uniqid(rand(), true)));
        $hyphen = chr(45);// "-"
        $uuid = substr($charid, 0, 8).$hyphen
            .substr($charid, 8, 4).$hyphen
            .substr($charid,12, 4).$hyphen
            .substr($charid,16, 4).$hyphen
            .substr($charid,20,12);
        return $uuid;
    }
}

function getToday(){
//-		$today = getToday();
	$date = new DateTime();
//	$today=$date->format('U = Y-m-d H:i:s');
	$today=$date->format('Y-m-d H:i:s');
	return $today;
}

function spaceOut($string){
	return str_replace(" ", "_", $string);
}

function spaceClean($string){
	$x = 0; 
	while($x <= 20) {
	    $string=str_replace("  ", " ", $string);
	    $x++;
	}
	$string = str_replace("[ ", "", "[$string");
	$string = str_replace(" ]", "", "$string]");
	$string = str_replace("[", "", "$string");
	$string = str_replace("]", "", "$string");

	return $string;
}

///////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////
function wasTransferred($guid){
	global $theDatabase;
	$query=" SELECT * FROM _reph_certificates WHERE transferid='$guid' ";
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'guid' => $row['guid'],
		);$i++;}
	if ($i === 0) {
		$resultArray = false;
	} else {
		$resultArray = true;
	}
	return $resultArray;
}

function orderDate($order){
	global $theDatabase;
	$query=" 
	SELECT * 
	FROM _reph_orders 
	WHERE ordernumber='$order' AND del = 0 
	ORDER BY id ASC
	";
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'date_modified' => $row['date_modified'],
		);$i++;}
	if ($i === 0) {
		$result = 'Error';
	} else {
		$result = $resultArray[0]['date_modified'];
	}
	return $result;
}

function xAuditGroup($orderid){
	global $theDatabase;
	$query=" 
	SELECT c.date_modified AS cert_date,
	g.date_modified AS group_date, 
	c.transferid AS transferid, 
	c.label AS label, 
	g.first AS first, 
	g.last AS last, 
	g.email AS email, 
	c.used AS used,
	g.orderid AS orderid,
	c.guid AS guid

	FROM _reph_certificates AS c
	JOIN _reph_certificates_group AS g ON c.groupid = g.guid
	WHERE c.orderid='$orderid' AND c.del IS NULL 
	ORDER BY g.date_modified, c.label ASC
	";
	// echo"$query";
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'create_date' => orderDate($row['orderid']),
		'group_date' => $row['group_date'],
		'cert_date' => $row['cert_date'],
		'guid' => $row['guid'],
		'transferid' => $row['transferid'],
		'label' => $row['label'],
		'first' => $row['first'],
		'last' => $row['last'],
		'email' => $row['email'],
		'used' => $row['used'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	if (!isset($dbclose)) dbclose();

	return $resultArray;
}
///////////////////////////////////////////////////////////////////


function checkOrderNumber($order){
	global $theDatabase;
	$query=" SELECT * FROM _reph_certificates WHERE del IS NULL AND orderid='$order' ";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'guid' => $row['guid'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	dbclose();
	
	
	return sizeOf($resultArray);

}


function addOrder($guid, $order, $dvalue, $dbclose){
	global $theDatabase;
	$query=" SELECT * FROM _reph_orders WHERE ordernumber='$order' ";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'guid' => $row['guid'],
		);$i++;}
	if ($i === 0) $resultArray=false;
	if (!isset($dbclose)) dbclose();
	if (!$resultArray) {
		global $theDatabase;
		$query = " INSERT INTO _reph_orders (ordernumber, guid, status) VALUES ('$order','$guid',0) ";
		dbquery($query,true);
		$result = $guid;
	} else {
		$result = $resultArray[0]['guid'];
	}
	
	return $result;

}

function addCertificateGroup($guid, $orderid, $first, $last, $email, $label, $dvalue, $dbclose){
	// dbopen();
	// $query=" SELECT * FROM _reph_certificates_group WHERE guid='$guid' ";
	
	// $result = $theDatabase->query($query);$i=0;
	// while ($row = mysqli_fetch_assoc($result)) {
	// $resultArray[$i] = array(
	// 	'id' => $row['id'],
	// 	'guid' => $row['guid'],
	// 	);$i++;}
	// if ($i === 0) $resultArray=false;
	// if (!isset($dbclose)) dbclose();
	// // if (!$resultArray) {
	// if (true) {

	// } else {
	// 	$result = $resultArray[0]['guid'];
	// }
	// var_dump($resultArray);
	// echo "$guid, $orderid, $result, $label";

		global $theDatabase;
		$query = "INSERT INTO _reph_certificates_group (guid, orderid, first, last, email, label, status, dvalue) VALUES ('$guid', '$orderid', '$first', '$last', '$email', '$label', 0, '$dvalue')";
		dbquery($query,true);
		$result = $guid;



		$ran = false;
		if (whatIsIt($label) === 'range') {
			$nuX = explode('-',$label);
			$x = tryNumber($nuX[0]);
			$y = tryNumber($nuX[1]);
			// echo $x . '-' . $y;
			while($x <= $y) {
			    $certificateID = $orderid . '' . padZero($x);
			    addCertificate(getGUID(), $orderid, $result, $certificateID, $dvalue, false);
			    $x++;
			    $ran = true;
			}

		}
		if (!$ran) {
			$certificateID = $orderid . '' . padZero($label);
			addCertificate(getGUID(), $orderid, $result, $certificateID, $dvalue, false);
		}

	return $result;

}
function addCertificateGroupX($guid, $orderid, $first, $last, $email, $label, $dvalue, $dbclose, $newID){
	global $theDatabase;
	$query = "INSERT INTO _reph_certificates_group (guid, orderid, first, last, email, label, status, dvalue) VALUES ('$guid', '$orderid', '$first', '$last', '$email', '$label', 0, '$dvalue')";
	dbquery($query,true);
	$result = $guid;

	$certificateID = $orderid . '' . padZero($label);
	addCertificate($newID, $orderid, $result, $certificateID, $dvalue, false);


	return $result;

}

function getCertificateData($guid){
	// echo "$guid, $orderid, $groupid, $label";
	global $theDatabase;
	$query=" 
	SELECT c.id AS id, c.guid AS guid
	FROM _reph_certificates AS c
	JOIN _reph_certificates_group AS g ON c.groupid = g.guid
	WHERE c.guid='$guid'
	";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'guid' => $row['guid'],
		);$i++;}
	if ($i === 0) $resultArray=false;
	if (!isset($dbclose)) dbclose();
	if (!$resultArray) {} else {
		$result = $resultArray[0]['guid'];
	}
	
	return $result;

}

function transferCertificate($guid,$first,$last,$email,$from,$message){
	// echo "$guid,$first,$last,$email,$from,$message<br/>";
	// $rest = substr("abcdef", 0, -1);
	$NEWID = getGUID();
	global $theDatabase;
	$query=" SELECT g.guid AS gid, g.orderid AS orderid, c.label AS label, g.dvalue AS dvalue, g.first AS first, g.last AS last
	FROM _reph_certificates AS c
	JOIN _reph_certificates_group AS g ON c.groupid = g.guid
	WHERE c.guid='$guid'
	";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'gid' => $row['gid'],
		'orderid' => $row['orderid'],
		'label' => $row['label'],
		'dvalue' => $row['dvalue'],
		'first' => $row['first'],
		'last' => $row['last'],
		);$i++;}
	if ($i === 0) {
		$finalResult = 'Transfer Failed';
	} else {
		$orderid = $resultArray[0]['orderid'];
		$label = str_replace($orderid,'',$resultArray[0]['label']);
		$dvalue = $resultArray[0]['dvalue'];
		$xid = getGUID();
		addCertificateGroupX($xid, $orderid, $first, $last, $email, $label, $dvalue, false, $NEWID);
		
		
		emailCustomer($xid, $email, $orderid, $label, $first, $last, true, $from, $message, $dvalue);
		$gid = $resultArray[0]['gid'];
		$query="UPDATE _reph_certificates_group SET transferid='$NEWID' WHERE guid='$gid' ";
		dbquery($query,true);
		$query="UPDATE _reph_certificates SET transferid='$NEWID' WHERE guid='$guid' ";
		dbquery($query,true);
		$finalResult = 'Transfered Successfully';
	}







	return $finalResult;
}



function getCertificateID($label){
	// echo $label;
	// echo "$guid, $orderid, $groupid, $label";
	global $theDatabase;
	$query=" SELECT * FROM _reph_certificates WHERE label='$label' ORDER BY id DESC";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'guid' => $row['guid'],
		);$i++;}
	if ($i === 0) $resultArray=false;
	if (!$resultArray) {
		$finalResult = '';
	} else {
		$finalResult = $resultArray[0]['guid'];
	}
	// echo " *** ";
	// echo count($resultArray);
	// echo $resultArray[0]['guid'];
	return $finalResult;

}

function certificatePrint($guid){
	// echo $label;
	// echo "$guid, $orderid, $groupid, $label";
	global $theDatabase;
	$query=" SELECT * FROM _reph_certificates WHERE guid='$guid'";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'dvalue' => $row['dvalue'],
		'label' => $row['label'],
		'del' => $row['del'],
		'used' => $row['used'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	// if (!$resultArray) {
	// 	$finalResult = '';
	// } else {
	// 	$finalResult = $resultArray[0]['label'];
	// }
	// echo " *** ";
	// echo count($resultArray);
	// echo $resultArray[0]['guid'];
	return $resultArray;
}


function transferCheck($guid){
	// echo $label;
	// echo "$guid, $orderid, $groupid, $label";
	global $theDatabase;
	$query=" SELECT * FROM _reph_certificates WHERE guid='$guid'";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['id'],
		'transferid' => $row['transferid'],
		);$i++;}
	if ($i === 0) $resultArray=false;
	if (!$resultArray) {
		$finalResult = '';
	} else {
		$finalResult = $resultArray[0]['transferid'];
	}
	// echo " *** ";
	// echo count($resultArray);
	// echo $resultArray[0]['guid'];
	return $finalResult;
}


function addCertificate($guid, $orderid, $groupid, $label, $dvalue, $dbclose){
	// echo "$guid, $orderid, $groupid, $label";
	// dbopen();
	// $query=" SELECT * FROM _reph_certificates WHERE label='$label' ";
	
	// $result = $theDatabase->query($query);$i=0;
	// while ($row = mysqli_fetch_assoc($result)) {
	// $resultArray[$i] = array(
	// 	'id' => $row['id'],
	// 	'guid' => $row['guid'],
	// 	);$i++;}
	// if ($i === 0) $resultArray=false;
	// if (!isset($dbclose)) dbclose();
	// 	global $theDatabase;
		$query = " INSERT INTO _reph_certificates (guid, orderid, groupid, label, status, dvalue) VALUES ('$guid','$orderid','$groupid','$label',0, '$dvalue') ";
		dbquery($query,true);
		$result = $guid;
	// if (!$resultArray) {
	// 	$result = $guid;
	// } else {
	// 	$result = $guid;
	// 	// $result = $resultArray[0]['guid'];
	// 	// dbopen();
	// 	// $query="DELETE FROM _reph_certificates WHERE id = " . $resultArray[0]['id'] . ';';
	// 	// dbquery($query,true);
	// 	// addCertificate($guid, $orderid, $groupid, $label, $dbclose);
	// }
	
	return $result;

}
function toggleCertificate($guid,$tog){
	$query="UPDATE _reph_certificates SET used=$tog WHERE guid='$guid' ";
	dbquery($query,true);
}
function deleteCertificate($guid){
	$query="UPDATE _reph_certificates SET del=1 WHERE guid='$guid' ";
	dbquery($query,true);
}

function spreadsheet(){
	$dbclose = true;
	// echo "$guid, $orderid, $groupid, $label";
	global $theDatabase;
	$query="
		SELECT c.used AS used, c.guid AS guid, o.ordernumber AS ordernumber,  g.first AS first, g.last AS last, g.email AS email, c.label AS ecard_number, c.dvalue AS dvalue
		  FROM _reph_orders AS o
		  JOIN _reph_certificates_group AS g ON g.orderid = o.ordernumber
		  JOIN _reph_certificates AS c ON c.groupid = g.guid
		  WHERE c.transferid IS NULL AND c.del IS NULL
		;
	";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'used' => $row['used'],
		'guid' => $row['guid'],
		'ordernumber' => $row['ordernumber'],
		'first' => $row['first'],
		'last' => $row['last'],
		'email' => $row['email'],
		'ecard_number' => $row['ecard_number'],
		'dvalue' => $row['dvalue'],
		);$i++;}
	if ($i === 0) $resultArray=false;
	if (!isset($dbclose)) dbclose();
	
	return $resultArray;

}

///////////////////////////////////////////////////////////////////


///////////////////////////////////////////////////////////////////

function itHas($needle,$haystack){
	if (strpos($haystack, $needle) !== false) {
	    $result = true;
	} else {
	    $result = false;
	}
	return $result;
}

function tryNumber($data){
	// echo $data;
	try {
		$result = number_format($data);
	} catch (Exception $eX) {
		$result = $data;
	}
	return $result;
}
function padZero($data){
	$data = strval($data);
	$result = '';
	if (strlen($data) == 1) {
		$result = '0';
		$result .= $data;
	} else {
		$result = $data;
	}
	return $result;
}
function whatIsIt($data){
	$result = '';
	$nu = tryNumber($data);
		
	if (is_string($data)) {
		$result = "string";
		if (itHas('-',$data)) {
			$result = "range";
		}
		// if (itHas('-',$data)) {
		// 	$test0 = str_replace('-','',$data);
		// 	$test0 = str_replace(' ','',$test0);
		// 	$test0 = str_replace(' ','',$test0);
		// 	$test1 = tryNumber($test0);
		// 	if (is_numeric($test1)) { $result = "range"; }
		// }
	} else {
		if (is_numeric($data)) { $result = "number"; }
		if (is_null($data)) { $result = "null"; }
		if (is_object($data)) { $result = "object"; }
		if (is_array($data)) { $result = "array"; }
		if (is_bool($data)) { $result = "bool"; }
	}
	return $result;
}

		use PHPMailer\PHPMailer\Exception;
		use PHPMailer\PHPMailer\PHPMailer;
		require 'PHPMailer/src/Exception.php';
		require 'PHPMailer/src/PHPMailer.php';
		require 'PHPMailer/src/SMTP.php';
		require 'PHPMailer/src/POP3.php';
		require 'PHPMailer/src/OAuth.php';

// function emailCandidateProfileSetup($guid, $email, $name, $last, $dbclose){}


function emailCustomer($guid, $email, $order, $count, $name, $last, $transfer, $from, $message, $dvalue){
	try {
		// use PHPMailer\PHPMailer\Exception;
		// use PHPMailer\PHPMailer\PHPMailer;

		// use PHPMailer\PHPMailer\Exception;
		// use PHPMailer\PHPMailer\PHPMailer;

		// require 'PHPMailer/src/Exception.php';
		// require 'PHPMailer/src/PHPMailer.php';
		// require 'PHPMailer/src/SMTP.php';
		// require 'PHPMailer/src/OAuth.php';

		$mail = new PHPMailer();


		// $mail->SMTPDebug = 2;
		$mail->Host = 'mail.smtp2go.com';
		$mail->Port = 80;
		$mail->SMTPSecure = 'tls';
		$mail->SMTPAuth = True;

		$mail->CharSet = 'UTF-8';

		$mail->SMTPKeepAlive = true;

		$mail->isSMTP();
		$mail->Username = 'scott.reph@gmail.com';
		$mail->Password = '9o2RNWcgZGbe';
		$mail->From = 'do_not_reply@signaturemassageandfacialspa.com';
		$mail->FromName = "Signature Massage & Facial Spa";
		$mail->addAddress($email, "$first $last");
		$mail->addReplyTo("do_not_reply@signaturemassageandfacialspa.com", "Reply");
		$mail->isHTML(true);




		/////////////////////////////////////////////////////
		$body = file_get_contents('email.htm');
		$row = file_get_contents('email_row.htm');
		// <!-- [THEROW] -->
		// <!-- [THEID] -->
		$ran = false;
		$fail = false;
		if (whatIsIt($count) === 'range') {
			$nuX = explode('-',$count);
			$x = tryNumber($nuX[0]);
			$y = tryNumber($nuX[1]);
			// echo $x . '-' . $y;
			while($x <= $y) {
			    $certificateID = $order . '' . padZero($x);
			    $THEGUID = getCertificateID($certificateID);
			    if ($THEGUID === '') {
			    	$fail = true;
			    }
			    
			    // echo"\nID: $certificateID\n";
			    // var_dump($THEGUID);
			    // echo"\nTHEGUID: $THEGUID\n";
			    $body = str_replace('<!-- [THEROW] -->',str_replace('<!-- [THEID] -->',$certificateID,str_replace('[THEGUID]',$THEGUID,str_replace('<!-- [THEVALUE] -->',$dvalue,$row))),$body);

			    $x++;
			    $ran = true;
			}

		}
		if (!$ran) {
			$certificateID = $order . '' . padZero($count);
			$THEGUID = getCertificateID($certificateID);
		    if ($THEGUID === '') {
		    	$fail = true;
		    }
			// echo"\nID: $certificateID\n";
			// var_dump($THEGUID);
			// echo"\nTHEGUID: $THEGUID\n";
			$body = str_replace('<!-- [THEROW] -->',str_replace('<!-- [THEID] -->',$certificateID,str_replace('[THEGUID]',$THEGUID,str_replace('<!-- [THEVALUE] -->',$dvalue,$row))),$body);
		}

		if ($transfer) {
			$body = str_replace('[TITLE]','Here is your eGift Card from '. $from,$body);
			$msg = str_replace("\n",'<br />',$message).'<br /> - '.$from;
			$msgBox = '<div style="border-style: solid; border-width: unset; border-color: #000; padding: 5px;">[MESSAGE]</div>';
			$msgBox = str_replace('[MESSAGE]',$msg,$msgBox);
			if (strlen($message) > 1) {
				$body = str_replace('[MESSAGE]',$msgBox,$body);
			} else {
				$body = str_replace('[MESSAGE]','',$body);
			}
			
			$subject = "$from has sent you a Gift Card";
		} else {
			$body = str_replace('[TITLE]','Thank You For Your Gift Card Purchase',$body);
			$body = str_replace('[MESSAGE]','',$body);
			$subject = "Your gift card purchase";
		}

		/////////////////////////////////////////////////////
		// var_dump($fail);
		if ($fail === false) {
			// echo "ran";
			$mail->Subject = $subject;
			$mail->Body = $body;
			$mail->AltBody = "This is the plain text version of the email content";
			$mail->msgHTML($body);
			if(!$mail->send()) {
				$result = 'failure';
				// echo "Mailer Error: " . $mail->ErrorInfo;
			} else {
				$result = 'success';
				// echo "Message has been sent successfully";
			}
		} else {
			$result = 'Failure2';
		}

	} catch (Exception $e) {
		$result = 'Failure3';
	}

	return $result;
}


///////////////////////////////////////////////////////////////////

function dbopen(){
	//$test = mysqli_ping() ? true : false;
	$test = false;
	if (!$test) {
		include('connect.php');
	}
}

function dbclose(){
	// mysqli_close();
	//$test = mysqli_ping() ? true : false; if ($test) mysqli_close();
	//$test = mysqli_ping() ? true : false; if ($test) dbclose();
}

function dbquery($query,$dbclose){
	global $theDatabase; 
	$theDatabase->query($query);
	if (!isset($dbclose)) dbclose();
}

function dbquery2($query,$dbclose){
	global $theDatabase;
	$result = $theDatabase->query($query);
	if (!isset($dbclose)) dbclose();
	return $result;
}

