</tr> <?php } ?> <?php $rows = count($breakdown_result); $html['clicks'] = BTHtml::encode($stats_total['clicks']); $html['leads'] = BTHtml::encode($stats_total['leads']); $html['conv'] = BTHtml::encode(calculate_conv($stats_total['clicks'], $stats_total['leads']) . '%'); $html['payout'] = BTHtml::encode(dollar_format(calculate_payout($stats_total['leads'], $stats_total['income']))); $html['epc'] = BTHtml::encode(dollar_format(calculate_epc($stats_total['clicks'], $stats_total['income']))); $html['cpc'] = BTHtml::encode(dollar_format(calculate_cpc($stats_total['clicks'], $stats_total['cost']))); $html['income'] = BTHtml::encode(dollar_format($stats_total['income'])); $html['cost'] = BTHtml::encode(dollar_format($stats_total['cost'])); $html['net'] = BTHtml::encode(dollar_format(calculate_net($stats_total['income'], $stats_total['cost']))); $html['roi'] = BTHtml::encode(calculate_roi($stats_total['income'], $stats_total['cost']) . '%'); ?> </tbody> <?php $stat_html = ob_get_contents(); ob_end_clean(); ?> <tfoot> <tr> <td><strong>Totals for report</strong></td> <td><strong><?php
$mysql['user_id'] = mysql_real_escape_string($_SESSION['user_id']);
$user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM 202_users_pref WHERE user_id=" . $mysql['user_id'];
$user_result = _mysql_query($user_sql, $dbGlobalLink);
//($user_sql);
$user_row = mysql_fetch_assoc($user_result);
$breakdown = $user_row['user_pref_breakdown'];
if ($user_row['user_cpc_or_cpv'] == 'cpv') {
$cpv = true;
} else {
$cpv = false;
}
//keywords already set in the table, just just download them
if (empty($mysql['order'])) {
$mysql['order'] = ' ORDER BY sort_referer_clicks DESC';
}
$db_table = '202_sort_referers';
$query = query('SELECT * FROM 202_sort_referers LEFT JOIN 202_site_domains ON (202_sort_referers.referer_id=202_site_domains.site_domain_id)', $db_table, false, false, false, $mysql['order'], false, false, true);
$keyword_sql = $query['click_sql'];
$keyword_result = mysql_query($keyword_sql) or record_mysql_error($keyword_sql);
header("Content-type: application/octet-stream");
# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=T202_referers_" . time() . ".xls");
header("Pragma: no-cache");
header("Expires: 0");
echo "Refering Domain" . "\t" . "Clicks" . "\t" . "Leads" . "\t" . "S/U" . "\t" . "Payout" . "\t" . "EPC" . "\t" . "Avg CPC" . "\t" . "Income" . "\t" . "Cost" . "\t" . "Net" . "\t" . "ROI" . "\n";
while ($keyword_row = mysql_fetch_array($keyword_result, MYSQL_ASSOC)) {
if (!$keyword_row['site_domain_host']) {
$keyword_row['site_domain_host'] = '[no referer]';
}
echo $keyword_row['site_domain_host'] . "\t" . $keyword_row['sort_referer_clicks'] . "\t" . $keyword_row['sort_referer_leads'] . "\t" . $keyword_row['sort_referer_su_ratio'] . '%' . "\t" . dollar_format($keyword_row['sort_referer_payout']) . "\t" . dollar_format($keyword_row['sort_referer_epc']) . "\t" . dollar_format($keyword_row['sort_referer_avg_cpc'], $cpv) . "\t" . dollar_format($keyword_row['sort_referer_income']) . "\t" . dollar_format($keyword_row['sort_referer_cost'], $cpv) . "\t" . dollar_format($keyword_row['sort_referer_net'], $cpv) . "\t" . $keyword_row['sort_referer_roi'] . '%' . "\n";
}
$ctr_ratio = @round($click_throughs / $clicks * 100, 2);
//cost
$cost = 0;
$cost = $click_row['cost'];
//avg cpc and cost
$avg_cpc = 0;
$avg_cpc = $click_row['avg_cpc'];
//leads
$leads = 0;
$leads = $click_row['leads'];
//signup ratio
$su_ratio - 0;
$su_ratio = @round($leads / $clicks * 100, 2);
//current payout
$payout = 0;
$payout = $click_row['payout'];
//income
$income = 0;
$income = $click_row['income'];
//grab the EPC
$epc = 0;
$epc = @round($income / $clicks, 2);
//net income
$net = 0;
$net = $income - $cost;
//roi
$roi = 0;
$roi = @round($net / $cost * 100);
echo $html['browser_name'] . "\t" . $clicks . "\t" . $click_throughs . "\t" . $ctr_ratio . "\t" . $leads . "\t" . $su_ratio . '%' . "\t" . dollar_format($payout) . "\t" . dollar_format($epc) . "\t" . dollar_format($avg_cpc, $cpv) . "\t" . dollar_format($income) . "\t" . dollar_format($cost, $cpv) . "\t" . dollar_format($net, $cpv) . "\t" . $roi . '%' . "\n";
}
}
</div>
<div class="col-sm-6">
<div class="form-group">
<label for="txtMaxPrice" class="control-label">Max. Price</label>
<select name="txtMaxPrice" id="txtMaxPrice" class="form-control">
<option value="">Any Maximum Price</option>
<?php
$min = 2500;
$max = 200000;
for ($i = $max; $i >= $min; $i -= 500) {
?>
<option value="<?php
echo dollar_format($i);
?>
"><?php
echo dollar_format($i);
?>
</option>
<?php
}
?>
</select>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="panel panel-default">
$html['ip'] = htmlentities($ip_row['ip_address'], ENT_QUOTES, 'UTF-8');
//shorten ip
if (strlen($html['ip']) > 25) {
$html['ip'] = substr($html['ip'], 0, 25) . '...';
}
}
error_reporting(0);
$html['sort_ip_clicks'] = htmlentities($ip_row['sort_ip_clicks'], ENT_QUOTES, 'UTF-8');
$html['sort_ip_leads'] = htmlentities($ip_row['sort_ip_leads'], ENT_QUOTES, 'UTF-8');
$html['sort_ip_su_ratio'] = htmlentities($ip_row['sort_ip_su_ratio'] . '%', ENT_QUOTES, 'UTF-8');
$html['sort_ip_payout'] = htmlentities(dollar_format($ip_row['sort_ip_payout']), ENT_QUOTES, 'UTF-8');
$html['sort_ip_epc'] = htmlentities(dollar_format($ip_row['sort_ip_epc']), ENT_QUOTES, 'UTF-8');
$html['sort_ip_avg_cpc'] = htmlentities(dollar_format($ip_row['sort_ip_avg_cpc'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_ip_income'] = htmlentities(dollar_format($ip_row['sort_ip_income']), ENT_QUOTES, 'UTF-8');
$html['sort_ip_cost'] = htmlentities(dollar_format($ip_row['sort_ip_cost'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_ip_net'] = htmlentities(dollar_format($ip_row['sort_ip_net'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_ip_roi'] = htmlentities($ip_row['sort_ip_roi'] . '%', ENT_QUOTES, 'UTF-8');
error_reporting(6135);
?>
<tr>
<td class="m-row2 m-row2-fade" ><?php
printf('<a target="_new" href="http://ws.arin.net/whois/?queryinput=%s">ARIN</a> / <a target="_new" href="http://www.db.ripe.net/whois?searchtext=%s">RIPE</a>', $html['ip'], $html['ip']);
?>
:: <?php
echo $html['ip'];
?>
</td>
<td class="m-row1"><?php
echo $html['sort_ip_clicks'];
$mysql['user_id'] = mysql_real_escape_string($_SESSION['user_id']);
$user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM 202_users_pref WHERE user_id=" . $mysql['user_id'];
$user_result = _mysql_query($user_sql, $dbGlobalLink);
//($user_sql);
$user_row = mysql_fetch_assoc($user_result);
$breakdown = $user_row['user_pref_breakdown'];
if ($user_row['user_cpc_or_cpv'] == 'cpv') {
$cpv = true;
} else {
$cpv = false;
}
//keywords already set in the table, just just download them
if (empty($mysql['order'])) {
$mysql['order'] = ' ORDER BY sort_text_ad_clicks DESC';
}
$db_table = '202_sort_text_ads';
$query = query('SELECT * FROM 202_sort_text_ads LEFT JOIN 202_text_ads USING (text_ad_id)', $db_table, false, false, false, $mysql['order'], false, false, true);
$keyword_sql = $query['click_sql'];
$keyword_result = mysql_query($keyword_sql) or record_mysql_error($keyword_sql);
header("Content-type: application/octet-stream");
# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=T202_textads_" . time() . ".xls");
header("Pragma: no-cache");
header("Expires: 0");
echo "Text Ad" . "\t" . "Clicks" . "\t" . "Leads" . "\t" . "S/U" . "\t" . "Payout" . "\t" . "EPC" . "\t" . "Avg CPC" . "\t" . "Income" . "\t" . "Cost" . "\t" . "Net" . "\t" . "ROI" . "\n";
while ($keyword_row = mysql_fetch_array($keyword_result, MYSQL_ASSOC)) {
if (!$keyword_row['text_ad_name']) {
$keyword_row['text_ad_name'] = '[no text ad recorded]';
}
echo $keyword_row['text_ad_name'] . "\t" . $keyword_row['sort_text_ad_clicks'] . "\t" . $keyword_row['sort_text_ad_leads'] . "\t" . $keyword_row['sort_text_ad_su_ratio'] . '%' . "\t" . dollar_format($keyword_row['sort_text_ad_payout']) . "\t" . dollar_format($keyword_row['sort_text_ad_epc']) . "\t" . dollar_format($keyword_row['sort_text_ad_avg_cpc'], $cpv) . "\t" . dollar_format($keyword_row['sort_text_ad_income']) . "\t" . dollar_format($keyword_row['sort_text_ad_cost'], $cpv) . "\t" . dollar_format($keyword_row['sort_text_ad_net'], $cpv) . "\t" . $keyword_row['sort_text_ad_roi'] . '%' . "\n";
}
<?php } ?> <?php error_reporting(0); $rows = mysql_num_rows($breakdown_result); $html['clicks'] = htmlentities($stats_total['clicks'], ENT_QUOTES, 'UTF-8'); $html['leads'] = htmlentities($stats_total['leads'], ENT_QUOTES, 'UTF-8'); $html['su_ratio'] = htmlentities(round($stats_total['leads'] / $stats_total['clicks'] * 100, 2) . '%', ENT_QUOTES, 'UTF-8'); $html['payout'] = htmlentities(dollar_format($stats_total['payout'] / $rows), ENT_QUOTES, 'UTF-8'); $html['epc'] = htmlentities(dollar_format($stats_total['income'] / $stats_total['clicks']), ENT_QUOTES, 'UTF-8'); $html['cpc'] = htmlentities(dollar_format($stats_total['cost'] / $stats_total['clicks'], $cpv), ENT_QUOTES, 'UTF-8'); $html['income'] = htmlentities(dollar_format($stats_total['income']), ENT_QUOTES, 'UTF-8'); $html['cost'] = htmlentities(dollar_format($stats_total['cost'], $cpv), ENT_QUOTES, 'UTF-8'); $html['net'] = htmlentities(dollar_format($stats_total['income'] - $stats_total['cost'], $cpv), ENT_QUOTES, 'UTF-8'); $html['roi'] = htmlentities(round(($stats_total['income'] - $stats_total['cost']) / $stats_total['cost'] * 100) . '%', ENT_QUOTES, 'UTF-8'); error_reporting(6135); ?> <tr> <td class="m-row2 m-row-bottom"><strong>Totals for report</strong></td> <td class="m-row1 m-row-bottom"><strong><?php echo $html['clicks']; ?> </strong></td> <td class="m-row1 m-row-bottom"><strong><?php echo $html['leads']; ?> </strong></td> <td class="m-row1 m-row-bottom"><strong><?php
public static function displayValue($value = null, $wrap_class = null) {
return dollar_format($value, $wrap_class);
}
$html['text_ad'] = htmlentities($text_ad_row['text_ad_name'], ENT_QUOTES, 'UTF-8');
//shorten text_ad
/*if (strlen($html['text_ad']) > 25) {
$html['text_ad'] = substr($html['text_ad'],0,25) . '...';
}*/
}
error_reporting(0);
$html['sort_text_ad_clicks'] = htmlentities($text_ad_row['sort_text_ad_clicks'], ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_leads'] = htmlentities($text_ad_row['sort_text_ad_leads'], ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_su_ratio'] = htmlentities($text_ad_row['sort_text_ad_su_ratio'] . '%', ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_payout'] = htmlentities(dollar_format($text_ad_row['sort_text_ad_payout']), ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_epc'] = htmlentities(dollar_format($text_ad_row['sort_text_ad_epc']), ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_avg_cpc'] = htmlentities(dollar_format($text_ad_row['sort_text_ad_avg_cpc'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_income'] = htmlentities(dollar_format($text_ad_row['sort_text_ad_income']), ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_cost'] = htmlentities(dollar_format($text_ad_row['sort_text_ad_cost'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_net'] = htmlentities(dollar_format($text_ad_row['sort_text_ad_net'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_text_ad_roi'] = htmlentities($text_ad_row['sort_text_ad_roi'] . '%', ENT_QUOTES, 'UTF-8');
error_reporting(6135);
?>
<tr>
<td class="m-row2 m-row2-fade" ><?php
echo $html['text_ad'];
?>
</td>
<td class="m-row1"><?php
echo $html['sort_text_ad_clicks'];
?>
</td>
<td class="m-row1"><?php
function reportQuery($db, $type, $id, $name, $user, $date_from, $date_to, $cid = null, $c1 = null, $c2 = null, $c3 = null, $c4 = null)
{
$date = array('date_from' => date('m/d/Y', $date_from), 'date_to' => date('m/d/Y', $date_to), 'time_zone' => date_default_timezone_get());
$data = array();
$mysql['user_id'] = $db->real_escape_string($user);
$select_id = $db->real_escape_string($id);
$mysql['date_from'] = $db->real_escape_string($date_from);
$mysql['date_to'] = $db->real_escape_string($date_to);
$mysql['aff_campaign_id'] = $db->real_escape_string($cid);
$mysql['c1'] = $db->real_escape_string($c1);
$mysql['c2'] = $db->real_escape_string($c2);
$mysql['c3'] = $db->real_escape_string($c3);
$mysql['c4'] = $db->real_escape_string($c4);
$report_sql = "SELECT *\n\t\t\t\tFROM \t202_clicks AS 2c\n\t\t\t\tLEFT OUTER JOIN 202_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id)";
//If referers report type
if ($type == "referers") {
$report_sql .= "\n\t\t\t\t\t\tLEFT OUTER JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)\n\t\t\t\t\t\tLEFT OUTER JOIN 202_site_urls AS 2su ON (2cs.click_referer_site_url_id = 2su.site_url_id)\n\t\t\t\t\t\tLEFT OUTER JOIN 202_site_domains AS 2l ON (2l.site_domain_id = 2su.site_domain_id)";
//If landing pages report type
} elseif ($type == "landing_pages") {
$report_sql .= " LEFT OUTER JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)\n\t\t\t\t\t\t\t\t\t LEFT OUTER JOIN 202_landing_pages AS 2lp ON (2lp.landing_page_id = 2c.landing_page_id)";
} else {
//If any other report type
$report_sql .= " LEFT OUTER JOIN 202_" . $type . " AS 2l ON (2l." . $select_id . " = 2ca." . $select_id . ")";
}
//If any of C1-C4 variables are set
if ($mysql['c1'] || $mysql['c2'] || $mysql['c3'] || $mysql['c4']) {
$report_sql .= "LEFT OUTER JOIN 202_clicks_tracking AS 2cv ON (2cv.click_id = 2c.click_id)";
if ($mysql['c1']) {
$report_sql .= "LEFT OUTER JOIN 202_tracking_c1 AS 2c1 ON (2c1.c1_id = 2cv.c1_id)";
}
if ($mysql['c2']) {
$report_sql .= "LEFT OUTER JOIN 202_tracking_c2 AS 2c2 ON (2c2.c2_id = 2cv.c2_id)";
}
if ($mysql['c3']) {
$report_sql .= "LEFT OUTER JOIN 202_tracking_c3 AS 2c3 ON (2c3.c3_id = 2cv.c3_id)";
}
if ($mysql['c4']) {
$report_sql .= "LEFT OUTER JOIN 202_tracking_c4 AS 2c4 ON (2c4.c4_id = 2cv.c4_id)";
}
}
$report_sql .= " WHERE 2c.user_id='" . $mysql['user_id'] . "' AND click_time > " . $mysql['date_from'] . " AND click_time < " . $mysql['date_to'] . "";
//If C variables are set
if ($mysql['c1']) {
$report_sql .= " AND 2c1.c1='" . $mysql['c1'] . "'";
}
if ($mysql['c2']) {
$report_sql .= " AND 2c2.c2='" . $mysql['c2'] . "'";
}
if ($mysql['c3']) {
$report_sql .= " AND 2c3.c3='" . $mysql['c3'] . "'";
}
if ($mysql['c4']) {
$report_sql .= " AND 2c4.c4='" . $mysql['c4'] . "'";
}
//If CID variable set
if ($mysql['aff_campaign_id']) {
$report_sql .= " AND 2c.aff_campaign_id='" . $mysql['aff_campaign_id'] . "'";
}
//If ISP/Carriers report type
if ($type == "locations_isp") {
$report_sql .= " AND 2ca.{$select_id} >= 1";
}
//If landing pages report type
if ($type == "landing_pages") {
$report_sql .= " GROUP BY 2c.landing_page_id";
} else {
$report_sql .= " GROUP BY 2l.{$select_id}";
}
$report_result = $db->query($report_sql);
$rows = $report_result->num_rows;
if ($rows > 0) {
while ($report_row = $report_result->fetch_assoc()) {
$click_sql = "SELECT \n\t\t\t\t\t\t\tCOUNT(*) AS clicks,\n\t\t\t\t\t\t\tAVG(2c.click_cpc) AS avg_cpc,\n\t\t\t\t\t\t\tSUM(2cr.click_out) AS click_throughs,\n\t\t\t\t\t\t\tSUM(2c.click_lead) AS leads,\n\t\t\t\t\t\t\tSUM(2c.click_payout*2c.click_lead) AS income\n\t\t\t\t\t FROM\n\t\t\t\t\t\t\t202_clicks AS 2c\n\t\t\t\t\t LEFT OUTER JOIN 202_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id)";
//If referers report type
if ($type == "referers") {
$click_sql .= "\n\t\t\t\t\t \t\tLEFT OUTER JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)\n\t\t\t\t\t\t\tLEFT OUTER JOIN 202_site_urls AS 2su ON (2cs.click_referer_site_url_id=2su.site_url_id)\n\t\t\t\t\t\t\tLEFT OUTER JOIN 202_site_domains AS 2l ON (2l.site_domain_id = 2su.site_domain_id)";
} else {
$report_sql .= " LEFT OUTER JOIN 202_" . $type . " AS 2l ON (2l." . $select_id . " = 2ca." . $select_id . ")";
}
//If any of C1-C4 variables are set
if ($mysql['c1'] || $mysql['c2'] || $mysql['c3'] || $mysql['c4']) {
$click_sql .= "LEFT OUTER JOIN 202_clicks_tracking AS 2cv ON (2cv.click_id = 2c.click_id)";
if ($mysql['c1']) {
$click_sql .= "LEFT OUTER JOIN 202_tracking_c1 AS 2c1 ON (2c1.c1_id = 2cv.c1_id)";
}
if ($mysql['c2']) {
$click_sql .= "LEFT OUTER JOIN 202_tracking_c2 AS 2c2 ON (2c2.c2_id = 2cv.c2_id)";
}
if ($mysql['c3']) {
$click_sql .= "LEFT OUTER JOIN 202_tracking_c3 AS 2c3 ON (2c3.c3_id = 2cv.c3_id)";
}
if ($mysql['c4']) {
$click_sql .= "LEFT OUTER JOIN 202_tracking_c4 AS 2c4 ON (2c4.c4_id = 2cv.c4_id)";
}
}
//If any other
$click_sql .= " LEFT OUTER JOIN 202_clicks_record AS 2cr ON (2cr.click_id = 2c.click_id)\n\t\t\t\t\t \t\t\t\t WHERE 2c.user_id='" . $mysql['user_id'] . "' AND click_time > '" . $mysql['date_from'] . "' AND click_time < '" . $mysql['date_to'] . "'";
//If C variables are set
if ($mysql['c1']) {
$click_sql .= " AND 2c1.c1='" . $mysql['c1'] . "'";
}
if ($mysql['c2']) {
$click_sql .= " AND 2c2.c2='" . $mysql['c2'] . "'";
}
if ($mysql['c3']) {
$click_sql .= " AND 2c3.c3='" . $mysql['c3'] . "'";
}
if ($mysql['c4']) {
$click_sql .= " AND 2c4.c4='" . $mysql['c4'] . "'";
}
//If CID variable is set
if ($mysql['aff_campaign_id']) {
$click_sql .= " AND 2c.aff_campaign_id='" . $mysql['aff_campaign_id'] . "'";
}
//If referers report type
if ($type == "referers") {
$click_sql .= "AND 2l." . $select_id . "='" . $report_row[$select_id] . "'";
//If landing pages report type
} elseif ($type == "landing_pages") {
$click_sql .= "AND 2c." . $select_id . "='" . $report_row[$select_id] . "'\n\t\t\t\t\t \t\t\t\t GROUP BY 2c." . $select_id;
} else {
$click_sql .= "AND 2ca." . $select_id . "='" . $report_row[$select_id] . "'";
}
$click_result = $db->query($click_sql);
$click_row = $click_result->fetch_assoc();
$country_code = '';
//get the stats
$clicks = 0;
$clicks = $click_row['clicks'];
$total_clicks = $total_clicks + $clicks;
$click_throughs = 0;
$click_throughs = $click_row['click_throughs'];
$total_click_throughs = $total_click_throughs + $click_throughs;
//ctr rate
$ctr_ratio = 0;
$ctr_ratio = @round($click_throughs / $clicks * 100, 2);
$total_ctr_ratio = @round($total_click_throughs / $total_clicks * 100, 2);
//avg cpc and cost
$avg_cpc = 0;
$avg_cpc = $click_row['avg_cpc'];
$cost = 0;
$cost = $clicks * $avg_cpc;
$total_cost = $total_cost + $cost;
$total_avg_cpc = @round($total_cost / $total_clicks, 5);
//leads
$leads = 0;
$leads = $click_row['leads'];
$total_leads = $total_leads + $leads;
//signup ratio
$su_ratio - 0;
$su_ratio = @round($leads / $clicks * 100, 2);
$total_su_ratio = @round($total_leads / $total_clicks * 100, 2);
//current payout
$payout = 0;
$payout = $report_row['click_payout'];
$total_payout = $total_payout + $payout;
//income
$income = 0;
$income = $click_row['income'];
$total_income = $total_income + $income;
//grab the EPC
$epc = 0;
$epc = @round($income / $clicks, 2);
$total_epc = @@round($total_income / $total_clicks, 2);
//net income
$net = 0;
$net = $income - $cost;
$total_net = $total_income - $total_cost;
//roi
$roi = 0;
$roi = @round($net / $cost * 100);
$total_roi = @round($total_net / $total_cost);
if ($name == "keyword") {
if (!$report_row['keyword']) {
$report_row[$name] = "[no keyword]";
}
}
if ($name == "text_ad_name") {
if (!$report_row['text_ad_name']) {
$report_row[$name] = "[no text ad]";
}
}
if ($name == "referer") {
if (!$report_row['site_domain_host']) {
$report_row[$name] = "[no referer]";
} else {
$report_row[$name] = $report_row['site_domain_host'];
}
}
if ($type == "locations_country") {
$type = "countries";
}
if ($type == "locations_city") {
$type = "cities";
}
if ($type == "locations_isp") {
$type = "carriers";
}
if ($type == "landing_pages") {
if (!$report_row['landing_page_nickname']) {
$report_row[$name] = "[direct link]";
} else {
$report_row[$name] = $report_row['landing_page_nickname'];
}
}
$data[] = array($name => $report_row[$name], "clicks" => $clicks, "click_throughs" => $click_throughs, "lp_ctr" => $ctr_ratio . "%", "leads" => $leads, "su_ratio" => $su_ratio . "%", "payout" => dollar_format($payout), "epc" => dollar_format($epc), "avg_cpc" => "\$" . $avg_cpc, "income" => dollar_format($income), "cost" => dollar_format($cost), "net" => dollar_format($net), "roi" => $roi . "%");
}
$totals = array("clicks" => $total_clicks, "click_throughs" => $total_click_throughs, "lp_ctr" => $total_ctr_ratio . "%", "leads" => $total_leads, "su_ratio" => $total_su_ratio . "%", "payout" => dollar_format($total_payout / $rows), "epc" => dollar_format($total_epc), "avg_cpc" => dollar_format($total_avg_cpc), "income" => dollar_format($total_income), "cost" => dollar_format($total_cost), "net" => dollar_format($total_net), "roi" => $total_roi . "%");
} else {
$totals = array();
}
return array("date_range" => $date, $type => $data, "totals" => $totals);
}
/** See docs for dollar_format for $opts */
public static function displayValue($value = null, $opts = null)
{
return dollar_format($value, $opts);
}
$mysql['user_id'] = mysql_real_escape_string($_SESSION['user_id']);
$user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM 202_users_pref WHERE user_id=" . $mysql['user_id'];
$user_result = _mysql_query($user_sql, $dbGlobalLink);
//($user_sql);
$user_row = mysql_fetch_assoc($user_result);
$breakdown = $user_row['user_pref_breakdown'];
if ($user_row['user_cpc_or_cpv'] == 'cpv') {
$cpv = true;
} else {
$cpv = false;
}
//ips already set in the table, just just download them
if (empty($mysql['order'])) {
$mysql['order'] = ' ORDER BY sort_ip_clicks DESC';
}
$db_table = '202_sort_ips';
$query = query('SELECT * FROM 202_sort_ips LEFT JOIN 202_ips USING (ip_id)', $db_table, false, false, false, $mysql['order'], false, false, true);
$ip_sql = $query['click_sql'];
$ip_result = mysql_query($ip_sql) or record_mysql_error($ip_sql);
header("Content-type: application/octet-stream");
# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=XT_ips_" . time() . ".xls");
header("Pragma: no-cache");
header("Expires: 0");
echo "ip" . "\t" . "Clicks" . "\t" . "Leads" . "\t" . "S/U" . "\t" . "Payout" . "\t" . "EPC" . "\t" . "Avg CPC" . "\t" . "Income" . "\t" . "Cost" . "\t" . "Net" . "\t" . "ROI" . "\n";
while ($ip_row = mysql_fetch_array($ip_result, MYSQL_ASSOC)) {
if (!$ip_row['ip_address']) {
$ip_row['ip_address'] = '[no ip]';
}
echo $ip_row['ip_address'] . "\t" . $ip_row['sort_ip_clicks'] . "\t" . $ip_row['sort_ip_leads'] . "\t" . $ip_row['sort_ip_su_ratio'] . '%' . "\t" . dollar_format($ip_row['sort_ip_payout']) . "\t" . dollar_format($ip_row['sort_ip_epc']) . "\t" . dollar_format($ip_row['sort_ip_avg_cpc'], $cpv) . "\t" . dollar_format($ip_row['sort_ip_income']) . "\t" . dollar_format($ip_row['sort_ip_cost'], $cpv) . "\t" . dollar_format($ip_row['sort_ip_net'], $cpv) . "\t" . $ip_row['sort_ip_roi'] . '%' . "\n";
}
function formatColumnValue($col, $value)
{
switch ($col) {
case 'click_through_rates':
case 'conv':
case 'roi':
$value = number_format($value, 2, '.', '') . '%';
break;
case 'payout':
case 'income':
case 'net':
case 'epc':
case 'cpc':
case 'avg_cpc':
case 'cost':
$value = dollar_format($value);
}
return $value;
}
/** Displays a number as dollar format - or if array arg, totals the numeric
* values of the array and retuns them as a $ formatted string
* @param numeric|numeric array $num - the value or array of values to represent
* @param int|string|array $opts:
* if int - precision $prec.
* If string - $wrap_class: enclose results in div w. class; negative in "$wrap_class negative-dollar-value"
* If boolean true - $wrap_class: enclose results in div w. class 'dollar-format'; negative in "$wrap_class negative-value"
* If array, look for those keys, & 'hide0' as well, for multiple options
* @return string - dollar formatted
*/
function d_f($num, $opts = 0, $hide0 = false) {
return dollar_format($num, $opts, $hide0);
}
$html['keyword'] = htmlentities($keyword_row['keyword'], ENT_QUOTES, 'UTF-8');
//shorten keyword
/*if (strlen($html['keyword']) > 25) {
$html['keyword'] = substr($html['keyword'],0,25) . '...';
}*/
}
error_reporting(0);
$html['sort_keyword_clicks'] = htmlentities($keyword_row['sort_keyword_clicks'], ENT_QUOTES, 'UTF-8');
$html['sort_keyword_leads'] = htmlentities($keyword_row['sort_keyword_leads'], ENT_QUOTES, 'UTF-8');
$html['sort_keyword_su_ratio'] = htmlentities($keyword_row['sort_keyword_su_ratio'] . '%', ENT_QUOTES, 'UTF-8');
$html['sort_keyword_payout'] = htmlentities(dollar_format($keyword_row['sort_keyword_payout']), ENT_QUOTES, 'UTF-8');
$html['sort_keyword_epc'] = htmlentities(dollar_format($keyword_row['sort_keyword_epc']), ENT_QUOTES, 'UTF-8');
$html['sort_keyword_avg_cpc'] = htmlentities(dollar_format($keyword_row['sort_keyword_avg_cpc'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_keyword_income'] = htmlentities(dollar_format($keyword_row['sort_keyword_income']), ENT_QUOTES, 'UTF-8');
$html['sort_keyword_cost'] = htmlentities(dollar_format($keyword_row['sort_keyword_cost'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_keyword_net'] = htmlentities(dollar_format($keyword_row['sort_keyword_net'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_keyword_roi'] = htmlentities($keyword_row['sort_keyword_roi'] . '%', ENT_QUOTES, 'UTF-8');
error_reporting(6135);
?>
<tr>
<td class="m-row2 m-row2-fade" ><?php
echo $html['keyword'];
?>
</td>
<td class="m-row1"><?php
echo $html['sort_keyword_clicks'];
?>
</td>
<td class="m-row1"><?php
$ctr_ratio = @round($click_throughs / $clicks * 100, 2);
//cost
$cost = 0;
$cost = $click_row['cost'];
//avg cpc and cost
$avg_cpc = 0;
$avg_cpc = $click_row['avg_cpc'];
//leads
$leads = 0;
$leads = $click_row['leads'];
//signup ratio
$su_ratio - 0;
$su_ratio = @round($leads / $clicks * 100, 2);
//current payout
$payout = 0;
$payout = $click_row['payout'];
//income
$income = 0;
$income = $click_row['income'];
//grab the EPC
$epc = 0;
$epc = @round($income / $clicks, 2);
//net income
$net = 0;
$net = $income - $cost;
//roi
$roi = 0;
$roi = @round($net / $cost * 100);
echo $click_row['city_name'] . "\t" . $click_row['country_code'] . "\t" . $clicks . "\t" . $click_throughs . "\t" . $ctr_ratio . "\t" . $leads . "\t" . $su_ratio . '%' . "\t" . dollar_format($payout) . "\t" . dollar_format($epc) . "\t" . dollar_format($avg_cpc, $cpv) . "\t" . dollar_format($income) . "\t" . dollar_format($cost, $cpv) . "\t" . dollar_format($net, $cpv) . "\t" . $roi . '%' . "\n";
}
}
/*if (strlen($html['referer']) > 25) {
$html['referer'] = substr($html['referer'],0,25) . '...';
}*/
$html['site_domain_host'] = htmlentities($referer_row['site_domain_host']);
$html['referer'] = '<a target="_new" title="' . $html['site_domain_host'] . '" href="http://' . $html['site_domain_host'] . '">' . $html['referer'] . '</a>';
}
error_reporting(0);
$html['sort_referer_clicks'] = htmlentities($referer_row['sort_referer_clicks'], ENT_QUOTES, 'UTF-8');
$html['sort_referer_leads'] = htmlentities($referer_row['sort_referer_leads'], ENT_QUOTES, 'UTF-8');
$html['sort_referer_su_ratio'] = htmlentities($referer_row['sort_referer_su_ratio'] . '%', ENT_QUOTES, 'UTF-8');
$html['sort_referer_payout'] = htmlentities(dollar_format($referer_row['sort_referer_payout']), ENT_QUOTES, 'UTF-8');
$html['sort_referer_epc'] = htmlentities(dollar_format($referer_row['sort_referer_epc']), ENT_QUOTES, 'UTF-8');
$html['sort_referer_avg_cpc'] = htmlentities(dollar_format($referer_row['sort_referer_avg_cpc'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_referer_income'] = htmlentities(dollar_format($referer_row['sort_referer_income']), ENT_QUOTES, 'UTF-8');
$html['sort_referer_cost'] = htmlentities(dollar_format($referer_row['sort_referer_cost'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_referer_net'] = htmlentities(dollar_format($referer_row['sort_referer_net'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_referer_roi'] = htmlentities($referer_row['sort_referer_roi'] . '%', ENT_QUOTES, 'UTF-8');
error_reporting(6135);
?>
<tr>
<td class="m-row2 m-row2-fade" ><?php
echo $html['referer'];
?>
</td>
<td class="m-row1"><?php
echo $html['sort_referer_clicks'];
?>
</td>
<td class="m-row1"><?php
<td><?php
echo $model['model_name'];
?>
</td>
<td><?php
echo $car['engine_size'];
?>
L <?php
echo $car['fuel_type'];
?>
<?php
echo $car['transmission'];
?>
</td>
<td><?php
echo dollar_format($offer['preferred_price']);
?>
<br/><br/><br/><br/><br/><a href="car-view.php?id=<?php
echo $offer['offer_id'];
?>
" class="btn btn-primary" role="button">View</a></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
<?php
} else {
?>
$site_url_sql = "SELECT * FROM 202_site_urls LEFT JOIN 202_site_domains USING (site_domain_id) \n\t\t\t\t\t\t WHERE 202_site_urls.site_url_id = '" . $mysql['click_redirect_site_url_id'] . "'\n\t\t\t\t\t\t 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');
$mysql['user_id'] = mysql_real_escape_string($_SESSION['user_id']);
$user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM 202_users_pref WHERE user_id=" . $mysql['user_id'];
$user_result = _mysql_query($user_sql, $dbGlobalLink);
//($user_sql);
$user_row = mysql_fetch_assoc($user_result);
$breakdown = $user_row['user_pref_breakdown'];
if ($user_row['user_cpc_or_cpv'] == 'cpv') {
$cpv = true;
} else {
$cpv = false;
}
//keywords already set in the table, just just download them
if (empty($mysql['order'])) {
$mysql['order'] = ' ORDER BY sort_landing_page_clicks DESC';
}
$db_table = '202_sort_landing_pages';
$query = query('SELECT * FROM 202_sort_landing_pages LEFT JOIN 202_landing_pages USING (landing_page_id)', $db_table, false, false, false, $mysql['order'], false, false, true);
$keyword_sql = $query['click_sql'];
$keyword_result = mysql_query($keyword_sql) or record_mysql_error($keyword_sql);
header("Content-type: application/octet-stream");
# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=T202_landing_pages_" . time() . ".xls");
header("Pragma: no-cache");
header("Expires: 0");
echo "Landing Page" . "\t" . "Clicks" . "\t" . "Click Throughs" . "\t" . "CTR" . "\t" . "Leads" . "\t" . "S/U" . "\t" . "Payout" . "\t" . "EPC" . "\t" . "Avg CPC" . "\t" . "Income" . "\t" . "Cost" . "\t" . "Net" . "\t" . "ROI" . "\n";
while ($keyword_row = mysql_fetch_array($keyword_result, MYSQL_ASSOC)) {
if (!$keyword_row['landing_page_nickname']) {
$keyword_row['landing_page_nickname'] = '[direct link]';
}
echo $keyword_row['landing_page_nickname'] . "\t" . $keyword_row['sort_landing_page_clicks'] . "\t" . $keyword_row['sort_landing_page_click_throughs'] . '%' . "\t" . $keyword_row['sort_landing_page_ctr'] . "\t" . $keyword_row['sort_landing_page_leads'] . "\t" . $keyword_row['sort_landing_page_su_ratio'] . '%' . "\t" . dollar_format($keyword_row['sort_landing_page_payout']) . "\t" . dollar_format($keyword_row['sort_landing_page_epc']) . "\t" . dollar_format($keyword_row['sort_landing_page_avg_cpc'], $cpv) . "\t" . dollar_format($keyword_row['sort_landing_page_income']) . "\t" . dollar_format($keyword_row['sort_landing_page_cost'], $cpv) . "\t" . dollar_format($keyword_row['sort_landing_page_net'], $cpv) . "\t" . $keyword_row['sort_landing_page_roi'] . '%' . "\n";
}
<?php
}
}
error_reporting(0);
$html['total_clicks'] = htmlentities(number_format($total_clicks), ENT_QUOTES, 'UTF-8');
$html['total_click_throughs'] = htmlentities(number_format($total_click_throughs), ENT_QUOTES, 'UTF-8');
$html['total_ctr'] = htmlentities($total_ctr_ratio . '%', ENT_QUOTES, 'UTF-8');
$html['total_leads'] = htmlentities($total_leads, ENT_QUOTES, 'UTF-8');
$html['total_su_ratio'] = htmlentities($total_su_ratio . '%', ENT_QUOTES, 'UTF-8');
$html['total_payout'] = htmlentities(dollar_format($total_payout / $total_rows), ENT_QUOTES, 'UTF-8');
$html['total_epc'] = htmlentities(dollar_format($total_epc), ENT_QUOTES, 'UTF-8');
$html['total_cpc'] = htmlentities(dollar_format($total_avg_cpc, $cpv), ENT_QUOTES, 'UTF-8');
$html['total_income'] = htmlentities(dollar_format($total_income, $cpv), ENT_QUOTES, 'UTF-8');
$html['total_cost'] = htmlentities(dollar_format($total_cost, $cpv), ENT_QUOTES, 'UTF-8');
$html['total_net'] = htmlentities(dollar_format($total_net, $cpv), ENT_QUOTES, 'UTF-8');
$html['total_roi'] = htmlentities($total_roi . '%', ENT_QUOTES, 'UTF-8');
?>
</tbody>
<tfoot>
<tr style="background-color: #F8F8F8;" id="totals">
<td colspan="2" style="text-align:left; padding-left:10px"><strong>Totals for report</strong></td>
<td><strong><?php
echo $html['total_clicks'];
?>
</strong></td>
<td><strong><?php
echo $html['total_click_throughs'];
?>
</strong></td>
<td><strong><?php
//shorten landing_page
/*if (strlen($html['landing_page']) > 25) {
$html['landing_page'] = substr($html['landing_page'],0,25) . '...';
}*/
}
$html['sort_landing_page_clicks'] = htmlentities($landing_page_row['sort_landing_page_clicks'], ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_click_throughs'] = htmlentities($landing_page_row['sort_landing_page_click_throughs'], ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_ctr'] = htmlentities($landing_page_row['sort_landing_page_ctr'] . '%', ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_leads'] = htmlentities($landing_page_row['sort_landing_page_leads'], ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_su_ratio'] = htmlentities($landing_page_row['sort_landing_page_su_ratio'] . '%', ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_payout'] = htmlentities(dollar_format($landing_page_row['sort_landing_page_payout']), ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_epc'] = htmlentities(dollar_format($landing_page_row['sort_landing_page_epc']), ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_avg_cpc'] = htmlentities(dollar_format($landing_page_row['sort_landing_page_avg_cpc'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_income'] = htmlentities(dollar_format($landing_page_row['sort_landing_page_income']), ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_cost'] = htmlentities(dollar_format($landing_page_row['sort_landing_page_cost'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_net'] = htmlentities(dollar_format($landing_page_row['sort_landing_page_net'], $cpv), ENT_QUOTES, 'UTF-8');
$html['sort_landing_page_roi'] = htmlentities($landing_page_row['sort_landing_page_roi'] . '%', ENT_QUOTES, 'UTF-8');
?>
<tr>
<td class="m-row2 m-row2-fade" ><?php
echo $html['landing_page_nickname'];
?>
</td>
<td class="m-row1"><?php
echo $html['sort_landing_page_clicks'];
?>
</td>
<td class="m-row1"><?php
echo $html['sort_landing_page_click_throughs'];
?>
