I'm hoping for insight on the best way to approach my goals for having reusable user-defined xbasic functions and complications with session variables and a tabbed ui. The functionality I'm trying to deliver is to have the ability to basically build an email message with attachments that can be sent to a list of contacts (grid with a checkbox column). I have about 10 of these grids that allow the user to query their various data in many ways to come up with the special combination of email recipients. I have a button on the bottom of each grid that allows the user to build the email by opening a UX Component with a file upload control and two textarea controls (with ckeditor): one for the email body and one for the signature. These email fields come from list controls over the repository table that holds user-defined email content. After the user has made these three selections, I set session variables to hold their choices. Then there is a second button on the grid that will send the emails to all who are selected in the grid with the checkbox checked. This is an ajax callback to a user-defined xbasic function that I created that uses dotnet to actually send the emails with the attachments and appropriate email text.
The problem I'm running into arises if the user opens one of the queries in a tab, sets up the email but doesn't send yet. They then open a different query and possibly click on the button that actually sends the emails. My session variables won't necessarily be what they want to construct the emails. Is there a way to have these two universal user-defined functions do the work I want them to do for all the queries but use some other kind of variable to store their selections in so that it doesn't matter if they have multiple instances of the ux component open (or if they are in mid-process of multiple queries and emails)? I don't even think this scenario is likely to happen, but obviously I want to provide a solution so it can't happen. I'll include the code here from my second function that does the work and maybe it will be more clear:
function processdotnet as c (e as p)
dim updcount as N
dim validresult as l
dim vfilter as c = ""
dim checksCount as n
dim planarray as c = ""
dim oktosend as l
dim subj as C
dim body as C
dim isHtml as L = .T. '.T. if body is HTML, otherwise .F.
dim FileAttachment as C = ""
'*******dim CC as C = "terri@xxxxxxx"
dim Bcc as C = ""
'If you want multiple recipients, CCs, or BCCs, you need to write FOR or FOR EACH loops
' to add them
'parameters that are often static for a web site
'******dim sender as C = "[email protected]"
dim sender as C = "xxxxxxx.com"
dim EmailServer as C = "xxxxxxx" 'put your server here
dim EmailPort as N = 25 'usually 25 (plain) or 465 (SSL/TLS). 587 for GMail TLS.
dim isSSL as L = .F. '.T. if SSL/TLS, .F. if not
dim EmailUser as C = "xxxx"
dim EmailPassword as C = "xxxxx"
dim recipient as C = ""
if val(e.checkboxRows.countChecked) > 0 then
'First make sure they selected attachments and then build an array of attachments
'---------- reformulate array from session variables
dim session.arrayString as c
dim session.arrayFormat as c
dim arr[0] as p
if session.arrayFormat <> "" .and. session.arrayString <> ""
arr.initialize_properties(session.arrayFormat,session.arrayString)
else
processdotnet = "alert('Error, No attachments have been selected.');"
end
end if
dim cn as sql::Connection
DIM cr as sql::CallResult
DIM updcr as sql::CallResult
dim basers as sql::ResultSet
dim args as SQL::Arguments
dim cnflag as l
dim updflag as l
dim allflag as l
dim sql as c = ""
dim updsql as c = ""
dim vemail as c
cnflag = cn.open("::Name::cnNAISQL")
cr = cn.CallResult
if cnflag = .f. then
processdotnet = "alert('Error, Connecting to database for generating emails. Error was: "+ cr.text + "');"
end
end if
on error goto neterrors2
'create a filter without arguments
checksCount = val(e.checkboxRows.countChecked)
for i = 1 to checksCount
vFilter = vFilter + "autoid = " + eval("e.checkedRows.key" + i +"[1]") + "" +crlf()
next i
vFilter = alltrim(vFilter)
vFilter = strtran(vFilter, crlf()," OR ")
sql = <<%longstring%
SELECT planinfo.AUTOID, planinfo.PLANID, planinfo.COMPANYID, planinfo.PLANNAME, planinfo.PYEMDY, planinfo.CLIENTDOCFOLDER, company.EMAIL
FROM planinfo planinfo
INNER JOIN company company
ON planinfo.COMPANYID = company.COMPANYID
WHERE
%longstring%
sql = sql + " " + vfilter
allflag = cn.Execute(sql)
cr = cn.CallResult
if allflag = .f. then
processdotnet = "alert('Error, Selecting plans. Error was: "+ cr.text + "');"
cn.Close()
end
end if
basers = cn.ResultSet
baseflag=basers.NextRow()
hourglass_cursor(.t.)
updcount = 0
'Cycle through the selected plans.
while baseflag
'create email with attachments
oktosend = .T.
'use the company email address to determine the recipient
vemail=alltrim(basers.data("EMAIL"))
recipient = ""
if vemail <> "" then
recipient = alltrim(basers.data("EMAIL"))
else
oktosend = .F.
planarray = planarray + "<br>The email address for the company for " + alltrim(basers.data("planname")) + " is blank. "
end if
'Is this a valid email address? If not, don't try to send and add the error to the error string
validresult = a5_is_email_valid(recipient)
if validresult = .F. then
oktosend = .F.
planarray = planarray + "<br>The email address for " + alltrim(basers.data("planname")) + " is not a valid format: " + alltrim(recipient)+ "."
end if
if oktosend = .T. then
dim msg as System::Net::Mail::MailMessage = new System::Net::Mail::MailMessage()
msg.To.Add(recipient)
msg.From = new System::Net::Mail::MailAddress(sender)
msg.Subject = "Email for " + alltrim(basers.data("planname"))
html_msg = <<%html%
<html>
<head> </head>
<body>{session.varemailmessage}{session.varemailsig}
</body>
</html>
%html%
msg.Body = evaluate_string(html_msg)
' msg.Body = session.varemailmessage + session.varemailsig
msg.BodyEncoding = System::Text::Encoding::UTF8
msg.IsBodyHtml = isHtml
msg.Priority = System::Net::Mail::MailPriority::Normal
dim addrCC as System::Net::Mail::MailAddress = new System::Net::Mail::MailAddress(CC)
msg.CC.Add(addrCC)
'dim addrBcc as System::Net::Mail::MailAddress = new System::Net::Mail::MailAddress(Bcc)
'msg.Bcc.Add(addrBcc)
'Loop through the attachments array and add the attachments to the email
dim count as n
count = arr.size()
dim i as n
for i = 1 to count ' loop through filearray'
msg.Attachments.Add(new System::Net::Mail::Attachment(arr[i].name))
next
dim MailClient as System::Net::Mail::SmtpClient = \
new System::Net::Mail::SmtpClient(EmailServer,EmailPort)
MailClient.EnableSsl = isSSL
MailClient.UseDefaultCredentials = .F.
MailClient.Credentials = new System::Net::NetworkCredential(EmailUser, EmailPassword)
MailClient.Send(msg) 'this does the actual sending of email
msg.Dispose() 'clean up
updcount = updcount + 1
else
planarray = planarray + "<br>Email was not sent to " + alltrim(basers.data("planname")) + "."
end if
'when there are no more records in the resultset, executing nextrow will return .f.
'so flag will be .f. and the while loop will end
baseflag = basers.nextRow()
end while
cn.close()
session.arrayString = ""
session.arrayFormat = ""
session.varemailmessage=""
session.varemailsig=""
processdotnet = "A5.msgBox.show('Results:','<div style=\'padding: 20px; width:600px;\' >Number of emails generated: " + js_escape(updcount) + " <br> " + js_escape(planarray) + "</div>','o');"
else
processdotnet = "A5.msgBox.show('Results','No plans were selected.','o');"
end if
end
neterrors2:
dim err_msg as c
line = error_line_number_get()
script = error_script_get()
err_msg = error_text_get(error_code_get())+ ". An error occurred at line "+ alltrim(str(line,4,0)) + " in script: " + script
processdotnet = "A5.msgBox.show('Error:','"+js_escape(err_msg)+"','o');"
end function
The problem I'm running into arises if the user opens one of the queries in a tab, sets up the email but doesn't send yet. They then open a different query and possibly click on the button that actually sends the emails. My session variables won't necessarily be what they want to construct the emails. Is there a way to have these two universal user-defined functions do the work I want them to do for all the queries but use some other kind of variable to store their selections in so that it doesn't matter if they have multiple instances of the ux component open (or if they are in mid-process of multiple queries and emails)? I don't even think this scenario is likely to happen, but obviously I want to provide a solution so it can't happen. I'll include the code here from my second function that does the work and maybe it will be more clear:
function processdotnet as c (e as p)
dim updcount as N
dim validresult as l
dim vfilter as c = ""
dim checksCount as n
dim planarray as c = ""
dim oktosend as l
dim subj as C
dim body as C
dim isHtml as L = .T. '.T. if body is HTML, otherwise .F.
dim FileAttachment as C = ""
'*******dim CC as C = "terri@xxxxxxx"
dim Bcc as C = ""
'If you want multiple recipients, CCs, or BCCs, you need to write FOR or FOR EACH loops
' to add them
'parameters that are often static for a web site
'******dim sender as C = "[email protected]"
dim sender as C = "xxxxxxx.com"
dim EmailServer as C = "xxxxxxx" 'put your server here
dim EmailPort as N = 25 'usually 25 (plain) or 465 (SSL/TLS). 587 for GMail TLS.
dim isSSL as L = .F. '.T. if SSL/TLS, .F. if not
dim EmailUser as C = "xxxx"
dim EmailPassword as C = "xxxxx"
dim recipient as C = ""
if val(e.checkboxRows.countChecked) > 0 then
'First make sure they selected attachments and then build an array of attachments
'---------- reformulate array from session variables
dim session.arrayString as c
dim session.arrayFormat as c
dim arr[0] as p
if session.arrayFormat <> "" .and. session.arrayString <> ""
arr.initialize_properties(session.arrayFormat,session.arrayString)
else
processdotnet = "alert('Error, No attachments have been selected.');"
end
end if
dim cn as sql::Connection
DIM cr as sql::CallResult
DIM updcr as sql::CallResult
dim basers as sql::ResultSet
dim args as SQL::Arguments
dim cnflag as l
dim updflag as l
dim allflag as l
dim sql as c = ""
dim updsql as c = ""
dim vemail as c
cnflag = cn.open("::Name::cnNAISQL")
cr = cn.CallResult
if cnflag = .f. then
processdotnet = "alert('Error, Connecting to database for generating emails. Error was: "+ cr.text + "');"
end
end if
on error goto neterrors2
'create a filter without arguments
checksCount = val(e.checkboxRows.countChecked)
for i = 1 to checksCount
vFilter = vFilter + "autoid = " + eval("e.checkedRows.key" + i +"[1]") + "" +crlf()
next i
vFilter = alltrim(vFilter)
vFilter = strtran(vFilter, crlf()," OR ")
sql = <<%longstring%
SELECT planinfo.AUTOID, planinfo.PLANID, planinfo.COMPANYID, planinfo.PLANNAME, planinfo.PYEMDY, planinfo.CLIENTDOCFOLDER, company.EMAIL
FROM planinfo planinfo
INNER JOIN company company
ON planinfo.COMPANYID = company.COMPANYID
WHERE
%longstring%
sql = sql + " " + vfilter
allflag = cn.Execute(sql)
cr = cn.CallResult
if allflag = .f. then
processdotnet = "alert('Error, Selecting plans. Error was: "+ cr.text + "');"
cn.Close()
end
end if
basers = cn.ResultSet
baseflag=basers.NextRow()
hourglass_cursor(.t.)
updcount = 0
'Cycle through the selected plans.
while baseflag
'create email with attachments
oktosend = .T.
'use the company email address to determine the recipient
vemail=alltrim(basers.data("EMAIL"))
recipient = ""
if vemail <> "" then
recipient = alltrim(basers.data("EMAIL"))
else
oktosend = .F.
planarray = planarray + "<br>The email address for the company for " + alltrim(basers.data("planname")) + " is blank. "
end if
'Is this a valid email address? If not, don't try to send and add the error to the error string
validresult = a5_is_email_valid(recipient)
if validresult = .F. then
oktosend = .F.
planarray = planarray + "<br>The email address for " + alltrim(basers.data("planname")) + " is not a valid format: " + alltrim(recipient)+ "."
end if
if oktosend = .T. then
dim msg as System::Net::Mail::MailMessage = new System::Net::Mail::MailMessage()
msg.To.Add(recipient)
msg.From = new System::Net::Mail::MailAddress(sender)
msg.Subject = "Email for " + alltrim(basers.data("planname"))
html_msg = <<%html%
<html>
<head> </head>
<body>{session.varemailmessage}{session.varemailsig}
</body>
</html>
%html%
msg.Body = evaluate_string(html_msg)
' msg.Body = session.varemailmessage + session.varemailsig
msg.BodyEncoding = System::Text::Encoding::UTF8
msg.IsBodyHtml = isHtml
msg.Priority = System::Net::Mail::MailPriority::Normal
dim addrCC as System::Net::Mail::MailAddress = new System::Net::Mail::MailAddress(CC)
msg.CC.Add(addrCC)
'dim addrBcc as System::Net::Mail::MailAddress = new System::Net::Mail::MailAddress(Bcc)
'msg.Bcc.Add(addrBcc)
'Loop through the attachments array and add the attachments to the email
dim count as n
count = arr.size()
dim i as n
for i = 1 to count ' loop through filearray'
msg.Attachments.Add(new System::Net::Mail::Attachment(arr[i].name))
next
dim MailClient as System::Net::Mail::SmtpClient = \
new System::Net::Mail::SmtpClient(EmailServer,EmailPort)
MailClient.EnableSsl = isSSL
MailClient.UseDefaultCredentials = .F.
MailClient.Credentials = new System::Net::NetworkCredential(EmailUser, EmailPassword)
MailClient.Send(msg) 'this does the actual sending of email
msg.Dispose() 'clean up
updcount = updcount + 1
else
planarray = planarray + "<br>Email was not sent to " + alltrim(basers.data("planname")) + "."
end if
'when there are no more records in the resultset, executing nextrow will return .f.
'so flag will be .f. and the while loop will end
baseflag = basers.nextRow()
end while
cn.close()
session.arrayString = ""
session.arrayFormat = ""
session.varemailmessage=""
session.varemailsig=""
processdotnet = "A5.msgBox.show('Results:','<div style=\'padding: 20px; width:600px;\' >Number of emails generated: " + js_escape(updcount) + " <br> " + js_escape(planarray) + "</div>','o');"
else
processdotnet = "A5.msgBox.show('Results','No plans were selected.','o');"
end if
end
neterrors2:
dim err_msg as c
line = error_line_number_get()
script = error_script_get()
err_msg = error_text_get(error_code_get())+ ". An error occurred at line "+ alltrim(str(line,4,0)) + " in script: " + script
processdotnet = "A5.msgBox.show('Error:','"+js_escape(err_msg)+"','o');"
end function
Comment