skip to Main Content

I want to convert this json into html table with sorted date wise. Basically my JSON data comes through looking like this:

Here I want to recurrenceList dates

  var frequencyList=  [
      {
        "project": "abc",
        "recurrenceList": [
          "2021-09-16",
          "2023-09-16",
          "2025-09-16"
        ]
      },
      {
        "project": "xyz",
        "recurrenceList": [
          "2021-08-23",
          "2025-08-23"
        ]
      },
      {
        "project": "mno",
        "recurrenceList": [
          "2021-09-11",
          "2022-05-11",
          "2023-01-11",
          "2023-09-11",
          "2024-05-11",
          "2025-01-11",
          "2025-09-11"
        ]
      }
    ]

I am getting output like this
enter image description here
expected output in html table with two columns given below (based on above JSON)

project name   reminder date
xyz              2021-08-23
mno              2021-09-11
abc              2021-09-16
mno              2022-05-11

etc…

Below code for Ajax to get the JSON DATA frequencyList

/* the getPaymentPlan function is standalone, it should not be within a loop or within any other callback functions! */
const getPaymentPlan = ({
  dateFrom,
  dateTo,
  recurrenceDate,
  daysBefore,
  period,
  skip,
  title
}) => {
  //start from either the recurrence start date, or the start date in the form - whichever is later
  let startDate = (recurrenceDate.getTime() > dateFrom.getTime() ? recurrenceDate : dateFrom);
  //reminders go out several days before the actual recurrence start date
  startDate = startDate.subtractDays(daysBefore);

  let recurObj = {
    "project": title,
    "recurrenceList": []
  }

  while (startDate.getTime() <= dateTo.getTime()) {
    recurObj.recurrenceList.push(startDate.toISOString().split('T')[0]);

    switch (period) {
      case 'Monthly':
        startDate = startDate.addMonths(parseInt(skip));
        break;
      case 'Yearly':
        startDate.setFullYear(startDate.getFullYear() + parseInt(skip));
        break;
      default:
        recurObj.recurrenceList.push("wrong period type is given")
        break;
    }
  }

  return recurObj;
}

/* below are some functions to correctly add / subtract days and months from a Date, accounting for leap years, differing month lengths, etc */
Date.prototype.addDays = function(days) {
  var date = new Date(this.valueOf());
  date.setDate(date.getDate() + days);
  return date;
}

Date.prototype.subtractDays = function(days) {
  var date = new Date(this.valueOf());
  date.setDate(date.getDate() - days);
  return date;
}

Date.prototype.addMonths = function(months) {
  var date = new Date(this.valueOf());
  var d = date.getDate();
  date.setMonth(date.getMonth() + months);
  if (date.getDate() != d) {
    date.setDate(0);
  }
  return date;
}
    $('#find_recurrence').click(function(event) {
        
         $('.tableinvoicelist_all').find('tbody').remove();
         
    var getID_comp = $('#getID_comp').val();
  var fromdate_recu_viewedit = $('#fromdate_recu_view').val(); //2025-01-01
  var fromdate_recu_view = fromdate_recu_viewedit.split("-").reverse().join("-");
  var todate_recu_viewedit = $('#todate_recu_view').val(); //2026-01-30
  var todate_recu_view = todate_recu_viewedit.split("-").reverse().join("-");
  
  //hard-code data instead of AJAX, for this demo:
  
   $.ajax({
            url: base_url + "index.php/welcome/list_all_projects_reminder/",
            
            type: "POST",
            data: {
                "company_id": getID_comp
            },
            success: function(data) {
                var new_datajson = JSON.parse(data);
  let new_data = new_datajson;

  let inputList = [];

  for (var i = 0; i < new_data.projectremindshow.length; i++) {
    var proj = new_data.projectremindshow[i];

    //add a new entry to inputList for each entry returned from the AJAX call
    inputList.push({
      dateFrom: new Date(fromdate_recu_view),
      dateTo: new Date(todate_recu_view),
      daysBefore: proj.reminder_set_days,
      recurrenceDate: new Date(proj.start_date),
      period: proj.period_type,
      skip: proj.recur_every,
      title: proj.project_title
    });
  }
  
   const frequencyList = inputList.map((el, index) => {
    return getPaymentPlan(el)
  });
  
  console.log(frequencyList);
   $.each(frequencyList, function(index, jsonObject){     
if(Object.keys(jsonObject).length > 0){
  var tableRow = '<tr>';
  $.each(Object.keys(jsonObject), function(i, key){
     tableRow += '<td>' + jsonObject[key] + '</td>';
  });
  tableRow += "</tr>";
 
  $("#tablereminder").last().append(tableRow);
}
});
   
            }
   })
 
});

