Searchpanes Help

Searchpanes Help

OburgwinOburgwin Posts: 11Questions: 2Answers: 0

Screenshot of panes for context

In the status pane, I want to change each status to it's respective name, so rather than 1, it displays new

In the Port ETA pane, I'd like to change dates to the format to appear by Month. So 2021-09-04, 2021-09-16, 2021-09-17, etc would be merged into September 2021

In the CTR Size pane, the are two nulls, this happens in multiple panes when it displays the same data. In some it does, in others it doesn't. How would this be rectified, and changed to "Blank". Example below shows a separate case where it shows 'null' twice and '' twice but it's all the same selection

Any advice on these would be appreciated.

Particularly The Port ETA pane, I've read a few articles like this but not got as much out of it as I'd like

This question has an accepted answers - jump to answer

Answers

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0

    Solved the Null Issue, I assume being able to rename 1 to new would also solve renaming "No Data" to "Blank"

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0
    edited September 2021

    For Status label rendering, I tried:

    {
        searchPanes: {
            show: true,
            options: [
                {
                    label: 'New',
                    value: function(rowData, rowIdx) {
                        return rowData['status'] === '1';
                    }
                },
                {
                    label: 'In Production',
                    value: function(rowData, rowIdx) {
                        return rowData['status'] === '2';
                    }
                },
                {
                    label: 'Cargo Ready',
                    value: function(rowData, rowIdx) {
                        return rowData['status'] === '3';
                    }
                },
                {
                    label: 'Loaded',
                    value: function(rowData, rowIdx) {
                        return rowData['status'] === '4';
                    }
                },
                {
                    label: 'On The Water',
                    value: function(rowData, rowIdx) {
                        return rowData['status'] === '5';
                    }
                },
                {
                    label: 'Arrived',
                    value: function(rowData, rowIdx) {
                        return rowData['status'] === '6';
                    }
                }
            ]
        },
        targets: [3]
    }
    

    However this caused the Searchpane to not function as a filter, the counts were all correct, but selecting an option would not give the relevant rows.

    So instead I've changed the rendering in the server-side processing to be the below:

    Field::inst('status')->searchPaneOptions(
                SearchPaneOptions::inst()
                    ->render(function ($str){
                        switch ($str){
                            case '1':
                                return 'New';
                                break;
                            case '2':
                                return 'In Production';
                                break;
                            case '3':
                                return 'Cargo Ready';
                                break;
                            case '4':
                                return 'Loaded';
                                break;
                            case '5':
                                return 'On The Water';
                                break;
                            case '6':
                                return 'Arrived';
                                break;
                        }
                    })
            ),
    

    I'm fairly certain I could render the panes with dates in a similar way to band Months together.

    I'll update this with my solution once I've got it, Maybe someone else finds it useful in future

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0

    Field::inst('requestedshipdate')->searchPaneOptions(
        SearchPaneOptions::inst()
        ->render(function($str){
            return date('M Y',strtotime($str));
        })
    )
    

    Simple enough to format, but as above it's still split by day of the month.

    Any ideas?

  • sandysandy Posts: 913Questions: 0Answers: 236

    Hi @Oburgwin ,

    This sounds like a use for columns.searchPanes.orthogonal data to me if you can work it on the client side. Take a look at this example that makes use of it for the status rendering.

    Thanks,
    Sandy

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0
    edited September 2021

    Thanks for your response, i think this would have solved status' client side had I not done it server-side which is useful for future!

    I tried applying the same fix to dates and did not work, I'll compile related code here now and hopefully can figure out what I need to do to combine the days in a Month to one.

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0
    edited September 2021

    Code below, drilled down a bit. Has the serverside and clientside parts most relevant to dates. I don't think I've missed anything out

                var editor; 
    
                $(document).ready(function() {
    
                    editor = new $.fn.dataTable.Editor( {
                        ajax: "/php/searchPanes.php",
                        table: "#example",
                        fields: [ 
                            {
                                label: "Requested Ship Date:",
                                name:  "requestedshipdate",
                                type:      'datetime',
                                def:       function () { return new Date(); },
                                displayFormat: 'DD/MM/YYYY',
                                wireFormat: 'YYYY-MM-DD',
                            },
                            {
                                label: "Cargo Ready Date:",
                                name:  "cargoreadydate",
                                type:      'datetime',
                                def:       function () { return new Date(); },
                                displayFormat: 'DD/MM/YYYY',
                                wireFormat: 'YYYY-MM-DD',
                            },
                            {
                                label: "Current ETD:",
                                name:  "currentetd",
                                type:      'datetime',
                                def:       function () { return new Date(); },
                                displayFormat: 'DD/MM/YYYY',
                                wireFormat: 'YYYY-MM-DD',
                            },
                            {
                                label: "Requested ETA:",
                                name:  "requestedeta",
                                type:      'datetime',
                                def:       function () { return new Date(); },
                                displayFormat: 'DD/MM/YYYY',
                                wireFormat: 'YYYY-MM-DD',
                            },{
                                label: "ETA UK:",
                                name:  "etauk",
                                type:      'datetime',
                                def:       function () { return new Date(); },
                                displayFormat: 'DD/MM/YYYY',
                                wireFormat: 'YYYY-MM-DD',
                            }
                        ]
                    } );
                    
                    moment.updateLocale(moment.locale(), { invalidDate: "" });
    
                    var table = $('#example').DataTable( {
                        ajax: {
                            url: "/php/searchPanes.php",
                            type: "POST"
                        },
                        columns: [
                           
                            { data: 'requestedshipdate', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  },
                            { data: 'cargoreadydate', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  },
                            { data: 'currentetd', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  },
                            { data: 'requestedeta', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' ) },
                            { data: 'etauk', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  }
                        ],
                        searchPanes:{
                            initCollapsed: true,
                            cascadePanes: true,
                            layout: 'columns-7',
                            viewTotal: true
                        },
                        "iDisplayLength": -1,
                       
                        "order": [[ 7, "asc" ]],
                        columnDefs:[
                          
                            {
                                searchPanes:{
                                    show: true,
                                },
    // Removed some columns to make easier to read so this targets is not correct for the data here
                                targets: [1,2,3,4,5,7,8,9,10,11,12,13,15,16]
                            }
    
    
                        ],
                        serverSide: true,
                        processing: false
                    } );
    
    
     $.fn.dataTable.render.moment = function ( from, to, locale ) {
                    if ( arguments.length === 1 ) {
                        locale = 'en';
                        to = from;
                        from = 'YYYY-MM-DD';
                    }
                    else if ( arguments.length === 2 ) {
                        locale = 'en';
                    }
    
                    return function ( d, type, row ) {
                        if (! d) {
                            return type === 'sort' || type === 'type' ? 0 : d;
                        }
    
                        var m = window.moment( d, from, locale, true );
    
                        return m.format( type === 'sort' || type === 'type' ? 'x' : to );
                    };
                };
    
    

    searchPanes.php

    <?php
    
    // DataTables PHP library
    include( "../lib/DataTables.php" );
    include '../ASEngine/AS.php';
    
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\SearchPaneOptions;
    
    
    // Build our Editor instance and process the data coming from _POST
    $editor = Editor::inst( $db, 'containers' )
        ->fields(
            Field::inst('currentetd')
                ->searchPaneOptions(SearchPaneOptions::inst()
                    ->render(function($str){
                        if(strtotime($str) == 0){
                            return '';
                        }else{
                            return date('M Y',strtotime($str));
                        }
                    })
                )
                ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                ->setFormatter( Format::dateFormatToSql('Y-m-d' )
                )
        )
        ->debug(false)
        ->process($_POST)
        ->json();
    


    Hopefully I can have these merged into one soon enough. I can't imagine getting much success out of custom panes but that's my last resort.

    Thanks again

  • sandysandy Posts: 913Questions: 0Answers: 236

    Hi @Oburgwin ,

    Could you please show me what your response from the server looks like?

    Thanks,
    Sandy

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0
    edited September 2021

    Hi @sandy sorry for the slow response

    Response below is extracted from 27000 lines. I think I've included everything and I've removed identifying information.

    Dates are given like "Jan 1st, 2021" for now as it's a live system I'm pulling data from and without being able to combine data from a given month I've been displaying date labels in searchpanes as "M jS, Y" whereas with combined it would be as "M Y" as in previous posts here.

    {
      "data": [
        {
          "DT_RowId": "row_13255",
          "ctr": "BR3",
          "factory": "Bris",
          "freightrate": "",
          "SO": "",
          "status": "5",
          "customer": "Otters",
          "requestedshipdate": "2021-07-27",
          "cargoreadydate": "2021-07-25",
          "currentetd": "2021-08-23",
          "requestedeta": "2021-09-20",
          "etauk": "2021-09-22",
          "vesselbookedon": "MUNKEBO MAERSK",
          "pod": "FELIXSTOWE",
          "ctrn": "FCIU",
          "term": "DDP",
          "forwarder": "Beacon/MRF",
          "ctr_size": "\t40'H",
          "comments": "",
          "equipment": "",
          "prange": "Turin"
        },
        {
          "DT_RowId": "row_509",
          "ctr": "OUD1",
          "factory": "Oude",
          "freightrate": "",
          "SO": "",
          "status": "1",
          "customer": "Summerhill Nurseries",
          "requestedshipdate": "2022-11-14",
          "cargoreadydate": "2021-11-07",
          "currentetd": null,
          "requestedeta": "2022-12-19",
          "etauk": null,
          "vesselbookedon": "",
          "pod": "",
          "ctrn": "",
          "term": "DDP",
          "forwarder": "Beacon/MRF",
          "ctr_size": "40’H",
          "comments": "",
          "equipment": "",
          "prange": "Catalan"
        }
      ],
      "options": [],
      "files": [],
      "searchPanes": {
        "options": {
          "ctr": [
            {
              "label": "OUD5",
              "total": "1",
              "value": "OUD5",
              "count": "1"
            },
            {
              "label": "TON4",
              "total": "1",
              "value": "TON4",
              "count": "1"
            }
          ],
          "factory": [
            {
              "label": "Bris",
              "total": "295",
              "value": "Bris",
              "count": "295"
            },
            {
              "label": "Genux",
              "total": "4",
              "value": "Genux",
              "count": "4"
            }
          ],
          "status": [
            {
              "label": "Cargo Ready",
              "total": "7",
              "value": "3",
              "count": "7"
            },
            {
              "label": "In Production",
              "total": "24",
              "value": "2",
              "count": "24"
            },
            {
              "label": "Loaded",
              "total": "24",
              "value": "4",
              "count": "24"
            },
            {
              "label": "New",
              "total": "731",
              "value": "1",
              "count": "731"
            },
            {
              "label": "On The Water",
              "total": "20",
              "value": "5",
              "count": "20"
            }
          ],
          "customer": [
            {
              "label": "Altons",
              "total": "2",
              "value": "Altons",
              "count": "2"
            },
            {
              "label": "Barker Bridge",
              "total": "18",
              "value": "Barker Bridge",
              "count": "18"
            }
          ],
          "requestedshipdate": [
            {
              "label": "Apr 11th, 2022",
              "total": "7",
              "value": "2022-04-11",
              "count": "7"
            },
            {
              "label": "Apr 12th, 2022",
              "total": "1",
              "value": "2022-04-12",
              "count": "1"
            }
          ],
          "cargoreadydate": [
            {
              "label": "",
              "total": "1",
              "value": null,
              "count": "1"
            },
            {
              "label": "Apr 11th, 2022",
              "total": "6",
              "value": "2022-04-11",
              "count": "6"
            },
            {
              "label": "Apr 12th, 2022",
              "total": "2",
              "value": "2022-04-12",
              "count": "2"
            },
            {
              "label": "Apr 13th, 2022",
              "total": "2",
              "value": "2022-04-13",
              "count": "2"
            },
            {
              "label": "Apr 15th, 2022",
              "total": "13",
              "value": "2022-04-15",
              "count": "13"
            },
            {
              "label": "Apr 19th, 2022",
              "total": "2",
              "value": "2022-04-19",
              "count": "2"
            },
            {
              "label": "Apr 1st, 2022",
              "total": "1",
              "value": "2022-04-01",
              "count": "1"
            }
          ],
          "currentetd": [
            {
              "label": "",
              "total": "740",
              "value": null,
              "count": "740"
            },
            {
              "label": "Aug 14th, 2021",
              "total": "11",
              "value": "2021-08-14",
              "count": "11"
            },
            {
              "label": "Aug 1st, 2021",
              "total": "2",
              "value": "2021-08-01",
              "count": "2"
            },
            {
              "label": "Aug 23rd, 2021",
              "total": "1",
              "value": "2021-08-23",
              "count": "1"
            },
            {
              "label": "Aug 2nd, 2021",
              "total": "3",
              "value": "2021-08-02",
              "count": "3"
            },
            {
              "label": "Aug 7th, 2021",
              "total": "3",
              "value": "2021-08-07",
              "count": "3"
            },
            {
              "label": "Nov 30th, 0001",
              "total": "1",
              "value": "0001-11-30",
              "count": "1"
            }
          ],
          "requestedeta": [
            {
              "label": "",
              "total": "6",
              "value": null,
              "count": "6"
            },
            {
              "label": "Apr 10th, 2022",
              "total": "3",
              "value": "2022-04-10",
              "count": "3"
            },
            {
              "label": "Apr 11th, 2022",
              "total": "5",
              "value": "2022-04-11",
              "count": "5"
            },
            {
              "label": "Apr 12th, 2022",
              "total": "1",
              "value": "2022-04-12",
              "count": "1"
            },
            {
              "label": "Apr 14th, 2022",
              "total": "2",
              "value": "2022-04-14",
              "count": "2"
            }
          ],
          "etauk": [
            {
              "label": "",
              "total": "740",
              "value": null,
              "count": "740"
            },
            {
              "label": "Nov 11th, 2021",
              "total": "11",
              "value": "2021-11-11",
              "count": "11"
            },
            {
              "label": "Nov 19th, 2021",
              "total": "1",
              "value": "2021-11-19",
              "count": "1"
            },
            {
              "label": "Nov 1st, 2021",
              "total": "2",
              "value": "2021-11-01",
              "count": "2"
            },
            {
              "label": "Nov 27th, 2021",
              "total": "1",
              "value": "2021-11-27",
              "count": "1"
            },
            {
              "label": "Nov 2nd, 2021",
              "total": "4",
              "value": "2021-11-02",
              "count": "4"
            }
          ],
          "vesselbookedon": [
            {
              "label": "",
              "total": "742",
              "value": "",
              "count": "742"
            },
            {
              "label": "CSCL ARCTIC OCEAN",
              "total": "2",
              "value": "CSCL ARCTIC OCEAN",
              "count": "2"
            },
            {
              "label": "EVER ACE ",
              "total": "1",
              "value": "EVER ACE",
              "count": "1"
            }
          ],
          "pod": [
            {
              "label": "",
              "total": "741",
              "value": "",
              "count": "741"
            },
            {
              "label": "Felixstowe ",
              "total": "47",
              "value": "Felixstowe ",
              "count": "47"
            },
            {
              "label": "Liverpool ",
              "total": "13",
              "value": "Liverpool ",
              "count": "13"
            },
            {
              "label": "London Gateway",
              "total": "3",
              "value": "London Gateway",
              "count": "3"
            },
            {
              "label": "Southampton",
              "total": "1",
              "value": "Southampton",
              "count": "1"
            },
            {
              "label": "Teesport",
              "total": "1",
              "value": "Teesport",
              "count": "1"
            }
          ],
          "term": [
            {
              "label": "DDP",
              "total": "510",
              "value": "DDP",
              "count": "510"
            },
            {
              "label": "DP",
              "total": "1",
              "value": "DP",
              "count": "1"
            },
            {
              "label": "FOB",
              "total": "214",
              "value": "FOB",
              "count": "214"
            },
            {
              "label": "Main Stock",
              "total": "81",
              "value": "Main Stock",
              "count": "81"
            }
          ],
          "forwarder": [
            {
              "label": "Beacon/MRF",
              "total": "589",
              "value": "Beacon/MRF",
              "count": "589"
            }
          ],
          "prange": [
            {
              "label": "Amalfi",
              "total": "52",
              "value": "Amalfi",
              "count": "52"
            },
            {
              "label": "Ashfield",
              "total": "9",
              "value": "Ashfield",
              "count": "9"
            }
          ]
        }
      },
      "draw": 1,
      "recordsTotal": "806",
      "recordsFiltered": "806"
    }
    

    Hopefully this is what you're after.

  • sandysandy Posts: 913Questions: 0Answers: 236
    Answer ✓

    Hi @Oburgwin ,

    Looking specifically at the requestedShipDate column, the data being shown in the SearchPanes is not the same as that being sent in your response...

    "requestedshipdate": [
            {
              "label": "Apr 11th, 2022",
              "total": "7",
              "value": "2022-04-11",
              "count": "7"
            },
            {
              "label": "Apr 12th, 2022",
              "total": "1",
              "value": "2022-04-12",
              "count": "1"
            }
          ],
    

    There must be something on the client side that is changing these further. If you are rendering on the ServerSide it is probably not a good idea to then render the SP options again on the client side. I think it would be best if you provide a test case that I can look at to see it all at once and do some debugging. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Thanks,
    Sandy

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0

    I'm sending labels as M jS, Y in this response if that's what you mean, I've done that to distinguish the dates in the searchpanes while I find a solution to the banded months problem. I haven't done any client side rendering that I'm aware of but I'll give it a look

  • OburgwinOburgwin Posts: 11Questions: 2Answers: 0

    I'm replying to say I don't believe what I need is a feature yet.

    Thank you for your time anyway

Sign In or Register to comment.