The order table looks generated according to the ProductionHours column. If you were looking for a scripted solution:
##############################################################################
# Sample datasets
downHeaders = ['StartDateTime', 'EndDateTime']
downList = [['2023-12-24 07:00:00', '2023-12-26 07:00:00'],
['2024-01-01 07:00:00', '2024-01-02 07:00:00'],
['2024-03-29 07:00:00', '2024-03-30 07:00:00']
]
# parse all the dates I was too lazy to do individually . ;)
downList = [[system.date.parse(item) for item in row] for row in downList]
# I reduced the contracts down to the contract number and production hours.
contractHeaders = ['contract', 'ProductionHours']
contractList = [[1, 0],
[2, 8],
[3, 10],
[4, 24],
[5, 88],
[6, 3],
[7, 12],
[8, 24],
[9, 80],
[10, 8]
]
# Create the sample datasets
downData = system.dataset.toPyDataSet(system.dataset.toDataSet(downHeaders, downList))
contractData = system.dataset.toPyDataSet(system.dataset.toDataSet(contractHeaders, contractList))
#############################################################################
def overlap(start1, end1, start2, end2):
''' check if two date ranges (start1, end1), start2, end2) overlap
'''
return system.date.isBetween(start1, start2, end2) \
or system.date.isBetween( end1, start2, end2) \
or system.date.isBetween(start2, start1, end1) \
or system.date.isBetween( end2, start1, end1)
# Set the production start date.
startDateTime = productionStart = system.date.parse('2023-12-22 07:00:00')
productionHeaders = ['contract', 'ProductionHours', 'DownTimeHours', 'StartDateTime', 'EndDateTime']
productionList = []
for row in contractData:
downHours = 0
# Set the planned production end date
endDateTime = system.date.addHours(startDateTime, row['ProductionHours'])
for downDay in downData:
# check for holiday overlap, and add the extra downtime, if needed
if overlap(startDateTime, endDateTime, downDay['StartDateTime'], downDay['EndDateTime']):
downHours = system.date.hoursBetween(downDay['StartDateTime'], downDay['EndDateTime'])
endDateTime = system.date.addHours(endDateTime, downHours)
break
productionList.append([row['contract'], row['ProductionHours'], downHours, startDateTime, endDateTime])
startDateTime = endDateTime
productionData = system.dataset.toDataSet(productionHeaders, productionList)
Dataset out:
row | contract | ProductionHours | DownTimeHours | StartDateTime | EndDateTime
--------------------------------------------------------------------------------------------------------------
0 | 1 | 0 | 0 | Fri Dec 22 07:00:00 EST 2023 | Fri Dec 22 07:00:00 EST 2023
1 | 2 | 8 | 0 | Fri Dec 22 07:00:00 EST 2023 | Fri Dec 22 15:00:00 EST 2023
2 | 3 | 10 | 0 | Fri Dec 22 15:00:00 EST 2023 | Sat Dec 23 01:00:00 EST 2023
3 | 4 | 24 | 0 | Sat Dec 23 01:00:00 EST 2023 | Sun Dec 24 01:00:00 EST 2023
4 | 5 | 88 | 48 | Sun Dec 24 01:00:00 EST 2023 | Fri Dec 29 17:00:00 EST 2023
5 | 6 | 3 | 0 | Fri Dec 29 17:00:00 EST 2023 | Fri Dec 29 20:00:00 EST 2023
6 | 7 | 12 | 0 | Fri Dec 29 20:00:00 EST 2023 | Sat Dec 30 08:00:00 EST 2023
7 | 8 | 24 | 0 | Sat Dec 30 08:00:00 EST 2023 | Sun Dec 31 08:00:00 EST 2023
8 | 9 | 80 | 24 | Sun Dec 31 08:00:00 EST 2023 | Thu Jan 04 16:00:00 EST 2024
9 | 10 | 8 | 0 | Thu Jan 04 16:00:00 EST 2024 | Fri Jan 05 00:00:00 EST 2024
That said, since we don't know how the orders table is populated, it makes it a bit more difficult.