// GET: StoreShow public ActionResult Index(int?store_id) { string Sql = "SELECT ID,NAME,TYPE,FILE_NAME FROM TB_STORE NATURAL JOIN TB_STORE_TYPE NATURAL JOIN TB_DECORATION WHERE ID= " + store_id.ToString(); List <StoreMes> storemess = db.Database.SqlQuery <StoreMes>(Sql).ToList(); IndexModel.name = storemess.First().NAME; IndexModel.filename = storemess.First().FILE_NAME; IndexModel.types = new List <string>(); foreach (StoreMes item in storemess) { IndexModel.types.Add(item.TYPE); } IndexModel.store_id = store_id; string SqlString = "SELECT * FROM TB_PRODUCT P WHERE P.S_ID = " + store_id.ToString() + "ORDER BY (SELECT SUM(NUM) FROM TB_CONTAINS GROUP BY S_ID,P_ID HAVING S_ID = P.S_ID AND P_ID = P.ID) DESC"; List <TB_PRODUCT> hot_products = db.Database.SqlQuery <TB_PRODUCT>(SqlString).ToList(); int min_num = Math.Min(4, hot_products.Count); int num = 0; int i = 0; while (num < min_num && i < hot_products.Count) { TB_PRODUCT temp_product = hot_products[i]; string Sql2 = "SELECT COUNT(*) FROM TB_CONTAINS WHERE S_ID = " + temp_product.S_ID.ToString() + "AND P_ID = " + temp_product.ID.ToString(); var list = db2.TB_CONTAINS.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).ToList(); if (list.Count > 0) { HotProductView product = new HotProductView(); product.name = temp_product.NAME; product.price = temp_product.PRICE; product.s_id = (int?)temp_product.S_ID; product.id = (int?)temp_product.ID; Pro_pics pics = new Pro_pics(); TB_PRO_PIC pic = pics.TB_PRO_PIC.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).FirstOrDefault(); if (pic != null) { product.picture = pic.PICTURE; } hot_product_models.Add(product); num--; } else { min_num--; min_num = Math.Min(min_num, 4); } i++; } IndexModel.hot_products = hot_product_models; IndexModel.low_products = db.Database.SqlQuery <TB_PRODUCT>("SELECT * FROM TB_PRODUCT WHERE S_ID = " + store_id.ToString() + "AND DISCOUNT_RATE < 0.5").ToList(); IndexModel.high_products = db.Database.SqlQuery <TB_PRODUCT>("SELECT * FROM TB_PRODUCT WHERE S_ID = " + store_id.ToString() + "AND DISCOUNT_RATE < 1 AND DISCOUNT_RATE > 0.5").ToList(); return(View(IndexModel)); }
public ActionResult Index() { string Sql = "SELECT * FROM TB_PRODUCT P ORDER BY (SELECT SUM(NUM) FROM TB_CONTAINS GROUP BY S_ID,P_ID HAVING S_ID = P.S_ID AND P_ID = P.ID) DESC"; List <TB_PRODUCT> hot_products = db.Database.SqlQuery <TB_PRODUCT>(Sql).ToList(); int min_num = Math.Min(8, hot_products.Count); int num = 0; int i = 0; while (num < min_num && i < hot_products.Count) { TB_PRODUCT temp_product = hot_products[i]; string Sql2 = "SELECT COUNT(*) FROM TB_CONTAINS WHERE S_ID = " + temp_product.S_ID.ToString() + "AND P_ID = " + temp_product.ID.ToString(); var list = db.TB_CONTAINS.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).ToList(); if (list.Count > 0) { HotProductView product = new HotProductView(); product.name = temp_product.NAME; product.price = temp_product.PRICE; product.id = (int?)temp_product.ID; product.s_id = (int?)temp_product.S_ID; Pro_pics pics = new Pro_pics(); TB_PRO_PIC pic = pics.TB_PRO_PIC.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).FirstOrDefault(); if (pic != null) { product.picture = pic.PICTURE; } hot_product_models.Add(product); num--; } else { min_num--; min_num = Math.Min(min_num, 8); } i++; } return(View(hot_product_models)); }
public ActionResult Index() { string Sql = "SELECT * FROM TB_PRODUCT P ORDER BY (SELECT SUM(NUM) FROM TB_CONTAINS GROUP BY S_ID,P_ID HAVING S_ID = P.S_ID AND P_ID = P.ID) DESC"; List<TB_PRODUCT> hot_products = db.Database.SqlQuery<TB_PRODUCT>(Sql).ToList(); int min_num = Math.Min(8, hot_products.Count); int num = 0; int i = 0; while (num < min_num && i < hot_products.Count) { TB_PRODUCT temp_product = hot_products[i]; string Sql2 = "SELECT COUNT(*) FROM TB_CONTAINS WHERE S_ID = " + temp_product.S_ID.ToString() + "AND P_ID = " + temp_product.ID.ToString(); var list = db.TB_CONTAINS.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).ToList(); if (list.Count > 0) { HotProductView product = new HotProductView(); product.name = temp_product.NAME; product.price = temp_product.PRICE; product.id = (int?)temp_product.ID; product.s_id = (int?)temp_product.S_ID; Pro_pics pics = new Pro_pics(); TB_PRO_PIC pic = pics.TB_PRO_PIC.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).FirstOrDefault(); if (pic != null) { product.picture = pic.PICTURE; } hot_product_models.Add(product); num--; } else { min_num--; min_num = Math.Min(min_num, 8); } i++; } return View(hot_product_models); }
// GET: StoreShow public ActionResult Index(int? store_id) { string Sql = "SELECT ID,NAME,TYPE,FILE_NAME FROM TB_STORE NATURAL JOIN TB_STORE_TYPE NATURAL JOIN TB_DECORATION WHERE ID= " + store_id.ToString(); List<StoreMes> storemess = db.Database.SqlQuery<StoreMes>(Sql).ToList(); IndexModel.name = storemess.First().NAME; IndexModel.filename = storemess.First().FILE_NAME; IndexModel.types = new List<string>(); foreach (StoreMes item in storemess) { IndexModel.types.Add(item.TYPE); } IndexModel.store_id = store_id; string SqlString = "SELECT * FROM TB_PRODUCT P WHERE P.S_ID = " + store_id.ToString() + "ORDER BY (SELECT SUM(NUM) FROM TB_CONTAINS GROUP BY S_ID,P_ID HAVING S_ID = P.S_ID AND P_ID = P.ID) DESC"; List<TB_PRODUCT> hot_products = db.Database.SqlQuery<TB_PRODUCT>(SqlString).ToList(); int min_num = Math.Min(4, hot_products.Count); int num = 0; int i = 0; while (num < min_num && i < hot_products.Count) { TB_PRODUCT temp_product = hot_products[i]; string Sql2 = "SELECT COUNT(*) FROM TB_CONTAINS WHERE S_ID = " + temp_product.S_ID.ToString() + "AND P_ID = " + temp_product.ID.ToString(); var list = db2.TB_CONTAINS.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).ToList(); if (list.Count > 0) { HotProductView product = new HotProductView(); product.name = temp_product.NAME; product.price = temp_product.PRICE; product.s_id = (int?)temp_product.S_ID; product.id = (int?)temp_product.ID; Pro_pics pics = new Pro_pics(); TB_PRO_PIC pic = pics.TB_PRO_PIC.Where(a => a.P_ID == temp_product.ID && a.S_ID == temp_product.S_ID).FirstOrDefault(); if (pic != null) { product.picture = pic.PICTURE; } hot_product_models.Add(product); num--; } else { min_num--; min_num = Math.Min(min_num, 4); } i++; } IndexModel.hot_products = hot_product_models; IndexModel.low_products = db.Database.SqlQuery<TB_PRODUCT>("SELECT * FROM TB_PRODUCT WHERE S_ID = " + store_id.ToString() + "AND DISCOUNT_RATE < 0.5").ToList(); IndexModel.high_products = db.Database.SqlQuery<TB_PRODUCT>("SELECT * FROM TB_PRODUCT WHERE S_ID = " + store_id.ToString() + "AND DISCOUNT_RATE < 1 AND DISCOUNT_RATE > 0.5").ToList(); return View(IndexModel); }