oxarticlelist.php

Go to the documentation of this file.

<?php

class oxArticleList extends oxList
{
    protected $_sCustomSorting;

    protected $_sObjectsInListName = 'oxarticle';

    protected $_blLoadSelectLists = false;

    public function setCustomSorting( $sSorting )
    {
        $this->_sCustomSorting = $sSorting;
    }

    public function enableSelectLists()
    {
        $this->_blLoadSelectLists = true;
    }

    public function selectString( $sSelect )
    {
        startProfile("loadinglists");
        $oRes = parent::selectString( $sSelect );
        stopProfile("loadinglists");

        return $oRes;
    }

    public function getHistoryArticles()
    {
        if ($aArticlesIds = $this->getSession()->getVar('aHistoryArticles')) {
            return $aArticlesIds;
        } elseif ( $sArticlesIds = oxRegistry::get("oxUtilsServer")->getOxCookie('aHistoryArticles')) {
            return explode('|', $sArticlesIds);
        }
    }

    public function setHistoryArticles($aArticlesIds)
    {
        if ($this->getSession()->getId()) {
            oxSession::setVar('aHistoryArticles', $aArticlesIds);
            // clean cookie, if session started
            oxRegistry::get("oxUtilsServer")->setOxCookie('aHistoryArticles', '');
        } else {
            oxRegistry::get("oxUtilsServer")->setOxCookie('aHistoryArticles', implode('|', $aArticlesIds));
        }
    }

    public function loadHistoryArticles( $sArtId, $iCnt = 4 )
    {
        $aHistoryArticles = $this->getHistoryArticles();
        $aHistoryArticles[] = $sArtId;

        // removing dublicates
        $aHistoryArticles = array_unique( $aHistoryArticles );
        if ( count( $aHistoryArticles ) > ( $iCnt + 1 ) ) {
            array_shift( $aHistoryArticles );
        }

        $this->setHistoryArticles( $aHistoryArticles );

        //remove current article and return array
        //asignment =, not ==
        if ( ( $iCurrentArt = array_search( $sArtId, $aHistoryArticles ) ) !== false ) {
            unset( $aHistoryArticles[$iCurrentArt] );
        }

        $aHistoryArticles = array_values( $aHistoryArticles );
        $this->loadIds( $aHistoryArticles );
        $this->sortByIds( $aHistoryArticles );
    }

    protected function sortByIds($aIds)
    {
        $this->_aOrderMap = array_flip($aIds);
        uksort($this->_aArray, array($this, '_sortByOrderMapCallback'));
    }

    protected function _sortByOrderMapCallback($key1, $key2)
    {
        if (isset($this->_aOrderMap[$key1])) {
            if (isset($this->_aOrderMap[$key2])) {
                $iDiff = $this->_aOrderMap[$key2] - $this->_aOrderMap[$key1];
                if ($iDiff > 0) {
                    return -1;
                } elseif ($iDiff < 0) {
                    return 1;
                } else {
                    return 0;
                }
            } else {
                // first is here, but 2nd is not - 1st gets more priority
                return -1;
            }
        } elseif (isset($this->_aOrderMap[$key2])) {
            // first is not here, but 2nd is - 2nd gets more priority
            return 1;
        } else {
            // both unset, equal
            return 0;
        }
    }

    public function loadNewestArticles( $iLimit = null )
    {
        //has module?
        $myConfig = $this->getConfig();

        if ( !$myConfig->getConfigParam( 'bl_perfLoadPriceForAddList' ) ) {
            $this->getBaseObject()->disablePriceLoad();
        }

        $this->_aArray = array();
        switch( $myConfig->getConfigParam( 'iNewestArticlesMode' ) ) {
            case 0:
                // switched off, do nothing
                break;
            case 1:
                // manually entered
                $this->loadActionArticles( 'oxnewest', $iLimit );
                break;
            case 2:
                $sArticleTable = getViewName('oxarticles');
                if ( $myConfig->getConfigParam( 'blNewArtByInsert' ) ) {
                    $sType = 'oxinsert';
                } else {
                    $sType = 'oxtimestamp';
                }
                $sSelect  = "select * from $sArticleTable ";
                $sSelect .= "where oxparentid = '' and ".$this->getBaseObject()->getSqlActiveSnippet()." and oxissearch = 1 order by $sType desc ";
                if (!($iLimit = (int) $iLimit)) {
                    $iLimit = $myConfig->getConfigParam( 'iNrofNewcomerArticles' );
                }
                $sSelect .= "limit " . $iLimit;

                $this->selectString($sSelect);
                break;
        }

    }

