<?php


date_default_timezone_set('America/New_York');

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(){
    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 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 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 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;
		$today = getToday(); // date_created,  '$today', 
		$query = " INSERT INTO _reph_orders (date_created, ordernumber, guid, status) VALUES ('$today', '$order','$guid',0) ";
		dbquery($query,true);
		$result = $guid;
	} else {
		$result = $resultArray[0]['guid'];
	}
	
	return $result;

}

function addCertificateGroup( $guid, $orderid, $first, $last, $email, $label, $dvalue, $tip, $card, $esend, $gift, $dbclose, $theType, $from, $message, $epoch){

		global $theDatabase;
		$today = getToday(); // date_created,  '$today', 
		$query = "INSERT INTO _reph_certificates_group (date_created, guid, orderid, first, last, email, label, status, dvalue, tip, cardtype, esend, gift) VALUES ('$today', '$guid', '$orderid', '$first', '$last', '$email', '$label', 0, '$dvalue', '$tip', $card, $esend, $gift)";
		// echo $query;
		dbquery($query,true);
		$result = $guid;



		$ran = false;
		$eachTip = explode( ',', $tip );
		$ii = 0;
		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);
			    if (sizeof($eachTip) >= ($ii+1)) { $theTip = $eachTip[$ii]; } else { $theTip = '0'; }
			    addCertificate(getGUID(), $orderid, $result, $certificateID, $dvalue, $theTip, $card, false, $theType, $from, $message, $epoch);
			    $x++;
			    $ii++;
			    $ran = true;
			}

		}
		if (!$ran) {
			$certificateID = $orderid . '' . padZero($label);
			addCertificate(getGUID(), $orderid, $result, $certificateID, $dvalue, $tip, $card, false, $theType, $from, $message, $epoch);
		}

	return $result;

}

function addCertificateGroupX( $guid, $orderid, $first, $last, $email, $label, $dvalue, $tip, $card, $esend, $gift, $dbclose, $newID , $theType, $from, $message, $epoch ){
	global $theDatabase;
	$today = getToday(); // date_created,  '$today', 
	$query = "INSERT INTO _reph_certificates_group (date_created, guid, orderid, first, last, email, label, status, dvalue, tip, cardtype, esend, gift) VALUES ('$today', '$guid', '$orderid', '$first', '$last', '$email', '$label', 0, '$dvalue', '$tip', $card, $esend, $gift)";
	dbquery($query,true);
	$result = $guid;

	$certificateID = $orderid . '' . padZero($label);
	addCertificate($newID, $orderid, $result, $certificateID, $dvalue, $tip, $card, false, $theType, $from, $message, $epoch);


	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, 
	g.tip AS tip,
	g.esend AS esend,
	g.cardtype AS card,
	g.gift AS gift

	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'],
		'tip' => $row['tip'],
		'esend' => $row['esend'],
		'card' => $row['card'],
		'gift' => $row['gift'],
		);$i++;}
	if ($i === 0) {
		$finalResult = 'Transfer Failed';
	} else {
		$orderid = $resultArray[0]['orderid'];
		$label = str_replace($orderid,'',$resultArray[0]['label']);
		$dvalue = $resultArray[0]['dvalue'];
		$esend = $resultArray[0]['esend'];
		$tip = $resultArray[0]['tip'];
		$card = $resultArray[0]['card'];
		$gift = $resultArray[0]['gift'];
		$xid = getGUID();
		
		addCertificateGroupX( $xid, $orderid, $first, $last, $email, $label, $dvalue, $tip, $card, $esend, $gift, false, $NEWID, 1, $from, $message, microtime(true) );
		// if ($esend === 1 || $esend === '1') {
		if (true) {
			emailCustomer($xid, $email, $orderid, $label, $first, $last, 1, $from, $message, $dvalue, $tip, $card, true);
		}
		
		$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 certificateById($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'],
		'orderid' => $row['orderid'],
		);$i++;}
	if ($i === 0) $resultArray=array();

	return $resultArray[0]['orderid'];
}

function certificateByOrder($order){
	// echo $label;
	// echo "$guid, $orderid, $groupid, $label";
	global $theDatabase;
	$query=" SELECT * FROM _reph_certificates WHERE orderid='$order' AND del IS NULL";
	
	$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'],
		'orderid' => $row['orderid'],
		);$i++;}

	return $i;
}
function disableOrderByOrder($order){
	$query="UPDATE _reph_orders SET del=1 WHERE ordernumber='$order' ";
	dbquery($query,true);
}
function disableGroupByOrder($order){
	$query="UPDATE _reph_certificates_group SET del=1 WHERE orderid='$order' ";
	dbquery($query,true);
}
function disableCertificateByOrder($order){
	$query="UPDATE _reph_certificates SET del=1 WHERE orderid='$order' ";
	dbquery($query,true);
}



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, $tip, $card, $dbclose, $theType, $from, $message, $epoch){
	$today = getToday(); // date_created,  '$today', 
	$query = " INSERT INTO _reph_certificates (date_created,  guid, orderid, groupid, label, status, dvalue, tip, cardtype, emailtype, emailfrom, emailmessage, epoch) VALUES ('$today', '$guid','$orderid','$groupid','$label',0, '$dvalue', $tip, $card, $theType, '$from', '$message', '$epoch') ";


	$fp = fopen('query.txt', 'w');
	fwrite( $fp, $query );
	fclose( $fp );


	dbquery($query,true);
	$result = $guid;

	return $result;

}
function toggleCertificate($guid,$tog){
	$query="UPDATE _reph_certificates SET used=$tog WHERE guid='$guid' ";
	dbquery($query,true);
	$query="UPDATE _reph_report_cache 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);

	$query="UPDATE _reph_report_cache SET del=1 WHERE guid='$guid' ";
	// $query="DELETE FROM _reph_report_cache WHERE guid='$guid' ";
	dbquery($query,true);
	

	$order = certificateById($guid);
	$activeCount = certificateByOrder($order);
	if ($activeCount === 0) {
		disableOrderByOrder($order);
		disableGroupByOrder($order);
	}
}

function undeleteCertificate($guid){

// UPDATE `_reph_report_cache` SET `del` = NULL WHERE `_reph_report_cache`.`id` = 238808;


	$query="UPDATE _reph_certificates SET del = NULL WHERE guid='$guid' ";
	dbquery($query,true);

	$query="UPDATE _reph_report_cache SET del = NULL WHERE guid='$guid' ";
	dbquery($query,true);

	$query="SELECT groupid FROM _reph_certificates  WHERE guid='$guid' ";
	$groupid = getValue( $query );

	echo "\n groupid: $groupid \n";

	// return 0;

	$query="UPDATE _reph_certificates_group SET del = 0 WHERE guid='$groupid' ";
	dbquery($query,true);



	$order = certificateById($guid);
	$activeCount = certificateByOrder($order);
	if ($activeCount === 0) {
		disableOrderByOrder($order);
		disableGroupByOrder($order);
	}
}


