5

I am facing issue while creating HTML table with JSON data, as I am new to this so not correctly able to write the logic.

I have a json data from which i have to create a dynamic html table. The design of table is little complex that's why I am not able to populate the HTML table with the correct data.

From my JSON I am trying to create:

table like this

but not able to.

I have done something like this

var data = [{
    "billdate": "2018-08-01",
    "outlet": "S0001",
    "amount": 291589,
    "cash": 288276,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 3313,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-01",
    "outlet": "S0002",
    "amount": 58337,
    "cash": 56727,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 1610,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-01",
    "outlet": "S0009",
    "amount": 65970,
    "cash": 65970,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 0,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-02",
    "outlet": "S0001",
    "amount": 296125,
    "cash": 290480,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 5645,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-02",
    "outlet": "S0002",
    "amount": 56545,
    "cash": 55034,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 1511,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-02",
    "outlet": "S0009",
    "amount": 72213,
    "cash": 72213,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 0,
    "kb": 0,
    "bigbasket": 0
  }
]


let formatData = function(data) {
  let billdates = [];
  let outlets = [];
  data.forEach(element => {
    if (billdates.indexOf(element.billdate) == -1) {
      billdates.push(element.billdate);
    }
    if (outlets.indexOf(element.outlet) == -1) {
      outlets.push(element.outlet);
    }
  });
  return {
    data: data,
    billdates: billdates,
    outlets: outlets,

  };
};

let renderTable = function(data) {
  billdates = data.billdates;
  outlets = data.outlets;
  data = data.data;
  let tbl = document.getElementById("tblOlSalesSummary");
  let table = document.createElement("table");
  let thead = document.createElement("thead");
  let headerRow = document.createElement("tr");
  let th = document.createElement("th");
  th.innerHTML = "BillDate";
  th.classList.add("text-center");
  headerRow.appendChild(th);
  let grandTotal = 0;
  let outletWiseTotal = {};
  th = document.createElement("th");
  th.innerHTML = "Sales Type";
  th.classList.add("text-center");
  headerRow.appendChild(th);
  outlets.forEach(element => {
    th = document.createElement("th");
    th.innerHTML = element;
    th.classList.add("text-center");

    headerRow.appendChild(th);
    outletWiseTotal[element] = 0;
    data.forEach(el => {
      if (el.outlet == element) {
        outletWiseTotal[element] += parseInt(el.amount);
      }
    });
    grandTotal += outletWiseTotal[element];
  });

  thead.appendChild(headerRow);
  headerRow = document.createElement("tr");
  th = document.createElement("th");
  th.innerHTML = "Total";
  th.classList.add("text-center");
  headerRow.appendChild(th);

  outlets.forEach(element => {
    th = document.createElement("th");
    th.innerHTML = outletWiseTotal[element].toLocaleString('en-in');
    th.classList.add("text-right");

    headerRow.appendChild(th);
  });
  th = document.createElement("th");
  th.innerHTML = grandTotal.toLocaleString('en-in');
  th.classList.add("text-right");

  /* console.log(grandTotal); */
  // headerRow.appendChild(th);
  headerRow.insertBefore(th, headerRow.children[1]);
  thead.appendChild(headerRow);
  table.appendChild(thead);

  let tbody = document.createElement("tbody");
  billdates.forEach(element => {
    let row = document.createElement("tr");
    td = document.createElement("td");
    td.innerHTML = element;
    row.appendChild(td);
    let total = 0;
    outlets.forEach(outlet => {
      let el = 0;
      data.forEach(d => {
        if (d.billdate == element && d.outlet == outlet) {
          total += parseInt(d.cash);
          el = d.cash;
        }
      });
      td = document.createElement("td");
      td.innerHTML = el.toLocaleString('en-in');
      td.classList.add("text-right");
      row.appendChild(td);
    });
    /* console.log("row is : " , row.children ) */
    td = document.createElement("td");
    td.innerHTML = total.toLocaleString('en-in');
    td.classList.add("text-right");
    // row.appendChild(td);
    row.insertBefore(td, row.children[1]);
    tbody.appendChild(row);
  });

  table.appendChild(tbody);
  tbl.innerHTML = "";
  tbl.appendChild(table);
  table.classList.add("table");
  table.classList.add("table-striped");
  table.classList.add("table-bordered");
  table.classList.add("table-hover");
}
let formatedData = formatData(data);
renderTable(formatedData);
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.2/css/bootstrap.min.css">
<div align="center">
  <table id="tblOlSalesSummary">
  </table>
