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 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 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 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 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))