public bool InsertCallboard(Callboard callboard)
{
// define a query
string query = "INSERT INTO public.callboard(" +
"\"strSubject\", \"strNote\", \"dtmDateTime\", \"intPostedByUserID\", \"intEventID\")" +
" VALUES(@strSubject, @strNote, @dtmDateTime, @intPostedByUserID, @intEventID);";
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
if (callboard.StrNote == null)
{
cmd.Parameters.AddWithValue("strNote", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("strNote", callboard.StrNote);
}
cmd.Parameters.AddWithValue("strSubject", callboard.StrSubject);
cmd.Parameters.AddWithValue("strNote", callboard.StrNote);
cmd.Parameters.AddWithValue("dtmDateTime", LocalDateTime.FromDateTime(DateTime.Now));
cmd.Parameters.AddWithValue("intPostedByUserID", Globals.LOGGED_IN_USER.IntUserID);
cmd.Parameters.AddWithValue("intEventID", callboard.Event.IntEventID);
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
private ActionResult SendEmail(Callboard c)
{
MailMessage mail = new MailMessage();
GetDAL get = new GetDAL();
get.OpenConnection();
foreach (Users u in get.GetUsersByEvent(get.GetEventByID(c.Event.IntEventID)))
{
mail.To.Add(u.StrEmail);
}
get.CloseConnection();
mail.From = new MailAddress("*****@*****.**");
mail.Subject = c.StrSubject;
mail.Body = "From: " + c.PostedByUser.StrName + " in " + c.Event.StrName;
if (c.StrNote != null)
{
mail.Body += "\n\n" + c.StrNote;
}
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new System.Net.NetworkCredential("ensemble395", "7GStz.pPy.6AfX[t");
smtp.EnableSsl = true;
smtp.Send(mail);
return(RedirectToAction("Index"));
}
public Callboard GetCallboardByID(int intCallboardID)
{
Callboard retval = null;
// define a query
string query = "SELECT * FROM \"callboard\" WHERE \"intCallboardID\" = " + intCallboardID;
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
// execute query
NpgsqlDataReader dr = cmd.ExecuteReader();
// read all rows and output the first column in each row
while (dr.Read())
{
retval = GetCallboardFromDR(dr);
}
return(retval);
}
public ActionResult AddAnnouncement(CallboardHomeVM vm)
{
Callboard newCallboard = vm.NewAnnouncement;
newCallboard.PostedByUser = Globals.LOGGED_IN_USER;
// insert announcement into database
InsertDAL insert = new InsertDAL();
insert.OpenConnection();
insert.InsertCallboard(newCallboard);
insert.CloseConnection();
// also send email
return(SendEmail(newCallboard));
}
public List <Callboard> GetCallboardsByPostedByMember(Member member)
{
List <Callboard> retval = new List <Callboard>();
// define a query
string query = "SELECT * FROM \"callboard\" WHERE \"intPostedByMemberID\" = " + member.IntMemberID;
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
// execute query
NpgsqlDataReader dr = cmd.ExecuteReader();
// read all rows and output the first column in each row
while (dr.Read())
{
Callboard tmpCallboard = GetCallboardFromDR(dr);
retval.Add(tmpCallboard);
}
return(retval);
}
public bool InsertCallboard(Callboard callboard)
{
//TODO
}