How to change the list in one list instead of three list (recurrenceList)?
Sort with date wise

2

Answers


  1. You can convert the dates to timestamps and sort according to those timestamps, like:

    const sortedDates = recurrenceList.map(date=>{
       const dateObj = {
          initial: date,
          timeStamp: new Date(
             Number(date.slice(0,4)),
             Number(date.slice(5,7)) - 1,
             Number(date.slice(8))
          ).getTime()
       }
       return dateObj
    })
    .sort((a,b) => a.timeStamp - b.timeStamp)
    .map(obj=> obj.initial);
    
    Login or Signup to reply.
  2. Here is an alternative version of the getPaymentPlan function which will output the data in a format which may be easier to sort, and also to output later according to your desired HTML format:

    const getPaymentPlan = ({
      dateFrom,
      dateTo,
      recurrenceDate,
      daysBefore,
      period,
      skip,
      title
    }) => {
      //start from either the recurrence start date, or the start date in the form - whichever is later
      let startDate = (recurrenceDate.getTime() > dateFrom.getTime() ? recurrenceDate : dateFrom);
      //reminders go out several days before the actual recurrence start date
      startDate = startDate.subtractDays(daysBefore);
    
      let recurrenceList = [];
    
      while (startDate.getTime() <= dateTo.getTime()) {
        recurrenceList.push({ "project": title, "reminderDate": startDate.toISOString().split('T')[0] });
    
        switch (period) {
          case 'Monthly':
            startDate = startDate.addMonths(parseInt(skip));
            break;
          case 'Yearly':
            startDate.setFullYear(startDate.getFullYear() + parseInt(skip));
            break;
          default:
            recurrenceList.push({ "project": "wrong period type is given", "reminderDate": null })
            break;
        }
      }
    
      return recurrenceList;
    }
    

    You can then sort it using a comparison function similar to the examples available in earlier questions such as this one:

    function compare( a, b ) {
      if ( a.reminderDate < b.reminderDate ){
        return -1;
      }
      if ( a.reminderDate > b.reminderDate){
        return 1;
      }
      return 0;
    }
    
    recurrenceList.sort( compare );
    

    Once you’ve done that you can just loop through the list to generate the HTML table:

    $.each(frequencyList, function(index, jsonObject){     
      var tableRow = '<tr>';
      $.each(Object.keys(jsonObject), function(i, key){
         tableRow += '<td>' + jsonObject[key] + '</td>';
      });
      tableRow += "</tr>";
     
      $("#tablereminder").last().append(tableRow);
    });
    

    Putting it all together in a demo, you can get the output you were looking for:

    $('#find_recurrence').click(function(event) {
      var fromdate_recu_view = $('#fromdate_recu_view').val(); //2025-01-01
      var todate_recu_view = $('#todate_recu_view').val(); //2026-01-30
    
      //hard-code data instead of AJAX, for this demo:
      let new_data = {
        "projectremindshow": [{
            "project_ID": "8",
            "project_title": "abc",
            "period_type": "Yearly",
            "recurrence_date": "2021-10-28",
            "reminder_set_days": "12",
            "recur_every": "2",
            "start_date": "2021-09-28"
          },
          {
            "project_ID": "10",
            "project_title": "xyz",
            "period_type": "Yearly",
            "recurrence_date": "2021-10-05",
            "reminder_set_days": "13",
            "recur_every": "4",
            "start_date": "2021-09-05"
          },
          {
            "project_ID": "11",
            "project_title": "mno",
            "period_type": "Monthly",
            "recurrence_date": "2021-10-01",
            "reminder_set_days": "10",
            "recur_every": "8",
            "start_date": "2021-09-21"
          }
        ]
      };
    
      let inputList = [];
    
      for (var i = 0; i < new_data.projectremindshow.length; i++) {
        var proj = new_data.projectremindshow[i];
    
        //add a new entry to inputList for each entry returned from the AJAX call
        inputList.push({
          dateFrom: new Date(fromdate_recu_view),
          dateTo: new Date(todate_recu_view),
          daysBefore: proj.reminder_set_days,
          recurrenceDate: new Date(proj.start_date),
          period: proj.period_type,
          skip: proj.recur_every,
          title: proj.project_title
        });
      }
    
      let frequencyList = [];
      for (let i = 0; i < inputList.length; i++)
      {
        let plan = getPaymentPlan(inputList[i]);
        Array.prototype.push.apply(frequencyList,plan);
      }
      console.log(frequencyList);
      frequencyList.sort(compare);
    
      $.each(frequencyList, function(index, jsonObject) {
        var tableRow = '<tr>';
        $.each(Object.keys(jsonObject), function(i, key) {
          tableRow += '<td>' + jsonObject[key] + '</td>';
        });
        tableRow += "</tr>";
    
        $("#tablereminder").last().append(tableRow);
      });
    
    
    });
    
    const getPaymentPlan = ({
      dateFrom,
      dateTo,
      recurrenceDate,
      daysBefore,
      period,
      skip,
      title
    }) => {
      //start from either the recurrence start date, or the start date in the form - whichever is later
      let startDate = (recurrenceDate.getTime() > dateFrom.getTime() ? recurrenceDate : dateFrom);
      //reminders go out several days before the actual recurrence start date
      startDate = startDate.subtractDays(daysBefore);
    
      let recurrenceList = [];
    
      while (startDate.getTime() <= dateTo.getTime()) {
        recurrenceList.push({
          "project": title,
          "reminderDate": startDate.toISOString().split('T')[0]
        });
    
        switch (period) {
          case 'Monthly':
            startDate = startDate.addMonths(parseInt(skip));
            break;
          case 'Yearly':
            startDate.setFullYear(startDate.getFullYear() + parseInt(skip));
            break;
          default:
            recurrenceList.push({
              "project": "wrong period type is given",
              "reminderDate": null
            })
            break;
        }
      }
    
      return recurrenceList;
    }
    
    function compare(a, b) {
      if (a.reminderDate < b.reminderDate) {
        return -1;
      }
      if (a.reminderDate > b.reminderDate) {
        return 1;
      }
      return 0;
    }
    
    Date.prototype.addDays = function(days) {
      var date = new Date(this.valueOf());
      date.setDate(date.getDate() + days);
      return date;
    }
    
    Date.prototype.subtractDays = function(days) {
      var date = new Date(this.valueOf());
      date.setDate(date.getDate() - days);
      return date;
    }
    
    Date.prototype.addMonths = function(months) {
      var date = new Date(this.valueOf());
      var d = date.getDate();
      date.setMonth(date.getMonth() + months);
      if (date.getDate() != d) {
        date.setDate(0);
      }
      return date;
    }
    #tablereminder
    {
      border: solid 1px black;
      padding: 5px;
      border-collapse: collapse;
    }
    
    #tablereminder td
    {
      border: solid 1px black;
      padding: 5px;
    }
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    From date: <input type="date" id="fromdate_recu_view" /><br/> To date: <input type="date" id="todate_recu_view"><br/>
    <button id="find_recurrence">Find Recurrence</button>
    
    <br/><br/>
    <table id="tablereminder"></table>

    (You may need to view the demo in full page mode to see the table as well as the debugging output from the console.)

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search