function spreadsheet(){
	$dbclose = true;
	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 spreadsheet2(){
	$dbclose = true;
	global $theDatabase;
	$query="
		SELECT 

		o.date_modified AS created,
		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,
		c.tip AS tip,
		g.esend AS esend,
		g.gift AS gift,
		c.date_modified AS modified,
		c.cardtype AS cardtype,
		c.id AS cid,
		c.emailfrom AS emailfrom,
		c.emailmessage AS emailmessage,
		c.del AS del


		  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
		  ORDER BY c.date_modified DESC, c.label ASC
		;
	";
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'created' => $row['created'],
		'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'],
		'tip' => $row['tip'],
		'esend' => $row['esend'],
		'gift' => $row['gift'],
		'modified' => $row['modified'],
		'cardtype' => $row['cardtype'],
		'emailfrom' => $row['emailfrom'],
		'emailmessage' => $row['emailmessage'],
		'cid' => $row['cid'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	if (!isset($dbclose)) dbclose();
	
	return $resultArray;

}

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

function spreadsheetY($records,$offset,$field,$search){
		// echo ' Y';
	if ($records === 0 && $field === '') {
		// echo "<div>0</div>";
		// echo 'spreadsheet4';
		$data = spreadsheet4();
	} else {
		if ($field === '') {
			// echo "<div>1<br/>$records,$offset,$field,$search</div>";
			// echo 'spreadsheet3';
			$data = spreadsheet3($records,$offset);
		} else {
			// echo "<div>2</div>";
			// echo 'spreadsheetSearch';
			$data = spreadsheetSearch($records,$offset,$field,$search);
		}
	}


	return $data;
	// 	$spentCard = array();
	// 	$spentGuid = array();
	// 	$newData = array();

	// 	// foreach ( $data as $record ) {
	// 	foreach($data as $key => $record) {

	// 		if ( in_array( $record['guid'], $spentGuid ) ) {
	// 			$query = ' DELETE FROM _reph_report_cache WHERE id = ' . $record['id'] . ';';
	// 			dbquery($query,false);

	// 		} else {
	// 			if ( in_array( $record['ecard_number'], $spentCard ) ) {

	// 				$query = ' DELETE FROM _reph_report_cache WHERE id = ' . $record['id'] . ';';
	// 				dbquery($query,false);
	// 				// printRecordHTML( $record );
	// 				// echo '<script type="text/javascript">';
	// 				// echo '</script>';
	// 			} else {
	// 				array_push( $spentCard, $record['ecard_number'] );
	// 				array_push( $spentGuid, $record['guid'] );
	// 				array_push( $newData, $record );
	// 			}

	// 		}

	// 	}

	// return $newData;
}

function spreadsheetX($records,$offset){
	if ($records > 0) {
		$data = spreadsheet3($records,$offset);
	} else {
		$data = spreadsheet4();
	}
	return $data;
}
function spreadsheet3($records,$offset){
	global $theDatabase;
	// var_dump($theDatabase);
	// var_dump($records);
	// var_dump($offset);
	set_time_limit(120);
	$dbclose = true;
	cacheDataPrep();
	// dbopen();
	$query="
		SELECT * FROM _reph_report_cache
		WHERE del IS NULL
		ORDER BY modified DESC, ecard_number ASC
		LIMIT $offset, $records
	";


	$data = getRecords( $query );

	return $data;

	// $result = $theDatabase->query($query);$i=0;
	// while ($row = mysqli_fetch_assoc($result)) {
	// $resultArray[$i] = array(
	// 	'created' => $row['created'],
	// 	'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'],
	// 	'tip' => $row['tip'],
	// 	'esend' => $row['esend'],
	// 	'gift' => $row['gift'],
	// 	'modified' => $row['modified'],
	// 	'cardtype' => $row['cardtype'],
	// 	'cid' => $row['cid'],
	// 	);$i++;}
	// if ($i === 0) $resultArray=array();
	// if (!isset($dbclose)) dbclose();
	// return $resultArray;

}






function spreadsheetSearch($records,$offset,$field,$search){
	// var_dump($records);
	// var_dump($offset);

	if ( $field === 'ecard' )
		$field = 'ecard_number';

	if ( $field === 'order' )
		$field = 'ordernumber';

	if ( $field === 'value' )
		$field = 'dvalue';

	if ( $field === 'emailed' )
		$field = 'esend';

	if ( $field === 'from' )
		$field = 'emailfrom';

	


	if ( $field === 'esend' || $field === 'gift'  || $field === 'used' ) {
		if ( substr_count( $search, 'Y' ) > 0 || substr_count( $search, 'y' ) > 0 || substr_count( $search, '1' ) > 0 ) {
			$search = 1;
		} else {
			$search = 0;
		}
	}
	if ( $field === 'cardtype' ) {
		if ( substr_count( $search, 'Cert' ) > 0 || substr_count( $search, 'cert' ) > 0 ) { $search=3; }
		elseif ( substr_count( $search, 'Gold' ) > 0 || substr_count( $search, 'gold' ) > 0 ) { $search=1; }
		elseif ( substr_count( $search, 'Bl' ) > 0 || substr_count( $search, 'bl' ) > 0 ) { $search=0; }

	}

	if ( $field === 'tip' ) {
		$search = str_replace("$","",$search);
		$search = str_replace(" ","",$search);
	}

	set_time_limit(120);
	$dbclose = true;
	cacheDataPrep();
	global $theDatabase;

	if ( $field === 'esend' || $field === 'gift'  || $field === 'used' || $field === 'tip' || $field === 'cardtype' ) {


		if ($records > 0) {
			$query="
				SELECT * FROM _reph_report_cache
				  WHERE $field = $search and del IS NULL
				  ORDER BY modified DESC, ecard_number ASC
				  LIMIT $offset, $records
			";
		} else {
			$query="
				SELECT * FROM _reph_report_cache
				  WHERE $field = $search and del IS NULL
				  ORDER BY modified DESC, ecard_number ASC
			";
		}



	} else {


		if ($records > 0) {
			$query="
				SELECT * FROM _reph_report_cache
				  WHERE $field LIKE '%$search%' and del IS NULL
				  ORDER BY modified DESC, ecard_number ASC
				  LIMIT $offset, $records
			";
		} else {
			$query="
				SELECT * FROM _reph_report_cache
				  WHERE $field LIKE '%$search%' and del IS NULL
				  ORDER BY modified DESC, ecard_number ASC
			";
		}


	}



	// echo $query;

	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'created' => $row['created'],
		'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'],
		'tip' => $row['tip'],
		'esend' => $row['esend'],
		'gift' => $row['gift'],
		'modified' => $row['modified'],
		'cardtype' => $row['cardtype'],
		'cid' => $row['cid'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	if (!isset($dbclose)) dbclose();
	
	return $resultArray;

}

function spreadsheet4(){
	set_time_limit(120);
	$dbclose = true;
	cacheDataPrep();
	global $theDatabase;
	$query="
		SELECT * FROM _reph_report_cache
		WHERE del IS NULL
		ORDER BY modified DESC, ecard_number ASC
		LIMIT 50
	";
	// echo $query;

	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'created' => $row['created'],
		'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'],
		'tip' => $row['tip'],
		'esend' => $row['esend'],
		'gift' => $row['gift'],
		'modified' => $row['modified'],
		'cardtype' => $row['cardtype'],
		'cid' => $row['cid'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	if (!isset($dbclose)) dbclose();
	
	return $resultArray;

}
function cacheCountY($field,$search){
	global $theDatabase;
	if ($field === '') {
		$query="SELECT COUNT(*) as cnt FROM _reph_report_cache;";
	} else {
		$query="SELECT COUNT(*) as cnt FROM _reph_report_cache WHERE $field LIKE '%$search%';";
	}
	// echo $query;

	$result = $theDatabase->query($query);
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'cnt' => $row['cnt'],
		);$i++;}
	return $resultArray[0]['cnt'];
}
function cacheCount(){
	global $theDatabase;
	$query="SELECT COUNT(*) as cnt FROM _reph_report_cache;";
	// echo $query;

	$result = $theDatabase->query($query);
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'cnt' => $row['cnt'],
		);$i++;}
	return $resultArray[0]['cnt'];
}

