public function getBrowserData()
{
$cols = array('meta1', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income');
$sql = 'select adv.browser_id, ';
$sql .= getReportGeneralSelects() . 'from ';
$sql .= getReportFilters('platforms/browser', 'left join bt_s_clicks_advanced as adv on (click.click_id=adv.click_id)');
$sql .= ' and adv.browser_id>0 group by adv.browser_id ';
$result = DB::getRows($sql);
$cnt = count($result);
DB::query("delete from bt_c_statcache where user_id='" . DB::quote(getUserId()) . "' and type='platbrow'");
$bulk = new DB_Bulk_Insert('bt_c_statcache', array('user_id', 'type', 'clicks', 'click_throughs', 'click_through_rates', 'leads', 'conv', 'payout', 'epc', 'income', 'meta1'));
foreach ($result as $row) {
$row['browser_name'] = Browser::getBrowserName($row['browser_id']);
if (!$row['browser_name']) {
$row['browser_name'] = '[unknown browser]';
}
$bulk->insert(array(DB::quote(getUserId()), "'platbrow'", $row['clicks'], $row['click_throughs'], $row['click_through_rates'], $row['leads'], $row['conv'], $row['payout'], $row['epc'], $row['income'], "'" . DB::quote($row['browser_name']) . "'"));
}
$bulk->execute();
$sql = "select * from bt_c_statcache where user_id='" . DB::quote(getUserId()) . "' and type='platbrow' ";
$sql .= getReportOrder($cols);
$sql .= getReportLimits();
$result = DB::getRows($sql);
return array('data' => $result, 'cnt' => $cnt, 'cols' => $cols);
}
public function viewClickidAction()
{
$clickid = $_POST['clickid'];
$sql = "select \n\t\tcl.time as `Click Time`,\n\t\tconcat('\$',cl.payout) as `Payout`,\n\t\tif(cl.lead=1,'True','False') as `Converted`,\n\t\t(case when (cl.filtered=0) then '' when (cl.filtered=1) then 'Affiliate Click' when (cl.filtered=2) then 'Repeat Visitor' end) as `Filtered`,\n\t\tconcat(ad_net.ad_network_name,' : ',ad_acct.ad_account_name) as `Ad Account`,\n\t\tconcat(camp_net.name,' : ',offer.name) as `Offer`,\n\t\tkw.keyword as `Keyword`,\n\t\tip.ip_address as `IP Address`,\n\t\tadv.platform_id,\n\t\tadv.browser_id,\n\t\tv1.var_value as `Subid1`,\n\t\tv2.var_value as `Subid2`,\n\t\tv3.var_value as `Subid3`,\n\t\tv4.var_value as `Subid4`,\n\t\tconcat(geo.city,', ',geo.state_full,', ',geo.country_full) as `Location`,\n\t\tcoalesce(lp.name,'') as `Landing Page`,\n\t\ttracker.name as `Campaign`\n\t\t\n\t\tfrom bt_s_clicks cl\n\t\t\n\t\tleft join bt_s_clicks_advanced adv on cl.click_id=adv.click_id\n\t\tleft join bt_u_ad_accounts ad_acct on ad_acct.ad_account_id=cl.ad_account_id\n\t\tleft join bt_u_offers camp on cl.offer_id=offer.offer_id\n\t\tleft join bt_u_aff_networks camp_net on offer.aff_network_id=camp_net.aff_network_id\n\t\tleft join bt_s_keywords kw on adv.keyword_id=kw.keyword_id\n\t\tleft join bt_s_ips ip on adv.ip_id=ip.ip_id\n\t\tleft join bt_u_campaigns tracker on tracker.campaign_id=adv.campaign_id\n\t\t\n\t\tleft join bt_u_landing_pages lp on lp.landing_page_id=cl.landing_page_id\n\t\t\n\t\tleft join bt_s_variables v1 on adv.v1_id=v1.var_id\n\t\tleft join bt_s_variables v2 on adv.v2_id=v2.var_id\n\t\tleft join bt_s_variables v3 on adv.v3_id=v3.var_id\n\t\tleft join bt_s_variables v4 on adv.v4_id=v4.var_id\n\t\t\n\t\tleft join bt_g_geo_locations geo on adv.location_id=geo.location_id\n\t\t\n\t\twhere cl.click_id='" . DB::quote(base_convert($clickid, 36, 10)) . "' ";
BTApp::firelog($sql);
$data = DB::getRow($sql);
if (!$data) {
echo 'Invalid Click ID';
BTApp::end();
}
$data['Platform'] = Browser::getPlatformName($data['platform_id']);
unset($data['platform_id']);
$data['Browser'] = Browser::getBrowserName($data['browser_id']);
unset($data['browser_id']);
$data['Click Time'] = date('Y-m-d H:i:s', $data['Click Time']);
ksort($data);
//alphabetize it
$this->setVar('clickid', $clickid);
$this->setVar("clickid_data", $data);
$this->loadView("analyze/view_clickid");
}
public function dataAction()
{
$this->loadModel("ClickModel");
$model = new ClickModel();
$data = $model->clickSpy(getUserId(), $_GET['iDisplayStart'], $_GET['iDisplayLength']);
$cnt = $data['count'];
$click_rows = $data['click_rows'];
$sEcho = $_GET['sEcho'];
$data = array('sEcho' => (int) $sEcho, 'iTotalRecords' => $cnt, 'iTotalDisplayRecords' => $cnt, 'aaData' => array());
foreach ($click_rows as $click_row) {
$html['referer'] = BTHtml::encode($click_row['referer_url_address']);
$html['referer_host'] = BTHtml::encode($click_row['referer_domain_host']);
$html['redirect'] = BTHtml::encode($click_row['redirect_url_address']);
$html['click_pid'] = BTHtml::encode(base_convert($click_row['click_id'], 10, 36));
$html['time'] = date('m/d/y g:ia', $click_row['time']);
$html['ts.name'] = BTHtml::encode($click_row['ts.name']);
$html['ip_address'] = BTHtml::encode($click_row['ip_address']);
$html['keyword'] = BTHtml::encode($click_row['keyword']);
$html['lead'] = BTHtml::encode($click_row['lead']);
$html['filtered'] = BTHtml::encode($click_row['filtered']);
$html['landing'] = BTHtml::encode($click_row['landing']);
$html['name'] = BTHtml::encode($click_row['name']);
$html['browser_id'] = '';
if ($click_row['browser_id']) {
$html['browser_id'] = '<img width="16" height="16" class="tooltip" title="' . Browser::getBrowserName($click_row['browser_id']) . '" src="/theme/img/icons/browsers/' . $click_row['browser_id'] . '.png"/>';
}
$html['platform_id'] = '';
if ($click_row['platform_id']) {
$html['platform_id'] = '<img width="16" height="16" class="tooltip" title="' . Browser::getPlatformName($click_row['platform_id']) . '" src="/theme/img/icons/platforms/' . $click_row['platform_id'] . '.png"/>';
}
$data_array = array();
$data_array[] = strtoupper($html['click_pid']);
$admin_image = '';
// if a click happened from the admin use user.png
if ($click_row['filtered'] == 1) {
// check if logged in user clicked
$admin_image = '<img width="16" height="16" class="tooltip" src="/theme/img/icons/packs/iconsweets2/16x16/user.png" alt="Affiliate Click" title="Affiliate Click"/>';
}
$filt = '';
$landingpage_image = '';
$offer_image = '';
$repeated_user = '';
if ($click_row['lead'] == '1') {
$alt = 'Converted Click';
$filt = '<img width="16" height="16" class="tooltip" src="/theme/img/icons/16x16/money_dollar.png" alt="' . $alt . '" title="' . $alt . '" width="16px" height="16px"/>';
}
// if a link is going to an offer directly use the bended arrow right.png
$offer_image = $html['redirect'] ? sprintf('<a href="%s" target="_new"><img width="16" height="16" class="tooltip" src="/theme/img/icons/packs/iconsweets2/16x16/bended-arrow-right.png" alt="Offer Click" title="Offer Click"/></a>', $html['redirect']) : '';
if (!$offer_image) {
// if the click happens on a landing page, they icon should be the document.png
$landingpage_image = $html['landing'] ? sprintf('<a href="%s" target="_new"><img width="16" height="16" class="tooltip" src="/theme/img/icons/packs/iconsweets2/16x16/document.png" alt="Landing" title="Landing Page"/></a>', $html['landing']) : '';
}
// if a click happened from a repeated user use footprint.png
if ($click_row['filtered'] == 2) {
// check if repeated user
$repeated_user = '******';
}
$cloaked_icon = '';
if ($click_row['cloaked']) {
$cloaked_icon = '<img width="16" height="16" class="tooltip" src="/theme/img/icons/packs/iconsweets2/16x16/bulls-eye.png" alt="Safe Page" title="Visitor Was Filtered By An Advanced Redirect."/>';
}
if ($filt) {
$data_array[] = $filt . $admin_image . $repeated_user . $cloaked_icon;
} else {
$data_array[] = $filt . $landingpage_image . $offer_image . $admin_image . $repeated_user . $cloaked_icon;
}
$data_array[] = $html['time'];
$data_array[] = '<span title="' . $html['keyword'] . '">' . $html['keyword'] . '</span>';
$data_array[] = $html['name'];
$data_array[] = sprintf('<a href="%s" target="_new" title="Referer" class="tablelink">%s</a> ', $html['referer'], $html['referer_host']);
$data_array[] = sprintf('<a target="_new" href="http://whois.arin.net/rest/ip/%s" class="tablelink">%s</a>', $html['ip_address'], $html['ip_address']);
$data_array[] = $html['browser_id'] . ' ' . $html['platform_id'];
$data['aaData'][] = $data_array;
}
echo json_encode($data);
}
