我迫切需要幫助。幾個星期以來，我嘗試了所有可能來自互聯網的代碼，以使用 Codeigniter 中的日期選擇器獲取 Datatables 服務器端日期范圍，但沒有成功。如果日期相同，那么我可以獲得范圍，但如果范圍在日/月/年之間，我會得到當天所有月份或年份的范圍。請指教。以下是我的 Codeigniter 視圖、控制器和模型代碼：-
查看部分
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;
});
控制器部分
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);
}
模型部分
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;
}
uj5u.com熱心網友回復：
對我來說聽起來像是日期選擇器的問題
請更改日期選擇器初始化，如下所示
$('#startdate').datepicker({
autoclose: true,
format: 'dd.mm.yyyy'
});
$('#enddate').datepicker({
autoclose: true,
format: 'dd.mm.yyyy'
})
您可以使用 jQuery 獲取值，如下所示
$('input[name=startdate]').val()
$('input[name=enddate]').val()
您可以在服務器端獲取 POST 值，如下所示
$startdate = date('Y-m-d', strtotime($this->input->post('startdate')));
$enddate = date('Y-m-d', strtotime($this->input->post('enddate')));
如果您的資料庫是 MSSQL，您可以在模型查詢中使用這些變數，如下所示
$this->db->where('FORMAT(invoices.Invoice_Date, "yyyy-MM-dd") BETWEEN "'.$startdate.'" AND "'.$enddate.'"');
請嘗試并反饋
更新
這可能會幫助你
前端
HTML 標記
<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" />
日期時間選擇器初始化
$('#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
});
重新格式化功能
function reformatDateString(s) {
var b = s.split(/\D/);
return b.reverse().join('-');
}
Ajax 發布資料
var data = {
start_date: reformatDateString($('#start_date').val()),
end_date: reformatDateString($('#end_date').val()),
csrf_test_name: csrfTokenHash
};
后端
控制器
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);
}
模型
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();
}
uj5u.com熱心網友回復：
問題解決了。我必須將 PHPMyAdmin 中的 MYSQL 表中的列型別從 varchar 更改為 date 以獲得日期范圍。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/386020.html
上一篇：我如何從Ajax的物件中獲取值
下一篇：file_get_contents():內容型別未指定假設application/x-www-form-urlencoded