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); }
function runBreakdown($user_pref) { //grab time $time = grab_timeframe(); //get breakdown pref $mysql['user_id'] = DB::quote(getUserID()); $filtered = getFilteredCondition(); //breakdown should be hour, day, month, or year. $breakdown = BTAuth::user()->getPref('breakdown'); //first delete old report $breakdown_sql = "\n\t\t\tDELETE\n\t\t\tFROM bt_c_statcache\n\t\t\tWHERE user_id='" . $mysql['user_id'] . "' and type='breakdown'\n\t\t"; DB::query($breakdown_sql); //($breakdown_sql); //find where to start from. $datetype = ''; //breakdown format if ($breakdown == 'day') { $datetype = '%Y-%m-%d'; } else { if ($breakdown == 'month') { $datetype = '%Y-%m'; } else { if ($breakdown == 'year') { $datetype = '%Y'; } } } //BTApp::end(); $start = $time['from']; $end = $time['to']; $spend_from = date("Y-m-d", $start); $spend_to = date("Y-m-d", $end); /****GET SPENDING****/ $sql = "select sum(amount) as cost, FROM_UNIXTIME(UNIX_TIMESTAMP(date),'{$datetype}') as date from bt_u_spending \n\t\t\t\twhere date >= '{$spend_from}' and date <= '{$spend_to}' "; $sql .= getSpendingReportFilters('bt_u_spending', getReportOptionsForPage('overview/breakdown')); $sql .= "group by date"; $spending_data = DB::getRows($sql, 'date'); /****END SPENDING****/ $offset = date('Z'); $bulk = new DB_Bulk_Insert('bt_c_statcache', array('user_id', 'time_from', 'time_to', 'type')); $x = 0; while ($end > $start) { if ($breakdown == 'day') { $yr = date('Y', $start); $mo = date('m', $start); $dy = date('d', $start); $from = mktime(0, 0, 0, $mo, $dy, $yr); $to = mktime(23, 59, 59, $mo, $dy, $yr); $start = $to + 1; } elseif ($breakdown == 'month') { $yr = date('Y', $start); $mo = date('m', $start); $from = mktime(0, 0, 0, $mo, 1, $yr); $to = mktime(23, 59, 59, $mo + 1, 0, $yr); $start = $to + 1; } elseif ($breakdown == 'year') { $yr = date('Y', $start); $from = mktime(0, 0, 0, 1, 1, $yr); $to = mktime(23, 59, 59, 1, 0, $yr + 1); $start = $to + 1; } $bulk->insert(array($mysql['user_id'], "FROM_UNIXTIME('" . $from . "','{$datetype}')", "FROM_UNIXTIME('" . $to . "','{$datetype}')", "'breakdown'")); } $bulk->execute(); $user_id = DB::quote(getUserID()); $start = DB::quote($time['from']); $end = DB::quote($time['to']); $sql = "\n\t\tSELECT COUNT(*) AS clicks, (SUM(click.payout*click.lead) / sum(click.lead)) as payout, SUM(click.lead) AS leads, SUM(click.payout*click.lead) AS income, \n\t\tFROM_UNIXTIME(click.time + {$offset},'{$datetype}') as date\n\n\t\tFROM \n\t\t"; $sql .= getReportFilters('overview/breakdown'); $sql .= "\n\t\tgroup by date\n\t\torder by null\n\t\t"; //echo $sql . "<br>"; //echo $click_sql; $click_rows = DB::getRows($sql); foreach ($click_rows as $click_row) { //get the stats $clicks = 0; $clicks = $click_row['clicks']; $mysql['date'] = $click_row['date']; if ($clicks) { $cost = getArrayVar($spending_data, $click_row['date'], array('cost' => 0)); $cost = $cost['cost']; $avg_cpc = calculate_cpc($clicks, $cost); } else { $avg_cpc = 0; $cost = 0; } //leads $leads = $click_row['leads']; //signup ratio $conv = calculate_conv($clicks, $leads); //were not using payout //current payout $payout = $click_row['payout']; //income $income = 0; $income = $click_row['income']; //grab the EPC $epc = calculate_epc($clicks, $income); //net income $net = 0; $net = $income - $cost; //roi $roi = calculate_roi($income, $cost); //html escape vars $mysql['clicks'] = DB::quote($clicks); $mysql['leads'] = DB::quote($leads); $mysql['conv'] = DB::quote($conv); $mysql['epc'] = DB::quote($epc); $mysql['avg_cpc'] = DB::quote($avg_cpc); $mysql['income'] = DB::quote($income); $mysql['cost'] = DB::quote($cost); $mysql['net'] = DB::quote($net); $mysql['roi'] = DB::quote($roi); $mysql['payout'] = DB::quote($payout); //insert chart $sort_breakdown_sql = "\n\t\t\t\tupdate\n\t\t\t\t\tbt_c_statcache\n\t\t\t\tSET\n\t\t\t\t\tclicks='" . $mysql['clicks'] . "',\n\t\t\t\t\tleads='" . $mysql['leads'] . "',\n\t\t\t\t\tconv='" . $mysql['conv'] . "',\n\t\t\t\t\tpayout='" . $mysql['payout'] . "',\n\t\t\t\t\tepc='" . $mysql['epc'] . "',\n\t\t\t\t\tcpc='" . $mysql['avg_cpc'] . "',\n\t\t\t\t\tincome='" . $mysql['income'] . "',\n\t\t\t\t\tcost='" . $mysql['cost'] . "',\n\t\t\t\t\tnet='" . $mysql['net'] . "',\n\t\t\t\t\troi='" . $mysql['roi'] . "'\n\t\t\t\t\t\n\t\t\t\t\twhere\n\t\t\t\t\t\n\t\t\t\t\ttime_from='" . $mysql['date'] . "' and\n\t\t\t\t\tuser_id='" . $mysql['user_id'] . "' and\n\t\t\t\t\ttype='breakdown'"; DB::query($sort_breakdown_sql); //($sort_breakdown_sql); } }
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); }