EVOLUTION-NINJA
Edit File: db_helper.php
<?php function eregi_match($pattern, $string) { return preg_match('/' . $pattern . '/i', $string); } function runBreakdown($user_pref) { //grab time $time = grab_timeframe(); //get breakdown pref $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = " SELECT * FROM pd_users LEFT JOIN pd_users_pref USING (user_id) WHERE pd_users.user_id='" . $mysql['user_id'] . "'"; $user_result = _mysql_query($user_sql);; //($user_sql); $user_row = mysql_fetch_assoc($user_result); $ppc_nw = ''; $ppc_acc = ''; $aff_nw = ''; $key = ''; $aff_cp = ''; $nich = ''; $ip = ''; 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'] == 'leads') { $click_filtered = " AND click_lead='1' "; } if ($user_row['user_pref_ppc_network_id']) { $ppc_nw = " AND ppv_network_id=" . $user_row['user_pref_ppc_network_id'] . " "; } if ($user_row['user_pref_ppc_account_id']) { $ppc_acc = " AND 2ac.ppv_account_id=" . $user_row['user_pref_ppc_account_id'] . " "; } if ($user_row['user_pref_aff_network_id']) { $aff_nw = " AND 2ac.aff_network_id=" . $user_row['user_pref_aff_network_id'] . " "; } if ($user_row['user_pref_aff_campaign_id']) { $aff_cp = " AND id_track=" . $user_row['user_pref_aff_campaign_id'] . " "; } if ($user_row['user_pref_niche']) { $nich = " AND niche_id=" . $user_row['user_pref_niche'] . " "; } if ($user_row['user_pref_keyword']) { $key = " AND keyword LIKE '%" . $user_row['user_pref_keyword'] . "' "; } if ($user_row['user_pref_ip']) { $ip = " AND ip_address ='" . $user_row['user_pref_ip'] . "' "; } //breakdown should be hour, day, month, or year. $breakdown = $user_row['user_pref_breakdown']; $pref_chart = $user_row['user_pref_chart']; //first delete old report $breakdown_sql = " DELETE FROM pd_sort_breakdowns WHERE user_id='" . $mysql['user_id'] . "' "; $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); //find where to start from. $start = $time['from']; $end = $time['to']; $ci = & get_instance(); $user_id = mysql_real_escape_string($ci->user_entry->get_user()); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); //print_r($r); $input_location_id = $r[4]; $new_time_stamp1 = GetTime($input_location_id); $new_time_stamp = strtotime($new_time_stamp1); if ($new_time_stamp < $end) { $end = $new_time_stamp; } $x = 0; list($start, $end) = fix_start_end_click_times($start, $end); while ($end >= $start) { #error_log("runBreakdown: $start - $end"); if ($breakdown == '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; } elseif ($breakdown == 'day') { $from = mktime(0, 0, 0, date('m', $end), date('d', $end), date('y', $end)); $to = mktime(23, 59, 59, date('m', $end), date('d', $end), date('y', $end)); $end = $end - 86400; } elseif ($breakdown == 'month') { $from = mktime(0, 0, 0, date('m', $end), 1, date('y', $end)); $to = mktime(23, 59, 59, date('m', $end), @getLastDayOfMonth(date('m', $end)), date('y', $end)); $end = $end - 2629743; } elseif ($breakdown == 'year') { $from = mktime(0, 0, 0, 1, 1, date('y', $end)); $to = mktime(23, 59, 59, @getLastDayOfMonth(date('m', $end)), 1, 12, date('y', $end)); $end = $end - 31556926; } $mysql['from'] = mysql_real_escape_string($from); $mysql['to'] = mysql_real_escape_string($to); $click_filtered = ''; //build query $command = " SELECT COUNT(*) AS clicks, AVG(2c.click_cpc) AS avg_cpc, SUM(2c.click_lead) AS leads, SUM(2c.payout*2c.click_lead) AS income FROM pd_clicks AS 2c "; $db_table = "2c"; $pref_time = false; if ($user_pref == true) { $pref_adv = true; $command = $command . " LEFT JOIN pd_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id) LEFT JOIN pd_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id) "; } else { $pref_adv = false; } $command = $command . " LEFT JOIN pd_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id) LEFT JOIN pd_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id) LEFT JOIN pd_camp_overview AS 2ac ON (2c.aff_campaign_id = 2ac.camp_overview_id) LEFT JOIN pd_keywords ON (pd_keywords.keyword_id = 2ca.keyword_id) LEFT JOIN pd_ips ON (pd_ips.ip_id = 2ca.ip_id)"; $pref_show = false; $pref_order = " AND 2c.click_time > " . $mysql['from'] . " AND 2c.click_time <= " . $mysql['to'] . $ppc_nw . $ppc_acc . $aff_nw . $nich . $key . $aff_cp . $ip . ""; $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']; $click_result = _mysql_query($click_sql); $click_row = mysql_fetch_assoc($click_result); //get the stats $clicks = 0; $clicks = $click_row['clicks']; $total_clicks = 0; $total_clicks = $total_clicks + $clicks; //avg cpc and cost $avg_cpc = 0; $avg_cpc = $click_row['avg_cpc']; $cost = 0; $cost = $clicks * $avg_cpc; $total_cost = 0; $total_cost = $total_cost + $cost; $total_avg_cpc = @round($total_cost / $total_clicks, 5); //leads $leads = 0; $leads = $click_row['leads']; $total_leads = 0; $total_leads = $total_leads + $leads; //signup ratio $su_ratio = 0; $su_ratio - 0; $su_ratio = @round($leads / $clicks * 100, 2); $total_su_ratio = @round($total_leads / $total_clicks * 100, 2); //were not using payout //current payout //$payout = 0; //$payout = $info_row['aff_campaign_payout']; //income $income = 0; $income = $click_row['income']; $total_income = 0; $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); //html escape vars $mysql['clicks'] = mysql_real_escape_string($clicks); $mysql['leads'] = mysql_real_escape_string($leads); $mysql['su_ratio'] = mysql_real_escape_string($su_ratio); $mysql['epc'] = mysql_real_escape_string($epc); $mysql['avg_cpc'] = mysql_real_escape_string($avg_cpc); $mysql['income'] = mysql_real_escape_string($income); $mysql['cost'] = mysql_real_escape_string($cost); $mysql['net'] = mysql_real_escape_string($net); $mysql['roi'] = mysql_real_escape_string($roi); $mysql['sort_breakdown_payout'] = 0; //insert chart $sort_breakdown_sql = " INSERT INTO pd_sort_breakdowns SET sort_breakdown_from='" . $mysql['from'] . "', sort_breakdown_to='" . $mysql['to'] . "', user_id='" . $mysql['user_id'] . "', sort_breakdown_clicks='" . $mysql['clicks'] . "', sort_breakdown_leads='" . $mysql['leads'] . "', sort_breakdown_su_ratio='" . $mysql['su_ratio'] . "', sort_breakdown_payout='" . $mysql['sort_breakdown_payout'] . "', sort_breakdown_epc='" . $mysql['epc'] . "', sort_breakdown_avg_cpc='" . $mysql['avg_cpc'] . "', sort_breakdown_income='" . $mysql['income'] . "', sort_breakdown_cost='" . $mysql['cost'] . "', sort_breakdown_net='" . $mysql['net'] . "', sort_breakdown_roi='" . $mysql['roi'] . "' "; $sort_breakdown_result = _mysql_query($sort_breakdown_sql); //($sort_breakdown_sql); } $breakdown_sql = " SELECT * FROM pd_sort_breakdowns WHERE user_id='" . $mysql['user_id'] . "' "; $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); $chartWidth = 800; $chartHeight = 180; //find where to start from. $start = $time['from']; $end = $time['to']; if (time() < $end) { $end = time(); } //cacluate the skip $x = 0; list($start, $end) = fix_start_end_click_times($start, $end); while ($start < $end) { #error_log("runBreakdown2: $start - $end"); if ($breakdown == 'hour') { $start = $start + 3600; } elseif ($breakdown == 'day') { $start = $start + 86400; } elseif ($breakdown == 'month') { $start = $start + 2629743; } elseif ($breakdown == 'year') { $start = $start + 31556926; } $x++; } $skip = 0; if ($breakdown == 'hour') { while ($x > 9) { $skip++; $x = $x - 9; } } else { while ($x > 14) { $skip++; $x = $x - 14; } } /* 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 < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); //populate the PHP array with the Year title $date = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $date = date_chart($breakdown, $date); $chart['chart_data'][0][$col] = $date; //populate the PHP array with the revenue data $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); $chart['chart_data'][2][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); $chart['chart_data'][3][$col] = mysql_result($breakdown_result, $i, "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' => $skip, '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' => .5, 'duration' => .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] = "Conversion Ratio"; } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][0] = "Payout"; } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][0] = "Earnings Per View"; } 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 < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); //populate the PHP array with the Year title $date = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $date = date_chart($breakdown, $date); $chart['chart_data'][0][$col] = $date; //populate the PHP array with the revenue data if ($pref_chart == 'clicks') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_clicks"); } elseif ($pref_chart == 'leads') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_leads"); } elseif ($pref_chart == 'su_ratio') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_su_ratio"); } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_payout"); } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_epc"); } elseif ($pref_chart == 'cpc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_avg_cpc"); } elseif ($pref_chart == 'income') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); } elseif ($pref_chart == 'cost') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); } elseif ($pref_chart == 'net') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_net"); } elseif ($pref_chart == 'roi') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_roi"); } } //$chart[ 'series_color' ] = array ( "003399"); $chart['series_color'] = array("000000"); $chart['chart_type'] = "Line"; //$chart[ 'chart_transition' ] = array ( 'type'=>"dissolve", 'delay'=>.5, 'duration'=>.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('skip' => $skip, '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); } function showChart($chart, $chartWidth, $chartHeight) { $reg_key = "C1XUW9CU8Y4L.NS5T4Q79KLYCK07EK"; $chart_xml = SendChartData($chart); $mysql['chart_xml'] = mysql_real_escape_string($chart_xml); $chart_sql = "INSERT INTO pd_charts SET chart_xml='" . $mysql['chart_xml'] . "'"; $chart_result = _mysql_query($chart_sql); //($chart_sql); $chart_id = mysql_insert_id(); $url['chart_id'] = urlencode($chart_id); $url = site_url('overview/showsummary'); $charturl = base_url() . "/images/charts.swf"; $chartlib = base_url() . "/charts_library"; showsummary($chart_id); /*echo InsertChart ( $charturl, $chartlib, showsummary($chart_id), $chartWidth, $chartHeight, 'FFFFFF', false, $reg_key ); */ echo show($chart_id); } function runBreakdown_lp($user_pref) { //grab time $time = grab_timeframe(); //get breakdown pref $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = " SELECT * FROM pd_users LEFT JOIN pd_users_pref USING (user_id) WHERE pd_users.user_id='" . $mysql['user_id'] . "'"; $user_result = _mysql_query($user_sql);; //($user_sql); $user_row = mysql_fetch_assoc($user_result); $ppc_nw = ''; $ppc_acc = ''; $aff_nw = ''; $key = ''; $aff_cp = ''; $nich = ''; $ip = ''; 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'] == 'leads') { $click_filtered = " AND click_lead='1' "; } if ($user_row['user_pref_ppc_network_id']) { $ppc_nw = " AND ppv_network_id=" . $user_row['user_pref_ppc_network_id'] . " "; } if ($user_row['user_pref_ppc_account_id']) { $ppc_acc = " AND 2ac.ppv_account_id=" . $user_row['user_pref_ppc_account_id'] . " "; } if ($user_row['user_pref_aff_network_id']) { $aff_nw = " AND 2ac.aff_network_id=" . $user_row['user_pref_aff_network_id'] . " "; } if ($user_row['user_pref_aff_campaign_id']) { $aff_cp = " AND id_track=" . $user_row['user_pref_aff_campaign_id'] . " "; } if ($user_row['user_pref_niche']) { $nich = " AND niche_id=" . $user_row['user_pref_niche'] . " "; } if ($user_row['user_pref_keyword']) { $key = " AND keyword LIKE '%" . $user_row['user_pref_keyword'] . "' "; } if ($user_row['user_pref_ip']) { $ip = " AND ip_address ='" . $user_row['user_pref_ip'] . "' "; } $lp = " AND landing_page_id !=0 "; //breakdown should be hour, day, month, or year. $breakdown = $user_row['user_pref_breakdown']; $pref_chart = $user_row['user_pref_chart']; //first delete old report $breakdown_sql = " DELETE FROM pd_sort_breakdowns WHERE user_id='" . $mysql['user_id'] . "' "; $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); //find where to start from. $start = $time['from']; $end = $time['to']; $ci = & get_instance(); $user_id = mysql_real_escape_string($ci->user_entry->get_user()); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); //print_r($r); $input_location_id = $r[4]; $new_time_stamp1 = GetTime($input_location_id); $new_time_stamp = strtotime($new_time_stamp1); if ($new_time_stamp < $end) { $end = $new_time_stamp; } $x = 0; list($start, $end) = fix_start_end_click_times($start, $end); while ($end >= $start) { #error_log("runBreakdown_lp: $start - $end"); if ($breakdown == '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; } elseif ($breakdown == 'day') { $from = mktime(0, 0, 0, date('m', $end), date('d', $end), date('y', $end)); $to = mktime(23, 59, 59, date('m', $end), date('d', $end), date('y', $end)); $end = $end - 86400; } elseif ($breakdown == 'month') { $from = mktime(0, 0, 0, date('m', $end), 1, date('y', $end)); $to = mktime(23, 59, 59, date('m', $end), @getLastDayOfMonth(date('m', $end)), date('y', $end)); $end = $end - 2629743; } elseif ($breakdown == 'year') { $from = mktime(0, 0, 0, 1, 1, date('y', $end)); $to = mktime(23, 59, 59, @getLastDayOfMonth(date('m', $end)), 1, 12, date('y', $end)); $end = $end - 31556926; } $mysql['from'] = mysql_real_escape_string($from); $mysql['to'] = mysql_real_escape_string($to); $click_filtered = ''; //build query $command = " SELECT COUNT(*) AS clicks, AVG(2c.click_cpc) AS avg_cpc, SUM(2c.click_lead) AS leads, SUM(2c.payout*2c.click_lead) AS income FROM pd_clicks AS 2c "; $db_table = "2c"; $pref_time = false; if ($user_pref == true) { $pref_adv = true; $command = $command . " LEFT JOIN pd_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id) LEFT JOIN pd_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id) "; } else { $pref_adv = false; } $command = $command . " LEFT JOIN pd_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id) LEFT JOIN pd_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id) LEFT JOIN pd_camp_overview AS 2ac ON (2c.aff_campaign_id = 2ac.camp_overview_id) LEFT JOIN pd_keywords ON (pd_keywords.keyword_id = 2ca.keyword_id) LEFT JOIN pd_ips ON (pd_ips.ip_id = 2ca.ip_id)"; $pref_show = false; $pref_order = " AND 2c.click_time > " . $mysql['from'] . " AND 2c.click_time <= " . $mysql['to'] . $ppc_nw . $ppc_acc . $aff_nw . $key . $aff_cp . $nich . $ip . $lp . ""; $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']; $click_result = _mysql_query($click_sql); $click_row = mysql_fetch_assoc($click_result); //get the stats $clicks = 0; $clicks = $click_row['clicks']; $total_clicks = 0; $total_clicks = $total_clicks + $clicks; //avg cpc and cost $avg_cpc = 0; $avg_cpc = $click_row['avg_cpc']; $cost = 0; $cost = $clicks * $avg_cpc; $total_cost = 0; $total_cost = $total_cost + $cost; $total_avg_cpc = @round($total_cost / $total_clicks, 5); //leads $leads = 0; $leads = $click_row['leads']; $total_leads = 0; $total_leads = $total_leads + $leads; //signup ratio $su_ratio = 0; $su_ratio - 0; $su_ratio = @round($leads / $clicks * 100, 2); $total_su_ratio = @round($total_leads / $total_clicks * 100, 2); //were not using payout //current payout //$payout = 0; //$payout = $info_row['aff_campaign_payout']; //income $income = 0; $income = $click_row['income']; $total_income = 0; $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); //html escape vars $mysql['clicks'] = mysql_real_escape_string($clicks); $mysql['leads'] = mysql_real_escape_string($leads); $mysql['su_ratio'] = mysql_real_escape_string($su_ratio); $mysql['epc'] = mysql_real_escape_string($epc); $mysql['avg_cpc'] = mysql_real_escape_string($avg_cpc); $mysql['income'] = mysql_real_escape_string($income); $mysql['cost'] = mysql_real_escape_string($cost); $mysql['net'] = mysql_real_escape_string($net); $mysql['roi'] = mysql_real_escape_string($roi); $mysql['sort_breakdown_payout'] = 0; //insert chart $sort_breakdown_sql = " INSERT INTO pd_sort_breakdowns SET sort_breakdown_from='" . $mysql['from'] . "', sort_breakdown_to='" . $mysql['to'] . "', user_id='" . $mysql['user_id'] . "', sort_breakdown_clicks='" . $mysql['clicks'] . "', sort_breakdown_leads='" . $mysql['leads'] . "', sort_breakdown_su_ratio='" . $mysql['su_ratio'] . "', sort_breakdown_payout='" . $mysql['sort_breakdown_payout'] . "', sort_breakdown_epc='" . $mysql['epc'] . "', sort_breakdown_avg_cpc='" . $mysql['avg_cpc'] . "', sort_breakdown_income='" . $mysql['income'] . "', sort_breakdown_cost='" . $mysql['cost'] . "', sort_breakdown_net='" . $mysql['net'] . "', sort_breakdown_roi='" . $mysql['roi'] . "' "; $sort_breakdown_result = _mysql_query($sort_breakdown_sql); //($sort_breakdown_sql); } $breakdown_sql = " SELECT * FROM pd_sort_breakdowns WHERE user_id='" . $mysql['user_id'] . "' "; $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); $chartWidth = 800; $chartHeight = 180; //find where to start from. $start = $time['from']; $end = $time['to']; if (time() < $end) { $end = time(); } //cacluate the skip $x = 0; list($start, $end) = fix_start_end_click_times($start, $end); while ($start < $end) { #error_log("runBreakdown_lp2: $start - $end"); if ($breakdown == 'hour') { $start = $start + 3600; } elseif ($breakdown == 'day') { $start = $start + 86400; } elseif ($breakdown == 'month') { $start = $start + 2629743; } elseif ($breakdown == 'year') { $start = $start + 31556926; } $x++; } $skip = 0; if ($breakdown == 'hour') { while ($x > 9) { $skip++; $x = $x - 9; } } else { while ($x > 14) { $skip++; $x = $x - 14; } } /* 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 < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); //populate the PHP array with the Year title $date = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $date = date_chart($breakdown, $date); $chart['chart_data'][0][$col] = $date; //populate the PHP array with the revenue data $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); $chart['chart_data'][2][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); $chart['chart_data'][3][$col] = mysql_result($breakdown_result, $i, "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' => $skip, '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' => .5, 'duration' => .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] = "Conversion Ratio"; } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][0] = "Payout"; } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][0] = "Earnings Per View"; } 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 < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); //populate the PHP array with the Year title $date = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $date = date_chart($breakdown, $date); $chart['chart_data'][0][$col] = $date; //populate the PHP array with the revenue data if ($pref_chart == 'clicks') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_clicks"); } elseif ($pref_chart == 'leads') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_leads"); } elseif ($pref_chart == 'su_ratio') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_su_ratio"); } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_payout"); } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_epc"); } elseif ($pref_chart == 'cpc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_avg_cpc"); } elseif ($pref_chart == 'income') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); } elseif ($pref_chart == 'cost') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); } elseif ($pref_chart == 'net') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_net"); } elseif ($pref_chart == 'roi') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_roi"); } } //$chart[ 'series_color' ] = array ( "003399"); $chart['series_color'] = array("000000"); $chart['chart_type'] = "Line"; //$chart[ 'chart_transition' ] = array ( 'type'=>"dissolve", 'delay'=>.5, 'duration'=>.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('skip' => $skip, '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); } function show($chart_id) { echo $a = "<script> AC_FL_RunContent( 'codebase', 'http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,45,0', 'width', '800', 'height', '220', 'scale', 'noscale', 'salign', 'TL', 'bgcolor', '#000', 'wmode', 'opaque', 'movie', 'charts', 'src', '" . base_url() . "images/charts', 'FlashVars', 'library_path=" . base_url() . "charts_library&xml_source=" . base_url() . "images/data.xml', 'id', 'my_chart', 'name', 'my_chart', 'menu', 'true', 'allowFullScreen', 'true', 'allowScriptAccess','sameDomain', 'quality', 'high', 'align', 'middle', 'pluginspage', 'http://www.macromedia.com/go/getflashplayer', 'play', 'true', 'devicefont', 'false' ); </script>"; } function InsertChart($flash_file, $library_path, $php_source, $width = 400, $height = 250, $bg_color = "666666", $transparent = false, $license = null) { $php_source = urlencode($php_source); $library_path = urlencode($library_path); $html = "<OBJECT classid='clsid:D27CDB6E-AE6D-11cf-96B8-444553540000' codebase='http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0' "; $html.= "WIDTH=" . $width . " HEIGHT=" . $height . " id='charts' ALIGN=''>"; $u = (strpos($flash_file, "?") === false) ? "?" : ((substr($flash_file, -1) === "&") ? "" : "&"); $html.= "<PARAM NAME=movie VALUE='" . $flash_file . $u . "library_path=" . $library_path . "&stage_width=" . $width . "&stage_height=" . $height . "&php_source=" . $php_source; if ($license != null) { $html.= "&license=" . $license; } $html.= "'> <PARAM NAME=quality VALUE=high> <PARAM NAME=bgcolor VALUE=#" . $bg_color . "> "; if ($transparent) { $html.= "<PARAM NAME=wmode VALUE=transparent> "; } $html.= "<EMBED src='" . $flash_file . $u . "library_path=" . $library_path . "&stage_width=" . $width . "&stage_height=" . $height . "&php_source=" . $php_source; if ($license != null) { $html.= "&license=" . $license; } $html.= "' quality=high bgcolor=#" . $bg_color . " WIDTH=" . $width . " HEIGHT=" . $height . " NAME='charts' ALIGN='' swLiveConnect='true' "; if ($transparent) { $html.= "wmode=transparent "; } $html.= "TYPE='application/x-shockwave-flash' PLUGINSPAGE='http://www.macromedia.com/go/getflashplayer'></EMBED></OBJECT>"; return $html; } function SendChartData($chart = array()) { $xml = "<chart>\r\n"; $Keys1 = array_keys((array)$chart); for ($i1 = 0;$i1 < count($Keys1);$i1++) { if (is_array($chart[$Keys1[$i1]])) { $Keys2 = array_keys($chart[$Keys1[$i1]]); if (is_array($chart[$Keys1[$i1]][$Keys2[0]])) { $xml.= "\t<" . $Keys1[$i1] . ">\r\n"; for ($i2 = 0;$i2 < count($Keys2);$i2++) { $Keys3 = array_keys((array)$chart[$Keys1[$i1]][$Keys2[$i2]]); switch ($Keys1[$i1]) { case "chart_data": $xml.= "\t\t<row>\r\n"; for ($i3 = 0;$i3 < count($Keys3);$i3++) { switch (true) { case ($chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] === null): $xml.= "\t\t\t<null/>\r\n"; break; case ($Keys2[$i2] > 0 and $Keys3[$i3] > 0): $xml.= "\t\t\t<number>" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "</number>\r\n"; break; default: $xml.= "\t\t\t<string>" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "</string>\r\n"; break; } } $xml.= "\t\t</row>\r\n"; break; case "chart_value_text": $xml.= "\t\t<row>\r\n"; $count = 0; for ($i3 = 0;$i3 < count($Keys3);$i3++) { if ($chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] === null) { $xml.= "\t\t\t<null/>\r\n"; } else { $xml.= "\t\t\t<string>" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "</string>\r\n"; } } $xml.= "\t\t</row>\r\n"; break; /*case "link_data_text": $xml.="\t\t<row>\r\n"; $count=0; for($i3=0;$i3<count($Keys3);$i3++){ if($chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]]===null){$xml.="\t\t\t<null/>\r\n";} else{$xml.="\t\t\t<string>".$chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]]."</string>\r\n";} } $xml.="\t\t</row>\r\n"; break;*/ case "draw": $text = ""; $xml.= "\t\t<" . $chart[$Keys1[$i1]][$Keys2[$i2]]['type']; for ($i3 = 0;$i3 < count($Keys3);$i3++) { if ($Keys3[$i3] != "type") { if ($Keys3[$i3] == "text") { $text = $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]]; } else { $xml.= " " . $Keys3[$i3] . "=\"" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "\""; } } } if ($text != "") { $xml.= ">" . $text . "</text>\r\n"; } else { $xml.= " />\r\n"; } break; default: //link, etc. $xml.= "\t\t<value"; for ($i3 = 0;$i3 < count($Keys3);$i3++) { $xml.= " " . $Keys3[$i3] . "=\"" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "\""; } $xml.= " />\r\n"; break; } } $xml.= "\t</" . $Keys1[$i1] . ">\r\n"; } else { if ($Keys1[$i1] == "chart_type" or $Keys1[$i1] == "series_color" or $Keys1[$i1] == "series_image" or $Keys1[$i1] == "series_explode" or $Keys1[$i1] == "axis_value_text") { $xml.= "\t<" . $Keys1[$i1] . ">\r\n"; for ($i2 = 0;$i2 < count($Keys2);$i2++) { if ($chart[$Keys1[$i1]][$Keys2[$i2]] === null) { $xml.= "\t\t<null/>\r\n"; } else { $xml.= "\t\t<value>" . $chart[$Keys1[$i1]][$Keys2[$i2]] . "</value>\r\n"; } } $xml.= "\t</" . $Keys1[$i1] . ">\r\n"; } else { //axis_category, etc. $xml.= "\t<" . $Keys1[$i1]; for ($i2 = 0;$i2 < count($Keys2);$i2++) { $xml.= " " . $Keys2[$i2] . "=\"" . $chart[$Keys1[$i1]][$Keys2[$i2]] . "\""; } $xml.= " />\r\n"; } } } else { //chart type, etc. $xml.= "\t<" . $Keys1[$i1] . ">" . $chart[$Keys1[$i1]] . "</" . $Keys1[$i1] . ">\r\n"; } } $xml.= "</chart>\r\n"; return $xml; } function grab_timeframe() { //AUTH::set_timezone($_SESSION['user_timezone']); $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_id = mysql_real_escape_string($ci->user_entry->get_user()); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); //print_r($r); $input_location_id = $r[4]; $new_time_stamp1 = GetTime($input_location_id); $new_time_stamp = strtotime($new_time_stamp1); $user_sql = "SELECT user_pref_time_predefined, user_pref_time_from, user_pref_time_to FROM pd_users_pref WHERE user_id='" . $mysql['user_id'] . "'"; $user_result = _mysql_query($user_sql);; //($user_sql); $user_row = mysql_fetch_assoc($user_result); if (($user_row['user_pref_time_predefined'] == 'today') or ($user_row['user_pref_time_from'] != '')) { $time['from'] = mktime(0, 0, 0, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); } if ($user_row['user_pref_time_predefined'] == 'yesterday') { $time['from'] = mktime(0, 0, 0, date('m', $new_time_stamp - 86400), date('d', $new_time_stamp - 86400), date('Y', $new_time_stamp - 86400)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp - 86400), date('d', $new_time_stamp - 86400), date('Y', $new_time_stamp - 86400)); } if ($user_row['user_pref_time_predefined'] == 'customdate') { $time['from'] = mktime(0, 0, 0, date('m', $user_row['user_pref_time_from']), date('d', $user_row['user_pref_time_from']), date('Y', $user_row['user_pref_time_from'])); $time['to'] = mktime(23, 59, 59, date('m', $user_row['user_pref_time_to']), date('d', $user_row['user_pref_time_to']), date('Y', $user_row['user_pref_time_to'])); } if ($user_row['user_pref_time_predefined'] == 'last7') { $time['from'] = mktime(0, 0, 0, date('m', $new_time_stamp - 86400 * 7), date('d', $new_time_stamp - 86400 * 7), date('Y', $new_time_stamp - 86400 * 7)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); } if ($user_row['user_pref_time_predefined'] == 'last14') { $time['from'] = mktime(0, 0, 0, date('m', $new_time_stamp - 86400 * 14), date('d', $new_time_stamp - 86400 * 14), date('Y', $new_time_stamp - 86400 * 14)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); } if ($user_row['user_pref_time_predefined'] == 'last30') { $time['from'] = mktime(0, 0, 0, date('m', $new_time_stamp - 86400 * 30), date('d', $new_time_stamp - 86400 * 30), date('Y', $new_time_stamp - 86400 * 30)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); } if ($user_row['user_pref_time_predefined'] == 'thismonth') { $time['from'] = mktime(0, 0, 0, date('m', $new_time_stamp), 1, date('Y', $new_time_stamp)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); } if ($user_row['user_pref_time_predefined'] == 'lastmonth') { $time['from'] = mktime(0, 0, 0, date('m', $new_time_stamp - 2629743), 1, date('Y', $new_time_stamp - 2629743)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp - 2629743), getLastDayOfMonth(date('m', $new_time_stamp - 2629743), date('Y', $new_time_stamp - 2629743)), date('Y', $new_time_stamp - 2629743)); } if ($user_row['user_pref_time_predefined'] == 'thisyear') { $time['from'] = mktime(0, 0, 0, 1, 1, date('Y', $new_time_stamp)); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); } if ($user_row['user_pref_time_predefined'] == 'lastyear') { $time['from'] = mktime(0, 0, 0, 1, 1, date('Y', $new_time_stamp - 31556926)); $time['to'] = mktime(0, 0, 0, 12, getLastDayOfMonth(date('m', $new_time_stamp - 31556926), date('Y', $new_time_stamp - 31556926)), date('Y', $new_time_stamp - 31556926)); } if ($user_row['user_pref_time_predefined'] == 'alltime') { //for the time from, do something special select the exact date this user was registered and use that :) $mysql['user_id'] = mysql_real_escape_string($_SESSION['user_id']); $user2_sql = "SELECT user_time_register FROM pd_users WHERE user_id='" . $mysql['user_id'] . "'"; $user2_result = mysql_query($user2_sql) or record_mysql_error($user2_sql); $user2_row = mysql_fetch_assoc($user2_result); $time['from'] = $user2_row['user_time_register']; $time['from'] = mktime(0, 0, 0, date('m', $time['from']), date('d', $time['from']), date('Y', $time['from'])); $time['to'] = mktime(23, 59, 59, date('m', $new_time_stamp), date('d', $new_time_stamp), date('Y', $new_time_stamp)); } if ($user_row['user_pref_time_predefined'] == '') { $time['from'] = $user_row['user_pref_time_from']; $time['to'] = $user_row['user_pref_time_to']; } $time['user_pref_time_predefined'] = $user_row['user_pref_time_predefined']; return $time; } function getLastDayOfMonth($month, $year) { return date("d", mktime(0, 0, 0, $month + 1, 0, $year)); } function _mysql_query($sql) { $result = mysql_query($sql) or die(mysql_error() . '<br/><br/>' . $sql); return $result; } function query($command, $db_table, $pref_time, $pref_adv, $pref_show, $pref_order, $offset, $pref_limit, $count) { //grab user preferences $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = "SELECT * FROM pd_users_pref WHERE user_id='" . $mysql['user_id'] . "'"; $user_result = _mysql_query($user_sql);; //($user_sql); $user_row = mysql_fetch_assoc($user_result); $click_sql = $command . " WHERE $db_table.user_id='" . $mysql['user_id'] . "' "; //set show preferences if ($pref_show == true) { if ($user_row['user_pref_show'] == 'filtered') { $click_sql.= " AND click_filtered='1' "; } elseif ($user_row['user_pref_show'] == 'real') { $click_sql.= " AND click_filtered='0' "; } elseif ($user_row['user_pref_show'] == 'leads') { $click_sql.= " AND click_filtered='0' AND click_lead='1' "; } } //set advanced preferences if ($pref_adv == true) { if ($user_row['user_pref_ppc_network_id'] and !($user_row['user_pref_ppc_account_id'])) { $mysql['user_pref_ppc_network_id'] = mysql_real_escape_string($user_row['user_pref_ppc_network_id']); $ppc_account_sql = "SELECT ppv_account_id FROM pd_ppv_accounts WHERE ppv_network_id='" . $mysql['user_pref_ppc_network_id'] . "' AND ppv_account_deleted=0"; $ppc_account_result = _mysql_query($ppc_account_sql); //($ppc_account_sql); $ppc_account_count = mysql_num_rows($ppc_account_result); if ($ppc_account_count > 0) { $click_sql.= " AND ( "; $counter = 0; while ($ppc_account_row = mysql_fetch_array($ppc_account_result, MYSQL_ASSOC)) { $counter++; $mysql['ppv_account_id'] = mysql_real_escape_string($ppc_account_row['ppv_account_id']); $click_sql.= " $db_table.ppv_account_id='" . $mysql['ppv_account_id'] . "'"; if ($counter < $ppc_account_count) { $click_sql.= " OR "; } } $click_sql.= " ) "; } } if ($user_row['user_pref_ppc_account_id']) { $mysql['user_pref_ppc_account_id'] = mysql_real_escape_string($user_row['user_pref_ppc_account_id']); $click_sql.= " AND $db_table.ppv_account_id='" . $mysql['user_pref_ppc_account_id'] . "'"; } if ($user_row['user_pref_aff_network_id'] and !$user_row['user_pref_aff_campaign_id']) { $mysql['user_pref_aff_network_id'] = mysql_real_escape_string($user_row['user_pref_aff_network_id']); $aff_campaign_sql = " SELECT camp_overview_id FROM pd_camp_overview WHERE aff_network_id='" . $mysql['user_pref_aff_network_id'] . "' "; $aff_campaign_result = _mysql_query($aff_campaign_sql); //($aff_campaign_sql); $aff_campaign_count = mysql_num_rows($aff_campaign_result); if ($aff_campaign_count > 0) { $click_sql.= " AND ( "; $counter = 0; while ($aff_campaign_row = mysql_fetch_array($aff_campaign_result, MYSQL_ASSOC)) { $counter++; $mysql['camp_overview_id'] = mysql_real_escape_string($aff_campaign_row['camp_overview_id']); $click_sql.= " $db_table.aff_campaign_id='" . $mysql['camp_overview_id'] . "'"; if ($counter < $aff_campaign_count) { $click_sql.= " OR "; } } $click_sql.= " ) "; } } if ($user_row['user_pref_aff_campaign_id']) { $mysql['user_pref_aff_campaign_id'] = mysql_real_escape_string($user_row['user_pref_aff_campaign_id']); $click_sql.= " AND $db_table.trackers_id='" . $mysql['user_pref_aff_campaign_id'] . "'"; } /* if ($user_row['user_pref_niche']) { $mysql['user_pref_niche'] = mysql_real_escape_string($user_row['user_pref_niche']); $click_sql .= " AND pd_camp_overview.niche_id='".$mysql['user_pref_niche']."'"; } */ if ($user_row['user_pref_text_ad_id']) { $mysql['user_pref_text_ad_id'] = mysql_real_escape_string($user_row['user_pref_text_ad_id']); $click_sql.= " AND text_ad_id='" . $mysql['user_pref_text_ad_id'] . "'"; } if ($user_row['user_pref_method_of_promotion'] != '0') { if ($user_row['user_pref_method_of_promotion'] == 'directlink') { $click_sql.= " AND $db_table.landing_page_id=''"; } elseif ($user_row['user_pref_method_of_promotion'] == 'landingpage') { $click_sql.= " AND $db_table.landing_page_id!=''"; } } if ($user_row['user_pref_landing_page_id']) { $mysql['user_landing_page_id'] = mysql_real_escape_string($user_row['user_pref_landing_page_id']); $click_sql.= " AND $db_table.landing_page_id='" . $mysql['user_landing_page_id'] . "'"; } /*if ($user_row['pref_country_id']) { $mysql['user_pref_country_id'] = mysql_real_escape_string($user_row['pref_country_id']); $click_sql .= " AND pref_country_id=".$mysql['user_pref_country_id']; } */ if ($user_row['user_pref_referer']) { $mysql['user_pref_referer'] = mysql_real_escape_string($user_row['user_pref_referer']); $site_url_sql = " SELECT site_url_id FROM pd_site_domains LEFT JOIN pd_site_urls USING (site_domain_id) WHERE site_domain_host LIKE CONVERT( _utf8 '" . $mysql['user_pref_referer'] . "%' USING latin1 ) COLLATE latin1_swedish_ci "; $site_url_result = _mysql_query($site_url_sql); //($site_url_sql); $site_url_count = mysql_num_rows($site_url_result); if ($site_url_count > 0) { $click_sql.= " AND ( "; $counter = 0; while ($site_url_row = mysql_fetch_array($site_url_result, MYSQL_ASSOC)) { $counter++; $mysql['site_url_id'] = mysql_real_escape_string($site_url_row['site_url_id']); $click_sql.= " click_referer_site_url_id='" . $mysql['site_url_id'] . "'"; if ($counter < $site_url_count) { $click_sql.= " OR "; } } $click_sql.= " ) "; } else { $click_sql.= " AND keyword_id = NULL"; } } if ($user_row['user_pref_keyword']) { $mysql['user_pref_keyword'] = mysql_real_escape_string($user_row['user_pref_keyword']); $keyword_sql = " SELECT keyword_id FROM pd_keywords WHERE keyword LIKE CONVERT( _utf8 '%" . $mysql['user_pref_keyword'] . "%' USING latin1 ) COLLATE latin1_swedish_ci "; $keyword_result = _mysql_query($keyword_sql); //($keyword_sql); $keyword_count = mysql_num_rows($keyword_result); if ($keyword_count > 0) { $click_sql.= " AND ( "; $counter = 0; while ($keyword_row = mysql_fetch_array($keyword_result, MYSQL_ASSOC)) { $counter++; $mysql['keyword_id'] = mysql_real_escape_string($keyword_row['keyword_id']); $click_sql.= " pd_clicks_advance.keyword_id='" . $mysql['keyword_id'] . "'"; if ($counter < $keyword_count) { $click_sql.= " OR "; } } $click_sql.= " ) "; } else { $click_sql.= " AND pd_clicks_advance.keyword_id = NULL"; } } if ($user_row['user_pref_ip']) { $mysql['user_pref_ip'] = mysql_real_escape_string($user_row['user_pref_ip']); $ip_sql = " SELECT ip_id FROM pd_ips WHERE ip_address LIKE CONVERT( _utf8 '" . $mysql['user_pref_ip'] . "%' USING latin1 ) COLLATE latin1_swedish_ci "; $ip_result = _mysql_query($ip_sql); //($ip_sql); $ip_count = mysql_num_rows($ip_result); if ($ip_count > 0) { $click_sql.= " AND ( "; $counter = 0; while ($ip_row = mysql_fetch_array($ip_result, MYSQL_ASSOC)) { $counter++; $mysql['ip_id'] = mysql_real_escape_string($ip_row['ip_id']); $click_sql.= " pd_clicks_advance.ip_id='" . $mysql['ip_id'] . "'"; if ($counter < $ip_count) { $click_sql.= " OR "; } } $click_sql.= " ) "; } else { $click_sql.= " AND ip_id = NULL"; } } } //set time preferences if ($pref_time == true) { $time = grab_timeframe(); $mysql['from'] = mysql_real_escape_string($time['from']); $mysql['to'] = mysql_real_escape_string($time['to']); if ($mysql['from'] != '') { $click_sql.= " AND click_time > " . $mysql['from'] . " "; } if ($mysql['to'] != '') { $click_sql.= " AND click_time < " . $mysql['to'] . " "; } } //set limit preferences if ($pref_order == true) { $click_sql.= $pref_order; } //only if we want to count stuff like the click history clciks do we need to do any of the stuff below. if ($count == true) { //before it limits, we want to know the TOTAL number of rows $click_result = _mysql_query($click_sql); //($click_sql); $rows = mysql_num_rows($click_result); //only if there is a limit set, run this code if ($pref_limit != false) { //rows is the total count of rows in this query. $query['rows'] = $rows; $query['offset'] = $offset; if ((is_numeric($offset) and ($pref_limit == true)) or ($pref_limit == true)) { $click_sql.= " LIMIT "; } if (is_numeric($offset) and ($pref_limit == true)) { $mysql['offset'] = mysql_real_escape_string($offset * $user_row['user_pref_limit']); $click_sql.= $mysql['offset'] . ","; //declare starting row number $query['from'] = ($query['offset'] * $user_row['user_pref_limit']) + 1; } else { $query['from'] = 1; } if ($pref_limit == true) { if (is_numeric($pref_limit)) { $mysql['user_pref_limit'] = mysql_real_escape_string($pref_limit); } else { $mysql['user_pref_limit'] = mysql_real_escape_string($user_row['user_pref_limit']); } $click_sql.= $mysql['user_pref_limit']; //declare the number of pages $query['pages'] = @ceil($query['rows'] / $user_row['user_pref_limit']) + 1; //declare end starting row number $query['to'] = ($query['from'] + $user_row['user_pref_limit']) - 1; if ($query['to'] > $query['rows']) { $query['to'] = $query['rows']; } } else { $query['pages'] = 1; $query['to'] = $query['rows']; } if (($query['from'] == 1) and ($query['to'] == 0)) { $query['from'] = 0; } } } $query['click_sql'] = $click_sql; //echo $click_sql . '<br/><br/>'; return $query; } function date_chart($breakdown, $date) { if ($breakdown == 'hour') { $date = date('m/d/y g:ia', $date); } elseif ($breakdown == 'day') { $date = date('M jS', $date); } elseif ($breakdown == 'month') { $date = date('M Y', $date); } elseif ($breakdown == 'year') { $date = date('Y', $date); } return $date; } function showsummary($id) { $mysql['chart_id'] = mysql_real_escape_string($id); $chart_sql = "SELECT chart_xml FROM pd_charts WHERE chart_id='" . $mysql['chart_id'] . "'"; $chart_result = mysql_query($chart_sql) or record_mysql_error($chart_sql); $chart_row = mysql_fetch_assoc($chart_result); //return $chart_row['chart_xml']; if (!write_file('./images/data.xml', $chart_row['chart_xml'])) { die("Unable to write to chart image file. <b>chmod 777 " . FCPATH . "images/</b> and try again."); } else { return; } } function set_user_prefs() { if (isset($_POST['user_pref_adv'])) { if ($_SERVER['REQUEST_METHOD'] == 'POST') { //start - update user user_preferences $mysql1['user_id'] = mysql_real_escape_string($_SESSION['user_id']); if (isset($_POST['user_pref_adv'])) $mysql1['user_pref_adv'] = mysql_real_escape_string($_POST['user_pref_adv']); if (isset($_POST['ppv_network_id'])) $mysql1['user_pref_ppc_network_id'] = mysql_real_escape_string($_POST['ppv_network_id']); if (isset($_POST['ppv_account_id'])) $mysql1['user_pref_ppc_account_id'] = mysql_real_escape_string($_POST['ppv_account_id']); if (isset($_POST['aff_network_id'])) $mysql1['user_pref_aff_network_id'] = mysql_real_escape_string($_POST['aff_network_id']); if (isset($_POST['aff_campaign_id'])) $mysql1['user_pref_aff_campaign_id'] = mysql_real_escape_string($_POST['aff_campaign_id']); if (isset($_POST['niche_id'])) $mysql1['user_pref_niche'] = mysql_real_escape_string($_POST['niche_id']); $mysql1['user_pref_text_ad_id'] = ''; $mysql1['user_pref_method_of_promotion'] = ''; $mysql1['user_pref_landing_page_id'] = ''; $mysql1['user_pref_country_id'] = ''; if (isset($_POST['ip'])) $mysql1['user_pref_ip'] = mysql_real_escape_string($_POST['ip']); $mysql1['user_pref_referer'] = ''; if (isset($_POST['keyword'])) $mysql1['user_pref_keyword'] = mysql_real_escape_string($_POST['keyword']); if (isset($_POST['user_pref_limit'])) $mysql1['user_pref_limit'] = mysql_real_escape_string($_POST['user_pref_limit']); if (isset($_POST['user_pref_breakdown'])) $mysql1['user_pref_breakdown'] = mysql_real_escape_string($_POST['user_pref_breakdown']); if (isset($_POST['user_pref_chart'])) $mysql1['user_pref_chart'] = mysql_real_escape_string($_POST['user_pref_chart']); if (isset($_POST['user_cpc_or_cpv'])) $mysql1['user_cpc_or_cpv'] = mysql_real_escape_string($_POST['user_cpc_or_cpv']); if (isset($_POST['user_pref_show'])) $mysql1['user_pref_show'] = mysql_real_escape_string($_POST['user_pref_show']); /*if(is_array($_POST['details'])) { foreach($_POST['details'] AS $key=>$value) { $mysql['user_pref_group_'.($key+1)] = mysql_real_escape_string($value); } }*/ } //predefined timelimit set, set the options if ($_POST['user_pref_time_predefined'] != '') { switch ($_POST['user_pref_time_predefined']) { case 'customdate'; case 'today'; case 'yesterday'; case 'last7'; case 'last14'; case 'last30'; case 'thismonth'; case 'lastmonth'; case 'thisyear'; case 'lastyear'; case 'alltime'; $clean['user_pref_time_predefined'] = $_POST['user_pref_time_predefined']; break; } //echo $clean['user_pref_time_predefined']; if (!isset($clean['user_pref_time_predefined'])) { $error['user_pref_time_predefined'] = '<div class="error">You choose an incorrect time user_preference</div>'; } } //echo $_POST['from']; $from = explode('-', $_POST['from']); $from = explode(':', $from[1]); $from_hour = $from[0]; $from_minute = $from[1]; $from = explode('-', $_POST['from']); $from = explode('/', $from[0]); $from_month = trim($from[0]); $from_day = trim($from[1]); $from_year = trim($from[2]); $to = explode('-', $_POST['to']); $to = explode(':', $to[1]); $to_hour = $to[0]; $to_minute = $to[1]; $to = explode('-', $_POST['to']); $to = explode('/', $to[0]); $to_month = trim($to[0]); $to_day = trim($to[1]); $to_year = trim($to[2]); //if from or to, validate, and if validated, set it accordingly /*if (($from != '') and ((checkdate($from_month, $from_day, $from_year) == false) or (($from_hour < 0) or ($from_hour > 59) or (!is_numeric($from_hour)) or (($from_minute < 0) or ($from_minute > 59) or (!is_numeric($from_minute)))))) { $error['date'] = '<div class="error">Wrong date format, you must use the following military time format: <strong>mm/dd/yyyy - hh:mms</strong></div>'; } else */ { $clean['user_pref_time_from'] = mktime($from_hour, $from_minute, 0, $from_month, $from_day, $from_year); /*if (($to != '') and ((checkdate($to_month, $to_day, $to_year) == false) or (($to_hour < 0) or ($to_hour > 59) or (!is_numeric($to_hour)) or (($to_minute < 0) or ($to_minute > 59) or (!is_numeric($to_minute)))))) { $error['date'] = '<div class="error">Wrong date format, you must use the following military time format: <strong>mm/dd/yyyy - hh:mm</strong></div>'; } else*/ { $clean['user_pref_time_to'] = mktime($to_hour, $to_minute, 59, $to_month, $to_day, $to_year); } } //echo $error['date'] . $error['user_pref_time_predefined'] . $error['user_pref_limit'] . $error['user_pref_show']; //if (!$error) { $mysql1['user_pref_time_predefined'] = mysql_real_escape_string($clean['user_pref_time_predefined']); if (isset($clean['user_pref_time_from'])) $mysql1['user_pref_time_from'] = mysql_real_escape_string($clean['user_pref_time_from']); if (isset($clean['user_pref_time_to'])) $mysql1['user_pref_time_to'] = mysql_real_escape_string($clean['user_pref_time_to']); $ci = & get_instance(); $ci->load->database(); $ci->db->where('user_id', $mysql1['user_id']); $ci->db->update('pd_users_pref', $mysql1); /*$user_sql = " UPDATE `pd_users_pref` SET `user_pref_adv`='".$mysql['user_pref_adv']."', `user_pref_ppc_network_id`='".$mysql['user_pref_ppc_network_id']."', `user_pref_ppc_account_id`='".$mysql['user_pref_ppc_account_id']."', `user_pref_aff_network_id`='".$mysql['user_pref_aff_network_id']."', `user_pref_aff_campaign_id`='".$mysql['user_pref_aff_campaign_id']."', `user_pref_text_ad_id`='".$mysql['user_pref_text_ad_id']."', `user_pref_method_of_promotion`='".$mysql['user_pref_method_of_promotion']."', `user_pref_landing_page_id`='".$mysql['user_pref_landing_page_id']."', `user_pref_country_id`='".$mysql['user_pref_country_id']."', `user_pref_ip`='".$mysql['user_pref_ip']."', `user_pref_referer`='".$mysql['user_pref_referer']."', `user_pref_keyword`='".$mysql['user_pref_keyword']."', `user_pref_limit`='".$mysql['user_pref_limit']."', `user_pref_show`='".$mysql['user_pref_show']."', `user_pref_breakdown`='".$mysql['user_pref_breakdown']."', `user_pref_chart`='".$mysql['user_pref_chart']."', `user_cpc_or_cpv`='".$mysql['user_cpc_or_cpv']."', `user_pref_time_from`='".$mysql['user_pref_time_from']."', `user_pref_time_to`='".$mysql['user_pref_time_to']."', `user_pref_time_predefined`='".$mysql['user_pref_time_predefined']."', `user_id`='".$mysql['user_id']."'"; //$user_result = mysql_query($user_sql); */ } } function pcc_network_icon($ppc_network_name, $ppc_account_name) { //7search if ((preg_match("/7search/i", $ppc_network_name)) or (preg_match("/7 search/i", $ppc_network_name))) { $ppc_network_icon = '7search.ico'; } //adbrite if (preg_match("/adbrite/i", $ppc_network_name)) { $ppc_network_icon = 'adbrite.ico'; } //adTegrity if ((preg_match("/adtegrity/i", $ppc_network_name)) or (preg_match("/ad tegrity/i", $ppc_network_name))) { $ppc_network_icon = 'adtegrity.png'; } //ask if (preg_match("/ask/i", $ppc_network_name)) { $ppc_network_icon = 'ask.ico'; } //adsonar if ((preg_match("/adsonar/i", $ppc_network_name)) or (preg_match("/ad sonar/i", $ppc_network_name))) { $ppc_network_icon = 'adsonar.png'; } //bidvertiser if (preg_match("/bidvertiser/i", $ppc_network_name)) { $ppc_network_icon = 'bidvertiser.gif'; } //enhance if (preg_match("/enhance/i", $ppc_network_name)) { $ppc_network_icon = 'enhance.ico'; } //facebook if (preg_match("/facebook/i", $ppc_network_name)) { $ppc_network_icon = 'facebook.ico'; } //google if ((preg_match("/google/i", $ppc_network_name)) or (preg_match("/adwords/i", $ppc_network_name))) { $ppc_network_icon = 'google.ico'; } //kanoodle if (preg_match("/kanoodle/i", $ppc_network_name)) { $ppc_network_icon = 'kanoodle.ico'; } //looksmart if (preg_match("/looksmart/i", $ppc_network_name)) { $ppc_network_icon = 'looksmart.gif'; } //miva if (preg_match("/miva/i", $ppc_network_name)) { $ppc_network_icon = 'miva.ico'; } //msn if ((preg_match("/microsoft/i", $ppc_network_name)) or (preg_match("/MSN/i", $ppc_network_name))) { $ppc_network_icon = 'msn.ico'; } //pulse360 if ((preg_match("/pulse360/i", $ppc_network_name)) or (preg_match("/pulse 360/i", $ppc_network_name))) { $ppc_network_icon = 'pulse360.ico'; } //search123 if ((preg_match("/search123/i", $ppc_network_name)) or (preg_match("/search 123/i", $ppc_network_name))) { $ppc_network_icon = 'google.ico'; } //searchfeed if (preg_match("/searchfeed/i", $ppc_network_name)) { $ppc_network_icon = 'searchfeed.gif'; } //yahoo if ((preg_match("/yahoo/i", $ppc_network_name)) or (preg_match("/YSM/i", $ppc_network_name))) { $ppc_network_icon = 'yahoo.ico'; } //mediatraffic if ((preg_match("/mediatraffic/i", $ppc_network_name)) or (preg_match("/media traffic/i", $ppc_network_name))) { $ppc_network_icon = 'mediatraffic.png'; } //social media if ((preg_match("/socialmedia/i", $ppc_network_name)) or (preg_match("/social media/i", $ppc_network_name))) { $ppc_network_icon = 'socialmedia.ico'; } if ((preg_match("/Linksador/i", $ppc_network_name))) { $ppc_network_icon = 'Linksador.png'; } if ((preg_match("/Adoori/i", $ppc_network_name))) { $ppc_network_icon = 'Adoori.png'; } if ((preg_match("/DirectCPV/i", $ppc_network_name))) { $ppc_network_icon = 'directcpv.png'; } if ((preg_match("/LeadImpact/i", $ppc_network_name))) { $ppc_network_icon = 'LeadImpact.png'; } //zango if (preg_match("/zango/i", $ppc_network_name)) { $ppc_network_icon = 'zango.ico'; } //adon network if ((preg_match("/adonnetwork/i", $ppc_network_name)) or (preg_match("/AdOnNetwork/i", $ppc_network_name))) { $ppc_network_icon = 'adonnetwork.ico'; } //clicksor if (preg_match("/clicksor/i", $ppc_network_name)) { $ppc_network_icon = 'clicksor.ico'; } //traffic vance if ((preg_match("/trafficvance/i", $ppc_network_name)) or (preg_match("/traffic vance/i", $ppc_network_name))) { $ppc_network_icon = 'trafficvance.ico'; } //unknown if (!isset($ppc_network_icon)) { $ppc_network_icon = 'unknown.png'; } $html['ppc_network_icon'] = '<img src="' . base_url() . '/images/icons/ppc/' . $ppc_network_icon . '" width="16" height="16" alt="' . $ppc_network_name . '" title="' . $ppc_network_name . ': ' . $ppc_account_name . '"/>'; return $html['ppc_network_icon']; } function get_keyword_id($keyword) { $mysql['keyword'] = mysql_real_escape_string($keyword); $keyword_sql = "SELECT keyword_id FROM pd_keywords WHERE keyword='" . $mysql['keyword'] . "'"; $keyword_result = _mysql_query($keyword_sql); $keyword_row = mysql_fetch_assoc($keyword_result); if ($keyword_row) { //if this already exists, return the id for it $keyword_id = $keyword_row['keyword_id']; return $keyword_id; } else { //else if this ip doesn't exist, insert the row and grab the id for it $keyword_sql = "INSERT INTO pd_keywords SET keyword='" . $mysql['keyword'] . "'"; $keyword_result = _mysql_query($keyword_sql); //($keyword_sql); $keyword_id = mysql_insert_id(); return $keyword_id; } } function keyword_download() { //show real or filtered clicks $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM pd_users_pref WHERE user_id=" . $mysql['user_id']; $user_result = _mysql_query($user_sql, $dbGlobalLink = false); //($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_keyword_clicks DESC'; } $db_table = 'pd_sort_keywords_lpctr'; $query = query('SELECT * FROM pd_sort_keywords_lpctr LEFT JOIN pd_keywords USING (keyword_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=PD_keywords_" . time() . ".xls"); header("Pragma: no-cache"); header("Expires: 0"); echo "Keyword" . "\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['keyword']) { $keyword_row['keyword'] = '[no keyword]'; } echo $keyword_row['keyword'] . "\t" . $keyword_row['sort_keyword_clicks'] . "\t" . $keyword_row['sort_keyword_leads'] . "\t" . $keyword_row['sort_keyword_su_ratio'] . '%' . "\t" . dollar_format($keyword_row['sort_keyword_payout']) . "\t" . dollar_format($keyword_row['sort_keyword_epc']) . "\t" . dollar_format($keyword_row['sort_keyword_avg_cpc'], $cpv) . "\t" . dollar_format($keyword_row['sort_keyword_income']) . "\t" . dollar_format($keyword_row['sort_keyword_cost'], $cpv) . "\t" . dollar_format($keyword_row['sort_keyword_net'], $cpv) . "\t" . $keyword_row['sort_keyword_roi'] . '%' . "\n"; } } function memcache_mysql_fetch_assoc($sql, $allowCaching = 1, $minutes = 5) { global $memcacheWorking, $memcache; if ($memcacheWorking == false) { $result = _mysql_query($sql); $row = mysql_fetch_assoc($result); return $row; } else { if ($allowCaching == 0) { $result = _mysql_query($sql); $row = mysql_fetch_assoc($result); return $row; } else { // Check if its set $getCache = $memcache->get(md5($sql)); if ($getCache === false) { // cache this data $fetchArray = mysql_fetch_assoc(_mysql_query($sql)); $setCache = $memcache->set(md5($sql), serialize($fetchArray), false, 60 * $minutes); //store all this users memcache keys, so we can delete them fast later on memcache_set_user_key($sql); return $fetchArray; } else { // Data Cached return unserialize($getCache); } } } } class browser { var $Name = "Unknown"; var $Version = "Unknown"; var $Platform = "Unknown"; var $UserAgent = "Not reported"; var $AOL = false; function browser() { $agent = $_SERVER['HTTP_USER_AGENT']; // initialize properties $bd['platform'] = "Unknown"; $bd['browser'] = "Unknown"; $bd['version'] = "Unknown"; $this->UserAgent = $agent; // find operating system if (eregi_match("win", $agent)) $bd['platform'] = "Windows"; elseif (eregi_match("mac", $agent)) $bd['platform'] = "MacIntosh"; elseif (eregi_match("linux", $agent)) $bd['platform'] = "Linux"; elseif (eregi_match("OS/2", $agent)) $bd['platform'] = "OS2"; elseif (eregi_match("BeOS", $agent)) $bd['platform'] = "BeOS"; // test for Opera if (eregi_match("opera", $agent)) { $val = stristr($agent, "opera"); if (eregi_match("\/", $val)) { $val = explode("/", $val); $bd['browser'] = $val[0]; $val = explode(" ", $val[1]); $bd['version'] = $val[0]; } else { $val = explode(" ", stristr($val, "opera")); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; } // test for WebTV } elseif (eregi_match("webtv", $agent)) { $val = explode("/", stristr($agent, "webtv")); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; // test for MS Internet Explorer version 1 } elseif (eregi_match("microsoft internet explorer", $agent)) { $bd['browser'] = "MSIE"; $bd['version'] = "1.0"; $var = stristr($agent, "/"); if (ereg("308|425|426|474|0b1", $var)) { $bd['version'] = "1.5"; } // test for NetPositive } elseif (eregi_match("NetPositive", $agent)) { $val = explode("/", stristr($agent, "NetPositive")); $bd['platform'] = "BeOS"; $bd['browser'] = $val[0]; $bd['version'] = $val[1]; // test for MS Internet Explorer } elseif (eregi_match("msie", $agent) && !eregi_match("opera", $agent)) { $val = explode(" ", stristr($agent, "msie")); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; // test for MS Pocket Internet Explorer } elseif (eregi_match("mspie", $agent) || eregi_match('pocket', $agent)) { $val = explode(" ", stristr($agent, "mspie")); $bd['browser'] = "MSPIE"; $bd['platform'] = "WindowsCE"; if (eregi_match("mspie", $agent)) $bd['version'] = $val[1]; else { $val = explode("/", $agent); $bd['version'] = $val[1]; } // test for Galeon } elseif (eregi_match("galeon", $agent)) { $val = explode(" ", stristr($agent, "galeon")); $val = explode("/", $val[0]); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; // test for Konqueror } elseif (eregi_match("Konqueror", $agent)) { $val = explode(" ", stristr($agent, "Konqueror")); $val = explode("/", $val[0]); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; // test for iCab } elseif (eregi_match("icab", $agent)) { $val = explode(" ", stristr($agent, "icab")); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; // test for OmniWeb } elseif (eregi_match("omniweb", $agent)) { $val = explode("/", stristr($agent, "omniweb")); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; // test for Phoenix } elseif (eregi_match("Phoenix", $agent)) { $bd['browser'] = "Phoenix"; $val = explode("/", stristr($agent, "Phoenix/")); $bd['version'] = $val[1]; // test for Firebird } elseif (eregi_match("firebird", $agent)) { $bd['browser'] = "Firebird"; $val = stristr($agent, "Firebird"); $val = explode("/", $val); $bd['version'] = $val[1]; // test for Firefox } elseif (eregi_match("Firefox", $agent)) { $bd['browser'] = "Firefox"; $val = stristr($agent, "Firefox"); $val = explode("/", $val); $bd['version'] = $val[1]; // test for Mozilla Alpha/Beta Versions } elseif (eregi_match("mozilla", $agent) && eregi_match("rv:[0-9].[0-9][a-b]", $agent) && !eregi_match("netscape", $agent)) { $bd['browser'] = "Mozilla"; $val = explode(" ", stristr($agent, "rv:")); eregi_match("rv:[0-9].[0-9][a-b]", $agent, $val); $bd['version'] = str_replace("rv:", "", $val[0]); // test for Mozilla Stable Versions } elseif (eregi_match("mozilla", $agent) && eregi_match("rv:[0-9]\.[0-9]", $agent) && !eregi_match("netscape", $agent)) { $bd['browser'] = "Mozilla"; $val = explode(" ", stristr($agent, "rv:")); eregi_match("rv:[0-9]\.[0-9]\.[0-9]", $agent, $val); $bd['version'] = str_replace("rv:", "", $val[0]); // test for Lynx & Amaya } elseif (eregi_match("libwww", $agent)) { if (eregi_match("amaya", $agent)) { $val = explode("/", stristr($agent, "amaya")); $bd['browser'] = "Amaya"; $val = explode(" ", $val[1]); $bd['version'] = $val[0]; } else { $val = explode("/", $agent); $bd['browser'] = "Lynx"; $bd['version'] = $val[1]; } // test for Safari } elseif (eregi_match("safari", $agent)) { $bd['browser'] = "Safari"; $bd['version'] = ""; // remaining two tests are for Netscape } elseif (eregi_match("netscape", $agent)) { $val = explode(" ", stristr($agent, "netscape")); $val = explode("/", $val[0]); $bd['browser'] = $val[0]; $bd['version'] = $val[1]; } elseif (eregi_match("mozilla", $agent) && !eregi_match("rv:[0-9]\.[0-9]\.[0-9]", $agent)) { $val = explode(" ", stristr($agent, "mozilla")); $val = explode("/", $val[0]); $bd['browser'] = "Netscape"; $bd['version'] = $val[1]; } // clean up extraneous garbage that may be in the name $bd['browser'] = preg_replace("/[^a-z,A-Z]/", "", $bd['browser']); // clean up extraneous garbage that may be in the version $bd['version'] = preg_replace("/[^0-9,.,a-z,A-Z]/", "", $bd['version']); // check for AOL if (eregi_match("AOL", $agent)) { $var = stristr($agent, "AOL"); $var = explode(" ", $var); $bd['aol'] = preg_replace("/[^0-9,.,a-z,A-Z]/", "", $var[1]); } if (preg_match("/Windows/i", $bd['platform'])) { $bd['platform'] = 1; } if (preg_match("/Macintosh/i", $bd['platform'])) { $bd['platform'] = 2; } if (preg_match("/Linux/i", $bd['platform'])) { $bd['platform'] = 3; } if (preg_match("/OS2/i", $bd['platform'])) { $bd['platform'] = 4; } if (preg_match("/BeOS/i", $bd['platform'])) { $bd['platform'] = 5; } if (preg_match("/Internet Explorer/i", $bd['browser'])) { $bd['browser'] = 1; } if (preg_match("/MSIE/i", $bd['browser'])) { $bd['browser'] = 1; } if (preg_match("/Mozilla/i", $bd['browser'])) { $bd['browser'] = 2; } if (preg_match("/Firefox/i", $bd['browser'])) { $bd['browser'] = 2; } if (preg_match("/Konqueror/i", $bd['browser'])) { $bd['browser'] = 3; } if (preg_match("/Netscape/i", $bd['browser'])) { $bd['browser'] = 4; } if (preg_match("/OmniWeb/i", $bd['browser'])) { $bd['browser'] = 5; } if (preg_match("/Opera/i", $bd['browser'])) { $bd['browser'] = 6; } if (preg_match("/Safari/i", $bd['browser'])) { $bd['browser'] = 7; } if (preg_match("/AOL/i", $bd['browser'])) { $bd['browser'] = 8; } if (preg_match("/Chrome/i", $agent)) { $bd['browser'] = 9; } if (preg_match("/iphone/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/mobile/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/blackberry/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/treo/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/g1/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/android/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/pearl/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/dash/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/sidekick/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/wing/i", $agent)) { $bd['browser'] = 10; } if (preg_match("/xbox/i", $agent)) { $bd['browser'] = 11; } if (preg_match("/wii/i", $agent)) { $bd['browser'] = 11; } if (preg_match("/playstation/i", $agent)) { $bd['browser'] = 11; } // finally assign our properties $this->Browser = $bd['browser']; $this->Platform = $bd['platform']; // $this->Version = $bd['version']; // $this->AOL = $bd['aol']; } } function get_platform_and_browser_id() { $br = new Browser; $id['platform'] = $br->Platform; $id['browser'] = $br->Browser; return $id; } function get_ip_id($ip_address) { $mysql['ip_address'] = mysql_real_escape_string($ip_address); $ip_sql = "SELECT ip_id FROM pd_ips WHERE ip_address='" . $mysql['ip_address'] . "'"; $ip_result = _mysql_query($ip_sql); $ip_row = mysql_fetch_assoc($ip_result); if ($ip_row) { //if this ip already exists, return the ip_id for it. $ip_id = $ip_row['ip_id']; return $ip_id; } else { //else if this doesn't exist, insert the new iprow, and return the_id for this new row we found //but before we do this, we need to grab the location_id //$location_id = get_location_id($ip_address); //$mysql['location_id'] = mysql_real_escape_string($location_id); $ip_sql = "INSERT INTO pd_ips SET ip_address='" . $mysql['ip_address'] . "'"; $ip_result = _mysql_query($ip_sql); //($ip_sql); $ip_id = mysql_insert_id(); return $ip_id; } } function startFilter($click_id, $ip_id, $ip_address, $user_id) { //we only do the other checks, if the first ones have failed. //we will return the variable filter, if the $filter returns TRUE, when the click is inserted and recorded we will insert the new click already inserted, //what was lagign this query is before it would insert a click, then scan it and then update the click, the updating later on was lagging, now we will just insert and it will not stop the clicks from being redirected becuase of a slow update. //check the user $filter = checkUserIP($click_id, $ip_id, $user_id); if ($filter == false) { //check the netrange $filter = checkNetrange($click_id, $ip_address); if ($filter == false) { $filter = checkLastIps($user_id, $ip_id); } } if ($filter == true) { return 1; } else { return 0; } } function get_site_url_id($site_url_address) { if (empty($site_url_address)) return ''; $mysql['site_url_address'] = mysql_real_escape_string($site_url_address); $site_url_sql = "SELECT site_url_id FROM pd_site_urls WHERE site_url_address='" . $mysql['site_url_address'] . "'"; $site_url_result = _mysql_query($site_url_sql); $site_url_row = mysql_fetch_assoc($site_url_result); if ($site_url_row) { //if this site_url_address already exists, return the site_url_id for it. $site_url_id = $site_url_row['site_url_id']; return $site_url_id; } else { //else if this doesn't exist, insert the new iprow, and return the_id for this new row we found //but before we do this, we need to grab the site_domain_id $site_domain_id = get_site_domain_id($site_url_address); $mysql['site_domain_id'] = mysql_real_escape_string($site_domain_id); $site_url_sql = "INSERT INTO pd_site_urls SET site_domain_id='" . $mysql['site_domain_id'] . "', site_url_address='" . $mysql['site_url_address'] . "'"; $site_url_result = _mysql_query($site_url_sql); //($site_url_sql); $site_url_id = mysql_insert_id(); return $site_url_id; } } function get_site_domain_id($site_url_address) { if (empty($site_url_address)) return ''; $parsed_url = @parse_url($site_url_address); $site_domain_host = $parsed_url['host']; $site_domain_host = str_replace('www.', '', $site_domain_host); $mysql['site_domain_host'] = mysql_real_escape_string($site_domain_host); $site_domain_sql = "SELECT site_domain_id FROM pd_site_domains WHERE site_domain_host='" . $mysql['site_domain_host'] . "'"; $site_domain_result = _mysql_query($site_domain_sql); $site_domain_row = mysql_fetch_assoc($site_domain_result); if ($site_domain_row) { //if this site_domain_id already exists, return the site_domain_id for it. $site_domain_id = $site_domain_row['site_domain_id']; return $site_domain_id; } else { //else if this doesn't exist, insert the new iprow, and return the_id for this new row we found $site_domain_sql = "INSERT INTO pd_site_domains SET site_domain_host='" . $mysql['site_domain_host'] . "'"; $site_domain_result = _mysql_query($site_domain_sql); //($site_domain_sql); $site_domain_id = mysql_insert_id(); return $site_domain_id; } } function visitor_download() { //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=PD_visitors_" . time() . ".xls"); header("Pragma: no-cache"); header("Expires: 0"); //get stuff $command = "SELECT * FROM pd_clicks LEFT JOIN pd_clicks_advance USING (click_id) LEFT JOIN pd_clicks_site USING (click_id)"; $db_table = "pd_clicks"; $query = query($command, $db_table, true, true, true, ' ORDER BY pd_clicks.click_id DESC ', true, true, false); //run query $click_sql = $query['click_sql']; $click_result = mysql_query($click_sql) or record_mysql_error($click_sql); 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']); $clicks_tbl = "pd_clicks"; $click_sql2 = "SELECT $clicks_tbl.click_id, click_alp, aff_campaign_name, landing_page_nickname, ppv_network_name, ppv_account_name, ip_address, keyword, click_out, click_lead, click_filtered, 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,"; $click_sql2.= " pd_browsers.browser_id, pd_browsers.browser_name, pd_platforms.platform_id, pd_platforms.platform_name FROM $clicks_tbl LEFT JOIN pd_clicks_advance USING (click_id) LEFT JOIN pd_clicks_record USING (click_id) LEFT JOIN pd_clicks_site USING (click_id) LEFT JOIN pd_camp_overview ON (pd_camp_overview.camp_overview_id = $clicks_tbl.aff_campaign_id) LEFT JOIN pd_ppv_accounts ON (pd_ppv_accounts.ppv_account_id = $clicks_tbl.ppv_account_id) LEFT JOIN pd_ppv_networks ON (pd_camp_overview.ppv_network_id = pd_ppv_networks.ppv_network_id) LEFT JOIN pd_landing_pages ON (pd_landing_pages.landing_page_id = $clicks_tbl.landing_page_id) LEFT JOIN pd_ips ON (pd_ips.ip_id = pd_clicks_advance.ip_id) LEFT JOIN pd_keywords ON (pd_keywords.keyword_id = pd_clicks_advance.keyword_id) LEFT JOIN pd_browsers ON (pd_browsers.browser_id = pd_clicks_advance.browser_id) LEFT JOIN pd_platforms ON (pd_platforms.platform_id = pd_clicks_advance.platform_id)"; $click_sql2.= " WHERE $clicks_tbl.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 pd_site_urls LEFT JOIN pd_site_domains USING (site_domain_id) WHERE pd_site_urls.site_url_id = '" . $mysql['click_referer_site_url_id'] . "' AND pd_site_urls.site_domain_id = pd_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 pd_site_urls LEFT JOIN pd_site_domains USING (site_domain_id) WHERE pd_site_urls.site_url_id = '" . $mysql['click_landing_site_url_id'] . "' AND pd_site_urls.site_domain_id = pd_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 pd_site_urls LEFT JOIN pd_site_domains USING (site_domain_id) WHERE pd_site_urls.site_url_id = '" . $mysql['click_outbound_site_url_id'] . "' AND pd_site_urls.site_domain_id = pd_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'); $mysql['click_cloaking_site_url_id'] = mysql_real_escape_string($click_row['click_cloaking_site_url_id']); $site_url_sql = "SELECT * FROM pd_site_urls LEFT JOIN pd_site_domains USING (site_domain_id) WHERE pd_site_urls.site_url_id = '" . $mysql['click_cloaking_site_url_id'] . "' AND pd_site_urls.site_domain_id = pd_site_domains.site_domain_id"; $site_url_row = memcache_mysql_fetch_assoc($site_url_sql); $html['cloaking'] = htmlentities($site_url_row['site_url_address'], ENT_QUOTES, 'UTF-8'); $html['cloaking_host'] = htmlentities($site_url_row['site_domain_host'], ENT_QUOTES, 'UTF-8'); $mysql['click_redirect_site_url_id'] = mysql_real_escape_string($click_row['click_redirect_site_url_id']); $site_url_sql = "SELECT * FROM pd_site_urls LEFT JOIN pd_site_domains USING (site_domain_id) WHERE pd_site_urls.site_url_id = '" . $mysql['click_redirect_site_url_id'] . "' AND pd_site_urls.site_domain_id = pd_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['click_id'] = htmlentities($click_row['click_id'], ENT_QUOTES, 'UTF-8'); $html['click_time'] = date('m/d/y g:ia', $click_row['click_time']); $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['ppv_account_id'] = htmlentities($click_row['ppv_account_id'], ENT_QUOTES, 'UTF-8'); $html['aff_campaign_name'] = htmlentities($click_row['aff_campaign_name'], ENT_QUOTES, 'UTF-8'); $html['ppv_network_name'] = htmlentities($click_row['ppv_network_name'], ENT_QUOTES, 'UTF-8'); $html['ppv_account_name'] = htmlentities($click_row['ppv_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'); 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['ppv_network_name'] . "\t" . $click_row['ppv_account_name'] . "\t" . $click_filtered . "\t" . $click_row['ip_address'] . "\t" . $click_row['aff_campaign_name'] . "\t" . $html['referer'] . "\t" . $html['landing'] . "\t" . $html['outbound'] . "\t" . $html['cloaking'] . "\t" . $html['redirect'] . "\t" . $click_row['keyword'] . "\n"; } } function checkUserIP($click_id, $ip_id, $user_id) { $mysql['ip_id'] = mysql_real_escape_string($ip_id); $mysql['user_id'] = mysql_real_escape_string($user_id); $count_sql = "SELECT COUNT(*) FROM pd_users WHERE user_id='" . $mysql['user_id'] . "' AND user_last_login_ip_id='" . $mysql['ip_id'] . "'"; $count_result = _mysql_query($count_sql); //($count_sql); //if the click_id's ip address, is the same ip adddress of the click_id's owner's last logged in ip, filter this. This means if the ip hit on the page was the same as the owner of the click affiliate program, we want to filter out the clicks by the owner when he/she is trying to test if (mysql_result($count_result, 0, 0) > 0) { return true; } return false; } function checkNetrange($click_id, $ip_address) { $ip_address = ip2long($ip_address); //check each netrange /*google1 */ if (($ip_address >= 1208926208) and ($ip_address <= 1208942591)) { return true; } /*MSN */ if (($ip_address >= 1093926912) and ($ip_address <= 1094189055)) { return true; } /*google2 */ if (($ip_address >= 3512041472) and ($ip_address <= 3512074239)) { return true; } /*Yahoo */ if (($ip_address >= 3640418304) and ($ip_address <= 3640426495)) { return true; } /*google3 */ if (($ip_address >= 1123631104) and ($ip_address <= 1123639295)) { return true; } /*level 3 communications */ if (($ip_address >= 1094189056) and ($ip_address <= 1094451199)) { return true; } /*yahoo2 */ if (($ip_address >= 3515031552) and ($ip_address <= 3515039743)) { return true; } /*Yahoo3 */ if (($ip_address >= 3633393664) and ($ip_address <= 3633397759)) { return true; } /*Google5 */ if (($ip_address >= 1089052672) and ($ip_address <= 1089060863)) { return true; } /*Yahoo */ if (($ip_address >= 1209925632) and ($ip_address <= 1209991167)) { return true; } /*Yahoo */ if (($ip_address >= 1241907200) and ($ip_address <= 1241972735)) { return true; } /*Performance Systems International Inc. */ if (($ip_address >= 637534208) and ($ip_address <= 654311423)) { return true; } /*Microsoft */ if (($ip_address >= 3475898368) and ($ip_address <= 3475963903)) { return true; } /*googleNew */ if (($ip_address >= - 782925824) and ($ip_address <= - 782893057)) { return true; } //if it was none of theses, return false return false; } //this will filter out a click if it the IP WAS RECORDED, for a particular user within the last 24 hours, if it existed before, filter out this click. function checkLastIps($user_id, $ip_id) { $mysql['user_id'] = mysql_real_escape_string($user_id); $mysql['ip_id'] = mysql_real_escape_string($ip_id); $check_sql = "SELECT COUNT(*) AS count FROM pd_last_ips WHERE user_id='" . $mysql['user_id'] . "' AND ip_id='" . $mysql['ip_id'] . "'"; $check_result = _mysql_query($check_sql); //($check_sql); $check_row = mysql_fetch_assoc($check_result); $count = $check_row['count']; if ($count > 0) { //if this ip has been seen within the last 24 hours, filter it out. return true; } else { //else if this ip has not been recorded, record it now $ci = & get_instance(); $user_id = $ci->user_entry->get_user(); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); //print_r($r); $input_location_id = $r[4]; $new_time_stamp1 = GetTime($input_location_id); $new_time_stamp = strtotime($new_time_stamp1); $mysql['time'] = $new_time_stamp; $insert_sql = "INSERT INTO pd_last_ips SET user_id='" . $mysql['user_id'] . "', ip_id='" . $mysql['ip_id'] . "', time='" . $mysql['time'] . "'"; $insert_result = _mysql_query($insert_sql); //($insert_sql); return false; } } //function get file extension function getFileExtension($str) { $i = strrpos($str, "."); if (!$i) { return ""; } $l = strlen($str) - $i; $ext = substr($str, $i + 1, $l); return $ext; } //makes sure only regular charactesr are in the string function cleanString($string = "") { $allowedCharacters = array(" ", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "�", "!", "\"", "#", "$", "%", "&", "'", "(", ")", "*", "+", ",", "-", ".", "/", ":", ";", "<", "=", ">", "?", "@", "[", "\\", "]", "^", "_", "`", "{", "|", "}", "~", "�", "�", "�", "�", "�", "?", "�", "?", "�", "�", "�", "�", "�", "�", "?", "�", "�", "�", "�", "?", "?", "�", "�", "�", "�", "�", "?", "�", "�", "?", "?", "?", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "?", "�", "�", "�", "�", "�", "�", "?", "�", "�", "�", "�", "�", "?", "?", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "?", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "�", "?", "?", "�"); $newString.= ""; for ($incString = 0;$incString < strlen($string);$incString++) { if (in_array(strtolower($string[$incString]), $allowedCharacters)) { $newString.= $string[$incString]; } } return $newString; } //this is like SUBSTR, but will cacluate for WHOLE CHARACTERS only, IE: it won't cut a word in half function cutText($string, $length) { while ($string{$length} != " ") { $length--; } return substr($string, 0, $length); } function referer_download() { //show real or filtered clicks $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM pd_users_pref WHERE user_id=" . $mysql['user_id']; $user_result = _mysql_query($user_sql, $dbGlobalLink = false); //($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 = 'pd_sort_referers_lpctr'; $query = query('SELECT * FROM pd_sort_referers_lpctr LEFT JOIN pd_site_domains ON (pd_sort_referers_lpctr.referer_id=pd_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=PD_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"; } } function ips_download() { //show real or filtered clicks $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM pd_users_pref WHERE user_id=" . $mysql['user_id']; $user_result = _mysql_query($user_sql, $dbGlobalLink = false); //($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 = 'pd_sort_ips'; $query = query('SELECT * FROM pd_sort_ips LEFT JOIN pd_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=PD_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 runHourly($user_pref) { //echo date('l jS \of F Y h:i:s A'); //grab time $time = grab_timeframe(); //get breakdown pref $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = " SELECT * FROM pd_users LEFT JOIN pd_users_pref USING (user_id) WHERE pd_users.user_id='" . $mysql['user_id'] . "'"; $user_result = _mysql_query($user_sql);; //($user_sql); $user_row = mysql_fetch_assoc($user_result); $click_flitered = ''; 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'] == '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 $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $breakdown_sql = " DELETE FROM pd_sort_breakdowns"; //WHERE user_id='".$mysql['user_id']."' $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); //find where to start from. $start = $time['from']; $end = $time['to']; $ci = & get_instance(); $user_id = $ci->user_entry->get_user(); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); //print_r($r); $input_location_id = $r[4]; $new_time_stamp1 = GetTime($input_location_id); $new_time_stamp = strtotime($new_time_stamp1); if ($new_time_stamp < $end) { $end = $new_time_stamp; } $x = 0; list($start, $end) = fix_start_end_click_times($start, $end); while ($end >= $start) { #error_log("runHourly: $start - $end"); //each hour $hour = date('G', $end); $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; $hour1 = date('G', $end); $mysql['from'] = mysql_real_escape_string($from); $mysql['to'] = mysql_real_escape_string($to); //build query $command = " SELECT COUNT(*) AS clicks, SUM(click_cpc) AS cost, SUM(2c.click_lead) AS leads, payout, SUM(2c.payout*2c.click_lead) AS income FROM pd_clicks AS 2c "; $db_table = "2c"; $pref_time = false; if ($user_pref == true) { $pref_adv = true; $command = $command . " LEFT JOIN pd_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id) LEFT JOIN pd_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id) "; } else { $pref_adv = false; } $command = $command . " LEFT JOIN pd_camp_overview AS 2ac ON (2c.aff_campaign_id = 2ac.camp_overview_id) LEFT JOIN pd_aff_networks AS 2an ON (2an.aff_network_id = 2ac.aff_network_id) "; $pref_show = false; $ppc_nw = 0; $ppc_acc = 0; $aff_nw = 0; $key = 0; $aff_cp = 0; $ip = 0; //AND (2c.click_alp = '1' OR ( 2an.aff_network_deleted='0')) $pref_order = " AND 2c.click_time > " . $mysql['from'] . " AND 2c.click_time <= " . $mysql['to'] . " "; $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']; $click_result = _mysql_query($click_sql); //($click_sql); if (isset($clicks[$hour]) == false) $clicks[$hour] = 0; if (isset($payout[$hour]) == false) $payout[$hour] = 0; if (isset($cost[$hour]) == false) $cost[$hour] = 0; if (isset($total_clicks) == false) $total_clicks = 0; if (isset($total_payout) == false) $total_payout = 0; if (isset($total_cost) == false) $total_cost = 0; if (isset($leads[$hour]) == false) $leads[$hour] = 0; if (isset($total_leads) == false) $total_leads = 0; if (isset($income[$hour]) == false) $income[$hour] = 0; if (isset($total_income) == false) $total_income = 0; if (isset($mysql['sort_breakdown_payout']) == false) $mysql['sort_breakdown_payout'] = 0; if (isset($avg_cpc[$hour]) == false) $avg_cpc[$hour] = 0; $click_row = mysql_fetch_assoc($click_result); //print_r($click_row); //get the stats $clicks[$hour] = $click_row['clicks'] + $clicks[$hour]; $payout[$hour] = $click_row['payout'] + $payout[$hour]; $total_clicks = $total_clicks + $click_row['clicks']; $total_payout = $total_payout + $click_row['payout']; //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); //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); //were not using payout //current payout //$payout = 0; //$payout = $info_row['aff_campaign_payout']; //income $income[$hour] = $click_row['income'] + $income[$hour]; $total_income = $total_income + $click_row['income']; //grab the EPC //if(isset($epc)==false) $epc=0; $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'] = mysql_real_escape_string($from); $mysql['to'] = mysql_real_escape_string($to); if (isset($clicks[$hour]) == false) { $clicks[$hour] = 0; } $mysql['clicks'] = mysql_real_escape_string($clicks[$hour]); if (isset($payout[$hour]) == false) { $payout[$hour] = 0; } $mysql['payout'] = mysql_real_escape_string($payout[$hour]); if (isset($leads[$hour]) == false) { $leads[$hour] = 0; } $mysql['leads'] = mysql_real_escape_string($leads[$hour]); if (isset($su_ratio[$hour]) == false) { $su_ratio[$hour] = 0; } $mysql['su_ratio'] = mysql_real_escape_string($su_ratio[$hour]); // if(isset($epc[$hour])==false) {$epc[$hour]=0; } // if(isset($epc[$hour])==false) {$epc[$hour]=0; } //if(isset($epc[$hour])==false) {$epc[$hour]=0;} // if(isset($epc[$hour])==false){$epc[$hour] =0;} $epc = 0; $mysql['epc'] = mysql_real_escape_string($epc[$hour]); if (isset($avg_cpc[$hour]) == false) { $avg_cpc[$hour] = 0; } $mysql['avg_cpc'] = mysql_real_escape_string($avg_cpc[$hour]); if (isset($income[$hour]) == false) { $income[$hour] = 0; } $mysql['income'] = mysql_real_escape_string($income[$hour]); if (isset($cost[$hour]) == false) { $cost[$hour] = 0; } $mysql['cost'] = mysql_real_escape_string($cost[$hour]); if (isset($net[$hour]) == false) { $net[$hour] = 0; } $mysql['net'] = mysql_real_escape_string($net[$hour]); if (isset($roi[$hour]) == false) { $roi[$hour] = 0; } $mysql['roi'] = mysql_real_escape_string($roi[$hour]); if (isset($mysql['sort_breakdown_payout']) == false) $mysql['sort_breakdown_payout'] = 0; //insert chart $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $sort_breakdown_sql = " INSERT INTO pd_sort_breakdowns SET sort_breakdown_from='" . $mysql['from'] . "', sort_breakdown_to='" . $mysql['to'] . "', user_id='" . $mysql['user_id'] . "', sort_breakdown_clicks='" . $mysql['clicks'] . "', sort_breakdown_leads='" . $mysql['leads'] . "', sort_breakdown_su_ratio='" . $mysql['su_ratio'] . "', sort_breakdown_payout='" . $mysql['payout'] . "', sort_breakdown_epc='" . $mysql['epc'] . "', sort_breakdown_avg_cpc='" . $mysql['avg_cpc'] . "', sort_breakdown_income='" . $mysql['income'] . "', sort_breakdown_cost='" . $mysql['cost'] . "', sort_breakdown_net='" . $mysql['net'] . "', sort_breakdown_roi='" . $mysql['roi'] . "' "; $sort_breakdown_result = _mysql_query($sort_breakdown_sql); //($sort_breakdown_sql); } $breakdown_sql = " SELECT * FROM pd_sort_breakdowns WHERE user_id='" . $mysql['user_id'] . "' ORDER BY sort_breakdown_from ASC "; $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); $chartWidth = 800; $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 < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $col++; //populate the PHP array with the Year title $hour = mysql_result($breakdown_result, $i, "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] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); $chart['chart_data'][2][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); $chart['chart_data'][3][$col] = mysql_result($breakdown_result, $i, "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' => .5, 'duration' => .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] = "Conversion Ratio"; } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][0] = "Payout"; } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][0] = "Earnings Per View"; } 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 < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $col++; //populate the PHP array with the Year title $hour = mysql_result($breakdown_result, $i, "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] = mysql_result($breakdown_result, $i, "sort_breakdown_clicks"); } elseif ($pref_chart == 'leads') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_leads"); } elseif ($pref_chart == 'su_ratio') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_su_ratio"); } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_payout"); } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_epc"); } elseif ($pref_chart == 'cpc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_avg_cpc"); } elseif ($pref_chart == 'income') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); } elseif ($pref_chart == 'cost') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); } elseif ($pref_chart == 'net') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_net"); } elseif ($pref_chart == 'roi') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_roi"); } } //$chart[ 'series_color' ] = array ( "003399"); $chart['series_color'] = array("000000"); $chart['chart_type'] = "Line"; $chart['chart_transition'] = array('type' => "dissolve", 'delay' => .5, 'duration' => .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); } function runWeekly($user_pref) { //grab time $time = grab_timeframe(); //get breakdown pref $ci = & get_instance(); $mysql['user_id'] = mysql_real_escape_string($ci->user_entry->get_user()); $user_sql = " SELECT * FROM pd_users LEFT JOIN pd_users_pref USING (user_id) WHERE pd_users.user_id='" . $mysql['user_id'] . "'"; $user_result = _mysql_query($user_sql);; //($user_sql); $user_row = mysql_fetch_assoc($user_result); 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'] == 'leads') { $click_filtered = " AND click_lead='1' "; } //breakdown should be hour, day, month, or year. $breakdown = 'day'; $pref_chart = $user_row['user_pref_chart']; //first delete old report $ci = & get_instance(); $mysql['user_id'] = $ci->user_entry->get_user(); $breakdown_sql = " DELETE FROM pd_sort_breakdowns"; //WHERE user_id='".$mysql['user_id']."' $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); //find where to start from. $start = $time['from']; $end = $time['to']; $ci = & get_instance(); $user_id = $ci->user_entry->get_user(); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); //print_r($r); $input_location_id = $r[4]; $new_time_stamp1 = GetTime($input_location_id); $new_time_stamp = strtotime($new_time_stamp1); if ($new_time_stamp < $end) { $end = $new_time_stamp; } $x = 0; list($start, $end) = fix_start_end_click_times($start, $end); while ($end >= $start) { #error_log("runWeekly: $start - $end"); $s = date('D', $end); $from = mktime(0, 0, 0, date('m', $end), date('d', $end), date('y', $end)); $to = mktime(23, 59, 59, date('m', $end), date('d', $end), date('y', $end)); $end = $end - 86400; $day = date('D', $end); switch ($s) { case "Sun": $day = 1; break; case "Mon": $day = 2; break; case "Tue": $day = 3; break; case "Wed": $day = 4; break; case "Thu": $day = 5; break; case "Fri": $day = 6; break; case "Sat": $day = 7; break; } $mysql['from'] = mysql_real_escape_string($from); $mysql['to'] = mysql_real_escape_string($to); //echo $html['from'] = date('m/d/Y - G:i', $mysql['from']); //echo $html['to'] = date('m/d/Y - G:i', $mysql['to']); //echo $html['to'] = date('m/d/Y - G:i',1285658309); //build query $command = " SELECT COUNT(*) AS clicks, AVG(2c.click_cpc) AS avg_cpc, SUM(2c.click_lead) AS leads, payout, SUM(2c.payout*2c.click_lead) AS income FROM pd_clicks AS 2c "; $db_table = "2c"; $pref_time = false; if ($user_pref == true) { $pref_adv = true; $command = $command . " LEFT JOIN pd_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id) LEFT JOIN pd_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id) "; } else { $pref_adv = false; } $command = $command . " LEFT JOIN pd_camp_overview AS 2ac ON (2c.aff_campaign_id = 2ac.camp_overview_id) LEFT JOIN pd_aff_networks AS 2an ON (2an.aff_network_id = 2ac.aff_network_id) "; /*$command = $command . " LEFT JOIN pd_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id) LEFT JOIN pd_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id) LEFT JOIN pd_camp_overview AS 2ac ON (2c.aff_campaign_id = 2ac.camp_overview_id) LEFT JOIN pd_keywords ON (pd_keywords.keyword_id = 2ca.keyword_id) LEFT JOIN pd_ips ON (pd_ips.ip_id = 2ca.ip_id)";*/ $pref_show = false; if (isset($click_filtered) == false) $click_filtered = 0; //AND (2c.click_alp = '1' OR 2an.aff_network_deleted='0') $pref_order = " AND 2c.click_time > " . $mysql['from'] . " AND 2c.click_time <= " . $mysql['to'] . " "; /* $pref_order = " AND 2c.click_time > ".$mysql['from'] ." AND 2c.click_time <= ".$mysql['to'] .$ppc_nw.$ppc_acc.$aff_nw.$key.$aff_cp.$ip."";*/ //echo $ppc_nw.$ppc_acc.$aff_nw.$key.$aff_cp.$ip; $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']; $click_result = _mysql_query($click_sql); $click_row = mysql_fetch_assoc($click_result); //print_r($click_row); //$click_row['clicks']=0; //$clicks[$day]) if (isset($click_row['cost']) == false) $click_row['cost'] = 0; if (isset($clicks[$day]) == false) $clicks[$day] = 0; if (isset($payout[$day]) == false) $payout[$day] = 0; if (isset($leads[$day]) == false) $leads[$day] = 0; if (isset($total_cost) == false) $total_cost = 0; if (isset($total_clicks) == false) $total_clicks = 0; if (isset($total_payout) == false) $total_payout = 0; if (isset($total_leads) == false) $total_leads = 0; if (isset($total_income) == false) $total_income = 0; //$click_row['income']=0; if (isset($cost[$day]) == false) $cost[$day] = 0; if (isset($income[$day]) == false) $income[$day] = 0; if (isset($avg_cpc[$day]) == false) $avg_cpc[$day] = 0; //$avg_cpc[$day]=0; if (isset($mysql['sort_breakdown_payout']) == false) $mysql['sort_breakdown_payout'] = 0; //$chartWidth=0; //get the stats if (isset($click_filtered) == false) $click_filtered = ''; $clicks[$day] = $click_row['clicks'] + $clicks[$day]; $payout[$day] = $click_row['payout'] + $payout[$day]; $total_clicks = $total_clicks + $click_row['clicks']; $total_payout = $total_payout + $click_row['payout']; //avg cpc and cost //$cost[$day] = $click_row['cost'] + $cost[$day]; // $click_row['cost']; $avg_cpc[$day] = $click_row['avg_cpc'] + $avg_cpc[$day]; //print_r( $click_row); //echo $avg_cpc[2]; $cost[$day] = $clicks[$day] * $avg_cpc[$day]; /*if ($clicks[$day] > 0) { //echo $cost[$day]; $avg_cpc[$day] = $cost[$day] / $clicks[$day]; //echo "$avg_cpc[$day]".$avg_cpc[$day]; }*/ $total_cost = $total_cost + $click_row['cost']; $total_avg_cpc = @round($total_cost / $total_clicks, 5); //leads $leads[$day] = $click_row['leads'] + $leads[$day]; $total_leads = $total_leads + $click_row['leads']; //signup ratio $su_ratio[$day] = @round($leads[$day] / $clicks[$day] * 100, 2); $total_su_ratio = @round($total_leads / $total_clicks * 100, 2); //were not using payout //current payout //$payout = 0; //$payout = $info_row['aff_campaign_payout']; //income //echo "day:".$day; // echo "<br>Income: ".$income[$day]; $income[$day] = $click_row['income'] + $income[$day]; $total_income = $total_income + $click_row['income']; //grab the EPC $epc = @round($income[$day] / $clicks[$day], 2); $total_epc = @round($total_income / $total_clicks, 2); //net income $net[$day] = $income[$day] - $cost[$day]; $total_net = $total_income - $total_cost; //roi $roi[$day] = @round($net[$day] / $cost[$day] * 100); $total_roi = @round($total_net / $total_cost); } for ($day = 1;$day < 8;$day++) { //html escape vars $from = $day; //$to = $hour +1; if ($to == 24) { $to = 0; } if (isset($from) == false) { $from = 0; } $mysql['from'] = mysql_real_escape_string($from); if (isset($to) == false) { $to = 0; } $mysql['to'] = mysql_real_escape_string($to); if (isset($clicks[$day]) == false) { $clicks[$day] = 0; } $mysql['clicks'] = mysql_real_escape_string($clicks[$day]); if (isset($payout[$day]) == false) { $payout[$day] = 0; } $mysql['payout'] = mysql_real_escape_string($payout[$day]); if (isset($leads[$day]) == false) { $leads[$day] = 0; } $mysql['leads'] = mysql_real_escape_string($leads[$day]); if (isset($su_ratio[$day]) == false) { $su_ratio[$day] = 0; } $mysql['su_ratio'] = mysql_real_escape_string($su_ratio[$day]); //if(isset($epc[$day])==false) {$epc[$day]=''; } $epc = 0; $mysql['epc'] = mysql_real_escape_string($epc[$day]); if (isset($avg_cpc[$day]) == false) { $avg_cpc[$day] = 0; } $mysql['avg_cpc'] = mysql_real_escape_string($avg_cpc[$day]); if (isset($income[$day]) == false) { $income[$day] = 0; } $mysql['income'] = mysql_real_escape_string($income[$day]); if (isset($cost[$day]) == false) { $cost[$day] = 0; } $mysql['cost'] = mysql_real_escape_string($cost[$day]); if (isset($net[$day]) == false) { $net[$day] = 0; } $mysql['net'] = mysql_real_escape_string($net[$day]); if (isset($roi[$day]) == false) { $roi[$day] = 0; } $mysql['roi'] = mysql_real_escape_string($roi[$day]); $mysql['sort_breakdown_payout'] = 0; /* $mysql['from'] = mysql_real_escape_string($from); $mysql['to'] = mysql_real_escape_string($to); $mysql['clicks'] = mysql_real_escape_string($clicks[$day]); $mysql['leads'] = mysql_real_escape_string($leads[$day]); $mysql['su_ratio'] = mysql_real_escape_string($su_ratio[$day]); $mysql['epc'] = mysql_real_escape_string($epc[$day]); $mysql['avg_cpc'] = mysql_real_escape_string($avg_cpc[$day]); $mysql['income'] = mysql_real_escape_string($income[$day]); $mysql['cost'] = mysql_real_escape_string($cost[$day]); $mysql['net'] = mysql_real_escape_string($net[$day]); $mysql['roi'] = mysql_real_escape_string($roi[$day]);*/ //insert chart $sort_breakdown_sql = " INSERT INTO pd_sort_breakdowns SET sort_breakdown_from='" . $mysql['from'] . "', sort_breakdown_to='" . $mysql['to'] . "', user_id='" . $mysql['user_id'] . "', sort_breakdown_clicks='" . $mysql['clicks'] . "', sort_breakdown_leads='" . $mysql['leads'] . "', sort_breakdown_su_ratio='" . $mysql['su_ratio'] . "', sort_breakdown_payout='" . $mysql['payout'] . "', sort_breakdown_epc='" . $mysql['epc'] . "', sort_breakdown_avg_cpc='" . $mysql['avg_cpc'] . "', sort_breakdown_income='" . $mysql['income'] . "', sort_breakdown_cost='" . $mysql['cost'] . "', sort_breakdown_net='" . $mysql['net'] . "', sort_breakdown_roi='" . $mysql['roi'] . "' "; $sort_breakdown_result = _mysql_query($sort_breakdown_sql); #echo "<p>$sort_breakdown_sql</p>"; } $breakdown_sql = " SELECT * FROM pd_sort_breakdowns WHERE user_id='" . $mysql['user_id'] . "' ORDER BY sort_breakdown_from ASC "; $breakdown_result = _mysql_query($breakdown_sql); //($breakdown_sql); // $breakdown_result[1]; $chartWidth = 800; $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 //echo mysql_num_rows($breakdown_result); for ($i = 1;$i < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $col++; //populate the PHP array with the Year title $day = mysql_result($breakdown_result, $i, "sort_breakdown_from"); switch ($day) { case 1: $day = "Sun"; break; case 2: $day = "Mon"; break; case 3: $day = "Tue"; break; case 4: $day = "Wed"; break; case 5: $day = "Thu"; break; case 6: $day = "Fri"; break; case 7: $day = "Sat"; break; } $chart['chart_data'][0][$col] = $day; //populate the PHP array with the revenue data $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); $chart['chart_data'][2][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); $chart['chart_data'][3][$col] = mysql_result($breakdown_result, $i, "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' => .5, 'duration' => .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] = "Conversion Ratio"; } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][0] = "Payout"; } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][0] = "Earnings Per View"; } 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 < mysql_num_rows($breakdown_result);$i++) { //determine which column in the PHP array the current data belongs to $col = mysql_result($breakdown_result, $i, "sort_breakdown_from"); $col++; //populate the PHP array with the Year title $day = mysql_result($breakdown_result, $i, "sort_breakdown_from"); switch ($day) { case 1: $day = "Sun"; break; case 2: $day = "Mon"; break; case 3: $day = "Tue"; break; case 4: $day = "Wed"; break; case 5: $day = "Thu"; break; case 6: $day = "Fri"; break; case 7: $day = "Sat"; break; } $chart['chart_data'][0][$col] = $day; //populate the PHP array with the revenue data if ($pref_chart == 'clicks') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_clicks"); } elseif ($pref_chart == 'leads') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_leads"); } elseif ($pref_chart == 'su_ratio') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_su_ratio"); } elseif ($pref_chart == 'payout') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_payout"); } elseif ($pref_chart == 'epc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_epc"); } elseif ($pref_chart == 'cpc') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_avg_cpc"); } elseif ($pref_chart == 'income') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_income"); } elseif ($pref_chart == 'cost') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_cost"); } elseif ($pref_chart == 'net') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_net"); } elseif ($pref_chart == 'roi') { $chart['chart_data'][1][$col] = mysql_result($breakdown_result, $i, "sort_breakdown_roi"); } } //$chart[ 'series_color' ] = array ( "003399"); $chart['series_color'] = array("000000"); $chart['chart_type'] = "Line"; $chart['chart_transition'] = array('type' => "dissolve", 'delay' => .5, 'duration' => .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); } function setClickIdCookie($click_id, $campaign_id = 0) { //set the cookie for the PIXEL to fire, expire in 30 days $ci = & get_instance(); $user_id = $ci->user_entry->get_user(); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); //print_r($r); $input_location_id = $r[4]; $new_time_stamp1 = GetTime($input_location_id); $new_time_stamp = strtotime($new_time_stamp1); $expire = $new_time_stamp + 300; setcookie('trackingpdsubid', $click_id, $expire, '/', $_SERVER['SERVER_NAME']); setcookie('trackingpdsubid_a_' . $click_id, $click_id, $expire, '/', $_SERVER['SERVER_NAME']); } ///////////////////////////////////////////////////////////////////////////////timezone/////////////////////////////////////////////////////////////////////////////// function GetTime1($input_location_id, $user_id) { //$ci= & get_instance(); //$user_id=$ci->user_entry->get_user(); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); $dst = $r['daylight']; //global $dst; /* Check for valid location ID, return 0 date if invalid */ if ($input_location_id > 0) { $result = mysql_query("SELECT timezoneid, gmt_offset, dst_offset, timezone_code FROM timezone WHERE timezoneid = '$input_location_id'"); list($timezoneid, $gmt_offset, $dst_offset, $timezone_code) = mysql_fetch_array($result); } else /* This is the default date returned upon first accessing the page */ return date('Y-m-d H:i'); if ($dst_offset > 0) { if (!($dst)) { /* Set the DST offset to zero if the box is not checked and append the standard time acronym to the timezone code */ $dst_offset = 0; $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "ST"); } else if (!isDaylightSaving($timezoneid, $gmt_offset)) { /* Set the DST offset to zero if the timezone is not currently in DST and append the standard time acronym to the timezone code */ $dst_offset = 0; $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "ST"); } else if ($timezone_code != '') /* Leave the DST offset and append the daylight saving time acronym to the timezone code */ $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "DT"); else /* Assign a timezone code */ $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, ""); } /* Does not observe DST at all */ else $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "ST"); /* Get the DST offset in minutes */ $dst_offset*= 60; /* Get the GMT offset in minutes */ $gmt_offset*= 60; $gmt_hour = gmdate('H'); $gmt_minute = gmdate('i'); /* Calculate the time in the timezone */ $time = $gmt_hour * 60 + $gmt_minute + $gmt_offset + $dst_offset; /* Convert time back into hours and minutes when returning */ // return date('Y-m-d H:i', mktime($time / 60, $time % 60, 0, gmdate('m'), gmdate('d'), gmdate('Y'))) . " $timezone_code"; //return time(). " $timezone_code"; return date('Y-m-d H:i', mktime($time / 60, $time % 60, 0, gmdate('m'), gmdate('d'), gmdate('Y'))); } function GetTime($input_location_id) { $ci = & get_instance(); $user_id = $ci->user_entry->get_user(); $qury1 = "select * from pd_users where user_id='$user_id'"; $res = mysql_query($qury1); $r = mysql_fetch_array($res); $dst = $r['daylight']; //global $dst; /* Check for valid location ID, return 0 date if invalid */ if ($input_location_id > 0) { $result = mysql_query("SELECT timezoneid, gmt_offset, dst_offset, timezone_code FROM timezone WHERE timezoneid = '$input_location_id'"); list($timezoneid, $gmt_offset, $dst_offset, $timezone_code) = mysql_fetch_array($result); } else /* This is the default date returned upon first accessing the page */ return date('Y-m-d H:i'); if ($dst_offset > 0) { if (!($dst)) { /* Set the DST offset to zero if the box is not checked and append the standard time acronym to the timezone code */ $dst_offset = 0; $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "ST"); } else if (!isDaylightSaving($timezoneid, $gmt_offset)) { /* Set the DST offset to zero if the timezone is not currently in DST and append the standard time acronym to the timezone code */ $dst_offset = 0; $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "ST"); } else if ($timezone_code != '') /* Leave the DST offset and append the daylight saving time acronym to the timezone code */ $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "DT"); else /* Assign a timezone code */ $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, ""); } /* Does not observe DST at all */ else $timezone_code = getTimeZoneCode($timezone_code, $gmt_offset + $dst_offset, "ST"); /* Get the DST offset in minutes */ $dst_offset*= 60; /* Get the GMT offset in minutes */ $gmt_offset*= 60; $gmt_hour = gmdate('H'); $gmt_minute = gmdate('i'); /* Calculate the time in the timezone */ $time = $gmt_hour * 60 + $gmt_minute + $gmt_offset + $dst_offset; /* Convert time back into hours and minutes when returning */ // return date('Y-m-d H:i', mktime($time / 60, $time % 60, 0, gmdate('m'), gmdate('d'), gmdate('Y'))) . " $timezone_code"; //return time(). " $timezone_code"; return date('Y-m-d H:i', mktime($time / 60, $time % 60, 0, gmdate('m'), gmdate('d'), gmdate('Y'))); } /* This function returns true if the specified timezone ID is in daylight saving time and false if it is not */ function isDaylightSaving($timezoneid, $gmt_offset) { /* Get the current year by geting GMT time and date and then adding offset */ $gmt_minute = gmdate("i"); $gmt_hour = gmdate("H"); $gmt_month = gmdate("m"); $gmt_day = gmdate("d"); $gmt_year = gmdate("Y"); $cur_year = date("Y", mktime($gmt_hour + $gmt_offset, $gmt_minute, 0, $gmt_month, $gmt_day, $gmt_year)); switch ($timezoneid) { /* North American cases: begins at 2 am on the first Sunday in April and ends on the last Sunday in October. Note: Monterrey does not actually observe DST */ case 4: /* Alaska */ case 5: /* Pacific Time (US & Canada); Tijuana */ case 8: /* Mountain Time (US & Canada) */ case 10: /* Central Time (US & Canada) */ case 11: /* Guadalajara, Mexico City, Monterrey */ case 14: /* Eastern Time (US & Canada) */ case 16: /* Atlantic Time (Canada) */ case 19: /* Newfoundland */ if (afterFirstDayInMonth($cur_year, $cur_year, 4, "Sun", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year, 10, "Sun", $gmt_offset)) return true; else return false; break; case 7: /* Chihuahua, La Paz, Mazatlan */ if (afterFirstDayInMonth($cur_year, $cur_year, 5, "Sun", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year, 9, "Sun", $gmt_offset)) return true; else return false; break; case 18: /* Santiago, Chile */ if (afterSecondDayInMonth($cur_year, $cur_year, 10, "Sat", $gmt_offset) && beforeSecondDayInMonth($cur_year + 1, $cur_year, 3, "Sat", $gmt_offset)) return true; else return false; break; case 20: /* Brasilia, Brazil */ if (afterFirstDayInMonth($cur_year, $cur_year, 11, "Sun", $gmt_offset) && beforeThirdDayInMonth($cur_year, $cur_year, 2, "Sun", $gmt_offset)) return true; else return false; break; case 23: /* Mid-Atlantic */ if (afterLastDayInMonth($cur_year, $cur_year, 3, "Sun", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year, 9, "Sun", $gmt_offset)) return true; else return false; break; /* EU, Russia, other cases: begins at 1 am GMT on the last Sunday in March and ends on the last Sunday in October */ case 22: /* Greenland */ case 24: /* Azores */ case 27: /* Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London */ case 28: /* Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna */ case 29: /* Belgrade, Bratislava, Budapest, Ljubljana, Prague */ case 30: /* Brussels, Copenhagen, Madrid, Paris */ case 31: /* Sarajevo, Skopje, Warsaw, Zagreb */ case 33: /* Athens, Istanbul, Minsk */ case 34: /* Bucharest */ case 37: /* Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius */ case 41: /* Moscow, St. Petersburg, Volgograd */ case 47: /* Ekaterinburg */ case 45: /* Baku, Tbilisi, Yerevan */ case 51: /* Almaty, Novosibirsk */ case 56: /* Krasnoyarsk */ case 58: /* Irkutsk, Ulaan Bataar */ case 64: /* Yakutsk, Sibiria */ case 71: /* Vladivostok */ if (afterLastDayInMonth($cur_year, $cur_year, 3, "Sun", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year, 10, "Sun", $gmt_offset)) return true; else return false; break; case 35: /* Cairo, Egypt */ if (afterLastDayInMonth($cur_year, $cur_year, 4, "Fri", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year, 9, "Thu", $gmt_offset)) return true; else return false; break; case 39: /* Baghdad, Iraq */ if (afterFirstOfTheMonth($cur_year, $cur_year, 4, $gmt_offset) && beforeFirstOfTheMonth($cur_year, $cur_year, 10, $gmt_offset)) return true; else return false; break; case 43: /* Tehran, Iran - Note: This is an approximation to the actual DST dates since Iran goes by the Persian calendar. There are tools for converting between Gregorian and Persian calendars at www.farsiweb.info. This may be added at a later date for better accuracy */ if (afterLastDayInMonth($cur_year, $cur_year, 3, "Sun", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year, 9, "Sun", $gmt_offset)) return true; else return false; break; case 65: /* Adelaide */ case 68: /* Canberra, Melbourne, Sydney */ if (afterLastDayInMonth($cur_year, $cur_year, 10, "Sun", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year + 1, 3, "Sun", $gmt_offset)) return true; else return false; break; case 70: /* Hobart */ if (afterFirstDayInMonth($cur_year, $cur_year, 10, "Sun", $gmt_offset) && beforeLastDayInMonth($cur_year, $cur_year + 1, 3, "Sun", $gmt_offset)) return true; else return false; break; case 73: /* Auckland, Wellington */ if (afterFirstDayInMonth($cur_year, $cur_year, 10, "Sun", $gmt_offset) && beforeThirdDayInMonth($cur_year, $cur_year + 1, 3, "Sun", $gmt_offset)) return true; else return false; break; default: break; } return false; } /* This function returns true if the current date (at the specified GMT offset) is after the first specified day of the week in specified month and false if it is not */ function afterFirstDayInMonth($curYear, $year, $month, $day, $gmt_offset) { for ($i = 1;$i < 8;$i++) { if (date("D", mktime(0, 0, 0, $month, $i)) == $day) { $first_day = $i; break; } } $curDay = gmdate("d"); $curMonth = gmdate("m"); $curHour = gmdate("H") + $gmt_offset; /* The current time stamp */ $cur_stamp = mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the first occurence for the specified day in the month */ $first_day_stamp = mktime(2, 0, 0, $month, $first_day, $year); if ($cur_stamp >= $first_day_stamp) return true; return false; } /* This function returns true if the current date (at the specified GMT offset) is before the last specified day of the week in specified month and false if it is not */ function beforeLastDayInMonth($curYear, $year, $month, $day, $gmt_offset) { $days_in_month = getDaysInMonth($month); for ($i = $days_in_month;$i > ($days_in_month - 8);$i--) { if (date("D", mktime(0, 0, 0, $month, $i)) == $day) { $last_day = $i; break; } } $curDay = gmdate("d"); $curMonth = gmdate("m"); $curHour = gmdate("H") + $gmt_offset; /* The current time stamp */ $cur_stamp = mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the last occurrence of the day in the month at 2 am */ $last_sun_stamp = mktime(2, 0, 0, $month, $last_day, $year); if ($cur_stamp < $last_sun_stamp) return true; return false; } /* This function returns true if the current date (at the specified GMT offset) is after the last specified day of the week in specified month and false if it is not */ function afterLastDayInMonth($curYear, $year, $month, $day, $gmt_offset) { $days_in_month = getDaysInMonth($month); for ($i = $days_in_month;$i > ($days_in_month - 8);$i--) { if (date("D", mktime(0, 0, 0, $month, $i)) == $day) { $last_day = $i; break; } } $curDay = gmdate("d"); $curMonth = gmdate("m"); /* All EU countries observe the DST change at 1 am GMT */ $curHour = gmdate("H"); /* The current time stamp */ $cur_stamp = mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the first occurence for the specified day in the month */ $last_day_stamp = mktime(1, 0, 0, $month, $last_day, $year); if ($cur_stamp >= $last_day_stamp) return true; return false; } /* This function returns true if the current date (at the specified GMT offset) is after the first day of the specified month and false if it is not */ function afterFirstOfTheMonth($curYear, $year, $month, $gmt_offset) { $curDay = gmdate("d"); $curMonth = gmdate("m"); $curHour = gmdate("H") + $gmt_offset; /* The current time stamp */ $cur_stamp = mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the first of the month */ $last_day_stamp = mktime(3, 0, 0, $month, 1, $year); if ($cur_stamp >= $last_day_stamp) return true; return false; } /* This function returns true if the current date (at the specified GMT offset) is before the first day of the specified month and false if it is not */ function beforeFirstOfTheMonth($curYear, $year, $month, $gmt_offset) { $curDay = gmdate("d"); $curMonth = gmdate("m"); $curHour = gmdate("H") + $gmt_offset; /* The current time stamp */ $cur_stamp = mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the first of the month */ $first_day_stamp = mktime(3, 0, 0, $month, 1, $year); if ($cur_stamp < $first_day_stamp) return true; return false; } /* This function returns true if the current date (at the specified GMT offset) is before the third occurrence of the specified day of the week in the specified month and false if it is not */ function beforeThirdDayInMonth($curYear, $year, $month, $day, $gmt_offset) { $count = 0; for ($i = 1;$i < 22;$i++) { if (date("D", mktime(0, 0, 0, $month, $i)) == $day) { $count++; if ($count == 3) { $third_day = $i; break; } } } $curDay = gmdate("d"); $curMonth = gmdate("m"); $curHour = gmdate("H") + $gmt_offset; /* The current time stamp */ $cur_stamp = mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the third occurence for the specified day in the month */ $third_day_stamp = mktime(2, 0, 0, $month, $third_day, $year); if ($cur_stamp < $third_day_stamp) return true; return false; } /* This function returns true if the current date (at the specified GMT offset) is before the second occurrence of the specified day of the week in the specified month and false if it is not */ function beforeSecondDayInMonth($curYear, $year, $month, $day, $gmt_offset) { $count = 0; for ($i = 1;$i < 15;$i++) { if (date("D", mktime(0, 0, 0, $month, $i)) == $day) { $count++; if ($count == 2) { $second_day = $i; break; } } } $curDay = gmdate("d"); $curMonth = gmdate("m"); $curHour = gmdate("H") + $gmt_offset; /* The current time stamp */ $cur_stamp = mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the second occurence of the specified day in the month; change in Chile occurs at midnight */ $second_day_stamp = mktime(0, 0, 0, $month, $second_day, $year); if ($cur_stamp < $second_day_stamp) return true; return false; } /* This function returns true if the current date (at the specified GMT offset) is after the second occurrence of the specified day of the week in the specified month and false if it is not */ function afterSecondDayInMonth($curYear, $year, $month, $day, $gmt_offset) { $count = 0; for ($i = 1;$i < 15;$i++) { if (date("D", @mktime(0, 0, 0, $month, $i)) == $day) { $count++; if ($count == 2) { $second_day = $i; break; } } } $curDay = gmdate("d"); $curMonth = gmdate("m"); $curHour = gmdate("H") + $gmt_offset; /* The current time stamp */ $cur_stamp = @mktime($curHour, 0, 0, $curMonth, $curDay, $curYear); /* Time stamp for the second occurence of the specified day in the month; change in Chile occurs at midnight */ $second_day_stamp = @mktime(0, 0, 0, $month, $second_day, $year); if ($cur_stamp >= $second_day_stamp) return true; return false; } /* A function that returns the number of days in the specified month */ function getDaysInMonth($month) { switch ($month) { /* The February case, check for leap year */ case 2: return (date("L") ? 29 : 28); break; /* Months with 31 days */ case 1: case 3: case 5: case 7: case 8: case 10: case 12: return 31; break; default: return 30; break; } } /* This function returns a formated time zone code based on the value of the input code, the offset, any suffix that might apply */ function getTimeZoneCode($timezone_code, $total_offset, $suffix) { if ($timezone_code == '') { /* If the code is NULL, create one */ if ($total_offset > 0) return ("GMT +$total_offset"); else if ($total_offset == 0) return ("GMT"); else return ("GMT $total_offset"); } else /* If not, append the suffix */ return $timezone_code . "$suffix"; } function fix_start_end_click_times($start, $end) { $q = "SELECT MIN(click_time) AS start, MAX(click_time) AS end FROM pd_clicks WHERE click_time >= $start AND click_time <= $end"; $result = _mysql_query($q); $row = mysql_fetch_assoc($result); if (empty($row['start'])) $row['start'] = $end; if (empty($row['end'])) $row['end'] = $end; return array($row['start'], $row['end']); } ?>