<?php

echo "<div>loading</div>";

date_default_timezone_set('America/New_York');

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;
	// dbopen();
	$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 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){

		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);
			    $x++;
			    $ii++;
			    $ran = true;
			}

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

	return $result;

}

function addCertificateGroupX($guid, $orderid, $first, $last, $email, $label, $dvalue, $tip, $card, $esend, $gift, $dbclose, $newID){
	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);


	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);
		if ($esend === 1 || $esend === '1') {
			emailCustomer($xid, $email, $orderid, $label, $first, $last, 1, $from, $message, $dvalue, $tip, $card);
		}
		
		$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();

	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 transferCheck($guid){
	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'];
	}
	return $finalResult;
}


function addCertificate($guid, $orderid, $groupid, $label, $dvalue, $tip, $card, $dbclose){
	$today = getToday(); // date_created,  '$today', 
	$query = " INSERT INTO _reph_certificates (date_created,  guid, orderid, groupid, label, status, dvalue, tip, cardtype) VALUES ('$today', '$guid','$orderid','$groupid','$label',0, '$dvalue', $tip, $card) ";
	dbquery($query,true);
	$result = $guid;

	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);
	$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
		  ORDER BY c.date_modified DESC, c.label ASC
		;
	";
	
	$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=array();
	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


		  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'],
		'cid' => $row['cid'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	if (!isset($dbclose)) dbclose();
	
	return $resultArray;

}

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

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

function spreadsheetX($records,$offset){
	if ($records > 0) {
		$data = spreadsheet3($records,$offset);
	} else {
		$data = spreadsheet4();
	}
	return $data;
}
function spreadsheet3($records,$offset){
	global $theDatabase;
	// var_dump($records);
	// var_dump($offset);
	set_time_limit(120);
	$dbclose = true;
	// cacheDataPrep();
	// dbopen();
	$query="
		SELECT * FROM _reph_report_cache
		  ORDER BY modified DESC, ecard_number ASC
		  LIMIT $offset, $records
	";
	// echo "<div> $query </div>";
	// echo $query;
	// var_dump( $theDatabase );
	$result = $theDatabase->query($query);$i=0;
	// var_dump( $result );
	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();
	// var_dump( $resultArray );
	return $resultArray;

}

function spreadsheetSearch($records,$offset,$field,$search){
	// var_dump($records);
	// var_dump($offset);
	set_time_limit(120);
	$dbclose = true;
	cacheDataPrep();
	global $theDatabase;
	if ($records > 0) {
		$query="
			SELECT * FROM _reph_report_cache
			  WHERE $field LIKE '%$search%'
			  ORDER BY modified DESC, ecard_number ASC
			  LIMIT $offset, $records
		";
	} else {
		$query="
			SELECT * FROM _reph_report_cache
			  WHERE $field LIKE '%$search%'
			  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
		  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 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(){
	$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'];
			$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); ";
			// 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 = fopen('last_cache_id.txt', 'w');
		fwrite($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 = fopen('last_cache_modified.txt', 'w');
		fwrite($file, $lastID);
		fclose($file);

	}
}
function cacheDataPrep(){
	$lastID = intval(file_get_contents('last_cache_id.txt'));
	$lastModified = file_get_contents('last_cache_modified.txt');
	// echo '<br>';
	// $lastID = $lastID - 5;
	$data = cacheDataSrc('id',$lastID);
	cacheDataAdd($data);
	$data = cacheDataSrc('modified',$lastModified);
	cacheDataUpdate($data);
}

function cacheDataAdd($data){
	$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'];
		$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); ";
		// 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 = fopen('last_cache_id.txt', 'w');
	fwrite($file, $lastID);
	fclose($file);
}

function cacheDataUpdate($data){
	$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'];
		$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 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 = fopen('last_cache_modified.txt', 'w');
		fwrite($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


			  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 AND c.id > $data
			  ORDER BY c.date_modified DESC, c.label ASC
			;
		";
		
	} 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


			  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 AND c.date_modified >= '$data'
			  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'],
		'cid' => $row['cid'],
		);$i++;}
	if ($i === 0) $resultArray=array();
	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){
	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 emailCustomer($guid, $email, $order, $count, $name, $last, $transfer, $from, $message, $dvalue, $tip, $card){
	$passTO = '';
	$passFROM = '';
	$hasFrom = false;
	if (strlen($name) > 1 || strlen($last) > 1) {
		$passTO = urlencode( $name . ' ' . $last );
	}
	if (strlen($from) > 1) {
		$hasFrom = true;
		$passFROM = urlencode( $from );
	}
	try {


		$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, "$first $last");
		$mail->addReplyTo("do_not_reply@signaturemassageandfacialspa.com", "Reply");
		$mail->isHTML(true);




		/////////////////////////////////////////////////////
		if ($hasFrom) {
			$card = 3;
		}
		$body = file_get_contents('email.htm');
		if ($card === '1' || $card === 1) {
			$row = file_get_contents('email_row_Gold.htm');
		} else if ($card === '3' || $card === 3) {
			$row = file_get_contents('email_row_New.htm');
		} else {
			$row = file_get_contents('email_row_Black.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;
			    }
			    

			    $body = str_replace('<!-- [THEROW] -->',str_replace('<!-- [THEID] -->',$certificateID,str_replace('[THEGUID]',$THEGUID,str_replace('<!-- [THEVALUE] -->',$dvalue,$row))),$body);
			    $body = str_replace( '[THETO]', $passTO, $body );
			    $body = str_replace( '[THEFROM]', $passFROM, $body );

			    $x++;
			    $ran = true;
			}

		}
		if (!$ran) {
			$certificateID = $order . '' . padZero($count);
			$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);
		}

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

		/////////////////////////////////////////////////////
		
		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';
		}

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

	return $result;
}


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

// echo "<div> open 0 </div>";
$theDatabase =  mysqli_connect('107.180.14.243', 'signaturemassage', 'PASSWORD_HERE', 'signaturemassageandfacialspa');
// echo "<div> open 1 </div>";
// var_dump( $theDatabase );
function dbopen(){
	global $theDatabase;
	echo "<div> open 0 </div>";
	// $link = mysqli_connect('107.180.14.243', 'signaturemassage', 'PASSWORD_HERE');
	// $link = mysqli_connect('localhost', 'signaturemassage', 'PASSWORD_HERE');
	$theDatabase = new mysqli_connect('localhost', 'signaturemassage', 'PASSWORD_HERE', 'signaturemassageandfacialspa');
	echo "<div> open 1 </div>";
	var_dump( $theDatabase );
	echo "<div> open 2 </div>";
	// @mysqli_set_charset($link, 'utf8');
	// @mysqli_select_db('signaturemassageandfacialspa') or die( "Unable to select database");
	// @mysqli_select_db('signaturemassageandfacialspa') or die( "Unable to select database");
	echo "<div> open 3 </div>";

}

function dbclose(){
	mysqli_close();
}

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;
}

echo "<div>done</div>";