OpenBravo POS into ADempiere

[red1]

ANNOUNCEMENT - THE OPENBRAVOPOS GUIDE HAS BEEN REMOVED FROM SF/ADEMPIERE. IT IS NOW IN OUR UNDERGROUND BUNKER HERE.

(For FAQs, if you have any questions on this sub-project, do refer to another thread here. Any questions asked here will be rephrased there. Thank you for your support.)

This is referring to another thread discussing about which POS, and since i have done the wonderful framework for testing JavaPOS based on UISpec4J and Fitnesse, i am going to take a look at how to tackle the big task of integrating OpenBravo POS (formerly TinaPOS) into iDempiere/361.

I have begun reading the online literature such as http://wiki.openbravo.com/wiki/Openbrav ... ntegration and downloaded the OB POS (v2.3). I have also downloaded the 3E webservice branch in our SVN started by some friends in Poland some years back and bridged substantially by Carlos to follow the OB POS integration plan. It is being referred to by the CookBook in a tutorial on how to use web services but not to OB POS.

I am breaking the task into two main areas: 1. Setting up the OB POS and reviewing with the QA framework tools i been using, 2. Continuing the 3E webservices interface for the POS and exploring a new QA tool for testing the service. (post-note: It seems that later below i have set to use the easier and asynchronous ActiveMQ approach)

There can also be an alternative strategy to integration. Which is to be more conservative by using 2 way replication in batch mode and using the Import loaders. I am also thinking of making variations of our JavaPOS such as switching it to Standard Sales Order in its DocType so that its lighter and faster rather than process all 4 documents for each transaction. It can then export its SOs to the central server for import which resolves all the 4 doctypes and do inventory and accts posting. The center then distribute back fresh inventory and pricing figures. This can be done overnight and free the branch clients from any outage for been hooked up online. There is also a good suggestion from Dellph who use POS alot in Cebu and Mindanao to be free from mouse and touchscreen device due to the higher maintenance cost of hardware. So now, it depends on which itch is more fun to scratch. WDYT?

[red1]

The basic work of integration seems laid out already by Carlos Ruiz in 3E Webservices branch. He has implemented some basic classes as explained in OpenBravo site above.

I have downloaded latest OB POS (v2.3) and able to run it very easily from its start.sh. In the sourcecode this calls com.openbravo.pos.forms.StartPOS. It has a main method and can be run from Eclipse. Thus we can start with a UISpec4J handshake.

I have committed the code in a separate branch in SVN/branches/openbravoPOS for the testing work. Some code review is also done. Starting with translating the Spanish terminology within.

[red1]

In playing around with it, i capture some screenshots to give an idea how OB POS behave. It is not a long learning curve (the = sign means Payment, in case you wonder how to pay the order). I run it with default Derby database and it does persist as the previous order remains when i resumed the POS after shutting down.
Here is the POS order been made out. I defined the two products from scratch first in the Std Product Category.

SalesOrder.png (43.48 KiB) Viewed 80146 times

You can pay in multi-tender mode such as Cash, Check, Card and others.

Paying.png (111.35 KiB) Viewed 80146 times

During payment, you can split the bill to different invoice or patrons.

Splitpayment.png (34.66 KiB) Viewed 80146 times

When recalling the payment, you can view the receipt and think of a refund or part or all of it. The example below I tested with the multi mode of tender.

Receipt.png (44.34 KiB) Viewed 80146 times

The only poor thing seem so far is the Tickets history which does not give much clue of which order we are looking at (as compared with our JavaPOS which is more useful for restaurant pending orders). It is not difficult to eventually modify it.

Tickets.png (14.68 KiB) Viewed 80146 times

So in one sentence, OB POS is very powerful for integration into iDempiere. Further more it is mostly integrated to OpenBravo which is a descendant from Compiere as we are too.

[red1]

To enable the buttons to appear in the Maintenance menu which handle synchronisation with the backend ERP, we have to modify the MenuRoot properties in Resources. Just under the line //Deprecated options were 3 more lines commented out with //. Just removed those 3 markers before the setting lines and this menu group shall appear.

MenuRoot.png (142.68 KiB) Viewed 80080 times

POSSynch.png (79.36 KiB) Viewed 80080 times

Now what is left is to prepare the 3E Webservices module that has CarlosRuiz startup for OB POS integration. That requires further debugging and completion of the module. Will update this in a few days.

[red1]

Here is the proof of concept using ActiveMQ instead to synch OpenBravo POS Orders to XML message for ADempiere backend to process as an Import Order.

