<?php
/**
 * @copyright © TMS-Plugins. All rights reserved.
 * @licence   See LICENCE.md for license details.
 */

namespace AmeliaBooking\Infrastructure\Repository\Payment;

use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Payment\Payment;
use AmeliaBooking\Domain\Factory\Payment\PaymentFactory;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Domain\Repository\Payment\PaymentRepositoryInterface;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\PackagesCustomersServicesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToExtrasTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Location\LocationsTable;

/**
 * Class PaymentRepository
 *
 * @package AmeliaBooking\Infrastructure\Repository\Payment
 */
class PaymentRepository extends AbstractRepository implements PaymentRepositoryInterface
{
    /** @var string */
    protected $appointmentsTable;

    /** @var string */
    protected $bookingsTable;

    /** @var string */
    protected $servicesTable;

    /** @var string */
    protected $usersTable;

    /** @var string */
    protected $eventsTable;

    /** @var string */
    protected $eventsProvidersTable;

    /** @var string */
    protected $eventsPeriodsTable;

    /** @var string */
    protected $customerBookingsToEventsPeriodsTable;

    /** @var string */
    protected $packagesTable;

    /** @var string */
    protected $packagesCustomersTable;

    /** @var string */
    protected $packagesCustomersServiceTable;


    /**
     * @param Connection $connection
     * @param string     $table
     * @param string     $appointmentsTable
     * @param string     $bookingsTable
     * @param string     $servicesTable
     * @param string     $usersTable
     * @param string     $eventsTable
     * @param string     $eventsProvidersTable
     * @param string     $eventsPeriodsTable
     * @param string     $customerBookingsToEventsPeriodsTable
     * @param string     $packagesTable
     * @param string     $packagesCustomersTable
     */
    public function __construct(
        Connection $connection,
        $table,
        $appointmentsTable,
        $bookingsTable,
        $servicesTable,
        $usersTable,
        $eventsTable,
        $eventsProvidersTable,
        $eventsPeriodsTable,
        $customerBookingsToEventsPeriodsTable,
        $packagesTable,
        $packagesCustomersTable
    ) {
        parent::__construct($connection, $table);

        $this->appointmentsTable = $appointmentsTable;
        $this->bookingsTable = $bookingsTable;
        $this->servicesTable = $servicesTable;
        $this->usersTable = $usersTable;
        $this->eventsTable = $eventsTable;
        $this->eventsProvidersTable = $eventsProvidersTable;
        $this->eventsPeriodsTable = $eventsPeriodsTable;
        $this->customerBookingsToEventsPeriodsTable = $customerBookingsToEventsPeriodsTable;
        $this->packagesTable = $packagesTable;
        $this->packagesCustomersTable = $packagesCustomersTable;
        $this->packagesCustomersServiceTable = PackagesCustomersServicesTable::getTableName();
    }

    const FACTORY = PaymentFactory::class;

