Answers

• rf1234 Posts: 2,809Questions: 85Answers: 406

  May 2022 edited May 2022 Answer ✓

  I would need to see your PHP setFormatter for the dateTime value on the server.

  This is being sent to the server:
  
  based on this input:
  

  That looks ok to me. I am in Cyprus at the moment. Hence Chrome uses GMT+0300 as the timezone which is "Eastern European DST".

  Your set formatter must be able to handle the format that is being sent and convert it to your expected database format.

  This setFormatter might work if your database has the usual dateTime format:

  Field::inst( 'detes_activity.date_time_inspection' )
      ->setFormatter( function($val, $data, $opts) {
          if ( is_null($val) || $val <= '' ) {
              return null;
          }
          $dateTime = new DateTime($val);
          return $dateTime->format('Y-m-d H:i:s');
      }),
  

  I tried it here and it works:
  https://onlinephp.io/c/22bff

  This should be the format your database expects:
  

  If you don't want those seconds you could do:

  return $dateTime->format('Y-m-d H:i:00');
  

  Which gives you this:
  
• Skrocki Posts: 21Questions: 9Answers: 0

  May 2022 edited May 2022

  Thank you very much brother. I appreciate your time and knowledge. Your solution worked perfectly!

  That's a cool sandbox site too. I've never seen it before but I'm gonna try it out.
• Skrocki Posts: 21Questions: 9Answers: 0

  May 2022

  Oops I spoke too soon...your fix does work, but how do I show 'n/j/Y g:i a' format on the table and still save to sql format? I tried varieties with Validate:: and getFormatter but no love the way I was trying it.

  `<?php
  require $_SERVER['DOCUMENT_ROOT'] . '/controllers/components/globalconfig.php';
  include $_SERVER['DOCUMENT_ROOT'] . '/modules/claimtracker/dataTable/php/lib/DataTables.php';

  // Alias Editor classes so they are easy to use
  use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions;

  // Build our Editor instance with SQL and process the data coming from _POST
  Editor::inst($db, 'deets_insured', 'deets_insured.claimtrackerid')
  ->where('deets_insured.usergroup', $usergroup)
  ->fields(
  Field::inst('deeets_insured.claimtrackerid'),
  Field::inst('deets_insured.claim_number'),
  Field::inst('deets_insured.insured_name'),
  Field::inst('deets_insured.insured_address'),
  Field::inst('deets_insured.insured_city'),
  Field::inst('deets_insured.insured_state'),
  Field::inst('deets_insured.insured_zip'),
  Field::inst('deets_insured.insured_county'),
  Field::inst('deets_insured.insured_phone'),
  Field::inst('deets_activity.date_of_loss')
  ->validator(Validate::dateFormat('n/j/Y',))
  ->getFormatter(Format::dateSqlToFormat('n/j/Y'))
  ->setFormatter(Format::dateFormatToSql('n/j/Y')),

        Field::inst('deets_activity.date_time_reported')
             ->validator(Validate::dateFormat('n/j/Y g:i a'))
             ->getFormatter(Format::datetime('Y-m-d H:i:s', 'n/j/Y g:i a'))
             ->setFormatter(Format::datetime('YYYY-MM-DD hh:mm:ss', 'Y-m-d H:i:s')),
  
        Field::inst('deets_activity.date_time_assigned')
             ->setFormatter(function ($val, $data, $opts) {
                 if (is_null($val) || $val <= '') {
                     return NULL;
                 }
                 $dateTime = new DateTime($val);
                 return $dateTime->format('Y-m-d H:i:s');
             }),
  

  // Field::inst('deets_activity.date_time_assigned')
  // ->validator(Validate::dateFormat('n/j/Y g:i a'))
  // ->getFormatter(Format::datetime('Y-m-d H:i:s', 'n/j/Y g:i a'))
  // ->setFormatter(Format::datetime('YYYY-MM-DD hh:mm:ss', 'Y-m-d H:i:s')),

  // Field::inst('detes_activity.date_time_inspection')
  // ->validator(Validate::dateFormat('n/j/Y g:i a'))
  // ->getFormatter(Format::dateSqlToFormat('Y-m-d H:i:s'))
  // ->setFormatter(Format::dateFormatToSql('YYYY-MM-DD hh:mm:ss')),

        Field::inst('deets_activity.date_time_inspection'),
            $val = "format('n/j/Y g:i a')";
             ->setFormatter(function ($val, $data, $opts) {
                 if (is_null($val) || $val <= '') {
                     return NULL;
                 }
                 $dateTime = new DateTime($val);
                 return $dateTime->format('Y-m-d H:i:s');
             }),
  

  // Field::inst( 'timestampdiff(DAY, now(), deets_activity.date_time_inspection)', 'ago' )

        Field::inst('deets_activity.current_status')
             ->options(options::inst()
                              ->table('list_items where list_id = 36 AND usergroup = 0 OR usergroup = ' . $usergroup . ' order by list_item')
                              ->value('list_item')
                              ->label('list_item')
             ),
  
        Field::inst('deets_inspection.inspection_scope')
             ->options(
                 options::inst()
                        ->table('list_items where list_id = 23 AND usergroup = 0 OR usergroup = ' . $usergroup . ' order by list_item')
                        ->value('list_item')
                        ->label('list_item')
             ),
        Field::inst('deets_inspection.assignment_notes'),
        Field::inst('deets_insured.claimtrackerid'),
        Field::inst('detes_insured.claimtrackerid'),
        Field::inst('detes_activity.date_time_inspection'),
        Field::inst('deets_insured.userid'),
        Field::inst('deets_insured.usergroup'),
        Field::inst('deets_insured.username')
    )
  //->leftJoin('deets_insured', 'deets_insured.claimtrackerid', '=', 'deets_insured.claimtrackerid')
  //->leftJoin('list_items', 'list_items.list_item', '=', 'deets_activity.current_status')
    ->leftJoin('deets_insured as detes_insured', 'detes_insured.claimtrackerid', '=', 'deets_insured.claimtrackerid')
    ->leftJoin('deets_insured as deeets_insured', 'deeets_insured.claimtrackerid', '=', 'deets_insured.claimtrackerid')
    ->leftJoin('deets_activity as detes_activity', 'detes_activity.claimtrackerid', '=', 'deets_insured.claimtrackerid')
    ->leftJoin('deets_activity', 'deets_activity.claimtrackerid', '=', 'deets_insured.claimtrackerid')
    ->leftJoin('deets_inspection', 'deets_inspection.claimtrackerid', '=', 'deets_insured.claimtrackerid')
    ->process($_POST)
    ->json();
  

  `

  Note that I have implemented your change on date_time_inspected and date_time_assigned, so those are finally saving at least. But I'd rather use a more familiar format for the visible table. I tried:

  ->validator(Validate::dateFormat('n/j/Y g:i a'))
  and
  ->getFormatter(Format::datetime('n/j/Y g:i a'))

  How do I control the visible format? With my code on this question, you can see I used the syntax presented in the tutorial I think.

  Thanks again for any help that anyone can provide.
