Symfony Profiler

<?php
namespace App\Model;
use Pimcore\Db;
use DateTime;
use Pimcore\Model\DataObject\Organization;
use Pimcore\Model\DataObject\Customer;
use Pimcore\Model\DataObject\Location;
use Pimcore\Model\DataObject\MannedAlertSubscription;
use Pimcore\Model\DataObject\MannedAlertLog;
use Pimcore\Model\DataObject\CustomNotification;
use Doctrine\DBAL\Query\QueryBuilder;
use Knp\Component\Pager\PaginatorInterface;
use Doctrine\DBAL\Connection;
use Pimcore\Model\DataObject;
use Carbon\Carbon;
class UserDashboardModel
{
private Connection $db;
private PaginatorInterface $paginator;
public function __construct(PaginatorInterface $paginator)
{
$this->db = Db::get();
$this->paginator = $paginator;
}
public function fetchTotalUsers(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
?string $role = null, // ← new
?int $isPublished = null,
int $page = 1,
int $pageSize = 20
): array {
$qb = $this->db->createQueryBuilder()
->select([
'rtu.organization_id',
'rtu.user_id',
'u.name AS user_name',
'u.email AS user_email',
'r.name AS user_role',
'rtu.created_at',
'rtu.updated_at',
'obj.o_published'
])
->from('reporting_total_user', 'rtu')
->leftJoin('rtu', 'object_query_customer', 'u', 'u.oo_id = rtu.user_id')
->leftJoin('u', 'object_query_user_role', 'r', 'r.oo_id = u.role__id')
->leftJoin('rtu', 'objects', 'obj', 'obj.o_id = rtu.user_id');
$this->applyCommonFilters($qb, $organizationId, $userId, $from, $to, 'updated_at');
if ($search) {
$expr = $qb->expr();
$qb->andWhere($expr->orX(
$expr->like('u.name', ':search'),
$expr->like('u.email', ':search'),
$expr->like('r.name', ':search')
))
->setParameter('search', "%{$search}%");
}
if ($role !== null) {
$qb->andWhere('r.name = :role')
->setParameter('role', $role);
}
if ($isPublished !== null) {
$qb->andWhere('obj.o_published = :published')
->setParameter('published', $isPublished);
}
$allowed = [
'organization_id',
'user_id',
'user_name',
'user_email',
'user_role',
'created_at',
'updated_at'
];
if ($sortBy && in_array($sortBy, $allowed, true)) {
$qb->orderBy($sortBy, strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC');
}
// ─── KNP pagination ───────────────────────────────────────────────────────
$pagination = $this->paginator->paginate(
$qb, // the QueryBuilder
$page, // current page number
$pageSize // items per page
);
$rows = $pagination->getItems(); // the slice of items
$totalItems = $pagination->getTotalItemCount(); // total across all pages
$perPage = $pagination->getItemNumberPerPage(); // your $pageSize
$current = $pagination->getCurrentPageNumber(); // your $page
// hydrate your data exactly as before
$data = [];
foreach ($rows as $r) {
$org = Organization::getById($r['organization_id']);
$cust = \Pimcore\Model\DataObject::getById($r['user_id']);
$subscriptionListing = new \Pimcore\Model\DataObject\Subscription\Listing();
//$subscriptionListing->setCondition('endDate >= ?', [time()]);
$subscriptionListing->filterBySubscriptionType("custom");
//$subscriptionListing->filterByIsActive(true);
$subscriptionListing->filterBySubscribedUser($cust);
$subscription = $subscriptionListing->current();
$data[] = [
'id' => $r['user_id'],
'name' => $r['user_name'],
'email' => $r['user_email'],
'role' => $r['user_role'],
'isPublished' => Customer::getById($r['user_id'])?->getPublished(),
'organization_id' => $r['organization_id'],
'created_at' => $r['created_at'],
'updated_at' => $r['updated_at'],
'is_published' => $cust->getPublished() ? true : false,
'user_status' => $cust->getPublished() ? "active" : "pending",
'package_name' => ($subscription) ? $subscription->getSubscribedPackage()->getPackageName() : "",
'subscription_start_date' => ($subscription) ? $subscription->getStartDate()->format("Y-m-d") : "",
'subscription_end_date' => ($subscription) ? $subscription->getEndDate()->format("Y-m-d") : "",
];
}
return [
'success' => true,
'data' => $data,
'paginationVariables' => $pagination->getPaginationData(),
];
}
public function fetchActiveUsers(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
?string $role = null, // ← new
int $page = 1,
int $pageSize = 20
): array {
$qb = $this->db->createQueryBuilder()
->select([
'rau.organization_id',
'rau.user_id',
'u.name AS user_name',
'u.email AS user_email',
'r.name AS user_role',
'rau.created_at',
'rau.updated_at'
])
->from('reporting_active_user', 'rau')
->leftJoin('rau', 'object_query_customer', 'u', 'u.oo_id = rau.user_id')
->leftJoin('u', 'object_query_user_role', 'r', 'r.oo_id = u.role__id');
$this->applyCommonFilters($qb, $organizationId, $userId, $from, $to, 'created_at');
if ($search) {
$expr = $qb->expr();
$qb->andWhere($expr->orX(
$expr->like('u.name', ':search'),
$expr->like('u.email', ':search'),
$expr->like('r.name', ':search')
))
->setParameter('search', "%{$search}%");
}
if ($role !== null) {
$qb->andWhere('r.name = :role')
->setParameter('role', $role);
}
$allowed = [
'organization_id',
'user_id',
'user_name',
'user_email',
'user_role',
'created_at',
'updated_at'
];
if ($sortBy && in_array($sortBy, $allowed, true)) {
$qb->orderBy($sortBy, strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC');
}
// KNP pagination
$pagination = $this->paginator->paginate($qb, $page, $pageSize);
$rows = $pagination->getItems();
$totalItems = $pagination->getTotalItemCount();
$perPage = $pagination->getItemNumberPerPage();
$current = $pagination->getCurrentPageNumber();
$data = [];
foreach ($rows as $r) {
$org = Organization::getById($r['organization_id']);
$data[] = [
'id' => $r['user_id'],
'name' => $r['user_name'],
'email' => $r['user_email'],
'role' => $r['user_role'],
'isPublished' => Customer::getById($r['user_id'])?->getPublished(),
'organization_id' => $r['organization_id'],
'created_at' => $r['created_at'],
'updated_at' => $r['updated_at'],
];
}
return [
'success' => true,
'data' => $data,
'paginationVariables' => $pagination->getPaginationData(),
];
}
public function fetchTotalLocations(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
int $page = 1,
int $pageSize = 20
): array {
$qb = $this->db->createQueryBuilder()
->select([
'rl.organization_id',
'rl.user_id',
'rl.location_id',
'l.name AS name_en',
'l.title AS name_ar',
'u.name AS user_name',
'u.email AS user_email',
'r.name AS user_role',
'rl.created_at',
'rl.updated_at'
])
->from('reporting_total_location', 'rl')
->leftJoin('rl', 'object_query_location', 'l', 'l.oo_id = rl.location_id')
->leftJoin('rl', 'object_query_customer', 'u', 'u.oo_id = rl.user_id')
->leftJoin('u', 'object_query_user_role', 'r', 'r.oo_id = u.role__id');
$this->applyCommonFilters($qb, $organizationId, $userId, $from, $to, 'updated_at');
if ($search) {
$expr = $qb->expr();
$qb->andWhere($expr->orX(
$expr->like('l.name', ':search'),
$expr->like('l.title', ':search'),
$expr->like('u.name', ':search'),
$expr->like('u.email', ':search'),
$expr->like('r.name', ':search')
))
->setParameter('search', "%{$search}%");
}
$allowed = [
'organization_id',
'user_id',
'location_id',
'name_en',
'name_ar',
'user_name',
'user_email',
'user_role',
'created_at',
'updated_at'
];
if ($sortBy && in_array($sortBy, $allowed, true)) {
$qb->orderBy($sortBy, strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC');
}
// KNP pagination
$pagination = $this->paginator->paginate($qb, $page, $pageSize);
$rows = $pagination->getItems();
$totalItems = $pagination->getTotalItemCount();
$perPage = $pagination->getItemNumberPerPage();
$current = $pagination->getCurrentPageNumber();
$data = [];
foreach ($rows as $r) {
// $org = Organization::getById($r['organization_id']);
// $cust = Customer::getById($r['user_id']);
$data[] = [
'id' => $r['location_id'],
'name_en' => $r['name_en'],
'name_ar' => $r['name_ar'],
'isPublished' => \Pimcore\Model\DataObject\Location::getById($r['location_id'])?->getPublished(),
'created_at' => $r['created_at'],
'updated_at' => $r['updated_at'],
];
}
return [
'success' => true,
'data' => $data,
'paginationVariables' => $pagination->getPaginationData(),
];
}
public function fetchEwsSubscriptions(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
int $page = 1,
int $pageSize = 20,
string $lang = 'en', // Default to English
bool $isSubscribed = null
): array {
$qb = $this->db->createQueryBuilder()
->select([
's.oo_id AS subscription_id',
's.isSubscribed AS is_subscribed',
'u.name AS user_name',
'u.email AS user_email',
'r.name AS user_role',
'region_en.name AS region_name_en',
'region_ar.name AS region_name_ar',
'FROM_UNIXTIME(s.o_creationDate) AS created_at',
'FROM_UNIXTIME(s.o_modificationDate) AS updated_at'
])
->from('object_MannedAlertSubscription', 's')
->leftJoin('s', 'object_query_customer', 'u', 'u.oo_id = s.creator__id')
->leftJoin('u', 'object_query_user_role', 'r', 'r.oo_id = u.role__id')
->leftJoin('s', 'object_localized_query_region_en', 'region_en', 'region_en.ooo_id = s.region__id')
->leftJoin('s', 'object_localized_query_region_ar', 'region_ar', 'region_ar.ooo_id = s.region__id');
// Filter by userId if provided
if ($userId !== null) {
$qb->andWhere('u.oo_id = :usr')
->setParameter('usr', $userId);
}
if ($isSubscribed !== null) {
$qb->andWhere('s.isSubscribed = :subscribed')
->setParameter('subscribed', $isSubscribed);
}
// Date filter: from/to on o_creationDate (UNIX timestamp)
if ($from) {
$fromTs = (new \DateTime($from))->setTime(0, 0, 0)->getTimestamp();
$qb->andWhere('s.o_creationDate >= :from')
->setParameter('from', $fromTs);
}
if ($to) {
$toTs = (new \DateTime($to))->setTime(23, 59, 59)->getTimestamp();
$qb->andWhere('s.o_creationDate <= :to')
->setParameter('to', $toTs);
}
if ($search) {
$expr = $qb->expr();
$qb->andWhere($expr->orX(
$expr->like('u.name', ':search'),
$expr->like('u.email', ':search'),
$expr->like('r.name', ':search'),
$lang == 'en'
? $expr->like('region_en.name', ':search')
: $expr->like('region_ar.name', ':search')
))
->setParameter('search', "%{$search}%");
}
$allowed = ['subscription_id', 'user_name', 'user_email', 'user_role', 'created_at', 'updated_at', 'region_name_en', 'region_name_ar'];
if ($sortBy && in_array($sortBy, $allowed, true)) {
$qb->orderBy($sortBy, strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC');
}
// KNP pagination
$pagination = $this->paginator->paginate($qb, $page, $pageSize);
$rows = $pagination->getItems();
$totalItems = $pagination->getTotalItemCount();
$perPage = $pagination->getItemNumberPerPage();
$current = $pagination->getCurrentPageNumber();
$data = [];
foreach ($rows as $r) {
$sub = MannedAlertSubscription::getById((int)$r['subscription_id']);
if (!$sub) {
continue;
}
// … your existing region/alertType/governorate/phenomena hydration …
$phenomenas = [];
if ($sub->getAlertStatus()) {
foreach ($sub->getAlertStatus() as $subscriptionAlertStatus) {
$phenomenas[] = [
"id" => $subscriptionAlertStatus->getAlertStatusId(),
"en" => $subscriptionAlertStatus->getName('en'),
"ar" => $subscriptionAlertStatus->getName('ar')
];
}
}
$data[] = [
'id' => (int)$r['subscription_id'],
'name' => $r['user_name'],
'email' => $r['user_email'],
'role' => $r['user_role'],
'is_subscribed' => (bool) $r['is_subscribed'],
'region_name_en' => $r['region_name_en'],
'region_name_ar' => $r['region_name_ar'],
'alert_status' => $phenomenas,
'created_at' => $r['created_at'],
'updated_at' => $r['updated_at'],
];
}
return [
'success' => true,
'data' => $data,
'paginationVariables' => $pagination->getPaginationData(),
];
}
public function fetchReceivedEmails(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
int $page = 1,
int $pageSize = 20
): array {
// 1) Build base query and apply filters
$qb = $this->buildBaseQuery('reporting_received_emails');
$this->applyCommonFilters($qb, $organizationId, $userId, $from, $to, 'sent_date');
// 2) (Optional) search across 'sent_to' or any other columns
if ($search) {
$expr = $qb->expr();
$qb->andWhere($expr->like('sent_to', ':search'))
->setParameter('search', "%{$search}%");
}
// 3) KNP pagination
$pagination = $this->paginator->paginate($qb, $page, $pageSize);
$rows = $pagination->getItems();
$totalItems = $pagination->getTotalItemCount();
$perPage = $pagination->getItemNumberPerPage();
$current = $pagination->getCurrentPageNumber();
// 4) Hydrate data
$data = [];
foreach ($rows as $r) {
$log = MannedAlertLog::getById($r['log_id']);
$data[] = [
'log_id' => $r['log_id'],
'classType' => $log?->getClassType(),
'purpose' => $log?->getPurpose(),
'sent_to' => $r['sent_to'],
'sent_date' => $r['sent_date'],
];
}
// 5) Return with full pagination block
return [
'success' => true,
'data' => $data,
'paginationVariables' => $pagination->getPaginationData(),
];
}
public function fetchCustomNotifications(
?int $organizationId,
?int $userId,
?int $type,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
int $page = 1,
int $pageSize = 20
): array {
// 1) Build query with joins
$qb = $this->db->createQueryBuilder()
->select([
'rcn.organization_id',
'rcn.user_id',
'rcn.notification_id',
'rcn.type',
'cn.title',
'cn.color',
'cn.units',
'cn.minValue',
'cn.maxValue',
'u.name AS user_name',
'u.email AS user_email',
'r.name AS user_role',
'rcn.created_at'
])
->from('reporting_custom_notification', 'rcn')
->leftJoin('rcn', 'object_query_custom_notification', 'cn', 'cn.oo_id = rcn.notification_id')
->leftJoin('rcn', 'object_query_customer', 'u', 'u.oo_id = rcn.user_id')
->leftJoin('u', 'object_query_user_role', 'r', 'r.oo_id = u.role__id');
// 2) Apply common org/user/date filters
$this->applyCommonFilters($qb, $organizationId, $userId, $from, $to, 'created_at');
// 3) Optional type filter: 1 = Basic, 2 = Advance
if ($type !== null) {
$qb->andWhere('rcn.type = :type')
->setParameter('type', $type);
}
// 4) Optional search
if ($search) {
$expr = $qb->expr();
$qb->andWhere($expr->orX(
$expr->like('cn.title', ':search'),
$expr->like('u.name', ':search'),
$expr->like('u.email', ':search'),
$expr->like('r.name', ':search')
))
->setParameter('search', "%{$search}%");
}
// 5) Sorting
$allowed = [
'organization_id',
'user_id',
'notification_id',
'title',
'color',
'units',
'minValue',
'maxValue',
'user_name',
'user_email',
'user_role',
'created_at',
'type'
];
if ($sortBy && in_array($sortBy, $allowed, true)) {
$qb->orderBy($sortBy, strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC');
}
// 6) Pagination
$pagination = $this->paginator->paginate($qb, $page, $pageSize);
$rows = $pagination->getItems();
// 7) Hydrate
$data = [];
foreach ($rows as $r) {
$data[] = [
'id' => $r['notification_id'],
'title' => $r['title'],
'color' => $r['color'],
'units' => $r['units'],
'minValue' => $r['minValue'],
'maxValue' => $r['maxValue'],
'created_at' => $r['created_at'],
'type' => isset($r['type']) ? $r['type'] : null,
];
}
return [
'success' => true,
'data' => $data,
'paginationVariables' => $pagination->getPaginationData(),
];
}
public function fetchAddedLocations(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search,
int $page = 1,
int $pageSize = 20
): array {
return $this->fetchTotalLocations($organizationId, $userId, $from, $to, $page, $pageSize);
}
private function buildBaseQuery(string $table): QueryBuilder
{
return $this->db->createQueryBuilder()
->select('*')
->from($table);
}
/**
* Fetch per‐user login details: summary + paginated list.
*
* @return array{
* success: bool,
* summary: array{ totalAttempts: int, successAttempts: int, failureAttempts: int, lastLogin: string|null },
* count: int,
* data: array<int, array{ id: int, user: array, organization: array, status: int, attempt_time: string, ip_address: string|null, user_agent: string|null }>
* }
*/
public function fetchUserLoginDetails(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search,
int $page = 1,
int $pageSize = 20
): array {
// Use a COLLATE on the join to force both sides to utf8mb4_unicode_ci
$joinCondition = "ua.customer_email COLLATE utf8mb4_unicode_ci = c.email";
// 1) Summary
$summaryQb = $this->db->createQueryBuilder()
->select(
'COUNT(*) AS total',
'SUM(CASE WHEN ua.status = 1 THEN 1 ELSE 0 END) AS success',
'SUM(CASE WHEN ua.status = 0 THEN 1 ELSE 0 END) AS failure',
'MAX(CASE WHEN ua.status = 1 THEN ua.attempt_time ELSE NULL END) AS last_login',
'MAX(CASE WHEN ua.status = 0 THEN ua.attempt_time ELSE NULL END) AS last_failed_login' // Added for last failed login
)
->from('user_login_attempts', 'ua')
->leftJoin('ua', 'object_query_customer', 'c', $joinCondition);
$this->applyLoginFilters($summaryQb, $organizationId, $userId, $from, $to);
$summaryRow = $summaryQb->execute()->fetchAssociative();
// 2) Summary
$emailDataQb = $this->db->createQueryBuilder()
->select(
'COUNT(id) AS totalEmailReceived',
'SUM(CASE WHEN re.email_type = "Customer" THEN 1 ELSE 0 END) AS totalOtpEmailReceived',
'SUM(CASE WHEN re.email_type = "CustomNotification" THEN 1 ELSE 0 END) AS totalBasicCustomNotificationEmailReceived',
'SUM(CASE WHEN re.email_type = "AdvanceCustomNotification" THEN 1 ELSE 0 END) AS totalAdvanceCustomNotificationEmailReceived',
'SUM(CASE WHEN re.email_type = "EwsNotification" THEN 1 ELSE 0 END) AS totalEwsAlertEmailReceived'
)
->from('reporting_received_emails', 're') // Reporting received emails table
->where('re.user_id = :userId');
$emailDataQb->setParameter('userId', $userId);
// Execute the query
$emailDataSummary = $emailDataQb->execute()->fetchAssociative();
// 2) Detail query
$detailQb = $this->db->createQueryBuilder()
->select(
'ua.id',
'ua.customer_email',
'c.oo_id AS user_id',
'c.organization__id AS organization_id',
'ua.status',
'ua.attempt_time',
'ua.ip_address',
'ua.user_agent'
)
->from('user_login_attempts', 'ua')
->leftJoin('ua', 'object_query_customer', 'c', $joinCondition);
$this->applyLoginFilters($detailQb, $organizationId, $userId, $from, $to);
// 3) KNP pagination
$pagination = $this->paginator->paginate($detailQb, $page, $pageSize);
$rows = $pagination->getItems();
$totalItems = $pagination->getTotalItemCount();
$perPage = $pagination->getItemNumberPerPage();
$currentPage = $pagination->getCurrentPageNumber();
// 4) Hydrate
$data = [];
$data["summary"] = [
'totalAttempts' => (int)$summaryRow['total'],
'successAttempts' => (int)$summaryRow['success'],
'failureAttempts' => (int)$summaryRow['failure'],
'lastLogin' => $summaryRow['last_login'],
'lastFailedLogin' => $summaryRow['last_failed_login'], // Added for last failed login
'totalEmailReceived' => (int) $emailDataSummary['totalEmailReceived'] ?? 0,
'totalOtpEmailReceived' => (int) $emailDataSummary['totalOtpEmailReceived'] ?? 0,
'totalBasicCustomNotificationEmailReceived' => (int) $emailDataSummary['totalBasicCustomNotificationEmailReceived'] ?? 0,
'totalAdvanceCustomNotificationEmailReceived' => (int) $emailDataSummary['totalAdvanceCustomNotificationEmailReceived'] ?? 0,
'totalEwsAlertEmailReceived' => (int) $emailDataSummary['totalEwsAlertEmailReceived'] ?? 0
];
$cust = \Pimcore\Model\DataObject\Customer::getById($userId);
if (!$cust) {
return [
'success' => false,
'message' => "User not found"
];
}
$org = $cust->getOrganization();
$subscriptionListing = new \Pimcore\Model\DataObject\Subscription\Listing();
// $subscriptionListing->setCondition('endDate >= ?', [time()]);
$subscriptionListing->filterBySubscriptionType("custom");
// $subscriptionListing->filterByIsActive(true);
$subscriptionListing->filterBySubscribedUser($cust);
$subscription = $subscriptionListing->current();
$data["user"] = [
'id' => $cust->getId(),
'email' => $cust->getEmail(),
'name' => $cust?->getName(),
'role' => $cust?->getRole()?->getName(),
'is_active' => $cust->getPublished() ? true : false,
'is_expired' => ($subscription) ? false : true,
'is_pending' => !$cust->getPublished() ? true : false,
'user_status' => $cust->getPublished() ? "active" : "pending",
'package_name' => ($subscription) ? $subscription->getSubscribedPackage()->getPackageName() : "",
'subscription_start_date' => ($subscription) ? $subscription->getStartDate()->format("Y-m-d") : "",
'subscription_end_date' => ($subscription) ? $subscription->getEndDate()->format("Y-m-d") : "",
];
$data["organization"] = [
'id' => $org?->getId(),
'name_en' => $org?->getName('en'),
'name_ar' => $org?->getName('ar'),
'smsLimit' => $org?->getSmsLimit(),
'smsConsumption' => $org?->getSmsConsumption(),
'smsBalance' => $org ? (int)$org->getSmsLimit() - (int)$org->getSmsConsumption() : null,
'organization_status' => $this->determineOrganizationStatus($org),
];
$data["attempts"] = [];
foreach ($rows as $r) {
$data["attempts"][] = [
'status' => (int)$r['status'],
'attempt_time' => $r['attempt_time'],
'ip_address' => $r['ip_address'],
'user_agent' => $r['user_agent'],
];
}
// 5) Return with pagination block
return [
'success' => true,
'data' => $data,
'paginationVariables' => $pagination->getPaginationData(),
];
}
/**
* Applies org, user and date-range filters.
* Date filters now cover the full day: from 00:00:00 to 23:59:59.
*/
private function applyCommonFilters(
QueryBuilder $qb,
?int $organizationId,
?int $userId,
?string $from,
?string $to,
string $dateField = 'created_at'
): void {
$expr = $qb->expr();
if ($organizationId !== null) {
$qb->andWhere($expr->eq('organization_id', ':org'))
->setParameter('org', $organizationId);
}
if ($userId !== null) {
$qb->andWhere($expr->eq('user_id', ':usr'))
->setParameter('usr', $userId);
}
if ($from) {
// start of day
$fromTs = (new DateTime($from))->format('Y-m-d') . ' 00:00:00';
$qb->andWhere($expr->gte($dateField, ':from'))
->setParameter('from', $fromTs);
}
if ($to) {
// end of day
$toTs = (new DateTime($to))->format('Y-m-d') . ' 23:59:59';
$qb->andWhere($expr->lte($dateField, ':to'))
->setParameter('to', $toTs);
}
}
/**
* Apply organization, user‐ID, and date‐range filters to a login‐attempts QB.
*/
private function applyLoginFilters(
QueryBuilder $qb,
?int $organizationId,
?int $userId,
?string $from,
?string $to
): void {
$expr = $qb->expr();
if ($organizationId) {
$qb->andWhere($expr->eq('c.organization__id', ':org'))
->setParameter('org', $organizationId);
}
if ($userId) {
$qb->andWhere($expr->eq('c.oo_id', ':usr'))
->setParameter('usr', $userId);
}
if ($from) {
$fromTs = (new DateTime($from))->format('Y-m-d') . ' 00:00:00';
$qb->andWhere($expr->gte('ua.attempt_time', ':from'))
->setParameter('from', $fromTs);
}
if ($to) {
$toTs = (new DateTime($to))->format('Y-m-d') . ' 23:59:59';
$qb->andWhere($expr->lte('ua.attempt_time', ':to'))
->setParameter('to', $toTs);
}
}
/**
* Adds a LIKE‐OR group across all given column names.
*/
private function applySearchFilter(QueryBuilder $qb, ?string $search, array $columns): void
{
if (!$search) {
return;
}
$expr = $qb->expr();
$orX = $expr->orX();
foreach ($columns as $col) {
$orX->add($expr->like($col, ':search'));
}
$qb->andWhere($orX)
->setParameter('search', '%' . $search . '%');
}
/**
* Return total counts for each report entity, grouped by organization (localized names),
* optionally filtering by organization name, and sorting.
*
* @param string|null $search substring to match against name_en or name_ar
* @param string|null $sortBy one of: organization_id, name_en, name_ar,
* total_users, active_users, total_locations,
* total_ews_subscriptions, total_custom_notifications,
* total_received_emails, total_added_locations
* @param string $sortDir ASC or DESC
*/
public function fetchEntityCounts(
?int $organizationId = null,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'DESC',
int $page = 1,
int $pageSize = 20
): array {
// 1) Base SQL
$sql = <<<SQL
SELECT
o.oo_id AS organization_id,
en.name AS name_en,
ar.name AS name_ar,
o.clientType AS client_type,
pkg.packageName AS package_name,
pkg.maxUsers AS total_user_allowed,
COALESCE(o.SmsConsumption, 0) AS total_sms_consumed,
COALESCE(rtu.total, 0) AS total_users,
COALESCE(rau.total, 0) AS active_users,
COALESCE(rtl.total, 0) AS total_locations,
COALESCE(res.total, 0) AS total_ews_subscriptions,
COALESCE(rcn.total, 0) AS total_custom_notifications,
COALESCE(rre.total, 0) AS total_received_emails,
COALESCE(ral.total, 0) AS total_added_locations,
COUNT(DISTINCT CASE
WHEN c.o_published = 1 AND s.subscribedUser__id IS NOT NULL AND r.name IN ('CLIENT_USER', 'CLIENT_ADMIN') AND (s.isWso IS NULL OR s.isWso = 0)
AND (s.isActive = 1)
THEN c.oo_id ELSE NULL END) AS total_active_user,
COUNT(DISTINCT CASE
WHEN c.o_published = 1 AND s.subscribedUser__id IS NOT NULL AND r.name IN ('CLIENT_USER', 'CLIENT_ADMIN') AND (s.isWso IS NULL OR s.isWso = 0)
AND (s.isActive = 0)
THEN c.oo_id ELSE NULL END) AS total_suspended_user,
COUNT(DISTINCT CASE
WHEN c.token IS NOT NULL AND c.o_published = 0 AND r.name IN ('CLIENT_USER', 'CLIENT_ADMIN') AND (s.isWso IS NULL OR s.isWso = 0)
THEN c.oo_id ELSE NULL END) AS total_pending_user,
COUNT(DISTINCT CASE
WHEN r.name ='CLIENT_ADMIN' AND (s.isWso IS NULL OR s.isWso = 0)
THEN c.oo_id ELSE NULL END) AS total_admin_user,
COUNT(DISTINCT CASE
WHEN c.jwtToken IS NOT NULL
AND c.jwtToken <> ''
AND c.jwtTokenExpiry > UNIX_TIMESTAMP() -- Valid token and not expired
THEN c.oo_id
ELSE NULL
END) AS total_real_login_users
FROM object_query_organization o
LEFT JOIN object_localized_query_organization_en en
ON en.ooo_id = o.oo_id
LEFT JOIN object_localized_query_organization_ar ar
ON ar.ooo_id = o.oo_id
LEFT JOIN object_localized_packages_en pkg
ON o.package__id = pkg.oo_id
LEFT JOIN (
SELECT organization_id, COUNT(*) AS total
FROM reporting_total_user
GROUP BY organization_id
) rtu ON rtu.organization_id = o.oo_id
LEFT JOIN (
SELECT organization_id, COUNT(*) AS total
FROM reporting_active_user
GROUP BY organization_id
) rau ON rau.organization_id = o.oo_id
LEFT JOIN (
SELECT organization_id, COUNT(*) AS total
FROM reporting_total_location
GROUP BY organization_id
) rtl ON rtl.organization_id = o.oo_id
LEFT JOIN (
SELECT organization_id, COUNT(*) AS total
FROM reporting_ews_subscription
GROUP BY organization_id
) res ON res.organization_id = o.oo_id
LEFT JOIN (
SELECT organization_id, COUNT(*) AS total
FROM reporting_custom_notification
GROUP BY organization_id
) rcn ON rcn.organization_id = o.oo_id
LEFT JOIN (
SELECT organization_id, COUNT(*) AS total
FROM reporting_received_emails
GROUP BY organization_id
) rre ON rre.organization_id = o.oo_id
LEFT JOIN (
SELECT organization_id, COUNT(*) AS total
FROM reporting_added_locations
GROUP BY organization_id
) ral ON ral.organization_id = o.oo_id
LEFT JOIN object_customer c
ON c.organization__id = o.oo_id
LEFT JOIN object_subscription s
ON s.subscribedUser__id = c.oo_id
LEFT JOIN object_user_role r ON r.oo_id = c.role__id
WHERE (o.isDeleted IS NULL OR o.isDeleted = 0) AND (o.isInternal IS NULL OR o.isInternal = 0)
SQL;
$params = [];
// 2) Optional organization filter
if ($organizationId !== null) {
$sql .= "\nAND o.oo_id = :org";
$params['org'] = $organizationId;
}
// 3) Apply search filter
if ($search) {
$clause = isset($params['org']) ? 'AND' : 'AND';
$sql .= "\n{$clause} (en.name LIKE :search OR ar.name LIKE :search)";
$params['search'] = "%{$search}%";
}
$sql .= " GROUP BY o.oo_id, en.name, ar.name, pkg.packageName, pkg.maxUsers, o.SmsConsumption, rtu.total, rau.total, rtl.total, res.total, rcn.total, rre.total, ral.total";
$allowed = [
'organization_id',
'name_en',
'name_ar',
'client_type',
'package_name',
'total_user_allowed',
'total_sms_consumed',
'total_users',
'active_users',
'total_locations',
'total_ews_subscriptions',
'total_custom_notifications',
'total_received_emails',
'total_added_locations',
'total_suspended_user',
'total_active_user',
'total_pending_user',
'total_real_login_users',
'total_admin_user'
];
if ($sortBy && in_array($sortBy, $allowed, true)) {
$dir = strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC';
$sql .= "\nORDER BY {$sortBy} {$dir}";
} else {
// Default sort by latest organization ID
$sql .= "\nORDER BY o.oo_id DESC";
}
$all = $this->db->executeQuery($sql, $params)->fetchAll(\PDO::FETCH_ASSOC);
$pagination = $this->paginator->paginate($all, $page, $pageSize);
// 7) Summary counts
$totalEntities = (int) $this->db->executeQuery("SELECT COUNT(*) FROM object_query_organization WHERE (isDeleted IS NULL OR isDeleted = 0) AND (isInternal IS NULL OR isInternal = 0)")->fetchOne();
$totalOrganizations = (int) $this->db->executeQuery("SELECT COUNT(*) FROM object_query_organization WHERE clientType = 'organization' AND (isDeleted IS NULL OR isDeleted = 0) AND (isInternal IS NULL OR isInternal = 0)")->fetchOne();
$totalGovernmentEntities = (int) $this->db->executeQuery("SELECT COUNT(*) FROM object_query_organization WHERE clientType = 'government' AND (isDeleted IS NULL OR isDeleted = 0) AND (isInternal IS NULL OR isInternal = 0)")->fetchOne();
// ✅ Updated user status logic for global counts
$userStatusSql = <<<SQL
SELECT
COUNT(DISTINCT CASE
WHEN c.o_published = 1 AND s.subscribedUser__id IS NOT NULL AND r.name IN ('CLIENT_USER', 'CLIENT_ADMIN') AND (s.isWso IS NULL OR s.isWso = 0) AND s.subscriptionType = 'custom'
AND (s.isActive = 1)
THEN c.oo_id ELSE NULL END) AS total_active,
COUNT(DISTINCT CASE
WHEN c.o_published = 1 AND s.subscribedUser__id IS NOT NULL AND r.name IN ('CLIENT_USER', 'CLIENT_ADMIN') AND (s.isWso IS NULL OR s.isWso = 0)
AND (s.isActive = 0)
THEN c.oo_id ELSE NULL END) AS total_suspended,
COUNT(DISTINCT CASE
WHEN c.token IS NOT NULL AND c.o_published = 0 AND r.name IN ('CLIENT_USER', 'CLIENT_ADMIN') AND (s.isWso IS NULL OR s.isWso = 0) ANd s.subscriptionType != 'custom'
THEN c.oo_id ELSE NULL END) AS total_pending
FROM object_customer c
LEFT JOIN object_subscription s ON s.subscribedUser__id = c.oo_id
LEFT JOIN object_user_role r ON r.oo_id = c.role__id
WHERE (c.isDeleted IS NULL OR c.isDeleted = 0)
SQL;
$userStatus = $this->db->executeQuery($userStatusSql)->fetchAssociative();
// 8) Per-organization enrichment
$orgResult = [];
$items = $pagination->getItems();
if ($items) {
$counter = 0;
foreach ($items as $item) {
$organization = \Pimcore\Model\DataObject::getById($item["organization_id"]);
if ($organization) {
$orgResult[$counter] = $item;
$counter++;
}
}
}
// 9) Return final data
return [
'success' => true,
'summary' => [
'total_entities' => $totalEntities,
'client_type_entity' => $totalOrganizations,
'client_type_government' => $totalGovernmentEntities,
'user_status_counts' => [
'active' => (int) ($userStatus['total_active'] ?? 0),
'pending' => (int) ($userStatus['total_pending'] ?? 0),
'suspended' => (int) ($userStatus['total_suspended'] ?? 0), // ✅ UPDATED here
],
],
'data' => $orgResult,
'paginationVariables' => $pagination->getPaginationData(),
];
}
/**
* Return high-level summary counts for a given user (and optional date range).
*
* @param int|null $userId
* @param string|null $from YYYY-MM-DD
* @param string|null $to YYYY-MM-DD
* @return array{ success: bool, summary: array{ emailsSent: int, smsSent: int, ewsCount: int, customNotifications: int, passwordResetEmails: int } }
*/
public function userAnalytics(
?int $userId = null,
?string $from = null,
?string $to = null
): array {
// helper to apply date filters
$applyDate = function (QueryBuilder $qb, string $alias, string $dateCol) use ($from, $to) {
if ($from) {
$qb->andWhere("$alias.$dateCol >= :from")
->setParameter('from', (new DateTime($from))->format('Y-m-d') . ' 00:00:00');
}
if ($to) {
$qb->andWhere("$alias.$dateCol <= :to")
->setParameter('to', (new DateTime($to))->format('Y-m-d') . ' 23:59:59');
}
};
// 1) Emails sent
$qbEmails = $this->db->createQueryBuilder()
->select('COUNT(*)')
->from('reporting_received_emails', 're');
if ($userId !== null) {
$qbEmails->andWhere('re.user_id = :usr')
->setParameter('usr', $userId);
}
$applyDate($qbEmails, 're', 'sent_date');
$emailsSent = (int)$qbEmails->execute()->fetchOne();
// 2) Total SMS sent
// (If you have a per-user SMS log table, swap this to count that instead;
// otherwise you’ll need to track SMS per user differently.)
$qbSms = $this->db->createQueryBuilder()
->select('COALESCE(SUM(o.SmsConsumption), 0)')
->from('object_query_organization', 'o')
->leftJoin('o', 'object_query_customer', 'c', 'c.organization__id = o.oo_id');
if ($userId !== null) {
$qbSms->andWhere('c.oo_id = :usr')
->setParameter('usr', $userId);
}
$smsSent = (int)$qbSms->execute()->fetchOne();
// 3) EWS subscription count
$qbEws = $this->db->createQueryBuilder()
->select('COUNT(*)')
->from('reporting_ews_subscription', 's');
if ($userId !== null) {
$qbEws->andWhere('s.user_id = :usr')
->setParameter('usr', $userId);
}
$applyDate($qbEws, 's', 'created_at');
$ewsCount = (int)$qbEws->execute()->fetchOne();
// 4) Custom notification count
$qbCustom = $this->db->createQueryBuilder()
->select('COUNT(*)')
->from('reporting_custom_notification', 'rcn');
if ($userId !== null) {
$qbCustom->andWhere('rcn.user_id = :usr')
->setParameter('usr', $userId);
}
$applyDate($qbCustom, 'rcn', 'created_at');
$customNotifications = (int)$qbCustom->execute()->fetchOne();
// 5) Password-reset email count
$qbPw = $this->db->createQueryBuilder()
->select('COUNT(*)')
->from('reporting_received_emails', 're')
->leftJoin('re', 'object_MannedAlertLog', 'l', 'l.oo_id = re.log_id')
->andWhere('l.purpose = :purpose')
->setParameter('purpose', 'Email Send For Reset Password');
if ($userId !== null) {
$qbPw->andWhere('re.user_id = :usr')
->setParameter('usr', $userId);
}
$applyDate($qbPw, 're', 'sent_date');
$passwordResetEmails = (int)$qbPw->execute()->fetchOne();
return [
'success' => true,
'summary' => [
'emailsSent' => $emailsSent,
'smsSent' => $smsSent,
'ewsCount' => $ewsCount,
'customNotifications' => $customNotifications,
'passwordResetEmails' => $passwordResetEmails,
],
];
}
public function getLocationsExcel(
?int $userId,
?string $from,
?string $to,
string $sortDir = 'ASC',
string $lang = 'en',
$translator
): array {
// Build query
$qb = $this->db->createQueryBuilder()
->select([
'rl.organization_id',
'rl.user_id',
'rl.location_id',
'l.name AS name_en',
'l.title AS name_ar',
'u.name AS user_name',
'u.email AS user_email',
'r.name AS user_role',
'rl.created_at',
'rl.updated_at'
])
->from('reporting_total_location', 'rl')
->leftJoin('rl', 'object_query_location', 'l', 'l.oo_id = rl.location_id')
->leftJoin('rl', 'object_query_customer', 'u', 'u.oo_id = rl.user_id')
->leftJoin('u', 'object_query_user_role', 'r', 'r.oo_id = u.role__id');
// $this->applyCommonFilters($qb, $userId, $from, $to, 'updated_at');
$allowed = [
'organization_id',
'user_id',
'location_id',
'name_en',
'name_ar',
'user_name',
'user_email',
'user_role',
'created_at',
'updated_at'
];
if ($sortDir && in_array($sortDir, $allowed, true)) {
$qb->orderBy($sortDir, strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC');
}
$rows = $qb->execute()->fetchAllAssociative();
$data = [];
if (count($rows) > 0) {
// Header row
$data[] = [
'S. No' => 'S. No',
'User Name' => 'User Name',
'User Email' => 'User Email',
'User Role' => 'User Role',
'Location ID' => 'Location ID',
'Location Name' => 'Location Name (EN)',
'Location Name (AR)' => 'Location Name (AR)',
'Created At' => 'Created At',
'Updated At' => 'Updated At'
];
foreach ($rows as $index => $row) {
$data[] = [
'S. No' => $index + 1,
'User Name' => $row['user_name'],
'User Email' => $row['user_email'],
'User Role' => $row['user_role'],
'Location ID' => $row['location_id'],
'Location Name' => $row['name_en'],
'Location Name (AR)' => $row['name_ar'],
'Created At' => $row['created_at'],
'Updated At' => $row['updated_at']
];
}
// You must have ExcelGenerator and $translator available in your context
$excelData = \App\Lib\ExcelGenerator::createAndSaveXlsx($data, "locations_excel", true, '/User_dashboard/excel/Locations/');
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $excelData
];
} else {
return [
"success" => false,
"message" => isset($translator) ? $translator->trans("records_not_found") : "Records not found"
];
}
}
public function getEntityExcel(
?int $userId,
?string $from,
?string $to,
string $sortDir = 'ASC',
string $lang = 'en',
$translator
): array {
// Build query
$entityData = $this->fetchEntityCounts(
null,
null,
null,
$sortDir,
1,
10000
);
// dd($entityData['data']);
$rows = $entityData['data'];
$data = [];
if (count($rows) > 0) {
// Header row
$data[] = [
'S. No' => 'S. No',
'Entity Name' => 'Entity Name',
'Package' => 'Package',
'Allocated User Count' => 'Allocated User Count',
'Total Users' => 'Total Users',
'Active Users' => 'Active Users',
'Pending Users' => 'Pending Users',
'Total Locations' => 'Total Locations',
'Added Locations' => 'Added Locations',
'Alerts Subscription' => 'Alerts Subscription',
'Custom Notifications' => 'Custom Notifications',
'Received Emails' => 'Received Emails'
];
foreach ($rows as $index => $row) {
$data[] = [
'S. No' => $index + 1,
'Entity Name' => $row['name_en'],
'Package' => $row['package_name'],
'Allocated User Count' => $row['total_user_allowed'],
'Total Users' => $row['total_users'],
'Active Users' => $row['active_users'],
'Pending Users' => $row['total_pending_user'],
'Total Locations' => $row['total_locations'],
'Added Locations' => $row['total_locations'],
'Alerts Subscription' => $row['total_ews_subscriptions'],
'Custom Notifications' => $row['total_custom_notifications'],
'Received Emails' => $row['total_received_emails']
];
}
// You must have ExcelGenerator and $translator available in your context
$excelData = \App\Lib\ExcelGenerator::createAndSaveXlsx($data, "entity_excel", true, '/User_dashboard/excel/Entities/');
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $excelData
];
} else {
return [
"success" => false,
"message" => isset($translator) ? $translator->trans("records_not_found") : "Records not found"
];
}
}
public function getEntityDetailExcel(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
?string $role = null, // ← new
?int $isPublished = null,
int $page = 1,
int $pageSize = 20,
string $lang = 'en', // Default to English
$translator
): array {
// Build query
$entityData = $this->fetchTotalUsers(
$organizationId,
$userId,
$from,
$to,
$search,
$sortBy,
$sortDir,
$role,
$isPublished,
$page,
$pageSize
);
// dd($entityData);
$rows = $entityData['data'];
$data = [];
if (count($rows) > 0) {
// Header row
$data[] = [
'S. No' => 'S. No',
'Email' => 'Entity Name',
'Role' => 'Role',
'Created On' => 'Created On',
'Package Name' => 'Package Name',
'Package Expiry' => 'Package Expiry',
'Status' => 'Status',
];
foreach ($rows as $index => $row) {
$data[] = [
'S. No' => $index + 1,
'Email' => $row['email'],
'Role' => $row['role'],
'Created On' => $row['created_at'],
'Package Name' => $row['package_name'],
'Package Expiry' => $row['subscription_end_date'] ?? 'N/A',
'Status' => $row['user_status'] ?? 'N/A'
];
}
// You must have ExcelGenerator and $translator available in your context
$excelData = \App\Lib\ExcelGenerator::createAndSaveXlsx($data, "entity_details_excel", true, '/User_dashboard/excel/Entity-details/');
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $excelData
];
} else {
return [
"success" => false,
"message" => isset($translator) ? $translator->trans("records_not_found") : "Records not found"
];
}
}
public function getEntityUserNotificationExcel(
?int $organizationId,
?int $userId,
?int $type,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
int $page = 1,
int $pageSize = 20,
string $lang = 'en', // Default to English
$translator
): array {
// Build query
$entityData = $this->fetchCustomNotifications(
$organizationId,
$userId,
$type,
$from,
$to,
$search,
$sortBy,
$sortDir,
$page,
$pageSize
);
// dd($entityData);
$rows = $entityData['data'];
$data = [];
if (count($rows) > 0) {
// Header row
$data[] = [
'S. No' => 'S. No',
'Notification Title' => 'Notification Title',
'Type' => 'Type',
'Created On' => 'Created On',
];
foreach ($rows as $index => $row) {
$data[] = [
'S. No' => $index + 1,
'Notification Title' => $row['title'],
'Type' => $row['type'] == 1 ? 'Custom' : 'System',
'Created On' => $row['created_at'],
];
}
// You must have ExcelGenerator and $translator available in your context
$excelData = \App\Lib\ExcelGenerator::createAndSaveXlsx($data, "entity_user_notifications_excel", true, '/User_dashboard/excel/entity-user-notifications-excel/');
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $excelData
];
} else {
return [
"success" => false,
"message" => isset($translator) ? $translator->trans("records_not_found") : "Records not found"
];
}
}
public function getEntityUserEwsSubscriptionsExcel(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
int $page = 1,
int $pageSize = 20,
string $lang = 'en', // Default to English
$translator
): array {
// Build query
$entityData = $this->fetchEwsSubscriptions(
$organizationId,
$userId,
$from,
$to,
$search,
$sortBy,
$sortDir,
$page,
$pageSize,
$lang
);
// dd($entityData);
$rows = $entityData['data'];
$data = [];
if (count($rows) > 0) {
// Header row
$data[] = [
'S. No' => 'S. No',
'Region Name' => 'Region Name',
'Subscription Date' => 'Subscription Date',
'Status' => 'Status',
];
foreach ($rows as $index => $row) {
$data[] = [
'S. No' => $index + 1,
'Region Name' => $row['region_name_en'],
'Subscription Date' => $row['created_at'],
'Status' => 'Active', // Assuming all subscriptions are active
];
}
// You must have ExcelGenerator and $translator available in your context
$excelData = \App\Lib\ExcelGenerator::createAndSaveXlsx($data, "entity_user_ews_subscription_excel", true, '/User_dashboard/excel/entity-user-ews-subscription-excel/');
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $excelData
];
} else {
return [
"success" => false,
"message" => isset($translator) ? $translator->trans("records_not_found") : "Records not found"
];
}
}
public function getEntityUserTotalLocationsExcel(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
int $page = 1,
int $pageSize = 20,
string $lang = 'en', // Default to English
$translator
): array {
// Build query
$entityData = $this->fetchTotalLocations(
$organizationId,
$userId,
$from,
$to,
$search,
$sortBy,
$sortDir,
$page,
$pageSize,
$lang
);
$rows = $entityData['data'];
$data = [];
if (count($rows) > 0) {
// Header row
$data[] = [
'S. No' => 'S. No',
'Added Locations' => 'Added Locations',
'Created On' => 'Created On',
'Updated On' => 'Updated On',
];
foreach ($rows as $index => $row) {
$data[] = [
'S. No' => $index + 1,
'Added Locations' => $row['name_en'],
'Created On' => $row['created_at'],
'Updated On' => $row['updated_at'],
];
}
// You must have ExcelGenerator and $translator available in your context
$excelData = \App\Lib\ExcelGenerator::createAndSaveXlsx($data, "entity_user_locations_excel", true, '/User_dashboard/excel/entity-user-locations-excel/');
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $excelData
];
} else {
return [
"success" => false,
"message" => isset($translator) ? $translator->trans("records_not_found") : "Records not found"
];
}
}
private function determineOrganizationStatus($org): string
{
$today = Carbon::now();
if ($org?->getStatus() === 'trial') {
$limitDays = (int) $org?->getTrialLimit();
$activationDate = Carbon::parse($org?->getPackageActivationDate());
return $today->greaterThan($activationDate->copy()->addDays($limitDays)) ? 'expired' : 'active';
}
if ($org?->getStatus() === 'expired') {
return 'expired';
}
$limitDays = (int) $org?->getTrialLimit();
$activationDate = Carbon::parse($org?->getPackageActivationDate());
return $today->greaterThan($activationDate->copy()->addDays($limitDays)) ? 'expired' : 'active';
}
public function getEntityUserCollectiveExcel(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
string $lang = 'en', // Default to English
$translator
): array {
$allSheets = [];
// Get User Login Details first (needed for User Details sheet)
$loginData = $this->fetchUserLoginDetails(
$organizationId,
$userId,
$from,
$to,
$search,
1,
1000000
);
// 1. User Details Sheet (FIRST SHEET)
$userDetailsSheetData = [];
if (isset($loginData['data']['user']) && isset($loginData['data']['summary'])) {
$user = $loginData['data']['user'];
$summary = $loginData['data']['summary'];
$attempts = $loginData['data']['attempts'] ?? [];
// Calculate attempts from the attempts array
$successAttempts = 0;
$failedAttempts = 0;
$successAttemptDates = [];
$failedAttemptDates = [];
foreach ($attempts as $attempt) {
if ($attempt['status'] == 1) {
$successAttempts++;
// Format date to "August 05, 2025 04:55 PM" format
try {
// Handle different date formats
$attemptTime = $attempt['attempt_time'];
if (is_numeric($attemptTime)) {
// If it's a timestamp
$date = new \DateTime('@' . $attemptTime);
} else {
// If it's a date string
$date = new \DateTime($attemptTime);
}
// Set timezone to Asia/Riyadh as per user preference
$date->setTimezone(new \DateTimeZone('Asia/Riyadh'));
$successAttemptDates[] = $date->format('F d, Y g:i A');
} catch (\Exception $e) {
// Fallback to original format if parsing fails
$successAttemptDates[] = $attempt['attempt_time'];
}
} elseif ($attempt['status'] == 0) {
$failedAttempts++;
// Format date to "August 05, 2025 04:55 PM" format
try {
// Handle different date formats
$attemptTime = $attempt['attempt_time'];
if (is_numeric($attemptTime)) {
// If it's a timestamp
$date = new \DateTime('@' . $attemptTime);
} else {
// If it's a date string
$date = new \DateTime($attemptTime);
}
// Set timezone to Asia/Riyadh as per user preference
$date->setTimezone(new \DateTimeZone('Asia/Riyadh'));
$failedAttemptDates[] = $date->format('F d, Y g:i A');
} catch (\Exception $e) {
// Fallback to original format if parsing fails
$failedAttemptDates[] = $attempt['attempt_time'];
}
}
}
// Determine package status based on subscription
$packageStatus = 'Active';
if (isset($user['subscription_end_date']) && !empty($user['subscription_end_date'])) {
$endDate = new \DateTime($user['subscription_end_date']);
$today = new \DateTime();
if ($today > $endDate) {
$packageStatus = 'Expired';
}
} else {
$packageStatus = 'No Package';
}
// Format last login dates
$lastLoginDate = '';
$lastFailedLoginDate = '';
if (!empty($summary['lastLogin'])) {
try {
$lastLogin = new \DateTime($summary['lastLogin']);
$lastLogin->setTimezone(new \DateTimeZone('Asia/Riyadh'));
$lastLoginDate = $lastLogin->format('F d, Y g:i A');
} catch (\Exception $e) {
$lastLoginDate = $summary['lastLogin'];
}
}
if (!empty($summary['lastFailedLogin'])) {
try {
$lastFailedLogin = new \DateTime($summary['lastFailedLogin']);
$lastFailedLogin->setTimezone(new \DateTimeZone('Asia/Riyadh'));
$lastFailedLoginDate = $lastFailedLogin->format('F d, Y g:i A');
} catch (\Exception $e) {
$lastFailedLoginDate = $summary['lastFailedLogin'];
}
}
// Capitalize first letter of user status
$userStatus = $user['user_status'] ?? '';
if (!empty($userStatus)) {
$userStatus = ucfirst($userStatus);
}
// Add User Details in row-wise format
$userDetailsSheetData[] = [
'Field' => 'USER DETAILS',
'Value' => ''
];
$userDetailsSheetData[] = [
'Field' => 'Name',
'Value' => $user['name'] ?? ''
];
$userDetailsSheetData[] = [
'Field' => 'Email',
'Value' => $user['email'] ?? ''
];
$userDetailsSheetData[] = [
'Field' => 'Package Name',
'Value' => $user['package_name'] ?? ''
];
$userDetailsSheetData[] = [
'Field' => 'Package Status',
'Value' => $packageStatus
];
$userDetailsSheetData[] = [
'Field' => 'User Status',
'Value' => $userStatus
];
$userDetailsSheetData[] = [
'Field' => 'Last Login Date',
'Value' => $lastLoginDate
];
$userDetailsSheetData[] = [
'Field' => 'Last Failed Login Date',
'Value' => $lastFailedLoginDate
];
$userDetailsSheetData[] = [
'Field' => 'Total Attempts',
'Value' => $failedAttempts + $successAttempts
];
$userDetailsSheetData[] = [
'Field' => 'Failed Attempts',
'Value' => $failedAttempts
];
$userDetailsSheetData[] = [
'Field' => 'Success Attempts',
'Value' => $successAttempts
];
} else {
$userDetailsSheetData[] = [
'Field' => 'Status',
'Value' => 'No User Details found'
];
}
$allSheets[] = [
'Sheet Name' => 'User Details',
'Data' => $userDetailsSheetData
];
// 1. Get EWS Subscriptions Data
$ewsData = $this->fetchEwsSubscriptions(
$organizationId,
$userId,
$from,
$to,
$search,
$sortBy,
$sortDir,
1,
100000,
$lang
);
$ewsSheetData = [];
// Add EWS Subscriptions headers
$ewsSheetData[] = [
'S. No' => 'S. No',
'Region Name' => 'Region Name',
'Subscription Date' => 'Subscription Date',
'Status' => 'Status',
];
if (count($ewsData['data']) > 0) {
foreach ($ewsData['data'] as $index => $row) {
$ewsSheetData[] = [
'S. No' => $index + 1,
'Region Name' => $row['region_name_en'],
'Subscription Date' => $row['created_at'],
'Status' => 'Active',
];
}
} else {
$ewsSheetData[] = [
'S. No' => '',
'Region Name' => 'No EWS Subscriptions found',
'Subscription Date' => '',
'Status' => '',
];
}
$allSheets[] = [
'Sheet Name' => 'EWS Subscriptions',
'Data' => $ewsSheetData
];
// 2. Get Notifications Data
$notificationData = $this->fetchCustomNotifications(
$organizationId,
$userId,
null, // type
$from,
$to,
$search,
$sortBy,
$sortDir,
1,
1000000
);
$notificationSheetData = [];
// Add Notifications headers
$notificationSheetData[] = [
'S. No' => 'S. No',
'Notification Title' => 'Notification Title',
'Type' => 'Type',
'Created On' => 'Created On',
];
if (count($notificationData['data']) > 0) {
foreach ($notificationData['data'] as $index => $row) {
$notificationSheetData[] = [
'S. No' => $index + 1,
'Notification Title' => $row['title'],
'Type' => $row['type'] == 1 ? 'Custom' : 'System',
'Created On' => $row['created_at'],
];
}
} else {
$notificationSheetData[] = [
'S. No' => '',
'Notification Title' => 'No Notifications found',
'Type' => '',
'Created On' => '',
];
}
$allSheets[] = [
'Sheet Name' => 'Notifications',
'Data' => $notificationSheetData
];
// 3. Get Total Locations Data
$locationsData = $this->fetchTotalLocations(
$organizationId,
$userId,
$from,
$to,
$search,
$sortBy,
$sortDir,
1,
1000000,
$lang
);
$locationsSheetData = [];
// Add Total Locations headers
$locationsSheetData[] = [
'S. No' => 'S. No',
'Added Locations' => 'Added Locations',
'Created On' => 'Created On',
'Updated On' => 'Updated On',
];
if (count($locationsData['data']) > 0) {
foreach ($locationsData['data'] as $index => $row) {
$locationsSheetData[] = [
'S. No' => $index + 1,
'Added Locations' => $row['name_en'],
'Created On' => $row['created_at'],
'Updated On' => $row['updated_at'],
];
}
} else {
$locationsSheetData[] = [
'S. No' => '',
'Added Locations' => 'No Locations found',
'Created On' => '',
'Updated On' => '',
];
}
$allSheets[] = [
'Sheet Name' => 'Total Locations',
'Data' => $locationsSheetData
];
// 4. Get Email Statistics (using existing loginData)
$emailSheetData = [];
// Add Email Received headers with exact format from screenshot
$emailSheetData[] = [
'OTP' => 'OTP',
'Early Warning Emails' => 'Early Warning Emails',
'Custom Notifications' => 'Custom Notifications',
];
if (isset($loginData['data']['summary'])) {
$summary = $loginData['data']['summary'];
// Calculate Custom Notifications (sum of basic and advance)
$basicCount = $summary['totalBasicCustomNotificationEmailReceived'] ?? 0;
$advanceCount = $summary['totalAdvanceCustomNotificationEmailReceived'] ?? 0;
$totalCustomCount = $basicCount + $advanceCount;
// Add data row with exact format from screenshot
$emailSheetData[] = [
'OTP' => $summary['totalOtpEmailReceived'] ?? 0,
'Early Warning Emails' => $summary['totalEwsAlertEmailReceived'] ?? 0,
'Custom Notifications' => $totalCustomCount,
];
} else {
// Add data row with zeros if no data
$emailSheetData[] = [
'OTP' => 0,
'Early Warning Emails' => 0,
'Custom Notifications' => 0,
];
}
$allSheets[] = [
'Sheet Name' => 'Email Received',
'Data' => $emailSheetData
];
if (count($allSheets) > 0) {
// Create multi-sheet Excel file
$excelData = \App\Lib\ExcelGenerator::createAndSaveMultiSheetXlsx($allSheets, "entity_user_collective_excel" . time(), true, '/User_dashboard/excel/entity-user-collective-excel/');
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $excelData
];
} else {
return [
"success" => false,
"message" => isset($translator) ? $translator->trans("records_not_found") : "Records not found"
];
}
}
public function generateEntityUserAllCollectiveExcel(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search = null,
?string $sortBy = null,
string $sortDir = 'ASC',
string $lang = 'en', // Default to English
$translator
): array {
$allSheets = [];
// Filter by entityId if provided
$usersData = $this->fetchFilteredUsersData(
$organizationId,
$userId,
$from,
$to,
$search,
$sortBy,
$sortDir
);
if (count($usersData) == 0) {
return ['success' => false, 'message' => $translator->trans("records_not_found")];
}
// Excel Header Row
$excelData = [];
$excelData[] = [
'Name',
'Email',
'Role',
'Client Type',
'Package Name',
'Package Status',
'Entity Name',
'User Status',
'Last Login Date',
'Last Failed Login Date',
'Total Attempts',
'Failed Attempts',
'Success Attempts',
'Subscription Count',
'Notification Count',
'Added Locations Count',
'Email Received Count',
'Email Received OTP Count',
'Email Received Early Warning Count',
'Email Received Custom Notifications Count'
];
// Add user data to Excel
foreach ($usersData as $user) {
$userDetails = [
$user['name'] ?? '',
$user['email'] ?? '',
$user['role'] ?? '',
$user['client_type'] ?? '',
$user['package_name'] ?? '',
$user['package_status'] ?? '',
$user['entity_name'] ?? '',
$user['user_status'] ?? '',
$user['last_login'] ?? '',
$user['last_failed_login'] ?? '',
$user['total_attempts'] ?? 0,
$user['failed_attempts'] ?? 0,
$user['success_attempts'] ?? 0,
$user['subscription_count'] ?? 0,
$user['notification_count'] ?? 0,
$user['added_locations_count'] ?? 0,
$user['email_received_count'] ?? 0,
$user['email_received_otp_count'] ?? 0,
$user['email_received_ews_count'] ?? 0,
$user['email_received_custom_notifications_count'] ?? 0
];
$excelData[] = $userDetails;
}
// Create the Excel file
$generatedExcel = \App\Lib\ExcelGenerator::createAndSaveMultiSheetXlsx(
[['Sheet Name' => 'User Data', 'Data' => $excelData]],
"entity_user_collective_excel" . time(),
true,
'/User_dashboard/excel/entity-user-collective-excel/'
);
return [
"success" => true,
"message" => isset($translator) ? $translator->trans("excel_generated") : "Excel generated",
"data" => $generatedExcel,
];
}
private function fetchFilteredUsersData(
?int $organizationId,
?int $userId,
?string $from,
?string $to,
?string $search,
?string $sortBy,
string $sortDir
): array {
// Normalize date filters
$fromDateTime = $from ? (new \DateTime($from))->format('Y-m-d') . ' 00:00:00' : null;
$toDateTime = $to ? (new \DateTime($to))->format('Y-m-d') . ' 23:59:59' : null;
$fromTs = $from ? (new \DateTime($from))->setTime(0, 0, 0)->getTimestamp() : null;
$toTs = $to ? (new \DateTime($to))->setTime(23, 59, 59)->getTimestamp() : null;
// Base users query (ensure one row per user). Join to latest custom subscription per user.
$qb = $this->db->createQueryBuilder();
$qb
->select([
'u.oo_id',
'u.name',
'u.email',
'org.clientType AS clientType',
'u.o_published',
'u.token',
'ur.name AS role',
'pkg.packageName AS packageName',
'org_en.name AS entityName',
'sub.isActive',
'sub.isWso',
'sub.oo_id AS sub_id'
])
->from('object_customer', 'u')
// Latest custom subscription per user
->leftJoin(
'u',
'(SELECT s.subscribedUser__id AS user_id, MAX(s.o_creationDate) AS max_created
FROM object_subscription s
WHERE s.subscriptionType = "custom"
GROUP BY s.subscribedUser__id)',
'smax',
'smax.user_id = u.oo_id'
)
->leftJoin(
'u',
'object_subscription',
'sub',
'sub.subscribedUser__id = u.oo_id AND sub.subscriptionType = "custom" AND sub.o_creationDate = smax.max_created'
)
->leftJoin('sub', 'object_localized_packages_en', 'pkg', 'pkg.oo_id = sub.subscribedPackage__id')
->leftJoin('u', 'object_user_role', 'ur', 'ur.oo_id = u.role__id')
->leftJoin('u', 'object_localized_query_organization_en', 'org_en', 'org_en.ooo_id = u.organization__id')
->leftJoin('u', 'object_query_organization', 'org', 'org.oo_id = u.organization__id')
->where('ur.name IN ("CLIENT_ADMIN", "CLIENT_USER")');
// Filters
if ($organizationId) {
$qb->andWhere('u.organization__id = :organizationId')->setParameter('organizationId', $organizationId);
}
if ($userId) {
$qb->andWhere('u.oo_id = :userId')->setParameter('userId', $userId);
}
if ($fromTs) {
$qb->andWhere('u.o_creationDate >= :fromUser')->setParameter('fromUser', $fromTs);
}
if ($toTs) {
$qb->andWhere('u.o_creationDate <= :toUser')->setParameter('toUser', $toTs);
}
if ($search) {
$expr = $qb->expr();
$qb->andWhere($expr->or(
$expr->like('u.name', ':search'),
$expr->like('u.email', ':search')
))->setParameter('search', "%{$search}%");
}
// Get users
$users = $qb->execute()->fetchAllAssociative();
// Unique user IDs for batch queries
$userIds = array_values(array_unique(array_column($users, 'oo_id')));
if (empty($userIds)) {
return [];
}
// Batch queries with date filtering
$loginAttempts = $this->fetchLoginAttemptsBatch($userIds, $fromDateTime, $toDateTime);
$subscriptionCounts = $this->fetchSubscriptionCountsBatch($userIds, $fromTs, $toTs); // EWS subscriptions
$locationCounts = $this->fetchLocationCountsBatch($userIds, $fromDateTime, $toDateTime);
$emailStats = $this->fetchEmailStatsBatch($userIds, $fromDateTime, $toDateTime);
$notificationCounts = $this->fetchNotificationCountsBatch($userIds, $fromDateTime, $toDateTime);
// Merge data
$result = [];
foreach ($users as $u) {
$uid = (int)$u['oo_id'];
$hasPackage = !empty($u['sub_id']);
$isActive = isset($u['isActive']) ? (int)$u['isActive'] === 1 : false;
$isWso = isset($u['isWso']) ? (int)$u['isWso'] === 1 : false;
// Derive user status
$userStatus = 'Unknown';
if ((int)$u['o_published'] === 1 && $hasPackage && !$isWso) {
$userStatus = $isActive ? 'Active' : 'Suspended';
} elseif (!empty($u['token']) && (int)$u['o_published'] === 0 && !$isWso) {
$userStatus = 'Pending';
}
$login = $loginAttempts[$uid] ?? [
'lastLogin' => null,
'lastFailedLogin' => null,
'LoginCount' => 0,
'LogoutCount' => 0
];
$email = $emailStats[$uid] ?? [
'user_id' => $uid,
'totalEmailReceived' => 0,
'totalOtpEmailReceived' => 0,
'totalBasicCustomNotificationEmailReceived' => 0,
'totalAdvanceCustomNotificationEmailReceived' => 0,
'totalEwsAlertEmailReceived' => 0
];
$result[] = [
'name' => $u['name'],
'email' => $u['email'],
'client_type' => $u['clientType'] == 'organization' ? 'Entity' : 'Government',
'role' => $u['role'] == 'CLIENT_ADMIN' ? 'Admin' : 'User',
'package_name' => $u['packageName'] ?? '',
'package_status' => $hasPackage ? ($isActive ? 'Active' : 'Inactive') : 'No Package',
'entity_name' => $u['entityName'],
'user_status' => $userStatus,
'last_login' => $login['lastLogin'] ?? '',
'last_failed_login' => $login['lastFailedLogin'] ?? '',
'total_attempts' => (int)($login['LoginCount'] ?? 0) + (int)($login['LogoutCount'] ?? 0),
'failed_attempts' => (int)($login['LogoutCount'] ?? 0),
'success_attempts' => (int)($login['LoginCount'] ?? 0),
'subscription_count' => (int)($subscriptionCounts[$uid] ?? 0),
'notification_count' => (int)($notificationCounts[$uid] ?? 0),
'added_locations_count' => (int)($locationCounts[$uid] ?? 0),
'email_received_count' => (int)($email['totalEmailReceived'] ?? 0),
'email_received_otp_count' => (int)($email['totalOtpEmailReceived'] ?? 0),
'email_received_ews_count' => (int)($email['totalEwsAlertEmailReceived'] ?? 0),
'email_received_custom_notifications_count' =>
(int)($email['totalBasicCustomNotificationEmailReceived'] ?? 0)
+ (int)($email['totalAdvanceCustomNotificationEmailReceived'] ?? 0)
];
}
// Optional sorting on merged result
if ($sortBy) {
$allowedSort = [
'name',
'email',
'role',
'package_name',
'package_status',
'entity_name',
'user_status',
'last_login',
'last_failed_login',
'total_attempts',
'failed_attempts',
'success_attempts',
'subscription_count',
'notification_count',
'added_locations_count',
'email_received_count',
'email_received_otp_count',
'email_received_ews_count',
'email_received_custom_notifications_count'
];
if (in_array($sortBy, $allowedSort, true)) {
$dir = strtoupper($sortDir) === 'DESC' ? -1 : 1;
usort($result, function ($a, $b) use ($sortBy, $dir) {
$va = $a[$sortBy] ?? null;
$vb = $b[$sortBy] ?? null;
// Normalize dates for comparison if needed
if (in_array($sortBy, ['last_login', 'last_failed_login'], true)) {
$ta = $va ? (strtotime($va) ?: 0) : 0;
$tb = $vb ? (strtotime($vb) ?: 0) : 0;
return ($ta <=> $tb) * $dir;
}
// Numeric comparison for counts
if (is_numeric($va) && is_numeric($vb)) {
return ((int)$va <=> (int)$vb) * $dir;
}
return strcmp((string)$va, (string)$vb) * $dir;
});
}
}
return $result;
}
private function fetchLoginAttemptsBatch(array $userIds, ?string $fromDateTime, ?string $toDateTime): array
{
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$params = $userIds;
$dateConds = '';
if ($fromDateTime) {
$dateConds .= ' AND la.attempt_time >= ?';
$params[] = $fromDateTime;
}
if ($toDateTime) {
$dateConds .= ' AND la.attempt_time <= ?';
$params[] = $toDateTime;
}
$sql = "SELECT
c.oo_id AS user_id,
MAX(CASE WHEN la.status = 1 THEN la.attempt_time ELSE NULL END) AS lastLogin,
MAX(CASE WHEN la.status = 0 THEN la.attempt_time ELSE NULL END) AS lastFailedLogin,
COUNT(CASE WHEN la.status = 1 THEN 1 ELSE NULL END) AS LoginCount,
COUNT(CASE WHEN la.status = 0 THEN 1 ELSE NULL END) AS LogoutCount
FROM object_customer c
LEFT JOIN user_login_attempts la
ON c.email COLLATE utf8mb4_unicode_ci = la.customer_email COLLATE utf8mb4_unicode_ci
WHERE c.oo_id IN ($placeholders) $dateConds
GROUP BY c.oo_id";
$result = $this->db->executeQuery($sql, $params)->fetchAllAssociative();
return array_column($result, null, 'user_id');
}
private function fetchSubscriptionCountsBatch(array $userIds, ?int $fromTs, ?int $toTs): array
{
// EWS subscriptions count per user (object_MannedAlertSubscription)
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$params = $userIds;
$dateConds = '';
if ($fromTs !== null) {
$dateConds .= ' AND s.o_creationDate >= ?';
$params[] = $fromTs;
}
if ($toTs !== null) {
$dateConds .= ' AND s.o_creationDate <= ?';
$params[] = $toTs;
}
$sql = "SELECT s.creator__id AS user_id, COUNT(*) AS count
FROM object_MannedAlertSubscription s
WHERE s.creator__id IN ($placeholders) $dateConds
GROUP BY s.creator__id";
$result = $this->db->executeQuery($sql, $params)->fetchAllAssociative();
return array_column($result, 'count', 'user_id');
}
private function fetchLocationCountsBatch(array $userIds, ?string $fromDateTime = null, ?string $toDateTime = null): array
{
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$params = $userIds;
$dateConds = '';
if ($fromDateTime) {
$dateConds .= ' AND updated_at >= ?';
$params[] = $fromDateTime;
}
if ($toDateTime) {
$dateConds .= ' AND updated_at <= ?';
$params[] = $toDateTime;
}
$sql = "SELECT user_id, COUNT(*) AS count
FROM reporting_total_location
WHERE user_id IN ($placeholders) $dateConds
GROUP BY user_id";
$result = $this->db->executeQuery($sql, $params)->fetchAllAssociative();
return array_column($result, 'count', 'user_id');
}
private function fetchEmailStatsBatch(array $userIds, ?string $fromDateTime, ?string $toDateTime): array
{
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$params = $userIds;
$dateConds = '';
if ($fromDateTime) {
$dateConds .= ' AND sent_date >= ?';
$params[] = $fromDateTime;
}
if ($toDateTime) {
$dateConds .= ' AND sent_date <= ?';
$params[] = $toDateTime;
}
$sql = "SELECT
user_id,
COUNT(*) AS totalEmailReceived,
COUNT(CASE WHEN email_type = 'Customer' THEN 1 ELSE NULL END) AS totalOtpEmailReceived,
COUNT(CASE WHEN email_type = 'CustomNotification' THEN 1 ELSE NULL END) AS totalBasicCustomNotificationEmailReceived,
COUNT(CASE WHEN email_type = 'AdvanceCustomNotification' THEN 1 ELSE NULL END) AS totalAdvanceCustomNotificationEmailReceived,
COUNT(CASE WHEN email_type = 'EwsNotification' THEN 1 ELSE NULL END) AS totalEwsAlertEmailReceived
FROM reporting_received_emails
WHERE user_id IN ($placeholders) $dateConds
GROUP BY user_id";
$result = $this->db->executeQuery($sql, $params)->fetchAllAssociative();
return array_column($result, null, 'user_id');
}
private function fetchNotificationCountsBatch(array $userIds, ?string $fromDateTime, ?string $toDateTime): array
{
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$params = $userIds;
$dateConds = '';
if ($fromDateTime) {
$dateConds .= ' AND created_at >= ?';
$params[] = $fromDateTime;
}
if ($toDateTime) {
$dateConds .= ' AND created_at <= ?';
$params[] = $toDateTime;
}
$sql = "SELECT user_id, COUNT(*) AS count
FROM reporting_custom_notification
WHERE user_id IN ($placeholders) $dateConds
GROUP BY user_id";
$result = $this->db->executeQuery($sql, $params)->fetchAllAssociative();
return array_column($result, 'count', 'user_id');
}
}

src/Model/UserDashboardModel.php line 1320