/// <summary>
/// 重新计算是否有在出售的otc品种
/// </summary>
public static void InitProduct_otc()
{
SOSOshop.BLL.Db bll = new SOSOshop.BLL.Db();
DataSet ds1 = bll.ExecuteDataSet("select id from Tag_PharmAttribute AS tag where tag_id=71 order by id");
SOSOshop.BLL.DrugsBase.Tag_PharmAttribute ta = new SOSOshop.BLL.DrugsBase.Tag_PharmAttribute();
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
int tagid = (int)ds1.Tables[0].Rows[i][0];
int count = ta.GetCount(tagid);
string sql = string.Format(@"IF EXISTS(SELECT * FROM Tag_PharmAttribute_Product_Count WHERE Tag_PharmAttribute_id={1})
UPDATE Tag_PharmAttribute_Product_Count SET Product_Count={0} WHERE Tag_PharmAttribute_id={1}
ELSE
INSERT Tag_PharmAttribute_Product_Count (Tag_PharmAttribute_id,Product_Count) VALUES({1},{0})", count, tagid);
bll.ExecuteNonQuery(sql);
}
}
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));
}