function cacheDataInitialize(){
	$query = "DELETE FROM _reph_report_cache WHERE id > 0;";
	dbquery($query,true);
	$data = cacheDataSrc('id',0);
	cacheDataAdd($data);
	return false;

	$data = spreadsheet2();
	if (!empty($data)) {
		echo"<br />Cleared the cache<br />";
		$query = "DELETE FROM _reph_report_cache WHERE id > 0;";
		dbquery($query,true);

		$today = getToday(); // date_created,  '$today', 
		foreach($data as $key => $item) {
			$created = $item['created'];
			$used = $item['used'];
			$guid = $item['guid'];
			$ordernumber = $item['ordernumber'];
			$first = $item['first'];
			$last = $item['last'];
			$email = $item['email'];
			$ecard_number = $item['ecard_number'];
			$dvalue = $item['dvalue'];
			$tip = $item['tip'];
			$esend = $item['esend'];
			$gift = $item['gift'];
			$modified = $item['modified'];
			$cardtype = $item['cardtype'];
			$cid = $item['cid'];

			$emailfrom = $item['emailfrom'];
			$emailmessage = $item['emailmessage'];






			// echo $guid;
			$query = " INSERT INTO _reph_report_cache (date_created, created, used, guid, ordernumber, first, last, email, ecard_number, dvalue, tip, esend, gift, modified, cardtype, cid, emailfrom, emailmessage) 
			VALUES ('$today', '$created', $used, '$guid', '$ordernumber', '$first', '$last', '$email', '$ecard_number', '$dvalue', $tip, $esend, $gift, '$modified', $cardtype, $cid, '$emailfrom', '$emailmessage'); ";
			// echo $query . '<br/>';
			dbquery($query,false);
		}




		$query=" SELECT * FROM _reph_report_cache ORDER BY cid DESC LIMIT 1";
		$result = $theDatabase->query($query);$i=0;
		while ($row = mysqli_fetch_assoc($result)) {
		$resultArray[$i] = array(
			'id' => $row['cid'],
			);$i++;}
		// var_dump($resultArray);
		$lastID = $resultArray[0]['id'];
		// echo $lastID;
		file_put_contents( 'last_cache_id.txt', $lastID );
		// $file = fopen('last_cache_id.txt', 'w');
		// fputs($file, $lastID);
		// fclose($file);

		// echo'<br>';

		$query=" SELECT * FROM _reph_report_cache ORDER BY modified DESC LIMIT 1";
		$result = $theDatabase->query($query);$i=0;
		while ($row = mysqli_fetch_assoc($result)) {
		$resultArray[$i] = array(
			'modified' => (string)$row['modified'],
			);$i++;}
		// var_dump($resultArray);
		$pieces = explode(" ", $resultArray[0]['modified']);
		$lastID = $pieces[0];
		// echo $lastID;
		file_put_contents( 'last_cache_modified.txt', $lastID );
		// $file = fopen('last_cache_modified.txt', 'w');
		// fputs($file, $lastID);
		// fclose($file);

	}
	removeDuplicates();
}


function cacheDataPrep(){
	// return 0;
	$lastID = intval(getFile('last_cache_id.txt'));
	$lastModified = getFile('last_cache_modified.txt');
	// echo $lastModified;
	// echo '<br>';
	// $lastID = $lastID - 5;
	$data = cacheDataSrc('id',$lastID);
	// var_dump( $data );
	// echo "HERE 0 <br/>";
	cacheDataAdd($data);
	// echo "HERE 1 <br/>";

	if ( strlen( $lastModified ) ) {
		$data = cacheDataSrc('modified',$lastModified);
		cacheDataUpdate($data);
	}

	removeDuplicates();
	
}

function cacheDataAdd($data){






		$spentCard = array();
		$spentGuid = array();
		$newData = array();

		// foreach ( $data as $record ) {
		foreach($data as $key => $record) {

			if ( in_array( $record['guid'], $spentGuid ) ) {} else {
				if ( in_array( $record['ecard_number'], $spentCard ) ) {} else {
					array_push( $spentCard, $record['ecard_number'] );
					array_push( $spentGuid, $record['guid'] );
					array_push( $newData, $record );
				}

			}

		}









	global $theDatabase;
	$today = getToday(); // date_created,  '$today', 
	foreach($newData as $key => $item) {
		$created = $item['created'];
		$used = $item['used'];
		$guid = $item['guid'];
		$ordernumber = $item['ordernumber'];
		$first = $item['first'];
		$last = $item['last'];
		$email = $item['email'];
		$ecard_number = $item['ecard_number'];
		$dvalue = $item['dvalue'];
		$tip = $item['tip'];
		$esend = $item['esend'];
		$gift = $item['gift'];
		$modified = $item['modified'];
		$cardtype = $item['cardtype'];
		$cid = $item['cid'];

		$emailfrom = $item['emailfrom'];
		$emailmessage = $item['emailmessage'];


		$query = " INSERT INTO _reph_report_cache (date_created, created, used, guid, ordernumber, first, last, email, ecard_number, dvalue, tip, esend, gift, modified, cardtype, cid, emailfrom, emailmessage) 
		VALUES ('$today', '$created', $used, '$guid', '$ordernumber', '$first', '$last', '$email', '$ecard_number', '$dvalue', $tip, $esend, $gift, '$modified', $cardtype, $cid, '$emailfrom', '$emailmessage'); ";
		// echo $query . '<br/>';
		dbquery($query,false);
	}
	// echo "HERE 0 a <br/>";
	$query=" SELECT * FROM _reph_report_cache ORDER BY cid DESC LIMIT 1";
	// echo $query;
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'id' => $row['cid'],
		);$i++;}
	// var_dump($resultArray);
	// echo "HERE 0 b <br/>";
	$lastID = $resultArray[0]['id'];
	// echo "HERE 0 c <br/>";
	// echo $lastID;
	file_put_contents( 'last_cache_id.txt', $lastID );
	// echo "HERE 0 d <br/>";
	// $file = fopen('last_cache_id.txt', 'w');
	// fputs($file, $lastID);
	// fclose($file);
}

