Prosellers Shell V2.0

Editing: class-dataengine.php

Cancel

<?php
ini_set('memory_limit', '-1');
if (! $_SESSION['user_timezone']) {
    date_default_timezone_set('GMT');
} else {
    date_default_timezone_set($_SESSION['user_timezone']);
}

class DataEngine
{

private $total_clicks = '';

private $mysql = Array();

private static $db;

private static $found_rows;

private $forDownload = 0;

function setDownload()
    {
        $this->forDownload = 1;
    }

function setDisplay()
    {
        $this->forDownload = 0;
    }

function foundRows()
    {
        return self::$found_rows;
    }

function getReportData($reportType, $clickFrom, $clickTo, $cpv)
    {
        switch ($reportType) {
            case 'LpOverview':
                return $this->doLpOverviewReport($clickFrom, $clickTo, $cpv);
                break;
            case 'campaignOverview':
                return $this->doCampaignOverviewReport($clickFrom, $clickTo, $cpv);
                break;
            case 'slp_direct_link_per_ppc':
                return $this->doPerPpcReport('slp_direct_link', $clickFrom, $clickTo, $cpv);
                break;
            case 'alp_per_ppc':
                return $this->doPerPpcReport('alp', $clickFrom, $clickTo, $cpv);
                break;
            case 'breakdown':
                return $this->doBreakdownReport($clickFrom, $clickTo, $cpv);
                break;
            case 'hourly':
                return $this->doHourlyReport($clickFrom, $clickTo, $cpv);
                break;
            case 'weekly':
                return $this->doWeeklyReport($clickFrom, $clickTo, $cpv);
                break;
            case 'keyword':
                return $this->doKeywordReport($clickFrom, $clickTo, $cpv);
                break;
            case 'textad':
                return $this->doTextadReport($clickFrom, $clickTo, $cpv);
                break;
            case 'referer':
                return $this->doRefererReport($clickFrom, $clickTo, $cpv);
                break;
            case 'ip':
                return $this->doIPReport($clickFrom, $clickTo, $cpv);
                break;
            case 'country':
                return $this->doCountryReport($clickFrom, $clickTo, $cpv);
                break;
            case 'region':
                return $this->doRegionReport($clickFrom, $clickTo, $cpv);
                break;
            case 'city':
                return $this->doCityReport($clickFrom, $clickTo, $cpv);
                break;
            case 'isp':
                return $this->doISPReport($clickFrom, $clickTo, $cpv);
                break;
            case 'landingpage':
                return $this->doLandingPageReport($clickFrom, $clickTo, $cpv);
                break;
            case 'device':
                return $this->doDeviceReport($clickFrom, $clickTo, $cpv);
                break;
            case 'browser':
                return $this->doBrowserReport($clickFrom, $clickTo, $cpv);
                break;
            case 'platform':
                return $this->doPlatformReport($clickFrom, $clickTo, $cpv);
                break;
            case 'variable':
                return $this->doVariableReport($clickFrom, $clickTo, $cpv);
                break;
        }
    }

function get_ip_id($ip)
    {
        global $memcacheWorking, $memcache;

$mysql['ip_address'] = self::$db->real_escape_string($ip->address);
        
        if($inet6_ntoa == '' && $ip->type == 'ipv6'){
            $mysql['ip_address'] = inet6_aton($mysql['ip_address']); //encode for db check
        }
        
        if ($ip->type==='ipv6') {
            $ip_sql = 'SELECT 202_ips.ip_id FROM 202_ips_v6  INNER JOIN 202_ips on (202_ips_v6.ip_id = 202_ips.ip_address COLLATE utf8mb4_general_ci) WHERE 202_ips_v6.ip_address= '.$inet6_aton.'("' . $mysql['ip_address'] . '")';
        }
        else{
            $ip_sql = "SELECT ip_id FROM 202_ips WHERE ip_address='" . $mysql['ip_address'] . "'";
        }
                
        if ($memcacheWorking) {
            $time = 2592000; // 7 days in sec
                            // get from memcached
            $getID = $memcache->get(md5("ip-id" . $mysql['ip_address'] . systemHash()));
            
            if ($getID) {
                $ip_id = $getID;
                return $ip_id;
            } else {
                
                $ip_result = _mysqli_query($ip_sql);
                $ip_row = $ip_result->fetch_assoc();
                if ($ip_row) {
                    // if this ip_id already exists, return the ip_id for it.
                    $ip_id = $ip_row['ip_id'];
                    // add to memcached
                    $setID = setCache(md5("ip-id" . $mysql['ip_address'] . systemHash()), $ip_id, $time);
                    return $ip_id;
                } else {
                    //insert ip
                    INDEXES::insert_ip(self::$db,$ip);
                }
            }
        } else {
            $ip_result = _mysqli_query($ip_sql);
            $ip_row = $ip_result->fetch_assoc();
           
            if ($ip_row) {
                // if this ip already exists, return the ip_id for it.
                $ip_id = $ip_row['ip_id'];
                return $ip_id;
            } else {
                //insert ip
                INDEXES::insert_ip(self::$db,$ip);
            }
        }
    }
    
    
    function ipAddress($ip_address){
        $ip = new stdClass;
    
        if (filter_var($ip_address, FILTER_VALIDATE_IP)) {
            $ip->address=$ip_address;
            if (filter_var($ip_address, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
                $ip->type='ipv4';
            } else {
                $ip->type='ipv6';
            }
        } else {
            $ip->type='invalid';
        }
    
        return $ip;
    }
    
    // this returns the site_url_id, when a site_url_address is given
    function get_site_url_id($site_url_address)
    {
        global $memcacheWorking, $memcache;
        
        $mysql['site_url_address'] = self::$db->real_escape_string($site_url_address);
        
        if ($memcacheWorking) {
            $time = 604800; // 7 days in sec
                            // get from memcached
            $getURL = $memcache->get(md5("url-id" . $site_url_address . systemHash()));
            if ($getURL) {
                return $getURL;
            } else {
                $site_url_sql = "SELECT  GROUP_CONCAT(distinct 2de.click_referer_site_url_id) AS site_url_id FROM 202_dataengine as 2de LEFT JOIN 202_site_urls ON (2de.click_referer_site_url_id = site_url_id)  WHERE site_url_address LIKE '%" . $mysql['site_url_address'] . "%'";
                
                $site_url_result = _mysqli_query($site_url_sql);
                $site_url_row = $site_url_result->fetch_assoc();
                if ($site_url_row) {
                    // if this site_url_id already exists, return the site_url_id for it.
                    $site_url_id = $site_url_row['site_url_id'];
                    $setID = $memcache->set(md5("url-id" . $site_url_address . systemHash()), $site_url_id, false, $time);
                    return $site_url_id;
                }
            }
        } else {
            
            $site_url_sql = "SELECT GROUP_CONCAT(distinct 2de.click_referer_site_url_id) AS site_url_id FROM 202_dataengine as 2de LEFT JOIN 202_site_urls ON (2de.click_referer_site_url_id = site_url_id)  WHERE site_url_address LIKE '%" . $mysql['site_url_address'] . "%'";
            $site_url_result = _mysqli_query($site_url_sql);
            $site_url_row = $site_url_result->fetch_assoc();
            
            if ($site_url_row) {
                // if this site_url_id already exists, return the site_url_id for it.
                $site_url_id = $site_url_row['site_url_id'];
                
                return $site_url_id;
            }
        }
    }

function doCampaignOverviewReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $click_filtered = $this->getAccountOverviewFilters();

$click_sql = "SELECT  2c.aff_campaign_id, 
             2ac.aff_campaign_name, 
             SUM(2c.clicks) AS clicks,
            SUM(2c.click_out) AS click_out, 
            SUM(2c.leads) AS leads, 
            2ac.aff_campaign_payout AS payout, 
            (clicks/click_out)*100 as ctr,
            SUM(2c.income) AS income, 
            SUM(2c.cost) AS cost,
            SUM(income)/SUM(clicks) as epc,
            (SUM(click_lead)/sum(clicks))*100 as su_ratio,
            SUM(cost)/sum(clicks) AS cpc,
            (SUM(income)-SUM(cost)) AS net,
            ((SUM(income)-SUM(cost))/SUM(cost)*100 ) as roi 
             FROM 202_dataengine AS 2c 
             LEFT OUTER JOIN 202_aff_campaigns AS 2ac ON (2c.aff_campaign_id = 2ac.aff_campaign_id) 
             WHERE 2c.user_id = " . $this->mysql['user_id'] . "
AND 2c.click_time >= " . $mysql['from'] . "
AND 2c.click_time <= " . $mysql['to'] . $click_filtered . "
             GROUP BY IF(2c.aff_campaign_id is null or 2c.aff_campaign_id = '', '0', 2c.aff_campaign_id)";
         
       /* $click_sql = "SELECT 2pn.ppc_network_id as ppc_network_id, 2pn.ppc_network_name, 2ac.aff_network_id, 2an.aff_network_name,
            2c.aff_campaign_id, 2ac.aff_campaign_name, SUM(2c.clicks) AS clicks,
            SUM(2c.click_out) AS click_out, 
            SUM(2c.leads) AS leads, 
            2ac.aff_campaign_payout AS payout, 
            (clicks/click_out)*100 as ctr,
            SUM(2c.income) AS income, 
            SUM(2c.cost) AS cost,
            SUM(income)/SUM(clicks) as epc,
            (SUM(click_lead)/sum(clicks))*100 as su_ratio,
            SUM(cost)/sum(clicks) AS cpc,
            (SUM(income)-SUM(cost)) AS net,
            ((SUM(income)-SUM(cost))/SUM(cost)*100 ) as roi
            FROM 202_dataengine AS 2c LEFT OUTER JOIN 202_aff_campaigns AS 2ac ON (2c.aff_campaign_id = 2ac.aff_campaign_id)
            LEFT OUTER JOIN 202_ppc_accounts AS 2pa ON (2c.ppc_account_id = 2pa.ppc_account_id)
            LEFT OUTER JOIN 202_ppc_networks AS 2pn ON (2pa.ppc_network_id = 2pn.ppc_network_id)
            LEFT OUTER JOIN 202_aff_networks AS 2an ON (2ac.aff_network_id = 2an.aff_network_id)
            WHERE 2c.user_id = " . $this->mysql['user_id'] . "
AND 2c.click_time >= " . $mysql['from'] . "
AND 2c.click_time <= " . $mysql['to'] . $click_filtered . "
            GROUP BY IFNULL(2c.aff_campaign_id, '')";*/
      //  echo $click_sql;
    //    die();
        
   /*      $click_sql = "select aff_network_name,
aff_campaign_name,
2st.aff_campaign_id, sum(clicks) as clicks,  sum(click_out) as click_out,";
        $click_sql .= "(clicks/click_out)*100 as ctr,SUM(leads) AS leads, (SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout,SUM(income)/SUM(clicks) as epc,SUM(cost)/sum(clicks) AS cpc,SUM(income) AS income, SUM(cost) AS cost,(SUM(income)-SUM(cost)) AS net,((SUM(income)-SUM(cost))/SUM(cost)*100 ) as roi
from 202_dataengine as 2st
LEFT JOIN 202_aff_campaigns USING (aff_campaign_id)
LEFT JOIN 202_aff_networks on (2st.aff_network_id= 202_aff_networks.`aff_network_id`)";
        $click_sql .=  $this->mysql['user_id_query'] . "
AND 2st.click_time >= " . $mysql['from'] . "
AND 2st.click_time <= " . $mysql['to'] . $click_filtered . "
AND 2st.aff_campaign_id IS NOT NULL group BY aff_campaign_id
ORDER BY aff_campaign_id ASC"; */

$click_result = _mysqli_query($click_sql); 
        
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            $data[] = $this->htmlFormat($click_row, $cpv,'','overview');
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
            $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');

return $data;
        
    }

$click_result = _mysqli_query($click_sql);
        
