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