public ActionResult Index(int?id) { DateTime StartTime = DateTime.Now, InitTime = StartTime; StringBuilder InitData = new StringBuilder(); if (id == null) { return(Redirect("/")); } ViewBag.ProductID = id.ToString(); SOSOshop.BLL.Product.Product bll = new SOSOshop.BLL.Product.Product(); int Member_Class; Price.GetMemberpermission(out Member_Class); InitData.Append("开始-商品信息:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; bool clearCache = !string.IsNullOrEmpty(Request["clearCache"]);//清除缓存 DataTable PriceTable = bll.GetProduct((int)id, SOSOshop.BLL.Product.Product._ProductInfoColumns_NotIn_PriceTableColumns + "," //品种信息 + SOSOshop.BLL.Product.Product._PriceTableColumns //价格信息 + "(SELECT TOP 1 Image FROM Goods_Picture WHERE Goods_ID=product_online_v.Goods_ID) pro_Picture, " //彩页 + "(SELECT TOP 1 a.Drugsbase_Direct_Context FROM Drugsbase_Direct AS a INNER JOIN DrugsBase AS b ON a.Drugsbase_Direct_ID=b.Drugsbase_Direct_ID WHERE b.Drugsbase_ID=product_online_v.Drugsbase_ID) Drugsbase_Direct, " //说明书 + "created, drug_sensitive,is_cl" //其他 , clearCache); InitData.Append("结束-商品信息:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; if (PriceTable != null && PriceTable.Rows.Count > 0) { //实时销售方式处理 清除缓存 int sellType = 1; int.TryParse(Convert.ToString(PriceTable.Rows[0]["sellType"]), out sellType); int sellType1 = 1; int.TryParse(Convert.ToString(bll.GetProductAttr((int)id, SOSOshop.BLL.Product.Product.sellTypeSql())), out sellType1); if (sellType != sellType1) { clearCache = true; PriceTable = bll.GetProduct((int)id, SOSOshop.BLL.Product.Product._ProductInfoColumns_NotIn_PriceTableColumns + "," //品种信息 + SOSOshop.BLL.Product.Product._PriceTableColumns //价格信息 + "(SELECT TOP 1 Image FROM Goods_Picture WHERE Goods_ID=product_online_v.Goods_ID) pro_Picture, " //彩页 + "(SELECT TOP 1 a.Drugsbase_Direct_Context FROM Drugsbase_Direct AS a INNER JOIN DrugsBase AS b ON a.Drugsbase_Direct_ID=b.Drugsbase_Direct_ID WHERE b.Drugsbase_ID=product_online_v.Drugsbase_ID) Drugsbase_Direct, " //说明书 + "created, drug_sensitive,is_cl" //其他 , clearCache); } PriceTable = PriceTable.GetPriceTable(); SOSOshop.Model.ProductInfo model = bll.ReaderBind(PriceTable.Rows[0], Member_Class); ViewBag.ProductDataRow = PriceTable.Rows[0];//原始数据行 string maxsell = null; if (string.IsNullOrEmpty(maxsell)) { maxsell = "0"; } ViewBag.MaxSell = maxsell; ViewBag.OldPrice = PriceTable.Rows[0]["OrigPrice"]; ViewBag.Price = PriceTable.Rows[0]["Price"]; ViewBag.IsProp = PriceTable.Rows[0]["iscu"]; DateTime addDate = DateTime.MinValue; DateTime.TryParse(PriceTable.Rows[0]["created"].ToString(), out addDate); ViewBag.AddedDate = addDate.ToShortDateString(); ViewBag.spid = PriceTable.Rows[0]["spid"]; ViewBag.pihao = PriceTable.Rows[0]["pihao"]; ViewBag.sxrq = PriceTable.Rows[0]["sxrq"]; SOSOshop.BLL.Db db = new SOSOshop.BLL.Db(); //国家基药 int DrugsBase_bNationalEssentialDrug = 0; if (model.tag_ids.IndexOf(",66,") >= 0) { DrugsBase_bNationalEssentialDrug = 1; } ViewBag.DrugsBase_bNationalEssentialDrug = DrugsBase_bNationalEssentialDrug; InitData.Append("开始-适应症分类:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; //OTC适应症分类 string syzsql = string.Format("select id,name from Tag_PharmAttribute where fullPath like '%/'+convert(varchar,(select top 1 case when ParentId=0 then id else ParentId end from Tag_PharmAttribute where id in(SELECT Tag_PharmAttribute_id FROM Tag_PharmProduct WHERE product_key='d' and product_id={0}) and tag_id=71))+'/%' order by ParentId", model.DrugsBase_ID); //取该商品适应症的二级类型名称 string sql2level = string.Format("select id,name from Tag_PharmAttribute where id in(select id from Tag_PharmAttribute where id in(SELECT Tag_PharmAttribute_id FROM Tag_PharmProduct WHERE product_key='d' and product_id={0}) and tag_id=71)", model.DrugsBase_ID); DataSet ds_fl = db.ExecuteDataSetForCache(syzsql + " " + sql2level, DateTime.Now.AddDays(1)); ViewBag.SyzList = ds_fl.Tables.Count > 0 ? ds_fl.Tables[0] : new DataTable(); ViewBag.SyzName = ""; StringBuilder syzname = new StringBuilder(); bool frist = true; DataTable dt = ds_fl.Tables.Count > 1 ? ds_fl.Tables[1] : new DataTable(); foreach (DataRow dr in dt.Rows) { //适应症产品推荐 if (frist) { string sqlSyzTj = string.Format("select top 4 * from product_online_v where DrugsBase_ID in( select product_id from Tag_PharmProduct where product_key='d' and Tag_PharmAttribute_id={0})", dr["id"]); ViewBag.SyzTjTitle = dr["name"].ToString(); ViewBag.SyzTj = db.ExecuteTableForCache(sqlSyzTj).GetPriceTable(); ViewBag.SyzTjCount = db.ExecuteScalarForCache(sqlSyzTj.Replace("top 4 *", "count(distinct product_id)")); frist = false; } //显示所有贴有标签的类 ViewBag.OtcLink = _101shop.v3.Controllers.HomeController.SearchUrl(int.Parse(dr["id"].ToString()), 1); syzname.AppendFormat("<a href='{0}'>{1}</a> ", _101shop.v3.Controllers.HomeController.SearchUrl(int.Parse(dr["id"].ToString()), 1), dr["name"].ToString()); } ViewBag.SyzName = syzname.ToString(); InitData.Append("结束-适应症分类:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; //获取账户ViewBag.UID,ViewBag.Member_IsLogOn是否登陆?ViewBag.UserType,ViewBag.Member_Type,ViewBag.Member_Class,ViewBag.MemberPermission权限等 BaseController.SetAccount(ViewBag); InitData.Append("会员账户信息:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; //获取其他价格 InitData.Append("其他价格信息:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; decimal RetailPrice = 0; //建议零售价 if (Member_Class != 1) //建议零售价 { RetailPrice = model.RetailPrice; } decimal Price_Temp = 0; decimal.TryParse(PriceTable.Rows[0]["Price"].ToString(), out Price_Temp); //是否批发 ViewBag.is_pf = model.Price_01 > 0; //是否可拆零 ViewBag.is_cl = PriceTable.Rows[0]["is_cl"]; //袋装量 var obj = bll.ExecuteScalar("SELECT BagCapacity FROM dbo.DrugsBase_ZYC WHERE DrugsBase_ID={0}", model.DrugsBase_ID); ViewBag.BagCapacity = obj != null?Math.Round(Convert.ToDecimal(obj), 2) : 1; ViewBag.Price = Price_Temp; ViewBag.ShowPrice = PriceTable.Rows[0]["showPrice"].ToString(); string minsell = string.Empty; if (ViewBag.is_cl == "是") { minsell = PriceTable.Rows[0]["minsell"].ToString(); } else { minsell = "1"; } ViewBag.minsell = minsell; //包装盒、彩页 ViewBag.pro_Image = "" + PriceTable.Rows[0]["Original"]; string pro_Picture = "" + PriceTable.Rows[0]["pro_Picture"]; if (!pro_Picture.ToLower().Contains("/picture/"))//旧的彩页 { pro_Picture = pro_Picture.ToLower().Replace(".jpg", "-sy1.jpg"); } ViewBag.pro_Picture = pro_Picture; //说明书 ViewBag.Drugsbase_Direct = "" + PriceTable.Rows[0]["Drugsbase_Direct"]; string sql = "SELECT a.Stock-ISNULL(b.Stock,0) FROM Product a LEFT JOIN Stock_Lock b ON a.Product_ID = b.Product_ID WHERE a.Product_ID=" + id; //近效期特殊处理 ViewBag.ISExpirationTime = false; if (ExpirationTime) { sql = string.Format("SELECT * FROM Product_ExpirationTime Product_ExpirationTime WHERE product_id={0}", id); DataTable DT_ExpirationTime = bll.ExecuteTable(sql); if (DT_ExpirationTime != null && DT_ExpirationTime.Rows.Count > 0) { ViewBag.ISExpirationTime = true; ViewBag.Goods_Unit = DT_ExpirationTime.Rows[0]["Goods_Unit"].ToString(); //取近效期价格 ViewBag.ShowPrice = DT_ExpirationTime.Rows[0]["Price"].ToString(); } //锁库查询 sql = string.Format("SELECT stock - ISNULL(dbo.fn_select_Expiration_lockStock({0}),0) FROM Product_ExpirationTime WHERE product_id={0}", id); } int Product_Stock = 0; int.TryParse(Convert.ToString(db.ExecuteScalar(sql)), out Product_Stock); //库存取整 if (sellType == 2) { int zbz = 0; int.TryParse(Convert.ToString(PriceTable.Rows[0]["Goods_Pcs_Small"]), out zbz); if (zbz > 0 && Math.Floor((double)Product_Stock / zbz) <= 0) { Product_Stock = 0; } } if (sellType == 3) { int jz = 0; int.TryParse(Convert.ToString(PriceTable.Rows[0]["Goods_Pcs"]), out jz); if (jz > 0 && Math.Floor((double)Product_Stock / jz) <= 0) { Product_Stock = 0; } } ViewBag.Product_Stock = Product_Stock; InitData.Append("实时库存:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; //药品导航 DataTable DrugsBase_Pharm_Of_Product = new SOSOshop.BLL.DrugsBase.DrugsBase_Pharm().GetListByDrugsBase_ID(model.DrugsBase_ID); ViewBag.DrugsBase_Pharm_Of_Product = DrugsBase_Pharm_Of_Product; InitData.Append("药品导航:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; //其他生产厂家 DataTable drugsBase_Manufacturer = new SOSOshop.BLL.DrugsBase.DrugsBase_Manufacturer().GetOhterManufacturerList(50, model.Product_Name, model.DrugsBase_Manufacturer); ViewBag.DrugsBase_Manufacturer_Of_ProductList = drugsBase_Manufacturer; if (drugsBase_Manufacturer != null) { ViewBag.DrugsBase_Manufacturer_Count = DrugsBase_Pharm_Of_Product == null ? 0 : DrugsBase_Pharm_Of_Product.Rows.Count; } InitData.Append("其他生产厂家:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; #region 本月热销排行榜 //本月热销排行榜 ViewBag.Member_Class = Member_Class; try { string sqlhot = string.Empty; if (Member_Class == 1) { sqlhot = "SELECT TOP 8 " + SOSOshop.BLL.Product.Product._PriceTableColumns + "ggy1,drug_sensitive,Product_ID,DrugsBase_Specification,DrugsBase_Manufacturer,Product_Name,Product_Advertisement,Image," + "Product_SaleNum as SaleNum,Goods_ConveRatio_Unit_Name,Goods_ConveRatio_Unit,Goods_ConveRatio,Goods_Unit, DrugsBase_ID " + "FROM product_online_v p " + "where (Price_02 is not null and Price_02!=0.000000) " + "ORDER BY SaleNum DESC"; } else { sqlhot = "SELECT TOP 8 " + SOSOshop.BLL.Product.Product._PriceTableColumns + "ggy1,Product_ID,DrugsBase_Specification,DrugsBase_Manufacturer,Product_Advertisement,Product_Name, Image," + "( " + "SELECT SUM(Product_SaleNum/Goods_pcs) " + "FROM product_online_v " + "WHERE DrugsBase_ID=p.DrugsBase_ID " + ")SaleNum,Goods_ConveRatio_Unit_Name,Goods_ConveRatio_Unit,Goods_ConveRatio,'件' Goods_Unit,drug_sensitive,Product_SaleNum " + "FROM product_online_v p " + "where (Price_01 is not null and Price_01!=0.000000) and DrugsBase_Manufacturer != '云南升和药业股份有限公司' " + "AND p.Goods_Pcs != 1 " + "ORDER BY SaleNum DESC"; } ViewBag.ThisMonthRanking_ProductList = db.ExecuteTableForCache(sqlhot).GetPriceTable(); InitData.Append("本月热销排行榜:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; } catch { ViewBag.ThisMonthRanking_ProductList = new DataTable(); } #endregion #region 分类热销品种 //分类热销品种 ViewBag.DrugsBase_Pharm1_Of_Product_Class = null; ViewBag.DrugsBase_Pharm1_Of_Product_ClassList = null; if (DrugsBase_Pharm_Of_Product != null) { NameValueCollection na = new NameValueCollection(); string[] Pharm_ID_Path = DrugsBase_Pharm_Of_Product.Rows[0]["Pharm_ID_Path"].ToString().Trim('\\').Split('\\'); string[] Pharm_Name_Path = DrugsBase_Pharm_Of_Product.Rows[0]["Pharm_Name_Path"].ToString().Trim('\\').Split('\\'); if (Pharm_ID_Path.Length == Pharm_Name_Path.Length) { for (var i = 0; i < Pharm_Name_Path.Length; i++) { na[Pharm_Name_Path[i]] = Pharm_ID_Path[i]; } } ViewBag.DrugsBase_Pharm1_Of_Product = na; string name = na.Keys.Get(na.Count - 1); int value = int.Parse(na[na.Count - 1]); ViewBag.ThisClass = name; //分类 ViewBag.ThisClassID = value; //分类ID //分类热销品种列表 DataTable ThisClass_ProductList = bll.Get_ThisClass_ProductList(4, value).GetPriceTable(); ViewBag.ThisClass_ProductList = ThisClass_ProductList; ViewBag.ThisClass_ProductList_Count = bll.Get_ThisClass_ProductList_Count(value); } InitData.Append("分类热销品种:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; #endregion #region 厂家的其他品种列表 //厂家的其他品种列表 DataTable ThisDrugsBase_Manufacturer_Of_ProductList = bll.Get_ThisDrugsBase_Manufacturer_Of_ProductList(8, model.DrugsBase_Manufacturer, 0).GetPriceTable(); int ThisDrugsBase_Manufacturer_Of_ProductList_Count = ThisDrugsBase_Manufacturer_Of_ProductList == null ? 0 : ThisDrugsBase_Manufacturer_Of_ProductList.Rows.Count; if (ThisDrugsBase_Manufacturer_Of_ProductList_Count > 0) { foreach (DataRow dr in ThisDrugsBase_Manufacturer_Of_ProductList.Rows) { if ((int)dr["Product_ID"] == model.Product_ID) { ThisDrugsBase_Manufacturer_Of_ProductList.Rows.Remove(dr); break; } } } ViewBag.ThisDrugsBase_Manufacturer_Of_ProductList = ThisDrugsBase_Manufacturer_Of_ProductList; ViewBag.ThisDrugsBase_Manufacturer_Of_ProductList_Count = bll.Get_ThisDrugsBase_Manufacturer_Of_ProductList_Count(model.DrugsBase_Manufacturer, (int)id); InitData.Append("厂家的其他品种列表:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); InitTime = DateTime.Now; #endregion //访问日志 if (User.Identity.IsAuthenticated && ViewBag.UID != null && ViewBag.Member_IsLogOn == true) { MemberBrowserProductContentLog bllLog = new SOSOshop.BLL.MemberBrowserProductContentLog(); bllLog.RecordBrowse(ViewBag.UID, id); //最近浏览过的商品 ViewBag.History_Of_ProductList = bll.Get_History_Of_ProductList(10, (int)ViewBag.UID).GetPriceTable(); } InitData.Append("最近浏览过的商品:" + (DateTime.Now - InitTime).TotalSeconds + "s|"); ViewBag.InitData = InitData + "总共:" + (DateTime.Now - StartTime).TotalSeconds + "s"; return(View(model)); } else { return(View("Error")); } }
public ActionResult Index(string filter, int pageIndex) { #region 控制搜索引擎访问频率(目前设置的十秒钟内最多可以访问10次) string SearchEngines = Public.IsSearchEnginesGet(); if (SearchEngines != null) { SOSOshop.BLL.SearchEngines blls = new SearchEngines(); if (!blls.isPower(SearchEngines)) { Response.StatusCode = 503; return(Content("")); } blls.created = DateTime.Now; blls.Engines = SearchEngines; blls.ip = Request.UserHostAddress; blls.insert(); } #endregion ViewBag.Title = ConfigurationManager.AppSettings["List_Title"]; ViewBag.description = ConfigurationManager.AppSettings["List_Description"]; ViewBag.keywords = ConfigurationManager.AppSettings["List_Key"]; ViewBag.ActuationValue = "查看所有商品"; SOSOshop.BLL.Db db = new Db(); int showpic = 0;//用户对列表状态无选择则为0,大图为1,列表为2. string isshow = Request["show"]; isshow = Library.Lang.Input.Filter(isshow); if (!string.IsNullOrEmpty(isshow)) { if (int.Parse(isshow) == 1) { showpic = 1; } else { showpic = 2; } } //设置是否是大图列表 ViewBag.Show = showpic; //设置排序 string sort = Request["sort"]; sort = Library.Lang.Input.Filter(sort); ViewBag.Sort = 0; string order = " order by Product_ID desc"; if (!string.IsNullOrEmpty(sort)) { switch (int.Parse(sort)) { case 1: order = " order by Product_ClickNum desc"; ViewBag.Sort = 1; break; case 2: ViewBag.Sort = 2; order = " order by Price_01 asc"; #region 买家类型判断 int UID = BaseController.GetUserId(); if (UID > 0) { int Member_Class = 0; //批发 object objMC = db.ExecuteScalarForCache("SELECT Member_Class FROM memberinfo WHERE UID=" + UID); if (objMC != null) { Member_Class = int.Parse(objMC.ToString()); } if (Member_Class == 1) { order = " order by Price_02 desc"; //OTC } } #endregion break; case 3: ViewBag.Sort = 3; order = " order by Product_SaleNum desc"; break; case 4: ViewBag.Sort = 4; order = " order by price_03 asc"; break; } } //定义分页信息 PageInfo pg = new PageInfo(); pg.pageSize = 24; if (pageIndex == 0) { pageIndex = 1; } //设置分类固定长度,增加新的分类后要将长度调整为对应的长度 string SearchUrl = HomeController.SearchUrl(0, 0); int[] keyIds = new int[filter.Split('-').Length]; if (keyIds.Length < 10) { return(Redirect(HomeController.SearchUrl(0, 0))); } int[] akeyIds = (from a in filter.Split('-') select int.Parse(a)).ToArray(); for (int x = 0; x < akeyIds.Length && akeyIds.Length <= keyIds.Length; x++) { keyIds[x] = akeyIds[x]; } // url_filter = string.Join("-", keyIds);//filter; List <string> keywords = new List <string>(); //选择的条件 List <int> keyposit = new List <int>(); //选择条件的位置 #region 拼接sql条件 StringBuilder filterExpression = new StringBuilder(177); filterExpression.Append(" 1=1"); int index = 0; string type = null; //药理药效 if (keyIds[index] != 0) { filterExpression.Append(" and DrugsBase_ID in (select DrugsBase_ID from [DrugsBase_PharmMediNameLink] where [Pharm_ID] in (select [Pharm_ID] from [DrugsBase_Pharm] where [Pharm_ID_Path] like '%\\" + keyIds[index].ToString() + "%'))"); if (keyIds[index] == 1) { keywords.Add("西药"); } else if (keyIds[index] == 583) { keywords.Add("中成药"); } else { if (SearchModel.GetList(SearchModelEnum.药理二级).Where(x => x.id == keyIds[index]).Count() > 0) { keywords.Add(SearchModel.GetList(SearchModelEnum.药理二级).Where(x => x.id == keyIds[index]).First().name); } } keyposit.Add(0); type = "药理药效"; //分类热销品种 SelectPharmById(keyIds[index]); } //适应症 index = 1; if (keyIds[index] != 0) { //filterExpression.AppendFormat(" and DrugsBase_id in(SELECT product_id FROM Tag_PharmProduct WHERE product_key='d' and Tag_PharmAttribute_id={0})", keyIds[index]); string sql_syz = string.Format(" and DrugsBase_id in(SELECT product_id FROM Tag_PharmProduct WHERE Tag_PharmAttribute_id in (select id from Tag_PharmAttribute where fullPath like '%/{0}/%'))", keyIds[index]); filterExpression.Append(sql_syz); var blltag = new SOSOshop.BLL.DrugsBase.Tag_PharmAttribute(); string syz = blltag.GetTagName(keyIds[index]); keywords.Add(blltag.GetKeyWord(keyIds[index])); keyposit.Add(1); type = "适应症"; //列出适应症的二级分类 string syzsql = string.Format("select id,name from Tag_PharmAttribute where fullPath like '%/'+convert(varchar,(select case when ParentId=0 then id else ParentId end from Tag_PharmAttribute where id={0}))+'/%' order by ParentId", keyIds[index]); ViewBag.SyzList = db.ExecuteTableForCache(syzsql); ViewBag.SelectMenuId = keyIds[index]; //分类热销品种 SelectSYZById(keyIds[index], syz, sql_syz); } //厂家数量 index = 2; if (keyIds[index] != 0) { var list = SearchModel.GetList(SearchModelEnum.厂家数量); if (list.Exists(x => x.id == keyIds[index])) { var model = list.Where(x => x.id == keyIds[index]).First(); filterExpression.Append(model.condition); keywords.Add(model.name); keyposit.Add(index); type = "厂家数量"; } } //价格区间 index = 3; if (keyIds[index] != 0) { var list = SearchModel.GetList(SearchModelEnum.价格区间); if (list.Exists(x => x.id == keyIds[index])) { var model = list.Where(x => x.id == keyIds[index]).First(); filterExpression.Append(model.condition); keywords.Add(model.name); keyposit.Add(index); type = "价格区间"; } } //剂型 index = 4; if (keyIds[index] != 0) { var list = SearchModel.GetList(SearchModelEnum.剂型二级); if (list.Exists(x => x.id == keyIds[index])) { var model = list.Where(x => x.id == keyIds[index]).First(); filterExpression.AppendFormat(model.condition == null ? " and DrugsBase_DrugName like('%{0}%')" : model.condition, model.name); keywords.Add(model.name); keyposit.Add(index); type = "剂型"; } } //热门标签 index = 5; if (keyIds[index] != 0) { var list = SearchModel.GetList(SearchModelEnum.热门标签); if (list.Exists(x => x.id == keyIds[index])) { var model = list.Where(x => x.id == keyIds[index]).First(); filterExpression.Append(model.condition); keywords.Add(model.name); keyposit.Add(index); type = "热门标签"; } } //品牌厂家 index = 6; if (keyIds[index] != 0) { var list = SearchModel.GetList(SearchModelEnum.品牌厂家); if (list.Exists(x => x.id == keyIds[index])) { var model = list.Where(x => x.id == keyIds[index]).First(); filterExpression.Append(model.condition); keywords.Add(model.name); keyposit.Add(index); type = "品牌厂家"; } } //中药饮片 index = 8; if (keyIds[index] != 0) { var list = SearchModel.GetList(SearchModelEnum.中药饮片); if (list.Exists(x => x.id == keyIds[index])) { var model = list.Where(x => x.id == keyIds[index]).First(); filterExpression.Append(model.condition); keywords.Add(model.name); keyposit.Add(index); type = "中药饮片"; } } //进口药品 index = 9; if (keyIds[index] != 0) { var list = SearchModel.GetList(SearchModelEnum.进口药品); if (list.Exists(x => x.id == keyIds[index])) { var model = list.Where(x => x.id == keyIds[index]).First(); filterExpression.Append(model.condition); keywords.Add(model.name); keyposit.Add(index); type = "进口药品"; } } #endregion //新品上架的品种必须有库存 if ("1".Equals(Request.QueryString["new"])) { filterExpression.Append(" and Product_ID in (SELECT a.Product_ID FROM dbo.Product a LEFT OUTER JOIN dbo.Stock_Lock b ON a.Product_ID = b.Product_ID WHERE a.Stock-ISNULL(b.Stock,0)>0)"); } //折扣商品列表 if ("2".Equals(Request.QueryString["new"])) { filterExpression.Append(" and Product_ID in (SELECT a.Product_ID FROM dbo.Product a where Discount>0 AND Discount<>1 AND GETDATE() BETWEEN BeginDate AND EndDate )"); } //促销商品列表 if ("3".Equals(Request.QueryString["new"])) { filterExpression.Append(" and Product_ID in (SELECT a.Product_ID FROM dbo.Product a where (CuPrice>0 or (discount>0 and discount<>1)) AND GETDATE() BETWEEN BeginDate AND EndDate )"); } //产品搜索 string search = Request["q"]; if (search != null) { search = search.Trim().Trim('+'); } search = Library.Lang.Input.Filter(search); if (!string.IsNullOrEmpty(search)) { filterExpression.Append(string.Format(" and ([Product_Name] like '%{0}%' or DrugsBase_SimpeCode like '%{0}%' or [DrugsBase_DrugName] like '%{0}%' or [DrugsBase_ProName] like '%{0}%' or [DrugsBase_Manufacturer] like '%{0}%' or [DrugsBase_ApprovalNumber] like '%{0}%' )", search)); type = "搜索"; ViewBag.ActuationValue = string.Format("搜索:" + search); ViewBag.Search = search; ViewBag.Title = search + ConfigurationManager.AppSettings["List_Search_Title"]; ViewBag.description = search + ConfigurationManager.AppSettings["List_Search_Description"]; ViewBag.keywords = search + ConfigurationManager.AppSettings["List_Search_Key"]; } string ypmc = Request["ypmc"]; if (ypmc != null) { ypmc = ypmc.Trim().Trim('+'); } ypmc = Library.Lang.Input.Filter(ypmc); if (!string.IsNullOrEmpty(ypmc)) { filterExpression.Append(string.Format(" and ([Product_Name] like '%{0}%' or DrugsBase_SimpeCode like '%{0}%' or [DrugsBase_DrugName] like '%{0}%' or [DrugsBase_ProName] like '%{0}%' or [DrugsBase_Manufacturer] like '%{0}%' or [DrugsBase_ApprovalNumber] like '%{0}%' )", ypmc)); type = "搜索"; ViewBag.Search = search + " " + ypmc; } string sccj = Request["sccj"]; if (sccj != null) { sccj = sccj.Trim().Trim('+'); } sccj = Library.Lang.Input.Filter(sccj); if (!string.IsNullOrEmpty(sccj)) { filterExpression.Append(string.Format(" and ([DrugsBase_Manufacturer] like '%{0}%')", sccj)); type = "搜索"; ViewBag.Search = search + " " + ypmc + " " + sccj; } //参数限定 string stop = "[Product_bShelves] =1"; //and [DrugsBase_bStop]=0 and [Product_bStop]=0"; filterExpression.Append(" and " + stop); //显示的字段列表 string fields = SOSOshop.BLL.Product.Product._PriceTableColumns + "[Product_ID],[Product_Name],[Product_ClickNum],[Product_SaleNum] ,[Product_State] ," + "DrugsBase_ProName,DrugsBase_Manufacturer,DrugsBase_Specification," + "Goods_ID,Goods_Pcs,Goods_Pcs_Small,Goods_ConveRatio,Goods_Unit,Image,drug_sensitive," + "Goods_ConveRatio_Unit,Goods_ConveRatio_Unit_Name,is_cl," + "stock-ISNULL((select stock from stock_lock where [Product_ID]=pt.[Product_ID]),0) stock_lock," //袋装量 + @"ISNULL(( SELECT ISNULL(BagCapacity, 1.00) AS BagCapacity FROM dbo.DrugsBase_ZYC WHERE DrugsBase_ID = pt.DrugsBase_ID ), 1) AS BagCapacity"; string stock = "case when Stock<=0 or (sellType=2 and Stock<Goods_Pcs_Small) or (sellType=3 and Stock<Goods_Pcs) then '可预订' else '现货' end as stock1"; fields = string.Format("{0},{1} ", fields, stock); //药品列表 string sql = "SELECT TOP " + pg.pageSize + " " + fields + " FROM product_online_v pt WHERE ([Product_ID] NOT IN(SELECT TOP (" + pg.pageSize + " * " + (pageIndex - 1) + ") [Product_ID] FROM product_online_v where " + filterExpression + " " + order + ")) and " + filterExpression + " " + order; //Response.Write(sql); //Response.End(); ViewBag.List = db.ExecuteTableForCache(string.Format(sql, filter), DateTime.Now.AddHours(1)).GetPriceTable(); ViewBag.url = filter; //列表总数 string countsql = "select count(Product_ID) as pagecount from product_online_v where " + filterExpression; //计算总数 DataTable page = db.ExecuteTableForCache(countsql, DateTime.Now.AddHours(1)); double cs = ((int)page.Rows[0]["pagecount"]) / pg.pageSize; //页总数 try { //Response.Write(((int)page.Rows[0]["pagecount"]) / pg.pageSize +" "+ Math.Ceiling((double)(((int)page.Rows[0][0]) / pg.pageSize)) + " " + cs + " " + page.Rows[0]["pagecount"] + " " + pg.pageSize); //Response.End(); ViewBag.PageCount = int.Parse(Math.Ceiling(cs).ToString()); } catch { ViewBag.PageCount = 1; } //当前页 ViewBag.CurrentPage = pageIndex; //记录总数 ViewBag.RecordSize = (int)page.Rows[0]["pagecount"]; //文件url ViewBag.PageURL = filter; ViewBag.Selected = keywords; ViewBag.Posit = keyposit; ViewBag.Type = type; PagedList <DataRow> pl = null; if (ViewBag.List != null) { ViewBag.List = ViewBag.List; pl = new PagedList <DataRow>(ViewBag.List.Select(), pageIndex, pg.pageSize, (int)page.Rows[0]["pagecount"]); } //SEO 复合条件选择 if (keywords.Count > 0 && string.IsNullOrEmpty(search)) { ViewBag.ActuationValue = string.Format("筛选条件:{0}", string.Join("-", keywords)); ViewBag.Title = string.Join("-", keywords) + ConfigurationManager.AppSettings["List_Search2_Title"]; string[] descriptions = ConfigurationManager.AppSettings["List_Search2_Description"].Split('|'); ViewBag.description = string.Format("{0}为您找到 " + string.Join(",", keywords) + " 的药品信息" + page.Rows[0]["pagecount"] + "条;更多网上药品批发,药品采购,药品价格信息登录{1},随时查询,方便快捷,采购就这么简单!", descriptions[0], descriptions[0]); ViewBag.keywords = string.Join(",", keywords) + ConfigurationManager.AppSettings["List_Search2_Key"]; } MemberInfo member = new MemberInfo(); int uid = BaseController.GetUserId(); int member_Class = -1;//客户未登录 if (uid > 0) { member_Class = member.GetModel(uid).Member_Class;//用户类型 0 批发客户,1 OTC批零客户 } ViewBag.Memberclass = member_Class; ViewBag.UID = uid; SOSOshop.BLL.Product.Product bll = new SOSOshop.BLL.Product.Product(); //最近浏览过的商品 if (uid != 0) { ViewBag.History_Of_ProductList = bll.Get_History_Of_ProductList(10, uid).GetPriceTable(); } //热销榜 string sqlhot = string.Empty; if (member_Class == 1) { sqlhot = "SELECT TOP 9 " + SOSOshop.BLL.Product.Product._PriceTableColumns + "Product_SellingPoint,Product_Advertisement,maid1,ggy1,drug_sensitive,Product_ID,DrugsBase_Specification,DrugsBase_Manufacturer,Product_Name,Product_Advertisement,Image," + "Product_SaleNum as SaleNum,Goods_ConveRatio_Unit_Name,Goods_ConveRatio_Unit,Goods_ConveRatio,Goods_Unit,Goods_Unit Goods_Unit1, DrugsBase_ID " + "FROM product_online_v p " + "ORDER BY SaleNum DESC"; } else { sqlhot = "SELECT TOP 9 " + SOSOshop.BLL.Product.Product._PriceTableColumns + "Product_SellingPoint,Product_Advertisement,maid1,ggy1,Product_ID,DrugsBase_Specification,DrugsBase_Manufacturer,Product_Advertisement,Product_Name, Image, " + "( " + "SELECT SUM(Product_SaleNum/Goods_pcs) " + "FROM product_online_v " + "WHERE DrugsBase_ID=p.DrugsBase_ID " + ")SaleNum,'件' Goods_Unit1,Goods_ConveRatio_Unit_Name,Goods_ConveRatio_Unit,Goods_ConveRatio,Goods_Unit,drug_sensitive,Product_SaleNum " + "FROM product_online_v p " + "where (Price_01 is not null and Price_01!=0.000000) " + "AND p.Goods_Pcs != 1 " + "ORDER BY SaleNum DESC"; } try { ViewBag.Hot = db.ExecuteTableForCache(sqlhot).GetPriceTable(); } catch (Exception) { ViewBag.Hot = new DataTable(); } //热门推荐 string sqltj = "select top 2 " + SOSOshop.BLL.Product.Product._PriceTableColumns + "Product_SellingPoint,Product_Advertisement,maid1,ggy1,Product_ID,product_name,DrugsBase_Specification as gg,DrugsBase_Manufacturer,Product_SaleNum/Goods_Pcs as jian,Image,[Goods_ConveRatio] ,[Goods_ConveRatio_Unit] ,[Goods_ConveRatio_Unit_Name],[Goods_Pcs],[Goods_Unit],drug_sensitive,minsell,maxsell from product_online_v where product_id=24 or product_id=37 order by Product_SaleNum desc"; ViewBag.HotTj = db.ExecuteTableForCache(sqltj).GetPriceTable(); ViewBag.ActuationValue += ":pageIndex-" + pageIndex; return(View(pl)); }