/**
* Show the application dashboard to the user.
*
* @return Response
*/
public function index()
{
$articles = Article::with('author')->orderBy('position', 'DESC')->orderBy('created_at', 'DESC')->limit(4)->get();
// TODO: abstract to model
$sliders = Photo::join('photo_albums', 'photo_albums.id', '=', 'photos.photo_album_id')->where('photos.slider', 1)->orderBy('photos.position', 'DESC')->orderBy('photos.created_at', 'DESC')->select('photos.filename', 'photos.name', 'photos.description', 'photo_albums.folder_id')->get();
$photoAlbums = PhotoAlbum::select(array('photo_albums.id', 'photo_albums.name', 'photo_albums.description', 'photo_albums.folder_id', DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id LIMIT 1) AS album_image'), DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
$videoAlbums = VideoAlbum::select(array('video_albums.id', 'video_albums.name', 'video_albums.description', 'video_albums.folder_id', DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id LIMIT 1) AS album_image'), DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
$categoriesArray = Object::where('type', 'category')->get();
$categories = array();
foreach ($categoriesArray as $category) {
$categories[] = array($category->title);
}
$categories = Object::where('type', 'category')->get();
global $types;
$types = Object::getTypes()->select(array('id', DB::raw("REPLACE(name, '_object_type_', '') as name"), DB::raw("REPLACE(title, 'Object Type: ', '') as title"), 'created_at'))->get();
return view('pages.home', compact('articles', 'sliders', 'videoAlbums', 'photoAlbums', 'categories', 'types'));
//return view('pages.welcome');
}
public function getSearch()
{
$categoryId = Input::get('categoryid');
$search = Input::get('query');
$objects = null;
if ($categoryId) {
// Category Image
if ($featuredImageId = ObjectMeta::getValue($categoryId, '_featured_image')) {
$featuredImageUrl = getImageSrc($featuredImageId, 'thumbnail');
}
// Get objects where in category
$objects = Object::Where('type', 'object_type')->whereExists(function ($query) use($categoryId) {
$query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.object_id = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_category_id')->where('meta_value', $categoryId);
})->select(DB::raw('substr(name, 14) as field_name'))->get()->toArray();
$types = array_map(function ($v) {
return $v['field_name'];
}, $objects);
if (!empty($types)) {
$objects = Object::whereIn('type', $types);
}
}
if ($objects) {
$objects = $objects->select(array('objects.id', DB::raw('"/uploads/' . $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt'))->get();
}
return $objects;
}
public function scopeCreatedAt($query)
{
return $query->where(function ($query) {
$prefix = DB::getTablePrefix();
$this_table = $prefix . $this->table;
$query->select(DB::raw('count(*) as resultcount'), DB::raw('ROUND(UNIX_TIMESTAMP(created_at)/(15 * 60)) AS timekey'))->from($prefix . 'results')->groupBy('timekey');
});
}
/**
* Get all galleries with files_count for back end.
*
* @return \Illuminate\Database\Eloquent\Collection
*/
public function allRaw()
{
$query = $this->make()->select('galleries.id', 'name', 'image', 'status', DB::raw('COUNT(' . DB::getTablePrefix() . 'files.id) as files_count'))->leftJoin('files', function ($join) {
$join->on('gallery_id', '=', 'galleries.id');
})->groupBy('galleries.id')->order();
// Get
return $query->get();
}
public function index()
{
$news = $this->news->orderBy('position', 'DESC')->orderBy('created_at', 'DESC')->limit(4)->get();
$sliders = Photo::join('photo_album', 'photo_album.id', '=', 'photo.photo_album_id')->where('photo.slider', 1)->orderBy('photo.position', 'DESC')->orderBy('photo.created_at', 'DESC')->select('photo.filename', 'photo.name', 'photo.description', 'photo_album.folderid')->get();
$photoalbums = PhotoAlbum::select(array('photo_album.id', 'photo_album.name', 'photo_album.description', 'photo_album.folderid', DB::raw('(select filename from ' . DB::getTablePrefix() . 'photo WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'photo.photo_album_id=' . DB::getTablePrefix() . 'photo_album.id) AS album_image'), DB::raw('(select filename from ' . DB::getTablePrefix() . 'photo WHERE ' . DB::getTablePrefix() . 'photo.photo_album_id=' . DB::getTablePrefix() . 'photo_album.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
$videoalbums = VideoAlbum::select(array('video_album.id', 'video_album.name', 'video_album.description', 'video_album.folderid', DB::raw('(select youtube from ' . DB::getTablePrefix() . 'video as v WHERE album_cover=TRUE and v.video_album_id=' . DB::getTablePrefix() . 'video_album.id) AS album_image'), DB::raw('(select youtube from ' . DB::getTablePrefix() . 'video WHERE ' . DB::getTablePrefix() . 'video.video_album_id=' . DB::getTablePrefix() . 'video_album.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
return view('site.home.index', compact('news', 'sliders', 'videoalbums', 'photoalbums'));
}
/**
* Show the application dashboard to the user.
*
* @return Response
*/
public function index()
{
$articles = Article::with('author')->orderBy('position', 'DESC')->orderBy('created_at', 'DESC')->limit(4)->get();
// TODO: abstract to model
$sliders = Photo::join('photo_albums', 'photo_albums.id', '=', 'photos.photo_album_id')->where('photos.slider', 1)->orderBy('photos.position', 'DESC')->orderBy('photos.created_at', 'DESC')->select('photos.filename', 'photos.name', 'photos.description', 'photo_albums.folder_id')->get();
$photoAlbums = PhotoAlbum::select(array('photo_albums.id', 'photo_albums.name', 'photo_albums.description', 'photo_albums.folder_id', DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id LIMIT 1) AS album_image'), DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
$videoAlbums = VideoAlbum::select(array('video_albums.id', 'video_albums.name', 'video_albums.description', 'video_albums.folder_id', DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id LIMIT 1) AS album_image'), DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
return view('pages.home', compact('articles', 'sliders', 'videoAlbums', 'photoAlbums'));
//return view('pages.welcome');
}
public function getSearch()
{
$categoryId = Input::get('categoryid');
$index = Input::get('index') ?: 0;
$search = Input::get('query');
$objects = null;
if ($categoryId) {
// Category Image
if ($categoryFeaturedImageId = ObjectMeta::getValue($categoryId, '_featured_image')) {
$featuredImageUrl = getImageSrc($categoryFeaturedImageId, 'thumbnail');
}
// Get objects where in category
$objects = Object::Where('type', 'object_type')->whereExists(function ($query) use($categoryId) {
$query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.object_id = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_category_id')->where('meta_value', $categoryId);
})->select(DB::raw('substr(name, 14) as field_name'))->get()->toArray();
$types = array_map(function ($v) {
return $v['field_name'];
}, $objects);
$objects = DB::table('objects')->whereIn('type', $types)->select(array('objects.id', DB::raw('"/uploads/' . $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt'));
$objects = DB::table('objects')->whereIn('type', $types)->select(array('objects.id', DB::raw('"/uploads/' . $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt'))->whereExists(function ($query) {
$query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.object_id = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_field_promoted')->where('meta_value', '1');
})->union($objects);
} else {
if ($search) {
$objects = Object::whereNotIn('type', ['object_type', 'image'])->where(function ($query) use($search) {
$query->where('title', 'LIKE', '%' . $search . '%')->orWhere('name', 'LIKE', '%' . $search . '%');
})->select('id', 'type', 'title')->get();
return response($objects);
}
}
if ($objects) {
$objects = $objects->skip($index)->take(50)->get();
foreach ($objects as $object) {
$this->processObject($object);
}
}
return $objects;
}
/**
* Returns the table for a given model. Model can be an Eloquent model object, or a full namespaced
* class string.
*
* @param string|Model $model
*
* @return mixed
*/
protected function getModelTable($model)
{
if (!is_object($model)) {
$model = new $model();
}
return DB::getTablePrefix() . $model->getTable();
}
/**
* Show a list of all the languages posts formatted for Datatables.
*
* @return Datatables JSON
*/
public function data($albumid = 0)
{
$condition = intval($albumid) == 0 ? ">" : "=";
$videoalbum = Video::join('languages', 'languages.id', '=', 'videos.language_id')->join('video_albums', 'video_albums.id', '=', 'videos.video_album_id')->where('videos.video_album_id', $condition, $albumid)->orderBy('videos.position')->select(array('videos.id', DB::raw($albumid . ' as albumid'), DB::getTablePrefix() . 'videos.name', 'video_albums.name as category', DB::getTablePrefix() . 'videos.album_cover', 'languages.name as language', DB::getTablePrefix() . 'videos.created_at'));
return Datatables::of($videoalbum)->edit_column('album_cover', '<a href="{{{ URL::to(\'admin/video/\' . $id . \'/\' . $albumid . \'/albumcover\' ) }}}" class="btn btn-warning btn-sm" >@if ($album_cover=="1") <span class="glyphicon glyphicon-ok"></span> @else <span class=\'glyphicon glyphicon-remove\'></span> @endif</a>')->add_column('actions', '<a href="{{{ URL::to(\'admin/video/\' . $id . \'/edit\' ) }}}" class="btn btn-success btn-sm iframe" ><span class="glyphicon glyphicon-pencil"></span> {{ Lang::get("admin/modal.edit") }}</a>
<a href="{{{ URL::to(\'admin/video/\' . $id . \'/delete\' ) }}}" class="btn btn-sm btn-danger iframe"><span class="glyphicon glyphicon-trash"></span> {{ Lang::get("admin/modal.delete") }}</a>
<input type="hidden" name="row" value="{{$id}}" id="row">')->remove_column('id')->remove_column('albumid')->make();
}
public function getSearch()
{
$categoryId = Input::get('categoryid');
$index = Input::get('index') ?: 0;
$search = Input::get('query');
$offset = (int) Input::get('offset');
$limit = 10;
$objects = null;
if ($categoryId) {
// Category Image
$featuredImageUrl = '';
if ($categoryFeaturedImageId = ObjectMeta::getValue($categoryId, '_featured_image')) {
// $featuredImageUrl = getImageSrc($categoryFeaturedImageId, 'thumbnail');
$featuredImageUrl = 'cropped/' . getTheImageSize(getImageSrc($categoryFeaturedImageId), '_featured_image');
}
$objects = Object::whereNotIn('objects.type', ['object_type', 'image', 'category'])->where(function ($query) use($search) {
$query->where('objects.title', 'LIKE', '%' . $search . '%')->orWhere('objects.content', 'LIKE', '%' . $search . '%')->orWhere('keywords.content', 'LIKE', '%' . $search . '%');
})->join('objects as obj', function ($join) {
$join->on('obj.name', '=', DB::raw("concat( '_object_type_', objects.type )"));
})->leftJoin('keywords', function ($join) {
$join->on('obj.id', '=', 'object_id')->orOn('objects.id', '=', 'object_id');
})->join('object_meta', function ($join) {
$join->on('object_meta.object_id', '=', 'obj.id')->where('object_meta.meta_key', '=', '_category_id');
})->join('objects as cat', function ($join) {
$join->on('cat.id', '=', 'object_meta.meta_value');
})->where('object_meta.meta_value', $categoryId)->select(DB::raw("'{$featuredImageUrl}' AS featured_image"), 'cat.id AS catID', 'cat.title AS catTitle', 'cat.name AS catName', 'obj.id AS objID', 'obj.name AS objName', 'objects.id', 'objects.excerpt', 'objects.parent_id', 'objects.name', 'objects.type', 'objects.title', 'objects.score')->orderBy('objects.score', 'DESC')->orderBy('objects.title', 'ASC')->groupBy('objects.id')->take($limit)->skip($offset);
// dd($objects->toSql());
$objects = $objects->get();
foreach ($objects as $object) {
$this->processObject($object);
}
foreach ($objects as &$object) {
unset($object['address']);
unset($object['address_city']);
unset($object['address_country']);
unset($object['address_street']);
unset($object['catID']);
// unset($object['catName']);
unset($object['catTitle']);
unset($object['email']);
unset($object['objID']);
unset($object['objName']);
unset($object['occupation']);
unset($object['parent_id']);
unset($object['phone']);
unset($object['promoted']);
unset($object['type']);
if ($featuredImageUrl == $object['featured_image']) {
unset($object['featured_image']);
}
}
// dd($objects->toArray());
return ['items' => $objects, 'offset' => count($objects) >= $limit ? $offset + $limit : 0];
} else {
if ($search) {
$objects = Object::whereNotIn('objects.type', ['object_type', 'image'])->where(function ($query) use($search) {
$query->where('objects.title', 'LIKE', '%' . $search . '%')->orWhere('objects.content', 'LIKE', '%' . $search . '%')->orWhere('keywords.content', 'LIKE', '%' . $search . '%');
})->leftJoin('objects as obj', function ($join) {
$join->on('obj.name', '=', DB::raw("concat( '_object_type_', objects.type )"));
})->leftJoin('keywords', function ($join) {
$join->on('obj.id', '=', 'object_id')->orOn('objects.id', '=', 'object_id');
})->select('objects.id', 'objects.parent_id', 'objects.name', 'objects.type', 'objects.title', 'objects.score')->orderBy(DB::raw("objects.type = 'category'"), 'DESC')->orderBy('objects.score', 'DESC')->orderBy('objects.title', 'ASC')->groupBy('objects.id')->take(50)->get();
$objects->each(function ($v) {
if ($v->type != 'category') {
$parentObject = Object::where('type', 'object_type')->where('name', '_object_type_' . $v->type)->first();
if ($parentObject) {
$parentCategories = Object::where('type', 'category')->whereExists(function ($query) use($parentObject) {
$query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.meta_value = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_category_id')->where('object_id', $parentObject->id);
})->first();
$v->category = array('id' => $parentCategories ? $parentCategories->id : '0', 'name' => $parentCategories ? $parentCategories->name : 'Home');
}
}
return $v;
});
// dd($objects);
return response($objects);
}
}
// dd($objects);
//dd($objects->toSql());
if ($objects) {
$objects = $objects->skip($index)->take(50)->get();
foreach ($objects as $object) {
$this->processObject($object);
}
}
return $objects;
}
public function scopeOfOrWithResults($query, $relation, $project)
{
return $query->with($relation)->whereExists(function ($query) use($project) {
$this_table = DB::getTablePrefix() . $this->table;
$query->selectRaw(DB::raw('resultable_id'))->from('results')->where('project_id', $project)->whereRaw('resultable_id = ' . $this_table . '.id');
});
}
public function scopeOrNotWithResults($query)
{
return $query->orWhereNotExists(function ($query) {
$this_table = DB::getTablePrefix() . $this->table;
$query->selectRaw(DB::raw('resultable_id'))->from('results')->whereRaw('resultable_id = ' . $this_table . '.id');
});
}
/**
* Makes the query not repeat the results.
*
* @param \Illuminate\Database\Eloquent\Builder $query
*/
protected function makeGroupBy(Builder $query)
{
if ($groupBy = $this->getGroupBy()) {
$query->groupBy($groupBy);
} else {
$driver = $this->getDatabaseDriver();
if ($driver == 'sqlsrv') {
$columns = $this->getTableColumns();
} else {
$columns = $this->getTable() . '.' . $this->primaryKey;
}
$query->groupBy($columns);
$joins = array_keys($this->getJoins());
foreach ($this->getColumns() as $column => $relevance) {
array_map(function ($join) use($column, $query) {
if (Str::contains($column, $join)) {
if (Str::startsWith($column, DB::getTablePrefix())) {
$column = substr($column, strlen(DB::getTablePrefix()));
}
$query->groupBy($column);
}
}, $joins);
}
}
}
/**
* @return mixed
*/
protected function getTableColumns()
{
return DB::select('SHOW COLUMNS FROM ' . DB::getTablePrefix() . $this->name);
}
/**
* Get all galleries with files_count for back end.
*
* @return \Illuminate\Database\Eloquent\Collection
*/
public function allRaw()
{
$query = $this->make(['translations'])->select('id', 'name', 'image', DB::raw('(SELECT COUNT(*) FROM `' . DB::getTablePrefix() . 'files` WHERE `gallery_id` = `' . DB::getTablePrefix() . "galleries`.`id`) AS 'files_count'"))->order();
// Get
return $query->get();
}
