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);
}
}
