Question 69904 in Ajax Question posted in
You can find the official documentation here.

CodeIgniter Datatables Server Side Date Range using Datepicker – Jquery ajax

I am in desperate need of help. For a few weeks, I have tried all the codes possible from the internet to get the Datatables serverside date range using the date picker in Codeigniter but no luck. If the dates are the same then I can get the range but if the range is between the day/month/year day I get a range of all the months or years that the day as. Please advise. Below is my codes for Codeigniter View, Controller and Model:-

View Section

    var table = $('.xtable').DataTable( {
     scrollY:        "400px",
    scrollX:        true,
     scrollCollapse: true,
    autoWidth:      true,
    paging:         false,
   fixedHeader: true,
   processing : true,
   serverSide : true,
   responsive: true,
    stateSave : true,
    deferRender: true,
    ajax: {
   "url": "<?php echo base_url(); ?>getallbillinglist",
   "type": "POST",
   "data": function ( data ) {
      console.log(data);
     data.startdate = $('#startdate').val();//$.trim(fdate1[0]);
      data.enddate = $('#enddate').val();//$.trim(fdate1[1]);
     console.log(data.startdate);
     console.log(data.enddate);
      return data;
    }
     }, ... code continues if not too long
  
   $('#startdate').datepicker({
      autoclose: true
    })

 $('#enddate').datepicker({
  autoclose: true
 })
 $('#startdate, #enddate').change(function () {
    console.log(this.value);
    table.draw();
    //table.columns(12).search(this.value ? '^'+this.value+'$' : '', true, false).draw(); 
    document.getElementById("sdate").value = document.getElementById("startdate").value;
    document.getElementById("edate").value = document.getElementById("enddate").value;
 });

Controller Section

       public function getallbillinglist()
        {
        if (!$this->ion_auth->logged_in())
         {
            redirect('/');
        }
         $startdate = $this->input->post('startdate');
       $enddate = $this->input->post('enddate');
       $data['data']=$this->Job->get_allbillinglist($startdate,$enddate);
        echo json_encode($data);    
        }

Model Section

          function get_allbillinglist($startdate,$enddate)
{
    $data = array();
    $multipleWhere = ['invoices.Approved' => 1,'invoices.xero' => 0];
    $this->db->select('Client_Name, Invoice_No, Invoice_Date, Due_Date, Item_Descript, Quantity, UCost, Amt, TAmt, Tax, Total, AccCode, AccName, Ship_Name');
    $this->db->from("invoices");
    // $this->db->where('Approved',1);
    $this->db->where($multipleWhere);
    if($startdate!='' && $enddate!=''){
        //$this->db->where('invoices.Invoice_Date BETWEEN "'.$startdate.'" AND "'.$enddate.'"');
        $this->db->where('invoices.Invoice_Date >=', $startdate);
        $this->db->where('invoices.Invoice_Date <=', $enddate);
    }
    $this->db->get();  
    $query1 = $this->db->last_query();
    $multipleWhere1 = ['cninvoices.Approved' => 1, 'cninvoices.xero' => 0];
    $this->db->select('Client_Name, Credit_No, Credit_Date, "", Item_Descript, Quantity, UCost, TISubTotal, TIGST, Tax, TITotal, AccCode, AccName, Ship_Name');
    $this->db->from("cninvoices");
    // $this->db->where('Approved',1);
    $this->db->where($multipleWhere1);
    if($startdate!='' && $enddate!=''){
        // $this->db->where('cninvoices.Credit_Date BETWEEN "'.$startdate.'" AND "'.$enddate.'"');
        $this->db->where('cninvoices.Credit_Date >=', $startdate);
        $this->db->where('cninvoices.Credit_Date <=', $enddate);
    }
    $this->db->get(); 
    $query2 =  $this->db->last_query();
    $Q = $this->db->query($query1." UNION ".$query2." ORDER BY Invoice_No ASC");
    
    if ($Q->num_rows() > 0){
        foreach ($Q->result_array() as $row){
            $data[] = $row;
        }
    }
    $Q->free_result();
    return $data;
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The problem is resolved. I had to change the column type in my MYSQL table in PHPMyAdmin from varchar to date in order to get the date range.


  2. 0

    Sounds like a problem with datepicker to me

    Please change datepicker initializations like below

    $('#startdate').datepicker({
  autoclose: true,
  format: 'dd.mm.yyyy'
});
$('#enddate').datepicker({
  autoclose: true,
  format: 'dd.mm.yyyy'
})

    You can get values using jQuery like below

    $('input[name=startdate]').val()
$('input[name=enddate]').val()

    You can get POST values on the server side like below

    $startdate = date('Y-m-d', strtotime($this->input->post('startdate')));

$enddate = date('Y-m-d', strtotime($this->input->post('enddate')));

    If your DB is MSSQL, you can use those variables in your model query like below

    $this->db->where('FORMAT(invoices.Invoice_Date, "yyyy-MM-dd") BETWEEN "'.$startdate.'" AND "'.$enddate.'"');

    Please try and feedback

    UPDATE

    This might help you

    FRONT END

    HTML Markup

    <input id="start_date" name="start_date" type="text" class="form-control m-input" placeholder="<?php echo START_DATE; ?>" autocomplete="off" />

<input id="start_date" name="start_date" type="text" class="form-control m-input" placeholder="<?php echo START_DATE; ?>" autocomplete="off" />

    Datetimepicker Initialization

    $('#start_date').datetimepicker({
  autoclose: true,
  format : 'dd.mm.yyyy',
  dateFormat: 'dd.mm.YYYY',
  todayHighlight: true,
  language: 'tr',
  startView:2,
  minView:2
});

$('#end_date').datetimepicker({
  autoclose: true,
  format : 'dd.mm.yyyy',
  dateFormat: 'dd.mm.YYYY',
  todayHighlight: true,
  language: 'tr',
  startView:2,
  minView:2
});

    Reformatting Function

    function reformatDateString(s) {
  var b = s.split(/D/);
  return b.reverse().join('-');
}

    Ajax Post Data

    var data = {
  start_date: reformatDateString($('#start_date').val()),
  end_date: reformatDateString($('#end_date').val()),
  csrf_test_name: csrfTokenHash
};

    BACKEND

    Controller

    public function featured_meetings_list_json() {
  $data = array(
    'company_uid' => $this->session->userdata('COMPANY_UID'),
    'start_date' => $this->input->post('start_date'),
    'end_date' => $this->input->post('end_date'),
  );
  $result['data'] = $this->reports_model->get_featured_meetings($data);
  
  echo json_encode($result);
}

    Model

    public function get_featured_meetings($data) {
  $dataArr = array(
    $data['company_uid']
  );

  $sql = "SELECT CONCAT(m.document_no, '-', md.no) price_offer_no, m.document_no, m.document_date, e.name enterprise_name, CONCAT(u.name, ' ', u.lastname) sales_representative_name, p.name product_name, "
            ."md.quantity, CASE WHEN p.product_class = 0 and p.color = 0 THEN p.price WHEN p.product_class = 0 AND p.color = 1 THEN p.price + 0.50 WHEN p.product_class = 1 AND p.color = 0 THEN p.price + 0.20 WHEN p.product_class = 1 AND p.color = 1 THEN p.price + 0.70 WHEN p.product_class = 2 THEN p.price + 1.20 END list_price, p.currency_name list_price_currency_name, md.price, v.name vat_name, CAST(ROUND(((md.quantity)*(md.price)*(100 + v.rate)/100), 2) AS NUMERIC(36,2)) total_price, c.name currency_name, pt.name payment_type_name "
            ."FROM meetings m "
            ."LEFT JOIN meeting_details md ON(m.company_uid = md.company_uid AND m.uid = md.meeting_uid) "
            ."LEFT JOIN enterprises e ON (m.company_uid = e.company_uid AND m.enterprise_uid = e.uid) "
            ."LEFT JOIN (SELECT p.uid, p.company_uid, p.name, p.color, p.product_class, pc.price, pc.currency_uid, c.name currency_name, c.sign FROM products p LEFT JOIN product_categories pc ON (p.product_category_uid = pc.uid) LEFT JOIN currencies c ON (pc.currency_uid = c.uid)) AS p ON (md.company_uid = p.company_uid AND md.product_uid = p.uid) "
            ."LEFT JOIN users u ON (m.company_uid = u.company_uid AND m.sales_representative_uid = u.uid) "
            ."LEFT JOIN currencies c ON (md.currency_uid = c.uid) "
            ."LEFT JOIN vats v ON (md.vat_uid = v.uid) "
            ."LEFT JOIN payment_types pt ON (md.company_uid = pt.company_uid AND md.payment_type_uid = pt.uid) "
            ."WHERE m.company_uid = ?";

  if($data['start_date'] != '' && $data['end_date'] != '') {
    array_push($dataArr, $data['start_date']);
    array_push($dataArr, $data['end_date']);
    $sql .= " AND FORMAT(m.document_date, 'yyyy-MM-dd') between ? AND ?";
  }

  $sql .= " ORDER BY total_price DESC";
  //Here you can check your query and variables and run the query in console to catch potential issues, e.x. error_log(print_r($dataArr, true));error_log($sql);
  $query = $this->db->query($sql, $dataArr);
  return (array)$query->result_array();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search