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'");
         }
     }
 }
				<?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 
echo $html['clicks'];
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);
}