- Published on Saturday, 27 March 2010 11:46
- Hits: 1853
OsFinancials Export Import Plugin User Guide
Import Export general
Version of VTiger
I built this version on then vtigercrm 5.0.3 RC2 release of vtiger
From version 18.104.22.168 + the release 5.0.3 will be supported. They made some last minute changes to the database. This is what they currently offer as download so many of you guys must have had problems setting up.
Setting up a Vtiger link
To set up a VtigerCRM link the files osfimportphp4.php and all *.MAP files must be uploaded to your web server that has connection to the VTiger MySQL database. Be sure that the directory where the files are uploaded are password protected by a .htaccess file, otherwise anyone can access your database directly!!
The procedure is exactly the same a described for setting up a oscommerce integration and we need the same files to connect to VTigerCRM
Copy files to webserver
You can find the files (if you have done a default installation of osFinancials) in directory C:\Programm Files\osFinancials\oscommercephp\.
Copy all files EXCEPT the 2 files, info.txt and osfimport.php, to your desired protected directory. Then change the content in configure.php to reflect your VTigerCRM database connection settings.
Remark 2: osfimport.php versus osfimportphp4.php. The first file was the original delivered file for the Plugin of first osFinancials release. In time this file is updated to solve specific errors and called osfimportphp4.php. See the release info inside the osfimportphp4.php itself.
As VtigerCRM works on a MySQL5 database, the osfimportphp4.php file should be inserted with the next part of code:
$todelphi = array(
'int' => 'i4',
'real' => 'r8',
'unknown' => 'r8',
Set up import/export in osFinancials
You use the ExportImport-plugin found in Extra->Plugins in the main osFinancials menu.
To add your VtigerCRM setup, you need to add it as an import source in the Setup tab. (press on the + sign)
Fill "Use the url" field with the full URL path to your osfimportphp4.php. If you have made the directory where the osfimportphp4.php resides secured, you can also add a an username and password (strongly recommended) in the fields php user name and php password.
To map the stock items in VTigerCRM to the right osFinancials account codes I have added a cross reference table. This table uses the VTigerCRM Tax percentage field to map osFinancial general ledgers to the items. So if you entered 19.000 % tax in VtigerCRM you can add a entry here. Use the External id to enter the tax rate that is in the VtigerCRM stock file.
Then set all general ledgers used in osFinancials to the correct ledgers.
The current plugin pulls all stock items, contacts, accounts and sales orders from VtigerCRM. Also your current debtors in osFinancials can be uploaded to VTigerCRM.
The text inserted into the ini field area is as follows:
Import and export data
You should now be able to import your VTigerCRM data in osFinancials and vise versa.
Setting up an internal link
There is a new plugin DataServerExe.exe that can be used to build an internal link between osF or to provide a link with many other databases including ODBC connection through ADO.
You can use this source to import data from any source if you add the select and field map files.
The DataServerExe.exe has an ini file with the same name. If you copy this exe and rename it there will be an ini file with the renamed name so you can set up multiple sources.
The current plugin is still in development but you can already pull in data
If you want to pull in contacts you need to pull the account data first so the contacts are link to the account.
Adding user defined links
This new way of importing allows you to build your own import from a source. The files that hold select SQL and field maps are in the c:\osFinancials\bin\SQL\VTiger folder. Lets take a look at the dirs there.
Dir 0 holds
SelectAllContacts.txt : the select for the contacts in Vtiger ContFieldmap.txt : The fieldmap for the select in vtiger
SelectAllCustomers.txt : the select for the customers in Vtiger CustFieldmap.txt: The fieldmap for the select in vtiger
SelectAllStock.txt : the select for the stock in Vtiger StockFieldmap.txt : The fieldmap for the select in vtiger
Dir 1 holds
SelectAllContacts.txt : the select for the contacts in a other osF ContFieldmap.txt : The fieldmap for the select
SelectAllCustomers.txt : the select for the customers in a other osF CustFieldmap.txt: The fieldmap for the select
SelectAllStock.txt : the select for the stock in a other osF StockFieldmap.txt : The fieldmap for the select
The select and source part
The dirname 0 is the type of import used (field type in setup) The SelectAllContacts.txt has the full sql needed to get contacts from the remote system (in this case VTiger). The ContFieldmap holds the mappimng between osF fields (left side) and external source fields (VTiger).
The ContFieldmap.txt and SelectAllContacts.txt in the folder 1 link this for a osF interal connection.
You can copy this to add your cutom type that does not get overriden by a install. The types are set in the ini part of the setup.
Correcting SQL errors
If you have a other system or you dont have a field that is requested in the SQL you must change the SQL for the selects and inserts. The sql must run in your PHP My sql admin for the VTiger (or other) database.
Import Export Clearing data
Deleting from osF
If you delete records form osF they will automaticly remove the records in the table IMP_LINK. If you want to delete the link between a debtor and a VTiger account you will need to delete the record in IMP_LINK. If you have removed this record from VTiger and want to upload the data again. The update SQL will be used if a record is matched in the IMP_LINK table else a insert SQL will be used.
Deleting from Vtiger
Deleting from vTiger does not affect your data in osF. You could remove them from osF if you like or just remove them out of the IMP_LINK table.