/// <summary>
/// 添加一条分润订单记录
/// </summary>
/// <param name="transaction"></param>
/// <param name="userId">用户Id</param>
private void AddShareOrder(DbTransaction transaction, long userId)
{
var sql =
@"INSERT INTO [dbo].[Things_ShareOrder] ([UserId] ,[Amount] ,[EntityId] ,[Parameters] ,[Status],[SystemStatus] ,[TriggerType] ,[Summary],[CreateTime] ,[UpdateTime],[Extension],[ExecuteCount])
VALUES
(@UserId ,@Amount ,@EntityId ,@Parameters ,@Status,@SystemStatus ,@TriggerType ,@Summary,@CreateTime ,@UpdateTime,@Extension,@ExecuteCount)
";
var parameters = new[]
{
RepositoryContext.CreateParameter("@UserId", userId),
RepositoryContext.CreateParameter("@Amount", 1),
RepositoryContext.CreateParameter("@EntityId", userId),
RepositoryContext.CreateParameter("@Parameters", string.Empty),
RepositoryContext.CreateParameter("@Status", Convert.ToInt16(ShareOrderStatus.Pending)),
RepositoryContext.CreateParameter("@SystemStatus", Convert.ToInt16(ShareOrderSystemStatus.Pending)),
RepositoryContext.CreateParameter("@TriggerType", Convert.ToInt16(TriggerType.UserReg)),
RepositoryContext.CreateParameter("@Summary", string.Empty),
RepositoryContext.CreateParameter("@CreateTime", DateTime.Now),
RepositoryContext.CreateParameter("@Extension", string.Empty),
RepositoryContext.CreateParameter("@ExecuteCount", 0),
RepositoryContext.CreateParameter("@UpdateTime", DateTime.Now)
};
RepositoryContext.ExecuteScalar(transaction, sql, parameters);
}
public UserMap Add(UserMap userMap)
{
if (userMap == null)
{
throw new ArgumentNullException("userMap");
}
var sql = @"INSERT INTO [dbo].[User_UserMap]
([UserId] ,[LevelNumber],[TeamNumber]
,[ChildNode] ,[ParentMap])
VALUES
(@UserId ,@LevelNumber,@TeamNumber
,@ChildNode ,@ParentMap)";
var parameters = new[]
{
RepositoryContext.CreateParameter("@LevelNumber", userMap.LevelNumber),
RepositoryContext.CreateParameter("@TeamNumber", userMap.TeamNumber),
RepositoryContext.CreateParameter("@ChildNode", userMap.ChildNode),
RepositoryContext.CreateParameter("@ParentMap", userMap.ParentMap)
};
var result = RepositoryContext.ExecuteScalar(sql, parameters);
if (result != null && result != DBNull.Value)
{
userMap.Id = Convert.ToInt64(result);
}
return(userMap);
}
public bool UpdateSingle(User model)
{
if (model == null)
{
throw new ArgumentNullException("model");
}
var sql =
"UPDATE dbo.User_User SET Name=@Name, Email=@Email, Mobile=@Mobile, Status=@Status,GradeId=@GradeId WHERE Id=@Id";
var parameters = new[]
{
RepositoryContext.CreateParameter("@Name", model.Name),
RepositoryContext.CreateParameter("@Email", model.Email),
RepositoryContext.CreateParameter("@Mobile", model.Mobile),
RepositoryContext.CreateParameter("@Status", model.Status),
RepositoryContext.CreateParameter("@GradeId", model.GradeId),
RepositoryContext.CreateParameter("@Id", model.Id)
};
var count = RepositoryContext.ExecuteNonQuery(sql, parameters);
if (count > 0)
{
return(true);
}
return(false);
}
private UserDetail AddUserDetail(DbTransaction transaction, UserDetail userDetail)
{
if (userDetail == null)
{
throw new ArgumentNullException("userDetail");
}
var sql = @"INSERT INTO [dbo].[User_UserDetail]
([UserId],[Password],[PayPassword] ,
[RegionId],[AddressId],[Sex],[Birthday]
,[CreateTime],[RegisterIp],[LoginNum],[LastLoginIp],[LastLoginTime],
[ModifiedTime],[OpenId],[Avator],[IdentityStatus])
VALUES
(@UserId,@Password,@PayPassword ,
@RegionId,@AddressId,@Sex,@Birthday
,@CreateTime,@RegisterIp,@LoginNum,@LastLoginIp,@LastLoginTime,
@ModifiedTime,@OpenId,@Avator,@Identity);
select @@identity;";
var parameters = new[]
{
RepositoryContext.CreateParameter("@UserId", userDetail.UserId),
RepositoryContext.CreateParameter("@Password", userDetail.Password),
RepositoryContext.CreateParameter("@PayPassword", userDetail.PayPassword),
RepositoryContext.CreateParameter("@RegionId", userDetail.RegionId),
RepositoryContext.CreateParameter("@AddressId", userDetail.AddressId),
RepositoryContext.CreateParameter("@Sex", userDetail.Sex),
RepositoryContext.CreateParameter("@Birthday", userDetail.Birthday),
RepositoryContext.CreateParameter("@CreateTime", userDetail.CreateTime),
RepositoryContext.CreateParameter("@RegisterIp", userDetail.RegisterIp),
RepositoryContext.CreateParameter("@LoginNum", userDetail.LoginNum),
RepositoryContext.CreateParameter("@LastLoginIp", userDetail.LastLoginIp),
RepositoryContext.CreateParameter("@LastLoginTime", userDetail.LastLoginTime),
RepositoryContext.CreateParameter("@OpenId", userDetail.OpenId ?? "default"),
RepositoryContext.CreateParameter("@Avator", userDetail.Avator ?? ""),
RepositoryContext.CreateParameter("@ModifiedTime", userDetail.ModifiedTime),
RepositoryContext.CreateParameter("@Identity", userDetail.IdentityStatus)
// RepositoryContext.CreateParameter("@Remark",userDetail.Remark)
};
var result = RepositoryContext.ExecuteScalar(transaction, sql, parameters);
if (result != null && result != DBNull.Value)
{
userDetail.Id = Convert.ToInt64(result);
}
return(userDetail);
}
public void SuccessOrder(long shareOrderId)
{
var sql = "update Things_Trade set Status=@Status ,UpdateTime=GETDATE() where Id=@Id";
var parameters = new[]
{
RepositoryContext.CreateParameter("@Id", shareOrderId),
RepositoryContext.CreateParameter("@Status", Convert.ToInt16(ShareOrderStatus.Handled)),
};
RepositoryContext.ExecuteNonQuery(sql, parameters);
}
/// <summary>
/// Errors the order.
/// </summary>
/// <param name="shareOrderId">The share order identifier.</param>
/// <param name="message">The message.</param>
public void ErrorOrder(long shareOrderId, string message)
{
var sql = "update Things_Trade set Summary=@Summary ,Status=@Status ,UpdateTime=GETDATE() where Id=@Id";
var parameters = new[]
{
RepositoryContext.CreateParameter("@Id", shareOrderId),
RepositoryContext.CreateParameter("@Status", Convert.ToInt16(ShareOrderStatus.Error)),
RepositoryContext.CreateParameter("@Summary", message)
};
RepositoryContext.ExecuteNonQuery(sql, parameters);
}
public bool ExistsOpenId(string openId)
{
var sql = "select count(Id) from User_UserDetail where OpenId=@OpenId";
var result = RepositoryContext.ExecuteScalar(sql, RepositoryContext.CreateParameter("@OpenId", openId));
if (result == null || result == DBNull.Value)
{
return(false);
}
return(Convert.ToInt64(result) > 0);
}
public bool ExistsMobile(string mobile)
{
var sql = "select count(Id) from User_User where Mobile=@mobile";
var result = RepositoryContext.ExecuteScalar(sql, RepositoryContext.CreateParameter("@mobile", mobile));
if (result == null || result == DBNull.Value)
{
return(false);
}
return(Convert.ToInt64(result) > 0);
}
public bool CheckUserExists(string userName, string password, out long userId)
{
var sql = "SELECT TOP 1 Id FROM dbo.User_User WHERE Name=@Name AND Password=@Password";
var parameters = new[]
{
RepositoryContext.CreateParameter("@Name", userName),
RepositoryContext.CreateParameter("@Password", password)
};
userId = RepositoryContext.ExecuteScalar(sql, parameters).ToInt64();
return(userId > 0);
}
public void UpdateMap(long userId, string map)
{
var updateMapSql = "UPDATE dbo.User_UserMap SET ParentMap=@ParentMap WHERE UserId=@UserId";
var parameters2 = new[]
{
RepositoryContext.CreateParameter("@UserId", userId),
RepositoryContext.CreateParameter("@ParentMap", map)
};
RepositoryContext.Transation(() => {
var count = RepositoryContext.ExecuteNonQuery(updateMapSql, parameters2);
});
}
public void ErrorQueue(long id, string message)
{
var sql =
"update Basic_MessageQueue set Status=@status, Message=@message,RequestTime=GETDATE() where Id=@id";
var parameters = new[]
{
RepositoryContext.CreateParameter("@status", MessageStatus.Error),
RepositoryContext.CreateParameter("@message", message),
RepositoryContext.CreateParameter("@id", id)
};
RepositoryContext.ExecuteNonQuery(sql, parameters);
}
public User Add(User user, List <MoneyTypeConfig> moneyTypes)
{
if (user == null)
{
throw new ArgumentNullException("user");
}
var sql =
"INSERT INTO dbo.User_User ([Name], [Email], [Mobile], [UserName],[Status],[GradeId],[ParentId] ) VALUES (@Name,@Email,@Mobile,@UserName ,@Status,@GradeId,@ParentId); select @@identity;";
var parameters = new[]
{
RepositoryContext.CreateParameter("@Name", user.Name),
RepositoryContext.CreateParameter("@Email", user.Email),
RepositoryContext.CreateParameter("@Status", user.Status),
RepositoryContext.CreateParameter("@GradeId", user.GradeId),
RepositoryContext.CreateParameter("@Mobile", user.Mobile),
RepositoryContext.CreateParameter("@ParentId", user.ParentId),
RepositoryContext.CreateParameter("@UserName", user.UserName)
};
using (var transaction = RepositoryContext.BeginNativeDbTransaction())
{
try
{
var result = Convert.ToInt64(RepositoryContext.ExecuteScalar(transaction, sql, parameters));
if (result != 0)
{
user.Id = Convert.ToInt64(result);
}
user.Map.UserId = user.Id;
user.Map = AddUserMap(transaction, user.Map);
user.Detail.UserId = user.Id;
//添加用户详情
user.Detail = AddUserDetail(transaction, user.Detail);
//添加用户资产
AddAccount(transaction, user.Id, moneyTypes);
//添加分润订单
AddShareOrder(transaction, user.Id);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
return(user);
}
}
public UserDetail Add(UserDetail userDetail)
{
if (userDetail == null)
{
throw new ArgumentNullException("userDetail");
}
var sql = @"INSERT INTO [dbo].[User_UserDetail]
([UserId],[Password],[PayPassword] ,
[RegionId],[AddressId],[Sex],[Birthday]
,[CreateTime],[RegisterIp],[LoginNum],[LastLoginIp],[LastLoginTime]
,[OpenId] ,[ModifiedTime],[Remark])
VALUES
(@UserId,@Password,@PayPassword ,
@RegionId,@AddressId,@Sex,@Birthday
,@CreateTime,@RegisterIp,@LoginNum,@LastLoginIp,@LastLoginTime
,@OpenId ,@ModifiedTime,@Remark)
";
var parameters = new[]
{
RepositoryContext.CreateParameter("@UserId", userDetail.UserId),
RepositoryContext.CreateParameter("@Password", userDetail.Password),
RepositoryContext.CreateParameter("@PayPassword", userDetail.PayPassword),
RepositoryContext.CreateParameter("@RegionId", userDetail.RegionId),
RepositoryContext.CreateParameter("@AddressId", userDetail.AddressId),
RepositoryContext.CreateParameter("@Sex", userDetail.Sex),
RepositoryContext.CreateParameter("@Birthday", userDetail.Birthday),
RepositoryContext.CreateParameter("@CreateTime", userDetail.CreateTime),
RepositoryContext.CreateParameter("@RegisterIp", userDetail.RegisterIp),
RepositoryContext.CreateParameter("@LoginNum", userDetail.LoginNum),
RepositoryContext.CreateParameter("@LastLoginIp", userDetail.LastLoginIp),
RepositoryContext.CreateParameter("@LastLoginTime", userDetail.LastLoginTime),
RepositoryContext.CreateParameter("@OpenId", userDetail.OpenId),
RepositoryContext.CreateParameter("@ModifiedTime", userDetail.ModifiedTime),
RepositoryContext.CreateParameter("@Remark", userDetail.Remark)
};
var result = RepositoryContext.ExecuteScalar(sql, parameters);
if (result != null && result != DBNull.Value)
{
userDetail.Id = Convert.ToInt64(result);
}
return(userDetail);
}
public UserMap GetParentMap(long userId)
{
var sql = @"SELECT ParentMap FROM User_UserMap WHERE UserId = @UserId";
using (var reader =
RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@userId", userId))) {
var userMap = new UserMap();
if (reader.Read())
{
userMap.ParentMap = reader["ParentMap"].ToString();
}
return(userMap);
}
}
public UserMap GetSingle(long userId)
{
var sql = @"SELECT * FROM User_UserMap WHERE UserId = @UserId";
using (var reader =
RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@UserId", userId))) {
UserMap userMap = null;
if (reader.Read())
{
userMap = ReadUser(reader);
}
return(userMap);
}
}
public User GetSingle(string userName)
{
var sql = @"SELECT * FROM User_User WHERE UserName = @UserName";
using (var reader =
RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@UserName", userName))) {
User user = null;
if (reader.Read())
{
user = ReadUser(reader);
}
return(user);
}
}
public User GetSingleByMail(string mail)
{
var sql = @"SELECT * FROM User_User WHERE Email = @Email";
using (var reader =
RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@Email", mail))) {
User user = null;
if (reader.Read())
{
user = ReadUser(reader);
}
return(user);
}
}
public bool ChangePassword(long userId, string password)
{
var sql = @"update User_UserDetail set Password=@Password,ModifiedTime=GETDATE() where userId=@userid";
var parameters = new[]
{
RepositoryContext.CreateParameter("@Password", password),
RepositoryContext.CreateParameter("@userid", userId)
};
var count = RepositoryContext.ExecuteNonQuery(sql, parameters);
if (count > 0)
{
return(true);
}
return(false);
}
public User UserTeam(long userId)
{
var sql =
@"SELECT u.* ,vi.ServiceCenterUserId from User_User u ,(SELECT ud.userId ,ud.ServiceCenterUserId FROM user_userDetail ud )
vi WHERE vi.userId=u.id and u.Id=@Id";
using (var reader =
RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@Id", userId))) {
User user = null;
if (reader.Read())
{
user = ReadUser(reader);
}
return(user);
}
}
private void AddAccount(DbTransaction transaction, long userId, List <MoneyTypeConfig> moneyTypes)
{
foreach (var item in moneyTypes)
{
var sql = @"INSERT INTO [dbo].[Asset_Account]
([UserId],[MoneyTypeId],[Amount],[FreezeAmount],[HistoryAmount],[Token]) VALUES
(@UserId,@MoneyTypeId,0,0,0,@Token)";
// 动态调用方法
var token = ServiceInterpreter.Eval <string>("IAccountService", "GetToken", userId, item);
var parameters = new[]
{
RepositoryContext.CreateParameter("@UserId", userId),
RepositoryContext.CreateParameter("@MoneyTypeId", item.Id),
RepositoryContext.CreateParameter("@Token", token)
};
RepositoryContext.ExecuteScalar(transaction, sql, parameters);
}
}
private void AddAccount(DbTransaction transaction, long userId, List <MoneyTypeConfig> moneyTypes)
{
foreach (var item in moneyTypes)
{
var sql = @"INSERT INTO [dbo].[Asset_Account]
([UserId],[MoneyTypeId],[Amount],[FreezeAmount],[HistoryAmount],[Token]) VALUES
(@UserId,@MoneyTypeId,0,0,0,@Token)";
//TODO 9月重构注释
// var token = Ioc.Resolve<IAccountService>().GetToken(userId, item);
var token = string.Empty;
var parameters = new[]
{
RepositoryContext.CreateParameter("@UserId", userId),
RepositoryContext.CreateParameter("@MoneyTypeId", item.Id),
RepositoryContext.CreateParameter("@Token", token)
};
RepositoryContext.ExecuteScalar(transaction, sql, parameters);
}
}
public void Add(MessageQueue entity)
{
if (entity == null)
{
throw new ArgumentNullException(nameof(entity));
}
if (entity.Message == null)
{
entity.Message = string.Empty;
}
if (entity.Summary == null)
{
entity.Summary = string.Empty;
}
if (entity.Parameters == null)
{
entity.Parameters = string.Empty;
}
var sql =
"insert into Basic_MessageQueue(TemplateCode, Mobile, Content, [Parameters], Status, Message, Summary, RequestTime, SendTime,IpAdress) values(@templatecode, @mobilse, @contentt, @parameters, @status, N'', N'', GETDATE(), GETDATE(),@ipadress); select @@identity;";
var parameters = new[]
{
RepositoryContext.CreateParameter("@templatecode", entity.TemplateCode),
RepositoryContext.CreateParameter("@mobilse", entity.Mobile),
RepositoryContext.CreateParameter("@contentt", entity.Content),
RepositoryContext.CreateParameter("@parameters", entity.Parameters),
RepositoryContext.CreateParameter("@status", entity.Status),
RepositoryContext.CreateParameter("@ipadress", entity.IpAdress)
};
var result = RepositoryContext.ExecuteScalar(sql, parameters);
if (result != null && result != DBNull.Value)
{
entity.Id = Convert.ToInt64(result);
}
}
/// <summary>
///
/// </summary>
/// <param name="transaction"></param>
/// <param name="userId"></param>
private void UpgradeQueue(DbTransaction transaction, long userId)
{
var sql =
@"INSERT INTO [dbo].[Basic_UpgradeQueue] ([Type] ,[UserId],[Status],[Message] ,[ExecuteTime] ,[HandleTime] ,[CreateTime])
VALUES
(@Type,@UserId ,@Status ,@Message,@ExecuteTime,@HandleTime,@CreateTime )
";
var parameters = new[]
{
RepositoryContext.CreateParameter("@Type", Convert.ToInt16(UpgradeType.Reg)),
RepositoryContext.CreateParameter("@UserId", userId),
RepositoryContext.CreateParameter("@Status", Convert.ToInt16(QueueStatus.Pending)),
RepositoryContext.CreateParameter("@Message", string.Empty),
RepositoryContext.CreateParameter("@ExecuteTime", DateTime.Now),
RepositoryContext.CreateParameter("@HandleTime", DateTime.Now),
RepositoryContext.CreateParameter("@CreateTime", DateTime.Now)
};
RepositoryContext.ExecuteScalar(transaction, sql, parameters);
}
public void AddSingleNative(Bill bill)
{
if (bill == null)
{
throw new ArgumentNullException(nameof(bill));
}
var sql = @"INSERT INTO dbo.Asset_Bill
([Serial],[UserId],[OtherUserId],[Type],MoneyTypeId
,MoneyTypeName,Flow, Amount ,AfterAmount ,
OrderSerial ,CreateTime,Intro ,CreateTime,[Remark])
VALUES
(@Serial,@UserId,@OtherUserId,@Type,MoneyTypeId,
MoneyTypeName,@Flow, @Amount ,@AfterAmount ,
@OrderSerial ,@CreateTime,@Intro,GETDATE(),@Remark) ;SELECT @@IDENTITY;";
var parameters = new[]
{
RepositoryContext.CreateParameter("@AfterAmount", bill.AfterAmount),
RepositoryContext.CreateParameter("@Type", bill.Type),
RepositoryContext.CreateParameter("@Amount", bill.Amount),
RepositoryContext.CreateParameter("@CreateTime", bill.CreateTime),
RepositoryContext.CreateParameter("@Flow", bill.Flow),
RepositoryContext.CreateParameter("@Intro", bill.Intro),
RepositoryContext.CreateParameter("@MoneyTypeId", bill.MoneyTypeId),
RepositoryContext.CreateParameter("@OtherUserId", bill.OtherUserId),
// RepositoryContext.CreateParameter("@Remark", bill.Remark),
RepositoryContext.CreateParameter("@Serial", bill.Serial),
RepositoryContext.CreateParameter("@UserId", bill.UserId)
};
var result = RepositoryContext.ExecuteScalar(sql, parameters);
if (result != null && result != DBNull.Value)
{
bill.Id = Convert.ToInt32(result);
}
}
public void HandleQueueAndUpdateContent(long id, string message = null, string summary = null)
{
if (message == null)
{
message = string.Empty;
}
if (summary == null)
{
summary = string.Empty;
}
var sql =
"update Basic_MessageQueue set Status=@status, Message=@message, Summary=@summary,RequestTime=GETDATE() where Id=@id";
var parameters = new[]
{
RepositoryContext.CreateParameter("@status", MessageStatus.Handled),
RepositoryContext.CreateParameter("@message", message),
RepositoryContext.CreateParameter("@summary", summary),
RepositoryContext.CreateParameter("@id", id)
};
RepositoryContext.ExecuteNonQuery(sql, parameters);
}
