Combining datasets

Ignition
1

I have a dataset of shift strings from my window.
I have 2 popup calendars for start date and end date.
I'm trying to create a dataset that looks like this to setup child groups broken out by date and shift.

Date Shift Shiftdataset
10/5/2015 Shift1 [shiftdataset]
10/5/2015 Shift2 [shiftdataset]
10/6/2015 Shift1 [shiftdataset]
10/6/2015 Shift2 [shiftdataset]

I can't seem to get past the date artithmatic, and i've tried datetime.timedelta and regular string formatting, nothing seems to work for me.
button code:

from datetime import datetime
#create a dataset within a dataset
date1 = event.source.parent.getComponent('Popup Calendar').date
date2 = event.source.parent.getComponent('Popup Calendar 1').date
start = datetime.strptime(date1, '%YYYY-%m-%d')
end = datetime.strptime(date2, '%YYYY-%m-%d')
#set up the final data variables
finalHeader = ["Date", "Shift"]
finalData = []
diffTime = (end-start).days
dateList = []
while start <= end:
	dateList.append(start)
	start += step
#Select is a dataset with one column
#create a dataset with column of dates between date1 and date2 AND the Shift column
shifts = event.source.parent.Select
dateData = [dates, shifts]
event.source.parent.getComponent('Table 2').data = system.dataset.toDataSet(finalHeader, finalData)

I'm not concered about the shiftdata query yet.
ignition version 7.6.6
Any suggestions?

2

I want to get a list of the dates like this

delta = datetime.timedelta(days=1) while startDate <= endDate: event.source.parent.getComponent('Label').text = startDate.strftime("%Y-%m-%d") startDate += delta

I’m going to try to convert to strings instead.

3

There be dragons!

Timezones and daylight savings time will eat your lunch whenever you try this sort of thing. Fortunately, java has the Calendar class that will take care of such things. Your event script would look like this:

[code]from java.util import Calendar
shift1hour = 6
shift2hour = 14

cal = Calendar.getInstance()
cal.time = event.source.parent.getComponent(“Popup Calendar”).date

date2 = event.source.parent.getComponent(“Popup Calendar 1”).date

rows =
while cal.time.before(date2):
cal.set(Calendar.HOUR_OF_DAY, shift1hour)
rows.append([cal.getTime(), “1”])
cal.set(Calendar.HOUR_OF_DAY, shift2hour)
rows.append([cal.getTime(), “2”])
cal.add(Calendar.DAY_OF_MONTH, 1)

table = event.source.parent.getComponent(“Table”)
table.data = system.dataset.toDataSet([‘start’, ‘shift’], rows)
[/code]

The “start” column may be all you need for your queries. Or you could add an “end” column to bracket the shift timeframe.
Shifts_2015-03-19.proj (15.6 KB)

4

Thanks!
Trying to open your window i get this error though,

SerializationException: Unexpected parsing error during binary deserialization.
caused by IOExceptionWithCause: java.lang.ClassNotFoundException: com.inductiveautomation.ignition.common.script.ScriptScopeStyle
caused by ClassNotFoundException: com.inductiveautomation.ignition.common.script.ScriptScopeStyle

Ignition v7.6.6 (b2014040112)
Java: Oracle Corporation 1.7.0_76

5

Sorry, I’m on 7.7.2. Project files aren’t backward compatible.

But just add a table named “Table” the window you started with and it should “just work”.

6

I got what I was looking for, thanks again for the calendar idea!
4 fields Date, Shift, Start, End

here’s my code.

[code]from datetime import datetime
from java.util import Calendar

#create a dataset within a dataset
cal = Calendar.getInstance()
cal.time = event.source.parent.getComponent(‘Popup Calendar’).date
date2 = event.source.parent.getComponent(‘Popup Calendar 1’).date
startDate = system.db.dateFormat(cal.time, “yyyy-MM-dd HH:mm:ss”)
endDate = system.db.dateFormat(date2, “yyyy-MM-dd HH:mm:ss”)

#Select is a dataset with one column
shifts = event.source.parent.Select
shiftPyData = system.dataset.toPyDataSet(shifts)
rows = []
while cal.time.before(date2):
num = 0
for x in shiftPyData:
value = shiftPyData[num][0]
event.source.parent.getComponent(‘Label’).text = value
query = “SELECT Starttime, Endtime from Shifts WHERE Shiftname = ‘%s’” % (value)
results = system.db.runQuery(query)
shiftStart = results[0][0]
shiftEnd = results[0][1]
rows.append([cal.getTime(), value, shiftStart, shiftEnd])
num += 1
cal.add(Calendar.DAY_OF_MONTH, 1)

#set up the final data variables
table = event.source.parent.getComponent(“Table 2”)
table.data = system.dataset.toDataSet([‘Date’, ‘Shift’, ‘StartTime’, ‘EndTime’], rows)[/code]