/// <summary>
/// 分页获取营业厅的活动列表
/// </summary>
/// <param name="hallid"></param>
/// <returns></returns>
public object GetHallPromotionListByPage(int hallid, PageInfo pageInfo)
{
OracleDbParameters Parameters = new OracleDbParameters();
Parameters.Add("hallid", hallid);
var pageResult = query.PaginationQuery <dynamic>(@"select t.promotion_id 'promotionid',
(case when t.promotion_type=1 then t1.pic_url
else t.img_url
end ) 'imgpath',
t.title 'title',
t.click_count 'num',
t.promotion_type 'promotiontype',
t.sort 'sort',
t.create_time 'createtime'
from business_promotion t
left join promotion_template t1 on t1.template_id=t.template_id
where t.status=0
and t.end_time>=sysdate
and t.promotion_id in
(select t1.promotion_id from business_bind t1 where t1.hall_id=:hallid and t1.bind_type=1 --平台绑定营业厅
union select t2.promotion_id from business_promotion t2 where t2.hall_id=:hallid and t2.promotion_type!=2 --该营业厅下营业员自建的
union select t3.promotion_id from business_bind t3 where t3.bind_type=0 and t3.user_id in
(select waiter_id from user_waiter where hall_id=:hallid)
) order by t.create_time desc", pageInfo, Parameters);
return(pageResult);
}
/// <summary>
/// 分页获取推广的历史记录
/// </summary>
/// <param name="pageInfo"></param>
/// <param name="SellerId"></param>
/// <returns></returns>
public object GetHistoryPromotionList(PageInfo pageInfo, int SellerId)
{
OracleDbParameters Parameters = new OracleDbParameters();
Parameters.Add("waiterid", SellerId);
var pageResult = query.PaginationQuery <dynamic>(@"select t.promotion_id 'promotionid',
t.title 'title',
(case when t.promotion_type=1 then t1.pic_url
else t.img_url
end ) 'picurl',
t.promotion_type 'promotiontype',
t.promotion_no 'promotionno',
t.click_count 'clickcount',
t.sort 'sort',
t.create_time 'createtime'
from business_promotion t
left join promotion_template t1 on t1.template_id=t.template_id
where t.status=0
and t.end_time<sysdate
and t.promotion_id in
(select t1.promotion_id from business_bind t1 where t1.user_id=:waiterid and t1.bind_type=0 --平台绑定给营业员
union select t2.promotion_id from business_promotion t2 where t2.promotion_type!=2 and t2.user_id in --该营业厅下所有营业员自建的
(select waiter_id from user_waiter where hall_id in (select hall_id from user_waiter where waiter_id=:waiterid ))
union select t3.promotion_id from business_bind t3 where t3.bind_type=1 and t3.hall_id in --绑定给营业员所属营业厅的
(select hall_id from user_waiter where waiter_id=:waiterid)
) order by t.create_time desc", pageInfo, Parameters);
pageResult.Data = new { status = Nbh_UsersServices.Instance.GetSellerStatus(SellerId) };
return(pageResult);
}
/// <summary>
/// 分页获取用户关注的营业厅列表
/// </summary>
/// <param name="UserId"></param>
/// <param name="pageInfo"></param>
/// <returns></returns>
public object GetHallListByPage(int UserId, PageInfo pageInfo)
{
OracleDbParameters Parameters = new OracleDbParameters();
Parameters.Add("userid", UserId);
var pageResult = query.PaginationQuery <dynamic>(@"select t.hall_id 'hallid',
t.name 'hallname',
t.identification_type 'identificationtype',
t.picpath 'imgpath',
t.hall_follow 'hallfollow',
t2.allpromotion 'allpromotion'
from business_hall t
left join (select * from
(select count(*) allpromotion,hall_id from
(select promotion_id,hall_id from business_promotion
where hall_id in (select follow_num from user_follow where user_id=:userid and follow_type=0)
union select promotion_id,hall_id from business_bind
where hall_id in (select follow_num from user_follow where user_id=:userid and follow_type=0)
) t1 group by t1.hall_id)
) t2 on t2.hall_id=t.hall_id
where t.hall_id in(select follow_num from user_follow where user_id=:userid and follow_type=0)", pageInfo, Parameters);
var list = (pageResult.Rows as IEnumerable <dynamic>).Select(m => { m.imgpath = Convert.ToString(m.imgpath).Split(',')[0]; return(m); });
pageResult.Rows = list;
return(pageResult);
}
/// <summary>
/// 分页获取首页营业厅数据
/// </summary>
/// <param name="lon">用户的精度</param>
/// <param name="lat">用户的纬度</param>
/// <param name="carrierno">营业厅类型</param>
/// <param name="pageindex">当前页</param>
/// <param name="rows">每页数据条数</param>
/// <returns></returns>
public object GetHallByPage(decimal lon, decimal lat, int carrierno, PageInfo pageinfo)
{
OracleDbParameters Parameters = new OracleDbParameters();
Parameters.Add("prelatitude", lat - 1, DbType.Double);
Parameters.Add("lastlatitude", lat + 1, DbType.Double);
Parameters.Add("prelongitude", lon - 1, DbType.Double);
Parameters.Add("lastlongitude", lon + 1, DbType.Double);
Parameters.Add("lon", lon, DbType.Double);
Parameters.Add("lat", lat, DbType.Double);
StringBuilder sSql = new StringBuilder();
string WhereString = "1=1";
if (carrierno >= 0)
{
WhereString = "carrier_no=:carrierno";
Parameters.Add("carrierno", carrierno);
}
sSql.AppendFormat(@"select t1.* from
(select t.hall_id 'hallid',
t.name 'hallname',
t.identification_type 'identificationtype',
t.hall_follow 'follow',
t.picpath 'picpath',
t.longitude 'longitude',
t.latitude 'latitude',
(select t2.title from
(select t.hall_id, t.title from business_promotion t where t.promotion_type!=2 and t.status=0 and t.start_time<=sysdate and t.end_time>=sysdate order by t.create_time desc) t2
where t2.hall_id=t.hall_id and rownum<=1) 'title'
from business_hall t where t.latitude>:prelatitude
and t.latitude<:lastlatitude
and t.longitude>:prelongitude
and t.longitude<:lastlongitude
and t.status=0 and {0}
order by abs(t.longitude-(:lon))+abs(t.latitude-(:lat))) t1", WhereString);
var pageResult = query.PaginationQuery <Dictionary <string, object> >(sSql.ToString(), pageinfo, Parameters);
var list = pageResult.Rows as List <Dictionary <string, object> >;
foreach (Dictionary <string, object> item in list)
{
double latitude = Convert.ToDouble(item["latitude"]); //营业厅纬度
double longitude = Convert.ToDouble(item["longitude"]); //营业厅经度
//计算距离
double distance = MathHelper.GetDistanceByTude((double)lat, (double)lon, latitude, longitude);
item.Add("distance", distance.ToString("f1"));
item["picpath"] = item["picpath"] != null ? item["picpath"].ToString().Split(',')[0] : string.Empty;
item["title"] = item["title"] == null ? "暂无活动" : item["title"];
item.Add("imgurl", string.Empty);
}
if (list != null)
{
list = list.OrderBy(m => Convert.ToDouble(m["distance"])).ToList();
}
pageResult.Rows = list;
return(pageResult);
}
/// <summary>
/// 用户关注
/// </summary>
/// <param name="followId">关注的对象的ID</param>
/// <param name="followtype">关注的类型（0：营业厅，1：营业员）</param>
/// <param name="UserId">用户ID</param>
/// <returns></returns>
public int Follow(int followId, int followtype, int UserId, out string weixinno)
{
OracleDbParameters Parameters = new OracleDbParameters();
Parameters.Add("v_user_id", UserId, DbType.Int32);
Parameters.Add("v_follow_id", followId, DbType.Int32);
Parameters.Add("v_follow_type", followtype, DbType.Int32);
Parameters.Add("v_code", null, DbType.Int32, ParameterDirection.Output);
Parameters.Add("v_weixin_no", null, DbType.String, ParameterDirection.Output, 50);
//执行关注存储过程
excute.ExcuteProcedure("sp_user_follow", Parameters);
int res = Parameters.Get <int>("v_code") == 100?1:0;
weixinno = Parameters.Get <string>("v_weixin_no");
return(res);
}
/// <summary>
/// 分页获取已关注营业员列表
/// </summary>
/// <param name="UserId"></param>
/// <returns></returns>
public object GetWaiterListByPage(int UserId, PageInfo pageInfo)
{
OracleDbParameters Parameters = new OracleDbParameters();
Parameters.Add("userid", UserId);
var pageResult = query.PaginationQuery <dynamic>(@" select t1.waiter_id 'waiterid',
t1.waiter_name 'waitername',
t1.img_url 'imgpath',
t1.user_follow 'waiterfollow',
t1.weixin_num 'weixinno',
t2.name 'name',
t1.mobile 'mobile'
from user_follow t
left join user_waiter t1 on t1.waiter_id=t.follow_num
left join business_hall t2 on t2.hall_id=t1.hall_id
where t.follow_type=1 and t.user_id=:userid and t1.status=0
order by t.follow_time desc", pageInfo, Parameters);
return(pageResult);
}
/// <summary>
/// 获取搜索分页数据
/// </summary>
/// <returns></returns>
public object GetSearchDataByPage(decimal lon, decimal lat, int carrierno, string keywords, PageInfo pageinfo)
{
OracleDbParameters Parameters = new OracleDbParameters();
Parameters.Add("keywords", string.Format("%{0}%", keywords));
Parameters.Add("prelatitude", lat - 1, DbType.Double);
Parameters.Add("lastlatitude", lat + 1, DbType.Double);
Parameters.Add("prelongitude", lon - 1, DbType.Double);
Parameters.Add("lastlongitude", lon + 1, DbType.Double);
StringBuilder sSql = new StringBuilder();
string WhereString = "1=1";
string AWhereString = "1=1";
if (carrierno >= 0)
{//运营商的查询
WhereString = "carrier_no=:carrierno";
AWhereString = "r1.carrier_no=:carrierno";
Parameters.Add("carrierno", carrierno);
}
sSql.AppendFormat(@"select t.*
from (select hall_id 'id',
name 'name',
0 'type', --类型 0营业厅 2活动
address 'address',
name 'hallname',
hall_id 'hallid',
longitude 'lon',
latitude 'lat'
from business_hall where status=0 and {0}
union select r.'id',
r.'name',
r.'type',
r1.address 'address',
r1.name 'hallname',
r1.hall_id 'hallid',
r1.longitude 'lon',
r1.latitude 'lat'
from
(select t.promotion_id 'id',
t1.title 'name',
2 'type',
(case when t.bind_type=1 then t.hall_id
when t.bind_type=0 then t2.hall_id
end) hall_id
from business_bind t
left join business_promotion t1 on t.promotion_id=t1.promotion_id
left join user_waiter t2 on t2.waiter_id=t.user_id and t2.status=0
where t1.status=0 and t1.start_time<=sysdate and t1.end_time>=sysdate
--两种活动 平台自建和营业员自建
union select t.promotion_id 'id',
t.title 'name',
2 'type',
t.hall_id
from business_promotion t
where t.status=0 and t.start_time<=sysdate and t.end_time>=sysdate and t.promotion_type!=2
) r
left join business_hall r1 on r.hall_id=r1.hall_id
where {1}
) t
where t.'name' like :keywords
and t.'lat'>:prelatitude
and t.'lat'<:lastlatitude
and t.'lon'>:prelongitude
and t.'lon'<:lastlongitude", WhereString, AWhereString);
var pageResult = query.PaginationQuery <Dictionary <string, object> >(sSql.ToString(), pageinfo, Parameters);
var list = pageResult.Rows as List <Dictionary <string, object> >;
foreach (var item in list)
{
double latitude = Convert.ToDouble(item["lat"]); //营业厅纬度
double longitude = Convert.ToDouble(item["lon"]); //营业厅经度
double distance = MathHelper.GetDistanceByTude((double)lat, (double)lon, latitude, longitude);
item.Add("distance", distance.ToString("f1"));
item.Remove("lon");
item.Remove("lat");
}
if (list != null)
{
list = list.OrderBy(m => m["distance"]).ToList();
}
pageResult.Rows = list;
pageResult.Data = keywords;
return(pageResult);
}
