bid/library/Ppb/Service/Listings.php

<?php

/**
 *
 * PHP Pro Bid $Id$ 7TpWuUNndfHVwBDz1rUqzAoDsI0XT6AA/3NqEqrjIMY=
 *
 * @link        http://www.phpprobid.com
 * @copyright   Copyright (c) 2017 Online Ventures Software & CodeCube SRL
 * @license     http://www.phpprobid.com/license Commercial License
 *
 * @version     7.10 [rev.7.10.02]
 */
/**
 * listings table service class
 */

namespace Ppb\Service;

use Cube\Controller\Front,
    Cube\Controller\Request\AbstractRequest,
    Cube\Db\Select,
    Cube\Db\Expr,
    Ppb\Service,
    Ppb\Db\Table,
    Ppb\Form\Element\StockLevels,
    Ppb\Db\Table\Row\Listing as ListingModel,
    Ppb\Model\Shipping as ShippingModel;

class Listings extends AbstractService
{

    /**
     * number of seconds in a day
     */
    const DAY_SECONDS = 86400;

    /**
     * listings browse statuses
     */
    const STATUS_OPEN = 'open';
    const STATUS_CLOSE = 'close';
    const STATUS_RELIST = 'relist';
    const STATUS_DRAFTS_LIST = 'draftsList';
    const STATUS_ACTIVATE = 'activate';
    const STATUS_SUSPEND = 'suspend';
    const STATUS_APPROVE = 'approve';
    const STATUS_UNDELETE = 'undelete';
    const STATUS_DELETE = 'delete';

    /**
     * custom fields tables "type" column
     */
    const CUSTOM_FIELDS_TYPE = 'item';

    /**
     *
     * status message generated by the changeStatus() method
     *
     * @var array
     */
    protected $_statusMessages = array(
        self::STATUS_OPEN        => "%s listings have been opened.",
        self::STATUS_CLOSE       => "%s listings have been closed.",
        self::STATUS_RELIST      => "%s listings have been relisted.",
        self::STATUS_DRAFTS_LIST => "%s drafts have been listed.",
        self::STATUS_ACTIVATE    => "%s listings have been activated.",
        self::STATUS_SUSPEND     => "%s listings have been suspended.",
        self::STATUS_APPROVE     => "%s listings have been approved.",
        self::STATUS_UNDELETE    => "%s listings have been undeleted.",
        self::STATUS_DELETE      => "%s listings have been deleted.",
    );

    /**
     * select options
     */
    const SELECT_ADMIN = 'admin';
    const SELECT_MEMBERS = 'members';
    const SELECT_LISTINGS = 'listings';

    /**
     *
     * listings select types
     *
     * @var array
     */
    protected $_selectTypes = array(
        self::SELECT_ADMIN,
        self::SELECT_MEMBERS,
        self::SELECT_LISTINGS,
    );

    /**
     *
     * listings media (images etc) table service
     *
     * @var \Ppb\Service\ListingsMedia
     */
    protected $_listingsMedia;

    /**
     *
     * custom fields data table service
     *
     * @var \Ppb\Service\CustomFieldsData
     */
    protected $_customFieldsData;

    /**
     *
     * listing types available
     * allowed: auction, product, wanted, reverse, first_bidder
     *
     * @var array
     */
    protected $_listingTypes = array();

    /**
     *
     * class constructor
     */
    public function __construct()
    {
        parent::__construct();

        $this->setTable(
            new Table\Listings());
    }

    /**
     *
     * get custom fields data service
     *
     * @return \Ppb\Service\CustomFieldsData
     */
    public function getCustomFieldsDataService()
    {
        if (!$this->_customFieldsData instanceof Service\CustomFieldsData) {
            $this->setCustomFieldsDataService(
                new Service\CustomFieldsData());
        }

        return $this->_customFieldsData;
    }

