public async Task <JsonResult> GetDataByPage(UserPermissionRequest request)
{
var returnValue = new List <MemberShipLivingRightsModel>();
if (request == null)
{
return(Json(new { code = -1, msg = "无参数信息", count = 0, data = returnValue }, JsonRequestBehavior.AllowGet));
}
if (request.Page <= 0)
{
request.Page = 1;
}
if (request.Limit <= 0)
{
request.Limit = 50;
}
var memberBll = new MemberService();
var requestModel = new MemberShipLivingRightsModel {
ChannelName = request.ChannelName
};
var count = DALMembershipLivingRights.QueryPageCount(requestModel);
returnValue = DALMembershipLivingRights.QueryPageList(requestModel, request.Page, request.Limit);
return(Json(new { code = 0, msg = "加载成功", count = count, data = returnValue.OrderByDescending(t => t.PKID) }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取生活权益数量
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static int QueryPageCount(MemberShipLivingRightsModel search)
{
var strCount = new StringBuilder();
var strCondition = new StringBuilder();
var sqlParamerts = new List <SqlParameter>();
#region 获取数量
strCondition.Append(@"
SELECT ISNULL(COUNT(*), 0) AS totalCount
FROM [Tuhu_profiles].[dbo].[MembershipLivingRights] WITH ( NOLOCK )
WHERE IsDeleted = 0
");
#endregion
#region 查询条件
if (search != null)
{
if (!string.IsNullOrWhiteSpace(search.ChannelName))
{
strCondition.AppendFormat(" And ChannelName like @ChannelName");
sqlParamerts.Add(new SqlParameter("@ChannelName", "%" + search.ChannelName + "%"));
}
if (!string.IsNullOrWhiteSpace(search.WelfareContent))
{
strCondition.AppendFormat(" And WelfareContent like @WelfareContent");
sqlParamerts.Add(new SqlParameter("@WelfareContent", "%" + search.WelfareContent + "%"));
}
}
#endregion
using (var countCmd = new SqlCommand(strCondition.ToString()))
{
countCmd.Parameters.AddRange(sqlParamerts.ToArray());
var count = DbHelper.ExecuteScalar(countCmd);
return(Convert.ToInt32(count));
}
}
public JsonResult Save(MemberShipLivingRightsModel model)
{
if (model == null)
{
return(Json(new { result = 0, msg = "无法获取数据对象" }));
}
var result = 0;
model.LastUpdateBy = HttpContext.User.Identity.Name;
if (model.PKID <= 0)
{
model.LastUpdateBy = model.LastUpdateBy;
result = DALMembershipLivingRights.Add(model);
}
else
{
model.LastUpdateDateTime = DateTime.Now;
result = DALMembershipLivingRights.Update(model);
}
var oprLog = new Tuhu.Provisioning.DataAccess.Entity.OprLog
{
ObjectID = model.PKID,
ObjectType = "UserLivingRights",
Author = HttpContext.User.Identity.Name,
Operation = model.PKID > 0 ? "更新" : "新增" + "UserLivingRights配置"
};
var operLogManager = new Business.OprLogManagement.OprLogManager();
operLogManager.AddOprLogAsync(oprLog);
return(Json(new { result = result, msg = "操作成功" }));
}
/// <summary>
/// 添加生活权益
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Add(MemberShipLivingRightsModel model)
{
var sqlParamerts = new List <SqlParameter>();
#region sql脚本
var strSql = @" INSERT INTO [Tuhu_profiles].[dbo].[MembershipLivingRights] ( [ChannelId] ,
[ChannelName] ,
[WelfareContent] ,
[RightsValue] ,
[ReceivingType] ,
[ReceivingDescription] ,
[CouponId] ,
[CouponDescription] ,
[LinkUrl] ,
[SortIndex] ,
[CreateDatetime] ,
[CreateBy] ,
[LastUpdateDateTime] ,
[LastUpdateBy] ,
[IsDeleted]
)
VALUES ( @ChannelId ,
@ChannelName ,
@WelfareContent ,
@RightsValue ,
@ReceivingType ,
@ReceivingDescription ,
@CouponId ,
@CouponDescription ,
@LinkUrl ,
@SortIndex ,
GetDate(),
@LastUpdateBy,
GetDate(),
@LastUpdateBy,
0
)";
#endregion
#region 参数化赋值
sqlParamerts.Add(new SqlParameter("@ChannelId", model.ChannelId));
sqlParamerts.Add(new SqlParameter("@ChannelName", model.ChannelName));
sqlParamerts.Add(new SqlParameter("@WelfareContent", model.WelfareContent));
sqlParamerts.Add(new SqlParameter("@RightsValue", model.RightsValue));
sqlParamerts.Add(new SqlParameter("@ReceivingType", model.ReceivingType));
sqlParamerts.Add(new SqlParameter("@ReceivingDescription", model.ReceivingDescription));
sqlParamerts.Add(new SqlParameter("@CouponId", model.CouponId));
sqlParamerts.Add(new SqlParameter("@CouponDescription", model.CouponDescription));
sqlParamerts.Add(new SqlParameter("@LinkUrl", model.LinkUrl));
sqlParamerts.Add(new SqlParameter("@SortIndex", model.SortIndex));
sqlParamerts.Add(new SqlParameter("@LastUpdateBy", model.LastUpdateBy));
#endregion
using (var cmd = new SqlCommand(strSql))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(sqlParamerts.ToArray());
return(DbHelper.ExecuteNonQuery(cmd));
}
}
/// <summary>
/// 获取生活权益列表
/// </summary>
/// <param name="search"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static List <MemberShipLivingRightsModel> QueryPageList(MemberShipLivingRightsModel search, int pageIndex, int pageSize)
{
var strSql = new StringBuilder();
var strCondition = new StringBuilder();
var sqlParamerts = new List <SqlParameter>();
#region 查询条件
if (search != null)
{
if (!string.IsNullOrWhiteSpace(search.ChannelName))
{
strCondition.AppendFormat(" And ChannelName like @ChannelName");
sqlParamerts.Add(new SqlParameter("@ChannelName", "%" + search.ChannelName + "%"));
}
if (!string.IsNullOrWhiteSpace(search.WelfareContent))
{
strCondition.AppendFormat(" And WelfareContent like @WelfareContent");
sqlParamerts.Add(new SqlParameter("@WelfareContent", "%" + search.WelfareContent + "%"));
}
}
#endregion
#region sql语句
strSql.AppendFormat(@"SELECT [PKID]
,[ChannelId]
,[ChannelName]
,[WelfareContent]
,[RightsValue]
,[ReceivingType]
,[ReceivingDescription]
,[CouponId]
,[CouponDescription]
,[LinkUrl]
,[SortIndex]
,[CreateDatetime]
,[CreateBy]
,[LastUpdateDateTime]
,[LastUpdateBy]
,[IsDeleted]
FROM [Tuhu_profiles].[dbo].[MembershipLivingRights] WITH ( NOLOCK ) WHERE IsDeleted=0 {0} ORDER BY PKID DESC OFFSET(@PageIndex-1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY", strCondition.ToString());
#endregion
sqlParamerts.Add(new SqlParameter("@PageIndex", pageIndex));
sqlParamerts.Add(new SqlParameter("@PageSize", pageSize));
using (var dataCmd = new SqlCommand(strSql.ToString()))
{
dataCmd.Parameters.AddRange(sqlParamerts.ToArray());
return(DbHelper.ExecuteDataTable(dataCmd)?.ConvertTo <MemberShipLivingRightsModel>().ToList());
}
}
public ActionResult Edit(int id)
{
if (id <= 0)
{
ViewBag.Title = "添加会员生活权益信息";
return(View(new MemberShipLivingRightsModel()));
}
else
{
ViewBag.Title = "编辑会员生活权益信息";
var model = DALMembershipLivingRights.GetModelById(id);
if (model == null)
{
model = new MemberShipLivingRightsModel();
}
return(View(model));
}
}
/// <summary>
/// 更新生活权益
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Update(MemberShipLivingRightsModel model)
{
var sqlParamerts = new List <SqlParameter>();
#region sql脚本
var strSql = @"
UPDATE [Tuhu_profiles].[dbo].[MembershipLivingRights] WITH ( ROWLOCK )
SET [ChannelId] = @ChannelId,
[ChannelName] = @ChannelName ,
[WelfareContent] = @WelfareContent ,
[RightsValue] = @RightsValue ,
[ReceivingType] = @ReceivingType ,
[ReceivingDescription] =@ReceivingDescription ,
[CouponId] = @CouponId ,
[CouponDescription] =@CouponDescription ,
[LinkUrl] = @LinkUrl ,
[SortIndex] =@SortIndex ,
[LastUpdateDateTime] = GETDATE() ,
[LastUpdateBy] =@LastUpdateBy
WHERE PKID = @PKID
";
#endregion
#region 参数化赋值
sqlParamerts.Add(new SqlParameter("@ChannelId", model.ChannelId));
sqlParamerts.Add(new SqlParameter("@ChannelName", model.ChannelName));
sqlParamerts.Add(new SqlParameter("@WelfareContent", model.WelfareContent));
sqlParamerts.Add(new SqlParameter("@RightsValue", model.RightsValue));
sqlParamerts.Add(new SqlParameter("@ReceivingType", model.ReceivingType));
sqlParamerts.Add(new SqlParameter("@ReceivingDescription", model.ReceivingDescription));
sqlParamerts.Add(new SqlParameter("@CouponDescription", model.CouponDescription));
sqlParamerts.Add(new SqlParameter("@LinkUrl", model.LinkUrl));
sqlParamerts.Add(new SqlParameter("@CouponId", model.CouponId));
sqlParamerts.Add(new SqlParameter("@SortIndex", model.SortIndex));
sqlParamerts.Add(new SqlParameter("@LastUpdateBy", model.LastUpdateBy));
sqlParamerts.Add(new SqlParameter("@PKID", model.PKID));
#endregion
using (var cmd = new SqlCommand(strSql))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(sqlParamerts.ToArray());
return(DbHelper.ExecuteNonQuery(cmd));
}
}