def loadItems(self):
print('Importing items...')
cnt = 0
starttime = time()
attrs = [ f[0] for f in getAttributes(Item) ]
if attrs:
attrsql = ', %s' % ', '.join(attrs)
else:
attrsql = ''
self.cursor.execute('''
SELECT
name, description, operation_id, owner_id,
price, category, subcategory, source %s
FROM item %s
''' % (attrsql, self.filter_where))
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i[0], description=i[1], category=i[5], subcategory=i[6], source=i[7])
if i[2]:
x.operation = frepple.operation(name=i[2])
if i[3]:
x.owner = frepple.item(name=i[3])
if i[4]:
x.price = i[4]
idx = 8
for a in attrs:
setattr(x, a, i[idx])
idx += 1
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def 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 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 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 loadOperationMaterials(self):
print('Importing operation materials...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, item_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM operationmaterial %s
ORDER BY operation_id, item_id
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
curflow = frepple.flow(
operation=frepple.operation(name=i[0]),
item=frepple.item(name=i[1]),
quantity=i[2],
type="flow_%s" % i[3],
source=i[9]
)
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
print("Error:", e)
print('Loaded %d operation materials in %.2f seconds' % (cnt, time() - starttime))
# Check for operations where:
# - operation.item is still blank
# - they have a single operationmaterial item with quantity > 0
# If found we update
starttime = time()
cnt = 0
print('Auto-update operation items...')
for oper in frepple.operations():
if oper.hidden or oper.item:
continue
item = None
for fl in oper.flows:
if fl.quantity < 0 or fl.hidden:
continue
if item and item != fl.item:
item = None
break
else:
item = fl.item
if item:
cnt += 1
oper.item = item
print('Auto-update of %s operation items in %.2f seconds' % (cnt, time() - starttime))
def loadOperationMaterials(self):
print('Importing operation materials...')
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
self.cursor.execute('''
SELECT
operation_id, item_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM operationmaterial %s
ORDER BY operation_id, item_id
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
curflow = frepple.flow(
operation=frepple.operation(name=i[0]),
item=frepple.item(name=i[1]),
quantity=i[2],
type="flow_%s" % i[3],
source=i[9]
)
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
print("Error:", e)
print('Loaded %d operation materials in %.2f seconds' % (cnt, time() - starttime))
# Check for operations where:
# - operation.item is still blank
# - they have a single operationmaterial item with quantity > 0
# If found we update
starttime = time()
cnt = 0
print('Auto-update operation items...')
for oper in frepple.operations():
if oper.hidden or oper.item:
continue
item = None
for fl in oper.flows:
if fl.quantity < 0 or fl.hidden:
continue
if item and item != fl.item:
item = None
break
else:
item = fl.item
if item:
cnt += 1
oper.item = item
print('Auto-update of %s operation items in %.2f seconds' % (cnt, time() - starttime))
def loadDemand(cursor):
print('Importing demands...')
cnt = 0
starttime = time()
cursor.execute('''SELECT name, due, quantity, priority, item_id,
operation_id, customer_id, owner_id, minshipment, maxlateness,
category, subcategory
FROM demand
where status is null or status ='open' or status = 'quote' ''')
for i, j, k, l, m, n, o, p, q, r, s, t in cursor.fetchall():
cnt += 1
try:
x = frepple.demand(name=i,
due=j,
quantity=k,
priority=l,
item=frepple.item(name=m),
category=s,
subcategory=t)
if n: x.operation = frepple.operation(name=n)
if o: x.customer = frepple.customer(name=o)
if p: x.owner = frepple.demand(name=p)
if q: x.minshipment = q
if r != None: x.maxlateness = r
except Exception as e:
print("Error:", e)
print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def 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].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 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 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 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 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 loadItems(cursor):
print('Importing items...')
cnt = 0
starttime = time()
cursor.execute('''SELECT
name, description, operation_id, owner_id,
price, category, subcategory
FROM item''')
for i,j,k,l,m,n,o in cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i, description=j, category=n, subcategory=o)
if k: x.operation = frepple.operation(name=k)
if l: x.owner = frepple.item(name=l)
if m: x.price = m
except Exception as e: print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def loadItems(cursor):
print('Importing items...')
cnt = 0
starttime = time()
cursor.execute('''SELECT
name, description, operation_id, owner_id,
price, category, subcategory
FROM item''')
for i, j, k, l, m, n, o in cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i, description=j, category=n, subcategory=o)
if k: x.operation = frepple.operation(name=k)
if l: x.owner = frepple.item(name=l)
if m: x.price = m
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
if cls.filter:
filter_and = "and %s " % cls.filter
filter_where = "where %s " % cls.filter
else:
filter_and = ""
filter_where = ""
with connections[database].chunked_cursor() as cursor:
cnt = 0
starttime = time()
cursor.execute('''
SELECT
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 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 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 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()
attrs = [ f[0] for f in getAttributes(Item) ]
if attrs:
attrsql = ', %s' % ', '.join(attrs)
else:
attrsql = ''
cursor.execute('''
SELECT
name, description, owner_id,
cost, category, subcategory, source %s
FROM item %s
''' % (attrsql, filter_where))
for i in cursor:
cnt += 1
try:
x = frepple.item(name=i[0], description=i[1], category=i[4], subcategory=i[5], source=i[6])
if i[2]:
x.owner = frepple.item(name=i[2])
if i[3]:
x.cost = i[3]
idx = 7
for a in attrs:
setattr(x, a, i[idx])
idx += 1
except Exception as e:
logger.error("**** %s ****" % e)
logger.info('Loaded %d items 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 loadItems(self):
print('Importing items...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
name, description, operation_id, owner_id,
price, category, subcategory, source
FROM item %s
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i[0], description=i[1], category=i[5], subcategory=i[6], source=i[7])
if i[2]:
x.operation = frepple.operation(name=i[2])
if i[3]:
x.owner = frepple.item(name=i[3])
if i[4]:
x.price = i[4]
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def loadItems(self):
print('Importing items...')
cnt = 0
starttime = time()
self.cursor.execute('''
SELECT
name, description, operation_id, owner_id,
price, category, subcategory, source
FROM item %s
''' % self.filter_where)
for i in self.cursor.fetchall():
cnt += 1
try:
x = frepple.item(name=i[0], description=i[1], category=i[5], subcategory=i[6], source=i[7])
if i[2]:
x.operation = frepple.operation(name=i[2])
if i[3]:
x.owner = frepple.item(name=i[3])
if i[4]:
x.price = i[4]
except Exception as e:
print("Error:", e)
print('Loaded %d items in %.2f seconds' % (cnt, time() - starttime))
def 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 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 loadDemand(cursor):
print('Importing demands...')
cnt = 0
starttime = time()
cursor.execute('''SELECT name, due, quantity, priority, item_id,
operation_id, customer_id, owner_id, minshipment, maxlateness,
category, subcategory
FROM demand
where status is null or status ='open' ''')
for i,j,k,l,m,n,o,p,q,r,s,t in cursor.fetchall():
cnt += 1
try:
x = frepple.demand( name=i, due=j, quantity=k, priority=l, item=frepple.item(name=m),category=s,subcategory=t)
if n: x.operation = frepple.operation(name=n)
if o: x.customer = frepple.customer(name=o)
if p: x.owner = frepple.demand(name=p)
if q: x.minshipment = q
if r != None: x.maxlateness = r
except Exception as e: print("Error:", e)
print('Loaded %d demands in %.2f seconds' % (cnt, time() - starttime))
def 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))
def loadOperationPlans(self): # TODO if we are going to replan anyway, we can skip loading the proposed operationplans
print('Importing operationplans...')
if 'supply' in os.environ:
confirmed_filter = " and operationplan.status = 'confirmed'"
else:
confirmed_filter = ""
cnt_mo = 0
cnt_po = 0
cnt_do = 0
cnt_dlvr = 0
starttime = time()
self.cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status, operationplan.source,
operationplan.type, operationplan.origin_id, operationplan.destination_id, operationplan.supplier_id,
operationplan.item_id, operationplan.location_id,
reference, coalesce(dmd.name, null)
FROM operationplan
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.owner_id IS NULL
and operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type in ('PO', 'MO', 'DO', 'DLVR')
ORDER BY operationplan.id ASC
''' % (self.filter_and, confirmed_filter))
for i in self.cursor.fetchall():
try:
if i[7] == 'MO':
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]), id=i[1],
quantity=i[2], source=i[6], start=i[3], end=i[4],
status=i[5], reference=i[13]
)
elif i[7] == 'PO':
cnt_po += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]), ordertype=i[7],
id=i[1], reference=i[12],
item=frepple.item(name=i[11]) if i[11] else None,
supplier=frepple.supplier(name=i[10]) if i[10] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
elif i[7] == 'DO':
cnt_do += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[9]) if i[9] else None,
id=i[1], reference=i[12], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
elif i[7] == 'DLVR':
cnt_dlvr += 1
opplan = frepple.operationplan(
location=frepple.location(name=i[12]) if i[12] else None,
id=i[1], reference=i[12], ordertype=i[7],
item=frepple.item(name=i[11]) if i[11] else None,
origin=frepple.location(name=i[8]) if i[8] else None,
demand=frepple.demand(name=i[14]) if i[14] else None,
quantity=i[2], start=i[3], end=i[4],
status=i[5], source=i[6]
)
opplan = None
else:
print("Warning: unhandled operationplan type '%s'" % i[7])
continue
if i[14] and opplan:
opplan.demand = frepple.demand(name=i[14])
except Exception as e:
print("Error:", e)
self.cursor.execute('''
SELECT
operationplan.operation_id, operationplan.id, operationplan.quantity,
operationplan.startdate, operationplan.enddate, operationplan.status,
operationplan.owner_id, operationplan.source, coalesce(dmd.name, null)
FROM operationplan
INNER JOIN (select id
from operationplan
) opplan_parent
on operationplan.owner_id = opplan_parent.id
LEFT OUTER JOIN (select name from demand
where demand.status = 'open'
) dmd
on dmd.name = operationplan.demand_id
WHERE operationplan.quantity >= 0 and operationplan.status <> 'closed'
%s%s and operationplan.type = 'MO'
ORDER BY operationplan.id ASC
''' % (self.filter_and, confirmed_filter))
for i in self.cursor.fetchall():
cnt_mo += 1
opplan = frepple.operationplan(
operation=frepple.operation(name=i[0]),
id=i[1], quantity=i[2], source=i[7],
owner=frepple.operationplan(id=i[6]),
start=i[3], end=i[4], status=i[5]
)
if i[8]:
opplan.demand = frepple.demand(name=i[8])
print('Loaded %d manufacturing orders, %d purchase orders, %d distribution orders and %s deliveries in %.2f seconds' % (cnt_mo, cnt_po, cnt_do, cnt_dlvr, time() - starttime))
# Assure the operationplan ids will be unique.
# We call this method only at the end, as calling it earlier gives a slower
# performance to load operationplans
self.cursor.execute('''
select coalesce(max(id),1) + 1 as max_id
from operationplan
''')
d = self.cursor.fetchone()
frepple.settings.id = d[0]
<quantity>1</quantity>
</flow>
</flows>
</plan>
''')
###
print("\nCreating operationplans")
opplan = frepple.operationplan(operation="make item",
quantity=9,
end=datetime.datetime(2011, 1, 1))
opplan.locked = True
###
print("\nCreating items")
item = frepple.item(name="end item", operation=shipoper)
itemlist = [frepple.item(name="item %d" % i) for i in range(10)]
###
print("\nTesting the comparison operator")
print("makeoper < shipoper", makeoper < shipoper)
print("shipoper < makeoper", shipoper < makeoper)
print("shipoper != makeoper", shipoper != makeoper)
print("shipoper == makeoper", shipoper == makeoper)
print("shipoper == shipoper", shipoper == shipoper)
try:
print("makeoper == item", makeoper == item)
except Exception as e:
print("Catching exception %s: %s" % (e.__class__.__name__, e))
###
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))
<operation name="make or buy item" />
<buffer name="end item" />
<quantity>1</quantity>
</flow>
</flows>
</plan>
''')
###
print("\nCreating operationplans")
opplan = frepple.operationplan(operation="make item", quantity=9, end=datetime.datetime(2011,1,1))
opplan.locked = True
###
print("\nCreating items")
item = frepple.item(name="end item", operation=shipoper)
itemlist = [ frepple.item(name="item %d" % i) for i in range(10) ]
###
print("\nTesting the comparison operator")
print("makeoper < shipoper", makeoper < shipoper)
print("shipoper < makeoper", shipoper < makeoper)
print("shipoper != makeoper", shipoper != makeoper)
print("shipoper == makeoper", shipoper == makeoper)
print("shipoper == shipoper", shipoper == shipoper)
try:
print("makeoper == item", makeoper == item)
except Exception as e:
print("Catching exception %s: %s" % (e.__class__.__name__, e))
###
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]
</flows>
</plan>
''')
###
print("\nCreating operationplans")
opplan = frepple.operationplan(
operation = frepple.operation(name="make item"),
quantity = 9,
end = datetime.datetime(2011,1,1)
)
opplan.status = 'confirmed'
###
print("\nCreating items")
item = frepple.item(name="end item")
itemlist = [ frepple.item(name="item %d" % i) for i in range(10) ]
###
print("\nTesting the comparison operator")
print("makeoper < shipoper", makeoper < shipoper)
print("shipoper < makeoper", shipoper < makeoper)
print("shipoper != makeoper", shipoper != makeoper)
print("shipoper == makeoper", shipoper == makeoper)
print("shipoper == shipoper", shipoper == shipoper)
try:
print("makeoper == item", makeoper == item)
except Exception as e:
print("Catching exception %s: %s" % (e.__class__.__name__, e))
###
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]
<quantity>1</quantity>
</flow>
</flows>
</plan>
''')
###
print("\nCreating operationplans")
opplan = frepple.operationplan(operation=frepple.operation(name="make item"),
quantity=9,
end=datetime.datetime(2011, 1, 1))
opplan.status = 'confirmed'
###
print("\nCreating items")
item = frepple.item(name="end item")
itemlist = [frepple.item(name="item %d" % i) for i in range(10)]
###
print("\nTesting the comparison operator")
print("makeoper < shipoper", makeoper < shipoper)
print("shipoper < makeoper", shipoper < makeoper)
print("shipoper != makeoper", shipoper != makeoper)
print("shipoper == makeoper", shipoper == makeoper)
print("shipoper == shipoper", shipoper == shipoper)
try:
print("makeoper == item", makeoper == item)
except Exception as e:
print("Catching exception %s: %s" % (e.__class__.__name__, e))
###
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
if cls.filter:
filter_and = "and %s " % cls.filter
filter_where = "where %s " % cls.filter
else:
filter_and = ""
filter_where = ""
with connections[database].chunked_cursor() as cursor:
cnt = 0
starttime = time()
# Note: The sorting of the flows is not really necessary, but helps to make
# the planning progress consistent across runs and database engines.
cursor.execute('''
SELECT
operation_id, item_id, quantity, type, effective_start,
effective_end, name, priority, search, source
FROM operationmaterial %s
ORDER BY operation_id, item_id
''' % filter_where)
for i in cursor:
cnt += 1
try:
curflow = frepple.flow(
operation=frepple.operation(name=i[0]),
item=frepple.item(name=i[1]),
quantity=i[2],
type="flow_%s" % i[3],
source=i[9]
)
if i[4]:
curflow.effective_start = i[4]
if i[5]:
curflow.effective_end = i[5]
if i[6]:
curflow.name = i[6]
if i[7]:
curflow.priority = i[7]
if i[8]:
curflow.search = i[8]
except Exception as e:
logger.error("**** %s ****" % e)
logger.info('Loaded %d operation materials in %.2f seconds' % (cnt, time() - starttime))
# Check for operations where:
# - operation.item is still blank
# - they have a single operationmaterial item with quantity > 0
# If found we update
starttime = time()
cnt = 0
logger.info('Auto-update operation items...')
for oper in frepple.operations():
if oper.hidden or oper.item or oper.hasSuperOperations:
continue
item = None
for fl in oper.flows:
if fl.quantity < 0 or fl.hidden:
continue
if item and item != fl.item:
item = None
break
else:
item = fl.item
if item:
cnt += 1
oper.item = item
logger.info('Auto-update of %s operation items in %.2f seconds' % (cnt, time() - starttime))
def 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))