        $ids = array();
        
        while ($click_row = $click_result->fetch_assoc()) {
            if ($click_row) {
                $data[$click_row[$select_by_id]] = $this->htmlFormat($click_row, $cpv, 'total');
                $ids[] = $click_row[$select_by_id];
            }
        }
        
        $ppc_sql = "select
            ppc_account_name,
            ppc_network_name,
            2st.ppc_account_id, 
            2st.{$select_by_id},
            sum(clicks) as clicks,
            sum(click_out) as click_out,
            (clicks/click_out)*100 as ctr,
            SUM(leads) AS leads,
            (SUM(click_lead)/sum(clicks))*100 as su_ratio,
            (SUM(income) / sum(leads)) AS payout, 
            SUM(income)/SUM(clicks) as epc,
            SUM(cost)/sum(clicks) AS cpc,
            SUM(income) AS income,
            SUM(cost) AS cost,
            (SUM(income)-SUM(cost)) AS net,
            ((SUM(income)-SUM(cost))/SUM(cost)*100 ) as roi
            from 202_dataengine as 2st
            LEFT JOIN 202_ppc_accounts ON (2st.ppc_account_id = 202_ppc_accounts.ppc_account_id) 
            LEFT JOIN 202_ppc_networks ON (202_ppc_accounts.ppc_network_id = 202_ppc_networks.ppc_network_id)".
            $this->mysql['user_id_query'].
            " AND 2st.{$select_by_id} IN (" . implode(",", $ids) . ")";
        
        if ($type == 'alp') {
            $ppc_sql .= " AND 2st.aff_campaign_id IS FALSE";
        }
        
