OptionsConnector for select filter with date values

Suite and Optimus Suite 5
#1

Hello,
I would like a connector select filter in the grid header so I can filter the grid based on date values.
The thing is I cannot make OptionsConnector to work correctly because I want the value to be the date as it is in the db: %y-%m-%d but the label to be displayed as: %d/%m/%y which is also the format I am using to display dates in my grid.
Any suggestions?
Thank you for your time,
Andreas.

#2

Hi,
you should use something like this:

<?php
	require_once("../config.php");
	$res=mysql_connect($mysql_server,$mysql_user,$mysql_pass);
	mysql_select_db($mysql_db);

	require("../../codebase/grid_connector.php");
	$grid = new GridConnector($res);
	$grid->dynamic_loading(100);
	$grid->event->attach("beforeRender", "date_process");
	$grid->event->attach("beforeFilter", "before_filter");

	$options = new OptionsConnector($res);
	$options->render_table("events", "event_id", "start_date(value)");
	$options->event->attach("beforeRender", "label_process");

	$grid->set_options("start_date", $options);
	$grid->render_table("events","event_id","event_name,start_date");

	function date_process($row) {
		row_process($row, "start_date");
	}
	function label_process($row) {
		row_process($row, "value");
	}
	function row_process($row, $name) {
		$value = $row->get_value($name);
		$value = date_parse($value);
		$value = mktime($value['hour'], $value['minute'], $value['second'], $value['month'], $value['day'], $value['year']);
		$value = date("d/m/Y", $value);
		$row->set_value($name, $value);
	}
	
	function before_filter($filter_by) {
		$index = $filter_by->index("start_date");
		if ($index === false) return;

		// parse date and set new rule
		$value = $filter_by->rules[$index]['value'];
		$value = explode("/", $value);
		$value = $value[2].'-'.$value[1].'-'.$value[0];
		$filter_by->rules[$index] = "DATE(`start_date`)='{$value}'";
	}
?>
#3

Thank you very much for the code radyno,
I was using DATE_FORMAT(askedDate,’%d/%m/%Y’) in my query to fill my grid but I changed that to just askedDate so that the date can be changed in php code instead. The filter works ok except that if I clear the selection in the filter selectbox I get no data in my grid and my log file doesn’t log anything, why is that?

#4

Hi,
please, modify code like here:

function before_filter($filter_by) {
	$index = $filter_by->index("start_date");
	// parse date and set new rule
	$value = $filter_by->rules[$index]['value'];
	if ($value!="") {
		$value = explode("/", $value);
		$value = $value[2].'-'.$value[1].'-'.$value[0];
		$filter_by->rules[$index] = "DATE(`start_date`)='{$value}'";
	}
}
#5

works! Thank you radyno, have I understood the before_filter function correctly I wouldn’t have to bother you, I am sorry for that, have a great day!