function cacheDataUpdate($data){
	global $theDatabase;
	$today = getToday(); // date_created,  '$today', 
	foreach($data as $key => $item) {
		$created = $item['created'];
		$used = $item['used'];
		$guid = $item['guid'];
		$ordernumber = $item['ordernumber'];
		$first = $item['first'];
		$last = $item['last'];
		$email = $item['email'];
		$ecard_number = $item['ecard_number'];
		$dvalue = $item['dvalue'];
		$tip = $item['tip'];
		$esend = $item['esend'];
		$gift = $item['gift'];
		$modified = $item['modified'];
		$cardtype = $item['cardtype'];
		$cid = $item['cid'];

		$emailfrom = $item['emailfrom'];
		$emailmessage = $item['emailmessage'];



		$query = " UPDATE _reph_report_cache SET created='$created', used=$used, guid='$guid', ordernumber='$ordernumber', first='$first', last='$last', email='$email', ecard_number='$ecard_number', dvalue='$dvalue', tip=$tip, esend=$esend, gift=$gift, modified='$modified', cardtype=$cardtype, cid=$cid, emailfrom='$emailfrom', emailmessage='$emailmessage' WHERE ecard_number='$ecard_number'; ";
		// echo $query . '<br/>';
		dbquery($query,false);
	}

		$query=" SELECT * FROM _reph_report_cache ORDER BY modified DESC LIMIT 1";
		$result = $theDatabase->query($query);$i=0;
		while ($row = mysqli_fetch_assoc($result)) {
		$resultArray[$i] = array(
			'modified' => (string)$row['modified'],
			);$i++;}
		// var_dump($resultArray);
		$pieces = explode(" ", $resultArray[0]['modified']);
		$lastID = $pieces[0];
		file_put_contents( 'last_cache_modified.txt', $lastID );
		// $file = fopen('last_cache_modified.txt', 'w');
		// fputs($file, $lastID);
		// fclose($file);
}

function cacheDataSrc($what,$data){
	$dbclose = true;
	global $theDatabase;
	if ($what === 'id') {
		$query="
			SELECT 

			o.date_modified AS created,
			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,
			c.tip AS tip,
			g.esend AS esend,
			g.gift AS gift,
			c.date_modified AS modified,
			c.cardtype AS cardtype,
			c.id AS cid,
			c.emailfrom AS emailfrom,
			c.emailmessage AS emailmessage,
			c.del AS del


			  FROM _reph_orders AS o
			  JOIN _reph_certificates_group AS g ON g.orderid = o.ordernumber
			  -- JOIN (SELECT * FROM _reph_certificates  GROUP BY label) AS c ON c.groupid = g.guid
			  JOIN _reph_certificates AS c ON c.groupid = g.guid
			  WHERE c.transferid IS NULL AND c.del IS NULL AND c.id > $data
			  ORDER BY c.date_modified DESC, c.label ASC

			  -- GROUP BY ecard_number

			;
		";
		
	} elseif ($what === 'modified') {
		$query="
			SELECT 

			o.date_modified AS created,
			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,
			c.tip AS tip,
			g.esend AS esend,
			g.gift AS gift,
			c.date_modified AS modified,
			c.cardtype AS cardtype,
			c.id AS cid,
			c.emailfrom AS emailfrom,
			c.emailmessage AS emailmessage,
			c.del AS del


			  FROM _reph_orders AS o
			  JOIN _reph_certificates_group AS g ON g.orderid = o.ordernumber
			  -- JOIN (SELECT * FROM _reph_certificates  GROUP BY label) AS c ON c.groupid = g.guid
			  JOIN _reph_certificates AS c ON c.groupid = g.guid
			  WHERE c.transferid IS NULL AND c.del IS NULL AND c.date_modified >= '$data'
			  ORDER BY c.date_modified DESC, c.label ASC

			  -- GROUP BY ecard_number

			;
		";
	}
	
	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	$resultArray[$i] = array(
		'created' => $row['created'],
		'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'],
		'tip' => $row['tip'],
		'esend' => $row['esend'],
		'gift' => $row['gift'],
		'modified' => $row['modified'],
		'cardtype' => $row['cardtype'],
		'emailfrom' => $row['emailfrom'],
		'emailmessage' => $row['emailmessage'],
		'cid' => $row['cid'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	if (!isset($dbclose)) dbclose();
	
	if ($i) {

		$spentCard = array();
		$spentGuid = array();
		$newResult = array();

		// foreach ( $resultArray as $record ) {
		foreach($resultArray as $key => $record) {

			if ( in_array( $record['guid'], $spentGuid ) ) {} else {
				if ( in_array( $record['ecard_number'], $spentCard ) ) {} else {
					array_push( $spentCard, $record['ecard_number'] );
					array_push( $spentGuid, $record['guid'] );
					array_push( $newResult, $record );
				}

			}

		}

	}



	return $newResult;
}
///////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////


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

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

function tryNumber($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";
		}

	} 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 titleCase( $string ){
	$words = explode(' ', $string);

	$wordList= array();

	foreach ($words as $key => $word) {
		array_push($wordList, ucwords($word) );
	}
	$newString = implode(' ', $wordList);
	return $newString;
}