    /**
     * @param Payment $entity
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function add($entity)
    {
        $data = $entity->toArray();

        $params = [
            ':customerBookingId' => $data['customerBookingId'] ? $data['customerBookingId'] : null,
            ':packageCustomerId' => $data['packageCustomerId'] ? $data['packageCustomerId'] : null,
            ':parentId'          => $data['parentId'] ? $data['parentId'] : null,
            ':amount'            => $data['amount'],
            ':dateTime'          => DateTimeService::getCustomDateTimeInUtc($data['dateTime']),
            ':status'            => $data['status'],
            ':gateway'           => $data['gateway'],
            ':gatewayTitle'      => $data['gatewayTitle'],
            ':data'              => $data['data'],
            ':entity'            => $data['entity'],
            ':created'           => DateTimeService::getNowDateTimeInUtc(),
            ':wcOrderId'         => !empty($data['wcOrderId']) ? $data['wcOrderId'] : null,
            ':transactionId'      => !empty($data['transactionId']) ? $data['transactionId'] : null,
            ':wcOrderItemId'     => !empty($data['wcOrderItemId']) ? $data['wcOrderItemId'] : null,
        ];

        if (!empty($data['invoiceNumber'])) {
            $params[':invoiceNumber'] = $data['invoiceNumber'];

            $invoiceNumberText = ":invoiceNumber";
        } else {
            $invoiceNumberText = "(SELECT MAX(CASE WHEN invoiceNumber IS NULL THEN 0 ELSE invoiceNumber END)+1 FROM {$this->table} p)";
        }

        if ($data['parentId']) {
            $params[':actionsCompleted'] = null;
        } else {
            $params[':actionsCompleted'] = !empty($data['actionsCompleted']) ? 1 : 0;
        }

        try {
            $statement = $this->connection->prepare(
                "INSERT INTO
                {$this->table} 
                (
                `customerBookingId`, `packageCustomerId`, `parentId`, `amount`, `dateTime`, `status`, `gateway`, `gatewayTitle`, `data`, `entity`, `actionsCompleted`, `created`, `wcOrderId`, `wcOrderItemId`, `transactionId`, `invoiceNumber`
                ) VALUES (
                :customerBookingId, :packageCustomerId, :parentId, :amount, :dateTime, :status, :gateway, :gatewayTitle, :data, :entity, :actionsCompleted, :created, :wcOrderId, :wcOrderItemId, :transactionId, {$invoiceNumberText}
                )"
            );

            $response = $statement->execute($params);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to add data in ' . __CLASS__, $e->getCode(), $e);
        }

        if (!$response) {
            throw new QueryExecutionException('Unable to add data in ' . __CLASS__);
        }

        return $this->connection->lastInsertId();
    }

    /**
     * @param int     $id
     * @param Payment $entity
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function update($id, $entity)
    {
        $data = $entity->toArray();

        $params = [
            ':customerBookingId' => $data['customerBookingId'] ? $data['customerBookingId'] : null,
            ':packageCustomerId' => $data['packageCustomerId'] ? $data['packageCustomerId'] : null,
            ':parentId'          => $data['parentId'] ? $data['parentId'] : null,
            ':amount'            => $data['amount'],
            ':dateTime'          => DateTimeService::getCustomDateTimeInUtc($data['dateTime']),
            ':status'            => $data['status'],
            ':gateway'           => $data['gateway'],
            ':gatewayTitle'      => $data['gatewayTitle'],
            ':data'              => $data['data'],
            ':transactionId'     => $data['transactionId'],
            ':id'                => $id,
        ];

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                `customerBookingId` = :customerBookingId,
                `packageCustomerId` = :packageCustomerId,
                `parentId`          = :parentId,
                `amount`            = :amount,
                `dateTime`          = :dateTime,
                `status`            = :status,
                `gateway`           = :gateway,
                `gatewayTitle`      = :gatewayTitle,
                `data`              = :data,
                `transactionId`     = :transactionId
                WHERE
                id = :id"
            );

            $response = $statement->execute($params);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to save data in ' . __CLASS__, $e->getCode(), $e);
        }

        if (!$response) {
            throw new QueryExecutionException('Unable to save data in ' . __CLASS__);
        }

        return $response;
    }

    /**
     * @param array $criteria
     *
     * @return Collection
     * @throws QueryExecutionException
     */
    public function getByCriteria($criteria)
    {
        $result = new Collection();

        $params = [];

        $where = [];

        if (!empty($criteria['bookingIds'])) {
            $queryBookings = [];

            foreach ($criteria['bookingIds'] as $index => $value) {
                $param = ':id' . $index;

                $queryBookings[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'customerBookingId IN (' . implode(', ', $queryBookings) . ')';
        }


        if (!empty($criteria['packageCustomerId'])) {
            $params[':packageCustomerId'] = $criteria['packageCustomerId'];
            $where[] = 'packageCustomerId = :packageCustomerId';
        }

        if (!empty($criteria['ids'])) {
            $queryIds = [];

            foreach ($criteria['ids'] as $index => $value) {
                $param = ':id' . $index;

                $queryIds[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'id IN (' . implode(', ', $queryIds) . ')';
        }

        $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    id AS id,
                    customerBookingId AS customerBookingId,
                    packageCustomerId AS packageCustomerId,
                    parentId AS parentId,
                    invoiceNumber AS invoiceNumber,
                    amount AS amount,
                    dateTime AS dateTime,
                    status AS status,
                    gateway AS gateway,
                    gatewayTitle AS gatewayTitle,
                    data AS data
                FROM {$this->table}
                {$where}"
            );

            $statement->execute($params);

            while ($row = $statement->fetch()) {
                $result->addItem(call_user_func([static::FACTORY, 'create'], $row), $row['id']);
            }
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
        }

        return $result;
    }

    /**
     * @param array $ids
     * @param boolean $invoice
     *
     * @return array
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getFiltered($ids, $invoice = false)
    {
        $params = [];
        $appointmentParams1 = [];
        $appointmentParams2 = [];
        $eventParams = [];
        $whereAppointment1 = [];
        $whereAppointment2 = [];
        $whereEvent = [];

        $basedOnDate = $invoice ? 'created' : 'dateTime';

        if (!empty($ids['appointment'])) {
            $appointment1PaymentsIds = [];

            foreach ($ids['appointment'] as $index => $value) {
                $param = ':sId' . $index;

                $appointment1PaymentsIds[] = $param;

                $params[$param] = $value;
            }

            $whereAppointment1[] = 'p.id IN (' . implode(', ', $appointment1PaymentsIds) . ')';
        }

        if (!empty($ids['package'])) {
            $appointment2PaymentsIds = [];

            foreach ($ids['package'] as $index => $value) {
                $param = ':pId' . $index;

                $appointment2PaymentsIds[] = $param;

                $params[$param] = $value;
            }

            $whereAppointment2[] = 'p.id IN (' . implode(', ', $appointment2PaymentsIds) . ')';
        }

        if (!empty($ids['event'])) {
            $eventsIds = [];

            foreach ($ids['event'] as $index => $value) {
                $param = ':eId' . $index;

                $eventsIds[] = $param;

                $params[$param] = $value;
            }

            $whereEvent[] = 'p.id IN (' . implode(', ', $eventsIds) . ')';
        }

        $whereAppointment1 = $whereAppointment1 ? ' AND ' . implode(' AND ', $whereAppointment1) : '';
        $whereAppointment2 = $whereAppointment2 ? ' AND ' . implode(' AND ', $whereAppointment2) : '';
        $whereEvent = $whereEvent ? ' AND ' . implode(' AND ', $whereEvent) : '';

        $customerBookingsExtrasTable = CustomerBookingsToExtrasTable::getTableName();
        $couponsTable = CouponsTable::getTableName();
        $locationsTable = LocationsTable::getTableName();

        $appointmentQuery1 = "SELECT
                p.id AS id,
                p.customerBookingId AS customerBookingId,
                NULL AS packageCustomerId,
                p.amount AS amount,
                p.invoiceNumber AS invoiceNumber,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.wcOrderId AS wcOrderId,
                p.wcOrderItemId AS wcOrderItemId,
                p.gateway AS gateway,
                p.gatewayTitle AS gatewayTitle,
                p.transactionId AS transactionId,
                p.parentId AS parentId,
                p.entity AS type,
                
                NULL AS packageId,
                cb.id AS bookingId,
                cb.price AS bookedPrice,
                cb.tax AS bookedTax,
                a.providerId AS providerId,
                cb.customerId AS customerId,
                cb.persons AS persons,
                cb.aggregatedPrice AS aggregatedPrice,
                cb.info AS info,

                cbe.id AS bookingExtra_id,
                cbe.quantity AS bookingExtra_quantity,
                cbe.price AS bookingExtra_price,
                cbe.aggregatedPrice AS bookingExtra_aggregatedPrice,
                cbe.tax AS bookingExtra_tax,
        
                c.id AS coupon_id,
                c.discount AS coupon_discount,
                c.deduction AS coupon_deduction,
                c.code AS coupon_code,
       
                a.serviceId AS serviceId,
                a.id AS appointmentId,
                a.bookingStart AS bookingStart,
                s.name AS bookableName,
                cu.firstName AS customerFirstName,
                cu.lastName AS customerLastName,
                cu.email AS customerEmail,
                cu.status AS customerStatus,
                pu.firstName AS providerFirstName,
                pu.lastName AS providerLastName,
                pu.email AS providerEmail,
                l.id AS locationId,
                l.name AS location_name,
                l.address AS location_address
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            LEFT JOIN {$customerBookingsExtrasTable} cbe ON cbe.customerBookingId = cb.id
            LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
            INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            INNER JOIN {$this->servicesTable} s ON s.id = a.serviceId
            INNER JOIN {$this->usersTable} cu ON cu.id = cb.customerId
            INNER JOIN {$this->usersTable} pu ON pu.id = a.providerId
            LEFT JOIN {$locationsTable} l ON l.id = a.locationId
            WHERE 1=1 {$whereAppointment1} GROUP BY p.customerBookingId ORDER BY p.id ASC";

        $appointmentQuery2 = "SELECT
                p.id AS id,
                NULL AS customerBookingId,
                p.packageCustomerId AS packageCustomerId,
                p.amount AS amount,
                p.invoiceNumber AS invoiceNumber,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.wcOrderId AS wcOrderId,
                p.wcOrderItemId AS wcOrderItemId,
                p.gateway AS gateway,
                p.gatewayTitle AS gatewayTitle,
                p.transactionId AS transactionId,
                p.parentId AS parentId,
                p.entity AS type,
                
                pc.packageId AS packageId,
                pc.id AS bookingId,
                pc.price AS bookedPrice,
                pc.tax AS bookedTax,
                NULL AS providerId,
                pc.customerId AS customerId,
                NULL AS persons,
                NULL AS aggregatedPrice,
                cb.info AS info,  
                
                NULL AS bookingExtra_id,
                NULL AS bookingExtra_quantity,
                NULL AS bookingExtra_price,
                NULL AS bookingExtra_aggregatedPrice,
                NULL AS bookingExtra_tax,
       
                c.id AS coupon_id,
                c.discount AS coupon_discount,
                c.deduction AS coupon_deduction,
                c.code AS coupon_code,
       
                NULL AS serviceId,
                NULL AS appointmentId,
                NULL AS bookingStart,
                pa.name AS bookableName,
                cu.firstName AS customerFirstName,
                cu.lastName AS customerLastName,
                cu.email AS customerEmail,
                cu.status AS customerStatus,
                '' AS providerFirstName,
                '' AS providerLastName,
                '' AS providerEmail,
                '' AS locationId,
                '' AS location_name,
                '' AS location_address
            FROM {$this->table} p
            INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
            INNER JOIN {$this->usersTable} cu ON cu.id = pc.customerId
            LEFT JOIN {$couponsTable} c ON c.id = pc.couponId
            INNER JOIN {$this->packagesTable} pa ON pa.id = pc.packageId
            INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
            LEFT JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
            LEFT JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            WHERE 1=1 {$whereAppointment2} ORDER BY p.id ASC";

        $eventQuery = "SELECT
                p.id AS id,
                p.customerBookingId AS customerBookingId,
                NULL AS packageCustomerId,
                p.amount AS amount,
                p.invoiceNumber AS invoiceNumber,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.wcOrderId AS wcOrderId,
                p.wcOrderItemId AS wcOrderItemId,
                p.gateway AS gateway,
                p.gatewayTitle AS gatewayTitle,
                p.transactionId AS transactionId,
                p.parentId AS parentId,
                p.entity AS type,
                
                NULL AS packageId,
                cb.id AS bookingId,
                cb.price AS bookedPrice,
                cb.tax AS bookedTax,
                NULL AS providerId,
                cb.customerId AS customerId,
                cb.persons AS persons,
                cb.aggregatedPrice AS aggregatedPrice,
                cb.info AS info,
                
                NULL AS bookingExtra_id,
                NULL AS bookingExtra_quantity,
                NULL AS bookingExtra_price,
                NULL AS bookingExtra_aggregatedPrice,
                NULL AS bookingExtra_tax,
       
                c.id AS coupon_id,
                c.discount AS coupon_discount,
                c.deduction AS coupon_deduction,
                c.code AS coupon_code,
       
                NULL AS serviceId,
                NULL AS appointmentId,
                NULL AS bookingStart,
                NULL AS bookableName,
                cu.firstName AS customerFirstName,
                cu.lastName AS customerLastName,
                cu.email AS customerEmail,
                cu.status AS customerStatus,
                NULL AS providerFirstName,
                NULL AS providerLastName,
                NULL AS providerEmail,
                l.id AS locationId,
                l.name AS location_name,
                (CASE WHEN e.customlocation IS NOT NULL THEN e.customlocation ELSE l.address END) AS location_address
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
            INNER JOIN {$this->usersTable} cu ON cu.id = cb.customerId
            INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
            INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
            INNER JOIN {$this->eventsTable} e ON e.id = ep.eventId
            LEFT JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
            LEFT JOIN {$locationsTable} l ON l.id = e.locationId
            WHERE 1=1 {$whereEvent} ORDER BY p.id ASC";

        $paymentQuery = '';

        if (!empty($ids['appointment'])) {
            $paymentQuery = "({$appointmentQuery1})";

            $params = array_merge($params, $appointmentParams1);
        }

        if (!empty($ids['package'])) {
            $paymentQuery = $paymentQuery ? $paymentQuery . " UNION ALL ({$appointmentQuery2})" : "({$appointmentQuery2})";

            $params = array_merge($params, $appointmentParams2);
        }

        if (!empty($ids['event'])) {
            $paymentQuery = $paymentQuery ? $paymentQuery . " UNION ALL ({$eventQuery})" : "({$eventQuery})";

            $params = array_merge($params, $eventParams);
        }

        try {
            $statement = $this->connection->prepare(
                "SELECT * FROM ({$paymentQuery}) payments
                ORDER BY {$basedOnDate}, id"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__, $e->getCode(), $e);
        }

        $result = [];

        foreach ($rows as &$row) {
            $customerInfo = $row['info'] ? json_decode($row['info'], true) : null;

            $result[(int)$row['id']] = [
                'id' =>  (int)$row['id'],
                'dateTime' =>  DateTimeService::getCustomDateTimeFromUtc($row['dateTime']),
                'created'  =>  DateTimeService::getCustomDateTimeFromUtc($row['created']),
                'bookingStart' =>  $row['bookingStart'] ?
                    DateTimeService::getCustomDateTimeFromUtc($row['bookingStart']) : null,
                'status' =>  $row['status'],
                'parentId' =>  $row['parentId'],
                'wcOrderId' =>  $row['wcOrderId'],
                'wcOrderItemId' =>  $row['wcOrderItemId'],
                'gateway' =>  $row['gateway'],
                'gatewayTitle' =>  $row['gatewayTitle'],
                'transactionId' =>  $row['transactionId'],
                'type' => $row['type'],
                'name' => $row['bookableName'],
                'customerBookingId' =>  (int)$row['customerBookingId'] ? (int)$row['customerBookingId'] : null,
                'packageCustomerId' =>  (int)$row['packageCustomerId'] ? (int)$row['packageCustomerId'] : null,
                'amount' =>  (float)$row['amount'],
                'invoiceNumber' => $row['invoiceNumber'],
                'providers' =>  (int)$row['providerId'] ? [
                    [
                        'id' => (int)$row['providerId'],
                        'fullName' => $row['providerFirstName'] . ' ' . $row['providerLastName'],
                        'email' => $row['providerEmail'],
                    ]
                ] : [],
                'location' => (int)$row['locationId'] || $row['location_address'] ?
                    [
                        'id' => (int)$row['locationId'],
                        'location_name' => $row['location_name'],
                        'location_address' => $row['location_address'],
                    ]
                 : null,
                'customerId' =>  (int)$row['customerId'],
                'serviceId' =>  (int)$row['serviceId'] ? (int)$row['serviceId'] : null,
                'appointmentId' =>  (int)$row['appointmentId'] ? (int)$row['appointmentId'] : null,
                'packageId' =>  (int)$row['packageId'] ? (int)$row['packageId'] : null,
                'bookedPrice' =>  $row['bookedPrice'] ? $row['bookedPrice'] : null,
                'bookedTax' =>  $row['bookedTax'] ? $row['bookedTax'] : null,
                'bookingId' => !empty($row['bookingId']) ? (int)$row['bookingId'] : null,
                'bookableName' => $row['bookableName'],
                'customerFirstName' => $customerInfo ? $customerInfo['firstName'] : $row['customerFirstName'],
                'customerLastName' => $customerInfo ? $customerInfo['lastName'] : $row['customerLastName'],
                'info' => $row['info'],
                'customerEmail' => $row['customerEmail'],
                'customerStatus' => $row['customerStatus'],
                'coupon' => !empty($row['coupon_id']) ? [
                    'id' => $row['coupon_id'],
                    'discount' => $row['coupon_discount'],
                    'deduction' => $row['coupon_deduction'],
                    'code' => $row['coupon_code']
                ] : null,
                'persons' => $row['persons'],
                'aggregatedPrice' => $row['aggregatedPrice'],
                'extras' =>  (int)$row['bookingExtra_id'] ? [
                    [
                        'id' => (int)$row['bookingExtra_id'],
                        'quantity' => $row['bookingExtra_quantity'],
                        'price' =>$row['bookingExtra_price'],
                        'aggregatedPrice' => $row['bookingExtra_aggregatedPrice'],
                        'tax' => $row['bookingExtra_tax'] ?: null
                    ]
                ] : [],
            ];
        }

        return $result;
    }

    /**
     * @param array $criteria
     * @param int   $itemsPerPage
     * @param boolean $invoice
     *
     * @return array
     * @throws QueryExecutionException
     */
    public function getFilteredIds($criteria, $itemsPerPage = null, $invoice = false)
    {
        $params = [];
        $appointmentParams1 = [];
        $appointmentParams2 = [];
        $eventParams = [];
        $whereAppointment1 = [];
        $whereAppointment2 = [];
        $whereEvent = [];

        $basedOnDate = $invoice ? 'created' : 'dateTime';

        if (!empty($criteria['dates'])) {
            $whereAppointment1[] = "(DATE_FORMAT(p.{$basedOnDate}, '%Y-%m-%d %H:%i:%s') BETWEEN :paymentAppointmentFrom1 AND :paymentAppointmentTo1)";
            $whereAppointment2[] = "(DATE_FORMAT(p.{$basedOnDate}, '%Y-%m-%d %H:%i:%s') BETWEEN :paymentAppointmentFrom2 AND :paymentAppointmentTo2)";
            $appointmentParams1[':paymentAppointmentFrom1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams2[':paymentAppointmentFrom2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams1[':paymentAppointmentTo1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
            $appointmentParams2[':paymentAppointmentTo2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);

            $whereEvent[] = "(DATE_FORMAT(p.{$basedOnDate}, '%Y-%m-%d %H:%i:%s') BETWEEN :paymentEventFrom AND :paymentEventTo)";
            $eventParams[':paymentEventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $eventParams[':paymentEventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
        }

        if (!empty($criteria['customerId'])) {
            $appointmentParams1[':customerAppointmentId1'] = $criteria['customerId'];
            $appointmentParams2[':customerAppointmentId2'] = $criteria['customerId'];
            $whereAppointment1[] = 'cb.customerId = :customerAppointmentId1';
            $whereAppointment2[] = 'pc.customerId = :customerAppointmentId2';

            $eventParams[':customerEventId'] = $criteria['customerId'];
            $whereEvent[] = 'cb.customerId = :customerEventId';
        }

        $eventsProvidersJoin = '';

        if (!empty($criteria['providerId'])) {
            $appointmentParams1[':providerAppointmentId1'] = $criteria['providerId'];
            $appointmentParams1[':providerAppointmentId2'] = $criteria['providerId'];
            $whereAppointment1[] = 'a.providerId = :providerAppointmentId1';
            $whereAppointment2[] = 'a.providerId = :providerAppointmentId2';

            $eventParams[':providerEventId'] = $criteria['providerId'];
            $whereEvent[] = 'epu.userId = :providerEventId';

            $eventsProvidersJoin = "
                INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
                INNER JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
            ";
        }

        if (!empty($criteria['services'])) {
            $queryServices1 = [];
            $queryServices2 = [];

            foreach ((array)$criteria['services'] as $index => $value) {
                $param1 = ':service0' . $index;
                $param2 = ':service1' . $index;
                $queryServices1[] = $param1;
                $queryServices2[] = $param2;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
            }

            $whereAppointment1[] = 'a.serviceId IN (' . implode(', ', $queryServices1) . ')';
            $whereAppointment2[] = 'a.serviceId IN (' . implode(', ', $queryServices2) . ')';
        }

        $appointments2ProvidersServicesJoin = '';

        if (!empty($criteria['providerId']) || !empty($criteria['services'])) {
            $appointments2ProvidersServicesJoin = "
                INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
                INNER JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
                INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            ";
        }

        if (!empty($criteria['status'])) {
            $appointmentParams1[':statusAppointment1'] = $criteria['status'];
            $appointmentParams2[':statusAppointment2'] = $criteria['status'];
            $whereAppointment1[] = 'p.status = :statusAppointment1';
            $whereAppointment2[] = 'p.status = :statusAppointment2';

            $eventParams[':statusEvent'] = $criteria['status'];
            $whereEvent[] = 'p.status = :statusEvent';
        }

        if (!empty($criteria['packages'])) {
            $queryPackages = [];

            foreach ((array)$criteria['packages'] as $index => $value) {
                $param = ':package' . $index;
                $queryPackages[] = $param;
                $appointmentParams2[$param] = $value;
            }

            $whereAppointment2[] = "p.packageCustomerId IN (SELECT pc.id
              FROM {$this->packagesCustomersTable} pc
              WHERE pc.packageId IN (" . implode(', ', $queryPackages) . '))';
        }

        if (!empty($criteria['events'])) {
            $queryEvents = [];

            foreach ((array)$criteria['events'] as $index => $value) {
                $param = ':event' . $index;
                $queryEvents[] = $param;
                $eventParams[$param] = $value;
            }

            $whereEvent[] = "p.customerBookingId IN (SELECT cbe.customerBookingId
              FROM {$this->eventsTable} e
              INNER JOIN {$this->eventsPeriodsTable} ep ON ep.eventId = e.id
              INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.eventPeriodId = ep.id 
              WHERE e.id IN (" . implode(', ', $queryEvents) . '))';
        }

        $whereAppointment1 = $whereAppointment1 ? ' AND ' . implode(' AND ', $whereAppointment1) : '';
        $whereAppointment2 = $whereAppointment2 ? ' AND ' . implode(' AND ', $whereAppointment2) : '';
        $whereEvent = $whereEvent ? ' AND ' . implode(' AND ', $whereEvent) : '';

        $groupBy = '';
        if ($invoice) {
            $groupBy = 'GROUP BY IFNULL(invoiceNumber, id)';
        }

        $appointmentQuery1 = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.invoiceNumber AS invoiceNumber,
                'appointment' AS type
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            WHERE 1=1 {$whereAppointment1} GROUP BY p.customerBookingId ORDER BY p.id ASC";

        $appointmentQuery2 = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.invoiceNumber AS invoiceNumber,
                'package' AS type
            FROM {$this->table} p
            INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
            {$appointments2ProvidersServicesJoin}
            WHERE 1=1 {$whereAppointment2} GROUP BY p.packageCustomerId ORDER BY p.id ASC";

        $eventQuery = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.invoiceNumber AS invoiceNumber,
                'event' AS type
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
            {$eventsProvidersJoin}
            WHERE 1=1 {$whereEvent} GROUP BY p.customerBookingId ORDER BY p.id ASC";

        $result = [
            'appointment' => [],
            'event'       => [],
            'package'     => [],
        ];

        if (isset($criteria['events'], $criteria['services'])) {
            return $result;
        } elseif (isset($criteria['services'])) {
            $paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2})";
            $params = array_merge($params, $appointmentParams1, $appointmentParams2);
        } elseif (isset($criteria['events'])) {
            $paymentQuery = "({$eventQuery})";
            $params = array_merge($params, $eventParams);
        } elseif (isset($criteria['packages'])) {
            $paymentQuery = "({$appointmentQuery2})";
            $params = array_merge($params, $appointmentParams2);
        } else {
            $paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2}) UNION ALL ({$eventQuery})";
            $params = array_merge($params, $appointmentParams1, $appointmentParams2, $eventParams);
        }

        $limit = $this->getLimit(
            !empty($criteria['page']) ? (int)$criteria['page'] : 0,
            (int)$itemsPerPage
        );

        try {
            $statement = $this->connection->prepare(
                "SELECT * FROM ({$paymentQuery}) payments
                {$groupBy}
                ORDER BY {$basedOnDate}, id
                {$limit}"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__, $e->getCode(), $e);
        }

        foreach ($rows as $row) {
            $result[$row['type']][] = (int)$row['id'];
        }

        return $result;
    }

    /**
     * @param array $criteria
     * @param boolean $invoice
     *
     * @return array
     * @throws QueryExecutionException
     */
    public function getFilteredIdsCount($criteria, $invoice = false)
    {
        $params = [];
        $appointmentParams1 = [];
        $appointmentParams2 = [];
        $eventParams = [];
        $whereAppointment1 = [];
        $whereAppointment2 = [];
        $whereEvent = [];

        $basedOnDate = $invoice ? 'created' : 'dateTime';

        if (!empty($criteria['dates'])) {
            $whereAppointment1[] = "(DATE_FORMAT(p.{$basedOnDate}, '%Y-%m-%d %H:%i:%s') BETWEEN :paymentAppointmentFrom1 AND :paymentAppointmentTo1)";
            $whereAppointment2[] = "(DATE_FORMAT(p.{$basedOnDate}, '%Y-%m-%d %H:%i:%s') BETWEEN :paymentAppointmentFrom2 AND :paymentAppointmentTo2)";
            $appointmentParams1[':paymentAppointmentFrom1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams2[':paymentAppointmentFrom2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams1[':paymentAppointmentTo1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
            $appointmentParams2[':paymentAppointmentTo2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);

            $whereEvent[] = "(DATE_FORMAT(p.{$basedOnDate}, '%Y-%m-%d %H:%i:%s') BETWEEN :paymentEventFrom AND :paymentEventTo)";
            $eventParams[':paymentEventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $eventParams[':paymentEventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
        }

        if (!empty($criteria['customerId'])) {
            $appointmentParams1[':customerAppointmentId1'] = $criteria['customerId'];
            $appointmentParams2[':customerAppointmentId2'] = $criteria['customerId'];
            $whereAppointment1[] = 'cb.customerId = :customerAppointmentId1';
            $whereAppointment2[] = 'pc.customerId = :customerAppointmentId2';

            $eventParams[':customerEventId'] = $criteria['customerId'];
            $whereEvent[] = 'cb.customerId = :customerEventId';
        }

        $eventsProvidersJoin = '';

        if (!empty($criteria['providerId'])) {
            $appointmentParams1[':providerAppointmentId1'] = $criteria['providerId'];
            $appointmentParams1[':providerAppointmentId2'] = $criteria['providerId'];
            $whereAppointment1[] = 'a.providerId = :providerAppointmentId1';
            $whereAppointment2[] = 'a.providerId = :providerAppointmentId2';

            $eventParams[':providerEventId'] = $criteria['providerId'];
            $whereEvent[] = 'epu.userId = :providerEventId';

            $eventsProvidersJoin = "
                INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
                INNER JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
            ";
        }

        if (!empty($criteria['services'])) {
            $queryServices1 = [];
            $queryServices2 = [];

            foreach ((array)$criteria['services'] as $index => $value) {
                $param1 = ':service0' . $index;
                $param2 = ':service1' . $index;
                $queryServices1[] = $param1;
                $queryServices2[] = $param2;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
            }

            $whereAppointment1[] = 'a.serviceId IN (' . implode(', ', $queryServices1) . ')';
            $whereAppointment2[] = 'a.serviceId IN (' . implode(', ', $queryServices2) . ')';
        }

        $appointments2ProvidersServicesJoin = '';

        if (!empty($criteria['providerId']) || !empty($criteria['services'])) {
            $appointments2ProvidersServicesJoin = "
                INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
                INNER JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
                INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            ";
        }

        if (!empty($criteria['status'])) {
            $appointmentParams1[':statusAppointment1'] = $criteria['status'];
            $appointmentParams2[':statusAppointment2'] = $criteria['status'];
            $whereAppointment1[] = 'p.status = :statusAppointment1';
            $whereAppointment2[] = 'p.status = :statusAppointment2';

            $eventParams[':statusEvent'] = $criteria['status'];
            $whereEvent[] = 'p.status = :statusEvent';
        }

        if (!empty($criteria['events'])) {
            $queryEvents = [];

            foreach ((array)$criteria['events'] as $index => $value) {
                $param = ':event' . $index;
                $queryEvents[] = $param;
                $eventParams[$param] = $value;
            }

            $whereEvent[] = "p.customerBookingId IN (SELECT cbe.customerBookingId
              FROM {$this->eventsTable} e
              INNER JOIN {$this->eventsPeriodsTable} ep ON ep.eventId = e.id
              INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.eventPeriodId = ep.id 
              WHERE e.id IN (" . implode(', ', $queryEvents) . '))';
        }

        if (!empty($criteria['packages'])) {
            $queryPackages = [];

            foreach ((array)$criteria['packages'] as $index => $value) {
                $param = ':package' . $index;
                $queryPackages[] = $param;
                $appointmentParams2[$param] = $value;
            }

            $whereAppointment2[] = "p.packageCustomerId IN (SELECT pc.id
              FROM {$this->packagesCustomersTable} pc
              WHERE pc.packageId IN (" . implode(', ', $queryPackages) . '))';
        }

        $whereAppointment1 = $whereAppointment1 ? ' AND ' . implode(' AND ', $whereAppointment1) : '';
        $whereAppointment2 = $whereAppointment2 ? ' AND ' . implode(' AND ', $whereAppointment2) : '';
        $whereEvent = $whereEvent ? ' AND ' . implode(' AND ', $whereEvent) : '';

        $groupBy = '';
        if ($invoice) {
            $groupBy = 'GROUP BY IFNULL(invoiceNumber, id)';
        }


        $appointmentQuery1 = "SELECT
                COUNT(DISTINCT(p.customerBookingId)) AS appointmentsCount1,
                0 AS appointmentsCount2,
                0 AS eventsCount,
                p.id AS id,
                p.invoiceNumber AS invoiceNumber,
                p.created AS created,
                p.dateTime AS dateTime
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            WHERE 1=1 {$whereAppointment1} GROUP BY p.customerBookingId ORDER BY p.id ASC";

        $appointmentQuery2 = "SELECT
                0 AS appointmentsCount1,
                COUNT(DISTINCT(p.packageCustomerId)) AS appointmentsCount2,
                0 AS eventsCount,
                p.id AS id,
                p.invoiceNumber AS invoiceNumber,
                p.created AS created,
                p.dateTime AS dateTime
            FROM {$this->table} p
            INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
            {$appointments2ProvidersServicesJoin}
            WHERE 1=1 {$whereAppointment2} GROUP BY p.packageCustomerId ORDER BY p.id ASC";

        $eventQuery = "SELECT
                0 AS appointmentsCount1,
                0 AS appointmentsCount2,
                COUNT(DISTINCT(p.customerBookingId)) AS eventsCount,
                p.id AS id,
                p.invoiceNumber AS invoiceNumber,
                p.created AS created,
                p.dateTime AS dateTime
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
            {$eventsProvidersJoin}
            WHERE 1=1 {$whereEvent} GROUP BY p.customerBookingId ORDER BY p.id ASC";

        if (isset($criteria['events'], $criteria['services'])) {
            return [];
        } elseif (isset($criteria['services'])) {
            $paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2})";
            $params = array_merge($params, $appointmentParams1, $appointmentParams2);
        } elseif (isset($criteria['events'])) {
            $paymentQuery = "({$eventQuery})";
            $params = array_merge($params, $eventParams);
        } elseif (isset($criteria['packages'])) {
            $paymentQuery = "({$appointmentQuery2})";
            $params = array_merge($params, $appointmentParams2);
        } else {
            $paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2}) UNION ALL ({$eventQuery})";
            $params = array_merge($params, $appointmentParams1, $appointmentParams2, $eventParams);
        }

        try {
            $statement = $this->connection->prepare(
                "SELECT * FROM ({$paymentQuery}) payments
                {$groupBy}
                ORDER BY {$basedOnDate}, id"
            );

            $statement->execute($params);

            $statements = $statement->fetchAll();

            $appointmentsCount1 = 0;
            $appointmentsCount2 = 0;
            $eventsCount        = 0;

            foreach ($statements as $st) {
                $appointmentsCount1 += !empty($st['appointmentsCount1']) ? $st['appointmentsCount1'] : 0;
                $appointmentsCount2 += !empty($st['appointmentsCount2']) ? $st['appointmentsCount2'] : 0;
                $eventsCount        += !empty($st['eventsCount']) ? $st['eventsCount'] : 0;
            }
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__, $e->getCode(), $e);
        }

        return $appointmentsCount1 + $appointmentsCount2 + $eventsCount;
    }

    /**
     * Returns a collection of customers that have birthday on today's date and where notification is not sent
     *
     * @return Collection
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     * @throws \Exception
     */
    public function getUncompletedActionsForPayments()
    {
        $params = [];

        $currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d %H:%i:%s')";

        $pastDateTime =
            "STR_TO_DATE('" .
            DateTimeService::getNowDateTimeObjectInUtc()->modify('-1 day')->format('Y-m-d H:i:s') .
            "', '%Y-%m-%d %H:%i:%s')";

        try {
            $statement = $this->connection->prepare(
                "SELECT * FROM {$this->table} 
                WHERE
                      actionsCompleted = 0 AND
                      {$currentDateTime} > DATE_ADD(created, INTERVAL 300 SECOND) AND
                      {$pastDateTime} < created AND
                      entity IS NOT NULL"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__, $e->getCode(), $e);
        }

        $items = [];

        foreach ($rows as $row) {
            $items[] = call_user_func([static::FACTORY, 'create'], $row);
        }

        return new Collection($items);
    }

    /**
     * @param int $status
     */
    public function findByStatus($status)
    {
        // TODO: Implement findByStatus() method.
    }

    /**
     * @param array $data
     * @param boolean $invoice
     *
     * @return array
     * @throws QueryExecutionException
     */
    public function getSecondaryPaymentIds($data, $invoice)
    {
        $rows = [];

        $params = [];

        $where = [];

        foreach ($data as $index => $item) {
            $paymentIdParam1 = ':paymentId1' . $index;
            $params[$paymentIdParam1] = $item['paymentId'];

            if ($invoice) {
                if (!empty($item['parentId'])) {
                    $parentIdParam1 = ':parentId1' . $index;
                    $params[$parentIdParam1] = $item['parentId'];
                    $parentIdParam2 = ':parentId2' . $index;
                    $params[$parentIdParam2] = $item['parentId'];
                }
                $paymentIdParam2 = ':paymentId2' . $index;
                $params[$paymentIdParam2] = $item['paymentId'];
            }

            $relationParam = ':' . $item['columnName'] . $index;

            $params[$relationParam] = $item['columnId'];

            // change in the future to simply retrieve by invoiceNumber when on invoice page since all the related payments will have the same invoiceNumber
            // cannot be done immediately since invoiceNumber is NULL for existing payments
            if ($invoice) {
                $where[] = "((id <> $paymentIdParam1 AND " . $item['columnName'] . " = $relationParam) OR parentId = $paymentIdParam2" . (!empty($item['parentId']) ? " OR id = $parentIdParam1 OR parentId = $parentIdParam2)" : ")");
            } else {
                $where[] = "(id <> $paymentIdParam1 AND " . $item['columnName'] . " = $relationParam)";
            }
        }

        $where = $where ? 'WHERE ' . implode(' OR ', $where) : '';

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    p.id AS id,
                    p.entity AS entity
                FROM {$this->table} p
                {$where}"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
        }

        $result = [
            'appointment' => [],
            'event' => [],
            'package' => []
        ];

        foreach ($rows as $row) {
            $result[$row['entity']][] = (int)$row['id'];
        }

        return $result;
    }

    /**
     * @param int $paymentId
     * @param string $transactionId
     *
     * @throws QueryExecutionException
     */
    public function updateTransactionId($paymentId, $transactionId)
    {
        $params = [
            ':transactionId' => $transactionId,
            ':paymentId1'    => $paymentId,
            ':paymentId2'    => $paymentId
        ];

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table} SET `transactionId` = :transactionId WHERE id = :paymentId1 OR parentId = :paymentId2"
            );

            $response = $statement->execute($params);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to update data in ' . __CLASS__, $e->getCode(), $e);
        }

        if (!$response) {
            throw new QueryExecutionException('Unable to update data in ' . __CLASS__);
        }

        return $response;
    }



    /**
     * @param array $data
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function setInvoiceNumber($data)
    {
        $params = [
            ':id1' => $data['id'],
            ':id2' => $data['id'],
            ":{$data['columnName']}" => $data['columnValue']
        ];

        $where = "WHERE id = :id1 OR parentId = :id2 OR {$data['columnName']} = :{$data['columnName']}";

        if (!empty($data['parentId'])) {
            $params[':parentId1'] = $params[':parentId2'] = $data['parentId'];
            $where = ' OR id = :parentId1 OR parentId = :parentId2';
        }

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table} 
                SET `invoiceNumber` = (SELECT MAX(CASE WHEN invoiceNumber IS NULL THEN 0 ELSE invoiceNumber END)+1 FROM (SELECT * FROM {$this->table}) AS p)
                {$where}"
            );

            $response = $statement->execute($params);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to save invoice number in ' . __CLASS__, $e->getCode(), $e);
        }

        if (!$response) {
            throw new QueryExecutionException('Unable to save invoice number in ' . __CLASS__);
        }

        return $response;
    }
}
