Reformatting/parsing datebox in popup form

[andyj]

Following on from this post: http://www.jeasyui.com/forum/index.php?topic=365.0

I am having difficulty in manipulating dates in a pop-up form launched from a datagrid.

I would like to convert my MySQL date field (format: yyyy-mm-dd) to a UK date format dd-mm-yyyy.

As per previous posts, the following script formats and parses the date field so that it at least works with the MySQL db format but displays as yyyy-mm-dd.

Code:

	$.fn.datebox.defaults.formatter = function(date){
		var y = date.getFullYear();
		var m = date.getMonth()+1;
		var d = date.getDate();
		return y+'-'+(m<10?('0'+m):m)+'-'+(d<10?('0'+d):d);
	};
	$.fn.datebox.defaults.parser = function(s){
		if (!s) return new Date();
		var ss = s.split('-');
		var d = parseInt(ss[2],10);
		var m = parseInt(ss[1],10);
		var y = parseInt(ss[0],10);
		if (!isNaN(y) && !isNaN(m) && !isNaN(d)){
			return new Date(y,m-1,d);
		} else {
			return new Date();
		}
	};

If I change it to the following in order to format it as dd-mm-yyyy, it no longer works...

Code:

	$.fn.datebox.defaults.formatter = function(date){
		var y = date.getFullYear();
		var m = date.getMonth()+1;
		var d = date.getDate();
		return (d<10?('0'+d):d)+'-'+(m<10?('0'+m):m)+'-'+y;
	};
	$.fn.datebox.defaults.parser = function(s){
		if (!s) return new Date();
		var ss = s.split('-');
		var d = parseInt(ss[2],10);
		var m = parseInt(ss[1],10);
		var y = parseInt(ss[0],10);
		if (!isNaN(y) && !isNaN(m) && !isNaN(d)){
			return new Date(y,m-1,d);
		} else {
			return new Date();
		}
	};

Upon opening the form the date appears incorrectly formatted yyyy-mm-dd (although the correct date).
Upon selecting a new date from the calendar popup tool, it correctly returns it in the correct format dd-mm-yyyy
If you re-open the calendar popup tool, the previously selected date is no longer selected and the calendar tool is set to a completely different year.
Upon saving the form it doesn't save the date. Presumably it fails to correctly parse the date...

Any ideas? Thanks in advance...

[stworthy]

Some mistakes occur in your parser function. Please try this:

Code:

	$.fn.datebox.defaults.formatter = function(date){
		var y = date.getFullYear();
		var m = date.getMonth()+1;
		var d = date.getDate();
		return (d<10?('0'+d):d)+'-'+(m<10?('0'+m):m)+'-'+y;
	};
	$.fn.datebox.defaults.parser = function(s){
		if (!s) return new Date();
		var ss = s.split('-');
		var d = parseInt(ss[0],10);
		var m = parseInt(ss[1],10);
		var y = parseInt(ss[2],10);
		if (!isNaN(y) && !isNaN(m) && !isNaN(d)){
			return new Date(y,m-1,d);
		} else {
			return new Date();
		}
	};

[andyj]

Thanks for your suggestion. Unfortunately it doesn't solve the problem.
I now understand that I needed to get the split date elements in the correct order to reflect the formatted format.

The problem is that when the form is saved the parsed date format that is being sent to MySQL is dd-mm-yyyy.
MySQL is expecting the date format yyyy-mm-dd.

The solution I have come up with involves three steps

1. Add stworthy's above code to change the defaults for formatter and parser:

Code:

	$.fn.datebox.defaults.formatter = function(date){
		var y = date.getFullYear();
		var m = date.getMonth()+1;
		var d = date.getDate();
		return (d<10?('0'+d):d)+'-'+(m<10?('0'+m):m)+'-'+y; //e.g. 12-03-1966 (= 12 March 1966)
	};
	$.fn.datebox.defaults.parser = function(s){
		if (!s) return new Date();
		var ss = s.split('-');
		var d = parseInt(ss[0],10);
		var m = parseInt(ss[1],10);
		var y = parseInt(ss[2],10);
		if (!isNaN(y) && !isNaN(m) && !isNaN(d)){
			return new Date(y,m-1,d);
		} else {
			return new Date();
		}
	};	

2. Modify your SELECT query that supplies data to your grid using the mysql function: DATE_FORMAT(fieldname,'%d-%m-%Y').
This reformats the date from yyyy-mm-dd to dd-mm-yyyy:

Code:

<?php
	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 20;
	$offset = ($page-1)*$rows;
	$result = array();

	include '../../common/conn.php';
	
	$rs = mysql_query("SELECT count(*) from supplierscheme");
	$row = mysql_fetch_row($rs);
	$result["total"] = $row[0];
	$rs = mysql_query("SELECT
supplierscheme.supplierlineitem_id,
supplierscheme.Rating,
DATE_FORMAT(supplierscheme.`Starts`,'%d-%m-%Y') as `Starts`,
DATE_FORMAT(supplierscheme.Expires,'%d-%m-%Y') as Expires,
supplierscheme.IsActive,
FROM
supplierscheme limit $offset,$rows");
		
	$items = array();
	while($row = mysql_fetch_object($rs)){
		array_push($items, $row);
	}
	$result["rows"] = $items;
	
	echo json_encode($result);
?>

3. Modify your UPDATE (and INSERT) query that updates (or inserts) the form data when saving using the function date("Y-m-d", strtotime($_REQUEST['fieldname']):

Code:

<?php
$supplierlineitem_id = intval($_REQUEST['supplierlineitem_id']);
$Rating = $_REQUEST['Rating'];
$Starts= date("Y-m-d", strtotime($_REQUEST['Starts']));
$Expires= date("Y-m-d", strtotime($_REQUEST['Expires']));
$IsActive = $_REQUEST['IsActive'];

include '../../common/conn.php';

$sql = "UPDATE supplierscheme SET Rating='$Rating',Starts='$Starts',Expires='$Expires',IsActive='$IsActive' WHERE supplierlineitem_id=$supplierlineitem_id";
$result = mysql_query($sql);
if ($result){
	echo json_encode(array('success'=>true));
} else {
	echo json_encode(array('msg'=>'Some errors occured while attempting to update the record.'));
}
?>

Everything then seems to work correctly.

One added bonus of reformatting dates in your MySQL queries is that you do not have to reformat the dates that appear in the datagrid.

[bvicencio]

Debes extender la funcionalidad utilizando $.extend(.......). Abajo dejo el código para que lo pruebes. Saludos

PD: Recuerda reemplazar el caracter "/" por "-" en caso de que lo utilices de esa forma.

$.extend($.fn.datebox.defaults,{
   formatter:function(date){
      var y = date.getFullYear();
      var m = date.getMonth()+1;
      var d = date.getDate();
      return (d<10?('0'+d):d)+'/'+(m<10?('0'+m):m)+'/'+y;
   },
   parser:function(s){
      if (!s) return new Date();
      var ss = s.split('/');
      var d = parseInt(ss[0],10);
      var m = parseInt(ss[1],10);
      var y = parseInt(ss[2],10);
      if (!isNaN(y) && !isNaN(m) && !isNaN(d)){
         return new Date(y,m-1,d);
      } else {
         return new Date();
      }
   },
});

[evaro]

// Ademas, si agregas este cambio, El calendario se mostrara usando Lunes como primer dia ( 0 = Domingo )
$.extend($.fn.calendar.defaults, {
    firstDay:1,
});