sending email based on who is selected

[chinedu]

Hello gurus, way back in February, I implemented an email program with the help of John.
The email prgogram basically checks our orders table and selects any orders that have missed submission deadline and sends an email to the clent.
This one is slightly different.
What we want to do here is to implement an accident program.
An accident happens, the individual involved in the accident calls our hotline to report the accident; accident info is logged into our system.
Upon hitting the submit button, an email is sent to 3 different people:
the supervisor of the employee involved in the accident, the safety officer and the deputy.
Each, beginning with the supervisor has 24 hours to complete a certain for.
So supervisor has 24 hours.
As soon as he forwards the form to safety officer, safety officer has 24 hours.
Once it is forwarded to deputy, he has 24 hours.
I am trying to come up the a logic that can tell when one individual has completed his form and when it is time for the other to complete his.
Basically, my email will say to the supervisor, you have 24 hours to complete your form.
My code will recognize that he has completed his form and that safety now has it and has 24 hours and so on.
Each will be giving reminders before his/her 24 hours elapses and will continue to receive it until he/she completes his form.
Can someone please give me ideas that will help me with this?
thanks in advance

 

[chinedu]

This is a repost.
I have been shopping this question around now for sometime hoping to get some guru to help but to no avail.
Please allow me to try here again and hope for the best.

Here is the problem.
I am trying to write a program that sends an email to 3 people whenever an accident occurs in their workplace.
The process states that when an accident occurs, the person involved in the accident, assuming the person is alright, calls into the company's accident hotline to report the accident.
The hotline receptionist logs the info into the system and submits to the database.
Once this is done, an email is sent to these 3 people to advice them of the accident and what actions they are required to take.
The first recepient of the email is the supervisor of the employee involved in the accident. This supervisor has 24 hours to complete a form and pass the info to the next person called the safety_officer who has 24 hours to complete a form and finally passes that form to the deputy of that company who has 24 hours to approve the form(s) passed to him/her.
I have taken 3 approaches to implementing this.
First approach is to write an asp code that collects all info from the caller and submits this info to the accident_Info table.
This works fine.
The second approach (this is the approach I am having problem with, I have posted some code snips) is to now write a program that goes to the accident_Info table and selects those records whose status is "Open" and inserts them into an email table.
The 3rd approach is to write an email program that now selects the records from email table and sends email to these 3 peopl.
I am struggling with the logic of determining when to send email to supervisor, when to send it to safety_officer, or when to send the email to the deputy.
There is a field in the database called status that defaults to "Open".
Right now, I just have a code that says once the accident information is logged into our database, go to the database, select those records where status is "Open" and insert them into the email table.
My email program will then send general emails to these 3 advising them that an accident has occured, each has 24 hours to complete their respective forms beginning with the supervisor. Once supervisor completes his/her form and passes it along to safety, safety_officer has 24 hours to complete his/her form; same with deputy.
That is the verbiage I am currently using.
It works this way but that is not the way the client wants it.
The client wants to send an email message addressed to Supervisor letting him/her know that he/she has 24 hours to complete his/paperwork.
Once completed, it needs to sent to the safety_officer.
Safety_Officer and Deputy will be copied on the email.
Sending the email to Supervisor and copying Safety_Officer and Deputy won't be a problem.
What is stumping me is finding to let the code know when one individual has completed his/her part of the form and then getting to code to send the email to next person in line and copying the other ones.
Now We can update the status to Complete once supervisor completes form but how do I reset it to Open for the next person in line?
If we can do this, then my code will always select from accident_Info table where status is Open but it needs to know who to send it to and which ones to copy.
If I can get help in coding this part correctly, we all will be home free.

Here is the code snip I mentioned earlier:
/*get records from accident_Info th
*/at have not been processed.
SELECT Caller, Supervisor,Deputy,Org, CaseNumber,
DateOfAccident, TimeOfAccident,TimeOfAccidentAMPM, emailAdd, status
FROM AccidentInfo
WHERE status = '1'


/*Insert selected record into email table*/

set @mailID = (SELECT max(mailID)+ 1 from notification)

INSERT into notification (mailid, Supervisor,
Deputy, caseNumber, mailContent, sender, AccidentDate, AccidentTime,
AccidentTimeAMPM,status, email,adminName)
VALUES (
@mailid,
@Supervisor,
@Deputy,
@caseNumber,
'This is a computer generated email message.
Please do NOT use the REPLY button above to respond to this email.
Dear ' + @Supervisor +'
Please be advised that an accident that occured today, '+cast(getdate() as varchar)+' with tracking number
'+ @caseNumber+ cast(getdate() as varchar) +' requires your action within 24 hours
effective from the receipt of this email.
If you wish to view the details of the accident, please click here:

http://localhost/safety/orderreport.asp?trackingNumber='+@caseNumber+'

Regards,
The Accident Tracking System',
'ATS Authorization Notification ' + @caseNumber,
@AccidentDate,
@AccidentTime,
@AccidentTimeAMPM,
case @status
when '1' then 'Open' else 'Complete'
end,
@email,
'IS Administrator'
)
I need help in adding a logic that determines the last to take action and when that action ends and the next person begins etc.
I truly appreciate your help on this.

 

[karsh44]

Just an idea, this should do what you want, but maybe not the way you wanted it? The way I understand it, you have one master form which needs to be sent to the supervisor, security officer and deputy, in succession, to have them fill out their parts. It seems to me that instead of trying to code it so that the email goes from one to the next with the form in it, you could make 3 forms using asp, one for each of the 3 users. Then, when the hotline person or whoever sends the email to the supervisor, they attach a link to the supervisor form asp page. The supervisor fills it out and submits it. On submission, the information is filled into the appropriate fields in a database, and an email sent to the safety officer, which contains a link to the safety officer's asp page. The officer fills in his page, and on submission, the email is sent to the deputy with a link to the deputy page. The deputy can then fill out his/her part, and on submission, the form is done. The other 2 can be informed each time the file is submitted by using a second mailer object on each asp page. This may not be the prettiest solution, but it should do what you want, I think. If you can do what you've already mentioned, the code should be pretty straightforward. Please reply if you use this, or if you want another idea