oxseoencoder.php

Go to the documentation of this file.

<?php

class oxSeoEncoder extends oxSuperCfg
{
    protected static $_aReservedWords = array( 'admin' );

    protected static $_aReservedEntryKeys = null;

    protected static $_sSeparator = null;

    protected $_iIdLength = 255;

    protected static $_sPrefix = null;

    protected $_sAddParams = null;

    protected static $_instance = null;

    protected static $_aFixedCache = array();

    protected static $_sCacheKey = null;

    protected static $_aCache = array();

    protected $_iMaxUrlLength = null;

    public static function getInstance()
    {
        if ( defined( 'OXID_PHP_UNIT' ) ) {
            self::$_instance = modInstances::getMod( __CLASS__ );
        }

        if (!self::$_instance) {
            self::$_instance = oxNew("oxSeoEncoder");
            if ( defined( 'OXID_PHP_UNIT' ) ) {
                modInstances::addMod( __CLASS__, self::$_instance);
            }
        }

        return self::$_instance;

    }

    public function addLanguageParam( $sSeoUrl, $iLang )
    {
        $iLang    = (int) $iLang;
        $iDefLang = (int) $this->getConfig()->getConfigParam( 'iDefSeoLang' );
        $aLangIds = oxLang::getInstance()->getLanguageIds();

        if ( $iLang != $iDefLang && isset( $aLangIds[$iLang] ) && getStr()->strpos( $sSeoUrl, $aLangIds[$iLang] . '/' ) !== 0 ) {
            $sSeoUrl = $aLangIds[$iLang] . '/'.$sSeoUrl;
        }

        return $sSeoUrl;
    }

    protected function _processSeoUrl( $sSeoUrl, $sObjectId = null, $iLang = null, $blExclude = false )
    {
        if (!$blExclude) {
            $sSeoUrl = $this->addLanguageParam( $sSeoUrl, $iLang );
        }
        return $this->_getUniqueSeoUrl( $sSeoUrl, $sObjectId, $iLang );
    }

    public function __construct()
    {
        $myConfig = $this->getConfig();
        if (!self::$_sSeparator) {
            $this->setSeparator( $myConfig->getConfigParam( 'sSEOSeparator' ) );
        }
        if (!self::$_sPrefix) {
            $this->setPrefix( $myConfig->getConfigParam( 'sSEOuprefix' ) );
        }
        $this->setReservedWords( $myConfig->getConfigParam( 'aSEOReservedWords' ) );
    }

    protected function _copyToHistory( $sId, $iShopId, $iLang, $sType = null, $sNewId = null )
    {
        $oDb = oxDb::getDb();
        $sObjectid = $sNewId?$oDb->quote( $sNewId ):'oxobjectid';
        $sType     = $sType?"oxtype =".$oDb->quote( $sType )." and":'';
        $iLang     = (int) $iLang;

        // moving
        $sSub = "select $sObjectid, MD5( LOWER( oxseourl ) ), oxshopid, oxlang, now() from oxseo
                 where {$sType} oxobjectid = ".$oDb->quote( $sId )." and oxshopid = ".$oDb->quote( $iShopId )." and
                 oxlang = {$iLang} and oxexpired = '1'";
        $sQ   = "replace oxseohistory ( oxobjectid, oxident, oxshopid, oxlang, oxinsert ) {$sSub}";
        $oDb->execute( $sQ );
    }

    public function getDynamicObjectId( $iShopId, $sStdUrl )
    {
        return $this->_getStaticObjectId( $iShopId, $sStdUrl );
    }

    protected function _getDynamicUri( $sStdUrl, $sSeoUrl, $iLang )
    {
        $iShopId = $this->getConfig()->getShopId();

        $sStdUrl   = $this->_trimUrl( $sStdUrl );
        $sObjectId = $this->getDynamicObjectId( $iShopId, $sStdUrl );
        $sSeoUrl   = $this->_prepareUri( $this->addLanguageParam( $sSeoUrl, $iLang ) );

        //load details link from DB
        $sOldSeoUrl = $this->_loadFromDb( 'dynamic', $sObjectId, $iLang );
        if ( $sOldSeoUrl === $sSeoUrl ) {
            $sSeoUrl = $sOldSeoUrl;
        } else {

            if ( $sOldSeoUrl ) {
                // old must be transferred to history
                $this->_copyToHistory( $sObjectId, $iShopId, $iLang, 'dynamic' );
            }

            // creating unique
            $sSeoUrl = $this->_processSeoUrl( $sSeoUrl, $sObjectId, $iLang );

            // inserting
            $this->_saveToDb( 'dynamic', $sObjectId, $sStdUrl, $sSeoUrl, $iLang, $iShopId );
        }

        return $sSeoUrl;
    }

