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);
}
