Subscribe to our Newsletter

Follow us:

Subscribe to our RSS feed

Get Noticed with a $7.99 .COM or .CO!

jui_datagrid (v0.9.0)

Show

demo

Theme switch using jui_theme_switch.

rowSelectionMode:

HTML (head section)

	<link id="ui-theme" rel="stylesheet" type="text/css" href="http://code.jquery.com/ui/1.10.0/themes/ui-lightness/jquery-ui.css"/>
	<link rel="stylesheet" type="text/css" href="http://pontikis.github.com/lib/jQuery-Timepicker-Addon/v1.1.1/jquery-ui-timepicker-addon.css"/>

	<link rel="stylesheet" type="text/css" href="http://pontikis.github.com/jui_dropdown/v1.0.2/jquery.jui_dropdown.css"/>
	<link rel="stylesheet" type="text/css" href="http://pontikis.github.com/jui_pagination/v2.0.0/jquery.jui_pagination.css"/>
	<link rel="stylesheet" type="text/css" href="http://pontikis.github.com/jui_filter_rules/v1.0.0/jquery.jui_filter_rules.css"/>
	<link rel="stylesheet" type="text/css" href="http://pontikis.github.com/jui_datagrid/v0.9.0/jquery.jui_datagrid.css"/>

	<!-- custom classes -->
	<link rel="stylesheet" href="index.css">

	<script type="text/javascript" src="http://pontikis.github.com/lib/momentjs/v1.7.2/moment.min.js"></script>
	<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.3.min.js"></script>
	<script type="text/javascript" src="http://code.jquery.com/ui/1.10.0/jquery-ui.min.js"></script>
	<script type="text/javascript" src="http://pontikis.github.com/lib/jQuery-Timepicker-Addon/v1.1.1/jquery-ui-timepicker-addon.js"></script>

	<!-- if touch event support is needed (mobile devices) -->
	<script type="text/javascript" src="http://pontikis.github.com/lib/jquery.ui.touch-punch/v0.2.2/jquery.ui.touch-punch.min.js"></script>

	<script type="text/javascript" src="http://pontikis.github.com/jui_dropdown/v1.0.2/jquery.jui_dropdown.min.js"></script>

	<script type="text/javascript" src="http://pontikis.github.com/jui_pagination/v2.0.0/jquery.jui_pagination.min.js"></script>
	<script type="text/javascript" src="http://pontikis.github.com/jui_pagination/v2.0.0/i18n/en.js"></script>

	<script type="text/javascript" src="http://pontikis.github.com/jui_filter_rules/v1.0.0/jquery.jui_filter_rules.min.js"></script>
	<script type="text/javascript" src="http://pontikis.github.com/jui_filter_rules/v1.0.0/i18n/en.js"></script>

	<script type="text/javascript" src="http://pontikis.github.com/jui_datagrid/v0.9.0/jquery.jui_datagrid.min.js"></script>
	<script type="text/javascript" src="http://pontikis.github.com/jui_datagrid/v0.9.0/i18n/en.js"></script>

HTML (body)

    <div id="demo_grid1"></div>
    <div id="demo_grid2"></div>

