I have created a template parameter with two user numeric inputs. When he clicks on the save button, i would like to insert the parameters to db. However I am getting 0 for the 'CurrentPlan' and "NextPlan' columns.
tabledata = event.source.parent.getComponent('tmp_Rpt_plan').templateParams
data = system.dataset.toPyDataSet(tabledata)
#event.source.parent.getComponent('Table').data = data
for row in range(data.getRowCount()):
column1 = data.getValueAt(row,'MachineName')
column2 = event.source.parent.Process
column3 = system.date.now()
column4 = data.getValueAt(row,'CurrentPlan')
column5 = system.date.addDays(column3, 1)
column6 = data.getValueAt(row,'NextPlan')
column7 = system.date.now()
column8 = "ign"
column9 = system.date.now()
column10= "ign"
column11 = event.source.parent.MachineID
vMachineID =system.dataset.toPyDataSet(column11)
for row in range(vMachineID.getRowCount()):
for col in range(vMachineID.getColumnCount()):
tMachine = vMachineID.getValueAt(row, col)
system.db.runPrepUpdate("INSERT INTO cdcms.tbl_production_plan (EquipmentID,EquipmentName, Process,CurrentDay,CurrentDayPlan,NextDay,NextDayPlan,_createdDate,_createdUser,_modifiedDate,_modifiedUser) VALUES (?,?,?,?,?,?,?,?,?,?,?)", [tMachine,column1, column2,column3,column4,column5,column6,column7,column8,column9,column10])
Add some logging to your code, reporting various variables to the console, so you can see if the flow matches your intentions. Also capture and log the return value from system.db.runPrepUpdate().
The problem is that the template parameters are not automatically changed whenever a value is edited in the textboxes. Add this to the very beginning of your save button's actionPerformed event handler, and it will fix your problem:
from com.inductiveautomation.factorypmi.application.components import PMINumericTextField
repeater = event.source.parent.getComponent('tmp_Rpt_plan').getLoadedTemplates()
for row, component in enumerate(repeater):
data = event.source.parent.getComponent('tmp_Rpt_plan').templateParams
dsUpdate = []
for subcomponent in component.getComponents():
if isinstance(subcomponent, PMINumericTextField):
dsUpdate.append(subcomponent.value)
event.source.parent.getComponent('tmp_Rpt_plan').templateParams = system.dataset.updateRow(data, row, {'CurrentPlan':dsUpdate[1], 'NextPlan':dsUpdate[0]})
The code takes the value from your textboxes and updates the template parameter dataset that you are subsequently using to update your database. Later on, with some effort, you could probably incorperate your database write into this code, and eliminate the subsequent iterations through the template parameters.
This is working fine. But there are few mismatches in the retrieving and saving. So i have included json format and modified the db table structure,
Save button:
import json
import re
tabledata = event.source.parent.getComponent('tmp_Rpt_plan').templateParams
data = system.dataset.toPyDataSet(tabledata)
from com.inductiveautomation.factorypmi.application.components import PMINumericTextField
repeater = event.source.parent.getComponent('tmp_Rpt_plan').getLoadedTemplates()
for row, component in enumerate(repeater):
data = event.source.parent.getComponent('tmp_Rpt_plan').templateParams
dsUpdate = []
for subcomponent in component.getComponents():
if isinstance(subcomponent, PMINumericTextField):
dsUpdate.append(subcomponent.value)
event.source.parent.getComponent('tmp_Rpt_plan').templateParams = system.dataset.updateRow(data, row, {'CurrentDayPlan':dsUpdate[1], 'NextDayPlan':dsUpdate[0]})
jsondata ={}
MachineName ={}
CurrentDayPlan ={}
NextDayPlan ={}
MachineID = {}
#tableName ="tbl_productionplan"
childcount = data.getRowCount()
Process = event.source.parent.Process
for a in range(childcount):
MachineName[a] =data.getValueAt(a,'MachineName')
CurrentDayPlan[a]= data.getValueAt(a,'CurrentDayPlan')
NextDayPlan[a]=data.getValueAt(a,'NextDayPlan')
MachineID[a] =data.getValueAt(a,'MachineID')
jsondata = {'MachineID':MachineID,'CurrentDayPlan':CurrentDayPlan,'NextDayPlan':NextDayPlan}
json_data = json.dumps(jsondata)
params ={"iPlanData":json_data,"iProcess":Process}
system.db.runNamedQuery("Prod_Plan/InsertPlan",params )
In my experience, Vision usually doesn't require very much hocus pocus when it comes to this sort of thing. You should be able to do something like this to get data into the template parameters: event.source.parent.getComponent('tmp_Rpt_plan').templateParams = system.db.runPrepQuery("SELECT * FROM cdcms.tbl_productionplan WHERE ...")
Previously I have designed db tabled as replica of the display screen,
But issues, I have faced:
User might enter the currentday plan and leave nextday plan as blank & vice versa
User might enter few currentday plan for few machiens and leave other fields blank
And user can enter the empty fields later.
And if he entered the values & saved. Once he click on the add plan button . Already saved values should be displayed and editing should be possible
If there are 0s in the already saved values, editing should be possible.
For eg:
if he enters the value for today (31-10-2022) and (01-11-2022) and if he opens the screen tomorrow,
current plan (01-11-2022) should have the already entered values.
Because of these issues, I have used JSON to save and retrieve. Now saving is working and IDK how to fetch to satisfy the above constraints.
New table:
I wouldn't use jsons for this sort of thing; I would use client tags. It's much simpler. You could even bidirectionally bind your custom dataset properties to client dataset tags, and retrieval would be automatic.
I'm starting to get my head around what you are saying. If I understand you correctly, the user can't write to the database until all of the fields have been completed, and the user doesn't have to fill out all of the fields during a single session or even within the same day. If so, you are correct; the client tags would lose the information.
In this case, I suppose a json file would be as viable of an intermediary as any. It's an interesting problem.