    protected function _getFullUrl( $sSeoUrl, $iLang = null, $blSsl = false )
    {
        if ( $sSeoUrl ) {
            $sFullUrl = ( $blSsl ? $this->getConfig()->getSslShopUrl( $iLang ) : $this->getConfig()->getShopUrl( $iLang, false ) ) . $sSeoUrl;
            return oxUtilsUrl::getInstance()->processSeoUrl( $sFullUrl );
        }
        return false;
    }

    protected function _getSeoIdent( $sSeoUrl )
    {
        return md5( strtolower( $sSeoUrl ) );
    }

    protected function _getStaticUri( $sStdUrl, $iShopId, $iLang )
    {
        $sStdUrl = $this->_trimUrl( $sStdUrl, $iLang );
        return $this->_loadFromDb( 'static', $this->_getStaticObjectId( $iShopId, $sStdUrl ), $iLang );
    }

    protected function _getUrlExtension()
    {
        return;
    }

    protected function _getUniqueSeoUrl( $sSeoUrl, $sObjectId = null, $iObjectLang = null )
    {
        $sSeoUrl = $this->_prepareUri( $sSeoUrl );
        $oStr = getStr();
        $sExt = '';
        if ( $oStr->preg_match( '/(\.html?|\/)$/i', $sSeoUrl, $aMatched ) ) {
            $sExt = $aMatched[0];
        }
        $sBaseSeoUrl = $sSeoUrl;
        if ( $sExt && $oStr->substr( $sSeoUrl, 0 - $oStr->strlen( $sExt ) ) == $sExt ) {
            $sBaseSeoUrl = $oStr->substr( $sSeoUrl, 0, $oStr->strlen( $sSeoUrl ) - $oStr->strlen( $sExt ) );
        }

        $oDb = oxDb::getDb();
        $iShopId = $this->getConfig()->getShopId();
        $iCnt = 0;
        $sCheckSeoUrl = $this->_trimUrl( $sSeoUrl );
        $sQ = "select 1 from oxseo where oxshopid = '{$iShopId}'";

        // skipping self
        if ( $sObjectId && isset($iObjectLang) ) {
            $iObjectLang = (int) $iObjectLang;
            $sQ .= " and not (oxobjectid = " . $oDb->quote( $sObjectId ) . " and oxlang = $iObjectLang)";
        }

        while ( $oDb->getOne( $sQ ." and oxident= " . $oDb->quote( $this->_getSeoIdent( $sCheckSeoUrl ) ) ) ) {
            $sAdd = '';
            if ( self::$_sPrefix ) {
                $sAdd = self::$_sSeparator . self::$_sPrefix;
            }
            if ( $iCnt ) {
                $sAdd .= self::$_sSeparator . $iCnt;
            }
            ++$iCnt;

            $sSeoUrl = $sBaseSeoUrl . $sAdd . $sExt;
            $sCheckSeoUrl = $this->_trimUrl( $sSeoUrl );
        }
        return $sSeoUrl;
    }

    protected function _isFixed( $sType, $sId, $iLang, $iShopId = null, $sParams = null, $blStrictParamsCheck = true)
    {
        if ( $iShopId === null ) {
            $iShopId = $this->getConfig()->getShopId();
        }
        $iLang = (int) $iLang;

        if ( !isset( self::$_aFixedCache[$sType][$sShopId][$sId][$iLang] ) ) {
            $oDb = oxDb::getDb( true );

            $sQ = "SELECT `oxfixed`
                FROM `oxseo`
                WHERE `oxtype` = ".$oDb->quote( $sType )."
                   AND `oxobjectid` = ".$oDb->quote( $sId ) ."
                   AND `oxshopid` = ".$oDb->quote( $iShopId )."
                   AND `oxlang` = '{$iLang}'";


            if ( $sParams ) {
                $sQ .= " AND `oxparams` = '{$sParams}'";
            } elseif ( ! $blStrictParamsCheck ) {
                $sQ .= " ORDER BY `oxparams` ASC" ;
            }

            $sQ .= " LIMIT 1";

            self::$_aFixedCache[$sType][$sShopId][$sId][$iLang] = (bool) $oDb->getOne( $sQ );
        }
        return self::$_aFixedCache[$sType][$sShopId][$sId][$iLang];
    }