JS code

	$(function() {
		// demo grid1 --------------------------------------------------------------
		$("#demo_grid1").jui_datagrid({

			columns: [
				{field: "customer_id", visible: "no", "header": 'Code', "headerClass": "th_code", "dataClass": "td_code"},
				{field: "lastname", visible: "yes", "header": 'Lastname', "headerClass": "th_lastname", "dataClass": "td_lastname"},
				{field: "firstname", visible: "yes", "header": 'Firstname', "headerClass": "th_firstname", "dataClass": "td_firstname"},
				{field: "email", visible: "no", "header": 'Email', "headerClass": "th_email", "dataClass": "td_email"},
				{field: "gender", visible: "yes", "header": 'Gender', "headerClass": "th_gender", "dataClass": "td_gender"},
				{field: "date_of_birth", visible: "yes", "header": 'Date of birth', "headerClass": "th_date_of_birth", "dataClass": "th_date_of_birth",
					column_value_conversion: {
						function_name: "date_decode",
						args: [
							{"col_index": 5},
							{"value": "DD/MM/YYYY"}
						]
					}
				},
				{field: "date_updated", visible: "yes", "header": 'Date updated', "headerClass": "th_date_updated", "dataClass": "th_date_updated",
					column_value_conversion_server_side: {
						function_name: "UTC_timestamp_to_local_datetime",
						args: [
							{"col_index": 6},
							{"value": "Europe/Athens"},
							{"value": "d/m/Y H:m:s"}
						]
					}
				}
			],

			sorting: [
				{sortingName: "Code", field: "customer_id", order: "none"},
				{sortingName: "Lastname", field: "lastname", order: "ascending"},
				{sortingName: "Firstname", field: "firstname", order: "ascending"},
				{sortingName: "Date of birth", field: "date_of_birth", order: "none"},
				{sortingName: "Date updated", field: "date_updated", order: "none"}
			],

			filterOptions: {
				filters: [
					{
						filterName: "Lastname", "filterType": "text", field: "lastname", filterLabel: "Last name",
						excluded_operators: ["in", "not_in"],
						filter_interface: [
							{
								filter_element: "input",
								filter_element_attributes: {"type": "text"}
							}
						]
					},
					{
						filterName: "Firstname", "filterType": "text", field: "firstname", filterLabel: "First name",
						excluded_operators: ["in", "not_in"],
						filter_interface: [
							{
								filter_element: "input",
								filter_element_attributes: {"type": "text"}
							}
						]
					},
					{
						filterName: "Gender", "filterType": "number", field: "lk_genders_id", filterLabel: "Gender",
						excluded_operators: ["equal", "not_equal", "less", "less_or_equal", "greater", "greater_or_equal"],
						filter_interface: [
							{
								filter_element: "input",
								filter_element_attributes: {type: "checkbox"}
							}
						],
						lookup_values: [
							{lk_option: "Male", lk_value: "1"},
							{lk_option: "Female", lk_value: "2", lk_selected: "yes"}
						]
					},
					{
						filterName: "DateOfBirth", "filterType": "date", field: "date_of_birth", filterLabel: "Date of birth",
						excluded_operators: ["in", "not_in"],
						filter_interface: [
							{
								filter_element: "input",
								filter_element_attributes: {
									type: "text",
									title: "Set the date using format: dd/mm/yyyy"
								},
								filter_widget: "datepicker",
								filter_widget_properties: {
									dateFormat: "dd/mm/yy",
									changeMonth: true,
									changeYear: true
								}
							}
						],
						validate_dateformat: ["DD/MM/YYYY"],
						filter_value_conversion_server_side: {
							function_name: "date_encode",
							args: [
								{"filter_value": "yes"},
								{"value": "d/m/Y"}
							]
						}
					},
					{
						filterName: "DateUpdated", "filterType": "date", field: "date_updated", filterLabel: "Datetime updated",
						excluded_operators: ["in", "not_in"],
						filter_interface: [
							{
								filter_element: "input",
								filter_element_attributes: {
									type: "text",
									title: "Set the date and time using format: dd/mm/yyyy hh:mm:ss"
								},
								filter_widget: "datetimepicker",
								filter_widget_properties: {
									dateFormat: "dd/mm/yy",
									timeFormat: "HH:mm:ss",
									changeMonth: true,
									changeYear: true,
									showSecond: true
								}
							}
						],
						validate_dateformat: ["DD/MM/YYYY HH:mm:ss"],
						filter_value_conversion: {
							function_name: "local_datetime_to_UTC_timestamp",
							args: [
								{"filter_value": "yes"},
								{"value": "DD/MM/YYYY HH:mm:ss"}
							]
						}
					}
				],

				decimal_separator: "."
			},

			dlgFiltersClass: 'grid1_filters',

			ajaxFetchDataURL: 'ajax/ajax_fetch_data1.php',
			row_primary_key: 'customer_id',

			containerClass: 'grid1_container ui-state-default ui-corner-all',
			datagridClass: 'grid1_data ui-widget-content',

			caption: 'Customers',

			showExportButton: false,
			showPrintButton: false,

			debug_mode: "yes",

			onDelete: function() {
				var a_sel = $(this).jui_datagrid("getSelectedIDs"),
					sel = a_sel.length;
				if(sel == 0) {
					log = 'Nothing selected...';
					create_log(elem_dlg_log1, log);
				} else {
					log = sel + ' Row(s) with ID: ' + a_sel + ' will be deleted.';
					create_log(elem_dlg_log1, log);
				}
			},
			onCellClick: function(event, data) {
				log = 'Click on cell: col ' + data.col + ' row ' + data.row + '.';
				create_log(elem_dlg_log1, log);
			},
			onRowClick: function(event, data) {
				log = 'Row with ID ' + data.row_id + ' ' + data.row_status + '.';
				create_log(elem_dlg_log1, log);
			},
			onDatagridError: function(event, data) {
				log = 'ERROR: ' + data.err_description + ' (' + data.err_code + ').';
				create_log(elem_dlg_log1, log, true);
				if(data.hasOwnProperty("elem_filter")) {
					data.elem_filter.focus();
				}
			},
			onDebug: function(event, data) {
				create_log(elem_dlg_log1, data.debug_message, false, true);
			},
			onDisplay: function() {
				log = 'Datagrid created.';
				create_log(elem_dlg_log1, log);
			}
		});

		$("#selection_multiple").click(function() {
			$("#demo_grid1").jui_datagrid({
				rowSelectionMode: 'multiple'
			})
		});

		$("#selection_single").click(function() {
			$("#demo_grid1").jui_datagrid({
				rowSelectionMode: 'single'
			})
		});

		$("#selection_false").click(function() {
			$("#demo_grid1").jui_datagrid({
				rowSelectionMode: false
			})
		});


		elem_dlg_log1.dialog({
			autoOpen: true,
			width: 250,
			height: 200,
			position: {
				my: "left",
				at: "right",
				of: '#demo_grid1'
			},
			title: "Log demo_grid1"
		});

		$("#log_show").click(function() {
			elem_dlg_log1.dialog("open");
			return false;
		});

		$("#log_hide").click(function() {
			elem_dlg_log1.dialog("close");
			return false;
		});

		$("#log_clear").click(function() {
			elem_dlg_log1.html('');
		});

		// demo grid2 --------------------------------------------------------------
		$("#dlg_demo_grid2").dialog({
			autoOpen: false,
			title: "Customers dialog",
			width: 750,
			height: 390,
			hide: "fade",
			zIndex: 500,
			open: function() {
				// just to fix column widths
				$("#demo_grid2").jui_datagrid('refresh');
			}
		});

		$("#demo_grid2").jui_datagrid({

			columns: [
				{field: "id", visible: "no", "header": 'Code', "headerClass": "", "dataClass": ""},
				{field: "lastname", visible: "yes", "header": 'Lastname', "headerClass": "", "dataClass": ""},
				{field: "firstname", visible: "yes", "header": 'Firstname', "headerClass": "", "dataClass": ""},
				{field: "gender", visible: "yes", "header": 'Gender', "headerClass": "", "dataClass": ""}
			],

			sorting: [
				{"sortingName": "Code", field: "id", order: "ascending"},
				{"sortingName": "Lastname", field: "lastname", order: "none"},
				{"sortingName": "Firstname", field: "firstname", order: "none"}
			],

			ajaxFetchDataURL: 'ajax/ajax_fetch_data2.php',
			row_primary_key: 'id',

			containerClass: 'grid2_container ui-state-default ui-corner-all',
			datagridClass: 'grid2_data ui-widget-content',

			paginationOptions: {
				sliderAnimation: false
			}
		});


		elem_dlg_demo_grid2_opener.button({
			icons: {
				primary: 'ui-icon-newwin'
			}
		});

		elem_dlg_demo_grid2_opener.click(function() {
			$("#dlg_demo_grid2").dialog("open");
			return false;
		});

	});