    public function loadTop5Articles( $iLimit = null )
    {
        //has module?
        $myConfig = $this->getConfig();

        if ( !$myConfig->getConfigParam( 'bl_perfLoadPriceForAddList' ) ) {
            $this->getBaseObject()->disablePriceLoad();
        }

        switch( $myConfig->getConfigParam( 'iTop5Mode' ) ) {
            case 0:
                // switched off, do nothing
                break;
            case 1:
                // manually entered
                $this->loadActionArticles( 'oxtop5', $iLimit );
                break;
            case 2:
                $sArticleTable = getViewName('oxarticles');

                //by default limit 5
                $sLimit = ( $iLimit > 0 ) ? "limit " . $iLimit : 'limit 5';

                $sSelect  = "select * from $sArticleTable ";
                $sSelect .= "where ".$this->getBaseObject()->getSqlActiveSnippet()." and $sArticleTable.oxissearch = 1 ";
                $sSelect .= "and $sArticleTable.oxparentid = '' and $sArticleTable.oxsoldamount>0 ";
                $sSelect .= "order by $sArticleTable.oxsoldamount desc $sLimit";

                $this->selectString($sSelect);
                break;
        }
    }

    public function loadActionArticles( $sActionID, $iLimit = null )
    {
        // Performance
        if ( !trim( $sActionID) ) {
            return;
        }

        $sShopID        = $this->getConfig()->getShopId();
        $sActionID      = oxDb::getDb()->quote(strtolower( $sActionID));

        //echo $sSelect;
        $oBaseObject    = $this->getBaseObject();
        $sArticleTable  = $oBaseObject->getViewName();
        $sArticleFields = $oBaseObject->getSelectFields();

        $oBase = oxNew("oxactions");
        $sActiveSql = $oBase->getSqlActiveSnippet();
        $sViewName = $oBase->getViewName();

        $sLimit = ( $iLimit > 0 ) ? "limit " . $iLimit : '';

        $sSelect = "select $sArticleFields from oxactions2article
                              left join $sArticleTable on $sArticleTable.oxid = oxactions2article.oxartid
                              left join $sViewName on $sViewName.oxid = oxactions2article.oxactionid
                              where oxactions2article.oxshopid = '$sShopID' and oxactions2article.oxactionid = $sActionID and $sActiveSql
                              and $sArticleTable.oxid is not null and " .$oBaseObject->getSqlActiveSnippet(). "
                              order by oxactions2article.oxsort $sLimit";

        $this->selectString( $sSelect );
    }


    public function loadAktionArticles( $sActionID, $iLimit = null )
    {
        $this->loadActionArticles( $sActionID, $iLimit = null );

    }

    public function loadArticleCrossSell( $sArticleId )
    {
        $myConfig = $this->getConfig();

        // Performance
        if ( !$myConfig->getConfigParam( 'bl_perfLoadCrossselling' ) ) {
            return null;
        }

        $oBaseObject   = $this->getBaseObject();
        $sArticleTable = $oBaseObject->getViewName();

        $sArticleId = oxDb::getDb()->quote($sArticleId);

        $sSelect  = "select $sArticleTable.* from oxobject2article left join $sArticleTable on oxobject2article.oxobjectid=$sArticleTable.oxid ";
        $sSelect .= "where oxobject2article.oxarticlenid = $sArticleId ";
        $sSelect .= " and $sArticleTable.oxid is not null and " .$oBaseObject->getSqlActiveSnippet(). " order by rand()";

        // #525 bidirectional crossselling
        if ( $myConfig->getConfigParam( 'blBidirectCross' ) ) {
            $sSelect = "SELECT $sArticleTable.* FROM $sArticleTable
                LEFT JOIN oxobject2article AS O2A1 on
                    ( O2A1.oxobjectid = $sArticleTable.oxid AND O2A1.oxarticlenid = $sArticleId )
                LEFT JOIN oxobject2article AS O2A2 ON
                    ( O2A2.oxarticlenid = $sArticleTable.oxid AND O2A2.oxobjectid = $sArticleId )
                WHERE 1
                    AND ( O2A2.oxarticlenid IS NOT NULL OR O2A1.oxobjectid IS NOT NULL )
                    AND " . $oBaseObject->getSqlActiveSnippet() . "
                    AND ($sArticleTable.oxid != $sArticleId) order by rand()";
        }

        $this->setSqlLimit( 0, $myConfig->getConfigParam( 'iNrofCrossellArticles' ));
        $this->selectString( $sSelect );
    }

