Laravel Datepicker And Eloquent Query

Question

I am making a page where I can use the date ranger, datepicker from jqueryui.com and i am very mewbie to laravel Framework.

I have the eloquent query as follows,

public function orderbydate()
{

    $order =DB::table('sales_flat_order_items as s')
            ->leftJoin('sales_flat_orders as w', 'w.entity_id','=','s.order_id')
            ->select(array(DB::Raw('sum(s.amount_refunded) as amount_refunded'),
                DB::Raw('sum(s.row_total) as row_total'),
                DB::Raw('sum(s.discount_amount) as discount_amount'),
                DB::Raw('sum(s.tax_amount) as tax_amount'),
                DB::Raw('sum(s.qty_ordered) as qty_ordered'),
                DB::Raw('sum(w.subtotal) as subtotal'), 
                DB::Raw('sum(w.total_invoiced) as total_invoiced'), 
                DB::Raw('sum(w.shipping_amount) as shipping_amount')))
            ->where('qty_canceled','=','0')
            ->where('status','!=','canceled')
            ->get();

    $orderbydate = DB::table('sales_flat_order_items as s')
            ->leftJoin('sales_flat_orders as w', 'w.entity_id','=','s.order_id')
            ->select(array(DB::Raw('sum(s.amount_refunded) as amount_refunded'),
                DB::Raw('sum(s.row_total) as row_total'),
                DB::Raw('sum(s.discount_amount) as discount_amount'),
                DB::Raw('sum(s.tax_amount) as tax_amount'),  
                DB::Raw('sum(s.qty_ordered) as qty_ordered'),
                DB::Raw('sum(w.subtotal) as subtotal'),
                DB::Raw('DATE(w.created_at) days'), 
                DB::Raw('sum(w.total_invoiced) as total_invoiced'),
                DB::Raw('sum(w.shipping_amount) as shipping_amount')))
            ->where('qty_canceled','=','0')
            ->where('status','!=','canceled')
            ->groupBy('days')
            ->orderBy('s.created_at')
            ->paginate(10);

        return View::make('sales_flat_orders.orderbydate', compact('order','orderbydate'));
}

Note: this function will show u all the data on the template

Another function is

public function orderbydate1()
    {
        $startDate = Input::get('w.created_at');
        $endDate = Input::get('w.created_at');

        $order1 =DB::table('sales_flat_order_items as s')
                ->leftJoin('sales_flat_orders as w', 'w.entity_id','=','s.order_id')
                ->select(array(DB::Raw('sum(s.amount_refunded) as amount_refunded'),
                    DB::Raw('sum(s.row_total) as row_total'),
                    DB::Raw('sum(s.discount_amount) as discount_amount'),
                    DB::Raw('sum(s.tax_amount) as tax_amount'),
                    DB::Raw('sum(s.qty_ordered) as qty_ordered'),
                    DB::Raw('sum(w.subtotal) as subtotal'), 
                    DB::Raw('sum(w.total_invoiced) as total_invoiced'), 
                    DB::Raw('sum(w.shipping_amount) as shipping_amount')))
                ->where('qty_canceled','=','0')
                ->where('status','!=','canceled')
                ->get();

        $orderbydate1 = DB::table('sales_flat_order_items as s')
                ->leftJoin('sales_flat_orders as w', 'w.entity_id','=','s.order_id')
                ->select(array(DB::Raw('sum(s.amount_refunded) as amount_refunded'),
                    DB::Raw('sum(s.row_total) as row_total'),
                    DB::Raw('sum(s.discount_amount) as discount_amount'),
                    DB::Raw('sum(s.tax_amount) as tax_amount'),  
                    DB::Raw('sum(s.qty_ordered) as qty_ordered'),
                    DB::Raw('sum(w.subtotal) as subtotal'),
                    DB::Raw('DATE(w.created_at) days'), 
                    DB::Raw('sum(w.total_invoiced) as total_invoiced'),
                    DB::Raw('sum(w.shipping_amount) as shipping_amount')))
                ->whereBetween('w.created_at',array($startDate,$endDate))
                ->where('qty_canceled','=','0')
                ->where('status','!=','canceled')
                ->groupBy('days')
                ->orderBy('s.created_at')
                ->paginate(10);

            return View::make('sales_flat_orders.result', compact('order','orderbydate1'));
    }

