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