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

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

So the modified files are

CustBranches.php
Stocks.php
DeliveryDetails.php
SelectOrderItems.php

some of the tables have additional fields:

so far I have:

ALTER TABLE custbranchs ADD COLUMN accountid INT;

I've included the mods for CustBranches.php under statments like ..

if ( $_SESSION['vtiger_integration'] ==1 ) {

I'm surprised there's no modification required for Customers.php - the script 
that allows new charge accounts to be created and edited?? You'd need to 
create a new customer in vtiger if you created on in webERP would you not???

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