How to compare datetime column cell by cell which one is latest date and perform some math

Shivani Vyas

Hello, I have table which has column location and details of assets by location. I want to iterate location wise for each asset and check some datetime column - that which asset has latest/biggest date, and add 14 days in that biggest date.

For e.g. location Avenue has two asset > A1 , A2.. now I want to check from A1 and A2 which asset has latest date.. take that date and add 14 days in to it and store in another column.

so I tried render function in which I am not able to iterate using location column.

Please check below example - This is how I compare data.. column wise. here I am comparing two different columns using if else. But now I want to comapre data from same column (so row wise).

Please help if there are any function in datatables that I can use.

 { data: "TIDForecast",  

 render : function(data,type,row){

                 if((row.SIForecast == null) && (row.SIActual == null)){

                     return null;
                 }

                 var SIF = moment(row.SIForecast).add(98, 'days'); // Start Install Forecast
                 var SIA = moment(row.SIActual).add(98, 'days'); // Start Install Actual

                 // IF ACTUAL IS EMPTY USE FORECAST.
                 // IF ACTUAL IS NOT EMPTY USE ACTUAL.


                    if(row.SIActual == null)
                    {
                        return SIF.format('DD MMM YYYY'); // Add 98 Days into SIForecast Date
                    }


                    else( row.SIActual != null)
                    {
                        return SIA.format('DD MMM YYYY'); // Add 98 Days into SIActual Date
                    }


                }
}

kthorngren

I'm not clear on when you want to do these calculations and how you want to use them. Here are some options to look at:

1. You can use rows().every() to iterate all the the rows.
2. You can use column().data() to get specific column data then iterate the array of data

You can use drawCallback if you want to perform the calculations on each table draw.

Kevin

Shivani Vyas

@kthorngren Here is picture of my table to get an idea what exactly want to do.

Red mark - Location
Blue mark - cells that I need to compare - which date is latest
Black mark - after finding latest date + add 14 days and store in Black marked column

I need to do this for each location..

Location Avenue has Two Drawing ID - ELV01, ELV02
I need to check which Drawing ID hase latest TSSA Inspection - Forecast date.
for e.g. for Avenue its 16 May 2022..
Add 14 days into 16 May 2022 which is " 30 May 2022".
Now store 30 May 2022 in SAT - forecast column for ELEV01.
and Add +1 in 30 May 2022 which I need to store for ELV02.

I need to perform same operation for each Location.

Please share any example as I dont know how to choose specific column's data and compare it .

colin

This example from this thread might help - it's showing how you can manipulate other rows in the data based on rows always processed. It's not the same issue, but it demonstrates what you need to do with your data,

Colin

Shivani Vyas

@colin the example you gave showing sum of data for project wise.. 3rd table is what some how related to my issue but I am not sure how do I compare .. which date is latest ? I mean in the example they used sum[data[2]]..sum function .. that would be great if you can share small example related to what I want to do.

kthorngren

One option might be to use an object to store the latest date for each location. Use rows().every() to loop through all the rows and build the object where the Location is the object key. For each row check to see if the Location date is greater than the one stored in the object.

