CounterPoint SQL and Magento ecommerce Interface

CounterPoint SQL and Magento eCommerce Interface

Requirements:

 CounterPoint SQL: 8.3.9, 8.4.2

 Magento Community Edition: 1.5.1+ (older versions are not compatible due to changes in Magento’s API)

 MagentoGo is compatible for functions that use Magento’s API. Direct database connectivity for functions such

as quantity synchronization is not available due to the restricted nature of MagentoGo  MagneticOne Store Manager for Magento: v2.3.2.458 or higher

o $199 per license (normally only need 1 license)

o $119 for 24 months of update service (this covers all new versions that are released)  Tested on SQL Server 2005, 2008, 2008R2 Express, Workgroup, Standard

 Tested on Windows XP, Windows 7 x64, Windows Server 2003, Windows Server 2008R2 x64

 Note: If using Radiant’s Watchguard Firewall you must add a custom Firewall Rule to allow TCP traffic on port

3306 for a direct connection to Magento’s MySQL database

CounterPoint Order Import Information

 Multiple Magento eCommerce Websites can be imported into multiple CounterPoint Web Stores or into the same Store

o Note: A license is required for each domain name being used

 Customers are created in CounterPoint with billing and shipping information  Exiting customers are determined by a matching email address

 State Names are automatically converted to abbreviations (ex: New York formatted to NY)  Phone numbers are formatted (ex: 5187830222 formatted to: 518-783-0222)

 Orders are created with customer bill to and ship to information  Order Deposit transactions are created

o Deposit Tickets will be assigned to an active drawer session for the eCommerce drawer specified, otherwise it will assign the tickets to drawer session 1

 Magento Order number up to 15 characters is stored with the order in CounterPoint  Orders that are cancelled in CounterPoint will also be cancelled in Magento

 Shipping Charges are imported into Point of Sale - Misc Charge 1  Shipping Method is imported for UPS, Fedex, and Flat Rate

 Any Magento Order Discounts are imported into Point of Sale - Misc Charge 5

 Tracking number that is attached to an order in CounterPoint will be uploaded back to Magento

o Only one shipping carrier can be specified: either UPS or FEDEX. If you are shipping using multiple carriers please contact us for a customization quote.

o Note: Only one tracking number is allowed to be uploaded to Magento per order

 If using Gift Cards in Magento the SKU number for the gift card must match a valid Gift Certificate Code in CounterPoint (ex: SKU – GC or GC10 or GC50)

o Note: you must be using the PRO version of CPOrderMakerMagento. Please contact us for details

 You can set this up by adding something like the following

Step-by-step: Create a Magento API user

This API user is related to a so-called API role. By default, there is neither API user nor API role, so you need

to configure both.

Logging into the Magento Admin Panel

First an API role needs to be added. Login to the Magento Admin Panel and navigate to System > Web

Services > Roles.

Create a new API role

This page lists the current API roles. If there none listed yet, click on Add New Role.

Every role needs a name. This can be anything. Just make sure it is descriptive enough for yourself.

Create a new API user

Now that the role is created, you can add a user that makes use of this role.

The strange thing here is that the user fields here seem to indicate a person or individual, while in fact we're

creating a system account of some kind. The fields First Name, Last Name and Email just need to be filled in

with any value you like.

Make sure the User Name and Api Key are secure enough and you keep track of this information. You will

need it to configure the interface

Setup CPOrderMakerMagento to import orders into CounterPoint

Specify the CounterPoint Connection and the Web Store in CounterPoint that you want orders imported into

Create a batch file called ImportMagentoOrders.bat

 Note: This batch file can be scheduled to run at your desired frequency using the Windows Task Scheduler Example Batch File Contents:

CPOrderMakerMagento.exe /url:www.fanoutfittersky.com /apiuser:username /apipass:password /filter:processing CPOrderMakerMagento.exe /url:www.fanoutfittersky.com /apiuser:username /apipass:password /filter:pending

 Username and password is from your Magento API setup

 Filter lets you pick which orders you want to import based on the Magento order status

Import Transaction Log File (XferLog.txt)

 The XferLog.txt file will be created in the same folder as the CPOrderMarkerMagento.exe  This log file will indicate import errors such as items not on file

How to Setup a Direct Linked Server Connection between your CounterPoint SQL

database and Magento’s MySql database for Updating Item Quantities and

Product Information

Note: There will need to be direct MySQL database access to the Magento database for direct updating of Inventory

Quantity and Product Information. This can be accomplished by white listing the CPSQL Server’s public IP in the firewall for the Magento Server. Please check with your Magento host to make sure they can grant this type of access. They will need to provide you with the MySQL database username and password.

Download and install on the CounterPoint SQL Server the MySQL Connector/ODBC driver that is compatible with your server

http://dev.mysql.com/downloads/connector/odbc/5.1.html

Date Source Name: MAGENTO Description: MAGENTO

TCP/IP Server: website URL (ecommerce.com) Port: 3306 (this is the default)

User: provided by Magento Host Password: provided by Magento Host

Click the Details button and check the following  Allow big result sets

Setup Linked Server in Microsoft SQL Server Management Studio

 Right Click on MSDASQL and go to Properties as seen below

**Note: if you do not see the Properties option you must install the latest SQL Server Service Pack

Make sure the following options are enabled

 Nested queries

 Level zero only

 Allow inprocess

Create a new linked server

 Select Other Data Source

o Provider – Microsoft OLE DB Provider for ODBC Drivers

o Product Name – MAGENTO

Change Server Options

 RPC – True

 RPC Out - True

Execute SQL scripts against your CounterPoint SQL Database to create all views

and stored procedures that connect both databases together

To Configure CPUpdateMagento