    protected function _getCacheKey( $sType, $iLang = null, $iShopId = null, $sParams = null )
    {
        $blAdmin = $this->isAdmin();
        if ( !$blAdmin && $sType !== "oxarticle" ) {
            return $sType . ( (int) $iLang ) . ( (int) $iShopId ) . "seo";
        }

        // use cache in non admin mode
        if ( self::$_sCacheKey === null ) {
            self::$_sCacheKey = false;
            if ( !$blAdmin && ( $oView = $this->getConfig()->getActiveView() ) ) {
                self::$_sCacheKey = md5( $oView->getViewId() ) . "seo";
            }
        }
        return self::$_sCacheKey;
    }

    protected function _loadFromCache( $sCacheIdent, $sType, $iLang = null, $iShopId = null, $sParams = null )
    {
        startProfile( "seoencoder_loadFromCache" );

        $sCacheKey = $this->_getCacheKey( $sType, $iLang, $iShopId, $sParams );
        $sCache = false;

        if ( $sCacheKey && !isset( self::$_aCache[$sCacheKey] ) ) {
            self::$_aCache[$sCacheKey] = oxUtils::getInstance()->fromFileCache( $sCacheKey );
        }

        if ( isset( self::$_aCache[$sCacheKey] ) && isset( self::$_aCache[$sCacheKey][$sCacheIdent] ) ) {
            $sCache = self::$_aCache[$sCacheKey][$sCacheIdent];
        }

        stopProfile( "seoencoder_loadFromCache" );
        return $sCache;
    }

    protected function _saveInCache( $sCacheIdent, $sCache, $sType, $iLang = null, $iShopId = null, $sParams = null )
    {
        startProfile( "seoencoder_saveInCache" );

        $blSaved = false;
        if ( $sCache && ( $sCacheKey = $this->_getCacheKey( $sType, $iLang, $iShopId, $sParams ) ) !== false ) {
            self::$_aCache[$sCacheKey][$sCacheIdent] = $sCache;
            $blSaved = oxUtils::getInstance()->toFileCache( $sCacheKey, self::$_aCache[$sCacheKey] );
        }

        stopProfile( "seoencoder_saveInCache" );
        return $blSaved;
    }

    protected function _loadFromDb( $sType, $sId, $iLang, $iShopId = null, $sParams = null, $blStrictParamsCheck = true)
    {
        if ( $iShopId === null ) {
            $iShopId = $this->getConfig()->getShopId();
        }

        $iLang = (int) $iLang;

        $sQ = "
            SELECT
                `oxfixed`,
                `oxseourl`,
                `oxexpired`,
                `oxtype`
            FROM `oxseo`
            WHERE `oxtype` = ".oxDb::getDb()->quote( $sType )."
               AND `oxobjectid` = ".oxDb::getDb()->quote( $sId ) ."
               AND `oxshopid` = ".oxDb::getDb()->quote( $iShopId )."
               AND `oxlang` = '{$iLang}'";

        if ( $sParams ) {
            $sQ .= " AND `oxparams` = '{$sParams}'";
        } elseif ( ! $blStrictParamsCheck) {
            $sQ .= " ORDER BY `oxparams` ASC" ;
        }

        $sQ .= " LIMIT 1";

        // caching to avoid same queries..
        $sIdent = md5( $sQ );

        // looking in cache
        if ( ( $sSeoUrl = $this->_loadFromCache( $sIdent, $sType, $iLang, $iShopId, $sParams ) ) === false ) {
            $oDb = oxDb::getDb( true );
            $oRs = $oDb->execute( $sQ );
            if ( $oRs && $oRs->recordCount() > 0 && !$oRs->EOF ) {
                // moving expired static urls to history ..
                if ( $oRs->fields['oxexpired'] && ( $oRs->fields['oxtype'] == 'static' || $oRs->fields['oxtype'] == 'dynamic' ) ) {
                    // if expired - copying to history, marking as not expired
                    $this->_copyToHistory( $sId, $iShopId, $iLang );
                    $oDb->execute( "update oxseo set oxexpired = 0 where oxobjectid = ".$oDb->quote( $sId )." and oxlang = '{$iLang}'" );
                    $sSeoUrl = $oRs->fields['oxseourl'];
                } elseif ( !$oRs->fields['oxexpired'] || $oRs->fields['oxfixed'] ) {
                    // if seo url is available and is valid
                    $sSeoUrl = $oRs->fields['oxseourl'];
                }

                // storing in cache
                $this->_saveInCache( $sIdent, $sSeoUrl, $sType, $iLang, $iShopId, $sParams );
            }
        }
        return $sSeoUrl;
    }

