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]);
}