        $ppc_sql .= "
            AND 2st.click_time >= '" . $mysql['from'] . "' 
            AND 2st.click_time <= '" . $mysql['to'] . "' 
            group BY 2st.{$select_by_id},2st.ppc_account_id 
            ORDER BY 2st.ppc_account_id ASC;";
   
        $ppc_result = _mysqli_query($ppc_sql);

if ($ppc_result->num_rows > 0) {
            while ($ppc_row = $ppc_result->fetch_assoc()) {
                $data[$ppc_row[$select_by_id]]['ppc_accounts'][$ppc_row['ppc_account_id']] = $this->htmlFormat($ppc_row, $cpv);
            }
        }
        
        return $data;
    }

function doBreakdownReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        // $rangeBreakdwon=$up->getPref('user_pref_breakdown');
        // echo $rangeBreakdwon;
        switch ($up->getPref('user_pref_breakdown')) {
            case 'hour':
                $groupby = " HOUR(FROM_UNIXTIME(click_time)) ";
                $dateFormat = "DATE_FORMAT(FROM_UNIXTIME(click_time),'%b %d, %Y at %l%p')";
                break;
            case 'day':
                $groupby = " DAY(FROM_UNIXTIME(click_time)) ";
                $dateFormat = "DATE_FORMAT(FROM_UNIXTIME(click_time),'%b %d, %Y')";
                break;
            case 'month':
                $groupby = " MONTH(FROM_UNIXTIME(click_time)) ";
                $dateFormat = "DATE_FORMAT(FROM_UNIXTIME(click_time),'%b %Y')";
                break;
            case 'year':
                $groupby = " YEAR(FROM_UNIXTIME(click_time)) ";
                $dateFormat = "DATE_FORMAT(FROM_UNIXTIME(click_time),'%Y')";
                break;
        }

$click_filtered = $this->getFilters();
        $click_sql = " 
SELECT " . $dateFormat . " as click_time_from_disp,sum(clicks) as clicks, sum(click_out) as click_out, 
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads, 
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout,  
SUM(2st.income) AS income, 
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by" . $groupby . $this->sortOrder('sort_breakdown_time_order asc');

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0; // counter for averages
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++; // increment the counter so we can get the right avarage
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
            $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        return $data;
    }

function doHourlyReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  DATE_FORMAT(FROM_UNIXTIME(click_time),'%l %p')  as click_time_from_disp, DATE_FORMAT(FROM_UNIXTIME(click_time),'%p') as ampm, sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by HOUR(FROM_UNIXTIME(click_time)) " . $this->sortOrder('breakdown asc');
  
        $click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        return $data;
    }

function doWeeklyReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT DATE_FORMAT(FROM_UNIXTIME(click_time),'%a') as click_time_from_disp, DATE_FORMAT(FROM_UNIXTIME(click_time),'%w') as click_time_from_sort, sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] .  $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by click_time_from_disp  ORDER BY click_time_from_sort ASC"; // . $this->sortOrder();
                                                                                                                                                                                                                                                                                                                                                         
        $click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        return $data;
    }

function doKeywordReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT SQL_CALC_FOUND_ROWS `keyword`,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st LEFT JOIN 202_keywords as 2k on (2st.keyword_id= 2k.keyword_id)             
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by keyword" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the number of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doTextadReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT SQL_CALC_FOUND_ROWS `text_ad_name`,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_text_ads on (2st.text_ad_id= 202_text_ads.text_ad_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by text_ad_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the number of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doRefererReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS site_domain_host as referer_name,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_site_urls on (2st.click_referer_site_url_id = 202_site_urls.site_url_id)
 LEFT JOIN 202_site_domains on (202_site_domains.site_domain_id = 202_site_urls.site_domain_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by referer_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the number of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doIPReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS IFNULL(".$inet6_ntoa."(2i6.ip_address),2i.ip_address) as ip_address,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_ips AS 2i on (2st.ip_id = 2i.ip_id)
LEFT JOIN 202_ips_v6 AS 2i6 ON (2i6.ip_id = 2i.ip_address COLLATE utf8mb4_general_ci)
             " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by ip_address" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
            $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doCountryReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS country_name,country_code,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_locations_country on (2st.country_id = 202_locations_country.country_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by country_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doRegionReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS region_name,country_code,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_locations_region on (2st.region_id = 202_locations_region.region_id)
LEFT JOIN 202_locations_country on (202_locations_region.main_country_id = 202_locations_country.country_id) 
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by region_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doCityReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS city_name,country_code,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_locations_city on (2st.city_id = 202_locations_city.city_id)
LEFT JOIN 202_locations_country on (202_locations_city.main_country_id = 202_locations_country.country_id)         
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by city_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doISPReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS isp_name,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_locations_isp on (2st.isp_id = 202_locations_isp.isp_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by isp_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doLandingPageReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS landing_page_nickname,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_landing_pages on (2st.landing_page_id = 202_landing_pages.landing_page_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by landing_page_nickname" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doDeviceReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS device_name,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_device_models on (2st.device_id = 202_device_models.device_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by device_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

function doBrowserReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS browser_name,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_browsers on (2st.browser_id = 202_browsers.browser_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by browser_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        return $data;
    }

function doPlatformReport($clickFrom, $clickTo, $cpv)
    {
        $mysql['from'] = $clickFrom;
        $mysql['to'] = $clickTo;
        $up = new UserPrefs();
        $click_filtered = $this->getFilters();
        $click_sql = "
SELECT  SQL_CALC_FOUND_ROWS platform_name,sum(clicks) as clicks, sum(click_out) as click_out,
	    (clicks/click_out)*100 as ctr,
	    SUM(leads) AS leads,
(SUM(click_lead)/sum(clicks))*100 as su_ratio,
(SUM(income) / sum(leads)) AS payout, 
SUM(2st.income) AS income,
SUM(2st.income)/sum(clicks) as epc,
SUM(2st.cost) AS cost,
SUM(2st.cost)/sum(clicks) AS cpc,
(SUM(2st.income)-SUM(2st.cost)) AS net,
((SUM(2st.income)-SUM(2st.cost))/SUM(2st.cost)*100 ) as roi FROM 202_dataengine as 2st " . $click_filtered['join'] . "  
LEFT JOIN 202_platforms on (2st.platform_id = 202_platforms.platform_id)
            " . $this->mysql['user_id_query'] . " AND click_time >= " . $mysql['from'] . " AND click_time <= " . $mysql['to'] . $click_filtered['filter'] . " group by platform_name" . $this->sortOrder() . $click_filtered['limit'];

$click_result = _mysqli_query($click_sql); // ($click_sql);
        $i = 0;
        while ($click_row = $click_result->fetch_assoc()) {
            $i ++;
            // print_r($click_row);
            $data[] = $this->htmlFormat($click_row, $cpv);
            $totals['clicks'] = $totals['clicks'] + $click_row['clicks'];
            $totals['click_out'] = $totals['click_out'] + $click_row['click_out'];
            $totals['ctr'] = @round($totals['click_out'] / $totals['clicks'] * 100, 2);
            $totals['cost'] = $totals['cost'] + $click_row['cost'];
            $totals['cpc'] = @round($totals['cost'] / $totals['clicks'], 5);
            $totals['leads'] = $totals['leads'] + $click_row['leads'];
            $totals['su_ratio'] = @round($totals['leads'] / $totals['clicks'] * 100, 2);
            $totals['payout'] = @round(($totals['payout'] + $click_row['payout']) / $i, 2);
            $totals['income'] = $totals['income'] + $click_row['income'];
            $totals['epc'] = @round($totals['income'] / $totals['clicks'], 5);
            $totals['net'] = $totals['income'] - $totals['cost'];
           $totals['roi'] = ($totals['cost']=='0') ? 0 : @round($totals['net'] / $totals['cost'] * 100);
            // print_r($click_row);
        }
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        
        $count_sql = "select FOUND_ROWS() as found_rows"; // query to get the nymber of results
        $count_result = _mysqli_query($count_sql);
        $count_row = $count_result->fetch_assoc();
        self::$found_rows = ($count_row['found_rows']);
        
        return $data;
    }

$click_result = _mysqli_query($click_sql);
        // Loop Traffic Sources
        if ($click_result) {
            while ($click_row = $click_result->fetch_assoc()) {
                $data[$click_row['ppc_network_id']][] = $this->htmlFormat($click_row, $cpv);
                $data[$click_row['ppc_network_id']]['variables'][$click_row['ppc_variable_id']][] = $this->htmlFormat($click_row, $cpv);
                $data[$click_row['ppc_network_id']]['variables'][$click_row['ppc_variable_id']]['values'][] = $this->htmlFormat($click_row, $cpv);
            }
        }
        
        $data[] = $this->htmlFormat($totals, $cpv, 'total');
        return $data;
    }

function htmlFormat($click_row, $cpv, $type = '', $mainKey = '')
    {

$currency_result = self::$db->query("SELECT user_account_currency FROM 202_users_pref WHERE user_id = '" . $this->mysql['user_id'] . "'");
        $currency_row = $currency_result->fetch_assoc();
        $currency_row['user_account_currency'];
        
        $prepend = '';
        $theCTR = @round($click_row['click_out'] / $click_row['clicks'] * 100, 2);
        if ($type == 'total')
            $prepend = "total_";
        if ($click_row) {
            foreach ($click_row as $key => $data_row) {
                switch ($key) {
                    case 'clicks':
                    case 'leads':
                    case 'click_out':
                        $html[$prepend . $key] = htmlentities(number_format($data_row), ENT_QUOTES, 'UTF-8');
                        break;
                    case 'su_ratio':
                        
                        $html[$prepend . $key] = htmlentities(round($data_row, 2) . '%', ENT_QUOTES, 'UTF-8');
                        break;
                    
                    case 'ctr':
                        $html[$prepend . $key] = htmlentities(round($theCTR, 2) . '%', ENT_QUOTES, 'UTF-8');
                        break;
                    case 'payout':
                    case 'income':
                        $html[$prepend . $key] = htmlentities(dollar_format($data_row, $currency_row['user_account_currency']), ENT_QUOTES, 'UTF-8');
                        break;
                    case 'epc':
                    case 'cpc':
                    case 'cost':
                    case 'net':
                        $html[$prepend . $key] = htmlentities(dollar_format($data_row, $currency_row['user_account_currency'], $cpv), ENT_QUOTES, 'UTF-8');
                        break;
                    case 'roi':
                        $html[$prepend . $key] = htmlentities(number_format($data_row) . '%', ENT_QUOTES, 'UTF-8');
                        break;
                    case 'click_time_from_disp':
                        $upper = array(
                            'AM',
                            'PM'
                        );
                        $lower = array(
                            'am',
                            'pm'
                        );
                        $html[$prepend . $key] = htmlentities(str_replace($upper, $lower, $data_row), ENT_QUOTES, 'UTF-8');
                        break;
                    case 'keyword':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no keyword]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'text_ad_name':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no text ad]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'referer_name':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no referer]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'ip_address':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no ip]', ENT_QUOTES, 'UTF-8');
                        else {
                            if (ctype_print($data_row)) { // non binary value so just return
                                $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                            } else { // binary value so decode and return
                                $html[$prepend . $key] = htmlentities(inet6_ntoa($data_row), ENT_QUOTES, 'UTF-8'); // decode ipv6 for display
                            }
                        }
                        break;
                    case 'country_name':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no country]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'region_name':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no region]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'city_name':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no city]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'country_code':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('non', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'isp_name':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no isp]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'landing_page_nickname':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[direct link]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    
                    case 'device':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no device]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'browser':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no browser]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    case 'platform':
                        if (! $data_row)
                            $html[$prepend . $key] = htmlentities('[no platform]', ENT_QUOTES, 'UTF-8');
                        else
                            $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                        break;
                    default:
                        $html[$prepend . $key] = htmlentities($data_row, ENT_QUOTES, 'UTF-8');
                }
            }
        }

if (strlen($html['aff_campaign_name']) == 0){
            $html['aff_campaign_name'] = "[Landing Page/Smart Redirector Campaign]";
            if($mainKey='overview')
                $html['aff_campaign_name'] = "[Landing Page/Smart Redirector Campaign]";
        }
        
        $stop = array(
            'total_clicks',
            'total_click_out',
            'total_ctr',
            'total_cost',
            'total_cpc',
            'total_leads',
            'total_su_ratio',
            'total_payout',
            'total_income',
            'total_epc',
            'total_net',
            'total_roi'
        );
        foreach ($stop as $key) {
            
            if ($html[$key] == '') {
                $html[$key] = '0';
            }
        }

return ($html);
    }

