<?php
/**
 * Sort meta-field company for PMPro member directory
 *
 * @param  [type] $sqlQuery [description]
 * @param  [type] $levels   [description]
 * @param  [type] $s        [description]
 * @param  [type] $pn       [description]
 * @param  [type] $limit    [description]
 * @param  [type] $start    [description]
 * @param  [type] $end      [description]
 * @param  [type] $order_by [description]
 * @param  [type] $order    [description]
 *
 * @return [type]           [description]
 */
function my_pmpro_member_directory_sql( $sqlQuery, $levels, $s, $pn, $limit, $start, $end, $order_by, $order ) {
	  global $wpdb;

	if ( $s ) {
		$sqlQuery =

		"SELECT SQL_CALC_FOUND_ROWS
		  u.ID,
		  u.user_login,
		  u.user_email,
		  u.user_nicename,
		  u.display_name,
		  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,
		  umf.meta_value as first_name,
		  uml.meta_value as last_name,
		  umz.meta_value as company

		  FROM $wpdb->users u

		  LEFT JOIN $wpdb->usermeta umh ON umh.meta_key = 'pmpromd_hide_directory' AND u.ID = umh.user_id
		  LEFT JOIN $wpdb->usermeta umf ON umf.meta_key = 'first_name' AND u.ID = umf.user_id
		  LEFT JOIN $wpdb->usermeta uml ON uml.meta_key = 'last_name' AND u.ID = uml.user_id
		  LEFT JOIN $wpdb->usermeta umz ON umz.meta_key = 'company' AND u.ID = umz.user_id
		  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

		  WHERE mu.status = 'active'
		  AND (umh.meta_value IS NULL OR umh.meta_value <> '1')
		  AND mu.membership_id > 0 AND ";

		$sqlQuery .= "(u.user_login LIKE '%" . esc_sql( $s ) . "%' OR u.user_email LIKE '%" . esc_sql( $s ) . "%' OR u.display_name LIKE '%" . esc_sql( $s ) . "%' OR um.meta_value LIKE '%" . esc_sql( $s ) . "%') ";
		if ( $levels ) {
			$sqlQuery .= ' AND mu.membership_id IN(' . esc_sql( $levels ) . ') ';
		}
		$sqlQuery .= 'GROUP BY u.ID ORDER BY ' . esc_sql( $order_by ) . ' ' . $order;
	} else {
		$sqlQuery =

		"SELECT SQL_CALC_FOUND_ROWS u.ID,
		  u.user_login,
		  u.user_email,
		  u.user_nicename,
		  u.display_name,
		  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,
		  umf.meta_value as first_name,
		  uml.meta_value as last_name,
		  umz.meta_value as company


		  FROM $wpdb->users u

		  LEFT JOIN $wpdb->usermeta umh ON umh.meta_key = 'pmpromd_hide_directory' AND u.ID = umh.user_id
		  LEFT JOIN $wpdb->usermeta umf ON umf.meta_key = 'first_name' AND u.ID = umf.user_id
		  LEFT JOIN $wpdb->usermeta uml ON uml.meta_key = 'last_name' AND u.ID = uml.user_id
		  LEFT JOIN $wpdb->usermeta umz ON umz.meta_key = 'company' AND u.ID = umz.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";

		$sqlQuery .= "
		WHERE mu.status = 'active'
		AND (umh.meta_value IS NULL OR umh.meta_value <> '1')
		AND mu.membership_id > 0 ";
		if ( $levels ) {
			$sqlQuery .= ' AND mu.membership_id IN(' . esc_sql( $levels ) . ') ';
		}
		$sqlQuery .= 'ORDER BY ' . esc_sql( $order_by ) . ' ' . esc_sql( $order );
	}
	  $sqlQuery .= " LIMIT $start, $limit";

	  return $sqlQuery;
}

  add_filter( 'pmpro_member_directory_sql', 'my_pmpro_member_directory_sql', 10, 9 );