    /**
     *
     * set custom fields data service
     *
     * @param \Ppb\Service\CustomFieldsData $customFieldsData
     *
     * @return \Ppb\Service\Listings
     */
    public function setCustomFieldsDataService(Service\CustomFieldsData $customFieldsData)
    {
        $this->_customFieldsData = $customFieldsData;

        return $this;
    }

    /**
     *
     * get status message
     *
     * @param string $status
     *
     * @return string|null
     */
    public function getStatusMessage($status)
    {

        if (array_key_exists($status, $this->_statusMessages)) {
            $translate = $this->getTranslate();

            return $translate->_($this->_statusMessages[$status]);
        }

        return $status;
    }

    /**
     *
     * get listings media service
     *
     * @return \Ppb\Service\ListingsMedia
     */
    public function getListingsMedia()
    {
        if (!$this->_listingsMedia instanceof Service\ListingsMedia) {
            $this->setListingsMedia(
                new Service\ListingsMedia());
        }

        return $this->_listingsMedia;
    }

    /**
     *
     * set the listings media service
     *
     * @param \Ppb\Service\ListingsMedia $listingsMedia
     *
     * @return $this
     */
    public function setListingsMedia(Service\ListingsMedia $listingsMedia)
    {
        $this->_listingsMedia = $listingsMedia;

        return $this;
    }


    /**
     *
     * get item types
     *
     * @return array
     */
    public function getListingTypes()
    {
        if (empty($this->_listingTypes)) {
            $this->setListingTypes();
        }

        return $this->_listingTypes;
    }

    /**
     *
     * set listing types array
     *
     * @param array $listingTypes
     *
     * @return $this
     */
    public function setListingTypes(array $listingTypes = null)
    {
        $translate = $this->getTranslate();

        if ($listingTypes === null) {
            $settings = $this->getSettings();

            if ($settings['enable_auctions']) {
                $listingTypes['auction'] = $translate->_('Auction');
            }

            if ($settings['enable_products']) {
                $listingTypes['product'] = $translate->_('Product');
            }
        }

        $this->_listingTypes = $listingTypes;

        return $this;
    }

    /**
     *
     * saves a listing (create or update)
     * will save data to any linked tables as well (like images, videos etc)
     * will also save shipping options serialized array
     *
     * 'partial' flag =    set by any methods that might use the save method and which shouldn't alter flags
     *                      that are altered only when creating or editing the listing
     *
     * if the item has been edited and was already counted, subtract the category counters and reset the count flag
     *
     * @param array $post post array to be saved in the listings table
     *
     * @return int              the id of the listing that was saved
     */
    public function save($post)
    {
        $row = null;
        $user = $this->getUser();

        $data = $this->_prepareSaveData($post);

        if (isset($user['id'])) {
            $data['user_id'] = $user['id'];
        }

        if (array_key_exists('id', $data)) {
            $select = $this->_table->select()
                ->where("id = ?", $data['id']);

            if (isset($data['user_id'])) {
                $select->where("user_id = ?", $data['user_id']);
            }

            $row = $this->findBy('id', $data['id'], false, true);

            unset($data['id']);
        }

        if (!isset($post['partial'])) {
            if (!$user->isAdmin()) {
                $data['approved'] = $this->_setApprovedFlag();
            }

            $postageSettings = array();

            foreach (ShippingModel::$postageFields as $key => $value) {
                if (isset($post[$key])) {
                    $postageSettings[$key] = \Ppb\Utility::unserialize($post[$key]);
                }
            }

            // workaround for bulk lister dimensions field
            if (!empty($post['dimensions'])) {
                $dimensions = $post['dimensions'];

                if (count($dimensions) == 3 && !array_key_exists(ShippingModel::DIMENSION_LENGTH, $dimensions)) {
                    $dimensions = array(
                        ShippingModel::DIMENSION_LENGTH => $dimensions[0],
                        ShippingModel::DIMENSION_WIDTH  => $dimensions[1],
                        ShippingModel::DIMENSION_HEIGHT => $dimensions[2],
                    );
                }

                $postageSettings['dimensions'] = $dimensions;
            }

            $data['postage_settings'] = serialize($postageSettings);
        }

        $id = null;
        if (count($row) > 0) {
            $data['rollback_data'] = serialize($row);

            if (
                $row->isActiveAndOpen() &&
                $row->getData('list_in') != 'store' &&
                $row['last_count_operation'] == ListingModel::COUNT_OP_ADD
            ) {
                $row->countCategoriesCounter(ListingModel::COUNT_OP_SUBTRACT);
                $data['last_count_operation'] = ListingModel::COUNT_OP_NONE;
            }

            $data['updated_at'] = new Expr('now()');
            unset($data['user_id']);

            $this->_table->update($data, "id='{$row['id']}'");
            $id = $row['id'];
        }
        else if (!isset($post['partial'])) {
            $data['created_at'] = new Expr('now()');

            $this->_table->insert($data);
            $id = $this->_table->getAdapter()->lastInsertId();
        }

        if (!isset($post['partial'])) {
            // save all media corresponding to the listing in the listings_media table
            $this->getListingsMedia()->save($id, $post);

            // save custom fields data in the custom_fields_data table
            foreach ($post as $key => $value) {
                if (strstr($key, 'custom_field_')) {
                    $fieldId = str_replace('custom_field_', '', $key);
                    $this->getCustomFieldsDataService()->save(
                        $value, self::CUSTOM_FIELDS_TYPE, $fieldId, $id);
                }
            }
        }

        return $id;
    }