First some order were made and the Order Synchronization button was pressed. For a single order we have acknowledgement that it is been processed into the message queue.

OBPOS1.png (14.34 KiB) Viewed 80041 times

OBPOS2.png (15.18 KiB) Viewed 80041 times

Here one order is indicated in the Information panel. Testing with more orders at one go gave a confirmation that they were processed and the qty count was 2 when 2 orders were made before synching.

When the button is pressed again, it shows that the POS Orders have been processed and no longer outstanding for synchronizing.

OBPOS3.png (14.08 KiB) Viewed 80041 times

Below is the sample XML message received from an ActiveMQ receiver.

Code: Select all

<?xml version="1.0" ?><entityDetail><type>I_Order</type><detail><DocTypeName>POS Order</DocTypeName><DocumentNo>7</DocumentNo><DateOrdered>Tue Aug 30 05:47:06 MYT 2011</DateOrdered></detail></entityDetail>


The reason why i am choosing ActiveMQ messaging service over web services is due to the complexity of the later over the former. ActiveMQ also allow more robust handling of events as the POS or ERP can be offline as long as the MQ service is online which is very light and convenient. The object passing in XML format is also more easier to handle and allow further anticipated scalability of integration.

[red1]

I have spent some weeks studying the issues and the code involved and thus came to the conclusion that we can reuse the present OB POS helper classes but switch the final commit of messages as an ActiveMQ connection. Both ways we be reusing the Synch modules from OB POS framework of v2.2 but deprecated in 2.3. This is even more important that we use the least intrusive messaging protocol which ActiveMQ is. Future releases of both iDempiere and OpenBravo POS would then pose a minimal maintenance issue.

The ERP side will be implemented in ModelValidator for the Products, Storage, BPartners and Pricing tables. The URL can be set in a SystemConfig i.e. http://queueserver:61613/Products
The POS side will be set via its openbravo.properties. i.e. http://queueserver:61613/ExternalSales
The OB POS XML writing will use constant strings from ADempiere model to ensure best practice review and maintenance.

The source of OB POS is committed to a new branch: SVN/POS_ActiveMQ

[red1]

The Queue is now compliant with I_Order for successful process into Sales Order. The properties setting in OpenBravo are extended to be :

<entry key="host">localhost</entry>
<entry key="port">61613</entry>
<entry key="queue">/queue/test</entry>
<entry key="id">11</entry>
<entry key="org">11</entry>
<entry key="pos">100</entry>
<entry key="user">test</entry>

The user value will be set to SalesRepresentative in the Order. The code is committed to above link. The ImportQueue2AD reads the queue and build the I_Order table. This java code is committed at SVN/branches/FitnesseOBpos. Later it will be introduced as a Process in ADempiere menu which will allow setting of params such as ActiveMQ url, whether to acknowledge the queue and consumed it, and view the no of records counted.

Another process will be created to export out data for the POSes, such as for Products, Storage, Pricings and Customer list. Params will determine which category or filter of info is exported for sales at those POSes.

[red1]

Here is a test for a OpenBravo POS order that has two orderlines which output as 2 XML nodes:

Code: Select all

<?xml version="1.0" ?><entityDetail><type>I_Order</type><detail><DocTypeName>POS Order</DocTypeName><AD_Client_ID>11</AD_Client_ID><AD_Org_ID>11</AD_Org_ID><DocumentNo>019</DocumentNo><DateOrdered>2011-09-01</DateOrdered><ProductValue>Hoe</ProductValue><QtyOrdered>1.0</QtyOrdered><PriceActual>16.0</PriceActual><C_Tax_ID>001</C_Tax_ID></detail><detail><DocTypeName>POS Order</DocTypeName><AD_Client_ID>11</AD_Client_ID><AD_Org_ID>11</AD_Org_ID><DocumentNo>019</DocumentNo><DateOrdered>2011-09-01</DateOrdered><ProductValue>Hoe</ProductValue><QtyOrdered>2.0</QtyOrdered><PriceActual>16.0</PriceActual><C_Tax_ID>001</C_Tax_ID></detail></entityDetail>

After running ImportQueue2AD, the Import Order window has 2 records which gets imported by clicking on the process button:

Import1.png (22.88 KiB) Viewed 79985 times

You can see that the ImportOrder process matches the two Orderlines into a single Order. The Import Order process also resolved the associated column values:

Import2.png (52.78 KiB) Viewed 79985 times

