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