public string GetAllCategory() { string sql = string.Format(@"SELECT FirstId,FirstName FROM dbo.CpkFirstCategory WHERE FirstName IN('菜系','菜式','食材') ORDER BY FirstId"); //根据用户需求只显示菜系 菜式 食材三种 var q = DataTableToList.ConvertTo <RCategory>(dataContext.ExecuteDataTable(CommandType.Text, sql)); List <RCategory> rlist = new List <RCategory>(); foreach (var item in q) { // sql = string.Format(@"SELECT c.SecondId,c.SecondName,COUNT(1) Total FROM dbo.CpkCaiPinBasicInfo a // JOIN dbo.CpkCaiPinCategory b ON b.CaiPinId = a.CaiPinId // JOIN dbo.CpkSecondCategory c ON c.SecondId=b.SecondCategoryId // WHERE a.IsEnable=1 AND a.IsPublish =1 AND c.IsEnable =1 AND c.IsPublish =1 AND c.FirstId ={0} // GROUP BY c.SecondId,c.SecondName", item.FirstId); sql = string.Format(@"SELECT a.SecondId,a.SecondName, SUM(CASE WHEN b.CaiPinId IS NULL THEN 0 ELSE 1 END) Total FROM CpkSecondCategory a LEFT JOIN CpkCaiPinCategory b ON a.SecondId=b.SecondCategoryId LEFT JOIN CpkCaiPinBasicInfo c ON c.CaiPinId = b.CaiPinId AND c.IsEnable =1 AND c.IsPublish =1 WHERE a.IsEnable=1 AND a.IsPublish =1 AND a.FirstId ={0} GROUP BY a.SecondId,a.SecondName", item.FirstId); DataTable dt2 = dataContext.ExecuteDataTable(CommandType.Text, sql); item.SecondList = dt2; item.Total = dt2.Rows.Count > 0 ? Convert.ToInt16(dt2.Compute("Sum(Total)", "true")) : 0; rlist.Add(item); } return(JsonConvert.SerializeObject(rlist)); }
public string GetById(int ZhuanTiId) { ReturnJson r = new ReturnJson(); string sql = string.Format(@"SELECT a.ZhuanTiId,a.ZhuanTiName,a.ZhuanTiURL,b.ZtContentId,b.Content FROM dbo.CpkZhuanTi a JOIN dbo.CpkZhuanTiContent b ON b.ZhuanTiId = a.ZhuanTiId AND b.IsEnable=1 WHERE a.IsEnable = 1 AND a.ZhuanTiId = {0}", ZhuanTiId); var data = dataContext.ExecuteDataTable(CommandType.Text, sql); var ztList = DataTableToList.ConvertTo <RZhuanTi>(data); foreach (var item in ztList) { item.CaiPinList = GetCaiPinByZhuanTiContentId(item.ZtContentId); } return(JsonConvert.SerializeObject(ztList)); }
public string GetStep1(int CaiPinId) { ReturnJson r = new ReturnJson(); List <RCpkCaiPinStep1> list = new List <RCpkCaiPinStep1>(); string sql = string.Format(@"SELECT CaiPinId,CaiPinName,VideoUrl,VideoImage,Images FROM CpkCaiPinBasicInfo WHERE CaiPinId = {0}", CaiPinId); var data = dataContext.ExecuteDataTable(CommandType.Text, sql); var a = DataTableToList.ConvertTo <RCpkCaiPinStep1>(data).FirstOrDefault(); if (a != null) { a.CategoryList = GetCategoryById(CaiPinId); a.BanKuai = GetBanKuaiByCpId(CaiPinId); return(JsonConvert.SerializeObject(a)); } else { return(""); } }
/// <summary> /// 根据菜品id获取所属分类 /// </summary> /// <param name="CaiPinId"></param> /// <returns></returns> public object GetCategoryById(int CaiPinId) { string sql = string.Format(@" SELECT DISTINCT c.FirstId ,c.FirstName FROM dbo.CpkCaiPinCategory a JOIN dbo.CpkSecondCategory b ON a.SecondCategoryId = b.SecondId JOIN dbo.CpkFirstCategory c ON c.FirstId = b.FirstId WHERE b.IsEnable =1 AND a.CaiPinId ={0};" , CaiPinId); var rCpkCateroryList = DataTableToList.ConvertTo <RCpkCaterory>(dataContext.ExecuteDataTable(CommandType.Text, sql)); //菜品所属一级分类 foreach (var item in rCpkCateroryList) { sql = string.Format(@"SELECT b.SecondId,b.SecondName FROM dbo.CpkCaiPinCategory a JOIN dbo.CpkSecondCategory b ON a.SecondCategoryId = b.SecondId JOIN dbo.CpkFirstCategory c ON c.FirstId = b.FirstId WHERE b.IsEnable =1 AND a.CaiPinId ={0} AND c.FirstId = {1}", CaiPinId, item.FirstId); item.SecondList = dataContext.ExecuteDataTable(CommandType.Text, sql); } return(rCpkCateroryList); }