Script with SQL for second database

Hmmmm, now how to use that in PHP… too many quotes and double quotes lol

Mix’n’Match …

$qry = "SELECT users.name, group_concat('" . '{"' . "'";
$qry.= ",TRIM(LEADING 'profile.' FROM profiles.profile_key),'" . '"' . ':"' . "',profiles.profile_value,'" . '"' . "','}')";
$qry.= " AS profile_json";
$qry.= " from guestlin_joom317.joscy_users users";
$qry.= " LEFT JOIN guestlin_joom317.joscy_user_profiles profiles on profiles.user_id = users.id";
$qry.= " GROUP BY users.id";

Returns:

SELECT users.name, group_concat('{"',TRIM(LEADING 'profile.' FROM profiles.profile_key),'":"',profiles.profile_value,'"','}') AS profile_json from guestlin_joom317.joscy_users users LEFT JOIN guestlin_joom317.joscy_user_profiles profiles on profiles.user_id = users.id GROUP BY users.id

Wasnt as bad as I thought, just needed to escape the json formatting wrap double quotes in the group_concat.

Now, what to do with that LOL

Think I should get rid of all the ugly escape forward slashes…

Need to work out how to wrap whole group_concat in square brackets to make the values a sub json array rather than level.

Well, 6 of 1, and 1/2 dozen of the other. Either way is ugly and confusing.

1 Like

Sorted the escapes with stripslashes()

Strugling with wrapping the concat with [ ]

SELECT users.name, group_concat(’{"’,TRIM(LEADING ‘profile.’ FROM profiles.profile_key),’":’,profiles.profile_value,’}’) AS profile_json from guestlin_joom317.joscy_users users LEFT JOIN guestlin_joom317.joscy_user_profiles profiles on profiles.user_id = users.id GROUP BY users.id"

Well, making progress…

<?php
$servername = "localhost";
$username = "###########";
$password = "##########";
$dbname = "###########";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT users.id, users.name, users.username, users.email, group_concat('\"',TRIM(LEADING 'profile.' FROM profiles.profile_key),'\":',profiles.profile_value) AS profile_json from guestlin_joom317.joscy_users users LEFT JOIN guestlin_joom317.joscy_user_profiles profiles on profiles.user_id = users.id GROUP BY users.id";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
	$array = array();
	while($r = mysqli_fetch_assoc($result)) {
		$r[profile_json] = json_decode( '[{'.$r[profile_json].'}]' );
		$array[] = $r;
	}
	echo json_encode($array);
//var_dump ('['.$array[1][profile_json].']');
	
} else {
    echo "0 results";
}
$conn->close();
?>

17th attempt but got result I was after :slight_smile:

1 Like

Quick addition of the user ID as the array key to make processing easier… i think it will anyway. lol

while($r = mysqli_fetch_assoc($result)) {
	$r[profile_json] = json_decode( '[{'.$r[profile_json].'}]' );
	$array[$r[id]] = $r;
}

Well couple more evenings and made some interesting progress.
Now have a real RestAPI on my joomla site with API keys and everything;

1 Like

Quite chuffed with the progress, although its not all 100% nesercery am learning LOL

This is the latest API request function ive just about finished;

Features loyalty member search based on a variable number of fields, dynamically checking the JSON key values against column headers and validating then building the query using that variable set of validated values. :slight_smile:

