def loadSuboperations(self):
print('Importing suboperations...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT operation_id, suboperation_id, priority, effective_start, effective_end,
(select type
from operation
where suboperation.operation_id = operation.name) as type
FROM suboperation
WHERE priority >= 0 %s
ORDER BY operation_id, priority
''' % self.filter_and)
curopername = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[0] != curopername:
curopername = i[0]
curoper = frepple.operation(name=curopername)
sub = frepple.suboperation(
owner=curoper,
operation=frepple.operation(name=i[1]),
priority=i[2]
)
if i[3]:
sub.effective_start = i[3]
if i[4]:
sub.effective_end = i[4]
except Exception as e:
print("Error:", e)
print('Loaded %d suboperations in %.2f seconds' % (cnt, time() - starttime))
def loadOperationPlans(cursor):
print('Importing operationplans...')
cnt = 0
starttime = time()
cursor.execute(
'''SELECT operation_id, id, quantity, startdate, enddate, locked
FROM operationplan
where owner_id is null
order by id asc''')
for i, j, k, l, m, n in cursor.fetchall():
cnt += 1
frepple.operationplan(operation=frepple.operation(name=i),
id=j,
quantity=k,
start=l,
end=m).locked = n
cursor.execute(
'''SELECT operation_id, id, quantity, startdate, enddate, locked, owner_id
FROM operationplan
where owner_id is not null
order by id asc''')
for i, j, k, l, m, n, o in cursor.fetchall():
cnt += 1
frepple.operationplan(operation=frepple.operation(name=i),
id=j,
quantity=k,
start=l,
end=m,
owner=frepple.operationplan(id=o)).locked = n
print('Loaded %d operationplans in %.2f seconds' %
(cnt, time() - starttime))
def loadOperationPlans(self):
print('Importing operationplans...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, locked, source
FROM operationplan
WHERE owner_id IS NULL and quantity >= 0 %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], start=i[3], end=i[4],
locked=i[5], source=i[6]
).quantity = i[2]
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, locked, owner_id, source
FROM operationplan
WHERE owner_id IS NOT NULL and quantity >= 0 %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], start=i[3], end=i[4], quantity=i[2],
locked=i[5], source=i[7],
owner=frepple.operationplan(id=i[6])
).quantity = i[2]
print('Loaded %d operationplans in %.2f seconds' % (cnt, time() - starttime))
def loadSuboperations(self):
print('Importing suboperations...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT operation_id, suboperation_id, priority, effective_start, effective_end,
(select type
from operation
where suboperation.operation_id = operation.name) as type
FROM suboperation
WHERE priority >= 0 %s
ORDER BY operation_id, priority
''' % self.filter_and)
curopername = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[0] != curopername:
curopername = i[0]
curoper = frepple.operation(name=curopername)
sub = frepple.suboperation(
owner=curoper,
operation=frepple.operation(name=i[1]),
priority=i[2]
)
if i[3]:
sub.effective_start = i[3]
if i[4]:
sub.effective_end = i[4]
except Exception as e:
print("Error:", e)
print('Loaded %d suboperations in %.2f seconds' % (cnt, time() - starttime))
def loadSuboperations(cursor):
print('Importing suboperations...')
cnt = 0
starttime = time()
cursor.execute('''
SELECT operation_id, suboperation_id, priority, effective_start, effective_end, operation.type
FROM suboperation, operation
WHERE suboperation.operation_id = operation.name
AND priority >= 0
ORDER BY operation_id, priority
''')
curopername = None
for i, j, k, l, m, n in cursor.fetchall():
cnt += 1
try:
if i != curopername:
curopername = i
if n == 'alternate':
curoper = frepple.operation_alternate(name=curopername)
else:
curoper = frepple.operation_routing(name=curopername)
if isinstance(curoper,frepple.operation_routing):
curoper.addStep(frepple.operation(name=j))
else:
if l:
if m:
curoper.addAlternate(operation=frepple.operation(name=j),priority=k,effective_start=l,effective_end=m)
else:
curoper.addAlternate(operation=frepple.operation(name=j),priority=k,effective_start=l)
elif m:
curoper.addAlternate(operation=frepple.operation(name=j),priority=k,effective_end=m)
else:
curoper.addAlternate(operation=frepple.operation(name=j),priority=k)
except Exception as e: print("Error:", e)
print('Loaded %d suboperations in %.2f seconds' % (cnt, time() - starttime))
def loadFlows(cursor):
print('Importing flows...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
cursor.execute('''SELECT
operation_id, thebuffer_id, quantity, type, effective_start,
effective_end, name, priority, search
FROM flow
WHERE alternate IS NULL OR alternate = ''
ORDER BY operation_id, thebuffer_id
''')
curbufname = None
for i, j, k, l, m, n, o, p, q in cursor.fetchall():
cnt += 1
try:
if j != curbufname:
curbufname = j
curbuf = frepple.buffer(name=curbufname)
curflow = frepple.flow(operation=frepple.operation(name=i),
type="flow_%s" % l,
buffer=curbuf,
quantity=k)
if m: curflow.effective_start = m
if n: curflow.effective_end = n
if o: curflow.name = o
if p: curflow.priority = p
if q: curflow.search = q
except Exception as e:
print("Error:", e)
cursor.execute('''
SELECT
operation_id, thebuffer_id, quantity, type, effective_start,
effective_end, name, alternate, priority, search
FROM flow
WHERE alternate IS NOT NULL AND alternate <> ''
ORDER BY operation_id, thebuffer_id
''')
curbufname = None
for i, j, k, l, m, n, o, p, q, r in cursor.fetchall():
cnt += 1
try:
if j != curbufname:
curbufname = j
curbuf = frepple.buffer(name=curbufname)
curflow = frepple.flow(operation=frepple.operation(name=i),
type=l,
buffer=curbuf,
quantity=k)
if m: curflow.effective_start = m
if n: curflow.effective_end = n
if o: curflow.name = o
if p: curflow.alternate = p
if q: curflow.priority = q
if r: curflow.search = r
except Exception as e:
print("Error:", e)
print('Loaded %d flows in %.2f seconds' % (cnt, time() - starttime))
def loadLoads(cursor):
print('Importing loads...')
cnt = 0
starttime = time()
# Note: The sorting of the loads is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end, name,
priority, setup, search, skill_id
FROM resourceload
WHERE alternate IS NULL OR alternate = ''
ORDER BY operation_id, resource_id
''')
curresname = None
for i, j, k, l, m, n, o, p, q, r in cursor.fetchall():
cnt += 1
try:
if j != curresname:
curresname = j
curres = frepple.resource(name=curresname)
curload = frepple.load(operation=frepple.operation(name=i), resource=curres, quantity=k)
if l: curload.effective_start = l
if m: curload.effective_end = m
if n: curload.name = n
if o: curload.priority = o
if p: curload.setup = p
if q: curload.search = q
if r: curload.skill = frepple.skill(name=r)
except Exception as e: print("Error:", e)
cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end,
name, alternate, priority, setup, search, skill_id
FROM resourceload
WHERE alternate IS NOT NULL AND alternate <> ''
ORDER BY operation_id, resource_id
''')
curresname = None
for i, j, k, l, m, n, o, p, q, r, s in cursor.fetchall():
cnt += 1
try:
if j != curresname:
curresname = j
curres = frepple.resource(name=curresname)
curload = frepple.load(operation=frepple.operation(name=i), resource=curres, quantity=k)
if l: curload.effective_start = l
if m: curload.effective_end = m
if n: curload.name = n
if o: curload.alternate = o
if p: curload.priority = p
if q: curload.setup = q
if r: curload.search = r
if s: curload.skill = frepple.skill(name=s)
except Exception as e: print("Error:", e)
print('Loaded %d loads in %.2f seconds' % (cnt, time() - starttime))
def loadFlows(cursor):
print('Importing flows...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
cursor.execute('''SELECT
operation_id, thebuffer_id, quantity, type, effective_start,
effective_end, name, priority, search
FROM flow
WHERE alternate IS NULL OR alternate = ''
ORDER BY operation_id, thebuffer_id
''')
curbufname = None
for i, j, k, l, m, n, o, p, q in cursor.fetchall():
cnt += 1
try:
if j != curbufname:
curbufname = j
curbuf = frepple.buffer(name=curbufname)
curflow = frepple.flow(operation=frepple.operation(name=i), type="flow_%s" % l, buffer=curbuf, quantity=k)
if m: curflow.effective_start = m
if n: curflow.effective_end = n
if o: curflow.name = o
if p: curflow.priority = p
if q: curflow.search = q
except Exception as e: print("Error:", e)
cursor.execute('''
SELECT
operation_id, thebuffer_id, quantity, type, effective_start,
effective_end, name, alternate, priority, search
FROM flow
WHERE alternate IS NOT NULL AND alternate <> ''
ORDER BY operation_id, thebuffer_id
''')
curbufname = None
for i, j, k, l, m, n, o, p, q, r in cursor.fetchall():
cnt += 1
try:
if j != curbufname:
curbufname = j
curbuf = frepple.buffer(name=curbufname)
curflow = frepple.flow(operation=frepple.operation(name=i), type=l, buffer=curbuf, quantity=k)
if m: curflow.effective_start = m
if n: curflow.effective_end = n
if o: curflow.name = o
if p: curflow.alternate = p
if q: curflow.priority = q
if r: curflow.search = r
except Exception as e: print("Error:", e)
print('Loaded %d flows in %.2f seconds' % (cnt, time() - starttime))
def loadOperationPlans(self):
print('Importing operationplans...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, status, source
FROM operationplan
WHERE owner_id IS NULL and quantity >= 0 and status <> 'closed' %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1],
quantity=i[2],
source=i[6],
start=i[3],
end=i[4],
status=i[5])
if opplan.start <= frepple.settings.current:
# We assume that locked operationplans with a start date in the past
# have already consumed all materials.
# TODO Specifying this explicitly may be more appropriate
opplan.consume_material = False
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, status, owner_id, source
FROM operationplan
WHERE owner_id IS NOT NULL and quantity >= 0 and status <> 'closed' %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1],
quantity=i[2],
source=i[7],
owner=frepple.operationplan(id=i[6]),
start=i[3],
end=i[4],
status=i[5])
if opplan.start <= frepple.settings.current:
# We assume that locked operationplans with a start date in the past
# have already consumed all materials.
# TODO Specifying this explicitly may be more appropriate
opplan.consume_material = False
print('Loaded %d operationplans in %.2f seconds' %
(cnt, time() - starttime))
def loadDemand(cursor):
print('Importing demands...')
cnt = 0
starttime = time()
cursor.execute('''SELECT name, due, quantity, priority, item_id,
operation_id, customer_id, owner_id, minshipment, maxlateness,
category, subcategory
FROM demand
where status is null or status ='open' or status = 'quote' ''')
for i, j, k, l, m, n, o, p, q, r, s, t in cursor.fetchall():
cnt += 1
try:
x = frepple.demand(name=i,
due=j,
quantity=k,
priority=l,
item=frepple.item(name=m),
category=s,
subcategory=t)
if n: x.operation = frepple.operation(name=n)
if o: x.customer = frepple.customer(name=o)
if p: x.owner = frepple.demand(name=p)
if q: x.minshipment = q
if r != None: x.maxlateness = r
except Exception as e:
print("Error:", e)
print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadOperationMaterials(self):
print('Importing operation materials...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, item_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM operationmaterial %s
ORDER BY operation_id, item_id
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
curflow = frepple.flow(
operation=frepple.operation(name=i[0]),
item=frepple.item(name=i[1]),
quantity=i[2],
type="flow_%s" % i[3],
source=i[9]
)
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
print("Error:", e)
print('Loaded %d operation materials in %.2f seconds' % (cnt, time() - starttime))
# Check for operations where:
# - operation.item is still blank
# - they have a single operationmaterial item with quantity > 0
# If found we update
starttime = time()
cnt = 0
print('Auto-update operation items...')
for oper in frepple.operations():
if oper.hidden or oper.item:
continue
item = None
for fl in oper.flows:
if fl.quantity < 0 or fl.hidden:
continue
if item and item != fl.item:
item = None
break
else:
item = fl.item
if item:
cnt += 1
oper.item = item
print('Auto-update of %s operation items in %.2f seconds' % (cnt, time() - starttime))
def loadItems(self):
print('Importing items...')
cnt = 0
starttime = time()
attrs = [ f[0] for f in getAttributes(Item) ]
if attrs:
attrsql = ', %s' % ', '.join(attrs)
else:
attrsql = ''
self.cursor.execute('''
SELECT
name, description, operation_id, owner_id,
price, category, subcategory, source %s
FROM item %s
''' % (attrsql, self.filter_where))
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i[0], description=i[1], category=i[5], subcategory=i[6], source=i[7])
if i[2]:
x.operation = frepple.operation(name=i[2])
if i[3]:
x.owner = frepple.item(name=i[3])
if i[4]:
x.price = i[4]
idx = 8
for a in attrs:
setattr(x, a, i[idx])
idx += 1
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def loadDemand(self):
print('Importing demands...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
name, due, quantity, priority, item_id,
operation_id, customer_id, owner_id, minshipment, maxlateness,
category, subcategory, source, location_id, status
FROM demand
WHERE (status IS NULL OR status ='open' OR status = 'quote') %s
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.demand(
name=i[0], due=i[1], quantity=i[2], priority=i[3], status=i[14],
item=frepple.item(name=i[4]), category=i[10], subcategory=i[11],
source=i[12]
)
if i[5]:
x.operation = frepple.operation(name=i[5])
if i[6]:
x.customer = frepple.customer(name=i[6])
if i[7]:
x.owner = frepple.demand(name=i[7])
if i[8]:
x.minshipment = i[8]
if i[9] is not None:
x.maxlateness = i[9].total_seconds()
if i[13]:
x.location = frepple.location(name=i[13])
except Exception as e:
print("Error:", e)
print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadOperationMaterials(self):
print('Importing operation materials...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, item_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM operationmaterial %s
ORDER BY operation_id, item_id
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
curflow = frepple.flow(
operation=frepple.operation(name=i[0]),
item=frepple.item(name=i[1]),
quantity=i[2],
type="flow_%s" % i[3],
source=i[9]
)
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
print("Error:", e)
print('Loaded %d operation materials in %.2f seconds' % (cnt, time() - starttime))
# Check for operations where:
# - operation.item is still blank
# - they have a single operationmaterial item with quantity > 0
# If found we update
starttime = time()
cnt = 0
print('Auto-update operation items...')
for oper in frepple.operations():
if oper.hidden or oper.item:
continue
item = None
for fl in oper.flows:
if fl.quantity < 0 or fl.hidden:
continue
if item and item != fl.item:
item = None
break
else:
item = fl.item
if item:
cnt += 1
oper.item = item
print('Auto-update of %s operation items in %.2f seconds' % (cnt, time() - starttime))
def loadDemand(self):
print('Importing demands...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
name, due, quantity, priority, item_id,
operation_id, customer_id, owner_id, minshipment, maxlateness,
category, subcategory, source, location_id, status
FROM demand
WHERE (status IS NULL OR status ='open' OR status = 'quote') %s
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.demand(
name=i[0], due=i[1], quantity=i[2], priority=i[3], status=i[14],
item=frepple.item(name=i[4]), category=i[10], subcategory=i[11],
source=i[12]
)
if i[5]:
x.operation = frepple.operation(name=i[5])
if i[6]:
x.customer = frepple.customer(name=i[6])
if i[7]:
x.owner = frepple.demand(name=i[7])
if i[8]:
x.minshipment = i[8]
if i[9] is not None:
x.maxlateness = i[9]
if i[13]:
x.location = frepple.location(name=i[13])
except Exception as e:
print("Error:", e)
print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(self):
print('Importing buffers...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT name, description, location_id, item_id, onhand,
minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory,
max_inventory, min_interval, max_interval, size_minimum,
size_multiple, size_maximum, fence, carrying_cost,
category, subcategory, source
FROM buffer %s
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
if i[8] == "procure":
b = frepple.buffer_procure(
name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4],
category=i[19], subcategory=i[20], source=i[21]
)
if i[9]:
b.leadtime = i[9]
if i[10]:
b.mininventory = i[10]
if i[11]:
b.maxinventory = i[11]
if i[14]:
b.size_minimum = i[14]
if i[15]:
b.size_multiple = i[15]
if i[16]:
b.size_maximum = i[16]
if i[17]:
b.fence = i[17]
elif i[8] == "infinite":
b = frepple.buffer_infinite(
name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4],
category=i[19], subcategory=i[20], source=i[21]
)
elif not i[8] or i[8] == "default":
b = frepple.buffer(
name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4],
category=i[19], subcategory=i[20], source=i[21]
)
else:
raise ValueError("Buffer type '%s' not recognized" % i[8])
if i[2]:
b.location = frepple.location(name=i[2])
if i[5]:
b.minimum = i[5]
if i[6]:
b.minimum_calendar = frepple.calendar(name=i[6])
if i[7]:
b.producing = frepple.operation(name=i[7])
if i[18]:
b.carrying_cost = i[18]
if i[12]:
b.mininterval = i[12]
if i[13]:
b.maxinterval = i[13]
print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadItems(self):
print('Importing items...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
name, description, operation_id, owner_id,
price, category, subcategory, source
FROM item %s
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i[0],
description=i[1],
category=i[5],
subcategory=i[6],
source=i[7])
if i[2]:
x.operation = frepple.operation(name=i[2])
if i[3]:
x.owner = frepple.item(name=i[3])
if i[4]:
x.price = i[4]
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def loadSuboperations(self):
print('Importing suboperations...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT operation_id, suboperation_id, priority, effective_start, effective_end,
(select type
from operation
where suboperation.operation_id = operation.name) as type
FROM suboperation
WHERE priority >= 0 %s
ORDER BY operation_id, priority
''' % self.filter_and)
curopername = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[0] != curopername:
curopername = i[0]
curoper = frepple.operation(name=curopername)
if isinstance(curoper, frepple.operation_routing):
curoper.addStep(frepple.operation(name=i[1]))
elif isinstance(curoper, frepple.operation_alternate):
if i[3]:
if i[4]:
curoper.addAlternate(operation=frepple.operation(name=i[1]), priority=i[2], effective_start=i[3], effective_end=i[4])
else:
curoper.addAlternate(operation=frepple.operation(name=i[1]), priority=i[2], effective_start=i[3])
elif i[4]:
curoper.addAlternate(operation=frepple.operation(name=i[1]), priority=i[2], effective_end=i[4])
else:
curoper.addAlternate(operation=frepple.operation(name=i[1]), priority=i[2])
elif isinstance(curoper, frepple.operation_split):
if i[3]:
if i[4]:
curoper.addAlternate(operation=frepple.operation(name=i[1]), percent=i[2], effective_start=i[3], effective_end=i[4])
else:
curoper.addAlternate(operation=frepple.operation(name=i[1]), percent=i[2], effective_start=i[3])
elif i[4]:
curoper.addAlternate(operation=frepple.operation(name=i[1]), percent=i[2], effective_end=i[4])
else:
curoper.addAlternate(operation=frepple.operation(name=i[1]), percent=i[2])
else:
print("Error: Operation '%s' can't have suboperations" % curopername)
except Exception as e:
print("Error:", e)
print('Loaded %d suboperations in %.2f seconds' % (cnt, time() - starttime))
def loadSuboperations(cursor):
print('Importing suboperations...')
cnt = 0
starttime = time()
cursor.execute('''
SELECT operation_id, suboperation_id, priority, effective_start, effective_end, operation.type
FROM suboperation, operation
WHERE suboperation.operation_id = operation.name
AND priority >= 0
ORDER BY operation_id, priority
''')
curopername = None
for i, j, k, l, m, n in cursor.fetchall():
cnt += 1
try:
if i != curopername:
curopername = i
if n == 'alternate':
curoper = frepple.operation_alternate(name=curopername)
else:
curoper = frepple.operation_routing(name=curopername)
if isinstance(curoper, frepple.operation_routing):
curoper.addStep(frepple.operation(name=j))
else:
if l:
if m:
curoper.addAlternate(
operation=frepple.operation(name=j),
priority=k,
effective_start=l,
effective_end=m)
else:
curoper.addAlternate(
operation=frepple.operation(name=j),
priority=k,
effective_start=l)
elif m:
curoper.addAlternate(operation=frepple.operation(name=j),
priority=k,
effective_end=m)
else:
curoper.addAlternate(operation=frepple.operation(name=j),
priority=k)
except Exception as e:
print("Error:", e)
print('Loaded %d suboperations in %.2f seconds' %
(cnt, time() - starttime))
def loadBuffers(self):
print('Importing buffers...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT name, description, location_id, item_id, onhand,
minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory,
max_inventory, min_interval, max_interval, size_minimum,
size_multiple, size_maximum, fence, category, subcategory, source
FROM buffer %s
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
if i[8] == "procure":
b = frepple.buffer_procure(
name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4],
category=i[18], subcategory=i[19], source=i[20]
)
if i[9]:
b.leadtime = i[9]
if i[10]:
b.mininventory = i[10]
if i[11]:
b.maxinventory = i[11]
if i[14]:
b.size_minimum = i[14]
if i[15]:
b.size_multiple = i[15]
if i[16]:
b.size_maximum = i[16]
if i[17]:
b.fence = i[17]
elif i[8] == "infinite":
b = frepple.buffer_infinite(
name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4],
category=i[18], subcategory=i[19], source=i[20]
)
elif not i[8] or i[8] == "default":
b = frepple.buffer(
name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4],
category=i[18], subcategory=i[19], source=i[20]
)
else:
raise ValueError("Buffer type '%s' not recognized" % i[8])
if i[20] == 'tool':
b.tool = True
if i[2]:
b.location = frepple.location(name=i[2])
if i[5]:
b.minimum = i[5]
if i[6]:
b.minimum_calendar = frepple.calendar(name=i[6])
if i[7]:
b.producing = frepple.operation(name=i[7])
if i[12]:
b.mininterval = i[12]
if i[13]:
b.maxinterval = i[13]
print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadOperationPlans(self):
print('Importing operationplans...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, status, source
FROM operationplan
WHERE owner_id IS NULL and quantity >= 0 and status <> 'closed' %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[6],
start=i[3], end=i[4], status=i[5]
)
if opplan.start <= frepple.settings.current:
# We assume that locked operationplans with a start date in the past
# have already consumed all materials.
# TODO Specifying this explicitly may be more appropriate
opplan.consume_material = False
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, status, owner_id, source
FROM operationplan
WHERE owner_id IS NOT NULL and quantity >= 0 and status <> 'closed' %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[7],
owner=frepple.operationplan(id=i[6]),
start=i[3], end=i[4], status=i[5]
)
if opplan.start <= frepple.settings.current:
# We assume that locked operationplans with a start date in the past
# have already consumed all materials.
# TODO Specifying this explicitly may be more appropriate
opplan.consume_material=False
print('Loaded %d operationplans in %.2f seconds' % (cnt, time() - starttime))
def loadOperationPlans(self):
print('Importing operationplans...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, status, source
FROM operationplan
WHERE owner_id IS NULL and quantity >= 0 and status <> 'closed' %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[6]
)
if i[3]:
opplan.start = i[3]
if i[4]:
opplan.end = i[4]
opplan.status = i[5]
self.cursor.execute('''
SELECT
operation_id, id, quantity, startdate, enddate, status, owner_id, source
FROM operationplan
WHERE owner_id IS NOT NULL and quantity >= 0 and status <> 'closed' %s
ORDER BY id ASC
''' % self.filter_and)
for i in self.cursor.fetchall():
cnt += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[7],
owner=frepple.operationplan(id=i[6])
)
if i[3]:
opplan.start = i[3]
if i[4]:
opplan.end = i[4]
opplan.status = i[5]
print('Loaded %d operationplans in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
if cls.filter:
filter_and = "and %s " % cls.filter
filter_where = "where %s " % cls.filter
else:
filter_and = ""
filter_where = ""
with connections[database].chunked_cursor() as cursor:
cnt = 0
starttime = time()
cursor.execute('''
SELECT operation_id, suboperation_id, priority, effective_start, effective_end,
(SELECT type
from operation
where suboperation.operation_id = operation.name) as type
FROM suboperation
WHERE priority >= 0 %s
ORDER BY operation_id, priority
''' % filter_and)
curopername = None
for i in cursor:
cnt += 1
try:
if i[0] != curopername:
curopername = i[0]
curoper = frepple.operation(name=curopername)
sub = frepple.suboperation(
owner=curoper,
operation=frepple.operation(name=i[1]),
priority=i[2]
)
if i[3]:
sub.effective_start = i[3]
if i[4]:
sub.effective_end = i[4]
except Exception as e:
logger.error("**** %s ****" % e)
logger.info('Loaded %d suboperations in %.2f seconds' % (cnt, time() - starttime))
def loadOperationPlans(cursor):
print('Importing operationplans...')
cnt = 0
starttime = time()
cursor.execute('''SELECT operation_id, id, quantity, startdate, enddate, locked
FROM operationplan
where owner_id is null
order by id asc''')
for i, j, k, l, m, n in cursor.fetchall():
cnt += 1
frepple.operationplan(operation=frepple.operation(name=i),
id=j, quantity=k, start=l, end=m).locked = n
cursor.execute('''SELECT operation_id, id, quantity, startdate, enddate, locked, owner_id
FROM operationplan
where owner_id is not null
order by id asc''')
for i, j, k, l, m, n, o in cursor.fetchall():
cnt += 1
frepple.operationplan(operation=frepple.operation(name=i),
id=j, quantity=k, start=l, end=m, owner=frepple.operationplan(id=o)).locked = n
print('Loaded %d operationplans in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(cursor):
print('Importing buffers...')
cnt = 0
starttime = time()
cursor.execute('''SELECT name, description, location_id, item_id, onhand,
minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory,
max_inventory, min_interval, max_interval, size_minimum,
size_multiple, size_maximum, fence, carrying_cost,
category, subcategory FROM buffer''')
for i, j, k, l, m, t, n, o, q, f1, f2, f3, f4, f5, f6, f7, f8, f9, p, r, s in cursor.fetchall(
):
cnt += 1
if q == "procure":
b = frepple.buffer_procure(name=i,
description=j,
item=frepple.item(name=l),
onhand=m,
category=r,
subcategory=s)
if f1: b.leadtime = f1
if f2: b.mininventory = f2
if f3: b.maxinventory = f3
if f4: b.mininterval = f4
if f5: b.maxinterval = f5
if f6: b.size_minimum = f6
if f7: b.size_multiple = f7
if f8: b.size_maximum = f8
if f9: b.fence = f9
elif q == "infinite":
b = frepple.buffer_infinite(name=i,
description=j,
item=frepple.item(name=l),
onhand=m,
category=r,
subcategory=s)
elif not q or q == "default":
b = frepple.buffer(name=i,
description=j,
item=frepple.item(name=l),
onhand=m,
category=r,
subcategory=s)
else:
raise ValueError("Buffer type '%s' not recognized" % q)
if k: b.location = frepple.location(name=k)
if t: b.minimum = t
if n: b.minimum_calendar = frepple.calendar(name=n)
if o: b.producing = frepple.operation(name=o)
if p: b.carrying_cost = p
print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadLoads(self):
print('Importing loads...')
cnt = 0
starttime = time()
# Note: The sorting of the loads is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end, name,
priority, setup, search, skill_id, source
FROM resourceload %s
ORDER BY operation_id, resource_id
''' % self.filter_where)
curresname = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[1] != curresname:
curresname = i[1]
curres = frepple.resource(name=curresname)
curload = frepple.load(operation=frepple.operation(name=i[0]),
resource=curres,
quantity=i[2],
source=i[10])
if i[3]:
curload.effective_start = i[3]
if i[4]:
curload.effective_end = i[4]
if i[5]:
curload.name = i[5]
if i[6]:
curload.priority = i[6]
if i[7]:
curload.setup = i[7]
if i[8]:
curload.search = i[8]
if i[9]:
curload.skill = frepple.skill(name=i[9])
except Exception as e:
print("Error:", e)
self.cursor.execute('''
SELECT count(*)
FROM resourceload
WHERE alternate IS NOT NULL AND alternate <> ''
''')
if self.cursor.fetchone()[0]:
raise ValueError(
"Load.alternate field is not used any longer. Use only load.name"
)
print('Loaded %d loads in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
if cls.filter:
filter_and = "and %s " % cls.filter
filter_where = "where %s " % cls.filter
else:
filter_and = ""
filter_where = ""
with connections[database].chunked_cursor() as cursor:
cnt = 0
starttime = time()
# Note: The sorting of the loads is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end, name,
priority, setup, search, skill_id, source
FROM operationresource %s
ORDER BY operation_id, resource_id
''' % filter_where)
for i in cursor:
cnt += 1
try:
curload = frepple.load(
operation=frepple.operation(name=i[0]),
resource=frepple.resource(name=i[1]),
quantity=i[2],
source=i[10]
)
if i[3]:
curload.effective_start = i[3]
if i[4]:
curload.effective_end = i[4]
if i[5]:
curload.name = i[5]
if i[6]:
curload.priority = i[6]
if i[7]:
curload.setup = i[7]
if i[8]:
curload.search = i[8]
if i[9]:
curload.skill = frepple.skill(name=i[9])
except Exception as e:
logger.error("**** %s ****" % e)
logger.info('Loaded %d resource loads in %.2f seconds' % (cnt, time() - starttime))
def loadFlows(self):
print('Importing flows...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, thebuffer_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM flow %s
ORDER BY operation_id, thebuffer_id
''' % self.filter_where)
curbufname = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[1] != curbufname:
curbufname = i[1]
curbuf = frepple.buffer(name=curbufname)
curflow = frepple.flow(operation=frepple.operation(name=i[0]),
type="flow_%s" % i[3],
buffer=curbuf,
quantity=i[2],
source=i[9])
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
print("Error:", e)
self.cursor.execute('''
SELECT count(*)
FROM flow
WHERE alternate IS NOT NULL AND alternate <> ''
''')
if self.cursor.fetchone()[0]:
raise ValueError(
"Flow.alternate field is not used any longer. Use only flow.name"
)
print('Loaded %d flows in %.2f seconds' % (cnt, time() - starttime))
def loadItems(cursor):
print('Importing items...')
cnt = 0
starttime = time()
cursor.execute('''SELECT
name, description, operation_id, owner_id,
price, category, subcategory
FROM item''')
for i,j,k,l,m,n,o in cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i, description=j, category=n, subcategory=o)
if k: x.operation = frepple.operation(name=k)
if l: x.owner = frepple.item(name=l)
if m: x.price = m
except Exception as e: print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def loadLoads(self):
print('Importing loads...')
cnt = 0
starttime = time()
# Note: The sorting of the loads is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end, name,
priority, setup, search, skill_id, source
FROM resourceload %s
ORDER BY operation_id, resource_id
''' % self.filter_where)
curresname = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[1] != curresname:
curresname = i[1]
curres = frepple.resource(name=curresname)
curload = frepple.load(operation=frepple.operation(name=i[0]), resource=curres, quantity=i[2], source=i[10])
if i[3]:
curload.effective_start = i[3]
if i[4]:
curload.effective_end = i[4]
if i[5]:
curload.name = i[5]
if i[6]:
curload.priority = i[6]
if i[7]:
curload.setup = i[7]
if i[8]:
curload.search = i[8]
if i[9]:
curload.skill = frepple.skill(name=i[9])
except Exception as e:
print("Error:", e)
self.cursor.execute('''
SELECT count(*)
FROM resourceload
WHERE alternate IS NOT NULL AND alternate <> ''
''')
if self.cursor.fetchone()[0]:
raise ValueError("Load.alternate field is not used any longer. Use only load.name")
print('Loaded %d loads in %.2f seconds' % (cnt, time() - starttime))
def loadItems(cursor):
print('Importing items...')
cnt = 0
starttime = time()
cursor.execute('''SELECT
name, description, operation_id, owner_id,
price, category, subcategory
FROM item''')
for i, j, k, l, m, n, o in cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i, description=j, category=n, subcategory=o)
if k: x.operation = frepple.operation(name=k)
if l: x.owner = frepple.item(name=l)
if m: x.price = m
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
if cls.filter:
filter_and = "and %s " % cls.filter
filter_where = "where %s " % cls.filter
else:
filter_and = ""
filter_where = ""
with connections[database].chunked_cursor() as cursor:
cnt = 0
starttime = time()
cursor.execute('''
SELECT
name, due, quantity, priority, item_id,
operation_id, customer_id, owner_id, minshipment, maxlateness,
category, subcategory, source, location_id, status
FROM demand
WHERE (status IS NULL OR status ='open' OR status = 'quote') %s
''' % filter_and)
for i in cursor:
cnt += 1
try:
x = frepple.demand(
name=i[0], due=i[1], quantity=i[2], priority=i[3], status=i[14],
item=frepple.item(name=i[4]), category=i[10], subcategory=i[11],
source=i[12]
)
if i[5]:
x.operation = frepple.operation(name=i[5])
if i[6]:
x.customer = frepple.customer(name=i[6])
if i[7]:
x.owner = frepple.demand(name=i[7])
if i[8] is not None:
x.minshipment = i[8]
if i[9] is not None:
x.maxlateness = i[9].total_seconds()
if i[13]:
x.location = frepple.location(name=i[13])
except Exception as e:
logger.error("**** %s ****" % e)
logger.info('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(cursor):
print('Importing buffers...')
cnt = 0
starttime = time()
cursor.execute('''SELECT name, description, location_id, item_id, onhand,
minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory,
max_inventory, min_interval, max_interval, size_minimum,
size_multiple, size_maximum, fence, carrying_cost,
category, subcategory FROM buffer''')
for i,j,k,l,m,t,n,o,q,f1,f2,f3,f4,f5,f6,f7,f8,f9,p,r,s in cursor.fetchall():
cnt += 1
if q == "procure":
b = frepple.buffer_procure(
name=i, description=j, item=frepple.item(name=l), onhand=m,
category=r, subcategory=s
)
if f1: b.leadtime = f1
if f2: b.mininventory = f2
if f3: b.maxinventory = f3
if f4: b.mininterval = f4
if f5: b.maxinterval = f5
if f6: b.size_minimum = f6
if f7: b.size_multiple = f7
if f8: b.size_maximum = f8
if f9: b.fence = f9
elif q == "infinite":
b = frepple.buffer_infinite(
name=i, description=j, item=frepple.item(name=l), onhand=m,
category=r, subcategory=s
)
elif not q or q == "default":
b = frepple.buffer(
name=i, description=j, item=frepple.item(name=l), onhand=m,
category=r, subcategory=s
)
else:
raise ValueError("Buffer type '%s' not recognized" % q)
if k: b.location = frepple.location(name=k)
if t: b.minimum = t
if n: b.minimum_calendar = frepple.calendar(name=n)
if o: b.producing = frepple.operation(name=o)
if p: b.carrying_cost = p
print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadDemand(cursor):
print('Importing demands...')
cnt = 0
starttime = time()
cursor.execute('''SELECT name, due, quantity, priority, item_id,
operation_id, customer_id, owner_id, minshipment, maxlateness,
category, subcategory
FROM demand
where status is null or status ='open' ''')
for i,j,k,l,m,n,o,p,q,r,s,t in cursor.fetchall():
cnt += 1
try:
x = frepple.demand( name=i, due=j, quantity=k, priority=l, item=frepple.item(name=m),category=s,subcategory=t)
if n: x.operation = frepple.operation(name=n)
if o: x.customer = frepple.customer(name=o)
if p: x.owner = frepple.demand(name=p)
if q: x.minshipment = q
if r != None: x.maxlateness = r
except Exception as e: print("Error:", e)
print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadFlows(self):
print('Importing flows...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, thebuffer_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM flow %s
ORDER BY operation_id, thebuffer_id
''' % self.filter_where)
curbufname = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[1] != curbufname:
curbufname = i[1]
curbuf = frepple.buffer(name=curbufname)
curflow = frepple.flow(operation=frepple.operation(name=i[0]), type="flow_%s" % i[3], buffer=curbuf, quantity=i[2], source=i[9])
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
print("Error:", e)
self.cursor.execute('''
SELECT count(*)
FROM flow
WHERE alternate IS NOT NULL AND alternate <> ''
''')
if self.cursor.fetchone()[0]:
raise ValueError("Flow.alternate field is not used any longer. Use only flow.name")
print('Loaded %d flows in %.2f seconds' % (cnt, time() - starttime))
def loadOperationResources(self):
print('Importing operation resources...')
cnt = 0
starttime = time()
# Note: The sorting of the loads is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end, name,
priority, setup, search, skill_id, source
FROM operationresource %s
ORDER BY operation_id, resource_id
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
curload = frepple.load(
operation=frepple.operation(name=i[0]),
resource=frepple.resource(name=i[1]),
quantity=i[2],
source=i[10]
)
if i[3]:
curload.effective_start = i[3]
if i[4]:
curload.effective_end = i[4]
if i[5]:
curload.name = i[5]
if i[6]:
curload.priority = i[6]
if i[7]:
curload.setup = i[7]
if i[8]:
curload.search = i[8]
if i[9]:
curload.skill = frepple.skill(name=i[9])
except Exception as e:
print("Error:", e)
print('Loaded %d resource loads in %.2f seconds' % (cnt, time() - starttime))
def loadOperationResources(self):
print('Importing operation resources...')
cnt = 0
starttime = time()
# Note: The sorting of the loads is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end, name,
priority, setup, search, skill_id, source
FROM operationresource %s
ORDER BY operation_id, resource_id
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
curload = frepple.load(
operation=frepple.operation(name=i[0]),
resource=frepple.resource(name=i[1]),
quantity=i[2],
source=i[10]
)
if i[3]:
curload.effective_start = i[3]
if i[4]:
curload.effective_end = i[4]
if i[5]:
curload.name = i[5]
if i[6]:
curload.priority = i[6]
if i[7]:
curload.setup = i[7]
if i[8]:
curload.search = i[8]
if i[9]:
curload.skill = frepple.skill(name=i[9])
except Exception as e:
print("Error:", e)
print('Loaded %d resource loads in %.2f seconds' % (cnt, time() - starttime))
def loadItems(self):
print('Importing items...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
name, description, operation_id, owner_id,
price, category, subcategory, source
FROM item %s
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i[0], description=i[1], category=i[5], subcategory=i[6], source=i[7])
if i[2]:
x.operation = frepple.operation(name=i[2])
if i[3]:
x.owner = frepple.item(name=i[3])
if i[4]:
x.price = i[4]
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def loadOperationPlans(self): # TODO if we are going to replan anyway, we can skip loading the proposed operationplans
print('Importing operationplans...')
if 'supply' in os.environ:
confirmed_filter = " and operationplan.status = 'confirmed'"
else:
confirmed_filter = ""
cnt_mo = 0
cnt_po = 0
cnt_do = 0
cnt_dlvr = 0
starttime = time()
self.cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.source,
operationplan.type, operationplan.origin_id, operationplan.destination_id, operationplan.supplier_id,
operationplan.item_id, operationplan.location_id,
reference, coalesce(dmd.name, null)
FROM operationplan
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.owner_id IS NULL
and operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type in ('PO', 'MO', 'DO', 'DLVR')
ORDER BY operationplan.id ASC
''' % (self.filter_and, confirmed_filter))
for i in self.cursor.fetchall():
try:
if i[7] == 'MO':
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]), id=i[1],
quantity=i[2], source=i[6], start=i[3], end=i[4],
status=i[5], reference=i[13]
)
elif i[7] == 'PO':
cnt_po += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]), ordertype=i[7],
id=i[1], reference=i[12],
item=frepple.item(name=i[11]) if i[11] else None,
supplier=frepple.supplier(name=i[10]) if i[10] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
elif i[7] == 'DO':
cnt_do += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[9]) if i[9] else None,
id=i[1], reference=i[12], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
elif i[7] == 'DLVR':
cnt_dlvr += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]) if i[12] else None,
id=i[1], reference=i[12], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
demand=frepple.demand(name=i[14]) if i[14] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
opplan = None
else:
print("Warning: unhandled operationplan type '%s'" % i[7])
continue
if i[14] and opplan:
opplan.demand = frepple.demand(name=i[14])
except Exception as e:
print("Error:", e)
self.cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status,
operationplan.owner_id, operationplan.source, coalesce(dmd.name, null)
FROM operationplan
INNER JOIN (select id
from operationplan
) opplan_parent
on operationplan.owner_id = opplan_parent.id
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type = 'MO'
ORDER BY operationplan.id ASC
''' % (self.filter_and, confirmed_filter))
for i in self.cursor.fetchall():
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[7],
owner=frepple.operationplan(id=i[6]),
start=i[3], end=i[4], status=i[5]
)
if i[8]:
opplan.demand = frepple.demand(name=i[8])
print('Loaded %d manufacturing orders, %d purchase orders, %d distribution orders and %s deliveries in %.2f seconds' % (cnt_mo, cnt_po, cnt_do, cnt_dlvr, time() - starttime))
# Assure the operationplan ids will be unique.
# We call this method only at the end, as calling it earlier gives a slower
# performance to load operationplans
self.cursor.execute('''
select coalesce(max(id),1) + 1 as max_id
from operationplan
''')
d = self.cursor.fetchone()
frepple.settings.id = d[0]
<quantity>-1</quantity>
</flow>
<flow xsi:type="flow_end">
<operation name="make or buy item" />
<buffer name="end item" />
<quantity>1</quantity>
</flow>
</flows>
</plan>
"""
)
###
print("\nCreating operationplans")
opplan = frepple.operationplan(
operation=frepple.operation(name="make item"), quantity=9, end=datetime.datetime(2011, 1, 1)
)
opplan.locked = True
###
print("\nCreating items")
item = frepple.item(name="end item", operation=shipoper)
itemlist = [frepple.item(name="item %d" % i) for i in range(10)]
###
print("\nTesting the comparison operator")
print("makeoper < shipoper", makeoper < shipoper)
print("shipoper < makeoper", shipoper < makeoper)
print("shipoper != makeoper", shipoper != makeoper)
print("shipoper == makeoper", shipoper == makeoper)
print("shipoper == shipoper", shipoper == shipoper)
<operation name="delivery end item" />
<buffer name="end item" />
<quantity>-1</quantity>
</flow>
<flow xsi:type="flow_end">
<operation name="make or buy item" />
<buffer name="end item" />
<quantity>1</quantity>
</flow>
</flows>
</plan>
''')
###
print("\nCreating operationplans")
opplan = frepple.operationplan(operation=frepple.operation(name="make item"),
quantity=9,
end=datetime.datetime(2011, 1, 1))
opplan.status = 'confirmed'
###
print("\nCreating items")
item = frepple.item(name="end item")
itemlist = [frepple.item(name="item %d" % i) for i in range(10)]
###
print("\nTesting the comparison operator")
print("makeoper < shipoper", makeoper < shipoper)
print("shipoper < makeoper", shipoper < makeoper)
print("shipoper != makeoper", shipoper != makeoper)
print("shipoper == makeoper", shipoper == makeoper)
def loadLoads(cursor):
print('Importing loads...')
cnt = 0
starttime = time()
# Note: The sorting of the loads is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end, name,
priority, setup, search, skill_id
FROM resourceload
WHERE alternate IS NULL OR alternate = ''
ORDER BY operation_id, resource_id
''')
curresname = None
for i, j, k, l, m, n, o, p, q, r in cursor.fetchall():
cnt += 1
try:
if j != curresname:
curresname = j
curres = frepple.resource(name=curresname)
curload = frepple.load(operation=frepple.operation(name=i),
resource=curres,
quantity=k)
if l: curload.effective_start = l
if m: curload.effective_end = m
if n: curload.name = n
if o: curload.priority = o
if p: curload.setup = p
if q: curload.search = q
if r: curload.skill = frepple.skill(name=r)
except Exception as e:
print("Error:", e)
cursor.execute('''
SELECT
operation_id, resource_id, quantity, effective_start, effective_end,
name, alternate, priority, setup, search, skill_id
FROM resourceload
WHERE alternate IS NOT NULL AND alternate <> ''
ORDER BY operation_id, resource_id
''')
curresname = None
for i, j, k, l, m, n, o, p, q, r, s in cursor.fetchall():
cnt += 1
try:
if j != curresname:
curresname = j
curres = frepple.resource(name=curresname)
curload = frepple.load(operation=frepple.operation(name=i),
resource=curres,
quantity=k)
if l: curload.effective_start = l
if m: curload.effective_end = m
if n: curload.name = n
if o: curload.alternate = o
if p: curload.priority = p
if q: curload.setup = q
if r: curload.search = r
if s: curload.skill = frepple.skill(name=s)
except Exception as e:
print("Error:", e)
print('Loaded %d loads in %.2f seconds' % (cnt, time() - starttime))
<buffer name="end item" />
<quantity>-1</quantity>
</flow>
<flow xsi:type="flow_end">
<operation name="make or buy item" />
<buffer name="end item" />
<quantity>1</quantity>
</flow>
</flows>
</plan>
''')
###
print("\nCreating operationplans")
opplan = frepple.operationplan(
operation = frepple.operation(name="make item"),
quantity = 9,
end = datetime.datetime(2011,1,1)
)
opplan.status = 'confirmed'
###
print("\nCreating items")
item = frepple.item(name="end item")
itemlist = [ frepple.item(name="item %d" % i) for i in range(10) ]
###
print("\nTesting the comparison operator")
print("makeoper < shipoper", makeoper < shipoper)
print("shipoper < makeoper", shipoper < makeoper)
print("shipoper != makeoper", shipoper != makeoper)
def loadOperationPlans(self): # TODO if we are going to replan anyway, we can skip loading the proposed operationplans
print('Importing operationplans...')
if 'supply' in os.environ:
confirmed_filter = " and operationplan.status = 'confirmed'"
else:
confirmed_filter = ""
cnt_mo = 0
cnt_po = 0
cnt_do = 0
cnt_dlvr = 0
starttime = time()
self.cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.source,
operationplan.type, operationplan.origin_id, operationplan.destination_id, operationplan.supplier_id,
operationplan.item_id, operationplan.location_id,
reference, coalesce(dmd.name, null)
FROM operationplan
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.owner_id IS NULL
and operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type in ('PO', 'MO', 'DO', 'DLVR')
ORDER BY operationplan.id ASC
''' % (self.filter_and, confirmed_filter))
for i in self.cursor.fetchall():
try:
if i[7] == 'MO':
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]), id=i[1],
quantity=i[2], source=i[6], start=i[3], end=i[4],
status=i[5], reference=i[13]
)
elif i[7] == 'PO':
cnt_po += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]), ordertype=i[7],
id=i[1], reference=i[12],
item=frepple.item(name=i[11]) if i[11] else None,
supplier=frepple.supplier(name=i[10]) if i[10] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
elif i[7] == 'DO':
cnt_do += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[9]) if i[9] else None,
id=i[1], reference=i[12], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
elif i[7] == 'DLVR':
cnt_dlvr += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]) if i[12] else None,
id=i[1], reference=i[12], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
demand=frepple.demand(name=i[14]) if i[14] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
opplan = None
else:
print("Warning: unhandled operationplan type '%s'" % i[7])
continue
if i[14] and opplan:
opplan.demand = frepple.demand(name=i[14])
except Exception as e:
print("Error:", e)
self.cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status,
operationplan.owner_id, operationplan.source, coalesce(dmd.name, null)
FROM operationplan
INNER JOIN (select id
from operationplan
) opplan_parent
on operationplan.owner_id = opplan_parent.id
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type = 'MO'
ORDER BY operationplan.id ASC
''' % (self.filter_and, confirmed_filter))
for i in self.cursor.fetchall():
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[7],
owner=frepple.operationplan(id=i[6]),
start=i[3], end=i[4], status=i[5]
)
if i[8]:
opplan.demand = frepple.demand(name=i[8])
print('Loaded %d manufacturing orders, %d purchase orders, %d distribution orders and %s deliveries in %.2f seconds' % (cnt_mo, cnt_po, cnt_do, cnt_dlvr, time() - starttime))
# Assure the operationplan ids will be unique.
# We call this method only at the end, as calling it earlier gives a slower
# performance to load operationplans
self.cursor.execute('''
select coalesce(max(id),1) + 1 as max_id
from operationplan
''')
d = self.cursor.fetchone()
frepple.settings.id = d[0]
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
if cls.filter:
filter_and = "and %s " % cls.filter
filter_where = "where %s " % cls.filter
else:
filter_and = ""
filter_where = ""
with connections[database].chunked_cursor() as cursor:
if 'supply' in os.environ:
confirmed_filter = " and operationplan.status in ('confirmed', 'approved')"
create_flag = True
else:
confirmed_filter = ""
create_flag = False
cnt_mo = 0
cnt_po = 0
cnt_do = 0
cnt_dlvr = 0
starttime = time()
cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.source,
operationplan.type, operationplan.origin_id, operationplan.destination_id, operationplan.supplier_id,
operationplan.item_id, operationplan.location_id,
reference, coalesce(dmd.name, null)
FROM operationplan
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.owner_id IS NULL
and operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type in ('PO', 'MO', 'DO', 'DLVR')
ORDER BY operationplan.id ASC
''' % (filter_and, confirmed_filter))
for i in cursor:
try:
if i[7] == 'MO':
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]), id=i[1],
quantity=i[2], source=i[6], start=i[3], end=i[4],
status=i[5], reference=i[13], create=create_flag
)
elif i[7] == 'PO':
cnt_po += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]), ordertype=i[7],
id=i[1], reference=i[13],
item=frepple.item(name=i[11]) if i[11] else None,
supplier=frepple.supplier(name=i[10]) if i[10] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6], create=create_flag
)
elif i[7] == 'DO':
cnt_do += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[9]) if i[9] else None,
id=i[1], reference=i[13], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6], create=create_flag
)
elif i[7] == 'DLVR':
cnt_dlvr += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]) if i[12] else None,
id=i[1], reference=i[13], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
demand=frepple.demand(name=i[14]) if i[14] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6], create=create_flag
)
opplan = None
else:
logger.warning("Warning: unhandled operationplan type '%s'" % i[7])
continue
if i[14] and opplan:
opplan.demand = frepple.demand(name=i[14])
except Exception as e:
logger.error("**** %s ****" % e)
with connections[database].chunked_cursor() as cursor:
cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status,
operationplan.owner_id, operationplan.source, operationplan.reference,
coalesce(dmd.name, null)
FROM operationplan
INNER JOIN (select id
from operationplan
) opplan_parent
on operationplan.owner_id = opplan_parent.id
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type = 'MO'
ORDER BY operationplan.id ASC
''' % (filter_and, confirmed_filter))
for i in cursor:
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[7],
start=i[3], end=i[4], status=i[5], reference=i[8]
)
if i[6] and opplan:
try:
opplan.owner = frepple.operationplan(id=i[6])
except:
pass
if i[9] and opplan:
opplan.demand = frepple.demand(name=i[9])
logger.info('Loaded %d manufacturing orders, %d purchase orders, %d distribution orders and %s deliveries in %.2f seconds' % (cnt_mo, cnt_po, cnt_do, cnt_dlvr, time() - starttime))
with connections[database].cursor() as cursor:
# Assure the operationplan ids will be unique.
# We call this method only at the end, as calling it earlier gives a slower
# performance to load operationplans
cursor.execute('''
select coalesce(max(id),1) + 1 as max_id
from operationplan
''')
d = cursor.fetchone()
frepple.settings.id = d[0]
def loadSuboperations(self):
print('Importing suboperations...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT operation_id, suboperation_id, priority, effective_start, effective_end,
(select type
from operation
where suboperation.operation_id = operation.name) as type
FROM suboperation
WHERE priority >= 0 %s
ORDER BY operation_id, priority
''' % self.filter_and)
curopername = None
for i in self.cursor.fetchall():
cnt += 1
try:
if i[0] != curopername:
curopername = i[0]
curoper = frepple.operation(name=curopername)
if isinstance(curoper, frepple.operation_routing):
curoper.addStep(frepple.operation(name=i[1]))
elif isinstance(curoper, frepple.operation_alternate):
if i[3]:
if i[4]:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
priority=i[2],
effective_start=i[3],
effective_end=i[4])
else:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
priority=i[2],
effective_start=i[3])
elif i[4]:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
priority=i[2],
effective_end=i[4])
else:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
priority=i[2])
elif isinstance(curoper, frepple.operation_split):
if i[3]:
if i[4]:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
percent=i[2],
effective_start=i[3],
effective_end=i[4])
else:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
percent=i[2],
effective_start=i[3])
elif i[4]:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
percent=i[2],
effective_end=i[4])
else:
curoper.addAlternate(
operation=frepple.operation(name=i[1]),
percent=i[2])
else:
print("Error: Operation '%s' can't have suboperations" %
curopername)
except Exception as e:
print("Error:", e)
print('Loaded %d suboperations in %.2f seconds' %
(cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
if cls.filter:
filter_and = "and %s " % cls.filter
filter_where = "where %s " % cls.filter
else:
filter_and = ""
filter_where = ""
with connections[database].chunked_cursor() as cursor:
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
cursor.execute('''
SELECT
operation_id, item_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM operationmaterial %s
ORDER BY operation_id, item_id
''' % filter_where)
for i in cursor:
cnt += 1
try:
curflow = frepple.flow(
operation=frepple.operation(name=i[0]),
item=frepple.item(name=i[1]),
quantity=i[2],
type="flow_%s" % i[3],
source=i[9]
)
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
logger.error("**** %s ****" % e)
logger.info('Loaded %d operation materials in %.2f seconds' % (cnt, time() - starttime))
# Check for operations where:
# - operation.item is still blank
# - they have a single operationmaterial item with quantity > 0
# If found we update
starttime = time()
cnt = 0
logger.info('Auto-update operation items...')
for oper in frepple.operations():
if oper.hidden or oper.item or oper.hasSuperOperations:
continue
item = None
for fl in oper.flows:
if fl.quantity < 0 or fl.hidden:
continue
if item and item != fl.item:
item = None
break
else:
item = fl.item
if item:
cnt += 1
oper.item = item
logger.info('Auto-update of %s operation items in %.2f seconds' % (cnt, time() - starttime))
