public static Post GetPost(int id)
{
NPDB db = new NPDB();
Post p = new Post();
MySqlCommand fetch = db.connection.CreateCommand();
fetch.CommandText = string.Format("SELECT * FROM posts WHERE id={0}", id);
if (id != 0)
{
MySqlDataReader reader = fetch.ExecuteReader();
while (reader.Read())
{
p = Post.Init(
(int)reader ["id"],
(string)reader ["title"],
(string)reader ["tags"],
(string)reader ["thumbnail"],
(string)reader ["author"],
(DateTime)reader ["post_time"],
(string)reader ["body"]);
}
reader.Close();
db.connection.Close();
}
else
{
p = null;
}
return((p == null) ? null : p);
}
public static Account GetUser(string username)
{
NPDB db = new NPDB();
Account user = new Account();
MySqlCommand fetch = db.connection.CreateCommand();
fetch.CommandText = string.Format("SELECT * FROM accounts WHERE username='******'", username);
if (!string.IsNullOrEmpty(username))
{
MySqlDataReader reader = fetch.ExecuteReader();
while (reader.Read())
{
user = Account.Init(
(int)reader["id"],
(string)reader["username"],
(string)reader["pword"],
(string)reader["dname"],
(string)reader["email"],
(string)reader["rank"],
(string)reader["profilepic"]
);
}
reader.Close();
db.connection.Close();
}
else
{
user = null;
}
return((user == null) ? null : user);
}
public static Comment[] GetComments(int id)
{
Post post = Post.GetPost(id);
List <Comment> comments = new List <Comment> ();
NPDB db = new NPDB();
MySqlCommand fetch = db.connection.CreateCommand();
fetch.CommandText = string.Format("SELECT * FROM comments WHERE pid={0}", post.id);
MySqlDataReader reader = fetch.ExecuteReader();
int counter = 0;
while (reader.Read())
{
comments.Add(Comment.Init(
(int)reader["id"],
(string)reader["uname"],
(DateTime)reader["ctime"],
(string)reader["body"],
(int)reader["pid"]));
}
reader.Close();
db.connection.Close();
return((comments.ToArray().Length > 0) ? comments.ToArray() : null);
}
public ActionResult Index(/*int id*/)
{
db = new NPDB();
Netpress.Models.Post[] posts = new Netpress.Models.Post[1024];
MySqlCommand com = db.connection.CreateCommand();
com.CommandText = "SELECT * FROM posts";
MySqlDataReader reader = com.ExecuteReader();
int i = 0;
while (reader.Read())
{
i++;
Models.Post p = new Netpress.Models.Post();
p.id = (int)reader ["id"];
p.title = (string)reader ["title"];
p.author = (string)reader ["author"];
p.tags = (string)reader ["tags"];
p.thumbnail = (string)reader ["thumbnail"];
p.body = (string)reader ["body"];
p.time = (DateTime)reader ["post_time"];
posts [i] = p;
ViewBag.Count = i;
}
reader.Close();
//ExecuteCommand (db.connection);
db.connection.Close();
ViewBag.PostDeleted = TempData ["PostDeleted"];
AccountSetup();
return(View(posts));
}
public ActionResult PostComment(string comment, int postId)
{
Post post = Post.GetPost(postId);
Comment com = new Comment();
NPDB db = new NPDB();
MySqlCommand cmd = db.connection.CreateCommand();
if (IsMember)
{
Account user = Account.GetUser(User.Identity.Name);
com.id = Comment.Max() + 1;
com.postId = post.id;
com.userName = user.displayName ?? user.username;
com.comment = comment;
cmd.CommandText = string.Format("INSERT INTO comments (id, uname, ctime, body, pid) " +
"VALUES ('{0}','{1}','{2}','{3}','{4}');",
com.id,
com.userName,
_time,
com.comment,
com.postId);
cmd.ExecuteNonQuery();
TempData ["CommentSuccess"] = "Comment Posted! :)";
}
db.connection.Close();
return(RedirectToAction("p", "Home", new { id = post.id }));
}
public static Comment GetComment(int id)
{
NPDB db = new NPDB();
Comment user = new Comment();
MySqlCommand fetch = db.connection.CreateCommand();
fetch.CommandText = string.Format("SELECT * FROM comments WHERE id={0}", id);
if (id != 0)
{
MySqlDataReader reader = fetch.ExecuteReader();
while (reader.Read())
{
user = Comment.Init(
(int)reader["id"],
(string)reader["uname"],
(DateTime)reader["ctime"],
(string)reader["body"],
(int)reader["pid"]);
}
reader.Close();
db.connection.Close();
}
else
{
user = null;
}
return((user == null) ? null : user);
}
public static void AddToDb(int id, string tagName)
{
NPDB db = new NPDB();
MySqlCommand com = db.connection.CreateCommand();
com.CommandText = string.Format("INSERT INTO tags (id, tag_name) VALUES ('{0}', '{1}')", id, tagName);
com.ExecuteNonQuery();
db.connection.Close();
}
public ActionResult DeleteComment(int id, int postId)
{
Post post = Post.GetPost(postId);
NPDB db = new NPDB();
MySqlCommand cmd = db.connection.CreateCommand();
cmd.CommandText = string.Format("DELETE FROM comments WHERE id={0}", id);
cmd.ExecuteNonQuery();
db.connection.Close();
TempData["DeleteMessage"] = "Comment at '(" + id + ")' deleted.";
return(RedirectToAction("p", "Home", new { id = postId }));
}
public ActionResult _Signup(int id, string username, string password, string displayName, string email, string rank, string profilePic)
{
NPDB db = new NPDB();
MySqlCommand fetch = db.connection.CreateCommand();
fetch.CommandText = "SELECT * FROM accounts";
MySqlDataReader reader = fetch.ExecuteReader();
while (reader.Read())
{
Account _user = Account.Init(
(int)reader["id"],
(string)reader["username"],
(string)reader["pword"],
(string)reader["dname"],
(string)reader["email"],
(string)reader["rank"],
(string)reader["profilepic"]);
if (username == _user.username)
{
return(RedirectToAction("Signup", "Account"));
}
}
reader.Close();
id = Account.Max() + 1;
MySqlCommand cmd = db.connection.CreateCommand();
cmd.CommandText = string.Format("INSERT INTO accounts (id, username, pword, dname, email, rank, profilepic) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}');",
id, username,
password, displayName,
email, rank,
profilePic);
cmd.BeginExecuteNonQuery();
if (rank == "a")
{
Session["IsAdmin"] = true;
Session["IsMember"] = true;
FormsAuthentication.SetAuthCookie(username, true);
return(RedirectToAction("Index", "Home"));
}
if (rank == "member")
{
Session["IsMember"] = true;
Session["IsAdmin"] = false;
TempData["Name"] = username;
FormsAuthentication.SetAuthCookie(username, true);
return(RedirectToAction("Index", "Home"));
}
return(RedirectToAction("Index", "Home"));
}
public ActionResult DeletePost(int id)
{
int counter = 0;
Post post = Post.GetPost(id);
NPDB db = new NPDB();
MySqlCommand delPostCmd = db.connection.CreateCommand();
MySqlCommand delPostComsCmd = db.connection.CreateCommand();
delPostCmd.CommandText = string.Format("DELETE FROM posts WHERE id={0}", id);
delPostCmd.ExecuteNonQuery();
delPostComsCmd.CommandText = string.Format("DELETE FROM comments WHERE pid={0}", id);
delPostComsCmd.ExecuteNonQuery();
db.connection.Close();
TempData ["PostDeleted"] = "Post at '(" + id + ")' Deleted.";
return(RedirectToAction("Index", "Home"));
}
public static Tag GetTag(string tagName)
{
NPDB db = new NPDB();
Tag t = new Tag();
MySqlCommand com = db.connection.CreateCommand();
com.CommandText = string.Format("SELECT * FROM tags WHERE tag_name={0}", tagName);
MySqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
t = Init((int)reader ["id"], (string)reader ["tagName"]);
}
reader.Close();
db.connection.Close();
return(t);
}
public static int Max()
{
NPDB db = new NPDB();
MySqlCommand fetch = db.connection.CreateCommand();
fetch.CommandText = string.Format("SELECT * FROM posts");
MySqlDataReader reader = fetch.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
}
reader.Close();
db.connection.Close();
count = (count == 0) ? 0 : count;
return(count);
}
public ActionResult edit_post(int?id)
{
int _id = (id == null) ? 0 : id.Value;
Post post = Post.GetPost(_id);
NPDB db = new NPDB();
MySqlCommand cmd = db.connection.CreateCommand();
if (Post.isNull(post))
{
post = new Post(0, "Title..", "Tags..", "Image Url..", "Author..", DateTime.Parse(_time), "Body..");
}
else
{
cmd.CommandText = string.Format("SELECT * FROM posts WHERE id={0}", _id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
post = Post.Init(
(int)reader ["id"],
(string)reader ["title"],
(string)reader ["tags"],
(string)reader ["thumbnail"],
(string)reader ["author"],
(DateTime)reader ["post_time"],
(string)reader ["body"]
);
}
reader.Close();
}
db.connection.Close();
AccountSetup();
return(View(post));
}
public static Post[] GetRelPosts(int id)
{
Post p = GetPost(id);
NPDB db = new NPDB();
Post[] _posts;
List <Post> relPosts = new List <Post> ();
MySqlCommand fetch = db.connection.CreateCommand();
fetch.CommandText = string.Format("SELECT * FROM posts");
int i = 0;
int _i = 0;
int length = 0;
MySqlDataReader reader = fetch.ExecuteReader();
while (reader.Read())
{
i++;
}
_posts = new Post[i];
reader.Close();
if (id != 0)
{
reader = fetch.ExecuteReader();
while (reader.Read())
{
Models.Post _post = new Netpress.Models.Post();
_post.id = (int)reader ["id"];
_post.title = (string)reader ["title"];
_post.author = (string)reader ["author"];
_post.tags = (string)reader ["tags"];
_post.thumbnail = (string)reader ["thumbnail"];
_post.body = (string)reader ["body"];
_post.time = (DateTime)reader ["post_time"];
_posts [_i] = _post;
_i++;
}
_i = 0;
foreach (Post _p in _posts)
{
if (!string.IsNullOrEmpty(_p.tags))
{
string[] pTags = _p.tags.Split(' ');
foreach (string t in pTags)
{
if (p.tags.Contains(t))
{
//relPosts [_i] = _p;
relPosts.Add(_p);
_i++;
break;
}
}
}
}
reader.Close();
db.connection.Close();
}
Post[] _relPosts = relPosts.ToArray();
return((_relPosts != null) ? _relPosts : null);
}
public ActionResult UpdatePost(int?id, string title, string tags, string thumbnail, string body, string author, DateTime time)
{
Post post = new Post();
post.id = id.HasValue ? id.Value : 0;
post.title = title;
post.tags = tags;
post.thumbnail = thumbnail;
post.body = body;
post.author = author;
post.time = time;
NPDB db = new NPDB();
MySqlCommand com = db.connection.CreateCommand();
string[] _tags = tags.Split(new char[] { ' ' });
int returnId = 0;
if (!id.HasValue || id == 0)
{
string _time = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
post.id = Post.Max() + 1;
foreach (string t in _tags)
{
Tag.AddToDb(Tag.Max() + 1, t);
}
com.CommandText = string.Format(
"INSERT INTO posts (id, title, tags, thumbnail, author, body, post_time) " +
"VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')",
post.id,
post.title,
post.tags,
post.thumbnail,
post.author,
post.body,
_time
);
com.ExecuteNonQuery();
}
else
{
string _time = time.ToString("yyyy-MM-dd hh:mm:ss");
com.CommandText = string.Format(
"UPDATE posts " +
"SET id='{0}', title='{1}', post_time='{2}', author='{3}', tags='{4}', thumbnail='{5}', body='{6}' " +
"WHERE id={7}",
post.id,
post.title,
_time,
post.author,
post.tags,
post.thumbnail,
post.body,
post.id
);
com.ExecuteNonQuery();
}
db.connection.Close();
returnId += post.id;
return(RedirectToAction("p", "Home", new { id = returnId }));
}
