We have a requirement that we need to send the sales summary of the product on the respective sellers.
For the demo we are using the AdventureWorksDW
Database.
We have product sales information.
|
SELECT pc.productcategorykey, pc.englishproductcategoryname
AS Productcategoryname, Sum(f.unitprice) AS TotalUnitPrice, Sum(f.extendedamount) AS TotalExtendedAmount, Sum(f.unitpricediscountpct) AS TotalUnitPriceDiscountPct, Sum(f.discountamount) AS TotalDiscountAmount, Sum(f.productstandardcost) AS TotalProductStandardCost, Sum(f.totalproductcost) AS TotalProductCost, Sum(f.taxamt)
AS TotalTaxAmt, Sum(f.freight)
AS TotalFreight, Sum(f.salesamount) AS Totalsales FROM dimproductcategory pc INNER JOIN dimproductsubcategory
Psc
ON PC.productcategorykey = psc.productcategorykey INNER JOIN dimproduct p
ON p.productsubcategorykey = pc.productcategorykey INNER JOIN factinternetsales f
ON f.productkey = p.productkey GROUP BY pc.productcategorykey, pc.englishproductcategoryname |
We need to send the mail to respective user.
We have user table.
We are
mapping the product category key with user id and sending that sales summary.
Mail Subject is : Product name sales Report .
In mail greetings
·
If there is one email id then
o
Hello user name
·
If there are two email id then
o
Hello User name1 & User name2
·
If more than 2 then
o
Hello Team
We are
writing the query to get the email id with the product sales summery details.
|
SELECT Product_details.productcategorykey, Product_details.productcategoryname, Product_details.totalunitprice, Product_details.totalextendedamount, Product_details.totalunitpricediscountpct, Product_details.totaldiscountamount, Product_details.totalproductstandardcost, Product_details.totalproductcost, Product_details.totaltaxamt, Product_details.totalfreight, Product_details.totalsales, email.username, email.useremailid FROM (SELECT pc.productcategorykey, pc.englishproductcategoryname AS Productcategoryname, Sum(f.unitprice) AS TotalUnitPrice, Sum(f.extendedamount) AS TotalExtendedAmount, Sum(f.unitpricediscountpct) AS TotalUnitPriceDiscountPct, Sum(f.discountamount) AS TotalDiscountAmount, Sum(f.productstandardcost) AS TotalProductStandardCost, Sum(f.totalproductcost) AS TotalProductCost, Sum(f.taxamt) AS TotalTaxAmt, Sum(f.freight) AS TotalFreight, Sum(f.salesamount) AS Totalsales FROM
dimproductcategory pc INNER JOIN dimproductsubcategory Psc ON PC.productcategorykey = psc.productcategorykey INNER JOIN dimproduct p ON p.productsubcategorykey = pc.productcategorykey INNER JOIN factinternetsales f ON f.productkey = p.productkey GROUP BY pc.productcategorykey, pc.englishproductcategoryname) Product_details LEFT JOIN (SELECT t1.productcategorykey AS
ProductCategoryKey, Stuff((SELECT ',' + t.username
FROM usertbl AS t
WHERE
t.productcategorykey = t1.productcategorykey
ORDER
BY t.productcategorykey
FOR xml path('')), 1, 1, '') AS Username, Stuff((SELECT ',' + t.useremailid
FROM usertbl AS t WHERE t.productcategorykey = t1.productcategorykey
ORDER
BY t.productcategorykey
FOR xml path('')), 1, 1, '') AS Useremailid FROM usertbl t1 GROUP BY t1.productcategorykey) email ON email.productcategorykey = Product_details.productcategorykey |
Now we are
designing the package.
Taking
Execute SQL Task and running the above script and storing this result in the
object variable.
Storing this
result in the object variable.
Now taking
the Script task to send the mail.
We are wring
the below C# script to send the mail.
|
public void Main() { DataTable dt =
new DataTable(); var oleDa = new OleDbDataAdapter(); oleDa.Fill(dt,
Dts.Variables["User::ProductDeatils"].Value); string smtpAddress = "smtp.gmail.com"; int portNumber = 587; bool enableSSL = true; string emailFromAddress = "***********@gmail.com"; //Sender Email Address string password = "*********"; //Sender Password foreach (DataRow row in dt.Rows) { try { string emailToAddress =
row.ItemArray[12].ToString(); //Receiver Email Address string subject =
row.ItemArray[1].ToString()+ " Sales Report"; string body = " "; body =
"Hello "; string[] words =
row.ItemArray[11].ToString().Split(','); int i = words.Length; if (i == 1) {
body = body + row.ItemArray[11].ToString()+","; } else if (i == 2) {
body = body + words[0].ToString() + " and " + words[1].ToString() + ","; } else {
body = body + " Team,"; } body =
body + Environment.NewLine +Environment.NewLine+ row.ItemArray[1].ToString()
+ " Report deatil as below."; using (MailMessage mail = new MailMessage()) {
mail.From = new
MailAddress(emailFromAddress);
mail.To.Add(emailToAddress);
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = true;
mail.Body += " <html>";
mail.Body += "<body>"; mail.Body += "<table border=2>";
mail.Body += "<tr>";
mail.Body += "<td
bgcolor=#00FFFF>Product category name</td><td
bgcolor=#00FFFF>Total Unit Price</td>"+
"<td bgcolor =#00FFFF>Total Extended
Amount</td><td bgcolor=#00FFFF>Total Unit Price Discount
Pct</td>" +
"<td bgcolor =#00FFFF>Total Discount
Amount</td><td bgcolor=#00FFFF>Total Product Standard
Cost</td>" +
"<td bgcolor =#00FFFF>Total Product
Cost</td><td bgcolor=#00FFFF>Total TaxAmt</td>"+
"<td bgcolor =#00FFFF>Total
Freight</td><td bgcolor=#00FFFF>Tota lsales</td>";
mail.Body += "</tr>"; mail.Body
+= "<tr>";
mail.Body += "<td>" + row.ItemArray[1].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[2].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[3].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[4].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[5].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[6].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[7].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[8].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[9].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[10].ToString() + "</td>";
mail.Body += "</tr>";
mail.Body += "</table>";
mail.Body += "<br/><br/>";
mail.Body += "Thank and
Regards,<br/>";
mail.Body += "DWH Load Team
<br/>";
mail.Body += "XYZ</body>";
mail.Body += "</html>"; using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber)) {
smtp.Credentials = new
NetworkCredential(emailFromAddress, password); smtp.EnableSsl = enableSSL;
smtp.Send(mail); } } } catch (Exception ex) { Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure; } } Dts.TaskResult
= (int)ScriptResults.Success; } |
Running the package now.
Clothing Sales Report.
Other user mail box
Other user mail
Other user
Get the expected result.
