public static function getList($parameters = array()) { $query = new Query(static::getEntity()); if (!isset($parameters['select'])) { $query->setSelect(array('*')); } foreach ($parameters as $param => $value) { switch ($param) { case 'select': $query->setSelect($value); break; case 'filter': $query->setFilter($value); break; case 'group': $query->setGroup($value); break; case 'order': $query->setOrder($value); break; case 'limit': $query->setLimit($value); break; case 'offset': $query->setOffset($value); break; case 'count_total': $query->countTotal($value); break; case 'options': $query->setOptions($value); break; case 'runtime': foreach ($value as $name => $fieldInfo) { $query->registerRuntimeField($name, $fieldInfo); } break; case 'data_doubling': if ($value) { $query->enableDataDoubling(); } else { $query->disableDataDoubling(); } break; default: throw new Main\ArgumentException("Unknown parameter: " . $param, $param); } } return $query->exec(); }
/** * @param Type\DateTime $dateFrom * @param Type\DateTime $dateTo * @param $interval * * @return array|bool * @throws \Bitrix\Main\ArgumentException */ public static function getMaxUserActivity(Type\DateTime $dateFrom, Type\DateTime $dateTo, $interval) { if (!in_array($interval, array('hour', 'day', 'month'), true)) { throw new Main\ArgumentException('Interval should be the "hour", or "day", or "month".'); } // first, get sum by user if ($interval === 'hour') { $subQuery = new Entity\Query(UserHourTable::getEntity()); $subQuery->setFilter(array('><HOUR' => array(ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'), ConvertTimeStamp($dateTo->getTimestamp(), 'FULL')))); } else { $subQuery = new Entity\Query(UserDayTable::getEntity()); $subQuery->setFilter(array('><DAY' => array(ConvertTimeStamp($dateFrom->getTimestamp()), ConvertTimeStamp($dateTo->getTimestamp())))); } $subQuery->addSelect(new Entity\ExpressionField('TOTAL_SUM', 'SUM(%s)', 'TOTAL')); foreach (UserHourTable::getSectionNames() as $sectionName) { $subQuery->addSelect(new Entity\ExpressionField($sectionName . '_SUM', 'SUM(%s)', $sectionName)); } $subQuery->setGroup('USER_ID'); // then get max values $query = new Entity\Query($subQuery); $query->addSelect(new Entity\ExpressionField('TOTAL', 'MAX(%s)', 'TOTAL_SUM')); foreach (UserHourTable::getSectionNames() as $sectionName) { $query->addSelect(new Entity\ExpressionField($sectionName, 'MAX(%s)', $sectionName . '_SUM')); } $result = $query->exec(); $data = $result->fetch(); return $data; }
public static function GetRegionsIdsByNames($arRegNames, $countryId = false) { if (self::isLocationProMigrated()) { try { $types = self::getTypes(); $query = new Entity\Query(self::SELF_ENTITY_NAME); $fieldMap = array('RID' => 'REGION_ID', 'RNAME' => 'NAME.NAME', 'RSHORT_NAME' => 'NAME.SHORT_NAME'); $selectFields = $fieldMap; $filterFields = array(array('LOGIC' => 'OR', 'RNAME' => $arRegNames, 'RSHORT_NAME' => $arRegNames), '=TYPE_ID' => $types['REGION'], '!=REGION_ID' => '0'); if ($countryId = intval($countryId)) { $filterFields['=COUNTRY_ID'] = $countryId; } // order $orderFields = array('RNAME' => 'asc', 'RSHORT_NAME' => 'asc'); // group $groupFields = array('RID'); $nameJoinCondition = array('=this.ID' => 'ref.LOCATION_ID'); if (strlen($strLang)) { $nameJoinCondition['=ref.LANGUAGE_ID'] = array('?', $strLang); } $query->registerRuntimeField('NAME', array('data_type' => self::NAME_ENTITY_NAME, 'reference' => $nameJoinCondition, 'join_type' => 'left')); $query->setSelect($selectFields); $query->setFilter($filterFields); $query->setOrder($orderFields); $query->setGroup($groupFields); $result = array(); $res = $query->exec(); while ($item = $res->fetch()) { $result[strlen($item['RNAME']) ? $item['RNAME'] : $item['RSHORT_NAME']] = $item['RID']; } return $result; } catch (Exception $e) { return array(); } } else { global $DB; $arResult = array(); $arWhere = array(); $arQueryFields = array('RL.NAME', 'RL.SHORT_NAME'); if (is_array($arRegNames)) { foreach ($arRegNames as $regName) { $regName = $DB->ForSql($regName); foreach ($arQueryFields as $field) { $arWhere[] = $field . " LIKE '" . $regName . "'"; } } if (count($arWhere) > 0) { $strWhere = implode(' OR ', $arWhere); $query = "\tSELECT RL.REGION_ID, RL.NAME, RL.SHORT_NAME\n\t\t\t\t\t\t\t\tFROM b_sale_location_region_lang RL "; if ($countryId) { $strWhere = 'L.COUNTRY_ID=\'' . intval($countryId) . '\' AND (' . $strWhere . ')'; $query .= "LEFT JOIN b_sale_location L ON L.REGION_ID=RL.REGION_ID "; } $query .= "WHERE " . $strWhere; $query .= " GROUP BY RL.REGION_ID"; $query .= " ORDER BY RL.NAME, RL.SHORT_NAME"; $dbList = $DB->Query($query); $arRegionsLang = array(); while ($arRegion = $dbList->Fetch()) { if (strlen($arRegion["NAME"]) > 0) { $idx = $arRegion["NAME"]; } else { $idx = $arRegion["SHORT_NAME"]; } $arResult[$idx] = $arRegion["REGION_ID"]; } } } return $arResult; } }
if (!array_key_exists($k, $totalColumns)) { unset($totalSelect[$k]); } } // add SUM aggr $_totalSelect = $totalSelect; $totalSelect = array(); foreach ($_totalSelect as $k => $v) { $totalSelect[] = new Entity\ExpressionField('TOTAL_' . $k, 'SUM(%s)', $k); } if (!empty($totalSelect)) { // source query $query_from = new Entity\Query($entity); $query_from->setSelect($select); $query_from->setFilter($filter); $query_from->setGroup($group); foreach ($runtime as $k => $v) { $query_from->registerRuntimeField($k, $v); } // total query $total_query = new Entity\Query($query_from); $total_query->setSelect($totalSelect); $result = $total_query->exec(); $total = $result->fetch(); $total = $total === false ? array() : $total; } else { $total = array(); } // </editor-fold> // <editor-fold defaultstate="collapsed" desc="group_concat fields"> $grcData = array();
public static function getConnectedEntitiesQuery($locationPrimary, $linkType = 'id', $parameters = array()) { if ($linkType == 'id') { $locationPrimary = Assert::expectIntegerPositive($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } else { $locationPrimary = Assert::expectStringNotNull($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } $useGroups = GroupTable::checkGroupUsage() && static::getUseGroups(); // check if we have groups in project and entity uses groups $useCodes = static::getUseCodes(); // this entity uses codes $groupUseCodes = GroupLocationTable::getUseCodes(); // group entity uses codes $typeFld = static::getTypeField(); /*LOCATION_TYPE*/ $linkFld = static::getLinkField(); /*DELIVERY_ID*/ $locationLinkFld = static::getLocationLinkField(); /*LOCATION_ID*/ $targetPrimaryFld = static::getTargetEntityPrimaryField(); /*ID*/ $groupLocationLinkFld = GroupLocationTable::getLocationLinkField(); /*LOCATION_ID*/ $groupLinkFld = GroupLocationTable::getLinkField(); /*LOCATION_GROUP_ID*/ $seachById = $linkType == 'id'; $dbConnection = Main\HttpApplication::getConnection(); if (!is_array($parameters)) { $parameters = array(); } if (is_array($parameters['runtime'])) { Assert::announceNotImplemented('Sorry, runtime clause is not implemented currently.'); } $order = array(); if (is_array($parameters['order'])) { Assert::announceNotImplemented('Sorry, order-over-union clause is not implemented currently.'); } $filter = array(); if (is_array($parameters['filter']) && !empty($parameters['filter'])) { $filter = $parameters['filter']; } $select = array('*'); if (is_array($parameters['select']) && !empty($parameters['select'])) { $select = $parameters['select']; } /* query example when working with delivery: select distinct D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'L' inner join b_sale_location L1 on L1.CODE = DL.LOCATION_ID inner join b_sale_location L2 on L2.ID(there will be CODE, if we search by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query = new Entity\Query(static::getTargetEntityName()); $DLCondition = array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld); if ($useGroups) { $DLCondition['=ref.' . $typeFld] = array('?', self::DB_LOCATION_FLAG); } $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => $DLCondition, 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.' . ($useCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); if (!$useGroups) { // emulate "select distinct" $query->setGroup($select); return $query->getQuery(); } else { $sqls = array($query->getQuery()); $query = new Entity\Query(static::getTargetEntityName()); /* query example when working with delivery: select D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'G' inner join b_sale_location_group G on G.CODE = DL.LOCATION_ID (if this entity uses ID, skip this join) inner join b_sale_grouplocation GL on GL.LOCATION_GROUP_ID = G.ID (if this entity uses ID, there will be DL.LOCATION_ID) inner join b_sale_location L1 on L1.ID (there will be CODE, if grouplocation entity uses CODE) = GL.LOCATION_ID inner join b_sale_location L2 on L2.ID (there will be CODE, if we seach by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld, '=ref.' . $typeFld => array('?', self::DB_GROUP_FLAG)), 'join_type' => 'inner')); if ($useCodes) { $query->registerRuntimeField('G', array('data_type' => '\\Bitrix\\Sale\\Location\\Group', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.CODE'), 'join_type' => 'inner')); } $query->registerRuntimeField('GL', array('data_type' => '\\Bitrix\\Sale\\Location\\GroupLocation', 'reference' => array($useCodes ? '=this.G.ID' : '=this.DL.' . $locationLinkFld => 'ref.' . $groupLinkFld), 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.GL.' . $groupLocationLinkFld => 'ref.' . ($groupUseCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); $sqls[] = $query->getQuery(); return static::unionize($sqls); } }