public static function getRow($sql)
{
$rows = DB::getRows($sql);
if (!$rows) {
return null;
}
return $rows[0];
}
static function getList($where, $select = '*', $order = 'id DESC')
{
if (!$where) {
return false;
}
self::_init();
return DB::getRows(static::$_table, $where, $select, $order);
}
public function clickSpy($user_id, $start, $length)
{
$cnt = DB::getVar("select count(1) from bt_s_clicks click\n\t\t\tLEFT JOIN bt_u_campaigns camp ON (click.campaign_id = camp.campaign_id)\n\t\t\tLEFT JOIN bt_u_traffic_sources ts ON (ts.traffic_source_id = click.traffic_source_id)\n\t\t\twhere ts.deleted=0\n\t\t\tand camp.deleted=0\n\t\t");
$data = array("count" => $cnt);
$click_sql = "SELECT click.click_id,\n\t\t\t\t\t\t\t\tclick.time,\n\t\t\t\t\t\t\t\tts.name as `ts.name`,\n\t\t\t\t\t\t\t\tip_address,\n\t\t\t\t\t\t\t\tkeyword,\n\t\t\t\t\t\t\t\tlead,\n\t\t\t\t\t\t\t\tfiltered,\n\t\t\t\t\t\t\t\tlanding_url as landing,\n\t\t\t\t\t\t\t\tclick.cloaked,\n\t\t\t\t\t\t\t\tadv.browser_id,\n\t\t\t\t\t\t\t\tadv.platform_id,\n\t\t\t\t\t\t\t\treferer_url as referer_url_address,\n\t\t\t\t\t\t\t\treferer_domain as referer_domain_host,\n\t\t\t\t\t\t\t\toffer_url as redirect_url_address,\n\t\t\t\t\t\t\t\tcamp.name\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t FROM bt_s_clicks AS click \n\t\t\t\t\t \t\t\t\t\tLEFT JOIN bt_s_clicks_advanced adv USING (click_id)\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN bt_s_clicks_site USING (click_id)\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN bt_u_traffic_sources ts ON (ts.traffic_source_id = click.traffic_source_id)\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN bt_s_ips ON (bt_s_ips.ip_id = adv.ip_id)\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN bt_s_keywords ON (bt_s_keywords.keyword_id = adv.keyword_id)\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN bt_u_campaigns camp ON (click.campaign_id = camp.campaign_id)\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t WHERE ts.deleted=0 and camp.deleted=0 order by click.click_id desc limit " . DB::quote($start) . ',' . DB::quote($length);
$click_rows = DB::getRows($click_sql);
$data['click_rows'] = $click_rows;
return $data;
}
function saveTrackingVariables($campaign)
{
$mysql = array();
$v1 = DB::quote(getArrayVar($_GET, $campaign->option('var_v1')->value));
$v2 = DB::quote(getArrayVar($_GET, $campaign->option('var_v2')->value));
$v3 = DB::quote(getArrayVar($_GET, $campaign->option('var_v3')->value));
$v4 = DB::quote(getArrayVar($_GET, $campaign->option('var_v4')->value));
if (!$v1) {
$v1 = DB::quote(getArrayVar($_GET, 'subid1'));
}
if (!$v2) {
$v2 = DB::quote(getArrayVar($_GET, 'subid2'));
}
if (!$v3) {
$v3 = DB::quote(getArrayVar($_GET, 'subid3'));
}
if (!$v4) {
$v4 = DB::quote(getArrayVar($_GET, 'subid4'));
}
$v1 = strtolower($v1);
$v2 = strtolower($v2);
$v3 = strtolower($v3);
$v4 = strtolower($v4);
$row = DB::getRows("select var_id,LOWER(var_value) as var_value from bt_s_variables where var_value IN ('{$v1}','{$v2}','{$v3}','{$v4}')", 'var_value');
if (!isset($row[$v1])) {
DB::query("insert into bt_s_variables set var_value='{$v1}'");
$row[$v1] = array('var_id' => DB::insertId(), 'var_value' => $v1);
}
if (!isset($row[$v2])) {
DB::query("insert into bt_s_variables set var_value='{$v2}'");
$row[$v2] = array('var_id' => DB::insertId(), 'var_value' => $v2);
}
if (!isset($row[$v3])) {
DB::query("insert into bt_s_variables set var_value='{$v3}'");
$row[$v3] = array('var_id' => DB::insertId(), 'var_value' => $v3);
}
if (!isset($row[$v4])) {
DB::query("insert into bt_s_variables set var_value='{$v4}'");
$row[$v4] = array('var_id' => DB::insertId(), 'var_value' => $v4);
}
$mysql['v1'] = $row[$v1]['var_value'];
$mysql['v1_id'] = $row[$v1]['var_id'];
$mysql['v2'] = $row[$v2]['var_value'];
$mysql['v2_id'] = $row[$v2]['var_id'];
$mysql['v3'] = $row[$v3]['var_value'];
$mysql['v3_id'] = $row[$v3]['var_id'];
$mysql['v4'] = $row[$v4]['var_value'];
$mysql['v4_id'] = $row[$v4]['var_id'];
return $mysql;
}
function initCloaker()
{
global $cloaker_orgs;
$cloaker_orgs = DB::getRows("SELECT * FROM `bt_g_cloaker_orgs`", 'org_name');
}
public function getLifetimeData()
{
$cols = array('lifetime', 'leads', 'payout', 'income');
$cnt_query = "select count(1) from (select 1 from";
$cnt_query .= getReportFilters('analyze/lifetime');
$cnt_query .= " GROUP BY click.lifetime) thedata";
$cnt = DB::getVar($cnt_query);
$sql = 'select click.lifetime, ';
$sql .= getReportGeneralSelects() . ' from ';
$sql .= getReportFilters('analyze/lifetime', '');
$sql .= ' and lead=1 group by click.lifetime ';
$sql .= getReportOrder($cols);
$sql .= getReportLimits();
$result = DB::getRows($sql);
foreach ($result as &$row) {
$row['lifetime'] = $this->LifetimeTime($row['lifetime']);
}
return array('data' => $result, 'cnt' => $cnt, 'cols' => $cols);
}
public function getMobileData()
{
if (!bt_mobile_enabled()) {
error404();
}
$groups = array();
if (BTAuth::user()->getPref("user_mobile_breakdown_1")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_1");
if (BTAuth::user()->getPref("user_mobile_breakdown_2")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_2");
if (BTAuth::user()->getPref("user_mobile_breakdown_3")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_3");
if (BTAuth::user()->getPref("user_mobile_breakdown_4")) {
$groups[] = BTAuth::user()->getPref("user_mobile_breakdown_4");
}
}
}
}
if (!$groups) {
$groups = array('devices.type');
}
$breakdown_cols = array("devices.brand" => "Brand", "devices.type" => "Device Type", "devices.os" => "OS", "devices.os_version" => "OS Version", "devices.browser" => "Browser", "devices.browser_version" => "Browser Version", "orgs.name" => "Carrier/ISP");
foreach ($groups as $group) {
if (!isset($breakdown_cols[$group])) {
$groups = array('devices.type');
break;
}
}
$group = " ";
$group .= implode(',', $groups);
//$cols = array('browser_name', 'clicks', 'leads', 'conv', 'payout', 'epc', 'avg_cpc', 'income', 'cost', 'net', 'roi');
$cols = array('label', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income');
$sql = 'select ' . $group . ', ';
$sql .= getReportGeneralSelects() . 'from ';
$sql .= getReportFilters('platforms/mobile', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id) LEFT JOIN bt_s_device_data AS devices on adv.device_id=devices.device_id
left join bt_g_organizations as orgs on adv.org_id=orgs.org_id ');
$sql .= " and adv.device_id>0 and devices.hash<>'d41d8cd98f00b204e9800998ecf8427e' group by " . $group;
$sql .= ' order by ' . $group . ' ';
$click_results = DB::getRows($sql);
for ($i = 0; $i < count($groups); $i++) {
if ($pos = strpos($groups[$i], '.')) {
$groups[$i] = substr($groups[$i], $pos + 1);
}
}
$final_rows = array();
$tree = makeHierarchical($click_results, $groups);
unset($click_results);
//no longer needed
foreach ($tree as $node) {
getRowsFromTreeNode($node, 0, $final_rows);
}
return array('data' => $final_rows, 'cols' => $cols, 'cnt' => count($final_rows));
}
public function viewBreakdownAction()
{
$_POST['order'] = '';
//show breakdown
runBreakdown(true);
//show real or filtered clicks
$mysql['user_id'] = DB::quote(getUserID());
$breakdown = BTAuth::user()->getPref('breakdown');
//grab breakdown report
$breakdown_sql = "SELECT * FROM bt_c_statcache WHERE user_id='" . $mysql['user_id'] . "' and type='breakdown' ";
$breakdown_result = DB::getRows($breakdown_sql);
$this->setVar("breakdown", $breakdown);
$this->setVar("breakdown_result", $breakdown_result);
$this->loadView('overview/view_breakdown');
}
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);
}
}
$rS .= $field->name;
$rS .= "' !important; ";
$rS .= "display: block; ";
$rS .= "font-weight: bold; ";
$rS .= "margin: 0 -15px; ";
$rS .= "border-bottom: 2px solid #ddd; ";
$rS .= "padding-left: 15px; ";
$rS .= "padding-bottom: 5px; } ";
$counter++;
}
$rS .= ".small-screen { display: block; } ";
$rS .= ".big-screen { display: none; } ";
$rS .= ".list-info .row { margin: 30px; } ";
$rS .= ".value { margin: 0 -15px 10px -15px; ";
$rS .= "padding: 10px 0 10px 15px; ";
$rS .= "background: #f9f9f9; ";
$rS .= "border-bottom: 1px solid #ddd; }";
$rS .= "}";
// End of media query
$style = "<style>";
$style .= $rS;
$style .= "</style>";
return $style;
}
}
$db = new DB();
$db->setFields();
$fields = $db->getFields();
$style = $db->getStyle();
$rows = $db->getRows();
public function getTimezoneData()
{
$cols = array('timezone', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income');
$cnt_query = "select count(1) from (select 1 from";
$cnt_query .= getReportFilters('geography/time_zones', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id) left join bt_g_geo_locations geo on adv.location_id=geo.location_id ');
$cnt_query .= " group by geo.timezone) thedata";
$cnt = DB::getVar($cnt_query);
$sql = 'select geo.timezone, ';
$sql .= getReportGeneralSelects() . 'from ';
$sql .= getReportFilters('geography/time_zones', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id) left join bt_g_geo_locations geo on adv.location_id=geo.location_id ');
$sql .= ' group by geo.timezone ';
$sql .= getReportOrder($cols);
$sql .= getReportLimits();
$result = DB::getRows($sql);
foreach ($result as &$row) {
if (!$row['timezone']) {
$row['timezone'] = '[unknown timezone]';
}
}
return array('data' => $result, 'cnt' => $cnt, 'cols' => $cols);
}
function findByQuery($query, $order = '', $limit = 0, $offset = 0)
{
if ($order) {
$query .= ' order by ' . $order;
}
if ($limit && $offset) {
$query .= ' limit ' . $offset . ',' . $limit;
}
if ($limit && !$offset) {
$query .= ' limit ' . $limit;
}
$rows = DB::getRows($query);
$objects = array();
foreach ($rows as $row) {
$obj = $this->instance();
$obj->addFromHash($row);
$this->cleanDirtyProperties();
$objects[] = $obj;
}
return $objects;
}
public function getLoginLogs($limit = 100, $offset = 0)
{
if (!is_numeric($limit) || !is_numeric($offset)) {
return array();
}
return DB::getRows("select * from bt_s_authsessions left join bt_s_ips using (ip_id) where user_id='" . DB::quote($this->id()) . "' order by session_id desc limit {$offset},{$limit}");
}
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);
}
<?php
require_once __DIR__ . '/../includes/connect.php';
require_once BT_ROOT . '/private/includes/traffic/lifetime.php';
$sql = "select click_id,(lead_time - time) as lifetime from bt_s_clicks where (lead=1) and (lead_time > time) and (lead_time > 0) and (lifetime = 0) limit %s, %s";
$cnt = DB::getVar("select count(*) from bt_s_clicks where (lead=1) and (lead_time > time) and (lead_time > 0) and (lifetime = 0)");
$cursor = 0;
$page = 200;
while ($rows = DB::getRows(sprintf($sql, $cursor, $page))) {
foreach ($rows as $row) {
$time = $row['lifetime'];
$id = $row['click_id'];
DB::query("update bt_s_clicks set lifetime='" . DB::quote(getClickLifetimeInterval($time)) . "' where click_id='" . $id . "'");
}
$cursor += $page;
}
printf("%d Links processed\n", $cnt);
