public function getOverviewData()
{
$time = grab_timeframe();
$user_id = DB::quote(getUserID());
$start = DB::quote($time['from']);
$end = DB::quote($time['to']);
/** GET SPENDING **/
$spend_from = date('Y-m-d', $time['from']);
$spend_to = date('Y-m-d', $time['to']);
$sql = "select sum(amount) as cost, campaign_id from bt_u_spending \n\t\t\t\twhere date >= '{$spend_from}' and date <= '{$spend_to}' ";
$sql .= getSpendingReportFilters('bt_u_spending', getReportOptionsForPage('overview/overview'));
$sql .= " group by campaign_id";
$spends = DB::getRows($sql, 'campaign_id');
/** END SPENDING **/
//Erase old cache
DB::query("delete from bt_c_statcache where type='overview' and user_id='{$user_id}'");
/** GET CLICK DATA **/
$sql = "insert into bt_c_statcache select null,\n\t\t\t" . $user_id . " as user_id, 0 as time_from, 0 as time_to,'overview' as type, \n\t\t\tcount(click.click_id) as clicks,\n\t\t\t0 as click_throughs, 0 as click_through_rates,\n\t\t\tsum(click.lead) as leads, \n\t\t\t0 as conv, \n\t\t\t(SUM(click.payout*click.lead) / sum(click.lead)) as payout, \n\t\t\t0 as epc, 0 as cpc, \n\t\t\tSUM(click.payout*click.lead) AS income,\n\t\t\t0 as cost, 0 as net, 0 as roi,\n\t\t\tclick.campaign_id as meta1, camp.type as meta2, click.offer_id as meta3, null as meta4\n\t\t\t\n\t\t\tfrom\n\t\t\t";
$sql .= getReportFilters('overview/overview');
$sql .= "group by click.campaign_id, click.offer_id\n\t\t\torder by null";
DB::query($sql);
/** END CLICK DATA **/
/** GET ALL CAMPAIGNS TO FILL ZEROES, AND CALCULATE TOP-LEVEL CAMPAIGNS **/
$sql = "select meta1 from bt_c_statcache where user_id='{$user_id}' and type='overview' and meta1>0 group by meta1";
$conditions = array();
$ts_id = BTAuth::user()->getPref('traffic_source_id');
if ($ts_id) {
$conditions['traffic_source_id'] = $ts_id;
}
$campaigns = CampaignModel::model()->getRows(array('conditions' => $conditions));
$existing_rows = DB::getRows($sql, 'meta1');
//insert blank rows wheren eeded
foreach ($campaigns as $campaign) {
if (isset($existing_rows[$campaign->id()])) {
continue;
}
if (BTAuth::user()->getPref('campaign_type') == 'lp') {
if ($campaign->type != 1) {
continue;
}
} else {
if (BTAuth::user()->getPref('campaign_type') == 'direct') {
if ($campaign->type != 2) {
continue;
}
}
}
DB::query("insert into bt_c_statcache set user_id='{$user_id}', type='overview', meta1='" . DB::quote($campaign->id()) . "'");
}
//calculate top-level stats
foreach ($campaigns as $campaign) {
DB::query("update bt_c_statcache c\n\t\t\t\t\n\t\t\t\tleft join (select \n\t\t\t\t\t\tmeta1, \n\t\t\t\t\t\tsum(s.clicks) as clicks,\n\t\t\t\t\t\tsum(s.click_throughs) as click_throughs,\n\t\t\t\t\t\tsum(s.leads) as leads,\n\t\t\t\t\t\t(sum(s.income) / sum(s.clicks)) as payout,\n\t\t\t\t\t\tsum(s.income) as income\n\t\t\t\t\t\t\n\t\t\t\t\t\tfrom bt_c_statcache s where s.meta1=" . $campaign->id() . "\n\t\t\t\t\t ) data\n\t\t\t\ton data.meta1 > 0\n\t\t\t\t\n\t\t\t\tset\n\t\t\t\t\tc.clicks=data.clicks, c.click_throughs=data.click_throughs, c.click_through_rates=(c.click_throughs / c.clicks), c.leads=data.leads, c.conv=(c.leads / c.clicks), c.payout=data.payout, c.income=data.income\n\t\t\t\t\t\n\t\t\t\t\twhere c.meta1=" . $campaign->id() . " and (c.meta3 is null or c.meta3=0)");
}
/** END TOP LEVEL LP **/
$this->calculateCostsPerCampaign($spends);
}
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 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 getReportFieldsForPage($page)
{
$opt_arr = getReportOptionsForPage($page);
if (!($option_fields = simplexml_load_file(BT_ROOT . '/private/config/option_fields.xml'))) {
echo 'Invalid report configuration';
exit;
}
$fields = array();
foreach ($opt_arr as $opt => $enabled) {
if ($option_fields->{$opt}) {
foreach ($option_fields->{$opt}->children() as $field) {
if ($enabled) {
$fields[(string) $field] = true;
} else {
unset($fields[(string) $field]);
}
}
}
}
return $fields;
}
