private static function loadBulk($entityTypeID, array &$entityIDs, array &$itemMap, array $options = null)
{
/** @var DuplicateEntityRanking[] $itemMap */
if ($entityTypeID !== \CCrmOwnerType::Contact && $entityTypeID !== \CCrmOwnerType::Company && $entityTypeID !== \CCrmOwnerType::Lead) {
return;
}
if (!is_array($options)) {
$options = array();
}
$checkPermissions = isset($options['CHECK_PERMISSIONS']) ? (bool) $options['CHECK_PERMISSIONS'] : false;
$userID = isset($options['USER_ID']) ? (int) $options['USER_ID'] : 0;
$permissions = $checkPermissions ? \CCrmPerms::GetUserPermissions($userID) : null;
$limit = isset($options['LIMIT']) ? (int) $options['LIMIT'] : 3000;
if ($limit <= 0) {
$limit = 3000;
}
$length = count($entityIDs);
if ($length === 0) {
return;
}
while ($length > 0) {
if ($length <= $limit) {
$ids = $entityIDs;
unset($entityIDs);
$entityIDs = array();
} else {
$ids = array_splice($entityIDs, 0, $limit);
}
$length = count($entityIDs);
if (empty($ids)) {
continue;
}
if ($entityTypeID === \CCrmOwnerType::Lead) {
$dbResult = Entity\DuplicateEntityStatisticsTable::getList(array('select' => array('ENTITY_ID', 'RANKING_DATA'), 'filter' => array('ENTITY_TYPE_ID' => \CCrmOwnerType::Lead, 'ENTITY_ID' => $ids)));
while ($fields = $dbResult->fetch()) {
$entityID = intval($fields['ENTITY_ID']);
$key = "{$entityTypeID}_{$entityID}";
if (!isset($itemMap[$key])) {
continue;
}
if (isset($fields['RANKING_DATA']) && $fields['RANKING_DATA'] !== '') {
$data = unserialize($fields['RANKING_DATA']);
/** @var DuplicateEntityRanking $ranking */
$ranking = $itemMap[$key];
$ranking->lastChanged = isset($data['LAST_CHANGED']) ? $data['LAST_CHANGED'] : 0;
$ranking->completeness = isset($data['COMPLETENESS']) ? $data['COMPLETENESS'] : 0;
if ($checkPermissions) {
$ranking->editable = \CCrmLead::CheckUpdatePermission($entityID, $permissions);
$ranking->deleteable = \CCrmLead::CheckDeletePermission($entityID, $permissions);
}
}
}
} else {
$query = new Main\Entity\Query(Entity\DuplicateEntityStatisticsTable::getEntity());
$query->addSelect('ENTITY_ID');
$query->addSelect('RANKING_DATA');
$query->addFilter('ENTITY_ID', $ids);
$query->addFilter('ENTITY_TYPE_ID', $entityTypeID);
if ($entityTypeID === \CCrmOwnerType::Contact) {
$subQuery = new Main\Entity\Query(DealTable::getEntity());
$subQuery->addSelect('CONTACT_ID');
$subQuery->addFilter('CONTACT_ID', $ids);
$subQuery->addSelect('QTY');
$subQuery->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)'));
$referenceField = new Main\Entity\ReferenceField('D', Main\Entity\Base::getInstanceByQuery($subQuery), array('=this.ENTITY_ID' => 'ref.CONTACT_ID'), array('join_type' => 'LEFT'));
} else {
$subQuery = new Main\Entity\Query(DealTable::getEntity());
$subQuery->addSelect('COMPANY_ID');
$subQuery->addFilter('COMPANY_ID', $ids);
$subQuery->addSelect('QTY');
$subQuery->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)'));
$referenceField = new Main\Entity\ReferenceField('D', Main\Entity\Base::getInstanceByQuery($subQuery), array('=this.ENTITY_ID' => 'ref.COMPANY_ID'), array('join_type' => 'LEFT'));
}
$query->registerRuntimeField('', $referenceField);
$query->addSelect('D.QTY', 'QTY');
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$entityID = intval($fields['ENTITY_ID']);
$key = "{$entityTypeID}_{$entityID}";
if (!isset($itemMap[$key])) {
continue;
}
$itemMap[$key]->referenceCount = isset($fields['QTY']) ? intval($fields['QTY']) : 0;
if (isset($fields['RANKING_DATA']) && $fields['RANKING_DATA'] !== '') {
$data = unserialize($fields['RANKING_DATA']);
/** @var DuplicateEntityRanking $ranking */
$ranking = $itemMap[$key];
$ranking->lastChanged = isset($data['LAST_CHANGED']) ? $data['LAST_CHANGED'] : 0;
$ranking->completeness = isset($data['COMPLETENESS']) ? $data['COMPLETENESS'] : 0;
if ($checkPermissions) {
if ($entityTypeID === \CCrmOwnerType::Contact) {
$ranking->editable = \CCrmContact::CheckUpdatePermission($entityID, $permissions);
$ranking->deleteable = \CCrmContact::CheckDeletePermission($entityID, $permissions);
} else {
$ranking->editable = \CCrmCompany::CheckUpdatePermission($entityID, $permissions);
$ranking->deleteable = \CCrmCompany::CheckDeletePermission($entityID, $permissions);
}
}
}
}
}
}
}
public function getEntityByPrimary(\Bitrix\Main\Entity\Base $entity, $primary, $select)
{
$this->connectInternal();
$table = $entity->getDBTableName();
$sqlConfiguration = $entity->getConnection()->getConfiguration();
$primary = (array) $primary;
if (count($primary) > 1) {
throw new \Exception('HSPHP Read Socket doesn\'t support multiple select');
}
$indexId = $this->resource->getIndexId($sqlConfiguration['database'], $table, '', join(',', (array) $select));
$this->resource->select($indexId, '=', $primary);
$response = $this->resource->readResponse();
//foreach
$result = array();
if (is_array($response)) {
foreach ($response as $row) {
$newRow = array();
foreach ($row as $k => $v) {
$newRow[$select[$k]] = $v;
}
$result[] = $newRow;
}
}
return $result;
}
protected function appendSaveDataModifier($modifier)
{
if (!is_callable($modifier)) {
throw new SystemException(sprintf('Save modifier of "%s" field of "%s" entity should be a callback', $this->name, $this->entity->getDataClass()));
}
$this->saveDataModifiers[] = $modifier;
}
/**
* @return \Bitrix\Main\Entity\Base
*/
public function getRefEntity()
{
if ($this->refEntity === null) {
$this->refEntity = Base::getInstance($this->refEntityName);
}
return $this->refEntity;
}
public static function unsetEntity($class)
{
$class = Base::normalizeEntityClass($class);
if (isset(static::$entity[$class])) {
unset(static::$entity[$class]);
return true;
}
}
/**
* @static
* @return Base
*/
public static function getEntity()
{
$class = get_called_class();
if (!isset(static::$entity[$class])) {
static::$entity[$class] = Base::getInstance($class);
}
return static::$entity[$class];
}
function UnInstallDB()
{
Loader::includeModule($this->MODULE_ID);
// Drop PersonTable
Application::getConnection(\Adelshin\Person\PersonTable::getConnectionName())->queryExecute('drop table if exists ' . Base::getInstance('\\Adelshin\\Person\\PersonTable')->getDBTableName());
// Drop GroupTable
Application::getConnection(\Adelshin\Person\GroupsTable::getConnectionName())->queryExecute('drop table if exists ' . Base::getInstance('\\Adelshin\\Person\\GroupsTable')->getDBTableName());
Option::delete($this->MODULE_ID);
}
/**
* Вызов методов \Bitrix\Main\Entity\DataManager
* @see \Bitrix\Main\Entity\DataManager
* @param $method
* @param $arguments
* @return mixed
* @throws Exception
*/
public function __call($method, $arguments)
{
$allowedMethods = array('getList', 'add', 'update', 'getRow', 'getById', 'delete');
$allowedMethods = array_map('ToLower', $allowedMethods);
if (!in_array(ToLower($method), $allowedMethods)) {
throw new \Exception("Unsupported method {$method}");
}
/** @var $className \Bitrix\Main\Entity\DataManager */
$className = $this->hblockEntity->getDataClass();
return call_user_func_array("{$className}::{$method}", $arguments);
}
/**
* @param $iblockId
* @return Entity\Base
* @throws ArgumentException
*/
public static function compileEntity($iblockId)
{
$iblock = IblockStructure::iblock($iblockId);
if (!$iblock) {
throw new ArgumentException('Указан несуществующий идентификатор инфоблока');
}
$entityName = "Iblock" . Entity\Base::snake2camel($iblockId) . "SectionTable";
$fullEntityName = '\\' . __NAMESPACE__ . '\\' . $entityName;
$code = "\n namespace " . __NAMESPACE__ . ";\n class {$entityName} extends SectionTable {\n public static function getIblockId(){\n return {$iblock['ID']};\n }\n public static function getUfId(){\n return 'IBLOCK_{$iblock['ID']}_SECTION';\n }\n }\n ";
if (!class_exists($fullEntityName)) {
eval($code);
}
return Entity\Base::getInstance($fullEntityName);
}
public static function getLatest($ownerID)
{
if (!is_int($ownerID)) {
$ownerID = (int) $ownerID;
}
if ($ownerID <= 0) {
throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID');
}
$subQuery = new Query(DealStageHistoryTable::getEntity());
$subQuery->registerRuntimeField('', new ExpressionField('MAX_ID', 'MAX(ID)'));
$subQuery->addSelect('MAX_ID');
$subQuery->addFilter('=OWNER_ID', $ownerID);
$query = new Query(DealStageHistoryTable::getEntity());
$query->addSelect('*');
$query->registerRuntimeField('', new ReferenceField('M', Base::getInstanceByQuery($subQuery), array('=this.ID' => 'ref.MAX_ID'), array('join_type' => 'INNER')));
$dbResult = $query->exec();
$result = $dbResult->fetch();
return is_array($result) ? $result : null;
}
/**
* @return callback[]|Validator\Base[]
* @throws \Exception
*/
public function getValidators()
{
if ($this->validators === null) {
$validators = array();
if ($this->validation !== null) {
$validators = call_user_func($this->validation);
if (!is_array($validators)) {
throw new \Exception(sprintf('Validation for %s field of %s entity should return array of validators', $this->name, $this->entity->getDataClass()));
}
foreach ($validators as $k => $validator) {
if (!$validator instanceof Validator\Base && !is_callable($validator)) {
throw new \Exception(sprintf('Validator "%s" of "%s" field of "%s" entity should be a Validator\\Base or callback', $k, $this->name, $this->entity->getDataClass()));
}
}
}
$this->validators = $validators;
}
return $this->validators;
}
/**
* @return array
*/
public static function prepareTimeline($ownerID)
{
if (!is_int($ownerID)) {
$ownerID = (int) $ownerID;
}
if ($ownerID <= 0) {
throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID');
}
$query = new Query(Crm\ActivityTable::getEntity());
$query->addFilter('=COMPLETED', 'Y');
$connection = Main\Application::getConnection();
if ($connection instanceof Main\DB\MysqlCommonConnection) {
$query->registerRuntimeField('', new ExpressionField('DEADLINE_DATE', 'DATE(DEADLINE)'));
} elseif ($connection instanceof Main\DB\MssqlConnection) {
$query->registerRuntimeField('', new ExpressionField('DEADLINE_DATE', 'CAST(FLOOR(CAST(DEADLINE AS FLOAT)) AS DATETIME)'));
} elseif ($connection instanceof Main\DB\OracleConnection) {
$query->registerRuntimeField('', new ExpressionField('DEADLINE_DATE', 'TRUNC(DEADLINE)'));
}
$query->addSelect('DEADLINE_DATE');
$query->addGroup('DEADLINE_DATE');
$subQuery = new Query(Crm\ActivityBindingTable::getEntity());
$subQuery->addSelect('ACTIVITY_ID');
$subQuery->addFilter('=OWNER_TYPE_ID', \CCrmOwnerType::Deal);
$subQuery->addFilter('=OWNER_ID', $ownerID);
$query->registerRuntimeField('', new ReferenceField('B', Base::getInstanceByQuery($subQuery), array('=this.ID' => 'ref.ACTIVITY_ID'), array('join_type' => 'INNER')));
$dbResult = $query->exec();
$dates = array();
while ($fieilds = $dbResult->fetch()) {
$dates[] = $fieilds['DEADLINE_DATE'];
}
return $dates;
}
protected static function getUtmEntityTableNameBySrcEntity(Entity\Base $srcEntity)
{
return 'b_utm_' . strtolower($srcEntity->getUfId());
}
}
if ($columnInfo["length"] > 0 && $columnInfo["orm_type"] == "string") {
$validateFunctionName = "validate" . \Bitrix\Main\Entity\Base::snake2camel($columnName);
echo "\t\t\t\t'validation' => array(_" . "_CLASS_" . "_, '" . $validateFunctionName . "'),\n";
$arValidators[$validateFunctionName] = array("length" => $columnInfo["length"], "field" => $columnName);
}
$messageId = strtoupper(implode("_", $tableParts) . "_ENTITY_" . $columnName . "_FIELD");
$arMessages[$messageId] = "";
echo "\t\t\t\t'title' => Loc::getMessage('" . $messageId . "'),\n";
echo "\t\t\t),\n";
}
foreach ($arParents as $columnName => $parentInfo) {
$parentTableParts = explode("_", $parentInfo["PARENT_TABLE"]);
array_shift($parentTableParts);
$parentModuleNamespace = ucfirst($parentTableParts[0]);
$parentClassName = \Bitrix\Main\Entity\Base::snake2camel(implode("_", $parentTableParts));
$columnNameEx = preg_replace("/_ID\$/", "", $columnName);
echo "\t\t\t'" . $columnNameEx . "' => array(\n";
echo "\t\t\t\t'data_type' => 'Bitrix\\" . $parentModuleNamespace . "\\" . $parentClassName . "',\n";
echo "\t\t\t\t'reference' => array('=this." . $columnName . "' => 'ref." . $parentInfo["PARENT_COLUMN"] . "'),\n";
echo "\t\t\t),\n";
}
echo "\t\t);\n";
echo "\t}\n";
foreach ($arValidators as $validateFunctionName => $validator) {
echo "\t/**\n";
echo "\t * Returns validators for " . $validator["field"] . " field.\n";
echo "\t *\n";
echo "\t * @return array\n";
echo "\t */\n";
echo "\tpublic static function " . $validateFunctionName . "()\n";
public static function beforeViewDataQuery(&$select, &$filter, &$group, &$order, &$limit, &$options, &$runtime)
{
parent::beforeViewDataQuery($select, $filter, $group, $order, $limit, $options, $runtime);
global $USER, $DB, $DBType;
$permFilter = array('LOGIC' => 'OR');
// owner permission
if (isset($_GET['select_my_tasks']) || !isset($_GET['select_my_tasks']) && !isset($_GET['select_depts_tasks']) && !isset($_GET['select_group_tasks'])) {
$runtime['IS_TASK_COWORKER'] = array('data_type' => 'integer', 'expression' => array("(CASE WHEN EXISTS(" . "SELECT 'x' FROM b_tasks_member TM " . "WHERE TM.TASK_ID = " . $DB->escL . (ToUpper($DBType) === "ORACLE" ? "TASKS_TASK" : "tasks_task") . $DB->escR . ".ID AND TM.USER_ID = " . $USER->GetID() . " AND TM.TYPE = 'A'" . ") THEN 1 ELSE 0 END)"));
$permFilter[] = array('LOGIC' => 'OR', '=RESPONSIBLE_ID' => $USER->GetID(), '=IS_TASK_COWORKER' => 1);
}
// own departments permission
if (isset($_GET['select_depts_tasks'])) {
$permFilterDepts = array('LOGIC' => 'OR', '=CREATED_BY' => $USER->GetID());
$deptsPermSql = CTasks::GetSubordinateSql('__ULTRAUNIQUEPREFIX__');
if (strlen($deptsPermSql)) {
$deptsPermSql = "EXISTS(" . $deptsPermSql . ")";
$deptsPermSql = str_replace('__ULTRAUNIQUEPREFIX__T.', $DB->escL . (ToUpper($DBType) === "ORACLE" ? "TASKS_TASK" : "tasks_task") . $DB->escR . '.', $deptsPermSql);
$deptsPermSql = str_replace('__ULTRAUNIQUEPREFIX__', '', $deptsPermSql);
$runtime['IS_SUBORDINATED_TASK'] = array('data_type' => 'integer', 'expression' => array("(CASE WHEN " . $deptsPermSql . " THEN 1 ELSE 0 END)"));
$permFilterDepts[] = array('!RESPONSIBLE_ID' => $USER->GetID(), '=IS_SUBORDINATED_TASK' => 1);
}
$permFilter[] = $permFilterDepts;
}
// group permission
if (isset($_GET['select_group_tasks'])) {
$allowedGroups = CTasks::GetAllowedGroups();
$permFilter[] = array('=GROUP_ID' => $allowedGroups);
}
// re-aggregate aggregated subquery in DURATION for mssql
if (\Bitrix\Main\Application::getConnection() instanceof \Bitrix\Main\DB\MssqlConnection) {
foreach ($select as $k => $v) {
if (substr($k, -9) == '_DURATION') {
// we have aggregated duration
$subQuery = new \Bitrix\Main\Entity\Query(\Bitrix\Tasks\ElapsedTimeTable::getEntity());
$subQuery->addSelect('TASK_ID');
$subQuery->addSelect(new \Bitrix\Main\Entity\ExpressionField('DURATION', 'ROUND(SUM(%s)/60, 0)', 'SECONDS'));
$subEntity = \Bitrix\Main\Entity\Base::getInstanceByQuery($subQuery);
// make reference
$subReferenceName = $k . '_REF';
$runtime[$subReferenceName] = array('data_type' => $subEntity, 'reference' => array('=this.ID' => 'ref.TASK_ID'));
// rewrite aggregated duration (put it in the end, after refence)
$runtimeField = $runtime[$k];
unset($runtime[$k]);
$runtimeField['expression'][1] = $subReferenceName . '.DURATION';
$runtime[$k] = $runtimeField;
} else {
if (substr($k, -20) == '_DURATION_FOR_PERIOD' && isset($options['SQL_TIME_INTERVAL'])) {
// we have aggregated DURATION_FOR_PERIOD field
$subQuery = new \Bitrix\Main\Entity\Query(\Bitrix\Tasks\ElapsedTimeTable::getEntity());
$subQuery->addSelect('TASK_ID');
$subQuery->addSelect(new \Bitrix\Main\Entity\ExpressionField('DURATION_FOR_PERIOD', 'ROUND((SUM(CASE WHEN CREATED_DATE ' . $options['SQL_TIME_INTERVAL'] . ' THEN %s ELSE 0 END)/60),0)', 'SECONDS'));
$subEntity = \Bitrix\Main\Entity\Base::getInstanceByQuery($subQuery);
// make reference
$subReferenceName = $k . '_REF';
$runtime[$subReferenceName] = array('data_type' => $subEntity, 'reference' => array('=this.ID' => 'ref.TASK_ID'));
// rewrite aggregated duration (put it in the end, after refence)
$runtimeField = $runtime[$k];
unset($runtime[$k]);
$runtimeField['expression'][1] = $subReferenceName . '.DURATION_FOR_PERIOD';
$runtime[$k] = $runtimeField;
}
}
}
}
// concat permissions with common filter
$filter[] = $permFilter;
}
public static function compileEntity($hlblock)
{
// generate entity & data manager
$fieldsMap = array();
// add ID
$fieldsMap['ID'] = array('data_type' => 'integer', 'primary' => true, 'autocomplete' => true);
// add other fields
$fields = $GLOBALS['USER_FIELD_MANAGER']->getUserFields('HLBLOCK_' . $hlblock['ID']);
foreach ($fields as $field) {
$fieldsMap[$field['FIELD_NAME']] = array('data_type' => \Bitrix\Main\Entity\UField::convertBaseTypeToDataType($field['USER_TYPE']['BASE_TYPE']));
}
// build classes
$entity_name = $hlblock['NAME'];
$entity_data_class = $hlblock['NAME'];
if (!class_exists($entity_data_class . 'Table')) {
if (!preg_match('/^[a-z0-9_]+$/i', $entity_data_class)) {
throw new \Exception(sprintf('Invalid entity name `%s`.', $entity_data_class));
}
$entity_table_name = $hlblock['TABLE_NAME'];
$eval = '
class ' . $entity_data_class . 'Table extends ' . __NAMESPACE__ . '\\DataManager
{
public static function getFilePath()
{
return __FILE__;
}
public static function getTableName()
{
return ' . var_export($entity_table_name, true) . ';
}
public static function getMap()
{
return ' . var_export($fieldsMap, true) . ';
}
}
';
eval($eval);
}
return \Bitrix\Main\Entity\Base::getInstance($entity_name);
}
public static function appendTextUserFieldsAsTrimmed(\Bitrix\Main\Entity\Base $entity)
{
/** @global string $DBType */
global $DBType;
$dbType = ToUpper(strval($DBType));
// Advanced fields for text user fields
$textFields = array();
foreach ($entity->getFields() as $field) {
if ($field instanceof Bitrix\Main\Entity\ExpressionField) {
$arUF = self::detectUserField($field);
if ($arUF['isUF']) {
if ($arUF['ufInfo']['MULTIPLE'] === 'Y') {
if ($dbType === 'ORACLE' || $dbType === 'MSSQL') {
$exprVal = '';
switch ($dbType) {
case 'ORACLE':
$maxStrLen = 4000;
$exprVal = 'TO_CHAR(SUBSTR(%s, 1, ' . $maxStrLen . '))';
break;
case 'MSSQL':
$maxStrLen = 8000;
$exprVal = 'SUBSTRING(%s, 1, ' . $maxStrLen . ')';
break;
}
/*$textFields[] = array(
'def' => array(
'data_type' => 'string',
'expression' => array(
$exprVal, $arUF['ufInfo']['FIELD_NAME']
)
),
'name' => $arUF['ufInfo']['FIELD_NAME'].self::UF_TEXT_TRIM_POSTFIX
);*/
if ($arUF['ufInfo']['USER_TYPE_ID'] === 'datetime') {
$fdmsGetterName = 'getFDMsMultipleTrimmedDateTime';
} else {
$fdmsGetterName = 'getFDMsMultipleTrimmed';
}
$textFields[] = new Main\Entity\ExpressionField($arUF['ufInfo']['FIELD_NAME'] . self::UF_TEXT_TRIM_POSTFIX, $exprVal, array($arUF['ufInfo']['FIELD_NAME']), array('fetch_data_modification' => array(__CLASS__, $fdmsGetterName)));
}
}
}
}
}
foreach ($textFields as $fieldInfo) {
if (is_object($fieldInfo)) {
$entity->addField($fieldInfo);
} else {
$entity->addField($fieldInfo['def'], $fieldInfo['name']);
}
}
}
public static function setRuntimeFields(\Bitrix\Main\Entity\Base $entity, $sqlTimeInterval)
{
global $DB;
$entity->addField(array('data_type' => 'integer', 'expression' => array('(SELECT SUM(1) FROM b_catalog_product, b_sale_viewed_product WHERE %s = b_sale_viewed_product.PRODUCT_ID
AND b_catalog_product.ID = b_sale_viewed_product.PRODUCT_ID
AND b_sale_viewed_product.DATE_VISIT ' . $sqlTimeInterval . ' AND b_sale_viewed_product.LID = \'' . $DB->ForSql(self::getDefaultSiteId()) . '\')', 'ID')), 'VIEWS_IN_PERIOD_BY_SHOP');
$entity->addField(array('data_type' => 'integer', 'expression' => array('(SELECT COUNT(DISTINCT b_sale_order.ID)
FROM b_catalog_product
INNER JOIN b_sale_basket ON b_catalog_product.ID = b_sale_basket.PRODUCT_ID
INNER JOIN b_sale_order ON b_sale_basket.ORDER_ID = b_sale_order.ID
WHERE
b_catalog_product.ID = %s
AND b_sale_order.PAYED = \'Y\'
AND b_sale_order.DATE_INSERT ' . $sqlTimeInterval . '
AND b_sale_basket.LID = \'' . $DB->ForSql(self::getDefaultSiteId()) . '\')', 'ID')), 'ORDERS_IN_PERIOD_BY_SHOP');
$entity->addField(array('data_type' => 'integer', 'expression' => array($DB->isNull('(SELECT SUM(b_sale_basket.QUANTITY)
FROM b_sale_basket
INNER JOIN b_sale_order ON b_sale_basket.ORDER_ID = b_sale_order.ID
WHERE b_sale_basket.PRODUCT_ID = %s
AND b_sale_order.PAYED = \'Y\'
AND b_sale_order.DEDUCTED = \'Y\'
AND b_sale_order.DATE_INSERT ' . $sqlTimeInterval . '
AND b_sale_basket.LID = \'' . $DB->ForSql(self::getDefaultSiteId()) . '\')', 0) . '+' . $DB->isNull('(SELECT SUM(b_catalog_docs_element.AMOUNT)
FROM b_catalog_store_docs
INNER JOIN b_catalog_docs_element on b_catalog_store_docs.ID = b_catalog_docs_element.DOC_ID
WHERE b_catalog_store_docs.DOC_TYPE = \'D\'
AND b_catalog_store_docs.STATUS = \'Y\'
AND b_catalog_store_docs.DATE_DOCUMENT ' . $sqlTimeInterval . '
AND b_catalog_docs_element.ELEMENT_ID = %s)', 0), 'ID', 'ID')), 'SALED_PRODUCTS_IN_PERIOD_BY_SHOP');
$entity->addField(array('data_type' => 'float', 'expression' => array('(SELECT SUM(b_catalog_docs_element.AMOUNT)
FROM b_catalog_store_docs
INNER JOIN b_catalog_docs_element on b_catalog_store_docs.ID = b_catalog_docs_element.DOC_ID
WHERE b_catalog_store_docs.DOC_TYPE in (\'A\', \'R\')
AND b_catalog_store_docs.STATUS = \'Y\'
AND b_catalog_store_docs.DATE_DOCUMENT ' . $sqlTimeInterval . '
AND b_catalog_docs_element.ELEMENT_ID = %s)', 'ID')), 'ARRIVED_PRODUCTS_IN_PERIOD_BY_SHOP');
$entity->addField(array('data_type' => 'float', 'expression' => array('(SELECT SUM(b_catalog_docs_element.AMOUNT)
FROM b_catalog_store_docs
INNER JOIN b_catalog_docs_element on b_catalog_store_docs.ID = b_catalog_docs_element.DOC_ID
WHERE b_catalog_store_docs.DOC_TYPE in (\'A\', \'M\', \'R\')
AND b_catalog_store_docs.STATUS = \'Y\'
AND b_catalog_store_docs.DATE_DOCUMENT ' . $sqlTimeInterval . '
AND b_catalog_docs_element.STORE_TO = %s
AND b_catalog_docs_element.ELEMENT_ID = %s)', 'StoreProduct:SALE_PRODUCT.STORE_ID', 'ID')), 'ARRIVED_PRODUCTS_IN_PERIOD_BY_STORE');
$entity->addField(array('data_type' => 'integer', 'expression' => array($DB->isNull('(SELECT SUM(b_sale_store_barcode.QUANTITY)
FROM b_sale_store_barcode
INNER JOIN b_sale_basket ON b_sale_store_barcode.BASKET_ID = b_sale_basket.ID
INNER JOIN b_sale_order ON b_sale_basket.ORDER_ID = b_sale_order.ID
WHERE b_sale_store_barcode.STORE_ID = %s
AND b_sale_basket.PRODUCT_ID = %s
AND b_sale_order.PAYED = \'Y\'
AND b_sale_order.DEDUCTED = \'Y\'
AND b_sale_order.DATE_INSERT ' . $sqlTimeInterval . '
AND b_sale_basket.LID = \'' . $DB->ForSql(self::getDefaultSiteId()) . '\')', 0) . '+' . $DB->isNull('(SELECT SUM(b_catalog_docs_element.AMOUNT)
FROM b_catalog_store_docs
INNER JOIN b_catalog_docs_element on b_catalog_store_docs.ID = b_catalog_docs_element.DOC_ID
WHERE b_catalog_store_docs.DOC_TYPE in (\'M\', \'D\')
AND b_catalog_store_docs.STATUS = \'Y\'
AND b_catalog_store_docs.DATE_DOCUMENT ' . $sqlTimeInterval . '
AND b_catalog_docs_element.STORE_FROM = %s
AND b_catalog_docs_element.ELEMENT_ID = %s)', 0), 'StoreProduct:SALE_PRODUCT.STORE_ID', 'ID', 'StoreProduct:SALE_PRODUCT.STORE_ID', 'ID')), 'EXPENSE_PRODUCTS_IN_PERIOD_BY_STORE');
$entity->addField(array('data_type' => 'float', 'expression' => array('100 * CASE WHEN %s IS NULL OR %s = 0 THEN NULL ELSE %s / %s END', 'VIEWS_IN_PERIOD_BY_SHOP', 'VIEWS_IN_PERIOD_BY_SHOP', 'ORDERS_IN_PERIOD_BY_SHOP', 'VIEWS_IN_PERIOD_BY_SHOP')), 'CONVERSION');
}
protected function buildJoinMap()
{
$connection = \Bitrix\Main\Application::getConnection();
$helper = $connection->getSqlHelper();
$aliasLength = $helper->getAliasLength();
// list of used joins
$done = array();
$talias_count = 0;
foreach ($this->global_chains as $chain) {
if ($chain->getLastElement()->getParameter('talias')) {
// already been here
continue;
}
// in NO_DOUBLING mode skip 1:N relations that presented in filter only
if (!$this->data_doubling && $chain->hasBackReference()) {
$alias = $chain->getAlias();
if (isset($this->filter_chains[$alias]) && !isset($this->select_chains[$alias]) && !isset($this->select_expr_chains[$alias]) && !isset($this->group_chains[$alias]) && !isset($this->order_chains[$alias])) {
continue;
}
}
$prev_entity = $this->init_entity;
$prev_alias = $this->getInitAlias(false);
$map_key = '';
/**
* elemenets after init entity
* @var $elements QueryChainElement[]
* */
$elements = array_slice($chain->getAllElements(), 1);
foreach ($elements as $element) {
$table_alias = null;
/**
* define main objects
* @var $src_entity Base
* @var $ref_field ReferenceField
* @var $dst_entity Base
*/
if ($element->getValue() instanceof ReferenceField) {
// ref to another entity
$src_entity = $prev_entity;
$ref_field = $element->getValue();
$dst_entity = $ref_field->getRefEntity();
} elseif (is_array($element->getValue())) {
// link from another entity to this
$src_entity = $prev_entity;
list($dst_entity, $ref_field) = $element->getValue();
} else {
// scalar field
$element->setParameter('talias', $prev_alias . $this->table_alias_postfix);
continue;
}
// mapping
if (empty($map_key)) {
$map_key = $src_entity->getName();
}
$map_key .= '/' . $ref_field->getName() . '/' . $dst_entity->getName();
if (isset($done[$map_key])) {
// already connected
$table_alias = $done[$map_key];
} else {
// prepare reference
$reference = $ref_field->getReference();
if ($element->getValue() instanceof ReferenceField) {
// ref to another entity
if (is_null($table_alias)) {
$table_alias = $prev_alias . '_' . strtolower($ref_field->getName());
if (strlen($table_alias . $this->table_alias_postfix) > $aliasLength) {
$table_alias = 'TALIAS' . $this->table_alias_postfix . '_' . ++$talias_count;
}
}
$alias_this = $prev_alias;
$alias_ref = $table_alias;
} elseif (is_array($element->getValue())) {
if (is_null($table_alias)) {
$table_alias = Base::camel2snake($dst_entity->getName()) . '_' . strtolower($ref_field->getName());
$table_alias = $prev_alias . '_' . $table_alias;
if (strlen($table_alias . $this->table_alias_postfix) > $aliasLength) {
$table_alias = 'TALIAS' . $this->table_alias_postfix . '_' . ++$talias_count;
}
}
$alias_this = $table_alias;
$alias_ref = $prev_alias;
if ($dst_entity->isUtm()) {
// add to $reference
$reference = array($reference, '=this.FIELD_ID' => array('?i', $element->getParameter('ufield')->getFieldId()));
}
}
// replace this. and ref. to real definition -- not supported yet
// instead it we set $alias_this and $alias_ref
$csw_reference = $this->prepareJoinReference($reference, $alias_this . $this->table_alias_postfix, $alias_ref . $this->table_alias_postfix);
$join = array('type' => $ref_field->getJoinType(), 'table' => $dst_entity->getDBTableName(), 'alias' => $table_alias . $this->table_alias_postfix, 'reference' => $csw_reference);
$this->join_map[] = $join;
$done[$map_key] = $table_alias;
}
// set alias for each element
$element->setParameter('talias', $table_alias . $this->table_alias_postfix);
$prev_entity = $dst_entity;
$prev_alias = $table_alias;
}
}
}
/**
* @param $elem
* @param $select
* @param $is_init_entity_aggregated
* @param $fList
* @param Entity\QueryChain[] $fChainList
* @param $helper_class
* @param Entity\Base $entity
*
* @return array
*/
public static function prepareSelectViewElement($elem, $select, $is_init_entity_aggregated, $fList, $fChainList, $helper_class, Entity\Base $entity)
{
$result = null;
$alias = null;
if (empty($elem['aggr']) && !strlen($elem['prcnt'])) {
$result = $elem['name'];
} else {
$expression = '';
/** @var Entity\Field $field */
$field = $fList[$elem['name']];
$chain = $fChainList[$elem['name']];
$alias = $chain->getAlias();
$dataType = call_user_func(array($helper_class, 'getFieldDataType'), $field);
if (!empty($elem['aggr'])) {
$alias = $elem['aggr'] . '_' . $alias;
if ($dataType == 'boolean') {
// sum int for boolean
global $DB;
/** @var Entity\BooleanField $field */
$trueValue = $field->normalizeValue(true);
$localDef = 'CASE WHEN %s = \'' . $DB->ForSql($trueValue) . '\' THEN 1 ELSE 0 END';
} else {
$localDef = '%s';
}
if ($elem['aggr'] == 'COUNT_DISTINCT') {
$dataType = 'integer';
$expression = array('COUNT(DISTINCT ' . $localDef . ')', $elem['name']);
} else {
if ($dataType == 'boolean') {
$dataType = 'integer';
}
if ($elem['aggr'] == 'GROUP_CONCAT') {
$expression = array($localDef, $elem['name']);
} else {
$expression = array($elem['aggr'] . '(' . $localDef . ')', $elem['name']);
}
}
// pack 1:N aggregations into subquery
if ($chain->hasBackReference() && $elem['aggr'] != 'GROUP_CONCAT') {
$confirm = call_user_func_array(array($helper_class, 'confirmSelectBackReferenceRewrite'), array(&$elem, $chain));
if ($confirm) {
$filter = array();
foreach ($entity->GetPrimaryArray() as $primary) {
$filter['=' . $primary] = new CSQLWhereExpression('?#', ToLower($entity->getCode()) . '.' . $primary);
}
$query = new Entity\Query($entity);
$query->addSelect(new Entity\ExpressionField('X', $expression[0], $elem['name']));
$query->setFilter($filter);
$query->setTableAliasPostfix('_sub');
$expression = array('(' . $query->getQuery() . ')');
// double aggregation if init entity aggregated
if ($is_init_entity_aggregated) {
if ($elem['aggr'] == 'COUNT_DISTINCT') {
$expression[0] = 'SUM(' . $expression[0] . ')';
} else {
$expression[0] = $elem['aggr'] . '(' . $expression[0] . ')';
}
}
}
// confirmed
}
}
if (strlen($elem['prcnt'])) {
$alias = $alias . '_PRCNT';
$dataType = 'integer';
if ($elem['prcnt'] == 'self_column') {
if (empty($expression)) {
$expression = array('%s', $elem['name']);
}
} else {
if (empty($expression)) {
$localDef = '%s';
$localMembers = array($elem['name']);
} else {
$localDef = $expression[0];
$localMembers = array_slice($expression, 1);
}
list($remoteAlias, $remoteSelect) = self::prepareSelectViewElement($select[$elem['prcnt']], $select, $is_init_entity_aggregated, $fList, $fChainList, $helper_class, $entity);
if (is_array($remoteSelect) && !empty($remoteSelect['expression'])) {
// remote field is expression
$remoteDef = $remoteSelect['expression'][0];
$remoteMembers = array_slice($remoteSelect['expression'], 1);
$alias = $alias . '_FROM_' . $remoteAlias;
} else {
// remote field is usual field
$remoteDef = '%s';
$remoteMembers = array($remoteSelect);
$remoteAlias = Entity\QueryChain::getAliasByDefinition($entity, $remoteSelect);
$alias = $alias . '_FROM_' . $remoteAlias;
}
$exprDef = '(' . $localDef . ') / (' . $remoteDef . ') * 100';
$expression = array_merge(array($exprDef), $localMembers, $remoteMembers);
// 'ROUND(STATUS / ID * 100)'
// 'ROUND( (EX1(F1, F2)) / (EX2(F3, F1)) * 100)',
// F1, F2, F3, F1
}
}
$result = array('data_type' => $dataType, 'expression' => $expression);
}
return array($alias, $result);
}
protected static function getRuntimeFieldMixinsCheckRights($parameters)
{
$result = false;
$parameters['USER_ID'] = Assert::expectIntegerPositive($parameters['USER_ID'], '$parameters[USER_ID]');
$rf = $parameters['REF_FIELD'];
if (!\CTasksTools::IsAdmin($userId) && !\CTasksTools::IsPortalB24Admin($userId)) {
list($conditions, $expression) = \CTasks::getPermissionFilterConditions($parameters, array('USE_PLACEHOLDERS' => true));
$conditions = "(case when (" . implode(' OR ', $conditions) . ") then '1' else '0' end)";
array_unshift($expression, $conditions);
$query = new \Bitrix\Main\Entity\Query('Bitrix\\Tasks\\Task');
$query->registerRuntimeField('F', array('data_type' => 'string', 'expression' => $expression));
$query->setFilter(array('=F' => '1'));
$query->setSelect(array('TASK_ID' => 'ID'));
$result = new Entity\ReferenceField($parameters['NAME'], \Bitrix\Main\Entity\Base::getInstanceByQuery($query), array('=this.' . ((string) $rf != '' ? $rf : 'ID') => 'ref.TASK_ID'), array('join_type' => 'inner'));
}
return $result;
}
use Bitrix\Main\Entity;
// <editor-fold defaultstate="collapsed" desc="calc variations">
$calcVariations = $arResult['calcVariations'] = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getCalcVariations'));
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="filter compare variations">
$compareVariations = $arResult['compareVariations'] = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getCompareVariations'));
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="period types">
$periodTypes = $arResult['periodTypes'] = array('month', 'month_ago', 'week', 'week_ago', 'days', 'after', 'before', 'interval', 'all');
// </editor-fold>
try {
// <editor-fold defaultstate="collapsed" desc="common initiazlize">
$ownerId = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getOwnerId'));
$entityName = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getEntityName'));
$entityFields = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getColumnList'));
$initEntity = Entity\Base::getInstance($entityName);
$strChains = $entityFields;
$chains = CReport::generateChains($strChains, $initEntity, '');
$arResult['chains'] = $chains;
$arResult['fieldsTree'] = CReport::generateColumnTree($chains, $initEntity, $arParams['REPORT_HELPER_CLASS']);
$fieldList = CReport::getUniqueFieldsByTree($arResult['fieldsTree']);
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="validation">
if ($arParams['ACTION'] == 'edit' || $arParams['ACTION'] == 'copy' || $arParams['ACTION'] == 'delete') {
$result = Bitrix\Report\ReportTable::getById($arParams['REPORT_ID']);
$report = $result->fetch();
if (empty($report)) {
throw new BXUserException(sprintf(GetMessage('REPORT_NOT_FOUND'), $arParams['REPORT_ID']));
}
if ($report['CREATED_BY'] != $USER->GetID()) {
throw new BXUserException(GetMessage('REPORT_VIEW_PERMISSION_DENIED'));
public static function getUtmEntityClassName(Entity\Base $hlentity, $userfield)
{
return $hlentity->getName() . 'Utm' . Entity\Base::snake2camel($userfield['FIELD_NAME']);
}
/**
* @param Base|string $entity
*
* @return bool
*/
public static function destroy($entity)
{
if ($entity instanceof Base) {
$entityName = $entity->getDataClass();
} else {
$entityName = static::normalizeEntityClass($entity);
}
if (isset(self::$instances[$entityName])) {
unset(self::$instances[$entityName]);
DataManager::unsetEntity($entityName);
return true;
}
return false;
}
protected function buildJoinMap($chains = null)
{
$connection = Main\Application::getConnection();
$helper = $connection->getSqlHelper();
$aliasLength = $helper->getAliasLength();
$talias_count = 0;
if (empty($chains)) {
$chains = $this->global_chains;
}
foreach ($chains as $chain) {
if ($chain->getLastElement()->getParameter('talias')) {
// already been here
continue;
}
// in NO_DOUBLING mode skip 1:N relations that presented in filter only
if ($chain->forcesDataDoublingOff() || $this->data_doubling_off && $chain->hasBackReference()) {
$alias = $chain->getAlias();
if (isset($this->filter_chains[$alias]) && !isset($this->select_chains[$alias]) && !isset($this->select_expr_chains[$alias]) && !isset($this->group_chains[$alias]) && !isset($this->order_chains[$alias])) {
continue;
}
}
$prev_alias = $this->getInitAlias(false);
$map_key = '';
/**
* elemenets after init entity
* @var $elements QueryChainElement[]
* */
$elements = array_slice($chain->getAllElements(), 1);
$currentDedinition = array();
foreach ($elements as $element) {
$table_alias = null;
/**
* define main objects
* @var $ref_field ReferenceField
* @var $dst_entity Base
*/
if ($element->getValue() instanceof ReferenceField) {
// ref to another entity
$ref_field = $element->getValue();
$dst_entity = $ref_field->getRefEntity();
} elseif (is_array($element->getValue())) {
// link from another entity to this
list($dst_entity, $ref_field) = $element->getValue();
} else {
// scalar field
$element->setParameter('talias', $prev_alias . $this->table_alias_postfix);
continue;
}
// mapping
if (empty($map_key)) {
$map_key = join('.', $currentDedinition);
}
$map_key .= '/' . $ref_field->getName() . '/' . $dst_entity->getName();
$currentDedinition[] = $element->getDefinitionFragment();
if (isset($this->join_registry[$map_key])) {
// already connected
$table_alias = $this->join_registry[$map_key];
} else {
// prepare reference
$reference = $ref_field->getReference();
if ($element->getValue() instanceof ReferenceField) {
// ref to another entity
if (is_null($table_alias)) {
$table_alias = $prev_alias . '_' . strtolower($ref_field->getName());
if (strlen($table_alias . $this->table_alias_postfix) > $aliasLength) {
$table_alias = 'TALIAS_' . ++$talias_count;
}
}
$alias_this = $prev_alias;
$alias_ref = $table_alias;
$isBackReference = false;
$definition_this = join('.', array_slice($currentDedinition, 0, -1));
$definition_ref = join('.', $currentDedinition);
} elseif (is_array($element->getValue())) {
if (is_null($table_alias)) {
$table_alias = Base::camel2snake($dst_entity->getName()) . '_' . strtolower($ref_field->getName());
$table_alias = $prev_alias . '_' . $table_alias;
if (strlen($table_alias . $this->table_alias_postfix) > $aliasLength) {
$table_alias = 'TALIAS_' . ++$talias_count;
}
}
$alias_this = $table_alias;
$alias_ref = $prev_alias;
$isBackReference = true;
$definition_this = join('.', $currentDedinition);
$definition_ref = join('.', array_slice($currentDedinition, 0, -1));
} else {
throw new Main\SystemException(sprintf('Unknown reference element `%s`', $element->getValue()));
}
// replace this. and ref. to real definition
$csw_reference = $this->prepareJoinReference($reference, $alias_this . $this->table_alias_postfix, $alias_ref . $this->table_alias_postfix, $definition_this, $definition_ref, $isBackReference);
// double check after recursive call in prepareJoinReference
if (!isset($this->join_registry[$map_key])) {
$join = array('type' => $ref_field->getJoinType(), 'table' => $dst_entity->getDBTableName(), 'alias' => $table_alias . $this->table_alias_postfix, 'reference' => $csw_reference, 'map_key' => $map_key);
$this->join_map[] = $join;
$this->join_registry[$map_key] = $table_alias;
}
}
// set alias for each element
$element->setParameter('talias', $table_alias . $this->table_alias_postfix);
$prev_alias = $table_alias;
}
}
}
// </editor-fold>
$runtime = array();
$select = array();
$group = array();
$order = array();
$limit = array();
$options = array('SQL_TIME_INTERVAL' => $sqlTimeInterval);
$excelView = isset($_GET["EXCEL"]) && $_GET["EXCEL"] == "Y";
// <editor-fold defaultstate="collapsed" desc="parse entity">
$entityName = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getEntityName'));
$entityFields = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getColumnList'));
$grcFields = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getGrcColumns'));
//$arUFInfo = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getUFInfo'));
$arUFEnumerations = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getUFEnumerations'));
// customize entity
$entity = clone Entity\Base::getInstance($entityName);
call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'setRuntimeFields'), $entity, $sqlTimeInterval);
$chains = CReport::generateChains($entityFields, $entity, '');
$fieldsTree = CReport::generateColumnTree($chains, $entity, $arParams['REPORT_HELPER_CLASS']);
unset($chains);
// custom columns types
$customColumnTypes = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getCustomColumnTypes'));
if (!is_array($customColumnTypes)) {
$customColumnTypes = array();
}
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="parse select columns">
// <editor-fold defaultstate="collapsed" desc="collect fields">
$fList = array();
$fChainList = array();
$bGroupingMode = false;
public static function setRuntimeFields(\Bitrix\Main\Entity\Base $entity, $sqlTimeInterval)
{
global $DB, $DBType;
$options = array();
Crm\LeadTable::processQueryOptions($options);
$entity->addField(array('data_type' => 'boolean', 'expression' => array('CASE WHEN %s IN ' . $options['WORK_STATUS_IDS'] . ' THEN 1 ELSE 0 END', 'STATUS_ID'), 'values' => array(0, 1)), 'IS_WORK');
$entity->addField(array('data_type' => 'boolean', 'expression' => array('CASE WHEN %s IN ' . $options['REJECT_STATUS_IDS'] . ' THEN 1 ELSE 0 END', 'STATUS_ID'), 'values' => array(0, 1)), 'IS_REJECT');
$datetimeNull = ToUpper($DBType) === 'MYSQL' ? 'CAST(NULL AS DATETIME)' : 'NULL';
$entity->addField(array('data_type' => 'datetime', 'expression' => array('CASE WHEN %s = \'CONVERTED\' OR %s IN ' . $options['REJECT_STATUS_IDS'] . ' THEN ' . $DB->datetimeToDateFunction('%s') . ' ELSE ' . $datetimeNull . ' END', 'STATUS_ID', 'STATUS_ID', 'DATE_CLOSED')), 'DATE_CLOSED_SHORT');
self::appendDateTimeUserFieldsAsShort($entity);
}
public static function prepareSortParams($entityTypeID, array &$entityIDs, $type = '')
{
if (empty($entityIDs)) {
return array();
}
if (!is_string($type)) {
$type = '';
}
$query = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::getEntity());
$query->addSelect('ENTITY_ID');
$query->addSelect('TYPE');
$query->addSelect('VALUE');
$subQuery = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::getEntity());
$subQuery->registerRuntimeField('', new Main\Entity\ExpressionField('MIN_ID', 'MIN(ID)'));
$subQuery->addSelect('MIN_ID');
$subQuery->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$subQuery->addFilter('@ENTITY_ID', $entityIDs);
if ($type !== '') {
$subQuery->addFilter('=TYPE', $type);
}
$subQuery->addGroup('ENTITY_ID');
$subQuery->addGroup('TYPE');
$query->registerRuntimeField('', new Main\Entity\ReferenceField('M', Main\Entity\Base::getInstanceByQuery($subQuery), array('=this.ID' => 'ref.MIN_ID'), array('join_type' => 'INNER')));
$result = array();
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$entityID = intval($fields['ENTITY_ID']);
if (!isset($result[$entityID])) {
$result[$entityID] = array();
}
$type = isset($fields['TYPE']) ? $fields['TYPE'] : '';
$value = isset($fields['VALUE']) ? $fields['VALUE'] : '';
$result[$entityID][$type] = $value;
}
return $result;
}
/** @return array */
public function getList(array $params)
{
$group = isset($params['group']) ? strtoupper($params['group']) : '';
if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) {
$group = '';
}
/** @var Filter $filter */
$filter = isset($params['filter']) ? $params['filter'] : null;
if (!$filter instanceof Filter) {
throw new Main\ObjectNotFoundException("The 'filter' is not found in params.");
}
$semanticID = $filter->getExtraParam('semanticID', PhaseSemantics::UNDEFINED);
$isFinalSemantics = PhaseSemantics::isFinal($semanticID);
$group = isset($params['group']) ? strtoupper($params['group']) : '';
if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) {
$group = '';
}
/** @var array $select */
$select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array();
$name = '';
$aggregate = '';
if (!empty($select)) {
$selectItem = $select[0];
if (isset($selectItem['name'])) {
$name = $selectItem['name'];
}
if (isset($selectItem['aggregate'])) {
$aggregate = strtoupper($selectItem['aggregate']);
}
}
if ($name === '') {
$name = 'INVOICE_SUM';
}
if ($aggregate !== '' && !in_array($aggregate, array('SUM', 'COUNT', 'MAX', 'MIN'))) {
$aggregate = '';
}
$permissionSql = '';
if ($this->enablePermissionCheck) {
$permissionSql = $this->preparePermissionSql();
if ($permissionSql === false) {
//Access denied;
return array();
}
}
$period = $filter->getPeriod();
$periodStartDate = $period['START'];
$periodEndDate = $period['END'];
$query = new Query(DealInvoiceStatisticsTable::getEntity());
$query->addSelect($name);
if ($aggregate !== '') {
if ($aggregate === 'COUNT') {
$query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)"));
} else {
$query->registerRuntimeField('', new ExpressionField($name, "{$aggregate}({$name})"));
}
}
$query->setTableAliasPostfix('_s2');
$subQuery = new Query(DealInvoiceStatisticsTable::getEntity());
$subQuery->setTableAliasPostfix('_s1');
$subQuery->addSelect('OWNER_ID');
$subQuery->addFilter('>=END_DATE', $periodStartDate);
$subQuery->addFilter('<=START_DATE', $periodEndDate);
//$subQuery->addFilter('<=CREATED_DATE', $periodEndDate);
if ($semanticID !== PhaseSemantics::UNDEFINED) {
$subQuery->addFilter('=STAGE_SEMANTIC_ID', $semanticID);
}
if ($this->enablePermissionCheck && is_string($permissionSql) && $permissionSql !== '') {
$subQuery->addFilter('@OWNER_ID', new SqlExpression($permissionSql));
}
$responsibleIDs = $filter->getResponsibleIDs();
if (!empty($responsibleIDs)) {
$subQuery->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
}
$subQuery->addGroup('OWNER_ID');
$subQuery->addSelect('MAX_CREATED_DATE');
$subQuery->registerRuntimeField('', new ExpressionField('MAX_CREATED_DATE', 'MAX(CREATED_DATE)'));
$query->registerRuntimeField('', new ReferenceField('M', Base::getInstanceByQuery($subQuery), array('=this.OWNER_ID' => 'ref.OWNER_ID', '=this.CREATED_DATE' => 'ref.MAX_CREATED_DATE'), array('join_type' => 'INNER')));
$sort = isset($params['sort']) && is_array($params['sort']) && !empty($params['sort']) ? $params['sort'] : null;
if ($sort) {
foreach ($sort as $sortItem) {
if (isset($sortItem['name'])) {
$query->addOrder($sortItem['name'], isset($sortItem['order']) ? $sortItem['order'] : 'asc');
}
}
}
if ($group !== '') {
if ($group === self::GROUP_BY_USER) {
$query->addSelect('RESPONSIBLE_ID');
$query->addGroup('RESPONSIBLE_ID');
} else {
if ($group === self::GROUP_BY_DATE) {
if ($isFinalSemantics) {
$query->addSelect('END_DATE', 'D');
$query->addGroup('END_DATE');
if (!$sort) {
$query->addOrder('END_DATE', 'ASC');
}
} else {
$query->addSelect('CREATED_DATE', 'D');
$query->addGroup('CREATED_DATE');
if (!$sort) {
$query->addOrder('CREATED_DATE', 'ASC');
}
}
}
}
}
$dbResult = $query->exec();
//Trace('sql', Query::getLastQuery(), 1);
$result = array();
if ($group === self::GROUP_BY_DATE) {
while ($ary = $dbResult->fetch()) {
$ary['DATE'] = $ary['D']->format('Y-m-d');
unset($ary['D']);
if ($ary['DATE'] === '9999-12-31') {
//Skip empty dates
continue;
}
$result[] = $ary;
}
} elseif ($group === self::GROUP_BY_USER) {
$userIDs = array();
while ($ary = $dbResult->fetch()) {
$userID = $ary['RESPONSIBLE_ID'] = (int) $ary['RESPONSIBLE_ID'];
if ($userID > 0 && !isset($userNames[$userID])) {
$userIDs[] = $userID;
}
$result[] = $ary;
}
$userNames = self::prepareUserNames($userIDs);
foreach ($result as &$item) {
$userID = $item['RESPONSIBLE_ID'];
$item['USER_ID'] = $userID;
$item['USER'] = isset($userNames[$userID]) ? $userNames[$userID] : "[{$userID}]";
unset($item['RESPONSIBLE_ID']);
}
unset($item);
} else {
while ($ary = $dbResult->fetch()) {
$result[] = $ary;
}
}
return $result;
}
public static function getChainByDefinition(Base $init_entity, $definition)
{
$chain = new QueryChain();
$chain->addElement(new QueryChainElement($init_entity));
$def_elements = explode('.', $definition);
$def_elements_size = count($def_elements);
$prev_entity = $init_entity;
$i = 0;
foreach ($def_elements as &$def_element) {
$is_last_elem = ++$i == $def_elements_size;
$not_found = false;
// all elements should be a Reference field or Entity
// normal (scalar) field can only be the last element
if ($prev_entity->hasField($def_element)) {
// field has been found at current entity
$field = $prev_entity->getField($def_element);
if ($field instanceof ReferenceField) {
$prev_entity = $field->getRefEntity();
} elseif ($field instanceof ExpressionField) {
// expr can be in the middle too
} elseif (!$is_last_elem) {
throw new SystemException(sprintf('Normal fields can be only the last in chain, `%s` %s is not the last.', $field->getName(), get_class($field)));
}
if ($is_last_elem && $field instanceof ExpressionField) {
// we should have own copy of build_from_chains to set join aliases there
$field = clone $field;
}
$chain->addElement(new QueryChainElement($field));
} elseif ($prev_entity->hasUField($def_element) && false) {
/** @deprecated */
// extend chain with utm/uts entity
$ufield = $prev_entity->getUField($def_element);
if ($ufield->isMultiple()) {
// add utm entity user.utm:source_object (1:N)
$utm_entity = Base::getInstance($prev_entity->getNamespace() . 'Utm' . $prev_entity->getName());
$chain->addElement(new QueryChainElement(array($utm_entity, $utm_entity->getField('SOURCE_OBJECT')), array('ufield' => $ufield)));
if ($ufield->getTypeId() == 'iblock_section' && substr($ufield->getName(), -3) == '_BY' && $prev_entity->hasUField(substr($ufield->getName(), 0, -3))) {
// connect next entity
$utm_fname = $ufield->getName();
$prev_entity = Base::getInstance('Bitrix\\Iblock\\Section');
} else {
$utm_fname = $ufield->getValueFieldName();
}
$chain->addElement(new QueryChainElement($utm_entity->getField($utm_fname), array('ufield' => $ufield)));
} else {
// uts table - single value
// add uts entity user.uts (1:1)
$uts_entity = Base::getInstance($prev_entity->getNamespace() . 'Uts' . $prev_entity->getName());
$chain->addElement(new QueryChainElement($prev_entity->getField('UTS_OBJECT')));
// add `value` field
$chain->addElement(new QueryChainElement($uts_entity->getField($def_element)));
}
} elseif (Base::isExists($def_element) && Base::getInstance($def_element)->getReferencesCountTo($prev_entity->getName()) == 1) {
// def_element is another entity with only 1 reference to current entity
// need to identify Reference field
$ref_entity = Base::getInstance($def_element);
$field = end($ref_entity->getReferencesTo($prev_entity->getName()));
$prev_entity = $ref_entity;
$chain->addElement(new QueryChainElement(array($ref_entity, $field)));
} elseif (($pos_wh = strpos($def_element, ':')) > 0) {
$ref_entity_name = substr($def_element, 0, $pos_wh);
if (strpos($ref_entity_name, '\\') === false) {
// if reference has no namespace, then it'is in the namespace of previous entity
$ref_entity_name = $prev_entity->getNamespace() . $ref_entity_name;
}
if (Base::isExists($ref_entity_name) && Base::getInstance($ref_entity_name)->hasField($ref_field_name = substr($def_element, $pos_wh + 1)) && Base::getInstance($ref_entity_name)->getField($ref_field_name) instanceof ReferenceField) {
/** @var ReferenceField $reference */
$reference = Base::getInstance($ref_entity_name)->getField($ref_field_name);
if ($reference->getRefEntity()->getFullName() == $prev_entity->getFullName()) {
// chain element is another entity with >1 references to current entity
// def like NewsArticle:AUTHOR, NewsArticle:LAST_COMMENTER
// NewsArticle - entity, AUTHOR and LAST_COMMENTER - Reference fields
$chain->addElement(new QueryChainElement(array(Base::getInstance($ref_entity_name), Base::getInstance($ref_entity_name)->getField($ref_field_name))));
$prev_entity = Base::getInstance($ref_entity_name);
} else {
$not_found = true;
}
} else {
$not_found = true;
}
} elseif ($def_element == '*' && $is_last_elem) {
continue;
} else {
// unknown chain
$not_found = true;
}
if ($not_found) {
throw new SystemException(sprintf('Unknown field definition `%s` (%s) for %s Entity.', $def_element, $definition, $prev_entity->getName()), 100);
}
}
return $chain;
}