public function get_sms_data()
{
$where = "";
if (!DashboardCommon::is_su()) {
$where = " AND gsm_number IN( " . $this->get_gsm_number() . " ) {$this->sms_data_limit_clause}\n ORDER BY sms_dt DESC";
}
$dates = createDateRangeArray($this->from, $this->to);
$data_array = array();
foreach ($dates as $date) {
$sql = "SELECT count(*) as total FROM sms ";
$sql .= "WHERE sms_dt LIKE '%" . $date . "%' {$where}";
// echo $sql;
$res = DashboardCommon::db()->Execute($sql);
$data_array[] = array('elapsed' => userdate($date), 'value' => $res->fields['total']);
}
return $data_array;
}
function get_weekday_stats()
{
//only for super admin
if (!DashboardCommon::is_su()) {
return null;
}
$sat = strtotime("last saturday");
$sat = date('w', $sat) == date('w') ? $sat + 7 * 86400 : $sat;
$fri = strtotime(date("Y-m-d", $sat) . " +6 days");
$from = date("Y-m-d", $sat);
//for current week only
$to = date("Y-m-d", $fri);
//for current week only
$sql = "SELECT DAYNAME(atr.call_start) as dayname,count(*) as total \n FROM week_days wd \n LEFT JOIN ( SELECT * FROM calls WHERE call_start >= '" . $this->from . "' AND call_start <= '" . $this->to . "') atr\n ON wd.week_day_num = DAYOFWEEK(atr.call_start)\n GROUP BY\n DAYOFWEEK(atr.call_start)";
$this_week_rec = DashboardCommon::db()->Execute($sql);
$saturday = $sunday = $monday = $tuesday = $wednesday = 0;
$thursday = $friday = 0;
// $data = array();
// while (!$this_week_rec->EOF) {
// $k = $this_week_rec->fields['dayname'];
// $data[$k]= $this_week_rec->fields['total'];
// $this_week_rec->MoveNext();
// }
//
// return array_keys($data, max($data));
while (!$this_week_rec->EOF) {
$daynames = $this_week_rec->fields['dayname'];
$totalcalls = $this_week_rec->fields['total'];
if ($daynames == 'Saturday') {
$saturday = $this_week_rec->fields['total'];
}
if ($daynames == 'Sunday') {
$sunday = $this_week_rec->fields['total'];
}
if ($daynames == 'Monday') {
$monday = $this_week_rec->fields['total'];
}
if ($daynames == 'Tuesday') {
$tuesday = $this_week_rec->fields['total'];
}
if ($daynames == 'Wednesday') {
$wednesday = $this_week_rec->fields['total'];
}
if ($daynames == 'Thursday') {
$thursday = $this_week_rec->fields['total'];
}
if ($daynames == 'Friday') {
$friday = $this_week_rec->fields['total'];
}
$this_week_rec->MoveNext();
}
$arr = array('Saturday' => $saturday, 'Sunday' => $sunday, 'Monday' => $monday, 'Tuesday' => $tuesday, 'Wednesday' => $wednesday, 'Thursday' => $thursday, 'Friday' => $friday);
$max_day = array_keys($arr, max($arr));
$avg_calltime_sql = "SELECT sum(duration) as total_call_time, count(*) as total_records, \n sum(duration) / count(*) as avg_time\n FROM \n (\n SELECT call_end-call_start as duration\n FROM calls\n WHERE call_start >= '" . $this->from . "' AND call_start <= '" . $this->to . "'\n ) as dt";
$avg_calltime_res = DashboardCommon::db()->Execute($avg_calltime_sql);
return array('weekday' => $max_day[0], 'avg_call_time' => $avg_calltime_res->fields['avg_time']);
}
public static function getFirstCampaignStartDate()
{
$client = "";
if (!DashboardCommon::is_su()) {
$client = "client_id='" . DashboardCommon::get_client_id() . "' AND";
}
$sql = "SELECT MIN(start_date) as start_date FROM campaigns WHERE {$client} start_date!='0000-00-00'";
return DashboardCommon::executeAndReturnSingleColResultAndCache($sql, 'start_date');
}
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;
}
function get_weekday_stats()
{
//only for super admin
if (!DashboardCommon::is_su()) {
return null;
}
$sat = strtotime("last saturday");
$sat = date('w', $sat) == date('w') ? $sat + 7 * 86400 : $sat;
$fri = strtotime(date("Y-m-d", $sat) . " +6 days");
$from = date("Y-m-d", $sat);
//for current week only
$to = date("Y-m-d", $fri);
//for current week only
$sql = "SELECT DAYNAME(atr.email_date) as dayname,count(*) as total \n FROM week_days wd \n LEFT JOIN ( SELECT * FROM emails WHERE email_date >= CONVERT_TZ('" . $this->from . "','+00:00','-04:00')\n AND email_date <= CONVERT_TZ('" . $this->to . "','+00:00','-04:00') ) atr\n ON wd.week_day_num = DAYOFWEEK(atr.email_date)\n GROUP BY\n DAYOFWEEK(atr.email_date)";
$this_week_rec = DashboardCommon::db()->Execute($sql);
$saturday = $sunday = $monday = $tuesday = $wednesday = 0;
$thursday = $friday = 0;
while (!$this_week_rec->EOF) {
$daynames = $this_week_rec->fields['dayname'];
$totalcalls = $this_week_rec->fields['total'];
if ($daynames == 'Saturday') {
$saturday = $this_week_rec->fields['total'];
}
if ($daynames == 'Sunday') {
$sunday = $this_week_rec->fields['total'];
}
if ($daynames == 'Monday') {
$monday = $this_week_rec->fields['total'];
}
if ($daynames == 'Tuesday') {
$tuesday = $this_week_rec->fields['total'];
}
if ($daynames == 'Wednesday') {
$wednesday = $this_week_rec->fields['total'];
}
if ($daynames == 'Thursday') {
$thursday = $this_week_rec->fields['total'];
}
if ($daynames == 'Friday') {
$friday = $this_week_rec->fields['total'];
}
$this_week_rec->MoveNext();
}
$arr = array('Saturday' => $saturday, 'Sunday' => $sunday, 'Monday' => $monday, 'Tuesday' => $tuesday, 'Wednesday' => $wednesday, 'Thursday' => $thursday, 'Friday' => $friday);
$max_day = array_keys($arr, max($arr));
//Peak time
$sql_peak = "SELECT EXTRACT(hour FROM CONVERT_TZ(email_date,'+00:00','+04:00')) as hour,count(*)\n FROM emails GROUP BY EXTRACT(hour FROM CONVERT_TZ(email_date,'+00:00','+04:00'))\n ORDER BY count(*) DESC LIMIT 1";
$res_peak = DashboardCommon::db()->Execute($sql_peak);
$peak_time_f = $res_peak->fields['hour'];
$peak_time_t = $peak_time_f + 1;
$today = new DateTime('NOW');
$today->setTime($peak_time_f, 0, 0);
$today2 = new DateTime('NOW');
$today2->setTime($peak_time_t, 0, 0);
$peak_time = $today->format('H A') . " - " . $today2->format('H A');
// Average Emails per month
$sql_avg_month = "select monthname(email_date) email_date,count(*) as total_sum \n FROM emails GROUP BY monthname(email_date)";
$res_avg_month = DashboardCommon::db()->Execute($sql_avg_month);
$avg_total = $res_avg_month->recordCount();
$total_sum = 0;
while (!$res_avg_month->EOF) {
$total_sum += $res_avg_month->fields['total_sum'];
$res_avg_month->MoveNext();
}
$avg_per_month = ceil($total_sum / $avg_total);
return array('average' => $avg_per_month, 'weekday' => $max_day[0], 'peak_time' => $peak_time);
}
public function getLeadsChartData($period)
{
if (!DashboardCommon::is_su()) {
$client_calls_where = " AND gsm_number IN (" . $this->get_gsm_number() . ") AND test_data=0\n {$this->calls_data_limit_clause}\n ORDER BY call_start DESC";
$client_email_where = " AND client_id IN ('" . implode('\',\'', $this->get_unbounce_ids()) . "') AND test_data=0\n ORDER BY email_date DESC";
}
$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)) {
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} ";
$rc = DashboardCommon::db()->Execute($q1);
$c_total = $rc->fields['total_calls'];
if ($c_total == '') {
$c_total = 0;
}
$q2 = "SELECT count(*) as total_emails FROM emails WHERE email_date LIKE '%" . $date_filtered . "%' {$client_email_where}";
$re = DashboardCommon::db()->Execute($q2);
$e_total = $re->fields['total_emails'];
if ($e_total == '') {
$e_total = 0;
}
$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 FROM calls \n 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 email_date LIKE '%" . $date_filtered . "%' {$client_email_where}\n ) as b";
//echo $sql_leads; die();
$res_leads = DashboardCommon::db()->Execute($sql_leads);
$tot_leads = $res_leads->fields['total_leads'] != '' ? $res_leads->fields['total_leads'] : 0;
$row = array('y' => $date_filtered, 'a' => $c_total, 'b' => $e_total, 'c' => $tot_leads);
$data[] = $row;
}
}
return $data;
}
public static function getFirstCampaignStartDate()
{
$client = "";
if (!DashboardCommon::is_su()) {
$client = "client_id='" . DashboardCommon::get_client_id() . "' AND";
}
$sql = "SELECT MIN(start_date) as start_date FROM campaigns WHERE {$client} start_date!='0000-00-00'";
$r = DashboardCommon::db()->Execute($sql);
return $r->fields['start_date'];
}
