public function datagrid() { $lead_model = Lead::query(); if (app('user') !== false) { $company_id = app('user')->company_id; if (app('user')->inRole(app('users_role'))) { $lead_model->whereHas('form', function ($query) use($company_id) { return $query->whereHas('area', function ($query) use($company_id) { return $query->where('company_id', '=', $company_id); }); }); } } $leads = $lead_model->get(); $data = []; $keys = []; foreach ($leads as $lead) { $row = []; $row['id'] = $lead->id; $row['date'] = $lead->created_at->format('d/m/Y H:i:s'); $row['created_at'] = $lead->created_at; $row['company_name'] = $lead->form->area->company->name; $row['area_name'] = $lead->form->area->name; $row['form_name'] = $lead->form->name; $row['utm_source'] = $lead->utm_source; $row['utm_medium'] = $lead->utm_medium; $row['utm_campaign'] = $lead->utm_campaign; $row['utm_content'] = $lead->utm_content; $row['utm_term'] = $lead->utm_term; $row['referer'] = $lead->referer; $keys[] = 'id'; $keys[] = 'date'; $keys[] = 'created_at'; $keys[] = 'company_name'; $keys[] = 'area_name'; $keys[] = 'form_name'; $keys[] = 'utm_source'; $keys[] = 'utm_medium'; $keys[] = 'utm_campaign'; $keys[] = 'utm_content'; $keys[] = 'utm_term'; $keys[] = 'referer'; // inject values foreach ($lead->values as $lead_value) { $row[$lead_value->key] = $lead_value->value; if (!in_array($lead_value->key, $keys)) { $keys[] = $lead_value->key; } } $data[] = $row; } //dd($data); return DataGrid::make($data, $keys, ['sort' => 'id', 'direction' => 'desc', 'csv_delimiter' => ';']); }
/** * Execute the console command. * * @return mixed */ public function handle() { $notification_queue = Lead::where('notify', '=', 1)->get(); foreach ($notification_queue as $lead) { if ($lead) { if (!empty($lead->form->integration) && !empty($lead->form->integration_id)) { $integration = new $lead->form->integration->class($lead->id, json_decode($lead->form->integration_options, true)); } } if (!empty($lead->form->notification_emails) && !empty($lead->form->notification_subject)) { $emails = explode(',', $lead->form->notification_emails); if (is_array($emails)) { // Internal e-mail notification try { Mail::send('ZLeader::lead.email-internal-notification', ['lead' => $lead], function ($message) use($emails, $lead) { $message->from(config('ZLeader.notification_sender_email_address'), $lead->form->area->company->name); $message->subject($lead->form->notification_subject); $reply_email = trim($lead->getValueByKey('email')); $message->replyTo($reply_email); foreach ($emails as $email) { if (!empty(trim($email))) { $message->to(trim($email)); } } }); } catch (\Exception $e) { Log::error($e->getMessage()); } } } try { // User e-mail notification $email = trim($lead->getValueByKey('email')); if (!empty($email) && !empty($lead->form->user_notification_subject)) { Mail::send('ZLeader::lead.email-user-notification', ['lead' => $lead], function ($message) use($email, $lead) { $message->from(config('ZLeader.notification_sender_email_address'), $lead->form->area->company->name); $message->subject($lead->form->user_notification_subject); $message->to($email); }); } } catch (\Exception $e) { Log::error($e->getMessage()); } $lead->notify = 0; $lead->save(); } }
public function store(Request $request) { $access_token = env('FACEBOOK_LEADGEN_ACCESS_TOKEN', 'token'); Log::info('New Webhook:'); if (is_array($request->entry)) { foreach ($request->entry[0]['changes'] as $change_data) { if ($change_data['field'] == 'leadgen') { $leadgen = $change_data['value']; $lead_exists = Lead::where('fb_leadgen_id', '=', $leadgen['leadgen_id'])->count(); if ($lead_exists == 0) { Log::info('New Lead!'); try { $response = Facebook::get('/' . $leadgen['leadgen_id'], $access_token); $lead_data = $response->getDecodedBody(); //Log::info('Lead values:'); //Log::info($lead_data); $leadgen['lead_values'] = $lead_data['field_data']; } catch (Exception $e) { Log::error($e->getMessage()); } try { $response = Facebook::get('/' . $leadgen['form_id'], $access_token); $form_data = $response->getDecodedBody(); //Log::info('Form data:'); //Log::info($form_data); $leadgen['form_name'] = $form_data['name']; } catch (Exception $e) { Log::error($e->getMessage()); } if (!empty($leadgen['ad_id'])) { try { $response = Facebook::get('/' . $leadgen['ad_id'] . '?fields=name,adset_id', $access_token); $ad_data = $response->getDecodedBody(); //Log::info('Ad data:'); //Log::info($ad_data); $leadgen['ad_name'] = $ad_data['name']; } catch (Exception $e) { Log::error($e->getMessage()); } try { $response = Facebook::get('/' . $ad_data['adset_id'] . '?fields=name', $access_token); $adset_data = $response->getDecodedBody(); //Log::info('Ad data:'); //Log::info($ad_data); $leadgen['adset_name'] = $adset_data['name']; } catch (Exception $e) { Log::error($e->getMessage()); } } Log::info($leadgen); $form_name_exploded = explode(' ', $leadgen['form_name']); $form_integration_prefix = array_shift($form_name_exploded); $form = Form::where('fb_integration_prefix', 'like', $form_integration_prefix)->first(); Log::info($form); if ($form) { $lead = new Lead(); $lead->form_id = $form->id; $lead->fb_leadgen_id = $leadgen['leadgen_id']; $lead->utm_source = 'facebook'; $lead->utm_medium = 'cpc'; $lead->utm_content = 'leadgen'; if (!empty($leadgen['ad_name'])) { $lead->utm_term = $leadgen['ad_name']; } if ($request->utm_content) { $lead->utm_content = $leadgen['adset_name']; } $lead->save(); foreach ($leadgen['lead_values'] as $field) { if ($field['name'] == 'email') { $validator = Validator::make(['email' => $field['values'][0]], ['email' => 'email']); if (!$validator->fails()) { $leadValue = new LeadValue(); $leadValue->lead_id = $lead->id; $leadValue->key = $field['name']; $leadValue->value = $field['values'][0]; $leadValue->save(); } } else { $leadValue = new LeadValue(); $leadValue->lead_id = $lead->id; $leadValue->key = $field['name']; $leadValue->value = $field['values'][0]; $leadValue->save(); } } $form_name_fields = explode(',', implode(' ', $form_name_exploded)); if (is_array($form_name_fields)) { foreach ($form_name_fields as $fields) { $field_data = explode('=', $fields); if (isset($field_data[0]) && isset($field_data[1])) { $data_key = trim($field_data[0]); $aux = explode(' ', $field_data[1]); $data_value = trim(array_shift($aux)); if (!empty($data_key) && !empty($data_value)) { $leadValue = new LeadValue(); $leadValue->lead_id = $lead->id; $leadValue->key = $data_key; $leadValue->value = $data_value; $leadValue->save(); } } } } Log::info('Lead saved'); } else { Log::warning('Form "' . $form_integration_prefix . '" not found!'); switch ($leadgen['form_name']) { default: Log::warning('Unknown form, reporting...'); $company_name = 'Leadgen'; $emails = 'andres@zephia.com.ar,cristian@zephia.com.ar'; } Log::info('Sending "' . $leadgen['form_name'] . '" to ' . $emails); $emails = explode(',', $emails); Mail::send('ZLeader::lead.email-webhook-failed', ['lead' => $leadgen], function ($message) use($emails, $company_name) { $message->from(config('ZLeader.notification_sender_email_address'), $company_name); $message->subject('Nueva consulta de Facebook leadgen'); //$message->replyTo($address); foreach ($emails as $email) { $message->to($email); } }); abort(404); } } else { Log::warning('Lead exists!'); } } } } return $request->hub_challenge; }
public function store(Request $request, $form_slug) { $forms = new Form(); $form = $forms->whereSlug($form_slug)->firstOrFail(); DB::beginTransaction(); try { $lead = new Lead(); $lead->form_id = $form->id; if ($request->header('referer')) { $lead->referer = $request->header('referer'); } $lead->remote_ip = $request->ip(); $lead->user_agent = $request->header('User-Agent'); if ($request->utm_source) { $lead->utm_source = $request->utm_source; } if ($request->utm_medium) { $lead->utm_medium = $request->utm_medium; } if ($request->utm_term) { $lead->utm_term = $request->utm_term; } if ($request->utm_content) { $lead->utm_content = $request->utm_content; } if ($request->utm_campaign) { $lead->utm_campaign = $request->utm_campaign; } $lead->save(); $input = $request->all(); foreach ($input as $name => $value) { $result_array = explode('_', $name); $prefix = array_shift($result_array); $key = implode('_', $result_array); if ($prefix == 'zlfield') { if ($key == 'email') { $validator = Validator::make(['email' => $value], ['email' => 'email']); if (!$validator->fails()) { $leadValue = new LeadValue(); $leadValue->lead_id = $lead->id; $leadValue->key = $key; $leadValue->value = $value; $leadValue->save(); } } else { $leadValue = new LeadValue(); $leadValue->lead_id = $lead->id; $leadValue->key = $key; $leadValue->value = $value; $leadValue->save(); } } } } catch (Exception $e) { DB::rollback(); throw $e; } DB::commit(); if (!empty($request->wpcf7)) { return Response::json(['mailSent' => true, 'captcha' => null, 'message' => 'Su mensaje se ha enviado con éxito. Muchas gracias.']); } else { if (empty($form->feedback_url)) { return; } else { return Redirect::to($form->feedback_url); } } }
public function index(Request $request) { $colors = ['blue', 'yellow', 'red', 'green', 'aqua', 'navy', 'teal', 'olive', 'lime', 'orange', 'fuchsia', 'purple', 'maroon', 'black', 'gray']; $hex_colors = [['color' => '#f56954', 'highlight' => '#f56954'], ['color' => '#00a65a', 'highlight' => '#00a65a'], ['color' => '#f39c12', 'highlight' => '#f39c12'], ['color' => '#00c0ef', 'highlight' => '#00c0ef'], ['color' => '#3c8dbc', 'highlight' => '#3c8dbc'], ['color' => '#d2d6de', 'highlight' => '#d2d6de']]; $area_data = []; $company_id = null; if (app('user') !== false) { if (app('user')->inRole(app('users_role'))) { $company_id = app('user')->company_id; } elseif (app('user')->inRole(app('admins_role'))) { $companies = Company::all(); if ($request->has('company_id')) { $company_id = $request->company_id; } } } if ($request->has('df')) { $date_from = new Carbon($request->df); } if ($request->has('dt')) { $date_to = new Carbon($request->dt); } if (isset($company_id)) { $area_data = []; $company = Company::find($company_id); $months_db = DB::table('zleader_leads')->select('zleader_leads.created_at', DB::raw('MONTH(zleader_leads.created_at) as month'), DB::raw('YEAR(zleader_leads.created_at) as year'))->groupBy('year', 'month'); if (isset($date_from) && isset($date_to)) { $months_db->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } $months = $months_db->get(); foreach ($company->areas as $area) { $months_formated = []; foreach ($months as $month) { $months_db = DB::table('zleader_leads')->leftJoin('zleader_forms', 'zleader_leads.form_id', '=', 'zleader_forms.id')->select(DB::raw('COUNT(*) as total'))->where('zleader_forms.area_id', '=', $area->id)->where(DB::raw("MONTH(zleader_leads.created_at)"), "=", DB::raw("MONTH(STR_TO_DATE('" . $month->month . "', '%m'))"))->where(DB::raw("YEAR(zleader_leads.created_at)"), "=", DB::raw("YEAR(STR_TO_DATE('" . $month->year . "', '%Y'))")); if (isset($date_from) && isset($date_to)) { $months_db->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } $months_data = $months_db->first(); $formated_month = Carbon::parse($month->created_at)->formatLocalized('%B'); $months_formated[] = ['month' => $formated_month, 'total' => $months_data->total]; } $area_data[] = ['area_name' => $area->name, 'months' => $months_formated]; } } // Companies count if (isset($company_id)) { $companies_data = Company::where('id', '=', $company_id)->get(); } else { $companies_data = Company::all(); } $lead = new Lead(); $companies_count = []; $company_index = 0; foreach ($companies_data as $company) { $company_item = []; $company_item['name'] = $company->name; $company_item['image'] = $company->image; $count_data = DB::table('zleader_leads')->select(DB::raw('count(*) as lead_count'))->join('zleader_forms', 'zleader_forms.id', '=', 'zleader_leads.form_id')->join('zleader_areas', 'zleader_areas.id', '=', 'zleader_forms.area_id')->where('zleader_areas.company_id', '=', $company->id); if (isset($date_from) && isset($date_to)) { $count_data->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } $count_result = $count_data->get(); $company_item['count'] = count($count_result) > 0 ? $count_result[0]->lead_count : 0; $areas_count = []; $area_index = 0; foreach ($company->areas as $area) { $areas_item = []; $areas_item['name'] = $area->name; $count_data_area = DB::table('zleader_leads')->select(DB::raw('count(*) as lead_count'))->join('zleader_forms', 'zleader_forms.id', '=', 'zleader_leads.form_id')->where('zleader_forms.area_id', '=', $area->id); if (isset($date_from) && isset($date_to)) { $count_data_area->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } $count_result_area = $count_data_area->get(); $areas_item['count'] = count($count_result_area) > 0 ? $count_result_area[0]->lead_count : 0; $areas_item['index'] = $area_index; $areas_count[] = (object) $areas_item; $area_index++; } if (count($company->areas) > 0) { $company_item['areas'] = $areas_count; } $company_item['index'] = $company_index; $companies_count[] = (object) $company_item; $company_index++; } // Leads por medium $leads_data = DB::table('zleader_leads')->select('utm_medium as name', DB::raw('count(*) as total'))->groupBy('utm_medium'); if (isset($company_id)) { $leads_data->join('zleader_forms', 'zleader_forms.id', '=', 'zleader_leads.form_id')->join('zleader_areas', 'zleader_areas.id', '=', 'zleader_forms.area_id')->where('zleader_areas.company_id', '=', $company_id); } if (isset($date_from) && isset($date_to)) { $leads_data->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } $leads_medium = $leads_data->get(); // Leads por source $leads_data = DB::table('zleader_leads')->select('utm_source as name', DB::raw('count(*) as total'), DB::raw('@curRow := @curRow + 1 AS row_number'))->where('utm_source', '!=', '')->groupBy('utm_source'); if (isset($date_from) && isset($date_to)) { $leads_data->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } if (isset($company_id)) { $leads_data->join('zleader_forms', 'zleader_forms.id', '=', 'zleader_leads.form_id')->join('zleader_areas', 'zleader_areas.id', '=', 'zleader_forms.area_id')->where('zleader_areas.company_id', '=', $company_id); } $leads_source = $leads_data->get(); // bar chart if (isset($company_id)) { $companies_data = Company::where('id', '=', $company_id)->get(); } else { $companies_data = Company::all(); } $bar_chart_data = []; $months_db = DB::table('zleader_leads')->select('zleader_leads.created_at', DB::raw('MONTH(zleader_leads.created_at) as month'), DB::raw('YEAR(zleader_leads.created_at) as year'))->groupBy('year', 'month'); if (isset($date_from) && isset($date_to)) { $months_db->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } $months = $months_db->get(); //dd($months); foreach ($companies_data as $company) { $months_formated = []; foreach ($months as $month) { $months_db = DB::table('zleader_leads')->leftJoin('zleader_forms', 'zleader_leads.form_id', '=', 'zleader_forms.id')->leftJoin('zleader_areas', 'zleader_forms.area_id', '=', 'zleader_areas.id')->select(DB::raw('COUNT(*) as total'))->where('zleader_areas.company_id', '=', $company->id)->where(DB::raw("MONTH(zleader_leads.created_at)"), "=", DB::raw("MONTH(STR_TO_DATE('" . $month->month . "', '%m'))"))->where(DB::raw("YEAR(zleader_leads.created_at)"), "=", DB::raw("YEAR(STR_TO_DATE('" . $month->year . "', '%Y'))")); if (isset($date_from) && isset($date_to)) { $months_db->whereDate('zleader_leads.created_at', '>=', $date_from)->whereDate('zleader_leads.created_at', '<=', $date_to); } $months_data = $months_db->first(); $formated_month = Carbon::parse($month->created_at)->formatLocalized('%B'); $months_formated[] = ['month' => $formated_month, 'total' => $months_data->total]; } $bar_chart_data[] = ['company_name' => $company->name, 'months' => $months_formated]; } //dd($bar_chart_data); // Platforms $leads_model = Lead::query(); if (isset($company_id)) { $leads_model->whereHas('form', function ($query) use($company_id) { return $query->whereHas('area', function ($query) use($company_id) { return $query->where('company_id', '=', $company_id); }); }); } $leads = $leads_model->get(); $platforms_count = ['Desktop' => 0, 'Mobile' => 0, 'Tablet' => 0]; $platforms_data = ['Desktop' => 0, 'Mobile' => 0, 'Tablet' => 0]; $platforms_total = 0; foreach ($leads as $lead) { if (!empty($lead->remote_platform)) { $platforms_total++; $platforms_count[$lead->remote_platform]++; } } foreach ($platforms_count as $key => $value) { if ($value !== 0) { $platforms_data[$key] = $value / $platforms_total * 100; } } //dd($platforms_count); return view('ZLeader::dashboard', ['companies' => isset($companies) ? $companies : null, 'companies_data' => $companies_data, 'colors' => $colors, 'hex_colors' => $hex_colors, 'companies_count' => $companies_count, 'leads_medium' => $leads_medium, 'leads_source' => $leads_source, 'area_data' => $area_data, 'bar_chart_data' => $bar_chart_data, 'platforms_data' => $platforms_data, 'company_id' => $company_id, 'date_from' => isset($date_from) ? $date_from : null, 'date_to' => isset($date_to) ? $date_to : null]); }