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 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;
}
