Email Tracking System, Help

[jmiller79]

I need a little help not only on the backend part of this project, but the logistics of the project. First of all; my project is an order entry system. Several different companies log into the website to check status of their orders. I have this part working perfect. The login, the reports, Etc…. but now I came up with an idea that instead of these companies logging in everyday to check status of their orders. If they could click a button to get an email every time the status changed. That would be the greatest thing. What I thought could happened was; When they say they want an email. That order number would save to a table. With there email address which is already located in a users information table. And then every time a page is hit or I hit a page this code would check the table and find that order and if the status has changed from when they submitted the order number they would receive an email with all the information on it. But if no change to the order number it would skip over that user and not send an email. But here is were it get a little complicated, when the order is finally done and it says closed; It will send out the email and then take the user off the list and delete him/her from the table. Any help would be great. Either some direction, code, examples, Etc… anything.

I am currently using MS SQL, ASP

Thanks for all the help in advanced.

Thank You
Joe

 

[snowboardr]

Hi Joe,

Well the way I imagine this setup is this..

Table setup:

NotifyMeTable --> userId | orderId

Users --> uid | uemail

If you change something in your backend.. such as status of order... then just check the whos order it is (get their id from the order table) then check the NotifyMeTable, but you are going to want to do a join of the tables instead of storing the email again... such as

Select NotifyMeTable.userID, Users.uid, Users.uemail FROM NotifyMeTable, Users WHERE Users.uid=NotifyMeTable.userId

'SOME DB CONNECTION CODE ETC..

If NOT rs.eof then
'# SEND EMAIL 
  Else
'# No email notification
End If

Now to delete them I would do one of the following...
do a join of the order ID and the notifiymetable order id and then delete where order status is closed... ? Or you could have it delete the notify me when you change the status of the order to complete..

its all up to you.


Hope that helps,
Jason

if any questions, dont hesitate to ask.













www.sitesd.com
ASP WEB DEVELOPMENT

 

[newmediaguy]

Hi

I would personally use a scheduled task and/or trigger in the database to do this.

then you can do automatic daily mails and 'on status' change database updates.

Just a thought.

Thanks




Glen
Conception | Execution