JJ
|
| Posted: 03/13/2003, 12:26 PM |
|
Hi there,
I'm using the taskman application (ASP/VBScript) and am trying to use the update / insert record to notify the "assigned to" that a new task or update has been assigned to them. Is there anyone out there who has managed to do something similar?
Thanks!
|
|
|
 |
GreggB
|
| Posted: 03/14/2003, 4:04 AM |
|
Here's an example of working code I use for the "After Update" in the TaskRecord form. I'm using IIS which has SMTP included so I use the CDONTS method. This code demostrats the use of sending the email in HTML format(what a paing to format). I'm sure you could tighten up the code as this was my first shot at it and I have not had to go back and clean it up.
DIM email_to, email_from, email_cc, dSQL, sSQL, date_resolved, tmp
DIM assigned_by_email, assigned_to_email, owner_email
DIM assigned_by_name, assigned_to_name, owner_name, user_email
DIM g_task_id, owner_employee_id, status_id, g_status, href1, g_priority
DIM g_project_name, task_desc_html, resolution_html
'' ------------------Misc. Lookup---------------------
g_project_name=CCDLookUp("project_name", "projects", "project_id=" & CCToSQL(tasks.project_id.Value, "Integer"), DBisoperations)
g_task_id = CCGetParam("task_id", Empty)
g_priority = CCDLookUp("priority_desc", "priorities", "priority_id=" & CCToSQL(tasks.priority_id.Value,"Integer"), DBisoperations)
g_status = CCDLookUp("status", "statuses", "status_ids=" & CCToSQL(tasks.status_id.Value,"Integer"), DBisoperations)
owner_employee_id=CCDLookUp("employee_id", "projects", "project_id=" & CCToSQL(tasks.project_id.Value, "Integer"), DBisoperations)
date_resolved = tasks.date_resolved.Value
'' ------------------Email Lookup---------------------
assigned_by_email=CCDLookUp("email", "employees", "employee_id=" & CCToSQL(tasks.assigned_by.Value, "Integer"), DBisoperations)
assigned_to_email=CCDLookUp("email", "employees", "employee_id=" & CCToSQL(tasks.assigned_to.Value, "Integer"), DBisoperations)
owner_email=CCDLookUp("email", "employees", "employee_id=" & CCToSQL(owner_employee_id,"Integer"), DBisoperations)
assigned_to_email=assigned_to_email & ", " & owner_email
user_email=CCDLookUp("email", "employees", "employee_id=" & CCToSQL(CCGetUserID,"Integer"), DBisoperations)
'' ------------------Name Lookup----------------------
assigned_by_name=CCDLookUp("employee_name", "employees", "employee_id=" & CCToSQL(tasks.assigned_by.Value, "Integer"), DBisoperations)
assigned_to_name=CCDLookUp("employee_name", "employees", "employee_id=" & CCToSQL(tasks.assigned_to.Value, "Integer"), DBisoperations)
owner_name=CCDLookUp("employee_name", "employees", "employee_id=" & CCToSQL(owner_employee_id, "Integer"), DBisoperations)
'' ------------------Memo to HTML, Kind of-------------
task_desc_html = replace(tasks.task_desc.Value,chr(13),"<br>")
task_desc_html = replace(task_desc_html,chr(32)," ")
resolution_html = replace(tasks.resolution.Value,chr(13),"<br>")
resolution_html = replace(resolution_html,chr(32)," ")
''-------------- IF Status = Closed Update Date RESOLVED --------------------
If tasks.status_id.Value = 3 and tasks.date_resolved.Value = "" then
date_resolved = Date()
sSQL = "Update tasks Set date_resolved=" & CCToSQL(date_resolved, "Date")& " Where task_id=" & g_task_id
tmp = CCExecSQL(sSQL, DBisoperations, "ShowError")
End if
''-------------- IF Date_resolved update Status to Closed --------------------
If tasks.status_id.Value <> 3 and tasks.date_resolved.Value >"" then
g_status="Closed"
dSQL = "Update tasks Set status_id=3 Where task_id=" & g_task_id
tmp = CCExecSQL(dSQL, DBisoperations, "ShowError")
End if
'' ---------------- Email Variables -------------------------
email_to = assigned_by_email
' assigned_by_email
''' "gregg_beard@learningtree.com"
email_from = user_email
' user_email
email_cc = assigned_to_email & ", " & tasks.email_cc.Value
' assigned_to_email
href1 = " "
href1 = href1 & "<a href="
href1 = href1 & """http://isoperations/projectmanCCS/Task_Record.asp?task_id="
href1 = href1 & g_task_id
href1 = href1 & " ""> "
href1 = href1 & "<b>Task Name:</b></a>"
With CreateObject("CDONTS.NewMail")
.From = email_from
.To = email_to
.Cc = email_cc
.Subject = "UPDATE " & g_status & " Project Task: " & tasks.task_name.Value
.Body = "<html><body>" _
& "<head>" _
& "<meta http-equiv=""Content-Language"" content=""en-us"">" _
& "<meta http-equiv=""Content-Type"" content=""text/html; charset=windows-1252"">" _
& "<title>Project Management UPDATE</title>" _
& "</head>" _
& "<div align=""center"">"_
& "<center>"_
& "<table style=""WIDTH: 354px; HEIGHT: 66px"" width=""354"" align=""center"" border=""0"">"_
& "<tr>"_
& "<td align=""middle"" colspan=""2""> <img id=""Image2"" height=""57"" src=""http://isoperations/projectmanCCS/images/projectmanagement.jpg"" width=""334"" name=""Image2""></td>"_
& "</tr>"_
& "</table>"_
& "</center>"_
& "</div>"_
&"<font FACE=""Arial"">" _
&"<div align=""center"">" _
& "<center>" _
& "<table border=""0"" cellpadding=""2"" cellspacing=""0"" style=""border-collapse: collapse"" bordercolor=""#111111"" width=""500"" id=""AutoNumber1"" bgcolor=""#EFF9D0"">" _
& "<tr>" _
& "<td width=""500"" colspan=""4"" align=""center"">" _
& "<font face=""Arial Black"">UPDATE</font></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""100"">" & href1 & "</td>" _
& "<td width=""400"" colspan=""3"" align=""left""><b><font FACE=""Arial"" size=""2"">" & tasks.task_name.Value & "</font></b></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""100""><font FACE=""Arial"" size=""2""><b>Project Name:</b></font></td>" _
& "<td width=""400"" colspan=""3""><font FACE=""Arial"" size=""2"">" & g_project_name & "</font></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""100""><font FACE=""Arial"" size=""2""><b>Project Owner:</b></font></td>" _
& "<td width=""400"" colspan=""3""><font FACE=""Arial"" size=""2""><b> </b>" & owner_name & "</font></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""500"" colspan=""4""> </td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""500"" colspan=""4""><font FACE=""Arial"" size=""2""><b>Task Description: </b></font></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""500"" colspan=""4""><font FACE=""Arial"" size=""2"">" & task_desc_html & "</font></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""500"" colspan=""4""> </td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""100""><font FACE=""Arial"" size=""2""><b>Assigned To:</b></font></td>" _
& "<td width=""150""><font FACE=""Arial"" size=""2"">" & assigned_to_name & "</font></td>" _
& "<td width=""100""><b><font FACE=""Arial"" size=""2"">Assigned By:</font></b></td>" _
& "<td width=""150""><font FACE=""Arial"" size=""2"">" & assigned_by_name & "</font></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""100""><font FACE=""Arial"" size=""2""><b>Date Assigned:</b></font></td>" _
& "<td width=""150""><font FACE=""Arial"" size=""2"">" & tasks.date_assigned.Value & "</font></td>" _
& "<td width=""100""><b><font FACE=""Arial"" size=""2"">Date Resolved:</font></b></td>" _
& "<td width=""150""><font FACE=""Arial"" size=""2"">" & date_resolved & "</font></td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""500"" colspan=""4""> </td>" _
& "</tr>" _
& "<tr>" _
& "<td width=""500"" colspan=""4""><font FACE=""Arial"" size=""2""><b>Resolution:</b></font></td>"_
& "</tr>" _
& "<tr>" _
& "<td width=""500"" colspan=""4""></font><font face=""Arial"" size=""2"">" & resolution_html & "</font></td>" _
& "</tr>" _
& "</table>" _
& "</center>" _
& "</div>" _
& "</font>" _
& "</body></html>"
.BodyFormat = 0
.MailFormat = 0
.Send
End With
Hope this helps
GreggB
|
|
|
|
JJ
|
| Posted: 03/19/2003, 7:00 PM |
|
Hi GreggB - thanks very much for the reply....is there any chance you'd be able to help me out setting this up..I'm really just learning..you're help is really appreciated...thanks again.
JJ
|
|
|
|
|
