protected void btn_sendMail_Click(object sender, EventArgs e)
{
// Instantiate SQL String
string SelectUsersSQL;
// Get Token from Session
string sOrgID = Session["OrgID"].ToString();
// Select SQL
SelectUsersSQL =
"SELECT UserEmail, UserTitle, UserFirstName, UserMiddleName, UserLastName, UserToken FROM tbl_UsersMaster WHERE OrgID ='" +
sOrgID + "' AND isLeader = '0' AND optOut = '0'";
// Use SQL Statement to Select Records from DB
var sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
var cmd = new SqlCommand(SelectUsersSQL, sqlConn);
cmd.Connection.Open();
SqlDataReader rdr;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Get Master Label
var lbl_masterOrg = (Label)Master.FindControl("lbl_masterOrg");
string sOrgName = lbl_masterOrg.Text;
var mail = new MailMessage();
mail.To.Add(rdr[0].ToString());
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "Welcome to Feedback Safe with " + sOrgName;
mail.IsBodyHtml = true;
mail.BodyEncoding = Encoding.UTF8;
string body = FetchTemplate.ReadFile("/Templates/welcomeTemplate.htm");
body = body.Replace("{UserName}", rdr[1] + " " + rdr[2] + " " + rdr[3] + " " + rdr[4]);
body = body.Replace("{Organization}", sOrgName);
body = body.Replace("{Token1}", rdr[5].ToString());
body = body.Replace("{Token2}", rdr[5].ToString());
mail.Body = body;
var smtp = new SmtpClient();
smtp.Send(mail);
}
cmd.Connection.Close();
cmd.Connection.Dispose();
// Send to Dashboard
Response.Redirect("/Leader/Default.aspx");
}
//// END LISTVIEW EVENTS ////
//// BEGIN BUTTONS ////
protected void btn_AddUser_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
// Add New User to tbl_UsersMaster, set to current OrgID and generate new GUID
string sOrgID = Session["OrgID"].ToString();
string sUserToken = Guid.NewGuid().ToString();
// Insert User to DB
var sqlConn =
new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
var cmd =
new SqlCommand(
"INSERT INTO tbl_UsersMaster (OrgID, UserEmail, UserTitle, UserFirstName, UserMiddleName, UserLastName, UserToken)VALUES(@OrgID, @UserEmail, @UserTitle, @UserFirstName, @UserMiddleName, @UserLastName, @UserToken)",
sqlConn);
cmd.Parameters.Add("@OrgID", SqlDbType.VarChar, 255).Value = sOrgID;
cmd.Parameters.Add("@UserEmail", SqlDbType.VarChar, 255).Value = txt_AddUserEmail.Text;
cmd.Parameters.Add("@UserTitle", SqlDbType.VarChar, 255).Value = txt_AddUserTitle.Text;
cmd.Parameters.Add("@UserFirstName", SqlDbType.VarChar, 255).Value = txt_AddUserFirstName.Text;
cmd.Parameters.Add("@UserMiddleName", SqlDbType.VarChar, 255).Value = txt_AddUserMiddleName.Text;
cmd.Parameters.Add("@UserLastName", SqlDbType.VarChar, 255).Value = txt_AddUserLastName.Text;
cmd.Parameters.Add("@UserToken", SqlDbType.VarChar, 255).Value = sUserToken;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Connection.Dispose();
// Check ASP.NET for Duplicate email
// STORED PROCEDURE
// Send Wecome Email to New User (checkbox?)
var mail = new MailMessage();
mail.To.Add(txt_AddUserEmail.Text);
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "Welcome to Feedback Safe";
mail.IsBodyHtml = true;
mail.BodyEncoding = Encoding.UTF8;
string body = FetchTemplate.ReadFile("/Templates/welcomeTemplate.htm");
body = body.Replace("{UserName}",
txt_AddUserTitle.Text + " " + txt_AddUserFirstName.Text + " " +
txt_AddUserMiddleName.Text + " " + txt_AddUserLastName.Text);
body = body.Replace("{Token1}", sUserToken);
body = body.Replace("{Token2}", sUserToken);
mail.Body = body;
var smtp = new SmtpClient();
smtp.Send(mail);
// Clear Textbox
txt_AddUserEmail.Text = "";
txt_AddUserTitle.Text = "";
txt_AddUserFirstName.Text = "";
txt_AddUserMiddleName.Text = "";
txt_AddUserLastName.Text = "";
// Call ListView Databind
BindOrg();
}
}
//// END LINKBUTTONS ////
//// BEGIN BUTTONS ////
protected void btn_askQuestion_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
// Insert new Conversation to DB and Return New ID
var sqlConn =
new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
var cmd =
new SqlCommand(
"INSERT INTO tbl_Conversations (OrgID, UserID, LeaderID, ConversationApproved, ConversationUnread)VALUES(@OrgID, @UserID, @LeaderID, '1', '1');SELECT @@IDENTITY",
sqlConn);
cmd.Parameters.Add("@OrgID", SqlDbType.VarChar, 255).Value = Session["OrgID"].ToString();
cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 255).Value = Session["UserID"].ToString();
cmd.Parameters.Add("@LeaderID", SqlDbType.VarChar, 255).Value = Session["UserID"].ToString();
cmd.Connection.Open();
Int32 NewConvID = Convert.ToInt32(cmd.ExecuteScalar());
lbl_ConvID.Text = NewConvID.ToString();
cmd.Connection.Close();
cmd.Connection.Dispose();
// Insert Comment to DB using New ConversationID
var sqlConn2 =
new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
var cmd2 =
new SqlCommand(
"INSERT INTO tbl_Comments (ConversationID, UserID, CommentText, CommentApproved)VALUES(@ConversationID, @UserID, @CommentText, '1')",
sqlConn2);
cmd2.Parameters.Add("@ConversationID", SqlDbType.VarChar, 255).Value = lbl_ConvID.Text;
cmd2.Parameters.Add("@UserID", SqlDbType.VarChar, 255).Value = Session["UserID"].ToString();
cmd2.Parameters.Add("@CommentText", SqlDbType.NVarChar, -1).Value = txt_askedQuestion.Text;
cmd2.Connection.Open();
cmd2.ExecuteNonQuery();
cmd2.Connection.Close();
cmd2.Connection.Dispose();
// TO DO Send Email to Users
if (chk_emailAll.Checked)
{
// Instantiate SQL String
string SelectUsersSQL;
// Get Token from Session
string sOrgID = Session["OrgID"].ToString();
// Select SQL
SelectUsersSQL =
"SELECT UserEmail, UserTitle, UserFirstName, UserMiddleName, UserLastName, UserToken FROM tbl_UsersMaster WHERE OrgID ='" +
sOrgID + "' AND isLeader = '0' AND optOut = '0'";
// Use SQL Statement to Select Records from DB
var sqlConn3 =
new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
var cmd3 = new SqlCommand(SelectUsersSQL, sqlConn3);
cmd3.Connection.Open();
SqlDataReader rdr;
rdr = cmd3.ExecuteReader();
while (rdr.Read())
{
// Get OrgName
string sOrgName = lbl_OrgName.Text;
var mail = new MailMessage();
mail.To.Add(rdr[0].ToString());
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "Feedback Safe: A New Conversation has been started with " + sOrgName;
mail.IsBodyHtml = true;
mail.BodyEncoding = Encoding.UTF8;
string body = FetchTemplate.ReadFile("/Templates/leaderAddConversation.htm");
body = body.Replace("{UserName}", rdr[1] + " " + rdr[2] + " " + rdr[3] + " " + rdr[4]);
body = body.Replace("{Organization}", sOrgName);
body = body.Replace("{Token1}", rdr[5].ToString());
body = body.Replace("{Token2}", rdr[5].ToString());
body = body.Replace("{LeaderComment}", txt_askedQuestion.Text);
mail.Body = body;
var smtp = new SmtpClient();
smtp.Send(mail);
}
cmd3.Connection.Close();
cmd3.Connection.Dispose();
}
// Clear Textbox
txt_askedQuestion.Text = "";
// Uncheck Mail Checkbox
chk_emailAll.Checked = false;
// Rebind Conversation List
BindConvList();
}
}
//// END DATALIST EVENTS ////
//// BEGIN BUTTONS ////
protected void btn_addComment_Click(object sender, ImageClickEventArgs e)
{
if (Page.IsValid)
{
// Insert List to DB and Return New ID
var sqlConn =
new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
var cmd =
new SqlCommand(
"INSERT INTO tbl_Comments (ConversationID, UserID, CommentText, CommentApproved)VALUES(@ConversationID, @UserID, @CommentText, '1')",
sqlConn);
cmd.Parameters.Add("@ConversationID", SqlDbType.VarChar, 255).Value =
Session["ConversationID"].ToString();
cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 255).Value = Session["UserID"].ToString();
cmd.Parameters.Add("@CommentText", SqlDbType.NVarChar, -1).Value = txt_addComment.Text;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Connection.Dispose();
// Do Checks and Send User Email
// Instantiate SQL String
string SelectUsersSQL;
// Get Token from Session
string sOrgID = Session["OrgID"].ToString();
// Select SQL
SelectUsersSQL =
"SELECT DISTINCT tbl_UsersMaster.UserEmail, tbl_UsersMaster.UserTitle, tbl_UsersMaster.UserFirstName, tbl_UsersMaster.UserMiddleName, tbl_UsersMaster.UserLastName, tbl_UsersMaster.UserToken FROM tbl_Comments, tbl_Conversations, tbl_UsersMaster WHERE tbl_Comments.ConversationID =@ConversationID AND tbl_Comments.ConversationID = tbl_Conversations.ConversationID AND tbl_Comments.UserID = tbl_UsersMaster.UserID AND tbl_UsersMaster.UserID = tbl_Comments.UserID AND tbl_UsersMaster.isLeader = '0' AND tbl_UsersMaster.optOut = '0' AND tbl_UsersMaster.optOutComment = '0'";
// Use SQL Statement to Select Records from DB
var sqlConn2 =
new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
var cmd2 = new SqlCommand(SelectUsersSQL, sqlConn2);
cmd2.Parameters.Add("@ConversationID", SqlDbType.VarChar, 255).Value =
Session["ConversationID"].ToString();
cmd2.Connection.Open();
SqlDataReader rdr;
rdr = cmd2.ExecuteReader();
while (rdr.Read())
{
// Get OrgName
var lbl_masterOrg = (Label)Master.FindControl("lbl_masterOrg");
string sOrgName = lbl_masterOrg.Text;
var mail = new MailMessage();
mail.To.Add(rdr[0].ToString());
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "Feedback Safe: A New Comment has been added with " + sOrgName;
mail.IsBodyHtml = true;
mail.BodyEncoding = Encoding.UTF8;
string body = FetchTemplate.ReadFile("/Templates/leaderAddComment.htm");
body = body.Replace("{UserName}", rdr[1] + " " + rdr[2] + " " + rdr[3] + " " + rdr[4]);
body = body.Replace("{Organization}", sOrgName);
body = body.Replace("{Token1}", rdr[5].ToString());
body = body.Replace("{Token2}", rdr[5].ToString());
body = body.Replace("{LeaderComment}", txt_addComment.Text);
mail.Body = body;
var smtp = new SmtpClient();
smtp.Send(mail);
}
cmd2.Connection.Close();
cmd2.Connection.Dispose();
}
// Clear Textbox
txt_addComment.Text = "";
// Rebind Datalist
BindConversation();
}
