Sort Members by enddate

pmpro-sort-members-by-enddate.php

function my_custom_memberslist_order($sqlQuery)
{
	echo "<a href=".admin_url('admin.php?page=pmpro-memberslist&enddate_sort=asc').">Sort by Enddate ASC</a><br>".
		"<a href=".admin_url('admin.php?page=pmpro-memberslist&enddate_sort=desc').">Sort by Enddate DESC</a>";
	
	if(isset($_REQUEST['enddate_sort']) && ($_REQUEST['enddate_sort'] == 'asc' || $_REQUEST['enddate_sort'] == 'desc'))
	{
		$sort_order = $_REQUEST['enddate_sort'];
		
		global $wpdb;
		if(isset($_REQUEST['s']))
			$s = sanitize_text_field(trim($_REQUEST['s']));
		else
			$s = "";

		if(isset($_REQUEST['l']))
			$l = sanitize_text_field($_REQUEST['l']);
		else
			$l = false;	
	
		//some vars for the search
		if(isset($_REQUEST['pn']))
			$pn = intval($_REQUEST['pn']);
		else
			$pn = 1;

		if(isset($_REQUEST['limit']))
			$limit = intval($_REQUEST['limit']);
		else
		{
			/**
		 	* Filter to set the default number of items to show per page
		 	* on the Members List page in the admin.
		 	*
		 	* @since 1.8.4.5
		 	*
		 	* @param int $limit The number of items to show per page.
		 	*/
			$limit = apply_filters('pmpro_memberslist_per_page', 15);
		}

		$end = $pn * $limit;
		$start = $end - $limit;
	
		if($s)
		{
			$sqlQuery = "SELECT SQL_CALC_FOUND_ROWS u.ID, u.user_login, u.user_email, UNIX_TIMESTAMP(u.user_registered) as joindate, mu.membership_id, mu.initial_payment, mu.billing_amount, mu.cycle_period, mu.cycle_number, mu.billing_limit, mu.trial_amount, mu.trial_limit, UNIX_TIMESTAMP(mu.startdate) as startdate, UNIX_TIMESTAMP(mu.enddate) as enddate, m.name as membership FROM $wpdb->users u LEFT JOIN $wpdb->usermeta um ON u.ID = um.user_id LEFT JOIN $wpdb->pmpro_memberships_users mu ON u.ID = mu.user_id LEFT JOIN $wpdb->pmpro_membership_levels m ON mu.membership_id = m.id ";

			if($l == "oldmembers" || $l == "expired" || $l == "cancelled")
				$sqlQuery .= " LEFT JOIN $wpdb->pmpro_memberships_users mu2 ON u.ID = mu2.user_id AND mu2.status = 'active' ";

			$sqlQuery .= " WHERE mu.membership_id > 0 AND (u.user_login LIKE '%" . esc_sql($s) . "%' OR u.user_email LIKE '%" . esc_sql($s) . "%' OR um.meta_value LIKE '%" . esc_sql($s) . "%') AND mu.enddate != 0";

			if($l == "oldmembers")
				$sqlQuery .= " AND mu.status <> 'active' AND mu2.status IS NULL ";
			elseif($l == "expired")
				$sqlQuery .= " AND mu.status = 'expired' AND mu2.status IS NULL ";
			elseif($l == "cancelled")
				$sqlQuery .= " AND mu.status IN('cancelled', 'admin_cancelled') AND mu2.status IS NULL ";
			elseif($l)
				$sqlQuery .= " AND mu.status = 'active' AND mu.membership_id = '" . esc_sql($l) . "' ";
			else
				$sqlQuery .= " AND mu.status = 'active' ";

			$sqlQuery .= "GROUP BY u.ID ";

			if($sort_order == 'asc')
				$sqlQuery .= "ORDER BY enddate ASC "; 
		
			elseif($sort_order == 'desc') 
				$sqlQuery .= "ORDER BY enddate DESC "; 
		
			$sqlQuery .= "LIMIT $start, $limit";
		}
		else
		{
			$sqlQuery = "SELECT SQL_CALC_FOUND_ROWS u.ID, u.user_login, u.user_email, UNIX_TIMESTAMP(u.user_registered) as joindate, mu.membership_id, mu.initial_payment, mu.billing_amount, mu.cycle_period, mu.cycle_number, mu.billing_limit, mu.trial_amount, mu.trial_limit, UNIX_TIMESTAMP(mu.startdate) as startdate, UNIX_TIMESTAMP(mu.enddate) as enddate, m.name as membership FROM $wpdb->users u LEFT JOIN $wpdb->pmpro_memberships_users mu ON u.ID = mu.user_id LEFT JOIN $wpdb->pmpro_membership_levels m ON mu.membership_id = m.id ";

			if($l == "oldmembers" || $l == "expired" || $l == "cancelled")
				$sqlQuery .= " LEFT JOIN $wpdb->pmpro_memberships_users mu2 ON u.ID = mu2.user_id AND mu2.status = 'active' ";

			$sqlQuery .= " WHERE mu.membership_id > 0 AND mu.enddate != 0 ";

			if($l == "oldmembers")
				$sqlQuery .= " AND mu.status <> 'active' AND mu2.status IS NULL ";
			elseif($l == "expired")
				$sqlQuery .= " AND mu.status = 'expired' AND mu2.status IS NULL ";
			elseif($l == "cancelled")
				$sqlQuery .= " AND mu.status IN('cancelled', 'admin_cancelled') AND mu2.status IS NULL ";
			elseif($l)
				$sqlQuery .= " AND mu.status = 'active' AND mu.membership_id = '" . esc_sql($l) . "' ";
			else
				$sqlQuery .= " AND mu.status = 'active' ";
		
			$sqlQuery .= "GROUP BY u.ID ";

			if($sort_order == 'asc')
				$sqlQuery .= " ORDER BY enddate ASC "; 
		
			elseif($sort_order == 'desc') 
				$sqlQuery .= " ORDER BY enddate DESC ";

			$sqlQuery .= "LIMIT $start, $limit";
		}
	}
	
	return $sqlQuery;
}

add_filter('pmpro_members_list_sql', 'my_custom_memberslist_order', 10, 1);