Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2003-06-25 : 22:15:54
|
| Hi John!Please forgive me for disturbing your peace.Please try to understand that I am sending you this thred out of desperation.I have been working on this for more than 2 weeks now and have posted this around many forums but none has responded.It is either because I am not asking the right question or no one truly has an answer.As a result, as a last ditch desperate effort to get help, I am pained to trouble you.We are designing accident tracking program for a client and they want a system that tracks the way accident reporting is done.They ask us to design a system whereby when an accident occurs, someone from the accident scene calls in the accident to our accident hotline.Once the hotline receptionist answers the phone, he/she logs the info into an accident_Info table and clicks the submit button to submit to the database.Once that is done, the client wants 3 things to happen.One, they want an email to be sent to 3 people.The first person is called the supervisor; he is the supervisor to the person who has an accident.He, the supervisor is sent an emailt that informs him/her that he/she has 24 hours to complete a certain paperwork.The email also says that after his paperwork is completed in 24 hours, then safety_officer has 24 hours from the completion of this paperwork to complete his/hers.Then the 3rd person is the deputy who has to sign off on everything; he/she has 24 hours from the time paperwork is passed to him/her to complete it and and then send it to risk management.I believe that using the email program that I have now,I can do this to this point.All I have to do is look for a certain flag that says pull data from accident_Info where certain conditions are true, take this data and insert it into the email_notification table.Then using sp_cdonts_sendMail, I can send email to these folks.Where I am completely stumped are:1, I need to do something that knows when supervisor has completed his/her paperwork and when safety begins.I also need to know when safety Officer has completed his/her paperwork and deputy begins his/hers.Finally, I need to know how to determine when Deputy has completed his/hers.Finally, I need to keep sending reminders to them, just like outlook until all have completed and returned their forms.I am really having problems getting started with my logic on this.I really, really need your help.I am not asking you to write it but help me with ideas on how to do it.Please, John, understand that Ihave no expectations here.Thanks again! |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-25 : 22:58:27
|
| This isn't John it is a Yak....I can't really see a problem here, you have outlined all the requirements and process and now it is time to design and implement..You will need a work tracking table..Something like..AccidentIDSupervisorCompleteDate DATETIMESafetyOfficerCompleteDate DATETIMEDeputyCompleteDate DATETIMESend Email to Supervisor, Supervisor does work, submits forms and SupervisorCompleteDate is updated, trigger fills email_notification for SafteyOfficer who does work, submits forms and SafetyOfficerCompleteDate is updated, trigger fills email_notification for Deputy who does work, submits forms and DeputyCompleteDate is updated.This can be done in a single update trigger using the UPDATE check on a column basis... Add more columns to this table for better tracking such as SupervisorEmailSentDate etc...Create a job that polls the email_notification table and send emails to run every X minutes..DavidM"SQL-3 is an abomination.." |
 |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2003-06-26 : 08:44:35
|
| thanks Yak and I apologize for using the name John.Yak, I have a similar program that works this way.When data is submitted to the accident table, then data is pulled from there into an email table that sends the email after a condition is met.I am not sure how to go about it with trigger.Will I be asking for too much if I ask for your help on this? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-06-26 : 09:14:57
|
| This doesn't sound like a job for a trigger or SQL server. by all means, store the data in SQL, but you need a front end application (i.e., in VB or ASP [web] or something similiar) that the user can use which will do the actual emailing and provide a much needed user interface.this app would allow the user to enter the info, verify the data, allow the user to choose any options needed, and then store the data in SQL. at that point, the app can also send the emails.then, another screen in the app would allow you to update the accident as time goes on, and send other emails or reminders as needed. Of course, the app can provide quick reporting as well (show all incidents marked "urgent" or "completed", etc).MS Access would also be a good veichle for the client end, but of course you can still store the data in SQL.If you don't want to provide any user interface at all and want the users to open up tables and update rows in the data, then you would use a trigger at that point to send emails. Needless to say, I don't think that would be a good way to go about it!- Jeff |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2003-06-26 : 11:37:11
|
| Jeff, the way I have done an email program before is to use ASP to do the data insertion into sql server db.The criterium for making it work for me in that case was to have a completion date.The client would select a date for submitting any orders they have processed.Once data is inserted into orders table, I then wrote a select statement that says "select certain fields from orders table where orderSubmission date has passed the completiondate.This way, if an order has a submission deadline of say, 06/22/2003 and the client has not submitted their order by that date, my select statement will pull that order and insert it into another table called notification table.Then the cdonts email program that I wrote will pick these orders and the email address of the client and send the client an email letting him know that his/her orders has missed deadline for submission.The client will continue to receive an email until they either submit their order or they cancel it.That worked well for me because only one condition has to be met for an order to be pulled from orders table and inserted into email table.The other reason that code worked well is because I only have to send email as long as that one condition remains true.I did not use trigger there.All I did was execute the email program in sql server jobs that well when an order is picked up and submitted to email program, job knows to run by sending email.What is making it hard for me in this case is determining what conditions to set before an accident info is pulled from accidentInfo table into email table.Once that is resolved, I still need to know how to make the email recognize that one person has completed his form and the other has received it.Even I try to implement your ideas which I think is a good idea, I still don't know how to go about incorporating that with an email program.Perhaps you can help me.I can write the asp code to do it, if you can help me with what steps I need to take to make this happen.For instance, step1, create an asp page for submission,step2 do this, step3 do that.If I can get you to help me, I would really appreciate it.Thank you! |
|
|
|
|
|
|
|
|
email program - I am really stumped