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);
}