public function loadLastCampaignChart()
{
$limit = Input::get('limit');
$modelCampaign = new CampaignBaseModel();
$listCampaign = $modelCampaign->getCampaignRecent($limit);
$listCampaignID = array_column($listCampaign->toArray(), 'id');
$listCampaignChart = array();
$trackingSummaryModel = new TrackingSummaryBaseModel();
if (!empty($listCampaignID)) {
foreach ($listCampaignID as $campaign) {
$listCampaignChart[$campaign] = $trackingSummaryModel->getFlightChart($campaign)->toArray();
}
}
$this->data['listCampaign'] = $listCampaign;
$this->data['listCampaignChart'] = $listCampaignChart;
return View::make('campaignChart', $this->data)->render();
}
public function incTotalInventory($fid, $event)
{
$sumID = (string) $fid;
$check = $this->where(array('sum_id' => $sumID))->first();
if (empty($check)) {
$this->sum_id = $sumID;
$this->flight_id = $fid;
$this->inventory = (int) TrackingSummaryBaseModel::where(array('flight_id' => $fid, 'ovr' => 1))->sum($event);
$this->save() + 1;
} else {
$check->increment('inventory');
}
}
public function showIndex()
{
$trackingSummary = new TrackingSummaryBaseModel();
$firstOfMonth = Carbon::now()->firstOfMonth();
$lastOfMonth = Carbon::now()->lastOfMonth();
$firstOfLastMonth = Carbon::now()->firstOfMonth()->subMonth();
$LastOfLastMonth = Carbon::now()->firstOfMonth()->subMonth()->lastOfMonth();
$websiteLists = $this->getPublisher()->publisherSite->lists('id');
// $this->data['earnPast3Month'] = $trackingSummary->getEarnPerMonth( $websiteLists, $firstOfLastMonth, $lastOfMonth);
$this->data['earnToday'] = $trackingSummary->getEarnTotal($websiteLists, date('Y-m-d'), date('Y-m-d'));
$this->data['earnThisMonth'] = $trackingSummary->getEarnTotal($websiteLists, $firstOfMonth, $lastOfMonth);
$this->data['earnLastMonth'] = $trackingSummary->getEarnTotal($websiteLists, $firstOfLastMonth, $LastOfLastMonth);
$this->data['earnPast30'] = $trackingSummary->getEarnPerDate($websiteLists, 30);
$this->data['earnPast3Month'] = $trackingSummary->getEarnPerMonth($websiteLists, 3);
$this->layout->content = View::make('index', $this->data);
}
function sumEarnings($campaign, $start_date, $end_date)
{
if (count($campaign) > 0) {
foreach ($campaign as $keycam => $cam) {
$data = new TrackingSummaryBaseModel();
$Earnings = $data->whereRaw("date >= '" . $start_date . "' AND date <= '" . $end_date . "' and flight_id = " . $cam->id)->groupby('flight_id')->select(DB::raw("sum(impression)as sum_impression"), DB::raw("sum(click)as sum_click"), 'flight_id')->first();
$earning = 0;
$delivered = 0;
if ($Earnings != null) {
if ($cam->cost_type == 'cpc') {
$earning = mathECPC($Earnings->sum_click, $cam->publisher_cost, $cam->total_inventory);
$delivered = $Earnings->sum_click;
} else {
if ($cam->cost_type == 'cpm') {
$earning = mathECPM($Earnings->sum_impression, $cam->publisher_cost, $cam->total_inventory);
$delivered = $Earnings->sum_impression;
}
}
}
$campaign[$keycam]->delivered = $delivered;
$campaign[$keycam]->earning = $earning;
$campaign[$keycam]->earning_tax = $earning * ($cam->sale_order_tax / 100);
}
}
return $campaign;
}
public function createMonthlyPaymentRequest()
{
$startDate = Carbon::now()->firstOfMonth()->subMonth();
$endDate = Carbon::now()->firstOfMonth()->subMonth()->lastOfMonth();
$date = $startDate->format('Y-m');
$check = PaymentRequestBaseModel::where(array('publisher_id' => $this->id, 'date' => $date))->first();
$websiteLists = $this->publisherSite->lists('id');
if (empty($check)) {
$tracking = new TrackingSummaryBaseModel();
$data = $tracking->sumEarnPerCampaign($websiteLists, $startDate, $endDate);
$campaigns = $data['campaign'];
$total = $data['total'];
$pr = new PaymentRequestBaseModel();
$pr->publisher_id = $this->id;
$pr->date = $date;
$pr->amount = $total;
$pr->save();
if (!empty($campaigns)) {
foreach ($campaigns as $campaignID => $campaign) {
$prd = new PaymentRequestDetailBaseModel();
$prd->publisher_id = $this->id;
$prd->campaign_id = $campaignID;
$prd->amount = $campaign['cost'];
$prd->impression = $campaign['impression'];
$prd->click = $campaign['click'];
if ($campaign['click'] != 0 && $campaign['impression'] != 0) {
$prd->ctr = $campaign['click'] / $campaign['impression'];
} else {
$prd->ctr = 0;
}
$prd->date = $date;
$prd->payment_request_id = $pr->id;
$prd->save();
}
}
}
}
function getTotalInventory()
{
$TrackingSummaryBaseModel = new TrackingSummaryBaseModel();
return $TrackingSummaryBaseModel->getFlightSummaryByID($this->id);
}
<td width="150" align="right">{{number_format($item->click,0,'',',')}}</td>
<td width="150" align="right">{{number_format($item->ctr *100, 2)}}%</td>
</tr>
<tr id="flight_{{$item->id}}" class="content collapse">
<td colspan="6" class="list-flight"><h5>List Flights</h5>
<table class="table table-striped custom-table-border">
<tr>
<th>No.</th>
<th>Title</th>
<th style="text-align: center">Amount (VND)</th>
<th style="text-align: center">Impressions</th>
<th style="text-align: center">Click</th>
<th style="text-align: center">CTR</th>
</tr>
<?php
$flights = TrackingSummaryBaseModel::getListFlight($item->campaign_id, $item->publisher_id, date('m', strtotime($item->created_at)), date('Y', strtotime($item->created_at)));
?>
@if(count($flights)>0)
<?php
$num = 1;
?>
@foreach($flights as $flight)
<?php
$amount = 0;
switch ($flight->flight->cost_type) {
case 'cpm':
$amount = $flight->amount_impression;
break;
case 'cpc':
$amount = $flight->amount_click;
break;
public function reportSchedule($numRecord = 30000)
{
set_time_limit(0);
$tblTSS = 'tracking_summary_schedule';
$cacheKey = "Report:Schedule";
if (RedisHelper::get($cacheKey)) {
return false;
}
RedisHelper::set($cacheKey, 1, 2);
// begin transaction
DB::beginTransaction();
$beginAt = new MongoDate(strtotime('2015-03-31 14:46:18'));
$lastReport = DB::table($tblTSS)->orderBy('id', 'desc')->first();
if ($lastReport) {
// time - 1 để chắc rằng ko bị sót record
$beginAt = new MongoDate($lastReport->last_record_sec - 2);
}
$endAt = new MongoDate(strtotime("-1 minute"));
$data = $this->where('created_at', '>=', $beginAt)->where('created_at', '<', $endAt)->take($numRecord)->get();
pr($data->count());
// pr($endAtTimestamp);
// pr($data->toArray());
try {
if ($data->count() > 0) {
// insert new report schedule
$trackingSummaryScheduleID = DB::table($tblTSS)->insertGetId(array('begin_at' => date('Y-m-d H:i:s')));
$reportArr = array();
$uniqueVisitor = array();
$lastRecord = '';
foreach ($data as $k => $record) {
//check duplicate record on report
if ($lastReport && $record->_id == $lastReport->last_record_id) {
$reportArr = array();
continue;
}
$record->event = strtolower($record->event);
$isOverReport = !empty($record->ovr) ? 'OverReport' : "NormalReport";
$key = "{$isOverReport}:{$record->date}:{$record->hour}:{$record->publisher_ad_zone_id}:{$record->website_id}:{$record->flight_website_id}";
if (empty($reportArr[$key])) {
$reportArr[$key] = array('publisher_ad_zone_id' => $record->publisher_ad_zone_id, 'ad_id' => $record->ad_id, 'ad_format_id' => $record->ad_format_id, 'campaign_id' => $record->campaign_id, 'flight_id' => $record->flight_id, 'flight_website_id' => $record->flight_website_id, 'website_id' => $record->website_id, 'hour' => $record->hour, 'date' => $record->date);
}
//ads reponses summary
$reportArr[$key][$record->response_type] = empty($reportArr[$key][$record->response_type]) ? 1 : $reportArr[$key][$record->response_type] + 1;
//ads request summary
if ($record->request_type == Delivery::REQUEST_TYPE_AD) {
$reportArr[$key]['ads_request'] = empty($reportArr[$key]['ads_request']) ? 1 : $reportArr[$key]['ads_request'] + 1;
}
if ($record->status) {
if ($record->response_type == Delivery::RESPONSE_TYPE_TRACKING_BEACON_SUCCESS) {
if (!empty($reportArr[$key][$record->event])) {
$reportArr[$key][$record->event]++;
} else {
$reportArr[$key][$record->event] = 1;
}
if ($record->event == 'impression' && !empty($record->unique_impression)) {
$reportArr[$key]['unique_impression'] = empty($reportArr[$key]['unique_impression']) ? 1 : $reportArr[$key]['unique_impression'] + 1;
}
if ($record->event == 'click' && !empty($record->unique_click)) {
$reportArr[$key]['unique_click'] = empty($reportArr[$key]['unique_click']) ? 1 : $reportArr[$key]['unique_click'] + 1;
}
}
} else {
$reportArr[$key]['failed_request'] = empty($reportArr[$key]['failed_request']) ? 1 : $reportArr[$key]['failed_request'] + 1;
}
}
//đánh dấu record cuối cùng đã xử lý
$lastRecord = $record;
// pr($lastRecord);
pr($record->created_at);
// no record -> return
if (!$lastRecord) {
throw new Exception();
}
$incrFields = array('impression', 'unique_impression', 'ads_request', 'click', 'unique_click', 'start', 'firstquartile', 'midpoint', 'thirdquartile', 'complete', 'fullscreen', 'mute', 'invalid_request', 'empty_referrer', 'referrer_not_match', 'log_preprocess', 'tracking_beacon_success', 'ads_success', 'geo_not_suitable', 'gender_not_suitable', 'age_not_suitable', 'audience_not_suitable', 'ad_zone_inventory_limited', 'ad_zone_daily_limited', 'inventory_limited', 'daily_inventory_limited', 'channel_not_suitable', 'no_ads_available', 'too_many_request', 'empty_visitor_id', 'ready_to_deliver', 'frequency_capped', 'checksum_error', 'failed_request');
$affectedRows = 0;
// pr($reportArr);
foreach ($reportArr as $key => $record) {
$isOVR = strpos($key, 'OverReport') !== FALSE ? 1 : 0;
$checkRecord = TrackingSummaryBaseModel::where(['flight_website_id' => $record['flight_website_id'], 'publisher_ad_zone_id' => $record['publisher_ad_zone_id'], 'website_id' => $record['website_id'], 'date' => $record['date'], 'hour' => $record['hour'], 'ovr' => $isOVR])->first();
if (!$checkRecord) {
$trackingSummary = new TrackingSummaryBaseModel();
$update = false;
} else {
$trackingSummary = $checkRecord;
$update = true;
}
foreach ($record as $k => $v) {
$trackingSummary->ovr = $isOVR;
if ($update) {
if (in_array($k, $incrFields)) {
$trackingSummary->{$k} = $trackingSummary->{$k} + $v;
}
} else {
$trackingSummary->{$k} = $v;
}
}
$trackingSummary->save();
$affectedRows++;
}
DB::table($tblTSS)->where('id', $trackingSummaryScheduleID)->update(array('finish_at' => date('Y-m-d H:i:s'), 'last_record_id' => $lastRecord->_id, 'last_record_sec' => $lastRecord->created_at->sec, 'last_record_usec' => $lastRecord->created_at->usec));
if (!empty($lastRecord)) {
DB::commit();
echo "Mongo Records: " . $data->count();
echo "\r\n<br/> Last Mongo Date : " . date('H:i:s d-m-Y', $lastRecord->created_at->sec);
echo "\r\nTracking Summary rows affected : " . $affectedRows;
echo "\r\n";
pr($affectedRows);
return $affectedRows;
}
}
} catch (\Exception $e) {
DB::rollback();
// throw $e;
return false;
}
}
public function showStats()
{
$show_type = 'website';
$inputs = $this->getParameter(Input::get('searchData'));
$campaign_ids = $inputs['campaign'];
$flight_ids = $inputs['flight'];
$website_ids = $inputs['webiste'];
$start = $inputs['start_date_range'];
$end = $inputs['end_date_range'];
$display = Input::get('showNumber', 10);
$data = null;
$paging = null;
$trackingSummaryModel = new TrackingSummaryBaseModel();
$paging = $trackingSummaryModel->getPagingData($campaign_ids, $flight_ids, $website_ids, $start, $end, $display);
$dataPaging = $this->getDataInPaging($paging);
$campaign_ids = $dataPaging['campaign_id'];
$website_ids = $dataPaging['website_id'];
$data = $trackingSummaryModel->getCampaignSummary($campaign_ids, $flight_ids, $website_ids, $start, $end);
$retval = $this->calculateStats($data);
$this->data['data'] = $retval;
if (!empty($paging)) {
$this->data['paging'] = $paging;
}
return View::make('showStats', $this->data);
}
static function getListFlight($campaignId, $publisher_id, $month, $year)
{
$query = TrackingSummaryBaseModel::select('flight.campaign_id', 'flight.cost_type', 'tracking_summary.flight_id', 'tracking_summary.flight_website_id', DB::raw('ROUND(pt_flight_website.publisher_base_cost,2) as ecpm'), DB::raw('SUM(impression) as impression'), DB::raw('SUM(unique_impression) as total_unique_impression'), DB::raw('SUM(click) as click'), DB::raw('ROUND(SUM(impression)/SUM(unique_impression),2) as frequency'), DB::raw('ROUND(SUM(click)/SUM(impression)*100,2) as ctr'), DB::raw('ROUND(pt_tracking_summary.publisher_base_cost/1000*SUM(impression),2) as amount_impression'), DB::raw('ROUND(pt_tracking_summary.publisher_base_cost*SUM(click),2) as amount_click'), DB::raw('SUM(complete) as complete'), DB::raw('ROUND(pt_tracking_summary.publisher_base_cost*SUM(complete),2) as amount_complete'))->join('flight_website', 'tracking_summary.flight_website_id', '=', 'flight_website.id')->join('flight', 'flight.id', '=', 'flight_website.flight_id')->join('publisher_site', 'publisher_site.id', '=', 'flight_website.website_id')->join('publisher', 'publisher.id', '=', 'publisher_site.publisher_id')->where('publisher.id', $publisher_id)->whereRaw('MONTH(pt_tracking_summary.date)="' . $month . '" AND YEAR(pt_tracking_summary.date)="' . $year . '"')->where('ovr', 0)->where('flight.campaign_id', '=', $campaignId)->groupBy('flight_website.flight_id', 'tracking_summary.publisher_base_cost')->get();
return $query;
}
function getReportDateWebsiteDetail()
{
$flight = Input::get('flight');
$campaign = Input::get('campaign');
$ad = Input::get('ad');
$start = Input::get('dstart');
$end = Input::get('dend');
$site = Input::get('site');
$items['tracking'] = TrackingSummaryBaseModel::getDataPerWebsite($start, $end, $site, $campaign, $ad, $flight);
return View::make("reportdateWebsite", $items);
}
