/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Model.UserTicket model)
{
if (model == null)
{
return(-1);
}
//定义查询命令
string cmdText = @"update [UserTicket] set PrizeTicketID = @PrizeTicketID,Status = @Status,LastUpdatedDate = @LastUpdatedDate where NumberID = @NumberID";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@NumberID", SqlDbType.UniqueIdentifier),
new SqlParameter("@PrizeTicketID", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserID", SqlDbType.UniqueIdentifier),
new SqlParameter("@Status", SqlDbType.TinyInt),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = Guid.Parse(model.NumberID.ToString());
parms[1].Value = Guid.Parse(model.PrizeTicketID.ToString());
parms[2].Value = Guid.Parse(model.UserID.ToString());
parms[3].Value = model.Status;
parms[4].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.UserTicket model)
{
if (model == null)
{
return(-1);
}
string cmdText = "insert into [UserTicket] (PrizeTicketID,UserID,Status,LastUpdatedDate) values (@PrizeTicketID,@UserID,@Status,@LastUpdatedDate)";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@PrizeTicketID", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserID", SqlDbType.UniqueIdentifier),
new SqlParameter("@Status", SqlDbType.TinyInt),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = Guid.Parse(model.PrizeTicketID.ToString());
parms[1].Value = Guid.Parse(model.UserID.ToString());
parms[2].Value = model.Status;
parms[3].Value = model.LastUpdatedDate;
//执行数据库操作
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 获取对应的数据
/// </summary>
/// <param name="numberId"></param>
/// <returns></returns>
public Model.UserTicket GetModel(string numberId)
{
Model.UserTicket model = null;
string cmdText = @"select top 1 NumberID,UserID,PrizeTicketID,Status,LastUpdatedDate from [UserTicket] where NumberID = @NumberID order by CreateDate desc ";
SqlParameter parm = new SqlParameter("@NumberID", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(numberId);
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new Model.UserTicket();
model.NumberID = reader["NumberID"].ToString();
model.PrizeTicketID = reader["PrizeTicketID"].ToString();
model.UserID = reader["UserID"].ToString();
model.Status = short.Parse(reader["Status"].ToString());
model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString());
}
}
}
return(model);
}
/// <summary>
/// 获取数据分页列表，并返回所有记录数
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <param name="sqlWhere"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public List <Model.UserTicket> GetList(int pageIndex, int pageSize, out int totalCount, string sqlWhere, params SqlParameter[] commandParameters)
{
//获取数据集总数
string cmdText = "select count(*) from [UserTicket] t1 ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
totalCount = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, commandParameters);
//返回分页数据
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
cmdText = @"select * from(select row_number() over(order by t1.LastUpdatedDate desc) as RowNumber,t1.NumberID,t1.PrizeTicketID,t1.UserID, t1.Status,t1.LastUpdatedDate from [UserTicket] t1 ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <Model.UserTicket> list = null;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, commandParameters))
{
if (reader != null && reader.HasRows)
{
list = new List <Model.UserTicket>();
while (reader.Read())
{
Model.UserTicket model = new Model.UserTicket();
model.NumberID = reader["NumberID"].ToString();
model.PrizeTicketID = reader["PrizeTicketID"].ToString();
model.UserID = reader["UserID"].ToString();
model.Status = short.Parse(reader["Status"].ToString());
model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString());
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 获取满足当前条件的数据列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sqlWhere"></param>
/// <param name="cmdParams"></param>
/// <returns></returns>
public List <Model.UserTicket> GetList(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParams)
{
List <Model.UserTicket> list = new List <Model.UserTicket>();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
string cmdText = @"select * from(select row_number() over(order by t1.LastUpdatedDate desc) as RowNumber,t1.NumberID,t1.PrizeTicketID,t1.UserID, t1.Status,t1.LastUpdatedDate,u.UserName,pt.TicketName from [UserTicket] t1
left join Aspnet_Users u on u.UserId = t1.UserID left join PrizeTicket pt on pt.NumberID = t1.PrizeTicketID ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParams))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
Model.UserTicket model = new Model.UserTicket();
model.NumberID = reader["NumberID"].ToString();
model.PrizeTicketID = reader["PrizeTicketID"].ToString();
model.UserID = reader["UserID"].ToString();
model.Status = short.Parse(reader["Status"].ToString());
model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString());
model.UserName = reader["UserName"].ToString();
model.TicketName = reader["TicketName"].ToString();
list.Add(model);
}
}
}
return(list);
}
private void OnSave(object nId)
{
BLL.PrizeTicket ptBll = new BLL.PrizeTicket();
Model.PrizeTicket ptModel = ptBll.GetModel(nId.ToString());
if (ptModel == null)
{
WebHelper.MessageBox.Messager(this.Page, rpData.Controls[0], "没有找到相关奖品");
return;
}
decimal pointNum = ptModel.PointNum;
BLL.UserPoint uBll = new BLL.UserPoint();
object userId = WebHelper.Common.GetUserId();
DateTime currTime = DateTime.Now;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
Model.UserPoint uModel = uBll.GetModelByUser(userId);
if (uModel == null)
{
WebHelper.MessageBox.Messager(this.Page, rpData.Controls[0], "您的棋子数额不足，无法兑换");
return;
}
decimal userPointNum = uModel.PointNum;
decimal currPointNum = userPointNum - pointNum;
if (currPointNum < 0)
{
WebHelper.MessageBox.Messager(this.Page, rpData.Controls[0], "您的棋子数额不足，无法兑换");
return;
}
Model.UserTicket utModel = new Model.UserTicket();
utModel.PrizeTicketID = ptModel.NumberID;
utModel.UserID = userId;
utModel.Status = 0;
utModel.LastUpdatedDate = currTime;
BLL.UserTicket utBll = new BLL.UserTicket();
if (utBll.Insert(utModel) > 0)
{
uModel.PointNum = currPointNum;
uModel.LastUpdatedDate = currTime;
if (uBll.Update(uModel) > 0)
{
//操作成功，提交事务
scope.Complete();
Response.Redirect("/u/ta.html");
}
else
{
WebHelper.MessageBox.Messager(this.Page, rpData.Controls[0], "奖品兑换失败，无法兑换", "系统异常");
return;
}
}
else
{
WebHelper.MessageBox.Messager(this.Page, rpData.Controls[0], "奖品兑换失败，无法兑换", "系统异常");
return;
}
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Model.UserTicket model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.UserTicket model)
{
return(dal.Insert(model));
}
