WHMCS custom matching of affiliates to clients (custom hook)

jucra-affiliates-to-clients-matching.php

<?php
/*
Custom WHMCS Affiliate hook by craig@jucra.com
Date: 14th October 2018

Stored in WHCMS: /includes/hooks/

The WHMCS affiliate system is basic, very basic in terms of: affiliates can only 
earn comissions based on orders they generate.

This means we cannot assign commisions to affiliates based on paid invoices 
EG: 10% comission on spend by clients.

So we need to create a custom way of pairing clients to affiliates and vice versa 
so that we can then display reports of paid invoices by clients
so that affiliates can see how much business they have refreered.

REQUIREMENTS
1. Add a drop down field in WHMCS client profile edit page
2. Populate that drop down with the affiliate name and affiliate id
3. When client is saved the affiliate id gets saved against the client (somehow)

CHALLENGES
1. WHMCS supports adding custom client field to edit profile page but there is no way 
to dyamically generate the options as it stores it in csv format

2. The csv data stored in the custom field would normally be like this: 
Justin Menchen,Ricardo Marquez, Joe Bloggs. This is not suitable as it need to also hold the affiliate id

SOLUTION
1. When the following actions take place in WHMCS: Admin Area page is loaded, 
Affiliate is Activated, Client profile is updated we run a function through a WHMCS hook which updates the field which populates the affiliate dropdown.

2. We save the data in this format: 4|Craig Edmonds,6|Joey Williams,3|John Frutos so 
that the dropdown shows the name and the affiliate id as the option

3. When the client profile is updated we filter the affiliate id from the option 
field so: 4|Craig Edmonds becomes 4, giving us the numeric affiliate id

4. We then update the custom column within tblclients.affiliateid with the affiliates id 
and the client is now paired with the affiliate

This now allows us to run reports on affiliates earning based on the paid invoices by clients.

NOTE: to accomplish this we added a new column inside of tblclients called affiliae_id. This
just seemed the simplest and cleanest way for us to map the client and affiliate together. Another way
would be to create a new table and simply map the client id and affiliate id together. But Hey.

*/

########################################
//generic function for populating the custom field (this is used in a few hooks
//so better to make one function here)
########################################
function jucra_update_affiliates_custom_field() {

	//the custom field value holding the list of affiliates
	$custom_field_id = 29;
	$updated_time = date('Y-m-d H:i:s');

	//grab a list of all the afiliates from tblaffiliates
	$sql = "
	SELECT
	tblaffiliates.id,
	tblclients.firstname,
	tblclients.lastname
	FROM
	tblaffiliates
	JOIN tblclients
	ON tblaffiliates.clientid = tblclients.id
	ORDER BY firstname, lastname
	";
    	
	//loop through the list of affiliates
	$result = full_query($sql);
	while ($data = @mysql_fetch_array ($result)) {
					
		$id = $data['id'];
		$firstname = $data['firstname'];
		$lastname = $data['lastname'];
		
		//create the record with a horizontal pipe
		//this is how WHMCS likes to store dropdown values
		$entry = "$id|$firstname $lastname";
		
		//add user to new array
		$array_of_affiliates[] = $entry;
			
	}

	//convert the array to a csv string
	$csv_of_affiliates = implode(',', $array_of_affiliates);
	
	//now we need to update tblcustomfields.fieldoptions row 29
	//so we save the list of afiliates in this format 1|Justin Menchen,2|Ricardo Marquez
	//as this is used for the client custom fields (its a weird way I know but it works)
	$sql = "
	UPDATE tblcustomfields 
	SET 
	fieldoptions = '$csv_of_affiliates', 
	updated_at = '$updated_time'
	WHERE 
	id = '$custom_field_id' 
	AND type='client' 
	AND relid = 0
	";
    full_query($sql);
	
}

########################################
//Everytime admin area is loaded
########################################
add_hook('AdminAreaPage', 1, function($vars) {
    
	//rebuild the affiliates custom field with affiliates data
	jucra_update_affiliates_custom_field();
	
});

########################################
//When an affiliate is created/activated
########################################
add_hook('AffiliateActivation', 1, function($vars) {

	//rebuild the affiliates custom field with affiliates data
	jucra_update_affiliates_custom_field();

});

########################################
//when client is updated we need to grab the affiliate value from the 
//custom field stored against the client in tblcustomfieldsvalues
########################################
add_hook('ClientEdit', 1, function($vars) {

	//the custom field value holding the list of affiliates
	$custom_field_id = 29;

	//grab the client id from the $vars
	$client_id = $vars["userid"];

	//check if the client 
	$sql = "SELECT * FROM tblcustomfieldsvalues WHERE fieldid = $custom_field_id AND relid = $client_id";
	$result = full_query($sql);
	$data = @mysql_fetch_array ($result);
	$affiliate_record = $data['value'];
	
	//if there is no affiliate do nothing
	if(empty($affiliate_record)) {
		return;
	}
	
	//now we need to split the string and get the affiliate id from 1|Justin Menchen
	$pieces = explode("|", $affiliate_record);
	$affiliate_id = $pieces[0];

	//check if is an id just to be sure
	if(!is_numeric($affiliate_id)) {
		return;
	}
	
	//now update the clients record inside of tblclients.affiliateid with the $affiliate_id
	$sql = "
	UPDATE tblclients 
	SET 
	affiliate_id = '$affiliate_id' 
	WHERE 
	id = '$client_id' 
	";
	//echo $sql;
  	full_query($sql);

});

//end of hook