function runGeoLookup() { global $GEOIP_REGION_NAME; $geoip = Net_GeoIP::getInstance(BT_ROOT . "/bt-config/GeoLiteCity.dat"); $location = null; try { $location = $geoip->lookupLocation($_SERVER['REMOTE_ADDR']); } catch (Exception $e) { //ignore it, seriously dude, friggin ignore it. } if ($location) { $country = DB::quote($location->countryCode); $country_full = DB::quote($location->countryName); $state = DB::quote($location->region); $state_full = @DB::quote($GEOIP_REGION_NAME[strtoupper($location->countryCode)][strtoupper($location->region)]); $city = DB::quote($location->city); $timezone = @DB::quote(get_time_zone($location->countryCode, $location->region)); $postalcode = DB::quote($location->postalCode); } else { return 0; } $sql = "select location_id from bt_g_geo_locations where country='{$country}' and state='{$state}' and city='{$city}'"; $id = DB::getVar($sql); if (!$id) { $sql = "insert into bt_g_geo_locations values ('','{$country}','{$country_full}','{$state}','{$state_full}','{$city}','{$timezone}','{$postalcode}')"; DB::query($sql); $id = DB::insertId(); } return $id; }
public static function getNumPreviousClicks($campaign_id, $ip_id) { if (!is_numeric($campaign_id) || !is_numeric($ip_id)) { return false; } return DB::getVar("select count(1) from bt_s_clicks_advanced left join bt_s_clicks using (click_id) where campaign_id='" . DB::quote($campaign_id) . "' and ip_id='" . DB::quote($ip_id) . "'"); }
public static function checkUniqueVariableName($name) { if ($name == 'click_id' || $name == 'referer') { return true; } return DB::getVar("select 1 from bt_u_campaign_options where name like 'var_%' and value = '" . DB::quote($name) . "'"); }
function article($id = null) { $page = isset($_GET['page']) ? intval($_GET['page']) : 1; $pageSize = isset($_GET['pagesize']) ? intval($_GET['pagesize']) : 10; if ($id) { $id = intval($id); $sql = "SELECT * FROM SIMU WHERE ID ={$id}"; $article = DB::getLine($sql); if ($article) { self::json(array('code' => 0, 'data' => $article)); } else { self::json(array('code' => -1, 'msg' => '没有找到这篇文章哦!')); } } else { $offset = ($page - 1) * $pageSize; $sql = "SELECT * FROM SIMU ORDER BY UPDATEID LIMIT {$offset},{$pageSize}"; $data = DB::getData($sql); if ($data) { foreach ($data as &$item) { unset($item['CONTENT']); } $total = DB::getVar("SELECT COUNT(1) FROM SIMU"); $total = ceil($total / $pageSize); self::json(array('code' => 0, 'page' => $page, 'total' => $total, 'data' => $data)); } else { self::json(array('code' => -1, 'msg' => '哇,你已经全部都看完了!')); } } }
public static function checkLastIps($ip_id) { $ip_id = DB::quote($ip_id); if (DB::getVar("SELECT 1 FROM bt_s_clicks_advanced left join bt_s_clicks using (click_id) where ip_id='{$ip_id}' and time > (UNIX_TIMESTAMP() - (60 * 60 * 24)) limit 1")) { return true; } return false; }
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; }
public function OyladiMi($UyeID, $SoruID) { $sonuc = DB::getVar("SELECT OyID FROM oylar WHERE SoruID=:SoruID AND UyeID=:UyeID", array(':SoruID' => $SoruID, ':UyeID' => $UyeID)); if ($sonuc == false) { return false; } else { return true; } }
function runOrganizationLookup() { $orgip = Net_GeoIP::getInstance(BT_ROOT . "/bt-config/GeoIPOrg.dat"); $org = ''; try { $org = $orgip->lookupOrg($_SERVER['REMOTE_ADDR']); } catch (Exception $e) { return 0; } if (!$org) { return 0; } $org = DB::quote($org); $sql = "select org_id from bt_g_organizations where name='{$org}'"; $id = DB::getVar($sql); if (!$id) { $sql = "insert into bt_g_organizations values ('','{$org}')"; DB::query($sql); $id = DB::insertId(); } return $id; }
/** * Returns the installed version string * @return stirng */ public static function version() { return DB::getVar("select version from bt_g_version"); }
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 getBreakdownData() { $cols = array('time', 'clicks', 'leads', 'conv', 'payout', 'epc', 'cpc', 'income', 'cost', 'net', 'roi'); $start = (int) $_GET['iDisplayStart']; $length = (int) $_GET['iDisplayLength']; if ($start == 0) { runBreakdown(true); } $cnt = DB::getVar("select count(*) from bt_c_statcache WHERE user_id='" . DB::quote(getUserID()) . "' and type='breakdown'"); $breakdown_sql = "SELECT * FROM bt_c_statcache WHERE user_id='" . DB::quote(getUserID()) . "' and type='breakdown' limit {$start},{$length}"; $breakdown_result = DB::getRows($breakdown_sql); $breakdown_type = BTAuth::user()->getPref('breakdown'); foreach ($breakdown_result as &$row) { $ex = explode('-', $row['time_from']); if ($breakdown_type == 'day') { $row['time'] = date('M d, Y', mktime(0, 0, 0, $ex[1], $ex[2], $ex[0])); } elseif ($breakdown_type == 'month') { $row['time'] = date('M Y', mktime(0, 0, 0, $ex[1], 1, $ex[0])); } elseif ($breakdown_type == 'year') { $row['time'] = date('Y', mktime(0, 0, 0, 1, 1, $ex[0])); } } return array('data' => $breakdown_result, 'cols' => $cols, 'cnt' => $cnt); }
<?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);
public function getDatabaseSize() { global $dbname; return DB::getVar("SELECT ((SUM(DATA_LENGTH+INDEX_LENGTH) / 1024) / 1024)\n\t\t\t\t\t\t FROM INFORMATION_SCHEMA.TABLES \n\t\t\t\t\t\t WHERE TABLE_SCHEMA = '" . $dbname . "'"); }
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 check() { $code = 530008; $sql = "SELECT * FROM `fund_data` WHERE `code`={$code} ORDER BY create_date DESC"; $data = DB::getLine($sql); $lastUpdate = strtotime($data['update_date']); $last = date('Y-m-d H:i:s', time() - 21600); if ($lastUpdate < time() - 21600) { self::log('!!!!Warning:fund_data was not update at least 6 hours'); } else { $num = DB::getVar("SELECT count(1) FROM `fund_data` where update_date > '{$last}'"); self::log("fund_data was updated at {$data['update_date']},please check {$code} {$data['increase']}"); self::log("total update {$num}/3034"); } //下面检查货币基金 $date = DB::getVar("SELECT date(create_date) create_date FROM `fund_value` WHERE `code`=740601 ORDER BY create_date DESC LIMIT 1"); if ($date != date('Y-m-d', strtotime('-1 days'))) { self::log("!!!!Warning:fund_value has no yesterday data,recent date {$date}"); } else { self::log("fund_value last update {$date}"); $last = date('Y-m-d 00:00:00', strtotime('-1 days')); $num = DB::getVar("SELECT count(1) FROM `fund_value` where create_date = '{$last}'"); self::log("total update {$num}/3107"); } }
private static function getStart() { $sql = "SELECT `date` FROM `bond` ORDER BY `date` DESC LIMIT 1"; $date = DB::getVar($sql); return $date ? $date : '2003-01-01'; }
public function countLoginLogs() { return DB::getVar("select count(1) from bt_s_authsessions where user_id='" . DB::quote($this->id()) . "'"); }
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); }
if (!DB::query($sql)) { echo "Upgrade to {$version} failed at: " . $sql; exit; } } function upgradeVersion($version) { $query = "update bt_g_version set version='{$version}'"; upgradeSql($version, $query); echo "Upgraded to {$version}<br><br>"; return $version; } define('BT_IS_ROUTED', false); define('BYPASS_CONFIG_LOCATION', '/bt-config/conf.php'); require_once __DIR__ . '/private/includes/BTApp.php'; $cur_ver = DB::getVar("select version from bt_g_version"); if ($cur_ver == '13.04.10') { $new = '13.04.11'; $sql = "CREATE TABLE `bt_s_variables` (\n\t\t`var_id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n\t\t`var_value` varchar(100) CHARACTER SET utf8 NOT NULL,\n\t\tPRIMARY KEY (`var_id`),\n\t\tUNIQUE KEY `value` (`var_value`)\n\t ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"; upgradeSql($new, $sql); upgradeSql($new, "insert into bt_s_variables set var_value=''"); $id = DB::quote(DB::insertId()); upgradeSql($new, "update bt_s_clicks_advanced set v1_id='{$id}', v2_id='{$id}',v3_id='{$id}',v4_id='{$id}'"); upgradeSql($new, "drop table bt_u_tracking_v1"); upgradeSql($new, "drop table bt_u_tracking_v2"); upgradeSql($new, "drop table bt_u_tracking_v3"); upgradeSql($new, "drop table bt_u_tracking_v4"); $cur_ver = upgradeVersion($new); } if ($cur_ver == '13.04.11') { $new = '13.04.12';
/** * * @param string $query Default NULL * @param int $total * @return void */ public function saveTags($query = NULL, $total) { if ($query == NULL or $total < 1) { return; } if (!Config::get('save', 'tags')) { return; } if (strlen(seoLinks($query, TRUE)) < Config::get('minCharLimit', 'tags')) { return; } if ($total < Config::get('minResultsLimit', 'tags')) { return; } $query = str_replace('-', ' ', seoLinks($query, TRUE)); DB::select('tag'); DB::from('tag'); DB::where(array('tag' => $query)); DB::limit(1); DB::run(); $getTag = DB::getVar(); if ($getTag) { return; } DB::insert('tag', array('tag' => $query)); }