def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue("plan.loglevel", database, 0))
if cls.task and cls.task.user:
maxloglevel = cls.task.user.getMaxLoglevel(database)
if loglevel > maxloglevel:
loglevel = maxloglevel
# Create a solver where the plan type are defined by an environment variable
try:
plantype = int(os.environ["FREPPLE_PLANTYPE"])
except:
plantype = 1 # Default is a constrained plan
try:
constraint = int(os.environ["FREPPLE_CONSTRAINT"])
except:
constraint = 15 # Default is with all constraints enabled
cls.solver = frepple.solver_mrp(
constraints=constraint,
plantype=plantype,
loglevel=loglevel,
lazydelay=int(Parameter.getValue("lazydelay", database, "86400")),
allowsplits=(Parameter.getValue("allowsplits", database,
"true").lower() == "true"),
minimumdelay=int(
Parameter.getValue("plan.minimumdelay", database, "3600")),
rotateresources=(Parameter.getValue("plan.rotateResources",
database,
"true").lower() == "true"),
plansafetystockfirst=(Parameter.getValue(
"plan.planSafetyStockFirst", database, "false").lower() !=
"false"),
iterationmax=int(
Parameter.getValue("plan.iterationmax", database, "0")),
resourceiterationmax=int(
Parameter.getValue("plan.resourceiterationmax", database,
"500")),
administrativeleadtime=86400 * float(
Parameter.getValue("plan.administrativeLeadtime", database,
"0")),
autofence=86400 * float(
Parameter.getValue("plan.autoFenceOperations", database, "0")),
)
if hasattr(cls, "debugResource"):
cls.solver.userexit_resource = cls.debugResource
if hasattr(cls, "debugDemand"):
cls.solver.userexit_demand = cls.debugDemand
if hasattr(cls, "debugOperation"):
cls.solver.userexit_operation = cls.debugOperation
logger.info("Plan type: %s" % plantype)
logger.info("Constraints: %s" % constraint)
cls.solver.solve()
frepple.printsize()
def getWeight(cls, database=DEFAULT_DB_ALIAS, **kwargs):
try:
cls.fence = float(Parameter.getValue('plan.autoFenceOperations', database, '0'))
cls.loglevel = int(Parameter.getValue('plan.loglevel', database, '0'))
except ValueError:
print("Warning: Invalid format for parameter 'plan.autoFenceOperations'.")
cls.fence = 0
except Exception:
cls.fence = 0
if 'supply' in os.environ and cls.fence > 0:
return 1
else:
return -1
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Create a solver where the plan type are defined by an environment variable
try:
plantype = int(os.environ['FREPPLE_PLANTYPE'])
except:
plantype = 1 # Default is a constrained plan
try:
constraint = int(os.environ['FREPPLE_CONSTRAINT'])
except:
constraint = 15 # Default is with all constraints enabled
cls.solver = frepple.solver_mrp(
constraints=constraint,
plantype=plantype,
loglevel=int(Parameter.getValue('plan.loglevel', database, 0)),
lazydelay=int(Parameter.getValue('lazydelay', database, '86400')),
allowsplits=(Parameter.getValue('allowsplits', database, 'true').lower() == "true"),
minimumdelay=int(Parameter.getValue('plan.minimumdelay', database, '0')),
rotateresources=(Parameter.getValue('plan.rotateResources', database, 'true').lower() == "true"),
plansafetystockfirst=(Parameter.getValue('plan.planSafetyStockFirst', database, 'false').lower() != "false"),
iterationmax=int(Parameter.getValue('plan.iterationmax', database, '0')),
administrativeleadtime=86400*float(Parameter.getValue('plan.administrativeLeadtime', database, '0'))
)
if hasattr(cls, 'debugResource'):
cls.solver.userexit_resource = cls.debugResource
if hasattr(cls, 'debugDemand'):
cls.solver.userexit_demand = cls.debugDemand
if hasattr(cls, 'debugOperation'):
cls.solver.userexit_operation = cls.debugOperation
print("Plan type: ", plantype)
print("Constraints: ", constraint)
cls.solver.solve()
frepple.printsize()
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue("plan.loglevel", database, 0))
# Propagate the operationplan status
logger.info("Propagating work-in-progress status information")
frepple.solver_propagateStatus(loglevel=loglevel).solve()
# Update the feasibility flag of all operationplans
for oper in frepple.operations():
for opplan in oper.operationplans:
opplan.updateFeasible()
# Report the result
print("Initial problems:")
probs = {}
for i in frepple.problems():
if i.name in probs:
probs[i.name] += 1
else:
probs[i.name] = 1
for i in sorted(probs.keys()):
print(" %s: %s" % (i, probs[i]))
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue('plan.loglevel', database, 0))
if cls.task and cls.task.user:
maxloglevel = cls.task.user.getMaxLoglevel(database)
if loglevel > maxloglevel:
loglevel = maxloglevel
# Propagate the operationplan status
frepple.solver_propagateStatus(
loglevel=loglevel
).solve()
# Update the feasibility flag of all operationplans
for oper in frepple.operations():
for opplan in oper.operationplans:
opplan.updateFeasible()
# Report the result
print ("Initial problems:")
probs = {}
for i in frepple.problems():
if i.name in probs:
probs[i.name] += 1
else:
probs[i.name] = 1
for i in sorted(probs.keys()):
print(" %s: %s" % (i, probs[i]))
def createPlan(database = DEFAULT_DB_ALIAS):
# Auxiliary functions for debugging
def debugResource(res,mode):
# if res.name != 'my favorite resource': return
print("=> Situation on resource", res.name)
for j in res.loadplans:
print("=> ", j.quantity, j.onhand, j.startdate, j.enddate, j.operation.name, j.operationplan.quantity, j.setup)
def debugDemand(dem,mode):
if dem.name == 'my favorite demand':
print("=> Starting to plan demand ", dem.name)
solver.loglevel = 2
else:
solver.loglevel = 0
# Create a solver where the plan type are defined by an environment variable
try: plantype = int(os.environ['FREPPLE_PLANTYPE'])
except: plantype = 1 # Default is a constrained plan
try: constraint = int(os.environ['FREPPLE_CONSTRAINT'])
except: constraint = 15 # Default is with all constraints enabled
solver = frepple.solver_mrp(name = "MRP", constraints = constraint,
plantype = plantype, loglevel=int(Parameter.getValue('plan.loglevel', database, 0))
#userexit_resource=debugResource,
#userexit_demand=debugDemand
)
print("Plan type: ", plantype)
print("Constraints: ", constraint)
solver.solve()
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue('plan.loglevel', database, 0))
if cls.task and cls.task.user:
maxloglevel = cls.task.user.getMaxLoglevel(database)
if loglevel > maxloglevel:
loglevel = maxloglevel
# Propagate the operationplan status
frepple.solver_propagateStatus(loglevel=loglevel).solve()
# Update the feasibility flag of all operationplans
for oper in frepple.operations():
for opplan in oper.operationplans:
opplan.updateFeasible()
# Report the result
print("Initial problems:")
probs = {}
for i in frepple.problems():
if i.name in probs:
probs[i.name] += 1
else:
probs[i.name] = 1
for i in sorted(probs.keys()):
print(" %s: %s" % (i, probs[i]))
def exportData(self, task, cursor):
exportPurchasingPlan = Parameter.getValue("openbravo.exportPurchasingPlan", self.database, default="false")
if exportPurchasingPlan.lower() == "true":
self.export_purchasingplan(cursor)
else:
self.export_procurement_order(cursor)
self.export_work_order(cursor)
self.export_sales_order(cursor)
def exportData(self, task, cursor):
exportPurchasingPlan = Parameter.getValue(
"openbravo.exportPurchasingPlan", self.database, default="false")
if exportPurchasingPlan.lower() == "true":
self.export_purchasingplan(cursor)
else:
self.export_procurement_order(cursor)
self.export_work_order(cursor)
self.export_sales_order(cursor)
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Determine log level
loglevel = int(Parameter.getValue('plan.loglevel', database, 0))
if cls.task and cls.task.user:
maxloglevel = cls.task.user.getMaxLoglevel(database)
if loglevel > maxloglevel:
loglevel = maxloglevel
# Create a solver where the plan type are defined by an environment variable
try:
plantype = int(os.environ['FREPPLE_PLANTYPE'])
except:
plantype = 1 # Default is a constrained plan
try:
constraint = int(os.environ['FREPPLE_CONSTRAINT'])
except:
constraint = 15 # Default is with all constraints enabled
cls.solver = frepple.solver_mrp(
constraints=constraint,
plantype=plantype,
loglevel=loglevel,
lazydelay=int(Parameter.getValue('lazydelay', database, '86400')),
allowsplits=(Parameter.getValue('allowsplits', database, 'true').lower() == "true"),
minimumdelay=int(Parameter.getValue('plan.minimumdelay', database, '0')),
rotateresources=(Parameter.getValue('plan.rotateResources', database, 'true').lower() == "true"),
plansafetystockfirst=(Parameter.getValue('plan.planSafetyStockFirst', database, 'false').lower() != "false"),
iterationmax=int(Parameter.getValue('plan.iterationmax', database, '0')),
resourceiterationmax=int(Parameter.getValue('plan.resourceiterationmax', database, '500')),
administrativeleadtime=86400 * float(Parameter.getValue('plan.administrativeLeadtime', database, '0')),
autofence=86400 * float(Parameter.getValue("plan.autoFenceOperations", database, '0'))
)
if hasattr(cls, 'debugResource'):
cls.solver.userexit_resource = cls.debugResource
if hasattr(cls, 'debugDemand'):
cls.solver.userexit_demand = cls.debugDemand
if hasattr(cls, 'debugOperation'):
cls.solver.userexit_operation = cls.debugOperation
logger.info("Plan type: %s" % plantype)
logger.info("Constraints: %s" % constraint)
cls.solver.solve()
frepple.printsize()
def export_work_order(self, cursor):
if self.filteredexport:
filter_expression = 'and (%s) ' % Parameter.getValue('openbravo.filter_export_manufacturing_order', self.database, "")
else:
filter_expression = ""
try:
starttime = time()
if self.verbosity > 0:
print("Exporting work orders...")
cursor.execute('''
select operation.source, out_operationplan.quantity, startdate, enddate
from out_operationplan
inner join operation
on out_operationplan.operation = operation.name
and operation.type = 'routing'
where operation like 'Process%' %s ''' % filter_expression)
count = 0
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
for i in cursor.fetchall():
# TODO generate documentno? <documentNo>10000000</documentNo>
body.append('''<ManufacturingWorkRequirement>
<organization id="%s" entity-name="Organization"/>
<active>true</active>
<processPlan id="%s" entity-name="ManufacturingProcessPlan"/>
<quantity>%s</quantity>
<startingDate>%s.0Z</startingDate>
<endingDate>%s.0Z</endingDate>
<closed>false</closed>
<insertProductsAndorPhases>true</insertProductsAndorPhases>
<includePhasesWhenInserting>true</includePhasesWhenInserting>
<processed>false</processed>
</ManufacturingWorkRequirement>
''' % (self.organization_id, i[0], i[1],
i[2].strftime("%Y-%m-%dT%H:%M:%S"), i[3].strftime("%Y-%m-%dT%H:%M:%S")
))
count += 1
if self.verbosity > 0 and count % 500 == 1:
print('.', end="")
if self.verbosity > 0:
print('')
body.append('</ob:Openbravo>')
post_data(
'\n'.join(body),
'/openbravo/ws/dal/ManufacturingWorkRequirement',
self.openbravo_host, self.openbravo_user, self.openbravo_password
)
if self.verbosity > 0:
print("Updated %d work orders in %.2f seconds" % (count, (time() - starttime)))
except Exception as e:
raise CommandError("Error updating work orders: %s" % e)
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)
if settings.ODOO_PASSWORDS.get(database) == '':
odoo_password = Parameter.getValue("odoo.password", database)
else:
odoo_password = settings.ODOO_PASSWORDS.get(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:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("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")
# 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:
print("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)
frepple.printsize()
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")
# 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)
frepple.printsize()
def export_sales_order(self, cursor):
try:
starttime = time()
if self.verbosity > 0:
print("Exporting expected delivery date of sales orders...")
fltr = Parameter.getValue('openbravo.filter_export_sales_order',
self.database, "")
if self.filteredexport and fltr:
filter_expression = 'and (%s) ' % fltr
else:
filter_expression = ""
cursor.execute('''select demand.source, max(operationplan.enddate)
from demand
inner join operationplan
on operationplan.demand_id = demand.name
inner join item on demand.item_id = item.name
inner join location on demand.location_id = location.name
inner join customer on demand.customer_id = customer.name
where demand.subcategory = 'openbravo'
and demand.status = 'open' %s
group by demand.source
''' % filter_expression)
count = 0
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
for i in cursor.fetchall():
body.append(
'<OrderLine id="%s"><description>frePPLe planned delivery date: %s</description></OrderLine>'
% i)
count += 1
if self.verbosity > 0 and count % 500 == 1:
print('.', end="")
if self.verbosity > 0:
print('')
body.append('</ob:Openbravo>')
get_data('/ws/dal/OrderLine',
self.openbravo_host,
self.openbravo_user,
self.openbravo_password,
method='POST',
xmldoc='\n'.join(body))
if self.verbosity > 0:
print("Updated %d sales orders in %.2f seconds" %
(count, (time() - starttime)))
except Exception as e:
raise CommandError("Error updating sales orders: %s" % e)
def odoo_read(db=DEFAULT_DB_ALIAS):
'''
This function connects to a URL, authenticates itself using HTTP basic
authentication, and then reads data from the URL.
The data from the source must adhere to frePPLe's official XML schema,
as defined in the schema files bin/frepple.xsd and bin/frepple_core.xsd.
'''
odoo_user = Parameter.getValue("odoo.user", db)
odoo_password = Parameter.getValue("odoo.password", db)
odoo_db = Parameter.getValue("odoo.db", db)
odoo_url = Parameter.getValue("odoo.url", db)
odoo_company = Parameter.getValue("odoo.company", db)
ok = True
if not odoo_user:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", db, 'en_US')
if not ok:
raise Exception("Odoo connector not configured correctly")
# Connect to the odoo URL to GET data
f = None
try:
request = Request("%sfrepple/xml/?%s" %
(odoo_url,
urlencode({
'database': odoo_db,
'language': odoo_language,
'company': odoo_company
})))
encoded = base64.encodestring(
('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))[:-1]
request.add_header("Authorization",
"Basic %s" % encoded.decode('ascii'))
f = urlopen(request)
except HTTPError as e:
print("Error connecting to odoo", e)
raise e
# Download and parse XML data
try:
frepple.readXMLdata(f.read().decode('utf-8'), False, False)
finally:
if f: f.close()
def odoo_read(db=DEFAULT_DB_ALIAS):
'''
This function connects to a URL, authenticates itself using HTTP basic
authentication, and then reads data from the URL.
The data from the source must adhere to frePPLe's official XML schema,
as defined in the schema files bin/frepple.xsd and bin/frepple_core.xsd.
'''
odoo_user = Parameter.getValue("odoo.user", db)
if settings.OODO_PASSWORDS.get(db) == '':
odoo_password = Parameter.getValue("odoo.password", db)
else:
odoo_password = settings.OODO_PASSWORDS.get(db)
odoo_db = Parameter.getValue("odoo.db", db)
odoo_url = Parameter.getValue("odoo.url", db)
odoo_company = Parameter.getValue("odoo.company", db)
ok = True
if not odoo_user:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", db, 'en_US')
if not ok:
raise Exception("Odoo connector not configured correctly")
# Connect to the odoo URL to GET data
f = None
try:
request = Request("%sfrepple/xml/?%s" % (odoo_url, urlencode({
'database': odoo_db, 'language': odoo_language, 'company': odoo_company
})))
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))[:-1]
request.add_header("Authorization", "Basic %s" % encoded.decode('ascii'))
f = urlopen(request)
except HTTPError as e:
print("Error connecting to odoo", e)
raise e
# Download and parse XML data
try:
frepple.readXMLdata(f.read().decode('utf-8'), False, False)
finally:
if f: f.close()
def export_sales_order(self, cursor):
try:
starttime = time()
if self.verbosity > 0:
print("Exporting expected delivery date of sales orders...")
fltr = Parameter.getValue('openbravo.filter_export_sales_order', self.database, "")
if self.filteredexport and fltr:
filter_expression = 'and (%s) ' % fltr
else:
filter_expression = ""
cursor.execute('''select demand.source, max(operationplan.enddate)
from demand
inner join operationplan
on operationplan.demand_id = demand.name
inner join item on demand.item_id = item.name
inner join location on demand.location_id = location.name
inner join customer on demand.customer_id = customer.name
where demand.subcategory = 'openbravo'
and demand.status = 'open' %s
group by demand.source
''' % filter_expression)
count = 0
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
for i in cursor.fetchall():
body.append('<OrderLine id="%s"><description>frePPLe planned delivery date: %s</description></OrderLine>' % i)
count += 1
if self.verbosity > 0 and count % 500 == 1:
print('.', end="")
if self.verbosity > 0:
print ('')
body.append('</ob:Openbravo>')
get_data(
'/ws/dal/OrderLine',
self.openbravo_host, self.openbravo_user, self.openbravo_password,
method='POST', xmldoc='\n'.join(body)
)
if self.verbosity > 0:
print("Updated %d sales orders in %.2f seconds" % (count, (time() - starttime)))
except Exception as e:
raise CommandError("Error updating sales orders: %s" % e)
def run(database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Auxiliary functions for debugging
def debugResource(res, mode):
# if res.name != 'my favorite resource': return
print("=> Situation on resource", res.name)
for j in res.loadplans:
print("=> ", j.quantity, j.onhand, j.startdate, j.enddate,
j.operation.name, j.operationplan.quantity, j.setup)
def debugDemand(dem, mode):
if dem.name == 'my favorite demand':
print("=> Starting to plan demand ", dem.name)
solver.loglevel = 2
else:
solver.loglevel = 0
# Create a solver where the plan type are defined by an environment variable
try:
plantype = int(os.environ['FREPPLE_PLANTYPE'])
except:
plantype = 1 # Default is a constrained plan
try:
constraint = int(os.environ['FREPPLE_CONSTRAINT'])
except:
constraint = 15 # Default is with all constraints enabled
solver = frepple.solver_mrp(
constraints=constraint,
plantype=plantype,
loglevel=int(Parameter.getValue('plan.loglevel', database, 0)),
lazydelay=int(Parameter.getValue('lazydelay', database, '86400')),
allowsplits=(Parameter.getValue('allowsplits', database,
'true').lower() == "true"),
rotateresources=(Parameter.getValue('plan.rotateResources',
database,
'true').lower() == "true"),
plansafetystockfirst=(Parameter.getValue(
'plan.planSafetyStockFirst', database, 'false').lower() !=
"false"),
iterationmax=int(
Parameter.getValue('plan.iterationmax', database, '0'))
#userexit_resource=debugResource,
#userexit_demand=debugDemand
)
print("Plan type: ", plantype)
print("Constraints: ", constraint)
solver.solve()
frepple.printsize()
def run(database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
# Auxiliary functions for debugging
def debugResource(res, mode):
# if res.name != 'my favorite resource': return
print("=> Situation on resource", res.name)
for j in res.loadplans:
print("=> ", j.quantity, j.onhand, j.startdate, j.enddate, j.operation.name, j.operationplan.quantity, j.setup)
def debugDemand(dem, mode):
if dem.name == 'my favorite demand':
print("=> Starting to plan demand ", dem.name)
solver.loglevel = 2
else:
solver.loglevel = 0
# Create a solver where the plan type are defined by an environment variable
try:
plantype = int(os.environ['FREPPLE_PLANTYPE'])
except:
plantype = 1 # Default is a constrained plan
try:
constraint = int(os.environ['FREPPLE_CONSTRAINT'])
except:
constraint = 15 # Default is with all constraints enabled
solver = frepple.solver_mrp(
constraints=constraint,
plantype=plantype,
loglevel=int(Parameter.getValue('plan.loglevel', database, 0)),
lazydelay=int(Parameter.getValue('lazydelay', database, '86400')),
allowsplits=(Parameter.getValue('allowsplits', database, 'true').lower() == "true"),
rotateresources=(Parameter.getValue('plan.rotateResources', database, 'true').lower() == "true"),
plansafetystockfirst=(Parameter.getValue('plan.planSafetyStockFirst', database, 'false').lower() != "false"),
iterationmax=int(Parameter.getValue('plan.iterationmax', database, '0'))
#userexit_resource=debugResource,
#userexit_demand=debugDemand
)
print("Plan type: ", plantype)
print("Constraints: ", constraint)
solver.solve()
frepple.printsize()
def __init__(self, task, database=DEFAULT_DB_ALIAS, verbosity=0):
self.task = task
self.database = database
self.verbosity = verbosity
# Pick up configuration parameters
self.odoo_user = Parameter.getValue("odoo.user", self.database)
self.odoo_password = Parameter.getValue("odoo.password", self.database)
self.odoo_db = Parameter.getValue("odoo.db", self.database)
self.odoo_url = Parameter.getValue("odoo.url", self.database)
self.odoo_company = Parameter.getValue("odoo.company", self.database)
if not self.odoo_user:
raise CommandError("Missing or invalid parameter odoo.user")
if not self.odoo_password:
raise CommandError("Missing or invalid parameter odoo.password")
if not self.odoo_db:
raise CommandError("Missing or invalid parameter odoo.db")
if not self.odoo_url:
raise CommandError("Missing or invalid parameter odoo.url")
if not self.odoo_company:
raise CommandError("Missing or invalid parameter odoo.company")
self.odoo_language = Parameter.getValue("odoo.language", self.database, 'en_US')
self.context = {'lang': self.odoo_language}
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
odoo_user = Parameter.getValue("odoo.user", 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:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("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")
boundary = email.generator._make_boundary()
# Generator function
# We generate output in the multipart/form-data format.
# We send the connection parameters as well as a file with the planning
# results in XML-format.
def publishPlan():
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="webtoken"\r'
yield '\r'
yield '%s\r' % jwt.encode({
'exp': round(time.time()) + 600,
'user': odoo_user,
},
settings.DATABASES[database].get('SECRET_WEBTOKEN_KEY', settings.SECRET_KEY),
algorithm='HS256').decode('ascii')
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="database"\r'
yield '\r'
yield '%s\r' % odoo_db
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="language"\r'
yield '\r'
yield '%s\r' % odoo_language
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="company"\r'
yield '\r'
yield '%s\r' % odoo_company
yield '--%s\r' % boundary
yield 'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"\r'
yield 'Content-Type: application/xml\r'
yield '\r'
yield '<?xml version="1.0" encoding="UTF-8" ?>'
yield '<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
# Export relevant operationplans
yield '<operationplans>'
for i in frepple.operationplans():
b = None
for j in i.flowplans:
if j.quantity > 0:
b = j.flow.buffer
if not b or not b.source or not b.source.startswith('odoo') or i.locked:
continue
yield '<operationplan id="%s" operation=%s start="%s" end="%s" quantity="%s" location=%s item=%s criticality="%d"/>' % (
i.id, quoteattr(i.operation.name),
i.start, i.end, i.quantity,
quoteattr(b.location.subcategory), quoteattr(b.item.subcategory),
int(i.criticality)
)
yield '</operationplans>'
yield '</plan>'
yield '--%s--\r' % boundary
yield '\r'
# Connect to the odoo URL to POST data
try:
body = '\n'.join(publishPlan()).encode('utf-8')
size = len(body)
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))
req = Request(
"%sfrepple/xml/" % odoo_url,
data=body,
headers={
'Authorization': "Basic %s" % encoded.decode('ascii')[:-1],
'Content-Type': 'multipart/form-data; boundary=%s' % boundary,
'Content-length': size
}
)
# Posting the data and displaying the server response
print("Uploading %d bytes of planning results to odoo" % size)
with urlopen(req) as f:
msg = f.read()
print("Odoo response: %s" % msg.decode('utf-8'))
except HTTPError as e:
print("Error connecting to odoo", e.read())
raise e
def handle(self, **options):
# Pick up the options
if 'verbosity' in options:
self.verbosity = int(options['verbosity'] or '1')
else:
self.verbosity = 1
if 'user' in options:
user = options['user']
else:
user = ''
if 'database' in options:
self.database = options['database'] or DEFAULT_DB_ALIAS
else:
self.database = DEFAULT_DB_ALIAS
if not self.database in settings.DATABASES.keys():
raise CommandError("No database settings known for '%s'" %
self.database)
# Pick up configuration parameters
self.openbravo_user = Parameter.getValue("openbravo.user",
self.database)
self.openbravo_password = Parameter.getValue("openbravo.password",
self.database)
self.openbravo_host = Parameter.getValue("openbravo.host",
self.database)
self.openbravo_organization = Parameter.getValue(
"openbravo.organization", self.database)
if not self.openbravo_user:
raise CommandError("Missing or invalid parameter openbravo_user")
if not self.openbravo_password:
raise CommandError(
"Missing or invalid parameter openbravo_password")
if not self.openbravo_host:
raise CommandError("Missing or invalid parameter openbravo_host")
if not self.openbravo_organization:
raise CommandError(
"Missing or invalid parameter openbravo_organization")
# Make sure the debug flag is not set!
# When it is set, the django database wrapper collects a list of all SQL
# statements executed and their timings. This consumes plenty of memory
# and cpu time.
tmp_debug = settings.DEBUG
settings.DEBUG = False
now = datetime.now()
task = None
try:
# Initialize the task
if 'task' in options and options['task']:
try:
task = Task.objects.all().using(
self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'Openbravo export':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='Openbravo export',
submitted=now,
started=now,
status='0%',
user=user)
task.save(using=self.database)
# Create a database connection to the frePPLe database
cursor = connections[self.database].cursor()
# Look up the id of the Openbravo user
query = urllib.quote("name='%s'" %
self.openbravo_user.encode('utf8'))
conn = self.get_data(
"/openbravo/ws/dal/ADUser?where=%s&includeChildren=false" %
query)[0]
self.user_id = None
for event, elem in conn:
if event != 'end' or elem.tag != 'ADUser':
continue
self.user_id = elem.get('id')
if not self.user_id:
raise CommandError("Can't find user id in Openbravo")
# Look up the id of the Openbravo organization id
query = urllib.quote("name='%s'" %
self.openbravo_organization.encode('utf8'))
conn = self.get_data(
"/openbravo/ws/dal/Organization?where=%s&includeChildren=false"
% query)[0]
self.organization_id = None
for event, elem in conn:
if event != 'end' or elem.tag != 'Organization':
continue
self.organization_id = elem.get('id')
if not self.organization_id:
raise CommandError("Can't find organization id in Openbravo")
# Upload all data
self.export_procurement_order(cursor)
self.export_work_order(cursor)
self.export_sales_order(cursor)
# Log success
task.status = 'Done'
task.finished = datetime.now()
except Exception as e:
if task:
task.status = 'Failed'
task.message = '%s' % e
task.finished = datetime.now()
raise e
finally:
if task:
task.save(using=self.database)
settings.DEBUG = tmp_debug
def export_work_order(self, cursor):
fltr = Parameter.getValue('openbravo.filter_export_manufacturing_order', self.database, "")
if self.filteredexport and fltr:
filter_expression = 'and (%s) ' % fltr
else:
filter_expression = ""
if True: #try:
starttime = time()
if self.verbosity > 0:
print("Exporting work orders...")
cursor.execute('''
select operation.source, operationplan.quantity, startdate, enddate
from operationplan
inner join operation
on operationplan.operation_id = operation.name
and operation.type = 'routing'
and operation.name like 'Process%%'
where operationplan.type = 'MO' %s ''' % filter_expression)
count = 0
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
for i in cursor.fetchall():
body.append('''<ManufacturingWorkRequirement>
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<processPlan id="%s" entity-name="ManufacturingProcessPlan"/>
<quantity>%s</quantity>
<startingDate>%s</startingDate>
<endingDate>%s</endingDate>
<closed>false</closed>
<insertProductsAndorPhases>false</insertProductsAndorPhases>
<processed>false</processed>
<includePhasesWhenInserting>true</includePhasesWhenInserting>
<processQuantity>0</processQuantity>
<createworkrequirement>false</createworkrequirement>
<closedStat>false</closedStat>
</ManufacturingWorkRequirement>
''' % (
self.organization_id, self.openbravo_organization,
i[0], i[1], i[2].strftime("%Y-%m-%d %H:%M:%S"),
i[3].strftime("%Y-%m-%d %H:%M:%S")
))
count += 1
if self.verbosity > 0 and count % 500 == 1:
print('.', end="")
if self.verbosity > 0:
print('')
body.append('</ob:Openbravo>')
get_data(
'/ws/org.openbravo.warehouse.advancedwarehouseoperations.manufacturing.AddWorkRequirementsWS',
self.openbravo_host, self.openbravo_user, self.openbravo_password,
method="PUT",
xmldoc='\n'.join(body),
headers={'DoProcess': 'true'}
)
if self.verbosity > 0:
print("Updated %d work orders in %.2f seconds" % (count, (time() - starttime)))
def odoo_read(db=DEFAULT_DB_ALIAS, mode=1):
'''
This function connects to a URL, authenticates itself using HTTP basic
authentication, and then reads data from the URL.
The data from the source must adhere to frePPLe's official XML schema,
as defined in the schema files bin/frepple.xsd and bin/frepple_core.xsd.
The mode is pass as an argument:
- Mode 1:
This mode returns all data that is loaded with every planning run.
- Mode 2:
This mode returns data that is loaded that changes infrequently and
can be transferred during automated scheduled runs at a quiet moment.
Which data elements belong to each category is determined in the odoo
addon module and can vary between implementations.
'''
odoo_user = Parameter.getValue("odoo.user", db)
if settings.ODOO_PASSWORDS.get(db) == '':
odoo_password = Parameter.getValue("odoo.password", db)
else:
odoo_password = settings.ODOO_PASSWORDS.get(db)
odoo_db = Parameter.getValue("odoo.db", db)
odoo_url = Parameter.getValue("odoo.url", db)
odoo_company = Parameter.getValue("odoo.company", db)
ok = True
if not odoo_user:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", db, 'en_US')
if not ok:
raise Exception("Odoo connector not configured correctly")
# Connect to the odoo URL to GET data
f = None
url = "%sfrepple/xml?%s" % (odoo_url, urlencode({
'database': odoo_db,
'language': odoo_language,
'company': odoo_company,
'mode': 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'))
f = urlopen(request)
except HTTPError as e:
print("Error connecting to odoo at %s: %s" % (url, e))
raise e
# Download and parse XML data
try:
frepple.readXMLdata(f.read().decode('utf-8'), False, False)
finally:
if f: f.close()
def export_purchasingplan(self, cursor):
purchaseplan = '''<MRPPurchasingRun id="%s">
<organization id="%s" entity-name="Organization"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Bulk export</description>
<timeHorizon>365</timeHorizon>
<safetyLeadTime>0</safetyLeadTime>
<mRPPurchasingRunLineList>'''
purchasingplanline = '''<ProcurementRequisitionLine>
<active>true</active>
<requisition id="%s" entity-name="ProcurementRequisition"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<uOM id="100" entity-name="UOM" identifier="Unit"/>
<requisitionLineStatus>O</requisitionLineStatus>
<needByDate>%s.0Z</needByDate>
<lineNo>%s</lineNo>
</ProcurementRequisitionLine>'''
try:
# Close the old purchasing plan generated by frePPLe
if self.verbosity > 0:
print("Closing previous purchasing plan generated from frePPLe")
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
query = urllib.parse.quote("createdBy='%s' and purchasingPlan.description='Bulk export'" % self.openbravo_user_id)
data = delete_data(
"/openbravo/ws/dal/MRPPurchasingRunLine?where=%s" % query,
self.openbravo_host,
self.openbravo_user,
self.openbravo_password
)
query = urllib.parse.quote("createdBy='%s' and description='Bulk export'" % self.openbravo_user_id)
data = delete_data(
"/openbravo/ws/dal/MRPPurchasingRun?where=%s" % query,
self.openbravo_host,
self.openbravo_user,
self.openbravo_password
)
if self.filteredexport:
filter_expression_po = Parameter.getValue('openbravo.filter_export_purchase_order', self.database, "")
if filter_expression_po:
filter_expression_po = ' and (%s) ' %filter_expression_po
filter_expression_do = Parameter.getValue('openbravo.filter_export_distribution_order', self.database, "")
if filter_expression_do:
filter_expression_do = ' and (%s) ' %filter_expression_do
else:
filter_expression_po = ""
filter_expression_do = ""
# Create new purchase plan
starttime = time()
if self.verbosity > 0:
print("Exporting new purchasing plan...")
count = 0
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
identifier = uuid4().hex
body = [purchaseplan % (identifier, self.organization_id, now),]
cursor.execute('''
select item.source, location.source, enddate, quantity
FROM purchase_order
inner JOIN buffer
ON buffer.item_id = purchase_order.item_id
AND buffer.location_id = purchase_order.location_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = purchase_order.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON purchase_order.location_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where status = 'proposed' %s
union all
select item.source, location.source, enddate, quantity
FROM distribution_order
inner JOIN buffer
ON buffer.item_id = distribution_order.item_id
AND buffer.location_id = distribution_order.destination_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = distribution_order.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON distribution_order.destination_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where status = 'proposed' %s
''' % (filter_expression_po,filter_expression_do))
for i in cursor.fetchall():
body.append(purchasingplanline % (identifier, i[0], i[3], i[2].strftime("%Y-%m-%dT%H:%M:%S"), count))
count += 1
body.append('</mRPPurchasingRunLineList>')
body.append('</MRPPurchasingRun>')
body.append('</ob:Openbravo>')
post_data(
'\n'.join(body),
'/openbravo/ws/dal/MRPPurchasingRun',
self.openbravo_host, self.openbravo_user, self.openbravo_password
)
if self.verbosity > 0:
print("Created purchasing plan with %d lines in %.2f seconds" % (count, (time() - starttime)))
except Exception as e:
raise CommandError("Error updating purchasing plan: %s" % e)
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
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")
boundary = email.generator._make_boundary()
# Generator function
# We generate output in the multipart/form-data format.
# We send the connection parameters as well as a file with the planning
# results in XML-format.
# TODO respect the parameters odoo.filter_export_purchase_order, odoo.filter_export_manufacturing_order, odoo.filter_export_distribution_order
# these are python expressions - attack-sensitive evaluation!
def publishPlan(cls):
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="webtoken"\r'
yield '\r'
yield '%s\r' % jwt.encode({
'exp': round(time.time()) + 600,
'user': odoo_user,
},
settings.DATABASES[database].get('SECRET_WEBTOKEN_KEY', settings.SECRET_KEY),
algorithm='HS256').decode('ascii')
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="database"\r'
yield '\r'
yield '%s\r' % odoo_db
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="language"\r'
yield '\r'
yield '%s\r' % odoo_language
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="company"\r'
yield '\r'
yield '%s\r' % odoo_company
yield '--%s\r' % boundary
yield 'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"\r'
yield 'Content-Type: application/xml\r'
yield '\r'
yield '<?xml version="1.0" encoding="UTF-8" ?>'
yield '<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
# Export relevant operationplans
yield '<operationplans>'
for i in frepple.operationplans():
if i.ordertype == 'PO':
if not i.item or not i.item.source or not i.item.source.startswith('odoo') or i.status not in ('proposed', 'approved'):
continue
cls.exported.append(i)
yield '<operationplan id="%s" ordertype="PO" item=%s location=%s supplier=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
i.id, quoteattr(i.item.name), quoteattr(i.location.name),
quoteattr(i.supplier.name), i.start, i.end, i.quantity,
quoteattr(i.location.subcategory), quoteattr(i.item.subcategory),
int(i.criticality)
)
elif i.ordertype == "MO":
if not i.operation or not i.operation.source \
or not i.operation.item \
or not i.operation.source.startswith('odoo') \
or i.status not in ('proposed', 'approved'):
continue
cls.exported.append(i)
yield '<operationplan id="%s" ordertype="MO" item=%s location=%s operation=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
i.id, quoteattr(i.operation.item.name), quoteattr(i.operation.location.name),
quoteattr(i.operation.name), i.start, i.end, i.quantity,
quoteattr(i.operation.location.subcategory), quoteattr(i.operation.item.subcategory),
int(i.criticality)
)
yield '</operationplans>'
yield '</plan>'
yield '--%s--\r' % boundary
yield '\r'
# Connect to the odoo URL to POST data
try:
cls.exported = []
body = '\n'.join(publishPlan(cls)).encode('utf-8')
size = len(body)
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))
req = Request(
"%sfrepple/xml/" % odoo_url,
data=body,
headers={
'Authorization': "Basic %s" % encoded.decode('ascii')[:-1],
'Content-Type': 'multipart/form-data; boundary=%s' % boundary,
'Content-length': size
}
)
# Posting the data and displaying the server response
logger.info("Uploading %d bytes of planning results to odoo" % size)
with urlopen(req) as f:
msg = f.read()
logger.info("Odoo response: %s" % msg.decode('utf-8'))
# Mark the exported operations as approved
for i in cls.exported:
i.status = 'approved'
del cls.exported
except HTTPError as e:
logger.error("Error connecting to odoo %s" % e.read())
def odoo_write(db=DEFAULT_DB_ALIAS):
'''
Uploads operationplans to odoo.
- Sends all operationplans, meeting the criteria:
a) locked = False
The operationplans with locked equal to true are input to the plan,
and not output.
b) operationplan produces into a buffer whose source field is 'odoo'.
Only those results are of interest to odoo.
- We upload the following info in XML form:
- id: frePPLe generated unique identifier
- operation
- start
- end
- quantity
- location: This is the odoo id of the location, as stored in
buffer.location.subcategory.
- item: This is the odoo id of the produced item and its uom_id, as
stored in buffer.item.subcategory.
- criticality: 0 indicates a critical operationplan, 999 indicates a
redundant operationplan.
- The XML file uploaded is not exactly the standard XML of frePPLe, but a
slight variation that fits odoo better.
- This code doesn't interprete any of the results. An odoo addon module
will need to read the content, and take appropriate actions in odoo:
such as creating purchase orders, manufacturing orders, work orders,
project tasks, etc...
'''
odoo_user = Parameter.getValue("odoo.user", db)
odoo_password = Parameter.getValue("odoo.password", db)
odoo_db = Parameter.getValue("odoo.db", db)
odoo_url = Parameter.getValue("odoo.url", db)
odoo_company = Parameter.getValue("odoo.company", db)
ok = True
if not odoo_user:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", db, 'en_US')
if not ok:
raise Exception("Odoo connector not configured correctly")
boundary = email.generator._make_boundary()
# Generator function
# We generate output in the multipart/form-data format.
# We send the connection parameters as well as a file with the planning
# results in XML-format.
def publishPlan():
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="database"\r'
yield '\r'
yield '%s\r' % odoo_db
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="language"\r'
yield '\r'
yield '%s\r' % odoo_language
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="company"\r'
yield '\r'
yield '%s\r' % odoo_company
yield '--%s\r' % boundary
yield 'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"\r'
yield 'Content-Type: application/xml\r'
yield '\r'
yield '<?xml version="1.0" encoding="UTF-8" ?>'
yield '<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
# Export relevant operationplans
yield '<operationplans>'
for i in frepple.operationplans():
b = None
for j in i.flowplans:
if j.quantity > 0:
b = j.flow.buffer
if not b or b.source != 'odoo' or i.locked:
continue
yield '<operationplan id="%s" operation=%s start="%s" end="%s" quantity="%s" location=%s item=%s criticality="%d"/>' % (
i.id, quoteattr(i.operation.name),
i.start, i.end, i.quantity,
quoteattr(b.location.subcategory), quoteattr(b.item.subcategory),
int(i.criticality)
)
yield '</operationplans>'
yield '</plan>'
yield '--%s--\r' % boundary
yield '\r'
# Connect to the odoo URL to POST data
try:
req = Request("%s/frepple/xml/" % odoo_url.encode('ascii'))
body = '\n'.join(publishPlan()).encode('utf-8')
size = len(body)
encoded = base64.encodestring('%s:%s' % (odoo_user, odoo_password)).replace('\n', '')
req.add_header("Authorization", "Basic %s" % encoded)
req.add_header("Content-Type", 'multipart/form-data; boundary=%s' % boundary)
req.add_header('Content-length', size)
req.add_data(body)
# Posting the data
print("Uploading %d bytes of planning results to odoo" % size)
req.get_data()
# Display the server response, which can contain error messages
print("Odoo response:")
for i in urlopen(req):
print(i, end="")
print("")
except HTTPError as e:
print("Error connecting to odoo", e.read())
raise e
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 export_purchasingplan(self, cursor):
purchaseplan = '''<MRPPurchasingRun id="%s">
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Bulk export</description>
<timeHorizon>365</timeHorizon>
<safetyLeadTime>0</safetyLeadTime>
<mRPPurchasingRunLineList>'''
purchasingplanline = '''<MRPPurchasingRunLine id="%s">
<active>true</active>
<purchasingPlan id="%s" entity-name="MRPPurchasingRun"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<requiredQuantity>%s</requiredQuantity>
<plannedDate>%s.0Z</plannedDate>
<plannedOrderDate>%s.0Z</plannedOrderDate>
<transactionType>%s</transactionType>
<businessPartner id="%s"/>
<fixed>true</fixed>
<completed>false</completed>
</MRPPurchasingRunLine>'''
try:
# Close the old purchasing plan generated by frePPLe
if self.verbosity > 0:
print("Closing previous purchasing plan generated from frePPLe") #
query = urllib.parse.quote("createdBy='%s' "
# TODO the filter in the next line generates an incorrect query in Openbravo
"and purchasingPlan.description='Bulk export' "
"and salesOrderLine is null and workRequirement is null "
"and requisitionLine is null" % self.openbravo_user_id)
data = get_data(
"/ws/dal/MRPPurchasingRunLine?where=%s" % query,
self.openbravo_host,
self.openbravo_user,
self.openbravo_password,
method="DELETE"
)
if self.filteredexport:
filter_expression_po = Parameter.getValue('openbravo.filter_export_purchase_order', self.database, "")
if filter_expression_po:
filter_expression_po = ' and (%s) ' %filter_expression_po
filter_expression_do = Parameter.getValue('openbravo.filter_export_distribution_order', self.database, "")
if filter_expression_do:
filter_expression_do = ' and (%s) ' %filter_expression_do
else:
filter_expression_po = ""
filter_expression_do = ""
# Create new purchase plan
starttime = time()
if self.verbosity > 0:
print("Exporting new purchasing plan...")
count = 0
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
identifier = uuid4().hex
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
purchaseplan % (identifier, self.organization_id, self.openbravo_organization, now)
]
cursor.execute('''
SELECT item.source, location.source, quantity, startdate, enddate, 'PO', supplier.source
FROM purchase_order
inner JOIN buffer
ON buffer.item_id = purchase_order.item_id
AND buffer.location_id = purchase_order.location_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = purchase_order.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON purchase_order.location_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
inner join supplier
on purchase_order.supplier_id = supplier.name
and supplier.source is not null
and supplier.subcategory = 'openbravo'
where status = 'proposed' %s
''' % (filter_expression_po))
# union all
# select item.source, location.source, quantity, startdate, enddate, 'DO'
# FROM distribution_order
# inner JOIN buffer
# ON buffer.item_id = distribution_order.item_id
# AND buffer.location_id = distribution_order.destination_id
# AND buffer.subcategory = 'openbravo'
# inner join item
# ON item.name = distribution_order.item_id
# and item.source is not null
# and item.subcategory = 'openbravo'
# inner join location
# ON distribution_order.destination_id = location.name
# and location.source is not null
# and location.subcategory = 'openbravo'
# where status = 'proposed' %s
# ''' % (filter_expression_po,filter_expression_do))
for i in cursor.fetchall():
body.append(purchasingplanline % (
uuid4().hex, identifier, i[0], i[2], i[2],
i[3].strftime("%Y-%m-%d %H:%M:%S"),
i[4].strftime("%Y-%m-%d %H:%M:%S"),
i[5], i[6]
))
count += 1
break
body.append('</mRPPurchasingRunLineList>')
body.append('</MRPPurchasingRun>')
body.append('</ob:Openbravo>')
if count > 0:
get_data(
'/ws/dal/MRPPurchasingRun',
self.openbravo_host, self.openbravo_user, self.openbravo_password,
method="POST", xmldoc='\n'.join(body)
)
if self.verbosity > 0:
print("Created purchasing plan with %d lines in %.2f seconds" % (count, (time() - starttime)))
except Exception as e:
raise CommandError("Error updating purchasing plan: %s" % e)
def handle(self, **options):
# Pick up the options
if 'verbosity' in options:
self.verbosity = int(options['verbosity'] or '1')
else:
self.verbosity = 1
if 'user' in options: user = options['user']
else: user = ''
if 'database' in options:
self.database = options['database'] or DEFAULT_DB_ALIAS
else:
self.database = DEFAULT_DB_ALIAS
if not self.database in settings.DATABASES.keys():
raise CommandError("No database settings known for '%s'" %
self.database)
# Pick up configuration parameters
self.openerp_user = Parameter.getValue("openerp.user", self.database)
self.openerp_password = Parameter.getValue("openerp.password",
self.database)
self.openerp_db = Parameter.getValue("openerp.db", self.database)
self.openerp_url = Parameter.getValue("openerp.url", self.database)
# Make sure the debug flag is not set!
# When it is set, the django database wrapper collects a list of all SQL
# statements executed and their timings. This consumes plenty of memory
# and cpu time.
tmp_debug = settings.DEBUG
settings.DEBUG = False
now = datetime.now()
ac = transaction.get_autocommit(using=self.database)
transaction.set_autocommit(False, using=self.database)
task = None
try:
# Initialize the task
if 'task' in options and options['task']:
try:
task = Task.objects.all().using(
self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'OpenERP export':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='OpenERP edport',
submitted=now,
started=now,
status='0%',
user=user)
task.save(using=self.database)
transaction.commit(using=self.database)
# Log in to the openerp server
sock_common = xmlrpclib.ServerProxy(self.openerp_url +
'xmlrpc/common')
self.uid = sock_common.login(self.openerp_db, self.openerp_user,
self.openerp_password)
# Connect to openerp server
self.sock = xmlrpclib.ServerProxy(self.openerp_url +
'xmlrpc/object')
# Create a database connection to the frePPLe database
self.cursor = connections[self.database].cursor()
# Upload all data
self.export_procurement_order()
task.status = '50%'
task.save(using=self.database)
transaction.commit(using=self.database)
self.export_sales_order()
task.status = '100%'
task.save(using=self.database)
transaction.commit(using=self.database)
# Log success
task.status = 'Done'
task.finished = datetime.now()
except Exception as e:
if task:
task.status = 'Failed'
task.message = '%s' % e
task.finished = datetime.now()
raise e
finally:
if task: task.save(using=self.database)
try:
transaction.commit(using=self.database)
except:
pass
settings.DEBUG = tmp_debug
transaction.set_autocommit(ac, using=self.database)
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
with connections[database].cursor() as cursor:
# Update item metrics
try:
try:
window = frepple.settings.current + timedelta(days=int(
Parameter.getValue("metrics.demand_window", database,
"999")))
except Exception:
print("Warning: invalid parameter 'metrics.demand_window'")
window = datetime(2030, 12, 31)
Item.createRootObject(database=database)
cursor.execute(
"""
create temporary table item_hierarchy (parent character varying(300),
child character varying(300));
insert into item_hierarchy
select parent.name, item.name from item
inner join item parent on item.lft > parent.lft and item.lft < parent.rght;
create index on item_hierarchy (child);
create temporary table out_problem_tmp
as
select item.name as item_id, out_problem.name, out_problem.weight, out_problem.weight*coalesce(item.cost,0) as weight_cost
from out_problem
inner join demand on demand.name = out_problem.owner
inner join item on item.name = demand.item_id
where out_problem.name in ('unplanned', 'late')
and out_problem.startdate < %s;
""",
(window, ),
)
cursor.execute("""
create temporary table metrics as
select item.name as item_id,
coalesce(sum(case when out_problem_tmp.name = 'late' then 1 end),0) as latedemandcount,
coalesce(sum(case when out_problem_tmp.name = 'late' then out_problem_tmp.weight end),0) as latedemandquantity,
coalesce(sum(case when out_problem_tmp.name = 'late' then out_problem_tmp.weight_cost end),0) as latedemandvalue,
coalesce(sum(case when out_problem_tmp.name = 'unplanned' then 1 end),0) as unplanneddemandcount,
coalesce(sum(case when out_problem_tmp.name = 'unplanned' then out_problem_tmp.weight end),0) as unplanneddemandquantity,
coalesce(sum(case when out_problem_tmp.name = 'unplanned' then out_problem_tmp.weight_cost end),0) as unplanneddemandvalue
from item
left outer join out_problem_tmp on out_problem_tmp.item_id = item.name
where item.lft = item.rght - 1
group by item.name;
create unique index on metrics (item_id);
insert into metrics
select parent,
coalesce(sum(latedemandcount),0),
coalesce(sum(latedemandquantity),0),
coalesce(sum(latedemandvalue),0),
coalesce(sum(unplanneddemandcount),0),
coalesce(sum(unplanneddemandquantity),0),
coalesce(sum(unplanneddemandvalue),0)
from item_hierarchy
left outer join metrics on item_hierarchy.child = metrics.item_id
group by parent;
""")
cursor.execute("""
update item
set latedemandcount = metrics.latedemandcount,
latedemandquantity = metrics.latedemandquantity,
latedemandvalue = metrics.latedemandvalue,
unplanneddemandcount = metrics.unplanneddemandcount,
unplanneddemandquantity = metrics.unplanneddemandquantity,
unplanneddemandvalue = metrics.unplanneddemandvalue
from metrics
where item.name = metrics.item_id
and (item.latedemandcount is distinct from metrics.latedemandcount
or item.latedemandquantity is distinct from metrics.latedemandquantity
or item.latedemandvalue is distinct from metrics.latedemandvalue
or item.unplanneddemandcount is distinct from metrics.unplanneddemandcount
or item.unplanneddemandquantity is distinct from metrics.unplanneddemandquantity
or item.unplanneddemandvalue is distinct from metrics.unplanneddemandvalue);
""")
cursor.execute("""
drop table item_hierarchy;
drop table out_problem_tmp;
drop table metrics;
""")
except Exception as e:
print("Error updating item metrics: %s" % e)
# Update resource metrics
try:
Resource.rebuildHierarchy(database)
cursor.execute("""
with resource_hierarchy as (select child.name child, parent.name parent
from resource child
inner join resource parent on child.lft between parent.lft and parent.rght
where child.lft = child.rght-1),
cte as (
select parent, count(out_problem.id) as overloadcount from resource_hierarchy
left outer join out_problem
on out_problem.name = 'overload'
and out_problem.owner = resource_hierarchy.child
group by parent
)
update resource
set overloadcount = cte.overloadcount
from cte
where cte.parent = resource.name
and resource.overloadcount is distinct from cte.overloadcount;
""")
except Exception as e:
print("Error updating resource metrics: %s" % e)
def Upload(request):
'''
TODO we are doing lots of round trips to the database and openbravo to
read the configuration. There is considerable overhead in this.
'''
# Decode the data received from the client
data = json.loads(request.body.decode('utf-8'))
# Validate records which exist in the database
cleaned_records = []
cleaned_MO_records = []
for rec in data:
try:
if rec['type'] == 'PO':
# Purchase orders
obj = PurchaseOrder.objects.using(
request.database).get(id=rec['id'])
obj.supplier = Supplier.objects.using(request.database).get(
name=rec.get('origin') or rec.get('supplier'))
if obj.item.name != rec['item']:
obj.item = Item.objects.using(
request.database).get(name=rec['item'])
if not obj.supplier.source or not obj.item.source or obj.status != 'proposed':
continue
obj.startdate = datetime.strptime(rec['startdate'],
"%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'],
"%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_records.append(obj)
elif rec['type'] == 'OP':
# Manufacturing orders
obj = OperationPlan.objects.using(
request.database).get(id=rec['id'])
if obj.operation.name != rec['operation']:
obj.operation = Operation.objects.using(
request.database).get(name=rec['operation'])
if not obj.operation.source or obj.status != 'proposed':
continue
obj.startdate = datetime.strptime(rec['startdate'],
"%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'],
"%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_MO_records.append(obj)
elif rec['type'] == 'DO':
# Distribution orders
obj = DistributionOrder.objects.using(
request.database).get(id=rec['id'])
#obj.destination = Location.objects.using(request.database).get(name=rec['destination'])
#obj.origin = Location.object.using(request.database).get(name=rec['origin'])
if obj.item.name != rec['item']:
obj.item = Item.objects.using(
request.database).get(name=rec['item'])
if not obj.item.source or obj.status != 'proposed':
continue
obj.startdate = datetime.strptime(rec['startdate'],
"%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'],
"%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_records.append(obj)
else:
raise Exception("Unknown transaction type")
except:
pass
if not cleaned_records and not cleaned_MO_records:
return HttpResponse(content=_("No proposed data records selected"),
status=500)
# Read the configuration data from the database.
openbravo_user = Parameter.getValue("openbravo.user", request.database)
# Passwords in djangosettings file are preferably used
openbravo_password = settings.OPENBRAVO_PASSWORDS.get(
request.database, None)
if not openbravo_password:
openbravo_password = Parameter.getValue("openbravo.password",
request.database)
openbravo_host = Parameter.getValue("openbravo.host", request.database)
openbravo_organization = Parameter.getValue("openbravo.organization",
request.database)
exportPurchasingPlan = Parameter.getValue("openbravo.exportPurchasingPlan",
request.database,
default="false")
# Look up the id of the Openbravo organization id
if request.database not in openbravo_organization_ids:
query = urllib.parse.quote("name='%s'" % openbravo_organization)
data = get_data(
"/ws/dal/Organization?where=%s&includeChildren=false" % query,
openbravo_host, openbravo_user, openbravo_password)
conn = iterparse(StringIO(data), events=('start', 'end'))
for event, elem in conn:
if event == 'end' and elem.tag == 'Organization':
openbravo_organization_ids[request.database] = elem.get('id')
break
if request.database not in openbravo_organization_ids:
return HttpResponse(
content="Can't find organization id in Openbravo", status=500)
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
# Export manufacturing orders
# This export requires the advanced warehousing functionality from openbravo.
# Details on this web service can be found on:
# http://wiki.openbravo.com/wiki/Modules:Advanced_Warehouse_Operations
if cleaned_MO_records:
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">',
]
for obj in cleaned_MO_records:
body.append(
'''<ManufacturingWorkRequirement>
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<processPlan id="%s" entity-name="ManufacturingProcessPlan"/>
<quantity>%s</quantity>
<startingDate>%s</startingDate>
<endingDate>%s</endingDate>
<closed>false</closed>
<insertProductsAndorPhases>false</insertProductsAndorPhases>
<processed>false</processed>
<includePhasesWhenInserting>true</includePhasesWhenInserting>
<processQuantity>0</processQuantity>
<processUnit xsi:nil="true"/>
<conversionRate xsi:nil="true"/>
<createworkrequirement>false</createworkrequirement>
<closedStat>false</closedStat>
</ManufacturingWorkRequirement>''' %
(openbravo_organization_ids[request.database],
openbravo_organization, obj.operation.source, obj.quantity,
datetime.strftime(obj.startdate, "%Y-%m-%d %H:%M:%S"),
datetime.strftime(obj.enddate, "%Y-%m-%d %H:%M:%S")))
body.append("</ob:Openbravo>")
try:
# Send the data to openbravo
get_data(
"/ws/org.openbravo.warehouse.advancedwarehouseoperations.manufacturing.AddWorkRequirementsWS",
openbravo_host,
openbravo_user,
openbravo_password,
method="PUT",
xmldoc='\n'.join(body),
headers={'DoProcess': 'true'})
# Now save the changed status also in our database
for obj in cleaned_MO_records:
obj.save(using=request.database)
except Exception as e:
# Something went wrong in the connection
return HttpResponse(content=str(e), status=500)
# Build the distribution and purchase orders
if cleaned_records:
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
]
if exportPurchasingPlan.lower() == 'true':
identifier = uuid4().hex
url = "/ws/dal/MRPPurchasingRun"
body.append(
'''<MRPPurchasingRun id="%s">
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Incremental export triggered by %s</description>
<timeHorizon>365</timeHorizon>
<safetyLeadTime>0</safetyLeadTime>
<mRPPurchasingRunLineList>''' %
(identifier, openbravo_organization_ids[request.database],
openbravo_organization, now, request.user.username))
for obj in cleaned_records:
identifier2 = uuid4().hex
businessPartner = ''
if isinstance(obj, PurchaseOrder):
transaction_type = 'PO'
if obj.supplier and obj.supplier.source:
businessPartner = '<businessPartner id="%s"/>' % obj.supplier.source
# TODO: where to store the destination of a purchase order
else:
transaction_type = 'MF' # TODO Is this right?
# TODO: where to store the source and destination of a stock transfer order
# Possible transaction types in Openbravo are:
# - SO (Pending Sales Order)
# - PO (Pending Purchase Order)
# - WR (Pending Work Requirement)
# - SF (Sales Forecast)
# - MF (Material Requirement)
# - UD (User defined)
# - WP (Suggested Work Requirement)
# - MP (Suggested Material Requirement)
# - PP (Suggested Purchase Order)
# - ST (Stock)
# - MS (Minimum Stock): Minimum or security stock
body.append('''<MRPPurchasingRunLine id="%s">
<active>true</active>
<purchasingPlan id="%s" entity-name="MRPPurchasingRun"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<requiredQuantity>%s</requiredQuantity>
<plannedDate>%s.0Z</plannedDate>
<plannedOrderDate>%s.0Z</plannedOrderDate>
<transactionType>%s</transactionType>
%s
<fixed>true</fixed>
<completed>false</completed>
</MRPPurchasingRunLine>
''' % (identifier2, identifier, obj.item.source, obj.quantity,
obj.quantity,
datetime.strftime(obj.enddate, "%Y-%m-%d %H:%M:%S"),
datetime.strftime(obj.startdate, "%Y-%m-%d %H:%M:%S"),
transaction_type, businessPartner))
body.append('''</mRPPurchasingRunLineList>
</MRPPurchasingRun>
</ob:Openbravo>
''')
else:
# Look up the id of the Openbravo user
query = urllib.parse.quote("name='%s'" % openbravo_user)
data = get_data(
"/ws/dal/ADUser?where=%s&includeChildren=false" % query,
openbravo_host, openbravo_user, openbravo_password)
openbravo_user_id = None
for event, elem in iterparse(StringIO(data),
events=('start', 'end')):
if event != 'end' or elem.tag != 'ADUser':
continue
openbravo_user_id = elem.get('id')
if not openbravo_user_id:
raise CommandError("Can't find user id in Openbravo")
identifier = uuid4().hex
url = "/ws/dal/ProcurementRequisition"
body.append(
'''<ProcurementRequisition id="%s">
<organization id="%s" entity-name="Organization"/>
<active>true</active>
<documentNo>frePPLe %s</documentNo>
<description>frePPLe export of %s</description>
<createPO>true</createPO>
<documentStatus>DR</documentStatus>
<userContact id="%s" entity-name="ADUser" identifier="%s"/>
<processNow>true</processNow>
<procurementRequisitionLineList>''' %
(identifier, openbravo_organization_ids[request.database], now,
now, openbravo_user_id, openbravo_user))
count = 0
for obj in cleaned_records:
count = count + 1
businessPartner = ''
if isinstance(obj, PurchaseOrder):
transaction_type = 'PO'
if obj.supplier and obj.supplier.source:
businessPartner = '<businessPartner id="%s"/>' % obj.supplier.source
# TODO: where to store the destination of a purchase order
body.append('''<ProcurementRequisitionLine>
<active>true</active>
<requisition id="%s" entity-name="ProcurementRequisition"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<uOM id="100" entity-name="UOM" identifier="Unit"/>
<requisitionLineStatus>O</requisitionLineStatus>
<needByDate>%s.0Z</needByDate>
<lineNo>%s</lineNo>
</ProcurementRequisitionLine>
''' % (identifier, obj.item.source, obj.quantity,
datetime.strftime(obj.enddate,
"%Y-%m-%d %H:%M:%S"), count))
else:
raise
body.append('''</procurementRequisitionLineList>
</ProcurementRequisition>
</ob:Openbravo>
''')
try:
# Send the data to openbravo
get_data(url,
openbravo_host,
openbravo_user,
openbravo_password,
method="POST",
xmldoc='\n'.join(body))
# Now save the changed status also in our database
for obj in cleaned_records:
obj.save(using=request.database)
return HttpResponse("OK")
except Exception as e:
# Something went wrong in the connection
return HttpResponse(content=str(e), status=500)
# Exported everything successfully
return HttpResponse("OK")
def Upload(request):
try:
# Prepare a message for odoo
boundary = email.generator._make_boundary()
odoo_db = Parameter.getValue("odoo.db", request.database)
odoo_company = Parameter.getValue("odoo.company", request.database)
odoo_user = Parameter.getValue("odoo.user", request.database)
odoo_password = settings.ODOO_PASSWORDS.get(request.database, None)
if not odoo_password:
odoo_password = Parameter.getValue("odoo.password",
request.database)
if not odoo_db or not odoo_company or not odoo_user or not odoo_password:
return HttpResponseServerError(
_("Invalid configuration parameters"))
data_odoo = [
"--%s" % boundary,
'Content-Disposition: form-data; name="webtoken"\r',
"\r",
"%s\r" % jwt.encode(
{
"exp": round(time.time()) + 600,
"user": odoo_user
},
settings.DATABASES[request.database].get(
"SECRET_WEBTOKEN_KEY", settings.SECRET_KEY),
algorithm="HS256",
).decode("ascii"),
"--%s\r" % boundary,
'Content-Disposition: form-data; name="database"',
"",
odoo_db,
"--%s" % boundary,
'Content-Disposition: form-data; name="language"',
"",
Parameter.getValue("odoo.language", request.database, "en_US"),
"--%s" % boundary,
'Content-Disposition: form-data; name="company"',
"",
odoo_company,
"--%s" % boundary,
'Content-Disposition: form-data; name="mode"',
"",
"2", # Marks incremental export
"--%s" % boundary,
'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"',
"Content-Type: application/xml",
"",
'<?xml version="1.0" encoding="UTF-8" ?>',
'<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><operationplans>',
]
# Validate records which exist in the database
data = json.loads(request.body.decode("utf-8"))
data_ok = False
obj = []
for rec in data:
try:
if rec["type"] == "PO":
po = PurchaseOrder.objects.using(
request.database).get(reference=rec["reference"])
if (not po.supplier.source or po.status != "proposed"
or not po.item.source):
continue
data_ok = True
obj.append(po)
data_odoo.append(
'<operationplan ordertype="PO" id="%s" item=%s location=%s supplier=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d" batch=%s/>'
% (
po.reference,
quoteattr(po.item.name),
quoteattr(po.location.name),
quoteattr(po.supplier.name),
po.startdate,
po.enddate,
po.quantity,
quoteattr(po.location.subcategory or ""),
quoteattr(po.item.subcategory or ""),
int(po.criticality),
quoteattr(po.batch or ""),
))
elif rec["type"] == "DO":
do = DistributionOrder.objects.using(
request.database).get(reference=rec["reference"])
if (not do.origin.source or not do.destination.source
or do.status != "proposed" or not do.item.source):
continue
data_ok = True
obj.append(do)
data_odoo.append(
'<operationplan status="%s" reference="%s" ordertype="DO" item=%s origin=%s destination=%s start="%s" end="%s" quantity="%s" origin_id=%s destination_id=%s item_id=%s criticality="%d" batch=%s/>'
% (
do.status,
do.reference,
quoteattr(do.item.name),
quoteattr(do.origin.name),
quoteattr(do.destination.name),
do.startdate,
do.enddate,
do.quantity,
quoteattr(do.origin.subcategory or ""),
quoteattr(do.destination.subcategory or ""),
quoteattr(do.item.subcategory or ""),
int(do.criticality),
quoteattr(do.batch or ""),
))
else:
op = OperationPlan.objects.using(
request.database).get(reference=rec["reference"])
if op.owner:
# Approving a routing step MO (ie odoo work order) translates into
# approving the routing MO (ie odoo manufacturing order)
op = op.owner
if (not op.operation.source or op.status != "proposed"
or not op.operation.item):
continue
data_ok = True
obj.append(op)
if op.operation.category == "subcontractor":
data_odoo.append(
'<operationplan ordertype="PO" id="%s" item=%s location=%s supplier=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d" batch=%s/>'
% (
op.reference,
quoteattr(op.item.name),
quoteattr(op.location.name),
quoteattr(op.operation.subcategory or ""),
op.startdate,
op.enddate,
op.quantity,
quoteattr(op.location.subcategory or ""),
quoteattr(op.item.subcategory or ""),
int(op.criticality),
quoteattr(op.batch or ""),
))
else:
data_odoo.append(
'<operationplan ordertype="MO" id="%s" item=%s location=%s operation=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d" batch=%s/>'
% (
op.reference,
quoteattr(op.operation.item.name),
quoteattr(op.operation.location.name),
quoteattr(op.operation.name),
op.startdate,
op.enddate,
op.quantity,
quoteattr(op.operation.location.subcategory
or ""),
quoteattr(op.operation.item.subcategory or ""),
int(op.criticality),
quoteattr(op.batch or ""),
))
except Exception as e:
logger.error("Exception during odoo export: %s" % e)
if not data_ok:
return HttpResponseServerError(
_("No proposed data records selected"))
# Send the data to Odoo
data_odoo.append("</operationplans></plan>")
data_odoo.append("--%s--" % boundary)
data_odoo.append("")
body = "\n".join(data_odoo).encode("utf-8")
size = len(body)
encoded = base64.encodebytes(
("%s:%s" % (odoo_user, odoo_password)).encode("utf-8"))
logger.debug("Uploading %d bytes of planning results to Odoo" % size)
req = Request(
"%sfrepple/xml/" %
Parameter.getValue("odoo.url", request.database),
data=body,
headers={
"Authorization": "Basic %s" % encoded.decode("ascii")[:-1],
"Content-Type": "multipart/form-data; boundary=%s" % boundary,
"Content-length": size,
},
)
# Read the response
with urlopen(req) as f:
msg = f.read()
logger.debug("Odoo response: %s" % msg.decode("utf-8"))
for i in obj:
i.status = "approved"
i.source = "odoo_1"
i.save(using=request.database)
return HttpResponse("OK")
except HTTPError:
logger.error("Can't connect to the Odoo server")
return HttpResponseServerError("Can't connect to the odoo server")
except Exception as e:
logger.error(e)
return HttpResponseServerError("internal server error")
def Upload(request):
'''
TODO we are doing lots of round trips to the database and openbravo to
read the configuration. There is considerable overhead in this.
'''
# Decode the data received from the client
data = json.loads(request.body.decode('utf-8'))
# Validate records which exist in the database
cleaned_records = []
for rec in data:
try:
if rec['type'] == 'PO':
obj = PurchaseOrder.objects.using(request.database).get(id=rec['id'])
obj.supplier = Supplier.objects.using(request.database).get(name=rec.get('origin') or rec.get('supplier'))
if not obj.supplier.source:
continue
else:
obj = DistributionOrder.objects.using(request.database).get(id=rec['id'])
#obj.destination = Location.objects.using(request.database).get(name=rec['destination'])
#obj.origin = Location.object.using(request.database).get(name=rec['origin'])
if obj.item.name != rec['item']:
obj.item = Item.objects.using(request.database).get(name=rec['item'])
if obj.status == 'proposed' and obj.item.source:
# Copy edited values on the database object. Changes aren't saved yet.
obj.startdate = datetime.strptime(rec['startdate'], "%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'], "%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_records.append(obj)
except:
pass
if not cleaned_records:
return HttpResponse(content=_("No proposed data records selected"), status=500)
# Read the configuration data from the database.
openbravo_user = Parameter.getValue("openbravo.user", request.database)
# Passwords in djangosettings file are preferably used
if settings.OPENBRAVO_PASSWORDS.get(request.database) == '':
openbravo_password = Parameter.getValue("openbravo.password", request.database)
else:
openbravo_password = settings.OPENBRAVO_PASSWORDS.get(request.database)
openbravo_host = Parameter.getValue("openbravo.host", request.database)
openbravo_organization = Parameter.getValue("openbravo.organization", request.database)
exportPurchasingPlan = Parameter.getValue("openbravo.exportPurchasingPlan", request.database, default="false")
# Look up the id of the Openbravo organization id
if request.database not in openbravo_organization_ids:
query = urllib.parse.quote("name='%s'" % openbravo_organization)
data = get_data(
"/openbravo/ws/dal/Organization?where=%s&includeChildren=false" % query,
openbravo_host, openbravo_user, openbravo_password
)
conn = iterparse(StringIO(data), events=('start', 'end'))
for event, elem in conn:
if event == 'end' and elem.tag == 'Organization':
openbravo_organization_ids[request.database] = elem.get('id')
break
if request.database not in openbravo_organization_ids:
return HttpResponse(content="Can't find organization id in Openbravo", status=500)
# Build the data content to send
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
]
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
if exportPurchasingPlan:
identifier = uuid4().hex
url = "/openbravo/ws/dal/MRPPurchasingRun"
body.append('''<MRPPurchasingRun id="%s">
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Incremental export triggered by %s</description>
<timeHorizon>365</timeHorizon>
<timeHorizon>365</timeHorizon>
<safetyLeadTime>0</safetyLeadTime>
<mRPPurchasingRunLineList>''' % (
identifier, openbravo_organization_ids[request.database],
openbravo_organization, now, request.user.username
))
for obj in cleaned_records:
identifier2 = uuid4().hex
businessPartner = ''
if isinstance(obj, PurchaseOrder):
transaction_type = 'PO'
if obj.supplier and obj.supplier.source:
businessPartner = '<businessPartner id="%s"/>' % obj.supplier.source
# TODO: where to store the destination of a purchase order
else:
transaction_type = 'MF' # TODO Is this right?
# TODO: where to store the source and destination of a stock transfer order
# Possible transaction types in Openbravo are:
# - SO (Pending Sales Order)
# - PO (Pending Purchase Order)
# - WR (Pending Work Requirement)
# - SF (Sales Forecast)
# - MF (Material Requirement)
# - UD (User defined)
# - WP (Suggested Work Requirement)
# - MP (Suggested Material Requirement)
# - PP (Suggested Purchase Order)
# - ST (Stock)
# - MS (Minimum Stock): Minimum or security stock
body.append('''<MRPPurchasingRunLine id="%s">
<active>true</active>
<purchasingPlan id="%s" entity-name="MRPPurchasingRun"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<requiredQuantity>%s</requiredQuantity>
<plannedDate>%s.0Z</plannedDate>
<plannedOrderDate>%s.0Z</plannedOrderDate>
<transactionType>%s</transactionType>
%s
<fixed>true</fixed>
<completed>false</completed>
</MRPPurchasingRunLine>
''' % (
identifier2, identifier, obj.item.source, obj.quantity,
obj.quantity, datetime.strftime(obj.enddate, "%Y-%m-%d %H:%M:%S"),
datetime.strftime(obj.startdate, "%Y-%m-%d %H:%M:%S"), transaction_type,
businessPartner
))
body.append('''</mRPPurchasingRunLineList>
</MRPPurchasingRun>
</ob:Openbravo>
''')
else:
raise Exception("Incremental export as a requisition not implemented yet") # TODO
xmldoc = '\n'.join(body).encode(encoding='utf_8')
try:
# Send the data to openbravo
post_data(xmldoc, url, openbravo_host, openbravo_user, openbravo_password)
# Now save the changed status also in our database
for obj in cleaned_records:
obj.save(using=request.database)
return HttpResponse("OK")
except Exception as e:
# Something went wrong in the connection
return HttpResponse(content=str(e), status=500)
def handle(self, **options):
# Pick up the options
if 'verbosity' in options: self.verbosity = int(options['verbosity'] or '1')
else: self.verbosity = 1
if 'user' in options: user = options['user']
else: user = ''
if 'database' in options: self.database = options['database'] or DEFAULT_DB_ALIAS
else: self.database = DEFAULT_DB_ALIAS
if not self.database in settings.DATABASES.keys():
raise CommandError("No database settings known for '%s'" % self.database )
# Pick up configuration parameters
self.openerp_user = Parameter.getValue("openerp.user", self.database)
self.openerp_password = Parameter.getValue("openerp.password", self.database)
self.openerp_db = Parameter.getValue("openerp.db", self.database)
self.openerp_url = Parameter.getValue("openerp.url", self.database)
# Make sure the debug flag is not set!
# When it is set, the django database wrapper collects a list of all SQL
# statements executed and their timings. This consumes plenty of memory
# and cpu time.
tmp_debug = settings.DEBUG
settings.DEBUG = False
now = datetime.now()
ac = transaction.get_autocommit(using=self.database)
transaction.set_autocommit(False, using=self.database)
task = None
try:
# Initialize the task
if 'task' in options and options['task']:
try: task = Task.objects.all().using(self.database).get(pk=options['task'])
except: raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'OpenERP export':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='OpenERP edport', submitted=now, started=now, status='0%', user=user)
task.save(using=self.database)
transaction.commit(using=self.database)
# Log in to the openerp server
sock_common = xmlrpclib.ServerProxy(self.openerp_url + 'xmlrpc/common')
self.uid = sock_common.login(self.openerp_db, self.openerp_user, self.openerp_password)
# Connect to openerp server
self.sock = xmlrpclib.ServerProxy(self.openerp_url + 'xmlrpc/object')
# Create a database connection to the frePPLe database
self.cursor = connections[self.database].cursor()
# Upload all data
self.export_procurement_order()
task.status = '50%'
task.save(using=self.database)
transaction.commit(using=self.database)
self.export_sales_order()
task.status = '100%'
task.save(using=self.database)
transaction.commit(using=self.database)
# Log success
task.status = 'Done'
task.finished = datetime.now()
except Exception as e:
if task:
task.status = 'Failed'
task.message = '%s' % e
task.finished = datetime.now()
raise e
finally:
if task: task.save(using=self.database)
try: transaction.commit(using=self.database)
except: pass
settings.DEBUG = tmp_debug
transaction.set_autocommit(ac, using=self.database)
def Upload(request):
'''
TODO we are doing lots of round trips to the database and openbravo to
read the configuration. There is considerable overhead in this.
'''
# Decode the data received from the client
data = json.loads(request.body.decode('utf-8'))
# Validate records which exist in the database
cleaned_records = []
for rec in data:
try:
if rec['type'] == 'PO':
obj = PurchaseOrder.objects.using(
request.database).get(id=rec['id'])
obj.supplier = Supplier.objects.using(request.database).get(
name=rec.get('origin') or rec.get('supplier'))
if not obj.supplier.source:
continue
else:
obj = DistributionOrder.objects.using(
request.database).get(id=rec['id'])
#obj.destination = Location.objects.using(request.database).get(name=rec['destination'])
#obj.origin = Location.object.using(request.database).get(name=rec['origin'])
if obj.item.name != rec['item']:
obj.item = Item.objects.using(
request.database).get(name=rec['item'])
if obj.status == 'proposed' and obj.item.source:
# Copy edited values on the database object. Changes aren't saved yet.
obj.startdate = datetime.strptime(rec['startdate'],
"%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'],
"%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_records.append(obj)
except:
pass
if not cleaned_records:
return HttpResponse(content=_("No proposed data records selected"),
status=500)
# Read the configuration data from the database.
openbravo_user = Parameter.getValue("openbravo.user", request.database)
# Passwords in djangosettings file are preferably used
if settings.OPENBRAVO_PASSWORDS.get(request.database) == '':
openbravo_password = Parameter.getValue("openbravo.password",
request.database)
else:
openbravo_password = settings.OPENBRAVO_PASSWORDS.get(request.database)
openbravo_host = Parameter.getValue("openbravo.host", request.database)
openbravo_organization = Parameter.getValue("openbravo.organization",
request.database)
exportPurchasingPlan = Parameter.getValue("openbravo.exportPurchasingPlan",
request.database,
default="false")
# Look up the id of the Openbravo organization id
if request.database not in openbravo_organization_ids:
query = urllib.parse.quote("name='%s'" % openbravo_organization)
data = get_data(
"/openbravo/ws/dal/Organization?where=%s&includeChildren=false" %
query, openbravo_host, openbravo_user, openbravo_password)
conn = iterparse(StringIO(data), events=('start', 'end'))
for event, elem in conn:
if event == 'end' and elem.tag == 'Organization':
openbravo_organization_ids[request.database] = elem.get('id')
break
if request.database not in openbravo_organization_ids:
return HttpResponse(
content="Can't find organization id in Openbravo", status=500)
# Build the data content to send
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
]
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
if exportPurchasingPlan:
identifier = uuid4().hex
url = "/openbravo/ws/dal/MRPPurchasingRun"
body.append('''<MRPPurchasingRun id="%s">
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Incremental export triggered by %s</description>
<timeHorizon>365</timeHorizon>
<timeHorizon>365</timeHorizon>
<safetyLeadTime>0</safetyLeadTime>
<mRPPurchasingRunLineList>''' %
(identifier, openbravo_organization_ids[request.database],
openbravo_organization, now, request.user.username))
for obj in cleaned_records:
identifier2 = uuid4().hex
businessPartner = ''
if isinstance(obj, PurchaseOrder):
transaction_type = 'PO'
if obj.supplier and obj.supplier.source:
businessPartner = '<businessPartner id="%s"/>' % obj.supplier.source
# TODO: where to store the destination of a purchase order
else:
transaction_type = 'MF' # TODO Is this right?
# TODO: where to store the source and destination of a stock transfer order
# Possible transaction types in Openbravo are:
# - SO (Pending Sales Order)
# - PO (Pending Purchase Order)
# - WR (Pending Work Requirement)
# - SF (Sales Forecast)
# - MF (Material Requirement)
# - UD (User defined)
# - WP (Suggested Work Requirement)
# - MP (Suggested Material Requirement)
# - PP (Suggested Purchase Order)
# - ST (Stock)
# - MS (Minimum Stock): Minimum or security stock
body.append('''<MRPPurchasingRunLine id="%s">
<active>true</active>
<purchasingPlan id="%s" entity-name="MRPPurchasingRun"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<requiredQuantity>%s</requiredQuantity>
<plannedDate>%s.0Z</plannedDate>
<plannedOrderDate>%s.0Z</plannedOrderDate>
<transactionType>%s</transactionType>
%s
<fixed>true</fixed>
<completed>false</completed>
</MRPPurchasingRunLine>
''' % (identifier2, identifier, obj.item.source, obj.quantity,
obj.quantity, datetime.strftime(obj.enddate,
"%Y-%m-%d %H:%M:%S"),
datetime.strftime(obj.startdate, "%Y-%m-%d %H:%M:%S"),
transaction_type, businessPartner))
body.append('''</mRPPurchasingRunLineList>
</MRPPurchasingRun>
</ob:Openbravo>
''')
else:
raise Exception(
"Incremental export as a requisition not implemented yet") # TODO
xmldoc = '\n'.join(body).encode(encoding='utf_8')
try:
# Send the data to openbravo
post_data(xmldoc, url, openbravo_host, openbravo_user,
openbravo_password)
# Now save the changed status also in our database
for obj in cleaned_records:
obj.save(using=request.database)
return HttpResponse("OK")
except Exception as e:
# Something went wrong in the connection
return HttpResponse(content=str(e), status=500)
def handle(self, **options):
# Pick up the options
if 'verbosity' in options:
self.verbosity = int(options['verbosity'] or '1')
else:
self.verbosity = 1
if 'user' in options:
user = options['user']
else:
user = ''
if 'database' in options:
self.database = options['database'] or DEFAULT_DB_ALIAS
else:
self.database = DEFAULT_DB_ALIAS
if self.database not in settings.DATABASES.keys():
raise CommandError("No database settings known for '%s'" % self.database )
# Pick up configuration parameters
self.openbravo_user = Parameter.getValue("openbravo.user", self.database)
# Passwords in djangosettings file are preferably used
if settings.OPENBRAVO_PASSWORDS.get(db) == '':
self.openbravo_password = Parameter.getValue("openbravo.password", self.database)
else:
self.openbravo_password = settings.OPENBRAVO_PASSWORDS.get(db)
self.openbravo_host = Parameter.getValue("openbravo.host", self.database)
self.openbravo_organization = Parameter.getValue("openbravo.organization", self.database)
if not self.openbravo_user:
raise CommandError("Missing or invalid parameter openbravo_user")
if not self.openbravo_password:
raise CommandError("Missing or invalid parameter openbravo_password")
if not self.openbravo_host:
raise CommandError("Missing or invalid parameter openbravo_host")
if not self.openbravo_organization:
raise CommandError("Missing or invalid parameter openbravo_organization")
# Make sure the debug flag is not set!
# When it is set, the django database wrapper collects a list of all SQL
# statements executed and their timings. This consumes plenty of memory
# and cpu time.
tmp_debug = settings.DEBUG
settings.DEBUG = False
now = datetime.now()
task = None
try:
# Initialize the task
if 'task' in options and options['task']:
try:
task = Task.objects.all().using(self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'Openbravo export':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='Openbravo export', submitted=now, started=now, status='0%', user=user)
task.save(using=self.database)
# Create a database connection to the frePPLe database
cursor = connections[self.database].cursor()
# Look up the id of the Openbravo user
query = urllib.quote("name='%s'" % self.openbravo_user.encode('utf8'))
conn = self.get_data("/openbravo/ws/dal/ADUser?where=%s&includeChildren=false" % query)[0]
self.user_id = None
for event, elem in conn:
if event != 'end' or elem.tag != 'ADUser':
continue
self.user_id = elem.get('id')
if not self.user_id:
raise CommandError("Can't find user id in Openbravo")
# Look up the id of the Openbravo organization id
query = urllib.quote("name='%s'" % self.openbravo_organization.encode('utf8'))
conn = self.get_data("/openbravo/ws/dal/Organization?where=%s&includeChildren=false" % query)[0]
self.organization_id = None
for event, elem in conn:
if event != 'end' or elem.tag != 'Organization':
continue
self.organization_id = elem.get('id')
if not self.organization_id:
raise CommandError("Can't find organization id in Openbravo")
# Upload all data
self.export_procurement_order(cursor)
self.export_work_order(cursor)
self.export_sales_order(cursor)
# Log success
task.status = 'Done'
task.finished = datetime.now()
except Exception as e:
if task:
task.status = 'Failed'
task.message = '%s' % e
task.finished = datetime.now()
raise e
finally:
if task:
task.save(using=self.database)
settings.DEBUG = tmp_debug
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
odoo_user = Parameter.getValue("odoo.user", 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:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("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")
boundary = email.generator._make_boundary()
# Generator function
# We generate output in the multipart/form-data format.
# We send the connection parameters as well as a file with the planning
# results in XML-format.
def publishPlan():
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="webtoken"\r'
yield '\r'
yield '%s\r' % jwt.encode({
'exp': round(time.time()) + 600,
'user': odoo_user,
},
settings.DATABASES[database].get('SECRET_WEBTOKEN_KEY', settings.SECRET_KEY),
algorithm='HS256').decode('ascii')
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="database"\r'
yield '\r'
yield '%s\r' % odoo_db
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="language"\r'
yield '\r'
yield '%s\r' % odoo_language
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="company"\r'
yield '\r'
yield '%s\r' % odoo_company
yield '--%s\r' % boundary
yield 'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"\r'
yield 'Content-Type: application/xml\r'
yield '\r'
yield '<?xml version="1.0" encoding="UTF-8" ?>'
yield '<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
# Export relevant operationplans
yield '<operationplans>'
for i in frepple.operationplans():
b = None
for j in i.flowplans:
if j.quantity > 0:
b = j.flow.buffer
if not b or not b.source or not b.source.startswith('odoo') or i.locked:
continue
yield '<operationplan id="%s" operation=%s start="%s" end="%s" quantity="%s" location=%s item=%s criticality="%d"/>' % (
i.id, quoteattr(i.operation.name),
i.start, i.end, i.quantity,
quoteattr(b.location.subcategory), quoteattr(b.item.subcategory),
int(i.criticality)
)
yield '</operationplans>'
yield '</plan>'
yield '--%s--\r' % boundary
yield '\r'
# Connect to the odoo URL to POST data
try:
body = '\n'.join(publishPlan()).encode('utf-8')
size = len(body)
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))
req = Request(
"%sfrepple/xml/" % odoo_url,
data=body,
headers={
'Authorization': "Basic %s" % encoded.decode('ascii')[:-1],
'Content-Type': 'multipart/form-data; boundary=%s' % boundary,
'Content-length': size
}
)
# Posting the data and displaying the server response
print("Uploading %d bytes of planning results to odoo" % size)
with urlopen(req) as f:
msg = f.read()
print("Odoo response: %s" % msg.decode('utf-8'))
except HTTPError as e:
print("Error connecting to odoo", e.read())
raise e
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 export_procurement_order(self, cursor):
def parse(conn):
# Stores the processplan documents
records = 0
root = None
for event, elem in conn:
if not root:
root = elem
continue
if event != 'end' or elem.tag != 'ManufacturingProcessPlan':
continue
records += 1
processplans[elem.get('id')] = elem
return records
requisition = '''<?xml version="1.0" encoding="UTF-8"?>
<ob:Openbravo xmlns:ob="http://www.openbravo.com">
<ProcurementRequisition id="%s">
<organization id="%s" entity-name="Organization"/>
<active>true</active>
<documentNo>frePPLe %s</documentNo>
<description>frePPLe export of %s</description>
<createPO>false</createPO>
<documentStatus>DR</documentStatus>
<userContact id="%s" entity-name="ADUser" identifier="%s"/>
<processNow>false</processNow>
<procurementRequisitionLineList>'''
requisitionline = '''<ProcurementRequisitionLine>
<active>true</active>
<requisition id="%s" entity-name="ProcurementRequisition"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<uOM id="100" entity-name="UOM" identifier="Unit"/>
<requisitionLineStatus>O</requisitionLineStatus>
<needByDate>%s.0Z</needByDate>
<lineNo>%s</lineNo>
</ProcurementRequisitionLine>'''
try:
# Close old purchase requisitions generated by frePPLe
if self.verbosity > 0:
print("Closing previous purchase requisitions from frePPLe")
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
query = urllib.parse.quote(
"documentStatus='DR' and documentNo like 'frePPLe %'")
data = get_data(
"/ws/dal/ProcurementRequisition?where=%s&includeChildren=false"
% query, self.openbravo_host, self.openbravo_user,
self.openbravo_password)
conn = iterparse(StringIO(data), events=('end', ))
for event, elem in conn:
if event != 'end' or elem.tag != 'ProcurementRequisition':
continue
body.append('<ProcurementRequisition id="%s">' %
elem.get('id'))
body.append('<documentStatus>CL</documentStatus>')
body.append('</ProcurementRequisition>')
body.append('</ob:Openbravo>')
get_data('/ws/dal/ProcurementRequisition',
self.openbravo_host,
self.openbravo_user,
self.openbravo_password,
method='POST',
xmldoc='\n'.join(body))
# Create new requisition
starttime = time()
if self.verbosity > 0:
print("Exporting new purchase requisition...")
count = 0
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
identifier = uuid4().hex
filter_expression_po = Parameter.getValue(
'openbravo.filter_export_purchase_order', self.database, "")
if self.filteredexport and filter_expression_po:
filter_expression_po = ' and (%s) ' % filter_expression_po
else:
filter_expression_po = ""
filter_expression_do = Parameter.getValue(
'openbravo.filter_export_distribution_order', self.database,
"")
if self.filteredexport and filter_expression_do:
filter_expression_do = ' and (%s) ' % filter_expression_do
else:
filter_expression_do = ""
body = [
requisition % (identifier, self.organization_id, now, now,
self.openbravo_user_id, self.openbravo_user)
]
cursor.execute('''
select item.source, location.source, enddate, quantity
FROM operationplan
inner JOIN buffer
ON buffer.item_id = operationplan.item_id
AND buffer.location_id = operationplan.location_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = operationplan.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON operationplan.location_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where operationplan.status = 'proposed' and operationplan.type = 'PO' %s
union all
select item.source, location.source, enddate, quantity
FROM operationplan
inner JOIN buffer
ON buffer.item_id = operationplan.item_id
AND buffer.location_id = operationplan.destination_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = operationplan.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON operationplan.destination_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where operationplan.status = 'proposed' and operationplan.type = 'DO' %s
''' % (filter_expression_po, filter_expression_do))
for i in cursor.fetchall():
body.append(requisitionline %
(identifier, i[0], i[3],
i[2].strftime("%Y-%m-%dT%H:%M:%S"), count))
count += 1
body.append('</procurementRequisitionLineList>')
body.append('</ProcurementRequisition>')
body.append('</ob:Openbravo>')
get_data('/ws/dal/ProcurementRequisition',
self.openbravo_host,
self.openbravo_user,
self.openbravo_password,
method='POST',
xmldoc='\n'.join(body))
if self.verbosity > 0:
print("Created requisition with %d lines in %.2f seconds" %
(count, (time() - starttime)))
# Change the status of the new requisition. Doesn't seem to work...
#body = ['<?xml version="1.0" encoding="UTF-8"?>',
# '<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
# '<ProcurementRequisition id="%s">' % identifier,
# '<documentStatus>CO</documentStatus>',
# '<documentAction>CO</documentAction>',
# '</ProcurementRequisition>',
# '</ob:Openbravo>'
# ]
#get_data(
# '/ws/dal/ProcurementRequisition/',
# self.openbravo_host, self.openbravo_user, self.openbravo_password,
# method='POST', xmldoc='\n'.join(body)
# )
except Exception as e:
raise CommandError("Error generation purchase requisitions: %s" %
e)
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
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")
boundary = email.generator._make_boundary()
# Generator function
# We generate output in the multipart/form-data format.
# We send the connection parameters as well as a file with the planning
# results in XML-format.
# TODO respect the parameters odoo.filter_export_purchase_order, odoo.filter_export_manufacturing_order, odoo.filter_export_distribution_order
# these are python expressions - attack-sensitive evaluation!
def publishPlan(cls):
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="webtoken"\r'
yield '\r'
yield '%s\r' % jwt.encode(
{
'exp': round(time.time()) + 600,
'user': odoo_user,
},
settings.DATABASES[database].get('SECRET_WEBTOKEN_KEY',
settings.SECRET_KEY),
algorithm='HS256').decode('ascii')
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="database"\r'
yield '\r'
yield '%s\r' % odoo_db
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="language"\r'
yield '\r'
yield '%s\r' % odoo_language
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="company"\r'
yield '\r'
yield '%s\r' % odoo_company
yield '--%s\r' % boundary
yield 'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"\r'
yield 'Content-Type: application/xml\r'
yield '\r'
yield '<?xml version="1.0" encoding="UTF-8" ?>'
yield '<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
# Export relevant operationplans
yield '<operationplans>'
for i in frepple.operationplans():
if i.ordertype == 'PO':
if not i.item or not i.item.source or not i.item.source.startswith(
'odoo') or i.locked:
continue
cls.exported.append(i)
yield '<operationplan id="%s" ordertype="PO" item=%s location=%s supplier=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
i.id, quoteattr(i.item.name), quoteattr(
i.location.name), quoteattr(
i.supplier.name), i.start, i.end, i.quantity,
quoteattr(i.operation.location.subcategory),
quoteattr(i.item.subcategory), int(i.criticality))
elif i.ordertype == "MO":
if not i.operation or not i.operation.source or not i.operation.source.startswith(
'odoo') or i.locked:
continue
cls.exported.append(i)
yield '<operationplan id="%s" ordertype="MO" item=%s location=%s operation=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
i.id, quoteattr(i.operation.item.name),
quoteattr(i.operation.location.name),
quoteattr(
i.operation.name), i.start, i.end, i.quantity,
quoteattr(i.operation.location.subcategory),
quoteattr(
i.operation.item.subcategory), int(i.criticality))
yield '</operationplans>'
yield '</plan>'
yield '--%s--\r' % boundary
yield '\r'
# Connect to the odoo URL to POST data
try:
cls.exported = []
body = '\n'.join(publishPlan(cls)).encode('utf-8')
size = len(body)
encoded = base64.encodestring(
('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))
req = Request("%sfrepple/xml/" % odoo_url,
data=body,
headers={
'Authorization':
"Basic %s" % encoded.decode('ascii')[:-1],
'Content-Type':
'multipart/form-data; boundary=%s' % boundary,
'Content-length':
size
})
# Posting the data and displaying the server response
logger.info("Uploading %d bytes of planning results to odoo" %
size)
with urlopen(req) as f:
msg = f.read()
logger.info("Odoo response: %s" % msg.decode('utf-8'))
# Mark the exported operations as approved
for i in cls.exported:
i.status = 'approved'
del cls.exported
except HTTPError as e:
logger.error("Error connecting to odoo %s" % e.read())
def export_work_order(self, cursor):
fltr = Parameter.getValue(
'openbravo.filter_export_manufacturing_order', self.database, "")
if self.filteredexport and fltr:
filter_expression = 'and (%s) ' % fltr
else:
filter_expression = ""
if True: #try:
starttime = time()
if self.verbosity > 0:
print("Exporting work orders...")
cursor.execute('''
select operation.source, operationplan.quantity, startdate, enddate
from operationplan
inner join operation
on operationplan.operation_id = operation.name
and operation.type = 'routing'
and operation.name like 'Process%%'
where operationplan.type = 'MO' %s ''' % filter_expression)
count = 0
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
for i in cursor.fetchall():
body.append('''<ManufacturingWorkRequirement>
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<processPlan id="%s" entity-name="ManufacturingProcessPlan"/>
<quantity>%s</quantity>
<startingDate>%s</startingDate>
<endingDate>%s</endingDate>
<closed>false</closed>
<insertProductsAndorPhases>false</insertProductsAndorPhases>
<processed>false</processed>
<includePhasesWhenInserting>true</includePhasesWhenInserting>
<processQuantity>0</processQuantity>
<createworkrequirement>false</createworkrequirement>
<closedStat>false</closedStat>
</ManufacturingWorkRequirement>
''' % (self.organization_id, self.openbravo_organization, i[0],
i[1], i[2].strftime("%Y-%m-%d %H:%M:%S"),
i[3].strftime("%Y-%m-%d %H:%M:%S")))
count += 1
if self.verbosity > 0 and count % 500 == 1:
print('.', end="")
if self.verbosity > 0:
print('')
body.append('</ob:Openbravo>')
get_data(
'/ws/org.openbravo.warehouse.advancedwarehouseoperations.manufacturing.AddWorkRequirementsWS',
self.openbravo_host,
self.openbravo_user,
self.openbravo_password,
method="PUT",
xmldoc='\n'.join(body),
headers={'DoProcess': 'true'})
if self.verbosity > 0:
print("Updated %d work orders in %.2f seconds" %
(count, (time() - starttime)))
def Upload(request):
try:
# Prepare a message for odoo
boundary = email.generator._make_boundary()
odoo_db = Parameter.getValue("odoo.db", request.database)
odoo_company = Parameter.getValue("odoo.company", request.database)
odoo_user = Parameter.getValue("odoo.user", request.database)
odoo_password = settings.ODOO_PASSWORDS.get(request.database, None)
if not odoo_password:
odoo_password = Parameter.getValue("odoo.password", request.database)
if not odoo_db or not odoo_company or not odoo_user or not odoo_password:
return HttpResponseServerError(_("Invalid configuration parameters"))
data_odoo = [
'--%s' % boundary,
'Content-Disposition: form-data; name="webtoken"\r',
'\r',
'%s\r' % jwt.encode({
'exp': round(time.time()) + 600,
'user': odoo_user,
},
settings.DATABASES[request.database].get('SECRET_WEBTOKEN_KEY', settings.SECRET_KEY),
algorithm='HS256').decode('ascii'),
'--%s\r' % boundary,
'Content-Disposition: form-data; name="database"',
'',
odoo_db,
'--%s' % boundary,
'Content-Disposition: form-data; name="language"',
'',
Parameter.getValue("odoo.language", request.database, 'en_US'),
'--%s' % boundary,
'Content-Disposition: form-data; name="company"',
'',
odoo_company,
'--%s' % boundary,
'Content-Disposition: form-data; name="mode"',
'',
'2', # Marks incremental export
'--%s' % boundary,
'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"',
'Content-Type: application/xml',
'',
'<?xml version="1.0" encoding="UTF-8" ?>',
'<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><operationplans>'
]
# Validate records which exist in the database
data = json.loads(request.body.decode('utf-8'))
data_ok = False
obj = []
for rec in data:
try:
if rec['type'] == 'PO':
po = PurchaseOrder.objects.using(request.database).get(id=rec['id'])
if not po.supplier.source or po.status != 'proposed' or not po.item.source:
continue
data_ok = True
obj.append(po)
data_odoo.append(
'<operationplan ordertype="PO" id="%s" item=%s location=%s supplier=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
po.id, quoteattr(po.item.name), quoteattr(po.location.name),
quoteattr(po.supplier.name), po.startdate, po.enddate, po.quantity,
quoteattr(po.location.subcategory), quoteattr(po.item.subcategory),
int(po.criticality)
))
elif rec['type'] == 'DO':
do = DistributionOrder.objects.using(request.database).get(id=rec['id'])
if not do.origin.source or do.status != 'proposed' or not do.item.source:
continue
data_ok = True
obj.append(do)
data_odoo.append(
'<operationplan ordertype="DO" id="%s" item=%s origin=%s location=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
do.id, quoteattr(do.item.name), quoteattr(do.origin.name),
quoteattr(do.location.name), do.startdate, do.enddate, do.quantity,
quoteattr(do.location.subcategory), quoteattr(do.item.subcategory),
int(do.criticality)
))
else:
op = OperationPlan.objects.using(request.database).get(id=rec['id'])
if not op.operation.source or op.status != 'proposed':
continue
data_ok = True
obj.append(op)
data_odoo.append(
'<operationplan ordertype="MO" id="%s" item=%s location=%s operation=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
op.id, quoteattr(op.operation.item.name),
quoteattr(op.operation.location.name), quoteattr(op.operation.name),
op.startdate, op.enddate, op.quantity,
quoteattr(op.operation.location.subcategory), quoteattr(op.operation.item.subcategory),
int(op.criticality)
))
except:
pass
if not data_ok:
return HttpResponseServerError(_("No proposed data records selected"))
# Send the data to Odoo
data_odoo.append('</operationplans></plan>')
data_odoo.append('--%s--' % boundary)
data_odoo.append('')
body = '\n'.join(data_odoo).encode('utf-8')
size = len(body)
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))
logger.debug("Uploading %d bytes of planning results to Odoo" % size)
req = Request(
"%sfrepple/xml/" % Parameter.getValue("odoo.url", request.database),
data=body,
headers={
'Authorization': "Basic %s" % encoded.decode('ascii')[:-1],
'Content-Type': 'multipart/form-data; boundary=%s' % boundary,
'Content-length': size
}
)
# Read the response
with urlopen(req) as f:
msg = f.read()
logger.debug("Odoo response: %s" % msg.decode('utf-8'))
for i in obj:
i.status = "approved"
i.source = "odoo_1"
i.save(using=request.database)
return HttpResponse("OK")
except HTTPError:
logger.error("Can't connect to the Odoo server")
return HttpResponseServerError("Can't connect to the odoo server")
except Exception as e:
logger.error(e)
return HttpResponseServerError("internal server error")
def export_purchasingplan(self, cursor):
purchaseplan = '''<MRPPurchasingRun id="%s">
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Bulk export</description>
<timeHorizon>365</timeHorizon>
<safetyLeadTime>0</safetyLeadTime>
<mRPPurchasingRunLineList>'''
purchasingplanline = '''<MRPPurchasingRunLine id="%s">
<active>true</active>
<purchasingPlan id="%s" entity-name="MRPPurchasingRun"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<requiredQuantity>%s</requiredQuantity>
<plannedDate>%s.0Z</plannedDate>
<plannedOrderDate>%s.0Z</plannedOrderDate>
<transactionType>%s</transactionType>
<businessPartner id="%s"/>
<fixed>true</fixed>
<completed>false</completed>
</MRPPurchasingRunLine>'''
try:
# Close the old purchasing plan generated by frePPLe
if self.verbosity > 0:
print("Closing previous purchasing plan generated from frePPLe"
) #
query = urllib.parse.quote(
"createdBy='%s' "
# TODO the filter in the next line generates an incorrect query in Openbravo
"and purchasingPlan.description='Bulk export' "
"and salesOrderLine is null and workRequirement is null "
"and requisitionLine is null" % self.openbravo_user_id)
data = get_data("/ws/dal/MRPPurchasingRunLine?where=%s" % query,
self.openbravo_host,
self.openbravo_user,
self.openbravo_password,
method="DELETE")
if self.filteredexport:
filter_expression_po = Parameter.getValue(
'openbravo.filter_export_purchase_order', self.database,
"")
if filter_expression_po:
filter_expression_po = ' and (%s) ' % filter_expression_po
filter_expression_do = Parameter.getValue(
'openbravo.filter_export_distribution_order',
self.database, "")
if filter_expression_do:
filter_expression_do = ' and (%s) ' % filter_expression_do
else:
filter_expression_po = ""
filter_expression_do = ""
# Create new purchase plan
starttime = time()
if self.verbosity > 0:
print("Exporting new purchasing plan...")
count = 0
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
identifier = uuid4().hex
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
purchaseplan % (identifier, self.organization_id,
self.openbravo_organization, now)
]
cursor.execute('''
SELECT item.source, location.source, quantity, startdate, enddate, 'PO', supplier.source
FROM purchase_order
inner JOIN buffer
ON buffer.item_id = purchase_order.item_id
AND buffer.location_id = purchase_order.location_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = purchase_order.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON purchase_order.location_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
inner join supplier
on purchase_order.supplier_id = supplier.name
and supplier.source is not null
and supplier.subcategory = 'openbravo'
where status = 'proposed' %s
''' % (filter_expression_po))
# union all
# select item.source, location.source, quantity, startdate, enddate, 'DO'
# FROM distribution_order
# inner JOIN buffer
# ON buffer.item_id = distribution_order.item_id
# AND buffer.location_id = distribution_order.destination_id
# AND buffer.subcategory = 'openbravo'
# inner join item
# ON item.name = distribution_order.item_id
# and item.source is not null
# and item.subcategory = 'openbravo'
# inner join location
# ON distribution_order.destination_id = location.name
# and location.source is not null
# and location.subcategory = 'openbravo'
# where status = 'proposed' %s
# ''' % (filter_expression_po,filter_expression_do))
for i in cursor.fetchall():
body.append(purchasingplanline %
(uuid4().hex, identifier, i[0], i[2], i[2],
i[3].strftime("%Y-%m-%d %H:%M:%S"),
i[4].strftime("%Y-%m-%d %H:%M:%S"), i[5], i[6]))
count += 1
break
body.append('</mRPPurchasingRunLineList>')
body.append('</MRPPurchasingRun>')
body.append('</ob:Openbravo>')
if count > 0:
get_data('/ws/dal/MRPPurchasingRun',
self.openbravo_host,
self.openbravo_user,
self.openbravo_password,
method="POST",
xmldoc='\n'.join(body))
if self.verbosity > 0:
print("Created purchasing plan with %d lines in %.2f seconds" %
(count, (time() - starttime)))
except Exception as e:
raise CommandError("Error updating purchasing plan: %s" % e)
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
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")
boundary = email.generator._make_boundary()
# Generator function
# We generate output in the multipart/form-data format.
# We send the connection parameters as well as a file with the planning
# results in XML-format.
# TODO respect the parameters odoo.filter_export_purchase_order, odoo.filter_export_manufacturing_order, odoo.filter_export_distribution_order
# these are python expressions - attack-sensitive evaluation!
def publishPlan(cls):
yield "--%s\r" % boundary
yield 'Content-Disposition: form-data; name="webtoken"\r'
yield "\r"
yield "%s\r" % jwt.encode(
{
"exp": round(time.time()) + 600,
"user": odoo_user
},
settings.DATABASES[database].get("SECRET_WEBTOKEN_KEY",
settings.SECRET_KEY),
algorithm="HS256",
).decode("ascii")
yield "--%s\r" % boundary
yield 'Content-Disposition: form-data; name="database"\r'
yield "\r"
yield "%s\r" % odoo_db
yield "--%s\r" % boundary
yield 'Content-Disposition: form-data; name="language"\r'
yield "\r"
yield "%s\r" % odoo_language
yield "--%s\r" % boundary
yield 'Content-Disposition: form-data; name="company"\r'
yield "\r"
yield "%s\r" % odoo_company
yield "--%s\r" % boundary
yield 'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"\r'
yield "Content-Type: application/xml\r"
yield "\r"
yield '<?xml version="1.0" encoding="UTF-8" ?>'
yield '<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
# Export relevant operationplans
yield "<operationplans>"
for i in frepple.operationplans():
if i.ordertype == "PO":
if (i.status not in ("proposed", "approved") or not i.item
or not i.item.source or not i.item.subcategory
or not i.location.subcategory
or not i.item.source.startswith("odoo")):
continue
cls.exported.append(i)
yield '<operationplan reference="%s" ordertype="PO" item=%s location=%s supplier=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d"/>' % (
i.reference,
quoteattr(i.item.name),
quoteattr(i.location.name),
quoteattr(i.supplier.name),
i.start,
i.end,
i.quantity,
quoteattr(i.location.subcategory),
quoteattr(i.item.subcategory),
int(i.criticality),
)
elif i.ordertype == "DO":
if (i.status not in ("proposed", "approved") or not i.item
or not i.item.source or not i.item.subcategory
or not i.operation.origin.location.subcategory
or not i.operation.destination.location.subcategory
or not i.item.source.startswith("odoo")):
continue
cls.exported.append(i)
yield '<operationplan status="%s" reference="%s" ordertype="DO" item=%s origin=%s destination=%s start="%s" end="%s" quantity="%s" origin_id=%s destination_id=%s item_id=%s criticality="%d"/>' % (
i.status,
i.reference,
quoteattr(i.operation.destination.item.name),
quoteattr(i.operation.origin.location.name),
quoteattr(i.operation.destination.location.name),
i.start,
i.end,
i.quantity,
quoteattr(i.operation.origin.location.subcategory),
quoteattr(
i.operation.destination.location.subcategory),
quoteattr(i.operation.destination.item.subcategory),
int(i.criticality),
)
elif i.ordertype == "MO":
if (i.status not in ("proposed", "approved")
or not i.operation or not i.operation.source
or not i.operation.item
or not i.operation.source.startswith("odoo")
or not i.operation.item.subcategory
or not i.operation.location.subcategory):
continue
cls.exported.append(i)
res = set()
try:
for j in i.loadplans:
res.add(j.resource.name)
except Exception:
pass
demand = {}
demand_str = ""
for d in i.pegging_demand:
demand[d.demand] = d.quantity
demand_str += "%s:%s, " % (d.demand, d.quantity)
if demand_str:
demand_str = demand_str[:-2]
yield '<operationplan reference="%s" ordertype="MO" item=%s location=%s operation=%s start="%s" end="%s" quantity="%s" location_id=%s item_id=%s criticality="%d" resource=%s demand=%s/>' % (
i.reference,
quoteattr(i.operation.item.name),
quoteattr(i.operation.location.name),
quoteattr(i.operation.name),
i.start,
i.end,
i.quantity,
quoteattr(i.operation.location.subcategory),
quoteattr(i.operation.item.subcategory),
int(i.criticality),
quoteattr(",".join(res)),
quoteattr(demand_str),
)
yield "</operationplans>"
yield "</plan>"
yield "--%s--\r" % boundary
yield "\r"
# Connect to the odoo URL to POST data
try:
cls.exported = []
body = "\n".join(publishPlan(cls)).encode("utf-8")
size = len(body)
encoded = base64.encodestring(
("%s:%s" % (odoo_user, odoo_password)).encode("utf-8"))
req = Request(
"%sfrepple/xml/" % odoo_url,
data=body,
headers={
"Authorization": "Basic %s" % encoded.decode("ascii")[:-1],
"Content-Type":
"multipart/form-data; boundary=%s" % boundary,
"Content-length": size,
},
)
# Posting the data and displaying the server response
logger.info("Uploading %d bytes of planning results to odoo" %
size)
with urlopen(req) as f:
msg = f.read()
logger.info("Odoo response: %s" % msg.decode("utf-8"))
# Mark the exported operations as approved
for i in cls.exported:
i.status = "approved"
del cls.exported
except HTTPError as e:
logger.error("Error connecting to odoo %s" % e.read())
def export_procurement_order(self, cursor):
def parse(conn):
# Stores the processplan documents
records = 0
root = None
for event, elem in conn:
if not root:
root = elem
continue
if event != 'end' or elem.tag != 'ManufacturingProcessPlan':
continue
records += 1
processplans[elem.get('id')] = elem
return records
requisition = '''<?xml version="1.0" encoding="UTF-8"?>
<ob:Openbravo xmlns:ob="http://www.openbravo.com">
<ProcurementRequisition id="%s">
<organization id="%s" entity-name="Organization"/>
<active>true</active>
<documentNo>frePPLe %s</documentNo>
<description>frePPLe export of %s</description>
<createPO>false</createPO>
<documentStatus>DR</documentStatus>
<userContact id="%s" entity-name="ADUser" identifier="%s"/>
<processNow>false</processNow>
<procurementRequisitionLineList>'''
requisitionline = '''<ProcurementRequisitionLine>
<active>true</active>
<requisition id="%s" entity-name="ProcurementRequisition"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<uOM id="100" entity-name="UOM" identifier="Unit"/>
<requisitionLineStatus>O</requisitionLineStatus>
<needByDate>%s.0Z</needByDate>
<lineNo>%s</lineNo>
</ProcurementRequisitionLine>'''
try:
# Close old purchase requisitions generated by frePPLe
if self.verbosity > 0:
print("Closing previous purchase requisitions from frePPLe")
body = [
'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">'
]
query = urllib.parse.quote("documentStatus='DR' and documentNo like 'frePPLe %'")
data = get_data("/ws/dal/ProcurementRequisition?where=%s&includeChildren=false" % query,
self.openbravo_host, self.openbravo_user, self.openbravo_password
)
conn = iterparse(StringIO(data), events=('end',))
for event, elem in conn:
if event != 'end' or elem.tag != 'ProcurementRequisition':
continue
body.append('<ProcurementRequisition id="%s">' % elem.get('id'))
body.append('<documentStatus>CL</documentStatus>')
body.append('</ProcurementRequisition>')
body.append('</ob:Openbravo>')
get_data(
'/ws/dal/ProcurementRequisition',
self.openbravo_host, self.openbravo_user, self.openbravo_password,
method='POST', xmldoc='\n'.join(body)
)
# Create new requisition
starttime = time()
if self.verbosity > 0:
print("Exporting new purchase requisition...")
count = 0
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
identifier = uuid4().hex
filter_expression_po = Parameter.getValue('openbravo.filter_export_purchase_order', self.database, "")
if self.filteredexport and filter_expression_po:
filter_expression_po = ' and (%s) ' % filter_expression_po
else:
filter_expression_po = ""
filter_expression_do = Parameter.getValue('openbravo.filter_export_distribution_order', self.database, "")
if self.filteredexport and filter_expression_do:
filter_expression_do = ' and (%s) ' %filter_expression_do
else:
filter_expression_do = ""
body = [requisition % (identifier, self.organization_id, now, now, self.openbravo_user_id, self.openbravo_user)]
cursor.execute('''
select item.source, location.source, enddate, quantity
FROM operationplan
inner JOIN buffer
ON buffer.item_id = operationplan.item_id
AND buffer.location_id = operationplan.location_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = operationplan.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON operationplan.location_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where operationplan.status = 'proposed' and operationplan.type = 'PO' %s
union all
select item.source, location.source, enddate, quantity
FROM operationplan
inner JOIN buffer
ON buffer.item_id = operationplan.item_id
AND buffer.location_id = operationplan.destination_id
AND buffer.subcategory = 'openbravo'
inner join item
ON item.name = operationplan.item_id
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON operationplan.destination_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where operationplan.status = 'proposed' and operationplan.type = 'DO' %s
''' % (filter_expression_po,filter_expression_do))
for i in cursor.fetchall():
body.append(requisitionline % (identifier, i[0], i[3], i[2].strftime("%Y-%m-%dT%H:%M:%S"), count))
count += 1
body.append('</procurementRequisitionLineList>')
body.append('</ProcurementRequisition>')
body.append('</ob:Openbravo>')
get_data(
'/ws/dal/ProcurementRequisition',
self.openbravo_host, self.openbravo_user, self.openbravo_password,
method='POST', xmldoc='\n'.join(body)
)
if self.verbosity > 0:
print("Created requisition with %d lines in %.2f seconds" % (count, (time() - starttime)))
# Change the status of the new requisition. Doesn't seem to work...
#body = ['<?xml version="1.0" encoding="UTF-8"?>',
# '<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
# '<ProcurementRequisition id="%s">' % identifier,
# '<documentStatus>CO</documentStatus>',
# '<documentAction>CO</documentAction>',
# '</ProcurementRequisition>',
# '</ob:Openbravo>'
# ]
#get_data(
# '/ws/dal/ProcurementRequisition/',
# self.openbravo_host, self.openbravo_user, self.openbravo_password,
# method='POST', xmldoc='\n'.join(body)
# )
except Exception as e:
raise CommandError("Error generation purchase requisitions: %s" % e)
def Upload(request):
'''
TODO we are doing lots of round trips to the database and openbravo to
read the configuration. There is considerable overhead in this.
'''
# Decode the data received from the client
data = json.loads(request.body.decode('utf-8'))
# Validate records which exist in the database
cleaned_records = []
cleaned_MO_records = []
for rec in data:
try:
if rec['type'] == 'PO':
# Purchase orders
obj = PurchaseOrder.objects.using(request.database).get(id=rec['id'])
obj.supplier = Supplier.objects.using(request.database).get(name=rec.get('origin') or rec.get('supplier'))
if obj.item.name != rec['item']:
obj.item = Item.objects.using(request.database).get(name=rec['item'])
if not obj.supplier.source or not obj.item.source or obj.status != 'proposed':
continue
obj.startdate = datetime.strptime(rec['startdate'], "%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'], "%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_records.append(obj)
elif rec['type'] == 'OP':
# Manufacturing orders
obj = OperationPlan.objects.using(request.database).get(id=rec['id'])
if obj.operation.name != rec['operation']:
obj.operation = Operation.objects.using(request.database).get(name=rec['operation'])
if not obj.operation.source or obj.status != 'proposed':
continue
obj.startdate = datetime.strptime(rec['startdate'], "%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'], "%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_MO_records.append(obj)
elif rec['type'] == 'DO':
# Distribution orders
obj = DistributionOrder.objects.using(request.database).get(id=rec['id'])
#obj.destination = Location.objects.using(request.database).get(name=rec['destination'])
#obj.origin = Location.object.using(request.database).get(name=rec['origin'])
if obj.item.name != rec['item']:
obj.item = Item.objects.using(request.database).get(name=rec['item'])
if not obj.item.source or obj.status != 'proposed':
continue
obj.startdate = datetime.strptime(rec['startdate'], "%Y-%m-%d %H:%M:%S")
obj.enddate = datetime.strptime(rec['enddate'], "%Y-%m-%d %H:%M:%S")
obj.quantity = abs(float(rec['quantity']))
obj.status = 'approved'
cleaned_records.append(obj)
else:
raise Exception("Unknown transaction type")
except:
pass
if not cleaned_records and not cleaned_MO_records:
return HttpResponse(content=_("No proposed data records selected"), status=500)
# Read the configuration data from the database.
openbravo_user = Parameter.getValue("openbravo.user", request.database)
# Passwords in djangosettings file are preferably used
openbravo_password = settings.OPENBRAVO_PASSWORDS.get(request.database, None)
if not openbravo_password:
openbravo_password = Parameter.getValue("openbravo.password", request.database)
openbravo_host = Parameter.getValue("openbravo.host", request.database)
openbravo_organization = Parameter.getValue("openbravo.organization", request.database)
exportPurchasingPlan = Parameter.getValue("openbravo.exportPurchasingPlan", request.database, default="false")
# Look up the id of the Openbravo organization id
if request.database not in openbravo_organization_ids:
query = urllib.parse.quote("name='%s'" % openbravo_organization)
data = get_data(
"/openbravo/ws/dal/Organization?where=%s&includeChildren=false" % query,
openbravo_host, openbravo_user, openbravo_password
)
conn = iterparse(StringIO(data), events=('start', 'end'))
for event, elem in conn:
if event == 'end' and elem.tag == 'Organization':
openbravo_organization_ids[request.database] = elem.get('id')
break
if request.database not in openbravo_organization_ids:
return HttpResponse(content="Can't find organization id in Openbravo", status=500)
now = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
# Export manufacturing orders
# This export requires the advanced warehousing functionality from openbravo.
# Details on this web service can be found on:
# http://wiki.openbravo.com/wiki/Modules:Advanced_Warehouse_Operations
if cleaned_MO_records:
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">',
]
for obj in cleaned_MO_records:
body.append('''<ManufacturingWorkRequirement>
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<processPlan id="%s" entity-name="ManufacturingProcessPlan"/>
<quantity>%s</quantity>
<startingDate>%s</startingDate>
<endingDate>%s</endingDate>
<closed>false</closed>
<insertProductsAndorPhases>false</insertProductsAndorPhases>
<processed>false</processed>
<includePhasesWhenInserting>true</includePhasesWhenInserting>
<processQuantity>0</processQuantity>
<processUnit xsi:nil="true"/>
<conversionRate xsi:nil="true"/>
<createworkrequirement>false</createworkrequirement>
<closedStat>false</closedStat>
</ManufacturingWorkRequirement>''' % (
openbravo_organization_ids[request.database],
openbravo_organization, obj.operation.source,
obj.quantity, datetime.strftime(obj.startdate, "%Y-%m-%d %H:%M:%S"),
datetime.strftime(obj.enddate, "%Y-%m-%d %H:%M:%S")
))
body.append("</ob:Openbravo>")
try:
# Send the data to openbravo
get_data(
"/ws/org.openbravo.warehouse.advancedwarehouseoperations.manufacturing.AddWorkRequirementsWS",
openbravo_host, openbravo_user, openbravo_password,
method="PUT",
xmldoc='\n'.join(body),
headers={'DoProcess': 'true'}
)
# Now save the changed status also in our database
for obj in cleaned_MO_records:
obj.save(using=request.database)
except Exception as e:
# Something went wrong in the connection
return HttpResponse(content=str(e), status=500)
# Build the distribution and purchase orders
if cleaned_records:
body = [
#'<?xml version="1.0" encoding="UTF-8"?>',
'<ob:Openbravo xmlns:ob="http://www.openbravo.com">',
]
if exportPurchasingPlan:
identifier = uuid4().hex
url = "/ws/dal/MRPPurchasingRun"
body.append('''<MRPPurchasingRun id="%s">
<organization id="%s" entity-name="Organization" identifier="%s"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Incremental export triggered by %s</description>
<timeHorizon>365</timeHorizon>
<safetyLeadTime>0</safetyLeadTime>
<mRPPurchasingRunLineList>''' % (
identifier, openbravo_organization_ids[request.database],
openbravo_organization, now, request.user.username
))
for obj in cleaned_records:
identifier2 = uuid4().hex
businessPartner = ''
if isinstance(obj, PurchaseOrder):
transaction_type = 'PO'
if obj.supplier and obj.supplier.source:
businessPartner = '<businessPartner id="%s"/>' % obj.supplier.source
# TODO: where to store the destination of a purchase order
else:
transaction_type = 'MF' # TODO Is this right?
# TODO: where to store the source and destination of a stock transfer order
# Possible transaction types in Openbravo are:
# - SO (Pending Sales Order)
# - PO (Pending Purchase Order)
# - WR (Pending Work Requirement)
# - SF (Sales Forecast)
# - MF (Material Requirement)
# - UD (User defined)
# - WP (Suggested Work Requirement)
# - MP (Suggested Material Requirement)
# - PP (Suggested Purchase Order)
# - ST (Stock)
# - MS (Minimum Stock): Minimum or security stock
body.append('''<MRPPurchasingRunLine id="%s">
<active>true</active>
<purchasingPlan id="%s" entity-name="MRPPurchasingRun"/>
<product id="%s" entity-name="Product"/>
<quantity>%s</quantity>
<requiredQuantity>%s</requiredQuantity>
<plannedDate>%s.0Z</plannedDate>
<plannedOrderDate>%s.0Z</plannedOrderDate>
<transactionType>%s</transactionType>
%s
<fixed>true</fixed>
<completed>false</completed>
</MRPPurchasingRunLine>
''' % (
identifier2, identifier, obj.item.source, obj.quantity,
obj.quantity, datetime.strftime(obj.enddate, "%Y-%m-%d %H:%M:%S"),
datetime.strftime(obj.startdate, "%Y-%m-%d %H:%M:%S"), transaction_type,
businessPartner
))
body.append('''</mRPPurchasingRunLineList>
</MRPPurchasingRun>
</ob:Openbravo>
''')
else:
raise Exception("Incremental export as a requisition not implemented yet") # TODO
try:
# Send the data to openbravo
get_data(
url, openbravo_host, openbravo_user, openbravo_password,
method="POST", xmldoc='\n'.join(body)
)
# Now save the changed status also in our database
for obj in cleaned_records:
obj.save(using=request.database)
return HttpResponse("OK")
except Exception as e:
# Something went wrong in the connection
return HttpResponse(content=str(e), status=500)
# Exported everything successfully
return HttpResponse("OK")
def Upload(request):
try:
# Prepare a message for odoo
boundary = email.generator._make_boundary()
odoo_db = Parameter.getValue("odoo.db", request.database)
odoo_company = Parameter.getValue("odoo.company", request.database)
odoo_user = Parameter.getValue("odoo.user", request.database)
odoo_password = settings.ODOO_PASSWORDS.get(request.database, None)
if not odoo_password:
odoo_password = Parameter.getValue("odoo.password", request.database)
if not odoo_db or not odoo_company or not odoo_user or not odoo_password:
return HttpResponseServerError(_("Invalid configuration parameters"))
data_odoo = [
'--%s' % boundary,
'Content-Disposition: form-data; name="webtoken"\r',
'\r',
'%s\r' % jwt.encode({
'exp': round(time.time()) + 600,
'user': odoo_user,
},
settings.DATABASES[request.database].get('SECRET_WEBTOKEN_KEY', settings.SECRET_KEY),
algorithm='HS256').decode('ascii'),
'--%s\r' % boundary,
'Content-Disposition: form-data; name="database"',
'',
odoo_db,
'--%s' % boundary,
'Content-Disposition: form-data; name="language"',
'',
Parameter.getValue("odoo.language", request.database, 'en_US'),
'--%s' % boundary,
'Content-Disposition: form-data; name="company"',
'',
odoo_company,
'--%s' % boundary,
'Content-Disposition: form-data; name="mode"',
'',
'2',
'--%s' % boundary,
'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"',
'Content-Type: application/xml',
'',
'<?xml version="1.0" encoding="UTF-8" ?>',
'<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><operationplans>'
]
# Validate records which exist in the database
data = json.loads(request.body.decode('utf-8'))
data_ok = False
obj = []
for rec in data:
try:
if rec['type'] == 'PO':
po = PurchaseOrder.objects.using(request.database).get(id=rec['id'])
if not po.supplier.source or po.status != 'proposed' or not po.item.source:
continue
data_ok = True
obj.append(po)
data_odoo.append(
'<operationplan ordertype="PO" id="%s" operation=%s start="%s" end="%s" quantity="%s" location=%s item=%s criticality="%d"/>' % (
po.id, quoteattr("Purchase %s @ %s" % (po.item.name, po.location.name)),
po.startdate, po.enddate, po.quantity,
quoteattr(po.location.subcategory), quoteattr(po.item.subcategory),
int(po.criticality)
))
elif rec['type'] == 'DO':
do = DistributionOrder.objects.using(request.database).get(id=rec['id'])
if not do.origin.source or do.status != 'proposed' or not do.item.source:
continue
data_ok = True
obj.append(do)
data_odoo.append(
'<operationplan ordertype="DO" id="%s" operation=%s start="%s" end="%s" quantity="%s" location=%s item=%s criticality="%d"/>' % (
do.id, quoteattr("Ship %s from %s to %s" % (do.item.name, do.origin.name, do.location.name)),
do.startdate, do.enddate, do.quantity,
quoteattr(do.location.subcategory), quoteattr(do.item.subcategory),
int(do.criticality)
))
else:
op = OperationPlan.objects.using(request.database).get(id=rec['id'])
try:
buf = op.operation.operationmaterials.all().using(request.database).filter(quantity__gt=0)[0].buffer
except:
buf = None
if not op.operation.source or op.status != 'proposed' or not buf or not buf.item.source:
continue
data_ok = True
obj.append(op)
data_odoo.append(
'<operationplan id="%s" operation=%s start="%s" end="%s" quantity="%s" location=%s item=%s criticality="%d"/>' % (
op.id, quoteattr(op.operation.name),
op.startdate, op.enddate, op.quantity,
quoteattr(buf.location.subcategory), quoteattr(buf.subcategory),
int(op.criticality)
))
except:
pass
if not data_ok:
return HttpResponseServerError(_("No proposed data records selected"))
# Send the data to Odoo
data_odoo.append('</operationplans></plan>')
data_odoo.append('--%s--' % boundary)
data_odoo.append('')
body = '\n'.join(data_odoo).encode('utf-8')
size = len(body)
encoded = base64.encodestring(('%s:%s' % (odoo_user, odoo_password)).encode('utf-8'))
logger.debug("Uploading %d bytes of planning results to Odoo" % size)
req = Request(
"%sfrepple/xml/" % Parameter.getValue("odoo.url", request.database),
data=body,
headers={
'Authorization': "Basic %s" % encoded.decode('ascii')[:-1],
'Content-Type': 'multipart/form-data; boundary=%s' % boundary,
'Content-length': size
}
)
# Read the response
with urlopen(req) as f:
msg = f.read()
logger.debug("Odoo response: %s" % msg.decode('utf-8'))
for i in obj:
i.status = "approved"
i.save(using=request.database)
return HttpResponse("OK")
except HTTPError:
logger.error("Can't connect to the Odoo server")
return HttpResponseServerError("Can't connect to the odoo server")
except Exception as e:
logger.error(e)
return HttpResponseServerError("internal server error")
def odoo_write(db=DEFAULT_DB_ALIAS):
'''
Uploads operationplans to odoo.
- Sends all operationplans, meeting the criteria:
a) locked = False
The operationplans with locked equal to true are input to the plan,
and not output.
b) operationplan produces into a buffer whose source field is 'odoo'.
Only those results are of interest to odoo.
- We upload the following info in XML form:
- id: frePPLe generated unique identifier
- operation
- start
- end
- quantity
- location: This is the odoo id of the location, as stored in
buffer.location.subcategory.
- item: This is the odoo id of the produced item and its uom_id, as
stored in buffer.item.subcategory.
- criticality: 0 indicates a critical operationplan, 999 indicates a
redundant operationplan.
- The XML file uploaded is not exactly the standard XML of frePPLe, but a
slight variation that fits odoo better.
- This code doesn't interprete any of the results. An odoo addon module
will need to read the content, and take appropriate actions in odoo:
such as creating purchase orders, manufacturing orders, work orders,
project tasks, etc...
'''
odoo_user = Parameter.getValue("odoo.user", db)
odoo_password = Parameter.getValue("odoo.password", db)
odoo_db = Parameter.getValue("odoo.db", db)
odoo_url = Parameter.getValue("odoo.url", db)
odoo_company = Parameter.getValue("odoo.company", db)
ok = True
if not odoo_user:
print("Missing or invalid parameter odoo.user")
ok = False
if not odoo_password:
print("Missing or invalid parameter odoo.password")
ok = False
if not odoo_db:
print("Missing or invalid parameter odoo.db")
ok = False
if not odoo_url:
print("Missing or invalid parameter odoo.url")
ok = False
if not odoo_company:
print("Missing or invalid parameter odoo.company")
ok = False
odoo_language = Parameter.getValue("odoo.language", db, 'en_US')
if not ok:
raise Exception("Odoo connector not configured correctly")
boundary = email.generator._make_boundary()
# Generator function
# We generate output in the multipart/form-data format.
# We send the connection parameters as well as a file with the planning
# results in XML-format.
def publishPlan():
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="database"\r'
yield '\r'
yield '%s\r' % odoo_db
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="language"\r'
yield '\r'
yield '%s\r' % odoo_language
yield '--%s\r' % boundary
yield 'Content-Disposition: form-data; name="company"\r'
yield '\r'
yield '%s\r' % odoo_company
yield '--%s\r' % boundary
yield 'Content-Disposition: file; name="frePPLe plan"; filename="frepple_plan.xml"\r'
yield 'Content-Type: application/xml\r'
yield '\r'
yield '<?xml version="1.0" encoding="UTF-8" ?>'
yield '<plan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
# Export relevant operationplans
yield '<operationplans>'
for i in frepple.operationplans():
b = None
for j in i.flowplans:
if j.quantity > 0:
b = j.flow.buffer
if not b or b.source != 'odoo' or i.locked:
continue
yield '<operationplan id="%s" operation=%s start="%s" end="%s" quantity="%s" location=%s item=%s criticality="%d"/>' % (
i.id, quoteattr(i.operation.name),
i.start, i.end, i.quantity,
quoteattr(b.location.subcategory), quoteattr(b.item.subcategory),
int(i.criticality)
)
yield '</operationplans>'
yield '</plan>'
yield '--%s--\r' % boundary
yield '\r'
# Connect to the odoo URL to POST data
try:
req = Request("%s/frepple/xml/" % odoo_url.encode('ascii'))
body = '\n'.join(publishPlan()).encode('utf-8')
size = len(body)
encoded = base64.encodestring('%s:%s' % (odoo_user, odoo_password)).replace('\n', '')
req.add_header("Authorization", "Basic %s" % encoded)
req.add_header("Content-Type", 'multipart/form-data; boundary=%s' % boundary)
req.add_header('Content-length', size)
req.add_data(body)
# Posting the data
print("Uploading %d bytes of planning results to odoo" % size)
req.get_data()
# Display the server response, which can contain error messages
print("Odoo response:")
for i in urlopen(req):
print(i, end="")
print("")
except HTTPError as e:
print("Error connecting to odoo", e.read())
raise e
def handle(self, **options):
# Pick up the options
if 'verbosity' in options:
self.verbosity = int(options['verbosity'] or '1')
else:
self.verbosity = 1
if 'user' in options:
user = options['user']
else:
user = ''
if 'database' in options:
self.database = options['database'] or DEFAULT_DB_ALIAS
else:
self.database = DEFAULT_DB_ALIAS
if self.database not in settings.DATABASES.keys():
raise CommandError("No database settings known for '%s'" % self.database )
# Pick up configuration parameters
self.openbravo_user = Parameter.getValue("openbravo.user", self.database)
# Passwords in djangosettings file are preferably used
self.openbravo_password = settings.OPENBRAVO_PASSWORDS.get(self.database)
if not self.openbravo_password:
self.openbravo_password = Parameter.getValue("openbravo.password", self.database)
self.openbravo_host = Parameter.getValue("openbravo.host", self.database)
self.openbravo_organization = Parameter.getValue("openbravo.organization", self.database)
if not self.openbravo_user:
raise CommandError("Missing or invalid parameter openbravo_user")
if not self.openbravo_password:
raise CommandError("Missing or invalid parameter openbravo_password")
if not self.openbravo_host:
raise CommandError("Missing or invalid parameter openbravo_host")
if not self.openbravo_organization:
raise CommandError("Missing or invalid parameter openbravo_organization")
# Make sure the debug flag is not set!
# When it is set, the django database wrapper collects a list of all SQL
# statements executed and their timings. This consumes plenty of memory
# and cpu time.
tmp_debug = settings.DEBUG
settings.DEBUG = False
now = datetime.now()
task = None
try:
# Initialize the task
if 'task' in options and options['task']:
try:
task = Task.objects.all().using(self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'Openbravo export':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='Openbravo export', submitted=now, started=now, status='0%', user=user)
task.save(using=self.database)
# Create a database connection to the frePPLe database
cursor = connections[self.database].cursor()
# Look up the id of the Openbravo user
query = urllib.parse.quote("name='%s'" % self.openbravo_user)
print ("/openbravo/ws/dal/ADUser?where=%s&includeChildren=false" % query)
data = get_data(
"/openbravo/ws/dal/ADUser?where=%s&includeChildren=false" % query,
self.openbravo_host,
self.openbravo_user,
self.openbravo_password
)
self.openbravo_user_id = None
for event, elem in iterparse(StringIO(data), events=('start', 'end')):
if event != 'end' or elem.tag != 'ADUser':
continue
self.openbravo_user_id = elem.get('id')
if not self.openbravo_user_id:
raise CommandError("Can't find user id in Openbravo")
# Look up the id of the Openbravo organization id
query = urllib.parse.quote("name='%s'" % self.openbravo_organization)
data = get_data(
"/openbravo/ws/dal/Organization?where=%s&includeChildren=false" % query,
self.openbravo_host,
self.openbravo_user,
self.openbravo_password
)
self.organization_id = None
for event, elem in iterparse(StringIO(data), events=('start', 'end')):
if event != 'end' or elem.tag != 'Organization':
continue
self.organization_id = elem.get('id')
if not self.organization_id:
raise CommandError("Can't find organization id in Openbravo")
# Upload all data
# We have two modes of bringing the results to openbravo:
# - generate purchase requisitions and manufacturing work orders
# - generate purchasing plans (which is the object where
# openbravo's MRP stores its results as well)
# The first one is the recommended approach
exportPurchasingPlan = Parameter.getValue("openbravo.exportPurchasingPlan", self.database, default="false")
if exportPurchasingPlan == "true":
self.export_purchasingplan(cursor)
else:
self.export_procurement_order(cursor)
self.export_work_order(cursor)
self.export_sales_order(cursor)
# Log success
task.status = 'Done'
task.finished = datetime.now()
except Exception as e:
if task:
task.status = 'Failed'
task.message = '%s' % e
task.finished = datetime.now()
raise e
finally:
if task:
task.save(using=self.database)
settings.DEBUG = tmp_debug
