def loadDistributionOrders(self): print('Importing distribution orders...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT destination_id, id, reference, item_id, origin_id, quantity, startdate, enddate, consume_material, status, source FROM distribution_order WHERE status <> 'closed' %s ORDER BY id ASC ''' % self.filter_and) for i in self.cursor.fetchall(): cnt += 1 try: frepple.operation_itemdistribution.createOrder( destination=frepple.location(name=i[0]), id=i[1], reference=i[2], item=frepple.item(name=i[3]) if i[3] else None, origin=frepple.location(name=i[4]) if i[4] else None, quantity=i[5], start=i[6], end=i[7], consume_material=i[8] if i[8] != None else True, status=i[9], source=i[10] ) except Exception as e: print("Error:", e) print('Loaded %d distribution orders in %.2f seconds' % (cnt, time() - starttime))
def loadLocations(self): print('Importing locations...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT name, description, owner_id, available_id, category, subcategory, source FROM location %s ''' % self.filter_where) for i in self.cursor.fetchall(): cnt += 1 try: x = frepple.location(name=i[0], description=i[1], category=i[4], subcategory=i[5], source=i[6]) if i[2]: x.owner = frepple.location(name=i[2]) if i[3]: x.available = frepple.calendar(name=i[3]) except Exception as e: print("Error:", e) print('Loaded %d locations in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(self): print('Importing buffers...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT name, description, location_id, item_id, onhand, minimum, minimum_calendar_id, type, min_interval, category, subcategory, source FROM buffer %s ''' % self.filter_where) for i in self.cursor.fetchall(): cnt += 1 if i[7] == "infinite": b = frepple.buffer_infinite( name=i[0], description=i[1], location=frepple.location(name=i[2]), item=frepple.item(name=i[3]), onhand=i[4], category=i[9], subcategory=i[10], source=i[11] ) elif not i[7] or i[7] == "default": b = frepple.buffer( name=i[0], description=i[1], location=frepple.location(name=i[2]), item=frepple.item(name=i[3]), onhand=i[4], category=i[9], subcategory=i[10], source=i[11] ) if i[8]: b.mininterval = i[8].total_seconds() else: raise ValueError("Buffer type '%s' not recognized" % i[7]) if i[11] == 'tool': b.tool = True if i[5]: b.minimum = i[5] if i[6]: b.minimum_calendar = frepple.calendar(name=i[6]) print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple if cls.filter: filter_and = "and %s " % cls.filter filter_where = "where %s " % cls.filter else: filter_and = "" filter_where = "" with connections[database].chunked_cursor() as cursor: cnt = 0 starttime = time() cursor.execute(''' SELECT name, description, owner_id, available_id, category, subcategory, source FROM location %s ''' % filter_where) for i in cursor: cnt += 1 try: x = frepple.location(name=i[0], description=i[1], category=i[4], subcategory=i[5], source=i[6]) if i[2]: x.owner = frepple.location(name=i[2]) if i[3]: x.available = frepple.calendar(name=i[3]) except Exception as e: logger.error("**** %s ****" % e) logger.info('Loaded %d locations in %.2f seconds' % (cnt, time() - starttime))
def loadDistributionOrders(self): print('Importing distribution orders...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT destination_id, id, reference, item_id, origin_id, quantity, startdate, enddate, consume_material, status, source FROM distribution_order WHERE status <> 'closed' %s ORDER BY id ASC ''' % self.filter_and) for i in self.cursor.fetchall(): cnt += 1 try: frepple.operation_itemdistribution.createOrder( destination=frepple.location(name=i[0]), id=i[1], reference=i[2], item=frepple.item(name=i[3]) if i[3] else None, origin=frepple.location(name=i[4]) if i[4] else None, quantity=i[5], start=i[6], end=i[7], consume_material=i[8] if i[8] != None else True, status=i[9], source=i[10]) except Exception as e: print("Error:", e) print('Loaded %d distribution orders in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple if cls.filter: filter_and = "and %s " % cls.filter filter_where = "where %s " % cls.filter else: filter_and = "" filter_where = "" with connections[database].chunked_cursor() as cursor: cnt = 0 starttime = time() cursor.execute(''' SELECT origin_id, item_id, location_id, sizeminimum, sizemultiple, cost, priority, effective_start, effective_end, source, leadtime, resource_id, resource_qty, fence FROM itemdistribution %s ORDER BY origin_id, item_id, location_id, priority desc ''' % filter_where) curoriginname = None curitemname = None for i in cursor: cnt += 1 try: if i[0] != curoriginname: curoriginname = i[0] curorigin = frepple.location(name=curoriginname) if i[1] != curitemname: curitemname = i[1] curitem = frepple.item(name=curitemname) curitemdistribution = frepple.itemdistribution( origin=curorigin, item=curitem, source=i[9], leadtime=i[10].total_seconds() if i[10] else 0, fence=i[13].total_seconds() if i[13] else 0, resource_qty=i[12] ) if i[2]: curitemdistribution.destination = frepple.location(name=i[2]) if i[3]: curitemdistribution.size_minimum = i[3] if i[4]: curitemdistribution.size_multiple = i[4] if i[5]: curitemdistribution.cost = i[5] if i[6]: curitemdistribution.priority = i[6] if i[7]: curitemdistribution.effective_start = i[7] if i[8]: curitemdistribution.effective_end = i[8] if i[11]: curitemdistribution.resource = frepple.resource(name=i[11]) except Exception as e: logger.error("**** %s ****" % e) logger.info('Loaded %d item distributions in %.2f seconds' % (cnt, time() - starttime))
def loadLocations(cursor): print('Importing locations...') cnt = 0 starttime = time() cursor.execute("SELECT name, description, owner_id, available_id, category, subcategory FROM location") for i,j,k,l,m,n in cursor.fetchall(): cnt += 1 try: x = frepple.location(name=i, description=j, category=m, subcategory=n) if k: x.owner = frepple.location(name=k) if l: x.available = frepple.calendar(name=l) except Exception as e: print("Error:", e) print('Loaded %d locations in %.2f seconds' % (cnt, time() - starttime))
def loadItemDistributions(self): print('Importing item distributions...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT origin_id, item_id, location_id, sizeminimum, sizemultiple, cost, priority, effective_start, effective_end, source, leadtime, resource_id, resource_qty, fence FROM itemdistribution %s ORDER BY origin_id, item_id, location_id, priority desc ''' % self.filter_where) curoriginname = None curitemname = None for i in self.cursor.fetchall(): cnt += 1 try: if i[0] != curoriginname: curoriginname = i[0] curorigin = frepple.location(name=curoriginname) if i[1] != curitemname: curitemname = i[1] curitem = frepple.item(name=curitemname) curitemdistribution = frepple.itemdistribution( origin=curorigin, item=curitem, source=i[9], leadtime=i[10].total_seconds() if i[10] else 0, fence=i[13].total_seconds() if i[13] else 0, resource_qty=i[12]) if i[2]: curitemdistribution.destination = frepple.location( name=i[2]) if i[3]: curitemdistribution.size_minimum = i[3] if i[4]: curitemdistribution.size_multiple = i[4] if i[5]: curitemdistribution.cost = i[5] if i[6]: curitemdistribution.priority = i[6] if i[7]: curitemdistribution.effective_start = i[7] if i[8]: curitemdistribution.effective_end = i[8] if i[11]: curitemdistribution.resource = frepple.resource(name=i[11]) except Exception as e: print("Error:", e) print('Loaded %d item itemdistributions in %.2f seconds' % (cnt, time() - starttime))
def loadItemDistributions(self): print('Importing item distributions...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT origin_id, item_id, location_id, sizeminimum, sizemultiple, cost, priority, effective_start, effective_end, source, leadtime, resource_id, resource_qty, fence FROM itemdistribution %s ORDER BY origin_id, item_id, location_id, priority desc ''' % self.filter_where) curoriginname = None curitemname = None for i in self.cursor.fetchall(): cnt += 1 try: if i[0] != curoriginname: curoriginname = i[0] curorigin = frepple.location(name=curoriginname) if i[1] != curitemname: curitemname = i[1] curitem = frepple.item(name=curitemname) curitemdistribution = frepple.itemdistribution( origin=curorigin, item=curitem, source=i[9], leadtime=i[10].total_seconds() if i[10] else 0, fence=i[13].total_seconds() if i[13] else 0, resource_qty=i[12] ) if i[2]: curitemdistribution.destination = frepple.location(name=i[2]) if i[3]: curitemdistribution.size_minimum = i[3] if i[4]: curitemdistribution.size_multiple = i[4] if i[5]: curitemdistribution.cost = i[5] if i[6]: curitemdistribution.priority = i[6] if i[7]: curitemdistribution.effective_start = i[7] if i[8]: curitemdistribution.effective_end = i[8] if i[11]: curitemdistribution.resource = frepple.resource(name=i[11]) except Exception as e: print("Error:", e) print('Loaded %d item itemdistributions in %.2f seconds' % (cnt, time() - starttime))
def loadPurchaseOrders(self): print('Importing purchase orders...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT location_id, id, reference, item_id, supplier_id, quantity, startdate, enddate, status, source FROM purchase_order WHERE status <> 'closed' %s ORDER BY id ASC ''' % self.filter_and) for i in self.cursor.fetchall(): cnt += 1 try: frepple.operation_itemsupplier.createOrder( location=frepple.location(name=i[0]), id=i[1], reference=i[2], item=frepple.item(name=i[3]) if i[3] else None, supplier=frepple.supplier(name=i[4]) if i[4] else None, quantity=i[5], start=i[6], end=i[7], status=i[8], source=i[9]) except Exception as e: print("Error:", e) print('Loaded %d purchase orders in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(self): print('Importing buffers...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT name, description, location_id, item_id, onhand, minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory, max_inventory, min_interval, max_interval, size_minimum, size_multiple, size_maximum, fence, carrying_cost, category, subcategory, source FROM buffer %s ''' % self.filter_where) for i in self.cursor.fetchall(): cnt += 1 if i[8] == "procure": b = frepple.buffer_procure( name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4], category=i[19], subcategory=i[20], source=i[21] ) if i[9]: b.leadtime = i[9] if i[10]: b.mininventory = i[10] if i[11]: b.maxinventory = i[11] if i[14]: b.size_minimum = i[14] if i[15]: b.size_multiple = i[15] if i[16]: b.size_maximum = i[16] if i[17]: b.fence = i[17] elif i[8] == "infinite": b = frepple.buffer_infinite( name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4], category=i[19], subcategory=i[20], source=i[21] ) elif not i[8] or i[8] == "default": b = frepple.buffer( name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4], category=i[19], subcategory=i[20], source=i[21] ) else: raise ValueError("Buffer type '%s' not recognized" % i[8]) if i[2]: b.location = frepple.location(name=i[2]) if i[5]: b.minimum = i[5] if i[6]: b.minimum_calendar = frepple.calendar(name=i[6]) if i[7]: b.producing = frepple.operation(name=i[7]) if i[18]: b.carrying_cost = i[18] if i[12]: b.mininterval = i[12] if i[13]: b.maxinterval = i[13] print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadResources(cursor): print('Importing resources...') cnt = 0 starttime = time() Resource.rebuildHierarchy(database=cursor.db.alias) cursor.execute('''SELECT name, description, maximum, maximum_calendar_id, location_id, type, cost, maxearly, setup, setupmatrix_id, category, subcategory, owner_id FROM %s order by lvl asc, name'''% connections[database].ops.quote_name('resource')) for i,j,t,k,l,m,n,o,p,q,r,s,u in cursor.fetchall(): cnt += 1 try: if m == "infinite": x = frepple.resource_infinite(name=i,description=j,category=r,subcategory=s) elif not m or m == "default": x = frepple.resource_default(name=i,description=j,category=r,subcategory=s) if k: x.maximum_calendar = frepple.calendar(name=k) if o: x.maxearly = o if t: x.maximum = t else: raise ValueError("Resource type '%s' not recognized" % m) if l: x.location = frepple.location(name=l) if n: x.cost = n if p: x.setup = p if q: x.setupmatrix = frepple.setupmatrix(name=q) if u: x.owner = frepple.resource(name=u) except Exception as e: print("Error:", e) print('Loaded %d resources in %.2f seconds' % (cnt, time() - starttime))
def loadPurchaseOrders(self): print('Importing purchase orders...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT location_id, id, reference, item_id, supplier_id, quantity, startdate, enddate, status, source FROM purchase_order WHERE status <> 'closed' %s ORDER BY id ASC ''' % self.filter_and) for i in self.cursor.fetchall(): cnt += 1 try: frepple.operation_itemsupplier.createOrder( location=frepple.location(name=i[0]), id=i[1], reference=i[2], item=frepple.item(name=i[3]) if i[3] else None, supplier=frepple.supplier(name=i[4]) if i[4] else None, quantity=i[5], start=i[6], end=i[7], status=i[8], source=i[9] ) except Exception as e: print("Error:", e) print('Loaded %d purchase orders in %.2f seconds' % (cnt, time() - starttime))
def loadDemand(self): print('Importing demands...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT name, due, quantity, priority, item_id, operation_id, customer_id, owner_id, minshipment, maxlateness, category, subcategory, source, location_id, status FROM demand WHERE (status IS NULL OR status ='open' OR status = 'quote') %s ''' % self.filter_and) for i in self.cursor.fetchall(): cnt += 1 try: x = frepple.demand( name=i[0], due=i[1], quantity=i[2], priority=i[3], status=i[14], item=frepple.item(name=i[4]), category=i[10], subcategory=i[11], source=i[12] ) if i[5]: x.operation = frepple.operation(name=i[5]) if i[6]: x.customer = frepple.customer(name=i[6]) if i[7]: x.owner = frepple.demand(name=i[7]) if i[8]: x.minshipment = i[8] if i[9] is not None: x.maxlateness = i[9] if i[13]: x.location = frepple.location(name=i[13]) except Exception as e: print("Error:", e) print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadDemand(self): print('Importing demands...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT name, due, quantity, priority, item_id, operation_id, customer_id, owner_id, minshipment, maxlateness, category, subcategory, source, location_id, status FROM demand WHERE (status IS NULL OR status ='open' OR status = 'quote') %s ''' % self.filter_and) for i in self.cursor.fetchall(): cnt += 1 try: x = frepple.demand( name=i[0], due=i[1], quantity=i[2], priority=i[3], status=i[14], item=frepple.item(name=i[4]), category=i[10], subcategory=i[11], source=i[12] ) if i[5]: x.operation = frepple.operation(name=i[5]) if i[6]: x.customer = frepple.customer(name=i[6]) if i[7]: x.owner = frepple.demand(name=i[7]) if i[8]: x.minshipment = i[8] if i[9] is not None: x.maxlateness = i[9].total_seconds() if i[13]: x.location = frepple.location(name=i[13]) except Exception as e: print("Error:", e) print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(self): print('Importing buffers...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT name, description, location_id, item_id, onhand, minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory, max_inventory, min_interval, max_interval, size_minimum, size_multiple, size_maximum, fence, category, subcategory, source FROM buffer %s ''' % self.filter_where) for i in self.cursor.fetchall(): cnt += 1 if i[8] == "procure": b = frepple.buffer_procure( name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4], category=i[18], subcategory=i[19], source=i[20] ) if i[9]: b.leadtime = i[9] if i[10]: b.mininventory = i[10] if i[11]: b.maxinventory = i[11] if i[14]: b.size_minimum = i[14] if i[15]: b.size_multiple = i[15] if i[16]: b.size_maximum = i[16] if i[17]: b.fence = i[17] elif i[8] == "infinite": b = frepple.buffer_infinite( name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4], category=i[18], subcategory=i[19], source=i[20] ) elif not i[8] or i[8] == "default": b = frepple.buffer( name=i[0], description=i[1], item=frepple.item(name=i[3]), onhand=i[4], category=i[18], subcategory=i[19], source=i[20] ) else: raise ValueError("Buffer type '%s' not recognized" % i[8]) if i[20] == 'tool': b.tool = True if i[2]: b.location = frepple.location(name=i[2]) if i[5]: b.minimum = i[5] if i[6]: b.minimum_calendar = frepple.calendar(name=i[6]) if i[7]: b.producing = frepple.operation(name=i[7]) if i[12]: b.mininterval = i[12] if i[13]: b.maxinterval = i[13] print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadOperations(self): print('Importing operations...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT name, fence, pretime, posttime, sizeminimum, sizemultiple, sizemaximum, type, duration, duration_per, location_id, cost, search, description, category, subcategory, source FROM operation %s ''' % self.filter_where) for i in self.cursor.fetchall(): cnt += 1 try: if not i[7] or i[7] == "fixed_time": x = frepple.operation_fixed_time( name=i[0], description=i[13], category=i[14], subcategory=i[15], source=i[16] ) if i[8]: x.duration = i[8] elif i[7] == "time_per": x = frepple.operation_time_per( name=i[0], description=i[13], category=i[14], subcategory=i[15], source=i[16] ) if i[8]: x.duration = i[8] if i[9]: x.duration_per = i[9] elif i[7] == "alternate": x = frepple.operation_alternate( name=i[0], description=i[13], category=i[14], subcategory=i[15], source=i[16] ) elif i[7] == "routing": x = frepple.operation_routing( name=i[0], description=i[13], category=i[14], subcategory=i[15], source=i[16] ) else: raise ValueError("Operation type '%s' not recognized" % i[7]) if i[1]: x.fence = i[1] if i[2]: x.pretime = i[2] if i[3]: x.posttime = i[3] if i[4]: x.size_minimum = i[4] if i[5]: x.size_multiple = i[5] if i[6]: x.size_maximum = i[6] if i[10]: x.location = frepple.location(name=i[10]) if i[11]: x.cost = i[11] if i[12]: x.search = i[12] except Exception as e: print("Error:", e) print('Loaded %d operations in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple if cls.filter: filter_and = "and %s " % cls.filter filter_where = "where %s " % cls.filter else: filter_and = "" filter_where = "" with connections[database].cursor() as cursor: cnt = 0 starttime = time() cursor.execute(''' SELECT supplier_id, item_id, location_id, sizeminimum, sizemultiple, cost, priority, effective_start, effective_end, source, leadtime, resource_id, resource_qty, fence FROM itemsupplier %s ORDER BY supplier_id, item_id, location_id, priority desc ''' % filter_where) cursuppliername = None curitemname = None for i in cursor.fetchall(): cnt += 1 try: if i[0] != cursuppliername: cursuppliername = i[0] cursupplier = frepple.supplier(name=cursuppliername) if i[1] != curitemname: curitemname = i[1] curitem = frepple.item(name=curitemname) curitemsupplier = frepple.itemsupplier( supplier=cursupplier, item=curitem, source=i[9], leadtime=i[10].total_seconds() if i[10] else 0, fence=i[13].total_seconds() if i[13] else 0, resource_qty=i[12] ) if i[2]: curitemsupplier.location = frepple.location(name=i[2]) if i[3]: curitemsupplier.size_minimum = i[3] if i[4]: curitemsupplier.size_multiple = i[4] if i[5]: curitemsupplier.cost = i[5] if i[6]: curitemsupplier.priority = i[6] if i[7]: curitemsupplier.effective_start = i[7] if i[8]: curitemsupplier.effective_end = i[8] if i[11]: curitemsupplier.resource = frepple.resource(name=i[11]) except Exception as e: print("Error:", e) print('Loaded %d item suppliers in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple if cls.filter: filter_and = "and %s " % cls.filter filter_where = "where %s " % cls.filter else: filter_and = "" filter_where = "" with connections[database].chunked_cursor() as cursor: cnt = 0 starttime = time() cursor.execute(''' SELECT name, description, location_id, item_id, onhand, minimum, minimum_calendar_id, type, min_interval, category, subcategory, source FROM buffer %s ''' % filter_where) for i in cursor: cnt += 1 if i[7] == "infinite": b = frepple.buffer_infinite( name=i[0], description=i[1], location=frepple.location(name=i[2]), item=frepple.item(name=i[3]), onhand=max(i[4] or 0, 0), category=i[9], subcategory=i[10], source=i[11] ) elif not i[7] or i[7] == "default": b = frepple.buffer( name=i[0], description=i[1], location=frepple.location(name=i[2]), item=frepple.item(name=i[3]), onhand=max(i[4] or 0, 0), category=i[9], subcategory=i[10], source=i[11] ) if i[8]: b.mininterval = i[8].total_seconds() else: raise ValueError("Buffer type '%s' not recognized" % i[7]) if i[10] == 'tool': b.tool = True if i[5]: b.minimum = i[5] if i[6]: b.minimum_calendar = frepple.calendar(name=i[6]) logger.info('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadLocations(cursor): print('Importing locations...') cnt = 0 starttime = time() cursor.execute( "SELECT name, description, owner_id, available_id, category, subcategory FROM location" ) for i, j, k, l, m, n in cursor.fetchall(): cnt += 1 try: x = frepple.location(name=i, description=j, category=m, subcategory=n) if k: x.owner = frepple.location(name=k) if l: x.available = frepple.calendar(name=l) except Exception as e: print("Error:", e) print('Loaded %d locations in %.2f seconds' % (cnt, time() - starttime))
def loadResources(self): print('Importing resources...') cnt = 0 starttime = time() Resource.rebuildHierarchy(database=self.database) self.cursor.execute(''' SELECT name, description, maximum, maximum_calendar_id, location_id, type, cost, maxearly, setup, setupmatrix_id, category, subcategory, owner_id, source FROM %s %s ORDER BY lvl ASC, name ''' % (connections[self.cursor.db.alias].ops.quote_name('resource'), self.filter_where) ) for i in self.cursor.fetchall(): cnt += 1 try: if i[5] == "infinite": x = frepple.resource_infinite( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) elif i[5] == "buckets": x = frepple.resource_buckets( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) if i[3]: x.maximum_calendar = frepple.calendar(name=i[3]) if i[7]: x.maxearly = i[7] elif not i[5] or i[5] == "default": x = frepple.resource_default( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) if i[3]: x.maximum_calendar = frepple.calendar(name=i[3]) if i[7]: x.maxearly = i[7] if i[2]: x.maximum = i[2] else: raise ValueError("Resource type '%s' not recognized" % i[5]) if i[4]: x.location = frepple.location(name=i[4]) if i[6]: x.cost = i[6] if i[8]: x.setup = i[8] if i[9]: x.setupmatrix = frepple.setupmatrix(name=i[9]) if i[12]: x.owner = frepple.resource(name=i[12]) except Exception as e: print("Error:", e) print('Loaded %d resources in %.2f seconds' % (cnt, time() - starttime))
def loadResources(self): print('Importing resources...') cnt = 0 starttime = time() Resource.rebuildHierarchy(database=self.database) self.cursor.execute(''' SELECT name, description, maximum, maximum_calendar_id, location_id, type, cost, maxearly, setup, setupmatrix_id, category, subcategory, owner_id, source FROM %s %s ORDER BY lvl ASC, name ''' % (connections[self.cursor.db.alias].ops.quote_name('resource'), self.filter_where) ) for i in self.cursor.fetchall(): cnt += 1 try: if i[5] == "infinite": x = frepple.resource_infinite( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) elif i[5] == "buckets": x = frepple.resource_buckets( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) if i[3]: x.maximum_calendar = frepple.calendar(name=i[3]) if i[7]: x.maxearly = i[7] elif not i[5] or i[5] == "default": x = frepple.resource_default( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) if i[3]: x.maximum_calendar = frepple.calendar(name=i[3]) if i[7]: x.maxearly = i[7].total_seconds() if i[2]: x.maximum = i[2] else: raise ValueError("Resource type '%s' not recognized" % i[5]) if i[4]: x.location = frepple.location(name=i[4]) if i[6]: x.cost = i[6] if i[8]: x.setup = i[8] if i[9]: x.setupmatrix = frepple.setupmatrix(name=i[9]) if i[12]: x.owner = frepple.resource(name=i[12]) except Exception as e: print("Error:", e) print('Loaded %d resources in %.2f seconds' % (cnt, time() - starttime))
def loadOperations(cursor): print('Importing operations...') cnt = 0 starttime = time() cursor.execute(''' SELECT name, fence, pretime, posttime, sizeminimum, sizemultiple, sizemaximum, type, duration, duration_per, location_id, cost, search, description, category, subcategory FROM operation ''') for i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x in cursor.fetchall(): cnt += 1 try: if not p or p == "fixed_time": x = frepple.operation_fixed_time(name=i, description=v, category=w, subcategory=x) if q: x.duration = q elif p == "time_per": x = frepple.operation_time_per(name=i, description=v, category=w, subcategory=x) if q: x.duration = q if r: x.duration_per = r elif p == "alternate": x = frepple.operation_alternate(name=i, description=v, category=w, subcategory=x) elif p == "routing": x = frepple.operation_routing(name=i, description=v, category=w, subcategory=x) else: raise ValueError("Operation type '%s' not recognized" % p) if j: x.fence = j if k: x.pretime = k if l: x.posttime = l if m: x.size_minimum = m if n: x.size_multiple = n if o: x.size_maximum = o if s: x.location = frepple.location(name=s) if t: x.cost = t if u: x.search = u except Exception as e: print("Error:", e) print('Loaded %d operations in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(cursor): print('Importing buffers...') cnt = 0 starttime = time() cursor.execute('''SELECT name, description, location_id, item_id, onhand, minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory, max_inventory, min_interval, max_interval, size_minimum, size_multiple, size_maximum, fence, carrying_cost, category, subcategory FROM buffer''') for i, j, k, l, m, t, n, o, q, f1, f2, f3, f4, f5, f6, f7, f8, f9, p, r, s in cursor.fetchall( ): cnt += 1 if q == "procure": b = frepple.buffer_procure(name=i, description=j, item=frepple.item(name=l), onhand=m, category=r, subcategory=s) if f1: b.leadtime = f1 if f2: b.mininventory = f2 if f3: b.maxinventory = f3 if f4: b.mininterval = f4 if f5: b.maxinterval = f5 if f6: b.size_minimum = f6 if f7: b.size_multiple = f7 if f8: b.size_maximum = f8 if f9: b.fence = f9 elif q == "infinite": b = frepple.buffer_infinite(name=i, description=j, item=frepple.item(name=l), onhand=m, category=r, subcategory=s) elif not q or q == "default": b = frepple.buffer(name=i, description=j, item=frepple.item(name=l), onhand=m, category=r, subcategory=s) else: raise ValueError("Buffer type '%s' not recognized" % q) if k: b.location = frepple.location(name=k) if t: b.minimum = t if n: b.minimum_calendar = frepple.calendar(name=n) if o: b.producing = frepple.operation(name=o) if p: b.carrying_cost = p print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadItemSuppliers(self): print('Importing item suppliers...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT supplier_id, item_id, location_id, sizeminimum, sizemultiple, cost, priority, effective_start, effective_end, source, leadtime, resource_id, resource_qty FROM itemsupplier %s ORDER BY supplier_id, item_id, location_id, priority desc ''' % self.filter_where) cursuppliername = None curitemname = None for i in self.cursor.fetchall(): cnt += 1 try: if i[0] != cursuppliername: cursuppliername = i[0] cursupplier = frepple.supplier(name=cursuppliername) if i[1] != curitemname: curitemname = i[1] curitem = frepple.item(name=curitemname) curitemsupplier = frepple.itemsupplier(supplier=cursupplier, item=curitem, source=i[9], leadtime=i[10] or 0, resource_qty=i[12]) if i[2]: curitemsupplier.location = frepple.location(name=i[2]) if i[3]: curitemsupplier.size_minimum = i[3] if i[4]: curitemsupplier.size_multiple = i[4] if i[5]: curitemsupplier.cost = i[5] if i[6]: curitemsupplier.priority = i[6] if i[7]: curitemsupplier.effective_start = i[7] if i[8]: curitemsupplier.effective_end = i[8] if i[11]: curitemsupplier.resource = frepple.resource(name=i[11]) except Exception as e: print("Error:", e) print('Loaded %d item suppliers in %.2f seconds' % (cnt, time() - starttime))
def loadItemSuppliers(self): print('Importing item suppliers...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT supplier_id, item_id, location_id, sizeminimum, sizemultiple, cost, priority, effective_start, effective_end, source, leadtime, resource_id, resource_qty FROM itemsupplier %s ORDER BY supplier_id, item_id, location_id, priority desc ''' % self.filter_where) cursuppliername = None curitemname = None for i in self.cursor.fetchall(): cnt += 1 try: if i[0] != cursuppliername: cursuppliername = i[0] cursupplier = frepple.supplier(name=cursuppliername) if i[1] != curitemname: curitemname = i[1] curitem = frepple.item(name=curitemname) curitemsupplier = frepple.itemsupplier( supplier=cursupplier, item=curitem, source=i[9], leadtime=i[10] or 0, resource_qty=i[12] ) if i[2]: curitemsupplier.location = frepple.location(name=i[2]) if i[3]: curitemsupplier.size_minimum = i[3] if i[4]: curitemsupplier.size_multiple = i[4] if i[5]: curitemsupplier.cost = i[5] if i[6]: curitemsupplier.priority = i[6] if i[7]: curitemsupplier.effective_start = i[7] if i[8]: curitemsupplier.effective_end = i[8] if i[11]: curitemsupplier.resource = frepple.resource(name=i[11]) except Exception as e: print("Error:", e) print('Loaded %d item suppliers in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple if cls.filter: filter_and = "and %s " % cls.filter filter_where = "where %s " % cls.filter else: filter_and = "" filter_where = "" with connections[database].chunked_cursor() as cursor: cnt = 0 starttime = time() cursor.execute(''' SELECT name, due, quantity, priority, item_id, operation_id, customer_id, owner_id, minshipment, maxlateness, category, subcategory, source, location_id, status FROM demand WHERE (status IS NULL OR status ='open' OR status = 'quote') %s ''' % filter_and) for i in cursor: cnt += 1 try: x = frepple.demand( name=i[0], due=i[1], quantity=i[2], priority=i[3], status=i[14], item=frepple.item(name=i[4]), category=i[10], subcategory=i[11], source=i[12] ) if i[5]: x.operation = frepple.operation(name=i[5]) if i[6]: x.customer = frepple.customer(name=i[6]) if i[7]: x.owner = frepple.demand(name=i[7]) if i[8] is not None: x.minshipment = i[8] if i[9] is not None: x.maxlateness = i[9].total_seconds() if i[13]: x.location = frepple.location(name=i[13]) except Exception as e: logger.error("**** %s ****" % e) logger.info('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def loadBuffers(cursor): print('Importing buffers...') cnt = 0 starttime = time() cursor.execute('''SELECT name, description, location_id, item_id, onhand, minimum, minimum_calendar_id, producing_id, type, leadtime, min_inventory, max_inventory, min_interval, max_interval, size_minimum, size_multiple, size_maximum, fence, carrying_cost, category, subcategory FROM buffer''') for i,j,k,l,m,t,n,o,q,f1,f2,f3,f4,f5,f6,f7,f8,f9,p,r,s in cursor.fetchall(): cnt += 1 if q == "procure": b = frepple.buffer_procure( name=i, description=j, item=frepple.item(name=l), onhand=m, category=r, subcategory=s ) if f1: b.leadtime = f1 if f2: b.mininventory = f2 if f3: b.maxinventory = f3 if f4: b.mininterval = f4 if f5: b.maxinterval = f5 if f6: b.size_minimum = f6 if f7: b.size_multiple = f7 if f8: b.size_maximum = f8 if f9: b.fence = f9 elif q == "infinite": b = frepple.buffer_infinite( name=i, description=j, item=frepple.item(name=l), onhand=m, category=r, subcategory=s ) elif not q or q == "default": b = frepple.buffer( name=i, description=j, item=frepple.item(name=l), onhand=m, category=r, subcategory=s ) else: raise ValueError("Buffer type '%s' not recognized" % q) if k: b.location = frepple.location(name=k) if t: b.minimum = t if n: b.minimum_calendar = frepple.calendar(name=n) if o: b.producing = frepple.operation(name=o) if p: b.carrying_cost = p print('Loaded %d buffers in %.2f seconds' % (cnt, time() - starttime))
def loadResources(cursor): print('Importing resources...') cnt = 0 starttime = time() Resource.rebuildHierarchy(database=cursor.db.alias) cursor.execute('''SELECT name, description, maximum, maximum_calendar_id, location_id, type, cost, maxearly, setup, setupmatrix_id, category, subcategory, owner_id FROM %s order by lvl asc, name''' % connections[cursor.db.alias].ops.quote_name('resource')) for i, j, t, k, l, m, n, o, p, q, r, s, u in cursor.fetchall(): cnt += 1 try: if m == "infinite": x = frepple.resource_infinite(name=i, description=j, category=r, subcategory=s) elif not m or m == "default": x = frepple.resource_default(name=i, description=j, category=r, subcategory=s) if k: x.maximum_calendar = frepple.calendar(name=k) if o: x.maxearly = o if t: x.maximum = t else: raise ValueError("Resource type '%s' not recognized" % m) if l: x.location = frepple.location(name=l) if n: x.cost = n if p: x.setup = p if q: x.setupmatrix = frepple.setupmatrix(name=q) if u: x.owner = frepple.resource(name=u) except Exception as e: print("Error:", e) print('Loaded %d resources in %.2f seconds' % (cnt, time() - starttime))
def loadOperationPlans(self): # TODO if we are going to replan anyway, we can skip loading the proposed operationplans print('Importing operationplans...') if 'supply' in os.environ: confirmed_filter = " and operationplan.status = 'confirmed'" else: confirmed_filter = "" cnt_mo = 0 cnt_po = 0 cnt_do = 0 cnt_dlvr = 0 starttime = time() self.cursor.execute(''' SELECT operationplan.operation_id, operationplan.id, operationplan.quantity, operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.source, operationplan.type, operationplan.origin_id, operationplan.destination_id, operationplan.supplier_id, operationplan.item_id, operationplan.location_id, reference, coalesce(dmd.name, null) FROM operationplan LEFT OUTER JOIN (select name from demand where demand.status = 'open' ) dmd on dmd.name = operationplan.demand_id WHERE operationplan.owner_id IS NULL and operationplan.quantity >= 0 and operationplan.status <> 'closed' %s%s and operationplan.type in ('PO', 'MO', 'DO', 'DLVR') ORDER BY operationplan.id ASC ''' % (self.filter_and, confirmed_filter)) for i in self.cursor.fetchall(): try: if i[7] == 'MO': cnt_mo += 1 opplan = frepple.operationplan( operation=frepple.operation(name=i[0]), id=i[1], quantity=i[2], source=i[6], start=i[3], end=i[4], status=i[5], reference=i[13] ) elif i[7] == 'PO': cnt_po += 1 opplan = frepple.operationplan( location=frepple.location(name=i[12]), ordertype=i[7], id=i[1], reference=i[12], item=frepple.item(name=i[11]) if i[11] else None, supplier=frepple.supplier(name=i[10]) if i[10] else None, quantity=i[2], start=i[3], end=i[4], status=i[5], source=i[6] ) elif i[7] == 'DO': cnt_do += 1 opplan = frepple.operationplan( location=frepple.location(name=i[9]) if i[9] else None, id=i[1], reference=i[12], ordertype=i[7], item=frepple.item(name=i[11]) if i[11] else None, origin=frepple.location(name=i[8]) if i[8] else None, quantity=i[2], start=i[3], end=i[4], status=i[5], source=i[6] ) elif i[7] == 'DLVR': cnt_dlvr += 1 opplan = frepple.operationplan( location=frepple.location(name=i[12]) if i[12] else None, id=i[1], reference=i[12], ordertype=i[7], item=frepple.item(name=i[11]) if i[11] else None, origin=frepple.location(name=i[8]) if i[8] else None, demand=frepple.demand(name=i[14]) if i[14] else None, quantity=i[2], start=i[3], end=i[4], status=i[5], source=i[6] ) opplan = None else: print("Warning: unhandled operationplan type '%s'" % i[7]) continue if i[14] and opplan: opplan.demand = frepple.demand(name=i[14]) except Exception as e: print("Error:", e) self.cursor.execute(''' SELECT operationplan.operation_id, operationplan.id, operationplan.quantity, operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.owner_id, operationplan.source, coalesce(dmd.name, null) FROM operationplan INNER JOIN (select id from operationplan ) opplan_parent on operationplan.owner_id = opplan_parent.id LEFT OUTER JOIN (select name from demand where demand.status = 'open' ) dmd on dmd.name = operationplan.demand_id WHERE operationplan.quantity >= 0 and operationplan.status <> 'closed' %s%s and operationplan.type = 'MO' ORDER BY operationplan.id ASC ''' % (self.filter_and, confirmed_filter)) for i in self.cursor.fetchall(): cnt_mo += 1 opplan = frepple.operationplan( operation=frepple.operation(name=i[0]), id=i[1], quantity=i[2], source=i[7], owner=frepple.operationplan(id=i[6]), start=i[3], end=i[4], status=i[5] ) if i[8]: opplan.demand = frepple.demand(name=i[8]) print('Loaded %d manufacturing orders, %d purchase orders, %d distribution orders and %s deliveries in %.2f seconds' % (cnt_mo, cnt_po, cnt_do, cnt_dlvr, time() - starttime)) # Assure the operationplan ids will be unique. # We call this method only at the end, as calling it earlier gives a slower # performance to load operationplans self.cursor.execute(''' select coalesce(max(id),1) + 1 as max_id from operationplan ''') d = self.cursor.fetchone() frepple.settings.id = d[0]
print("makeoper == item", makeoper == item) except Exception as e: print("Catching exception %s: %s" % (e.__class__.__name__, e)) ### print("\nCreating a resource") frepple.resource(name="machine", maximum_calendar=frepple.calendar(name="Cal2")) ### print("\nCreating customers") mycustomer = frepple.customer(name="client") ### print("\nCreating locations") locA = frepple.location(name="locA") locB = frepple.location(name="locB") ### print("\nCreating some buffers") buf = frepple.buffer(name="end item", producing=choice, item=item) buf1 = frepple.buffer_procure(name="buffer1", description="My description", category="My category", location=locA, item=itemlist[1]) print(buf1, buf1.__class__, buf1.location, isinstance(buf1, frepple.buffer), \ isinstance(buf1, frepple.buffer_default), \ isinstance(buf1, frepple.buffer_procure), \
def loadOperations(self): print('Importing operations...') cnt = 0 starttime = time() self.cursor.execute(''' SELECT 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 FROM operation %s ''' % self.filter_where) for i in self.cursor.fetchall(): cnt += 1 try: if not i[6] or i[6] == "fixed_time": x = frepple.operation_fixed_time( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) if i[7]: x.duration = i[7].total_seconds() elif i[6] == "time_per": x = frepple.operation_time_per( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) if i[7]: x.duration = i[7].total_seconds() if i[8]: x.duration_per = i[8].total_seconds() elif i[6] == "alternate": x = frepple.operation_alternate( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) elif i[6] == "split": x = frepple.operation_split( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) elif i[6] == "routing": x = frepple.operation_routing( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) else: raise ValueError("Operation type '%s' not recognized" % i[6]) if i[1]: x.fence = i[1].total_seconds() if i[2]: x.posttime = i[2].total_seconds() if i[3] is not None: x.size_minimum = i[3] if i[4]: x.size_multiple = i[4] if i[5]: x.size_maximum = i[5] if i[9]: x.location = frepple.location(name=i[9]) if i[10]: x.cost = i[10] if i[11]: x.search = i[11] if i[16]: x.item = frepple.item(name=i[16]) if i[17] is not None: x.priority = i[17] if i[18]: x.effective_start = i[18] if i[19]: x.effective_end = i[19] except Exception as e: print("Error:", e) print('Loaded %d operations in %.2f seconds' % (cnt, time() - starttime))
try: print("makeoper == item", makeoper == item) except Exception as e: print("Catching exception %s: %s" % (e.__class__.__name__, e)) ### print("\nCreating a resource") frepple.resource(name="machine", maximum_calendar=frepple.calendar(name="Cal2")) ### print("\nCreating customers") mycustomer = frepple.customer(name="client") ### print("\nCreating locations") locA = frepple.location(name="locA") locB = frepple.location(name="locB") ### print("\nCreating some buffers") buf = frepple.buffer(name="end item", producing=choice, item=item) buf1 = frepple.buffer_procure(name="buffer1", description="My description", category="My category", location=locA, item=itemlist[1]) print(buf1, buf1.__class__, buf1.location, isinstance(buf1, frepple.buffer), \ isinstance(buf1, frepple.buffer_default), \ isinstance(buf1, frepple.buffer_procure), \
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 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() Resource.rebuildHierarchy(database=database) cursor.execute(''' SELECT name, description, maximum, maximum_calendar_id, location_id, type, cost, maxearly, setup, setupmatrix_id, category, subcategory, owner_id, source, available_id FROM %s %s ORDER BY lvl ASC, name ''' % (connections[cursor.db.alias].ops.quote_name('resource'), filter_where) ) for i in cursor: cnt += 1 try: if i[5] == "infinite": x = frepple.resource_infinite( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) elif i[5] == "buckets": x = frepple.resource_buckets( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) if i[3]: x.maximum_calendar = frepple.calendar(name=i[3]) if i[7] is not None: x.maxearly = i[7] elif not i[5] or i[5] == "default": x = frepple.resource_default( name=i[0], description=i[1], category=i[10], subcategory=i[11], source=i[13] ) if i[3]: x.maximum_calendar = frepple.calendar(name=i[3]) if i[7] is not None: x.maxearly = i[7].total_seconds() if i[2] is not None: x.maximum = i[2] else: raise ValueError("Resource type '%s' not recognized" % i[5]) if i[4]: x.location = frepple.location(name=i[4]) if i[6]: x.cost = i[6] if i[8]: x.setup = i[8] if i[9]: x.setupmatrix = frepple.setupmatrix(name=i[9]) if i[12]: x.owner = frepple.resource(name=i[12]) if i[14]: x.available = frepple.calendar(name=i[14]) except Exception as e: logger.error("**** %s ****" % e) logger.info('Loaded %d resources in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple if cls.filter: filter_and = "and %s " % cls.filter filter_where = "where %s " % cls.filter else: filter_and = "" filter_where = "" with connections[database].cursor() as cursor: cnt = 0 starttime = time() # Preprocessing step # Make sure any routing has the produced item of its last step populated in the operation table cursor.execute(''' update operation set item_id = t.item_id from ( select operation.name operation_id, min(operationmaterial.item_id) item_id from operation inner join suboperation s1 on s1.operation_id = operation.name inner join operationmaterial on operationmaterial.operation_id = s1.suboperation_id and quantity > 0 where operation.type = 'routing' and not exists (select 1 from suboperation s2 where s1.operation_id = s2.operation_id and s1.priority < s2.priority) group by operation.name having count(operationmaterial.item_id) = 1 ) t where operation.type = 'routing' and operation.name = t.operation_id ''') # Preprocessing step # Make sure any regular operation (i.e. that has no suboperation and is not a suboperation) # has its item_id field populated # That should cover 90% of the cases cursor.execute(''' update operation set item_id = t.item_id from ( select operation.name operation_id, min(operationmaterial.item_id) item_id from operation inner join operationmaterial on operationmaterial.operation_id = operation.name and quantity > 0 where not exists (select 1 from suboperation where suboperation.operation_id = operation.name or suboperation.suboperation_id = operation.name) and operation.type not in ('routing', 'alternate', 'split') group by operation.name having count(operationmaterial.item_id) = 1 ) t where operation.type not in ('routing', 'alternate', 'split') and t.operation_id = operation.name ''') # Preprocessing step # Operations that are suboperation of a parent operation shouldn't have # the item field set. It is the parent operation that should have it set. cursor.execute(''' update operation set item_id = null from suboperation where operation.name = suboperation.suboperation_id and operation.item_id is not null ''') with connections[database].chunked_cursor() as cursor: cursor.execute(''' SELECT 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, available_id FROM operation %s ''' % filter_where) for i in cursor: cnt += 1 try: if not i[6] or i[6] == "fixed_time": x = frepple.operation_fixed_time( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) if i[7]: x.duration = i[7].total_seconds() elif i[6] == "time_per": x = frepple.operation_time_per( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) if i[7]: x.duration = i[7].total_seconds() if i[8]: x.duration_per = i[8].total_seconds() elif i[6] == "alternate": x = frepple.operation_alternate( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) elif i[6] == "split": x = frepple.operation_split( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) elif i[6] == "routing": x = frepple.operation_routing( name=i[0], description=i[12], category=i[13], subcategory=i[14], source=i[15] ) else: raise ValueError("Operation type '%s' not recognized" % i[6]) if i[1]: x.fence = i[1].total_seconds() if i[2]: x.posttime = i[2].total_seconds() if i[3] is not None: x.size_minimum = i[3] if i[4]: x.size_multiple = i[4] if i[5]: x.size_maximum = i[5] if i[9]: x.location = frepple.location(name=i[9]) if i[10]: x.cost = i[10] if i[11]: x.search = i[11] if i[16]: x.item = frepple.item(name=i[16]) if i[17] is not None: x.priority = i[17] if i[18]: x.effective_start = i[18] if i[19]: x.effective_end = i[19] if i[20]: x.available = frepple.calendar(name=i[20]) except Exception as e: logger.error("**** %s ****" % e) logger.info('Loaded %d operations in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs): import frepple if cls.filter: filter_and = "and %s " % cls.filter filter_where = "where %s " % cls.filter else: filter_and = "" filter_where = "" with connections[database].chunked_cursor() as cursor: if 'supply' in os.environ: confirmed_filter = " and operationplan.status in ('confirmed', 'approved')" create_flag = True else: confirmed_filter = "" create_flag = False cnt_mo = 0 cnt_po = 0 cnt_do = 0 cnt_dlvr = 0 starttime = time() cursor.execute(''' SELECT operationplan.operation_id, operationplan.id, operationplan.quantity, operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.source, operationplan.type, operationplan.origin_id, operationplan.destination_id, operationplan.supplier_id, operationplan.item_id, operationplan.location_id, reference, coalesce(dmd.name, null) FROM operationplan LEFT OUTER JOIN (select name from demand where demand.status = 'open' ) dmd on dmd.name = operationplan.demand_id WHERE operationplan.owner_id IS NULL and operationplan.quantity >= 0 and operationplan.status <> 'closed' %s%s and operationplan.type in ('PO', 'MO', 'DO', 'DLVR') ORDER BY operationplan.id ASC ''' % (filter_and, confirmed_filter)) for i in cursor: try: if i[7] == 'MO': cnt_mo += 1 opplan = frepple.operationplan( operation=frepple.operation(name=i[0]), id=i[1], quantity=i[2], source=i[6], start=i[3], end=i[4], status=i[5], reference=i[13], create=create_flag ) elif i[7] == 'PO': cnt_po += 1 opplan = frepple.operationplan( location=frepple.location(name=i[12]), ordertype=i[7], id=i[1], reference=i[13], item=frepple.item(name=i[11]) if i[11] else None, supplier=frepple.supplier(name=i[10]) if i[10] else None, quantity=i[2], start=i[3], end=i[4], status=i[5], source=i[6], create=create_flag ) elif i[7] == 'DO': cnt_do += 1 opplan = frepple.operationplan( location=frepple.location(name=i[9]) if i[9] else None, id=i[1], reference=i[13], ordertype=i[7], item=frepple.item(name=i[11]) if i[11] else None, origin=frepple.location(name=i[8]) if i[8] else None, quantity=i[2], start=i[3], end=i[4], status=i[5], source=i[6], create=create_flag ) elif i[7] == 'DLVR': cnt_dlvr += 1 opplan = frepple.operationplan( location=frepple.location(name=i[12]) if i[12] else None, id=i[1], reference=i[13], ordertype=i[7], item=frepple.item(name=i[11]) if i[11] else None, origin=frepple.location(name=i[8]) if i[8] else None, demand=frepple.demand(name=i[14]) if i[14] else None, quantity=i[2], start=i[3], end=i[4], status=i[5], source=i[6], create=create_flag ) opplan = None else: logger.warning("Warning: unhandled operationplan type '%s'" % i[7]) continue if i[14] and opplan: opplan.demand = frepple.demand(name=i[14]) except Exception as e: logger.error("**** %s ****" % e) with connections[database].chunked_cursor() as cursor: cursor.execute(''' SELECT operationplan.operation_id, operationplan.id, operationplan.quantity, operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.owner_id, operationplan.source, operationplan.reference, coalesce(dmd.name, null) FROM operationplan INNER JOIN (select id from operationplan ) opplan_parent on operationplan.owner_id = opplan_parent.id LEFT OUTER JOIN (select name from demand where demand.status = 'open' ) dmd on dmd.name = operationplan.demand_id WHERE operationplan.quantity >= 0 and operationplan.status <> 'closed' %s%s and operationplan.type = 'MO' ORDER BY operationplan.id ASC ''' % (filter_and, confirmed_filter)) for i in cursor: cnt_mo += 1 opplan = frepple.operationplan( operation=frepple.operation(name=i[0]), id=i[1], quantity=i[2], source=i[7], start=i[3], end=i[4], status=i[5], reference=i[8] ) if i[6] and opplan: try: opplan.owner = frepple.operationplan(id=i[6]) except: pass if i[9] and opplan: opplan.demand = frepple.demand(name=i[9]) logger.info('Loaded %d manufacturing orders, %d purchase orders, %d distribution orders and %s deliveries in %.2f seconds' % (cnt_mo, cnt_po, cnt_do, cnt_dlvr, time() - starttime)) with connections[database].cursor() as cursor: # Assure the operationplan ids will be unique. # We call this method only at the end, as calling it earlier gives a slower # performance to load operationplans cursor.execute(''' select coalesce(max(id),1) + 1 as max_id from operationplan ''') d = cursor.fetchone() frepple.settings.id = d[0]