Email to address obtained from table

[J.R. Noe]

I utilize MySQL in a multi-user/company configuration. When a user logs in, a variable is created with the company ID number. When a record is saved, that variable is used for the CompID that exists in every table. Users can only see records for their own company.
What I want to be able to do is allow a user to email the person who is listed as being the manager for the company they belong to.
For example: I am an employee of Jackson Transportation and when I click a button in a UX I want an email sent to Jeff Smith, who is my manager and his email address is [email protected]. All this is obtained by looking at my company name variable, which in this case is "1201".



alphamail.jpg

[JBrown]

Re: Email to address obtained from table

There's many ways to send emails using Alpha. How far along are you and whats the issue?

[CharlesParker]

Re: Email to address obtained from table

JR you can create a field called "email to" and use that as the variable or field from which your email gets the info. Then setup that as a dropdown from your choice. Vague but help it helps you work through the logic. I can probably post my code later on tonight. What method are you using to send?

[J.R. Noe]

Re: Email to address obtained from table

Jim, it is not exactly an issue with how do I email. It is more of how do I get the email address from a secondary table.

Charles. The problem is getting the email address from a table other than the one utilized in the UX.
When the user clicks the "Email Manager" button in the UX, I want it to look up the manager name and email address in another table based on the company id of the user who is logged in (which is the user who clicked the "Email Manager" button).

[GGandhi]

Re: Email to address obtained from table

you need to write your own ajax callback to get the email address via an xbasic function. as long as company id is available you should be able to do. and then run the code to send an email via xbasic not built in function on the ux.

[GGandhi]

Re: Email to address obtained from table

if you think this will be a major requirement then you can set another variable that will hold the supervisor name and another one for the email and dispose them off when people log out.
in that way no ajax callback needed to get the email address or the name.

[J.R. Noe]

Re: Email to address obtained from table

I thought about doing that Govindan, but I am already loading variables, such as user id, company id, along with a few others, but I was hoping to keep the number of variables to a minimum. I am assuming holding variables, especially multiple variables, will create an extra load on the system. I would rather use some type of lookup to get that value when/if needed, because I would like to eventually expand on this and allow users to email a fleet manager, supply manager, quality control manager, etc, without loading a bunch of variable which may or may not get used.

[GGandhi]

Re: Email to address obtained from table

then it will be necessary to make an ajax callback to get the email address, that should be possible as long as you have the id of the company, correct?

[J.R. Noe]

Re: Email to address obtained from table

Yes, the company id is set as a variable when the user logs in.
Back to the question at hand though...how to create the callback to make it work.

[GGandhi]

Re: Email to address obtained from table

write an ajax callback that will call an xbasic routine say xb
there you define
connection string and connect to the table where the data is
execute a sql to select the name and email address where companyId variable equals the company id
then set the name to rs.data("name") email to rs.data("email")
then
construct the email using
pm and ps pointer variables and send via
email_send2(pm, ps)

[CharlesParker]

Re: Email to address obtained from table

Not sure if your following the other thread I have been pretty involved in the last few hours but it is essentially the same - getting a variable from another table via an xbasic function in an ajax callback - very similar indeed...

[CharlesParker]

Re: Email to address obtained from table

look at this one:
http://www.alphasoftware.com/alphafo...ign-a-variable

[CharlesParker]

Re: Email to address obtained from table

Here's what I do and essentially this WAS the mandrill method that I adapted to steve woods sparkpost method (not alphas, so you can ignore the comments a little) but regardless of the method the idea is the same where I have a variable set from the "email" field (currentRowDataNew.email) which could be a drop down that is a lookup to customers, but could also be employees, managers, etc. and of course the lookup is filtered by the company_id as mine is a multi-tenant application as well.

