FreeNetshop

FreeNetshop is an on-line e-commerce, sales and order-tracking software suite with a customer interface modeled on the popular “shopping cart” theme. It is designed to run on any UNIX system using any NCSA-compatible web server, including Apache. FreeNetshop is, of course, free and is licensed under the GPL.

FreeNetshop (FNS) works with the freely available mSQL relational database and is capable of running multiple independent copies on one server using a single instance of msqld. This makes it ideal for web presence providers and ISPs who serve multiple customers/domains from each system in their server pool. FNS has been tested on version 1.0.9 and 1.0.16 of mSQL. Porting to mSQL 2 and MySQL is underway.

FNS uses the mSQL C programming API, the excellent CGIC library from Thomas Boutell, and UNIX library calls to optimize speed and minimize runtime size. Independent configuration parameters are compiled at installation to reduce lookup overhead during execution. FNS is also customizable from site to site by editing a few HTML pages.

The FreeNetshop distribution is composed of Common Gateway Interface (CGI) programs and HTML forms. FNS was designed from the ground up to allow multiple sites to be hosted on a single host machine. As such, FNS CGI programs are small and fast.

Performance of an FNS installation is primarily a function of the speed of the database (mSQL) and the web server. mSQL was chosen as the database since it has defined the performance mark for small to medium-sized relational databases and is easily obtainable. For example, an indexed lookup of one record in a table with 100,000 records took less than half a second, including web server and client response, on an AMD 586, 133MHz machine with 16MB RAM. A non-indexed search for a string in a 50-character field for 100,000 records took less than three seconds on the same machine.

FreeNetshop version 1.3 features:

A view of the FreeNetshop table schemas reveals that the database has not been fully normalized. This decision was made to minimize table lookups at specific points in the application CGI programs. Of course, the effect of this decision is to sacrifice some small amount of storage space to achieve faster response. Figure 1 shows the table relationships. It can be seen that customers may have one-to-many and one-to-one relations with Cart and Orders. Orders, in turn, can have one-to-many or one-to-one relations with Lineitems. Lineitems and Cart are one-to-one related to Products. Products (i.e., a product record) will have a many-to-one relation with Lineitems and Cart, since one product number will be in multiple shopping carts and order line items.

Figure 1. Table Relationships in FNS

The FNS distribution is divided into customer-level programs and administration-level programs. Customer programs are located in the site cgi-bin directory. Administration programs are located in admin/cgi-bin for each site. The admin directory is protected with the htaccess facility included in most NCSA-compatible web servers, including Apache.

Customer-level programs are linked into the HTML pages and each other so that the process of adding items to the shopping cart and placing an order is completely menu-driven. Nevertheless, it may be useful for the hosting ISP or administrator to understand the ordering process as it relates to the cgi programs. The customer-level programs are search.cgi, addcart.cgi, viewcart.cgi, upcart.cgi, preorder.cgi, ordercalc.cgi, order.cgi, clearcust.cgi and account.cgi. Figure 2 shows the customer ordering sequence.

Figure 2. Customer Ordering Sequence

search.cgi provides the means for the customer to enter keywords to search the products database table. search.cgi prints out a listing of all matching records (products) with a short description and a link to product HTML pages. In addition, search.cgi pulls in header and footer HTML files so that the dynamically generated results page appears within a consistent-look site interface (refer to the CUSTOMIZE file included with the distribution for details).

addcart.cgi adds a product to the customer's shopping cart. Internally, addcart.cgi sends a unique cookie to the customer's computer and adds the appropriate product number to the Cart database table. In addition to the standard product number, price, etc., a sequence number is added to the Cart table. The sequence number is based on the date and time the item was added. This provides a means for the site administrator to remove old entries from the Cart table. There is no limit to the amount of items a customer may add to his shopping cart.

viewcart.cgi pulls up a listing that shows all product items in the customer's shopping cart. Additionally, quantities are shown in a form field so that the customer can change quantities or remove products from his shopping cart.

upcart.cgi performs the actual update of product quantity in the Cart table, then regenerates the shopping cart listing page with the updated quantity shown. Links are provided in viewcart.cgi and upcart.cgi to allow the customer to proceed with placing an order.

preorder.cgi was added to FNS 1.3 when shipping costs were added for five classes of UPS. preorder.cgi takes address information from the Customer table if the customer has ordered before, and provides blank form fields if the customer is new.

ordercalc.cgi takes the shipping information and address data entered from preorder.cgi and calculates order cost and shipping. order.cgi then provides form fields for the customer to enter credit card information. If the password the customer entered into preorder.cgi is correct, then the default customer credit card information is filled into the form fields automatically. There is also a field for the customer to enter any special comments regarding his order.