function sortOrder($order='')
    {

// run the order by settings
        if(isset($order)){
            $_POST['order'] = htmlentities($order, ENT_QUOTES, 'UTF-8');
        }
        
        $html['order'] = htmlentities($_POST['order'], ENT_QUOTES, 'UTF-8');
        
        
        
        $html['sort_breakdown_time_order'] = 'sort_breakdown_time_order asc';
        if ($_POST['order'] == 'sort_breakdown_time_order desc') {
            $html['sort_breakdown_time_order'] = 'sort_breakdown_time_order desc';
            $mysql['order'] = 'ORDER BY click_time DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_time_order asc') {
            $html['sort_breakdown_time_order'] = 'sort_breakdown_time_order asc';
            $mysql['order'] = 'ORDER BY click_time ASC';
        }
        
        $html['sort_breakdown_order'] = 'breakdown asc';
        if ($_POST['order'] == 'breakdown desc') {
            $html['sort_breakdown_order'] = 'breakdown desc';
            $mysql['order'] = 'ORDER BY cast(DATE_FORMAT(FROM_UNIXTIME(click_time),"%k") as UNSIGNED) DESC';
        } elseif ($_POST['order'] == 'breakdown asc') {
            $html['sort_breakdown_order'] = 'breakdown asc';
            $mysql['order'] = 'ORDER BY cast(DATE_FORMAT(FROM_UNIXTIME(click_time),"%k") as UNSIGNED) ASC';
        }
        
        $html['sort_breakdown_clicks_order'] = 'sort_breakdown_clicks asc';
        if ($_POST['order'] == 'sort_breakdown_clicks asc') {
            $html['sort_breakdown_clicks_order'] = 'sort_breakdown_clicks desc';
            $mysql['order'] = 'ORDER BY `clicks` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_clicks desc') {
            $html['sort_breakdown_clicks_order'] = 'sort_breakdown_clicks asc';
            $mysql['order'] = 'ORDER BY `clicks` ASC';
        }
        
        $html['sort_breakdown_click_throughs_order'] = 'sort_breakdown_click_throughs asc';
        if ($_POST['order'] == 'sort_breakdown_click_throughs asc') {
            $html['sort_breakdown_click_throughs_order'] = 'sort_breakdown_click_throughs desc';
            $mysql['order'] = 'ORDER BY `click_out` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_click_throughs desc') {
            $html['sort_breakdown_click_throughs_order'] = 'sort_breakdown_click_throughs asc';
            $mysql['order'] = 'ORDER BY `click_out` ASC';
        }
        
        $html['sort_breakdown_ctr_order'] = 'sort_breakdown_ctr asc';
        if ($_POST['order'] == 'sort_breakdown_ctr asc') {
            $html['sort_breakdown_ctr_order'] = 'sort_breakdown_ctr desc';
            $mysql['order'] = 'ORDER BY `ctr` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_ctr desc') {
            $html['sort_breakdown_ctr_order'] = 'sort_breakdown_ctr asc';
            $mysql['order'] = 'ORDER BY `ctr` ASC';
        }
        
        $html['sort_breakdown_leads_order'] = 'sort_breakdown_leads asc';
        if ($_POST['order'] == 'sort_breakdown_leads asc') {
            $html['sort_breakdown_leads_order'] = 'sort_breakdown_leads desc';
            $mysql['order'] = 'ORDER BY `leads` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_leads desc') {
            $html['sort_breakdown_leads_order'] = 'sort_breakdown_leads asc';
            $mysql['order'] = 'ORDER BY `leads` ASC';
        }
        
        $html['sort_breakdown_su_ratio_order'] = 'sort_breakdown_su_ratio asc';
        if ($_POST['order'] == 'sort_breakdown_su_ratio asc') {
            $html['sort_breakdown_su_ratio_order'] = 'sort_breakdown_su_ratio desc';
            $mysql['order'] = 'ORDER BY `su` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_su_ratio desc') {
            $html['sort_breakdown_su_ratio_order'] = 'sort_breakdown_su_ratio asc';
            $mysql['order'] = 'ORDER BY `su` ASC';
        }
        
        $html['sort_breakdown_payout_order'] = 'sort_breakdown_payout asc';
        if ($_POST['order'] == 'sort_breakdown_payout asc') {
            $html['sort_breakdown_payout_order'] = 'sort_breakdown_payout desc';
            $mysql['order'] = 'ORDER BY `payout` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_payout desc') {
            $html['sort_breakdown_payout_order'] = 'sort_breakdown_payout asc';
            $mysql['order'] = 'ORDER BY `payout` ASC';
        }
        
        $html['sort_breakdown_epc_order'] = 'sort_breakdown_epc asc';
        if ($_POST['order'] == 'sort_breakdown_epc asc') {
            $html['sort_breakdown_epc_order'] = 'sort_breakdown_epc desc';
            $mysql['order'] = 'ORDER BY `epc` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_epc desc') {
            $html['sort_breakdown_epc_order'] = 'sort_breakdown_epc asc';
            $mysql['order'] = 'ORDER BY `epc` ASC';
        }
        
        $html['sort_breakdown_cpc_order'] = 'sort_breakdown_cpc asc';
        if ($_POST['order'] == 'sort_breakdown_cpc asc') {
            $html['sort_breakdown_cpc_order'] = 'sort_breakdown_cpc desc';
            $mysql['order'] = 'ORDER BY `cpc` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_cpc desc') {
            $html['sort_breakdown_cpc_order'] = 'sort_breakdown_cpc asc';
            $mysql['order'] = 'ORDER BY `cpc` ASC';
        }
        
        $html['sort_breakdown_income_order'] = 'sort_breakdown_income asc';
        if ($_POST['order'] == 'sort_breakdown_income asc') {
            $html['sort_breakdown_income_order'] = 'sort_breakdown_income desc';
            $mysql['order'] = 'ORDER BY `income` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_income desc') {
            $html['sort_breakdown_income_order'] = 'sort_breakdown_income asc';
            $mysql['order'] = 'ORDER BY `income` ASC';
        }
        
        $html['sort_breakdown_cost_order'] = 'sort_breakdown_cost asc';
        if ($_POST['order'] == 'sort_breakdown_cost asc') {
            $html['sort_breakdown_cost_order'] = 'sort_breakdown_cost desc';
            $mysql['order'] = 'ORDER BY `cost` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_cost desc') {
            $html['sort_breakdown_cost_order'] = 'sort_breakdown_cost asc';
            $mysql['order'] = 'ORDER BY `cost` ASC';
        }
        
        $html['sort_breakdown_net_order'] = 'sort_breakdown_net asc';
        if ($_POST['order'] == 'sort_breakdown_net asc') {
            $html['sort_breakdown_net_order'] = 'sort_breakdown_net desc';
            $mysql['order'] = 'ORDER BY `net` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_net desc') {
            $html['sort_breakdown_net_order'] = 'sort_breakdown_net asc';
            $mysql['order'] = 'ORDER BY `net` ASC';
        }
        
        $html['sort_breakdown_roi_order'] = 'sort_breakdown_roi asc';
        if ($_POST['order'] == 'sort_breakdown_roi asc') {
            $html['sort_breakdown_roi_order'] = 'sort_breakdown_roi desc';
            $mysql['order'] = 'ORDER BY `roi` DESC';
        } elseif ($_POST['order'] == 'sort_breakdown_roi desc') {
            $html['sort_breakdown_roi_order'] = 'sort_breakdown_roi asc';
            $mysql['order'] = 'ORDER BY `roi` ASC';
        }
        
        if (empty($mysql['order'])) {
            $mysql['order'] = ' ORDER BY leads DESC';
        }
        return ($mysql['order']);
    }
    
    // dirty hours by clicks id: This function marks the hour range that the click happened in for updating reports
    function setDirtyHour($click_id)
    {
        global $ip_address,$db, $dbGlobalLink;
        if (! isset($click_id) || $click_id == '') { // if not find the list clicks id of the ip within a 30 day range
            $mysql['user_id'] = 1;
            $mysql['ip_address'] = $db->real_escape_string($ip_address->address);
            
            if($inet6_ntoa == '' && $ip_address->type == 'ipv6'){
                $mysql['ip_address'] = inet6_aton($mysql['ip_address']); //encode for db check
            }
            
            $daysago = time() - 86400; // 24 hours
            $click_sql1 = 'SELECT  202_clicks.click_id
                           FROM            202_clicks
                           LEFT JOIN       202_clicks_advance USING (click_id)
                           LEFT JOIN       202_ips USING (ip_id)
                           LEFT JOIN       202_ips_v6 ON (202_ips_v6.ip_id = 202_ips.ip_address COLLATE utf8mb4_general_ci)
                           WHERE           IFNULL('.$inet6_ntoa.'(202_ips_v6.ip_address),202_ips.ip_address)="'.$mysql['ip_address'].'"
                           AND             202_clicks.user_id="' . $mysql['user_id'] . '"
                           AND             202_clicks.click_time >= "' . $daysago . '"
                           ORDER BY        202_clicks.click_id DESC
                           LIMIT           1';
            
            $click_result1 = $db->query($click_sql1) or record_mysql_error($click_sql1);
            $click_row1 = $click_result1->fetch_assoc();
            // empy $mysql array
            unset($mysql);
            $mysql['click_id'] = $db->real_escape_string($click_row1['click_id']);
            $click_id = $mysql['click_id'];
            // $mysql['ppc_account_id'] = $db->real_escape_string($click_row1['ppc_account_id']);
        }
        
        if (! isset($click_id) || $click_id == '') {
            return false;
        }
        
        $dsql = " insert into 202_dataengine(user_id,
click_id,
click_time,
ppc_network_id,
ppc_account_id,
aff_network_id,
aff_campaign_id,
landing_page_id,
keyword_id,
utm_source_id,
utm_medium_id,
utm_campaign_id,
utm_term_id,
utm_content_id,
text_ad_id,
click_referer_site_url_id,
country_id,
region_id,
city_id,
isp_id,
browser_id,
device_id,
platform_id,
ip_id,
c1_id,
c2_id,
c3_id,
c4_id,
variable_set_id,
rotator_id,
rule_id,
rule_redirect_id,
click_lead,
click_filtered,
click_bot,
click_alp,
clicks,
click_out,
leads,
payout,
income,
cost)  
    SELECT 
