function userPrefDate()
{
$time = grab_timeframe();
$date['from_date'] = date('Y-m-d', $time['from']);
$date['to_date'] = date('Y-m-d', $time['to']);
return $date;
}
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);
}
<?php
include_once $_SERVER['DOCUMENT_ROOT'] . '/202-config/connect.php';
AUTH::require_user();
//set the timezone for the user, for entering their dates.
AUTH::set_timezone($_SESSION['user_timezone']);
//grab user time range preference
$time = grab_timeframe();
$mysql['to'] = $db->real_escape_string($time['to']);
$mysql['from'] = $db->real_escape_string($time['from']);
//show real or filtered clicks
$mysql['user_id'] = $db->real_escape_string($_SESSION['user_id']);
$user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM 202_users_pref WHERE user_id=" . $mysql['user_id'];
$user_result = _mysqli_query($user_sql, $dbGlobalLink);
//($user_sql);
$user_row = $user_result->fetch_assoc();
$breakdown = $user_row['user_pref_breakdown'];
if ($user_row['user_pref_show'] == 'all') {
$click_flitered = '';
}
if ($user_row['user_pref_show'] == 'real') {
$click_filtered = " AND click_filtered='0' ";
}
if ($user_row['user_pref_show'] == 'filtered') {
$click_filtered = " AND click_filtered='1' ";
}
if ($user_row['user_pref_show'] == 'filtered_bot') {
$click_filtered = " AND click_bot='1' ";
}
if ($user_row['user_pref_show'] == 'leads') {
$click_filtered = " AND click_lead='1' ";
function runWeekly($user_pref)
{
$database = DB::getInstance();
$db = $database->getConnection();
//grab time
$time = grab_timeframe();
//get breakdown pref
$mysql['user_id'] = $db->real_escape_string($_SESSION['user_id']);
$user_sql = "SELECT \tuser_time_register,\n\t\t\t\t\t\t\t\tuser_pref_breakdown,\n\t\t\t\t\t\t\t\tuser_pref_chart,\n\t\t\t\t\t\t\t\tuser_pref_show\n\t\t\t\t\t FROM 202_users LEFT JOIN 202_users_pref USING (user_id) \n\t\t\t\t\t WHERE 202_users.user_id='" . $mysql['user_id'] . "'";
$user_result = _mysqli_query($user_sql);
//($user_sql);
$user_row = $user_result->fetch_assoc();
if ($user_row['user_pref_show'] == 'all') {
$click_flitered = '';
}
if ($user_row['user_pref_show'] == 'real') {
$click_filtered = " AND click_filtered='0' ";
}
if ($user_row['user_pref_show'] == 'filtered') {
$click_filtered = " AND click_filtered='1' ";
}
if ($user_row['user_pref_show'] == 'filtered_bot') {
$click_filtered = " AND click_bot='1' ";
}
if ($user_row['user_pref_show'] == 'leads') {
$click_filtered = " AND click_lead='1' ";
}
//breakdown should be hour, day, month, or year.
$breakdown = 'day';
$pref_chart = $user_row['user_pref_chart'];
//first delete old report
$breakdown_sql = "\n\t\t\tDELETE\n\t\t\tFROM 202_sort_breakdowns\n\t\t\tWHERE user_id='" . $mysql['user_id'] . "'\n\t\t";
$breakdown_result = _mysqli_query($breakdown_sql);
//($breakdown_sql);
//find where to start from.
$start = $time['from'];
$end = $time['to'];
//make sure the start isn't past this users registration time, and likewise, make sure END isn't past today, else theses will try to grab reports for dates that do not exists slowing down mysql doing reports for nothing.
if ($user_row['user_time_register'] > $start) {
$start = $user_row['user_time_register'];
}
if (time() < $end) {
$end = time();
}
$x = 0;
while ($end > $start) {
$from = mktime(0, 0, 0, date('m', $end), date('d', $end), date('y', $end));
$to = mktime(23, 59, 59, date('m', $end), date('d', $end), date('y', $end));
$end = $end - 86400;
$end1 = $end + 86400;
$day = date('D', $end1);
switch ($day) {
case "Sun":
$day = 1;
break;
case "Mon":
$day = 2;
break;
case "Tue":
$day = 3;
break;
case "Wed":
$day = 4;
break;
case "Thu":
$day = 5;
break;
case "Fri":
$day = 6;
break;
case "Sat":
$day = 7;
break;
}
$mysql['from'] = $db->real_escape_string($from);
$mysql['to'] = $db->real_escape_string($to);
//build query
$command = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*) AS clicks,\n\t\t\t\tSUM(2cr.click_out) AS click_throughs,\n\t\t\t\tSUM(click_cpc) AS cost,\n\t\t\t\tSUM(2c.click_lead) AS leads,\n\t\t\t\tSUM(2c.click_payout*2c.click_lead) AS income\n\t\t\tFROM\n\t\t\t\t202_clicks AS 2c\n\t\t\t\tLEFT JOIN 202_clicks_record AS 2cr ON 2cr.click_id = 2c.click_id\n\t\t";
$db_table = "2c";
$pref_time = false;
if ($user_pref == true) {
$pref_adv = true;
$command = $command . "\n\t\t\t\tLEFT JOIN 202_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id)\n\t\t\t\tLEFT JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)\n\t\t\t";
} else {
$pref_adv = false;
}
$command = $command . "\n\t\t\tLEFT JOIN 202_aff_campaigns AS 2ac ON (2c.aff_campaign_id = 2ac.aff_campaign_id)\n\t\t\tLEFT JOIN 202_aff_networks AS 2an ON (2an.aff_network_id = 2ac.aff_network_id)\n\t\t";
$pref_show = false;
$pref_order = "\n\t\t\tAND (2c.click_alp = '1' OR (2ac.aff_campaign_deleted='0' AND 2an.aff_network_deleted='0'))\n\t\t\t{$click_filtered}\n\t\t\tAND 2c.click_time > " . $mysql['from'] . "\n\t\t\tAND 2c.click_time <= " . $mysql['to'] . "\n\t\t";
$offset = false;
$pref_limit = false;
$count = false;
$query = query($command, $db_table, $pref_time, $pref_adv, $pref_show, $pref_order, $offset, $pref_limit, $count);
$click_sql = $query['click_sql'];
$click_result = _mysqli_query($click_sql);
//($click_sql);
$click_row = $click_result->fetch_assoc();
//for AVG Payouts
$payouts_sql = "\n\t\t\tSELECT\n\t\t\t\tSUM(2c.click_payout) AS payout\n\t\t\tFROM 202_clicks AS 2c\n\t\t\tWHERE 2c.click_lead='1'\n\t\t\tAND 2c.click_time > " . $mysql['from'] . "\n\t\t\tAND 2c.click_time <= " . $mysql['to'] . "\n\t\t";
$payout_result = _mysqli_query($payouts_sql);
//($breakdown_sql);
$payout_row = $payout_result->fetch_assoc();
//get the stats
$clicks[$day] = $click_row['clicks'] + $clicks[$day];
$total_clicks = $total_clicks + $click_row['clicks'];
//click_throughs
$click_throughs[$day] = $click_row['click_throughs'] + $click_throughs[$day];
$total_click_throughs = $total_click_throughs + $click_row['click_throughs'];
//ctr
$ctr[$day] = @round($click_throughs[$day] / $clicks[$day] * 100, 2);
$total_ctr = @round($total_click_throughs / $total_clicks * 100, 2);
//avg cpc and cost
$cost[$day] = $click_row['cost'] + $cost[$day];
if ($clicks[$day] > 0) {
$avg_cpc[$day] = $cost[$day] / $clicks[$day];
}
$total_cost = $total_cost + $click_row['cost'];
$total_avg_cpc = @round($total_cost / $total_clicks, 5);
//leads
$leads[$day] = $click_row['leads'] + $leads[$day];
$total_leads = $total_leads + $click_row['leads'];
//signup ratio
$su_ratio[$day] = @round($leads[$day] / $clicks[$day] * 100, 2);
$total_su_ratio = @round($total_leads / $total_clicks * 100, 2);
//current payout
if ($click_row['leads'] > 0) {
$payout[$day] = $payout_row['payout'] / $leads[$day];
}
//income
$income[$day] = $click_row['income'] + $income[$day];
$total_income = $total_income + $click_row['income'];
//grab the EPC
$epc = @round($income[$day] / $clicks[$day], 2);
$total_epc = @round($total_income / $total_clicks, 2);
//net income
$net[$day] = $income[$day] - $cost[$day];
$total_net = $total_income - $total_cost;
//roi
$roi[$day] = @round($net[$day] / $cost[$day] * 100);
$total_roi = @round($total_net / $total_cost);
}
for ($day = 1; $day < 8; $day++) {
//html escape vars
$from = $day;
//$to = $hour +1; if ($to == 24) { $to = 0; }
$mysql['from'] = $db->real_escape_string($from);
$mysql['to'] = $db->real_escape_string($to);
$mysql['clicks'] = $db->real_escape_string($clicks[$day]);
$mysql['click_throughs'] = $db->real_escape_string($click_throughs[$day]);
$mysql['ctr'] = $db->real_escape_string($ctr[$day]);
$mysql['leads'] = $db->real_escape_string($leads[$day]);
$mysql['su_ratio'] = $db->real_escape_string($su_ratio[$day]);
$mysql['payout'] = $db->real_escape_string($payout[$day]);
$mysql['epc'] = $db->real_escape_string($epc[$day]);
$mysql['avg_cpc'] = $db->real_escape_string($avg_cpc[$day]);
$mysql['income'] = $db->real_escape_string($income[$day]);
$mysql['cost'] = $db->real_escape_string($cost[$day]);
$mysql['net'] = $db->real_escape_string($net[$day]);
$mysql['roi'] = $db->real_escape_string($roi[$day]);
//insert chart
$sort_breakdown_sql = "\n\t\t\tINSERT INTO 202_sort_breakdowns\n\t\t\tSET\n\t\t\t\tsort_breakdown_from='" . $mysql['from'] . "',\n\t\t\t\tsort_breakdown_to='" . $mysql['to'] . "',\n\t\t\t\tuser_id='" . $mysql['user_id'] . "',\n\t\t\t\tsort_breakdown_clicks='" . $mysql['clicks'] . "',\n\t\t\t\tsort_breakdown_click_throughs='" . $mysql['click_throughs'] . "',\n\t\t\t\tsort_breakdown_ctr='" . $mysql['ctr'] . "',\n\t\t\t\tsort_breakdown_leads='" . $mysql['leads'] . "',\n\t\t\t\tsort_breakdown_su_ratio='" . $mysql['su_ratio'] . "',\n\t\t\t\tsort_breakdown_payout='" . $mysql['payout'] . "',\n\t\t\t\tsort_breakdown_epc='" . $mysql['epc'] . "',\n\t\t\t\tsort_breakdown_avg_cpc='" . $mysql['avg_cpc'] . "',\n\t\t\t\tsort_breakdown_income='" . $mysql['income'] . "',\n\t\t\t\tsort_breakdown_cost='" . $mysql['cost'] . "',\n\t\t\t\tsort_breakdown_net='" . $mysql['net'] . "',\n\t\t\t\tsort_breakdown_roi='" . $mysql['roi'] . "'\n\t\t";
$sort_breakdown_result = _mysqli_query($sort_breakdown_sql);
#echo "<p>$sort_breakdown_sql</p>";
}
$breakdown_sql = "\n\t\tSELECT *\n\t\tFROM 202_sort_breakdowns\n\t\tWHERE user_id='" . $mysql['user_id'] . "'\n\t\tORDER BY sort_breakdown_from ASC\n\t";
$breakdown_result = _mysqli_query($breakdown_sql);
//($breakdown_sql);
$chartWidth = $_POST['chartWidth'];
$chartHeight = 180;
/* THIS IS A NET INCOME BAR GRAPH */
if ($pref_chart == 'profitloss') {
//start the PHP multi-dimensional array and create the region titles
$chart['chart_data'][0][0] = "";
$chart['chart_data'][1][0] = "Income";
$chart['chart_data'][2][0] = "Cost";
$chart['chart_data'][3][0] = "Net";
//extract the data from the query result one row at a time
for ($i = 0; $i < $breakdown_result->num_rows; $i++) {
//determine which column in the PHP array the current data belongs to
$breakdown_result->field_seek($breakdown_result->current_field);
$row = $breakdown_result->fetch_assoc();
$col = $row['sort_breakdown_from'];
$col++;
//populate the PHP array with the Year title
$day = $row['sort_breakdown_from'];
switch ($day) {
case 1:
$day = "Sun";
break;
case 2:
$day = "Mon";
break;
case 3:
$day = "Tue";
break;
case 4:
$day = "Wed";
break;
case 5:
$day = "Thu";
break;
case 6:
$day = "Fri";
break;
case 7:
$day = "Sat";
break;
}
$chart['chart_data'][0][$col] = $day;
//populate the PHP array with the revenue data
$chart['chart_data'][1][$col] = $row['sort_breakdown_income'];
$chart['chart_data'][2][$col] = $row['sort_breakdown_cost'];
$chart['chart_data'][3][$col] = $row['sort_breakdown_net'];
}
$chart['series_color'] = array("70CF40", "CF4040", "409CCF", "000000");
$chart['series_gap'] = array('set_gap' => 40, 'bar_gap' => -35);
$chart['chart_grid_h'] = array('alpha' => 20, 'color' => "000000", 'thickness' => 1, 'type' => "dashed");
$chart['axis_value'] = array('bold' => false, 'size' => 10);
$chart['axis_category'] = array('skip' => 3, 'bold' => false, 'size' => 10);
$chart['legend_label'] = array('bold' => true, 'size' => 12);
$chart['chart_pref'] = array('line_thickness' => 1, 'point_shape' => "none", 'fill_shape' => true);
$chart['chart_rect'] = array('x' => 40, 'y' => 20, 'width' => $chartWidth - 60, 'height' => $chartHeight);
$chart['chart_transition'] = array('type' => "scale", 'delay' => 0.5, 'duration' => 0.5, 'order' => "series");
} else {
//start the PHP multi-dimensional array and create the region titles
$chart['chart_data'][0][0] = "";
if ($pref_chart == 'clicks') {
$chart['chart_data'][1][0] = "Clicks";
} elseif ($pref_chart == 'leads') {
$chart['chart_data'][1][0] = "Leads";
} elseif ($pref_chart == 'su_ratio') {
$chart['chart_data'][1][0] = "Signup Ratio";
} elseif ($pref_chart == 'payout') {
$chart['chart_data'][1][0] = "Payout";
} elseif ($pref_chart == 'epc') {
$chart['chart_data'][1][0] = "EPC";
} elseif ($pref_chart == 'cpc') {
$chart['chart_data'][1][0] = "Avg CPC";
} elseif ($pref_chart == 'income') {
$chart['chart_data'][1][0] = "Income";
} elseif ($pref_chart == 'cost') {
$chart['chart_data'][1][0] = "Cost";
} elseif ($pref_chart == 'net') {
$chart['chart_data'][1][0] = "Net";
} elseif ($pref_chart == 'roi') {
$chart['chart_data'][1][0] = "ROI";
}
//extract the data from the query result one row at a time
for ($i = 0; $i < $breakdown_result->num_rows; $i++) {
//determine which column in the PHP array the current data belongs to
$breakdown_result->field_seek($breakdown_result->current_field);
$row = $breakdown_result->fetch_assoc();
$col = $row['sort_breakdown_from'];
$col++;
//populate the PHP array with the Year title
$day = $row['sort_breakdown_from'];
switch ($day) {
case 1:
$day = "Sun";
break;
case 2:
$day = "Mon";
break;
case 3:
$day = "Tue";
break;
case 4:
$day = "Wed";
break;
case 5:
$day = "Thu";
break;
case 6:
$day = "Fri";
break;
case 7:
$day = "Sat";
break;
}
$chart['chart_data'][0][$col] = $day;
//populate the PHP array with the revenue data
if ($pref_chart == 'clicks') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_clicks'];
} elseif ($pref_chart == 'leads') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_leads'];
} elseif ($pref_chart == 'su_ratio') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_su_ratio'];
} elseif ($pref_chart == 'payout') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_payout'];
} elseif ($pref_chart == 'epc') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_epc'];
} elseif ($pref_chart == 'cpc') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_avg_cpc'];
} elseif ($pref_chart == 'income') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_income'];
} elseif ($pref_chart == 'cost') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_cost'];
} elseif ($pref_chart == 'net') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_net'];
} elseif ($pref_chart == 'roi') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_roi'];
}
}
//$chart[ 'series_color' ] = array ( "003399");
$chart['series_color'] = array("000000");
$chart['chart_type'] = "Line";
$chart['chart_transition'] = array('type' => "dissolve", 'delay' => 0.5, 'duration' => 0.5, 'order' => "series");
$chart['chart_grid_h'] = array('alpha' => 20, 'color' => "000000", 'thickness' => 1, 'type' => "dashed");
}
$chart['chart_pref'] = array('line_thickness' => 1, 'point_shape' => "circle", 'fill_shape' => false);
$chart['axis_value'] = array('bold' => false, 'size' => 10);
$chart['axis_category'] = array('bold' => false, 'size' => 10);
$chart['legend_label'] = array('bold' => true, 'size' => 12);
$chart['chart_rect'] = array('x' => 40, 'y' => 20, 'width' => $chartWidth - 60, 'height' => $chartHeight);
showChart($chart, $chartWidth - 20, $chartHeight + 40);
?>
<div style="padding: 3px 0px;"></div><?php
}
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);
}
/**
*This should technically fill in almost everything in the "where" conditions
**/
function getReportFilters($report_page, $extra_join = '')
{
$time = grab_timeframe();
$start = DB::quote($time['from']);
$end = DB::quote($time['to']);
$option_fields = getReportOptionsForPage($report_page);
$sql = '
bt_s_clicks click
LEFT JOIN bt_u_offers AS offer ON (click.offer_id = offer.offer_id)
LEFT JOIN bt_u_aff_networks AS net ON (net.aff_network_id = offer.aff_network_id)
LEFT JOIN bt_u_traffic_sources AS ts ON (ts.traffic_source_id = click.traffic_source_id)
LEFT JOIN bt_u_campaigns as camp ON (camp.campaign_id=click.campaign_id)
' . $extra_join . ' where ';
$conds = array();
$conds[] = " ts.deleted='0' ";
$conds[] = " camp.deleted='0' ";
$conds[] = " (net.deleted='0' || net.deleted is null ) ";
$conds[] = " (offer.deleted='0' || offer.deleted is null ) ";
if (@$option_fields['show_type']) {
if (BTAuth::user()->getPref('campaign_type') == 'lp') {
$conds[] = sprintf(" camp.type=1 ");
} else {
if (BTAuth::user()->getPref('campaign_type') == 'direct') {
$conds[] = sprintf(" camp.type=2 ");
}
}
}
if (@$option_fields['show_traffic_source']) {
if (BTAuth::user()->getPref('traffic_source_id')) {
$conds[] = sprintf(" ts.traffic_source_id='%s' ", DB::quote(BTAuth::user()->getPref('traffic_source_id')));
}
}
if (@$option_fields['show_campaign']) {
if (BTAuth::user()->getPref('campaign_id')) {
$conds[] = sprintf(" camp.campaign_id=%s ", DB::quote(BTAuth::user()->getPref('campaign_id')));
}
}
$filt = getFilteredCondition();
if ($filt) {
$conds[] = $filt;
}
$conds[] = sprintf(' ((click.time >= %s) and (click.time <= %s)) ', $start, $end);
$sql .= join(' and ', $conds);
return $sql;
}