consulta.php

public function reportLink($link)
    {

        $link = $link->domain;

        /*$visits = DB::table('tracker_page_trackings')
                ->select(DB::raw('count(DISTINCT dimension1) as visits'), DB::raw('count(*) as pageViews'))
                ->where('pagePathLevel1', 'like', '%'.$link.'%')
                ->get();*/

        $visits = TrackerPageTrack::where('pagePathLevel1', 'like', '%'.$link.'%')
            ->select(DB::raw('count(DISTINCT dimension1) as visits'), DB::raw('count(*) as pageViews'))->get();


        /*$clicks = DB::table('tracker_clicks')
             ->select(DB::raw('count(DISTINCT dimension1, link_streaming_id) as clicks'), DB::raw('count(*) as pageViews'))
             ->where('domain', 'like', '%'.$link.'%')
             ->get();*/

        $clicks = TrackerClick::where('domain', 'like', '%'.$link.'%')
            ->select(DB::raw('count(DISTINCT dimension1, link_streaming_id) as clicks'), DB::raw('count(*) as pageViews'))
            ->get();

         /*$tackerPageTrackings = DB::table('tracker_page_trackings')
             ->select('reportDateStart as reportDate', DB::raw('count(DISTINCT dimension1) as visits'), DB::raw('count(*) as pageViews'))
             ->where('pagePathLevel1', 'like', '%'.$link.'%')
             ->groupby('reportDateStart')
             ->get();*/

         $tackerPageTrackings = TrackerPageTrack::where('pagePathLevel1', 'like', '%'.$link.'%')
             ->select('reportDateStart as reportDate', DB::raw('count(DISTINCT dimension1) as visits'), DB::raw('count(*) as pageViews'))
             ->groupby('reportDateStart')
             ->get();


         $trackerPageGeoNetworkCity = DB::table('tracker_geo_networks')
             ->join('tracker_users', 'tracker_geo_networks.dimension1', '=', 'tracker_users.dimension1')
             ->join('tracker_page_trackings', 'tracker_geo_networks.dimension1', '=', 'tracker_page_trackings.dimension1')
             ->select('tracker_geo_networks.city', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
             ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
             ->groupby('city')
             ->get();

        $trackerPageGeoNetworkCity = TrackerGeoNetwork::where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
            ->join('tracker_users', 'tracker_geo_networks.dimension1', '=', 'tracker_users.dimension1')
            ->join('tracker_page_trackings', 'tracker_geo_networks.dimension1', '=', 'tracker_page_trackings.dimension1')

            ->select('tracker_geo_networks.city', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
            ->groupby('city')
            ->get();


                 $trackerPageGeoNetworkCountry = DB::table('tracker_geo_networks')
                     ->join('tracker_users', 'tracker_geo_networks.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_geo_networks.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_geo_networks.country', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_geo_networks.country')
                     ->get();

                 $trackerChannels = DB::table('tracker_channels')
                     ->join('tracker_users', 'tracker_channels.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_channels.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_channels.channelGrouping', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_channels.channelGrouping')
                     ->get();

                 $trackerSources = DB::table('tracker_sources')
                     ->join('tracker_users', 'tracker_sources.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_sources.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_sources.source', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_sources.source')
                     ->get();

                 $trackerDeviceCategory = DB::table('tracker_devices')
                     ->join('tracker_users', 'tracker_devices.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_devices.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_devices.deviceCategory', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_devices.deviceCategory')
                     ->get();

                 $trackerDeviceBrowser = DB::table('tracker_devices')
                     ->join('tracker_users', 'tracker_devices.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_devices.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_devices.browser', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_devices.browser')
                     ->get();

                 $trackerDeviceOperatingSystem = DB::table('tracker_devices')
                     ->join('tracker_users', 'tracker_devices.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_devices.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_devices.operatingSystem', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_devices.operatingSystem')
                     ->get();

                 $trackerTimesWeek = DB::table('tracker_times')
                     ->join('tracker_users', 'tracker_times.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_times.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_times.dayOfWeekName', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_times.dayOfWeekName')
                     ->get();

                 $trackerTimesHour = DB::table('tracker_times')
                     ->join('tracker_users', 'tracker_times.dimension1', '=', 'tracker_users.dimension1')
                     ->join('tracker_page_trackings', 'tracker_times.dimension1', '=', 'tracker_page_trackings.dimension1')
                     ->where('tracker_page_trackings.pagePathLevel1', 'like', '%'.$link.'%')
                     ->select('tracker_times.hour', DB::raw('count(DISTINCT tracker_users.dimension1) as visits'),  DB::raw('count(*) as pageViews'))
                     ->groupby('tracker_times.hour')
                     ->get();




                 return
                     [
                         ['visits' => $visits],
                         ['clicks' => $clicks],
                         ['tackerPageTrackings' => $tackerPageTrackings],
                         ['trackerPageGeoNetworkCity' => $trackerPageGeoNetworkCity],
                         ['trackerPageGeoNetworkCountry' => $trackerPageGeoNetworkCountry],
                         ['trackerChannels' => $trackerChannels],
                         ['trackerSources' => $trackerSources],
                         ['trackerDeviceCategory' => $trackerDeviceCategory],
                         ['trackerDeviceBrowser' => $trackerDeviceBrowser],
                         ['trackerDeviceOperatingSystem' => $trackerDeviceOperatingSystem],
                         ['trackerTimesWeek' => $trackerTimesWeek],
                         ['trackerTimesHour' => $trackerTimesHour],
                     ];

        //return (['visits' => $visits, 'clicks' => $clicks, 'tackerPageTrackings' => $tackerPageTrackings, 'trackerPageGeoNetworkCity' => $trackerPageGeoNetworkCity]);


    }