    public function loadArticleAccessoires( $sArticleId )
    {
        $myConfig = $this->getConfig();

        // Performance
        if ( !$myConfig->getConfigParam( 'bl_perfLoadAccessoires' ) ) {
            return;
        }

        $sArticleId = oxDb::getDb()->quote($sArticleId);

        $oBaseObject   = $this->getBaseObject();
        $sArticleTable = $oBaseObject->getViewName();

        $sSelect  = "select $sArticleTable.* from oxaccessoire2article left join $sArticleTable on oxaccessoire2article.oxobjectid=$sArticleTable.oxid ";
        $sSelect .= "where oxaccessoire2article.oxarticlenid = $sArticleId ";
        $sSelect .= " and $sArticleTable.oxid is not null and " .$oBaseObject->getSqlActiveSnippet();
        //sorting articles
        $sSelect .= " order by oxaccessoire2article.oxsort";

        $this->selectString( $sSelect );
    }

    public function loadCategoryIds( $sCatId, $aSessionFilter )
    {
        $sArticleTable = $this->getBaseObject()->getViewName();
        $sSelect = $this->_getCategorySelect( $sArticleTable.'.oxid as oxid', $sCatId, $aSessionFilter );

        $this->_createIdListFromSql( $sSelect );
    }

    public function loadCategoryArticles( $sCatId, $aSessionFilter, $iLimit = null )
    {
        $sArticleFields = $this->getBaseObject()->getSelectFields();

        $sSelect = $this->_getCategorySelect( $sArticleFields, $sCatId, $aSessionFilter );

        // calc count - we can not use count($this) here as we might have paging enabled
        // #1970C - if any filters are used, we can not use cached category article count
        $iArticleCount = null;
        if ( $aSessionFilter) {
            $iArticleCount = oxDb::getDb()->getOne( $this->_getCategoryCountSelect( $sCatId, $aSessionFilter ) );
        }

        if ($iLimit = (int) $iLimit) {
            $sSelect .= " LIMIT $iLimit";
        }

        $this->selectString( $sSelect );

        if ( $iArticleCount !== null ) {
            return $iArticleCount;
        }

        // this select is FAST so no need to hazzle here with getNrOfArticles()
        return oxRegistry::get("oxUtilsCount")->getCatArticleCount( $sCatId );
    }

    public function loadRecommArticles( $sRecommId, $sArticlesFilter = null )
    {
        $sSelect = $this->_getArticleSelect( $sRecommId, $sArticlesFilter);
        $this->selectString( $sSelect );
    }

    public function loadRecommArticleIds( $sRecommId, $sArticlesFilter )
    {
        $sSelect = $this->_getArticleSelect( $sRecommId, $sArticlesFilter );

        $sArtView = getViewName( 'oxarticles' );
        $sPartial = substr( $sSelect, strpos( $sSelect, ' from ' ) );
        $sSelect  = "select distinct $sArtView.oxid $sPartial ";

        $this->_createIdListFromSql( $sSelect );
    }

    protected function _getArticleSelect( $sRecommId, $sArticlesFilter = null )
    {
        $sRecommId = oxDb::getDb()->quote($sRecommId);

        $sArtView = getViewName( 'oxarticles' );
        $sSelect  = "select distinct $sArtView.*, oxobject2list.oxdesc from oxobject2list ";
        $sSelect .= "left join $sArtView on oxobject2list.oxobjectid = $sArtView.oxid ";
        $sSelect .= "where (oxobject2list.oxlistid = $sRecommId) ".$sArticlesFilter;

        return $sSelect;
    }

    public function loadSearchIds( $sSearchStr = '', $sSearchCat = '', $sSearchVendor = '', $sSearchManufacturer = '' )
    {
        $oDb = oxDb::getDb();
        $sSearchCat    = $sSearchCat?$sSearchCat:null;
        $sSearchVendor = $sSearchVendor?$sSearchVendor:null;
        $sSearchManufacturer = $sSearchManufacturer?$sSearchManufacturer:null;

        $sWhere = null;

        if ( $sSearchStr ) {
            $sWhere = $this->_getSearchSelect( $sSearchStr );
        }

        $sArticleTable = getViewName('oxarticles');

        // longdesc field now is kept on different table
        $sDescTable = '';
        $sDescJoin  = '';
        if ( is_array( $aSearchCols = $this->getConfig()->getConfigParam( 'aSearchCols' ) ) ) {
            if ( in_array( 'oxlongdesc', $aSearchCols ) || in_array( 'oxtags', $aSearchCols ) ) {
                $sDescView  = getViewName( 'oxartextends' );
                $sDescJoin  = " LEFT JOIN $sDescView ON {$sDescView}.oxid={$sArticleTable}.oxid ";
            }
        }

        // load the articles
        $sSelect  =  "select $sArticleTable.oxid from $sArticleTable $sDescJoin where ";

        // must be additional conditions in select if searching in category
        if ( $sSearchCat ) {
            $sO2CView = getViewName('oxobject2category');
            $sSelect  = "select $sArticleTable.oxid from $sO2CView as oxobject2category, $sArticleTable $sDescJoin ";
            $sSelect .= "where oxobject2category.oxcatnid=".$oDb->quote( $sSearchCat )." and oxobject2category.oxobjectid=$sArticleTable.oxid and ";
        }
        $sSelect .= $this->getBaseObject()->getSqlActiveSnippet();
        $sSelect .= " and $sArticleTable.oxparentid = '' and $sArticleTable.oxissearch = 1 ";

        // #671
        if ( $sSearchVendor ) {
            $sSelect .= " and $sArticleTable.oxvendorid = ".$oDb->quote( $sSearchVendor )." ";
        }

        if ( $sSearchManufacturer ) {
            $sSelect .= " and $sArticleTable.oxmanufacturerid = ".$oDb->quote( $sSearchManufacturer )." ";
        }
        $sSelect .= $sWhere;

        if ($this->_sCustomSorting) {
            $sSelect .= " order by {$this->_sCustomSorting} ";
        }

        $this->_createIdListFromSql($sSelect);
    }

    public function loadPriceIds( $dPriceFrom, $dPriceTo )
    {
        $sSelect = $this->_getPriceSelect( $dPriceFrom, $dPriceTo );
        $this->_createIdListFromSql( $sSelect );
    }

    public function loadPriceArticles( $dPriceFrom, $dPriceTo, $oCategory = null)
    {
        $sSelect =  $this->_getPriceSelect( $dPriceFrom, $dPriceTo );

        startProfile("loadPriceArticles");
        $this->selectString( $sSelect);
        stopProfile("loadPriceArticles");

        if ( !$oCategory ) {
            return $this->count();
        }

        return oxRegistry::get("oxUtilsCount")->getPriceCatArticleCount( $oCategory->getId(), $dPriceFrom, $dPriceTo );
    }

    public function loadVendorIDs( $sVendorId)
    {
        $sSelect = $this->_getVendorSelect($sVendorId);
        $this->_createIdListFromSql($sSelect);
    }

    public function loadManufacturerIDs( $sManufacturerId)
    {
        $sSelect = $this->_getManufacturerSelect($sManufacturerId);
        $this->_createIdListFromSql($sSelect);
    }

    public function loadVendorArticles( $sVendorId, $oVendor = null )
    {
        $sSelect = $this->_getVendorSelect($sVendorId);
        $this->selectString( $sSelect);

        return oxRegistry::get("oxUtilsCount")->getVendorArticleCount( $sVendorId );
    }

    public function loadManufacturerArticles( $sManufacturerId, $oManufacturer = null )
    {
        $sSelect = $this->_getManufacturerSelect($sManufacturerId);
        $this->selectString( $sSelect);

        return oxRegistry::get("oxUtilsCount")->getManufacturerArticleCount( $sManufacturerId );
    }

    public function loadTagArticles( $sTag, $iLang )
    {
        $oListObject = $this->getBaseObject();
        $sArticleTable = $oListObject->getViewName();
        $sArticleFields = $oListObject->getSelectFields();
        $sViewName = getViewName( 'oxartextends', $iLang );

        $oTag = oxNew( 'oxtag', $sTag );
        $oTag->addUnderscores();
        $sTag = $oTag->get();

        $sQ = "select {$sArticleFields} from {$sViewName} inner join {$sArticleTable} on ".
              "{$sArticleTable}.oxid = {$sViewName}.oxid where {$sArticleTable}.oxparentid = '' AND match ( {$sViewName}.oxtags ) ".
              "against( ".oxDb::getDb()->quote( "\"".$sTag."\"" )." IN BOOLEAN MODE )";

        // checking stock etc
        if ( ( $sActiveSnippet = $oListObject->getSqlActiveSnippet() ) ) {
            $sQ .= " and {$sActiveSnippet}";
        }

        if ( $this->_sCustomSorting ) {
            $sSort = $this->_sCustomSorting;
            if (strpos($sSort, '.') === false) {
                $sSort = $sArticleTable.'.'.$sSort;
            }
            $sQ .= " order by $sSort ";
        }

        $this->selectString( $sQ );

        // calc count - we can not use count($this) here as we might have paging enabled
        return oxRegistry::get("oxUtilsCount")->getTagArticleCount( $sTag, $iLang );
    }