I have template named OrderByDAte.blade.php

Sales By Date

    {{ Form::open() }}

<label for="from">From</label>
<input type="text" id="from" name="from">
<label for="to">to</label>
<input type="text" id="to" name="to">
<input type="submit" value="Submit" class="btn btn-info btn-block">
        {{ Form::close() }}
<script>
  $(function() {
    $( "#from" ).datepicker({
      defaultDate: "+1w",
      changeMonth: true,
      numberOfMonths: 3,
      onClose: function( selectedDate ) {
        $( "#to" ).datepicker( "option", "minDate", selectedDate );
      }
    });
    $( "#to" ).datepicker({
      defaultDate: "+1w",
      changeMonth: true,
      numberOfMonths: 3,
      onClose: function( selectedDate ) {
        $( "#from" ).datepicker( "option", "maxDate", selectedDate );
      }
    });
  });
  </script>

Also the loop which displays the table.

And Now

**result.blade.php** which displays the data. 
<table class="table table-striped table-bordered table-hover" id="dataTables-example">
    <thead>
        <tr class="odd gradeX"> 
        <th>DATE</th>
      <th>Items Ordered</th>
      <th>Subtotal</th>
      <th>Tax Amount</th>
      <th>Discount Amount</th>
      <th>Row Total</th>
      <th>Refunded</th>
     <th>Total Invoiced</th>
     <th>Shipping Amount</th>
    </tr>
        </thead>

  @foreach($orderbydate1 as $s)
 <tbody>
  <tr class="odd gradeX">
     <td>{{date("d F, Y",strtotime($s->days))}}</td>
     <td>{{ round($s->qty_ordered, 2) }}</td>
     <td>{{round($s->subtotal,2)}}</td>
     <td>{{round($s->tax_amount,2)}}</td>
     <td>{{round($s->discount_amount,2)}}</td>
     <td>{{round($s->row_total,2)}}</td>
     <td>{{round($s->amount_refunded,2)}}</td>
     <td>{{round($s->total_invoiced,2)}}</td>
     <td>{{round($s->shipping_amount,2)}}</td>
  </tr>

 @endforeach

Note: I think my Submit Button Does not works. I dnt know why?

Now I want to use datepicker to filter this by date, in my routes i know that i have to use post and after submitting the date i must get the datatable in that range.

How to use $startDate and $endDate over here. in my eloquent query. I have to take input 'w.created_at' field for my startDate and also 'w.created_at' for $endDate. I think i have to use facade input in my function like this.

$startDate = Input::get('w.created_at');
$endDate = Input::get('w.created_at');

But this function does not work.

I am currently using the bootstrap datepicker. But if you know other one then U can give me examples for it.

Now there are two template which i use for the

Route::get('orderbydate', array('as'=>'orderbydate', 'uses'=>'SalesFlatController@orderbydatesales'))

to get all the data into my template.

And for the post i use

Route::get('result', array('as'=>'result', 'uses'=>'SalesFlatController@orderbydate'))

Note: I get an error like Missing 1 argument.

Can anyone just help me a little in just knowing where i am going wrong??

Answer 1

You need to change:

public function orderbydate($startDate, $endDate)

to:

public function orderbydate()

as you're getting those values from Input::get(). You'd put those variables in the function call only if those variables were coming from variables in the route, i.e.:

Route::get('result/{startDate}/{endDate}', array('as'=>'result', 'uses'=>'SalesFlatController@orderbydate'))