/// <summary>
/// 个人会员登录 密码登录 http://192.168.1.223/GR_NewHouse_Price_Line/Price_LineByVillageid
/// </summary>
/// <param name="username">用户名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public string Price_LineByVillageid(int villageid)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
// string sqlnn = "select MaxPrice,AddDate from(select top 6 ID,MaxPrice,AddDate from NewHouse_Price_Line where NHID=" + houseid.ToString() + " order by ID desc)as a order by id";
using (var dbshvillage = new shvillageEntities())
{
// var Price_Line = dbshvillage.NewHouse_Price_Line.Where(p=>p.NHID==villageid&&p.City=="无锡").Select(s => new { AddDate =s.AddDate.Value, MaxPrice = s.MaxPrice }).OrderByDescending(p => p.AddDate).Take(6);
var Price_Line = (from p in dbshvillage.NewHouse_Price_Line where (p.NHID == villageid && p.City == "无锡")
select new { ID = p.ID, AddDate = DbFunctions.TruncateTime(p.AddDate), MaxPrice = p.MaxPrice }).OrderByDescending(S => S.ID).Take(6);
DateTime bdt = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime sdt = bdt.AddMonths(-1);
Double byMaxPrice = 0;
var myNewHouse_Price_Lineby = dbshvillage.NewHouse_Price_Line.Where(x => x.NHID == villageid && x.AddDate >= bdt).FirstOrDefault();
if (myNewHouse_Price_Lineby != null)
{
byMaxPrice = Convert.ToDouble(myNewHouse_Price_Lineby.MaxPrice.ToString());
}
Double sybyMaxPrice = 0;
var myNewHouse_Price_Linesy = dbshvillage.NewHouse_Price_Line.Where(x => x.NHID == villageid && x.AddDate < bdt && x.AddDate >= sdt).FirstOrDefault();
if (myNewHouse_Price_Linesy != null)
{
sybyMaxPrice = Convert.ToDouble(myNewHouse_Price_Linesy.MaxPrice.ToString());
}
Double hb = 0;
if (byMaxPrice != 0 && sybyMaxPrice != 0)
{
hb = (byMaxPrice - sybyMaxPrice) / sybyMaxPrice;
}
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "小区价格走势", data = new { Price_Line, byMaxPrice, sybyMaxPrice, hb }
}, timeFormat));
}
}
/// <summary>
/// 用户基础信息 http://192.168.1.223/GR_User/UserData
/// </summary>
/// <returns></returns>
public string UserData()
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
try
{
//用户房源点击数量、
using (shhouseEntities db = new shhouseEntities())
{
//var houseclick = db.Database.DynamicSqlQuery(@"select isnull(SUM(clicknum),0)num from statist_house where houseid in (select saleid from house_sale_search_wuxi where userid=@userid)", new SqlParameter[] { new SqlParameter("@userid", User.userid) });
//var housetopclick = db.Database.DynamicSqlQuery(@"select isnull(SUM(clicknum),0)num from statist_house where houseid in (select saleid from house_sale_search_wuxi where istop =1 and userid=@userid)", new SqlParameter[] { new SqlParameter("@userid", User.userid) });
//var todaytophouse = db.Database.DynamicSqlQuery(@"select isnull(count(0),0)num from house_sale_search_wuxi where DateDiff(dd,updatetime,getdate())=0 and istop =1 and userid=@userid", new SqlParameter[] { new SqlParameter("@userid", User.userid) });
int totaluser_noteinfo = db.Database.SqlQuery <int>(@"select isnull(count(0),0)num from user_noteinfo where userid=@userid", new SqlParameter[] { new SqlParameter("@userid", User.userid) }).First();
//关注数量
// 二手房
int Favourite_house_sale_count = (from a in db.favourite_house_village
join b in db.house_sale_list_wuxi on a.houseid equals b.saleid into ab
from abi in ab
join c in db.house_sale_search_wuxi on abi.saleid equals c.saleid into abc
from abci in abc
where a.userid == User.userid && a.housetype == 1
select new { a.id }).Count();
//新房
int Favourite_NewHouse_count = 0;
using (var dbefwnewhouse = new efwnewhouseEntities())
{
int[] NewHouseID = db.favourite_house_village.Where(p => p.userid == User.userid && p.housetype == 6).Select(x => x.houseid.Value).ToArray();
Favourite_NewHouse_count = (from a in dbefwnewhouse.NewHouse where NewHouseID.Any(x => x == a.ID) select new { a.ID }).Count();
}
//租房
int Favourite_house_rent_count = (from a in db.favourite_house_village
join b in db.house_rent_list_wuxi on a.houseid equals b.rentid into ab
from abi in ab
join c in db.house_rent_search_wuxi on abi.rentid equals c.rentid into abc
from abci in abc
where a.userid == User.userid && a.housetype == 2
select new { a.id }).Count();
//小区
int Favourite_Community_count = 0;
using (var dbshvillage = new shvillageEntities())
{
int[] NewHouseID = db.favourite_house_village.Where(p => p.userid == User.userid && p.housetype == 3).Select(x => x.houseid.Value).ToArray();
Favourite_Community_count = (from a in dbshvillage.NewHouse where NewHouseID.Any(x => x == a.ID) select new { a.ID }).Count();
}
//转介绍 1 二手房 2租房 3买客 4租客
// 出售房
int EFW_Guest_house_sale_count = db.EFW_Guest.Where(p => p.userid == User.userid && p.guesttype == 1).Count();
//出租房
int EFW_Guest_house_rent_count = db.EFW_Guest.Where(p => p.userid == User.userid && p.guesttype == 2).Count();
//求购
int EFW_Guest_sellcustom_count = db.EFW_Guest.Where(p => p.userid == User.userid && p.guesttype == 3).Count();
//求租
int EFW_Guest_rentcustom_count = db.EFW_Guest.Where(p => p.userid == User.userid && p.guesttype == 4).Count();
DateTime dtdy = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
int totaluser_signIn_dy = db.SignIn.Where(x => x.UserID == User.userid && x.exe_date >= dtdy).Count();
return(JsonConvert.SerializeObject(
new repmsg
{
state = 1,
msg = "用户信息",
data =
new
{
username = User.user_member.username, //用户名
userid = User.user_member.userid, //用户id
mobile = User.user_details.mobile, //手机号
//mobile_zs = string.IsNullOrEmpty(User.user_details.BindNum) ? User.user_details.mobile_zs : User.user_details.BindNum,//有隐号用隐号 没隐号显示展示号
mobile_zs = User.user_details.mobile_zs,
////yjrate = User.user_member.yjrate,//佣金比例
////servicetype = User.user_member.servicetype,//服务类型
photoname = User.user_details.photoname, //头像
gradeid = User.user_details.gradeid, //等级
//shangquanval = User.user_details.shangquanval, //商圈
////origin = User.user_details.origin, //上级门店或总店
ebtotalnum = User.user_member.ebtotalnum, //总e币数量
allscore = User.user_details.scoretotal, //j积分
realname = User.user_details.realname, //真实姓名
searchtitle = User.searchtitle, //搜索字段
//viliditystart = Convert.ToDateTime(User.user_member.viliditystart).ToShortDateString(),//用户开始有效期
//vilidityend = Convert.ToDateTime(User.user_member.vilidityend).ToShortDateString(),//用户结束有效期
remark = User.user_details.remark, //简介
remarkxuanyan = User.user_details.remark_xuanyan, //简介——宣言
//knowarea = User.user_details.know_area,//熟悉区域
//knowvillage = User.user_details.know_village,//熟悉等级
//refusenum = User.user_member.refusenum,//刷新使用数量
//reftotalnum = User.user_member.reftotalnum,//刷新总数量
////houseclick = houseclick,//房源点击率
////housetopclick = housetopclick,////房源点击率
//housetotalnum = User.user_member.housetotalnum,//房源总数量
//houseusenum = User.user_member.houseusenum,//房源已使用数量
//syhousenum = User.user_member.housetotalnum - User.user_member.houseusenum,
//Days = (Convert.ToDateTime(User.user_member.vilidityend) - Convert.ToDateTime(User.user_member.viliditystart)).TotalDays,//服务期
//syDays = (Convert.ToDateTime(User.user_member.vilidityend) - DateTime.Now).TotalDays,//还有多少天到期
//iszhongshan = string.IsNullOrEmpty(User.user_member.deptpath) ? -1 : User.user_member.deptpath.IndexOf("0,439"),//关联部门ID
silvertotal = User.user_details.silvertotal, //元宝
issignIn = User.IsSignIn, //签到
//todaytophouse = todaytophouse,
roleid = User.user_member.roleid, //用户类型
idnumber = User.user_details.idnumber, //身份证号码
cardtype = User.user_details.CardType, //证件类型
sexuality = User.user_details.sexuality, //性别
birthday = User.user_details.birthday, //性别
totaluser_note = totaluser_noteinfo, //站内信息数
RCToken = User.RCToken,
totaluser_signIn_dy = totaluser_signIn_dy, //本月签到数量
Favourite_house_sale_count, //关注出售数量
Favourite_NewHouse_count, //关注新房数量
Favourite_house_rent_count, //关注出租数量
Favourite_Community_count, //关注小区数量
EFW_Guest_house_sale_count, //转介绍出售数量
EFW_Guest_house_rent_count, //转介绍出租数量
EFW_Guest_sellcustom_count, //转介绍求购数量
EFW_Guest_rentcustom_count, //转介绍求租数量
NickName = User.user_member.NickName
}
}, timeFormat));
}
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "暂无数据，请稍后再试！", data = null
}));
}
}
/// <summary>
/// 经纪人查看客户需求列表通用 http://192.168.4.111/house_agentize/ListByWhere
/// </summary>
/// <param name="AgentID">经纪人ID 类型 int</param>
/// <param name="IsLink">是否联系 类型 int</param>
/// <param name="IsFollow">是否跟进 类型 int</param>
/// <param name="IsDel">是否委托 类型 int</param>
/// <param name="housetype">housetype 说明：1求购2求租 类型：int</param>
/// <param name="SortName">排序字段名</param>
/// <param name="IsDesc">升序降序</param>
/// <param name="IsAgentize">待分配已分配状态</param>
/// <returns></returns>
public string ListByWhere(int?AgentID = null, int?IsLink = null, int?IsFollow = null, int?IsDel = null, int?housetype = null, string SortName = "exe_date", bool IsDesc = true, bool?IsAgentize = null)
{
try
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
using (var db = new shhouseEntities())
{
IQueryable <house_agentize_wuxi> result = db.house_agentize_wuxi;
if (AgentID != null)
{
result = result.Where(p => p.Agentid == AgentID);
}
if (IsLink != null)
{
DataTable dt = Utils.Query(shhouseconn, "SELECT ID FROM house_agentize_wuxi a LEFT JOIN house_customize_wuxi b ON A.Cusid=B.cusid WHERE a.isLink=0 and a.isdel = 0 and a.IsFollow = 0 and b.isdel = 0 and b.iscustomize = 1");
int[] IDss = new int[dt.Rows.Count];
for (int j = 0; j < dt.Rows.Count; j++)
{
IDss[j] = int.Parse(dt.Rows[j][0].ToString());
}
result = result.Where(p => IDss.Contains(p.ID));
}
if (IsFollow != null)
{
DataTable dt = Utils.Query(shhouseconn, "SELECT ID FROM house_agentize_wuxi a LEFT JOIN house_customize_wuxi b ON A.Cusid=B.cusid WHERE a.isfollow = 1 and a.isdel = 0 and b.isdel = 0 and iscustomize = 1");
int[] IDss = new int[dt.Rows.Count];
for (int j = 0; j < dt.Rows.Count; j++)
{
IDss[j] = int.Parse(dt.Rows[j][0].ToString());
}
result = result.Where(p => IDss.Contains(p.ID));
}
//包含客户取消,经纪人自己取消,客户禁止经纪人
if (IsDel != null)
{
if (IsDel == 1)
{
DataTable dt = Utils.Query(shhouseconn, "select ID from house_agentize_wuxi where (IsDel=1 or Cusid in(select cusid from house_customize_wuxi where isdel=1 or iscustomize=0))");
int[] IDss = new int[dt.Rows.Count];
for (int j = 0; j < dt.Rows.Count; j++)
{
IDss[j] = int.Parse(dt.Rows[j][0].ToString());
}
result = result.Where(p => IDss.Contains(p.ID));
}
else
{
result = result.Where(p => p.IsDel == IsDel);
}
}
if (IsAgentize != null)
{
if (Convert.ToBoolean(IsAgentize))
{
result = result.Where(p => db.house_agentize_wuxi.Where(a => a.IsDel != 1).GroupBy(a => a.Cusid).Select(a => new { Cusid = a.Key, Count = a.Count() }).Where(a => a.Count >= 3).Select(a => a.Cusid).Contains(p.Cusid));
}
else
{
result = result.Where(p => !db.house_agentize_wuxi.Where(a => a.IsDel != 1).GroupBy(a => a.Cusid).Select(a => new { Cusid = a.Key, Count = a.Count() }).Where(a => a.Count >= 3).Select(a => a.Cusid).Contains(p.Cusid));
}
}
if (housetype != null)
{
result = result.Where(p => db.house_customize_wuxi.Where(a => a.housetype == housetype).Select(a => a.cusid).Contains(p.Cusid));
}
int count = result.Count();
var house_agentize_wuxi_List_temp = result.OrderBy(string.Format("{0} {1}", SortName, IsDesc ? "desc" : "asc"));
Dictionary <string, object>[] house_agentize_wuxi_List = new Dictionary <string, object> [house_agentize_wuxi_List_temp.Count()];
int i = 0;
foreach (var temp in house_agentize_wuxi_List_temp)
{
house_customize_wuxi cus = db.house_customize_wuxi.Where(p => p.cusid == temp.Cusid).FirstOrDefault();
house_agentize_wuxi_List[i] = new Dictionary <string, object>();
//house_agentize_wuxi_List_temp表
house_agentize_wuxi_List[i].Add("ID", temp.ID);
house_agentize_wuxi_List[i].Add("AgentID", temp.Agentid);
house_agentize_wuxi_List[i].Add("IsLink", temp.IsLink);
house_agentize_wuxi_List[i].Add("IsFollow", temp.IsFollow);
house_agentize_wuxi_List[i].Add("IsDel", temp.IsDel);
house_agentize_wuxi_List[i].Add("exe_date", temp.exe_date);
//house_customize_wuxi_List_temp表
house_agentize_wuxi_List[i].Add("cusid", cus.cusid);
house_agentize_wuxi_List[i].Add("userid", cus.userid);
house_agentize_wuxi_List[i].Add("areaID", cus.areaID);
house_agentize_wuxi_List[i].Add("tradingareaID", cus.tradingareaID);
house_agentize_wuxi_List[i].Add("countyIdList", cus.countyIdList);
house_agentize_wuxi_List[i].Add("jiageId", cus.jiageId);
house_agentize_wuxi_List[i].Add("room", cus.room);
house_agentize_wuxi_List[i].Add("housetype", cus.housetype);
house_agentize_wuxi_List[i].Add("rentype", cus.rentype);
house_agentize_wuxi_List[i].Add("linkman", cus.linkman);
house_agentize_wuxi_List[i].Add("tel", cus.tel);
house_agentize_wuxi_List[i].Add("iscustomize", cus.iscustomize);
house_agentize_wuxi_List[i].Add("cusisdel", cus.isdel);
house_agentize_wuxi_List[i].Add("city", cus.city);
house_agentize_wuxi_List[i].Add("addtime", cus.addtime);
string areaname = "";
if (cus.areaID != null && cus.areaID != 0)
{
var areaIDtemp = db.base_area.Where(p => p.areaid == cus.areaID).OrderBy(p => p.areaid).FirstOrDefault();
if (areaIDtemp != null)
{
areaname = areaIDtemp.areaname;
}
}
house_agentize_wuxi_List[i].Add("areaname", areaname);
string tradingareaname = "";
if (cus.tradingareaID != null && cus.tradingareaID != 0)
{
var tradingareaIDtemp = db.base_area.Where(p => p.areaid == cus.tradingareaID).OrderBy(p => p.areaid).FirstOrDefault();
if (tradingareaIDtemp != null)
{
tradingareaname = tradingareaIDtemp.areaname;
}
}
house_agentize_wuxi_List[i].Add("tradingareaname", tradingareaname);
using (shvillageEntities dbshvillage = new shvillageEntities())
{
if (!string.IsNullOrWhiteSpace(cus.countyIdList))
{
string[] str_countyIdList = cus.countyIdList.Split(',').ToArray();
int[] int_countyIdList = Array.ConvertAll(str_countyIdList, new Converter <string, int>(Utils.StrToInt));
var countyList = dbshvillage.NewHouse.Where(p => int_countyIdList.Contains(p.ID)).Select(a => new { ID = a.ID, title = a.Name }).OrderBy(p => p.ID).ToList();
house_agentize_wuxi_List[i].Add("countyList", countyList);
}
else
{
house_agentize_wuxi_List[i].Add("countyList", null);
}
}
string jiagename = null;
if (cus.jiageId != null && cus.jiageId != 0)
{
var jiagetemp = db.base_samtype.Where(p => p.typeid == cus.jiageId).FirstOrDefault();
if (jiagetemp != null)
{
jiagename = jiagetemp.typename;
}
}
house_agentize_wuxi_List[i].Add("jiagename", jiagename);
string rentypename = null;
if (cus.rentype != null && cus.rentype != 0)
{
var rentypetemp = db.base_samtype.Where(p => p.typeid == cus.rentype).FirstOrDefault();
if (rentypetemp != null)
{
rentypename = rentypetemp.typename;
}
}
house_agentize_wuxi_List[i].Add("rentypename", rentypename);
int AgentizeNumber = db.house_agentize_wuxi.Where(p => p.Cusid == cus.cusid && p.IsDel != 1).Count();
house_agentize_wuxi_List[i].Add("AgentizeNumber", AgentizeNumber);
string photoname = null;
if (cus.userid != null && cus.userid != 0)
{
var user_detailstemp = db.user_details.Where(p => p.userid == cus.userid).FirstOrDefault();
if (user_detailstemp != null)
{
photoname = user_detailstemp.photoname;
}
}
house_agentize_wuxi_List[i].Add("photoname", photoname);
i++;
}
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "", data = new { house_agentize_wuxi_List, count }
}, timeFormat));
}
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "网络异常，请稍后再试!", data = e
}));
}
}
