function runHourly($user_pref)
{
$database = DB::getInstance();
$db = $database->getConnection();
//grab time
$time = grab_timeframe();
//get breakdown pref
$mysql['user_id'] = $db->real_escape_string($_SESSION['user_id']);
$user_sql = "\n\t\t\tSELECT\n\t\t\t\tuser_time_register,\n\t\t\t\tuser_pref_breakdown,\n\t\t\t\tuser_pref_chart,\n\t\t\t\tuser_pref_show\n\t\t\tFROM\n\t\t\t\t202_users AS 2u\n\t\t\t\tLEFT JOIN 202_users_pref AS 2up ON (2up.user_id = 2u.user_id)\n\t\t\tWHERE\n\t\t\t\t2u.user_id='" . $mysql['user_id'] . "'\n\t\t";
$user_result = _mysqli_query($user_sql);
//($user_sql);
$user_row = $user_result->fetch_assoc();
if ($user_row['user_pref_show'] == 'all') {
$click_flitered = '';
}
if ($user_row['user_pref_show'] == 'real') {
$click_filtered = " AND click_filtered='0' ";
}
if ($user_row['user_pref_show'] == 'filtered') {
$click_filtered = " AND click_filtered='1' ";
}
if ($user_row['user_pref_show'] == 'filtered_bot') {
$click_filtered = " AND click_bot='1' ";
}
if ($user_row['user_pref_show'] == 'leads') {
$click_filtered = " AND click_lead='1' ";
}
//breakdown should be hour, day, month, or year.
$pref_chart = $user_row['user_pref_chart'];
//first delete old report
$breakdown_sql = "\n\t\t\tDELETE\n\t\t\tFROM 202_sort_breakdowns\n\t\t\tWHERE user_id='" . $mysql['user_id'] . "'\n\t\t";
$breakdown_result = _mysqli_query($breakdown_sql);
//($breakdown_sql);
//find where to start from.
$start = $time['from'];
$end = $time['to'];
//make sure the start isn't past this users registration time, and likewise, make sure END isn't past today, else theses will try to grab reports for dates that do not exists slowing down mysql doing reports for nothing.
if ($user_row['user_time_register'] > $start) {
$start = $user_row['user_time_register'];
}
if (time() < $end) {
$end = time();
}
$x = 0;
while ($end > $start) {
//each hour
$from = mktime(date('G', $end), 0, 0, date('m', $end), date('d', $end), date('y', $end));
$to = mktime(date('G', $end), 59, 59, date('m', $end), date('d', $end), date('y', $end));
$end = $end - 3600;
$end1 = $end + 3600;
$hour = date('G', $end1);
$mysql['from'] = $db->real_escape_string($from);
$mysql['to'] = $db->real_escape_string($to);
//build query
$command = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*) AS clicks,\n\t\t\t\tSUM(2cr.click_out) AS click_throughs,\n\t\t\t\tSUM(click_cpc) AS cost,\n\t\t\t\tSUM(2c.click_lead) AS leads,\n\t\t\t\tSUM(2c.click_payout*2c.click_lead) AS income\n\t\t\tFROM\n\t\t\t\t202_clicks AS 2c\n\t\t\t\tLEFT JOIN 202_clicks_record AS 2cr ON 2cr.click_id = 2c.click_id\n\t\t";
$db_table = "2c";
$pref_time = false;
if ($user_pref == true) {
$pref_adv = true;
$command = $command . "\n\t\t\t\tLEFT JOIN 202_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id)\n\t\t\t\tLEFT JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)\n\t\t\t";
} else {
$pref_adv = false;
}
$command = $command . "\n\t\t\tLEFT JOIN 202_aff_campaigns AS 2ac ON (2c.aff_campaign_id = 2ac.aff_campaign_id)\n\t\t\tLEFT JOIN 202_aff_networks AS 2an ON (2an.aff_network_id = 2ac.aff_network_id)\n\t\t";
$pref_show = false;
$pref_order = "\n\t\t\tAND (2c.click_alp = '1' OR (2ac.aff_campaign_deleted='0' AND 2an.aff_network_deleted='0'))\n\t\t\t{$click_filtered}\n\t\t\tAND 2c.click_time > " . $mysql['from'] . "\n\t\t\tAND 2c.click_time <= " . $mysql['to'] . "\n\t\t";
$offset = false;
$pref_limit = false;
$count = false;
$query = query($command, $db_table, $pref_time, $pref_adv, $pref_show, $pref_order, $offset, $pref_limit, $count);
$click_sql = $query['click_sql'];
global $memcacheWorking, $memcache;
if (!user_cache_time($mysql['user_id'])) {
$click_result = _mysqli_query($click_sql);
//($click_sql);
$click_row = $click_result->fetch_assoc();
} else {
if ($memcacheWorking) {
$getCache = $memcache->get(md5($click_sql . systemHash()));
if ($getCache) {
$click_row = unserialize($getCache);
} else {
$click_result = _mysqli_query($click_sql);
$fetchArray = $click_result->fetch_assoc();
$setCache = $memcache->set(md5($click_sql . systemHash()), serialize($fetchArray), false, user_cache_time($mysql['user_id']));
$click_row = $fetchArray;
}
} else {
$click_result = _mysqli_query($click_sql);
//($click_sql);
$click_row = $click_result->fetch_assoc();
}
}
//for AVG Payouts
$payouts_sql = "\n\t\t\tSELECT\n\t\t\t\tSUM(2c.click_payout) AS payout\n\t\t\tFROM 202_clicks AS 2c\n\t\t\tWHERE 2c.click_lead='1'\n\t\t\tAND 2c.click_time > " . $mysql['from'] . "\n\t\t\tAND 2c.click_time <= " . $mysql['to'] . "\n\t\t";
$payout_result = _mysqli_query($payouts_sql);
//($breakdown_sql);
$payout_row = $payout_result->fetch_assoc();
//get the stats
$clicks[$hour] = $click_row['clicks'] + $clicks[$hour];
$total_clicks = $total_clicks + $click_row['clicks'];
//avg cpc and cost
$cost[$hour] = $click_row['cost'] + $cost[$hour];
if ($clicks[$hour] > 0) {
$avg_cpc[$hour] = $cost[$hour] / $clicks[$hour];
}
$total_cost = $total_cost + $click_row['cost'];
$total_avg_cpc = @round($total_cost / $total_clicks, 5);
//clicks out
$click_throughs[$hour] = $click_row['click_throughs'] + $click_throughs[$hour];
$total_click_throughs = $total_click_throughs + $click_row['click_throughs'];
//CTR
$ctr[$hour] = @round($click_throughs[$hour] / $clicks[$hour] * 100, 2);
$total_ctr = @round($total_click_throughs / $total_clicks * 100, 2);
//leads
$leads[$hour] = $click_row['leads'] + $leads[$hour];
$total_leads = $total_leads + $click_row['leads'];
//signup ratio
$su_ratio[$hour] = @round($leads[$hour] / $clicks[$hour] * 100, 2);
$total_su_ratio = @round($total_leads / $total_clicks * 100, 2);
//current payout
if ($click_row['leads'] > 0) {
$payout[$hour] = $payout_row['payout'] / $leads[$hour];
}
//income
$income[$hour] = $click_row['income'] + $income[$hour];
$total_income = $total_income + $click_row['income'];
//grab the EPC
$epc = @round($income[$hour] / $clicks[$hour], 2);
$total_epc = @round($total_income / $total_clicks, 2);
//net income
$net[$hour] = $income[$hour] - $cost[$hour];
$total_net = $total_income - $total_cost;
//roi
$roi[$hour] = @round($net[$hour] / $cost[$hour] * 100);
$total_roi = @round($total_net / $total_cost);
}
for ($hour = 0; $hour < 24; $hour++) {
//html escape vars
$from = $hour;
$to = $hour + 1;
if ($to == 24) {
$to = 0;
}
$mysql['from'] = $db->real_escape_string($from);
$mysql['to'] = $db->real_escape_string($to);
$mysql['clicks'] = $db->real_escape_string($clicks[$hour]);
$mysql['click_throughs'] = $db->real_escape_string($click_throughs[$hour]);
$mysql['ctr'] = $db->real_escape_string($ctr[$hour]);
$mysql['leads'] = $db->real_escape_string($leads[$hour]);
$mysql['su_ratio'] = $db->real_escape_string($su_ratio[$hour]);
$mysql['payout'] = $db->real_escape_string($payout[$hour]);
$mysql['epc'] = $db->real_escape_string($epc[$hour]);
$mysql['avg_cpc'] = $db->real_escape_string($avg_cpc[$hour]);
$mysql['income'] = $db->real_escape_string($income[$hour]);
$mysql['cost'] = $db->real_escape_string($cost[$hour]);
$mysql['net'] = $db->real_escape_string($net[$hour]);
$mysql['roi'] = $db->real_escape_string($roi[$hour]);
//insert chart
$sort_breakdown_sql = "\n\t\t\tINSERT INTO 202_sort_breakdowns\n\t\t\tSET\n\t\t\t\tsort_breakdown_from='" . $mysql['from'] . "',\n\t\t\t\tsort_breakdown_to='" . $mysql['to'] . "',\n\t\t\t\tuser_id='" . $mysql['user_id'] . "',\n\t\t\t\tsort_breakdown_clicks='" . $mysql['clicks'] . "',\n\t\t\t\tsort_breakdown_click_throughs='" . $mysql['click_throughs'] . "',\n\t\t\t\tsort_breakdown_ctr='" . $mysql['ctr'] . "',\n\t\t\t\tsort_breakdown_leads='" . $mysql['leads'] . "',\n\t\t\t\tsort_breakdown_su_ratio='" . $mysql['su_ratio'] . "',\n\t\t\t\tsort_breakdown_payout='" . $mysql['payout'] . "',\n\t\t\t\tsort_breakdown_epc='" . $mysql['epc'] . "',\n\t\t\t\tsort_breakdown_avg_cpc='" . $mysql['avg_cpc'] . "',\n\t\t\t\tsort_breakdown_income='" . $mysql['income'] . "',\n\t\t\t\tsort_breakdown_cost='" . $mysql['cost'] . "',\n\t\t\t\tsort_breakdown_net='" . $mysql['net'] . "',\n\t\t\t\tsort_breakdown_roi='" . $mysql['roi'] . "'\n\t\t";
$sort_breakdown_result = _mysqli_query($sort_breakdown_sql);
//($sort_breakdown_sql);
}
$breakdown_sql = "\n\t\tSELECT *\n\t\tFROM 202_sort_breakdowns\n\t\tWHERE user_id='" . $mysql['user_id'] . "'\n\t\tORDER BY sort_breakdown_from ASC\n\t";
$breakdown_result = _mysqli_query($breakdown_sql);
//($breakdown_sql);
$chartWidth = $_POST['chartWidth'];
$chartHeight = 180;
/* THIS IS A NET INCOME BAR GRAPH */
if ($pref_chart == 'profitloss') {
//start the PHP multi-dimensional array and create the region titles
$chart['chart_data'][0][0] = "";
$chart['chart_data'][1][0] = "Income";
$chart['chart_data'][2][0] = "Cost";
$chart['chart_data'][3][0] = "Net";
//extract the data from the query result one row at a time
for ($i = 0; $i < $breakdown_result->num_rows; $i++) {
//determine which column in the PHP array the current data belongs to
$breakdown_result->field_seek($breakdown_result->current_field);
$row = $breakdown_result->fetch_assoc();
$col = $row['sort_breakdown_from'];
$col++;
//populate the PHP array with the Year title
$hour = $row['sort_breakdown_from'];
if ($hour == 0) {
$hour = 'midnight';
}
if ($hour > 0 and $hour < 12) {
$hour = $hour . 'am';
}
if ($hour == 12) {
$hour = 'noon';
}
if ($hour > 12) {
$hour = $hour - 12 . 'pm';
}
$chart['chart_data'][0][$col] = $hour;
//populate the PHP array with the revenue data
$chart['chart_data'][1][$col] = $row['sort_breakdown_income'];
$chart['chart_data'][2][$col] = $row['sort_breakdown_cost'];
$chart['chart_data'][3][$col] = $row['sort_breakdown_net'];
}
$chart['series_color'] = array("70CF40", "CF4040", "409CCF", "000000");
$chart['series_gap'] = array('set_gap' => 40, 'bar_gap' => -35);
$chart['chart_grid_h'] = array('alpha' => 20, 'color' => "000000", 'thickness' => 1, 'type' => "dashed");
$chart['axis_value'] = array('bold' => false, 'size' => 10);
$chart['axis_category'] = array('skip' => 3, 'bold' => false, 'size' => 10);
$chart['legend_label'] = array('bold' => true, 'size' => 12);
$chart['chart_pref'] = array('line_thickness' => 1, 'point_shape' => "none", 'fill_shape' => true);
$chart['chart_rect'] = array('x' => 40, 'y' => 20, 'width' => $chartWidth - 60, 'height' => $chartHeight);
$chart['chart_transition'] = array('type' => "scale", 'delay' => 0.5, 'duration' => 0.5, 'order' => "series");
} else {
//start the PHP multi-dimensional array and create the region titles
$chart['chart_data'][0][0] = "";
if ($pref_chart == 'clicks') {
$chart['chart_data'][1][0] = "Clicks";
} elseif ($pref_chart == 'leads') {
$chart['chart_data'][1][0] = "Leads";
} elseif ($pref_chart == 'su_ratio') {
$chart['chart_data'][1][0] = "Signup Ratio";
} elseif ($pref_chart == 'payout') {
$chart['chart_data'][1][0] = "Payout";
} elseif ($pref_chart == 'epc') {
$chart['chart_data'][1][0] = "EPC";
} elseif ($pref_chart == 'cpc') {
$chart['chart_data'][1][0] = "Avg CPC";
} elseif ($pref_chart == 'income') {
$chart['chart_data'][1][0] = "Income";
} elseif ($pref_chart == 'cost') {
$chart['chart_data'][1][0] = "Cost";
} elseif ($pref_chart == 'net') {
$chart['chart_data'][1][0] = "Net";
} elseif ($pref_chart == 'roi') {
$chart['chart_data'][1][0] = "ROI";
}
//extract the data from the query result one row at a time
for ($i = 0; $i < $breakdown_result->num_rows; $i++) {
//determine which column in the PHP array the current data belongs to
$breakdown_result->field_seek($breakdown_result->current_field);
$row = $breakdown_result->fetch_assoc();
$col = $row['sort_breakdown_from'];
$col++;
//populate the PHP array with the Year title
$hour = $row['sort_breakdown_from'];
if ($hour == 0) {
$hour = 'midnight';
}
if ($hour > 0 and $hour < 12) {
$hour = $hour . 'am';
}
if ($hour == 12) {
$hour = 'noon';
}
if ($hour > 12) {
$hour = $hour - 12 . 'pm';
}
$chart['chart_data'][0][$col] = $hour;
//populate the PHP array with the revenue data
if ($pref_chart == 'clicks') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_clicks'];
} elseif ($pref_chart == 'leads') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_leads'];
} elseif ($pref_chart == 'su_ratio') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_su_ratio'];
} elseif ($pref_chart == 'payout') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_payout'];
} elseif ($pref_chart == 'epc') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_epc'];
} elseif ($pref_chart == 'cpc') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_avg_cpc'];
} elseif ($pref_chart == 'income') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_income'];
} elseif ($pref_chart == 'cost') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_cost'];
} elseif ($pref_chart == 'net') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_net'];
} elseif ($pref_chart == 'roi') {
$chart['chart_data'][1][$col] = $row['sort_breakdown_roi'];
}
}
//$chart[ 'series_color' ] = array ( "003399");
$chart['series_color'] = array("000000");
$chart['chart_type'] = "Line";
$chart['chart_transition'] = array('type' => "dissolve", 'delay' => 0.5, 'duration' => 0.5, 'order' => "series");
$chart['chart_grid_h'] = array('alpha' => 20, 'color' => "000000", 'thickness' => 1, 'type' => "dashed");
}
$chart['chart_pref'] = array('line_thickness' => 1, 'point_shape' => "circle", 'fill_shape' => false);
$chart['axis_value'] = array('bold' => false, 'size' => 10);
$chart['axis_category'] = array('bold' => false, 'size' => 10);
$chart['legend_label'] = array('bold' => true, 'size' => 12);
$chart['chart_rect'] = array('x' => 40, 'y' => 20, 'width' => $chartWidth - 60, 'height' => $chartHeight);
showChart($chart, $chartWidth - 20, $chartHeight + 40);
?>
<div style="padding: 3px 0px;"></div><?php
}
}
?>
"><?php
echo $html['roi'];
?>
</span></td>
</tr>
</tbody>
</table>
</div>
</div>
<?php
if (user_cache_time($mysql['user_id']) == 0) {
$cache_time = false;
} else {
$cache_time = user_cache_time($mysql['user_id']) / 60;
}
if ($cache_time == 1) {
$time = "minute";
} elseif ($cache_time == 60) {
$time = "hour";
} else {
$time = $cache_time . " minutes";
}
?>
<?php
if (!$cache_time) {
?>
<center style="margin-top:10px"><span style="font-size:10px">Stats are updated instant</span></center>
<?php
} else {