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);