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; $net = $income - $cost; //roi $roi = calculate_roi($income, $cost); //html escape vars $mysql['clicks'] = DB::quote($clicks); $mysql['leads'] = DB::quote($leads); $mysql['conv'] = DB::quote($conv); $mysql['epc'] = DB::quote($epc); $mysql['avg_cpc'] = DB::quote($avg_cpc); $mysql['income'] = DB::quote($income); $mysql['cost'] = DB::quote($cost); $mysql['net'] = DB::quote($net); $mysql['roi'] = DB::quote($roi); $mysql['payout'] = DB::quote($payout); //insert chart $sort_breakdown_sql = "\n\t\t\t\tupdate\n\t\t\t\t\tbt_c_statcache\n\t\t\t\tSET\n\t\t\t\t\tclicks='" . $mysql['clicks'] . "',\n\t\t\t\t\tleads='" . $mysql['leads'] . "',\n\t\t\t\t\tconv='" . $mysql['conv'] . "',\n\t\t\t\t\tpayout='" . $mysql['payout'] . "',\n\t\t\t\t\tepc='" . $mysql['epc'] . "',\n\t\t\t\t\tcpc='" . $mysql['avg_cpc'] . "',\n\t\t\t\t\tincome='" . $mysql['income'] . "',\n\t\t\t\t\tcost='" . $mysql['cost'] . "',\n\t\t\t\t\tnet='" . $mysql['net'] . "',\n\t\t\t\t\troi='" . $mysql['roi'] . "'\n\t\t\t\t\t\n\t\t\t\t\twhere\n\t\t\t\t\t\n\t\t\t\t\ttime_from='" . $mysql['date'] . "' and\n\t\t\t\t\tuser_id='" . $mysql['user_id'] . "' and\n\t\t\t\t\ttype='breakdown'"; DB::query($sort_breakdown_sql); //($sort_breakdown_sql); } }
/** *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; }