Tuesday, October 31, 2017

Update SQL database after printing report

October 31st 2017

So you know people who use alpha native DBF for their database needs are able to update the table after printing the report by using simple four lines of code and wondering if that can be done for the SQL database too.
Of course you can do it. In fact it can be done more than one way.
I am going to take one method in each post and discuss how that can be achieved.

Today I am going to show you how you can update using sql connection string and sqldatasource for the report.
For the purpose of this post the table contains a field vintage, and i am going to update that field with an arbitrary value to show the point. The report has a filter where the prompt ask for the name of the brand of the wine and prints the report then updates the vintage in the table to a year specified.  In a real world example you would print invoices to many people and at the end update the table to show date of printing and the fact the invoice was printed to set a flag to true.

So here is how it is done:
Step 1: Create a report based on the sqldatasource as usual. add any where clause and order clause as needed. Test run the report and see if all works okay. Once satisfied save the report.
Step 2: Now go to the top and select report, then report events, then onPrintInit enter the following code:

dim SHARED cn as sql::Connection
dim result as l = .f.
result = cn.Open("::Name::claretPOS")
if .not. result then
end
end if 

Then go to onPrintexit enter the following code:

dim SHARED cn as sql::Connection
if cn then
cn.Close()
end if

Then go to Reports, Section events, detail, onRecord, there enter this code:

dim t as p
dim SHARED cn as sql::Connection
dim args as sql::Arguments
t = table.current()
dim id as n
id = t.ID

dim result as l = .f.
dim sqlUpdate as c
sqlUpdate = "UPDATE inventory SET vintage = 2000 WHERE id = :newID"
args.set("newId", id)
result = cn.Execute(sqlUpdate, args)

As you can see I am showing my connection string and my table information, you need to adjust to yours.
Also I want to emphasize that you need to dim in each segment, then it will look for SHARED namespace.
And you can have your own update statement, not necessary to have the way I have, this is to show  as an example without corrupting my data.
So what happens is when the detail section is printed the id of that record is harvested and sqlUpdate statement is called and the backend is updated.  This will work every time you call this report and if your intenstion is to update then it will happen.
So here is an example:

http://www.youtube.com/watch?v=0vyh34jiWXE

Sunday, September 24, 2017

Sparkpost and AplhaFive - Fantastic Part 4 (Final)

September 24th 2017

Sparkpost and AlphaFive - Fantastic

Part 4 ( Final)

So far we have covered 
1> to create an account with Sparkpost and generate an api key for sending the emails.
2> created a separate table to hold the api key, so that we can retrieve that key with a sqlLookup command to save us from exposing the key to the outside world.
3> we created a function sparkpost_email_send function with three arguments and sent one or more emails.  Then we changed and incorporated that function in our code to send email  itself, to make it easy and not worry about publishing user defined functions.

Today is the final discussion, we will cover how to send professional looking email with images and links the body of the email. The prerequisite for the images to be sent along the email is somehow the email should get the data for the images sent.  There are few methods available and I am going to take easy method.

You can send the images as attachment, then it will show somewhere in the bottom.  That is not what we need. We need to format the email with logo and banner at the top then the body then the footer with some other banner and social network images as links. This will not be possible with that method.

You can send images as base64encoded value and the email will convert that into images when received. A cumbersome process for me, at least.

The easy method, which I will describe now and follow in the example, will be to store the image in a content delivery network(CDN), preferably hosted by you and link that image to show in the email.  Works every time except when the CDN goes down.  The advantage of CDN hosted by private provider is that it rarely goes down but they might flag you down for spamming, this is not the case when you host it yourself, but your server may go down when the internet is down or rebooting.  There is advantage and disadvantage in both systems. You decide which is better.  I am using my own hosted server to deliver the content.

Then finally, if you have time, please read documentation in alphafive for html templates and using placeholders to replace variables in that location to customize the email.