    protected function _getReservedEntryKeys()
    {
        if ( !isset( self::$_aReservedEntryKeys ) || !is_array( self::$_aReservedEntryKeys ) ) {
            $sDir = getShopBasePath();
            self::$_aReservedEntryKeys = array_map('preg_quote', self::$_aReservedWords, array('#'));
            $oStr = getStr();
            foreach ( glob( "$sDir/*" ) as $sFile ) {
                if ( $oStr->preg_match( '/^(.+)\.php[0-9]*$/i', basename( $sFile ), $aMatches ) ) {
                    self::$_aReservedEntryKeys[] = preg_quote( $aMatches[0], '#' );
                    self::$_aReservedEntryKeys[] = preg_quote( $aMatches[1], '#' );
                } elseif ( is_dir( $sFile ) ) {
                    self::$_aReservedEntryKeys[] = preg_quote( basename( $sFile ), '#' );
                }
            }
            self::$_aReservedEntryKeys = array_unique(self::$_aReservedEntryKeys);
        }
        return self::$_aReservedEntryKeys;
    }

    protected function _prepareUri( $sUri )
    {
        // decoding entities
        $sUri = $this->encodeString( $sUri );

        // basic string preparation
        $sUri = strip_tags( $sUri );
        $oStr = getStr();


        // if found ".html" or "/" at the end - removing it temporary
        $oStr = getStr();
        $sExt = $this->_getUrlExtension();
        if ($sExt === null) {
            $aMatched = array();
            if ( $oStr->preg_match( '/(\.html?|\/)$/i', $sUri, $aMatched ) ) {
                $sExt = $aMatched[0];
            } else {
                $sExt = '/';
            }
        }
        if ( $sExt && $oStr->substr( $sUri, 0 - $oStr->strlen( $sExt ) ) == $sExt ) {
            $sUri = $oStr->substr( $sUri, 0, $oStr->strlen( $sUri ) - $oStr->strlen( $sExt ) );
        }

        // removing any special characters
        $sRegExp = '/[^A-Za-z0-9'.preg_quote( self::$_sSeparator, '/').preg_quote( self::$_sPrefix, '/').'\/]+/';
        $sUri  = $oStr->preg_replace( array( "/\W*\/\W*/", $sRegExp ), array( "/", self::$_sSeparator ), $sUri );

        // SEO id is empty ?
        if ( !$sUri && self::$_sPrefix ) {
            $sUri = $this->_prepareUri( self::$_sPrefix );
        }

        $sAdd = '';
        if ('/' != self::$_sSeparator) {
            $sAdd = self::$_sSeparator . self::$_sPrefix;
            $sUri = trim($sUri, self::$_sSeparator);
        } else {
            $sAdd = '_' . self::$_sPrefix;
        }

        // binding the ending back
        $sUri .= $sExt;

        // fix for not having url, which executes through /other/ script then seo decoder
        $sUri = $oStr->preg_replace( "#^(/*)(".implode('|', $this->_getReservedEntryKeys()).")(/|$)#i", "\$1\$2$sAdd\$3", $sUri );

        // cleaning
        return $oStr->preg_replace( array( '|//+|', '/' . preg_quote( self::$_sSeparator . self::$_sSeparator, '/' ) .'+/' ),
                             array( '/', self::$_sSeparator ), $sUri );
    }


    protected function _prepareTitle( $sTitle, $blSkipTruncate = false )
    {
        $sTitle = $this->encodeString( $sTitle );
        $sSep = self::$_sSeparator;
        if (!$sSep || ('/' == $sSep)) {
            $sSep = '_';
        }

        $sRegExp = '/[^A-Za-z0-9\/'.preg_quote( self::$_sPrefix, '/').preg_quote($sSep, '/').']+/';
        $sTitle = preg_replace( array("#/+#", $sRegExp, "# +#", "#(".preg_quote($sSep, '/').")+#"), $sSep, $sTitle );

        $oStr = getStr();
        // smart truncate
        if ( !$blSkipTruncate && $oStr->strlen( $sTitle ) > $this->_iIdLength ) {
            $iFirstSpace = $oStr->strpos( $sTitle, $sSep, $this->_iIdLength);
            if ( $iFirstSpace !== false ) {
                $sTitle = $oStr->substr( $sTitle, 0, $iFirstSpace );
            }
        }

        $sTitle = trim( $sTitle, $sSep );

        if (!$sTitle) {
            return self::$_sPrefix;
        }
        // cleaning
        return $sTitle;
    }


