search

Chumper / Datatable

This is a laravel 4 package for the server and client side of datatables at http://datatables.net/
https://github.com/Chumper/Datatable
388 stars 154 forks source link

Need help in order column by date #369

Closed mverma16 closed 8 years ago

mverma16 commented 8 years ago

Hello, I am trying to implement datatable where I want records to be sort by last updated record.

Code in my blade file looks like this

{!! Datatable::table()
        ->addColumn(
                    "",
                    Lang::get('lang.subject'),
                    Lang::get('lang.ticket_id'),
                    Lang::get('lang.priority'),
                    Lang::get('lang.from'),
                    Lang::get('lang.last_replier'),
                    Lang::get('lang.assigned_to'),
                    Lang::get('lang.last_activity'))
        ->setUrl(route('get.inbox.ticket')) 
        ->setOrder(array(7=>'desc'))  
        ->setClass('table table-hover table-bordered table-striped')       
        ->render();!!}

And controller function is 

    public function get_inbox()
    {
        if (Auth::user()->role == 'admin') {
            $tickets = Tickets::where('status', '=', 1)->get();
        } else {
            $dept = DB::table('department')->where('id', '=', Auth::user()->primary_dpt)->first();
            $tickets = Tickets::where('status', 1)->where('dept_id', '=', $dept->id)->get();
        }

        return \Datatable::collection(new Collection($tickets))
                        ->addColumn('id', function ($ticket) {
                              return "<input type='checkbox' name='select_all[]' id='".$ticket->id."' onclick='someFunction(this.id)' class='selectval icheckbox_flat-blue' value='".$ticket->id."'></input>";
                        })
                        ->addColumn('subject', function ($ticket) {
                            $subject = DB::table('ticket_thread')->select('title')->where('ticket_id', '=', $ticket->id)->first();
                            if (isset($subject->title)) {
                                $string = $subject->title;
                                if (strlen($string) > 20) {
                                    $stringCut = substr($string, 0, 30);
                                    $string = substr($stringCut, 0, strrpos($stringCut, ' ')).' ...';
                                }
                            } else {
                                $string = '(no subject)';
                            }
                            //collabrations
                            $collaborators = DB::table('ticket_collaborator')->where('ticket_id', '=', $ticket->id)->get();
                            $collab = count($collaborators);
                            if ($collab > 0) {
                                $collabString = '&nbsp;<i class="fa fa-users"></i>';
                            } else {
                                $collabString = null;
                            }

                            $threads = Ticket_Thread::where('ticket_id', '=', $ticket->id)->first(); //
                            $count = Ticket_Thread::where('ticket_id', '=', $ticket->id)->count(); //Ticket_Thread::where('ticket_id', '=', $ticket->id)->get();

                            $attachment = Ticket_attachments::where('thread_id', '=', $threads->id)->get();
                            $attachCount = count($attachment);
                            if ($attachCount > 0) {
                                $attachString = '&nbsp;<i class="fa fa-paperclip"></i>';
                            } else {
                                $attachString = '';
                            }

                            return "<a href='".route('ticket.thread', [$ticket->id])."' title='".$subject->title."'>".$string."&nbsp;<span style='color:green'>(".$count.")<i class='fa fa-comment'></i></span></a>".$collabString.$attachString;
                        })
                        ->addColumn('ticket_number', function ($ticket) {
                            return "<a href='".route('ticket.thread', [$ticket->id])."' title='".$ticket->ticket_number."'>#".$ticket->ticket_number.'</a>';
                        })
                        ->addColumn('priority', function ($ticket) {
                            $priority = DB::table('ticket_priority')->select('priority_desc', 'priority_color')->where('priority_id', '=', $ticket->priority_id)->first();

                            return '<span class="btn btn-'.$priority->priority_color.' btn-xs">'.$priority->priority_desc.'</span>';
                        })
                        ->addColumn('from', function ($ticket) {
                            $from = DB::table('users')->select('user_name')->where('id', '=', $ticket->user_id)->first();

                            return "<span style='color:#508983'>".$from->user_name.'</span>';
                        })
                        ->addColumn('Last Replier', function ($ticket) {
                            $TicketData = Ticket_Thread::where('ticket_id', '=', $ticket->id)->max('id');
                            $TicketDatarow = Ticket_Thread::where('id', '=', $TicketData)->first();
                            $LastResponse = User::where('id', '=', $TicketDatarow->user_id)->first();
                            if ($LastResponse->role == 'user') {
                                $rep = '#F39C12';
                                $username = $LastResponse->user_name;
                            } else {
                                $rep = '#000';
                                $username = $LastResponse->first_name.' '.$LastResponse->last_name;
                                if ($LastResponse->first_name == null || $LastResponse->last_name == null) {
                                    $username = $LastResponse->user_name;
                                }
                            }

                            return "<span style='color:".$rep."'>".$username.'</span>';
                        })
                        ->addColumn('assigned_to', function ($ticket) {
                            if ($ticket->assigned_to == null) {
                                return "<span style='color:red'>Unassigned</span>";
                            } else {
                                $assign = DB::table('users')->where('id', '=', $ticket->assigned_to)->first();

                                return "<span style='color:green'>".$assign->first_name.' '.$assign->last_name.'</span>';
                            }
                        })
                        ->addColumn('Last', function ($ticket) {
                            $TicketData = Ticket_Thread::where('ticket_id', '=', $ticket->id)->max('id');
                            $TicketDatarow = Ticket_Thread::select('updated_at')->where('id', '=', $TicketData)->first();

                            return UTC::usertimezone($TicketDatarow->updated_at);
                        })
                        ->searchColumns('subject', 'from', 'assigned_to', 'ticket_number', 'priority')
                        ->orderColumns('subject', 'from', 'assigned_to', 'Last Replier', 'ticket_number', 'priority', 'Last')
                        ->make();
    }