So, now that all the bases are covered, I will show you how the email is sent.  In this model, I have an dialog to fill in the from address, to address, cc, subject, item name that will bring up the name, image and description, then I will fill in the price then send that as an email.  This is only an example, you can design any way you want and as long as you incorporate what I have shown in the html template, placeholders and image delivery method, this method will work for you too.

So, I will give you the code that sends the email and show you via a screencast the working of that.
Here is the code:
First onChange event of the item will do a lookup to the back end and bring the necessary data back to the dialog;

function xb2 as c (e as p)
'debug(1)
dim cn as sql::Connection
dim args as sql::Arguments
dim result as l = .f.
dim sqlSelect as c
dim product_id as n
product_id = e.dataSubmitted.product
result = cn.Open("::Name::local_MySql")
if result then
sqlSelect = "SELECT * FROM product WHERE id = :newProduct_id"
args.set("newProduct_id",product_id)
result = cn.Execute(sqlSelect,args)
if result then
rs = cn.ResultSet
description = rs.data("description")
image_name = rs.data("image_name")
end if
cn.close()
end if
e._set.image_name.value = image_name
e._set.description.value = description
'xb2 = "javascript commands to be executed in the Browser."
end function 

Then you will fill in the price ( this can also be automated if you wish), thenwhen you press send email button this will call another function that will send the email and send the message to the browser.
Here is that code:
function xb as c (e as p)
'debug(1)
dim subject as c
dim description as c
dim to as c
dim cc as c = ""
dim list as c = ""
dim flag as l = .f.
dim msg as c
dim email_msg as c
dim image_name as c
dim price as c
dim product_id as n
dim cn as sql::Connection
dim sqlSelect as c
dim args as sql::Arguments
dim rs as sql::ResultSet
dim result as l
dim ps as p
dim pm as p
dim pp as p
cc = e.dataSubmitted.cc
select
case cc = "All"
sqlSelect = "SELECT * FROM customer2"
case cc = "Gandhi"
sqlSelect = "SELECT * FROM customer2 WHERE name = 'gandhi'"
case cc = "Papi"
sqlSelect = "SELECT * FROM customer2 WHERE name = 'papi'"
case cc = "Gandhi&Papi"
sqlSelect = "SELECT * FROM customer2 WHERE name = 'gandhi' OR name = 'papi'"
end select
result = cn.Open("::Name::local_MySql")
if result then
result = cn.Execute(sqlSelect)
if result then
rs = cn.ResultSet
flag = rs.nextRow()
while flag
list = list + rs.data("email")+","
flag = rs.nextRow()
end while
if substr(list,len(list)) = "," then
list = substr(list,1,len(list)-1)
end if
end if
end if
to = e.dataSubmitted.to
subject = e.dataSubmitted.subject
description = e.dataSubmitted.description
image_name = e.dataSubmitted.image_name
price = e.dataSubmitted.price
pp.description = description
pp.image_name = image_name
pp.price = price
email_msg = <<%str%
<!DOCTYPE html>
<html>
<head>
<meta name="generator" content="Alpha Five HTML Editor Version 11 Build 3381-4096">
<!-- must use in order to make XP Themes render -->
<meta HTTP-EQUIV="MSThemeCompatible" content="Yes" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 

<title></title>
<style>
<!--
body {
background-color:white;
}
h1 {
font-size:35px;
font-style:italic;
}

-->
</style>
</head>
<body>

<table width="90%" border="0" bordercolor="" bordercolorlight="" bordercolordark="" bgcolor="" cellpadding="0" cellspacing="0">
<tr>
<td style="background-color:#008080; text-align:center; color:#ffffff;"><h1>Nini's Wine Cellar</h1></td>
</tr>
<tr><td>&nbsp;</td></tr>
<tr>
<td><div style="text-align:center;">
<img style="width:320px;height:180px;" src="http://24.90.115.79:82/claretpos/naples_morning.jpg">
</div></td>
</tr>
<tr>
<td style="text-align:center;"><h3>Our weekly Promotions</h3> </td>
</tr>
<tr>
<td style="text-align:center;">
<table width="90%" border="0" bordercolor="" bordercolorlight="" bordercolordark="" bgcolor="" cellpadding="0" cellspacing="0">
<tr>
<td style="width:50%;"><div style="text-align:center;">
<img style="width:110px;height:110px;" src="http://24.90.115.79:82/claretpos/{image_name}">
</div></td>
<td style="width:50%; text-align:left;"> {description}<br/>{price}</td>
</tr>
</table>