    protected function _saveToDb( $sType, $sObjectId, $sStdUrl, $sSeoUrl, $iLang, $iShopId = null, $blFixed = null, $sParams = null )
    {
        $oDb = oxDb::getDb( true );
        if ( $iShopId === null ) {
            $iShopId = $this->getConfig()->getShopId();
        }

        $iLang = (int) $iLang;

        $sStdUrl = $this->_trimUrl( $sStdUrl );
        $sSeoUrl = $this->_trimUrl( $sSeoUrl );
        $sIdent  = $this->_getSeoIdent( $sSeoUrl );

        // transferring old url, thus current url will be regenerated
        $sQtedObjectId = $oDb->quote( $sObjectId );
        $iQtedShopId   = $oDb->quote( $iShopId );
        $sQtedType     = $oDb->quote( $sType );
        $sQtedSeoUrl   = $oDb->quote( $sSeoUrl );
        $sQtedStdUrl   = $oDb->quote( $sStdUrl );
        $sQtedParams   = $oDb->quote( $sParams );
        $sQtedIdent    = $oDb->quote( $sIdent );

        // transferring old url, thus current url will be regenerated
        $sQ  = "select oxfixed, oxexpired, ( oxstdurl like {$sQtedStdUrl} ) as samestdurl,
                oxseourl like {$sQtedSeoUrl} as sameseourl from oxseo where oxtype = {$sQtedType} and
                oxobjectid = {$sQtedObjectId} and oxshopid = {$iQtedShopId}  and oxlang = {$iLang} ";

        $sQ .= $sParams ? " and oxparams = {$sQtedParams} " : '';
        //$sQ .= isset( $blFixed ) ? " and oxfixed = " . ( (int) $blFixed ) . " " : '';
        $sQ .= "limit 1";

        $oRs = $oDb->execute( $sQ );
        if ( $oRs && $oRs->recordCount() > 0 && !$oRs->EOF ) {
            if ( $oRs->fields['samestdurl'] && $oRs->fields['sameseourl'] && $oRs->fields['oxexpired'] ) {
                // fixed state change
                $sFixed = isset( $blFixed ) ? ", oxfixed = " . ( (int) $blFixed ) . " " : '';
                // nothing was changed - setting expired status back to 0
                $sSql  = "update oxseo set oxexpired = 0 {$sFixed} where oxtype = {$sQtedType} and
                          oxobjectid = {$sQtedObjectId} and oxshopid = {$iQtedShopId} and oxlang = {$iLang} ";
                $sSql .= $sParams ? " and oxparams = {$sQtedParams} " : '';
                $sSql .= " limit 1";

                return $oDb->execute( $sSql );
            } elseif ( $oRs->fields['oxexpired'] ) {
                // copy to history
                $this->_copyToHistory( $sObjectId, $iShopId, $iLang, $sType );
            }
        }

        // inserting new or updating
        $sParams = $sParams ? $oDb->quote( $sParams ) :'""';
        $blFixed = (int) $blFixed;

        $sQ  = "insert into oxseo
                    (oxobjectid, oxident, oxshopid, oxlang, oxstdurl, oxseourl, oxtype, oxfixed, oxexpired, oxparams)
                values
                    ( {$sQtedObjectId}, {$sQtedIdent}, {$iQtedShopId}, {$iLang}, {$sQtedStdUrl}, {$sQtedSeoUrl}, {$sQtedType}, '$blFixed', '0', {$sParams} )
                on duplicate key update
                    oxident = {$sQtedIdent}, oxstdurl = {$sQtedStdUrl}, oxseourl = {$sQtedSeoUrl}, oxfixed = '$blFixed', oxexpired = '0'";

        return $oDb->execute( $sQ );
    }

    protected function _trimUrl( $sUrl, $iLang = null )
    {
        $myConfig = $this->getConfig();
        $oStr = getStr();
        $sUrl = str_replace( array( $myConfig->getShopUrl( $iLang, false ), $myConfig->getSslShopUrl( $iLang ) ), '', $sUrl );
        $sUrl = $oStr->preg_replace( '/(\?|&(amp;)?)(force_)?(admin_)?sid=[a-z0-9\.]+&?(amp;)?/i', '\1', $sUrl );
        $sUrl = $oStr->preg_replace( '/(\?|&(amp;)?)shp=[0-9]+&?(amp;)?/i', '\1', $sUrl );
        $sUrl = $oStr->preg_replace( '/(\?|&(amp;)?)lang=[0-9]+&?(amp;)?/i', '\1', $sUrl );
        $sUrl = $oStr->preg_replace( '/(\?|&(amp;)?)cur=[0-9]+&?(amp;)?/i', '\1', $sUrl );
        $sUrl = $oStr->preg_replace( '/(\?|&(amp;)?)stoken=[a-z0-9]+&?(amp;)?/i', '\1', $sUrl );
        $sUrl = $oStr->preg_replace( '/(\?|&(amp;)?)&(amp;)?/i', '\1', $sUrl );
        $sUrl = $oStr->preg_replace( '/(\?|&(amp;)?)+$/i', '', $sUrl );
        $sUrl = trim( $sUrl );

        // max length <= $this->_iMaxUrlLength
        $iLength = $this->_getMaxUrlLength();
        if ( $oStr->strlen( $sUrl ) > $iLength ) {
            $sUrl = $oStr->substr( $sUrl, 0, $iLength );
        }

        return $sUrl;
    }