JS demo functions

function create_log(elem_log, log, is_error, is_debug) {
    var line_number = parseInt(elem_log.find("p").length) + 1;
    if(is_error) {
        elem_log.prepend('
' + line_number + ') ' + log);
    } else if(is_debug) {
    var log_len = log.length, d;

    var debug_html = '
' + line_number + ') DEBUG LOG:';
    debug_html += '
    ';
    for(d = 0; d < log_len; d++) {
    debug_html += '
  • ' + log[d];
        }
        debug_html += '
';

        elem_log.prepend(debug_html);
    } else {
        elem_log.prepend('
' + line_number + ') ' + log);
    }

    }


    /**
    * Converts date (no time) string of YYYYMMDD to date dtring of given format
    *
    * @param {String} date_str
    * @param {String} dateformat
    * @return {String}
    */
    function date_decode(date_str, dateformat) {

    if(date_str == null || date_str.length == 0) {
    return '';
    }

    var year = date_str.substr(0, 4),
    month = date_str.substr(4, 2),
    day = date_str.substr(6, 2);

    var a = moment([year, month - 1, day]);
    return a.format(dateformat);
    }


    /**
    * Convert local timezone date string to UTC timestamp
    *
    * Alternative syntax using jquery (instead of moment.js):
    *     var date = $.datepicker.parseDateTime(dateformat, timeformat, date_str);
    *
    * @see http://stackoverflow.com/questions/948532/how-do-you-convert-a-javascript-date-to-utc
    * @param {String} date_str
    * @param {String} dateformat
    * @return {String}
    */
    function local_datetime_to_UTC_timestamp(date_str, dateformat) {

    // avoid date overflow in user input (moment("14/14/2005", "DD/MM/YYYY") => Tue Feb 14 2006)
    if(moment(date_str, dateformat).isValid() == false) {
    throw new Error("Invalid date");
    }

    // parse date string using given dateformat and create a javascript date object
    var date = moment(date_str, dateformat).toDate();

    // use javascript getUTC* functions to conv ert to UTC
    return  date.getUTCFullYear() +
    PadDigits(date.getUTCMonth() + 1, 2) +
    PadDigits(date.getUTCDate(), 2) +
    PadDigits(date.getUTCHours(), 2) +
    PadDigits(date.getUTCMinutes(), 2) +
    PadDigits(date.getUTCSeconds(), 2);

    }

    /**
    * Add leading zeros
    * @param {Number} n
    * @param {Number} totalDigits
    * @return {String}
    */
    function PadDigits(n, totalDigits) {
    n = n.toString();
    var pd = '';
    if(totalDigits > n.length) {
    for(i = 0; i < (totalDigits - n.length); i++) {
    pd += '0';
    }
    }
    return pd + n.toString();
    }