function emailCustomer($guid, $email, $order, $count, $name, $last, $transfer, $from, $message, $dvalue, $tip, $card, $sendEmail){
	if ( $message === 'Message' ||  $message === 'Message ' ||  $message === ' Message' ) { $message=''; }
	date_default_timezone_set('America/New_York');
	$epoch = time();
	file_put_contents( 'tracker/'.$_SERVER['REMOTE_ADDR']."__.txt", $epoch );

	$fail = false;
	$passTO = '';
	$passFROM = '';
	$hasFrom = false;
	// echo "HERE a<br/>";
	if (strlen($name) > 1 || strlen($last) > 1) {
		$passTO = urlencode( $name . ' ' . $last );
	}
	if (strlen($from) > 1) {
		$hasFrom = true;
		$passFROM = urlencode( $from );
	}
	try {

		// echo "HERE b<br/>";
		$mail = new PHPMailer();


		
		$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, "$name $last");
		$mail->addReplyTo("do_not_reply@signaturemassageandfacialspa.com", "Reply");
		$mail->isHTML(true);
		// echo "HERE c<br/>";




		/////////////////////////////////////////////////////
		// if ($hasFrom) {
		// 	$card = 3;
		// }
		$body = getFile('email.htm');
		if ($card === '1' || $card === 1) {
			$card = 1;
			$row = getFile('email_row_Gold.htm');
			$YouWereGivenA='Gift Card';
		} else if ($card === '3' || $card === 3) {
			$card = 3;
			$row = getFile('email_row_New.htm');
			$YouWereGivenA='Gift Certificate';
		} else {
			$card = 0;
			$row = getFile('email_row_Black.htm');
			$YouWereGivenA='Gift Card';
		}
		
		// <!-- [THEROW] -->
		// <!-- [THEID] -->
		$ran = false;
		$fail = false;
		// echo "HERE d<br/>";
		$t = whatIsIt($count);
		// echo "HERE e $t<br/>";
		$sentIDs = array();
		
		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);
			    array_push($sentIDs,$certificateID);
			    $THEGUID = getCertificateID($certificateID);
			    if ($THEGUID === '') {
			    	$fail = true;
			    }
			    
			    

			    $body = str_replace('<!-- [THEROW] -->',str_replace('<!-- [THEID] -->',$certificateID,str_replace('[THEGUID]',$THEGUID,str_replace('<!-- [THEVALUE] -->',$dvalue,$row))),$body);
			    $body = str_replace( '[THETO]', titleCase($passTO), $body );
			    $body = str_replace( '[THEFROM]', titleCase($passFROM), $body );
			    $body = str_replace( 'Gift Certificate', $YouWereGivenA, $body );
			    $body = str_replace( 'eGift Card', $YouWereGivenA, $body );
			    $body = str_replace( 'gift card', $YouWereGivenA, $body );

			    $x++;
			    $ran = true;
			}

		}












		if (whatIsIt($count) === 'array') {


			foreach ($count as $x) {

			    $certificateID = $order . '' . padZero($x);
			    array_push($sentIDs,$certificateID);
			    $THEGUID = getCertificateID($certificateID);
			    if ($THEGUID === '') {
			    	$fail = true;
			    }
			    
			    

			    $body = str_replace('<!-- [THEROW] -->',str_replace('<!-- [THEID] -->',$certificateID,str_replace('[THEGUID]',$THEGUID,str_replace('<!-- [THEVALUE] -->',$dvalue,$row))),$body);
			    $body = str_replace( '[THETO]', titleCase($passTO), $body );
			    $body = str_replace( '[THEFROM]', titleCase($passFROM), $body );
			    $body = str_replace( 'Gift Certificate', $YouWereGivenA, $body );
			    $body = str_replace( 'eGift Card', $YouWereGivenA, $body );
			    $body = str_replace( 'gift card', $YouWereGivenA, $body );

			    $x++;
			    $ran = true;
			}

		}














		// echo "HERE f<br/>";
		if (!$ran) {
			$certificateID = $order . '' . padZero($count);
			// echo 'ran';
			array_push($sentIDs,$certificateID);
			$THEGUID = getCertificateID($certificateID);
		    if ($THEGUID === '') {
		    	$fail = true;
		    }

			$body = str_replace('<!-- [THEROW] -->',str_replace('<!-- [THEID] -->',$certificateID,str_replace('[THEGUID]',$THEGUID,str_replace('<!-- [THEVALUE] -->',$dvalue,$row))),$body);
		    $body = str_replace( 'Gift Certificate', $YouWereGivenA, $body );
		    $body = str_replace( 'eGift Card', $YouWereGivenA, $body );
		    $body = str_replace( 'gift card', $YouWereGivenA, $body );
			    $body = str_replace( '[THETO]', titleCase($passTO), $body );
			    $body = str_replace( '[THEFROM]', titleCase($passFROM), $body );
		}


		if ($hasFrom) {

			$body = str_replace('[TITLE]',"Here is your $YouWereGivenA from ". titleCase($from),$body);
			$msg = str_replace("\n",'<br />',$message).'<br /> - '.titleCase($from);
			$passMSG = urlencode( $message );
			$passFROM = urlencode( $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 = titleCase($from)." has sent you a $YouWereGivenA";


		}


		if ($transfer === 1) {
			$body = str_replace('[TITLE]',"Here is your $YouWereGivenA from ". titleCase($from),$body);
			$msg = str_replace("\n",'<br />',$message).'<br /> - '.titleCase($from);
			$passMSG = urlencode( $message );
			$passFROM = urlencode( $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 = titleCase($from)." has sent you a $YouWereGivenA";
			
		} else if ($transfer === 0) {
			$body = str_replace('[TITLE]',"Thank You For Your $YouWereGivenA Purchase",$body);
			$body = str_replace('[MESSAGE]','',$body);
			$subject = "Your $YouWereGivenA purchase";
		} else if ($transfer === 3) {
			$body = str_replace('[TITLE]',"You have been given a $YouWereGivenA",$body);
			$body = str_replace('[MESSAGE]','',$body);
			$subject = "You have been given a $YouWereGivenA";
		}

		$body = str_replace( 'THE_EMAIL_TRACKER', 'ecards=' . implode("X",$sentIDs), $body );

		
		// echo "HERE g<br/>";



		/////////////////////////////////////////////////////
		if ( $sendEmail ) {
			date_default_timezone_set('America/New_York');
			$epoch = time();

			$theHTML_Log = "";

			$theHTML_Log .= "<!DOCTYPE html>\n";
			$theHTML_Log .= "<html lang='en'>\n";
			$theHTML_Log .= "<head>\n";
			$theHTML_Log .= "        <meta charset='utf-8'>\n";
			$theHTML_Log .= "        <title>" . implode('-',$sentIDs) . "</title>\n";
			$theHTML_Log .= "        <link rel='stylesheet' href='style.css'>\n";
			$theHTML_Log .= "</head>\n";
			$theHTML_Log .= "<body>\n";

			$theHTML_Log .= "To: $name $last &lt;$email&gt; <br/><br/>";
			$theHTML_Log .= "Subject: $subject<br/><br/>";
			$theHTML_Log .= "  <div id='theEmailPreview'>   $body  </div>  ";
			// $theHTML_Log .= "  <iframe>   $body  </iframe>  ";
			$theHTML_Log .= "<hr/>";


			$theHTML_Log .= "</body>\n";
			$theHTML_Log .= "</html>\n";


			// $theHTML_Log = "<div>";
			// $theHTML_Log .= "To: $name $last &lt;$email&gt; <br/><br/>";
			// $theHTML_Log .= "Subject: $subject<br/><br/>";
			// $theHTML_Log .= "$body";
			// $theHTML_Log .= "</div>";
			// $theHTML_Log .= $body;
			file_put_contents( "emails/".$epoch."__ORDER-".$order."__CARDS-".implode('-',$sentIDs)."__.htm", $theHTML_Log );
			

			if ($fail === false) {
				$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';
				} else {
					$result = 'success';
				}
			} else {
				$result = 'Failure2';
			}
		} else {

			$output = "";
			$output .= "To: $name $last &lt;$email&gt; <br/><br/>";
			$output .= "Subject: $subject<br/><br/>";
			$output .= "$body";
			$output .= "<hr/>";
			return $output;
		}



	} catch (Exception $e) {
		$result = 'Failure3';
	}
	// echo "HERE h<br/>";
	return $result;
}

