public function ChangeStatus($id)
{
if (Auth::check()) {
if (in_array('ADD_EDIT_GEOSEGMENTLIST', $this->permission)) {
if (User::isSuperAdmin()) {
$entity = GeoSegmentList::find($id);
} else {
$usr_company = $this->user_company();
$entity = GeoSegmentList::whereHas('getAdvertiser', function ($q) use($usr_company) {
$q->whereHas('GetClientID', function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
});
})->find($id);
}
if ($entity) {
$data = array();
$audit = new AuditsController();
if ($entity->status == 'Active') {
array_push($data, 'status');
array_push($data, $entity->status);
array_push($data, 'Inactive');
$entity->status = 'Inactive';
$msg = 'disable';
} elseif ($entity->status == 'Inactive') {
array_push($data, 'status');
array_push($data, $entity->status);
array_push($data, 'Active');
$entity->status = 'Active';
$msg = 'actived';
}
$audit->store('geosegment', $id, $data, 'edit');
$entity->save();
return $msg;
}
return 'please Select your Client';
}
return "You don't have permission";
}
return Redirect::to(url('user/login'));
}
public function targetgroup_bulk(Request $request)
{
// return dd($request->all());
if (Auth::check()) {
if (in_array('ADD_EDIT_TARGETGROUP', $this->permission)) {
$validate = $this->validation($request);
$usr_company = $this->user_company();
if ($validate->passes()) {
$audit = new AuditsController();
$audit_key = $audit->generateRandomString();
if ($request->input('campaign_id') == 'all' and !$request->has('tg_list')) {
if ($request->input('advertiser_id') == 'all' and $request->input('client_id') == 'all') {
if (User::isSuperAdmin()) {
$tg_list1 = Targetgroup::get(['id'])->toArray();
} else {
$tg_list1 = Targetgroup::whereHas('getCampaign', function ($p) use($usr_company) {
$p->whereHas('getAdvertiser', function ($q) use($usr_company) {
$q->whereHas('GetClientID', function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
});
});
})->get(['id'])->toArray();
}
} elseif ($request->input('advertiser_id') != 'all') {
if (User::isSuperAdmin()) {
$tg_list1 = Targetgroup::whereHas('getCampaign', function ($p) use($request) {
$p->whereHas('getAdvertiser', function ($q) use($request) {
$q->where('id', $request->input('advertiser_id'));
});
})->get(['id'])->toArray();
} else {
$tg_list1 = Targetgroup::whereHas('getCampaign', function ($p) use($usr_company, $request) {
$p->whereHas('getAdvertiser', function ($q) use($usr_company, $request) {
$q->where('id', $request->input('advertiser_id'))->whereHas('GetClientID', function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
});
});
})->get(['id'])->toArray();
}
} elseif ($request->input('client_id') != 'all') {
if (User::isSuperAdmin()) {
$tg_list1 = Targetgroup::whereHas('getCampaign', function ($p) use($request) {
$p->whereHas('getAdvertiser', function ($q) use($request) {
$q->whereHas('GetClientID', function ($p) use($request) {
$p->where('id', $request->input('client_id'));
});
});
})->get(['id'])->toArray();
} else {
$tg_list1 = Targetgroup::whereHas('getCampaign', function ($p) use($usr_company, $request) {
$p->whereHas('getAdvertiser', function ($q) use($usr_company, $request) {
$q->where('id', $request->input('advertiser_id'))->whereHas('GetClientID', function ($p) use($usr_company, $request) {
$p->where('id', $request->input('client_id'))->whereIn('user_id', $usr_company);
});
});
})->get(['id'])->toArray();
}
}
} elseif ($request->input('campaign_id') != 'all' and !$request->has('tg_list')) {
if (User::isSuperAdmin()) {
$tg_list1 = Targetgroup::whereHas('getCampaign', function ($p) use($request) {
$p->where('id', $request->input('campaign_id'));
})->get(['id'])->toArray();
} else {
$tg_list1 = Targetgroup::whereHas('getCampaign', function ($p) use($usr_company, $request) {
$p->where('id', $request->input('campaign_id'))->whereHas('getAdvertiser', function ($q) use($usr_company) {
$q->whereHas('GetClientID', function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
});
});
})->get(['id'])->toArray();
}
} else {
$tg_list1 = explode(',', $request->input('tg_list'));
}
// return dd($tg_list1);
$bid_hour = '';
$flg = 0;
for ($i = 0; $i < 7; $i++) {
for ($j = 0; $j < 24; $j++) {
if (!is_null($request->input($i . '-' . $j . '-hour'))) {
$flg = 1;
$bid_hour1[$j] = "1";
} else {
$bid_hour1[$j] = "0";
}
}
$bid_hour[$i + 1] = $bid_hour1;
}
if (count($tg_list1) > 0) {
foreach ($tg_list1 as $index) {
$data = array();
if (!$request->has('tg_list')) {
$tg_id = $index['id'];
$tg = Targetgroup::find($tg_id);
} else {
$tg_id = $index;
if (User::isSuperAdmin()) {
$tg = Targetgroup::find($tg_id);
} else {
$tg = Targetgroup::whereHas('getCampaign', function ($p) use($usr_company) {
$p->whereHas('getAdvertiser', function ($q) use($usr_company) {
$q->whereHas('GetClientID', function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
});
});
})->find($tg_id);
}
}
// return dd($tg_id);
if ($tg) {
// return dd($tg);
if ($request->has('date_range')) {
$check_date = $this->date_validation($request->input('date_range'));
if (!$check_date) {
return Redirect::back()->withErrors(['success' => false, 'msg' => 'please check your date range!']);
}
$date_range = explode('-', $request->input('date_range'));
$start_date = Carbon::createFromFormat('m/d/Y', str_replace(' ', '', $date_range[0]))->toDateString();
$end_date = Carbon::createFromFormat('m/d/Y', str_replace(' ', '', $date_range[1]))->toDateString();
}
if ($flg == 1) {
$target_bid_hour = Targetgroup_Bidhour_Map::where('targetgroup_id', $tg_id)->first();
$target_bid_hour->hours = json_encode($bid_hour);
$target_bid_hour->save();
}
if ($request->has('name')) {
array_push($data, 'Name');
array_push($data, $request->input('name'));
$tg->name = $request->input('name');
}
if ($request->has('active')) {
$active = 'Inactive';
if ($request->input('active') == 'on') {
$active = 'Active';
}
array_push($data, 'Status');
array_push($data, $active);
$tg->status = $active;
}
if ($request->has('max_impression')) {
array_push($data, 'Max Imps');
array_push($data, $request->input('max_impression'));
$tg->max_impression = $request->input('max_impression');
}
if ($request->has('daily_max_impression')) {
array_push($data, 'Daily Max Imps');
array_push($data, $request->input('daily_max_impression'));
$tg->daily_max_impression = $request->input('daily_max_impression');
}
if ($request->has('max_budget')) {
array_push($data, 'Max Budget');
array_push($data, $request->input('max_budget'));
$tg->max_budget = $request->input('max_budget');
}
if ($request->has('daily_max_budget')) {
array_push($data, 'Daily Max Budget');
array_push($data, $request->input('daily_max_budget'));
$tg->daily_max_budget = $request->input('daily_max_budget');
}
if ($request->has('cpm')) {
array_push($data, 'CPM');
array_push($data, $request->input('cpm'));
$tg->cpm = $request->input('cpm');
}
if ($request->has('pacing_plan')) {
array_push($data, 'Pacing Plan');
array_push($data, $request->input('pacing_plan'));
$tg->pacing_plan = $request->input('pacing_plan');
}
if ($request->has('frequency_in_sec')) {
array_push($data, 'Frequency in Sec');
array_push($data, $request->input('frequency_in_sec'));
$tg->frequency_in_sec = $request->input('frequency_in_sec');
}
if ($request->has('iab_category')) {
array_push($data, 'Iab Category');
array_push($data, $request->input('iab_category'));
$tg->iab_category = $request->input('iab_category');
}
if ($request->has('iab_sub_category')) {
array_push($data, 'Iab Sub Category');
array_push($data, $request->input('iab_sub_category'));
$tg->iab_sub_category = $request->input('iab_sub_category');
}
if ($request->has('domain_name')) {
array_push($data, 'Domain Name');
array_push($data, $request->input('domain_name'));
$tg->advertiser_domain_name = $request->input('domain_name');
}
if ($request->has('description')) {
array_push($data, 'Description');
array_push($data, $request->input('description'));
$tg->description = $request->input('description');
}
if (isset($start_date)) {
array_push($data, 'Start Date');
array_push($data, $start_date);
$tg->start_date = $start_date;
}
if (isset($end_date)) {
array_push($data, 'End Date');
array_push($data, $end_date);
$tg->end_date = $end_date;
}
if ($request->has('unassign_geolocation')) {
Targetgroup_Geolocation_Map::where('targetgroup_id', $tg_id)->delete();
}
if ($request->has('unassign_geosegment')) {
Targetgroup_Geosegmentlist_Map::where('targetgroup_id', $tg_id)->delete();
}
if ($request->has('unassign_segment')) {
Targetgroup_Segment_Map::where('targetgroup_id', $tg_id)->delete();
}
if ($request->has('unassign_segment')) {
Targetgroup_Bwlist_Map::where('targetgroup_id', $tg_id)->delete();
}
if ($request->has('unassign_creative')) {
Targetgroup_Creative_Map::where('targetgroup_id', $tg_id)->delete();
}
if ($request->has('unassign_bidprofile')) {
Targetgroup_Bidprofile_Map::where('targetgroup_id', $tg_id)->delete();
}
if (count($request->input('to_geosegment')) > 0) {
$geoSegment_map = Targetgroup_Geosegmentlist_Map::where('targetgroup_id', $tg_id)->get();
$geoSegArr = array();
foreach ($geoSegment_map as $index2) {
array_push($geoSegArr, $index2->geosegmentlist_id);
}
foreach ($request->input('to_geosegment') as $index1) {
if (!in_array($index1, $geoSegArr)) {
if (User::isSuperAdmin()) {
$check = GeoSegmentList::find($index1);
} else {
$check = GeoSegmentList::whereHas('getAdvertiser', function ($q) use($usr_company) {
$q->whereHas('GetClientID', function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
});
})->find($index1);
}
if ($check) {
$geosegment_assign = new Targetgroup_Geosegmentlist_Map();
$geosegment_assign->targetgroup_id = $tg_id;
$geosegment_assign->geosegmentlist_id = $index1;
$geosegment_assign->save();
$assign_data = array('Geo Segment', $check->name);
$audit->store('targetgroup', $tg_id, $assign_data, 'bulk_edit', $audit_key);
}
array_push($geoSegArr, $index1);
}
}
}
if (count($request->input('to_creative')) > 0) {
$map = Targetgroup_Creative_Map::where('targetgroup_id', $tg_id)->get();
$mapArr = array();
foreach ($map as $index1) {
array_push($mapArr, $index1->creative_id);
}
foreach ($request->input('to_creative') as $index1) {
if (!in_array($index1, $mapArr)) {
if (User::isSuperAdmin()) {
$check = Creative::find($index1);
} else {
$check = Creative::whereHas('getAdvertiser', function ($q) use($usr_company) {
$q->whereHas('GetClientID', function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
});
})->find($index1);
}
if ($check) {
$creative_assign = new Targetgroup_Creative_Map();
$creative_assign->targetgroup_id = $tg_id;
$creative_assign->creative_id = $index1;
$creative_assign->save();
$assign_data = array('Creative', $check->name);
$audit->store('targetgroup', $tg_id, $assign_data, 'bulk_edit', $audit_key);
}
array_push($mapArr, $index1);
}
}
}
if (count($request->input('to_segment')) > 0) {
$segment_map = Targetgroup_Segment_Map::where('targetgroup_id', $tg_id)->get();
$segArr = array();
foreach ($segment_map as $index1) {
array_push($segArr, $index1->segment_id);
}
foreach ($request->input('to_geosegment') as $index1) {
if (!in_array($index1, $segArr)) {
$segment_assign = new Targetgroup_Segment_Map();
$segment_assign->targetgroup_id = $tg_id;
$segment_assign->segment_id = $index;
$segment_assign->save();
$assign_data = array('Segment', 'segmentID:' . $segment_assign->id);
$audit->store('targetgroup', $tg_id, $assign_data, 'bulk_edit', $audit_key);
array_push($segArr, $index1);
}
}
}
if (count($request->input('to_geolocation')) > 0) {
$map = Targetgroup_Geolocation_Map::where('targetgroup_id', $tg_id)->get();
$mapArr = array();
foreach ($map as $index1) {
array_push($mapArr, $index1->geolocation_id);
}
foreach ($request->input('to_geolocation') as $index1) {
if (!in_array($index1, $mapArr)) {
$geolocation_assign = new Targetgroup_Geolocation_Map();
$geolocation_assign->targetgroup_id = $tg_id;
$geolocation_assign->geolocation_id = $index1;
$geolocation_assign->save();
$assign_data = array('Geo Location', 'gln' . $geolocation_assign->id);
$audit->store('targetgroup', $tg_id, $assign_data, 'bulk_edit', $audit_key);
array_push($mapArr, $index1);
}
}
}
$audit->store('targetgroup', $tg_id, $data, 'bulk_edit', $audit_key);
$tg->save();
}
}
return Redirect::back()->withErrors(['success' => true, 'msg' => 'Target Groups Edited Successfully']);
}
return Redirect::back()->withErrors(['success' => false, 'msg' => 'No Target Groups found']);
}
return Redirect::back()->withErrors(['success' => false, 'msg' => $validate->messages()->all()]);
}
return Redirect::back()->withErrors(['success' => false, 'msg' => 'dont have Edit Permission']);
}
return Redirect::to(url('/user/login'));
}
public function SubAudit($audit)
{
$audit_obj = array();
foreach ($audit as $index) {
$entity_obj = null;
switch ($index->entity_type) {
case 'user':
if (in_array('VIEW_USER', $this->permission)) {
$entity_obj = User::find($index->entity_id);
}
break;
case 'company':
if (User::isSuperAdmin()) {
$entity_obj = \App\Models\Company::find($index->entity_id);
}
break;
case 'inventory':
if (User::isSuperAdmin()) {
$entity_obj = Inventory::find($index->entity_id);
}
break;
case 'client':
if (in_array('VIEW_CLIENT', $this->permission)) {
$entity_obj = Client::find($index->entity_id);
}
break;
case 'advertiser':
if (in_array('VIEW_ADVERTISER', $this->permission)) {
$entity_obj = Advertiser::with('GetClientID')->find($index->entity_id);
}
break;
case 'creative':
if (in_array('VIEW_CREATIVE', $this->permission)) {
$entity_obj = Creative::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'campaign':
if (in_array('VIEW_CAMPAIGN', $this->permission)) {
$entity_obj = Campaign::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'offer':
if (in_array('VIEW_OFFER', $this->permission)) {
$entity_obj = Offer::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'pixel':
if (in_array('VIEW_PIXEL', $this->permission)) {
$entity_obj = Pixel::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'targetgroup':
if (in_array('VIEW_TARGETGROUP', $this->permission)) {
$entity_obj = Targetgroup::find($index->entity_id);
}
break;
case 'targetgroup_geolocation_map':
if (in_array('VIEW_TARGETGROUP', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = Geolocation::find($index->after_value);
} else {
$entity_obj = Geolocation::find($index->entity_id);
}
}
break;
case 'targetgroup_creative_map':
if (in_array('VIEW_TARGETGROUP', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = Creative::find($index->after_value);
} else {
$entity_obj = Creative::find($index->entity_id);
}
}
break;
case 'targetgroup_segment_map':
if (in_array('VIEW_TARGETGROUP', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = Segment::find($index->after_value);
} else {
$entity_obj = Segment::find($index->entity_id);
}
}
break;
case 'targetgroup_geosegment_map':
if (in_array('VIEW_TARGETGROUP', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = GeoSegmentList::find($index->after_value);
} else {
$entity_obj = GeoSegmentList::find($index->entity_id);
}
}
break;
case 'targetgroup_bwlist_map':
if (in_array('VIEW_TARGETGROUP', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = BWList::find($index->after_value);
} else {
$entity_obj = BWList::find($index->entity_id);
}
}
break;
case 'targetgroup_bidprofile_map':
if (in_array('VIEW_TARGETGROUP', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = Bid_Profile::find($index->after_value);
} else {
$entity_obj = Bid_Profile::find($index->entity_id);
}
}
break;
case 'geosegment':
if (in_array('VIEW_GEOSEGMENTLIST', $this->permission)) {
$entity_obj = GeoSegmentList::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'geosegmententrie':
if (in_array('VIEW_GEOSEGMENTLIST', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = GeoSegment::find($index->after_value);
} else {
$entity_obj = GeoSegment::find($index->entity_id);
}
}
break;
case 'bwlist':
if (in_array('VIEW_BWLIST', $this->permission)) {
$entity_obj = BWList::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'bwlistentrie':
if (in_array('VIEW_BWLIST', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = BWEntries::find($index->after_value);
} else {
$entity_obj = BWEntries::find($index->entity_id);
}
}
break;
case 'bid_profile':
if (in_array('VIEW_BIDPROFILE', $this->permission)) {
$entity_obj = Bid_Profile::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'bid_profile_entry':
if (in_array('VIEW_BIDPROFILE', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = Bid_Profile::where('id', $index->after_value)->first();
} else {
$entity_obj = Bid_Profile_Entry::with('getParent')->find($index->entity_id);
}
}
break;
case 'modelTable':
if (in_array('VIEW_MODEL', $this->permission)) {
if ($index->audit_type == 'del') {
$entity_obj = BWList::where('id', $index->after_value)->first();
} else {
$entity_obj = ModelTable::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
}
break;
case 'offer_pixel_map':
if (in_array('VIEW_OFFER', $this->permission)) {
$entity_obj = Pixel::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'advertiser_model_map':
if (in_array('VIEW_ADVERTISER', $this->permission)) {
$entity_obj = ModelTable::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'positive_offer_model':
if (in_array('VIEW_MODEL', $this->permission)) {
$entity_obj = Offer::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
case 'negative_offer_model':
if (in_array('VIEW_MODEL', $this->permission)) {
$entity_obj = Offer::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($index->entity_id);
}
break;
}
if (!is_null($entity_obj)) {
array_push($audit_obj, $index);
array_push($audit_obj, $entity_obj);
}
}
return $audit_obj;
}
public function ChangeState(Request $request)
{
// return dd($request->all());
if (Auth::check()) {
if (in_array('VIEW_ADVERTISER', $this->permission)) {
//TODO: set report permission
$type = $request->input('type');
$arr = array();
array_push($arr, $type);
$time = '';
$client = '';
$advertiser = '';
$creative = '';
$geosegment = '';
$campaign = '';
$targetgroup = '';
if (User::isSuperAdmin()) {
$clientArry = Client::get(['id'])->toArray();
$advertiserArry = Advertiser::get(['id'])->toArray();
$query = "1 = 1 ";
if ($request->has('client')) {
$query .= " and impression.client_id=" . $request->input('client');
}
if ($request->has('advertiser')) {
$query .= " and impression.advertiser_id=" . $request->input('advertiser');
}
if ($request->has('creative')) {
$query .= " and impression.creative_id=" . $request->input('creative');
}
if ($request->has('geosegment')) {
$query .= " and impression.geosegment_id=" . $request->input('geosegment');
}
if ($request->has('campaign')) {
$query .= " and impression.campaign_id=" . $request->input('campaign');
}
if ($request->has('targetgroup')) {
$query .= " and impression.targetgroup_id=" . $request->input('targetgroup');
}
if ($request->input('report_type') == 'today') {
$time = "between '" . date('Y-m-d H:i:s', time() - 60 * 60 * 24) . "' and '" . date('Y-m-d H:i:s', time() + 60 * 30 * 10) . "'";
$interval = 300;
$query .= " and impression.created_at " . $time;
}
if ($request->input('report_type') == '10m') {
$time = "between '" . date('Y-m-d H:i:s', time() - 600) . "' and '" . date('Y-m-d H:i:s') . "'";
$query .= " and impression.created_at " . $time;
$interval = 10;
}
if ($request->input('report_type') == '1h') {
$time = "between '" . date('Y-m-d H:i:s', time() - 60 * 60) . "' and '" . date('Y-m-d H:i:s') . "'";
$query .= " and impression.created_at " . $time;
$interval = 30;
}
if ($request->input('report_type') == '3h') {
$time = "between '" . date('Y-m-d H:i:s', time() - 60 * 60 * 3) . "' and '" . date('Y-m-d H:i:s') . "'";
$query .= " and impression.created_at " . $time;
$interval = 60;
}
if ($request->input('report_type') == '6h') {
$time = "between '" . date('Y-m-d H:i:s', time() - 60 * 60 * 6) . "' and '" . date('Y-m-d H:i:s') . "'";
$query .= " and impression.created_at " . $time;
$interval = 120;
}
if ($request->input('report_type') == '1D') {
$time = "between '" . date('Y-m-d H:i:s', time() - 60 * 60 * 24) . "' and '" . date('Y-m-d H:i:s') . "'";
$query .= " and impression.created_at " . $time;
$interval = 300;
}
if ($request->input('report_type') == '1M') {
$time = "between '" . date('Y-m-d H:i:s', time() - 60 * 60 * 24 * 30) . "' and '" . date('Y-m-d H:i:s') . "'";
$query .= " and impression.created_at " . $time;
$interval = 60 * 60;
}
if ($request->input('report_type') == 'rang') {
//todo: 120 noghte
if ($this->report_date_validation($request->input('date_range'))) {
$date_range = explode('&', $request->input('date_range'));
$start_date = explode('T', $date_range[0]);
$end_date = explode('T', $date_range[1]);
$time = "between '" . $start_date[0] . ' ' . $start_date[1] . "' and '" . $end_date[0] . ' ' . $end_date[1] . "'";
$query .= " and impression.created_at " . $time;
$interval = 24 * 60 * 60;
}
}
switch ($type) {
case 'client':
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereRaw($query)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
if ($request->input('advertiser') == '') {
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
}
break;
case 'report_type':
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereRaw($query)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
$campaign = DB::table('impression')->join('campaign', 'impression.campaign_id', '=', 'campaign.id')->select(DB::raw('count(impression.campaign_id) as imps, impression.campaign_id as id , campaign.name'))->whereRaw($query)->groupBy('impression.campaign_id')->orderBy('imps', 'DESC')->get();
$targetgroup = DB::table('impression')->join('targetgroup', 'impression.targetgroup_id', '=', 'targetgroup.id')->select(DB::raw('count(impression.targetgroup_id) as imps, impression.targetgroup_id as id , targetgroup.name'))->whereRaw($query)->groupBy('impression.targetgroup_id')->orderBy('imps', 'DESC')->get();
$creative = DB::table('impression')->join('creative', 'impression.creative_id', '=', 'creative.id')->select(DB::raw('count(impression.creative_id) as imps, impression.creative_id as id , creative.name'))->whereRaw($query)->groupBy('impression.creative_id')->orderBy('imps', 'DESC')->get();
$geosegment = DB::table('impression')->join('geosegmentlist', 'impression.geosegment_id', '=', 'geosegmentlist.id')->select(DB::raw('count(impression.geosegment_id) as imps, impression.geosegment_id as id , geosegmentlist.name'))->whereRaw($query)->groupBy('impression.geosegment_id')->orderBy('imps', 'DESC')->get();
break;
case 'campaign':
$campaign_client_advertiser = Campaign::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($request->input('campaign'));
if ($request->input('client') == '') {
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereRaw($query)->where('impression.client_id', $campaign_client_advertiser->getAdvertiser->GetClientID->id)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('advertiser') == '') {
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->where('impression.advertiser_id', $campaign_client_advertiser->getAdvertiser->id)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
}
$campaign = DB::table('impression')->join('campaign', 'impression.campaign_id', '=', 'campaign.id')->select(DB::raw('count(impression.campaign_id) as imps, impression.campaign_id as id , campaign.name'))->whereRaw($query)->groupBy('impression.campaign_id')->orderBy('imps', 'DESC')->get();
break;
case 'targetgroup':
$targetgroup_client_advertiser = Targetgroup::with(['getCampaign' => function ($q) {
$q->with(['getAdvertiser' => function ($p) {
$p->with('GetClientID');
}]);
}])->find($request->input('targetgroup'));
if ($request->input('client') == '') {
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereRaw($query)->where('impression.client_id', $targetgroup_client_advertiser->getCampaign->getAdvertiser->GetClientID->id)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('advertiser') == '') {
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->where('impression.advertiser_id', $targetgroup_client_advertiser->getCampaign->getAdvertiser->id)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('campaign') == '') {
$campaign = DB::table('impression')->join('campaign', 'impression.campaign_id', '=', 'campaign.id')->select(DB::raw('count(impression.campaign_id) as imps, impression.campaign_id as id , campaign.name'))->where('impression.campaign_id', $targetgroup_client_advertiser->getCampaign->id)->groupBy('impression.campaign_id')->orderBy('imps', 'DESC')->get();
}
$targetgroup = DB::table('impression')->join('targetgroup', 'impression.targetgroup_id', '=', 'targetgroup.id')->select(DB::raw('count(impression.targetgroup_id) as imps, impression.targetgroup_id as id , targetgroup.name'))->whereRaw($query)->groupBy('impression.targetgroup_id')->orderBy('imps', 'DESC')->get();
break;
case 'advertiser':
if ($request->input('client') == '') {
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->where('impression.advertiser_id', $request->input('advertiser'))->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
}
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
break;
case 'creative':
$creativ_client_advertiser = Creative::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($request->input('creative'));
if ($request->input('client') == '') {
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereRaw($query)->where('impression.client_id', $creativ_client_advertiser->getAdvertiser->GetClientID->id)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('advertiser') == '') {
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->where('impression.advertiser_id', $creativ_client_advertiser->getAdvertiser->id)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
}
$creative = DB::table('impression')->join('creative', 'impression.creative_id', '=', 'creative.id')->select(DB::raw('count(impression.creative_id) as imps, impression.creative_id as id , creative.name'))->whereRaw($query)->groupBy('impression.creative_id')->orderBy('imps', 'DESC')->get();
break;
case 'geosegment':
$geosegment_client_advertiser = GeoSegmentList::with(['getAdvertiser' => function ($q) {
$q->with('GetClientID');
}])->find($request->input('geosegment'));
// return dd($geosegment_client_advertiser->getAdvertiser);
if ($request->input('client') == '') {
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereRaw($query)->where('impression.client_id', $geosegment_client_advertiser->getAdvertiser->GetClientID->id)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('advertiser') == '') {
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->where('impression.advertiser_id', $geosegment_client_advertiser->getAdvertiser->id)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
}
$geosegment = DB::table('impression')->join('geosegmentlist', 'impression.geosegment_id', '=', 'geosegmentlist.id')->select(DB::raw('count(impression.geosegment_id) as imps, impression.geosegment_id as id , geosegmentlist.name'))->whereRaw($query)->groupBy('impression.geosegment_id')->orderBy('imps', 'DESC')->get();
break;
case 'client_unfilter':
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereIn('impression.advertiser_id', $advertiserArry)->whereIn('impression.client_id', $clientArry)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereIn('impression.advertiser_id', $advertiserArry)->whereIn('impression.client_id', $clientArry)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
$campaign = DB::table('impression')->join('campaign', 'impression.campaign_id', '=', 'campaign.id')->select(DB::raw('count(impression.campaign_id) as imps, impression.campaign_id as id , campaign.name'))->whereIn('impression.advertiser_id', $advertiserArry)->whereIn('impression.client_id', $clientArry)->groupBy('impression.campaign_id')->orderBy('imps', 'DESC')->get();
$targetgroup = DB::table('impression')->join('targetgroup', 'impression.targetgroup_id', '=', 'targetgroup.id')->select(DB::raw('count(impression.targetgroup_id) as imps, impression.targetgroup_id as id , targetgroup.name'))->whereIn('impression.advertiser_id', $advertiserArry)->whereIn('impression.client_id', $clientArry)->groupBy('impression.targetgroup_id')->orderBy('imps', 'DESC')->get();
$creative = DB::table('impression')->join('creative', 'impression.creative_id', '=', 'creative.id')->select(DB::raw('count(impression.creative_id) as imps, impression.creative_id as id , creative.name'))->whereIn('impression.advertiser_id', $advertiserArry)->whereIn('impression.client_id', $clientArry)->groupBy('impression.creative_id')->orderBy('imps', 'DESC')->get();
$geosegment = DB::table('impression')->join('geosegmentlist', 'impression.geosegment_id', '=', 'geosegmentlist.id')->select(DB::raw('count(impression.geosegment_id) as imps, impression.geosegment_id as id , geosegmentlist.name'))->whereIn('impression.advertiser_id', $advertiserArry)->whereIn('impression.client_id', $clientArry)->groupBy('impression.geosegment_id')->orderBy('imps', 'DESC')->get();
break;
}
if ($request->input('client') == '' and $client == '') {
$client = DB::table('impression')->join('client', 'impression.client_id', '=', 'client.id')->select(DB::raw('count(impression.client_id) as imps, impression.client_id as id , client.name'))->whereRaw($query)->groupBy('impression.client_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('advertiser') == '' and $advertiser == '') {
$advertiser = DB::table('impression')->join('advertiser', 'impression.advertiser_id', '=', 'advertiser.id')->select(DB::raw('count(impression.advertiser_id) as imps, impression.advertiser_id as id , advertiser.name'))->whereRaw($query)->groupBy('impression.advertiser_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('campaign') == '' and $campaign == '') {
$campaign = DB::table('impression')->join('campaign', 'impression.campaign_id', '=', 'campaign.id')->select(DB::raw('count(impression.campaign_id) as imps, impression.campaign_id as id , campaign.name'))->whereRaw($query)->groupBy('impression.campaign_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('targetgroup') == '') {
$targetgroup = DB::table('impression')->join('targetgroup', 'impression.targetgroup_id', '=', 'targetgroup.id')->select(DB::raw('count(impression.targetgroup_id) as imps, impression.targetgroup_id as id , targetgroup.name'))->whereRaw($query)->groupBy('impression.targetgroup_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('creative') == '') {
$creative = DB::table('impression')->join('creative', 'impression.creative_id', '=', 'creative.id')->select(DB::raw('count(impression.creative_id) as imps, impression.creative_id as id , creative.name'))->whereRaw($query)->groupBy('impression.creative_id')->orderBy('imps', 'DESC')->get();
}
if ($request->input('geosegmentlist') == '') {
$geosegment = DB::table('impression')->join('geosegmentlist', 'impression.geosegment_id', '=', 'geosegmentlist.id')->select(DB::raw('count(impression.geosegment_id) as imps, impression.geosegment_id as id , geosegmentlist.name'))->whereRaw($query)->groupBy('impression.geosegment_id')->orderBy('imps', 'DESC')->get();
}
// return dd($query);
if ($time != '') {
$impChart = Impression::where('event_type', 'impression')->whereRaw($query)->whereRaw('created_at ' . $time)->orderBy('created_at', 'ASC')->get();
$imps = 1;
$flg = 0;
// return dd($impChart);
$impsString = "Date,Imps\n";
if ($impChart) {
foreach ($impChart as $key => $index) {
if ($flg == 0) {
$timeToShow = $index->created_at;
$firstHop = strtotime($index->created_at);
$nextHop = $firstHop + $interval;
$flg = 1;
}
if (isset($impChart[$key + 1]) and strtotime($impChart[$key + 1]->created_at) >= $nextHop) {
$impsString .= $timeToShow . ",0;" . $imps . ";0\n";
$flg = 0;
$imps = 0;
}
if (!isset($impChart[$key + 1])) {
}
$imps++;
}
}
// return dd($impsString);
$clkChart = Impression::where('event_type', 'click')->whereRaw($query)->whereRaw('created_at ' . $time)->orderBy('created_at', 'ASC')->get();
$imps = 1;
$flg = 0;
$clkString = "Date,Clicks\n";
foreach ($clkChart as $key => $index) {
if ($flg == 0) {
$timeToShow = $index->created_at;
$firstHop = strtotime($index->created_at);
$nextHop = $firstHop + $interval;
$flg = 1;
}
if (isset($clkChart[$key + 1]) and strtotime($clkChart[$key + 1]->created_at) >= $nextHop) {
$clkString .= $timeToShow . ",0;" . $imps . ";0\n";
$flg = 0;
$imps = 0;
}
if (!isset($clkChart[$key + 1])) {
}
$imps++;
}
$cnvChart = Impression::where('event_type', 'conversion')->whereRaw($query)->whereRaw('created_at ' . $time)->orderBy('created_at', 'ASC')->get();
$imps = 1;
$flg = 0;
$cnvString = "Date,Conversion\n";
foreach ($cnvChart as $key => $index) {
if ($flg == 0) {
$timeToShow = $index->created_at;
$firstHop = strtotime($index->created_at);
$nextHop = $firstHop + $interval;
$flg = 1;
}
if (isset($cnvChart[$key + 1]) and strtotime($cnvChart[$key + 1]->created_at) >= $nextHop) {
$cnvString .= $timeToShow . ",0;" . $imps . ";0\n";
$flg = 0;
$imps = 0;
}
if (!isset($cnvChart[$key + 1])) {
}
$imps++;
}
array_push($arr, $client);
array_push($arr, $advertiser);
array_push($arr, $creative);
array_push($arr, $geosegment);
array_push($arr, $campaign);
array_push($arr, $targetgroup);
array_push($arr, $impsString);
array_push($arr, $clkString);
array_push($arr, $cnvString);
}
return json_encode($arr);
$clients = Client::get();
$advertiser = Advertiser::get();
// $advertiser = Advertiser::with(['Campaign' => function ($q) {
// $q->select(DB::raw('*,count(advertiser_id) as advertiser_count'))->groupBy('advertiser_id');
// }])->with('GetClientID')->get();
} else {
$usr_company = User::where('company_id', Auth::user()->company_id)->get(['id'])->toArray();
$clients = Client::whereIn('user_id', $usr_company)->get();
$advertiser = Advertiser::with(['GetClientID' => function ($p) use($usr_company) {
$p->whereIn('user_id', $usr_company);
}])->get();
}
// return dd($advertiser);
return view('report.report')->with('clients', $clients)->with('advertiser', $advertiser);
}
return Redirect::back()->withErrors(['success' => false, 'msg' => "You don't have permission"]);
}
return Redirect::to(url('user/login'));
}
