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);
}
public function getMobileData()
{
if (!bt_mobile_enabled()) {
error404();
}
$groups = array();
if (BTAuth::user()->getPref("user_mobile_breakdown_1")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_1");
if (BTAuth::user()->getPref("user_mobile_breakdown_2")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_2");
if (BTAuth::user()->getPref("user_mobile_breakdown_3")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_3");
if (BTAuth::user()->getPref("user_mobile_breakdown_4")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_4");
}
}
}
}
if (!$groups) {
$groups = array('devices.type');
}
$breakdown_cols = array("devices.brand" => "Brand", "devices.type" => "Device Type", "devices.os" => "OS", "devices.os_version" => "OS Version", "devices.browser" => "Browser", "devices.browser_version" => "Browser Version", "orgs.name" => "Carrier/ISP");
foreach ($groups as $group) {
if (!isset($breakdown_cols[$group])) {
$groups = array('devices.type');
break;
}
}
$group = " ";
$group .= implode(',', $groups);
//$cols = array('browser_name', 'clicks', 'leads', 'conv', 'payout', 'epc', 'avg_cpc', 'income', 'cost', 'net', 'roi');
$cols = array('label', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income');
$sql = 'select ' . $group . ', ';
$sql .= getReportGeneralSelects() . 'from ';
$sql .= getReportFilters('platforms/mobile', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id) LEFT JOIN bt_s_device_data AS devices on adv.device_id=devices.device_id
left join bt_g_organizations as orgs on adv.org_id=orgs.org_id ');
$sql .= " and adv.device_id>0 and devices.hash<>'d41d8cd98f00b204e9800998ecf8427e' group by " . $group;
$sql .= ' order by ' . $group . ' ';
$click_results = DB::getRows($sql);
for ($i = 0; $i < count($groups); $i++) {
if ($pos = strpos($groups[$i], '.')) {
$groups[$i] = substr($groups[$i], $pos + 1);
}
}
$final_rows = array();
$tree = makeHierarchical($click_results, $groups);
unset($click_results);
//no longer needed
foreach ($tree as $node) {
getRowsFromTreeNode($node, 0, $final_rows);
}
return array('data' => $final_rows, 'cols' => $cols, 'cnt' => count($final_rows));
}
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);
}