public function member_details_by_value(&$response, &$status = null){
	
	// Get a db connection.
	$db = JFactory::getDbo();
	 
	// Create a new query object.
	$query = $db->getQuery(true);
	
	$json = new JInputJSON;
	$json_data = json_decode($json->getRaw(), true);
	unset($json_data['api_key']);
	
	if ( sizeof($json_data) < 1 ) {
			$response = self::generateError('MDBV_NIV');
			return false;
	}
	
	$keys = array_keys($json_data);
	
	$column_query = 'SHOW COLUMNS FROM #__loyalty_members';
	$db->setQuery($column_query);
	$db->execute();
	$columns = $db->loadObjectList();
	$test='';
	foreach ($keys as $key) {
		$key_is_column = 'false';
		
		foreach ($columns as $column) {
			if ($key === $column->Field){
				$key_is_column = 'true';
				$test .= $key_is_column;
				break;
			}
			
		}
		if($key_is_column === 'false'){
			$response = self::generateError('MDBV_IIV');
			return false;
			break;
			
		}
	}
	
	
	$where_string = '';
	for ($x = 0; $x <= sizeof($json_data)-1; $x++) {
		$where_string .= $keys[$x].' = "'.$json_data[$keys[$x]].'"';
		if ( $x < sizeof($json_data)-1) {
			$where_string .= ' AND ';
		}
	}
	
	$query = 'SELECT * FROM #__loyalty_members WHERE '.$where_string;
	$db->setQuery($query);
	$db->execute();
	$rows = $db->getNumRows();
	$results = $db->loadObjectList();
	
	if ( $rows < 1 ) {
		$response = self::generateError('SQL_NRV');
		return false;
	}
	
	$response['status'] = 'ok';
	$response['message'] = $results;

	return true;
	
}

Calling it a night half way through the create member request…;

public function member_details(&$response, &$status = null){
	
	// Get a db connection.
	$db = JFactory::getDbo();
	 
	// Create a new query object.
	$query = $db->getQuery(true);
	$column_query = $db->getQuery(true);
	
	//get input json data
	$json = new JInputJSON;
	$json_data = json_decode($json->getRaw(), true);
	//remove api key from input to check input count
	unset($json_data['api_key']);
	if ( sizeof($json_data) < 1 ) {
			$response = self::generateError('MDBV_NIV');
			return false;
	}
	
	//keys for input data
	$keys = array_keys($json_data);
	//get column names objects
	$column_query = 'SHOW COLUMNS FROM #__loyalty_members';
	$db->setQuery($column_query);
	$db->execute();
	$columns = $db->loadObjectList();

	//loop keys and check against columns
	foreach ($keys as $key) {
		$key_is_column = 'false';
		
		foreach ($columns as $column) {
			if ($key === $column->Field){
				$key_is_column = 'true';
				$test .= $key_is_column;
				break;
			}
			
		}
		//exit if unmatched key
		if($key_is_column === 'false'){
			$response = self::generateError('MDBV_IIV');
			return false;
			break;
			
		}
	}
	
	//start where sting build
	$where_string = '';
	for ($x = 0; $x <= sizeof($json_data)-1; $x++) {
		$where_string .= $keys[$x].' = "'.$json_data[$keys[$x]].'"';
		//add AND between values exc last
		if ( $x < sizeof($json_data)-1) {
			$where_string .= ' AND ';
		}
	}
	
	//query database with built WHERE
	$query = 'SELECT * FROM #__loyalty_members WHERE '.$where_string;
	$db->setQuery($query);
	$db->execute();
	$rows = $db->getNumRows();
	$results = $db->loadObjectList();
	
	//if no rows returned exit with error
	if ( $rows < 1 ) {
		$response = self::generateError('SQL_NRV');
		return false;
	}
	
	$response['status'] = 'ok';
	$response['message'] = $results;

	return true;
	
}

