Skip to content

Instantly share code, notes, and snippets.

@kasirye

kasirye/CourseController.php

Forked from MubinSayed/CourseController.php
Created April 23, 2021 06:07
Show Gist options
  • Save kasirye/5033bc72434c45747ebc3f32c0856430 to your computer and use it in GitHub Desktop.
Save kasirye/5033bc72434c45747ebc3f32c0856430 to your computer and use it in GitHub Desktop.
Download ZIP
Laravel Datatable Dynamic Filter (Advance Filter)
Raw
course.js
var Course = function () {
var lookupTable = function () {
var oTable = $('#course_table').DataTable({
dom: 'Bfrtip',
processing: true,
serverSide: true,
"dom": "<'row'<'col-md-6 col-sm-12'l><'col-md-6 col-sm-12'>r>t<'row'<'col-md-5 col-sm-12'i><'col-md-7 col-sm-12'p>>",
"bStateSave": true, // save datatable state(pagination, sort, etc) in cookie.
"lengthMenu": [
[10, 15, 20, -1],
[10, 15, 20, "All"] // change per page values here
],
// set the initial value
"pageLength": 10,
ajax: {
url: lookup_route,
data: function (d) {
d.course_name = $('input[name=course_name]').val();
d.course_name_operator = $('#course_name_operator').val();
d.course_code = $('input[name=course_code]').val();
d.course_code_operator = $('#course_code_operator').val();
}
},
"drawCallback": function(settings) {
// console.log(settings.json);
$('[data-toggle="tooltip"]').tooltip()
},
columns: [
{ data: 'rownum', name: 'rownum' },
{ data: 'name', name: 'name' },
{ data: 'code', name: 'code' },
{ data: 'description', name: 'description' },
{data: 'action', name: 'action', orderable: false, searchable: false}
]
});
$(document).on('click','.search-form', function(e) {
oTable.draw();
e.preventDefault();
});
$(document).on('keypress','.filterControl', function (e) {
var code = e.keyCode || e.which;
if (code == 13) {
oTable.draw();
e.preventDefault();
}
});
$(document).on('click', '.filter-reset', function(e) {
$('.select2me').each(function () {
$(this).val($(this).find('option:first-child').val()).trigger('change.select2');
});
$('.searchOperator').trigger('change');
$(".filterControl").val("");
$('#select_filter').trigger("reset");
oTable.draw(false);
});
$(document).on('click', '.delete-course', function(event) {
event.preventDefault();
course_id = $(this).data('id');
route = delete_route.replace('-1', course_id);
bootbox.confirm({
title: "Delete course?",
message: "Are you sure want to delete it?",
buttons: {
cancel: {
label: '<i class="fa fa-times"></i> Cancel',
className: 'btn-danger'
},
confirm: {
label: '<i class="fa fa-check"></i> Confirm',
className: 'btn-success'
}
},
callback: function (result) {
// console.log('This was logged in the callback: ' + result);
if (result) {
// alert(course_id);
$.ajax({
url: route,
type:"POST",
// dataType:'json',
data: { '_method' : 'DELETE' },
success: function(data)
{
if (data.success)
{
$('.info-message').html(data.message);
info.show().delay(5000).fadeOut();
oTable.draw(false);
App.scrollTo(info, -200);
}
else
{
$('.error-message').html("Something went wrong.");
error.show().delay(4000).fadeOut();
}
// location.reload();
},
error: function(data)
{
$('.error-message').html("Something went wrong.");
error.show().delay(4000).fadeOut();
console.log('error');
}
});
}
}
});
});
$(document).on('change','.searchOperator',function(){
var input_id = $(this).attr("id");
//alert(input_id);
input_id = input_id.split("_operator");
var input_val = $(this).val();
//alert(input_id[0]);
//alert(input_val);
if(input_val == "=''" || input_val == "!=''")
{
$("#"+input_id[0]).val('');
$("#"+input_id[0]+"_div").hide();
$("#"+input_id[0]+"_range").hide();
}
else if(input_val == "BETWEEN" || input_val == "NOT BETWEEN")
{
$("#"+input_id[0]).val('');
$("#"+input_id[0]+"_div").hide();
$("#"+input_id[0]+"_range").show();
}
else{
$("#"+input_id[0]+"_div").show();
$("#"+input_id[0]+"_range").hide();
}
});
// to focus on Name field (by default) on page load
$('#course_name').focus();
}
return {
//main function
initLookupTable: function () {
lookupTable();
// pageActions();
},
}
}
Raw
CourseController.php
<?php
namespace App\Http\Controllers;
use DB;
use Config;
use App\Models\Course;
use Illuminate\Http\Request;
use Yajra\Datatables\Datatables;
use Illuminate\Support\Facades\Validator;
class CourseController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
return view('course.lookup');
}
public function lookup()
{
// Array having field name as key and column name as value for filter
$this->filterFieldArray = [
'course_name' => 'name',
'course_code' => 'code',
];
DB::statement(DB::raw('set @rownum=0'));
$course = Course::select([
'*',
DB::raw('@rownum := @rownum + 1 AS rownum') /* increment rownum by 1, for each record */
]);
return Datatables::of($course)
->filter(function ($query) {
foreach ($this->filterFieldArray as $field => $column) {
if(request($field."_operator")=="=''" || request($field."_operator")=="!=''")
{
datatableFilterQuery($query, $column, request($field.'_operator'), request($field), request($field.'_from'), request($field.'_to'));
}
else if (request()->has($field) && (!empty(request($field) ) || !empty( request($field.'_from')) ))
{
datatableFilterQuery($query, $column, request($field.'_operator'), request($field), request($field.'_from'), request($field.'_to'));
}
}
})
->addColumn('action', function ($course) {
return '<a href="'. route('course.edit', $course->id) .'" data-toggle="tooltip" title="Edit" class="btn btn-sm btn-primary edit-course" data-id="'.$course->id.'"><i class="fa fa-edit"></i> </a>
<a href="javascript:void(0);" data-toggle="tooltip" title="Delete" class="btn btn-sm btn-danger delete-course" data-id="'.$course->id.'"><i class="fa fa-trash"></i></a>
';
})
->toJson();
// ->make(true);
}
}
Raw
Helper.php
<?php
/*Filter Select Options*/
if (!function_exists('filterOptions')) {
function filterOptions($optArr)
{
$options = '';
if (isset($optArr)) {
foreach ($optArr as $opt) {
switch ($opt) {
case 'e':
$options .= '<option value="=">Equals</option>';
break;
case 'ne':
$options .= '<option value="!=">Not Equal</option>';
break;
case 'gt':
$options .= '<option value=">">Greater than</option>';
break;
case 'gte':
$options .= '<option value=">=">Greater than or equal to</option>';
break;
case 'lt':
$options .= '<option value="<">Less than</option>';
break;
case 'lte':
$options .= '<option value="<=">Less than or equal to</option>';
break;
case 'lk':
$options .= '<option value="LIKE \'%...%\'">Contains</option>';
break;
case 'in':
$options .= '<option value="IN (...)">IN (...)</option>';
break;
case 'nin':
$options .= '<option value="NOT IN (...)">NOT IN (...)</option>';
break;
case 'bt':
$options .= '<option value="BETWEEN" id="between_range">BETWEEN</option>';
break;
case 'nbt':
$options .= '<option value="NOT BETWEEN">NOT BETWEEN</option>';
break;
case 'n':
$options .= '<option value="=\'\'" id="blank">IS BLANK</option>';
break;
case 'nn':
$options .= '<option value="!=\'\'">IS NOT BLANK</option>';
break;
}
}
echo $options;
}
}
}
/*Query Filter*/
if (!function_exists('datatableFilterQuery')) {
function datatableFilterQuery($query, $column, $operator, $input = null, $input_from = null, $input_to = null, $rawQuery = false)
{
if( isset($operator) && $operator != "" ){
switch ($operator) {
case ">" : return $query->where( $column, $operator, $input);
case ">=" : return $query->where( $column, $operator, $input);
case "<" : return $query->where( $column, $operator, $input);
case "<=" : return $query->where( $column, $operator, $input);
case "=" :
if ($rawQuery) {
return $query->whereRaw($column. ' = "' . $input . '"');
} else {
return $query->where( $column, $operator, $input);
}
case "!=" :
if ($rawQuery) {
return $query->whereRaw($column. ' != "' . $input . '"');
} else {
return $query->where( $column, $operator, $input);
}
case "IN (...)" :
$input = explode(',', $input);
if ($rawQuery) {
return $query->whereRaw($column. ' IN ("' .implode('","', $input). '")');
} else {
return $query->whereIn($column, $input);
}
case "NOT IN (...)" :
$input = explode(',', $input);
if ($rawQuery) {
return $query->whereRaw($column. ' NOT IN ("' .implode('","', $input). '")');
} else {
return $query->whereNotIn($column, $input);
}
case "=''" :
if ($rawQuery) {
return $query->whereRaw($column. ' IS NULL ');
} else {
return $query->whereNull($column);
}
case "!=''" :
if ($rawQuery) {
return $query->whereRaw($column. ' IS NOT NULL ');
} else {
return $query->whereNotNull($column);
}
case "LIKE '%...%'" :
if ($rawQuery) {
return $query->whereRaw($column. ' like "%' . $input . '%"');
} else {
return $query->where($column, 'like', "%" . $input . "%");
}
case "BETWEEN" :
$input = array($input_from, $input_to);
return $query->whereBetween($column, $input);
case "NOT BETWEEN" :
$input = array($input_from, $input_to);
return $query->whereNotBetween($column, $input);
}
}
}
}
Raw
lookup.blade.php
@extends('layouts.master')
@section('content')
<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">
<!-- Content Header (Page header) -->
<div class="content-header">
<div class="container-fluid">
<div class="row mb-2">
<div class="col-sm-6">
<h1 class="m-0 text-dark">Course</h1>
</div>
<!-- /.col -->
<div class="col-sm-6">
<ol class="breadcrumb float-sm-right">
<li class="breadcrumb-item"><a href="{{ route('home') }}">Home</a></li>
<li class="breadcrumb-item"><a href="{{ route('coursebatch.menu') }}">Course</a></li>
<li class="breadcrumb-item active">Course</li>
</ol>
</div>
<!-- /.col -->
</div>
<!-- /.row -->
<!-- Alert Box -->
<div class="alert alert-danger alert-dismissible" style="display: none;">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
<span class="error-message"></span>
</div>
<div class="alert alert-info alert-dismissible" style="display: none;">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
<span class="info-message"></span>
</div>
<!-- /.Alert Box -->
</div>
<!-- /.container-fluid -->
</div>
<!-- /.content-header -->
<!-- Main content -->
<section class="content">
<div class="container-fluid">
<div class="row">
<div class="col-12">
<div class="card card-warning animated slideInLeft delay-1s">
<div class="card-header">
<h3 class="card-title"><i class="fas fa-filter"></i> Filter</h3>
<div class="card-tools">
<button type="button" class="btn btn-tool" data-widget="collapse"><i class="fa fa-minus"></i>
</button>
</div>
<!-- /.card-tools -->
</div>
<!-- /.card-header -->
<div class="card-body">
<form id="select_filter" action="{{ route('course.export') }}" method="POST" >
@csrf
<div class="row">
<div class="col-md-6">
<div class="form-group row">
<label class="col-sm-3 col-form-label" for="course_name">Course Name</label>
<div class="col-md-4">
<select name="course_name_operator" id="course_name_operator" class="select2me form-control searchOperator">
{{ filterOptions(["e", "ne", "n", "nn", "lk"]) }}
</select>
</div>
<div class="col-sm-4" id="course_name_div">
<input type="text" name="course_name" value="" class="form-control filterControl" id="course_name" placeholder="Course Name" required>
</div>
</div>
</div>
<div class="col-md-6">
<div class="form-group row">
<label class="col-sm-3 col-form-label" for="course_code"> Course Code</label>
<div class="col-md-4">
<select name="course_code_operator" id="course_code_operator" class="select2me form-control searchOperator">
{{ filterOptions(["e", "ne", "n", "nn", "lk"]) }}
</select>
</div>
<div class="col-sm-4" id="course_code_div">
<input type="text" name="course_code" value="" class="form-control filterControl" id="course_code" placeholder="Course Code" >
</div>
</div>
</div>
</div>
</form>
</div>
<!-- /.card-body -->
<div class="card-footer text-center">
<button type="reset" class="btn btn-danger filter-reset"><i class="fas fa-undo"></i> Reset</button>
<button type="button" class="btn btn-success search-form"><i class="fas fa-search"></i> Search</button>
</div>
<!-- /.card-body -->
</div>
<!-- /.card -->
<div class="card animated slideInRight delay-1s">
<div class="card-header">
<h3 class="card-title"><i class="fas fa-clipboard-list"></i> Lookup</h3>
<div class="card-tools">
<a href="{{ route('course.create') }}" class="btn btn-sm btn-primary add-course" data-toggle="tooltip" title="Add course">
<i class="fa fa-plus"></i>
</a>
<a href="javascript:void(0)" class="btn btn-sm btn-warning export" data-toggle="tooltip" title="Export">
<i class="fa fa-download"></i>
</a>
</div>
</div><!-- /.card-header -->
<div class="card-body">
<div class="tab-pane active" id="lookup">
<table class="table table-bordered" id="course_table" style="width: 100%">
<thead>
<tr>
<th style="width: 8%">#</th>
<th>Course Name</th>
<th>Course Code</th>
<th>Description</th>
<th style="width: 10%">Action</th>
</tr>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div><!-- /.card-body -->
</div>
<!-- ./card -->
</div>
</div>
<div class="row">
<div class="col-md-6">
</div>
<div class="col-md-6">
</div>
</div>
<!-- /.row -->
</div>
<!-- /.container-fluid -->
</section>
<!-- /.content -->
</div>
<!-- /.content-wrapper -->
@endsection
@section('css')
<!-- DataTables -->
<link rel="stylesheet" href="{{ asset('/assets/dist/plugins/datatables/dataTables.bootstrap4.css') }}">
<link rel="stylesheet" href="{{ asset('/assets/dist/plugins/select2/select2.min.css') }}">
@stop
@section('javascript')
<!-- DataTables -->
<script src="{{ asset('/assets/dist/plugins/datatables/jquery.dataTables.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/datatables/dataTables.bootstrap4.js') }}"></script>
<!-- Sparkline -->
<script src="{{ asset('/assets/dist/plugins/sparkline/jquery.sparkline.min.js') }}"></script>
<!-- jvectormap -->
<script src="{{ asset('/assets/dist/plugins/jvectormap/jquery-jvectormap-1.2.2.min.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/jvectormap/jquery-jvectormap-world-mill-en.js') }}"></script>
<!-- ChartJS 1.0.2 -->
<script src="{{ asset('/assets/dist/plugins/chartjs-old/Chart.min.js') }}"></script>
<!-- jquery validation -->
<script src="{{ asset('/assets/dist/plugins/jquery-validation/js/jquery.validate.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/jquery-validation/js/additional-methods.min.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/select2/select2.full.min.js') }}"></script>
<!-- Page JS -->
<script src="{{ asset('/assets/js/course.js') }}"></script>
<script type="text/javascript">
$.ajaxSetup({
headers: {
'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
}
});
var delete_route = '{!! route('course.destroy', -1) !!}';
var lookup_route = '{!! route('course.data') !!}';
Course.initLookupTable();
Course.setTableHighlight();
</script>
@stop
Raw
web.php
<?php
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('currency/lookup', 'CurrencyController@lookup')->name('currency.data');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment