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();
}
public static function getUsersTop($userId, $departmentId, Type\DateTime $dateFrom, Type\DateTime $dateTo, $interval, $section = null, $nonInvolvedOnly = false, $from = 0, $limit = 100)
{
if (!in_array($interval, array('hour', 'day', 'month'), true)) {
throw new Main\ArgumentException('Interval should be the "hour", or "day", or "month".');
}
$data = array();
// rating for TOTAL activity or for an instrument
$sumField = $section === null ? 'TOTAL' : $section;
if ($interval === 'hour') {
$query = new Entity\Query(UserHourTable::getEntity());
$query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField)));
$query->setFilter(array('><HOUR' => array(ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'), ConvertTimeStamp($dateTo->getTimestamp(), 'FULL'))));
} else {
$query = new Entity\Query(UserDayTable::getEntity());
$query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField)));
$query->setFilter(array('><DAY' => array(ConvertTimeStamp($dateFrom->getTimestamp()), ConvertTimeStamp($dateTo->getTimestamp()))));
}
if ($sumField == 'TOTAL') {
// count number of used services
$names = UserHourTable::getSectionNames();
$fieldExpressions = array_fill(0, count($names), 'CASE WHEN SUM(%s) > 0 THEN 1 ELSE 0 END');
$serviceCountExpression = join(' + ', $fieldExpressions);
$query->addSelect(new Entity\ExpressionField('SERVICES_COUNT', $serviceCountExpression, $names));
if ($nonInvolvedOnly) {
// who didn't use 4 or more instruments
$query->addFilter('<SERVICES_COUNT', static::INVOLVEMENT_SERVICE_COUNT);
}
} else {
if ($nonInvolvedOnly) {
// who didn't use instrument
$query->addFilter('=SUM_' . $sumField, 0);
} else {
// who used it
$query->addFilter('>SUM_' . $sumField, 0);
}
}
$query->addOrder('SUM_' . $sumField, 'DESC');
if (!$nonInvolvedOnly) {
// we don't need this for non-involved users
$query->registerRuntimeField('MYSELF', array('data_type' => 'integer', 'expression' => array('CASE WHEN %s = ' . (int) $userId . ' THEN 1 ELSE 0 END', 'USER_ID')));
$query->addOrder('MYSELF', 'DESC');
}
$query->setOffset($from);
$query->setLimit($limit);
$result = $query->exec();
while ($row = $result->fetch()) {
$_data = array('USER_ID' => $row['USER_ID'], 'ACTIVITY' => $row['SUM_' . $sumField]);
if ($sumField == 'TOTAL') {
$_data['SERVICES_COUNT'] = $row['SERVICES_COUNT'];
$_data['IS_INVOLVED'] = $row['SERVICES_COUNT'] >= static::INVOLVEMENT_SERVICE_COUNT;
} else {
$_data['SERVICES_COUNT'] = null;
$_data['IS_INVOLVED'] = $row['SUM_' . $sumField] > 0;
}
$data[] = $_data;
}
return $data;
}
function compatibleNavQuery(Query $query, array $arNavStartParams)
{
$cnt = $query->exec()->getSelectedRowsCount();
// TODO check groups
global $DB;
if (isset($arNavStartParams["SubstitutionFunction"])) {
$arNavStartParams["SubstitutionFunction"]($this, $query->getLastQuery(), $cnt, $arNavStartParams);
return null;
}
if (isset($arNavStartParams["bDescPageNumbering"])) {
$bDescPageNumbering = $arNavStartParams["bDescPageNumbering"];
} else {
$bDescPageNumbering = false;
}
$this->InitNavStartVars($arNavStartParams);
$this->NavRecordCount = $cnt;
if ($this->NavShowAll) {
$this->NavPageSize = $this->NavRecordCount;
}
//calculate total pages depend on rows count. start with 1
$this->NavPageCount = $this->NavPageSize > 0 ? floor($this->NavRecordCount / $this->NavPageSize) : 0;
if ($bDescPageNumbering) {
$makeweight = 0;
if ($this->NavPageSize > 0) {
$makeweight = $this->NavRecordCount % $this->NavPageSize;
}
if ($this->NavPageCount == 0 && $makeweight > 0) {
$this->NavPageCount = 1;
}
//page number to display
$this->NavPageNomer = $this->PAGEN < 1 || $this->PAGEN > $this->NavPageCount ? $_SESSION[$this->SESS_PAGEN] < 1 || $_SESSION[$this->SESS_PAGEN] > $this->NavPageCount ? $this->NavPageCount : $_SESSION[$this->SESS_PAGEN] : $this->PAGEN;
//rows to skip
$NavFirstRecordShow = 0;
if ($this->NavPageNomer != $this->NavPageCount) {
$NavFirstRecordShow += $makeweight;
}
$NavFirstRecordShow += ($this->NavPageCount - $this->NavPageNomer) * $this->NavPageSize;
$NavLastRecordShow = $makeweight + ($this->NavPageCount - $this->NavPageNomer + 1) * $this->NavPageSize;
} else {
if ($this->NavPageSize > 0 && $this->NavRecordCount % $this->NavPageSize > 0) {
$this->NavPageCount++;
}
//calculate total pages depend on rows count. start with 1
if ($this->PAGEN >= 1 && $this->PAGEN <= $this->NavPageCount) {
$this->NavPageNomer = $this->PAGEN;
} elseif ($_SESSION[$this->SESS_PAGEN] >= 1 && $_SESSION[$this->SESS_PAGEN] <= $this->NavPageCount) {
$this->NavPageNomer = $_SESSION[$this->SESS_PAGEN];
} elseif ($arNavStartParams["checkOutOfRange"] !== true) {
$this->NavPageNomer = 1;
} else {
return null;
}
//rows to skip
$NavFirstRecordShow = $this->NavPageSize * ($this->NavPageNomer - 1);
$NavLastRecordShow = $this->NavPageSize * $this->NavPageNomer;
}
$NavAdditionalRecords = 0;
if (is_set($arNavStartParams, "iNavAddRecords")) {
$NavAdditionalRecords = $arNavStartParams["iNavAddRecords"];
}
if (!$this->NavShowAll) {
$query->setOffset($NavFirstRecordShow);
$query->setLimit($NavLastRecordShow - $NavFirstRecordShow + $NavAdditionalRecords);
}
$res_tmp = $query->exec();
//, $bIgnoreErrors);
// // Return false on sql errors (if $bIgnoreErrors == true)
// if ($bIgnoreErrors && ($res_tmp === false))
// return false;
// $this->result = $res_tmp->result;
$this->DB = DB;
if ($this->SqlTraceIndex) {
$start_time = microtime(true);
}
$temp_arrray = array();
$temp_arrray_add = array();
$tmp_cnt = 0;
while ($ar = $res_tmp->fetch()) {
$tmp_cnt++;
if (intval($NavLastRecordShow - $NavFirstRecordShow) > 0 && $tmp_cnt > $NavLastRecordShow - $NavFirstRecordShow) {
$temp_arrray_add[] = $ar;
} else {
$temp_arrray[] = $ar;
}
}
if ($this->SqlTraceIndex) {
/** @noinspection PhpUndefinedVariableInspection */
$exec_time = round(microtime(true) - $start_time, 10);
$DB->addDebugTime($this->SqlTraceIndex, $exec_time);
$DB->timeQuery += $exec_time;
}
$this->arResult = !empty($temp_arrray) ? $temp_arrray : false;
$this->arResultAdd = !empty($temp_arrray_add) ? $temp_arrray_add : false;
$this->nSelectedCount = $cnt;
$this->bDescPageNumbering = $bDescPageNumbering;
$this->bFromLimited = true;
return null;
}
/**
* @return Main\Entity\Query
*/
private function createQuery($offset = 0, $limit = 0)
{
if (!is_int($offset)) {
$offset = intval($offset);
}
if (!is_int($limit)) {
$limit = intval($limit);
}
$typeIDs = $this->getTypeIDs();
if (empty($typeIDs)) {
throw new Main\NotSupportedException("Criterion types are required.");
}
$query = new Main\Entity\Query(Entity\DuplicateIndexTable::getEntity());
$query->addSelect('ROOT_ENTITY_ID');
$query->addSelect('ROOT_ENTITY_NAME');
$query->addSelect('ROOT_ENTITY_TITLE');
$query->addSelect('QUANTITY');
$query->addSelect('TYPE_ID');
$query->addSelect('MATCHES');
$query->addSelect('IS_JUNK');
$permissionSql = '';
if ($this->enablePermissionCheck) {
$permissions = \CCrmPerms::GetUserPermissions($this->userID);
$permissionSql = \CCrmPerms::BuildSql(\CCrmOwnerType::ResolveName($this->entityTypeID), '', 'READ', array('RAW_QUERY' => true, 'PERMS' => $permissions));
if ($permissionSql === false) {
//Access denied;
return null;
}
}
$query->addFilter('=USER_ID', $this->userID);
$query->addFilter('=ENTITY_TYPE_ID', $this->entityTypeID);
$query->addFilter('@TYPE_ID', $typeIDs);
if ($this->enablePermissionCheck && $permissionSql !== '') {
$query->addFilter('@ROOT_ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
if ($offset > 0) {
$query->setOffset($offset);
}
if ($limit > 0) {
$query->setLimit($limit);
}
$enableSorting = $this->sortTypeID !== DuplicateIndexType::UNDEFINED;
if ($enableSorting) {
$order = $this->sortOrder === SORT_DESC ? 'DESC' : 'ASC';
if ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_EMAIL) {
$query->addOrder('ROOT_ENTITY_EMAIL_FLAG', $order);
$query->addOrder('ROOT_ENTITY_EMAIL', $order);
} elseif ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_PHONE) {
$query->addOrder('ROOT_ENTITY_PHONE_FLAG', $order);
$query->addOrder('ROOT_ENTITY_PHONE', $order);
} elseif ($this->sortTypeID === DuplicateIndexType::PERSON) {
$query->addOrder('ROOT_ENTITY_NAME_FLAG', $order);
$query->addOrder('ROOT_ENTITY_NAME', $order);
} elseif ($this->sortTypeID === DuplicateIndexType::ORGANIZATION) {
$query->addOrder('ROOT_ENTITY_TITLE_FLAG', $order);
$query->addOrder('ROOT_ENTITY_TITLE', $order);
}
}
return $query;
}
/**
* @return DedupeDataSourceResult
*/
public function getList($offset, $limit)
{
$result = new DedupeDataSourceResult();
$typeID = $this->typeID;
$entityTypeID = $this->getEntityTypeID();
$enablePermissionCheck = $this->isPermissionCheckEnabled();
//$userID = $this->getUserID();
$query = new Main\Entity\Query(Entity\DuplicateEntityMatchHashTable::getEntity());
$query->addSelect('MATCH_HASH');
$query->addGroup('MATCH_HASH');
$query->addOrder('MATCH_HASH', 'ASC');
$query->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)'));
$query->addSelect('QTY');
$query->addFilter('>QTY', 1);
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=TYPE_ID', $typeID);
$permissionSql = '';
if ($enablePermissionCheck) {
$permissionSql = $this->preparePermissionSql();
if ($permissionSql === false) {
//Access denied;
return $result;
}
if ($permissionSql !== '') {
$query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
}
if (!is_int($offset)) {
$offset = (int) $offset;
}
if ($offset > 0) {
$query->setOffset($offset);
}
if (!is_int($limit)) {
$limit = (int) $limit;
}
if ($limit > 0) {
$query->setLimit($limit);
}
$dbResult = $query->exec();
$processedItemCount = 0;
$lightHashes = array();
$heavyHashes = array();
while ($fields = $dbResult->fetch()) {
$processedItemCount++;
$quantity = isset($fields['QTY']) ? (int) $fields['QTY'] : 0;
$matchHash = isset($fields['MATCH_HASH']) ? $fields['MATCH_HASH'] : '';
if ($matchHash === '' || $quantity < 2) {
continue;
}
if ($quantity <= 100) {
$lightHashes[] = $matchHash;
} else {
$heavyHashes[] = $matchHash;
}
}
$result->setProcessedItemCount($processedItemCount);
$map = array();
if (!empty($heavyHashes)) {
foreach ($heavyHashes as $matchHash) {
$query = new Main\Entity\Query(Entity\DuplicateEntityMatchHashTable::getEntity());
$query->addSelect('ENTITY_ID');
$query->addSelect('IS_PRIMARY');
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=TYPE_ID', $typeID);
$query->addFilter('=MATCH_HASH', $matchHash);
if ($enablePermissionCheck && $permissionSql !== '') {
$query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
$query->setOffset(0);
$query->setLimit(100);
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$entityID = isset($fields['ENTITY_ID']) ? (int) $fields['ENTITY_ID'] : 0;
if ($entityID <= 0) {
continue;
}
if (!isset($map[$matchHash])) {
$map[$matchHash] = array();
}
$isPrimary = isset($fields['IS_PRIMARY']) && $fields['IS_PRIMARY'] === 'Y';
if ($isPrimary) {
if (!isset($map[$matchHash]['PRIMARY'])) {
$map[$matchHash]['PRIMARY'] = array();
}
$map[$matchHash]['PRIMARY'][] = $entityID;
} else {
if (!isset($map[$matchHash]['SECONDARY'])) {
$map[$matchHash]['SECONDARY'] = array();
}
$map[$matchHash]['SECONDARY'][] = $entityID;
}
}
}
}
if (!empty($lightHashes)) {
$query = new Main\Entity\Query(Entity\DuplicateEntityMatchHashTable::getEntity());
$query->addSelect('ENTITY_ID');
$query->addSelect('MATCH_HASH');
$query->addSelect('IS_PRIMARY');
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=TYPE_ID', $typeID);
$query->addFilter('@MATCH_HASH', $lightHashes);
if ($enablePermissionCheck && $permissionSql !== '') {
$query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$entityID = isset($fields['ENTITY_ID']) ? (int) $fields['ENTITY_ID'] : 0;
if ($entityID <= 0) {
continue;
}
$matchHash = isset($fields['MATCH_HASH']) ? $fields['MATCH_HASH'] : '';
if ($matchHash === '') {
continue;
}
if (!isset($map[$matchHash])) {
$map[$matchHash] = array();
}
$isPrimary = isset($fields['IS_PRIMARY']) && $fields['IS_PRIMARY'] === 'Y';
if ($isPrimary) {
if (!isset($map[$matchHash]['PRIMARY'])) {
$map[$matchHash]['PRIMARY'] = array();
}
$map[$matchHash]['PRIMARY'][] = $entityID;
} else {
if (!isset($map[$matchHash]['SECONDARY'])) {
$map[$matchHash]['SECONDARY'] = array();
}
$map[$matchHash]['SECONDARY'][] = $entityID;
}
}
}
$this->prepareResult($map, $result);
return $result;
}
// limit
$limit = array('nPageSize' => $arParams['ROWS_PER_PAGE'], 'iNumPage' => is_set($_GET['PAGEN_1']) ? $_GET['PAGEN_1'] : 1, 'bShowAll' => true);
// execute query
$main_query = new Entity\Query($entity);
$main_query->setSelect(array('*'));
$main_query->setOrder(array($sort_id => $sort_type));
//$main_query->setSelect($select)
// ->setFilter($filter)
// ->setGroup($group)
// ->setOrder($order)
// ->setOptions($options);
if (isset($limit['nPageTop'])) {
$main_query->setLimit($limit['nPageTop']);
} else {
$main_query->setLimit($limit['nPageSize']);
$main_query->setOffset(($limit['iNumPage'] - 1) * $limit['nPageSize']);
}
//$main_query->setLimit($limit['nPageSize']);
//$main_query->setOffset(($limit['iNumPage']-1) * $limit['nPageSize']);
$result = $main_query->exec();
$result = new CDBResult($result);
// build results
$rows = array();
$tableColumns = array();
while ($row = $result->Fetch()) {
foreach ($row as $k => $v) {
if ($k == 'ID') {
$tableColumns['ID'] = true;
continue;
}
$arUserField = $fields[$k];
