I will describe another way to achieve the task in the previous post. This is assuming that you want to package the logic to meet the objective with as little dependencies as possible. One way to do this is to just rely on source data as it refreshes each day and handles all the steps from transforming to a pivot table to sending out the daily mail. This could be written as an executable in C# and deployed on a server to be invoked as a scheduled job. Since the logic is self-contained, it provides a single point of maintenance.
1) Read or fill a data set directly with a webclient request to the tfs server or the database. If there is access to the database, a stored procedure to pivot the data can be authored. Alternatively, there could be an excel layer available via excel apis that generates the pivot table and saves it on a worksheet which can be opened. The pivot table has to be marked for refresh on each opening. If the pivot table could alone be published from the workbook to a web page, the web page could be read to take the contents as html. This way we could skip step 2.
2) If the data is not already in the pivoted format, the data can be pivoted in the .Net layer. Here is the code to transform the data to a pivot
var input = new DataTable("Item-Priority");
input.Columns.Add("ID", typeof(int));
input.Columns.Add("Owner", typeof(string));
input.Columns.Add("Priority", typeof(int));
input.Rows.Add(new object[] { 1111, "abc", 1 });
input.Rows.Add(new object[] { 2222, "def", 2 });
input.Rows.Add(new object[] { 3333, "def", 2 });
input.Rows.Add(new object[] { 4444, "jkl", 3 });
var pivot = new DataTable();
var dict = new Dictionary<string, int>();
pivot.Columns.Add("owner", typeof(string));
int index = 0;
foreach (var r in input.Rows)
{
var row = r as DataRow;
var key = int.Parse(row.ItemArray[0].ToString());
var owner = row.ItemArray[1].ToString();
var pri = int.Parse(row.ItemArray[2].ToString());
if (!pivot.Columns.Contains(row.ItemArray[2].ToString()))
pivot.Columns.Add(row.ItemArray[2].ToString(), typeof(int));
if (dict.ContainsKey(owner) == false)
{
dict.Add(owner, index);
pivot.Rows.Add(new object[] { owner });
index++;
}
}
foreach (var r in input.Rows)
{
var row = r as DataRow;
var key = int.Parse(row.ItemArray[0].ToString());
var owner = row.ItemArray[1].ToString();
var pri = int.Parse(row.ItemArray[2].ToString());
int count = 0;
int.TryParse(input.Rows[dict[owner]].ItemArray[2].ToString(), out count);
int old = 0;
int.TryParse(pivot.Rows[dict[owner]][pri.ToString()].ToString(), out old);
pivot.Rows[dict[owner]][pri.ToString()] = old + 1;
pivot.Rows[dict[owner]].AcceptChanges();
if (pivot.HasErrors == true) throw new Exception();
pivot.AcceptChanges();
}
3) Finally, we can prepare the html with the insertions for the pivot table and send it out by mail as follows:
var client = new SmtpClient("your_smtp_server");
var from = new MailAddress("your_email_address");
var to = new MailAddress("your_recipient_address");
var message = new MailMessage();
message.Body = "your_prepared_html";
message.Subject = "your_title";
message.IsBodyHtml = true;
client.Send(message);