Although. it's in two seperate functions it could be all done more efficiently within one and I am certain my code is not "robust" or perfect by any stretch of the imagination but it does work...I hope you will take the idea of what I am doing with the realization that like you I am very new to this and even after a couple of years working with Alpha I am still a noob - no fault of theirs, I realize my own short comings. If it was not for the great timely advice on here I wouldn't be anywhere at all with this!
I guess someone could make the point that me posting mediocre code only serves to promote more of the same, but on the other hand I just hope it helps.
Remember also, that you probably do NOT need session variables, they could be arguments or just regular variables - state or otherwise. It would really just depend on what your after and for how long and where you need the variables to persist - I know that much, lol - execution is another matter...

function setmailto as c (e as p)
	session.mailedto = e._currentRowDataNew.EMAIL
	session.sentto = session.mailedto + " " + Date()
	'session.mailfrom = e._currentRowDataNew.REPLYEMAIL
	session.mymsg = e._currentRowDataNew.CUSTOM_MESSAGE
	session.sentto = session.mailedto + " " + Now()	

end function
	

function ReportForEmail as c (e as p)    
'debug(1)
dim key as c 
key = e.fileSessionKey
dim b as b 
'get the binary day for the report
session.GetDataFromFile(b,key)
dim size as n 
size = b.size()
if size = 0 then 
    ReportForEmail = "alert('Report was not sent.');"
    exit function
end if 

'dim mysecretkey as c = ""   'if you leave this blank the key value is read from a setting in Project Properties
dim ms as p
dim date as d
ms.send_to = session.mailedto
    if session.mailedto = "" then
        ReportForEmail = "alert('Email Address is missing or invalid!');"
        exit function
    end if
ms.from_email = "[email protected]"
ms.subject = "Wildlife Control Invoice/Estimate"
ms.message_html = "Please find the attached invoice and or estimate." + "<br>" + session.mymsg + crlf()+\ 
"<br>""<br>""<br>""<center><b>Confidentiality Notice:</b> This e-mail message, including any attachments,is for the sole use of the intended recipients." + crlf()+\
"It may contain confidential and privileged information or may be otherwise protected by law." + crlf()+\
"Any unauthorized review, use, disclosure or distribution is prohibited." + crlf()+\
"If you are not the intended recipient, please contact the sender immediately by reply e-mail and destroy all copies of the original message." + crlf()+\
"If you received this email by mistake, send complaints/concerns to [email protected]</a>This email is generated using NWCOPRO software.</center>" 


dim ms.attachmentsArray[1] as p
ms.attachmentsArray[1].name = "Wildlife Control.pdf"   'if the report was an html report, use the .html extension
ms.attachmentsArray[1].type = resolve_mime_type("pdf") 'if the report was an html report, use the 'html'
ms.attachmentsArray[1].content = base64encode(b)


dim pResult as p
pResult = wt_email_send_sparkpost("",ms)
?"RETURNED: " + pResult.body + "<hr>"
?"POSTED  : " + pResult.json + "<hr>"
?"HASERROR: " + convert_type(pResult.error,"C")
?"APIERROR: " + convert_type(pResult.error,"C") + "<hr>" '==True IF SPARKPOST RETURNED AN ERROR OF ANY KIND
if pResult.error = .f. then
	ReportForEmail = "alert('Your Invoice/Estimate was sent.');"
	save_to_file(pResult.body + crlf(2) + pResult.json,"H:\a5\bodysent.txt") 
else
	ReportForEmail = "alert('Your Invoice/Estimate was not sent because your a turd.');"
	save_to_file(pResult.body + crlf(2) + pResult.json,"H:\a5\bodynotsent.txt")
end if
end function

And no, my code does not call anyone a turd for real, lol.

Cheers~

[-Jinx-]

Re: Email to address obtained from table

I utilize MySQL in a multi-user/company configuration. When a user logs in, a variable is created with the company ID number. When a record is saved, that variable is used for the CompID that exists in every table. Users can only see records for their own company.
What I want to be able to do is allow a user to email the person who is listed as being the manager for the company they belong to.
For example: I am an employee of Jackson Transportation and when I click a button in a UX I want an email sent to Jeff Smith, who is my manager and his email address is [email protected]. All this is obtained by looking at my company name variable, which in this case is "1201".



