/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index(Request $request)
{
$querystringArray = $request->input();
// set default values
$orderBy = isset($request->orderby) ? $request->orderby : 'title';
$order = isset($request->order) ? $request->order : 'ASC';
// with filtering?
if (isset($request->filterby) && $request->filterby != 'songs' && isset($request->filtervalue)) {
// we need to set the Pagination currentPage to 0,
// otherwise we would not see the search results
if (isset($request->page)) {
$currentPage = 0;
Paginator::currentPageResolver(function () use($currentPage) {
return $currentPage;
});
$querystringArray = [];
}
if ($request->filterby == 'fulltext') {
$songs = Song::withCount('items')->orderBy($orderBy, $order)->where('title', 'like', '%' . $request->filtervalue . '%')->orWhere('title_2', 'like', '%' . $request->filtervalue . '%')->orWhere('author', 'like', '%' . $request->filtervalue . '%')->orWhere('book_ref', 'like', '%' . $request->filtervalue . '%')->orWhere('lyrics', 'like', '%' . $request->filtervalue . '%');
} elseif ($request->filterby == 'title') {
$songs = Song::withCount('items')->orderBy($orderBy, $order)->where('title', 'like', '%' . $request->filtervalue . '%')->orWhere('title_2', 'like', '%' . $request->filtervalue . '%');
} elseif ($request->filtervalue == '_') {
$songs = Song::withCount('items')->orderBy($orderBy, $order)->where($request->filterby, null)->orWhere($request->filterby, '');
} else {
$songs = Song::withCount('items')->orderBy($orderBy, $order)->where($request->filterby, 'like', '%' . $request->filtervalue . '%');
}
} else {
// (the where clause is needed since the 'withCount' would bring in all items with song_id = 0)
$songs = Song::withCount('items')->where('id', '>', 0)->orderBy($orderBy, $order);
}
// if orderBy is 'book_ref', then exclude all songs without a book_ref!
if ($request->orderby == 'book_ref') {
$songs = $songs->where('book_ref', '<>', '');
}
if ($request->orderby == 'author') {
$songs = $songs->where('author', '<>', '');
}
// special case for OrderBy Last Time Used (in a plan!)
if ($request->orderby == 'last_used') {
$songs = Song::withCount('items')->where('songs.id', '>', 0)->leftJoin('items', 'items.song_id', '=', 'songs.id')->leftJoin('plans', 'plans.id', '=', 'items.plan_id')->groupBy('songs.id')->selectRaw('songs.*, max(plans.date) as last_used')->orderBy('last_used', $order);
}
$heading = 'Manage Songs etc.';
if (isset($request->filterby) && $request->filtervalue == 'video') {
$heading = 'Manage Videoclips';
}
if (isset($request->filterby) && $request->filtervalue == 'slides') {
$heading = 'Manage Slides';
}
if (isset($request->filterby) && $request->filterby == 'songs' && isset($request->filtervalue) && $request->filtervalue == 'rare') {
//::
$songs = Song::withCount('items')->where('songs.id', '>', 0)->leftJoin('items', 'items.song_id', '=', 'songs.id')->leftJoin('plans', 'plans.id', '=', 'items.plan_id')->groupBy('songs.id')->selectRaw('songs.*, max(plans.date) as last_used')->havingRaw('count(items.id) > 1')->orderBy('last_used', 'ASC');
}
// URL contains ...?plan_id=xxx (needed in order to add a song to that plan)
$plan_id = 0;
if ($request->has('plan_id')) {
$plan_id = $request->plan_id;
$heading = 'Select A Song For Your Plan';
}
// for pagination, always append the original query string
$songs = $songs->paginate(20)->appends($querystringArray);
return view($this->view_all, array('songs' => $songs, 'heading' => $heading, 'plan_id' => $plan_id, 'currentPage' => $songs->currentPage()));
}
