Re: [WebERP-developers] [webERP-users] successful integration of webERP and VTiger

[Phil D. <we...@pa...>]

Gareth,

It is so good to get a contribution like this - many people have requested 
this and it needs someone with a hand in both to get down and dirty. Great 
job!

I started to work through the modifications to webERP necessary for the 
integration - I am planning to build these in subject to the setting of a 
parameter to allow the code to execute. I'll comment on things as they come 
to hand .... 

First off I notice in your sql file that you have all webERP tables as myisam.

It is very important to use innodb tables with webERP - data integrity is at 
risk when myisam tables are used and installations that do no use transaction 
safe tables are in my view taking unecessary risks. myisam tables have no 
foreign key checking and no ability to roll back transactions that don't make 
it for whatever reason potentially leaving the db in a very difficult 
position to recover proper DB integrity.

If vtiger doesn't use transaction safe tables (and this surprises me) then it 
is no problem to have inserts/updates to these tables which will not rollback 
should something go wrong with the transaction. Of course the downer here is 
the integration with webERP will be stuffed!!

It would a seriously retrograde step to compromise data security integrity in 
webERP for the sake of this integration.

Phil


On Wednesday 26 July 2006 06:49, Gareth Spanglett wrote:
> Hello all,
>
> Well, it took a little longer than I thought, but I think I have
> successfully integrated webERP and VTiger.  I have a demo up at
> http://testbed2.agora2.com/ with the demo data sets.
>
> My client has separate departments for sales, production (including
> inventory and logistics) and finance.  The sales department is
> responsible for the sales process up to the sales order, after which the
> production and logistics department arranges manufacturing and shipping.
> Suppliers deal exclusively with production while customers deal
> exclusively with the Sales Department.
>
> The details of the integration are as follows.  Please note that since
> this is being posted to webERP's mailing list, I will refer to webERP
> terms.  A similar post is being made to the VTiger discussion forums.
> The integration is almost invisible from webERP's interface and only
> required minor php changes and additions.  I haven't changed the basic
> functionality of webERP in any way.
>
> IN WEBERP:
>
> webERP has been modified to post appropriate entries into a number of
> VTiger tables and fields in shared tables when adding or editing
> records  (customer branches, inventory, sales orders and sales order
> details).  It also ties into VTiger's tracking system.
>
> To match VTiger, the modified webERP now maintains subtotal, tax, and
> grand total fields printed on sales orders.
>
> I also added several new fields to underlying webERP tables to capture
> VTiger data.  This way, VTiger still tracks all of its usual information
> - just in different tables and with some modified field names. Otherwise
> the vast majority of webERP remains unchanged.  In particular, the
> following webERP files were modified:  CustomerBranches.php,
> DeliveryDetails.php, Stocks.php, SelectOrderItems.php, and a few
> include/ files.
>
> IN VTIGER:
>
> In the modified VTiger, accounts, products and sales orders tabs are now
> completely integrated with webERP.
>
> Accounts tab - VTiger accounts correspond with branches in webERP.
> While VTiger did allow a "Parent Company" to be identified, the list of
> companies was essentially the branches.
>
> VTiger now corresponds with webERP's customer/branch structure.  When a
> new 'account' is created in VTiger, the record must be associated with
> an existing Parent (a webERP customer) or if new, it creates the
> appropriate webERP customer and related branch record at the same time.
>
> All account/branch information is now equally and fully shared between
> both applications.  Accounts (customers and branches) can be added, and
> branches can be edited or deleted in either program.   The following
> fields have been integrated into VTiger from webERP:  Sales Area, Sales
> Person, Tax Group, Currency, Stock Location, Default Freight Company,
> Billing and Shipping Address, and Parent Company.
>
> Products tab - inventory is controlled by the production department via
> webERP.  They order raw ingredients and determine manufacturing and
> hence quantities in stock.  Therefore, VTiger can view products
> (inventory) and edit some fields, but cannot add or delete products.
> Quantities in stock and in demand as well as inventory categories are
> pulled from webERP.  Vendor information has been removed from VTiger,
> since vendors are controlled by Production (and thus webERP).
> Pricebooks work as normal.
>
> Orders tab - since Production is in charge of suppliers, the Purchase
> Order tab has been removed.  Both webERP and VTiger can post sales
> orders, with both programs able to view, add and edit orders.  Taxes are
> automatically calculated using webERP's formulas by looking at the
> location of the customer, the location of the distribution center, and
> the taxes applicable to the item itself. Both webERP and VTiger can
> manually adjust the tax amount before saving a record.
>
> Invoice tab - has been de-activated in VTiger for now.  Invoices (and
> packing slips) are generated through webERP.  There are two reasons for
> this:  one, invoicing affects accounting and it is already integrated
> into webERP; second, batched, lotted or controlled products are tracked
> by webERP at the invoicing and shipping stage of the process.  As far as
> I can tell, VTiger does not handle batches, lots and individually
> controlled inventory items.  I will modify the Invoices tab in VTiger to
> grab invoice information read-only, in order to inform the sales staff
> when invoices and deliveries are sent.  However, since sales won't be
> issuing invoices, the Invoices tab will be read-only.
>
> Quotes tab - quotes now have some additional fields to match the sales
> order modifications.  otherwise, works as normal.
>
> Potentials tab - Potentials used to be convertible to an invoice.  Since
> we are no longer using VTiger to create invoices, potentials are instead
> converted to quotes.  This has the added advantage of being able to
> attach products (via an attached quote) to potentials - to be used for
> raw material ordering, manufacturing and cash flow projections in webERP.
>
> I also added customized numbering for products, accounts and sales
> orders that are used by both VTiger and webERP.  Otherwise the vast
> majority of webERP remains unchanged.
>
> Technically speaking, most of the VTiger changes were in the MySQL
> tables (modifying 'field' table, moving accounts, products, and sales
> orders to corresponding webERP tables: debtorsmaster, custbranch,
> stockmaster, salesorders, salesorderdetails), and to utils.php (several
> new VTiger uitypes for new fields, removing others).  I also modified
> some of VTiger's module files in Accounts, Products, Orders, Quotes
> (minor) and Potentials (minor), as well as a couple of changes to
> include/general.js.  Finally, I did some global searching and replacing
> on changed fields and tables.
>
> TO DO:
>
> *  flagging accounts in VTiger that have been flagged in webERP (i.e.
> over credit limit, account on hold, etc.).
> *  bringing invoice information into VTiger from webERP (read only)
> * synchronizing sales personnel between VTiger and webERP  (manually
> maintained at the moment)
> * filtering out only finished goods and inventory for sales staff to
> order from
> * customizing reports
> * building in sales projection tool (using potentials and quotes) tied
> into production needs and cash flow statement.
>
>
> FINAL COMMENTS:
>
> The trick to getting this to work was to work with the existing logic
> and structure of both applications - change too much and they no longer
> perform as designed.  webERP is a fabulous application, and the
> production and finance departments are thrilled with the upgrade.  The
> level of detail clearly reflects the great experience and expertise of
> the production, inventory and finance personnel who helped create it.
>
> VTiger's strength is the way it creates related information through a
> unique crmentity id for each item entered, and it continues to do so.
> It simply uses tables in common with webERP and adds some new
> information needed for both applications to be able to equally view and
> manipulate shared records.
>
> It would be unreasonable for the VTiger team to try and replicate this
> kind of ERP functionality from scratch:  the number of interrelated
> pieces of data tracked in parallel, taxes, currencies, managed inventory
> and warehouses, all tied into accounting.  Not only is VTiger currently
> incapable of this type of inventory and logistics management, it was not
> structured and built to ever have these types of connections.
>
> However, the inverse is also true.  VTiger was built by people with
> great insight into sales and customer relationship management.  It would
> be equally unreasonable for the webERP team to expect to simply create
> an ad-hoc sales management system - the amount of tracking and
> functionality built into VTiger reflects years of experience in this
> highly specialized field:  identifying potentials, leads, tracking
> meetings, calls, emails, identifying proposal stages, managing sales and
> multiple pricebooks, etc.
>
> These two products working together is the best of both worlds.  When I
> finish the sales projections integration, it will be able to match
> projected sales from VTiger with bills of materials in webERP,
> identifying what raw materials will need to be ordered and when.
>
> I have the code and MySQL database file available and I would be more
> than happy to work with anyone else who may be interested.  I am kind of
> new in the open source community so any advice as to what would be the
> best way of sharing the code would be appreciated.  It can be manually
> installed in a couple of minutes and would be pretty easy to set up a
> automated installation script to make the necessary changes to the
> config files.
>
> Keep up the great work - webERP is an amazing ERP and I hope this
> contribution helps.
>
> Gareth.
>
>
>
>
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share
> your opinions on IT & business topics through brief surveys -- and earn
> cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> web-ERP-users mailing list
> web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-users