function userPrefDate()
{
    $time = grab_timeframe();
    $date['from_date'] = date('Y-m-d', $time['from']);
    $date['to_date'] = date('Y-m-d', $time['to']);
    return $date;
}

 public function getOverviewData()
 {
     $time = grab_timeframe();
     $user_id = DB::quote(getUserID());
     $start = DB::quote($time['from']);
     $end = DB::quote($time['to']);
     /** GET SPENDING **/
     $spend_from = date('Y-m-d', $time['from']);
     $spend_to = date('Y-m-d', $time['to']);
     $sql = "select sum(amount) as cost, campaign_id from bt_u_spending \n\t\t\t\twhere date >= '{$spend_from}' and date <= '{$spend_to}' ";
     $sql .= getSpendingReportFilters('bt_u_spending', getReportOptionsForPage('overview/overview'));
     $sql .= " group by campaign_id";
     $spends = DB::getRows($sql, 'campaign_id');
     /** END SPENDING **/
     //Erase old cache
     DB::query("delete from bt_c_statcache where type='overview' and user_id='{$user_id}'");
     /** GET CLICK DATA **/
     $sql = "insert into bt_c_statcache select null,\n\t\t\t" . $user_id . " as user_id, 0 as time_from, 0 as time_to,'overview' as type, \n\t\t\tcount(click.click_id) as clicks,\n\t\t\t0 as click_throughs, 0 as click_through_rates,\n\t\t\tsum(click.lead) as leads, \n\t\t\t0 as conv, \n\t\t\t(SUM(click.payout*click.lead) / sum(click.lead)) as payout, \n\t\t\t0 as epc, 0 as cpc, \n\t\t\tSUM(click.payout*click.lead) AS income,\n\t\t\t0 as cost, 0 as net, 0 as roi,\n\t\t\tclick.campaign_id as meta1, camp.type as meta2, click.offer_id as meta3, null as meta4\n\t\t\t\n\t\t\tfrom\n\t\t\t";
     $sql .= getReportFilters('overview/overview');
     $sql .= "group by click.campaign_id, click.offer_id\n\t\t\torder by null";
     DB::query($sql);
     /** END CLICK DATA **/
     /** GET ALL CAMPAIGNS TO FILL ZEROES, AND CALCULATE TOP-LEVEL CAMPAIGNS **/
     $sql = "select meta1 from bt_c_statcache where user_id='{$user_id}' and type='overview' and meta1>0 group by meta1";
     $conditions = array();
     $ts_id = BTAuth::user()->getPref('traffic_source_id');
     if ($ts_id) {
         $conditions['traffic_source_id'] = $ts_id;
     }
     $campaigns = CampaignModel::model()->getRows(array('conditions' => $conditions));
     $existing_rows = DB::getRows($sql, 'meta1');
     //insert blank rows wheren eeded
     foreach ($campaigns as $campaign) {
         if (isset($existing_rows[$campaign->id()])) {
             continue;
         }
         if (BTAuth::user()->getPref('campaign_type') == 'lp') {
             if ($campaign->type != 1) {
                 continue;
             }
         } else {
             if (BTAuth::user()->getPref('campaign_type') == 'direct') {
                 if ($campaign->type != 2) {
                     continue;
                 }
             }
         }
         DB::query("insert into bt_c_statcache set user_id='{$user_id}', type='overview', meta1='" . DB::quote($campaign->id()) . "'");
     }
     //calculate top-level stats
     foreach ($campaigns as $campaign) {
         DB::query("update bt_c_statcache c\n\t\t\t\t\n\t\t\t\tleft join (select \n\t\t\t\t\t\tmeta1, \n\t\t\t\t\t\tsum(s.clicks) as clicks,\n\t\t\t\t\t\tsum(s.click_throughs) as click_throughs,\n\t\t\t\t\t\tsum(s.leads) as leads,\n\t\t\t\t\t\t(sum(s.income) / sum(s.clicks)) as payout,\n\t\t\t\t\t\tsum(s.income) as income\n\t\t\t\t\t\t\n\t\t\t\t\t\tfrom bt_c_statcache s where s.meta1=" . $campaign->id() . "\n\t\t\t\t\t  ) data\n\t\t\t\ton data.meta1 > 0\n\t\t\t\t\n\t\t\t\tset\n\t\t\t\t\tc.clicks=data.clicks, c.click_throughs=data.click_throughs, c.click_through_rates=(c.click_throughs / c.clicks), c.leads=data.leads, c.conv=(c.leads / c.clicks), c.payout=data.payout, c.income=data.income\n\t\t\t\t\t\n\t\t\t\t\twhere c.meta1=" . $campaign->id() . " and (c.meta3 is null or c.meta3=0)");
     }
     /** END TOP LEVEL LP **/
     $this->calculateCostsPerCampaign($spends);
 }

