/YABE/Models/PostDL.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

using System.Data.Linq;



namespace YABE.Models

{



    public interface IPostDL

    {

        List<DateTime> GetPostMonths();

        List<Post> GetRecentPosts(int numberPosts);

        List<Post> GetPosts();

        List<Post> GetPosts(int year);

        List<Post> GetPosts(int year, int month);

        List<Post> GetPosts(int year, int month, int day);

        Post GetPost(int year, int month, int day, string title);

        Post GetPostById(int id);

        Post GetPostByIdWithUnApprovedComments(int id);

        List<Post> GetPostsByTag(string tag);

        void SavePosts(IList<Post> posts);

        void AddPost(Post post, IList<Tag> tags);

		void UpdatePost(Post post, IList<Tag> tags);

		void DeletePost(int id);

        void AddComment(int PostID,string Author, string Email, string Url, string Comments, DateTime Created,bool IsApproved,

               string ClientIP, DateTime Modified);

		void ScrubPosts();

    }



    public class PostDL:IPostDL

	{

		public List<DateTime> GetPostMonths()

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				var dates = from p in context.Posts

							group p by new

							{

								PostMonth = new DateTime(p.Created.Year, p.Created.Month, 1)

							} into g

							select  g.Key.PostMonth;



				return dates.ToList();

			}

		}



		public void ScrubPosts()

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				var posts = from p in context.Posts

							where p.Body.Contains("<br>")

							select p;

				foreach (Post p in posts)

				{

					p.Body = p.Body.Replace("<br>", "<br/>");

				}



				context.SubmitChanges();

			}

		}



		public List<Post> GetRecentPosts(int numberPosts)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				context.DeferredLoadingEnabled = false;

				DataLoadOptions dlo = new DataLoadOptions();

				dlo.LoadWith<Post>(p => p.Comments);

                dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

				dlo.LoadWith<Post>(p => p.PostTags);

				dlo.LoadWith<PostTag>(pt => pt.Tag);

				context.LoadOptions = dlo;

				return context.Posts.OrderByDescending( p => p.Created).Take(numberPosts).ToList();

			}

		}



		public List<Post> GetPosts()

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				context.DeferredLoadingEnabled = false;

				DataLoadOptions dlo = new DataLoadOptions();

				dlo.LoadWith<Post>(p => p.Comments);

                dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

				dlo.LoadWith<Post>(p => p.PostTags);

				dlo.LoadWith<PostTag>(pt => pt.Tag);

				context.LoadOptions = dlo;

				return context.Posts.ToList();

			}

		}

		public List<Post> GetPosts(int year)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				context.DeferredLoadingEnabled = false;

				DataLoadOptions dlo = new DataLoadOptions();

				dlo.LoadWith<Post>(p => p.Comments);

                dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

				dlo.LoadWith<Post>(p => p.PostTags);

				dlo.LoadWith<PostTag>(pt => pt.Tag);

				context.LoadOptions = dlo;

				return context.Posts.Where(s => s.Created.Year == year).ToList();

			}



		}



		public List<Post> GetPosts(int year, int month)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				context.DeferredLoadingEnabled = false;

				DataLoadOptions dlo = new DataLoadOptions();

				dlo.LoadWith<Post>(p => p.Comments);

                dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

				dlo.LoadWith<Post>(p => p.PostTags);

				dlo.LoadWith<PostTag>(pt => pt.Tag);

				context.LoadOptions = dlo;

				return context.Posts.Where(s => s.Created.Year == year && s.Created.Month == month).ToList();

			}

		}



		public List<Post> GetPosts(int year, int month, int day)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				context.DeferredLoadingEnabled = false;

				DataLoadOptions dlo = new DataLoadOptions();

				dlo.LoadWith<Post>(p => p.Comments);

                dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

				dlo.LoadWith<Post>(p => p.PostTags);

				dlo.LoadWith<PostTag>(pt => pt.Tag);

				context.LoadOptions = dlo;

				return context.Posts.Where(s => s.Created.Year == year && s.Created.Month == month

					&& s.Created.Day == day).ToList();

			}

		}



		public Post GetPost(int year, int month, int day, string title)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				context.DeferredLoadingEnabled = false;

				DataLoadOptions dlo = new DataLoadOptions();

				dlo.LoadWith<Post>(p => p.Comments);

                dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

				dlo.LoadWith<Post>(p => p.PostTags);

				dlo.LoadWith<PostTag>(pt => pt.Tag);

				context.LoadOptions = dlo;

				return context.Posts.Single(s => s.Created.Year == year && s.Created.Month == month && s.Created.Day == day && s.Title.Replace(' ', '-').ToLower() == title.ToLower());

			}

		}

        

		public Post GetPostById(int id)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				context.DeferredLoadingEnabled = false;

				DataLoadOptions dlo = new DataLoadOptions();

				dlo.LoadWith<Post>(p => p.Comments);

                dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

            	dlo.LoadWith<Post>(p => p.PostTags);

				dlo.LoadWith<PostTag>(pt => pt.Tag);

				context.LoadOptions = dlo;

				return context.Posts.Single(s => s.PostID == id);

			}

		}



        public Post GetPostByIdWithUnApprovedComments(int id)

        {

            using (YabeDataContext context = YabeDataManager.Instance.DataContext) {

                context.DeferredLoadingEnabled = false;

                DataLoadOptions dlo = new DataLoadOptions();

                dlo.LoadWith<Post>(p => p.Comments);

                dlo.LoadWith<Post>(p => p.PostTags);

                dlo.LoadWith<PostTag>(pt => pt.Tag);

                context.LoadOptions = dlo;

                return context.Posts.Single(s => s.PostID == id);

            }

        }



		public List<Post> GetPostsByTag(string tag)

		{

			if (tag == null)

			{

				using (YabeDataContext context = YabeDataManager.Instance.DataContext)

				{

					context.DeferredLoadingEnabled = false;

					DataLoadOptions dlo = new DataLoadOptions();

					dlo.LoadWith<Post>(p => p.Comments);

                    dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

					dlo.LoadWith<Post>(p => p.PostTags);

					dlo.LoadWith<PostTag>(pt => pt.Tag);

					context.LoadOptions = dlo;

					return context.Posts.Where(s => s.PostTags == null || s.PostTags.Count == 0 ).ToList();

				}

			}

			else

			{

				using (YabeDataContext context = YabeDataManager.Instance.DataContext)

				{

					context.DeferredLoadingEnabled = false;

					DataLoadOptions dlo = new DataLoadOptions();

					dlo.LoadWith<Post>(p => p.Comments);

                    dlo.AssociateWith<Post>(p => p.Comments.Where(c => c.IsApproved == true));

					dlo.LoadWith<Post>(p => p.PostTags);

					dlo.LoadWith<PostTag>(pt => pt.Tag);

					context.LoadOptions = dlo;

					return context.Posts.Where(s => s.PostTags.Single(

						pt => pt.Tag.TagName.ToLower() == tag.Trim().ToLower()) != null).ToList();

				}

			}

		}

		

		public void SavePosts(IList<Post> posts)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				foreach (Post postItem in posts)

				{

				    Post postItem1 = postItem;

				    Post p = context.Posts.SingleOrDefault(c =>

									c.PostID == postItem1.PostID);

					if (p == null)

					{

						p = new Post()//post does not exist in db

						{

							Author = postItem.Author,

							Title = postItem.Title,

							Description = postItem.Description,

							Body = postItem.Body,

							Created = postItem.Created,

							Modified = postItem.Modified,

							IsPublic = true,

							ShowOnFrontPage = true,

							AllowComments = true,

							//Categories = postItem.Categories

						};

						context.Posts.InsertOnSubmit(p);

					}

					else //update

					{

						p.Author = postItem.Author;

						p.Title = postItem.Title;

						p.Description = postItem.Description;

						p.Body = postItem.Body;

						p.Modified = DateTime.Now;

						p.IsPublic = true;

						p.ShowOnFrontPage = true;

						p.AllowComments = true;

						//p.Categories = postItem.Categories;

					}

				}



				try

				{

					context.SubmitChanges(ConflictMode.ContinueOnConflict);



				}

				catch (ChangeConflictException)

				{

					context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues); //User values will be keeped on the resolution



				}

			}



		}



		public void UpdatePost(Post post, IList<Tag> tags)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				Post p = context.Posts.SingleOrDefault(c =>

								c.PostID == post.PostID);



				p.AllowComments = post.AllowComments;

				p.Author = post.Author;

				p.Body = post.Body;

				p.Categories = post.Categories;

				//p.Comments = post.Comments;

				p.Created = post.Created;

				p.Description = post.Description;

				p.IsPublic = post.IsPublic;

				p.Modified = post.Modified;

				p.PostTags.Clear();

				p.ShowOnFrontPage = post.ShowOnFrontPage;

				p.Syndicated = post.Syndicated;

				p.Title = post.Title;

				



				foreach (Tag tag in tags)

				{

					PostTag pt = new PostTag();

					pt.TagID = tag.TagID;

					//tag.PostTags.Add(pt);

					p.PostTags.Add(pt);

				}



				try

				{

					context.SubmitChanges(ConflictMode.ContinueOnConflict);

				}

				catch (ChangeConflictException)

				{

					context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues); //User values will be keeped on the resolution



				}

			}

		}



		public void AddPost(Post post, IList<Tag> tags)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				Post p = context.Posts.SingleOrDefault(c =>

								c.PostID == post.PostID);

				// new post

				if (p == null)

				{

					p = post;

					foreach (Tag tag in tags)

					{

						PostTag pt = new PostTag();

						pt.TagID = tag.TagID;

						//tag.PostTags.Add(pt);

						p.PostTags.Add(pt);

					}



					context.Posts.InsertOnSubmit(p);

				}

				else //update

				{

					p.Author = post.Author;

					p.Title = post.Title;

					p.Description = post.Description;

					p.Body = post.Body;

					p.Modified = DateTime.Now;

					p.IsPublic = true;

					p.ShowOnFrontPage = true;

					p.AllowComments = true;

					//p.Categories = postItem.Categories;

				}



				try

				{

					context.SubmitChanges(ConflictMode.ContinueOnConflict);



				}

				catch (ChangeConflictException)

				{

					context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues); //User values will be keeped on the resolution



				}

			}

		}

        //TODO: Check if this deletes all the foreign objects such as comments, tags (dont think so)

		public void DeletePost(int id)

		{

			using (YabeDataContext context = YabeDataManager.Instance.DataContext)

			{

				Post p = context.Posts.SingleOrDefault(c =>

								c.PostID == id);



				// delete post

				if (p != null)

				{

					context.Posts.DeleteOnSubmit(p);

				}



				try

				{

					context.SubmitChanges(ConflictMode.ContinueOnConflict);



				}

				catch (ChangeConflictException)

				{

					context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues); //User values will be keeped on the resolution



				}

			}

		}



	    public void AddComment(int PostID,string Author, string Email, string Url, string Comments,

            DateTime Created,bool IsApproved, string ClientIP, DateTime Modified)

	    {

	        using (YabeDataContext context = YabeDataManager.Instance.DataContext)

	        {

	            Comment newComment = new Comment();

	            newComment.PostID = PostID;

	            newComment.Author = Author;

	            newComment.Email = Email;

	            newComment.Url = Url;

	            newComment.Comments = Comments;

	            newComment.IsApproved = IsApproved;

	            newComment.ClientIP = ClientIP;

	            newComment.Modified = Modified;

	            newComment.Created = Created;

                context.Comments.InsertOnSubmit(newComment);

                try

                {

                    context.SubmitChanges(ConflictMode.ContinueOnConflict);



                }

                catch (ChangeConflictException)

                {

                    context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues); //User values will be keeped on the resolution



                }

	        }

	    }

	}

}