<?php
namespace AmeliaBooking\Infrastructure\Repository\User;
use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\User\AbstractUser;
use AmeliaBooking\Domain\Entity\User\Provider;
use AmeliaBooking\Domain\Factory\User\ProviderFactory;
use AmeliaBooking\Domain\Repository\User\ProviderRepositoryInterface;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Domain\ValueObjects\String\DayOffType;
use AmeliaBooking\Domain\ValueObjects\String\Status;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\Licence\Licence;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\ExtrasTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\AppointmentsTable;
use AmeliaBooking\Infrastructure\Licence\LicenceConstants;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\WPUsersTable;
/**
* Class ProviderRepository
*
* @package AmeliaBooking\Infrastructure\Repository
*/
class ProviderRepository extends UserRepository implements ProviderRepositoryInterface
{
public const FACTORY = ProviderFactory::class;
/** @var string */
protected $providerWeekDayTable;
/** @var string */
protected $providerPeriodTable;
/** @var string */
protected $providerPeriodServiceTable;
/** @var string */
protected $providerPeriodLocationTable;
/** @var string */
protected $providerTimeOutTable;
/** @var string */
protected $providerSpecialDayTable;
/** @var string */
protected $providerSpecialDayPeriodTable;
/** @var string */
protected $providerSpecialDayPeriodServiceTable;
/** @var string */
protected $providerSpecialDayPeriodLocationTable;
/** @var string */
protected $providerDayOffTable;
/** @var string */
protected $providerServicesTable;
/** @var string */
protected $providerLocationTable;
/** @var string */
protected $serviceTable;
/** @var string */
protected $locationTable;
/** @var string */
protected $providerViewsTable;
/** @var string */
protected $providersGoogleCalendarTable;
/** @var string */
protected $providersOutlookCalendarTable;
/**
* @param Connection $connection
* @param string $table
* @param string $providerWeekDayTable
* @param string $providerPeriodTable
* @param string $providerPeriodServiceTable
* @param string $providerPeriodLocationTable
* @param string $providerTimeOutTable
* @param string $providerSpecialDayTable
* @param string $providerSpecialDayPeriodTable
* @param string $providerSpecialDayPeriodServiceTable
* @param string $providerSpecialDayPeriodLocationTable
* @param string $providerDayOffTable
* @param string $providerServicesTable
* @param string $providerLocationTable
* @param string $serviceTable
* @param string $locationTable
* @param string $providerViewsTable
* @param string $providersGoogleCalendarTable
* @param string $providersOutlookCalendarTable
*/
public function __construct(
Connection $connection,
$table,
$providerWeekDayTable,
$providerPeriodTable,
$providerPeriodServiceTable,
$providerPeriodLocationTable,
$providerTimeOutTable,
$providerSpecialDayTable,
$providerSpecialDayPeriodTable,
$providerSpecialDayPeriodServiceTable,
$providerSpecialDayPeriodLocationTable,
$providerDayOffTable,
$providerServicesTable,
$providerLocationTable,
$serviceTable,
$locationTable,
$providerViewsTable,
$providersGoogleCalendarTable,
$providersOutlookCalendarTable
) {
parent::__construct($connection, $table);
$this->providerWeekDayTable = $providerWeekDayTable;
$this->providerPeriodTable = $providerPeriodTable;
$this->providerPeriodServiceTable = $providerPeriodServiceTable;
$this->providerPeriodLocationTable = $providerPeriodLocationTable;
$this->providerTimeOutTable = $providerTimeOutTable;
$this->providerSpecialDayTable = $providerSpecialDayTable;
$this->providerSpecialDayPeriodTable = $providerSpecialDayPeriodTable;
$this->providerSpecialDayPeriodServiceTable = $providerSpecialDayPeriodServiceTable;
$this->providerSpecialDayPeriodLocationTable = $providerSpecialDayPeriodLocationTable;
$this->providerDayOffTable = $providerDayOffTable;
$this->providerServicesTable = $providerServicesTable;
$this->providerLocationTable = $providerLocationTable;
$this->serviceTable = $serviceTable;
$this->locationTable = $locationTable;
$this->providerViewsTable = $providerViewsTable;
$this->providersGoogleCalendarTable = $providersGoogleCalendarTable;
$this->providersOutlookCalendarTable = $providersOutlookCalendarTable;
}
/**
* @param int $id
*
* @return Provider|null
* @throws QueryExecutionException
*/
public function getById($id)
{
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
u.status AS user_status,
u.externalId AS external_id,
u.firstName AS user_firstName,
u.lastName AS user_lastName,
u.email AS user_email,
u.note AS note,
u.phone AS phone,
u.countryPhoneIso AS user_countryPhoneIso,
u.pictureFullPath AS picture_full_path,
u.pictureThumbPath AS picture_thumb_path,
u.zoomUserId AS user_zoom_user_id,
u.appleCalendarId as user_apple_calendar_id,
u.googleCalendarId as user_google_calendar_id,
u.outlookCalendarId as user_outlook_calendar_id,
u.employeeAppleCalendar as user_employee_apple_calendar,
u.stripeConnect AS user_stripeConnect,
u.translations AS user_translations,
u.timeZone AS user_timeZone,
u.badgeId AS badge_id,
gd.id AS google_calendar_id,
gd.token AS google_calendar_token,
gd.calendarId AS google_calendar_calendar_id,
gd.insertPendingAppointments AS google_calendar_insert_pending_appointments,
gd.includeBufferTime AS google_calendar_include_buffer_time,
gd.title AS google_calendar_title,
gd.description AS google_calendar_description,
gd.accountId AS google_calendar_account_id,
gd.syncId AS google_calendar_sync_id,
od.id AS outlook_calendar_id,
od.token AS outlook_calendar_token,
od.calendarId AS outlook_calendar_calendar_id,
od.insertPendingAppointments AS outlook_calendar_insert_pending_appointments,
od.includeBufferTime AS outlook_calendar_include_buffer_time,
od.title AS outlook_calendar_title,
od.description AS outlook_calendar_description,
od.accountId AS outlook_calendar_account_id,
od.syncId AS outlook_calendar_sync_id
FROM {$this->table} u
LEFT JOIN {$this->providersGoogleCalendarTable} gd ON gd.userId = u.id
LEFT JOIN {$this->providersOutlookCalendarTable} od ON od.userId = u.id
WHERE u.type = :type AND u.id = :userId
ORDER BY u.id"
);
$type = AbstractUser::USER_ROLE_PROVIDER;
$statement->bindParam(':type', $type);
$statement->bindParam(':userId', $id);
$statement->execute();
$providerRows = [];
$serviceRows = [];
$providerServiceRows = [];
if ($statement->rowCount() === 0) {
return null;
}
while ($row = $statement->fetch()) {
$this->parseUserRow($row, $providerRows, $serviceRows, $providerServiceRows);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $providerRows, $serviceRows, $providerServiceRows)->getItem($id);
}
/**
*
* @return Collection
* @throws QueryExecutionException
*/
public function getAll()
{
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
u.status AS user_status,
u.externalId AS external_id,
u.firstName AS user_firstName,
u.lastName AS user_lastName,
u.email AS user_email,
u.note AS note,
u.phone AS phone,
u.countryPhoneIso AS user_countryPhoneIso,
u.pictureFullPath AS picture_full_path,
u.pictureThumbPath AS picture_thumb_path,
u.translations AS user_translations,
u.badgeId AS user_badge_id,
u.googleCalendarId as user_google_calendar_id,
u.outlookCalendarId as user_outlook_calendar_id,
lt.locationId AS user_locationId
FROM {$this->table} u
LEFT JOIN {$this->providerLocationTable} lt ON lt.userId = u.id
WHERE u.type = :type
ORDER BY CONCAT(u.firstName, ' ', u.lastName)"
);
$type = AbstractUser::USER_ROLE_PROVIDER;
$statement->bindParam(':type', $type);
$statement->execute();
$providerRows = [];
$serviceRows = [];
$providerServiceRows = [];
while ($row = $statement->fetch()) {
$this->parseUserRow($row, $providerRows, $serviceRows, $providerServiceRows);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $providerRows, $serviceRows, $providerServiceRows);
}
/**
* @param array $criteria
* @param int $itemsPerPage
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getFiltered($criteria, $itemsPerPage)
{
// Apply Lite license restriction
$criteria = $this->applyLiteLicenseRestriction($criteria);
try {
$wpUserTable = WPUsersTable::getTableName();
$params[':type'] = AbstractUser::USER_ROLE_PROVIDER;
$order = '';
if (!empty($criteria['sort'])) {
$orderColumn = 'CONCAT(u.firstName, \' \', u.lastName)';
$orderDirection = $criteria['sort'][0] === '-' ? 'DESC' : 'ASC';
$order = "ORDER BY {$orderColumn} {$orderDirection}";
}
$where = [];
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
$where1 = [];
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where1[] = "(
u.firstName LIKE {$param}
OR u.lastName LIKE {$param}
OR u.email LIKE {$param}
OR u.phone LIKE {$param}
OR u.note LIKE {$param}
OR wpUser.display_name LIKE {$param}
OR u.id LIKE {$param}
)";
$termIndex++;
}
$where1 = implode(' AND ', $where1);
$where[] = "u.id IN(
SELECT DISTINCT(user.id)
FROM {$this->table} user
LEFT JOIN {$wpUserTable} wpUser ON user.externalId = wpUser.ID
WHERE ({$where1})
)";
}
if (!empty($criteria['services'])) {
$queryServices = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param = ':service' . $index;
$queryServices[] = $param;
$params[$param] = $value;
}
$where[] = "u.id IN (
SELECT pst.userId FROM {$this->providerServicesTable} pst
WHERE pst.userId = u.id AND pst.serviceId IN (" . implode(', ', $queryServices) . ')
)';
}
if (!empty($criteria['providerStatus'])) {
$params[':providerStatus'] = $criteria['providerStatus'];
$where[] = 'u.status = :providerStatus';
}
if (!empty($criteria['providers'])) {
$queryProviders = [];
$index = 0;
foreach ((array)$criteria['providers'] as $value) {
$param = ':provider' . $index++;
$queryProviders[] = $param;
$params[$param] = (int)$value;
}
$where[] = 'u.id IN (' . implode(', ', $queryProviders) . ')';
}
if (!empty($criteria['location'])) {
$queryLocations = [];
foreach ((array)$criteria['location'] as $index => $value) {
$param = ':location' . $index;
$queryLocations[] = $param;
$params[$param] = $value;
}
$where[] = "u.id IN (
SELECT plt.userId FROM {$this->providerLocationTable} plt
WHERE plt.userId = u.id AND plt.locationId IN ( " . implode(', ', $queryLocations) . "))";
}
$where[] = "u.status NOT LIKE 'disabled'";
$where = ' AND ' . implode(' AND ', $where);
$limit = $this->getLimit(
!empty($criteria['page']) ? (int)$criteria['page'] : 0,
(int)$itemsPerPage
);
$statement = $this->connection->prepare(
"SELECT u.*
FROM {$this->table} u
WHERE u.type = :type $where
{$order}
{$limit}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$items = new Collection();
foreach ($rows as $row) {
$items->addItem(call_user_func([static::FACTORY, 'create'], $row), $row['id']);
}
return $items;
}
/**
*
* @param array $criteria
* @param bool $filterForLicence
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getWithSchedule($criteria, $filterForLicence = true)
{
// Apply Lite license restriction
$criteria = $filterForLicence ? $this->applyLiteLicenseRestriction($criteria) : $criteria;
$providerRows = [];
$serviceRows = [];
$providerServiceRows = [];
$where = ['u.type = :type'];
$userParams = [];
$params[':type'] = AbstractUser::USER_ROLE_PROVIDER;
$queryProviders = [];
if (!empty($criteria['dates'][0])) {
$criteria['dates'][0] = explode(' ', $criteria['dates'][0])[0];
}
if (!empty($criteria['dates'][1])) {
$criteria['dates'][1] = explode(' ', $criteria['dates'][1])[0];
}
if (!empty($criteria['providerStatus'])) {
$params[':providerStatus'] = $criteria['providerStatus'];
$where[] = 'u.status = :providerStatus';
}
if (isset($criteria['show'])) {
$params[':show'] = $criteria['show'];
$where[] = 'u.show = :show';
}
if (!empty($criteria['providers'])) {
$index = 0;
foreach ((array)$criteria['providers'] as $value) {
$param = ':provider' . $index++;
$queryProviders[] = $param;
$userParams[$param] = (int)$value;
}
}
if (!empty($criteria['locations'])) {
$queryLocations = [];
foreach ((array)$criteria['locations'] as $index => $value) {
$param = ':location' . $index;
$queryLocations[] = $param;
$params[$param] = $value;
}
$where[] = "u.id IN (
SELECT plt.userId FROM {$this->providerLocationTable} plt
WHERE plt.userId = u.id AND plt.locationId IN ( " . implode(', ', $queryLocations) . "))";
}
if (!empty($criteria['services'])) {
$queryServices = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param = ':service' . $index;
$queryServices[] = $param;
$params[$param] = $value;
}
$where[] = "u.id IN (
SELECT pst.userId FROM {$this->providerServicesTable} pst
WHERE pst.userId = u.id AND pst.serviceId IN (" . implode(', ', $queryServices) . ')
)';
}
if ($queryProviders) {
$where[] = 'u.id IN (' . implode(', ', $queryProviders) . ')';
}
$dotJoinQuery = '';
if (isset($criteria['dates'][0], $criteria['dates'][1])) {
$dotJoinQuery = "AND (dot.repeat = 1 OR (
dot.startDate BETWEEN :from1 AND :to1 OR
dot.endDate BETWEEN :from2 AND :to2 OR
(dot.startDate <= :from3 AND dot.endDate >= :to3)
))";
$userParams[':from1'] = $userParams[':from2'] = $userParams[':from3'] = $criteria['dates'][0];
$userParams[':to1'] = $userParams[':to2'] = $userParams[':to3'] = $criteria['dates'][1];
} elseif (isset($criteria['dates'][0])) {
$dotJoinQuery = "AND (dot.repeat = 1 OR (dot.startDate >= :from1 OR dot.endDate >= :from2))";
$userParams[':from1'] = $userParams[':from2'] = $criteria['dates'][0];
}
$where = 'WHERE ' . implode(' AND ', $where);
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
u.status AS user_status,
u.externalId AS external_id,
u.firstName AS user_firstName,
u.lastName AS user_lastName,
u.email AS user_email,
u.zoomUserId AS user_zoom_user_id,
u.appleCalendarId AS user_apple_calendar_id,
u.googleCalendarId as user_google_calendar_id,
u.outlookCalendarId as user_outlook_calendar_id,
u.employeeAppleCalendar AS user_employee_apple_calendar,
u.stripeConnect AS user_stripeConnect,
u.countryPhoneIso AS user_countryPhoneIso,
u.note AS note,
u.description AS description,
u.phone AS phone,
u.pictureFullPath AS picture_full_path,
u.pictureThumbPath AS picture_thumb_path,
u.translations AS user_translations,
u.timeZone AS user_timeZone,
u.badgeId AS badge_id,
u.show AS user_show,
plt.locationId AS user_locationId,
pst.serviceId AS service_id,
pst.price AS service_price,
pst.customPricing AS service_customPricing,
pst.minCapacity AS service_minCapacity,
pst.maxCapacity AS service_maxCapacity,
dot.id AS dayOff_id,
dot.name AS dayOff_name,
dot.startDate AS dayOff_startDate,
dot.endDate AS dayOff_endDate,
dot.repeat AS dayOff_repeat,
dot.type AS dayOff_type
FROM {$this->table} u
LEFT JOIN {$this->providerServicesTable} pst ON pst.userId = u.id
LEFT JOIN {$this->providerLocationTable} plt ON plt.userId = u.id
LEFT JOIN {$this->providerDayOffTable} dot ON (dot.userId = u.id OR dot.userId IS null) {$dotJoinQuery}
{$where}
ORDER BY CONCAT(u.firstName, ' ', u.lastName), u.id"
);
$statement->execute(array_merge($params, $userParams));
while ($row = $statement->fetch()) {
$this->parseUserRow($row, $providerRows, $serviceRows, $providerServiceRows);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Collection $providers */
$providers = call_user_func(
[static::FACTORY, 'createCollection'],
$providerRows,
$serviceRows,
$providerServiceRows
);
if (!$providers->length()) {
return new Collection();
}
$where = 'WHERE wdt.userId IN (' . implode(', ', $providers->keys()) . ')';
try {
$statement = $this->connection->prepare(
"SELECT
wdt.id AS weekDay_id,
wdt.userId AS user_id,
wdt.dayIndex AS weekDay_dayIndex,
wdt.startTime AS weekDay_startTime,
wdt.endTime As weekDay_endTime,
tot.id AS timeOut_id,
tot.startTime AS timeOut_startTime,
tot.endTime AS timeOut_endTime,
pt.id AS period_id,
pt.startTime AS period_startTime,
pt.endTime AS period_endTime,
pt.locationId AS period_locationId,
pst.id AS periodService_id,
pst.serviceId AS periodService_serviceId,
plt.id AS periodLocation_id,
plt.locationId AS periodLocation_locationId
FROM {$this->providerWeekDayTable} wdt
LEFT JOIN {$this->providerTimeOutTable} tot ON tot.weekDayId = wdt.id
LEFT JOIN {$this->providerPeriodTable} pt ON pt.weekDayId = wdt.id
LEFT JOIN {$this->providerPeriodServiceTable} pst ON pst.periodId = pt.id
LEFT JOIN {$this->providerPeriodLocationTable} plt ON plt.periodId = pt.id
{$where}"
);
$statement->execute();
while ($row = $statement->fetch()) {
$this->parseUserRow($row, $providerRows, $serviceRows, $providerServiceRows);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Collection $providersWithWeekDays */
$providersWithWeekDays = call_user_func(
[static::FACTORY, 'createCollection'],
$providerRows,
$serviceRows,
$providerServiceRows
);
/** @var Provider $provider */
foreach ($providersWithWeekDays->getItems() as $provider) {
$providers->getItem(
$provider->getId()->getValue()
)->setWeekDayList($provider->getWeekDayList());
}
$where = ['sdt.userId IN (' . implode(', ', $providers->keys()) . ')'];
$sdtParams = [];
if (isset($criteria['dates'][0], $criteria['dates'][1])) {
$where[] = "(
sdt.startDate BETWEEN :from1 AND :to1 OR
sdt.endDate BETWEEN :from2 AND :to2 OR
(sdt.startDate <= :from3 AND sdt.endDate >= :to3)
)";
$sdtParams[':from1'] = $sdtParams[':from2'] = $sdtParams[':from3'] = $criteria['dates'][0];
$sdtParams[':to1'] = $sdtParams[':to2'] = $sdtParams[':to3'] = $criteria['dates'][1];
} elseif (isset($criteria['dates'][0])) {
$where[] = "(
sdt.startDate >= :from1 OR
sdt.endDate >= :from2
)";
$sdtParams[':from1'] = $sdtParams[':from2'] = $criteria['dates'][0];
}
$where = 'WHERE ' . implode(' AND ', $where);
try {
$statement = $this->connection->prepare(
"SELECT
sdt.id AS specialDay_id,
sdt.userId AS user_id,
sdt.startDate AS specialDay_startDate,
sdt.endDate As specialDay_endDate,
sdpt.id AS specialDayPeriod_id,
sdpt.startTime AS specialDayPeriod_startTime,
sdpt.endTime AS specialDayPeriod_endTime,
sdpt.locationId AS specialDayPeriod_locationId,
sdpst.id AS specialDayPeriodService_id,
sdpst.serviceId AS specialDayPeriodService_serviceId,
sdplt.id AS specialDayPeriodLocation_id,
sdplt.locationId AS specialDayPeriodLocation_locationId
FROM {$this->providerSpecialDayTable} sdt
LEFT JOIN {$this->providerSpecialDayPeriodTable} sdpt ON sdpt.specialDayId = sdt.id
LEFT JOIN {$this->providerSpecialDayPeriodServiceTable} sdpst ON sdpst.periodId = sdpt.id
LEFT JOIN {$this->providerSpecialDayPeriodLocationTable} sdplt ON sdplt.periodId = sdpt.id
{$where}
ORDER BY sdt.startDate ASC"
);
$statement->execute($sdtParams);
while ($row = $statement->fetch()) {
$this->parseUserRow($row, $providerRows, $serviceRows, $providerServiceRows);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Collection $providersWithSpecialDays */
$providersWithSpecialDays = call_user_func(
[static::FACTORY, 'createCollection'],
$providerRows,
$serviceRows,
$providerServiceRows
);
/** @var Provider $provider */
foreach ($providersWithSpecialDays->getItems() as $provider) {
$providers->getItem(
$provider->getId()->getValue()
)->setSpecialDayList($provider->getSpecialDayList());
}
if (!empty($criteria['fetchCalendars'])) {
$where = 'WHERE u.id IN (' . implode(', ', $providers->keys()) . ')';
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
gd.id AS google_calendar_id,
gd.token AS google_calendar_token,
gd.calendarId AS google_calendar_calendar_id,
gd.insertPendingAppointments AS google_calendar_insert_pending_appointments,
gd.includeBufferTime AS google_calendar_include_buffer_time,
gd.title AS google_calendar_title,
gd.description AS google_calendar_description,
gd.accountId AS google_calendar_account_id,
gd.syncId AS google_calendar_sync_id,
od.id AS outlook_calendar_id,
od.token AS outlook_calendar_token,
od.calendarId AS outlook_calendar_calendar_id,
od.insertPendingAppointments AS outlook_calendar_insert_pending_appointments,
od.includeBufferTime AS outlook_calendar_include_buffer_time,
od.title AS outlook_calendar_title,
od.description AS outlook_calendar_description,
od.accountId AS outlook_calendar_account_id,
od.syncId AS outlook_calendar_sync_id
FROM {$this->table} u
LEFT JOIN {$this->providersGoogleCalendarTable} gd ON gd.userId = u.id
LEFT JOIN {$this->providersOutlookCalendarTable} od ON od.userId = u.id
{$where}"
);
$statement->execute();
while ($row = $statement->fetch()) {
$this->parseUserRow($row, $providerRows, $serviceRows, $providerServiceRows);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Collection $providersWithCalendars */
$providersWithCalendars = call_user_func(
[static::FACTORY, 'createCollection'],
$providerRows,
$serviceRows,
$providerServiceRows
);
/** @var Provider $provider */
foreach ($providersWithCalendars->getItems() as $provider) {
/** @var Provider $targetProvider */
$targetProvider = $providers->getItem(
$provider->getId()->getValue()
);
if ($provider->getGoogleCalendar()) {
$targetProvider->setGoogleCalendar($provider->getGoogleCalendar());
}
if ($provider->getOutlookCalendar()) {
$targetProvider->setOutlookCalendar($provider->getOutlookCalendar());
}
}
}
return $providers;
}
/**
* @param array $criteria
*
* @return mixed
* @throws QueryExecutionException
*/
public function getCount($criteria)
{
// Apply Lite license restriction
$criteria = $this->applyLiteLicenseRestriction($criteria);
$params = [
':type' => AbstractUser::USER_ROLE_PROVIDER,
':visibleStatus' => Status::VISIBLE,
':hiddenStatus' => Status::HIDDEN,
];
try {
$wpUserTable = WPUsersTable::getTableName();
$where = [];
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
$where1 = [];
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where1[] = "(
u.firstName LIKE {$param}
OR u.lastName LIKE {$param}
OR u.email LIKE {$param}
OR u.phone LIKE {$param}
OR u.note LIKE {$param}
OR wpUser.display_name LIKE {$param}
OR u.id LIKE {$param}
)";
$termIndex++;
}
$where1 = implode(' AND ', $where1);
$where[] = "u.id IN(
SELECT DISTINCT(user.id)
FROM {$this->table} user
LEFT JOIN {$wpUserTable} wpUser ON user.externalId = wpUser.ID
WHERE ({$where1})
)";
}
if (!empty($criteria['services'])) {
$queryServices = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param = ':service' . $index;
$queryServices[] = $param;
$params[$param] = $value;
}
$where[] = "u.id IN (
SELECT pst.userId FROM {$this->providerServicesTable} pst
WHERE pst.userId = u.id AND pst.serviceId IN (" . implode(', ', $queryServices) . ')
)';
}
if (!empty($criteria['location'])) {
$queryLocations = [];
foreach ((array)$criteria['location'] as $index => $value) {
$param = ':location' . $index;
$queryLocations[] = $param;
$params[$param] = $value;
}
$where[] = "u.id IN (
SELECT plt.userId FROM {$this->providerLocationTable} plt
WHERE plt.userId = u.id AND plt.locationId IN ( " . implode(', ', $queryLocations) . "))";
}
if (!empty($criteria['providers'])) {
$queryProviders = [];
foreach ((array)$criteria['providers'] as $index => $value) {
$param = ':provider' . $index;
$queryProviders[] = $param;
$params[$param] = $value;
}
$where[] = 'u.id IN (' . implode(', ', $queryProviders) . ')';
}
$where = $where ? ' AND ' . implode(' AND ', $where) : '';
$statement = $this->connection->prepare(
"SELECT COUNT(*) AS count
FROM {$this->table} u
WHERE u.type = :type AND u.status IN (:visibleStatus, :hiddenStatus) $where"
);
$statement->execute($params);
$row = $statement->fetch()['count'];
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $row;
}
/**
* @param $criteria
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getWithServicesAndExtras($criteria)
{
$extrasTable = ExtrasTable::getTableName();
$params = [
':type' => AbstractUser::USER_ROLE_PROVIDER,
':userStatus' => Status::VISIBLE,
':serviceStatus' => Status::VISIBLE
];
$where = [];
foreach ((array)$criteria as $index => $value) {
$params[':service' . $index] = $value['serviceId'];
$params[':provider' . $index] = $value['providerId'];
$where[] = "(s.id = :service$index AND u.id = :provider$index)";
}
$where = $where ? ' AND ' . implode(' OR ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
u.firstName AS user_firstName,
u.lastName AS user_lastName,
u.email AS user_email,
u.timeZone AS user_timeZone,
u.translations AS user_translations,
st.serviceId AS service_id,
st.price AS service_price,
st.customPricing AS service_customPricing,
st.minCapacity AS service_minCapacity,
st.maxCapacity AS service_maxCapacity,
s.name AS service_name,
s.description AS service_description,
s.color AS service_color,
s.status AS service_status,
s.categoryId AS service_categoryId,
s.duration AS service_duration,
s.bringingAnyone AS service_bringingAnyone,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.aggregatedPrice AS service_aggregatedPrice,
s.recurringPayment AS service_recurringPayment,
s.translations AS service_translations,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.deposit AS service_deposit,
s.depositPayment AS service_depositPayment,
s.depositPerPerson AS service_depositPerPerson,
s.fullPayment AS service_fullPayment,
e.id AS extra_id,
e.name AS extra_name,
e.price AS extra_price,
e.maxQuantity AS extra_maxQuantity,
e.duration AS extra_duration,
e.description AS extra_description,
e.position AS extra_position,
e.aggregatedPrice AS extra_aggregatedPrice
FROM {$this->table} u
INNER JOIN {$this->providerServicesTable} st ON st.userId = u.id
INNER JOIN {$this->serviceTable} s ON s.id = st.serviceId
LEFT JOIN {$extrasTable} e ON e.serviceId = s.id
WHERE u.status = :userStatus AND s.status = :serviceStatus AND u.type = :type $where"
);
$statement->execute($params);
$providerRows = [];
$serviceRows = [];
$providerServiceRows = [];
while ($row = $statement->fetch()) {
$this->parseUserRow($row, $providerRows, $serviceRows, $providerServiceRows);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $providerRows, $serviceRows, $providerServiceRows);
}
/**
* Returns array of available (currently working) Providers where keys are Provider ID's and array values are
* Working Hours Data
*
* @param $dayIndex
*
* @return array
* @throws QueryExecutionException
*/
public function getAvailable($dayIndex, $providerTimeZone)
{
$currentDateTime = DateTimeService::getNowDateTime();
$currentDateTimeInTimeZone = DateTimeService::getCustomDateTimeObjectInTimeZone($currentDateTime, $providerTimeZone);
$currentDateTimeSQL = "STR_TO_DATE('" . $currentDateTimeInTimeZone->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
$params = [
':dayIndex' => $dayIndex === 0 ? 7 : $dayIndex,
':type' => AbstractUser::USER_ROLE_PROVIDER,
':providerTimeZone' => $providerTimeZone,
':WPtimeZone' => DateTimeService::getTimeZone()->getName()
];
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
u.firstName AS user_firstName,
u.lastName AS user_lastName,
u.translations AS user_translations,
u.timeZone AS user_timeZone,
wdt.id AS weekDay_id,
wdt.dayIndex AS weekDay_dayIndex,
wdt.startTime AS weekDay_startTime,
wdt.endTime AS weekDay_endTime,
pt.id AS period_id,
pt.startTime AS period_startTime,
pt.endTime AS period_endTime
FROM {$this->table} u
LEFT JOIN {$this->providerWeekDayTable} wdt ON wdt.userId = u.id
LEFT JOIN {$this->providerPeriodTable} pt ON pt.weekDayId = wdt.id
WHERE u.type = :type AND
wdt.dayIndex = :dayIndex AND
(COALESCE(u.timeZone, :WPtimeZone) = :providerTimeZone) AND
((
{$currentDateTimeSQL} >= wdt.startTime AND
{$currentDateTimeSQL} <= wdt.endTime AND
pt.startTime IS NULL AND
pt.endTime IS NULL
) OR (
{$currentDateTimeSQL} >= pt.startTime AND
{$currentDateTimeSQL} <= pt.endTime AND
pt.startTime IS NOT NULL AND
pt.endTime IS NOT NULL
))"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
if (!array_key_exists($row['user_id'], $result)) {
$result[$row['user_id']] = $row;
}
$result[$row['user_id']]['periods'][$row['period_id']] = [
'startTime' => $row['period_startTime'],
'endTime' => $row['period_endTime']
];
}
return $result;
}
/**
* Returns array of available (currently working) Providers where keys are Provider ID's and array values are
* Working Hours Data on special day
*
* @return array
* @throws QueryExecutionException
*/
public function getOnSpecialDay()
{
$dateTimeNowString = DateTimeService::getNowDateTime();
$currentDateTime = "STR_TO_DATE('" . $dateTimeNowString . "', '%Y-%m-%d %H:%i:%s')";
$currentDateString = DateTimeService::getNowDate();
$params = [
':type' => AbstractUser::USER_ROLE_PROVIDER
];
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
u.firstName AS user_firstName,
u.lastName AS user_lastName,
sdpt.startTime AS sdp_startTime,
sdpt.endTime AS sdp_endTime,
IF (
{$currentDateTime} >= STR_TO_DATE(CONCAT(sdt.startDate, ' 00:00:00'), '%Y-%m-%d %H:%i:%s') AND
{$currentDateTime} <= DATE_ADD(STR_TO_DATE(CONCAT(sdt.endDate, ' 00:00:00'), '%Y-%m-%d %H:%i:%s'), INTERVAL 1 DAY) AND
{$currentDateTime} >= STR_TO_DATE(CONCAT('{$currentDateString}', ' ', sdpt.startTime), '%Y-%m-%d %H:%i:%s') AND
{$currentDateTime} <= STR_TO_DATE(CONCAT('{$currentDateString}', ' ', sdpt.endTime), '%Y-%m-%d %H:%i:%s'),
1,
0
) AS available
FROM {$this->table} u
INNER JOIN {$this->providerSpecialDayTable} sdt ON sdt.userId = u.id
INNER JOIN {$this->providerSpecialDayPeriodTable} sdpt ON sdpt.specialDayId = sdt.id
WHERE u.type = :type AND
STR_TO_DATE('{$currentDateString}', '%Y-%m-%d') BETWEEN sdt.startDate AND sdt.endDate
"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
$dateTimeNow = DateTimeService::getNowDateTimeObject();
foreach ($rows as $row) {
$dateTimeEnd = DateTimeService::getCustomDateTimeObject($currentDateString . " " . $row['sdp_endTime']);
if (!array_key_exists($row['user_id'], $result) && $dateTimeNow <= $dateTimeEnd) {
$result[$row['user_id']] = $row;
}
}
return $result;
}
/**
* @param $dayIndex
*
* @return array
* @throws QueryExecutionException
*/
public function getOnBreak($dayIndex)
{
$currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTime() . "', '%Y-%m-%d %H:%i:%s')";
$params = [
':dayIndex' => $dayIndex === 0 ? 7 : $dayIndex,
':type' => AbstractUser::USER_ROLE_PROVIDER
];
try {
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
u.firstName AS user_firstName,
u.lastName AS user_lastName,
wdt.id AS weekDay_id,
wdt.dayIndex AS weekDay_dayIndex,
wdt.startTime AS weekDay_startTime,
wdt.endTime As weekDay_endTime,
tot.id AS timeOut_id,
tot.startTime AS timeOut_startTime,
tot.endTime AS timeOut_endTime
FROM {$this->table} u
LEFT JOIN {$this->providerWeekDayTable} wdt ON wdt.userId = u.id
LEFT JOIN {$this->providerTimeOutTable} tot ON tot.weekDayId = wdt.id
WHERE u.type = :type AND
wdt.dayIndex = :dayIndex AND
{$currentDateTime} >= wdt.startTime AND
{$currentDateTime} <= wdt.endTime AND
{$currentDateTime} >= tot.startTime AND
{$currentDateTime} <= tot.endTime"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$result[$row['user_id']] = $row;
}
return $result;
}
/**
* @return array
* @throws QueryExecutionException
*/
public function getOnVacation()
{
$currentDateTime = explode(' ', DateTimeService::getNowDateTime())[0];
$params = [
':type' => AbstractUser::USER_ROLE_PROVIDER,
':currentDate' => $currentDateTime,
':dayOffType' => DayOffType::DAY_OFF
];
try {
$statement = $this->connection->prepare(
"SELECT
u.id,
u.firstName,
u.lastName,
dot.startDate,
dot.endDate,
dot.name
FROM {$this->table} u
LEFT JOIN {$this->providerDayOffTable} dot ON dot.userId = u.id AND dot.type = :dayOffType
WHERE u.type = :type AND
:currentDate BETWEEN dot.startDate AND dot.endDate"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$result[$row['id']] = $row;
}
return $result;
}
/**
* Return an array of providers with the number of appointments for the given date period.
* Keys of the array are Provider IDs.
*
* @param $criteria
*
* @return array
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getAllNumberOfAppointments($criteria)
{
$appointmentTable = AppointmentsTable::getTableName();
$params = [];
$where = [];
if ($criteria['dates']) {
$where[] = "(a.bookingStart BETWEEN :bookingFrom AND :bookingTo)";
$params[':bookingFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$params[':bookingTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
}
if (isset($criteria['status'])) {
$where[] = 'u.status = :status';
$params[':status'] = $criteria['status'];
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
u.id,
CONCAT(u.firstName, ' ', u.lastName) AS name,
COUNT(a.providerId) AS appointments
FROM {$this->table} u
INNER JOIN {$appointmentTable} a ON u.id = a.providerId
$where
GROUP BY providerId"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$result[$row['id']] = $row;
}
return $result;
}
/**
* Return an array of providers with the number of views for the given date period.
* Keys of the array are Providers IDs.
*
* @param $criteria
*
* @return array
* @throws QueryExecutionException
*/
public function getAllNumberOfViews($criteria)
{
$params = [];
$where = [];
if ($criteria['dates']) {
$where[] = "(DATE_FORMAT(pv.date, '%Y-%m-%d') BETWEEN :bookingFrom AND :bookingTo)";
$params[':bookingFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$params[':bookingTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
}
if (isset($criteria['status'])) {
$where[] = 'u.status = :status';
$params[':status'] = $criteria['status'];
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
u.id,
CONCAT(u.firstName, ' ', u.lastName) as name,
SUM(pv.views) AS views
FROM {$this->table} u
INNER JOIN {$this->providerViewsTable} pv ON pv.userId = u.id
$where
GROUP BY u.id"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$result[$row['id']] = $row;
}
return $result;
}
/**
* @param $providerId
*
* @return bool
* @throws QueryExecutionException
*/
public function addViewStats($providerId)
{
$date = DateTimeService::getNowDate();
$params = [
':userId' => $providerId,
':date' => $date,
':views' => 1
];
try {
// Check if there is already data for this provider for this date
$statement = $this->connection->prepare(
"SELECT COUNT(*) AS count
FROM {$this->providerViewsTable} AS pv
WHERE pv.userId = :userId
AND pv.date = :date"
);
$statement->bindParam(':userId', $providerId);
$statement->bindParam(':date', $date);
$statement->execute();
$count = $statement->fetch()['count'];
if (!$count) {
$statement = $this->connection->prepare(
"INSERT INTO {$this->providerViewsTable}
(`userId`, `date`, `views`)
VALUES
(:userId, :date, :views)"
);
} else {
$statement = $this->connection->prepare(
"UPDATE {$this->providerViewsTable} pv SET pv.views = pv.views + :views
WHERE pv.userId = :userId
AND pv.date = :date"
);
}
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to add data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return true;
}
/**
* @param array $serviceIds
*
* @return array
* @throws QueryExecutionException
*/
public function getProvidersServices($serviceIds = [])
{
// Apply Lite license restriction
$criteria = $this->applyLiteLicenseRestriction([]);
try {
$type = AbstractUser::USER_ROLE_PROVIDER;
$params = [
':type' => $type
];
$where = 'WHERE u.type = :type';
// Apply provider restriction for Lite license
if (!empty($criteria['providers'])) {
$queryProviders = [];
foreach ((array)$criteria['providers'] as $index => $value) {
$param = ':provider' . $index;
$queryProviders[] = $param;
$params[$param] = $value;
}
$where .= ' AND u.id IN (' . implode(', ', $queryProviders) . ')';
}
if (!empty($serviceIds)) {
$query = [];
foreach ((array)$serviceIds as $index => $value) {
$param = ':serviceId' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where .= ' AND st.serviceId IN (' . implode(', ', $query) . ')';
}
$statement = $this->connection->prepare(
"SELECT
u.id AS user_id,
st.serviceId AS service_id,
st.price AS service_price,
st.customPricing AS service_customPricing,
st.minCapacity AS service_minCapacity,
st.maxCapacity AS service_maxCapacity
FROM {$this->table} u
INNER JOIN {$this->providerServicesTable} st ON st.userId = u.id
{$where}
ORDER BY CONCAT(u.firstName, ' ', u.lastName)"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$userId = (int)$row['user_id'];
$serviceId = (int)$row['service_id'];
if (!array_key_exists($userId, $result) || !array_key_exists($serviceId, $result[$userId])) {
$result[$userId][$serviceId] = [
'price' => $row['service_price'],
'customPricing' => $row['service_customPricing'],
'minCapacity' => (int)$row['service_minCapacity'],
'maxCapacity' => (int)$row['service_maxCapacity'],
];
}
}
return $result;
}
/** @noinspection MoreThanThreeArgumentsInspection */
/**
* @param array $row
* @param array $providerRows
* @param array $serviceRows
* @param array $providerServiceRows
*
* @return void
*/
private function parseUserRow($row, &$providerRows, &$serviceRows, &$providerServiceRows)
{
$userId = (int)$row['user_id'];
$serviceId = isset($row['service_id']) ? (int)$row['service_id'] : null;
$extraId = isset($row['extra_id']) ? $row['extra_id'] : null;
$couponId = isset($row['coupon_id']) ? $row['coupon_id'] : null;
$googleCalendarId = isset($row['google_calendar_id']) ? $row['google_calendar_id'] : null;
$outlookCalendarId = isset($row['outlook_calendar_id']) ? $row['outlook_calendar_id'] : null;
$weekDayId = isset($row['weekDay_id']) ? $row['weekDay_id'] : null;
$timeOutId = isset($row['timeOut_id']) ? $row['timeOut_id'] : null;
$periodId = isset($row['period_id']) ? $row['period_id'] : null;
$periodServiceId = isset($row['periodService_id']) ? $row['periodService_id'] : null;
$periodLocationId = isset($row['periodLocation_id']) ? $row['periodLocation_id'] : null;
$specialDayId = isset($row['specialDay_id']) ? $row['specialDay_id'] : null;
$specialDayPeriodId = isset($row['specialDayPeriod_id']) ? $row['specialDayPeriod_id'] : null;
$specialDayPeriodServiceId = isset($row['specialDayPeriodService_id'])
? $row['specialDayPeriodService_id'] : null;
$specialDayPeriodLocationId = isset($row['specialDayPeriodLocation_id'])
? $row['specialDayPeriodLocation_id'] : null;
$dayOffId = isset($row['dayOff_id']) ? $row['dayOff_id'] : null;
if (!array_key_exists($userId, $providerRows)) {
$providerRows[$userId] = [
'id' => $userId,
'type' => 'provider',
'status' => isset($row['user_status']) ? $row['user_status'] : null,
'externalId' => isset($row['external_id']) ? $row['external_id'] : null,
'firstName' => $row['user_firstName'],
'lastName' => $row['user_lastName'],
'email' => $row['user_email'],
'note' => isset($row['note']) ? $row['note'] : null,
'description' => isset($row['description']) ? $row['description'] : null,
'phone' => isset($row['phone']) ? $row['phone'] : null,
'zoomUserId' => isset($row['user_zoom_user_id']) ? $row['user_zoom_user_id'] : null,
'stripeConnect' => isset($row['user_stripeConnect']) ? $row['user_stripeConnect'] : null,
'countryPhoneIso' => isset($row['user_countryPhoneIso']) ? $row['user_countryPhoneIso'] : null,
'locationId' => isset($row['user_locationId']) ? $row['user_locationId'] : null,
'pictureFullPath' => isset($row['picture_full_path']) ? $row['picture_full_path'] : null,
'pictureThumbPath' => isset($row['picture_thumb_path']) ? $row['picture_thumb_path'] : null,
'translations' => $row['user_translations'],
'googleCalendar' => [],
'outlookCalendar' => [],
'weekDayList' => [],
'dayOffList' => [],
'blockTimeList' => [],
'specialDayList' => [],
'serviceList' => [],
'timeZone' => isset($row['user_timeZone']) ? $row['user_timeZone'] : null,
'badgeId' => isset($row['badge_id']) ? $row['badge_id'] : null,
'appleCalendarId' => isset($row['user_apple_calendar_id']) ? $row['user_apple_calendar_id'] : null,
'googleCalendarId' => isset($row['user_google_calendar_id']) ? $row['user_google_calendar_id'] : null,
'outlookCalendarId' => isset($row['user_outlook_calendar_id']) ? $row['user_outlook_calendar_id'] : null,
'employeeAppleCalendar' => isset($row['user_employee_apple_calendar']) ? $row['user_employee_apple_calendar'] : null,
'show' => isset($row['user_show']) ? $row['user_show'] : 0,
];
}
$rowGoogleCalendarId = isset($row['google_calendar_calendar_id']) ? $row['google_calendar_calendar_id'] : null;
if (
$googleCalendarId &&
array_key_exists($userId, $providerRows) &&
(
empty($providerRows[$userId]['googleCalendar']) ||
(
empty($providerRows[$userId]['googleCalendar']['calendarId']) &&
!empty($rowGoogleCalendarId)
)
)
) {
$providerRows[$userId]['googleCalendar']['id'] = $row['google_calendar_id'];
$providerRows[$userId]['googleCalendar']['token'] = $row['google_calendar_token'];
$providerRows[$userId]['googleCalendar']['calendarId'] = $rowGoogleCalendarId;
$providerRows[$userId]['googleCalendar']['insertPendingAppointments'] = isset($row['google_calendar_insert_pending_appointments']) ?
$row['google_calendar_insert_pending_appointments'] :
null;
$providerRows[$userId]['googleCalendar']['includeBufferTime'] = isset($row['google_calendar_include_buffer_time']) ?
$row['google_calendar_include_buffer_time'] :
null;
$providerRows[$userId]['googleCalendar']['title'] = isset($row['google_calendar_title']) && $row['google_calendar_title'] ?
json_decode($row['google_calendar_title'], true) :
['appointment' => '%service_name%', 'event' => '%event_name%'];
$providerRows[$userId]['googleCalendar']['description'] = isset($row['google_calendar_description']) && $row['google_calendar_description'] ?
json_decode($row['google_calendar_description'], true) :
['appointment' => '', 'event' => ''];
$providerRows[$userId]['googleCalendar']['userId'] = $userId;
$providerRows[$userId]['googleCalendar']['calendarId'] = isset($row['google_calendar_calendar_id'])
? $row['google_calendar_calendar_id']
: null;
$providerRows[$userId]['googleCalendar']['accountId'] = isset($row['google_calendar_account_id'])
? $row['google_calendar_account_id']
: null;
$providerRows[$userId]['googleCalendar']['syncId'] = isset($row['google_calendar_sync_id'])
? $row['google_calendar_sync_id']
: null;
}
$rowOutlookCalendarId = isset($row['outlook_calendar_calendar_id']) ? $row['outlook_calendar_calendar_id'] : null;
if (
$outlookCalendarId &&
array_key_exists($userId, $providerRows) &&
(
empty($providerRows[$userId]['outlookCalendar']) ||
(
empty($providerRows[$userId]['outlookCalendar']['calendarId']) &&
!empty($rowOutlookCalendarId)
)
)
) {
$providerRows[$userId]['outlookCalendar']['id'] = $row['outlook_calendar_id'];
$providerRows[$userId]['outlookCalendar']['token'] = $row['outlook_calendar_token'];
$providerRows[$userId]['outlookCalendar']['calendarId'] = $rowOutlookCalendarId;
$providerRows[$userId]['outlookCalendar']['insertPendingAppointments'] = isset($row['outlook_calendar_insert_pending_appointments']) ?
$row['outlook_calendar_insert_pending_appointments'] :
null;
$providerRows[$userId]['outlookCalendar']['includeBufferTime'] = isset($row['outlook_calendar_include_buffer_time']) ?
$row['outlook_calendar_include_buffer_time'] :
null;
$providerRows[$userId]['outlookCalendar']['title'] = isset($row['outlook_calendar_title']) && $row['outlook_calendar_title'] ?
json_decode($row['outlook_calendar_title'], true) :
['appointment' => '%service_name%', 'event' => '%event_name%'];
$providerRows[$userId]['outlookCalendar']['description'] = isset($row['outlook_calendar_description']) && $row['outlook_calendar_description'] ?
json_decode($row['outlook_calendar_description'], true) :
['appointment' => '', 'event' => ''];
$providerRows[$userId]['outlookCalendar']['accountId'] = isset($row['outlook_calendar_account_id'])
? $row['outlook_calendar_account_id']
: null;
$providerRows[$userId]['outlookCalendar']['syncId'] = isset($row['outlook_calendar_sync_id'])
? $row['outlook_calendar_sync_id']
: null;
}
if (
$weekDayId &&
array_key_exists($userId, $providerRows) &&
!array_key_exists($weekDayId, $providerRows[$userId]['weekDayList'])
) {
$providerRows[$userId]['weekDayList'][$weekDayId] = [
'id' => $weekDayId,
'dayIndex' => $row['weekDay_dayIndex'],
'startTime' => $row['weekDay_startTime'],
'endTime' => $row['weekDay_endTime'],
'timeOutList' => [],
'periodList' => [],
];
}
if (
$periodId &&
$weekDayId &&
array_key_exists($userId, $providerRows) &&
array_key_exists($weekDayId, $providerRows[$userId]['weekDayList']) &&
!array_key_exists($periodId, $providerRows[$userId]['weekDayList'][$weekDayId]['periodList'])
) {
$providerRows[$userId]['weekDayList'][$weekDayId]['periodList'][$periodId] = [
'id' => $periodId,
'startTime' => $row['period_startTime'],
'endTime' => $row['period_endTime'],
'locationId' => $row['period_locationId'],
'periodServiceList' => [],
'periodLocationList' => [],
];
}
if (
$periodServiceId &&
$periodId &&
$weekDayId &&
array_key_exists($userId, $providerRows) &&
array_key_exists($weekDayId, $providerRows[$userId]['weekDayList']) &&
array_key_exists($periodId, $providerRows[$userId]['weekDayList'][$weekDayId]['periodList']) &&
!array_key_exists($periodServiceId, $providerRows[$userId]['weekDayList'][$weekDayId]['periodList'][$periodId]['periodServiceList'])
) {
$providerRows[$userId]['weekDayList'][$weekDayId]['periodList'][$periodId]['periodServiceList'][$periodServiceId] = [
'id' => $periodServiceId,
'serviceId' => $row['periodService_serviceId'],
];
}
if (
$periodLocationId &&
$periodId &&
$weekDayId &&
array_key_exists($userId, $providerRows) &&
array_key_exists($weekDayId, $providerRows[$userId]['weekDayList']) &&
array_key_exists($periodId, $providerRows[$userId]['weekDayList'][$weekDayId]['periodList']) &&
!array_key_exists($periodLocationId, $providerRows[$userId]['weekDayList'][$weekDayId]['periodList'][$periodId]['periodLocationList'])
) {
$providerRows[$userId]['weekDayList'][$weekDayId]['periodList'][$periodId]['periodLocationList'][$periodLocationId] = [
'id' => $periodLocationId,
'locationId' => $row['periodLocation_locationId'],
];
}
if (
$timeOutId &&
$weekDayId &&
array_key_exists($userId, $providerRows) &&
array_key_exists($weekDayId, $providerRows[$userId]['weekDayList']) &&
!array_key_exists($timeOutId, $providerRows[$userId]['weekDayList'][$weekDayId]['timeOutList'])
) {
$providerRows[$userId]['weekDayList'][$weekDayId]['timeOutList'][$timeOutId] = [
'id' => $timeOutId,
'startTime' => $row['timeOut_startTime'],
'endTime' => $row['timeOut_endTime'],
];
}
if (
$specialDayId &&
array_key_exists($userId, $providerRows) &&
!array_key_exists($specialDayId, $providerRows[$userId]['specialDayList'])
) {
$providerRows[$userId]['specialDayList'][$specialDayId] = [
'id' => $specialDayId,
'startDate' => $row['specialDay_startDate'],
'endDate' => $row['specialDay_endDate'],
'periodList' => [],
];
}
if (
$specialDayPeriodId &&
$specialDayId &&
array_key_exists($userId, $providerRows) &&
array_key_exists($specialDayId, $providerRows[$userId]['specialDayList']) &&
!array_key_exists($specialDayPeriodId, $providerRows[$userId]['specialDayList'][$specialDayId]['periodList'])
) {
$providerRows[$userId]['specialDayList'][$specialDayId]['periodList'][$specialDayPeriodId] = [
'id' => $specialDayPeriodId,
'startTime' => $row['specialDayPeriod_startTime'],
'endTime' => $row['specialDayPeriod_endTime'],
'locationId' => $row['specialDayPeriod_locationId'],
'periodServiceList' => [],
'periodLocationList' => [],
];
}
if (
$specialDayPeriodServiceId &&
$specialDayPeriodId &&
$specialDayId &&
array_key_exists($userId, $providerRows) &&
array_key_exists($specialDayId, $providerRows[$userId]['specialDayList']) &&
array_key_exists($specialDayPeriodId, $providerRows[$userId]['specialDayList'][$specialDayId]['periodList']) &&
!array_key_exists(
$specialDayPeriodServiceId,
$providerRows[$userId]['specialDayList'][$specialDayId]['periodList'][$specialDayPeriodId]['periodServiceList']
)
) {
$providerRows[$userId]['specialDayList'][$specialDayId]['periodList'][$specialDayPeriodId]['periodServiceList'][$specialDayPeriodServiceId] = [
'id' => $specialDayPeriodServiceId,
'serviceId' => $row['specialDayPeriodService_serviceId'],
];
}
if (
$specialDayPeriodLocationId &&
$specialDayPeriodId &&
$specialDayId &&
array_key_exists($userId, $providerRows) &&
array_key_exists($specialDayId, $providerRows[$userId]['specialDayList']) &&
array_key_exists($specialDayPeriodId, $providerRows[$userId]['specialDayList'][$specialDayId]['periodList']) &&
!array_key_exists(
$specialDayPeriodLocationId,
$providerRows[$userId]['specialDayList'][$specialDayId]['periodList'][$specialDayPeriodId]['periodLocationList']
)
) {
$providerRows[$userId]['specialDayList'][$specialDayId]['periodList'][$specialDayPeriodId]['periodLocationList'][$specialDayPeriodLocationId] = [
'id' => $specialDayPeriodLocationId,
'locationId' => $row['specialDayPeriodLocation_locationId'],
];
}
if (
$dayOffId &&
array_key_exists($userId, $providerRows) &&
!array_key_exists($dayOffId, $providerRows[$userId]['dayOffList'])
) {
if ($row['dayOff_type'] === DayOffType::DAY_OFF) {
$providerRows[$userId]['dayOffList'][$dayOffId] = [
'id' => $dayOffId,
'name' => $row['dayOff_name'],
'startDate' => $row['dayOff_startDate'],
'endDate' => $row['dayOff_endDate'],
'repeat' => $row['dayOff_repeat'],
];
}
if ($row['dayOff_type'] === DayOffType::BLOCK_TIME) {
$providerRows[$userId]['blockTimeList'][$dayOffId] = [
'id' => $dayOffId,
'name' => $row['dayOff_name'],
'startDate' => $row['dayOff_startDate'],
'endDate' => $row['dayOff_endDate'],
'repeat' => $row['dayOff_repeat'],
];
}
}
if (
$serviceId &&
!array_key_exists($serviceId, $serviceRows)
) {
$serviceRows[$serviceId] = [
'id' => $serviceId,
'customPricing' => isset($row['service_customPricing']) ? $row['service_customPricing'] : null,
'price' => $row['service_price'],
'minCapacity' => $row['service_minCapacity'],
'maxCapacity' => $row['service_maxCapacity'],
'name' => isset($row['service_name']) ? $row['service_name'] : null,
'description' => isset($row['service_description']) ? $row['service_description'] : null,
'color' => isset($row['service_color']) ? $row['service_color'] : null,
'status' => isset($row['service_status']) ? $row['service_status'] : null,
'categoryId' => isset($row['service_categoryId']) ? (int)$row['service_categoryId'] : null,
'duration' => isset($row['service_duration']) ? $row['service_duration'] : null,
'bringingAnyone' => isset($row['service_bringingAnyone']) ? $row['service_bringingAnyone'] : null,
'show' => isset($row['service_show']) ? $row['service_show'] : null,
'aggregatedPrice' => isset($row['service_aggregatedPrice']) ? $row['service_aggregatedPrice'] : null,
'pictureFullPath' => isset($row['service_picture_full']) ? $row['service_picture_full'] : null,
'pictureThumbPath' => isset($row['service_picture_thumb']) ? $row['service_picture_thumb'] : null,
'timeBefore' => isset($row['service_timeBefore']) ? $row['service_timeBefore'] : null,
'timeAfter' => isset($row['service_timeAfter']) ? $row['service_timeAfter'] : null,
'extras' => [],
'coupons' => [],
'settings' => isset($row['service_settings']) ? $row['service_settings'] : null,
'recurringCycle' => isset($row['service_recurringCycle']) ? $row['service_recurringCycle'] : null,
'recurringSub' => isset($row['service_recurringSub']) ? $row['service_recurringSub'] : null,
'recurringPayment' => isset($row['service_recurringPayment']) ? $row['service_recurringPayment'] : null,
'translations' => isset($row['service_translations']) ? $row['service_translations'] : null,
'deposit' => isset($row['service_deposit']) ? $row['service_deposit'] : 0,
'depositPayment' => isset($row['service_depositPayment']) ? $row['service_depositPayment'] : 'disabled',
'depositPerPerson' => isset($row['service_depositPerPerson']) ? $row['service_depositPerPerson'] : 1,
'fullPayment' => isset($row['service_fullPayment']) ? $row['service_fullPayment'] : false,
];
}
if (
$extraId &&
$serviceId &&
array_key_exists($serviceId, $serviceRows) &&
!array_key_exists($extraId, $serviceRows[$serviceId]['extras'])
) {
$serviceRows[$serviceId]['extras'][$extraId] = [
'id' => $extraId,
'name' => $row['extra_name'],
'price' => $row['extra_price'],
'maxQuantity' => $row['extra_maxQuantity'],
'position' => $row['extra_position'],
'aggregatedPrice' => $row['extra_aggregatedPrice'],
'description' => $row['extra_description']
];
}
if (
$couponId &&
$serviceId &&
array_key_exists($serviceId, $serviceRows) &&
!array_key_exists($couponId, $serviceRows[$serviceId]['coupons'])
) {
$serviceRows[$serviceId]['coupons'][$couponId] = [
'id' => $couponId,
'code' => $row['coupon_code'],
'discount' => $row['coupon_discount'],
'deduction' => $row['coupon_deduction'],
'limit' => $row['coupon_limit'],
'customerLimit' => $row['coupon_customerLimit'],
'status' => $row['coupon_status']
];
}
if ($serviceId && (!array_key_exists($userId, $providerServiceRows) || !array_key_exists($serviceId, $providerServiceRows[$userId]))) {
$providerServiceRows[$userId][$serviceId] = [
'price' => $row['service_price'],
'customPricing' => $row['service_customPricing'],
'minCapacity' => (int)$row['service_minCapacity'],
'maxCapacity' => (int)$row['service_maxCapacity']
];
}
}
/**
* @param int $userId
*
* @return mixed
* @throws QueryExecutionException
*/
public function deleteViewStats($userId)
{
$params = [
':userId' => $userId,
];
try {
$statement = $this->connection->prepare(
"DELETE FROM {$this->providerViewsTable} WHERE userId = :userId"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to delete data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* Get max service capacity for a specific provider
*
* @param int $providerId
* @param int $serviceId
*
* @return int|null
* @throws QueryExecutionException
*/
public function getMaxCapacityByServiceId($providerId, $serviceId)
{
$params = [
':userId' => $providerId,
':serviceId' => $serviceId
];
try {
$statement = $this->connection->prepare(
"SELECT maxCapacity
FROM {$this->providerServicesTable}
WHERE userId = :userId AND serviceId = :serviceId"
);
$statement->execute($params);
$row = $statement->fetch();
return $row ? (int)$row['maxCapacity'] : 0;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get max capacity from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* Batch update to clear googleCalendarId for all providers with a single SQL query
*
* @return void
* @throws QueryExecutionException
*/
public function clearGoogleCalendarIds()
{
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET googleCalendarId = NULL
WHERE googleCalendarId IS NOT NULL
AND type = 'provider'"
);
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to batch clear googleCalendarId in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* Batch update to clear outlookCalendarId for all providers with a single SQL query
*
* @return void
* @throws QueryExecutionException
*/
public function clearOutlookCalendarIds()
{
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET outlookCalendarId = NULL
WHERE outlookCalendarId IS NOT NULL
AND type = 'provider'"
);
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to batch clear outlookCalendarId in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* Apply license restriction for Lite license - forces only the first provider (smallest ID)
* to be returned regardless of filters
*
* @param array $criteria
* @return array Modified criteria array
* @throws QueryExecutionException
*/
private function applyLiteLicenseRestriction($criteria)
{
if (!Licence::hasLicenseAccess(LicenceConstants::STARTER)) {
try {
$params = [
':type' => AbstractUser::USER_ROLE_PROVIDER
];
$statement = $this->connection->prepare(
"SELECT u.id
FROM {$this->table} u
WHERE u.type = :type
AND u.status NOT LIKE 'disabled'
ORDER BY u.id ASC
LIMIT 1"
);
$statement->execute($params);
$result = $statement->fetch();
if ($result) {
// Force only this specific provider to be returned
$criteria['providers'] = [(int)$result['id']];
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to apply Lite license restriction in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
return $criteria;
}
/**
* Update the calendarId for a specific Google Calendar account row.
*
* @param int $accountId
* @param string|null $calendarId
*
* @return void
* @throws QueryExecutionException
*/
public function updateGoogleCalendarCalendarIdByAccountId($accountId, $calendarId)
{
try {
$statement = $this->connection->prepare(
"UPDATE {$this->providersGoogleCalendarTable}
SET calendarId = :calendarId
WHERE id = :accountId"
);
$statement->bindParam(':calendarId', $calendarId);
$statement->bindParam(':accountId', $accountId);
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update Google Calendar calendarId in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Get all Google Calendar accounts for a provider
*
* @param int $providerId
*
* @return array
* @throws QueryExecutionException
*/
/**
* Employees whose calendar selection lives on the user row, which is how the global Settings page
* connection stores it - those employees have no providers_to_*_calendar row of their own.
*
* @param string $calendarId
* @param string $column googleCalendarId|outlookCalendarId
*
* @return int[]
* @throws QueryExecutionException
*/
public function getProviderIdsByCalendarId($calendarId, $column): array
{
if (!in_array($column, ['googleCalendarId', 'outlookCalendarId'], true)) {
throw new QueryExecutionException('Unsupported calendar column ' . $column . ' in ' . __CLASS__);
}
try {
// Restricted to providers: the calendar columns live on the shared users table, so without
// this a row of any other role holding the same value would be handed back as an employee.
$statement = $this->connection->prepare(
"SELECT id FROM {$this->table} WHERE `{$column}` = :calendarId AND type = :type"
);
$statement->execute(
[
':calendarId' => $calendarId,
':type' => AbstractUser::USER_ROLE_PROVIDER,
]
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get providers by calendar in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return array_map(
function ($row) {
return (int)$row['id'];
},
$rows ?: []
);
}
/**
* @param int $providerId
* @param string $calendarId
* @param string $column googleCalendarId|outlookCalendarId
*
* @return bool
* @throws QueryExecutionException
*/
public function isProviderUsingCalendarId($providerId, $calendarId, $column): bool
{
if (!in_array($column, ['googleCalendarId', 'outlookCalendarId'], true)) {
throw new QueryExecutionException('Unsupported calendar column ' . $column . ' in ' . __CLASS__);
}
try {
$statement = $this->connection->prepare(
"SELECT id FROM {$this->table}
WHERE id = :id AND `{$column}` = :calendarId AND type = :type
LIMIT 1"
);
$statement->execute(
[
':id' => $providerId,
':calendarId' => $calendarId,
':type' => AbstractUser::USER_ROLE_PROVIDER,
]
);
return (bool)$statement->fetch();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to check provider calendar in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
public function getGoogleCalendarAccounts($providerId): array
{
$accounts = [];
try {
$statement = $this->connection->prepare(
"SELECT id, token, calendarId, blockedCalendars, accountId, syncId
FROM {$this->providersGoogleCalendarTable}
WHERE userId = :userId"
);
$statement->bindParam(':userId', $providerId);
$statement->execute();
while ($row = $statement->fetch()) {
$blockedCalendars = [];
if (isset($row['blockedCalendars']) && $row['blockedCalendars']) {
$blockedCalendars = json_decode($row['blockedCalendars'], true) ?: [];
}
$accounts[] = [
'id' => $row['id'],
'token' => $row['token'],
'calendarId' => $row['calendarId'],
'blockedCalendars' => $blockedCalendars,
'accountId' => isset($row['accountId']) ? $row['accountId'] : null,
'syncId' => isset($row['syncId']) ? $row['syncId'] : null,
];
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get Google Calendar accounts in ' . __CLASS__, $e->getCode(), $e);
}
return $accounts;
}
/**
* Get employee level Google Calendar "Event title and description" templates
*
* The account that has a calendar selected has priority, the same way the provider entity is
* hydrated in self::parseUserRow()
*
* @param int $providerId
*
* @return array
* @throws QueryExecutionException
*/
public function getGoogleCalendarSettings($providerId)
{
try {
$statement = $this->connection->prepare(
"SELECT calendarId, title, description
FROM {$this->providersGoogleCalendarTable}
WHERE userId = :userId"
);
$statement->bindParam(':userId', $providerId);
$statement->execute();
$settingsRow = null;
while ($row = $statement->fetch()) {
if ($settingsRow === null || (empty($settingsRow['calendarId']) && !empty($row['calendarId']))) {
$settingsRow = $row;
}
}
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to get Google Calendar settings in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
if ($settingsRow === null) {
return [];
}
return [
'title' => !empty($settingsRow['title']) ? json_decode($settingsRow['title'], true) : null,
'description' => !empty($settingsRow['description']) ? json_decode($settingsRow['description'], true) : null,
];
}
/**
* Update blocked calendars for a Google Calendar account
*
* @param int $accountId
* @param array $blockedCalendars
*
* @return void
* @throws QueryExecutionException
*/
public function updateGoogleCalendarBlockedCalendars($accountId, $blockedCalendars)
{
try {
$blockedCalendarsJson = json_encode($blockedCalendars);
if ($blockedCalendarsJson === false) {
throw new QueryExecutionException(
'Unable to encode blocked calendars to JSON in ' . __CLASS__ . '. Error: ' . json_last_error_msg()
);
}
$statement = $this->connection->prepare(
"UPDATE {$this->providersGoogleCalendarTable}
SET blockedCalendars = :blockedCalendars
WHERE id = :accountId"
);
$statement->bindParam(':blockedCalendars', $blockedCalendarsJson);
$statement->bindParam(':accountId', $accountId);
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update blocked calendars in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Update Google Calendar account token (used when refreshing expired tokens)
*
* @param int $accountId
* @param string $token
*
* @return bool
* @throws QueryExecutionException
*/
public function updateGoogleCalendarAccountToken($accountId, $token): bool
{
try {
$statement = $this->connection->prepare(
"UPDATE {$this->providersGoogleCalendarTable}
SET token = :token
WHERE id = :accountId"
);
$statement->bindParam(':token', $token);
$statement->bindParam(':accountId', $accountId);
$statement->execute();
return true;
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update Google Calendar account token in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Update Google Calendar account settings (insertPendingAppointments, includeBufferTime, title, description)
*
* @param int $providerId
* @param array $settings
*
* @return bool
* @throws QueryExecutionException
*/
public function updateGoogleCalendarAccountSettings($providerId, $settings)
{
try {
$updateFields = [];
$params = [':userId' => $providerId];
if (isset($settings['insertPendingAppointments'])) {
$updateFields[] = 'insertPendingAppointments = :insertPendingAppointments';
$params[':insertPendingAppointments'] = (bool)$settings['insertPendingAppointments'];
}
if (isset($settings['includeBufferTime'])) {
$updateFields[] = 'includeBufferTime = :includeBufferTime';
$params[':includeBufferTime'] = (bool)$settings['includeBufferTime'];
}
if (isset($settings['title'])) {
$updateFields[] = 'title = :title';
$params[':title'] = json_encode($settings['title']);
}
if (isset($settings['description'])) {
$updateFields[] = 'description = :description';
$params[':description'] = json_encode($settings['description']);
}
if (empty($updateFields)) {
return true;
}
$statement = $this->connection->prepare(
"UPDATE {$this->providersGoogleCalendarTable}
SET " . implode(', ', $updateFields) . "
WHERE userId = :userId"
);
foreach ($params as $key => $value) {
$statement->bindValue($key, $value);
}
return $statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update Google Calendar account settings in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Get all Google Calendar accounts for a provider
*
* @param int $providerId
*
* @return array
* @throws QueryExecutionException
*/
public function getOutlookCalendarAccounts($providerId): array
{
$accounts = [];
try {
$statement = $this->connection->prepare(
"SELECT id, token, calendarId, blockedCalendars, insertPendingAppointments, includeBufferTime, title, description,
accountId, syncId
FROM {$this->providersOutlookCalendarTable}
WHERE userId = :userId"
);
$statement->bindParam(':userId', $providerId);
$statement->execute();
while ($row = $statement->fetch()) {
$blockedCalendars = [];
if (isset($row['blockedCalendars']) && $row['blockedCalendars']) {
$blockedCalendars = json_decode($row['blockedCalendars'], true) ?: [];
}
$accounts[] = [
'id' => $row['id'],
'token' => $row['token'],
'calendarId' => $row['calendarId'],
'blockedCalendars' => $blockedCalendars,
'insertPendingAppointments' => isset($row['insertPendingAppointments']) ? $row['insertPendingAppointments'] : null,
'includeBufferTime' => isset($row['includeBufferTime']) ? $row['includeBufferTime'] : null,
'title' => isset($row['title']) && $row['title'] ? json_decode($row['title'], true) : null,
'description' => isset($row['description']) && $row['description'] ? json_decode($row['description'], true) : null,
'accountId' => isset($row['accountId']) ? $row['accountId'] : null,
'syncId' => isset($row['syncId']) ? $row['syncId'] : null,
];
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get Outlook Calendar accounts in ' . __CLASS__, $e->getCode(), $e);
}
return $accounts;
}
/**
* Update blocked calendars for a Outlook Calendar account
*
* @param int $accountId
* @param array $blockedCalendars
*
* @return void
* @throws QueryExecutionException
*/
public function updateOutlookCalendarBlockedCalendars($accountId, $blockedCalendars)
{
try {
$blockedCalendarsJson = json_encode($blockedCalendars);
if ($blockedCalendarsJson === false) {
throw new QueryExecutionException(
'Unable to encode blocked calendars to JSON in ' . __CLASS__ . '. Error: ' . json_last_error_msg()
);
}
$statement = $this->connection->prepare(
"UPDATE {$this->providersOutlookCalendarTable}
SET blockedCalendars = :blockedCalendars
WHERE id = :accountId"
);
$statement->bindParam(':blockedCalendars', $blockedCalendarsJson);
$statement->bindParam(':accountId', $accountId);
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update blocked calendars in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Update the calendarId for a specific Outlook Calendar account row
*
* @param int $accountId
* @param string|null $calendarId
*
* @return void
* @throws QueryExecutionException
*/
public function updateOutlookCalendarCalendarIdByAccountId($accountId, $calendarId)
{
try {
$statement = $this->connection->prepare(
"UPDATE {$this->providersOutlookCalendarTable}
SET calendarId = :calendarId
WHERE id = :accountId"
);
$statement->bindParam(':calendarId', $calendarId);
$statement->bindParam(':accountId', $accountId);
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update Outlook Calendar calendarId in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Update Outlook Calendar account token (used when refreshing expired tokens)
*
* @param int $accountId
* @param string $token
*
* @return void
* @throws QueryExecutionException
*/
public function updateOutlookCalendarAccountToken($accountId, $token): void
{
try {
$statement = $this->connection->prepare(
"UPDATE {$this->providersOutlookCalendarTable}
SET token = :token
WHERE id = :accountId"
);
$statement->bindParam(':token', $token);
$statement->bindParam(':accountId', $accountId);
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update Outlook Calendar account token in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Update Outlook Calendar account settings (insertPendingAppointments, includeBufferTime, title, description)
*
* @param int $providerId
* @param array $settings
*
* @return void
* @throws QueryExecutionException
*/
public function updateOutlookCalendarAccountSettings($providerId, $settings)
{
try {
$updateFields = [];
$params = [':userId' => $providerId];
if (isset($settings['insertPendingAppointments'])) {
$updateFields[] = 'insertPendingAppointments = :insertPendingAppointments';
$params[':insertPendingAppointments'] = (bool)$settings['insertPendingAppointments'];
}
if (isset($settings['includeBufferTime'])) {
$updateFields[] = 'includeBufferTime = :includeBufferTime';
$params[':includeBufferTime'] = (bool)$settings['includeBufferTime'];
}
if (isset($settings['title'])) {
$updateFields[] = 'title = :title';
$params[':title'] = json_encode($settings['title']);
}
if (isset($settings['description'])) {
$updateFields[] = 'description = :description';
$params[':description'] = json_encode($settings['description']);
}
if (empty($updateFields)) {
return;
}
$statement = $this->connection->prepare(
"UPDATE {$this->providersOutlookCalendarTable}
SET " . implode(', ', $updateFields) . "
WHERE userId = :userId"
);
foreach ($params as $key => $value) {
$statement->bindValue($key, $value);
}
$statement->execute();
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to update Outlook Calendar account settings in ' . __CLASS__ . ': ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
}