Bootstrap Datagrid demo - Set rules

Start datagrid with predefined filter values, just setting filter_rules attribute.

Get the code from [Code ] tab.

<!--  It is a good idea to bundle all CSS in one file. The same with JS -->

<!--  JQUERY -->
<script type="text/javascript" src="/path/to/jquery.min.js"></script>

<!--  BOOTSTRAP -->
<link rel="stylesheet" type="text/css" href="/path/to/bootstrap.min.css">
<script type="text/javascript" src="/path/to/bootstrap.min.js"></script>

<!--  JQUERY-UI (only sortable and datepicker is needed) -->
<link rel="stylesheet" type="text/css" href="/path/to/jquery-ui.min.css">
<script type="text/javascript" src="/path/to/jquery-ui.min.js"></script>
<!--  if timepicker is used in filters -->
<link rel="stylesheet" type="text/css" href="/path/to/jquery-ui-timepicker-addon.min.css"/>
<script type="text/javascript" src="/path/to/jquery-ui-timepicker-addon.min.js"></script>
<!--  if touch event support is needed (mobile devices) -->
<script type="text/javascript" src="/path/to/jquery.ui.touch-punch.min.js"></script>
 
<!--  PAGINATION plugin -->
<link rel="stylesheet" type="text/css" href="/path/to/jquery.bs_pagination.min.css">
<script type="text/javascript" src="/path/to/jquery.bs_pagination.min.js"></script>
<script type="text/javascript" src="/path/to/bs_pagination/localization/en.min.js"></script>

<!--  FILTERS plugin --> 
<link rel="stylesheet" type="text/css" href="/path/to/jquery.jui_filter_rules.bs.min.css">
<script type="text/javascript" src="/path/to/jquery.jui_filter_rules.min.js"></script>
<script type="text/javascript" src="/path/to/jui_filter_rules/localization/en.min.js"></script>
<!--  required from filters plugin -->
<script type="text/javascript" src="/path/to/moment.js"></script>

<!--  DATAGRID plugin -->
<link rel="stylesheet" type="text/css" href="/path/to/jquery.bs_grid.min.css">
<script type="text/javascript" src="/path/to/jquery.bs_grid.min.js"></script>
<script type="text/javascript" src="/path/to/bs_grid/localization/en.min.js"></script>
<!--  Just create a div and give it an ID -->

<div id="demo_grid1"></div>
$(function() {

    $("#demo_grid1").bs_grid({

        ajaxFetchDataURL: "ajax_fetch_page_data.php",
        row_primary_key: "customer_id",

        columns: [
            {field: "customer_id", header: "Code", visible: "no"},
            {field: "lastname", header: "Lastname"},
            {field: "firstname", header: "Firstname"},
            {field: "email", header: "Email", visible: "no", "sortable": "no"},
            {field: "gender", header: "Gender"},
            {field: "date_updated", header: "Date updated"}
        ],

        sorting: [
            {sortingName: "Code", field: "customer_id", order: "none"},
            {sortingName: "Lastname", field: "lastname", order: "ascending"},
            {sortingName: "Firstname", field: "firstname", order: "ascending"},
            {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: "Gender", "filterType": "number", "numberType": "integer", 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: "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"}
                        ]
                    }
                }
            ],
            filter_rules: [
                {
                    "condition": {
                        "filterType": "text",
                        "field": "lastname",
                        "operator": "contains",
                        "filterValue": [
                            "A"
                        ]
                    },
                    "logical_operator": "AND"
                },
                {
                    "condition": {
                        "filterType": "number",
                        "numberType": "integer",
                        "field": "lk_genders_id",
                        "operator": "in",
                        "filterValue": [
                            "2"
                        ]
                    },
                    "logical_operator": "AND"
                }
            ]
        }
    });

});
/**
 * 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();
}
<?php
require_once '/path/to/dacapo.class.php'; // simple database wrapper
require_once '/path/to/jui_filter_rules.php';
require_once '/path/to/bs_grid.php';

$db_settings = array(
	'rdbms' => 'MYSQLi',
	'db_server' => 'SERVER_NAME OR IP',
	'db_user' => 'DB USER',
	'db_passwd' => 'DB PASS',
	'db_name' => 'DB NAME',
	'db_port' => '3306',
	'charset' => 'utf8',
	'use_pst' => true, // use prepared statements
	'pst_placeholder' => 'question_mark'
);

$ds = new dacapo($db_settings, null);

$page_settings = array(
	"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
	                FROM customers c INNER JOIN lk_genders g ON (c.lk_genders_id = g.id)",
	"page_num" => $_POST['page_num'],
	"rows_per_page" => $_POST['rows_per_page'],
	"columns" => $_POST['columns'],
	"sorting" => isset($_POST['sorting']) ? $_POST['sorting'] : array(),
	"filter_rules" => isset($_POST['filter_rules']) ? $_POST['filter_rules'] : array()
);

$jfr = new jui_filter_rules($ds);
$jdg = new bs_grid($ds, $jfr, $page_settings, $_POST['debug_mode'] == "yes" ? true : false);

$data = $jdg->get_page_data();

// data conversions (if necessary)
foreach($data['page_data'] as $key => $row) {
	// this will convert Lastname to a link
	$data['page_data'][$key]['lastname'] = "<a href=\"/test/{$row['customer_id']}\">{$row['lastname']}</a>";
	// this will format date_updated (attention date_convert is a local function)
	$data['page_data'][$key]['date_updated'] = date_convert($row['date_updated'], 'UTC', 'YmdHis', 'UTC', 'd/m/Y H:i:s');
}

echo json_encode($data);

More demos

Demo SQL code

SQL of this demo is available here (MySQL).