/// <summary>
/// 获取轮播图
/// </summary>
/// <param name="type">0 资讯轮播图 1首页轮播图</param>
/// <returns>List<FocusPicModel>.</returns>
public List <FocusPicModel> GetAppList(int type)
{
string strSql = APP_SELECT + " and IsEnable =1 ";
//0 资讯轮播图 1首页轮播图
if (type == 0)
{
strSql += " and Type=0 ";
}
else
{
strSql += " and Type=2 ";
}
strSql += " order by Sort desc";
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql))
{
List <FocusPicModel> data = DbHelperSQLP.GetEntityList <FocusPicModel>(dr);
if (data != null)
{
data.ForEach((item) =>
{
item.PicUrl = WebConfig.reswebsite() + item.PicUrl;
});
}
return(data);
}
}
/// <summary>
/// 查询数量
/// </summary>
/// <param name="danhao"></param>
/// <param name="time"></param>
/// <returns></returns>
public baozhuang_chuhuo QueryByShul(string 备货单, string 出货日期)
{
string sql = "";
if (备货单 != string.Empty && 出货日期.ToString() != string.Empty)
{
sql = @"SELECT 备货单,出货日期,sum(出货数量) as 总数量 FROM [baozhuang_chuhuo]
where 备货单 like '%" + 备货单 + "%' and CONVERT(varchar(10),出货日期,23) like '%" + 出货日期 + "%' group by 备货单,出货日期";
}
else if (备货单 != string.Empty && 出货日期.ToString() == string.Empty)
{
sql = "SELECT 备货单,sum(出货数量) as 总数量 FROM [baozhuang_chuhuo] where 备货单 like '%" + 备货单 + "%' group by 备货单";
}
else if (备货单 == string.Empty && 出货日期.ToString() != string.Empty)
{
sql = @"SELECT 出货日期,sum(出货数量) as 总数量 FROM [baozhuang_chuhuo]
where CONVERT(varchar(10),出货日期,23) like '%" + 出货日期 + "%'group by 出货日期";
}
else
{
sql = "select sum(出货数量) as 总数量 from baozhuang_chuhuo";
}
//baozhuang_chuhuo model = new baozhuang_chuhuo();
// DataSet ds = dbhelper1.Query(sql.ToString());
//总数量 = Convert.ToInt32(parameters[0].Value);
SqlDataReader sdr = dbhelper1.ExecuteReader(sql.ToString());
sdr.Read();
//总数量 = Convert.ToInt32(sdr.GetOrdinal("总数量").ToString());
return(sdr.ToEntity <baozhuang_chuhuo>());
}
public List <StatisticsListModel> OrderStatistics(int shopId, int userIdentity, string startTime, string endTime)
{
string strSql = @"select CONVERT(nvarchar(10),orderTime,121) as xData,COUNT(1) as yData,OrderStatus as Code from BM_Orders
where
CONVERT(nvarchar(10),orderTime,121)>=@startTime
and CONVERT(nvarchar(10),orderTime,121)<=@endTime";
if (userIdentity == 1)
{
strSql += " and BelongOneShopId=@ShopId";
}
else if (userIdentity == 2)
{
strSql += " and ShopId=@ShopId";
}
var param = new[] {
new SqlParameter("@startTime", startTime),
new SqlParameter("@endTime", endTime),
new SqlParameter("@ShopId", shopId)
};
strSql += " group by CONVERT(nvarchar(10),orderTime,121),OrderStatus";
strSql += " order by CONVERT(nvarchar(10),orderTime,121),OrderStatus";
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntityList <StatisticsListModel>(dr));
}
}
/// <summary>
/// 获取分页数据集合
/// </summary>
/// <typeparam name="T"></typeparam>
///<param name="PageSize"></param>
///<param name="PageIndex"></param>
/// <param name="strSql"></param>
///<param name="orderbyField">排序字段</param>
/// <param name="commandParameters"></param>
/// <param name="callback">回调处理函数</param>
/// <returns></returns>
public ResultPageModel getPageData <T>(int PageSize, int PageIndex, string strSql, string orderbyField, SqlParameter[] commandParameters, Action <List <T> > callback, bool orderby = false) where T : new()
{
string querySql = DbHelperSQLP.buildPageSql(PageIndex, PageSize, strSql, orderbyField, orderby);
string recordCountSql = DbHelperSQLP.buildRecordCountSql(strSql);
ResultPageModel result = new ResultPageModel();
int recordCount = Convert.ToInt32(DbHelperSQLP.ExecuteScalar(WebConfig.getConnectionString(), CommandType.Text, recordCountSql, commandParameters));
List <T> lst = new List <T>();
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, querySql, commandParameters))
{
lst = DbHelperSQLP.GetEntityList <T>(dr);
callback?.Invoke(lst);
}
int pageCount = recordCount / PageSize;
if (recordCount % PageSize != 0)
{
++pageCount;
}
result.PageIndex = PageIndex;
result.PageSize = PageSize;
result.Total = recordCount;
result.PageCount = pageCount;
result.Rows = lst;
return(result);
}
/// <summary>
/// 获取盟友的订单列表
/// </summary>
/// <param name="userId"></param>
/// <param name="status"></param>
/// <param name="lastId"></param>
/// <returns></returns>
public List <OrderModel> GetUserOrderList(int userId, int status, long lastId)
{
string strSql = "select top 10 * from BM_Orders where Ct_BelongId=@UserId";
if (status > 0)
{
strSql += " and OrderStatus=" + status;
}
if (lastId > 0)
{
strSql += " and CreateTime<" + StringHelper.GetTimeFromUTC(lastId);
}
strSql += " order by CreateTime desc";
SqlParameter[] parameters =
{
new SqlParameter("@UserId", userId)
};
List <OrderModel> list = new List <OrderModel>();
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql.ToString(), parameters))
{
list = DbHelperSQLP.GetEntityList <OrderModel>(dr);
}
return(list);
}
/// <summary>
/// 根据CODE获取实体信息
/// </summary>
/// <param name="code">The code.</param>
/// <returns>ConfigModel.</returns>
public ConfigModel GetModel(string code)
{
var parm = new[] { new SqlParameter("@Code", code) };
ConfigModel model = null;
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, SQL_SELECT, parm))
{
model = DbHelperSQLP.GetEntity <ConfigModel>(dr);
}
return(model);
}
/// <summary>
/// 获取所有的配置数据
/// </summary>
/// <returns>List<ConfigModel>.</returns>
public List <ConfigModel> List()
{
string strsql = @"select Code,Value,Remark from BM_BaseConfig with(nolock) ";
List <ConfigModel> list = new List <ConfigModel>();
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strsql, null))
{
list = DbHelperSQLP.GetEntityList <ConfigModel>(dr);
}
return(list);
}
/// <summary>
///客户饼状图
/// </summary>
/// <param name="shopid">The shopid.</param>
/// <param name="useridentity">The useridentity.</param>
/// <param name="startTime">The start time.</param>
/// <param name="endTime">The end time.</param>
/// <returns>List<StatisticsListModel>.</returns>
public List <StatisticsListModel> CustomerStatisticsPie(int shopid, int useridentity, string startTime, string endTime)
{
if (useridentity != 2)
{
string strSql = @"select shop.ShopName as xData,COUNT(1) as yData from BM_CustomerManage as c
left join BM_ShopManage as shop on c.ShopId=shop.shopid
where 1=1 and c.Status=1
and CONVERT(nvarchar(10), c.CreateTime, 121)>=@startTime
and CONVERT(nvarchar(10), c.CreateTime, 121)<=@endTime
";
if (useridentity == 0)
{
strSql += " and shop.ShopType=1";
}
else if (useridentity == 1)
{
strSql += " and (c.BelongOneShopId=@ShopId or c.ShopId=@ShopId)";
}
else if (useridentity == 2)
{
strSql += " and shop.ShopId=@ShopId";
}
strSql += "group by shop.ShopName order by count(1) desc";
var param = new[] {
new SqlParameter("@startTime", startTime),
new SqlParameter("@endTime", endTime),
new SqlParameter("@ShopId", shopid)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntityList <StatisticsListModel>(dr));
}
}
else
{
string strSql = @"select u.UB_UserRealName as xData,COUNT(1) as yData from BM_CustomerManage as c
left join Hot_UserBaseInfo as u with(nolock) on c.BelongOne=u.UB_UserID
where 1=1 and c.Status=1 and u.UB_UserType=1
and CONVERT(nvarchar(10), c.CreateTime, 121)>=@startTime
and CONVERT(nvarchar(10), c.CreateTime, 121)<=@endTime
";
strSql += "group by u.UB_UserRealName order by count(1) desc";
var param = new[] {
new SqlParameter("@startTime", startTime),
new SqlParameter("@endTime", endTime),
new SqlParameter("@ShopId", shopid)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntityList <StatisticsListModel>(dr));
}
}
}
/// <summary>
/// 获取客户最新的一条维护信息
/// </summary>
/// <param name="CID"></param>
/// <returns></returns>
public CustomerAssertModel GetCustomerAssertModel(int CID)
{
string strSql = "select top 1 ID,CID,UserId,AssertContent,CreateTime from BM_CustomerAssert where CID=@CID order by CreateTime desc";
var param = new[] {
new SqlParameter("@CID", CID),
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntity <CustomerAssertModel>(dr));
}
}
/// <summary>
/// 获取资讯信息
/// </summary>
/// <param name="articleId"></param>
/// <returns></returns>
public ArticleModel GetModel(int articleId)
{
string strSql = APP_SELECT + " and ArticleId=@ArticleId";
var param = new[] {
new SqlParameter("@ArticleId", articleId)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntity <ArticleModel>(dr));
}
}
/// <summary>
/// 获取客户信息
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <returns>CustomerModel.</returns>
public CustomerModel GetModel(int customerId)
{
string strSql = APP_SELECT + " and ID=@ID";
var param = new[] {
new SqlParameter("@ID", customerId),
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntity <CustomerModel>(dr));
}
}
/// <summary>
/// 获取系统菜单
/// </summary>
/// <param name="type">0总后台菜单，1总门店菜单，2分店菜单</param>
/// <returns></returns>
public List <SystemMenuModel> GetMenuList(int type)
{
string strSql = "select ID,ItemType,ItemCode,ItemNavLabel,ItemParentCode,ItemUrl,ItemShow,ItemIcons,CreateTime from BM_MenuList where ItemType=@ItemType ";
var param = new[] {
new SqlParameter("@ItemType", type)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntityList <SystemMenuModel>(dr));
}
}
/// <summary>
/// 获取管理员信息
/// </summary>
/// <param name="userid">The userid.</param>
/// <returns>AdminLoginModel.</returns>
/// <exception cref="System.NotImplementedException"></exception>
public AdminLoginModel GetModel(int userid)
{
string strSql = SELECT_SQL + " and ID=@ID";
SqlParameter[] param =
{
new SqlParameter("@ID", userid)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntity <AdminLoginModel>(dr));
}
}
/// <summary>
/// 获得优惠卷发送列表
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List <CouponSendModel> getCouponSendList(int userId)
{
List <CouponSendModel> list = new List <CouponSendModel>();
string strSql = "select * from BM_CouponSend where UserId=@UserId order by id desc";
var parms = new[] {
new SqlParameter("@UserId", userId)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, parms))
{
list = DbHelperSQLP.GetEntityList <CouponSendModel>(dr);
}
return(list);
}
/// <summary>
/// 获取消息信息
/// </summary>
/// <param name="messageId">The message identifier.</param>
/// <returns>MessageModel.</returns>
/// <exception cref="NotImplementedException"></exception>
public MessageModel GetModel(int messageId)
{
MessageModel model = new MessageModel();
string strSql = SELECT_SQL + " and ID=@ID";
var parms = new[] {
new SqlParameter("@ID", messageId)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql.ToString(), parms))
{
model = DbHelperSQLP.GetEntity <MessageModel>(dr);
}
return(model);
}
/// <summary>
///获取盟友奖励信息
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <returns>RewardsSettingModel.</returns>
public RewardsSettingModel GetRewardModel(int userId)
{
string strSql = "select top 1 UserId,CustomerReward,OrderReward,ShopReward,UpdateTime,CreateTime from BM_RewardsSetting where UserId=@UserId";
SqlParameter[] param =
{
new SqlParameter("@UserId", userId)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntity <RewardsSettingModel>(dr));
}
}
/// <summary>
/// 前端登录
/// </summary>
/// <param name="loginName">Name of the login.</param>
/// <param name="loginPassword">The login password.</param>
/// <returns>UserModel.</returns>
public UserModel Login(string loginName, string loginPassword)
{
string strSql = APP_USER_SELECT + " and U.UB_UserLoginName=@LoginName and U.UB_UserLoginPassword=@LoginPassword";
SqlParameter[] parm =
{
new SqlParameter("@LoginName", loginName),
new SqlParameter("@LoginPassword", loginPassword)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, parm))
{
return(DbHelperSQLP.GetEntity <UserModel>(dr));
}
}
/// <summary>
/// 根据CODE获取值
/// </summary>
/// <param name="code">The code.</param>
/// <returns>System.String.</returns>
public string GetValue(string code)
{
string val = string.Empty;
string strsql = @"select Value from BM_BaseConfig with(nolock) where Code=@Code";
var parm = new[] { new SqlParameter("@Code", code) };
SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strsql, parm);
if (dr.Read())
{
val = !dr.IsDBNull(0) ? dr.GetString(0) : string.Empty;
}
dr.Close();
return(val);
}
/// <summary>
/// 获取门店信息
/// </summary>
/// <param name="shopId">The shop identifier.</param>
/// <returns>ShopModel.</returns>
public ShopModel GetShopModel(int shopId)
{
string strSql = "select ShopID,ShopName,ShopType,ShopBelongId,ShopProv,ShopCity,ShopArea,ShopAddress,Contacts,ContactWay,LoginName,CreateTime,IsActive from BM_ShopManage where 1=1 and IsActive=1 ";
strSql += " and ShopID=@ShopID";
var param = new[] {
new SqlParameter("@ShopID", shopId)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntity <ShopModel>(dr));
}
}
/// <summary>
/// 根据用户ID和优惠券ID，获取优惠券记录ID
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <param name="couponId">The coupon identifier.</param>
/// <returns>System.Int32.</returns>
public CashCouponLogModel GetCashCouponLogIDByUserID(int userId, int couponId)
{
string strSql = @"select top 1 L.*,C.Remark from BM_GetCashCouponLog L
left join BM_CashCoupon C on C.CouponId=L.CouponId
where L.UserId=@UserId and L.CouponId=@CouponId and L.IsGet=0 and L.IsShare=1 and L.IsDel=0";
var param = new[] {
new SqlParameter("@UserId", userId),
new SqlParameter("@CouponId", couponId)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntity <CashCouponLogModel>(dr));
}
}
/// <summary>
/// 获取优惠卷信息
/// </summary>
/// <param name="mobile"></param>
/// <param name="cashNo"></param>
/// <returns></returns>
public CashCouponLogModel getEnableCashCouponLogModel(string mobile, string cashNo)
{
CashCouponLogModel model = new CashCouponLogModel();
string strSql = "select * from BM_GetCashCouponLog where CouponNo=@CouponNo and Mobile=@Mobile and IsGet=1 and IsDel=0 ";
var parms = new[] {
new SqlParameter("@CouponNo", cashNo),
new SqlParameter("@Mobile", mobile)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql.ToString(), parms))
{
model = DbHelperSQLP.GetEntity <CashCouponLogModel>(dr);
}
return(model);
}
/// <summary>
/// 获得现金卷列表
/// </summary>
/// <param name="shopId"></param>
/// <returns></returns>
public List <CashCouponModel> getEnabledCashCouponList(int shopId)
{
List <CashCouponModel> list = new List <CashCouponModel>();
string strSql = "select * from BM_CashCoupon where ShopId=@ShopId and EndTime>@Date and IsEnable=1 and IsDel=0 order by CouponId desc";
var parms = new[] {
new SqlParameter("@ShopId", shopId),
new SqlParameter("@Date", DateTime.Now)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, parms))
{
list = DbHelperSQLP.GetEntityList <CashCouponModel>(dr);
}
return(list);
}
/// <summary>
/// 获得客户信息
/// </summary>
/// <param name="mobile"></param>
/// <param name="address"></param>
/// <returns></returns>
public CustomerModel getCustomerModel(int cid)
{
//审核状态 1已同意 2已拒绝 3未生成订单 4已生成订单，5已失效
CustomerModel model = new CustomerModel();
string strSql = "select * from BM_CustomerManage where ID=@ID and IsDel=0 and Status in(1,3,4,5)";
var parms = new[] {
new SqlParameter("@ID", cid)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql.ToString(), parms))
{
model = DbHelperSQLP.GetEntity <CustomerModel>(dr);
}
return(model);
}
/// <summary>
/// 获得客户信息
/// </summary>
/// <param name="mobile"></param>
/// <param name="address"></param>
/// <returns></returns>
public CustomerModel getCustomerModel(string mobile, string address)
{
CustomerModel model = new CustomerModel();
string strSql = "select * from BM_CustomerManage where (Addr=@Addr or Mobile=@Mobile) and IsDel=0 and Status in(1,3,4,5)";
var parms = new[] {
new SqlParameter("@Addr", address),
new SqlParameter("@Mobile", mobile)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql.ToString(), parms))
{
model = DbHelperSQLP.GetEntity <CustomerModel>(dr);
}
return(model);
}
/// <summary>
/// 根据盟友ID，获取盟友的现金券列表
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <returns>List<CashCouponModel>.</returns>
public List <CashCouponModel> GetEnableCashCouponListByUserId(int userId)
{
string strSql = @"select g.CouponId as CouponId,c.Title,g.CouponNo,g.StartTime,g.EndTime,g.Money from BM_GetCashCouponLog g
left join BM_CashCoupon c on c.CouponId = g.CouponId
where c.IsEnable = 1 and g.UserId =@UserId and g.EndTime>@Date and isShare=0 and c.IsDel=0 order by g.ID desc";
var parms = new[] {
new SqlParameter("@UserId", userId),
new SqlParameter("@Date", DateTime.Now)
};
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, parms))
{
return(DbHelperSQLP.GetEntityList <CashCouponModel>(dr));
}
}
public OrderModel GetModel(string orderId)
{
string strSql = "select * from BM_Orders where orderId=@orderId";
SqlParameter[] parameters =
{
new SqlParameter("@orderId", orderId)
};
OrderModel model = null;
using (IDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql.ToString(), parameters))
{
model = DbHelperSQLP.GetEntity <OrderModel>(dr);
}
return(model);
}
/// <summary>
/// 获取用户实体信息
/// </summary>
/// <param name="UserId"></param>
/// <returns></returns>
public UserModel GetUserModel(int UserId)
{
string strSql = APP_USER_SELECT + " and ue.UserId=@UserId";
var param = new[] {
new SqlParameter("@UserId", UserId)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
var data = DbHelperSQLP.GetEntity <UserModel>(dr);
//TODO:
data.CustomerAmount = 100;
data.OrderSuccessAmount = 10;
return(data);
}
}
/// <summary>
/// 获取管理员订单饼图统计
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public List <StatisticsListModel> OrderStatisticsPieByAdmin(string startTime, string endTime)
{
string strSql = @"select log.BelongOneShopId,shop.ShopName as xData ,count(1) as yData from BM_Orders as log
left join BM_ShopManage as shop on log.BelongOneShopId=shop.shopid
where CONVERT(nvarchar(10), log.orderTime, 121)>=@startTime
and CONVERT(nvarchar(10), log.orderTime, 121)<=@endTime
group by log.BelongOneShopId,shop.ShopName
order by count(1) desc";
var param = new[] {
new SqlParameter("@startTime", startTime),
new SqlParameter("@endTime", endTime)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntityList <StatisticsListModel>(dr));
}
}
/// <summary>
/// 获取分店订单统计
/// </summary>
/// <param name="shopId"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public List <StatisticsListModel> OrderStatisticsPieByShop(int shopId, string startTime, string endTime)
{
string strSql = @"select log.UserId ,shop.UB_UserRealName as xData ,count(1) as yData from BM_Orders as log
left join Hot_UserBaseInfo as shop on log.UserId=shop.UB_UserID
where CONVERT(nvarchar(10), log.orderTime, 121)>=@startTime
and CONVERT(nvarchar(10), log.orderTime, 121)<=@endTime and log.ShopId=@ShopId
group by log.UserId,shop.UB_UserRealName
order by count(1) desc";
var param = new[] {
new SqlParameter("@startTime", startTime),
new SqlParameter("@endTime", endTime),
new SqlParameter("@ShopId", shopId)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
return(DbHelperSQLP.GetEntityList <StatisticsListModel>(dr));
}
}
/// <summary>
/// 获取信息实体
/// </summary>
/// <param name="mailId">The mail identifier.</param>
/// <returns>MailModel.</returns>
public MailModel GetMailModel(int mailId)
{
string strSql = "select ID,AuthorId,AuthorName,SendType,Title,BodyContent,CoverUrl,SendTime,IsRead,ReplyUserId,ReplyPid,ReplyTime from BM_Mail where ID=@ID and ReplyPid=0";
var param = new[] {
new SqlParameter("@ID", mailId)
};
using (SqlDataReader dr = DbHelperSQLP.ExecuteReader(WebConfig.getConnectionString(), CommandType.Text, strSql, param))
{
var data = DbHelperSQLP.GetEntity <MailModel>(dr);
if (data != null)
{
data.time = StringHelper.GetConvertFriendlyTime(data.SendTime.ToString(), 7);
}
return(data);
}
}