function str_replace_n($search, $replace, $subject, $occurrence) {
	$search = preg_quote($search);
    return preg_replace("/^((?:(?:.*?$search){".--$occurrence."}.*?))$search/", "$1$replace", $subject);
}
///////////////////////////////////////////////////////////////////

function dbopen(){
	global $theDatabase;
	$theDatabase =  mysqli_connect('107.180.14.243', 'signaturemassage', 'PASSWORD_HERE', 'signaturemassageandfacialspa');
	// $test = false;
	// if (!$test) {
	// 	include('connect.php');
	// }
}

function dbclose(){
	global $theDatabase;
	// mysqli_close();
}

function dbquery($query,$dbclose){
	global $theDatabase;
	// var_dump( $query );
	// echo " <br/> ";
	// echo " <br/> ";
	$theDatabase->query($query);
	// echo "works";
	// echo " <br/> ";
	if (!isset($dbclose)) dbclose();
}




function queryValue2($query){
	global $theDatabase;

	echo "\n";
	echo $query;
	echo "\n";
	$theDatabase->query($query);

	$result = $theDatabase->query($query);
	// $value = mysql_fetch_object($result);

	// if (!isset($dbclose)) dbclose();

	// return $value;
	return $result;
}

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

function getFile( $url ){

	$options = array(
		'http'=>array(
			'method'=>"GET",
			'header'=>"Accept-language: en\r\n" .
			"User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.132 Safari/537.36\r\n"
	));

	$context = stream_context_create($options);
	$file = file_get_contents($url, false, $context);
	return $file;
}






function getRecords2($query,$shouldEcho){
	
	if ( $shouldEcho )
		echo "<pre>$query</pre>";

	return getRecords($query);
}
function getRecords($query){
	global $theDatabase;



	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	// $resultArray[$i] = array(
	// 	'id' => $row['id'],
	// 	);
    foreach( $row as $field => $value) {
    	$resultArray[$i][$field] = $row[$field];
        
    }

	$i++;}
	if ($i === 0) return false;
	
	return $resultArray;

}

function getRecord($query){
	global $theDatabase;

	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	// $resultArray[$i] = array(
	// 	'id' => $row['id'],
	// 	);
    foreach( $row as $field => $value) {
    	$resultArray[$i][$field] = $row[$field];
        
    }
    return $resultArray[$i];
	$i++;}
	if ($i === 0) return false;
	
	return $resultArray;

}


function getValue($query){
	global $theDatabase;

	$result = $theDatabase->query($query);$i=0;
	while ($row = mysqli_fetch_assoc($result)) {
	// $resultArray[$i] = array(
	// 	'id' => $row['id'],
	// 	);
    foreach( $row as $field => $value) {
    	return $row[$field];
    	$resultArray[$i][$field] = $row[$field];
        
    }

	$i++;}
	if ($i === 0) return false;
	
	return $resultArray;

}


function getStuffTest(){

	$guid = '602F7F2C-D35D-75CF-8AE0-7BD495CB7F78';

	$query = "SELECT orderid FROM _reph_certificates WHERE guid='$guid' ";
	
	$data = getValue($query);

	if (is_bool($data) === false) {
		echo $data;
	}

	

	$query = "SELECT * FROM _reph_certificates WHERE guid='$guid' ";

	echo "\n\n\n\n";

	$data = getRecord($query);
	var_dump($data);

	echo "\n\n\n\n";

	$data = getRecords($query);
	var_dump($data);
/**/
	foreach($data as $key => $item) {
		echo '______________________________________'."\n";
	    foreach( $item as $field => $value) {
	    	if ( is_null($value) ) {
	    		echo "(nl) $field = NULL\n";
	    	} elseif (is_a($value, 'DateTime')) {
	    		echo "(dt) $field = datetime\n";
	    	} elseif ( is_numeric($value) ) {
	    		echo "(n) $field = $value\n";
	    	} elseif ( is_bool($value) ) {
	    		echo "(b) $field = $value\n";
	    	} elseif ( is_string ($value) ) {
	    		if ( substr_count( $value, ':' ) === 2 && substr_count( $value, '-' ) === 2 && strlen( $value ) == 19) {
	    			$dt = strtotime( $value );
	    			echo "(dt) $field = $dt, '$value'\n";
	    		} else {
	    			echo "(s) $field = '$value'\n";
	    		}


	    	} else {
	    		echo "(?) $field = $value\n";
	    	}
	    }
		echo '______________________________________'."\n";
	}


	

}


function trackEmail($ecards){
	date_default_timezone_set('America/New_York');
	$epoch = time();
	$theHTML_Log = $_SERVER['REMOTE_ADDR'];
	$sentIDs = explode( 'X', $ecards );
	file_put_contents( "tracker/".$epoch."__CARDS-".implode('-',$sentIDs)."__IP-".$_SERVER['REMOTE_ADDR']."__.htm", $theHTML_Log );

}
function trackEmail2($ecards){
	echo $ecards . "\n";
	foreach ( explode('X',$ecards)  as $value) {
		echo $value . "\n";

		$filename = "tracking/$value.txt";
	
		if (file_exists($filename)) {
			$file = file_get_contents($filename);
			$file .= "\n";
			$file .= timestampString();
			file_put_contents($filename, $file );
		} else {
			file_put_contents($filename, timestampString() );
		}

	}


}

