Practical Programming

At one point or another you’ll be faced with figuring out how to accept secure payments from your website. As with most things, once you know the basics it isn’t quite as daunting as you might expect it to be. But before you get started remember that the things you will need like a Merchant Account (from a bank), an Online Payment Gateway (such as Authorize.net), and SSL Certificates cost money. Prices vary, but the average cost of a merchant account runs around $100.00 for setup, and the Online Gateway will typically have a minimum monthly fee of $30.00 and a cost per transaction anywhere from 10 to 20 cents. SSL Certificate prices are all over the board depending upon who you go with but a good price is $200.00 a year. (Expect to pay more if you go with a name-brand company like VeriSign or want extra features.) Obviously these prices are subject to change quite radically (and vary widely depending upon who you decide to do business with) so you will need to shop around and decide what fits best for you. Make no mistake you should know exactly whos services you will be using and exactly how much it is going to cost BEFORE ever starting development on an e-commerce website. period.

Although there are three methods of implementation, we will be looking at the Advanced Integration Method (aim). Other options are available if you wish to have orders take place on a third party site, or enter transactions in manually. I recommend the Advanced Integration Method  because it allows you to incorporate the POS directly inside your own website or application; providing a professional stream lined interface where all the transactions take place in the background with no interruption to the user. 

For those of you who prefer to RTFM, take a look at The AIM Guide PDF.

The basic process of communicating with a payment gateway is simple. You send in a request for a POS and it responds back with the result. That result will contain sale deatils, for example, wether or not the credit card was accepted, and if not the reason why. In our case we will be sending our payment request via post data to:

• secure.authorize.net/gateway/transact.dll (for production)
• test.authorize.net/gateway/transact.dll (for testing)

In upcoming articles I will walk you through the basic steps using Coldfusion:

Note:

Although this article covers the defacto authorize.net implementation (which by the way works with almost every online payment gateway since they are usually resellers of authorize.net), you may wish to take a look at Google Checkout. As the service is relatively new, there are some current promotions and discounts in place. I haven’t looked into the product heavily but it looks fairly solid and the idea of not having the liability of holding credit card numbers, etc…. can take a lot of weight off your shoulders.

In my next article I will cover tying in shipping services through UPS or FEDEX to round out the e-commerce experience. Please feel free to ask any questions before hand if you can’t wait.

Tip:

If you don’t already have a good traffic base coming to your website, don’t go spending the money and using the time to build a payment infrastrcuture. New companies often make the mistake of spending money up front assuming “if we build it they will come”… unfortunately they quickly learn the hard way this is far from the truth. 

If you have already managed to establish a good traffic base or have marketing material ready to roll out take the time to double check your work. It only takes one bad experience for a user to never come back… work all the kinks out, do user studies, and make sure your hardware and software can support a sudden influx of customers without going down.

Advanced Stuff ecommerce, merchant account, payment gateway

Calculating the distance between zip codes is not nearly as difficult as you might expect. To get started you will need two things:

1. A zip code database that contains longitude and latitude coordindates
2. A formula for measuring the distance between one point and another on a circle (in this case our circle is the circumference of earth)
    

You can obtain a free zip code databse from Popular Data.com, or you may wish to try the US Census Beurau. Google (and a few other companies with mapping software) also has GEO Code/Data available but make sure you check the license agreement carefully.

Once you have all your data imported into your preferred database and you have a form setup to accept a Starting and Ending Zip you need to integrate the formula into your program. Enter the Haversine formula to the rescue.

Here is a SQL implementation that works fantastic for me:

CREATE function [dbo].[geoDistance] (@lat1 decimal(9,6), @lon1 decimal(9,6), @lat2 decimal(9,6), @lon2 decimal(9,6))
returns float
as
begin
     declare @result float
     declare @r int
     declare @kmpm float
     declare @lat1R float
     declare @lat2R float
     set @kmpm = 0.621371192
     set @r = 6371
     set @lat1R = radians(@lat1)
     set @lat2R = radians(@lat2)
     set @result = acos(sin(@lat1R)*
                        sin(@lat2R)+cos(@lat1R)*
                        cos(@lat2R)*cos(radians(@lon2-@lon1)))*
                        @r*@kmpm
     if @result is null set @result = 0
     return @result
end

Here are some links to code in various other languages:

*It is worth mentioning that the earth is not a perfect circle, so you should expect an inaccuracy somewhere around 2% depending upon where you are on the globe. For those who need more accuracy (about 3mm) check out the Vicenty’s Formula (which is based on the ellipsoidal model of earth).

Advanced Stuff measuring distance, zip code

Microsoft’s excellent implementation of XML in SQL 2005 allows you to insert multiple rows with only one insert statement. Here’s how to insert many rows without calling insert many times. In this example we will have three tables in our database. They are: PRODUCTS, PRODUCTS_COLLECTIONS, PRODUCTS_COLLECTIONS_JOIN. PRODUCTS is our main table which contains our products; a product can belong to one or many collections. Therefore, we have PRODUCTS_COLLECTIONS_JOIN to bind a product to a collection via their id’s.

Pros vs. Cons

1. PRO – Only need to connect to your database once, to execute the procedure.
2. PRO – We are using XML support built into SQL 2005.
3. PRO – Keep things neat. There is only one procedure to manage which joins these tables.
4. CON – You are passing more data with the one connection (the XML). Whether this matters to you will depend on many factors dependent on your setup.

How?

SQL 2005 supports xPath strings, go here for a quick tutorial. The first statement in this procedure deletes all joins in PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id and the collection_id is NOT found in the XML passed. Next the procedure inserts into PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id, and where a collection_id is in the XML, and where a join with this product_id and collection_id does not already exist.

create procedure [spProductsCollectionsJoin] @id int, @collections xml
as
declare @deleted bit
set @deleted = 0
delete from [products_collections_join]
where
	[product_id] = @id
	and [collection_id] not in(
            select x.item.value('@id','int') as id
            from @collections.nodes('/collections/item') as x(item))
insert into [products_collections_join]([product_id],[collection_id])
	select
		[products].[id],
		[products_collections].[id]
	from [products]
	inner join [products_collections] on [products_collections].[id] in (
                   select x.item.value('@id','int') as id
                   from @collections.nodes('/collections/item') as x(item))
	left join [products_collections_join] on
                  ([products_collections_join].[collection_id] =
                   [products_collections].[id]
                   and [products_collections_join].[product_id] = @id)
	where
		[products].[id] = @id
		and [products_collections_join].[collection_id] is null
		and [products_collections].[deleted] = @deleted

To execute the join we need to create our XML to pass to the procedure:

declare @product_id int
declare @collections xml
set @product_id = 1
set @collections = '
<collections>
<item id="1"/>
<item id="2"/>
<item id="3"/>
</collections>
'
exec spProductsCollectionsJoin @product_id, @collections

After executing the procedure there will be three new rows in PRODUCTS_COLLECTIONS_JOIN which correspond to the 3 new item nodes in the XML.

Further Reading

http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

http://www.w3.org/TR/xpath

http://www.zvon.org/xxl/XPathTutorial/Output/examples.html

Source

SQL Script to create products table.
SQL Script to create products_collections table.
SQL Script to create products_collections_join table.
SQL Script to create spProductsCollectionsJoin procedure.
SQL Script to test everything.

Until next time, dream in digital.

Advanced Stuff, sql sql, xml