    /**
     *
     * find a row on the listings table by querying a certain column
     * if it exists, fetch all linked data:
     * - media (images, etc)
     * - custom fields
     * - TBD
     * - postage settings (unserialize from the field)
     *
     * @param string $name      column name
     * @param string $value     column value
     * @param bool   $strict    if set to true, it will return the listing only if
     *                          the owner is the currently logged in user.
     * @param bool   $enhanced  if set to true, it will retrieve all related data as an array
     *
     * @return \Ppb\Db\Table\Row\Listing|null
     */
    public function findBy($name, $value, $strict = false, $enhanced = false)
    {
        $where = $this->getTable()->getAdapter()->quoteInto("{$name} = ?", $value);

        return $this->fetchAll($where, null, null, null, $strict, $enhanced)->getRow(0);
    }


    /**
     *
     * fetches all matched rows
     *
     * @param string|\Cube\Db\Select $where    SQL where clause, or a select object
     * @param string|array           $order
     * @param int                    $count
     * @param int                    $offset
     * @param bool                   $strict   if set to true, it will return the listing only if
     *                                         the owner is the currently logged in user.
     * @param bool                   $enhanced if set to true, it will retrieve all related data as an array
     *
     * @return \Ppb\Db\Table\Rowset\Listings
     */
    public function fetchAll($where = null, $order = null, $count = null, $offset = null, $strict = false, $enhanced = false)
    {
        /** @var \Ppb\Db\Table\Rowset\Listings $listings */
        $listings = parent::fetchAll($where, $order, $count, $offset);
        $user = $this->getUser();

        /** @var \Ppb\Db\Table\Row\Listing $listing */
        foreach ($listings as $key => $listing) {
            if ($strict === true && $listing['user_id'] != $user['id']) {
                $listings[$key] = null;
            }
            else if ($enhanced === true) {
                // listing media formatted data
                /** @var \Ppb\Db\Table\Rowset\ListingsMedia $listingsMediaRowset */
                $listingsMediaRowset = $listing->findDependentRowset('\Ppb\Db\Table\ListingsMedia', null,
                    $this->getTable()->select()->order('order_id ASC'));

                $listingsMedia = $listingsMediaRowset->getFormattedData();
                foreach ($listingsMedia as $k => $v) {
                    $listing[$k] = $v;
                }

                // custom fields data
                $customFieldsData = $this->getCustomFieldsData($listing['id']);
                foreach ($customFieldsData as $k => $v) {
                    $listing['custom_field_' . $k] = $v;
                }

                $listings[$key] = $listing;
            }
        }

        return $listings;
    }

