public function getOSData()
 {
     $cols = array('meta1', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income');
     $sql = 'select adv.platform_id, ';
     $sql .= getReportGeneralSelects() . 'from ';
     $sql .= getReportFilters('platforms/os', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id)');
     $sql .= ' and adv.platform_id > 0 group by adv.platform_id ';
     $result = DB::getRows($sql);
     $cnt = count($result);
     DB::query("delete from bt_c_statcache where user_id='" . DB::quote(getUserId()) . "'  and type='platos'");
     $bulk = new DB_Bulk_Insert('bt_c_statcache', array('user_id', 'type', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income', 'meta1'));
     foreach ($result as $row) {
         $row['platform_name'] = Browser::getPlatformName($row['platform_id']);
         if (!$row['platform_name']) {
             $row['platform_name'] = '[unknown os]';
         }
         $bulk->insert(array(DB::quote(getUserId()), "'platos'", $row['clicks'], $row['click_throughs'], $row['click_through_rates'], $row['leads'], $row['conv'], $row['payout'], $row['epc'], $row['income'], "'" . DB::quote($row['platform_name']) . "'"));
     }
     $bulk->execute();
     $sql = "select * from bt_c_statcache where user_id='" . DB::quote(getUserId()) . "'  and type='platos' ";
     $sql .= getReportOrder($cols);
     $sql .= getReportLimits();
     $result = DB::getRows($sql);
     return array('data' => $result, 'cnt' => $cnt, 'cols' => $cols);
 }
 public function getLifetimeData()
 {
     $cols = array('lifetime', 'leads', 'payout', 'income');
     $cnt_query = "select count(1) from (select 1 from";
     $cnt_query .= getReportFilters('analyze/lifetime');
     $cnt_query .= " GROUP BY click.lifetime) thedata";
     $cnt = DB::getVar($cnt_query);
     $sql = 'select click.lifetime, ';
     $sql .= getReportGeneralSelects() . ' from ';
     $sql .= getReportFilters('analyze/lifetime', '');
     $sql .= ' and lead=1 group by click.lifetime ';
     $sql .= getReportOrder($cols);
     $sql .= getReportLimits();
     $result = DB::getRows($sql);
     foreach ($result as &$row) {
         $row['lifetime'] = $this->LifetimeTime($row['lifetime']);
     }
     return array('data' => $result, 'cnt' => $cnt, 'cols' => $cols);
 }
 public function getTimezoneData()
 {
     $cols = array('timezone', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income');
     $cnt_query = "select count(1) from (select 1 from";
     $cnt_query .= getReportFilters('geography/time_zones', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id) left join bt_g_geo_locations geo on adv.location_id=geo.location_id ');
     $cnt_query .= " group by geo.timezone) thedata";
     $cnt = DB::getVar($cnt_query);
     $sql = 'select geo.timezone, ';
     $sql .= getReportGeneralSelects() . 'from ';
     $sql .= getReportFilters('geography/time_zones', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id) left join bt_g_geo_locations geo on adv.location_id=geo.location_id ');
     $sql .= ' group by geo.timezone ';
     $sql .= getReportOrder($cols);
     $sql .= getReportLimits();
     $result = DB::getRows($sql);
     foreach ($result as &$row) {
         if (!$row['timezone']) {
             $row['timezone'] = '[unknown timezone]';
         }
     }
     return array('data' => $result, 'cnt' => $cnt, 'cols' => $cols);
 }
Пример #4
0
function dayparting_data($order = '', $use_cache = false)
{
    $offset = date('Z');
    DB::query("delete from bt_c_statcache where user_id='" . DB::quote(getUserId()) . "'  and type='daypart'");
    /****GET SPENDING****/
    $time = grab_timeframe();
    $spend_from = date("Y-m-d", $time['from']);
    $spend_to = date("Y-m-d", $time['to']);
    $sql = "select sum(amount) as cost from bt_u_spending where date >= '{$spend_from}' and date <= '{$spend_to}' ";
    $sql .= getSpendingReportFilters('bt_u_spending', getReportOptionsForPage('dateparting/date'));
    $spending_amt = DB::getVar($sql);
    /****END SPENDING****/
    $bulk = new DB_Bulk_Insert('bt_c_statcache', array('user_id', 'time_from', 'time_to', 'type'));
    for ($i = 0; $i < 24; $i++) {
        $bulk->insert(array(getUserId(), $i, "0", "'daypart'"));
    }
    $bulk->execute();
    $cols = array('time_from_int', 'clicks', 'leads', 'conv', 'payout', 'epc', 'income', 'cost', 'net', 'roi');
    $sql = "select FROM_UNIXTIME(time + {$offset},'%H') as time_from, ";
    $sql .= getReportGeneralSelects() . ' from ';
    $sql .= getReportFilters('dateparting/date', '');
    $sql .= ' group by time_from ';
    $result = DB::getRows($sql);
    $total_clicks = 0;
    foreach ($result as $row) {
        $total_clicks += $row['clicks'];
    }
    $st = DB::prepare("update bt_c_statcache set clicks=?, leads=?, conv=?, payout=?, epc=?, income=?, cost=?, net=?, roi=? where user_id=? and type='daypart' and time_from=?");
    foreach ($result as $row) {
        //simple ratio.
        $cost = $row['clicks'] * $spending_amt / $total_clicks;
        $row['cost'] = round($cost, 2);
        $row['net'] = calculate_net($row['income'], $row['cost']);
        $row['roi'] = calculate_roi($row['income'], $row['cost']);
        $arr = array($row['clicks'], $row['leads'], $row['conv'], $row['payout'], $row['epc'], $row['income'], $row['cost'], $row['net'], $row['roi'], getUserID(), (int) $row['time_from']);
        $st->execute($arr);
    }
    $sql = "select *,convert(`time_from`,UNSIGNED INTEGER) as time_from_int,? as total_spend from bt_c_statcache where user_id=? and type='daypart' ";
    $sql .= getReportOrder($cols);
    $st = DB::prepare($sql);
    $st->execute(array($spending_amt, getUserID()));
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);
    //show breakdown
    $total_clicks = 0;
    $total_leads = 0;
    $total_income = 0;
    $total_cost = 0;
    $cnt = 0;
    foreach ($rows as &$row) {
        $row['time_from_int'] = dayparting_time($row['time_from_int']) . ' - ' . dayparting_time($row['time_from_int'] + 1);
        $total_clicks += $row['clicks'];
        $total_leads += $row['leads'];
        $total_income += $row['income'];
        $total_cost = $row['total_spend'];
    }
    $data_array = array();
    $data_array['time_from_int'] = 'Totals for report';
    $data_array['clicks'] = $total_clicks;
    $data_array['leads'] = $total_leads;
    $data_array['conv'] = calculate_conv($total_clicks, $total_leads);
    $data_array['payout'] = calculate_payout($total_leads, $total_income);
    $data_array['epc'] = calculate_epc($total_clicks, $total_income);
    $data_array['income'] = $total_income;
    $data_array['cost'] = $total_cost;
    $data_array['net'] = calculate_net($total_income, $total_cost);
    $data_array['roi'] = calculate_roi($total_income, $total_cost);
    $rows[] = $data_array;
    echo getDatatablesReportJson($rows, count($rows), $cols);
}