public IQueryable <sline_region_province> GetProvince()
{
var db = new LTHWMysqlModel();
var provinces = from p in db.sline_region_province select p;
return(provinces);
}
public IQueryable <sline_region_county> GetCountyByCity(int cityid)
{
var db = new LTHWMysqlModel();
var countys = from c in db.sline_region_county where c.cityid == cityid select c;
return(countys);
}
public IQueryable <sline_region_city> GetCityByProvince(int provid)
{
var db = new LTHWMysqlModel();
var citys = from c in db.sline_region_city where c.provid == provid select c;
return(citys);
}
public IQueryable <TripListEntity> GetPage(TripQueryParam pageParam)
{
var db = new LTHWMysqlModel();
var trips = from a in db.sline_line
select new TripListEntity
{
id = a.id,
attrid = a.attrid,
kindlist = a.kindlist,
lineday = a.lineday,
litpic = a.litpic,
storeprice = a.storeprice,
title = a.title,
modtime = a.modtime
};
if (!string.IsNullOrEmpty(pageParam.title))
{
trips = trips.Where(p => p.title.Contains(pageParam.title));
}
if (!string.IsNullOrEmpty(pageParam.kindlist))
{
trips = trips.Where(p => ("," + p.kindlist + ",").Contains(("," + pageParam.kindlist + ",")));
}
if (!string.IsNullOrEmpty(pageParam.attrid))
{
trips = trips.Where(p => ("," + p.attrid + ",").Contains(("," + pageParam.attrid + ",")));
}
pageParam.total = trips.Count();
if (!string.IsNullOrEmpty(pageParam.orderprice))
{
if (pageParam.isDesc)
{
trips = trips.OrderByDescending(c => c.storeprice).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
trips = trips.OrderBy(c => c.storeprice).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
}
else
{
if (pageParam.isDesc)
{
trips = trips.OrderByDescending(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
trips = trips.OrderBy(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
}
return(trips);
}
public sline_article GetById(int id)
{
using (var db = new LTHWMysqlModel())
{
var articles = from a in db.sline_article
where a.id == id
select a;
return(articles.FirstOrDefault());
}
}
public List <sline_spot_attr> GetSpotAttrTree(long pid)
{
var db = new LTHWMysqlModel();
var query = from c in db.sline_spot_attr
where c.pid == pid && c.isopen == 1
select c;
return(query.ToList().Concat(query.ToList().SelectMany(t => GetSpotAttrTree(t.id))).ToList());
}
/// <summary>
/// 获取当前线程内的数据上下文，如果当前线程内没有上下文，那么创建一个上下文，
/// </summary>
/// <returns>当前线程内的数据上下文</returns>
public static DbContext GetCurrentDbContext()
{
DbContext currentContext = CallContext.GetData("CurrentDbContext") as DbContext;
if (currentContext == null)
{
currentContext = new LTHWMysqlModel();
CallContext.SetData("CurrentDbContext", currentContext);
}
return(currentContext);
}
/// <summary>
/// 根据传入的mid，用pid条件查询
/// </summary>
/// <param name="mid_pid">!string.isNullOrEmtpy(pid)?(mid+","+pid):mid</param>
/// <param name="thirdfrom">第三方来源，微信wx，可空</param>
/// <returns></returns>
public List <UserInfoEntity> GetUserInfoListByPid(string mid_pid, string thirdfrom)
{
using (var db = new LTHWMysqlModel())
{
var users = from u in db.sline_member
join t in db.sline_member_third on u.mid equals t.mid into temp
from tt in temp.DefaultIfEmpty()
where ("-" + u.pid).Contains("-" + mid_pid)
select new UserInfoEntity
{
mid = u.mid,
checkmail = u.checkmail,
checkphone = u.checkphone,
province = u.province,
city = u.city,
cardid = u.cardid,
address = u.address,
postcode = u.postcode,
connectid = u.connectid,
email = u.email,
@from = u.@from,
jifen = u.jifen,
joinip = u.joinip,
jointime = u.jointime,
litpic = u.litpic,
loginip = u.loginip,
logintime = u.logintime,
mobile = u.mobile,
money = u.money,
mtype = u.mtype,
nickname = u.nickname,
openid = tt == null ? "" : tt.openid,
wxfrom = tt == null ? "" : tt.@from,
wxnickname = tt == null ? "" : tt.nickname,
pid = u.pid,
pwd = u.pwd,
rank = u.rank,
regtype = u.regtype,
remarks = u.remarks,
safeanswer = u.safeanswer,
safequestion = u.safequestion,
sex = u.sex,
truename = u.truename
};
if (!string.IsNullOrEmpty(thirdfrom))
{
users = users.Where(u => u.wxfrom == thirdfrom);
}
return(users.ToList <UserInfoEntity>());
}
}
public List <sline_destinations> GetTree(long pid)
{
var db = new LTHWMysqlModel();
//var query = from c in db.sline_destinations
// where c.pid == pid
// select new TreeObject { id=c.id,text=c.kindname, nodes= GetTree(c.id) };
//return query.ToList();
var query = from c in db.sline_destinations
where c.pid == pid
select c;
return(query.ToList().Concat(query.ToList().SelectMany(t => GetTree(t.id))).ToList());
}
public IQueryable <sline_article> GetPage(ArticleQueryParam pageParam)
{
try
{
var db = new LTHWMysqlModel();
var articles = from a in db.sline_article
select a;
if (!string.IsNullOrEmpty(pageParam.title))
{
articles = articles.Where(p => p.title.Contains(pageParam.title));
}
if (!string.IsNullOrEmpty(pageParam.themelist))
{
articles = articles.Where(p => ("," + p.themelist + ",").Contains(("," + pageParam.themelist + ",")));
}
if (!string.IsNullOrEmpty(pageParam.kindlist))
{
articles = articles.Where(p => ("," + p.kindlist + ",").Contains(("," + pageParam.kindlist + ",")));
}
if (!string.IsNullOrEmpty(pageParam.attrid))
{
articles = articles.Where(p => ("," + p.attrid + ",").Contains(("," + pageParam.attrid + ",")));
}
pageParam.total = articles.Count();
if (pageParam.isDesc)
{
articles = articles.OrderByDescending(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
articles = articles.OrderBy(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
return(articles);
}
catch (Exception ex)
{
LogHelper.Error(this, ("文章：" + ex.Message + ex.InnerException.Message));
return(null);
}
}
public TripInfoEntity GetById(int id)
{
using (var db = new LTHWMysqlModel())
{
var tripInfo = new TripInfoEntity();
var trips = from a in db.sline_line where a.id == id select a;
tripInfo.Info = trips.FirstOrDefault();
var contentList = from b in db.sline_line_jieshao
where b.lineid == id
select b;
tripInfo.ContentList = contentList.ToList();
if (tripInfo.Info != null)
{
var kinds = tripInfo.Info.kindlist.Split(',');
var kindlist = new List <int>();
foreach (var k in kinds)
{
kindlist.Add(int.Parse(k));
}
var mudidiList = from b in db.sline_destinations
from c in kindlist
where b.id == c
select b;
tripInfo.MudidiList = mudidiList.ToList();
var attrs = tripInfo.Info.attrid.Split(',');
var alist = new List <int>();
foreach (var a in attrs)
{
alist.Add(int.Parse(a));
}
var attrList = from b in db.sline_line_attr
from c in alist
where b.id == c
select b;
tripInfo.AttrList = attrList.ToList();
}
return(tripInfo);
}
}
public SightInfoEntity GetById(int id)
{
using (var db = new LTHWMysqlModel())
{
var sights = from b in db.sline_spot_ticket
join a in db.sline_spot on b.spotid equals a.id
join c in db.sline_spot_ticket_type on b.tickettypeid equals c.id into temp
from tt in temp.DefaultIfEmpty()
where b.id == id
select new SightInfoEntity
{
id = b.id,
spotid = b.spotid,
sellprice = b.sellprice,
ourprice = b.ourprice,
kindname = tt == null ? "" : tt.kindname,
address = a.address,
aid = a.aid,
booknotice = a.booknotice,
content = a.content,
friendtip = a.friendtip,
getway = a.getway,
modtime = a.modtime,
piclist = a.piclist,
price = a.price,
sellpoint = a.sellpoint,
shortname = a.shortname,
title = a.title,
webid = a.webid,
attrid = a.attrid,
bookcount = a.bookcount,
recommendnum = a.recommendnum,
satisfyscore = a.satisfyscore,
lat = a.lat,
lng = a.lng,
litpic = a.litpic,
kindlist = a.kindlist
};
return(sights.FirstOrDefault());
}
}
public IQueryable <sline_notes> GetPage(NotesQueryParams pageParam)
{
try
{
var db = new LTHWMysqlModel();
var notes = from a in db.sline_notes
select a;
if (!string.IsNullOrEmpty(pageParam.title))
{
notes = notes.Where(p => p.title.Contains(pageParam.title));
}
if (pageParam.memberid > 0)
{
notes = notes.Where(p => p.memberid == pageParam.memberid);
}
pageParam.total = notes.Count();
if (pageParam.isDesc)
{
notes = notes.OrderByDescending(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
notes = notes.OrderBy(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
return(notes);
}
catch (Exception ex)
{
LogHelper.Error(this, ("文章：" + ex.Message + ex.InnerException.Message));
return(null);
}
}
public HotelInfoEntity GetById(int id)
{
using (var db = new LTHWMysqlModel())
{
var hotels = from a in db.sline_hotel
where a.id == id
select new HotelInfoEntity
{
address = a.address,
aid = a.aid,
content = a.content,
modtime = a.modtime,
piclist = a.piclist,
price = a.price,
sellpoint = a.sellpoint,
title = a.title,
webid = a.webid,
attrid = a.attrid,
bookcount = a.bookcount,
recommendnum = a.recommendnum,
satisfyscore = a.satisfyscore,
lat = a.lat,
lng = a.lng,
kindlist = a.kindlist,
litpic = a.litpic,
aroundspots = a.aroundspots,
decoratetime = a.decoratetime,
description = a.description,
equipment = a.equipment,
fuwu = a.fuwu,
hotelrankid = a.hotelrankid,
id = a.id,
notice = a.notice,
opentime = a.opentime,
telephone = a.telephone,
traffic = a.traffic
};
var hotel = hotels.FirstOrDefault();
if (hotel != null)
{
var roomList = from b in db.sline_hotel_room
where b.hotelid == id
select b;
hotel.RoomList = roomList.ToList();
var kinds = hotel.kindlist.Split(',');
var kindlist = new List <int>();
foreach (var k in kinds)
{
kindlist.Add(int.Parse(k));
}
var mudidiList = from b in db.sline_destinations
from c in kindlist
where b.id == c
select b;
hotel.MudidiList = mudidiList.ToList();
var attrs = hotel.attrid.Split(',');
var alist = new List <int>();
foreach (var a in attrs)
{
alist.Add(int.Parse(a));
}
var attrList = from b in db.sline_hotel_attr
from c in alist
where b.id == c
select b;
hotel.AttrList = attrList.ToList();
}
return(hotel);
}
}
/// <summary>
/// 根据会员id获取分销关联上下级
/// </summary>
/// <param name="mid"></param>
/// <param name="thirdfrom">第三方来源，微信wx，可空</param>
/// <returns></returns>
public FenxiaoGuanlianUsersEntity GetFenxiaoGuanlianUsers(int mid, string thirdfrom)
{
using (var db = new LTHWMysqlModel())
{
var fxglUserInfo = new FenxiaoGuanlianUsersEntity();
var user = db.sline_member.FirstOrDefault(u => u.mid == mid);
if (user != null && !string.IsNullOrEmpty(user.pid))
{
//取前五名
var arrPid = user.pid.Split(',');
int firstpid = arrPid.Length > 0 ? int.Parse(arrPid[0]) : 0,
secondpid = arrPid.Length > 1 ? int.Parse(arrPid[1]) : 0,
thirdpid = arrPid.Length > 2 ? int.Parse(arrPid[2]) : 0,
forthpid = arrPid.Length > 3 ? int.Parse(arrPid[3]) : 0,
fifthpid = arrPid.Length > 4 ? int.Parse(arrPid[4]) : 0;
var s_users = from u in db.sline_member
join t in db.sline_member_third on u.mid equals t.mid into temp
from tt in temp.DefaultIfEmpty()
where (u.mid == firstpid || u.mid == secondpid || u.mid == thirdpid || u.mid == forthpid || u.mid == fifthpid)
select new UserInfoEntity
{
mid = u.mid,
checkmail = u.checkmail,
checkphone = u.checkphone,
province = u.province,
city = u.city,
cardid = u.cardid,
address = u.address,
postcode = u.postcode,
connectid = u.connectid,
email = u.email,
@from = u.@from,
jifen = u.jifen,
joinip = u.joinip,
jointime = u.jointime,
litpic = u.litpic,
loginip = u.loginip,
logintime = u.logintime,
mobile = u.mobile,
money = u.money,
mtype = u.mtype,
nickname = u.nickname,
openid = tt == null ? "" : tt.openid,
wxfrom = tt == null ? "" : tt.@from,
wxnickname = tt == null ? "" : tt.nickname,
pid = u.pid,
pwd = u.pwd,
rank = u.rank,
regtype = u.regtype,
remarks = u.remarks,
safeanswer = u.safeanswer,
safequestion = u.safequestion,
sex = u.sex,
truename = u.truename
};
if (!string.IsNullOrEmpty(thirdfrom))
{
s_users = s_users.Where(u => u.wxfrom == thirdfrom);
}
fxglUserInfo.SuperiorUsers = s_users.ToList <UserInfoEntity>();
}
fxglUserInfo.JuniorUsers = GetUserInfoListByPid(mid.ToString(), thirdfrom);
return(fxglUserInfo);
}
}
public IQueryable <SightListEntity> GetPage(SightQueryParam pageParam)
{
var db = new LTHWMysqlModel();
var sights = from b in db.sline_spot_ticket
join a in db.sline_spot on b.spotid equals a.id
select new SightListEntity
{
id = b.id,
spotid = b.spotid,
sellprice = b.sellprice,
ourprice = b.ourprice,
address = a.address,
friendtip = a.friendtip,
getway = a.getway,
modtime = a.modtime,
piclist = a.piclist,
sellpoint = a.sellpoint,
shortname = a.shortname,
title = a.title,
attrid = a.attrid,
bookcount = a.bookcount,
recommendnum = a.recommendnum,
satisfyscore = a.satisfyscore,
lat = a.lat,
lng = a.lng,
litpic = a.litpic,
kindlist = a.kindlist
};
if (!string.IsNullOrEmpty(pageParam.title))
{
sights = sights.Where(p => p.title.Contains(pageParam.title));
}
if (!string.IsNullOrEmpty(pageParam.kindlist))
{
sights = sights.Where(p => ("," + p.kindlist + ",").Contains(("," + pageParam.kindlist + ",")));
}
if (!string.IsNullOrEmpty(pageParam.attrid))
{
sights = sights.Where(p => ("," + p.attrid + ",").Contains(("," + pageParam.attrid + ",")));
}
pageParam.total = sights.Count();
if (!string.IsNullOrEmpty(pageParam.orderprice))
{
if (pageParam.isDesc)
{
sights = sights.OrderByDescending(c => c.ourprice).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
sights = sights.OrderBy(c => c.ourprice).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
}
else
{
if (pageParam.isDesc)
{
sights = sights.OrderByDescending(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
sights = sights.OrderBy(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
}
return(sights);
}
/// <summary>
/// 增加微信会员
/// </summary>
/// <param name="wxUserInfoEntity"></param>
/// <returns>返回会员id</returns>
public int EditWeixinUser(WXUserInfoEntity wxUserInfoEntity)
{
using (var db = new LTHWMysqlModel())
{
LogHelper.Info(this, "编辑微信会员！");
int res = 0;
var tran = db.Database.BeginTransaction(); //开启事务
try
{
var t_member = db.sline_member_third.FirstOrDefault(m => m.openid == wxUserInfoEntity.openid);
if (t_member != null && t_member.openid == wxUserInfoEntity.openid)
{//已添加到数据库
t_member.headimgurl = wxUserInfoEntity.headimgurl;
t_member.status = wxUserInfoEntity.status;
if (t_member.status == 1)
{//关注时修改
t_member.nickname = wxUserInfoEntity.nickname;
t_member.headimgurl = wxUserInfoEntity.headimgurl;
t_member.subscribetime = wxUserInfoEntity.subscribetime;
LogHelper.Info(this, "微信会员重新关注！");
}
else
{//取消关注时修改
t_member.unsubscribetime = wxUserInfoEntity.unsubscribetime;
LogHelper.Info(this, "微信会员取消关注！");
}
db.SaveChanges();
}
else
{
if (wxUserInfoEntity.status == 0)
{//如果没加入到数据库，并且是取消关注时，直接返回
return(1);
}
var member = new sline_member
{
nickname = wxUserInfoEntity.nickname,
pwd = wxUserInfoEntity.pwd,
connectid = wxUserInfoEntity.openid,
from = string.IsNullOrEmpty(wxUserInfoEntity.from) ? "wx" : wxUserInfoEntity.from,//wx
};
if (!string.IsNullOrEmpty(wxUserInfoEntity.recommendopenid))
{//有推荐人时，添加推荐人
var pusers = from u in db.sline_member
from t in db.sline_member_third
where u.mid == t.mid && t.openid == wxUserInfoEntity.recommendopenid
select u;
var puser = pusers.FirstOrDefault();
if (puser != null)
{
member.pid = puser.mid.ToString() + (string.IsNullOrEmpty(puser.pid) ? "" : "," + puser.pid);
}
}
db.sline_member.Add(member);
db.SaveChanges();
res = member.mid;
var member_third = new sline_member_third
{
mid = res,
from = string.IsNullOrEmpty(wxUserInfoEntity.from) ? "wx" : wxUserInfoEntity.from,//wx
nickname = wxUserInfoEntity.nickname,
openid = wxUserInfoEntity.openid,
headimgurl = wxUserInfoEntity.headimgurl,
status = wxUserInfoEntity.status,
subscribetime = wxUserInfoEntity.subscribetime
};
db.sline_member_third.Add(member_third);
db.SaveChanges();
LogHelper.Info(this, "增加微信会员！");
}
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
catch (Exception ex)
{
tran.Rollback(); //出错就回滚
res = 0;
LogHelper.Error(this, ex.Message);
}
LogHelper.Info(this, "结束编辑微信会员！");
return(res);
}
}
public IQueryable <HotelListEntity> GetPage(HotelQueryParam pageParam)
{
var db = new LTHWMysqlModel();
var hotels = from a in db.sline_hotel
select new HotelListEntity
{
address = a.address,
modtime = a.modtime,
piclist = a.piclist,
price = a.price,
sellpoint = a.sellpoint,
title = a.title,
attrid = a.attrid,
bookcount = a.bookcount,
recommendnum = a.recommendnum,
satisfyscore = a.satisfyscore,
lat = a.lat,
lng = a.lng,
kindlist = a.kindlist,
litpic = a.litpic,
decoratetime = a.decoratetime,
hotelrankid = a.hotelrankid,
id = a.id,
opentime = a.opentime,
telephone = a.telephone
};
if (!string.IsNullOrEmpty(pageParam.title))
{
hotels = hotels.Where(p => p.title.Contains(pageParam.title));
}
if (!string.IsNullOrEmpty(pageParam.kindlist))
{
hotels = hotels.Where(p => ("," + p.kindlist + ",").Contains(("," + pageParam.kindlist + ",")));
}
if (!string.IsNullOrEmpty(pageParam.attrid))
{
hotels = hotels.Where(p => ("," + p.attrid + ",").Contains(("," + pageParam.attrid + ",")));
}
pageParam.total = hotels.Count();
if (!string.IsNullOrEmpty(pageParam.orderprice))
{
if (pageParam.isDesc)
{
hotels = hotels.OrderByDescending(c => c.price).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
hotels = hotels.OrderBy(c => c.price).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
}
else
{
if (pageParam.isDesc)
{
hotels = hotels.OrderByDescending(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
else
{
hotels = hotels.OrderBy(c => c.modtime).Skip(pageParam.size * (pageParam.page - 1)).Take(pageParam.size);
}
}
return(hotels);
}
/// <summary>
/// 根据会员id或者微信openid获取会员信息
/// </summary>
/// <param name="mid"></param>
/// <param name="thirdfrom">第三方来源，微信wx，可空</param>
/// <param name="openid"></param>
/// <returns></returns>
public UserInfoEntity GetUserInfo(int mid, string thirdfrom, string openid)
{
using (var db = new LTHWMysqlModel())
{
var userInfo = new UserInfoEntity();
if (mid > 0)
{
var users = from u in db.sline_member
join t in db.sline_member_third on u.mid equals t.mid into temp
from tt in temp.DefaultIfEmpty()
where u.mid == mid
select new UserInfoEntity
{
mid = u.mid,
checkmail = u.checkmail,
checkphone = u.checkphone,
province = u.province,
city = u.city,
cardid = u.cardid,
address = u.address,
postcode = u.postcode,
connectid = u.connectid,
email = u.email,
@from = u.@from,
jifen = u.jifen,
joinip = u.joinip,
jointime = u.jointime,
litpic = u.litpic,
loginip = u.loginip,
logintime = u.logintime,
mobile = u.mobile,
money = u.money,
mtype = u.mtype,
nickname = u.nickname,
openid = tt == null ? "" : tt.openid,
wxfrom = tt == null ? "" : tt.@from,
wxnickname = tt == null ? "" : tt.nickname,
pid = u.pid,
pwd = u.pwd,
rank = u.rank,
regtype = u.regtype,
remarks = u.remarks,
safeanswer = u.safeanswer,
safequestion = u.safequestion,
sex = u.sex,
truename = u.truename
};
if (!string.IsNullOrEmpty(thirdfrom))
{
users = users.Where(u => u.wxfrom == thirdfrom);
}
userInfo = users.FirstOrDefault();
}
if (mid == 0 && !string.IsNullOrEmpty(openid))
{
var opens = from u in db.sline_member
from t in db.sline_member_third
where u.mid == t.mid && t.openid == openid
select new UserInfoEntity
{
mid = u.mid,
checkmail = u.checkmail,
checkphone = u.checkphone,
province = u.province,
city = u.city,
cardid = u.cardid,
address = u.address,
postcode = u.postcode,
connectid = u.connectid,
email = u.email,
@from = u.@from,
jifen = u.jifen,
joinip = u.joinip,
jointime = u.jointime,
litpic = u.litpic,
loginip = u.loginip,
logintime = u.logintime,
mobile = u.mobile,
money = u.money,
mtype = u.mtype,
nickname = u.nickname,
openid = t.openid,
wxfrom = t.@from,
wxnickname = t.nickname,
pid = u.pid,
pwd = u.pwd,
rank = u.rank,
regtype = u.regtype,
remarks = u.remarks,
safeanswer = u.safeanswer,
safequestion = u.safequestion,
sex = u.sex,
truename = u.truename
};
userInfo = opens.FirstOrDefault();
}
return(userInfo);
}
}