    /**
     *
     * creates and returns a new \Cube\Db\Select object used for selecting listings
     *
     * @param string                                   $selectType the type of select to be created - admin, members, listings
     * @param \Cube\Controller\Request\AbstractRequest $request
     *
     * @throws \InvalidArgumentException
     * @return \Cube\Db\Select
     */
    public function select($selectType, AbstractRequest $request = null)
    {
        if (!$request instanceof AbstractRequest) {
            $request = Front::getInstance()->getRequest();
        }

        if (!in_array($selectType, $this->_selectTypes)) {
            throw new \InvalidArgumentException(
                sprintf("Invalid select type submitted. Allowed types: %s", implode(', ', $this->_selectTypes)));
        }

        $user = $this->getUser();
        $settings = $this->getSettings();

        $categoriesService = new Service\Table\Relational\Categories();

        $type = $request->getParam('type');
        $keywords = $request->getParam('keywords');
        $listingId = intval($request->getParam('listing_id'));
        $filter = $request->getParam('filter');

        /* listings sort drop-down */
        $sort = $request->getParam('sort');

        $showOnly = (array)$request->getParam('show_only');
        $listingTypes = (array)$request->getParam('listing_type');
        $country = $request->getParam('country');

        /* listings module specific params */
        $parentId = intval($request->getParam('parent_id'));
        $price = $request->getParam('price');

        $priceFrom = isset($price[\Ppb\Form\Element\Range::RANGE_FROM]) ? doubleval($price[\Ppb\Form\Element\Range::RANGE_FROM]) : null;
        $priceTo = isset($price[\Ppb\Form\Element\Range::RANGE_TO]) ? doubleval($price[\Ppb\Form\Element\Range::RANGE_TO]) : null;

        /* members module specific params */
        $show = $request->getParam('show');
        $userId = $request->getParam('user_id');

        $select = $this->getTable()->getAdapter()
            ->select()
            ->from(array('l' => 'listings'), '*');

        $listingTypes = array_filter($listingTypes);
        if (empty($listingTypes) || in_array('all', (array)$listingTypes)) {
            $listingTypes = array_keys($this->getListingTypes());
        }

        $select->where("l.listing_type IN (?)", $listingTypes);

        switch ($selectType) {
            case self::SELECT_LISTINGS:
                if (!in_array($filter, array('open', 'closed', 'scheduled'))) {
                    $filter = 'open';
                }
                if ($show != 'store') {
                    $select->where('l.list_in != ?', 'store');
                }
                break;
            case self::SELECT_MEMBERS:
                $select->where('l.user_id = ?', $user['id']);
                break;
        }

        switch ($type) {
            case 'categories':
                if ($parentId) {
                    $select->reset('order')
                        ->where('l.catfeat = ?', 1)
                        ->order(new Expr('rand()'));
                }
                break;
            case 'homepage':
                $select->reset('order')
                    ->where('l.hpfeat = ?', 1)
                    ->order(new Expr('rand()'));
                break;
            case 'recent':
                $select->reset('order')
                    ->order('l.start_time DESC');
                break;
            case 'ending':
                $select->reset('order')
                    ->where('l.end_time IS NOT NULL')
                    ->order('l.end_time ASC');
                break;
            case 'popular':
                $select->reset('order')
                    ->order('l.nb_clicks DESC');
                break;
            case 'seller-other-items':
                $select->reset('order')
                    ->where('l.user_id = ?', (int)$userId)
                    ->where('l.id != ?', (int)$request->getParam('current_listing_id'))
                    ->order('l.start_time DESC');
                break;
            case 'seller':
                $select->reset('order')
                    ->where('l.user_id = ?', (int)$userId)
                    ->order('l.start_time DESC');
        }

        switch ($show) {
            case 'bids':
                $select->join(array('bids' => 'bids'), 'bids.listing_id = l.id', 'bids.id AS bid_id')
                    ->group('l.id');
                break;

            case 'offers':
                $select->join(array('o' => 'offers'), "o.listing_id = l.id AND o.type='offer'", 'o.id AS offer_id')
                    ->group('l.id');
                break;

            case 'sold':
                $select = $this->_addSelectPart('sold', $select);

                break;

            case 'pending':
                $select->join(array('o' => 'offers'), "o.listing_id = l.id AND o.type='offer'", 'o.id AS offer_id')
                    ->joinLeft(array('s' => 'sales_listings'), "s.listing_id = l.id", 's.id AS sale_listing_id')
                    ->where('s.id IS NULL')
                    ->group('l.id');
                break;

            case 'unsold':
                $select->joinLeft(array('s' => 'sales_listings'), "s.listing_id = l.id", 's.id AS sale_listing_id')
                    ->where('s.id IS NULL')
                    ->group('l.id');
                break;
            case 'store':
                $select->where('l.list_in != ?', 'site');

                if ($slug = $request->getParam('store_slug')) {
                    $usersService = new Users();
                    $store = $usersService->findBy('store_slug', $slug);
                    if (!empty($store['id'])) {
                        $userId = $store['id'];
                    }
                }

                if ($userId) {
                    $select->where('l.user_id = ?', $userId);
                }

                break;
            case 'other-items':
                $select->where('l.list_in != ?', 'store')
                    ->where('l.user_id = ?', $userId);
                break;
            case 'featured':
                $select->where('l.hpfeat = ?', 1);
                break;

            case 'recent':
                if (empty($sort)) {
                    $sort = 'started_desc';
                }
                break;
            case 'popular':
                if (empty($sort)) {
                    $sort = 'clicks_desc';
                }
                break;
            case 'ending':
                $select->where('l.end_time <> ?', 0);
                if (empty($sort)) {
                    $sort = 'ending_asc';
                }
                break;
            case 'by-user':
                $select->where('l.user_id = ?', $userId);
        }

        if (in_array('sold', $showOnly)) {
            $filter = 'sold';
        }
        if (in_array('accept_returns', $showOnly)) {
            $select->where('l.postage_settings LIKE ?', '%s:14:"accept_returns";s:1:"1"%');
        }
        if (in_array('make_offer', $showOnly)) {
            $select->where('l.enable_make_offer = ?', 1);
        }

        if ($country) {
            $select->where('l.country = ?', (int)$country);
        }

        if ($listingId) {
            $select->where('l.id = ?', $listingId);
        }

        if (!empty($keywords)) {
            $params = '%' . str_replace(' ', '%', $keywords) . '%';
            if (is_numeric($keywords)) {
                $select->where('(l.id = "' . intval($keywords) . '" OR l.name LIKE "' . $params . '" OR l.subtitle LIKE "' . $params . '")');
            }
            else {
                $keywords = explode(' ', $keywords);
                $conditions = array();

                $conditions[] = 'l.name LIKE "%1$s"';

                if ($settings['search_subtitle']) {
                    $conditions[] = 'l.subtitle LIKE "%1$s"';
                }
                if ($settings['search_description']) {
                    $conditions[] = 'l.description LIKE "%1$s"';
                }
                if ($settings['search_category_name']) {
                    $select->joinLeft(array('mc' => 'categories'), 'mc.id = l.category_id', 'mc.full_name AS main_category_name');
                    $conditions[] = 'mc.full_name LIKE "%1$s"';

                    if ($settings['addl_category_listing']) {
                        $select->joinLeft(array('ac' => 'categories'), 'ac.id = l.addl_category_id', 'ac.full_name AS addl_category_name');
                        $conditions[] = 'ac.full_name LIKE "%1$s"';
                    }
                }

                $cond = implode(' OR ', $conditions);

                foreach ((array)$keywords as $keyword) {
                    $select->where('(' . sprintf($cond, '%' . $keyword . '%') . ')');
//                    $select->where('(l.name LIKE "%' . $keyword . '%" OR l.subtitle LIKE "%' . $keyword . '%")'); // OBSOLETE
                }

                // REGEXP SOLUTION - SEARCHES FOR ANY KEYWORD - NOT GOOD
//              $select->where("l.name REGEXP '( )*(" . str_replace(' ', ')*( )*(', $keywords) . ")( )*'");

            }
        }

        if ($priceFrom > 0) {
            $select->where('l.start_price >= ?', $priceFrom);
        }
        if ($priceTo > 0) {
            $select->where('l.start_price <= ?', $priceTo);
        }

        switch ($filter) {
            case 'open':
                $select->where('l.closed = ?', 0)
                    ->where('l.deleted = ?', 0);

                if ($selectType != self::SELECT_MEMBERS) {
                    $select->where('l.active = ?', 1)
                        ->where('l.approved = ?', 1);
                }
                break;
            case 'closed':
                $select->where('l.closed = ?', 1)
                    ->where('l.deleted = ?', 0)
                    ->where('l.end_time <= ?', new Expr('now()'));

                if ($selectType != self::SELECT_MEMBERS) {
                    $select->where('l.active = ?', 1)
                        ->where('l.approved = ?', 1);
                }

                if (empty($sort)) {
                    $sort = 'ending_desc';
                }
                break;
            case 'scheduled':
                $select->where('l.closed = ?', 1)
                    ->where('l.deleted = ?', 0)
                    ->where('l.start_time > ?', new Expr('now()'));

                if ($selectType != self::SELECT_MEMBERS) {
                    $select->where('l.active = ?', 1)
                        ->where('l.approved = ?', 1);
                }
                break;
            case 'suspended':
                $select->where('l.active != ?', 1)
                    ->where('l.approved = ?', 1);
                break;
            case 'awaiting_approval':
                $select->where('l.approved = ?', 0);
                break;
            case 'deleted':
                $select->where('l.deleted = ?', 1);
                break;
            case 'sold':
                $select = $this->_addSelectPart('sold', $select);
                break;
        }

        switch ($sort) {
            case 'price_asc':
                $select->order(new Expr("IF(l.listing_type='product',l.buyout_price, IF(max(b.amount) is null, l.start_price, max(b.amount))) ASC"))
                    ->joinLeft(array('b' => 'bids'), 'b.listing_id = l.id', new Expr('max(b.amount) as current_bid'))
                    ->group('l.id');

                break;
            case 'price_desc':
                $select->order(new Expr("IF(l.listing_type='product',l.buyout_price, IF(max(b.amount) is null, l.start_price, max(b.amount))) DESC"))
                    ->joinLeft(array('b' => 'bids'), 'b.listing_id = l.id', new Expr('max(b.amount) as current_bid'))
                    ->group('l.id');
                break;
            case 'started_asc':
                $select->order('l.start_time ASC');
                break;
            case 'started_desc':
                $select->order('l.start_time DESC');
                break;
            case 'ending_asc':
                // 7.8: nulls last
                $select->order('-l.end_time DESC');
                break;
            case 'ending_desc':
                $select->order('l.end_time DESC');
                break;
            case 'clicks_asc':
                $select->order('l.nb_clicks ASC');
                break;
            case 'clicks_desc':
                $select->order('l.nb_clicks DESC');
                break;
            case 'relevance':
                $select->order('l.nb_clicks DESC');
                break;
            default:
                if (!$select->getPart('order')) {
                    $select->order('l.created_at DESC');
                }
                break;
        }

        $select->where('l.draft = ?', ($filter == 'drafts') ? 1 : 0);

        /*
         * search by category
         *
         * @7.9: if no category is selected, do not display items from adult categories
         */
        $categoriesFilter = array(0);

        if ($parentId) {
            $categoriesIds = array_keys($categoriesService->getChildren($parentId, true));

            if ($settings['addl_category_listing']) {
                $select->where('l.category_id IN (?) OR l.addl_category_id IN (?)', $categoriesIds);
            }
            else {
                $select->where('l.category_id IN (?)', $categoriesIds);
            }

            $categoriesFilter = array_merge($categoriesFilter, array_keys(
                $categoriesService->getBreadcrumbs($parentId)));
        }
        else if ($selectType == self::SELECT_LISTINGS) {
            if ($settings['enable_adult_categories']) {
                $select->joinLeft(array('mct' => 'categories'), "l.category_id = mct.id", 'mct.adult')
                    ->where('mct.adult = ? OR mct.adult is null', 0);

                if ($settings['addl_category_listing']) {
                    $select->joinLeft(array('act' => 'categories'), "l.addl_category_id = act.id", 'act.adult')
                        ->where('act.adult = ? OR act.adult is null', 0);
                }
            }
        }

        $customFieldsService = new CustomFields();

        $customFields = $customFieldsService->getFields(
            array(
                'type'         => 'item',
                'active'       => 1,
                'searchable'   => 1,
                'category_ids' => $categoriesFilter,
            ))->toArray();

        // pre php 5.5 array_column workaround - equivalent to:
        // $customFields = array_column($customFields, 'id');
        $customFields = array_map(function ($element) {
            return $element['id'];
        }, $customFields);
        /*
         * custom fields search 
         */
        $params = $request->getParams();

        foreach ($params as $key => $value) {
            if (preg_match('#^(custom_field_)#', $key) && !empty($value)) {
                $fieldId = str_replace('custom_field_', '', $key);

                if (in_array($fieldId, $customFields)) {
                    $alias = 'cf' . $fieldId;

                    $customFieldsSelect = null;
                    if (is_string($value)) {
                        if (!empty($value)) {
                            // search using like, just like with keywords search
                            //$customFieldsSelect = "{$alias}.value = '{$value}'"; OBSOLETE
                            $value = explode(' ', $value);
                            $customFieldsSelect = array();
                            foreach ((array)$value as $val) {
                                $customFieldsSelect[] = "{$alias}.value LIKE '%{$val}%'";
                            }
                            $customFieldsSelect = implode(' OR ', $customFieldsSelect);
                        }
                    }
                    else if (is_array($value)) {
                        $checkBoxSrc = array();
                        $radioSrc = array();
                        foreach ($value as $val) {
                            if (!empty($val)) {
                                $checkBoxSrc[] = $val;
                                $radioSrc[] = "{$alias}.value = '{$val}'";
                            }
                        }
                        if (count($checkBoxSrc) > 0) {
                            $customFieldsSelect = "{$alias}.value REGEXP '\"" . implode('"|"',
                                    array_unique($checkBoxSrc)) . "\"' OR "
                                . implode(' OR ', $radioSrc);
                        }
                    }

                    if (!empty($customFieldsSelect)) {
                        $select->join(array($alias => 'custom_fields_data'),
                            "{$alias}.owner_id = l.id AND {$alias}.field_id = '" . (int)$fieldId . "' AND ({$customFieldsSelect})",
                            $alias . '.id AS ' . $alias . '_id');
                    }
                }
            }
        }

        return $select;
    }