Save CPUpdateMagento .exe into the CounterPoint\CompanyFolder\Actions Double-click on CPUpdateMagento.exe

To schedule the update process create a batch file to call CPUpdateMagento and use Windows Task Scheduler to execute this batch file

Batch File 1: If you want to update Inventory Quantities use the following command in your batch file

 CPUpdateMagento /url:moosemtn.nexcess.net /qtys:true

Batch File 2: If you want to update Name, Description, Weight and Price use the following

 CPUpdateMagento /url:moosemtn.nexcess.net

CounterPoint eCommerce Settings

o Description o Short Description o Long Description o Additional Description 1 o Additional Description 2 o Additional Description 3  Qty Available

You can specify the Ship Via Code Mapping for UPS

**Note: The following Ship Via Code must be setup in CounterPoint – FLAT RATE

Store Manager for Magento Setup

Store Manager for Magento requires direct MySQL database connection. Most of hosting companies allow direct connections to MySQL database. However in most cases you have to request your Magento host to add your public IP to the list of IP addresses allowed to access MySQL port 3306 from outside. This option can be found under MySQL settings.

Feel free to ask your hosting support to assist you in enabling direct MySQL access; they must help you since it is trivial task.

To configure direct connection to your remote database, follow few steps:

1. Open Tools -> Preferences -> Database Connection Tab (F12)

2. Enable Direct connection to remote database check-box

3. Specify the following Remote Database Connection Settings:

 Host (Server address) - Usually it is your website name like "mywebsite.com" (do not include http://www. prefix); you may use IP

address as well. Please check your store server parameters (see local.xml, you can find it at your store /app/etc directory). Open local.xml file and you will find your database server name (host).

<host><![CDATA[mywebsite.com]]></host>

 Port - 3306 is used in most cases (please ask your hosting provider to check if the one is used).

 MySQL Version - Most of hosting companies have 4.1 or 4.0 and some of them have 5.x version. You can use Auto if you're not

 User name (login), Password and Database (name) - Specify your username, password and database name. You can find them

in local.xml file as well:

<username><![CDATA[name]]></username> //here your User Name (login) is defined <password><![CDATA[pass]]></password> //here your password is defined

<dbname><![CDATA[remote_database]]></dbname> //here your database name is defined

Also you may type database name manually or use browse button [...] to see the list of available databases and select the one you need from the drop-down.

You may type database name manually or use browse button [...] to see the list of available databases and select the one you need from the drop-down.

Tip: Use the Test Connection button to verify if all settings are correct and connection to your remote database is possible.

4. Click Ok to save this entry, close Preferences and to connect to your database, or click cancel to discard this entry.

Also you can use Database Connection wizard to setup your connection or make Database Backup/Restore using corresponding options.

If you're unable to connect to remote database, here are few key items to check.

If you got "SQL ERROR: Access denied for user 'admin'@'91.203.79.82' (using Password: YES)" notification (where 'admin' is your username and '91.203.79.82' is your IP) you have to add your IP address to the Access List and allow direct MySQL

connections. Please see an article to find out how to add your IP to the Access List.

Setup FTP or SFTP connection

This section describes how to setup FTP connection to be able to upload and preview images using Store Manager for Magento.

FTP connection is not required, you may skip it. FTP is used for images - downloading and uploading images for products, categories and manufacturers. If there is no valid FTP connection specified, you will not be able to view images and upload them to your server.

See an example of properly configured FTP below:

FTP settings:

 Server address - IP address or domain name of FTP server. Do not use any prefixes or suffixes there, just simple name or IP.  FTP user name/ FTP password - specify login information. If you do not know your access details, please contact your Hosting

Company and request details.

 Use passive mode (recommended).

 Store root directory - default directory where all product images are stored. Most likely it is your-domain-name/html directory.

Additional settings:

 Max FTP threads – (recommend setting is 5) the number of threads allowed. Can be used if your Hosting Company has any limitations on the number of threads.

 Automatically download images - has to be enabled to download images automatically to preview them in Store Manager

application.

 Cache images - has to be enabled to cache images downloaded to Store Manager.  Clear image cache - clear local images cache.

Note: FTP account (settings - login/password) used for images management must have rights to read/write appropriate directories

otherwise FTP feature may not work properly.

Import Settings Setup

Change: Select delimiter character to which will be used to separate fields data: to comma

Change: Select delimiter character to which will be used to separate categories in category tree to /

Check Import Images Check Box and specify the Local Directory with Images

Click Auto set… button under the .csv File Columns section to ensure that the fields have been mapped properly

Click Set default values to unassigned fields under the Database Fields section

Magento Tips and Tricks

Turn Manage Stock on and set Display Out of Stock Products to No if you want to automatically hide products on the website based on the quantity in stock in CounterPoint SQL.

You can do this by going to System/Configuration in Magento Then navigate to Catalog/Inventory

Setup a Cron Job to Reindex the entire database every night

Ex Script:

/usr/bin/php -f

Connection Timeout Errors when running CPUpdateMagent.exe



Run the following query against the MySQL database



show variables like 'connect_timeout'



connect_timeout should be set to a value of 300

Database Information (informational only)

The complete list of item attributes is stored on the eav_attribute table  Attribute_id = Unique Attribute Number

 Entity_type_id = this will identity the correct attribute_id = value of 4  Attribute_Code = database name of attribute

 Backend_type = references which table the attribute value is stored on

The complete image path is stored on the catalog_product_entity_media_gallery table  Entity_id = Product ID number

 Value = image path (ex: /b/bottle.jpg)

The product URL is stored on the catalog_product_entity_varchar table  Entity_id = Product ID Number

 Attribute_id = 87 (default)

 Store_ID = filter to the specific store

 Value = URL (ex: babich-sauvignon-blanc-marlborough-2009.html)