def exportOperations(self, cursor):
with transaction.atomic(using=self.database, savepoint=False):
print("Exporting operations...")
starttime = time()
cursor.execute("SELECT name FROM operation")
primary_keys = set([ i[0] for i in cursor.fetchall() ])
cursor.executemany(
'''insert into operation
(name,fence,posttime,sizeminimum,sizemultiple,sizemaximum,type,duration,
duration_per,location_id,cost,search,description,category,subcategory,source,lastmodified)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''',
[
(
i.name, i.fence, i.posttime, round(i.size_minimum, settings.DECIMAL_PLACES),
round(i.size_multiple, settings.DECIMAL_PLACES),
i.size_maximum < 9999999999999 and round(i.size_maximum, settings.DECIMAL_PLACES) or None,
i.__class__.__name__[10:],
isinstance(i, (frepple.operation_fixed_time, frepple.operation_time_per)) and i.duration or None,
isinstance(i, frepple.operation_time_per) and i.duration_per or None,
i.location and i.location.name or None, round(i.cost, settings.DECIMAL_PLACES),
isinstance(i, frepple.operation_alternate) and i.search or None,
i.description, i.category, i.subcategory, i.source, self.timestamp
)
for i in frepple.operations()
if i.name not in primary_keys and not i.hidden and i.name != 'setup operation' and (not self.source or self.source == i.source)
])
cursor.executemany(
'''update operation
set fence=%s, posttime=%s, sizeminimum=%s, sizemultiple=%s,
sizemaximum=%s, type=%s, duration=%s, duration_per=%s, location_id=%s, cost=%s, search=%s,
description=%s, category=%s, subcategory=%s, source=%s, lastmodified=%s
where name=%s''',
[
(
i.fence, i.posttime, round(i.size_minimum, settings.DECIMAL_PLACES),
round(i.size_multiple, settings.DECIMAL_PLACES),
i.size_maximum < 9999999999999 and round(i.size_maximum, settings.DECIMAL_PLACES) or None,
i.__class__.__name__[10:],
isinstance(i, (frepple.operation_fixed_time, frepple.operation_time_per)) and i.duration or None,
isinstance(i, frepple.operation_time_per) and i.duration_per or None,
i.location and i.location.name or None, round(i.cost, settings.DECIMAL_PLACES),
isinstance(i, frepple.operation_alternate) and i.search or None,
i.description, i.category, i.subcategory, i.source, self.timestamp, i.name
)
for i in frepple.operations()
if i.name in primary_keys and not i.hidden and i.name != 'setup operation' and (not self.source or self.source == i.source)
])
print('Exported operations in %.2f seconds' % (time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue('plan.loglevel', database, 0))
if cls.task and cls.task.user:
maxloglevel = cls.task.user.getMaxLoglevel(database)
if loglevel > maxloglevel:
loglevel = maxloglevel
# Propagate the operationplan status
frepple.solver_propagateStatus(
loglevel=loglevel
).solve()
# Update the feasibility flag of all operationplans
for oper in frepple.operations():
for opplan in oper.operationplans:
opplan.updateFeasible()
# Report the result
print ("Initial problems:")
probs = {}
for i in frepple.problems():
if i.name in probs:
probs[i.name] += 1
else:
probs[i.name] = 1
for i in sorted(probs.keys()):
print(" %s: %s" % (i, probs[i]))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue("plan.loglevel", database, 0))
# Propagate the operationplan status
logger.info("Propagating work-in-progress status information")
frepple.solver_propagateStatus(loglevel=loglevel).solve()
# Update the feasibility flag of all operationplans
for oper in frepple.operations():
for opplan in oper.operationplans:
opplan.updateFeasible()
# Report the result
print("Initial problems:")
probs = {}
for i in frepple.problems():
if i.name in probs:
probs[i.name] += 1
else:
probs[i.name] = 1
for i in sorted(probs.keys()):
print(" %s: %s" % (i, probs[i]))
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 getPOs(flag):
for i in frepple.operations():
if not isinstance(i, frepple.operation_itemsupplier):
continue
for j in i.operationplans:
if (flag and j.status == "proposed") or (not flag and j.status != "proposed"):
yield j
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue('plan.loglevel', database, 0))
if cls.task and cls.task.user:
maxloglevel = cls.task.user.getMaxLoglevel(database)
if loglevel > maxloglevel:
loglevel = maxloglevel
# Propagate the operationplan status
frepple.solver_propagateStatus(loglevel=loglevel).solve()
# Update the feasibility flag of all operationplans
for oper in frepple.operations():
for opplan in oper.operationplans:
opplan.updateFeasible()
# Report the result
print("Initial problems:")
probs = {}
for i in frepple.problems():
if i.name in probs:
probs[i.name] += 1
else:
probs[i.name] = 1
for i in sorted(probs.keys()):
print(" %s: %s" % (i, probs[i]))
def exportOperationplans(process):
print("Exporting operationplans...")
starttime = time()
process.stdin.write(
"COPY out_operationplan (id,operation,quantity,startdate,enddate,criticality,locked,unavailable,owner) FROM STDIN;\n".encode(
encoding
)
)
for i in frepple.operations():
# if isinstance(i, (frepple.operation_itemsupplier, frepple.operation_itemdistribution)):
# # TODO Purchase orders and distribution orders are exported separately
# continue
for j in i.operationplans:
process.stdin.write(
(
"%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n"
% (
j.id,
i.name[0:300],
round(j.quantity, 4),
str(j.start),
str(j.end),
round(j.criticality, 4),
j.locked,
j.unavailable,
j.owner and j.owner.id or "\\N",
)
).encode(encoding)
)
process.stdin.write("\\.\n".encode(encoding))
print("Exported operationplans in %.2f seconds" % (time() - starttime))
def getDOs(flag):
for i in frepple.operations():
if not isinstance(i, frepple.operation_itemdistribution):
continue
for j in i.operationplans:
if (flag and j.status == 'proposed') or (not flag and j.status != 'proposed'):
yield j
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 getDOs(flag):
for i in frepple.operations():
if not isinstance(i, frepple.operation_itemdistribution):
continue
for j in i.operationplans:
if (flag and j.status == 'proposed') or (
not flag and j.status != 'proposed'):
yield j
def subops():
for i in frepple.operations():
if isinstance(i,frepple.operation_alternate):
for j in i.alternates:
yield i, j
if isinstance(i, frepple.operation_routing):
for j in i.steps:
yield i, j
def subops():
for i in frepple.operations():
if isinstance(i,frepple.operation_alternate):
for j in i.alternates:
yield i, j
if isinstance(i, frepple.operation_routing):
for j in i.steps:
yield i, j
def loads(source):
for o in frepple.operations():
if o.hidden:
continue
for i in o.loads:
if i.hidden:
continue
if not source or source == i.source:
yield i
def getPOs(flag):
for i in frepple.operations():
if not isinstance(i, frepple.operation_itemsupplier):
continue
if self.cluster != -1 and self.cluster != i.cluster:
continue
for j in i.operationplans:
if (flag and j.status == 'proposed') or (not flag and j.status != 'proposed'):
yield j
def loads(source):
for o in frepple.operations():
if o.hidden:
continue
for i in o.loads:
if i.hidden:
continue
if not source or source == i.source:
yield i
def subops():
for i in frepple.operations():
if isinstance(i, frepple.operation_alternate):
for j in i.alternates:
yield i, j[0], j[1], j[2], j[3], i.source
if isinstance(i, frepple.operation_routing):
cnt = 1
for j in i.steps:
yield i, j, cnt, None, None, i.source
cnt += 1
def exportOperationplans(self, process):
if self.verbosity:
print("Exporting operationplans...")
starttime = time()
excluded_operations = (
frepple.operation_itemsupplier,
frepple.operation_itemdistribution,
frepple.operation_routing,
frepple.operation_alternate
)
# TODO: the first part of the export is a redundant duplicate. We still need it for the pegging information... for now.
# TODO: also run an update for the criticality of the existing, locked operationplan records
# TODO: export of owner, export of demand delivery
process.stdin.write('COPY out_operationplan (id,operation,quantity,startdate,enddate,criticality,locked,unavailable,owner) FROM STDIN;\n'.encode(self.encoding))
for i in frepple.operations():
if self.cluster != -1 and self.cluster != i.cluster:
continue
opname = i.name[0:300]
for j in i.operationplans:
process.stdin.write(("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" % (
j.id, opname, round(j.quantity, 4), str(j.start), str(j.end),
round(j.criticality, 4), j.locked, j.unavailable,
j.owner and j.owner.id or "\\N"
)).encode(self.encoding))
process.stdin.write('\\.\n'.encode(self.encoding))
process.stdin.write('COPY operationplan (id,operation_id,status,quantity,startdate,enddate,criticality,lastmodified) FROM STDIN;\n'.encode(self.encoding))
for i in frepple.operations():
if isinstance(i, excluded_operations):
continue
if self.cluster != -1 and self.cluster != i.cluster:
continue
opname = i.name[0:300]
for j in i.operationplans:
if not j.locked and not (j.demand or (j.owner and j.owner.demand)):
process.stdin.write(("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" % (
j.id, opname, j.status, round(j.quantity, 4),
str(j.start), str(j.end), round(j.criticality, 4),
self.timestamp
)).encode(self.encoding))
process.stdin.write('\\.\n'.encode(self.encoding))
if self.verbosity:
print('Exported operationplans in %.2f seconds' % (time() - starttime))
def subops():
for i in frepple.operations():
if isinstance(i, frepple.operation_alternate):
for j in i.alternates:
yield i, j[0], j[1], j[2], j[3], i.source
elif isinstance(i, frepple.operation_split):
for j in i.alternates:
yield i, j[0], j[1], j[2], j[3], i.source
elif isinstance(i, frepple.operation_routing):
cnt = 1
for j in i.steps:
yield i, j, cnt, None, None, i.source
cnt += 1
def exportOperationplans(process):
print("Exporting operationplans...")
starttime = time()
process.stdin.write('COPY out_operationplan (id,operation,quantity,startdate,enddate,locked,unavailable,owner) FROM STDIN;\n')
for i in frepple.operations():
for j in i.operationplans:
process.stdin.write("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" % (
j.id, i.name[0:settings.NAMESIZE].encode(encoding),
round(j.quantity,settings.DECIMAL_PLACES), str(j.start), str(j.end),
j.locked, j.unavailable, j.owner and j.owner.id or "\\N"
))
process.stdin.write('\\.\n')
print('Exported operationplans in %.2f seconds' % (time() - starttime))
def exportOperationplans(process):
print("Exporting operationplans...")
starttime = time()
excluded_operations = (frepple.operation_itemsupplier,
frepple.operation_itemdistribution,
frepple.operation_routing,
frepple.operation_alternate)
# TODO: the first part of the export is a redundant duplicate. We still need it for the pegging information... for now.
# TODO: also run an update for the criticality of the existing, locked operationplan records
# TODO: export of owner, export of demand delivery
process.stdin.write(
'COPY out_operationplan (id,operation,quantity,startdate,enddate,criticality,locked,unavailable,owner) FROM STDIN;\n'
.encode(encoding))
for i in frepple.operations():
opname = i.name[0:300]
for j in i.operationplans:
process.stdin.write(
("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" %
(j.id, opname, round(j.quantity, 4), str(j.start), str(
j.end), round(j.criticality, 4), j.locked, j.unavailable,
j.owner and j.owner.id or "\\N")).encode(encoding))
process.stdin.write('\\.\n'.encode(encoding))
process.stdin.write(
'COPY operationplan (id,operation_id,status,quantity,startdate,enddate,criticality,lastmodified) FROM STDIN;\n'
.encode(encoding))
for i in frepple.operations():
if isinstance(i, excluded_operations):
continue
opname = i.name[0:300]
for j in i.operationplans:
if not j.locked and not (j.demand or (j.owner and j.owner.demand)):
process.stdin.write(
("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" %
(j.id, opname, j.status, round(j.quantity, 4), str(
j.start), str(j.end), round(
j.criticality, 4), timestamp)).encode(encoding))
process.stdin.write('\\.\n'.encode(encoding))
print('Exported operationplans in %.2f seconds' % (time() - starttime))
def truncate(self, process):
if self.verbosity:
print("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
process.stdin.write("truncate table out_demandpegging;\n".encode(self.encoding))
process.stdin.write("truncate table out_problem, out_resourceplan, out_constraint;\n".encode(self.encoding))
process.stdin.write("truncate table out_loadplan, out_flowplan, out_operationplan;\n".encode(self.encoding))
process.stdin.write("truncate table out_demand;\n".encode(self.encoding))
process.stdin.write("delete from purchase_order where status='proposed' or status is null;\n".encode(self.encoding))
process.stdin.write("delete from distribution_order where status='proposed' or status is null;\n".encode(self.encoding))
process.stdin.write("delete from operationplan where status='proposed' or status is null;\n".encode(self.encoding))
else:
# Partial export for a single cluster
process.stdin.write('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name));\n'.encode(self.encoding))
for i in frepple.items():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write('''delete from out_demandpegging where demand in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n'''.encode(self.encoding))
process.stdin.write("delete from out_demand where item in (select name from cluster_keys);\n".encode(self.encoding))
process.stdin.write("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write("delete from out_flowplan where thebuffer in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n".encode(self.encoding))
process.stdin.write('''delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n'''.encode(self.encoding))
process.stdin.write("delete from out_problem where entity = 'material' and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n".encode(self.encoding))
process.stdin.write("delete from purchase_order using cluster_keys where (status='proposed' or status is null) and purchase_order.item_id = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from distribution_order using cluster_keys where (status='proposed' or status is null) and distribution_order.item_id = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("truncate table cluster_keys;\n".encode(self.encoding))
for i in frepple.resources():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write('delete from out_loadplan using cluster_keys where theresource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write('delete from out_resourceplan using cluster_keys where theresource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write('truncate table cluster_keys;\n'.encode(self.encoding))
for i in frepple.operations():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from out_operationplan using cluster_keys where operation = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.operation_id = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("drop table cluster_keys;\n".encode(self.encoding))
if self.verbosity:
print("Emptied plan tables in %.2f seconds" % (time() - starttime))
def exportOperationplans(process):
print("Exporting operationplans...")
starttime = time()
process.stdin.write(
'COPY out_operationplan (id,operation,quantity,startdate,enddate,locked,unavailable,owner) FROM STDIN;\n'
)
for i in frepple.operations():
for j in i.operationplans:
process.stdin.write(
"%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" %
(j.id, i.name[0:settings.NAMESIZE].encode(encoding),
round(j.quantity, settings.DECIMAL_PLACES), str(j.start),
str(j.end), j.locked, j.unavailable, j.owner and j.owner.id
or "\\N"))
process.stdin.write('\\.\n')
print('Exported operationplans in %.2f seconds' % (time() - starttime))
def exportOperationplans(cursor):
print("Exporting operationplans...")
starttime = time()
cnt = 0
for i in frepple.operations():
cursor.executemany(
"insert into out_operationplan \
(id,operation,quantity,startdate,enddate,criticality,locked,unavailable,owner) \
values (%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[(
j.id, i.name.replace("'", "''"),
round(j.quantity, settings.DECIMAL_PLACES), str(j.start), str(j.end),
round(j.criticality, settings.DECIMAL_PLACES), j.locked, j.unavailable,
j.owner and j.owner.id or None
) for j in i.operationplans ])
cnt += 1
cursor.execute("select count(*) from out_operationplan")
print('Exported %d operationplans in %.2f seconds' % (cursor.fetchone()[0], time() - starttime))
def exportOperationplans(process):
print("Exporting operationplans...")
starttime = time()
process.stdin.write(
'COPY out_operationplan (id,operation,quantity,startdate,enddate,criticality,locked,unavailable,owner) FROM STDIN;\n'
.encode(encoding))
for i in frepple.operations():
#if isinstance(i, (frepple.operation_itemsupplier, frepple.operation_itemdistribution)):
# # TODO Purchase orders and distribution orders are exported separately
# continue
for j in i.operationplans:
process.stdin.write(
("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" %
(j.id, i.name[0:300], round(j.quantity, 4), str(j.start),
str(j.end), round(j.criticality, 4), j.locked, j.unavailable,
j.owner and j.owner.id or "\\N")).encode(encoding))
process.stdin.write('\\.\n'.encode(encoding))
print('Exported operationplans in %.2f seconds' % (time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Update the feasibility flag of all operationplans
for oper in frepple.operations():
for opplan in oper.operationplans:
opplan.updateFeasible()
# Report the result
print("Initial problems:")
probs = {}
for i in frepple.problems():
if i.name in probs:
probs[i.name] += 1
else:
probs[i.name] = 1
for i in sorted(probs.keys()):
print(" %s: %s" % (i, probs[i]))
def exportOperationplans(cursor):
print("Exporting operationplans...")
starttime = time()
cnt = 0
for i in frepple.operations():
cursor.executemany(
"insert into out_operationplan \
(id,operation,quantity,startdate,enddate,criticality,locked,unavailable,owner) \
values (%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[(
j.id, i.name.replace("'", "''"),
round(j.quantity, settings.DECIMAL_PLACES), str(j.start), str(j.end),
round(j.criticality, settings.DECIMAL_PLACES), j.locked, j.unavailable,
j.owner and j.owner.id or None
) for j in i.operationplans ])
cnt += 1
cursor.execute("select count(*) from out_operationplan")
print('Exported %d operationplans in %.2f seconds' % (cursor.fetchone()[0], time() - starttime))
def loads():
for o in frepple.operations():
for i in o.loads:
yield i
def flows():
for o in frepple.operations():
for i in o.flows:
yield i
def subops():
for i in frepple.operations():
if not i.hidden and isinstance(i, (frepple.operation_split, frepple.operation_routing, frepple.operation_alternate)):
for j in i.suboperations:
yield j
def getData(cls, timestamp, cluster=-1, accepted_status=[]):
import frepple
linetemplate = "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s"
for i in cls.attrs:
linetemplate += "\v%s"
linetemplate += "\n"
for i in frepple.operations():
if cluster != -1 and cluster != i.cluster:
continue
# variable used to make sure only first proposed operationplan has its color set.
proposedFound = False
proposedFoundDate = None
for j in i.operationplans:
status = j.status
if status not in accepted_status:
continue
delay = j.delay
color = 100 - delay / 86400
data = None
if isinstance(i, frepple.operation_inventory):
# Export inventory
data = [
clean_value(i.name),
"STCK",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.buffer.item.name),
clean_value(j.operation.buffer.location.name),
"\\N",
"\\N",
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
"\\N", # color is empty for stock
clean_value(j.reference),
clean_value(j.batch),
"\\N",
]
elif isinstance(i, frepple.operation_itemdistribution):
# Export DO
data = [
clean_value(i.name),
"DO",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.destination.item.name)
if j.operation.destination else
j.operation.origin.item.name,
clean_value(j.operation.destination.location.name)
if j.operation.destination else "\\N",
clean_value(j.operation.origin.location.name)
if j.operation.origin else "\\N",
"\\N",
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
color if
(proposedFound is False and status == "proposed") or
(status == "proposed" and j.start == proposedFoundDate)
or status in ("confirmed",
"approved") else "\\N", # color
clean_value(j.reference),
clean_value(j.batch),
"\\N",
]
elif isinstance(i, frepple.operation_itemsupplier):
# Export PO
data = [
clean_value(i.name),
"PO",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.buffer.item.name),
"\\N",
"\\N",
clean_value(j.operation.buffer.location.name),
clean_value(j.operation.itemsupplier.supplier.name),
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
color if
(proposedFound is False and status == "proposed") or
(status == "proposed" and j.start == proposedFoundDate)
or status in ("confirmed", "approved") else
"\\N", # color
clean_value(j.reference),
clean_value(j.batch),
"\\N",
]
elif not i.hidden:
# Export MO
data = [
clean_value(i.name),
"MO",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
clean_value(i.name),
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(i.item.name)
if i.item else clean_value(i.owner.item.name)
if i.owner and i.owner.item else clean_value(
j.demand.item.name) if j.demand and j.demand.item
else clean_value(j.owner.demand.item.name) if j.owner
and j.owner.demand and j.owner.demand.item else "\\N",
"\\N",
"\\N",
clean_value(i.location.name) if i.location else "\\N",
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
color if
(proposedFound is False and status == "proposed") or
(status == "proposed" and j.start == proposedFoundDate)
or status in ("confirmed",
"approved") else "\\N", # color
clean_value(j.reference),
clean_value(j.batch),
round(j.quantity_completed, 8)
if j.quantity_completed else "\\N",
]
elif j.demand or (j.owner and j.owner.demand):
# Export shipments (with automatically created delivery operations)
data = [
clean_value(i.name),
"DLVR",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.buffer.item.name),
"\\N",
"\\N",
clean_value(j.operation.buffer.location.name),
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
"\\N", # color is empty for deliver operation
clean_value(j.reference),
clean_value(j.batch),
"\\N",
]
if data:
for attr in cls.attrs:
v = getattr(j, attr[0], None)
if v is None:
data.append("\\N")
elif attr[2] == "boolean":
data.append(True if v else False)
elif attr[2] == "duration":
data.append(v)
elif attr[2] == "integer":
data.append(round(v))
elif attr[2] == "number":
data.append(round(v, 6))
elif attr[2] == "string":
data.append(clean_value(v))
elif attr[2] == "time":
data.append(v)
elif attr[2] == "date":
data.append(v)
elif attr[2] == "datetime":
data.append(v)
else:
raise Exception("Unknown attribute type %s" %
attr[2])
yield linetemplate % tuple(data)
if status == "proposed":
proposedFound = True
proposedFoundDate = j.start
def getData():
for i in frepple.operations():
if self.cluster != -1 and self.cluster != i.cluster:
continue
for j in i.operationplans:
delay = j.delay
color = 100 - delay / 86400
if isinstance(i, frepple.operation_inventory):
# Export inventory
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"STCK",
j.status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
self.getPegging(j),
clean_value(j.source),
self.timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden
else "\\N",
clean_value(j.operation.buffer.item.name),
clean_value(j.operation.buffer.location.name),
"\\N",
"\\N",
"\\N",
clean_value(j.demand.name)
if j.demand
else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand
else "\\N",
j.demand.due
if j.demand
else j.owner.demand.due
if j.owner and j.owner.demand
else "\\N",
color,
clean_value(j.reference),
)
elif isinstance(i, frepple.operation_itemdistribution):
# Export DO
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"DO",
j.status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
self.getPegging(j),
clean_value(j.source),
self.timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden
else "\\N",
clean_value(j.operation.destination.item.name)
if j.operation.destination
else j.operation.origin.item.name,
clean_value(j.operation.destination.location.name)
if j.operation.destination
else "\\N",
clean_value(j.operation.origin.location.name)
if j.operation.origin
else "\\N",
"\\N",
"\\N",
clean_value(j.demand.name)
if j.demand
else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand
else "\\N",
j.demand.due
if j.demand
else j.owner.demand.due
if j.owner and j.owner.demand
else "\\N",
color,
clean_value(j.reference),
)
elif isinstance(i, frepple.operation_itemsupplier):
# Export PO
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"PO",
j.status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
self.getPegging(j),
clean_value(j.source),
self.timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden
else "\\N",
clean_value(j.operation.buffer.item.name),
"\\N",
"\\N",
clean_value(j.operation.buffer.location.name),
clean_value(j.operation.itemsupplier.supplier.name),
clean_value(j.demand.name)
if j.demand
else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand
else "\\N",
j.demand.due
if j.demand
else j.owner.demand.due
if j.owner and j.owner.demand
else "\\N",
color,
clean_value(j.reference),
)
elif not i.hidden:
# Export MO
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"MO",
j.status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
self.getPegging(j),
clean_value(j.source),
self.timestamp,
clean_value(i.name),
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden
else "\\N",
clean_value(i.item.name) if i.item else "\\N",
"\\N",
"\\N",
clean_value(i.location.name) if i.location else "\\N",
"\\N",
clean_value(j.demand.name)
if j.demand
else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand
else "\\N",
j.demand.due
if j.demand
else j.owner.demand.due
if j.owner and j.owner.demand
else "\\N",
color,
clean_value(j.reference),
)
elif j.demand or (j.owner and j.owner.demand):
# Export shipments (with automatically created delivery operations)
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"DLVR",
j.status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
self.getPegging(j),
clean_value(j.source),
self.timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden
else "\\N",
clean_value(j.operation.buffer.item.name),
"\\N",
"\\N",
clean_value(j.operation.buffer.location.name),
"\\N",
clean_value(j.demand.name)
if j.demand
else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand
else "\\N",
j.demand.due
if j.demand
else j.owner.demand.due
if j.owner and j.owner.demand
else "\\N",
color,
clean_value(j.reference),
)
def printModel(filename):
'''
A function that prints out all models to a file.
'''
# Open the output file
with open(filename, "wt", encoding='utf-8') as output:
# Global settings
print("Echoing global settings", file=output)
print("Plan name:", frepple.settings.name, file=output)
print("Plan description:", frepple.settings.description, file=output)
print("Plan current:", frepple.settings.current, file=output)
# Solvers
print("\nEchoing solvers:", file=output)
for b in frepple.solvers():
print(" Solver:",
b.name,
b.loglevel,
getattr(b, 'constraints', None),
file=output)
# Calendars
print("\nEchoing calendars:", file=output)
for b in frepple.calendars():
print(" Calendar:",
b.name,
getattr(b, 'default', None),
file=output)
for j in b.buckets:
print(" Bucket:",
getattr(j, 'value', None),
j.start,
j.end,
j.priority,
file=output)
# Customers
print("\nEchoing customers:", file=output)
for b in frepple.customers():
print(" Customer:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
# Locations
print("\nEchoing locations:", file=output)
for b in frepple.locations():
print(" Location:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
# Items
print("\nEchoing items:", file=output)
for b in frepple.items():
print(" Item:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
b.operation,
file=output)
# Resources
print("\nEchoing resources:", file=output)
for b in frepple.resources():
print(" Resource:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
for l in b.loads:
print(" Load:",
l.operation.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
for l in b.loadplans:
print(" Loadplan:",
l.operationplan.id,
l.operationplan.operation.name,
l.quantity,
l.startdate,
l.enddate,
file=output)
# Buffers
print("\nEchoing buffers:", file=output)
for b in frepple.buffers():
print(" Buffer:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
for l in b.flows:
print(" Flow:",
l.operation.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
for l in b.flowplans:
print(" Flowplan:",
l.operationplan.id,
l.operationplan.operation.name,
l.quantity,
l.date,
file=output)
# Operations
print("\nEchoing operations:", file=output)
for b in frepple.operations():
print(" Operation:",
b.name,
b.description,
b.category,
b.subcategory,
file=output)
for l in b.loads:
print(" Load:",
l.resource.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
for l in b.flows:
print(" Flow:",
l.buffer.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
if isinstance(b, frepple.operation_alternate):
for l in b.alternates:
print(" Alternate:",
l[0].name,
l[1],
l[2],
l[3],
file=output)
if isinstance(b, frepple.operation_routing):
for l in b.steps:
print(" Step:", l.name, file=output)
# Demands
print("\nEchoing demands:", file=output)
for b in frepple.demands():
print(" Demand:",
b.name,
b.due,
b.item.name,
b.quantity,
file=output)
for i in b.operationplans:
print(" Operationplan:",
i.id,
i.operation.name,
i.quantity,
i.end,
file=output)
# Operationplans
print("\nEchoing operationplans:", file=output)
for b in frepple.operationplans():
print(" Operationplan:",
b.operation.name,
b.quantity,
b.start,
b.end,
file=output)
for s in b.operationplans:
print(" ",
s.operation.name,
s.quantity,
s.start,
s.end,
file=output)
# Problems
print("\nPrinting problems", file=output)
for i in frepple.problems():
print(" Problem:",
i.entity,
i.name,
i.description,
i.start,
i.end,
i.weight,
file=output)
def truncate(self, process):
if self.verbosity:
print("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
process.stdin.write("truncate table out_problem, out_resourceplan, out_constraint;\n".encode(self.encoding))
process.stdin.write("truncate table operationplanmaterial, operationplanresource;\n".encode(self.encoding))
# Above line is a temporary solution until we have a correct version of this block of code
# process.stdin.write('''
# delete from operationplanmaterial
# using operationplan
# where operationplanmaterial.operationplan_id = operationplan.id
# and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK');\n
# '''.encode(self.encoding))
# process.stdin.write('''
# delete from operationplanresource
# using operationplan
# where operationplanresource.operationplan_id = operationplan.id
# and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK');\n
# '''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK';\n
'''.encode(self.encoding))
else:
# Partial export for a single cluster
process.stdin.write('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name));\n'.encode(self.encoding))
for i in frepple.items():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write('''
delete from operationplanmaterial
where buffer in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name;\n
'''.encode(self.encoding))
process.stdin.write("truncate table cluster_keys;\n".encode(self.encoding))
for i in frepple.resources():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write('delete from operationplanresource using cluster_keys where resource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write('delete from out_resourceplan using cluster_keys where resource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write('truncate table cluster_keys;\n'.encode(self.encoding))
for i in frepple.operations():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.operation_id = cluster_keys.name;\n".encode(self.encoding)) # TODO not correct in new data model
process.stdin.write("drop table cluster_keys;\n".encode(self.encoding))
if self.verbosity:
print("Emptied plan tables in %.2f seconds" % (time() - starttime))
def truncate(self):
cursor = connections[self.database].cursor()
if self.verbosity:
logger.info("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
cursor.execute("truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute('''
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.reference = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
''')
cursor.execute('''
truncate operationplanmaterial, operationplanresource
''')
cursor.execute('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
''')
else:
# Partial export for a single cluster
cursor.execute('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))')
for i in frepple.items():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)")
cursor.execute('''
delete from operationplanmaterial
using cluster_keys
where operationplan_id in (
select reference from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
union
select reference from operationplan where owner_id in (
select reference from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
)
''')
cursor.execute('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
''')
cursor.execute('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id)
''')
cursor.execute('''
delete from operationplanresource
where operationplan_id in (
select reference
from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
where status = 'proposed' or status is null or type='STCK'
union
select reference
from operationplan
where owner_id in (
select reference
from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
and (status = 'proposed' or status is null)
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.reference
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
''')
# TODO next subqueries are not efficient - the exists condition triggers a sequential scan
cursor.execute("delete from out_constraint where exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_constraint.demand like forecast.name || ' - %')")
cursor.execute("delete from out_problem where entity = 'demand' and exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_problem.owner like forecast.name || ' - %')")
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)")
cursor.execute('delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name')
cursor.execute('delete from out_resourceplan using cluster_keys where resource = cluster_keys.name')
cursor.execute("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name")
cursor.execute('truncate table cluster_keys')
for i in frepple.operations():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name")
cursor.execute("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.name = cluster_keys.name")
cursor.execute("drop table cluster_keys")
if self.verbosity:
logger.info("Emptied plan tables in %.2f seconds" % (time() - starttime))
def run(cls, cluster=-1, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
cursor = connections[database].cursor()
if cluster == -1:
# Complete export for the complete model
cursor.execute(
"truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute("""
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.reference = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
""")
cursor.execute("""
truncate operationplanmaterial, operationplanresource
""")
cursor.execute("""
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
""")
else:
# Partial export for a single cluster
cursor.execute(
"create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))"
)
for i in frepple.items():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute("""
delete from out_constraint
where demand in (
select demand.name
from demand
inner join cluster_keys
on cluster_keys.name = demand.item_id
)
""")
cursor.execute("""
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
""")
cursor.execute("""
delete from out_problem
where entity = 'material'
and owner in (
select buffer.item_id || ' @ ' || buffer.location_id
from buffer
inner join cluster_keys on cluster_keys.name = buffer.item_id
)
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.reference
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
""")
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute("""
delete from out_problem
where entity = 'demand'
and owner in (
select demand.name
from demand
inner join cluster_keys on cluster_keys.name = demand.item_id
)
""")
cursor.execute(
"delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name"
)
cursor.execute(
"delete from out_resourceplan using cluster_keys where resource = cluster_keys.name"
)
cursor.execute(
"delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name"
)
cursor.execute("truncate table cluster_keys")
for i in frepple.operations():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute(""""
delete from out_problem
using cluster_keys
where entity = 'operation' and owner = cluster_keys.name
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where (status='proposed' or status is null)
and operationplan.name = cluster_keys.name
""")
cursor.execute("drop table cluster_keys")
def getData(cls, timestamp, cluster=-1):
import frepple
for i in frepple.operations():
if cluster != -1 and cluster != i.cluster:
continue
# variable used to make sure only first proposed operationplan has its color set.
proposedFound = False
proposedFoundDate = None
for j in i.operationplans:
delay = j.delay
status = j.status
color = 100 - delay / 86400
if isinstance(i, frepple.operation_inventory):
# Export inventory
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"STCK",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.buffer.item.name),
clean_value(j.operation.buffer.location.name),
"\\N",
"\\N",
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
"\\N", # color is empty for stock
clean_value(j.reference),
clean_value(j.batch),
)
elif isinstance(i, frepple.operation_itemdistribution):
# Export DO
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"DO",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.destination.item.name)
if j.operation.destination else
j.operation.origin.item.name,
clean_value(j.operation.destination.location.name)
if j.operation.destination else "\\N",
clean_value(j.operation.origin.location.name)
if j.operation.origin else "\\N",
"\\N",
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
color if
(proposedFound is False and status == "proposed") or
(status == "proposed" and j.start == proposedFoundDate)
or status in ("confirmed",
"approved") else "\\N", # color
clean_value(j.reference),
clean_value(j.batch),
)
elif isinstance(i, frepple.operation_itemsupplier):
# Export PO
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"PO",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.buffer.item.name),
"\\N",
"\\N",
clean_value(j.operation.buffer.location.name),
clean_value(j.operation.itemsupplier.supplier.name),
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
color if
(proposedFound is False and status == "proposed") or
(status == "proposed" and j.start == proposedFoundDate)
or status in ("confirmed", "approved") else
"\\N", # color
clean_value(j.reference),
clean_value(j.batch),
)
elif not i.hidden:
# Export MO
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"MO",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
clean_value(i.name),
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(i.item.name)
if i.item else clean_value(i.owner.item.name)
if i.owner and i.owner.item else clean_value(
j.demand.item.name) if j.demand and j.demand.item
else clean_value(j.owner.demand.item.name) if j.owner
and j.owner.demand and j.owner.demand.item else "\\N",
"\\N",
"\\N",
clean_value(i.location.name) if i.location else "\\N",
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
color if
(proposedFound is False and status == "proposed") or
(status == "proposed" and j.start == proposedFoundDate)
or status in ("confirmed",
"approved") else "\\N", # color
clean_value(j.reference),
clean_value(j.batch),
)
elif j.demand or (j.owner and j.owner.demand):
# Export shipments (with automatically created delivery operations)
yield "%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\v%s\n" % (
clean_value(i.name),
"DLVR",
status,
round(j.quantity, 8),
str(j.start),
str(j.end),
round(j.criticality, 8),
j.delay,
cls.getPegging(j),
clean_value(j.source),
timestamp,
"\\N",
clean_value(j.owner.reference)
if j.owner and not j.owner.operation.hidden else "\\N",
clean_value(j.operation.buffer.item.name),
"\\N",
"\\N",
clean_value(j.operation.buffer.location.name),
"\\N",
clean_value(j.demand.name)
if j.demand else clean_value(j.owner.demand.name)
if j.owner and j.owner.demand else "\\N",
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else "\\N",
"\\N", # color is empty for deliver operation
clean_value(j.reference),
clean_value(j.batch),
)
if status == "proposed":
proposedFound = True
proposedFoundDate = j.start
def truncate(self, process):
if self.verbosity:
print("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
process.stdin.write(
"truncate table out_problem, out_resourceplan, out_constraint;\n"
.encode(self.encoding))
process.stdin.write('''
delete from operationplanmaterial
using operationplan
where operationplanmaterial.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK' or operationplanmaterial.status = 'proposed');\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplanresource
using operationplan
where operationplanresource.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK' or operationplanresource.status = 'proposed');\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK';\n
'''.encode(self.encoding))
else:
# Partial export for a single cluster
process.stdin.write(
'create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name));\n'
.encode(self.encoding))
for i in frepple.items():
if i.cluster == self.cluster:
process.stdin.write(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(
self.encoding)).encode(self.encoding))
process.stdin.write(
"delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n"
.encode(self.encoding))
process.stdin.write('''
delete from operationplanmaterial
where buffer in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name;\n
'''.encode(self.encoding))
process.stdin.write("truncate table cluster_keys;\n".encode(
self.encoding))
for i in frepple.resources():
if i.cluster == self.cluster:
process.stdin.write(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(
self.encoding)).encode(self.encoding))
process.stdin.write(
"delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n"
.encode(self.encoding))
process.stdin.write(
'delete from operationplanresource using cluster_keys where resource = cluster_keys.name;\n'
.encode(self.encoding))
process.stdin.write(
'delete from out_resourceplan using cluster_keys where resource = cluster_keys.name;\n'
.encode(self.encoding))
process.stdin.write(
"delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name;\n"
.encode(self.encoding))
process.stdin.write('truncate table cluster_keys;\n'.encode(
self.encoding))
for i in frepple.operations():
if i.cluster == self.cluster:
process.stdin.write(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(
self.encoding)).encode(self.encoding))
process.stdin.write(
"delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name;\n"
.encode(self.encoding))
process.stdin.write(
"delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.operation_id = cluster_keys.name;\n"
.encode(self.encoding)) # TODO not correct in new data model
process.stdin.write("drop table cluster_keys;\n".encode(
self.encoding))
if self.verbosity:
print("Emptied plan tables in %.2f seconds" % (time() - starttime))
def truncate(self):
cursor = connections[self.database].cursor()
if self.verbosity:
logger.info("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
cursor.execute(
"truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute('''
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.id = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
''')
cursor.execute('''
delete from operationplanmaterial
using operationplan
where operationplanmaterial.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null)
or operationplan.type = 'STCK'
or operationplanmaterial.status = 'proposed'
or operationplanmaterial.status is null)
''')
cursor.execute('''
delete from operationplanresource
using operationplan
where operationplanresource.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null)
or operationplan.type = 'STCK'
or operationplanresource.status = 'proposed'
or operationplanresource.status is null)
''')
cursor.execute('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
''')
else:
# Partial export for a single cluster
cursor.execute(
'create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))'
)
for i in frepple.items():
if i.cluster == self.cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute(
"delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)"
)
cursor.execute('''
delete from operationplanmaterial
using cluster_keys
where operationplan_id in (
select id from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
union
select id from operationplan where owner_id in (
select id from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
)
''')
cursor.execute('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
''')
cursor.execute('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id)
''')
cursor.execute('''
delete from operationplanresource
where operationplan_id in (
select id from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
union
select id from operationplan where owner_id in (
select id from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.id
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
''')
# TODO next subqueries are not efficient - the exists condition triggers a sequential scan
cursor.execute(
"delete from out_constraint where exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_constraint.demand like forecast.name || ' - %')"
)
cursor.execute(
"delete from out_problem where entity = 'demand' and exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_problem.owner like forecast.name || ' - %')"
)
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == self.cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute(
"delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)"
)
cursor.execute(
'delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name'
)
cursor.execute(
'delete from out_resourceplan using cluster_keys where resource = cluster_keys.name'
)
cursor.execute(
"delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name"
)
cursor.execute('truncate table cluster_keys')
for i in frepple.operations():
if i.cluster == self.cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute(
"delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name"
)
cursor.execute(
"delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.name = cluster_keys.name"
)
cursor.execute("drop table cluster_keys")
if self.verbosity:
logger.info("Emptied plan tables in %.2f seconds" %
(time() - starttime))
def getOperationPlans():
for i in frepple.operations():
if self.cluster != -1 and self.cluster != i.cluster:
continue
for j in i.operationplans:
delay = j.delay
color = 100 - delay / 86400
if isinstance(i, frepple.operation_inventory):
# Export inventory
yield (i.name, 'STCK', j.status, j.reference or '\\N',
round(j.quantity, 8), str(j.start), str(j.end),
round(j.criticality, 8), j.delay,
self.getPegging(j), j.source or '\\N',
self.timestamp, '\\N', j.owner.id if j.owner
and not j.owner.operation.hidden else '\\N',
j.operation.buffer.item.name,
j.operation.buffer.location.name, '\\N', '\\N',
'\\N', j.demand.name
if j.demand else j.owner.demand.name
if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else '\\N', color,
j.id)
elif isinstance(i, frepple.operation_itemdistribution):
# Export DO
yield (i.name, 'DO', j.status, j.reference or '\\N',
round(j.quantity, 8), str(j.start), str(j.end),
round(j.criticality, 8), j.delay,
self.getPegging(j), j.source or '\\N',
self.timestamp, '\\N', j.owner.id if j.owner
and not j.owner.operation.hidden else '\\N',
j.operation.destination.item.name
if j.operation.destination else
j.operation.origin.item.name,
j.operation.destination.location.name
if j.operation.destination else '\\N',
j.operation.origin.location.name
if j.operation.origin else '\\N', '\\N', '\\N',
j.demand.name
if j.demand else j.owner.demand.name
if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else '\\N', color,
j.id)
elif isinstance(i, frepple.operation_itemsupplier):
# Export PO
yield (i.name, 'PO', j.status, j.reference or '\\N',
round(j.quantity, 8), str(j.start), str(j.end),
round(j.criticality, 8), j.delay,
self.getPegging(j), j.source or '\\N',
self.timestamp, '\\N', j.owner.id if j.owner
and not j.owner.operation.hidden else '\\N',
j.operation.buffer.item.name, '\\N', '\\N',
j.operation.buffer.location.name,
j.operation.itemsupplier.supplier.name,
j.demand.name
if j.demand else j.owner.demand.name
if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else '\\N', color,
j.id)
elif not i.hidden:
# Export MO
yield (i.name, 'MO', j.status, j.reference or '\\N',
round(j.quantity, 8), str(j.start), str(j.end),
round(j.criticality, 8), j.delay,
self.getPegging(j), j.source or '\\N',
self.timestamp, i.name, j.owner.id if j.owner
and not j.owner.operation.hidden else '\\N',
i.item.name if i.item else '\\N', '\\N', '\\N',
i.location.name if i.location else '\\N', '\\N',
j.demand.name
if j.demand else j.owner.demand.name
if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else '\\N', color,
j.id)
elif j.demand or (j.owner and j.owner.demand):
# Export shipments (with automatically created delivery operations)
yield (i.name, 'DLVR', j.status, j.reference or '\\N',
round(j.quantity, 8), str(j.start), str(j.end),
round(j.criticality, 8), j.delay,
self.getPegging(j), j.source or '\\N',
self.timestamp, '\\N', j.owner.id if j.owner
and not j.owner.operation.hidden else '\\N',
j.operation.buffer.item.name, '\\N', '\\N',
j.operation.buffer.location.name, '\\N',
j.demand.name
if j.demand else j.owner.demand.name
if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due
if j.owner and j.owner.demand else '\\N', color,
j.id)
def printModel(filename):
'''
A function that prints out all models to a file.
'''
# Open the output file
output = open(filename,"wt")
# Global settings
print("Echoing global settings", file=output)
print("Plan name:", frepple.settings.name, file=output)
print("Plan description:", frepple.settings.description.encode('utf-8'), file=output)
print("Plan current:", frepple.settings.current, file=output)
# Solvers
print("\nEchoing solvers:", file=output)
for b in frepple.solvers():
print(" Solver:", b.name, b.loglevel, getattr(b,'constraints',None), file=output)
# Calendars
print("\nEchoing calendars:", file=output)
for b in frepple.calendars():
print(" Calendar:", b.name, getattr(b,'default',None), file=output)
for j in b.buckets:
print(" Bucket:", getattr(j,'value',None), j.start, j.end, j.priority, file=output)
# Customers
print("\nEchoing customers:", file=output)
for b in frepple.customers():
print(" Customer:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
# Locations
print("\nEchoing locations:", file=output)
for b in frepple.locations():
print(" Location:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
# Items
print("\nEchoing items:", file=output)
for b in frepple.items():
print(" Item:", b.name, b.description, b.category, b.subcategory, b.owner, b.operation, file=output)
# Resources
print("\nEchoing resources:", file=output)
for b in frepple.resources():
print(" Resource:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
for l in b.loads:
print(" Load:", l.operation.name, l.quantity, l.effective_start, l.effective_end, file=output)
for l in b.loadplans:
print(" Loadplan:", l.operationplan.id, l.operationplan.operation.name, l.quantity, l.startdate, l.enddate, file=output)
# Buffers
print("\nEchoing buffers:", file=output)
for b in frepple.buffers():
print(" Buffer:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
for l in b.flows:
print(" Flow:", l.operation.name, l.quantity, l.effective_start, l.effective_end, file=output)
for l in b.flowplans:
print(" Flowplan:", l.operationplan.id, l.operationplan.operation.name, l.quantity, l.date, file=output)
# Operations
print("\nEchoing operations:", file=output)
for b in frepple.operations():
print(" Operation:", b.name, b.description, b.category, b.subcategory, file=output)
for l in b.loads:
print(" Load:", l.resource.name, l.quantity, l.effective_start, l.effective_end, file=output)
for l in b.flows:
print(" Flow:", l.buffer.name, l.quantity, l.effective_start, l.effective_end, file=output)
if isinstance(b, frepple.operation_alternate):
for l in b.alternates:
print(" Alternate:", l.name, file=output)
if isinstance(b, frepple.operation_routing):
for l in b.steps:
print(" Step:", l.name, file=output)
# Demands
print("\nEchoing demands:", file=output)
for b in frepple.demands():
print(" Demand:", b.name, b.due, b.item.name, b.quantity, file=output)
for i in b.operationplans:
print(" Operationplan:", i.id, i.operation.name, i.quantity, i.end, file=output)
# Operationplans
print("\nEchoing operationplans:", file=output)
for b in frepple.operationplans():
print(" Operationplan:", b.operation.name, b.quantity, b.start, b.end, file=output)
for s in b.operationplans:
print(" ", s.operation.name, s.quantity, s.start, s.end, file=output)
# Problems
print("\nPrinting problems", file=output)
for i in frepple.problems():
print(" Problem:", i.entity, i.name, i.description, i.start, i.end, i.weight, file=output)
def getOperationPlans():
for i in frepple.operations():
if self.cluster != -1 and self.cluster != i.cluster:
continue
for j in i.operationplans:
if isinstance(i, frepple.operation_inventory):
# Export inventory
yield (
i.name, 'STCK', j.status, j.reference or '\\N', round(j.quantity, 6),
str(j.start), str(j.end), round(j.criticality, 6), j.delay,
self.getPegging(j), j.source or '\\N', self.timestamp,
'\\N', j.owner.id if j.owner and not j.owner.operation.hidden else '\\N',
j.operation.buffer.item.name, j.operation.buffer.location.name, '\\N', '\\N', '\\N',
j.demand.name if j.demand else j.owner.demand.name if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due if j.owner and j.owner.demand else '\\N',
j.id
)
elif isinstance(i, frepple.operation_itemdistribution):
# Export DO
yield (
i.name, 'DO', j.status, j.reference or '\\N', round(j.quantity, 6),
str(j.start), str(j.end), round(j.criticality, 6), j.delay,
self.getPegging(j), j.source or '\\N', self.timestamp,
'\\N', j.owner.id if j.owner and not j.owner.operation.hidden else '\\N',
j.operation.destination.item.name, j.operation.destination.location.name,
j.operation.origin.location.name,
'\\N', '\\N',
j.demand.name if j.demand else j.owner.demand.name if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due if j.owner and j.owner.demand else '\\N',
j.id
)
elif isinstance(i, frepple.operation_itemsupplier):
# Export PO
yield (
i.name, 'PO', j.status, j.reference or '\\N', round(j.quantity, 6),
str(j.start), str(j.end), round(j.criticality, 6), j.delay,
self.getPegging(j), j.source or '\\N', self.timestamp,
'\\N', j.owner.id if j.owner and not j.owner.operation.hidden else '\\N',
j.operation.buffer.item.name, '\\N', '\\N',
j.operation.buffer.location.name, j.operation.itemsupplier.supplier.name,
j.demand.name if j.demand else j.owner.demand.name if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due if j.owner and j.owner.demand else '\\N',
j.id
)
elif not i.hidden:
# Export MO
yield (
i.name, 'MO', j.status, j.reference or '\\N', round(j.quantity, 6),
str(j.start), str(j.end), round(j.criticality, 6), j.delay,
self.getPegging(j), j.source or '\\N', self.timestamp,
i.name, j.owner.id if j.owner and not j.owner.operation.hidden else '\\N',
'\\N', '\\N', '\\N', '\\N', '\\N',
j.demand.name if j.demand else j.owner.demand.name if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due if j.owner and j.owner.demand else '\\N',
j.id
)
elif j.demand or (j.owner and j.owner.demand):
# Export shipments (with automatically created delivery operations)
yield (
i.name, 'DLVR', j.status, j.reference or '\\N', round(j.quantity, 6),
str(j.start), str(j.end), round(j.criticality, 6), j.delay,
self.getPegging(j), j.source or '\\N', self.timestamp,
'\\N', j.owner.id if j.owner and not j.owner.operation.hidden else '\\N',
j.operation.buffer.item.name, '\\N', '\\N', j.operation.buffer.location.name, '\\N',
j.demand.name if j.demand else j.owner.demand.name if j.owner and j.owner.demand else '\\N',
j.demand.due if j.demand else j.owner.demand.due if j.owner and j.owner.demand else '\\N',
j.id
)
def exportOperations(self, cursor):
with transaction.atomic(using=self.database, savepoint=False):
print("Exporting operations...")
starttime = time()
default_start = datetime.datetime(1971, 1, 1)
default_end = datetime.datetime(2030, 12, 31)
cursor.execute("SELECT name FROM operation")
primary_keys = set([ i[0] for i in cursor.fetchall() ])
cursor.executemany(
"insert into operation \
(name,fence,posttime,sizeminimum,sizemultiple,sizemaximum,type, \
duration,duration_per,location_id,cost,search,description,category, \
subcategory,source,item_id,priority,effective_start,effective_end, \
lastmodified) \
values(%s,%s * interval '1 second',%s * interval '1 second',%s,%s, \
%s,%s,%s * interval '1 second',%s * interval '1 second',%s,%s,%s, \
%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[
(
i.name, i.fence, i.posttime, round(i.size_minimum, 6),
round(i.size_multiple, 6),
i.size_maximum < 9999999999999 and round(i.size_maximum, 6) or None,
i.__class__.__name__[10:],
isinstance(i, (frepple.operation_fixed_time, frepple.operation_time_per)) and i.duration or None,
isinstance(i, frepple.operation_time_per) and i.duration_per or None,
i.location and i.location.name or None, round(i.cost, 6),
isinstance(i, frepple.operation_alternate) and i.search or None,
i.description, i.category, i.subcategory, i.source,
i.item.name if i.item else None, i.priority if i.priority != 1 else None,
i.effective_start if i.effective_start != default_start else None,
i.effective_end if i.effective_end != default_end else None,
self.timestamp
)
for i in frepple.operations()
if i.name not in primary_keys and not i.hidden and not isinstance(i, frepple.operation_itemsupplier) and i.name != 'setup operation' and (not self.source or self.source == i.source)
])
cursor.executemany(
"update operation \
set fence=%s * interval '1 second', posttime=%s* interval '1 second', \
sizeminimum=%s, sizemultiple=%s, sizemaximum=%s, type=%s, \
duration=%s * interval '1 second', duration_per=%s * interval '1 second', \
location_id=%s, cost=%s, search=%s, description=%s, \
category=%s, subcategory=%s, source=%s, lastmodified=%s, \
item_id=%s, priority=%s, effective_start=%s, effective_end=%s \
where name=%s",
[
(
i.fence, i.posttime, round(i.size_minimum, 6),
round(i.size_multiple, 6),
i.size_maximum < 9999999999999 and round(i.size_maximum, 6) or None,
i.__class__.__name__[10:],
isinstance(i, (frepple.operation_fixed_time, frepple.operation_time_per)) and i.duration or None,
isinstance(i, frepple.operation_time_per) and i.duration_per or None,
i.location and i.location.name or None, round(i.cost, 6),
isinstance(i, frepple.operation_alternate) and i.search or None,
i.description, i.category, i.subcategory, i.source, self.timestamp,
i.item.name if i.item else None, i.priority,
i.effective_start if i.effective_start != default_start else None,
i.effective_end if i.effective_end != default_end else None,
i.name
)
for i in frepple.operations()
if i.name in primary_keys and not i.hidden and not isinstance(i, frepple.operation_itemsupplier) and i.name != 'setup operation' and (not self.source or self.source == i.source)
])
print('Exported operations in %.2f seconds' % (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))
def exportOperations(self, cursor):
with transaction.atomic(using=self.database, savepoint=False):
print("Exporting operations...")
starttime = time()
default_start = datetime.datetime(1971, 1, 1)
default_end = datetime.datetime(2030, 12, 31)
cursor.execute("SELECT name FROM operation")
primary_keys = set([ i[0] for i in cursor.fetchall() ])
cursor.executemany(
"insert into operation \
(name,fence,posttime,sizeminimum,sizemultiple,sizemaximum,type, \
duration,duration_per,location_id,cost,search,description,category, \
subcategory,source,item_id,priority,effective_start,effective_end, \
lastmodified) \
values(%s,%s * interval '1 second',%s * interval '1 second',%s,%s, \
%s,%s,%s * interval '1 second',%s * interval '1 second',%s,%s,%s, \
%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[
(
i.name, i.fence, i.posttime, round(i.size_minimum, 6),
round(i.size_multiple, 6),
i.size_maximum < 9999999999999 and round(i.size_maximum, 6) or None,
i.__class__.__name__[10:],
isinstance(i, (frepple.operation_fixed_time, frepple.operation_time_per)) and i.duration or None,
isinstance(i, frepple.operation_time_per) and i.duration_per or None,
i.location and i.location.name or None, round(i.cost, 6),
isinstance(i, frepple.operation_alternate) and i.search or None,
i.description, i.category, i.subcategory, i.source,
i.item.name if i.item else None, i.priority if i.priority != 1 else None,
i.effective_start if i.effective_start != default_start else None,
i.effective_end if i.effective_end != default_end else None,
self.timestamp
)
for i in frepple.operations()
if i.name not in primary_keys and not i.hidden and not isinstance(i, frepple.operation_itemsupplier) and i.name != 'setup operation' and (not self.source or self.source == i.source)
])
cursor.executemany(
"update operation \
set fence=%s * interval '1 second', posttime=%s* interval '1 second', \
sizeminimum=%s, sizemultiple=%s, sizemaximum=%s, type=%s, \
duration=%s * interval '1 second', duration_per=%s * interval '1 second', \
location_id=%s, cost=%s, search=%s, description=%s, \
category=%s, subcategory=%s, source=%s, lastmodified=%s, \
item_id=%s, priority=%s, effective_start=%s, effective_end=%s \
where name=%s",
[
(
i.fence, i.posttime, round(i.size_minimum, 6),
round(i.size_multiple, 6),
i.size_maximum < 9999999999999 and round(i.size_maximum, 6) or None,
i.__class__.__name__[10:],
isinstance(i, (frepple.operation_fixed_time, frepple.operation_time_per)) and i.duration or None,
isinstance(i, frepple.operation_time_per) and i.duration_per or None,
i.location and i.location.name or None, round(i.cost, 6),
isinstance(i, frepple.operation_alternate) and i.search or None,
i.description, i.category, i.subcategory, i.source, self.timestamp,
i.item.name if i.item else None, i.priority,
i.effective_start if i.effective_start != default_start else None,
i.effective_end if i.effective_end != default_end else None,
i.name
)
for i in frepple.operations()
if i.name in primary_keys and not i.hidden and not isinstance(i, frepple.operation_itemsupplier) and i.name != 'setup operation' and (not self.source or self.source == i.source)
])
print('Exported operations in %.2f seconds' % (time() - starttime))