CSS

#tab-demo ul li {
    background: none;
}

.wrap {
    padding: 5px;
}

.spacer {
    margin: 20px 0;
}

#dlg_demo_grid1_log p {
    padding-bottom: 5px;
    font-size: 12px;
    font-family: "courier new";
}

#dlg_demo_grid1_log li {
    padding-bottom: 10px;
    font-size: 11px;
    font-family: "courier new";
}

/* demo_grid1 --------------------------------------------------------------- */
.grid1_container {
    width: 750px;
    padding: 5px;
    background: none !important;
}

.grid1_data {
    padding: 0;
    margin: 0 0 5px 0;
    border: none !important;
    height: 210px;
    overflow-y: auto;
    overflow-x: hidden !important;
}
.grid1_filters {
    font-size: .9em !important;
    width: 700px !important;
}

.th_code, .th_lastname, .th_firstname, .th_email, .th_gender, .th_date_of_birth, .th_date_updated {
    text-align: left;
}

.th_code {
    width: 5%;
}
.th_lastname {
    width: 17%;
}
.th_firstname {
    width: 12%;
}
.th_email {
    width: 50%;
}

.th_gender {
    width: 10%;
}

.th_date_of_birth {
    width: 20%;
}

.th_date_updated {
    width: 20%;
}