I am sorting the data before rendering table by 'Last' which is last updated_at date column.

Ordering is successful in "mm/dd/yyyy" and "yyyy/mm/dd" format but in "dd/mm/yyyy" ordering is getting failed.

While sorting in ''mm-dd-yyyy" format it works fine mm-dd-yyyy

But sorting in dd-mm-yyyy it sorts incorrectly dd-mm-yyyy

Can you help me to implement sorting correctly in all date formats. TIA

mverma16 commented 8 years ago

So I tried to solve the issue using "aoColumnDefs" in setOptions().

I converted the date I was fetching from database into javascript date format before rendering the data in to column.

here's the snippet of the code I used

  ->setOptions('aoColumnDefs',array(
        array(
            'render' => "function ( data, type, row ) {
                    var t = row[6].split(/[- :,/ :,. /]/);
                    var d = new Date(t[0], t[1]-1, t[2], t[3], t[4], t[5]);
                    <!--  -->
                    var dtf= '$date_time_format';
                    if(dtf==1) {
                        dtf = 'D/MMM/YYYY hh:mm:ss A';
                    } else if(dtf==2) {
                        dtf = 'D MMM, YYYY hh:mm:ss A';
                    } else if(dtf==3) {
                        dtf = 'D-MMM-YYYY hh:mm:ss A';
                    } else if(dtf==4) {
                        dtf = 'MMM/D/YYYY hh:mm:ss A';
                    } else if(dtf==5) {
                        dtf = 'MMM D, YYYY hh:mm:ss A';
                    } else if(dtf==6) {
                        dtf = 'MMM-D-YYYY hh:mm:ss A';
                    } else if(dtf==7) {
                        dtf = 'YYYY/MMM/D hh:mm:ss A';
                    } else if(dtf==8) {
                        dtf = 'YYYY, MMM D hh:mm:ss A';
                    } else if(dtf==9) {
                        dtf = 'YYYY-MMM-D hh:mm:ss A';
                    }
                    return  moment(d).format(dtf);
                    <!-- //return d; -->
                }", 
            'aTargets' => array(6))
        ))

Hope this will help you, if you are looking for the solution for this issue.