Author Topic: compute cell values  (Read 870 times)

Richard

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 41
  • Richard
    • View Profile
compute cell values
« on: March 29, 2022, 09:57:09 am »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #1 on: March 30, 2022, 10:23:49 am »
[ Only Pro members may read this post. ]
« Last Edit: March 30, 2022, 10:30:38 am by paramvir »

Richard

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 41
  • Richard
    • View Profile
Re: compute cell values
« Reply #2 on: March 30, 2022, 01:06:13 pm »
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.
« Last Edit: March 30, 2022, 01:13:27 pm by Richard »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #3 on: March 31, 2022, 01:40:07 pm »
[ Only Pro members may read this post. ]

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #4 on: April 01, 2022, 09:52:43 am »
[ Only Pro members may read this post. ]

Richard

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 41
  • Richard
    • View Profile
Re: compute cell values
« Reply #5 on: April 04, 2022, 06:41:02 am »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #6 on: April 04, 2022, 10:52:16 am »
[ Only Pro members may read this post. ]

Richard

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 41
  • Richard
    • View Profile
Re: compute cell values
« Reply #7 on: April 05, 2022, 10:24:57 am »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #8 on: April 05, 2022, 05:25:02 pm »
[ Only Pro members may read this post. ]

Richard

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 41
  • Richard
    • View Profile
Re: compute cell values
« Reply #9 on: April 06, 2022, 05:14:53 am »
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;
   }
},
« Last Edit: April 06, 2022, 05:17:20 am by Richard »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #10 on: April 06, 2022, 09:28:13 am »
[ Only Pro members may read this post. ]

Richard

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 41
  • Richard
    • View Profile
Re: compute cell values
« Reply #11 on: April 06, 2022, 11:16:46 am »
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)
« Last Edit: April 06, 2022, 11:23:13 am by Richard »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #12 on: April 06, 2022, 01:29:57 pm »
[ Only Pro members may read this post. ]

Richard

  • Pro Ultimate
  • Newbie
  • *
  • Posts: 41
  • Richard
    • View Profile
Re: compute cell values
« Reply #13 on: April 07, 2022, 11:12:10 am »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 5740
    • View Profile
Re: compute cell values
« Reply #14 on: April 08, 2022, 09:45:38 am »
[ Only Pro members may read this post. ]
« Last Edit: April 11, 2022, 03:02:09 pm by paramvir »