Log in Register

Login to your account

Username *
Password *
Remember Me

Create an account

Fields marked with an asterisk (*) are required.
Name *
Username *
Password *
Verify password *
Email *
Verify email *
Captcha *

osCommerce Plugin User Guide

OsFinancials osCommerce Plugin User Guide

Inhoud

Introduction

This OsFinancials osCommerce Plugin User Guide was previously for very short time available via the webstore. I have decided to give this guide-book to the community so everybody can make use of it.

You are invited to update this manual if you find parts that can have a better explantion or additional information so we can make it better together.

Jan Verlaan

Context

The osCommerce Plugin is delivered free of charge with osFinancials1.X and is licensed under the same Open Source license (GNU-GPL) as osFinancials.

The osCommerce Plugin is a very powerful tool to automate your customer-, order- and stock- administration by downloading all your necessary osCommerce data directly into osFinancials. 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.

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. 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 osCommerce by an upload to your osCommerce web store.

This Guide-book will let you get acquainted with the osCommerce Plugin of osFinancials1.X, and therefore does not pretend to be complete. It describes in chronological order the setup and the required parameter settings for the osCommerce Plugin delivered within osFinancials1.X.

Points of Attention

For the purpose of this document the osCommerce installation is prepared on the following Internet address: http://localhost/catalog/

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:\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 osCommerce Plugin of osFinancials 1.X; download data into osFinancials, influence the data by means of parameter settings and upload/sync your stock item level between osFinancials and osCommerce.

Global overview

First have a look at the picture below. It gives a graphical overview of the osCommerce Plugin within osFinancials1.X. It shows all elements and the data flow between those elements.

The osCommerce 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.

Afbeelding:1osC osF plugin overview.jpg

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 not download your stock level from osCommerce into osFinancials1.X. The Plugin will upload your stock level from osFinancials1.X into osCommerce if manually activated.

The osCommerce Plugin process flow:

  • Insert your stock item data in osCommerce needed for selling products
  • A new customer registers himself in osCommerce
  • 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 osCommerce

You can also download your stock items directly after inserting them in osCommerce 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\. Afbeelding:Osc_osf2.jpg

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


and the plugin will work now

You can proceed with the following sections. Use in the examples where needed your specific path to the files created in this chapter.

osCommerce Plugin settings

Starting the Plugin and setting the connection parameters

The osCommerce Plugin is included into the installation of osFinancials and directly available to you. The start button is situated on the right in the action bar on top of your screen.

Afbeelding:Osc osf3.jpg

Press the OSC-Plugin start button and the window displayed below will open on your screen.

Afbeelding:Osc osf4.jpg

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:

Afbeelding:Osc osf5.jpg

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.

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:

 

Afbeelding:Osc osf6.jpg

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.

Afbeelding:Osc osf7.jpg

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.

Afbeelding:Osc osf8.jpg

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:

Afbeelding:Osc osf3.jpg

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.

Afbeelding:Osc osf9.jpg

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.

Afbeelding:Osc osf10.jpg

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”.

Afbeelding:Osc osf11.jpg

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.

Afbeelding:Osc osf12.jpg

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.

Afbeelding:Osc osf13.jpg

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

Afbeelding:Osc osf14.jpg

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.

Afbeelding:Osc_osf15.jpg

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.

Afbeelding:Osc_osf16.jpg

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.

Afbeelding:Osc_osf17.jpg

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.

Afbeelding:Osc osf18.jpg

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.

Afbeelding:Osc_osf19.jpg

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.

Afbeelding:Osc_osf20.jpg

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.