Description of problem: I am creating a timeclock system for our employees. They are able to go back to their weekly clock in and out records and change the clock in and out times of the previous records.

There is error checking done to ensure their times do not conflict with other records of that one particular date. For example, if there is a record on a Monday from 7AM - 12:00PM and the next is 12:30PM-3:30PM, the second record could not have a clock in time changed to 11:30AM.

These times must be validated by the original date of the record. I have three columns for each record that are relevant - a date column for the date of the record, a timestamp clock in time, and a datetime clock out time. The issues I have found are as follows:

First rendition: I tried to use just time on the two clock columns, but when submitting a change, the date portion of the datetime column updated to today's date instead of staying the same date with the new time. This is an issue because it changed the table to having all modified records be a part of today's time clock records, leaving the error checking to return a consistent response of "Dates must be the same". When I tried to use the error checking stored procedure to verify the difference in dates, there was no consistency as to when the date field would also update to today's date. I tried to disable the date column to ensure this would not change, but that leaves the user with the clunkiness of having to enter the date of the clock in and out columns twice.

Second rendition: I changed the datetime fields to being both date and time, which is redundant because these records have to be in the same day but the day is rarely today's date. To try to mend this, I tried to use SET_EDIT on the serverside, but could not figure out how to default the values to the original date (and perhaps I missed something and could use the date column?).

Current code (as seen in the images):

Fields from Editor:

fields: [ 
      {
        label: "Date:",
        name: "clock_in.clock_date",
        type: "datetime",
        format: "MMMM D, YYYY",
        wireFormat: "YYYY-MM-DD"
      },
      {
        label: 'Job:',
        name:  'clock_in.job_no',
        type: "select"
      }, {
        label: 'In:',
        name:  'clock_in.clock_in_time',
        type: "datetime",
        format: "MMMM D, YYYY h:mm A",
        wireFormat: "YYYY-MM-DD HH:mm:ss"
      }, {
        label:  'Out:',
        name:   'clock_in.clock_out_time',
        type:   'datetime',
        format: "MMMM D, YYYY h:mm A",
        wireFormat: "YYYY-MM-DD HH:mm:ss"
      }
      ]

Error Checking via Editor - this runs a stored procedure to return a validation code so records do not overlap:

 editor.on("preSubmit", function(e, o, action) {
      if (submitFlag === false) {
        //if (action == 'edit') {
          //var rowid = data.DT_RowId;
          rowid = this.ids()[0];
          rowdate = this.field('clock_in.clock_date').val();
          newin = this.field('clock_in.clock_in_time').val();
          newout = this.field('clock_in.clock_out_time').val();
          newjob = this.field('clock_in.job_no').val();
          responseValidation(rowid, rowdate, newin, newout, newjob);
          return false;
        }
        submitFlag = false;
      });
      
      function responseValidation(rowid, rowdate, cin, cout, job) {
        $.ajax({
          url: "/timeclock/include/updateRecord.php",
          type: "POST",
          data: {
            rowid: rowid,
            rowdate: rowdate,
            newin: newin,
            newout: newout,
            newjob: newjob
          },
          success: function(response) {
            if (response == "1") {
              editor.field('clock_in.clock_out_time').error("Clock in time must be less than the clock out time.");
              submitFlag = false;
            } else if (response == "2") {
              editor.field('clock_in.clock_in_time').error("Clock in time conflicts with another record.");
              submitFlag = false;
            } else if (response == "3") {
              editor.field('clock_in.clock_out_time').error("Clock out time conflicts with another record.");
              submitFlag = false;
            } else if (response == "4") {
              editor.field('clock_in.clock_in_time').error("Clock in time conflicts with another record.");
              editor.field('clock_in.clock_out_time').error("Clock out time conflicts with another record.");
              submitFlag = false;
            } else if (response == "5") {
              editor.field('clock_in.clock_out_time').error("Time clock records must occur within the same day.");
              submitFlag = false;
            } else if (response == "6") {
              editor.field('clock_in.clock_in_time').error("Clock in time is required.");
              submitFlag = false;
            } else if (response == "7") {
              editor.field('clock_in.clock_out_time').error("Clock out time is required.");
              submitFlag = false;
            } else {
              submitFlag = true;
              editor.submit();
            }
          }
        });
        
      }

Table column definitions:

 columns: [
          { data: "clock_in.clock_date",
            render: function(data, type, row) {
              return moment(data).format("MMMM D, YYYY");
            } },
            { data: "001_PROJTRK.projtrk_bid_no", editField: "clock_in.job_no" },
            { data: "clock_in.clock_in_time", 
              render: function(data, type, row) {
                return moment(data).format("h:mm A");
              } },
              { data: "clock_in.clock_out_time", 
                render: function(data, type, row) {
                  return moment(data).format("h:mm A");
                } },
                { data: "clock_in.time_worked" }
              ],

Controller:

->field( 
    Field::inst( 'clock_in.clock_date' )
    ->validator( Validate::dateFormat(
        'Y-m-d',
        ValidateOptions::inst()
            ->allowEmpty( false )
    ) )
    ->getFormatter( Format::datetime( 'Y-m-d', 'Y-m-d' ) )
    ->setFormatter( Format::datetime( 'Y-m-d', 'Y-m-d' ) ),
    Field::inst( 'clock_in.clock_in_time' )
    ->validator( Validate::dateFormat(
        'Y-m-d H:i:s',
        ValidateOptions::inst()
            ->allowEmpty( false )
    ) )
    ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
    ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
    Field::inst( 'clock_in.clock_out_time' )
    ->validator( Validate::dateFormat(
        'Y-m-d H:i:s',
        ValidateOptions::inst()
            ->allowEmpty( false )
    ) )
    ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
    ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),

Related question to this table: Is there a way to use RowGrouping without actually showing the column? For instance, instead of having a column in the table for the date, simply group by the date and order by the time columns?