static function GetFields($arOptions = null)
{
$companyJoin = 'LEFT JOIN b_crm_company CO ON L.COMPANY_ID = CO.ID';
$contactJoin = 'LEFT JOIN b_crm_contact C ON L.CONTACT_ID = C.ID';
$quoteJoin = 'LEFT JOIN b_crm_quote Q ON L.QUOTE_ID = Q.ID';
$assignedByJoin = 'LEFT JOIN b_user U ON L.ASSIGNED_BY_ID = U.ID';
$createdByJoin = 'LEFT JOIN b_user U2 ON L.CREATED_BY_ID = U2.ID';
$modifyByJoin = 'LEFT JOIN b_user U3 ON L.MODIFY_BY_ID = U3.ID';
$result = array(
'ID' => array('FIELD' => 'L.ID', 'TYPE' => 'int'),
'TITLE' => array('FIELD' => 'L.TITLE', 'TYPE' => 'string'),
'TYPE_ID' => array('FIELD' => 'L.TYPE_ID', 'TYPE' => 'string'),
'STAGE_ID' => array('FIELD' => 'L.STAGE_ID', 'TYPE' => 'string'),
'ORDER_STAGE' => array('FIELD' => 'L.ORDER_STAGE', 'TYPE' => 'string'),
'PROBABILITY' => array('FIELD' => 'L.PROBABILITY', 'TYPE' => 'int'),
'CURRENCY_ID' => array('FIELD' => 'L.CURRENCY_ID', 'TYPE' => 'string'),
'EXCH_RATE' => array('FIELD' => 'L.EXCH_RATE', 'TYPE' => 'double'),
'OPPORTUNITY' => array('FIELD' => 'L.OPPORTUNITY', 'TYPE' => 'double'),
'IS_MANUAL_OPPORTUNITY' => array('FIELD' => 'L.IS_MANUAL_OPPORTUNITY', 'TYPE' => 'char'),
'TAX_VALUE' => array('FIELD' => 'L.TAX_VALUE', 'TYPE' => 'double'),
'ACCOUNT_CURRENCY_ID' => array('FIELD' => 'L.ACCOUNT_CURRENCY_ID', 'TYPE' => 'string'),
'OPPORTUNITY_ACCOUNT' => array('FIELD' => 'L.OPPORTUNITY_ACCOUNT', 'TYPE' => 'double'),
'TAX_VALUE_ACCOUNT' => array('FIELD' => 'L.TAX_VALUE_ACCOUNT', 'TYPE' => 'double'),
'LEAD_ID' => array('FIELD' => 'L.LEAD_ID', 'TYPE' => 'int'),
'COMPANY_ID' => array('FIELD' => 'L.COMPANY_ID', 'TYPE' => 'int'),
'COMPANY_ADDRESS' => array('FIELD' => 'CO.ADDRESS', 'TYPE' => 'string', 'FROM' => $companyJoin),
'COMPANY_ADDRESS_LEGAL' => array('FIELD' => 'CO.ADDRESS_LEGAL', 'TYPE' => 'string', 'FROM' => $companyJoin),
'CONTACT_ID' => array('FIELD' => 'L.CONTACT_ID', 'TYPE' => 'int'),
'CONTACT_ADDRESS' => array('FIELD' => 'C.ADDRESS', 'TYPE' => 'string', 'FROM' => $contactJoin),
'QUOTE_ID' => array('FIELD' => 'L.QUOTE_ID', 'TYPE' => 'int'),
'QUOTE_TITLE' => array('FIELD' => 'Q.TITLE', 'TYPE' => 'string', 'FROM' => $quoteJoin),
'BEGINDATE' => array('FIELD' => 'L.BEGINDATE', 'TYPE' => 'date'),
'CLOSEDATE' => array('FIELD' => 'L.CLOSEDATE', 'TYPE' => 'date'),
'ASSIGNED_BY_ID' => array('FIELD' => 'L.ASSIGNED_BY_ID', 'TYPE' => 'int'),
'ASSIGNED_BY_LOGIN' => array('FIELD' => 'U.LOGIN', 'TYPE' => 'string', 'FROM' => $assignedByJoin),
'ASSIGNED_BY_NAME' => array('FIELD' => 'U.NAME', 'TYPE' => 'string', 'FROM' => $assignedByJoin),
'ASSIGNED_BY_LAST_NAME' => array('FIELD' => 'U.LAST_NAME', 'TYPE' => 'string', 'FROM' => $assignedByJoin),
'ASSIGNED_BY_SECOND_NAME' => array('FIELD' => 'U.SECOND_NAME', 'TYPE' => 'string', 'FROM' => $assignedByJoin),
'ASSIGNED_BY_WORK_POSITION' => array('FIELD' => 'U.WORK_POSITION', 'TYPE' => 'string', 'FROM' => $assignedByJoin),
'ASSIGNED_BY_PERSONAL_PHOTO' => array('FIELD' => 'U.PERSONAL_PHOTO', 'TYPE' => 'string', 'FROM' => $assignedByJoin),
'CREATED_BY_ID' => array('FIELD' => 'L.CREATED_BY_ID', 'TYPE' => 'int'),
'CREATED_BY_LOGIN' => array('FIELD' => 'U2.LOGIN', 'TYPE' => 'string', 'FROM' => $createdByJoin),
'CREATED_BY_NAME' => array('FIELD' => 'U2.NAME', 'TYPE' => 'string', 'FROM' => $createdByJoin),
'CREATED_BY_LAST_NAME' => array('FIELD' => 'U2.LAST_NAME', 'TYPE' => 'string', 'FROM' => $createdByJoin),
'CREATED_BY_SECOND_NAME' => array('FIELD' => 'U2.SECOND_NAME', 'TYPE' => 'string', 'FROM' => $createdByJoin),
'MODIFY_BY_ID' => array('FIELD' => 'L.MODIFY_BY_ID', 'TYPE' => 'int'),
'MODIFY_BY_LOGIN' => array('FIELD' => 'U3.LOGIN', 'TYPE' => 'string', 'FROM' => $modifyByJoin),
'MODIFY_BY_NAME' => array('FIELD' => 'U3.NAME', 'TYPE' => 'string', 'FROM' => $modifyByJoin),
'MODIFY_BY_LAST_NAME' => array('FIELD' => 'U3.LAST_NAME', 'TYPE' => 'string', 'FROM' => $modifyByJoin),
'MODIFY_BY_SECOND_NAME' => array('FIELD' => 'U3.SECOND_NAME', 'TYPE' => 'string', 'FROM' => $modifyByJoin),
'DATE_CREATE' => array('FIELD' => 'L.DATE_CREATE', 'TYPE' => 'datetime'),
'DATE_MODIFY' => array('FIELD' => 'L.DATE_MODIFY', 'TYPE' => 'datetime'),
'OPENED' => array('FIELD' => 'L.OPENED', 'TYPE' => 'char'),
'CLOSED' => array('FIELD' => 'L.CLOSED', 'TYPE' => 'char'),
'COMMENTS' => array('FIELD' => 'L.COMMENTS', 'TYPE' => 'string'),
'ADDITIONAL_INFO' => array('FIELD' => 'L.ADDITIONAL_INFO', 'TYPE' => 'string'),
'LOCATION_ID' => array('FIELD' => 'L.LOCATION_ID', 'TYPE' => 'string'),
'CATEGORY_ID' => array('FIELD' => 'L.CATEGORY_ID', 'TYPE' => 'int'),
'STAGE_SEMANTIC_ID' => array('FIELD' => 'L.STAGE_SEMANTIC_ID', 'TYPE' => 'string'),
'IS_NEW' => array('FIELD' => 'L.IS_NEW', 'TYPE' => 'char'),
'IS_RECURRING' => array('FIELD' => 'L.IS_RECURRING', 'TYPE' => 'char'),
'IS_RETURN_CUSTOMER' => array('FIELD' => 'L.IS_RETURN_CUSTOMER', 'TYPE' => 'char'),
'IS_REPEATED_APPROACH' => array('FIELD' => 'L.IS_REPEATED_APPROACH', 'TYPE' => 'char'),
'SOURCE_ID' => array('FIELD' => 'L.SOURCE_ID', 'TYPE' => 'string'),
'SOURCE_DESCRIPTION' => array('FIELD' => 'L.SOURCE_DESCRIPTION', 'TYPE' => 'string'),
'WEBFORM_ID' => array('FIELD' => 'L.WEBFORM_ID', 'TYPE' => 'int'),
'ORIGINATOR_ID' => array('FIELD' => 'L.ORIGINATOR_ID', 'TYPE' => 'string'), //EXTERNAL SYSTEM THAT OWNS THIS ITEM
'ORIGIN_ID' => array('FIELD' => 'L.ORIGIN_ID', 'TYPE' => 'string'), //ITEM ID IN EXTERNAL SYSTEM
'MOVED_BY_ID' => ['FIELD' => 'L.MOVED_BY_ID', 'TYPE' => 'int'],
'MOVED_TIME' => ['FIELD' => 'L.MOVED_TIME', 'TYPE' => 'datetime'],
// For compatibility only
'PRODUCT_ID' => array('FIELD' => 'L.PRODUCT_ID', 'TYPE' => 'string'),
// Obsolete
'EVENT_ID' => array('FIELD' => 'L.EVENT_ID', 'TYPE' => 'string'),
'EVENT_DATE' => array('FIELD' => 'L.EVENT_DATE', 'TYPE' => 'datetime'),
'EVENT_DESCRIPTION' => array('FIELD' => 'L.EVENT_DESCRIPTION', 'TYPE' => 'string'),
'LAST_ACTIVITY_TIME' => array('FIELD' => 'L.LAST_ACTIVITY_TIME', 'TYPE' => 'datetime')
);
// Creation of field aliases
$result['ASSIGNED_BY'] = $result['ASSIGNED_BY_ID'];
$result['CREATED_BY'] = $result['CREATED_BY_ID'];
$result['MODIFY_BY'] = $result['MODIFY_BY_ID'];
if(!is_array($arOptions))
{
$arOptions = array();
}
$categoryID = isset($arOptions['CATEGORY_ID']) ? (int)$arOptions['CATEGORY_ID'] : 0;
$additionalFields = isset($arOptions['ADDITIONAL_FIELDS'])
? $arOptions['ADDITIONAL_FIELDS'] : null;
if(is_array($additionalFields))
{
if(in_array('STAGE_SORT', $additionalFields, true))
{
$statusEntityID = DealCategory::getStatusEntityID($categoryID);
$stageJoin = "LEFT JOIN b_crm_status ST ON ST.ENTITY_ID = '{$statusEntityID}' AND L.STAGE_ID = ST.STATUS_ID";
$result['STAGE_SORT'] = array('FIELD' => 'ST.SORT', 'TYPE' => 'int', 'FROM' => $stageJoin);
}
if(in_array('ACTIVITY', $additionalFields, true))
{
$commonActivityJoin = CCrmActivity::PrepareJoin(0, CCrmOwnerType::Deal, 'L', 'AC', 'UAC', 'ACUSR');
$result['C_ACTIVITY_ID'] = array('FIELD' => 'UAC.ACTIVITY_ID', 'TYPE' => 'int', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_TIME'] = array('FIELD' => 'UAC.ACTIVITY_TIME', 'TYPE' => 'datetime', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_SUBJECT'] = array('FIELD' => 'AC.SUBJECT', 'TYPE' => 'string', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_RESP_ID'] = array('FIELD' => 'AC.RESPONSIBLE_ID', 'TYPE' => 'int', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_RESP_LOGIN'] = array('FIELD' => 'ACUSR.LOGIN', 'TYPE' => 'string', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_RESP_NAME'] = array('FIELD' => 'ACUSR.NAME', 'TYPE' => 'string', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_RESP_LAST_NAME'] = array('FIELD' => 'ACUSR.LAST_NAME', 'TYPE' => 'string', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_RESP_SECOND_NAME'] = array('FIELD' => 'ACUSR.SECOND_NAME', 'TYPE' => 'string', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_TYPE_ID'] = array('FIELD' => 'AC.TYPE_ID', 'TYPE' => 'int', 'FROM' => $commonActivityJoin);
$result['C_ACTIVITY_PROVIDER_ID'] = array('FIELD' => 'AC.PROVIDER_ID', 'TYPE' => 'string', 'FROM' => $commonActivityJoin);
$userID = CCrmPerms::GetCurrentUserID();
if($userID > 0)
{
$activityJoin = CCrmActivity::PrepareJoin($userID, CCrmOwnerType::Deal, 'L', 'A', 'UA', '');
$result['ACTIVITY_ID'] = array('FIELD' => 'UA.ACTIVITY_ID', 'TYPE' => 'int', 'FROM' => $activityJoin);
$result['ACTIVITY_TIME'] = array('FIELD' => 'UA.ACTIVITY_TIME', 'TYPE' => 'datetime', 'FROM' => $activityJoin);
$result['ACTIVITY_SUBJECT'] = array('FIELD' => 'A.SUBJECT', 'TYPE' => 'string', 'FROM' => $activityJoin);
$result['ACTIVITY_TYPE_ID'] = array('FIELD' => 'A.TYPE_ID', 'TYPE' => 'int', 'FROM' => $activityJoin);
$result['ACTIVITY_PROVIDER_ID'] = array('FIELD' => 'A.PROVIDER_ID', 'TYPE' => 'string', 'FROM' => $activityJoin);
}
}
if (in_array('RECURRING', $additionalFields, true))
{
$recurringJoin = "LEFT JOIN b_crm_deal_recur DR ON DR.DEAL_ID = L.ID";
$result['CRM_DEAL_RECURRING_ACTIVE'] = array('FIELD' => 'DR.ACTIVE', 'TYPE' => 'string', 'FROM' => $recurringJoin);
$result['CRM_DEAL_RECURRING_COUNTER_REPEAT'] = array('FIELD' => 'DR.COUNTER_REPEAT', 'TYPE' => 'int', 'FROM' => $recurringJoin);
$result['CRM_DEAL_RECURRING_NEXT_EXECUTION'] = array('FIELD' => 'DR.NEXT_EXECUTION', 'TYPE' => 'date', 'FROM' => $recurringJoin);
$result['CRM_DEAL_RECURRING_START_DATE'] = array('FIELD' => 'DR.START_DATE', 'TYPE' => 'date', 'FROM' => $recurringJoin);
$result['CRM_DEAL_RECURRING_LIMIT_DATE'] = array('FIELD' => 'DR.LIMIT_DATE', 'TYPE' => 'date', 'FROM' => $recurringJoin);
$result['CRM_DEAL_RECURRING_LIMIT_REPEAT'] = array('FIELD' => 'DR.LIMIT_REPEAT', 'TYPE' => 'int', 'FROM' => $recurringJoin);
}
}
$result = array_merge(
$result,
self::prepareClientFields(
CCrmContact::GetFields([
'TABLE_ALIAS' => 'C',
'ADD_FIELD_ALIASES' => false,
]),
'CONTACT_',
$contactJoin
)
);
$result = array_merge(
$result,
self::prepareClientFields(
CCrmCompany::GetFields([
'TABLE_ALIAS' => 'CO',
'ADD_FIELD_ALIASES' => false,
]),
'COMPANY_',
$companyJoin
)
);
// add utm fields
$result = array_merge($result, UtmTable::getFieldsDescriptionByEntityTypeId(CCrmOwnerType::Deal));
$result = array_merge(
$result,
Crm\Service\Container::getInstance()->getParentFieldManager()->getParentFieldsSqlInfo(
CCrmOwnerType::Deal,
'L'
)
);
// add uf fields
if (
isset($arOptions['UF_FIELDS']) &&
is_array($arOptions['UF_FIELDS'])
)
{
foreach ($arOptions['UF_FIELDS'] as $ufField)
{
if (
isset($ufField['FIELD']) &&
isset($ufField['TYPE']) &&
is_string($ufField['FIELD']) &&
is_string($ufField['TYPE'])
)
{
$result[$ufField['FIELD']] = [
'FIELD' => 'UF.' . $ufField['FIELD'],
'TYPE' => $ufField['TYPE'],
'FROM' => 'LEFT JOIN b_uts_crm_deal UF ON L.ID = UF.VALUE_ID'
];
}
}
}
$result += self::getLastActivityAdapter()->getFields();
return $result;
}