public function __construct()
{
parent::__construct();
$this->ga_client_email = '*****@*****.**';
$this->ga_key_file = realpath(dirname(__FILE__) . '/../../data/keys.p12');
$this->setPeriod('lifetime');
}
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 getLeadsChartDataClient($period)
{
require_once dirname(__FILE__) . '/Client.php';
$campaigns = Client::get_campaigns();
$first_campaign_start = Client::getFirstCampaignStartDate();
//if(empty($campaigns)) return array();
$union_array = array();
foreach ($campaigns as $campaign) {
$sql = "(SELECT * FROM calls WHERE gsm_number='" . $campaign['gsm_number'] . "'";
if ($campaign['start_date'] != '0000-00-00') {
//if($date < $campaign['start_date']) continue;
$sql .= " AND call_start>='" . $campaign['start_date'] . "'";
}
if ($campaign['end_date'] != '0000-00-00') {
//if($date > $campaign['end_date']) continue;
$sql .= " AND call_end<='" . $campaign['end_date'] . "'";
}
$sql .= ")";
$union_array[] = $sql;
}
$inner_sql = implode(" UNION ", $union_array);
if ($inner_sql == '') {
$inner_sql = "(SELECT * FROM calls WHERE id = NULL)";
}
$client_calls_where = " AND test_data=0 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);
}
$campaign_start_limit = "";
if ($first_campaign_start != '') {
$campaign_start_limit = "WHERE call_start>='{$first_campaign_start}'";
}
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 ({$inner_sql}) AS calls WHERE call_start LIKE '%" . $date_filtered . "%' {$client_calls_where} ";
$c_total = 0;
$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 ({$inner_sql}) AS calls \n WHERE\n id IN ( SELECT id FROM calls {$campaign_start_limit} GROUP BY callerid HAVING MIN(call_start) ) AND\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($q1, '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 __construct()
{
parent::__construct();
}
public function delete($id)
{
$id = (int) $id;
$sql = "DELETE FROM campaigns WHERE id={$id}";
return DashboardCommon::db()->Execute($sql);
}
}
////////////////////////////////////////////////////////////////////////////
// SAVE DATE RANGE
////////////////////////////////////////////////////////////////////////////
if ($_GET['act'] == 'save_date_range') {
if (isset($_GET['period'])) {
$period = $_GET['period'];
$from = $_GET['from'];
$to = $_GET['to'];
require '../classes/dashboard/DashboardCommon.php';
DashboardCommon::saveDateRangeFilter($period, $to, $from);
echo "1";
die;
}
echo "0";
die;
}
if ($_GET['act'] == 'get_saved_date_range') {
require '../classes/dashboard/DashboardCommon.php';
$date_range = DashboardCommon::getSavedDateRangeFilter();
header('Content-Type: application/json');
echo json_encode($date_range);
die;
}
if ($_GET['act'] == 'get_campaign_start_date') {
require '../classes/dashboard/DashboardCommon.php';
$start_date = DashboardCommon::getFirstCampaignStartDate();
echo json_encode($start_date);
die;
}
}
<?php
error_reporting(0);
include_once 'file_include.php';
include 'process.php';
require_once dirname(__FILE__) . '/classes/dashboard/DashboardCommon.php';
$saved_dates = DashboardCommon::getSavedDateRangeFilter();
$LM_PERIOD = $saved_dates['period'];
$LM_PERIOD_FROM = date('F d, Y', strtotime($saved_dates['from']));
$LM_PERIOD_TO = date('F d, Y', strtotime($saved_dates['to']));
######################
#
# POST SECTION
#
######################
if (isset($_POST['mode'])) {
$inner_page = $_POST['request_page'];
if ($inner_page == "") {
$inner_page = "index";
}
include "query.include/" . $inner_page . ".php";
}
//end if(isset($_POST['mode']))
######################
#
# GET SECTION
#
######################
if (isset($_GET['mode'])) {
$inner_page = $_GET['request_page'];
if ($inner_page == "") {
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'];
}
