ParamQuery grid support forum
General Category => Help for ParamQuery Pro => Topic started by: Richard 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)'}
},
],
-
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 )
-
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.
-
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.
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.
change: function(){
this.refresh();
},
Result: https://jsfiddle.net/0rp52swa/
-
And reference to grid is obtained in render event.
render: function(){
grid = this;
},
Please let me know if you need further assistance.
-
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.
-
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
-
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();
-
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
getData: function (response) {
debugger;
response.data.forEach(function(rd){
rd.pq_cellattr = JSON.parse(rd.pq_cellattr);
if(rd.cd_kind == 4){
......
}
})
return response;
}
-
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;
}
},
-
Apparently your code snippet looks fine. Please check the browser developer console for error and its stack trace.
-
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)
-
Please add the checks for existence of various variables before reading their property.
e.g.,
day = (pdata && pdata[ri-4])? pdata[ri-4].qty1: 0;
-
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;
}
},
-
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.
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
-
Hi paramvir
I haven't tried the method you gave me today, but I'll try it.
Regarding the stock quantity calculation, I have to solve the following part, so I ask the question again.
I would really appreciate it if you could let me how to solve it.
1) For Stock quantity (C) on the first day of every month,
I want to display the result of adding Day production and Night production to Stock quantity of the previous month (A)
and subtracting Shipment quantity and Etc quantity.
(As you konw, the stock quantity from the second day (D) of every month is displayed as the attached image shows the result of adding
Day production and Night production to the previous day's stock quantity and subtracting Shipment quantity and Etc quantity)
2) In the Stock quantity (B) of this month (March in the attched image), I want to display the Stock quantity of the last day of this month.
-
What issue are you facing exactly? The way I see it, it's simple addition and subtraction of cell values and cell values are obtained from rowData[ dataIndx ], example of which I have shared already in previous post.
Please let me know if I've missed something.
-
Hi paramvir.
I have 2 questions.
1) Regarding cell data calculation, I applied the rowTemplate that you informed me on April 8, but it did not work as I wanted.
So, I modified it as follows and got the result I wanted, so please review it.
if(rd.cd_kind == 4){
Object.defineProperty(rd, 'qty1', {
enumerable: true,
get (){
var rd = this, ri = rd.pq_ri, pdata = grid.pageData(),
pre_month_stock = rd['total_pre_month'],
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 pre_month_stock + day + night - shipment - etc;
}
}),
Object.defineProperty(rd, 'qty2', {
enumerable: true,
get (){
var rd = this, ri = rd.pq_ri, pdata = grid.pageData(),
pre_day_stock = (pdata && pdata[ri])? pdata[ri].qty1: 0,
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;
total_this_month = pre_day_stock + day + night - shipment - etc;
return total_this_month;
}
}),
....
Object.defineProperty(rd, 'total_this_month1', { // March Results
get (){
return total_this_month;
}
})
}
2) Another question is, as in the attached image,
if the user accesses this screen again after saving the value set by the user to show/hide the column in the DB,
the value is retrieved from the DB and the previously set value is saved.
I want to display it on the screen in the form.
When the Save button is clicked, I want to save the changed value of the cell and the display/hide setting value of the column at the same time in the DB.
I would really appreciate it if you could let me how.
-
Hi Richard
1. We can only help how to use a particular feature of pqgrid. Checking business code or logic is outside the scope of support.
2. Columns hidden state comes under state maintenance of grid using API methods saveState and loadState.
Example: https://paramquery.com/pro/demos/grid_state
-
Hi paramvir
Referring to the screen you commented on,
it was confirmed that the setting was saved when the Save button was clicked after showing/hiding the column.
It is the state coded as below in relation to saving and loading the setting state of Show/Hide Column.
function save_column_state(){
this.saveState();
}
{
type: 'button',
icon: 'ui-icon-disk',
label: 'Save',
cls: 'changes',
listeners: [saveChanges,
save_memo,
save_column_state ],
options: { disabled: true },
},
create: function (){
this.loadState({ refresh: false });
},
When the user accesses the screen for the first time,
the Save button is disabled, but if a specific column is checked to show or unhide,
the Save button does not change to the enabled state.
I want to make the Save button change to enable when changing a cell.
I would really appreciate it if you could let me how to do this.
-
hideCols event is triggered when a column is hidden or displayed: https://paramquery.com/pro/api#event-hideCols
So this event can be used to enable the save button.
-
Hi paramvir
I would appreciate it if you could let me how to set the hideCols event to make enable the Save button.
I've tried several methods, but with no success, I'm asking you again.
-
Assuming you have a save button (having class 'changes' ) in the toolbar:
hideCols: function(){
var $tb = this.toolbar;
$('button.changes', $tb).button('option', { disabled: false });
},
-
Hi paramvir
As a result of applying the method you gave,
there is a problem that the Save button does not change to enable
when checking a column for the first time in the Column List or unchecking it for the first time.
After the second time, the Save button is changed to enable.
hideCols: function (){
var $tb = this.toolbar;
$('button.changes', $tb).button('option', { disabled: false });
},
I'd appreciate it if you could let me know what the problem is.
-
Sorry about that.
Please correct
var $tb = this.toolbar;
to
var $tb = this.toolbar();