<?php

include_once $_SERVER['DOCUMENT_ROOT'] . '/202-config/connect.php';
AUTH::require_user();
//set the timezone for the user, for entering their dates.
AUTH::set_timezone($_SESSION['user_timezone']);
//grab user time range preference
$time = grab_timeframe();
$mysql['to'] = $db->real_escape_string($time['to']);
$mysql['from'] = $db->real_escape_string($time['from']);
//show real or filtered clicks
$mysql['user_id'] = $db->real_escape_string($_SESSION['user_id']);
$user_sql = "SELECT user_pref_breakdown, user_pref_show, user_cpc_or_cpv FROM 202_users_pref WHERE user_id=" . $mysql['user_id'];
$user_result = _mysqli_query($user_sql, $dbGlobalLink);
//($user_sql);
$user_row = $user_result->fetch_assoc();
$breakdown = $user_row['user_pref_breakdown'];
if ($user_row['user_pref_show'] == 'all') {
    $click_flitered = '';
}
if ($user_row['user_pref_show'] == 'real') {
    $click_filtered = " AND click_filtered='0' ";
}
if ($user_row['user_pref_show'] == 'filtered') {
    $click_filtered = " AND click_filtered='1' ";
}
if ($user_row['user_pref_show'] == 'filtered_bot') {
    $click_filtered = " AND click_bot='1' ";
}
if ($user_row['user_pref_show'] == 'leads') {
    $click_filtered = " AND click_lead='1' ";

function runWeekly($user_pref)
{
    $database = DB::getInstance();
    $db = $database->getConnection();
    //grab time
    $time = grab_timeframe();
    //get breakdown pref
    $mysql['user_id'] = $db->real_escape_string($_SESSION['user_id']);
    $user_sql = "SELECT \tuser_time_register,\n\t\t\t\t\t\t\t\tuser_pref_breakdown,\n\t\t\t\t\t\t\t\tuser_pref_chart,\n\t\t\t\t\t\t\t\tuser_pref_show\n\t\t\t\t\t FROM   202_users LEFT JOIN 202_users_pref USING (user_id) \n\t\t\t\t\t WHERE  202_users.user_id='" . $mysql['user_id'] . "'";
    $user_result = _mysqli_query($user_sql);
    //($user_sql);
    $user_row = $user_result->fetch_assoc();
    if ($user_row['user_pref_show'] == 'all') {
        $click_flitered = '';
    }
    if ($user_row['user_pref_show'] == 'real') {
        $click_filtered = " AND click_filtered='0' ";
    }
    if ($user_row['user_pref_show'] == 'filtered') {
        $click_filtered = " AND click_filtered='1' ";
    }
    if ($user_row['user_pref_show'] == 'filtered_bot') {
        $click_filtered = " AND click_bot='1' ";
    }
    if ($user_row['user_pref_show'] == 'leads') {
        $click_filtered = " AND click_lead='1' ";
    }
    //breakdown should be hour, day, month, or year.
    $breakdown = 'day';
    $pref_chart = $user_row['user_pref_chart'];
    //first delete old report
    $breakdown_sql = "\n\t\t\tDELETE\n\t\t\tFROM 202_sort_breakdowns\n\t\t\tWHERE user_id='" . $mysql['user_id'] . "'\n\t\t";
    $breakdown_result = _mysqli_query($breakdown_sql);
    //($breakdown_sql);
    //find where to start from.
    $start = $time['from'];
    $end = $time['to'];
    //make sure the start isn't past this users registration time, and likewise, make sure END isn't past today, else theses will try to grab reports for dates that do not exists slowing down mysql doing reports for nothing.
    if ($user_row['user_time_register'] > $start) {
        $start = $user_row['user_time_register'];
    }
    if (time() < $end) {
        $end = time();
    }
    $x = 0;
    while ($end > $start) {
        $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;
        $end1 = $end + 86400;
        $day = date('D', $end1);
        switch ($day) {
            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'] = $db->real_escape_string($from);
        $mysql['to'] = $db->real_escape_string($to);
        //build query
        $command = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*) AS clicks,\n\t\t\t\tSUM(2cr.click_out) AS click_throughs,\n\t\t\t\tSUM(click_cpc) AS cost,\n\t\t\t\tSUM(2c.click_lead) AS leads,\n\t\t\t\tSUM(2c.click_payout*2c.click_lead) AS income\n\t\t\tFROM\n\t\t\t\t202_clicks AS 2c\n\t\t\t\tLEFT JOIN 202_clicks_record AS 2cr ON 2cr.click_id = 2c.click_id\n\t\t";
        $db_table = "2c";
        $pref_time = false;
        if ($user_pref == true) {
            $pref_adv = true;
            $command = $command . "\n\t\t\t\tLEFT JOIN 202_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id)\n\t\t\t\tLEFT JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)\n\t\t\t";
        } else {
            $pref_adv = false;
        }
        $command = $command . "\n\t\t\tLEFT JOIN 202_aff_campaigns AS 2ac ON (2c.aff_campaign_id = 2ac.aff_campaign_id)\n\t\t\tLEFT JOIN 202_aff_networks AS 2an ON (2an.aff_network_id = 2ac.aff_network_id)\n\t\t";
        $pref_show = false;
        $pref_order = "\n\t\t\tAND (2c.click_alp = '1' OR (2ac.aff_campaign_deleted='0' AND 2an.aff_network_deleted='0'))\n\t\t\t{$click_filtered}\n\t\t\tAND 2c.click_time > " . $mysql['from'] . "\n\t\t\tAND 2c.click_time <= " . $mysql['to'] . "\n\t\t";
        $offset = false;
        $pref_limit = false;
        $count = false;
        $query = query($command, $db_table, $pref_time, $pref_adv, $pref_show, $pref_order, $offset, $pref_limit, $count);
        $click_sql = $query['click_sql'];
        $click_result = _mysqli_query($click_sql);
        //($click_sql);
        $click_row = $click_result->fetch_assoc();
        //for AVG Payouts
        $payouts_sql = "\n\t\t\tSELECT\n\t\t\t\tSUM(2c.click_payout) AS payout\n\t\t\tFROM 202_clicks AS 2c\n\t\t\tWHERE 2c.click_lead='1'\n\t\t\tAND 2c.click_time > " . $mysql['from'] . "\n\t\t\tAND 2c.click_time <= " . $mysql['to'] . "\n\t\t";
        $payout_result = _mysqli_query($payouts_sql);
//.........这里部分代码省略.........

function runBreakdown($user_pref)
{
    //grab time
    $time = grab_timeframe();
    //get breakdown pref
    $mysql['user_id'] = DB::quote(getUserID());
    $filtered = getFilteredCondition();
    //breakdown should be hour, day, month, or year.
    $breakdown = BTAuth::user()->getPref('breakdown');
    //first delete old report
    $breakdown_sql = "\n\t\t\tDELETE\n\t\t\tFROM bt_c_statcache\n\t\t\tWHERE user_id='" . $mysql['user_id'] . "' and type='breakdown'\n\t\t";
    DB::query($breakdown_sql);
    //($breakdown_sql);
    //find where to start from.
    $datetype = '';
    //breakdown format
    if ($breakdown == 'day') {
        $datetype = '%Y-%m-%d';
    } else {
        if ($breakdown == 'month') {
            $datetype = '%Y-%m';
        } else {
            if ($breakdown == 'year') {
                $datetype = '%Y';
            }
        }
    }
    //BTApp::end();
    $start = $time['from'];
    $end = $time['to'];
    $spend_from = date("Y-m-d", $start);
    $spend_to = date("Y-m-d", $end);
    /****GET SPENDING****/
    $sql = "select sum(amount) as cost, FROM_UNIXTIME(UNIX_TIMESTAMP(date),'{$datetype}') as date from bt_u_spending \n\t\t\t\twhere date >= '{$spend_from}' and date <= '{$spend_to}' ";
    $sql .= getSpendingReportFilters('bt_u_spending', getReportOptionsForPage('overview/breakdown'));
    $sql .= "group by date";
    $spending_data = DB::getRows($sql, 'date');
    /****END SPENDING****/
    $offset = date('Z');
    $bulk = new DB_Bulk_Insert('bt_c_statcache', array('user_id', 'time_from', 'time_to', 'type'));
    $x = 0;
    while ($end > $start) {
        if ($breakdown == 'day') {
            $yr = date('Y', $start);
            $mo = date('m', $start);
            $dy = date('d', $start);
            $from = mktime(0, 0, 0, $mo, $dy, $yr);
            $to = mktime(23, 59, 59, $mo, $dy, $yr);
            $start = $to + 1;
        } elseif ($breakdown == 'month') {
            $yr = date('Y', $start);
            $mo = date('m', $start);
            $from = mktime(0, 0, 0, $mo, 1, $yr);
            $to = mktime(23, 59, 59, $mo + 1, 0, $yr);
            $start = $to + 1;
        } elseif ($breakdown == 'year') {
            $yr = date('Y', $start);
            $from = mktime(0, 0, 0, 1, 1, $yr);
            $to = mktime(23, 59, 59, 1, 0, $yr + 1);
            $start = $to + 1;
        }
        $bulk->insert(array($mysql['user_id'], "FROM_UNIXTIME('" . $from . "','{$datetype}')", "FROM_UNIXTIME('" . $to . "','{$datetype}')", "'breakdown'"));
    }
    $bulk->execute();
    $user_id = DB::quote(getUserID());
    $start = DB::quote($time['from']);
    $end = DB::quote($time['to']);
    $sql = "\n\t\tSELECT COUNT(*) AS clicks, (SUM(click.payout*click.lead) / sum(click.lead)) as payout, SUM(click.lead) AS leads, SUM(click.payout*click.lead) AS income, \n\t\tFROM_UNIXTIME(click.time + {$offset},'{$datetype}') as date\n\n\t\tFROM \n\t\t";
    $sql .= getReportFilters('overview/breakdown');
    $sql .= "\n\t\tgroup by date\n\t\torder by null\n\t\t";
    //echo $sql . "<br>";
    //echo $click_sql;
    $click_rows = DB::getRows($sql);
    foreach ($click_rows as $click_row) {
        //get the stats
        $clicks = 0;
        $clicks = $click_row['clicks'];
        $mysql['date'] = $click_row['date'];
        if ($clicks) {
            $cost = getArrayVar($spending_data, $click_row['date'], array('cost' => 0));
            $cost = $cost['cost'];
            $avg_cpc = calculate_cpc($clicks, $cost);
        } else {
            $avg_cpc = 0;
            $cost = 0;
        }
        //leads
        $leads = $click_row['leads'];
        //signup ratio
        $conv = calculate_conv($clicks, $leads);
        //were not using payout
        //current payout
        $payout = $click_row['payout'];
        //income
        $income = 0;
        $income = $click_row['income'];
        //grab the EPC
        $epc = calculate_epc($clicks, $income);
        //net income
        $net = 0;
//.........这里部分代码省略.........

function dayparting_data($order = '', $use_cache = false)
{
    $offset = date('Z');
    DB::query("delete from bt_c_statcache where user_id='" . DB::quote(getUserId()) . "'  and type='daypart'");
    /****GET SPENDING****/
    $time = grab_timeframe();
    $spend_from = date("Y-m-d", $time['from']);
    $spend_to = date("Y-m-d", $time['to']);
    $sql = "select sum(amount) as cost from bt_u_spending where date >= '{$spend_from}' and date <= '{$spend_to}' ";
    $sql .= getSpendingReportFilters('bt_u_spending', getReportOptionsForPage('dateparting/date'));
    $spending_amt = DB::getVar($sql);
    /****END SPENDING****/
    $bulk = new DB_Bulk_Insert('bt_c_statcache', array('user_id', 'time_from', 'time_to', 'type'));
    for ($i = 0; $i < 24; $i++) {
        $bulk->insert(array(getUserId(), $i, "0", "'daypart'"));
    }
    $bulk->execute();
    $cols = array('time_from_int', 'clicks', 'leads', 'conv', 'payout', 'epc', 'income', 'cost', 'net', 'roi');
    $sql = "select FROM_UNIXTIME(time + {$offset},'%H') as time_from, ";
    $sql .= getReportGeneralSelects() . ' from ';
    $sql .= getReportFilters('dateparting/date', '');
    $sql .= ' group by time_from ';
    $result = DB::getRows($sql);
    $total_clicks = 0;
    foreach ($result as $row) {
        $total_clicks += $row['clicks'];
    }
    $st = DB::prepare("update bt_c_statcache set clicks=?, leads=?, conv=?, payout=?, epc=?, income=?, cost=?, net=?, roi=? where user_id=? and type='daypart' and time_from=?");
    foreach ($result as $row) {
        //simple ratio.
        $cost = $row['clicks'] * $spending_amt / $total_clicks;
        $row['cost'] = round($cost, 2);
        $row['net'] = calculate_net($row['income'], $row['cost']);
        $row['roi'] = calculate_roi($row['income'], $row['cost']);
        $arr = array($row['clicks'], $row['leads'], $row['conv'], $row['payout'], $row['epc'], $row['income'], $row['cost'], $row['net'], $row['roi'], getUserID(), (int) $row['time_from']);
        $st->execute($arr);
    }
    $sql = "select *,convert(`time_from`,UNSIGNED INTEGER) as time_from_int,? as total_spend from bt_c_statcache where user_id=? and type='daypart' ";
    $sql .= getReportOrder($cols);
    $st = DB::prepare($sql);
    $st->execute(array($spending_amt, getUserID()));
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);
    //show breakdown
    $total_clicks = 0;
    $total_leads = 0;
    $total_income = 0;
    $total_cost = 0;
    $cnt = 0;
    foreach ($rows as &$row) {
        $row['time_from_int'] = dayparting_time($row['time_from_int']) . ' - ' . dayparting_time($row['time_from_int'] + 1);
        $total_clicks += $row['clicks'];
        $total_leads += $row['leads'];
        $total_income += $row['income'];
        $total_cost = $row['total_spend'];
    }
    $data_array = array();
    $data_array['time_from_int'] = 'Totals for report';
    $data_array['clicks'] = $total_clicks;
    $data_array['leads'] = $total_leads;
    $data_array['conv'] = calculate_conv($total_clicks, $total_leads);
    $data_array['payout'] = calculate_payout($total_leads, $total_income);
    $data_array['epc'] = calculate_epc($total_clicks, $total_income);
    $data_array['income'] = $total_income;
    $data_array['cost'] = $total_cost;
    $data_array['net'] = calculate_net($total_income, $total_cost);
    $data_array['roi'] = calculate_roi($total_income, $total_cost);
    $rows[] = $data_array;
    echo getDatatablesReportJson($rows, count($rows), $cols);
}

/**
 *This should technically fill in almost everything in the "where" conditions
 **/
function getReportFilters($report_page, $extra_join = '')
{
    $time = grab_timeframe();
    $start = DB::quote($time['from']);
    $end = DB::quote($time['to']);
    $option_fields = getReportOptionsForPage($report_page);
    $sql = '
		bt_s_clicks click
		LEFT JOIN bt_u_offers AS offer ON (click.offer_id = offer.offer_id)
		LEFT JOIN bt_u_aff_networks AS net ON (net.aff_network_id = offer.aff_network_id)
		LEFT JOIN bt_u_traffic_sources AS ts ON (ts.traffic_source_id = click.traffic_source_id)
		LEFT JOIN bt_u_campaigns as camp ON (camp.campaign_id=click.campaign_id)
		' . $extra_join . ' where ';
    $conds = array();
    $conds[] = " ts.deleted='0' ";
    $conds[] = " camp.deleted='0' ";
    $conds[] = " (net.deleted='0' || net.deleted is null ) ";
    $conds[] = " (offer.deleted='0' || offer.deleted is null ) ";
    if (@$option_fields['show_type']) {
        if (BTAuth::user()->getPref('campaign_type') == 'lp') {
            $conds[] = sprintf(" camp.type=1 ");
        } else {
            if (BTAuth::user()->getPref('campaign_type') == 'direct') {
                $conds[] = sprintf(" camp.type=2 ");
            }
        }
    }
    if (@$option_fields['show_traffic_source']) {
        if (BTAuth::user()->getPref('traffic_source_id')) {
            $conds[] = sprintf(" ts.traffic_source_id='%s' ", DB::quote(BTAuth::user()->getPref('traffic_source_id')));
        }
    }
    if (@$option_fields['show_campaign']) {
        if (BTAuth::user()->getPref('campaign_id')) {
            $conds[] = sprintf(" camp.campaign_id=%s ", DB::quote(BTAuth::user()->getPref('campaign_id')));
        }
    }
    $filt = getFilteredCondition();
    if ($filt) {
        $conds[] = $filt;
    }
    $conds[] = sprintf(' ((click.time >= %s) and (click.time <= %s)) ', $start, $end);
    $sql .= join(' and ', $conds);
    return $sql;
}