DataTables

How to sort a column (jobTitle) with a custom type

jobTitle employeeNumber firstName lastName extension
President 1,002 Diane Murphy x5800
VP Sales 1,056 Mary Patterson x4611
VP Marketing 1,076 Jeff Firrelli x9273
Sales Manager (APAC) 1,088 William Patterson x4871
Sale Manager (EMEA) 1,102 Gerard Bondur x5408
Sales Manager (NA) 1,143 Anthony Bow x5428
Sales Rep 1,165 Leslie Jennings x3291
Sales Rep 1,166 Leslie Thompson x4065
Sales Rep 1,188 Julie Firrelli x2173
Sales Rep 1,216 Steve Patterson x4334
Sales Rep 1,286 Foon Yue Tseng x2248
Sales Rep 1,323 George Vanauf x4102
Sales Rep 1,337 Loui Bondur x6493
Sales Rep 1,370 Gerard Hernandez x2028
Sales Rep 1,401 Pamela Castillo x2759
Sales Rep 1,501 Larry Bott x2311
Sales Rep 1,504 Barry Jones x102
Sales Rep 1,611 Andy Fixter x101
Sales Rep 1,612 Peter Marsh x102
Sales Rep 1,619 Tom King x103
Sales Rep 1,621 Mami Nishi x101
Sales Rep 1,625 Yoshimi Kato x102
Sales Rep 1,702 Martin Gerard x2312

This exmaple shows how to sort a column with a custom type

<?php 
    DataTables::create(array(
        ...
        "columns" => [
            "jobTitle" => [
                'type' => 'customType',
            ],
            "employeeNumber", "firstName", "lastName", "extension"
        ],
    ));
?>
<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();
<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        ); 
    } 
    protected function setup()
    {
        $this->src('automaker')
        ->query("SELECT employeeNumber, firstName,lastName,jobTitle, extension from employees")
        ->pipe($this->dataStore("employees"));
    } 

}
<?php
    use \koolreport\datagrid\DataTables;
?>
<div class="report-content">
    <div class="text-center">
        <h1>DataTables</h1>
        <p class="lead">
        How to sort a column (jobTitle) with a custom type
        </p>
    </div>
    <script>
        var customOrder = {
            "President": 0, 
            "VP Sales": 1, 
            "VP Marketing": 2, 
            "Sales Manager (NA)": 3,
            "Sales Manager (APAC)": 4,
            "Sale Manager (EMEA)": 5,
            "Sales Rep": 6
        };
        function dtBeforeInit() {
            console.log('dtBeforeInit');
            jQuery.extend( jQuery.fn.dataTable.ext.type.order, {
                // "customType-pre": function (a) {
                //     console.log('pre', a);
                //     a = a.trim();
                //     return customOrder[a];
                // },
                "customType-asc": function (a, b) {
                    console.log('asc', a, b);
                    a = a.trim(); b = b.trim();
                    return customOrder[b] - customOrder[a];
                },
                "customType-desc": function (a, b) {
                    console.log('desc', a, b);
                    a = a.trim(); b = b.trim();
                    return customOrder[a] - customOrder[b];
                }
            } );
        }
    </script>
    <?php
    DataTables::create(array(
        "dataSource"=>$this->dataStore("employees"),
        "themeBase"=>"bs4", // Optional option to work with Bootsrap 4
        "cssClass"=>array(
            "table"=>"table table-striped table-bordered"
        ),
        "columns" => [
            "jobTitle" => [
                'type' => 'customType',
            ],
            "employeeNumber", "firstName", "lastName", "extension"
        ],
        "options" => array(
            "order" => [ [0, 'desc'] ],
        ),
        "onBeforeInit" => "dtBeforeInit",
    ));
    ?>
</div>

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro