date conversion Searchbuilder

nlooije

Link to test case:
Debugger code (debug.datatables.net): apipiv
Error messages shown: DataTables warning: table id=dynamic-table-open-purchase - Ajax error. For more information about this error, please see http://datatables.net/tn/7
Description of problem: My database has the date in the form of YYYYMMDD, everything works well with the editor when using:

Field::inst('DATE')
                    ->getFormatter(Format::dateTime('Ymd', 'Y-m-d'))
                    ->setFormatter(Format::dateTime('Y-m-d', 'Ymd'))
                    ->validator(Validate::dateFormat('Y-m-d',
                                                     ValidateOptions::inst()
                                                                    ->allowEmpty(false)
                                                                    ->message('Please use date in YYYY-MM-DD'
                                                                    )
                    )
                    )

On my screen and in the DateTime I want to have it as YYYY-MM-DD. This is working with the following:

 {
                                                   label: "Confirmed Delivery date:",
                                                   name: "DATE",
                                                   type: 'datetime',
                                                   opts: {
                                                       showWeekNumber: true,
                                                       disableDays: [0, 6],
                                                       yearRange: 5,
                                                       buttons: {
                                                           today: true,
                                                       },
                                                   },
                                                   dateFormat: 'yyyy-mm-dd',
                                               }

When using the searchbuilder it also detect the date format but is it doesn't convert the date to YYYYMMDD format, the debug sends:

searchBuilder[criteria][0][condition]: =
searchBuilder[criteria][0][data]: Date confirmed
searchBuilder[criteria][0][origData]: DATE
searchBuilder[criteria][0][type]: date
searchBuilder[criteria][0][value][]: 2021-10-28
searchBuilder[criteria][0][value1]: 2021-10-28
searchBuilder[logic]: AND

When using searchBuilderType: 'moment-YYYYMMDD':

searchBuilder[criteria][0][condition]: =
searchBuilder[criteria][0][data]: Date confirmed
searchBuilder[criteria][0][origData]: DATE
searchBuilder[criteria][0][type]: moment-YYYYMMDD
searchBuilder[criteria][0][value][]: 2021-10-27T22:00:00.000Z
searchBuilder[criteria][0][value1]: 2021-10-27T22:00:00.000Z
searchBuilder[logic]: AND

When I use searchBuilderType: 'string' and type in manual 20211027 then it works well.

nlooije

It seems that in datatables.js it is forced to convert the datetime toISO when using moment. I think there should be an option to give the format how to push back the input of the datetime format and how the display it on the screen.

When I change the code in datatables.js to not make an toISOString it works with the searchBuilderType: 'moment-YYYYMMDD':

else if (this.s.type.includes('moment')) {
                    for (var i = 0; i < this.s.value.length; i++) {
                        // this.s.value[i] = moment(this.s.value[i], this.s.dateFormat).toISOString();
                        this.s.value[i] = this.s.value[i];
                    }
                }

Then the $_POST is:

searchBuilder[criteria][0][condition]: =
searchBuilder[criteria][0][data]: Date confirmed
searchBuilder[criteria][0][origData]: BEVESTIGDELEVERDATUM
searchBuilder[criteria][0][type]: moment-YYYYMMDD
searchBuilder[criteria][0][value][]: 20211027
searchBuilder[criteria][0][value1]: 20211027
searchBuilder[logic]: AND

This should also be possible for searchBuilderType: 'date', just give some extra information in which form push the date back to the query

sandy

Hi @nlooije ,

The line that you are referencing isn't just used for server side processing, but also when using client side processing, allowing easy comparison of dates.

Is that column a date type in your database? Or is it stored as a string?

Since you have access to the POST data within the controller you could spin through the data there and convert it to whatever format you wish.

Thanks,
Sandy

nlooije

The column in the database is an string in the form of YYYYMMDD. With the editor it works perfectly in combination with the DataTables PHP library. When using the editor it also send by $_POST an edit for the date in the form of YYYY-DD-MM and then the PHP with setFormatter uses in the queries the YYYYMMDD format. For the searchbuilder it it doens't use the setFormatter and just put in straight the values of the search boxes into the SQL queries. The $_POST I can't change because it is injected directly into the PHP library with ->process($_POST)

nlooije

I have solved it now to manipulate the $_POST data with the following:

// Convert date YYY-MM-DD to YYYYMMDD in for Searchbuilder
if (!empty($_POST)) {
    // Array of post columns to manipulate.
    $postNameArr = array('DATE1', 'DATE2', 'DATE3');
    foreach ($_POST['searchBuilder']['criteria'] as &$value) {
        if (in_array($value['origData'], $postNameArr) && $value['type'] === 'date') {
            foreach ($value['value'] as &$item) {
                $date = date_create_from_format( 'Y-m-d', $item );
                $item = date_format( $date, 'Ymd' );
            }
            foreach ($value as $key => &$item) {
                if (preg_match('/(value.)/', $key)) {
                    $date = date_create_from_format( 'Y-m-d', $item );
                    $item = date_format( $date, 'Ymd' );
                }
            }
        }
    }
}

colin

Excellent, thanks for posting back,

Colin