public static function setRuntimeFields(\Bitrix\Main\Entity\Base $entity, $sqlTimeInterval)
{
$entity->addField(array('data_type' => 'integer', 'expression' => array('ROUND((SELECT SUM(CASE WHEN CREATED_DATE ' . $sqlTimeInterval . ' THEN SECONDS ELSE 0 END)/60 FROM b_tasks_elapsed_time WHERE TASK_ID = %s),0)', 'ID')), 'DURATION_FOR_PERIOD');
$entity->addField(array('data_type' => 'integer', 'expression' => array('(SELECT SUM(CASE WHEN CREATED_DATE ' . $sqlTimeInterval . ' THEN SECONDS ELSE 0 END) ' . 'FROM b_tasks_elapsed_time WHERE TASK_ID = %s)', 'ID')), 'TIME_SPENT_IN_LOGS_FOR_PERIOD');
$entity->addField(array('data_type' => 'boolean', 'expression' => array('CASE WHEN %s ' . $sqlTimeInterval . ' THEN 1 ELSE 0 END', 'CREATED_DATE'), 'values' => array(0, 1)), 'IS_NEW');
$entity->addField(array('data_type' => 'boolean', 'expression' => array('CASE WHEN %s ' . $sqlTimeInterval . ' THEN 0 ELSE 1 END', 'DATE_START'), 'values' => array(0, 1)), 'IS_OPEN');
$entity->addField(array('data_type' => 'boolean', 'expression' => array('CASE WHEN %s ' . $sqlTimeInterval . ' AND %s IS NOT NULL THEN 1 ELSE 0 END', 'CLOSED_DATE', 'CLOSED_DATE'), 'values' => array(0, 1)), 'IS_FINISHED');
}
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');
}
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 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']);
}
}
}
