/// <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);
}
/// <summary>
/// 根据下面的会员，更新团队信息
/// </summary>
/// <param name="childuUserId"></param>
public void UpdateTeamInfo(long childuUserId = 0)
{
var userConfig = Ioc.Resolve <IAutoConfigService>().GetValue <TeamConfig>();
var userIds = new List <long>
{
childuUserId
};
if (childuUserId == 0)
{
var pageCount = 30; // 每次处理30个
// 总数
var totalCount = RepositoryContext.ExecuteScalar("select count(id) from User_UserMap").ConvertToLong();
var totalPage = totalCount / 30 + 1;
for (var i = 1; i < totalPage + 1; i++)
{
userIds = new List <long>();
var sql =
$"SELECT TOP 30 userId FROM (SELECT ROW_NUMBER() OVER (ORDER BY id ) AS RowNumber,userId FROM User_UserMap ) as A WHERE RowNumber > {pageCount}*({i}-1) ";
using (var reader = RepositoryContext.ExecuteDataReader(sql))
{
while (reader.Read())
{
userIds.Add(reader["UserId"].ConvertToLong());
}
}
UpdateTeamInfo(userIds, userConfig);
}
}
else
{
UpdateTeamInfo(userIds, userConfig);
}
}
/// <summary>
/// is exists database
/// </summary>
/// <param name="databaseName"></param>
/// <returns></returns>
public bool IsExistsDatabase(string databaseName)
{
var sql = $"select COUNT(1) From master.dbo.sysdatabases where name='{databaseName.ToLower()}'";
var obj = RepositoryContext.ExecuteScalar(sql);
return(obj.ToInt16() > 0);
}
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 long MaxUserId()
{
var sql = "select MAX(Id) from User_User ";
var result = RepositoryContext.ExecuteScalar(sql);
return(result.ConvertToLong());
}
/// <summary>
/// 根据商品SkuId，获取店铺Id
/// </summary>
/// <param name="productSkuId"></param>
public ObjectId GetStoreIdByProductSkuId(long productSkuId)
{
var sql =
$"SELECT Shop_Product.StoreId FROM Shop_Product INNER JOIN Shop_ProductSku ON Shop_Product.Id = Shop_ProductSku.ProductId where Shop_ProductSku.Id={productSkuId}";
var result = RepositoryContext.ExecuteScalar(sql);
return(result.ToObjectId());
}
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 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);
}
/// <summary>
/// Gets the account amount.
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <param name="moneyTypeId">The money type identifier.</param>
/// <returns>System.Decimal.</returns>
public decimal GetAccountAmount(long userId, Guid moneyTypeId)
{
var sql = $"select Amount from Asset_Account where UserId={userId} and MoneyTypeId='{moneyTypeId}'";
var result = RepositoryContext.ExecuteScalar(sql);
if (result == null || result == DBNull.Value)
{
return(-1);
}
return(Convert.ToDecimal(result));
}
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);
}
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 List <UserDetail> GetList(UserDetailInpt userDetail, out long count)
{
if (userDetail.PageIndex < 0)
{
throw new ArgumentNullException("pageIndex", "pageindex has to be greater than 1");
}
if (userDetail.PageSize > 100)
{
userDetail.PageSize = 100;
}
var sqlWhere = string.Empty;
var sqlCount = $"SELECT COUNT(Id) [Count] FROM User_User where 1=1 {sqlWhere}";
count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0;
var sql = $@"SELECT TOP {userDetail.PageSize} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id desc) AS RowNumber,* FROM User_UserDetail where 1=1 {
sqlWhere
}
) as A
WHERE RowNumber > {userDetail.PageSize}*({userDetail.PageIndex}-1)";
var result = new List <UserDetail>();
using (var dr = RepositoryContext.ExecuteDataReader(sql))
{
while (dr.Read())
{
result.Add(ReadUser(dr));
}
}
return(result);
}
public List <ProductItem> GetProductItems(ProductApiInput input, out long count)
{
if (input.PageIndex < 0)
{
input.PageIndex = 1;
}
if (input.PageSize > 100)
{
input.PageSize = 100;
}
#region 标签 分类查询
var TcSql = string.Empty;
if (input.ClassIds != null && input.ClassIds.Count > 1)
{
TcSql =
$"{TcSql} AND Type='{typeof(ProductClassRelation).FullName}' AND Id in ({input.ClassIds.ToSqlString()}) or FatherId in ({input.ClassIds.ToSqlString()}) ";
}
if (input.TagIds != null && input.TagIds.Count > 1)
{
TcSql =
$"{TcSql} AND Type='{typeof(ProductTagRelation).FullName}' AND Id in ({input.TagIds.ToSqlString()}) or FatherId in ({input.TagIds.ToSqlString()}) ";
}
if (!string.IsNullOrEmpty(TcSql))
{
TcSql = $@"SELECT DISTINCT EntityId FROM Basic_RelationIndex WHERE RelationId IN(
SELECT Id FROM Basic_Relation WHERE 1=1 {TcSql} )";
}
#endregion 标签 分类查询
var sqlWhere = string.Empty;
if (!string.IsNullOrEmpty(input.Keyword))
{
sqlWhere = $"{sqlWhere} AND Name Like '%{input.Keyword}%'";
}
if (input.MinPrice.HasValue)
{
sqlWhere = $"{sqlWhere} AND Price>{input.MinPrice}";
}
if (input.MaxPrice.HasValue)
{
sqlWhere = $"{sqlWhere} AND Price<{input.MaxPrice}";
}
if (input.PriceStyleId.HasValue)
{
sqlWhere = $"{sqlWhere} AND PriceStyleId ='{input.PriceStyleId}'";
}
if (input.BrandId.HasValue)
{
sqlWhere = $"{sqlWhere} AND BrandId ='{input.BrandId}'";
}
if (!TcSql.IsNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND Id in ({TcSql})";
}
if (!input.ProductIds.IsNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND Id in ({input.ProductIds})";
}
//商品状态过滤 商品店铺过滤
sqlWhere = $"{sqlWhere} AND ProductStatus= {(int)GoodsStatus.Online}";
// sqlWhere = $"{sqlWhere} AND StoreId>0";
// 库存
sqlWhere = $"{sqlWhere} AND Stock >= 1 ";
var priceStyleId = Guid.Empty;
// 商城模式状态不正常，获取不存在的，商品不显示
if (input.PriceStyles != null && input.PriceStyles.Count > 0 && !input.PriceStyleId.IsGuidNullOrEmpty())
{
input.PriceStyles = input.PriceStyles
.Where(r => input.PriceStyleId != null && r.Id == (Guid)input.PriceStyleId).ToList();
var priceStyleIdList = input.PriceStyles.Select(r => r.Id).ToList();
sqlWhere = $"{sqlWhere} AND PriceStyleId ='{input.PriceStyleId}' ";
}
var sqlCount = $"SELECT COUNT(Id) [Count] FROM Shop_Product where 1=1 {sqlWhere}";
count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0;
//排序处理 desc 降序
var sort = string.Empty;
if (input.SortOrder != ProductSortOrder.Defualt)
{
sort = input.SortOrder.ToString();
}
else
{
sort = "Id";
}
if (input.OrderType == 0)
{
sort = $"{sort} desc";
}
// 如果有传入指定数量，不分页，输出具体的数量
if (input.Count > 0)
{
input.PageSize = input.Count;
input.PageIndex = 1;
}
var sql = $@"SELECT TOP {input.PageSize} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY {sort}) AS RowNumber,* FROM Shop_Product where 1=1 {
sqlWhere
}
) as A
WHERE RowNumber > {input.PageSize}*({input.PageIndex}-1) ";
var result = new List <ProductItem>();
using (var dr = RepositoryContext.ExecuteDataReader(sql)) {
while (dr.Read())
{
result.Add(ReadProduct(dr));
}
}
return(result);
}
public List <PresaleProductItem> GetPresaleProducts(PresaleProductApiInput input, out long count)
{
if (input.PageIndex < 0)
{
throw new ArgumentNullException("pageIndex", "pageindex has to be greater than 1");
}
if (input.PageSize > 100)
{
input.PageSize = 100;
}
if (input.StartPrice > input.EndPrice)
{
throw new ArgumentNullException("price", "开始区间不大于结束区间");
}
//where
var sqlWhere = string.Empty;
if (input.PriceStyleId.HasValue)
{
sqlWhere += $" AND presale.PriceStyleId ='{input.PriceStyleId}'";
}
if (!string.IsNullOrEmpty(input.ProductName))
{
sqlWhere += $" AND product.Name Like '%{input.ProductName}%'";
}
if (!string.IsNullOrEmpty(input.CategoryId))
{
sqlWhere += $" AND product.CategoryId='{input.CategoryId}'";
}
if (input.StartPrice > 0 && input.EndPrice > 0)
{
sqlWhere += $" AND presale.VirtualPrice>={input.StartPrice} AND presale.VirtualPrice<={input.EndPrice}";
}
//status
var status = (int)GoodsStatus.Online;
sqlWhere += $" AND product.ProductStatus= {status} AND product.StoreId>0 AND presale.Status={status}";
//count
var sqlCount = $@"SELECT COUNT(presale.Id) [Count]
FROM Shop_PresaleProduct AS presale
INNER JOIN Shop_Product AS product ON presale.ProductId = product.Id AND presale.PriceStyleId=product.PriceStyleId
INNER JOIN Shop_Category AS category ON category.Id = product.CategoryId
LEFT JOIN Shop_ProductSku AS sku ON sku.Id = presale.SkuId
where 1=1 {sqlWhere}";
count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0;
//datas
var sql = $@"SELECT TOP {input.PageSize} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY presale.CreateTime) AS RowNumber,
presale.Id,
presale.ProductId,
product.Name AS ProductName,
product.ThumbnailUrl,
sku.PropertyValueDesc AS SkuName,
sku.CostPrice,
category.Name AS ProductTypeName,
presale.VirtualPrice,
presale.Stock,
presale.QuantitySold,
presale.Status,
presale.Sort
FROM Shop_PresaleProduct AS presale
INNER JOIN Shop_Product AS product ON presale.ProductId = product.Id AND presale.PriceStyleId=product.PriceStyleId
INNER JOIN Shop_Category AS category ON category.Id = product.CategoryId
LEFT JOIN Shop_ProductSku AS sku ON sku.Id = presale.SkuId
where 1=1 {sqlWhere}
) as A
WHERE RowNumber > {input.PageSize}*({input.PageIndex}-1) ";
var result = new List <PresaleProductItem>();
using (var dr = RepositoryContext.ExecuteDataReader(sql)) {
while (dr.Read())
{
result.Add(ReadProduct(dr));
}
}
return(result);
}
public IList <Bill> GetBillList(BillInput userInput, out long count)
{
if (userInput.PageIndex < 0)
{
throw new ArgumentNullException("pageIndex", "pageindex has to be greater than 1");
}
// TODO: ??? why set this at first??
//if (userInput.PageSize > 100) {
// userInput.PageSize = 100;
//}
var sqlWhere = string.Empty;
if (userInput.Flow.HasValue)
{
sqlWhere = $"{sqlWhere} AND Flow={(int)userInput.Flow}";
}
if (userInput.OtherUserId > 0)
{
sqlWhere = $"{sqlWhere} AND ParentId={userInput.OtherUserId}";
}
if (userInput.Id > 0)
{
sqlWhere = $"{sqlWhere} AND Id= '{userInput.Id}' ";
}
if (!userInput.MoneyTypeId.IsGuidNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND MoneyTypeId= '{userInput.MoneyTypeId}' ";
}
if (userInput.UserId > 0)
{
sqlWhere = $"{sqlWhere} AND UserId='{userInput.UserId}' ";
}
var sqlCount = $"SELECT COUNT(Id) [Count] FROM [Asset_Bill] where 1=1 {sqlWhere}";
count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0;
var result = new List <Bill>();
var sql = $@"SELECT TOP {userInput.PageSize} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id desc) AS RowNumber,[Id] ,[UserId] ,[OtherUserId] ,[Type],[Flow],[MoneyTypeId],[Amount] ,[AfterAmount] ,[Intro] ,[CreateTime] FROM [Asset_Bill] where 1=1 {
sqlWhere
}
) as A
WHERE RowNumber > {userInput.PageSize}*({userInput.PageIndex}-1) ";
using (var dr = RepositoryContext.ExecuteDataReader(sql))
{
while (dr.Read())
{
result.Add(ReadBill(dr));
}
}
return(result);
}
public IList <Bill> GetApiBillList(BillApiInput billApiInput, out long count)
{
if (billApiInput.PageIndex < 0)
{
throw new ArgumentNullException("pageIndex", "pageindex has to be greater than 1");
}
if (billApiInput.PageSize > 100)
{
billApiInput.PageSize = 100;
}
var sqlWhere = string.Empty;
if (billApiInput.Flow.HasValue)
{
sqlWhere = $"{sqlWhere} AND Flow={(int)billApiInput.Flow}";
}
if (!billApiInput.MoneyTypeId.IsGuidNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND MoneyTypeId= '{billApiInput.MoneyTypeId}' ";
}
if (billApiInput.UserId > 0)
{
sqlWhere = $"{sqlWhere} AND UserId='{billApiInput.UserId}' ";
}
var sqlCount = $"SELECT COUNT(Id) [Count] FROM [Asset_Bill] where 1=1 {sqlWhere}";
count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0;
var result = new List <Bill>();
var sql = $@"SELECT TOP {billApiInput.PageSize} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id desc) AS RowNumber,[Id] ,[Type],[Flow],AfterAmount,[MoneyTypeId],[Amount],[CreateTime] FROM [Asset_Bill] where 1=1 {
sqlWhere
}
) as A
WHERE RowNumber > {billApiInput.PageSize}*({billApiInput.PageIndex}-1) ";
using (var dr = RepositoryContext.ExecuteDataReader(sql))
{
while (dr.Read())
{
var item = new Bill
{
Id = dr.Read <long>("Id"),
Amount = dr.Read <decimal>("Amount"),
AfterAmount = dr.Read <decimal>("AfterAmount"),
Type = (BillActionType)dr["Type"].ToInt16(),
Flow = (AccountFlow)dr["Flow"].ToInt16(),
CreateTime = dr.Read <DateTime>("CreateTime"),
MoneyTypeId = dr.Read <Guid>("MoneyTypeId")
};
result.Add(item);
}
}
return(result);
}
/// <summary>
/// 支付完成后更新订单状态
/// 更新支付订单状态
/// </summary>
/// <param name="entityIdList">The entity identifier list.</param>
/// <param name="pay">The pay.</param>
/// <param name="isPaySucess">是否支出成功</param>
public ServiceResult AfterPay(List <object> entityIdList, Pay pay, bool isPaySucess)
{
var sqlList = new List <string>();
var sql = string.Empty;
var result = ServiceResult.Success;
//扣除解冻后的虚拟资产(支付成功后，冻结资产减少）
foreach (var payPair in pay.AccountPayPair)
{
if (payPair.Value > 0)
{
var accountSql =
$"select FreezeAmount from Asset_Account where MoneyTypeId='{payPair.Key}' and UserId={pay.UserId}";
var freezeAmount = RepositoryContext.ExecuteScalar(accountSql).ToDecimal();
if (freezeAmount < payPair.Value)
{
return(ServiceResult.Failure("冻结账户余额不足"));
}
// 扣除冻结资产
sql =
$"update Asset_Account set FreezeAmount=FreezeAmount-{payPair.Value} where UserId={pay.UserId} and MoneyTypeId='{payPair.Key}'";
sqlList.Add(sql);
// 财务记录
var intro =
$"支付订单(编号{pay.PayExtension.TradeNo})后，减少冻结资产,减少金额{payPair.Value},冻结金额账后{freezeAmount - payPair.Value}";
sql =
$@"INSERT INTO [dbo].[Asset_Bill]([UserId] ,[OtherUserId] ,[Type] ,[Flow] ,[MoneyTypeId],[Amount] ,[AfterAmount],[Intro] ,[CreateTime] ,[EntityId])
VALUES
({pay.UserId},0,{Convert.ToInt16(BillActionType.Shopping)},{
Convert.ToInt16(AccountFlow.Spending)
},'{payPair.Key}',{-payPair.Value},{freezeAmount - payPair.Value},'{intro}',GETDATE(),{
pay.Id
})";
sqlList.Add(sql);
}
}
//支付成功
if (isPaySucess)
{
// 更新支付账单状态
sql =
$"UPDATE [dbo].[Asset_Pay] SET [Message] = '{pay.Message}',[PayType]={Convert.ToInt16(pay.PayType)} ,[ResponseSerial] ='{pay.ResponseSerial}' ,[Status] =2 ,[ResponseTime] = '{pay.ResponseTime}' where id={pay.Id} and Status=1";
sqlList.Add(sql);
// 插入分润订单
if (pay.Type == CheckoutType.Order)
{
//更新支付状态
var orderStatus = 2; // 代发货
if (pay.PayExtension.IsGroupBuy)
{
orderStatus = 10; // 如果是团购商品，状态改成待分享
}
sql =
$"update Shop_Order set OrderStatus={orderStatus},PayId='{pay.Id}' where OrderStatus=1 and id in ({entityIdList.ToSqlString()})";
sqlList.Add(sql);
foreach (var item in entityIdList)
{
// 如果是管理员代付
var orderUserId = pay.UserId;
if (pay.PayExtension?.OrderUser?.Id >= 0) // 订单Id使用实际订单Id
{
orderUserId = pay.PayExtension.OrderUser.Id;
}
if (pay.PayType == PayType.AdminPay)
{
var order = EntityDynamicService.GetSingleOrder((long)item);
orderUserId = order.UserId;
}
// 通过支付记录，修改分入订单的触发类型
var triggerType = TriggerType.Order;
if (Convert.ToInt16(pay.PayExtension.TriggerType) > 0)
{
triggerType = pay.PayExtension.TriggerType;
}
//TODO 2019年9月22日 订单完成后分润 重构
//var shareOrderConfig = Ioc.Resolve<IAutoConfigService>().GetValue<ShopOrderShareConfig>();
//int pending = (int)ShareOrderSystemStatus.Pending;
//if (shareOrderConfig.OrderSuccess) {
// pending = (int)ShareOrderSystemStatus.OrderPendingSucess;
//}
// 分润订单, UserId 有等于 OrderId的可能, 用 EntityId = {trade.Id}) AND TriggerType != 1 联合进行限制
sql =
$" IF NOT EXISTS(SELECT * FROM Things_Trade WHERE EntityId = {(long)item} AND TriggerType != 1) " +
"INSERT INTO [dbo].[Things_Trade]([UserId] ,[Amount] ,[EntityId],[Parameters] ,[Status],[SystemStatus] , [TriggerType] ,[Summary] ,[CreateTime] ,[UpdateTime],[Extension],[ExecuteCount]) " +
$"VALUES ({orderUserId} ,{pay.Amount},{(long)item},'{string.Empty}' ,{(int)ShareOrderStatus.Pending} ,{(int)ShareOrderSystemStatus.Pending} ,{(int)triggerType} ,'{string.Empty}' ,'{DateTime.Now}' ,'{DateTime.Now}','',0)";
sqlList.Add(sql);
// 订单操作记录
sql =
"INSERT INTO [dbo].[Shop_OrderAction] ([OrderId] ,[ActionUserId] ,[Intro] ,[Extensions] ,[CreateTime],[OrderActionType])" +
$"VALUES({(long)item},{pay.UserId},'会员支付订单，支付方式为{pay.PayType.GetDisplayName()},支付现金金额为{pay.Amount}','','{DateTime.Now}',102)";
sqlList.Add(sql);
#region 如果是拼团购买
//更新活动记录状态
if (pay.PayExtension.IsGroupBuy)
{
sql = $"update Shop_ActivityRecord set Status=2 where OrderId={item} and Status=1";
sqlList.Add(sql);
sql =
$" select count(id) from Shop_ActivityRecord where ParentId = (select ParentId from Shop_ActivityRecord where OrderId = {item}) and ParentId> 0";
var gourpBuyCount = RepositoryContext.ExecuteScalar(sql).ConvertToLong();
if (gourpBuyCount > 0 && gourpBuyCount + 1 == pay.PayExtension.BuyerCount &&
pay.PayExtension.BuyerCount > 0)
{
// 拼团结束(修改订单状态)
var parentId = RepositoryContext
.ExecuteScalar($"select ParentId from Shop_ActivityRecord where OrderId={item}")
.ConvertToLong();
//将订单状态从待分享修改成待发货
sql =
$"update Shop_Order set OrderStatus=2 where Id in( select OrderId from Shop_ActivityRecord where (ParentId={parentId} or Id={parentId}) and Status=2) and OrderStatus=10";
sqlList.Add(sql);
//修改活动记录支付状态，为成功
sql =
$"update Shop_ActivityRecord set Status=5 where Id in( select Id from Shop_ActivityRecord where (ParentId={parentId} or Id={parentId}) and Status=2)";
sqlList.Add(sql);
}
}
#endregion 如果是拼团购买
}
}
#region 支付时附加的Sql后操作
var excecuteSqlList = pay.PayExtension?.ExcecuteSqlList;
if (excecuteSqlList != null)
{
// 动态调用执行
var resolveResult = DynamicService.ResolveMethod(excecuteSqlList.ServiceName,
excecuteSqlList.Method, entityIdList);
if (resolveResult.Item1.Succeeded)
{
var afterSqlList = (IList <string>)resolveResult.Item2;
if (afterSqlList.Count > 0)
{
sqlList.AddRange(afterSqlList);
}
}
}
#endregion 支付时附加的Sql后操作
}
//支付失败
else
{
if (pay.Type == CheckoutType.Order)
{
foreach (var payPair in pay.AccountPayPair)
{
if (payPair.Value > 0)
{
// 减少冻结资产
var accountSql =
$"select Amount from Asset_Account where MoneyTypeId='{payPair.Key}' and UserId={pay.UserId}";
var amount = RepositoryContext.ExecuteScalar(accountSql).ToDecimal();
// 扣除冻结资产
sql =
$"update Asset_Account set amount=amount+{payPair.Value} where UserId={pay.UserId} and MoneyTypeId='{payPair.Key}'";
sqlList.Add(sql);
// 财务记录
var intro =
$"支付订单(编号{pay.PayExtension.TradeNo})失败后，解冻金额，金额{payPair.Value},解冻账后{amount + payPair.Value}";
sql =
$@"INSERT INTO [dbo].[Asset_Bill]([UserId] ,[OtherUserId] ,[Type] ,[Flow] ,[MoneyTypeId],[Amount] ,[AfterAmount],[Intro] ,[CreateTime] ,[EntityId])
VALUES
({pay.UserId},0,{Convert.ToInt16(BillActionType.Shopping)},{
Convert.ToInt16(AccountFlow.Income)
},'{payPair.Key}',{payPair.Value},{amount + payPair.Value},'{intro}',GETDATE(),{
pay.Id
})";
sqlList.Add(sql);
}
}
}
//更新数据库
}
var count = RepositoryContext.ExecuteSqlList(sqlList);
if (count <= 0) // Ioc. Resolve<IPayService>().Log("订单支付后，数据库相关处理失败", LogsLevel.Error);
{
return(ServiceResult.Failure("订单支付后，数据库相关处理失败"));
}
// 支付成功后处理
var afterSuccess = pay.PayExtension?.AfterSuccess;
if (afterSuccess != null) // 动态调用执行
{
DynamicService.ResolveMethod(afterSuccess.ServiceName, afterSuccess.Method, entityIdList);
}
return(result);
}
public IList <User> GetViewUserList(UserInput userInput, out long count)
{
if (userInput.PageIndex < 0)
{
throw new ArgumentNullException("pageIndex", "pageindex has to be greater than 1");
}
if (userInput.PageSize > 100)
{
userInput.PageSize = 100;
}
var sqlWhere = string.Empty;
if (Convert.ToInt16(userInput.Status) > 0)
{
sqlWhere = $"{sqlWhere} AND Status={(int)userInput.Status}";
}
if (userInput.ParentId > 0)
{
sqlWhere = $"{sqlWhere} AND ParentId={userInput.ParentId}";
}
if (!userInput.Email.IsNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND Email= '{userInput.Email}' ";
}
if (!userInput.Mobile.IsNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND Mobile= '{userInput.Mobile}' ";
}
if (!userInput.UserName.IsNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND UserName='******' ";
}
if (!userInput.Name.IsNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND Name='{userInput.Name}'";
}
if (!userInput.GradeId.IsGuidNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND GradeId='{userInput.GradeId}'";
}
if (userInput.ServiceCenterId > 0)
{
sqlWhere = $"{sqlWhere} AND ServiceCenterUserId={userInput.ServiceCenterId}";
}
var sqlCount = $"SELECT COUNT(Id) [Count] FROM User_User where 1=1 {sqlWhere}";
count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0;
var result = new List <User>();
var sql = $@"SELECT TOP {userInput.PageSize} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id desc) AS RowNumber,* FROM User_User where 1=1 {sqlWhere}
) as A
WHERE RowNumber > {userInput.PageSize}*({userInput.PageIndex}-1) ";
using (var dr = RepositoryContext.ExecuteDataReader(sql))
{
while (dr.Read())
{
result.Add(ReadUser(dr));
}
}
return(result);
}
public IList <Reward> GetRewardList(RewardInput userInput, out long count)
{
if (userInput.PageIndex < 0)
{
throw new ArgumentNullException("pageIndex", "pageindex has to be greater than 1");
}
if (userInput.PageSize > 100)
{
userInput.PageSize = 100;
}
var sqlWhere = string.Empty;
//if (userInput.BeginAmount.HasValue)
// sqlWhere = $"{sqlWhere} AND BeginAmount={(decimal)userInput.BeginAmount}";
//if (userInput.EndAmount.HasValue)
// sqlWhere = $"{sqlWhere} AND EndAmount={(decimal)userInput.EndAmount}";
//if (userInput.EneTime.HasValue)
// sqlWhere = $"{sqlWhere} AND CreateTime> '{userInput.EneTime}' ";
//if (!userInput.MoneyTypeId.IsGuidNullOrEmpty())
// sqlWhere = $"{sqlWhere} AND MoneyTypeId= '{userInput.MoneyTypeId}' ";
//if (userInput.Serial.IsNullOrEmpty())
// sqlWhere = $"{sqlWhere} AND Serial='{userInput.Serial}' ";
if (userInput.UserId > 0)
{
sqlWhere = $"{sqlWhere} AND UserId='{userInput.UserId}' ";
}
if (userInput.OrderId > 0)
{
sqlWhere = $"{sqlWhere} AND OrderId='{userInput.OrderId}' ";
}
if (!userInput.ModuleId.IsGuidNullOrEmpty())
{
sqlWhere = $"{sqlWhere} AND ModuleId='{userInput.ModuleId}' ";
}
if (userInput.ModuleConfigId > 0)
{
sqlWhere = $"{sqlWhere} AND ModuleConfigId='{userInput.ModuleConfigId}' ";
}
var sqlCount = $"SELECT COUNT(Id) [Count] FROM [Share_Reward] where 1=1 {sqlWhere}";
count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0;
var result = new List <Reward>();
var sql = $@"SELECT TOP {userInput.PageSize} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id desc) AS RowNumber,[Id] ,[UserId] ,[OrderUserId] ,[OrderId],[MoneyTypeId],[Amount] ,[AfterAmount]
,[ModuleId],[RuleId] ,[Intro],[CreateTime] ,[Status] FROM [Share_Reward] where 1=1 {sqlWhere}
) as A
WHERE RowNumber > {userInput.PageSize}*({userInput.PageIndex}-1) ";
using (var dr = RepositoryContext.ExecuteDataReader(sql)) {
while (dr.Read())
{
result.Add(ReadReward(dr));
}
}
return(result);
}
