function getPlayers($whereClause = [], $metaQuery = [], $search = '', $orderBy = '', $order = '', $limit = 10, $offset = 0, $noLimit = false) {
$db = \Config\Database::connect();
$flagPath = base_url() . 'uploads/logos/';
// Start building the main query
$builder = $db->table('users');
$builder->select( 'users.*,
user_roles.role_name as role_name,
languages.language as language,
domains.domain_name as domain_name,
domains.location as user_location,
auth.secret as email,
packages.title as package_name,
pm.last4 as last4,
pm.brand as brand,
pm.exp_month as exp_month,
pm.exp_year as exp_year,
us.status as subscription_status,
us.plan_period_start as plan_period_start,
us.plan_period_end as plan_period_end,
us.plan_interval as plan_interval,
us.coupon_used as coupon_used,
CASE
WHEN TIMESTAMPDIFF(SECOND, `users`.`last_active`, NOW()) < 60 THEN
CONCAT(TIMESTAMPDIFF(SECOND, `users`.`last_active`, NOW()), " secs ago")
WHEN TIMESTAMPDIFF(MINUTE, `users`.`last_active`, NOW()) < 60 THEN
CONCAT(TIMESTAMPDIFF(MINUTE, `users`.`last_active`, NOW()), " mins ago")
WHEN TIMESTAMPDIFF(HOUR, `users`.`last_active`, NOW()) < 24 THEN
CONCAT(TIMESTAMPDIFF(HOUR, `users`.`last_active`, NOW()), " hours ago")
WHEN TIMESTAMPDIFF(DAY, `users`.`last_active`, NOW()) < 30 THEN
CONCAT(TIMESTAMPDIFF(DAY, `users`.`last_active`, NOW()), " days ago")
WHEN TIMESTAMPDIFF(MONTH, `users`.`last_active`, NOW()) < 12 THEN
CONCAT(TIMESTAMPDIFF(MONTH, `users`.`last_active`, NOW()), " months ago")
ELSE
CONCAT(TIMESTAMPDIFF(YEAR, `users`.`last_active`, NOW()), " years ago")
END AS last_activity_time,
CASE
WHEN TIMESTAMPDIFF(SECOND, `users`.`created_at`, NOW()) < 60 THEN
CONCAT(TIMESTAMPDIFF(SECOND, `users`.`created_at`, NOW()), " secs ago")
WHEN TIMESTAMPDIFF(MINUTE, `users`.`created_at`, NOW()) < 60 THEN
CONCAT(TIMESTAMPDIFF(MINUTE, `users`.`created_at`, NOW()), " mins ago")
WHEN TIMESTAMPDIFF(HOUR, `users`.`created_at`, NOW()) < 24 THEN
CONCAT(TIMESTAMPDIFF(HOUR, `users`.`created_at`, NOW()), " hours ago")
WHEN TIMESTAMPDIFF(DAY, `users`.`created_at`, NOW()) < 30 THEN
CONCAT(TIMESTAMPDIFF(DAY, `users`.`created_at`, NOW()), " days ago")
WHEN TIMESTAMPDIFF(MONTH, `users`.`created_at`, NOW()) < 12 THEN
CONCAT(TIMESTAMPDIFF(MONTH, `users`.`created_at`, NOW()), " months ago")
ELSE
CONCAT(TIMESTAMPDIFF(YEAR, `users`.`created_at`, NOW()), " years ago")
END AS registration_time,
JSON_ARRAYAGG(
JSON_OBJECT(
"country_name", c.country_name,
"flag_path", CONCAT("'.$flagPath.'", c.country_flag)
)
) AS user_nationalities
');
$builder->join('(SELECT user_id, secret FROM auth_identities WHERE type = "email_password") auth', 'auth.user_id = users.id', 'LEFT');
$builder->join('user_roles', 'user_roles.id = users.role', 'INNER');
$builder->join('languages', 'languages.id = users.lang', 'INNER');
$builder->join('domains', 'domains.id = users.user_domain', 'INNER');
$builder->join('user_nationalities un', 'un.user_id = users.id', 'LEFT');
$builder->join('countries c', 'c.id = un.country_id', 'LEFT');
$builder->join('(SELECT user_id, brand, exp_month, exp_year, last4 FROM payment_methods WHERE is_default = 1) pm', 'pm.user_id = users.id', 'LEFT');
// START: membership check
if (isset($whereClause['membership'])){
if($whereClause['membership'] == 'paid'){
$builder->join('(SELECT id as max_id, user_id, status, package_id, plan_period_start, plan_period_end, plan_interval, coupon_used FROM user_subscriptions WHERE id IN (SELECT MAX(id) as max_id FROM user_subscriptions GROUP BY user_id) AND status = "active") us', 'us.user_id = users.id', 'INNER');
} else {
$builder->join('user_subscriptions us', 'us.user_id = users.id', 'LEFT');
}
} else {
$builder->join('(SELECT id as max_id, user_id, status, package_id, plan_period_start, plan_period_end, plan_interval, coupon_used FROM user_subscriptions WHERE id IN (SELECT MAX(id) as max_id FROM user_subscriptions GROUP BY user_id)) us', 'us.user_id = users.id', 'LEFT');
}
// END: membership check
$builder->join('packages', 'packages.id = us.package_id', 'LEFT');
$builder->where('users.deleted_at', NULL);
// Build the query with the same conditions
if (isset($whereClause) && count($whereClause) > 0) {
foreach ($whereClause as $key => $value) {
if ($key == 'membership') {
continue;
} elseif ($key == 'email') {
$builder->like("auth.secret", $value);
} elseif ($key == 'location') {
$builder->like("domains.id", $value);
} /*elseif ($key == 'last_active') {
$builder->where("users.last_active >= ", ( CURDATE() " - INTERVAL". $value ));
} */ else {
$builder->where("users.$key", $value);
}
}
}
if (isset($metaQuery) && count($metaQuery) > 0) {
foreach ($metaQuery as $meta) {
$metaBuilder = $db->table('user_meta');
$metaBuilder->select('user_id')
->where('meta_key', $meta['meta_key']);
$metaValue = is_array($meta['meta_value']) ? $meta['meta_value'] : (is_numeric($meta['meta_value']) ? (int)$meta['meta_value'] : $meta['meta_value']);
if ($meta['operator'] == "=") {
$metaBuilder->where('meta_value', $metaValue);
} elseif ($meta['operator'] == ">=") {
$metaBuilder->where('meta_value >=', $metaValue);
} elseif ($meta['operator'] == "<=") {
$metaBuilder->where('meta_value <=', $metaValue);
} elseif ($meta['operator'] == "IN") {
$metaBuilder->whereIn('meta_value', $metaValue);
} else {
$metaBuilder->like('meta_value', $metaValue);
}
$metaSubquery = $metaBuilder->getCompiledSelect();
$builder->where("users.id IN ($metaSubquery)");
}
}
// START: membership check
// check only users with free membership
if (isset($whereClause['membership']) && $whereClause['membership'] == 'free'){
$builder->where("us.user_id", NULL);
}
// END: membership check
if (isset($search) && !empty($search)) {
$builder->groupStart();
$builder->orLike('users.username', $search);
$builder->orLike('users.first_name', $search);
$builder->orLike('users.last_name', $search);
$builder->orLike('auth.secret', $search);
$builder->groupEnd();
}
$builder->groupBy('users.id');
// Count the total number of results
$countBuilder = $db->newQuery()->select('count(*) as total_count')->fromSubquery($builder, 'counter_table');
$countQuery = $countBuilder->get();
$totalCount = $countQuery->getRow()->total_count;
// Add Sorting
if (!empty($orderBy)) {
$builder->orderBy($orderBy, $order);
} else {
$builder->orderBy('id', 'DESC');
}
// Add pagination
if ($noLimit != true) {
$builder->limit($limit, $offset);
}
// Execute the main query
$query = $builder->get();
// echo '>>>>>>>> getLastQuery >>>>> '. $db->getLastQuery(); exit;
$result = $query->getResultArray();
// Process the results to merge metadata into user objects
$users = [];
foreach ($result as $row) {
$userId = $row['id'];
if (!isset($users[$userId])) {
$users[$userId] = [
'id' => $row['id'],
'username' => $row['username'],
'email' => $row['email'],
'created_at' => $row['created_at'],
'updated_at' => $row['updated_at'],
'first_name' => $row['first_name'],
'last_name' => $row['last_name'],
'role_name' => $row['role_name'],
'language' => $row['language'],
'newsletter' => $row['newsletter'],
'user_domain' => $row['domain_name'],
'user_location' => $row['user_location'],
'last_active' => $row['last_active'],
'status' => $row['status'],
'last4' => $row['last4'],
'brand' => $row['brand'],
'exp_month' => $row['exp_month'],
'exp_year' => $row['exp_year'],
'package_name' => $row['package_name'],
'subscription_status' => $row['subscription_status'],
'plan_period_start' => $row['plan_period_start'],
'plan_period_end' => $row['plan_period_end'],
'plan_interval' => $row['plan_interval'],
'coupon_used' => $row['coupon_used'],
'last_activity_time' => $row['last_activity_time'],
'user_nationalities' => $row['user_nationalities'],
'registration_time' => $row['registration_time'],
'meta' => []
];
}
}
$imagePath = base_url() . 'uploads/';
// Get all meta data for each user
$userIds = array_keys($users);
if (!empty($userIds)) {
$metaBuilder = $db->table('user_meta');
$metaBuilder->whereIn('user_id', $userIds);
$metaQuery = $metaBuilder->get();
$metaResult = $metaQuery->getResultArray();
foreach ($metaResult as $metaRow) {
//pr($metaRow);
$userId = $metaRow['user_id'];
$users[$userId]['meta'][$metaRow['meta_key']] = $metaRow['meta_value'];
if($metaRow['meta_key'] == 'profile_image'){
$users[$userId]['meta']['profile_image_path'] = $imagePath.$metaRow['meta_value'];
}
if($metaRow['meta_key'] == 'cover_image'){
$users[$userId]['meta']['cover_image_path'] = $imagePath.$metaRow['meta_value'];
}
}
}
$userData = array_values($users);
return [
'totalCount' => $totalCount,
'users' => $userData
];
}