public function member_create(&$response, &$status = null){
	
	// Get a db connection.
	$db = JFactory::getDbo();
	 
	// Create a new query object.
	$query = $db->getQuery(true);
	$column_query = $db->getQuery(true);
	
	//get input json data
	$json = new JInputJSON;
	$json_data = json_decode($json->getRaw(), true);
	
	//remove api key from input to check input count
	unset($json_data['api_key']);
	
	//keys for input data
	$keys = array_keys($json_data);
	
	//get column names objects
	$column_query = 'SHOW COLUMNS FROM #__loyalty_members';
	$db->setQuery($column_query);
	$db->execute();
	$columns = $db->loadObjectList();

	$prohibited_key = 'false';
	//loop keys and check against columns
	foreach ($keys as $key) {
		
		if (in_array($key, array('id','user_id','updated'), true )) {
				$prohibited_key = 'true';
				$test .= $key.$prohibited_key;
				break;
			}
			
		$key_is_column = 'false';
			foreach ($columns as $column) {
			if ($key === $column->Field ) {
				$key_is_column = 'true';
				break;
			}
			
		}
	}	
	
	// exit if unmatched key
	if($key_is_column === 'false' ){
		$response = self::generateError('MDBV_IIV');
		return false;
	}
	
	// exit if prohibited key key
	if ($prohibited_key === 'true'){
		$response = self::generateError('MCR_PIK');
		return false;
	}
	
	//check for existing
			//start where sting build
	$where_string = '';
	for ($x = 0; $x <= sizeof($json_data)-1; $x++) {
		$where_string .= $keys[$x].' = "'.$json_data[$keys[$x]].'"';
		//add AND between values exc last
		if ( $x < sizeof($json_data)-1) {
			$where_string .= ' OR ';
		}
	}
	
	//query database with built WHERE
	$query = 'SELECT * FROM #__loyalty_members WHERE '.$where_string;
	$db->setQuery($query);
	$db->execute();
	$rows = $db->getNumRows();
	$results = $db->loadObjectList();
	
	//if no rows returned exit with error
	if ( $rows > 0 ) {
		$response = self::generateError('SQL_VAE');
		return false;
	}
	
	$response['status'] = 'ok';
	$response['message'] = 'NO ERRORS - Test Values: '.$test;
	return true;
}

Well looks like im pretty much there on my own ‘GuestLink’ customer sync system and API…

