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 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; }
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 function delete($id) { $id = (int) $id; $sql = "DELETE FROM campaigns WHERE id={$id}"; return DashboardCommon::db()->Execute($sql); }
public static function getFirstCampaignStartDate() { $sql = "SELECT MIN(start_date) as start_date FROM campaigns\n WHERE client_id='" . DashboardCommon::get_client_id() . "' AND start_date!='0000-00-00'"; $r = DashboardCommon::db()->Execute($sql); return $r->fields['start_date']; }