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