Then use -api rows().every()` to loop through all the rows to perform the updates you want.

If you want an example then start by building a simple example with a sample of your data. At a minimum try to use rows().every() to build the Locations object. We can then help you piece it together.

Kevin

Shivani Vyas

@kthorngren I am not sure I can do that or not because the column data - which I have to compare is result of rendered data.. and its not store back in database. I mean How to store client side rendered data in to database ?

so the "TSSA Inspection - Forecast date" - is the result of client side rendered.. see below code for TSSA Inspection - Forecast date column. currently database have empty column. Also please suggest if you have any idea if I can achieve this some other way. Thank you.

      { data: "SIForecast"},

      { data: "SIActual"},


      { data: "PCPlanned"},

      { data: "PCForecast"},

      { data: "PCActual"},

      { data: "TIDPlanned"},

      { data: "TIDForecast", // - TSSA Forecast Calculation

         render : function(data,type,row){

             if((row.SIForecast == null) && (row.SIActual == null)){

                 return null;
             }

             var SIF = moment(row.SIForecast).add(98, 'days'); // Start Install Forecast
             var SIA = moment(row.SIActual).add(98, 'days'); // Start Install Actual

             // IF ACTUAL IS EMPTY USE FORECAST.
             // IF ACTUAL IS NOT EMPTY USE ACTUAL.


                if(row.SIActual == null)
                {
                    return SIF.format('DD MMM YYYY'); // Add 98 Days into SIForecast Date
                }


                else( row.SIActual != null)
                {
                    return SIA.format('DD MMM YYYY'); // Add 98 Days into SIActual Date
                }


            }

      },

kthorngren

I mean How to store client side rendered data in to database ?

You would need to send an ajax request to the server with the data you want stored. This seems overly complex. My suggestion is to use a query on the server to update that column in the DB.

Are you trying to do all the calculations and data changes above then store them in the DB? I would look at creating server queries to perform the calculations and update the DB.

Kevin

Shivani Vyas

@kthorngren Yes I mean I am not sure I am just guessing if its possible to stored client side rendered value in to database or may be something like when editing form and saving data... at that time If I can call the trigger for storing TSSA Forecast value in to database using update query.. I am not sure in datatables editor I can use trigger or not. If I am able to store TSSA Forecast dates in DB ..after that may be I can use rows().every() .

Shivani Vyas

I would look at creating server queries to perform the calculations and update the DB.

as per above sentence you mean to say .. to do all the calculation on server database ?

kthorngren

to do all the calculation on server database ?

Depends on your requirements. If you want to perform table calculations and save them in the DB it would be better and more efficient to use server side queries. However if you just want to display calculated data in the client and there is no need to save the data then use columns.render or the other methods we discussed above.

Kevin

kthorngren

This new thread might be of interest to you. It has a simple example of summing a total based on a particular group (Office in this case). First it loops through to sum each group then it loops through to update the Sum column with the totals. You can do something similar but keep track of the latest date. Then loop through a second time to make the updates.

Kevin

Shivani Vyas

@kthorngren so same as thread I tried to add data in HTML manually for example purpose.. but I am still looking for how can I find latest date and how I can add 14 days for first drawing id of each location , and add + 1 for other drawing id in same location. Please help me how I can achieve this.

E.g. Location Eglinton have ELV01 to ELV07 .. so check from ELV01 to ELV07 which drawing ID has Biggest TSSA Forecast date. After finding Biggest date add 14 days and store in ELV01's - SAT Forecast column.
ELV02's - SAT Forecast = Biggest Date + 15 days
ELV03's - SAT Forecast = Biggest Date + 16 Days
and so on..

Also I added and commented out my actual code for TSSA Forecast column which explains how I did calculation for that column in render function.

here is my test case.

http://live.datatables.net/saqoxaqu/1/edit

kthorngren

The test case isn't running. You have a syntax error. Please make sure the test case is running.

You have this in the first loop:

        var Tssa = api.cell( rowIdx, 4).data();

        var latest =  Math.Max(row.TIDForecast);
            result[data] = moment(latest).add(14, 'days');

Not sure what you are trying to do but my suggestion is to see if the date (Tssa) is greater than the one stored in result[data]. If it is then store the new value. That is all in that loop. Once the loop is complete all that should be in result is the latest data for each Location.

Then in the second loop perform your calculations for each row.

Kevin

Shivani Vyas

@kthorngren I am sorry I am not much good in JS. I tried what you suggested. Now there is no error but its not displaying any result in SAT Forecast column. I think I am doing wrong for adding 14 days in latest date. I am trying using moment() same like I did for TSSA Forecast date. But I am not sure what else I can do. Please suggest any other way.

http://live.datatables.net/saqoxaqu/1/edit

kthorngren

Your test case is generating this error:

Uncaught TypeError: Cannot set properties of undefined (setting '0')

There is something wrong with your footer so I removed it. Now you have this error:

Uncaught TypeError: Cannot set properties of undefined (setting '_DT_CellIndex')

You had an extra tr tag in the HTML.

I also needed to add the moment.js library. Made a couple adjustments to the code:
http://live.datatables.net/saqoxaqu/1/edit

I removed the use of latest and stored the latest date in result.

Kevin

Shivani Vyas

@kthorngren sorry I updated HTML but I couldn't see the changes you have made.

live.datatables.net/saqoxaqu/3/edit

kthorngren

Sorry wrong link. Try this one:
http://live.datatables.net/baruliji/1/edit

Kevin

Shivani Vyas

@kthorngren wow..thank you so much. Now we are adding 14 days location wise.. but.. How do I achieve as below example.

E.g. Location Eglinton have ELV01 to ELV07 .. so check from ELV01 to ELV07 which drawing ID has Biggest TSSA Forecast date. After finding Biggest date add 14 days and store in ELV01's - SAT Forecast column.
ELV02's - SAT Forecast = Biggest Date + 15 days
ELV03's - SAT Forecast = Biggest Date + 16 Days
and so on..

currently ELV01 to ELV07 for Eglinton has same SAT Forecast value - Oct 04 2022
instead of that.. I need to do as below. So where should I loop and add +1

ELV01 - SAT Forecast = Oct 04 2022
ELV02 - SAT Forecast = Oct 05 2022
ELV03 - SAT Forecast = Oct 06 2022
ELV04 - SAT Forecast = Oct 07 2022
ELV05 - SAT Forecast = Oct 08 2022
ELV06 - SAT Forecast = Oct 09 2022
ELV07 - SAT Forecast = Oct 10 2022

Shivani Vyas

@kthorngren I am also getting weird dates when I copy drawCallback function in my actual php page.

please check picture and code.

var table = $('#assyntCx_Table').DataTable( {
    dom: "lBfrtip",



    //AJAX
    //serverSide: true,
    processing: true,
    ajax: {
      url: "../ajax/at/elevators.php",
      type: "POST",
      deferRender: true,
    },

    //TABLE WINDOW
    scrollY:        "65vh",
    scrollX:        true,
    scrollCollapse: false,
    paging:         true,
    fixedColumns:   {
      left: 3
    },

    orderFixed: [0, 'asc'],
    drawCallback: function(){

        var api = this.api();

        var result = {};

         // Loop all cells in Location column
        api.cells(null, 0).every( function (rowIdx){

            var data = this.data();

            if( ! result.hasOwnProperty( data)){

                result[data] = 0;
            }


         // Get the TSSA Forecast date from the same row

            var Tssa = api.cell( rowIdx, 36).data();

        // Keep track of latest Tssa date

          if ( result[data] < moment(Tssa) ) {
            result[data] = moment(Tssa);
          }

     });

     console.log(result);

    // Loop through all the rows and store the lates date, per Locaiton, +14 days
    api.cells(null, 0).every( function (rowIdx){

        var data = this.data();

        var ans = moment( result[data] ).add(14, 'days');

        api.cell( rowIdx, 42).data(moment(ans).format('DD MMM YYYY'));


     });


 },

    columns: [

      { data: "Location" },

      { data: "DrawingID"},

      { data: "Status"},

      //{ data: "E.assetid" },

      { data: "higherlevel"},

      { data: "lowerlevel"},



      {data: "MSDPlanned",
        class: "toggle"

      },


      { data: "MSDForecast",
        class: "toggle"
      },

      { data: "MSDActual",
        class: "toggle"
      },

      { data: "OCCPlanned"},

      { data: "OCCForecast"},

      { data: "OCCActual"},

      { data: "ETDPlanned"},

      { data: "ETDForecast" },

      { data: "ETDActual"},

      { data: "FTDPlanned"},

      { data: "FTDForecast"},

      { data: "FTDActual"},

      { data: "SFDAPlanned"},

      { data: "SFDAForecast"},

      { data: "SFDAActual"},

      { data: "FFIPlanned"},

      { data: "FFIForecast"},

      { data: "FFIActual"},

      { data: "AACPlanned"},

      { data: "AACForecast"},

      { data: "AACActual"}, 

      { data: "AASPlanned"},

      { data: "AASForecast"},

      { data: "AASActual"},

      { data: "SIPlanned"},

      { data: "SIForecast"},

      { data: "SIActual"},


      { data: "PCPlanned"},

      { data: "PCForecast"},

      { data: "PCActual"},

      { data: "TIDPlanned"},

      { data: "TIDForecast", // - TSSA Forecast Calculation

         render : function(data,type,row){

             if((row.SIForecast == null) && (row.SIActual == null)){

                 return null;
             }

             var SIF = moment(row.SIForecast).add(98, 'days'); // Start Install Forecast
             var SIA = moment(row.SIActual).add(98, 'days'); // Start Install Actual

             // IF ACTUAL IS EMPTY USE FORECAST.
             // IF ACTUAL IS NOT EMPTY USE ACTUAL.


                if(row.SIActual == null)
                {
                    return SIF.format('DD MMM YYYY'); // Add 98 Days into SIForecast Date
                }


                else( row.SIActual != null)
                {
                    return SIA.format('DD MMM YYYY'); // Add 98 Days into SIActual Date
                }


            }

      },

      { data: "TIDActual"},

      { data: "CIDPlanned"},

      { data: "CIDForecast"},

      { data: "CIDActual" },


      { data: "SATPlanned"},

      { data: "SATForecast"},

      { data: "SATActual"},


      { data: "U1.username"},

      { data: "UL"},


],

kthorngren

Now we are adding 14 days location wise.. but.. How do I achieve as below example.

Is this what you want?
http://live.datatables.net/baruliji/2/edit

Basically it keeps track of the Location. When it changes it resets the varaible used to add the number of days.

This will require both the Location column and Drawing ID columns to be sorted. I updated the orderFixed option for this.

I am also getting weird dates when I copy drawCallback function in my actual php page.

Looks like the moment function isn't parsing your date format correctly. You will need to update the test case to show the issue or post a link to your page so we can take a look.

Kevin

Shivani Vyas

@kthorngren here I make copy of code in word file and make url. as I am nto able to share actual page url. let me know if you are able to open it.

Also see below pictures of output.

https://seneca-my.sharepoint.com/:w:/g/personal/kvyas6_myseneca_ca/EdQ7cM3WFd9NrD1RcOqbf1UB4a6gxSJuw5kNtzQT-sVjGA?e=k6t5Kh

Shivani Vyas

@kthorngren Also I forgot that we have some columns with null location and drawing ID.. is that an issue if location and drawingid is null ?

Shivani Vyas

@kthorngren Also i forgot to inform you .. yes this is what I want...->(http://live.datatables.net/baruliji/2/edit)

and its working perfectly.. just showing 1970's dates.. I don't know why. Because I already used moment.js before in same file.. and its working fine.

kthorngren

The problem is we will need to work with your data. Screenshots and code listings aren't going to help. You can get a sample of your data using the browser's network inspector by looking at the Response tab of the ajax request. For the test case you can load the sample using data instead of ajax. Like this example.

Or you can do some debugging of that code to see what its doing. I suspect that whatever you are passing into moment.js is not a valid date or a format it doesn't understand.

Kevin

Shivani Vyas

@kthorngren when I apply format in datatables.net example.. is working fine.. but when I try to run in my php page.. its giving me correct format but wrong date from 1970's. Can you please share which libraries you added for moment.js in below example ?

live.datatables.net/qofahumi/3/edit

kthorngren

The HTML tab has all the libraries loaded, for example:

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>

The problem is likely due to a difference in the actual data. In the test case you supplied the actual date format. Your JSON data may have something different. You will need to debug the drawCallback function to see where the difference is. Or provide a test case with an example of your JSON data so we can look.

Kevin

Shivani Vyas

@kthorngren The data I am getting from Database is 'Y-m-d' and I am updating it to the 'd M Y' and displaying it. Now the column TSSA forecast's date is result of another two column's calculation(these two column's data are coming from database) and I did calculation using render function and I format it when displaying it using momet().format()

This is controller file for Elevators. In which I am fetching all data from database. But TIDForecast is empty in database.. and I used render function to perform calculation on two another column - SIForecast, SIActual (these both are coming from database and has format 'Y-m-d' which I am updating in below code using 'd M Y' format.

<?php

if(!isset($_SESSION)) {
    session_start(); 
  }    

  if(isset($_SESSION['userID'])) {
    $userID = $_SESSION['userID'];
    //$role = $_SESSION['role'];
  } else {
    $userID = null;
    //$role = null;
  }
  $del = $_SESSION['del'];

include("../lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

$update = 'd M Y';

Editor::inst( $db, 'Elevators_Tracker E', 'E.id' )
->field(    


    Field::inst( 'L.LocationName', 'Location' )
      ->options( Options::inst()
        ->table('loc')
        ->value('LocationName')
        ->label('LocationName')

      )
      ->validator( Validate::dbValues() )
      ->validator( Validate::notEmpty( ValidateOptions::inst()
        ->message( 'What location are we using?' )
      ) ),


      Field::inst( 'A.dwgTag', 'DrawingID'),



    Field::inst( 'E.assetid'),




    //to display status dropdown
    Field::inst( 'E.elevatorStatus')
        ->options( Options::inst()
        ->table('assetstatus')
        ->value('id')
        ->label('assetStatus')
        ->order('id')
        )
        ->validator( Validate::dbValues() )
        ->validator( Validate::notEmpty( ValidateOptions::inst()
                  ->message( 'What status are we allocating?' )
        ) ),

    //to display status in grid  
    Field::inst( 'S.assetStatus', 'Status'),

    Field::inst( 'E.higher', 'Higher' )
      ->options( Options::inst()
        ->table('levels')
        ->value('levelID')
        ->label('levels')
        ->order('levels')
      )
      ->validator( Validate::dbValues() )
      ->setFormatter( Format::ifEmpty( null )),
      Field::inst( 'L1.levels', 'higherlevel' ),


      Field::inst( 'E.lower', 'Lower' )
      ->options( Options::inst()
        ->table('levels')
        ->value('levelID')
        ->label('levels')
        ->order('levels')
      )
      ->validator( Validate::dbValues() )
      ->setFormatter( Format::ifEmpty( null )),
      Field::inst( 'L2.levels', 'lowerlevel' ),





    Field::inst( 'E.SI_Planned', 'SIPlanned' )
    ->getFormatter( Format::dateSqlToFormat( $update ) )
    ->setFormatter( function ( $val ) {
        if ($val == '') {
            return null;
        } else {
            return date( 'Y-m-d', strtotime( $val ) );
        }
    } ),

    Field::inst( 'E.SI_Forecast', 'SIForecast' )
    ->getFormatter( Format::dateSqlToFormat( $update ) )
    ->setFormatter( function ( $val ) {
        if ($val == '') {
            return null;
        } else {
            return date( 'Y-m-d', strtotime( $val ) );
        }
    } ),

    Field::inst( 'E.SI_Actual', 'SIActual' )
    ->getFormatter( Format::dateSqlToFormat( $update ) )
    ->setFormatter( function ( $val ) {
        if ($val == '') {
            return null;
        } else {
            return date( 'Y-m-d', strtotime( $val ) );
        }
    } ),





    Field::inst( 'E.TID_Forecast', 'TIDForecast' )
    ->getFormatter( Format::dateSqlToFormat( $update ) )
    ->setFormatter( function ( $val ) {
        if ($val == '') {
            return null;
        } else {
            return date( 'Y-m-d', strtotime( $val ) );
        }
    } ),




    Field::inst( 'E.SAT_Forecast', 'SATForecast' )
    ->getFormatter( Format::dateSqlToFormat( $update ) )
    ->setFormatter( function ( $val ) {
        if ($val == '') {
            return null;
        } else {
            return date( 'Y-m-d', strtotime( $val ) );
        }
    } ),



    Field::inst( 'E.Updated_By')
      ->set(true)
      ->setValue( $userID ),
    Field::inst( 'U1.username' ),

    Field::inst( 'E.Updated_Last', 'UL' )
      ->set(true)
      ->setValue( date("Y-m-d H:i:s") ),



    //to store in db
    Field::inst( 'E.createdBy' )
    ->set(Field::SET_CREATE)
    ->setValue( $userID ),



)


->debug(true)
->process( $_POST )
->json();
?>

Shivani Vyas

This is how I render TIDForecast (TSSA forecast) column and format it.

 { data: "SIForecast"},

      { data: "SIActual"},


      { data: "PCPlanned"},

      { data: "PCForecast"},

      { data: "PCActual"},

      { data: "TIDPlanned"},

      { data: "TIDForecast", // - TSSA Forecast Calculation

         render : function(data,type,row){

             if((row.SIForecast == null) && (row.SIActual == null)){

                 return null;
             }

             var SIF = moment(row.SIForecast).add(98, 'days'); // Start Install Forecast
             var SIA = moment(row.SIActual).add(98, 'days'); // Start Install Actual

             // IF ACTUAL IS EMPTY USE FORECAST.
             // IF ACTUAL IS NOT EMPTY USE ACTUAL.


                if(row.SIActual == null)
                {
                    return SIF.format('DD MMM YYYY'); // Add 98 Days into SIForecast Date
                }


                else( row.SIActual != null)
                {
                    return SIA.format('DD MMM YYYY'); // Add 98 Days into SIActual Date
                }


            }

      },

kthorngren

To get rendered data use cell().render() instead of cell().data().

Kevin

Shivani Vyas

@kthorngren Thank you so much Kevin. I really appreciate your help. Issue is resolved. Now I just have one question.. when I go to the page in starting it displays all button and complex header first in some weird manner and than actual table appears. Here is a link of my page.. I made it public so you can view it. When you will open the link it browser in first few second you will be able to see it what I am asking.

https://test.assettrack.cx/elevators/ele1.php