<?
if(!defined("B_PROLOG_INCLUDED") || B_PROLOG_INCLUDED!==true)die();
use \Bitrix\Iblock\PropertyEnumerationTable;
use Bitrix\Main\Grid\Options as GridOptions;
use Bitrix\Main\UI\PageNavigation;
if(!CModule::IncludeModule("crm")) {
ShowError("CRM_MODULE_NOT_INSTALLED");
return false;
}
$arResult = [];
$arResult['LEADS'] = [];
$list_id = 'report_list';
$arResult['list_id'] = $list_id;
$grid_options = new GridOptions($list_id);
$sort = $grid_options->GetSorting(['sort' => ['ID' => 'DESC'], 'vars' => ['by' => 'by', 'order' => 'order']]);
$nav_params = $grid_options->GetNavParams();
$nav = new PageNavigation('request_list');
$nav->allowAllRecords(true)
->setPageSize($nav_params['nPageSize'])
->initFromUri();
$arResult['nav'] = $nav;
$filterOption = new Bitrix\Main\UI\Filter\Options($list_id);
$filterData = $filterOption->getFilter([]);
$filter = [];
foreach ($filterData as $k => $v) {
// Тут разбор массива $filterData из формата, в котором его формирует main.ui.filter в формат, который подойдет для вашей выборки.
// Обратите внимание на поле "FIND", скорее всего его вы и захотите засунуть в фильтр по NAME и еще паре полей
$filter['NAME'] = "%".$filterData['FIND']."%";
}
// SOURCE_ID = SOURCE_ID
// DEAL_TYPE = UF_CRM_1603300557
// ANALYTICS = ?
// SUBDIVISION = UF_CRM_1602060930
// CHANNEL_EVENT = UF_CRM_1632338031
// REPEAT = IS_RETURN_CUSTOMER
// STATUS_ID = STATUS_ID
// DATE_CREATE_from = ">=DATE_CREATE"
// DATE_CREATE_to = "<DATE_CREATE"
// GROUPING_1/2/3 настраивается конекретно перед GRID'ом
$arFilter = array();
if($filterData['SOURCE_ID'] != null) {
$arFilter['SOURCE_ID'] = $filterData['SOURCE_ID'];
}
if($filterData['DEAL_TYPE'] != null) {
$arFilter['UF_CRM_1603300557'] = $filterData['DEAL_TYPE'];
}
if($filterData['SUBDIVISION'] != null) {
$arFilter['UF_CRM_1602060930'] = $filterData['SUBDIVISION'];
}
if($filterData['CHANNEL_EVENT'] != null) {
$arFilter['UF_CRM_1632338031'] = $filterData['CHANNEL_EVENT'];
}
if($filterData['REPEAT'] != null) {
$arFilter['IS_RETURN_CUSTOMER'] = $filterData['REPEAT'];
}
if($filterData['REPEAT'] != null) {
$arFilter['IS_RETURN_CUSTOMER'] = $filterData['REPEAT'];
}
if($filterData['STATUS_ID'] != null) {
$arFilter['STATUS_ID'] = $filterData['STATUS_ID'];
}
if($filterData["DATE_CREATE_from"] != null) {
$arFilter[">=DATE_CREATE"] = $filterData["DATE_CREATE_from"];
}
if($filterData["DATE_CREATE_to"] != null) {
$arFilter["<=DATE_CREATE"] = $filterData["DATE_CREATE_to"];
}
$arFilter["CHECK_PERMISSIONS"] = "N"; // не проверять права доступа текущего пользователя
//$arFilter["!STATUS_ID"] = ["JUNK",7,8]; // кроме нецелевой, текущий клиент, спам
$arResult['filter'] = $filter;
// Все лиды по аналитикам
global $DB;
$sql = "SELECT b_crm_tracking_trace_entity.ENTITY_ID as LEAD_ID, b_crm_tracking_trace.SOURCE_ID as ANALYTIC_ID FROM b_crm_tracking_trace_entity, b_crm_tracking_trace WHERE b_crm_tracking_trace_entity.TRACE_ID = b_crm_tracking_trace.ID AND b_crm_tracking_trace_entity.ENTITY_TYPE_ID=1"; //sql запрос
$sql = $DB->ForSql($sql); //Подготавливает строку (заменяет кавычки и прочее) для вставки в SQL запрос
$res = $DB->Query($sql); //выполняет запрос SELECT,INSERT,DELETE,UPDATE
$arResult['LEADS_BY_ANALYTICS'] = [];
$analytic_ids = [];
$leads_by_id = [];
while ($row = $res->Fetch()){
$leads_by_id[$row['LEAD_ID']] = $row;
}
foreach($leads_by_id as $lead) {
$arResult['LEADS_BY_ANALYTICS'][$lead['ANALYTIC_ID']][] = $lead['LEAD_ID'];
$analytic_ids[] = $lead['ANALYTIC_ID'];
}
//сортировка аналитики
$arSortAnalytic = []; //тут правильный порядок аналитики
$arSelect = Array("ID", "PROPERTY_ANALYTICS_ID");
$analyticArFilter = Array("IBLOCK_ID"=>150, "PROPERTY_ANALYTICS_ID" => $analytic_ids);
$res = CIBlockElement::GetList(Array("sort"=>"asc"), $analyticArFilter, false, Array(), $arSelect);
while($arItem = $res->Fetch()) {
$arSortAnalytic[] = $arItem["PROPERTY_ANALYTICS_ID_VALUE"];
}
$arSelect = array(
// "ID",
// "TITLE",
// "SOURCE_ID",
// "STATUS_ID",
// "OPPORTUNITY",
// "UF_CRM_1602060930", //подразделение
// "UF_CRM_1632338031",
// "UF_CRM_1603300557",
// "DATE_CREATE",
// "IS_RETURN_CUSTOMER" // повторный
);
$arLeads = CCrmLead::GetList(Array(), $arFilter, $arSelect);
$channel_event_id = [];
while($row = $arLeads->Fetch()){
if(isset($filterData['ANALYTICS'])) {
foreach($filterData['ANALYTICS'] as $analytic_id) {
if(in_array($row['ID'], $arResult['LEADS_BY_ANALYTICS'][$analytic_id])) {
$arResult['LEADS'][$row['ID']] = $row;
if($row['UF_CRM_1632338031'] != "" && $row['UF_CRM_1632338031'] != null) {
$channel_event_id[$row['UF_CRM_1632338031']] = $row['UF_CRM_1632338031'];
}
}
}
} else {
$arResult['LEADS'][$row['ID']] = $row;
if($row['UF_CRM_1632338031'] != "" && $row['UF_CRM_1632338031'] != null) {
$channel_event_id[$row['UF_CRM_1632338031']] = $row['UF_CRM_1632338031'];
}
}
}
$leads_by = [];
$status_ids = [];
foreach($arResult['LEADS'] as $lead) {
if($lead['UF_CRM_1603300557'] == null) {
$leads_by['DEAL_TYPE']['no_deal_type'][$lead['ID']] = $lead;
} else {
$leads_by['DEAL_TYPE'][$lead['UF_CRM_1603300557']][$lead['ID']] = $lead;
}
$leads_by['SOURCE'][$lead['SOURCE_ID']][$lead['ID']] = $lead;
$leads_by['SUBDIVISION'][$lead['UF_CRM_1602060930']][$lead['ID']] = $lead;
$leads_by['STATUS_ID'][$lead['STATUS_ID']][$lead['ID']] = $lead;
$status_ids[$lead['STATUS_ID']] = $lead['STATUS_ID'];
}
$leads_by['ANALYTICS'] = [];
/// ERROR arSortAnalytic EMPTY
$arSortAnalytic[] = "";
$arSortAnalytic[] = "0";
foreach($arSortAnalytic as $analyticKey){
$analytic = $arResult['LEADS_BY_ANALYTICS'][$analyticKey] ?? [];
foreach($analytic as $lead) {
if(isset($arResult['LEADS'][$lead])) {
$leads_by['ANALYTICS'][$analyticKey][$arResult['LEADS'][$lead]['ID']] = $arResult['LEADS'][$lead];
}
}
}
if(!isset($filterData['GROUPING_1'])) {
$filterData['GROUPING_1'] = 'SOURCE';
}
if(!CModule::IncludeModule("iblock")) {
ShowError("CIBlockSection_NOT_INSTALLED");
return false;
}
$arSubdivisions = CIBlockSection::GetList(Array("SORT"=>"ASC"), Array("IBLOCK_ID" => 1), false, Array(), false);
$arResult['SUBDIVISION'] = [];
while($row = $arSubdivisions->Fetch()){
$arResult['SUBDIVISION'][$row['ID']] = $row['NAME'];
}
$arResult['channel_events'] = [];
$arSelect = Array("ID", "NAME");
$arFilter = Array("IBLOCK_ID"=>IntVal(153), "ACTIVE_DATE"=>"Y", "ACTIVE"=>"Y", "ID" => $channel_event_id);
$res = CIBlockElement::GetList(Array(), $arFilter, false, Array(), $arSelect);
while($ob = $res->GetNextElement()) {
$arEvents = $ob->GetFields();
$arResult['channel_events'][$arEvents['ID']] = $arEvents['NAME'];
}
//список источников
$arResult['SOURCE'] = [];
$dbRow = CCrmStatus::GetList(['SORT'=>'asc'], ['ENTITY_ID'=>'SOURCE']);
while ($row = $dbRow->fetch()) {
if(!in_array($row["STATUS_ID"],["CALLBACK","WEBFORM","CALL","CALL_TRACKER","RC_GENERATOR","STORE"])){
$arResult['SOURCE'][$row["STATUS_ID"]] = $row["NAME"];
}
}
//спиок типов лидов
$arResult['DEAL_TYPE'] = [];
$dbRow = CCrmStatus::GetList(['SORT'=>'asc'], ['ENTITY_ID'=>'DEAL_TYPE']);
while ($row = $dbRow->fetch()) {
$arResult['DEAL_TYPE'][$row["STATUS_ID"]] = $row["NAME"];
}
// DEAL_STATE STATUS
$arResult["STATUS_ID"] = [];
$dbRow = CCrmStatus::GetList(array('SORT'=>'asc'), array('ENTITY_ID' => 'STATUS'));
while ($row = $dbRow->fetch()) {
$arResult["STATUS_ID"][$row["STATUS_ID"]] = $row["NAME"];
}
//список сквозной аналитики
$arResult['ANALYTICS'] = [];
$dbRow = Bitrix\Crm\Tracking\Internals\SourceTable::getList([
'select' => [
'*'
],
'filter' => [
// 'ID' => $channel_event_id
//'=ACTIVE' => 'Y'
],
'cache' => ['ttl' => 3600],
]);
$analytic_ids = [];
while ($row = $dbRow->fetch()) {
if($row["ID"] != 37){
$arResult['ANALYTICS'][$row["ID"]] = $row;
$analytic_ids[] = $row['ID'];
}
}
$arResult["SORT_ANALYTICS"] = [];
foreach($arSortAnalytic as $analyticKey){
if(isset($arResult['ANALYTICS'][$analyticKey])){
$arResult["SORT_ANALYTICS"] [$analyticKey] = $arResult['ANALYTICS'][$analyticKey];
}
}
$leadsGroupingBy = [];
foreach($leads_by[$filterData['GROUPING_1']] as $group_name_1 => $group_leads_1) {
if($group_name_1 == "" && $filterData['GROUPING_1'] == 'ANALYTICS') {
$group_name_1 = 'empty';
}
if($group_name_1 == 'empty') {
foreach($group_leads_1 as $leadGrEmpty) {
$leadsGroupingBy[$group_name_1]['LEADS'][] = $leadGrEmpty;
}
} else {
$leadsGroupingBy[$group_name_1]['LEADS'] = $group_leads_1;
}
if(isset($filterData['GROUPING_2'])) {
foreach($leads_by[$filterData['GROUPING_2']] as $group_name_2 => $group_leads_2) {
foreach($group_leads_2 as $lead2) {
if(isset($group_leads_1[$lead2['ID']])) {
if($group_name_2 == "" && $filterData['GROUPING_2'] == 'ANALYTICS') {
$group_name_2 = 'empty';
}
$leadsGroupingBy[$group_name_1]['GROUPS'][$group_name_2]['LEADS'][] = $lead2;
}
// if(isset($filterData['GROUPING_3'])) {
// foreach($leads_by[$filterData['GROUPING_3']] as $group_name_3 => $group_leads_3) {
// foreach($group_leads_3 as $lead3) {
// if(isset($leadsGroupingBy[$group_name_1]['GROUPS'][$group_name_2]['LEADS'][$lead3['ID']])) {
// $leadsGroupingBy[$group_name_1]['GROUPS'][$group_name_2]['GROUPS'][$group_name_3]['LEADS'][] = $lead3;
// }
// }
// }
// }
}
}
}
}
$arResult['grouping'] = array(
"ANALYTICS" => "По сквозной аналитике",
"DEAL_TYPE" => "По типу сделки",
"SOURCE" => "По источнику",
"SUBDIVISION" => "По подразделению",
"STATUS_ID" => "По статусу",
// "REPEAT" => "Повторный"
);
if(!isset($filterData['GROUPING_1'])) { // Сделать чтобы выбиралось по умолчанию в фильтре
$filterData['GROUPING_1'] = 'ANALYTICS';
}
$grouping_label_1 = isset($filterData['GROUPING_1']) ? 'Группировка 1 - '.$arResult['grouping'][$filterData['GROUPING_1']] : 'Группировка 1';
$grouping_label_2 = isset($filterData['GROUPING_2']) ? 'Группировка 2 - '.$arResult['grouping'][$filterData['GROUPING_2']] : 'Группировка 2';
$grouping_label_3 = isset($filterData['GROUPING_3']) ? 'Группировка 3 - '.$arResult['grouping'][$filterData['GROUPING_3']] : 'Группировка 3';
$arResult['columns'] = [];
$arResult['columns'][] = ['id' => 'GROUPING_1', 'name' => $grouping_label_1, 'sort' => '1', 'default' => true];
if(isset($filterData['GROUPING_2'])) {
$arResult['columns'][] = ['id' => 'GROUPING_2', 'name' => $grouping_label_2, 'sort' => '2', 'default' => true];
}
if(isset($filterData['GROUPING_3'])) {
$arResult['columns'][] = ['id' => 'GROUPING_3', 'name' => $grouping_label_3, 'sort' => '3', 'default' => true];
}
$arResult['columns'][] = ['id' => 'LEADS', 'name' => 'Количество лидов', 'sort' => '4', 'default' => true];
$list_labels_1 = [];
if($filterData['GROUPING_1'] == 'ANALYTICS') {
foreach($arResult['LEADS_BY_ANALYTICS'] as $key => $value) {
$list_labels_1[$key] = $arResult['ANALYTICS'][$key]['NAME'];
if($arResult['ANALYTICS'][$key]['NAME'] == "") {
$list_labels_1[$key] = 'Без аналитики';
}
}
} else if($filterData['GROUPING_1'] == 'DEAL_TYPE') {
foreach($arResult['DEAL_TYPE'] as $key => $value) {
$list_labels_1[$key] = $value;
}
} else if($filterData['GROUPING_1'] == 'SOURCE') {
foreach($arResult['SOURCE'] as $key => $value) {
$list_labels_1[$key] = $value;
}
} else if($filterData['GROUPING_1'] == 'SUBDIVISION') {
foreach($arResult['SUBDIVISION'] as $key => $value) {
$list_labels_1[$key] = $value;
}
} else if($filterData['GROUPING_1'] == 'STATUS_ID') {
foreach($arResult['STATUS_ID'] as $key => $value) {
$list_labels_1[$key] = $value;
}
}
if(isset($filterData['GROUPING_2'])) {
$list_labels_2 = [];
if($filterData['GROUPING_2'] == 'ANALYTICS') {
foreach($arResult['LEADS_BY_ANALYTICS'] as $key => $value) {
$list_labels_2[$key] = $arResult['ANALYTICS'][$key]['NAME'];
if($arResult['ANALYTICS'][$key]['NAME'] == "") {
$list_labels_2[$key] = 'Без аналитики';
}
}
} else if($filterData['GROUPING_2'] == 'DEAL_TYPE') {
foreach($arResult['DEAL_TYPE'] as $key => $value) {
$list_labels_2[$key] = $value;
}
} else if($filterData['GROUPING_2'] == 'SOURCE') {
foreach($arResult['SOURCE'] as $key => $value) {
$list_labels_2[$key] = $value;
}
} else if($filterData['GROUPING_2'] == 'SUBDIVISION') {
foreach($arResult['SUBDIVISION'] as $key => $value) {
$list_labels_2[$key] = $value;
}
} else if($filterData['GROUPING_2'] == 'STATUS_ID') {
foreach($arResult['STATUS_ID'] as $key => $value) {
$list_labels_2[$key] = $value;
}
}
}
if($filterData['GROUPING_1'] == "ANALYTICS") {
$empty_an = $leadsGroupingBy['empty'];
unset($leadsGroupingBy['empty']);
$leadsGroupingBy['empty'] = $empty_an;
}
foreach($leadsGroupingBy as $grouping_name_1 => $data_1) {
$data = [];
if(isset($filterData['GROUPING_2'])) {
if($list_labels_1[$grouping_name_1] == "" && $filterData['GROUPING_1'] == "ANALYTICS") {
$data['GROUPING_1'] = 'Без аналитики'.' ('.count($data_1['LEADS']).')';
} else {
$data['GROUPING_1'] = $list_labels_1[$grouping_name_1].' ('.count($data_1['LEADS']).')';
}
} else {
if($list_labels_1[$grouping_name_1] == "" && $filterData['GROUPING_1'] == "ANALYTICS") {
$data['GROUPING_1'] = 'Без аналитики';
} else {
$data['GROUPING_1'] = $list_labels_1[$grouping_name_1];
}
}
if(isset($filterData['GROUPING_2'])) {
$data['GROUPING_2'] = '';
$data['LEADS'] = '';
foreach($data_1['GROUPS'] as $grouping_name_2 => $data_2) {
if($list_labels_2[$grouping_name_2] == "" && $filterData['GROUPING_2'] == "ANALYTICS") {
$data['GROUPING_2'] .= '<div class="report-label">Без аналитики</div>';
} else {
$data['GROUPING_2'] .= '<div class="report-label">'.$list_labels_2[$grouping_name_2].'</div>';
}
$details_html = '';
foreach($data_2['LEADS'] as $lead) {
if($lead['OPPORTUNITY'] > 0) {
$details_html .= '<a href="/crm/lead/details/'.$lead['ID'].'/">('.$lead['ID'].') '.$lead['TITLE'].' Цена: '.$lead['OPPORTUNITY'].'</a>';
} else {
$details_html .= '<a href="/crm/lead/details/'.$lead['ID'].'/">('.$lead['ID'].') '.$lead['TITLE'].'</a>';
}
}
$data['LEADS'] .= '<div class="report-label"><details><summary>Лидов: '.count($data_2['LEADS']).'</summary>'.$details_html.'</details></div>';
}
} else {
$data['LEADS'] = '<details><summary>Лидов: '.count($data_1['LEADS']).'</summary>';
foreach($data_1['LEADS'] as $lead) {
if($lead['OPPORTUNITY'] > 0) {
$data['LEADS'] .= '<a href="/crm/lead/details/'.$lead['ID'].'/">('.$lead['ID'].') '.$lead['TITLE'].' Цена: '.$lead['OPPORTUNITY'].'</a>';
} else {
$data['LEADS'] .= '<a href="/crm/lead/details/'.$lead['ID'].'/">('.$lead['ID'].') '.$lead['TITLE'].'</a>';
}
}
$data['LEADS'] .= '</details>';
}
$arResult['list'][] = [
"data" => $data,
"actions" => []
];
}
$arResult['ui_filter'] = [
['id' => 'ANALYTICS', 'name' => 'По Сквозной аналитике', 'type'=>'list', 'params'=>array("multiple" => "Y"), 'items' => $arResult['ANALYTICS']],
['id' => 'DEAL_TYPE', 'name' => 'По типу сделки', 'type'=>'list', 'params'=>array("multiple" => "Y"), 'items' => $arResult['DEAL_TYPE']],
['id' => 'SOURCE_ID', 'name' => 'По источнику', 'type'=>'list', 'params'=>array("multiple" => "Y"), 'items' => $arResult['SOURCE']],
['id' => 'SUBDIVISION', 'name' => 'По подразделению', 'type'=>'list', 'params'=>array("multiple" => "Y"), 'items' => $arResult['SUBDIVISION']],
['id' => 'STATUS_ID', 'name' => 'По статусу', 'type'=>'list', 'params'=>array("multiple" => "Y"), 'items' => $arResult["STATUS_ID"]],
['id' => 'REPEAT', 'name' => 'Повторный', 'type'=>'checkbox', 'default' => false],
['id' => 'CHANNEL_EVENT', 'name' => 'По событию "канал"', 'type'=>'list', 'params'=>array("multiple" => "Y"), 'items' => $arResult['channel_events']],
['id' => 'DATE_CREATE', 'name' => 'По дате', 'type'=>'date', 'default' => true],
['id' => 'GROUPING_1', 'name' => 'Группировка 1', 'type'=>'list', "items" => $arResult['grouping']],
['id' => 'GROUPING_2', 'name' => 'Группировка 2', 'type'=>'list', "items" => $arResult['grouping']],
// ['id' => 'GROUPING_3', 'name' => 'Группировка 3', 'type'=>'list', "items" => $arResult['grouping']],
];
$this->IncludeComponentTemplate(); |