</div>

As you can look at my image I have to loop data billdate wise, that's where I am stuck. The image I have uploaded is just for example, please don't match the values there, all the values of image and my JSON are different but Full Total Total should be calculated on the basis of that only.

I know how to create dynamic table but here I am stuck with some looping scenario.

My table is fully dynamic all the data is coming from db on the basis of user selection.

Edit

I have added amount in my JSON data which is billdate wise total so there is not need to calculate that by coding.

Amount is: the total date wise for each outlets

Improve this question
1
  • Not a direct solution but damn.. Use HTML templating, nobody is going to understand fast how your table is build. For example: handlebarsjs.com Commented Feb 27, 2019 at 10:44

2 Answers 2

Reset to default
1

I have got the output similar to the image you have posted.The only possible downside to my code is that it needs a hardcoded array of "sales types", so if the json data is consistent and will not change, this code can be used, otherwise it is not difficult to parse the object and create a brand new array of "sales types" every time.

The basic idea is I have parsed the main object and converted it to this object:

Object{
  date1:{
    outlet1: {
            service1: value,
            service2: value

            },
     outlet2: {
            service1: value,
            service2: value

             }
           }
 date2:{
    outlet1: {
            service1: value,
            service2: value

  }
    ...and so on
}

And then, loop through this object to render the table on the DOM.

var data = [{
    "billdate": "2018-08-01",
    "outlet": "S0001",
    "amount": 291589,
    "cash": 288276,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 3313,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-01",
    "outlet": "S0002",
    "amount": 58337,
    "cash": 56727,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 1610,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-01",
    "outlet": "S0009",
    "amount": 65970,
    "cash": 65970,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 0,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-02",
    "outlet": "S0001",
    "amount": 296125,
    "cash": 290480,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 5645,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-02",
    "outlet": "S0002",
    "amount": 56545,
    "cash": 55034,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 1511,
    "kb": 0,
    "bigbasket": 0
  },
  {
    "billdate": "2018-08-02",
    "outlet": "S0009",
    "amount": 72213,
    "cash": 72213,
    "creditcard": 0,
    "coupon": 0,
    "paytm": 0,
    "credit": 0,
    "swiggy": 0,
    "kb": 0,
    "bigbasket": 0
  }
]


let formatData = function(data) {
  let formattedData = {};
  data.forEach(element => {
    if (!formattedData.hasOwnProperty(element.billdate)){
      formattedData[element.billdate] = {};
    }
  });
  
Object.keys(formattedData).forEach(function(key) {
   //console.log(key, formattedData[key]);
  data.forEach(element => {
    if(key == element.billdate){
      formattedData[key][element.outlet] = {'amount': element.amount,
                                           'cash': element.cash,
                                           'creditcard': element.creditcard,
                                            'coupon': element.coupon,
                                            'paytm': element.paytm,
                                            'credit': element.credit,
                                            'swiggy': element.swiggy,
                                            'kb': element.kb,
                                            'bigbasket': element.bigbasket
                                           };
    }
  });
  
});
  
  //console.log(formattedData);
  return formattedData;
}





let renderTable = function(data) {
  //console.log(data);

  let tbl = document.getElementById("tblOlSalesSummary");
  let table = document.createElement("table");
  let thead = document.createElement("thead");
  let headerRow = document.createElement("tr");
  
  let th = document.createElement("th");
  th.innerHTML = "BillDate";
  th.classList.add("text-center");
  headerRow.appendChild(th);
  
  th = document.createElement("th");
  th.innerHTML = "Sales Type";
  th.classList.add("text-center");
  headerRow.appendChild(th);
  
  let outletArray = [];
  Object.keys(data).forEach(element => {
    let obj = data[element];
    //console.log(obj);
    Object.keys(obj).forEach(elem => {
      if(outletArray.indexOf(elem) == -1){
        outletArray.push(elem);
      }
    });
  });
  //console.log(outletArray);
  
 
  
  outletArray.forEach(element => {
    th = document.createElement("th");
    th.innerHTML = element;
    th.classList.add("text-center");
    headerRow.appendChild(th);
  });

  thead.appendChild(headerRow);
   table.appendChild(thead);

   let tbody = document.createElement("tbody");
  
   
  //full total
  let fullTotal = {};
  outletArray.forEach(elem => {
    fullTotal[elem] = 0;
    Object.keys(data).forEach(element => {

         fullTotal[elem] += data[element][elem]["amount"];
    
    })
  })
  //console.log(fullTotal);
  
  let row = document.createElement("tr");
  td = document.createElement("td");
   td.innerHTML = "";
  row.appendChild(td);
  td = document.createElement("td");
   td.innerHTML = "Full Total";
  row.appendChild(td);
  Object.keys(fullTotal).forEach(elem =>{
    td = document.createElement("td");
   td.innerHTML = fullTotal[elem];
     row.appendChild(td);
  })
 
  tbody.appendChild(row);

  
  
  
  
  
  let salesTypes = ["amount","cash","creditcard","coupon","paytm","credit","swiggy","kb","bigbasket"];
  
  Object.keys(data).forEach(element => {
    
    
    let salesTypesIndex = 0;
   salesTypes.forEach(elem => {
     let row = document.createElement("tr");
     td = document.createElement("td");
     if(salesTypesIndex == 0){
    td.innerHTML = element;
     }else{
    td.innerHTML = "";
     }
    
    row.appendChild(td);
    td = document.createElement("td");
     if(elem == "amount"){
       td.innerHTML = "Totals";
     }else{
        td.innerHTML = elem;
     }
   
    row.appendChild(td);
     
     outletArray.forEach(elem2 => {
       let value = data[element][elem2][elem];
       //console.log("value:",value);
        td = document.createElement("td");
       td.innerHTML = value;
       row.appendChild(td);
     })

    /* console.log("row is : " , row.children ) */

    tbody.appendChild(row);
     
     salesTypesIndex++;
   })
   
  });

  table.appendChild(tbody);
  tbl.innerHTML = "";
  tbl.appendChild(table);
  table.classList.add("table");
  table.classList.add("table-striped");
  table.classList.add("table-bordered");
  table.classList.add("table-hover");
}


let formattedData = formatData(data);
renderTable(formattedData);
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.2/css/bootstrap.min.css">
<div align="center">
  <table id="tblOlSalesSummary">
  </table>
</div>

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

sales-type is not hard coded, any way i have found the solution
@viveksingh sure, i just hard coded for simplicity, but it can be easily constructed everytime from the json response
0

Maybe can help. You should organize your data, then build table with logic

build an array with all Sx and totalvalues build an array with all dates and totalvalues build an array with totals etc

table:
  head and row
  - date cell
  - sales cell
  - foreach Sx a cell

  another row
  - empty cell
  - fulltotal cell
  - foreach Sx totalvalues cells

  body
    foreach date a row with
    - date cell
    - total cell
    - totalvalues cells on each Sx

    forech field in date a row with
      - empty cell
      - name cell
      - foreach sX a cell with value
Improve this answer

6 Comments

but calculation i am not able to do.. i am bit of weak in logic and basics thats why put up the question..if you can make some usefull code ,it would be very helpfull
you have to separate total build from html build, so when you have all the data you want in arrays, you can build the table and get totals from here, like outletWiseTotal, use your formatData function to save keys and totals then build
@ you are saying amount which i am calling from backend i should calculate here only in javascript?
outletWiseTotal, grandTotal, total += parseInt(d.cash) All this is on javascript?
outletWiseTotal i have in my JSON as amount grand total is full total which should be calculated in javascript
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.