def exportItems(cursor): print("Exporting items...") starttime = time() cursor.execute("SELECT name FROM item") primary_keys = set([ i[0] for i in cursor.fetchall() ]) cursor.executemany( "insert into item \ (name,description,operation_id,price,category,subcategory,lastmodified) \ values(%s,%s,%s,%s,%s,%s,%s)", [( i.name, i.description, i.operation and i.operation.name or None, round(i.price,settings.DECIMAL_PLACES), i.category, i.subcategory, timestamp ) for i in frepple.items() if i.name not in primary_keys ]) cursor.executemany( "update item \ set description=%s, operation_id=%s, price=%s, category=%s, subcategory=%s, lastmodified=%s \ where name=%s", [( i.description, i.operation and i.operation.name or None, round(i.price,settings.DECIMAL_PLACES), i.category, i.subcategory, timestamp, i.name ) for i in frepple.items() if i.name in primary_keys ]) cursor.executemany( "update item set owner_id=%s where name=%s", [( i.owner.name, i.name ) for i in frepple.items() if i.owner ]) transaction.commit(using=database) print('Exported items in %.2f seconds' % (time() - starttime))
def exportItems(self, cursor): with transaction.atomic(using=self.database, savepoint=False): print("Exporting items...") starttime = time() cursor.execute("SELECT name FROM item") primary_keys = set([ i[0] for i in cursor.fetchall() ]) cursor.executemany( "insert into item \ (name,description,operation_id,price,category,subcategory,source,lastmodified) \ values(%s,%s,%s,%s,%s,%s,%s,%s)", [ ( i.name, i.description, i.operation and i.operation.name or None, round(i.price, 4), i.category, i.subcategory, i.source, self.timestamp ) for i in frepple.items() if i.name not in primary_keys and (not self.source or self.source == i.source) ]) cursor.executemany( "update item \ set description=%s, operation_id=%s, price=%s, category=%s, subcategory=%s, source=%s, lastmodified=%s \ where name=%s", [ ( i.description, i.operation and i.operation.name or None, round(i.price, 4), i.category, i.subcategory, i.source, self.timestamp, i.name ) for i in frepple.items() if i.name in primary_keys and (not self.source or self.source == i.source) ]) cursor.executemany( "update item set owner_id=%s where name=%s", [ (i.owner.name, i.name) for i in frepple.items() if i.owner and (not self.source or self.source == i.source) ]) print('Exported items in %.2f seconds' % (time() - starttime))
def exportItems(self, cursor): with transaction.atomic(using=self.database, savepoint=False): print("Exporting items...") starttime = time() cursor.execute("SELECT name FROM item") primary_keys = set([ i[0] for i in cursor.fetchall() ]) cursor.executemany( "insert into item \ (name,description,cost,category,subcategory,source,lastmodified) \ values(%s,%s,%s,%s,%s,%s,%s)", [ ( i.name, i.description, round(i.cost, 6), i.category, i.subcategory, i.source, self.timestamp ) for i in frepple.items() if i.name not in primary_keys and (not self.source or self.source == i.source) ]) cursor.executemany( "update item \ set description=%s, cost=%s, category=%s, subcategory=%s, source=%s, lastmodified=%s \ where name=%s", [ ( i.description, round(i.cost, 6), i.category, i.subcategory, i.source, self.timestamp, i.name ) for i in frepple.items() if i.name in primary_keys and (not self.source or self.source == i.source) ]) cursor.executemany( "update item set owner_id=%s where name=%s", [ (i.owner.name, i.name) for i in frepple.items() if i.owner and (not self.source or self.source == i.source) ]) print('Exported items 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 run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple # Uncomment the following lines to bypass the connection to odoo and use # a XML flat file alternative. This can be useful for debugging. #with open("my_path/my_data_file.xml", 'rb') as f: # frepple.readXMLdata(f.read().decode('utf-8'), False, False) # frepple.printsize() # return odoo_user = Parameter.getValue("odoo.user", database) odoo_password = settings.ODOO_PASSWORDS.get(database, None) if not settings.ODOO_PASSWORDS.get(database): odoo_password = Parameter.getValue("odoo.password", database) odoo_db = Parameter.getValue("odoo.db", database) odoo_url = Parameter.getValue("odoo.url", database) odoo_company = Parameter.getValue("odoo.company", database) ok = True if not odoo_user: logger.error("Missing or invalid parameter odoo.user") ok = False if not odoo_password: logger.error("Missing or invalid parameter odoo.password") ok = False if not odoo_db: logger.error("Missing or invalid parameter odoo.db") ok = False if not odoo_url: logger.error("Missing or invalid parameter odoo.url") ok = False if not odoo_company: logger.error("Missing or invalid parameter odoo.company") ok = False odoo_language = Parameter.getValue("odoo.language", database, 'en_US') if not ok: raise Exception("Odoo connector not configured correctly") # Assign to single roots root_item = None for r in frepple.items(): if r.owner is None: root_item = r break root_customer = None for r in frepple.customers(): if r.owner is None: root_customer = r break root_location = None for r in frepple.locations(): if r.owner is None: root_location = r break # Connect to the odoo URL to GET data url = "%sfrepple/xml?%s" % (odoo_url, urlencode({ 'database': odoo_db, 'language': odoo_language, 'company': odoo_company, 'mode': cls.mode })) try: request = Request(url) encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))[:-1] request.add_header("Authorization", "Basic %s" % encoded.decode('ascii')) except HTTPError as e: logger.error("Error connecting to odoo at %s: %s" % (url, e)) raise e # Download and parse XML data with urlopen(request) as f: frepple.readXMLdata(f.read().decode('utf-8'), False, False) # Assure single root hierarchies for r in frepple.items(): if r.owner is None and r != root_item: r.owner = root_item for r in frepple.customers(): if r.owner is None and r != root_customer: r.owner = root_customer for r in frepple.locations(): if r.owner is None and r != root_location: r.owner = root_location
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 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 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 getItemsFromCluster(cluster=-1, ): import frepple for i in frepple.items(): if i.cluster == cluster: yield "%s\n" % (clean_value(i.name), )
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 itemdistributions(): for o in frepple.items(): for i in o.itemdistributions: if not i.hidden: yield i
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple # Uncomment the following lines to bypass the connection to odoo and use # a XML flat file alternative. This can be useful for debugging. # with open("my_path/my_data_file.xml", 'rb') as f: # frepple.readXMLdata(f.read().decode('utf-8'), False, False) # frepple.printsize() # return odoo_user = Parameter.getValue("odoo.user", database) odoo_password = settings.ODOO_PASSWORDS.get(database, None) if not settings.ODOO_PASSWORDS.get(database): odoo_password = Parameter.getValue("odoo.password", database) odoo_db = Parameter.getValue("odoo.db", database, None) odoo_url = Parameter.getValue("odoo.url", database, "").strip() if not odoo_url.endswith("/"): odoo_url = odoo_url + "/" odoo_company = Parameter.getValue("odoo.company", database, None) ok = True # Set debugFile=PathToXmlFile if you want frePPLe to read that file # rather than the data at url # else leave it to False debugFile = False # "c:/temp/frepple_data.xml" if not odoo_user and not debugFile: logger.error("Missing or invalid parameter odoo.user") ok = False if not odoo_password and not debugFile: logger.error("Missing or invalid parameter odoo.password") ok = False if not odoo_db and not debugFile: logger.error("Missing or invalid parameter odoo.db") ok = False if not odoo_url and not debugFile: logger.error("Missing or invalid parameter odoo.url") ok = False if not odoo_company and not debugFile: logger.error("Missing or invalid parameter odoo.company") ok = False odoo_language = Parameter.getValue("odoo.language", database, "en_US") if not ok and not debugFile: raise Exception("Odoo connector not configured correctly") # Connect to the odoo URL to GET data try: loglevel = int(Parameter.getValue("odoo.loglevel", database, "0")) except Exception: loglevel = 0 if not debugFile: url = "%sfrepple/xml?%s" % ( odoo_url, urlencode({ "database": odoo_db, "language": odoo_language, "company": odoo_company, "mode": cls.mode, }), ) try: request = Request(url) encoded = base64.encodestring( ("%s:%s" % (odoo_user, odoo_password)).encode("utf-8"))[:-1] request.add_header("Authorization", "Basic %s" % encoded.decode("ascii")) except HTTPError as e: logger.error("Error connecting to odoo at %s: %s" % (url, e)) raise e # Download and parse XML data with urlopen(request) as f: frepple.readXMLdata(f.read().decode("utf-8"), False, False, loglevel) else: # Download and parse XML data with open(debugFile, encoding="utf-8") as f: frepple.readXMLdata(f.read(), False, False, loglevel) # Hierarchy correction: Count how many items/locations/customers have no owner # If we find 2+ then we use All items/All customers/All locations as root # otherwise we assume that the hierarchy is correct rootItem = None for r in frepple.items(): if r.owner is None: if not rootItem: rootItem = r else: rootItem = None break rootLocation = None for r in frepple.locations(): if r.owner is None: if not rootLocation: rootLocation = r else: rootLocation = None break rootCustomer = None for r in frepple.customers(): if r.owner is None: if not rootCustomer: rootCustomer = r else: rootCustomer = None break if not rootItem: rootItem = frepple.item_mts(name="All items", source="odoo_%s" % cls.mode) for r in frepple.items(): if r.owner is None and r != rootItem: r.owner = rootItem if not rootLocation: rootLocation = frepple.location(name="All locations", source="odoo_%s" % cls.mode) for r in frepple.locations(): if r.owner is None and r != rootLocation: r.owner = rootLocation if not rootCustomer: rootCustomer = frepple.customer(name="All customers", source="odoo_%s" % cls.mode) for r in frepple.customers(): if r.owner is None and r != rootCustomer: r.owner = rootCustomer
def itemdistributions(): for o in frepple.items(): for i in o.itemdistributions: yield i
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))