function updateGuestLink() {
	//varables list
	var GuestLinkURL		= 'http://guestlink.cloud/rest/#######/';
	var listRequest			= 'members_list';
	var memberCreateRequest	= 'member_create';
	var memberUpdateRequest	= 'member_update';
	var localSource			= 'Redesdale';
	var loyaltyEntityType	= 'Customers';
	var api_key				= "#########################";
	var test 				= '';
	
	//script start
	//local entities list and count for loop
	var localLoyaltyCount	= sqlEntityCount(loyaltyEntityType);
	var localLoyaltyList	= sqlEntityList(loyaltyEntityType);
	
	var getData 			= new Object();		
	getData.api_key 		= api_key;
	var getDataJSON			= JSON.stringify(getData);
	var getDataresponse 	= web.PostJson(GuestLinkURL+listRequest,getDataJSON);				//--JSON POST
	var getDataresponseJSON	= JSON.parse(getDataresponse);
	var getresponseData		= getDataresponseJSON.message;
	var getresponseLength	= getresponseData.length;
		
	//local entity loop for members without GuestLink ID in order to upload
	for (var e = 0; e<localLoyaltyCount; e++) {
		var loopName			= localLoyaltyList[e];
		//clear samba guestlink ids
		//api.Entity(loopName).Data('GuestLink ID').Update('')
		var localGuestLinkID	= '';
		var postData			= '';
		var localGuestLinkID	= api.Entity(loopName).Data('GuestLink ID').Get();
		//is new? had GuestLink ID? No
		if (!localGuestLinkID) {
			api.Entity(loopName).Data('Source').Update(localSource)
			var postData 			= new Object();		
			postData.api_key 		= api_key;
			postData.name	 		= loopName;
			postData.email	 		= api.Entity(loopName).Data('Email').Get();
			postData.cardNumber		= api.Entity(loopName).Data('Fob Number').Get();
			postData.priceListTag 	= api.Entity(loopName).Data('Price List Tag').Get();
			postData.discountType	= api.Entity(loopName).Data('Discount Type').Get();
			postData.source		 	= api.Entity(loopName).Data('Source').Get();
			var requestJSON			= JSON.stringify(postData);
			var response 			= web.PostJson(GuestLinkURL+memberCreateRequest,requestJSON);				
			var responseJSON		= JSON.parse(response);
			if (responseJSON.status === 'ok') {
				//create sucsessful - update values
				var createMemberID 		= responseJSON.message[0].id;
				var createMemberUpdated	= responseJSON.message[0].updated
				api.Entity(loopName).Data('GuestLink ID').Update(createMemberID)
				api.Entity(loopName).Data('Updated').Update(createMemberUpdated)
				api.Entity(loopName).Data('Sync Message').Update('Created')
			} else {
				//create error message
				api.Entity(loopName).Data('Sync Message').Update(responseJSON.error_code+': '+responseJSON.error_description)
			}
		} else {
			//is existing? has GuestLink ID? Yes
			var guestLinkMemberData = '';
			for (var m = 0; m<getresponseLength; m++) {
				var memberId			= getresponseData[m].id;
				if(memberId === localGuestLinkID) {
					var guestLinkMemberData 			= getresponseData[m];
					var loopMemberGuestLinkJSONIndex	= m;
				}
			}
			if (guestLinkMemberData) {
				var updatedValue		= guestLinkMemberData.updated;
				var t					= updatedValue.split(/[- :]/);
				var d					= new Date(Date.UTC(t[0], t[1]-1, t[2], t[3], t[4], t[5]));
				var updatedTimestamp	= d.valueOf();
				
				//find entity locaaly using GuestLink ID
				var localUpdated			= api.Entity(loopName).Data('Updated').Get();
				var t						= localUpdated.split(/[- :]/);
				var d						= new Date(Date.UTC(t[0], t[1]-1, t[2], t[3], t[4], t[5]));
				var localUpdatedTimestamp	= d.valueOf();
				
				//update required?
				if(updatedTimestamp > localUpdatedTimestamp){
					api.Entity(loopName).Data('Email').Update(guestLinkMemberData.email)
					api.Entity(loopName).Data('Fob Number').Update(guestLinkMemberData.cardNumber)
					api.Entity(loopName).Data('Price List Tag').Update(guestLinkMemberData.priceListTag)
					api.Entity(loopName).Data('Discount Type').Update(guestLinkMemberData.discountType)
					api.Entity(loopName).Data('Updated').Update(guestLinkMemberData.updated)
					api.Entity(loopName).Data('Source').Update(guestLinkMemberData.source)
					sqlEntityNameUpdate(loyaltyEntityType,loopName,guestLinkMemberData.name)
					//mark member by index as updated (to pick out new members)
					getresponseData[loopMemberGuestLinkJSONIndex].status = 'updated on till';
				} else {
					var localUpdated		= false;
					//email
					var localEmail			= api.Entity(loopName).Data('Email').Get();
					if(localEmail && localEmail != guestLinkMemberData.email){
						var localUpdated = true;
						}
					//fob
					var localCardNumber		= api.Entity(loopName).Data('Fob Number').Get();
					if(localCardNumber && localCardNumber != guestLinkMemberData.cardNumber){
						var localUpdated = true;
						}
					//pricelist
					var localPriceList		= api.Entity(loopName).Data('Price List Tag').Get();
					if(localPriceList && localPriceList != guestLinkMemberData.priceListTag){
						var localUpdated = true;
						}
					//discount
					var localDiscountType	= api.Entity(loopName).Data('Discount Type').Get();
					if(localDiscountType && localDiscountType != guestLinkMemberData.discountType){
						var localUpdated = true;
						}
					//source
					var localSource	= api.Entity(loopName).Data('Source').Get();
					if(localSource && localSource != guestLinkMemberData.source){
						var localUpdated = true;
						}
				
					if(localUpdated == true){
						var updateData 			= new Object();		
						updateData.api_key 		= api_key;
						updateData.id			= api.Entity(loopName).Data('GuestLink ID').Get();
						updateData.name			= loopName;
						updateData.email		= api.Entity(loopName).Data('Email').Get();
						updateData.cardNumber	= api.Entity(loopName).Data('Fob Number').Get();
						updateData.priceListTag	= api.Entity(loopName).Data('Price List Tag').Get();
						updateData.discountType	= api.Entity(loopName).Data('Discount Type').Get();
						updateData.source		= api.Entity(loopName).Data('Source').Get();
						var updateDataJSON				= JSON.stringify(updateData);
						var updateDataResponse 			= web.PostJson(GuestLinkURL+memberUpdateRequest,updateDataJSON);				
						var updateDataResponseJSON		= JSON.parse(updateDataResponse);
						if (updateDataResponseJSON.status === 'ok') {
							var createMemberUpdated	= updateDataResponseJSON.message[0].updated
							api.Entity(loopName).Data('Updated').Update(createMemberUpdated)
							api.Entity(loopName).Data('Sync Message').Update('Updated')
							//mark member by index as updated (to pick out new members)
							getresponseData[loopMemberGuestLinkJSONIndex].status = 'updated on server';
						} else {
							//create error message
							api.Entity(loopName).Data('Sync Message').Update(updateDataResponseJSON.error_code+': '+updateDataResponseJSON.error_description)
						}
					} else if(localUpdated == false) {
						api.Entity(loopName).Data('Sync Message').Update('Up to date')
						//mark member by index as updated (to pick out new members)
						getresponseData[loopMemberGuestLinkJSONIndex].status = 'up to date';
					}
				} 
			} else {
				api.Entity(loopName).Data('Sync Message').Update('ID/GuestLink ID not found on server')
			}
		}
	}
	
	var updatedMemberList		= getresponseData;
	var updatedLength			= updatedMemberList.length;
	for (var n = 0; n<updatedLength; n++) {
		if(!updatedMemberList[n].status){
			var newMemberName		= updatedMemberList[n].name;
			if(!api.Entity(newMemberName).Exists()){
				api.Entity(newMemberName).Create(loyaltyEntityType);
				api.Entity(newMemberName).Data('GuestLink ID').Update(updatedMemberList[n].id)
				api.Entity(newMemberName).Data('Email').Update(updatedMemberList[n].email)
				api.Entity(newMemberName).Data('Fob Number').Update(updatedMemberList[n].cardNumber)
				api.Entity(newMemberName).Data('Price List Tag').Update(updatedMemberList[n].priceListTag)
				api.Entity(newMemberName).Data('Discount Type').Update(updatedMemberList[n].discountType)
				api.Entity(newMemberName).Data('Updated').Update(updatedMemberList[n].updated)
				api.Entity(newMemberName).Data('Source').Update(updatedMemberList[n].source)
				api.Entity(newMemberName).Data('Sync Message').Update('Downloaded')
			}
		}
	}

	return 'ok';
}