</td>
</tr>
   <tr>
<td style="background-color:#008080; text-align:center; color:#ffffff; font-size:20px;" >Fine wines for less</td>
</tr>
<td style="text-align:center;">Stay Connected<br/></td>
</tr>
<tr>
<td style="background-color:#ffffff; text-align:center;">
<a href = "https://www.facebook.com/niniswinecellar" target="_blank">
<img src = "http://24.90.115.79:82/claretpos/facebook.png" style="width:22px;height:22px;border:0">
</a>
<a href = "https://www.twitter.com/niniswinecellar" target="_blank">
<img src = "http://24.90.115.79:82/claretpos/twitter.jpg" style="width:22px;height:22px;border:0">
</a>
</td>
</tr>
</table>

</body></html>
%STR%
result = email_smtp_open(ps,"smtp.sparkpostmail.com",587,"SMTP_Injection",sql_lookup("::Name::local_MySql","sparkpost_api","ID=1","api_key"),"STARTTLS")
if result then
pm.from = "admin@niniswinecellar.com"
pm.from_alias = "Govindan Gandhi"
pm.to = to
pm.bcc = list
pm.subject = subject
'pm.message = message
pm.html_message = evaluate_string(email_msg,pp)
result = email_smtp_send(pm,ps)
if result then
msg = "alert('email successfully sent');"
else
msg = "alert('there was a problem sending email, try again later');"
end if
end if
email_smtp_close(ps)
xb = msg

end function 

Some of the things to remember, always send the email to yourself and bcc to others so that the email address is hidden. Always use hidden api key to avoid people exploiting your or your client key.
While this is shown as an example, you can design your email differently and if you use these methods you can definitely send customized emails not plain simple drag as an email.

Hers is a sample email sent via Sparkpost and using the code.

Sunday, September 17, 2017

Sparkpost and Alpha Five - Fantastic

September 17th, 2017

Sparkpost and AlphaFive - Fantastic

part 3:

Okay, now that we have some working examples, we are going to send email to multiple recipients.

First things first. Email etiquette dictates that you do not give out others' email addresses without their consent.  So in order to do that NEVER send an email with multiple mail addresses in the 'to' or 'cc' address locations, they are visible to everyone. Always send the email to yourself and 'bcc' to all the recipients.  In this way the email addresses are hidden from others.

In AlphaFive when you send an email via any one of the email send functions the to, cc and bcc will take comma separated list. That is what we are going to use to send the emails via sparkpost and smtp_send function.

Secondly, you really do not need to write out a separate function and then use it to send an email.  Since it takes only about 10 lines of code you can easily add it to the email code and done with it.  So any modification you do will immediately be propagated to the server, instead of republishing all the files to upload a small change you did in the UDF.

So what we need to do today is to build a brand new dialog that will have the following controls:
Subject, a textbox, this is the subject.
Message, a textarea, this is the message we are sending.
a button labelled 'send email, to send email by calling the xbasic function and send out alert to the browser.

we are going to select all the members of the table and send out simple email using the function we created earlier.
Okay here is the code to do that:
function xb as c (e as p)
'debug(1)
dim subject as c
dim message as c
subject = e.dataSubmitted.subject
message = e.dataSubmitted.message
dim cn as sql::Connection
dim result as l
dim sqlSelect as c
dim rs as sql::ResultSet
dim list as c = ""
dim flag as l = .f.
dim msg as c
result = cn.Open("::Name::local_MySql")
if result then
sqlSelect = "SELECT * FROM customer2"
result = cn.Execute(sqlSelect)
if result then
rs = cn.ResultSet
flag = rs.nextRow()
while flag
list = list + rs.data("email")+","
flag = rs.nextRow()
end while
if substr(list,len(list)) = "," then
list = substr(list,1,len(list)-1)
end if
result = email_sparkpost_send(list,subject,message)
if result then
msg = "alert('email sent to recipents in the list');"
else
msg = "alert('there was a problem sending email, try again later');"
end if
end if
cn.close()
end if
xb = msg
end function

