public function calculateCostsPerCampaign($spends) { $user_id = DB::quote(getUserID()); $sql = "select * from bt_c_statcache where user_id='{$user_id}' and type='overview'"; $rows = DB::getRows($sql); foreach ($rows as $row) { if (isset($spends[$row['meta1']])) { $row['cost'] = $spends[$row['meta1']]['cost']; } $row['conv'] = calculate_conv($row['clicks'], $row['leads']); $row['epc'] = calculate_epc($row['clicks'], $row['income']); $row['cpc'] = calculate_cpc($row['clicks'], $row['cost']); $row['net'] = calculate_net($row['income'], $row['cost']); $row['roi'] = calculate_roi($row['income'], $row['cost']); DB::query("update bt_c_statcache set\n\t\t\t\tcost='" . DB::quote($row['cost']) . "',\n\t\t\t\tconv='" . DB::quote($row['conv']) . "',\n\t\t\t\tepc='" . DB::quote($row['epc']) . "',\n\t\t\t\tcpc='" . DB::quote($row['cpc']) . "',\n\t\t\t\tnet='" . DB::quote($row['net']) . "',\n\t\t\t\troi='" . DB::quote($row['roi']) . "'\n\t\t\t\t\n\t\t\t\twhere id='" . DB::quote($row['id']) . "'"); //if it has a campaign, update the top lvl LP as well if ($row['meta2']) { $clicks = DB::quote($row['clicks']); $leads = DB::quote($row['leads']); $meta1 = DB::quote($row['meta1']); $meta2 = DB::quote($row['meta2']); $income = DB::quote($row['income']); DB::query("update bt_c_statcache set\n\t\t\t\t\tclicks=clicks+{$clicks},\n\t\t\t\t\tleads=leads+{$leads},\n\t\t\t\t\tincome=income+{$income}\n\t\t\t\t\t\n\t\t\t\t\twhere user_id='{$user_id}' and type='overview' and meta1='{$meta1}' and meta2='0'"); } } }
</tr> <?php } ?> <?php $rows = count($breakdown_result); $html['clicks'] = BTHtml::encode($stats_total['clicks']); $html['leads'] = BTHtml::encode($stats_total['leads']); $html['conv'] = BTHtml::encode(calculate_conv($stats_total['clicks'], $stats_total['leads']) . '%'); $html['payout'] = BTHtml::encode(dollar_format(calculate_payout($stats_total['leads'], $stats_total['income']))); $html['epc'] = BTHtml::encode(dollar_format(calculate_epc($stats_total['clicks'], $stats_total['income']))); $html['cpc'] = BTHtml::encode(dollar_format(calculate_cpc($stats_total['clicks'], $stats_total['cost']))); $html['income'] = BTHtml::encode(dollar_format($stats_total['income'])); $html['cost'] = BTHtml::encode(dollar_format($stats_total['cost'])); $html['net'] = BTHtml::encode(dollar_format(calculate_net($stats_total['income'], $stats_total['cost']))); $html['roi'] = BTHtml::encode(calculate_roi($stats_total['income'], $stats_total['cost']) . '%'); ?> </tbody> <?php $stat_html = ob_get_contents(); ob_end_clean(); ?> <tfoot> <tr> <td><strong>Totals for report</strong></td> <td><strong><?php
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); }
function calculate_roi($income, $cost) { $net = calculate_net($income, $cost); if ($cost > 0) { $roi = $net / $cost * 100; } else { $roi = 0; } return number_format($roi, 2, '.', ''); }