public static function GetList($order = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array())
{
$filter = array("LOGIC" => "AND");
if (is_array($arFilter)) {
foreach ($arFilter as $dkey => $val) {
$key = CSqlUtil::GetFilterOperation($dkey);
if ($key["FIELD"] == "ID" && is_array($val)) {
$res = array("LOGIC" => "OR");
foreach ($val as $v) {
if ($v == self::SUBSCRIBE_ALL) {
$res[] = array("=SUBSCRIBE_TYPE" => \Bitrix\Idea\NotifyEmailTable::SUBSCRIBE_TYPE_ALL);
} else {
if (strpos($v, self::SUBSCRIBE_IDEA_COMMENT) === 0) {
$res[] = array("=ENTITY_TYPE" => \Bitrix\Idea\NotifyEmailTable::ENTITY_TYPE_IDEA, "=ENTITY_CODE" => str_replace(self::SUBSCRIBE_IDEA_COMMENT, "", $v));
}
}
}
$filter[] = $res;
} else {
if ($key["FIELD"] == "USER_ID" || $key["FIELD"] == "USER_EMAIL") {
$filter[] = array($dkey => $val);
}
}
}
}
$select = array();
$runtime = array();
if (is_array($arSelectFields)) {
$select = array_intersect($arSelectFields, array_keys(\Bitrix\Idea\NotifyEmailTable::getMap()));
if (in_array("USER_EMAIL", $arSelectFields)) {
$select["USER_EMAIL"] = "USER.EMAIL";
}
if (in_array("ID", $arSelectFields)) {
$select["ID"] = 'RUNTIME_ID';
$runtime[] = new ExpressionField('RUNTIME_ID', Application::getConnection()->getSqlHelper()->getConcatFunction("CASE " . "WHEN %s='" . \Bitrix\Idea\NotifyEmailTable::ENTITY_TYPE_IDEA . "' AND %s='' THEN '" . self::SUBSCRIBE_ALL . "' " . "WHEN %s='" . \Bitrix\Idea\NotifyEmailTable::ENTITY_TYPE_IDEA . "' THEN '" . self::SUBSCRIBE_IDEA_COMMENT . "' " . "WHEN %s='" . \Bitrix\Idea\NotifyEmailTable::ENTITY_TYPE_CATEGORY . "' AND %s='' THEN '" . self::SUBSCRIBE_ALL_IDEA . "' " . "WHEN %s='" . \Bitrix\Idea\NotifyEmailTable::ENTITY_TYPE_CATEGORY . "' THEN '" . \Bitrix\Idea\NotifyEmailTable::ENTITY_TYPE_CATEGORY . "' " . "ELSE 'UNK' END", "%s"), array("ENTITY_TYPE", "ENTITY_CODE", "ENTITY_TYPE", "ENTITY_TYPE", "ENTITY_CODE", "ENTITY_TYPE", "ENTITY_CODE"));
}
}
$db_res = \Bitrix\Idea\NotifyEmailTable::getList(array('filter' => $filter, 'select' => $select, 'order' => $order, 'runtime' => $runtime));
return new CDBResult($db_res);
}
private static function GetRoughRowCount(&$arSql, $tableName, $tableAlias = '', $dbType = '')
{
global $DB;
$tableName = strval($tableName);
$tableAlias = strval($tableAlias);
$dbType = strval($dbType);
if ($dbType === '') {
$dbType = 'MYSQL';
} else {
$dbType = strtoupper($dbType);
}
if ($dbType !== 'MYSQL') {
return CSqlUtil::GetRowCount($arSql, $tableName, $tableAlias, $dbType);
}
$subQuery = $tableAlias !== '' ? "SELECT {$tableAlias}.ID FROM {$tableName} {$tableAlias}" : "SELECT ID FROM {$tableName}";
if ($arSql['FROM'] !== '') {
$subQuery .= ' ' . $arSql['FROM'];
}
if ($arSql['WHERE'] !== '') {
$subQuery .= ' WHERE ' . $arSql['WHERE'];
}
if ($arSql['GROUPBY'] !== '') {
$subQuery .= ' GROUP BY ' . $arSql['GROUPBY'];
}
$query = "SELECT COUNT(*) as CNT FROM ({$subQuery} ORDER BY NULL LIMIT 0, 5000) AS T";
$rs = $DB->Query($query, false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__);
$result = 0;
while ($ary = $rs->Fetch()) {
$result += intval($ary['CNT']);
}
return $result;
}
public static function GetCommunications($activityID, $top = 0)
{
$activityID = intval($activityID);
if ($activityID <= 0) {
self::RegisterError(array('text' => 'Invalid arguments are supplied.'));
return false;
}
global $DB;
$commTableName = CCrmActivity::COMMUNICATION_TABLE_NAME;
$sql = "SELECT ID, TYPE, VALUE, ENTITY_ID, ENTITY_TYPE_ID, ENTITY_SETTINGS FROM {$commTableName} WHERE ACTIVITY_ID = {$activityID} ORDER BY ID ASC";
$top = intval($top);
if ($top > 0) {
CSqlUtil::PrepareSelectTop($sql, $top, CCrmActivity::DB_TYPE);
}
$dbRes = $DB->Query($sql, false, 'FILE: ' . __FILE__ . '<br /> LINE: ' . __LINE__);
$result = array();
while ($arRes = $dbRes->Fetch()) {
$arRes['ENTITY_SETTINGS'] = isset($arRes['ENTITY_SETTINGS']) && $arRes['ENTITY_SETTINGS'] !== '' ? unserialize($arRes['ENTITY_SETTINGS']) : array();
$result[] = $arRes;
}
return $result;
}
function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arUF = array())
{
global $DB;
$obUserFieldsSql = false;
if (is_array($arUF) && array_key_exists("ENTITY_ID", $arUF)) {
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity($arUF["ENTITY_ID"], $arFields["ID"]["FIELD"]);
$obUserFieldsSql->SetSelect($arSelectFields);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
}
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy) > 0) {
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct)) {
if (strlen($strSqlGroupBy) > 0) {
$strSqlGroupBy .= ", ";
}
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// WHERE -->
$arAlreadyJoinedOld = $arAlreadyJoined;
$strSqlWhere .= CSqlUtil::PrepareWhere($arFields, $arFilter, $arAlreadyJoined);
$arAlreadyJoinedDiff = array_diff($arAlreadyJoined, $arAlreadyJoinedOld);
foreach ($arAlreadyJoinedDiff as $from_tmp) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $from_tmp;
}
if ($obUserFieldsSql) {
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strSqlWhere .= (strlen($strSqlWhere) > 0 ? " AND" : "") . " (" . $r . ") ";
}
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
} else {
$order = "ASC";
}
if (array_key_exists($by, $arFields)) {
if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date") {
$arSqlOrder[] = " " . $by . "_X1 " . $order . " ";
if (!is_array($arSelectFields) || !in_array($by, $arSelectFields)) {
$arSelectFields[] = $by;
}
} else {
$arSqlOrder[] = " " . $arFields[$by]["FIELD"] . " " . $order . " ";
}
if (isset($arFields[$by]["FROM"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
} elseif ($obUserFieldsSql && ($s = $obUserFieldsSql->GetOrder($by))) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$tmp_count = count($arSqlOrder);
for ($i = 0; $i < $tmp_count; $i++) {
if (strlen($strSqlOrderBy) > 0) {
$strSqlOrderBy .= ", ";
}
if (strtoupper($DB->type) == "ORACLE") {
if (substr($arSqlOrder[$i], -3) == "ASC") {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS FIRST";
} else {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS LAST";
}
} else {
$strSqlOrderBy .= $arSqlOrder[$i];
}
}
// <-- ORDER BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy) == 0) {
$strSqlSelect = "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT ";
} else {
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
$arSelectFields = array($arSelectFields);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
$tmp_count = count($arFieldsKeys);
for ($i = 0; $i < $tmp_count; $i++) {
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"]) && $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y") {
continue;
}
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL") . " as " . $arFieldsKeys[$i];
} elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT") . " as " . $arFieldsKeys[$i];
} else {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i];
}
if (isset($arFields[$arFieldsKeys[$i]]["FROM"]) && strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0 && !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD"] . ") as " . $val;
} else {
if ($arFields[$val]["TYPE"] == "datetime") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["TYPE"] == "date") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if ($obUserFieldsSql) {
$strSqlSelect .= (strlen($strSqlSelect) <= 0 ? $arFields["ID"]["FIELD"] : "") . $obUserFieldsSql->GetSelect();
}
if (strlen($strSqlGroupBy) > 0) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
$strSqlSelect .= "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT";
} else {
$strSqlSelect = "%%_DISTINCT_%% " . $strSqlSelect;
}
}
// <-- SELECT
if ($obUserFieldsSql) {
$strSqlFrom .= " " . $obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
}
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy);
}
private static function BuilUserAddresseeSelectSql(&$params, $options = array())
{
global $DB, $DBType;
$userID = isset($params['USER_ID']) ? intval($params['USER_ID']) : 0;
if ($userID <= 0) {
$userID = CCrmSecurityHelper::GetCurrentUserID();
}
if (!is_array($options)) {
$options = array();
}
$startTime = isset($options['START_TIME']) ? $options['START_TIME'] : '';
if ($startTime !== '') {
$startTime = $DB->CharToDateFunction($DB->ForSql($startTime), 'FULL');
}
$top = isset($options['TOP']) ? intval($options['TOP']) : 0;
$allEntitySql = CCrmLiveFeedEntity::GetForSqlString(isset($params['ENTITY_TYPES']) ? $params['ENTITY_TYPES'] : null);
$sql = "SELECT L1.ID FROM b_sonet_log L1\n\t\t\t\tINNER JOIN b_sonet_log_right LR1\n\t\t\t\t\tON L1.ID = LR1.LOG_ID AND LR1.GROUP_CODE = 'U{$userID}'\n\t\t\t\t\tAND L1.ENTITY_TYPE IN ({$allEntitySql})";
if ($startTime !== '') {
$sql .= " AND L1.LOG_UPDATE >= {$startTime}";
}
if ($top > 0) {
$sql .= ' ORDER BY L1.LOG_UPDATE DESC';
CSqlUtil::PrepareSelectTop($sql, $top, $DBType);
}
return $sql;
}
public static function GetCount($arFilter)
{
$fields = self::GetFields();
return CSqlUtil::GetCount(CCrmDeal::TABLE_NAME, self::TABLE_ALIAS, $fields, $arFilter);
}
public function Synchronize()
{
$currentDay = time() + CTimeZone::GetOffset();
$currentDayEnd = ConvertTimeStamp(mktime(23, 59, 59, date('n', $currentDay), date('j', $currentDay), date('Y', $currentDay)), 'FULL', SITE_ID);
$count = 0;
if ($this->typeID === self::CurrentActivies) {
//Count of open user activities (start time: before tomorrow)
$filter = array('RESPONSIBLE_ID' => $this->userID, 'COMPLETED' => 'N', '<=START_TIME' => $currentDayEnd);
$count = CCrmActivity::GetCount($filter);
} elseif ($this->typeID === self::CurrentCompanyActivies) {
$count = CCrmActivity::GetCurrentQuantity($this->userID, CCrmOwnerType::Company);
} elseif ($this->typeID === self::CurrentContactActivies) {
$count = CCrmActivity::GetCurrentQuantity($this->userID, CCrmOwnerType::Contact);
} elseif ($this->typeID === self::CurrentLeadActivies) {
$count = CCrmActivity::GetCurrentQuantity($this->userID, CCrmOwnerType::Lead);
if (CCrmUserCounterSettings::GetValue(CCrmUserCounterSettings::ReckonActivitylessItems, true)) {
$leadTable = CCrmLead::TABLE_NAME;
$activityTable = CCrmActivity::USER_ACTIVITY_TABLE_NAME;
$statusStr = "'CONVERTED'";
$statusCount = 1;
$statuses = self::GetStatusList('STATUS');
$isFound = false;
foreach ($statuses as &$status) {
if (!$isFound) {
$isFound = $status['STATUS_ID'] === 'CONVERTED';
} else {
$statusStr .= ",'{$status['STATUS_ID']}'";
$statusCount++;
// Foolproof
if ($statusCount === 10) {
break;
}
}
}
unset($status);
global $DBType;
$sqlData = array('FROM' => '', 'WHERE' => "l.ASSIGNED_BY_ID = {$this->userID} AND l.STATUS_ID NOT IN({$statusStr}) AND l.ID NOT IN(SELECT a.OWNER_ID FROM {$activityTable} a WHERE a.USER_ID = 0 AND a.OWNER_TYPE_ID = 1)", 'GROUPBY' => '');
$count += CSqlUtil::GetRowCount($sqlData, $leadTable, 'l', $DBType);
}
} elseif ($this->typeID === self::CurrentDealActivies) {
$count = CCrmActivity::GetCurrentQuantity($this->userID, CCrmOwnerType::Deal);
if (CCrmUserCounterSettings::GetValue(CCrmUserCounterSettings::ReckonActivitylessItems, true)) {
$dealTable = CCrmDeal::TABLE_NAME;
$activityTable = CCrmActivity::USER_ACTIVITY_TABLE_NAME;
$stageStr = "'WON'";
$stageCount = 1;
$stages = self::GetStatusList('DEAL_STAGE');
$isFound = false;
foreach ($stages as &$stage) {
if (!$isFound) {
$isFound = $stage['STATUS_ID'] === 'WON';
} else {
$stageStr .= ",'{$stage['STATUS_ID']}'";
$stageCount++;
// Foolproof
if ($stageCount === 10) {
break;
}
}
}
unset($stage);
global $DB;
$dbResult = $DB->Query("SELECT COUNT(d.ID) AS CNT FROM {$dealTable} d WHERE d.ASSIGNED_BY_ID = {$this->userID} AND d.STAGE_ID NOT IN({$stageStr}) AND d.ID NOT IN(SELECT a.OWNER_ID FROM {$activityTable} a WHERE a.USER_ID = 0 AND a.OWNER_TYPE_ID = 2)", false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__);
$result = $dbResult->Fetch();
$count += is_array($result) ? intval($result['CNT']) : 0;
}
} elseif ($this->typeID === self::CurrentQuoteActivies) {
$count = 0;
if (CCrmUserCounterSettings::GetValue(CCrmUserCounterSettings::ReckonActivitylessItems, true)) {
$quoteTable = CCrmQuote::TABLE_NAME;
$statusStr = "'APPROVED'";
$statusCount = 1;
$statuses = self::GetStatusList('QUOTE_STATUS');
$isFound = false;
foreach ($statuses as &$status) {
if (!$isFound) {
$isFound = $status['STATUS_ID'] === 'APPROVED';
} else {
$statusStr .= ",'{$status['STATUS_ID']}'";
$statusCount++;
// Foolproof
if ($statusCount === 10) {
break;
}
}
}
unset($status);
global $DB;
$currentDay = time() + CTimeZone::GetOffset();
$currentDayEnd = ConvertTimeStamp(mktime(23, 59, 59, date('n', $currentDay), date('j', $currentDay), date('Y', $currentDay)), 'FULL', SITE_ID);
$currentDayEnd = $DB->CharToDateFunction($DB->ForSql($currentDayEnd), 'FULL');
$dbResult = $DB->Query("SELECT COUNT(q.ID) AS CNT FROM {$quoteTable} q WHERE q.ASSIGNED_BY_ID = {$this->userID} AND q.CLOSEDATE IS NOT NULL AND q.CLOSEDATE <= {$currentDayEnd} AND q.STATUS_ID NOT IN ({$statusStr})", false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__);
$result = $dbResult->Fetch();
$count += is_array($result) ? intval($result['CNT']) : 0;
}
}
if ($this->curValue !== $count) {
$this->curValue = $count;
if ($this->code !== '') {
CUserCounter::Set($this->userID, $this->code, $this->curValue, SITE_ID, '', false);
}
}
$this->RefreshLastCalculatedTime();
return $this->curValue;
}
private static function PrepareSearchQuery(&$arFilter, &$arSqlSearch)
{
global $DB;
$filter_keys = array_keys($arFilter);
for ($i = 0, $ic = count($filter_keys); $i < $ic; $i++) {
$val = $arFilter[$filter_keys[$i]];
if (!is_array($val) && strlen($val) <= 0 || $val == "NOT_REF") {
continue;
}
$key = strtoupper($filter_keys[$i]);
$operationInfo = CSqlUtil::GetFilterOperation($key);
$operation = $operationInfo['OPERATION'];
// Process only like operation
$isLikeOperation = $operation === 'LIKE' ? 'Y' : 'N';
$fieldName = $operationInfo['FIELD'];
switch ($fieldName) {
case 'ID':
$arSqlSearch[] = GetFilterQuery('CFM.ID', $val, 'N');
break;
case 'ENTITY_ID':
$arSqlSearch[] = GetFilterQuery('CFM.ENTITY_ID', $val, $isLikeOperation);
break;
case 'ELEMENT_ID':
if (is_array($val)) {
$ar = array();
foreach ($val as $v) {
$ar[] = intval($v);
}
if (!empty($ar)) {
$arSqlSearch[] = 'CFM.ELEMENT_ID IN (' . implode(',', $ar) . ')';
}
} else {
$arSqlSearch[] = 'CFM.ELEMENT_ID = ' . intval($val);
}
break;
case 'TYPE_ID':
$arSqlSearch[] = GetFilterQuery('CFM.TYPE_ID', $val, $isLikeOperation);
break;
case 'VALUE_TYPE':
if (is_array($val)) {
$valueTypeFilter = '';
foreach ($val as $v) {
$v = $DB->ForSql(trim(strval($v)));
if ($v === '') {
continue;
}
if ($valueTypeFilter !== '') {
$valueTypeFilter .= ', ';
}
$valueTypeFilter .= "'{$v}'";
}
if ($valueTypeFilter !== '') {
$arSqlSearch[] = "CFM.VALUE_TYPE IN ({$valueTypeFilter})";
}
} else {
$arSqlSearch[] = GetFilterQuery('CFM.VALUE_TYPE', $val, $isLikeOperation);
}
break;
case 'COMPLEX_ID':
$arSqlSearch[] = GetFilterQuery('CFM.COMPLEX_ID', $val, $isLikeOperation);
break;
case 'VALUE':
$arSqlSearch[] = GetFilterQuery('CFM.VALUE', $val, $isLikeOperation);
break;
case 'FILTER':
$arSqlFilterSearch = array();
if (is_array($val)) {
// Processing of filter parts
foreach ($val as $v) {
// Prepering filter part - items are joined by 'AND'
$arSqlInnerSearch = array();
self::PrepareSearchQuery($v, $arSqlInnerSearch);
if (!empty($arSqlInnerSearch)) {
$arSqlFilterSearch[] = '(' . implode(' AND ', $arSqlInnerSearch) . ')';
}
}
}
if (!empty($arSqlFilterSearch)) {
//$logic = isset($arFilter['LOGIC']) && is_string($arFilter['LOGIC']) ? strtoupper($arFilter['LOGIC']) : '';
//$logic = '';
//if($logic === '')
//{
// $logic = 'OR';
//}
// Prepering filter - parts are joined by 'OR'
//$arSqlSearch[] = '('.implode(" {$logic} ", $arSqlFilterSearch).')';
$arSqlSearch[] = '(' . implode(" OR ", $arSqlFilterSearch) . ')';
}
break;
}
}
}
if ($nTopCount > 0) {
$arNavParams['nTopCount'] = $nTopCount;
}
//$arEntityList = Array();
$arResult['EVENT'] = array();
$event = new CCrmInvoiceEvent();
if (!array_key_exists('TYPE', $arFilter)) {
$arFilter['TYPE'] = array_keys($arResult['EVENT_TYPES']);
}
$obRes = $event->GetList($arResult['SORT'], $arFilter, false, $arNavParams, array(), array());
$arResult['DB_LIST'] = $obRes;
$arResult['ROWS_COUNT'] = $obRes->NavRecordCount;
// Prepare raw filter ('=CREATED_BY' => 'CREATED_BY')
$arResult['DB_FILTER'] = array();
foreach ($arFilter as $filterKey => &$filterItem) {
$info = CSqlUtil::GetFilterOperation($filterKey);
$arResult['DB_FILTER'][$info['FIELD']] = $filterItem;
}
unset($filterItem);
$arUserDistinct = array();
$arUserInfo = array();
$arEventDescr = array();
while ($arEvent = $obRes->Fetch()) {
$arEvent['PATH_TO_EVENT_DELETE'] = CHTTP::urlAddParams($arParams['PATH_TO_EVENT_LIST'], array('action_' . $arResult['GRID_ID'] => 'delete', 'ID' => $arEvent['ID'], 'sessid' => bitrix_sessid()));
$arEvent['~FILES'] = $arEvent['FILES'];
//$arEvent['~EVENT_NAME'] = $arEvent['EVENT_NAME'];
$arUserDistinct[intval($arEvent['USER_ID'])] = true;
$arEvent['DATE_CREATE'] = $arEvent['DATE_CREATE'];
//$arEvent['EVENT_NAME'] = htmlspecialcharsbx($arEvent['~EVENT_NAME']);
if (!empty($arEvent['FILES'])) {
$i = 1;
protected function internalizeFilterFields(&$filter, &$fieldsInfo)
{
if (!is_array($filter)) {
return;
}
foreach ($filter as $k => $v) {
$operationInfo = CSqlUtil::GetFilterOperation($k);
$fieldName = $operationInfo['FIELD'];
$info = isset($fieldsInfo[$fieldName]) ? $fieldsInfo[$fieldName] : null;
if (!$info) {
unset($filter[$k]);
continue;
}
$fieldType = isset($info['TYPE']) ? $info['TYPE'] : '';
if ($fieldType === 'datetime') {
$filter[$k] = CRestUtil::unConvertDateTime($v);
}
}
CCrmEntityHelper::PrepareMultiFieldFilter($filter);
}
public static function GetList($arSort = array(), $arFilter = array(), $arSelect = array())
{
if (!CModule::IncludeModule('sale')) {
return array();
}
$arStatus = array();
self::ensureLanguageDefined();
$fieldsInfo = self::GetFieldsInfo();
$filterOperations = array();
foreach ($arFilter as $k => $v) {
$operationInfo = CSqlUtil::GetFilterOperation($k);
$operationInfo['FILTER_VALUE'] = $v;
$fieldName = $operationInfo['FIELD'];
$info = isset($fieldsInfo[$fieldName]) ? $fieldsInfo[$fieldName] : null;
if ($info) {
$operationInfo['FIELD_TYPE'] = $info['TYPE'];
$filterOperations[] = $operationInfo;
}
}
$res = CSaleStatus::GetList(array(), array('LID' => self::$languageID), false, false, array('ID', 'SORT', 'NAME'));
while ($row = $res->Fetch()) {
if ($row['ID'] === 'F') {
continue;
}
$arStatus[$row['ID']] = array('ID' => strval(ord($row['ID'])), 'ENTITY_ID' => 'INVOICE_STATUS', 'STATUS_ID' => $row['ID'], 'NAME' => $row['NAME'], 'NAME_INIT' => '', 'SORT' => $row['SORT'], 'SYSTEM' => 'N');
if (in_array($row['ID'], array('N', 'P', 'F', 'D'))) {
if ($row['ID'] === 'F') {
$arStatus[$row['ID']]['NAME_INIT'] = GetMessage('CRM_INVOICE_STATUS_F');
} elseif ($row['ID'] === 'D') {
$arStatus[$row['ID']]['NAME_INIT'] = GetMessage('CRM_INVOICE_STATUS_D');
} elseif ($row['ID'] === 'N') {
$arStatus[$row['ID']]['NAME_INIT'] = GetMessage('CRM_INVOICE_STATUS_N');
} elseif ($row['ID'] === 'P') {
$arStatus[$row['ID']]['NAME_INIT'] = GetMessage('CRM_INVOICE_STATUS_P');
}
$arStatus[$row['ID']]['SYSTEM'] = 'Y';
}
}
// filter
$arResult = array();
foreach ($arStatus as $row) {
$bRowSelected = true;
foreach ($filterOperations as $filterInfo) {
if (!self::CheckFilter($filterInfo, $row)) {
$bRowSelected = false;
break;
}
}
if ($bRowSelected) {
$arResult[] = $row;
}
}
// sort
if (count($arSort) > 0 && count($arResult) > 0) {
$arSortKeys = array_keys($arSort);
$arSortBy = $arSortDir = $arSortType = array();
$origFieldsNames = array_keys($fieldsInfo);
$numSorts = 0;
foreach ($arSortKeys as $sortKey) {
if (in_array($sortKey, $origFieldsNames, true)) {
$arSortBy[] = ToUpper($sortKey);
$arSortDir[] = ToUpper($arSort[$sortKey]) === 'DESC' ? SORT_DESC : SORT_ASC;
$sortType = SORT_REGULAR;
switch ($fieldsInfo[$sortKey]['TYPE']) {
case 'integer':
$sortType = SORT_NUMERIC;
break;
case 'string':
case 'char':
$sortType = SORT_STRING;
break;
}
$arSortType[] = $sortType;
$numSorts++;
}
}
if ($numSorts > 0) {
$fieldsNames = array();
foreach ($origFieldsNames as $fieldName) {
if (!in_array($fieldName, $arSortBy, true)) {
$fieldsNames[] = $fieldName;
}
}
$fieldsNames = array_merge($arSortBy, $fieldsNames);
$fieldsIndex = $columns = array();
$index = 0;
foreach ($fieldsNames as $fieldName) {
$columns[$index] = array();
$fieldsIndex[$fieldName] = $index++;
}
foreach ($arResult as $row) {
foreach ($row as $fieldName => $fieldValue) {
if (isset($fieldsIndex[$fieldName])) {
$columns[$fieldsIndex[$fieldName]][] = $fieldValue;
}
}
}
$args = array();
$index = 0;
foreach ($columns as &$column) {
$args[] =& $column;
if ($index < $numSorts) {
$args[] =& $arSortDir[$index];
$args[] =& $arSortType[$index];
}
$index++;
}
unset($column);
call_user_func_array('array_multisort', $args);
$numRows = count($arResult);
$arResult = array();
for ($index = 0; $index < $numRows; $index++) {
$row = array();
foreach ($origFieldsNames as $fieldName) {
$row[$fieldName] = $columns[$fieldsIndex[$fieldName]][$index];
}
$arResult[] = $row;
}
}
}
// select
if (count($arResult) > 0 && is_array($arSelect) && count($arSelect) > 0) {
$selectedFields = array_intersect($arSelect, array_keys($fieldsInfo));
if (count($selectedFields) > 0) {
$arStatus = $arResult;
$arResult = array();
foreach ($arStatus as $row) {
$newRow = array();
foreach ($selectedFields as $fieldName) {
$newRow[$fieldName] = $row[$fieldName];
}
$arResult[] = $newRow;
}
}
}
return $arResult;
}
public function ImportResponsibility($entityTypeID, $userID, $top)
{
if (!CCrmOwnerType::IsDefined($entityTypeID)) {
return false;
}
$userID = max(intval($userID), 0);
$top = max(intval($top), 0);
$typeID = CCrmSonetSubscriptionType::Observation;
global $DB;
$tableName = self::TABLE_NAME;
$slEntityType = $DB->ForSql(CCrmLiveFeedEntity::GetByEntityTypeID($entityTypeID));
$selectSql = '';
if ($entityTypeID === CCrmOwnerType::Lead || $entityTypeID === CCrmOwnerType::Contact || $entityTypeID === CCrmOwnerType::Company || $entityTypeID === CCrmOwnerType::Deal || $entityTypeID === CCrmOwnerType::Activity) {
if ($entityTypeID === CCrmOwnerType::Lead) {
$selectTableName = CCrmLead::TABLE_NAME;
$userFieldName = 'ASSIGNED_BY_ID';
} elseif ($entityTypeID === CCrmOwnerType::Contact) {
$selectTableName = CCrmContact::TABLE_NAME;
$userFieldName = 'ASSIGNED_BY_ID';
} elseif ($entityTypeID === CCrmOwnerType::Company) {
$selectTableName = CCrmCompany::TABLE_NAME;
$userFieldName = 'ASSIGNED_BY_ID';
} elseif ($entityTypeID === CCrmOwnerType::Deal) {
$selectTableName = CCrmDeal::TABLE_NAME;
$userFieldName = 'ASSIGNED_BY_ID';
} else {
$selectTableName = CCrmActivity::TABLE_NAME;
$userFieldName = 'RESPONSIBLE_ID';
}
$userFieldCondition = $userID > 0 ? " = {$userID}" : ' > 0';
$selectSql = "SELECT {$userFieldName}, '{$slEntityType}', ID, {$typeID} FROM {$selectTableName} WHERE {$userFieldName}{$userFieldCondition} ORDER BY ID DESC";
}
if ($selectSql === '') {
return false;
}
if ($top > 0) {
CSqlUtil::PrepareSelectTop($selectSql, $top, self::DB_TYPE);
}
$deleteSql = "DELETE QUICK FROM {$tableName} WHERE SL_ENTITY_TYPE = '{$slEntityType}' AND TYPE_ID = {$typeID}";
if ($userID > 0) {
$deleteSql .= " AND USER_ID = {$userID}";
}
$DB->Query($deleteSql, false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__);
$insertSql = "INSERT INTO {$tableName}(USER_ID, SL_ENTITY_TYPE, ENTITY_ID, TYPE_ID) " . $selectSql;
$dbResult = $DB->Query($insertSql, false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__);
return is_object($dbResult);
}
protected function internalizeFilterFields(&$filter, &$fieldsInfo)
{
if (!is_array($filter)) {
return;
}
foreach ($filter as $k => $v) {
$operationInfo = CSqlUtil::GetFilterOperation($k);
$fieldName = $operationInfo['FIELD'];
$info = isset($fieldsInfo[$fieldName]) ? $fieldsInfo[$fieldName] : null;
if (!$info) {
unset($filter[$k]);
continue;
}
$operation = substr($k, 0, strlen($k) - strlen($fieldName));
if (isset($info['FORBIDDEN_FILTERS']) && is_array($info['FORBIDDEN_FILTERS']) && in_array($operation, $info['FORBIDDEN_FILTERS'], true)) {
unset($filter[$k]);
continue;
}
$fieldType = isset($info['TYPE']) ? $info['TYPE'] : '';
if (($fieldType === 'crm_status' || $fieldType === 'crm_company' || $fieldType === 'crm_contact') && ($operation === '%' || $operation === '%=' || $operation === '=%')) {
//Prevent filtration by LIKE due to performance considerations
$filter["={$fieldName}"] = $v;
unset($filter[$k]);
continue;
}
if ($fieldType === 'datetime') {
$filter[$k] = CRestUtil::unConvertDateTime($v);
} elseif ($fieldType === 'date') {
$filter[$k] = CRestUtil::unConvertDate($v);
}
}
CCrmEntityHelper::PrepareMultiFieldFilter($filter, array(), '=%', true);
}
