/IntellectShop.solution/IntellectShop.Common/Data/Certification.data.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Transactions;



namespace IntellectShop.Common

{

	public partial class Certification : Commodity

	{

		public static List<Common.Category> RetrieveCategoriesAll()

		{

			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))

			{

				var query =

							from category in context.Categories

							join certification in context.Certifications

								on category.CategoryID equals certification.CategoryID

							group category by new

							{

								category.CategoryID,

								category.SortingID,

								category.Name

							} into g

							orderby g.Key.SortingID descending, g.Key.Name

							select g.Key;



				List<Common.Category> result = new List<Category>();

				foreach (var row in query)

				{

					result.Add(new Common.Category

					{

						CategoryID = row.CategoryID,

						Name = row.Name,

						SortingID = row.SortingID

					});

				}



				return result;

			}

		}



		public static Certification RetrieveByCertificationID(int certificationID)

		{

			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))

			{

				var query = (

							from certification in dataContext.Certifications

							where certification.CertificationID == certificationID

							join commodity in dataContext.Commodities

								on certification.CommodityID equals commodity.CommodityID

							join category in dataContext.Categories

								on certification.CategoryID equals category.CategoryID

							orderby commodity.SortingID descending, commodity.Code

							select new

							{

								category.CategoryID,

								CategoryName = category.Name,

								certification.CertificationID,

								commodity.CommodityID,

								commodity.SortingID,

								commodity.Code,

								commodity.Name,

								commodity.Price,

								commodity.Descr

							}).SingleOrDefault();



				if (query == null)

					return null;



				Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName };



				Common.Certification selectedCertification = new Common.Certification

				{

					CertificationID = query.CertificationID,

					Category = selectedCategory,

					Code = query.Code,

					CommodityID = query.CommodityID,

					SortingID = query.SortingID,

					Description = query.Descr,

					Name = query.Name,

					Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB }

				};



				return selectedCertification;

			}

		}



		public static Certification RetrieveByCommodityID(int commodityID)

		{

			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))

			{

				var query = (

							from commodity in dataContext.Commodities

							where commodity.CommodityID == commodityID

							join certification in dataContext.Certifications

								on commodity.CommodityID equals certification.CommodityID

							join category in dataContext.Categories

								on certification.CategoryID equals category.CategoryID

							select new

							{

								category.CategoryID,

								CategoryName = category.Name,

								CategorySortingID = category.SortingID,

								certification.CertificationID,

								commodity.CommodityID,

								commodity.SortingID,

								commodity.Code,

								commodity.Name,

								commodity.Price,

								commodity.Descr

							}).SingleOrDefault();



				if (query == null)

					return null;



				Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName, SortingID = query.CategorySortingID };



				Common.Certification selectedCertification = new Common.Certification

				{

					CertificationID = query.CertificationID,

					Category = selectedCategory,

					Code = query.Code,

					CommodityID = query.CommodityID,

					SortingID = query.SortingID,

					Description = query.Descr,

					Name = query.Name,

					Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB }

				};



				return selectedCertification;

			}

		}



		public new static Dictionary<Common.Category, List<Common.Certification>> RetrieveAll()

		{

			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))

			{

				var query = (

							from commodity in dataContext.Commodities

							join certification in dataContext.Certifications

								on commodity.CommodityID equals certification.CommodityID

							join category in dataContext.Categories

								on certification.CategoryID equals category.CategoryID

							orderby category.SortingID descending, commodity.SortingID descending, commodity.Code

							select new

							{

								category.CategoryID,

								CategoryName = category.Name,

								CategorySortingID = category.SortingID,

								certification.CertificationID,

								commodity.CommodityID,

								CommoditySortingID = commodity.SortingID,

								commodity.SortingID,

								commodity.Code,

								commodity.Name,

								commodity.Price,

								commodity.Descr

							});



				Dictionary<Common.Category, List<Common.Certification>> result = new Dictionary<Common.Category, List<Common.Certification>>();

				Common.Category selectedCategory = null;

				foreach (var row in query)

				{

					selectedCategory = new Common.Category { CategoryID = row.CategoryID, Name = row.CategoryName, SortingID = row.CategorySortingID };

					List<Common.Certification> certifications;

					if (result.TryGetValue(selectedCategory, out certifications))

					{

						if (certifications == null)

						{

							certifications = new List<Certification>();

						}

					}

					else

					{

						certifications = new List<Certification>();

						result.Add(selectedCategory, certifications);

					}



					certifications.Add(new Common.Certification

					{

						CertificationID = row.CertificationID,

						Category = selectedCategory,

						Code = row.Code,

						CommodityID = row.CommodityID,

						SortingID = row.CommoditySortingID,

						Name = row.Name,

						Price = new Common.Money { Amount = (row.Price.HasValue ? row.Price.Value : 0), Currency = Currency.RUB }

					});

				}



				return result;

			}

		}



		public static List<Common.Certification> RetrieveByCategoryID(int categoryID)

		{

			List<Common.Certification> selectedCertifications = new List<Common.Certification>();

			Common.Category selectedCategory = Common.Category.RetrieveByID(categoryID);

			if (selectedCategory == null)

			{

				return selectedCertifications;

			}



			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))

			{

				var queryCertifications =

							from category in dataContext.Categories

							where category.CategoryID == categoryID

							join certification in dataContext.Certifications

								on category.CategoryID equals certification.CategoryID

							join commodity in dataContext.Commodities

								on certification.CommodityID equals commodity.CommodityID

							orderby commodity.SortingID descending, commodity.Code

							select new

							{

								category.CategoryID,

								CategoryName = category.Name,

								certification.CertificationID,

								commodity.CommodityID,

								commodity.SortingID,

								commodity.Code,

								commodity.Name,

								commodity.Price,

								commodity.Descr

							};



				foreach (var each in queryCertifications)

				{

					selectedCertifications.Add(new Common.Certification

					{

						CertificationID = each.CertificationID,

						Category = selectedCategory,

						Code = each.Code,

						CommodityID = each.CommodityID,

						SortingID = each.SortingID,

						Description = each.Descr,

						Name = each.Name,

						Price = new Common.Money { Amount = (each.Price.HasValue ? each.Price.Value : 0), Currency = Currency.RUB }

					});

				}



				return selectedCertifications;

			}

		}



		public new void Create(string createdBy)

		{

			using (TransactionScope trans = new TransactionScope())

			{

				using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))

				{

					var commodityDB = new DB.Commodity

						{

							Code = this.Code,

							Descr = this.Description,

							Name = this.Name,

							Price = this.Price.Amount,

							SortingID = this.SortingID,

							Created = DateTime.Now,

							CreatedBy = createdBy,

							CommodityType = (int)this.CommodityType

						};

					dataContext.Commodities.InsertOnSubmit(commodityDB);

					dataContext.SubmitChanges();



					var certificationDB = new DB.Certification

						{

							CommodityID = commodityDB.CommodityID,

							CategoryID = this.Category.CategoryID,

							Created = DateTime.Now,

							CreatedBy = createdBy

						};

					dataContext.Certifications.InsertOnSubmit(certificationDB);

					dataContext.SubmitChanges();

					this.CertificationID = certificationDB.CertificationID;

					this.CommodityID = commodityDB.CommodityID;



					trans.Complete();

				}

			}



			this.Tags.CommodityID = this.CommodityID;

			this.Tags.Save();

		}



		public new void Update(string updateBy)

		{

			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))

			{

				var certificationDB = dataContext.Certifications.SingleOrDefault(x => x.CommodityID == this.CommodityID);

				certificationDB.CategoryID = this.Category.CategoryID;

				certificationDB.Updated = DateTime.Now;

				certificationDB.UpdatedBy = updateBy;



				certificationDB.Commodity.SortingID = this.SortingID;

				certificationDB.Commodity.Name = this.Name;

				certificationDB.Commodity.Code = this.Code;

				certificationDB.Commodity.Descr = this.Description;

				certificationDB.Commodity.Price = this.Price.Amount;

				certificationDB.Commodity.Updated = DateTime.Now;

				certificationDB.Commodity.UpdatedBy = updateBy;



				dataContext.SubmitChanges();

			}



			this.Tags.Save();

		}



		public new static void Delete(int certificationID)

		{

			int commodityID;

			using (TransactionScope trans = new TransactionScope())

			{

				using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))

				{

					var certificationDB = (from certification in dataContext.Certifications where certification.CertificationID == certificationID select certification).SingleOrDefault();

					commodityID = certificationDB.CommodityID;



					var cartItemsDB = from cart in dataContext.CartItems where cart.CommodityID == commodityID select cart;

					dataContext.CartItems.DeleteAllOnSubmit(cartItemsDB);

					dataContext.SubmitChanges();



					var tagsSetDB = from tags in dataContext.TagsSets where tags.CommodityID == commodityID select tags;

					dataContext.TagsSets.DeleteAllOnSubmit(tagsSetDB);

					dataContext.SubmitChanges();



					dataContext.Certifications.DeleteOnSubmit(certificationDB);

					dataContext.SubmitChanges();



					var commodityDB = (from commodity in dataContext.Commodities where commodity.CommodityID == commodityID select commodity).SingleOrDefault();

					dataContext.Commodities.DeleteOnSubmit(commodityDB);

					dataContext.SubmitChanges();



					trans.Complete();

				}

			}

		}

	}

}