OsFinancials E-commerce Plugin User Guide
Introduction
This is a further developed manual for the E-commerce plugin, instead of only supporting osCommerce.
Context
The E-commerce Plugin is a commercial add-on for osFinancials and is distributed as closed source.
The E-commerce Plugin is a very powerful tool to automate your customer-, order- and stock- administration by downloading all your necessary E-commerce data directly into osFinancials or upload it into your e-commerce solution. By using this Plugin you are able to save a lot of time with re-typing your orders and customer data into your financial system, avoiding typing errors and / or selecting wrong General Ledger Accounts. As the import will take only seconds, your financial administration will be up-to-date with actual data from your web store. You can also upload your products into the E-commerce solution (for now only osC2go and osCommerce) without having to creat the products both in osF and your E-commerce solution.
All customer data, stock items and customer orders will be downloaded into your osFinancials administration for further processing to your General Ledger Accounts. You are able to use the invoicing functionality from osFinancials to print your invoice reports. And even send the invoice per email to your customer, or upload ythe invoice to the webserver for your customer to download it by their sales history. With the processing of your customer orders the stock item level will decrease, with processing of your purchase orders your stock item level will increase. This results in having the correct stock item level in osFinancials that can be reflected in the Ecommerce solution by an upload.
This Guide-book will let you get acquainted with the E-commerce Plugin of osFinancials, and therefore does not pretend to be complete. It describes in chronological order the setup and the required parameter settings for the E-commerce Plugin delivered within osFinancials.
Points of Attention
For the purpose of this document the E-commerce installation is prepared on the following Internet address: osCommerce: http://localhost/catalog/ Virtuemart: http://localhost/
Note! You have to change this web link where it is used in the examples to your own web store address where needed.
osFinancials is installed into the default installation directory c:\Program files\osFinancials. You have to chance this directory to your needs if you have installed the osFinancials program on another place. Also the Firebird database is installed on your machine where osFinancials is installed.
osFinancials can be installed on a network share. The Firebird database can be installed on a separated server. In Section F – appendix 1, the different location of the configuration storage file will be discussed.
What you may expect
After reading this document you should be able to: activate the E-commerce Plugin of osFinancials download data into osFinancials influence the data by means of parameter settings upload/sync your stock item level between osFinancials and the E-commerce solution.
Global overview
First have a look at the picture below. It gives a graphical overview of the E-commerce Plugin within osFinancials. It shows all elements and the data flow between those elements.
The E-commerce Plugin is easy to reach via the osFinancials main program and lead the data exchange process. osFinancials and osCommerce will not exchange data without activating the Plugin manually.
The osCommerce Plugin will set up the connection between osFinancials and osCommerce. The Plugin is situated inside the osFinancials program. The Plugin will collect all customer-, order- and static stock item data from osCommerce and will insert the data to the right place in your osFinancials administration.
Note! The Plugin will download your stock level from E-commerce solution into osFinancials. The Plugin will upload your stock level from osFinancials into E-commerce solution if manually activated.
The E-commerce Plugin process flow:
- Insert your stock item data in E-commerce solution needed for selling products
- A new customer registers him/herself in the E-commerce solution
- This customer orders a stock item
- Download the sales order including the customer- and stock item data
- Create your purchase order in osFinancials
- Process purchase- and sales order to update your osFinancials stock level
- Upload your osFinancials stock level (result of purchase minus sales) into the E-commerce solution
You can also download your stock items directly after inserting them in the E-commerce solution and purchase your stock items on forehand. When the purchased stock items arrive, check the goods and process the purchase order in osFinancials. Upload your osFinancials updated stock level and wait for your first sales order.
osCommerce web store preparation
First step is to prepare osCommerce for communication with osFinancials. For this purpose the osFinancials full installer will create a directory that contains a set of files that you must copy/FTP to your osCommerce web server into your admin section.
The security of your data connection depends on the .htaccess and .htpasswd file on your web server. With a standard osCommerce installation you have created this security file for your /catalog/admin directory and subdirectories.
The osCommerce Plugin will use the user and password created in your .htaccess and .htpasswd file to connect from osFinancials to osCommerce.
Copy/FTP files to osCommerce web server
You can find the files (if you have done a default installation of osFinancials) in directory C:\osFinancials\OSCommercePHP\.
Copy all files EXCEPT the 3 files, configure.php and info.txt and osfinancials.php, to the following osCommerce installation path: /catalog/admin/includes/
Remark 1: In osFinancials1.0 software the files osfinancials.php and osfinancialsphp4.php are called respectively turbocash.php and turbocashphp4.php. {Therefore, if you are using 1.0.x, please substitute those filenames in these instructions?}
Remark 2: osfinancials.php versus osfinancialsphp4.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 osfinancialsphp4.php. See the release info inside the osfinancialsphp4.php itself.
Check and Edit the configure.php file
Use a php-editor or Wordpad (not Notepad) and open the original configure.php file in your /admin/includes/ directory on the web server. Scroll to the bottom of the configure.php file. Check if the four "define" text strings listed below have been set. If not, paste the four "define" text strings mentioned below, above the last “php end code” ?>.
define('DB_SERVER', 'localhost'); // eg, localhost - should not be empty for productive servers
define('DB_SERVER_USERNAME', 'username'); // user with more than select grant
define('DB_SERVER_PASSWORD', 'password'); // his password define('DB_DATABASE', 'databasename'); // name of the osc shop database
The last lines should look as follows:
// define our database connection define('DB_SERVER', 'localhost');
// eg, localhost - should not be empty for productive servers
define('DB_SERVER_USERNAME', 'username'); put the database username in here
define('DB_SERVER_PASSWORD', 'password'); put the database user password here
define('DB_DATABASE', 'databasename'); put the database name here
define('USE_PCONNECT', 'false'); // use persisstent connections?
define('STORE_SESSIONS', 'mysql'); // leave empty for default handler or set to 'mysql' ?>
Note! Be sure that you adjust 'username', 'password', and 'databasename' according your database connection details. As to the define('DB_SERVER', 'localhost', in most instances it remains 'localhost', unless your database is stored on a secured server separated from the web server. Regarding defines USE_PCONNECT and STORE_SESSIONS values, they have tot have your normal osCommerce settings, these values do not influence the working of this Plugin. Save the configure.php file on the web server.
Copy/FTP files to Zen-Cart, Creloaded web server
There are several web store software packages available that are based on osCommerce. Zen-Cart is one of them and commonly used. As long as the database structure equals the osCommerce database structure these web stores can communicate with the osCommerce Plugin too.
Still we have to take a slightly different approach regarding the web store preparation, as for example Zen-Cart doesn't make use of the .htaccess and .htpasswd file to secure access to the admin section but uses a own login screen programmed into the core code of Zen-Cart.
Notice here that also osCommerce has a third party module where the same login screen is used to grand administrators access to the admin section. If you have installed this third party module, you can follow the guidelines described here too.
As the osCommerce Plugin does rely on the .htaccess and .htpasswd file, a connection can not be made in Zen-Cart as described in the previous chapter of this document. To make it work for Zen-Cart, create a new directory called /osfinancials in your web store root directory.
Copy all files from your osFinancials installation directory C:\osFinancials\OSCommercePHP\ EXCEPT the 2 files, info.txt and osfinancials.php, to the following Zen-Cart installation path on your web store server: /zencart/osfinancials/
Edit the copied configure.php file to reflect your database connection settings.
Create a .htacces and .htpasswd file to secure your /zendcart/osfinancials/ directory. If you need help to create both files please request your web hoster. Probably you will find a tool in your webhosters admin section to create the needed files.
Note! that we also copied the configure.php in contrast with what is described in section Copy/FTP files to osCommerce web server!
Note! From Creloaded 6.2 version some tables and fields are changed due to added functionality in the product options area. Tables and fields are added and fields are shifted between tables. Due to this the query in ZQOptions.txt needs to be changed for Creloaded 6.2 and upwards versions.
The query ZQOptions.txt can be found in your osFinancials installation directory e.g. c:\osFinancials\OSFOSCsql\
Replace Code:
SELECT products_options_id, products_options_name
FROM products_options where language_id =%s
with Code:
SELECT a.products_options_id, b.products_options_name
FROM products_options a, products_options_text b
WHERE b.products_options_text_id = a.products_options_id
AND b.language_id = %s
Virtuemart web store preparation
First step is to prepare Virtuemart for communication with osFinancials. For this purpose the osFinancials full installer will create a directory that contains a set of files that you must copy/FTP to your virtuemart web server.
The security of your data connection depends on the .htaccess and .htpasswd file on your web server. With a standard virtumart installation you have created this security file for your /administrator directory and subdirectories.
The Ecommerce Plugin will use the user and password created in your .htaccess and .htpasswd file to connect from osFinancials to virtuemart.
Copy/FTP files to Virtuemart web server
You can find the files (if you have done a default installation of osFinancials) in directory C:\osFinancials\OSCommercePHP\.
Copy this folder, to the following virtuemart installation path: /administrator
Remark 1: In osFinancials1.0 software the files osfinancials.php and osfinancialsphp4.php are called respectively turbocash.php and turbocashphp4.php. {Therefore, if you are using 1.0.x, please substitute those filenames in these instructions?}
Remark 2: osfinancials.php versus osfinancialsphp4.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 osfinancialsphp4.php. See the release info inside the osfinancialsphp4.php itself.
Check and Edit the configure.php file
Use a php-editor or Wordpad (not Notepad) and open the original configure.php file in your /admininistrator/OSCommercePHP/ directory on the web server. Scroll to the bottom of the configure.php file. Check if the four "define" text strings listed below have been set. If not, paste the four "define" text strings mentioned below, above the last “php end code” ?>.
define('DB_SERVER', 'localhost'); // eg, localhost - should not be empty for productive servers
define('DB_SERVER_USERNAME', 'username'); // user with more than select grant
define('DB_SERVER_PASSWORD', 'password'); // his password
define('DB_DATABASE', 'databasename'); // name of the osc shop database
The last lines should look as follows:
// define our database connection define('DB_SERVER', 'localhost');
// eg, localhost - should not be empty for productive servers
define('DB_SERVER_USERNAME', 'username'); put the database username in here
define('DB_SERVER_PASSWORD', 'password'); put the database user password here
define('DB_DATABASE', 'databasename'); put the database name here
define('USE_PCONNECT', 'false'); // use persisstent connections?
define('STORE_SESSIONS', 'mysql'); // leave empty for default handler or set to 'mysql' ?>
Note! Be sure that you adjust 'username', 'password', and 'databasename' according your database connection details. As to the define('DB_SERVER', 'localhost', in most instances it remains 'localhost', unless your database is stored on a secured server separated from the web server. Regarding defines USE_PCONNECT and STORE_SESSIONS values, they have tot have your normal osCommerce settings, these values do not influence the working of this Plugin. Save the configure.php file on the web server.
E-commerce Plugin settings
Starting the Plugin and setting the connection parameters
The E-commerce Plugin is included into the installation of osFinancials and directly available to you. You only need to buy it through our webshop and activate it by the instructions you'll receive by email. The start button is situated on the right in the action bar on top of your screen.
Press the OSC-Plugin start button and the window displayed below will open on your screen.
Now you have to setup the connection from the osCommerce Plugin to the web store. Click on the menu item “Connection” and the window on the next page will open:
In this opened window you can setup all the connection parameters to make a connection between osFinancials and your osCommerce web store. A list of the fields and their descriptions is given below.
As of version 2.0.1.3 a new parameter is introduced to specify the folder where your sql files are stored. This allows custom sql files to exists and not be overridden with the default installed files. This folder is relative to the root folder of osfinancials.
Its defaulted to OSFOSCsql so the folder c:\osfinancials\OSFOSCsql contains all the SQL files. if you change the parameter to your custom OSFOSCsql2 diretory it will use the sql files located in c:\osfinancials\OSFOSCsql2.
If youre running a virtuemart shop please replace OSFOSCsql with osCOsfJoomla1_0
Field name | Description |
Date formaat: | The format how the date is retrieved from the store. This depends on the database locale settings on your hosting machine. Mostly this is configured as standard and you can use the predefined setting. Otherwise you have to specify how the date is retrieved, where yyyy =year – mm=month – dd=day |
Datetime format: | The format how datetime is retrieved from your store. Depends on the database locale settings on your hosting machine. Mostly this is configured as standard and you can use the predefined setting. Otherwise you have to specify how the date is retrieved, where yyyy =year – mm=month – dd=day – hh=hours – nn=minutes – ss=seconds. |
Prefix: | Here you can insert the table prefix if you have a customized table name configuration. e.g. “shop1_address_book” instead of standard “address_book”. Then you fill here “shop1_” |
PHP Url: | The full Internet address to your osfinancialsphp4.php file.
e.g. http://localhost/catalog/admin/includes/osfinancialsphp4.php or if you have a SSL certificate installed https://localhost/catalog/admin/includes/osfinancialsphp4.php |
Username: | The username that you use to login in your admin section of the web store (defined in your .htacces and .htpasswd file, not the database user) |
Password: | The password that you use to login in your admin section of the web store (defined in your .htacces and .htpasswd file, not the database user password) |
Tax setup: | You have to choose between:
Exclusive (All prices are stored excl. Tax in your osCommerce database) Inclusive (All prices are stored incl. Tax in your osCommerce database) With a standard osCommerce web store configuration the prices are normally stored excluding Tax, even when your osCommerce parameter in the web store is set to display prices incl. Tax! |
Remember Password: | If you tick this field, the password is stored for automatic login next time. |
Button Test Login SQL | Herewith you are able to test if your inserted connection parameters are correct. |
After all data is filled in correctly, push on the button “Test login SQL”. Into the result screen the following text should be displayed:
If your connection is successful, save the configuration by pushing the most right “Save” button. The parameters are saved as “Default” connection. The default connection is the first connection that is configured and the Plugin will start every time automatically with this configuration.
If you have more web stores that you want to connect to osFinancials and share data within one specific administration, you can fill in the data for the second web store and push the “Save config” button and the following screen will pop up.
You will be asked with what name the configuration needs to be saved. Use your second web store name. If you want to make a connection to your second web store you easily can determine the correct store to connect to.
Press the OK button and you will come back in the window below.
Press the “OK” button en this window will close. You will now enter automatically into the Standard window of the osCommerce Plugin.
SECURITY ISSUE
- All connection data is stored in a file on your local computer at C:\osFinancials\books\your_administrationname\OSCommerce.ini. For security reasons keep in mind that the password used is stored in a readable flat text format when “Remember Password” is set to Yes!
- If you have a SSL certificate installed for your web store use https instead of http in your connection string.
Standard Window of the osCommerce Plugin at startup
The window below (see next page) is the “Standard” window of your osCommerce Plugin that opens when a connection is made with your web store. This will have your default connection details configured as described in paragraph C.1. This window also opens every time you press the OSC-Plugin button on the osFinancials action bar:
The details of this screen will be handled in Section 4.5
You now start with the two most important steps of the osCommerce Plugin configuration.
First you have to define several generic parameters in tab “Extra options” and then link the osFinancials General Ledger Accounts to the osCommerce invoice details in tab “Tax mapping”. This will ensure that all downloaded data is inserted into osFinancials at the right places. By means of these parameters you can influence data retrieval and storage, resulting in having a flexible solution that will fit in all kind of administrations. The tab “Extra options” is described in paragraph 4.3 and the tab “Tax mapping” in paragraph 4.4
Extra Options window
The Extra Options window has to be setup only once. Here you can define options which data you want to transfer from osCommerce to osFinancials and with which conditions you would like to do that.
Open the “Extra Options” window by clicking on this tab. A part of data stored in your osCommerce web store is automatically downloaded and displayed. Have a look to the picture below.
Below an explanation will be given for all fields on this tab.
In this opened window you can setup all the connection parameters to make a connection between osFinancials and your osCommerce web store. A list of the fields and their descriptions is given below.
Field name | Description |
Product Language | This field is a pull down menu. All languages installed at your web store will be displayed on the pull down menu. You have to choose in which language your osCommerce data will be stored in your osFinancials administration, e.g. how your Stock Item data descriptions will be inserted into osFinancials. |
Status | This field displays the order statuses defined in your web store. Select here which orders will be downloaded to your osFinancials administration. Statuses not ticket here, will be omitted.
(If the statuses are displayed in a wrong language, select the correct language and close the plugin and start the plugin again. The data will be downloaded in the selected language.) |
Invoice Number prefix | This is a free to choose prefix that will be added to your osCommerce order number when imported into osFinancials. |
Customer Code prefix | This is a free to choose prefix that will be added to your osCommerce Customer code when imported to osFinancials. |
Stock Code prefix | This is a free to choose prefix that will be added to your osCommerce Stock Code when imported to osFinancials. |
How to dial with these prefixes? The prefixes are very handy to identify in your osFinancials the different kind of orders. If you sell only from your web store it isn't that important, but if you sell also from a regular store or from a second web store, you certainly want to distinguish between your regular customers and orders and your web store customers and orders. Thus by using a prefix, you are able to distinguish them in your administration. In this example we use “OS” as prefix for the web store orders and customers. |
Field name | Description |
Resync Date-Time on pull | If this field is ticked then the “from date” on the Standard tab changes automatically to the latest synchronization date and time. |
Import as quote | If this field is ticked then your osCommerce order will be imported as a quote into osFinancials. You have to transfer manually the generated osFinancials quote into an invoice within osFinancials. The invoice will have your standard invoice prefix from osFinancials. (This function is very handy when your government only allows one invoice series.) |
Import Item Attributes as comment | If this field is ticked the Item Attributes of an order will be imported into a text line below the invoice item line in osFinancials. |
Use Stock ID | If this field is ticked the osCommerce Stock ID will be imported instead of the Products Model description. Your osFinancials Stock Code field is than filled with the osCommerce Stock ID instead of the Products Model. |
Doc Reportinggroup1 as Status | If this field is ticked the current order status from osCommerce will be imported to osFinancials into the Reportgroup1 field. This field is available in your osFinancials invoice overview (last column) but can be moved to any place on your overview. Just drag it with your mouse to the desired place. You can follow the order status now in osFinancials as this status field will be updated with every synchronization. |
Import osC Categories as Stockgroup1 | If this field is ticked the osCommerce Categories will be imported into the Stockgroup1 field in osFinancials. In this way you can easily group your reports based on your osCommerce categories. If you have sub categories, these will be delimited with a @.
example: Bicycles@mountainbike NOTE 1: If you have a Stock Item copied into more then one Category in osCommerce, the Stock Item is attached to the last category. NOTE 2: If you chance the category name in osCommerce, the category will chance in osFinancials too with the next Sync/Download |
Edit Existing Items | If this field is ticked only new Stock Items are added to your osFinancials stock. Existing Stock Items will be unchanged, even when the Stock Item is changed in your osCommerce stock. |
Global ORDERS URL link osF with osC | Double click on this field and the default link to your osCommerce web store will be filled in automatically. Change the example domain name to your own domain name to make it work.
As a result you will be able to right click on your osFinancials Invoice overview to open the selected order file in osCommerce directly in your Internet browser. |
Global CUSTOMER URL link osF with osC | Double click on this field and the default link to your osCommerce web store will be filled in automatically. Change the example domain name to your own domain name to make it work.
As a result you will be able to right click on your osFinancials Invoice overview to open the selected customer file in osCommerce directly in your Internet browser. |
The next fields are only used if you have a very complex osCommerce setup and deliver products over the whole world. In that case you might want to define special general ledgers for sold products outside your state that can be shipped without tax. This functionality was build for a specific client situation and works for Dutch resellers that deliver outside the European Union. Because osCommerce only stores the Tax value without a description, it is very difficult to use it in a situation where you are in needed of several tax classes that have the same % e.g. in the USA. Therefore the next fields are described less. In normal situations you do not need these fields. |
Field name | Description |
Use tax rates from order products | This field works in combination with the field “Link Rate US tax” of the Tax mapping tab, described in Section C4, and the “Debtor default sales account not in country list”. If flagged the next field can be set. |
Debtor default sales account not in country list | This field is a text field that only has to be filled if you have flagged “Use tax rates from order products”. In that case you have to define all countries that need to be mapped to a tax value listed on the Tax mapping tab.
The field “Link Rate US tax” on the Tax mapping tab needs to have a value that equals the osCommerce tax value so a match can be made between the osCommerce tax lines and osFinancials tax lines. If the tax line can not be matched based on “Link Rate Ustax” and the predefined countries, the invoice line will be mapped against a specific sales account mention in field “Account” on the right. |
Debtor group 1 | This field list your created debtor group in osFinancials via the Menu -> Setup -> System Parameters -> Groups in tab Debtor groups. This chosen debtor group will be added to the debtor that falls within this special criteria. |
TAX Mapping window
Default Tax
Now you have to link the osCommerce Taxes and Order Total Modules appearing on the osCommerce invoice lines to the specific General Ledger Accounts in osFinancials. This is done via the Tax Mapping window.
This is an action that needs to be done during the set up of the Plugin and when you are customizing your osCommerce website by changing or adding Order Total Modules or Tax settings.
Open the Tax Mapping window by clicking on this tab. The “Tax Classes” stored in your osCommerce web store are downloaded and displayed into the menu line “Default tax”.
In this example only one Tax Classes in osCommerce named Taxable Goods has been defined. See for more details the next picture taken from the osCommerce web store.
Now you have to define the relationship between the Tax Classes of osCommerce and the General Ledger Accounts in osFinancials. Select a osCommerce tax class on the left by opening “Default tax” and selecting a specific tax class, in the example “Taxable Goods”. On the right the details of the relationship between the selected osCommerce tax class and osFinancials General Ledger Accounts can be registered.
Select the required General Ledger for the different pull down fields. You have the option to use the pull down menu on each field to select a specif General Ledger.
The last Default tax is called “Unknown Tax” and is inserted automatically by osFinancials in case there is an osCommerce invoice that can not be mapped with one of the default taxes listed.
The Tax Classes stored in osCommerce that are imported into the osCommerce Plugin.
Order Total modules
Big chance that you have installed and activated so called “Order Total Modules” in your osCommerce web store.
You are probably using the two very common Order Total Modules Low Order Fee and Shipping. In the Plugin we need the exact name of the Order Total Module, not the description else the match can not be made between osCommerce and osFinancials. You can find the exact module name via your Internet browser. See for an example (http://.......&module=ot_shipping) the picture above.
First you have to think about which Order Total Modules you want to specify in your administration.
If you don't specify a Order Total Module, you will not have the specifications of this module copied into your osFinancials order lines and thus also not on your specific General Ledger account. In this chapter you will see several examples on how this works.
The modules SubTotal, Tax and Total are generated automatically in osFinancials. You don't have to configure these in your osCommerce Plugin.
If desired you can configure the Order Total Module Shipping (exact name is “ot_shipping”).
In osCommerce (see picture above) this module is configured, as an example, to calculate zero shipping cost when the order total is above $50,00.
When a customer orders a stock item below $50,00 a shipping fee will be calculated in osCommerce from the shipping module “Flat Rate” at a fixed price of $5,00. Let's have a look into the osCommerce generated order in the picture below
The customer has ordered:
Product | Price |
1x Blade Runner – Directors Cut, (price is without VAT). | $30,00 |
Shipping Method is Flat Rate | $5,00 |
Tax 19% calculated | $6,65 |
Order Total: | $41,65 |
Let's see what happens when we import this order in osFinancials without configuring ot_shipping in the osCommerce Plugin.
Notice that the Item Price is imported as Order Total price. No invoice line for the shipping cost is made, but the shipping cost are added to the Item price.
The Invoice Total is the same as in osCommerce.
Let's configure the Order Total module Shipping (ot_shipping) on the osCommerce Plugin and look again to the results of the booking.
Open the osCommerce Plugin and select the “Tax Mapping” tab.
Right mouse click on the Order Total description on the Tax Mapping tab to start set up the Shipping functionality. A sub menu appears, select “Insert”. On the right you can map your osFinancials General Ledger Accounts to the appreciated fields like you did by the Default Tax before.
Attention: Service Items (like shipping) and Cost of Sales and Stock Account field. For Service Items in most cases the same General Ledger Account is chosen as Sales Account, Cost of Sales Account as well as for Stock Account. If you do have physical stock for a service item you need to choose different Cost of Sales and Stock account than your Sales Account.
Select the General Ledger Accounts corresponding your needs. If you have a specific General Ledger Account for gathering the shipping cost, you can define it on the Sales account.
In the field “Type” you have the choice for DISCOUNT or PRODUCT. With type DISCOUNT the calculated amount will deduct from the invoice. With type PRODUCT Shipping (ot_shipping) will be added as a stock item in osFinancials and Shipping (ot_shipping) will appear as a detail line in your invoice.
Only stock items can be added on a invoice line, so probably you will choose type PRODUCT. In field “Order Total exact text” you fill in the exact name of the order total module, in the example “ot_shipping”
In the field “Order Total description” you can fill in a free of choice name that will be copied as description in your osFinancials invoice order line and your stock item if chosen for type PRODUCT.
In field “Is Exclusive Price” you have to define if the price of your Order Total Module that you are configuring, is a price inclusive or exclusive VAT. You can check this in your osCommerce module setup.
Lets have a look again to the import of the osCommerce order in osFinancials with the settings from the picture above. To be able to do that, you need to import the order again.
The invoice result will be different compared with the first example. The values are now correct. The correct item price is at the item line and the shipping cost appear as a separated line on the invoice.
Do now the configuration for the rest of your installed and active osCommerce Order Total Modules like Low Order Fee and others.
Standard window
Synchronizing Orders, Customers and Stock Items.
You have the Plugin configured and ready to import your osCommerce data. When your Plugin is started the following screen will be displayed.
Note1: Before downloading the first time the orders from osCommerce into osFinancials, make sure you take a small range of orders and check if these are correct as expected. If not, go back to your configuration. Downloaded orders can be deleted and re imported in osFinancials as long they have the status Unposted.
Note2: Stock items downloaded and created the first time will transfer your osCommerce stocklevel only once to osFinancials. From there osFinancials will update the stocklevel by means of purchase- and sales orders! osCommerce will never update your osFinancials stocklevel. You are able to upload your dynamic stocklevel from osFinancials to osCommerce (current stocklevel - sales + purchase)
Note3: Synchronizing of the countries in your downloaded customers data is done from the osCommerce country table to the osFinancials country tabel. Please make sure these names are in Sync so the country can be imported and matched correctly.
Field | Description |
Date From | Orders created on or after the “Date From” will be downloaded from osCommerce to osFinancials. All previously fetched orders (thus falling before the Date From) will be updated to the current status if changed in osCommerce. The Date From field will be updated to the latest download date-time if this function is set on in the Extra options tab. |
Date To | Orders created on or before the “Date To” will be downloaded from osCommerce to osFinancials. This field will be updated to your current computer date-time, but can be changed manually. |
Button Sync/Download Orders | Will import your osCommerce orders, including Customer data and General Stock Item data. It will not import your current osCommerce Stock Item Level!
The osFinancials stock Item level will decrease based on the sold quantity on your invoice order lines! |
Button Sync/Download Customers only | Will only download/sync your Customers data. |
Button Sync/Download Stock Items only | Will import/sync all your General Stock Item data. (not stock level). This is very useful if you have added new Stock Items in osCommerce and want to import them on forehand in osFinancials. Then you can add already your stock item level and reporting groups, etc. |
Button Upload osF Stock Level to osC | Will upload your current osFinancials Stock Item level into osCommerce. |
Ordernumber | Insert on this field the osCommerce order number, in our example 2. The leading zero's are not needed.
Will work together with the buttons “Show Order” and “Process Order”. |
button Show Order | With this action the osCommerce order will be shown in a separated window. This could be very handy if you are into a setup stage of the plugin to see if all invoice lines are imported correctly. Note! No data is stored. |
button Process Order | With this action the selected osCommerce order will be inserted/updated in osFinancials. |
Run every XXX second | Fill in the update interval in seconds.
This field works together with the Start Auto Update button. |
Button Start Auto Update | Starts the sync/download your orders with the specified interval.
This functionality is very handy when using osFinancials in multi-user environments. In this case everyone is up to date about the orders in osCommerce, without having to login in osCommerce itself. |
Synchronizing Stock Item Level
With a “Upload osF Stock Level to osC” you will upload your “up-to-date” osFinancials Stock Item Level into osCommerce. When you use the osCommerce functionality to display your current stock, it will show the correct amount. All it needs is an up to data Stock Item Level in osFinancials.
When the goods of a purchase order are delivered and checked at your storage, the purchase order will be processed in osFinancials and your stock level will increase accordingly. When a osCommerce customer order is downloaded and processed, your stock level will decrease based on the invoice line quantity.
TIP: Do an upload of your stock item level only after downloading and processing your customers orders and purchase orders to have the correct stock level in osCommerce.
File defaults and configuration storage
All your defaults and configuration data is stored in directory c:\osFinancials\books\your_administration under:
- 1OSC_default.dfm where your defaults are stored
- OSCommerce.ini where the connection settings to your web store are stored.
Securing your connection
You have now configured the osCommerce Plugin and downloaded your data in osFinancials. Now it is time to secure your connection settings more and have a closer look at the possibilities Internet users have to manipulate your osCommerce data.
Go to your web store server in directory /catalog/admin/includes/ and open the file osfinancialsphp4.php. Here are 2 section where you can put restrictions on the connection.
Have a look to the first section from line 28 till line 36
// ==================================================================== //
// If you want ip checking (just to be sure) uncomment one or more off
// the following line(s) and change the ip numbers to those you want to allow
// else checking is off. //
// $ipallowed[] = '192.168.1.2';
// $ipallowed[] = '82.161.104.114';
// ====================================================================
At line 34 change the ip address to the Internet ip address from where your osFinancials is connecting to your osCommerce web store. Delete the double slashes in front of the line else it is considered plain text.
If you are connecting from more then one ip address change the second line as well and eventually create new lines for other ip addresses that have to connect to the web store as well.
Only the mentioned ip addressen are able to connect to the osfinancialsphp4.php file. In combination with the login for your admin section this will double your security.
From line 43 till line 55 you will find the second section listed below.
// Allowed actions. If you do not like an action comment it out.
// Leave array empty for ALL actions allowed
$actionallowed = array();
$actionallowed[] = 'SELECT'; $actionallowed[] = 'UPDATE'; $actionallowed[] = 'INSERT'; $actionallowed[] = 'DELETE'; $actionallowed[] = 'CREATE'; $actionallowed[] = 'ALTER'; $actionallowed[] = 'SHOW'; $actionallowed[] = 'REPLACE'; //$actionallowed[] = 'DROP';
The Plugin is standard able to do all database actions except a DROP table.
The last line “//$actionallowed[] = 'DROP';” is commented with a double slash resulting in not processing this line and thus no DROP table functionality is available.
You are able to restrict more allowed database actions by commenting more lines with a double slash. Keep in mind that the more you restrict here the less functionality of the Plugin you can use. If e.g. UPDATE is commented out, you will not be able to upload your stock item level from osFinancials to osCommerce anymore.
Note! Every user that has direct access to your osfinancialsphp4.php file via the Internet browser, can access your database via; http://localhost/catalog/admin/includes/osfinancialsphp4.php?sql= [your query]
Appendix 1 - osFinancials with Plugin in a Network
osFinancials can be installed on a network share on your server. This can be a Samba server running on Linux or a Windows server.
osFinancials on a Samba-Linux share
If you have a Linux server in your network, you are able to install the Firebird database directly in Linux. You can get a Firebird Linux installer at http://www.firebirdsql.org/ The installation is out of the scope of this guidebook, but there is a lot of information on the Internet on how to install the database under Linux.
The installation of osFinancials can be done from a Windows client directly into a mapped Samba share (Z:\osFinancials\ in this example). Don't install the database from the osFinancials installer in this case, but do install the database client software on your own windows computer. This will install some dll's that osFinancials need for talking to the database.
On every other windows client that needs to run osFinancials directly from the share only the database client software needs to be installed. The Samba share must be mapped to a virtual drive on your windows client. Specially in multi-user environments this setup is very handy as you have only one installed osFinancials system on the share. When updating the software you have to do this only at one place, on the network share. If you change the default settings of the osCommerce Plugin, every user has directly the changed settings available as the settings are stored on the share too.
Due to the fact that the Firebird database is installed separately from the osFinancials system, your administration books must be manually copied to the place where you have installed the database. There will be no books directory in your osFinancials installation directory, unless you have requested the installation of osFinancials to create the sample set of books. The sample set of books will not be used by your Firebird server, but can be copied to your Firebird books directory defined while installing Firebird.
In Z:\osFinancials\localfiles\ a sub directory will automatically be created with the exact name of your administration when you connect the first time to your external configured database. In here your settings and configuration data for the Plugin are stored.
To start osFinancials from a windows client execute “osFinancials.exe” on the network share in Z:\osFinancials.
osFinancials on a Windows server share
If you have a Windows server in your network or a Windows client acting as a server, login at your server with administrator rights. Install osFinancials including the Firebird database. Share the server directory c:\osFinancials with your network users.
On every other windows client that needs to run osFinancials directly from the share only the Firebird database client software needs to be installed. The windows share must be mapped to a virtual drive on your windows client.
Specially in multi-user environments this setup is very handy as you have only one installed osFinancials system on the share. When updating the software you have to do this only at one place, on the network share. If you change the default settings of the osCommerce Plugin, every user has directly the changed settings available as the settings are stored on the share too.
To start osFinancials from a windows client execute “osFinancials.exe” on the network share in Z:\osFinancials.
Note! The books are stored at their normal place on the Z:\osFinancials\books directory.
osFinancials installed on each client and only Firebird on a server
In this case install on each client the osFinancials software without the Firebird database. You need to install the Firebird database client software.
On the server install the Firebird database. You can get a Firebird Linux installer or Windows installer at http://www.firebirdsql.org/
Due to the fact that the Firebird database is installed separately from the osFinancials system, your administration books must be manually copied to the place where you have installed the database. There will be no books directory in your osFinancials installation directory, unless you have requested the installation of osFinancials to create the sample set of books. The sample set of books will not be used by your Firebird server, but can be copied to your Firebird books directory defined while installing Firebird.
In C:\osFinancials\localfiles\ a sub directory will automatically be created with the exact name of your administration when you connect the first time to your external configured database. In here your settings and configuration data for the Plugin are stored.
Disadvantage is that each client has its own installation that needs to be updated when updates comes out.
Secondly the configuration files needs to be on each machine. For each user that needs access to the osCommerce Plugin, needs to set the configuration. Different configurations files could lead to different ways of pulling data. Probably this is not desired. The files can be copied from one machine to another machine without any problem.