function timestampString(string $formato_final = 'Y-m-d H:i:s'){
	return (new DateTime('now'))->format($formato_final);
}

function resendEmailData($guid){
	global $theDatabase;

	// _reph_report_cache
	$cert = getRecord(   " SELECT * FROM _reph_certificates WHERE guid='$guid' "   );

	$group = getRecord(   " SELECT * FROM _reph_certificates_group WHERE guid='". $cert['groupid'] ."' "   );

	$result = array();

	$result['cert'] = $cert;
	$result['group'] = $group;

	return $result;
}

function printRecords($data){
	foreach($data as $key => $item) {
		echo '______________________________________'."\n";
	    foreach( $item as $field => $value) {
	    	if ( is_null($value) ) {
	    		echo "(nl) $field = NULL\n";
	    	} elseif (is_a($value, 'DateTime')) {
	    		echo "(dt) $field = datetime\n";
	    	} elseif ( is_numeric($value) ) {
	    		echo "(n) $field = $value\n";
	    	} elseif ( is_bool($value) ) {
	    		echo "(b) $field = $value\n";
	    	} elseif ( is_string ($value) ) {
	    		if ( substr_count( $value, ':' ) === 2 && substr_count( $value, '-' ) === 2 && strlen( $value ) == 19) {
	    			$dt = strtotime( $value );
	    			echo "(dt) $field = $dt, '$value'\n";
	    		} else {
	    			echo "(s) $field = '$value'\n";
	    		}


	    	} else {
	    		echo "(?) $field = $value\n";
	    	}
	    }
		echo '______________________________________'."\n";
	}
}



function printRecord($data){
    foreach( $data as $field => $value) {
    	// echo $field;
    	if ( is_null($value) ) {
    		echo "(nl) $field = NULL\n";
    	} elseif (is_a($value, 'DateTime')) {
    		echo "(dt) $field = datetime\n";
    	} elseif ( is_numeric($value) ) {
    		echo "(n)  $field = $value\n";
    	} elseif ( is_bool($value) ) {
    		echo "(b)  $field = $value\n";
    	} elseif ( is_string ($value) ) {
    		if ( substr_count( $value, ':' ) === 2 && substr_count( $value, '-' ) === 2 && strlen( $value ) == 19) {
    			$dt = strtotime( $value );
    			echo "(dt) $field = $dt, '$value'\n";
    		} else {
    			echo "(s)  $field = '$value'\n";
    		}


    	} else {
    		echo "(?)  $field = $value\n";
    	}
    }
}

function printRecordsHTML($data){
	foreach($data as $key => $item) {
		echo '______________________________________'."<br/>";
	    foreach( $item as $field => $value) {
	    	if ( is_null($value) ) {
	    		echo "(nl) $field = NULL<br/>";
	    	} elseif (is_a($value, 'DateTime')) {
	    		echo "(dt) $field = datetime<br/>";
	    	} elseif ( is_numeric($value) ) {
	    		echo "(n) $field = $value<br/>";
	    	} elseif ( is_bool($value) ) {
	    		echo "(b) $field = $value<br/>";
	    	} elseif ( is_string ($value) ) {
	    		if ( substr_count( $value, ':' ) === 2 && substr_count( $value, '-' ) === 2 && strlen( $value ) == 19) {
	    			$dt = strtotime( $value );
	    			echo "(dt) $field = $dt, '$value'<br/>";
	    		} else {
	    			echo "(s) $field = '$value'<br/>";
	    		}


	    	} else {
	    		echo "(?) $field = $value<br/>";
	    	}
	    }
		echo '______________________________________'."<br/>";
	}
}

function printRecordHTML($data){
    foreach( $data as $field => $value) {
    	if ( is_null($value) ) {
    		echo "(nl) $field = NULL<br/>";
    	} elseif (is_a($value, 'DateTime')) {
    		echo "(dt) $field = datetime<br/>";
    	} elseif ( is_numeric($value) ) {
    		echo "(n) $field = $value<br/>";
    	} elseif ( is_bool($value) ) {
    		echo "(b) $field = $value<br/>";
    	} elseif ( is_string ($value) ) {
    		if ( substr_count( $value, ':' ) === 2 && substr_count( $value, '-' ) === 2 && strlen( $value ) == 19) {
    			$dt = strtotime( $value );
    			echo "(dt) $field = $dt, '$value'<br/>";
    		} else {
    			echo "(s) $field = '$value'<br/>";
    		}


    	} else {
    		echo "(?) $field = $value<br/>";
    	}
    }
}



