private async Task <List <Voucher> > GetVouchers(string id) { string sql = MSSqlFunctions.getQueryVoucher(id); using (IDbConnection con = Connection) { con.Open(); var voucherDictionary = new Dictionary <string, Voucher>(); var productDictionary = new Dictionary <int, Product>(); var productCategoryDictionary = new Dictionary <int, ProductCategory>(); var list = await con.QueryAsync <dynamic, Product, ProductCategory, Voucher>( sql, (voucher_, product, productCategory) => { Voucher voucherConverted = VoucherRepository.VoucherConvert(voucher_); Voucher voucherEntry; Product productEntry; ProductCategory productCategoryEntry; if (!voucherDictionary.TryGetValue(voucherConverted.Id, out voucherEntry)) { voucherEntry = voucherConverted; voucherDictionary.Add(voucherConverted.Id, voucherEntry); } if (product != null) { if (!productDictionary.TryGetValue(product.Id, out productEntry)) { productEntry = product; product.productCategory = productCategory; productDictionary.Add(productEntry.Id, productEntry); } voucherEntry.validProducts.Add(product); } if (productCategory != null) { if (!productCategoryDictionary.TryGetValue(productCategory.Id, out productCategoryEntry)) { productCategoryEntry = productCategory; productCategoryDictionary.Add(productCategoryEntry.Id, productCategoryEntry); } voucherEntry.validCategorys.Add(productCategory); } return(voucherEntry); }, splitOn : "Id,Id,Id"); return(list.ToList()); } }
/// <summary> /// Gets the Store by identifier. /// </summary> /// <param name="id">The identifier.</param> /// <returns></returns> public async Task <Store> GetByID(int id) { try { string sql = MSSqlFunctions.getQueryStore(id); using (IDbConnection con = Connection) { con.Open(); var storeDictionary = new Dictionary <int, Store>(); var productDictionary = new Dictionary <int, Product>(); var list = con.Query <Store, StockItem, Product, ProductCategory, Store>( sql, (store, stock, product, productCategory) => { Store storeEntry; Product productEntry; if (!storeDictionary.TryGetValue(store.Id, out storeEntry)) { storeEntry = store; storeDictionary.Add(storeEntry.Id, storeEntry); } if (product != null) { if (!productDictionary.TryGetValue(product.Id, out productEntry)) { productEntry = product; product.productCategory = productCategory; productDictionary.Add(productEntry.Id, productEntry); } stock.product = product; storeEntry.Stock.Add(stock); } return(storeEntry); }, splitOn: "Id") .Distinct() .ToList(); return(list.FirstOrDefault()); } } catch (Exception ex) { throw ex; } }
public async Task <ActionResult <List <String> > > Setup() { using (var connection = this.Connection) { connection.Open(); // Store Entity var result = await connection.QueryAsync <String>(MSSqlFunctions.getCreateStore()); List <String> log_results = result.ToList(); try { var resultFunction = await connection.QueryAsync <String>(MSSqlFunctions.getFunctionStore()); log_results.Add("Store function created"); } catch (SqlException e) { log_results.Add("Error creating store function"); } // Product Category Entity var resultProductCategory = await connection.QueryAsync <String>(MSSqlFunctions.getCreateProductCategory()); log_results = log_results.Union(resultProductCategory.ToList()).ToList(); try { var resultFunction = await connection.QueryAsync <String>(MSSqlFunctions.getFunctionProductCategory()); log_results.Add("Product Category function created"); } catch (SqlException e) { log_results.Add("Error creating Product Category function"); } // Product Entity var resultProduct = await connection.QueryAsync <String>(MSSqlFunctions.getCreateProduct()); log_results = log_results.Union(resultProduct.ToList()).ToList(); try { var resultFunction = await connection.QueryAsync <String>(MSSqlFunctions.getFunctionProduct()); log_results.Add("Product function created"); } catch (SqlException e) { log_results.Add("Error creating Product function"); } // Voucher Entity var resultVoucher = await connection.QueryAsync <String>(MSSqlFunctions.getCreateVoucher()); log_results = log_results.Union(resultVoucher.ToList()).ToList(); try { var resultFunction = await connection.QueryAsync <String>(MSSqlFunctions.getFunctionVoucher()); log_results.Add("Voucher function created"); } catch (SqlException e) { log_results.Add("Error creating Voucher function"); } // Cart Entity var resultCart = await connection.QueryAsync <String>(MSSqlFunctions.getCreateCart()); log_results = log_results.Union(resultCart.ToList()).ToList(); try { var resultFunction = await connection.QueryAsync <String>(MSSqlFunctions.getFunctionCart()); log_results.Add("Cart function created"); } catch (SqlException e) { log_results.Add("Error creating Cart function"); } try { var resultFunction = await connection.QueryAsync <String>(MSSqlFunctions.getFunctionCartItem()); log_results.Add("Cart Item function created"); } catch (SqlException e) { log_results.Add("Error creating Cart Item function"); } // Stock Entity var resultStock = await connection.QueryAsync <String>(MSSqlFunctions.getCreateStock()); log_results = log_results.Union(resultStock.ToList()).ToList(); // Constraint try { var resultFunction = await connection.QueryAsync <String>(MSSqlFunctions.getConstraint()); log_results.Add("Constraints created"); } catch (SqlException e) { log_results.Add("Error creating Constraint"); } return(log_results); } }
public IList <Cart> getCarts(Nullable <int> id) { string sql = MSSqlFunctions.getQueryCart(id); using (IDbConnection con = Connection) { con.Open(); var cartDictionary = new Dictionary <int, Cart>(); var cartItemDictionary = new Dictionary <int, CartItem>(); var productDictionary = new Dictionary <int, Product>(); var productCategoryDictionary = new Dictionary <int, ProductCategory>(); var cartVoucherDictionary = new Dictionary <string, Voucher>(); return(con.Query <Cart, CartItem, Product, ProductCategory, dynamic, Cart>( sql, (cart, cartItem, product, productCategory, cartVoucher) => { Cart cartEntry; CartItem cartItemEntry = null; Product productEntry; ProductCategory productCategoryEntry; if (cartItem != null) { if (!productCategoryDictionary.TryGetValue(productCategory.Id, out productCategoryEntry)) { productCategoryEntry = productCategory; productCategoryDictionary.Add(productCategoryEntry.Id, productCategoryEntry); } if (!productDictionary.TryGetValue(product.Id, out productEntry)) { productEntry = product; productDictionary.Add(productEntry.Id, productEntry); } productEntry.productCategory = productCategoryEntry; if (cartItem != null) { if (!cartItemDictionary.TryGetValue(cartItem.Id, out cartItemEntry)) { cartItemEntry = cartItem; cartItemEntry.Id = cartItem.Id; cartItemEntry.Product = productEntry; cartItemDictionary.Add(cartItemEntry.Id, cartItemEntry); } } } Voucher cartVoucher_ = VoucherRepository.VoucherConvert(cartVoucher); Voucher cartVoucherEntry; if (!cartDictionary.TryGetValue(cart.Id, out cartEntry)) { cartEntry = cart; cartDictionary.Add(cartEntry.Id, cartEntry); } if (cartVoucher_ != null) { if (!cartVoucherDictionary.TryGetValue(cartVoucher_.Id, out cartVoucherEntry)) { cartVoucherEntry = cartVoucher_; cartVoucherDictionary.Add(cartVoucherEntry.Id, cartVoucher_); } cartEntry.addVoucher(cartVoucherEntry); } if (cartItemEntry != null) { cartEntry.Items.Add(cartItemEntry); } return cartEntry; }, splitOn: "Id,Id,Id,Id,Id") .Distinct() .ToList()); } }