function sqlEntityCount(inputEntityType) {																												//--Entity count by entity type
	qry =  " SELECT COUNT([Name]) as [CT] 							";
	qry += " FROM [Entities] 										";
	qry += " WHERE [EntityTypeId]= 									";
	qry += "					(									";
	qry += "					SELECT [Id]							";
	qry += "					FROM [EntityTypes] 					";
	qry += "					WHERE [Name]='"+inputEntityType+"' 	";
	qry += "					) 									";																					//--QRY Variable + 'roomEntityType' Variable
	var entityCount = sql.Query(qry).First;																												//--SQL Query responce -> entityCount Variable
	return entityCount;																																	//--Return
}

function sqlEntityList(inputEntityType) {																												//--Entity list by entity type
	qry =  " SELECT [Name] 											";
	qry += " FROM [Entities] 										";
	qry += " WHERE [EntityTypeId]= 									";
	qry += "					( 									";
	qry += "					SELECT [Id] 						";
	qry += "					FROM [EntityTypes] 					";
	qry += "					WHERE [Name]='"+inputEntityType+"'	";
	qry += "					) 									";
	qry += " ORDER BY [Name]										";																					//--QRY Variable + 'roomEntityType' Variable
	var entityList = sql.Query(qry).Delimit('~').All;																									//--SQL Query responce -> entitiesList Variable (~ seperated list of entities)
	return entityList;																																	//--Return
}