The Sales Order window showed the newly imported Order where both the OrderLines are correctly placed.

Import3.png (54.05 KiB) Viewed 79985 times

[red1]

For the return trip (to export out Product records for OpenBravo POS), I created an ADempiere process, Export2Queue with parameters set to:

Export2Queue.png (22.89 KiB) Viewed 79958 times

and that exported out:

Code: Select all

<?xml version="1.0" ?><entityDetail><type>M_Product</type><detail><ProductName>Grass Seed Container</ProductName><M_Product_Category_ID>107</M_Product_Category_ID><CategoryName>Standard</CategoryName><M_Product_ID>125</M_Product_ID><C_TaxCategory_ID>107</C_TaxCategory_ID><TaxName>Standard</TaxName><UPC></UPC><PriceList>80</PriceList><PriceLimit>72</PriceLimit></detail><detail><ProductName>Travel cost</ProductName><M_Product_Category_ID>107</M_Product_Category_ID><CategoryName>Standard</CategoryName><M_Product_ID>131</M_Product_ID><C_TaxCategory_ID>107</C_TaxCategory_ID><TaxName>Standard</TaxName><UPC></UPC><PriceList>50</PriceList><PriceLimit>50</PriceLimit></detail><detail><ProductName>Mary Consultant</ProductName><M_Product_Category_ID>107</M_Product_Category_ID><CategoryName>Standard</CategoryName><M_Product_ID>132</M_Product_ID><C_TaxCategory_ID>107</C_TaxCategory_ID><TaxName>Standard</TaxName><UPC></UPC><PriceList>100</PriceList><PriceLimit>80</PriceLimit></detail><detail><ProductName>TShirt - Red Large</ProductName><M_Product_Category_ID>107</M_Product_Category_ID><CategoryName>Standard</CategoryName><M_Product_ID>147</M_Product_ID><C_TaxCategory_ID>107</C_TaxCategory_ID><TaxName>Standard</TaxName><UPC></UPC><PriceList>15</PriceList><PriceLimit>15</PriceLimit></detail><detail><ProductName>TShirt - Green Large</ProductName><M_Product_Category_ID>107</M_Product_Category_ID><CategoryName>Standard</CategoryName><M_Product_ID>148</M_Product_ID><C_TaxCategory_ID>107</C_TaxCategory_ID><TaxName>Standard</TaxName><UPC></UPC><PriceList>15</PriceList><PriceLimit>15</PriceLimit></detail><detail><ProductName>Planting Service</ProductName><M_Product_Category_ID>107</M_Product_Category_ID><CategoryName>Standard</CategoryName><M_Product_ID>126</M_Product_ID><C_TaxCategory_ID>107</C_TaxCategory_ID><TaxName>Standard</TaxName><UPC></UPC><PriceList>45</PriceList><PriceLimit>40.5</PriceLimit></detail></entityDetail>


guess.png (18.44 KiB) Viewed 79939 times

With, the above confirmation, the queued XML is then automatically synched into Openbravo POS:

Synch1.png (15.73 KiB) Viewed 79951 times

Checking the Stock shows the new Category "Standard" and its products and its prices.

Synch2.png (35.45 KiB) Viewed 79951 times

Now we can sell those items right away:

Synch3.png (62.72 KiB) Viewed 79951 times

My code is committed under FitnesseOBPOS and the receiving process will be OB POS' ProductsQueueSync (taken from ProductsSync). (Revision: 16120 http://adempiere.svn.sourceforge.net/ad ... 0&view=rev)