[ATTACH=CONFIG]42081[/ATTACH]

The easiest is possibly just creating an unbound field (or a bound field if you'd like to save it to whatever table your UX is based on) that is populated with the email address by using a "lookup and fill in fields" action javascript based on the logged in users company id. You could use an on change event or some other event to trigger the action and populate the field. Then you have the email address in your UX to use.

Otherwise, Gandi's idea will work fine as well. I use both, for different reasons, throughout my application. Here's an Xbasic version:

function SendEmailToApprover as c (e as p)
debug(1)
dim reportName as C = e.reportFilename
dim vjscmd as c

dim key as c
key = e.fileSessionKey
dim b as b
session.getDataFromFile(b,key)

file.From_blob(reportName,b)

if (e.dataSubmitted.complete = "True") then '.AND. (e.datasubmitted.complete <> e.oldDataSubmitted.complete) then
	dim trnum as c = e.dataSubmitted.unique_id
	dim tooln	as c = e.dataSubmitted.toolno
	dim approver as c = e.dataSubmitted.written
	if approver <> "" then
		dim firstname as c = word(approver,1," ")
		dim lastname as c = word(approver,-1," ")
		dim cn as SQL::Connection
		dim args as SQL::Arguments
		dim SQLCommand as c
		args.Add("approver",approver)
		SQLCommand = "SELECT emailaddress FROM security.allowedusers WHERE fullname = :approver"
			if cn.Open("::Name::MVWKS") then
				if cn.Execute(SQLCommand,args) then
					dim rs as SQL::ResultSet = cn.ResultSet
					dim recipient as c = rs.Data("emailaddress")	
					dim subj as C = "M.E. Request Number "+trnum+" Is Complete"
					dim body as C = "Hello "+firstname+","+crlf()+crlf()+"A M.E. Request (Number: "+ trnum +" ) that was approved by you has been checked as completed."+crlf()+crlf()+"Please check with the Manufacturing Engineering department if you have any questions."
					dim ishtml as L = .F.
					dim CC as C = "[email protected]"
					dim CC2 as C = "[email protected]"
					dim sender as C = "[email protected]"
					dim EmailPassword as C = "mvworks"
					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 = subj
						msg.Body = body
						msg.BodyEncoding = System::Text::Encoding::UTF8
						msg.Priority = System::Net::Mail::MailPriority::Normal
					dim addrCC as System::Net::Mail::MailAddress = new System::Net::Mail::MailAddress(CC)
					dim addrCC2 as System::Net::Mail::MailAddress = new System::Net::Mail::MailAddress(CC2)
					msg.CC.Add(addrCC)
					msg.CC.Add(addrCC2)
					msg.Attachments.Add(new System::Net::Mail::Attachment(reportName))
					dim MailClient as System::Net::Mail::SmtpClient = new System::Net::Mail::SmtpClient("bung.hole.com",25)
					MailClient.EnableSsl = .F.
					MailClient.UseDefaultCredentials = .T.
					debug(1)
					MailClient.Send(msg)
					vjscmd = "setTimeout(function(){{dialog.Object}.submit()},3000);{dialog.object}.hideWaitMessage();alert('An email to the approver of this tool request has been sent telling them that it has been completed.');"
					msg.Dispose()
				end if
			args.Clear()
			cn.Close()
			end if
	else
	vjscmd = "setTimeout(function(){{dialog.Object}.submit()},3000);{dialog.object}.hideWaitMessage();alert('Tool Request has not been approved so an email can not be sent to the approver');"
	end if
else
vjscmd = "setTimeout(function(){{dialog.Object}.submit()},3000);{dialog.object}.hideWaitMessage();"
end if
file.remove(reportName)
SendEmailToApprover = vjscmd
end function

That was written a while ago so please excuse any optimization violations.

The code above also attaches a file since I'm using the "Open a Report, Letter or Layout" action to call it.