API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 20th, 2017
236
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 59.79 KB | None | 0 0
raw download clone embed print report
  1. <?
  2.  
  3. if (!defined("B_PROLOG_INCLUDED") || B_PROLOG_INCLUDED!==true)die();
  4.  
  5. global $USER_FIELD_MANAGER;
  6.  
  7. $requiredModules = array('report', 'studiobit.alliance');
  8.  
  9. foreach ($requiredModules as $requiredModule)
  10. {
  11. if (!CModule::IncludeModule($requiredModule))
  12. {
  13. ShowError(GetMessage("F_NO_MODULE"));
  14. return 0;
  15. }
  16. }
  17.  
  18. // Suppress the timezone, while report works in server time
  19. CTimeZone::Disable();
  20.  
  21. use Bitrix\Main\Entity;
  22.  
  23. // <editor-fold defaultstate="collapsed" desc="period types">
  24. $periodTypes =
  25. $arResult['periodTypes'] = array(
  26. 'month',
  27. 'month_ago',
  28. 'week',
  29. 'week_ago',
  30. 'days',
  31. 'after',
  32. 'before',
  33. 'interval',
  34. 'all'
  35. );
  36. // </editor-fold>
  37.  
  38. if(!isset($arParams["SAVE_FILE_NAME"])) $arParams["SAVE_FILE_NAME"] = "report.xlsx";
  39.  
  40. // <editor-fold defaultstate="collapsed" desc="chart types">
  41. if ($arParams['USE_CHART'])
  42. {
  43. $arResult['chartTypes'] = array(
  44. array('id' => 'line', 'name' => GetMessage('REPORT_CHART_TYPE_LINE'), 'value_types' => array(
  45. /*'boolean', 'date', 'datetime', */
  46. 'float', 'integer'/*, 'string', 'text', 'enum', 'file', 'disk_file', 'employee', 'crm', 'crm_status',
  47. 'iblock_element', 'iblock_section'*/
  48. )),
  49. array('id' => 'bar', 'name' => GetMessage('REPORT_CHART_TYPE_BAR'), 'value_types' => array(
  50. /*'boolean', 'date', 'datetime', */
  51. 'float', 'integer'/*, 'string', 'text', 'enum', 'file', 'disk_file', 'employee', 'crm', 'crm_status',
  52. 'iblock_element', 'iblock_section'*/
  53. )),
  54. array('id' => 'pie', 'name' => GetMessage('REPORT_CHART_TYPE_PIE'), 'value_types' => array(
  55. /*'boolean', 'date', 'datetime', */
  56. 'float', 'integer'/*, 'string', 'text', 'enum', 'file', 'disk_file', 'employee', 'crm', 'crm_status',
  57. 'iblock_element', 'iblock_section'*/
  58. ))
  59. );
  60. }
  61. // </editor-fold>
  62.  
  63. // get view params
  64. $strReportViewParams = CReport::getViewParams($arParams['REPORT_ID'], $this->GetTemplateName());
  65. if (isset($_GET['set_filter']))
  66. {
  67. if (substr($_SERVER['QUERY_STRING'], 0, 6) !== 'EXCEL=')
  68. {
  69. if ($_SERVER['QUERY_STRING'] !== $strReportViewParams)
  70. {
  71. CReport::setViewParams($arParams['REPORT_ID'], $this->GetTemplateName(), $_SERVER['QUERY_STRING']);
  72. }
  73. }
  74. }
  75. else
  76. {
  77. if (!empty($strReportViewParams))
  78. {
  79. if (!is_set($_GET['sort_id']))
  80. {
  81. $len = strpos($arParams['PATH_TO_REPORT_VIEW'], '?');
  82.  
  83. if ($len === false) $redirectUrl = $arParams['PATH_TO_REPORT_VIEW'];
  84. else $redirectUrl = substr($arParams['PATH_TO_REPORT_VIEW'], 0, $len);
  85. $redirectUrl = CComponentEngine::makePathFromTemplate($redirectUrl, array('report_id' => $arParams['REPORT_ID']));
  86. $redirectUrl .= '?'.$strReportViewParams;
  87. LocalRedirect($redirectUrl);
  88. }
  89. else
  90. {
  91. CReport::clearViewParams($arParams['REPORT_ID']);
  92. }
  93. }
  94. }
  95.  
  96. try
  97. {
  98. // select report info/settings
  99. $report = array();
  100. $result = false;
  101. if (intval($arParams['REPORT_ID']) > 0)
  102. {
  103. $result = Bitrix\Report\ReportTable::getById($arParams['REPORT_ID']);
  104. }
  105. if (is_object($result))
  106. {
  107. $report = $result->fetch();
  108. }
  109.  
  110. if (empty($report))
  111. {
  112. throw new BXUserException(sprintf(GetMessage('REPORT_NOT_FOUND'), $arParams['REPORT_ID']));
  113. }
  114.  
  115. if ($report['CREATED_BY'] != $USER->GetID())
  116. {
  117. throw new BXUserException(GetMessage('REPORT_VIEW_PERMISSION_DENIED'));
  118. }
  119.  
  120. $arResult['MARK_DEFAULT'] = 0;
  121. if (isset($report['MARK_DEFAULT']))
  122. {
  123. $arResult['MARK_DEFAULT'] = intval($report['MARK_DEFAULT']);
  124. }
  125.  
  126. // action
  127. $settings = unserialize($report['SETTINGS']);
  128.  
  129. // <editor-fold defaultstate="collapsed" desc="parse period">
  130. $date_from = $date_to = null;
  131. $form_date = array('from' => null, 'to' => null, 'days' => null);
  132.  
  133. // <editor-fold defaultstate="collapsed" desc="get value from POST or DB">
  134. if (!empty($_GET['F_DATE_TYPE']) && in_array($_GET['F_DATE_TYPE'], $periodTypes, true))
  135. {
  136. $period = array('type' => $_GET['F_DATE_TYPE']);
  137.  
  138. switch ($_GET['F_DATE_TYPE'])
  139. {
  140. case 'days':
  141. $days = !empty($_GET['F_DATE_DAYS']) ? (int) $_GET['F_DATE_DAYS'] : 1;
  142. $period['value'] = $days ? $days : 1;
  143. break;
  144.  
  145. case 'after':
  146. $date = !empty($_GET['F_DATE_TO']) ? (string) $_GET['F_DATE_TO'] : ConvertTimeStamp(false, 'SHORT');
  147. $date = MakeTimeStamp($date);
  148. $period['value'] = $date ? $date : time();
  149. break;
  150.  
  151. case 'before':
  152. $date = !empty($_GET['F_DATE_FROM']) ? (string) $_GET['F_DATE_FROM'] : ConvertTimeStamp(false, 'SHORT');
  153. $date = MakeTimeStamp($date);
  154. $period['value'] = $date ? $date + (3600*24-1) : time() + (3600*24-1);
  155. break;
  156.  
  157. case 'interval':
  158. $date_f = !empty($_GET['F_DATE_FROM']) ? (string) $_GET['F_DATE_FROM'] : ConvertTimeStamp(false, 'SHORT');
  159. $date_f = MakeTimeStamp($date_f);
  160. $date_t = !empty($_GET['F_DATE_TO']) ? (string) $_GET['F_DATE_TO'] : ConvertTimeStamp(false, 'SHORT');
  161. $date_t = MakeTimeStamp($date_t);
  162. if ($date_f || $date_t)
  163. {
  164. $period['value'][0] = $date_f ? $date_f : time();
  165. $period['value'][1] = $date_t ? $date_t + (3600*24-1) : time() + (3600*24-1);
  166. }
  167. break;
  168.  
  169. default:
  170. $period['value'] = null;
  171. }
  172. }
  173. else
  174. {
  175. $period = $settings['period'];
  176. }
  177. // </editor-fold>
  178.  
  179. // <editor-fold defaultstate="collapsed" desc="parse period">
  180. switch ($period['type'])
  181. {
  182. case 'month':
  183. $date_from = strtotime(date("Y-m-01"));
  184. break;
  185.  
  186. case 'month_ago':
  187. $date_from = strtotime(date("Y-m-01", strtotime("-1 month")));
  188. $date_to = strtotime(date("Y-m-t", strtotime("-1 month"))) + (3600*24-1);
  189. break;
  190.  
  191. case 'week':
  192. $date_from = strtotime("-".((date("w") == 0 ? 7 : date("w")) - 1)." day 00:00");
  193. break;
  194.  
  195. case 'week_ago':
  196. $date_from = strtotime("-".((date("w") == 0 ? 7 : date("w")) + 6)." day 00:00");
  197. $date_to = strtotime("-".(date("w") == 0 ? 7 : date("w"))." day 23:59:59");
  198. break;
  199.  
  200. case 'days':
  201. $date_from = strtotime(date("Y-m-d")." -".intval($period['value'])." day");
  202. $form_date['days'] = intval($period['value']);
  203. break;
  204.  
  205. case 'after':
  206. $date_from = $period['value'];
  207. $form_date['to'] = ConvertTimeStamp($period['value'], 'SHORT');
  208. break;
  209.  
  210. case 'before':
  211. $date_to = $period['value'];
  212. $form_date['from'] = ConvertTimeStamp($period['value'], 'SHORT');
  213. break;
  214.  
  215. case 'interval':
  216. list($date_from, $date_to) = $period['value'];
  217. $form_date['from'] = ConvertTimeStamp($period['value'][0], 'SHORT');
  218. $form_date['to'] = ConvertTimeStamp($period['value'][1], 'SHORT');
  219. break;
  220. }
  221.  
  222. $site_date_from = !is_null($date_from) ? ConvertTimeStamp($date_from, 'FULL') : null;
  223. $site_date_to = !is_null($date_to) ? ConvertTimeStamp($date_to, 'FULL') : null;
  224.  
  225. // to_date for oracle
  226. // rewrite to CDatabase::CharToDateFunction
  227. global $DB;
  228.  
  229. $db_date_from = !is_null($site_date_from) ? $DB->CharToDateFunction($site_date_from) : null;
  230. $db_date_to = !is_null($site_date_to) ? $DB->CharToDateFunction($site_date_to) : null;
  231.  
  232. // user name format
  233. if (isset($arParams['USER_NAME_FORMAT'])
  234. && is_string($arParams['USER_NAME_FORMAT'])
  235. && !empty($arParams['USER_NAME_FORMAT']))
  236. {
  237. call_user_func(
  238. array($arParams['REPORT_HELPER_CLASS'], 'setUserNameFormat'),
  239. $arParams['USER_NAME_FORMAT']
  240. );
  241. }
  242.  
  243. // period filter
  244. $filter = array('LOGIC' => 'AND');
  245. $period_filter = call_user_func(
  246. array($arParams['REPORT_HELPER_CLASS'], 'getPeriodFilter'),
  247. $site_date_from, $site_date_to
  248. );
  249.  
  250. if (!empty($period_filter))
  251. {
  252. $filter[] = $period_filter;
  253. }
  254.  
  255. // preiod option
  256. if (!is_null($date_from) && !is_null($date_to))
  257. {
  258. $sqlTimeInterval = "BETWEEN ".$db_date_from." AND ".$db_date_to;
  259. }
  260. else if (!is_null($date_from))
  261. {
  262. $sqlTimeInterval = "> ".$db_date_from;
  263. }
  264. else if (!is_null($date_to))
  265. {
  266. $sqlTimeInterval = "< ".$db_date_to;
  267. }
  268. else
  269. {
  270. $sqlTimeInterval = " IS NOT NULL";
  271. }
  272. // </editor-fold>
  273.  
  274. // </editor-fold>
  275.  
  276. $runtime = array();
  277. $select = array();
  278. $group = array();
  279. $order = array();
  280. $limit = array();
  281.  
  282. $options = array(
  283. 'SQL_TIME_INTERVAL' => $sqlTimeInterval
  284. );
  285.  
  286. $excelView = isset($_GET["EXCEL"]) && $_GET["EXCEL"] == "Y";
  287.  
  288. // <editor-fold defaultstate="collapsed" desc="parse entity">
  289. $entityName = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getEntityName'));
  290. $entityFields = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getColumnList'));
  291. $grcFields = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getGrcColumns'));
  292. //$arUFInfo = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getUFInfo'));
  293. $arUFEnumerations = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getUFEnumerations'));
  294.  
  295. // customize entity
  296. $entity = clone Entity\Base::getInstance($entityName);
  297. call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'setRuntimeFields'), $entity, $sqlTimeInterval);
  298.  
  299. $chains = CReport::generateChains($entityFields, $entity, '');
  300. $fieldsTree = CReport::generateColumnTree($chains, $entity, $arParams['REPORT_HELPER_CLASS']);
  301. unset($chains);
  302.  
  303. // custom columns types
  304. $customColumnTypes = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getCustomColumnTypes'));
  305. if (!is_array($customColumnTypes))
  306. $customColumnTypes = array();
  307. // </editor-fold>
  308.  
  309. // <editor-fold defaultstate="collapsed" desc="parse select columns">
  310.  
  311. // <editor-fold defaultstate="collapsed" desc="collect fields">
  312. $fList = array();
  313. $fChainList = array();
  314. $bGroupingMode = false;
  315. foreach ($settings['select'] as $elem)
  316. {
  317. if (!$bGroupingMode) if ($elem['grouping'] === true) $bGroupingMode = true;
  318. $fName = $elem['name'];
  319.  
  320. if (array_key_exists($fName, $fList))
  321. {
  322. continue;
  323. }
  324.  
  325. try
  326. {
  327. $chain = Entity\QueryChain::getChainByDefinition($entity, $fName);
  328. }
  329. catch (Exception $e)
  330. {
  331. if ($e->getCode() == 100)
  332. throw new BXUserException('<p style="color: red;">'.GetMessage('REPORT_UNKNOWN_FIELD_DEFINITION').'</p>');
  333. else
  334. throw $e;
  335. }
  336. $fList[$fName] = $chain->getLastElement()->getValue();
  337. if (is_array($fList[$fName])) $fList[$fName] = end($fList[$fName]);
  338. $fChainList[$fName] = $chain;
  339. }
  340.  
  341. // customize select fields
  342. $customSelectFields = call_user_func_array(
  343. array($arParams['REPORT_HELPER_CLASS'], 'getCustomSelectFields'),
  344. array($settings['select'], $fList)
  345. );
  346. if (is_array($customSelectFields) && !empty($customSelectFields))
  347. {
  348. $customSelectKeys = array_keys($customSelectFields);
  349. $newSelect = array();
  350. foreach ($settings['select'] as $k => $elem)
  351. {
  352. if (in_array($k, $customSelectKeys, true))
  353. {
  354. $fName = $customSelectFields[$k]['name'];
  355.  
  356. if (array_key_exists($fName, $fList))
  357. continue;
  358.  
  359. try
  360. {
  361. $chain = Entity\QueryChain::getChainByDefinition($entity, $fName);
  362. }
  363. catch (Exception $e)
  364. {
  365. if ($e->getCode() == 100)
  366. throw new BXUserException('<p style="color: red;">'.GetMessage('REPORT_UNKNOWN_FIELD_DEFINITION').'</p>');
  367. else
  368. throw $e;
  369. }
  370. $fList[$fName] = $chain->getLastElement()->getValue();
  371. if (is_array($fList[$fName])) $fList[$fName] = end($fList[$fName]);
  372. $fChainList[$fName] = $chain;
  373. $newSelect[$k] = $customSelectFields[$k];
  374. }
  375. else
  376. {
  377. $newSelect[$k] = $elem;
  378. }
  379. }
  380. $settings['select'] = $newSelect;
  381. unset($customSelectKeys, $newSelect);
  382. }
  383. unset($customSelectFields);
  384.  
  385. // </editor-fold>
  386.  
  387. // <editor-fold defaultstate="collapsed" desc="collect hrefs' fields">
  388. //$settings['select'][0]['href'] = array(
  389. // 'pattern' => '/company/personal/user/#RESPONSIBLE_ID#/tasks/task/view/#ID#/', //'/tasks/#ID#/',
  390. // /*'elements' => array( // not required
  391. // 'ID' => array(
  392. // 'name' => 'ID',
  393. // 'aggr' => null
  394. // )
  395. // )*/
  396. //);
  397.  
  398. foreach ($settings['select'] as &$elem)
  399. {
  400. //if (in_array($elem['name'], $grcFields, true) && empty($elem['aggr']))
  401. if ($elem['aggr'] == 'GROUP_CONCAT')
  402. {
  403. continue;
  404. }
  405.  
  406. CReport::appendHrefSelectElements($elem, $fList, $entity, $arParams['REPORT_HELPER_CLASS'], $select, $runtime);
  407. }
  408. unset($elem);
  409. // </editor-fold>
  410.  
  411. // <editor-fold defaultstate="collapsed" desc="collect columns with aliases, build runtime fields">
  412.  
  413. // if there is aggr of init entity or there is no init entity at all, then we think that 1:N need double aggregation
  414. $is_init_entity_aggregated = false;
  415. $is_init_entity_in_select = false;
  416.  
  417. foreach ($settings['select'] as $num => $elem)
  418. {
  419. $chain = $fChainList[$elem['name']];
  420. if ($chain->getSize() == 2)
  421. {
  422. $is_init_entity_in_select = true;
  423.  
  424. if (!empty($elem['aggr']))
  425. {
  426. $is_init_entity_aggregated = true;
  427. break;
  428. }
  429. }
  430. }
  431.  
  432. if (!$is_init_entity_aggregated && !$is_init_entity_in_select)
  433. {
  434. $is_init_entity_aggregated = true;
  435. }
  436.  
  437.  
  438. // init variables
  439. $viewColumns = array();
  440. $viewColumnsByResultName = array();
  441.  
  442. // blacklist of entity with aggr
  443. $aggr_bl = array();
  444.  
  445. // grc stuff
  446. $grcSelectPrimaries = array();
  447. $grcInitPrimary = false;
  448.  
  449. $need_concat_rows = false;
  450. $grcSettingsNum = array();
  451.  
  452. foreach ($settings['select'] as $num => $elem)
  453. {
  454. /** @var Entity\Field $field */
  455. $chain = $fChainList[$elem['name']];
  456. $field = $fList[$elem['name']];
  457. $fType = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getFieldDataType'), $field);
  458.  
  459. $is_grc = false;
  460.  
  461. //if (in_array($elem['name'], $grcFields, true) && empty($elem['aggr']) && !strlen($elem['prcnt']))
  462. if ($elem['aggr'] == 'GROUP_CONCAT')
  463. {
  464. $is_grc = true;
  465.  
  466. // collect grc_fields pointers
  467. $need_concat_rows = true;
  468. $grcSettingsNum[] = $num;
  469. }
  470.  
  471. list($alias, $selElem) = CReport::prepareSelectViewElement($elem, $settings['select'], $is_init_entity_aggregated, $fList, $fChainList, $arParams['REPORT_HELPER_CLASS'], $entity);
  472.  
  473. if (is_array($selElem) && !empty($selElem['expression']))
  474. {
  475. // runtime expr
  476. $fType = $selElem['data_type'];
  477. }
  478. else
  479. {
  480. // normal field
  481. $alias = Entity\QueryChain::getAliasByDefinition($entity, $elem['name']);
  482. }
  483.  
  484. if (!$is_grc)
  485. {
  486. // grc will be selected later
  487. if (is_array($selElem))
  488. {
  489. // runtime field
  490. $select[$alias] = $alias;
  491. $runtime[$alias] = $selElem;
  492. }
  493. else
  494. {
  495. $select[$alias] = $selElem;
  496. }
  497. }
  498.  
  499. // default sort
  500. if ($is_grc
  501. || ((in_array($fType, array('file', 'disk_file', 'employee', 'crm', 'crm_status', 'iblock_element',
  502. 'iblock_section'), true)
  503. || ($arUF['isUF'] && $arUF['ufInfo']['MULTIPLE'] === 'Y'))
  504. && empty($elem['aggr'])))
  505. {
  506. $defaultSort = '';
  507. }
  508. else if ($num == $settings['sort'] && array_key_exists('sort_type', $settings))
  509. {
  510. $defaultSort = $settings['sort_type'];
  511. }
  512. else if (($fType === 'string' || $fType === 'enum') && empty($elem['aggr']))
  513. {
  514. $defaultSort = 'ASC';
  515. }
  516. else
  517. {
  518. $defaultSort = 'DESC';
  519. }
  520.  
  521. $arUF = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'detectUserField'), $field);
  522. $viewColumns[$num] = array(
  523. 'field' => $field,
  524. 'fieldName' => $elem['name'],
  525. 'resultName' => $alias,
  526. 'humanTitle' => empty($elem['alias']) ? $alias : $elem['alias'],
  527. 'defaultSort' => $defaultSort,
  528. 'aggr' => empty($elem['aggr']) ? '' : $elem['aggr'],
  529. 'prcnt' => strlen($elem['prcnt']) ? $elem['prcnt'] : '',
  530. 'href' => empty($elem['href']) ? '' : $elem['href'],
  531. 'grouping' => ($elem['grouping'] === true) ? true : false,
  532. 'grouping_subtotal' => ($elem['grouping_subtotal'] === true) ? true : false,
  533. 'isUF' => $arUF['isUF'],
  534. 'ufInfo' => $arUF['ufInfo']
  535. );
  536. unset($arUF);
  537.  
  538. $viewColumnsByResultName[$alias] = &$viewColumns[$num];
  539.  
  540. // blacklist of entity with aggr
  541. //if (!in_array($elem['name'], $grcFields, true) && !empty($elem['aggr']))
  542. if ($elem['aggr'] != 'GROUP_CONCAT' && !empty($elem['aggr']))
  543. {
  544. $preDef = substr($elem['name'], 0, strrpos($elem['name'], '.'));
  545. $preDef = strlen($preDef) ? $preDef.'.' : '';
  546.  
  547. $aggr_bl[$preDef] = true;
  548. }
  549. }
  550.  
  551. // collect entity primaries of fields without aggregation
  552. foreach ($settings['select'] as $num => $elem)
  553. {
  554. //if (!in_array($elem['name'], $grcFields, true) && empty($elem['aggr']))
  555. if (empty($elem['aggr']))
  556. {
  557. $primary = $viewColumns[$num]['field']->getEntity()->getPrimaryArray();
  558.  
  559. $preDef = substr($elem['name'], 0, strrpos($elem['name'], '.'));
  560. $preDef = strlen($preDef) ? $preDef.'.' : '';
  561.  
  562. if (array_key_exists($preDef, $aggr_bl))
  563. {
  564. continue;
  565. }
  566.  
  567. foreach ($primary as $pField)
  568. {
  569. $palias = Entity\QueryChain::getAliasByDefinition($entity, $preDef.$pField);
  570. $grcSelectPrimaries[$palias] = $preDef.$pField;
  571. }
  572.  
  573. // remember if there is initEntity primary in data
  574. if ($viewColumns[$num]['field']->getEntity() === $entity)
  575. {
  576. $grcInitPrimary = true;
  577. }
  578. }
  579. }
  580.  
  581. // normalize $grcSelectPrimaries
  582. if ($grcInitPrimary)
  583. {
  584. // it's enough only init primary
  585. $initPrimary = $entity->getPrimaryArray();
  586.  
  587. foreach ($grcSelectPrimaries as $k => $v)
  588. {
  589. if (!in_array($v, $initPrimary, true))
  590. {
  591. unset($grcSelectPrimaries[$k]);
  592. }
  593. }
  594. }
  595.  
  596. $select = array_merge($select, $grcSelectPrimaries);
  597. // </editor-fold>
  598.  
  599. // </editor-fold>
  600.  
  601. // <editor-fold defaultstate="collapsed" desc="parse filter">
  602.  
  603. // <editor-fold defaultstate="collapsed" desc="rewrite values by filter">
  604. $isFieldResponsibleUser = false;
  605. $countNumberElementFilter = 1;
  606.  
  607. if(substr_count($settings['entity'], "Lead") > 0){
  608. $nameFieldsResponsibly = LEAD_PROP_RESPONSIBLE_USER;
  609. }
  610. else if(substr_count($settings['entity'], "Company") > 0){
  611. $nameFieldsResponsibly = COMPANY_PROP_RESPONSIBLE_USER;
  612. }
  613. else if(substr_count($settings['entity'], "Contact") > 0){
  614. $nameFieldsResponsibly = CONTACT_PROP_RESPONSIBLE_USER;
  615. }
  616. else if(substr_count($settings['entity'], "Deal") > 0){
  617. $nameFieldsResponsibly = DEAL_PROP_RESPONSIBLE_USER;
  618. }
  619. else if(substr_count($settings['entity'], "Activity") > 0){
  620. $nameFieldsResponsibly = "RESPONSIBLE_ID";
  621. }
  622. else if(substr_count($settings['entity'], "Tasks") > 0){
  623. $nameFieldsResponsibly = "TASK";
  624. $isFieldResponsibleUser = true;
  625. }
  626. else{
  627. $nameFieldsResponsibly = "TASK";
  628. $isFieldResponsibleUser = true;
  629. }
  630.  
  631. foreach ($settings['filter'] as $fId => &$fInfo)
  632. {
  633. foreach ($fInfo as $k => &$fElem)
  634. {
  635. if (!empty($_GET['filter'][$fId]) && array_key_exists($k, $_GET['filter'][$fId]))
  636. {
  637. $fElem['value'] = $_GET['filter'][$fId][$k];
  638. }
  639.  
  640. if(($fElem['name'] == $nameFieldsResponsibly) && ($arParams["OWNER_ID"] == "crm")){
  641. $isFieldResponsibleUser = true;
  642. }
  643. }
  644. }
  645.  
  646. if(!$isFieldResponsibleUser){
  647. // текущий департамент
  648. $arDepartament = CAllianceCommon::DepartmentOfCurrentUser($USER->GetID());
  649.  
  650. if(CAllianceCommon::isUserOfGroup(GROUP_USER_ADMIN) || CAllianceCommon::isUserOfGroup(GROUP_USER_BUSINESS_ADMIN) || CAllianceCommon::isUserOfGroup(GROUP_USER_DIRECTION) || (CAllianceCommon::isUserOfGroup(GROUP_USER_ANALITIC_MASTER))){ // если админ или руководство
  651. $departmentID = 0;
  652. }
  653. else{
  654. // текущий департамент
  655. $arDepartament = CAllianceCommon::DepartmentOfCurrentUser($USER->GetID());
  656. if(in_array(GROUP_USER_SUPPORT, $USER->GetUserGroupArray()) || in_array(GROUP_USER_ANALITIC, $USER->GetUserGroupArray())){
  657. $departmentID = $arDepartament[0];
  658. }
  659. else{
  660. $departmentID = 1111111;
  661. }
  662. }
  663.  
  664. // все подразделы текущего департамента
  665. if($departmentID == 0){
  666. $arDepartamentOpend = array();
  667. $rsSect = CIBlockSection::GetList(array('left_margin' => 'asc'), array('IBLOCK_ID' => IBLOCK_DEPARTMENT, 'DEPTH_LEVEL' => 1));
  668. while ($arSect = $rsSect->GetNext()){
  669. $arDepartamentOpend = array_merge($arDepartamentOpend, CAllianceCommon::GetListSectionChild($arSect["ID"]));
  670. }
  671. }
  672. else{
  673. if(count($arDepartament) > 1){
  674. $arDepartamentOpend = array();
  675. foreach($arDepartament as $id){
  676. $arDepartamentOpend = array_merge($arDepartamentOpend, CAllianceCommon::GetListSectionChild($id));
  677. }
  678. }
  679. else
  680. $arDepartamentOpend = CAllianceCommon::GetListSectionChild($departmentID);
  681. }
  682.  
  683. // список пользователей котрые будут задействованы в фильтре
  684. $arUserListIDOpenedUser = array();
  685.  
  686. // если есть фильтр по подразделениям тогда нужно опрделить подразделы выбранного раздела
  687. if(IntVal($arResult["other_filter"]["department"]) > 0){
  688. $arListSelectedSection = CAllianceCommon::GetListSectionChild($arResult["other_filter"]["department"]);
  689.  
  690. // удаляем лишние разделы
  691. foreach($arListSelectedSection as $key=>$sectionId){
  692. if(!in_array($sectionId, $arDepartamentOpend)) unset($arListSelectedSection[$key]);
  693. }
  694. }
  695. else{
  696. $arUserListIDOpenedUser[] = $USER->GetID();
  697. $arListSelectedSection = $arDepartamentOpend;
  698. }
  699.  
  700. // список пользователей этих департаментов
  701. foreach($arListSelectedSection as $id_department){
  702. $rsUserListOfDepartament = CUser::GetList($by="ID", $order="desc", array("UF_DEPARTMENT"=>$id_department), array("SELECT"=>array("UF_DEPARTMENT"), "FIELDS"=>array("ID", "NAME", "LAST_NAME", "LOGIN")));
  703. while($arUserList = $rsUserListOfDepartament->GetNext()):
  704. if((IntVal($arResult["other_filter"]["staff"]) > 0) && ($arResult["other_filter"]["staff"] != $arUserList["ID"])){
  705. continue;
  706. }
  707. $arUserListIDOpenedUser[] = $arUserList["ID"];
  708. endwhile;
  709. }
  710.  
  711. if(!CAllianceCommon::isUserOfGroup(GROUP_USER_ADMIN) &&
  712. !CAllianceCommon::isUserOfGroup(GROUP_USER_BUSINESS_ADMIN) &&
  713. !CAllianceCommon::isUserOfGroup(GROUP_USER_DIRECTION) &&
  714. !CAllianceCommon::isUserOfGroup(GROUP_USER_ANALITIC_MASTER) &&
  715. !CAllianceCommon::isUserOfGroup(GROUP_USER_ANALITIC) &&
  716. !CAllianceCommon::isUserOfGroup(GROUP_USER_SUPPORT)
  717. ){
  718. // проверка, руководитель это или нет
  719. $rsSect = CIBlockSection::GetList(array('left_margin' => 'asc'), array('IBLOCK_ID' => IBLOCK_DEPARTMENT, 'UF_HEAD' => $USER->GetID()));
  720. if($arSect = $rsSect->GetNext()){
  721. // все ок, идет дальше
  722. $arUserListIDOpenedUser = CAllianceAccountTeam::getUserIfHead($USER->GetID());
  723. }
  724. else if(CAllianceCommon::isUserOfGroup(GROUP_USER_ANALITIC)){
  725. $arUserListIDOpenedUser = CAllianceAccountTeam::getUserIfHead($USER->GetID());
  726. }
  727. else{
  728. // простой клерк, - значит от видит только свои записи
  729. $arUserListIDOpenedUser = array($USER->GetID());
  730. }
  731. }
  732.  
  733. if(CAllianceCommon::isUserOfGroup(GROUP_USER_ADMIN) || CAllianceCommon::isUserOfGroup(GROUP_USER_BUSINESS_ADMIN) || CAllianceCommon::isUserOfGroup(GROUP_USER_DIRECTION) || (CAllianceCommon::isUserOfGroup(GROUP_USER_ANALITIC_MASTER))){ // если админ или руководство
  734. $arUserListIDOpenedUser = array();
  735. }
  736.  
  737. $arUserListIDOpenedUser = array_unique($arUserListIDOpenedUser);
  738. trace($arUserListIDOpenedUser);
  739.  
  740. $arListFilterResponsibly = array();
  741. foreach($arUserListIDOpenedUser as $userResp){
  742. if($arParams["OWNER_ID"] == "crm_activity"){
  743. $arListFilterResponsibly[] = array(
  744. '=RESPONSIBLE_ID' => $userResp
  745. );
  746. }
  747. else{
  748. $arListFilterResponsibly[] = array(
  749. '='.DEAL_PROP_OWNER => $userResp
  750. );
  751. $arListFilterResponsibly[] = array(
  752. '='.DEAL_PROP_SELLER => $userResp
  753. );
  754. }
  755. }
  756. $arListFilterResponsibly["LOGIC"] = "OR";
  757.  
  758. $countNumberElementFilter = count($settings['filter']);
  759. if(count($arUserListIDOpenedUser) > 0){
  760. $settings['filter'][$countNumberElementFilter] = $arListFilterResponsibly;
  761. }
  762. }
  763.  
  764. #trace($settings['filter']);
  765. unset($fInfo);
  766. unset($fElem);
  767. // </editor-fold>
  768.  
  769. // <editor-fold defaultstate="collapsed" desc="add filter to fList and fChainList">
  770. foreach ($settings['filter'] as $fId => $fInfo)
  771. {
  772. foreach ($fInfo as $k => $fElem)
  773. {
  774. if (is_array($fElem) && $fElem['type'] == 'field')
  775. {
  776. if (preg_match('/__COLUMN__\d+/', $fElem['name']))
  777. {
  778. continue;
  779. }
  780.  
  781. try
  782. {
  783. $chain = Entity\QueryChain::getChainByDefinition($entity, $fElem['name']);
  784. }
  785. catch (Exception $e)
  786. {
  787. if ($e->getCode() == 100)
  788. throw new BXUserException('<p style="color: red;">'.GetMessage('REPORT_UNKNOWN_FIELD_DEFINITION').'</p>');
  789. else
  790. throw $e;
  791. }
  792. $field = $chain->getLastElement()->getValue();
  793. if (is_array($field)) $field = end($field);
  794. $fList[$fElem['name']] = $field;
  795. $fChainList[$fElem['name']] = $chain;
  796. }
  797. }
  798. }
  799. // </editor-fold>
  800.  
  801. #p($settings['filter']);
  802. // <editor-fold defaultstate="collapsed" desc="collect changeables">
  803. $changeableFilters = array();
  804. $changeableFiltersEntities = array();
  805. foreach ($settings['filter'] as $fId => &$fInfo)
  806. {
  807. foreach ($fInfo as $k => &$fElem)
  808. {
  809. if (is_array($fElem) && $fElem['type'] == 'field' && (int) $fElem['changeable'] > 0)
  810. {
  811. $match = array();
  812. if (preg_match('/__COLUMN__(\d+)/', $fElem['name'], $match))
  813. {
  814. /** @var Entity\Field[] $view */
  815. $num = $match[1];
  816. $view = $viewColumns[$num];
  817. $data_type = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getFieldDataType'), $view['field']);
  818.  
  819. if ($view['prcnt'])
  820. {
  821. $data_type = 'float';
  822. }
  823. else if ($view['aggr'] == 'COUNT_DISTINCT')
  824. {
  825. $data_type = 'integer';
  826. }
  827.  
  828. $field = null;
  829. }
  830. else
  831. {
  832. $field = $fList[$fElem['name']];
  833. $data_type = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getFieldDataType'), $field);
  834. }
  835.  
  836. if ($field instanceof Entity\ReferenceField)
  837. {
  838. $tmpElem = $fElem;
  839. call_user_func_array(
  840. array($arParams['REPORT_HELPER_CLASS'], 'fillFilterReferenceColumn'),
  841. array(&$tmpElem, &$field)
  842. );
  843. $value = $tmpElem['value'];
  844. $changeableFiltersEntities[$field->getRefEntityName()] = true;
  845. }
  846. else
  847. {
  848. $arUF = call_user_func_array(
  849. array($arParams['REPORT_HELPER_CLASS'], 'detectUserField'),
  850. array($field)
  851. );
  852. if ($arUF['isUF'] && is_array($arUF['ufInfo']) && isset($arUF['ufInfo']['USER_TYPE_ID']))
  853. {
  854. $tmpElem = $fElem;
  855. call_user_func_array(
  856. array($arParams['REPORT_HELPER_CLASS'], 'fillFilterUFColumn'),
  857. array(&$tmpElem, $field, $arUF['ufInfo'])
  858. );
  859. $value = $tmpElem['value'];
  860. }
  861. else
  862. {
  863. $value = $fElem['value'];
  864. }
  865. }
  866.  
  867. // detect UF
  868. $arUF = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'detectUserField'), $field);
  869.  
  870. $changeableFilters[] = array(
  871. 'name' => $fElem['name'],
  872. 'title' => '', // will be added later
  873. 'value' => $value,
  874. 'compare' => $fElem['compare'],
  875. 'filter' => $fId,
  876. 'num' => $k,
  877. 'formName' => 'filter['.$fId.']['.$k.']',
  878. 'formId' => 'filter_'.$fId.'_'.$k,
  879. 'field' => $field,
  880. 'data_type' => $data_type,
  881. 'isUF' => $arUF['isUF'],
  882. 'ufId' => $arUF['isUF'] ? $arUF['ufInfo']['ENTITY_ID'] : '',
  883. 'ufName' => $arUF['isUF'] ? $arUF['ufInfo']['FIELD_NAME'] : ''
  884. );
  885.  
  886. unset($arUF);
  887. }
  888. }
  889. }
  890. unset($fInfo);
  891. unset($fElem);
  892. // </editor-fold>
  893.  
  894. // <editor-fold defaultstate="collapsed" desc="rewrite references to primary">
  895. foreach ($settings['filter'] as $fId => &$fInfo)
  896. {
  897. foreach ($fInfo as $k => &$fElem)
  898. {
  899. if (is_array($fElem) && $fElem['type'] == 'field')
  900. {
  901. // delete empty filters
  902. if (is_array($fElem['value']))
  903. {
  904. foreach ($fElem['value'] as $l => $value)
  905. {
  906. if ($value === '' || !is_numeric($l)) unset($fElem['value'][$l]);
  907. }
  908. $l = count($fElem['value']);
  909. if ($l === 0) $fElem['value'] = '';
  910. else if ($l === 1) $fElem['value'] = $fElem['value'][0];
  911. }
  912.  
  913. if (preg_match('/__COLUMN__(\d+)/', $fElem['name'], $match))
  914. {
  915. $num = $match[1];
  916. $field = $viewColumns[$num]['field'];
  917. }
  918. else
  919. {
  920. $field = $fList[$fElem['name']];
  921. }
  922.  
  923. // rewrite
  924. if ($field instanceof Entity\ReferenceField)
  925. {
  926. // get primary
  927. $field = $field->GetRefEntity()->getField('ID');
  928.  
  929. // get primary filter field name
  930. $primaryFilterField = call_user_func_array(
  931. array($arParams['REPORT_HELPER_CLASS'], 'getEntityFilterPrimaryFieldName'),
  932. array($fElem)
  933. );
  934. $fElem['name'] .= '.'.$primaryFilterField;
  935. unset($primaryFilterField);
  936.  
  937. $fList[$fElem['name']] = $field;
  938. $fChainList[$fElem['name']] = Entity\QueryChain::getChainByDefinition($entity, $fElem['name']);
  939. }
  940.  
  941. $dataType = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getFieldDataType'), $field);
  942.  
  943. if (($dataType != 'crm') && ($fElem['compare'] != "заполнено" && $fElem['compare'] != "пусто"))
  944. {
  945. if ($fElem['value'] === '')
  946. {
  947. unset($fInfo[$k]);
  948. continue;
  949. }
  950. }
  951.  
  952. // rewrite date <=> {today, yesterday, tomorrow, etc}
  953. if ($dataType === 'datetime'
  954. && !CheckDateTime($fElem['value'], CSite::GetDateFormat('SHORT'))
  955. )
  956. {
  957. $fElem['value'] = ConvertTimeStamp(strtotime($fElem['value']), 'SHORT');
  958. }
  959.  
  960. // rewrite date=DAY to date BETWEEN DAY_START AND DAY_END
  961. if ($dataType === 'datetime')
  962. {
  963. if ($fElem['compare'] == 'EQUAL')
  964. {
  965. // clone filter
  966. $fElem_start = $fElem;
  967. $fElem_end = $fElem;
  968.  
  969. $fElem_start['compare'] = 'GREATER_OR_EQUAL';
  970. $fElem_start['value'] .= ' 00:00:00';
  971.  
  972. $fElem_end['compare'] = 'LESS_OR_EQUAL';
  973. $fElem_end['value'] .= ' 23:59:59';
  974.  
  975. // replace filter by subfilter
  976. $settings['filter'][] = array('LOGIC' => 'AND', $fElem_start, $fElem_end);
  977. end($settings['filter']);
  978. $lastFilterNum = key($settings['filter']);
  979.  
  980. $fElem = array('type' => 'filter', 'name' => $lastFilterNum);
  981. }
  982. else if ($fElem['compare'] == 'NOT_EQUAL')
  983. {
  984. // clone filter
  985. $fElem_start = $fElem;
  986. $fElem_end = $fElem;
  987.  
  988. $fElem_start['compare'] = 'LESS';
  989. $fElem_start['value'] .= ' 00:00:00';
  990.  
  991. $fElem_end['compare'] = 'GREATER';
  992. $fElem_end['value'] .= ' 23:59:59';
  993.  
  994. // replace filter by subfilter
  995. $settings['filter'][] = array('LOGIC' => 'AND', $fElem_start, $fElem_end);
  996. end($settings['filter']);
  997. $lastFilterNum = key($settings['filter']);
  998.  
  999. $fElem = array('type' => 'filter', 'name' => $lastFilterNum);
  1000. }
  1001. else if ($fElem['compare'] == 'LESS_OR_EQUAL')
  1002. {
  1003. $fElem['value'] .= ' 23:59:59';
  1004. }
  1005. else if ($fElem['compare'] == 'GREATER_OR_EQUAL')
  1006. {
  1007. $fElem['value'] .= ' 00:00:00';
  1008. }
  1009. else if ($fElem['compare'] == 'LESS')
  1010. {
  1011. $fElem['value'] .= ' 00:00:00';
  1012. }
  1013. else if ($fElem['compare'] == 'GREATER')
  1014. {
  1015. $fElem['value'] .= ' 23:59:59';
  1016. }
  1017. }
  1018. }
  1019. }
  1020. }
  1021. unset($fInfo);
  1022. unset($fElem);
  1023. // </editor-fold>
  1024.  
  1025.  
  1026. #p($settings['filter']);
  1027. // <editor-fold defaultstate="collapsed" desc="rewrite 1:N relations to EXISTS expression">
  1028. call_user_func_array(
  1029. array($arParams['REPORT_HELPER_CLASS'], 'beforeFilterBackReferenceRewrite'),
  1030. array(&$settings['filter'], $viewColumns)
  1031. );
  1032.  
  1033. $f_filter_alias_count = 0;
  1034.  
  1035. foreach ($settings['filter'] as $fId => &$fInfo)
  1036. {
  1037. foreach ($fInfo as $k => &$fElem)
  1038. {
  1039. if (is_array($fElem) && $fElem['type'] == 'field')
  1040. {
  1041. if (preg_match('/__COLUMN__\d+/', $fElem['name']))
  1042. {
  1043. continue;
  1044. }
  1045.  
  1046. $fField = $fList[$fElem['name']];
  1047. $arUF = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'detectUserField'), $fField);
  1048. if ($arUF['isUF'])
  1049. {
  1050. $fFieldDataType = call_user_func(
  1051. array($arParams['REPORT_HELPER_CLASS'], 'getUserFieldDataType'), $arUF
  1052. );
  1053. if ($fFieldDataType === 'boolean')
  1054. {
  1055. if ($fElem['value'] === 'true')
  1056. $fElem['value'] = 1;
  1057. else
  1058. $fElem['value'] = 0;
  1059. }
  1060. }
  1061.  
  1062. $chain = $fChainList[$fElem['name']];
  1063.  
  1064. if ($chain->hasBackReference())
  1065. {
  1066. $confirm = call_user_func_array(
  1067. array($arParams['REPORT_HELPER_CLASS'], 'confirmFilterBackReferenceRewrite'),
  1068. array(&$fElem, $chain)
  1069. );
  1070.  
  1071. if (!$confirm)
  1072. {
  1073. continue;
  1074. }
  1075.  
  1076. $_sub_init_table_alias = ToLower($entity->getCode());
  1077.  
  1078. $_sub_filter = array();
  1079.  
  1080. // add primary linking with main query
  1081. foreach ($entity->GetPrimaryArray() as $_primary)
  1082. {
  1083. $_sub_filter['='.$_primary] = new CSQLWhereExpression('?#', $_sub_init_table_alias.'.'.$_primary);
  1084. }
  1085.  
  1086. // add value filter
  1087. $arCompareVariations = CAllianceReportDefault::$iBlockCompareVariations;
  1088.  
  1089. $filterCompare = $arCompareVariations[$fElem['compare']];
  1090.  
  1091. $filterName = $fElem['name'];
  1092. $filterValue = $fElem['value'];
  1093. if ($filterCompare === '>%')
  1094. {
  1095. $filterCompare = '';
  1096. $filterValue = $filterValue.'%';
  1097. }
  1098.  
  1099. $_sub_filter[$filterCompare.$filterName] = $filterValue;
  1100.  
  1101. // build subquery
  1102. $_sub_query = new Entity\Query($entity);
  1103. $_sub_query->setFilter($_sub_filter);
  1104. $_sub_query->setTableAliasPostfix('_sub');
  1105.  
  1106. $_sub_sql = 'EXISTS('.$_sub_query->getQuery().')';
  1107. $_sub_sql = '(CASE WHEN '.$_sub_sql.' THEN 1 ELSE 0 END)';
  1108.  
  1109. // expression escaping as sprintf requires
  1110. $_sub_sql = str_replace('%', '%%', $_sub_sql);
  1111.  
  1112. $_runtime_field = array(
  1113. 'data_type' => 'integer',
  1114. 'expression' => array($_sub_sql)
  1115. );
  1116.  
  1117. $f_filter_alias = 'F_FILTER_ALIAS_'.(++$f_filter_alias_count);
  1118.  
  1119. $runtime[$f_filter_alias] = $_runtime_field;
  1120. $fElem['name'] = $f_filter_alias;
  1121. $fElem['compare'] = 'EQUAL';
  1122. $fElem['value'] = 1;
  1123. }
  1124. }
  1125. }
  1126. }
  1127. unset($fInfo);
  1128. unset($fElem);
  1129. // </editor-fold>
  1130.  
  1131. // <editor-fold defaultstate="collapsed" desc="rewrite __COLUMN__\d filters">
  1132. foreach ($settings['filter'] as $fId => &$fInfo)
  1133. {
  1134. foreach ($fInfo as $k => &$fElem)
  1135. {
  1136. if (is_array($fElem) && $fElem['type'] == 'field')
  1137. {
  1138. if (preg_match('/__COLUMN__(\d+)/', $fElem['name'], $match))
  1139. {
  1140. $num = $match[1];
  1141. $view = $viewColumns[$num];
  1142.  
  1143. if (!empty($view['prcnt']) || !empty($view['aggr']))
  1144. {
  1145. $fElem['name'] = $view['resultName'];
  1146. }
  1147. else
  1148. {
  1149. $fElem['name'] = $view['fieldName'];
  1150. }
  1151. }
  1152. }
  1153. }
  1154. }
  1155. // </editor-fold>
  1156. #p($settings['filter']);
  1157.  
  1158. $iFilter = CAllianceReportDefault::makeSingleFilter($settings['filter']);
  1159. if(!$isFieldResponsibleUser){
  1160. $iFilter[] = $settings['filter'][$countNumberElementFilter];
  1161. }
  1162. #trace($iFilter);
  1163. $filter[] = $iFilter;
  1164. // </editor-fold>
  1165.  
  1166. // <editor-fold defaultstate="collapsed" desc="parse sort">
  1167. $sort_id = $settings['sort'];
  1168. $sort_name = $viewColumns[$sort_id]['resultName'];
  1169. $sort_type = $viewColumns[$sort_id]['defaultSort'];
  1170.  
  1171. // rewrite sort by POST
  1172. if (array_key_exists('sort_id', $_GET) && array_key_exists($_GET['sort_id'], $viewColumns))
  1173. {
  1174. $sort_id = $_GET['sort_id'];
  1175. $sort_name = $viewColumns[$sort_id]['resultName'];
  1176.  
  1177. if ($_GET['sort_type'] === 'ASC' || $_GET['sort_type'] === 'DESC')
  1178. {
  1179. $sort_type = $_GET['sort_type'];
  1180. }
  1181. else
  1182. {
  1183. $sort_type = $viewColumns[$sort_id]['defaultSort'];
  1184. }
  1185. }
  1186.  
  1187. if ($sort_name != '' && ($sort_type === 'ASC' || $sort_type === 'DESC'))
  1188. $order = array($sort_name => $sort_type);
  1189. // </editor-fold>
  1190.  
  1191. // <editor-fold defaultstate="collapsed" desc="parse limit">
  1192. if (!$bGroupingMode) // no limit in grouping mode
  1193. {
  1194. $limit['nPageSize'] = $arParams['ROWS_PER_PAGE'];
  1195.  
  1196. if (!empty($settings['limit']))
  1197. {
  1198. $limit['nPageTop'] = $settings['limit'];
  1199. }
  1200. else if (!$excelView)
  1201. {
  1202. $limit['iNumPage'] = is_set($_GET['PAGEN_1']) ? $_GET['PAGEN_1'] : 1;
  1203. $limit['bShowAll'] = true;
  1204. }
  1205. }
  1206.  
  1207. if($excelView){
  1208. $limit['nPageTop'] = 1000000;
  1209. $limit['bShowAll'] = true;
  1210. }
  1211. // </editor-fold>
  1212.  
  1213.  
  1214. // <editor-fold defaultstate="collapsed" desc="connect Lang">
  1215. $fullHumanTitles = CReport::collectFullHumanTitles($fieldsTree);
  1216.  
  1217. foreach ($viewColumns as $num => &$view)
  1218. {
  1219. if ($view['resultName'] == $view['humanTitle'])
  1220. {
  1221. $view['humanTitle'] = CReport::getFullColumnTitle($view, $viewColumns, $fullHumanTitles);
  1222. }
  1223. }
  1224. unset($view);
  1225.  
  1226. foreach ($changeableFilters as &$chFilter)
  1227. {
  1228. if (preg_match('/__COLUMN__(\d+)/', $chFilter['name'], $match))
  1229. {
  1230. $num = $match[1];
  1231. $chFilter['title'] = $viewColumns[$num]['humanTitle'];
  1232. }
  1233. else
  1234. {
  1235. $chFilter['title'] = $fullHumanTitles[$chFilter['name']];
  1236. }
  1237.  
  1238. }
  1239. unset($chFilter);
  1240. // </editor-fold>
  1241.  
  1242. // rewrite User SHORT_NAME
  1243. CReport::rewriteUserShortName($select, $runtime, $arParams['USER_NAME_FORMAT'], $entity);
  1244.  
  1245. // <editor-fold defaultstate="collapsed" desc="retrieve report rows">
  1246.  
  1247. call_user_func_array(
  1248. array($arParams['REPORT_HELPER_CLASS'], 'beforeViewDataQuery'),
  1249. array(&$select, &$filter, &$group, &$order, &$limit, &$options, &$runtime)
  1250. );
  1251.  
  1252. // отчет по задачам
  1253. if($arParams['REPORT_HELPER_CLASS'] == "CTasksReportHelper"){
  1254. $select["PARENT_ID"] = "PARENT_ID";
  1255. $select["ADD_IN_REPORT"] = "ADD_IN_REPORT";
  1256. #echo '<pre>'.print_r($select, true).'</pre>';
  1257. }
  1258.  
  1259. $main_query = new Entity\Query($entity);
  1260. $main_query->setSelect($select)
  1261. ->setFilter($filter)
  1262. ->setGroup($group)
  1263. ->setOrder($order)
  1264. ;
  1265.  
  1266. foreach ($runtime as $k => $v)
  1267. {
  1268. $main_query->registerRuntimeField($k, $v);
  1269.  
  1270. // add view column if needed
  1271. if (isset($v['view_column']) && is_array($v['view_column']))
  1272. {
  1273. $runtimeColumnInfo = $v['view_column'];
  1274. $newNum = max(array_keys($viewColumns)) + 1;
  1275. $queryChains = $main_query->getChains();
  1276. if (isset($queryChains[$k]))
  1277. {
  1278. $runtimeField = $queryChains[$k]->getLastElement()->getValue();
  1279. if (is_array($runtimeField)) $runtimeField = end($runtimeField);
  1280. /*$arUF = CReport::detectUserField($runtimeField, $arUFInfo);*/
  1281. $viewColumns[$newNum] = array(
  1282. 'field' => $runtimeField,
  1283. 'fieldName' => $k,
  1284. 'resultName' => $k,
  1285. 'humanTitle' => empty($runtimeColumnInfo['humanTitle']) ? '' : $runtimeColumnInfo['humanTitle'],
  1286. 'defaultSort' => '',
  1287. 'aggr' => '',
  1288. 'prcnt' => '',
  1289. 'href' => empty($runtimeColumnInfo['href']) ? '' : $runtimeColumnInfo['href'],
  1290. 'grouping' => false,
  1291. 'grouping_subtotal' => ($runtimeColumnInfo['grouping_subtotal'] === true) ? true : false,
  1292. 'runtime' => true/*,
  1293. 'isUF' => $arUF['isUF'],
  1294. 'isUF' => $arUF['isUF'],
  1295. 'ufInfo' => $arUF['ufInfo']*/
  1296. );
  1297. /*unset($arUF);*/
  1298. $viewColumnsByResultName[$k] = &$viewColumns[$newNum];
  1299. }
  1300. }
  1301. }
  1302.  
  1303. if (isset($limit['nPageTop']))
  1304. $main_query->setLimit($limit['nPageTop']);
  1305.  
  1306. $result = $main_query->exec();
  1307.  
  1308. $result = new CDBResult($result);
  1309. if (!$bGroupingMode)
  1310. {
  1311. if (isset($limit['nPageTop']))
  1312. $result->NavStart($limit['nPageTop']);
  1313. else
  1314. $result->NavStart($limit['nPageSize']/*, true, $limit['iNumPage']*/);
  1315. }
  1316.  
  1317. $data = array();
  1318. $grcDataPrimaryValues = array();
  1319. $grcDataPrimaryPointers = array();
  1320.  
  1321. while ($row = $result->Fetch())
  1322. {
  1323. // rewrite UF values
  1324. call_user_func_array(
  1325. array($arParams['REPORT_HELPER_CLASS'], 'rewriteResultRowValues'),
  1326. array(&$row, &$viewColumnsByResultName)
  1327. );
  1328.  
  1329. // attach URLs
  1330. foreach ($row as $k => $v)
  1331. {
  1332. if (!array_key_exists($k, $viewColumnsByResultName))
  1333. {
  1334. continue;
  1335. }
  1336.  
  1337. $elem = $viewColumnsByResultName[$k];
  1338.  
  1339. if (!empty($elem['href']))
  1340. {
  1341. $url = CReport::generateValueUrl($elem, $row, $entity);
  1342. $row['__HREF_'.$k] = $url;
  1343. }
  1344. }
  1345.  
  1346. $row['TITLE'] = htmlspecialchars_decode($row['TITLE']);
  1347. #echo '<pre>'.print_r($row, true).'</pre>';
  1348.  
  1349. // collect
  1350. $data[] = $row;
  1351.  
  1352. // grc stuff
  1353. $grc_primary_string = '';
  1354.  
  1355. foreach ($grcSelectPrimaries as $alias => $def)
  1356. {
  1357. // for grc filter
  1358. $grcDataPrimaryValues['='.$def][] = $row[$alias];
  1359.  
  1360. // for concat
  1361. $grc_primary_string .= (string) $row[$alias] . '/';
  1362. }
  1363.  
  1364. // save original data indexes for grc values
  1365. if (!isset($grcDataPrimaryPointers[$grc_primary_string]))
  1366. {
  1367. $grcDataPrimaryPointers[$grc_primary_string] = array();
  1368. }
  1369.  
  1370. $grcDataPrimaryPointers[$grc_primary_string][] = count($data)-1;
  1371. }
  1372.  
  1373. $grcDataPrimaryValues = array_map('array_unique', $grcDataPrimaryValues);
  1374. // </editor-fold>
  1375.  
  1376. if (empty($settings['limit']))
  1377. {
  1378. $arResult["NAV_STRING"] = $result->GetPageNavString('', (is_set($arParams['NAV_TEMPLATE'])) ? $arParams['NAV_TEMPLATE'] : 'arrows');
  1379. $arResult["NAV_PARAMS"] = $result->GetNavParams();
  1380. $arResult["NAV_NUM"] = $result->NavNum;
  1381. }
  1382.  
  1383. // если это последняя страница
  1384. if(substr_count($arResult["NAV_STRING"], 'id="navigation_1_next_page') == 0){
  1385.  
  1386. // сделать выборку параметров фильтрации по компаниям
  1387. $arFilterCompany = array();
  1388. if(substr_count($settings['entity'], "Deal") > 0){
  1389. $arFilterCompany = CAllianceReport::GetFilterCompany($filter);
  1390. $arSelectCompany = CAllianceReport::GetSelectCompany($select);
  1391. if(count($arFilterCompany) > 0){
  1392. $dataCompany = CAllianceReport::GetDataCompanyNotQuotation($arFilterCompany, $arSelectCompany, $select);
  1393.  
  1394. if(count($data) < 10){
  1395. $data = array_merge($data, $dataCompany);
  1396. }
  1397. }
  1398. }
  1399. }
  1400.  
  1401. // <editor-fold defaultstate="collapsed" desc="retrieve total counts">
  1402. $totalSelect = $select;
  1403. $totalColumns = array();
  1404.  
  1405. foreach ($viewColumns as $num => $view)
  1406. {
  1407. // total's fields are the same as percentable fields
  1408. // they are also all numerics
  1409. if (CReport::isColumnTotalCountable($view, $arParams['REPORT_HELPER_CLASS']))
  1410. {
  1411. // exclude from select all except those
  1412. $totalColumns[$view['resultName']] = true;
  1413. }
  1414. }
  1415.  
  1416. // save only totalCountable visible fields
  1417. foreach ($totalSelect as $k => $v)
  1418. {
  1419. if (!array_key_exists($k, $totalColumns))
  1420. {
  1421. unset($totalSelect[$k]);
  1422. }
  1423. }
  1424.  
  1425. // add SUM aggr
  1426. $_totalSelect = $totalSelect;
  1427. $totalSelect = array();
  1428.  
  1429. foreach ($_totalSelect as $k => $v)
  1430. {
  1431. $totalSelect[] = new Entity\ExpressionField('TOTAL_'.$k, 'SUM(%s)', $k);
  1432. }
  1433.  
  1434. if (!empty($totalSelect))
  1435. {
  1436. // source query
  1437. $query_from = new Entity\Query($entity);
  1438. $query_from->setSelect($select);
  1439. $query_from->setFilter($filter);
  1440. $query_from->setGroup($group);
  1441.  
  1442. foreach ($runtime as $k => $v)
  1443. {
  1444. $query_from->registerRuntimeField($k, $v);
  1445. }
  1446.  
  1447. // total query
  1448. $total_query = new Entity\Query($query_from);
  1449. $total_query->setSelect($totalSelect);
  1450.  
  1451. $result = $total_query->exec();
  1452. $total = $result->fetch();
  1453. $total = ($total === false) ? array() : $total;
  1454. }
  1455. else
  1456. {
  1457. $total = array();
  1458. }
  1459.  
  1460. // </editor-fold>
  1461.  
  1462. // <editor-fold defaultstate="collapsed" desc="group_concat fields">
  1463. $grcData = array();
  1464.  
  1465. // check necessity of concat rows
  1466. if ($need_concat_rows && $grcDataPrimaryValues)
  1467. {
  1468. // filter - add primaries from data
  1469. if ($grcInitPrimary)
  1470. {
  1471. // init primary enough
  1472. $grcFilter = $grcDataPrimaryValues;
  1473. }
  1474. else
  1475. {
  1476. // merge with primaries
  1477. $grcFilter = array_merge($filter, $grcDataPrimaryValues);
  1478. }
  1479.  
  1480. // select data for each grc field
  1481. foreach ($grcSettingsNum as $num)
  1482. {
  1483. $elem = $settings['select'][$num];
  1484.  
  1485. // prepare
  1486. $grcSelect = $grcSelectPrimaries;
  1487.  
  1488. CReport::appendHrefSelectElements($elem, $fList, $entity, $arParams['REPORT_HELPER_CLASS'], $grcSelect, $runtime);
  1489.  
  1490. if (!empty($elem['href']))
  1491. {
  1492. $viewColumns[$num]['href'] = $elem['href'];
  1493. }
  1494.  
  1495. list($alias, $selElem) = CReport::prepareSelectViewElement($elem, $settings['select'], $is_init_entity_aggregated, $fList, $fChainList, $arParams['REPORT_HELPER_CLASS'], $entity);
  1496.  
  1497. if (is_array($selElem) && !empty($selElem['expression']))
  1498. {
  1499. $runtime[$alias] = $selElem;
  1500. $grcSelect[] = $alias;
  1501. }
  1502. else
  1503. {
  1504. // normal field
  1505. $alias = Entity\QueryChain::getAliasByDefinition($entity, $elem['name']);
  1506. $grcSelect[$alias] = $selElem;
  1507. }
  1508.  
  1509. CReport::rewriteUserShortName($grcSelect, $runtime, $arParams['USER_NAME_FORMAT'], $entity, true);
  1510.  
  1511. // add primary of grc entity field
  1512. $grcChain = Entity\QueryChain::getChainByDefinition($entity, $elem['name']);
  1513. $grc_field = $grcChain->getLastElement()->getValue();
  1514. if (is_array($grc_field)) $grc_field = end($grc_field);
  1515. $grc_primary = end($grc_field->getEntity()->getPrimaryArray());
  1516. $grc_marker = substr($elem['name'], 0, strrpos($elem['name'], '.')) . '.' . $grc_primary;
  1517. $grc_marker_alias = Entity\QueryChain::getAliasByDefinition($entity, $grc_marker);
  1518.  
  1519. $grcSelect[$grc_marker_alias] = $grc_marker;
  1520.  
  1521. // select
  1522. $resultName = $viewColumns[$num]['resultName'];
  1523. $grcData[$resultName] = array();
  1524.  
  1525. $grc_query = new Entity\Query($entity);
  1526. $grc_query->setSelect($grcSelect);
  1527. $grc_query->setFilter($grcFilter);
  1528.  
  1529. foreach ($runtime as $k => $v)
  1530. {
  1531. $grc_query->registerRuntimeField($k, $v);
  1532. }
  1533.  
  1534. $result = $grc_query->exec();
  1535.  
  1536. while ($row = $result->fetch())
  1537. {
  1538. if (empty($row[$grc_marker_alias]))
  1539. {
  1540. continue;
  1541. }
  1542.  
  1543. $grcData[$resultName][] = $row;
  1544. }
  1545.  
  1546. // add empty values to data
  1547. foreach ($data as $k => $v)
  1548. {
  1549. $data[$k][$alias] = null;
  1550. }
  1551.  
  1552. // add values to data
  1553. foreach ($grcData[$resultName] as $grcIndex => &$row)
  1554. {
  1555. $grc_primary_string = '';
  1556.  
  1557. foreach ($grcSelectPrimaries as $pResultName => $def)
  1558. {
  1559. $grc_primary_string .= (string) $row[$pResultName] . '/';
  1560. }
  1561.  
  1562. $dataIndexes = $grcDataPrimaryPointers[$grc_primary_string];
  1563.  
  1564. foreach ($dataIndexes as $dataIndex)
  1565. {
  1566. if (!isset($data[$dataIndex][$alias]))
  1567. {
  1568. $data[$dataIndex][$alias] = array();
  1569. }
  1570.  
  1571. if (!empty($elem['href']) && strlen($row[$alias]))
  1572. {
  1573. $url = CReport::generateValueUrl($elem, $row, $entity);
  1574. $row['__HREF_'.$alias] = $url;
  1575. }
  1576.  
  1577. $data[$dataIndex][$alias][$grcIndex] = $row[$alias];
  1578. }
  1579. }
  1580. unset($row);
  1581. }
  1582. } // end concat grc
  1583. // </editor-fold>
  1584.  
  1585.  
  1586. // collect UF values
  1587. call_user_func_array(
  1588. array($arParams['REPORT_HELPER_CLASS'], 'collectUFValues'),
  1589. array(&$data, &$viewColumnsByResultName, $total)
  1590. );
  1591.  
  1592. $customChartTotal = $customChartData = array();
  1593. // format results
  1594. call_user_func_array(
  1595. array($arParams['REPORT_HELPER_CLASS'], 'formatResults'),
  1596. array(&$data, &$viewColumnsByResultName, $total, &$customChartData)
  1597. );
  1598.  
  1599. call_user_func_array(
  1600. array($arParams['REPORT_HELPER_CLASS'], 'formatResultsTotal'),
  1601. array(&$total, &$viewColumnsByResultName, &$customChartTotal)
  1602. );
  1603.  
  1604. // отчет по задачам
  1605. if($arParams['REPORT_HELPER_CLASS'] == "CTasksReportHelper"){
  1606. foreach($data as $keyData=>$valueData){
  1607. if(IntVal($valueData["PARENT_ID"]) > 0){
  1608. $data[$keyData]["ID"] = IntVal($valueData["PARENT_ID"]);
  1609. $data[$keyData]["ADD_IN_REPORT"] = "Подзадача";
  1610. }
  1611. else{
  1612. $data[$keyData]["ADD_IN_REPORT"] = "Задача";
  1613. }
  1614. }
  1615. }
  1616.  
  1617. if(substr_count($entityName, 'Activity') > 0){
  1618. $arClient = $arDescription = $arActivityID = array();
  1619. // собираем данные о клиентах и описаниям всех событий
  1620. foreach($data as $keyData=>$valueData){
  1621. $arActivityID[] = $valueData["ID"];
  1622. }
  1623.  
  1624. // выборка данных
  1625. if(count($arActivityID) > 0){
  1626. $rsActivity = CCrmActivity::GetList(array(), array("ID"=>$arActivityID), false, false, array("ID", "SUBJECT", "DESCRIPTION", "OWNER_ID", "OWNER_TYPE_ID"));
  1627. while($arActivity = $rsActivity->Fetch()){
  1628. if($arActivity["OWNER_TYPE_ID"] == CCrmOwnerType::Contact)
  1629. {
  1630. $arOwner = CCrmContact::GetByID($arActivity["OWNER_ID"]);
  1631. $arClient[$arActivity["ID"]] = $arOwner["LAST_NAME"]." ".$arOwner["NAME"];
  1632. }
  1633. elseif($arActivity["OWNER_TYPE_ID"] == CCrmOwnerType::Company)
  1634. {
  1635. $arOwner = CCrmCompany::GetByID($arActivity["OWNER_ID"]);
  1636. $arClient[$arActivity["ID"]] = $arOwner["TITLE"];
  1637. }
  1638. elseif($arActivity["OWNER_TYPE_ID"] == CCrmOwnerType::Lead)
  1639. {
  1640. $arOwner = CCrmLead::GetByID($arActivity["OWNER_ID"]);
  1641. $arClient[$arActivity["ID"]] = $arOwner["LAST_NAME"]." ".$arOwner["NAME"];
  1642. }
  1643. elseif($arActivity["OWNER_TYPE_ID"] == CCrmOwnerType::Deal)
  1644. {
  1645. $rsListDeal = CCrmDeal::GetListEx(array("ID"=>"ASC"), array("ID"=>$arActivity["OWNER_ID"]), false, false, array("ID", "TITLE", "COMPANY_ID", DEAL_PROP_LEAD_ID, DEAL_PROP_NAME_INSURED));
  1646. if($arDeal = $rsListDeal->GetNext()){
  1647. if(IntVal($arDeal[DEAL_PROP_LEAD_ID]) == 0){
  1648. $arOwner = CCrmCompany::GetByID(str_replace("CO_", "", $arDeal[DEAL_PROP_NAME_INSURED]));
  1649. $arClient[$arActivity["ID"]] = $arOwner["TITLE"];
  1650. }
  1651. else{
  1652. $arClient[$arActivity["ID"]] = 'Лид';
  1653. }
  1654. }
  1655. else{
  1656. $arClient[$arActivity["ID"]] = "";
  1657. }
  1658. }
  1659.  
  1660. $arDescription[$arActivity["ID"]] = preg_replace('#\[.+\]#Uis', '', HTMLToTxt(htmlspecialchars_decode($arActivity["DESCRIPTION"])));
  1661. }
  1662. }
  1663.  
  1664. foreach($data as $keyData=>&$valueData){
  1665. $valueData["LOCATION"] = $arClient[$valueData["ID"]]?$arClient[$valueData["ID"]]:"";
  1666. $valueData["DIRECTION"] = $arDescription[$valueData["ID"]]?$arDescription[$valueData["ID"]]:"";
  1667. }
  1668. }
  1669.  
  1670. foreach($data as $keyData=>$valueData){
  1671. if(strlen($valueData["CRM_DEAL_COMPANY_BY_TITLE"]) > 0){
  1672. $arDealInfo = CAllianceCommon::GetUFPropertyDeal($valueData["ID"], array(DEAL_PROP_LEAD_ID));
  1673. $LeadID = $arDealInfo[DEAL_PROP_LEAD_ID];
  1674. if(IntVal($LeadID) > 0){
  1675. $arLeadInfo = CCrmLead::GetByID($LeadID, false);
  1676. $data[$keyData]["CRM_DEAL_COMPANY_BY_TITLE"] = "<a href='/crm/lead/show/".$arLeadInfo["ID"]."/' target='_blank'>".$arLeadInfo["TITLE"]."</a>";
  1677. }
  1678. }
  1679.  
  1680. // вывод данных по лиду
  1681. $arListPropLead = array();
  1682. foreach($valueData as $key=>$value){
  1683. if(substr_count($key, "CRM_DEAL_LEAD_BY_") > 0){
  1684. $arListPropLead[] = str_replace("CRM_DEAL_LEAD_BY_", "", $key);
  1685. }
  1686. }
  1687.  
  1688. if(count($arListPropLead) > 0){
  1689. if(IntVal($LeadID) == 0){
  1690. $arDealInfo = CAllianceCommon::GetUFPropertyDeal($valueData["ID"], array(DEAL_PROP_LEAD_ID));
  1691. $LeadID = $arDealInfo[DEAL_PROP_LEAD_ID];
  1692. }
  1693. $arLeadPropList = CAllianceCommon::GetUFPropertyLead($LeadID, $arListPropLead);
  1694. foreach($arListPropLead as $propName){
  1695. if($propName == "UF_COMPANY_TYPE"){
  1696. $rsEnum = CUserFieldEnum::GetList(array(), array("USER_FIELD_NAME"=>"UF_COMPANY_TYPE", "ID" =>$arLeadPropList[$propName]));
  1697. if($arEnum = $rsEnum->GetNext()){
  1698. $arLeadPropList[$propName] = $arEnum['VALUE'];
  1699. }
  1700. }
  1701. else if($propName == "UF_TYPE"){
  1702. $rsEnum = CUserFieldEnum::GetList(array(), array("USER_FIELD_NAME"=>"UF_TYPE", "ID" =>$arLeadPropList[$propName]));
  1703. if($arEnum = $rsEnum->GetNext()){
  1704. $arLeadPropList[$propName] = $arEnum['VALUE'];
  1705. }
  1706. }
  1707. else if($propName == "UF_NORESIDENT"){
  1708. $rsEnum = CUserFieldEnum::GetList(array(), array("USER_FIELD_NAME"=>"UF_NORESIDENT", "ID" =>$arLeadPropList[$propName]));
  1709. if($arEnum = $rsEnum->GetNext()){
  1710. $arLeadPropList[$propName] = $arEnum['VALUE'];
  1711. }
  1712. }
  1713. else if($propName == LEAD_PROP_INDUSTRY){
  1714. $rsEnum = CIBlockSection::GetByID($arLeadPropList[$propName])->GetNext();
  1715. $arLeadPropList[$propName] = $rsEnum['NAME'];
  1716. }
  1717. else if($propName == LEAD_PROP_POD_INDUSTRY){
  1718. $rsEnum = CIBlockElement::GetByID($arLeadPropList[$propName])->GetNext();
  1719. $arLeadPropList[$propName] = $rsEnum['NAME'];
  1720. }
  1721.  
  1722. $data[$keyData]["CRM_DEAL_LEAD_BY_".$propName] = $arLeadPropList[$propName];
  1723. }
  1724. }
  1725. }
  1726. }
  1727. catch (Exception $e)
  1728. {
  1729. if ($e instanceof BXUserException)
  1730. {
  1731. $arResult['ERROR'] = $e->getMessage();
  1732. }
  1733. else
  1734. {
  1735. CTimeZone::Enable();
  1736. throw $e;
  1737. }
  1738. }
  1739.  
  1740. CTimeZone::Enable();
  1741.  
  1742. if(substr_count($entityName, 'Activity') > 0){
  1743. foreach($viewColumns as &$arItemTree){
  1744. if($arItemTree["fieldName"] == "DIRECTION"){
  1745. $arItemTree["humanTitle"] = $arItemTree["fullHumanTitle"] = "Описание";
  1746. }
  1747.  
  1748. if($arItemTree["fieldName"] == "LOCATION"){
  1749. $arItemTree["humanTitle"] = $arItemTree["fullHumanTitle"] = "Клиент";
  1750. }
  1751. }
  1752. }
  1753.  
  1754. // template vars
  1755. $arResult['entityName'] = $entityName;
  1756. $arResult['helperClassName'] = $arParams['REPORT_HELPER_CLASS'];
  1757. $arResult['fList'] = $fList;
  1758. $arResult['settings'] = $settings;
  1759. $arResult['sort_id'] = $sort_id;
  1760. $arResult['sort_type'] = $sort_type;
  1761. $arResult['report'] = $report;
  1762. $arResult['viewColumns'] = $viewColumns;
  1763. $arResult['data'] = $data;
  1764. $arResult['grcData'] = $grcData;
  1765. $arResult['changeableFilters'] = $changeableFilters;
  1766. $arResult['ufEnumerations'] = $arUFEnumerations;
  1767. $arResult['changeableFiltersEntities'] = $changeableFiltersEntities;
  1768. $arResult['chfilter_examples'] = array();
  1769. $arResult['total'] = $total;
  1770.  
  1771. $arResult['form_date'] = $form_date;
  1772. $arResult['period'] = $period;
  1773.  
  1774. $arResult['groupingMode'] = $bGroupingMode;
  1775.  
  1776. $arResult['customColumnTypes'] = $customColumnTypes;
  1777. $arResult['customChartData'] = $customChartData;
  1778. $arResult['customChartTotal'] = $customChartTotal;
  1779.  
  1780. $arResult['ufInfo'] = call_user_func(array($arParams['REPORT_HELPER_CLASS'], 'getUFInfo'));
  1781.  
  1782.  
  1783. if ($excelView)
  1784. {
  1785. $APPLICATION->RestartBuffer();
  1786.  
  1787. while(ob_get_level()) {
  1788. ob_end_clean();
  1789. }
  1790. ob_start();
  1791.  
  1792. $this->IncludeComponentTemplate('excel_x'); //build HTML
  1793.  
  1794. $report_excel_data = ob_get_contents();
  1795. ob_end_clean();
  1796.  
  1797. if($arParams['CRON'] != "Y"){
  1798. if($report_excel_data) {
  1799. require_once($_SERVER['DOCUMENT_ROOT'].'/local/php_interface/excel_parser/PHPExcel.php');
  1800. require_once($_SERVER['DOCUMENT_ROOT'].'/local/php_interface/excel_parser/PHPExcel/Writer/Excel2007.php');
  1801.  
  1802. $current_micro = mktime();
  1803. $inputFileType = 'HTML';
  1804. $inputFileName = $_SERVER['DOCUMENT_ROOT'].'/upload/tmp/report/report_'.$current_micro.'.html';
  1805. $outputFileType = 'Excel2007';
  1806. //$outputFileName = $_SERVER['DOCUMENT_ROOT']'./upload/tmp/reports/report_'.$current_micro.'.xlsx';
  1807. $outputFileName = 'report_'.$current_micro.'.xlsx';
  1808.  
  1809. if(file_put_contents($inputFileName, $report_excel_data) === false) {
  1810. throw new \Exception('Cannot create file '.$inputFileName.' to save report in XLSX format! Exit.');
  1811. }
  1812. else {
  1813. try {
  1814. $objPHPExcelReader = PHPExcel_IOFactory::createReader($inputFileType);
  1815. $objPHPExcel = $objPHPExcelReader->load($inputFileName);
  1816. $first_row_index = 1;
  1817.  
  1818. $objPHPExcel->getActiveSheet()->removeRow($first_row_index); //hack - because first row always appeared empty!
  1819. $objPHPExcel->getActiveSheet()->getStyle($first_row_index)->getFont()->setBold(true);
  1820. $lastColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
  1821. $lastColumn++;
  1822.  
  1823. //cell styles begin
  1824. $cell_styleArray = array(
  1825. 'font' => array(
  1826. 'bold' => true,
  1827. 'color' => array('rgb' => '000000'),
  1828. 'size' => 8,
  1829. ),
  1830. 'alignment' => array(
  1831. 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  1832. 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
  1833. ),
  1834. );
  1835. $cell_width = 20;
  1836. $row_height = 30;
  1837.  
  1838. $objPHPExcel->getActiveSheet()->getRowDimension($first_row_index)->setRowHeight($row_height); //apply first row styles
  1839. for($column = 'A'; $column != $lastColumn; $column++) {
  1840.  
  1841. $objPHPExcel->getActiveSheet()->getStyle($column.$first_row_index)->applyFromArray($cell_styleArray); //apply first row cell styles
  1842. //$objPHPExcel->getActiveSheet()->getStyle($column.$first_row_index)->getAlignment()->setWrapText(true);
  1843. $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth($cell_width);
  1844. }
  1845.  
  1846. $lastRow = $objPHPExcel->getActiveSheet()->getHighestRow();
  1847.  
  1848. for($column = 'A'; $column != $lastColumn; $column++) {
  1849. for($row = $first_row_index + 1; $row <= $lastRow; $row++) {
  1850. $cell_value = $objPHPExcel->getActiveSheet()->getCell($column.$row)->getValue();
  1851. $cell_value_blanked = trim(preg_replace("/[[:blank:]]+/", "", $cell_value));
  1852. $arValue = explode(".", $cell_value_blanked);
  1853. if(is_numeric($cell_value_blanked)) {
  1854. $objPHPExcel->getActiveSheet()->getCell($column.$row)->setValue($cell_value_blanked);
  1855. $objPHPExcel->getActiveSheet()->getStyle($column.$row)->getNumberFormat()->setFormatCode('_-* # ##0_р_._-;-* # ##0_р_._-;_-* "-"??_р_._-;_-@_-'); //correct formatting of numeric values
  1856. }
  1857. else if(substr_count($cell_value_blanked, "href") > 0){
  1858. $objPHPExcel->getActiveSheet()->getStyle($column.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
  1859. }
  1860. else if(count($arValue) == 3){ // date
  1861. $objPHPExcel->getActiveSheet()->getStyle($column.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);
  1862. }
  1863. }
  1864. }
  1865.  
  1866. $objPHPExcel->getActiveSheet()->getStyle('A1:'.$lastColumn.$lastRow)->applyFromArray(array(
  1867. 'borders' => array(
  1868. 'allborders' => array(
  1869. 'style' => PHPExcel_Style_Border::BORDER_THIN
  1870. )
  1871. )
  1872. )); //set borders to all cells
  1873.  
  1874. unlink($inputFileName); //remove source file
  1875.  
  1876. header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  1877. header("Content-Disposition: attachment;filename=$outputFileName");
  1878. header("Cache-Control: max-age=0");
  1879.  
  1880. $objPHPExcelWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFileType);
  1881. $objPHPExcel = $objPHPExcelWriter->save("php://output");
  1882.  
  1883. } catch (Exception $e) {
  1884. Header("Content-Type: application/force-download");
  1885. Header("Content-Type: application/octet-stream");
  1886. Header("Content-Type: application/download");
  1887. Header("Content-Disposition: attachment;filename=report.xls");
  1888. Header("Content-Transfer-Encoding: binary");
  1889. echo $report_excel_data;
  1890. }
  1891. }
  1892. }
  1893. }
  1894. else{
  1895. echo 'OK';
  1896. }
  1897.  
  1898. exit;
  1899. }
  1900. else
  1901. {
  1902. $this->IncludeComponentTemplate();
  1903. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!