function sqlEntityNameUpdate(inputEntityType,inputOldEntityName,inputNewEntityName) {																	//--Entity list by entity type
	qry =  " UPDATE [Entities]												";
	qry += " SET [Name] = '"+inputNewEntityName+"'							";
	qry += " WHERE [Name] = '"+inputOldEntityName+"' AND [EntityTypeId]= 	";
	qry += "					( 											";
	qry += "					SELECT [Id] 								";
	qry += "					FROM [EntityTypes] 							";
	qry += "					WHERE [Name]='"+inputEntityType+"'			";
	qry += "					) 											";																			//--QRY Variable + 'roomEntityType' Variable
	var entityList = sql.Query(qry).First;																												//--SQL Query responce -> entitiesList Variable (~ seperated list of entities)
	return entityList;																																	//--Return
}

So so many for loops LOL :persevere:

TESTED;

  • New customer on till uploaded to server and server od returned and updated on till
  • New customer on server downloaded to till with all required firelds - this could be from registering to loyalty system on website or uploaded from second Samba system at other site.
  • Update on Samba uploaded to server
  • Update on server downloaded to Samba.

Now, am I confident enough for live roll out LOL

3 Likes

Think I’ll run a few more tests first but think Samba side is ready for samba<->samba sync.
Next step is tarting up the website for loyalty registration.

The key bonus beyond synced data between sites is customers register themselves on website and makes email and data collection more accurate.

Next step would be to move the SQL side of the radius server into the website for easy connection and making wifi registration go through the website to.
This would give all customers wif and/or loyalty a joomla account and linked entries in the loyalty and radius tables.
I should then be able to lookup how often they are on wifi and target regular wifi using guests with promos/offer of joining loyalty system if not already.
Glitch here is don’t have digital list of emails for existing 250 loyalty members but think I’ll have a quick run through the existing wifi radius accounts emails to get majority as handwriting on many is not clear.

Also plan use TimeTrex at some point and use that in similar way as above to sync users between sites.
For which I will have to double sync TimeTrex<->Staff Entities<->Users
The staff entities being a buffer to store link able ids between each sites user ID value to the timetrex ID key as existing users/updates will mean user ids at sites will not match directly.

So wait… you are planning on setting up a loyalty registration on website… which auto creates a member on the pos?

I’m keen for this

Yep, that exactly.
Main point is to sync between sites however the customers will register on the site once live rather than fill out a form at the bar.
It creates the customer entity with details given, as it would be also creating them a user on the website Ill get email for new registrations anyway.
All I then do is set their card number and price list/discount option which I can do either on the website or on Samba at either site from where it will propogate out to the other locations/up to the site.

There is a fair bit of custom code on a joomla API plugin to go with this script which a chunk of is shown above but the site is a basic joomla website with a couple of relitivly cheap plugins and a custom table added to the database for the loyalty members.

As mentioned the plan is to also use this site as a centeral wifi member system (same users as loyalty system) in such a way that registering on wifi at hotel whill also be the account covering you at the other and future properties :wink:

In theory with some expantion it would be posible to use the wifi radius data to calculate things like spend per hour etc of customers on the loyalty system but TBH thats not something we are fussed about.

Main thing is loyalty cards work at both properties for locals at both places to go to either and in the process collect newsletter subscriptions and facebook likes etc.

My sites are all wordpress. I could go to joomla at a push as I used that prior to wordpress but I’d rather not.

There must be plugin for wordpress that’ll integrate

it depends on how you want the setup to work.
Using the API the website could just directly add the customer entity from the website…
I wanted more of a sync type setup.
In short would could just replace the API web posts in Samba script with SQL to directly interact with the website database. the API was just something I wanted to do.

1 Like

Have now implemented this… ok so far, am leaving it on manual initiated sync for now to just monitor but initial testing seems good.