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