Lead::prepare

public function prepare()
{
	$userPermission = \CCrmPerms::GetCurrentUserPermissions();
	if (!\CCrmAuthorizationHelper::CheckReadPermission(\CCrmOwnerType::Lead, 0, $userPermission))
	{
		return false;
	}

	/** @var DropDown $grouping */
	$groupingField = $this->getFormElement('groupingBy');
	$groupingValue = $groupingField ? $groupingField->getValue() : null;

	$calculateField = $this->getFormElement('calculate');
	$calculateValue = $calculateField ? $calculateField->getValue() : null;

	$disableSuccessStatesField = $this->getFormElement('disableSuccessStates');
	$disableSuccessStatesValue = $disableSuccessStatesField ? $disableSuccessStatesField->getValue() : false;

	$query = new Query(LeadTable::getEntity());

	switch ($groupingValue)
	{
		case self::GROUPING_BY_DATE:
			$query->registerRuntimeField(new ExpressionField('DATE_CREATE_DAY', "DATE_FORMAT(%s, '%%Y-%%m-%%d 00:00')", 'DATE_CREATE'));
			$query->addSelect('DATE_CREATE_DAY');
			$query->addGroup('DATE_CREATE_DAY');
			break;
		case self::GROUPING_BY_STATE:
			$query->addSelect('FULL_HISTORY.STATUS_ID', 'STATUS_KEY');
			$query->addGroup('FULL_HISTORY.STATUS_ID');

			$statusNameListByStatusId = [];
			foreach ($this->getStatusList() as $status)
			{
				$statusNameListByStatusId[$status['STATUS_ID']] = $status['NAME'];
			}

			break;
		case self::GROUPING_BY_SOURCE:
			$query->addGroup('SOURCE_ID');
			$query->addSelect('SOURCE_ID');

			$sourceNameListByStatusId = [];
			foreach ($this->getSourceNameList() as $source)
			{
				$sourceNameListByStatusId[$source['STATUS_ID']] = $source['NAME'];
			}
			break;
		case self::GROUPING_BY_RESPONSIBLE:
			$query->addGroup('ASSIGNED_BY_ID');
			$query->addSelect('ASSIGNED_BY_ID');
			break;
	}

	switch ($calculateValue)
	{
		case self::WHAT_WILL_CALCULATE_LEAD_DATA_FOR_FUNNEL:
		case self::WHAT_WILL_CALCULATE_SUCCESS_LEAD_DATA_FOR_FUNNEL:
			$query->addSelect(Query::expr()->max('OPPORTUNITY_ACCOUNT'), 'MAX_OPPORTUNITY_ACCOUNT');
			$query->addSelect('FULL_HISTORY.OWNER_ID', 'FULL_HISTORY_OWNER_ID');
			$query->addSelect(Query::expr()->min('FULL_HISTORY.IS_SUPPOSED'), 'FULL_HISTORY_IS_SUPPOSED');
			$query->addSelect(Query::expr()->max('FULL_HISTORY.SPENT_TIME'), 'FULL_HISTORY_SPENT_TIME');
			$query->addSelect(Query::expr()->max('ACCOUNT_CURRENCY_ID'), 'MAX_ACCOUNT_CURRENCY_ID');
			break;
		default:
			$query->addSelect(new \Bitrix\Main\Entity\ExpressionField('VALUE', 'COUNT(DISTINCT %s)', 'FULL_HISTORY.OWNER_ID'));
	}

	switch ($calculateValue)
	{
		case self::WHAT_WILL_CALCULATE_GOOD_LEAD_COUNT:
			$query->whereIn('FULL_HISTORY.STATUS_SEMANTIC_ID', ['P', 'S']);
			break;
		case self::WHAT_WILL_CALCULATE_ACTIVE_LEAD_COUNT:
			$query->where('FULL_HISTORY.STATUS_SEMANTIC_ID', 'P');
			break;
		case self::WHAT_WILL_CALCULATE_CONVERTED_LEAD_COUNT:
		case self::WHAT_WILL_CALCULATE_SUCCESS_LEAD_DATA_FOR_FUNNEL:
			$query->where('FULL_HISTORY.STATUS_SEMANTIC_ID', 'S');
			break;
		case self::WHAT_WILL_CALCULATE_LOST_LEAD_COUNT:
			$query->where('FULL_HISTORY.STATUS_SEMANTIC_ID', 'F');
			break;
		case self::WHAT_WILL_CALCULATE_LEAD_CONVERSION:
		case self::WHAT_WILL_CALCULATE_LEAD_LOSES:
			$query->addGroup('FULL_HISTORY.STATUS_SEMANTIC_ID');
			$query->addSelect('FULL_HISTORY.STATUS_SEMANTIC_ID', 'STATUS_SEMANTIC_ID_VALUE');
			break;
	}

	$this->addToQueryFilterCase($query);
	$this->addPermissionsCheck($query);

	switch ($calculateValue)
	{
		case self::WHAT_WILL_CALCULATE_SUCCESS_LEAD_DATA_FOR_FUNNEL:
		case self::WHAT_WILL_CALCULATE_LEAD_DATA_FOR_FUNNEL:
			$querySql = "SELECT 
							res.STATUS_KEY,
							(SUM(res.FULL_HISTORY_SPENT_TIME) / SUM(CASE WHEN res.FULL_HISTORY_IS_SUPPOSED = 'N' AND res.FULL_HISTORY_SPENT_TIME IS NOT NULL THEN 1 ELSE 0 END)) as SPENT_TIME,  
							COUNT(DISTINCT res.FULL_HISTORY_OWNER_ID) as VALUE, 
							SUM(res.MAX_OPPORTUNITY_ACCOUNT) as SUM, 
							MAX_ACCOUNT_CURRENCY_ID as ACCOUNT_CURRENCY_ID FROM(";
			$querySql .= $query->getQuery();
			$querySql .= ") as res GROUP BY res.STATUS_KEY";
			$results = Application::getConnection()->query($querySql);
			break;
		default:
			$results = $query->exec()->fetchAll();
	}

	$amountLeadCount = 0;
	$amountLeadSum = 0;
	switch ($calculateValue)
	{
		case self::WHAT_WILL_CALCULATE_LEAD_CONVERSION:
			$allLeadCount = [];
			$successLeadCount = [];
			$successAmountLeadCount = 0;
			$groupingFieldName = 'withoutGrouping';

			switch ($groupingValue)
			{
				case self::GROUPING_BY_RESPONSIBLE:
					$allLeadCount = $this->getLeadAmountCountByResponsible();
					$allAmountLeadCount = array_sum($allLeadCount);
					break;
				default:
					$allLeadCount['withoutGrouping'] = $this->getLeadAmountCount();
					$allAmountLeadCount = $allLeadCount['withoutGrouping'];
			}

			foreach ($results as $result)
			{
				switch ($groupingValue)
				{
					case self::GROUPING_BY_RESPONSIBLE:
						$groupingFieldName = 'ASSIGNED_BY_ID';
						$groupingFieldValue = $result[$groupingFieldName];
						break;
					default:
						$groupingFieldValue = 'withoutGrouping';
				}

				if ($result['STATUS_SEMANTIC_ID_VALUE'] == 'S')
				{
					$successLeadCount[$groupingFieldValue] += $result['VALUE'];
					$successAmountLeadCount += $result['VALUE'];
				}
			}
			$results = [];

			foreach ($allLeadCount as $groupingKey => $count)
			{
				if (!empty($successLeadCount[$groupingKey]))
				{
					$results[] = [
						$groupingFieldName => $groupingKey,
						'VALUE' => $count > 0 ? ($successLeadCount[$groupingKey] / $count) * 100 : 0
					];
				}
				else
				{
					$results[] = [
						$groupingFieldName => $groupingKey,
						'VALUE' => 0
					];
				}
			}

			$amountLeadCount = $allAmountLeadCount ? (($successAmountLeadCount / $allAmountLeadCount) * 100) : 0;

			break;
		case self::WHAT_WILL_CALCULATE_LEAD_LOSES:
			$allLeadCount = [];
			$loseLeadCount = [];
			$losesAmountLeadCount = 0;
			$groupingFieldName = 'withoutGrouping';

			switch ($groupingValue)
			{
				case self::GROUPING_BY_RESPONSIBLE:
					$allLeadCount = $this->getLeadAmountCountByResponsible();
					$allAmountLeadCount = array_sum($allLeadCount);
					break;
				default:
					$allLeadCount['withoutGrouping'] = $this->getLeadAmountCount();
					$allAmountLeadCount = $allLeadCount['withoutGrouping'];
			}

			foreach ($results as $result)
			{
				switch ($groupingValue)
				{
					case self::GROUPING_BY_RESPONSIBLE:
						$groupingFieldName = 'ASSIGNED_BY_ID';
						$groupingFieldValue = $result[$groupingFieldName];
						break;
					default:
						$groupingFieldValue = 'withoutGrouping';
				}

				if ($result['STATUS_SEMANTIC_ID_VALUE'] == 'F')
				{
					$loseLeadCount[$groupingFieldValue] += $result['VALUE'];
					$losesAmountLeadCount += $result['VALUE'];
				}
			}
			$results = [];

			foreach ($allLeadCount as $groupingKey => $count)
			{
				if (!empty($loseLeadCount[$groupingKey]))
				{
					$results[] = [
						$groupingFieldName => $groupingKey,
						'VALUE' => $count > 0 ? ($loseLeadCount[$groupingKey] / $count) * 100 : 0
					];
				}
				else
				{
					$results[] = [
						$groupingFieldName => $groupingKey,
						'VALUE' => 0
					];
				}
			}

			$amountLeadCount = $allAmountLeadCount ? (($losesAmountLeadCount / $allAmountLeadCount) * 100) : 0;
			break;
	}

	$leadCalculatedValue = [];
	$percentageMetricsList = [
		self::WHAT_WILL_CALCULATE_LEAD_CONVERSION,
		self::WHAT_WILL_CALCULATE_LEAD_LOSES,
	];

	$statusNum = 0;
	foreach ($results as $result)
	{
		if (!in_array($calculateValue, $percentageMetricsList))
		{
			$statusNum++;
			if ($this->getView()->getKey() !== ColumnFunnel::VIEW_KEY)
			{
				$amountLeadCount += $result['VALUE'];
			}
		}

		switch ($groupingValue)
		{
			case self::GROUPING_BY_DATE:
				$leadCalculatedValue[$result['DATE_CREATE_DAY']]['value'] = $result['VALUE'];
				$leadCalculatedValue[$result['DATE_CREATE_DAY']]['title'] = $result['DATE_CREATE_DAY'];
				break;
			case self::GROUPING_BY_STATE:
				if ($statusNum === 1)
				{
					$leadCountAndSum = $this->getLeadAmountCountAndSum();
					$amountLeadCount = $leadCountAndSum['COUNT'];
					$amountLeadSum = $leadCountAndSum['SUM'];
				}

				$leadCalculatedValue[$result['STATUS_KEY']]['value'] = $result['VALUE'];
				$leadCalculatedValue[$result['STATUS_KEY']]['additionalValues']['sum']['VALUE'] = 0;
				$leadCalculatedValue[$result['STATUS_KEY']]['additionalValues']['sum']['currencyId'] = \CCrmCurrency::GetAccountCurrencyID();

				if ($result['SUM'])
				{
					$leadCalculatedValue[$result['STATUS_KEY']]['additionalValues']['sum'] = [
						'VALUE' => $result['SUM'],
						'currencyId' => !empty($result['ACCOUNT_CURRENCY_ID']) ? $result['ACCOUNT_CURRENCY_ID'] : null
					];
				}

				$statusSemanticId = \CCrmLead::GetSemanticID($result['STATUS_KEY']);
				if (!PhaseSemantics::isFinal($statusSemanticId))
				{
					$leadCalculatedValue[$result['STATUS_KEY']]['additionalValues']['avgSpentTime']['VALUE'] = (int)$result['SPENT_TIME'];
				}
				$leadCalculatedValue[$result['STATUS_KEY']]['title'] = !empty($statusNameListByStatusId[$result['STATUS_KEY']]) ? $statusNameListByStatusId[$result['STATUS_KEY']] : '';
				$leadCalculatedValue[$result['STATUS_KEY']]['color'] = $this->getStatusColor($result['STATUS_KEY']);
				break;
			case self::GROUPING_BY_SOURCE:
				$leadCalculatedValue[$result['SOURCE_ID']]['value'] = $result['VALUE'];
				$leadCalculatedValue[$result['SOURCE_ID']]['title'] = !empty($sourceNameListByStatusId[$result['SOURCE_ID']]) ? $sourceNameListByStatusId[$result['SOURCE_ID']] : '';
				break;
			case self::GROUPING_BY_RESPONSIBLE:
				if ($result['ASSIGNED_BY_ID']  == 0)
				{
					continue 2;
				}
				//TODO optimise here
				$userInfo = $this->getUserInfo($result['ASSIGNED_BY_ID']);
				$leadCalculatedValue[$result['ASSIGNED_BY_ID']]['value'] = $result['VALUE'];
				$leadCalculatedValue[$result['ASSIGNED_BY_ID']]['title'] = $userInfo['name'];
				$leadCalculatedValue[$result['ASSIGNED_BY_ID']]['logo'] = $userInfo['icon'];
				$leadCalculatedValue[$result['ASSIGNED_BY_ID']]['targetUrl'] = $userInfo['link'];
				break;
			default:
				$leadCalculatedValue['withoutGrouping'] = $result['VALUE'];
				break;
		}
	}

	if ($groupingValue === self::GROUPING_BY_STATE && isset($statusNameListByStatusId) && $calculateValue !==self::WHAT_WILL_CALCULATE_SUCCESS_LEAD_DATA_FOR_FUNNEL)
	{
		$sortedLeadCountListByStatus = [];
		foreach ($statusNameListByStatusId as $statusId => $statusName)
		{
			if (!empty($leadCalculatedValue[$statusId]))
			{
				$sortedLeadCountListByStatus[$statusId] = $leadCalculatedValue[$statusId];
			}
			else
			{
				$sortedLeadCountListByStatus[$statusId] = [
					'value' => 0,
					'title' => $statusName,
					'color' => $this->getStatusColor($statusId)
				];
			}
		}
		$leadCalculatedValue = $sortedLeadCountListByStatus;
	}

	$leadCalculatedValue['amount']['count'] = $amountLeadCount;
	$leadCalculatedValue['amount']['sum'] = $amountLeadSum;

	if ($calculateValue !== self::WHAT_WILL_CALCULATE_SUCCESS_LEAD_DATA_FOR_FUNNEL)
	{
		$leadCalculatedValue['amount']['successPassTime'] = $this->getLeadPassingTime();
	}

	if ($disableSuccessStatesValue)
	{
		unset($leadCalculatedValue['CONVERTED']);
	}

	//replace converted value to the end in column funnel
	if ($calculateValue === self::WHAT_WILL_CALCULATE_LEAD_DATA_FOR_FUNNEL)
	{
		if (!empty($leadCalculatedValue['CONVERTED']))
		{
			$convertedValue = $leadCalculatedValue['CONVERTED'];
			unset($leadCalculatedValue['CONVERTED']);

			$leadCalculatedValue['CONVERTED'] = $convertedValue;
		}
	}

	return $leadCalculatedValue;
}