order.cgi commits the order information to the proper tables. In particular, order.cgi updates the Order tables, creating a unique order number, updates the Lineitem table and removes the selected items from the Cart table. Additionally, order.cgi creates an e-mail order notification message and sends it to the site administrator. order.cgi also provides output of the order to the customer for saving or printing. Once the administrator receives e-mail notification of the order, he can verify completeness of the order and forward a copy of it to the customer via e-mail. The e-mail order notification can also be printed by the administrator and used as a packing list.

clearcust.cgi allows the customer to delete all entries from the Customer table that correspond to his cookie-based user ID number. This is intended as an additional privacy/security feature for customers who order from public terminals/kiosks. While a thief would still need to know the customer's password to gain access to credit card information, the shipping and contact information that was last entered on a particular terminal would appear the next time the FNS site was visited if this functionality was not provided. clearcust.cgi can be invoked by the customer from the bottom of the order completion/processed page which is generated by order.cgi.

account.cgi allows the customer to change default billing and shipping information. The customer must enter his e-mail address and correct password before being allowed to access any data.

FNS includes programs which allow complete web-based site administration. Products can be added, changed and deleted from the Web via links under the products section of the administration page. FNS also allows automatic web-page generation for products in the products database table. Access to customer data is provided, and the administrator can view, update, delete or select (by name) customer information over the Web. Likewise, the ability to view order data and update it is provided, plus sales reporting by product type and date ranges. Importing and exporting data in ASCII-delimited text is also offered.

Administrative CGI programs include executables for adding, updating and deleting data from Customer, Order and Product database tables. Most of the programs are self-explanatory, but several will be discussed below where there is a particular item of importance.

addprod.cgi adds product data to the Products tables. The data is entered into an HTML form page. The form includes data fields for a user-entered unique product identification number (prodno) plus description, name and price. Availability in days or weeks plus quantity on hand are available. A field to enter search keywords is also provided. Keywords should be entered with a space between each one; it is not recommended that special characters such as quotes or apostrophes be used. The keywords entered here are used by search.cgi to determine a match. A field for entering the URL of an image is given. The image referenced by this URL will be added to the product HTML page when generated by FNS's genppage.cgi program. The default location for images is in the /images directory. Entries should follow the format:

 ../images/prodimage.jpg

where prodimage.jpg is the image file name. The actual image can be transferred with FTP to the images directory before or after the product is added. Multiple products can also share the same image. Once the data is entered, addprod.cgi will insert the information into the Product table.

genppage.cgi generates product pages automatically in HTML format. genppage.cgi reads in the header.htm and footer.htm files and writes them to an HTML file after being combined with product data from the Products tables. genppage.cgi names the output files after the product number and creates the pages in the “products” directory. Thus, genpage.cgi creates the file products/1000.htm for product number 1000. When a product is updated and the header.htm or footer.htm files are changed, the site administrator can update the product HTML page by re-running genppage.cgi for that particular product or for all products by specifying the range (by product number). The search.cgi program provides a link to the product page for each product returned from a search.

upord.cgi can be used to update basic order information. If lineitem-level changes are required, the order can be zeroed out or voided and re-entered as a new order. When all product for an order ships, the actual shipping date can then be entered into the “Completion Ship Date” field for future reference.

export.cgi allows the administrator to export the contents of the FNS tables to text files. Running export.cgi will create the following files: dataprod.csv, datacust.csv, dataord.csv, dataline.csv, datacart.csv, datapack.csv and datahold.csv. The names of the files correspond to the contents; for instance, dataprod.csv contains all data from the Products tables. The files are created in the admin directory. Files are written in ASCII comma-separated value format with text delimited by quotation marks (sometimes referred to as ASCII-delimited text). Exported data files can be imported into spreadsheets or other databases that recognize this format.

Work on import.cgi is in progress. When completed, import.cgi will read data files exported by export.cgi into database tables. If one desires to import particular tables, for example Products, the other data files can be renamed, moved or deleted from the /admin directory. If import.cgi is used to insert data files exported from spreadsheets in comma-separated value format, it is important to ensure field lengths do not exceed the lengths allowed in FNS tables (see the createtables file for table definitions), or the data values will be truncated. For instance, the customer name field (name) shows a data type of char(50). This means up to 50 characters are allowed in this field. However, the actual usable number of characters that can be entered is 48, since the delimiters must be accounted for in the total. It is also important to verify that all text fields are enclosed by quotation marks, and the field sequence fits the field order as defined in FNS. Most spreadsheets will automatically enclose text cells with quotes when exported in CSV format.

In addition to these web-based utilities, mSQL comes with several command-line tools and a terminal monitor that allows maintenance of the database from a shell account. Detailed information on these can be found in the mSQL documentation.

All in all, FreeNetshop provides a fairly simple yet scalable and robust application on which to develop e-commerce sites.