public void SendMinutesEmail(string date)
{
//sql call fetch data
List <DetailsByHour> NotesList = new List <DetailsByHour>();
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("SPU_HT_Reports_GetNotesAndDetailsForDateForSending"))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Date", date);
command.Connection = connection;
connection.Open();
SqlDataReader myReader = command.ExecuteReader();
while (myReader.Read())
{
DetailsByHour NoteItem = new DetailsByHour();
NoteItem.Notes = myReader["tvnotes"].ToString().Split('¬')[0];
NoteItem.DirectorNotes = myReader["tvnotes"].ToString().Split('¬')[1];
NoteItem.FloorNotes = myReader["tvnotes"].ToString().Split('¬')[2];
NoteItem.GoingForward = myReader["tvnotes"].ToString().Split('¬')[3];
NoteItem.ShowName = myReader["tvnotesother"].ToString().Split('¬')[0];
NoteItem.Presenter = myReader["Presenter"].ToString();
NoteItem.Producer = myReader["Producer"].ToString();
NoteItem.Sales = myReader["tvnotesother"].ToString().Split('¬')[3];
NoteItem.Director = myReader["tvnotesother"].ToString().Split('¬')[4];
NoteItem.Floor = myReader["tvnotesother"].ToString().Split('¬')[5];
NoteItem.Guest = myReader["tvnotesother"].ToString().Split('¬')[6];
NoteItem.Hour = myReader["showdatetime"].ToString().Split(' ')[1].Split(':')[0];
NotesList.Add(NoteItem);
}
connection.Close();
}
}
//format data
var emailHtml = "";
foreach (DetailsByHour note in NotesList)
{
var propertime = getpropertime(note.Hour);
emailHtml += "<table style=\"max-width:600px;\" width=\"100%\" align=\"center\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">" +
"<tbody><tr><td>" +
"<table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">" +
"<tbody><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:30px; font-weight:bold; text-transform:uppercase; color:#ffffff; background: #058ca0; background-image: url('https://www.hochanda.com/Styles/Images/strip_texture10.png'); text-align: center; padding:5px;\"><a href=\"#\" style=\"color:#FFFFFF; text-decoration:none;\">" + propertime + " - " + note.ShowName + "</a></td>" +
"</tr><tr><td> </td></tr><tr><td>" +
"<table style=\"border-color: #ccc;border: 1px;\" width=\"300\" align=\"left\" border=\"1\" cellpadding=\"5\" cellspacing=\"0\">" +
"<tbody><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px; font-weight: bold; text-transform: uppercase; background-color: #f1f1f1; width: 90px;\">Producer:</td>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.Producer + "</td>" +
"</tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px; font-weight: bold; text-transform: uppercase; background-color: #f1f1f1; width: 90px;\">Presenter:</td>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.Presenter + "</td>" +
"</tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px; font-weight: bold; text-transform: uppercase; background-color: #f1f1f1; width: 90px;\">Guest:</td>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.Guest + "</td>" +
"</tr></tbody></table>" +
"<table style=\"border-color: #ccc;border: 1px;\" width=\"300\" align=\"left\" border=\"1\" cellpadding=\"5\" cellspacing=\"0\">" +
"<tbody><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px; font-weight: bold; text-transform: uppercase; background-color: #f1f1f1; width: 90px;\">Director:</td>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.Director + "</td>" +
"</tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px; font-weight: bold; text-transform: uppercase; background-color: #f1f1f1; width: 90px;\">Floor:</td>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.Floor + "</td>" +
"</tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px; font-weight: bold; text-transform: uppercase; background-color: #f1f1f1; width: 90px;\">Sales:</td>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:16px; line-height:16px; color:#ed137d; font-weight:bold;\">" + note.Sales + "</td>" +
"</tr></tbody></table></td></tr></tbody></table>" +
"<table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">" +
"<tbody><tr><td> </td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:30px; font-weight:bold; text-transform:uppercase; color:#ed137d; background: #f1f1f1; text-align: center;\">Summary</td>" +
"</tr><tr><td> </td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:18px; font-weight:bold; text-transform:uppercase;\">Producer:</td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.Notes + "</td>" +
"</tr><tr><td> </td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:18px; font-weight:bold; text-transform:uppercase;\">Director:</td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.DirectorNotes + "</td>" +
"</tr><tr><td> </td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:18px; font-weight:bold; text-transform:uppercase;\">Floor Manager:</td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.FloorNotes + "</td>" +
"</tr><tr><td> </td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:18px; font-weight:bold; text-transform:uppercase;\">Going Forward:</td></tr><tr>" +
"<td style=\"font-family:Gotham, Helvetica, Arial, sans-serif; font-size:12px; line-height:16px;\">" + note.GoingForward + "</td>" +
"</tr><tr><td> </td></tr><tr>" +
"</tr></tbody></table></td></tr></tbody></table>";
}
var myMessage = new SendGrid.SendGridMessage();
myMessage.AddTo(GetMinutesEmail());
myMessage.From = new MailAddress("*****@*****.**", "Hope Tools");
myMessage.Subject = "Post Production Minutes ";
myMessage.Html = emailHtml;
var transportWeb = new Web(ConfigurationManager.AppSettings["SendGridApi"].ToString());
transportWeb.DeliverAsync(myMessage);
}
} //Done
public JsonResult RetriveInfoForDateAndHour(string Channel, string Date, string Hour) //Done
{
//Make SQL Datetime from Date & Time
var datetime = Date + ' ' + Hour + ":00:00.000";
var showid = "";
// check if notes exist for this date and time and populate data & Get Show info
DetailsByHour obj = new DetailsByHour();
#region first connection
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("SPU_HT_Reports_GetNotesAndDetailsForDateAndTime"))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Channel", Channel);
command.Parameters.AddWithValue("@Date", datetime);
command.Connection = connection;
connection.Open();
SqlDataReader myReader = command.ExecuteReader();
while (myReader.Read())
{
showid = myReader["tvscheduleid"].ToString();
if (myReader["tvnotes"].ToString() != "NULL" && myReader["tvnotes"].ToString() != "")
{
obj.Notes = myReader["tvnotes"].ToString().Split('¬')[0];
obj.DirectorNotes = myReader["tvnotes"].ToString().Split('¬')[1];
obj.FloorNotes = myReader["tvnotes"].ToString().Split('¬')[2];
obj.GoingForward = myReader["tvnotes"].ToString().Split('¬')[3];
}
else
{
obj.Notes = "";
obj.DirectorNotes = "";
obj.FloorNotes = "";
obj.GoingForward = "";
}
if (obj.Notes == "None")
{
obj.Notes = "";
}
if (obj.GoingForward == "None")
{
obj.GoingForward = "";
}
obj.ShowName = myReader["showname"].ToString();
obj.Presenter = myReader["PresenterName"].ToString();
obj.Producer = myReader["ProducerName"].ToString();
if (myReader["tvnotesother"].ToString() != "NULL" && myReader["tvnotesother"].ToString() != "")
{
obj.Director = myReader["tvnotesother"].ToString().Split('¬')[4];
obj.Floor = myReader["tvnotesother"].ToString().Split('¬')[5];
obj.Guest = myReader["tvnotesother"].ToString().Split('¬')[6];
}
else
{
obj.Director = "";
obj.Floor = "";
obj.Guest = "";
}
if (obj.Director == "None")
{
obj.Director = "";
}
if (obj.Floor == "None")
{
obj.Floor = "";
}
if (obj.Guest == "None")
{
obj.Guest = "";
}
var thing = myReader["tvnotesother"].ToString();
}
connection.Close();
}
}
#endregion
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("SPU_Apps_ProPanel_GetSalesFiguresForTVScheduleID"))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@TVScheduleID", showid);
command.Connection = connection;
connection.Open();
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
connection.Close();
DataTable something = ds.Tables[1];
obj.Sales = something.Rows[0]["showsoldvalue1"].ToString();
}
}
return(Json(obj, JsonRequestBehavior.AllowGet));
}
