def export_sales_order(self, cursor):
try:
starttime = time()
if self.verbosity > 0:
print("Exporting expected delivery date of sales orders...")
cursor.execute('''select demand.source, max(plandate)
from demand
left outer join out_demand
on demand.name = out_demand.demand
where demand.subcategory = 'openbravo'
and status = 'open'
group by source
''')
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>')
post_data(
'\n'.join(body),
'/openbravo/ws/dal/OrderLine',
self.openbravo_host, self.openbravo_user, self.openbravo_password
)
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_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 export_work_order(self, cursor):
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%'
''')
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 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"/>
<active>true</active>
<name>FREPPLE %s</name>
<description>Bulk export</description>
<timeHorizon>365</timeHorizon>
<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
)
# 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, sum(out_operationplan.quantity)
FROM out_operationplan
inner join out_flowplan
ON operationplan_id = out_operationplan.id
AND out_flowplan.quantity > 0
inner JOIN buffer
ON buffer.name = out_flowplan.thebuffer
AND buffer.subcategory = 'openbravo'
inner join item
ON buffer.item_id = item.name
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON buffer.location_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where out_operationplan.operation like 'Purchase %'
and out_operationplan.locked = 'f'
group by location.source, item.source, enddate
''')
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 export_procurement_order(self, cursor):
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 %'")
conn = self.get_data("/openbravo/ws/dal/ProcurementRequisition?where=%s&includeChildren=false" % query)[0]
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>')
post_data(
'\n'.join(body),
'/openbravo/ws/dal/ProcurementRequisition',
self.openbravo_host, self.openbravo_user, self.openbravo_password
)
# 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
body = [requisition % (identifier, self.organization_id, now, now, self.openbravo_user_id, self.openbravo_user)]
cursor.execute('''select item.source, location.source, enddate, sum(out_operationplan.quantity)
FROM out_operationplan
inner join out_flowplan
ON operationplan_id = out_operationplan.id
AND out_flowplan.quantity > 0
inner JOIN buffer
ON buffer.name = out_flowplan.thebuffer
AND buffer.subcategory = 'openbravo'
inner join item
ON buffer.item_id = item.name
and item.source is not null
and item.subcategory = 'openbravo'
inner join location
ON buffer.location_id = location.name
and location.source is not null
and location.subcategory = 'openbravo'
where out_operationplan.operation like 'Purchase %'
and out_operationplan.locked = 'f'
group by location.source, item.source, enddate
''')
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>')
post_data(
'\n'.join(body),
'/openbravo/ws/dal/ProcurementRequisition',
self.openbravo_host, self.openbravo_user, self.openbravo_password
)
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>'
# ]
#post_data(
# '\n'.join(body),
# '/openbravo/ws/dal/ProcurementRequisition/',
# self.openbravo_host, self.openbravo_user, self.openbravo_password
# )
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 = []
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)