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