public string Refresh(int houseid)
{
ObjectParameter state = new ObjectParameter("state", typeof(int));
ObjectParameter msg = new ObjectParameter("msg", typeof(string));
int userid = User.userid;
using (shhouseEntities ent = new shhouseEntities())
{
//try
//{
ent.House_Sale_Refresh_GR2018(userid, houseid, state, msg);
return(JsonConvert.SerializeObject(new repmsg
{
state = (int)state.Value,
msg = (string)msg.Value
}));
//}
//catch (Exception e)
//{
// return JsonConvert.SerializeObject(new repmsg
// {
// state = 0,
// msg = "提交失败，请稍后再试"
// });
//}
}
}
public string Delete(int houseid)
{
int userid = User.userid;
using (shhouseEntities ent = new shhouseEntities())
{
try
{
ObjectParameter state = new ObjectParameter("state", typeof(int));
ObjectParameter msg = new ObjectParameter("msg", typeof(string));
var ls = ent.House_Sale_Delete_GR2018(userid, houseid, state, msg);
return(JsonConvert.SerializeObject(new repmsg
{
state = (int)state.Value,
msg = (string)msg.Value
}));
//OssHelper.delete(ls);
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "提交失败，请稍后再试"
}));
}
}
}
/// <summary>
/// 详细 http://192.168.1.223/GR_house_require/Find
/// </summary>
/// <param name="eid"></param>
/// <returns></returns>
public string Find(int eid)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
using (shhouseEntities ent = new shhouseEntities())
{
try
{
var house_require_wuxi = ent.house_require_wuxi.FirstOrDefault(p => p.eid == eid);
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = house_require_wuxi
}, timeFormat));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "网路异常"
}));
}
}
}
public string Top(int houseid, int days)
{
int userid = User.userid;
using (shhouseEntities ent = new shhouseEntities())
{
ObjectParameter state = new ObjectParameter("state", typeof(int));
ObjectParameter msg = new ObjectParameter("msg", typeof(string));
try
{
int i = ent.House_Sale_Top_GR2018(userid, houseid, days, state, msg);
return(JsonConvert.SerializeObject(new repmsg
{
state = (int)state.Value,
msg = (string)msg.Value
}));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "提交失败，请稍后再试"
}));
}
}
}
/// <summary>
/// 首页右边列信息（公告 1，违规公告 2，问答 3）
/// </summary>
/// <param name=""></param>
/// <returns></returns>
public string RightMsg(int type)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd";
using (var db = new shhouseEntities())
{
if (type == 1)
{
var user_noteinfos = db.user_noteinfo.OrderByDescending(p => p.addtime).Take(5);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "公告", data = user_noteinfos
}, timeFormat));
}
else if (type == 2)
{
var user_noteinfos = db.user_noteinfo.OrderByDescending(p => p.addtime).Take(5);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "违规公告", data = user_noteinfos
}));
}
else
{
var user_noteinfos = db.user_noteinfo.OrderByDescending(p => p.addtime).Take(5);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "问答", data = user_noteinfos
}));
}
}
}
/// <summary>
/// 根据手机号重设新密码
/// </summary>
/// <param name="mobile"></param>
/// <param name="smscode"></param>
/// <param name="newpassword"></param>
/// <returns></returns>
public string findpassword(string mobile, string smscode, string newpassword)
{
using (var db = new shhouseEntities())
{
try
{
var user_details = db.user_details.Where(x => x.mobile == mobile).FirstOrDefault();
var uservalidityrecord = db.user_validityrecord.SqlQuery(" select top 1 * from user_validityrecord where userid = " + user_details.userid + " and codetype = 1 and checkcode = '" + smscode + "' and state = 0 and validity > getdate() order by validity desc ").FirstOrDefault();
if (uservalidityrecord == null)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "验证码错误！"
}));
}
uservalidityrecord.state = "1";
var user_member = db.user_member.Find(user_details.userid);
user_member.password = Utils.MD5(newpassword);
db.SaveChanges();
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "修改成功！"
}));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "修改失败，请稍后再试！"
}));
}
}
}
/// <summary>
/// 今日使用
/// <param name = "times" > 起始时间 </ param >
/// < param name="timee">结束时间</param>
/// <returns></returns>
public string RentSaleData(string times, string timee)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd";
using (shhouseEntities db = new shhouseEntities())
{
try
{
string where = "";
string nomal = " and DateDiff(dd,addtime,getdate())=0 ";
if (!string.IsNullOrEmpty(times))
{
where = " and addtime >='" + times + "'";
nomal = "";
}
if (!string.IsNullOrEmpty(timee))
{
where += " and addtime <='" + timee + "'";
nomal = "";
}
var statist_day = db.Database.DynamicSqlQuery($@"select * from statist_day where userid =@userid{ nomal }{where }", new SqlParameter[] { new SqlParameter("@userid", User.userid) });
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "今日使用", data = statist_day
}, timeFormat));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "暂无数据，请稍后再试！"
}));
}
}
}
public string On(int houseid)
{
int userid = User.userid;
using (shhouseEntities ent = new shhouseEntities())
{
try
{
ObjectParameter state = new ObjectParameter("state", typeof(int));
ObjectParameter msg = new ObjectParameter("msg", typeof(string));
int i = ent.House_Rent_On_GR2018(userid, houseid, state, msg);
return(JsonConvert.SerializeObject(new repmsg
{
state = (int)state.Value,
msg = (string)msg.Value
}));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "提交失败"
}));
}
}
}
/// <summary>
/// e币兑换刷新量
/// </summary>
/// <param name="setid"></param>
/// <param name="num"></param>
/// <returns></returns>
public string exchangerefresh(int setid, int num)
{
int userid = User.userid;
using (shhouseEntities ent = new shhouseEntities())
{
try
{
ObjectParameter state = new ObjectParameter("state", typeof(int));
ObjectParameter msg = new ObjectParameter("msg", typeof(string));
ent.exchange_e2refresh(userid, setid, num, state, msg);
return(JsonConvert.SerializeObject(new repmsg
{
state = (int)state.Value,
msg = (string)msg.Value
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "兑换失败，请稍后再试"
}));
}
}
}
/// <summary>
/// 经纪人详情
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public string Detailjjr(int?userid = null)
{
using (var db = new shhouseEntities())
{
try
{
var user_member = db.user_member.Find(userid);
var user_detail = db.user_details.Find(userid);
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = new
{
data1 = user_member,
data2 = user_detail
}
}, timeFormat));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "删除失败，请稍后再试"
}));
}
}
}
/// <summary>
/// 更新经纪人接口
/// </summary>
/// <param name="userid">经纪人ID </param>
/// <param name="username">用户名</param>
/// <param name="password">密码</param>
/// <param name="mobile">手机</param>
/// <param name="realname">真实姓名</param>
/// <param name="erpgh">erp工号</param>
/// <returns></returns>
public string Editjjr(int?userid = null, string username = null, string password = null, string mobile = null, string realname = null, string erpgh = null)
{
using (var db = new shhouseEntities())
{
try
{
var user_member = db.user_member.Find(userid);
user_member.username = username;
user_member.password = Utils.MD5(password);
user_member.erpgh = erpgh;
var user_details = db.user_details.Find(userid);
user_details.realname = realname;
user_details.mobile = mobile;
db.SaveChanges();
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "修改成功"
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "修改失败，请稍后再试"
}));
}
}
}
/// <summary>
/// 查询转介绍列表 http://192.168.1.223/GR_EFW_Guest/ListByUser
/// </summary>
/// <param name="guesttype">1 二手房 2租房 3买客 4租客</param>
/// <param name="pagesize"></param>
/// <param name="pageindex"></param>
/// <returns></returns>
public string ListByUser(int guesttype = 1, int pagesize = 20, int pageindex = 1)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
using (var db = new shhouseEntities())
{
try
{
var datas = new List <EFW_Guest>();
var datas_c = 0;
datas = db.EFW_Guest.Where(p => p.userid == User.userid && p.guesttype == guesttype).OrderByDescending(p => p.exedate).Skip(pagesize * (pageindex - 1)).Take(pagesize).ToList();
datas_c = db.EFW_Guest.Where(p => p.userid == User.userid && p.guesttype == guesttype).Count();
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = new
{
efw_guest = datas,
count = datas_c
}
}, timeFormat));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "暂无记录，请稍后再试!", data = null
}));
}
}
}
/// <summary>
/// 删除转介绍 http://192.168.1.223/GR_EFW_Guest/Delete
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string Delete(int id)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
int userid = User.userid;
string sql = string.Empty;
sql += " Delete EFW_Guest where id=@id and userid=@userid ";
using (shhouseEntities db = new shhouseEntities())
{
try
{
var edituser = db.Database.ExecuteSqlCommand(sql,
new SqlParameter[] {
new SqlParameter("@id", id),
new SqlParameter("@userid", userid)
});
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "删除成功", data = null
}));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "删除失败，请稍后再试！", data = null
}));
}
}
}
/// <summary>
/// 套餐查询
/// </summary>
/// <param name="settype"></param>
/// <returns></returns>
public string findsets(int settype)
{
using (shhouseEntities ent = new shhouseEntities())
{
try
{
var data = ent.user_set.Where(p => p.settype == settype).ToList();
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = data
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "查找失败"
}));
}
}
}
public string ListCategory()
{
//CTE递归查询
string sql = @"with tt as
(
select classid,classname,parentid,orderid,isshow, 1 as depth from help_classify where classid=1
union all
select A.classid,A.classname,A.parentid,A.orderid,A.isshow, b.depth+1 as depth from help_classify A inner join
tt B on B.classid=A.parentid
)
select * from tt where isshow=0 order by depth, orderid
";
using (shhouseEntities ent = new shhouseEntities())
{
try
{
var datas = ent.Database.DynamicSqlQuery(sql);
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = datas
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "暂无帮助信息"
}));
}
}
}
/// <summary>
/// 元宝兑换e币
/// </summary>
/// <param name="元宝数">ybnum</param>
/// <returns></returns>
public string APPybToeb(int ybnum)
{
using (var db = new shhouseEntities())
{
try
{
var user_details = db.user_details.Find(User.userid);
var user_member = db.user_member.Find(User.userid);
if (ybnum > user_details.silvertotal)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "兑换元宝大于已拥有的数量!"
}));
}
user_details.silvertotal = user_details.silvertotal - ybnum;
user_member.ebtotalnum = user_member.ebtotalnum + (ybnum / 100);
db.SaveChanges();
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "兑换成功!",
data = new
{
ybtotal = user_details.silvertotal,
ebtotal = user_member.ebtotalnum
}
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "兑换失败!"
}));
}
}
}
/// <summary>
/// 拨打电话消费1e币
/// </summary>
/// <param name="类型">type=0关注 1 取消关注</param>
/// <returns></returns>
public string call(int eb = 1)
{
using (var db = new shhouseEntities())
{
try
{
var user_details = db.user_details.Find(User.userid);
var user_member = db.user_member.Find(User.userid);
if (eb > user_member.ebtotalnum)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "e币余额不足!"
}));
}
user_member.ebtotalnum = user_member.ebtotalnum - eb;
db.SaveChanges();
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "消费成功!",
data = new
{
ebtotal = user_member.ebtotalnum
}
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "兑换失败!"
}));
}
}
}
public string AddTempl(int ttype, string tname, string tcontent)
{
int userid = User.userid;
using (shhouseEntities ent = new shhouseEntities())
{
try
{
house_template tt = new house_template()
{
ttype = ttype,
tname = tname,
tcontent = tcontent,
userid = userid,
createtime = DateTime.Now
};
ent.house_template.Add(tt);
ent.SaveChanges();
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "提交成功"
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "提交失败，请稍后再试"
}));
}
}
}
/// <summary>
/// 小区管家判断状态
/// </summary>
/// <param name="ID">小区ID</param>
/// <param name="userid">userid</param>
/// <returns></returns>
protected string getState(int ID, int Userid)
{
string result = string.Empty;
int iUnixdate = Utils.GetUnixNum(CharString.DateConvert(DateTime.Now.ToString("yyyy-MM-dd")));
using (var db = new shhouseEntities())
{
var uservillagesteward = db.user_villagesteward.Where(p => p.villageid == ID && p.userid == Userid).FirstOrDefault();
if (uservillagesteward == null)
{
result = "未申请";
}
else if (uservillagesteward.isapply == 0)
{
result = "未审核";
}
else if (uservillagesteward.isapply == 2)
{
result = "驳回";
}
else if (uservillagesteward.endunixdate >= iUnixdate)
{
result = "使用中";
}
else
{
result = "已过期";
}
return(result);
}
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="oldpassword">旧密码</param>
/// <param name="newpassword">新密码</param>
/// <param name=""></param>
/// <returns></returns>
public string ChangePassword(string oldpassword, string newpassword)
{
oldpassword = Utils.MD5(oldpassword);
using (var db = new shhouseEntities())
{
try
{
var user_member = db.user_member.Where(p => p.userid == User.userid && p.password == oldpassword).FirstOrDefault();
if (user_member == null)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "旧密码错误", data = ""
}));
}
else
{
var user_member1 = db.user_member.Find(User.userid);
user_member.password = Utils.MD5(newpassword);
db.SaveChanges();
}
//string path = System.Web.HttpContext.Current.Server.MapPath($"~/tokens/" + Utils.MD5(User.userid.ToString()) + ".json");
//Utils.DeleteFile(path);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "修改成功"
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "修改失败,请稍后再试！"
}));
}
}
}
public string ListTempl(string keyword, int?type, int pageindex = 1, int pagesize = 20)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
int userid = User.userid;
List <string> where1 = new List <string>();
List <SqlParameter> where2 = new List <SqlParameter>();
string _where = "";
where1.Add($"userid={userid}");
if (type.HasValue)
{
where1.Add($"ttype={type}");
}
if (!string.IsNullOrEmpty(keyword))
{
where1.Add("titletag like @keyword");
where2.Add(new SqlParameter("", "%" + keyword + "%"));
}
_where = " where " + string.Join(" and ", where1.ToArray());
string sql = $@"select templateid, ttype, tname, createtime,tcontent
from (SELECT templateid, ttype, tname, createtime,tcontent,
ROW_NUMBER() over(order by templateid desc) as rows
FROM house_template { _where }) t
where t.rows>={ (pageindex - 1) * pagesize + 1 } and t.rows<={ pageindex * pagesize }
";
string sql_c = $@"select count(1) from house_template { _where }";
using (shhouseEntities ent = new shhouseEntities())
{
try
{
var parms1 = new object[] { };
if (where2.Count > 0)
{
parms1 = where2.ToArray();
}
var datas = ent.Database.DynamicSqlQuery(sql, parms1);
var datas_c = ent.Database.SqlQuery <int>(sql_c, parms1).First();
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = new
{
sales = datas,
count = datas_c
}
}, timeFormat));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "没有找到模板信息"
}));
}
}
}
/// <summary>
/// 添加取消委托记录
/// </summary>
/// <param name="FollowID">跟进原因ID </param>
/// <param name="Followtext">跟进原因文本</param>
/// <param name="Cusid">需求ID</param>
/// <returns></returns>
public string AddFollow(int FollowID, string Followtext, int Cusid)
{
try
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
using (var db = new shhouseEntities())
{
var follow = new house_agentfollow_wuxi
{
Cusid = Cusid,
AgentID = User.userid,
FollowText = Followtext,
exe_date = DateTime.Now
};
db.house_agentfollow_wuxi.Add(follow);
db.SaveChanges();
}
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "取消委托成功!"
}));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "网络异常，请稍后再试!", data = e
}));
}
}
/// <summary>
/// 修改状态
/// </summary>
/// <param name="ID"></param>
/// <param name="IsLink"></param>
/// <param name="IsFollow"></param>
/// <param name="IsDel"></param>
/// <returns></returns>
public string EditState(int ID, int?IsLink = null, int?IsFollow = null, int?IsDel = null)
{
try
{
using (var db = new shhouseEntities())
{
house_agentize_wuxi agentize = db.house_agentize_wuxi.Find(ID);
if (IsDel != null)
{
agentize.IsDel = IsDel;
}
if (IsLink != null)
{
agentize.IsLink = IsLink;
}
if (IsFollow != null)
{
agentize.IsFollow = IsFollow;
}
db.SaveChanges();
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "修改成功!"
}));
}
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "网络异常，请稍后再试!", data = e
}));
}
}
public string FocusOn(int userid, int agentid)
{
try
{
using (shhouseEntities ent = new shhouseEntities())
{
var Single = ent.AgentCollection.Where(p => p.UserID == userid && p.AgentID == agentid).FirstOrDefault();
if (Single == null)
{
var AgentCollection = new AgentCollection
{
UserID = userid,
AgentID = agentid,
AddTime = DateTime.Now
};
ent.AgentCollection.Add(AgentCollection);
ent.SaveChanges();
}
}
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "关注成功！"
}));
}
catch
{
return(JsonConvert.SerializeObject(new repmsg {
state = 0, msg = "暂无数据，请稍后再试！"
}));
}
}
/// <summary>
/// 手机号码管理
/// </summary>
/// <param name="phone">手机号码</param>
/// <param name="yzm">验证码</param>
/// <param name="password">密码</param>
/// <param name="type">1 安全手机号码,2 展示手机号码</param>
/// <param name=""></param>
/// <returns></returns>
public string phonemanage(string phone, string password, string yzm, int type = 1)
{
using (var db = new shhouseEntities())
{
try
{
var user_details = db.user_details.Find(User.userid);
if (type == 1)
{
//var uservalidityrecord = db.Database.SqlQuery<int>(" select top 1 eid from user_validityrecord where userid = @userid and codetype = 2 and checkcode = @checkcode and state = 0 and validity > getdate() order by addtime desc ", new SqlParameter[] { new SqlParameter("@userid", User.userid), new SqlParameter("@checkcode", yzm) });
var uservalidityrecord = db.user_validityrecord.SqlQuery(" select top 1 * from user_validityrecord where userid = " + User.userid + " and codetype = 1 and checkcode = '" + yzm + "' and state = 0 and validity > getdate() order by validity desc ").FirstOrDefault();
if (uservalidityrecord == null)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "验证码错误！"
}));
}
uservalidityrecord.state = "1";
user_details.mobile = phone;
db.SaveChanges();
//string path = System.Web.HttpContext.Current.Server.MapPath($"~/tokens/" + Utils.MD5(User.userid.ToString()) + ".json");
//Utils.DeleteFile(path);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "修改成功！"
}));
}
else
{
if (!string.IsNullOrEmpty(password))
{
password = Utils.MD5(password);
}
var user_member = db.user_member.Where(p => p.userid == User.userid && p.password == password).FirstOrDefault();
if (user_member == null)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "密码错误！"
}));
}
user_details.mobile_zs = phone;
db.SaveChanges();
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "修改成功"
}));
}
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "修改失败，请稍后再试！"
}));
}
}
}
/// <summary>
/// 基础数据 base_samtype 表所有数据 /Base/Base_Samtype
/// </summary>
/// <returns></returns>
public string Base_Samtype()
{
using (var db = new shhouseEntities())
{
var base_samtype = db.base_samtype.OrderBy(p => p.parentid).OrderBy(p => p.orderid);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "基础数据", data = base_samtype
}));
}
}
/// <summary>
/// 基础数据 个人服务 /Base/Base_PersonalService
/// </summary>
/// <returns></returns>
public string Base_PersonalService()
{
using (var db = new shhouseEntities())
{
var base_samtype = db.base_samtype.Where(p => p.parentid == 131 && p.citypy == "wuxi").OrderBy(p => p.parentid).OrderBy(p => p.orderid);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "基础数据 个人服务", data = base_samtype
}));
}
}
/// <summary>
/// 房源标签 /Base/HouseFlag
/// </summary>
/// <returns></returns>
public string HouseFlag()
{
using (var db = new shhouseEntities())
{
var house_tags = db.house_tags.OrderByDescending(p => p.parentid);
return(JsonConvert.SerializeObject(new repmsg {
state = 1, msg = "房源标签", data = house_tags
}));
}
}
//查询订单列表
// public string orders(int pagesize = 20, int pageindex = 1)
// {
// int userid = User.userid;
// string sql = $@"select setname,ordernum,createtime,totals,paytype from(
//select setname,ordernum,createtime,totals,paytype,
//ROW_NUMBER() over(order by orderid desc) as rows from user_order where userid={userid} and ispay=1) t
//where rows>={ (pageindex - 1) * pagesize + 1 } and rows<={ pageindex * pagesize };
//";
// string sql_c = $@"select count(1) from user_order where userid={userid} and ispay=1;";
// using (shhouseEntities ent = new shhouseEntities())
// {
// try
// {
// var datas = ent.Database.DynamicSqlQuery(sql);
// var datas_c = ent.Database.SqlQuery<int>(sql_c).First();
// return JsonConvert.SerializeObject(new repmsg
// {
// state = 1,
// msg = "",
// data = new
// {
// items = datas,
// count = datas_c
// }
// });
// }
// catch (Exception e)
// {
// return JsonConvert.SerializeObject(new repmsg
// {
// state = 0,
// msg = "没有找到订单数据"
// });
// }
// }
// }
//账单查询
public string bills(int?subtype, DateTime?start, DateTime?end, int pagesize = 20, int pageindex = 1)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
int userid = User.userid;
List <string> where1 = new List <string>();
string _where = "";
where1.Add($"userid={userid}");
if (subtype.HasValue)
{
where1.Add($"subtype={subtype}");
}
if (start.HasValue)
{
where1.Add($"createtime>='{start.Value.ToString("yyyy-MM-dd")}'");
}
if (end.HasValue)
{
where1.Add($"createtime<='{end.Value.AddDays(1).ToString("yyyy-MM-dd")}'");
}
_where = " where " + string.Join(" and ", where1.ToArray());
string sql = $@"select billid,userid,billtype,notes,createtime,subtype,totals,extdata
from (SELECT *, ROW_NUMBER() over(order by billid desc) as rows FROM user_bill { _where }) t
where t.rows>={ (pageindex - 1) * pagesize + 1 } and t.rows<={ pageindex * pagesize }
";
string sql_c = $@"select count(1) from user_bill { _where }";
using (shhouseEntities ent = new shhouseEntities())
{
try
{
var datas = ent.Database.DynamicSqlQuery(sql);
var datas_c = ent.Database.SqlQuery <int>(sql_c).First();
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = new
{
items = datas,
count = datas_c
}
}, timeFormat));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "没有找到账单信息"
}));
}
}
}
/// <summary>
/// 门店下对应经纪人列表
/// </summary>
/// <param name="deptid">门店id</param>
/// <param name="pagesize">条数</param>
/// <param name="pageindex">页数</param>
/// <returns></returns>
public string Listjjr(int?deptid = null, int pagesize = 20, int pageindex = 1)
{
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
int userid = User.userid;
List <string> where1 = new List <string>();
List <SqlParameter> where2 = new List <SqlParameter>();
string _where = "";
where1.Add($" a.state=0 and a.city=3 and a.roleid=4 and a.deptpath+ ',' like '%,{deptid},%'");
_where = " where " + string.Join(" and ", where1.ToArray());
string sql = $@"select * from (select a.userid,a.username,a.viliditystart,a.vilidityend ,b.realname,b.mobile,b.bindnum,erpgh,b.photoname,b.gradeid,b.origin,b.know_area,b.know_village,ROW_NUMBER() over(order by a.userid desc) as rows from user_member a
left join user_details b on a.userid = b.userid
left join user_roles c on a.roleid = c.roleid
left join user_search_all_wuxi m on a.userid = m.userid
left join (select userid, agentnum, storenum from statist_total) f on f.userid = a.userid
left join user_dept g on a.deptid=g.deptid { _where }) t
where t.rows>={ (pageindex - 1) * pagesize + 1 } and t.rows<={ pageindex * pagesize }";
string sql_c = $@"select count(1) from user_member a { _where }";
using (shhouseEntities ent = new shhouseEntities())
{
try
{
//var datas = ent.Database.DynamicSqlQuery(sql, where2.Select(x => ((ICloneable)x).Clone()).ToArray());
DataTable datas = DBHelperSQL.Query(sql);
for (int i = 0; i < datas.Rows.Count; i++)
{
datas.Rows[i]["know_area"] = Utils.chuli(datas.Rows[i]["know_area"]);
}
var datas_c = ent.Database.SqlQuery <int>(sql_c, where2.Select(x => ((ICloneable)x).Clone()).ToArray()).First();
return(JsonConvert.SerializeObject(new repmsg
{
state = 1,
msg = "",
data = new
{
sales = datas,
count = datas_c,
}
}, timeFormat));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg
{
state = 0,
msg = "没有找到经纪人信息"
}));
}
}
}
