public function __construct() { DashboardCommon::$is_su = $_SESSION['lm_auth']['account_type'] == 'super' ? true : false; $gsm_temp = $_SESSION['lm_auth']['client_id']; $gsm_id = ""; if (!empty($gsm_temp)) { $gsm_id_arr = explode("#", $gsm_temp); foreach ($gsm_id_arr as $gsm) { $gsm_ids .= $gsm . ','; } $gsm_id = rtrim($gsm_ids, ','); } $this->client_gsm_id = $gsm_id; //use gsm number from assigned campaigns instead of client table require_once dirname(__FILE__) . '/Client.php'; $campaigns = Client::get_campaigns(); $gsm_id = ""; foreach ($campaigns as $campaign) { $gsm_id .= $campaigns['gsm_number'] . ','; } $gsm_id = rtrim($gsm_ids, ','); $this->client_gsm_id = $gsm_id; $this->ga_view_id = $_SESSION['lm_auth']['ga_view_id']; $this->client_unbounce_ids = $this->fetch_unbounce_ids(); }
public function get_calls_table_data_client() { 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; } //if(empty($union_array)) return array(); $inner_sql = implode(" UNION ", $union_array); $json_array = array(); $campaign_start_limit = ""; if ($first_campaign_start != '') { $campaign_start_limit = "WHERE call_start>='{$first_campaign_start}'"; } $sql = "SELECT *, DATE_FORMAT(call_start,'%b %e, %Y') as call_date,\n DATE_FORMAT(call_start,'%h:%i %p') as call_time,\n IF( id IN (SELECT id FROM calls {$campaign_start_limit} GROUP BY callerid HAVING MIN(call_start)),'New','') AS new_call\n FROM ({$inner_sql}) as calls"; $sql .= " WHERE call_start >= '" . $this->from . "' AND call_start<= '" . $this->to . "'\n AND test_data=0 ORDER BY call_start DESC "; //echo $sql;die; $r = DashboardCommon::db()->Execute($sql); foreach ($r as $res) { $json_array[] = $res; } return $json_array; }
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 getROIChartData($from, $to) { if ($is_su) { //admin roi not supported yet return array(); } global $db; global $client_gsm_id; //use gsm ids linked to campaigns instead of client table require_once dirname(__FILE__) . '/Client.php'; $campaigns = Client::get_campaigns(); print_r($campaigns); die; }