    protected function _getMaxUrlLength()
    {
        if ( $this->_iMaxUrlLength === null ) {
            // max length <= 2048 / custom
            $this->_iMaxUrlLength = $this->getConfig()->getConfigParam( "iMaxSeoUrlLength" ) ;
            if ( !$this->_iMaxUrlLength ) {
                $this->_iMaxUrlLength = 2048;
            }
        }
        return $this->_iMaxUrlLength;
    }

    public function encodeString( $sString, $blReplaceChars = true )
    {
        // decoding entities
        $sString = getStr()->html_entity_decode( $sString );

        if ( $blReplaceChars ) {
            if ( $aReplaceChars = $this->getConfig()->getConfigParam( 'aSeoReplaceChars' ) ) {
                $sString = str_replace( array_keys( $aReplaceChars ), array_values( $aReplaceChars ), $sString );
            }
        }


        // special chars
        $aReplaceWhat = array( '&amp;', '&quot;', '&#039;', '&lt;', '&gt;' );
        return str_replace( $aReplaceWhat, '', $sString );
    }

    public function setSeparator( $sSeparator = null )
    {
        self::$_sSeparator = $sSeparator;
        if ( !self::$_sSeparator ) {
            self::$_sSeparator = '-';
        }
    }

    public function setPrefix( $sPrefix )
    {
        if ($sPrefix) {
            self::$_sPrefix = $sPrefix;
        } else {
            self::$_sPrefix = 'oxid';
        }
    }

    public function setIdLength( $iIdlength = null )
    {
        if ( isset( $iIdlength ) ) {
            $this->_iIdLength = $iIdlength;
        }
    }

    public function setReservedWords( $aReservedWords )
    {
        self::$_aReservedWords = array_merge( self::$_aReservedWords, $aReservedWords );
    }


    public function markAsExpired( $sId, $iShopId = null, $iExpStat = 1, $iLang = null, $sParams = null )
    {
        $oDb = oxDb::getDb();
        $sWhere  = $sId ? "where oxobjectid =  " . $oDb->quote( $sId ) : '';
        $sWhere .= isset( $iShopId ) ? ( $sWhere ? " and oxshopid = ". $oDb->quote( $iShopId ) : "where oxshopid = ". $oDb->quote( $iShopId ) ) : '';
        $sWhere .= $iLang ? ( $sWhere ? " and oxlang = '{$iLang}'" : "where oxlang = '{$iLang}'" ) : '';
        $sWhere .= $sParams ? ( $sWhere ? " and {$sParams}" : "where {$sParams}" ) : '';

        $sQ = "update oxseo set oxexpired =  " . $oDb->quote( $iExpStat ) . " $sWhere ";
        $oDb->execute( $sQ );
    }

    protected function _getPageUri( $oObject, $sType, $sStdUrl, $sSeoUrl, $sParams, $iLang = null, $blFixed = false )
    {
        if ( !isset( $iLang ) ) {
            $iLang = $oObject->getLanguage();
        }
        $iShopId = $this->getConfig()->getShopId();

        //load page link from DB
        $sOldSeoUrl = $this->_loadFromDb( $sType, $oObject->getId(), $iLang, $iShopId, $sParams );
        if ( !$sOldSeoUrl ) {
            // generating new..
            $sSeoUrl = $this->_processSeoUrl( $sSeoUrl, $oObject->getId(), $iLang );
            $this->_saveToDb( $sType, $oObject->getId(), $sStdUrl, $sSeoUrl, $iLang, $iShopId, (int) $blFixed, $sParams );
        } else {
            // using old
            $sSeoUrl = $sOldSeoUrl;
        }
        return $sSeoUrl;
    }

    protected function _getStaticObjectId( $iShopId, $sStdUrl )
    {
        return md5( strtolower ( $iShopId . $this->_trimUrl( $sStdUrl ) ) );
    }

