/** * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View */ public function index() { $TicketsTotal = Movie::select(DB::raw('SUM(ticket_price) as TotalCost, AVG(ticket_price) as AverageCostPerTicketInclFree, AVG(CASE WHEN ticket_price > 0 THEN ticket_price ELSE NULL END) AS AverageCostPerTicketExclFree, COUNT(ticket_datetime) as TotalTickets, COUNT(CASE WHEN ticket_price = 0 THEN 1 ELSE NULL END) AS NumberOfFreeTickets, COUNT(CASE WHEN ticket_price = 0 THEN NULL ELSE 1 END) AS NumberOfNotFreeTickets'))->whereNotNull('ticket_datetime')->get(); $AverageTicketPricePerYear = Movie::select(DB::raw('DATE_FORMAT(ticket_datetime, \'%Y-01-01 00:00:00\') AS Year, AVG(ticket_price) as AverageCostPerTicketInclFree, AVG(CASE WHEN ticket_price > 0 THEN ticket_price ELSE NULL END) AS AverageCostPerTicketExclFree'))->whereNotNull('ticket_datetime')->groupby(DB::raw('YEAR(ticket_datetime)'))->orderby(DB::raw('YEAR(ticket_datetime)'))->get(); $LineChartAverageTicketPricePerYear = $this->getLineChartAverageTicketPricePerYear($AverageTicketPricePerYear); $NumberOfTicketsPerYear = Movie::select(DB::raw('DATE_FORMAT(ticket_datetime, \'%Y-01-01 00:00:00\') AS Year, COUNT(CASE WHEN ticket_price = 0 THEN 1 ELSE NULL END) AS NumberOfFreeTickets, COUNT(CASE WHEN ticket_price = 0 THEN NULL ELSE 1 END) AS NumberOfNotFreeTickets'))->whereNotNull('ticket_datetime')->groupby(DB::raw('YEAR(ticket_datetime)'))->orderby(DB::raw('YEAR(ticket_datetime)'))->get(); $BarChartNumberOfTicketsPerYear = $this->getBarChartNumberOfTicketsPerYear($NumberOfTicketsPerYear->transform(function (Movie $item) { if ($item->NumberOfNotFreeTickets == 0) { $item->NumberOfNotFreeTickets = null; } if ($item->NumberOfFreeTickets == 0) { $item->NumberOfFreeTickets = null; } return $item; })); $TotalCostPerYear = Movie::select(DB::raw('DATE_FORMAT(ticket_datetime, \'%Y-01-01 00:00:00\') AS Year, SUM(ticket_price) as TotalCost'))->whereNotNull('ticket_datetime')->groupby(DB::raw('YEAR(ticket_datetime)'))->orderby(DB::raw('YEAR(ticket_datetime)'))->get(); $LineTotalCostPerYear = $this->getLineTotalCostPerYear($TotalCostPerYear); return view('pages.Movie.ticketstats', ['TicketsTotal' => $TicketsTotal[0], 'LineChartAverageTicketPricePerYear' => $LineChartAverageTicketPricePerYear, 'BarChartNumberOfTicketsPerYear' => $BarChartNumberOfTicketsPerYear, 'LineTotalCostPerYear' => $LineTotalCostPerYear]); }
/** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $movies = Movie::select('movies.id', 'movies.name', DB::raw("SUM(movie_ratings.rating) AS rating"))->leftJoin('movie_ratings', 'movie_ratings.movie_id', '=', 'movies.id')->groupBy('movies.id')->orderBy(DB::raw('rating'), 'DESC')->get(); return view('_table')->with(compact('movies')); }
/** * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View */ public function index() { $title = 'Movie Tickets'; $tickets = Movie::select('title', 'slug', 'year', 'last_watched_at')->where('ticket_price', null)->orderby('last_watched_at', 'asc')->get(); return view('admin.dashboard.movietickets.movieticketspage', compact('title', 'tickets')); }