    /**
     *
     * get the custom fields data of a certain listing
     *
     * @param integer $id
     *
     * @return array
     */
    public function getCustomFieldsData($id)
    {
        $result = array();

        // custom fields data
        $rowset = $this->getCustomFieldsDataService()->fetchAll(
            $this->getCustomFieldsDataService()->getTable()->select('value, field_id')
                ->where('type = ?', self::CUSTOM_FIELDS_TYPE)
                ->where('owner_id = ?', (int)$id));

        foreach ($rowset as $row) {
            $result[$row['field_id']] = \Ppb\Utility::unserialize($row['value']);
        }

        return $result;
    }

    /**
     *
     * prepare listing data for when saving to the table
     * if listing is scheduled, 'closed' = 1
     *
     * important: the daylight saving changes will automatically be calculated when setting the end time!
     * @7.9: if we have an unlimited duration set, then the duration field is set to 0, and if we have a custom end
     * time set then the duration field is set as null
     *
     * @param array $data
     *
     * @return array
     */
    protected function _prepareSaveData($data = array())
    {
        if (isset($data['id']) && empty($data['id'])) {
            unset($data['id']);
        }

        if (isset($data['addl_category_id']) && empty($data['addl_category_id'])) {
            $data['addl_category_id'] = new Expr('null');
        }

        if (!empty($data['buyout_price']) && $data['listing_type'] == 'product') {
            $data['start_price'] = $data['buyout_price'];
        }

        $keysToUnset = array('rollback_data', 'active', 'approved', 'closed', 'deleted', 'nb_clicks');
        foreach ($keysToUnset as $keyToUnset) {
            if (array_key_exists($keyToUnset, $data)) {
                unset($data[$keyToUnset]);
            }
        }

        $startTime = time();
        if (isset($data['start_time_type'])) {
            switch ($data['start_time_type']) {
                case 1: // custom
                    $startTime = strtotime($data['start_time']);
                    $data['start_time'] = date('Y-m-d H:i:s', $startTime);
                    $data['closed'] = 1;
                    break;
                default: // now
                    $data['start_time'] = new Expr('now()');
                    break;
            }
        }
        else if (isset($data['start_time'])) {
            $startTime = strtotime($data['start_time']);
        }

        $endTime = null;

        $endTimeType = isset($data['end_time_type']) ? $data['end_time_type'] : 0;
        switch ($endTimeType) {
            case 1: // custom
                $endTime = strtotime($data['end_time']);
                $data['duration'] = new Expr('null');
                break;
            default: // duration
                // the duration field when using the duration option must be NOT NULL
                $data['duration'] = (isset($data['duration'])) ? $data['duration'] : 0;
                if ($data['duration'] > 0) {
                    $endTime = $startTime + $data['duration'] * 86400;
                }
                break;
        }

        if ($endTime) {
            $data['end_time'] = date('Y-m-d H:i:s', $endTime);
        }
        else {
            $data['end_time'] = new Expr('null');
        }

        if (!empty($data['stock_levels'])) {
            foreach ($data['stock_levels'] as $key => $value) {
                if (!empty($value[StockLevels::FIELD_OPTIONS])) {
                    $data['stock_levels'][$key][StockLevels::FIELD_OPTIONS] = \Ppb\Utility::unserialize($value[StockLevels::FIELD_OPTIONS]);
                }

                if (!empty($value[StockLevels::FIELD_QUANTITY])) {
                    $data['stock_levels'][$key][StockLevels::FIELD_QUANTITY] = abs(intval($value[StockLevels::FIELD_QUANTITY]));
                }
            }
        }

        return parent::_prepareSaveData($data);
    }

    /**
     *
     * return the approved flag that needs to be set for a certain listing
     * if the admin edits a listing, that listing will always be approved
     *
     * @return integer                  1 if approved, 0 otherwise
     */
    protected function _setApprovedFlag()
    {
        $user = $this->getUser();

        if (!empty($user['id'])) {
            $settings = $this->getSettings();
            if ($settings['enable_listings_approval']) {
                return 0;
            }
            else if ($user['listing_approval']) {
                return 0;
            }
        }

        return 1;
    }

    /**
     *
     * add select part internal method
     *
     * @param string          $type
     * @param \Cube\Db\Select $select
     *
     * @return \Cube\Db\Select
     */
    protected function _addSelectPart($type, Select $select)
    {
        switch ($type) {
            case 'sold':
                $select->join(array('sl' => 'sales_listings'), "sl.listing_id = l.id", 'sl.id AS sale_listing_id')
                    ->join(array('s' => 'sales'), "s.id = sl.sale_id AND s.pending = 0", 's.id AS sale_id')
                    ->order('s.created_at DESC')
                    ->group('l.id');

                break;
        }

        return $select;
    }

}