2c.user_id,
2c.click_id,
2c.click_time,
2pn.ppc_network_id, 
2c.ppc_account_id,
2an.aff_network_id,
2ac.aff_campaign_id,
2c.landing_page_id,
2k.keyword_id,
2gg.utm_source_id,
2gg.utm_medium_id,
2gg.utm_campaign_id,
2gg.utm_term_id,
2gg.utm_content_id,
2ta.text_ad_id,
2cs.click_referer_site_url_id,
2cy.country_id,
2rg.region_id,
2ci.city_id,
2is.isp_id,
2b.browser_id,
2dm.device_id,
2p.platform_id,
2ca.ip_id,
2tc1.c1_id,
2tc2.c2_id,
2tc3.c3_id,
2tc4.c4_id,
2cv.variable_set_id,
2rc.rotator_id,
2rc.rule_id,
2rc.rule_redirect_id,
2c.`click_lead`,
2c.`click_filtered`,
2c.`click_bot`,
2c.`click_alp`, 
1 AS clicks, 
2cr.click_out AS click_out, 
2c.click_lead AS leads, 
2c.click_payout AS payout, 
IF (2c.click_lead>0,2c.click_payout,0) AS income, 
2c.click_cpc AS cost 
FROM 202_clicks AS 2c 
LEFT OUTER JOIN 202_clicks_record AS 2cr ON (2c.click_id = 2cr.click_id) 
LEFT OUTER JOIN 202_aff_campaigns AS 2ac ON (2c.aff_campaign_id = 2ac.aff_campaign_id) 
LEFT OUTER JOIN 202_clicks_advance AS 2ca ON (2c.click_id = 2ca.click_id) 
LEFT OUTER JOIN 202_browsers AS 2b ON (2ca.browser_id = 2b.browser_id) 
LEFT OUTER JOIN 202_platforms AS 2p ON (2ca.platform_id = 2p.platform_id) 
LEFT OUTER JOIN 202_aff_networks AS 2an ON (2ac.aff_network_id = 2an.aff_network_id) 
LEFT OUTER JOIN 202_ppc_accounts AS 2pa ON (2c.ppc_account_id = 2pa.ppc_account_id) 
LEFT OUTER JOIN 202_ppc_networks AS 2pn ON (2pa.ppc_network_id = 2pn.ppc_network_id)
LEFT OUTER JOIN 202_keywords AS 2k ON (2ca.keyword_id = 2k.keyword_id)
LEFT OUTER JOIN 202_google AS 2gg ON (2c.click_id = 2gg.click_id)
LEFT OUTER JOIN 202_landing_pages AS 2lp ON (2c.landing_page_id = 2lp.landing_page_id)
LEFT OUTER JOIN 202_text_ads AS 2ta ON (2ca.text_ad_id = 2ta.text_ad_id)
LEFT OUTER JOIN 202_clicks_site AS 2cs ON (2c.click_id = 2cs.click_id)
LEFT OUTER JOIN 202_clicks_tracking AS 2ct ON (2c.click_id = 2ct.click_id)
LEFT OUTER JOIN 202_site_urls AS 2suf ON (2cs.click_referer_site_url_id = 2suf.site_url_id) 
LEFT OUTER JOIN 202_locations_country AS 2cy ON (2ca.country_id = 2cy.country_id) 
LEFT OUTER JOIN 202_locations_region AS 2rg ON (2ca.region_id = 2rg.region_id) 
LEFT OUTER JOIN 202_locations_city AS 2ci ON (2ca.city_id = 2ci.city_id)
LEFT OUTER JOIN 202_locations_isp AS 2is ON (2ca.isp_id = 2is.isp_id) 
LEFT OUTER JOIN 202_device_models AS 2dm ON (2ca.device_id = 2dm.device_id)
LEFT OUTER JOIN 202_ips AS 2i ON (2ca.ip_id = 2i.ip_id)
LEFT OUTER JOIN 202_tracking_c1 AS 2tc1 ON (2ct.c1_id = 2tc1.c1_id) 
LEFT OUTER JOIN 202_tracking_c2 AS 2tc2 ON (2ct.c2_id = 2tc2.c2_id) 
LEFT OUTER JOIN 202_tracking_c3 AS 2tc3 ON (2ct.c3_id = 2tc3.c3_id) 
LEFT OUTER JOIN 202_tracking_c4 AS 2tc4 ON (2ct.c4_id = 2tc4.c4_id)
LEFT OUTER JOIN 202_clicks_variable AS 2cv ON (2c.click_id = 2cv.click_id)
LEFT OUTER JOIN 202_clicks_rotator AS 2rc ON (2c.click_id = 2rc.click_id)
WHERE 2c.click_id=" . $click_id . "
on duplicate key update 
click_lead=values(click_lead),
click_bot=values(click_bot),
click_out=values(click_out),
click_filtered=values(click_filtered),    
leads=values(leads),
payout=values(payout),
income=values(income),
cost=values(cost),
rotator_id=values(rotator_id),
rule_id=values(rule_id),
rule_redirect_id=values(rule_redirect_id),
aff_campaign_id=values(aff_campaign_id),
aff_network_id=values(aff_network_id)";
        
        $result = $db->query($dsql);
    }

