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 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 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):
'''
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)
self.filteredexport = 'filter' in options
# 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, None)
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("/ws/dal/ADUser?where=%s&includeChildren=false" % query)
data = get_data(
"/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(
"/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
self.exportData(task, 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_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 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 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 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
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):
'''
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 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 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 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)
