public static function executeAndReturnSingleColResultAndCache($sql, $col, $storeData = true, $memcacheHash = "", $returnZeroInsteadOfFalse = true) { $ret = false; if (!$memcacheHash) { $memcacheHash = md5($sql); } $cachedData = DashboardCommon::getMemcacheData($memcacheHash); if (!$cachedData) { $res = DashboardCommon::db()->Execute($sql); $field = $res->fields[$col]; if ($field == '') { $field = 0; } if ($storeData) { DashboardCommon::setMemcacheData($memcacheHash, $field); } else { DashboardCommon::setMemcacheData($memcacheHash, $res); } $ret = $field; } else { $ret = $cachedData; } if ($returnZeroInsteadOfFalse === true) { if ($ret === false) { $ret = 0; } } return $ret; }
public function getLeadsChartData($period) { if (!DashboardCommon::is_su()) { return $this->getLeadsChartDataClient($period); } $data = array(); $period_days = array(); $date_filter = "Y-m-d"; if ($period == 'lifetime') { $period_days = getMonths($this->from, $this->to); $date_filter = "Y-m"; } elseif ($period == 'last_30_days' || $period == 'last_7_days' || $period == 'yesterday' || $period == 'month' || $period == 'daily' || $period == 'today' || $period == 'this_month' || $period == 'custom' || $period == 'last_month') { $period_days = createDateRangeArray($this->from, $this->to); } else { $period_days = createDateRangeArray($this->from, $this->to); } if (!empty($period_days)) { $callerGrpSql = "SELECT id FROM calls GROUP BY callerid HAVING MIN(call_start)"; $callerSqlKey = DashboardCommon::generateMemcacheHash($callerGrpSql); $callerMemcachedData = DashboardCommon::getMemcacheData($callerSqlKey); if (!$callerMemcachedData) { $callerRes = DashboardCommon::db()->Execute($callerGrpSql); $callerRows = $callerRes->GetRows(); $commaSepratedCallerIds = ""; foreach ($callerRows as $row) { if ($commaSepratedCallerIds) { $commaSepratedCallerIds .= "," . $row['id']; } else { $commaSepratedCallerIds .= $row['id']; } } DashboardCommon::setMemcacheData($callerSqlKey, $callerMemcachedData); } else { $callerRes = $callerMemcachedData; } foreach ($period_days as $date) { if ($date_filter === 'Y-m-d') { $date_filtered = $date; } else { $date_filtered = date_format($date, "{$date_filter}"); } $q1 = "SELECT count(*) as total_calls FROM calls WHERE call_start LIKE '" . $date_filtered . "%' {$client_calls_where} "; $c_total = DashboardCommon::executeAndReturnSingleColResultAndCache($q1, 'total_calls'); $q2 = "SELECT count(*) as total_emails FROM emails WHERE \n CONVERT_TZ(email_date,'+00:00','+04:00') LIKE '" . $date_filtered . "%' {$client_email_where}"; $e_total = DashboardCommon::executeAndReturnSingleColResultAndCache($q2, 'total_emails'); $sql_leads = "SELECT * , tcalls+temails as total_leads\n FROM\n (SELECT \n /*COUNT(DISTINCT /*DATE_FORMAT(call_start,'%y-%m-%d') ,// gsm_number) as tcalls*/\n COUNT(*) AS tcalls\n FROM calls \n WHERE\n id IN ( {$commaSepratedCallerIds} ) AND\n /*call_start LIKE '%" . $date_filtered . "%' {$client_calls_where}*/\n call_start LIKE '" . $date_filtered . "%' {$client_calls_where}\n ) as a , \n (SELECT \n COUNT(*) as temails\n FROM emails \n WHERE\n CONVERT_TZ(email_date,'+00:00','+04:00') LIKE '" . $date_filtered . "%' {$client_email_where}\n ) as b"; //echo $sql_leads; die(); $tot_leads = DashboardCommon::executeAndReturnSingleColResultAndCache($sql_leads, 'total_leads'); $row = array('y' => $date_filtered, 'a' => $c_total, 'b' => $e_total, 'c' => $tot_leads); $data[] = $row; } } return $data; }