This will send emails to all from customer2 table with exposed email address in the 'to' column.
so we will redo the code to send with hidden email address and incorporating the email send function in the xbasic function itself.
So here is the modified code to do that:
function xb as c (e as p)
'debug(1)
dim subject as c
dim message as c
subject = e.dataSubmitted.subject
message = e.dataSubmitted.message
dim cn as sql::Connection
dim result as l
dim sqlSelect as c
dim rs as sql::ResultSet
dim list as c = ""
dim flag as l = .f.
dim msg as c
result = cn.Open("::Name::local_MySql")
if result then
sqlSelect = "SELECT * FROM customer2"
result = cn.Execute(sqlSelect)
if result then
rs = cn.ResultSet
flag = rs.nextRow()
while flag
list = list + rs.data("email")+","
flag = rs.nextRow()
end while
if substr(list,len(list)) = "," then
list = substr(list,1,len(list)-1)
end if
       dim ps as p dim pm as p result = email_smtp_open(ps,"smtp.sparkpostmail.com",587,"SMTP_Injection",sql_lookup("::Name::local_MySql","sparkpost_api","ID=1","api_key"),"STARTTLS") if result then pm.from = "administrator@niniswinecellar.com" pm.from_alias = "Govindan Gandhi" pm.to = "ggandhi344@gmail.com" p.bcc = list pm.subject = subject pm.message = message result = email_smtp_send(pm,ps) end if         email_smtp_close(ps)
                if result then
msg = "alert('email sent to recipents in the list');"
else
msg = "alert('there was a problem sending email, try again later');"
end if                
end if
cn.close()
end if
xb = msg
end function

See if this works well before we go into sending email with html markup.

The next week article will finalize with HTML markup and images on the body of the email.

Sunday, September 10, 2017

Sparkpost and Alpha Five - Fantastic

September 10th 2017

Sparkpost and Alphafive - Fantastic

part 2:

Okay, now that you have configured the sparkpost, if you have not read that please go back and take a look at part 1 on this series, now you are ready to configure alpha to work with sparkpost to send emails from alphafive.

First and foremost we are going to create a table and store the api key there, and when needed in our xbasic function we will retrieve that api key via a sql lookup.  This way the key is not exposed to unwanted elements. Create a table in your back end db, in my case it is MySql. The table is going to have only one important field and only one record.  You may, however, add more fields and able to use them in your emails as you please.  In our example the table will hold
id primary key, auto increment, integer, length 2,
api_key varchar, not null, length 50.
and you can set the table name anything you want, for example mine is, sparkpost_api. Once it is done enter the sparkpost api key in to the field and save it. Now let's check if it works okay.
Go to interactive window and type in
?sql_lookup("::Name::local_mysql","sparkpost_api","id=1","api_key")
and it will spit out the api key. For example, if your api key is "they shoot the horses, don't they?" it will print that. If that is working then well and good, you can go to the next step.  If not go back and correct any mistakes then you are set to go.

Next, we are going to test with sparkpost.  Log into sparkpost and take a look at api documents.  They give examples for various languages and SMTP_Relay. we will be using SMTP_Relay since it lot easier to integrate with alpha and takes only few lines of code. Take note of host, port, user_name and password.  we will see if we can sign in to sparkpost using smtp_open method from alpha.
So again in your interactive window type in

dim ps as p
dim pm as p
?email_smtp_open(ps, "smtp.sparkpostmail.com", 587, "SMTP_Injection", sql_lookup("::Name::local_MySql", "sparkpost_api", "ID=1", "api_key"), "STARTTLS")