    public function getTagArticleIds( $sTag, $iLang )
    {
        $oListObject = $this->getBaseObject();
        $sArticleTable = $oListObject->getViewName();
        $sViewName = getViewName( 'oxartextends', $iLang );

        $oTag = oxNew( 'oxtag', $sTag );
        $oTag->addUnderscores();
        $sTag = $oTag->get();

        $sQ = "select {$sViewName}.oxid from {$sViewName} inner join {$sArticleTable} on ".
            "{$sArticleTable}.oxid = {$sViewName}.oxid where {$sArticleTable}.oxparentid = '' and {$sArticleTable}.oxissearch = 1 and ".
            "match ( {$sViewName}.oxtags ) ".
            "against( ".oxDb::getDb()->quote( "\"".$sTag."\"" )." IN BOOLEAN MODE )";

        // checking stock etc
        if ( ( $sActiveSnippet = $oListObject->getSqlActiveSnippet() ) ) {
            $sQ .= " and {$sActiveSnippet}";
        }

        if ( $this->_sCustomSorting ) {
            $sSort = $this->_sCustomSorting;
            if (strpos($sSort, '.') === false) {
                $sSort = $sArticleTable.'.'.$sSort;
            }
            $sQ .= " order by $sSort ";
        }

        return $this->_createIdListFromSql( $sQ );
    }

    public function loadIds($aIds)
    {
        if (!count($aIds)) {
            $this->clear();
            return;
        }

        foreach ($aIds as $iKey => $sVal) {
            $aIds[$iKey] = oxDb::getInstance()->escapeString($sVal);
        }

        $oBaseObject    = $this->getBaseObject();
        $sArticleTable  = $oBaseObject->getViewName();
        $sArticleFields = $oBaseObject->getSelectFields();

        $sSelect  = "select $sArticleFields from $sArticleTable ";
        $sSelect .= "where $sArticleTable.oxid in ( '".implode("','", $aIds)."' ) and ";
        $sSelect .= $oBaseObject->getSqlActiveSnippet();

        $this->selectString($sSelect);
    }

    public function loadOrderArticles($aOrders)
    {
        if (!count($aOrders)) {
            $this->clear();
            return;
        }

        foreach ($aOrders as $iKey => $oOrder) {
            $aOrdersIds[] = $oOrder->getId();
        }

        $oBaseObject    = $this->getBaseObject();
        $sArticleTable  = $oBaseObject->getViewName();
        $sArticleFields = $oBaseObject->getSelectFields();
        $sArticleFields = str_replace( "`$sArticleTable`.`oxid`", "`oxorderarticles`.`oxartid` AS `oxid`", $sArticleFields );

        $sSelect  = "SELECT $sArticleFields FROM oxorderarticles ";
        $sSelect .= "left join $sArticleTable on oxorderarticles.oxartid = $sArticleTable.oxid ";
        $sSelect .= "WHERE oxorderarticles.oxorderid IN ( '".implode("','", $aOrdersIds)."' ) ";
        $sSelect .= "order by $sArticleTable.oxid ";

        $this->selectString( $sSelect );

        // not active or not available products must not have button "tobasket"
        $sNow = date('Y-m-d H:i:s');
        foreach ( $this as $oArticle ) {
            if ( !$oArticle->oxarticles__oxactive->value  &&
             (  $oArticle->oxarticles__oxactivefrom->value > $sNow ||
                $oArticle->oxarticles__oxactiveto->value < $sNow
             )) {
                $oArticle->setBuyableState( false );
            }
        }
    }

    public function loadStockRemindProducts( $aBasketContents )
    {
        if ( is_array( $aBasketContents ) && count( $aBasketContents ) ) {
            $oDb = oxDb::getDb();
            foreach ( $aBasketContents as $oBasketItem ) {
                $aArtIds[] = $oDb->quote($oBasketItem->getProductId());
            }

            $oBaseObject = $this->getBaseObject();

            $sFieldNames = $oBaseObject->getSelectFields();
            $sTable      = $oBaseObject->getViewName();

            // fetching actual db stock state and reminder status
            $sQ = "select {$sFieldNames} from {$sTable} where {$sTable}.oxid in ( ".implode( ",", $aArtIds )." ) and
                          oxremindactive = '1' and oxstock <= oxremindamount";
            $this->selectString( $sQ );

            // updating stock reminder state
            if ( $this->count() ) {
                $sQ = "update {$sTable} set oxremindactive = '2' where {$sTable}.oxid in ( ".implode( ",", $aArtIds )." ) and
                              oxremindactive = '1' and oxstock <= oxremindamount";
                $oDb->execute( $sQ );
            }
        }
    }

