public string GetMySelfFR()
{
try
{
string sql = "select MySelfFRId,FRName,FRPicUrl from MySelfFR where BeginTime<GETDATE() and EndTime>GETDATE() order by CreateTime";
var q = dataContext.ExecuteDataTable(CommandType.Text, sql);
return(JsonConvert.SerializeObject(q, DateTimeConvent.DateTimehh()));
}
catch (Exception)
{
return("No");
}
}
public string UpdateIsDel(dynamic requestData)
{
try
{
int bannerId = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.BannerId));
//先查询
var banner = (from v in db.Banner
where v.BannerId == bannerId
select v).Single();
db.Banner.Remove(banner);
var count = db.SaveChanges();
if (count > 0)
{
var q = (from v in db.Banner
orderby v.BannerId
select new
{
BannerId = v.BannerId,
BannerType = v.BannerType,
PicUrl = v.PicUrl,
Link = v.Link,
IsDisplay = v.IsDisplay == 1 ? "是" : "否",
PriorityId = v.PriorityId
});
var count1 = q.Count();
var lastq = q.Skip(0).Take(10);
return("{ \"Count\":\"" + count1 + "\",\"data\":" + JsonConvert.SerializeObject(lastq, DateTimeConvent.DateTimehh()) + "}");
}
else
{
return("No");
}
}
catch (Exception)
{
return("No");
}
}
public string GetBannerList(int pageIndex, int pageCount)
{
try
{
if (pageIndex < 1)
{
pageIndex = 1;
}
var q = (from v in db.Banner
orderby v.BannerId
select new
{
BannerId = v.BannerId,
BannerType = v.BannerType,
PicUrl = v.PicUrl,
Link = v.Link,
IsDisplay = v.IsDisplay == 1 ? "是" : "否",
PriorityId = v.PriorityId,
});
var count = q.Count();
var lastq = q.OrderBy(v => v.BannerId).Skip((pageIndex - 1) * pageCount).Take(pageCount);
return("{ \"Count\":\"" + count + "\",\"data\":" + JsonConvert.SerializeObject(lastq, DateTimeConvent.DateTimehh()) + "}");
}
catch (Exception)
{
return("No");
}
}
public string GetDishChefComment(int dishId, int index, int pageSize)
{
var q = (from v in db.DishComment
where v.DishId == dishId
select v);
var count = q.Count();
var lastq = q.OrderByDescending(v => v.CreateTime).Skip((index - 1) * pageSize).Take(pageSize);
//查找HotelName
var lastResult = (from v in lastq
join p in db.OpenIdAssociated on v.OpenId equals p.OpenId
join n in db.RegistMember on p.UserId equals n.MemberId
select new
{
HeadPicUrl = v.HeadPicUrl,
MemebName = v.MemberName,
Comment = v.Comment,
CreateTime = v.CreateTime,
HotelName = n.HotelName
});
count = lastResult.Count();
return("{ \"Count\":\"" + count + "\",\"data\":" + JsonConvert.SerializeObject(lastResult, DateTimeConvent.DateTimehh()) + "}");
}
public string AddDishComment(dynamic requestData)
{
try
{
//增加留言
string addDishComment = JsonConvert.SerializeObject(requestData);
DishComment model = JsonConvert.DeserializeObject <DishComment>(addDishComment);
db.DishComment.Add(model);
db.SaveChanges();
var q = (from v in db.DishComment
where v.DishId == model.DishId
select v);
var count = q.Count();
var lastq = q.OrderByDescending(v => v.CreateTime).Skip(0).Take(3);
//查找HotelName
var lastResult = (from v in lastq
join p in db.OpenIdAssociated on v.OpenId equals p.OpenId
join n in db.RegistMember on p.UserId equals n.MemberId
select new
{
HeadPicUrl = v.HeadPicUrl,
MemebName = v.MemberName,
Comment = v.Comment,
CreateTime = v.CreateTime,
HotelName = n.HotelName
});
return("{ \"Count\":\"" + count + "\",\"data\":" + JsonConvert.SerializeObject(lastResult, DateTimeConvent.DateTimehh()) + "}");
}
catch (Exception)
{
return("{\"Count\":0}");
}
}
public string GetChefStarAll(dynamic requestData)
{
try
{
//var d = ImgHandle.DNS;
string name = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.Name));
string city = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.City));
int pageIndex = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.pageIndex));
int chefActivityId = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.ChefActivityId));
//获取当前活动ID
var qcaID = (from v in db.ChefActivity
where v.IsEnable == 1
select v
).FirstOrDefault();
chefActivityId = qcaID.ChefActivityId;
//获取赛区下城市列表
var sql = string.Format("select AreaName from MatchRegion where ChefActivityId={0} group by AreaName", chefActivityId);
var CityNameList = dataContext.ExecuteDataTable(CommandType.Text, sql);
if (pageIndex == null || pageIndex <= 0)
{
pageIndex = 1;
}
var q = (from v in db.ChefStar
where v.ChefActivityId == chefActivityId
orderby v.PriorityId
select new
{
ChefStarId = v.ChefStarId,
HeadPicUrl = v.HeadPicUrl,
ChefStarName = v.ChefStarName,
ChefStarPosition = v.ChefStarPosition,
ChefActivityId = v.ChefActivityId,
HotelName = v.HotelName,
CityName = v.CityName
});
if (name != null && !string.IsNullOrWhiteSpace(name))
{
q = q.Where(v => v.ChefStarName.Contains(name) || v.HotelName.Contains(name));
//q = q.Where(v => v.HotelName.Contains(name));
}
if (city != null && !string.IsNullOrWhiteSpace(city))
{
q = q.Where(v => v.CityName == city);
}
var count = q.Count();
q = q.Skip((pageIndex - 1) * 6).Take(6);
return("{ \"Count\":\"" + count + "\",\"data\":" + JsonConvert.SerializeObject(q, DateTimeConvent.DateTimehh()) + ",\"CityNameList\":" + JsonConvert.SerializeObject(CityNameList) + "}");
}
catch (Exception)
{
return("No");
}
}
public string FootPirntMember(dynamic requestData)
{
try
{
string openId = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.OpenId));
int pageIndex = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.PageIndex));
int userType = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.UserType));
string name = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.Name));
string sql = "";
//会员
if (userType == 2)
{
sql = string.Format(@"select ECD.PageShort,ECD.PageDetail,ECD.CreateTime,ECD.OpenId,RM.MemberName,RM.MemberId from ECBrowseDetails as ECD
left join OpenIdAssociated as OI on ECD.OpenId=OI.OpenId
left join RegistMember as RM on RM.MemberId=OI.UserId
where ECD.PageType=1 and ECD.OpenId='{0}'
", openId);
}
//队员
else
{
sql = string.Format(@"select ECD.PageShort,ECD.PageDetail,ECD.CreateTime,ECD.OpenId,RM.MemberName,RM.MemberId from ECBrowseDetails as ECD
left join OpenIdAssociated as OI on ECD.OpenId=OI.OpenId
left join RegistMember as RM on RM.MemberId=OI.UserId
where ECD.PageType=1
and ECD.OpenId in ( select OpenIdAssociated.OpenId from OpenIdAssociated join ( select MemberId from OpenIdAssociated
left join RegistCode on OpenIdAssociated.UserId=RegistCode.SalemanId
where OpenIdAssociated.OpenId='{0}')A on OpenIdAssociated.UserId=A.MemberId)
", openId);
}
// order by ECD.CreateTime desc
if (!string.IsNullOrEmpty(name))
{
//会员
if (userType == 2)
{
sql = sql + "and ( ECD.PageShort like '%" + name + "%' or ECD.PageDetail like '%" + name + "%' ) order by ECD.CreateTime desc";
}
//队员
if (userType == 1)
{
sql = sql + "and ( ECD.PageShort like '%" + name + "%' or ECD.PageDetail like '%" + name + "%' or RM.MemberName like '%" + name + "%' ) order by ECD.CreateTime desc";
}
}
else
{
sql += " order by ECD.CreateTime desc";
}
var q = dataContext.ExecuteDataTable(CommandType.Text, sql);
var lastq = q;
if (pageIndex > 0)
{
lastq = imgHandle.GetPagedTable(q, pageIndex, 10);
}
return(JsonConvert.SerializeObject(lastq, DateTimeConvent.DateTimehh()));
}
catch (Exception)
{
return("No");
}
}
public string GetMySelfDishKu(dynamic requestData)
{
try
{
//审核状态，-1，表示全部，0、待审核 1、审核通过 2、审核不通过
int isApply = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.IsApply));
//上传时间
string beginTime = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.BeginTime));
string endTime = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.EndTime));
//会员姓名，菜品名，产品名称
string name = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.Name));
//页数
int pageIndex = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.PageIndex));
string sql = string.Format(@"select MyDK.MemberId,RM.MemberName,MyDK.MySelfDishId,MyDK.DishName,FR.FRName,MyDK.CreateTime,MyDK.IsApply ,MyDK.PhoneNum from MySelfDishKu as MyDK
left join RegistMember as RM on MyDK.MemberId=RM.MemberId
left join ( select B.MySelfDishKuId , FRName=(stuff( (select ' '+A.FRName from (select role.*,FR.FRName from MySelfFRRole as role left join MySelfFR as FR on role.MySelfFRId=FR.MySelfFRId)as A where B.MySelfDishKuId=A .MySelfDishKuId for xml path('')),1, 1, ''))
from ( select role.*,FR.FRName from MySelfFRRole as role left join MySelfFR as FR on role.MySelfFRId=FR.MySelfFRId) as B group by B.MySelfDishKuId) As FR on MyDK.MySelfDishId=FR.MySelfDishKuId where 1=1");
if (isApply > -1)
{
sql = sql + " and MyDK.IsApply= " + isApply;
}
if (!string.IsNullOrEmpty(beginTime))
{
sql = sql + " and MyDK.CreateTime>='" + beginTime + "' ";
}
if (!string.IsNullOrEmpty(endTime))
{
sql = sql + " and MyDK.CreateTime<='" + endTime + "' ";
}
if (!string.IsNullOrEmpty(name))
{
sql = sql + " and (RM.MemberName like '%" + name + "%' or MyDK.DishName like '%" + name + "%' or FR.FRName like '%" + name + "%') ";
}
sql = sql + " order by MyDK.CreateTime desc";
var q = dataContext.ExecuteDataTable(CommandType.Text, sql);
int totalCount = q.Rows.Count;
if (pageIndex > 0)
{
q = imgHandle.GetPagedTable(q, pageIndex, 10);
}
return("{\"totalCount\":" + totalCount + ",\"Info\":" + JsonConvert.SerializeObject(q, DateTimeConvent.DateTimehh()) + "}");
}
catch (Exception)
{
return("No");
}
}
public string GetDishComment(int dishId, int index, int pageSize)
{
try
{
var q = (from v in db.DishComment
where v.DishId == dishId
select new
{
MemberName = v.MemberName,
HeadPicUrl = v.HeadPicUrl,
CreateTime = v.CreateTime,
Comment = v.Comment
});
var count = q.Count();
var lastq = q.OrderByDescending(v => v.CreateTime).Skip((index - 1) * pageSize).Take(pageSize);
return("{ \"Count\":\"" + count + "\",\"data\":" + JsonConvert.SerializeObject(lastq, DateTimeConvent.DateTimehh()) + "}");
}
catch (Exception)
{
return("No");
}
}
public string GetMyCollect(dynamic requestData)
{
try
{
string openId = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.OpenId));
int memberId = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.MemberId));
int pageIndex = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.PageIndex));
string sql = string.Format(@"select * from
(
select DCL.DishId,DCL.OpenId,DCL.DishType,DCL.CreateTime,DT.DishName,DT.DishPicUrl,DT.VisitCount,T.TutorName as PeopleName,T.TutorHotel as HotelName,1 as PeopleType from DishCollectLog as DCL
left join DishTutor as DT on DCL.DishId=DT.TutorDishId
left join Tutor as T on DT.TutorId=T.TutorId
where 1=1 and DCL.DishId between 200000 and 500000-1
and DCL.OpenId='{0}'
union all
---收藏厨师菜品
select DCL.DishId,DCL.OpenId,DCL.DishType,DCL.CreateTime,DC.DishName,DC.DishPicUrl,DC.VisitCount,RM.MemberName as PeopleName,RM.HotelName as HotelName, 2 as PeopleType from DishCollectLog as DCL
left join DishChef as DC on DCL.DishId=DC.DishChefId
left join Chef as C on DC.ChefId=C.ChefId
left join RegistMember as RM on C.MemberId=RM.MemberId
where 1=1 and DCL.DishId between 500000 and 1000000
and DCL.OpenId='{0}'
union all
---收藏星厨菜品
select DCL.DishId,DCL.OpenId,DCL.DishType,DCL.CreateTime,DCS.DishName,DCS.DishPicUrl,DCS.VisitCount,CS.ChefStarName as PeopleName,CS.HotelName as HotelName, 3 as PeopleType from DishCollectLog as DCL
left join DishChefStar as DCS on DCL.DishId=DCS.DishChefStarId
left join ChefStar as CS on DCS.ChefStarId=CS.ChefStarId
where 1=1 and DCL.DishId between 80000 and 150000
and DCL.OpenId='{0}'
) as Collect
order by Collect.CreateTime desc", openId);
var q = dataContext.ExecuteDataTable(CommandType.Text, sql);
int totalCount = q.Rows.Count;
if (pageIndex > 0)
{
q = imgHandle.GetPagedTable(q, pageIndex, 10);
}
return("{\"totalCount\":" + totalCount + ",\"Info\":" + JsonConvert.SerializeObject(q, DateTimeConvent.DateTimehh()) + "}");
}
catch (Exception)
{
return("No");
}
//return null;
}
public string GetMy(dynamic requestData)
{
try
{
string openId = JsonConvert.DeserializeObject <string>(JsonConvert.SerializeObject(requestData.OpenId));
int memberId = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.MemberId));
int pageIndex = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.PageIndex));
string sql = string.Format(@"select * from(
select DC.DishChefId as DishId, C.MemberId,DC.DishType,DC.ApplyTime ,DC.DishName,DC.DishPicUrl,DC.VisitCount ,2 as PeopleType ,case when DCL.DishId is not null then 't' else 'f' end as IsCollect
from DishChef as DC
left join Chef as C on DC.ChefId=C.ChefId
left join
(select * from DishCollectLog where OpenId='{0}')as DCL on DC.DishChefId=DCL.DishId
where C.MemberId={1} and DC.IsApply=1
union all
select MS.MySelfDishId as DishId,MS.MemberId,MS.DishType,MS.ApplyTime,MS.DishName,MS.DishPicUrl,MS.VisitCount,4 as PeopleType ,case when DCL.DishId is not null then 't' else 'f' end as IsCollect from MySelfDishKu as MS
left join
(select * from DishCollectLog where OpenId='{0}')as DCL on MS.MySelfDishId=DCL.DishId
where MS.MemberId={1} and MS.IsApply=1
) as my order by my.ApplyTime desc", openId, memberId);
var q = dataContext.ExecuteDataTable(CommandType.Text, sql);
int totalCount = q.Rows.Count;
if (pageIndex > 0)
{
q = imgHandle.GetPagedTable(q, pageIndex, 10);
}
return("{\"totalCount\":" + totalCount + ",\"Info\":" + JsonConvert.SerializeObject(q, DateTimeConvent.DateTimehh()) + "}");
}
catch (Exception)
{
return("No");
}
}
public string GetCultivateComment(dynamic requestData)
{
try
{
int cuId = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.CuInterId));
int pageIndex = JsonConvert.DeserializeObject <int>(JsonConvert.SerializeObject(requestData.PageIndex));
if (pageIndex <= 0)
{
pageIndex = 1;
}
var q = (from v in db.CultivateComment
where v.CuInterId == cuId
select new
{
HeadPicUrl = v.HeadPicUrl,
NickName = v.NickName,
Comment = v.Comment,
CreateTime = v.CreateTime
});
var count = q.Count();
q = q.OrderByDescending(v => v.CreateTime).Skip((pageIndex - 1) * 3).Take(3);
return("{ \"Count\":\"" + count + "\",\"data\":" + JsonConvert.SerializeObject(q, DateTimeConvent.DateTimehh()) + "}");
}
catch (Exception)
{
return("No");
}
}