    public function encodeStaticUrls( $aStaticUrl, $iShopId, $iLang )
    {
        $oDb = oxDb::getDb();
        $sValues = '';
        $sOldObjectId = null;

        // standard url
        $sStdUrl = $this->_trimUrl( trim( $aStaticUrl['oxseo__oxstdurl'] ) );
        $sObjectId = $aStaticUrl['oxseo__oxobjectid'];

        if ( !$sObjectId || $sObjectId == '-1' ) {
            $sObjectId = $this->_getStaticObjectId( $iShopId, $sStdUrl );
        } else {
            // marking entry as needs to move to history
            $sOldObjectId = $sObjectId;

            // if std url does not match old
            if ( $this->_getStaticObjectId( $iShopId, $sStdUrl ) != $sObjectId ) {
                $sObjectId = $this->_getStaticObjectId( $iShopId, $sStdUrl );
            }
        }

        foreach ( $aStaticUrl['oxseo__oxseourl'] as $iLang => $sSeoUrl ) {

            $iLang = (int) $iLang;

            // generating seo url
            $sSeoUrl = $this->_trimUrl( $sSeoUrl );
            if ( $sSeoUrl ) {
                $sSeoUrl = $this->_processSeoUrl( $sSeoUrl, $sObjectId, $iLang );
            }


            if ( $sOldObjectId ) {
                // move changed records to history
                if ( !$oDb->getOne( "select (" . $oDb->quote( $sSeoUrl ) . " like oxseourl) & (" . $oDb->quote( $sStdUrl ) . " like oxstdurl) from oxseo where oxobjectid = ".$oDb->quote( $sOldObjectId )." and oxshopid = '{$iShopId}' and oxlang = '{$iLang}' " ) ) {
                    $this->_copyToHistory( $sOldObjectId, $iShopId, $iLang, 'static', $sObjectId );
                }
            }

            if ( !$sSeoUrl || !$sStdUrl ) {
                continue;
            }

            $sIdent = $this->_getSeoIdent( $sSeoUrl );

            if ( $sValues ) {
                $sValues .= ', ';
            }

            $sValues .= "( " . $oDb->quote( $sObjectId ) . ", " . $oDb->quote( $sIdent ) . ", " . $oDb->quote( $iShopId ).", '{$iLang}', " . $oDb->quote( $sStdUrl ) . ", " . $oDb->quote( $sSeoUrl ) . ", 'static' )";
        }

        // must delete old before insert/update
        if ( $sOldObjectId ) {
            $oDb->execute( "delete from oxseo where oxobjectid in ( " . $oDb->quote( $sOldObjectId ) . ", " . $oDb->quote( $sObjectId ) . " )" );
        }

        // (re)inserting
        if ( $sValues ) {

            $sQ = "insert into oxseo ( oxobjectid, oxident, oxshopid, oxlang, oxstdurl, oxseourl, oxtype ) values {$sValues} ";
            $oDb->execute( $sQ );
        }

        return $sObjectId;
    }

    public function copyStaticUrls( $iShopId )
    {
        $iBaseShopId = $this->getConfig()->getBaseShopId();
        if ( $iShopId != $iBaseShopId ) {
            $oDb = oxDb::getDb();
            foreach (array_keys(oxLang::getInstance()->getLanguageIds()) as $iLang) {
                $sQ = "insert into oxseo ( oxobjectid, oxident, oxshopid, oxlang, oxstdurl, oxseourl, oxtype )
                       select MD5( LOWER( CONCAT( " . $oDb->quote( $iShopId ) . ", oxstdurl ) ) ), MD5( LOWER( oxseourl ) ),
                       " . $oDb->quote( $iShopId ) . ", oxlang, oxstdurl, oxseourl, oxtype from oxseo where oxshopid = '{$iBaseShopId}' and oxtype = 'static' and oxlang='$iLang' ";
                $oDb->execute( $sQ );
            }
        }
    }

    public function getStaticUrl( $sStdUrl, $iLang = null, $iShopId = null )
    {
        if (!isset($iShopId)) {
            $iShopId = $this->getConfig()->getShopId();
        }
        if (!isset($iLang)) {
            $iLang   = oxLang::getInstance()->getEditLanguage();
        }

        if ( isset($this->_aStaticUrlCache[$sStdUrl][$iLang][$iShopId])) {
            return $this->_aStaticUrlCache[$sStdUrl][$iLang][$iShopId];
        }

        $sFullUrl = '';
        if ( ( $sSeoUrl = $this->_getStaticUri( $sStdUrl, $iShopId, $iLang ) ) ) {
            $sFullUrl = $this->_getFullUrl( $sSeoUrl, $iLang, strpos( $sStdUrl, "https:" ) === 0 );
        }


        $this->_aStaticUrlCache[$sStdUrl][$iLang][$iShopId] = $sFullUrl;

        return $sFullUrl;
    }