.td_code {
    width: 5%;
    font-family: "courier new";
}
.td_lastname {
    width: 17%;
}
.td_firstname {
    width: 12%;
}
.td_email {
    width: 50%;
    color: blue;
    font-family: "courier new";
}

.td_gender {
    width: 10%;
    font-size: 11px;
}

.td_date_of_birth {
    width: 20%;
}
.td_date_updated {
    width: 20%;
}


/* demo_grid2 --------------------------------------------------------------- */
.dlg_demo_grid2 {
    padding-left: 3px !important;
    font-size: 14px;
}

.grid2_container {
    width: 100%;
    padding: 5px;
    background: none !important;
}

.grid2_data {
    padding: 0;
    margin: 0 0 5px 0;
    border: none !important;
    height: 200px;
    overflow: auto;
}


/* demo_rules1 widgets ------------------------------------------------------ */
.ui-datepicker{
    font-size: 12px;
}

.ui-autocomplete{
    font-size: 12px;
}

ajax_fetch_data1.php

    <?php
        // prevent direct access (optional) --------------------------------------------
		$isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
			strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
		if(!$isAjax) {
			print 'Access denied - not an AJAX request...' . ' (' . __FILE__ . ')';
			exit;
		}
        // required --------------------------------------------------------------------
		require_once '../../../../common/settings.php';
		require_once PROJECT_PATH . '/lib/php_adodb_v5.18/adodb.inc.php';
		require_once PROJECT_PATH . '/labs/jui_filter_rules/server_side/php/jui_filter_rules.php';
		require_once PROJECT_PATH . '/labs/jui_datagrid/server_side/php/jui_datagrid.php';
		require_once 'demo_functions.php';

		$db_settings = $dbcon_settings;

		$selectCountSQL = 'SELECT count(id) as totalrows FROM customers';
		$selectSQL = 'SELECT c.id as customer_id, c.lastname, c.firstname, c.email, g.gender, c.date_updated, c.date_of_birth ' .
			'FROM customers c INNER JOIN lk_genders g ON (c.lk_genders_id = g.id)';

		include('../../server_side/php/fetch_data.php');
		?>

ajax_fetch_data2.php

	<?php
    // prevent direct access (optional) --------------------------------------------
	$isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
		strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
	if(!$isAjax) {
		print 'Access denied - not an AJAX request...' . ' (' . __FILE__ . ')';
		exit;
	}
// required --------------------------------------------------------------------
	require_once '../../../../common/settings.php';
	require_once PROJECT_PATH . '/lib/php_adodb_v5.18/adodb.inc.php';
	require_once PROJECT_PATH . '/labs/jui_filter_rules/server_side/php/jui_filter_rules.php';
	require_once PROJECT_PATH . '/labs/jui_datagrid/server_side/php/jui_datagrid.php';

	$db_settings = $dbcon_settings;

	$selectCountSQL = 'SELECT count(id) as totalrows FROM customers';
	$selectSQL = 'SELECT c.id, c.lastname, c.firstname, g.gender ' .
		'FROM customers c INNER JOIN lk_genders g ON (c.lk_genders_id = g.id)';

	include('../../server_side/php/fetch_data.php');
	?>

