function getMobileReports() { define('COLUMNS_COUNT_FOR_SIMPLE_TEMPLATE', 3); define('PATH_TO_MOBILE_REPORTS', '/bitrix/admin/mobile/sale_reports_view.php'); // Using report module if (!CModule::IncludeModule('report')) { return false; } // Using catalog module if (!CModule::IncludeModule('catalog')) { return false; } CBaseSaleReportHelper::initOwners(); $dbRepList = Bitrix\Report\ReportTable::getList(array('select' => array('ID', 'TITLE', 'SETTINGS'), 'filter' => array('=CREATED_BY' => $GLOBALS["USER"]->GetID(), '=OWNER_ID' => CBaseSaleReportHelper::getOwners()))); $arMenuItems = array(); while ($arReport = $dbRepList->fetch()) { $settings = unserialize($arReport['SETTINGS']); if (isset($settings['mobile']) && is_array($settings['mobile']) && isset($settings['mobile']['enabled']) && $settings['mobile']['enabled'] == true) { if (count($settings['select']) <= COLUMNS_COUNT_FOR_SIMPLE_TEMPLATE) { $template = "admin_mobile"; } else { $template = "admin_mobile_encl"; } $arMenuItems[] = array("text" => htmlspecialcharsbx($arReport['TITLE']), "data-url" => PATH_TO_MOBILE_REPORTS . '?ID=' . $arReport['ID'] . '&rep_templ=' . $template); } } if (empty($arMenuItems)) { return false; } else { return array("text" => GetMessage("SMOB_REPORTS"), "type" => "section", "items" => $arMenuItems); } }
"items_id" => "menu_sale_stat", "items" => array(), ); if(IsModuleInstalled('report')) { $arSaleReports = array(); if(method_exists($adminMenu, "IsSectionActive")) { if($adminMenu->IsSectionActive("menu_sale_report") && CModule::IncludeModule("report")) { CModule::IncludeModule("sale"); CBaseSaleReportHelper::initOwners(); $dbRepList = Bitrix\Report\ReportTable::getList(array( 'select' => array('ID', 'TITLE', 'DESCRIPTION'), 'filter' => array('=CREATED_BY' => $USER->GetID(), '=OWNER_ID' => CBaseSaleReportHelper::getOwners()) )); while($arReport = $dbRepList->fetch()) { $arSaleReports[] = array( "text" => htmlspecialcharsbx($arReport["TITLE"]), "title" => htmlspecialcharsbx($arReport["DESCRIPTION"]), "url" => "sale_report_view.php?lang=".LANGUAGE_ID."&ID=".$arReport["ID"], "more_url" => array("sale_report_construct.php?lang=".LANGUAGE_ID."&ID=".$arReport["ID"]), ); } } } $arMenu["items"][] = array( "text" => GetMessage("SALE_REPORTS_DESCR"),
public static function formatResultsTotal(&$total, &$columnInfo, $bFormatOnly = false) { parent::formatResultsTotal($total, $columnInfo); foreach ($total as $k => &$v) { // Formatting of monetary fields. foreach (self::$monetaryFields as $monField) { if (preg_match('/' . $monField . '$/', $k)) { if (!$bFormatOnly) { $v = self::calculateInReportCurrency($v); } $v = number_format(doubleval($v), 2, '.', ' '); break; } } // Formatting fields with goods quantity. foreach (self::$goodsQuantityFields as $qField) { if (preg_match('/' . $qField . '$/', $k)) { if (!empty($v) && !is_null($v) && $v != ' ') { if (self::$fDecimalQuant) { $v = sprintf('%.3f', round($v, 3)); } else { $v = sprintf('%d', round($v, 0)); } } break; } } // Formatting fields without summary calculation. foreach (self::$withoutTotalFields as $wtField) { if (preg_match('/TOTAL_' . $wtField . '$/', $k)) { unset($total[$k]); break; } } // Formatting fields of price types if (!$bFormatOnly) { if (preg_match('/[A-Za-z_]*PRICE_TYPE_[0-9]+$/', $k) && !empty($v) && $v !== ' ') { $v = trim($v); $spacePos = strpos($v, ' '); $v = number_format(doubleval(substr($v, 0, $spacePos)), 2, '.', ' ') . substr($v, $spacePos); } } } }
CBaseSaleReportHelper::setSelectedCurrency($reportCurrencyId); $reportCurrency = CCurrencyLang::GetById($reportCurrencyId, LANGUAGE_ID); $reportWeightUnits = CBaseSaleReportHelper::getDefaultSiteWeightUnits(); $arParams['REPORT_CURRENCY_LABEL_TEXT'] = GetMessage('SALE_REPORT_VIEW_CURRENCY_LABEL_TITLE') . ': ' . $reportCurrency['FULL_NAME']; $arParams['REPORT_WEIGHT_UNITS_LABEL_TEXT'] = GetMessage('SALE_REPORT_VIEW_WEIGHT_UNITS_LABEL_TITLE') . ': ' . $reportWeightUnits; // Beforehand we get report parameters. $arRepParams = array(); if (!($arRepParams = Bitrix\Report\ReportTable::getById($ID)->fetch())) { $errorMessage .= GetMessage("SALE_REPORT_VIEW_ERROR_GET_REP_PARAMS") . '<br>'; $fCriticalError = true; } } if (!$fCriticalError) { // get helper name $arParams['OWNER_ID'] = $arRepParams['OWNER_ID']; $arParams['REPORT_HELPER_CLASS'] = CBaseSaleReportHelper::getHelperByOwner($arRepParams['OWNER_ID']); // fill report title $arParams['TITLE'] = $arRepParams['TITLE']; } if (!$fCriticalError) { // helper specific filters if ($arParams['OWNER_ID'] === 'sale_SaleProduct') { // Product custom filter (set value to helper) if (!empty($arParams['F_SALE_PRODUCT'])) { call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'setCustomProductFilter'), $arParams['F_SALE_PRODUCT']); } // Product custom "types of prices" filter (set report setting to helper) $arRepSetting = unserialize($arRepParams['SETTINGS']); if ($arRepSetting['helper_spec']['ucspt'] === true) { call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'enablePriceTypesColumns'), true); }
$DB->StartTransaction(); if (!CReport::Delete($ID)) { $DB->Rollback(); if ($ex = $APPLICATION->GetException()) { $lReports->AddGroupError($ex->GetString(), $ID); } else { $lReports->AddGroupError(GetMessage("SALE_REPORT_ERROR_DELREPFROMLIST"), $ID); } } $DB->Commit(); break; } } } // Getting reports list. $dbRepList = Bitrix\Report\ReportTable::getList(array('select' => array('ID', 'TITLE', 'DESCRIPTION', 'CREATED_DATE', 'MARK_DEFAULT'), 'filter' => array('=CREATED_BY' => $USER->GetID(), '=OWNER_ID' => CBaseSaleReportHelper::getOwners()))); $dbRepList = new CAdminResult($dbRepList, $sTableID); $dbRepList->NavStart(); $lReports->NavText($dbRepList->GetNavPrint(GetMessage("SALE_REPORT_TITLE"))); $lReports->AddHeaders(array(array('id' => 'TITLE', 'content' => '<b>' . GetMessage('SALE_REPORT_LIST_TITLE') . '</b>', 'sort' => 'TITLE', 'default' => true), array('id' => 'CREATED_DATE', 'content' => '<b>' . GetMessage('SALE_REPORT_LIST_CREATED_DATE') . '</b>', 'sort' => 'CREATED_DATE', 'default' => true))); // $arRes containing array as: array( 'ID' =>..., 'TITLE'=>..., 'DESCRIPTION'=>..., 'CREATED_DATE'=>... ). $nReports = 0; while ($arRes = $dbRepList->NavNext(false)) { $lRow = $lReports->AddRow($arRes['ID'], $arRes); $lRow->AddViewField('TITLE', "<a href='" . "sale_report_view.php?lang=" . LANG . '&ID=' . $arRes['ID'] . "' title='" . $arRes['DESCRIPTION'] . "'>" . $arRes['TITLE'] . "</a>"); $createdDateStr = $arRes['CREATED_DATE'] instanceof \Bitrix\Main\Type\DateTime || $arRes['CREATED_DATE'] instanceof \Bitrix\Main\Type\Date ? ConvertTimeStamp($arRes['CREATED_DATE']->getTimestamp(), 'SHORT') : ''; $lRow->AddViewField('CREATED_DATE', $createdDateStr); $markNum = 0; if (isset($arRes['MARK_DEFAULT'])) { $markNum = intval($arRes['MARK_DEFAULT']); }
?> </select> </span> <span name="report-filter-value-control-PRODUCT.GoodsSection:PRODUCT.SECT"> <select class="report-filter-select sale-report-site-dependent" name="value" tid="Section"> <?php echo CBaseSaleReportHelper::getSectionsSelectHTMLOptions(); ?> </select> </span> <span name="report-filter-value-control-IBLOCK.SectionElement:IBLOCK_ELEMENT.IBLOCK_SECTION"> <select class="report-filter-select sale-report-site-dependent" name="value" tid="Section"> <?php echo CBaseSaleReportHelper::getSectionsSelectHTMLOptions(); ?> </select> <!-- Loading lists of filters dependent on a site when the filter of a site is changed --> <script type="text/javascript"> BX.ready( function () { var siteSelect = BX('sale-site-filter'); if (siteSelect) { BX.bind(siteSelect, 'change', onChangeSiteFilter); } } ); function onChangeSiteFilter()
public static function getMap() { // Get weight factor $siteId = ''; $weight_koef = 0; $site_currency = ''; if (class_exists('\\CBaseSaleReportHelper')) { if (\CBaseSaleReportHelper::isInitialized()) { $siteId = \CBaseSaleReportHelper::getDefaultSiteId(); if ($siteId !== null) { $weight_koef = intval(\CBaseSaleReportHelper::getDefaultSiteWeightDivider()); } // Get site currency $site_currency = \CBaseSaleReportHelper::getSiteCurrencyId(); } } if ($weight_koef <= 0) { $weight_koef = 1; } global $DB, $DBType; if (function_exists('___dbCastIntToChar') !== true) { eval('function ___dbCastIntToChar($dbtype, $param)' . '{' . ' $result = $param;' . ' if (ToLower($dbtype) === "mssql")' . ' {' . ' $result = "CAST(".$param." AS VARCHAR)";' . ' }' . ' return $result;' . '}'); } $fieldsMap = array('ID' => array('data_type' => 'integer', 'primary' => true), 'TIMESTAMP_X' => array('data_type' => 'integer'), 'DATE_UPDATED' => array('data_type' => 'datetime', 'expression' => array($DB->datetimeToDateFunction('%s'), 'TIMESTAMP_X')), 'QUANTITY' => array('data_type' => 'float'), 'PURCHASING_PRICE' => array('data_type' => 'float'), 'PURCHASING_CURRENCY' => array('data_type' => 'string'), 'IBLOCK' => array('data_type' => 'Bitrix\\Iblock\\Element', 'reference' => array('=this.ID' => 'ref.ID')), 'NAME' => array('data_type' => 'string', 'expression' => array('%s', 'IBLOCK.NAME')), 'NAME_WITH_IDENT' => array('data_type' => 'string', 'expression' => array($DB->concat('%s', '\' [\'', ___dbCastIntToChar($DBType, '%s'), '\']\''), 'NAME', 'ID')), 'ACTIVE' => array('data_type' => 'boolean', 'expression' => array('%s', 'IBLOCK.ACTIVE'), 'values' => array('N', 'Y')), 'WEIGHT' => array('data_type' => 'float'), 'WEIGHT_IN_SITE_UNITS' => array('data_type' => 'float', 'expression' => array('%s / ' . $DB->forSql($weight_koef), 'WEIGHT')), 'PRICE' => array('data_type' => 'float', 'expression' => array('(SELECT b_catalog_price.PRICE FROM b_catalog_price LEFT JOIN b_catalog_group ON b_catalog_group.ID = b_catalog_price.CATALOG_GROUP_ID WHERE b_catalog_price.PRODUCT_ID = %s AND b_catalog_group.base = \'Y\' AND ( b_catalog_price.quantity_from <= 1 OR b_catalog_price.quantity_from IS NULL ) AND ( b_catalog_price.quantity_to >= 1 OR b_catalog_price.quantity_to IS NULL))', 'ID')), 'CURRENCY' => array('data_type' => 'string', 'expression' => array('(SELECT b_catalog_price.CURRENCY FROM b_catalog_price LEFT JOIN b_catalog_group ON b_catalog_group.ID = b_catalog_price.CATALOG_GROUP_ID WHERE b_catalog_price.PRODUCT_ID = %s AND b_catalog_group.base = \'Y\' AND ( b_catalog_price.quantity_from <= 1 OR b_catalog_price.quantity_from IS NULL ) AND ( b_catalog_price.quantity_to >= 1 OR b_catalog_price.quantity_to IS NULL))', 'ID')), 'SUMMARY_PRICE' => array('data_type' => 'float', 'expression' => array('%s * %s', 'QUANTITY', 'PRICE')), 'CURRENT_CURRENCY_RATE' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'CURRENCY')), 'CURRENT_CURRENCY_RATE_CNT' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'CURRENCY')), 'CURRENT_SITE_CURRENCY_RATE' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC) WHERE i = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC', 1) . ')', 'ID')), 'CURRENT_SITE_CURRENCY_RATE_CNT' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC) WHERE i = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC', 1) . ')', 'ID')), 'PURCHASING_CURRENCY_RATE' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'PURCHASING_CURRENCY')), 'PURCHASING_CURRENCY_RATE_CNT' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'PURCHASING_CURRENCY')), 'PRICE_IN_SITE_CURRENCY' => array('data_type' => 'float', 'expression' => array('%s * (%s * %s / %s / %s)', 'PRICE', 'CURRENT_CURRENCY_RATE', 'CURRENT_SITE_CURRENCY_RATE_CNT', 'CURRENT_SITE_CURRENCY_RATE', 'CURRENT_CURRENCY_RATE_CNT')), 'PURCHASING_PRICE_IN_SITE_CURRENCY' => array('data_type' => 'float', 'expression' => array('%s * (%s * %s / %s / %s)', 'PURCHASING_PRICE', 'PURCHASING_CURRENCY_RATE', 'CURRENT_SITE_CURRENCY_RATE_CNT', 'CURRENT_SITE_CURRENCY_RATE', 'PURCHASING_CURRENCY_RATE_CNT')), 'SUMMARY_PRICE_IN_SITE_CURRENCY' => array('data_type' => 'float', 'expression' => array('%s * (%s * %s / %s / %s)', 'SUMMARY_PRICE', 'CURRENT_CURRENCY_RATE', 'CURRENT_SITE_CURRENCY_RATE_CNT', 'CURRENT_SITE_CURRENCY_RATE', 'CURRENT_CURRENCY_RATE_CNT')), 'VIEWS_IN_PERIOD_BY_SHOP' => 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 %%RT_TIME_INTERVAL%% AND b_sale_viewed_product.LID %%RT_SITE_FILTER%%)', 'ID')), 'ORDERS_IN_PERIOD_BY_SHOP' => 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 %%RT_TIME_INTERVAL%% AND b_sale_basket.LID %%RT_SITE_FILTER%%)', 'ID')), 'SALED_PRODUCTS_IN_PERIOD_BY_SHOP' => 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 %%RT_TIME_INTERVAL%% AND b_sale_basket.LID %%RT_SITE_FILTER%%)', 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 %%RT_TIME_INTERVAL%% AND b_catalog_docs_element.ELEMENT_ID = %s)', 0), 'ID', 'ID')), 'ARRIVED_PRODUCTS_IN_PERIOD_BY_SHOP' => 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 %%RT_TIME_INTERVAL%% AND b_catalog_docs_element.ELEMENT_ID = %s)', 'ID')), 'CONVERSION' => 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'))); return $fieldsMap; }
public static function getMap() { // Get weight factor $siteId = ''; $weight_koef = 0; $site_currency = ''; if (class_exists('\\CBaseSaleReportHelper')) { if (\CBaseSaleReportHelper::isInitialized()) { $siteId = \CBaseSaleReportHelper::getDefaultSiteId(); if ($siteId !== null) { $weight_koef = intval(\CBaseSaleReportHelper::getDefaultSiteWeightDivider()); } // Get site currency $site_currency = \CBaseSaleReportHelper::getSiteCurrencyId(); } } if ($weight_koef <= 0) { $weight_koef = 1; } global $DB, $DBType; if (function_exists('___dbCastIntToChar') !== true) { eval('function ___dbCastIntToChar($dbtype, $param)' . '{' . ' $result = $param;' . ' if (ToLower($dbtype) === "mssql")' . ' {' . ' $result = "CAST(".$param." AS VARCHAR)";' . ' }' . ' return $result;' . '}'); } $fieldsMap = array('ID' => array('data_type' => 'integer', 'primary' => true), 'TIMESTAMP_X' => array('data_type' => 'integer'), 'DATE_UPDATED' => array('data_type' => 'datetime', 'expression' => array($DB->datetimeToDateFunction('%s'), 'TIMESTAMP_X')), 'QUANTITY' => array('data_type' => 'float'), 'PURCHASING_PRICE' => array('data_type' => 'float'), 'PURCHASING_CURRENCY' => array('data_type' => 'string'), 'IBLOCK' => array('data_type' => 'Bitrix\\Iblock\\Element', 'reference' => array('=this.ID' => 'ref.ID')), 'NAME' => array('data_type' => 'string', 'expression' => array('%s', 'IBLOCK.NAME')), 'NAME_WITH_IDENT' => array('data_type' => 'string', 'expression' => array($DB->concat('%s', '\' [\'', ___dbCastIntToChar($DBType, '%s'), '\']\''), 'NAME', 'ID')), 'ACTIVE' => array('data_type' => 'boolean', 'expression' => array('%s', 'IBLOCK.ACTIVE'), 'values' => array('N', 'Y')), 'WEIGHT' => array('data_type' => 'float'), 'WEIGHT_IN_SITE_UNITS' => array('data_type' => 'float', 'expression' => array('%s / ' . $DB->forSql($weight_koef), 'WEIGHT')), 'PRICE' => array('data_type' => 'float', 'expression' => array('(SELECT b_catalog_price.PRICE FROM b_catalog_price LEFT JOIN b_catalog_group ON b_catalog_group.ID = b_catalog_price.CATALOG_GROUP_ID WHERE b_catalog_price.PRODUCT_ID = %s AND b_catalog_group.base = \'Y\' AND ( b_catalog_price.quantity_from <= 1 OR b_catalog_price.quantity_from IS NULL ) AND ( b_catalog_price.quantity_to >= 1 OR b_catalog_price.quantity_to IS NULL))', 'ID')), 'CURRENCY' => array('data_type' => 'string', 'expression' => array('(SELECT b_catalog_price.CURRENCY FROM b_catalog_price LEFT JOIN b_catalog_group ON b_catalog_group.ID = b_catalog_price.CATALOG_GROUP_ID WHERE b_catalog_price.PRODUCT_ID = %s AND b_catalog_group.base = \'Y\' AND ( b_catalog_price.quantity_from <= 1 OR b_catalog_price.quantity_from IS NULL ) AND ( b_catalog_price.quantity_to >= 1 OR b_catalog_price.quantity_to IS NULL))', 'ID')), 'SUMMARY_PRICE' => array('data_type' => 'float', 'expression' => array('%s * %s', 'QUANTITY', 'PRICE')), 'CURRENT_CURRENCY_RATE' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'CURRENCY')), 'CURRENT_CURRENCY_RATE_CNT' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'CURRENCY')), 'CURRENT_SITE_CURRENCY_RATE' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC) WHERE i = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC', 1) . ')', 'ID')), 'CURRENT_SITE_CURRENCY_RATE_CNT' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC) WHERE i = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = \'' . $DB->forSql($site_currency) . '\' ORDER BY DATE_RATE DESC', 1) . ')', 'ID')), 'PURCHASING_CURRENCY_RATE' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE IS NOT NULL THEN b_catalog_currency_rate.RATE ELSE b_catalog_currency.AMOUNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'PURCHASING_CURRENCY')), 'PURCHASING_CURRENCY_RATE_CNT' => array('data_type' => 'float', 'expression' => array($DBType === 'oracle' ? '(SELECT r FROM (SELECT b_catalog_currency.CURRENCY c, b_catalog_product.ID i, (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) r FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') ORDER BY DATE_RATE DESC) WHERE i = %s AND c = %s AND ROWNUM = 1)' : '(' . $DB->topSql('SELECT (CASE WHEN b_catalog_currency_rate.RATE_CNT IS NOT NULL THEN b_catalog_currency_rate.RATE_CNT ELSE b_catalog_currency.AMOUNT_CNT END) FROM b_catalog_product INNER JOIN b_catalog_currency ON 1=1 LEFT JOIN b_catalog_currency_rate ON (b_catalog_currency.CURRENCY = b_catalog_currency_rate.CURRENCY AND b_catalog_currency_rate.DATE_RATE <= ' . $DB->datetimeToDateFunction('b_catalog_product.TIMESTAMP_X') . ') WHERE b_catalog_product.ID = %s AND b_catalog_currency.CURRENCY = %s ORDER BY DATE_RATE DESC', 1) . ')', 'ID', 'PURCHASING_CURRENCY')), 'PRICE_IN_SITE_CURRENCY' => array('data_type' => 'float', 'expression' => array('%s * (%s * %s / %s / %s)', 'PRICE', 'CURRENT_CURRENCY_RATE', 'CURRENT_SITE_CURRENCY_RATE_CNT', 'CURRENT_SITE_CURRENCY_RATE', 'CURRENT_CURRENCY_RATE_CNT')), 'PURCHASING_PRICE_IN_SITE_CURRENCY' => array('data_type' => 'float', 'expression' => array('%s * (%s * %s / %s / %s)', 'PURCHASING_PRICE', 'PURCHASING_CURRENCY_RATE', 'CURRENT_SITE_CURRENCY_RATE_CNT', 'CURRENT_SITE_CURRENCY_RATE', 'PURCHASING_CURRENCY_RATE_CNT')), 'SUMMARY_PRICE_IN_SITE_CURRENCY' => array('data_type' => 'float', 'expression' => array('%s * (%s * %s / %s / %s)', 'SUMMARY_PRICE', 'CURRENT_CURRENCY_RATE', 'CURRENT_SITE_CURRENCY_RATE_CNT', 'CURRENT_SITE_CURRENCY_RATE', 'CURRENT_CURRENCY_RATE_CNT'))); return $fieldsMap; }
} } } } </script> </span> <span name="report-filter-value-control-StoreProduct:SALE_PRODUCT"> <select class="report-filter-select" name="value" tid="Section" multiple="multiple" size="5"> <option value="" selected="selected"><?php echo GetMessage('REPORT_IGNORE_FILTER_VALUE'); ?> </option> <?php foreach (CBaseSaleReportHelper::getProductStores() as $k => $v) { ?> <option value="<?php echo htmlspecialcharsbx($k); ?> "><?php echo htmlspecialcharsbx($v); ?> </option> <?php } ?> </select> </span> <style type="text/css">