• rf1234 Posts: 2,809Questions: 85Answers: 406

  May 2022 edited May 2022

  Oops I spoke too soon...your fix does work, but how do I show 'n/j/Y g:i a' format on the table and still save to sql format? I tried varieties with Validate:: and getFormatter but no love the way I was trying it.

  If you use Editor and don't change the table values outside of Editor the rendering in the data table is not relevant for saving values in sql format. On the other hand you need to make sure that Editor can deal with the format you return from the server.

  All you need is either
  a) a getFormatter to send the rendered format from the server, or
  b) a renderer on the client side to render the database format if you don't use a getFormatter on the server

  The kind of validator you can use depends on whether you choose a) or b).

  I personally prefer solution a) but many people would choose b).

  Since I do a) I use moment.js on the client side on the rare occasions that I do date comparisons client side. I serve dates in two user languages. So I use momentLocale to make sure I am refering to the right date format (in my case "de" which is DD.MM.YYYY or "en-gb" which is DD/MM/YYYY).

  If you search the forum and include "rf1234" in the search string you should find more on this.
• rf1234 Posts: 2,809Questions: 85Answers: 406

  May 2022 Answer ✓

  No rule without exception ...

  Since I always return DD.MM.YYYY or DD/MM/YYYY from the server I do special dateTime formatting client side as well. But that is only for rendering in the data table. Hence it has no effect on server setFormatting!

  Here I use moment.js to render the file update_time (which is a timestamp in the usual database format)´depending on the user language:

  if (lang == 'de') {         
      returnString += ( lineBreak + rowFile[ix].updaterName + ', am: ' + 
          moment(rowFile[ix].update_time.replace(/ /g, ' ')).format('DD.MM.YYYY, HH:mm [Uhr]: '));
  } else {
      returnString += ( lineBreak + rowFile[ix].updaterName + ', on: ' + 
          moment(rowFile[ix].update_time.replace(/ /g, ' ')).format('DD/MM/YYYY, @ hh:mm A: ') );
  }