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;
}
CodePudding user response：
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" placeholder="<?php echo START_DATE; ?>" autocomplete="off" />
<input id="start_date" name="start_date" type="text" 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();
}
CodePudding user response：
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.