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(); }