    public function addSeoEntry( $sObjectId, $iShopId, $iLang, $sStdUrl, $sSeoUrl, $sType, $blFixed = 1, $sKeywords = '', $sDescription = '', $sParams = '', $blExclude = false, $sAltObjectId = null )
    {
        $sSeoUrl = $this->_processSeoUrl( $this->_trimUrl( $sSeoUrl ? $sSeoUrl : $this->_getAltUri( $sAltObjectId ? $sAltObjectId : $sObjectId, $iLang ) ), $sObjectId, $iLang, $blExclude );
        if ( $this->_saveToDb( $sType, $sObjectId, $sStdUrl, $sSeoUrl, $iLang, $iShopId, $blFixed, $sParams ) ) {

            $oDb = oxDb::getDb( true );

            //
            $sQtedObjectId = $oDb->quote( $sAltObjectId ? $sAltObjectId : $sObjectId );
            $iQtedShopId   = $oDb->quote( $iShopId );

            $oStr = getStr();
            if ( $sKeywords !== false ) {
                $sKeywords = $oDb->quote( $oStr->htmlspecialchars( $this->encodeString( strip_tags( $sKeywords ), false ) ) );
            }

            if ( $sDescription !== false ) {
                $sDescription = $oDb->quote( $oStr->htmlspecialchars( strip_tags( $sDescription ) ) );
            }

            $sQ = "insert into oxobject2seodata
                       ( oxobjectid, oxshopid, oxlang, oxkeywords, oxdescription )
                   values
                       ( {$sQtedObjectId}, {$iQtedShopId}, {$iLang}, ".( $sKeywords ? $sKeywords : "''" ).", ".( $sDescription ? $sDescription : "''" )." )
                   on duplicate key update
                       oxkeywords = ".( $sKeywords ? $sKeywords : "oxkeywords" ).", oxdescription = ".( $sDescription ? $sDescription : "oxdescription" );
            $oDb->execute( $sQ );
        }
    }

    protected function _getAltUri( $sObjectId, $iLang )
    {
    }

    public function deleteSeoEntry( $sObjectId, $iShopId, $iLang, $sType )
    {
        $oDb = oxDb::getDb();
        $sQ = "delete from oxseo where oxobjectid = " . $oDb->quote( $sObjectId ) . " and oxshopid = " . $oDb->quote( $iShopId ) . " and oxlang = " . $oDb->quote( $iLang ) . " and oxtype = " . $oDb->quote( $sType ) . " ";
        oxDb::getDb()->execute( $sQ );
    }

    public function getMetaData( $sObjectId, $sMetaType, $iShopId = null, $iLang = null )
    {
        $oDb = oxDb::getDb();

        $iShopId = ( !isset( $iShopId ) ) ? $this->getConfig()->getShopId():$iShopId;
        $iLang   = ( !isset( $iLang ) ) ? oxLang::getInstance()->getObjectTplLanguage():((int) $iLang);
        return $oDb->getOne( "select {$sMetaType} from oxobject2seodata where oxobjectid = " . $oDb->quote( $sObjectId ) . " and oxshopid = " . $oDb->quote( $iShopId )." and oxlang = '{$iLang}'" );
    }

    public function getDynamicUrl( $sStdUrl, $sSeoUrl, $iLang )
    {
        startProfile("getDynamicUrl");
        $sDynUrl = $this->_getFullUrl( $this->_getDynamicUri( $sStdUrl, $sSeoUrl, $iLang ), strpos( $sStdUrl, "https:" ) === 0 );
        stopProfile("getDynamicUrl");
        return $sDynUrl;
    }

    public function fetchSeoUrl( $sStdUrl, $iLanguage = null )
    {
        $oDb = oxDb::getDb( true );
        $iLanguage = isset( $iLanguage ) ? ( (int) $iLanguage ) : oxLang::getInstance()->getBaseLanguage();
        $sSeoUrl   = false;

        $sShopId = $this->getConfig()->getShopId();

        $sQ = "select oxseourl, oxlang from oxseo where oxstdurl = ".$oDb->quote( $sStdUrl )." and oxlang = '$iLanguage' and oxshopid = '$sShopId' limit 1";
        $oRs = $oDb->execute( $sQ );
        if ( !$oRs->EOF ) {
            $sSeoUrl = $oRs->fields['oxseourl'];
        }

        return $sSeoUrl;
    }
}