PageRenderTime 46ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 0ms

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

#
C# | 339 lines | 304 code | 35 blank | 0 comment | 17 complexity | b3faa9da7e9282ec27a930c3667e8c57 MD5 | raw file
Possible License(s): LGPL-3.0, GPL-2.0, CC-BY-SA-3.0
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Transactions;
  5. namespace IntellectShop.Common
  6. {
  7. public partial class Certification : Commodity
  8. {
  9. public static List<Common.Category> RetrieveCategoriesAll()
  10. {
  11. using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
  12. {
  13. var query =
  14. from category in context.Categories
  15. join certification in context.Certifications
  16. on category.CategoryID equals certification.CategoryID
  17. group category by new
  18. {
  19. category.CategoryID,
  20. category.SortingID,
  21. category.Name
  22. } into g
  23. orderby g.Key.SortingID descending, g.Key.Name
  24. select g.Key;
  25. List<Common.Category> result = new List<Category>();
  26. foreach (var row in query)
  27. {
  28. result.Add(new Common.Category
  29. {
  30. CategoryID = row.CategoryID,
  31. Name = row.Name,
  32. SortingID = row.SortingID
  33. });
  34. }
  35. return result;
  36. }
  37. }
  38. public static Certification RetrieveByCertificationID(int certificationID)
  39. {
  40. using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
  41. {
  42. var query = (
  43. from certification in dataContext.Certifications
  44. where certification.CertificationID == certificationID
  45. join commodity in dataContext.Commodities
  46. on certification.CommodityID equals commodity.CommodityID
  47. join category in dataContext.Categories
  48. on certification.CategoryID equals category.CategoryID
  49. orderby commodity.SortingID descending, commodity.Code
  50. select new
  51. {
  52. category.CategoryID,
  53. CategoryName = category.Name,
  54. certification.CertificationID,
  55. commodity.CommodityID,
  56. commodity.SortingID,
  57. commodity.Code,
  58. commodity.Name,
  59. commodity.Price,
  60. commodity.Descr
  61. }).SingleOrDefault();
  62. if (query == null)
  63. return null;
  64. Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName };
  65. Common.Certification selectedCertification = new Common.Certification
  66. {
  67. CertificationID = query.CertificationID,
  68. Category = selectedCategory,
  69. Code = query.Code,
  70. CommodityID = query.CommodityID,
  71. SortingID = query.SortingID,
  72. Description = query.Descr,
  73. Name = query.Name,
  74. Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB }
  75. };
  76. return selectedCertification;
  77. }
  78. }
  79. public static Certification RetrieveByCommodityID(int commodityID)
  80. {
  81. using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
  82. {
  83. var query = (
  84. from commodity in dataContext.Commodities
  85. where commodity.CommodityID == commodityID
  86. join certification in dataContext.Certifications
  87. on commodity.CommodityID equals certification.CommodityID
  88. join category in dataContext.Categories
  89. on certification.CategoryID equals category.CategoryID
  90. select new
  91. {
  92. category.CategoryID,
  93. CategoryName = category.Name,
  94. CategorySortingID = category.SortingID,
  95. certification.CertificationID,
  96. commodity.CommodityID,
  97. commodity.SortingID,
  98. commodity.Code,
  99. commodity.Name,
  100. commodity.Price,
  101. commodity.Descr
  102. }).SingleOrDefault();
  103. if (query == null)
  104. return null;
  105. Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName, SortingID = query.CategorySortingID };
  106. Common.Certification selectedCertification = new Common.Certification
  107. {
  108. CertificationID = query.CertificationID,
  109. Category = selectedCategory,
  110. Code = query.Code,
  111. CommodityID = query.CommodityID,
  112. SortingID = query.SortingID,
  113. Description = query.Descr,
  114. Name = query.Name,
  115. Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB }
  116. };
  117. return selectedCertification;
  118. }
  119. }
  120. public new static Dictionary<Common.Category, List<Common.Certification>> RetrieveAll()
  121. {
  122. using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
  123. {
  124. var query = (
  125. from commodity in dataContext.Commodities
  126. join certification in dataContext.Certifications
  127. on commodity.CommodityID equals certification.CommodityID
  128. join category in dataContext.Categories
  129. on certification.CategoryID equals category.CategoryID
  130. orderby category.SortingID descending, commodity.SortingID descending, commodity.Code
  131. select new
  132. {
  133. category.CategoryID,
  134. CategoryName = category.Name,
  135. CategorySortingID = category.SortingID,
  136. certification.CertificationID,
  137. commodity.CommodityID,
  138. CommoditySortingID = commodity.SortingID,
  139. commodity.SortingID,
  140. commodity.Code,
  141. commodity.Name,
  142. commodity.Price,
  143. commodity.Descr
  144. });
  145. Dictionary<Common.Category, List<Common.Certification>> result = new Dictionary<Common.Category, List<Common.Certification>>();
  146. Common.Category selectedCategory = null;
  147. foreach (var row in query)
  148. {
  149. selectedCategory = new Common.Category { CategoryID = row.CategoryID, Name = row.CategoryName, SortingID = row.CategorySortingID };
  150. List<Common.Certification> certifications;
  151. if (result.TryGetValue(selectedCategory, out certifications))
  152. {
  153. if (certifications == null)
  154. {
  155. certifications = new List<Certification>();
  156. }
  157. }
  158. else
  159. {
  160. certifications = new List<Certification>();
  161. result.Add(selectedCategory, certifications);
  162. }
  163. certifications.Add(new Common.Certification
  164. {
  165. CertificationID = row.CertificationID,
  166. Category = selectedCategory,
  167. Code = row.Code,
  168. CommodityID = row.CommodityID,
  169. SortingID = row.CommoditySortingID,
  170. Name = row.Name,
  171. Price = new Common.Money { Amount = (row.Price.HasValue ? row.Price.Value : 0), Currency = Currency.RUB }
  172. });
  173. }
  174. return result;
  175. }
  176. }
  177. public static List<Common.Certification> RetrieveByCategoryID(int categoryID)
  178. {
  179. List<Common.Certification> selectedCertifications = new List<Common.Certification>();
  180. Common.Category selectedCategory = Common.Category.RetrieveByID(categoryID);
  181. if (selectedCategory == null)
  182. {
  183. return selectedCertifications;
  184. }
  185. using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
  186. {
  187. var queryCertifications =
  188. from category in dataContext.Categories
  189. where category.CategoryID == categoryID
  190. join certification in dataContext.Certifications
  191. on category.CategoryID equals certification.CategoryID
  192. join commodity in dataContext.Commodities
  193. on certification.CommodityID equals commodity.CommodityID
  194. orderby commodity.SortingID descending, commodity.Code
  195. select new
  196. {
  197. category.CategoryID,
  198. CategoryName = category.Name,
  199. certification.CertificationID,
  200. commodity.CommodityID,
  201. commodity.SortingID,
  202. commodity.Code,
  203. commodity.Name,
  204. commodity.Price,
  205. commodity.Descr
  206. };
  207. foreach (var each in queryCertifications)
  208. {
  209. selectedCertifications.Add(new Common.Certification
  210. {
  211. CertificationID = each.CertificationID,
  212. Category = selectedCategory,
  213. Code = each.Code,
  214. CommodityID = each.CommodityID,
  215. SortingID = each.SortingID,
  216. Description = each.Descr,
  217. Name = each.Name,
  218. Price = new Common.Money { Amount = (each.Price.HasValue ? each.Price.Value : 0), Currency = Currency.RUB }
  219. });
  220. }
  221. return selectedCertifications;
  222. }
  223. }
  224. public new void Create(string createdBy)
  225. {
  226. using (TransactionScope trans = new TransactionScope())
  227. {
  228. using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
  229. {
  230. var commodityDB = new DB.Commodity
  231. {
  232. Code = this.Code,
  233. Descr = this.Description,
  234. Name = this.Name,
  235. Price = this.Price.Amount,
  236. SortingID = this.SortingID,
  237. Created = DateTime.Now,
  238. CreatedBy = createdBy,
  239. CommodityType = (int)this.CommodityType
  240. };
  241. dataContext.Commodities.InsertOnSubmit(commodityDB);
  242. dataContext.SubmitChanges();
  243. var certificationDB = new DB.Certification
  244. {
  245. CommodityID = commodityDB.CommodityID,
  246. CategoryID = this.Category.CategoryID,
  247. Created = DateTime.Now,
  248. CreatedBy = createdBy
  249. };
  250. dataContext.Certifications.InsertOnSubmit(certificationDB);
  251. dataContext.SubmitChanges();
  252. this.CertificationID = certificationDB.CertificationID;
  253. this.CommodityID = commodityDB.CommodityID;
  254. trans.Complete();
  255. }
  256. }
  257. this.Tags.CommodityID = this.CommodityID;
  258. this.Tags.Save();
  259. }
  260. public new void Update(string updateBy)
  261. {
  262. using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
  263. {
  264. var certificationDB = dataContext.Certifications.SingleOrDefault(x => x.CommodityID == this.CommodityID);
  265. certificationDB.CategoryID = this.Category.CategoryID;
  266. certificationDB.Updated = DateTime.Now;
  267. certificationDB.UpdatedBy = updateBy;
  268. certificationDB.Commodity.SortingID = this.SortingID;
  269. certificationDB.Commodity.Name = this.Name;
  270. certificationDB.Commodity.Code = this.Code;
  271. certificationDB.Commodity.Descr = this.Description;
  272. certificationDB.Commodity.Price = this.Price.Amount;
  273. certificationDB.Commodity.Updated = DateTime.Now;
  274. certificationDB.Commodity.UpdatedBy = updateBy;
  275. dataContext.SubmitChanges();
  276. }
  277. this.Tags.Save();
  278. }
  279. public new static void Delete(int certificationID)
  280. {
  281. int commodityID;
  282. using (TransactionScope trans = new TransactionScope())
  283. {
  284. using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
  285. {
  286. var certificationDB = (from certification in dataContext.Certifications where certification.CertificationID == certificationID select certification).SingleOrDefault();
  287. commodityID = certificationDB.CommodityID;
  288. var cartItemsDB = from cart in dataContext.CartItems where cart.CommodityID == commodityID select cart;
  289. dataContext.CartItems.DeleteAllOnSubmit(cartItemsDB);
  290. dataContext.SubmitChanges();
  291. var tagsSetDB = from tags in dataContext.TagsSets where tags.CommodityID == commodityID select tags;
  292. dataContext.TagsSets.DeleteAllOnSubmit(tagsSetDB);
  293. dataContext.SubmitChanges();
  294. dataContext.Certifications.DeleteOnSubmit(certificationDB);
  295. dataContext.SubmitChanges();
  296. var commodityDB = (from commodity in dataContext.Commodities where commodity.CommodityID == commodityID select commodity).SingleOrDefault();
  297. dataContext.Commodities.DeleteOnSubmit(commodityDB);
  298. dataContext.SubmitChanges();
  299. trans.Complete();
  300. }
  301. }
  302. }
  303. }
  304. }