    public function renewPriceUpdateTime()
    {
        $oDb = oxDb::getDb();

        // fetching next update time
        $sQ = "select unix_timestamp( oxupdatepricetime ) from %s where oxupdatepricetime > 0 order by oxupdatepricetime asc";
        $iTimeToUpdate = $oDb->getOne( sprintf( $sQ, "`oxarticles`" ), false, false );


        // next day?
        $iCurrUpdateTime = oxRegistry::get("oxUtilsDate")->getTime();
        $iNextUpdateTime = $iCurrUpdateTime + 3600 * 24;

        // renew next update time
        if ( !$iTimeToUpdate || $iTimeToUpdate > $iNextUpdateTime ) {
            $iTimeToUpdate = $iNextUpdateTime;
        }

        $this->getConfig()->saveShopConfVar( "int", "iTimeToUpdatePrices", $iTimeToUpdate );

        return $iTimeToUpdate;
    }

    public function updateUpcomingPrices( $blForceUpdate = false )
    {
        $blUpdated = false;

        if ( $blForceUpdate || $this->_canUpdatePrices() ) {

            $oDb = oxDb::getDb();

            $oDb->startTransaction();

            $sCurrUpdateTime = date( "Y-m-d H:i:s", oxRegistry::get("oxUtilsDate")->getTime() );


            // updating oxarticles
            $sQ = "UPDATE %s SET
                       `oxprice`  = IF( `oxupdateprice` > 0, `oxupdateprice`, `oxprice` ),
                       `oxpricea` = IF( `oxupdatepricea` > 0, `oxupdatepricea`, `oxpricea` ),
                       `oxpriceb` = IF( `oxupdatepriceb` > 0, `oxupdatepriceb`, `oxpriceb` ),
                       `oxpricec` = IF( `oxupdatepricec` > 0, `oxupdatepricec`, `oxpricec` ),
                       `oxupdatepricetime` = 0,
                       `oxupdateprice`     = 0,
                       `oxupdatepricea`    = 0,
                       `oxupdatepriceb`    = 0,
                       `oxupdatepricec`    = 0
                   WHERE
                       `oxupdatepricetime` > 0 AND
                       `oxupdatepricetime` <= '{$sCurrUpdateTime}'" ;
            $blUpdated = $oDb->execute( sprintf( $sQ, "`oxarticles`" ) );


            // update oxvarminprice field value also
            $sQ = "CREATE TEMPORARY TABLE IF NOT EXISTS `__oxprices` (
                    `oxid` CHAR(32) character set latin1 collate latin1_general_ci NOT NULL,
                    `oxminprice` DOUBLE NOT NULL,
                    `oxmaxprice` DOUBLE NOT NULL,
                    PRIMARY KEY ( `oxid` )
                  ) ENGINE=MYISAM
                  SELECT `oxparentid` AS `oxid`, MIN(`oxprice`) AS `oxminprice`, MAX(`oxprice`) AS `oxmaxprice`
                      FROM `oxarticles` WHERE `oxparentid` <> '' GROUP BY `oxparentid`";

                $blUpdated = $oDb->execute( $sQ );

            $sQ = "UPDATE `oxarticles`
                    INNER JOIN `__oxprices` ON `__oxprices`.`oxid` = `oxarticles`.`oxid`
                  SET
                      `oxarticles`.`oxvarminprice` = `__oxprices`.`oxminprice`,
                      `oxarticles`.`oxvarmaxprice` = `__oxprices`.`oxmaxprice";

            $blUpdated = $oDb->execute( $sQ );
            $blUpdated = $oDb->execute( "DROP TEMPORARY TABLE `__oxprices`" );

            // renew update time in case update is not forced
            if ( !$blForceUpdate ) {
                $this->renewPriceUpdateTime();
            }

            $oDb->commitTransaction();

        }

        return $blUpdated;
    }

    protected function _createIdListFromSql( $sSql)
    {
        $rs = oxDb::getDb( oxDb::FETCH_MODE_ASSOC )->select( $sSql );
        if ($rs != false && $rs->recordCount() > 0) {
            while (!$rs->EOF) {
                $rs->fields = array_change_key_case($rs->fields, CASE_LOWER);
                $this[$rs->fields['oxid']] =  $rs->fields['oxid']; //only the oxid
                $rs->moveNext();
            }
        }
    }

    protected function _getFilterIdsSql( $sCatId, $aFilter )
    {
        $sO2CView = getViewName( 'oxobject2category' );
        $sO2AView = getViewName( 'oxobject2attribute' );

        $sFilter = '';
        $iCnt    = 0;

        $oDb = oxDb::getDb();
        foreach ( $aFilter as $sAttrId => $sValue ) {
            if ( $sValue ) {
                if ( $sFilter ) {
                    $sFilter .= ' or ';
                }
                $sValue  = $oDb->quote( $sValue );
                $sAttrId = $oDb->quote( $sAttrId );

                $sFilter .= "( oa.oxattrid = {$sAttrId} and oa.oxvalue = {$sValue} )";
                $iCnt++;
            }
        }
        if ( $sFilter ) {
            $sFilter = "WHERE $sFilter ";
        }

        $sFilterSelect = "select oc.oxobjectid as oxobjectid, count(*) as cnt from ";
        $sFilterSelect.= "(SELECT * FROM $sO2CView WHERE $sO2CView.oxcatnid = '$sCatId' GROUP BY $sO2CView.oxobjectid, $sO2CView.oxcatnid) as oc ";
        $sFilterSelect.= "INNER JOIN $sO2AView as oa ON ( oa.oxobjectid = oc.oxobjectid ) ";
        return $sFilterSelect . "{$sFilter} GROUP BY oa.oxobjectid HAVING cnt = $iCnt ";
    }

    protected function _getFilterSql( $sCatId, $aFilter )
    {
        $sArticleTable = getViewName( 'oxarticles' );
        $aIds = oxDb::getDb(oxDb::FETCH_MODE_ASSOC)->getAll( $this->_getFilterIdsSql( $sCatId, $aFilter ) );
        $sIds = '';

        if ( $aIds ) {
            foreach ( $aIds as $aArt ) {
                if ( $sIds ) {
                    $sIds .= ', ';
                }
                $sIds .= oxDb::getDb()->quote( current( $aArt ) );
            }

            if ( $sIds ) {
                $sFilterSql = " and $sArticleTable.oxid in ( $sIds ) ";
            }
        // bug fix #0001695: if no articles found return false
        } elseif ( !( current( $aFilter ) == '' && count( array_unique( $aFilter ) ) == 1 ) ) {
            $sFilterSql = " and false ";
        }

        return $sFilterSql;
    }

    protected function _getCategorySelect( $sFields, $sCatId, $aSessionFilter )
    {
        $sArticleTable = getViewName( 'oxarticles' );
        $sO2CView      = getViewName( 'oxobject2category' );

        // ----------------------------------
        // sorting
        $sSorting = '';
        if ( $this->_sCustomSorting ) {
            $sSorting = " {$this->_sCustomSorting} , ";
        }

        // ----------------------------------
        // filtering ?
        $sFilterSql = '';
        $iLang = oxRegistry::getLang()->getBaseLanguage();
        if ( $aSessionFilter && isset( $aSessionFilter[$sCatId][$iLang] ) ) {
            $sFilterSql = $this->_getFilterSql($sCatId, $aSessionFilter[$sCatId][$iLang]);
        }

        $oDb = oxDb::getDb();

        $sSelect = "SELECT $sFields FROM $sO2CView as oc left join $sArticleTable
                    ON $sArticleTable.oxid = oc.oxobjectid
                    WHERE ".$this->getBaseObject()->getSqlActiveSnippet()." and $sArticleTable.oxparentid = ''
                    and oc.oxcatnid = ".$oDb->quote($sCatId)." $sFilterSql ORDER BY $sSorting oc.oxpos, oc.oxobjectid ";

        return $sSelect;
    }

    protected function _getCategoryCountSelect( $sCatId, $aSessionFilter )
    {
        $sArticleTable = getViewName( 'oxarticles' );
        $sO2CView      = getViewName( 'oxobject2category' );


        // ----------------------------------
        // filtering ?
        $sFilterSql = '';
        $iLang = oxRegistry::getLang()->getBaseLanguage();
        if ( $aSessionFilter && isset( $aSessionFilter[$sCatId][$iLang] ) ) {
            $sFilterSql = $this->_getFilterSql($sCatId, $aSessionFilter[$sCatId][$iLang]);
        }

        $oDb = oxDb::getDb();

        $sSelect = "SELECT COUNT(*) FROM $sO2CView as oc left join $sArticleTable
                    ON $sArticleTable.oxid = oc.oxobjectid
                    WHERE ".$this->getBaseObject()->getSqlActiveSnippet()." and $sArticleTable.oxparentid = ''
                    and oc.oxcatnid = ".$oDb->quote($sCatId)." $sFilterSql ";

        return $sSelect;
    }

    protected function _getSearchSelect( $sSearchString )
    {
        // check if it has string at all
        if ( !$sSearchString || !str_replace( ' ', '', $sSearchString ) ) {
            return '';
        }

        $oDb = oxDb::getDb();
        $myConfig = $this->getConfig();
        $myUtils  = oxRegistry::getUtils();
        $sArticleTable = $this->getBaseObject()->getViewName();

        $aSearch = explode( ' ', $sSearchString);

        $sSearch  = ' and ( ';
        $blSep = false;

        // #723
        if ( $myConfig->getConfigParam( 'blSearchUseAND' ) ) {
            $sSearchSep = ' and ';
        } else {
            $sSearchSep = ' or ';
        }

        $aSearchCols = $myConfig->getConfigParam( 'aSearchCols' );
        $oBaseObject = $this->getBaseObject();
        $myUtilsString = oxRegistry::get("oxUtilsString");
        foreach ( $aSearch as $sSearchString) {

            if ( !strlen( $sSearchString ) ) {
                continue;
            }

            if ( $blSep ) {
                $sSearch .= $sSearchSep;
            }
            $blSep2   = false;
            $sSearch .= '( ';

            $sUml = $myUtilsString->prepareStrForSearch($sSearchString);
            foreach ( $aSearchCols as $sField ) {

                if ( $blSep2) {
                    $sSearch  .= ' or ';
                }

                // as long description now is on different table table must differ
                if ( $sField == 'oxlongdesc' || $sField == 'oxtags') {
                    $sSearchTable = getViewName( 'oxartextends' );
                } else {
                    $sSearchTable = $sArticleTable;
                }

                $sSearch .= $sSearchTable.'.'.$sField.' like '.$oDb->quote('%'.$sSearchString.'%') . ' ';
                if ( $sUml ) {
                    $sSearch  .= ' or '.$sSearchTable.'.'.$sField.' like '.$oDb->quote('%'.$sUml.'%');
                }
                $blSep2 = true;
            }
            $sSearch  .= ' ) ';
            $blSep = true;
        }
        $sSearch .= ' ) ';

        return $sSearch;
    }

    protected function _getPriceSelect( $dPriceFrom, $dPriceTo )
    {
        $oBaseObject   = $this->getBaseObject();
        $sArticleTable = $oBaseObject->getViewName();
        $sSelectFields = $oBaseObject->getSelectFields();

        $sSubSelect = "";

        $sSelect  = "select {$sSelectFields} from {$sArticleTable} where oxvarminprice >= 0 ";
        $sSelect .= $dPriceTo ? "and oxvarminprice <= " . (double)$dPriceTo . " " : " ";
        $sSelect .= $dPriceFrom ? "and oxvarminprice  >= " . (double)$dPriceFrom . " " : " ";

        $sSelect .= " and ".$oBaseObject->getSqlActiveSnippet()." and {$sArticleTable}.oxissearch = 1";

        if ( !$this->_sCustomSorting ) {
            $sSelect .= " order by {$sArticleTable}.oxvarminprice asc , {$sArticleTable}.oxid";
        } else {
            $sSelect .= " order by {$this->_sCustomSorting}, {$sArticleTable}.oxid ";
        }

        return $sSelect;
    }

    protected function _getVendorSelect( $sVendorId )
    {
        $sArticleTable = getViewName('oxarticles');
        $oBaseObject = $this->getBaseObject();
        $sFieldNames = $oBaseObject->getSelectFields();
        $sSelect  = "select $sFieldNames from $sArticleTable ";
        $sSelect .= "where $sArticleTable.oxvendorid = ".oxDb::getDb()->quote($sVendorId)." ";
        $sSelect .= " and " . $oBaseObject->getSqlActiveSnippet() . " and $sArticleTable.oxparentid = ''  ";

        if ( $this->_sCustomSorting ) {
            $sSelect .= " ORDER BY {$this->_sCustomSorting} ";
        }

        return $sSelect;
    }

    protected function _getManufacturerSelect( $sManufacturerId )
    {
        $sArticleTable = getViewName('oxarticles');
        $oBaseObject = $this->getBaseObject();
        $sFieldNames = $oBaseObject->getSelectFields();
        $sSelect  = "select $sFieldNames from $sArticleTable ";
        $sSelect .= "where $sArticleTable.oxmanufacturerid = ".oxDb::getDb()->quote($sManufacturerId)." ";
        $sSelect .= " and " . $oBaseObject->getSqlActiveSnippet() . " and $sArticleTable.oxparentid = ''  ";

        if ( $this->_sCustomSorting ) {
            $sSelect .= " ORDER BY {$this->_sCustomSorting} ";
        }

        return $sSelect;
    }

    protected function _canUpdatePrices()
    {
        $oConfig = $this->getConfig();
        $blCan = false;

        // crontab is off?
        if ( !$oConfig->getConfigParam( "blUseCron" ) ) {
            $iTimeToUpdate = $oConfig->getConfigParam( "iTimeToUpdatePrices" );
            if ( !$iTimeToUpdate || $iTimeToUpdate <= oxRegistry::get("oxUtilsDate")->getTime() ) {
                $blCan = true;
            }
        }
        return $blCan;
    }

}