$result = self::$db->query($sql);
            
            // $update_sql = $delayed_row['delayed_sql'];
            // echo $update_sql."<br>";
            // $update_result = _mysql_query($update_sql);
            // echo $update_result;

$sql = "
			UPDATE
			202_dirty_hours
			set deleted='1'
        			where id=
        			    " . $delayed_row['id'];

$result = self::$db->query($sql);
            flush();
        }
        
        $sql = "
			UPDATE
            202_dirty_hours
            set deleted='1'
            where processed=1";

// $result = _mysql_query($sql);
        
        $sql = "	DELETE
            FROM 202_dirty_hours
            where deleted=1";

$result = self::$db->query($sql);
        
        $time_end = microtime(true);
        
        // dividing with 60 will give the execution time in minutes other wise seconds
        $execution_time = ($time_end - $time_start);
        
        // execution time of the script
        // echo '<b>Total Execution Time:</b> ' . $execution_time . ' Secs';
    }

function getSummary($start, $end, $params, $user_id = 1, $upgrade = false, $new = false)
    {
        global $db, $click_filtered, $dbGlobalLink;
        $mysql['from'] = $db->real_escape_string($start); // mysqli_real_escape_string($dbGlobalLink,$start);
        $mysql['to'] = $db->real_escape_string($end);
        $mysql['user_id'] = $db->real_escape_string($user_id);
        
        if ($upgrade) {
            $sql = "UPDATE 202_dataengine_job SET processing = '1' WHERE time_from ='" . $mysql['from'] . "' AND time_to = '" . $mysql['to'] . "'";
            $db->query($sql);
        }

if ($new == true) {
            $table = '202_dataengine_new';
        } else {
            $table = '202_dataengine';
        }
        
        if (1) {
            
            // insert into dataengine
            $query = "
 insert into " . $table . "(user_id,
click_id,
click_time,
ppc_network_id,
ppc_account_id,
aff_network_id,
aff_campaign_id,
landing_page_id,
keyword_id,
utm_medium_id,
utm_source_id,
utm_campaign_id,
utm_term_id,
utm_content_id,
text_ad_id,
click_referer_site_url_id,
country_id,
region_id,
city_id,
isp_id,
browser_id,
device_id,
platform_id,
ip_id,
c1_id,
c2_id,
c3_id,
c4_id,
variable_set_id,
rotator_id,
rule_id,
rule_redirect_id,
click_lead,
click_filtered,
click_bot,
click_alp,
clicks,
click_out,
leads,
payout,
income,
cost)  
    SELECT 
2c.user_id,
2c.click_id,
2c.click_time,
2pn.ppc_network_id, 
2c.ppc_account_id,
2an.aff_network_id,
2ac.aff_campaign_id,
2c.landing_page_id,
2k.keyword_id,
2gg.utm_source_id,
2gg.utm_medium_id,
2gg.utm_campaign_id,
2gg.utm_term_id,
2gg.utm_content_id,
2ta.text_ad_id,
2cs.click_referer_site_url_id,
2cy.country_id,
2rg.region_id,
2ci.city_id,
2is.isp_id,
2b.browser_id,
2dm.device_id,
2p.platform_id,
2ca.ip_id,
2tc1.c1_id,
2tc2.c2_id,
2tc3.c3_id,
2tc4.c4_id,
2cv.variable_set_id,
2rc.rotator_id,
2rc.rule_id,
2rc.rule_redirect_id,
2c.`click_lead`,
2c.`click_filtered`,
2c.`click_bot`,
2c.`click_alp`, 
1 AS clicks, 
2cr.click_out AS click_out, 
2c.click_lead AS leads, 
2c.click_payout AS payout, 
IF (2c.click_lead>0,2c.click_payout,0) AS income, 
2c.click_cpc AS cost 
FROM 202_clicks AS 2c 
LEFT OUTER JOIN 202_clicks_record AS 2cr ON (2c.click_id = 2cr.click_id) 
LEFT OUTER JOIN 202_aff_campaigns AS 2ac ON (2c.aff_campaign_id = 2ac.aff_campaign_id) 
LEFT OUTER JOIN 202_clicks_advance AS 2ca ON (2c.click_id = 2ca.click_id) 
LEFT OUTER JOIN 202_browsers AS 2b ON (2ca.browser_id = 2b.browser_id) 
LEFT OUTER JOIN 202_platforms AS 2p ON (2ca.platform_id = 2p.platform_id) 
LEFT OUTER JOIN 202_aff_networks AS 2an ON (2ac.aff_network_id = 2an.aff_network_id) 
LEFT OUTER JOIN 202_ppc_accounts AS 2pa ON (2c.ppc_account_id = 2pa.ppc_account_id) 
LEFT OUTER JOIN 202_ppc_networks AS 2pn ON (2pa.ppc_network_id = 2pn.ppc_network_id)
LEFT OUTER JOIN 202_keywords AS 2k ON (2ca.keyword_id = 2k.keyword_id)
LEFT OUTER JOIN 202_google AS 2gg ON (2c.click_id = 2gg.click_id)
LEFT OUTER JOIN 202_landing_pages AS 2lp ON (2c.landing_page_id = 2lp.landing_page_id)
LEFT OUTER JOIN 202_text_ads AS 2ta ON (2ca.text_ad_id = 2ta.text_ad_id)
LEFT OUTER JOIN 202_clicks_site AS 2cs ON (2c.click_id = 2cs.click_id)
LEFT OUTER JOIN 202_clicks_tracking AS 2ct ON (2c.click_id = 2ct.click_id)
LEFT OUTER JOIN 202_site_urls AS 2suf ON (2cs.click_referer_site_url_id = 2suf.site_url_id) 
LEFT OUTER JOIN 202_locations_country AS 2cy ON (2ca.country_id = 2cy.country_id) 
LEFT OUTER JOIN 202_locations_region AS 2rg ON (2ca.region_id = 2rg.region_id) 
LEFT OUTER JOIN 202_locations_city AS 2ci ON (2ca.city_id = 2ci.city_id)
LEFT OUTER JOIN 202_locations_isp AS 2is ON (2ca.isp_id = 2is.isp_id) 
LEFT OUTER JOIN 202_device_models AS 2dm ON (2ca.device_id = 2dm.device_id)
LEFT OUTER JOIN 202_ips AS 2i ON (2ca.ip_id = 2i.ip_id)
LEFT OUTER JOIN 202_tracking_c1 AS 2tc1 ON (2ct.c1_id = 2tc1.c1_id) 
LEFT OUTER JOIN 202_tracking_c2 AS 2tc2 ON (2ct.c2_id = 2tc2.c2_id) 
LEFT OUTER JOIN 202_tracking_c3 AS 2tc3 ON (2ct.c3_id = 2tc3.c3_id) 
LEFT OUTER JOIN 202_tracking_c4 AS 2tc4 ON (2ct.c4_id = 2tc4.c4_id)
LEFT OUTER JOIN 202_clicks_variable AS 2cv ON (2c.click_id = 2cv.click_id)
LEFT OUTER JOIN 202_clicks_rotator AS 2rc ON (2c.click_id = 2rc.click_id)
WHERE 2c.click_time >= " . $mysql['from'] . "
AND 2c.click_time <= " . $mysql['to'] . " " . $params . "
on duplicate key update 
click_lead=values(click_lead),
click_bot=values(click_bot),
click_out=values(click_out),
click_filtered=values(click_filtered),    
leads=values(leads),
payout=values(payout),
income=values(income),
cost=values(cost),
rotator_id=values(rotator_id),
rule_id=values(rule_id),
rule_redirect_id=values(rule_redirect_id),
aff_campaign_id=values(aff_campaign_id),
aff_network_id=values(aff_network_id)";
            // die($query);
        }
        $this->doQuery($query, $mysql['from'], $mysql['to'], $upgrade, $new);
        return $query . "<br><br>";
    }