fetch_data.php


	<?php
	/**
	 * Sample php file getting totalrows and database data for current page
	 *
	 */

    // initialize ------------------------------------------------------------------
	$total_rows = null;
	$a_data = null;
	$last_error = null;
	$last_filter_error = array();
	$debug_message = array();
	$result = array(
		'total_rows' => $total_rows,
		'page_data' => $a_data,
		'error' => $last_error,
		'filter_error' => $last_filter_error,
		'debug_message' => $debug_message
	);

    // get params ------------------------------------------------------------------
	$page_num = $_POST['page_num'];
	$rows_per_page = $_POST['rows_per_page'];

	$columns = $_POST['columns'];

	$filter_rules = array();
	if(isset($_POST['filter_rules'])) {
		$filter_rules = $_POST['filter_rules'];
	}

	$sorting = array();
	if(isset($_POST['sorting'])) {
		$sorting = $_POST['sorting'];
	}

	$debug_mode = ($_POST['debug_mode'] == "yes" ? true : false);

    // -----------------------------------------------------------------------------
	$jdg = new jui_datagrid($debug_mode);
	$conn = $jdg->db_connect($db_settings);
	if($conn === false) {
		$last_error = $jdg->get_last_error();
	} else {
		$where = $jdg->get_whereSQL($conn, $filter_rules);

		if(array_key_exists('error_message', $where)) {
			$last_filter_error = $where;
		} else {
			$whereSQL = $where['sql'];
			$bind_params = $where['bind_params'];

			$total_rows = $jdg->get_total_rows($conn, $selectCountSQL, $whereSQL, $bind_params);

			if($total_rows === false) {
				$last_error = $jdg->get_last_error();
			} else {
				$a_data = $jdg->fetch_page_data($conn, $columns, $page_num, $rows_per_page, $selectSQL, $sorting, $whereSQL, $bind_params);
				if($a_data === false) {
					$last_error = $jdg->get_last_error();
				}
			}
		}
		$jdg->db_disconnect($conn);
	}

    // return JSON -----------------------------------------------------------------
	$result['total_rows'] = $total_rows;
	$result['page_data'] = $a_data;
	$result['error'] = $last_error;
	$result['filter_error'] = $last_filter_error;
	$result['debug_message'] = $jdg->get_debug_message();
	$json = json_encode($result);
	print $json;
	?>

demo_functions.php

<?php
/**
 * Convert date string (without time) of given format to YYYYMMDD string
 *
 * @param string $str_user_date
 * @param string $str_user_dateformat
 * @return string
 */
function date_encode($str_user_date, $str_user_dateformat) {
	$date = DateTime::createFromFormat($str_user_dateformat, $str_user_date);
	return $date->format("Y") . $date->format("m") . $date->format("d");
}

/**
 * Converts a UTC timestamp to date string of given timezone (considering DST) and given dateformat
 *
 * DateTime requires PHP >= 5.2
 *
 * @param $str_server_datetime
 *
 * <li>Normally is a 14-digit UTC timestamp (YYYYMMDDHHMMSS). It can also be 8-digit (date), 12-digit (datetime without seconds).
 * If given dateformat (<var>$str_user_dateformat</var>) is longer than <var>$str_server_datetime</var>,
 * the missing digits of input value are filled with zero,
 * so (YYYYMMDD is equivalent to YYYYMMDD000000 and YYYYMMDDHHMM is equivalent to YYYYMMDDHHMM00).
 *
 * <li>It can also be 'now', null or empty string. In this case returns the current time.
 *
 * <li>Other values (invalid datetime strings) throw an error. Milliseconds are not supported.
 *
 * @param string $str_user_timezone
 * @param $str_user_dateformat
 * @return string
 */
function UTC_timestamp_to_local_datetime($str_server_datetime,
										 $str_user_timezone,
										 $str_user_dateformat) {

	if(is_null($str_server_datetime) || $str_server_datetime == '') {
		return '';
	}

	// create date object
	$date = new DateTime($str_server_datetime);

	// convert to user timezone
	$userTimeZone = new DateTimeZone($str_user_timezone);
	$date->setTimeZone($userTimeZone);

	// convert to user dateformat
	$str_user_datetime = $date->format($str_user_dateformat);

	return $str_user_datetime;
}