function copyCertificate( $recordid ) {
	// $order = getRecords(   " SELECT * FROM _reph_report_cache WHERE ordernumber='$recordid' AND del IS  NULL  ORDER BY  ecard_number DESC "   );
	$cert = getRecord(   " SELECT * FROM _reph_certificates WHERE orderid='$recordid' AND del IS  NULL   ORDER BY  label DESC "   );
	// var_dump( $cert );
	// str_replace(" ", "_", $string);
	// echo "\n\n\n\n\n";
	$record = $cert['label'];
	// echo $record;
	$newRecord = intval( $record );
	$newRecord++;
	echo $newRecord;

	// $cert = getRecord(   " SELECT * FROM _reph_certificates WHERE label='$record' AND del IS  NULL  "   );
	$cache = getRecord(   " SELECT * FROM _reph_report_cache WHERE ecard_number='$record' AND del IS  NULL  ORDER BY  ecard_number DESC "   );

	// var_dump( $cache );

 
// AND del=0
// AND del IS NOT NULL


	$today = getToday();
	$guid  = getGUID();
	$orderid  = $cert['orderid'];
	$groupid  = $cert['groupid'];
	$label  = $newRecord;
	$dvalue  = $cert['dvalue'];
	$tip  = $cert['tip'];

	$card  = $cert['cardtype'];
	$theType  = $cert['emailtype'];

	$from  = $cert['emailfrom'];
	$message  = $cert['emailmessage'];
	$epoch = $cert['epoch'];

	$query = " INSERT INTO _reph_certificates (date_created,  guid, orderid, groupid, label, status, dvalue, tip, cardtype, emailtype, emailfrom, emailmessage, epoch) VALUES ('$today', '$guid','$orderid','$groupid','$label',0, '$dvalue', $tip, $card, $theType, '$from', '$message', '$epoch') ";


	// echo "\n\n\n<br/><br/><br/><br/>\n\n$query";

	dbquery($query,true);

	$query = " UPDATE _reph_report_cache SET esend=0 WHERE ecard_number='$newRecord'; ";
	dbquery($query,true);

	// {
	//   ["id"]=>  string(4) "2244"
	//   ["date_created"]=>  string(19) "2019-12-07 15:31:31"
	//   ["date_modified"]=>  string(19) "2019-12-07 14:20:00"
	//   ["guid"]=>  string(36) "F5A7916C-F552-014A-A485-88AE254C3D8D"
	//   ["orderid"]=>  string(4) "5511"
	//   ["groupid"]=>  string(36) "4E28214F-9D9D-D68E-1DA2-82F3F32972D1"
	//   ["transferid"]=>  NULL
	// *  ["label"]=>  string(6) "551103"
	//   ["dvalue"]=>  string(24) "One Hour Swedish Massage"
	//   ["del"]=>  string(1) "1"
	//   ["used"]=>  string(1) "0"
	//   ["status"]=>  string(1) "0"
	//   ["cardtype"]=>  string(1) "3"
	//   ["tip"]=>  string(1) "0"
	//   ["emailtype"]=>  string(1) "1"
	//   ["emailfrom"]=>  string(6) "scotty"
	//   ["emailmessage"]=>  string(5) "enjoy"
	//   ["epoch"]=>  string(14) "1575750691.861"
	// }


	$created = $today;
	$used = 0;
	// $guid = $cache['guid'];
	$ordernumber = $cache['ordernumber'];
	$first = $cache['first'];
	$last = $cache['last'];
	$email = $cache['email'];
	$ecard_number = $cache['ecard_number'];
	$dvalue = $cache['dvalue'];
	$tip = $cache['tip'];
	$esend = 0;
	$gift = $cache['gift'];
	$modified = $today;
	$cardtype = $cache['cardtype'];
	$cid = $cache['cid'];

	// {
	//     ["id"]=>    string(5) "96165"
	//     ["date_created"]=>    string(19) "2019-12-16 19:47:36"
	//     ["date_modified"]=>    string(19) "2019-12-16 17:47:36"
	//     ["created"]=>    string(19) "2019-12-06 16:32:22"
	//     ["ordernumber"]=>    string(4) "5511"
	//     ["first"]=>    string(5) "Scott"
	//     ["last"]=>    string(4) "Reph"
	//     ["email"]=>    string(20) "scott.reph@gmail.com"
	// *    ["ecard_number"]=>    string(6) "551102"
	//     ["esend"]=>    string(1) "1"
	//     ["gift"]=>    string(1) "1"
	//     ["modified"]=>    string(19) "2019-12-16 17:47:33"
	//     ["guid"]=>    string(36) "1D9E4CEF-CF9E-D43E-1311-31BF356CF251"
	//     ["dvalue"]=>    string(24) "One Hour Swedish Massage"
	//     ["tip"]=>    string(1) "0"
	//     ["cardtype"]=>    string(1) "3"
	//     ["used"]=>    string(1) "0"
	//     ["cid"]=>    string(4) "2374"
	//     ["del"]=>    NULL
	//     ["status"]=>    NULL
	//   }







	// $query = " INSERT INTO _reph_report_cache (date_created, created, used, guid, ordernumber, first, last, email, ecard_number, dvalue, tip, esend, gift, modified, cardtype, cid) 
	// 		VALUES ('$today', '$created', $used, '$guid', '$ordernumber', '$first', '$last', '$email', '$ecard_number', '$dvalue', $tip, $esend, $gift, '$modified', $cardtype, $cid); ";
	// dbquery($query,true);

	// echo "\n\n\n<br/><br/><br/><br/>\n\n$query";



	// ecard_number
	// _reph_report_cache
	// 	cacheDataAdd($data);
}




function removeDuplicates(){

	$query = "
				SELECT * FROM _reph_report_cache
				WHERE del IS NULL
				ORDER BY id DESC
	";

	$data = getRecords( $query );


	$spentCard = array();
	$spentGuid = array();
	$newData = array();

	foreach($data as $key => $record) {

		if ( in_array( $record['guid'], $spentGuid ) ) {

			$query = ' DELETE FROM _reph_report_cache WHERE id = ' . $record['id'] . ';';
			dbquery($query,false);
			
		} else {
			if ( in_array( $record['ecard_number'], $spentCard ) ) {

				$query = ' DELETE FROM _reph_report_cache WHERE id = ' . $record['id'] . ';';
				dbquery($query,false);

			} else {

				array_push( $spentCard, $record['ecard_number'] );
				array_push( $spentGuid, $record['guid'] );
				array_push( $newData, $record );

			}
		}

	}
}




// ALTER TABLE _reph_orders ADD INDEX (ordernumber);
// ALTER TABLE _reph_certificates_group ADD INDEX (orderid, guid);
// ALTER TABLE _reph_certificates ADD INDEX ( transferid, label, groupid );
// ALTER TABLE _reph_certificates_group ADD INDEX (id, date_modified);


function emptyTrash(){

	$query = "DELETE FROM _reph_report_cache WHERE del = 1";
	dbquery($query,false);
	$query = "DELETE FROM _reph_certificates WHERE del = 1";
	dbquery($query,false);
	$query = "DELETE FROM _reph_certificates_group WHERE del = 1";
	dbquery($query,false);
	$query = "DELETE FROM _reph_orders WHERE del = 1";
	dbquery($query,false);

	// DELETE FROM _reph_report_cache WHERE del = 1;
	// DELETE FROM _reph_certificates WHERE del = 1;
	// DELETE FROM _reph_certificates_group WHERE del = 1;
	// DELETE FROM _reph_orders WHERE del = 1;

}


function dumpDB( $obj ){

	// dumpDB( $theDatabase );


	echo "variables:\n";
	// var_dump( $theDatabase );

	foreach ($obj as $key => $value) {
		echo "\t$key\n";
	}	

	echo "\n\n\n";
	
	echo "commands:\n";
	$data = get_class_methods( $obj );

	foreach ($data as $key => $value) {
		echo "\t$value\n";
	}	
		/*

		$result = $theDatabase->query($query);


		variables:
			affected_rows
			client_info
			client_version
			connect_errno
			connect_error
			errno
			error
			error_list
			field_count
			host_info
			info
			insert_id
			server_info
			server_version
			stat
			sqlstate
			protocol_version
			thread_id
			warning_count



		commands:
			query
			commit
			close

			ping

			autocommit
			begin_transaction
			change_user
			character_set_name
			connect
			dump_debug_info
			debug
			get_charset
			get_client_info
			get_server_info
			get_warnings
			init
			kill
			multi_query
			__construct
			more_results
			next_result
			options
			prepare
			real_connect
			real_escape_string
			escape_string
			real_query
			release_savepoint
			rollback
			savepoint
			select_db
			set_charset
			set_opt
			ssl_set
			stat
			stmt_init
			store_result
			thread_safe
			use_result
			refresh
		*/

}


function clearCache(){
	cacheDataInitialize();
}




