compute cell values

[Richard]

Hi paramvir.

The problem was solved by modifying the program according to your comments.
Thank you.

I have one more problem to solve.
As shown in the image below, by calculating the daily stock quantity by product name,
each of A, B, C, D, ... should be indicated, and each calculation formula is as follows.
1) A = Stock quantity for the previous month + data on March 1st(Day + Night – Shipment – Etc)
2) B = A + data on March 2nd(Day + Night – Shipment – Etc)
3) C = B + data on March 3rd(Day + Night – Shipment – Etc)

The problem to be solved is to make the daily stock data change automatically
when the user enters data into the Day and Night cells.

The monthly and daily totals for Day and Night by Product Name are
the desired value was displayed by making it as below,
but I don't know how to solve the above problem.

I would appreciate it if you let me how to solve it.


var obj = {
  title: '<b>Production Schedule</b>',
  height: 900,
  freezeCols: 14,
  collapsible: {on: false},
  hwrap: true,
  //rowHt: 35,
  trackModel: { on: true },
  //numberCell: { show: true },
  //selectionModel: { type: 'row' },

  formulas: [
     ['total_this_month1', function (rd) {
           return rd['qty1'] + rd['qty2'] + rd['qty3'] + rd['qty4'] + rd['qty5'] + rd['qty6'] + rd['qty7'] + rd['qty8'] + rd['qty9'] + rd['qty10'] +
    rd['qty11'] + rd['qty12'] + rd['qty13'] + rd['qty14'] + rd['qty15'] + rd['qty16'] + rd['qty17'] + rd['qty18'] + rd['qty19'] + rd['qty20'] +
    rd['qty21'] + rd['qty22'] + rd['qty23'] + rd['qty24'] + rd['qty25'] + rd['qty26'] + rd['qty27'] + rd['qty28'] + rd['qty29'] + rd['qty30'] +
    rd['qty31'] ; }],
     ['total_this_month2', function (rd) {
           return rd['qty1'] + rd['qty2'] + rd['qty3'] + rd['qty4'] + rd['qty5'] + rd['qty6'] + rd['qty7'] + rd['qty8'] + rd['qty9'] + rd['qty10'] +
    rd['qty11'] + rd['qty12'] + rd['qty13'] + rd['qty14'] + rd['qty15'] + rd['qty16'] + rd['qty17'] + rd['qty18'] + rd['qty19'] + rd['qty20'] +
    rd['qty21'] + rd['qty22'] + rd['qty23'] + rd['qty24'] + rd['qty25'] + rd['qty26'] + rd['qty27'] + rd['qty28'] + rd['qty29'] + rd['qty30'] +
    rd['qty31'] ; }],
   ],

   // Summary ------------------------------------------------------------
   summaryData: [
      { info_item:'Total', summaryRow: true, pq_fn:{
      total_pre_month:'sum(M:M)', total_this_month1:'sum(N:N)', 'qty1':'sum(O:O)', 'qty2':'sum(P:P)', 'qty3':'sum(Q:Q)', 'qty4':'sum(R:R)',
           'qty5':'sum(S:S)', 'qty6':'sum(T:T)', 'qty7':'sum(U:U)', 'qty8':'sum(V:V)', 'qty9':'sum(W:W)', 'qty10':'sum(X:X)', 'qty11':'sum(Y:Y)',
           'qty12':'sum(Z:Z)', 'qty13':'sum(AA:AA)', 'qty14':'sum(AB:AB)', 'qty15':'sum(AC:AC)', 'qty16':'sum(AD:AD)', 'qty17':'sum(AE:AE)',
           'qty18':'sum(AF:AF)', 'qty19':'sum(AG:AG)', 'qty20':'sum(AH:AH)', 'qty21':'sum(AI:AI)', 'qty22':'sum(AJ:AJ)', 'qty23':'sum(AK:AK)',
           'qty24':'sum(AL:AL)', 'qty25':'sum(AM:AM)', 'qty26':'sum(AN:AN)', 'qty27':'sum(AO:AO)', 'qty28':'sum(AP:AP)', 'qty29':'sum(AQ:AQ)',
           'qty30':'sum(AR:AR)', 'qty31':'sum(AS:AS)', 'qty32':'sum(AT:AT)', 'qty33':'sum(AU:AU)', total_this_month2:'sum(N:N)'}
      },
   ],

[paramvir]

Could you please mention what exact specific issue are you facing?

Excel formulas can be used to compute cell values dependent upon other cell values. Have you tried them.

Address and range of cells is used rather than dataIndx in Excel formulas.

Example of defining them with pq_fn in JSON data is provided here: https://paramquery.com/pro/demos/excel_formula

e.g., to get the sum of range of values in a month: SUM( address of first cell : address of last cell )

[Richard]

Hi

What I want is like the image above
by product name
when the user enters quantity data into the daily Day and Night cells,
add Day data and Night data, and add the stock quantity of the previous day to this.
Subtract the quantity of Shipment on the day, and Subtract the quantity of Etc.
I want to code the part that displays the resulting data in the Stock cell(A,B,C,D..).

I've seen the Demo you mentioned, but it's not what I want.

I'd appreciate it if you could tell me how to do it.

[paramvir]

js getters can also be used to compute values of A,B,C etc cells. I've created a similar example to your requirement, please update with your business logic.

Code: [Select]

data.forEach(function(rd){
   if( rd.cd_kind == 4 ){
   
Object.defineProperty(rd, 'qty1', {
          enumerable: true,
          get (){           
          //debugger;
            var rd = this, ri = rd.pq_ri, pdata = grid.pageData(), day = pdata[ri-4].qty1, night = pdata[ri-3].qty1,
            shipment = pdata[ri-2].qty1, etc = pdata[ri-1].qty1;
           
            return day + night - shipment - etc;
          }
        })   
  }
})

ALso ensure to refresh all cells when any cell value changes.

Code: [Select]

    change: function(){
    this.refresh();
    },

Result: https://jsfiddle.net/0rp52swa/

[paramvir]

And reference to grid is obtained in render event.

Code: [Select]

render: function(){
     grid = this;
},

Please let me know if you need further assistance.

[Richard]

Hi paramvir

In jsfiddle, I checked that the program was working normally.
I applied what you commented to my program,
but nothing was displayed on the screen.

The reason is that my program is different from the program in jsfiddle.
This is because the data in the DB is fetched and displayed on the screen.
I don't know how to fix it because my understanding is not good enough.

And I have one more question.
Just like in an Excel file, after entering data in a cell, press the Enter key,
I want to set the cursor to move to the cell below.

Two questions about what to do
any help would be greatly appreciated.

[paramvir]

1. I understand jsfiddle has local data while it's remote data in your case. Please iterate ( using forEach loop ) over the data in dataModel.getData callback.

2. Please set editModel.onSave to 'downFocus' Example: https://paramquery.com/pro/demos/import-xlsx

[Richard]

Hi paramvir

Based on your comments, the result was modified as follows
I have checked that the daily stock data is calculated correctly.
However, in this state, after saving the data to the DB,
there is a problem that only daily stock data is not stored in the DB.

I would appreciate it if you could tell me how to solve it.


$(function (){
   function calculate_stock(grid){
      var data = grid.option("dataModel.data");

      data.forEach(function(rd){
         if(rd.cd_kind == 4){
            //rd.pq_fn = {qty1: fn, qty2: fn, qty3: fn, qty4: fn};
            Object.defineProperty(rd, 'qty1', {
               enumerable: true,
               get (){
                  //debugger;
                  var rd = this, ri = rd.pq_ri, pdata = grid.pageData(), day = pdata[ri-4].qty1, night = pdata[ri-3].qty1, shipment = pdata[ri-2].qty1, etc = pdata[ri-1].qty1;
                  return day + night - shipment - etc;
               }
            })
         }
      })
   }


   var obj = {
      title: '<b>Production Schedule</b>',
      height: 900,
      freezeCols: 14,
      collapsible: {on: false},
      hwrap: true,
      trackModel: { on: true },
      render: function(){
         grid = this;
      },

      change: function(){
         grid = this;
         calculate_stock(grid);
         this.refresh();
      

[paramvir]

That is incorrect.

Please use dataModel.getData callback to place the code. The same callback is discussed in this post: https://paramquery.com/forum/index.php?topic=4305.msg15422#msg15422

Code: [Select]

                getData: function (response) {
                    debugger;
                    response.data.forEach(function(rd){
                        rd.pq_cellattr = JSON.parse(rd.pq_cellattr);
                        if(rd.cd_kind == 4){
                              ......
                        }
                    })
                    return response;
                }


[Richard]

Hi paramvir

The source code that I uploaded together while inquiring yesterday (April 5) is
the result of testing by applying the comment you provided on March 31 as follows
only the phrase 'No rows to display' was displayed on the screen.
So I made a separate function and applied it.

I'd appreciate it if you could let me know which part is the problem.

dataModel: {
   dataType: 'JSON',
   location: 'remote',
   recIndx: 'id',
   url: "product_plan_save.php",

   getData: function (response){
      debugger;
      response.data.forEach(function(rd){
         if(rd.pq_cellattr){
            rd.pq_cellattr = JSON.parse(rd.pq_cellattr);
         }

         if(rd.cd_kind == 4){
            Object.defineProperty(rd, 'qty1', {
               enumerable: true,
               get (){           
                  //debugger;
                  var rd = this, ri = rd.pq_ri, pdata = grid.pageData(), day = pdata[ri-4].qty1, night = pdata[ri-3].qty1,
                  shipment = pdata[ri-2].qty1, etc = pdata[ri-1].qty1;

                  return day + night - shipment - etc;
               }
            })   
         }
      })
      return response;
   }
},

[paramvir]

Apparently your code snippet looks fine. Please check the browser developer console for error and its stack trace.

[Richard]

Hi paramvir

First of all, thank you very much for your sincere responses to the continuing questions.

The following error message is displayed on the browser developer console.
I would appreciate it if you could tell me how to solve it.

1) Code (the part where the error occurred)
var rd = this, ri = rd.pq_ri, pdata = grid.pageData(), day = pdata[ri-4].qty1, night = pdata[ri-3].qty1,
                  shipment = pdata[ri-2].qty1, etc = pdata[ri-1].qty1;

2) Error Message
product_plan_list.php:1240 Uncaught TypeError: Cannot read properties of undefined (reading 'qty1')
    at Object.get [as qty1] (product_plan_list.php:1240:30)
    at $.<computed>.<computed>.obj.formulas (product_plan_list.php:192:48)
    at e.cFormula.callRow (pqgrid.min.js:8:227783)
    at e.cFormula.calcMainData (pqgrid.min.js:8:228110)
    at e.cFormula.onDA (pqgrid.min.js:8:227831)
    at n (pqgrid.min.js:8:9161)
    at $.<computed>.<computed>.e._trigger (pqgrid.min.js:8:9571)
    at $.<computed>.<computed>.o.<computed> [as _onDataAvailable] (pqgrid.min.js:8:118570)
    at Object.callback (pqgrid.min.js:8:55299)
    at $.<computed>.<computed>.a.<computed> [as onRemoteSuccess] (pqgrid.min.js:8:53176)

[paramvir]

Please add the checks for existence of various variables before reading their property.

e.g.,

Code: [Select]

day = (pdata && pdata[ri-4])? pdata[ri-4].qty1: 0;


[Richard]

Hi paramvir

Thank you very much for your help and the problem has been resolved.

However, since the program I want to make is a program that makes a monthly production plan,
in the case of March, it must be indicated from the 1st to the 31st,
in the case of April, it must be indicated from the 1st to the 30th.
If it is composed in the form below, the source seems to be too complicated.

I'd really appreciate it if you could let me know if there's a simpler way to make it.


dataModel: {
   dataType: 'JSON',
   location: 'remote',
   recIndx: 'id',
   url: "product_plan_save.php",

   getData: function (response){
      debugger;
      response.data.forEach(function(rd){
         if(rd.pq_cellattr){
            rd.pq_cellattr = JSON.parse(rd.pq_cellattr);
         }

         if(rd.cd_kind == 4){
            //rd.pq_fn = {qty1: fn, qty2: fn, qty3: fn, qty4: fn};
            Object.defineProperty(rd, 'qty1', {
               enumerable: true,
               get (){
                  //debugger;
                  var   rd         = this,
                        ri         = rd.pq_ri,
                        pdata      = grid.pageData(),
                        day      = (pdata && pdata[ri-4])? pdata[ri-4].qty1: 0,
                        night      = (pdata && pdata[ri-3])? pdata[ri-3].qty1: 0,
                        shipment = (pdata && pdata[ri-2])? pdata[ri-2].qty1: 0,
                        etc      = (pdata && pdata[ri-1])? pdata[ri-1].qty1: 0;
                  return day + night - shipment - etc;
               }
            }),
            Object.defineProperty(rd, 'qty2', {
               enumerable: true,
               get (){
                  //debugger;
                  var   rd               = this,
                        ri               = rd.pq_ri,
                        pdata            = grid.pageData(),
                        pre_day_stock   = (pdata && pdata[ri])? pdata[ri].qty1: 0,   // stock quantity of the previous day
                        day            = (pdata && pdata[ri-4])? pdata[ri-4].qty2: 0,
                        night            = (pdata && pdata[ri-3])? pdata[ri-3].qty2: 0,
                        shipment         = (pdata && pdata[ri-2])? pdata[ri-2].qty2: 0,
                        etc            = (pdata && pdata[ri-1])? pdata[ri-1].qty2: 0;
                  return pre_day_stock + day + night - shipment - etc;
               }
            })
         }
      })
      return response;
   }
},

[paramvir]

Hi Richard

As there is a similar pattern in the various getter functions, the common part can be abstracted into a separate function to make it less verbose.

Instead of writing getters manually and looping through the data, logic for calculating qty1, qty2, etc can be placed in rowTemplate which is less verbose and easy to maintain. Also it automatically applies to new rows.

Code: [Select]

rowTemplate: {
    get qty1() {
       var   rd         = this,
               ri         = rd.pq_ri,
               pdata      = grid.pageData(),
               day      = (pdata && pdata[ri-4])? pdata[ri-4].qty1: 0,
               night      = (pdata && pdata[ri-3])? pdata[ri-3].qty1: 0,
               shipment = (pdata && pdata[ri-2])? pdata[ri-2].qty1: 0,
               etc      = (pdata && pdata[ri-1])? pdata[ri-1].qty1: 0;
         return day + night - shipment - etc;
    },
    get qty2() {
       ....
    }
},

There is a similar example for computing cell values in RANK column https://paramquery.com/pro/demos/data_bars