/**
* Creates delivery list filter SQL to load current state delivery list
*
* @param oxuser $oUser session user object
* @param string $sCountryId user country id
* @param string $sDelSet user chosen delivery set
*
* @return string
*/
protected function _getFilterSelect($oUser, $sCountryId, $sDelSet)
{
$oDb = oxDb::getDb();
$sTable = getViewName('oxdelivery');
$sQ = "select {$sTable}.* from ( select {$sTable}.* from {$sTable} left join oxdel2delset on oxdel2delset.oxdelid={$sTable}.oxid ";
$sQ .= "where " . $this->getBaseObject()->getSqlActiveSnippet() . " and oxdel2delset.oxdelsetid = " . $oDb->quote($sDelSet) . " ";
// 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');
$sCountrySql = $sCountryId ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxcountry' and oxobject2delivery.OXOBJECTID=" . $oDb->quote($sCountryId) . ")" : '0';
$sUserSql = $sUserId ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxuser' and oxobject2delivery.OXOBJECTID=" . $oDb->quote($sUserId) . ")" : '0';
$sGroupSql = count($aIds) ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxgroups' and oxobject2delivery.OXOBJECTID in (" . implode(', ', oxDb::getInstance()->quoteArray($aIds)) . ") )" : '0';
$sQ .= ") as {$sTable} where (\n select\n if(EXISTS(select 1 from oxobject2delivery, {$sCountryTable} where {$sCountryTable}.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid={$sTable}.OXID and oxobject2delivery.oxtype='oxcountry' 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='oxuser' 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='oxgroups' LIMIT 1),\n {$sGroupSql},\n 1)\n )";
$sQ .= " order by {$sTable}.oxsort ";
return $sQ;
}