function doQuery($query, $from, $to, $upgrade = false, $new = false)
    {
        global $db, $dbGlobalLink;
        $info_result = $result = $db->query($query) or die($db->error . '<br/><br/>' . $query);
        // if (mysqli_num_rows($info_result)>0)
        // if ($info_result->num_rows>0)
        // die(($info_result->num_rows));
        // $this->doSummary($info_result, $from, $to, $upgrade, $new);
    }

function processClickUpgrade()
    {
        global $db, $dbGlobalLink;
        $dbGlobalLink = $db;

if (function_exists('curl_version')) { // if curel is installed use the multiget method
            include_once (substr(dirname(__FILE__), 0, - 10) . '/202-cronjobs/process_dataengine_job.php');
        } else { // loop daily
            $query = "SELECT * FROM 202_dataengine_job WHERE processed = '0'";
            $result = $db->query($query);
            $row = $result->fetch_assoc();
            
            if ($result->num_rows) {
                if (! $row['processing']) {
                    $snippet = "AND 2c.user_id = " . 1;
                    
                    $mysql['click_time_from'] = $db->real_escape_string($row['time_from']);
                    $mysql['click_time_to'] = $db->real_escape_string($row['time_to']);
                    
                    $this->getSummary($mysql['click_time_from'], $mysql['click_time_to'], $snippet, 1, true);
                }
            }
        }
    }

class DisplayData
{

private static $db;

echo ' <tr>
                   <td colspan="4" style="text-align:left; padding-left:10px">' . $source_name . '</td>
                   <td>' . $ppc_account['clicks'] . '</td>
                       
                        <td>' . $ppc_account['click_out'] . '</td>   
                        <td>' . $ppc_account['ctr'] . '</td>
                        <td>' . $ppc_account['leads'] . '</td>
                        <td>' . $ppc_account['su_ratio'] . '</td>
                        <td>' . $ppc_account['payout'] . '</td> 
                        <td>' . $ppc_account['epc'] . '</td>
                        <td>' . $ppc_account['cpc'] . '</td>
                        <td><span class="label label-info">' . $ppc_account['income'] . '</span></td>
                        <td><span class="label label-info">' . $ppc_account['cost_wrapper'] . '</span></td>
                        <td> <span class="label label-' . $netStyle . '">' . $ppc_account['net'] . '</span></td>
                        <td> <span class="label label-' . $roiStyle . '">' . $ppc_account['roi'] . '</span></td>    
                        
                </tr> ';
                }
                
                if (! $userObj->hasPermission("access_to_campaign_data") && !$_SESSION['publisher']) {
                    $campaign['total_clicks'] = '?';
                    $campaign['total_click_out'] = '?';
                    $campaign['total_leads'] = '?';
                    $campaign['total_income'] = '?';
                    $campaign['cost_wrapper'] = '?';
                    $campaign['net'] = '?';
                } else {
                    $campaign['cost_wrapper'] = '(' . $campaign['total_cost'] . ')';
                }
                
                echo '<tr style="background-color: #F8F8F8;" id="totals" class="no-sort">
                    <td colspan="4" style="text-align:left; padding-left:10px;"><strong>Totals for report</strong></td>
                    <td><strong>' . $campaign['total_clicks'] . '</strong></td>
                    <td><strong>' . $campaign['total_click_out'] . '</strong></td>
                    <td><strong>' . $campaign['total_ctr'] . '</strong></td>
                    <td><strong>' . $campaign['total_leads'] . '</strong></td>
                    <td><strong>' . $campaign['total_su_ratio'] . '</strong></td>
                    <td><strong>' . $campaign['total_payout'] . '</strong></td>
                    <td><strong>' . $campaign['total_epc'] . '</strong></td>
                    <td><strong>' . $campaign['total_cpc'] . '</strong></td>
                    <td><strong>' . $campaign['total_income'] . '</strong></td>
                    <td><strong>' . $campaign['cost_wrapper'] . '</strong></td>
                    <td><strong><span class="label label-' . $totalNetStyle . '">' . $campaign['total_net'] . '</span></strong></td>
                    <td><strong><span class="label label-' . $totalRoiStyle . '">' . $campaign['total_roi'] . '</span></strong></td>
                </tr>
                </tbody>
            </table> ';
            }
        }
    }

function convertToNumber($val)
    {
        $moneyStuff = array(
            "$",
            ","
        );
        
        return str_replace($moneyStuff, "", $val);
    }

class UserPrefs
{

private static $userPref = array();

static $mysql = Array();

private static $db;

public static function getPref($pref)
    {
        // print_r(self::$userPref);
        return self::$userPref[$pref];
    }
}

Editing: class-dataengine.php

Upload Files

Create New

Change Permissions

Change Date

Rename Item

Confirm Delete