The final part will be synching in the Customers and Inventory (during the Product synch, via its Org's Warehouse Locator).

[red1]

The latest openbravo.properties for you to set at the Maintenance Menu when running the latest code:

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Openbravo Websevice configuration</comment>
    <entry key="url">
   http://192.168.1.2:8088/ADInterface/services
    </entry>
    <entry key="id">11</entry>
    <entry key="org">11</entry>
    <entry key="pos">100</entry>
    <entry key="user">test</entry>
    <entry key="password">adempiere</entry>

    <entry key="queue-host">localhost</entry>
    <entry key="queue-port">61613</entry>
    <entry key="orders-queue">/queue/test</entry>
    <entry key="products-queue">/queue/test</entry>
    <entry key="customers-queue">/queue/test</entry>
</properties>

[red1]

Here is a short movie to give a full cycle view of its operations. I will commit the migration scripts that group the synch functions in the AD Menu.

[red1]

I have put the whole process cycle into a workflow together with the ActiveMQ starting and stopping tasks!

synchwf1.png (23.43 KiB) Viewed 79831 times

The above is the new menu structure proposed to house the POS stuff separately from the Sales Order menu to reduce clutter there.

synchwf2.png (34.87 KiB) Viewed 79831 times

In the Workflow we have the Start and Stop tasks for the ActiveMQ.

synchwf3.png (13.44 KiB) Viewed 79831 times

It is tested ok, and I could do the whole cycle similar to the steps in the movie using this workflow (for Adempiere side plus the ActiveMQ turned ON at the start and turned OFF when done.

synchwf4.png (22.48 KiB) Viewed 79831 times

All code and migration script is committed. You have to modify the Task windows to point to your actual ActiveMQ path as i done below for my own server.

tasksynch.png (38.41 KiB) Viewed 79824 times

[red1]

Persistence is now solved.

persistent.png (66.22 KiB) Viewed 79790 times

This is done with the code during connection:

Code: Select all

         header.put(PERSISTENT, "true");
         connection.open(URLhost, port);
         connection.connect("", "");
         connection.begin("MQClient");
         connection.send(queuePath, messageText, "MQClient", header);

The MQ was stopped and then restart couple of times to prove that it really persists. I have tested it both ways, exporting products to the OB POS and importing orders from it. That means if the MQ service is down, there is no fatal lost of data. Once the service is restored you can inspect the panel above and see what messages are still there.

All code submitted to SVN.

[red1]

One good thing about this Apache project is its power of control. Note in the image below of the Customers and Products message queues. We can keep track in real-time the activity among the clients talking to the queue. Also we can configure persistence, performance as well as security parameters. (more, more to come!)

activeadmin.png (103.51 KiB) Viewed 79704 times

Below shows its high visibility and intuitive grasp where you can drill right to the message itself (see the XML string at the bottom of the screenshot, Message Details section):

queuedrill.png (87.95 KiB) Viewed 79704 times

Its terminal console (if run as activemq console) is also very descriptive:

activemqconsole.png (39.73 KiB) Viewed 79704 times

I extended the Process dialog to cover more scenarios such as sending either OR/AND both categories of data, non-availability of MQ, removal of queue to help the newbies user better.

Picture 13.png (21.32 KiB) Viewed 79746 times

To erase, you leave both categories blank and tick the Erase box. You can also refer the Admin Panel before this to view what Queue contents are there and decide what to do.

[red1]

With the help of Dominik (banym) in Munich which setup a remote MQ server http://jenkins.idempiere.com:8161/admin/queues.jsp

jenkinsMQ.png (47.45 KiB) Viewed 79679 times

You can click on it to see the queues in action. We just set the properties in both ERP:

erp2jenkins.png (36.2 KiB) Viewed 79679 times

and at the POS' openbravo.properties:

Code: Select all

<entry key="queue-host">jenkins.idempiere.com</entry>

We can communicate between the ERP and the POS virtually anywhere in the world. We can have location to setup more POS clients to stress the MQ service.

Now i am sending both Product and Customer information:

bothProdCust.png (15.62 KiB) Viewed 79679 times

[red1]

And here are the customers included this time during POS Sync.

synprodcust.png (8.45 KiB) Viewed 79678 times

And we can now examine the Customers table within the POS

custdetails.png (28.5 KiB) Viewed 79678 times

[red1]

We then test it out during a Sales at the POS we click on the Persons icon:

CustSales1.png (15.06 KiB) Viewed 79659 times

And make two sample sales with two different Customers, first we get C&W,

CustSales2.png (11.57 KiB) Viewed 79659 times

and a Hoe for Joe Block. Then after payments, we do a POS Sync and from the ADempiere ERP we do an Import Orders from Queue. We get the loaded Orders processed:

CustSales3.png (7.9 KiB) Viewed 79659 times

They seem to be successful. This means we now have integration of customer list to the POS. From there it is viable to expand that to have Customer Loyalty Promotions where each Customer track record can be accessed and qualified according to a Sales Promotion campaign to enjoy special discounts according to some business rules. Such a module can be developed next within the openbravo POS and ADempiere.

[red1]

I have managed to integrate the Stock movements into openbravoPOS. However there is a gap within that POS which is they do not set that into the display. They do exist in the database as shown here:

StockDiary.png (50.73 KiB) Viewed 79452 times

After making some sales, the table shows the movement accordingly:

StockDiary2.png (32.79 KiB) Viewed 79452 times

And the current stock shows nett:

StockCurrent.png (38.1 KiB) Viewed 79452 times

After exporting the sales to the ERP, and processing the imports allow us to see the stock movement reflected in the Product Info panel. However i discovered a flaw in all the ImportOrders. They should not import IDs but Name or SearchValues and an IDLookUp be done instead. Thus the Warehouse_ID field is not useful as the POS ID is not a PK ID but a real name such as HQ Warehouse. Such a name field is not set in I_Order window nor table. I will correct that ref type and modify the source of ImportOrder and update in SVN later.

[red1]

A comprehensive manual has been uploaded to SVN together with all the binaries and sources of this integration works. There software is workable but I am on the lookout for minor issues which i am still clearing or getting some apprentices here to assist.

Document: http://sourceforge.net/projects/adempie ... f/download

Repository: http://sourceforge.net/projects/adempie ... nbravoPOS/

[red1]

I just uploaded a fresh set of binaries and guide. The latest can solve full Warehouse locator synchronisation for all POS clients. Please download again. I added more user friendly info such as below:

IntegrationModel.png (49.2 KiB) Viewed 79313 times

[red1]

Following some guidelines from openbravo site we can download the Germanlanguage pack and apply it into the application locales and reports folders, and after selecting it in the Configuration and restarting, it is ready in Deutsch!

Screen Shot 2011-11-23 at 3.19.23 AM.png (160.96 KiB) Viewed 78718 times

[red1]

And here is the movie done in German also. Voice over by 9 year old Lia.

[moneil222]

I've gotten my system working just like your POC system. Instead of German, I did the French localization. Very nice and easy!
I've found a bug that I'd like to dig into deeper. Is this the best place to post comments on this topic?

Start with a fresh openbravopos-database with no products.
Export the Tools category from ADempiere and import into the POS.
Everything looks good at this point. It creates the category Tools and all products marked "In Catalog"
Now, export another category of products form ADempiere. I choose Bushes.
Import into the POS.
At this point, we have a problem. All the products in the DB are now set to the category of Bushes.
The existing Tools category gets deleted.
All of the products that existed before (the Tools), have the attribute "In Catalog" set to null (interesting, I can't find this attribute in the DB)
When this attribute is unchecked (found under the Stock Tab of the product listing) the product won't show when you try to make a sale.

Can anyone else reproduce this?
I'd like to dig into this more to see if I can find where this is happening. Is the latest source at:
http://adempiere.svn.sourceforge.net/vi ... _ActiveMQ/

Thanks!
SEan

[red1]

Yes, anywhere is quite a right place to post because if it is not i can always edit and move it, so it is ok for posting here for now.

Yes you are right about the behaviour above. But i think it is easy to solve (if you got the time and can code, i can help review your work and commit it or i move this sub project to a new SF space so that i can admin it easier).

I was not able to resolve some of this matters of which i already described in my PDF's technical section that are still some gaps. Actually the original scope was already big and ambitious which is to integrate OB POS to AD ERP which was never published before, and we find Openbravo closing its synch adapter when it upgraded and locked into its OB ERP and charging a price for it. So it is not easy to think at first how to achieve it or where to start. So it took alot of study and code gap matching until i got it fixed.

Thus this is at best a good POC that integration works both ways, and i left a comprehensive enough PDF to encourage more users and developers to contribute back.

Thank you for showing interest, and your French feedback further give us positive news that OB integration has no backward instability and i hope you do assist further in any small way.

cheers!
red1

[red1]

Moneil,
I just read your comment praising this work in Youtube. And now realised you were here too and i did answer but not in full.

OK, if you can go into the code, there is a reloading of products during synchronising where Openbravo POS deletes all the old Catalogs. What this means is this: That you supposed to load multiple catalogs at one time but we are only setting one catalog during export from ERP.

Thus this can be resolved in two approaches:
1. To make the ERP side able to export more Catalogs perhaps under a Product Category Summary heading (easy to do) and we just have to tweak the Process to iterate the child categories and write to the XML string each different category.
1.b We have to make sure the import side of the Openbravo POS during POS synchronization remembers that arrangement.

2. We can just break the normal Openbravo POS treatment of importing not to delete old categories.

In reflecting this, i think we should allow both ways. But this will mean we need to setup more flags perhaps in the openbravo.properties page.

WDYT?
BTW, i be in Paris from 26th Dec till 3rd Jan, 2012. We can meet too!

red1