Créer une table SQL WordPress personnalisée pour stocker les données de commission

Première partie de cette série de tutoriels : il nous faut d’abord créer une table SQL sur-mesure et accessible via notre installation WordPress afin de stocker nos données de commissions.

Dans WordPress, il est nativement possible d’enregistrer des valeurs dans la base de données. Encore mieux, on peut les stocker à divers endroits selon l’utilité de la donnée : les meta-données d’un post vont dans la table wp_postmeta, les options globales iraient plutôt dans la table wp_options, etc.

Mais comme nous n’avons pas la possibilité d’ajouter des colonnes aux tables WordPress existantes, nous sommes parfois un peu embêté pour stocker des données plus évoluées : un ensemble de valeurs (comme un tableau ou un objet) doit soit être sérialisé pour être enregistré dans une seule colonne, soit être enregistré dans plusieurs lignes séparées.

Alors, autant faire les choses proprement : plutôt que d’enregistrer chaque « gain » ou « dépense » de commission dans la table d’options WordPress, nous allons créer une table SQL wp_commissions qui enregistrera les informations vitales au bon fonctionnement de notre extension WooCommerce.

La structure des données de commissions

Dans ce plugin WordPress, nous allons vouloir enregistrer deux types de données très similaires :

1. d’une part les « gains » (autrement dit, les « commissions versées ») suite à un achat : si un produit acheté a été proposé par un parrain (prescripteur), nous reversons une commission à ce parrain
2. d’autre part, les « dépenses » (autrement dit, « l’utilisation des commissions ») : si un parrain achète sur notre boutique en ligne, il peut utiliser ses gains de commissions en tant que réduction sur sa commande

Quelles données à stocker pour les gains de commissions ?

Pour chaque gain de points par un parrain, nous allons stocker :

1. le type (colonne type) avec une valeur « gain »
2. le montant du gain (colonne amount) avec une valeur correspondant au pourcentage de commission du produit multiplié par le sous-total de la ligne (donc le total payé pour un produit)
   1. petit piège : si le parrain achète son propre produit, nous lui doublons sa commission. Ainsi, une commande de 7 produits coûtant 150€ ayant une commission définie à 5% entrainera un gain de 105 points : ( (7*150) * 5% ) * 2
3. l’identifiant utilisateur du parrain (colonne user_id)
4. l’identifiant de la commande (colonne order_id)
5. l’identifiant du produit acheté (colonne line_product_id)
6. le montant du pourcentage de commission défini pour ce produit (colonne line_product_rate)
7. la quantité de produit(s) acheté(s) (colonne line_product_quantity)
8. le total pour ce produit dans une commande (donc le prix du produit multiplié par la quantité de produit(s) acheté(s)) (colonne line_subtotal)
9. le suivi de notification par e-mail de ce nouveau gain de points (colonne user_notified) avec une valeur « no » par défaut. Cette valeur sera modifiée par la date d’envoi de l’e-mail qui l’a notifié de son nouveau gain de points (que l’on développera via un cron)
10. et enfin, le jour/heure de l’ajout du versement de cette commission (colonne time)

Quelles données à stocker pour les usages de points ?

Pour chaque usage de points d’un parrain sur sa commande (comme réduction), nous allons stocker :

1. le type (colonne type) avec une valeur « use »
2. le montant utilisé (colonne amount)
3. l’identifiant utilisateur du parrain (colonne user_id)
4. l’identifiant de la commande (colonne order_id)
5. et enfin, le jour/heure de l’ajout de l’utilisation de ces points (colonne time)

Une table SQL rien qu’à nous dans WordPress

Comment créer notre table SQL custom via WordPress ?

D’abord, le code…

<?php

/**
 * Si inexistante, on créée la table SQL "commissions" après l'activation du thème
 */
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();

$commissions_table_name = $wpdb->prefix . 'commissions';

$commissions_sql = "CREATE TABLE IF NOT EXISTS $commissions_table_name (
	id mediumint(9) NOT NULL AUTO_INCREMENT,
	type varchar(45) DEFAULT NULL,
	amount decimal(10,2) DEFAULT NULL,
	user_id mediumint(9) DEFAULT NULL,
	order_id mediumint(9) DEFAULT NULL,
	line_product_id mediumint(9) DEFAULT NULL,
	line_product_rate decimal(10,2) DEFAULT NULL,
	line_product_quantity mediumint(9) DEFAULT NULL,
	line_subtotal decimal(10,2) DEFAULT NULL,
	user_notified varchar(45) DEFAULT NULL,
	time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
	PRIMARY KEY  (id)
) $charset_collate;";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

