/tracking202/visitors/download/index.php
PHP | 207 lines | 162 code | 27 blank | 18 comment | 13 complexity | 0361cc2b6fe9201c22f2fb53e02e704c MD5 | raw file
- <? include_once($_SERVER['DOCUMENT_ROOT'] . '/202-config/connect.php');
-
- //make sure user is logged in or die
- AUTH::require_user();
-
- //start displaying the data
- header("Content-type: application/octet-stream");
-
- # replace excelfile.xls with whatever you want the filename to default to
- header("Content-Disposition: attachment; filename=T202_visitors_".time().".xls");
- header("Pragma: no-cache");
- header("Expires: 0");
-
-
- //get stuff
- $command = "SELECT * FROM 202_clicks AS 2c LEFT JOIN 202_clicks_advance AS 2ca USING (click_id) LEFT JOIN 202_clicks_site AS 2cs USING (click_id)";
- $db_table = "2c";
- $query = query($command, $db_table, true, true, true, ' ORDER BY 2c.click_id DESC ', $_POST['offset'], true, false);
-
-
- //run query
- $click_sql = $query['click_sql'];
- $click_result = mysql_query($click_sql) or record_mysql_error($click_sql);
-
- //html escape vars
- $html['from'] = htmlentities($query['from'], ENT_QUOTES, 'UTF-8');
- $html['to'] = htmlentities($query['to'], ENT_QUOTES, 'UTF-8');
- $html['rows'] = htmlentities($query['rows'], ENT_QUOTES, 'UTF-8');
- //set the timezone for the user, to display dates in their timezone
- AUTH::set_timezone($_SESSION['user_timezone']);
-
- echo "Subid" . "\t" .
- "Date" . "\t" .
- "Browser" . "\t" .
- "OS" . "\t" .
- "PPC Network" . "\t" .
- "PPC account" . "\t" .
- "Click Real/Filtered" . "\t" .
- "IP Address" . "\t" .
- "Offer/LP" . "\t" .
- "Text Ad" . "\t" .
- "Referer" . "\t" .
- "Landing" . "\t" .
- "Outbound" . "\t" .
- "Cloaked Referer" . "\t" .
- "Redirect" . "\t" .
- "Keyword" . "\n";
-
- //now display all the clicks
- while ($click_row = mysql_fetch_array($click_result, MYSQL_ASSOC)) {
-
- $mysql['click_id'] = mysql_real_escape_string($click_row['click_id']);
-
- $click_sql2 = "SELECT 2c.click_id,
- click_alp,
- text_ad_name,
- aff_campaign_name,
- aff_campaign_id_public,
- landing_page_nickname,
- ppc_network_name,
- ppc_account_name,
- ip_address,
- keyword,
- click_out,
- click_lead,
- click_filtered,
- click_id_public,
- click_cloaking,
- click_referer_site_url_id,
- click_landing_site_url_id,
- click_outbound_site_url_id,
- click_cloaking_site_url_id,
- click_redirect_site_url_id,"; /*
- location_country_name,
- location_country_code,
- location_region_code,
- location_city_name,*/ $click_sql2 .="
- 202_browsers.browser_id,
- 202_platforms.platform_id
- FROM 202_clicks AS 2c
- LEFT JOIN 202_clicks_advance USING (click_id)
- LEFT JOIN 202_clicks_record USING (click_id)
- LEFT JOIN 202_clicks_site USING (click_id)
- LEFT JOIN 202_aff_campaigns ON (202_aff_campaigns.aff_campaign_id = 2c.aff_campaign_id)
- LEFT JOIN 202_ppc_accounts ON (202_ppc_accounts.ppc_account_id = 2c.ppc_account_id)
- LEFT JOIN 202_ppc_networks USING (ppc_network_id)
- LEFT JOIN 202_landing_pages ON (202_landing_pages.landing_page_id = 2c.landing_page_id)
- LEFT JOIN 202_text_ads ON (202_text_ads.text_ad_id = 202_clicks_advance.text_ad_id)
- LEFT JOIN 202_ips ON (202_ips.ip_id = 202_clicks_advance.ip_id)
- LEFT JOIN 202_keywords ON (202_keywords.keyword_id = 202_clicks_advance.keyword_id)
- LEFT JOIN 202_browsers ON (202_browsers.browser_id = 202_clicks_advance.browser_id)
- LEFT JOIN 202_platforms ON (202_platforms.platform_id = 202_clicks_advance.platform_id)";
- /*LEFT JOIN 202_locations USING (location_id)
- LEFT JOIN 202_locations_country USING (location_country_id)
- LEFT JOIN 202_locations_city USING (location_city_id)
- LEFT JOIN 202_locations_region USING (location_region_id)*/
- $click_sql2 .= " WHERE 2c.click_id='".$mysql['click_id']."'";
- $click_row2 = memcache_mysql_fetch_assoc($click_sql2);
- $click_row = array_merge($click_row, $click_row2);
-
- $mysql['click_referer_site_url_id'] = mysql_real_escape_string($click_row['click_referer_site_url_id']);
- $site_url_sql = "SELECT * FROM 202_site_urls LEFT JOIN 202_site_domains USING (site_domain_id)
- WHERE 202_site_urls.site_url_id = '".$mysql['click_referer_site_url_id']."'
- AND 202_site_urls.site_domain_id = 202_site_domains.site_domain_id";
- $site_url_row = memcache_mysql_fetch_assoc($site_url_sql);
- $html['referer'] = htmlentities($site_url_row['site_url_address'], ENT_QUOTES, 'UTF-8');
- $html['referer_host'] = htmlentities($site_url_row['site_domain_host'], ENT_QUOTES, 'UTF-8');
- $mysql['click_landing_site_url_id'] = mysql_real_escape_string($click_row['click_landing_site_url_id']);
- $site_url_sql = "SELECT * FROM 202_site_urls LEFT JOIN 202_site_domains USING (site_domain_id)
- WHERE 202_site_urls.site_url_id = '".$mysql['click_landing_site_url_id']."'
- AND 202_site_urls.site_domain_id = 202_site_domains.site_domain_id";
- $site_url_row = memcache_mysql_fetch_assoc($site_url_sql);
- $html['landing'] = htmlentities($site_url_row['site_url_address'], ENT_QUOTES, 'UTF-8');
- $html['landing_host'] = htmlentities($site_url_row['site_domain_host'], ENT_QUOTES, 'UTF-8');
-
- $mysql['click_outbound_site_url_id'] = mysql_real_escape_string($click_row['click_outbound_site_url_id']);
- $site_url_sql = "SELECT * FROM 202_site_urls LEFT JOIN 202_site_domains USING (site_domain_id)
- WHERE 202_site_urls.site_url_id = '".$mysql['click_outbound_site_url_id']."'
- AND 202_site_urls.site_domain_id = 202_site_domains.site_domain_id";
- $site_url_row = memcache_mysql_fetch_assoc($site_url_sql);
- $html['outbound'] = htmlentities($site_url_row['site_url_address'], ENT_QUOTES, 'UTF-8');
- $html['outbound_host'] = htmlentities($site_url_row['site_domain_host'], ENT_QUOTES, 'UTF-8');
- //this is alittle different
- if ($click_row['click_cloaking']) {
-
- //if not a landing page
- if (!$click_row['click_alp']) {
- $html['cloaking'] = htmlentities( 'http://' .$_SERVER['SERVER_NAME'] . '/tracking202/redirect/cl.php?pci=' . $click_row['click_id_public'] );
- $html['cloaking_host'] = htmlentities( $_SERVER['SERVER_NAME'] );
- } else {
- //advanced lander
- $html['cloaking'] = htmlentities( 'http://' .$_SERVER['SERVER_NAME'] . '/tracking202/redirect/off.php?acip='. $click_row['aff_campaign_id_public'] . '&pci=' . $click_row['click_id_public'] );
- $html['cloaking_host'] = htmlentities( $_SERVER['SERVER_NAME'] );
- }
- } else {
- $html['cloaking'] = '';
- $html['cloaking_host'] = '';
- }
- $mysql['click_redirect_site_url_id'] = mysql_real_escape_string($click_row['click_redirect_site_url_id']);
- $site_url_sql = "SELECT * FROM 202_site_urls LEFT JOIN 202_site_domains USING (site_domain_id)
- WHERE 202_site_urls.site_url_id = '".$mysql['click_redirect_site_url_id']."'
- AND 202_site_urls.site_domain_id = 202_site_domains.site_domain_id";
- $site_url_result = mysql_query($site_url_sql) or record_mysql_error($site_url_sql);
- $site_url_row = mysql_fetch_assoc($site_url_result);
- $html['redirect'] = htmlentities($site_url_row['site_url_address'], ENT_QUOTES, 'UTF-8');
- $html['redirect_host'] = htmlentities($site_url_row['site_domain_host'], ENT_QUOTES, 'UTF-8');
-
-
- $html['aff_campaign_id'] = htmlentities($click_row['aff_campaign_id'], ENT_QUOTES, 'UTF-8');
- $html['landing_page_nickname'] = htmlentities($click_row['landing_page_nickname'], ENT_QUOTES, 'UTF-8');
- $html['ppc_account_id'] = htmlentities($click_row['ppc_account_id'], ENT_QUOTES, 'UTF-8');
- $html['text_ad_id'] = htmlentities($click_row['text_ad_id'], ENT_QUOTES, 'UTF-8');
- $html['text_ad_name'] = htmlentities($click_row['text_ad_name'], ENT_QUOTES, 'UTF-8');
- $html['aff_campaign_name'] = htmlentities($click_row['aff_campaign_name'], ENT_QUOTES, 'UTF-8');
- $html['aff_network_name'] = htmlentities($click_row['aff_network_name'], ENT_QUOTES, 'UTF-8');
- $html['ppc_network_name'] = htmlentities($click_row['ppc_network_name'], ENT_QUOTES, 'UTF-8');
- $html['ppc_account_name'] = htmlentities($click_row['ppc_account_name'], ENT_QUOTES, 'UTF-8');
- $html['ip_address'] = htmlentities($click_row['ip_address'], ENT_QUOTES, 'UTF-8');
- $html['click_cpc'] = htmlentities(dollar_format($click_row['click_cpc']), ENT_QUOTES, 'UTF-8');
- $html['keyword'] = htmlentities($click_row['keyword'], ENT_QUOTES, 'UTF-8');
- $html['click_lead'] = htmlentities($click_row['click_lead'], ENT_QUOTES, 'UTF-8');
- $html['click_filtered'] = htmlentities($click_row['click_filtered'], ENT_QUOTES, 'UTF-8');
-
- $html['location'] = '';
- if ($click_row['location_country_name']) {
- if ($click_row['location_country_name']) {
- $origin = $click_row['location_country_name'];
- } if (($click_row['location_region_code']) and (!is_numeric($click_row['location_region_code']))) {
- $origin = $click_row['location_region_code'] . ', ' . $origin;
- } if ($click_row['location_city_name']) {
- $origin = $click_row['location_city_name'] . ', ' . $origin;
- }
-
- $html['origin'] = htmlentities($origin, ENT_QUOTES, 'UTF-8');
- }
-
- if ($click_row['click_filtered'] == '1') {
- $click_filtered = 'filtered';
- } elseif ($click_row['click_lead'] == '1') {
- $click_filtered = 'conversion';
- } else {
- $click_filtered = 'real';
- }
-
- echo $click_row['click_id'] . "\t" .
- date('m/d/y g:ia',$click_row['click_time']) . "\t" .
- $click_row['browser_name'] . "\t" .
- $click_row['platform_name'] . "\t" .
- $click_row['ppc_network_name'] . "\t" .
- $click_row['ppc_account_name'] . "\t" .
- $click_filtered . "\t" .
- $click_row['ip_address'] . "\t" .
- $click_row['aff_campaign_name'] . "\t" .
- $click_row['text_ad_name'] . "\t" .
- $html['referer'] . "\t" .
- $html['landing'] . "\t" .
- $html['outbound'] . "\t" .
- $html['cloaking'] . "\t" .
- $html['redirect'] . "\t" .
- $click_row['keyword'] . "\n";
-
- }