/** * Creates delivery set list filter SQL to load current state delivery set list * * @param oxUser $oUser user object * @param string $sCountryId user country id * * @return string */ protected function _getFilterSelect($oUser, $sCountryId) { $sTable = getViewName('oxdeliveryset'); $sQ = "select {$sTable}.* from {$sTable} "; $sQ .= "where " . $this->getBaseObject()->getSqlActiveSnippet() . ' '; // defining initial filter parameters $sUserId = null; $aGroupIds = null; // checking for current session user which gives additional restrictions for user itself, users group and country if ($oUser) { // user ID $sUserId = $oUser->getId(); // user groups ( maybe would be better to fetch by function oxuser::getUserGroups() ? ) $aGroupIds = $oUser->getUserGroups(); } $aIds = array(); if (count($aGroupIds)) { foreach ($aGroupIds as $oGroup) { $aIds[] = $oGroup->getId(); } } $sUserTable = getViewName('oxuser'); $sGroupTable = getViewName('oxgroups'); $sCountryTable = getViewName('oxcountry'); $oDb = oxDb::getDb(); $sCountrySql = $sCountryId ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxdelset' and oxobject2delivery.OXOBJECTID=" . $oDb->quote($sCountryId) . ")" : '0'; $sUserSql = $sUserId ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxdelsetu' and oxobject2delivery.OXOBJECTID=" . $oDb->quote($sUserId) . ")" : '0'; $sGroupSql = count($aIds) ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxdelsetg' and oxobject2delivery.OXOBJECTID in (" . implode(', ', oxDb::getDb()->quoteArray($aIds)) . ") )" : '0'; $sQ .= "and (\n select\n if(EXISTS(select 1 from oxobject2delivery, {$sCountryTable} where {$sCountryTable}.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxdelset' LIMIT 1),\n {$sCountrySql},\n 1) &&\n if(EXISTS(select 1 from oxobject2delivery, {$sUserTable} where {$sUserTable}.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxdelsetu' LIMIT 1),\n {$sUserSql},\n 1) &&\n if(EXISTS(select 1 from oxobject2delivery, {$sGroupTable} where {$sGroupTable}.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxdelsetg' LIMIT 1),\n {$sGroupSql},\n 1)\n )"; //order by $sQ .= " order by {$sTable}.oxpos"; return $sQ; }