it should come out .t. If it does you are good to go, if not go back and correct any mistakes and re do till you succeed.
Next, we will create a simple xbasic function and save that function. Go to regular control panel and click on the code tab and select new and function. Name the function anything you like, mine is email_sparkpost_send. The result is logical and the parameters are to: character, subject: character, message: character. When written the function should look like this:

FUNCTION email_sparkpost_send AS L (to AS C, subject AS C, message AS C )
dim result as l
dim ps as p
dim pm as p
result = email_smtp_open(ps,"smtp.sparkpostmail.com",587,"SMTP_Injection",sql_lookup("::Name::local_MySql","sparkpost_api","ID=1","api_key"),"STARTTLS")
if result then
pm.from = "you@yourdomain.com"
pm.from_alias = "Your Name"
pm.to = to
pm.subject = subject
pm.message = message
result = email_smtp_send(pm,ps)
end if
email_smtp_close(ps)
email_sparkpost_send = result
END FUNCTION

Now we are going to test this with a dialog component and see if all works as expected.
Create a dialog with three controls: 1> to text, 2> subject text 3> message text-area and add a button > send email. On the onClick event call an xbasic function to cal this email_sparkpost_send.
See if the message is sent and an alert appears. It does for me.
If all goes well you have sent an email with sparkpost and alphafive, isn't that wonderful?
Congratulations.

Next we will see how to send multiple emails at one shot. That's for next week.

Sunday, September 3, 2017

Sparkpost and Alpha Five - Fantastic

Date: September 3rd 2017.

Sparkpost and Alpha Five... 

Part One:
Okay, now that you have done lots of work in Alpha Five and you have many clients under your belt, you are thinking - "Hey how about mass email marketing.. sounds like its about time."
Yes it's about time.
I am assuming you have done text messaging via Twilio earlier and now ready for email stuff.

The first thing you need to do is to sign up with an email service provider.  There are few out there, but this is about Sparkpost.
So I am going to deal with that only.
Go to www.sparkpost.com and check it out. You can try for free or get started for free. I did get started for free.  They give you about 25000 emails per day, for me that is lots of emails. Once you get the hang of it then you can upgrade anytime you want. So, I would say sign up for free account. Nothing fancy, no money. Just free as in free.

Once you get signed up then you can send an email via their sandbox account but that is only good for 5 emails, pittance, because you need more than that to complete all testing.  So what can you do.
Well, as far as sparkpost is concerned from address a sending domain. So go to your dashboard and create a new shiny domain.  They need to verify you hold that account before you can send email via that address. When you create the domain it says unverified. In order to verify you will need do either of these two steps.  You can verify with SMTP relay, meaning they will send an email to postmaster or abuse @ your domain.com. So you should have one of those set in your website.  I had postmaster@....com and the email came to it and verified to start sending.  Or you can copy paste the DNS setting which according to them is better, but I did not lose anything by the email method. So do the way you like, but at the end your domain ( that is the from address ) needs to be verified.
Once verified, that account will say verified, ready for sending. Now you are halfway thru with sparkpost.

Next you need to generate an api key. 
Go to your dashboard and scroll down, it will indicate the progress. You have created sending domain and verified.  Next click on the manage api keys and generate a new api key. Now here is the important part. Once you close that window you cannot get that key back. so copy and paste it in at least two places with some kind of name that you can recognize and recall the api key..

Now you are almost there in sparkpost.
There are several ways to send emails with sparkpost and they give you example codes.
The example codes include cURL, NodeJs and C# among other languages. You can also send via SMTP_Relay, meaning using standard smtp open, send and close methods. I am going to talk about SMTP_Relay since it is the easiest of all. There is nothing fancy no need to work hard and just few lines of code you can send one or many emails as you want. But eventually I will try to master javascript way of doing things, but that is for down the road.

Next, we need to work on alpha to complete the rest of the stuff so we can send one or many emails we need.
That is for next week...
Stay tuned.