def exportItems(cursor):
print("Exporting items...")
starttime = time()
cursor.execute("SELECT name FROM item")
primary_keys = set([ i[0] for i in cursor.fetchall() ])
cursor.executemany(
"insert into item \
(name,description,operation_id,price,category,subcategory,lastmodified) \
values(%s,%s,%s,%s,%s,%s,%s)",
[(
i.name, i.description, i.operation and i.operation.name or None,
round(i.price,settings.DECIMAL_PLACES), i.category, i.subcategory, timestamp
) for i in frepple.items() if i.name not in primary_keys
])
cursor.executemany(
"update item \
set description=%s, operation_id=%s, price=%s, category=%s, subcategory=%s, lastmodified=%s \
where name=%s",
[(
i.description, i.operation and i.operation.name or None,
round(i.price,settings.DECIMAL_PLACES), i.category, i.subcategory, timestamp, i.name
) for i in frepple.items() if i.name in primary_keys
])
cursor.executemany(
"update item set owner_id=%s where name=%s",
[(
i.owner.name, i.name
) for i in frepple.items() if i.owner
])
transaction.commit(using=database)
print('Exported items in %.2f seconds' % (time() - starttime))
def exportItems(cursor):
print("Exporting items...")
starttime = time()
cursor.execute("SELECT name FROM item")
primary_keys = set([ i[0] for i in cursor.fetchall() ])
cursor.executemany(
"insert into item \
(name,description,operation_id,price,category,subcategory,lastmodified) \
values(%s,%s,%s,%s,%s,%s,%s)",
[(
i.name, i.description, i.operation and i.operation.name or None,
round(i.price,settings.DECIMAL_PLACES), i.category, i.subcategory, timestamp
) for i in frepple.items() if i.name not in primary_keys
])
cursor.executemany(
"update item \
set description=%s, operation_id=%s, price=%s, category=%s, subcategory=%s, lastmodified=%s \
where name=%s",
[(
i.description, i.operation and i.operation.name or None,
round(i.price,settings.DECIMAL_PLACES), i.category, i.subcategory, timestamp, i.name
) for i in frepple.items() if i.name in primary_keys
])
cursor.executemany(
"update item set owner_id=%s where name=%s",
[(
i.owner.name, i.name
) for i in frepple.items() if i.owner
])
transaction.commit(using=database)
print('Exported items in %.2f seconds' % (time() - starttime))
def exportItems(self, cursor):
with transaction.atomic(using=self.database, savepoint=False):
print("Exporting items...")
starttime = time()
cursor.execute("SELECT name FROM item")
primary_keys = set([ i[0] for i in cursor.fetchall() ])
cursor.executemany(
"insert into item \
(name,description,operation_id,price,category,subcategory,source,lastmodified) \
values(%s,%s,%s,%s,%s,%s,%s,%s)",
[
(
i.name, i.description, i.operation and i.operation.name or None,
round(i.price, 4), i.category, i.subcategory,
i.source, self.timestamp
)
for i in frepple.items()
if i.name not in primary_keys and (not self.source or self.source == i.source)
])
cursor.executemany(
"update item \
set description=%s, operation_id=%s, price=%s, category=%s, subcategory=%s, source=%s, lastmodified=%s \
where name=%s",
[
(
i.description, i.operation and i.operation.name or None,
round(i.price, 4), i.category, i.subcategory,
i.source, self.timestamp, i.name
)
for i in frepple.items()
if i.name in primary_keys and (not self.source or self.source == i.source)
])
cursor.executemany(
"update item set owner_id=%s where name=%s",
[
(i.owner.name, i.name)
for i in frepple.items()
if i.owner and (not self.source or self.source == i.source)
])
print('Exported items in %.2f seconds' % (time() - starttime))
def exportItems(self, cursor):
with transaction.atomic(using=self.database, savepoint=False):
print("Exporting items...")
starttime = time()
cursor.execute("SELECT name FROM item")
primary_keys = set([ i[0] for i in cursor.fetchall() ])
cursor.executemany(
"insert into item \
(name,description,cost,category,subcategory,source,lastmodified) \
values(%s,%s,%s,%s,%s,%s,%s)",
[
(
i.name, i.description, round(i.cost, 6), i.category,
i.subcategory, i.source, self.timestamp
)
for i in frepple.items()
if i.name not in primary_keys and (not self.source or self.source == i.source)
])
cursor.executemany(
"update item \
set description=%s, cost=%s, category=%s, subcategory=%s, source=%s, lastmodified=%s \
where name=%s",
[
(
i.description, round(i.cost, 6), i.category, i.subcategory,
i.source, self.timestamp, i.name
)
for i in frepple.items()
if i.name in primary_keys and (not self.source or self.source == i.source)
])
cursor.executemany(
"update item set owner_id=%s where name=%s",
[
(i.owner.name, i.name)
for i in frepple.items()
if i.owner and (not self.source or self.source == i.source)
])
print('Exported items in %.2f seconds' % (time() - starttime))
def truncate(self, process):
if self.verbosity:
print("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
process.stdin.write("truncate table out_demandpegging;\n".encode(self.encoding))
process.stdin.write("truncate table out_problem, out_resourceplan, out_constraint;\n".encode(self.encoding))
process.stdin.write("truncate table out_loadplan, out_flowplan, out_operationplan;\n".encode(self.encoding))
process.stdin.write("truncate table out_demand;\n".encode(self.encoding))
process.stdin.write("delete from purchase_order where status='proposed' or status is null;\n".encode(self.encoding))
process.stdin.write("delete from distribution_order where status='proposed' or status is null;\n".encode(self.encoding))
process.stdin.write("delete from operationplan where status='proposed' or status is null;\n".encode(self.encoding))
else:
# Partial export for a single cluster
process.stdin.write('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name));\n'.encode(self.encoding))
for i in frepple.items():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write('''delete from out_demandpegging where demand in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n'''.encode(self.encoding))
process.stdin.write("delete from out_demand where item in (select name from cluster_keys);\n".encode(self.encoding))
process.stdin.write("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write("delete from out_flowplan where thebuffer in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n".encode(self.encoding))
process.stdin.write('''delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n'''.encode(self.encoding))
process.stdin.write("delete from out_problem where entity = 'material' and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n".encode(self.encoding))
process.stdin.write("delete from purchase_order using cluster_keys where (status='proposed' or status is null) and purchase_order.item_id = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from distribution_order using cluster_keys where (status='proposed' or status is null) and distribution_order.item_id = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("truncate table cluster_keys;\n".encode(self.encoding))
for i in frepple.resources():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write('delete from out_loadplan using cluster_keys where theresource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write('delete from out_resourceplan using cluster_keys where theresource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write('truncate table cluster_keys;\n'.encode(self.encoding))
for i in frepple.operations():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from out_operationplan using cluster_keys where operation = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.operation_id = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("drop table cluster_keys;\n".encode(self.encoding))
if self.verbosity:
print("Emptied plan tables in %.2f seconds" % (time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Uncomment the following lines to bypass the connection to odoo and use
# a XML flat file alternative. This can be useful for debugging.
#with open("my_path/my_data_file.xml", 'rb') as f:
# frepple.readXMLdata(f.read().decode('utf-8'), False, False)
# frepple.printsize()
# return
odoo_user = Parameter.getValue("odoo.user", database)
odoo_password = settings.ODOO_PASSWORDS.get(database, None)
if not settings.ODOO_PASSWORDS.get(database):
odoo_password = Parameter.getValue("odoo.password", database)
odoo_db = Parameter.getValue("odoo.db", database)
odoo_url = Parameter.getValue("odoo.url", database)
odoo_company = Parameter.getValue("odoo.company", database)
ok = True
if not odoo_user:
logger.error("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
logger.error("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
logger.error("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
logger.error("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
logger.error("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", database, 'en_US')
if not ok:
raise Exception("Odoo connector not configured correctly")
# Assign to single roots
root_item = None
for r in frepple.items():
if r.owner is None:
root_item = r
break
root_customer = None
for r in frepple.customers():
if r.owner is None:
root_customer = r
break
root_location = None
for r in frepple.locations():
if r.owner is None:
root_location = r
break
# Connect to the odoo URL to GET data
url = "%sfrepple/xml?%s" % (odoo_url, urlencode({
'database': odoo_db,
'language': odoo_language,
'company': odoo_company,
'mode': cls.mode
}))
try:
request = Request(url)
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))[:-1]
request.add_header("Authorization", "Basic %s" % encoded.decode('ascii'))
except HTTPError as e:
logger.error("Error connecting to odoo at %s: %s" % (url, e))
raise e
# Download and parse XML data
with urlopen(request) as f:
frepple.readXMLdata(f.read().decode('utf-8'), False, False)
# Assure single root hierarchies
for r in frepple.items():
if r.owner is None and r != root_item:
r.owner = root_item
for r in frepple.customers():
if r.owner is None and r != root_customer:
r.owner = root_customer
for r in frepple.locations():
if r.owner is None and r != root_location:
r.owner = root_location
def truncate(self):
cursor = connections[self.database].cursor()
if self.verbosity:
logger.info("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
cursor.execute("truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute('''
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.reference = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
''')
cursor.execute('''
truncate operationplanmaterial, operationplanresource
''')
cursor.execute('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
''')
else:
# Partial export for a single cluster
cursor.execute('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))')
for i in frepple.items():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)")
cursor.execute('''
delete from operationplanmaterial
using cluster_keys
where operationplan_id in (
select reference from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
union
select reference from operationplan where owner_id in (
select reference from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
)
''')
cursor.execute('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
''')
cursor.execute('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id)
''')
cursor.execute('''
delete from operationplanresource
where operationplan_id in (
select reference
from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
where status = 'proposed' or status is null or type='STCK'
union
select reference
from operationplan
where owner_id in (
select reference
from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
and (status = 'proposed' or status is null)
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.reference
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
''')
# TODO next subqueries are not efficient - the exists condition triggers a sequential scan
cursor.execute("delete from out_constraint where exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_constraint.demand like forecast.name || ' - %')")
cursor.execute("delete from out_problem where entity = 'demand' and exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_problem.owner like forecast.name || ' - %')")
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)")
cursor.execute('delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name')
cursor.execute('delete from out_resourceplan using cluster_keys where resource = cluster_keys.name')
cursor.execute("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name")
cursor.execute('truncate table cluster_keys')
for i in frepple.operations():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name")
cursor.execute("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.name = cluster_keys.name")
cursor.execute("drop table cluster_keys")
if self.verbosity:
logger.info("Emptied plan tables in %.2f seconds" % (time() - starttime))
def run(cls, cluster=-1, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
cursor = connections[database].cursor()
if cluster == -1:
# Complete export for the complete model
cursor.execute(
"truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute("""
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.reference = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
""")
cursor.execute("""
truncate operationplanmaterial, operationplanresource
""")
cursor.execute("""
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
""")
else:
# Partial export for a single cluster
cursor.execute(
"create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))"
)
for i in frepple.items():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute("""
delete from out_constraint
where demand in (
select demand.name
from demand
inner join cluster_keys
on cluster_keys.name = demand.item_id
)
""")
cursor.execute("""
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
""")
cursor.execute("""
delete from out_problem
where entity = 'material'
and owner in (
select buffer.item_id || ' @ ' || buffer.location_id
from buffer
inner join cluster_keys on cluster_keys.name = buffer.item_id
)
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.reference
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
""")
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute("""
delete from out_problem
where entity = 'demand'
and owner in (
select demand.name
from demand
inner join cluster_keys on cluster_keys.name = demand.item_id
)
""")
cursor.execute(
"delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name"
)
cursor.execute(
"delete from out_resourceplan using cluster_keys where resource = cluster_keys.name"
)
cursor.execute(
"delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name"
)
cursor.execute("truncate table cluster_keys")
for i in frepple.operations():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute(""""
delete from out_problem
using cluster_keys
where entity = 'operation' and owner = cluster_keys.name
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where (status='proposed' or status is null)
and operationplan.name = cluster_keys.name
""")
cursor.execute("drop table cluster_keys")
def truncate(self):
cursor = connections[self.database].cursor()
if self.verbosity:
logger.info("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
cursor.execute(
"truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute('''
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.id = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
''')
cursor.execute('''
delete from operationplanmaterial
using operationplan
where operationplanmaterial.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null)
or operationplan.type = 'STCK'
or operationplanmaterial.status = 'proposed'
or operationplanmaterial.status is null)
''')
cursor.execute('''
delete from operationplanresource
using operationplan
where operationplanresource.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null)
or operationplan.type = 'STCK'
or operationplanresource.status = 'proposed'
or operationplanresource.status is null)
''')
cursor.execute('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
''')
else:
# Partial export for a single cluster
cursor.execute(
'create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))'
)
for i in frepple.items():
if i.cluster == self.cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute(
"delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)"
)
cursor.execute('''
delete from operationplanmaterial
using cluster_keys
where operationplan_id in (
select id from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
union
select id from operationplan where owner_id in (
select id from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
)
''')
cursor.execute('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
''')
cursor.execute('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id)
''')
cursor.execute('''
delete from operationplanresource
where operationplan_id in (
select id from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
union
select id from operationplan where owner_id in (
select id from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.id
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
''')
# TODO next subqueries are not efficient - the exists condition triggers a sequential scan
cursor.execute(
"delete from out_constraint where exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_constraint.demand like forecast.name || ' - %')"
)
cursor.execute(
"delete from out_problem where entity = 'demand' and exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_problem.owner like forecast.name || ' - %')"
)
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == self.cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute(
"delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)"
)
cursor.execute(
'delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name'
)
cursor.execute(
'delete from out_resourceplan using cluster_keys where resource = cluster_keys.name'
)
cursor.execute(
"delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name"
)
cursor.execute('truncate table cluster_keys')
for i in frepple.operations():
if i.cluster == self.cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute(
"delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name"
)
cursor.execute(
"delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.name = cluster_keys.name"
)
cursor.execute("drop table cluster_keys")
if self.verbosity:
logger.info("Emptied plan tables in %.2f seconds" %
(time() - starttime))
def printModel(filename):
'''
A function that prints out all models to a file.
'''
# Open the output file
output = open(filename,"wt")
# Global settings
print("Echoing global settings", file=output)
print("Plan name:", frepple.settings.name, file=output)
print("Plan description:", frepple.settings.description.encode('utf-8'), file=output)
print("Plan current:", frepple.settings.current, file=output)
# Solvers
print("\nEchoing solvers:", file=output)
for b in frepple.solvers():
print(" Solver:", b.name, b.loglevel, getattr(b,'constraints',None), file=output)
# Calendars
print("\nEchoing calendars:", file=output)
for b in frepple.calendars():
print(" Calendar:", b.name, getattr(b,'default',None), file=output)
for j in b.buckets:
print(" Bucket:", getattr(j,'value',None), j.start, j.end, j.priority, file=output)
# Customers
print("\nEchoing customers:", file=output)
for b in frepple.customers():
print(" Customer:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
# Locations
print("\nEchoing locations:", file=output)
for b in frepple.locations():
print(" Location:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
# Items
print("\nEchoing items:", file=output)
for b in frepple.items():
print(" Item:", b.name, b.description, b.category, b.subcategory, b.owner, b.operation, file=output)
# Resources
print("\nEchoing resources:", file=output)
for b in frepple.resources():
print(" Resource:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
for l in b.loads:
print(" Load:", l.operation.name, l.quantity, l.effective_start, l.effective_end, file=output)
for l in b.loadplans:
print(" Loadplan:", l.operationplan.id, l.operationplan.operation.name, l.quantity, l.startdate, l.enddate, file=output)
# Buffers
print("\nEchoing buffers:", file=output)
for b in frepple.buffers():
print(" Buffer:", b.name, b.description, b.category, b.subcategory, b.owner, file=output)
for l in b.flows:
print(" Flow:", l.operation.name, l.quantity, l.effective_start, l.effective_end, file=output)
for l in b.flowplans:
print(" Flowplan:", l.operationplan.id, l.operationplan.operation.name, l.quantity, l.date, file=output)
# Operations
print("\nEchoing operations:", file=output)
for b in frepple.operations():
print(" Operation:", b.name, b.description, b.category, b.subcategory, file=output)
for l in b.loads:
print(" Load:", l.resource.name, l.quantity, l.effective_start, l.effective_end, file=output)
for l in b.flows:
print(" Flow:", l.buffer.name, l.quantity, l.effective_start, l.effective_end, file=output)
if isinstance(b, frepple.operation_alternate):
for l in b.alternates:
print(" Alternate:", l.name, file=output)
if isinstance(b, frepple.operation_routing):
for l in b.steps:
print(" Step:", l.name, file=output)
# Demands
print("\nEchoing demands:", file=output)
for b in frepple.demands():
print(" Demand:", b.name, b.due, b.item.name, b.quantity, file=output)
for i in b.operationplans:
print(" Operationplan:", i.id, i.operation.name, i.quantity, i.end, file=output)
# Operationplans
print("\nEchoing operationplans:", file=output)
for b in frepple.operationplans():
print(" Operationplan:", b.operation.name, b.quantity, b.start, b.end, file=output)
for s in b.operationplans:
print(" ", s.operation.name, s.quantity, s.start, s.end, file=output)
# Problems
print("\nPrinting problems", file=output)
for i in frepple.problems():
print(" Problem:", i.entity, i.name, i.description, i.start, i.end, i.weight, file=output)
def getItemsFromCluster(cluster=-1, ):
import frepple
for i in frepple.items():
if i.cluster == cluster:
yield "%s\n" % (clean_value(i.name), )
def truncate(self, process):
if self.verbosity:
print("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
process.stdin.write(
"truncate table out_problem, out_resourceplan, out_constraint;\n"
.encode(self.encoding))
process.stdin.write('''
delete from operationplanmaterial
using operationplan
where operationplanmaterial.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK' or operationplanmaterial.status = 'proposed');\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplanresource
using operationplan
where operationplanresource.operationplan_id = operationplan.id
and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK' or operationplanresource.status = 'proposed');\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK';\n
'''.encode(self.encoding))
else:
# Partial export for a single cluster
process.stdin.write(
'create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name));\n'
.encode(self.encoding))
for i in frepple.items():
if i.cluster == self.cluster:
process.stdin.write(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(
self.encoding)).encode(self.encoding))
process.stdin.write(
"delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n"
.encode(self.encoding))
process.stdin.write('''
delete from operationplanmaterial
where buffer in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name;\n
'''.encode(self.encoding))
process.stdin.write("truncate table cluster_keys;\n".encode(
self.encoding))
for i in frepple.resources():
if i.cluster == self.cluster:
process.stdin.write(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(
self.encoding)).encode(self.encoding))
process.stdin.write(
"delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n"
.encode(self.encoding))
process.stdin.write(
'delete from operationplanresource using cluster_keys where resource = cluster_keys.name;\n'
.encode(self.encoding))
process.stdin.write(
'delete from out_resourceplan using cluster_keys where resource = cluster_keys.name;\n'
.encode(self.encoding))
process.stdin.write(
"delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name;\n"
.encode(self.encoding))
process.stdin.write('truncate table cluster_keys;\n'.encode(
self.encoding))
for i in frepple.operations():
if i.cluster == self.cluster:
process.stdin.write(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode(
self.encoding)).encode(self.encoding))
process.stdin.write(
"delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name;\n"
.encode(self.encoding))
process.stdin.write(
"delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.operation_id = cluster_keys.name;\n"
.encode(self.encoding)) # TODO not correct in new data model
process.stdin.write("drop table cluster_keys;\n".encode(
self.encoding))
if self.verbosity:
print("Emptied plan tables in %.2f seconds" % (time() - starttime))
def itemdistributions():
for o in frepple.items():
for i in o.itemdistributions:
if not i.hidden:
yield i
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Uncomment the following lines to bypass the connection to odoo and use
# a XML flat file alternative. This can be useful for debugging.
# with open("my_path/my_data_file.xml", 'rb') as f:
# frepple.readXMLdata(f.read().decode('utf-8'), False, False)
# frepple.printsize()
# return
odoo_user = Parameter.getValue("odoo.user", database)
odoo_password = settings.ODOO_PASSWORDS.get(database, None)
if not settings.ODOO_PASSWORDS.get(database):
odoo_password = Parameter.getValue("odoo.password", database)
odoo_db = Parameter.getValue("odoo.db", database, None)
odoo_url = Parameter.getValue("odoo.url", database, "").strip()
if not odoo_url.endswith("/"):
odoo_url = odoo_url + "/"
odoo_company = Parameter.getValue("odoo.company", database, None)
ok = True
# Set debugFile=PathToXmlFile if you want frePPLe to read that file
# rather than the data at url
# else leave it to False
debugFile = False # "c:/temp/frepple_data.xml"
if not odoo_user and not debugFile:
logger.error("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password and not debugFile:
logger.error("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db and not debugFile:
logger.error("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url and not debugFile:
logger.error("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company and not debugFile:
logger.error("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", database, "en_US")
if not ok and not debugFile:
raise Exception("Odoo connector not configured correctly")
# Connect to the odoo URL to GET data
try:
loglevel = int(Parameter.getValue("odoo.loglevel", database, "0"))
except Exception:
loglevel = 0
if not debugFile:
url = "%sfrepple/xml?%s" % (
odoo_url,
urlencode({
"database": odoo_db,
"language": odoo_language,
"company": odoo_company,
"mode": cls.mode,
}),
)
try:
request = Request(url)
encoded = base64.encodestring(
("%s:%s" %
(odoo_user, odoo_password)).encode("utf-8"))[:-1]
request.add_header("Authorization",
"Basic %s" % encoded.decode("ascii"))
except HTTPError as e:
logger.error("Error connecting to odoo at %s: %s" % (url, e))
raise e
# Download and parse XML data
with urlopen(request) as f:
frepple.readXMLdata(f.read().decode("utf-8"), False, False,
loglevel)
else:
# Download and parse XML data
with open(debugFile, encoding="utf-8") as f:
frepple.readXMLdata(f.read(), False, False, loglevel)
# Hierarchy correction: Count how many items/locations/customers have no owner
# If we find 2+ then we use All items/All customers/All locations as root
# otherwise we assume that the hierarchy is correct
rootItem = None
for r in frepple.items():
if r.owner is None:
if not rootItem:
rootItem = r
else:
rootItem = None
break
rootLocation = None
for r in frepple.locations():
if r.owner is None:
if not rootLocation:
rootLocation = r
else:
rootLocation = None
break
rootCustomer = None
for r in frepple.customers():
if r.owner is None:
if not rootCustomer:
rootCustomer = r
else:
rootCustomer = None
break
if not rootItem:
rootItem = frepple.item_mts(name="All items",
source="odoo_%s" % cls.mode)
for r in frepple.items():
if r.owner is None and r != rootItem:
r.owner = rootItem
if not rootLocation:
rootLocation = frepple.location(name="All locations",
source="odoo_%s" % cls.mode)
for r in frepple.locations():
if r.owner is None and r != rootLocation:
r.owner = rootLocation
if not rootCustomer:
rootCustomer = frepple.customer(name="All customers",
source="odoo_%s" % cls.mode)
for r in frepple.customers():
if r.owner is None and r != rootCustomer:
r.owner = rootCustomer
def itemdistributions():
for o in frepple.items():
for i in o.itemdistributions:
if not i.hidden:
yield i
def itemdistributions():
for o in frepple.items():
for i in o.itemdistributions:
yield i
def printModel(filename):
'''
A function that prints out all models to a file.
'''
# Open the output file
with open(filename, "wt", encoding='utf-8') as output:
# Global settings
print("Echoing global settings", file=output)
print("Plan name:", frepple.settings.name, file=output)
print("Plan description:", frepple.settings.description, file=output)
print("Plan current:", frepple.settings.current, file=output)
# Solvers
print("\nEchoing solvers:", file=output)
for b in frepple.solvers():
print(" Solver:",
b.name,
b.loglevel,
getattr(b, 'constraints', None),
file=output)
# Calendars
print("\nEchoing calendars:", file=output)
for b in frepple.calendars():
print(" Calendar:",
b.name,
getattr(b, 'default', None),
file=output)
for j in b.buckets:
print(" Bucket:",
getattr(j, 'value', None),
j.start,
j.end,
j.priority,
file=output)
# Customers
print("\nEchoing customers:", file=output)
for b in frepple.customers():
print(" Customer:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
# Locations
print("\nEchoing locations:", file=output)
for b in frepple.locations():
print(" Location:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
# Items
print("\nEchoing items:", file=output)
for b in frepple.items():
print(" Item:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
b.operation,
file=output)
# Resources
print("\nEchoing resources:", file=output)
for b in frepple.resources():
print(" Resource:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
for l in b.loads:
print(" Load:",
l.operation.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
for l in b.loadplans:
print(" Loadplan:",
l.operationplan.id,
l.operationplan.operation.name,
l.quantity,
l.startdate,
l.enddate,
file=output)
# Buffers
print("\nEchoing buffers:", file=output)
for b in frepple.buffers():
print(" Buffer:",
b.name,
b.description,
b.category,
b.subcategory,
b.owner,
file=output)
for l in b.flows:
print(" Flow:",
l.operation.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
for l in b.flowplans:
print(" Flowplan:",
l.operationplan.id,
l.operationplan.operation.name,
l.quantity,
l.date,
file=output)
# Operations
print("\nEchoing operations:", file=output)
for b in frepple.operations():
print(" Operation:",
b.name,
b.description,
b.category,
b.subcategory,
file=output)
for l in b.loads:
print(" Load:",
l.resource.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
for l in b.flows:
print(" Flow:",
l.buffer.name,
l.quantity,
l.effective_start,
l.effective_end,
file=output)
if isinstance(b, frepple.operation_alternate):
for l in b.alternates:
print(" Alternate:",
l[0].name,
l[1],
l[2],
l[3],
file=output)
if isinstance(b, frepple.operation_routing):
for l in b.steps:
print(" Step:", l.name, file=output)
# Demands
print("\nEchoing demands:", file=output)
for b in frepple.demands():
print(" Demand:",
b.name,
b.due,
b.item.name,
b.quantity,
file=output)
for i in b.operationplans:
print(" Operationplan:",
i.id,
i.operation.name,
i.quantity,
i.end,
file=output)
# Operationplans
print("\nEchoing operationplans:", file=output)
for b in frepple.operationplans():
print(" Operationplan:",
b.operation.name,
b.quantity,
b.start,
b.end,
file=output)
for s in b.operationplans:
print(" ",
s.operation.name,
s.quantity,
s.start,
s.end,
file=output)
# Problems
print("\nPrinting problems", file=output)
for i in frepple.problems():
print(" Problem:",
i.entity,
i.name,
i.description,
i.start,
i.end,
i.weight,
file=output)
def truncate(self, process):
if self.verbosity:
print("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
process.stdin.write("truncate table out_problem, out_resourceplan, out_constraint;\n".encode(self.encoding))
process.stdin.write("truncate table operationplanmaterial, operationplanresource;\n".encode(self.encoding))
# Above line is a temporary solution until we have a correct version of this block of code
# process.stdin.write('''
# delete from operationplanmaterial
# using operationplan
# where operationplanmaterial.operationplan_id = operationplan.id
# and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK');\n
# '''.encode(self.encoding))
# process.stdin.write('''
# delete from operationplanresource
# using operationplan
# where operationplanresource.operationplan_id = operationplan.id
# and ((operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK');\n
# '''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK';\n
'''.encode(self.encoding))
else:
# Partial export for a single cluster
process.stdin.write('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name));\n'.encode(self.encoding))
for i in frepple.items():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write('''
delete from operationplanmaterial
where buffer in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id);\n
'''.encode(self.encoding))
process.stdin.write('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name;\n
'''.encode(self.encoding))
process.stdin.write("truncate table cluster_keys;\n".encode(self.encoding))
for i in frepple.resources():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id);\n".encode(self.encoding))
process.stdin.write('delete from operationplanresource using cluster_keys where resource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write('delete from out_resourceplan using cluster_keys where resource = cluster_keys.name;\n'.encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write('truncate table cluster_keys;\n'.encode(self.encoding))
for i in frepple.operations():
if i.cluster == self.cluster:
process.stdin.write(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)).encode(self.encoding))
process.stdin.write("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name;\n".encode(self.encoding))
process.stdin.write("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.operation_id = cluster_keys.name;\n".encode(self.encoding)) # TODO not correct in new data model
process.stdin.write("drop table cluster_keys;\n".encode(self.encoding))
if self.verbosity:
print("Emptied plan tables in %.2f seconds" % (time() - starttime))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Uncomment the following lines to bypass the connection to odoo and use
# a XML flat file alternative. This can be useful for debugging.
#with open("my_path/my_data_file.xml", 'rb') as f:
# frepple.readXMLdata(f.read().decode('utf-8'), False, False)
# frepple.printsize()
# return
odoo_user = Parameter.getValue("odoo.user", database)
odoo_password = settings.ODOO_PASSWORDS.get(database, None)
if not settings.ODOO_PASSWORDS.get(database):
odoo_password = Parameter.getValue("odoo.password", database)
odoo_db = Parameter.getValue("odoo.db", database)
odoo_url = Parameter.getValue("odoo.url", database)
odoo_company = Parameter.getValue("odoo.company", database)
ok = True
if not odoo_user:
logger.error("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
logger.error("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
logger.error("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
logger.error("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
logger.error("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", database, 'en_US')
if not ok:
raise Exception("Odoo connector not configured correctly")
# Assign to single roots
root_item = None
for r in frepple.items():
if r.owner is None:
root_item = r
break
root_customer = None
for r in frepple.customers():
if r.owner is None:
root_customer = r
break
root_location = None
for r in frepple.locations():
if r.owner is None:
root_location = r
break
# Connect to the odoo URL to GET data
url = "%sfrepple/xml?%s" % (odoo_url, urlencode({
'database': odoo_db,
'language': odoo_language,
'company': odoo_company,
'mode': cls.mode
}))
try:
request = Request(url)
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))[:-1]
request.add_header("Authorization", "Basic %s" % encoded.decode('ascii'))
except HTTPError as e:
logger.error("Error connecting to odoo at %s: %s" % (url, e))
raise e
# Download and parse XML data
with urlopen(request) as f:
frepple.readXMLdata(f.read().decode('utf-8'), False, False)
# Assure single root hierarchies
for r in frepple.items():
if r.owner is None and r != root_item:
r.owner = root_item
for r in frepple.customers():
if r.owner is None and r != root_customer:
r.owner = root_customer
for r in frepple.locations():
if r.owner is None and r != root_location:
r.owner = root_location
