Web site / backoffice 'integration' 1

[Glasgow]

We have a back-office VB6 application that processes orders to calculate commissions on sales referred by ‘agents’. In the past, data relating to the agents and the orders they refer have been fed manually into the application. However, we need to modify the application to be ‘fed’ orders from an on-line shopping cart and to be ‘fed’ agent sign-up data from an on-line ASP application. It is likely that both the shopping cart and the ASP application will be hosted on the same web site.

What is the ‘best’/typical way of approaching this:

a) Keep the backoffice and on-line functionality separate and somehow feed data from the web-based database to the back-office database? Perhaps some kind of twice daily export/import.
b) Modify the backoffice application to directly interrogate the on-line database?
c) Some other mechanism?

It is likely that the site will be hosted by a budget ISP 'package' so access to on-line database(s) may be restricted.

Do shopping carts tend to have one database off-line and one on-line and a means of synchronising the two?

 

[Sheco]

Since the backoffice function already works, and since it involves people getting paid, I would be inclined to alter it as little as possible.

 

[Sheco]

ASP applications in general, and shopping carts in particuluar, are best suited to tasks that revolve around a database.

Since you already have the backend logic program, and you probably already have access to a shopping cart... I'd start by trying to find a design that minimizes modifications to these working parts... I'd see if it would be possible to write a sort of "integration bridge

 

[Glasgow]

Hi Sheco and many thanks. It would appear you are steering me towards option a) and I guess that's where my intuition takes me also.

It's how to address that "integration bridge" now, I guess.

Thinking out loud...

Ideally I need a mechanism for transporting order and agent data from the web site to the backoffice application. The web-based Agent data will be directly under my control and, while I might choose to store this in an on-line database for the convenience of the ASP application, it may prove easier to duplicate that info in a file that I know I can easily retrieve from the server (e.g. text file) via, for example, ftp. The file could then be used to import data to the application. I only suggest this approach as I may not be able to achieve direct access to the web-based database from the backoffice application if it is hosted by an ISP and it may only be feasible to generate queries via some interactive control panel which could not be automated.

Theoretically I could take a similar approach with the order data but the problem here may be that I will have no control over (nor perhaps any access to) the shopping cart data other than, perhaps, notification emails that convey details of individual orders. The customer wants to use a specific shopping cart but I have no knowledge of it at present. One option would be to scan the incoming emails to retrieve the incoming data (eek!) or, if the cart supplies some kind of order summary report, read in that report data.

Does this sound like madness?

 

[Sheco]

Does this sound like madness?

It sounds like fun.

If the backend system and the shopping cart use MS SQL Server then I'd see about doing the bridge piece using DTS... then the DB itself can manange the scheduled transfers.

 

[Glasgow]

Thanks again. I'm not sure whether retrieving and parsing the emails will be such fun but certainly a challenge. It's not exactly a safe method and I think I will do everything in my power to avoid this. I'd almost rather develop our own shopping cart to give us the necessary control.

The backend does use SQL Server but it uses SQL 2005 Express and I'm not sure if that supports DTS. I have a feeling that the shopping cart may be MySQL and I may be forced in the same direction for the web-based Agent data.

I use BULK INSERT quite a lot and that might be enough. If necessary I could probably get away with providing a 'sychronise with web site' button in the backend to allow the users to update on an ad hoc basis. However DTS is worth a look - it's a while since I've touched it.

 

[Sheco]

Bleh, I just checked the MS website and the new name for DTS is now SSIS. Unlike DTS, which was available in all versions, the SSIS is only on Professional and Enterprise. Grrr... Time to vandalize the wiki....

 

[Glasgow]

Probably the only reason they renamed it.

Thanks again for your help.

 

[Sheco]

I agree that parsing the email is a bad approach.

How about just making an ASP that uses ADO to fetch the recent changes in the shopping cart database... then use the recordset object's built in Save() method to persist it as an XML file...

See here:

http://www.w3schools.com/ado/met_rs_save.asp

Then, in your bridge app, use either FTP or ....perhaps even better... use XMLHTTP to retrieve the persisted recordset file and open it back into a recordset object.

 

[Glasgow]

Yes, Rs.Save is another good option. I have used it before but didn't think of it in this context. Thanks. It should work fine for the Agent data. I don't really know anything about XMLHTTP but time to learn I guess.

However, I think my problem may be in physically getting access to the shopping cart data. I need to find out more about the preferred shopping cart, the database it uses and whether the ISP allows me to access it. I may also be able to steer the customer towards a different shopping cart.

 

[Sheco]

If you can get a recordset saved to a file then the bridge program could use XMLHTTP to fetch the file off the web server becuase might be quite a bit easier than automating an FTP transaction or parsing email.

 

[Glasgow]

Hi again. Sorry to revive this but I am now thinking along slightly different lines...

What if I just put the back-office application's database on to the web server? Then, in theory, that application could run without modification and I could develop the customer-facing ASP code in such a way that it interacts with the database tables with which I am familiar. A single database solution.

The drawbacks as I see them would be:
a) possible performance implications because the back-office application is manipulating a remote database.
b) problems accessing the on-line database when the ISP's server is down.
c) I have to develop a shopping cart or mould an existing one around my own order and customer tables.

while the advantages:
a) A generally simpler and more elegant approach??
b) No import/export or synchronisation required.

Any comments / constructive criticism would be appreciated.