dbDelta($commissions_sql);

C’est ce code qui va nous permettre de créer notre table SQL « commissions ».

Si la table wp_commissions n’existe pas, on la créée avec toutes les colonnes décrites précédemment. On assigne à chaque colonne un nom, un type et la valeur par défaut qu’elle doit avoir (ligne 12 à 22).

Afin de la créer dans la même base de données que celle utilisée par notre WordPress, il est nécessaire d’utiliser l’objet $wpdb : on préfixe (ligne 9) notre table avec le même préfixe que les autres tables WordPress (défini dans wp-config.php) et on passe notre requête à la fonction dbDelta (ligne 28).

Comment lire les données de cette table personnalisée ?

Nous terminons cet article avec deux fonctions qui nous seront plus tard vitales :

1. l’une (msk_get_customer_commission_balance()) permet de récupérer des informations sur les points d’un utilisateur, à savoir :
   1. le nombre de points disponibles (la différence entre ses gains et ses dépenses)
   2. le total des points qu’il a gagnés jusqu’à présent
   3. le total des points qu’il a déjà dépensés
2. l’autre (msk_get_customer_commission_data()) permet de récupérer le détails des gains et dépenses d’un utilisateur. Nous nous en servirons à la fin de cette série de tutoriels pour afficher un tableau récapitulatif des gains et dépenses dans l’espace client de WooCommerce

<?php

/**
 * Permet de savoir combien de "points" un utilisateur possède
 * On compte combien de points il a gagné (récompense) et on soustrait le nombre de points qu'il a déjà utilisés (usage)
 */
function msk_get_customer_commission_balance($user_id) {
	global $wpdb;
	$commissions_table_name = $wpdb->prefix . 'commissions';

	$commission_data = $wpdb->get_row(
		$wpdb->prepare("
			SELECT 
			IFNULL(sum(IF(type = %s, amount, 0)), 0) as user_gain,
			IFNULL(sum(IF(type = %s, amount, 0)), 0) as user_use
			FROM $commissions_table_name
			WHERE user_id = %d
			", 
			'gain',
			'use',
			$user_id
		)
	);

	$commission_balance = ($commission_data->user_gain > $commission_data->user_use) ? ($commission_data->user_gain - $commission_data->user_use) : 0;

	return array(
		'balance' => $commission_balance,
		'gain' => $commission_data->user_gain,
		'use' => $commission_data->user_use
	);
}


/**
 * On récupère chaque ligne de commission (récompense ou usage)
 */
function msk_get_customer_commission_data($user_id) {
	global $wpdb;
	$commissions_table_name = $wpdb->prefix . 'commissions';

	$commission = msk_get_customer_commission_balance($user_id);

	$commission_data = $wpdb->get_results(
		$wpdb->prepare("
			SELECT id, type, amount, order_id, line_product_id, line_product_rate, line_product_quantity, time
			FROM $commissions_table_name 
			WHERE user_id = %d 
			ORDER BY time DESC
			",
			$user_id
		)
	);

	return array('points' => $commission, 'details' => $commission_data);
}

Elles ne vous parleront peut-être pas encore mais prendront tout leur sens dans les prochaines articles 😉

A nouveau, nous utilisons l’objet $wpdb qui nous permet de communiquer avec notre table commissions et d’y lire des données.

Dans notre cas, peu de risque d’injection car les données seront fournies par WooCommerce un peu plus tard : mais pour éviter les attaques par injection SQL, il est très important d’utiliser la méthode $wpdb->prepare() qui fonctionne à la manière d’un sprintf() et remplace des subtituts par les valeurs de variables.

Et voila, nous avons maintenant le minimum syndical pour continuer : avec notre table SQL sur-mesure, nous allons pouvoir enregistrer et lire les données des commissions.

Passons à la deuxième étape : la création d’un onglet et des champs (dans le back-office d’un produit WooCommerce) pour stocker les informations relatives aux commissions / prescripteur d’un produit.

A très vite !